数据更新
一、插入数据
1、插入元组
①如果在INTO子句中指出表名和属性名,那么属性名可以打乱顺序,同时VALUES子句中值要与属性名相对应。
②如果只在INTO子句中指出表名,不指出属性名,那么VALUES子句的值一定要与CREATE TABLE中属性列的顺序一样。
INSERT
INTO Student (Sno,Sname,Ssex,Sdept,Sage)
VALUES ('201215128','陈东','男','IS','18');
INSERT
INTO Student
VALUES('201215126','张成民','男','18','CS');
INSERT
INTO CS(Sno,Cno)
VALUES('201215128','1');/*新插入的Grade列上自动赋空值*/
/*或者*/
INSERT
INTO CS
VALUES('201215128','1','NULL');/*因为没有指出属性名,所以要明确给出空值*/
2、插入子查询结果
子查询不仅可以嵌套在SELECT语句中用于构造父查询的条件,也可以嵌套在INSERT语句中用于生成要插入的批量数据。
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;/*再把系名和平均年龄存入新表中*/
查询结果:
二、修改数据
1、修改某一个元组的值
UPDATE Student
SET Sage=22
WHERE Sno='201215121';
2、修改多个元组的值
UPDATE Student
SET Sage=Sage+1;
(加上1岁,我又给减回去了,我不允许都老一岁!)
3、带子查询的修改语句
子查询也可以嵌套在UPDATE语句中,用于构造修改的条件。
UPDATE SC
SET Grade=0
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept='CS');
三、删除数据
①DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。(如果省略WHERE语句则表示删除表中的全部元组)
②DELETE语句删除的是表中的数据,而不是关于表的定义。
1、删除某一个元组的值
DELETE
FROM Student
WHERE Sno='201215128';
2、删除多个元组的值
DELETE
FROM SC;
3、带子查询的删除语句
子查询也可以嵌套在DELETE语句中,用于构造执行删除操作的条件。
DELETE
FROM SC
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept='CS');
空值的处理
1、空值的产生
INSERT INTO SC(Sno,Cno,Grade)
VALUES('201215126','1',NULL);/*没有考试成绩取空值*/
/*或者*/
INSERT INTO SC(Sno,Cno)
VALUES('201215126','1');/*没有赋值的属性,值为空值*/
UPDATE Student
SET Sdept=NULL
WHERE Sno='201215200';
另外,外连接也会产生空值,空值的关系运算也会产生空值。
2、空值的判断
判断一个值是否为空值,用IS NULL或IS NOT NULL来表示。
SELECT *
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;
3、空值的约束条件
①属性定义(或者域定义)中NOT NULL约束条件的不能为空值;
②加了UNIQUE的限制的属性不能为空值;
③码属性不能为空值。
4、空值的算术运算、比较运算和逻辑运算
①空值与另一个(空)值的算数运算结果为空值;
②空值与另一个(空)值的比较运算结果为UNKNOWN;
③有了UNKNOWN后,传统的逻辑运算中的二值逻辑就扩展成了三值逻辑。
SELECT Sno
FROM SC
WHERE Grade<60 AND Cno='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);
视图
一、定义视图
1、建立视图
①组成视图的属性列名或全部省略或者全部指定,没有第三种选择;
②如果省略了视图的各个属性列名,则隐含该视图由子查询中SELECT子句目标列中的诸字段组成;
③在下列情况中必须明确指定组成视图的所有列名:
(1)某个目标列不是单纯的属性名,而是聚集函数或者列表达式;
(2)多表连接时选出了几个同名列作为视图的字段;
(3)需要在视图中为某个列启用新的更合适的名字。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Ssex
FROM Student
WHERE Sdept='IS';
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Ssex
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;/*加上该子句后,以后对该视图进行插入、删除和修改操作时,关系数据库管理系统会自动加上Sdept='IS'的条件*/
若一个视图是从单个基本表导出的,并且只是去除了基本表的某些行和某些列,但保留了主码,则称这类视图为行列子集视图。Sdept='IS’视图就是一个行列子集视图。
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';
该例说明视图不仅可以建立在单个基本表上,也可以建立在多个基本表上。
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90;
该例说明视图还可以建立在一个或多个已定义好的视图上,或建立在基本表与视图上。
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2014-Sage
FROM Student;
这里视图BT_S是一个带表达式的视图,视图中的出生年份是通过计算得到的。
还可以用带有聚集函数和GROUP BY的子句的查询来定义视图,这种视图称为分组视图。
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
该例中平均成绩是由聚集函数得到的,所以CREATE VIEW中必须明确定义组成S_G视图的各个属性列名。S_G就是一个分组视图。
CREATE VIEW F_Student(F_sno,name,sex,age,dept)
AS
SELECT *
FROM Student
WHERE Ssex='女';
2、删除视图
①如果要删除的视图上还导出了其他视图,则使用CASCADE级联删除语句把该视图和它导出的所有视图一起删除。
②基本表删除后,又该基本表导出的所有视图均无法使用了,但是视图的定义没有从字典中删除。
DROP VIEW BT_S;/*成功执行*/
DROP VIEW IS_S1;/*拒绝执行*/
拒绝执行是因为IS_S1还导出了IS_S2视图,如果确定要删除,则使用级联删除语句:
DROP VIEW IS_S1 CASCADE;/*删除了视图IS_S1和它导出的所有视图*/
二、查询视图
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;
视图消解:进行有效性检查后,如果存在则取出视图的定义,把定义中的子查询和用户的查询结合起来,转换成等价的对基本表的查询然后再执行修正后的查询。
本例转换后为:
SELECT Sno,Sage
FROM Student
WHERE Sdept='IS' AND Sage<20;
查询结果:
SELECT IS_Student.Sno,Sage
FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND SC.Cno='1';
查询结果:
SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno;
出现语法错误:
聚合不应出现在 WHERE 子句中,除非该聚合位于 HAVING 子句或选择列表所包含的子查询中,并且要对其进行聚合的列是外部引用。
修改后为:
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 S_G(Sno,Gavg)
WHERE Gavg>=90;
派生表只是在语句执行时临时定义,语句执行后该定义即被删除。
三、更新视图
UPDATE IS_Student
SET Sname='刘辰'
WHERE Sno='201215122';
转换后的更新语句为:
UPDATE Student
SET Sname='刘辰'
WHERE Sdept='IS' AND Sno='201215122';
INSERT
INTO IS_Student
VALUES('201215129','赵新',20);
转换为对基本表的更新:
INSERT
INTO Student(Sno,Sname,Sage,Sdept)
VALUES('201215129','赵新',20,'IS');
这里系统自动将系名‘IS’放入VALUES子句中。
DELETE
FROM IS_Student
WHERE Sno='201215129';
转换为对基本表的更新:
DELETE
FROM Student
WHERE Sdept='IS' AND Sno='201215129';
四、视图的作用
①简化用户的操作;
②使用户能以多种角度看待同一数据;
③对重构数据库提供了一定程度的逻辑独立性;
④对机密数据提供安全保护;
⑤适当利用视图可以更清晰的表达查询。