#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 连接条件
where 筛选条件
(group by
having
order by)*/
#等值连接
#案例:查询员工名部门名
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 d.location_id=l.location_id
GROUP BY city
HAVING 部门个数>3;
#案例:查询那个部门的部门员工个数>3的部门名和员工个数,并按个数降序
SELECT department_name,COUNT(*) 员工个数
FROM employees e INNER
JOIN departments d ON e.department_id=d.department_id
GROUP BY d.department_id
HAVING 员工个数>3
ORDER BY 员工个数 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;
#非等值连接
#案例:员工的工资级别
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades g ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;
#案例:查询工资级别的个数大于20,并且排序
SELECT `grade_level`,COUNT(*)
FROM employees e
INNER JOIN job_grades g ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY `grade_level`
HAVING COUNT(*)>20
ORDER BY COUNT(*);
#自连接
#案例: 员工的名字和上级的名字
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m ON e.manager_id =m.employee_id;
MySQL sql99标准内连接
最新推荐文章于 2023-02-17 07:19:11 发布