SQL 数据查询（5）—— 集合查询、基于派生表的查询、数据更新、视图

2024-02-23 18:33:10

文章目录

小结

集合查询

集合操作主要包括：
并操作 UNION
交操作 INTERSECT
差操作 EXCEPT

例3.64：查询计算机科学系的学生及年龄不大于19岁的学生

SELECT *
FROM Student
WHERE Sdept = 'CS'
UNION 
SELECT *
FROM Student
WHERE Sage <= 19

例3.65：查询选修了课程1或者选修了课程2的学生

SELECT *
FROM SC
WHERE Cno = '1'
UNION
SELECT *
FROM SC
WHERE Cno = '2'

例3.66：查询计算机科学系的学生与年龄不大于19岁的学生的交集

SELECT *
FROM Student
WHERE Sdept = 'CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage <= 19

例3.67：查询既选修了课程1又选修了课程2的学生

SELECT *
FROM SC
WHERE Cno = '1'
INTERSECT
SELECT *
FROM SC
WHERE Cno = '2'

例3.68：查询计算机科学系的学生与年龄不大于19的学生的差集

SELECT *
FROM Student
WHERE Sdept = 'CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage <= 19

基于派生表的查询

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

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

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

可以改写成

SELECT Sno,Cno
FROM SC,(
	SELECT Sno,AVG(Grade) 
	FROM SC 
	GROUP BY Sno) 
AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno AND SC.Grade >= Avg_sc.avg_grade

例3.60：查询所有选修了1号课程的学生姓名

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

可以改写成

SELECT Sname
FROM Student,(
	SELECT Sno
	FROM SC
	WHERE Cno = '1'
)
AS SCI
WHERE Student.Sno = SCI.Sno;

派生表出来的表格不会在数据库中显现出来，相当于是一个临时数据库

数据更新

插入数据

插入元组

插入元组用INSERT谓词，一般格式为

