数据库连接查询004

1、内外交叉连接查询

1.1内连接等值查询

根据两个表的ID连接查询

mysql> select b.name,y.boyname 
from boys y,beauty b 
where b.boyfriend_id=y.id;
+-----------+-----------+
| name      | boyname   |
+-----------+-----------+
| 杨颖      | 黄晓明    |
| 热巴      | 鹿晗晗    |
| 周芷若    | 张无忌    |
| 赵敏      | 张无忌    |
| 小昭      | 张无忌    |
| 王语嫣    | 段誉      |
| 岳灵珊    | 令狐冲    |
+-----------+-----------+

连接查询添加筛选条件：

还可以加筛选条件：不用where，而是用and连接
查询城市名中第二个字符是o的城市名和部门名？

mysql> select department_name,city
    -> from departments d,location l
    -> where d.'location_id'=l.'location_id'
    -> and city like '_o%';

连接查询添加分组条件：
eg1、查询每个城市的部门个数：

mysql> select count(*) 个数,city
    -> from departments d,locations l
    -> where d.'location_id'=l.'location_id'
    -> group by city;

查询有奖金的部门的部门名和领导编号，和该部门的最低工资

select department_name,e.manager_id,min(salary) min_sal
    -> from departments d, employers e
    -> where d.'department_id'=e.'department_id'
    -> and depact is not null;
    -> group by department
    

查询每个工种的工种名和员工个数，按员工个数降序排序

mysql> select job_title,count(*) num
    -> from employees e, jobs j
    -> where e.'job_title'=j.'job_title'
    -> group by job_title,
    -> order by num desc;

3表连接
查询员工名，部门名，和所在城市
第2个=连接使用and，然后再使用and接筛选条件，
再看排序；

mysql> 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%'
    -> order by department_name desc;

总结
多表等值连接的结果是交集部分
n表连接，需要n-1个连接条件
多表的顺序无关
可搭配子句：筛选条件子句，分组子句，排序子句

1.2非等值连接

eg1、查询员工表的工资和工资等级

mysql> select salary,grade_level
    -> from employees e,job_grades g
    -> where salary between g.'lowest_sal' and g.'hight_sal';

可以加筛选、分组、排序子句

1.3、自连接

只在当前这张表里查询，不过是查询2次或多次，
eg1、查询员工名和其领导的名字

mysql> select e.employee_id,e.last_name,m.employee_id,m.last_name
    -> from employees e,employees m  # 别名的好处
    -> where e.'manager_id'=m.'employee_id';

加密：
passwd(‘字符’);# 对这个字符进行加密
MD5(‘字符’); # 使用MD5进行加密

2、sql99语法

mysql> select 查询列表
    -> from employeer e 连接类型 join department d
    -> on 连接条件
    -> where 筛选条件
    -> group by 分组
    -> having 筛选条件
    -> order by 排序列表
    -> 
    -> 内连接 : inner
    -> 外连接：左外：left outer
    ->         右外：right outer
    ->         全外：full outer
    -> 交叉连接：cross

eg1、查询员工名，部门名

mysql> select last_name,department_name
    -> from department d inner join employees e
    -> on e.'department_id'=d.'department_id';

eg2、添加筛选
查询名字中包含e的员工名和工种名

mysql> select last_name,job_name
    -> from employee e inner join jobs j
    -> on e.'department_id'=j.'department_id'
    -> where e.'last_name' is like "%a%";

eg3、挑选部门个数>3的城市名和部门个数

mysql> select city,count(*)
    -> from location l inner join department d
    -> on l.'department_id'=d.'department_id'
    -> group by city
    -> having count(*)>3;

eg4、查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序显示

mysql> select city,count(*)
    -> from location l inner join department d
    -> on l.'department_id'=d.'department_id'
    -> group by city
    -> having count(*)>3;

eg5、查询部门名，员工名，工种名，并按部门名排序
直接把连接条件紧跟表的后面，如下第3、4行

mysql> select last_name,department_name,jobs_name
    -> from employee e 
    -> inner join department d on e.'department_id'=d.'department_id'
    -> inner join jobs j on e.'job_id'=j.'job_id'
    -> order by d.'department_name' desc;

2.2非等值连接

eg1、问每个员工的工资级别？

mysql> select lastname,grade
    -> from employees e inner join department d
    -> on e.'salary' betweem d.'lowest_sal' and d.'hight';

eg2、查询工资级别>20的个数，并按工资级别降序

mysql> select count(*),salary_level
    -> from employees e inner join department d
    -> on e.'salary' between d.'low_sal' and d.'hight_sal'
    -> group by d.'grade_level'
    -> having count(*)> 20;
    -> order by d.'sal_level' desc;

2.3、自连接

eg1、查询员工的名字和领导的名字

mysql> select e.last_name,m.last_name
    -> from employees e inner join employee m
    -> on e.'manage_id'=m.'employee_id';

3、外连接

应用场景：一个表中有，另一个表中没有

特点：
1、外连接的查询结果主表中的所有记录
如果从表有对应的记录，就把对应的值添加进去，
如果从表中没有相对应的值，就是用null填充
外连接的查询结果=内连接+主表中有而从表中没有的记录
2、左外连接：主表是left join 左边的表
右外连接：主表是right join 右边的表
3、左外和右外交换两个表的位置可以得到相同的结果

eg1、查询男朋友不在男神表中的女神名：

mysql> select b.'name',bo.*
    -> from beauty b left outer join boys bo
    -> on b.'boyfriend_id'=bo.'id'  # 把女神表的所有行都表示出来了，
    
    -> where bo.'id' is null;

eg2、查询哪个部门没有员工
主表：部门表

mysql> select e.'last_name',d.*
    -> from department d left join employee e
    -> on d.'department_id'=e.'department_id'
    -> where e.'employee_id' is null;

3.3全外连接

把主表和从表的：内连接匹配结果+主表有从表里没有的记录（左外连接和右外连接查询这两部门）
+从表里有主表里没有的记录

mysql> select b.*,bo.*
    -> from beauty b full join boys bo
    -> on b.'boyfriend_id'=bo.id;

4、交叉连接

笛卡尔集

mysql> select b.* bo.*
    -> from beauty b
    -> cross join boys bo;