SQL初步学习

这学期学习了《数据库系统基础教程》,学习了SQL的增删查改功能,知识点繁多,简单记录一下

表的定义

简单表定义

1
2
3
4
5
6
CREAT TABLE_NAME(
......
attr1_name type,
attr2_name type,
......
);

其中type类型有如下几种

1
2
3
4
5
6
7
8
9
CHAR(n)——填充字符串,长为n
VARCHAR(n)——终止符字符串,最长为n
BIT(n)——比特位串,长为n
INT——整形
SHORTINT——比INT小的整形
FLOAT——浮点型
DECIMAL(n,d)/NUMERIC(n,d)——长为n的十进制数字串,小数点是右数第d位
DATE——日期类型字符串
TIME——时间类型字符串

当在R中增加/修改元组时,并非一定会为所有属性赋值,当某个属性不存在赋值时,建议用NULL来填充,但是也可以用DEFAULT VALUE默认值来填充

1
2
3
...
attr_name type DEFAULT xxx, # xxx需与type类型一致
...

针对表结构的操作

1
2
3
DROP TABLE R # 删除R表
ALTER R ADD attr_name type [DEFAULT xxx] # 在R关系表中增加一个类型为type,名字为attr_name的属性
ALTER R DELETE attr_name # 在R关系表中删除一个名为arrt_name的属性

键属性

指明键属性有两个关键字,PRIMARY KEYUNIQUE,两者区别在于PRIMARY KEY声明的键属性不能为NULLUNIQUE声明的键属性可以,而且一个表中只能有一个PRIMARY KEY而可以有多个UNIQUE,简单来说就是PRIMARY KEY = UNIQUE + NOT NULL

在关系表中建表时定义键的方式有两种

1
2
3
4
5
6
7
8
9
10
...
attr1_name type PRIMARY KEY,
...
# or
...
attr1_name type,
attr2_name type,
...
PRIMARY KEY(attr1) # KEY(attr1,attr2) 可定义多重键
...

数据库中要求一个表中所有元组在键属性上不能一致,否则无法通过键去索引唯一元组,此时DBMS就会拒绝这类违反规则的插入或者更新操作

外键

外键FOREIGN KEY,一个属性/属性组定义为外键,则它会引用其他关系(也可为自身)的属性/属性组,其中被引用的属性若在自身关系中,则此属性必须为UNIQUE或者PRIMARY kEY;外键属性的值,必须在被引用关系中存在,否则违反参照完整性

声明外键的方法

1
2
3
4
5
6
7
8
...
R1_attr1_name type REFERENCES R2(R2_attr_name),
...
# or
...
...
FOREIGN KEY (R1_attr1_name) REFERENCES R2(R2_attr_name)
...

这两种方法都是等价的,其意义都是无论何时R2_attr_name必须为R2的键,R1表中的R1_attr1_name属性的值,必须为R2表中某个元组在R2_attr_name属性上的值,除非R1_attr1_name值为NULL(键不能为NULL)

维护参照完整性

上文提到了键属性之间的参照完整性,其意义是在某些操作会引起不满足键的参照完整性要求时(如两个元组键相同,某个关系中某个元组的外键的值在被参照关系中不存在等),DBMS会为了维护整体结构的参照完整性,对这类操作做出反应

DBMS对这些操作有以下几种反应可选

  1. 缺省原则,拒绝违法更新(RESTRICT),拒绝这类操作
  2. 级联原则(CASCADE),保持一致性,若修改则全修改,若删除则全删除
  3. 置空原则(SET NULL),影响外键时,若外键不符合要求,则把外键置空

为外键选择原则的方式为

1
2
3
...
R1_attr_name type REFERENCEs R2(R2_attr_name) ON DELETE/UPDATE SET NULL/CASCADE
...

RESTRICT是默认的原则

属性/元组约束

可以对关系表中具体属性或者元组进行约束

NOT NULL约束要求某个属性不能为NULL值,例如主键要求NOT NULL

CHECK (condition)可以进一步约束属性值,满足condition的属性值的元组才能放入,一般会在UPDATEINSERT操作时进行check,其中condition还支持通过查表来进行动态check

1
2
3
4
...
R1_attr_name type CHECK(R1_attr_name in SELECT R2_attr_name from R2)
# 约束 R1中的R1_attr_name属性必须在R2的R2_attr_name中出现
...

当约束条件同时针对关系中两个属性时,则必须使用基于元组的check

1
2
3
4
5
...
attr1_name type,
attr2_name type,
CHECK( attr1_name > attr2_name)
...

