MySQL上篇

服务启动与登录

net stop mysql80		# 停止服务
net start mysql80		# 启动服务
mysql -uroot -p密码		# 直接使用密码登录
mysql -uroot -p		# 之后再输入密码（推荐）
mysql -uroot -P13306 -p		# 指定端口号（3306是8.0，13306是5.7）
mysql -uroot -P3306 -hlocalhost -p		# 指定主机
mysql -uroot -P3306 -hlocalhost -p atguigudb -e"select * from employees"	# 指定数据库和查询语句，zh
# 查看版本号
mysql -V
mysql --version
# 退出
exit
quit

基础知识

分类

  • DDL（数据定义语言）：CREATE、ALTER、DROP、RENAME、TRUNCATE

  • DML（数据操作语言）：INSERT、DELETE、UPDATE、SELECT

  • DCL（数据控制语言）：COMMIT、ROLLBACK、SAVEPOINT、GRANT、REVOKE

规则与规范

  • 语句可以写在一行或多行

  • 以;或\g或\G结束

  • 字符串、日期用单引号

  • MySQL在windows环境下大小写不敏感，Linux环境下敏感

    • 建议数据库名、表名、表别名、字段名等小写
    • 关键字、函数名、绑定变量等大写

注释

# 注释（MySQL特有）
-- 注释（--后有空格）
/*
	多行注释
*/

导入现有的数据

  • 方法一：命令行source文件的全路径名
source D:\programming\MySQL\资料\atguigudb.sql
  • 方法二：基于图形化界面

基本SELECT语句

最基本的SELECT语句

SELECT 字段1,字段2…… FROM 表名;

SELECT 1 + 1, 2 + 3 FROM DUAL;		# DUAL：伪表
SELECT * FROM employees;
SELECT employee_id, last_name
FROM employees;

列的别名

  • 用As关键字（可省略，也就是用空格）
  • 用双引号

注意：列的别名只能在ORDER BY中使用，不能在WHERE中使用。

SELECT employee_id As emp_id, last_name lname, department_id "dept_id"
FROM employees;

去除重复行（DISTINCT）

SELECT DISTINCT department_id
FROM employees;

空值参与计算

空值：NULL

空值参与计算，结果也为空

SELECT employee_id, salary * (1 + commission_pct) FROM employees;

着重号

如果自己的命名和关键字重复，使用``引起来。

SELECT * FROM `order`;

查询常数

常数会自动添加到每一行。

SELECT '尚硅谷', 123, employee_id FROM employees;

显示表结构（DESC）

显示表中字段的相关信息

DESCRIBE employees;
DESC employees;

过滤数据（WHERE）

WHERE写在FROM后面。

SELECT * FROM employees WHERE department_id = 90;
SELECT * FROM employees WHERE last_name = 'King';

运算符

算术运算符

+ - * /（DIV） %（MOD）

SELECT 100 + '1' FROM DUAL;		# 隐式将字符串转为数值
SELECT 100 + 'a' FROM DUAL;		# 不能识别a，当成0处理

# 取模结果的正负与被模数一致

比较运算符

比较结果为真返回1，假返回0，其他情况返回NULL。

/*
	= 等于
	<=> 安全等于：两边都是NULL，返回1；一边是NULL，返回0
	!= <> 不等于
	< <= > >=
	
	一般只要有NULL参与，结果就为NULL
*/
SELECT 1 = 'a', 0 = 'a' FROM DUAL;	# 结果为0和1（a转化为0）
SELECT commission_pct FROM employees
WHERE commission_pct <=> NULL;		# 查询为NULL的字段

# BETWEEN 条件上界 AND 条件下界		包含边界

/*
	% 代表不确定个数的字符
	_ 代表一个不确定字符
	\ 转义字符
*/
SELECT last_name FROM employees WHERE last_name LIKE '%a%';

# 正则表达式格式同Java

逻辑运算符

NOT !
AND &&	# 与的优先级高于或
OR ||
XOR

位运算符

&
|
^
~
>>
<<

排序（ORDER BY）

默认情况返回的数据是按照添加的顺序显示的。

使用ORDER BY关键字。升序ASC（默认），降序DESC。

SELECT employee_id, last_name, salary FROM employees
ORDER BY salary DESC;

# ORDER BY中可以使用别名
SELECT employee_id, last_name, salary * 12 "annual_salary" FROM employees
WHERE salary > 5000		# WHERE始终在FROM之后
ORDER BY annual_salary;

# 二级排序
SELECT employee_id, last_name, salary, department_id FROM employees
ORDER BY department_id ASC, salary DESC;	# 先按id排，再按工资排

分页（LIMIT）

语法：LIMIT 偏移, 条目数或在8.0中LIMIT 条目数 OFFSET 偏移。

LIMIT 0, 条目数 可以简写为 LIMIT 条目数。

LIMIT写在WHERE和ORDER BY之后。

# 假设每页20条
SELECT employee_id, last_name FROM employees
LIMIT 0, 20;		# 从第0条开始显示20条，第一页
SELECT employee_id, last_name FROM employees
LIMIT 20, 20;		# 第二页
SELECT employee_id, last_name FROM employees
LIMIT 40, 20;		# 第三页

多表查询

笛卡尔积的错误

缺少多表的连接条件。

SELECT employee_id, department_name
FROM employees, departments;
# 输出107*27条数据，每个员工与每个部门都匹配了一遍

正确实现

需要有连接条件。n张表连接至少需要n-1个条件连接。

# 两张表的连接
SELECT employee_id, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

# 三张表的连接
SELECT e.employee_id, e.last_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。

从优化角度来看，建议多表查询时每个字段前都指明所在的表。

SELECT employee_id, department_name, employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

