本文所属《数据库系统原理》分类专栏，相关文章导航请点击《数据库系统原理》目录汇总贴

一、SQL概述

SQL（Structured Query Language，结构化查询语言），是关系数据库的标准语言。
【SQL的功能】：

功能 动词
数据查询 SELECT
数据定义 CREATE, DROP, ALTER
数据操纵 INSERT, UPDATE, DELETE
数据控制 GRANT, REVOKE

数据查询：select选择
数据定义：create创建, drop删除, alter更改
数据操纵：insert插入, update更新, delete删除
数据控制：grant授予, revoke收回

【SQL的特点】：

  1. 综合统一；
  2. 高度非过程化；
  3. 面向集合的操作方式；
  4. 以同一种语法结构进行多种使用；
  5. 语言简洁，易学易用；

【SQL的基本概念】：
支持SQL的关系数据库管理系统，同样支持关系数据库的三级模式结构。

3.1.3 SQL对关系数据库模式的支持.png

用户可以用SQL对基本表和视图进行查询和其他操作，基本表和视图一样，都是关系。

基本表是本身独立存在的表，在关系数据库管理系统中，一个关系就对应一个基本表。

视图是从一个或几个基本表导出的表，本身不独立储存在数据库中，因此视图是一个虚表。

二、学生-课程数据库

本章以学生-课程数据库为例，来讲解SQL的数据定义、数据操纵、数据查询和数据控制语句。

【学生-课程模式 S-T 】（加粗为主码）
学生表：Student(Sno,Sname,Ssex,Sage,Sdept)
课程表：Course(Cno,Cname,Cpno,Ccredit)
学生选课表：SC(Sno,Cno,Grade)

学生表：Student

3.2_a_学生表.png

课程表：Course

3.2_b_课程表.png

学生选课表：SC

3.2_c_选课表.png

三、数据定义

SQL数据定义功能的操作对象包括：模式、表、视图、索引。

3.3 SQL的数据定义语句.png
其中SQL标准不提供“修改模式定义”和“修改视图定义”的操作，只能先删除再重建。SQL标准也不提供“索引”相关语句，图中最后一行的语句是为了提高查询效率，商用关系数据库管理系统提供的索引机制和语句。

一个关系数据库管理系统可以建立多个数据库，
一个数据库可以建立多个模式，
一个模式通常包括多个表、视图、索引等数据库对象。

1、模式的定义与删除

（1）定义模式

模式定义语句：

1
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;

create schema <模式名> authorization <用户名>;
创建 模式 <模式名> 授权 <用户名>;
（若没有“模式名”，则隐含为“用户名”）

例如，为用户 ZHANG 创建一个模式 TEST

1
CREATE SCHEMA TEST AUTHORIZATION ZHANG;

要创建模式，调用该命令的用户必须拥有数据库管理员权限，或者获得数据库管理员授予的 CREATE SCHEMA 的权限。

（2）删除模式

模式删除语句：

1
DROP SCHEMA <模式名><CASCADE丨RESTRICT>;

drop schema <模式名><cascade丨restrict>;
删除 模式 <模式名><级联丨限制>

其中CASCADE和RESTRICT两者必选其一。
CASCADE（级联）：在删除数据库的同时把该模式中的所有数据库对象全部删除。
RESTRICT（限制）：如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。 仅当该模式中没有任何下属的对象时才能执行DROP SCHEMA语句。

例如，下面语句表示删除了模式 ZHANG，同时由于选择了级联，已经定义的表也会被删除。

1
DROP SCHEMA ZHANG CASCADE;

2、基本表的定义、删除与修改

创建完一个模式，就建立了一个数据库的命名空间，一个框架。在这个空间中首先要定义的是该模式包含的数据库基本表。

（1）定义基本表

基本表定义语句基本格式：

1
2
3
4
CREATE TABLE <表名> (<列名><数据类型>[列级完整性约束条件]
                    [,<列名><数据类型>[列级完整性约束条件]]
                    ...
                    [,<表级完整性约束条件>]);

<表名>：所要定义的基本表的名字
<列名>：组成该表的各个属性（列）
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件
<数据类型>：定义表的各个属性时需要指明其数据类型及长度，以此来保证每一个属性来自一个特定的域。

几种常用的数据类型（请看课本P83~84表格）
定义基本表时，有三种方法定义他所属的模式（请看课本P84）

例如，建立一个“学生”表 Student

