数据库笔记--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的更新操作
- 由于视图是不实际存储数据的虚表,因此对视图的更新最终要转换为对基本表的更新。
- 并不是所有视图都可以更新的,有些视图无法唯一转换成对应的基本表的更新。目前的数据库管理系统通常只允许对行列子集视图进行更新。