可以给表起别名，在SELECT和WHERE中使用。如果起了别名，就只能使用别名，不能使用原名。

SELECT emp.employee_id, dept.department_name, emp.department_id
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id;

分类

等值连接 vs 非等值连接

# 非等值连接（查询工资的档位）
SELECT e.last_name, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接 vs 非自连接

# 查询员工id、员工姓名、管理者id、管理者姓名
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;

内连接 vs 外连接

  • 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。

  • 外连接：还查询到了左表或右表不匹配的行。分为左外连接（左表有但右表无）、右外连接（右表有但左表无）、满外连接。

# 外连接
# 查询所有员工的last_name,department_name信息

# SQL92语法（MySQL不支持）
SELECT emp.employee_id, dept.department_name
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id(+);
# SQL99语法见下

SQL99语法

内连接

使用INNER JOIN，INNER可以省略。

# 两张表
SELECT emp.employee_id, dept.department_name
FROM employees emp JOIN departments dept
ON emp.department_id = dept.department_id;
# 三张表
SELECT e.employee_id, d.department_name, l.city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;

外连接

使用OUTER。

左外连接：LEFT OUTER，OUTER可以省略。

SELECT emp.employee_id, dept.department_name
FROM employees emp LEFT JOIN departments dept
ON emp.department_id = dept.department_id;

右外连接：RIGHT OUTER，OUTER可以省略。

SELECT emp.employee_id, dept.department_name
FROM employees emp RIGHT JOIN departments dept
ON emp.department_id = dept.department_id;

满外连接：FULL OUTER，OUTER可以省略（但MySQL不支持）。

UNION

  • UNION操作符返回两个查询结果集的并集，去除重复记录。
  • UNION ALL操作符返回两个查询结果集的并集，不去重。（需要的资源少，能用尽量用）

7种JOIN的实现

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

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

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

# 左中图
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

# 右中图
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

# 左下图：满外连接
# 方式一：左上 UNION ALL 右中
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
# 方式二：左中 UNION ALL 右上

# 右下图：左中 UNION ALL 右中

自然连接

自动查询两张表中所有相同的字段，然后进行等值连接。

SELECT employee_id, last_name, department_name
FROM employees NATURAL JOIN departments;

USING连接

指定同名字段进行等值连接。

SELECT employee_id, last_name, department_name
FROM employees JOIN departments
USING(department_id);

单行函数

数值函数

基本函数

ABS(x)
SIGN(x)
PI()
CEIL(x), CEILING(x)
FLOOR(x)
LEAST(e1, e2, e3...)
GREATEST(e1, e2, e3...)
MOD(x, y)
RAND()		# 返回[0,1)的随机值
RAND(x)		# 返回[0,1)的随机值，x是种子，相同的x产生相同的随机数
ROUND(x)		# 四舍五入为整数
ROUND(x, y)		# 四舍五入并保留到小数点后y位，y可以为正数、0、负数（小数点前）
TRUNCATE(x, y)		# 返回数字x截断为y位小数的结果，y取值同上
SQRT(x)

三角函数

RADIANS(x)		# 角度转弧度
DEGREES(x)		# 弧度转角度

以下函数中x为弧度。

SIN(x)
ASIN(x)		# 如果x的值不在-1到1之间则返回NULL
COS(x)
ACOS(x)		# 如果x的值不在-1到1之间则返回NULL
TAN(x)
ATAN(x)
ATAN2(m, n)		# ATAN2(m, n) = ATAN(m / n)
COT(x)

指数与对数

POW(x, y), POWER(x, y)
EXP(x)
LN(x), LOG(x)
LOG10(x)
LOG2(x)

进制转化

BIN(x)
HEX(x)
OCT(x)
CONV(x, f1, f2)		# 返回f1进制数变成f2进制数

字符串函数

注意：字符串的索引从1开始

ASCII(s)		# 返回字符串s中第一个字符的ASCII码值
CHAR_LENGTH(s)	# 返回字符数
LENGTH(s)		# 返回字节数
CONCAT(s1, s2,..., sn)
CONCAT_WS(x, s1, s2,..., sn)		# 每个字符串之间要加上x
INSERT(str, idx, len, replacestr)	# 将字符串str从第idx位置开始的len个字符长的字串替换为字符串replacestr
REPLACE(str, a, b)		# 用字符串b替换字符串str中所有出现的字符串a
UPPER(s), UCASE(s)
LOWER(s), LCASE(s)
LEFT(str, n)		# 返回最左边的n个字符
RIGHT(str, n)		# 返回最右边的n个字符
LPAD(str, len, pad)		# 用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str, len, pad)		# 用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)		# 去掉左侧空格
RTRIM(s)		# 去掉右侧空格
TRIM(s)			# 去掉首尾空格
TRIM(s1 FROM s)	# 去掉首尾的s1
TRIM(LEADING s1 FROM s)		# 去掉开始处的s1
TRIM(TRAILING s1 FROM s)	# 去掉结尾处的s1
REPEAT(str, n)		# str重复n次
SPACE(n)		# 返回n个空格
STRCMP(s1, s2)
SUBSTR(s, index, len)		# 返回从index开始的len个字符
LOCATE(substr, str)			# 返回substr在str中首次出现的位置，未找到返回0
ELT(m, s1, s2,..., sn)		# 返回指定位置的字符串，m=1返回s1，m=2返回s2
FIELD(s, s1, s2,..., sn)	# 返回s在字符串列表中第一次出现的位置
FIND_IN_SET(s1, s2)			# 返回s1在s2中出现的位置。其中s2是以逗号分隔的字符串
REVERSE(s)
NULLIF(s1, s2)		# 如果s1和s2相等则返回NULL，否则返回value1

日期和时间函数

获取日期时间

