目录
第一章 数据库概述1.1、数据库的好处1.2、数据库的常见概念1.3、数据库的存储特点1.4、数据库的常见分类1.5、SQL语言的分类
第二章 MySQL概述2.1、MySQL的背景2.2、MySQL的优点2.3、MySQL的安装2.4、MySQL的启动2.5、MySQL的停止2.6、MySQL的登录2.7、MySQL的退出
第三章 DQL语言3.1、基础查询3.2、条件查询3.3、排序查询3.4、单行函数3.5、分组函数3.6、分组查询3.7、连接查询3.8、sql99标准3.9、子查询3.10、分页查询3.11、联合查询
第四章 DML语言4.1、插入语句4.2、修改语句4.3、删除语句
第五章 DDL语言5.1、库的管理5.1.1、库的创建5.1.2、库的修改5.1.3、库的删除
5.2、表的管理5.2.1、表的创建5.2.2、表的修改5.2.3、表的删除5.2.4、表的复制
5.3、数据类型5.3.1、数值型5.3.2、浮点型5.3.3、字符型5.3.4、日期型
5.4、常见约束5.5、自增长列
第六章 DCL语言6.1、创建用户6.2、删除用户6.3、用户授权6.4、撤销授权6.5、刷新授权6.6、查看授权6.7、修改密码
第七章 TCL语言7.1、事务7.2、事务并发（读问题）7.3、丢失更新（写问题）
第八章 高级部分8.1、索引8.2、视图8.3、存储过程8.3.1、语法8.3.2、变量8.3.3、分支结构8.3.3.1、if结构8.3.3.2、case结构
8.3.4、循环结构8.3.4.1、while结构8.3.4.2、repeat结构8.3.4.3、loop结构
8.3.5、游标
8.4、函数8.5、触发器8.6、事件
第九章 数据备份与恢复9.1、数据导出9.2、数据导入
第十章 安装MySQL单实例10.1、准备虚拟机10.2、删除自带库10.3、安装依赖包10.4、下载服务端10.5、安装服务端10.6、下载客户端10.7、安装客户端10.8、启动数据库服务10.9、查看初始化密码10.10、修改初始化密码10.11、远程的授权10.12、开放防火墙10.13、添加自启动10.14、测试连接10.15、关闭系统
第十一章 安装MySQL主从复制11.1、准备虚拟机11.2、主从复制介绍11.3、主从复制架构11.4、主节点配置11.5、从节点配置11.6、连接测试
第十二章 安装MySQL读写分离12.1、环境准备12.2、读写分离介绍12.3、读写分离架构12.4、修改从节点二12.5、安装proxysql12.6、删除自带库12.7、安装依赖包12.8、下载服务端12.9、安装服务端12.10、下载客户端12.11、安装客户端12.12、启动数据库服务12.13、查看初始化密码12.14、修改初始化密码12.15、创建两个账号12.16、配置proxysql12.17、连接proxysql12.18、查询监控数据
第十三章 安装MySQL分片集群13.1、环境准备13.2、分片集群介绍13.3、分片集群架构13.4、Mycat的介绍13.5、Mycat的分片13.6、Mycat的安装13.7、Mycat的配置13.8、Mycat的启动13.9、Mycat的测试13.10、Mycat的效果13.11、Mycat的踩坑
配套资料，免费下载
链接：https://pan.baidu.com/s/1WmxBogBdP2yyCSe6YPm6Hg
提取码：y287
复制这段内容后打开百度网盘手机App，操作更方便哦
第一章 数据库概述
1.1、数据库的好处
- 将数据持久化到本地提供结构化查询功能
1.2、数据库的常见概念
- DB：数据库，存储数据的仓库DBMS：数据库管理系统，又称为数据库软件或者数据库产品，用于创建和管理数据库，常见的有MySQL、Oracle、SQL ServerDBS：数据库系统，数据库系统是一个通称，包括数据库、数据库管理系统、数据库管理人员等，是最大的范畴SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言
1.3、数据库的存储特点
- 数据存放到表中，然后表再放到库中一个库中可以有多张表，每张表具有唯一的表名用来标识自己表中有一个或多个列，列又称为“字段”，相当于Java中“属性”表中的每一行数据，相当于Java中“对象”
1.4、数据库的常见分类
- 关系型数据库：MySQL、Oracle、DB2、SQL Server非关系型数据库：
键值存储数据库：Redis、Memcached、MemcacheDB列存储数据库：HBase、Cassandra面向文档的数据库：MongDB、CouchDB图形数据库：Neo4J
1.5、SQL语言的分类
- DQL：数据查询语言：select、from、whereDML：数据操作语言：insert、update、deleteDDL：数据定义语言：create、alter、drop、truncateDCL：数据控制语言：grant、revokeTCL：事务控制语言：commit、rollback
第二章 MySQL概述
2.1、MySQL的背景
MySQL的前身是属于MySQL AB，08年被SUN公司收购，09年SUN公司又被Oracle公司收购
2.2、MySQL的优点
- 成本低、开源免费性能高、移植性好体积小、便于安装
2.3、MySQL的安装
2.4、MySQL的启动
net start MySQL
2.5、MySQL的停止net stop MySQL
2.6、MySQL的登录mysql -h主机名 -P端口号 -u用户名 -p密码
2.7、MySQL的退出exit
第三章 DQL语言
3.1、基础查询一、语法
SELECT 查询列表 FROM 表名;
二、特点
查询列表可以是字段、常量、函数、表达式查询结果是一个虚拟表
三、示例
1、查询单个字段
SELECT 字段名 FROM 表名;
2、查询多个字段
SELECT 字段名,字段名 FROM 表名;
3、查询所有字段
SELECT * FROM 表名;
4、查询常量
SELECT 常量值;
注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
5、查询函数
SELECT 函数名(实参列表);
6、查询表达式
SELECT 100/25;
7、起别名
1、 SELECT 字段名 AS "别名" FROM 表名;
注意：别名可以使用单引号、双引号引起来，当只有一个单词时，可以省略引号，当有多个单词且有空格或特殊符号时，不能省略，AS可以省略
8、去重复
SELECT DISTINCT 字段名 FROM 表名;
9、做加法
1、 SELECT 数值+数值; 直接运算
2、 SELECT 字符+数值; 首先先将字符转换为整数，如果转换成功，则继续运算，如果转换失败，则默认为0，然后继续运算
3、 SELECT NULL+数值; NULL和任何数值参与运算结果都是NULL
10、【补充】ifnull函数
功能：判断某字段或表达式是否为null，如果为null，返回指定的值，否则返回原本的值
SELECT IFNULL(字段名, 指定值) FROM 表名;
11、【补充】isnull函数
功能：判断某字段或表达式是否为null，如果是null，则返回1，否则返回0
SELECT ISNULL(字段名) FROM 表名;
3.2、条件查询一、语法
SELECT 查询列表 FROM 表名 WHERE 筛选条件;
二、分类
条件运算符：>、>=、<、<=、=、<=>、!=、<>逻辑运算符：and、or、not模糊运算符：
like：%任意多个字符、_任意单个字符，如果有特殊字符，需要使用escape转义between andnot between andinis nullis not null三、演示
1、查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE salary > 12000 ;
2、查询工资>=14000的员工信息
SELECT
*
FROM
employees
WHERE salary >= 14000 ;
3、查询工资<12000的员工信息
SELECT
*
FROM
employees
WHERE salary < 12000 ;
4、查询工资<=14000的员工信息
SELECT
*
FROM
employees
WHERE salary <= 14000 ;
5、查询员工编号=100的员工信息
SELECT
*
FROM
employees
WHERE employee_id = 100 ;
6、查询员工编号<=>100的员工信息
SELECT
*
FROM
employees
WHERE employee_id <=> 100 ;
注意：=只能判断普通类型的数值，而<=>不仅可以判断普通类型的数值还可以判断NULL
7、查询员工编号!=100的员工信息
SELECT
*
FROM
employees
WHERE employee_id != 100 ;
8、查询员工编号<>100的员工信息
SELECT
*
FROM
employees
WHERE employee_id <> 100 ;
注意：!=和<>都是判断不等于的意思，但是MySQL推荐使用<>
9、查询工资>12000&&工资<18000的员工信息
SELECT
*
FROM
employees
WHERE salary > 12000 AND salary < 18000 ;
10、查询工资<=12000||工资>=18000的员工信息
SELECT
*
FROM
employees
WHERE salary <= 12000 OR salary >= 18000 ;
11、查询工资<=12000||工资>=18000的员工信息
SELECT
*
FROM
employees
WHERE NOT (salary > 12000 AND salary < 18000) ;
12、查询员工名中第一个字符为B、第四个字符为d的员工信息
SELECT
*
FROM
employees
WHERE last_name LIKE 'B__d%' ;
13、查询员工编号在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE employee_id BETWEEN 100 AND 120 ;
14、查询员工编号不在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE employee_id NOT BETWEEN 100 AND 120 ;
15、查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE job_id IN ('IT_PROT', 'AD_VP', 'AD_PRES') ;
注意：in列表的值类型必须一致或兼容，in列表中不支持通配符%和_
16、查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct IS NULL ;
17、查询有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct IS NOT NULL ;
注意：=、!=不能用来判断NULL、而<=>、is null 、 is not null可以用来判断NULL，但注意<=>也可以判断普通类型的数值
3.3、排序查询
一、语法
SELECT
查询列表
FROM
表
【WHERE 筛选条件】
ORDER BY 排序列表 【asc | desc】 ;
二、注意
排序列表可以是单个字段、多个字段、别名、函数、表达式asc代表升序，desc代表降序，如果不写，默认是ascorder by的位置一般放在查询语句的最后（除limit语句之外）
三、示例
1、按单个字段排序：查询员工信息，要求按工资降序
SELECT
*
FROM
employees
ORDER BY salary DESC ;
2、按多个字段查询：查询员工信息，要求先按工资降序，再按员工编号升序
SELECT
*
FROM
employees
ORDER BY salary DESC, employee_id ASC ;
3、按别名排序查询：查询员工信息，要求按员工年薪升序
SELECT
*,
salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY 年薪 ASC ;
4、按函数排序查询：查询员工信息，要求按员工名字的长度降序
SELECT
LENGTH(last_name),
last_name
FROM
employees
ORDER BY LENGTH(last_name) DESC ;
5、按表达式排序：查询员工信息，要求按员工年薪升序
SELECT
*,
salary * 12 * (1+ IFNULL(commission_pct, 0))
FROM
employees
ORDER BY salary * 12 * (1+ IFNULL(commission_pct, 0)) ASC ;
3.4、单行函数一、语法
SELECT 函数名(实参列表) 【FROM 表】;
二、分类
字符函数
concat：连接字符substr：截取子串replace：替换字符upper：变大写lower：变小写lpad：左填充rpad：右填充length：获取字节长度trim：去除前后空格instr：获取子串第一次出现的索引
1、注意MySQL中的索引是从1开始的
数学函数
round：四舍五入ceil：向上取整floor：向下取整mod：取模运算（a-a/b*b）truncate：保留小数的位数，不进行四舍五入rand：获取随机数，返回0-1之间的小数
日期函数
now：返回当前日期+时间curdate：返回当前日期curtime：返回当前时间year：返回年month：返回月day：返回日hour：小时minute：分钟second：秒monthname：以英文形式返回月datediff：返回两个日期相差的天数date_format：将日期转换成字符str_to_date：将字符转换成日期
格式符：
%Y：四位的年份
%y：二位的年份
%m：二位的月份（01，02，...，12）
%c：一位的月份（1，2，...，12）
%d：日（01，02，...，31）
%H：小时（24小时制）
%h：小时（12小时制）
%i：分钟（00，01，02，...，59）
%s：秒（00，01，02，...，59）
控制函数
if：判断函数case：分支函数
1、IF(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式22、case的格式一：
CASE 变量或字段或表达式
WHEN 常量1 THEN 值1
WHEN 常量2 THEN 值2
...
ELSE 值n
END ;3、case的格式二：
CASE
WHEN 条件1 THEN 值1
WHEN 条件2 THEN 值2
...
ELSE 值n
END
其它函数
version：当前数据库的版本database：当前打开的数据库user：当前登录的用户password(‘字符’)：返回该字符的密码形式md5(‘字符’)：返回该字符的md5加密形式
三、演示
1、concat
SELECT CONCAT('Hello',' ','World') AS out_put;
2、substr
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) AS out_put;#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) AS out_put;
3、replace
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
4、upper
SELECT UPPER('john') AS out_put;
5、lower
SELECT LOWER('john') AS out_put;
6、lpad
SELECT LPAD('殷素素',10,'*') AS out_put;
7、rpad
SELECT RPAD('殷素素',10,'*') AS out_put;
8、length
SELECT LENGTH('john') AS out_put;
9、trim
#删除指定字符的左右空格
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;#删除指定字符的指定字符
SELECT TRIM('aaa' FROM 'aaaaaaaaa张翠山aaaaaaaaa') AS out_put;
10、instr
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;
注意：返回子串第一次出现的索引，如果找不到返回0
11、round
#默认四舍五入
SELECT ROUND(-1.55) AS out_put;#指定小数位数
SELECT ROUND(1.567,2) AS out_put;
注意：四舍五入和符号无关
12、ceil
SELECT CEIL(-1.02) AS out_put;
注意：向上取整，返回>=该参数的最小整数
13、floor
SELECT FLOOR(-9.99) AS out_put;
注意：向下取整，返回<=该参数的最大整数
14、mod
SELECT MOD(10,3) AS out_put;
15、truncate
SELECT TRUNCATE(1.69999,1) AS out_put;
16、rand
SELECT RAND() AS out_put;
17、now
SELECT NOW() AS out_put;
18、curdate
SELECT CURDATE() AS out_put;
19、curtime
SELECT CURTIME() AS out_put;
20、year
SELECT YEAR(NOW()) 年;
21、month
SELECT MONTH(NOW()) 月;
22、day
SELECT DAY(NOW()) 日;
23、hour
SELECT HOUR(NOW()) 时;
24、minute
SELECT MINUTE(NOW()) 分;
25、second
SELECT SECOND(NOW()) 秒;
26、monthname
SELECT MONTHNAME(NOW()) 月名;
27、datediff
SELECT DATEDIFF('1995-2-7','1995-2-6') AS out_put;
28、date_format
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS out_put;
29、str_to_date
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
30、if
SELECT IF(10 < 5, '大', '小') AS out_put;
31、case的格式一
/*
案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其它部门，显示的工资为原工资
*/SELECT
salary 原始工资,
department_id,
CASE
department_id
WHEN 30 THEN salary * 1.1
WHEN 40 THEN salary * 1.2
WHEN 50 THEN salary * 1.3
ELSE salary
END AS 新工资
FROM
employees ;
32、case的格式二
/*
案例：查询员工的工资情况
如果工资>20000，显示A级别
如果工资>15000，显示B级别
如果工资>10000，显示C级别
否则，显示D级别
*/SELECT
salary,
CASE
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM
employees ;
33、version
SELECT VERSION();
34、database
SELECT DATABASE();
35、user
SELECT USER();
36、password(‘字符’)
SELECT PASSWORD('123456');
37、md5(‘字符’)
SELECT MD5('123456');
3.5、分组函数一、语法
SELECT 函数名(实参列表) 【FROM 表】;
二、分类
sum：求和
avg：平均值
max：最大值
min：最小值
count：计算个数
1、sum、avg一般用于处理数值型，max、min、count可以处理任何类型2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算：select sum(distinct 字段) from 表;
4、一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段
三、演示
1、sum
SELECT SUM(salary) FROM employees;
2、avg
SELECT AVG(salary) FROM employees;
3、max
SELECT MAX(salary) FROM employees;
4、min
SELECT MIN(salary) FROM employees;
5、count
SELECT COUNT(salary) FROM employees;
3.6、分组查询一、语法
SELECT
查询列表
FROM
表
【where 筛选条件】
GROUP BY 分组的字段
【having 分组后的筛选】
【order BY 排序的字段】 ;
二、特点
1、和分组函数一同查询的字段必须是group by后出现的字段2、筛选分为两类：分组前筛选和分组后筛选
针对的表 语句位置 连接的关键字
分组前筛选 分组前的原始表 group by前 where
分组后筛选 分组后的结果集 group by后 having3、分组可以按单个字段也可以按多个字段
4、分组可以搭配着排序使用
三、演示
1、查询每个工种的员工平均工资
SELECT
AVG(salary),
job_id
FROM
employees
GROUP BY job_id ;
2、查询每个位置的员工部门个数
SELECT
COUNT(*),
location_id
FROM
departments
GROUP BY location_id ;
3、查询有奖金的每个领导手下员工的平均工资
SELECT
AVG(salary),
manager_id
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id ;
4、查询哪个部门的员工个数>5
SELECT
COUNT(*),
department_id
FROM
employees
GROUP BY department_id
HAVING COUNT(*) > 5 ;
5、查询每个工种有奖金的员工的最高工资>6000的最高工资和公众编号，按最高工资升序
SELECT
MAX(salary) m,
job_id
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m > 6000
ORDER BY m ;
6、查询每个工种每个部门的最低工资并按最低工资降序
SELECT
MIN(salary),
job_id,
department_id
FROM
employees
GROUP BY job_id, department_id
ORDER BY MIN(salary) DESC ;
3.7、连接查询一、含义
连接查询又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
二、注意
笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
三、分类
按年代分类
sql92标准：支持内连接sql99标准：支持内连接、部分外连接（左外、右外）、交叉连接 按功能分类
内连接
等值连接非等值连接自连接 外连接
左外连接右外连接全外连接 交叉连接四、sql92标准演示
1、sql92标准：等值连接
#查询员工名和对应的部门名SELECT
last_name,
department_name
FROM
employees,
departments
WHERE employees.`department_id` = departments.`department_id` ;
2、sql92标准：非等值连接
#查询员工的工资和工资级别SELECT
salary,
grade_level
FROM
employees e,
job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal` ;
3、sql92标准：自连接
#查询员工名和它对应上级的名称SELECT
e.employee_id,
e.last_name,
m.employee_id,
m.last_name
FROM
employees e,
employees m
WHERE e.`manager_id` = m.`employee_id` ;
3.8、sql99标准一、语法
SELECT
查询列表
FROM 表1 别名1
【连接类型】 JOIN 表2 别名2 ON 连接条件
【where 分组前筛选条件】
【group BY 分组列表】
【having 分组后筛选条件】
【order BY 排序列表】 ;
二、连接类型
内连接：inner外连接
左外连接：left 【outer】（左边的是主表）右外连接：right 【outer】（右边的是主表）全外连接：full 【outer】（两边都是主表，但是MySQL不支持全外连接、Oracle支持） 交叉连接：cross（交叉连接其实是用sql99语法实现笛卡尔乘积）三、演示
1、内连接：等值连接
#查询员工名和对应的部门名SELECT
last_name,
department_name
FROM
departments d
INNER JOIN employees e ON e.`department_id` = d.`department_id` ;
2、内连接：非等值连接
#查询员工的工资和工资级别SELECT
salary,
grade_level
FROM
employees e
INNER JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal` ;
3、内连接：自连接
#查询员工名和它对应上级的名称SELECT
e.last_name,
m.last_name
FROM
employees e
INNER JOIN employees m ON e.`manager_id` = m.`employee_id` ;
4、外连接：左外连接
#查询哪个部门没有员工SELECT
d.`department_name`
FROM
departments d
LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL ;
5、外连接：右外连接
#查询哪个部门没有员工SELECT
d.`department_name`
FROM
employees e
RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL ;
6、交叉连接
#使用交叉连接进行笛卡尔乘积查询SELECT
b.*,
bo.*
FROM beauty b
CROSS JOIN boys bo ;
3.9、子查询一、含义
嵌套在其它语句内部的select语句称为子查询或内查询，外面的语句可以是insert、delete、update、select等，一般select作为外面语句较多，外面如果为select语句，则此语句称为外查询或主查询
二、分类
按出现的位置划分
select后面：标量子查询from后面：表子查询where或having后面
标量子查询列子查询行子查询 exists后面
标量子查询列子查询行子查询表子查询 按结果集行列数划分
标量子查询（单行子查询）：结果集为一行一列列子查询（多行子查询）：结果集为多行一列行子查询：结果集为多行多列表子查询：结果集为多行多列三、特点
子查询放在小括号内子查询一般放在条件的右侧子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果标量子查询，一般搭配着单行操作符使用：>、>=、<、<=、!=、<>、=、<=>列子查询，一般搭配着多行操作符使用：in、not in、any、some、all、exits
四、演示
1、select后面
#查询每个部门的员工个数SELECT
d.*, (
SELECT
COUNT(*)
FROM
employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM
departments d ;
2、from后面
#查询每个部门平均工资的工资等级SELECT
ag_dep.*,
g.`grade_level`
FROM (
SELECT
AVG(salary) ag,
department_id
FROM
employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal AND highest_sal ;
3、where或having后面
标量子查询：查询最低工资的员工姓名和工资
SELECT
last_name,
salary
FROM
employees
WHERE salary = (
SELECT MIN(salary) FROM employees
) ;
列子查询：
#查询所有是领导的员工姓名SELECT
last_name
FROM
employees
WHERE employee_id IN (
SELECT DISTINCT manager_id FROM employees
) ;
#返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id以及salarySELECT
employee_id,
last_name,
job_id,
salary
FROM
employees
WHERE salary < ANY (
SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG' ;
#返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salarySELECT
employee_id,
last_name,
job_id,
salary
FROM
employees
WHERE salary < ALL (
SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG' ;
行子查询：查询员工编号最小并且工资最高的员工信息
SELECT
*
FROM
employees
WHERE (employee_id, salary) = (
SELECT MIN(employee_id), MAX(salary) FROM employees
) ;
4、exists后面
#查询有员工的部门名SELECT
department_name
FROM
departments d
WHERE EXISTS (
SELECT * FROM employees e
WHERE e.`department_id` = d.`department_id`
) ;
3.10、分页查询一、语法
SELECT
查询列表
FROM
表1 别名1
【连接类型】 JOIN 表2 别名2 ON 连接条件
【WHERE 分组前的筛选】
【GROUP BY 分组字段】
【HAVING 分组后的筛选 】
【ORDER BY 排序字段 ASC|DESC】
LIMIT 【offset, 】size ;
二、特点
limit语句放在查询语句的最后offset代表起始索引，起始索引从0开始，size代表条目个数分页语句：select 查询列表 from 表 limit (page-1)*size,size;
三、演示
#查询前五条员工信息SELECT * FROM employees LIMIT 0,5;
3.11、联合查询一、语法
查询语句1
union 【all】
查询语句2
union 【all】
...
二、特点
要查询的结果来自于多个表且多个表没有直接的连接关系，但查询的信息一致时，可以使用联合查询要求多条查询语句的查询列数是一致的要求多条查询语句的查询的每一列的类型和顺序最好一致union关键字默认去重，如果使用union all可以包含重复项
三、演示
#查询中国用户中男性的信息以及外国用户中年男性的用户信息SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';
第四章 DML语言
4.1、插入语句一、语法
#方式一：
INSERT INTO 表名(字段名,...) VALUES(值,...);#方式二：
INSERT INTO 表名 SET 字段名=值,字段名=值,...;
二、特点
要求值的类型和字段的类型要一致或兼容字段的个数和顺序不一定与原始表中的字段个数和顺序一致，但必须保证值和字段一一对应假如表中有可以为null的字段，注意可以通过以下两种方式插入null值：①字段和值都省略、②字段写上，值使用null字段和值的个数必须一致字段名可以省略，默认所有列方式一支持一次插入多行，语法如下：INSERT INTO 表名【(字段名,…)】 VALUES(值,…),(值,…),…;方式一支持子查询，语法如下：INSERT INTO 表名 查询语句;
三、演示
1、方式一：插入数据
INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
VALUES(15,'唐艺昕','女','1997-12-05','15633029014',NULL,2);
2、方式二：插入数据
INSERT INTO beauty SET
id = 19,name = '张倩倩',
sex = '女',
borndate = '1997-12-05',
phone = '15633029014',
photo = NULL,
boyfriend_id = 3 ;
4.2、修改语句一、语法
1、单表更新
UPDATE 表名 SET 列 = 值,... WHERE 查询条件;2、多表更新
#sql92语法：
UPDATE
表1 别名,
表2 别名
SET
列 = 值,
...
WHERE 连接条件 AND 筛选条件 ;#sql99语法：
UPDATE
表1 别名
INNER | LEFT | RIGHT JOIN 表2 别名 ON 连接条件
SET 列 = 值,
...
WHERE 筛选条件 ;
二、演示
1、单表更新
#修改beauty表中姓唐的女神的电话为13899888899UPDATE beauty SET phone = '13899888899' WHERE NAME LIKE '唐%';
2、多表更新
#修改张无忌的女朋友的手机号为13899888899，魅力值为1000
sql92语法：
UPDATE
boys bo,
beauty b
SET b.`phone` = '13899888899',
bo.`userCP` = 1000
WHERE bo.`id` = b.`boyfriend_id` AND bo.`boyName` = '张无忌' ;#修改张无忌的女朋友的手机号为13899888899，魅力值为1000
sql99语法：
UPDATE
boys bo
INNER JOIN beauty b ON bo.`id` = b.`boyfriend_id`
SET b.`phone` = '13899888899',
bo.`userCP` = 1000
WHERE bo.`boyName` = '张无忌' ;
4.3、删除语句一、语法
1、单表删除
DELETE FROM 表名 【WHERE 筛选条件 】;2、多表删除（级联删除）
sql92语法：
DELETE
表1的别名,
表2的别名
FROM
表1 别名,
表2 别名
WHERE 连接条件 AND 筛选条件 ;sql99语法：
DELETE
表1的别名,
表2的别名
FROM
表1 别名
INNER | LEFT | RIGHT JOIN 表2 别名 ON 连接条件
WHERE 筛选条件 ;
二、演示
1、单表删除
#删除手机号以9结尾的女神信息DELETE FROM beauty WHERE phone LIKE '%9';
2、多表删除
#删除黄晓明的信息以及他女朋友的信息
#sql92语法：
DELETE
b,
bo
FROM
beauty b,
boys bo
WHERE b.`boyfriend_id` = bo.`id` AND bo.`boyName` = '黄晓明' ;#删除黄晓明的信息以及他女朋友的信息
#sql99语法：
DELETE
b,
bo
FROM
beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName` = '黄晓明' ;
第五章 DDL语言
5.1、库的管理
5.1.1、库的创建CREATE DATABASE 【IF NOT EXISTS】 库名 【 CHARACTER SET 字符集名】;
5.1.2、库的修改#它已经被废弃
RENAME DATABASE 旧库名 TO 新库名;#修改库字符集
ALTER DATABASE 库名 CHARACTER SET 字符集名;
5.1.3、库的删除DROP DATABASE 【IF EXISTS】 库名;
5.2、表的管理
5.2.1、表的创建CREATE TABLE 【IF NOT EXISTS】 表名 (
字段名 字段类型 【约束】,
字段名 字段类型 【约束】,
...
字段名 字段类型 【约束】
) ;
5.2.2、表的修改1、添加列
ALTER TABLE 表名 ADD COLUMN 列名 类型 【FIRST|AFTER 字段名】;2、修改列的类型或约束
ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 【新约束】;3、修改列名
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;4、删除列
ALTER TABLE 表名 DROP COLUMN 列名;5、修改表名
ALTER TABLE 表名 RENAME 【TO】 新表名;
5.2.3、表的删除方式一：DROP TABLE 【IF EXISTS】 表名;方式二：TRUNCATE TABLE 【IF EXISTS】 表名;
5.2.4、表的复制1、复制表的结构
CREATE TABLE 表名 LIKE 旧表;2、复制表的某些字段
CREATE TABLE 表名
SELECT 字段1,字段2,... FROM 旧表 WHERE 0;3、复制表的结构+数据
CREATE TABLE 表名
SELECT 查询列表 FROM 旧表 【WHERE 筛选条件】;4、复制表的某些字段+数据
CREATE TABLE 表名
SELECT 字段1,字段2,... FROM 旧表 【WHERE 筛选条件】;
5.3、数据类型
5.3.1、数值型一、类型
类型 TINYINT SMALLINT MEDIUMINT INT/INTEGER BIGINT 字节12348
二、特点
都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号如果超出了范围，会报out or range异常，插入临界值（该类型的最大值或最小值即为临界值）长度可以不指定，默认会有一个长度，长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型如果对数据没有特殊要求，则优先考虑使用INT/INTEGER
5.3.2、浮点型
一、类型
定点数
DEC(M,D) ：M+2字节DECIMAL(M,D)：M+2字节 浮点数
FLOAT(M,D) ：4字节DOUBLE(M,D)：8字节二、特点
M代表整数部位+小数部位的个数，D代表小数部位如果超出范围，则报out or range异常，并且插入临界值（该类型的最大值或最小值即为临界值）M和D都可以省略，但对于定点数，M默认为10，D默认为0如果精度要求较高，则优先考虑使用定点数
5.3.3、字符型
一、类型
类型 CHAR VARCHAR BINARY VARBINARY ENUM SET TEXT BLOB 描述固定长度字符可变长度字符二进制字符串二进制字符串枚举集合文本二进制大型对象
二、特点
char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，其中M不可以省略如果对数据没有特殊要求，则优先考虑使用VARCHAR
5.3.4、日期型
一、类型
类型 YEAR DATE TIME DATETIME TIMESTAMP 描述年份日期时间日期+时间日期+时间
二、特点
TIMESTAMP比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间，而DATETIME则只能反映出插入时的当地时区TIMESTAMP支持的时间范围较小，DATETIME的取值范围：1000-1-1 — 9999-12-31TIMESTAMP的属性受Mysql版本和SQLMode的影响很大如果对数据没有特殊要求，则优先考虑使用DATETIME
5.4、常见约束
一、含义
约束是一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
二、分类
NOT NULL：非空，该字段的值必填UNIQUE：唯一，该字段的值不可重复DEFAULT：默认，该字段的值不用手动插入有默认值CHECK：检查，MySQL不支持PRIMARY KEY：主键，该字段的值不可重复并且非空 unique+not nullFOREIGN KEY：外键，该字段的值引用了另外的表的字段
三、特点
主键和唯一
#不同点：
1、一个表至多有一个主键，但可以有多个唯一
2、主键不允许为空，唯一可以为空#相同点：
1、都具有唯一性
2、都支持组合键，但不推荐
外键
1、用于限制两个表的关系，从表的字段值引用了主表的某字段值
2、外键列和主表的被引用列要求类型一致，意义一样，名称无要求
3、主表的被引用列要求是一个key（一般就是主键）
4、插入数据，先插入主表；删除数据，先删除从表可以通过以下两种方式来删除主表的记录
#方式一：级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;#方式二：级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
四、演示
1、创建表时添加约束
CREATE TABLE 表名(
字段名 字段类型 NOT NULL,#非空
字段名 字段类型 DEFAULT 值,#默认
字段名 字段类型 PRIMARY KEY,#主键
字段名 字段类型 UNIQUE,#唯一
CONSTRAINT 约束名 FOREIGN KEY(字段名) REFERENCES 主表(被引用列)
) ;
注意：
列级约束支持：非空、默认、主键、唯一，不可以起约束名表级约束支持：主键、唯一、外键，可以起约束名，但是在MySQL中对主键无效列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求
2、修改表时添加或删除约束
1、非空
添加非空（列级约束）
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 NOT NULL;
删除非空
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;2、默认
添加默认（列级约束）
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 DEFAULT 值;
删除默认
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;3、主键
添加主键（列级约束）
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 PRIMARY KEY;
添加主键（表级约束）
ALTER TABLE 表名 add 【CONSTRAINT 约束名】 PRIMARY KEY(字段名);
删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;4、唯一
添加唯一（列级约束）
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 UNIQUE;
添加唯一（表级约束）
ALTER TABLE 表名 add 【CONSTRAINT 约束名】 UNIQUE(字段名);
删除唯一
ALTER TABLE 表名 DROP INDEX 索引名;5、外键
添加外键（表级约束）
ALTER TABLE 表名 add 【CONSTRAINT 约束名】 FOREIGN KEY(字段名) REFERENCES 主表(被引用列);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 约束名;
5.5、自增长列一、含义
自增长列又称为标识列，它可以不用手动的插入值，系统提供默认的序列值
二、特点
不用手动插入值，可以自动提供序列值，默认从1开始，步长为1，如果要更改起始值，第一次手动插入值，后续使用NULL，如果要更改步长，修改更改系统变量：SET auto_increment_increment = 值;一个表至多有一个自增长列自增长列只能支持数值型自增长列必须为一个key
三、演示
1、创建表时添加自增长列
CREATE TABLE 表名 (
字段名 字段类型 约束 AUTO_INCREMENT
) ;
2、修改表时添加或删除自增长列
添加自增长列
ALTER TABLE 表 MODIFY COLUMN 字段名 字段类型 约束 AUTO_INCREMENT;删除自增长列
ALTER TABLE 表 MODIFY COLUMN 字段名 字段类型 约束;
第六章 DCL语言关于授权的权限列表：
6.1、创建用户
CREATE USER 用户名@'IP地址' IDENTIFIED BY '密码';
注意：'IP地址'可以设置为localhost(代表本机)或者'%'(代表允许所有IP地址登录)
6.2、删除用户DROP USER 用户名@'IP地址';
注意：'IP地址'可以设置为localhost(代表本机)或者'%'(代表允许所有IP地址登录)
6.3、用户授权GRANT 权限1,权限2,...... ON 数据库名.* TO 用户名@'IP地址' IDENTIFIED BY '密码';
注意：所有的数据库就用*.*，所有的权限就用all或者all privileges
6.4、撤销授权REVOKE 权限1,权限2,...... ON 数据库名.* FROM 用户名@'IP地址' IDENTIFIED BY '密码';
注意：所有的数据库就用*.*，所有的权限就用all或者all privileges
6.5、刷新授权FLUSH PRIVILEGES;
6.6、查看授权SHOW GRANTS FOR 用户名@'IP地址';
注意：'IP地址'可以设置为localhost(代表本机)或者'%'(代表允许所有IP地址登录)
6.7、修改密码#修改密码
SET PASSWORD = PASSWORD('123456');
#登录授权
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
#刷新授权
FLUSH PRIVILEGES;
第七章 TCL语言
7.1、事务一、含义
一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行
二、特点（ACID）
原子性：一个事务是不可再分割的整体，要么都执行要么都不执行一致性：一个事务的执行不能破坏数据库数据的完整性和一致性隔离性：一个事务不受其它事务的干扰，多个事务是互相隔离的持久性：一个事务一旦提交了，则永久的持久化到本地
三、分类
隐式事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete显式事务：具有明显的开启和结束，例如以下格式：
1、开启事务
set autocommit=0;#关闭自动提交
start transaction;#开启事务机制2、编写一组逻辑sql语句
注意：sql语句支持的是insert、update、delete【设置回滚点，可选项】
savepoint 回滚点名;3、结束事务
提交：commit;
回滚：rollback;
回滚到指定的地方： rollback to 回滚点名;
7.2、事务并发（读问题）一、事物的并发问题如何发生？
多个事务同时操作同一个数据库的相同数据时
二、事务的并发问题都有哪些？
脏读：一个事务读到了另一个事务还未提交的update数据，导致多次查询的结果不一样不可重复读：一个事务读到了另一个事务已经提交的update数据，导致多次查询结果不一致幻读：一个事务读到了另一个事务已经提交的insert数据，导致多次查询的结果不一样
三、事物的并发问题如何解决？
通过设置隔离级别来解决并发问题
四、隔离级别
隔离级别 隔离描述 脏读 不可重复读 幻读 READ UNCOMMITTED读未提交×××READ COMMITTED读已提交√××REPEATABLE READ可重复读√√×SERIALIZABLE串行化√√√
五、注意问题
mysql 中默认第三个隔离级别 REPEATABLE READ
oracle中默认第二个隔离级别 READ COMMITTED
查看隔离级别 SELECT @@tx_isolation;
设置隔离级别 SET SESSION|GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;
7.3、丢失更新（写问题）
一、定义
在事务的隔离级别内容中，能够了解到两个不同的事务在并发的时候可能会发生数据的影响。细心的话可以发现事务隔离级别章节中，脏读、不可重复读、幻读三个问题都是由事务A对数据进行修改、增加，事务B总是在做读操作。如果两事务都在对数据进行修改则会导致另外的问题：丢失更新。
二、解决
悲观锁：认为两个事务更新操作一定会发生丢失更新
解决：通过在语句后边添加for update来实现行级上锁，所以又称为“行级锁”，例如：select * from t_account t wheret.id=‘1’ for update; 乐观锁：认为事务不一定会产生丢失更新，让事务进行并发修改，不对事务进行锁定
解决：由程序员自己解决，可以通过给数据表添加自增的version字段或时间戳timestamp，进行数据修改时，数据库会检测version字段或者时间戳是否与原来的一致，若不一致，抛出异常或者重新查询三、注意
对于账户交易建议直接使用悲观锁，数据库的性能很高，并发度不是很高的场景两者性能没有太大差别。如果是交易减库存的操作可以考虑乐观锁，保证并发度。
第八章 高级部分
8.1、索引一、含义
索引（index）是帮助MySQL高效获取数据的一种有序的数据结构
二、特点
优势：
类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗 劣势：
实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息三、语法
1、创建
CREATE 【 UNIQUE|FULLTEXT|SPATIAL 】 INDEX 索引名称 ON 表名(字段列表);
2、修改
先删除，在创建
3、删除
DROP INDEX 索引名称 ON 表名;
4、查看
SHOW INDEX FROM 表名;
5、alter命令
#该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
ALTER TABLE 表名 ADD PRIMARY KEY(字段列表);#这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
ALTER TABLE 表名 ADD UNIQUE 索引名称(字段列表);#添加普通索引，索引值可以出现多次。
ALTER TABLE 表名 ADD INDEX 索引名称(字段列表);#该语句指定了索引为FULLTEXT，用于全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名称(字段列表);
四、注意
索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引：
索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价对查询频次较高，且数据量比较大的表建立索引使用唯一索引，区分度越高，使用索引的效率越高使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率使用组合索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。例如：CREATE INDEX idx_name_email_status ON tb_seller(name,email,status); 就相当于对name 创建索引；对name , email 创建了索引；对name , email, status 创建了索引
8.2、视图
一、含义
MySQL在5.1以后推出了视图（VIEW），本身是一个虚拟表，它的数据来自于表，通过执行时动态生成
二、特点
简化sql语句提高了sql的重用性保护基表的数据，提高了安全性
三、语法
1、创建
CREATE VIEW 视图名
AS
查询语句;
2、修改
#方式一：
CREATE OR REPLACE VIEW 视图名
AS
查询语句;#方式二：
ALTER VIEW 视图名
AS
查询语句;
3、删除
DROP VIEW 视图1,视图2,...;
4、查看
#方式一：
DESC 视图名;#方式二：
SHOW CREATE VIEW 视图名;
四、注意
视图一般用于查询的，而不是更新的，所以具备以下特点的视图都不允许更新：
包含分组函数、group by、distinct、having、union、join常量视图where后的子查询用到了from中的表用到了不可更新的视图
8.3、存储过程
8.3.1、语法一、含义
存储过程，类似于Java中的方法，它是一组预先编译好的SQL语句的集合，理解成批处理语句
二、特点
简化sql语句提高了sql的重用性减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
三、语法
1、创建
#标准格式如下：
DELIMITER $
CREATE PROCEDURE 存储过程名(参数模式 参数名 参数类型,...)
BEGIN
存储过程体（一组合法的SQL语句）
END $
DELIMITER ;#参数模式in：参数类型是输入的
#参数模式out：参数类型是输出的
#参数模式inout：参数类型既可以输入也可以输出#调用in模式的参数： CALL sp1('Hello,World');
#调用out模式的参数： SET @name; CALL sp1(@name); SELECT @name;
#调用inout模式的参数： SET @name=值; CALL sp1(@name); SELECT @name;
2、修改
先删除，在创建
3、删除
DROP PROCEDURE 存储过程名;
4、查看
SHOW CREATE PROCEDURE 存储过程名;
8.3.2、变量一、分类
系统变量
全局变量会话变量 自定义变量
用户变量局部变量二、语法
1、全局变量
描述：服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效，但不能跨重启
1、查看所有系统变量
SHOW GLOBAL VARIABLES;2、查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';3、查看指定的系统变量的值
SELECT @@global 系统变量名;4、为某个系统变量赋值
方式一： SET GLOBAL 系统变量名=值;
方式二： SET @@global 系统变量名=值;
2、会话变量
描述：服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）
1、查看所有系统变量
SHOW 【 SESSION 】 VARIABLES;2、查看满足条件的部分系统变量
SHOW 【 SESSION 】 VARIABLES LIKE '%char%';3、查看指定的系统变量的值
SELECT 【 SESSION 】系统变量名;4、为某个系统变量赋值
SET 【 SESSION 】系统变量名=值;
3、用户变量
描述：针对于当前连接（会话）生效
位置：可以在begin end里面，也可以放在外面
1、声明并赋值
SET @变量名=值; 或
SET @变量名:=值; 或
SELECT @变量名:=值;2、更新值
方式一：
SET @变量名=值; 或
SET @变量名:=值; 或
SELECT @变量名:=值;
方式二：
SELECT xxx INTO @变量名 FROM 表;3、查看值
SELECT @变量名;
4、局部变量
描述：作用域：仅仅在定义它的begin end中有效
位置：只能放在begin end中，而且只能放在第一句1、声明并赋值
DECLARE 变量名 类型 【 DEFAULT 值 】;2、更新值
方式一：
SET 变量名=值; 或
SET 变量名:=值; 或
SELECT @变量名:=值;
方式二：
SELECT xxx INTO 变量名 FROM 表;3、查看值
SELECT 变量名;
8.3.3、分支结构
8.3.3.1、if结构一、语法
注意：只能应用在begin end中
IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
ELSEIF 条件3 THEN 语句3;
....
【 ELSE 语句n; 】
END IF;
二、演示
#创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
DELIMITER $
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
IF score>90 THEN SET ch='A';
ELSEIF score>80 THEN SET ch='B';
ELSEIF score>60 THEN SET ch='C';
ELSE SET ch='D';
END IF;
RETURN ch;
END $
DELIMITER ;#函数调用
SELECT test_if(87)$
8.3.3.2、case结构一、语法
注意：只能应用在begin end中
语法1：
CASE 表达式或字段
WHEN 值1 THEN 语句1;
WHEN 值2 THEN 语句2;
...
【 ELSE 语句n; 】
END 【 CASE 】;语法2：
CASE
WHEN 条件1 THEN 语句1;
WHEN 条件2 THEN 语句2;
...
【 ELSE 语句n; 】
END 【 CASE 】;
二、演示
#创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
DELIMITER $
CREATE FUNCTION test_case (score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A' ;CASE
WHEN score>90 THEN SET ch='A';
WHEN score>80 THEN SET ch='B';
WHEN score>60 THEN SET ch='C';
ELSE SET ch='D';
END CASE;RETURN ch;
END $
DELIMITER ;#函数调用
SELECT test_case(56);
8.3.4、循环结构一、分类
二、演示
8.3.4.1、while结构
#批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
#删除过程
DROP PROCEDURE IF EXISTS test_while;#定义过程
DELIMITER $
CREATE PROCEDURE test_while(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
#LEAVE代表Java中的break关键字；ITERATE代表Java中的continue关键字
IF i>20 THEN LEAVE a;
END IF;
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
SET i=i+1;
END WHILE a;
END $
DELIMITER ;#调用过程
CALL test_while(100);
8.3.4.2、repeat结构#批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
#删除过程
DROP PROCEDURE IF EXISTS test_repeat;#定义过程
DELIMITER $
CREATE PROCEDURE test_repeat(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:REPEAT
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
SET i=i+1;
UNTIL i>20
END REPEAT a;
END $
DELIMITER ;#调用过程
CALL test_repeat(100);
8.3.4.3、loop结构#批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
#删除过程
DROP PROCEDURE IF EXISTS test_loop;#定义过程
DELIMITER $
CREATE PROCEDURE test_loop(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:LOOP
#LEAVE代表Java中的break关键字；ITERATE代表Java中的continue关键字
IF i>20 THEN LEAVE a;
END IF;
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
SET i=i+1;
END LOOP a;
END $
DELIMITER ;#调用过程
CALL test_loop(100);
8.3.5、游标一、含义
游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理
二、语法
1、声明游标
DECLARE 游标名称 CURSOR FOR select结果集;
2、打开游标
OPEN 游标名称;
3、获取游标
FETCH 游标名称 INTO 变量列表（需要跟select结果集的字段对应）;
4、关闭游标
CLOSE 游标名称;
三、演示
建数据表以用来演示
CREATE TABLE emp (
id INT (11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR (50) NOT NULL COMMENT '姓名',
age INT (11) COMMENT '年龄',
salary INT (11) COMMENT '薪水',
PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 ;INSERT INTO emp(id,NAME,age,salary)
VALUES
(NULL,'金毛狮王',55,3800),
(NULL,'白眉鹰王',60,4000),
(NULL,'青翼蝠王',38,2800),
(NULL,'紫衫龙王',42,1800);
循环获取该表的内容
DELIMITER $
CREATE PROCEDURE getAllRowsForEmpTable()
BEGIN
DECLARE e_id INT(11);
DECLARE e_name VARCHAR(50);
DECLARE e_age INT(11);
DECLARE e_salary INT(11);
DECLARE has_data INT DEFAULT 1;DECLARE emp_result CURSOR FOR SELECT * FROM emp;
DECLARE EXIT HANDLER FOR NOT FOUND SET has_data=0;OPEN emp_result;
REPEAT
FETCH emp_result INTO e_id,e_name,e_age,e_salary;
SELECT CONCAT('id=',e_id , ', name=',e_name, ', age=', e_age, ', salary=',e_salary) as emp_info;
UNTIL has_data = 0
END REPEAT;CLOSE emp_result;
END$
DELIMITER ;
打开命令行调用查看
CALL getAllRowsForEmpTable();
8.4、函数一、含义
其实函数就是一个有返回值的过程
二、语法
1、创建
DELIMITER $
CREATE FUNCTION 函数名(参数名 参数类型)
RETURNS 返回类型
BEGIN
函数过程体（一组合法的SQL语句）
END $
DELIMITER ;
2、修改
先删除，在创建
3、删除
DROP FUNCTION 函数名；
4、查看
SHOW CREATE FUNCTION 函数名;
5、调用
SELECT 函数名(实参列表);
三、注意
函数体中肯定需要有return语句存储过程和函数的区别在于函数必须有返回值，而存储过程没有
8.5、触发器
一、含义
MySQL在5.0以后推出了触发器（TRIGGER），触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录 、数据校验等操作。使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在MySQL触发器还只支持行级触发，不支持语句级触发
二、分类
触发器类型 NEW 和 OLD的使用 INSERT 型触发器NEW 表示将要或者已经新增的数据UPDATE 型触发器OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据DELETE 型触发器OLD 表示将要或者已经删除的数据
三、语法
1、创建
DELIMITER $
CREATE TRIGGER 触发器名称
BEFORE/AFTER INSERT/UPDATE/DELETE
ON 表名称
【 FOR EACH ROW 】--行级触发器
BEGIN
触发器过程体（一组合法的SQL语句）
END $
DELIMITER ;
2、更新
先删除，在创建
3、删除
DROP TRIGGER 触发器名称;
4、查看
SHOW TRIGGERS;
四、演示
创建数据表以用来演示
CREATE TABLE emp (
id INT (11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR (50) NOT NULL COMMENT '姓名',
age INT (11) COMMENT '年龄',
salary INT (11) COMMENT '薪水',
PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 ;
INSERT INTO emp(id,NAME,age,salary)
VALUES
(NULL,'金毛狮王',55,3800),
(NULL,'白眉鹰王',60,4000),
(NULL,'青翼蝠王',38,2800),
(NULL,'紫衫龙王',42,1800);CREATE TABLE emp_logs (
id INT (11) NOT NULL AUTO_INCREMENT,
operation VARCHAR (20) NOT NULL COMMENT '操作类型, insert/update/delete',
operate_time DATETIME NOT NULL COMMENT '操作时间',
operate_id INT (11) NOT NULL COMMENT '操作表的ID',
operate_params VARCHAR (500) COMMENT '操作参数',
PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 ;
编写触发器进行测试
创建 insert 型触发器，完成插入数据时的日志记录：
DELIMITER $
CREATE TRIGGER emp_logs_insert_trigger
AFTER INSERT
ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp_logs (id,operation,operate_time,operate_id,operate_params) VALUES(NULL,'insert',NOW(),new.id,CONCAT('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));
END $
DELIMITER ;
创建 update 型触发器，完成更新数据时的日志记录：
DELIMITER $
CREATE TRIGGER emp_logs_update_trigger
AFTER UPDATE
ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp_logs (id,operation,operate_time,operate_id,operate_params) VALUES(NULL,'update',NOW(),new.id,CONCAT('修改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')'));
END $
DELIMITER ;
创建delete 行的触发器 , 完成删除数据时的日志记录：
DELIMITER $
CREATE TRIGGER emp_logs_delete_trigger
AFTER DELETE
ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp_logs (id,operation,operate_time,operate_id,operate_params) VALUES(NULL,'delete',NOW(),old.id,CONCAT('删除前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));
END $
DELIMITER ;
依次调用以下的语句，查看emp_logs是否有触发器保存的日志记录
INSERT INTO emp(id,NAME,age,salary) VALUES(NULL, '光明左使',30,3500);UPDATE emp SET age = 39 WHERE id = 3;
DELETE FROM emp WHERE id = 5;
8.6、事件一、含义
MySQL在5.1以后推出了事件调度器（Event Scheduler），与triggers的事件触发不同，event类似与linux crontab计划任务，用于时间触发
二、特点
事件是一组SQL集合，简单说就是mysql中的定时器，时间到了就执行事件由一个特定的线程来管理的，也就是所谓的事件调度器，但是事件不能直接调用，通过单独或调用存储过程使用，在某一特定的时间点，触发相关的SQL语句或存储过程
三、语法
1、创建
MySQL事件调度器event_scheduler负责调用事件，它默认是关闭的。这个调度器不断地监视一个事件是否要调用， 要创建事件，必须打开调度器。
#开启事件调度器（任意一个都可以）
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;#关闭事件调度器（任意一个都可以）
SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;
DELIMITER $
CREATE EVENT 事件名称
ON SCHEDULE 执行时间和频率
-- 执行时间和频率有两种形式AT和EVERY
-- 使用 AT 关键字只会执行一次，格式如下：
-- AT CURRENT_TIMESTAMP 或者 'YYYY-MM-DD HH:MM.SS'
-- 使用 EVERY 关键字指定时间间隔，格式如下：
-- EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]-- 可选项，使用 STARTS 关键字指定开始时间，格式如下：
-- STARTS CURRENT_TIMESTAMP 或者 'YYYY-MM-DD HH:MM.SS'
-- 可选项，使用 ENDS 关键字指定结束时间，格式如下：
-- ENDS CURRENT_TIMESTAMP 或者 'YYYY-MM-DD HH:MM.SS'-- [ON COMPLETION [NOT] PRESERVE]
-- 可选项，设定event的生命
-- ON COMPLETION NOT PRESERVE ：即计划任务执行完毕后自动drop该事件（默认）
-- ON COMPLETION PRESERVE ：即计划任务执行完毕后不会drop该事件-- [ENABLE | DISABLE]
-- 可选项，设定event的状态
-- ENABLE ：表示系统尝试执行这个事件（默认）
-- DISABLE ：表示系统尝试关闭这个事件-- [COMMENT 'comment']
-- 可选项，设定event的备注
DO
BEGIN
/* 在这里写SQL语句或者调用存储过程 */
END$
DELIMITER ;
2、修改
先删除，在创建
3、删除
DROP EVENT 事件名称;
4、查看
SHOW EVENTS;
5、调用
#启用事件
ALTER EVENT 事件名称 ENABLE;#禁用事件
ALTER EVENT 事件名称 DISABLE;
四、注意
默认创建事件存储在当前库中，也可显示指定事件创建在哪个库中通过show events只能查看当前库中创建的事件事件执行完即释放，如立即执行事件，执行完后，事件便自动删除，多次调用事件或等待执行事件可以查看到如果两个事件需要在同一时刻调用，mysql会确定调用他们的顺序，如果要指定顺序，需要确保一个事件至少在另一个事件1秒后执行对于递归调度的事件，结束日期不能在开始日期之前select可以包含在一个事件中，然而他的结果消失了，就好像没执行过
第九章 数据备份与恢复
9.1、数据导出1、导出某张数据表
#格式：mysqldump -u账户 -p密码 数据库名 数据表名 > 文件保存路径
mysqldump -uroot -p123456 test emp > D:/emp.sql
2、导出整个数据库
#格式：mysqldump -u账户 -p密码 数据库名 > 文件保存路径
mysqldump -uroot -p123456 test > D:/test.sql
9.2、数据导入1、导入某张数据表
#格式：mysql -u账户 -p密码
# use 数据库名;
# source 文件保存路径
mysql -uroot -p123456
>use test;
>source D:/emp.sql
2、导入整个数据库
#格式：mysql -u账户 -p密码 < 文件保存路径
mysql -uroot -p123456 < test.sql
第十章 安装MySQL单实例
10.1、准备虚拟机虚拟机配置：全新系统，桌面版
虚拟机地址：进入系统，可上网
连接的工具：自行百度，在下载
SecureCRT：7.0.0
10.2、删除自带库
[root@caochenlei ~]# rpm -qa | grep mysql
mysql-libs-5.1.73-8.el6_8.x86_64
[root@caochenlei ~]# rpm -e mysql-libs-5.1.73-8.el6_8.x86_64 --nodeps
10.3、安装依赖包[root@caochenlei ~]# yum -y install numactl perl libaio wget
10.4、下载服务端下载：
[root@caochenlei ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-server-5.6.49-1.el6.x86_64.rpm
查看：
[root@caochenlei ~]# ll MySQL-server-5.6.49-1.el6.x86_64.rpm
-rw-r--r--. 1 root root 65467136 6月 3 13:36 MySQL-server-5.6.49-1.el6.x86_64.rpm
10.5、安装服务端[root@caochenlei ~]# rpm -ivh MySQL-server-5.6.49-1.el6.x86_64.rpm
10.6、下载客户端下载：
[root@caochenlei ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-client-5.6.49-1.el6.x86_64.rpm
查看：
[root@caochenlei ~]# ll MySQL-client-5.6.49-1.el6.x86_64.rpm
-rw-r--r--. 1 root root 21584380 6月 3 13:36 MySQL-client-5.6.49-1.el6.x86_64.rpm
10.7、安装客户端[root@caochenlei ~]# rpm -ivh MySQL-client-5.6.49-1.el6.x86_64.rpm
10.8、启动数据库服务[root@caochenlei ~]# service mysql start
Starting MySQL.Logging to '/var/lib/mysql/caochenlei.err'.
.. [确定]
10.9、查看初始化密码[root@caochenlei ~]# cat /root/.mysql_secret
# The random password set for the root user at Mon Aug 24 23:06:53 2020 (local time): E4qT_4CJ9YNf0daG
10.10、修改初始化密码登录：
[root@caochenlei ~]# mysql -uroot -pE4qT_4CJ9YNf0daG
修改：
mysql> set password = password('123456');
Query OK, 0 rows affected (0.01 sec)
10.11、远程的授权授权：
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
刷新：
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
退出：
mysql> exit
Bye
10.12、开放防火墙[root@caochenlei ~]# /sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
[root@caochenlei ~]# /etc/rc.d/init.d/iptables save
iptables：将防火墙规则保存到 /etc/sysconfig/iptables： [确定]
10.13、添加自启动[root@caochenlei ~]# chkconfig --add mysql
[root@caochenlei ~]# chkconfig mysql on
10.14、测试连接
10.15、关闭系统
[root@caochenlei ~]# poweroffBroadcast message from root@caochenlei
(/dev/pts/1) at 23:24 ...The system is going down for power off NOW!
第十一章 安装MySQL主从复制
11.1、准备虚拟机复制CentOS 6 64 bit-1 > CentOS 6 64 bit-2
启动CentOS 6 64 bit-1：
启动CentOS 6 64 bit-2：选择“我已复制该虚拟机”
11.2、主从复制介绍
主从复制允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）。
11.3、主从复制架构
实现目标：一主一从
11.4、主节点配置
编辑：
注意：192.168.206.128为主节点
[root@caochenlei ~]# vi /usr/my.cnf
添加：
注意：在[mysqld]下一行添加以下信息
#日志名称
log-bin=mysql-bin
#日志格式：row、statement、mixed
binlog-format=row
#服务器ID标识
server-id=1
#刷写事务日志
innodb_flush_log_at_trx_commit=1
#执行写入1次同步1次
sync_binlog=1
#及时更新master_info
sync_master_info=1
重启：
[root@caochenlei ~]# service mysql restart
Shutting down MySQL... [确定]
Starting MySQL..... [确定]
连接：
[root@caochenlei ~]# mysql -uroot -p123456
授权：
mysql> grant replication slave on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected (0.03 sec)
刷新：
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
查看：
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 397
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
11.5、从节点配置编辑：
注意：192.168.206.129为从节点
[root@caochenlei ~]# vi /usr/my.cnf
添加：
注意：在[mysqld]下一行添加以下信息
#日志名称
log-bin=mysql-bin
#日志格式：row、statement、mixed
binlog-format=row
#服务器ID标识
server-id=2
#是否只读
read_only=1
停止：
[root@caochenlei ~]# service mysql stop
Shutting down MySQL.. [确定]
删除：
注意：这里不能直接重启mysql是因为它们的auto.cnf中的server-uuid是一样的，在进行接下来的配置的时候会失败，所以要删除从库的server-uuid，让它在启动的时候自动生成一个全新的server-uuid
[root@caochenlei ~]# rm -f /var/lib/mysql/auto.cnf
启动：
[root@caochenlei ~]# service mysql start
Starting MySQL... [确定]
连接：
[root@caochenlei ~]# mysql -uroot -p123456
执行：
注意：MASTER_LOG_FILE=show master status的File、MASTER_LOG_POS=show master status的Position
CHANGE MASTER TO
MASTER_HOST='192.168.206.128',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=397;
开启：
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
查看：
注意：重点检查Master_Log_File、Read_Master_Log_Pos是否和主节点的信息一致，Slave_IO_Running、Slave_SQL_Running是否为YES
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.206.128
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 397
Relay_Log_File: caochenlei-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 397
Relay_Log_Space: 461
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: d7200ffb-e698-11ea-87e0-000c29e99cce
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.02 sec)
11.6、连接测试测试连接：192.168.206.128（master）
创建数据库：
CREATE DATABASE `mytest` CHARACTER SET utf8 ;
创建数据表：
CREATE TABLE `mytest`.`myuser` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`username` VARCHAR (20) NOT NULL,
`password` VARCHAR (20) NOT NULL,
PRIMARY KEY (`id`)
) ;
添加数据集：
INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('1', 'zhangsan', '123456');
INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('2', 'lisi', '123456');
INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('3', 'wangwu', '123456');
查看数据集：
use mytest;
SELECT * FROM myuser;
连接测试：192.168.206.129（slave）
查看数据集：
USE mytest;
