本文最后更新于：1 小时前

# MySQL 学习记录

# 服务的启动与关闭

启动：net start 服务名
关闭：net stop 服务名

# 服务端的登录和退出

方法一：

登录：打开 MySQL 8.0 Command Line Client，输入密码即可。这种方式只能是 root 用户使用，不推荐

退出：exit
方法二：

登录：通过 windows cmd 输入指令 mysql -h 服务器 ip -P 端口号 -u 用户 -p 密码

例如： mysql -h localhost -P 3306 -u root -p 123456

注意：-p 和空格之间不能有空格 如果是本机登录，则 - h，-P 可以省略

退出：exit

# 常见命令

查看当前所有数据库：show databases;
打开指定的库：use 库名；
查看当前库所有表：show tables;
查看其他库所有表：show tables from 库名；
创建表：create table 表名 (

列名列类型，

列名列类型，

…

);
查看表结构：desc 表名；
查看服务器版本
- 方式一：
  
  登录到 mysql 服务端后，select version ();
- 方式二：
  
  windows cmd 输入 ==mysql --version 或 mysql -V==

# MySQL 的语法规范

不区分大小写，但建议关键字大写，表名和列名小写
每条命令最好用分号结尾
每条命令根据需要，可以进行缩进或换行
注释

单行注释：# 注释文字

单行注释：-- 注释文字（注意‘–’后必须有空格）

多行注释：/* 注释文字 */

# DQL 语言

# 基础查询

语法：SELECT 查询列表 FROM 表名；
查询列表可以是：表中的字段
查询的结果是一个虚拟的表格
字符型和日期型的常量值必须用引号引起来

# 举例

# 查询单个字段
SELECT last_name 
FROM employees;
# 查询多个字段
SELECT last_name, salary, email 
FROM employees;
# 查询所有字段
SELECT * 
FROM employees;
# 查询常量
SELECT 100;
# 查询表达式
SELECT 100 * 98;
# 查询函数
SELECT VERSION();
# 为字段起别名
SELECT last_name AS 姓, first_name AS 名 
FROM employees; # 方式一
SELECT last_name 姓, first_name 名 
FROM employees; # 方式二
# 特殊情况：查询salary，显示结果为out put
# 有特殊符号的，建议加上双引号
SELECT salary AS "out put" 
FROM employees;
# 去重
SELECT DISTINCT department_id 
FROM employees;
/* 
	'+'号的作用，在MySQL中，'+'只有加法运算的作用
	SELECT '123' + 90:如果出现字符型，试图转换成数值型，转换成功则继续做加法；转换失败，就会把字符型看成0
	SELECT 'John' + 90 结果是90
	SELECT NULL + 90 只要出现NULL，结果一定是NULL
*/
# CONCAT()函数
# 案例：查询员工姓名连接成一个字段，并显示为"姓名"
SELECT CONCAT(last_name, first_name) AS 姓名 
FROM employees;
# IFNULL函数
SELECT IFNULL(exp1, exp2) # 如果exp1为空，就返回exp2；否则返回exp1

# 条件查询

语法：SELECT 查询列表 FROM 表名 WHERE 筛选条件；
筛选条件包括：
- 条件表达式： > <= != <> >= <=
- 逻辑表达式：&&，||，!，and，or，not
- 模糊查询：like，between and，not between and，in，is null,，is not null

# 举例

/*
********************************************
				条件表达式
********************************************			
*/
# 查询员工工资大于10000的员工信息
SELECT * 
FROM employees 
WHERE salary > 10000;
/*
********************************************
				逻辑表达式
********************************************			
*/
# 查询工资在10000到20000之间的员工信息
SELECT * 
FROM employees 
WHERE salary >= 10000 
AND salary <= 20000;

/*
********************************************
				模糊查询
********************************************			
*/
/*
	模糊查询：LIKE，一般和通配符配合使用。不但可以查字符型，也可以查数值型
	'%'代表0或n个字符
	'_'代表一个字符
	\_代表转义字符'_'
*/
# 查询员工名中包含'a'的员工信息
SELECT * 
FROM employees 
WHERE first_name LIKE '%a%';
# 查询员工名第三个字符为'a'，第五个字符为'e'的员工信息
SELECT * 
FROM employees 
WHERE first_name like '__a_e%'
# 查询员工名第三个字符为'_'的员工信息
SELECT * 
FROM employees 
WHERE first_name like '_\_%';
SELECT * 
FROM employees 
WHERE first_name like '_$_%' ESCAPE '$'; # 这里表示'$'是转义字符，推荐使用这种
/*
	BETWEEN a AND b 的区间是闭区间[a,b]
	b必须>=a，否则筛选出来的结果是0条
*/
# 查询工资在10000到20000之间的员工信息
SELECT * 
FROM employees 
WHERE salary BETWEEN 10000 AND 20000;
/*
	IN列表的值类型必须一致或兼容
	不支持通配符
*/
# 查询员工的工种编号是IT_PROG, AD_VP, AD_PRES中的一个的员工信息
SELECT * 
FROM employees 
WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES');
/*
	'='不能用于判断NULL值，要使用IS NULL或IS NOT NULL
*/
# 查询没有奖金的员工信息
SELECT * 
FROM employees 
WHERE commission_pac IS NULL;# 必须写成is null，不能写成 = null
# 查询有奖金的员工信息
SELECT * 
FROM employees 
WHERE commission_pac IS NOT NULL;# 必须写成is not null，不能写成 != null
# 安全等于'<=>'
# IS NULL 可以写成<=> NULL
# 查询工资为12000的员工信息
SELECT * 
FROM employees 
WHERE salary <=> 12000;
/*
	IS NULL只能判断NULL值
	<=> 既可以判断NULL值，也可以判断普通的数值，但可读性较低
*/
# ISNULL(expr)如果expr是NULL，就返回1，否则返回0
SELECT ISNULL(commission_pac) 
FROM employees;

# 一个坑

问：SELECT * FROM employees 和 SELECT * FROM employees WHERE first_name LIKE ‘%%’ AND last_name LIKE '%%' 效果一样吗？

答：不一样，如果 first_name 或 last_name 中包含 NULL 值，则不一样。因为 NULL LIKE ’%%‘是错的。

# 排序查询

语法：SELECT 查询列表 FROM 表名 [WHERE 子句] ORDER BY 排序列表 [asc | desc]；
默认是从小到大排序
如果排序列表有多个，则优先按写在前面的排序列表排序。对每个排序列表，可以分别指定是升序或者降序

# 举例

# 查询员工信息，要求工资从高到低排序
SELECT * 
FROM employees 
ORDER BY salary ASC;
# 查询员工信息，要求工资从低到高排序
SELECT * 
FROM employees 
ORDER BY salary DESC;
# 查询部门编号大于等于90的员工信息，要求按入职时间从小到大排序
SELECT * 
FROM employees 
WHERE department_id >= 90 
ORDER BY hire_date ASC;
# 按年薪的高低显示员工信息（按表达式排序）
SELECT * 
FROM employees 
ORDER BY salary * 12 * (1 + IFNULL(commision_pac, 0)) ASC;
# 按姓的长度显示员工信息（按函数排序）
SELECT * 
FROM employees 
ORDER BY LENGTH(last_name) ASC;
# 查询员工信息，按照工资排序，若工资相同，则按员工编号排序（多个字段排序）
SELECT * 
FROM employees 
ORDER BY salary ASC, employee_id DESC;

