MySQL基础查询
#MySQL 2024-07-21

基本结构

1
2
3
4
5
6
7
select selection_list /*要查询的字段，多个字段用逗号隔开*/
from table_list /*要查询的表名称*/
[where condition /*筛选记录的条件*/
 group by grouping_clounms /*对结果进行分组*/
 having condition /*对分组后的记录进行条件筛选*/
order by cloumns /*对结果进行排序*/
 limit  /*对记录总数进行限定*/]
  • select后面可以是表中的字段，常量值，表达式，函数；查询的结果是一个虚拟的表格；

执行顺序：

FROM - 确定数据源，执行表连接和子查询。

WHERE - 筛选满足条件的记录。

GROUP BY - 根据一个或多个列对结果进行分组。

HAVING - 筛选分组后的结果集。

SELECT - 投影选择所需的列。

ORDER BY - 对结果集进行排序。

LIMIT - 返回指定数量的记录。

基础查询，查询所有的列

1
2
3
SELECT * FROM t_stu;
-- 指定字段进行查询
SELECT sid,sname FROM t_stu;

条件查询

  • 按条件表达式筛选，> ， < ， = ， != ， <>（不等于）， >= ，<=
  • 逻辑表达式筛选 &&（and）， || (or)， and ,or,not(取反)
  • 模糊查询 like，between and（not between and） ,in(列表中的值不支持通配符), is null(is not null)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 指定某一个条件进行查询
SELECT * FROM t_stu WHERE courseid=2 
-- AND 是两个条件都要满足
SELECT * FROM t_stu WHERE courseid=2 AND score>60;
-- 查询sid为 1，6，8的记录，属于某个集合
SELECT * FROM t_stu WHERE sid IN(1,6,8);
-- 查询记录某个字段为null 
SELECT * FROM t_stu WHERE sgender is null;
SELECT * FROM  tb_stu WHERE updatetime IS NOT null;
--  查询成绩在70到90区间范围内的记录
SELECT * FROM t_stu WHERE score BETWEEN 70 AND 90;
-- 性别非男的记录
select * FROM t_stu WHERE sgender <> 'm';
select * FROM t_stu WHERE sgender != 'm';

注意：

%：表示0或任意多个字符

_:任意一个字符

between …and… 是左右两边都闭合的。

1
2
3
4
5
6
7
8
-- 名字由3个字母组成
SELECT * FROM t_stu WHERE sname LIKE '___';
-- 名字由j 开头
SELECT * FROM t_stu WHERE sname LIKE 'j%';
-- 第二个字母为a 的学生记录
SELECT * FROM t_stu WHERE sname LIKE '_a%';
-- 查询姓名中包含字母a的记录
SELECT * FROM t_stu WHERE sname LIKE '%a%';

去重查询&起别名

  • 表或者字段还可以起别名，起别名是便于理解，如果查询的字段有重名情况使用别名可以区分，AS可省略 使用空格
1
2
3
4
5
--  查询学生表中的所有性别
SELECT DISTINCT sgender FROM t_stu ;
-- 给查询出的字段起别名 AS是可以省略的 使用空格
SELECT age AS 年龄,sname AS 姓名 FROM  tb_stu;
SELECT sid a,sname b,sgender gender,score c FROM t_stu;

排序 order by子句可以跟单个字段，多个字段，表达式，函数，别名

1
2
3
4
5
-- 查询所有学生记录，按成绩进行降序排序
-- 缺省是ASC升序
SELECT * FROM t_stu ORDER BY score DESC;
-- 查询所有学生记录，首先先按成绩进行降序排序，如果成绩相同，按名字进行升序排序
SELECT * FROM t_stu ORDER BY score DESC, sname ASC;

组函数/聚合函数/分组函数(方法，已经封装好的功能直接调用)

用作统计使用，又称为聚合函数或者统计函数或者组函数

  • 聚合函数是用来做纵向运算的函数：

  • COUNT(字段)：统计指定列不为NULL的记录行数；一般使用count（*）统计行数

  • MAX(字段)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

  • MIN(字段)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

  • SUM(字段)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

  • AVG(字段)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

sum,avg一般处理数值型,

max，min,count可以处理任意数据类型

分组函数都忽略了null值，可以和distinct搭配使用

注意点：组函数可以出现多个，但是不能嵌套；如果没有group by 子句，结果集中所有行数作为一组

1
2
3
4
5
SELECT COUNT(employee_id) AS total_employees FROM salaries;
SELECT SUM(salary) AS total_salaries FROM salaries;
SELECT AVG(salary) AS average_salary FROM salaries;
SELECT MAX(salary) AS highest_salary FROM salaries;
SELECT MIN(salary) AS lowest_salary FROM salaries;

group by分组查询

查询出来的字段要求是group by后的字段，查询字段中可以出现组函数和分组字段

group by后面可以跟聚合函数 可以起别名

1
2
3
4
5
6
-- 	查询每个部门的部门编号和每个部门的工资和
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
-- 	查询每个部门的部门编号以及每个部门的人数
SELECT deptno,SUM(sal),COUNT(1) FROM emp GROUP BY deptno;
-- 	查询每个部门的部门编号以及每个部门员工工资大于1500的人数：
SELECT deptno,COUNT(1) FROM emp WHERE sal>1500 GROUP BY deptno;

按多个字段分组，后面字段一致的为一组

1
2
3
4
-- 按job进行分类
SELECT COUNT(*),job FROM emp GROUP BY job;
-- 按job和mgr进行分类
SELECT COUNT(*),job,mgr FROM emp GROUP BY job,mgr;

having子句

where是对分组前进行过滤；having是对分组后进行过滤

where中不能出现分组/聚合函数，having中可以出现

where是比分组先执行的，having是在分组之后执行的；

