数据库实验【8】 | 集合查询派生表查询数据更新以及视图

最新推荐文章于 2023-02-18 15:29:11 发布

WizzenH

最新推荐文章于 2023-02-18 15:29:11 发布

阅读量673

点赞数 2

分类专栏：数据库实验文章标签： sql

本文链接：https://blog.csdn.net/wizzenh/article/details/115613685

版权

数据库实验专栏收录该内容

20 篇文章 1 订阅

订阅专栏

［例3.64］査询计算机科学系的学生及年龄不大于19岁的学生

select *
	from Student
	where Sdept = 'CS'
	union
	select *
	from Student
	where Sage <= 19;

在这里插入图片描述
UNION为并操作，会自动删去重复的元组，若要保留重复元素，则使用UNION ALL操作符。

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

select Sno 
	from SC
	where Cno = '1'
union
select Sno 
	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的学生。就是査询选修课程1的学生集合与选修课程2的学生集合的交集。

select Sno
	from SC
	where Cno = '1'
intersect
select Sno
	from SC
	where Cno = '2';
--或者
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
	where Sdept = 'CS'
	and Sage > 19;

在这里插入图片描述
［例3.57］找出每个学生超过他自己的选修课程平均成绩的课程号

基于派生表的查询：

    子査询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子査询生成的临时派生表成为主査询的査询对象。

        --原来的方法：
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;

在这里插入图片描述
如果子査询中没有聚集函数，派生表可以不指定属性列，子査询SELECT子句后面的列名为其默认属性。例如:
［例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 SC1
	where Student.Sno = SC1.Sno;

在这里插入图片描述
查询部分到此结束，接下来是数据更新：

[例3.69]将一个新学生元组（学号：201215131;姓名：马俊;性别：男;所在系：IS;年龄：18岁）插入到Student表中

insert 
into Student(Sno,Sname,Ssex,Sdept,Sage)
values('201215131','马俊','男','IS',18);

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

insert 
into Student
values('201215126','张成民','男',18,'CS');

在这里插入图片描述
[例3.71] 插入一条选课记录（ ‘200215128’,'1 '）

insert
into SC(Sno,Cno)
values(‘201215128’ , ‘1’);
–相当于：
insert
into SC(Sno,Cno)
values(‘201215128’ , ‘1’ , NULL);
在这里插入图片描述
[例3.72] 对每一个系，求学生的平均年龄，并把结果存入数据库

--首先，建立新表：
create table Dept_age
(
	Sdept char(15),
	Avg_age smallint
);
--插入数据：
insert into Dept_age(Sdept,Avg_age)
select  Sdept,AVG(Sage)
from     Student
group by Sdept;

在这里插入图片描述
[例3.73] 将学生201215121的年龄改为22岁

update  Student
set Sage=22
where  Sno='201215121';

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

update Student
set Sage=Sage+1;

在这里插入图片描述

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

update SC
set    Grade=90
where Sno  in
  (
    select Sno
    from   Student
    where  Sdept= 'CS' 
  );

在这里插入图片描述
[例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)
 values('201215126','1');

在这里插入图片描述

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

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

在这里插入图片描述
[例 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号课程的不及格的学生以及缺考的学生。查询结果要包括grade值为null的学生

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 <视图名>  [(<列名>  [,<列名>]…)]
as <子查询>
[with  check  option];
with  check  option 表示对视图进行update、insert等操作时候保证更新、

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

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

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

create view IS_Student2
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 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,2020-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 F_Student(F_Sno,name,sex,age,dept)
as
select *                       
from Student
where Ssex='女';

在这里插入图片描述
[例3.91] 删除视图BT_S和IS_S1

去掉cascade即可删除IS_S1

drop view BT_S;
drop view IS_S1;--拒绝执行
drop view IS_S1 CASCADE

但SQL server 不支持CASCADE，所以只能将S2一并删除
在这里插入图片描述
只删除S1时，S2会报错

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

select   Sno,Sage
from      IS_Student
where   Sage<20;
--视图消解转换后语句为(有些情况下使用此方法不能正确查询)：
select  Sno,Sage       
from  Student
where  Sdept= 'IS'  AND  Sage<20;

在这里插入图片描述
[例3.93] 查询选修了1号课程的信息系学生

select IS_Student.Sno,Sname
from IS_Student,SC
where IS_Student.Sno = SC.Sno and SC.Cno ='1';

在这里插入图片描述
[例3.94]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

select *
from   S_G
where  Gavg >= 90;

--定义S_G视图： 
create S_G (Sno,Gavg)
as
select  Sno,avg(Grade)
from  SC
group by Sno;

--或者用以下语句
select *
from  (select Sno,avg(Grade)
from  SC group by Sno)
as S_G(Sno,Gavg)
where Gavg>=90;

在这里插入图片描述
[例3.95] 将信息系学生视图IS_Student中学号”201215128”的学生姓名改为”刘辰”。

update  IS_Student
set  Sname= '刘辰'
where  Sno= '201215128';
--转换的代码：
update  Student
set Sname = '刘辰'
where Sno = '201215128' and Sdept= 'IS';

在这里插入图片描述
[例3.96] 向信息系学生视图IS_S中插入一个新的学生记录，其中学号为”201215132”，姓名为”赵新”，年龄为20岁

insert
into IS_Student
values('201215132','赵新',20);

执行语句是正确的，但是并没有插入到视图中，只显示在Student表中

[例3.97]删除信息系学生视图IS_Student中学号为”201215129”的记录

delete
from IS_Student
where Sno= ' 201215129 ';
转换为对基本表的更新：
delete
from Student
where Sno= ' 201215129 ' AND Sdept= 'IS';

第三章例题部分学完了，这一章讲的都是SQL语句的基本操作，内容极多，要好好整理消化，好好做课后习题。

WizzenH

关注

2
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

数据库实验【8】 | 集合查询派生表查询 数据更新以及视图

数据库实验【8】 | 集合查询派生表查询数据更新以及视图