DBA 数据库管理 . 内连接.外链接

内连接

通过链接把多张表组合成一张临时表

select  表头名  from  表名1 inner join 表名2 ;

 select * from employees inner join departments

select  表头名  from  表名1 inner join 表名2    on   链接条件;

select * from employees inner join departments on employees.dept_id = departments.dept_id;
在临时组成新表里，可以在哟以下处理

select  表头名  from  表名1 inner join 表名2    on   链接条件[where | order by | group by|  limit | having];

等值链接 ： 前提是链接里有储存相同数据的表头

select * from employees inner join departments on employees.dept_id = departments.dept_id;

定义别名后必须使用别名表示表名

1. select e.* , d.dept_name
2. from employees as e inner join departments as d on e.dept_id=d.dept_id;

查询每个员工2018年的总工资

select name ,sum(basic+bonus) as total from employees inner join salary on employees.employee_id = salary.employee_id where year(date)=2018 group by name;

select salary.employee_id ,sum(basic+bonus) as total from employees inner join salary on employees.employee_id = salary.employee_id  where year(date)=2018 group by employee_id having total > 300000 order by total desc ,employee_id asc;查询2018年总工资大于30万的员工，按2018年总工资降序排列

非等值链接： 表里没有存储相同数据的表头

select employee_id ,basic,garde from salary inner join wage_grade on salary.basic between wage_grade.low and wage_grade.high where year(date)=2018 and month(date)=12;
查询2018年12月员工基本工资

select garde as 级别,count(employee_id) as 总人数 from salary inner join wage_grade on salary.basic between wage_grade.low and wage_grade.high where year(date)=2018 and month(date)=12 group by garde;查询2018年12月员工基本工资级别的人数

3张表连接的例子

select name ,basic ,garde from employees inner join salary on employees.employee_id  = salary.employee_id  inner join wage_grade on salary.basic between wage_grade.low and wage_grade.high where year(date)=2018 and month(date)=12;

外链接查询

外链接是比较2个表的不同

左链接 用左表数据和右表数据作比较  输出结果左表头数据全部显示，

右边仅显示与链接匹配的`行

select  表头名  from  表名1   left jion  表名2  on 链接条件  ;

select  表头名  from  表名1   left jion  表名2  on 链接条件 ·[where |group by| orderby | having | limit] ;

 select dept_name, name from departments left join employees on departments.dept_id=employees.dept_id where name is null;
 

右链接 用右表数据和左表数据作比较  输出结果右表头数据全部显示

右边仅显示与链接匹配的`行

select  表头名  from  表名1   right  jion  表名2  on 链接条件 ·[where |group by| orderby | having | limit] ;

mysql> select dept_name,name from departments as d right join employees as e on d.dept_id=e.dept_id where dept_name is null;

update employees set dept_id=11 where name="bob"; 

全链接查询

把多个select命令查询的行一起数据  多个select查询，表头数要一致

去重行的输出

mysql> (select date , max(basic) as 工资 from salary where date=20180110)union(select date,min(basic) from salary where date=20180110);

（select 查询命令）union  （select 查询命令）;

不去重行的输出

select 查询命令）union  all（select 查询命令）;

嵌套查询

select 查询命令包含select查询命令

where之后嵌套查询  分组

select  表头名  from 库.表  where  表头名   判断符号 （ select  查询命令）

select employee_id,date,basic,bonus from salary where year(date)=2018 and month(date)=12 and basic > (select basic from salary where year(date)=2018 and month(date)=12 and employee_id=100);

having之后嵌套查询    把（）里的查询结果作为过滤条件，

select  表头名  from 库.表  having  表头名   判断符号 （ select  查询命令） 

select dept_id , count(name) as total from employees group by dept_id

having total < (

select count(name) from employees where dept_id=(

select dept_id from departments where dept_name='开发部')

from之后嵌套查询   把（）的查询结果作表使用

select  表头名  from  （select 查询结果）as 临时表名 where 筛选条件；

select employee_id ,name,email,dept_name from (select d.dept_name,e.* from departments as d inner join employees as e on d.dept_id=e.dept_id) as tmp_table where dept_id=3;

select之后嵌套查询

select  表头名, (select 查询命令)as 表头名  from 库.表 where 筛选条件;

select d.* ,(select count(name)from employees as e where d.dept_id=e.dept_id)as vumen from departments as d;