黑马程序员多表查询综合案例（附数据库建表代码）

• 目录

  创建表

  创建emp表

  创建salgrade表

  创建dept表

  多表查询语句练习

  添加外键约束

  查询员工的姓名、年龄、职位部门信息（隐式内连接）

  查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显示内连接

  查询所有年龄大于40岁的员工，及其归属的部门名称，若员工没有分配部门，也要展示出来

  ​查询所有员工的工资等级

  查询“研发部”员工的平均工资

  查询工资比“灭绝”高的员工信息 子查询

  查询比平均薪资高的员工信息 子查询

  ​查询低于本部门平均工资的员工信息

  查询所有部门信息，并统计部门的员工人数

  ---

  创建表

• 创建emp表

• ​​​​​​​

create table emp(
    id int auto_increment comment 'id' primary key ,
    name varchar(50) not null comment '姓名',
    age int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    enterdate date comment '入职时间',
    managerid int comment '直属领导id',
    dept_id int comment '部门id'
)comment '员工表';

insert into emp values
    (1,'金庸',66,'总裁',20000,'2000-01-01',null,5),
    (2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
    (3,'杨逍',33,'开发',8400,'2000-11-03',2,1),
    (4,'韦一笑',48,'开发',11000,'2002-02-05',2,1),
    (5,'常遇春',43,'开发',10500,'2004-07-09',3,1),
    (6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1),
    (7,'灭绝',60,'财务总监',8500,'2002-09-12',1,3),
    (8,'周芷若',19,'会计',4800,'2006-06-02',7,3),
    (9,'丁敏君',23,'出纳',5250,'2009-05-12',7,3),
    (10,'赵敏',20,'市场部总监',12500,'2002-02-05',1,2),
    (11,'鹿杖客',56,'职员',3750,'2006-12-05',10,2),
    (12,'鹤笔翁',19,'职员',3750,'2006-05-25',10,2),
    (13,'方东白',19,'职员',5500,'2002-11-05',10,2),
    (14,'张三丰',88,'销售总监',14000,'2003-06-15',1,4),
    (15,'俞莲舟',38,'销售',4600,'2003-07-05',14,4),
    (16,'宋远桥',40,'销售',4600,'2004-09-18',14,4),
    (17,'陈友谅',42,null,2000,'2010-06-16',1,null);

创建salgrade表

create table salgrade(
grade int,
losal int,
hisal int 
)comment'薪资等级表';			 

insert into salgrade values
(1,0,3000),
(2,3001,5000),			 
(3,5001,8000),
(4,8001,10000),
(5,10001,15000),
(6,15001,20000),			 
(7,20001,25000),
(8,25001,30000);		 
			 

创建dept表

create table dept(
    id int auto_increment comment 'ID' primary key ,
    name varchar(50) not null comment '部门名称'
)comment '部门表';
insert into dept (id, name) values(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');

多表查询语句练习

添加外键约束

alter table emp add foreign key(dept_id) references dept(id);

查询员工的姓名、年龄、职位部门信息（隐式内连接）

select emp.name,emp.age,emp.job,dept.name from emp,dept where emp.dept_id=dept.id;

查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显示内连接

select emp.name,emp.age,emp.job,dept.name from emp,dept where emp.dept_id=dept.id and age<30;

查询所有年龄大于40岁的员工，及其归属的部门名称，若员工没有分配部门，也要展示出来

select e.*,d.name from emp as e left join dept as d on e.dept_id=d.id where age>40;

查询所有员工的工资等级

select emp.name,salgrade.grade from emp,salgrade where emp.salary between salgrade.losal and salgrade.hisal;

查询“研发部”员工的平均工资

select avg(emp.salary) from emp,dept where emp.dept_id=dept.id and dept.name='研发部';

查询工资比“灭绝”高的员工信息 子查询

select emp.*from emp where emp.salary>(select emp.salary from emp where emp.name='灭绝');

查询比平均薪资高的员工信息 子查询

select emp.* from emp where emp.salary>(select avg(emp.salary) from emp);

查询低于本部门平均工资的员工信息

-- a.指定部门平均薪资
select avg(e1.salary) from dept,emp e1 where e1.dept_id=1;
-- b.低于本部门平均薪资的员工信息
select e2.* from dept,emp e2 where e2.dept_id=dept.id and e2.salary<(select avg(e1.salary) from dept,emp e1 where e1.dept_id=1);

查询所有部门信息，并统计部门的员工人数

select dept.*,(select count(*) from emp e1 where e1.dept_id=dept.id)'人数' from dept;