MySQL学习_mysql 学习笔记-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_51970364/article/details/131131878

文章目录

数据库操作
表
函数
存储过程和存储函数
- 存储过程
- 存储函数
数据类型
变量
- 系统变量
- 用户变量
处理错误
- - 定义条件
  - 定义处理程序
流程控制与游标
- 游标
触发器
JDBC
架构
- MySQL的数据目录

SQL分类：

DDL：数据定义语言

Data Definition Language，负责数据结构定义与库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成。
DML：数据操作语言

Data Manipulation Language，负责对数据库对象运行数据访问工作的指令集，以 INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。
DQL：数据查询语言

Data Query Language，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有FROM，WHERE，GROUP BY，HAVING和ORDER BY。
DCL：数据控制语言

Data Control Language，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。DCL以控制的访问权限为主，GRANT为授权语句，对应的REVOKE是撤销授权语句。

注：

MySQL在Linux下数据库名、表名、列名、别名大小写规则如下：

数据库名、表名、表的别名、变量名是严格区分大小写的；
关键字、函数名称在 SQL 中不区分大小写；
列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的；

MySQL在Windows的环境下全部不区分大小写

数据库操作

创建与删除

#删除数据库指令	
DROP DATABASE 数据库名
DROP DATABASE IF EXISTS 数据库名 -- 推荐
    
#创建数据库指令	
CREATE DATABASE 数据库名
# 判断数据库是否已经存在，不存在则创建数据库（推荐）
CREATE DATABASE IF NOT EXISTS 数据库名;

#创建一个使用utf8字符集的数据库
CREATE DATABASE 数据库名 CHARACTER SET utf8
#创建一个使用utf8字符集，并带校对规则的数据库 

CREATE DATABASE 数据库名 CHARACTER SET utf8 COLLATE utf8_bin
#校对规则 utf8_bin区分大小写，默认utf8_general_ci 不区分大小写

注：DATABASE不能改名，一些可视化工具可以改名，是新建一个库，把所有表复制过去，再删旧库完成。

查看数据库

#查看当前数据库服务器中的所有数据库
SHOW DATABASES
#查看当前正在使用的数据库，使用的一个mysql的全局函数
SELECT DATABASE()

# 查看指定库下所有的表
SHOW TABLES FROM 数据库名

#查看前面创建的zzc_db01数据库的定义信息
SHOW CREATE DATABASE zzc_db01

# 切换数据库
USE 数据库名

修改数据库

ALTER DATABASE 数据库名 CHARACTER SET 字符集

使用反引号

#在创建数据库，表时，为了规避关键字，可以使用反引号解决,删除的时候也要带反引号
#平时用的时候，带反引号更安全
CREATE DATABASE `CREATE`

备份与恢复

#备份数据库（注意：在DOS执行; 这个备份的文件就是对应的sql语句）:
mysqldump -u 用户名 -p m -B 数据库1 数据库2 数据库n > 文件路径名.spl
#备份一个数据库中的若干表 (在DOS下执行)
mysqldump -u 用户名 -p 数据库 表1 表2 表n > 文件路径名.sql
    
#恢复数据库（注意：进入Mysql命令行再执行）：Source 文件路径名.sql
source 文件路径名.sql
#第二个恢复办法：将整个备份文件的内容放到查询编辑器中 执行一遍

表

表操作

DUAL表是一个虚拟的表，很特殊，是建立数据库的时候随数据字典创建而建立的，它是单行单列的一个表，一般称为“哑表、虚表等”，列名DUMMY，类型VARCHAR2(1)，值为’X’，

创建表

creat table 表名

CREATE TABLE [IF NOT EXISTS] 表名
(
	field1 datatype,
	field2 datatype
)CHARACTER SET 字符集 COLLATE 校对规则 ENGINE 存储引擎
#field : 指定列名	datatype : 指定列类型（字段类型）
#character set : 如果没有指定，则默认为所在数据库的字符集
#collate : 如果没有指定，则默认为所在数据库的校对规则
#engine : 引擎
#加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

# 将其他表的查询结果集作为新表
CREATE TABLE 表名 AS 查询语句

# 查看表的数据结构
SHOW CREATE TABLE 表名

