Mysql基础(二)

多表连接

#多表查询
/*
sql99标准
      等值连接
             ①多表等值连接的结果为多表的交集部分
             ② n个连接至少需要 n-1个连接
             ③一般需要为表起别名
             ④可以搭配前面介绍的所有子句的使用，比如排序，分组，筛选             
*/

-- 1.查询有奖金的员工名，部门名
SELECT  last_name,department_name,commission_pct
FROM  employees e,departments d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL;

-- 2.查询城市中第二个字符为o的部门和城市
SELECT  department_name,city
FROM departments d,locations l
WHERE    d.`location_id` = l.`location_id`
AND city LIKE '%_o%';

-- 3.查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM  departments d,locations l
WHERE  d.`location_id` = l.`location_id`
GROUP BY city;

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

-- 查询每个员工的工种的工作名和员工的个数，并且按员工的个数降序
SELECT  job_title,COUNT(*)
FROM employees e ,jobs j
WHERE e.`job_id` = j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*);

#实现三表连接

-- 查询员工名，部门名和所在的城市
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`
AND city LIKE '%s%';

datediff:返回两个日期相差的天数

#自连接
-- 查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id 上级id,m.last_name AS 上级名字
FROM employees e,employees m
WHERE  e.`manager_id` = m.`employee_id`;




-- 查询每个工种，每个部门的部门名和最低工资
SELECT  department_name,job_title,MIN(salary)最低工资
FROM  employees e,departments d,jobs j
WHERE e.`department_id` = d.`department_id`
AND e.`job_id` = j.`job_id`
GROUP BY department_name,job_title;

-- 查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(*)部门个数
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY country_id
HAVING COUNT(*)>2;



/*sql99语法
          select 查询列表
          from  表1  别名  连接类型
          join  表2  别名  on   连接条件
          where  筛选条件
          group by  分组
          Having    筛选条件
          order by  排序列表
          
分类：
    内连接:inne
    外连接:
         左外:letf
         右外:right
         全外:full    
交叉连接:  cross

特点： 添加排序，分组，筛选
       inner可以省略
       筛选条件放在where后面，连接条件放在on后面，提高分离性
       inner连接和sql192的效果一样的，同样都是查询多表的交集
*/

-- 查询员工名，部门名
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 e.`last_name` LIKE '%e%';

-- 查询部门个数>3的城市名和部门个数，添加分组+筛选
SELECT city,COUNT(*) 部门个数
FROM departments d  INNER JOIN  locations l
ON  d.`location_id`  = l.`location_id`
GROUP BY  city
HAVING COUNT(*)>3;

-- 查询那个部门的部门员工数>3的部门名和员工个数，并按个数降序
SELECT department_name,COUNT(*) 员工个数
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`    
GROUP BY department_name
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 j.`job_id` = e.job_id
ORDER BY department_name  DESC;

-- 左外连接，left join左边的是主表
-- 右外连接，right join右边的是主表
-- mysql不支持全外连接


#交叉连接也就是sql99语法的笛卡尔乘积
SELECT b.*,boo.*
FROM beauty b
CROSS JOIN boy  bo;

-- 查询那个城市没有部门 城市为主表
SELECT  city
FROM departments d RIGHT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE  d.`department_id` IS NULL;

-- 查询部门名为sal或it的员工信息 部门一定有的员工可能没有  部门为主表
SELECT  e.*,department_name
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE d.`department_name` IN('SAL','IT');

#子查询(内查询)：出现在其他语句中的select
-- 查询谁的工资比Abel的工资
SELECT *
FROM employees
WHERE salary>(SELECT salary
              FROM employees
              WHERE last_name = 'Abel'
               );

-- 查询job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(SELECT job_id
              FROM employees
              WHERE employee_id = 141 
             ) AND salary>(SELECT salary
                           FROM employees
                           WHERE employee_id = 143              
                           );

#多行子查询  操作符 IN  / NOT IN  等于列表中的任意一个
#                     ANY / SOME  和子查询返回的某一个值比较 
#                            ALL  和子查询返回的所有值比较

-- 返回location_id是1400或1700的部门中所有员工的姓名
SELECT  last_name
FROM employees
WHERE department_id IN (SELECT    DISTINCT department_id
                        FROM departments
                        WHERE location_id IN(1400,1700)                        
                        );

-- 返回其他部门中比job_id为IT_PROG部门任一工资低的员工的员工号，姓名，job_id以及salary
SELECT employee_id,last_name,salary
FROM employees
WHERE salary<ANY(SELECT salary
                 FROM employees
                 WHERE job_id = 'IT_PROG'
                 )AND job_id<>'IT_PROG';
