数据库系统概论 | MySQL | 数据定义 | 单表查询 | 嵌套查询 | 连接查询 | 带有谓词的查询

数据定义

模式的定义与删除

定义模式与删除模式：

CREATE SCHEMA S_C_SC;
DROP SCHEMA S_C_SC;

进入模式：

USE S_C_SC;

建立学生表：

CREATE TABLE Student
(Sno CHAR(8) PRIMARY KEY,
Sname VARCHAR(20) UNIQUE,
Ssex CHAR(6),
Sbirthdate Date,
Smajor VARCHAR(40)
);

CHAR(8)：长度为8的定长字符串

VARCHAR(20)：最大长度为20的变长字符串

DATE：日期，包含年、月、日

PRIMARY KEY：列级完整性约束条件，Sno是主码

UNIQUE：Sname取唯一值

建立课程表：

CREATE TABLE Course
(Cno CHAR(5) PRIMARY KEY,
Cname VARCHAR(40) NOT NULL,
Credit SMALLINT,
Cpno CHAR(5),
FOREIGN KEY (Cpno) REFERENCES Course (Cno)
);

表级完整性约束，Cpno是外码，被参照表是Course，被参照列是Cno

SMALLINT：短整数，2字节

建立选课表：

CREATE TABLE SC
(Sno CHAR(8),
Cno CHAR(5),
Grade SMALLINT,
Semester CHAR(5),
Teachingclass CHAR(8),
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student (Sno),
FOREIGN KEY (Cno) REFERENCES Course (Cno)
);

修改与删除基本表

向Student表增加邮箱地址列Semail，数据类型为字符型：

ALTER TABLE Student ADD Semail VARCHAR(30);

将Student表的出生日期的数据类型由DATE改为字符型：

ALTER TABLE Student MODIFY COLUMN Sbirthdate VARCHAR(20);

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

ALTER TABLE Course ADD UNIQUE (Cname);

删除Student表，选择CASCADE：

DROP TABLE Student CASCADE;

执行CASCADE的删除语句后，不仅表中的数据和此表的定义被删除，而且此表上建立的索引、约束、触发器等对象也被删除。

索引的建立与删除

对Student、Course和SC三个表建立索引，其中Student表按学生姓名升序建唯一索引，Course表按课程名升序建唯一索引，SC表按学号升序和课程号降序建唯一索引（即先按照学号升序，对同一个学号再按课程号降序：

CREATE UNIQUE INDEX Idx_StuSname ON Student (Sname);
CREATE UNIQUE INDEX Idx_CouCname ON Course (Cname);
CREATE UNIQUE INDEX Idx_SCCno ON SC(Sno ASC, Cno DESC);

将SC表的Idx_SCCno索引名改为Idx_SCSnoCno：

ALTER TABLE SC RENAME INDEX Idx_SCCno TO Idx_SCSnoCno;

删除Student表的Idx_StuSname索引：

DROP INDEX Idx_StuSname ON Student;

数据查询

表数据插入

/* student表插入数据*/           
INSERT INTO Student(Sno,Sname,ssex,Sbirthdate,Smajor)
    VALUES ('20180001','李勇','男','2000-3-8','信息安全');
INSERT INTO Student(Sno,Sname,ssex,Sbirthdate,Smajor)
    VALUES ('20180002','刘晨','女','1999-9-1','计算机科学与技术');
INSERT INTO Student(Sno,Sname,ssex,Sbirthdate,Smajor)
    VALUES ('20180003','王敏','女','2001-8-1','计算机科学与技术');
INSERT INTO Student(Sno,Sname,ssex,Sbirthdate,Smajor)
    VALUES ('20180004','张立','男','2000-1-8','计算机科学与技术');
INSERT INTO Student(Sno,Sname,ssex,Sbirthdate,Smajor)
    VALUES ('20180005','陈新奇','男','2000-11-1','信息管理与信息系统');
