数据库笔记--SQL部分

3.SQL语言

3.3数据定义DDL

模式定义与删除：

定义：

REAT　SCHEMA<模式名>　AUTHORIZATION<用户名>

若未显示的给出模式名，默认为用户名

• 拓展：创建模式同时可以接受创建基本表、视图、授权。
  REAT　SCHEMA<模式名>　AUTHORIZATION<用户名>[<表定义子句>|<视图……>|<授权……>]

删除：

• DROP SCHEMA<模式名><CASCADE|RESTRICT>
• CASCADE:级联，表示同时把模式下所有数据库对象都删除
• RESTRICT：只有当模式下没有任何下属的数据库对象才能执行DROP SCHEMA

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

每一个基本表都属于某一个模式，一个模式包含多个基本表。

数据类型:

• 域：是关系模型中很重要的概念。每一个属性来自一个域，其取值必须是域中的值。SQL中的域用数据类型来实现。
• 例如：CHAR(n)表示长度为n的定长字符串，FLOAT(n)精度为n的浮点数。

定义：

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

• 注意:[]内的内容表示可选可不选

修改：

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

删除：

DROP TABLE<表名>[RESTRICT|CASCADE]

• 若选择RESTRICT：欲删除的表不能被其他表的约束所引用（如CHECK，FOREIGN KEY等），不能有视图view，不能有触发器trigger，不能有存储过程或函数等。若存在这些依赖该表的对象则此表无法删除
• 若选择CASACADE：删除无限制，删除基本表时直接删除相关依赖（索引、触发器等）。

索引

建立：

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

• <表名>是要建索引表的名字。索引可建立在该表的一列或多列上，各列名之间用逗号分隔。每个列名后可以用次序指定排列顺序，可选ASC(升序)(默认)或者DESC(降序)。
• UNIQUE 表明此索引的每一个索引值只对应唯一的数据记录
• CLUSTER 表示要建立的是聚簇索引。

修改

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

删除

DROP INDEX <索引名>；

3.4数据查询DQL

SELECT语句一般格式

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

含义：

• 根据WHERE子句的条件表达式从FROM子句指定的基本表、视图或派生表中找到满足条件的元组，再按SELECT子句中的目标列表达式选出元组中的属性值形成结果表。
• 如果有GROUP BY子句，则将结果按<列名1>的值进行分组，该属性相等的元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY子句带HAVING短语，则只有满足指定条件的组才予以输出
• 如果有ORDER BY子句，则结果还要按<列名2>的值按照（ASC|DESC）排序。

单表查询

选择若干列

查询指定列或全部列

SELECT <目标列表达式> #查询指定列
SELECT * #查询全部列

目标列表达式：不仅可以是表中的列，也可以是表达式

• 例如：SELECT Sname,2021-Sage

目标列表达式：还可以是字符串常量、函数等

• 例如：SELECT Sname,“CHENEY”,LOWER(Sdept)
• 这里"CHENEY"是字符串，"LOWER"是函数(用于将字母全部转化为小写)

用户可以指定别名：直接在要指定别名的目标列表达式的后面加空格写出别名即可

• 例如：SELECT Sname,2021-Sage BIRTHDAY
• 这里就给2021-Sage 取了别名叫 BIRTHDAY

选择若干行

消除重复的行：

用DISTINCT可以消除重复的行

• 例如：SELECT DISTINCT Sno FROM SC;
• 若没有指定DISTINCT 默认为ALL

对行进行筛选(查询满足条件的元组):

用WHERE子句实现

常用的查询条件表:

功能	形式
比较：	=,>,<,>=,<=,!=，等
确定范围：	(NOT)BETWEEN AND
确定集合：	(NOT)IN
字符匹配：	(NOT)LIKE
空值：	IS (NOT) NULL
逻辑运算：	AND,OR,NOT

用法：

• BETWEEN a AND b #a,b都是数值，相当于 a< x <b
• IN(“ONE”,“TWO”,“THREE”) #括号内元素间以逗号分隔
• 多个条件之间用AND、OR、NOT连接而不是逗号连接(NOT>AND>OR)

LIKE的用法：

格式：[NOT] LIKE “<匹配串>” [ESCAPE"<换码字符>"]
含义：查找与<匹配串>相符的行

通配符：%、_、\

• '%'代表任意长度（可以为0）的字符串
• '_'代表任意单个字符
• ''转移字符通常用于匹配通配符本身代表的符号

ORDER BY 子句

用户可以对查询结果用ORDER BY子句按照一个或多个属性列的升序(ASC)或降序(DESC)排列，默认为升序。

聚集函数

函数	功能
COUNT(*)	统计行数
SUM	求一列的和
AVG	求一列的均值
MAX	求一列的最大值
MIN	求一列的最小值

用法：

函数名 [DISTINCT|ALL] <列名>

GROUP BY子句

• 结果按<列名1>的值进行分组，该属性相等的元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY子句带HAVING短语，则只有满足指定条件的组才予以输出

形式：

[GROUP BY <列名1>[HAVING<条件表达式>]]

HAVING和WHERE的区别在于作用对象不同。WHERE作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。

连接查询

同时涉及两个以上的表的查询称为连接查询

等值与非等值连接

