数据库【第二部分】

一.SQL结构化语言

1.1 DQL（Data Query Language）：数据查询语言，用来查询数据库中表的记录(数据)

1.1.1 基本查询

语法：select 查询列表 from 表名；（查询列表换为*的时候是查询所有字段）
起别名：使用AS或空格（注意：有的别名需要用单引号或者双引号引用）
格式：SELECT 字段名 AS xx,字段名 AS xx From 表名

1.1.2 常见函数

1.1.2.1单行函数

字符函数
LENGTH()：用于获取参数值的字节个数
CONCAT():拼接函数
UPPER():转换为大写
LOWER():转换为小写
SUBSTR/SUBSTRING（‘指定字符串’,n,m）:后接out_put输出
INSTR():返回子串第一次出现的索引，找不到返回0
TRIM():去除前后指定的的字符,比如：SELECT TRIM(‘a’ FROM ‘aaaaa宏aaaaa’) AS OUT_PUT
LPAD():用指定的字符左填充指定长度（如果截断，就从右边截断）,比如：SELECT LPAD(‘放到赛’,10,‘$’) AS out_put
RPAD():用指定的字符右填充指定长度（如果截断，就从左边截断）
REPLACE()替换，SELECT REPLACE(str,from_str,to_str);第一个定义一个字符串，第二个从这个里面找到需要替换的内容，第三个是设置替换的结果

数学函数
round()：四舍五入（如果需要设置小数个数，则在需要四舍五入的数后面加’,'后接位数）
ceil()：向上取整
floor()：向下取整
truncate(X,D)：截断(X为需要截断的小数，D为截断的小数位数)
MOD(n,m):取余（n为被除数，m为除数）

日期函数
NOW()：返回当前系统日期和时间
curdate()：返回日期，不包含时间
curtime()：返回时间，不包含日期
获取指定部分的日期，对于年月日以及时分秒：每个对应的用每个的英文加NAME,就比如年：YEARNAME(NOW()) 年； 以此类推
SELECT STR_TO_DATE(str,format)
DATE_FORMAT(date,format)

流程控制函数
IF语句：SELECT IF(expr1,expr2,expr3)：如果expr1成立，执行expr2，不成立执行expr3
case语句（类似于switch语句（等值判断））

多重if在mysql中的使用（判断区间），相较于java，在mysql中使用case。只是和平常的case不一样，case后不加任何东西，直接写when，后和以上的case一样

1.1.2.2 分组函数（统计函数，聚合函数，组函数）做统计使用（传进去一组值，返回一个值）

分类：sum()求和，avg()平均值，max()最大值，min()最小值，count()计算个数
支持类型：sum,avg一般处理数值型，其他三种可以处理任何类型，且都忽略null值，分组函数都可以和distinct（去重函数）搭配使用

1.1.3 进阶查询

1.1.3.1 模糊查询

like： 格式：SELECT 查询列表 FROM 表名 where 字段 like ‘包含的字符’；
特点：一般和通配符搭配使用，%多个字符（在’包含字段的字符’里使用），_ 单个字符。

between and：格式：SELECT 查询列表 FROM 表名 where 字段 BETWEEN 条件 AND 条件 提高语句的简介性，包含临界值

in：前接字段后接需要查询的具体字段，如果有多个字段，必须使用括号，它相较于like来说，IN查的是一个大的字段，而LIKE查的是某一截，另外LIKE不用括号

is unll：格式：字段 IS NULL，查询没有特定条件的字段，相反，要查询存在特点条件的语句就是IS NOT NULL

1.1.3.2 排序查询

格式：SELECT 查询列表 FROM 表名 WHERE order by 排序列表名 asc(升序)/desc(降序)
如果不写关键字，默认是升序，order by后支持字段(单个/多个)，别名，表达式，函数；一般放在查询语句的最后面（limit子句例外）。

1.1.3.3 分组查询

在分组前筛选，然后再需要分组的字段where后添加grouo by 关键字，grouo by后接需要分组的字段，可以有多个字段，中间必须使用,隔开，但必须放在order by前 ，from 表后，一般搭配count使用。
在分组后筛选，搭配having使用，由于用的数据源是之前筛选好的

where 和 having 的区别？

1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。

1.1.3.4 连接查询

为什么要使用该方法;表和表之间存在连接，如果在查询的时候就会发生错误，就必须要设置相应的条件来防止输出错误

内连接
等值连接:利用引用“.”来确定连接条件（一般放在WHERE后面），相应的可以为表取别名，当然如果需要加筛选条件，WHERE的附加条件后面加AND，再写筛选条件,多表连接条件：多表连接的结果为多表的交集部分，n表连接，至少需要n-1个条件，多表的顺序没有要求，一般需要为表取别名，可以搭配前面所有的查询子句

