Mysql的查询、分组

Mysql的查询、过滤、排序与分组

1、基于select语句的查询功能：
1.1、查询所有列：

SELECT * FROM t_mysql_employees;

1.2、查询特定列：

SELECT first_name FROM t_mysql_employees;

1.3、设置别名：在列名后加空格或者as 最后加别名即可

select first_name 姓,last_name 名 from t_mysql_employees;

或者

select first_name as 姓,last_name as 名 from t_mysql_employees;

1.4、去重功能：

SELECT DISTINCT department_id FROM t_mysql_employees;

1.5、+号的作用：
Mysql中+号的作用不同于java中+号的是：java中既可以做运算符也可以做连接符，mysql中的+号只能做运算符，而不能做连接符。
在mysql中：
SELECT 10+50;#结果为190

SELECT ‘10’+90;#结果为100

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

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

而连接字符所有的语句是concat：

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

2、过滤与排序：
2.1、利用运算符筛选（> 、< 、>=、<=、<>)：
需要注意的是！=虽可行，但mysql普遍来说多使用<>符号。
#查询工资>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.2、逻辑筛选：
&&和and：两个条件都为true，结果为true，反之为false
||或or： 只要有一个条件为true，结果为true，反之为false
!或not： 如果连接的条件本身为false，结果为true，反之为false

2.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计算个数
特点：

sum、avg一般用于处理数值型
max、min、count可以处理任何类型
可以和distinct搭配实现去重的运算
一般使用count(*)用作统计行数
和分组函数一同查询的字段要求是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;