多表查询连接
1. 外链接语法
select 字段列表 from 表1 inner|left|right join 表2 on 表1.字段 = 表2.字段;
交叉连接
1. 交叉连接: 不适用任何匹配条件, 生成笛卡尔积.
select * from 表1, 表2;
内连接 (INNER)
1. 内连接: 只连接匹配的行 (两边共同拥有的连接字段)
select * from 表1 inner join 表2 on 表1.字段 = 表2.字段;
select * from 表1,表2 where 表1.字段 = 表2.字段;
示例:
select employee.id,employee.name,employee.age,employee.sex,department.name as d_name from employee inner join department on employee.dep_id=department.id;
外链接
1. 外链接之左连接：优先显示左表全部记录 (包含左表没有外键的字段)
select 字段1, 字段2 from 表1 left join 表2 on 表1.字段 = 表2.字段;
2. 外链接之右连接：优先显示右表全部记录 (包含右表没有外键的字段)
select 字段1, 字段2 from 表1 right join 表2 on 表1.字段 = 表2.字段;
注: 不存在外键关联的数据对应的外表字段显示为 NULL
全外链接 (UNION)
1. 全外连接：显示左右两个表全部记录 (在内连接的基础上增加左边有右边没有的和右边有左边没有的结果)
select * from 表1 left join 表2 on 表1.字段 = 表2.字段
union
select * from 表1 right join 表2 on 表1.字段 = 表2.字段;
2. 注意事项
1) mysql不支持全外连接 full JOIN
2) union与 union all的区别: union会去掉相同的纪录
符合条件的连接查询
语法:
select 表1.字段,表2.字段 from 表1 inner|left|right join 表2
on 表1.字段 = 表2.字段 where 条件限制;
示例1:
以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department
on employee.dep_id = department.id where age > 25;
示例2:
以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name as d_name from employee,department where employee.dep_id = department.id and age > 25 order by age;
子查询
1. 子查询是将一个查询语句嵌套在另一个查询语句中。
2. 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3. 子查询中可以包含： IN、 NOT IN、 ANY、 ALL、 EXISTS 和 NOT EXISTS等关键字
4. 还可以包含比较运算符：= 、 !=、> 、<等
带 IN 关键字的子查询
语法:
select 字段1,字段2 from 表名 where 关联字段
in (select 字段 from 表名 group by 字段 having 条件 > 值);
1. 查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
2. 查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name='技术');
3. 查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);
带比较运算符的子查询
1. 比较运算符：=、!=、>、>=、<、<=、<>
示例:
1) 查询大于所有人平均年龄的员工名与年龄
select name,age from employee where age > (select avg(age) from employee);
2) 查询大于部门内平均年龄的员工名、年龄 (加别名来区分左右表)
select t1.name,t1.age from employee as 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关键字的子查询
1. EXISTS关键字判断是否存在。
EXISTS对外表用loop逐条查询，每次查询都会查看 EXISTS的条件语句，当 EXISTS里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录，反之如果 EXISTS里的条 件语句不能返回记录行，则当前loop到的这条记录被丢弃， EXISTS的条件就像一个bool条件，当能返回结果集则为 true，不能返回结果集则为 false
示例:
1) department表中存在dept_id=203, True
select * from employee where exists (select id from department where id=200);
建表与数据准备
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,'销售'),
(203,'运营');
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204);