having后面可以跟别名

1
2
3
4
-- 查询工资总和大于9000的部门编号以及工资和：
SELECT deptno,sum(sal)  FROM emp GROUP BY deptno HAVING sum(sal)>9000;
-- having中使用别名
SELECT deptno,sum(sal) 总薪资 FROM emp GROUP BY deptno HAVING 总薪资>9000;
1
2
-- 查询部门员工个数大于3的，having中使用了别名
SELECT COUNT(1) cc,deptno FROM emp GROUP BY deptno HAVING cc>3;

limit 

1
2
3
-- 第一位表示起始索引位置（从0开始），第二位表示个数；在分页中会使用
SELECT * FROM emp LIMIT 1,5;
SELECT * FROM emp LIMIT 5; 等价与 SELECT * FROM emp LIMIT 0, 5;

select完整语法使用

1
SELECT deptno,count(1),sum(sal) allsum FROM emp WHERE sal>1500 GROUP BY deptno HAVING sum(sal)>7000 ORDER BY allsum desc LIMIT 2;

多表查询

内连接

  • 多表等值连接的结果是多表的交集部分，N表连接，至少需要N-1个连接条件，没有顺序要求，一般起别名

  • 非等值连接，只要不是等号连接的都是非等值连接

外连接，有主表有从表，主表肯定会显示完整的内容

  • 左外连接，以左表为主

  • 右外连接，以右表为主

ON后面的条件（ON条件）和WHERE条件的区别：

  • ON条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。

  • WHERE条件：在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。

    建议：ON只进行连接操作，WHERE只过滤中间表的记录

单纯做匹配连接用 join

需要得到笛卡尔积用 cross join

连接时需要某个表全部保留用 left join

自连接，通过别名，将同一张表视为多张表；同一张表中某个字段要去关联另外一个字段

1
2
-- 查询员工姓名和员工的老板的名称
SELECT e1.empno,e1.ename,e2.empno,e2.ename  FROM emp e1, emp e2 WHERE e1.mgr=e2.empno;

子查询(独立子查询)

1
2
-- 查询工资为20号部门平均工资的员工信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp GROUP BY deptno HAVING deptno=20);

相关子查询

1
2
3
4
5
SELECT employee_name, salary
FROM employees e
WHERE salary > (SELECT AVG(salary)
                FROM employees e2
                WHERE e2.department_id = e.department_id);

EXISTS

1
2
3
SELECT column1, column2, ...
FROM table1
WHERE EXISTS (subquery);

返回布尔值： EXISTS 子查询返回布尔值（TRUE 或 FALSE）。如果子查询返回至少一行数据，则 EXISTS 返回 TRUE，否则返回 FALSE。

效率高： 在很多情况下，EXISTS 子查询的执行效率较高，因为一旦找到符合条件的行，它就会停止扫描。

与 NOT EXISTS 结合使用： NOT EXISTS 用于检查子查询是否没有返回任何行。

Exists 往往使用于要查询的字段都在一个表，其他表的字段是用来过滤的情况下，会提高效率。如果涉及获取多表字段，exists并没有优势。

1
2
3
4
5
6
SELECT department_id, department_name
FROM departments d
WHERE EXISTS (SELECT 1
              FROM employees e
              WHERE e.department_id = d.department_id);

WITH

WITH 语句在 SQL 中用于定义一个或多个公用表表达式（Common Table Expressions, CTEs）。公用表表达式是一种临时结果集，它可以在主查询中引用，使得查询更具可读性和维护性。WITH 语句通常用于简化复杂的查询，尤其是在需要重复使用相同的子查询时。

1
2
3
4
5
6
7
8
9
WITH department_avg_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_name, e.salary
FROM employees e
JOIN department_avg_salary d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

集合查询

  • union–将两个表做行拼接，同时自动删除重复的行。
  • union all—将两个表做行拼接，保留重复的行。
  • 使用union组合查询时，只能使用一条order by字句，它必须位于最后一条select语句之后，因为对于结果集不存在对于一部分数据进行排序，而另一部分用另一种排序规则的情况。

INTERSECT（交） 和 EXCEPT（差）是MySQL 在 8.0.31 版本中开始支持

函数

注意：MySQL中的+就只有运算符的功能；会试图将字符型数值转换为数值型再继续操作，转换失败则转为0；若其中有null则结果为null；字符串可以使用concat函数拼接；

聚合函数

除了常见的五个聚合函数（COUNT、SUM、AVG、MIN、MAX），MySQL 还提供了一些其他有用的聚合函数：

GROUP_CONCAT()

定义

1
2
GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])

  • DISTINCT：可选，用于删除重复值。

  • expr：要连接的列或表达式。

  • ORDER BY：可选，指定结果的排序顺序。

  • SEPARATOR：可选，指定结果中使用的分隔符，默认为逗号。

1
2
3
4
# 单列合并，默认以逗号分隔
select a,group_concat(name) as tn from t group by id;
# 分号
SELECT GROUP_CONCAT(name ORDER BY name ASC SEPARATOR '; ')  FROM employees GROUP BY department;

STD()

计算标准差

1
2
3
SELECT STD(salary) FROM employees GROUP BY department;
# same as
SELECT STDDEV(salary) FROM employees GROUP BY department;

VARIANCE()

计算方差

1
2
3
SELECT VARIANCE(salary) FROM employees GROUP BY department;
# same as
SELECT VAR_POP(salary) FROM employees GROUP BY department;

BIT_AND(),BIT_OR(),BIT_XOR()

按位与，或，异或

这些按位聚合函数 (BIT_AND(), BIT_OR(), BIT_XOR()) 在处理权限位、标志位、或其他需要按位操作的数据时有用。(需要搭配数据定义使用)