# 常见函数

语法：SELECT 函数名 () [FROM 表]；

分类：

单行函数
分组函数：用作统计用，输入多个数据，输出一个数据

# 字符函数

#1、LENGTH()：用于获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha'); # utf-8中一个汉字占3个字节
# 2、CONCAT（）：拼接字符串
SELECT CONCAT(first_name, '_', last_name) AS 姓名 FROM employees;
#3、UPPER(),LOWER():将字符转成大写、小写
SELECT CONCAT(UPPER(first_name), LOWER(last_name)) AS 姓名 FROM employees;
# 4、SUBSTR,SUBSTRING
# 注意：SQL语言中，索引从1开始！！！！！！
SELECT SUBSTR('12345678', 6) # 截取从索引位置为6开始的字串
SELECT SUBSTR('12345678', 1, 3) # 截取从索引位置为1，字符长度为3的字串
# 5、INSTR(expr1, expr2)：判断expr2在expr1首次出现的起始索引位置，若不存在，则返回0
SELECT INSTR('12341234','123'); # 输出是1
# 6、TRIM()
SELECT TRIM('   1234   '); # 去除字符前后的空格
SELECT TRIM('a' FROM 'aaaa1234aaaaaaa'); # 去除字符前后的'a'
SELECT TRIM('aa' FROM 'aaaaa1234aaaaaaa'); # 以'aa'为单位去除字符前后的'aa'，这里的输出是'a1234a'
# 7、LPAD()：用指定的字符实现左填充到指定长度
SELECT LPAD('ABC', 10, '*'); # 输出是'*******ABC'
SELECT LPAD('ABC', 2, '*'); # 输出是'AB'
# 8、RPAD()：用指定的字符实现左填充到指定长度
# 9、REPLACE():替换
SELECT REPLACE('ABC', 'A', 'B') # 把'ABC'中的所有'A'换成'C'

# 数学函数

# 1、round(x)：四舍五入
SELECT ROUND(1.65); # 2
SELECT ROUND(-1.65); # -2
SELECT ROUND(-1.45); # -1
# 2、ROUND(x, y)：照第y位小数四舍五入
# 3、CEIL(X)：返回大于等于x的最小整数
SELECT CEIL(1.01); # 2
SELECT CEIL(1.00); # 1
SELECT CEIL(-1.01); # -1
# 4、FLOOR(x)：返回小于等于x的最大整数
SELECT CEIL(1.01); # 1
SELECT CEIL(1.00); # 1
SELECT CEIL(-1.01); # -2
# 5、TRUNCATE(x, y)：让x保留y位小数
SELECT TRUNCATE(1.6999, 1); # 1.6
# 6、MOD(a,b)：取余  = a - a / b * b
SELECT MOD(10, 3); # 1
SELECT MOD(-10, -3); # -1

# 日期函数

# 1、NOW():返回当前系统日期 + 时间
SELECT NOW();
# 2、CURDATE()：返回当年系统日期，不包含时间
SELECT CURDATE(); # 2021-11-08
# 3、CURTIME()：获取当前时间，不包含日期
SELECT CURTIME(); # 12:17:43
# 4、获取指定部分：年，月，日，时，分，秒
SELECT YEAR(NOW());
SELECT YEAR('2021-11-08'); # 2021
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
# 5、STR_TO_DATE():将日期格式的字符串转换成指定格式的日期
SELECT STR_TO_DATE('1998-3-02','%Y-%c-%d') 
# 6、DATA_FORMATE()：将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y-%m-%d');
# DATEDIFF(exp1, exp2)：求两个日期时间之差

# 流程控制函数

# IF()：实现if-else的效果
SELECT IF(expr1, expr2, expr3); # 若expr1为真，返回expr2，否则expr3
# CASE函数，使用一：类似于Java中switch case的效果
/*
	格式： case 要判断的字段或表达式
	when 常量1 then 要显示的值或语句1;（值的时候不加';'，语句的时候加';'）
	when 常量2 then 要显示的值或语句2;
	...
	else 要显示的值或语句2;
	end [as new_name]
*/
SELECT salary, department_id,
CASE department_id 
WHEN 30 THEN salary * 1.1
WHEN 40 THEN salary * 1.2
ELSE salary * 1.2
END AS '新工资' 
FROM employees
# CASE函数，使用二：类似于Java中的多重if
/*
	CASE
	WHEN 条件1 THEN 要显示的值或语句1;（值的时候不加';'，语句的时候加';'）
	WHEN 条件2 THEN 要显示的值1或语句2;
	...
	ELSE 要显示的值1或语句2;
	END
*/
SELECT salary,
CASE
WHEN salary > 12000 THEN 'A'
WHEN salary > 10000 THEN 'B'
ELSE 'C'
END AS rank
FROM employees

# 其他函数

1
2
3

# VERSION()：版本号
# DATABASE()：查看当前数据库
# USER()：当前用户

# 聚合函数

SUM、AVG、MAX、MIN、COUNT

sum，avg 一般用于处理数值型
max、min、count 可以处理任何类型
以上聚合函数都会忽略 NULL 值，求平均时，分母也是忽略 NULL 的
可以和 distinct 搭配
和聚集函数一同查询的字段只能是 GROUP BY 之后的字段

# SUM
SELECT SUM(salary) 
FROM employees; # 求工资之和
# 配合使用
SELECT SUM(salary), MAX(salary) 
FROM employees; # 求总和以及最大值
# COUNT() 计算不为NULL的数量 
# 和DISTINCT搭配使用，去重
SELECT SUM(DISTINCT salary), SUM(salary) 
FROM employees;
SELECT COUNT(DISTINCT salary) 
FROM employees;
# COUNT()的详细介绍
SELECT COUNT (*) 
FROM employees; # 统计总行数（对于每一行，只要有一个字段不为NULL，就加1）
SELECT COUNT(常量值) 
FROM employees; # 统计总行数 
/*
	效率对比：
	MYISAM存储引擎下，COUNT(*)效率高
	INNODB存储引擎下，COUNT(*)和COUNT(1)效率差不多，比COUNT(常量值)要高一些
	推荐使用COUNT(*)
*/

# 分组查询

语法

SELECT 查询列表 FROM 查询列表 [WHERE 字句] [ GROUP BY 子句 [ HAVING 子句 ] ] [ ORDER BY 子句 ]
要求查询列表是分组函数和 GROUP BY 后出现的字段
WHERE 子句的执行顺序先于 GROUP BY，HAVING 子句的执行顺序后于 GROUP BY
WHERE 子句的数据源是原始表，而 HAVING 子句的数据源是 GROUP BY 的筛选结果，所以在 WHERE 子句不能直接使用聚合函数
GROUP BY 子句支持单字段分组，也支持多字段分组（多个字段之间用逗号隔开，和顺序无关），也支持函数或表达式。