CURDATE(), CURRENT_DATE()		# 返回当前日期（年月日）
CURTIME(), CURRENT_TIME()		# 返回当前时间（时分秒）
NOW(), SYSDATE(), CURRENT_TIMESTAMP(), LOCALTIME(), LOCALTIMESTAMP()	# 返回当前系统日期和时间
UTC_DATE()		# 返回UTC（世界标准时间）日期
UTC_TIME()		# 返回UTC时间

日期与时间戳的转换

UNIX_TIMESTAMP()		# 以UNIX时间戳形式返回当前时间
UNIX_TIMESTAMP(date)	# 将时间date以UNIX时间戳的形式返回
FROM_UNIXTIME(timestamp)	# 将UNIX时间戳的时间转换为普通格式的时间

获取月份、星期、天数等

YEAR(date)/MONTH(date)/DAY(date)
HOUR(time)/MINUTE(time)/SECOND(time)
MONTHNAME(date)		# 返回月份：January...
DAYNAME(date)		# 返回星期几：MONDAY...
WEEKDAY(date)		# 返回周几：周一是0，周2是1...
QUARTER(date)		# 返回季度1~4
WEEK(date), WEEKOFYEAR(date)		# 返回一年中的第几周
DAYOFYEAR(date)
DAYOFMONTH(date)
DAYOFWEEK(date)		# 周日是1，周一是2

日期的操作函数

EXTRACT(type FROM date)		# 返回日期中特定的部分
# type的取值有：
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
......

时间和秒钟的转换函数

TIME_TO_SEC(time)
SEC_TO_TIME(seconds)

计算日期和时间的函数

# 在datetime上加上/减去时间
DATE_ADD(datetime, INTERVAL expr type), ADDDATE(...)
DATE_SUB(datetime, INTERVAL expr type), SUBDATE(...)
# type同上

SELECT DATE_ADD('2021-10-21 23:32:12', INTERVAL '1_1' MINUTE_SECOND) FROM DUAL;
函数 描述
ADDTIME(time1,time2) 返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数
SUBTIME(time1,time2) 返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数
DATEDIFF(date1,date2) 返回date1- date2的日期间隔天数
TIMEDIFF(time1,time2) 返回time1-time2的时间间隔
FROM_DAYS(N) 返回从0000年1月1日起，N天以后的日期
TO_DAYS(date) 返回日期date距离0000年1月1日的天数
LAST_DAY(date) 返回date所在月份的最后一天的日期
MAKEDATE(year,n) 针对给定年份与所在年份中的天数返回一个日期
MAKETIME(hour,minute,second) 将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n) 返回time加上n后的时间

日期的格式化与解析

函数 描述
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进行解析，解析为一个日期

fmt参数格式如下：

参数 描述 参数 描述
%Y 4位数字表示年份 %y 表示两位数字表示年份
%M 月名表示月份 (January,....) %m 两位数字表示月份 (01,02,03。。。)
%b 缩写的月名 (Jan.，Feb.，....) %c 数字表示月份 (1,2,3,...)
%D 英文后缀表示月中的天数(1st,2nd,3rd,...) %d 两位数字表示月中的天数(01,02...)
%e 数字形式表示月中的天数 (1,2,3,4,5.....)
参数 描述 参数 描述
%H 两位数字表示小时，24小时制 (01,02..) %h 两位数字表示小时，12小时制 (01,02..)
%k 数字形式的小时，24小时制(1,2,3) %l 数字形式表示小时，12小时制 (1,2,3,4....)
%i 两位数字表示分钟 (00,01,02) %S和%s 两位数字表示秒(00,01,02...)
%W 一周中的星期名称(Sunday...) %a 一周中的星期缩写 (Sun.,Mon.,Tues.，..)
%w 以数字表示周中的天数(0=Sunday,1=Monday....)
参数 描述 参数 描述
%j 以3位数字表示年中的天数(001,002...) %U 以数字表示年中的第几周，(1,2,3,...) 其中Sunday为周中第一天
%u 以数字表示年中的第几周，(1,2,3,...) 其中Monday为周中第一天
%T 24小时制 %r 12小时制
%p AM或PM %% 表示%

流程控制函数

函数 描述
IF(value,value1,value2) 如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2) 如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ....[ELSE resultn] END 相当于Java的if...else if...else..
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1....[ELSE 值n] END 相当于Java的switch...case...

case语句如果不写else，都不满足条件就为NULL

加密与解密函数

函数 描述
PASSWORD(str) 返回字符串str的加密版本，41位长的字符串。加密结果不可逆，常用于用户的密码加密
MD5(str) 返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL
SHA(str) 从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5更加安全
ENCODE(value,password_seed) 返回使用password_seed作为加密密码加密value
DECODE(value,password_seed) 返回使用password_seed作为加密密码解密value

PASSWORD()、ENCODE()、DECODE()在MySQL8.0中弃用。

MySQL信息函数

函数 描述
VERSION() 返回当前MySQL的版本号
CONNECTION_ID() 返回当前MySQL服务器的连接数
DATABASE()，SCHEMA() 返回MySQL命令行当前所在的数据库
USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER() 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
CHARSET(value) 返回字符串value自变量的字符集
COLLATION(value) 返回字符串value的比较规则

其他函数

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

聚合函数

常见聚合函数

  • AVG()、SUM()：只适用于数值类型的字段/变量
SELECT SUM(salary), AVG(salary) FROM employees;

  • MAX()、MIN()：适用于数值、字符串、日期时间类型的字段/变量

  • COUNT()：计算指定字段在查询结构中出现的个数（不包含NULL值）

SELECT COUNT(employee_id), COUNT(salary), COUNT(salary * 2), COUNT(1) FROM employees;
# 以上结果都为107

# 如果使用InnoDB存储引擎，则一般情况下三者效率 COUNT(*) = COUNT(1) > COUNT(字段)