非等值连接:只是在where条件语句中的等于条件换成其他符合条件的判断语句

自连接:对于连接条件在同一个表内，相当于查询了两次，只是第一次查的内容和第二次查的内容不一样

外连接
特点：如果表中有和他匹配的条件语句，则显示匹配的值；如果没有则显示null；外连接查询结果=内连接查询结果+主表中有而从表中没有的记录
左外连接
* 语法：
select 字段列表
from 表1
left [outer] join 表2 on 条件
…
* 注意：
左外连接查询的是左表所有数据以及其交集部分

右外连接
* 语法：
select 字段列表
from 表1
right [outer] join 表2 on 条件
…
* 注意：
右外连接查询的是右表所有数据以及其交集部分

全外连接
cross join 类似于笛卡尔积

1.1.3.5 子查询

查询中嵌套查询，称嵌套查询为子查询或内查询；外部的查询语句，称为主查询或外查询

分类
标量 子查询：结果只有一行一列
列 子查询：结果集只有一列多行
行 子查询：结果集有一行多列或者多行多列
表 子查询：结果集一般为多行多列

其按照位置所在查询中放位置：
select后面只支持标量 子查询
from 后支持表 子查询，将子查询结果充当一个表，必须起别名
where或having 后支持标量，列，行 子查询
exitsts后支持 相关子查询（表子查询），查询该表是否有值，固它的禅熏结果只有0或1

特点（放在where或者having后）：
子查询放在小括号内，子查询一般放在条件的右侧

单行子查询
一般搭配这单行操作符使用；
结果集只有一行
一般搭配单行操作符使用：> < = <> >= <=
非法使用子查询的情况：
a、子查询的结果为一组值
b、子查询的结果为空

多行子查询
列子查询，一般搭配这多行操作符使用
结果集有多行
一般搭配多行操作符使用：any、all、in、not in
in： 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替

子查询的执行都优先于主查询，主查询进行条件判断时都用到了子查询的结果

补充：多行操作符

1.1.3.6 分页查询

语法：

select 字段|表达式,…
from 表
where 条件
group by 分组字段
having 条件
order by 排序的字段
limit 起始的条目索引，条目数;

特点：limit放在查询语句的最后，位置和执行顺序都是在最后，在最后追加limit offset(要显示条目的起始索引)（起始索引从0开始）,size（需要显示的条目个数），如果是从0开始，0可以省略，只需要写显示的条目个数。
公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage（每页显示条目数sizePerPage，要显示的页数 page）

1.1.3.7 联合查询

使用关键字UNION去连接查询的语句，如果下面还有连接的语句，则还是需要写UNION

注意：
将多条查询的语句的结果合并为一个结果,且多个表没有直接联系，但查询的信息一般一样，
查询的语句的列数必须一样，其中多条查询语句的每一列的类型和顺序最好一致，
union关键字默认去重，如果需要不去重，则需要加union all

1.2 DML(Data Manipulate Language):数据操纵语言，用于添加、删除、修改数据库记录，并检查数据完整性

1.2.1插入语句（insert）

语法一：insert into 表名(列名,…) values(值1，…)；
语法二：insert into 表名 select 列名=值，UNION …
注意：
插入的值的类型要与列的类型一致或者兼容；
如果在列中没有添加的属性，再添加的时候的可以不写，要么写null。

1.2.2修改语句（update）

修改单表：update 表名 set 列名1 = 值1, 列名2 = 值2,… [where 条件]

修改多表： update 表1 别名1,表2 别名2 set 字段=新值，字段=新值 where 连接条件 and 筛选条件

1.2.3删除语句（DELETE）

方式一：delete
单表删除语法：delete from 表名 where 筛选条件（按行删）
删除多表（补充）

方式二：truncate
单表语法：truncate table 表名
删除多表（补充）:该语句中不允许加where（条件语句 ），删除整个表

两种方式的对比:

truncate不能加where条件，而delete可以加where条件;
truncate的效率高
truncate删除带自增长的列的表后，如果再插入数据，数据从1开始
delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
truncate删除不能回滚，delete删除可以回滚

1.3 DDL（Data Define Languge）：数据定义语言，用于库和表的创建、修改、删除（alter关键字 操纵）

1.3.1 库的管理

创建库：creat database （if not exits） 库名 ,其中 if not exits判断库是否存在
修改库名：rename database 库名 to 新库名，但是这句话已近舍弃，现在改不了了，只能修改库的字符集：alter database 库名 character 需要更改的字符集名称
库的删除：drop database （if exits） 库名，其中 if exits判断库是否存在
查询当前正在使用的数据库名称：select database();
使用数据库：use 数据库名称;
查询所有数据库的名称：show databases;
查询某个数据库的字符集:查询某个数据库的创建语句：show create database 数据库名称;