INSERT INTO Student(Sno,Sname,ssex,Sbirthdate,Smajor)
    VALUES ('20180006','赵明','男','2000-6-12','数据科学与大数据技术');
INSERT INTO Student(Sno,Sname,ssex,Sbirthdate,Smajor)
    VALUES ('20180007','王佳佳','女','2001-12-7','数据科学与大数据技术');

/*course表插入数据*/
INSERT INTO course(cno,cname,Credit,cpno)
    VALUES ('81001','程序设计基础与C语言',4,null);
INSERT INTO course(cno,cname,Credit,cpno)
    VALUES ('81002','数据结构',4,'81001');
INSERT INTO course(cno,cname,Credit,cpno)
    VALUES ('81003','数据库系统概论',4,'81002');
INSERT INTO course(cno,cname,Credit,cpno)
    VALUES ('81004','信息系统概论',4,'81003');
INSERT INTO course(cno,cname,Credit,cpno)
    VALUES ('81005','操作系统',4,'81001');
INSERT INTO course(cno,cname,Credit,cpno)
    VALUES ('81006','python语言',3,'81002');
INSERT INTO course(cno,cname,Credit,cpno)
    VALUES ('81007','离散数学',4,null);
INSERT INTO course(cno,cname,Credit,cpno)
    VALUES ('81008','大数据技术概论',4,'81003');

/*sc表插入数据*/
insert into sc values('20180001','81001',85,'20192','81001-01');
insert into sc values('20180001','81002',96,'20201','81002-01');
insert into sc values('20180001','81003',87,'20202','81003-01');
insert into sc values('20180002','81001',80,'20192','81001-02');
insert into sc values('20180002','81002',98,'20201','81002-01');
insert into sc values('20180002','81003',71,'20202','81003-02');
insert into sc values('20180003','81001',81,'20192','81001-01');
insert into sc values('20180003','81002',76,'20201','81002-02');
insert into sc values('20180004','81001',56,'20192','81001-02');
insert into sc values('20180004','81002',97,'20201','81002-02');
insert into sc values('20180005','81003',68,'20202','81003-01');

单表查询

查询全体学生的详细记录：

SELECT * FROM Student;

图1-1

查全体学生的姓名及其年龄：

SELECT Sname, (EXTRACT(YEAR FROM CURRENT_DATE)-EXTRACT(YEAR FROM Sbirthdate)) "年龄" FROM Student;

图1-2

查询全体学生的姓名、出生日期和主修专业：

SELECT Sname, "Date of Birth:",Sbirthdate, Smajor FROM Student;

图1-3

查询表中的若干元组

查询选修了课程的学生学号：

SELECT ALL Sno FROM SC;
SELECT DISTINCT Sno FROM SC;

图1-4

查询主修计算机科学与技术专业全体学生的姓名：

SELECT Sname FROM Student WHERE Smajor = '计算机科学与技术';

图1-5

查询所有2000年后（包括2000年）出生的学生姓名及其性别：

SELECT Sname FROM Student WHERE EXTRACT(YEAR FROM Sbirthdate) >= 2000;

函数extract(year from Sbirthdate)从出生日期中抽取出年份

图1-6

查询考试成绩不及格的学生的学号：

SELECT Sno FROM SC WHERE Grade < 60;

图1-6

查询年龄在20~23岁（包括20岁和23岁）之间的学生的学生的姓名、出生年月和主修专业：

SELECT Sname,Sbirthdate,Smajor FROM Student WHERE EXTRACT(YEAR FROM CURRENT_DATE)-EXTRACT(YEAR FROM Sbirthdate) NOT BETWEEN 20 AND 23;

图1-7

查询计算机科学与技术专业和信息安全专业学生的姓名和性别：

SELECT Sname,Ssex FROM Student WHERE Smajor IN ('计算机科学与技术', '信息安全');

图1-8

如果查询一个专业，也要加括号：

SELECT Sname,Ssex FROM Student WHERE Smajor IN ('信息安全');

查询学号为20180003的学生的详细情况；

