跳至主要內容

MySQL基础

程序员李某某原创数据库MySQL大约 53 分钟

MySQL基础

查询

SELECT(省略了FROM DUAL)
SELECT 1; -- 没有任何子句
SELECT 9/2; -- 没有任何子句
SELECT ... FROM...;
SELECT * FROM departments;
SELECT department_id, location_id FROM departments;

列的别名

使用AS 或 空格 或 空格+""（避免别名中有空格）

SELECT last_name AS name, commission_pct comm FROM employees;

去除重复行

DISTINCT只能置于SELECT后

SELECT DISTINCT depertment_id,salary; -- 开发中，无需再添加其他列名，没有意义

空值参与运算

空值：null null不是0，也不是"null"

所有运算符或列值遇到null，运算结果都为null

着重号

有表名，列名和关键字一样时，用着重号引起来，开发中避免一样

查询常数

用一个常量代表一个列名，可以增加一个固定的列

SELECT '尚硅谷' as corporation, last_name FROM employees;

显示表结构

DESCRIBE employees;
DESC employees;

过滤数据

SELECT...
FROM...
WHERE...

SELECT *
FROM EMPLOYEES
WHERE LAST_NAME = 'King';

字符串和时间日期用单引号引起来

运算符

  • 算数运算符 + - * /（DIV） %（MOD）

  • 在算数运算中，字符串存在隐式转换，不能转换为数值的，看作0

  • 比较运算符 = <=> <> （!=） < <= > >=

  • = ：都是字符串比较ANSI值，有null参与，结果为null

  • <=> ：安全等于。 记忆技巧：为NULL而生

    安全等于运算符（<=>）与等于运算符（=）的作用是相似的， 唯一的区别 是‘<=>’可 以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL

  • ！=：不相等返回1，相等返回0

  • 关键字：

    • IS NULL
    • IS NOT NULL
    • BETWEEN 小 AND 大 包含边界 NOT BETWEEN ...AND...

  • 函数：

    • IS NULL()

    • IN() 离散值包含 NOT IN()

    • LIKE() 模糊查询 %表示多个字符，_表示一个字符

      若查询的字符含%和_，用转义字符\

      ESCAPE 定义转义字符WHERE job_id LIKE 'it$_%' ESCAPE '$'

    • LEAST() 多个值中返回最小值

      SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);
-- | 0 | a | NULL |

    • GREATEST() 多个值中返回最大值

      SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2);
-- | 2 | c | NULL |

    • 正则表达式运算符

      REGEXP、RLIKE（返回值为0、1）

      SELECT 'shkstart' REGEXP '^shk', 'shkstart' REGEXP 'hk' FROM DUAL;

      SELECT 'atguigu' RLIKE 'gu.gu','atguigu' RLIKE '[ab]';

  • 逻辑运算符 OR || AND && NOT ! XOR AND的优先级高于OR

  • 位运算符： & | ^ ~ >> <<

排序

ORDER BY...

升序 ASC（可省略）

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;

降序 DESC

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ;

多列排序

SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC; -- 先按department_id 升序，相同的按salary降序

可使用别名排序，但是别名只能使用在ORDER BY中，不能使用在WHERE中

分页

关键字 LIMIT（MySQL、PostgreSQL、MariaDB 和 SQLite 特有）

两个参数，位置偏移量（可省略，默认为0），每页多少条数据

SELECT *
FROM 表名
LIMIT 10;
SELECT * FROM 表名 LIMIT 10,10;

​

MySQL8.0新特性

关键字 OFFSET

SELECT *
FROM 表名
LIMIT 3 OFFSET 4； #相当于LIMIT 4，3；

  • 如果是 SQL Server 和 Access，需要使用 TOP 关键字

    SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC;

  • 如果是 DB2，使用 FETCH FIRST 5 ROWS ONLY 这样的关键字

    SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY;

  • 如果是 Oracle，你需要基于 ROWNUM 来统计行数

    SELECT rownum,last_name,salary FROM employees WHERE rownum < 5 ORDER BY salary DESC;

    需要说明的是，这条语句是先取出来前 5 条数据行，然后再按照 hp_max 从高到低的顺序进行排序。实现排序后的分页用到子查询

    SELECT rownum, last_name,salary
FROM (
    SELECT last_name,salary
    FROM employees
    ORDER BY salary DESC)
WHERE rownum < 10;

多表查询

需要正确的连接条件，否则会出现笛卡尔积的错误

  • 笛卡尔积的错误会在下面条件下产生：
    • 省略多个表的连接条件（或关联条件）
    • 连接条件（或关联条件）无效
    • 所有表中的所有行互相连接
  • 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件
SELECT employees.employee_id,employees.last_name,employees.department_id,departments.department_id,departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id; -- 连接条件

  • 表也可用使用别名，一旦使用，SELECT和WHERE都需要使用

  • 开发中，多表查询时，我们在列名前都要加上所属的表

等值连接与非等值连接

连接两个表的条件为=时，就是等值连接查询；其他的运算符连接的就是非等值查询

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接与非自连接

非自连接又叫笛卡氏积连接，是两张表的交叉乘积，就是说其中一个表中的每一个元组都要与另一表中的每一个元组做拼接，然后结果就会很多 自然连接，自己和自己连接，展示的结果中不包含重复的属性列，保留了所有不重复的属性列，自连接肯定要对表命别名

SELECT worker.last_name , manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

内连接与外连接

  • 内连接：结果集中不包含一个表与另一个表不匹配的行

  • 外连接：结果集中包含一个表与另一个表不匹配的行

    • 左外连接：结果集中包含左表中不满足条件的行

    • 右外连接：结果集中包含右表中不满足条件的行

    • 满外连接：结果集中包含所有表中不满足条件的行

92语法： + 在外连接中 +在哪，哪个就是从表，就包含哪个表，MySQL不支持，Oracle支持

#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

99语法：使用JOIN 表名ON 连接条件