1
2
# 查看最高权限
SELECT BIT_XOR(access_level) FROM user_permissions GROUP BY user_id;

JSON_ARRAYAGG() 和 JSON_OBJECTAGG()

JSON_ARRAYAGG(expr)：要聚合成 JSON 数组的列或表达式。

JSON_OBJECTAGG(key_expr, value_expr)：要聚合成 JSON 对象的键和值的列或表达式。

1
2
3
SELECT JSON_ARRAYAGG(name) FROM employees GROUP BY department;

SELECT JSON_OBJECTAGG(id, name) FROM employees GROUP BY department;

字符串函数

MySQL 提供了许多常用的字符串函数，以下是一些常用字符串函数的定义、参数、默认值和返回值：

CONCAT()

定义：

1
CONCAT(str1, str2, ...)

  • 参数：str1, str2, ...：要连接的字符串。

  • 返回值：返回连接后的字符串。如果任一参数为 NULL，则返回 NULL。

示例：

1
2
SELECT CONCAT('Hello', ' ', 'World');
# Hello World

CONCAT_WS()

定义：

1
CONCAT_WS(separator, str1, str2, ...)

  • 参数：separator：要使用的分隔符。str1, str2, ...：要连接的字符串。

  • 返回值：返回用分隔符连接的字符串。如果任一参数为 NULL，则忽略该参数。

示例：

1
2
SELECT CONCAT_WS(',', 'Apple', 'Orange', 'Banana');
# Apple,Orange,Banana

LENGTH()

定义：

1
LENGTH(str)

  • 参数：str：要计算长度的字符串。

  • 返回值：返回字符串的字节长度。

示例：

1
2
SELECT LENGTH('Hello');
# 5

CHAR_LENGTH()

定义：

1
CHAR_LENGTH(str)

  • 参数：str：要计算长度的字符串。

  • 返回值：返回字符串的字符长度。

示例：

1
2
SELECT CHAR_LENGTH('Hello');
# 5

LOWER(), UPPER()

SUBSTRING()

定义：

1
SUBSTRING(str, pos, len)

  • 参数：str：源字符串。pos：起始位置（从1开始）len：要提取的长度（可选）。

  • 返回值：返回从指定位置开始，长度为 len 的子字符串。如果 len 未指定，则返回从 pos 开始到字符串末尾的所有字符。

示例：

1
2
SELECT SUBSTRING('Hello World', 7, 5);
# World

TRIM()

定义：

1
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

  • 参数：

    • {BOTH | LEADING | TRAILING}：指定要移除的字符位置，默认为 BOTH。
    • remstr：要移除的字符（可选），默认为空格。
    • str：要修剪的字符串。

  • 返回值：返回移除指定字符后的字符串。

示例：

1
2
SELECT TRIM('  Hello World  ');
# Hello World

REPLACE()

定义：

1
REPLACE(str, from_str, to_str)

  • 参数：

    • str：源字符串。
    • from_str：要替换的子字符串。
    • to_str：替换后的子字符串。

  • 返回值：返回替换指定子字符串后的新字符串。

示例：

1
SELECT REPLACE('Hello World', 'World', 'MySQL');

结果：

1
Hello MySQL

INSTR()

定义：

1
INSTR(str, substr)

  • 参数：

    • str：源字符串。
    • substr：要查找的子字符串。

  • 返回值：返回子字符串在源字符串中首次出现的位置（从1开始）。如果未找到，返回 0。

示例：

1
2
SELECT INSTR('Hello World', 'World');
# 7

数学函数

**ABS(x)：返回x的绝对值，SQRT(x)**：返回一个数的平方根

**BIN(x)**：返回x的二进制，OCT返回八进制，HEX返回十六进制

**CEILING(x)：返回大于x的最小整数值，FLOOR(x)**：返回小于x的最大整数值

**POW(x,y)：返回x的y次方，EXP(x)**：返回e的x次方

**GREATEST(x1,x2,…,xn)：返回集合中最大的值，LEAST(x1,x2,…,xn)**：返回集合中最小的值

**LOG(x,y)：返回x的以y为底的对数，LN(x)**：返回x的自然对数

**MOD(x,y)**：返回x/y的模（余数）

**PI()**：返回pi的值（圆周率）

**RAND()**：返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

**ROUND(x,y)**：返回参数x的四舍五入的有y位小数的值

**SIGN(x)**：返回代表数字x的符号的值

**TRUNCATE(x,y)**：返回数字x截短为y位小数的结果

日期函数

获取

now() 返回日期+时间

curdate()返回系统日期，不包含时间

curtime() 返回当前时间，不包含日期

提取

YEAR(date)函数，用于提取时间字段的年返回的格式为”YYYY”

MONTH(date)函数，用于提取时间字段的月，返回的格式为”mm”，若月份前有0会忽略

MONTHNAME(date)提取月名August

DAY(date)函数，用于提取时间字段的日期，返回的格式为”dd”，若日期前有0会忽略

HOUR(date)函数，用于提取时间字段的小时，返回的格式为”HH”，若小时前有0会忽略

MINUTE(date)函数，用于提取时间字段的分钟，返回的格式为”ii”，若分钟前有0会忽略

SECOND(date)函数，用于提取时间字段的秒，返回的格式为”ss”，若秒前有0会忽略

DATE(date)函数，返回的格式为”YYYY-mm-dd”，

TIME(date)函数，用于提取时间字段的时间，例如’2020-05-26 11:11:11’，返回的格式为”HH:ii:ss”

WEEK(date)函数,返回时间字段是一年中的第几周（1-53）

转换

DATE_FORMAT() 函数在 MySQL 中用于将日期或时间数据格式化为字符串，根据指定的格式显示。

1
DATE_FORMAT(date, format)