1.3.2 表的管理

表的创建：

create table 表名(
列名1 数据类型1 【字段约束】,
列名2 数据类型2 【字段约束】,
…
列名n 数据类型n 【字段约束】
);
* 注意：最后一列，不需要加逗号（,）

表的修改：
语法模板：

例子说明操作：

（1）修改列名 ALTER(操纵) COLUMN（列）,COLUM可以省略
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
（2）修改列的类型或约束 MODIFY（修改） ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
（3）添加新列 ADD（添加） ALTER TABLE book ADD COLUMN number INT;
（4）删除列 DROP（删除） ALTER TABLE book DROP COLUMN number;
（5）修改表名 RENAME TO(换成 表名) ALTER TABLE book RENAME TO books;

表的删除： IF EXISTS(判断存在才删除) DROP TABLE IF EXISTS 表名;

表的复制：
（1）复制结构和数据，在创建的同时，用了一个子查询，将数据全出查询到再复制到新表中。

CREATE TABLE 目标表名 SELECT * FROM 复制源表名;

（2）复制表的结构，没有数据

CREATE TABLE 目标表名LIKE 复制源表名;

1.3.3 MySQL常用数据类型

整型：int

小数：
浮点型（较短的文本）float(M,D);double(M,D)
定点型（较长的文本）dec(M,D);decimal(M,D)
原则：所选择的类型越简单越好，能保存的数值类型越小越好

特点：

字符型：较短的文本使用char或者varchar
区别：

日期型：

datetime和timestamp的区别：

1.3.3 约束

含义：用于限制表中的数据，为了保证的数据的准确性和可靠性

六个约束：

NOT NULL:非空
DEFAULT:默认
UNIQUE:唯一，和主键的约束相似，只是值可以为空（只允许一个值为空）
CHECK:检查约束【mysql中不支持】

PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空（简单通俗理解，不能有重复的值），如果加上auto_increment就代表自增长，注意：非空且唯一；一张表只能有一个字段为主键；主键就是表中记录的唯一标识
(1)创建表时，添加主键约束

(2)删除主键
ALTER TABLE stu DROP PRIMARY KEY;

(3)创建完表后，添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;

FOREIGN KEY:外键，用于限制两个表的关系，用于保障该字段的值通过对应关系来自于主表的关联的值（一个值—>主表中对应另外一个表相关联的值）
(1)在创建表时，可以添加外键

(2)删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

(3)创建表之后，添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

(4)级联操作
级联删除：在添加外键的时候:alter tables 表名 add constraint 外键名称 foreign key(外键字段名称) refences 主表名称(主表列名称) ON DELETE CASCADE;

1.4TCL（Data Control Language）：数据事务语言

(1)事务介绍：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。在这个单元中，每个sql语句是相互依赖的,而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影
响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。
回滚：把刚才执行的操作撤销，回到做出的状态

(2)事务的ACID特征

原子性：指事务是不可分割的工作单位，事务中的操作要么都发生，要么都不发生
一致性：事务必须是数据库从一个一致性状态变换为另一个一致性状态
隔离性：指一个事务的执行不能被其他事务干扰，即一个事务内部的操作以及使用的数据对并发的其他事务是隔离的，并发执行额各个事务之间不能互相干扰
持久性：指一个事务一旦被提交，它对数据库中数据的改变就是永久性，接下来的其他操作和数据库故障不应该对其有任何影响

(3)事务的创建
显示事务：步骤一：开启事务：set autocommit = 0;start trransaction;(可选)
步骤二：编写事务中的sql语句（）
步骤三：结束事务，commit（提交事务）;rollback（回滚事务）

隐式事务：没有明显的开启和结束事务的标志(insert、update、delete语句本身就是一个事务)

(4)事务并发问题：

脏读：在未更新的数据读取（未被提交）
不可重复读：读取更新后同一个数据，数据不相同
幻读：插入数据前后表的结构不一致

隔离性解释，数据库系统必须具有隔离并发运行各个事务的能力，使他们不会相互影响，避免各种并发问题，隔离级别越高，数据一致性就越好，并发性越弱

（5）设置隔离级别
数据库提供的 4 种事务隔离级别:

设置隔离级别模板：set session|global transaction isolation level 隔离级别名;
查看隔离级别：select @@tx_isolation；

二.视图

（1）简介：通过创建一个虚拟表,来延用它的功能

（2）好处：sql语句提高重用性，效率高，和表实现了分离，提高了使用的安全性,简化复杂的sql操作，不必知道它的查询细节.

