DQL查询、筛选、排序、分组

一、基础查询语句

1.查询所有列
语法：select * from 表名;或select 所有列名 from 表名;

SELECT * FROM t_mysql_employees;

2.查询特定列
语法：select 列名 from 表名;

SELECT first_name,last_name FROM t_mysql_employees;

3.别名
语法一：select 列名 空格 别名 from 表名;

select first_name 姓,last_name 名 from t_mysql_employees;

语法二：select 列名 as 别名 from 表名;

select first_name as 姓,last_name as 名 from t_mysql_employees;

4.去重
语法：select distinct 列名 from 表名;

SELECT DISTINCT department_id FROM t_mysql_employees;

5.+号的作用
java中的+号：
运算符，两个操作数都为数值型。
连接符，只要有一个操作数为字符串。

MySQL中的+号：运算符

SELECT 10+50;#结果为190

SELECT '10'+90;#结果为100

SELECT 'john'+90;#结果为90,字符串换算为0

SELECT 100+NULL;#只要一方为Null,则结果为Null

拼接字符串：
语法：select concat(‘字符串A’,‘字符串B’) from 表名;#结果为AB

SELECT 
	CONCAT(last_name,first_name) 姓名
FROM
	t_mysql_employees;
	

二、过滤及排序

语法：select 列名 from 表名 where 筛选条件;

1.运算符筛选
简单条件运算符：> < = != <> >= <=

#查询工资>10000的员工信息
SELECT
	* 
FROM
	t_mysql_employees 
WHERE
	salary > 10000;
	

#查询部门编号不等于90号的员工名和部门编号
SELECT
	department_id,
	CONCAT( first_name, last_name ) 姓名 
FROM
	t_mysql_employees 
WHERE
	department_id <> 90;
	

2.逻辑筛选
逻辑运算符：
&&和and：两个条件都为true，结果为true，反之为false
||或or： 只要有一个条件为true，结果为true，反之为false
!或not： 如果连接的条件本身为false，结果为true，反之为false

#查询工资在10000到20000之间的员工名、工资以及奖金
SELECT
	CONCAT( first_name, last_name ) 姓名,
	salary 工资,
	commission_pct 奖金 
FROM
	t_mysql_employees 
WHERE
	salary >= 10000 
	AND salary <= 20000;


#查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT 
	* 
FROM 
	t_mysql_employees 
WHERE 
	( department_id < 90 OR department_id > 110 ) 
	OR 
	salary > 15000;
	

3.模糊查询筛选
关键词like:
%，任意多个字符，包括0个字符
_，任意单个字符

#查询员工名中包含字符a的员工信息
SELECT
	* 
FROM
	t_mysql_employees 
WHERE
	CONCAT( first_name, last_name ) LIKE '%a%';
	

#查询员工名中第三个字符为e，第五个字符为a的员工名和工资
SELECT
	CONCAT( first_name, last_name ) 姓名,
	salary 
FROM
	t_mysql_employees 
WHERE
	CONCAT( first_name, last_name ) LIKE '__e_a%';
	

关键词between…and…：
区间查询，包含临界值，两个临界值不要调换顺序

#查询员工编号在100到120之间的员工信息
SELECT
	* 
FROM
	t_mysql_employees 
WHERE
	employee_id BETWEEN 100 
	AND 120;
	

关键词in：
判断某字段的值是否属于in列表中的某一项，in列表的值类型必须一致或兼容，不支持通配符

#查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
	CONCAT( first_name, last_name ) 姓名,
	job_id 
FROM
	t_mysql_employees 
WHERE
	job_id IN ( 'IT_PROT', 'AD_VP', 'AD_PRES' );
	

关键字is null：
is null或is not null 可以判断是否为null值，=或<>不能用于判断null值

#查询没有奖金的员工名和奖金率
SELECT
	CONCAT( first_name, last_name ) 姓名,
	commission_pct 
FROM
	t_mysql_employees 
WHERE
	commission_pct IS NULL;
	

安全等于<=>：既可以判断NULL值，又可以判断普通的数值，可读性较低。

4.排序
asc代表的是升序，可以省略
desc代表的是降序

语法：select 查询列表 from 表名【where 筛选条件】order by 排序的字段或表达式;

关键字Order by：
order by子句可以支持 单个字段、别名、表达式、函数、多个字段
order by子句在查询语句的最后面，除了limit子句