基于元组的check比基于属性的check检验更频繁,一个是元组任何属性改变都会check,另一个是只有声明check的属性改变 时才会check

当存在属性check与元组check时,基于属性的check先检验

表的查询是最重要的部分

SQL的查询基本框架为

1
2
3
4
5
SELECT [ALL|DISTINCT] attr-list
# *|{column-name|expression} [AS alias-name]
FROM R
[WHERE condition]
[ORDER BY {expression|interger} [ASC|DESC]]

SELECT

SELECT语句相当于一个投影,将需要的属性投影出来

1
2
3
4
SELECT * # 全投影
SELECT attr1 [AS name1],... # 将某个属性/某组属性投影出来,AS可以起别名
SELECT expression(e.g. a*b as c) # 将某些属性进行运算
SELECT 'A' as c # 加入常量

FROM

FROM语句说明了表的查询范围,FROM R即在R中进行筛选查询

同时SQL也支持多关系查询(多表联合查询),FROM R1, R2是将R1R2进行了笛卡尔积得到的新关系再进行筛选查询,针对重复属性,可以利用R1.nameR2.name进行区分

FROM语句还可以对关系进行改名,FROM R AS S,则在WHERESELECT中可以直接利用S.name来操作

WHERE

WHERE语句相当于一个选择语句,从关系中筛选符合condition的元组

选择对象可以是关系中的属性attr,常量值const

运算符有+,-,*,/,=,<>(不等),>,<,<=,>=,IS [NOT] NULL,NOT,AND,OR

基于字符串的比较是比较字典序,还支持模式匹配(低级正则)

1
str [NOT] LIKE pattern

pattern中用 &代表零个到多个字符,用_代表一个字符

1
2
name LIKE 'Mask _%'
# 匹配name是 Mask x... 的元组

特别注意在进行运算时,如果某个属性值为NULL,则比较运算的结果是NULL,算数运算的结果是UNKNOWN,而且NULL不能用作常量字面值出现在表达式中,常用 IS [NOT] NULL判是否为空

ORDER BY

ORDER BY语句可以对结果元组序列进行排序,ASC是默认方向,升序,DESC是降序,而且可以进行多属性排序

1
2
3
4
SELECT *
FROM R
ORDER BY a1 DESC, a2;
# 将R表中先按a1属性降序排序,针对相同的a1属性元组,按a2属性升序排序

查询结果运算

SQL支持将多个查询的结果进行类似集合的交并差运算

1
2
3
4
5
6
7
UNION # 并,大多数DBMS支持
... UNION [ALL] ... # ALL保留重复
INTERSECT # 交,大多数DBMS不支持
EXCEPT/MINUS # 差,大多数DBMS不支持
# e.g.
(SELECT * FROM R1) UNION (SELECT * FROM R2)
# 将R1与R2并起来

子查询

一个查询语句作为另一个查询语句的一部分

子查询常出现在WHERE语句中充当单值常量(标量)或者作为关系存在

子查询也可以出现在FROM语句中,不过此时应当给予一个别名,以便在SELECTWHERE中引用

1
2
3
4
5
6
7
8
SELECT *
FROM R1
WHERE R1.a1 IN (SELECT a1 IN R2);
# 将R1中a1属性在R2中a1属性出现过的元组投影出来

SELECT *
FROM R1, (SELECT a1 FROM R2) AS R3;
# 将R2中的a1属性列表投影出来作为R3关系

产生标量的子查询,即在子查询中SELECT了单个属性出来,如上面的(SELECT a1 IN R2)

关系条件表达式

WHERE语句中可以使用关系条件表达式进行筛选

ANY:存在某个元组满足即为真 s > ANY R

ALL:所有元组满足即为真s > ALL R

IN:关系中存在这个元组即为真s IN R

EXISTS:关系中存在元组(非空)即为真EXISTS R

(这里的R也可用子查询)

可以利用()括起来的标量值列表来表示一个元组,这里的标量值可以是常量字面值('Mask', 20)也可以是属性(name, age)

如果使用元组来进行关系条件运算,则必须要求元组和关系的元组有相同的分量个数

关联子查询

关联子查询出现在嵌套结构中,常常会进行多次查询,主要在于外部的每一次查询,内部都要进行一次子查询

1
2
3
4
5
# R1(a,b,c) R2(a,b,c)
SELECT *
FROM R1
WHERE R1.a < ALL (SELECT b FROM R2 WHERE c = R1.c)
# 针对R1中每个元组,都要查询一次R2获得所有的c值相同的b,再判断a是否小于所有的b

