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

最新推荐文章于 2022-12-25 13:52:48 发布

Jin4869

最新推荐文章于 2022-12-25 13:52:48 发布

阅读量526

点赞数 2

分类专栏：数据库文章标签：数据库 sql

本文链接：https://blog.csdn.net/Jin4869/article/details/128293703

版权

数据库专栏收录该内容

8 篇文章 2 订阅

订阅专栏

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

1 SQL动词
2 数据定义
3 数据查询
4 数据更新
5 空值的处理
- 5.1 空值的约束条件
- 5.2 空值的算数运算，比较运算，逻辑运算
6 视图

1 SQL动词

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

2 数据定义

2.1 模式的定义和删除

2.1.1 模式的定义

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

如果没有指定模式名，那么模式名隐含用户名

例：为用户ZHANG创建一个模式TEST，并在其中定义一个表TAB1

CREATE SCHEMA TEST AUTHORIZATION ZHANG  //创建模式
CREATE TABLE TAB1(                      //创建表
            COL1 INT,
            COL2 CHAR(20)            
            );

2.1.2 模式的删除

DROP SCHEMA <模式名> <CASCADE|RESTRICT>;

CASCADE 级联，表示删除模式时同时把该模式中所有数据库对象全部删除
RESTRICT 限制，如果该模式已经定义了下属的数据库对象（表、视图等），则拒绝该语句执行

2.2 基本表

2.2.1 基本表的定义

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

例：建立学生选课表SC

CREATE TABLE SC(
    Sno CHAR(9),
    Cno CHAR(4),]
    Grade SMALLINT,     /*短整数，2字节*/
    PRIMARY KEY (Sno, Cno),     
        /*主码由两个属性构成，必须作为表级完整性进行定义*/
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
        /*表级完整性约束条件，Sno是外码，被参照表是Student*/
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
        /*表级完整性约束条件，Cno是外码，被参照表是Course*/
    );

数据类型

数据类型	含义
CHAR(n)	长度为n的定长字符串
VARCHAR(n)	最大长度为n的变长字符串
CLOB	字符串大对象
BLOB	二进制大对象
INT	长整数（4字节）
SMALLINT	短整数（2字节）
BIGINT	大整数（8字节）
NUMERIC(p, d)	定点数，总共p位数字，小数点后d位数字
FLOAT(n)	精度至少为n位数字的浮点数
BOOLEAN	逻辑布尔量
DATE	日期，YYYY-MM-DD
TIME	时间，HH:MM:SS
TIMESTAMP	时间戳类型

2.2.2 基本表的修改

ALTER TABLE <表名>
[ADD [COLUMN] <新列名> <数据类型> [完整性约束]]
        /*增加新列、新列级完整性*/
[ADD <表级完整性约束>]
        /*增加新的表完整性约束*/
[DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
        /*删除列。RESTRICT表示如果该列被其他对象引用则拒绝删除，CASCADE表示自动删除引用了该列的其他对象*/
[DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE]]
        /*删除指定的完整型约束条件*/
[ALTER COLUMN <列名> <数据类型>]
        /*修改原有的列定义，包括修改列名和数据类型*/

例：

/*向Student表增加“S_entrance”列，数据类型为DATE*/
ALTER TABLE Student ADD S_entrance DATE;

/*将年龄(Sage)的数据类型由字符型改为整数*/
ALTER TABLE Student ALTER COLUMN Sage INT;

/*增加课程名称(Cname)必须取唯一值的约束条件*/
ALTER TABLE Course ADD UNIQUE(Cname);

2.2.3 基本表的删除

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

RESTRICT（默认情况）: 欲删除的基本表不能被其他表的约束所引用（如CHECK，FOREIGN KEY等），不能有视图，不能有触发器，不能有存储过程或函数
CASCADE 删除没有限制，相关的依赖对象比如视图都会被删

2.3 索引

常见索引包括顺序文件上的索引、B+树索引，散列（hash）索引，位图索引

维护索引由DBMS自动完成

2.3.1 索引的建立

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名>(<列名> [<次序>] [,<列名>[<次序>]]...)

次序：ASC(升序，默认)，DESC(降序)

UNIQUE 此索引的每一个索引值只对应唯一的数据记录
CLUSTER 表示要建立的索引是聚簇索引
- 物理排序，在最常查询且少更新的列上进行
- 一个基本表最多一个聚簇索引

2.3.2 索引的修改

ALTER INDEX <旧索引名> RENAME TO <新索引名>

2.3.3 索引的删除

DROP INDEX <索引名>

2.4 数据字典

关系数据库管理系统内部的一组系统表，记录了数据库中所有的定义信息。

