MySQL的基本操作及命令6

基本操作

`departments``locations`#进阶6
/*
二、sq199语法
语法：
	select 插寻列表
	from 表1 别名 【连接类型】
	join 表2 别名 
	on 连接条件
	【where 筛选条件】
	【group 便于分组】
	【having 筛选条件】
	【order by 排序列表】

分类：
内连接（☆）：inner
外连接
	左外（☆）：left【outer】
	右外（☆）：right【outer】
	全外：full 【outer】
	
交叉连接：cross

*/

#一）内连接
/*
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件 

分类：
等值、特点：
	（1）添加排序、分组、筛选
	（2）inner可以省略
	（3）筛选条件放在where后面，连接条件放在on后面，提高分离性、便于阅读
	（4）inner join 连接和sql192语法中的等值连接效果是一样的，都是查询多表的连接、

非等值
自连接

*/

#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 l.city
HAVING COUNT(*)>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 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;

#2.非等值连接
#案例1：查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

#案例2：查询工资级别的个数>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;

#3.自连接
#案例：查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id=m.employee_id;

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

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

*/

#引入：查询男朋友 不在男神表的女神名
SELECT * FROM beauty;
SELECT *FROM boys;
#左外连接
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id=bo.id;
#右外连接
SELECT b.name,bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.boyfriend_id=bo.id;

#案例1：查询哪个部门没有员工
#左外
USE myemployees;
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;

#全外

USE gitls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id=bo.id;

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

#sq192和sq199pk

功能：sq199支持的较多
可读性：sq199实现连接条件和筛选条件的分离，可读性较高

常见几种查询列表图鉴