首页 数据库

# 前言

本篇记录学习数据库的第三章，SQL（结构化查询语言）是关系数据的标准语言，也是一个通用的功能性极强的关系性数据库语言，虽然说是查询语言，但是其功能包括但不仅限于数据库数据查询，而是包含了数据库创建，数据库数据的插入，删除，修改和完整性安全性定义等一系列操作。

# SQL 概述

前面也有提到，SQL 是结构化查询语言，是关系数据库的标准语言，一个通用的，功能性极强的关系型数据库语言。这里可能会对什么是结构化查询语言的概念比较懵

结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。

# SQL 的产生

SQL 是 1974 年由 Boyce 和 Chamberlin 提出的，最初叫 sequel，并在 IBM 公司研制的关系数据库管理系统原型 System R 上实现。由于 SQL 简单易学 ， 功能丰富 ，深受用户及计算机工业界欢迎，因此被数据库厂商所采用。经各公司的不断修改、扩充和完善，SQL 得到业界的认可。

目前，没有一个数据库系统能够支持 SQL 标准化的所有概念和特性。同时，许多的厂商对 SQL 基本命令集群进行了不同程度的扩充和修改，使其可以支持更多的功能特性，因此我们在具体使用某个产品的时候，阅读用户手册不可或缺。

# SQL 的特点

  1. 综合统一

    • 集数据定义语言（DDL），数据查询语言（DQL），数据操作语言（DML），数据控制语言（DCL）功能于一体。

    • 可以独立完成数据库生命周期中的全部活动

      包括定义关系模式、插入数据建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作

  2. 高度非过程化

    SQL 只要提出做什么，无须了解存取路径（存取路径的选取由系统自动完成）

  3. 面向集合的操作方式

  4. 意同一种语法结构提供两种使用方法

    （SQL 即是自含式语言，又是嵌入式语言）

  5. 语言简介，易学易用（等于没说【小声哔哔】）

    SQL 中完成核心功能只有 9 个动词

    SQL 功能动词
    数据查询SELECT
    数据定义CREATE，DROP，ALTER
    数据操纵INSERT，UPDATE，DELETE
    数据控制GRANT，REVOKE

# SQL 基本概念

支持 SQL 的关系数据库管理系统同样支持关系数据库的三级模式

  • 外模式对应视图和部分基本表
  • 模式对应基本表
  • 内模式对应存储文件

image-20221130165919850

  1. 基本表

    • 本身独立存在的表

    • SQL 中一个关系就对应一个基本表

    • 一个（或多个）基本表对应一个储存文件

  2. 存储文件

    • 物理结构组成了关系数据库的内模式
    • 物理结构是任意的，对用户透明

  3. 视图

    • 从一个或几个基本表导出的表
    • 数据库中只存放视图的定义没有对应的数据
    • 视图是一个虚表
    • 用户可以在视图上面再定义视图

# 数据定义

因关系数据库支持三级模式结构，其模式、内模式、外模式中的基本对象有模式，表，视图和索引，故 SQL 的数据定义功能包括模式定义，表定义，视图和索引的定义。

image-20221130173020187

注：一个关系数据库管理系统的实例中可以创建多个数据库，一个数据库里可以创建多个模式，一个模式通常包括多个表，视图和索引等数据库对象。

# 模式的定义和删除

定义模式

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>；
create schema <模式名> authorization <用户名>；

至于这里为什么要写两遍，SQL 语言是不区分大小写的，两种都行个人喜欢小写

注：

  1. 如果没有指定<模式名>，则<模式名>隐含为<用户名>
  2. 调用该命令的用户需要有数据库管理员的权限，或者被授予create schema的权限

这里的定义模式其实就是定义了一个命名空间，可以进一步的创建表，视图

CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];
create schema <模式名> authorization <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];

注：这里的[]表示这部分可以没有

例如，如果要为用户 zhang 创建一个模式 test，并且在其中定义一个表 TAB1

CREATE SCHEMA TEST AUTHORIZATION zhang CREATE TABLE TAB1(COL1 SNALLINT,COL2 INT,COL3 CHAR(20));

删除模式

DROP SCHEMA <模式名> <CASCADE|RESTRICT>
drop schema <模式名> <cascade|restrict>

这里尖括号中的两者必选其一，cascade（级联）表示在删除模式的时候会将该模式下的数据库对象都删除，restrict（限制）如果该模式下面已经定义了数据库对象，则拒绝删除语句的执行。