执行SQL的数据定义语句时，实际上就是在跟新数据字典表中的相应信息，在进行查询优化和查询处理时，数据字典中的信是最重要的依据

3 数据查询

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

DISTINCT：取值唯一
GROUP BY：分组

3.1 单表查询

3.1.1 选择表中若干列

3.1.1.1查询指定列

例：查询全体学生的姓名、学号、所在系

SELECT Sname,Sno,Sdept
FROM Student;

3.1.1.2查询所有列

SELECT * FROM Student;

3.1.1.3查询经过计算的值

例：查询全体学生姓名及其出生年份

SELECT Sname,2022-Sage  /*"2022-Sage"是一个算数表达式*/
FROM Student;

目标列表达式还可以是字符串常量（整列都是那个常量），函数

3.1.1.4 通过指定别名来改变查询结果的列标题

SELECT Sname NAME, 
        'Year of Birth:' BIRTH, 
        2022-Sage BIRTHDAY,
        LOWER(Sdept) DEPARTMENT /*将Sdept改为小写*/
FROM Student;

结果：

NAME	BIRTH	BIRTHDAY	DEPARTMENT
李勇	Year of Birth:	1994	cs

3.1.2 选择表中若干元组

3.1.2.1消除取值重复的行 DISTINCT

DISTINCT 作用范围是所有目标列

SELECT DISTINCT Sno
FROM SC;

3.1.2.2 查询满足条件的元组 WHERE

比较大小， $! >$ （不大于）， $! <$ (不小于)

确定范围

WHERE Sage [NOT] BETWEEN 20 AND 30; /*包含边界*/

确定集合
```
WHERE Sdept [NOT] IN ('CS','MA')
```
字符匹配
```
[NOT] LIKE '<匹配串>' [ESCAPE'<换码字符>']
```
- $\%$ 表示任意长度（长度可以为0）的字符串
- $\_$ 表示单个字符
- ESCAPE '<换码字符>'对通配符进行转义
```
WHERE Cname LIKE 'DB\_%i__' ESCAPE '\'
```
  \为换码字符，第一个_为普通字符，后两个为通配符
涉及空值的查询 IS [NOT] NULL
多重条件查询 AND OR
- AND的优先级高于OR，可以用括号

3.1.3ORDER BY子句

对一个或多个属性列的升序或降序排列

空值最大

ORDER BY Grade DESC;

3.1.4 聚集函数

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

除COUNT(*)，都跳过空值只处理非空值

WHERE子句是不能用聚集函数做条件表达式的，聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句

3.1.5 GROUP BY子句

例：求各个课程号及相应的选课人数

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

结果

Cno	COUNT(Sno)
1	22
2	34

3.1.5.1 用HAVING短语对组进行筛选

WHERE与HAVING的区别：

WHERE作用于基本表或视图
HAVING作用于组，从而选择满足条件的组（非属性约束）

下面语句是不对的：

SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90    /*WHERE子句不能用聚集函数做条件表达式*/
GROUP BY Sno;

正确应是：

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

3.6 连接查询

3.6.1 等值与非等值连接，自然连接

运算符为=时为等值，其余为非等值

SELECT Student.*,SC.*       /*.*某表中的所有属性列*/
FROM Student,SC
WHERE Student.Sno=SC.Sno    /*Student与 SC中同一学生的元组连接起来*/

自然连接：把目标列中重复的属性列去掉（两表中的Sno相同）

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno

3.6.2 自身连接

为表取两个别名，FIRST和SECOND

例：对同一个表的Cno和Cpno连接

SELECT FIRST.Cno, SECOND.Cpno
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;

3.6.3 外连接

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno)
/*也可以用USING连接：FROM Student LEFT OUTER JOIN SC USING(Sno);*/

左外连接 LEFT OUTER
右外连接 RIGHT OUTER

3.6.4 多表连接

WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno

3.7 嵌套查询

3.7.1 带有IN的子查询

子查询是个集合时

3.7.2 带有比较运算符的子查询

子查询返回单个值时

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

> ANY	大于子查询结果某个值	等价于 >MIN
> ALL	大于子查询结果所有值	等价于 >MAX
<ANY	小于子查询结果某个值	等价于 <MAX
< ALL	小于子查询结果所有值	等价于 <MIN
>= ANY	大于等于某个值
>= ALL	大于等于所有值
<= ANY	小于等于某个值
<= ALL	小于等于所有值
= ANY	等于某个值	等价于 IN
= ALL	等于所有值（通常没有实际意义）
!= (<>) ANY	不等于某个值
!= (<>) ALL	不等于任何一个值	等价于NOT IN

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

