sql199语法


文章目录


语法


select 查询列表

from 表1 别名 [连接类型]

jion 表2 别名

on 连接条件

[where 筛选条件]

[group by 分组]

[having 筛选条件]

[order by 排序列表]


分类:

内连接: inner

外连接


左外:left [outer]

右外:right [outer]

全外: full [outer]


交叉连接: cross

内连接

语法:


select 查询列表

from 表1 别名

inner join表2 别名

on 连接条件


分类

等值连接:

案例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 e.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 COUNT(​)>3;


案例4:查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序(添加排序)

①查询每个部门的员工个数


SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name;


②在①结果上筛选员工个数>3的记录,并排序


SELECT COUNT(​),department_name
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;


案例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;


​特点:​ ①添加排序,分组,筛选

②inner可以省略

③筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读

④inner jion连接和sql192语法中等值连接效果是一样的,都是查询多表的交集

非等值连接

查询工资级别的个数>20的个数,并且按工资级别降序


SELECT COUNT(​),grade_level
FROM employees e
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;


自连接

查询员工的名字,上级的名字


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


交叉连接

cross jion 一定会产生笛卡尔积


SELECT b.​,bo. FROM beauty b
CROSS JOIN boys bo;


综合案例练习

查询编号>3的女神的男朋友信息,如果有,列出详细,没有用null填充


SELECT b.id, b.​​name​​,bo.*
FROM beauty b
LEFT JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE b.id>3;


查询哪个城市没有部门


SELECT city
FROM departments d
RIGHT JOIN locations l
ON d.location_id=l.location_id
WHERE d.department_id IS NULL;