【Mysql】数据操作2

多表查询

连表操作

 

完整版 

执行顺序:  FROM >ON>JOIN> WHERE>GROUP BY >HAVING>SELECT>DISTINCT>ORDERBY>LIMIT>SELECT

语法顺序: SELECT [DISTINCT]<name> FROM tablename <join type> [JOIN] <right table name> [ON]<condition on>[WHERE]<condition where> [GROUP BY]<name> HAVING <condition having> ORDER BY <name> LIMIT<int>

 

笛卡尔积

select * from employee, dep   #笛卡尔积

 

内连接

select * from employee inner join department on employee.dep_id= dep.id;

 

左连接

内连接的基础上保留左表的内容

select * from employee left join department on employee.dep_id= dep.id;

 

右连接

内连接的基础上保留右表的内容

select * from employee left join department on employee.dep_id= dep.id;

 

全外连接, full join , mysql不支持

内连接的基础上保留左右表没有对应关系的内容

select * from employee left join department on employee.dep_id= dep.id

union

select * from employee right join department on employee.dep_id= dep.id;

 

查询员工平均年龄大于30岁的部门名称

select dep.name,avg(age) from employee inner join dep on employee.dep_id = dep.id

group by dep.name

having avg(age) > 30;