连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词，一般格式为：

[表名1.]<列名1><比较运算符> [<表名2.>]<列名2>
或者：[表名1.]<列名1> BETWEEN [<表名2.>]<列名2> AND [<表名2.>]<列名3>

当连接运算符为=时称为等值连接否则为非等值连接。

自身连接

一个表与自己连接称为自身连接。一个表出现两次，不易区分，故应给表取别名用于区分。

FROM Course A,Course B WHERE A.Cno = B.Sno;
这里Course做了自身连接，两次用到course表分别称为A、B

外连接

外连接分为外左连接(left outer join)和外右连接(right outer join)

• from 后面直接写两个表名，这样写等价于内连接 如果不写 where 条件，相当于笛卡尔积。

• 带有 where 条件 相当于内连接。

• 左联接，是包括 A,B 表的交集 和 A 表的数据 （A eft join B on A.1=B.1），如果 A 表中有的数据 B 表没有找到相等的，会显示A表的数据，B表中的部分会为 null

select from A left join B on A.id = B.id

• 右外链接，同左外链接一样，包括并集和B表的数据。

嵌套查询

SQL语言中，一个SELECT-FROM-WHERE语句块称为一个查询块，将一个查询快嵌入到另一个WHERE子句或者HAVING短语的条件中的查询称为嵌套查询。
被嵌套的内部查询称为子查询，如果子程序的查询条件不依赖于父查询，称为不相关子查询。

带有IN谓词的子查询

条件：子查询的结果是一个集合

例如：……WHERE Sno IN (SELECT ……);

带有比较运算的子查询

条件：子查询的结果是一个值

例如：……WHERE Sno > (SELECT ……);

带有(ANY)SOME或ALL的子查询

符号	含义
>/</>=/<= SOME	大(小)于或大(小)于等于 查询结果中的某个值
>/</>=/<= ALL	大(小)于或大(小)于等于 查询结果中的所有值
!=/= SOME	(不)等于查询结果中的某个值
!=/= ALL	(不)等于查询结果中的所有值(没什么实际含义)

一些等价关系

……	=	!=	<	<=	>	>=
SOME	IN	……	< MAX	<= MAX	> MIN	>= MIN
ALL	……	NOT IN	< MIN	<=MIN	>MAX	>=MAX

带有EXISTS的子查询

EXISTS代表存在量词，带有其的子查询不返回任何数据，只产生逻辑值真true/假false

EXISTS可以替代所有的带IN、比较运算符、SOME&ALL的子查询，反之不一定。

例如：查询没有选修一号课程的学生姓名
SELECT Sname FROM Student WHERE NOT EXISTS
(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno = ‘1’);

由EXISTS引出的子查询，其目标表达式通常都用* 。

SQL中没有全称量词，但可以通过存在量词转换而来(NOT EXISTS NOT……)

集合查询

SELECT的查询结果是元组的集合，所以多个SELECT集合可以进行集合操作，如并UNION、交INTERSECT、差EXCEPT。UNION操作系统自动去除重复元组，如果需要保留采用UNION ALL即可。

形式：SELECT * FROM A INTERSECT\EXCEPT\UNION SELECT * FROM B

基于派生表的查询

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

通过FROM子句生成派生表时，AS关键字可以省略，但必须为派生表取别名。

3.5数据更新DCL

更新操作通常有三种：添加、修改、删除。

插入

插入元组

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

插入子查询结果

修改

一般格式

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

功能是修改指定表中满足WHERE子句条件的元组，SET子句给出表达式的值用于取代原有的值。省略WHERE子句表示修改表中所有元组。

删除

一般格式

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

功能：删除满足WHERE子句条件的所有元组。若省略WHERE子句表示清空表，但不是删除表，表还在。

3.6空值的处理

定义：
所谓空值就是"不知道"、"不存在"或"无意义"的值。

判断：
用IS NULL 和 IS NOT NULL 判断。

约束：
NOT NULL属性的不能取空值，UNIQUE属性的不能取空值，码属性不能取空值。

运算
空值与其他所有值的算术运算结果为空NULL
空值与其他所有值的比较结果为UNKNOW(破坏了二值逻辑)
含UNKNOWN的逻辑运算结果：

3.7视图

意义

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

作用

• 简化用户操作
• 让用户以多角度看待同一数据
• 对重构数据库提供了一定的逻辑独立性
• 提供安全保护
• 更清晰的表达查询

建立

CREATE VIEW<>[(<列名>[,<列名>])……] AS<子查询>[WITH CHECK OPTION];

WITH CHECK OPTION表示对视图进行UPDATE、INSERT、DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(子查询中的条件表达式)

组成视图的属性列名或者全部省略或者全部指定。若省略属性名，默认由SELECT的目标列所有字段组成。

删除

DROP VIEW <视图名> [CASCADE];

CASCADE 级联删除可以将该视图上导出的其他视图全部一起删除。

查询

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

更新

• 对已经定义的视图，用户可以像对基本表一样对视图进行INSERT、DELETE、UPDATE的更新操作
• 由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。
• 并不是所有视图都可以更新的，有些视图无法唯一转换成对应的基本表的更新。目前的数据库管理系统通常只允许对行列子集视图进行更新。