数据库--SQL

SQL：Structured Query Language，结构化查询语言

SQL是用于管理关系型数据库并对其中的数据进行一系列操作（包括数据插入、查询、修改删除）的一种语言

分类：数据定义语言DDL、数据操纵语言DML、数据控制语言DCL、事务处理、存储过程和函数、触发器

一. SQL数据定义语言

DDL包含创建、修改、删除表的语句，创建、修改、删除索引的语句，创建、修改、删除视图的语句。

	创建	修改	删除
表	create table	alter table	drop table
索引	create index	alter index	drop index
视图	create view	alter view	drop view

1 创建表

create table <表名>{<列名1> <数据类型> [列级完整性约束],<列名2> <数据类型> [列级完整性约束],[表级完整性约束]
};

1.1 表的数据类型

	数据类型	描述
文本型	char(n)	长度为n的定长字符串
文本型	varchar(n)	长度为n的变长字符串
数字型	int	整数（4字节）
	smallint	短整数（2字节）
	bigint	大整数（8字节）
	float	单精度浮点数
	double	双精度浮点数
	decimal(p,d)	定点数，由p位数字组成，小数点后有d位数字
	boolean	布尔型
时间型	date	日期，包含年、月、日，格式为YYYY-MM-DD
	time	时间，包含时、分、秒，格式为HH:MM:SS
	timestamp	时间戳，格式为YYYY-MM--DD HH:MM:SS

1.2 完整性约束

非空约束（not null）：指定某列不可为空

唯一约束（unique）：指定某列不能重复

主键约束（primary key）：指定某列位主键，主键属性必须是非空且唯一的

外键约束（foreign key ... references）：指定某列为外键，同时需指定外键引用其他表的主键（可以与本表的每个主键，进行外键约束）

1.3 修改表

添加列：

alter table <表名> add [column] <列名> <数据类型>;

删除列：

alter table <表名> drop [column] [restrict | cascade];

restrict：如果该列被其他列引用，则无法删除该列

cascade：引用该列的其他列会和该列同时删除

修改列：

alter table <表名> alter column <列名> <数据类型>;

1.4 删除表

drop table <表名> add [restrict|cascade];

注：drop table不仅删除表中的所有元组，还删除了该表的关系模式

2 创建索引

为了提高查询速率，可以在一个表上建立一个或者多个索引，以加速查询。

create [unique] [cluster] index <索引名>
on <表名> (<列名> [<次序>], <列名> [<次序>]);

含义：在on字句中指定的一列或者多列上建立索引，还可以指定某列索引值的排列次序

次序：ASC（升序）、DESC（降序）

unique：此索引的值必须唯一

cluster：此索引是聚集/聚簇索引

2.1 修改、删除索引

修改：

alter index <旧索引名> rename to <新索引名>;

删除：

drop index <索引名>;

3 创建视图

视图是数据库中的一个查询的查询结果构成的“虚关系”。

将重复执行的SQL查询存储在数据库中，并对其进行命名，在之后的查询中可通过名字进行查询，可以减少重复工作。

一个视图就是一个命名的存储在数据库中的查询，视图中的数据即为该查询对应的查询结果

create view <视图名> [(<列名1>,<列名2>)] as <子查询>;

子查询：任何select语句

在执行create view语句时并不执行其中的子查询语句，只是把视图的定义存入数据字典。

select * from <视图名> where 条件;

3.1 修改视图

alter view <视图名> as <子查询>;

3.2 删除视图

drop view <视图名> [cascade];

该语句将把指定的视图定义从数据字典中删除

cascade：该视图和由该视图导出的视图都会被删除

3.3 物化视图

视图是一种“虚关系”，实际查询时需要根据定义查询底层关系，当存在大量这样的查询时会有较高的成本。

某些数据库支持物化视图，像存储表一样将创建的视图关系，“物化”存储在数据库中。

物化视图的创建、修改与删除语法同视图类似，区别是多列关键字materialized

create materialized view <物化视图名> [(<列名1>,<列名2>)] as <子查询>;

二.SQL数据操纵语言

DML包括数据的查询、插入、删除和修改

1 数据查询

数据查询是从数据库中获取满足一定条件的数据，是数据库的核心操作。利用SQL进行数据查询的基本语法为：

