【2019-2020春学期】数据库作业9:SQL练习6 - INSERT / UPDATE / DELETE / NULL / VIEW

数据更新

首先要了解数据更新操作有三种:向表中添加若干行数据、修改表中的数据和删除表中的若干行数据。
插入数据
1、插入元组
关键词 INSERT

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

注意:
1、在定义该属性的时候若说明NOT NULL时,不能取空值,否则会报错。
2、INTO语句中没有说明那些属性列,那在VALUES中要都提及,并且要按照定义的属性次序相同。若没有属性所对应的值,则用NULL。
3、字符串中的内容要用单引号引出
4、不要插入主码相同的数据,否则会报错

例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('201215126','张成民','男',18,'CS');

两例如果如下图:
在这里插入图片描述
例3.71
插入一条选课记录(‘201215128’,‘1’)。

INSERT 
INTO SC(Sno,Cno)
VALUES('201215128','1');

或者

INSERT 
INTO SC
VALUES('201215128','1','NULL');

在这里插入图片描述2、插入子查询的结果
子查询简单来说就是嵌套在SELECT语句中当作条件来使用。

INSERT
INTO<表名>[(<属性列1>[,<属性列2>....])]
子查询;

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

--1、建新表,存放系名和平均值。
CREATE TABLE Dept_age
       (Sdept VARCHAR(15),
	   Avg_age SMALLINT);

--2、存数据
INSERT
INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept;

在这里插入图片描述
修改数据

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

**注意的是:
1、若省略WHERE,则是全部的元组都要修改。
2、子查询也可以嵌套在UPDATE语句中。
**
修改某一个元组的值
例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');

(承上启下tips:在尝试置零或者删除时,一定要慎重,考虑之后还会用吗等之类的问题,或者挑选出感觉影响不大进行置零或者删除的工作,因为我挑选一个没有影响的数据进行工作,所以没有结果,看见运行成功即可,因此这里没有截图,所以还请见谅。)

删除数据(慎重小心删除)

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

**注意的是:
删除要小心,且行且珍惜。。。。。
1、有WHERE语句时,就删除指定部分,没有WHERE语句时,则删除全部元组,但是表的定义仍在字典中。概括就是删的是表中数据,而不是表的定义。
2、子查询也可以出现在DELETE语句中。
3、对某个基本表中数据的增、删、改操作可能会破坏参照完整性。
**

1、删除某一个元组的值
例3.76
删除学号为201215128的学生记录。

DELETE
FROM Student
WHERE Sno='201215128';

2、删除多个元组的值
例3.77
删除所有的学生选课记录。

DELETE
FROM SC;

3、带子查询的删除语句
例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
SET Sdept=NULL
WHERE Sno='201215200';

2、空值的判断
判断是否是空值,用的是IS NULL或者IS NOT NULL。
例3.81
从Student表中找出漏填了数据的学生信息。

SELECT *
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;

在我自己的表中没有空值,因此就不截图了。
3、空值的约束条件
首先定义中有NOT NULL时,就不能取空值;
其次,在加了UNIQUE限制的属性就不能空值;
还有就是,码属性不能取空值。

4、空值的算术运算、比较运算和逻辑运算
1、空值与另外一个值(包括空值)的算数运算的结果为空值
2、空值与另外一个值(包括空值)的比较运算的结果为UNKNOWN.
3、空值与另外一个值(包括空值)的逻辑运算的结果为
在这里插入图片描述注意的是:
在查询语句中,只有WHERE和HAVING子句中的选择条件为TRUE的元组才被选出作为输出结果。

例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 (Grade<60 OR Grade IS NULL) AND Cno='1';

在这里插入图片描述

视图

首先视图的定义:是从一个或者几个基本表(或视图)导出的表,他是虚表,数据库中只存放视图的定义,而不存放视图对应的数据,一旦表中的数据变化了,视图中的数据也会随之改变。
定义视图
建立试图

CREATE VIEW <视图名> [(<列名>[,<列名>]....)]
AS<子查询>(可以是任意的SELECT语句)
[WITH CHECK OPTION](表示对视图进行更新操作时自动.);

