Mysql常规27种sql查询语句_mysql查询语句

Mysql常规27种sql查询语句

话不多说，直接上干货

1、查询部分列
select 列名 from 表名
例：员工表中查询员工编号、名字、邮箱
select employee_id,employee_name,employee_email from t_employee;
1
2
3
4

2、查询所有列
例：查询所有员工信息
select * from t_employee;
1
2
3

3、对列中的数据进行运算
例：查询员工编号、姓名、月薪、年薪
select employee_id,employee_name,employee_salary,employee_salary*12;
注：算术运算符：+(加)、-(减)、*(乘)、/(除);%为占位符而非模运算符
1
2
3
4

4、去重查询DISTINCT 列名
例：查询员工表中所有的经理ID
select distinct manager_id from t_employee;
1
2
3

5、排序查询
排序规则
ASC  升序
DESC 降序
select 列名 from 表名 order by 排序列 排序规则
依据单列排序：
例：查询员工编号、姓名、月薪；薪资按高到低排序
select employee_id,employee_name,employee_salary from t_employee order by employee_salary DESC;
依据多列排序：
例：
查询员工编号、姓名、月薪。按照薪资降序排列，薪资相同时候按照编号进行升序排列
select employee_id,employee_name,employee_salary from t_employee
order by employee_salary DESC,employee_id ASC;
1
2
3
4
5
6
7
8
9
10
11
12
13

6、条件查询where
select 列名 from 表名 where 条件
补充：where查询条件筛选符号条件的查询结果，为布尔表达式
例：查询员工为"rich_wang"的员工信息
select * from t_employee where employee_name = "rich_wang"
1
2
3
4
5

7、等值判断(=)
查询薪资是11000的员工信息(编号、名字、薪资)
select employee_id,employee_name,employee_salary
from t_employee
where employee_salary = 11000;
1
2
3
4
5

8、逻辑判断(and,or,not)
例：
查询薪资是11000且提成是0.3的员工信息(编号、名字、薪资)
select employee_id,employee_name,employee_salary
from t_employee
where employee_salary = 11000 and employee_pct = 0.3;
or和not同理
1
2
3
4
5
6
7

9、不等值判断(>,<,>=,<=,!=,<>)
例：
查询员工薪资在10000-20000之间的员工信息(编号、名字、薪资)
select employee_id,employee_name,employee_salary
from t_employee
where employee_salary>=10000 and employee_salary<=20000
其他几个同理
1
2
3
4
5
6
7

10、区间判断(between and)
例：查询员工薪资在10000-20000之间的员工信息
select employee_id,employee_name,employee_salary
from t_employee
where employee_salary between 10000 and 20000;
说明：区间判断中，小值在前，大值在后，否则得不到正确结果；
1
2
3
4
5
6

11、NULL值判断(IS NULL,IS NOT NULL)
列名 IS NULL
列名 IS NOT NULL
例：查询没有提成的员工信息(编号、名字、薪资、提成)
select employee_id,employee_name,employee_salary,employee_pct
from t_employee
where employee_pct IS NULL;
IS NOT NULL同理
注：不能是=NULL,要拿到NULL值需要IS NULL和IS NOT  NULL来获取
1
2
3
4
5
6
7
8
9

12、枚举查询
IN (值1,值2,值3....)
例：
查询部门编号为70,80,90的员工信息(编号、名字、薪资、部门编号)
select e,manager_id,employee_name,employee_salary,employee_dpt
from t_employee
where employee_dpt in (70,80,90);
注：in的查询效率较低，推荐使用多条件拼接查询
1
2
3
4
5
6
7
8

13、模糊查询(LIKE、%)
LIKE_(单个任意字符)：列名 LIKE '王_';
%(任意长度字符)列名 LIKE '王%';
注：模糊查询只能和LIKE关键字结合使用
例：
查询名字以'王'开头的员工信息(编号、名字、薪资、部门编号)
select employee_id,employee_name,employee_salary,employee_dpt
from t_employee
where employee_name LIKE '王%';
例：
查询名字以"王"开头且长度为3的员工信息(编号、名字、薪资、部门编号)
select employee_id,employee_name,employee_salary,employee_pct
from t_employee
where employee_name LIKE '王___';
注：一个"_"占一位
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

14、分支结构查询CASE END
CASE
    WHEN 条件1 THEN 结果1
	WHEN 条件2 THEN 结果2
	WHEN 条件3 THEN 结果3
	ELSE 除以上条件外的结果
    ....
