数据库作业

集合查询

select语句查询结果是元组的集合，所以多个select语句的结果可进行集合操作。集合操作主要包括并操作union、交操作intersect和差操作except。

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

select *
from Student
where Sdept='CS'
union//两个条件取并集
select *
from Student
where Sage<=19;

注：系统会自动去掉重复元组，若要保留重复元组可用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;

它等价于

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

3.67 查询既选修了课程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;

基于派生表的查询

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

数据更新

数据更新有三种，增、删、改

1.插入数据

插入元组语句

insert
into 表名 (列1，列2.....)
values (常量1，常量2......)； 

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('201215128','张成民','男',18,'CS');//没有属性名就要按属性依次插入

3.71 插入一条选课记录(‘201215128’,‘1’)

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

3.72 对每一个系，求学生的平均年龄，并把结果存入数据库

create table Dept_avg(
Sdept char(15),
Avg_age smallint
);--先建新表后查询所需信息插入
insert  
into Dept_avg
select Sdept,avg(cast (Sage as int))--这里Sage是nchar类型，要先用
--									  cast函数转换成整型！！！！！
from Student
group by Sdept
;

2.修改数据

一般格式：
update 表名
set 列名=表达式,列名=表达式…
（where 条件）；

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

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

3.74 将所有学生年龄加一岁

update Student
set Sage=Sage+1;

3.75 将计算机系全体学生成绩置零

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

3.删除数据

一般格式：
delete
from 表名
(where 条件)；

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表中学生号为201215200的学生所属系改为空值

update student
set sdept=null
where sno='201215200';

3.81 从student表中找出填漏了数据的学生信息

select *
from student
where sname is null or ssex is null or sage is null or sdept is null;

空值与另一个值的算术运算结果为空值，空值与另外一个值的比较运算结果为unknown
3.82 找出选修了1号课程不及格的学生

select sno
from sc
where cno='1'and grade<60;

3.83 找出选修1号课程不及格及缺考学生

select sno
from sc
where cno='1'and (grade<60 or grade is null)

;

视图

建立视图一般格式：
create view 视图名（列名，
列名，
…）
as 子查询
with check option；
其中子查询可以是任意的select语句，with check option 表示对视图进行update insert 和delete 操作时要保证更新、插入或删除的行满足视图定义中的谓词条件

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

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

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

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