# 查询每个工种的平均工资
SELECT AVG(salary), job_id 
FROM employees 
GROUP BY job_id;
# 查询每个领导手下员工中有奖金的员工的最高工工资
SELECT MAX(salary), manager_id 
FROM employees 
WHERE commission_pac IS NOT NULL 
GROUP BY manager_id;
# 复杂的分组查询，分组后的筛选
# 查询那个部门的员工人数 > 2
SELECT COUNT(*), department_id 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*) > 2;
# 查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资
SELECT MAX(salary), job_id 
FROM employees 
WHERE commission_pac IS NOT NULL
GROUP BY job_id HAVING MAX(salary) > 12000;
# 查询领导编号 > 102的每个领导手下的最低工资大于5000的领导编号是哪个，以及最低工资
SELECT manager_id, MIN(salary) 
FROM employees 
WHERE manager_id > 102 
GROUP BY manager_id 
HAVING MIN(salary) > 5000
# 按表达式或函数分组
# 例：按照员工姓名分组，查询每组员工的人数，筛选出员工个数 > 5的有哪些
SELECT COUNT(*), LENGTH(last_name) 
FROM employees 
GROUP BY LENGTH(last_name) 
HAVING COUNT(last_name) > 5;
# 按多个字段分组
# 例：查询每个部门每个工种的员工的平均工资
SELECT AVG(salary), department_id, job_id 
FROM employees 
GROUP BY job_id, department_id;
# 添加排序
# 查询每个部门每个工种的员工的平均工资，并按平均工资从小到大排序
SELECT AVG(salary), department_id, job_id 
FROM employees 
GROUP BY job_id, department_id 
ORDER BY AVG(salary) ASC

# 连接查询

语法

SELECT 查询列表 FROM A, B [WHERE 子句] [ 其他子句 ]
查询的数据源是 A 和 B 的笛卡尔积，若 A 有 m 行，B 有 n 行，则 A 和 B 的笛卡尔积有 mn 行。
通常需要使用 WHERE 子句进行筛选
MySQL 不支持全外连接

# 等值连接

# 等值连接
# 例：查询女生对应的男生
SELECT 'name', boy_name 
FROM girls, boys 
WHERE girls.boyfriend_id = boys.id;
# 例：查询员工名和对应的部门名
SELECT last_name, department_name 
FROM employees, departments 
WHERE employees.department_id = departmens.departmen_id;
# 为表起别名，起了别名后，就不能使用原来的表名了
# 查询员工名、工种号、工种名
SELECT last_name, e.job_id /*因为两个表都有job_id字段，所以要指明*/, job_title 
FROM employees AS e, jobs AS j 
WHERE e.job_id = j.job_id;
# 配合筛选
# 查询有奖金的员工名、部门名
SELECT last_name, department_name 
FROM employees AS e, dempartments AS d
WHERE e.'deparrment_id' = d.'departmen_id' 
AND e.commission_id IS NOT NULL;
# 配合分组
# 查询每个城市的部门个数
SELECT COUNT(*), city 
FROM departments, locations 
WHERE departments.location_id = locations.id 
GROUP BY city;
# 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name, manager_id, MIN(salary) 
FROM departments, employees
WHERE departments.manager_id = employees.manager_id 
AND commissioin_id IS NOT NULL
GROUP BY department_name;
# 配合排序
# 查询每个工种的工种名和员工个数，按员工个数降序
SELECT job_title, COUNT(*) 
FROM employees, jobs 
WHERE emoloyees.job_id = jobs.job_id 
GROUP BY job_title DESC;
# 多表连接
# 例：查询员工名，部门名和所在城市，按员工名降序排序
SELECT last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id 
ORDER BY last_name DESC;

# 非等值连接

# 非等值连接
# 例：查询员工的工资和工资等级
SELECT salary, grade_level 
FROM employees, job_grades 
WHERE employees.salary BETWEEN job_grades.lowest_salary AND job_grades.highest_salary;

# 自连接

使用自连接必须起别名

# 例：查询员工名和其上级的名字
SELECT A.last_name, B.last_name 
FROM employees AS A, employees AS B
WHERE A.manager_id = B.id;

# SQL99 语法

语法：

SELECT 查询列表

FROM 表 1 别名 [连接类型] JOIN 表 2 别名

ON 连接条件

[WHERE 子句]

[ GROUP BY 子句

[HAVING 子句] ]

[ORDER BY 子句]
分类
- 内连接：INNER
- 外连接
  - 左外连接：LEFT [OUTER]
  - 右外连接：RIGHT [OUTER]
  - 全外连接：FULL [OUTER]
- 交叉连接：CROSS
注意：如果有多个连接，则必须每个连接后面跟一个 ON 子句，不能写在一起

-- 内连接 -- 
# 等值连接
# 例：查询员工名、部门名
SELECT last_name, department_name 
FROM employees AS e 
INNER JOIN departmens AS d
ON e.department_id = d.department_id;
# 非等值连接
# 例：查询员工的工资级别，按工资从小到大排序
SELECT salary,grade_level
FROM employees AS e
INNER JOIN job_grades AS g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
ORDER BY salary ASC;
# 自连接
# 例：查询员工名和他领导的名字
SELECT e.last_name, m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id;

# 外连接

外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录（以 NULL 值显示）
左外连接，LEFT JOIN 左边的是主表
右外连接，RIGHT JOIN 右边的是主表
MySQL 不支持全外连接

# 外连接：用于查询一个表中有，另一个表中没有的情况
# 例：查询男朋友不在男神表中的女神名
SELECT grils.name, boys.name
FROM grils
LEFT OUTER JOIN boys
ON girls.boyfriend_id = boys.id
WHERE boys.id IS NULL;
# 例：查询哪个部门没有员工
SELECT department_name, employee_id
FROM departments
LEFT OUTER JOIN employees
ON departments.department_id = employees.department_id
WHERE employee_id IS NULL;
# 全外连接 = 内连接 + 表1中有表2中没有 + 表1中没有表2有
# 交叉连接：其实就是笛卡尔积
SELECT * 
FROM employees
CROSS JOIN departments;

# 子查询

含义：出现在其他语句中的 SELECT 语句。主查询可以是 UPDATE, INSERT, DELETE, SELECT
按照子查询出现的位置分类
- SELECT 后面
- FROM 后面
- WHERE 或 HAVING 后面
- EXISTS 后面（相关子查询）
多行子查询的操作符：IN, NOT IN, ANY, SOME, ALL（ANY 和 SOME 是一样的）

# 放在 WHERE 或 HAVING 后面

