MySql.doc文件中多表查询中的练习

1.//查询出公司所有员工姓名, 所在部门名
mysql> select s.name ,c.name from person as s join place as c on

s.department_id = c.id;


//查询出开发部所有员工名
mysql> select s.name from person as s join place as c on s.department_id =

c.id and c.name="开发部";

//查询出张三所在部门名称
mysql> select c.name from place as c join person as s on s.department_id =

c.id and s.name="张三";

2.//查询所有员工, 员工所属部门以及部门所在城市
mysql> select s.id,s.name,c.name,a.cityName from person as s join place as c

join city as a on s.department_id = c.id and c.city_id = a.id;


//查询北京的所有员工
mysql> select s.name from person as s join place as c join city as a on

s.department_id = c.id and c.city_id = a.id and a.cityName="北京";

3.查询王五的经理姓名
select manager_name from 部门信息 where id = (select manager_id from 员工信息

where 员工信息.name = "王五");
查询赵六手下的员工名
select name from 员工信息 where manager_id = (select id from 部门信息 where

manager_name = "赵六");


4.使用左外连接查询每个部门下都有哪些员工
mysql> select p.name ,pe.name from place as p left outer join person as per on

per.department_id = p.id;

使用右外连接查询所有员工所属部门
mysql> select p.name ,per.name from place as p right outer join person as per

on pe.department_id = p.id;

查询所有员工名和所在部门, 以及部门所在城市.
mysql> select p.name ,per.name ,c.cityName from place as p join person as per

join city as c on per.department_id = p.id and p.city_id = c.id;

查询在上海工作的所有员工

mysql> select name from person where department_id = (select id from place

where city_id =(select id from city where cityName = "上海"));

查询张三的经理姓名
mysql> select name from person where id = (select manager_id from person where

name ="张三");

5.///查询每个部门的平均工资
mysql> select p.name ,avg(pe.money) from person as pe right outer join place

as p on pe.department_id = p.id group by p.name;

///查询每个部门的平均工资(包括没有部门的).
mysql> select p.name ,avg(pe.money) from person as pe left outer join place as

p on pe.department_id = p.id group by p.name;

///查询每个城市的平均工资(包括没有城市的).
mysql> select c.cityName ,avg(pe.money) from person as pe left outer join city

as c on pe.department_id = c.id group by c.cityName;

查询每个城市的平均工资(包括没有城市的), 只显示高于7000的, 并且按平均工资从

高到低排序
mysql> select c.cityName ,avg(pe.money) from person as pe left outer join city

as c on pe.department_id = c.id group by c.cityName having avg(money)>7000;

///查询北京市年龄30岁以上员工的平均工资
mysql> select avg(pe.money) from person as pe join place as p join city as c

on pe.department_id = p.id and p.city_id = c.id where c.cityName = "北京" and

pe.age>30;

6.查询公司员工工资
///最大值,
mysql> select max(money) from person;
///最小值,
mysql> select min(money) from person;
///平均值,
mysql> select avg(money) from person;

///总和.

mysql> select count(money)*avg(money) from person;


5. 查询公司各部门的工资
///最大值,
mysql> select p.name ,max(pe.money) from person as pe right outer join place

as p on pe.department_id = p.id group by p.name;

/// 最小值,
mysql> select p.name ,min(pe.money) from person as pe right outer join place

as p on pe.department_id = p.id group by p.name;

/// 平均值,
mysql> select p.name ,avg(pe.money) from person as pe right outer join place

as p on pe.department_id = p.id group by p.name;


/// 总和.
mysql> select p.name ,avg(pe.money)*count(pe.money) from person as pe right

outer join place as p on pe.department_id = p.id;

6. 查询各个城市的员工人数.

mysql> select c.cityName,count(p.name) from person as pe join place as p join

city as c on pe.department_id = p.id and p.city_id = c.id group by c.id;

7. 查询员工最高工资和最低工资的差距.
mysql> select max(money)-min(money) from person;
8. 查询各个经理手下员工的平均工资.
mysql> select avg(money) from person where manager_id is not null group by

manager_id;


8.查询工资最高的员工是谁
mysql> select name from person where money = (select max(money) from person);
查询所有城市中的最高平均工资是多少
mysql> select max(money) from person where money= (select avg(pe.money) from

person as pe right outer join city as c on pe.department_id = c.id);

//查询25岁以上工资最高的员工的详细信息
mysql> select * from person ,place ,city where age>25 and money=(select max

(money) from person);