1
2
3
4
5
6
7
CREATE TABLE Student 
    (Sno CHAR(9) PRIMARY KEY,       /*列级完整性约束条件，Sno 是主码（primary key）*/
    Sname CHAR(20) UNIQUE,          /*Sname 取唯一值（unique：唯一的）*/
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
    );

例如，建立一个“课程”表 Course

1
2
3
4
5
6
7
8
9
10
CREATE TABLE Course
    (Cno CHAR(4) PRIMARY KEY,       /*列级完整性约束条件，Sno 是主码*/
    Cname CHAR(40) NOT NULL,        /*列级完整性约束条件，Cname 不能取空值*/
    Cpno CHAR(4),                   /*Cpno 的含义是先修课*/
    Ccredit SMALLINT,
    FOREIGN KEY (Cpno) REFERENCES Course(Cno) 
        /*foreign key：外码 ；references：参照*/
        /*表级完整性约束条件，Cpno 是外码，被参照表是 Course，被参照列是 Cno*/
        /*本例说明参照表和被参照表可以是同一个表*/
    );

例如，建立学生选课表 SC

1
2
3
4
5
6
7
8
9
10
CREATE TABLE SC
    (Sno CHAR(9),      
    Cno CHAR(4),                   
    Grade SMALLINT,
    PRIMARY KEY (Sno.Cno),  /*主码由两个属性构成，涉及多个属性时必须作为表级完整性进行定义*/
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
        /*表级完整性约束条件，Sno 是外码，被参照表是 Student，被参照列是 Sno*/
    FOREIGN KEY (Cno) REFERENCES Course(Cno),
        /*表级完整性约束条件，Cno 是外码，被参照表是 Course，被参照列是 Cno*/
    );

（2）修改基本表

基本表修改语句一般格式：

1
2
3
4
5
6
ALTER TABLE <表名>
[ADD [COLUMN] <新列名><数据类型> [完整性约束]]
[ADD <表级完整性约束>]
[DROP [COLUMN] <列名> [CASCADE丨RESTRICT ]] 
[DROP CONSTRAINT<完整性约束名> [CASCADE丨RESTRICT ]]    /*drop constraint：下降约束（机翻）*/
[ALTER COLUMN <列名><数据类型>];                         /*alter column：修改列*/

<表名>：要修改的基本表
ADD子句：增加新列、新的列级完整性约束条件、新的表级完整性约束条件。
DROP CONSTRAINT子句：删除表中的列。
（1）若指定了 CASCADE（级联）短语，则自动删除引用了该列的其他对象，比如视图。
（2）若指定了 RESTRICT（限制）短语，则如果该列被其他对象引用，RDBMS（关系数据库）将拒绝删除该列。
DROP CONSTRAINT子句：删除指定的完整性约束条件。
ALTER COLUMN子句：修改原有的列定义，包括修改列名和数据类型。

例如，向 Student 表增加“入学时间”列，其数据类型为日期型
(不论基本表中原来是否已有数据，新增加的列一律为空值)

1
ALTER TABLE Student ADD S_entrance DATE;

例如，将年龄的数据类型由字符型（假设原来是）改为整数

1
ALTER TABLE Student ALTER COLUMN Sage INT;

例如，增加课程名称必须取唯一值的约束条件

1
ALTER TABLE Course ADD UNIQUE(Cname);

（3）删除基本表

基本表删除语句一般格式：

1
DROP TABLE <表名> [CASCADE丨RESTRICT]

（1）若指定了 CASCADE（级联）短语，则该表的删除没有限制条件。在删除表的同时，相关的依赖对象，比如视图，都将被一起删除。（因此删表时要格外小心）
（2）若指定了 RESTRICT（限制）短语，则表的删除是有限制条件的。要删除的基本表不能被其他表的约束所引用（如 CHECK, FOREIGN KEY 等约束），不能有视图，不能有触发器（trigger），不能有储存过程或函数等。如果存在这些依赖该表的对象，则此表不能被删除。（默认情况是这个）

例如，删除 Student 表

1
DROP TABLE StudentCASCADE;

例如，若表上建有视图，选择RESTRICT(限制)短语时表不能删除；选择CASCADE(级联)短语时可以删除表，视图也会被删除。

1
代码演示在课本P86例题3.12

3、索引的建立与删除

数据库索引有多种类型，常见的包括：

  1. 顺序文件上的索引
  2. B+树索引
  3. 散列索引
  4. 位图索引