# 放在WHERE或HAVING后面
# 例1：谁的工资比Abel高
SELECT * 
FROM employees 
WHERE salary > (
    SELECT salary 
    FROM employees 
    WHERE last_name = "Abel"
);
# 例2：查询job_id与141号员工相同，salary比143号员工多的员工信息
SELECT * 
FROM employees
WHERE job_id = (
    SELECT job_id 
    FROM employees 
    WHERE employee_id = 141
) AND salary > (
    SELECT salary 
    FROM employees 
    WHERE employee_id = 143
);
# 例3：查询工资最少的员工的员工信息
SELECT * 
FROM employees
WHERE salary = （
	SELECT MIN(salary)
	FROM employees
);
# 例4：查询最低工资大于50号部门的最低工资的部门信息
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary) 
    FROM employees 
    WHERE department_id = 50
);
# 非法使用标量子查询
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > ( # 这里的结果是一个数salary > 一个列。 应该改成 salary > all (..) 
    SELECT salary 
    FROM employees 
    WHERE department_id = 50
);
# 例5：返回location_id是1400或1700的部门中所有员工姓名
SELECT last_name
FROM employees
WHERE department_id in ( # 或 = ANY(...)
    SELECT department_id 
    FROM departments
    WHERE location_id in (1400, 1700)
);
# 例6：返回其他工种中比job_id为'IT_PROG'工种任意工资低的员工的信息
SELECT *
FROM employees
WHERE salary < all(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = "IT_PROG"
) AND job_id <> "IT_PROG";
# 例7：查询员工编号最小并且工资最高的员工信息
SELECT * 
FROM employees
WHERE (employee_id, salary) = ( ####### 这种写法不常用
	SELECT MIN(employee_id), MAX(salary)
	FROM employees
);

# 放在 SELECT 后面

只支持标量子查询

# 例1：查询每个部门的员工个数
SELECT departments.*, (
    SELECT COUNT(*) 
    FROM employees
    WHERE employee_id = departments.department_id = employees.department_id
) 
FROM departments;
# 例2：查询员工号为102的部门名
SELECT department_name
FROM departments AS d
INNER JOIN employees AS e
ON d.department_id = e.department_id
WHERE e.employee_id = 102;

# 放在 FROM 后面

FROM 后面的子查询必须起别名

# 例1：查询每个部门的平均工资的工资等级
SELECT avg_dep.ag, avg_dep.department_id, g.grade_level
FROM (
    SELECT AVG(salary) AS ag, department_id
	FROM employees
	GROUP BY department_id
) AS avg_dep
INNER JOIN job_grades AS g
ON avg_dep.ag BETWEEN g.lowest_sal AND g.highest_sal;

# 放在 EXISTS 后面

# SELECT EXISTS(完整的查询语句)：若括号中查询语句不为空，则返回1，否则返回0
# 例1：查询有员工的部门名
SELECT department_name
FROM departments
WHERE EXISTS(
    SELECT *
    FROM employees
    WHERE employees.department_id = departments.department_id
);
# 可以用IN代替
SELECT department_name
FROM departments
WHERE department_id IN(
    SELECT DISTINCT department_id 
    FROM employees
);
# 例2：查询没有女朋友的男生信息
# 可以用IN实现
SELECT b.*
FROM boys
WHERE b.id NOT IN(
	SELECT DISTINCT boyfriend_id
	FROM girls
);
# 也可以用EXISTS代替IN
SELECT b.*
FROM boys
WHERE NOT EXISTS(
	SELECT DISTINCT boyfriend_id
	FROM girls
    WHERE b.id = girls.boyfriend_id
);

# 分页查询

使用情景：如果要查询的数据过多，可以分多页显示（比如淘宝商品不是一次显示完的，这样可以提高速度）
语法：

SELECT 查询列表

FROM 表

[JOIN TYPE] JOIN 表

ON 连接条件

WHERE 筛选条件

GROUP BY

HAVING

ORDER BY

LIMIT offset，size
offset 表示要显示条目的起始索引（索引位置从 0 开始），size 表示要显示的条目个数
LIMIT 语句在语法上和执行顺序上都是最后一步

# 例：查询前五条员工信息
SELECT * FROM employees LIMIT 0, 5; # 等价于 SELECT * FROM employees LIMIT 5;(默认从第一条数据开始)
# 显示有奖金的工资最高的10名员工信息
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

# 联合查询

UNION：将多个查询语句的结果合并成一个结果
语法：查询语句 1 UNION 查询语句 2 UNION 查询语句 3 …
应用场景：要查询的结果来自于多个表，且多个表之间没有直接的连接关系，但查询的信息一致（查询的字段值必须相同）
注意
- 要求多条查询语句要查询的列数一致
- 要求多条查询语句查询的每一列的类型和顺序最好要一致（不报错，但没意义）
- UNION 关键字默认是去重的，如果想要包含重复项，可以使用 UNION ALL

# 例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%' 
UNION ALL
SELECT * FROM employees WHERE department_id > 90;
# 查询中国用户表中的男性用户信息和外国用户表中的女性用户信息
SELECT * FROM t_ca WHERE gender = 'male'
UNION ALL
SELECT * FROM t_ua WHERE gender = 'female';

# DML 语言

# 插入语句

语法：
- 方式一：INSERT INTO 表名 (字段名 1, 字段名 2, …) VALUES (值 1，值 2，……);
- 方式二：INSERT INTO 表名 SET 列名 = 值，列名 = 值，……
注意
- 插入的值的类型必须与列的类型一致或兼容（顺序、个数必须相同，如果某字段值为空，那就写 NULL）
- 可以为 NULL 的字段值，插入的时候可以为 NULL
- 不可以为 NULL 的列，必须有值
- 可以省略列名，但默认是所有插入所有列，且顺序和表中一致

方式一支持一次插入多行

INSERT INTO girls(id, name, gender, birthday, phone, photo, boyfriend_id)
VALUES(13,'Angela', 'girl', '2001-02-12', '13100000000', NULL, 2),
	(14,'Angela1', 'girl', '2001-03-12', '13100000020', NULL, 4),
	(15,'Angela1', 'girl', '2001-04-12', '13100000400', NULL, 6);

方式一支持子查询

INSERT INTO girls(id, name, phone)
SELECT id, boy_name, phone_number # 这里指把子查询的结果插入到表中，这样可以实现一次性插入多行
FROM boys
WHERE id < 3;

# 举例

# 若photo值可以为NULL，则以下4种方式等价
# ①
INSERT INTO girls(id, name, gender, birthday, phone, photo, boyfriend_id)
VALUES(13,'Angela', 'girl', '2001-02-12', '13100000000', NULL, 2);
# ②
INSERT INTO girls
VALUES(13,'Angela', 'girl', '2001-02-12', '13100000000', NULL, 2);
INSERT INTO girls(id, name, gender, birthday, phone, boyfriend_id)
# ③
VALUES(13,'Angela', 'girl', '2001-02-12', '13100000000', 2);
# ④
INSERT INTO girls
SET name = 'Angela',
	id = 13,
	gender = 'girl'
	birthday = '2001-02-12'
	phone = '13100000000'
	boyfriend_id = 2;

# 修改语句

# 修改单表记录

语法：UPDATE 表名 SET 字段名 1 = 新值 1，字段名 2 = 新值 2，…… WHERE 筛选条件

