第三章 关系数据库标准语言SQL

3.1 SQL概述

3.1.1 SQL的产生与发展

1）SQL的概念

SQL语言是结构化查询语言，介于关系代数和关系演算之间的语言，是关系数据库的标准语言。

2）SQL的标准

3）SQL的应用、SQL的命令

⑴数据定义语言(DDL)

创建、修改或删除数据库中各种对象，包括 SQL数据库、模式、基本表、视图、索引等。

⑵查询语言(QL)

按照指定的组合、条件表达式或排序检索 已存在的数据库中数据，不改变数据库中数据。

命令：SELECT    FROM    WHERE

(3)数据操纵语言(DML)

对已经存在的数据库进行元组的插入、删除、 修改等操作。

命令：INSERT、UPDATE、DELETE

⑷数据控制语言(DCL)

用来授予或收回访问数据库的某种特权，控制事务的提交和回滚。

命令：GRANT、REVOKE、COMMIT、ROLLBACK

3.1.2 SQL的特点

1）SQL的特点

(1)   功能强大

(2)   语言简洁、易学易用

（3）同一语法结构提供两种使用方式

（4） 高度非过程化

（5）面向集合的操作方式

2）SQL对三级模式的支持

 

 

3.2 学生课程数据库

学生表：S(sno,sname,age,sex)

课程表：C(cno,cname,tname)

学生选课表：SC(sno, cno,grade)

三表之间联系：

3.3 数据定义

3.3.1 数据库和模式的定义

1）数据库的创建和删除

(1)   创建数据库：

CREATE DATABASE database_name [      ………]

例：  CREATE DATABASE  student；

(2)   修改数据库：

ALTER DATABASE database_name

(3)   删除数据库：

DROP DATABASE database_name

例:  DROP DATABASE student；

2）模式的创建和删除

创建SQL模式即定义一个DB下的命名空间， 在这个空间中可进一步定义该模式包含的各个数据库对象。

(1)创建模式

CREATE SCHEMA ＜模式名> AUTHORIZATION <用户名>

（2）删除模式

DROP SCHEMA〈模式名〉[CASCADE|RESTRICT]

备注：CASCADE （连锁）方式；RESTRICT （约束，默认）方式

首先要建立用户自己的数据库。一个数据库中可以建立多个模式，用户不指定，系统会自动建立与用户名相同的模式名，并将其后建立的基本表、索引等数据库对象建立在默认模式中。

一个模式下可以包括多个表、视图和索引等数据库对象，可以用数据库模式表(DATABASE.SCHEMA.TABLE) 来引用这些数据库对象。

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

1）基本表的创建

CREATE TABLE〈基本表名〉（<列名＞＜数据类型>［列级完整性约束条件］,［＜列名 ＞＜数据类型＞［列级完整性约束条件］］……［,表级完整性约束条件］）；

常用完整性约束

-主码约束：  PRIMARY KEY

-唯一性约束：UNIQUE

-非空值约束：NULL/NOTNULL

-参照完整性约束：FOREIGN KEY REFERENCES

-检查子句：CHECK

★这些保留字不能用作表名、列名等。

例题：

建立学生关系模式S(sno,sname,age,sex)

CREATE TABLE S

(sno char(4) PRIMARY KEY sname char(8) NOT NULL, age smallint, sex char(2),

PRIMARY KEY(sno));

建立课程关系模式C(cno,cname,tname)

CREATE TABLE C

(cno char(4) NOT NULL, cname char(8) NOT NULL, tname char(10), PRIMARY KEY(cno));

建立选课关系模式SC(sno,cno,grade)

CREATE TABLE SC

(sno char(4) NOT NULL, cno char(4) NOT NULL, grade smallint, PRIMARY KEY (sno,cno), FOREIGN KEY(sno)REFERENCES S(sno), FOREIGN KEY(cno)REFERENCES C(cno), CHECK( (grade IS NULL) OR

(grade BETWEEN 0 AND 100))

);

2）基本表的修改

(1)   对表增加列

ALTER TABLE ＜表名>ADD〈列名〉<数据类型>[NULL|NOTNULL],...;

ALTER TABLE S ADD addr CHAR(20);

