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);
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);