文章目录

一. 数据表的创建、删除、修改与查询

数据库语法(上)

一. 数据表的创建、删除、修改与查询

$[]$ 种的内容可选， $<>$ 中的参数必选

1. 创建表

create table 表名(
            列名1 数据类型 [约束条件]
            [,列名2 数据类型 [约束条件]]
                ...
            表级约束条件);

2. 表的修改

新增一列

alter table 表名 add 列名 数据类型 [约束条件];
alter table student add semail varchar(20);

添加主键

alter table 表名 add primary key(列名);

添加外键

ALTER table 表名 add foreign key(列名) REFERENCES 表名(列名);

修改列名称

alter table 表名 change 旧列名 新列名 数据类型;
alter table student change semail se1 varchar(20);

修改列的数据类型

alter table 表名 modify 列名 新数据类型;    
alter table student modify sel char(20);

修改字符集

alter table 表名 modify 列名 数据类型 character set gbk/utf8;

处理字符乱码错误
```
set names gbk/utf8;
```

删除列

alter table 表名 drop 列名;
alter table student drop sel;

删除主键
```
alter table 表名 drop primary key;
```

删除外键

alter table 表名 drop foreign key 外键名;

表的删除

drop table 表名;

3. 索引的创建与删除

创建索引

create [unique] index 索引名 on 表名(列名 次序 [,列名 次序]...);
create INDEX s2 on student(sbirthdate asc,smajor desc);

或者：

alter table 表名 add index 表名(列名 次序);
alter table student add index student(sbirth desc);

查看索引

show index from 表名;
show index from student;

修改索引

5.7 版本之前先删除后新建

5.7 版本之后：

alter table student rename index 旧索引名 to 新索引名;
alter table student rename index s2 to s3;

删除索引

drop index 索引名 on 表名;
alter table 表名 drop index 索引名;

删除外键

外键没有起别名，需要先查询创表语句查看外键的默认名

SHOW CREATE TABLE purchase #查看创建表语句

结果如下：

 CREATE TABLE purchase (
  purchase_id int NOT NULL AUTO_INCREMENT,
  pquantity int NOT NULL,
  pdate date NOT NULL,
  buyer_id int DEFAULT NULL,
  provider_id int DEFAULT NULL,
  commodity_id int DEFAULT NULL,
  PRIMARY KEY (purchase_id),
  KEY provider_id (provider_id),
  KEY commodity_id (commodity_id),
  KEY buyer_id (buyer_id),
  CONSTRAINT purchase_ibfk_1 FOREIGN KEY (buyer_id) REFERENCES buyer (buyer_id),
  CONSTRAINT purchase_ibfk_2 FOREIGN KEY (provider_id) REFERENCES provider (provider_id),
  CONSTRAINT purchase_ibfk_3 FOREIGN KEY (commodity_id) REFERENCES commodity (commodity_id),
  CONSTRAINT purchase_ibfk_4 FOREIGN KEY (buyer_id) REFERENCES buyer (buyer_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8015 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

可以看到想删除的外键buyer_id有个默认名purchase_ibfk_1。如果想删除这个外键用下面语句：

ALTER TABLE purchase DROP FOREIGN KEY purchase_ibfk_1

4. 数据更新

首先创建三个表并插入数据

student表：学生表；属性：sno、sname、ssex、sbirthdate、smajor

course表：课程表；属性：cno、cname、cpno、ccredit(学分)

sc表：学生选课表；属性：sno、cno、grade、Semester、Teachingclass

#学生表
CREATE TABLE Student          
(Sno   CHAR(8) PRIMARY KEY, 
 Sname VARCHAR(20) UNIQUE,            
 Ssex  CHAR(6),
 Sbirthdate   date,
 Smajor  varCHAR(40)
); 

#课程表
create table course
(Cno CHAR(5) PRIMARY KEY,
 Cname CHAR(40) not null,
 Cpno  CHAR(5),
 Ccredit SMALLINT,
 FOREIGN KEY (Cpno) REFERENCES Course(Cno)
); 

#学生选课表
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)
);

#数据更新
insert into student values('20180001','李勇','男','2000-3-8','信息安全');
insert into student values('20180002','刘晨','女','1999-9-1','计算机科学与技术');
insert into student values('20180003','王敏','女','2001-8-1','计算机科学与技术');
insert into student values('20180004','张立','男','2000-1-8','计算机科学与技术');
insert into student values('20180005','陈新奇','男','2001-11-1','信息管理与信息系统');
insert into student values('20180006','赵明','男','2000-6-12','数据科学与大数据技术');
insert into student values('20180007','王佳佳','女','2001-12-7','数据科学与大数据技术');