# 基本表的定义、删除与修改

# 定义基本表

使用 create table 语句

CREATE TABLE <表名>
(<列名> <数据类型> [列级完整性约束条件]
[,<列名> <数据类型> [列级完整性约束条件]]
····
);

建立一个学生表Student

CREATE TABLE Student (
	Sno CHAR(9) PRIMARY KEY,/* 表级约束条件～设置主键 */
    Sname CHAR(20) UNIQUE,/* 表级约束条件～取唯一值 */
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
)；

建立一个“课程”表Course

CREATE TABLE Course(
	Cno CHAR(4) PRIMARY KEY,/* 表级约束条件～设置主键 */
    Cname CHAR(40) NOT NULL,/* 表级约束条件～不能为空 */
    Cpno CHAR(4),
    Ccredit SMALLINT,
    FOREIGN KEY (Cpno) REFERENCES Course(Cno)
    /* 表级约束条件，Cpno 是外码，被参照表是 Course，被参照列是 Cno*/
);

注：参照表和被参照表可以是同一个表

建立一个学生选课表SC

CREATE TABLE SC
(
	Sno CHAR(9),
	Cno CHAR(4),
	Grade SMALLINT,
	PRIMARY KEY(Sno,Cno),/* 主码由两个属性组成必须通过表级约束条件来定义 */
    FOREIGN KEY (Sno) REFERENCES Student(Sno),/* 表级约束条件～Sno 是外码，参照表是 Student*/
    FOREIGN KEY (Cno) REFERENCES Course(Cno),/* 表级约束条件～Cno 是外码，参照表是 Couese*/
);

# 数据类型

在上面基本表的定义中提到了数据类型，关系模型中的每一个属性都来自一个域，他的取值必须是域中的值，在 SQL 中用数据类型来实现

image-20221130183148691

image-20221130183201344

# 模式与表

模式与表的关系很简单，每一个表都对应一个模式，一个模式对应多个表

# 修改基本表

使用 ALTER TABLE 来修改基本表

ALTER TABLE <表名>
[ADD [COLUMN] <新列名> <数据类型> [完整性约束条件]]
[ADD <表级完整性约束条件>]
[DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
[DROP CONSTRAINT <完整性约束> [RESTRICT|CASCADE]]
[ALTER COLUMN <列名> <数据类型>];

ADD 用于增加新列、新的完整性约束条件 DROP COLUMN 子句用于删除表中的列，如果指定了 CASCADE 则删除引用了该列的其他对象， DROP CONSTRAINT 用于删除只能怪的完整性约束条件， ALTER COLUMN 子句用于修改原有的列定义

给Student表增加入学时间列，数据类型为日期型

alter table Student add S_entrance DATE;

将年龄的数据类型由字符型改为整型

alter table Student alter cloumn Sage INT;

增加课程名唯一的约束条件

alter table Course add unique(Cname);

# 删除基本表

当一个表不需要的时候我们可以通过

DROP TABLE <表名> [RESTRICT|CASCADE];

删除Student表

drop table Student restrict;

# 索引的建立与删除

索引的目的是加快数据查询的速度

使用 create index 建立索引

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名> [<次序>] [,<列名> [<次序>]]···);
次序中ASC为升序，DESC为降序（默认是ASC）

为学生课程数据库中的Student，Course，SC三个表建立索引，其中Student表按照学号升序建立唯一索引，Course表按照课程号升序建立唯一索引，SC表中按照学号升序和课程号降序建立唯一索引

create unique index Student on Student(Sno);
create unique index Course on Course(Cno);
create unique index SC on SC(Sno ASC,Cno DESC);

注，索引的建立只有建表的人和数据库管理员可以操作，其他的都依靠DBMS自动完成

# 数据查询

接下来开始本篇的重点部分，数据的查询其一般格式为：

SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>]··· FROM <表名或者视图名> [,<表名或者视图名>...] | (<SELECT 语句>) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];

整个 select 语句的含义是，根据 where 子句的条件表达式从 from 子句指定的基本表，视图或派生表中找出满足条件的元组，再按照 select 子句中的目标表达式选出元组中的属性值形成的结果表。

ALL 表示显示所有结果， DISTINCT 表示去除重复的属性， group by 子句，将结果按照 <列名 1> 来进行分组，该属性列值相等的元组为一个组通常会在每组中作用聚集函数。如果有 having 则需要满足指定的条件， order by 的作用是将查询的结果排序。