GROUP BY

SELECT中出现的非组函数的字段（department_id, job_id）必须声明在GROUP BY中，GROUP BY中声明的字段却不一定要SELECT。

# 查询各部门的平均工资
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

# 查询同一部门同一工种的平均工资
SELECT department_id, job_id, AVG(salary) FROM employees GROUP BY department_id, job_id;

# 声明顺序
SELECT
FROM
WHERE
GROUP BY
ORDER BY
LIMIT

# 加上WITH ROLLUP还会输出将整体当成一组的结果。注意不能同时使用ORDER BY。
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id WITH ROLLUP;

HAVING

  • 如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。

  • HAVING必须声明在GROUP BY的后面。

  • 使用HAVING的前提是使用了GROUP BY。

  • 当过滤条件中有聚合函数时，此过滤条件必须声明在HAVING中；当过滤条件中没有聚合函数时，过滤条件声明在WHERE或HAVING中都可以，但是建议声明在WHERE中。

# 查询各部门中最高工资大于10000的部门信息
SELECT department_id, MAX()
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

# 查询id为10，20，30，40各部门中最高工资大于10000的部门信息
# 方式一（推荐，执行效率高于方式二）
SELECT department_id, MAX()
FROM employees
WHERE department_id IN(10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;
# 方式二
SELECT department_id, MAX()
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN(10,20,30,40);

SQL底层执行原理

语法

# sql92语法
SELECT ...,...,...(存在聚合函数)
FROM ...,...,...
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...,...
HAVING 包含聚合函数的过滤条件
ORDER BY ...,...(ASC / DESC)
LIMIT ...,...

# sql99语法
SELECT ...,...,...(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ... ON 多表的连接条件
JOIN ... ON ...
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,...
HAVING 包含聚合函数的过滤条件
ORDER BY ...,...(ASC / DESC)
LIMIT ...,...

执行顺序

子查询

子查询要包含在括号内；一般放在比较条件的右侧；单行操作符对应单行子查询，多行操作符对应多行子查询。

在SELECT中，除了GROUP BY和LIMIT都可以用子查询。

# 外查询（主查询）
SELECT last_name, salary
FROM employees
WHERE salary > (
    			# 内查询（子查询）
				SELECT salary
				FROM employees
				WHERE last_name = 'Abel'
				);

分类

单行子查询 vs 多行子查询

相关子查询 vs 不相关子查询

单行子查询

# 单行操作符
= != > >= < <=
# 返回job_id与141号员工相同，salary比143号员工多的员工姓名、job_id、工资
SELECT last_name, job_id, salary FROM employees
WHERE job_id = (
				SELECT job_id FROM employees
				WHERE employee_id = 141
				)
AND salary > (
				SELECT salary FROM employees
				WHERE employee_id = 143
				);
SELECT department_id, MIN(salary) FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
					  SELECT MIN(salary) FROM employees
					  WHERE department_id = 110
					  );

多行子查询

多行比较操作符：

操作符 含义
IN 等于列表中的任意一个
ANY 需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL 需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME 实际上是ANY的别名，作用相同，一般常使用ANY 
# IN
SELECT employee_id, last_name
FROM employees
WHERE salary IN (
    SELECT MIN(salary)
    FROM employees
    GROUP BY department_id
);
#题目: 返回其它job_id中比job_id为'IT PROG'部门所有工资低的员工的员工号、姓名、job_id 以及 salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
	SELECT salary
    FFROM employees
    WHERE job_id = 'IT_PROG'
);
# 查询平均工资最低的部门id
# 方式一
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
						SELECT MIN(avg_sal)
						FROM (
								SELECT AVG(salary) avg_sal
								FROM employees
								GROUP BY department_id
								) dept_avg_sal
					  );
# 方式二
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY department_id
					  );

如果内查询有NULL，则外查询会查找失败。

相关子查询

#题目: 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
	SELECT AVG(salary)
	FROM employees e2
	WHERE department_id = el.`department_id`
);
#题目:查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
    SELECT department_name
    FROM departments d
    WHERE e.`department_id`= d.`department_id`
) ASC;

EXISTS 与 NOT EXISTS

子查询中是否存在满足条件的记录。

# 查找管理者的信息
SELECT employee_id, last_name, department_id
FROM employees e1
WHERE EXISTS (
				SELECT *
				FROM employees e2
				WHERE e1.employee_id = e2.manager_id
			 );

创建和管理数据库

创建数据库

CREATE DATABASE 数据库名;		# c
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
CREATE DATABASE IF NOT EXISTS 数据库名;	# 如果已经存在则不会创建

管理数据库

# 查看当前连接中的数据库都有哪些
SHOW DATABASES;

# 切换数据库
USE mytest;

# 查看当前数据库中保存的数据表
SHOW TABLES;

# 查看当前使用的数据库
SELECT DATABASE() FROM DUAL;

# 查看指定数据库下保存的数据表
SHOW TABLES FROM mysql;

修改数据库

数据库不能改名，只能新建复制再删除。

# 查看数据库字符集
SHOW CREATE DATABASE mytest;

# 修改字符集
ALTER DATABASE mytest CHARACTER SET 'utf8';

删除数据库

DROP DATABASE mytest;		# 删除失败会报错
DROP DATABASE IF EXISTS mytest;

创建和管理数据表

创建表

创建时没有指明字符集，则默认使用表所在数据库的字符集。

USE atguigudb;

CREATE TABLE [IF NOT EXISTS] myemp1(
	id INT,
	emp_name VARCHAR(15),	# 须指明长度
	hire_date DATE
);

