MoCuishle

本文整理一下常用的SQL语句

在本文中，我们主要是用以下实例数据库：
学生-课程模式S-T：

• 学生表（学号，姓名，性别，年龄，院系）：Student（Sno，Sname，Ssex，Sage，Sdept）
• 课程表（课程号，课程名，先行课，学分）：Course（Cno，Cname，Cpno，Ccredit）
• 学生选课表（学号，课程号，成绩）：SC（Sno，Cno，Grade）

1. SQL的动词

• 数据查询：SELECT；
• 数据定义：CREATE；DROP；ALTER；
• 数据操纵：INSERT；UPDATE；DELETE；
• 数据控制：GRANT；REVOKE；

2. 数据定义

2.1 关于模式：

2.1.1 定义模式

CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>]

--为用户User定义一个学生-课程模式S-T
CREATE SCHEMA S-T AUTHORIZATION User；

P.s:

1. 若未指定<模式名>，则<模式名>隐含为<用户名>；
2. 定义模式实际上定义了一个命名空间，在该命名空间中可定义该模式包含的数据库对象，如：基本表、视图、索引等。

2.1.2 删除模式

DROP SCHEMA <模式名> <CASCADE（级联）|RESTRICT（限制）>

• CASCADE（级联）：删除模式的同时把模式中所有数据库对象全部删除；
• RESTRICT（限制）：若该模式中定义了下属数据库对象（表、视图等），则拒绝该删除语句的执行。

--级联删除模式User
DROP SCHEMA User CASCADE;

2.2 关于表

2.2.1 创建表：

