出自《数据库系统概论(第5版)》
目录
1 学生-课程数据库
本文以学生-课程数据库为例来描写SQL基本语句。
为此要定义一个学生-课程模式S-T。学生-课程数据库中包括以下三个表。
- 学生表:Student(Sno,Sname,Ssex,Sage,Sdept)
- 课程表:Course(Cno,Cname,Cpno,Ccredit)
- 学生选课表:SC(Sno,Cno,Grade)
学号 Sno | 姓名 Sname | 性别 Ssex | 年龄 Sage | 所在系 Sdept |
---|---|---|---|---|
201215121 | 李勇 | 男 | 20 | CS |
201215122 | 刘晨 | 女 | 19 | CS |
201215123 | 王敏 | 女 | 18 | MA |
201215125 | 张立 | 男 | 19 | IS |
课程号 Cno | 课程名 Cname | 先行课 Cpno | 学分 Ccredit |
---|---|---|---|
1 | 数据库 | 5 | 4 |
2 | 数学 | 2 | |
3 | 信息系统 | 1 | 4 |
4 | 操作系统 | 6 | 3 |
5 | 数据结构 | 7 | 4 |
6 | 数据处理 | 2 | |
7 | PASCAL语言 | 6 | 4 |
学号 Sno | 课程号 Cno | 成绩 Grade |
---|---|---|
201215121 | 1 | 92 |
201215121 | 2 | 85 |
201215121 | 3 | 88 |
201215122 | 2 | 90 |
201215122 | 3 | 80 |
图1.1 学生-课程数据库的数据示例
2 数据定义
2.1 定义基本表
[ 例 2.1 ] 建立一个“学生”表 Student。
CREATE TABLE Student(
Sno CHAR(9) PRIMARY KEY, /*Sno是主键*/
Sname CHAR(20) UNIQUE, /*Sname具有唯一性*/
Ssex CHAR(2),
Sage SMALLINT, /*SMALLINT短整数(2字节)*/
Sdept CHAR(20)
);
[ 例 2.1 ] 建立一个“课程”表 Course。
CREATE TABLE Course(
Cno CHAR(4) PRIMARY KEY, /*Cno是主键*/
Cname CHAR(40) NOT NULL, /*约束条件:Cname不能为空*/
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
/*Cpno是外键,被参照表是Course,被参照列是Cno*/
);
CREATE TABLE CS(
Sno CHAR(9),
Cno CHAR(4),
Grade SAMLLINT,
PRIMARY KEY (Sno,Cno), /*主键由Sno,Cno这两个属性构成*/
FOREIGN KEY (Sno) REFERENCES Student(Sno), /*Sno是外键,被参照表是Student*/
FOREIGN KEY (Cno) REFERENCES Course(Cno) /*Cno是外键,被参照表是Course*/
);
3 数据查询
3.1 单表查询
3.1.1 查询指定列
[ 例 3.1 ] 查询全体学生的学号和姓名。
SELECT Sname,Sno
FROM Student;
SELECT Sname,Sno,Sdept
FROM Student;
3.1.2 查询全部列
[ 例 3.3 ] 查询全体学生的详细记录。
SELECT * /*查询所有字段*/
FROM Student;
3.1.3 查询经过计算的值
[ 例 3.4 ] 查询全体学生的姓名及其出生年份。
SELECT Sname,2023-Sage
/*查询结果第二列是一个算术表达式,用年份减去学生的年龄得出学生的出生年份*/
FROM Student;
[ 例 3.5 ] 查询全体学生的姓名、出生年份和所在的院系,要求用小写字母表示系名。
SELECT Sname,2023-Sage as BIRTHDAY,LOWER(Sdept) as DEPARTMENT
/*单行处理函数:LOWER转换小写,UPPER转换大写*/
/*使用as关键字起别名。 as关键字也可以省略。
注意:只是将显示的查询结果列名显示为BIRTHDAY和DEPARTMENT*/
FROM Student;
3.1.4 消除取值重复的行
[ 例 3.6 ] 查询选修了课程的学生学号。
SELECT DISTINCT Sno
/*DISTINCT:去掉表中重复的行。
如果没有指定DISTINCT关键字,即默认为ALL,保留结果表中取值重复的行*/
FROM Student;
3.1.5 查询满足条件的元组 —— 比较大小
用于比较的运算符包括 =(等于),>(小于),<(大于) ,>=(大于等于),<=(小于等于),!=或<>(不等于),!>(不大于),!<(不小于)
[ 例 3.7 ] 查询计算机系全体学生的名单。
SELECT Sname
FROM Student
WHERE Sdept = 'CS';
[ 例 3.8 ] 查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT Sname,Sage
FROM Student
WHERE Sage < 20;
[ 例 3.9 ] 查询考试成绩不合格的学生的学号。
SELECT DISTINCT Sno
/*这里使用了DISTINCT关键字,当一个学生有多门课程不及格,他的学号也只列一次*/
FROM SC
WHERE Grade < 60;
3.1.6 查询满足条件的元组 —— 确定范围
[ 例 3.10 ] 查询年龄在20~30岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 30;
/*BETWEEN...AND...用来查找属性值在规定范围内的元组
注意:使用BETWEEN...AND...的时候,必须遵循左小右大。
BETWEEN...AND...是闭区间,包括两端的值。*/
[ 例 3.11 ] 查询年龄不在20~30岁之间的学生的姓名、系别和年龄。
SELECT Sage,Sdept,Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 30;
3.1.7 查询满足条件的元组 —— 确定集合
谓词IN可以用来查找属性值属于指定集合的元组。
[ 例 3.12 ] 查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别。
SELECT Sname,Ssex
FROM Student
WHERE Sdept IN ('CS','MA','IS');
[ 例 3.13 ] 查询既不是计算机科学系(CS)、数学系(MA),也不是信息系(IS)学生的姓名和性别。
SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN ('CS','MA','IS');
3.1.8 查询满足条件的元组 —— 字符匹配
谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下:
[NOT] LIKE '<匹配符>' [ ESCPAE '<换码字符>' ]
<匹配串>可以是一个完整的字符串,也可以含有通配符%和_。其中:
- %(百分号)代表任意长度(长度也可以为0)的字符串。例如 a%b 表示以a开头,以b结尾的任意长度的字符串。如acd、addcg、ab等都满足该匹配串。
- _(下划线)代表任意单个字符。例如a_b表示以a开头,以b结尾的长度为3的任意字符串。如acb、afd都满足该匹配串。
[ 例 3.14 ] 查询学号为201215121的学生的详细情况。
SELECT *
FROM Student
WHERE Sno LIKE '201215121';
等价于
SELECT *
FROM Student
WHERE Sno = '201215121';
/*如果LIKE后面的匹配串不含通配符,则可以用=(等于)运算符取代LIKE谓词,
用!=或<>(不等于)运算符取代NOT LIKE谓词*/
[ 例 3.15 ] 查询所有姓刘的学生、学号和性别。
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE '刘%';
[ 例 3.16 ] 查询姓“欧阳”且全名为三个汉字的学生的姓名。
SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳_';
[ 例 3.17 ] 查询名字中第二个字为“阳”的学生的姓名和学号。
SELECT Sname,Sno
FROM Student
WHERE Sname LIKE '_阳%';
[ 例 3.18 ] 查询所有不姓刘的学生的姓名、学号和性别。
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname NOT LIKE '刘%';
[ 例 3.19 ] 查询DB_Design课程的课程号和学分。
SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE' \ ';
/*如果用户要查询的字符串本身就含有通配符 % 或 _ ,
这时就要使用 ESCAPE '<换码字符>'短语对通配符进行转义了*/
/*ESCAPE'\'表示 “\” 为换码字符。这样匹配串中紧跟在 “\” 后面的字符 “_”
不再具有通配符的含义,转义为普通的 “_” 字符。*/
[ 例 3.20 ] 查询以“DB_”开头,且倒数第三个字符为 i 的课程的详细情况。
SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i_ _' ESCAPE ' \ ';
3.1.9 查询满足条件的元组 —— 涉及空值的查询
[ 例 3.21 ] 某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL; /*分数Grade是空值*/
/*注意:这里的 IS 不能用 = (等号)代替*/
[ 例 3.22 ] 查询所有有成绩的学生学号和课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;
3.1.10 查询满足条件的元组 —— 多重条件查询
逻辑运算符AND和OR可用来连接多个查询条件。AND优先级高于OR。
[ 例 3.23 ] 查询计算机科学系年龄在20岁以下的学生姓名。
SELECT Sname
FROM Student
WHERE Sdept = 'CS' AND Sage < 20;
3.1.11 ORDER BY 子句
用户可以用ORDER BY 子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列,默认值为升序。
[ 例 3.24 ] 查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列。
SELECT Sno, Grade
FROM SC
WHERE Cno = '3'
ORDER BY DESC;
[ 例 3.25 ] 查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT *
FROM Student
ORDER BY Sdept,Sgae DESC; /*ORDER BY 默认值为升序*/
3.1.12 聚集函数
为了进一步方便用户,增强检索功能,SQL提供了许多聚集函数,主要有:
COUNT(*) 统计表当中的总行数。(只要有一行数据count则++)
COUNT( [DISTINCT|ALL <列名>] ) 统计一列下所有不为NULL的值的总数。
SUM( [DISTINCT|ALL <列名>] ) 求一列值的总和
AVG( [DISTINCT|ALL <列名>] ) 求一列值的平均值
MAX( [DISTINCT|ALL <列名>] ) 求一列值中的最大值
MIN( [DISTINCT|ALL <列名>] ) 求一列值中的最小值
如果指定DISTINCT关键字,则表示在计算时要取消指定列中的重复值。
如果不指定DISTINCT关键字或者指定ALL关键字(ALL为默认值),则表示不取消重复值。
[ 例 3.26 ] 查询学生总人数。
SELECT COUNT(*)
FROM Student;
[ 例 3.27 ] 查询选修了课程的学生人数。
SELECT COUNT(DISTINCT Sno)
FROM SC;
[ 例 3.28 ] 查询选修1号课程的学生平均成绩。
SELECT AVG(Grade)
FROM SC
WHERE Cno = '1';
[ 例 3.29 ] 查询选修1号课程的学生最高成绩。
SELECT MAX(Grade)
FROM SC
WHERE Cno = '1';
[ 例 3.30 ] 查询学生201215012选修课程的总学分数。
SELECT SUM(Ccredit)
FROM SC,Course
WHERE Sno = '201215012' AND SC.Cno = Course.Cno;
/*聚集函数只能用于 SELECT 子句和 GROUP BY 中的 HAVING 子句
3.1.13 GROUP BY 子句
GROUP BY子句的目的是细化聚集函数的作用对象。分组后聚集函数将作用于每一组。
[ 例 3.31 ] 求各个课程号以及相应的选课人数。
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;
/*该语句对查询结果按Cno的值进行分组,使具有相同Cno值的元组为一组,
然后对每一组作用聚集函数COUNT进行计算,以求得改组的学生人数。*/
[ 例 3.32 ] 查询选修了三门以上课程的学生学号。
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) > 3;
/*先用GROUP BY子句按 Sno进行分组,再用聚集函数COUNT对每一组计数。
HAVING给出了选择组的条件(此学生选修的课超过3门),只有满足条件的组才会被选出来。*/
[ 例 3.33 ] 查询平均成绩大于等于90分的学生学号和平均成绩。
下面的语句是不对的:
SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade) >= 90
/*该语句错误,因为WHERE子句中不能用聚集函数作为条件表达式*/
GROUP BY Sno;
正确的语句如下:
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >= 90;
/*WHERE子句和HAVING短语的区别在于作用对象不同。
WHERE子句作用于基本表或视图,从中选择满足条件的元组。
HAVING短语作用于组,从中选择满足条件的组。*/
3.2 连接查询
前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表,则称之为连接查询。连接查询包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询等。
3.2.1 等值与非等值连接查询
连接查询的WHERE子句中用来连接两个表的条件成为连接条件或连接谓词。
当连接运算符为 = 时,成为等值连接。使用其他运算符称为非等值连接。
[ 例 3.34 ] 查询每个学生及其选修课程的情况。
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Cno = SC.Cno; /*将Student与SC中同一学生的元组连接起来*/
假设Student表、SC表的数据如图1.1所示,该查询的执行结果如图3.1所示。
图3.1 例3.34查询结果
Student.Sno | Sname | Ssex | Sage | Sdept | SC.Sno | Cno | Grade |
201215121 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 |
201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 |
201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 |
201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 2 | 90 |
201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 3 | 80 |
[ 例 3.35 ] 对 [ 例 3.34 ] 用自然连接完成。
SELECT Student.Sno,Sname,Ssex,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Cno = SC.Cno;
/*若在等值连接中把目标列中重复的属性列去掉则为自然连接。*/
/*由于Sname,Ssex,Sage,Sdept,Cno和Grade属性列在Student表与SC表中是唯一的,
因此引用时可以去掉表前缀;而Sno在两个表都出现了,因此引用时必须加上表名前缀。*/
[ 例 3.36 ] 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。
SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno = SC.Sno AND /*连接谓词*/
SC.Cno = '2' AND SC.Grade > 90; /*其他限定条件*/
3.2.2 自身连接
连接操作不仅可以在两个表之间进行,也可以是一个与其自己连接,称为表的自身连接。
[ 例 3.37 ] 查询每一门课的间接先修课(即先修课的先修课)。
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND /*为Course取两个别名,一个为FIRST,一个为SECOND*/
WHERE FIRST.Cpno = SECOND.Cno;
/* 在Course表中只有每门课的直接先修课信息,没有先修课的先修课。
要得到这个信息,必须先对一门课找到其先修课,再按此先修课的课程号查找它的先修课。
这就要将Course与其自身连接。*/
3.2.3 外连接
在外连接当中,两张表连接,产生了主次关系。
- LEFT OUTER JOIN...ON...:左外连接,表示将JOIN关键字左边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询右边的表。
- RIGHT OUTER JOIN...ON...:右外连接,表示将JOIN关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表。
[ 例 3.38 ] 对 [ 例 3.34 ] 用外连接完成,要求以Student表为主体列出每个学生的基本情况及其选课情况。
SELECT Student.Sno,Sname,Ssex,Sdept,Cno,Grade
FROM Student
LEFT OUTER JOIN SC /*OUTER可省略*/
ON (Student.Sno = SC.Sno);
3.2.4 多表连接
两个表以上的连接,称为多表连接。
[ 例 3.39 ] 查询每个学生的学号、姓名、选修的课程名及成绩。
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
/*执行方式是:先将Student表与SC表进行连接,
得到每个学生的学号、姓名、所选课程号和相应的成绩,
然后再将其余Course表进行连接,得到最终结果。*/
3.3 嵌套查询
3.3.1 带有IN谓词的子查询
[ 例 3.40 ] 查询与“刘晨”在同一个系学习的学生。
先分步来完成此查询,然后再构造嵌套查询。
①确定“刘晨”所在系名
SELECT Sdept
FROM Student
WHERE Sname = '刘晨';
②查找所有在CS系学习的学生
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept = 'CS'
③将第一步查询嵌入到第二步查询的条件中,嵌套查询如下
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname = '刘晨';)
[ 例 3.41 ] 查询选修了课程名为“信息系统”的学生学号和姓名。
SELECT Sno,Sname /*3.最后在Student关系中*/
FROM Student /*取出Sno和Sname*/
WHERE Sno IN
(SELECT Sno /*2.然后再SC关系中找出*/
FROM SC /*选修了3号课程的学生学号*/
WHERE Cno IN
(SELECT Cno /*1.首先在Course关系中找出*/
FROM Course /*“信息系统”的课程号,结果为3号*/
WHERE Cname = '信息系统'
)
);
3.3.2 带有比较运算符的子查询
[ 例 3.42 ] 找出每个学生超过他自己选修课程平均成绩的课程号。
SELECT Sno,Cno
FROM SC x /*x是SC的别名*/
WHERE Grade >
(SELECT AVG(Grade) /*某学生的平均成绩*/
FROM SC y
WHERE y.Sno = x.Sno);
3.3.3 带有ANY(SOME)或ALL谓词的子查询
子查询返回单值可以使用比较运算符,但返回多值要用ANY(有的系统使用SOME)或ALL谓词修饰符。而使用ANY或ALL谓词时必须同时使用比较运算符。如下:
>ANY 大于子查询结果中的某个值
<ANY 小于子查询结果中的某个值
>=ANY 大于等于子查询结果中的某个值
<=ANY 小于等于子查询结果中的某个值
=ANY 等于子查询结果中的某个值
!=(或<>)ANY 不等于子查询结果中的某个值
>ALL 大于子查询结果中的所有值
<ALL 小于子查询结果中的所有值
>=ALL 大于等于子查询结果中的所有值
<=ALL 小于等于子查询结果中的所有值
=ALL 等于子查询结果中的所有值
!=(或<>)ALL 不等于子查询结果中的任何一个值
[ 例 3.43 ] 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。
SELECT Sname,Sage
FROM Student
WHERE Sage < ANY
(SELECT Sage
FROM Student
WHERE Sdept = 'CS';)
AND Sdept <> 'CS'; /*条件:非计算机科学系*/
[ 例 3.44 ] 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名和年龄。
SELECT Sname,Sage
FROM Student
WHERE Sage < ALL
(SELECT Sage
FROM Studet
WHERE Sdept = 'CS')
AND Sdept <> 'CS';
3.3.4 带有EXISTS谓词的子查询
EXISTS代表存在量词彐。带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。
[ 例 3.45 ] 查询所有选修了1号课程的学生姓名。
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno = '1');
/*由EXISTS引出的子查询,其目标列表达式通常都用 * ,
因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义。*/
[ 例 3.46 ] 查询没有选修1号课程的学生姓名。
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno = '1');
[ 例 3.47 ] 查询选修了全部课程的学生姓名。
SQL中没有全称量词,但是可以把带有全称量词的谓词转换成等价的带有存在量词的谓词。
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno = Student.Sno
AND Cno = Course.Cno
)
); /*由于没有全称量词,将题目转化为:查询这样的学生:没有一门课程是他不选修的。*/
[ 例 3.48 ] 查询至少选修了学生201215122选修的全部课程的学生号码。
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
(SELECT *
FROM SC SCY
WHERE SCY.Sno = ‘201215122’ AND
NOT EXISTS
(SELECCT *
FROM SC SCZ
WHERE SCZ.Sno = SCX.Sno AND
SCZ.Cno= SCY.Cno
)
); /*本题的语义:不存在这样的课程y,学生201215122选修了y,而学生x没有选。*/
3.4 集合查询
集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXPECT。
[ 例 3.49 ] 查询计算机科学系的学生及年龄不大于19岁的学生。
SELECT *
FROM Student
WHERE Sdept = 'CS'
UNION
SELECT *
FROM Student
WHERE Sage <= 19;
[ 例 3.50 ] 查询选修了课程1或者选修了课程2的学生。
SELECT *
FROM Student
WHERE Cno = '1'
UNION /*查询集合的并集*/
SELECT *
FROM Student
WHERE Cno = '2'
[ 例 3.51 ] 查询既选修了课程1又选修了课程2的学生。
SELECT *
FROM Student
WHERE Cno = '1'
INTERSECT /*查询集合的交集*/
SELECT *
FROM Student
WHERE Cno = '2'
[ 例 3.52 ] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。
SELECT *
FROM Student
WHERE Sdept = 'CS'
EXCEPT /*查询集合的差集*/
SELECT *
FROM Student
WHERE Sage <= 19;
4 数据更新
4.1 插入数据
4.1.1 插入元组
[ 例 4.1 ] 将一个新学生元组(学号:201215128,姓名:陈冬,性别:男,所在系:IS,年龄:18岁)插入到Student表中。
INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage) /*INTO指出了表明Student*/
VALUES('201215128','陈冬','男','IS',18); /*VALUES对新元组各属性赋值*/
[ 例 4.2 ] 将一个学生张成民的信息插入到Student表中。
INSERT
INTO Student
VALUES('201215126','张成民','男',18,'CS');
/*与上面的例子不同的是在INTO子句中只指出了表名,
没有指出属性名。因此属性列的次序要与CREATE TABLE中的次序一样。*/
[ 例 4.3 ] 插入一条选课记录('201215128','1')。
INSERT
INTO SC(Sno,Cno)
VALUES('201215128','1');
或者
INSERT
INTO SC
VALUES('201215128','1',NULL);
4.1.2 插入子查询结果
[ 例 4.4 ] 对每一个系,求学生的平均年龄,并把结果存入数据库。
①首先在数据库中建立一个新表,其中一列存放系名,另一列存放相应的学生平均年龄。
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;
4.2 修改数据
4.2.1 修改某一个元组的值
[ 例 4.5 ] 将学生201215121的年龄改为22岁。
UPDATE Student
SET Sage = 22
WHERE Sno = '201215121';
4.2.2 修改多个元组的值
[ 例 4.6 ] 将所有学生的年龄增加1岁。
UPDATE Student
SET Sage = Sage + 1;
4.2.3 带子查询的修改语句
[ 例 4.7 ] 将计算机科学系全体学生的成绩置零。
UPDATE SC
SET Grade = 0
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept = 'CS');
4.3 删除数据
DELETE删除的是表中的数据,而不是关于表的定义。
4.3.1 删除某一个元组的值
[ 例 4.8 ] 删除学号为201215128的学生记录。
DELETE
FROM Student
WHERE Sno = '201215128';
4.3.2 删除多个元组的值
[ 例 4.9 ] 删除所有的学生选课记录。
DELETE
FROM SC;
4.3.3 带子查询的删除语句
[ 例 4.10 ] 删除计算机科学系所有学生的选课记录。
DELETE
FROM SC
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept = 'CS')
5 空值的处理
SQL语言中允许某些元组的某些属性在一定情况下取空值。一般有以下几种情况:
- 该属性应该有一个值,但目前不知道它的具体值。
- 该属性不应该有值。
- 由于某种原因不便于填写。
因此,空值是一个很特殊的值,含有不确定性,对关系运算带来特殊的问题,需要做特殊的处理。
5.1 空值的产生
[ 例 5.1 ] 向SC表中插入一个元组,学号是“201215126”,课程号是“1”,成绩为空。
INSERT
INTO SC(Sno,Cno,Grade)
VALUES('201215126','1',NULL); /*在插入时该学生还没有考试成绩,成绩为空。*/
[ 例 5.2 ] 将Student表中学生号为“201215200”的学生所属的系改为空值。
UPDATE Student
SET Sdept = NULL
WHERE Sno = '201215200';
5.2 空值的判断
[ 例 5.3 ] 从Student表中找出漏填了数据的学生信息。
SELECT *
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;
5.3 空值的约束条件
属性定义中有NOT NULL 约束条件的不能取空值,
加了UNIQUE限制的属性不能取空值,
码属性不能取空值
5.4 空值的算数运算、比较运算和逻辑运算
[ 例 5.4 ] 找出选修1号课程的不及格的学生。
SELECT Sno
FROM SC
WHERE Grade < 60 AND Cno = '1';
[ 例 5.5 ] 选出选修1号课程的不及格的学生以及缺考的学生。
SELECT Sno
FROM SC
WHERE Cno = '1' AND (Grade < 60 OR Grade IS NULL);
6 视图
视图是从一个或几个基本表(或视图)导出的表。它与基本表不同,是一个虚表。
数据库中只存放视图的定义,而不存放视图对应的数据,这些数据仍存放在原来的基本表中。
所以一旦基本表中的数据发生变化,从视图中查询出的数据也就随之改变了。
从这意义上说,视图像是一个窗口,透过它可以看到数据库中自己感兴趣的数据及其变化。
视图一旦定义,就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图,但对视图的更新操作则有一定的限制。
6.1 定义视图
6.1.1 建立视图
SQL语言用CREATE VIEW命令建立视图,其一般格式为:
CREATE VIEW <视图名>
AS <子查询>
......
[ 例 6.1 ] 建立信息系学生的视图。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept = 'IS';
[ 例 6.2 ] 建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept = 'IS'
WITH CHECK OPTION;
/*加上WITH CHECK OPTION子句,以后对该视图进行插入、修改和删除操作时,
关系数据库管理系统会自动加上Sdept = 'IS'的条件*/
[ 例 6.3 ] 建立信息系选修了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.Cno = '1';
[ 例 6.4 ] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade >= 90; /*这里的视图就是建立在视图IS_S1之上的。*/
[ 例 6.5 ] 定义一个反映学生出生年份的视图。
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS /*这里的BT_S视图是一个带有表达式的视图。视图中的出生年份值是通过计算得到的。*/
SELECT Sno,Sname,2023-Sage
FROM Student;
[ 例 6.6 ] 将学生的学号及平均成绩定义为一个视图。
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
/*由于AS子句中SELECT语句的目标列平均成绩是通过作用聚集函数得到的,
所以CREATE VIEW中必须明确定义组成S_G视图的各个属性名。S_G是一个分组函数。*/
[ 例 6.7 ] 将Student表中所有女生记录定义为一个视图。
CREATE VIEW F_Student(F_sno,name,sex,age,dept)
AS
SELECT *
FROM Student
WHERE Ssex = '女';
6.1.2 删除视图
该语句的格式:DROP VIEW <视图> [CASCADE]
视图删除后视图的定义将从数据字典中删除。如果该视图上还导出了其他视图,则使用CASCADE级联删除语句把该视图和由它导出的所有视图一起删除。
[ 例 6.8 ] 删除视图BT_S和视图IS_S1。
下面的语句是不对的:
DROP VIEW BT_S; /*成功执行*/
DROP VIEW IS_S1; /*拒绝执行*/
正确的语句如下:
DROP VIEW IS_SI CASCADE; /*删除了视图IS_S1和由它导出的所有视图。*/
6.2 查询视图
视图定义后,用户就可以向对基本表一样对视图进行查询了。
[ 例 6.9 ] 在信息系学生的视图中找出年龄小于20岁的学生。
SELECT Sno,Sage
FROM Student
WHERE Sdept = 'IS' AND Sage < 20;
/*关系数据库管理系统执行对视图的查询时,首先进行有效性检查,
检查查询中涉及的表、视图是否存在。*/
[ 例 6.10 ] 查询选修了1号课程的信息系学生。
SELECT IS_Student.Sno,Sname
FROM IS_Student,SC
WHERE IS_Student.Sno = SC.Sno AND SC.Cno = '1';
[ 例 6.11 ] 在S_G视图(例6.6中定义的视图)中查询平均成绩在90分以上的学生的学号和平均成绩。
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >= 90;
6.3 更新视图
更新视图是指通过视图来插入(INSERT)、删除(DELETE)和修改(UPDATE)数据。
由于视图是不实际存储数据的虚表,因此对视图的更新最终要转换为对基本表的更新。
为防止用户通过视图对数据进行增加、删除、修改时,有意无意地对不属于视图范围内的基本表数据进行操作,可在定义视图时加上WITH CHECK OPTION子句。
这样在视图上增删改数据时,关系数据库管理系统会检查视图定义条件,若不满足条件则拒绝执行该操作。
[ 例 6.11 ] 将信息系学生视图IS_Student中学号为“201215122”的学生姓名改为“刘辰”。
UPDATE IS_Student
SET Sname = '刘辰'
WHERE Sno = '201215122';
转换后的更新语句为
UPDATE Student
SET Sname = '刘辰'
WHERE Sno = '201215122' AND Sdept = 'IS';
[ 例 6.12 ] 将信息系学生视图IS_Student中插入一个新的学生记录,其中学号为“201215129”,姓名为“赵新”,年龄为20岁。
INSERT
INTO IS_Student
VALUES('201215129','赵新',20);
转换后的更新语句为
INSERT
INTO Student(Son,Sname,,Sage,Sdept)
VALUES('201215129','赵新',20,'IS');
[ 例 6.13 ] 删除信息系学生视图IS_Student中学号为“201215129”的记录。
DELETE
FROM IS_Student
WHERE Sno = '201215129';
转换后的更新语句为
DELETE
FROM Student
WHERE Sno = '201215129' AND Sdept = 'IS';
7 SQL语句执行顺序总结
SELECT ...
FROM ...
WHERE...
GROUP BY...
HAVING...
ORDER BY...
执行顺序是:
1.FROM 2.WHERE 3.GROUP BY 4.HAVING 5.SELECT 6.ORDER BY