★如果原表中已存在数据，则新定义的列必须设置为NULL,表中原有元组在新增加的列上 的值均被定义为空值。

(2)撤销原有的列

ALTER TABLE〈表名〉DROP COLUMN〈列名〉;

 ALTER TABLE S DROP COLUMN addr;

（3）修改原有的类型

ALTER TABLE〈表名〉ALTER COLUMN〈列名〉;

ALTER TABLE S ALTER addr CHAR(10);

3）基本表的删除

DROP TABLE ＜表名>[CASCADE|RESTRICT]

★ CASCADE（连锁,即与表相关的均删除）；RESTRICT（约束）

例如：DROP TABLE SC CASCADE;

当删除表时，表的数据、表上建立的索引和视 图都自动被删除。

3.3.3 索引的定义

1）索引的使用

建立索引是加快查询速度的有效手段。

(1)建立索引

-DBA或表的属主(即建立表的人)根据需要建立。

-有些DBMS自动建立以下列上的索引。

•      PRIMARY KEY

•      UNIQUE

(2 )维护索引

-索引建立后由DBMS自动维护，不需用户干预。

(3)使用索引

-DBMS自动选择是否使用索引以及使用哪些索引。

2）索引的创建

CREATE [UNIQUE] [CLUSTER] INDEX<索引名> ON〈基本表名〉（<列名1>[ASC|DESC]

[,<列名 2 >[ASC|DESC]]…）

备注：

UNIQUE表示每一个索引值只对应唯一的数据记录。

CLUSTER表示建立的索引是聚簇索引，索引值相同或相邻的数据记录存放在连续的物理块中。 

例1：CREATE UNIQUE INDEX SI ON S(sno)// UNIQUE：唯一性索引，SI：索引名

例2:

CREATE UNIQUE INDEX SC_INDDEX1 ON SC(sno ASC, cno DESC)

缺省时，表示升序：ASC不写表示升序

例3：

CREATE CLUSTER INDEX SC_INDDEX2 ON SC(grade)

CLUSTER聚簇索引，相邻成绩存放在同一数据块中

3）索引的删除

语法：DROP INDEX〈表名.索引名〉

例: DROP INDEX SC.SC_INDDEX1 或

DROP INDEX SC_INDDEX1 on SC

删除索引时，系统会同时从数据字典中删除 有关该索引的描述。

3.4数据查询

3.4.1 单表查询

只涉及一个 表的查询称为单表查询

1）简单的单表查询

（1）查询语句的基本结构

SQL的查询语句基本语法结构: SELECT- FROM-WHERE

SELECT Al,A2,...,An   //列名

FROM Rl,R2,...,Rn    //来源的表

WHERE〈查询条件表达式〉;  

//三条语句顺序不可改变

 （2）选择表中的列 SELECT

1.查询指定列(或全部列)

例1：查看学生全部信息

SELECT sno,sname,age,sex FROMS； 或：SELECT * FROMS；

例2：查询全部学生的学号和姓名。

SELECT sno,sname FROM S ；

例3：查询全部学生的姓名和学号。

SELECT sname,sno FROM S ；

2. 查询经过计算的列

例4：查询学生学号、姓名和出生年份。

SELECT sno,sname, 2019-age as 出生年份 FROM S ;

as …… 可为新的属性取名

（3）选择表中的行

1. 取消值重复的行(DISTINCT)

SELECT DISTINCT sno FROM SC;

2. 查询满足条件的元组(WHERE)

查询满足条件的元组是通过WHERE子句实现

在WHERE子句中常用的查询条件如表所示:

例如：查询女学生的学号、姓名和年龄。

SELECT sno,sname,age

FROM S

WHERE sex=‘女'；

例如：

查询所有年龄大于19岁的女生的姓名和学号。

SELECT sname,sno

FROM S

WHERE age>19 and sex=‘女'；

（4）排序 ORDER BY

查询学生学号、姓名和出生年份，并按出生年份的升序排列，出生年份相同时，按学号的降序排列。

SELECT sno,sname,2019-age

FROM S

ORDER BY  3     ,sno desc  //3代表第三列，空格代表默认为升序；sno学号为desc降序

