进阶6

连接查询

含义：多表查询。当查询的字段来自多个表时，就会用到连接查询。
笛卡尔乘积现象：表1 m行，表2 n行结果就会编程m*n 行
发生的原有：没有添加连接条件。
如何避免：添加有效的连接条件。

分类：

按年代分类：

sql192标准 支持内连接   
sql199标准 [推荐] 支持内连接 外连接(左外和右外)+交叉连接

按功能分类：

内连接： 等值连接  非等值连接   自连接
外连接： 左外连接   右外连接   全外连接
交叉连接。

sql192标准

1.等值连接：

SELECT course,Name FROM scorefuqiang,stufuqiang WHERE scorefuqiang.stu_id=stufuqiang.id

上面的语句重点在于：where引用后的=

为表起别名

1.提高语句的简洁度。
2.区分多个重名的字段
注意：如果已经为表起了表名，则查询的字段就不能使用原来的表名进行限定。

案例：查询有奖金的员工名、部门名
select 名字，部门名，奖金 from 表1,表2 where 表1.部门=表2.部门 and 表1.奖金 is not null;

案例查询每个工种的工种名和员工个数，并且员工的个数降序。
select 工种名字,count(*) as number from 表1 a ，表2 b where a.id=b.id group by 工种 order by number desc
解析：这里工种与员工的个数是两个表所以from 需要两个表顺便定义好别名因为他俩表有相同的id，select 人数count(*)我设置了别名number 还有工种名在where设置连接条件两个别哦有相同的id 就用id作为有效连接。 group by 的作用是这里用来分组每个工种 order by 用员工个数来进行desc降序。

案例：三表连接需要注意的是进行等值连接的话不能三个表同时=需要两个两个表的字段进行
相等。中间用and进行隔开。

非等值连接

结构：
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
[and 筛选条件]
[group by 分组字段]
[having 分组后的筛选]
[order by 排序字段]

案例：查询员工的工资和工资级别
SELECT e.salary,j.grade_level FROM employees e,job_grades j WHERE
e.salary BETWEEN j.lowest_sal AND j.highest_sal
解析：这两个表中没有相同的的值可以用这里用了between and 在什么什么之间这里的之间就是最高工资和最低工资
我现在也说不明白，心里只有了大概。以后透彻了再来补全。

自连接

案例：查询员工名和他的上级名称

SELECT a.employee_id,a.last_name,b.employee_id,b.last_name
FROM employees a,employees b
WHERE a.manager_id=b.employee_id

结果：

101    Kochhar    100    K_ing
102    De Haan    100    K_ing
103    Hunold    102    De Haan
104    Ernst    103    Hunold
105    Austin    103    Hunold
106    Pataballa    103    Hunold
107    Lorentz    103    Hunold
108    Greenberg    101    Kochhar
109    Faviet    108    Greenberg
           ………………………………

练习题：

查询员工的job_id中包含a和e的并且a在e的前面。
SELECT job_id FROM employees WHERE job_id LIKE "%a%e%"

查询员工表的 employee_id，job_id,last_name 按照 department_id 降序 salary升序
SELECT employee_id,job_id,last_name FROM employees ORDER BY department_id DESC,salary ASC

查询90号部门的 job_id 和 location_id
SELECT e.department_id,e.job_id,d.location_id FROM employees e,departments d WHERE e.department_id=d.department_id AND e.department_id=90

sql99语法

语法：
{

内连接 ※
外连接：左外连接※与右外连接※，全外。
交叉连接

}

结构：
select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排列顺序]

连接类型的使用：
内连接： inner
外连接：左外：left [outer] 右外：right [outer] 全外：full [outer]
交叉连接： cross

内连接 inner可以省略

select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件;
分类：等值非等值自连接

1.等值连接

案例：查询员工名、部门名
select last_name,de
SELECT last_name,department_name FROM employees e INNER JOIN departments d ON
d.department_id=e.department_id

查询名字中包含e的员工名和工种名(添加筛选) 员工名 last_name 工种名 job_title
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的城市名和部门个数，(添加分组加筛选)
SELECT l.city,COUNT(*) FROM locations l INNER JOIN departments d ON l.location_id=d.location_id GROUP BY city HAVING COUNT(1)>3

查询员工名、部门名、工种名、并按部门名降序() 三表连接
SELECT e.last_name,d.department_name,j.job_title FROM departments d INNER JOIN employees e ON d.department_id=e.department_id INNER JOIN jobs j ON j.job_id=e.job_id ORDER BY d.department_name DESC

2.非等值连接

查询员工的等级标准。
-- 查询员工的等级标准。按照升序排列
SELECT e.last_name,j.grade_level FROM employees e INNER JOIN job_grades j ON e.salary BETWEEN lowest_sal AND highest_sal ORDER BY grade_level ASC

-- 查询员工的等级标准。进行各个等级的>20的个数
SELECT e.last_name,j.grade_level,COUNT(*) he FROM employees e INNER JOIN job_grades j ON e.salary BETWEEN lowest_sal AND highest_sal GROUP BY grade_level HAVING he>20

自连接

查询员工的名字以及他的上级的名字
SELECT a.last_name 员工 ,l.last_name 领导 FROM employees l INNER JOIN employees a ON a.manager_id=l.employee_id;

外连接：
应用场景：用于查询一个表中有另一个表没有的记录。
特点：
1.外连接的查询结果为主表中的所有记录。

如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null

外连接的结果=内连接结果+主表有而从表没有的记录
2.左外连接，left join 左边的是主表
右外连接，right join 右边的是主表
3.左外和右外交换两个表的顺序，可以实现同样的效果。

左外右外的区别就是：谁是主表谁在from 从表就在right left的后面的哪个表

左外连接：

女生单身的有谁。
SELECT b.name,bo.* FROM beauty b LEFT OUTER JOIN boys bo ON b.boyfriend_id=bo.id

哪个部门没有人。
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;

mysql 不支持全外。
全外连接=内连接的结果+表1有但是表2没有的+表2有但是表1没有的。

交叉连接
就是一个笛卡尔乘积
select b.,bo. from beauty b cross join boys bo;

MYSQL数据库5

进阶6

连接查询

分类：

sql192标准

为表起别名

非等值连接

自连接

练习题：

sql99语法

内连接 inner可以省略

评论

进阶6

连接查询

分类：

sql192标准

为 表 起别名

非等值连接

自连接

练习题：

sql99语法

内连接 inner可以省略

评论

为表起别名