#示例：
CREATE TABLE `t2`(
	id INT AUTO_INCREMENT COMMENT "zi'zhen", #自增
	`name` VARCHAR(255),	 #name和password是关键字，要用反引号包起来
	`password` VARCHAR(255),
	birthday DATE
    PRIMARY KEY (id)  #主键
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

创建表——方式2，基于现有的表创建
CREATE TABLE 表名 AS 其他表/结果集

#例子:
CREATE TABLE myemp
AS 
SELECT employee_id,name,salary FROM employees;
mysql8新特性：计算列

—— 列名 generated always 公式 virtual

可以在create table 和 alter teble中使用
CREATE TABLE tb1(
	a INT,
	b INT,
	c INT GENERATED ALWAYS AS (a + b) VIRTUAL	-- 字段c为计算列
);

删除表

drop table 表名

drop是删除整个表，包括表的结构，约束，索引等。并释放掉所占的空间。

DROP TABLE 表名

删除表的数据 : delete from 表名

delete不会减少表或索引占用的空间。

DELETE FROM t7 
	WHERE `day` = 1988		#删除哪一行
#如果没有where语句，则删除表中的所有记录，delete语句不会删除表本身
#只能删行，不能删列（删列操作时alter table..drop..）

TRUNCATE TABLE： truncate table语句删除表中所有的数据，重置表的自增值，会保留表结构及其约束，索引等。并使表和索引占用的空间恢复到初始大小。

添加数据

**insert into 表名 (可选列名) values (数据)，(数据) **

CREATE TABLE `t6`(	--创建表
	birthday DATE,
	job_time DATETIME,
	login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP		--自动更新时间戳为登陆时间
			     ON UPDATE CURRENT_TIMESTAMP);
			     
#添加数据：
INSERT INTO `t6`(birthday, job_time)
	VALUES('2020-11-11','2022-12-12 12:12:12'),(....),(...);	--往表中添加数据,可以一次添加多条数据，字符串类型和日期类应该用单引号包起来

注：如果是将查询结果插入表中，则不必写values：
INSERT INTO 表名 (可选列名) SELECT ...
#子查询中的列表应与INSERT句中的列名对应

查询数据

select , desc

#select 查询；* 表示所有字段，可改为具体的多个列名； FROM 从哪个表；WHERE 从哪个字段(按条件查询)； 
SELECT * FROM `t1` WHERE 列名 = ***
#查询整张表
SELECT * FROM `t1`

# distinct用于去重
SELECT DISTINCT 列名 FROM 表名 WHERE 条件 

#order by : 可以指定列名按值升序asc或降序desc排列
#asc：ascending order 升序; desc：descending order 降序
SELECT 列名，运算表达式 FROM 表m ORDER BY 列名/表达式/别名 asc|desc #没写，默认升序

#使用表达式对查询的列进行运算，可以用as为列名和表达式换上别名，as可以省略，直接后接别名
SELECT 列名 AS 列别名, 运算表达式 AS 表达式别名 FROM 表名

#查看表结构，可以看所有列
DESC 表名

where子语句中常用的

比较运算符： > < <= >= = <> 或 != (不等于，两种写法) between…and…（显示在某一区间的值） in(set)（显示在int列表中的值，如：in(10,20,30)） like ‘赵%’ not like ‘赵%’ （模糊查询：%匹配0到多个字符，_ 单个下划线匹配单个字符） is null（判断是否为空）
exists 指定一个子查询，检测行的存在。该子查询实际上并不返回任何数据，而是返回值True或False。
xx all ( … )： all前的xx数据要大于括号内的所有数据，相当于 xx > max ( … )

xx any ( … )： all前的xx数据只要大于括号内的任一数据，相当于 xx > min ( … )
逻辑运算符：and（多个条件同时成立） or（多个条件任一成立） not（条件需不成立）

exists 练习

有学生表student（学号，姓名），课程表course（课程号，课程名），成绩表sc（学号，课程号，成绩）

（1）查询选修了所有课程的学生姓名

#由于SQL中没有全称量词，所以一旦涉及到“全部”这个概念，就要用双重否定，这道题要考虑的是，对于 课程表里所有课程，查询这样的学生，没有一门课程是他不选修的。
# 所以，将每个学生选修的课程一一列出与课程表所有课程进行比较！首先找出课程表中，每个学生没有选修的课；然后再挑出没有选修课为空集的学生返回即可。
SELECT s.姓名 FROM student s WHERE NOT EXISTS
  (SELECT * FROM course c WHERE NOT EXISTS
   	(SELECT * FROM sc WHERE sc.学号 = s.学号 AND sc.课程号 = c.课程号));

（2）使用嵌套查询，查询选修了学生S2所有课程的学生姓名

# 首先要查找的学生选修课程包含了学生S2选修的全部课程。即要找到的:不存在哪一门课，学生S2选了，符合条件的学生没有选
# 所以，将每个学生选修的课程一一列出与学生S2选修课程进行比较！首先找出S2课程表中，每个学生没有选修的课；然后再挑出没有选修课为空集的学生返回即可。
SELECT s.SNAME FROM student s WHERE NOT EXISTS
  (SELECT * FROM sc x WHERE x.SNO = "S2" AND NOT EXISTS
  	  (SELECT * FROM sc y WHERE y.SNO = s.SNO AND x.CNO = y.CNO));

注意：列的别名不能在where中使用。

因为select语句的执行顺序：

1.from语句
2.where语句（结合条件）
3.start with语句
4.connect by语句
6.group byi语句
7.having语句
8.model语句
9.select语句
10.union、ninus、intersect等集合演算演算
11.order by语句

12.limit语句

#查询结构:
方式1：
SELECT ...,....,...
	FROM ...,...,....
	WHERE 多表的连接条件
	AND 不包含组函数的过滤条件
	GROUP BY ...,...
	HAVING 包含组函数的过滤条件
	ORDER BY ... ASC/DESC
	LIMIT ...,...
#方式2：
SELECT ...,....,...
	FROM ... 
	JOIN ... ON 多表的连接条件
	JOIN ... ON ...
	WHERE 不包含组函数的过滤条件
	AND/OR 不包含组函数的过滤条件
	GROUP BY ...,...
	HAVING 包含组函数的过滤条件
	ORDER BY ... ASC/DESC
	LIMIT ...,...
#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

分页查询

#select ... limit start, rows 
#表示从start+1行开始取，取出rows行，start从0开始计算
SELECT * FROM 表名
	ORDER BY 列
	LIMIT start, rows

select语句中如果包含了group by，having，order by，limit：则书写顺序为group by，having，order by，limit

SELECT column1, column2... FROM table 
	GROUP BY column
	HAVING condition
	ORDER BY column
	LIMIT start,rows;

多表查询

多表查询是基于两个或以上的表的查询，查询所规定的条件不能少于表的个数-1，否则会出现笛卡尔集

SELECT ename,sal,dname,emp.deptno
	FROM emp, dept	-- 查询两张表
	WHERE emp.deptno = dept.deptno AND emp.deptno = 10	-- 不同表的列有同一名称时，要说明所属：表名.列名

表连接的约束条件可以有三种方式：WHERE, ON, USING

自连接

自连接是指在同一张表的连接查询：将同一张表看作两张表

# 给同一张表起不同别名，以达到自连接的目的
SELECT worker.ename, boss.ename 
	FROM emp worker, emp boss  -- 同一张表起不同的别名
	WHERE worker.mgr = boss.empno
#也可使用 inner join
SELECT 字段列表
	FROM A表 INNER JOIN B表
	ON 关联条件
	WHERE 等其他子句;

外连接

使用多表查询时，会根据关联条件显示匹配记录，而那些没有匹配到的记录则不会显示，如果在多表查询中至少需要显示表的全部内容，就要用到外连接。

SELECT ... FROM 表1
	JOIN 表2 ON 表1 和 表2 的连接条件
	JOIN 表3 ON 表2 和 表3 的连接条件

左外连接：让左侧的表完全显示，即左表为主表；

右外连接：让右侧的表完全显示；即右表为主表；

#左外连接
SELECT ... 
	FROM 表1 LEFT JOIN 表2 
	ON 条件
#右外连接	
SELECT ... 
	FROM 表1 RIGHT JOIN 表2 
	ON 条件

或者使用符号 (+)

满外连接 FULL JOIN

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

各种关系的实现：

#中图：内连接 A∩B
SELECT employee_id,last_name,department_name
	FROM employees e JOIN departments d
	ON e.`department_id` = d.`department_id`;

#左上图：左外连接
SELECT employee_id,last_name,department_name
	FROM employees e LEFT JOIN departments d
	ON e.`department_id` = d.`department_id`;

#右上图：右外连接
SELECT employee_id,last_name,department_name
	FROM employees e RIGHT JOIN departments d
	ON e.`department_id` = d.`department_id`;

#左中图：A - A∩B
SELECT employee_id,last_name,department_name
	FROM employees e LEFT JOIN departments d
	ON e.`department_id` = d.`department_id`
	WHERE d.`department_id` IS NULL

#右中图：B-A∩B
SELECT employee_id,last_name,department_name
	FROM employees e RIGHT JOIN departments d
	ON e.`department_id` = d.`department_id`
	WHERE e.`department_id` IS NULL

#左下图：满外连接
# 左中图 + 右上图 A∪B
SELECT employee_id,last_name,department_name
	FROM employees e LEFT JOIN departments d
	ON e.`department_id` = d.`department_id`
	WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name
	FROM employees e RIGHT JOIN departments d
	ON e.`department_id` = d.`department_id`;

#右下图
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
	FROM employees e LEFT JOIN departments d
	ON e.`department_id` = d.`department_id`
	WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
	FROM employees e RIGHT JOIN departments d
	ON e.`department_id` = d.`department_id`
	WHERE e.`department_id` IS NULL

自然连接

natural join 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

#在SQL92中
SELECT employee_id,last_name,department_name
	FROM employees e JOIN departments d
	ON e.`department_id` = d.`department_id`
	AND e.`manager_id` = d.`manager_id`;
#使用自然连接:
SELECT employee_id,last_name,department_name
	FROM employees e NATURAL JOIN departments d;

USING

SQL99还支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。
SELECT employee_id,last_name,department_name
	FROM employees e JOIN departments d
	USING (department_id);
#等值于
SELECT employee_id,last_name,department_name
	FROM employees e ,departments d
	WHERE e.department_id = d.department_id;

子查询

子查询是指嵌入其他sql语句中的select语句，也叫嵌套查询

单行子查询：只返回一行数据的子查询语句

# 单行子查询：只返回一行数据的子查询语句
SELECT * 
	FROM emp
	WHERE deptno = (   -- 只返回一行数据
		SELECT deptno
			FROM emp
			WHERE ename = 'SMITH' )

# 多行子查询：返回多行数据的子查询语句
select ename, job, sal, deptno
	from emp
	where job in (	-- 返回了多行数据
		SELECT DISTINCT job
			FROM emp
			WHERE deptno = 10
	) and deptno != 10

还可以把子查询当作一张临时表：

select goods_id, ecs_goods.cat_id, goods_name, shop_price
	from (
		SELECT cat_id , MAX(shop_price) as max_price	-- 子查询结果被当作临时表
			FROM ecs_goods 
        	GROUP BY cat_id
	) temp , ecs_goods		-- 其他表
	where temp.cat_id = ecs_goods.cat_id and temp.max_price = ecs_goods.shop_priceQ

空值问题：

如果子查询的表中有NULL值，则使用NOT IN筛选子表时会出现问题：

SELECT last_name
	FROM employees
	WHERE employee_id NOT IN (
		SELECT manager_id
		FROM employees
        -- WHERE manager_id is not  NULL
		);
# 结果应该是排除子表筛出来的元素，但因为子表的结果集有NULL值，所以最后结果是什么都没筛出来。

合并查询

操作符 union，union all 可以取得两个结果集的并集；

其中 union all 不会去重， union 会去重

SELECT `name`, id FROM table01 WHERE id > 2500 
UNION ALL
SELECT `name`, id FROM table01 WHERE `name`='abab'

表复制

表的复制

#将表1的数据复制添加到表2，如果两表相同，则相当于自我复制
INSERT INTO 表2
	SELECT * FROM 表1
#只复制部分数据：
INSERT INTO 表2
	(列，列 ...)
	SELECT 列，列... FROM 表1

#删除表1中的重复记录
CREATE TABLE 表2 LIKE 表1	-- 创建临时表2，于表1有相同的结构
INSERT INTO 表2 		
	SELECT DISTINCT * FROM 表1; -- 将表1的数据复制到表2，同时去重(不重复添加)
DELETE FROM 表1;	-- 删除表1的全部数据
INSERT INTO 表1 	
	SELECT * FROM 表2; -- 将表2去重后的数据复制到表1
DROP TABLE 表2; -- 删除临时表

修改列属性

alter table 表名 add 内容 after 在哪之后/ modify 列名属性更改/ drop 列名 / character 字符集 / change 旧名新名属性更改； rename 表名

#添加列
ALTER TABLE `t6` 		--往表t6中添加
	ADD `name` VARCHAR(255) NOT NULL DEFAULT ''		--添加列名：name，类型：VARCHAR，不允许为NULL，默认为''
	AFTER birthday				--在列名birthday之后
#修改列（的属性）
ALTER TABLE `t6`
	MODIFY `name` VARCHAR(60) NOT NULL DEFAULT ''		--修改name
#删除列
ALTER TABLE `t6`
	DROP `name`		--删去name	
#修改表名
RENAME TABLE t6 TO t7			--表明t6改为t7
#修改表的字符集为utf8
ALTER TABLE t7 
	CHARACTER utf8	
#列名birthday修改为day
ALTER TABLE t7 
	CHANGE birthday `day` VARCHAR(32) NOT NULL DEFAULT ''

# 删除表 （不能回滚）
DROP TABLE 表名
#清空表   （truncate不能回滚，用delete语句删除数据就可以回滚）
TRUNCATE TABLE 表名

更新数据

update 表名 set 修改内容 where 位置

UPDATE t7 			
	SET salary = salary + 1000		#set后接要更新的数据，可以有多个，用逗号分隔
	WHERE `name` = 'aaa'		#where用于指定位置
#如果没有where语句,则更新所有的行(记录)

如果需要回滚数据，需要保证在DML前进行设置：set autocommit = false;

删除数据

delete from 表名 where …

DELETE FROM departments
	WHERE department_id = 60;
#如果没有where语句，则删除表中全部数据

约束

约束用于确保数据库的数据满足特定的商业规则；为保证数据完整性，一般从以下四个方面考虑限制：

实体完整性（Entity Integrity）

例如：同一个表中，不能存在两条完全相同无法区分的记录
域完整性（Domain Integrity）

例如：年龄范围0-120，性别范围“男/女”
引用完整性（Referential Integrity）

例如：员工所在部门，在部门表中要能找到这个部门
用户自定义完整性（User-defined Integrity）

例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

mysq的约束包括：

not null，非空约束，规定某字段不能为空
unique，唯一约束，规定某字段在表中唯一
primary key，主键约束（非空且唯一）
foreign key，外键约束
check，检查约束
default，默认值约束

查看某个表已有的约束：

SELECT * FROM information_schema.table_constraints
	WHERE table_name = "表名";
# information_schema 系统库
# table_constraints 专门存储各表的约束

注：MySQL不支持check约束，可以用，但没效果。

primary key (主键)

用于唯一的标识表行的数据，当定义主键约束后，该列不能重复；

primary key约束列的数据不能重复，且不能为null
一张表最多只能有一个主键，但可以是复合主键：比如有列：name和id，可以将两者用一个主键约束，单个name或id可以重复，但name+id的组合不能有重复；
主键的指定方式有两种：
- 在列定义后指定：列定义 primary key
- 在表定义最后写：primary key (列名)
主键名总是PRIMAR，对主键定义约束名没用；
使用desc 表名，可以看到primary key的情况；
开发中，每个表往往会设计一个主键，且建议不要修改主键字段的值，否则有破坏数据完整性的危险；

#字段后 定义主键
CREATE TABLE t1(
    id INT PRIMARY KEY,
    `name` VARCHAR(32)
);
#表定义末尾 定义主键    
CREATE TABLE t1(
    id INT,
    `name` VARCHAR(32),
    PRIMARY KEY(id,`name`)
);    
# 建表后
ALTER TABLE 表名称 ADD PRIMARY KEY (字段列表);

删除主键约束：

删除主键时，不需指定主键名，因为一个表只有一个主键；删除主键约束后，非空约束会存在。

alter table 表名称 drop primary key；

自增列 auto_increment

一个表最多只能有一个自增列；
自增列约束的必须是键列（主键列、唯一键列，即 primary key 或 unique ）；
自增列约束的列的数据类型必须是整数类型；
默认从1开始，也可用：alter table 表名auto_increment = 新的开始值；

如果自增列指定了 0 和 null，会在当前基础上继续自增，如果指定了具体值，直接赋值为具体值。

# 设置自增列
create table 表名称(
	字段名 数据类型 primary key auto_increment,
);

create table 表名称(
	字段名 数据类型 unique key auto_increment,
);

alter table 表名称 modify 字段名 数据类型 auto_increment;

# 删除自增列
alter table 表名称 modify 字段名 数据类型; #修改有自增列的字段，不加auto_increment就相当于删除

not null (非空)

在列上定义了not null，那么当插入数据时，必须为列提供数据

字段名 字段类型 NOT NULL

# 建表时
CREATE TABLE emp(
	id INT(10) NOT NULL,
	NAME VARCHAR(20) NOT NULL,
	sex CHAR NULL
);
# 建表后 
ALTER TABLE emp
	MODIFY sex VARCHAR(30) NOT NULL;

# 删除非空约束
# 去掉not null，相当于修改某个非注解字段，该字段允许为空
alter table 表名称 modify 字段名 数据类型 NULL;	
alter table 表名称 modify 字段名 数据类型;

unique (唯一)

当定义了唯一约束后，该列的数据不能重复。

字段名 字段类型 UNIQUE

没有指定not null时，unique字段允许有多个null；
如果一个列，约束了 unique not null ，则使用效果类似主键 primary key；
MySQL会给唯一约束的列上默认创建一个唯一索引。

# 建表时
create table 表名称(
	字段名 数据类型,
	字段名 数据类型 unique,
	字段名 数据类型 unique key,
);
create table 表名称(
	字段名 数据类型,
	字段名 数据类型,
	字段名 数据类型,
	[constraint 约束名] unique(字段名)
);
#建表后
alter table 表名称 add unique (字段列表);
alter table 表名称 modify 字段名 字段类型 unique；

# 复合唯一约束
create table 表名(
	字段名 数据类型,
	字段名 数据类型,
	字段名 数据类型,
	unique key(字段列表) #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
);

删除唯一约束：

删除时需要指定唯一索引名（唯一索引名和唯一约束名一样，如果是复合约束且没有指定约束名，则默认为组合里的第一列的名）

ALTER TABLE 表名 DROP INDEX 唯一索引名

foreign key (外键)

用于定义主表和从表之间的关系：’

外键约束主要定义在从表上，主表则必须具有主键约束或unique约束，当定义外键约束后，要求外键列的数据必须在主表的主键列存在或为null；

#foreign key(...) references 主表(...)
FOREIGN KEY(本表列名) REFERENCES 主表名(主键名/unique字段名)

-- 主表
CREATE TABLE class (
	id INT PRIMARY KEY , -- 班级编号
);
-- 从表
CREATE TABLE stuent (
	id INT PRIMARY KEY , 
    class_id INT , -- 学生所在班级的编号
	FOREIGN KEY (class_id) REFERENCES class(id)	-- 指定外键关系
) 

# 建表后
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];

