数据库实验第七周【集合查询&数据更新】

最新推荐文章于 2023-04-21 14:48:57 发布
最新推荐文章于 2023-04-21 14:48:57 发布
阅读量3.2k 收藏 33
点赞数 5
分类专栏： 数据库 文章标签： 数据库 mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45745322/article/details/115615012
版权

3.4.4集合查询

并-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';

在这里插入图片描述

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

结果如下：
在这里插入图片描述
但是如果将 Sno 换成 * ，什么都查不出来了就，？？？

select *
from SC
where Cno='1'
intersect
select *
from SC
where Cno='2';

结果：
在这里插入图片描述

原因在这里的：

先来看看这这两个select执行之后这里面有什么：

select * from SC where Cno=‘1’

select * from SC where Cno=‘2’

观察可以知道，上下的结果中只有Sno所属的列中有201215121一个元素是重复的。

如果使用select Sno进行交操作，就会出现一个学生的学号。
但是如果使用select * 进行交操作，这个时候，（Sno，Cno，Grade）就会被看成一个整体，在上下两个表中进行交操作，这是时候没有完全相同的整体集合，就不会出现任何结果了。

嵌套查询方法：

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.4.5 基于派生表的查询

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

在这里插入图片描述

[例]查询所有选修了1号课程的学生姓名。

可以用如下查询完成：

select Sname
from Student,
	(select Sno from SC where Cno='1') as SC1
where Student.Sno=SC1.Sno;

在这里插入图片描述

3.4.6 SELECT语句的一般格式

 SELECT [ALL|DISTINCT]  
   <目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
 FROM     <表名或视图名> [别名] 
                [ ,<表名或视图名> [别名]] …
                |(<SELECT语句>)[AS]<别名>
 [WHERE <条件表达式>]
 [GROUP BY <列名1>[HAVING<条件表达式>]]
 [ORDER BY <列名2> [ASC|DESC]];

3.5 数据更新

3.5.1 插入数据 insert

这个之前就写过了，

数据库【数据索引&插入&查询】

1.插入元组

【例3.69】将一个新学生元组（学号：201215128，姓名：陈冬，性别：男，所在系：IS，年龄：18岁）插入到Student表中。

insert
into Student(Sno,Sname,Ssex,Sdept,Sage)
values('201215128','陈冬','男','IS',18);

在这里插入图片描述

【例3.70】将学生张成民的信息插入到Student表中。

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

在这里插入图片描述

【例3.71】插入一条选课记录（‘201215128’,‘1’）。

在这里插入图片描述
有外键约束，SC表存在一个字段Cno，有外键约束，引用于Course表的主键Cno，那么在向SC表插入数据时，字段Cno必须为Course表中Cno已经存在的值，如果SC中存放一个Cno中没有的值，则会报违反外键约束。

insert
into SC(Sno,Cno)
values('201215128','1');

或者

insert
into SC
values('201215128','1',NULL);

若先在Course表中输入课程信息，就会得到以下结果。
在这里插入图片描述

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;
select * from Dept_age

在这里插入图片描述

3.5.2 修改数据 update

三种修改方式

  • 修改某一个元组的值
  • 修改多个元组的值
  • 带子查询的修改语句

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

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

修改前：
在这里插入图片描述
修改后：
在这里插入图片描述

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

在这里插入图片描述

3.5.3 删除数据 delete

WHERE子句
指定要删除的元组
缺省表示要删除表中的全部元组，表的定义仍在字典中

三种删除方式

  • 删除某一个元组的值
  • 删除多个元组的值
  • 带子查询的删除语句

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

在这里插入图片描述
错误提示：
DELETE 语句与 REFERENCE 约束"FK__SC__Sno__71D1E811"冲突。该冲突发生于数据库"Student"，表"dbo.SC", column ‘Sno’。
语句已终止。

就是与下面这个键发生冲突。
在这里插入图片描述

原因：外键冲突，不能删除父行

解决方法：先找到关联的外键的表，删掉子行数据，再删除父行即可。

所以先删除SC表中关于201215128的行。
代码如下：

delete 
from SC
where Sno='201215128';

删除前：
在这里插入图片描述
删除后：
在这里插入图片描述
此时再次执行最上面的语句：
201215128的信息已成功删除。
在这里插入图片描述

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

delete 
from SC;

结果如下：
在这里插入图片描述

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

delete 
from SC
where Sno in 
	(select Sno
	from Student
	where Sdept='CS');

在这里插入图片描述在这里插入图片描述
删除后：
在这里插入图片描述