在关联子查询中需要注意属性变量的范围,在子查询中的某个属性aa,若在子查询的FROM关系中不存在aa,则会引用外部查询,简单来说子查询可以使用父查询中的属性

连接表达式

在上文提到的FROM中可以使用FROM R1, R2来进行多关系查询,也可以使用连接运算符来创造新的关系

CROSS JOIN:交叉连接,就是笛卡尔积

1
2
SELECT *
FROM R1 CROSS JOIN R2;

JOIN ON:$\theta$连接,笛卡尔积后进行选择

1
2
SELECT *
FROM R1 JOIN R2 ON condition;

NATURAL JOIN:自然连接,同名属性且同值的拼接

1
2
SELECT *
FROM R1 NATURAL JOIN R2 [ON condition]

上面的NATURAL JOIN是默认的内连接,实际上一个是NATURAL INNER JOIN,内连接对于不匹配的悬浮元组,是不会加入结果表的

而外连接OUTER JOIN则会把悬浮元组中的未匹配值填充空值使之称为查询结果

1
2
3
4
5
6
7
8
9
10
11
SELECT *
FROM R1 FULL OUTER JOIN R2 [ON condition];
# R1与R2的悬浮元组都保留

SELECT *
FROM R1 RIGHT OUTER JOIN R2 [ON condition];
# R2的悬浮元组都保留

SELECT *
FROM R1 LEFT OUTER JOIN R2 [ON condition];
# R1的悬浮元组都保留

针对关系的运算

消除重复

SQL是基于包运算的,意思是结果可能有重复,在SELECT时可以消除重复的元组

1
2
3
SELECT DISTINCT *
FROM R
# 将R中重复元组去除

SQL中的消除重复是基于排序的,因此频繁的消除重复可能会造成额外的时间复杂度

GROUP BY

首先介绍一下聚合运算符SUMAVGMINMAXCOUNT

前四个都是求关系表中某一列,即某一个属性的统计值,第五个是求列值个数,也可以用来求元组的数目(COUNT(*),去重计数:COUNT(DISTINCT *))

上述的运算都是忽略NULL运算的,因为NULL的算数运算都是KNOWN,空包的COUNT0

1
2
3
4
SELECT *
FROM R
WHERE a = (SELECT MAX(a) FROM R);
# R中a值最大的元组

接着继续来讲GROUP BY,它一般与聚合运算一起用

1
GROUP BY {name|expression},...

可以多属性分组GROUP BY a1, a2,先按a1分组,相同的a1里按a2分组

分组后,相应的SELECT中只能出现GROUP BY语句中出现的属性(分组依据)以及聚合运算符,因为未出现的属性被分组了,单独打印是没有意义的,但是可以通过聚合运算使得这些未出现的属性相对于分组的统计量存进表里,这是有意义的

1
2
3
4
5
# R(a,b,c)
SELECT a,MAX(b),MIN(c)
FROM R
ORDER BY a
# 统计相同a的最大b与最小c

HAVING

HAVING子句常用来根据聚合运算的结果对分组进行选择

HAVING不能使用SELECT中的别名,只能使用聚合运算符和ORDER BY中出现的属性

1
2
3
4
5
6
# R(a,b,c)
SELECT a,MAX(b) AS MAXB,MIN(c)
FROM R
ORDER BY a
HAVING MAX(b) > a # HAVING MAXB > a 错误
# 统计相同a的最大b,且b大于a,与最小c

增加表的元组

1
INSERT INTO R(...) VALUES (...)

插入元组时注意键值唯一性,属性列表和值表的对应数量类型一致,如果属性列表没有罗列全部属性R(a1,a2),则其余的值用缺省值填充,如果省略了属性列表,则值表应与R属性顺序一致

可以利用子查询向关系中插入多个元组,此时利用子查询替换VALUES (...)

1
INSERT INTO R(a1,a2) SELECT b1,b2 FROM S WHERE ...;

删除表中的元组

1
DELETE FROM R [WHERE condition]

删除满足condition的元组,这里的condition也可使用子查询,若没有WHERE语句,则把整个R表清空

在删除具有参照完整性约束的关系时,应当先把参照关系(子关系)先删除,再删除被参照关系(父关系),否则会因为违反参照完整性约束而操作失败

修改元组的特定分量,可以使用WHERE来增加修改条件

1
2
3
UPDATE R
SET A1 = V1, A2 = V2
[WHERE condition]

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×