表的类型需要是innodb，这样的表才支持外键；一个表可以有多个外键；
外键列的类型要和主键列的类型一致(长度可以不同)；
一旦建立主外键关系，主键列数据就不能随意删除，需要先删除依赖该记录的数据，即对应外键列的所有同一数据，才能删除主键的这一数据。
当创建外键约束时，系统会默认在所在的列上建立对应的普通索引，索引名是外键的约束名（根据外键查询效率很高）；删除外键约束后，必须手动删除对应的索引。

约束等级：

Cascade方式：在父表上update/delete记录时，同步update/delete子表的匹配记录
Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
Restrict方式：同no action，都是立即检查外键约束
Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别。

对于外键约束，最好是采用: on update cascade on delete restrict 的方式。

create table dept(
	did int primary key, #部门编号
	dname varchar(50) #部门名称
);
create table emp(
	eid int primary key, #员工编号
	ename varchar(5), #员工姓名
	deptid int, #员工所在的部门
	foreign key (deptid) references dept(did) on update cascade on delete restrict	#把修改操作设置为级联修改等级，把删除操作设置为restrict等级
);

删除外键约束：

查看约束名——删除外键约束——查看索引名——删除外键索引

# 第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称'; #查看某个表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
# 第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称;  #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;

外键约束是用于保证数据的参照完整性的，因此主表与从表的存储引擎需要一致。

建不建外键约束和查询有没有关系。

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。

所以，MySQL 允许不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。

阿里开发规范：不得使用外键与级联，一切外键概念必须在应用层解决。

级联更新：主键更新，外键跟着更新；

外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

check

用于强制数据必须满足的条件

（oracle和sql server支持check，mysql8.0才支持check）

列名 类型 CHECK(check条件)

CREATE TABLE temp(
	id INT AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT CHECK(age > 20),
	PRIMARY KEY(id)
);

default

# 默认值一般不加在唯一键和主键列上
create table 表名称(
	字段名 数据类型 not null default 默认值,
);
alter table 表名称 modify 字段名 数据类型 default 默认值; #如果字段原来有非空约束，并且要保留，需要在此句加上not null，否则非空约束会被删除

建表时，通常会加 not null default ‘’ 或 default 0，因为：

null是特殊的值，比较时只能用专门的 is null 和 is not null 来比较。碰到运算符，通常返回null。
效率不高，影响提高索引效果。

索引

索引可以极大加快查询的速度；

使用索引会为该列形成一个索引的数据结构，如索引二叉树；
代价：额外占用磁盘空间；对dml语句(update, delete, insert) 的效率有影响，因为数据变动时需要重新维护索引结构
一般使用中，查询操作比增删改操作要多很多，因此索引很有必要

更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

索引的类型：

主键索引：主键就是一个索引
唯一索引（unique）
普通索引（index）
全文索引：不适用mysql自带的fulltext，而是使用全文搜索Solr和ElasticSearch（ES）

#建表后 创建索引
CREATE suo'yin 索引名 ON 表名 (列名 [长度] [ASC|DESC])

#建表时 创建索引
INDEX [索引名] [索引类型] (列名,…)
UNIQUE [ INDEX ] [索引名] [索引类型] (列名,…)
FOREIGN KEY 索引名 列名

#建表时
DROP TABLE IF EXISTS `order_table`;
CREATE TABLE `order_table`  (
  `id` int(30) NOT NULL AUTO_INCREMENT,
  `上架单` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `型号代码` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,	#主键索引的数据结构用B树
  INDEX `idx_orderno`(`上架单`,`型号代码`) USING BTREE,   #使用B树作为索引的数据结构
) 

#建表后
#为列创建索引：
-- create index .. on ..
CREATE UNIQUE INDEX 索引名 ON 表(列) -- 唯一索引
CREATE INDEX 索引名 ON 表(列) -- 普通索引
-- alter table .. add index ..
ALTER TABLE 表 ADD INDEX 索引名(列)

#删除索引
DROP INDEX 索引名 ON 表
#删除主键索引
ALTER TABLE 表 DROP PRIMARY KEY

#修改索引：先删除，再修改

#查询索引
SHOW INDEX FROM 表
SHOW INDEXES FROM 表
SHOW KEYS FROM 表
DESC 表

索引使用：

频繁的作为查询条件的字段应创建索引；

唯一性太差的字段不适合单独创建索引，即使频繁查询；更新很频繁的字段也不适合创建索引（因为维护索引结构需要额外开销）；不会或极少作为查询条件出现的字段也不用创建索引

事务

事务用于保证数据的一致性，事务期间的操作要么全部成，要么全部失败。

当执行事务操作时（dml语句），mysql会在表上加锁，防止其他用户修改表；

mysql的事务机制需要设置innodb的存储引擎才能使用

事务ACID特性：

原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生；
一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态
隔离性（Isolation）：事务的隔离性使多个用户并发访问数据库时，数据库未每个用户开启的事务不能被其他事务的操作所干扰，多个并发事务之间要相互隔离；
持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变是永久性的，即使数据库发生故障也不应该对其有影响

事务操作

start transaction ：开启一个事务（或者：set autocommit = off ，即关闭自动提交）
savepoint 保存点名：设置保存点
rollback to 保存点名：回退到某个保存点，回退后，会删除此保存点以及在其之后设置的保存点
rollback ：回退全部事务，即回到事务开始的时候
commit：提交事务，所有操作生效，不能再回退（确认事务的变化—结束事务—删除保存点—释放锁—数据生效）

DDL和DML的说明：

DDL的操作一旦执行，就不可回滚；
DML的操作默认情况也不可回滚；想要回滚，需要在操作前设置：SET autocommit = FALSE；

事务隔离级别

隔离级别	脏读	不可重复读	幻读	加锁读
读未提交(Read uncommitted)	Y	Y	Y	不加锁
读已提交(Read committed)	N	Y	Y	不加锁
可重复读(Repeatable read)	N	N	Y	不加锁
可串行化(Serializable)	N	N	N	加锁

脏读：当一个事务读取另一个事务还未提交的改变时，产生脏读

不可重复读：由于其他事务所提交的修改或删除操作，致使在同一事务中的同一查询返回不同结果，即发生不可重复读

幻读：由于其他事务所提交的插入操作，致使在同一事务中的同一查询返回不同结果，即发生幻读

事务隔离级别操作：

设置当前会话隔离级别：

set session transaction isolation level repeattable read;

设置系统当前隔离级别：

set global transaction isolation level repeattable read;
查看当前会话隔离级别：

select @@tx_isolation;

查看系统当前隔离级别：

select @@global.tx_isolation;
mysql默认的事务隔离级别是repeatable read，一般都用，无需修改；
全局修改：直接修改my.ini配置文件，在末尾加上：

#可选参数有：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

[mysqld]

transaction-isolation = REPEATABLE-READ

表类型/存储引擎

mysql的表类型由存储引擎（Storage Engines）决定，

MySQL数据表主要支持六种类型：CSV，Memory，ARCHIVE，MRG_MYISAM，MYISAM，InnoDB；

其中InnoDB为事务安全型，其他五种为非事务安全型；

MYISAM：不支持事务，不支持外键，但访问速度快

InnoDB：支持事务安全，但读写的处理效率相比MYISAM差一些，且会占用更多的磁盘空间以保留数据和索引；

MEMORY：使用存在内存中的内容来创建表，每个MEMORY表实际对应一个磁盘文件。MEMORY类型的表访问非常快，因为它的数据放在内存中，且默认使用hash索引；但如果MySQL服务关闭，表中的数据就会丢失，而表的结构还在

修改存储引擎：

alter table 表名 engine = 存储引擎

视图

视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包括列，其数据来自对应的基表（可以是多个基表）。通过视图可以修改基表的数据，基表的改变也会影响视图的数据。（视图中可以再使用视图，数据仍来自基表）

操作：

#创建：
creat view 视图名 as select 语句

#修改：
alter view 视图名 as select

#显示创建视图的指令：
show create view 视图名

#删除视图：
drop view 视图名1,视图名2

视图的好处：

安全：可以只让用户查询到需要的字段，一些保密的字段不能查看。
性能：视图存储的是查询语句，本身并不占用数据存储资源，减少了数据冗余。
灵活：可以在旧表上建立一张视图，然后映射到新表上，达到升级数据表的目的。

视图的不足：

如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂，可读性不好，容易变成系统的潜在隐患。

实际项目中，如果视图过多，会导致数据库维护成本的问题。

#查看视图
# 查看数据库的表对象、视图对象
SHOW TABLES;

# 查看视图的结构
DESC 视图名;

# 查看视图的属性信息
SHOW TABLE STATUS LIKE '视图名';

# 查看视图的详细定义信息
SHOW CREATE VIEW 视图名;

# 更新视图，跟更新表一样
update 视图名 set 更新内容 where ...

# 删除数据，跟删除表数据一样
delete from 视图名 where ...

# 修改视图
create or replace view 视图名 as 查询语句
alter view 视图名称 as 查询语句
# 删除视图
drop view if exists 视图1，视图2

视图作为虚拟表，主要用于方便查询，不建议更新视图数据。
对视图数据的更改，都是通过对实际数据表的更新来完成。

一些不可更新的视图：

要使视图可更新，视图中的行和底层基本表中的行之间必须存在一对一的关系。

另外当视图定义出现如下情况时，视图不支持更新操作：

在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
在定义视图的SELECT语句中使用了 JOIN联合查询，视图将不支持INSERT和DELETE操作；
在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；
在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、聚合函数、 GROUP BY 、 HAVING 、 UNION 等，视图将不支持INSERT、UPDATE、DELETE；
在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持 INSERT、UPDATE、DELETE；
视图定义基于一个不可更新视图；
常量视图。

用户管理

mysql的用户，都存储在系统数据库mysql中的user表中;

user表的重要字段说明：

host：允许登录的“位置”/IP，可以指定ip地址登录；其中localhost表示该用户只允许本机登录；
user：用户名；
authentication_string：密码，用mysql的password()函数加密过的密码字段

操作：

#创建用户，并指定密码：
create user '用户名' @'允许登录位置' identified by '密码'
-- 指定host时，可以写 % ，表示所有IP都有连接权限;IP也可以写成 192.168.1.% , 表示某用户的ip为 192.168.1.% z

#删除用户：
drop user '用户名' @'允许登录位置';

#修改密码：
-- 修改自己的密码：
set password = password('密码');
-- 修改他人的密码(需要有修改用户密码的权限):
set password for '用户名' @'登录位置' = password('密码');

用户权限

# 给用户权限：
grant 权限列表 on 库.对象名 to '用户名'@'登录位置' [identified by '密码']
-- 说明：权限列表写all表示赋予该用户在该对象上的所有权限
-- 库.对象名 写成 *.* : 代表系统中的所有数据库的所有对象(表，视图，存储过程); 写成 库.* : 代表某个数据库的所有数据对象(表，视图，存储过程);
-- identified by 可以省略，1.如果用户存在，就是修改该用户的密码; 2.如果该用户不存在，就是创建该用户;

#回收用户权限：
revoke 权限列表 on 库.对象名 from '用户名'@'登录位置';