END
说明：通过使用CASE END进行条件判断，每条数据对应生成一个值，类似Java中的switch case
例：
查询员工信息(编号、名字、薪资、薪资级别<根据级别条件生成>)
select employee_id、employee_name,employee_salary,
       CASE
	       WHEN employee_salary >=10000 THEN 'A'
		   WHEN employee_salary >=8000 AND employee_salary<10000 THEN 'B'
		   WHEN employee_salary >=6000 AND employee_salary<8000 THEN 'C'
		   WHEN employee_salary >=4000 AND employee_salary<6000 THEN 'D'
		ELSE
		    'E'
		END
		AS "薪资级别"
from t_employee;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

15、时间查询-时间函数
select 时间函数(参数列表)
注：执行时间函数查询，会自动生成一张虚表(一列一行)
相关时间函数：
SYSDATE()             当前系统时间(年月日时分秒)
CURDATE()             获取当前日期(年月日)
CURTIME()             获取当前时间(时分秒)
WEEK(DATE)            获取指定日期为一年中的第几周
YEAR(DATE)            获取指定日期的年份
HOUR(TIME)            获取指定时间的小时值
MINUTE(TIME)          获取指定时间分钟值
DATEDIFF(DATE1,DATE2) 获取DATE1和DATE2之间相差的天数
ADDDATE(DATE,N)       计算DATE加上N天后的日期
例：
获取系统当前时间
select SYSDATE()
获取当前日期
select CURDATE
获取指定日期中的年份
select YEAR('2022-07-03')
指定日期之前的天数
select DATEDIFF('2021-07-03','2022-07-03')
其他时间函数同理
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

16、字符串查询-字符串函数
select 字符串函数(参数列表)
相关字符串函数：
CONCAT(str1,str2,...)        将多个字符串拼接
INSERT(str,pos,len,newStr)   将str字符串中指定的pos位置开始长度为len的内容替换为newStr
LOWER(str)                   将指定字符串str转换为小写
UPPER(str)                   将指定字符串str转换为大写
SUBSTRING(str,num,len)       将指定字符串str中从位置num开始截取len个长度的子串
例：
拼接字符串
select CONCAT('My','S','QL');//MUSQL
字符串替换
select INSERT('学习什么数据库',3,2,'MYSQL');//学习MYSQL数据库
其他几个同理
注：在mysql中字符串下标从1开始，而非像Java中从0开始
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

17、聚合函数(SUM、AVG、MAX、MIN、COUNT)
select 聚合函数(列名) from 表名
相关聚合函数
SUM()   对所有行中的单列结果求和
AVG()   平均值
MAX()   最大值
MIN()   最小值
COUNT() 求表总行数
例：
统计所有员工每月的薪资总和
select SUM(employee_salary) from t_employee
其他几个同理
注：聚合函数会自动忽略NULL值，不对NUL进行统计
1
2
3
4
5
6
7
8
9
10
11
12
13

18、分组查询GROUP BY
select 列名 from 表名 where 条件 GROUP BY 分组依据(列名)
注：分组依据，必须在where之后生效
例：
查询各部门的总人数
分析：
先按部门编号(employee_dpt)进行分组,再针对各部门人数进行统计(COUNT)
select employee_dpt AS '部门',COUNT(employee_id)
from t_employee
GROUP BY employee_dpt

查询各部门的平均工资
select  employee_dpt,AVG(employee_salary)
from t_employee
GROUP BY employee_dpt

查询各个部门、各个岗位的人数
分析：
先按部门编号进行分组，再按岗位进行分组，再对各个部门各个岗位进行COUNT
select employee_dpt,employee_job,COUNT(employee_id) AS '岗位人数'
from t_employee
GROUP BY employee_dpt,employee_job

注：分组查询中，select显示的列只能是分组依据列或者聚合函数；列，不能出现其他列！注意规避
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

19、分组过滤查询HAVING
select 列名 from 表名 where 条件 GROUP BY 分组列 HAVING 过滤规则
注：过滤规则定义对分组后的数据进行过滤
例：
统计部门编号70、80、90部门的最高工资
分析：
先按部门编号进行分组，对分组后的数据过滤出部门编号为70、80、90的部门，然后在MAX进行统计
select employee_dpt,MAX(employee_salary)
from t_employee
GROUP BY employee_dpt
HAVING employee_dpt in (70,80,90)
1
2
3
4
5
6
7
8
9
10
11

20、限定查询LIMIT
select 列名 from 表名 LIMIT 起始行,查询行数
关键字：
LIMIT offset_start,row_count 限定查询结果的起始行和总行数
限定查询一般用于分页的场景最多
例：
查询前5条数据
select * from t_employee LIMIT 0,5;
注：起始行是从0开始，代表第一行，第二个参数代表的是指定行开始查询几行