insert into course(cno,cname,ccredit,cpno) values('81001','程序设计基础与C语言',4,NULL);
insert into course(cno,cname,ccredit,cpno) values('81007','离散数学',4,NULL);
insert into course(cno,cname,ccredit,cpno) values('81002','数据结构',4,'81001');
insert into course(cno,cname,ccredit,cpno) values('81005','操作系统',4,'81001');
insert into course(cno,cname,ccredit,cpno) values('81003','数据库系统概论',4,'81002');
insert into course(cno,cname,ccredit,cpno) values('81006','Python语言',3,'81002');
insert into course(cno,cname,ccredit,cpno) values('81004','信息系统概论',4,'81003');
insert into course(cno,cname,ccredit,cpno) values('81008','大数据技术概论',4,'81003');

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');

插入数据

sql插入语句

insert into 表名[(列名1...)]  VALUES(常量1....);
insert into student values
        ('20180009','张三','男','2000-10-1','软件工程');
insert into student(sno,sname,ssex,sbirthdate,smajor) values
        ('20180010','李红','女','2005-10-1','软件工程');
insert into student(sname,sno) values('王五','20180011'),('20180010','李红','女','2005-10-1','软件工程');

插入子查询的值，子查询：以查询到的结果集合作为插入条件

insert into 表名[(列名1...)] select 子句;
insert into xs select sno from student; #将student表数据插入到sx表中
#insert into xs(sno) select sno from student;

修改数据

update 表名 set 列名1=值|表达式.... [where 条件];
update student set smajor='软件工程',ssex='男' where sno='20180011';

例题1：将计算机科学与技术专业的学生成绩置 $0$ ；

update sc set grade=0 where sno IN
        (SELECT sno from student where smajor='计算机科学与技术');

例题2：将选修数据结构的计算机科学与技术专业的同学的生日都改成 $2023-10-8$ ；

update student set sbirthdate='2023-10-8' where smajor='计算机科学与技术'
        and sno in(SELECT sno from sc where cno IN
                (SELECT cno from course where cname='数据结构'));

删除数据

delete from 表名 [where 条件];
delete from student; 
#等价于:
truncate table student;

例题1：删除计算机科学与技术专业所有学生的选课记录；

delete from sc where sno in(select sno from student where smajor='计算机科学与技术');

例题2：删除数据库系统概论的选课信息；

delete from sc where cno in(select cno from course where cname='数据库系统概论');

5. 数据查询

三张表同上

5.1 单表查询

选择指定列
```
select sno,sname from student;
```

选择所有列

select sno,sname,ssex,sbirthdate,smajor from student;
select * from student;

查询经过计算的值，查询所有学生的学号，姓名，年龄：

#根据学生出生年月计算学生年龄
select sno,sname,year(now())-year(sbirthdate) from student;

5.2 选择表中的若干元组

消除重复行

distinct：消除重复行

#查询选课的学生学号
select distinct sno from sc;

查询满足条件的元组

查询满足指定条件的元组通过where字句实现。where字句常用的查询条件如下：

查询条件	关键字
比较	$=,>,<,>=,<=,!=,<>,!>,!<;$ `NOT`+上述运算符
确定范围	`BETWEEN AND`、`NOT BETWEEN AND`
确定集合	`IN`、`NOT IN`
字符匹配	`LIKE`、`NOT LIKE`
空值	`IS NULL`、`IS NOT NULL`
多重条件(逻辑运算)	`AND`、`OR`、`NOT`

查询计算机科学与技术专业年龄大于22岁的学生的学号，姓名，年龄；

select sno,sname,year(now())-year(sbirthdate) from student 
    where smajor="计算机科学与技术" and year(now())-year(sbirthdate)>22;

[not] between....and....

查询成绩是良好（80-90）的学生的学号，成绩；

select sno,grade from sc where grade between 80 and 90;
#不在80~90之间的学生
select sno,grade from sc where grade not between 80 and 90;

确定集合：in

查询计算机科学与技术和信息安全专业的同学的信息；

select * from student where smajor in ('计算机科学与技术','信息安全');
#等价于：
select * from student where smajor='计算机科学与技术' or smajor='信息安全';

字符匹配：关键字like

符号功能

_ 任意单个字符

% 任意多个字符

escape 声明一个转义字符
```
select * from student where sname like '刘%';     #查询学生姓刘的学生，任意多个字符：刘某某
select * from student where sname like '王%';   #查询学生姓王的学生，单个字符：王
```
查询开头三DB_的课程信息：
```
select * from course where sname like 'DB#_%' escape '#';
```
#被escape声明变为转义字符，#后面的第一个字符将变为普通的字符使用，不再有通配符意义。注意\不能声明为转义字符