format 字符串中可以使用以下格式说明符：

  • 以下是 DATE_FORMAT() 函数中常用的 format 字符串的转换表格：

    格式说明符 描述
    %a 缩写的星期名 (Sun..Sat)
    %b 缩写的月名 (Jan..Dec)
    %c 月，数字 (0..12)
    %D 带英文前缀的月中的天 (1st, 2nd, 3rd, …)
    %d 月中的天，数字 (00..31)
    %e 月中的天，数字 (0..31)
    %f 微秒
    %H 小时 (00..23)
    %h 小时 (01..12)
    %I 小时 (01..12)
    %i 分钟，数字 (00..59)
    %j 年中的天 (001..366)
    %k 小时 (0..23)
    %l 小时 (1..12)
    %M 月名 (January..December)
    %m 月，数字 (00..12)
    %p AM 或 PM
    %r 时间，12 小时（hh:mm:ss AM 或 PM）
    %S 秒 (00..59)
    %s 秒 (00..59)
    %T 时间，24 小时（hh:mm:ss）
    %U 周 (00..53)，星期日是星期的第一天
    %u 周 (00..53)，星期一是星期的第一天
    %V 周 (01..53)，星期日是星期的第一天，与 %X 使用
    %v 周 (01..53)，星期一是星期的第一天，与 %x 使用
    %W 星期名 (Sunday..Saturday)
    %w 周中的天 (0=Sunday..6=Saturday)
    %X 年，4 位，与 %V 使用
    %x 年，4 位，与 %v 使用
    %Y 年，4 位
    %y 年，2 位

常用的格式

1
2
3
4
5
6
'%Y-%m-%d':2024-08-02
'%Y-%m-%d %H:%i:%s'2024-08-02 14:35:09
'%W, %M %d, %Y %H:%i:%s' Friday, August 02, 2024 14:35:09
'%M %Y' August 2024
'%Y-%m-%d %r' 2024-08-02 02:30:45 PM
'%W' Friday

STR_TO_DATE() 函数用于将字符串按指定格式转换为日期。

1
STR_TO_DATE(str, format)

计算

DATE_ADD

对指定的日期加上一个时间间隔。

1
2
3
4
DATE_ADD(date, INTERVAL value unit)

SELECT DATE_ADD('2024-08-02', INTERVAL 10 DAY) AS new_date;
-- 示例结果: '2024-08-12'

DATE_SUB

对指定的日期减去一个时间间隔。

1
2
3
4
DATE_SUB(date, INTERVAL value unit)

SELECT DATE_SUB('2024-08-02', INTERVAL 1 MONTH) AS new_date;
-- 示例结果: '2024-07-02'

DATEDIFF

计算两个日期之间的天数差。

1
2
3
4
DATEDIFF(date1, date2)

SELECT DATEDIFF('2024-08-02', '2024-07-01') AS days_diff;
-- 示例结果: 32

TIMESTAMPDIFF(unit, datetime1, datetime2)

计算两个日期或时间之间的差异，单位可以是秒、分钟、小时、天等。

1
2
3
4
TIMESTAMPDIFF(unit, datetime1, datetime2)

SELECT TIMESTAMPDIFF(DAY, '2024-07-01', '2024-08-02') AS days_diff;
-- 示例结果: 32

单位以指定的interval为准，常用可选：

  • SECOND 秒，

  • MINUTE 分钟（返回秒数差除以60的整数部分）

  • HOUR 小时（返回秒数差除以3600的整数部分）

  • DAY 天数（返回秒数差除以3600*24的整数部分）

  • MONTH 月数

  • YEAR 年数

TIMESTAMPADD(unit, value, datetime)

1
2
3
4
TIMESTAMPADD(unit, value, datetime)

SELECT TIMESTAMPADD(MONTH, 2, '2024-08-02') AS new_date;
-- 示例结果: '2024-10-02'

窗口函数

MySQL 8.0 引入了窗口函数

语法

1
2
3
4
[你要的操作] OVER ( PARTITION BY  <用于分组的列名>
                    ORDER BY <按序叠加的列名> 
                    ROWS|RANGE <窗口滑动的数据范围> )

<窗口滑动的数据范围> 用来限定 [你要的操作] 所运用的数据的范围，具体有如下这些：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
当前 - current row
之前的 - preceding
之后的 - following
无界限 - unbounded
表示从前面的起点 - unbounded preceding
表示到后面的终点 - unbounded following

举例
取当前行和前五行：ROWS between 5 preceding and current row --共6行
取当前行和后五行：ROWS between current row and 5 following --共6行
取前五行和后五行：ROWS between 5 preceding and 5 following --共11行
取当前行和前六行：ROWS 6 preceding（等价于between...and current row） --共7行
这一天和前面6天：RANGE between interval 6 day preceding and current row --共7天
这一天和前面6天：RANGE interval 6 day preceding（等价于between...and current row） --共7天
字段值落在当前值-100到+200的区间：RANGE between 100 preceding and 200 following  --共301个数值

操作可以是：

排序函数

1
2
3
4
5
(1) ROW_NUMBER(): 依次排序，不会出现相同排名 1 2 3 4 5 6 7 8

(2) RANK(): 出现相同排名时，跳跃排序 1 1 3 4 5 5 7

(3) DENSE_RANK(): 出现相同排名时，连续排序 1 1 2 3 3 4 5 6 6

前后行

1
2
3
4
5
6
7
LAG(column_name, offset, default_value): 用于访问当前行之前的行中的值。默认是当前行前面的1行

LEAD(column_name, offset, default_value): 用于访问当前行之后的行中的值。默认是当前行后面的1行

# column_name: 指定要返回的列的名称。
# offset (可选): 指定要向后移动的行数。默认值为 1。
# default_value (可选): 如果指定的偏移量超出窗口范围，则返回的默认值。