SELECT table1.column, table2.column,table3.column
FROM table1
	JOIN table2 ON table1 和 table2 的连接条件
		JOIN table3 ON table2 和 table3 的连接条件

  • JOIN表示内连接（INNER JOIN、CROSS JOIN同）

  • LEFT JOIN表示左外连接（省略了OUTER，即LEFT OUTER JOIN）

  • RIGHT JOIN表示右外连接（省略了OUTER，即RIGHT OUTER JOIN）

  • FULL JOIN表示满外连接（省略了OUTER，即FULL OUTER JOIN），MySQL不支持

UNION、UNION ALL关键字（联合）

用在两个SELECT语句之间，返回几个单个结果集联合后的结果，

UNION执行时会去重

UNION ALL不去重，两个结果集的交集部分，出现两次

UNION ALL效率高，不去重时尽量使用

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

7种JOIN语句

第06章_多表查询

  1. 中图：内连接 A ∩ B

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

  2. 左上：左外连接

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

  3. 右上：右外连接

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

  4. 左中：A - A ∩ B

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

  5. 右中：B - A ∩ B

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

  6. 左下：满外连接 A ∪ B

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

  7. 右下：（A ∪ B） - （A ∩ B） 或 (A - A∩B) ∪（B - A∩B）

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

自然连接：NATURAL JOIN 用于等值连接，自动匹配相等字段

92语法：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

99语法（自然连接）：

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

USING连接：指定同名字段连接，必须配合JOIN使用

  • 可以简化 JOIN ON 的等值连接
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

约束条件小结：

WHERE：通用，但在MySQL中无外连接 +

ON：和JOIN搭配，只能写关联条件，（也可写到WHERE中，但可读性差）

USING：和JOIN搭配，关联条件名称一致

多表连接很耗资源，不要超过三个

存储函数

单行函数

只对一行进行变换每行返回一个结果

参数可以是一列或一个值

基本函数：

绝对值 ABS()
符号 SIGN()
圆周率 PI()
天花板 大于等于最小整数（即进一） CEIL(),CEILING()
地板 小于等于最大整数（即去尾） FLOOR()
最小值 LEAST()
最大值 GREATEST()
取模 MOD()
随机数 RAND()
	种子随机数,伪随机，给定种子，返回的随机数相同RAND(x)
四舍五入 ROUND()
	保留四舍五入 ROUND(x,y) y可为负数
		FORMAT(x,y) y为0或负数只保留整数部分
截断（即去尾法，负数也直接去） TRUNCATE(x,y)
平方根 SQRT()

三角函数：

转弧度 RADIANS()
转角度 DEGREES()
SIN(),ASIN(),COS(),ACOS(),TAN(),ATAN(),ATAN2(m,n),COT()

指对函数：

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

进制转换：

BIN() 返回二进制
HEX() 返回16进制
OCT() 返回八进制
CONV(x,m,n) 把m进制的x转换为n进制

字符串函数：

MySQL中，字符串的位置是从1开始的

ASCII() 返回第一个字符的ASCII值
CHAR_LENGTH(),CHARACTER_LENGTH() 返回字符数
LENGTH() 返回字节数（和字符集有关）
CONCAT() 拼接
	CONCAT_WS(x,...) 每个串之间加x
INSERT(str,idx,len,newstr) 把str中从idx开始len长度的部分替换为newstr
	SELECT INSERT('fkahfhakh',2,5,'abc'); #fabcakh
REPLACE(str,m,n) 替换全部m
UPPER(),UCASE() 转大写
LOWER(),LCASE() 转小写
LEFT(str,n) 返回左边n个字符
RIGHT(str,n) 返回右边n个字符
LPAD(str,len,pad) 右对齐，用pad在左侧填充str，直到长度为len
RPAD(str,len,pad) 左对齐，用pad在右侧填充str，直到长度为len
TRIM() 去收尾空格
    LTRIM() 去左边空格
    RTRIM() 去右边空格
    TRIM(s FROM str) 去掉str中两头的s
REPEAT(str,n) str重复n次
SPACE(n) n个空格
STRCMP(s1,s2) 比较两个字符串ASCII值
SUBSTR(s,index,len) 返回从index开始len长度的子串
	SUBSTRING(s,n,l) 同上
	MID(s,n,l) 同上
LOCATE(substr,str) 返回子串首次出现的位置
	POSITION(sub,str) 同上
	INSTRUCTION(sub,str)同上
ELT(m,str1,str2,...strn) 返回指定位置m的字符串
FIELD(s,s1,s2,s3,...sn) 返回串s在串列表中首次出现的位置
FIND_IN_SET(s1,s2) s1在s2中首次出现的位置
REVERSE(s) 反转
NULLIF(value1,value2) 相等返回null，不等返回value1

时间日期函数：

CURDATE(),CURRENT_DATE() 当前日期
CURTIME(),CURRENT_TIME() 当前时间
NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP() 系统时间
UTC_DATE() 世界标准日期
UTC_TIME() 世界标准时间

UNIX_TIMESTAMP() 当前时间戳
	UNIX_TIMESTAMP(date) date的时间戳
FROM_UNIXTIME(timestamp) 时间戳转普通格式

YEAR(d),MONTH(d),DAY(d) 返回具体日期的年/月/日
HOUR(t),MIBUTE(t),SECOND(t) 返回具体时间的时/分/秒
MONTHNAME(d) 返回具体日期的英文月份
DAYNAME(d) 返回具体日期的英文星期
WEEKDAY(d) 返回数字星期（周一0，周二1...）
QUARTER(d) 返回数字季度（冬1，春2...）
WEEK(d),WEEKOFYEAR(d) 一年的第几周
DAYOFYEAR(d) 一年的第几天
DAYOFMONTH(d) 一月的第几天
DAYOFWEEK(d) 一周的第几天（周日1，周一2...）

