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

最新推荐文章于 2023-02-18 15:29:11 发布
最新推荐文章于 2023-02-18 15:29:11 发布
阅读量607 收藏 7
点赞数 2
分类专栏： 数据库实验 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/WizzenH/article/details/115613685
版权

［例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
    评论
专栏目录
数据库学习8 — 集合查询、数据更新、视图
Mr_Alpenliebe的博客
04-16 954
章节3.4.4 — 3.7.3 文章目录一、集合查询二、基于派生表的查询三、数据更新四、空值的处理五、视图 一、集合查询 SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。需要注意：各结果的列数、对应项的数据类型必须相同。主要有以下操作： 并操作 UNION 交操作 INTERSECT 差操作 EXCEPT 例题1：查询计算机科学系的学生及年龄不大于19岁的学生。 SELECT * FROM Student WHERE Sdept='CS' UNION SELECT
数据库中基于派生表查询知识点和示例总结
qq_46137108的博客
06-27 1454
数据库中基于派生表查询知识点和示例总结 问题概述：当子查询出现在from字句中，这时子查询生成的临时派生表成为主查询的查询对象。 下面举一个例子对比常规查询写法与基于派生表查询写法： --常规写法 select [all/distinct]<目标表达式>[别名]...... from<表明或视图>[别名]... [where<条件表达式>] [group by<列名1>[having<条件表达式>]] [order by<列名2>[as
基于派生表的查询
weixin_51992178的博客
05-15 525
定义：子查询出现在from子句中，这时子查询生成的临时的派生表成为主查询的查询对象 select sno,cno from sc,(select sno,avg(grade) from sc group by sno) as ave(a,b) where sc.Sno=ave.a and sc.Grade>=ave.b; 说明：这里的from子句的子查询生成一个派生表ave，该表由a，b两列属性组成 如果子查询没有聚焦函数可以不指定属性列子查询的列名为默认值 通过from子句生成派生表时，as.
数据库（六）：数据查询之基于派生表的查询
甜鲸鱼的博客
11-05 6487
子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，此时子查询生成的临时派生表成为主查询的查询对象。
SQL数据库中用创建好的视图修改表数据
12-14
　2、视图中被修改的列必须直接引用表列中的基础数据。不能通过任何其他方式对这些列进行派生，如通过以下方式： 　1）聚合函数：AVG、COUNT、SUM、MIN、MAX、GROUPING、STDEV、STDEVP、VAR 和 VARP。 　2）计算...
SQL SERVER数据库的查询和视图.pdf
最新发布
05-06
视图是一种虚拟表，它不实际存储数据，而是从一个或多个实际表中派生出来的数据集合。创建视图可以简化复杂的查询，提高安全性，并允许用户以更灵活的方式访问数据。 **1. 创建视图** 创建视图的基本语法如下： `...
计算机软件及应用SQL数据表定义查询PPT课件.pptx
10-09
13. **视图**：视图是从一个或多个表中选择的部分数据视图，可以视为虚拟表，提供简化查询的途径。 14. **关系的连接**：包括内连接（INNER JOIN）、右连接（RIGHT JOIN）、左连接（LEFT JOIN）、全连接（FULL JOIN...
计算机软件及应用SQL数据表定义查询PPT学习教案.pptx
10-11
`FROM`子句定义了查询的数据源，可以是一个或多个表或视图的组合。 4. **WHERE子句**： `WHERE`子句用于设定筛选条件，只返回满足条件的行。 5. **更名运算**： 通过`AS`关键字可以为列或表提供别名，便于阅读和...
数据库设计包括表、视图、触发器、存储过程、序列、索引等.doc
02-24
视图是从一个或多个表中派生出来的虚拟表，提供了对数据的另一种表现形式。视图可以简化复杂的查询，提高查询效率，提供数据的安全性和隐私性。 视图可以被用来实现数据的抽象、数据的安全性和隐私性、数据的虚拟化...
sql server 嵌套查询，集合查询，基于派生表的查询，数据更新，空值处理，视图
jiaotshidi的博客
04-16 793
文章目录嵌套查询带有IN谓词的子查询2.带有比较运算符的子查询带有ANY（SOME）或ALL谓词的子查询带有EXISTS谓词的子查询集合查询基于派生表的查询 嵌套查询 带有IN谓词的子查询 [例 3.55] 查询与“刘晨”在同一个系学习的学生。 第一种方法：分步完成 SELECT Sdept FROM Student WHERE Sname= '刘晨'; SELECT Sno, Sname, Sdept FROM
查询（集合查询、基于派生表的查询）、数据更新、空值处理、视图
m0_55788811的博客
04-13 307
查询（集合查询、基于派生表的查询）、数据更新、空值处理、视图一、查询1、集合查询（1）并操作（UNION）（2）交操作（INTERSECT）（3）差操作（EXCEPT）2、基于派生表的查询二、数据更新1、插入数据（插入子查询结果）2、修改数据（带子查询的修改语句）3、删除数据（1）删除某一个元组的值（2）删除多个元组的值（3）带子查询的删除语句三、空值的处理1、空值的产生2、空值的判断3、空值的约束条件4、空值的算术运算、比较运算和逻辑运算四、视图1、定义视图（1）建立视图（2）删除视图2、查询视图3、更新
SQL语言的数据查询之基于派生表的查询
tester Xclincer的博客
02-18 644
子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表成为了主查询的查询对象。 [例] 找出每个学生超过他自己选修课程平均成绩的课程号
SQL 数据查询（5）—— 集合查询、基于派生表的查询、数据更新、视图
ChrisKevin0221的博客
04-16 1565
文章目录集合查询基于派生表的查询数据更新插入数据插入元组插入子查询的结果修改数据删除数据删除一个元组的值删除多个元组的值空值的处理空值的产生空值的算术运算、比较运算和逻辑运算视图建立视图带表达式的视图删除视图查询视图更新视图小结 集合查询 集合操作主要包括：   并操作  UNION   交操作  INTERSECT   差操作  EXCEPT 例3.64：查询计算机科学系的学生及年龄不大于19岁的学生 SELECT * FROM Student WHERE Sdept = 'CS' UNION SELE
数据库系统概论 集合查询、基于派生表的查询
weixin_53329472的博客
07-26 521
使用UNION将多个查询结果合并起来时，系统会自动去掉重复元组。子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象。如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其默认属性。通过FROM子句生成派生表时，AS关键字可以省略，但必须为派生关系指定一个别名。实际上该例子就是查询计算机科学系中年龄不大于19岁的学生，即。实际上该例子就是查询计算机科学系中年龄大于19岁的学生，即。查询结果的列数必须相同。...
SQL数据查询——嵌套查询
SAMSARA的博客
04-25 4671
嵌套查询语法格式带有IN谓词的子查询IN谓词实现交运算IN谓词实现差运算带有比较运算符的子查询带有ANY谓词的子查询带有ALL谓词的子查询相关子查询引用子查询的值不引用子查询的值(EXISTS)基于派生表的查询 语法格式 SELECT 列名表达式 FROM 表名 WHERE 表达式 运算符 (SELECT 子查询) 其中运算符包含IN、关系运算符、ANY、ALL、EXISTS五类 【注意】 1.S...
SQL语言（二）数据更新
Algernon98的博客
11-09 886
SQL中数据更新、空值处理以及视图
SQL Server 数据库原理 实验三 数据高级查询
bearcat303295的博客
06-30 1582
SQL Server实验系列 实验三 数据高级查询 实验目的： 掌握复杂数据查询操作。 掌握各种连接查询、嵌套查询的使用 实验过程： （1）查询每个学生及其选课情况； SELECT Student.Sname, SC.Cno FROM Student,SC WHERE Student.Sno = SC.Sno （2）查询每门课的间接先修课 SELECT c1.cname, c2.cpno FROM course c1, course c2 WHERE c1.cpno = c2.cno （3）将STU.
数据库实验二——SQL查询语言
梦心长存的博客
10-23 5061
日常更新数据库实验报告 我们可以看到，总共有25条要求，老规矩，一个一个来，以实验一的数据为基础 １）查询全体学生的详细记录 select * from student; 效果如图，以下只放代码，不在放图（除非特别重要的） ２）查询不是信息系（IS）、计算机系（CS）的学生性别、年龄、系别 select ssex,sage,sdept from student where not sd...
掌握MySQL派生表实战：关系数据库设计详解
派生表是关系数据库中的一个重要概念，它允许我们在不创建新表的情况下，根据现有表的数据动态生成新的视图，从而简化复杂的查询操作。 章节7.4首先会回顾数据库设计的基础，强调数据库在信息技术中的核心地位，它...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值