-- 或者
SELECT employee_id,last_name,salary
FROM employees
WHERE salary<(SELECT MAX(salary)
                 FROM employees
                 WHERE job_id = 'IT_PROG'
                 )AND job_id<>'IT_PROG';



-- 返回其他部门中比job_id为IT_PROG部门所有工资低的员工的员工号，姓名，job_id以及salary
SELECT employee_id,last_name,salary
FROM employees
WHERE salary<ALL(SELECT salary
                 FROM employees
                 WHERE job_id = 'IT_PROG'
                 )AND job_id<>'IT_PROG';
-- 或者
SELECT employee_id,last_name,salary
FROM employees
WHERE salary<(SELECT MIN(salary)
                 FROM employees
                 WHERE job_id = 'IT_PROG'
                 )AND job_id<>'IT_PROG';


-- 查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE employee_id = (SELECT MIN(employee_id)
                     FROM employees
                      )AND  salary = (SELECT MAX(salary)
                                      FROM employees                                    
                                      );
-- 或者
SELECT *
FROM employees
WHERE (employee_id,salary) = (SELECT MIN(employee_id),MAX(salary)
                              FROM employees);
                     

# 分页查询
/*
  select 查询列表
  from 表
  limit(page-1)*size,size;  
*/  

-- 查询所有学员的邮箱用户名(邮箱中@前面的字符)
SELECT SUBSTRING(email,1,INSTR(eamil,,'@')-1) 用户名
FROM stuinfo;

-- 2.查询平均工资最低的部门信息
-- MIN MIN  AVG 等不能互相嵌套
SELECT * 
FROM departments
WHERE department_id =(SELECT department_id
                      FROM employees
                      GROUP BY department_id
                      HAVING AVG(salary)=(SELECT MIN(ag)
                                          FROM((SELECT AVG(salary) ag
                                                FROM employees
                                                GROUP BY department_id)  
                                              ) AS a 
                                          )                  
                      );
   
-- 简洁的方法二
SELECT * 
FROM departments
WHERE department_id =(SELECT department_id
                      FROM employees
                      GROUP BY department_id
                      ORDER BY  AVG(salary)
                      LIMIT 1 -- 升序排序之后选择第一页第一个
                      );      -- 得到的也就是最小值



-- 2.查询平均工资的最低的部门信息和该部门的品均工资
SELECT AVG(salary)
FROM employees
GROUP BY department_id
LIMIT 1;

-- 平均工资最低的部门编号,作为新的表连接
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
--
SELECT d.*,ag
FROM departments  d
JOIN (SELECT AVG(salary) ag,department_id
      FROM employees
      GROUP BY department_id
      ORDER BY AVG(salary)
      LIMIT 1) AS a
ON d.`department_id` = a.department_id;   



-- 3.查询平均工资最高的job信息
SELECT *
FROM jobs 
WHERE job_id = 
            (SELECT job_id
             FROM employees
             GROUP BY  job_id  
             ORDER BY AVG(salary) DESC
             LIMIT 1);

-- 4.查询平均工资高于公司平均工资的部门有哪些
SELECT department_id
FROM employees
GROUP BY department_id
HAVING  AVG(salary)>(
                     SELECT AVG(salary)
                     FROM employees);


-- 5.查询出公司中所有manager的详细信息
SELECT *
FROM employees
WHERE employee_id = ANY(
                       SELECT DISTINCT manager_id
                       FROM employees );
         
-- 6.各部门中，最高工资中最低的那个部门的，最低工资是多少
-- 每个部门最高工资比较后 得到的 最低的工资和对应的部门号
SELECT MAX(salary), department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1;

SELECT MIN(salary)
FROM employees
WHERE department_id = (SELECT department_id
                       FROM employees
                       GROUP BY department_id
                       ORDER BY MAX(salary)
                       LIMIT 1);

-- 7.查询平均工资最高的部门的manager的详细信息：last_name,department_id,email,salary
SELECT *
FROM employees e  JOIN departments d
ON e.`employee_id` = d.`manager_id`
WHERE  d.`department_id` = (SELECT department_id    
                            FROM employees                          
                            GROUP BY department_id
                            ORDER BY AVG(salary) DESC
                            LIMIT 1);

#联合查询
/*union 联合 合并：将多条查询语句的结果合并成一个结果
  语法：
      查询语句1
      union
      查询语句2
      union
      ...
  应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，
            但查询的信息一致时          
            
  特点：①要求多条哈讯语句的查询列数一致
        ②要求多条查询语句的查询的每一列的类型和顺序最好一致  
        ③union关键字默认去重，如果使用union all可以包含重复项                                 
*/