CRUD —— 增删改查(create、retrieve、update、delete)
同样是增改查,需要区分的是:create/ alter/ drop是对于数据库,表的操作,insert/ update/ delete是对数据的操作。
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 <表名>
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
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');
[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 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';
个人理解:对视图进行操作就是间接对基本表进行操作。
与插入不同的是:删除操作不仅是间接将基本表中的数据删除,视图中的数据也一并删除了