# 查看表结构
DESC myemp1;
# 查看创建表的语句结构
SHOW CREATE TABLE myemp1;
# 方式二：基于现有的表，同时导入数据
CREATE TABLE myemp2 AS
SELECT employee_id, last_name, salary	# 查询语句中字段的别名可以作为新创建表的字段的名称
FROM employees;

# 不导入数据
CREATE TABLE myemp2 AS
SELECT employee_id, last_name, salary
FROM employees
WHERE 1 = 2;

修改表

添加字段

# 默认添加到最后一个字段的位置
ALTER TABLE myemp1
ADD salary DOUBLE(10,2);	# 共10位，小数点后2位

# 添加到第一个
ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;

# 添加到某一个字段之后
ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name;

修改字段

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25);

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) DEFAULT 'AAA';

重命名字段

ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10, 2);		# 重命名包含了修改字段操作

删除字段

ALTER TABLE myemp1
DROP COLUMN email;

重命名表

RENAME TABLE myemp1
TO myemp11;

ALTER TABLE myemp1
RENAME TO myemp11;

删除表

DROP TABLE [IF EXISTS] myemp2;

清空表

清空表数据，保留结构。

TRUNCATE TABLE employees_copy;

DCL、DML、DDL的一些说明

  • DCL 中 COMMIT 和 ROLLBACK
    • COMMIT：提交数据。一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。
    • ROLLBACK：回滚数据。一旦执行ROLLBACK，则可以实现数据的回滚。回滚到最近的一次COMMIT之后。
  • 对比 TRUNCATE TABLE 和 DELETE FROM
    • 相同点：都可以实现对表中所有数据的删除，同时保留表结构。
    • 不同点：
      • TRUNCATE TABLE：一旦执行此操作，表数据全部清除。同时，数据是不可以回滚的
      • DELETE FROM：一旦执行此操作，表数据可以全部清除 (不带WHERE) 。同时，数据可以实现回滚
  • DDL 和 DML 的说明
    • DDL的操作一旦执行，就不可回滚。
    • DML的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行DML之前，执行了SET autocommit = FALSE，则执行的DML操作就可以实现回滚。
COMMIT;
SET autocommit = FALSE;
DELETE FROM myemp3;
ROLLBACK;	# 可以回滚

数据处理之增删改

添加数据

方式一：一条一条添加

# 按照声明的顺序添加
INSERT INTO emp1
VALUES (1, 'Tom', '2000-12-21', 3400);
# 指明要添加的字段（可以改变顺序，只写一部分）
INSERT INTO emp1(id, hire_date, salary, name)
VALUES(2, '1999-09-09', 4000, 'Jerry');

INSERT INTO emp1(id, salary, name)
VALUES(3, 4500, 'Mike');	# 没有赋值的为NULL

INSERT INTO emp1(id, name, salary)
VALUES (4, 'Jim', 5000), (5, '陈茂瑭', 5500);	# 添加多条

方式二：插入查询结果

INSERT INTO emp1(id, name, salary, hire_date)
SELECT employee_id, last_name, salary, hire_date	# 要一一对应
FROM employees
WHERE department_id IN (60, 70);
# 添加的数据的字段长度不能低于查询字段的长度，否则就会有添加失败的风险

更新数据

# 可以批量修改
UPDATE emp1
SET hire_date = CURDATE()
WHERE id = 5;

# 修改多个字段
UPDATE emp1
SET hire_date = CURDATE(), salary = 6000
WHERE id = 4;

# 修改数据可能不成功（可能是由于约束的影响）

删除数据

DELETE FROM emp1
WHERE id = 1;

# 删除时也可能因为约束的影响导致删除失败

MySQL8新特性：计算列

CREATE TABLE test1(
	a INT,
	b INT,
	c int GENERATED ALWAYS AS (a + b) VIRTUAL
);

# c列会自动赋值为a+b的值
INSERT INTO test1(a, b)
VALUES(10, 20);

# a列变化c列也会变化
UPDATE test1
SET a = 100;

数据类型

指明表和字段的字符集

CREATE TABLE temp(
	id INT
) CHARACTER SET 'utf8';

CREATE TABLE temp(
	id INT,
    name VARCHAR(5) CHARACTER SET 'utf8'
);

整数类型

UNSIGNED

后面添加UNSIGNED表示无符号。如果使用了ZEROFILL，则会自动添加UNSIGNED。

显示宽度

类型后面加括号表示显示宽度，要搭配ZEROFILL使用，表示如果宽度不满，则用0填充，若宽度超出则不受影响。（MySQL8.0以后不推荐）

CREATE TABLE mytest(
	f1 INT,
	f2 INT(5),
	f3 INT(5) ZEROFILL
);

INSERT INTO mytest(f3)
VALUES (123), (123456);
# 00123 123456

浮点类型

非标准语法

如果小数位超范围了则四舍五入，整数位超范围了则报错。

误差

避免使用=来判断浮点数是否相等。

定点数类型

如果小数位超范围了则四舍五入，整数位超范围了则报错。

位类型

SELECT BIN(f1),HEX(f2) FROM mytest;		# 以二进制、十六进制显示
SELECT f1+0,f2+0 FROM mytest;		# 以十进制显示

日期与时间类型

YEAR

INSERT INTO test_year(f1)
VALUES('2021'),(2022);		# 都可以，但推荐加单引号

DATE

TIME

DATETIME

TIMESTAMP

文本字符串类型

CHAR和VERCHAR

M都指的是字符数。CHAR是定长的，存在空间浪费。InnoDB中建议使用VARCHAR，因为性能主要由存储总量影响。

TEXT

内容不多建议使用CHAR和VARCHAR。

ENUM

CREATE TABLE test_enum(
	season ENUM('春','夏','秋','冬','unknown')
);

INSERT INTO test_enum
VALUES('春'),('UNKNOWN'),(1),('3');