# 例1：修改girls表中姓唐的女生的电话为13899999999
UPDATE girls
SET phone = '13899999999'
WHERE name LIKE '唐%';
# 例2：修改boys表中id为2的人名称为张飞，魅力值10
UPDATE boys
SET boy_name = '张飞',
	usercp = 10
WHERE id = 2;

# 修改多表记录

语法：
- sql92：UPDATE 表 1 别名，表 2 别名 SET 列 = 值，… WHERE 连接条件 AND 筛选条件；
- sql99：UPDATE 表 1 别名连接类型表 2 别名 ON 连接条件 SET 列 = 值，… WHERE 连接条件 AND 筛选条件；

# 例1：修改张无忌的女朋友们的手机号为114
UPDATE boys b
INNER JOIN girls g
ON b.id = g.boyfriend_id
SET g.phone = '114'
WHERE b.name = '张无忌';
# 例2：修改没有男朋友的女生的男朋友编号为2
UPDATE girls g
LEFT INNER JOIN boys b
ON g.boyfriend_id = b.id
SET g.boyfriend_id = 2
WHERE g.boyfriend_id IS NULL;

# 删除语句

语法
- 方式一：DELETE 要删的表名 FROM 表名 WHERE 筛选条件 [LIMIT 子句]
- 方式二：TRUNCATE TABLE 表名 **（删除整张表）**
假如要删除的表中有自增长列，用 delete 删除后，再插入数据，自增长的列从断点开始，而 TRUNCATE 删除后，再插入数据，自增长的列从 1 开始
DELETE 有返回值（比如：共 3 行受影响）；TRUNCATE 没有返回值（会显示：共 0 行受影响）
TRUNCATE 删除不能回滚，DELETE 删除可以回滚

# 单表删除

# 删除手机号以9结尾的女生信息
DELETE FROM girls
WHERE phone LIKE '%9';
# LIMIT配合删除语句
DELETE FROM girls LIMIT 1; # 删除第一条数据

# 多表删除

# 删除张无忌的女朋友的信息
DELETE girls # 这里只会删除girls表中的数据
# DELETE girls， boys # 这样就会删除girls和boys两个表中的信息
FROM girls 
INNER JOIN boys
ON boys.id = girls.boyfriend_id
WHERE boys.name = '张无忌'

# 删除黄晓明的信息和他女朋友的信息
DELETE girls, boys # 两个表中的信息都会被删除
FROM boys
INNER JOIN girls
ON boys.id = girls.boyfriend_id
WHERE boys.name = '黄晓明'

# DDL 语言

# 库管理

库的创建
- 语法：CREATE DATABASE [IF NOT EXISTS] 库名；
更改库的字符集
- 语法：ALTER DATABASE 库名 CHARACTER SET 字符集名
库的删除
- 语法：DROP DATABASE [IF EXISTS] 库名

# 表管理

# 创建：CREATE

语法：
- CREATE TABLE [IF NOT EXISTS] 表名 (
  
  字段名字段类型 [长度约束])，
  
  字段名字段类型 [长度约束])，
  
  字段名字段类型 [长度约束])，
  
  ……
  
  );

# 创建表book
CREATE TABLE book(
	id INT, # 书的编号
    `name` VARCHAR(20), # 书名
    price DOUBLE, # 价格
    author_id INT # 作者编号
);

# 修改：ALTER

修改列名

ALTER TABLE 表名 CHANGE COLUMN 原名新名类型；

	ALTER TABLE books CHANGE COLUMN author_id author VARCHAR(20);
	<!--code29-->

添加新列

ALTER TABLE 表名 ADD COLUMN 列名类型 [FIRST | AFTER 字段名]

	# 添加在第一列
	ALTER TABLE books ADD COLUMN publish_time DATETIME FIRST;
	# 添加在 author 列后面
	ALTER TABLE books ADD COLUMN publish_time DATETIME AFTER author;
	<!--code30-->

修改表名

ALTER TABLE 表名 RENAME TO 新名字；

	ALTER TABLE books RENAME TO my_books;
	<!--code31-->

# 复制表

仅仅复制表的结构
- CREATE TABLE 表名 LIKE 源表名；
- CREATE TABLE book_copy LIKE books;

仅仅复制表的部分字段

	CREATE TABLE books_copy
	SELECT id, author_id
	FROM books
	WHERE 0; # 筛选条件永远为 false，所以最后复制过去的只有 id 和 author_id 两个字段
	<!--code33-->

# 浮点数

包括两种：float（4 字节），double（8 字节）
写法：FLOAT (M, D), DOUBLE (M, D)
M 代表整数部分和小数部分的长度总和，D 代表精确到小数点后几位，超过范围，则插入临界值
M 和 D 都可以省略，会根据插入的数值的精度来决定精度

# 定点数

写法：DEC (M, D) 或 DECIMAL (M, D)
M 代表整数部分和小数部分的长度总和，D 代表精确到小数点后几位，超过范围，则插入临界值
M 和 D 都可以省略，默认 M = 10，D = 0
精度相比浮点数较高，对精度要求较高的情况下可以选择定点数

# 字符型

保存较短的文本

char (M)：固定长度的字符，M 可省略，默认为 1，效率较高，但耗费空间
varchar (M)：可变长度的字符，M 不可省略，效率较低，但节省空间
enum：枚举

	CREATE TABLE enum_table(
	a enum('a', 'b', 'c')
	);
	INSERT INTO enum_table VALUES('a');# 插入成功
	INSERT INTO enum_table VALUES('b');# 插入成功
	INSERT INTO enum_table VALUES('c');# 插入成功
	INSERT INTO enum_table VALUES('A');# 插入成功，但实际插入的是 'a'
	INSERT INTO enum_table VALUES('m');# 插入失败
	<!--code34-->

binary，varbinary：用于保存较小的二进制数

保存较长的文本
- text
- blob（较长的二进制数据）

# 日期型

DATE：只保存日期
TIME：只保存时间
DATETIME：可以保存日期和时间，与时区无关
YEAR：只保存年份
TIMESTAMP：时间戳，和实际时区有关，范围是 1970 年到 2038 年
查询当前程序时区：SHOW VARIABLES LIKE ‘time_zone’
设置程序时区：SET time_zone 时区

# 约束

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

# 分类

NOT NULL：非空约束，用于保证该字段的值不能为空
DEFAULT：默认约束，保证该字段有默认值
PRIMARY KEY：主键约束，保证该字段具有唯一性、非空性，一张表最多有一个主键，可以多个列共同组成一个主键
UNIQUE：唯一约束，保证该字段具有唯一性，但可以为 NULL
CHECK：检查约束（MySQL8 开始支持）
FOREIGN KEY：外键约束，保证该字段值必须来自于主表的关联列的值

主键约束和唯一约束的对比：

	保证唯一性	是否允许为空	一张表是否可以有多个	是否允许组合
主键约束	√	×	×	√，但不推荐
唯一约束	√	√	√	√，但不推荐

外键的特点：

