Mysql常规27种sql查询语句

话不多说,直接上干货

1、查询部分列
select 列名 from 表名
例:员工表中查询员工编号、名字、邮箱
select employee_id,employee_name,employee_email from t_employee;
2、查询所有列
例:查询所有员工信息
select * from t_employee;
3、对列中的数据进行运算
例:查询员工编号、姓名、月薪、年薪
select employee_id,employee_name,employee_salary,employee_salary*12;
注:算术运算符:+()-()*()/();%为占位符而非模运算符
4、去重查询DISTINCT 列名
例:查询员工表中所有的经理ID
select distinct manager_id from t_employee;
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;
6、条件查询where
select 列名 from 表名 where 条件
补充:where查询条件筛选符号条件的查询结果,为布尔表达式
例:查询员工为"rich_wang"的员工信息
select * from t_employee where employee_name = "rich_wang"
7、等值判断(=)
查询薪资是11000的员工信息(编号、名字、薪资)
select employee_id,employee_name,employee_salary
from t_employee
where employee_salary = 11000;
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同理
9、不等值判断(>,<,>=,<=,!=,<>)
例:
查询员工薪资在10000-20000之间的员工信息(编号、名字、薪资)
select employee_id,employee_name,employee_salary
from t_employee
where employee_salary>=10000 and employee_salary<=20000
其他几个同理
10、区间判断(between and)
例:查询员工薪资在10000-20000之间的员工信息
select employee_id,employee_name,employee_salary
from t_employee
where employee_salary between 10000 and 20000;
说明:区间判断中,小值在前,大值在后,否则得不到正确结果;
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来获取
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的查询效率较低,推荐使用多条件拼接查询
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 '王___';
注:一个"_"占一位
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;
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')
其他时间函数同理
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开始
17、聚合函数(SUM、AVG、MAX、MIN、COUNT)
select 聚合函数(列名) from 表名
相关聚合函数
SUM()   对所有行中的单列结果求和
AVG()   平均值
MAX()   最大值
MIN()   最小值
COUNT() 求表总行数
例:
统计所有员工每月的薪资总和
select SUM(employee_salary) from t_employee
其他几个同理
注:聚合函数会自动忽略NULL值,不对NUL进行统计
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显示的列只能是分组依据列或者聚合函数;列,不能出现其他列!注意规避
19、分组过滤查询HAVING
select 列名 from 表名 where 条件 GROUP BY 分组列 HAVING 过滤规则
注:过滤规则定义对分组后的数据进行过滤
例:
统计部门编号708090部门的最高工资
分析:
先按部门编号进行分组,对分组后的数据过滤出部门编号为708090的部门,然后在MAX进行统计
select employee_dpt,MAX(employee_salary)
from t_employee
GROUP BY employee_dpt
HAVING employee_dpt in (70,80,90)
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

小结

小结:
sql语句的编写顺序:
select列名from表名where条件GROUP BY分组依据HAVING过滤条件order by排序列 排序规则 LIMIT 起始行,总条数
sql语句的执行顺序:
from->where->GROUP BY->HAVING->select->order by->LIMIT
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"
		);
注:将多行一列的子查询结果作为外部查询枚举查询条件
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关键字
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
注:将多行多列的结果作为外部查询的一张表进行二次查询
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
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
说明:左外连接,是以左表作为主表,依次像右表匹配,匹配成功则返回结果,匹配不到则返回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
注:实际开发时根据需求选择主表
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
注:实际开发时根据需求选择主表
Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