只能添加枚举的这几个值，忽略大小写，也可以使用索引，没有限制非空的话可以为NULL。

SET

可以选择多个值。

CREATE TABLE test_set(
	s SET ('A','B','C')
);

INSERT INTO test_set(s)	VALUES('A'),('A,B'),('A,B,C,A');

自动去重。

二进制字符串类型

BINARY和VARBINARY

同CHAR和VARCHAR。

BLOB

JSON类型

CREATE TABLE test_json(
	js json
);

INSERT INTO test_json(js)
VALUES('{"name":"songhk", "age":18, "address":{"province": "beijing", "city":"beijing"}}');

SELECT * FROM test_json;	# 获取json字符串
SELECT js -> '$.name' AS name, js -> '$.age' AS age, js -> '$.address.province' AS province FROM test_json;		# 获取m

约束

查看约束

DESC employees;

SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'employees';

非空约束

默认所有值都可以是NULL；非空约束只能出现在列上。

设置非空约束后，插入NULL/修改为NULL时会报错。

CREATE TABLE test1(
	id INT NOT NULL,
    last_name VARCHAR(15) NOT NULL,
    email VARCHAR(25),
    salary DECIMAL(10, 2)
);

# 如果表中该字段有为NULL的，则修改失败
ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;

唯一性约束

可以是某一个列的值唯一，也可以多个列组合的值唯一。可以向声明为UNIQUE的字段上添加NULL值，而且可以多次添加。创建唯一约束时，如果不给唯一约束命名，就默认和列名相同。

单列

CREATE TABLE test2(
	id INT UNIQUE,	# 列级约束
    last_name VARCHAR(15),
    email VARCHAR(25) UNIQUE,
    salary DECIMAL(10, 2),
    
    # 表级约束
    [CONSTRAINT uk_test_email] UNIQUE(email)
);

# 如果已经有值相同了，则执行失败
ALTER TABLE test2
ADD [CONSTRAINT uk_test2_sal] UNIQUE(salary);

ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;

多列

不能都一样。

CREATE TABLE USER(
	id INT,
	`name` VARCHAR(15),
	`password` VARCHAR(25),
    
    CONSTRAINT uk_user_name_pwd UNIQUE(`name`, `password`)
);

删除唯一性约束

添加唯一性约束的列上会自动创建唯一索引。删除唯一约束只能通过删除唯一索引的方式删除。删除时需要指定唯一索引名，唯一索引名和唯一约束名一样。如果创建唯一约束时未指定名称：如果是单列，就默认和列名相同；如果是组合列，则默认与第一个列名相同。

ALTER TABLE test2
DROP INDEX uk_test2_sal;

主键约束

一个表最多只能有一个主键约束，可以在列级别创建，也可以在表级别上创建。如果是多列组合的复合主键约束，那么这些列都不允许为空，并且组合的值不允许重复。创建主键约束时，系统默认会在列或列组合上建立主键索引。

添加

CREATE TABLE test3(
	id INT PRIMARY KEY,
    last_name VARCHAR(15) NOT NULL,
    email VARCHAR(25),
    salary DECIMAL(10, 2)
);

CREATE TABLE test4(
	id INT,
    last_name VARCHAR(15) NOT NULL,
    email VARCHAR(25),
    salary DECIMAL(10, 2),
    
    # 没有必要起名字，起不起名字都叫PRIMARY
    [CONSTRAINT pk_test5_id] PRIMARY KEY(id)
);

CREATE TABLE `user1`(
	id INT,
	`name` VARCHAR(15),
	`password` VARCHAR(25),
    
    PRIMARY KEY(`name`, `password`)
);
ALTER TABLE test6
ADD PRIMARY KEY (id);

删除

实际开发时不会使用。

ALTER TABLE test6
DROP PRIMARY KEY;

自增列

一个表最多只能有一个自增列。自增长列的约束必须是主键列或唯一键列。自增约束列的数据类型必须是整数类型。如果自增列指定了0和NULL，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

CREATE TABLE test7(
	id INT PRIMARY KEY AUTO_INCREMENT,
    last_name VARCHAR(15),
) [AUTO_INCREMENT=1001];

ALTER TABLE test8
MODIFY id INT AUTO_INCREMENT;

# 删除
ALTER TABLE test8
MODIFY id INT;

如果添加了1、2、3、4，删掉4，再添加，则id为5（如果重启服务，mysql5.7中还是4，mysql8.0中是5）。

外键约束

  • 主表（父表）：被引用的表
  • 从表（子表）：引用别人的表

要求

  • 从表的外键列必须引用主表的主键或唯一约束的列
  • 默认名不是列名，而是自动产生的一个外键名；可以指定外键名
  • 先创建主表，再创建从表；删除相反
  • 删除主表记录时需要先删除从表有关的记录
  • 创建外键约束时，系统会默认在列上建立对应的普通索引
  • 删除外键约束后，必须手动删除对应的索引

添加

# 主表
CREATE TABLE dept1(
	dept_id INT PRIMARY KEY,
	dept_name VARCHAR(15)
);
# 从表
CREATE TABLE emp1(
	emp_id INT PRIMARY KEY AUTO_INCREMENT,
	emp_name VARCHAR(15),
	department_id INT,

	# 外键约束
    CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id)
);
# ALTER时添加
ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(dept_id);

约束等级

建议：ON UPDATE CASCADE ON DELETE RESTRICT。

删除

ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;

# 查看索引
SHOW INDEX FROM emp1;

# 删除索引
ALTER TABLE emp1
DROP INDEX fk_emp1_dept_id；

检查约束

5.7不支持。

CREATE TABLE test10(
	id INT,
	last_name VARCHAR(15),
	salary DECIMAL(10, 2) CHECK(salary > 2000)
);

