MySQL基础之多表查询（实战）

        

目录

1，表的创建

        （1）员工表

         （2）部门表

        （3）薪资登记表 

        （4）学生表

        （5）课程表

        （6）学生选课表

2，举例

---

        前面发过一篇有关多表查询的知识点，虽然每个知识点也有对应的例子，但是可能效果不够好，所以还是想多做一些题以掌握知识点。

1，表的创建

        （1）员工表

-- 员工表
create table emp(
    id int primary key auto_increment comment '唯一id',
    name varchar(20) comment '员工姓名',
    age int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职日期',
    managerid int comment '直属领导id',
    dept_id int constraint fk_emp_dept_id foreign key (dpet_id) references dept(id) comment '部门id' 
) comment '员工表';

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

         （2）部门表

-- 部门表
create table dept(
    id int primary key auto_increment comment '唯一id',
    name varchar(20) comment '部门名称'
) comment '部门表';

insert into dept values (1, '研发部'), (2, '市场部'), (3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');

        （3）薪资登记表 

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

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

        （4）学生表

CREATE TABLE student (
  id int PRIMARY KEY COMMENT '主键id',
  name varchar(10) DEFAULT NULL COMMENT '姓名',
  no varchar(10) DEFAULT NULL COMMENT '学号',
) COMMENT='学生表';

 

        （5）课程表

CREATE TABLE course (
  id int PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
  name varchar(10) DEFAULT NULL COMMENT '课程名称',
) COMMENT='课程表';

        （6）学生选课表

CREATE TABLE student_course (
  id int PRIMARY KEY, AUTO_INCREMENT COMMENT '主键',
  studentid int NOT NULL COMMENT '学生id',
  courseid int NOT NULL COMMENT '课程id',
  KEY fk_courseid (courseid),
  KEY fk_studentid (studentid),
  CONSTRAINT fk_courseid FOREIGN KEY (courseid) REFERENCES course (id),
  CONSTRAINT fk_studentid FOREIGN KEY (studentid) REFERENCES student (id)
) COMMENT = '学生课程中间表'

 

2，举例

-- 1.查询员工的姓名、年龄、职位、部门信息（隐式内连接）。(练习隐式内连接语法)
-- 表：emp, dept
-- 连接条件：emp.dept_id = dept.id
select emp.name, emp.age, emp.job, dept.name from emp, dept where emp.dept_id = dept.id ;

-- 2.查询年龄小于30岁的员工姓名、年龄、职位、部门信息（显示内连接）。（练习显示内连接语法）
-- 表：emp, dept
-- 连接条件：emp.dept_id = dept.id
select emp.name, emp.age, emp.job, dept.name from emp inner join dept on emp.dept_id = dept.id where emp.age < 30;

-- 3.查询拥有员工的部门ID 、部门名称。（内连接，去重关键字）
-- 表：emp, dept
-- 连接条件：emp.dept_id = dept.id
select distinct dept.id, dept.name from emp, dept where emp.dept_id = dept.id;

-- 4.查询所有年龄大于40岁的员工，及其归属的部门名称;如果员工没有分配部门，也需要展示出来。（联系外连接语法）
-- 表：emp, dept
-- 连接条件：emp.dept_id = dept.id
select emp.*, dept.name from emp left join dept on emp.dept_id = dept.id where emp.age > 40;

-- 5.查询所有员工的工资等级。
-- 表：emp, salgarde
-- 连接条件：emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
select emp.name, salgrade.grade from emp, salgrade where emp.salary >= salgrade.losal && emp.salary <= salgrade.hisal;
select emp.name, salgrade.grade from emp, salgrade where emp.salary between salgrade.losal and salgrade.hisal;

-- 6.查询"研发部"所有员工的信息及工资等级。
-- 表：emp, salgarde, dept
-- 连接条件：emp.salary between salgrade.losal and salgrade.hisal, emp.dept_id = dept,id
-- dept.name = '研发部'
select emp.*, salgrade.grade from emp, dept, salgrade where dept.name = '研发部' && emp.salary between salgrade.losal and salgrade.hisal && emp.dept_id = dept.id;

-- 7.查询"研发部"员工的平均工资。
-- 表：emp, dept
-- 连接条件：emp.dept_id = dept.id
select avg(emp.salary) from emp, dept where emp.dept_id = dept.id && dept.name = '研发部';

-- 8.查询工资比"灭绝"高的员工信息。(标量子查询)
-- a.查询 灭绝 的薪资
-- select salary from emp where name = '灭绝';
-- b.查询比他工资高的员工信息
select * from emp where salary > (select salary from emp where name = '灭绝');

-- 9.查询比平均薪资高的员工信息。
-- a.查询员工的平均薪资
select avg(salary) from emp;
-- b.查询比平均工资高的员工信息
select * from emp where salary > (select avg(salary) from emp);

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

-- 11.查询所有的部门信息，并统计部门的员工人数。
select dept.*, (select count(*) from emp where emp.dept_id = dept.id) '人数' from dept;

-- 12.查询所有学生的选课情况，展示出学生名称，学号，课程名称。
-- 表：student, course, student_course
-- 连接条件：student.id = student_course.studentid, student_course.courseid = course.id
select student.name '姓名', student.no '学号', course.name '选课' from student, course, student_course where student.id = student_course.studentid && student_course.courseid = course.id;