SQL的语言特性^§3.1.0#

• 综合统一
  • 非关系模型数据语言
    • 以下语言分别定义
      • 模式DDL（Schema Data Definition Language）
      • 外模式DDL（Subschema Data Definition Language）
      • DSDL（Data Storage Description Language）
      • DML（Data Manipulation Language）
    • 如需修改模式，必须停止当前DB运行、备份、修改、编译、重装数据
  • SQL语言
    • 集DDL、DML、DCL于一体
    • 有能力运行中修改模式
    • 数据结构的单一带来数据操作符的简化
• 高度非过程化
  • 不再关心存取路径
• 面向集合的操作方式
  • 不再说明具体处理过程（如存取路径、如何循环处理）
• 一种语法两种用途
  • 自含式语言
  • 嵌入式语言
• 语言简洁、易学易用
  • 查询：SELECT
  • 定义：CREATE, DROP, ALTER
  • 操纵：INSERT, UPDATE, DELETE
  • 控制：GRANT, REVOKE

SQL的语法特性^§3.1.0#

• 大小写不敏感 关键词、表名(关系名)、字段名(属性名)、内建/自定义函数或过程名
• 以 ; 作为语句分隔符

  1
  注意："分隔符"不是"结束符"

SQL中的基本(数据)类型^§3.2.1#

• char(n)定长串，不足充空
• varchar(n)变长串，不足截断
• int 整数(精度长度和具体机器相关，4字节居多)
• smallint小整数(机器相关，2字节居多)
• numeric(p,d)定点数，用户指定精度为p位，小数点右侧为d位
• real,double浮点数和双精度浮点数(精度长度和具体机器相关)
• float(n) 浮点数，用户指定的精度至少为n位
• Chapter 4 会涉及更多类型

基本模式(表)定义 - Create Table^§3.2.2#

• create table语法
  CREATE TABLE 表名 ( 字段名 数据类型列级约束条件 ,字段名 数据类型列级约束条件 ,表级约束条件 )
• 例

  1
  CREATE TABLE Cou( --课程表
  2
    Cno int primary key, /*课程号*/
  3
    Cname varchar(16) not null, --课程名
  4
    Cpno int, --先导课程号
  5
    Ccredit int not null, --学分
  6
    foreign key (Cpno) references Cou(cno) --外码约束条件
  7
  )

完整性约束条件^§3.2.2#

• 列级约束条件::= null|not null|unique|primay key|references 表名(字段名)

• 表级约束条件::=
  unqiue(字段名,字段名) |primary key(字段名,字段名) |foreign key(字段名,字段名) references 表名(字段名,字段名)
• 例

  1
  Create Table Tea( --教师表
  2
    tno int primary key, --工号
  3
    tname varchar(16) not null, --姓名
  4
    phone varchar(13) unique --联系电话
  5
  );
  6
  CREATE TABLE TC( --教师可讲学科表
  7
    tno int references Tea, --教师工号
  8
    cno int references Cou, --课程号
  9
    primary key(tno,cno)
  10
  );
  11
  create table CouHis( --开课记录表
  12
    at_year int, --开课学年
  13
    cno int references Cou, --课程号
  14
    serialNo int, --课序号
  15
    tno int not null references Tea, --主讲老师工号
  16
    weekDay int not null, --星期几
  17
    secNo int not null, --第几大节
  18
    unique(at_year,cno,weekDay,secNo,tno),
  19
    primary key(at_year,cno,serialNo),
  20
    foreign key(tno,cno) references TC
  21
  )

  显示更多显示更少

1
💡大多数数据库的 primary key 隐含 not null 约束
2
😈SQLite是例外，它要求 not null 必须显式标出

修改表定义(模式)^§3.2.2#

• 语法:ALTER TABLE 表名 修改指令,修改指令
  修改指令::= ADD COLUMN 字段名 数据类型列级约束条件 |DROP COLUMN 字段名 |ALTER COLUMN 字段名 新字段名 数据类型列级约束条件 |DROP CONSTRAINT 约束名称 |ADD CONSTRAINT 约束名称 表级约束条件