SELECT * FROM Student WHERE Sno LIKE '20180003';

图1-9

查询所有姓刘学生的姓名、学号和性别：

SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '刘%';

图1-10

查询2018级学生的学号和姓名；

SELECT Sno,Sname FROM Student WHERE Sno LIKE '2018%';

图1-11

查询课程号为81开头，最后一位是6的课程名称和课程号：

SELECT Cname,Cno FROM Course WHERE Cno LIKE '81__6';

由于课程号是定长的CHAR(5)，因此在这种开头和结尾限定的查询中，要用_ _来表示。

查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况：

SELECT * FROM Course WHERE Cname LIKE 'DB\\_%i__'  ESCAPE '\\';

在上面这个示例中，DB_的下划线，需要使用\\换码字符将通配符转义为普通字符，并且，倒数第三个字符为i，那么i后面要接两个下划线，由于课程名称是变长字符型，所以中间用%去匹配。

某些学生选修课程后没有参加考试，有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号：

SELECT Sno,Cno FROM SC WHERE Grade IS NULL;

IS不能用=代替

查询主修计算机科学与技术专业2000年（包括2000年）以后出生的学生学号、姓名和性别：

SELECT Sno,Sname,Ssex FROM Student WHERE Smajor IN('计算机科学与技术') AND EXTRACT(YEAR FROM Sbirthdate)>=2000;

图1-12

ORDER BY子句

可以按一个或多个属性列排序升序（ASC）降序（DESC）排列，默认值为升序.对于空值，排序时显示的次序由具体系统实现决定

查询全体学生选修课程情况，查询结果先按照课程号升序排列，同一课程中按成绩降序排列：

SELECT * FROM SC ORDER BY Cno ,Grade DESC;

图1-13

聚集函数

查询选修了课程的学生人数：

SELECT COUNT(DISTINCT Sno) FROM SC;

图1-14

计算选修81001号课程的学生平均成绩：

SELECT AVG(Grade) FROM SC WHERE Cno LIKE '81001';

图1-15

查询选修1号课程的学生最高分数：

SELECT MAX(Grade) FROM SC WHERE Cno LIKE '81001';

图1-16

查询学号为20180003学生选修课程的总学分数：

SELECT SUM(Credit) FROM SC,Course WHERE Sno LIKE '20180001' AND SC.Cno=Course.Cno;

图1-17

GROUP BY子句

按指定的一列或多列值分组，值相等的为一组

如果未对查询结果分组，聚集函数将作用于整个查询结果

分组后，聚集函数将作用于每一个组

求各个课程号及选修该课程的人数：

SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno;

图1-18

查询2019年第2学期选修了10门以上课程的学生学号：

SELECT Sno FROM SC WHERE Semester LIKE '20192' GROUP BY Sno HAVING COUNT(*) > 10;

先求出2019年第2学期选课的所有学生，用GROUP BY子句按Sno进行分组，再用聚集函数COUNT对每一组计数

查询平均成绩大于等于90分的学生学号和平均成绩：

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

HAVING短语与WHERE子句的区别：

WHERE子句作用于基表或视图，从中选择满足条件的元组

HAVING短语作用于组，从中选择满足条件的组

LIMIT子句

用于限制SELECT语句查询结果的（元组）数量

查询选修了数据库系统概论课程的成绩排名前10名的学生学号和成绩：

SELECT Sno, Grade FROM Course, SC WHERE Course.Cname LIKE '数据库系统概论' AND Course.Cno = SC.Cno ORDER BY SC.Grade DESC LIMIT 10;

图1-19

查询平均成绩排名在3-5名的学生学号和平均成绩：

SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno ORDER BY AVG(Grade) DESC LIMIT 5 OFFSET 2;

由于要计算平均成绩所以要用GROUP BY进行分组，并用ORDER BY进行降序，最后用LIMIT取前五名，丢弃前三名，结果就是3~5名。

连接查询

用于同时涉及两个以上的表的查询

等值与非等值连接查询

