【数据库实验】查询结尾&&数据更新

最新推荐文章于 2023-04-11 18:15:36 发布

chantec

最新推荐文章于 2023-04-11 18:15:36 发布

阅读量1.2k

点赞数 5

分类专栏：数据库

本文链接：https://blog.csdn.net/Chantec/article/details/115677013

版权

数据库专栏收录该内容

25 篇文章 3 订阅

订阅专栏

集合查询

这里就比较简单了，我们都知道，select返回的是一个集合，那么他的集合操作就是普通的集合操作，交并差 union intersect except。

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

select *
from student
where Sdept = 'cs'
union 
select *
from student
where Sage<=19;

在这里插入图片描述

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

select Sno
from sc
where Cno='1'
union 
select Sno
from sc
where Cno='2';

select *
from sc;

在这里插入图片描述

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

select *
from student
where Sdept = 'cs'
intersect
select *
from student
where Sage<=19;

在这里插入图片描述

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

select Sno
from sc
where Cno='1'
intersect
select Sno
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,(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;

在这里插入图片描述

“如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其缺省属性。”（但是也得指定名字用as）

数据更新

数据插入insert

其中数据插入有两种方式，一种是插入元组，我们前面已经做过例题了；还有一种是插入查询结果，也就是查询集合。

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

当然了，我们得注意匹配。

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

当然了我们得先建表

--创建表，用来存 查询的结果--
create  table  dept_avg_age
(
	Sdept char(20),
	avg_age smallint
);

然后

insert 
into dept_avg_age
	select Sdept,AVG(Sage)
	from student
	group by Sdept;

在这里插入图片描述

修改数据update

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

where是条件用来选择要更改的元组，类似select里的作用。不写的话，就是所有的元组。
SET子句给出<表达式>的值用于取代相应的属性列。

[例3.73] 将学生201215126的年龄改为22岁

update student
set Sage=22
where Sno='201215126';

在这里插入图片描述

[例3.74] 将所有学生的年龄增加1岁。

update student
set Sage=Sage+1;

在这里插入图片描述

[例3.75] 将计算机科学系全体学生的成绩置零。

update sc
set Grade=0
where Sno IN 
			(select Sno
			 from student 
			 where Sdept='cs');

在这里插入图片描述

删除数据delete

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

[例3.76] 删除学号为201215128的学生记录。

delete
from student
where Sno='201215128';

容易想到，这里应该有约束，但是这个语句却成功运行成功了，是因为sc表里没有用到这个sno。
在这里插入图片描述

[例3.77] 删除所有的学生选课记录。

delete 
from sc;

（这里就先不演示了，因为之前添加的数据有点乱，一部分是insert，一部分通过sql sever 图形窗口添加的，等我稍后会整理一下，把insert部分整理整齐，当然这个代码的结果是显而易见的）

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

delete 
from sc
where Sno IN (select Sno
		      from student
			  where Sdept='cs')

空值的处理

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

insert
into SC
values('201215126','1',NULL)

在这里插入图片描述

[例3.80] 将Student表中学生号为”201215126”的学生所属的系改为空值。

update student
set Sdept=NULL
where Sno='201215126';

在这里插入图片描述

写这个例题的时候，我把where条件的sno写成sdept了，即sdept=‘201215126’，按理说在update的时候，没有这个元组应该报错；但我再一想，sql针对的是集合，不是一个元组，而一个集合有很多时候都是空。

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

select *
from student 
where Sage IS NULL OR Ssex IS NULL OR Sdept IS NULL OR Sname IS NULL;

在这里插入图片描述

属性定义（或者域定义）中有NOT NULL约束条件的不能取空值
加了UNIQUE限制的属性不能取空值
码属性不能取空值

[例3.82] 找出选修1号课程的不及格的学生。

select Sno
from sc
where Cno='1' AND Grade<60;

在这里插入图片描述

[例 3.83] 选出选修1号课程的不及格的学生以及缺考的学生。

select Sno
from sc
where Cno='1' AND (Grade<60 OR Grade IS NULL)

在这里插入图片描述

视图

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

属性名全部省略或者指定
啥时候全部指定？

某个目标列是聚集函数或列表达式
多表连接时选出了几个同名列作为视图的字段
需要在视图中为某个列启用新的更合适的名字

“关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。”

视图不保存数据，只记录select语句，当使用的时候，再执行select语句。

[例3.84] 建立信息系学生的视图。

在这里插入图片描述
他做语法检查会报错，因为再检查的时候还没有IS_Student还没创建好

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 CS_C1(Sno,Sname,Grade)
as
	select student.Sno,Sname,Grade
	from student,sc
	where student.Sno=sc.Sno AND Cno='1' AND Sdept='CS';

在这里插入图片描述

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

create view CS_C1_G
as 
	select Sno,Sname,Grade
	from CS_C1
	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,avg_Grade)
