章节导航
顶部	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 具有打印功能