select [all | distinct] <列表达式>
from <表名或视图名>
where 条件
group by <列名1>,<列名2>
having 条件
order by <列表达式> [<次序>];

select子句：指定要显示的属性列

from子句：指定从哪些表或者视图中查询数据

where子句：筛选满足条件的元组

group by子句：指定按照哪些列对元组进行分组

having子句：对分组结果进行筛选

order by子句：将查询结果按照指定顺序进行排序

1.1 选择和投影

选择操作可以选择表中的若干元组，通过where子句实现

投影操作可以选择表中的若干列，通过select子句实现

1.2 投影操作

投影操作可以选择表中的若干列，主要体现在select子句后面的列表达式

查询指定列：select句子中依次列出要查找列的名字

查询全部列：用*号替代所有列

查询经过计算的值：对列进行计算后的表达式

用户可以通过AS指定列别名来改变查询结果的列标题

select <列名> as <别名> from <表名或视图名>;

1.3 选择操作

选择操作通过where子句选择若干满足条件的元组

where子句后面跟着一个条件表达式，满足该条件表达式的元组会被返回

查询条件	谓词
比较	= , > , < , >= , <= , != , <> , !> , !< , Not+上述比较符号
确定范围	between and, not between and
确定集合	in, not in
空值	is null, is not null
逻辑运算	and, or
字符串运算	like, not like, _ , % , escape

like可以用来查询与匹配串匹配的字符串。其语法格式为：

[not] like "<匹配串>" [escape "<转码字符>"];

匹配串一般由字符和通配符（ _ 或者 %）组成

_：表示匹配任意一个字符

%：表示匹配任意多个字符

当字符串的所有字符均可匹配成功时，该字符串的like查询结果为真

当匹配串本身就包含通配符时，需要在匹配串中包含的通配符前加上转义字符来纪念性转移，同时需要使用escape "<转码字符>" 来指定转码字符

1.4 聚集操作

为了查询一些数据聚集之后的结果，需要使用聚集函数。常见的聚集函数有：

聚集函数	含义
count([distinct \| all *])	统计元组个数
count([distinct \| all] <列名>)	统计一列值的个数
sum([distinct \| all] <列名>)	统计一列值的总和
avg([distinct \| all] <列名>)	统计一列值平均值
max([distinct \| all] <列名>)	统计一列值的最大值
min([distinct \| all] <列名>)	统计一列值的最小值

其中，distinct表示去除重复值，all表示保留所有值

1.5 分组操作

group by子句可以将查询的满足条件的元组按某一列或者多列的值进行分组，值相等的为一组。

当进行分组，并且使用聚集函数时，聚集函数分别作用于每个组。

可以使用having句子对分组之后得到的组进行筛选。

任何没有出现在group by子句中的列，如果出现在select子句中的话，那么它一定是出现在聚集函数内部。

1.6 排序操作

order by将查询结果按照一列或者多列的值进行排序，desc降序，asc升序

1.7 连接操作

同时涉及两个以上表的查询称为连接查询，通常需要在from子句中指定需要连接的表，在where子句中指定连接条件。常见的格式为：

