一. 准备表
#部门表 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 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) ;
查看表结构
二. 多表连接查询
#语法: select * from 表1 <inner,left,right> join 表2 on 表1.字段=表2.字段;
1.交叉连接:没有任何查询条件,直接生成笛卡尔积.(笛卡尔积:https://baike.baidu.com/item/%E7%AC%9B%E5%8D%A1%E5%B0%94%E4%B9%98%E7%A7%AF)
select * from employee,department;
2.内连接:找出两张表共有的部分,相当于利用条件在笛卡尔积的基础上进行筛选正确的结果.(只连接匹配的行)
#找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了正确的结果 #department没有204这个部门,因而employee表中关于204这条员工信息没有匹配出来 select * from employee inner join department on employee.dep_id = department.id; #还可以使用where条件来查询,建议使用上面的语句 select * from employee,department where employee.dep_id = department.id;
3.左连接:优先显示坐标表的全部记录
select * from employee left join department on employee.dep_id = department.id; select * from department left join employee on employee.dep_id = department.id;
4. 右连接:优先显示右边的全部记录.
#优先显示员工表(employee)的信息
select * from department right join employee on employee.dep_id = department.id; #优先显示部门表(department)的信息 select * from employee right join department on employee.dep_id = department.id;
5.全外连接:显示2张表中的所有记录
注意:mysql不支持全外连接 full join
强调:mysql可以使用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;
三. 符合条件连接查询
1.需求,查询所有年龄大于25岁的员工姓名,年龄和部门名称.
select employee.name,employee.age,department.name from employee inner join department on employee.dep_id = department.id where age >25;
2.按照员工的年龄,进行降序排序
select * from employee inner join department on employee.dep_id = department.id order by age desc;