聚合函数

1
2
3
SUM(), AVG(), MAX(), MIN()

# SUM(column_name)

最值

1
2
3
4
5
6
7
8
9
10
11
12
# 返回窗口帧中的第一个值和最后一个值。
FIRST_VALUE(column_name)

LAST_VALUE(column_name)

# 语法
LAST_VALUE(column_name) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression [ASC|DESC]
    [window_frame]
)
注意window_frame (可选): 指定窗口帧范围，默认为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

控制流函数

IF

1
2
3
4
IF(condition, true_value, false_value) # 根据条件返回不同的值。

SELECT IF(1 > 0, 'True', 'False') AS result;
-- 示例结果: 'True'

CASE

在查询中进行条件判断，返回不同的值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

SELECT 
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age BETWEEN 18 AND 65 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM users;

# 另一种case
CASE
WHEN [conditional test 1] THEN[result 1]
WHEN [conditional test 2] THEN[result 2]
ELSE [default result]
END

IFNULL

1
2
3
4
IFNULL(expression, alt_value) #如果表达式为 NULL，则返回替代值。

SELECT IFNULL(phone_number, 'No Phone') AS phone;
-- 如果 phone_number 为 NULL，则返回 'No Phone'

NULLIF

1
2
3
4
NULLIF(expression1, expression2) #如果两个表达式相等，则返回 NULL，否则返回第一个表达式的值。

SELECT NULLIF(score, 0) AS adjusted_score;
-- 如果 score 为 0，则返回 NULL，否则返回 score

COALESCE

1
2
3
4
COALESCE(expression1, expression2, ..., expressionN) #返回第一个非 NULL 的表达式值。

SELECT COALESCE(email, 'No Email') AS contact_email;
-- 如果 email 为 NULL，则返回 'No Email'

LOOP

在存储过程中创建循环。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DELIMITER //

CREATE PROCEDURE loop_example()
BEGIN
    DECLARE counter INT DEFAULT 0;
    loop_start: LOOP
        SET counter = counter + 1;
        IF counter > 5 THEN
            LEAVE loop_start;
        END IF;
        SELECT counter;
    END LOOP;
END //

DELIMITER ;

REPEAT

在存储过程中创建循环，直到条件为真才退出循环。

1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER //

CREATE PROCEDURE repeat_example()
BEGIN
    DECLARE counter INT DEFAULT 0;
    REPEAT
        SET counter = counter + 1;
        SELECT counter;
    UNTIL counter > 5
    END REPEAT;
END //

DELIMITER ;

WHILE

在存储过程中创建循环，条件为真时执行。

1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER //

CREATE PROCEDURE while_example()
BEGIN
    DECLARE counter INT DEFAULT 0;
    WHILE counter <= 5 DO
        SET counter = counter + 1;
        SELECT counter;
    END WHILE;
END //

DELIMITER ;

LEAVE

在存储过程或循环中用于退出循环或块。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DELIMITER //

CREATE PROCEDURE leave_example()
BEGIN
    DECLARE counter INT DEFAULT 0;
    loop_start: LOOP
        SET counter = counter + 1;
        IF counter > 5 THEN
            LEAVE loop_start;
        END IF;
        SELECT counter;
    END LOOP;
END //

DELIMITER ;

ITERATE

在存储过程中的循环中跳过当前迭代，继续下一个迭代。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DELIMITER //

CREATE PROCEDURE iterate_example()
BEGIN
    DECLARE counter INT DEFAULT 0;
    loop_start: LOOP
        SET counter = counter + 1;
        IF counter = 3 THEN
            ITERATE loop_start;
        END IF;
        SELECT counter;
        IF counter >= 5 THEN
            LEAVE loop_start;
        END IF;
    END LOOP;
END //

DELIMITER ;

拓展

聚合函数里使用表达式

1
avg(a2.timestamp -a1.timestamp )

AVG括弧里直接加条件，过滤掉一些数据

1
2
avg(rating<3)
# 只计算评分小于三的均值
1
SELECT product_id, SUM(quantity * price) AS total_sales FROM sales GROUP BY product_id;

条件计数

常见的三形式

1

1
count(if(c.action='confirmed',1,null))

当然，几乎所有使用if的地方可以替换为case end 表达式。

2

1
2
3
4
sum(if(c.action='confirmed',1,0))
#更为简洁的一种形式 
SELECT SUM(status = 'confirmed') AS confirmed_count FROM orders;
# 在 MySQL 中，布尔表达式会返回 1（表示 TRUE）或 0（表示 FALSE）

3

1
count(col_value='confirmed' or null)

解释：

col_value=’confirmed’：是一个布尔表达式，如果 col_value 等于 'confirmed'，则结果为 TRUE，否则为 FALSE。

  1. 如果 col_value='confirmed' 结果为 TRUE，那么 TRUE OR NULL 结果是 TRUE。
  2. 如果 col_value='confirmed' 结果为 FALSE，那么 FALSE OR NULL 结果是 NULL。
  3. 如果 col_value 本身是 NULL，那么 col_value='confirmed' 结果是 NULL，而 NULL OR NULL 结果还是 NULL。
  • 当表达式结果为 TRUE 时，这一行会被计入。
  • 当表达式结果为 NULL 时，这一行不会被计入。

注意

  1. count里，只有字段值为null才不计数，其他都是计数的。
  2. count 里面可以添加distinct 关键字，只计数不重复的值。

建议使用第二种

使用构建的字段分组

可以使用自己构建的字段分组，以时间举例

字段为日期，包含年月日，如果按月分组，则需要先select 里形成月，然后group by里使用