• 例

  1
  ALTER TABLE Tea add gender char(2) not null;
  2
  --上方SQL如表中已有数据，会执行失败
  3
  ALTER TABLE Tea add gender char(2) not null default '男';
  4
  --上方SQL成功

  1
  🚨SQL中的字符串常量应使用单引号 ' 而不是双引号 "
  2
  🚨许多数据库不支持删除已有字段，不支持操作约束
  3
  🚨各版本数据库 ALTER TABLE 语法实现不够规范，注意查看数据库手册

select子句^{§3.3.1,§3.4.1}#

select子句列出查询结果希望获得的属性，对应关系代数中的投影(π)

1
--指定目标字段
2
select Sname,Sage from Stu;

1
--获取全部字段
2
select * from Stu;

1
--计算表达式
2
select Sname,2025-Sage,'乖学生' from Stu;

1
--更名(别名)
2
select Sname as 姓名, 2025-Sage as 出生年, '乖学生' as 属性
3
from Stu;

1
--更名(别名)
2
select Sname as 姓名, strftime('%Y','now')-Sage as 出生年
3
from Stu;

• ALL|DISTINCT
  SQL允许查询结果中有重复行

  1
  select dept from Stu; --返回所有行，包括重复行
  2
  select ALL dept from Stu; --同上，ALL是默认值
  3
  select DISTINCT dept from Stu; --去重
  4
  select DISTINCT dept,Sname from Stu; --DISTINCT的作用范围

1
-- 字符串连接
2
select sname || '-' || ssex || sage from Stu; --使用连接符
3
select concat(sname,'-',ssex,sage) from Stu; --使用内建函数

1
-- 大小写转换，获取字符串长度，抽取子字符串等内建函数
2
select sname,lower(dept),upper(dept) from Stu; --大小写转换
3
select cname,length(cname) from Cou; --获取字符串长度
4
select cname,substr(cname,2,3) from Cou; --抽取子字符串

1
🚨内建函数并无统一标准，不同数据库需注意查手册

where子句^{§3.3 §3.4}#

where子句查询满足条件的元组，对应关系代数中的选择(σ)

1
select * from Stu where sage<20;
2
select * from Stu where sage<20 AND ssex='女';

1
select * from Stu where NOT sage<20 AND ssex='女';
2
select * from Stu where NOT(sage<20 AND ssex='女');

1
🚨比较运算符: >,<,>=,<=,!>,!< 要求<目标列>的值是能够比较大小的(值是有序的)

1
select * from SC where grade BETWEEN 80 AND 90;
2
select * from SC where grade>=80 AND grade<=90; --等价

1
🚨范围判断运算符要求<目标列>的值是有序的
2
🚨范围区间是闭区间

1
select * from Stu where dept IN('CS','IS');

1
🚨字符串的比较默认是`大小写敏感`的

1
💡很多数据库在字符串常量中使用连续的两个单引号表示一个单引号

1
select * from Stu where sname LIKE '李%';
2
/* 示例改名
3
update Stu set sname='李刘晨' where sno='95002';
4
update Stu set sname='张王敏' where sno='95003'; -- */

1
insert into Stu values('96001','李%','男',17,'CS');
2
select * from Stu where sname LIKE '李\%' ESCAPE '\';
3
--select * from Stu where sname='李%';

1
🚨字符串匹配运算符要求<目标列>的值是字符串
2
🚨字符串的匹配默认是`大小写敏感`的

1
select * from Cou where cpno IS NULL;

1
有的系统允许使用＝和<>进行NULL比较完成空值判断，但不推荐

from子句与多关系查询^{§3.3.1~§3.3.2}#

from子句指明查询涉及到的表(关系)，概念上对应关系代数中的笛卡尔积(×)

• 只涉及到一张表时，就是单关系查询
• 涉及到的表(关系)不止一个即多关系查询

1
select Sname,Cname from Stu,Cou;

1
笛卡尔积直接使用一般没有意义，通常和`where`配合使用构成连接

1
select S.sno,sname,cname,grade
2
from Stu AS S,SC,Cou AS C
3
where S.sno=SC.sno and C.cno=SC.cno;

1
💡有些数据库(比如Oracle)只接受省略关键词`AS`的别名语法

1
/* 反派黑料 */
2
select sno,sname,cname,grade
3
from Stu,SC,Cou
4
where sno=sno and cno=cno;

1
/* 这才是正面角色 */
2
select SC.sno,sname,cname,grade
3
from Stu,SC,Cou
4
where SC.sno=Stu.sno and SC.cno=Cou.cno;