要求在从表中设置外键关系
从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
主表的关联列必须是一个 key（一般是主键或唯一键）
插入数据时，应该先插入主表，再插入从表
删除数据时，应该先删除从表，在删除主表

主键删除数据时，外键数据的处理方法：

==CASCADE：== 级联删除
==SET NULL：== 置空删除

	DELETE FROM my_table WHERE id = 1 ON DELETE SET NULL; # 置空删除
	DELETE FROM my_table WHERE id = 1 ON DELETE SET CASCADE; # 级联删除
	<!--code35-->

# 修改表时添加约束

添加列级约束：ALTER TABLE 表名 MODIFY COLUMN 字段名字段类型新约束；
添加表级别约束：ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型 (字段名) [ 外键的引用 ]

列级约束和表级约束对比：

	位置	支持的约束类型	是否可以起约束名
列级约束	列的后面	除了外键	√
表级约束	所有列的下面	除了 DEFAULT 和 NOT NULL	×

CREATE TABLE student(
	id INT PRIMARY,
    stu_name VARCHAR(20)L,
    gender char,
    seat INT,
    majorID INT,
);
# 添加非空约束
ALTER TABLE student MODIFY COLUMN stu_name NOT NULL;
# 添加主键约束，，CONSTRAINT可选
ALTER TABLE student MODIFY COLUMN stu_name PRIMARY KEY;
ALTER TABLE student ADD PRIMARY KEY(stu_name);
# 添加唯一约束，，CONSTRAINT可选
ALTER TABLE student MODIFY COLUMN stu_name UNIQUE;
ALTER TABLE student ADD UNIQUE(stu_name);
# 添加外键约束，CONSTRAINT可选
ALTER TABLE student ADD CONSTRAINT fk_student_major FOREIGN KEY(major_id) REFERENCES major(id);

# 删除约束

# 删除非空约束
ALTER TABLE student MODIFY COLUMN stu_name;
# 删除主键约束
ALTER TABLE student MODIFY COLUMN stu_name;
# 或
ALTER TABLE student DROP PRIMARY KEY;
# 删除唯一约束
ALTER TABLE student DROP INDEX 约束名;
# 删除外键约束
ALTER TABLE student DROP FOREIGN KEY 约束名;

# 标识列

含义：又成为自增长列，可以不用手动插入值，系统提供默认的序列值

语法：列名类型 [约束] AUTO_INCREMENT

设置标识列:

# 在创建表时设置标识列
CREATE TABLE tab_identity(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
)

# 这样，在插入时，就不需要插入id这个字段
INSERT INTO tab_identity VALUES(NULL, 'john');
# 或
INSERT INTO tab_identity(name) VALUES('john');
# 实际上还是可以自己插入id这个值的
INSERT INTO tab_identity VALUES(10, 'john'); # 这样会插入一条数据(10, 'john')


-- 也可以在修改表的时候设置标识列
ALTER TABLE tab_identity ALTER COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
# 删除标识列
ALTER TABLE tab_identity ALTER COLUMN id INT PRIMARY KEY;

特点：

一张表只能有一个标识列，且标识列必须是一个键
标识列的类型必须是数值型
可以修改每次增加的数值：SET AOTU_INCREMENT_INCREMENT = 步长；
可以通过手动插入，设置起始值

# TCL 语言

# 事务

含义：一个或一组 SQL 语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

查看数据库支持的存储引擎：==SHOW ENGINES；== 可以看到，默认的存储引擎是 InnoDB。

数据库存储引擎

InnoDB 支持事务，MyISAM 和 MEMORY 不支持事务。

# 事务的属性

原子性：指事物是一个不可分割的工作单位，事物中的操作要么都发生，要么都不发生。
一致性：事务必须使数据库从一个一致性状态变换到另一个一致性状态
隔离性：一个事务的执行不能被其他事物干扰（这取决于隔离级别）
持久性：一个事务一旦被提交，其对数据库的改变就是永久性的

# 事务的创建

隐式事务：事务没有明显的开启和结束的标记，如 INSERT, UPDATE, DELETE 语句

显式事务：事务具有明显的开启和结束的标记。前提：必须通过 SET AUTOCOMMIT = 0; 设置自动提交功能为禁用

显示事务的创建：

# 开启事务
SET AUTOCOMMIT = 0;
START TRANSACTION;# 这句话可选，只要写了SET AUTOCOMMIT = 0 即可
# 步骤二：编写事务中的SQL语句（SELECT, INSERT, UPDATE, DELETE，其他的写了也没用）
# 结束事务，以下两个语句二选一
COMMIT; # 提交事务
ROLLBACK; # 回滚事务

DELETE 和 TRUNCATE 在事务使用时的区别：

	能否回滚
DELETE	√
TRUNCATE	×

# 事务的隔离级别

查看当前隔离级别：SELECT @@TRANSACTION_ISOLATION(MySQL8)

修改隔离级别：SET SESSION / GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别，SESSION 对当前连接有效，GLOBAL 对全局有效，修改的时候要写成中间的’-' 要换成空格！

事务隔离级别（从低到高）

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

# SAVEPOINT 的使用

# savepoint的使用
SET AUTOCOMMIT = 0;
START TRANSACTION;
DELETE FROM account WHERE id = 25;
SAVEPOINT a; # 设置保存点A
DELETE FROM account WHERE id = 19;
ROLLBACK TO a; # 回滚到存档点a

# 视图

含义：虚拟表，和普通表一样使用，是通过表动态生成的数据，其中只保存了 SQL 逻辑，不保存查询结果。

创建视图：CREATE VIEW 视图名 AS 查询语句

# 创建视图，查询员工中包含字符a的员工名、部门名和工种信息
CREATE VIEW my_view AS
SELECT e.last_name, department_name, e.job_id
FROM employee e
INNER JOIN departments d ON e.departmen_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
WHERE e.last_name LIKE '%a%'

修改视图：

CREATE OR REPLACE VIEW 视图名 AS 查询语句
ALTER VIEW 视图名 AS 查询语句

删除视图：DROP VIEW 视图名，视图名，……

查看视图信息：DESC 视图名或 SHOW CREATE VIEW 视图名

具备以下特点的视图不允许更新：

包含以下关键字的 SQL 语句：分组函数、DISTINCT、GROUP BY、HAVING、UNION、UNION ALL
常量视图
SELECT 中包含子查询
用上了连接语句（能 UPDATE，不能 INSERT、DELETE）
视图的创建过程中用到了不能更新的另一个视图
WHERE 子句的子查询引用了 FROM 子句中的表

	# WHERE 子句的子查询引用了 FROM 子句中的表
	CREATE VIEW my_view AS
	SELECT *
	FROM employees
	WHERE employee_id in(
	SELECT manager_id
	FROM employees
	);
	<!--code42-->

# 会话变量

作用域：仅仅针对于当前会话连接有效

# 查看所有会话变量
SHOW SESSION VARIABLES; # SHOW VARIBALES效果是一样的 
# 查看部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
# 查看指定的某个会话变量
SELECT @@SESSION.TRANSACTION_ISOLATION;
# 修改某个会话变量
SET @@SESSION.TRANSACTION_ISOLATION = SERIALIZABLE;
# 或
SET SESSION TRANSACTION_ISOLATION = SERIALIZABLE;

