MySQL连表查询

前期表准备：

#建表
create table dep (id int,name varchar(20));

create table emp (
    id int primary key auto_increment,
    name varchar(20),
    sex enum ('male','female') not null default 'male',
    age int,
    dep_id int
);

#录入数据
insert into dep values (200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营');
insert into emp values 
(7,'jason','male',18,200),
(8,'mony','female',28,201),
(9,'kay','male',38,201),
(10,'wose','female',48,202),
(11,'dessy','male',58,203),
(12,'mar','male',68,204);

查询：

select * from emp,dep where emp.dep_id = dep.id; #这是拼表查询
#mysql也知道，在后面查询数据过程中，肯定会经常用到拼表操作，所以特地开设了对应的方法
    inner join  #内连接
    left join   #左连接
    right join  #右连接
    union   #全连接
#inner join 内连接 #只拼接两张表中公有的数据部分
select * from emp inner join dep on emp.dep_id = dep.id;

#left join 左连接 左表所有的数据都展示出来，没有的对应项则用null展示；
select * from emp left join dep on emp.dep_id = dep.id;

#right join 右连接 右表所有的数据都展示出来，没有的对应项则用null展示；
select * from emp right join dep on emp.dep_id = dep.id;  

子查询：

#子查询就是我们平时解决问题的思路
#分步解决问题：
#第一步
#第二步
#将一个查询语句的结果当做另外一个查询语句的条件去用
#1.查询部门是技术或者人力资源的员工信息；
    #先获取部门的id
    select id from dep where name ='技术' or name ='人力资源'；
    #再去员工表里面筛选出对应的员工
    select name from emp where dep_id in (200,201);
 #将2条语句合为1条
 select * from emp where dep_id in (select id from dep where name='技术' or name='人力资源') ; 

总结：

• 表的查询结果可以作为其他表的查询条件；
• 也可以通过起别名的方式把它作为一张虚拟表跟其他表关联；
• 多表查询就是2种方式：
• 1、先拼接表再查询
• 2、子查询，一步一步来

#查询平均年龄在25岁以上的部门名称
#'只要是多表查询，就有两种思路：1.连表，2.子查询'
#连表操作
#1.先拿到部门和员工表，拼接之后的结果
#2.分析语义，得出需要进行分组
select dep.name from emp inner join dep on emp.dep_id = dep.id group by dep.name having avg(age)>25;
#子查询
select name from dep where id in (select dep_id from dep group by dep_id having avg(age)>25);

　　

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d004','1995-12-03','9999-01-01');
INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
INSERT INTO salaries VALUES(10009,95409,'2002-02-14','9999-01-01');
INSERT INTO salaries VALUES(10010,94409,'2001-11-23','9999-01-01');

#获取所有部门中员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列
select dept_emp.dept_no as dept_no_a, dept_emp.emp_no, max(salaries.salary) as salary
from dept_emp,salaries
where salaries.emp_no=dept_emp.emp_no
group by dept_emp.emp_no,dept_emp.dept_no
having max(salaries.salary) = 
(
    select max(salaries.salary) 
    from dept_emp inner join salaries
    on salaries.emp_no=dept_emp.emp_no 
    where dept_emp.dept_no = dept_no_a
)
order by dept_no_a;

 
delete from stock   
where (org_id,material_id,state) in  
(SELECT * from (select org_id,material_id, state 
from stock WHERE state = 1 group by org_id,material_id,state having count(*) > 1) as a )