1
/* '*'之领域和别名的妙用 */
2
select Cou.*,CP.cname as 先导课
3
from Cou,Cou as CP
4
where Cou.cpno=CP.cno;

1
💡实际项目中增加和修改字段时，应避免非必要的重复名称

简单SQL查询与关系代数的联系^§3.3.2#

1
SELECT A1, A2, ..., An
2
FROM T1, T2, ..., Tm
3
WHERE F;

$\Pi_{A1,A2,...An}\sigma_F(T1 \times T2 \times ... \times Tm)$

• 简单SQL查询的逻辑
  1. 通过from子句列出的表(关系)得到笛卡尔积
  2. 使用where子句给出的条件筛选步骤1的结果
  3. 对步骤2得到结果中的每一条元组，根据select子句指明的属性或计算表达式输出

order by子句 - 结果排序^§3.4.4#

order by子句使输出关系按<字段名>分量值的升序或降序排序

• ASC - 升序; DESC - 降序

1
select * from Stu ORDER BY sage; --升序(从小到大)
2
select * from Stu ORDER BY sage ASC; --同上，ASC是默认值
3
select * from Stu ORDER BY sage ASC,ssex DESC; --多属性组合排序

聚集函数/Aggregate Functions^§3.7.1#

目标列中一种特殊的计算表达式

1
select MAX(sage),MIN(sage),AVG(sage) from Stu;

1
/* COUNT那些事 */
2
select COUNT(*),COUNT(cno) from Cou; --COUNT(*) vs. COUNT(key)
3
select COUNT(*),COUNT(cpno) from Cou; --遇到null

1
/* distinct那些事 */
2
select SUM(sage),SUM(distinct sage) from Stu;

1
/* AVG=SUM/COUNT ? */
2
select AVG(sage),SUM(sage),COUNT(sage),
3
  SUM(sage)/COUNT(sage) as avg from Stu;
4
select AVG(sage),SUM(sage),COUNT(sage),
5
  cast(SUM(sage) as float)/COUNT(sage) as avg from Stu;

group by子句 - 分组^§3.7.2#

• 在group by指定字段上取值都相同的元组分到同一组
• 每一组在结果集中产生一条元组(行)
• 一般会在select子句中使用聚集函数配合group by

1
/* 列出每门课的课程号、最高分、最低分、平均分 */
2
select cno,max(grade),min(grade),avg(grade)
3
from SC GROUP BY cno;
4

5
/* 🚫下面是一个错误的SQL，请大家批判地看它 */
6
select cno,cname,max(grade),min(grade),avg(grade)
7
from SC,Cou GROUP BY cno;

1
/* 下面这个例子才是光明正确滴 */
2
select SC.cno,cname,max(grade),min(grade),avg(grade)
3
from SC,Cou where SC.cno=Stu.cno
4
GROUP BY SC.cno,cname;

1
🚨select子句中的字段只能使用出现在group by后的字段
2
🚨作为聚集函数的参数没有上述限制

1
select sno,avg(grade) from SC
2
group by sno HAVING avg(grade)>80;
3

4
select sno,avg(grade) as avg from SC
5
group by sno HAVING avg>80 and sno='95001';

1
select sno,avg(grade) from SC where cno<>2
2
group by sno HAVING avg(grade)>80;

当NULL遇到计算表达式^§3.6#

1
select 5+NULL, 'string' || NULL, concat('A',NULL,'B');
2
select DISTINCT cpno from Cou;

• 规则

  1
  💡包含NULL的计算表达的结果是NULL
  2
  💡内建函数试图忽略NULL
  3
  💡DISTINCT判断时，NULL=NULL 处理为 true

当NULL遇到逻辑运算^§3.6#

• 包含null的逻辑表达式的结果是unknown
  • 如: $5<null$ ; $null<>null$ ; $null=null$
• unknown 参与逻辑运算有以下规则
  • $(unknown~OR~true)=true$
  • $(unknown~OR~false)=unknown$
  • $(unknown~OR~unknown)=unknown$
  • $(unknown~AND~true)=unknown$
  • $(unknown~AND~false)=false$
  • $(unknown~AND~unknown)=unknown$
  • $(NOT~unknown)=unknown$
• where/having子句的逻辑表达式如结果为unknown，视为false

当NULL遇到聚集函数^§3.7.4#

1
select avg(cpno),sum(cpno),count(*),count(cpno) from Cou;