注意的是:
1、组成使徒的属性列名或者全部省略(由子查询SELECT目标列中的诸字段组成)或者全部指定(明确指定所有列名),没有第三种选择。
2、下列必须明确指定组成视图的聚集函数或者表达式:
(1)、聚集函数或者表达式
(2)、多表连接时选出了几个同名列作为视图的字段
(3)、需要在视图中为某个列启用新的更合适的名字。
3、关系数据库管理系统执行CREATE VIEW的语句的结果只是把视图的定义存入数据字典中,并不执行其中的SELECT语句。
4、若一个视图是从单个基本表导出的,并且只是去掉了基本表的某些行和某些列,但保留了主码,则称这类视图为行列子集视图。
5、在建立多个基本表的基础上,有重复属性时,一定要标明这个属性是来自于那张表。
6、用带有聚集函数和GROUP BY子句的查询来定义视图,这种视图称为分组视图。

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

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';

这是视图编辑前200行的结果:
在这里插入图片描述
例3.85(建立在一个表上)
建立信息系学生的视图,并要求进行修改和插入操作时仍保证该视图只有信息系的学生。

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;

这样操作后,以后对该视图进行插入、修改和删除操作时,关系数据库管理系统会自动加上Sdept='IS’的条件。
例3.86(建立在多个基本表上)
建立信息系系统选修了1号 课程的学生的视图(包括学号、姓名、成绩)。

CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Sage
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,F_name,F_sex,F_age,F_dept)
AS
SELECT *
FROM Student
WHERE Ssex='女';

在这里插入图片描述(注:F_Student视图的属性列与Student表的属性列一一对应。如果以后修改了基本表Student的结构,则Student表与F_Student视图的映像关系会被破坏,视图就不能正常工作了,因此为避免该情况,最好在修改之后删除这个视图,重新建立)
2、删除视图

DROP VIEW <视图名>[CASCADE];

注意的是:
删除视图是将视图的定义从数据字典中删除。如果视图还导出了其他视图,则使用CASCADE级联删除。删除基本表,不等同于把视图删除掉,删除视图还是要用DROP VIEW

例3.91
删除视图BT_S和视图IS_S1;

DROP VIEW BT_S;
DROP VIEW IS_S1 CASCADE;

注意的是:在MySQL中即使不加CASCADE也可以将IS_S1成功删除,但是还有记住CASCADE这个关键词。

3、查询视图
注意的是:
用户角度:查询视图与查询基本表相同
RDBMS实现视图查询的方法
视图消解法 ----有些情况下,试图消解法不能生成正确的查询。
进行有效性检查
转换成等价的查询

例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视图(例3.89中定义的试图)中查询平均成绩在90分以上的学生学号和平均成绩。

(视图)

SELECT *
FROM S_G
WHERE Gavg>=90;

或(基本表):
(WHERE子句是不能用聚集函数作为条件表达式的)

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

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中插入一个新的学生记录,其中学号为“201215119”,姓名为“赵新”,年龄为20岁。

INSERT
INTO IS_Student
VALUES('201215119','赵新',20);

或者:

INSERT
INTO Student(Sno,Sname,Sage,Sdept)
VALUES('201215119','赵新',20,'IS');

在这里插入图片描述在这里插入图片描述
例3.97
删除信息系学生视图IS_Student中学号为“201215119”的记录。

DELETE
FROM IS_Student
WHERE Sno='201215119';

或者:

DELETE
FROM Student
WHERE Sno='201215119' AND Sdept='IS';

第三章小结:
本章主要讲的数SQL语句的使用,SQL可以分为数据定义、数据查询、数据更新、数据控制四大部分。主要是事件应用,我们将的的关键词分别为SELECT、INSERT、UPDATE、DELETE,主要是他们,在配合FROM或者TO之类的介词,再加上WHERE或者GROUP BY或者HAVING语句,构成的SQL语句。主要就是掌握他们的使用,大体上使用方法是一致的,只要区分开关键词即可,在本章中还有一些小细节需要注意,具体就看之前的博客就行,主要就是掌握SQL语句。

参考:
【1】王珊 萨师煊.数据库系统概论(第5版).高等教育出版社

本次作业写的还是很曲折的,耗时时间长,但是有些题目在之前联系过,关于SQL语句,无非就是增删改查,只要将这四部分弄清楚后,其余的知识点在看一遍,这一张就没有什么大问题了,多写立体还是很有帮助的。好了,我要滚去写下一个了,不多说什么废话了

发布了11 篇原创文章 · 获赞 10 · 访问量 3200
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 数字20 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览