3.6 空值的处理

1.空值的产生

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

insert into SC(Sno,Cno,Grade)
values('201215126','1',null);

insert into SC(Sno,Cno)
values('201215126','1');

错误：
消息 547，级别 16，状态 0，第 3 行
INSERT 语句与 FOREIGN KEY 约束"FK__SC__Sno__3864608B"冲突。该冲突发生于数据库"Student"，表"dbo.Student", column ‘Sno’。
语句已终止。

原因：SC表中引用了Student表中的外键Sno，但是Student表中没有201215126该学号的学生。

解决方法：
先在Student表中添加201215126为学号的学生。

insert into Student(Sno)
values('201215126');

插入之后可在Student表中查看，然后再执行插入SC表的操作就不会报错了。
在这里插入图片描述
运行结果如下：
在这里插入图片描述

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

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

先插入该学生，数据如下：
在这里插入图片描述
更新后：
在这里插入图片描述

2. 空值的判断

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

select * 
from Student
where Sname is null or Ssex is null or Sage is null or Sdept is null;

在这里插入图片描述

3. 空值的算术运算

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

select Sno
from SC
where Grade<60 and Cno='1';

[例 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 Sno
from SC
where Cno='1' and (Grade<60 or Grade is null);

在这里插入图片描述

3.7 视图

3.7.1 定义视图

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

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

在这里插入图片描述

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

上一个例题已经创建了IS_Student这个视图，所以我们需要先删除在重建。
删除语句：

drop view IS_Student

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

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

[例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,2014-Sage
from Student;

在这里插入图片描述
视图的查询结果如下：

select * from BT_S

在这里插入图片描述

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

create view S_G(Sno,Gavg)
as
select Sno,avg(Grade)
from SC
group by Sno;

在这里插入图片描述
查询视图结果如下：

select * from S_G

在这里插入图片描述

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

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

在这里插入图片描述
查询视图结果如下：

select * from F_Student

在这里插入图片描述

[例3.91 ] 删除视图BT_S和IS_S1

drop view BT_S;

drop view IS_S1;

在 SQL server 上可以直接运行，不需要考虑cascade的情况。

标准SQL则需要drop view IS_S1 cascade;

3.7.2 查询视图

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

这种嵌套查询也可以的。

select * 
from (select Sno,avg(Grade)
		from SC
		group by Sno) as S_G(Sno,Gavg)
where Gavg>=90;

3.7.3 更新视图

[例3.95] 将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。

update IS_Student
set Sname='刘辰'
where Sno='201215122';

转换后的语句：

update Student
set Sname='刘辰'
where Sno='201215122' and Sdept='IS';

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

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

上面这条语句会报错！！！

消息 550，级别 16，状态 1，第 1 行
试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。
语句已终止。

原因：

视图中加入了 WITH CHECK OPTION 后，以后对视图进行插入、修改和删除操作时，都会对语句的Sdept字段进行判断，如果Sdept=‘IS’，即满足条件，那么才可以进行该语句的操作。否则，即不满足Sdept=‘IS’，也就是说语句中指定的元组的Sdept的值不等于IS，那么就不能进行该操作。

比如该语句的插入，根本没有指定Sdept的值，只插入了数据(Sno,Sname,Sage)，该元组的Sdept值是null，显然是不等于’IS’的，所以就会插入失败，就会显示上面的情况。

啰里啰嗦的，简单的意思就是，有【例3.84】和【例3.85】两种创建IS_Student视图的方式。
with check option;
前者没有这条语句，后者有这条语句。

前者可以成功执行上面的语句，后者不行，会报如上的错误。

但是前者的虽然可以执行，但是并没有插入到视图中，只显示在Student表中。

  • 带有with check option子句，对该视图进行插入、修改和删除操作时，会自动加上Sdept='IS’的条件。
  • with check option子句会在更新数据的时候检查新数据是否符合视图定义中where子句的条件。
insert 
into Student(Sno,Sname,Sage,Sdept)
values('201215129','赵新',20,'IS');

在这里插入图片描述

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

delete 
from IS_Student
where Sno='201215129';

转换为对基本表的更新：

delete 
from Student
where Sno='201215129' and Sdept='IS';

3.7.4 视图的作用

  1. 简化用户的操作
  2. 使用户能以多种角度看待同一数据
  3. 对重构数据库提供了一定程度的逻辑独立性
  4. 对机密数据提供安全保护
  5. 适当的利用视图可以更清晰的表达查询

！心得

这个作业写了好久啊~~~

虽然题目没什么难度，但是好复杂。。。

完结，撒（种）花！！！

在这里插入图片描述

摆烂.MVP
关注
专栏目录
数据库第七周实验——集合查询,空值处理，视图——例题实现
YKYBTao的博客
04-15 1264
集合查询 并操作UNION,交操作INTERSECT，差操作EXCEPT 注意：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同 【例3.64 3.65 3.66 3.67 3.68】 --查询计算机科学系的学生及年龄不大于19岁的学生：并 SELECT * FROM Student WHERE Sdept='CS' UNION SELECT * FROM Student WHERE Sage<=19; --查询选修了课程1或者选修了课程2的学生：并 SELECT Sno FRO
数据库7 集合查询 数据更新
Justafive的博客
04-19 1576
集合查询 集合操作主要包括 UNION 并 INTERSECT 交 和 EXCEPT 差 查询计算机科学系的学生及年龄不大于19岁的学生 SELECT * FROM STUDENT WHERE SDEPT='CS' UNIOM SELECT* FROM STUDENT WHERE SAGE<=19; 例 3.65 查询选修了课程1或者选修了课程2的学生 SELECT * FROM SC WHERE SNO='1' UNION SELECT * FROM SC WHERE SNO='2';
【数据库实验】查询结尾&&数据更新
Chantec的博客
04-14 1242
目录集合查询[例 3.64] 查询计算机科学系的学生及年龄不大于19岁的学生。[例 3.65] 查询选修了课程1或者选修了课程2的学生。[例3.66] 查询计算机科学系的学生与年龄不大于19岁的学生 的交集。[例 3.67]查询既选修了课程1又选修了课程2的学生。[例 3.68] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。基于派生表的查询[例3.57]找出每个学生超过他自己选修课程平均成绩的课程号数据更新数据插入insert[例3.72] 对每一个系，求学生的平均年龄，并把结果
实验二 SQL 语言——SELECT 查询操作(第三部分)
qq_43540354的博客
05-27 1万+
5、 复杂查询 Student、Course、SC三表再 第一部分 中 （1） 查询与“钱横”在同一系学习的学生信息。 （2） 找出同系、同年龄、同性别的学生。 （3） 查询选修了课程名为“数据库系统”的学生学号、姓名和所在系。 （4） 查询至少不学 1002 号和 1004 号课程的学生学号与姓名。 （5） 查询王同学不学的课程的课程号。 （6） 查询其他系中比信息系（IS）所有学生年龄均大的学生名单，并排序输出。 （7） 查询哪些课程只有女生选读。 （8） 查询所有未修 1001 号课程的学生姓名。 （
MongoDB-插入数据insert、insertOne、insertMany、save用法介绍
专注测试领域知识分享和技能提升
07-17 1万+
先针对上篇文章中MongoDB的_id字段含义做一些补充的描述，上篇文章地址：MongoDB-_id字段的含义介绍补充说明:MongoDB是一个分布式的数据库，使用ObjectId类型数据作为主键的话，可以有效避免不同机器写入数据时_id的唯一性。上篇文章中有介绍过ObjectId类型的_id每一位对应的含义。除了之前介绍过的，可以自定义_id的值以外，_id字段的类型除......
数据查询小练习
weixin_46703961的博客
10-31 3193
：数据查询（一） 一、实验目的 掌握SQL程序设计基本规范，熟练运用SQL语言实现数据基本查询，包括单表查询、分组统计查询和连接查询等。 二、实验内容和要求 查询SC表中的全部数据。 查询计算机系学生的姓名和年龄。 查询成绩在70～80分的学生的学号、课程号和成绩。 查询计算机系年龄在18～20岁的男生姓名和年龄。 查询C001课程的最高分。 查询计算机系学生的最大年龄和最小年龄。 统计每个系的学生人数。 统计每门课程的选课人数和最高成绩。 统计每个学生的选课门数和考试总
Mysql学习笔记
qq_61247494的博客
08-27 2119
DDL----数据库操作、表、表字段操作； DML----完成数据的增删改 DQL----查询数据 DCL--数据控制语言（管理数据库用户、访问权限）
数据库实验报告-查询
05-29
### 数据库实验报告知识点解析 #### 实验目的 1. **掌握查询语句的一般格式：** - SQL查询语句的基本结构包括`SELECT`, `FROM`, `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY`等关键词。正确理解和使用这些关键词...
mysql实验训练2 数据查询操作,【学习笔记】温习sql及MySQL的使用（数据库第2次实验）...
weixin_39631316的博客
03-18 932
借着这次实验，复习一下sql语言。实验二：交互式SQL文章目录1.实验目的熟悉数据库的交互式SQL工具。熟悉通过SQL对数据库进行操作。完成作业的上机练习。2.实验工具Windows105.7.22 MySQL Community Server (GPL)3.实验过程实验用例就取教材上最经典的学生-课程的数据表，包括学生表Student，课程表Course，学生选课表SC。3.1 数据定义3.1...
SQL数据的查询和更新实验报告报告.docx
09-27
该实验报告主要介绍了 SQL 数据查询和更新的实验步骤，旨在熟悉 SQL 语句的数据查询语言，并能够对数据库进行单表查询、连接查询、嵌套查询、集合查询和统计查询。 一、单表查询 * 查询的目标表达式可以是所有列、...
数据库实验三：数据查询
知足且坚定，温柔且上进
10-27 5576
实验三 数据查询实验指导 （简单查询和复杂查询） 1．实验目的 （1）对实验一、实验二已建好的student 、course 、sc 表进行查询操作。 （2）充分体会SQL语言的结构化查询功能，让学生充分体会SQL语言的灵活多变，功能强大。 2．实验内容 （1）在实验一和实验二的基础上，进行数据库的查询，充分体会SQL语言的强大功能。简单查询：包含选择和投影的简单单表查询。、包含between、is null等条件的单表查询。包含分组和排序的单表查询、带连接条件的简单多表查询。 （2）嵌套查询 ：使用In
【数据库】基础知识【MySql】
我的的博客哦
05-09 7198
操作对象创建删除修改模式表drop table视图drop view索引drop indexcreate schema < 模式名 > authorization < 用户名 >;//例：为用户zhang创建一个模式test. create schema test authorization zhang;
数据库学习笔记
最新发布
xjf_IT的博客
04-21 256
having count（*）>3（having作用于组，在group by 后面，进行筛选）在order by 的前面。insert into student values（01，‘张飞’，‘男’，20）and、or、not、in、between、like（%任意字符、_单字符）limit 6，3 跳过前面6条数据，再显示3条数据。学生基本信息表（学号，身份证号，姓名）学号是唯一的。第二范式：消除了非主属性对主属性的部分函数依赖。查询既选修了课程1又选修了课程2的学生。查询了1号课程或则2号课程的学生，
mysql查询男女平均年龄_查询计算机系学生的姓名、性别和年龄
热门推荐
weixin_33550394的博客
02-02 1万+
【填空题】用 insert命令向学生表student里插入一条新记录:学号为18007,姓名为:张飞驰,男,20岁,师范系 () into student(sno,sname,sex,sdept) values(‘18007’, ‘张飞驰’‘男’, 20,’ 师范系 ’) ;【判断题】意识不仅是自然界长期发展的产生,而且是社会历史发展的产物。【填空题】1 0. 查询所有选择 C2课程的学生的学号,...
数据库查询语句（书上例题）
x20020402的博客
03-28 1万+
/*[例3.11]向Student表增加“入学时间”列，其数据类型为日期型*/ alter table Student add Student_entrance DATE; /*[例3.12]将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数*/ alter table Student alter column Sage int /*[例3.13]增加课程名称必须取唯一值的约束条件。*/ alter table Course add unique(Cname); /*[例3.14] 将SC
数据库原理及应用习题三
CNSA183_20的博客
07-16 2436
数据库原理及应用习题三及答案
数据库作业7-集合、基于派生表的查询，数据更新，空值处理，视图
dfndj的博客
04-14 828
3.4.4 集合查询 3.4.4 集合查询 三种集合操作： 并-union 交-intersect 差-except 【注】参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。 【例3.64】查询计算机科学系的学生及年龄不大于19岁的学生。（并） select * from Student where Sdept='CS'--选择CS系的学生 union select * from Student where Sage<=19;--选择年龄不大于19的学生 重复元组自动去重，要保.
SQL语句基本操作练习(五)
FDR_Enterprise的博客
03-22 2754
SQL查询必会的几个操作：嵌套查询EXISTS、集合查询、基于派生表的查询
2. 查询所有选修了1号课程的学生姓名
weixin_43939570的博客
05-07 1万+
SELECT sname FROM student WHERE EXISTS ( SELECT * FROM sc WHERE sc.sno = student.sno AND sc.cno = 1 )
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

摆烂.MVP

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值