DQL,常见函数

发表于 | 分类于
字数统计: 3.6k | 阅读时长 ≈ 15

基础查询

语法：
select 查询列表 from 表名
特点：
1.查询列表可以是：表中的字段，常量值，表达式，函数；
2.查询的结果是一个虚拟的表格。

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
36
37
38
39
40
# 1.查询表中的单个字段
SELECT first_name FROM employees;

# 2.查询表中的多个字段
SELECT first_name,last_name FROM employees;

# 3.查询表中的所有字段
SELECT  * from employees;

# 4.查询常量值
SELECT 'qwer';
SELECT 666;

# 5.查询表达式
SELECT 1+2+3;
SELECT 100%3;

# 6.查询函数
SELECT VERSION();

# 7.起别名
# 方式一：使用AS
SELECT 9/2 AS 结果;
SELECT first_name AS 名,last_name AS 姓 FROM employees;
# 方式二：使用空格
SELECT salary 薪水 FROM employees;

# 8.去重:使用DISTINCT
SELECT DISTINCT department_id FROM employees;

# 9.+号的作用：只能作运算符
SELECT '22'+33; -- 55
SELECT 'qwer'+333; -- 333
SELECT null+333; -- NULL
# 字符串连接函数CONCAT(str1,str2,...)
SELECT  CONCAT(last_name, first_name) AS 全名 FROM employees;

# 10.使用IFNULL(expr,expr为null时需要返回的值)对结果为null的数据进行处理
SELECT CONCAT(salary,',',IFNULL(commission_pct,0)) AS '月薪,年奖金率' FROM employees;
# 11.ISNULL(expr) expr为null 返回0，否则返回1

条件查询

语法：
select 查询列表 from 表名 where 筛选条件
分类：
1.按条件表达式进行筛选
条件运算符：> < != <> >= <=
2.按逻辑表达式
&& || ! and or not
3.模糊查询
like：
1.一般与通配符搭配使用
2.通配符(使用\转义)：
%：表示0个或任意多个字符;
_ ：表示任意单个字符;
(not) between and： 包含边界值
in： 判断某字段的值是否属于in列表中的某一项
in列表中的元素类型要相同或兼容
is null / is not null：只能判断是否为null;
<=> : 安全等于，既能判断是否为null也能判断数字;
= 和 <>不能判断null

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 1.按条件表达式筛选
SELECT * FROM employees WHERE salary>15000;
SELECT last_name,first_name,salary FROM  employees WHERE department_id<>90;

# 2.按逻辑表达式筛选
SELECT last_name,salary,commission_pct FROM employees WHERE salary>=10000 and salary<=15000;
# 查询部门编号不是在90到110之间，或者工资高于12000的员工的信息
SELECT last_name,first_name,salary FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>12000;

# 3.模糊筛选
# 查询员工名中包含字符a的员工信息
SELECT * FROM employees WHERE first_name LIKE '%a%' ;
# 查询员工名中第三个字符为a,第四个字符为n的员工信息
SELECT * FROM employees WHERE first_name LIKE '__an%';
# 查询员工编号在100~200之间的员工信息
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 200;
# 查询员工的工种编号是IT_PROG,IT_VP,AD_PRES中的一个员工的员工名和工种编号
SELECT first_name,job_id FROM employees where job_id IN ('IT_PROG','IT_VP','AD_PRES');
# 查询没有奖金的员工信息
SELECT * FROM employees where commission_pct = NULL; -- 查询不到结果
SELECT * FROM employees WHERE commission_pct IS NULL;

排序查询