#权限生效指令(如果权限没有生效，可以执行此命令)
flush privileges

授予权限时，如果 [库.对象名] 写 *.*，则代表数据库的所有对象，可以在mysql.user中查到；写 具体库名.* ，则代表某数据库下所有表，可以在mysql.db中查到；如果写 具体库名.具体表名，可以在 mysql.tables_priv 中查到。

函数

不同DBMS采用的函数有所不同，因此使用了SQL函数的代码，往往可移植性较差。

统计函数

count, sum, avg, max, min

#count(*)会返回满足条件的记录的个数，count(列名)同样返回满足条件的列的个数，但会排除为null的情况
SELECT COUNT(*) FROM 表名 WHERE 条件

#sum(列)返回满足条件的列的所有数值的总和(sum仅对数值起作用，对字符等没有意义; 对多列求和，逗号不能少)
SELECT SUM(列名)，SUM(列名)...FROM 表明；

#avg返回满足条件的列的平均值
SELECT AVG(列名) FROM 表名

#max/min 返回满足列的最高值/最低值
SELECT MAX(列名) FROM 表名
SELECT MIN(列名) FROM 表名

where 不能使用统计函数。

分组

group by … having … （having可对分组后结果进行过滤）

 SELECT 列, 列(可以是函数), 分组的列名 FROM 表名 
 	GROUP BY 分组的列名 HAVING 过滤条件

对比where和having：

	优点	缺点
WHERE	先筛选数据再关联，执行效率高	不能使用分组中的计算函数进行筛选
HAVING	可以使用分组中的计算函数	在最后的结果集中进行筛选，执行效率较低

字符串函数

#charset(str) 返回字串的字符集
SELECT CHARSET(列名) FROM 表名

#concat(string1,string2...) 连接字符，可将多个列拼接成一列
SELECT CONCAT(列名,'string',列名) FROM 表名

#instr(stirng, substring) 返回substring在string中出现的位置，没有则放回0
SELECT INSTR('STRING','RING') FROM DUAL  #dual 亚元表，是系统表，可用于测试表使用

#ucase(stirng) 转换成大写
#lcase(stirng) 转换成小写
SELECT UCASE(列名) FROM 表名

#left(string, length) 从string中的左边起取length个字符
SELECT LEFT(列名，2) FROM 表名

#length(string) 返回string长度(按字节算)

#replace(str, search_str, replace_str) 在str中用replace_str替换search_str
SELECT 列名1， REPLACE(列名2，'search_str','replace_str') FROM 表名 
/*搜索列名1，如果每行的列名2中有search_str，就用replace_str替换*/

#strcmp(srting1, string2) 逐字符比较两字符大小

#substring(str, position, length) 从str的position开始(从1开始计算)，取length个字符

#ltrim(string) / rtrim(string) 取出前端空格/后端空格

数学函数

-- abs(num) 绝对值
SELECT ABS(-10) FROM DUAL; 

-- bin(decimal_number )十进制转二进制
SELECT BIN(10) FROM DUAL; 

-- hex(DecimalNumber ) 转十六进制
-- oct(DecimalNumber ) 转八进制

-- ceiling(number2 ) 向上取整, 得到比 num2 大的最小整数
SELECT CEILING(-1.1) FROM DUAL;

-- floor(number2 ) 向下取整,得到比 num2 小的最大数
SELECT FLOOR(-1.1) FROM DUAL;

-- conv(number2,from_base,to_base) 进制转换
-- 下面的含义是: 8 是十进制的, 转成 二进制输出
SELECT CONV(8, 10, 2) FROM DUAL;
-- 下面的含义是: 8 是 16 进制的, 转成 十进制输出
SELECT CONV(16, 16, 10) FROM DUAL;

-- format(number,decimal_places ) 保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL; 

-- least(number , number2 [,..]) 求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL;

-- mod(numerator ,denominator)  求余
SELECT MOD(10, 3) FROM DUAL;

-- rand([seed]) RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
-- 1. 如果使用 rand(), 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果seed不变，该随机数也不变(固定了)
SELECT RAND() FROM DUAL;

日期函数

-- CURRENT_DATE ( ) 当前日期
SELECT CURRENT_DATE() FROM DUAL; 

-- CURRENT_TIME ( )当前时间
SELECT CURRENT_TIME() FROM DUAL;

-- CURRENT_TIMESTAMP ( ) 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;

#NOW() 返回当前时间
SELECT NOW() FROM DUAL; 

#date_add(data,interval d_value d_type) 在date中加上日期或时间
#interval 后可以是 year，minute，second，day等
SELECT * FROM mes WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW()
#date_sub(data,interval d_value d_type) 在date中减去日期或时间
#interval 后可以是 year，minute，second，hour，day等
SELECT * FROM mes WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)

#datediff(date1, date2) 两个日期差(天)
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL; 

-- YEAR|Month|DAY| DATE (datetime )
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2013-11-10') FROM DUAL; 

-- unix_timestamp() : 返回的是 1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL; 

-- 格式化日期
DATE_FORMAT(date,fmt) -- 按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt) -- 按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type) -- 返回日期字符串的显示格式
STR_TO_DATE(str, fmt) -- 按照字符串fmt对str进行解析，解析为一个日期

常用格式符:

常用取值：

例子：

-- 创建测试表 信息表
CREATE TABLE mes(
    id INT ,
    content VARCHAR(30), 
    send_time DATETIME
);
-- 添加一条记录
INSERT INTO mes VALUES(1, '北京新闻', CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2, '上海新闻', NOW());
INSERT INTO mes VALUES(3, '广州新闻', NOW());
SELECT * FROM mes;

-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间. 
SELECT id, content, DATE(send_time) FROM mes; 
-- 查询在 10 分钟内发布的新闻
#date_add(data,interval d_value d_type) 在date中加上日期或时间
SELECT * FROM mes WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW()
#date_sub(data,interval d_value d_type) 在date中减去日期或时间
SELECT * FROM mes WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)

-- 求出 2011-11-11 和 1990-1-1 相差多少天
#datediff(date1, date2) 两个日期差(天)
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL; 
-- 求出你活了多少天? (假如1986-11-11 出生)
SELECT DATEDIFF(NOW(), '1986-11-11') FROM DUAL;

-- 如果你能活 80 岁，求出你还能活多少天. (1986-11-11 出生)
-- 先求出活 80 岁 时, 是什么日期 X
-- 然后在使用 datediff(x, now()); 1986-11-11->datetime
-- INTERVAL 80 YEAR ： YEAR 可以是 年月日，时分秒
-- '1986-11-11' 可以 date,datetime timestamp
SELECT DATEDIFF(DATE_ADD('1986-11-11', INTERVAL 80 YEAR), NOW()) FROM DUAL;
SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL; 

-- FROM_UNIXTIME() : 可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过 FROM_UNIXTIME 转换
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;

加密函数

#user() 查询用户，可以查看登录到mysql的有哪些用户，以及登录的IP
SELECT USER() FROM DUAL;
#database()查询当前使用的数据库名称
SELECT DATABASE();

# MD5(str) 为字符串算出一个MD5(32位)字符串，常用于(用户密码)加密
# 在数据库中存放的应该是加密后的密码
SELECT MD5('zzc') FROM DUAL;

# SHA(STR),根据密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL;
# SHA加密算法比MD5更加安全 。
SELECT SHA('zzc') FROM DUAL;

# password(str)根据密码str计算并返回密码字符串，常用于数据库的用户密码加密
SELECT PASSWORD('zzc') FROM DUAL

# encode(v,p):返回使用password_seed作为加密密码 加密value
ENCODE(value,password_seed) 
#返回使用password_seed作为加密密码 解密value
DECODE(value,password_seed) 返回使用password_seed作为加密密码解密value

流程函数

# IF(expr1,expr2,expr3) 如果 expr1 为 True ,则返回 expr2 否则返回 expr3
SELECT IF(TRUE, '北京', '上海') FROM DUAL;

# IFNULL(expr1,expr2) 如果expr1不为空,返回expr1, 否则返回expr2
SELECT IFNULL( NULL, 'mysql学习') FROM DUAL;

# case when 条件1 then 结果1 when 条件2 then 结果2 else 结果3 end; [类似多重分支.]
# 类似: if...else if...else..
SELECT CASE
	WHEN job = 'CLERK' THEN '职员'
   	WHEN job = 'MANAGER' THEN '经理' 		
    WHEN job = 'SALESMAN' THEN '销售人员' 	  
    ELSE job END
	
# case expr when 常量1 then 值1 when 常量2 then 值2 else 值3 end; 
# 类似: switch...case...
SELECT CASE x
	WHEN 1 THEN '我是1'
	WHEN 2 THEN '我是2'
	ELSE '你是谁'

其他函数

FORMAT(value,n) -- 返回对数字value进行格式化后的结果数据。n表示 四舍五入 后保留到小数点后n位
CONV(value,from,to) -- 将value的值进行不同进制之间的转换
INET_ATON(ipvalue) -- inet_aton(i): 将以点分隔的IP地址转化为一个数字
INET_NTOA(value) -- inet_ntoa(v): 将数字形式的IP地址转化为以点分隔的IP地址
BENCHMARK(n,expr) -- benchmark(n,e): 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间
CONVERT(value USING char_code) -- 将value所使用的字符编码修改为char_code

存储过程和存储函数

存储过程

存储过程就是将一组经过预先编译的SQL语句封装。

执行流程：存储过程预先存储在mysql服务器上，用户需要的时候，只需要向服务器发出调用存储过程的命令，就像调用一个封装的方法一样，之后服务器就可以把预先存储好的这一系列SQL语句全部执行。

好处：

简化操作，提高sql语言的重用性；
减少失误；
减少网络传输量，用户只需使用存储过程的名字即可；
减少sql语句暴露在网络上的风险，也提高数据查询的安全性；

不足：

可移植性差：

存储过程程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。
调试困难：

只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。
版本管理困难：

比如数据表索引发生变化了，可能会导致存储过程失效。在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
不适合高并发：

高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

分类：

存储过程的参数有：IN，OUT，INOUT

没有参数（无参数无返回）
仅带IN（有参数无返回）
仅带OUT（无参数有返回）
带IN，OUT（有参数有返回）
带INOUT（有参数有返回）

注：IN、OUT、INOUT 都可以在一个存储过程中带多个。

# 创建 
create procedure 存储过程名 (IN|OUT|INOUT 参数名 参数类型, ....) 
[charcateristics...]
begin 
	存储过程体
end

# 调用
call 存储过程名(实参列表)
# 调用in模式的参数
CALL sp1('值');
# 调用out模式的参数
SET @name;
CALL SPQ(@name);
SELECT @name;
#调用inout模式的参数
SET @name=值;
CALL sp1(@name);
SELECT @name;

因为mysql默认的语句结束符号为分号 ‘；‘，为了避免与存储过程的sql语句冲突，需要使用delimiter改变存储过程的结束符；

如果使用的Navicat工具，那么在编写存储过程时，Navicat会自动设置 DELIMITER ，不需要自己再额外设置了。

DELIMITER //

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
	sql语句1;
	sql语句2;
END //

DELIMITER ;

IN：当前函数为输入参数；存储过程只是读取这个参数的值。没有定义参数种类时，默认为IN；

OUT：当前参数为输出参数；执行完毕后，执行调用的客户端可以读取这个这个参数返回的值。

INOUT：当前参数即可为输入参数，也可为输出参数；

characteristics：表示创建存储过程时指定的对存储过程的约束条件，取值如下：
language sql
| [not] deterministic
| {contains sql | no sql | reads sql data | modifies sql data}
| sql security { definer | invoker }
| comment 'string'
LANGUAGE SQL ：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。

[NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用SQL语句的限制。

CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
默认情况下，系统会指定为CONTAINS SQL。

SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。

DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
如果没有设置相关的值，则MySQL默认指定值为DEFINER。

COMMENT ‘string’ ：注释信息，可以用来描述存储过程。

存储函数

mysql允许自定义函数，调用方式与调用mysql的系统函数一样。

语法格式：

create function 函数名(参数名 参数类型, ...)
returns 返回值类型
[characteristics ...]
begin
	函数体
end
# returns对函数而言是强制的，函数体必须包含一个RETURN value语句；
# characteristic是创建函数时指定的对函数的约束。取值与创建存储过程时相同。

调用：与调用mysql内部函数是一样的

SELECT 函数名(实参列表)

例子：

创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型为整型。

DELIMITER //

CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
	LANGUAGE SQL
	NOT DETERMINISTIC
	READS SQL DATA
	SQL SECURITY DEFINER
	COMMENT '查询部门平均工资'
BEGIN
	RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END //

DELIMITER ;

# 调用
SET @dept_id = 50;
SELECT count_by_id(@dept_id);

	关键字	调用语法	返回值	应用场景
存储过程	procedure	call 存储过程()	0个或多个	一般用于更新
存储函数	function	select 函数()	只能一个	一般用于查询结果为一个值并返回时

查看存储过程和存储函数的

创建信息：

show create {procedure | function} 存储过程名或函数名

状态信息：

# 返回子程序的特征，如数据库，名字，类型，创建者，创建、修改日期
show {procedure | function} status [like 'pattern']
# [LIKE 'pattern'] 匹配存储过程或函数的名称，可省略。省略时，列出数据库中存在的所有存储过程和函数的信息。

存储信息：

MySQL中存储过程和函数的信息放在 infornation_schema 数据库下的 Routines 表中。

SELECT * FROM information_schema.Routines
	WHERE ROUTINE_NAME='存储过程或函数名' [AND ROUNTINE_TYPE = {'PROCEDURE | FUNCTION'}];
# 如果存储过程和函数有同名的情况，可以指定routine_type来指明查的是存储过程还是函数。

修改：

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic]

characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
CONTAINS SQL ，表示子程序包含SQL语句，但不包含读或写数据的语句。

NO SQL ，表示子程序中不包含SQL语句。

READS SQL DATA ，表示子程序中包含读数据的语句。

MODIFIES SQL DATA ，表示子程序中包含写数据的语句。

SQL SECURITY { DEFINER | INVOKER } ，指明谁有权限来执行。

DEFINER ，表示只有定义者自己才能够执行。
INVOKER ，表示调用者可以执行。

COMMENT ‘string’ ，表示注释信息。

删除：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

IF EXISTS：如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告。

数据类型

MySQL常用列类型(数据类型)

数值类型
- 整型：tinyint（1个字节）smallint（2个字节）mediumint（3个字节）int（4个字节）bigint（8个字节）decimal[M, 0]（数值长度为M）
- 小数类型：float（单精度4字节）double（双精度8字节）decimal[M,D]（数值长度M，小数部分长度D；M最大65，D最大30；M被省略则默认为10，D被省略则默认为0)
文本类型（字符串类型）
- char （0~255字符，固定长）
- varchar （0~65535 即最大放0~2^16 - 1字节，可变长，实际占用空间看存放多少数据，varchar本身需要占用1~3个字节来记录存放内容的长度）
  - 查询速度：char > varchar
  - varchar 存放大小的单位写的仍是字符，但存放上下限是按字节算的
- text （0~ 2^16 - 1）
- longtext （0~2^32 - 1）
二进制数据类型
- blob （0~2^16 - 1）
- longblob （0~2^32 - 1）
日期类型
- date （日期年月日 3字节）
- time （时间时分秒 3字节）
- datetime （年月日时分秒 YYYY-MM-DD HH:mm:ss 8字节）
- timestamp （时间戳，会自动更新 4字节）
  - 自动更新时间戳为当前时间：login_time（列名） TIMESTAMP（数据类型：时间戳） NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
- year (年 1字节)

常用的类型介绍：

MySQL在执行建表语句时，将id字段的类型默认设置为int(11)，这里的11是指int类型的显示宽度，默认的显示宽度为11。

使用建议：

任何字段如果为非负数，应该加unsigned
整数：int
小数：decimal，如果存储的数据范围超过，建议将数据拆成整数和小数并分开存储。
日期时间：datetime
字符串：
- 如果存储的字符串长度几乎相等，使用char定长字符串类型；
- varchar不预先分配存储空间，长度不要超过5000，如果超过，应该定义为text，并独立出来一张表，用主键来对应，避免影响其他字段索引效率。

变量

系统变量

变量由系统定义，属于服务器层面。启动MySQL服务，生成MySQL服务实例期间， MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。

系统变量的值大都是编译MySQL时参数的默认值，或配置文件(如 .ini)中的参数值。

global 全局系统变量，也简称全局变量
session 会话系统变量，也可称 local变量，

如果没有加global关键字，则默认为会话级别。

静态变量属于特殊的全局系统变量。

每个客户机功连接MySQL服务器后，都会产生与之对应的会话。

会话期间，MySQL服务实例会在MySQL服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的复制。

全局系统变量针对于所有会话（连接）有效，但不能跨重启 (即重启服务);
修改会话变量仅影响当前的会话/连接，不会影响其他会话的同一会话系统变量的值。
修改全局系统变量的值会导致同一会话系统的其他会话的全局系统变量改变。

查看系统变量：

# 查看所有全局变量
SHOW GLOBAL VARIABLES;
# 查看所有会话变量
SHOW SESSION VARIABLES;
SHOW VARIABLES;

# 查看满足条件的系统/会话变量
SHOW GLOBAL VARIABLES LIKE '%标识符%';
SHOW SESSION VARIABLES LIKE '%标识符%'
# 例如:
SHOW GLOBAL VARIABLES LIKE 'ADMIN_%';

# 查看指定的系统变量的值
SELECT @@global.变量名;

# 查看指定的会话变量的值
SELECT @@session.变量名;
SELECT @@变量名;

修改系统变量的值：

方法1：修改MySQL配置文件，继而修改MySQL系统变量的值（需要重启MySQL服务）

方法2：使用 set命令重新设置系统变量的值

#为某个系统变量赋值
SET @@global.变量名=变量值;
SET GLOBAL 变量名=变量值;

#为某个会话变量赋值
SET @@session.变量名=变量值;
SET SESSION 变量名=变量值;

# 例如：
SELECT @@global.autocommit;
SET GLOBAL autocommit=0;

SET GLOBAL max_connections=1000;
SELECT @@global.max_connections;

MySQL8.0新特性：全局变量的持久化

使用 set global 语句设置的变量值只会临时生效，数据库重启后又变成默认值。

而使用 set persist 命令，会将该命令的配置保存到数据目录下的musql-auto.cnf文件中，下次数据库重启时会读取该文件，用其中的配置来覆盖默认的配置文件。

-- 例：设服务器最大连接数为1000
SET PERSIST global max_connections = 1000;

用户变量

mysql中的用户变量以一个 @ 开头，分为会话用户变量和局部变量。

会话用户变量：作用域和会话变量一样，只对当前连接会话有效。
局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在存储过程和函数中使用。

	作用域	定义位置	语法
会话用户变量	当前会话	会话的任何地方	加@符号，不用指定类型
局部变量	定义它的begin end中	begin end的第一句话	一般不加@，需指定类型

# 会话用户变量的定义
#方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;

#方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];

# 查看
SELECT @用户变量

# 例：
SET @a = 1;
SELECT @a;

SELECT @num := COUNT(*) FROM employee;
SELECT @num;

SELECT AVG(salary) INTO @avgSalary FROM employee;
SELECT @avgSalary;

SELECT @big;   -- 查看未声明变量时，得到NULL值

# 局部变量 的定义 declare只能放在begin...end中的第一句
BEGIN
	# 声明
	DECLARE 变量1 变量数据类型 [DEFAULT 变量默认值];  -- 默认初始值是NULL
	DECLARE 变量2,变量3,... 变量数据类型 [DEFAULT 变量默认值];
	
	# 赋值
	SET 变量名1 = 值;
	SET 变量名1 := 值;
	SELECT 值 INTO 变量名2 [FROM 子句];
	SELECT 字段名或表达式 INTO 变量名2 FROM 表;
	
	# 查看
	SELECT 变量1,变量2,变量3;
END

# 例：
DELIMITER //

CREATE PROCEDURE set_value()
BEGIN
	DECLARE emp_name VARCHAR(25);
	DECLARE sal DOUBLE(10,2);
	
	SELECT last_name,salary INTO emp_name,sal
		FROM employees
		WHERE employee_id = 102;
		
	SELECT emp_name,sal;
END //

DELIMITER ;

处理错误

定义条件：事先定义程序执行过程中可能遇到的问题；

处理程序：定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。——增强了处理问题的能力，避免程序异常停止运行。

定义条件和处理程序在存储过程、存储函数中都是支持的。

在存储过程中未定义条件和处理程序，且当存储过程中执行的SQL语句报错时，MySQL数据库会抛出错误，并退出当前SQL逻辑，不再向下继续执行。

定义条件

定义条件就是给MySQL中的错误码命名，它将一个错误名字和指定的错误条件关联起来。这个名字随后被用在定义处理程序的 declare handler 语句中。

语法格式：

declare 错误名称 condition for 错误码(或错误条件)

错误码的说明：

MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误。

MySQL_error_code是数值类型错误代码。
sqlstate_value是长度为5的字符串类型错误代码。

例如：

在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000’是sqlstate_value。

在ERROR 1142（42000）中，1142是MySQL_error_code，'42000’是sqlstate_value。

# 例: 给违反非空约束的错误类型“ERROR 1048(23000)”定义
#使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
#使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

定义处理程序

为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。

语法格式：

DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

处理方式有三种：continue，exit，undo

CONTINUE：遇到错误不处理，继续执行；
EXIT：遇到错误马上退出；
UNDO：遇到错误后撤回之前的操作，MySQL中暂时不支持。

错误类型：

SQLSTATE ‘字符串错误码’ ：表示长度为5的sqlstate_value类型的错误代码；
MySQL_error_code ：匹配数值类型错误代码；
错误名称：表示DECLARE … CONDITION定义的错误条件名称。
SQLWARNING ：匹配所有以01开头的SQLSTATE错误代码；
NOT FOUND ：匹配所有以02开头的SQLSTATE错误代码；
SQLEXCEPTION ：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；

处理语句：可以是“set 变量 = 值”这样简单的语句，也可以是使用“begin…end”的复合语句。

# 定义处理程序的几种方式举例：
#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';

#方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';

#方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';

#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';

#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';

#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

示例：

创建一个名称为“InsertDataWithCondition”的存储过程：在存储过程中，定义处理程序，捕获sqlstate_value值，当遇到sqlstate_value值为23000（重复条目错误）时，执行EXIT操作，并且将@proc_value的值设置为-1。

DELIMITER //

CREATE PROCEDURE InsertDataWithCondition()
BEGIN
	DECLARE duplicate_entry CONDITION FOR SQLSTATE '23000' ;
	DECLARE EXIT HANDLER FOR duplicate_entry SET @proc_value = -1;
	
	SET @x = 1;
	INSERT INTO departments(department_name) VALUES('测试');
	SET @x = 2;
	INSERT INTO departments(department_name) VALUES('测试');
	SET @x = 3;
END //

DELIMITER ;

流程控制与游标

流程控制：控制存储过程中sql语句的执行顺序。分别有三大类：顺序结构，分支结构，循环结构。