查询每个学生及其选修课程的情况：

SELECT Student.* , SC.* FROM Student,SC WHERE Student.Sno=SC.Sno;

图1-20

自然连接查询

若在等值连接中把目标列中重复的属性列去掉则为自然连接

查询每个学生的学号、姓名、性别、出生日期、主修专业及该学生选修课程的课程号与成绩：

SELECT Student.Sno,Sname,Sbirthdate,Smajor,Cno,Grade FROM Student,SC WHERE Student.Sno = SC.Sno;

图1-21

Sname，Ssex，Sbirthdate，Smajor，Cno和Grade属性列在Student表与SC表中唯一，引用时可以去掉表名前缀

Sno在两个表都出现，因此SELECT子句和WHERE子句在引用时必须加上表名前缀

复合条件连接查询

查询选修81002号课程且成绩在90分以上的所有学生的学号和姓名：

SELECT Student.Sno,Sname FROM Student,SC WHERE Student.Sno = SC.Sno AND SC.Cno LIKE '81002' AND SC.Grade > 90;

图1-22

自身连接查询

自身连接：一个表与其自己进行连接

需要给表起别名以示区别

由于所有属性名都是同名属性，因此必须使用别名前缀

查询每一门课的间接先修课（即先修课的先修课）：

SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND WHERE FIRST.Cpno=SECOND.Cno AND SECOND.Cpno IS NOT NULL;

图1-23

外连接查询

想以Student表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，则只输出其基本情况的数据，而把选课信息填为空值NULL：

SELECT Student.Sno,Sname,Ssex,Sbirthdate,Smajor,Cno,Grade FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);

图1-24

多表连接查询

查询每个学生的学号、姓名、选修的课程名及成绩：

SELECT Student.Sno,Sname,Cname,Grade FROM Student,Course,SC WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno;

图1-25

嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块

将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

上层的查询块称为外层查询或父查询

下层的查询块称为内层查询或子查询

SQL语言允许多层嵌套查询即一个子查询中还可以嵌套其他

子查询子查询的限制SELECT语句不能使用ORDER BY子句

带有IN谓词的子查询

查询与“刘晨”在同一个主修专业的学生学号、姓名和主修专业：

SELECT Student.Sno,Sname,Smajor FROM Student WHERE Smajor IN (SELECT Smajor FROM Student WHERE Sname LIKE '刘晨');

图1-26

这个示例中的IN换成LIKE也可以

查询选修了课程名为“数据结构”的学生的学号和姓名：

SELECT Sname FROM Student WHERE Sno IN (SELECT SC.Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname LIKE '数据结构'));

图1-27

带有比较运算符的子查询

找出每个学生超过他选修课程平均成绩的课程号：

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

别名允许在同一个查询中多次引用同一个表

图1-28

别写成下面这种代码了：

SELECT Sno,Cno FROM SC WHERE Grade >= (SELECT AVG(Grade)FROM SC);

这是针对整个表进行计算和比较，因此它返回的是成绩高于或等于整个表所有课程的平均成绩的课程

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

>ANY 大于子查询结果中的某个值

>ALL 大于子查询结果中的所有值

<ANY 小于子查询结果中的某个值

<ALL 小于子查询结果中的所有值

$\ge$ANY 大于等于子查询结果中的某个值

$\ge$ALL 大于等于子查询结果中的所有值

$\le$ANY 小于等于子查询结果中的某个值

$\le$ALL 小于等于子查询结果中的所有值

=ANY 等于子查询结果中的某个值

=ALL 等于子查询结果中的所有值（通常没有实际意义）

!=ANY 不等于子查询结果中的某个值

!=ALL 不等于子查询结果中的任何一个值

查询非计算机科学技术专业中比计算机科学技术专业任意一个学生年龄小（出生日期晚）的学生的姓名、出生日期和主修专业：

SELECT Sname,Sbirthdate, Smajor FROM Student WHERE Sbirthdate > ANY (SELECT Sbirthdate FROM Student WHERE Smajor LIKE '计算机科学与技术') AND Smajor != '计算机科学与技术';