as
	select Sno,AVG(Grade)
	from sc
	group by Sno;

在这里插入图片描述

[例3.90]将Student表中所有女生记录定义为一个视图

create view F_Student(F_Sno,F_name,sex,age,dept)
as 
	select *
	from student
	where Ssex='女';

在这里插入图片描述

删除视图

DROP  VIEW  <视图名>[CASCADE];

“删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除”
“如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除 ”

[例3.91 ] 删除视图BT_S和CS_C1

前者只有自己，后者被CS_C1_G依赖。
在这里插入图片描述

而后者也能成功运行
这里和标准sql有差别，标准sql会拒绝执行这条命令。
我们看一下CS_C1_G是什么情况：

我们再试一下cascade，首先重新传教一下CS_C1

看来tsql依旧不支持cascade，但是标准sql会和之前一样的效果，级联删除。

视图查询

RDBMS实现视图查询的方法
视图消解法（View Resolution）
进行有效性检查
转换成等价的对基本表的查询
执行修正后的查询

[例3.92] 在计科学生的视图中找出年龄小于20岁的学生。

select *
from CS_Student
where Sage<20;

所谓试图消解法
就是在查询的where里加上了构建视图时的条件

在这里插入图片描述

[例3.94]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

select *
from S_G
where avg_Grade>=90;

在这里插入图片描述
所谓视图消解法的局限性：把条件只加在where子句里，但是也有情况会出现在having子句里，比如这个例子

在这里插入图片描述
一方面where里面不能有聚集函数，一方面where里面的是对整个表的所有元组的条件，having是对每个组里的条件。

[例3.95] 将信息系学生视图CS_Student中学号”201215131”的学生姓名改为”刘一一”。

update CS_Student
set Sname='刘一一'
where Sno='201215131';

在这里插入图片描述

[例3.96] 向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为”201215100”，姓名为”梁二二”，年龄为20岁

我们做一个对比：
没有添加with check option的
在这里插入图片描述
添加了with check option参数的

试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。

由上面对比可以看到，with check option参数会对添加数据产生限制，如果不符合创建视图时的where，将不能添加。
这里有个差异关于标准sql和tsql：
标准sql会

带有WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时，RDBMS会自动加上Sdept='IS’的条件。

而tsql直接不能插入。
标准sql中，向视图里插入数据会自动补充一个信息：就是创建视图的那个条件。

[例3.97]删除计科学生视图CS_Student中学号为”201215131”的记录

delete 
from CS_Student
where Sno='201215131';

在这里插入图片描述
发现删除不了，是sc的外键约束，我们删除sc里对应的记录再试一下。

还有就是，有些视图是没法更新的，因为他的数据就不是某一个数据或者说他没有储存在计算机里。比如说我们有个视图存的是每个班里的平均分，而我们得到这个平均分是按照班级分组然后用avg函数，我们得到的平均分在数据库里并没有对应的的位置来储存，也就不能改。

好多啊，终于写完了，昨天晚上在图书馆写了俩小时，回宿舍又写了一个多小时，今天上午又写了半个上午。

这里关于标准sql和tsql有好多区别，比如往视图里面插入数据的时候，标准sql会自动将没有写的那个那个条件加上（建立视图时那个条件），而t-sql却不这样干，他会补上null。目前我还有个疑问，就是，有with check option的视图如何添加数据。

chantec

关注

5
点赞
踩
14

收藏

觉得还不错? 一键收藏
1
评论
【数据库实验】查询结尾&&数据更新

目录集合查询[例 3.64] 查询计算机科学系的学生及年龄不大于19岁的学生。[例 3.65] 查询选修了课程1或者选修了课程2的学生。[例3.66] 查询计算机科学系的学生与年龄不大于19岁的学生的交集。[例 3.67]查询既选修了课程1又选修了课程2的学生。[例 3.68] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。基于派生表的查询[例3.57]找出每个学生超过他自己选修课程平均成绩的课程号数据更新数据插入insert[例3.72] 对每一个系，求学生的平均年龄，并把结果
复制链接

扫一扫