# 例子
CHECK(sex IN ('男', '女'))
CHECK(height >= 0 AND height <= 3)

默认值约束

CREATE TABLE test12(
	id INT,
	last_name VARCHAR(15),
	salary DECIMAL(10, 2) DEFAULT 2000
);

ALTER TABLE test12
MODIFY salary DECIMAL(8, 2) DEFAULT 2000;
# 删除约束
ALTER TABLE test12
MODIFY salary DECIMAL(8, 2);

视图

  • 视图，可以看做是一个虚拟表，本身是不存储数据的。视图的本质，就可以看做是存储起来的SELECT语句
  • 视图中SELECT语句中涉及到的表，称为基表
  • 针对视图做DML操作，会影响到对应的基表中的数据。反之亦然。
  • 视图本身的删除，不会导致基表中数据的删除。
  • 视图的应用场景：针对于小型项目，不推荐使用视图。针对于大型项目，可以考虑使用视图。
  • 视图的优点：简化查询；控制数据的访问

创建

CREATE VIEW vu_emp1 AS
SELECT employee_id, last_name FROM emps;

CREATE VIEW vu_emp_sal AS
SELECT department_id, AVG(salary) FROM emps
WHERE department_id IS NOT NULL
GROUP BY department_id;

指定别名：

# 方式一
CREATE VIEW vu_emp2 AS
SELECT employee_id emp_id, last_name lname FROM emps;

# 方式二
CREATE VIEW vu_emp3(emp_id, lname) AS	# 要一一匹配
SELECT employee_id, last_name FROM emps;

针对多表：

CREATE VIEW vu_emp_dept AS
SELECT e.employee_id, e.department_id, d.department_name
FROM emps e JOIN depts d
ON e.department_id = d.department_id;

可以基于视图创建视图。

查看视图

# 查看表和视图
SHOW TABLES;

# 查看视图结构
DESC vu_emp1;

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

# 查看定义信息
SHOW CREATE VIEW vu_emp1;

更新

修改

CREATE OR REPLACE VIEW vu_emp1 AS
SELECT employee_id, last_name, salary, email FROM emps
WHERE salary > 7000;

ALTER VIEW vu_emp1 AS
SELECT employee_id, last_name, salary, email FROM emps
WHERE salary > 7000;

删除

DROP VIEW [IF EXISTS] vu_emp4;

存储过程与存储函数

存储过程的创建

# 无参
DELIMITER $

CREATE PROCEDURE select_all_data()
BEGIN
	SELECT * FROM employees;
END $

DELIMITER ;
# OUT
DELIMITER $
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
	SELECT MIN(salary) INTO ms
	FROM employees;
END $
DELIMITER ;
# IN
DELIMITER $
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
	SELECT salary FROM employees
	WHERE last_name = empname;
END $
DELIMITER ;
# IN OUT
DELIMITER $
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20), OUT empsalary DECIMAL(10,2))
BEGIN
	SELECT salary INTO empsalary FROM employees
	WHERE last_name = empname;
END $
DELIMITER ;
# INOUT
DELIMITER $
CREATE PROCEDURE show_manager_name(INOUT empname VARCHAR(25))
BEGIN
	SELECT last_name FROM employees
	WHERE employee_id = (
		SELECT manager_id FROM employees
		WHERE last_name = empname
	);
END $
DELIMITER ;

存储过程的调用

# 无参
CALL select_all_data();

# OUT
CALL show_min_salary(@ms);
SELECT @ms;

# IN
# 方式一
CALL show_someone_salary('Abel');
# 方式二
SET @empname := 'Abel';	# =或:=都可以
CALL show_someone_salary(@empname);

# IN OUT
SET @empname = 'Abel';
CALL show_someone_salary2(@empname, @empsalary);
SELECT @empsalary;

# INOUT
SET @empname := 'Abel';
CALL show_manager_name(@empname);
SELECT @empname;

存储函数

要么设置characteristics，要么SET GLOBAL log_bin_trust_function_creators，否则会报错。

DELIMITER $
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
	# characteristics
	DETERMINISTIC
	CONTAINS SQL
	READS SQL DATA
BEGIN
	RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END $
DELIMITER ;

# 调用
SELECT email_by_name();
SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER $
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
BEGIN
	RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END $
DELIMITER ;

# 调用
SELECT email_by_id(100);

SET @emp_id := 102;
SELECT email_by_id(@emp_id);

对比

查看

# 查看创建信息
SHOW CREATE PROCEDURE select_all_data;
SHOW CREATE FUNCTION email_by_id;
# 查看状态
SHOW PROCEDURE STATUS;	# 所有存储过程的状态
SHOW PROCEDURE STATUS LIKE 'select_all_%';	# 可以用LIKE
SHOW FUNCTION STATUS;
SHOW FUNCTION STATUS LIKE 'email_by_id';
# 从information_schema.Routines表中查询
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME = 'show_min_salary' [AND ROUTINE_TYPE = 'PROCEDURE/FUNCTION'];

修改

ALTER PROCEDURE show_min_salary
SQL SECURITY INVOKER
COMMENT '查询最高工资';

删除

DROP FUNCTION [IF EXISTS] email_by_id;
DROP PROCEDURE [IF EXISTS] show_min_salary;

变量、流程控制与游标

变量

系统变量

  • 全局系统变量global
  • 会话系统变量session

有些变量既是全局的又是会话的。

查看系统变量

# 查看全局系统变量
SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES LIKE 'admin_%';
# 查看会话系统变量
SHOW SESSION VARIABLES;
SHOW VARIABLES; 	# 不写默认是会话系统变量
SHOW SESSION VARIABLES LIKE 'admin_%';

查看指定系统变量