图1-29

查询非计算机科学与技术专业中比计算机科学与技术专业所有学生年龄都小（出生日期晚）的学生的姓名及出生日期：

SELECT Sname,Sbirthdate FROM Student WHERE Sbirthdate > ALL (SELECT Sbirthdate FROM Student WHERE Smajor = '计算机科学与技术') AND Smajor != '计算机科学与技术';

图1-30

这里不要写成这样：

SELECT Sname,Sbirthdate FROM Student WHERE Sbirthdate > ALL (SELECT Sbirthdate FROM Student WHERE Smajor = '计算机科学与技术') AND Smajor NOT IN '计算机科学与技术';

NOT IN操作符需要一个列表，在这里传递的是一个单独的字符串

带有EXISTS谓词的子查询

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”

若内层查询结果非空，则外层的WHERE子句返回真值

若内层查询结果为空，则外层的WHERE子句返回假值

由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义

查询没有选修81001号课程的学生姓名：

SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='81001');

图1-31

查询选修了全部课程的学生姓名：

SELECT Sname
FROM Student
WHERE NOT EXISTS
(
    SELECT *
    FROM Course
    WHERE NOT EXISTS
    (
        SELECT *
        FROM SC
        WHERE Sno = Student.Sno AND Cno = Course.Cno
    )
);

查询至少选修了学生20180002选修的全部课程的学生的学号：

SELECT Sno
FROM Student
WHERE NOT EXISTS
(
    SELECT * /* 这是一个相关子查询 */
    FROM SC SCX /* 父查询和子查询均引用了SC表 */
    WHERE SCX.Sno = '20180002'
    AND NOT EXISTS
    (
        SELECT *
        FROM SC SCY /* 用别名SCX、SCY将父查询 */
        WHERE SCY.Sno = Student.Sno
        AND SCY.Cno = SCX.Cno /* 与子查询中的SC表区分开 */
    )
);

图1-32

集合查询

查询计算机科学与技术专业的学生及年龄不大于19岁（包括等于19岁）的学生：

UNION：将多个查询结果合并起来时，系统自动去掉重复元组

UNION ALL：将多个查询结果合并起来时，保留重复元组

SELECT * 
FROM Student 
WHERE Smajor = '计算机科学与技术'
UNION
SELECT * 
FROM Student 
WHERE (extract(year from current_date) - extract(year from Sbirthdate)) <= 19;

图1-33

查询计算机科学与技术专业的学生与年龄不大于19岁的学生的交集：

SELECT *
FROM Student s1
INNER JOIN (
    SELECT *
    FROM Student
    WHERE (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM Sbirthdate)) <= 19
) s2 ON s1.Sno = s2.Sno
WHERE s1.Smajor = '计算机科学与技术';

= Student.Sno
AND SCY.Cno = SCX.Cno /* 与子查询中的SC表区分开 */
)
);

[外链图片转存中...(img-04xHjuUc-1719990528459)]

#### 图1-32

## 集合查询

查询计算机科学与技术专业的学生及年龄不大于19岁（包括等于19岁）的学生：

UNION：将多个查询结果合并起来时，系统自动去掉重复元组 

UNION ALL：将多个查询结果合并起来时，保留重复元组

```sql
SELECT * 
FROM Student 
WHERE Smajor = '计算机科学与技术'
UNION
SELECT * 
FROM Student 
WHERE (extract(year from current_date) - extract(year from Sbirthdate)) <= 19;

[外链图片转存中…(img-T6d6NhOy-1719990528460)]

图1-33

查询计算机科学与技术专业的学生与年龄不大于19岁的学生的交集：

SELECT *
FROM Student s1
INNER JOIN (
    SELECT *
    FROM Student
    WHERE (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM Sbirthdate)) <= 19
) s2 ON s1.Sno = s2.Sno
WHERE s1.Smajor = '计算机科学与技术';