符号	功能
`_`	任意单个字符
`%`	任意多个字符
`escape`	声明一个转义字符

空值判断：is null、is not null

查询先修课cpno为空的课程信息：

select * from course where cpno is null;
#不为空的课程信息
select * from course where cpno is not null;

多重条件查询：and、or、not

查询成绩在90分以上的学分为4的课程信息：

select * from course where ccredit=4 and cno in(select cno from sc where grade>90);

查询成绩在90分以上的计算机科学与技术专业的男生同学的信息：

select * from student where smajor='计算机科学与技术' 
        and ssex='男' and sno in(select sno from sc where grade>90);

查询数据结构和程序设计基础与c语言的选课成绩：

SELECT Grade FROM sc WHERE Cno IN
    (SELECT Cno FROM course WHERE cname in('数据结构','程序设计基础与c语言'));

ORDER BY排序

ORDER BY后面跟列名[表达式]，参数asc升序(默认)，desc降序

在学生表中，将学生的信息按照年龄的升序排列

select * from student ORDER BY year(now())-year(sbirthdate) asc;
select * from student ORDER BY sbirthdate desc;         #运行结果同上

将计算机科学与技术的同学按照其选课的平均成绩进行降序排列：

SELECT sno, avg(grade) FROM sc where sno in 
    (SELECT sno from student where smajor='计算机科学与技术') GROUP BY sno ORDER BY avg(grade) desc;
select * from student where smajor='计算机科学与技术' 
    order by (select avg(grade) from sc where student.sno=sc.sno GROUP BY sno) desc;

聚集函数

SQL提供了许多聚集函数，主要有：

函数名	作用
COUNT(*)	统计元组个数
COUNT([DISTINCT\|ALL]<列名>)	统计一列中值的个数
SUM([DISTINCT\|ALL]<列名>)	计算一列值得总和(此列为数值型)
AVG([DISTINCT\|ALL]<列名>)	计算一列值得平均值
MAX([DISTINCT\|ALL]<列名>)	求一列值中的最大值
MIN([DISTINCT\|ALL]<列名>)	求一列值中的最小值

#查询学生来自几个专业
select count(DISTINCT smajor) 专业数目 from student;
#查询选课的学生人数
select count(DISTINCT sno) 选课人数 from sc;
#查询学生选课成绩的最大值和最小值
select MAX(grade),MIN(grade) from sc;

limit子句

limit m查询出 $m$ 个元组

limit m,n查询从 $m+1$ 行开始的 $n$ 个元组

查询成绩在前五名的学生学号：

select sno,grade from sc order by grade dsec limit 5;

查询成绩在6-10名的学生学号，成绩；

select sno,grade from sc order by grade desc limit5,5;

GROUP BY

GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组，如果是多列，则先对第一列的值进行分组，然后对每一组中的值按照第二列值分组，依次类推。结果集中如果有重复的列组值，则将其合并为一行输出。

GROUP BY有个分组筛选条件HAVING 后面跟判断表达式

例：对表A，B两列进行如下分组

A	B
1	2
1	2
1	3
2	4

最后的分组结果为：

A	B
1	2
1	3
2	4

对查询结果分组的目的之一是细化聚集函数的作用对象，如果未对查询结果分组，聚集函数将作用于整个查询结果，分组后聚集函数将作用于每一组，即每组都有一个聚集函数的值。

查询学生的学号，平均成绩

select cno,count(sno) from sc group by cno;

该语句对查询结果按Cno的值进行分组，所有具有相同cno值的元素为一组，然后对每一组应用聚集函数COUNT进行计算，求得改组的学生人数

查询课程的课程号，平均成绩，并按照平均成绩降序排列

select sno,avg(grade) from sc group by cno order by avg(grade) desc;

查询学生的平均年龄大于20岁的专业名

select smajor,avg(year(now())-year(sbirthdate)) 
    from student group by smajor having avg(year(now())-year(sbirthdate))>20;

先用group by对smajor进行分组，再用having来过滤平均年龄大于 $20$ 的数据

查询 $2019$ 年第 $2$ 学期选修课程数超过 $10$ 门的学生学号

select sno from sc 
    where semester=20192        #先求出2019年第二学期选课的所有学生
    group by sno                #用group b字句按sno进行分组
    having count(*)>10;          #用聚集函数count对每一组进行计数

这里having语句给出了选择组的条件，只有满足条件(即一个组中元组的个数 $>10$ ，表示此学生 $2019$ 年第 $2$ 学期选修课程超过 $10$ 门)的组才会被选出来

