数据库——SQL 下

最新推荐文章于 2022-11-14 23:21:06 发布

「已注销」

最新推荐文章于 2022-11-14 23:21:06 发布

阅读量453

点赞数 1

分类专栏：数据库系统概论

本文链接：https://blog.csdn.net/zjuwxx/article/details/90300064

版权

数据库系统概论专栏收录该内容

11 篇文章 0 订阅

订阅专栏

本文详细介绍了SQL中的数据更新操作，包括插入、修改和删除，并深入探讨了视图的定义、创建、查询和更新。视图作为虚表，提供了逻辑独立性和安全性，同时也简化了复杂查询。

摘要由CSDN通过智能技术生成

一、数据更新

数据更新包括插入，删除，修改

1.1插入

1.1.1插入元组

insert into T [(A1, ..., Ak)]
    values (C1, ..., Ck)

/*
T是基本表
A1, ..., Ak是 T的属性
    into子句中属性的顺序可与表中定义的不一致：
        没有指定属性列：表示插入的是一条完整的元组，且属性与表定义中的顺序一致
        指定部分属性列：插入的元组在其余属性列上取空值
C1, ..., Ck是 A1, ..., Ak对应的取值
    values子句中提供的值必须与 into子句匹配
*/

例：插入 Computer Science系开设的名为"Database Systems"的课程 CS-437，4个学分

insert into course
    values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);

--若用户不记得属性顺序，可以在 insert语句中指定属性
--上式等价于
insert into course (title, course_id, credits, dept_name)
    values ('Database Systems', 'CS-437', 4, 'Comp. Sci.');

1.1.2插入子查询结果

insert into T [(A1, ..., Ak)]
    <查询表达式>

/*
T是基本表或者视图
A1, ..., Ak是 T的属性
<查询表达式>是一个 select语句，其目标列必须与 into子句匹配
*/

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

--第一步：建表
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.2修改

update T
    set A1 = C1, ..., Ak = Ck
    [where <修改条件>]

--T是基本表或视图
--A1, ..., Ak是 T的属性
--C1, ..., Ck是表达式

功能

修改指定表中满足 where子句条件的元组
set子句给出的表达式的值用于取代相应的属性的值
如果省略 where子句，表示要修改表中的所有元组

例：对工资低于平均数的教师涨5%的工资

update instructor
    set salary = salary*1.05
    where salary < (
                    select avg(salary)
                    from instructor
                    );

--SQL还提供 case结构进行多选择操作
case
    when pred1 then result1
    when pred2 then result2
    …
    when predn then resultn
    else result0
end


--给工资低于10000美元的教师涨5%的工资，其余涨3%
update instructor
    set salary = case
                    when case <= 10000 then salary*1.05
                    else salary*1.03
                 end

1.3删除

delete from T
    [where <删除条件>]

--T是基本表或视图

功能：删除指定表中满足WHERE子句条件的元组

where子句

指定要删除的元组
缺省表示要删除表中的全部元组，表的定义仍在字典中

delete命令只能作用于一个关系，但是可以通过在 where子句中嵌套子查询引用任意数目的关系

执行任何删除之前先对所有元组进行测试，测试完成后再进行删除操作，防止部分元组提前改变导致标准变动，引起结果错误

例：删除所有在位于 Waston大楼的系工作的教师元组

delete from instructor
where dept_name in (
                    select dept_name
                    from department
                    where building = 'Waston'
                    );

二、视图

特点

虚表，是从一个或几个基本表（或视图）导出的表
只存放视图的定义，不存放视图对应的数据
基表中的数据发生变化，从视图中查询出的数据也随之改变

2.1定义视图

2.1.1建立视图

create view <视图名> [ (<列名>, ..., <列名>]) ]
    as <查询表达式>
    [with check option]

--with check option表示该视图是可更新的，且对视图更新时要满足<查询表达式>的查询条件
--create view是说明语句，它创建一个视图并将视图的定义存放在数据字典中，而定义中的<查询表达式>并不立即执行

组成视图的属性列名：全部省略或全部指定

全部省略

由子查询中SELECT目标列中的诸字段组成

明确指定视图的所有列名

某个目标列是聚集函数或列表达式
多表连接时选出了几个同名列作为视图的字段
需要在视图中为某个列启用新的更合适的名字

例：建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生

--create view IS_Student
as 
    select Sno,Sname,Sage
    from Student
    where Sdept= 'is'
    with check option; --对该视图进行插入、修改和删除操作时，RDBMS会自动加上 Sdept='is'的条件

若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这类视图为行列子集视图，上述 IS_Student视图就是一个行列子集视图。行列子集视图是可更新的

2.1.2删除视图

drop view <视图名> [cascade|restrict]

--从数据字典中删除指定的视图定义
--如果该视图上还导出了其他视图，使用cascade级联删除语句会把该视图和由它导出的所有视图一起删除
--删除基表时，由该基表导出的所有视图定义都必须显式地使用drop view语句删除

2.2查询视图

用户角度：查询视图与查询基本表相同

关系数据库管理系统实现视图查询的方法：视图消解法

进行有效性检查
转换成等价的对基本表的查询
执行修正后的查询

例：在信息系学生的视图中找出年龄小于20岁的学生

select Sno,Sage
from IS_Student
where Sage<20;

--视图消解转换后的查询语句为：
select Sno,Sage       
from Student
where Sdept= 'IS' and Sage<20;

有些情况下，视图消解法不能生成正确的查询

如：在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

select *
from S_G
where Gavg>=90;

--S_G视图的子查询定义： 
create view S_G (Sno,Gavg)
as
    select Sno,AVG(Grade)
    from SC
    --这里不能用 where AVG(Grade)>=90 !!!
    group by Sno
    having AVG(Grade)>=90;

2.3更新视图

视图的更新指通过试图进行增、删、改操作

由于视图都是直接或间接基于基本表定义的，因此基于视图的更新最终要转换成基于基本表的个更新

有些视图可以将更新唯一地转换成对定义它的基本表的更新，成为可更新的视图；有些视图不能将更新唯一地转换成对定义它的基本表的更新，成为不可更新的视图

--将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”
update IS_Student
set Sname= '刘辰'
where Sno= '201215122';

--转换后的语句：
update Student
set Sname= '刘辰'
where Sno= '201215122' and Sdept= 'IS';

/*插入，删除同理*/

允许对行列子集视图进行更新。对其他类型视图的更新不同系统有不同限制