数据库--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 创建表

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

复制代码

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 修改表

        添加列：

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

复制代码

        删除列：

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

复制代码

 restrict：如果该列被其他列引用，则无法删除该列
 cascade：引用该列的其他列会和该列同时删除
        修改列：

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

复制代码

1.4 删除表

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

复制代码

注：drop table不光删除表中的全部元组，还删除了该表的关系模式
2 创建索引

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

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

复制代码

        寄义：在on字句中指定的一列或者多列上创建索引，还可以指定某列索引值的分列序次
        序次：ASC（升序）、DESC（降序）
        unique：此索引的值必须唯一
        cluster：此索引是聚集/聚簇索引
2.1 修改、删除索引

        修改：

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

复制代码

        删除：

1. drop index <索引名>;

复制代码

3 创建视图

        视图是数据库中的一个查询的查询结果构成的“虚关系”。
        将重复实行的SQL查询存储在数据库中，并对其进行定名，在之后的查询中可通过名字进行查询，可以减少重复工作。
        一个视图就是一个定名的存储在数据库中的查询，视图中的数据即为该查询对应的查询结果

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

复制代码

子查询：任何select语句
        在实行create view语句时并不实行此中的子查询语句，只是把视图的界说存入数据字典。

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

复制代码

3.1 修改视图

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

复制代码

3.2 删除视图

1. drop view <视图名> [cascade];

复制代码

该语句将把指定的视图界说从数据字典中删除
cascade：该视图和由该视图导出的视图都会被删除
3.3 物化视图

        视图是一种“虚关系”，实际查询时需要根据界说查询底层关系，当存在大量这样的查询时会有较高的成本。
        某些数据库支持物化视图，像存储表一样将创建的视图关系，“物化”存储在数据库中。
        物化视图的创建、修改与删除语法同视图雷同，区别是多列关键字materialized

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

复制代码

二.SQL数据操纵语言

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

1 数据查询

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

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

复制代码

select子句：指定要显示的属性列
from子句：指定从哪些表或者视图中查询数据
where子句：筛选满意条件的元组
group by子句：指定按照哪些列对元组进行分组
having子句：对分组结果进行筛选
order by子句：将查询结果按照指定顺序进行排序
1.1 选择和投影

        选择操作可以选择表中的多少元组，通过where子句实现
        投影操作可以选择表中的多少列，通过select子句实现
1.2 投影操作

        投影操作可以选择表中的多少列，主要体如今select子句后面的列表达式
        查询指定列：select句子中依次列出要查找列的名字
        查询全部列：用*号替换全部列
        查询经过盘算的值：对列进行盘算后的表达式
        用户可以通过AS指定列别名来改变查询结果的列标题

1. 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可以用来查询与匹配串匹配的字符串。其语法格式为：

1. [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>.]<列名1> <比较运算符> [<表名2>.]<列名2>;
2. [<表名1>.]<列名1> between [<表名2>.]<列名2> and [<表名2>.]<列名3>;

复制代码

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

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

复制代码

1. select *
2. 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）

1. select *
2. 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 插入数据

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

复制代码

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

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

复制代码

2.3 修改数据

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

复制代码

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

1. delete from <表名> where 条件;

复制代码

如果没有where子句，则表对应的数据将全部被删除
三. SQL数据控制语言

        SQL数据控制语言：
                权限授予：用于向用户授予数据库操作权限
                权限收回：用于向用户收回数据库操作权限
1 权限授予

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

复制代码

该语句的寄义为将某些对象（由on子句指定，对象可以是表的形式）的某些操作权限（由grant子句指定）授予给某些用户（由to子句指定）：
        with grant option：被授权用户可以把该grant语句授予他的权限再授予其他用户
        权限：查询数据权限（select）、插入新数据权限（insert）、更新数据权限（update）、删除数据权限（delete）
        可以指定grant语句的用户：数据库管理员、数据库的创建者，拥有grant子句中指定的用户
2 权限收回

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

复制代码

该语句的寄义为收回某些用户(由FROM子句指定)对某些对象(由ON子句指定)的某些操作权限(由REVOKE子句指定)
        CASCADE:支持级联收回，即由这些用户授予了以上权限的用户的这些权限也会被收回(默认选项)
        RESTRICT:不支持级联收回
四.存储过程和函数

        存储过程和函数是实现经过编译并存储在数据库中的一段SQL语句的集合
        存储过程和函数可以对一段代码进行封装，以便日后调用
        数据库中创建存储过程的语句为create procedure，并通过call语句加存储过程名来调用存储过程
        数据库中创建函数的语句为create function，并通过函数名来调用函数
        存储过程的上风：降低网络开销、安全性高、性能高
        存储过程的劣势：开发调式难、可移植性差、难以支持分布式数据库
1 创建和调用存储过程

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

复制代码

1. call <存储过程名()>;

复制代码

括号内指定参数列表（也可以没有参数，但括号不可省略）
停止符表示存储过程结束，利用 delimiter <符号> 来界说停止符
参数格式为:[in| out | inout] 参数名 参数数据类型
        in表示该参数为输入参数，out表示该参数为输出参数，inout表示该参数既可以为输入也可以为输出

2 存储过程和函数的作用

        存储过程和函数都用于提高数据库性能，减少频仍访问数据库和减少网络耽误等方式加速实行服从。
        函数：简朴的盘算型任务，例如字符串或日期拼接、返回单个值等。
        存储过程：复杂的业务逻辑、更新和删除相关操作。存储过程需要显式地被调用，而且可以包含各种复杂的控制结构和代码块。
3 创建和调用函数

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

复制代码

1. delimiter ;
2. 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语块中
        变量界说：

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

复制代码

        变量赋值：

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

复制代码

5.2 IF语句

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

1. if <表达式> then <sql语句>;
2. [elseif <表达式> then <sql语句>;
3. elseif <表达式> then <sql语句>;
4. ]
5. else <sql语句>;
6. end if;

复制代码

5.3 LOOP语句

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

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

复制代码

程序实行时，会重复实行loop后的语句块，知道循环被退出。
在loop语句中，利用leave子句可跳出循环。
loop语句中必须包含leave子句，否则会陷入死循环
标签可以用来标志一个loop语句，为可选项。
5.4 WHILE语句

        while语句也是循环语句，当满意while语句的循环条件时，循环会不停实行；当不满意while语句的循环条件时，会跳出循环：

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

复制代码

5.5 删除存储过程和函数

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

复制代码

五.触发器

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

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

复制代码

触发器名：要创建的触发器的名字
触发时机：指定了触发实行的时间，可以为before（触发事件之前）或after（触发事件之后）
触发事件：指定了当发生何种事件时，触发器会被激活，包括insert、delete、update
表名：指定了触发器时在哪张表上创建的
触发动作体：触发事件发生后要实行的SQL语句块
        对于触发事件作用的每一行（for each row），会实行触发动作体
        对于触发事件作用的每一行，在触发事件发生之前该行称为old，在触发事件发生之后称为new
        可以利用old和new来访问触发事件发生前后的元组的值

2 删除触发器

1. drop trigger <触发器名>;

复制代码

触发器时基于激活的，对数据库中每行数据的修改都会调用触发器。因此可能会导致数据库性能的降低，所以要制止编写太多的触发器。
触发器应用场景：数据完整性、数据审计、数据安全性、数据备份和同步

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。