ios 分组 条件筛选 分组查询加条件

分组查询

语法：

select 分组函数、列（要求出现在group by 的后面）

from 表

【where 筛选条件】

group by 分组的列表

【order by 子句】

注意：查询列表必须特殊，要求是分组函数和group by后出现的字段

特点：

1. 分组查询中的筛选条件分为两类
   数据源 位置 关键字

• 分组前筛选 原始表 group by前 where
• 分组后筛选 分组后的结果集 group by前 having

分组函数做条件一定会放在having语句中

1. group by 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数（用得较少）
2. 排序也可以添加（排序放在整个分组查询的最后）

# 案例： 查询每个工种的最高工资
 SELECT MAX(salary),job_id
 FROM employees
 GROUP BY job_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;
    
     # 案例：查询每个工种有奖金的员工的最高工资>12000d工种编号和最高工资
 SELECT MAX(salary),job_id
 FROM employees
 WHERE commission_pct IS NOT NULL
 GROUP BY job_id
 HAVING MAX(salary) > 12000;
 
  # 按表达式或函数分组
 # 案例： 按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;

# 添加排序
#案例：查询每个部门每个工种的员工的平均工资并且按平均工资的高低
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;

连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有 m 行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

分类：

1. 按年代分类：sql92标准 ：仅仅支持内连接
   sql99标准【推荐】：支持内连接+外连接(左外+右外)+交叉连接
2. 按功能分类：内连接： 等值连接
   非等值连接
   自连接
   外连接： 左外连接
   右外连接
   全外连接
   交叉连接

sql92语法

1. 等值连接

①多表等值连接的结果为多表的交集部分

②n表连接，至少需要n-1个连接条件

③多表的顺序没有要求

④一般需要为表起别名

⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

# 案例：查询女生对应的男生名
SELECT NAME,boyName FROM beauty,boys
WHERE beauty.`boyfriend_id` = boys.`id`;

#案例：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

• 为表起别名

提高语句的简洁度，区分多个重名的字段

如果为表起了别名，则查询的字段就不能使用原来的表名去限定

# 查询员工名、工种名、工种号
SELECT e.last_name,e.job_id,j.job_name
FROM employees e,job j
WHERE e.`job_id`=j.`job_id`;

• 可以加筛选

# 案例：查询有奖金的员工名、部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;

• 可以加分组

# 案例：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT MIN(salary),department_name,d.manager_id
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id;

• 可以加排序

#案例：查询每个工种的工种名和员工的个数
SELECT job_title,COUNT(*)
FROM jobs j,employees e
WHERE j.`job_id`=e.`job_id`
GROUP BY job_title
ORDER BY COUNT(*)DESC;

• 可以实现3表连接

#案例：查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;

2. 非等值连接

#案例：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
wheere e.`salary` BETWEEN lowest_sal AND highest_sal;

3.自连接

#案例：查询 员工名和上级的名称
 SELECT e.last_name,e.employee_id,m.employee_id,m.last_name
 FROM employees e,employees m
 WHERE e.`manager_id`=m.`employee_id`;

sql99语法

语法：

select 查询列表

from 表1 别名【连接类型】

join 表2 别名

on 连接条件

【where 筛选条件】

【group by 分组】

【having 筛选条件】

【order by 排序列表】

分类

内连接(★) inner

外连接

左外（★）left【outer】

右外(★) right【outer】

全外 full【outer】

交叉连接 cross

一、内连接

select 查询列表

from 表1 别名

inner join 表2 别名

on 连接条件;

分类：

等值连接

非等值连接

自连接

1.等值连接

特点：

• 可以添加排序、分组、筛选
• inner 可以省略
• 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
• inner join 连接和 sql92语法中的等值连接效果是一样的，都是查询多表的交集

#案例：查询员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;

#案例：查询名字中包含e的员工名和工种名（筛选）
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE 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 e.`department_id`=d.`department_id`
GROUP BY department_id
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

#案例：查询员工名、部门名、工种名，并按部门名降序
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
INNER JOIN jobs j
ON e.`job_id`= j.`job_id`
ORDER BY department_name DESC;

2.非等值连接

#案例：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#案例：查询工资级别的个数>20的个数，并且按工资级别排序 降序
SELECT grade_level,COUNT(*) 个数
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

3.自连接

#查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;

二、外连接

特点：

• 外连接的查询结果为主表中的所有记录
  如果从表中有和它匹配的，则显示匹配的值
  如果从表中没有和它匹配的，则显示null
  外连接查询结果=内连接结果+主表中有而从表中没有的记录
• 左外连接，left join左边的为主表
  右外连接，right join 右边的是主表
• 左外和右外交换两个表的顺序，可以实现同样的结果

1.左外连接

#案例：查询男朋友 不在男神表的女生名
 SELECT  b.name,bo.*
 FROM beauty b
 LEFT OUTER JOIN boys bo
 ON b.`boyfriend_id`=bo.`id`
 WHERE bo.`id` IS NULL;
 
  #案例：查询哪个部门没有员工
 SELECT d.*,e.employee_id
 FROM departments d
 LEFT OUTER JOIN employees e
 ON d.`department_id`=e.`department_id`
 WHERE e.`employee_id`IS NULL;

2.右外连接

#案例：查询男朋友 不在男神表的女生名
 SELECT  b.name,bo.*
 FROM boys bo
 RIGHT OUTER JOIN beauty b
 ON b.`boyfriend_id`=bo.`id`
 WHERE bo.`id` IS NULL;
 
 #案例：查询哪个部门没有员工
 SELECT d.*,e.employee_id
 FROM employees e
 RIGHT OUTER JOIN departments d
 ON d.`department_id`=e.`department_id`
 WHERE e.`employee_id`IS NULL;

3.全外连接

mysql不支持

• 全外连接相当于内连接的结果+表1中有但表2中没有的+表2中有但表1中没有的

SELECT b.*,bo.*
 FROM beauty b
 FULL OUTER JOIN boys bo
 ON b.`boyfriend_id`=bo.id;

4.交叉连接

相当于笛卡尔乘积

SELECT b.*,bo.*
 FROM beauty b
 CROSS JOIN boys bo
 ON b.`boyfriend_id`=bo.`id`;

sql92和sql99总结：

• 功能：sql99支持的较多
• 可读性：sql99实现连接条件和筛选条件的分类，可读性较高