语法：select 查询列表 from 表 where 查询条件 order by 排序列表 asc/desc;
特点：1.asc:升序；desc:降序；如果不写，默认为升序
2.order by 子句支持单个字段，多个字段，表达式，函数，别名
3.order by 子句一般放在查询语句的最后面，limit子句除外

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT * FROM employees ORDER BY salary ASC; -- 升序
SELECT * FROM employees ORDER BY salary; -- 省略时默认为升序排列
SELECT * FROM employees ORDER BY salary DESC; -- 降序
# 查询部门编号>=90的员工信息，按入职时间的先后顺序进行排序
SELECT *  FROM employees  WHERE department_id>=90 ORDER BY hiredate;
# 按年薪的高低显示员工的信息和年薪（按表达式排序）
SELECT * ,salary*12*(1+IFNULL(commission_pct,0)) FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
# 按别名排序
SELECT * ,salary*12*(1+IFNULL(commission_pct,0)) AS '年薪' FROM employees ORDER BY '年薪' DESC;
# 按姓名的长度显示员工的姓名和工资（按函数值进行排序）
SELECT CONCAT(last_name,first_name) AS '姓名' ,LENGTH(CONCAT(last_name,first_name)),salary AS '工资' FROM employees ORDER BY LENGTH(姓名);
# 查询员工信息，要求先按工资的升序，再按员工编号的降序（按多个字段排序）
SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;

常见函数

概念：类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：隐藏了实现细节；提高了代码的复用性
调用：select 函数名() from 表名;
分类： 1.单行函数：作用于单行数据，返回一个结果
2.分组函数：作用于一组数据，并对一组数据返回一个值

单行函数

一、字符函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 1.LENGTH(str) 获取参数值的字节个数
SELECT LENGTH('哈哈hh'); -- 8

# 2.CONCAT(str1,str2,...) 拼接字符串

# 3.UPPER(str),LOWER(str)
SELECT CONCAT(upper(last_name),' ',lower(first_name)) AS '姓名'FROM employees;

# 4.SUBSTR(str FROM pos FOR len),substring()截取字符串(索引从1开始)
SELECT SUBSTR(last_name,1,1) FROM employees;

# 5.INSTR(str,substr) 返回子串在母串第一次出现的索引位置，如果找不到就返回0
SELECT INSTR('helloworld','world'); -- 6
SELECT INSTR('helloworld','word'); -- 0

# 6.TRIM([remstr FROM] str) 去掉首尾空格或指定字符串
# 7.LPAD(str,len,padstr) /RPAD(str,len,padstr)用指定的字符实现左/右填充指定长度
# REPLACE(str,from_str,to_str) 替换

二、数学函数

1
2
3
4
5
6
7
8
9
10
# 1.ROUND(X) 四舍五入
SELECT ROUND(-1.55); -- -2
SELECT ROUND(-1.566,2); -- -1.57
# 2.CEIL(X) 向上取整，返回>=该参数的最小整数
# 3.FLOOR(X) 向下取整，返回<=该参数的最大整数
# 4.TRUNCATE(X,D) 从小数点后D位截断
SELECT TRUNCATE(1.29,1); -- 1.2
# 5.MOD(N,M) 取余:符号与被除数相同
SELECT MOD(-10,3); -- -1
SELECT MOD(10,-3); -- 1

三、日期函数

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
# NOW() 返回当前系统日期+时间
SELECT NOW(); -- 2020-02-23 11:27:48
# CURDATE() 返回当前系统的日期
SELECT CURDATE(); -- 2020-02-23
# CURTIME() 返回当前系统的事件
SELECT CURTIME(); -- 11:30:11

# 获取指定的年，月，日，时，分，秒
SELECT YEAR(NOW()) AS 年份;
SELECT YEAR('1999-2-2'); -- 1999
SELECT MONTH(NOW()) AS 月;
SELECT MONTHNAME(NOW()); -- February
SELECT DAY(NOW()) AS  日;
SELECT HOUR(CURTIME()) AS 时; 
SELECT MIN(CURTIME()) AS 分;

# STR_TO_DATE(str,format) 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1999-2-29','%Y-%c-%d'); -- 1999-02-29
SELECT STR_TO_DATE('99-02-29','%y-%m-%d');

#  DATE_FORMAT(date,format) 将日期转换成字符
SELECT DATE_FORMAT(now(),'%Y年%m月%d日');-- 2020年02月23日
SELECT DATE_FORMAT(now(),'%y年%c月%d日'); -- 20年2月23日
# 查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name AS 姓名,DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 入职日期 FROM employees WHERE commission_pct IS NOT NULL;

四、其他函数

1
2
3
SELECT VERSION();
SELECT DATABASE();
SELECT USER();

五、流程控制函数

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
# IF(expr1,expr2,expr3)
SELECT IF(2>1,'true','false');