注意：  对于排序列含空值，（默认按升序排列）

若按升序排列，含空值的元组将最后显示。

若按降序排列，含空值的元组将最先显示。

2）使用谓词的查询

（1）常用谓词 LIKE   （模式匹配）

语法:<列名>[NOT] LIKE字符串；

例：查询王红同学的全部信息。

SELECT  *

FROM  S

WHERE sname like'王红'

或

SELECT *

FROM  S

WHERE sname ='王红'；

通配符：“_”（“?”）可代表任一单个字符,

 “%〃（“*”）可代表任意多个字符。

通配符的使用:

例：查询所有姓王的学生的全部信息。

SELECT *

FROM  S

WHERE sname LIKE '王%’;

例：查询学号第二位是1的所有学生姓名。

SELECT sname

FROM  S

WHERE sno LIKE’_1%”;  //第二位是1

例：查询课程名为“DB_DE”的课程号(一般不会考)

SELECT cno

FROM C

WHERE cname LIKE “DB_DE”;

而：WHERE cname LIKE “DB\_DE’ ESCAPE’\’ ;

“\”后面的“_”不具有通配含义

（2）常用谓词 NULL （判空）

语法：WHERE sno IS [NOT] NULL

例：查询选修了课程但没参加考试的学生的学号。

SELECT sno

FROM SC

WHERE grade IS NULL;

例：查询所有成绩的学生学号和课程号

SELECT sno

FROM SC

WHERE grade IS NOT NULL;

（3）常用谓词  BETWEEN  (范围)

查找属性值在指定范围内的元组

语法：A BETWEEN B and C       等价于(A>=B and A<=C)

A NOT BETWEEN B and C  等价于（A>CORA<B）

例：查询所有年龄在17~18岁之间的学生的学号和姓名

SELECT sno,sname

FROM S

WHERE age BETWEEN 17and 18;

(或 WHERE age>=17 and age<=18)

（4）常用谓词 IN （集合）

用来查找属性属于制定集合的元组

语法：〈元素〉[NOT] IN〈结果集〉

例如：sno IN(‘s001’,’s002’,’s003’)

例：查询选修了课程号c1或c2的学生的学号

SELECT sno

FROM SC

WHERE cno IN(‘c1’,’c2’);

（等价于 WHERE cno=’c1’ or cno=’c2’）

3）使用聚集函数的查询

（1）五种聚集函数

1. COUNT

COUNT（ [distinct]＜列名＞ ）：统计一列中值的个数，不计算空值；

COUNT（ [distinct] * ）：计算元组的个数，不管列值是否为空。

distinct表示不计重复元组。

2. SUM（[distinct]＜列名〉）：计算一列的总和（此列必须是数值型）；

3. AVG（[distinct]＜列名〉）:计算一列的平均值（此列必须是数值型）；

4. MAX（[distinct]＜列名〉）：求一列值中的最大值；

5. MIN（[distinct]＜列名〉）：求一列值中的最小值。

注意：除COUNT（*）外，列中的空值先去掉再计算

（2）聚集函数的举例

例1： 求c2课程的平均成绩。

SELECT AVG(grade)   //AVG去掉空值

FROM SC

WHERE cno=‘c2’；

例2：求男同学的总人数和平均年龄。//sum , avg

SELECT COUNT（*）, avg（age）

FROM S

WHERE sex=‘ 男'；//条件

例3：统计选修了课程的学生人数。

SELECT  COUNT  （distinct sno）// distinct表示不计重复元组

FROM SC；

例4：求选修课程c2的学生的最高分和最低分。

SELECT MAX（grade）as 最高分，MIN（grade）

FROM SC

WHERE cno=‘c2’；

4）分组查询

（1）分组

对查询结果分组(GROUP BY)

GROUP BY子句将查询结果按某一列或多列值分组，值相等的为一组。

分组的目的是细化聚集函数的作用对象。如果未分组，聚集函数作用于整个 查询结果。

分组后作用于每一组，即每一组有一个函数值。

例1:求每个同学平均分。

SELECT sno, AVG(grade) as 平均分

FROM SC

GROUP BY sno;