（1）建立索引

索引建立语句一般格式：

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

<表名>：是要建索引的基本表的名字
UNIQUE：表名此索引的每一个索引值只对应唯一的数据记录。
CLUSTER：表示要建立的索引是聚簇索引。

例如，为学生-课程数据库中的 Student、Course 和 SC 三个表建立索引。其中 Student 表按学号升序建唯一索引，Course 表按课程号升序建唯一索引，SC 表按学号升序和课程号降序建唯一索引。

1
2
3
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）修改索引

对已经建立的索引重新命名：

1
ALTER INDEX <旧索引名> RENAME TO <新索引名>;

例如，将 SC 表的 SCno 索引名改为SCSno

1
ALTER INDEX SCno RENAME TO SCSno;

（3）删除索引

建立索引是为了减少查询操作的时间，但如果数据增、删、改频繁，系统会花费很多时间来维护索引，从而降低了查询效率。这时可以删除一些不必要的索引。

索引删除语句一般格式：

1
DROP INDEX <索引名>;

例如，删除 Student 表的 Stusname 索引

1
DROP INDEX Stusname;

删除索引时，系统会同时从数据字典中删去有关该索引的描述。

4、数据字典

数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典表中的相应信息。在进行查询优化和查询处理时，数据字典中的信息是其重要依据。

四、数据查询

数据查询是数据库的核心操作。
查询语句有灵活的使用方式和丰富的功能，一般格式为：

1
2
3
4
5
SELECT [ALL丨DISTINGCT] <目标列表达式> [,<目标列表达式>]...
FROM <表名或视图名> [,<表名或视图名>...]丨(SELECTUI语句) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC丨DESC]];

整个 SELECT 语句的含义是，根据 WHERE 子句的条件表达式从 FROM 子句指定的基本表、视图或派生表中找出满足条件的元组，再按 SELECT 子句中的目标列表达式选出元组中的属性值形成结果表。

（1）如果有GROUP BY子句，则将结果按<列名1>的值进行分组，该属性列值相等的一个元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY子句带 HAVING 短语，则只有满足指定条件的组才予以输出。
（2）如果有ORDER BY子句，则结果表还要按<列名2>的值的升序或降序排列。

SELECT 语句既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。下面以学生-课程数据库为例，说明 SELECT 语句的各种用法。

1、单表查询

指仅涉及一个表的查询。

》选择表中的若干列

选择表中的全部或部分列，即关系代数的投影运算。

（1）查询指定列

例如，查询全体学生的学号与姓名

1
2
SELECT Sno,Sname
FROM Student;

例如，查询全体学生的姓名、学号、所在系

1
2
SELECT Sname,Sno,Sdept
FROM Student;

（2）查询全部列

例如，查询全体学生的详细记录

1
2
3
4
5
6
7
SELECT *
FROM Student;

等价于列出所有列名：

SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;

（3）查询经过计算的值

例如，查询全体学生的姓名及其出生年份
用现在的年份（本文写于2021年）减去学生的年龄，即可得到学生的出生年份。

1
2
SELECT Sname,2021-Sage  /*查询结果的第2列是一个算术表达式*/
FROM Student;

例如，查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名。

1
2
SELECT Sname,'Year of Birth:',2014-Sage,LOWER(Sdept)
FROM Student;

我们还可以通过制定别名来改变查询结果的列标题，这对于含算术表达式、常量、函数名的<目标表达式>尤为重要。例如可以把上边这道题如下定义列别名：

1
2
3
SELECT Sname NAME,'Year of Birth:' BIRTH,2014-Sage BIRTHDAY,
       LOWER(Sdept) DEPARTMENT
FROM Student;

》选择表中的若干元组

（1）消除取值重复的行

两个本来并不完全相同的元组在投影到指定的某些列后，可能会变成相同的行。可以用 DISTINCT 消除它们。例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/*我们先查询一下选修了课程的学生学号*/
SELECT Sno
FROM SC;

/*由于一个学生可以选修多个课程，
所以执行上面的语句后，
查询结果里会有许多重复的行。
如果想去掉表中的重复行，
必须指定 DISTINCT*/
SELECT DISTINCT Sno
FROM SC;

/*如果没有指定 DISTINCT 关键词，
则默认为 ALL，
即保留结果表中取值重复的行*/
SELECT Sno
FROM SC;
/*等价于*/
SELECT ALL Sno
FROM SC;