# 自定义变量

# 用户变量

作用域：针对于当前会话连接有效，可以在任何地方使用，即可以放在 begin end 里面或外面

声明并初始化（在声明时必须初始化）：

SET @用户变量名 = 值
SET @用户变量名 := 值
SELECT @用户变量名 := 值

赋值：

方式一：

SET @用户变量名 = 值
SET @用户变量名 := 值
SELECT @用户变量名 := 值

方式二：

SELECT 字段 INTO 变量名 FROM 表；

	# 例子，将变量名 @count 赋值为 employees 表的数据条数
	SELECT COUNT(*) INTO @count FROM employees;
	# 查看
	SELECT @count;
	<!--code44-->

# 存储过程和函数

含义：类似于 Java 中的方法

# 存储过程

含义：一组预先编译号的 SQL 语句的集合，可以理解成批处理语句

优点：

提高代码的重用性
简化操作
减少编译次数并且减少了和数据库服务器的连接次数，提高了效率

# 创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体(一组合法的SQL语句)
END

注意：

参数列表包含三部分：参数模式参数名参数类型，如：IN stuname VARCHAR (20)
参数模式：
- IN：该参数可以作为输入，也就是说该参数需要调用方传入值
- OUT：该参数可以作为输出，也就是说该参数可以作为返回值
- INOUT：该参数既可以作为输入，又可以作为输出；既需要传入值，又可以返回值
如果存储函数体只有一句话，BEGIN END 可以省略
存储过程体的每条 SQL 语句的结尾要求必须加分号，存储过程的结尾可以使用 DELIMITER 重新设置
- 语法：DELIMITER 结束标记，如 DELIMITER $

# 调用语法

语法：CALL 存储过程名 (实参列表)；

# 空参

# 空参列表
-- 案例：插入2条数据到admin表中
-- 定义存储过程
DELIMITER $
CREATE PROCEDURE test()
BEGIN
	INSERT INTO admin VALUES('john1', 0000),('john2', 0001); # 这里必须加分号
END $

-- 调用
DELIMITER ;
CALL test();

# IN

# 带in模式参数的存储过程
-- 根据女生名，查询对应的男生信息
DELIMITER $
CREATE PROCEDURE test2(IN name VARCHAR(20))
BEGIN
	SELECT boys.*
	FROM boys
	RIGHT JOIN girls ON boys.id = girls.boyfriend_id
	WHERE girls.name = name
END $
-- 调用
DELIMITER ;
CALL test2('my_girl_name');

