MySQL学习 连接查询 sq199语法

#测试2
#1.显示员工表的最大工资，工资平均值

SELECT MAX(salary),AVG(salary)
FROM employees

#2.已知表student里面有id(学号)，name，gradeId（年级编号）
#已知表grade里面有id（年级编号），name（年级名）
#已知表result里面有id，score，studentNo（学号）
#要求查询姓名，年级名，成绩

SELECT s.name,g.name,score
FROM student s,grade g,result r
WHERE s.id = r.studentNo
AND g.id = s.gradeId

#3.显示所有员工的姓名，部门号和部门名称
SELECT last_name,d.department_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id

#4.查询90号部门员工的job_id和90号部门员工的location_id

SELECT e.job_id,location_id
FROM employees e,departments d
WHERE e.department_id = d.department_id
AND e.department_id = 90

#5.选择所有有奖金的员工的

SELECT last_name,department_name,l.location_id,city,commission_pct
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND commission_pct IS NOT NULL

#6.选择city在Toronto工作的员工的last_name,job_id,department_name,department_id

SELECT last_name,job_id,department_name,d.department_id
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND city = ‘Toronto’

#7.查询每个工种每个部门的部门名、工种名和最低工资

SELECT job_title,department_name,MIN(salary)
FROM departments d,employees e,jobs j
WHERE e.department_id = d.department_id
AND e.job_id= j.job_id
GROUP BY department_name,job_title

#8.查询每个国家下的部门个数大于2的国家编号

SELECT country_id,COUNT(*) 部门个数
FROM departments d,locations l
WHERE d.location_id = l.location_id
GROUP BY country_id
HAVING 部门个数>2

#二、sq199语法
/*
语法：
SELECT 查询列表
FROM 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【WHERE 筛选条件】
【GROUP BY】
【ORDER BY】
内连接（）：INNER
外连接
左外（）left 【OUTER】
右外（*）right 【OUTER】
全外 full 【OUTER】
交叉连接 cross

*/

#（一、内连接
/*
分类：等值，非等值，自连接

特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
*/

#1.等值连接

#案例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 locations l
INNER JOIN departments d
ON l.location_id = d.location_id
GROUP BY city
HAVING COUNT()>3

#案例4：查询哪个部门员工个数>3的部门名字和员工个数，并按个数排序

#1.查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_name

#2.在1.结果上进行筛选员工个数>3的记录，并排序
SELECT COUNT(),department_id
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

#（二、非等值连接

#案例1：查询员工的工资级别

SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal

#案例2： 查询工资级别个数>2的个数，并且按工资级别降序

SELECT COUNT(),grade_level,salary
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

#（三、自连接

#查询姓名中包含k的员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id = m.manager_id
WHERE e.last_name LIKE ‘%k%’

#二、外连接
/*
应用场景：用于查询一个表中有，另一个表没有的记录

特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join 左边的是主表
右外连接，right join 右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

*/
#引入：查询男朋友不在男神表的女神名

SELECT b.name,bo.boyName
FROM beauty b
LEFT OUTER JOIN boys bo
on b.boyfriend_id = bo.id

#案例1:查询哪个部门没有员工

SELECT d.*，e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL

#全外连接与交叉连接

#练习：
#1.查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有则用NULL填充

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

#2.查询那个城市没有部门

SELECT city
FROM departments d
RIGHT JOIN locations l
on d.location_id = l.location_id
WHERE d.department_id is NOT NULL

#3.查询部门名为SAL或IT的员工信息

SELECT e.*
FROM departments d
LEFT JOIN employees e
ON e.department_id = d.department_id
WHERE department_name = ‘SAL’
OR department_name = ‘IT’

SELECT e.*,department_name
FROM departments d
LEFT JOIN employees e
ON e.department_id = d.department_id
WHERE d.department_name IN(‘SAL’,‘IT’)