EXTRACT(type FROM date) 返回指定部分
	type可以为MICROSECONI（毫秒）,SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,YEAR
        SECOND_MICROSECOND（指定时间的秒的毫秒）
        MINUTE_MICROSECOND,MINUTE_SECOND,
        HOUR_MICROSECOND,HOUR_SECOND,HOUR_MINUTE
        DAY_,
        YEAR_MONTH
TIME_TO_SEC(time) 指定时间转为秒（h*3600+m*60+s）
SEC_TO_TIME(seconds) 指定秒数转为时间（hh：mm：ss）

DATE_ADD(datetime,INTERCAL a type) 在datetime上加a个年/月/日/时/分/秒（a可负）
	ADDDATE()同上 DATE_SUB(),SUBDATE() 减去

ADDTIME(t1,t2) 在t1上加t2，t2为一个数时表示秒，可以为负数
SUBTIME(t1,t2) 减
DATEDIFF(d1,d2) 间隔天
TIMEDIFF(t1,t2) 间隔时间（hh：mm：ss）
FROM_DAYS(n) 0000年1月1日后的n天
TO_DAYS(d) 距离0000年1月1日的天数
LAST_DAY(d) 所在月的最后一天
MAKEDATE(year,n) 返回指定年的第n天的日期
MAKETIME(h,m,s) 时分秒组合时间
PERIOD_ADD(yyyymm,n) 日期yyyymm上加n个月后的yyyymm

DATE_FORMAT(date,fmt) 按照指定格式格式化
TIME_FORMAT(time,fmt)
GET_FORMAT(date_type,format_type) 返回某地区格式SELECT GET_FORMAT(DATE,'USA')
STR_TO_DATE(str,fmt) 格式化逆过程，把str按fmt返回默认格式
	fmt可以为
        年 %Y 2022 %y 22
        月 %M January %m 02 %b Jan. %c 1
        日 %D 1st %d 01 %e 1
        时 %H 23 %h 11（12小时制）
        分 %i 01
        秒 %S 01 %s 同
        周 %W Sunday %a Sun.
			%T 24小时制 %r 12小时制 %p AMPM
	format_type可以为 USA、JIS、ISO、EUR、INTERNAL

流程控制：

IF(value,v1,v2) value为真，则返回v1，为假，则返回v2
IFNULL(v1,v2) 非空返回v1，为空返回v2
CASE WHEN...THEN...WHEN...THEN...ELSE...END 类似if
CASE expr WHEN..THEN...ELSE...END 类似switch

加密解密：

PASSWORD(), ENCODE(),DECODE() 在mysql8.0中弃用
MD5(str) 不可逆，
SHA(str) 不可逆，比MD5更安全

MySQL信息函数：

VERSION() 版本
CONNECTION() 服务器连接数
DATABASE() 当前所在数据库
USER() 用户 主机名@用户名
CHARSET(value) 字符集 utf8mb3
COLLATION(value) 比较规则 utf8_general_ci

其他：

INET_ATON(ip) ip转数字 SELECT INET_ATON('192.168.1.100');
INET_NTOA(v) 数字转ip SELECT INET_NTOA(3232235876);
BENCHMARK(n,expr) expr执行n次所用时间
CONVERT(value USING char_code) 把value字符集改为char_code

聚合函数

作用于一组数据，返回一个值

  • AVG(),SUM()：只适用于数值类型的字段变量，AVG不统计null

  • MAX / MIN :适用于数值类型、字符串类型、日期时间类型的字段（或变量）

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

    计算表中多少条记录

    • 方式1：COUNT(*)，会统计null行

    • 方式2：COUNT(1)，会统计null行

    • 方式3：COUNT(具体字段) : 不一定对！（null值）

    公式：AVG = SUM / COUNT

    有null的数据计算平均时要注意，null计数时用AVG(具体字段,0))

    效率：

    • 对于MyISAM引擎的表是没有区别的

    • 对于InnoDB引擎的表COUNT(*) = COUNT(1)> COUNT(字段)

    • MySQL8.0默认引擎是InnoDB

    • COUNT(*)是标准统计行数的语法

GROUP BY 分组

  • SELECT中出现在聚合函数外的字段，必须出现在GROUP BY中，反之不必

    SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

  • WITH ROLLUP，最后增加一行，记录总和

    SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

  • ROLLUP与ORDER BY互斥

  • HAVING

    • 由于WHERE中不能使用聚合函数（分组之前无法对分组结果筛选）
    • HAVING只能用在GROUP BY后
    • 分组统计函数用HAVING，其他的仍然用WHERE（先筛选一次，效率高）
    SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

SELECT的执行过程

结构：

SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

书写顺序：SELECT ... FROM ... JOIN … ON … WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

执行顺序：FROM->ON->JOIN-> WHERE-> GROUP BY-> HAVING-> SELECT-> DISTINCT-> ORDER BY -> LIMIT

执行原理：先求笛卡尔积，得到虚拟表v1，on筛选得到v2，再join外连接的外部行得到v3，若有2张表以上，重复上述步骤，WHERE筛选后得到v4，GROUP...HAVING得到v5，上述操作都是筛选行，接着进行SELECT筛选列得到v6，DISTINCT去重得到v7，ORDER...BY后得到v8，最后进行分页，得到虚拟表V9

自定义存储函数

和Java的方法基本相同

DELIMITER $
CREATE FUNCTION 函数名(参数名 参数类型,...)#FUNCTION中总是默认为IN参数
RETURNS 返回值类型	#必须有RETURNS
[characteristics ...]
BEGIN
	函数体 #函数体中肯定有 RETURN 语句
END $
DELIMITER ;

SELECT 函数名(实参列表)#调用

若在创建存储函数中报错“ you might want to use the less safelog_bin_trust_function_creators variable ”，有两种处理方法：

方式1：加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA |MODIFIES SQL DATA}” 方式2：

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

窗口函数（8.0新特性）

函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

OVER 关键字指定函数窗口的范围。

如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所 有满足WHERE条件的记录进行计算。 如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。

窗口名：为窗口设置一个别名，用来标识窗口。 PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。 ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。 FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。

