【数据库作业9】SQL练习6 - INSERT / UPDATE / DELETE / NULL / VIEW

CRUD —— 增删改查（create、retrieve、update、delete）
同样是增改查，需要区分的是：create/ alter/ drop是对于数据库，表的操作，insert/ update/ delete是对数据的操作。

• 插入数据——INSERT

INSERT INTO <表名>[(<属性列1>[,<属性列2>...])]
VALUES (<常量1>[,<常量2>...])

[3.69]将一个新学生元组插入到Student表中。

insert into student(sno,sname,ssex,sdept,sage)
values('201215128','陈冬','男','is',18);

[3.70]省去属性列的情况：

insert into student
values('201215176','陈3冬','男',18,'cs');

此时插入值需要与默认属性列相对应。

[3.71]插入一条选课记录

//方法一：此时省略了grade属性列，自动赋值为空。
 INSERT 
 INTO SC(Sno,Cno)
 VALUES('20121521','1');
 //方法二：省略属性列的值，此时属性列赋值若为空，则需要赋值NULL
 INSERT 
 INTO SC
 VALUES('20121521','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;
//查询该表
select * from dept_age

查询结果：需要说明的是：若建表时插入的值有空值，则在插入时会有警告。

• 修改数据——update

UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>...]
[WHERE<条件>]

[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');
select * from sc;

我们选择的是CS系的学生，学号为28的同学不是该专业的，所以分数没有置零。
附：本来想用第二种方法做一下

update sc,student
set grade=0
where student.sno=sc.sno
and sdept='cs'
select * from sc;

但最终查询结果是报错的：

经过查询：一条update只能修改一张表里的字段，但是可以关联多张表去修改。常用的sqlserver格式如下：

update table1 
set a.字段1=b.字段1,....,a.字段N=b.字段N 
from table1 a,table2 b 
where 两个表的关联字段。

因此新的方法为：

update sc
set grade=0
from sc,student
where student.sno=sc.sno
and sdept='cs'
select * from sc;

经过验证是正确的。

• 删除数据——DELETE

DELETE
FROM <表名>
[WHERE <条件>]

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

delete 
from student
where sno='201215128'
select * from student

删除结果是失败的

主要是由于由于要删除的记录有外键约束,无法删除。先删除从表，再删主表才可以。因为目前还有有依赖关系，不给删除。
解决方法，在该语句前加上以下语句：

alter table sc
drop CONSTRAINT FK__sc__sno__52593CB8

删除以前：

删除之后：

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

delete
from sc;

区别于drop，该方法将sc表中数据删除，但sc表还在。

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

delete 
from sc
where sno in(
  select sno
  from student
  where sdept='cs');

• 空值 NULL

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

insert into sc(sno,cno,grade)
values('201215126','1',NULL);

[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 sdept is NULL
or sage is NULL;

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

• 视图

视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
视图的建立：

CREATE VIEW
  <视图名>[(<列名>[,<列名>...])]
AS <子查询>
[WITH CHECK OPTION];

其中，在with check option的选项下，可以总结为
1.update，要保证数据update之后能被视图查询出来，也就是要符合where的条件
2.insert,保证insert的数据能被视图查询出来
3.delete,有无 with check option都一样
4.对于没有where字句的视图，使用with check option是多余的

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

create view is_student
as
select sno,sname,sdept
from student
where sdept='is';

行列子集视图：单个基本表导出，并只是去掉了某些行和某些列，但仍保留主属性的视图。

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

create view is_student
as
select sno,sname,sdept
from student
where sdept='is'
with check option;

末尾加with check option后每次自动更新数据。
[3.86]建立信息系选修了1号课程的学生的视图。

create view is_s1(sno,sname,grade)
as 
 select student.sno,sname,grade
 from student,sc
 where cno='1' and sdept='is'
 and sc.sno=student.sno;

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

create view is_s2
as 
select *
from is_s1
where grade>90;

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

create view bir_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 fe_stu(f_sno,name,sex,age,dept)
as
 select * 
 from student
 where ssex='女';

经过验证，直接在视图中编辑是可以的，但所编辑数据不会在原数据中呈现，即只适用于当前视图：

• 删除视图 —— DROP

DROP VIEW <视图名>[CASCADE];
//从数据字典中删除了视图的定义，如果视图之上还有视图，
//CASCADE可以全部删除。
//删除基表时，由该表导出的视图定义要用DROP VIEW显式删除。

[3.91]删除视图S_G和视图IS_S1;

drop view s_g;//删除成功
drop view is_s1;//删除失败

由于 is_s2视图时基于is_s1建立起来的，因此不能直接将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.94]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩。

//方法一：
select *
from s_g
where gavg>=90;
//方法二：
select sno,avg(grade)
from sc
group by sno
having avg(grade)>=90;
//方法三：
select * 
from (select sno,avg(grade)
  from sc
  group by sno)
  as a_g(sno,gavg)
where gavg>=90;

查询结果是相同的：

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

update is_student
set sname='刘辰'
where sno='201215122';

开始的时候显示0行受影响，主要是因为在is_student视图中是 没有学号为22的学生的，因此需要在该基础上增加。因此我们修改存在的学号。
我们发现不光是视图中的信息修改了，基本表中的信息也随着修改完成。

[3.96]向IS_Student中插入一条新的学生记录（‘201215129’,‘赵新’,20）

insert into is_student
values ('201215130','赵新',20);

但在查询的时候发现，该语句并没有将该条数据成功插入视图，

最后还莫名其妙的发现该语句将数据成功插入到了基本表中
经过查询资料，我明白了，前面即使可以对视图进行编辑，但实际上查询出来的值仍然是开始的值，视图只是个虚拟表，不能添加数据到视图里，但可以通过视图向基表插入数据和更新基表中的数据、删除数据。你的插入数据语句是指向基表中插入数据。而不是在视图里显示出来，你可以看到你的基表里有你插入的数据。

[3.97]删除信息系学生视图中学号为‘201215129’学生的记录

delete 
from is_student
where sno='201215129';

//或者

delete 
from student
where sno='201215129'and sdept='is';

个人理解：对视图进行操作就是间接对基本表进行操作。

与插入不同的是：删除操作不仅是间接将基本表中的数据删除，视图中的数据也一并删除了