MySQL笔记 sql99连接查询一
一.sql99语法
语法:
select 查询列表 from 表1 别名【连接类型】 join 表2 别名 on 连接条件 【where筛选条件】 【group by分组】 【having 筛选条件】 【order by 排序列表】
分类:
内连接(重点):inner
外连接:
左外(重点):left【outer】
右外(重点):raght【outer】
全外:full【outer】
交叉连接:cross
内连接
分类:
等值连接
非等值连接
自连接
案例:
等值连接: #案例1.查询员工名,部门名
SELECT last_name, department_name
FROM employees e
INNER JOIN departments d
ON e.department_id
=d.department_id;
#案例2:查询名字中包含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:查询部门个数>3的城市名和部门个数(添加分组+筛选)
SELECT city, COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.location_id
=l.location_id
GROUP BY city HAVING 部门个数>3;
#案例4:查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序(添加分组+筛选+排序)
SELECT department_name, COUNT(*) 员工个数
FROM employees e INNER JOIN departments d
ON e.department_id
=d.department_id
GROUP BY department_name
HAVING 员工个数>3
ORDER BY 员工个数 DESC;
#案例5:查询员工名,部门名,工种名,并按部门名降序(添加三表连接)
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;
非等值连接: #案例:查询工资级别的个数>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 grade_level DESC;
自连接: #案例:查询姓名中包含字符k的员工的名字,上级的名字
SELECT e.last_name, m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id=m.employee_id
WHERE e.last_name
LIKE "%k%";