注意：关系数据库管理系统在处理SQL语句时，先处理WHERE子句，根据条件选出合格元组，生成一个临时表，上例中选出了 $2019$ 年第 $2$ 学期选课的所有学生，再使用GROUP BY子句对临时表按照学号进行分组，最后用HAVING条件中的聚集函数COUNT对每一组进行计数，选出COUNT>10的组，输出该组的sno。

where和having子句的区别：

作用的对象不同。WHERE子句作用于表和视图，HAVING子句作用于组。
WHERE在分组和聚集计算之前选取输入行(因此，它控制哪些行进入聚集计算)，而HAVING在分组和聚集之后选取分组的行。因此，WHERE子句不能包含聚集函数。以下写法是错误的：
```
SELECT smajor,AVG(YEAR(NOW())-YEAR(sbirthdate)) age FROM student 
    where AVG(YEAR(NOW())-YEAR(sbirthdate))>20;          #where后面不能跟聚集函数avg
```
聚集函数是从确定的结果集中整列数据进行计算的，而where子句则是对数据行进行过滤的，所以where子句后面跟聚集函数毫无意义。相反，HAVING子句总是包含聚集函数，严格说来，可以写不使用聚集的HAVING子句，但这样做效率不高，因为同样的条件可以更有效地用于WHERE进行过滤。
WHERE子句在聚合前先筛选记录，也就是说作用在GROUP BY子句和HAVING子句前。而HAVING子句在聚合后对组记录进行筛选。

5.3 连接查询

若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询是关系型数据库中最常用的查询，包括：等值连接查询、非等值连接查询、自然连接查询、外连接查询、复合条件连接查询、多表连接查询等。

数据库中连接表的关系代数是笛卡尔积和选择

等值与非等值连接查询

连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词。WHERE后面用=连接的查询称为等值查询，其他比较运算符的连接查询称为非等值连接查询。

查询学生的学号，姓名，课程号，成绩；

select student.sno,sname,cno,grade from student,sc where student.sno=sc.sno;

上面执行过程是：

首先在 $student$ 表中找到第一个元组，然后从头开始扫描 $sc$ 表，逐一查找与 $student$ 第一个元组的 $sno$ 相等的 $sc$ 元组，找到后将 $student$ 中的第一个元组与该元组拼接起来，形成结果表中的一个元组。 $sc$ 全部查找完后，再找 $student$ 中第二个元组，然后再从头开始扫描 $sc$ ，逐一查找满足连接条件的元组，找到后将 $student$ 中的第二个元组与该元组拼接起来，形成结果表中的一个元组。重复上述操作，知道 $student$ 表中全部元组都处理完毕为止。

自然连接查询

把结果表目标列中重复的属性去掉的等值连接查询则为自然连接查询。

查询学生的信息和选课情况；

SELECT sname,ssex,sbirthdate,smajor,sc.* FROM sc,student WHERE student.sno=sc.sno;

复合条件连接查询

使用一条sql语句可以同时完成选择和连接查询，这时WHERE子句是由连接谓词和选择谓词组成的复合条件。WHERE子句中有多个条件的连接查询，称为复合条件连接查询。

查询计算机科学与技术专业的男性同学选课的课程号与成绩；

select cno,grade from sc,student where student.sno=sc.sno and smajor='计算机科学与技术' and ssex='男';

查询数据结构和数据库系统概论的学生的学号，成绩；

select sno,grade from sc,course where course.cno=sc.cno and cname in('数据结构','数据库系统概论');

自身连接查询

连接操作不仅可以两个表之间进行，也可以是一个表与其自身进行连接，此时的连接查询称为自身连接查询。

查询先修课的先修课；

SELECT c1.cno,c2.cpno FROM course c1,course c2  WHERE c1.cpno=c2.cno AND c2.cpno IS NOT NULL;

外连接查询

关键词	作用
A_TABLE `left join` B_TABLE(左外连接)	以A_TABLE为主表建立投影，主表所有元组被保留
A_TABLE `right join` B_TABLE（右外连接）	以B_TABLE为主表建立投影，主表所有元组被保留
`join`（连接）	只返回两个表中联结字段相等的行

查询数据结构和数据库系统概论的学生的学号，课程号，成绩；

select sc.sno,sc.cno,sc.grade from sc join course on(course.cno=sc.cno) 
    where cname in ('数据结构','数据库系统概论');

查询所有学生的选课记录，如果该学生没有参与选课，也要把其记录保留在结果内；