# case 用法一：相当于java中的switch()
/*
CASE 要判断的字段或表达式
	WHEN 常量1 THEN
		要显示的值1(或语句1;)
	ELSE
		要显示的值n(或语句n;)
	END;
*/
/*查询员工工资，要求：
部门号=30，显示工资为1.1倍；
部门号=40，显示工资为1.2倍；
部门号=50，显示工资为1.3倍；
*/
SELECT salary	AS 原始工资,department_id AS 部门号,
CASE department_id
	WHEN 30 THEN
		salary *1.1
	WHEN 40 THEN
		salary*1.2
	WHEN 50 THEN
		salary*1.3
	ELSE salary
END AS 最终工资 FROM employees;
# case用法二：相当于java中if()  else if() else 
/*
CASE 
	WHEN 条件1 THEN
		要显示的值1(或语句1;)
	WHEN 条件2 THEN
		要显示的值2(或语句2;)
	ELSE
		要显示的值n(或语句n;)
END CASE;
*/
/*查询员工的工资情况
如果工资>20000,显示A级别；
如果工资>15000,显示B级别；
如果工资>10000,显示C级别；
其余的显示D级别
*/
SELECT salary AS '工资',
CASE 
	WHEN salary>20000 THEN 'A'
	WHEN salary>15000 THEN 'B'
	WHEN salary>10000 THEN 'C'
	ELSE 'D'
END AS '工资等级' FROM employees;

分组函数

作用：用作统计使用，又称聚合函数，统计函数，组函数
特点：
1.sum，avg一般处理数值型数据；max，min，count可以处理任何类型
2.以上分组函数都忽略null值
3.可以和discount()搭配使用实现去重
4.一般使用count(*)统计行数，没有记录返回0
5.和分组函数一起查询的字段必须是group by后面的字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# sum() 求和
SELECT sum(salary) FROM employees;
# avg() 求平均值
SELECT AVG(salary) FROM employees;
# max() 求最大值;min() 求最小值
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
# count() 计算个数
SELECT COUNT(employee_id) FROM employees;
SELECT COUNT(*) FROM employees;

# 查询员工表中最大入职时间和最小入职时间的相差天数
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;
# 查询部门编号为60的员工个数
SELECT COUNT(*) FROM employees WHERE department_id = 60;

分组查询

语法：
select 分组函数,列(要求出现在group by的后面)
from 表
where 筛选条件
group by 分组的列表
order by 子句
特点：分组查询中的筛选条件分为两类
分组前筛选：数据源在原始表中，位于group by子句前面，使用where关键字；
分组后筛选：数据源在分组后的结果集中，位于group by子句后面，使用having关键字
1.分组函数作条件肯定放在having后
2.能分组前筛选尽量放在group by前面

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 查询每个工种的最高工资
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
# 查询每个位置上的部门个数
SELECT COUNT(*),location_id FROM departments GROUP BY location_id;
# 查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id FROM employees WHERE email LIKE "%a%" GROUP BY department_id;
# 查询员工数>2的部门
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;
# 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL HAVING MAX(salary)>12000;
# 查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪一个，以及最低工资
SELECT MIN(salary),manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000 
# 查询每个部门每个工种的员工的平均工资，并按平均工资的高低显示 
SELECT AVG(salary),job_id,department_id FROM employees GROUP BY job_id,department_id ORDER BY AVG(salary) DESC;

连接查询

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
# 一、内连接
/*
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件
*/
# 1.等值
# 查询员工名、部门名
SELECT last_name,department_name FROM departments d INNER JOIN employees e
ON d.department_id = e.department_id;
# 查询名字中包含e的员工名和工种名
SELECT last_name,job_title from employees e INNER JOIN jobs j
ON e.job_id= j.job_id where e.last_name LIKE "%e%";
# 查询部门个数>3的城市名和部门个数
SELECT city,COUNT(*) 部门个数 FROM locations l INNER JOIN departments d 
ON l.location_id=d.location_id GROUP BY city HAVING COUNT(*)>3; 
# 查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序排序
SELECT department_name,COUNT(*) 部门员工个数 FROM departments d INNER JOIN employees e
ON d.department_id = e.department_id GROUP BY department_name HAVING COUNT(*)>3 ORDER BY COUNT(*) DESC;