INSERT 
INTO <表名> [(<属性列1> [,<属性列2>…  )]
VALUES (<常量1> [,常量2]...);

例3.69：将一个新学生元组（学号：201215128，姓名：陈冬，性别：男，所在系：IS，年龄：18岁）插入到Student表中

INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES('201215128','陈冬','男','IS',18);

出现这个提示是因为原表格中已经有陈冬这个人了

例3.70：将学生张成民的信息插入到Student表中

INSERT
INTO Student
VALUES('201215126','张成民','男',18，'CS');

在INTO后面没有指定属性的话，如果该属性可以赋NULL，则数据库自动将其赋值为NULL，否则会报错

插入子查询的结果

插入子循环结果的INSERT语句一般格式为：

INSERT 
INTO <表名>  [(<属性列1> [,<属性列2>…  )]
子查询;

例3.72：对每一个系，求学生的平均年龄，并把结果存入数据库

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;

修改数据

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

例3.73：将学生201215121的年龄改为22岁

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

例3.74：将所有学生的年龄增加1岁

UPDATE Student
SET Sage = Sage + 1

改变前：

改变后（手抖了，点击了两次执行）：

例3.75：将计算机科学系的全体学生的成绩置为0

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

删除数据

删除语句的一般格式为

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

删除一个元组的值

例3.76：删除学号为201215128的学生记录

DELETE
FROM Student
WHERE Sno = '201215128';

发现陈冬已被删除

删除多个元组的值

例3.77：删除所有学生的选课记录

DELETE
FROM SC;

空空如也了

例3.78：删除计算机科学系所有学生的选课记录

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

空值的处理

空值的产生

例3.79：向SC表中插入一个元组，学生号是”201215126”，课程号是“1“，成绩为空

INSERT INTO SC(Sno,Cno,Grade)
VALUES('201215126','1',NULL);

或

INSERT INTO SC(Sno,Cno,Grade)
VALUES('201215126','1');

例3.80：将Student表中学生号为”201215200“的学生所属的系改为空值

UPDATE Student
SET Sdept = NULL
WHERE Sno = '201215200';

因为没有201215200学生，所以数据库0行进行了改动

例3.81：从Student表中找出漏填了数据的学生信息

SELECT *
FROM Student
WHERE Sname IS NULL OR
		Ssex IS NULL OR
		Sage IS NULL OR
		Sdept IS NULL;

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

例3.82：找出选修了1号课程的不及格的学生

SELECT Sno
FROM SC
WHERE Grade < 60 AND Cno = '1';

例3.83：选出选修了1号课程的不及格的学生以及缺考的学生

SELECT Sno
FROM SC
WHERE Grade < 60 AND Cno = '1'
UNION
SELECT Sno
FROM SC
WHERE Grade IS NULL AND Cno = '1';


SELECT Sno
FROM SC
WHERE Cno = '1' AND (Grade < 60 OR Grade IS NULL);

视图

视图是从一个或几个基本表中导出的表。与基本表不同的是，他是虚表。一旦基本表中的数据发生变化，从视图中查询出的数据也会随之发生变化。

建立视图

建立视图的 CREATE VIEW 命令建立视图

CREATE VIEW <视图名> [(<列名> [,<列名>] ...)]
AS <子查询>
[WITH CHECK OPTION]

WITH CHECK OPTION表示对视图进行UPDATE、INSERT、DELETE操作时要保证更新、插入、删除的行满足视图定义中的谓词条件

组成视图的列名必须全部省略或全部指定。以下3中情况中，必须明确指明组成视图的所有列名：

某个目标列不是单纯的属性名，而是聚集函数或列表达式

多表连接时选出了几个同名列作为视图的字段

需要在视图中为某个列启用新的更合适的名字

例3.84：建立信息系学生的视图

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept = 'IS'

例3.85：建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图中只有信息系的学生

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept = 'IS'
WITH CHECK OPTION;

如果数据库中已经有了同名的视图，数据库不会给覆盖，而是会报错

例3.86：建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）

CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Stduent.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept = 'IS' AND Student.Sno = SC.Sno AND SC.Cno = '1';

多表建立视图的时候，AS后的SELECT可以将多表中的各属性直接一起列出来。如果视图的列名要重新命名的话，要将各新属性直接写在要创建的视图名后边

例3.87：建立信息系选修了1号课程并且成绩在90分以上的学生的视图

CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade > 90

带表达式的视图

例3.88：定义一个反映学生出生年份的视图

CREATE VIEW Student_Birthday(Sno,Sname,Sdept,YEAR)
AS
SELECT Sno,Sname,Sdept,2021-Sage
FROM Student;

例3.89：将学生的学号及平均成绩定义为一个视图

CREATE VIEW AVG_Grade(Sno,Grade)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;

这里的运行结果可能是在做上面实验的时候，将SC表清空了，而且126同学的Grade置为NULL

删除视图

删除视图语句的一般格式是

DROP VIEW <视图名> [CASCADE]

CASCADE是级联（与其有关的全部删除）

例3.91：删除视图BT_S和视图IS_S1

DROP VIEW BT_S;
DROP VIEW IS_S1;

查询视图

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

SELECT Sno,Sage
FROM IS_Student
WHERE Sage < 20

整体查询方法于基本表查询方法一致可参照前4篇博文进行对比查看
SQL 索引的操作数据查询（1）数据更新
 SQL 数据查询（2）
SQL 数据查询（3）
SQL 数据查询（4）—— 嵌套查询

更新视图

例3.95：将信息系学生视图IS_Student中学号为”201215122“的学生姓名改为”刘辰“

UPDATE IS_Student
SET Sname='刘辰'
WHERE Sno='201215122';

例3.96：向信息系学生视图IS_Student中插入一个新的学生记录

INSERT
INTO IS_Student
VALUES('201215129','赵新','20');

例3.97：删除信息系学生视图IS_Student中学号为2012151129的学生记录

DELETE
FROM Student
WHERE Sno = '201215129';

小结

感觉整个第三章主要难点在于嵌套查询这一块，上课的时候，虽然感觉在听讲，但总是有一种脑子转不过来劲的感觉。课后研究的时候，有点感觉，但是相关子查询和非相关子查询的嵌套步骤还需要熟悉。就不能光做完就了事了，还是要多研究。

码农公寓