多表查询

'''
1.多表查询
   连表查询
   子查询
   如果一个问题既可以用连表查询解决,也可以使用子查询解决
   推荐使用连表查询,因为效率高

http://www.cnblogs.com/Eva-J/articles/9688383.html
'''

#连表查询
   #总是在连接的时候创建一张大表,里面存放的是两张表的笛卡尔积
   #在根据条件进行筛选就可以了
   #内连接 inner join .... on ....
       #select * from 表1,表2 where 条件
       #select * from 表1 inner join 表2 on 条件
       #select * from department inner join employee on department.id = dep_id;
   #外连接
       # 左外连接
       # select * from department left join employee on department.id = dep_id;
       # 右外连接
       # select * from department right join employee on department.id = dep_id;

       #全连接
# select * from department left join employee on department.id = dep_id
# union
# select * from department right join employee on department.id = dep_id;

#练习
#1.找到人力资源部的年龄大于40岁的人的姓名
#select * from department as d inner join employee as e on d.id = dep_id;
#select t2.name from department as d inner join employee as e on d.id = dep_id where t1.name = '人力资源' and age >40;

#2.找出年龄大于25岁的员工以及员工所在的部门
#select * from department as d inner join employee as e on d.id = dep_id;
#select d.name,e.name from department as d inner join employee as e on d.id = dep_id where age >25;

#3.以内连接的方式查询employee和department表,并且以age字段的升序方式显示
#select * from department as d inner join employee as e on d.id = dep.id order by age;

#4.求每一个部门多少人
#select d.name,count(e.id) from department as d left join employee as e on d.id = dep_id group by d.name;

#且按照人数从低到高排
#select d.name,count(e.id)  c from department as d left join employee as e on d.id = dep_id group by d.name order by c desc;
import os
os.path.exists(r'D:\workspace\com\baizhi\practice2\day17\init.sql')