SELECT Sname, Sage
FROM Student
WHERE Sage<ANY(SELECT Sage          /*子查询块*/
                FROM Student        /*查询计算机系所有学生的年龄*/
                WHERE Sdept='CS')   
AND Sdept <> 'CS'                   /*父查询块中的条件*/

3.7.4 带有EXISTS谓词的子查询

若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值

NOT EXISTS相反

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

在Student中依次取每个元组的Sno值，用此值去检查SC表

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

例：查询选修了全部课程的学生姓名（全称转换为存在）

SQL中没有全称量词（for all），可将题目的意思转换成等价的用存在量词的形式
本题中，题目意思转换成：查询这样的学生，没有一门课程是他不选修的

SELECT Sname 
FROM Student 
WHERE NOT EXISTS
        (SELECT * FROM Course
         WHERE NOT EXISTS
                (SELECT * FROM SC
                 WHERE Sno=Student.Sno
                    AND Cno=Course.Cno));

例：查询至少选修了学生201215122选修的全部课程的学生号码（逻辑蕴涵转换为存在）

SQL中没有蕴涵逻辑运算，蕴涵谓词转换 $\to q\equiv \neg p\vee q$

分析：

本题语义为：查询学号为x的学号的学生，对所有课程y，只要201215122学生选修了课程y，则x也选修了y。
形式化表示：
- p：“学生201215122选修了课程y”
- q：“学生x选修了课程y”
则上述查询为 $(\exists y )p\to q$
$(\exists y)p \to q\equiv \neg (\exists y\neg (p\to q)) \equiv\neg(\exists y(\neg(\neg p \vee q)))\equiv \neg \exists y(p \wedge \neg q)$

即：不存在这样的课程y，学生201215122选修了y，而学生x没有选

SELECT DISTINCT Sno
FROM SC SCX         /*SC另称为SCX，以下同*/
WHERE NOT EXISTS
        (SELECT * FROM SC SCY       /*查询课程表SC中201215122选修的全部课程*/
         WHERE SCY.Sno='201215122' 
         AND NOT EXISTS
                (SELECT * FROM SC SCZ   /*学生x选择了SCY筛选出来的即201215122学生学的课程*/
                 WHERE SCZ.Sno=SCX.Sno 
                 AND SCZ.Cno=SCY.Cno))

3.8 集合查询

参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同

3.8.1 UNION 并操作

例：查询计算机系的学生及年龄不大于19岁的学生

SELECT * FROM Student 
WHERE Sdept='CS'
UNION   /*求并集*/
SELECT * FROM Student
WHERE Sage<=19;

UNION会自动去掉重复元组，要保留重复元组则用UNION ALL操作符

3.8.2 INTERSECT 交操作

例：查询计算机系的学生与年龄不大于19岁的学生的交集

SELECT * FROM Student 
WHERE Sdept='CS'
INTERSECT   /*求交集*/
SELECT * FROM Student
WHERE Sage<=19;

实际上就是查询计算机系年龄不大于19岁的学生

SELECT * FROM Student
WHERE Sdept='CS'
        AND Sage<=19;

3.8.3 EXCEPT 差操作

例：查询计算机系的学生与年龄不大于19岁的学生的差集

SELECT * FROM Student
WHERE Sdept='CS'
EXCEPT      /*求差集*/
SELECT * FROM Student 
WHERE Sage<=19;

也就是查询计算机系中年龄大于19岁的学生

SELECT * FROM Student
WHERE Sdept='CS' AND Sage>19;

对集合操作结果的排序 order by 1(对第一列进行排序)，只能放在语句的最后

3.9 基于派生表的查询

子查询出现在FROM子句中，生成临时派生表

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

SELECT Sno,Cno
FROM SC,(SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno)
        AS Avg_sc(avg_sno, avg_grade)       /*生成一个派生表Avg_sc，由avg_sno和avg_grade组成*/
WHERE SC.Sno=Avg_sc.avg_sno                 /*按学号相等进行连接*/
        AND SC.grade >= Avg_sc.avg_grade    /*选出修课成绩大于其平均成绩的课程号*/

如果子查询中没有聚集函数，派生表可以不指定属性列

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

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

通过FROM子句生成派生表时。AS关键字可省略，但必须为派生关系指定一个别名

4 数据更新

4.1 插入数据

执行插入语句时会检查所插入元组是否破坏已定义的完整性

4.1.1 插入元组

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