1
2
3
select date_format(trans_date,'%Y-%m') as month,country,

 from Transactions group by month

当然，如果查询中不需要group by 或者 order by 分组所依据的字段，也可以直接在group by /order by里写表达式

1
2
select  country,
from Transactions group by date_format(trans_date,'%Y-%m'),country

字符串匹配

LIKE 关键字

  • 包含：LIKE ‘%keyword%’
  • 不包含：NOT LIKE ‘%keyword%’
  • 先后顺序 LIKE ‘%keyword1%keyword2%’

使用字符串函数

  • locate函数：Locate(substr,str) > 0，表示sub字符串包含str字符串，并返回位置，从1开始；Locate(substr,str) = 0，表示sub字符串不包含str字符串。
  • instr(filed,str)函数：返回str子字符串在filed字符串的第一次出现位置，为0则表示不包含该字符串

正则表达式

使用regexp ‘正则表达式’

1
2
//查询不以a，w，j开头的学生信息
select id,name from t where name regexp ‘^[^awj]’

LeetCode练习题

高频 SQL 50 题

查询

可回收且低脂的产品

1
2
3
select product_id 
from Products 
where low_fats = 'Y' and recyclable = 'Y';

寻找用户推荐人

1
2
3
SELECT name 
FROM Customer 
WHERE  ifnull(referee_id,1) != 2;

大的国家

1
2
3
select name, population, area 
from World 
where area>=3000000 or population>=25000000

文章浏览 I

1
2
3
select distinct author_id id 
from Views 
where author_id = viewer_id order by author_id

无效的推文

1
2
3
select tweet_id 
from Tweets 
where length(content) >15

连接

使用唯一标识码替换员工ID

1
2
3
select e1.unique_id,e.name 
from Employees e 
left join EmployeeUNI e1 on e.id= e1.id

产品销售分析 I

1
2
3
select p.product_name,s.year,s.price 
from Sales s 
left join Product p on s.product_id = p.product_id;

进店却未进行过交易的顾客

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 子查询
select customer_id, count(customer_id) as count_no_trans
from visits
where visit_id not in (select visit_id from transactions)
group by customer_id;

# 也可以使用not exists
select customer_id, count(customer_id) as count_no_trans
from visits v
where not exists (select 1 from transactions t where v.visit_id = t.visit_id)
group by customer_id;

# 也可以使用left join
select customer_id, count(customer_id) count_no_trans 
from visits v
left join transactions t on v.visit_id = t.visit_id 
where transaction_id is null
group by customer_id;

上升的温度

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 使用date_sub函数,date_add也可以
select w1.id from weather w1 
left join weather w2 
on DATE_SUB(w1.recordDate, INTERVAL 1 DAY) =w2.recordDate 
where w1.temperature>w2.temperature

# 使用datediff函数
select w2.id from Weather w1  
join  Weather w2 on datediff(w2.recordDate,w1.recordDate) = 1 
where w1.temperature < w2.temperature;

# Exists
select w1.id from Weather w1  
where 
	Exists(select 1 from weather w2 where date_sub(w1.recordDate, interval 1 day) = w2.recordDate 
and w1.temperature>w2.temperature)

每台机器的进程平均运行时间

1
2
3
4
5
6
select a.machine_id,ROUND(SUM(b.timestamp-a.timestamp)/count(*),3) as processing_time 
from Activity a 
join Activity b on a.machine_id = b.machine_id and a.process_id = b.process_id 
and a.activity_type ='start' and b.activity_type='end'
group by a.machine_id
# 此题Exists大概不可行

员工奖金

1
2
3
4
5
# join
select name ,bonus 
from employee e 
left join bonus b on e.empId = b.empId 
where bonus<1000 or bonus is null

学生们参加各科测试的次数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# join
select st.student_id, st.student_name,su.subject_name ,count(e.subject_name) as attended_exams 
from Students st 
cross join Subjects su 
left join Examinations e on st.student_id = e.student_id and su.subject_name = e.subject_name
group by st.student_id, su.subject_name order by st.student_id ,su.subject_name

# with语句
WITH student_with_sbjects AS (
    SELECT s1.student_id, s1.student_name, s2.subject_name
    FROM Students s1 CROSS JOIN Subjects s2
),
exam_count AS (
    SELECT student_id,subject_name,COUNT(*) as attended_exams
    FROM Examinations
    GROUP BY student_id,subject_name
)
SELECT s.student_id,s.student_name,s.subject_name,IFNULL(e.attended_exams, 0) AS attended_exams
FROM student_with_sbjects s
LEFT JOIN exam_count e 
ON s.student_id = e.student_id AND s.subject_name = e.subject_name
ORDER BY s.student_id,s.subject_name

至少有5名直接下属的经理

1
2
3
4
5
6
7
8
#join
select e1.name from Employee e1 join Employee e2 on e1.id =e2.managerId 
group by e2.managerId having count(*) >=5

# 使用exists会很慢
select e1.name from Employee e1 
where exists 
(select 1 from Employee e2 where e1.id = e2.managerId group by e2.managerId having  count(*)>=5)

确认率

1
2
3
4
5
6
select s.user_id,
	round(ifNull(sum(c.action ="confirmed"),0)/count(*),2)
	as confirmation_rate
from Signups s 
left join Confirmations c on s.user_id = c.user_id
group by s.user_id

聚合函数

有趣的电影

1
2
3
4
select * 
from cinema 
where id%2=1 and description != 'boring' 
order by rating desc

平均售价

1
2
3
4
5
6
7
select Prices.product_id,
ifnull(ROUND(SUM(price*units)/SUM(units),2),0) 
as average_price
from UnitsSold 
right join Prices on Prices.product_id = UnitsSold.product_id 
and purchase_date between start_date and end_date
group by Prices.product_id