（2）查询满足条件的元组

可以通过 WHERE 子句实现，WHERE 子句常用的查询条件如下表：

查询条件 谓词
比较 =，>，<，>=，<=，!>，!<；NOT+上述比较运算符
确定范围 BETWEED AND，NOT BETWEED AND
确定集合 IN，NOT IN
字符匹配 LIKE，NOT LIKE
空值 IS NULL，IS NOT NULL
多重条件（逻辑运算） AND，OR，NOT

例如，“比较大小”的几道例题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 查询计算机科学系全体学生的名单
SELECT Sname
FROM Student
WHERE Sdept='CS'

-- 查询所有年龄在20岁以下的学生姓名及其年龄
SELECT Sname,Sage
FROM Student
WHERE Sage<20;

-- 查询考试成绩不合格的学生的学号
SELECT DISTINCT Sno     
FROM SC
WHERE Grade<60;
/*这里使用了 DISTINCT 短语，
当一个学生有多门课程不及格，
他的学号也只列一次*/

例如，“确定范围”的几道例题
谓词BETWEED AND和NOT BETWEED AND可以查找属性值（在/不在）某一范围内的元组。其中BETWEED后面是范围的下限（低值）， AND后是范围的上限（高值）。

1
2
3
4
5
6
7
8
9
-- 查询年龄在20~23岁（包括20、23）之间的学生的姓名、系别和年龄。
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;

-- 查询年龄不在20~23岁的学生的姓名、系别和年龄。
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;

例如，“确定集合”的几道例题
通过IN来查找属性值属于指定的集合元素

1
2
3
4
5
6
7
8
9
-- 查询计算机科学系（CS）、数学系（MA）、和信息系（IS）学生的姓名和性别
SELECT Sname,Ssex
FROM Student
WHERE Sdept IN('CS','MA','IS');

-- 查询既不是计算机科学系（CS）、数学系（MA）也不是信息系（IS）学生的姓名和性别
SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN('CS','MA','IS');

例如，“字符匹配”的几道例题
谓词LIKE可以用来进行字符串的匹配

  • %（百分号）代表任意长度的字符串。
    例如a%b表示以a开头，以b结尾的任意长度的字符串
  • _（下横线）代表任意单个字符。例如a_b代表以a开头，以b结尾的长度为3的任意字符串。
    如acb、afb等都满足该匹配串。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- 查询学号为201215121的学生的详细情况
SELECT*
FROM Student
WHERE Sno LIKE'201215121'

/*上面代码也等价于*/
SELECT*
FROM Student
WHERE Sno ='201215121'
/*如果LIKE后面的匹配串中不含通配符，
则可以用=运算符取代LIKE谓词，
用！=或<>运算符取代NOT LIKE 谓词。*/

-- 查询所有姓刘学生的姓名、学号和性别
SELECT Sname,Ssex
FROM Student
WHERE Sname LIKE '刘%';

-- 查询性欧阳且全名为三个汉子的学生的姓名
SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳_';

-- 查询性名中第二个字为阳的学生的姓名和学号
SELECT Sname,Sno
FROM Student
WHERE Sname LIKE '_阳%';

-- 查询所有不姓刘的学生的姓名和学号
SELECT Sname,Sno
FROM Student
WHERE Sname NOT LIKE '刘%';

-- 查询DB_Design课程的课程号和学分
SELECT Cno,Ccredit
FROM Course
WHERE Sname  LIKE 'DB\_Design' ESCAPE'\';   /*ESCAPE’'表示""为换码字符*/

-- 查询以‘DB_’开头，且倒数第三个字符为i的课程详细情况
SELECT *
FROM Course
WHERE Cname LIKE 'DB\_i__' ESCAPE'\';

例如，“涉及空值的查询”的几道例题

1
2
3
4
5
6
7
8
9
-- 查询缺少成绩的学生的学号和相应的课程号
SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;    /*这里的IS不能用=代替*/

-- 查询所有有成绩的学生的学号和相应的课程号
SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;

例如，“多重条件查询”的几道例题
使用 AND 和 OR 解决

1
2
3
4
-- 查询计算机科学系年龄在20岁以下的学生姓名
SELECT Sname
FROM Student
WHERE Sdept='CS' AND Sage<20;

》ORDER BY 子句

我们可以用 ORDER BY 子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认值为升序。

