章節導航
頂部	E/R 理論 - 規範化 - 資料查詢 - 整合 SQL 和程式語言

一位 Wikibookian 已提名此頁面進行清理。
您可以幫助改進它。請檢視任何相關討論。

資料查詢

查詢語言

SQL（結構化查詢語言）。SQL 是一種資料定義和操作語言（DDL 和 DML 兩種）
RA（關係代數）
Datalog

建立表

命令：CREATE TABLE

對於關係

Gene(gid, name, annotation)

Experiment(eid, name,date)

Microarray(gid, eid, exprlevel)

可以使用以下 SQL 命令集建立表

CREATE TABLE Gene( gid INTEGER, name VARCHAR(20), annotation  
VARCHAR(50), PRIMARY KEY (gid));

CREATE TABLE Experiment(eid INTEGER, name VARCHAR(10), date  
DATE, PRIMARY KEY (eid));

CREATE TABLE Microarray (a INTEGER, b INTEGER, exprlevel REAL,  
PRIMARY KEY (gid, eid));

主鍵用於唯一標識表中的每一行。它可以是實際記錄的一部分，也可以是人工欄位。SQL 將根據主鍵自動對錶進行索引。還可以對其他鍵（除了主鍵）進行索引；可以透過使用 CREATE INDEX 命令來完成。

請注意，“Microarray”表的主鍵由其他兩個表（即 Gene 和 Experiment）的鍵組成。這些欄位稱為外部索引鍵。必須參考表從哪裡獲得主鍵。語法如下。

CREATE TABLE Microarray (a INTEGER, b INTEGER, exprlevel REAL,  
PRIMARY KEY (gid, eid), FOREIGN KEY (a) REFERENCES Gene 
(gid), FOREIGN KEY (b) Experiment (eid) );

這些型別的約束通常稱為引用完整性約束。

儲存資料

命令：INSERT INTO

示例

INSERT INTO Gene VALUES(1, "1433E", "enzyme binding");

大多數資料庫管理系統都具有能夠一次載入大量資料的功能。

查詢

投影

顯示 Gene 表中儲存的所有資料
- SQL
```
SELECT * 
FROM Gene;
```
- RA $Gene$
- Datalog $Gene(x,y,z)$
顯示 Gene 表中所有基因的名稱
- SQL
```
SELECT name
FROM Gene;
```
- RA $\pi _{name}(Gene)$
- Datalog $Answer(x)\leftarrow Gene(y,x,z)$
顯示滿足特定條件的基因名稱
- SQL
```
SELECT * 
FROM Gene
WHERE annotation like '%phosphates%';
```
- RA $\sigma _{annotation='\%phosphate\%'}(Gene)$
- Datalog $Gene(x,y,z),z='\%phosphates\%'$

笛卡爾積

定義：兩個大小分別為 nxa 和 mxb 的表的笛卡爾積是一個具有 n*m 行和 a+b 列的表

SQL
```
SELECT * 
FROM Gene, Experiment;
```
RA $Gene\times Experiment$
Datalog $Answer(x,y,z,a,b,c)\leftarrow Gene(x,y,z),Experiment(a,b,c)$

連線

示例 1

表格

基因

表達

實驗

gid	名稱	註釋
...	...	...
...	...	...
...	...	...

gid	eid	表達水平
...	...	...
...	...	...
...	...	...

eid	名稱	日期
...	...	...
...	...	...
...	...	...

E-R 圖

檔案：Cs5634-ER-pic1.gif

SQL

SELECT Genes.*, Expts.*, exp_level
  FROM Genes, Expression, Expts
 WHERE Genes.gid = Expression.gid
   AND Expression.eid = Expts.gid;

關係代數

 Genes ?? Expression ?? Expts

 (A ?? B) ?? C = A ?? (B ?? C)

Datalog

 Answer(x, y, t, a, b, w) ← Genes(x, y, z), Expression(x, t, w), Expts(t, a, b)

 Genes' x match Expression's x
 Expts' t match Expression's t

示例 2

問題：...

表達

gid	eid	表達水平
...	...	...
...	...	...
...	...	...

RA CannotbeMax = ... Allpairs = ... Allpairs - CannotbeMax

SQL

CREATE VIEW CannotbeMax(gid, eid) AS
    SELECT E1.gid, E1.eid
      FROM Expression AS E1, Expression AS E2
     WHERE E1.explevel < E2.explevel;

CREATE VIEW Allpairs(gid, eid) AS
    SELECT gid, eid
      FROM expression;

SELECT * FROM Allpairs
EXCEPT
SELECT * FROM CannotbeMax;

- EXCEPT
∪ UNION
∩ INTERSECT

Datalog

 CannotbeMax(x, y) <- Expression(x, y, t), Expression(a, b, w), t < w.

 Answer(x, y) <- Expression(x, y, a), NOT CannotbeMax(x, y).

有趣的查詢

沒有 MAX() 運算子

最大值

- ref. Join section Example 2

最小值
第二大值
中位數

關係除法

A X B = C
C / B = ?

學生和課程

學生ID	課程ID
one	CS2204
one	CS4104
one	CS6354
two	CS2204
two	CS4104

畢業必修課（R4G）

課程ID
CS2204
CS4104
CS6354

透過關係代數
1. sid = $\pi$ _sid學生和課程
2. Ideal = sid X R4G
3. Reality = 學生和課程
4. Defaulters = Ideal - Reality
5. Answer = sid - $\pi$ _sidDefaulters
透過 SQL

1.

CREATE VIEW SID(sid)<BR>
          SELECT sid<BR>
          FROM   Student&Courses<BR>

2.

CREATE VIEW IDEAL(sid,courseId)<BR>
           SELECT sid,courseID<BR>
           FROM  SID,R4G<BR>

3.

CREATE VIEW REALITY(sid,courseId)<BR>
           SELECT *<BR>
           FROM Student&Courses<BR>

4.

CREATE VIEW Defaulters(sid,courseId)<BR>
           SELECT * <BR>
           FROM IDEAL<BR>
           EXCEPT<BR>
           SELECT *<BR>
           FROM REALITY<BR>

5.

SELECT * <BR>
  FROM SID<BR>
  EXCEPT <BR>
  SELECT sid<BR>
  FROM Defaulters <BR>

練習

找出滿足畢業要求但只差一門課的學生。
找出滿足大多數畢業要求的學生。

檢視並沒有真正計算，大多數情況下，由於歧義，無法插入行。
SQL 注入是一種駭客技術，它利用格式化的輸入執行了非預期的 SQL 查詢。

花哨的功能
1. ORDER BY
2. MAX,AVG,SUM,MIN
3. GROUP BY, HAVING
4. SELECT 具有列印功能