流程控制语句（只能用于存储程序）：

条件判断语句：if 和 case语句
循环语句：loop，while，repeat语句
跳转语句：iterate 和 leave语句

分支结构—— IF

# if
IF 表达式1 THEN 操作1;
[ELSEIF 表达式2 THEN 操作2;]....
[ELSE 操作n;]
END IF;

# 例：
IF val IS NULL
	THEN SELECT 'val is null';
ELSE SELECT 'val is not null';

END IF;

#
DELIMITER //

CREATE PROCEDURE update_salary_by_eid2(IN emp_id INT)
BEGIN
	DECLARE emp_salary DOUBLE;
	DECLARE hire_year DOUBLE;
	SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
	SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year FROM employees WHERE employee_id = emp_id;	-- DATEDIFF()判断日期差，curdate()获取当前日期
	IF emp_salary < 8000 AND hire_year > 5
		THEN UPDATE employees SET salary = salary + 500 WHERE employee_id =
emp_id;
	ELSE
		UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
	END IF;
END //

DELIMITER ;

分支结构——CASE

# case
# 1.类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1 -- 如果是语句，需要加分号
WHEN 值2 THEN 结果2或语句2 -- 如果是语句，需要加分号
...
ELSE 结果n或语句n -- 如果是语句，需要加分号
END [case] -- 如果是放在begin end中需要加上case，如果放在select后面不需要

# 2.类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1 -- 如果是语句，需要加分号
WHEN 条件2 THEN 结果2或语句2 -- 如果是语句，需要加分号
...
ELSE 结果n或语句n -- 如果是语句，需要加分号
END [case] -- 如果是放在begin end中需要加上case，如果放在select后面不需要

循环结构——LOOP

LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句）。

[loop_label:] LOOP
	循环执行的语句
END LOOP [loop_label]
-- loop_label表示LOOP语句的标注名称，参数可省略。

示例：

涨工资—— 声明存储过程 “update_salary_loop()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数。

DELIMITER //

CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
	DECLARE avg_salary DOUBLE;
	DECLARE loop_count INT DEFAULT 0;
	
	SELECT AVG(salary) INTO avg_salary FROM employees;
	
	label_loop:LOOP
		IF avg_salary >= 12000 THEN LEAVE label_loop;
		END IF;
		
		UPDATE employees SET salary = salary * 1.1;
		SET loop_count = loop_count + 1;
		SELECT AVG(salary) INTO avg_salary FROM employees;
	END LOOP label_loop;
	
	SET num = loop_count;
END //

DELIMITER ;

循环结构——WHILE

WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。

[while_label:] WHILE 循环条件 DO
	循环体
END WHILE [while_label];

循环结构——REPEAT

REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出。

[repeat_label:] REPEAT
	循环体
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]

三种循环的对比：

都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。

LOOP：一般用于实现简单的"死"循环

WHILE：先判断后执行

REPEAT：先执行后判断，无条件至少执行一次

跳转语句——LEAVE

LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。(类似于编程语言的break)

LEAVE 标记名

跳转语句——ITERATE

iterate语句只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。（类似于编程语言的continue）

ITERATE label

游标

游标提供一种灵活的操作方式，能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。

在SQL中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。即充当了指针的作用。

游标让面向集合的SQL语言有了面向过程开发的能力。

一些性能问题：

游标可以在存储程序中使用，效率高，程序也更加简洁，比在应用层实现相同的功能要好。

但在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。

所以要在用完后及时关闭，这样才能提高系统整体效率。

使用步骤：

游标必须在声明处理程序之前被声明，且变量和条件必须在声明游标/处理程序之前被声明。

第一步：声明游标

# 适用于MySQL,SQL Server,DB2 和 MariaDB
DECLARE 游标名 CURSOR FOR 查询语句/结果集

# 适用于Oricle,PostgreSQL
DECLARE 有标明 CURSOR IS 查询语句/结果集

第二步：打开游标
```
OPEN 游标名
```
打开游标时， SELECT 语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备。
第三步：使用游标（从游标中取得数据）
```
FETCH 游标名 INTO 变量名[,变量名...]
```
将游标读取到的行数据保存到变量名中，然后指针指向下一行。

查询结果集的字段数，必须与into后的变量数一致。
第四步：关闭游标
```
CLOSE 游标名
```
游标会占用系统资源，不关闭的话，会保持到存储过程结束。

示例：

创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，DOUBLE类型；声明OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count。

DELIMITER //

CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT
total_count INT)

BEGIN
	DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资
	DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值
	DECLARE emp_count INT DEFAULT 0; #记录循环个数
	#定义游标
	DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
	#打开游标
	OPEN emp_cursor;
	
	REPEAT
		#使用游标（从游标中获取数据）
		FETCH emp_cursor INTO cursor_salary;
		SET sum_salary = sum_salary + cursor_salary;
		SET emp_count = emp_count + 1;
		UNTIL sum_salary >= limit_total_salary
	END REPEAT;
	
	SET total_count = emp_count;
	#关闭游标
	CLOSE emp_cursor;
END //

DELIMITER ;

触发器

触发器是由事件来触发某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。

如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。

当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

优点：

可以确保数据的完整性；—— 比如一张表的修改会影响其他表的数据，则使用触发器就可以同步多个表了。
可以记录操作日志；—— 利用触发器，可以具体记录什么时间发生了什么。
操作数据前对数据进行合法性检查；

缺点：

可读性差；—— 因为触发器存储在数据库中，且由事件驱动，这意味着触发器可以不受应用层的控制。使得触发器发生错误时可能难以发现。
相关数据的变更，可能导致触发器出错；—— 特别是表结构的变更，都可能导致触发器的出错。

创建：

CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句快;
 -- 表名：是触发器监控的对象;  
 -- before|after:触发的事件，before指在事件之前触发，after指在事件之后触发
 -- INSERT|UPDATE|DELETE ：表示触发的事件。
 	-- INSERT 表示插入记录时触发；
	-- UPDATE 表示更新记录时触发；
 	-- DELETE 表示删除记录时触发。 
 -- 触发器执行的语句块：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。

示例：

定义触发器“salary_check_trigger”，基于员表“employees”的INSERT事件，在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，则报sqlstate_value为’HY000’的错误，从而使得添加失败。

DELIMITER //

CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
	DECLARE mgrsalary DOUBLE;
	SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;
	
	IF NEW.salary > mgrsalary THEN
		SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
	END IF;
END //

DELIMITER ;

在触发器的执行语句块中，可以使用 NEW. 指代被监听的表发生添加或修改的那条记录；

要自定义错误，则使用以下格式：
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '...';

查看：

-- 语句结尾不加；加\G，数据显示较清晰直观，但SQLyog不识别
# 查看当前数据库的所有触发器的定义
SHOW TRIGGERS

# 查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名

# 从系统库information_schema的TRIGGERS表中查询所有触发器的信息
SELECT * FROM information_schema.TRIGGERS;

删除：

触发器也是数据库对象，所以也用DROP语句：

DROP TRIGGER IF EXISTS 触发器名称;

JDBC

JDBC是java提供的一套用于数据库操作的接口API，不同的数据库厂商会针对这套接口，提供不同实现。

JDBC 为访问不同的数据库提供了统一的接口，为使用者屏蔽了细节问题（JDBC统一和规范了应用程序和数据库的连接，执行SQL语句，并得到返回结果等操作）；

使用JDBC可以连接任何提供了JDBC驱动程序的数据库系。

JDBC程序编写步骤

注册驱动 – 加载Driver类
获取连接 – 得到Connection
执行增删改查 – 发送SQL给mysql执行
释放资源 – 关闭相关连接

	public static void main(String[] args) throws SQLException {
        // 前置工作: 在项目下创建一个文件夹比如 libs
        // 将 mysql.jar 拷贝到该目录下，并将其添加为库

        //1. 注册驱动
        Driver driver = new Driver();

        //2. 得到连接:
        // jdbc:mysql:// 规定好协议，通过jdbc方式连接mysql
        // localhost  指主机，可以是ip地址
        // 3306 表示mysql监听的端口
        // zzc_db02 连接到mysql的哪个数据库
        String url = "jdbc:mysql://localhost:3306/zzc_db02";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "zzc");
        Connection connect = driver.connect(url, properties);

        //3. 执行sql
        String sql = "delete from actor where id = 1";
        //statement 用于执行静态SQL语句并返回其生成的结果的对象
        Statement statement = connect.createStatement();
        int rows = statement.executeUpdate(sql);

        //4. 关闭连接资源
        statement.close();
        connect.close();
    }

获取数据库连接

