4、关系数据库标准语言SQL

4.1 SQL概述

最早称为Sequel [ˈsiːkwəl]，后更名SQL

1. DDL提供了模式定义、修改和删除，基本表定义、修改和删除、域定义、修改和删除、视图的定义和删除
2. DML提供了数据查询子语言；还提供数据更新语句；允许用户对视图进行查询和受限的更新操作
3. DCL定义用户对数据对象的访问权限和审计
   特点
4. 集多种数据库语言于一体
   
5. 高度非过程化
   
6. 面向集合的操作方式
   
7. 一种语法两种使用方式
   
8. 功能强大，语言简洁
   

4.2 数据定义

4.2.1 SQL的数据类型

char(n)：定长字符串，长度n由用户指定。忽略n时，长度为1
varchar(n)：变长字符串，最大长度用户指定。
差别表现在前者需要固定长度的空间，后者占用空间在最大长度范围内可改变
bit(n)：定长二进位串，长度n由用户指定。忽略n时，长度为1
bit varying(n)：变长二进位串，最大长度用户指定。
int：整数，值域依赖具体实现
smallint：小整数，值域依赖于具体实现，小于int值域
dec(p,d)：p为有效数字的定点数，小数点右边占d位
float(n)：精度至少为n位数字的浮点数，值域依赖具体实现
real：实数，值域依赖具体实现
double precision：双精度实数，精度依赖具体实现，精度比real高
date：日期，年月日格式YYYY-MM-DD
time：时间，时分秒格式HH:MM:SS，time(n)可以表示比秒更小的单位，秒后取n位
timestamp：时间戳，date和time的结合
interval:时间间隔，SQL允许对date、time和interval类型的值进行计算

4.2.2 模式的定义和删除

4.2.3 表的定义、修改和删除

4.2.4 索引的创建和删除

创建索引是加快表的查询速度的有效手段
可以根据需要在基本表上建立一个或多个索引
用户可以在最频繁查询的列上建立聚簇索引以提高查询效率
在一个基本表上最多只能建立一个聚簇索引

4.3 数据查询

4.3.1 select语句的一般形式

select [all | distinct] <选择序列>
from <>, …, <>
[where <> ]
[group by <> {} [having <>] ]
[order by <> [asc | desc] {, <> [asc | desc]} ]
最基本的结构是select-from-where，并且select子句和from子句是必需的
select子句

1. 相当于关系代数的投影运算
2. 集合量词all或distinct，作用于所有列，缺省时为all
3. all不删除结果的重复行，distinct删除
4. 在不引起混淆时列名前的表明可缺省

4.3.2 不带where的简单查询

select子句中的列可以是表达式

select Sname, 2020-year(Birthday) as Age 
from Students;

distinct作用范围是所有目标列

select distinct Cno, Grade 
from SC
#错误写法如下
select distinct Cno, distinct Grade 
from SC

4.3.3 带where的查询：where子句的常用形式

比较表达式：>、<、=等
between表达式：between and，not between and

select sname, speciality
from students
where year(birthday) not between 2000 and 2001

in表达式：in，not in，判断给定元素是否在给定集合内

select sno, sname
from students
where speciality in ('计科','软工')

like表达式：like，not like，格式->[not] like <匹配串> [escape ‘<换码字符>’]
"_"与任意单个字符匹配
"%"与任意0个或多个字符匹配
escape用于定义转义字符

select sno, sname
from students
where sname like '李_ _' 
#一个汉字占两个字符，这里查找叫李某的人

select *
from courses
where cname like 'C\_%' escape '\'
#使'_'被转义，取字面意义，这里查询课程名为"C_XXXX"的课程

null表达式：is null，is not null

select sno, cno
from sc
where grade is null
#查询成绩为空的学生的学号和课程号

存在表达式：exist，not exist
唯一表达式：unique

4.3.4 排序和分组

排序：查询的结果按一定次序显示，如成绩由高到低
分组：查询每个学生的平均成绩，或者查询每门课程的平均成绩，单纯使用聚集函数很难表达，因为需要先对sc表中的元组按学生或课程进行分组再应用聚集函数
order by