例如，查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列

1
2
3
4
SELECT Sno,Grade
FROM Sc
WHERE Cno='3'
ORDER BY Grade DESC;

例如，查询全体学生情况，查询结果按所在系的系号生序排列，同一系中的学生按年龄降序排列

1
2
3
SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;   /*Sdept可以不写升序（ASC），因为默认值为升序*/

》聚集函数

主要有：

1
2
3
4
5
6
COUNT(*)                            /*统计【元组个数】*/
COUNT( [DISTINCT丨ALL] <列名>)      /*统计一列中【值的个数】*/
SUM( [DISTINCT丨ALL] <列名>)        /*计算一列值的【总和】*/
AVG( [DISTINCT丨ALL] <列名>)        /*计算一列值的【平均值】*/
MAX( [DISTINCT丨ALL] <列名>)        /*求一列值中的【最大值】*/
MIN( [DISTINCT丨ALL] <列名>)        /*求一列值中的【最小值】*/

（1）如果指定 DISTINCT 短语，则表示在计算时取消列中的重复值；
（2）如果不指定，则 ALL 为默认值，则表示不取消重复值。

例如，下面几个例题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 查询学生总人数
SELECT COUNT(*)
FROM Student;

-- 查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno)    /*为了避免重复计算学生人数，必须使用DISTINCT*/
FROM SC;

-- 计算选修1号课程的学生平均成绩
SELECT AVG(Grade)
FROM SC
WHERE Cno='1';

-- 查询选修1号课程的学生最高分数
SELECT MAX(Grade)
FROM SC
WHERE Cno='1';

-- 查询学生201215012选修课程的总学分数
SELECT SUM(Grade)
FROM SC,Course
WHERE Sno='201215012'AND SC.Cno=Course.Cno;

注意：WHERE子句中是不能用聚集函数作为条件表达式的。
聚集函数只能用于SELECT子句和GROUP BY句中的HAVING子句。

》GROUP BY 子句

主要用途：将查询结果按某一列或多列的值分组，值相等的为一组

例如，下面几例题的用法

1
2
3
4
5
6
7
8
9
-- 求各个课程号及相应的选课人数
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno
/*该语句对查询结果按Cno的值分组，
所有具有相同Cno值得元组为一组，
然后对每一个组作用聚集函数COUNT进行计算，以求得该组的学生人数。
若分组后还要按照一定的条件对这些组进行筛选，
最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。*/
1
2
3
4
5
6
7
8
9
-- 查询选修了三门以上课程的学生学号
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;
/*这里先用GROUP BY 子句按Sno进行分组，
再用聚集函数COUNT对每一组计数；
HAVING短语给出了选择组的条件，只有满足条件(即元组个数>3，
表示此学生选修的课超过三门的组才会被选出来*/

WHERE子句和HAVING短语的区别在于作用对象不同：

  • WHERE子句作用于基本表或视图，从中选择满足条件的元组。
  • HAVING短语作用于组，从中选择满足条件的组。
1
2
3
4
5
6
7
8
9
10
11
12
-- 查询平均成绩大于等于90分的学生学号和平均成绩
/*下面的语句是错误的*/
SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno;
/*因为WHERE子句中是不能用聚集函数作为条件表达式的，
正确的查询语句应该是：*/
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
HAVING AVG(Grade)>=90;

2、连接查询

（1）等值与非等值连接查询

（2）自身连接

（3）外连接

（4）多表连接

3、嵌套查询

（1）带有IN谓词的子查询

（2）带有比较运算符的子查询

（3）带有ANY(SOME)或ALL谓词的子查询

（4）带有EXISTS谓词的子查询

4、集合查询

  • 并操作 UNION
  • 交操作 INTERSECT
  • 差操作 EXCEPT

5、基于派生表的查询

6、SELECT语句的一般格式

五、数据更新

1、插入数据

数据插入语句 INSERT 两种形式：

  • 插入一个元组
  • 插入子查询结果（可一次插入多个元组）

例如，下面几个插入元组的例题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 将一个新学生元组(学号：201215128，姓名：陈东，性别：男，所在系：IS，年龄：18岁)插入到Student表中
INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage) /*指出表名以及要赋值的属性名*/
VALUES('201215128','陈东','男','IS',18); /*对新元组的各属性赋值*//*字符串常数要用（英文）单引号括起来*/