（2）组选择

如果分组后还要求按一定条件对这些组进行选择，最终只输出满足条件的组，则可以使用 HAVING子句指定选择条件。

例：查询平均分在80以上的同学的平均分。

SELECT sno,AVG(grade)

FROM SC

GROUP BY sno；

HAVING AVG(grade)>80  //分组后选出均分在80以上的人

WHERE子句与HAVING子句的区别

~WHERE子句作用于基本表或视图，从中选择满足条件的元组；

~GROUP BY对WHERE的结果进行分组；

~HAVING子句作用于组，从中选择满足条件的组，即对分组数据进一步筛选；

~HAVING子句中可以使用聚集函数，而WHERE子句中不能。

 例：查询至少选修两门课程的学生学号。

//“至少选修两门”->课程数，“学生学号”->按学号分组

SELECT SNO

FROM SC

GROUP BY SNO

HAVING COUNT(CNO)>=2;

3.4.2 连接查询

1）多表等值连接查询

-按什么分组

-分组条件

-需要什么表头

-最终显示（出现在分组条件中过）

（1）连接查询的定义

基本语法结构:

SELECT Al,A2,...,An

FROM Rl,R2,...,Rn

WHERE〈查询条件表达式〉

若一个查询同时涉及两个以上的表时（FROM 子句中有多个表名），称为连接查询。

连接的字段类型必须可比，但不必相同。

例1：查询有一门课程成绩等于95分的学号、姓名、课程号和成绩。

SELECT S.sno,sname,cno,grade

FROM S, SC

WHERE S.sno=SC.sno and grade=95

（2）连接查询的执行过程

DBMS执行连接的过程：

首先在表1中找到第1个元组，然后从头开始 扫描表2,逐一查找满足连接条件的元组，找到后 将表1中的第1个元组与该元组拼接，形成结果表 中的一个元组。

表2中全部找完后，再找表1中的 第2个元组，第3个元组等……

重复上述步骤，直到 表1中的全部元组处理完毕。 

例：查询女学生的学号、姓名、成绩。

SELECT S.sno,sname,grade

FROM S, SC

WHERE S.sno=SC.sno and sex=’女'；

（3）连接查询的示例

例：找出平均成绩80以上的女生姓名。

SELECT sname

FROM S,SC

WHERE S.sno=SC.sno and sex=’女‘；

GROUP BY sname

HAVING AVG(grade)>80;

注意：如果有GROUP BY子句，那么SELECT后面只能出现GROUP BY中的分组属性和聚集函数。

例：求刘正老师所授的每门课程的课程号、课程名 和学生平均成绩。

SELECT C.cno,cname,AVG(grade)

FROM C,SC

WHERE C.cno=SC.cno and tname=’liuzhen’

GROUP BY C.cno,cname  //两个都不可缺少

例：查询每个学生的学号、姓名、选修的课程名及成绩。

SELECT s.sno,sname,cname,grade

FROM S, C, SC

WHERE S.sno=SC.sno and SC.cno=C.cno  //S与SC连接，结果再与C连接

例：检索至少选修两门课程的学生姓名。

SELECT sname

FROM SC，S

WHERE SC.sno=S. sno

GROUP BY sname

HAVING COUNT (cno)>=2

2）自身连接查询  （一张表查两次）

1.自身连接查询的定义

同一个表的不同元组之间的连接称为自身连接，即一个表与其自己进行连接。

自身连接要求给表取不同的名字，即当作两个不同的表来处理。

2.自身连接查询的示例

例：查询选修c2或c3课程的学生学号和姓名

SELECT S.sno,sname

FROM S,SC

WHERE S.sno=SC.sno and (cno=’c2’或cno=’c3’);

例：查询选修C2和c3课程的学生学号

SELECT X.sno

FROM SC as X, SC as Y   //自身连接

WHERE X.sno=Y.sno and X.cno=’c2’ and Y.cno=’c3’;

 例:查询选修了课程'cl'并且选修课程在三门以上的学生学号。

SELECT X.sno

FROM SC X, SC Y

WHERE X.sno=Y.sno and X.cno='c1'

GROUP BY X.sno

