MySql尚硅谷入门（六）

进阶六

#进阶6：连接查询
/*
含义:又成多表查询，当查询的字段来自多个表时，就会用
select 字段1，字段2；
from 表1,表2；

笛卡尔乘积现象：表1 有m行，表2有n行 结果= m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
按照年代分类：
sql92标准：仅仅支持内连接
sql199标准【推荐】：支持所有的内连接+外连接（左外和右外）+交叉连接

按照功能分类：
	内连接：
		等值连接
		非等值连接
		自连接
	外连接：
		左外连接
		右外连接
		全外连接
	交叉连接：

*/

SELECT * FROM beauty;
SELECT * FROM boys;
SELECT NAME,boyName FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id;

#一、sql标准
#1.等值连接

1、多表等值连接的结果为多表的交集部分
2、n表连接，至少需要n-1个连接条件
3、多表的顺序没有要求
4、一般需要为表起别名
5、可以搭配之前的所有查询子句来使用，排序，分组，筛选。

#案例1：查询女生名字和男生名字

SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id;

#案例2:查询员工名和对应的部门名。

SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;

#2.为表起别名
#查询工种号，员工名，工种号
#为表起别名 区分多个重名的字段

SELECT e.last_name,e.job_id,job_title
FROM employees AS e,jobs j
WHERE e.`job_id` = j.`job_id`;

#3、两个表的顺序是否可以调换
#查询工种号，员工名，工种号

SELECT e.last_name,e.job_id,job_title
FROM jobs j,employees e
WHERE e.`job_id` = j.`job_id`;

#4.可以加筛选
#案例：查询有奖金的员工名，部门名

SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL

#案例2：查询城市名中第二个字符为O的部门名和城市名

SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`= l.`location_id`
AND city LIKE '_o%'

#4.可以加分组
#案例1：查询每个城市的部门个数

SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY city;

#案例2：查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资

SELECT department_name,d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id` = e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name;

#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按照员工个数降序

SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

#7、可以实现三表连接
#案例：查询员工名，部门名和所在是城市

SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'

#2、非等值连接
#案例1：查询出员工的工资和工资级别

SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level = 'A';

#3.自连接
#案例：查询员工名和上级名称

SELECT e.employee_id,e.last_name,m.manager_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;

复习

#一、语法
/*
select 查询列表
from 表
where 筛选条件
order by 排序列表 【asc|desc】

二、特点
1、asc：升序，如果不写默认排序
desc：降序

2、排序列表 支持单个字段，支持多个字段、函数、表达式、别名

3、order by 的位置一般放在查询语句的最后（除limit之外）

/
/
二、常见函数
1、概述
功能：好处，提高重用性，隐藏实现细节
调用函数：select 函数名（实参列表）；
2、单行函数

1、concat;连接
2、substr;截取子串
3、upper;lower;大小写
4、replace；替换
5、length;获取字节长度
6、trim;去前后空格
7、lpad;左空格
8、rpad;右空格
9、instr;获取子串的第一次出现的索引

3、数学函数

1、ceil；向上取整
2、round；四舍五入
3、mod；取模
4、floor；向下取整
5、truncate；截断
6、rand；获取随机数，默认返回0-1之间的小数

4、 日期函数

1、now：返回当前日期+时间
2、year：返回年
3、month；返回月
4、day；返回日
5、date_from 将日期转成字符
6、curdate：返回当前日期
7、str_to_date;将字符解析成日期
8、curtime;返回当前时间
9、hour；小时
10、minute；分钟
11、second；秒
12、datediff；返回日期相差的天数
13、monthname；以英文的形式返回月

4、其他函数

version 数据库版本号
database 当前打开的数据库
user 当前用户
password（‘字符’）；自动加密
MD5（‘字符’）；自动加密

5、流程控制函数

1、if(条件表达式，表达式1，表达式2)；如果条件表达式成立，返回表达式1，否则返回表达式2

2、case 情况1
case 变量或者表达式或者字段
when 常量1 then 值1
when 常量2 then 值2
。。。
else 值n
end

2、case 情况2
case 变量或者表达式或者字段
when 条件1 then 值1
when 条件2 then 值2
。。。
else 值n
end

三、分组函数
1、分类
max、min、sum、avg、count

2、特点

1、语法
select max(字段) from表名；
2、支持的类型
sum、avg数值型
max、min、count可以处理任何类型数据
3、以上分组函数都忽略null

4、都可以搭配distinct使用，实现去重统计
select sum(distinct 字段) from表名；

5、count函数
count（字段）：统计该字段非空值的个数
count（*）；统计结果集的行数
count（1）；添加一列1 统计1的个数。

6、和分组函数一同查询的字段 ，要求输 group by之后的字段

#一、语法
select 分组函数，分组后的字段
from 表
where 筛选条件
group by 分组的字段
having 分组后的筛选
order by 排序列表

#二、特点
使用关键字 筛选的表 位置
分组前筛选 where 原始表 group by 的前面
分组后筛选 having 分组后的结果 group by 的后面

三、sql192语法
1、等值连接
语法：select 查询列表
from 表1 别名 ，表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by排序字段】
特点：
1、一般为表起别名
2、多表的顺序可以调换
3、n表连接至少需要n-1个连接
4、等值连接是多表的交集部分

2、等值连接
语法：select 查询列表
from 表1 别名 ，表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by排序字段】
特点：
1、一般为表起别名
2、多表的顺序可以调换
3、n表连接至少需要n-1个连接
4、等值连接是多表的交集部分

3、自连接
1、等值连接
语法：select 查询列表
from 表 别名1 ，表 别名2
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by排序字段】
特点：
1、一般为表起别名
2、多表的顺序可以调换
3、n表连接至少需要n-1个连接
4、等值连接是多表的交集部分
*/

#二、sql99语法
/*
语法：select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by】
【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、jinner join连接和sq192语法的等值连接的效果是一样的，都是查询多表的交集

#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 e.`last_name` LIKE '%e%'

#案例3：查询部门个数》3的城市名和部门个数（分组+筛选）
#1、查询每个城市的部门个数

SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY city
#2、在1的结果上筛选
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
#在1的结果上筛选
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的个数，并且按照工资级别的降序排序

SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.lowest_id AND g.highest_id;

#三）自连接
#查询姓名中包含字符k员工的名字、上级的名字

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%";

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

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

SELECT * FROM beauty;
SELECT * FROM boys;

#案例1：查询哪个部门没有员工
#左外

SELECT d.*,e.emplouee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

#右外

SELECT d.*,e.emplouee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

#交叉连接

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

#sql92和sql99
/*
功能：SQL199支持的较多
可读性：sql199时效内的连接条件和筛选条件的分离，可读性高

*/
#练习：
#一、查询编号》3的女神的男朋友的信息，如果有则列出详细信息。如果没有，用null填充

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

#二、查询哪个城市没有部门

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

#三、查询部门名为sal或it的员工信息

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

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';