• 规则

  1
  💡所有聚集函数都忽略输入集中的空值，COUNT(*)除外
  2
  💡如输入集为空(或忽略空值后导致空集)，COUNT返回0，其他返回null

集合成员谓词IN带子查询^§3.8.1#

IN带子查询是指父查询与子查询之间用IN/NOT IN进行连接，判断某个属性列值是否在子查询的结果中。
🔎 查询与“刘晨”在同一个系的学生的学号、姓名、所在系

1
-- 自身连接求解:
2
SELECT S1.Sno,S1.Sname,S1.dept FROM Stu S1,Stu S2
3
WHERE S1.dept=S2.dept AND S2.sname='刘晨';
4
-- 上方语句步骤分解:
5
SELECT dept FROM Stu WHERE Sname='刘晨';--步骤1
6
SELECT Sno,Sname,dept FROM Stu WHERE dept IN('CS');--步骤2
7
-- 子查询求解:
8
SELECT Sno,Sname,dept FROM Stu WHERE dept IN(
9
  SELECT dept FROM Stu WHERE Sname='刘晨'
10
)

🔎 查询选修了课程名为“数据库”的学生的学号和姓名

1
-- 连接求解:
2
SELECT Stu.Sno,Sname FROM Stu,SC,Cou
3
WHERE Stu.Sno=SC.Sno AND SC.Cno=Cou.Cno AND Cname='数据库';
4
-- 子查询求解:
5
SELECT Sno,Sname FROM Stu WHERE Sno IN(
6
  SELECT Sno FROM SC WHERE Cno IN(
7
    SELECT Cno FROM Cou WHERE Cname='数据库'
8
  )
9
);

🔎 查询没有选修课程名为“数据库”的学生的学号和姓名

1
SELECT Sno,Sname FROM Stu WHERE Sno NOT IN(
2
  SELECT Sno FROM SC WHERE Cno IN(
3
    SELECT Cno FROM Cou WHERE Cname='数据库'
4
  )
5
);

如果不使用子查询

1
SELECT Stu.Sno,Sname FROM Stu
2
  EXCEPT
3
SELECT Stu.Sno,Sname FROM Stu,SC,Cou
4
WHERE Stu.Sno=SC.Sno AND SC.Cno=Cou.Cno AND Cname='数据库';

比较运算带子查询^§3.8.2#

父查询与子查询之间用比较运算符(> < = <= >= <>)进行连接

1
需要注意的是，子查询一定要跟在比较符之后而不是之前

🔎 找出超过平均年龄的学生的学号和姓名

1
SELECT Sno,Sname FROM Stu WHERE Sage>(
2
  SELECT AVG(Sage) FROM Stu
3
);

1
select sname from Stu where sage>SOME(
2
  select sage from Stu where dept='IS'
3
);

1
select sname from Stu where sage>ALL(
2
  select sage from Stu where dept='IS'
3
);

嵌套查询的求解^§3.8.0#

1
SELECT Sname FROM Stu WHERE Sno IN(
2
  SELECT Sno FROM SC WHERE Cno=2;
3
);
4
--首先求解子查询:
5
SELECT Sno FROM SC WHERE Cno=2; --得到{('95001'),('95002')}
6
--代入上级查询求解:
7
SELECT Sname FROM Stu WHERE Sno IN('95001','95002');

1
SELECT Sno,Cno FROM SC X WHERE Grade>=(
2
  SELECT AVG(Grade) FROM SC Y WHERE Y.Sno=X.Sno
3
);

空关系(Empty Relation)测试：EXISTS^§3.8.3#

语法: NOTEXISTS(子查询)

• 判断子查询结果是否空集，代表存在量词∃ 🔎列出所有选修了1号课程的学生姓名

  1
  -- 连接求解:
  2
  SELECT Sname FROM Stu,SC WHERE SC.Sno=Stu.Sno AND Cno=1;
  3
  -- EXISTS子查询求解:
  4
  SELECT Sname FROM Stu WHERE EXISTS(
  5
    SELECT * FROM SC WHERE SC.Sno=Stu.Sno AND Cno=1
  6
  );

• 等价替换
  一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换，但所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换

1
select Sname from Stu where NOT EXISTS(
2
  select * from Cou where NOT EXISTS(
3
    select * from SC where Sno=Stu.Sno and Cno=Cou.Cno
4
  )
5
);

