mysql基础复习——连接查询（sql99）

sql99语法 连接查询

#语法
select 查询列表
from	表1 别名 
【连接类型】 join 	表2 别名
on		连接条件
	【where 筛选条件】
	【group by 分组】
	【having 筛选条件】
	【order by 排序列表】

# 【连接类型】
# 内连接：inner
# 外连接：
#		左外：left【outer】
#		右外：right【outer】
# 		全外：full【outer】
# 交叉连接

1、内连接

a、等值连接

# 案例1：查询员工名、部门名
# 等值连接
SELECT 
	CONCAT(first_name,'_',last_name) AS "员工名",
	department_name AS "部门名"
FROM 
	employees AS e,departments AS d
WHERE
	e.department_id = d.department_id;

# sql99 等值连接
SELECT
	CONCAT(first_name,'_',last_name) AS "员工名",
	department_name AS "部门名"
FROM
	employees AS e
INNER JOIN
	departments AS d
ON
	e.department_id = d.department_id;

# 案例2：查询名字中包含e的员工名和工种名
select
	last_name as '员工名',
	job_title as '工种名'
from
	employees as e
inner join
	jobs as j
on
	e.job_id=j.job_id
where
	last_name like "%e%"; 

# 案例3：查询部门个数 > 3的城市名和部门个数（添加分组+筛选）
select
	count(*) as "部门个数",
	city as "城市名"
from
	departments as d
inner join
	locations as l
on
	d.location_id = l.location_id
group by
	city
having
	部门个数 > 3;

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

# 查询员工名、部门名、工种名，并按部门名降序排序
SELECT
	CONCAT(first_name,"_",last_name) AS 员工名,
	department_name AS 部门名,
	job_title AS 工种名
FROM
	departments AS d
INNER JOIN
	employees AS e
ON
	d.department_id = e.department_id
INNER JOIN
	jobs AS j
ON
	e.job_id = j.job_id
ORDER BY
	部门名 DESC;

b、非等值连接

# 案例1：查询员工工资级别
SELECT 
	salary,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(*) 员工数量, grade_level 工资级别
FROM
	employees AS e
JOIN
	job_grades AS j
ON
	e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY
	grade_level
HAVING
	员工数量 > 20
ORDER BY
	工资级别 DESC

c、自连接

# 案例1：查询员工姓名和对应员工领导的姓名
SELECT 
	CONCAT(e.first_name,'_', e.last_name) 员工姓名,
	CONCAT(m.first_name,'_', m.last_name) 领导姓名
FROM
	employees AS e
INNER JOIN
	employees AS m
ON
	e.manager_id = m.employee_id

d、外(左、右)连接

应用场景：用于查询a表中有，但是b表中没有的数据
特点：
        1、外连接查询结果为主表中的所有内容;
              如果从表中有和条件匹配的，显示匹配的内容；没有和条件匹配的则显示null；
              外连接查询结果 = 内连接结果 + 主表中有而从表中没有的数据
        2、左外连接：left join左边的是主表
              右外连接：right join右边的是主表
外连接：
左外：left【outer】
右外：right【outer】
全外：full【outer】

# 案例1：查询哪个部门没有员工
SELECT 
	d.*,e.`employee_id`
FROM
	departments AS d
LEFT OUTER JOIN
	employees AS e
ON
	d.`department_id`=e.`department_id`
WHERE
	e.`employee_id` IS NULL

e、交叉连接

# 本质上是sql99语法实现的笛卡尔乘积
# 不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，
# 返回结果的行数等于两个表行数的乘积（例如：T_student和T_class，返回4*4=16条记录），
# 如果带where，返回或显示的是匹配的行数。
SELECT beauty.*, boys.*
FROM beauty
CROSS JOIN boys


SELECT beauty.*,boys.*
FROM beauty, boys

# 带where
SELECT beauty.*, boys.*
FROM beauty
CROSS JOIN boys
WHERE beauty.`boyfriend_id`=boys.`id`

连接查询案例

# 案例1：查询id>3的女生的男朋友信息，如果有列出姓名，如果没有填充为NULL
SELECT
	b.bname,bo.boyName
FROM
	beauty AS b
LEFT OUTER JOIN
	boys AS bo
ON
	b.`boy_id`=bo.`boy_id`
WHERE
	b.`id`>3

# 案例2：查询哪个城市没有公司部门
# 左外连接
SELECT 
	l.city,d.department_name
FROM
	locations AS l
LEFT OUTER JOIN
	departments AS d
ON
	l.`location_id` = d.`location_id`
WHERE
	d.`department_name` IS NULL;
	

# 右外连接
SELECT 
	l.city,d.department_name
FROM
	departments AS d
RIGHT OUTER JOIN
	locations AS l
ON
	l.`location_id` = d.`location_id`
WHERE
	d.`department_name` IS NULL;

# 案例3：查询部门名为‘SAL’或者‘IT’的员工姓名
# 用外连接更保险，因为有些部门可能没有员工
SELECT
	d.`department_name`,e.`last_name`
FROM
	departments AS d
LEFT OUTER JOIN
	employees AS e
ON
	d.`department_id` = e.`department_id`
WHERE
	d.`department_name` IN('SAL', 'IT');
#·	d.`department_name`='SAL' OR d.`department_name` = 'IT';