-- 将学生张成民的信息插入到Student表中
INSERT
INTO Student    /*没指出属性名，说明要插入所有属性*/
VALUES('201215128','张成民','男',18,'CS');

-- 插入一条选课记录(‘2001215128’,‘1’)
INSERT 
INTO SC(Sno,Cno)
VALUES('201215128','1') /*关系数据库管理系统将在新插入记录Grade上自动赋空值*/
-- 或者：
INSERT 
INTO SC
VALUES('201215128','1',NULL) /*因为没有指出SC的属性名，在Grade列上要明确给出空值*/

例如，下面插入子查询结果的例题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 对每一个系，求学生的平均年龄，并把结果存入数据库

首先在数据库中建立一个新表，其中一列存放系名，另一列存放相应的学生平均年龄

CREATE TABLE Dept_age
		(Sdept CHAR(15)
		 Avg_age SMALLINT
		);

然后对Student表按系分组求平均年龄，再把系名和平均年龄存入新表中

INSERT
INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept;

2、修改数据

修改操作又称为更新操作。

SET子句给出的值用于取代相应的属性列值。

如果省略WHERE子句，则表示要修改表中的所有元组。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
1、修改某一个元组的值

UPDATE Student
SET Sage=22
WHERE Sno='201215121';

2、修改多个元组的值

-- 将所有学生的年龄增加一岁
UPDATE Student
SET Sage=Sage+1;

3、带子查询的修改语句

UPDATE SC
SET Grade=0
WHERE Sno IN
		(SELECT Sno
		 FROM Student
		 WHERE Sdept='CS'
		);

3、删除数据

如果省略WHERE子句，则表示要删除表中的全部元组。但也仅仅只是删除表的数据，不删除表的定义，表的定义仍在字典中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
1、删除某一个元组的值
-- 删除学号为201215128的学生记录

DELETE
FROM Studnt
WHERE Sno='201215128'

2、删除多个元组的值
-- 删除所有学生的选课记录

DELETE
FROM SC;

3、带子查询的删除语句
-- 删除计算机科学系所有学生的选课记录

DELETE
FROM SC
WHERE Son IN
		(SELECT Sno
		 FROM Student
		 WHERE Sdept='CS'
		);

六、空值的处理

所谓空值，就是“不知道”或“不存在”或“无意义”的值，含有不确定性。

1、空值的产生

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 向SC表中插入一个元组，学生号是“201215126”，课程号是“1”，成绩为空
INSERT 
INTO SC(Sno,Cno,Grade)
VALUES('201215126','1',NULL);   /*插入时没有考试成绩，取空值*/
-- 或
INSERT 
INTO SC(Sno,Cno,Grade)
VALUES('201215126','1');   /*插入时没有对成绩赋值，取空值*/

-- 将Student表中学生号为“201215200”的学生所属的系改为空值
UPDATE Student
SET Sdept=NULL
WHERE Sno='201215200';

2、空值的判断

1
2
3
4
-- 从Student表中找出漏填了数据的学生信息
SELECT*
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sdept IS NULL;

3、空值的约束条件

以下情况不能取空值：

  • 属性定义（或域定义）中有 NOT NULL 约束条件的
  • 加了 UNIQUE 限制的属性
  • 码属性不能取空值

4、空值的算术运算、比较运算和逻辑运算

  • 空值与另一个值（包括空值）的算数运算结果为空值
  • 空值与另一个值（包括空值）的比较运算结果为 UNKNOWN

有了 UNKNOWN 后，传统的逻辑运算的二值（TRUE, FALSE）逻辑就扩展成了三值逻辑。

七、视图

1、定义视图

1、建立视图一般格式：
其中WITH CHECK OPTION表示对视图进行 UPDATE、INSERT 和 DELETE 操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。

1
2
3
CREATE VIEW <视图名> [(<列名>[,<列名>]…)]
AS <子查询>
[WITH CHICK OPTION]

例如，下面几个例题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 建立信息系学生的视图
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';

-- 建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系学生
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;

视图还可以建立在多个基本表上

