1.多表查询
提前创建两张表,部门表(department),员工表(employee)
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), ('nvshen','male',18,200), ('xiaomage','female',18,204) ;
1.1多表查询链接
交叉连接:它会生成笛卡尔积,这种不用,只做了解
select * from employee,department;
内连接:取出两张表共有部分(join---on---的使用)
select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id; # 也可以这么写(推荐) select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
效果
外链接之左链接:优先显示左表全部记录
# 在内连接的基础上增加左边有,右边没有的结果 select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
效果
外链接之右链接:优先显示右表全部记录
select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
效果
全外链接:显示左右两个表全部记录
mysql通过使用union(使用union会自动去重,使用union all不会)关键字间接实现全外连接
select * from employee left join department on department.id=employee.dep_id union select * from employee right join department on department.id=employee.dep_id;
效果
1.2升级查询
以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工,姓名以及员工所在的部门
select employee.name,employee.age,department.name from employee inner join department on employee.dep_id=department.id where employee.age>25;
以内连接的方式查询employee和department表,并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc;
带in关键字的子查询
#查询平均年龄在25岁以上的部门名 select department.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 ='技术'); 或 select name from employee where dep_id = (select id from department where name='技术'); #查看空部门 select name from department where id not in(select dep_id from employee group by dep_id);
带比较运算符的子查询
# 查询大于所有人平均年龄的员工名与年龄 select name,age from employee where age>(select avg(age) from employee); # 查询大于部门内平均年龄的员工名、年龄 select t1.name,t1.age from employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id=t2.dep_id where t1.age>t2.avg_age;
带exists关键字的子查询
使用EXISTS关键字时,内层查询语句不返回查询的记录,而是返回True或False
返回True时,外层查询语句将进行查询
返回False时,外层查询语句不进行查询
2.一张图告诉你left join,inner join的区别