项目员工 I

1
2
3
4
5
select p.project_id,
Round(avg(e.experience_years),2) as average_years
from Project p
left join Employee e on p.employee_id=e.employee_id
group by p.project_id

各赛事的用户注册率

1
2
3
4
select contest_id,
ROUND((count(*)/(select count(*) from Users))*100,2) as percentage 
from Register group by contest_id
order by percentage desc,contest_id asc

查询结果的质量和占比

1
2
3
4
5
6
select query_name,
ROUND(avg(rating/position),2) as quality, 
ROUND(sum(case when rating<3 then 1 else 0 end) / count(result) * 100,2) as poor_query_percentage
from Queries 
group by query_name 
having query_name is not null

每月交易 I

1
2
3
4
5
6
7
select date_format(trans_date,'%Y-%m') as month,country,
count(*) as trans_count, 
sum(if(state='approved',1,0)) as approved_count,
sum(amount) as trans_total_amount,
sum(if (state='approved',amount,0)) as approved_total_amount 
from Transactions 
group by month,country

即时食物配送 II

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 窗口函数+子查询
select round((sum(if(order_date=customer_pref_delivery_date, 1, 0))/count(*))*100, 2) as immediate_percentage 
from (
    select *,
        row_number() over(partition by customer_id order by order_date) as first_order
    from Delivery
) A 
where A.first_order = 1

# join
select 
round(100* sum(case when d1.order_date=d1.customer_pref_delivery_date and d1.order_date = d2.first_order then 1 else 0 end) /count(distinct d1.customer_id),2) 
as immediate_percentage 
from Delivery d1 left join
(select customer_id, min(order_date) as first_order 
from Delivery group by customer_id) d2 
on d1.customer_id =d2.customer_id

游戏玩法分析 IV

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# join
select 
round(sum(case when a1.event_date = DATE_ADD(a2.first_login, interval 1 day) then 1 else 0 end)/count(distinct a1.player_id),2) as fraction
from Activity a1 left join 
(select player_id, min(event_date) AS first_login from Activity group by player_id) a2
on a1.player_id = a2.player_id

# 在连接的时候筛选
SELECT 
  ROUND(COUNT(a.player_id) /COUNT(m.player_id),2) AS fraction 
FROM
  (SELECT player_id, MIN(event_date) AS 'firstD' from Activity Group by player_id) m
LEFT JOIN 
  Activity a
ON 
  m.player_id = a.player_id AND date(m.firstD) = SUBDATE(date(a.event_date), interval 1 day)

# 使用 with 搭配 窗口函数

with FirstLogin as (
    select player_id,
           event_date,
           min(event_date) over (partition by player_id) as first_login,
           lead(event_date) over (partition by player_id order by event_date) as next_login
    from Activity
)
select 
    round(sum(case when next_login = date_add(first_login, interval 1 day) then 1 else 0 end) / count(distinct player_id), 2) as fraction
from FirstLogin
where event_date = first_login;

排序和分组

每位教师所教授的科目种类的数量

1
2
3
select teacher_id,count(distinct subject_id) as cnt 
from Teacher 
group by teacher_id

查询近30天活跃用户数

1
2
select activity_date as day ,count(distinct user_id) as active_users from Activity 
where activity_date between "2019-06-28" and "2019-07-27" group by activity_date

销售分析III

1
2
3
4
5
select product_id ,product_name 
from Product 
where product_id in
(select distinct product_id from Sales group by product_id 
 having count(if(sale_date not between "2019-01-01" and "2019-03-31",1,null))=0 )

超过5名学生的课

1
2
3
select class from Courses 
group by class 
having count(student)>=5

求关注者的数量

1
2
3
4
select user_id,
count(distinct follower_id) as followers_count 
from Followers 
group by user_id

只出现一次的最大数字

1
2
3
4
5
select ifnull((select num from MyNumbers
group by num
having count(num) = 1
order by num desc
limit 1),null) as num

买下所有产品的客户

1
2
3
select customer_id from Customer 
group by customer_id 
having count(distinct product_key) = (select count(*) from Product)

高级查询和连接

每位经理的下属员工数量

1
2
3
4
5
6
7
8
9
10
select 
e1.employee_id,
e1.name,
count(e2.reports_to) as reports_count,
round(avg(e2.age)) as average_age
from Employees e1 
left join Employees e2 on e1.employee_id = e2.reports_to
group by e1.employee_id 
having reports_count >0
order by e1.employee_id

员工的直属部门

1
2
3
4
5
6
7
8
9
select employee_id,department_id
from Employee 
where primary_flag = "Y"
group by employee_id
union
select employee_id,department_id
from Employee 
group by employee_id
having count(department_id) = 1

判断三角形

1
2
3
select x,y,z ,
if(x+y>z and y+z>x and x+z>y,"Yes","No") as triangle 
from Triangle

连续出现的数字

1
2
3
4
5
SELECT distinct a.num as ConsecutiveNums
from Logs as a 
left join Logs as b on a.id = b.id-1
left join Logs as c on a.id = c.id-2
where a.num = b.num and b.num = c.num

指定日期的产品价格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 注意这种写法是错误的，因为case并不能起到组函数的作用，它会逐一检查每一行，可能会记录覆盖。
WITH prices AS (
    SELECT 
        product_id, 
        new_price, 
        change_date,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS ranking 
    FROM Products
)
SELECT 
    p.product_id,
    CASE 
        WHEN p.change_date > '2019-08-16' THEN 10 
        ELSE (
            SELECT pr.new_price 
            FROM prices pr 
            WHERE pr.product_id = p.product_id AND pr.ranking = 1
        )
    END AS price
FROM 
    Products p
GROUP BY 
    p.product_id
    