获取数据库连接的5种方法，常用方法4，5：

    //方式 1
    public void connect01() throws SQLException {
        Driver driver = new Driver(); //创建 driver 对象

        String url = "jdbc:mysql://localhost:3306/hsp_db02";
        //将 用户名和密码放入到 Properties 对象
        Properties properties = new Properties();
        //说明 user 和 password 是规定好，后面的值根据实际情况写
        properties.setProperty("user", "root");// 用户
        properties.setProperty("password", "zzc"); //密码
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
    }

    //方式 2
    public void connect02() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        //使用反射加载 Driver 类 , 动态加载，更加的灵活，减少依赖性
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();

        String url = "jdbc:mysql://localhost:3306/hsp_db02";
        //将 用户名和密码放入到 Properties 对象
        Properties properties = new Properties();
        //说明 user 和 password 是规定好，后面的值根据实际情况写
        properties.setProperty("user", "root");// 用户
        properties.setProperty("password", "zzc"); //密码
        Connection connect = driver.connect(url, properties);
        System.out.println("方式 2=" + connect);
    }

    //方式 3 使用 DriverManager 替代 driver 进行统一管理
    public void connect03() throws IllegalAccessException, InstantiationException, ClassNotFoundException, SQLException {
        //使用反射加载 Driver
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();

        //创建 url 和 user 和 password
        String url = "jdbc:mysql://localhost:3306/hsp_db02";
        String user = "root";
        String password = "zzc";

        //注册 Driver 驱动
        DriverManager.registerDriver(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("第三种方式=" + connection);
    }

    //方式 4: 使用 Class.forName 自动完成注册驱动，简化代码
    //这种方式获取连接是使用的最多，推荐使用
    public void connect04() throws ClassNotFoundException, SQLException {
        // 使用反射加载了 Driver 类
        // 在加载 Driver 类时，完成注册
        /*
        源码:
        1. 静态代码块，在类加载时，会执行一次.
        2. DriverManager.registerDriver(new Driver());
        3. 因此注册 driver 的工作已经完成
            static {
                try {
                    DriverManager.registerDriver(new Driver());
                } catch (SQLException var1) {
                    throw new RuntimeException("Can't register driver!");
                }
            }
        */

        Class.forName("com.mysql.jdbc.Driver");
        //创建 url 和 user 和 password
        String url = "jdbc:mysql://localhost:3306/hsp_db02";
        String user = "root";
        String password = "zzc";
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("第 4 种方式~ " + connection);
    }

    //方式 5 , 在方式 4 的基础上改进，增加配置文件，让连接 mysql 更加灵活
    public void connect05() throws IOException, ClassNotFoundException, SQLException {
        //通过 Properties 对象获取配置文件的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        //获取相关的值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        Class.forName(driver);//建议写上

        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println("方式 5 " + connection);
    }

JDBC API

DriverManger

驱动管理类

getConnection(url, user, pwd) : 获取到连接

Connection 接口

createStatement() ：创建Statement对象

Statement

Statement对象用于执行静态SQL语句并返回其生成的结果的对象；

但使用Statement存在SQL注入的风险：SQL注入是利用利用某些系统没有对用户输入数据进行充分检查，而在输入数据中注入非法的SQL语句，恶意攻击数据库；

SELECT * FROM admin
	WHERE NAME = '1' OR' AND pwd = 'OR '1'= '1'
# SQL注入例子：万能密码 or '1'= '1
# 后面的 '1'='1' 必定为真，且前面都是OR，所以这条语句查询的内容在数据库中没有，但它依然能通过

推荐使用PreparedStatement取代Statement，可以防止SQL注入；

方法：

executeUpdate(sql) ：执行dml语句，返回影响的行数；

executeQuery(sql)：执行查询，返回ResultSet对象；

execute(sql)：执行任意的sql，返回布尔值；

Statement使用：

public void Statement01() throws Exception {
        Scanner scanner = new Scanner(System.in);
        //让用户输入管理员名和密码
        System.out.print("请输入管理员的名字: "); //
        String admin_name = scanner.nextLine(); //如果希望看到SQL注入，这里需要用 nextLine; 因为用next():当接收到 空格或者 '就是表示结束
        System.out.print("请输入管理员的密码: ");
        String admin_pwd = scanner.nextLine();
        //通过 Properties 对象获取配置文件的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        //获取相关的值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        //1. 注册驱动
        Class.forName(driver);//建议写上
        //2. 得到连接
        Connection connection = DriverManager.getConnection(url, user, password);
        //3. 得到 Statement
        Statement statement = connection.createStatement();
        //4. 组织 SqL
        String sql = "select name , pwd from admin where name ='" 
                + admin_name + "' and pwd = '" + admin_pwd + "'";
        ResultSet resultSet = statement.executeQuery(sql);
        if (resultSet.next()) { //如果查询到一条记录，则说明该管理存在
            System.out.println("恭喜， 登录成功");
        } else {
            System.out.println("对不起，登录失败");
        }
        //关闭连接
        resultSet.close();
        statement.close();
        connection.close();
    }

PreparedStatement

PreparedStatement 执行的SQL语句中的参数用问号(?)来表示；

调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数， .setXxx() 方法有两个参数，第一个参数是SQL语句中的参数的索引（从1开始，第几个问号），第二个参数是设置的 SQL语句中的参数的值；

方法：

executeUpdate(sql) ：执行dml语句，返回影响的行数；

executeQuery(sql)：执行查询，返回ResultSet对象；

execute(sql)：执行任意的sql，返回布尔值；

setXxx(占位符索引，占位符的值)：将指定位置的参数设置为给定值（解决了SQL注入）

setObject(占位符索引，占位符的值)：将指定位置的参数设置为给定的对象

PreparedStatement使用：

    public void preparedStatement() throws Exception {
        Scanner scanner = new Scanner(System.in);
        //让用户输入管理员名和密码
        System.out.print("请输入管理员的名字: ");
        String admin_name = scanner.nextLine();
        System.out.print("请输入管理员的密码: ");
        String admin_pwd = scanner.nextLine();
        //通过 Properties 对象获取配置文件的信息
        Properties properties = new Properties();

        properties.load(new FileInputStream("src\\mysql.properties"));
        //获取相关的值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        //1. 注册驱动
        Class.forName(driver);//建议写上
        //2. 得到连接
        Connection connection = DriverManager.getConnection(url, user, password);
        //3. 得到 PreparedStatement
        //3.1 组织SqL, 其中，Sql语句的 ? 就相当于占位符
        String sql = "select name , pwd from admin where name =? and pwd = ?";
        //3.2 preparedStatement 对象实现了 PreparedStatement 接口的实现类的对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //3.3 给 ? 赋值
        preparedStatement.setString(1, admin_name);
        preparedStatement.setString(2, admin_pwd);
        //4. 执行 select 语句使用 executeQuery
        // 如果执行的是 dml(update, insert ,delete) executeUpdate()
        ResultSet resultSet = preparedStatement.executeQuery(sql);
        if (resultSet.next()) {     //如果查询到一条记录，则说明该管理存在
            System.out.println("恭喜， 登录成功");
        } else {
            System.out.println("对不起，登录失败");
        }
        //关闭连接
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

ResultSet

ResultSet是结果集，表示数据库结果集的数据表，通常通过执行查询数据库的语句生成；

ResultSet对象保持一个光标指向当前的数据行，最初，光标位于第一行之前，next方法将光标移到下一行，且在ResultSet对象中没有更多行时返回false，因此可以在while循环中用来遍历结果集

方法：

next()：向下移动一行，如果没有下一行，返回false；

previous()：向上移动一行，如果没有上一行，返回false；

getXxx( 列的索引 / 列名) ：返回对应列的值，接收类型是Xxx

getObject(列的索引/ 列名) ：返回索引对应的值，接收类型为Object

ResultSet使用：

    public void ResultSet01() throws Exception {
        //通过 Properties 对象获取配置文件的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        //获取相关的值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        //1. 注册驱动
        Class.forName(driver);//建议写上

        //2. 得到连接
        Connection connection = DriverManager.getConnection(url, user, password);
        //3. 得到 Statement
        Statement statement = connection.createStatement();
        //4. 组织 SqL
        String sql = "select id, name , sex, borndate from actor";
        //执行给定的 SQL 语句，该语句返回单个 ResultSet 对象
        ResultSet resultSet = statement.executeQuery(sql);
        //5. 使用 while 取出数据
        while (resultSet.next()) { // 让光标向后移动，如果没有更多行，则返回 false
            int id = resultSet.getInt(1); //获取该行的第 1 列
            //int id1 = resultSet.getInt("id"); 通过列名来获取值, 推荐
            String name = resultSet.getString(2);//获取该行的第 2 列
            String sex = resultSet.getString(3);
            Date date = resultSet.getDate(4);
            System.out.println(id + "\t" + name + "\t" + sex + "\t" + date);
        }
        //6. 关闭连接
        resultSet.close();
        statement.close();
        connection.close();
    }

JDBCUtils(封装)

在jdbc操作中，获取连接和释放资源是经常使用到的，可以将其封装到 JDBC连接的工具类JDBCUtils中。

JDBCUtils 实现示例以及使用：

//JDBCUtils实现实例:
class JDBCUtils {
    //定义相关的属性(4个), 因为只需要一份，因此，我们做出 static
    private static String user; //用户名
    private static String password; //密码
    private static String url; //url
    private static String driver; //驱动名

    //在static代码块中进行初始化
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\mysql.properties"));
            //读取相关的属性值
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver = properties.getProperty("driver");
        } catch (IOException e) {
            //在实际开发中，我们可以这样处理
            //1. 将编译异常转成 运行异常
            //2. 调用者 可以选择捕获该异常，也可以选择默认处理该异常，比较方便.
            throw new RuntimeException(e);
        }
    }

    //连接数据库, 返回 Connection
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            //1. 将编译异常转成 运行异常
            //2. 调用者，可以选择捕获该异常，也可以选择默认处理该异常，比较方便.
            throw new RuntimeException(e);
        }
    }

    //关闭相关资源
    /*
    1. ResultSet 结果集
    2. Statement 或者 PreparedStatement
    3. Connection
    4. 如果需要关闭资源，就传入对象，否则传入 null
    */
    public static void close(ResultSet set, Statement statement, Connection connection) {
        //判断是否为 null
        try {
            if (set != null) {
                set.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            //将编译异常转成运行异常抛出
            throw new RuntimeException(e);
        }
    }
}

//使用实例:
class JDBCUtils_Use {
    @Test
    public void testSelect() {
        //1. 得到连接
        Connection connection = null;
        //2. 组织一个 sql
        String sql = "select * from actor where id = ?";
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        //3. 创建 PreparedStatement 对象
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 5);//给?号赋值
            //执行, 得到结果集
            set = preparedStatement.executeQuery();
            //遍历该结果集
            while (set.next()) {
                int id = set.getInt("id");
                String name = set.getString("name");
                String sex = set.getString("sex");
                Date borndate = set.getDate("borndate");
                String phone = set.getString("phone");
                System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.close(set, preparedStatement, connection);
        }
    }
}

使用Druid实现JDBCUtils

//用Druid实现JDBCUtils
public class JDBCUtilsByDruid {
    private static DataSource ds;

    //在静态代码块完成 ds 初始化
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //编写 getConnection 方法
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    //关闭连接, 注意：在数据库连接池技术中，close不是真的断掉连接，而是把使用的 Connection 对象放回连接池
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

//使用
public class JDBCUtilsByDruid_USE {
    public void testSelect() {
        //1. 得到连接
        Connection connection = null;
        //2. 组织一个 sql
        String sql = "select * from actor where id >= ?";
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        //3. 创建 PreparedStatement 对象
        try {
            connection = JDBCUtilsByDruid.getConnection();
            // System.out.println(connection.getClass());  运行类型 com.alibaba.druid.pool.DruidPooledConnection
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);//给问号赋值
            //执行, 得到结果集
            set = preparedStatement.executeQuery();
            //遍历该结果集
            while (set.next()) {
                int id = set.getInt("id");
                String name = set.getString("name");//getName()
                String sex = set.getString("sex");//getSex()
                Date borndate = set.getDate("borndate");
                String phone = set.getString("phone");
                System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtilsByDruid.close(set, preparedStatement, connection);
        }
    }
}

事务

JDBC程序中当一个Connection对象创建时，默认是自动提交事务的（语句执行成功，就会向数据库自动提交，而不能回滚）。

JDBC 使用事务：

使用Connection的setAutoCommit (false) 可以取消自动提交事务；
在所有sql语句成功执行后，调用Connection的commit() 方法提交事务；
当其中某个操作失败或出现异常，调用Connection的rollback() 方法回滚事务；

在异常捕获中，可以将 setAutoCommit (false) 方法和 commit() 方法放在try语句块中，将 rollback() 方法放在catch中，当出现异常时，就可以进行回滚

批处理

当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。

JDBC连接MySQL时，如果要使用批处理功能，要在url中加参数： rewriteBatchedStatements=true

批处理往往和PreparedStatement搭配使用，可以减少编译次数，运行次数，提高效率；

方法：

addBatch() ：添加需要批量处理的SQL语句或参数；

executeBatch() ：执行批量处理语句；

clearBatch() ：清空批处理包的语句；