查询范围记录，表中第三条开始，查询10行
select * from t_employee LIMIT 3,10；
1
2
3
4
5
6
7
8
9
10
11
12

小结

小结：
sql语句的编写顺序：
select列名from表名where条件GROUP BY分组依据HAVING过滤条件order by排序列 排序规则 LIMIT 起始行,总条数
sql语句的执行顺序：
from->where->GROUP BY->HAVING->select->order by->LIMIT
1
2
3
4
5

21、子查询(作为条件判断)
select 列名 from 表名 where 条件(子查询结果)
例：
查询工资大于员工rich_wang的员工信息
select * from t_employee where employee_salary > (
         select employee_salary from t_employee where employee_name="rich_wang"
		 );
注：将子查询的"一行一列"的结果作为外部查询的条件做二次查询，子查询结果得到一行一列才能作为外部查询的判断条件

21、子查询(作为枚举查询条件)
select 列名 from 表名 where 列名 in (子查询结果)
例：
查询与员工rich_wang在同一个部门的员工信息，其中rich_wang所属多个部门
select * from t_employee where employee_dpt in (
        select employee_dpt from t_employee where employee_name = "rich_wang"
		);
注：将多行一列的子查询结果作为外部查询枚举查询条件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

22、子查询(ANY和ALL)
查询工资高于60部门的所有员工信息
高于部分：
select * from t_employee where employee_salary > ANY(
        select employee_salary from t_employee where employee_dpt=60
);
高于所有：
select * from t_employee where employee_salary > ALL(
        select employee_salary from t_employee where employee_dpt=60
);
注：当子查询结果形式为多行单列时可使用ANY或者ALL关键字
1
2
3
4
5
6
7
8
9
10
11

23、子查询(作为一张表)
select 列名 from 子查询结果集 where 条件
例：
查询员工表中工资排名前五的员工信息(编号、名字、薪资)
select employee_id,employee_name,employee_salary from
        (select employee_id,employee_name,employee_salary 
		 from t_employee
		 order by employee_salary DESC
		) as temp
	LIMIT 0,,5
注：将多行多列的结果作为外部查询的一张表进行二次查询
1
2
3
4
5
6
7
8
9
10
11

24、合并查询UNION(应用场景较少)
select * from 表名1 UNION select * from 表2
select * from 表名1 UNION ALL select * from 表2
例：
合并2张表的结果(去除重复记录)
select * from t1 UNION select * from t2
注意：合并结果的两张表，列数必须相同！！！列的数据类型可以不同

合并2张表(保留重复记录)
select * from t1 UNION ALL select * from t2
1
2
3
4
5
6
7
8
9
10

25、表连接查询-内连接查询(INNER JOIN ON)
select 列名 from 表1 连接方式 表2 连接条件
通过员工表t_employee和岗位表t_job查询所有有部门的员工信息
sql标准写法：
select * from t_employee INNER JOIN t_job ON t_employee.job_id = t_job.job_id
mysql可这样写：
select * from t_employee,t_job where t_employee.job_id = t_job.job_id
注意：务必要指定连接条件，否则会造成笛卡尔积的结果

26、表连接查询-三表连接查询
例：
查询所有员工信息包含员工编号，姓名，部门名称，部门所在国家ID
涉及三张表：t_employee员工表，t_department部门表,t_location国家地区表
select * from t_employee e
INNER JOIN t_department d
ON e.employee_id = d.employee_id
INNER JOIN t_location l
ON d.location_id = l.location_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

说明：左外连接，是以左表作为主表，依次像右表匹配，匹配成功则返回结果，匹配不到则返回NULL填充
查询所有员工信息，以及对应的部门名称(没有部门的员工，也在查询结果中，部门以NULL填充)
涉及员工表和部门表
select e.employee_id,e.employee_name,e.employee_salary,d.department_name from t_employee e
LEFT JOIN t_department d
ON e.department_id = d.department_id
注：实际开发时根据需求选择主表
1
2
3
4
5
6
7

27、表连接查询-右外连接查询(RIGHT JOIN ON)
说明：右外连接，是以右表作为主表，依次像左表匹配，匹配成功则返回结果，匹配不到则返回NULL填充
查询所有部门信息，以及此部门中的所有员工信息(没有员工的部门，也在查询结果中，员工信息以NULL填充)
select e.employee_id,e.employee_name,e.employee_salary,d.department_name from t_employee e
RIGHT JOIN t_department d
ON e.department_id = d.department_id
注：实际开发时根据需求选择主表
1
2
3
4
5
6
7