INTO子句中没有出现的属性列，新元组在这些列上会取空值
表定义时说明了NOT NULL的属性列不能取空值否则会出错
INTO子句没有指明任何属性列，则新插入的元组必须在每个属性列上均有值，且顺序要和CREATE TABLE中的次序相同
指明了的属性列的顺序可以和CREATE TABLE中的顺序不一样
字符串常量要用’‘括起来
明确空值NULL

例：将学生张承敏的信息插入到Student表中

INSERT 
INTO Student        /*没有指定属性列，则每个属性都要有值*/
VALUES ('201215126','张成民','男',18,'CS');

4.1.2插入子查询结果（一次性插入多个元组）

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

例：对每一个系求学生的平均年龄并把系名和平均年龄存入表中

INSERT 
INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(age)
FROM Student 
GROUP BY Sdept;         /*按系分组求每个系的平均年龄*/

4.2 修改数据

UPDATE <表名>
SET <列名>=<表达式> [,<列名>=<表达式>]...
[WHERE <条件>];       /*如果省略则表示要修改表中的所有元组*/

4.2.1 修改某一个元组的值

例：将学生201215121的年龄改为22岁

UPDATE Student
SET Sage=22
WHERE Sno='201215121'

4.2.2 修改多个元组的值

例：将所有学生的年龄增加1岁

UPDATE Student
SET Sage=Sage+1;

4.2.3 带子查询的修改语句

子查询嵌套在UPDATE语句中，用以构造修改的条件

例：将计算机系全体学生的成绩置零

UPDATE SC 
SET Grade=0
WHERE Sno IN 
        (SELECT Sno
         FROM Student
         WHERE Sdept='CS');
/*
WHERE语句等同于：
WHERE 'CS'=(SELECT Sdept FROM Student
            WHERE Student.Sno=SC.Sno);
*/

4.3 删除数据

DELETE 
FROM <表名>
[WHERE <条件>];   /*省略WHERE子句删除的是表中全部元组，但不删除表的定义*/

4.3.1 删除某一个元组的值

例：删除学号为201215128的学生记录

DELETE FROM Student
WHERE Sno='201215128';

4.3.2 删除多个元组的值

例：删除所有学生的选课记录（SC成为了空表）

DELETE FROM SC;

4.3.3 带子查询的删除语句

例：删除计算机系所有学生的选课记录

DELETE FROM SC
WHERE Sno IN 
        (SELECT Sno FROM Student
         WHERE Sdept='CS');

5 空值的处理

判断：IS NULL、IS NOT NULL

5.1 空值的约束条件

属性定义中含有NOT NULL的不能取空值
加了UNIQUE限制的属性不能取空值
码属性不能取空值

5.2 空值的算数运算，比较运算，逻辑运算

空值与另一个值（包括另一个空值）的算数运算结果为空值
空值与另一个值（包括另一个空值）的比较运算结果为UNKNOWN
逻辑运算如下

x y	x AND y	x OR y	NOT x
T U	U	T	F
U T	U	T	U
U U	U	U	U
U F	F	U	U
F U	F	U	T

6 视图

6.1 定义视图

6.1.1 建立视图

CREATE VIEW <视图名> [(<列名>[,<列名>]...)]
AS <子查询>            /*子查询可以是任意的SELECT语句*/
[WITH CHECK OPTION]   /*表示对视图进行UPDATE、INSERT、DELETE操作时要保证更新、插入
                        或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）*/

组成视图的属性列名或全部省略或全部指定
在下列情况中必须明确指定组成视图的所有列名

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

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

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

视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上

例：建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）

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    /*对Student和SC进行连接*/
      SC.Cno='1';

可以用带有虚拟列（表达式）、聚集函数、GROUP BY子句的查询来定义视图

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

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

6.1.2 删除视图

DROP VIEW <视图名> [CASCADE]   
/*如果该视图还导出其他视图，CASCADE级联删除该视图以及由他导出的子图*/

6.2 查询视图

大部分情况与基本表相同

由于WHERE子句中不能用聚集函数作为条件表达式，对于WHERE子句中带有聚集函数的视图的查询很可能出错，一般不使用视图而是使用基本表查询

6.3 更新视图

由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新

在定义视图时加上WITH CHECK OPTION子句，在视图上增删改数据时关系数据库管理系统会检查视图定义中的条件，不满足条件则拒绝执行该操作

不可更新的视图： 并不是所有视图都是可更新的，例如视图中有平均成绩这一列，是由成绩表的成绩计算而来，想要修改平均成绩为90，但是不可能修改成绩表的成绩使得平均成绩为90，此时视图就是不可更新的。
不允许更新的视图： 与不可更新的视图不同的是，前者是理论上证明其不可更新，后者指实际系统不支持其更新，但其本身可能可以更新