select * from student left join sc on(student.sno=sc.sno);
select * from sc right join student on(student.sno=sc.sno);

多表连接查询

连接查询除了可以是两表连接查询、一个表与其自身连接查询外，还可以是两个以上的表进行连接查询，后者通常称为多表连接查询。

查询选修数据结构的计算机科学与技术专业的学生姓名和成绩；

select sname,grade from student,course,sc where 
    student.sno=sc.sno 
    and course.cno=sc.cno 
    and smajor='计算机科学与技术' 
    and cname='数据结构';

在执行连接查询时，多个表连接的次序会影响执行效率。

5.4 嵌套查询

在SQL中一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询快的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。子查询的查询条件依赖于父查询。

带有IN谓词的子查询

子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最长使用的谓词。

查询刘晨的选课成绩;

select grade from sc where sno in (select sno from student where sname='刘晨');

查询计算机科学与技术专业和信息安全专业选课的数据结构的成绩；

SELECT grade FROM sc WHERE cno IN (SELECT cno FROM course WHERE cname='数据结构') 
    AND sno IN (SELECT sno FROM student WHERE smajor IN('计算机科学与技术','信息安全'));

查询选修数据结构的学生姓名；

select sname form student where sno in
    (select sno from sc where cno in
        (select cno from course where cname='数据结构'));

查询张立选修的课程名；

select cname from course where cno in
    (select cno from sc where sno in
        (select sno from student where sname='张立'));

带有比较运算符的子查询

查询刘晨的选课成绩；

select grade from sc where sno = (select sno from student where sname='刘晨');

上面例子是不相关子查询：子查询的条件不依赖于父查询。下面是相关子查询：子查询的条件依赖于父查询

查询每个学生超出其平均成绩的课程号；

#法一：
select cno from sc,(select sno,avg(grade) ag from sc group by sno) sc2 
    where sc.sno=sc2.sno and grade>ag;
#法二：
select cno from sc x where grade>(select avg(grade) from sc y where y.sno=x.sno);

该语句执行过程采用以下三个步骤：

①从外层查询中取出 $sc$ 的一个元组 $x$ ，将元组 $x$ 的 $sno$ 值 $(20180001)$ 传送给内层查询。

SELECT AVG(Grade) FROM sc y WHERE y.sno='20180001'

②执行内层查询，得到近似值 $89.3$ ，用该值代替内层查询，得到外层查询：

SELECT sno,cno FROM sc x WHERE Grade>=89.3

③执行这个查询，得到 $(20180001,81002)$ ，然后外层查询取出下一个元组重复做上述步骤处理直到外层的 $sc$ 元组全部处理完毕

查询每个专业超出其专业平均年龄的学生学号和姓名；

#法一：
select sno,sname from student where 
    year(now())-year(sbirthdate)>(select avg(year(now())-year(sbirthdate)) 
                                  from student y where y.smajor=student.smajor);  
#法二：
slelct sno,sname from 
    student,(select smajor,avg(year(now))-year(sbirthdate)) ag from student group by smajor) s2 
        where student.smajor=s2.smajor and year(now())-year(sbirthdate)>ag;

带有any和all谓词的子查询

当子查询返回单值时，可以用比较运算符，但返回多值时要用 $ANY$ 或 $ALL$ 谓词修饰符。而使用 $ANY$ 和 $ALL$ 谓词时必须同时使用比较远算符。其 $ANY$ 语义如下所示：

符号	意义
$>ANY$	大于任意一个值，大于最小值
$<ANY$	小于任意一个值，小于最大值
$>=ANY$	大于或等于子查询结果中的某个值
$<=ANY$	小于或等于子查询结果中的某个值
$=ANY\sim IN$	等于子查询结果中的某个值
$!=ANY$	不等于子查询结果中的某个值

$ALL$ 语义如下所示：

符号	意义
$>ALL$	大于所以值，大于最大值
$<ALL$	小于所以值，小于最小值
$>=ALL$	大于或等于子查询结果中的所有值
$<=ANY$	小于或等于子查询结果中的所有值
$!=ANY$	不等于子查询结果中的任何值
$=ALL$	等于子查询结果中的所有值(没有什么实际意义)

查询非计算机科学与技术专业比计算机科学与技术专业年龄都小的学生的学号，姓名，出生日期，专业。

#法一：
SELECT sno,sname,sbirthdate,smajor FROM student WHERE 
    smajor!='计算机科学与技术' AND 
    sbirthdate>ANY(SELECT sbirthdate FROM student WHERE smajor='计算机科学与技术');
