Mysql学习笔记（5）sql99语法：自连接、外连接、交叉连接、子查询

sql99语法

连接类型

内连接

#省略inner
SELECT last_name,`department_name`
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`

#分组+筛选+排序
SELECT `department_name`,COUNT(*) co
FROM departments AS d
INNER JOIN employees AS e
ON d.`department_id` = e.`department_id`
GROUP BY e.`department_id`
HAVING co > 3
ORDER BY co 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 last_name,salary,`grade_level`
FROM employees e
JOIN `job_grades` j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
ORDER BY `grade_level` 

#非等值连接:分组+筛选+排序
SELECT `grade_level`,COUNT(*) AS co
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING co > 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`
WHERE e.`last_name` LIKE '%k%'

外连接

#左连接+筛选
SELECT g.`name` FROM beauty g
LEFT JOIN boys b ON g.`boyfriend_id` = b.`id`
WHERE b.`id` IS  NULL

#右连接
SELECT b.`name`
FROM boys AS bo
RIGHT JOIN beauty AS b
ON b.`boyfriend_id` = bo.`id`
WHERE bo.id IS NULL

交叉连接

#交叉连接：实现笛卡尔乘积
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo

子查询

标量子查询（单行子查询）

#单行子查询
SELECT last_name,job_id,salary FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

SELECT last_name,job_id,salary FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE `employee_id` = 141)
AND salary > (SELECT salary FROM employees WHERE `employee_id` = 143)

#单行子查询 分组+筛选
SELECT MIN(salary),`department_id` FROM employees
GROUP BY `department_id`
HAVING MIN(salary) > (
	SELECT MIN(salary) FROM employees WHERE `department_id` = 50
	)

列子查询（多行自查询）

#IN()
SELECT last_name FROM employees 
WHERE `department_id` 
IN (SELECT `department_id` FROM departments WHERE `location_id` IN(1400,1700))

#ANY()
SELECT `employee_id`,last_name,job_id,salary 
FROM employees
WHERE salary < ANY (
	SELECT salary FROM employees WHERE `job_id` = 'IT_PROG'
) AND job_id <> 'IT_PROG'
#替代解法
SELECT `employee_id`,last_name,job_id,salary 
FROM employees
WHERE salary < (
	SELECT MAX(salary) FROM employees WHERE `job_id` = 'IT_PROG'
) AND job_id <> 'IT_PROG'

#ALL()
SELECT `employee_id`,last_name,job_id,salary
FROM employees
WHERE salary < ALL(
	SELECT salary FROM employees WHERE job_id = 'IT_PROG'
)
# 替代解法
SELECT `employee_id`,last_name,job_id,salary
FROM employees
WHERE salary < (
	SELECT MIN(salary) FROM employees WHERE job_id = 'IT_PROG'
)

行子查询

SELECT * FROM employees
WHERE (`employee_id`,salary) = (
	SELECT MIN(`employee_id`),MAX(salary) FROM employees
)

select后面

SELECT d.*,(SELECT COUNT(*) FROM employees e 
			WHERE e.`department_id` = d.`department_id`)
FROM `departments` d

from后面

SELECT d.*, grade_level FROM job_grades j,
(SELECT TRUNCATE(AVG(salary),2) av,`department_id` FROM employees
GROUP BY `department_id`) AS d
WHERE d.av BETWEEN j.`lowest_sal` AND j.`highest_sal`

EXISTS() 相关子查询

#EXISTS() 判断是否有结果 返回：1 或 0
SELECT `department_name` FROM departments d
WHERE EXISTS(
	SELECT `employee_id` FROM employees e
	WHERE d.`department_id` = e.`department_id`
)
#类似解法 in()
SELECT `department_name` FROM departments d
WHERE `department_id` IN(
	SELECT `department_id` FROM employees
)

#NOT EXISTS()
SELECT `boyName` FROM boys bo
WHERE NOT EXISTS(
	SELECT id FROM beauty b
	WHERE bo.`id` = b.`boyfriend_id`
)