[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>;
[<表名1>.]<列名1> between [<表名2>.]<列名2> and [<表名2>.]<列名3>;

注：如果连接操作的两个表是同一个表，则需要在from子句中使用as来重命名表的名字，以区分。

连接操作的三种写法：

select * from 表1, 表2 where 表1.列1==表2.列2;

select * 
from ((表1 inner join 表2 on 表1.列1=表2.列2);

这里连接的方式有内连接（inner join，join）、左连接（left join，left outer join）、右连接（right join，right outer join）、外连接（full join，full outer join）、笛卡尔积（cross join）

select * 
from ((表1 inner join 表2 using(列)));

using使用表示表1和表2具有同名字段

1.8 外连接操作

外连接是连接运算的扩展，用来处理缺失值。

左外连接取出左表中所有与右表中的任一元组都不匹配的元组，用控制填充所有右表中的属性，再将产生的元组加到左表和右表自然连接的结果中。

右外连接取出右表中所有与左表中的任一元组都不匹配的元组，用控制填充所有左表中的属性，再将产生的元组加到右表和左表自然连接的结果中。

全外连接的查询结果是左外连接和右外连接查询结果的并集。

1.9 嵌套查询

一个select-from-where语句是一个查询块，将一个查询块嵌套在另外一查询块的where子句、from子句或者having子句的条件中的查询称为嵌套查询。上层的查询称为外层查询或者父查询，下层的查询称为内层查询或者子查询。在求解嵌套查询时，先求解子查询，然后基于子查询的求解结果来求解父查询。

有些嵌套查询可以使用连接操作实现。当内层查询的查询结果是一个值时，使用=,>,<等比较运算符；当内层查询的结果是一个集合时，使用in, not in, any, all 等。

当子查询的查询条件不依赖于父查询的查询时为不相关子查询，依赖于父查询的某个属性的查询为相关子查询。

1.10 集合操作

SQL语句查询结果是元组的集合，因此，可以将查询结果进行集合操作，SQL中支持的集合操作主要包括并（union）、交（intersect）和差（except），参加集合操作的各查询结果列数必须相同，对应的数据类型也需要相同。

直接使用union、intersect、except表示去重，union/intersect/except all表示不去重

1.11 关系代数与SQL的转换

关系代数是关系型数据库理论的一部分，是SQL的基础，SQL在执行时需要先转换为等价的关系代数表达式。以下是转换规则：

关系代数运算	对应SQL语句	关系代数运算	对应SQL语句
选择运算	where	连接运算	join
投影运算	select	赋值运算	as
并运算	union	除运算	not exists
差运算	except	去重运算	distinct
笛卡尔积运算	from	广义投影运算	select
重命名运算	as	聚集运算	聚集函数
交运算	intersect	分组运算	group by

2 数据更新

数据更新包括插入数据、删除数据、修改数据

在执行数据更新时会检查所插入、修改和删除的元组是否破坏表中的完整性约束。如果不满足完整性约束，则可能会执行失败。

2.1 插入数据

insert into <表名> [(<列名1>,<列名2>)] values (<常量1>,<常量2>);

如果没有在into子句中出现的列，新元组在这些列上的取值为空值。into子句中的列名可以省略，那么values子句中则要按表中列的顺序依次列出所有列的取值。

2.2 插入子查询结果

insert into <表名> [(<列名1>,<列名2>)]
子查询;

2.3 修改数据

update <表名> set <列名1>=<表达式1> [,<列名2>=<表达式2>] where 条件;

如果没有where子句，则列对应的数据将全部被修改

2.4 删除数据

delete from <表名> where 条件;

如果没有where子句，则表对应的数据将全部被删除

三. SQL数据控制语言

SQL数据控制语言：

权限授予：用于向用户授予数据库操作权限

权限收回：用于向用户收回数据库操作权限

1 权限授予

grant <权限>[,<权限>]
on <对象类型><对象名>[,<对象类型><对象名>]
to <用户名>[,<用户名>]
[with grant option];

该语句的含义为将某些对象（由on子句指定，对象可以是表的形式）的某些操作权限（由grant子句指定）授予给某些用户（由to子句指定）：

with grant option：被授权用户可以把该grant语句授予他的权限再授予其他用户

权限：查询数据权限（select）、插入新数据权限（insert）、更新数据权限（update）、删除数据权限（delete）

可以指定grant语句的用户：数据库管理员、数据库的创建者，拥有grant子句中指定的用户

2 权限收回

revoke <权限>[,<权限>]
on <对象类型><对象名>[,<对象类型><对象名>]
from <用户名>[,<用户名>]
[cascade | restrict];

该语句的含义为收回某些用户(由FROM子句指定)对某些对象(由ON子句指定)的某些操作权限(由REVOKE子句指定)

CASCADE:支持级联收回，即由这些用户授予了以上权限的用户的这些权限也会被收回(默认选项)

RESTRICT:不支持级联收回

四.存储过程和函数

存储过程和函数是实现经过编译并存储在数据库中的一段SQL语句的集合

存储过程和函数可以对一段代码进行封装，以便日后调用

数据库中创建存储过程的语句为create procedure，并通过call语句加存储过程名来调用存储过程

数据库中创建函数的语句为create function，并通过函数名来调用函数

存储过程的优势：降低网络开销、安全性高、性能高

存储过程的劣势：开发调式难、可移植性差、难以支持分布式数据库

1 创建和调用存储过程

create procedure <存储过程名> ([参数,参数])
begin
<SQL语句>
end <终止符>

call <存储过程名()>;

括号内指定参数列表（也可以没有参数，但括号不可省略）

终止符表示存储过程结束，使用 delimiter <符号> 来定义终止符

参数格式为:[in| out | inout] 参数名参数数据类型

in表示该参数为输入参数，out表示该参数为输出参数，inout表示该参数既可以为输入也可以为输出

2 存储过程和函数的作用

存储过程和函数都用于提高数据库性能，减少频繁访问数据库和减少网络延迟等方式加速执行效率。

函数：简单的计算型任务，例如字符串或日期拼接、返回单个值等。

存储过程：复杂的业务逻辑、更新和删除相关操作。存储过程需要显式地被调用，并且可以包含各种复杂的控制结构和代码块。

3 创建和调用函数

create function <函数名> ([参数,参数])
returns <数据类型>
begin
<SQL语句>
end <终止符>

delimiter ;
select <函数名(参数)>;

参数格式与存储过程相似，但只能是in参数，returns返回数据类型，return返回数据值

4 存储过程和函数区别

存储过程可以通过out或者inout参数返回多个值，而函数只能返回returns子句中指定的某一类型的单值或表对象。

存储过程的参数可以为in、out、inout，而函数的参数只能是in类型。

存储过程可以通过call语句作为一个独立的部分来调用和执行，而函数可以作为查询语句的一部分来调用。此外，由于函数可以返回表对象，因此函数的返回结果可以用在查询语句的from子句中。

创建函数时必须指定返回值数据类型，且函数体内必须有一个returns语句。

存储过程中可以执行更新表的数据库操作，而函数不行。

5 变量和流程控制

变量：可以在存储过程和函数中声明比使用变量

流程控制：可以用来改变存储过程和函数内部语句的执行顺序。包括IF、LOOP、WHILE、REPEAT、LEAVE等。

5.1 变量

可以在存储过程和函数中声明并使用变量，变量的作为范围是在begin...end语块中

变量定义：

declare <变量名> <数据类型> [default <默认值>];

变量赋值：

set <变量名>=<表达式>;

5.2 IF语句

IF语句包含多个判断条件，根据判断结果是否为真来选择执行哪个分支。

if <表达式> then <sql语句>;
[elseif <表达式> then <sql语句>;
elseif <表达式> then <sql语句>;
]
else <sql语句>;
end if;

5.3 LOOP语句

LOOP语句时循环语句，用来重复执行一些语句：

[<标签>:] loop <sql语句> end loop [<标签>];

程序执行时，会重复执行loop后的语句块，知道循环被退出。

在loop语句中，使用leave子句可跳出循环。

loop语句中必须包含leave子句，否则会陷入死循环

标签可以用来标志一个loop语句，为可选项。

5.4 WHILE语句

while语句也是循环语句，当满足while语句的循环条件时，循环会一直执行；当不满足while语句的循环条件时，会跳出循环：

[<标签>:] while <表达式> 
do <sql语句>
end while [<标签>];

5.5 删除存储过程和函数

drop procedure <存储过程名>;
drop function <函数名>;

五.触发器

        触发器是与表相关的特殊的存储过程，在满足特定条件时，它会被触发执行
        触发器是定义在基本表上的，当基本表被修改(比如插入、删除、更新数据)时，会激活定义在其上的触发器，该基本表称为触发器的目标表
        触发器可以用来保证数据库的完整性

1 创建触发器

create trigger <触发器名>
<触发时机> <触发事件> on <表名>
for each row
<触发动作体>;

触发器名：要创建的触发器的名字

触发时机：指定了触发执行的时间，可以为before（触发事件之前）或after（触发事件之后）

触发事件：指定了当发生何种事件时，触发器会被激活，包括insert、delete、update

表名：指定了触发器时在哪张表上创建的

触发动作体：触发事件发生后要执行的SQL语句块

对于触发事件作用的每一行（for each row），会执行触发动作体

对于触发事件作用的每一行，在触发事件发生之前该行称为old，在触发事件发生之后称为new

可以使用old和new来访问触发事件发生前后的元组的值

2 删除触发器

drop trigger <触发器名>;

触发器时基于激活的，对数据库中每行数据的修改都会调用触发器。因此可能会导致数据库性能的降低，所以要避免编写太多的触发器。