#法二：
SELECT sno,sname,sbirthdate,smajor FROM student WHERE 
    smajor!='计算机科学与技术' AND 
    YEAR(NOW())-YEAR(sbirthdate)<ANY(SELECT YEAR(NOW())-YEAR(sbirthdate) 
                                     FROM student WHERE smajor='计算机科学与技术');

执行步骤是：

先处理子查询，找出计算机科学与技术专业中所有学生的年龄，构成一个集合。然后处理父查询，找所有不是计算机科学与技术专业且出生日期晚于集合中所有值的学生。

查询非信息安全专业比信息安全专业选课成绩都要小的学生的学号，课程号，成绩；

#法一：
select sno,cno,grade from sc where sno in
    (select sno from student where smajor!='信息安全') 
    and grad<all(select grade from sc where sno in(select sno from student where smajor='信息安全'));
#法二：
SELECT sc.sno,sc.cno,sc.grade FROM sc,student WHERE 
    student.sno=sc.sno AND smajor!='信息安全' AND 
    grade<ALL(SELECT grade FROM sc,student WHERE student.sno=sc.sno AND smajor='信息安全');

带有 $EXISTS$ 谓词的子查询

EXISTS代表存在量词 $\exists$ 。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值 $true/false$ ，所以查询列名用*即可。

查询所有选修了81001号课程的学生姓名。

select sname from student where exists (select * from sc where sno=student.sno and cno='81001');

本查询中子查询条件依赖于外层父查询的某个属性值(student的sno值)，因此也是相关子查询。处理过程如下：

首先取外层查询中 $student$ 表的第一个元组，根据它与内层查询相关的属性值(sno值)处理内层查询，若where子句返回值为真，则取外层查询中该元组的sname放入结果表，然后再取student表的下一个元组。重复这一过程，直到外层student表全部检查完为止。

$\Large 例1:$ 查询选修了全部课程的学生姓名

SELECT sname FROM student WHERE NOT EXISTS 
    (SELECT * FROM course WHERE NOT EXISTS 
        (SELECT * FROM sc WHERE sc.sno=student.sno AND sc.cno=course.cno));

$\Large 例2:$ 查询至少选修了学生20180002选修的全部课程的学生的学号(不存在这样的课程 $y$ ，学生 $21080002$ 选修了 $y$ ，而学生 $x$ 没有选修)

SELECT sno FROM student WHERE NOT EXISTS 
    (SELECT * FROM sc WHERE sno='20180002' AND NOT EXISTS 
        (SELECT * FROM sc X WHERE x.sno=student.sno AND x.cno=sc.cno));

EXISTS原理

EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT ... FROM...)，我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。

一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。

SELECT *FROM student WHERE EXISTS(SELECT cno FROM course WHERE cno='81009')

上句因为 $exists$ 始终返回的是 $false$ （没有81009这门课程），所以外层查询始终无效，也就不会产生数据。

分析器会先看语句的第一个词，当它发现第一个词是SELECT关键字的时候，它会跳到FROM关键字，然后通过FROM关键字找到表名并把表装入内存。并通过指针找到第一条记录，接着是找WHERE关键字计算它的条件表达式，如果找不到则返回到SELECT字段解析。如果找到WHERE，则分析其中的条件，如果为真那么把这条记录装到一个虚表当中，指针再指向下一条记录。如果为假那么指针直接指向下一条记录，而不进行其它操作。一直检索完整个表，并把检索出来的虚拟表返回给用户。完成后再回到SELECT分析字段。最后形成一张我们要的虚表。

　　WHERE关键字后面的是条件表达式。条件表达式计算完成后，会有一个返回值，即非0或0，非0即为真(true)，0即为假(false)。同理EXISTS后面的条件也有一个返回值，真或假，来确定接下来执不执行SELECT。

EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，但要看实际情况具体使用：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

执行顺序如下：

首先执行一次外部查询
对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会引用外部查询中当前行的值。
使用子查询的结果来确定外部查询的结果集。

总结：总的来说Exists执行的流程Exists首先执行外层查询，再执行内存查询，与IN相反。流程为首先取出外层中的第一元组，再执行内层查询，将外层表的第一元组代入，若内层查询为真，即有结果时。返回外层表中的第一元组，接着取出第二元组，执行相同的算法。一直到扫描完外层整表。

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

SELECT sname FROM student WHERE NOT EXISTS 
    (SELECT * FROM course WHERE NOT EXISTS 
        (SELECT * FROM sc WHERE sc.sno=student.sno AND sc.cno=course.cno));

全面分析这句话，首先在题目上，题目可以转换为：不存在一门课程这个学生没有选修。因为没有MySQL没有任意一个这样的谓词，只能用EXISTS或者NOT EXISTS来表示。这也是EXISTS存在意义。