-- 案例2：创建存储过程实现用户是否登录成功
DELIMITER $
CREATE PROCEDURE test3(IN username VARCHAR(20), IN `password` VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0; # 声明并初始化
	
	SELECT COUNT(*) INTO result # 将查询结果赋值给result
	FROM admin
	WHERE admin.username = username 
	AND admin.password = `password`;
	
	SELECT IF(result > 0, '成功', '失败'); # 打印结果
END $
-- 调用
DELIMITER ;
CALL test2('my_name', 'my_password');

# OUT

-- 根据女生名，返回对应的男生名
DELIMITER $
CREATE PROCEDURE test3(IN girl_name VARCHAR(20), OUT boy_name VARCHAR(20))
BEGIN
	SELECT boys.boy_name INTO boy_name
	FROM boys
	INNER JOIN girls ON boys.id = girls.boyfriend_id
	WHERE girls.name = girl_name;
END $
-- 调用
DELIMITER ;
SET @boy_name;
CALL test3('girl_name', @boy_name);

-- 根据女生名，查询男生名和其对应的魅力值
DELIMITER $
CREATE PROCEDURE test4(IN girl_name VARCHAR(20), OUT boy_name VARCHAR(20), OUT user_cp INT)
BEGIN
	SELECT boys.boy_name, boys.user_cp INTO boy_name, user_cp
	FROM boys
	INNER JOIN girls ON boys.id = girls.boyfriend_id
	WHERE girls.name = girl_name;
END $
-- 调用
DELIMITER ;
SET @boy_name;
SET @user_cp;
CALL test4('girl_name', @boy_name, @user_cp);

# INOUT

# 传入a和b两个值，最终a和b翻倍并返回
DELIMITER $
CREATE PROCEDURE test5(INOUT a INT, INOUT b INT)
BEGIN
	SET a = a * 2;
	SET b = b * 2;
END $
-- 调用
DELIMITER ;
SET @m := 1;
SET @n := 2;
CALL test5(@m, @n);

# 删除存储过程

语法：DROP PROCEDURE 存储过程名

# 查看存储过程的信息

语法：SHOW CREATE PROCEDURE 存储过程名；

# 函数

函数	有且只有一个返回
存储过程	0 个返回或多个返回

# 创建函数

语法：CREATE FUNCTION 函数名 (参数列表) RETURNS 返回类型

创建类型

BEGIN

函数体

END

注意：

参数列表包含两部分：参数名参数类型
函数体一定要有 RETURN 语句，如果没有，会报错
若函数体只有一句话，则可以省略 BEGIN END
要使用 MELIMITER 语句设置结束标记
8.0 版本 MySQL 创建函数需要在 RETURNS TYPE 后面，BEGIN 前面加上创建类型，一般有下面这几种类型：
- ==DETERMINISTIC：== 不确定的
- ==NO SQL：== 没有 SQL 语句
- ==READS SQL DATA：== 读取数据，不涉及修改数据
- ==MODIFIES SQL DATA：== 涉及到修改数据
- ==CONTAINS SQL：== 包含了 SQL 语句

# 调用函数

语法：SELECT 函数名 (参数列表)；

# 返回公司的员工人数
-- 定义函数
DELIMITER $
CREATE FUNCTION test1() RETURNS INT
READS SQL DATA # MySQL8，要加上创建类型
BEGIN
	DECLARE number INT DEFAULT 0; #定义变量
	SELECT count(*) INTO number
	FROM employees;
	RETURNS number;
END$
-- 调用函数
DELIMITER ;
SELECT test1();

# 根据员工名，返回工资
-- 定义函数
DELIMITER $
CREATE FUNCTION test2(`name` VARCHAR(20)) RETURNS INT
READS SQL DATA
BEGIN
	SET @salary := 0; # 设置用户变量，设置局部变量也可以
	SELECT employees.salary INTO @salary # 赋值
	FROM employees
	WHERE last_name = `name`;
	RETURN @salary;
END $
-- 调用
DELIMITER ;
SELECT test2('K_ing');

# 查看函数

语法：SHOW CREATE FUNCTION 函数名；

# 删除函数

语法：DROP FUNCTION 函数名；

# 流程控制结构

# 分支结构

# IF 函数

语法：SELECT IF(expr1, expr2, expr3)

功能：实现简单的双分支，若 expr1 为 true，则返回 expr2，否则返回 expr3

# CASE 结构

# CASE函数，使用一：类似于Java中switch case的效果
/*
	格式： case 要判断的字段或表达式
	when 常量1 then 要显示的值或语句1;（值的时候不加';'，语句的时候加';'）
	when 常量2 then 要显示的值或语句2;
	...
	else 要显示的值或语句2;
	end [as new_name]
*/
SELECT salary, department_id,
CASE department_id 
WHEN 30 THEN salary * 1.1
WHEN 40 THEN salary * 1.2
ELSE salary * 1.2
END AS '新工资' 
FROM employees
# CASE函数，使用二：类似于Java中的多重if
/*
	CASE
	WHEN 条件1 THEN 要显示的值或语句1;（值的时候不加';'，语句的时候加';'）
	WHEN 条件2 THEN 要显示的值1或语句2;
	...
	ELSE 要显示的值1或语句2;
	END
*/
SELECT salary,
CASE
WHEN salary > 12000 THEN 'A'
WHEN salary > 10000 THEN 'B'
ELSE 'C'
END AS rank
FROM employees

## 以上是作为表达式的，也可以作为语句单独出现
/*
	CASE [表达式]
	WHEN 条件1 THEN 要显示的值或语句1;（值的时候不加';'，语句的时候加';'）
	WHEN 条件2 THEN 要显示的值1或语句2;
	...
	ELSE 要显示的值1或语句2;
	END CASE;
*/
# 案例：创建存储过程，根据传入的成绩，显示等级
-- 创建
DELIMITER $
CREATE PROCEDURE test(IN grade INT)
BEGIN
	CASE 
	WHEN grade >= 90 THEN SELECT 'A' AS '等级';
	WHEN grade >= 80 THEN SELECT 'B' AS '等级';
	WHEN grade >= 60 THEN SELECT 'C' AS '等级';
	ELSE SELECT 'D' AS '等级';
	END CASE; # 作为独立语句的时候，必须写成END CASE
END $
-- 执行
DELIMITER ;
CALL test(90);

特点：

可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，BEGIN END 中或 BEGIN END 的外面
可以作为独立的语句去使用，但只能放在 BEGIN END 中
如果 WHEN 中的条件成立，则执行对应的 THEN 后面的语句，并且结束 CASE，之后的语句不会执行。如果都不满足，则执行 ELSE 中的语句
ELSE 可以省略，如果 ELSE 省略了，并且所有的 WHEN 条件都不满足，则返回 NULL

# IF 结构

功能：实现多重分支

语法：IF 条件 1 THEN 语句 1; ELSEIF 条件 2 THEN 语句 2； ELSEIF 条件 3 THEN 语句 3…… END IF；

注意：只能用在 BEGIN END 中

# 案例：创建存储过程，根据传入的成绩，返回等级
-- 创建
DELIMITER $
CREATE FUNCTION test(grade INT) RETURNS CHAR
NO SQL
BEGIN	
	IF grade >= 90 THEN RETURN 'A';
	ELSEIF grade >= 80 THEN RETURN 'B';
	ELSEIF grade >= 60 THEN RETURN 'C';
	ELSE RETURN 'D';
	END IF;
END $
-- 执行
DELIMITER ;
SELECT test(90);

# 循环结构

# WHILE

语法：[标签：] WHILE 循环条件 DO

循环体；

END WHILE [标签]

# LOOP

语法：[标签：] LOOP

循环体；

END LOOP [标签]

可以用来模拟简单的死循环

# REPEAT

语法：[标签：] REPEAT

循环体

UNTIL 结束循环的条件

END REPEAT [标签：]

# 循环控制

ITERATE：结束本次循环，继续下一次
LEAVE：结束当前循环

# 批量插入
DELIMITER $
CREATE PROCEDURE test(IN times INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i <= times DO
		INSERT INTO admin(`user_name`, `password`)
		VALUES(CONCAT('rose', i), '666');
		SET i = i + 1;
	END WHILE;
END $

# 批量插入，如果次数超过30，就退出
DELIMITER $
CREATE PROCEDURE test1(IN times INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	tag1: WHILE i <= times DO
			SET i = i + 1;
			IF I > 30 THEN LEAVE tag1; END IF; # 配合标签跳出循环
			INSERT INTO admin(`user_name`, `password`)
			VALUES(CONCAT('JACK', i), '777');
		  END WHILE tag1;
END $

# 三种结构对比

名称	特点	位置
WHILE	先判断后执行	BEGIN END 之间
LOOP	没有条件的死循环	BEGIN END 之间
REPEAT	先执行后判断	BEGIN END 之间

举例：

# 已知表stringcontent，其中字段:id 自增长，content varchar(20)
# 向该表插入指定个数的，随机的字符串
-- 定义存储结构
DELIMITER $
CREATE PROCEDURE test(IN num INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	DECLARE startIndex INT DEFAULT 1;
	WHILE i <= num DO
		# 产生一个随机整数，代表起始索引，范围是1-26
		SET startIndex = FLOOR(RAND() * 26 + 1;)
		INSERT INTO stringcontent(content) 
		VALUES(SUBSTR(str, startIndex));# 这样其实没用做到随机，举个例子，将就下
		SET i = i + 1;
		END WHILE;
END $
-- 调用

# 完结撒花

本大三菜鸡终于学完 MySQL 基础了。。。。。。溜

面向Offer学习记录

MySQL 学习笔记

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

系统调用与进程通信上一篇

JavaSE 下一篇

	CREATE TABLE book_copy LIKE books;
	<!--code32-->

MySQL基础

# MySQL 学习记录

# 服务的启动与关闭

# 服务端的登录和退出

# 常见命令

# MySQL 的语法规范

# DQL 语言

# 基础查询

# 举例

# 条件查询

# 举例

# 一个坑

# 排序查询

# 举例

# 常见函数

# 字符函数

# 数学函数

# 日期函数

# 流程控制函数

# 其他函数

# 聚合函数

# 分组查询

# 连接查询

# 等值连接

# 非等值连接

# 自连接

# SQL99 语法

# 外连接

# 子查询

# 放在 WHERE 或 HAVING 后面

# 放在 SELECT 后面

# 放在 FROM 后面

# 放在 EXISTS 后面

# 分页查询

# 联合查询

# DML 语言

# 插入语句

# 举例

# 修改语句

# 修改单表记录

# 修改多表记录

# 删除语句

# 单表删除

# 多表删除

# DDL 语言

# 库管理

# 表管理

# 创建：CREATE

# 修改：ALTER

# 复制表

# 浮点数

# 定点数

# 字符型

# 日期型

# 约束

# 分类

# 修改表时添加约束

# 删除约束

# 标识列

# TCL 语言

# 事务

# 事务的属性

# 事务的创建

# 事务的隔离级别

# SAVEPOINT 的使用

# 视图

# 会话变量

# 自定义变量

# 用户变量

# 存储过程和函数

# 存储过程

# 创建语法

# 调用语法

# 空参

# IN

# OUT

# INOUT

# 删除存储过程

# 查看存储过程的信息

# 函数