HAVING COUNT ( Y.cno )>3

例：检索至少选修两门课程的学生姓名（采用自身连接查询）

SELECT sname

FROM SC X,SC Y, S

WHERE X.sno=Y.sno and X.sno=S.sno and X.cno<> Y.cno//<>为不等号，等价于 !=

3）外连接查询（重点：左外连接）

（1）外连接连接查询的定义

把舍弃的元组也保存在结果关系中，而在其他属性上填 NULL

语法：

SELECT 列名

FROM  A表名 LEFT(/RIGHT) [OUTER] JOIN  B表名

ON (连接条件)

备注：

A表中保留左边信息，在B表中寻找与A表匹配的信息，若没有匹配的信息，就填NULL

（2）外连接查询示例

例：查询每个学生及其选修课程的情况包括没有选修课程的学生。

SELECT S.sno,sname,sex,cno,grade

FROM S left join SC on(S.sno=SC.sno)；

//S为左表

3.4.3 嵌套查询

1)不相关子查询  IN

（1）子查询分类

—个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句的条件中的查询称为嵌套查询（子查询）。

子查询可分成不相关子查询和相关子查询两类：

不相关子查询：子查询的查询条件不依赖于父查询，一般使用谓词IN。

相关子查询：子查询的查询条件依赖于外层父查询的某个属性值，一般使用谓词EXISTS。

（2）不相关子查询

不相关子查询的执行过程：

执行顺序由里往外，先执行子查询，子查询的结果作为父查询的条件。

根据子查询的结果再执行父查询。

例1:查询有一门课程成绩等于95分的学号和姓名。

采用多表等值连接实现

SELECT S.sno,sname

FROM S, SC

WHERE S.sno=SC.sno and grade=95

采用不相关子查询实现

SELECT sno，sname

FROM  S

WHERE sno IN( SELECT sno

FROM SC

WHERE grade = 95)；//先运行子查询

-能独立运行,子查询条件不依赖父查询;

-另能运行一次；

-先执行子查询;

例2：查询选修了课程名为‘DB'的学号和姓名。

用多表连接查询来实现

SELECT S.sno,sname

FROM S, SC, C

WHERE S.sno=SC.sno and C.cno=SC.cno

and cname=‘DB’；

用不相关子查询实现

 例3：检索王同学不学的课程的课程名。

SELECT cname  FROM C

WHERE cno NOT IN    //挑出姓王同学不学的课程

(SELECT cno  FROM     SC

WHERE sno IN  //找出姓王同学所学的课程

(SELECT sno FROM S

WHERE sname like ‘王％')); //找出姓王的同学

2)相关子查询  EXISTS

例1:查询有一门课程成绩等于95分的学号和姓名。

采用多表等值连接实现

SELECT S.sno,sname

FROM S, SC

WHERE S.sno=SC.sno and grade=95

采用相关子查询实现

SELECT sno，sname  //先选择学号姓名

FROM  S

WHERE EXISTS

(SELECT *      //一般情况都写 * 号

FROM SC

WHERE S.sno=SC.sno and grade = 95)；//连接S和SC后进行查找

-子查询不能独立运行;

-子查询多次运行；

-先执行外查询。

相关子查询的执行过程：

•首先取外层查询中表S的第1个元组，根据它与内层查询相关的属性值处理内层查询；

•若查询结果非空，则WHERE子句返回真，取此元组放入结果表中；

•然后取外层查询中表S的下一个元组，重复上述过程，直到外层查询中表S全部检索完为止。

若此题使用不相关子查询：

SELECT sno,sname

FROM S     //显示符合条件的学号姓名

WHERE sno IN(

       SELECT sno

       FROM SC

       WHERE grade=95); //先找成绩为95的学生学号

例2:查询没有选修C1课程的学生姓名。

SELECT sname

FROM S  //S表中选择学生姓名

WHERE NOT EXISTS

       (SELECT *

FROM SC

WHERE S.sno=SC.sno and CNO=‘C1’); //在SC表中查找选C1的人

连接查询一定能用不相关子查询（IN）实现，

一般形式的子查询都能用相关子查询（EXISTS）实现

3)带运算符的子查询