所以外层查询语句代表SELECT sname FROM student WHERE NOT EXISTS(一门课程这个学生没有选修)接下来就是把course表中的课程依次拿出来找出没有选修的。因为NOT EXISTS子查询中找不到的提交结果集。NOT EXISTS查询的本质还是相关查询，所以只要把在最后一个select中sc.sno=student.sno AND sc.cno=course.cno满足这个就可以将这个同学选课信息通过sc表和crouse的课程连接一遍，找到连接不上的，即：没有选修的。这样就找到了一门课这个学生没有选修，也即存在没有选修的课，那么该学生被去掉，进行下一个同学的判断。

这样可以形成一个结构：

第一个select就是你要选的就是学生
SELECT Sname
FROM Student
第二个 select 就是课程
第三个select就是学生和课程发生关系的表：SC选修表让他们连接起来

再从程序结构上解析这句话：EXISTS的相关子查询语句就是一个嵌套循环，内层循环对应外层循环。转化为C++代码结构如下：

for(i=1;i<student.length( 学生的总人数); i++){
    bool notExitst=true;                        //notExitst同上面NOT EXISTS
    for(j=1;j<Crouse.length(总的课门数); j++){
        for(sc=1;sc<sc.length(sc表总记录数);sc++){
            if(sc.sno=student.sno AND sc.cno=course.cno){
         //如果该学生这门课在sc表中找到对应,那么第二个NOT EXISTS为false不返回任何结果,第二层course进入下一个循环  
                notExitst=false;
                break;
            }
        } 
//如果sc表遍历完还没有找到,则第二个NOT EXISTS为true,返回该学生元组,但下面15行第一个NOT EXISTS取反就变为false
       if(notExitst) break; 
    }
 //这是第一个NOT EXISTS,如果学生选课信息表sc都能对应课程号course.cno,即内层为notExitst=false,那么该学生信息放入结果集
    if(!notExitst){将sname放入结果集;}    //只有内层notExitst=false时才将sname放入结果集
}

从这里可以看出EXISTS语句效率取决于外层表元组大小，所以EXISTS适合外表小，内表大的查询。这是与IN在效率上本质区别。

同时也不难理解EXISTS只有两种情况会为上层传递返回值：①当EXISTS谓词判定为true时；②内层表所有元组遍历完时。

MySQL代码逻辑是这样的：

先将student表放入内存中并将指针指向第一个元组，即学号为 $20180001$ 学生；course表也是一样操作指针指向第一个元组 $81001$ 课程号，再将最内层sc表通过sc.sno=student.sno AND sc.cno=course.cno条件连接起来。
当sc表中找到满足(and谓词)学号是 $20180001$ 课程号是 $81001$ 元组时，内层NOT EXISTS不为NULL，返回false，即第二层course表不返回任何数据给第一层的student，而是继续将course表指针指向下一个元组81002课程号。重复此操作，直到当course中指针指向最后一个课程号，此时由于是最后一次循环，必定有返回值，如果为false则外层取反即该学生选修所有课程，将该学生保存至结果集中。最外层student指向第二个元组，即学号是 $20180002$ 学生。
重复上述操作，如果当course中指针指向一个元组的课程号，而学号和该课程号没有在sc中找到对应记录时，证明该学生没有学习这门课程，此时第二层course的NOT EXISTS返回true，将该学生信息传递给第一层的NOT EXISTS，而外层取反变为false，该记录值对应学号的学生不被保留。
student表继续指向下一个学生号，重复上述三步操作。

关于EXISTS与NOT EXISTS什么时候用：当题中涉及到，"全部"、"任意一个"这样的谓词时，应该转化为 $\exists$ 谓词，使用EXISTS语句。一般来说NOT EXISTS(select *From 表2)修饰的谓词要遍历表2内所有元组，当所有元组遍历完后仍不满足条件，即为NULL时，NOT EXISTS才返回true。而EXISTS谓词只需要在表二中找到满足条件的元组，即可返回true。

5.5 集合查询

查询语句的查询结果是元组的集合，所以对多个查询语句的结果可进行集合操作。集合操作包括：

并操作(UNION)可以用or替代
交操作(INTERSECT)可以用and替代
差操作(EXCEPT)

注意：参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同。

并集(自带去重)

查询计算机科学与技术专业和信息安全专业学生的信息

select * from student where smajor='计算机科学与技术'
   union select * from student where smajor='信息安全';

上面的语句可以用or关键字替代

select * from student where smajor='计算机科学与技术'
   or  smajor='信息安全';

交集

