Sql Server 07

最新推荐文章于 2022-11-01 11:08:54 发布

柳小茶

最新推荐文章于 2022-11-01 11:08:54 发布

阅读量1.2k

点赞数 2

分类专栏： DB实验文章标签： sql

本文链接：https://blog.csdn.net/bibiobi/article/details/115614752

版权

DB实验专栏收录该内容

24 篇文章 3 订阅

订阅专栏

集合查询

集合操作的种类

并-UNION

交-INTERSECT

差-EXCEPT

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

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

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

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


--用or也一样 就是需要加distinct 才可以得到不重复的值 但 union则不用
select distinct Sno
from SC
where Cno ='1' or Cno='2';

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

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

--注意这里是 or 的关系
select *
from Student
where Sdept = 'CS' or Sage<=19;

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

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

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

例 3.67]查询既选修了课程1又选修了课程2的学生（intersect）

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岁的学生的差集。（except）

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

--查询CS系年龄>19的
select *
from Student
where Sdept ='CS' and Sage>19;

3.4.5基于派生表的查询

子查询不仅可以出现在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;

如果子查询中没有聚集函数，

派生表可以不指定属性列，

子查询SELECT子句后面的列名为其缺省属性。

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

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

3.5 数据更新

3.5.1 插入数据 insert

两种插入数据方式

插入元组

插入子查询结果

可以一次插入多个元组

之前已经学习过 Sql Server 03 已有

[例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.5.2 修改数据

三种修改方式

修改某一个元组的值

修改多个元组的值

带子查询的修改语句

[例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 删除数据

三种删除方式

删除某一个元组的值

删除多个元组的值

带子查询的删除语句

[例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.6 空值的处理

空值就是“不知道”或“不存在”或“无意义”的值。

一般有以下几种情况：

该属性应该有一个值，但目前不知道它的具体值

该属性不应该有值

由于某种原因不便于填写

空值的产生

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

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

--或
insert into SC(Sno,Cno)
values('201215125','1');

注意：因为受到参照完整性约束插入SC表的行必须在Student 里已经存在了并且不可以在SC表中插入重复值

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

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

Q：未有改变？

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

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

空值的约束条件

属性定义（或者域定义）中

有NOT NULL约束条件的不能取空值

加了UNIQUE限制的属性不能取空值

码属性不能取空值

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

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

注：

查询结果不包括缺考的学生 因为他们的Grade值为null。

[例 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.84] 建立信息系学生的视图。

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

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

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

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

行列子集视图

若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这类视图为行列子集视图。

基于多个基表的视图

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

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

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

删除视图

语句的格式：

DROP VIEW <视图名>[CASCADE];

[例3.91 ] 删除视图BT_S和IS_S1

drop view BT_S;
--课本上说 不能这样只删除 S1 
--要删除IS_S1，需使用级联删除，同时删除IS_S2
--DROP VIEW IS_S1 CASCADE;            

drop view IS_S1;
--但是实际直接删除是可以的

3.7.2 查询视图

用户角度：查询视图与查询基本表相同

RDBMS实现视图查询的方法

视图消解法（View Resolution）

l进行有效性检查

l转换成等价的对基本表的查询

l执行修正后的查询

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

select Sno,Sage
from Student 
where Sage<20;

--视图消解
select Sno,Sage
from Student
where Sdept = 'IS' and Sage<20;

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

select *
from S_G
where Gavg>=90;

这样是错误的

聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。

SELECT Sno,AVG(Grade)
FROM     SC
WHERE  AVG(Grade)>=90
GROUP BY Sno;

应改为

SELECT  Sno,AVG(Grade)
FROM  SC
GROUP BY Sno
HAVING AVG(Grade)>=90;

3.7.3 更新视图

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

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

、

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

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

--基本表 也可以
insert
into Student(Sno,Sname,Sage,Sdept)
values(‘200215129 ','赵新',20,'IS' );

[例3.97]删除信息系学生视图IS_Student中学号

delete
from IS_Student
where Sno ='201212129';
--基本表
delete 
from Student
where Sno ='201215129' and Sdept ='IS';

更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新

例3.89定义的视图S_G为不可更新视图。

UPDATE  S_G

SET          Gavg=90

WHERE  Sno= '201215121';

对视图或函数 'S_G' 的更新或插入失败，因其包含派生域或常量域。 因为Gavg并不存在对应的基本表中的实际数据。

柳小茶

关注

2
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
Sql Server 07

集合查询集合操作的种类并-UNION交-INTERSECT差-EXCEPT [例 3.64] 查询计算机科学系的学生及年龄不大于19岁的学生。select *from Studentwhere Sdept = 'CS'unionselect *from Studentwhere Sage <=19; [例 3.65] 查询选修了课程1或者选修了课程2的学生。（union）select Snofrom SC...
复制链接

扫一扫