（1）带有比较运算符的子查询

当能确切知道内层子查询返回的是单值时，可以用比较运算符代替IN。

例：查询和'王红'相同性别的学生学号和姓名

（2）带有ANY或ALL谓词的子查询

1. ANY :

<表达式＞ ＜比较运算符>any＜子查询结果集＞ //满足一个

2. ALL：

<表达式＞ ＜比较运算符>all<子查询结果集>  //满足全部

例：

age＞ALL (13,15,17)  //age要大于集合中的所有值

age＞ANY(13,15,17)  //age只要大于集合中任意一个即可

例：查询选修C2课程的学生学号和姓名。

用IN：

SELECTsno,sname FROM S

WHERE sno in

(SELECT sno FROM SC

 WHERE cno=‘c2’)；

用ANY或ALL：

SELECT sno,sname FROM S

WHERE sno = any

(SELECT sno FROM SC

WHERE cno=‘c2’)；//只要满足条件就加入结果集

例：查询没选修C2课程的学生学号和姓名。

用 NOT IN：

SELECTsno,sname FROM S

WHERE sno NOT IN

(SELECT sno FROM SC

WHERE cno=‘c2’)；

用ANY或ALL:

SELECT sno,sname FROM S

WHERE sno <> ALL  //不等于

(SELECT sno FROM SC

WHERE cno=‘c2’)；//先找出选修了C2的

例：査询男同学中比某一女生年龄小的学生姓名和年龄。

用ANY：      

SELECT sname,age FROM S

WHERE sex='男' and