# 2.非等值
# 查询员工的工资等级
SELECT salary,grade_level FROM employees e JOIN job_grades j 
ON salary BETWEEN j.lowest_sal AND j.highest_sal;

#3.自连接
# 查询员工名、上级的名字
SELECT e.last_name,m.last_name FROM employees e JOIN employees m
ON e.manager_id = m.employee_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 二、外连接
/*
用于查询一个表中有，另一个表中没有的字段
特点：
1.外连接的查询结果为主表中的所有记录
		如果从表中有和它匹配的，则显示匹配的值
		否则显示null
		外连接的查询结果=内连接的结果+主表中有 而 从表中没有的记录
2.左外连接，left [outer] join   左边的是主表
  右外连接，right [outer] join  右边的是主表
3.全外连接=内连接的结果+表1中有单表2中没有的+表2中有的而表1中没有的
*/
# 查询没有员工的部门信息
SELECT d.* FROM departments d LEFT OUTER JOIN employees e
ON d.department_id=e.department_id WHERE employee_id IS NOT NULL;

子查询

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
36
37
38
39
40
41
42
43
44
45
46
# 子查询
/*
出现在其他语句内部的select语句，称为子查询
*/
# where或having后面
# 查询工资比 Abel高的员工信息
SELECT * FROM employees WHERE salary>(SELECT salary FROM employees WHERE last_name="Abel");
# 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary) FROM  employees GROUP BY department_id 
HAVING MIN(salary)>(SELECT MIN(salary) FROM employees WHERE department_id=50);
# 查询location_id在1400或1700的部门中的所有员工的姓名
SELECT last_name FROM employees WHERE department_id IN (SELECT department_id FROM	departments  WHERE location_id IN (1400,1700));

# select后面
# 查询每个部门的人数
SELECT d.*,(SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id) 个数
FROM departments d;

# from后面
# 查询每个部门的平均工资的等级
SELECT ag_dep.*,g.grade_level FROM 
(SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id) ag_dep 
INNER JOIN job_grades g ON  ag_dep.ag BETWEEN lowest_sal AND highest_sal;
# 子查询
/*
出现在其他语句内部的select语句，称为子查询
*/
# where或having后面
# 查询工资比 Abel高的员工信息
SELECT * FROM employees WHERE salary>(SELECT salary FROM employees WHERE last_name="Abel");
# 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary) FROM  employees GROUP BY department_id 
HAVING MIN(salary)>(SELECT MIN(salary) FROM employees WHERE department_id=50);
# 查询location_id在1400或1700的部门中的所有员工的姓名
SELECT last_name FROM employees WHERE department_id IN (SELECT department_id FROM	departments  WHERE location_id IN (1400,1700));

# select后面
# 查询每个部门的人数
SELECT d.*,(SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id) 个数
FROM departments d;

# from后面
# 查询每个部门的平均工资的等级
SELECT ag_dep.*,g.grade_level FROM 
(SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id) ag_dep 
INNER JOIN job_grades g ON  ag_dep.ag BETWEEN lowest_sal AND highest_sal;

分页查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 分页查询
/*
分页提交sql请求
select 查询列表 from 表名 limit 起始索引(从0开始)，结果的数目
limit语句放在查询语句的最后 
当前页 pageNum , 每页的条目数pageSize
select 查询列表 from 表名 limit (pageNum-1)*pageSize,pageSize;
*/
# 查询前5条员工信息
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM	employees	LIMIT 5;
# 查询第11条~第25条的信息
SELECT * FROM employees LIMIT 11,15;
# 查询有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * FROM employees WHERE	commission_pct IS NOT NULL ORDER BY salary	DESC LIMIT 0,10;

联合查询

将多条查询语句的结果合并在一起
语法：
select 查询列表1 from 表名 union select 查询列表2 from 表名
特点：
1、要查询的结果来自多个表，且多个表之间没有联系
2、要求查询列表1与查询列表2的列数相等
3、要求多条查询语句的查询的每一列的类型和顺序最好一致
4、union关键字默认去重，使用union all 可以包含重复项