我的数据库三个表：

image-20221201163315778

image-20221201163333243

image-20221201163346603

查询方式，主要分为下面四种

# 单表查询

查询仅涉及一个表，查询中常需要一些条件，下表中总结了常用的查询条件

image-20221201140948099

  1. 查询表中的若干列

    • 类似投影

    • 查询全体学生的学号和姓名 （查询指定列）

      select Sno,Sname from Student;

      image-20221201132002432

    • 查询全体学生的详细记录 （查询全部列）

      select * from Student;/* 这里的 * 代表查询所有 */

      image-20221201132332402

    • 查询全体学生的姓名及出生年月 （查询经过计算的值）

      <font color="red">select 子句中的 < 目标列表达式 > 不仅可以是属性列，也可以是表达式，还可以是支付穿常量，函数等 </font>

      select Sname,2022-Sage from Student;

      image-20221201132913026

    • 查询全体学生姓名，出生年月和所在院系，使用小写字母表示系名

      select Sname,'出生年：',2022-Sage,LOWER(Sdept) from Student;/* 这里 LOWER 表示转化为小写，对应的有 UPPER 转化为大写 */

      image-20221201134153565

      <font color=blue> 这里字符串用单引号包裹 </font>

    • 对上一个例子的查询结果进行别名
      用 as 进行别名（<font color=red> 可以省略 </font>）

      select Sname AS NAME,'出生年：',2022-Sage BIRTHDAY,UPPER(Sdept) DEPARTMENT from Student;

      image-20221201135923229

  2. 选择表中的若干行（元组）

    • 类似选择

    • 查询选修了课程的学生学号 （消除重复行）

      select DISTINCT Sno from SC;

      image-20221201140711800

    • 接下来通过 where 子句使用查询条件进行查询

      1. 比较大小

        • 查询计算机科学系全体学生的姓名

          select Sname from Student where Sdept='CS';

          image-20221201142040007

        • 查询所有年龄在20岁一下的学生姓名及其年龄

          select Sname,Sage from Student where Sage!>20;

          image-20221201142007098

        • 查询考试成绩没有90的学生学号

          select DISTINCT Sno from SC where Grade<=90;

          image-20221201142333099

      2. 确定范围

        使用谓词 BETWEEN ....AND... 确定范围

        • 查询年龄在 20~23 岁，包括 20 和 23 岁之间的学生的姓名系别和年龄

          select Sname,Sdept,Sage from Student where Sage between 20 and 23;

          image-20221201142947029

        • 查询年龄不在 20~23 岁，之间的学生的姓名系别和年龄

          select Sname,Sdept,Sage from Student where Sage not between 20 and 23;

          image-20221201143028072

      3. 确定集合

        使用谓词 IN 来查找属性值属于指定集合的元组

        • 查询信息系（IS）、数学系（MA）和计算机科学性（CS）学生的姓名和性别。

          select Sname,Ssex from Student where Sdept IN('IS','MA','CS');

          image-20221201143628640

        • 查询即不是信息系，数学系，也不是计算机科学系的学生的姓名和性别

          select Sname,Ssex from Student where Sdept not IN('IS','MA','CS');

          image-20221201143815063

      4. 字符匹配

        使用谓词 like 来进行字符串匹配

        [NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>']/* 用 % 匹配任意字符，用_匹配任意单个字符 */

        • 查询学号为201215121的学生的详细情况 （字符串为固定字符）

          select * FROM Student where Sno LIKE '201215121';

          image-20221201162259416

        • 查询所有刘姓学生的姓名，学号，性别 （匹配串为含通配符的字符串）

          select Sname,Sno,Ssex FROM Student where Sname LIKE '刘%';

          image-20221201162905296

          查询姓”欧阳“，且全名为三个汉字的学生的姓名

          select Sname FROM Student where Sname LIKE '欧阳_';

          image-20221201163127358

        • 查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况 （特殊字符转义）

          select * FROM Course where Cname LIKE 'DB\_%i__' ESCAPE '\'; /*escape表示'\'为转义字符*/

          image-20221201164143639

      5. 涉及空值的查询

        • 某些学生选修课程后没有参加考试，所以有选课记录，但是没有成绩，查询缺少成绩的学生的学号和相应的课程号

          select Sno,Cno FROM SC where Grade is NULL;

          image-20221201164934434

        • 查询所有有成绩的学生信息

          select Sno,Cno FROM SC where Grade is NOT NULL;

          image-20221201165045093

      6. 多重条件查询

        使用 and，or 来连接条件，and 的优先级高于 or

        • 查询计算机系年龄在20岁一下的学生姓名

          select Sname FROM Student where Sage<=20 and Sdept='CS';

          image-20221201172127267

  3. order by 子句

    • 对选择结果排序

    • 查询选修了2号课程的学生的学号及成绩，查询结果按照分数的降序排列

      select Sno,Grade FROM SC where Cno='2' Order BY Grade DESC;

      image-20221201222545664

    • 查询全体学生情况，查询结果按所在系的系名降序排列，同一系中的学生按年龄的升序排列

      select * from Student ORDER BY Sdept DESC,Sage;

      image-20221201223148145

  4. 使用集合函数

    COUNT(*)					统计元组个数
    COUNT([DISTINCT|ALL] <列名>) 	统计一列中值的个数
    SUM([DISTINCT|ALL] <列名>)	统计一列值的总和（此列必须是数值型）
    AVG([DISTINCT|ALL] <列名>)	计算一列值的平均值（此列必须是整数型）
    MAX([DISTINCT|ALL] <列名>)	求一列中的最大值
    MIN([DISTINCT|ALL] <列名>)	求一列中的最小值

    • 又称级函数，或者聚集函数，或组函数

    • 查询学生的总人数

      select COUNT(*) from Student;

      image-20221201223742434

    • 查询选修了课程的学生人数

      select COUNT(DISTINCT Sno) from SC;

      image-20221201224035853

    • 计算2号课程的学生的平均成绩

      select AVG(Grade) from SC WHERE Cno='2';

      image-20221201225221482

  5. 分组（group by 子句）

    • 对查询结果分组，将查询的结果再次分组

    • 求各个课程号及其对应的人数

      select Cno,COUNT(Sno) FROM SC GROUP BY Cno;

      image-20221201231512674

    • 查询选修了一门以上的课程的学生的学号

      SELECT Sno from SC GROUP BY Sno HAVING COUNT(*)>1;

      image-20221201231859584

# 连接查询

前面的都只在一个表中进行查询，而连接查询同时涉及 <font color=red> 多个表 </font>

[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>;#常见的比较运算符主要 =,>,<,>=,<=,<>,!=,!>,!<
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名3>.]<列名3>;

<font color=red> 比较的两个条件名字可以不同，但是数据类型要是相同的 </font>

  1. 等值与非等值连接查询

    • 等值连接

      连接运算符为 =

      • 查询每个学生及其选修课程的情况

        select Student.*,SC.* FROM Student,SC where Student.Sno=SC.Sno;

        image-20221202103658842

      • 对上一个例子使用自然连接完成

        select Student.*,SC.Cno,SC.Grade FROM Student,SC where Student.Sno=SC.Sno;#区别是去掉了比较的重复列

        image-20221202103938455

    • 自身连接

      • 查询每一门课的间接先修课 （即先修课的先修课）

        select table1.Con,table2.Cpno from Course table1,Course table2 where table1.Cpno=table2.Con;#这里需要对表进行别名使其自身相比

        image-20221202105430451

        <font color=red> 注，自身连接由于属性名相同，因此必须要用别名相互区别 </font>

    • 多表连接

      顾名思义就是俩个以上的表进行连接

      • 查询每个学生的学号姓名选修的课程名及成绩

        select Student.Sname,Course.Cname,SC.Grade FROM Student,Course,SC WHERE Student.Sno=SC.Sno and SC.Cno=Course.Con;

        image-20221202115556674

# 嵌套查询

在 SQL 语言中每一个 SELECT-FROM-WHERE 为一个查询块将一个查询块嵌套到另一个查询块中的 where 或者 having 子句中查询称为嵌套查询

SQL 的结构化的含义就是多以层层嵌套的方式来构造程序

  1. 带有 IN 谓词的子查询

    • 查询与“刘留”在同一个系的学生

      • <font color=blue> 先查询 “刘留” 所在的系 </font>

        SELECT Sdept FROM Student where Sname='刘留';

        image-20221202124310325

      • <font color=blue> 在查询所有在 CS 系的学生 </font>

        SELECT Sname FROM Student where Sdept='CS';

        image-20221202125011126

      • <font color=blue> 将两个语句嵌套完成 </font>

        SELECT * FROM Student where Sdept in (SELECT Sdept FROM Student where Sname='刘留');

        image-20221202125420911

        子查询的条件不依赖父查询，称为不相干子查询

    • 查询选修了课程名为“信息系统”的学生学号和姓名

      SELECT Sno,Sname FROM Student WHERE Sno in (select Sno FROM SC where Cno in (select Con from Course where Cname='信息系统'));

      image-20221202131341962

  2. 带有比较运算符的子查询

    • 找出每个学生超过他自己选修课程平均成绩的课程号

      SELECT Sno,Cno FROM SC x WHERE Grade>=(SELECT AVG(Grade) from SC y WHERE y.Sno=x.Sno);

      image-20221202133356393

  3. 带有 AND（SOME）或 ALL 谓词的子查询

    • 查询非计算机科学系中比任意一个计算机科学系的学生年龄都小的学生的姓名和年龄

      select Sname,Sage FROM Student WHERE Sage<ANY(select Sage FROM Student WHERE Sdept='CS') and Sdept<>'CS';#小于任意一个即可

      image-20221202134147599

    • 查询非计算机科学系中比任意一个计算机科学系的学生年龄都小的学生的姓名和年龄

      select Sname,Sage FROM Student WHERE Sage<ALL(select Sage FROM Student WHERE Sdept='CS') and Sdept<>'CS';#小于所有

      image-20221202134555583

  4. 带有 EXISTS 谓词的子查询

    <font color=red> 带有 EXISTS 谓词的子查询不返回数据只返回逻辑真值与逻辑假值 </font>

    • 选修了一号课程的学生的姓名

      SELECT Sname from Student WHERE EXISTS(select * from SC where Cno='1' AND
      Student.Sno=Sno);

      image-20221202141805285

# 集合查询

集合操作的种类：

  1. 并操作 UNION
  2. 交操作 INTERSECT
  3. 差操作 EXCEPT

<font color=red> 注：参加集合操作的各查询结果的列数必须相同；对应的数据类型也必须相同 </font>

  1. 查询计算机科学系的学生及年龄不大于19岁的学生

    select * from Student where Sdept='CS' union select * from Student where Sage<=19;#union 将多个查询结果合并起来，系统自动去掉重复元组
    #union all 将多个查询结果合并起来，保留重复元组

    image-20221203132840712

  2. 查询选修了课程1或者选修了课程2的学生

    select  DISTINCT * from Student,SC where Student.Sno=SC.Sno and SC.Cno='1' UNION select  DISTINCT * from Student,SC where Student.Sno=SC.Sno and SC.Cno='2';

    image-20221203133748443

# 基于派生表的查询

子查询不仅可以出现在 where子句 中，还可以出现在 from子句 中，这时子查询生成的 临时派生表 成为主查询对象

查询每个学生超过他自己选修课程平均成绩的课程号

select Sno,Cno from SC,(select Sno,avg(Grade) from SC GROUP BY Sno) as Avg_sc(avg_sno,avg_grade) WHERE Sno=avg_sno and Grade>=avg_grade;

image-20221203142017891

查询所有选修了1号课程的学生姓名

SELECT Sname from Student,(SELECT Sno FROM SC WHERE Cno='1') SC1 WHERE Student.Sno=SC1.Sno;

image-20221203142502097

# 数据更新

# 插入数据

  1. 插入一个元组

    语句格式：

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

    将一个新学生元组（学号：201215128）；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。

    INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215128','陈东','男','IS',18);

    image-20221203204704729

    image-20221203204727631

    插入一条选课记录（'201215128','1')

    INSERT into SC(Sno,Cno,Grade) VALUES('201215128','1',null)
    INSERT INTO <表名> [(<属性列1> [,<属性列2>...)] 子查询：

    image-20221203205007471

    image-20221203205018798

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

    /* 先建表 */create table Dept_age (Sdept char(15),avg_age SMALLINT);

    image-20221203205512524

    INSERT into Dept_age(Sdept,avg_age) select Sdept,AVG(Sage) from Student GROUP BY Sdept;

    image-20221203205748078

    image-20221203205811314

# 修改数据

基本格式：

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

  1. 将学生201215121的年龄改为22岁

    UPDATE Student SET Sage=22 where Sno='201215121';

    image-20221203215806818

  2. 將所有學生年齡增加一歲

    UPDATE Student SET Sage=Sage+1;

    image-20221203215914475

  3. 将计算机系的全体学生的成绩置0

    UPDATE SC set Grade=0 WHERE Sno in (select Sno FROM Student where Sdept='CS');

    image-20221203220428813

# 删除数据

语句格式

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

删除元组

  1. 删除一个元组的值

    • 删除学号为201215128的学生记录

      DELETE from Student where Sno='201215128';

      image-20221203220934710

  2. 删除多个元组的值

    • 删除所有学生的选课记录

      DELETE from SC;

      image-20221203221028823

  3. 带子查询的删除语句

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

      DELETE from SC WHERE Sno in (select Sno from Student where Sdept='CS');

      image-20221203221226462

# 空值的处理

  1. 空值的产生

    • 将Student表中学生号为201215125的学生所属的系改为空值

      update Student SET Sdept=NULL where Sno='201215125';

      image-20221203221622264

  2. 空值的判断

    • 从Student表中找出信息填漏了的学生信息

      select * from Student where  Sno is NUll or Sname is null or Ssex is null or Sage is null or Sdept is null;

      image-20221203222101262

  3. 空值的约束条件

    • 定义为 not null
    • unique
    • 主码

  4. 空值的运算

    • 找出选修了一号课程不及格的学生以及缺考的学生

      select * from SC where (Grade<=60 AND Cno=1) or (Grade is null and Cno=1)

      image-20221203222726794

# 视图

特点：

  • 虚表，是从一个或者多个基本表或者视图导出的表
  • 只能存放视图的定义，不存放对应的数据
  • 基表中的数据发生变化，从视图中查询出来的数据也发生变化

# 定义视图

语句格式：

CREATE VIEW <视图名> [(<列名> [,<列名>...)] AS <子查询> [WITH CHECK OPTION];# [WITH CHECK OPTION] 表示对视图进行增删改查操作的时候要保证修改的行满足视图定义中的谓词条件（也就是子查询表达式中的条件）

下面三种情况需要指出组成视图的所有列名

  • 某个目标列不是单纯的属性名，而是聚集函数或者列表达式
  • 多表连接的时候选出了同名列作为视图的字段
  • 需要在视图中为某个列启用新的更合适的名字

  1. 基于单表

    • 创建计算机系学神的视图

      CREATE VIEW CS_Student AS SELECT * from Student where Sdept='CS';

      image-20221203224115912

    • 使得上面的视图在修改时只能加入计算机系的学生

      CREATE VIEW CS_Student AS SELECT * from Student where Sdept='CS' WITH CHECK OPTION;

  2. 基于多表

    • 建立计算机系选修了一号课程的学生视图

      CREATE VIEW SC_Student1 AS SELECT Student.* from Student,SC where Sdept='CS' and Cno='1' and Student.Sno=SC.Sno;

      image-20221203225330680

  3. 基于视图

    建立计算机系选修了1号课程且成绩在90分以上的学生的视图

    CREATE VIEW SC_Student2 AS SELECT * from SC_Student1 where Student.Grade>=90;

  4. 带表达式的视图

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

    create VIEW BT_S(Sno,Sname,Sbirth) AS select Sno,Sname,2022-Sage FROM Student;

    image-20221203231530346

  5. 分组视图

    将学生的学号及他的平均成绩定义为一个视图

    CREATE VIEW S_G(Sno,Gavg) AS SELECT Sno,Avg(Grade) FROM SC GROUP BY Sno;

    image-20221203231831066

  6. 删除视图

    语句格式：

    DROP VIEW <视图名>;

    删除视图BT_S

    DROP VIEW BT_S;

    image-20221203232031229

# 查询视图

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

在计算机系学生的视图中找到年龄小于22岁的学生

SELECT Sno,Sage FROM CS_Student WHERE Sage<=22;

image-20221203232531066

# 更新视图

操作计算机系学生视图CS_Student,并将学号201215122的学生姓名改为“刘辰”

UPDATE CS_Student SET Sname='刘辰' WHERE Sno='201215122';

image-20221203232829223

# 视图的作用

  1. 视图能够简化用户的操作
  2. 视图使用户能以多种角度看待同一数据
  3. 视图对重构数据库提供了一定程度的逻辑独立性
  4. 视图能够对机密数据提供安全保护
  5. 适当的利用视图可以更清晰的表达查询