2022.3.22学习内容

1.sql92剩余内容

#2.非等值连接

#案例1：查询员工的工资和工资级别

SELECT salary,grade_level
FROM employees AS e,job_grades AS j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
ORDER BY salary DESC;

#3.自连接
/*
把一张表查两次，from表名的时候起两个别名，当成两张表来用
*/

#案例：查询员工名以及其上级名称
SELECT CONCAT(e.`first_name`,e.`last_name`) AS 员工名,CONCAT(m.`first_name`,m.`last_name`) AS 领导名
FROM employees AS e,employees AS m
WHERE e.`manager_id`=m.`employee_id`;

#测试1：显示员工表的最大工资，平均工资
SELECT AVG(salary),MAX(salary)
FROM employees;

#测试2：查询员工表的employee_id,job_id,last_name,按department_id降序，salary升序
SELECT employee_id,job_id,last_name
FROM employees
ORDER BY department_id DESC,salary;

#测试3:查询员工表的job_id中包含a和e的，并且a在e的前面
SELECT job_id
FROM employees
WHERE job_id LIKE('%a%e%');

#测试4：显示所有员工的姓名，部门号和部门名称
SELECT e.`last_name`,e.`department_id`,d.`department_name`
FROM employees AS e,departments AS d
WHERE e.`department_id`=d.`department_id`;

#测试5.查询90号部门员工的job_id和90号部门的localtion_id
SELECT e.`job_id`,d.`location_id`
FROM employees AS e,departments AS d
WHERE e.`department_id`=d.`department_id` AND e.`department_id`=90;

#测试6.查询所有有奖金员工的姓名，部门名，位置编号，城市
SELECT e.`last_name`,d.`department_name`,l.`location_id`,l.`city`
FROM employees AS e,departments AS d,locations AS l
WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`
AND e.`commission_pct` IS NOT NULL;

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

#测试8，查询部门个数大于2的国家编号
SELECT l.`country_id`,COUNT(d.`department_id`)
FROM departments AS d,locations AS l
WHERE d.`location_id`=l.`location_id`
GROUP BY l.`country_id`
HAVING COUNT(d.`department_id`)>2;

#测试9.查询员工姓名，工号，以及其领导的姓名工号
SELECT e.`last_name` AS 'employees',e.`employee_id` AS emp,m.`last_name` AS manager ,m.`employee_id` AS mgr
FROM employees AS e,employees AS m
WHERE e.`manager_id`=m.`employee_id`;

2.sql部分内容

#sql99语法
/*
语法：
         select 查询列表
         from 表1 别名 【连接类型】
         join 表2 别名
         on 连接条件
         where 筛选条件
区别与sql92，连接类型代替逗号，on代替where下的连接条件

内连接：inner
外连接
      左外: left【outer】
      右外: right【outer】
      全外: full【outer】
交叉连接：cross
*/
#内连接
/*
语法：
select 查询列表
from 表1 别名 inner jion 表2 别名
on 连接条件;

分类：
等值
非等值
自连接

特点：1.添加排序，分组，筛选
      2.inner可以省略
      3.筛选条件用where，筛选条件用on，便于阅读
      4.inner join连接和sql92语法等值连接效果一样
      
*/
#一.内连接
#案例1.查询员工名，部门名
SELECT e.`last_name`,d.`department_name` 
FROM employees AS e INNER JOIN departments AS d
ON e.`department_id`=d.`department_id`;

#案例2：查询名字中包含e的员工名和工种名
SELECT e.`last_name`,j.`job_title`
FROM employees AS e INNER JOIN jobs AS j
ON e.`job_id`=j.`job_id`
WHERE e.`last_name` LIKE '%e%';

#案例3：查询部门个数大于3的城市名和部门个数
SELECT l.`city`,COUNT(d.`department_id`)
FROM departments AS d INNER JOIN locations AS l
ON d.`location_id`=l.`location_id`
GROUP BY l.`city`
HAVING COUNT(d.`department_id`)>3;

#案例4:查询员工大于3的部门名和其员工个数，按个数降序排列
SELECT d.`department_name`,COUNT(*)
FROM employees AS e INNER JOIN departments AS d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_name`
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

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

#也可以把连接条件on直接写在后面
SELECT e.`last_name`,d.`department_name`,j.`job_title`
FROM employees AS e INNER JOIN departments AS d ON e.`department_id`=d.`department_id`
INNER JOIN jobs AS j ON e.`job_id`=j.`job_id`
ORDER BY d.`department_name` DESC;

#2,非等值连接

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

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

#3.自连接

#查询员工名和其上级名,其中员工名包含k
SELECT e.`last_name` AS 员工名,m.`last_name` AS 上级名
FROM employees AS e JOIN employees AS m
ON e.`manager_id`=m.`employee_id`
WHERE e.`last_name` LIKE '%e%';

3.明日计划sql99剩余内容，其他内容