mysql insert前查询 mysql用in查询

建表和数据准备
#建表
create table department(id int, name varchar(20));
create table employee(id int primary key auto_increment, name varchar(20), sex enum("male", "female") not null default "male", age int, dep_id int);#写入数据
insert into department values (200, "技术"), (201, "销售"), (202, "运营");
insert into employee (name, sex, age, dep_id) values
("haha", "male", 24, 201),
("hax", "male", 24, 201),
("sax", "male", 24, 202),
("sexy", "male", 24, 200),
("proxy", "male", 24, 201);
建表，准备数据
外连接语法
select 字段列表 from 表1 inner | left | right join 表2 on 表1.字段=表2.字段
交叉连接
直接将两张表拼接在一起，不使用任何匹配条件，生成笛卡尔积；
select * from department, employee;
内连接：inner join
找到两张表共有的部分，相当于利用条件在笛卡尔积中筛选出了正确的结果；
select employee.id employee.name employee.age from employee inner join department on employee.dep_id=department.id;
select employee.id employee.name employee.age from employee, department where employee.dep_id=department.id;
这两条SQL语句的效果是等同的；
左外连接：left join
优先显示左表的全部记录，以左表为准，找出所有员工信息，也包括没有部门的员工信息；本质就是在内连接的基础上增加左边有右边没有的结果；
select employee.id, employee.name, department.name as depart_name from employee left join department on employee.dep_id=department.id;
右外连接：right join
优先显示右表的全部记录，以右表为准；
select employee.id, employee.name, department.name as depart_name from employee right join department on employee.dep_id=department.id;
全外连接
在内连接的基础上增加左表有右表没有的和右表有左表没有的结果；mysql不支持全外连接full join；但可以用下面的方式实现全外连接，也就是用union连接上左连接和右连接
select * from employee left join department on employee.dep_id=department.id union select * from employee right join department on employee.dep_id=department.id;
子查询
子查询是将一个查询语句嵌套在另一个查询语句中，内层查询语句的结果可以作为外层查询语句的查询条件；在子查询中可以包含：in、not in、any、all、exists、not exists等关键字，还可以包含 =、!=、>、
带in关键字的子查询
#查询平均年龄在25岁以上的部门名
select id, name from department where id in
(select dep_id from employee group by dep_id having avg(age) > 25);#查看技术部门员工姓名
select name from employee where dep_id in
(select id from department where name="技术");#查看不足1人的部门名称
select name from department where id not in (select distinct dep_id from employee);
带比较运算符的子查询
#查询 大于 所有人平均年龄的员工名与年龄
select name,age from employee where age > (select avg(age) fromemployee);#查询 大于 部门内平均年龄的员工名、年龄
select t1.name, t1.age from employee t1
inner join
(select dep_id, avg(age) avg_age from employee group by dep_id) t2
on t1.dep_id=t2.dep_id where t1.age > t2.avg_age;
带exists关键字的子查询
exists关键字表示存在，使用exists关键字时，内层查询不返回查询的记录，而是返回一个True或者False；返回True时，外层查询语句将会进行查询；返回值为False时，外层查询语句不进行查询；
#存在id为200的部门，True；可以查询出结果
select * from employee where exists (select id from department where id=200);#不存在id为204的部门，False；
select * from employee where exists (select id from department where id=204);#查询结果为empty