（3）应用场景;多个地方用到同样的查询结果,该查询结果使用的sql语句较复杂

（4）视图和表的区别：视图仅仅值保存了sql的逻辑，不占用物理空间；而表占用，使用的方式和表一样，没有区别。

（5）使用:

创建：create view 表名 as 查询语句
修改:UPDATE 视图名 SET 视图字段名=值 WHERE 条件
删除:drop view 视图名,视图名,…;
查看:desc 视图名；show create view 视图名
插入：INSERT INTO 视图名（视图字段）VALUES(值)

更新：视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的
包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表。

三. 存储过程（类似于java中的方法）和函数

3.1 存储过程

3.1.1含义以及好处

一组预先编译好的sql语句的集合，理解成批处理语句
好处：提高代码的复用性，简化操作，减少编译次数并且减少和数据库服务器的链接次数，提高效率

3.1.2 创建

（1）creat procedure 存储过程名(参数列表) begin 存储过程体(一组sql语句) end （如果存储过程体仅仅一句话，begin end 可以省略）

（2）参数列表包含三部分：参数模式 参数名 参数类型
参数模式：in（作为输入，说明改参数需要调用传入值） out（作为输出，该参数可以作为返回值） inout（作为输入和输出，可以传入值可以返回值）

（3）运行：这里一般在命令提示窗里运行，使用delimiter 结束标记来结束，不用’;'，一般写在开头，最后的end 后要书写其结束符号，结束符号可以自己定义。

3.1.3 使用

（1）分类：无返回无参；仅仅带in类型，无返回有参；仅仅带out类型，有返回无参；既带in又带out，有返回有参；带inout，有返回有参
注意：in、out、inout都可以在一个存储过程中带多个

（2）使用带in 的存储过程，语法：命名时 creat procedure 存储过程名(IN 你取得字段名 类型（长度））后跟begin 方法体（在这个里面需要用到into 取得的字段名 end结束符），调用使用：CALL 存储过程名(‘您想要查的数据’)结束符号 ；

（3）使用带OUT 的存储过程，语法：和in大致语法的一样，只是在 存储过程名(IN 你取得字段名 类型（长度），OUT 想要的返回值名（随便取）类型（长度）），在方法体的查询中要使用到into 想要的返回值名，然后调用的时候，可以设置用户变量名，也可以不设置然后call 存储过程名(‘传入参数名’,@随便取) 结束符号

（4）inout和in，out的一样

3.1.4 调用

call 存储过程名(实参列表)结束符号

3.1.5 删除

drop procedure 存储过程名（只能一个一个的删除）

3.1.6 查看

show create procedure 存储过程名;

3.2 函数

3.1.1 含义以及好处

含义：经过编译并存储在数据库中的一段sql语句的集合
好处：提高代码的复用性，简化操作，减少编译次数并且减少和数据库服务器的链接次数，提高效率

3.1.2 创建

（1）create function 函数名(参数列表) returns 返回类型 后和存储过程的一样

（2）注意：参数列表包含两部分（参数名 参数类型），函数体中肯定有return语句，没有会报错；如果return没有在最后面，不会报错，但不建议

3.1.3 调用

select 函数名(参数列表) 结束符号

3.1.4 查看

show create function 函数名;

3.1.5 删除

drop function 函数名；

3.3 区别

（1）存储过程可以有0个返回值，也可以有多个返回值，适合做批量的插入和更新；函数有且仅有一个返回值，适合做处理数据后返回一个的结果
（2）

（3）sql语句（DML或SELECT)中不可用调用存储过程，而函数可以。

四.流程控制语句

4.1 分支语句

if结构：IF（表达式1，表达式2，表达式3）：表达式1成立执行表达式2，不成立执行表达式三，
这个只是在平常语句中其实现多重分支语句(引用begin end)，if 条件 then;elseif 条件2 then 语句2;…end if;

case判断值使用,最后结尾要加一个case end，和平常case语句中不一样
特点：可嵌套在其他语句中使用，也可以单独使用；执行when的条件或者值，执行then后面的语句，如果不满足往下执行，如果都不满足，执行else，如果没有else,则返回null

4.2 循环语句

分类：while loop repeat

while语法（先判断再循环）: 标签名：【 while 循环条件 do 循环体 end while 标签】;
loop语法：标签 loop 循环体；end loop【标签】;(可以模拟简单的死循环 )
repeat语法（先执行后判断）：【标签名】repeat 循环体；until(结束循环的条件) end repeatp[标签]；

循环控制(跳出):iterate 类似于continue,结束本次循环；leave类似于break，直接跳出当前循环