mysql 连接查询

#分组查询

/**

  select 查询列表
  from 表名
  where 筛选条件
  group by 分组列表
  having 分组后筛选
  order by 排序列表;

  执行顺序：
  1 、from
  2、where
  3、group by
  4、having 子句
  5、select 子句
  6、order by 子句

  特点:
  1、查询列表往往是 分组函数 和 分组字段
  2、分组查询筛选分为两类
           筛选的表      使用的关键词         位置
分组前筛选   原始表       where               group by 前面
分组后筛选   分组后的结果集 having              group by 后面

 */

-- 根据对应不同的typeid 算出views 的平均数 （例如 两条数据 typeid 1，view 分别为1,2 ，就会有 typeid 1，views 2/1+2 =1.5）
select avg(b.views) ,b.typeId from blog b  group by b.typeId ;

-- 统计 typeid 值的条数
select count(*),b.typeId  from blog b group by typeId;

-- 统计typeid 不同值的最大views
select max(views),typeid from blog group by typeid;

-- 统计typeid 值 大于5 的个数
select count(*),typeId FROM blog  GROUP BY  typeId HAVING COUNT(*)>5;

#------------------------SQL99语法
#一、内连接
/*
SQL92和SQL99的区别：

	SQL99，使用JOIN关键字代替了之前的逗号，并且将连接条件和筛选条件进行了分离，提高阅读性！！！

*/
#一）等值连接
#①简单连接
#案例：查询员工名和部门名

SELECT last_name,department_name
FROM departments d
       JOIN  employees e
               ON e.department_id =d.department_id;

#②添加筛选条件
#案例1：查询部门编号>100的部门名和所在的城市名
SELECT department_name,city
FROM departments d
         JOIN locations l
              ON d.`location_id` = l.`location_id`
WHERE d.`department_id`>100;


#③添加分组+筛选
#案例1：查询每个城市的部门个数

SELECT COUNT(*) 部门个数,l.`city`
FROM departments d
         JOIN locations l
              ON d.`location_id`=l.`location_id`
GROUP BY l.`city`;


#④添加分组+筛选+排序
#案例1：查询部门中员工个数>10的部门名，并按员工个数降序

SELECT COUNT(*) 员工个数,d.department_name
FROM employees e
         JOIN departments d
              ON e.`department_id`=d.`department_id`
GROUP BY d.`department_id`
HAVING 员工个数>10
ORDER BY 员工个数 DESC;

#二）非等值连接

#案例：查询部门编号在10-90之间的员工的工资级别，并按级别进行分组
SELECT * FROM sal_grade;

SELECT COUNT(*) 个数,grade
FROM employees e
         JOIN sal_grade g
              ON e.`salary` BETWEEN g.`min_salary` AND g.`max_salary`
WHERE e.`department_id` BETWEEN 10 AND 90
GROUP BY g.grade;

#三）自连接

#案例：查询员工名和对应的领导名

SELECT e.`last_name`,m.`last_name`
FROM employees e
         JOIN employees m
              ON e.`manager_id`=m.`employee_id`;

#二、外连接
/*
说明：查询结果为主表中所有的记录，如姑婆从标有匹配项，则为显示匹配项，则显示 NULL
应用场景：一般用于查询主表中有但从表中没有的记录特点： 1 外连接分主从表，量表的顺寻不能任意调换 2 做链接的话，

LEFT JOIN 左边为主表有连接的话，
RIGHT JOIN 右边为朱标语法
SELECT
    查询列表
FROM
    表 1 别名 LEFT |
    RIGHT OUTER JOIN 表 2 别名
ON 联接条件
WHERE 筛选条件
*/

USE girls;

#案例1：查询所有女神的记录以及对象的男神名，如果没有对应的男神名
#左连接
SELECT b.*,bo.*
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id`=bo.`id`;
#右连接
select b.*,bo.*
from boys bo
right join beauty b on  b.boyfriend_id = bo.id;

#案例2 :查哪个女神没有男朋友
#左连接
SELECT b.*,bo.*
FROM beauty b
         LEFT JOIN boys bo ON b.`boyfriend_id`=bo.`id`
where bo.id is  null;
#右连接
select b.*,bo.*
from boys bo
         right join beauty b on  b.boyfriend_id = bo.id
where bo.id is null;

#子查询
/*
 select 后面：
  要求：子查询的结果为单行单列 （标量子查询)
  from 后面
  要求：子查询单结果可以为多行多列
  where 或者 having 后面
  要求：子查询单结果为 单列
         单行子查询
         多行子查询
  exists 后面
  要求：子查询结果必须为单列 （相关子查询）
  特点：
  1、子查询放在条件中吗，要求必须放在条件的右侧
  2、子查询一般在小括号中
  3、子查询的执行优先于住查询
  4、单行子查询对应了 单行操作符：> < >= <= = <>
     多行子查询对应了 多行操作符：any /some all in
 */

# 单行子查询
# 查询和Zlotkey 相同部门的员工姓名和工资
use myemployees;

select department_id from employees where last_name = 'Zlotkey';

select last_name,salary,department_id from
                             employees
where department_id =
      (select department_id from employees where last_name = 'Zlotkey');

# 查询工资比公司平均工资高的员工的员工号，姓名和工资
select avg(salary) from employees ;

select employee_id,last_name,salary from
                                         employees
where salary >
      (select avg(salary) from employees );

# 多行子查询
/*
 in :判断某字段是否在指定列表内
 any/some ：判断某字段的值是否满足其中一个
 pg：x > any(10，30，50)
     x > min()
 all : 判断某字段的值是否满足里面所有的
 pg：x > any(10，30，50)
     x > max()
 */
# 案例1 查询location_id  是 1400 或者 1700的员工姓名
select department_id from departments where location_id in (1400,1700);

select last_name from
                      employees
where department_id in
      (select department_id from departments where location_id in (1400,1700)
);

# 案例2 返回其他部门中比job_id为'IT_PROG'部门任意工资低低员工、姓名、job_id 以及 salary
select distinct  salary from employees where job_id = 'IT_PROG';

select employee_id,last_name,job_id,salary from
                                                employees
where salary<any(select distinct salary from employees where job_id = 'IT_PROG');

# 等价于
select employee_id,last_name,job_id,salary from
    employees
where salary< (select max(salary) from employees where job_id = 'IT_PROG');

# 案例3 返回其他部门中比job_id为'IT_PROG'部门任意工资低低员工、姓名、job_id 以及 salary

select distinct  salary from employees where job_id = 'IT_PROG';

select employee_id,last_name,job_id,salary from
    employees
where salary<all(select distinct salary from employees where job_id = 'IT_PROG');

# 子查询放在from 后面
# 查询每个部门的平均工资和工资级别
select avg(salary),department_id from employees group by department_id;

select  deg_ag.department_id,deg_ag.ag,g.grade from
                                                    sal_grade g
                                                        join (
    select avg(salary) ag,department_id from employees group by department_id
    ) deg_ag on deg_ag.ag between g.min_salary and g.max_salary;