#创建表：
CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
category_id INT,
category VARCHAR(15),
NAME VARCHAR(30),
price DECIMAL(10,2),
stock INT,
upper_time DATETIME
);

#添加数据
INSERT INTO goods(category_id,category,NAME,price,stock,upper_time)
VALUES
(1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00'),
(2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00'),
(2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00'),
(2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
(2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00'),
(2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00'),
(2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00');

#ROW_NUMBER()分组排序，顺序显示

mysql> SELECT *		
-> FROM (
->	 SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
-> 	 id, category_id, category, NAME, price, stock
->	 FROM goods) t
-> WHERE row_num <= 3;
+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category		 | NAME 	  | price	| stock |
+---------+----+-------------+---------------+------------+---------+-------+
| 1		  | 6  | 1 			 | 女装/女士精品 	 | 呢绒外套	    | 399.90  | 1200  |
| 2 	  | 3  | 1			 | 女装/女士精品 	 | 卫衣 		 | 89.90   | 1500  |
| 3 	  | 4  | 1 			 | 女装/女士精品 	 | 牛仔裤		 | 89.90   | 3500  |
| 1  	  | 8  | 2 			 | 户外运动 	   | 山地自行车	 | 1399.90 | 2500  |
| 2 	  | 11 | 2			 | 户外运动		   | 运动外套 	  | 799.90 	| 500  |
| 3	  	  | 12 | 2 			 | 户外运动		   | 滑板 		| 499.90 | 1200  |
+---------+----+-------------+---------------+------------+----------+-------+

RANK()分组排序，跳过重复，如：1、2、2、4

mysql> SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
-> id, category_id, category, NAME, price, stock
-> FROM goods;

DENSE_RANK()不跳重复，如：1、2、2、3

PERCENT_RANK()和RANK()有关，PERCENT_RANK() = (rank - 1) / (rows - 1) #rows表示改组总行数

子查询

单行子查询

  • 基于单行操作符，子查询结果只有一行

  • 子查询代码完全一样时可合并

  • 空值问题：子查询结果为空值

  • 非法使用子查询：Subquery returns more than 1 row（结果为多行，却使用单行比较符）

  • 注意字眼，其他

多行子查询

多行比较符

  • IN 等于任意子查询结果 相当于多个=用OR连接

  • ANY、SOME 任一

    • 若<ANY 则小于最大值

    • 若>ANY 则大于最小值

  • ALL 所有

    • 若<ALL 则小于最小值

    • 若>ALL 则大于最大值

空值问题：若子查询结果含空值，那查询结果只显示字段名

  • 解决办法，过滤条件去掉null WHERE **** IS NOT NULL

相关子查询

子查询的过滤条件中，使用了外部表的数据，每次调用都需要外部表输送数据

在SELECT中，除了GROUP BY 和 LIMIT之外，其他位置都可以声明子查询！

  • FROM 用子查询创建一个虚拟表（需要别名）

    SELECT last_name,salary,e1.department_id
FROM employees e1,(
                    SELECT department_id,AVG(salary) dept_avg_sal
                    FROM employees GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;

  • ORDER BY 按照其他表中数据排序

    SELECT employee_id,salary
FROM employees e
ORDER BY (
        SELECT department_name
        FROM departments d
        WHERE e.`department_id` = d.`department_id`
		);

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

EXISTS 与 NOT EXISTS关键字

  • 用来检查在子查询中是否存在满足条件的行

查公司管理者的employee_id，last_name，job_id，department_id信息

  • 方式一：子查询

    SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
                    SELECT DISTINCT manager_id
                    FROM employees);

  • 方式二：自连接

    SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;

  • 方式三： EXISTS

    SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( SELECT *
                FROM employees e2
                WHERE e2.manager_id =e1.employee_id);

公用表表达式（8.0新特性）

公用表表达式类似于子查询，不过，跟子查询不同的是，它可以被多次引用，而且可以被其他的普通公用表表达式所引用

普通公用表表达式

WITH CTE名称
AS （子查询）
SELECT|DELETE|UPDATE 语句;

SELECT * FROM departments
WHERE department_id IN (
                        SELECT DISTINCT department_id
                        FROM employees
                        );

WITH emp_dept_id
AS (SELECT DISTINCT department_id FROM employees)
SELECT *
FROM departments d JOIN emp_dept_id e		#引用公用表
ON d.department_id = e.department_id;

递归公用表表达式

WITH RECURSIVE
CTE名称 AS （子查询）
SELECT|DELETE|UPDATE 语句;

递归公用表表达式由 2 部分组成，分别是种子查询和递归查询，中间通过关键字 UNION [ALL]进行连接

这里的种子查询，意思就是获得递归的初始值。这个查询只会运行一次，以创建初始数据集，之后递归查询会一直执行，直到没有任何新的查询数据产生，递归返回

#用递归公用表表达式中的种子查询，找出初代管理者。字段 n 表示代次，初始值为 1，表示是第一代管理者。
#用递归公用表表达式中的递归查询，查出以这个递归公用表表达式中的人为管理者的人，并且代次的值加 1。直到没有人以这个递归公用表表达式中的人为管理者了，递归返回。
#在最后的查询中，选出所有代次大于等于 3 的人，他们肯定是第三代及以上代次的下属了，也就是下下属了
WITH RECURSIVE cte
AS
(
SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100
-- 种子查询，找到第一代领导
UNION ALL
SELECT a.employee_id,a.last_name,a.manager_id,n+1 FROM employees AS a JOIN cte
ON (a.manager_id = cte.employee_id) -- 递归查询，找出以递归公用表表达式的人为领导的人
)
SELECT employee_id,last_name FROM cte WHERE n >= 3;

表的创建、管理与更新

数据库表字段数据
增加CREATE DATABASE [IF NOT EXISTS] 库名 [CHARACTER SET 字符集]CREATE TABLE [_IF NOT EXISTS] 表名( 字段1, 数据类型 [约束条件] [默认值], …… [表约束条件] )AS 查询表（要求列名一一对应）;ALTER TABLE 表名 ADD [FIRST/AFTER] 字段INSERT INTO 表名(字段）VALUES()，()，(); INSERT INTO 表名(字段） 查询语句
修改ALTER DATABASE 库名 [CHARACTER SET 字符集]ALTER TABLE 表名 RENABE 新表名ALTER TABLE 表名 MODIFY 字段 ALTER TABLE 表名 CHANGE 字段名 新字段UPDATE .... SET .... WHERE ...
查询SHOW DATABASES;SHOW CREATE TABLE 查看表创建信息
SHOW TABLES FROM 查看指定库下数据表		DESCRIBE/DESC 查看表字段信息SELECT
删除DROP DATABASE [IF EXISTS] 库名DROP TABLE IF EXISTS #删除 TRUNCATE TABLE #清空 DELETE FROM #清空，可回滚ALTER TABLE 表名 DROP 字段DELETE FROM .... WHERE....
数据库 DATABASE表 TABLE字段 ALTER TABLE …数据
增CREATECREATEADDINSERT INTO … VALUES
改ALTERALTERMODIFYUPDATE … SET … WHERE
查SHOW DATABASESSHOW TABLESDESCSELECT
删DROPDROPDROPDELETE FROM … WHERE

  • 清空

    TRUNCATE TABLE detail_dept;

    TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

    TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少

    但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故

    故不建议在开发代码中使用此语句

    说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同

5.7版本中，字段类型声明时int(11)型小括号内是字段长度，默认11,（8.0中已不再有小括号）

DDL 和 DML 的说明

① DDL的操作一旦执行，就不可回滚。指令SET autocommit = FALSE对DDL操作失效。(因为在执行完DDL操作之后，一定会执行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE影响的)

② DML的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行DML之前，执行了SET autocommit = FALSE，则执行的DML操作就可以实现回滚。

8.0新特性：DDL原子化

DROP TABLE book1,book2; #其中book1存在，book2不存在

​ 5.7中删除报错，book1仍删除成功，8.0中报错，删除失败

8.0新特性：计算列

  • 简单来说就是某一列的值是通过别的列计算得来的

    例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的

  • 创建和修改表时都支持计算列

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

INSERT INTO tb1(a,b) VALUES (100,200);

数据类型

整数

CREATE TABLE test_int2(
f1 TINYINT, 		#极小int，用于枚举
f2 SMALLINT, 		#小int，小范围统计数据
f3 MEDIUMINT, 		#中int，客流量等
f4 INTEGER, 		#常用int，一般不会超，商品编号
f5 BIGINT 			#大int，金融证券持仓，双十一
f6 INT UNSIGNED 	#无符号int，自然数
f7 INT(5) ZEROFILL 	#指定宽度int，对齐填充
)

INT(5) ZEROFILL ① 显示宽度为5。当insert的值不足5位时，使用0填充。 ②当使用ZEROFILL时，自动会添加UNSIGNED

给int指定宽度必须结合zerofill 和unsigned才有意义

小数

FLOAT(M,D) 或DOUBLE(M,D)

  • (M,D)中 M=整数位+小数位，D=小数位
  • 也可以加UNSIGNED ，但是不会改变数据范围
  • 从MySQL 8.0.17开始，FLOAT(M,D) 和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用
  • 推荐使用定点数

定点数 -- 全面代替浮点型

DECIMAL(M,D),
DEC,
NUMERIC

位类型

BIT(M) -- M默认为1 +0后转为十进制

日期时间

  • YEAR、TIME、DATE、DATETIME 常用

  • TIMESTAMP 范围小 改时区 SET time_zone = '+8:00';

  • TIME可以使用带有冒号的字符串，比如' D HH:MM:SS' 、' HH:MM:SS '、' HH:MM '、' D HH:MM '、' D HH '或' SS '格式

    • D表示天，其最小值为0，最大值为34
    • 如果使用带有D格式的字符串插入TIME类型的字段时，D会被转化为小时，计算格式为D*24+HH
    • 当使用带有冒号并且不带D的字符串表示时间时，表示当天的时间，比如12:10表示12:10:00，而不是00:12:10

字符串

  • CHAR：用CHAR：很短的，固定长度的，频繁改变column，其他建议用varchar

  • VARCHAR 常用

  • TEXT 不是大内容不用TEXT，即使用，也要单独建表，防止更新时导致内存空洞

ENUM，相当于单选列表，内容忽略大小写，可以用索引获取，可以是NULL

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

INSERT INTO test_enum
VALUES('春'),('秋');

SET，相当于多选列表，自动去重

BLOB，存放图片，视频，音频等，注意空洞问题

约束

为什么需要约束

实体完整性（Entity Integrity） ：例如，同一个表中，不能存在两条完全相同无法区分的记录 域完整性（Domain Integrity） ：例如：年龄范围0-120，性别范围“男/女” 引用完整性（Referential Integrity） ：例如：员工所在部门，在部门表中要能找到这个部门 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门 经理的工资不得高于本部门职工的平均工资的5倍。

位置支持的约束类型是否可以起约束名
列级约束列的后面语法都支持，但外键没有效果不可以
表级约束所有列的下面默认和非空不支持，其他支持可以（主键没有效果）

查看某个表已有的约束

#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名称';

非空约束（NOT NULL）

  • 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型
  • 列级约束、单列约束：只能在列上（不能在表上），只能单列限定非空（不能是组合非空​ ）
CREATE TABLE 表名称(
	字段名 数据类型,
	字段名 数据类型 NOT NULL,
	字段名 数据类型 NOT NULL
);

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

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

唯一性约束（UNIQUE KEY）

  • 同一个表可以有多个唯一约束​
  • 有列级约束，有表级约束
  • 唯一约束可以单列唯一，也可以组合唯一
  • 唯一性约束允许列值为空
  • 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同
  • MySQL会给唯一约束的列上默认创建一个唯一索引
create table 表名称(
    字段名 数据类型,
    字段名 数据类型 unique,
    字段名 数据类型 unique key,
    字段名 数据类型
);
create table 表名称(
    字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型,
    constraint 起个约束名 unique key(字段名)	#表级约束，表示字段名的组合不能同时重复
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
unique key(字段列表) #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
);

alter table 表名称 add unique key(字段列表);
alter table 表名称 modify 字段名 字段类型 unique;

ALTER TABLE USER
DROP INDEX uk_name_pwd;	#表级约束名

主键约束（PRIMARY KEY）

  • 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值
  • 一个表最多只能有一个主键约束，有列级约束，有表级约束
  • 主键约束对应着表中的一列或者多列（复合主键）
  • 复合主键约束，这些列都不允许为空值，组合的值不允许重复
  • MySQL的主键名总是PRIMARY，无法命名
  • 当创建主键约束时，系统默认会建立对应的主键索引，如果删除主键约束了，对应的索引就自动删
  • 不要修改主键字段的值。主键是数据记录的唯一标识，如果修改了主键的值，有可能破坏数据完整性
create table 表名称(
    字段名 数据类型 primary key, #列级模式
    字段名 数据类型,
    字段名 数据类型
);
create table 表名称(
    字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型,
    constraint 约束名 primary key(字段名) #表级模式，起名无效
);
create table 表名称(
    字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型,
    primary key(字段名1,字段名2) #复合主键，表示字段1和字段2的组合是唯一的，也可以有更多个字段
);

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键

alter table 表名称 drop primary key;

自增列（auto_increment）

​ （1）一个表最多只能有一个自增长列 ​ （2）当需要产生唯一标识符或顺序值时，可设置自增长 ​ （3）自增长列约束的列必须是键列（主键列，唯一键列） ​ （4）自增约束的列的数据类型必须是整数类型 ​ （5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值

create table 表名称(
                字段名 数据类型 primary key auto_increment,
                字段名 数据类型 unique key not null,
                字段名 数据类型 unique key,
                字段名 数据类型 not null default 默认值,
);
alter table 表名称 modify 字段名 数据类型 auto_increment;

alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除

MySQL 8.0新特性—自增变量的持久化（5.7的索引只加载在内存中）

外键约束（FOREIGN KEY）（了解）

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以， MySQL 允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

​ 限定某个表的某个字段的引用完整性。 ​ 比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

​ 被引用的叫主表或父表

（1）从表的外键列，必须引用/参考主表的主键或唯一约束的列 为什么？因为被依赖/被参考的值必须是唯一的 （2）在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如student_ibfk_1;），也可以指定外键约束名。 （3）创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表 （4）删表时，先删从表（或先删除外键约束），再删除主表 （5）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据 （6）在“从表”中指定外键约束，并且一个表可以建立多个外键约束 （7）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致

如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can't create table 'database.tablename' (errno: 150)”

（8）当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高） （9）删除外键约束后，必须手动删除对应的索引

约束关系是针对双方的

添加了外键约束后，主表的修改和删除数据受约束 添加了外键约束后，从表的添加和修改数据受约束 在从表上建立外键，要求主表必须存在 删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

约束等级

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

删除

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

检查约束（CHECK）

MySQL 5.7 不支持

检查某个字段的值是否符合xx要求，一般指的是值的范围

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

age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))

CHECK(height>=0 AND height<3)

默认约束（DEFAULT）

设置默认值

设置或删除时注意：默认约束和非空约束互不影响

面试题

面试1、为什么建表时，加 not null default '' 或 default 0 答：不想让表中出现null值

面试2、为什么不想要 null 的值 答:（1）不好比较。null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。碰到运算符，通常返回null。 （2）效率不高。影响提高索引效果。因此，我们往往在建表时 not null default '' 或 default 0

面试3、带AUTO_INCREMENT约束的字段值是从1开始的吗？

​ 不一定，看有没有手动赋值

面试4、并不是每个表都可以任意选择存储引擎？

外键约束（FOREIGN KEY）不能跨引擎使用。 MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的

视图

视图

  1. 操作简单：将经常使用的查询操作定义为视图，
  2. 减少数据冗余：视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。
  3. 数据安全：MySQL将用户对数据的访问限制在某些数据的结果集上
  4. 适应灵活多变的需求：工作量相对较大，可以使用视图来减少改动的工作量
  5. 能够分解复杂的查询逻辑：分解数据库中复杂的查询逻辑
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

精简版

CREATE VIEW 视图名称 [(字段列表)]#没有指明字段列表时，默认和查询语句一样，指明时，保证个数相同
AS 查询语句

创建多表联合视图

CREATE VIEW empview
AS
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

利用视图对数据进行格式化

CREATE VIEW emp_depart
AS
SELECT CONCAT(last_name,'(',department_name,')') AS emp_dept
FROM employees e JOIN departments d
WHERE e.department_id = d.department_id

基于视图创建视图

CREATE VIEW emp_dept_ysalary
AS
SELECT emp_dept.ename,dname,year_salary #其中emp_dept，emp_year_salary为视图
FROM emp_dept INNER JOIN emp_year_salary
ON emp_dept.ename = emp_year_salary.ename;

查看视图

SHOW TABLES;	#查看数据库的表对象、视图对象
DESC / DESCRIBE 视图名称;	#查看视图的结构

## 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'G
SHOW CREATE VIEW 视图名称; #查看视图的详细定义信息

更新视图

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然

修改方式一：使用CREATE OR REPLACE VIEW 子句修改视图

修改方式二：ALTER VIEW

ALTER VIEW 视图名称
AS
查询语句

不可更新的视图

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

存储过程

变量

系统变量（@@）

全局系统变量：针对于所有会话（连接）有效，但不能跨重启

会话系统变量：仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修改，不会影响其他会话同一个会话系统变量的值。会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改。

在MySQL中有些系统变量只能是全局的，例如 max_connections 用于限制服务器的最大连接数；

有些系统变量作用域既可以是全局又可以是会话，例如 character_set_client 用于设置客户端的字符集；

有些系统变量的作用域只能是当前会话，例如 pseudo_thread_id 用于标记当前会话的 MySQL 连接 ID。

查看会话系统变量

SHOW VARIABLES;
SHOW SESSION VARIABLES;#session可省略

查看全局系统变量

SHOW GLOBAL VARIABLES;

查看指定系统变量

SELECT @@global.max_connections;#global.查看全局
SELECT @@session.character_set_client;#session.查看会话
SELECT @@character_set_client; #先查询会话系统变量，再查询全局系统变量

用户变量

会话用户变量（@）：只对当前连接会话有效

局部变量：只在 BEGIN 和 END 语句块中有效，只能放在第一句。局部变量只能在存储过程和函数中使用。

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

#查看
SELECT @用户变量

#定义局部变量
BEGIN
    #声明局部变量
    DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
    DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
    #为局部变量赋值
    SET 变量名1 = 值;
    SELECT 值 INTO 变量名2 [FROM 子句];
    #查看局部变量的值
    SELECT 变量1,变量2,变量3;
END

MySQL 8.0的新特性—全局变量的持久化

在MySQL数据库中，全局变量可以通过SET GLOBAL语句来设置。例如，设置服务器语句超时的限制，可以通过设置系统变量max_execution_time来实现：

使用SET GLOBAL语句设置的变量值只会临时生效。数据库重启后，服务器又会从MySQL配置文件中读取变量的默认值。 MySQL 8.0版本新增了SET PERSIST 命令。例如，设置服务器的最大连接数为1000：

MySQL会将该命令的配置保存到数据目录下的mysqld-auto.cnf 文件中，下次启动时会读取该文件，用 其中的配置来覆盖默认的配置文件。

存储过程

概述

执行过程：

存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。

好处：

1、简化操作，提高了sql语句的重用性，减少了开发程序员的压力

2、减少操作过程中的失误，提高效率

3、减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）

4、减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性

缺点：

1、可移植性差。存储过程不能跨数据库移植

2、调试困难。只有少数 DBMS 支持存储过程的调试

3、存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效

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

和视图、函数的对比：

相同点：它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。

与视图区别：视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。

与函数区别：

  • 存储过程用户在数据库中完成特定操作或者任务（如插入，删除等），函数用于返回特定的数据
  • 存储过程声明用procedure，函数用function
  • 存储过程不需要返回类型，函数必须要返回类型
  • 存储过程可作为独立的pl-sql执行，函数不能作为独立的plsql执行，必须作为表达式的一部分
  • 存储过程只能通过out和in/out来返回值，函数除了可以使用out，in/out以外，还可以使用return返回值
  • sql语句（DML或SELECT)中不可用调用存储过程，而函数可以
  • 存储过程的执行方式有两种（1.使用execute2.使用begin和end），函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）

适用场合：

1、如果需要返回多个值和不返回值，就使用存储过程；如果只需要返回一个值，就使用函数

2、存储过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值

3、可以再SQL内部调用函数来完成复杂的计算问题，但不能调用存储过程

分类

1、没有参数（无参数无返回）：调用后直接执行存储过程体

2、仅仅带 IN 类型（有参数无返回）

3、仅仅带 OUT 类型（无参数有返回） ：通过查看变量的方式获取返回值

4、既带 IN 又带 OUT（有参数有返回）

5、带 INOUT（有参数有返回）：通常用子查询传入参数，主查询传出

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

DELIMITER $	#改变语句结束标志，过程体中有;常用$和//
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
	存储过程体
END $
DELIMITER ;

CALL 存储过程名(@参数名);	#调用
#调用方式2
SET @参数名 := 'Abel';
CALL 存储过程名(@参数名);

SELECT @参数名;	#查看变量值
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名;#使用SHOW CREATE语句查看存储过程和函数的创建信息
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']#返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期
SELECT * FROM information_schema.Routines	#查询存储过程和函数的信息
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

没有参数

调用后直接执行存储过程体

DELIMITER //
CREATE PROCEDURE avg_employee_salary()
BEGIN 
	SELECT AVG(salary) FROM employees;	
END //
DELIMITER ;

CALL avg_employee_salary();

仅仅带 OUT 类型

通过查看变量的方式获取返回值

DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
	SELECT MIN(salary) INTO ms
	FROM employees;
END //
DELIMITER ;

CALL show_min_salary(@ms);
SELECT @ms;

仅仅带 IN 类型

DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
	SELECT salary FROM employees
	WHERE last_name = empname;
END //
DELIMITER ;

CALL show_someone_salary('Abel');

既带 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 ;

CALL show_someone_salary2('Abel',@empsalary);
SELECT @empsalary;

带 INOUT（有参数有返回）

通常用子查询传入参数，主查询传出

DELIMITER $
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN
	SELECT last_name INTO empname		#INTO给变量赋值
	FROM employees
	WHERE employee_id = (
				SELECT manager_id
				FROM employees
				WHERE last_name = empname
				);	
END $
DELIMITER ;

CALL show_mgr_name('Abel');
SELECT @empname;

定义条件与处理程序

类似异常机制

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

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

DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）

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

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

例如，在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000'是sqlstate_value。 例如，在ERROR 1142（42000）中，1142是MySQL_error_code，'42000'是sqlstate_value。

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

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

处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。

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

错误类型（即条件）可以有如下取值：

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

处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像“ SET 变量 = 值”这样的简单语句，也可以是使用BEGIN ... END 编写的复合语句。

#1.直接捕获sqlstate_value（错误类型）
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#2：直接捕获mysql_error_value（错误代码）
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#3：先定义条件，再调用（调名字）
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
#4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

流程控制

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF

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

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

[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label]

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

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

LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break。

ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue，意思为“再次循环”。

LEAVE 标记名;#循环结构名字就是标记名

ITERATE label;

游标(了解)

游标是 MySQL 的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。

游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，我们可以通过操作游标来对数据行进行操作。实现随意定位到某一条记录，并对记录的数据进行处理。

第一步，声明游标

第二步，打开游标

第三步，使用游标（从游标中取得数据）

第四步，关闭游标

DECLARE cursor_name CURSOR FOR select_statement;#select_statement 代表的是SELECT 语句
OPEN cursor_name;
FETCH cursor_name INTO var_name [, var_name] ...;#查询的结果集有几个字段，就有几个var_name
CLOSE cursor_name;

#创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，DOUBLE类型；声明OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count。
DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT
total_count INT)
BEGIN
DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资
DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值
DECLARE emp_count INT DEFAULT 0; #记录循环个数
#定义游标
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
#打开游标
OPEN emp_cursor;
REPEAT
#使用游标（从游标中获取数据）
FETCH emp_cursor INTO cursor_salary;
SET sum_salary = sum_salary + cursor_salary;
SET emp_count = emp_count + 1;
UNTIL sum_salary >= limit_total_salary
END REPEAT;
SET total_count = emp_count;
#关闭游标
CLOSE emp_cursor;
END //
DELIMITER ;

触发器TRIGGER（了解）

多个相互关联的表，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录。触发器是由事件来触发某个操作，这些事件包括INSERT 、UPDATE 、DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。

缺点：

1、触发器最大的一个问题就是可读性差。因为触发器存储在数据库中，并且由事件驱动，这就意味着触发器有可能不受应用层的控制。这对系统维护是非常有挑战的。

2、相关数据的变更，可能会导致触发器出错。特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率。

优点：

1.确保完整性，自动触发

2.记录操作日志

3.可以用在操作前进行检查

CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名		#：表示触发器监控的对象。
FOR EACH ROW
触发器执行的语句块;

SHOW TRIGGERSG
SHOW CREATE TRIGGER 触发器名
SELECT * FROM information_schema.TRIGGERS;
DROP TRIGGER IF EXISTS 触发器名称;

#创建数据表：
CREATE TABLE test_trigger (
id INT PRIMARY KEY AUTO_INCREMENT,
t_note VARCHAR(30)
);
CREATE TABLE test_trigger_log (
id INT PRIMARY KEY AUTO_INCREMENT,
t_log VARCHAR(30)
);
#创建触发器
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 ;
#向test_trigger数据表中插入数据
INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');

注意点

注意，如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此 时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子 表的UPDATE和DELETE语句定义的触发器并不会被激活。

事务

概述

概念

事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。

事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务**回滚(rollback)**到最初状态。

为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

DBC事务处理

  • 数据一旦提交，就不可回滚。

  • 数据什么时候意味着提交？

    • 当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
    • **关闭数据库连接，数据就会自动的提交。**如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同一个连接下。

  • JDBC程序中为了让多个 SQL 语句作为一个事务执行：

    • 调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务
    • 在所有的 SQL 语句都成功执行后，调用 commit(); 方法提交事务
    • 在出现异常时，调用 rollback(); 方法回滚事务

    若此时 Connection 没有被关闭，还可能被重复使用，则需要恢复其自动提交状态 setAutoCommit(true)。尤其是在使用数据库连接池技术时，执行close()方法前，建议恢复自动提交状态。

事务的ACID属性

  1. 原子性（Atomicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

  2. 一致性（Consistency） 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

  3. 隔离性（Isolation） 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

  4. 持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

数据库的并发问题

  • 对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
    • 脏读: T2的更新事务还未提交，就被T1读取，一旦T2回滚，T1读取的数据只是临时的，无效的。
    • 不可重复读: T2的 更新事务提交了，T1在T2提交前后两次读到的数据不同。
    • 幻读: T2 的插入事务提交了。T1在T2提交后读到的数据比T2提交前读到的多出几行。
  • 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。
  • 一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

四种隔离级别

  • 数据库提供的4种事务隔离级别：

    REPEATABLE-READ		#可重复读
READ-COMMITTED		#读已提交
READ-UNCOMMITTED	#读未提交
SERIALIZABLE		#可序列化

  • Read Uncommited都没解决，Read Commited解决了脏读，REPEATABLE READ解决了脏读和不可重复复，SERIALIZABLE都解决了

  • Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED 。

  • Mysql 支持 4 种事务隔离级别。Mysql 默认的事务隔离级别为: REPEATABLE READ。

mysql中设置隔离级别

SELECT @@GLOBAL.transaction_isolation;		#查全局
SELECT @@SESSION.transaction_isolation;		#查会话
set session transaction isolation level REPEATABLE READ;	#改当前会话
set global transaction isolation level REPEATABLE READ;		#改全局

#权限授予
#授予通过网络方式登录的tom用户，对所有库所有表的全部权限，密码设为abc123.
grant all privileges on *.* to tom@'%'  identified by 'abc123'; 
#给tom用户使用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限。
grant select,insert,delete,update on atguigudb.* to tom@localhost identified by 'abc123';

关键字

DISTINCT去重

AS别名

SELECT

FROM

WHERE

LIKE

ORDER BY排序

ASC升序

DESC降序 描述,显示表结构（即DESCRIBE）

LIMIT分页

LIMIT...OFFSET...

JOIN（LEFT、RIGHT、INNER、OUTER、FULL）... ON...

NATURAL... JOIN...

JOIN...USING...

GROUP BY

HAVING 组内过滤条件

ESCAPE 定义转义字符

DELIMITER 定义结束符号

DECLARE 定义局部变量、定义条件、处理过程、游标

LEAVE 相当于break

ITERATE 迭代 相当于continue

CURSOR 游标

FETCH 游标取数据

INSERT|UPDATE|DELETE 表数据的增改删

TRIGGER 触发器

BEFORE|AFTER

PARTITION BY 窗口函数分组

运算符

函数

上次编辑于:
贡献者: ext.liyuanhao3,李元昊
Copyright © 2025 程序员李某某