age<ANY(SELECT age

FROMS

WHERE sex=‘女')；

用聚集函数：

SELECT sname,age FROM S

WHERE sex='男' and

age<(SELECT MAX(age)

FROMS

WHERE sex=‘女')；

例：査询男生中比所有女生年龄都小的学生姓名和年龄。

用ALL：

SELECT sname,age FROM S

WHERE sex='男' and

age< ALL(SELECT age

FROM S WHERE sex=‘女')；

用聚合函数：

SELECT sname,age FROM S

WHERE sex='男'and

age<(SELECT MIN(age)

FROM S WHERE sex=‘女')；

例：查询平均分最高的学生学号和平均分。

不能用MAX( AVG( grade ))，将子査询结果用于比较。//不能嵌套

SELECT sno，AVG(grade)

FROM SC

GROUP BY sno

HAVING AVG(grade)>=all

(SELECT AVG(grade)

FROM SC

GROUP BY sno)；//算出每个学生的平均分

例：查找‘DB'课程成绩低于该课平均成绩的学生学号。

SELECT sno

FROM C,SC

WHERE SC.cno=C.cno AND cname=‘DB’ AND

grade< ( SELECT AVG(grade)  //得出平均成绩

FROM SC,C

WHERE SC.cno=C.cno AND

cname=‘DB') //查找该课程的课程号

3.4.4 集合运算

1）UNION （并）

例：查询选修课程cl或选修课程c2的学生

(SELECT sno

FROM SC

WHERE cno=‘c1’)

Union    //如果union后面有all，结果保留重复

(SELECT sno

FROM SC

WHERE cno=‘c2’);

2）INTERSECT （交）

例：查询女学生和年龄不大于19岁的学生的交集。

(SELECT *

FROM S

WHERE sex=‘女)

Intersect

(SELECT *

FROM S

WHERE age<=19); 

3）MINUS （差）

例：查询女学生与年龄不大于19岁的学生的差集。//女学生中除去不大于19岁的

(SELECT*

FROM  S

WHERE sex=‘女')

Minus

(SELECT *

FROM  S

WHERE age<=19);

3.5数据更新

3.5.1插入数据

1）用VALUES子句向表中插入数据（一次只能插一条）

语法：

INSERT INTO〈基本表名＞［（列名表）］VALUES （元组值）；

将VALUES后面的元组插入到基本表中，即将元组值依次赋值给表名后面给出的各个列。

例：INSERT INTO S (sno,sname,age)VALUES (‘s1’, ‘李涛。19 )；

// (sno,sname,age)中的顺序要与第二个括号中插入数据对应

例：INSERT INTO S VALUES (‘s1’,‘李涛',19,‘男')；

//此处S后的括号省略，因为VALUES的元组给的值与列表名一一对应

2）用子查询向表中插入数据（一次能插入n条）

语法：

INSERT INTO <表名＞（＜列名表>）

SELECT查询语句；

可以将子查询的结果插入到另一个已知表中。

例：把成绩不及格的学生的学号、姓名、课程号和成绩存入另一个已知基本表：NoPass(sno ,sname ,cno,grade)中。

INSERT INTO NoPass

SELECT S.sno, sname, cno, grade

FROM S,SC

WHERE S.sno =SC.sno and grade <60;

3.5.2删除数据

1）数据删除命令的基本用法

DELETE FROM 基本表名    [WHERE条件表达式];

注意：

-DELETE后只能有一张表

-DELETE只删表中的数据，表的定义仍然在数据字典中。若要删去表的结构：DROPTABLE

-若无[WHERE条件表达式] ，则是删去整个表的数据

2）删除一条记录

例：在基本表s中删除学号是s的学生。

DELETE  FROM  S

WHERE sno=‘s1';

3）删除多条记录

例2：在基本表SC中删除无成绩的选课记录。

DELETE FROM SC

WHERE grade IS NULL;

4）带子查询的删除

例：删除所有女生的选课记录

DELETE FROM SC

WHERE sno in(SELECT sno FROM S WHERE sex=’女‘)；

3.5.3修改数据

1）数据修改命令的基本用法

语法：

UPDATE基本表名

SET列名=值表达式[，列名=值表达式・・・]

[WHERE条件表达式];

注意：

UPDATE语句只能修改一个基本表中满足WHERE条件的元组的某些列值，即其后只能有一个基本表名。

2）修改一条记录

例1：把课程号为’c5’的课程的任课教师名改为wu

UPDATE C

SET tname = 'wu'

WHERE cno = 'c5'；

3）修改多条记录

例：把全体学生的年龄加1。

UPDATE S

SET age=age+1 ；

4）带子查询的修改

例：把全体女学生的成绩置0。

UPDATE SC

SET grade=0

WHERE sno in ( SELECT sno FROM  S  WHERE sex =‘女');

例:将s1同学的年龄改成与s2同学的年龄相同。

UPDATE S

SET age=( SELECT age FROM S WHERE sno=‘s2’)

WHERE sno =‘s1’;

3.6视图

3.6.1 视图的概念

1）视图

视图是从一个或几个基本表（或视图）中导出 的表。

视图是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，数据仍放在基本表中。

~数据库中不存放视图 对应的数据，数据仍放在基本表中。

~视图可以像基本表一样被查询和删除。

~对视图的操作实际上对基本表操作。

2）作用

⑴有利于应用程序独立性、数据一致性

（2）有利于数据库的安全性

（3）使用户从多个角度观察数据

（4）简化用户查询操作

例如：

査询选'DB'课程的学生姓名，课程名和成绩。

SELECT S.sno, sname, cname, grade

FROM S,SC,C

WHERE S.sno=SC.sno and SC.cno=C.cno and cname=‘DB,；

选课视图SC-G1 (sno, sname, cname, grade )

SELECT * FROM SC_G1 WHERE cname=‘DB';

3.6.2 视图的定义

1）视图分类

行列子集视图：一个视图从单个基本表导出，并且只去掉了基本表的某些行和某些列，但保留了关键字。

带表达式视图：带虚拟列的视图。派生列在基本表中并不实际存在，所以称为虚拟列。

分组视图：定义视图时带有聚集函数和 GROUP BY子句

2）视图定义

语法：

CREATE VIEW<视图名> [（<列名1 >,<列名 2 >,...）]

AS SELECT査询语句

[WITH CHECK OPTION];

~列名要么省略，要么全部指定，省略时由SELECT中的字段组成。

~必须有列名的3种情况：有表达式或聚集函数；起新名; 多表时有同名列。

~ WITH CHECK OPTION表示对视图做插入、修改时要满 足查询语句WHERE中的条件。

例1:建立一个男生的视图--行列子集视图

CREATE VIEW F_S

AS SELECT sno,sname,age  //选三列

FROM  S

WHERE sex=‘男'

 

 

例2：建立学生出生年份的视图——带表达式的视图

CREATE VIEW Bt_S(sno,sname,birth)

AS SELECT sno,sname,2021-age

FROM S;

例3：建立学生的学号和平均成绩的视图——分组视图

CREATE VIEW S_G(sno,gavg)

AS SELECT sno,AVG(grade)

FROM SC

GROUP BY sno;

 

 AS SELECT后要查询的内容来源于多处；FROM后是原始视图；WHERE语句进行等值连接

3）视图删除

语法：

DROP VIEW〈视图名〉

例5:

DROP VIEW S_G；

则建立在S_G上的视图SG_1失效。

3.6.3 视图的查询

1）视图查询的语法

视图定义后，用户可以像对基本表一样对视图进行查询。

例 1:已知视图S_G(sno,sname,cname,grade),在视图S_G上查询课程'DB '的全部信息。

SELECT *

FROM S_G

WHERE cname=‘DB';

2）视图查询的执行   （来源于基本表）

视图消解：把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询

视图查询：SELECT *  FROM S_G  WHERE cname=‘DB';

视图定义：CREATE VIEW S_G(sno,sname,cname,grade)

AS SELECT S.sno, sname, cname, grade

FROM S,SC,C

WHERE SC.sno=S.sno and SC.cno=C.cno；

合并视图查询和视图定义：

SELECT S.sno, sname, cname, grade

FROM S，SC，C

WHERE S.sno=SC.sno  and  SC.cno=C.cno  and  cname= 'DB'; 

例2：已知一个包含每个同学的学号和平均成绩的视图S_G1。在视图S_G1中查询平均成绩在90分以上的学生学号和平均成绩。

查询语句：

SELECT * FROM S_G1 WHERE gavg>=90

视图的定义：

CREATE VIEW S_G1(sno,gavg)

AS SELECT sno,AVG(grade)

FROM SC  //基本表

GROUP BY sno;

查询语句：

SELECT sno,AVG(grade)

FROM SC  //基本表

GROUP BY sno

HAVING AVG(grade)>=90;

对视图的查询最终要转换成对基本表的查询

3.6.4 视图的更新

1）视图更新的基本原则

视图更新指：在视图上进行插入、修改或删 除操作，视图更新实质上是对基本表的更新。

例如 在视图上增加一行数据，系统需要转换为在基本表上增加一行数据。

对视图的更新操作一般受到以下限制

~视图是从多个基本表使用连接操作导出的，则不允许更新

~导出的视图使用了分组和聚集操作，也不允许更新

~视图是从单个基本表使用选择和投影操作导出的，并且包括了基本表的主键，即视图为行列子集视图，则可以执行更新操作。

2）视图更新的示例

例1:判断下列视图是否允许更新。不允许

CREATE VIEW S_grade (sno,sname,cno,cname,grade)

AS SELECT S.sno,sname,C.cno,cname,grade

FROM S,SC,C //来源多个表

WHERE S.sno=SC.sno and SC.cno=C.cno

例2：建立每个学生选课（grade非空）的门数及平

均成绩的视图，判断该视图是否允许更新。

CREATE VIEW S_grade(sno,c_num,avg_g)

AS SELECT sno,COUNT(cno),AVG(grade)  //分组和聚集操作

FROM SC

WHERE grade is NOT NULL

GROUP BY sno;

不允许对上述视图进行更新。

INSERT INTO S_grade VALUES （’s3',3,80）//不能更新，无法将数据插入基础表

 

例3：建立有关男同学的视图。

CREATE VIEW S_m

AS SELECT sno,sname,age

FROM S

WHERE sex=‘ 男’

WITH CHECK OPTION；

可以对上述视图进行更新。

允许更新的视图在定义时必须加上WITH子句，保证进行插入和修改操作时该视图只有男生。 

对男生视图S_m执行插入操作:  INSERT INTO S_m VALUES（‘S4’，‘王四’，18）

系统会自动把它转变为：INSERT INTO S VALUES (‘s4’，‘王四’，18，‘男’)；