MySql-复杂查询

分组函数

• 说明：分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数
• 分组函数清单：
  • sum(字段名)：求和
  • avg(字段名)：求平均数
  • max(字段名)：求最大值
  • min(字段名)：求最小值
  • count(字段名)：计算非空字段值的个数
• 特点：
  • sum,avg一般用于处理数值类型，max,min,count可以处理任何类型，以上分组函数都忽略Null值
  • 可以和distinct搭配实现去重的运算
  • count函数的单独介绍， 一般使用count(*)用作统计行数
  • 和分组函数一同查询的字段要求是group by后的字段

简单的使用

• 简单的使用

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary),ROUND(AVG(salary),2),MAX(salary),MIN(salary),COUNT(salary) FROM employees;

• 参数支持那些类型

SELECT SUM(last_name),AVG(last_name) FROM employees;
SELECT SUM(hiredate),AVG(hiredate) FROM employees;

SELECT MAX(hiredate),MIN(hiredate) FROM employees;// 2016-03-03 00:00:00 1992-04-03 00:00:00
SELECT MAX(last_name),MIN(last_name) FROM employees; 

SELECT COUNT(hiredate) FROM employees; //107
SELECT COUNT(commission_pct) FROM employees; //35

• 是否忽略null值 sum忽略null值

SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,
SUM(commission_pct)/107 FROM employees;//7.80    0.222857 0.222857   0.072897

SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;

SELECT COUNT(commission_pct) FROM employees;

• 和distinct搭配使用

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees; // 397900.00      691400.00 

SELECT COUNT(DISTINCT salary) FROM employees;  //57

• count函数的详细介绍
  • 效率：
  • MYISAM:存储引擎下,COUNT(*)的效率高
  • INNODB:存储引擎下,COUNT(*)和COUNT(1)的效率比COUNT('字段')要高一些

SELECT COUNT(salary) FROM employees;

SELECT COUNT(*) FROM employees;//统计总行数

SELECT COUNT(1) FROM employees;

SELECT COUNT(2) FROM employees;

• 查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数

SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) FROM employees;

• 添加筛选条件

  • 查询emp表中记录数：

  SELECT COUNT(employee_id) FROM employees;
  

  • 查询emp表中有佣金的人数：

  SELECT COUNT(salary) FROM employees;
  

  • 查询emp表中月薪大于2500的人数：

  SELECT COUNT(salary) FROM employees WHERE salary>2500;
  

  • 查询有领导的人数：

  SELECT COUNT(manager_id) FROM employees;
  

• 查询员工表中的最大入职时间和最小入职时间的相差天数(DATEDIFF)

MAX(hiredate);
MIN(hiredate);

SELECT DATEDIFF(NOW(),'1997-03-20');

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFERENCE FROM employees;

• 查询部门编号为90的员工个数

SELECT COUNT(*) FROM employees WHERE `department_id`<=>90; 

排序查询

语法

• select 查询列表 from 表名【where 筛选条件】 order by 排序列表
• 执行顺序：①from子句②where子句③select子句④order by 子句
• 举例：select last_name,salary from employees where salary>20000 order by salary ;
  • select * from employees;
• 特点：
  • 排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合
  • 升序 ，通过 asc ，默认行为，降序 ，通过 desc

基本用法

• 查询员工信息，并按工资从高到低排序

SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY salary ASC;

• 查询部门编号>=90的员工信息，按入职时间的先后进行排序

SELECT * FROM employees WHERE `department_id`>=90 ORDER BY `hiredate` ASC;

• 按年薪的高低显示员工的信息和年薪【按表达式排序】

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)) 年薪 
FROM employees ORDER BY 年薪 DESC;

• 按姓名的长度显示员工的姓名和工资【按函数排序】

SELECT LENGTH(`last_name`) 姓名长度,`last_name`,`salary` FROM employees
ORDER BY 姓名长度 DESC;

• 查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】

SELECT * FROM employees ORDER BY salary ASC,`employee_id` DESC;

• 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序

SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC,last_name ASC;

• 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

• 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT *
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;

• 查询编号>3 的女神的男朋友信息，如果有则列出详细，如果没有，用 null 填充

SELECT b.id,b.name,bo.*
FROM beauty b
LEFT JOIN boys bo ON b.boyfriend_id = bo.id
WHERE b.id>3;

• 查询哪个城市没有部门

SELECT l.city
FROM departments d
RIGHT JOIN locations l ON l.location_id = d.location_id
WHERE d.`department_id` IS NULL;

• 查询部门名为 SAL 或 IT 的员工信息

SELECT d.*,e.*
FROM departments d
LEFT JOIN employees e ON d.`department_id` = e.`department_id`
WHERE d.`department_name` = 'SAL' OR d.`department_name`='IT';