#按单个字段排序：查询员工姓名及工资，并按工资降序排序
SELECT
	CONCAT( first_name, last_name ) 姓名,
	salary 
FROM
	t_mysql_employees 
ORDER BY
	salary DESC;

#添加筛选条件再排序：查询部门编号>=90的员工信息，并按员工编号降序
SELECT
	* 
FROM
	t_mysql_employees 
WHERE
	department_id >= 90 
ORDER BY
	employee_id DESC;
	
#按表达式排序：查询员工信息 按年薪降序
SELECT
	*,
	salary * 12 * ( 1+ IFNULL( commission_pct, 0 ) ) 
FROM
	t_mysql_employees 
ORDER BY
	salary * 12 * ( 1+ IFNULL( commission_pct, 0 ) ) DESC;

#按多个字段排序：要求先按工资降序，再按employee_id升序
SELECT
	* 
FROM
	t_mysql_employees 
ORDER BY
	salary DESC,
	employee_id ASC;
	

三、分组查询

概念：分组函数作用于一组数据，并对一组数据返回一个值。
1.分组函数
分类：sum求和、avg平均值、max最大值 、min最小值 、count计算个数
特点：

1. sum、avg一般用于处理数值型
2. max、min、count可以处理任何类型
3. 可以和distinct搭配实现去重的运算
4. 一般使用count(*)用作统计行数
5. 和分组函数一同查询的字段要求是group by后的字段

count(1)和count(字段)的主要区别：
count(1) 会统计表中的所有的记录数，包含字段为null 的记录。
count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。

#求工资总数
SELECT SUM(salary) FROM t_mysql_employees;
#求工资平均值
SELECT AVG(salary) FROM t_mysql_employees;
#求工资最低值
SELECT MIN(salary) FROM t_mysql_employees;
#求工资最高值
SELECT MAX(salary) FROM t_mysql_employees;
#有工资的个数
SELECT COUNT(salary) FROM t_mysql_employees;


#查询工资去重后的总数和工资总数
SELECT
	SUM( DISTINCT salary ),
	SUM( salary ) 
FROM
	t_mysql_employees;
	
#工资去重后的个数和工资总个数
SELECT
	COUNT( DISTINCT salary ),
	COUNT( salary ) 
FROM
	t_mysql_employees;
	

2.group by
语法：select 列名 from 表【where 筛选条件】group by 分组的字段【order by 排序的字段】;

#查询每个工种的员工平均工资
SELECT
	AVG( salary ),
	job_id 
FROM
	t_mysql_employees 
GROUP BY
	job_id;

特点：
1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选。分组前筛选在group by前用where关键字，分组后筛选在group by后用having关键字。
3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用

#分组前筛选：查询邮箱中包含e字符的 每个部门的最高工资
SELECT
	department_id 部门,
	MAX( salary ) 最高工资 
FROM
	t_mysql_employees 
WHERE
	email LIKE '%e%' 
GROUP BY
	department_id;

#分组后筛选：查询员工个数>5的每个部门和员工个数
SELECT
	COUNT( * ),
	department_id 
FROM
	t_mysql_employees 
GROUP BY
	department_id 
HAVING
	COUNT( * ) > 5;

#分组前筛选+分组后筛选：领导编号>102 的 每个领导 手下的 最低工资大于5000 的领导编号和最低工资
SELECT
	manager_id 领导编号,
	MIN( salary ) 最低工资 
FROM
	t_mysql_employees 
WHERE
	manager_id > 102 
GROUP BY
	manager_id 
HAVING
	MIN( salary ) > 5000;
	

分组排序：

#每个工种 有奖金 的员工的 最高工资>6000 的 工种编号和最高工资,按最高工资升序
SELECT
	job_id 工种,
	MAX( salary ) 最高工资 
FROM
	t_mysql_employees 
WHERE
	commission_pct IS NOT NULL 
GROUP BY
	job_id 
HAVING
	MAX( salary ) > 6000 
ORDER BY
	MAX( salary );
	

按多个字段分组：

#查询 每个工种 每个部门 的 最低工资,并按 最低工资降序
SELECT
	MIN( salary ),
	job_id,
	department_id 
FROM
	t_mysql_employees 
GROUP BY
	department_id,
	job_id 
ORDER BY
	MIN( salary ) DESC;