1
select Sno from Stu X where NOT EXISTS(
2
  select * from Cou Y where Cno in(
3
    select Cno from SC where Sno='95002' --95002选了Y
4
  ) and NOT EXISTS(
5
    select * from SC where Sno=X.Sno and Cno=Y.Cno
6
  )
7
);

1
select distinct Sno from SC X where NOT EXISTS(
2
  select * from SC Y where Y.Sno='95002' and NOT EXISTS(
3
    select * from SC Z where Z.Sno=X.Sno and Z.Cno=Y.Cno
4
  )
5
);

重复元组(Duplicate Tuples)存在测试^§3.8.4#

语法: NOTUNIQUE(子查询)

• 判断子查询结果是否有重复元组
  🔎列出最多只被一门课作为先导课的课程号

  1
  select Cno from Cou C where UNIQUE(
  2
    select Cpno from Cou where Cpno=C.Cno
  3
  )

• 很多RDBS不支持UNIQUE(子查询)，可用替代方案

  1
  select Cno from Cou C where 1>=(
  2
    select COUNT(*) from Cou where Cpno=C.Cno
  3
  )

FROM子句中的子查询^§3.8.5#

1
select avg_grade,Cname from Cou,(
2
  select Cno,avg(grade) as avg_grade from SC group by Cno
3
) AS SC_AVG
4
where SC_AVG.Cno=Cou.Cno and avg_grade>85

WITH子句^§3.8.6#

WITH...AS...子句提供一种定义临时关系的方式，作用域仅在同一查询中

1
WITH SC_AVG(Cno,avg_grade) AS(
2
  select Cno,avg(grade) as avg_grade from SC group by Cno
3
)
4
select avg_grade,Cname from Cou,SC_AVG
5
where SC_AVG.Cno=Cou.Cno and avg_grade>85;

DELETE - 删除元组^§3.9.1#

1
DELETE FROM Cou; --尝试删除所有课程
2
DELETE FROM SC WHERE grade<60;
3
DELETE FROM SC WHERE grade<(select avg(grade) from SC);
4
-- 疑问：删除会导致平均成绩变化！
5
-- 实际上会先计算“子查询” 然后再带入求解

1
🚨注意：FROM后只能有一个关系表!!!

INSERT INTO - 添加元组^§3.9.2#

1
--不指定字段名，values中的值数量和域需匹配create table的字段定义:
2
INSERT INTO Stu VALUES('95020','陈冬','男',18,'CS');
3
--指定属性列values中值的数量和顺序需和属性列匹配:
4
INSERT INTO Stu(Sname,Sno,Ssex) VALUES('辛夏','95021','女');
5
--指定为空值:
6
INSERT INTO Stu VALUES('95020','梁秋','男',NULL,NULL);

1
🚨注意：INSERT INTO后只能有一个关系表!!!

• 缺省值和默认值

  1
  -- /*假定有表
  2
  create table T(
  3
    id int primary key,
  4
    x int not null DEFAULT 0,
  5
    y int not null,
  6
    z int
  7
  ); -- */
  8
  insert into T(y,id) values(2,1); -- (id,x,y,z)=(1,0,2,null)
  9
  insert into T(id) values(2);-- y报错

  1
  💡未指定某字段值时，首先尝试字段的DEFAULT设定，其次尝试NULL

• 插入子查询，子查询先完成，然后再插入

  1
  insert into R(A,B,C) select S.*,0 from S;

  1
  🚨子查询的结果的模式必需和指定的目标模式匹配!!!

UPDATE - 修改元素分量^§3.9.3#

🚨注意：UPDATE后只允许存在一个关系表!!!
💡值可以是常量、计算表达式,也可以是标量查询

1
UPDATE SC SET grade=grade+10; --分数可能超100
2
--两条语句分段改分，不同顺序会导致不同结果:
3
UPDATE SC SET grade=grade*1.05 WHERE grade>80;
4
UPDATE SC SET grade=grade*1.25 WHERE grade<=80;
5
--使用标量子查询赋值:
6
update Stu set Sage=(SELECT avg(sage) FROM Stu)+1;

1
update SC set grade=CASE
2
  WHEN grade<=90 THEN grade*1.05
3
  WHEN grade<=80 THEN grade*1.25
4
  ELSE grade*1.01
5
END;