SELECT @@global.max_connections;
SELECT @@global.character_set_client;
SELECT @@session.character_set_client;
SELECT @@session.pseudo_thread_id;
# 没有写global或session：先找会话系统变量，没找到再找全局系统变量
SELECT @@character_set_client;

修改系统变量的值

  • 修改MySQL配置文件（需要重启服务）
  • 使用SET修改
SET @@global.变量名 = 变量值;
SET GLOBAL 变量名 = 变量值;
SET @@session.变量名 = 变量值;
SET SESSION 变量名 = 变量值;

用户变量

  • 会话用户变量：以@开头，作用域为当前会话
  • 局部变量：用于存储过程和存储函数

会话用户变量

# 方式一
SET @m1 = 1;
SET @m2 := 2;
SET @m3 := @m1 + @m2;

# 方式二
SELECT @count := COUNT(*) FROM employees;
SELECT @count;

SELECT AVG(salary) INTO @avg_sal FROM employees;
SELECT @avg_sal;

局部变量

使用DECLARE语句定义一个局部变量；仅在BEGIN...END中有效；只能放在第一句。

DELIMITER $
CREATE PROCEDURE test_var()
BEGIN
	# 声明
	DECLARE a INT DEFAULT 0;
	DECLARE b INT;		# 不写DEFAULT默认为NULL
	DECLARE emp_name VARCHAR(25);
	# 赋值
	SET a = 1;
	SET b := 2;
	SELECT last_name INTO emp_name FROM employees WHERE employee_id = 101;
	# 查询
	SELECT a, b, emp_name;
END $
DELIMITER ;

CALL test_var();

定义条件与处理程序

定义条件

DECLARE 错误名 CONDITION FOR 错误码;

# 方式一：使用MySQL_error_code 数值
DECLARE field_not_be_NULL CONDITION FOR 1048;
# 方式二：使用sqlstate_value 字符串
DECLARE field_not_be_NULL CONDITION FOR SQLSTATE '23000';

定义处理程序

#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SOLSTATE '42S02' SET @info = 'NOSUCH_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 NOSUCHTABLE SET @nfO = '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';

在存储过程中，定义处理程序，捕获sqlstate_value值，当遇到MySQL_error_code值为1048时，执行CONTINUE操作，并且将@proc_value的值设置为-1。

DELIMITER //

CREATE PROCEDURE UpdateDataNoCondition()
	BEGIN
		#定义处理程序
		DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1;

		SET @x = 1;
		UPDATE employees SET email = NULL WHERE last_name ='Abel';
		SET @x = 2;
		UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
		SET @x = 3;
	END //
	
DELIMITER;

流程控制

IF

IF 条件1 THEN
	语句1;
ELSEIF 条件2 THEN
	语句2;
ELSE
	语句3;
END IF;

CASE

CASE 表达式
	WHEN 值1 THEN 语句1;
	WHEN 值2 THEN 语句2;
	ELSE 语句3;
END CASE;
CASE 
	WHEN 条件1 THEN 语句1;
WHEN 条件2 THEN 语句2;
ELSE 语句3；
END CASE;

LOOP

[loop_label:] LOOP
	循环体
END LOOP [loop_label]
DELIMITER $
CREATE PROCEDURE test_loop()
BEGIN
	DECLARE num INT DEFAULT 1;
	L1: LOOP
		SET num = num + 1;
		if num >= 10 THEN LEAVE L1;
		END IF;
	END LOOP L1;
	SELECT num;
END $
DELIMITER ;

WHILE

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

REPEAT

至少都会执行一次。

[repeat_label:] REPEAT
	循环体
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]
DELIMITER $
CREATE PROCEDURE test_repeat()
BEGIN
	DECLARE num INT DEFAULT 1;
	REPEAT
		SET num = num + 1;
		UNTIL num >= 10		# 注意没有;
	END REPEAT;
	SELECT num;
END $
DELIMITER ;

LEAVE

可以在循环语句或BEGIN END内使用。类似于break。

LEAVE 标签名;

ITERATE

只能用于循环。

ITERATE 标签名;

游标

在存储过程和存储函数中使用，要写在变量声明的后面。

# 累加几个员工的工资能够达到limit_salary
DELIMITER $
CREATE PROCEDURE get_count(IN limit_salary DOUBLE, OUT cnt INT)
BEGIN
	DECLARE sum_sal DOUBLE DEFAULT 0.0;
	DECLARE emp_sal DOUBLE;
	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 emp_sal;
		
		SET sum_sal = sum_sal + emp_sal;
		SET emp_count = emp_count + 1;
		UNTIL sum_sal >= limit_salary
	END REPEAT;
	
	SET cnt = emp_count;
	
	# 关闭游标
	CLOSE emp_cursor;
END $
DELIMITER ;

触发器

创建

DELIMITER $

CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
	INSERT INTO test_trigger_log(t_log)
	VALUES ('before insert');
END $

DELIMITER ;

查看

# 查看所有触发器
SHOW TRIGGERS;
# 查看某个触发器
SHOW CREATE TRIGGER salary_check_trigger;
# 从information_schema.TRIGGERS表中查询
SELECT * FROM information_schema.TRIGGERS;

删除

DROP TRIGGER [IF EXISTS] after_insert;

窗口函数

序号函数

ROW_NUMBER

RANK

DENSE_RANK

分布函数

PERCENT_RANK

CUME_DIST

前后函数

LAG

LEAD

首尾函数

FIRST_VALUE

LAST_VALUE

用法同上。

其他函数

NTH_VALUE

NTILE

公用表表达式

可以理解为轻量级的子查询。

普通公用表表达式

递归公用表表达式

Java开发
#数据库 #MySQL
MySQL上篇
https://shuusui.site/blog/2022/08/12/mysql-1/
作者
Shuusui
发布于
2022年8月12日
更新于
2022年8月12日
许可协议