MySQL 为结果集起别名数据库的结果集

转载

网络锐评 2024-02-04 20:43:04

文章标签 MySQL 为结果集起别名 Sage sql 计算机科学 文章分类 MySQL 数据库

[例 3.64] 查询计算机科学系的学生及年龄不大于19岁的学生。
[例 3.65] 查询选修了课程1或者选修了课程2的学生。
[例3.66] 查询计算机科学系的学生与年龄不大于19岁的学生的交集。
[例 3.67]查询既选修了课程1又选修了课程2的学生。
[例 3.68] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。

基于派生表的查询

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

数据更新

数据插入insert

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

修改数据update

[例3.73] 将学生201215126的年龄改为22岁
[例3.74] 将所有学生的年龄增加1岁。
[例3.75] 将计算机科学系全体学生的成绩置零。

删除数据delete

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

空值的处理

[例 3.79]向SC表中插入一个元组，学生号是”201215126”，课程号是”1”，成绩为空。
[例3.80] 将Student表中学生号为”201215126”的学生所属的系改为空值。
[例 3.81] 从Student表中找出漏填了数据的学生信息
[例3.82] 找出选修1号课程的不及格的学生。
[例 3.83] 选出选修1号课程的不及格的学生以及缺考的学生。

视图

[例3.84] 建立信息系学生的视图。
[例3.85]建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。
[例3.86] 建立计科选修了1号课程的学生的视图（包括学号、姓名、成绩）。
[例3.87] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。
[例3.88] 定义一个反映学生出生年份的视图。（带有表达式）
[例3.89] 将学生的学号及平均成绩定义为一个视图
[例3.90]将Student表中所有女生记录定义为一个视图

删除视图

[例3.91 ] 删除视图BT_S和CS_C1

视图查询

[例3.92] 在计科学生的视图中找出年龄小于20岁的学生。
[例3.94]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩
[例3.95] 将信息系学生视图CS_Student中学号”201215131”的学生姓名改为”刘一一”。
[例3.96] 向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为”201215100”，姓名为”梁二二”，年龄为20岁
[例3.97]删除计科学生视图CS_Student中学号为”201215131”的记录

集合查询

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

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

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

MySQL 为结果集起别名数据库的结果集_Sage

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

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

select *
from sc;

MySQL 为结果集起别名数据库的结果集_计算机科学_02

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

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

MySQL 为结果集起别名数据库的结果集_Sage_03

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

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

MySQL 为结果集起别名数据库的结果集_sql_04

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

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

MySQL 为结果集起别名数据库的结果集_sql_05

基于派生表的查询

不仅可以在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;

MySQL 为结果集起别名数据库的结果集_Sage_06

“如果子查询中没有聚集函数，派生表可以不指定属性列，子查询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;

MySQL 为结果集起别名数据库的结果集_计算机科学_07

修改数据update

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

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

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

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

MySQL 为结果集起别名数据库的结果集_MySQL 为结果集起别名_08

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

update student
set Sage=Sage+1;

MySQL 为结果集起别名数据库的结果集_sql_09

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

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

MySQL 为结果集起别名数据库的结果集_MySQL 为结果集起别名_10

删除数据delete

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

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

delete
from student
where Sno='201215128';

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

MySQL 为结果集起别名数据库的结果集_MySQL 为结果集起别名_11

[例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)

MySQL 为结果集起别名数据库的结果集_MySQL 为结果集起别名_12

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

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

MySQL 为结果集起别名数据库的结果集_计算机科学_13

写这个例题的时候，我把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;

MySQL 为结果集起别名数据库的结果集_MySQL 为结果集起别名_14

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

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

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

MySQL 为结果集起别名数据库的结果集_MySQL 为结果集起别名_15

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

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

MySQL 为结果集起别名数据库的结果集_MySQL 为结果集起别名_16

视图

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

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

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

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

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

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

MySQL 为结果集起别名数据库的结果集_Sage_17

他做语法检查会报错，因为再检查的时候还没有IS_Student还没创建好

create view IS_Student
as 
	select Sno,Sname,Sage
	from student
	where Sdept='IS';

MySQL 为结果集起别名数据库的结果集_Sage_18

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

create view IS_Student
as 
	select Sno,Sname,Sage
	from student
	where Sdept='IS'
	with check option;

MySQL 为结果集起别名数据库的结果集_计算机科学_19

[例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';

MySQL 为结果集起别名数据库的结果集_sql_20

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

create view CS_C1_G
as 
	select Sno,Sname,Grade
	from CS_C1
	where Grade>=90;

MySQL 为结果集起别名数据库的结果集_sql_21

[例3.88] 定义一个反映学生出生年份的视图。（带有表达式）

create view BT_S(Sno,Sname,Sbirth)
as
	select Sno,Sname,2021-Sage
	from student;

MySQL 为结果集起别名数据库的结果集_sql_22

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

create view S_G(Sno,avg_Grade)
as
	select Sno,AVG(Grade)
	from sc
	group by Sno;

MySQL 为结果集起别名数据库的结果集_MySQL 为结果集起别名_23

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

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

MySQL 为结果集起别名数据库的结果集_计算机科学_24

删除视图

DROP  VIEW  <视图名>[CASCADE];

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

[例3.91 ] 删除视图BT_S和CS_C1

前者只有自己，后者被CS_C1_G依赖。

MySQL 为结果集起别名数据库的结果集_MySQL 为结果集起别名_25

MySQL 为结果集起别名数据库的结果集_MySQL 为结果集起别名_26

而后者也能成功运行

这里和标准sql有差别，标准sql会拒绝执行这条命令。

我们看一下CS_C1_G是什么情况：

MySQL 为结果集起别名数据库的结果集_MySQL 为结果集起别名_27

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

MySQL 为结果集起别名数据库的结果集_Sage_28

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

视图查询

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

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

select *
from CS_Student
where Sage<20;

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

MySQL 为结果集起别名数据库的结果集_sql_29

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

select *
from S_G
where avg_Grade>=90;

MySQL 为结果集起别名数据库的结果集_sql_30

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

MySQL 为结果集起别名数据库的结果集_MySQL 为结果集起别名_31

MySQL 为结果集起别名数据库的结果集_sql_32

一方面where里面不能有聚集函数，一方面where里面的是对整个表的所有元组的条件，having是对每个组里的条件。

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

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

MySQL 为结果集起别名数据库的结果集_Sage_33

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

我们做一个对比：

没有添加with check option的

MySQL 为结果集起别名数据库的结果集_sql_34

添加了with check option参数的

MySQL 为结果集起别名数据库的结果集_sql_35

试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 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';

MySQL 为结果集起别名数据库的结果集_计算机科学_36

发现删除不了，是sc的外键约束，我们删除sc里对应的记录再试一下。

MySQL 为结果集起别名数据库的结果集_计算机科学_37

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

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

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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：android 长按泡泡弹框安卓泡泡怎么截屏

下一篇：typescript 枚举变字符串 typescript 枚举的作用

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