MySQL没有交集关键字只能用INNER JOIN替代： INNER JOIN(等值连接)只返回两个表中联结字段相等的行。
差集

MySQL没有差集关键字， $A$ 表与 $B$ 表的差集，相当于两张表相同数据不保留，只保留 $A$ 表中不同于 $B$ 表的数据。

5.6 基于派生表的查询

子查询不仅可以出现在WHERE中，还可以出现在FROM子句中。

查询每个学生超出他自己平均成绩的课程号;

select cno from sc,(select sno,avg(grade) ag from sc group by sno)sc2 
    where sc.sno=sc2.sno and grade>ag;

6. 空值的处理

所谓空值就是指不知道,不存在或无意义的值。

空值的判断: is null，is not null，<=>：相等或都等于null

查询先修课为空的课程信息

select * from course where cpno is  null;
select * from course where cpno <=> null;

空值的约束格式：列名数据类型 not null

控制的运算：

空值与另一个值(包括空值)的算术运算为空值
空值与另一个值(包括空值)的比较运算结果为UNKNOW

7. 视图的操作

视图是从一个或几个基本表(也可以是视图)导出的表，它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍在原来的基本表当中。

视图一经定义就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图，但对视图的更新(增、删、改)操作有一定的限制。

视图的作用：

视图能够对机密数据提供保护
视图对重构数据库提供了一定程度的逻辑独立
视图能够简化用户的操作
视图能够使用户能以多种角度看待同一数据

7.1 视图的创建

创建视图的语法如下：

create view 视图名 as 子查询 [with check option];

子查询可以是任何select语句，with check option表示对视图进行UPDATE，INSERT，DELETE操作时，要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。且视图可以嵌套创建。

创建视图s1，要求视图中包含学生，课程，学生选课的所有信息

create view s1 as 
     select student.*,course.*,grade,semester,teachingclass
     from student,course,sc where student.sno=sc.sno and course.cno =sc.cno;

关系数据库在执行CREATE VIEW语句的结果只是把视图的定义存入数据字典，并不执行其中的select语句。只有在对视图查询时，才按视图的定义从基本表中将数据查出。

如果在视图后面加上with check option，则对表进行除查询操作之外要保证满足谓词条件：

创建视图s3，要求包含计算机专业学生的所有信息，以及对视图的增删改都只能是计算机专业

create view s3 as 
    select *from student where smajor='计算机科学与技术' with check option;

7.2 视图的更新、删除与查询

更新视图：

当视图来源于多个表时或视图出现聚集函数时，不允许更新；当视图来源于单个表时，则可以更新。同时会更新基本表中的数据。
```
INSERT INTO s1(sno,sname,cno,cname)VALUES('20180013','张三','81012','数据库');
```
以上代码会执行出错因为，s1为多表视图，这样只能在子表中插入，再查询视图。
删除视图：

视图删除后视图定义将从数据字典中删除，如果该视图上还导出了其他视图，则使用CASCADE级联删除语句把该视图和由它导出的所有视图一起删除。
```
drop view 视图名;
drop view s1;
```
级联删除视图：如果视图s1基础上还定义视图s2，此时直接删除s1会失败。成功操作是加上CASCADE关键字。如下：
```
drop view s1 cascade;
```
查询视图：

视图简化了查询的操作，视图可以像基本表一样进行查询。
```
select * from 视图名;
```
查询信息安全专业选修的课程名
```
select cname from s1 where smajor='信息安全';
```

2025 年 4 月
一	二	三	四	五	六	日
	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

一. 数据表的创建、删除、修改与查询

1. 创建表

2. 表的修改

新增一列

修改列名称

修改列的数据类型

删除列

表的删除

3. 索引的创建与删除

创建索引

查看索引

修改索引

删除索引

删除外键

4. 数据更新

插入数据

修改数据

删除数据

5. 数据查询

5.1 单表查询

5.2 选择表中的若干元组

消除重复行

查询满足条件的元组

ORDER BY排序

聚集函数

limit子句

GROUP BY

5.3 连接查询

等值与非等值连接查询

自然连接查询

复合条件连接查询

自身连接查询

外连接查询

多表连接查询

5.4 嵌套查询

带有IN谓词的子查询

带有比较运算符的子查询

带有any和all谓词的子查询

带有EXISTS谓词的子查询

EXISTS原理

5.5 集合查询

5.6 基于派生表的查询

6. 空值的处理

7. 视图的操作

7.1 视图的创建

7.2 视图的更新、删除与查询

评论

发送评论 编辑评论

推荐文章

带有 $EXISTS$ 谓词的子查询

发送评论编辑评论