    public void batch() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admin2 values(null, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
 
        for (int i = 0; i < 5000; i++) {    //5000次执行
            preparedStatement.setString(1, "jack" + i);
            preparedStatement.setString(2, "666");

            /*
            //addBatch源码:
            public void addBatch () throws SQLException {
                synchronized (this.checkClosed().getConnectionMutex()) {
                    if (this.batchedArgs == null) {
                        this.batchedArgs = new ArrayList();
                    }
                    for (int i = 0; i < this.parameterValues.length; ++i) {
                        this.checkAllParametersSet(this.parameterValues[i], this.parameterStreams[i], i);
                    }
                    this.batchedArgs.add(new PreparedStatement.BatchParams(this.parameterValues, this.parameterStreams, this.isStream, this.streamLengths, this.isNull));
                }
            }
            */
            preparedStatement.addBatch();
            //当有 1000 条记录时，再批量执行，并清空批处理包
            if ((i + 1) % 1000 == 0) {//满 1000 条 sql
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }
        //关闭连接
        JDBCUtils.close(null, preparedStatement, connection);
    }

数据库连接池

传统的 JDBC数据库使用DriverManager来获取连接的弊端：

每次向数据库建立连接都要将Connection加载到内存中，再验证IP地址，用户名和密码(0.05~1s)。
频繁的进行数据库连接操作将占用很多的系统资源，容易造成服务器崩溃。
每一次数据库连接，使用完后都得断开，如果程序出现异常而未能关闭，将导致数据库内存泄漏。
不能控制连接的方式，不能控制创建的连接数量，如果连接过多，会导致内存泄漏，MySQL崩溃。

因此有了数据库连接池技术（connection pool）。

JDBC的数据库连接池使用javax.sql.DataSource来表示， DataSource只是一个接口，该接口通常由第三方提供实现；

C3P0 数据库连接池：速度相对较慢，稳定性不错；
DBCP 数据库连接池：速度较C3P0快，但不稳定；
Proxool 数据库连接池：有监控连接池状态的功能，稳定性较C3P0差一些；
BoneCp 数据库连接池：速度快；
**Druid(德鲁伊)**是阿里提供的数据库连接池，集DBCP，C3P0，Proxool有点于一身的数据库连接池；

C3P0

//方式 1： 相关参数，在程序中指定 user, url , password 等
    public void testC3P0_01() throws Exception {
        //1. 创建一个数据源对象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        //2. 通过配置文件 mysql.properties 获取相关连接的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        //读取相关的属性值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");
        
        //给数据源 comboPooledDataSource 设置相关的参数
        //注意：连接管理是由 comboPooledDataSource 来管理
        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);
        //设置初始化连接数
        comboPooledDataSource.setInitialPoolSize(10);
        //最大连接数
        comboPooledDataSource.setMaxPoolSize(50);
        //测试连接池的效率, 测试对 mysql 5000 次操作
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {
            Connection connection = comboPooledDataSource.getConnection(); //这个方法就是从 DataSource 接口实现的
            //其他操作略
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("c3p0 5000 连接 mysql 耗时=" + (end - start));
    }

    //第二种方式 使用配置文件模板来完成
    //1. 将 c3p0 提供的 c3p0.config.xml 拷贝到 src 目录下
    //2. 该文件指定了连接数据库和连接池的相关参数
    public void testC3P0_02() throws SQLException {
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hsp_edu");
        //测试 5000 次连接 mysql
        long start = System.currentTimeMillis();
        System.out.println("开始执行....");
        for (int i = 0; i < 500000; i++) {
            Connection connection = comboPooledDataSource.getConnection();
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("c3p0 的第二种方式(500000) 耗时=" + (end - start));
    }

Druid

 public void testDruid() throws Exception {
        //1. 加入 Druid.jar 包
        //2. 加入 配置文件 druid.properties (将该文件拷贝项目的src目录)
        //3. 创建 Properties 对象, 读取配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\druid.properties"));
        //4. 创建一个指定参数的数据库连接池, Druid 连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 500000; i++) {
            Connection connection = dataSource.getConnection();
            //其他操作
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("druid 连接池 操作 500000 耗时=" + (end - start));
    }

DBUtils

DBUtils是对JDBC的封装，使用dbutils可以极大简化jdbc的编码的工作量。

DBUtils类：

QueryRunner类：该类封装了SQL的执行，是线程安全的。可以实现增，删，改，查，批处理；
ResultSetHandler接口：该接口用于处理java.sql.ResultSet，将数据按要求转换为另一种形式；
- ArrayHandler：把结果集中的第一行转成对象数组；
- ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中；
- BeanHandler：把结果集中的第一行数据封装到一个对应的 JavaBean实例中；
- BeanListHandler：把结果集中的每一行数据封装到一个对应的 JavaBean实例中，存放到List里；
- ColumnListHandler：将结果集中某一列的数据存放到List中；
- KeyedHandler(name)：将结果集中的每一行数据都封装到Map里，再把这些map再存到一个map里，其key为指定的key；
- MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值；
- MapListHandler：将结果集中的每一行数据都封装到一个Map里，再存放到list

DBUtils和Druid搭配使用例子：

class DBUtils_USE {
    //使用 apache-DBUtils 工具类 + druid 完成对表的 crud 操作
    public void testQueryMany() throws SQLException { //返回结果是多行的情况
        //1. 得到 连接 (druid)
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2. 使用 DBUtils 类和接口 , 先引入 DBUtils 相关的 jar , 加入到本 Project
        //3. 创建 QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4. 就可以执行相关的方法，返回 ArrayList 结果集
        //String sql = "select * from actor where id >= ?";
        // 注意: sql 语句也可以查询部分列
        String sql = "select id, name from actor where id >= ?";
        //(1) query 方法就是执行 sql 语句，得到 resultset ---封装到 --> ArrayList 集合中
        //(2) 返回集合
        //(3) connection: 连接
        //(4) sql : 执行的 sql 语句
        //(5) new BeanListHandler<>(Actor.class): 在将 resultset -> Actor 对象 -> 封装到 ArrayList
        // 底层使用反射机制 去获取 Actor 类的属性，然后进行封装
        //(6) 1 就是给 sql 语句中的第一个 ? 赋值，可以有多个值，因为此处是可变参数 Object... params
        //(7) PreparedStatment, 以及底层得到的 resultset ,会在 query 中关闭, 所以最后只需关闭连接connection就行
        List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
        System.out.println("输出集合的信息");
        for (Actor actor : list) {
            System.out.print(actor);
        }
        //释放资源
        JDBCUtilsByDruid.close(null, null, connection);
    }

    //演示 apache-dbutils + druid 完成 返回的结果是单行记录(单个对象)
    public void testQuerySingle() throws SQLException {
        //1. 得到 连接 (druid)
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2. 使用 DBUtils 类和接口 , 先引入 DBUtils 相关的 jar , 加入到本 Project
        //3. 创建 QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4. 就可以执行相关的方法，返回单个对象
        String sql = "select * from actor where id = ?";
        // 因为我们返回的单行记录<--->单个对象 , 使用的 Hander 是 BeanHandler
        Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 10);
        System.out.println(actor);
        // 释放资源
        JDBCUtilsByDruid.close(null, null, connection);
    }

    //演示 apache-dbutils + druid 完成查询结果是单行单列-返回的就是 object
    public void testScalar() throws SQLException {
        //1. 得到 连接 (druid)
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2. 使用 DBUtils 类和接口 , 先引入 DBUtils 相关的 jar , 加入到本 Project
        //3. 创建 QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4. 就可以执行相关的方法，返回单行单列 , 返回的就是 Object
        String sql = "select name from actor where id = ?";
        // 因为返回的是一个对象, 使用的 handler 就是 ScalarHandler
        Object obj = queryRunner.query(connection, sql, new ScalarHandler(), 4);
        System.out.println(obj);
        // 释放资源
        JDBCUtilsByDruid.close(null, null, connection);
    }

    //演示 apache-dbutils + druid 完成 dml (update, insert ,delete)
    public void testDML() throws SQLException {
        //1. 得到 连接 (druid)
        Connection connection = JDBCUtilsByDruid.getConnection();
        //3. 创建 QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4. 这里组织 sql 完成 update, insert delete
        //String sql = "update actor set name = ? where id = ?";
        //String sql = "insert into actor values(null, ?, ?, ?, ?)";
        String sql = "delete from actor where id = ?";
        //(1) 执行 dml 操作是 queryRunner.update()
        //(2) 返回的值是受影响的行数 (affected: 受影响)
        //int affectedRow = queryRunner.update(connection, sql, "林青霞", "116");
        //int affectedRow = queryRunner.update(connection, sql, "林青霞", "女", "1966-10-10", "116");
        int affectedRow = queryRunner.update(connection, sql, 1000);
        System.out.println(affectedRow > 0 ? "执行成功" : "执行没有影响到表");
        // 释放资源
        JDBCUtilsByDruid.close(null, null, connection);
    }
}

DAO

DAO：data access object 数据访问对象

这样的通用类，称为BasicDao，是专门和数据库交互的，即完成对数据库(表)的crud操作；

编写DAO的例子：

class BasicDAO<T> { //泛型指定具体类型
    private QueryRunner qr = new QueryRunner();

    //开发通用的 dml 方法, 针对任意的表
    public int update(String sql, Object... parameters) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            int update = qr.update(connection, sql, parameters);
            return update;
        } catch (SQLException e) {
            throw new RuntimeException(e); //将编译异常->运行异常 ,抛出
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }

    //返回多个对象(即查询的结果是多行), 针对任意表
    public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e); //将编译异常->运行异常 ,抛出
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }

    //查询单行结果 的通用方法
    public T querySingle(String sql, Class<T> clazz, Object... parameters) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection, sql, new BeanHandler<T>(clazz), parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e); //将编译异常->运行异常 ,抛出
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }

    //查询单行单列的方法,即返回单值的方法
    public Object queryScalar(String sql, Object... parameters) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection, sql, new ScalarHandler(), parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e); //将编译异常->运行异常 ,抛出
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }
}


class ActorDAO extends BasicDAO<Actor> {
    // 根据业务需求，可以编写特有的方法. 
    public class TestDAO {
        //测试 ActorDAO 对 actor 表 crud 操作
        public void testActorDAO() {
            ActorDAO actorDAO = new ActorDAO();
            //1. 查询
            List<Actor> actors = actorDAO.queryMulti("select * from actor where id >= ?", Actor.class, 1);
            System.out.println("===查询结果===");
            for (Actor actor : actors) {
                System.out.println(actor);
            }
            //2. 查询单行记录
            Actor actor = actorDAO.querySingle("select * from actor where id = ?", Actor.class, 6);
            System.out.println("====查询单行结果====");
            System.out.println(actor);
            //3. 查询单行单列
            Object o = actorDAO.queryScalar("select name from actor where id = ?", 6);
            System.out.println("====查询单行单列值===");
            System.out.println(o);
            //4. dml 操作 insert ,update, delete
            int update = actorDAO.update("insert into actor values(null, ?, ?, ?, ?)", "张无忌", "男", "2000-11-11", "999");
            System.out.println(update > 0 ? "执行成功" : "执行没有影响表");
        }
    }
}

架构

MySQL的数据目录

MySQL数据库文件的存放路径：/var/lib/mysql/

相关命令目录：/usr/bin 和 /usr/sbin。

配置文件目录：/usr/share/mysql-8.0（命令及配置文件），/etc/mysql（如my.cnf）

数据库和文件系统的关系：

MySQL有4个自带的系统数据库：mysql，information_schema，performance_schema，sys

mysql

MySQL 系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
information_schema

保存mysql服务器维护其他所有数据库的信息，比如有哪些表，哪些触发器等等，是一些描述性信息，称之为元数据。

information_schema还提供了一些以innodb_sys开头的表，用于表示内部系统表。
performance_schema

保存MySQL服务器运行过程中的一些状态信息，可以用来监控 MySQL 服务的各类性能指标。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。
sys

这个数据库主要是通过视图的形式把 information_schema 和 performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。

表在文件系统中的表示：

InnoDB存储引擎模式：
- 表结构：
  
  为了保存表结构， InnoDB 在数据目录下对应的数据库子目录下创建了一个专门用于 描述表结构的文件 ，文件名是这样：表名.frm
- 表的数据和索引：
  - 系统表空间（5.6.6版本以前）
    
    默认情况下，InnoDB会在数据目录下创建一个名为 ibdata1 、大小为 12M 的文件，这个文件就是对应的系统表空间在文件系统上的表示。它是一个自扩展文件。
  - 独立表空间（5.6.6版本以后）
    
    InnoDB 为每一个表建立一个独立表空间，表现为：在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，为：表名.ibd
MyISAM存储引擎模式：
- 表结构：
  
  （同InnoDB一样）
- 表数据和索引：
  
  在MyISAM中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件，同时表数据都存放在对应的数据库子目录下。
  
  MyISAM存储引擎会在表所在数据库对应的 atguigu 目录下创建这三个文件：
```
表名.frm 存储表结构
表名.MYD 存储数据 (MYData)
表名.MYI 存储索引 (MYIndex)
```