# 正确
select product_id,new_price as price
from (select *,row_number() over (partition by product_id order by change_date desc)
as ranking from Products where change_date<="2019-08-16") as P where ranking = 1
union
select product_id,10 from Products where product_id not in (select product_id from Products where change_date<="2019-08-16")


最后一个能进入巴士的人

1
2
3
4
select person_name 
from 
(select *,sum(weight) over (order by turn) as total_weight from Queue) q 
where q.total_weight<=1000 order by q.total_weight desc limit 1

按分类统计薪水

1
2
3
4
5
select "Low Salary" as category,sum(income<20000) as accounts_count from Accounts
union all
select "Average Salary" as category,sum(income>=20000 and income<=50000) as accounts_count from Accounts
union all
select "High Salary" as category,sum(income>50000) as accounts_count from Accounts

子查询

上级经理已离职的公司员工

1
2
3
4
5
select employee_id 
from Employees 
where salary <30000 
and manager_id not in (select employee_id from Employees)
order by employee_id

换座位

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
select s1.id,ifnull(s2.student,s1.student) as student from Seat s1 left join
(select id ,student,if(id%2=0,id-1,id+1) as change_id from Seat ) s2  
on s1.id = s2.change_id

# 窗口函数
SELECT
    id,
    CASE 
        WHEN id % 2 = 0 THEN prev_student
        WHEN id % 2 = 1 THEN next_student
    END AS student
FROM (
    SELECT
        id,
        student,
        LAG(student, 1, student) OVER (ORDER BY id) AS prev_student,
        LEAD(student, 1, student) OVER (ORDER BY id) AS next_student
    FROM Seat
) AS subquery;

# 三次自连接
SELECT
    s1.id,
CASE
        WHEN s1.id % 2 = 1 and s2.student is not null THEN
        s2.student 
        WHEN s1.id % 2 = 1 and s2.student is null THEN
        s1.student 
        ELSE s3.student 
    END student 
FROM
    Seat s1
    LEFT JOIN Seat s2 ON s1.id = s2.id - 1
    LEFT JOIN Seat s3 ON s1.id = s3.id + 1

电影评分

1
2
3
4
5
6
7
8
(select u.name as results from MovieRating mr left join Users u on mr.user_id=u.user_id
group by mr.user_id order by count(mr.movie_id) desc ,u.name limit 1)
union all
(select m.title as results from MovieRating mr left join Movies m on 
mr.movie_id = m.movie_id
where month(created_at) = 2 and year(created_at) =2020
group by mr.movie_id
order by AVG(mr.rating) desc,m.title limit 1)

2016年的投资

1
2
3
select Round(sum(tiv_2016),2) as tiv_2016 from Insurance 
where pid in (select pid from Insurance group by lat,lon having count(pid) = 1) 
and pid not in (select pid from Insurance group by tiv_2015 having count(pid) = 1)

部门工资前三高的所有员工

1
2
3
4
5
6
7
select Department,Employee,Salary from
(
    select d.name as Department,e.name as Employee,e.salary as Salary 
    ,DENSE_RANK() over (partition by d.id order by salary desc) as order_salary 
    from Employee e left join department d on e.departmentId = d.id
) p 
where order_salary<=3

字符串函数 / 正则表达式

修复表中的名字

1
2
3
select user_id ,concat(upper(substr(name,1,1)),lower(substr(name,2))) as name 
from Users
order by user_id

患某种疾病的患者

1
2
3
4
5
select * from Patients where conditions regexp '\\bDIAB1.*'

# \\b：这是一个单词边界，确保匹配的是以 DIAB1 开头的单词。由于在 SQL 查询中反斜杠需要转义，因此使用两个反斜杠 \\ 来表示一个实际的反斜杠 \。
# .*：表示匹配零个或多个任意字符，意味着 DIAB1 后面可以跟随任何内容（或没有内容）。
# 具体来说，它会匹配以下情况：DIAB1 出现在 conditions 列的开头。DIAB1 前面有空格或其他分隔符。

删除重复的电子邮箱

1
2
3
delete from Person where id not in (
    select a.id from (select min(id) as id from Person group by email) as a
)

第二高的薪水

1
2
3
select (
    select distinct salary from employee order by salary desc limit 1,1
) as SecondHighestSalary;

按日期分组销售产品

1
2
3
4
5
6
select sell_date,
count(distinct product) as num_sold,
group_concat(distinct product order by product separator ",")  as products 
from Activities 
group by sell_date 
order by sell_date

列出指定时间段内所有的下单产品

1
2
3
4
5
6
7
select p.product_name,
sum(o.unit) as unit 
from Orders o 
left join Products p on o.product_id  = p.product_id
where Month(order_date) = 2 and year(order_date) =2020
group by p.product_id 
having unit>=100

查找拥有有效邮箱的用户

1
2
3
4
5
6
7
8
9
select * 
from Users 
where mail regexp '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$'
# ^：匹配字符串的开头。
# [a-zA-Z]：匹配一个字母字符（大写或小写），即邮箱地址必须以一个字母开头。
# [a-zA-Z0-9_.-]*：匹配零个或多个字母、数字、下划线 _、点 . 或连字符 -。这表示邮箱地址的本地部分（在 @ 之前的部分）可以包含这些字符。
# \@：匹配一个 @ 字符。由于 @ 是一个特殊字符，需要通过 \\ 进行转义。在 SQL 中，反斜杠本身也需要转义，因此这里用了 \\@。
# leetcode\.com：匹配 leetcode.com 字符串。由于点 . 在正则表达式中也是一个特殊字符（表示匹配任意单个字符），所以需要用 \\. 进行转义。在 SQL 中，反斜杠本身也需要转义，因此这里用了 \\.com。
# $：匹配字符串的结尾。