目录
数据查询(续)
集合查询
主要包括并操作UNION
交操作INTSERSECT
差操作EXCEPT
例3.64 查询计算机科学系的学生及年龄不大于19岁的学生
SELECT *
FROM Student
WHERE Sdept='CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;
SELECT * FROM Student;
'及‘可以看成’或‘,既可以用’OR来表示
例3.65 查询选修了课程1或者选修了课程2的学生集合的并集
SELECT Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno='2';
--SELECT * FROM Student;
SELECT * FROM SC;
例3.66 查询计算机科学系的学生与年龄不大于19岁的学生的交集
SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19;
SELECT * FROM Student;
交集INTERSECT
可以看成并且AND
SELECT *
FROM Student
WHERE Sdept='CS' AND Sage<=19;
例3.67 查询既选修了课程1又选修了课程2的学生。就是查询查询选修课程1的学生集合与选修课程2的学生集合的交集
SELECT Sno
FROM SC
WHERE Cno='1'
INTERSECT
SELECT Sno
FROM SC
WHERE Cno='2';
--SELECT * FROM Student;
SELECT * FROM SC;
可以表示为:
SELECT Sno
FROM SC
WHERE Cno='1' AND Sno IN
(SELECT Sno
FROM SC
WHERE Cno='2';
例3.68 查询计算机科学系的学生余年龄不大于19岁的学生的差集
SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage<=19;
SELECT * FROM Student;
等价于查询计算机科学系中年龄大于19岁的学生:
SELECT *
FROM Student
WHERE Sdept='CS' AND Sage>19;
基于派生表的查询
例3.57 找出每个学生超过他自己选修课程平均成绩的课程号
一:
SELECT Sno,Cno
FROM SC x
WHERE Grade>=
(SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno);
--SELECT * FROM Student;
SELECT * FROM SC;
--SELECT * FROM Course;
二:
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;
--SELECT * FROM Student;
SELECT * FROM SC;
三:
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 * FROM Student;
SELECT * FROM SC;
等价于:
SELECT Sname
FROM Student,(SELECT Sno FROM SC WHERE Cno='1') AS SC1
WHERE Studet.Sno=SC1.Sno;
数据更新
插入数据
插入子查询结果
语句格式:
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岁
SELECT * FROM Student;
UPDATE Student
SET Sage=22
WHERE Sno='201215121';
SELECT * FROM Student ;
例3.74 将所有的学生年龄增加一岁
SELECT * FROM Student;
UPDATE Student
SET Sage=Sage+1;
SELECT * FROM Student ;
例3.75 将计算机科学系全体学生的成绩置零
SELECT * FROM Student ;
SELECT * FROM SC;
UPDATE SC
SET Grade=0
WHERE Sno IN(
SELECT Sno
FROM Student
WHERE Sdept='CS');
SELECT * FROM Student ;
SELECT * FROM SC;
删除数据
一般格式:
DELETE
FROM <表名>
[WHERE <条件>];
删除某一个元组的值
例3.76 删除学号为201215129的学生记录
SELECT * FROM Student ;
--SELECT * FROM SC;
DELETE
FROM Student
WHERE Sno='201215129';
SELECT * FROM Student ;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210412111322443.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zhbmxycg==,size_16,color_FFFFFF,t_70)
删除多个元组的值
例3.77 删除所有学生的选课记录
SELECT * FROM SC;
DELETE
FROM SC;
--SELECT * FROM Student ;
SELECT * FROM SC;
带子查询的删除语句
例3.78 删除计算机科学系所有学生的选课记录
坏了,坏了,出大问题了,刚才我不是全删了么
再输一遍吧。。。。
SELECT * FROM SC;
INSERT
INTO SC
VALUES('201215121','1',92),
('201215121','2',85),
('201215121','3',88),
('201215122','2',90),
('201215122','3',80),
('201215123','2',82);
--SELECT * FROM Student ;
SELECT * FROM SC;
--SELECT * FROM Student ;
SELECT * FROM SC;
DELETE
FROM SC
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept='CS');
--SELECT * FROM Student ;
SELECT * FROM SC;
空值的处理
空值的产生
例3.79 向SC表中插入一个元组,学生号是’201215127‘,课程号是1,成绩为空
--SELECT * FROM Student ;
SELECT * FROM SC;
INSERT INTO SC(Sno,Cno,Grade)
VALUES('201215127','1',NULL)
--SELECT * FROM Student ;
SELECT * FROM SC;
或者:
SELECT * FROM SC;
INSERT INTO SC(Sno,Cno)
VALUES('201215127','1')
--SELECT * FROM Student ;
SELECT * FROM SC;
例3.80 将Student表中学生学号为’201215122‘的学生所属的系改为空值
SELECT * FROM SC;
UPDATE Student
SET Sdept=NULL;
WHERE Sno='201215127';
SELECT * FROM SC;
空值的判断
例3.81 从Student表中找出漏填了数据的学生信息
SELECT * FROM Student ;
--SELECT * FROM SC;
SELECT *
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;
SELECT * FROM Student ;
空值的约束条件
1、属性定义(或者域定义)中有NOT NULL约束条件的不能取空值
2、加了UNIQUE限制的属性不能取空
3、码属性不能取空
空值的算术运算、比较运算和逻辑运算
空值与另一个值(包括空值)的比较运算结果为UNKNOW,传统逻辑运算结果(TRUE,FALSE)的逻辑就扩展成了三值逻辑。
只有使WHERE和HAVING都为TRUE的时候才能出输出结果。
例3.82 找出选修了1号课程的不及格学生
SELECT Sno
FROM SC
WHERE Grade<60 AND Cno='1';
--SELECT * FROM Student ;
SELECT * FROM SC;
例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 * FROM Student ;
SELECT * FROM SC;
或者:
SELECT Sno
FROM SC
WHERE Cno='1' AND (Grade<60 OR Grade IS NULL);
视图
1、视图是一个或几个基本表(或视图)导出的表
2、是一个虚表
3、数据库只存放试图定义,而不存放视图数据
定义视图
建立视图
一般格式:
CREATE VIEW <视图名>[(<列名>[<列名>...)]]
AS<子查询>
[WITH CHECK OPTION];
WITH CHECK OPTION
表示视图对UPDATE、INSERT、DELETE操作时要保证更新、插入或删除的行满足视图定义的谓词条件(子查询的条件表达式)
以下三种情况必须明确指定组成视图的所有列名:
1、某个目标列不是单纯的属性名,而是聚集函数或列表达式
2、多表连接时选出了几个同名列作为视图的字段
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
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept='IS' AND
Student.Sno=SC.Sno AND
SC.Cno='1';
例3.87 建立信息系选修了1号课程且成绩在90分以上的学生的视图
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90;
例3.88 定义一个反应学生出生年份的视图
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2021-Sage
FROM Student
例3.89 将学生的学生学号及平均成绩定义为一个视图
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
例3.90 将Student表中所有女生记录定义为一个视图
CREATE VIEW S_Student(F_sno,name,sex,age,dept)
AS
SELECT *
FROM Student
WHERE Ssex='女';
删除视图
语句格式:
DROP VIEW <视图名>[DASCADE];
例3.91 删除视图BT_S和视图IS_S1
DROP VIEW BT_S;
DROP VIEW IS_S1;
坏了,坏了
我的S1直接删除了,emmm,CSDN说对索引视图执行 DROP VIEW 时,将自动删除视图上的所有索引,啊,晓得了,我的SQL server有亿点点猛啊,对于有索引的视图直接删,好脾气,所以想下载我的SQL server吗?点这儿
然后我来说一哈标准SQL是删不了的,应采用语句:
DROP VIEW IS_S! CASCADE;
查询视图
例3.92 在信息系学生的视图中找出年龄小于20岁的学生
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;
SELECT * FROM IS_Student;
例3.93 查询选修了1号课程的信息系学生
SELECT IS_Student.Sno,SC.Sno,Sname
FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND SC.Cno='1';
SELECT * FROM IS_Student;
SELECT * FROM SC;
例3.94 在S_G视图(例3.89中定义的视图)中查询平均成绩在80分以上的学生学号和平均成绩
开始使用:
SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno;
SELECT * FROM IS_Student;
SELECT * FROM SC;
出现了下下面的问题:
WHERE子句中是不能使用聚集函数作为条件表达式,因此执行此修正后的查询将会出现语法错误,正确语句应该是:
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno HAVING
AVG(Grade)>=80;
--SELECT * FROM IS_Student;
SELECT * FROM SC;
目前多数关系数据库系统对行列子集视图的查询均能进行正确转换。但对非行列子集视图的查询就不一定能够转换。
也可使用SQL语句完成:
SELECT *
FROM(SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno)AS S_G(Sno,Gavg)
WHERE Gavg>=80;
试图一旦定义,其定义将永远保存在数据字典中,之后的所有查询都可以直接引用该视图。派生表只是语句执行时临时定义,语句执行后该定义即被删除。
更新视图
例3.95 将信息系学生视图IS_Student学好中为201215122的学生姓名改为刘辰
UPDATE IS_Student
SET Sname='刘辰'
WHERE Sno='201215122';
SELECT * FROM IS_Student;
转换为更新语句:
UPDATE Student
SET Sname='刘辰'
WHERE Sno='201215122'AND Sdept='IS';
等价于对IS系进行信息更新
例3.96 向信息系学生视图IS_Student中插入一个新的学生记录,其中学号为201215129,姓名为赵新,年龄为20岁
INSERT
INTO IS_Student
VALUES('201215129','赵新',20);
SELECT * FROM IS_Student ;
我发现哈,这个SQL server版本有点离谱,是不会按标准SQL来进行的,T-SQL不会自动填充IS的系名。
可以采用下面的SQL语句直接进行对基本表的更新:
INSERT
INTO Student(Sno,Sname,Sage,Sdept)
VALUES('201215129','赵新',20,'IS');
例3.97 删除信息系学生视图IS_Student中序号为201215129的记录
SELECT * FROM IS_Student ;
DELETE
FROM IS_Student
WHERE Sno='201215129';
SELECT * FROM IS_Student ;
转换为对基本表的更新:
DELETE
FROM Student
WHERE Sno='201215129' AND Sdept='IS';
视图的作用
1、视图能够简化用户的操作
2、视图使用户能以多种角度看待同一数据
3、视图对重构数据库提供了一定程度的逻辑独立性
4、视图能够对机密数据提供安全保护
5、适当利用视图可以更清晰地表达查询
总结
这次充分发现标准SQL和T-SQL 的不同,要随机应变以及寻找他们的不同,加以适应。