1. 可以有多个排序列，用逗号隔开
2. 每个排序列都可以独立指定升降序，缺省时为升序
3. 优先按第一个排序列的值排序，第一个排序列值相同的结果元组按第二个排序列值排序，如此递推

select *
from sc
order by cno, grade desc
#查询每位学生的每门课程的成绩，并将结果按课程号升序、成绩降序排序

聚集函数：方便进行各种统计查询
count([all | distinct] *)：统计元组个数
count([all | distinct] 列名)：统计一列中值的个数
sum([all | distinct] 列名)：计算一列值的总和
avg([all | distinct] 列名)：计算一列值得平均值
max([all | distinct] 列名)：求一列值中得最大值
min([all | distinct] 列名)：求一列值中的最小值
f(distinct e)在得到函数值之前要删除多重集中的重复元素（f是聚集函数，e是值表达式）

select count(*)
from sc
where cno='cs102'
#查询选修了cs102课程的学生的人数

group by
group by <分组列> {, <分组列>} [having <分组选择条件>]

1. 分组列是属性，可以带表名前缀，它所在的表出现在from子句中
2. 可选的having短语用来过滤掉不满足<分组选择条件>的分组，缺省时等价于 having true
3. <分组选择条件>类似于where子句的查询条件，但其中允许出现聚集函数，而where中不能使用聚集函数

select sno,avg(grade)
from sc
group by sno having avg(grade)>85
#查询每门课平均成绩，输出平均成绩大于85的学生的学号号和平均成绩

对于带group by子句的select语句，select子句的结果列必须是group by子句中的<分组列>或聚集函数，如上例。

分组语句细化了聚集函数作用的作用对象

4.3.5 连接查询

SQL支持多表查询，from子句包含多个表时，相当于求这些表的笛卡尔积
可以在where子句中说明连接条件，并通过select子句选取所需要的属性

4.3.6 嵌套运算

允许将一个查询作为子查询嵌套在另一个select语句中
最常见的嵌套是将子查询嵌套在where子句或having短语的条件中
不相关子查询：子查询的条件不依赖于父查询
相关子查询：子查询的条件依赖于父查询
引出嵌套查询的表达式：in表达式、exist表达式、集合比较表达式、unique表达式

select sno,sname
from students
where sex='女' and speciality in
	(select speciality
	 from students
	 where sname='小明');
#查询和小明一个专业的女同学的学号姓名

select sno,sname
from students
where speciality !='软工' and year(birthday)>all(select year(birthday)
	 											 from students
	 											 where speciality='软工');
#查询比软工所有学生都小的其他专业的学生的姓名学号

select sno,sname
from students
where speciality !='软工' and year(birthday)>(select max(year(birthday))
	 										  from students
	 										  where speciality='软工');
#可以使用聚集函数实现，且效率更高

select cno,avg(grade)
from cs
group by cno
having avg(grade)>=all(select avg(garde)
					   from sc
					   group by cno);
#查询平均成绩最高的课程的课程号和平均成绩
#注意：SQL中的聚集函数不允许复合

4.3.7 集合运算

select name from stu where address='中国'
union
select name from stu where address='河南'
#等价于
select distinct name from stu where address='中国' or address='河南'

select name from stu where address='中国'
intersect
select name from stu where grade>70
#mysql不支持交运算，可以用disdinct in短语实现;差运算由not in实现

4.4 数据更新

插入单个元组有两种

insert into stu values ('201824100730','李云龙','男','山东','60','2000-06-24')
insert into stu (id,name,sex,address,grade,birthday) values ('201824100730','李云龙','男','山东','60','2000-06-24')

将查询结果插入基本表

insert into pet(stuid,name) select stu.id,'宠物' from stu where address='中国'
#为每一个地址是中国的元组添加一个宠物

删除条件可以涉及多个表

delete from stu where id in (
	select stuid from pet where name='宠物'
)

更新

update stu set sex='女' where name='赵六'
update stu set grade=grade-10 where grade>60

4.5 视图

DBMS执行CREATE VIEW语句的结果只是把对视图的定义存入数据字典，并不执行其中的SELECT语句。只是在对视图查询时，才按视图的定义从基本表中将数据查出。