CREATE TABLE <表名>
(<列名><数据类型>[<列级完整性约束>]
[,<列名><数据类型>[<列级完整性约束>]
...
[,<表级完整性约束>]
)

• <列级完整性约束>：设计相应属性列的完整性约束条件；
• <表级完整性约束>：设计一个或多个属性列的完整性约束条件；

--建立学生表
CREATE TABLE Student
(
Sno CAHR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
--建立课程表
CREATE TABLE Course
(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);

--建立学生选课表
CREATE TABLE SC
(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student (Sno),
FOREIGN KEY (Cno) REFERENCES Course (Cno)
)

2.2.2 修改表

ALTER TABLE <表名>
[ADD [COLUMN] <列名><数据类型>[完整性约束]]
[ADD <表级完整性约束>]
[DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
[DROP CONSTRAINT <完整性约束名> [CASCADE|RESTRICT]]
[ALTER COLUMN <列名><数据类型>];

--向Student表中加入“入学时间”列，数据类型为日期（新增的一列均为空值）
ALTER TABLE Student ADD S_entrance DATE;

--修改年龄数据类型为整数
ALTER TABLE Student ALTER COLUMN Sage INT;

--增加课程名称必为唯一值的约束
ALTER TABLE Course ADD UNIQUE(Cname);

2.2.3 删除表

DROP TABLE <表名> [CASCADE|RESTRICT]

• CASCADE：删除表时将相关依赖对象（索引、视图等）一起删除；
• RESTRICT：删除的表不能被其他表的约束引用，否则不能删除；

2.3 模式与表

每个基本表需属于某个模式。定义基本表所属模式有如下方式：

• 表名中显式给出：CREATE TABLE "S-T".Student（...）;
• 创建模式同时创建表；
• 设置所属模式；

创建基本表时，若未指定模式，系统会根据搜索路径来确定该对象所属的模式。设置搜索路径的方式：
SET search_path TO "S-T", PUBLIC；

2.4 关于索引

数据库管理系统中常见的索引：

• 顺序文件上的索引
• B+树索引
• Hash索引
• 位图索引

其中，B+树索引具有动态平衡的优点，Hash索引具有查找速度快的特点。

2.4.1 建立索引

索引可以建立在表的一列或多列上：

CREATE [UNIQUE][CLUSTER] INDEX <索引名>
ON <表名>(<列名>[<次序>][,<列名>[<次序>]]...)

• 表名：要建立索引的基本表的名字；
• 次序：制定索引值的排列次序——升序：ASC；降序：DESC。缺省值为ASC。
• UNIQUE：索引的每一个索引值只对应唯一的数据记录
• CLUSTER： 聚簇索引

--为Student，Course，SC三个表建立索引。其中Student按学号升序建立唯一索引，Course按课程号升序建立唯一索引，SC按学号升序和课程号降序建立唯一索引：
CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

2.4.2 修改/删除索引

ALTER INDEX <旧索引名> RENAME TO <新索引名>
E.g：ALTER INDEX SCno RENAME TO SCSno;

DROP INDEX <索引名>
E.g：DROP INDEX Stusname;

3. 数据查询

SELECT [ALL|DISTINCT] <目标表达式>[,<目标表达式>]...
FROM <表/视图名>[,<表/视图名>]...|(SELECT 语句) [AS] <别名>
[WHERE <条件表达式>]
[GROUP <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]]

• SELECT：指定要显示的属性列；
• FROM：指定查询对象
• WHERE：指定查询条件
• GROUP BY：对查询结果按指定列分组
• HAVING：只有满足指定条件的组才输出
• ORDER BY：对查询结果按列值的升序或降序排列

3.1 基础单表查询

E.g：
“查询全体学生姓名及出生年份”：
SELECT Sname，2020-Sage From Student；

“查询全体学生姓名，出生年份和所在院系（小写字母表示系名）”：
SELECT Sname，2020-Sage，LOWER(Sdept) FROM Student；

【使用别名】：
SELECT Sname NAME，2020-Sage BIRTHDAY FROM Student；

【消除重复行】（不指定DISTINCT关键词，则缺省为ALL）：
SELECT DISTINCT Sno FROM SC；

【大小比较】：
SELECT Sname FROM Student WHERE Sdept=‘CS’；
SELECT Sname，Sage FROM Student WHERE Sage<20 AND Sage>17；

【范围】：
SELECT Sname，Sage FROM Student WHERE Sage （NOT） BETWEEN 20 AND 23；

【集合】：
SELECT Sname，Sage FROM Student WHERE Sdept （NOT） IN（‘CS’，‘MA’，‘IS’）；

【字符匹配】（‘%’：任意长度字符；‘’：任意单个字符）：
SELECT * FROM Student WHERE Sno （NOT）LIKE ‘2018216020’；
SELECT * FROM Student WHERE Sno （NOT）LIKE ‘刘%’；
SELECT * FROM Course WHERE Cname （NOT）LIKE ‘DB_%i ’ ESCAPE ‘\’；（查询以‘DB’开头且倒数第三个字符为i的课程信息，ESCAPE ‘\’说明\为转码符号）。

【空值】
SELECT Sno，Cno FROM SC WHERE Grade IS（NOT）NULL；

3.2 进阶单表查询：

3.2.1 ORDER BY 子句

【ORDER BY】按一个或多个属性排序（ASC——升序（默认）；DESC——降序）：
SELECT Sno，Grade
FROM SC
WHERE Cno=‘3’
ORDER BY Grade DESC；

3.2.2 聚集函数

• 统计元组个数：COUNT（*）
• 统计一列中值得个数：COUNT （[DISTINCT｜ALL]<列名>）
• 计算一列总和：SUM（[DISTINCT｜ALL]<列名>）
• 计算一列平均值：AVG（[DISTINCT｜ALL]<列名>）
• 求一列最值：MAX/MIN（[DISTINCT｜ALL]<列名>）

SELECT COUNT（*）FROM Student；
SELECT COUNT（DISTINCT Sno） FROM SC；
SELECT AVG（Grade）FROM SC WHERE Cno=‘1’；

3.2.3 GROUP BY 子句

细化聚集函数作用对象：

• 未对查询结果分组，聚集函数将作用在整个查询结果；
• 对查询结果分组，聚集函数会作用在每个组；

求各门课程相应的选课人数：
SELECT Cno，COUNT（Sno）
FROM SC
GROUP BY Cno；

查询选了三门以上课程的学生学号
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT（*）>3;

P.s：WHERE 与 HAVING 区别
作用对象不同
WHERE子句作用于基本表或视图，从中选择满足条件的元组；
HAVING短语作用于组，从中选择满足条件的组；
WHERE子句中不能用聚集函数作为条件表达式；

3.3 连接查询

3.3.1 等值&非等值连接查询

等值连接：
SELECT Student., SC.
FROM Student, SC
WHERE Student.Sno=SC.Sno;

自然连接：
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno;

连接操作执行过程：
（1）嵌套循环：多重循环嵌套
（2）排序合并（常用于等值连接）：先排序后连接
（3）索引连接：建立索引进而连接

3.3.2 自身连接

一个表与自身进行连接。需要通过别名以示区别：
SELECT FIRST.Cname,SECOND.Cname
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.cno;

3.3.3 外连接

普通连接仅输出满足连接条件的元组，而外连接则以指定表为连接主体，将主体表中不满足的元组一并输出：

• 左外连接：列出左边关系中所有的元组；
• 右外连接：列出右边关系中所有的元组；

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUT JOIN SC ON（Student.Sno=SC.Sno）;

4. 数据更新

4.1 插入数据

A. 插入元组
INSERT INTO <表名>[(<属性1>[,<属性2>]...)]
VALUES (<常量1>[,<常量2>]...)；

E.g：
INSERT INTO Student（Sno，Sname，Ssex，Sdept，Sage）
VALUES （‘2018216020’，‘Li’，‘男’，‘CS’，‘18’）；

B. 插入子查询结果
INSERT INTO <表名>[(<属性1>[,<属性2>]...)]
子查询

E.g：
INSERT INTO Dept_age（Sdept，Avg_age）
SELECT Sdept，AVG（Sage）
FROM Student
GROUP BY Sdept；

4.2 数据修改

UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]...
[WHERE <条件>];

存在三种修改方式：

• 修改某一个元组的值；
• 修改多个元组的值；
• 带子查询的修改语句；

【将学号为2018216020的年龄改为24岁】：
UPDATE Student
SET Sage=24
WHERE Sno=‘2018216020’；

【将所有学生年龄+1】
UPDATE Student
SET Sage=Sage+1；

【将计算机专业全体学生成绩置0】
UPDATE SC
SET Grade=0
WHERE Sno IN
（SELECT Sno FROM WHERE Sdept=‘CS’）；

4.3 删除数据

DELETE FROM <表名>
[WHERE <条件>]

功能：删除指定表中满足WHERE子句条件的元组
存在三种删除方式：

• 删除某一个元组的值
• 删除多个元组的值；
• 带子查询的删除语句；

【将学号为2018216020学生记录删除】
DELETE FROM Student
WHERE Sno=‘2018216020’；

【删除所有学生的选课记录】
DELETE FROM SC；

【删除计算机系所有学生的选课记录】
DELETE FROM SC
WHERE Sno IN
（SELECT Sno FROM Student WHERE Sdept=‘CS’）；