mysql 查询平均年龄

转载

烂漫树林 2024-11-29 13:50:36

文章标签 mysql 查询平均年龄数据库 mysql sql Sage 文章分类 MySQL 数据库

3.4.4集合查询

并-UNION
交-INTERSECT
差-EXCEPT

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

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

mysql 查询平均年龄_sql

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

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

mysql 查询平均年龄_mysql_02

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

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

mysql 查询平均年龄_mysql_03

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

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

结果如下：

mysql 查询平均年龄_sql_04

但是如果将 Sno 换成 * ，什么都查不出来了就，？？？

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

结果：

mysql 查询平均年龄_Sage_05

原因在这里的：

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

select * from SC where Cno=‘1’
select * from SC where Cno=‘2’

mysql 查询平均年龄_sql_06

观察可以知道，上下的结果中只有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;

mysql 查询平均年龄_Sage_07

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;

mysql 查询平均年龄_mysql 查询平均年龄_08

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

可以用如下查询完成：

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

mysql 查询平均年龄_mysql_09

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);

mysql 查询平均年龄_mysql_10

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

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

mysql 查询平均年龄_sql_11

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

mysql 查询平均年龄_mysql 查询平均年龄_12

有外键约束，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表中输入课程信息，就会得到以下结果。

mysql 查询平均年龄_数据库_13

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

mysql 查询平均年龄_sql_14

3.5.2 修改数据 update

三种修改方式

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

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

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

修改前：

mysql 查询平均年龄_mysql 查询平均年龄_15

修改后：

mysql 查询平均年龄_mysql 查询平均年龄_16

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

update Student
set Sage=Sage+1;

修改前：

mysql 查询平均年龄_mysql 查询平均年龄_17

修改后：

mysql 查询平均年龄_mysql 查询平均年龄_18

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

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

mysql 查询平均年龄_数据库_19

3.5.3 删除数据 delete

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

三种删除方式

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

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

mysql 查询平均年龄_mysql 查询平均年龄_20

错误提示：

DELETE 语句与 REFERENCE 约束"FK__SC__Sno__71D1E811"冲突。该冲突发生于数据库"Student"，表"dbo.SC", column ‘Sno’。

语句已终止。就是与下面这个键发生冲突。

mysql 查询平均年龄_sql_21

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

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

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

delete 
from SC
where Sno='201215128';

删除前：

mysql 查询平均年龄_Sage_22

删除后：

mysql 查询平均年龄_Sage_23

此时再次执行最上面的语句：

201215128的信息已成功删除。

mysql 查询平均年龄_Sage_24

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

delete 
from SC;

结果如下：

mysql 查询平均年龄_sql_25

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

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

mysql 查询平均年龄_数据库_26

mysql 查询平均年龄_Sage_27

删除后：

mysql 查询平均年龄_mysql 查询平均年龄_28

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表的操作就不会报错了。

mysql 查询平均年龄_数据库_29

运行结果如下：

mysql 查询平均年龄_数据库_30

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

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

先插入该学生，数据如下：

mysql 查询平均年龄_sql_31

更新后：

mysql 查询平均年龄_sql_32

2. 空值的判断

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

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

mysql 查询平均年龄_数据库_33

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);

mysql 查询平均年龄_Sage_34

3.7 视图

3.7.1 定义视图

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

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

mysql 查询平均年龄_mysql_35

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

mysql 查询平均年龄_数据库_36

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

create view IS_S2
as
select Sno,Sname,Grade
from IS_S1
where Grade>=90;

mysql 查询平均年龄_mysql 查询平均年龄_37

[例3.88] 定义一个反映学生出生年份的视图。

create view BT_S(Sno,Sname,Sbirth)
as
select Sno,Sname,2014-Sage
from Student;

mysql 查询平均年龄_mysql_38

视图的查询结果如下：

select * from BT_S

mysql 查询平均年龄_sql_39

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

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

mysql 查询平均年龄_mysql 查询平均年龄_40

查询视图结果如下：

select * from S_G

mysql 查询平均年龄_mysql_41

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

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

mysql 查询平均年龄_Sage_42

查询视图结果如下：

select * from F_Student

mysql 查询平均年龄_mysql_43

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

mysql 查询平均年龄_Sage_44

下面这个答案可以得到相同结果

视图消解转换后的查询语句为

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

mysql 查询平均年龄_mysql 查询平均年龄_45

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

delete 
from IS_Student
where Sno='201215129';

转换为对基本表的更新：

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

3.7.4 视图的作用

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

！心得

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

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

完结，撒（种）花！！！

mysql 查询平均年龄_sql_46

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

上一篇：消息队列传输文件

下一篇：ios 用户协议窗口

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

发布评论

相关文章

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

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