1
2
3
4
5
6
7
8
-- 建立信息系选修了1号课程的学生的视图(包括学号、姓名、成绩)
CREATE VIEW IS_S1(Sno，Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept='IS'AND
			Student.Sno=SC.Sno AND
			SC.Sno='1';

视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上。

1
2
3
4
5
6
-- 建立信息系选修了1号课程且成绩在90分以上的学生的视图
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Sdept>=90;

带虚拟列的视图（带表达式的视图）：

1
2
3
4
5
-- 定义一个反映学生出生年份的视图
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2014-Sage
FROM Student;

分组视图（带有聚集函数和 FROUP BY 子句的查询来定义视图）：

1
2
3
4
5
6
-- 将学生的学号及平均年龄定义为一个视图
CREATE VIEW S_G(Sno，Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;

下面这个例题中，F_Student视图的属性列与Student表的属性列一一对应。如果修改了基本表Student的结构，则F_Student视图与Student表的映像关系就会被破坏，该视图就无法正常工作了。故修改基本表后要先删除原先导出的视图，再重建一次视图。

1
2
3
4
5
6
-- 将Student表中女生记录定义为一个视图
CREATE VIEW F_Student(F_sno,name,sex,age,dept)
AS
SELECT*
FROM Student
WHERE Ssex='女';

2、删除视图的语句格式：

1
DROP VIEW <视图名> [CASCADE];

例如下面例题

1
2
3
4
5
6
7
删除视图BT_S和视图IS_S1

DROP VIEW BT_S;     /*成功执行*/
DROP VIEW IS_S1;    /*拒绝执行*/

-- 由于IS_S1视图上还导出了IS_S2视图，所以需要使用级联删除语句
DROP VIEW IS_S1 CASCADE;

2、查询视图

在信息系学生的视图中找出年龄小于20岁的学生

1
2
3
4
5
6
7
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;
--本例转换后（即视图消解后）的查询语句为：
SELECT Sno,Sage
FROM Student
WHERE Sdept='IS' AND Sage<20;

查询选修了一号课程信息的学生

1
2
3
SELECT IS_Student.Sno,Sname
FROM IS_Student，SC
WHERE IS_Student.Sno=SC.Sno AND SC.Cno='1';

在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

1
2
3
4
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;

3、更新视图

是指通过视图来插入（INSERT）、删除（DELECT）和修改（UPDATE）数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 将信息系学生视图IS_Student中学号为‘201215122’的学生姓名改为刘晨
UPDATE IS_Student
SET Sname='刘晨'
WHERE Sno='201215122'

-- 将信息系学生视图IS_Student中插入一个新的学生记录
INSERT
INTO S_Student
VALUES('201215129','赵信',20,'IS');

-- 删除信息系学生视图IS_Student中学号为‘201215129’的记录
DELETE
FROM IS_Student
WHERE Sno='201215129';

4、视图的作用

  • 视图能够简化用户的操作
  • 视图使用户以多种角度看待同一数据
  • 视图对重构数据库提供了一定程度的逻辑独立性
  • 视图能够对机密数据提供安全保护
  • 适当利用视图可以更清晰地表达查询

八、授权

该内容在课本下一章（第二章 数据库安全性）的P141页第二节（数据库安全性控制）中。

1、授予

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 【例4.1】把Student表的权限授给用户U1
GRANT SELECT
ON TABLE Student
TO U1;

-- 【例4.2】把对Student表和Course表的全部操作权限授予用户U2和U3
GRANT ALL PRIVILEGES //向多个用户传播
ON TABLE Student,Course
TO U2,U3;

-- 【例4.3】把对表SC的查询权限授予所有用户
GRANT SELECT
ON TABLE SC
TO PUBLIC;

-- 【例4.4】把查询Student表和修改学生学号的权限授给用户U4
GRANT UPDATE(Sno),SELECT
ON TABLE Student
TO U4;

-- 【例4.5】把对表SC的INSERT权限授予U5用户，并允许将此权限再授予给其他用户
GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION;

2、收回

1
2
3
4
5
-- 【例4.8】把用户U4修改学生学号的权限收回

REVOKE UPDATE(Sno)
ON TABLE Student
FROM U4;

【本章感想】

  SQL语句这一章的东西好多啊，花了好几天时间才慢慢啃完，中途也因为感觉太长，好几次写的想吐。还有，以后再学其他章节的时候我可能不会像这一章的这篇文章一样写那么详细了，因为效率真的太低了！“所收获的学习效果”远不及“所付出的时间成本”！虽然确实，章节内的逻辑会整理得很清晰，通过博文目录很一目了然，但我的做法无异于把课本内容“搬运”过来啊，那我买纸质书又有何意义？

感谢下方博客为此篇文章写作提供了方便

CSDN - 数据库系统概论期末复习