MySQL中多表查询的一般操作汇总

Leave a reply 
/****************************创建表、插入数据**********************************/

CREATE TABLE department (
    id INT PRIMARY KEY auto_increment,
    dept_name VARCHAR ( 32 ) 
) DEFAULT charset = utf8mb4;

CREATE TABLE employee (
    id INT PRIMARY KEY auto_increment,
    username VARCHAR ( 32 ),
    age INT,
    gender VARCHAR ( 2 ),
    dept_id INT,
FOREIGN KEY ( dept_id ) REFERENCES department ( id ) 
) DEFAULT charset = utf8mb4;

INSERT INTO department VALUES (1,'研发部'),(2,'销售部'),(3,'财务部');

SELECT*FROM department;

INSERT INTO employee VALUES (1,'张三',18,'男',1),(2,'李四',18,'男',1),(3,'王五',18,'男',2),(4,'赵六',18,'女',3),(5,'田七',18,'男',2);

/****************************多表查询**********************************/

SELECT*FROM employee,department;-- 直接查询多张表得到的结果是多表的笛卡尔积，即 M x N

/*
    多表查询的分类：
        1、内连接查询
        2、外连接查询
        3、子查询
*/
# 1、内连接查询
-- 隐式内连接查询，使用where条件来过滤不必要的数据
SELECT*FROM employee,department WHERE employee.dept_id=department.id;-- 通过where语句消除重复数据

-- 多表查询一般会指定字段
SELECT employee.id,employee.username,employee.gender,department.dept_name FROM employee,department WHERE employee.dept_id=department.id;
-- 显式内连接
SELECT*FROM employee INNER JOIN department WHERE employee.dept_id=department.id;

-- 可以为表定义别名更方便书写
SELECT t1.id,-- 员工表id
t1.username,-- 员工表姓名
t1.gender,-- 员工表性别
t2.dept_name-- 部门表名称
FROM employee t1,department t2 WHERE t1.dept_id=t2.id;

-- 外连接：左外右外
SELECT t1.*,t2.dept_name,t2.dept_loc FROM employee t1 LEFT OUTER JOIN department t2 ON t1.dept_id=t2.id;

-- 内连接
# 单行单列
SELECT*FROM employee WHERE mgr=(SELECT id FROM employee WHERE ename='刘明');
SELECT*FROM employee WHERE salary> (SELECT avg(salary) FROM employee);
# 多行单列
SELECT*FROM employee WHERE mgr IN (SELECT id FROM employee WHERE mgr=12);
# 多行多列，多行多列可等效于子查询结果为虚拟表
SELECT t1.*,t2.dept_name,t2.dept_loc FROM (SELECT*FROM employee WHERE salary>=10000) t1 JOIN department t2 WHERE t1.dept_id=t2.id;

/*** 多表查询练习 ***/
# 查询所有员工信息，编号、姓名、工资、职务名称、职务描述
SELECT t1.id,t1.ename,t1.salary,t2.jname,t2.description FROM employee t1,job t2 WHERE t1.job_id=t2.id;
SELECT t1.id,t1.ename,t1.salary,t2.jname,t2.description FROM employee t1 INNER JOIN job t2 WHERE t1.job_id=t2.id; 
SELECT t1.id,t1.ename,t1.salary,t2.jname,t2.description FROM employee t1 LEFT OUTER JOIN job t2 ON t1.job_id=t2.id;
# 查询员工编号、姓名、工资、职务名称、职务描述、部门名称、部门位置
-- 内连接隐式连接查询
SELECT
    t1.id,-- 员工编号
    t1.ename,-- 姓名
    t1.salary,-- 工资
    t2.jname,-- 职务名称
    t2.description,-- 职务描述
    t3.dept_name,-- 部门名称
    t3.dept_loc -- 部门位置

FROM
    employee t1,
    job t2,
    department t3 
WHERE
    t1.job_id = t2.id 
    AND t1.dept_id = t3.id;
-- 左外连接查询
SELECT
    t1.id,-- 员工编号
    t1.ename,-- 姓名
    t1.salary,-- 工资
    t2.jname,-- 职务名称
    t2.description,-- 职务描述
    t3.dept_name,-- 部门名称
    t3.dept_loc -- 部门位置

FROM
    employee t1
    LEFT JOIN job t2 ON t1.job_id = t2.id
    LEFT JOIN department t3 ON t1.dept_id = t3.id 
ORDER BY
    t1.id;
# 查询员工姓名、工资、工资等级
-- 隐式内连接查询
SELECT
    t1.id,
    t1.ename,
    t1.salary,
    t2.grade 
FROM
    employee t1,
    salary_grade t2 
WHERE
    t1.salary BETWEEN t2.losalary 
    AND t2.hisalary;
-- 左外连接查询
SELECT
    t1.id,
    t1.ename,
    t1.salary,
    t2.grade 
FROM
    employee t1
    LEFT JOIN salary_grade t2 ON t1.salary BETWEEN t2.losalary 
    AND t2.hisalary;

# 查询员工姓名、工资、职务名称、职务描述、部门名称、部门位置、工资等级
-- 内连接隐式查询
SELECT
    t1.ename 姓名,
    t1.salary 工资,
    t2.jname 职务名称,
    t2.description 职务描述,
    t3.dept_name 部门名称,
    t3.dept_loc 部门位置,
    t4.grade 工资等级
FROM
    employee t1,
    job t2,
    department t3,
    salary_grade t4 
WHERE
    t1.job_id = t2.id and
    t1.dept_id = t3.id and
    t1.salary BETWEEN t4.losalary 
    AND t4.hisalary;

-- 左外连接查询
SELECT
    t1.ename 姓名,
    t1.salary 工资,
    t2.jname 职务名称,
    t2.description 职务描述,
    t3.dept_name 部门名称,
    t3.dept_loc 部门位置,
    t4.grade 工资等级 
FROM
    employee t1
    LEFT JOIN job t2 ON t1.job_id = t2.id
    LEFT JOIN department t3 ON t1.dept_id = t3.id
    LEFT JOIN salary_grade t4 ON t1.salary BETWEEN t4.losalary 
    AND t4.hisalary;

# 查询部门编号、部门名称、部门位置、部门人数
-- 子查询
SELECT*,(SELECT COUNT(id) FROM employee WHERE employee.dept_id=t1.id) '部门人数' FROM department t1;

SELECT t1.*,t2.ecount '部门人数' FROM department t1,(SELECT dept_id,COUNT(id) ecount FROM employee GROUP BY dept_id) t2 WHERE t1.id=t2.dept_id;

# 查询所有员工及其直接上级的姓名，没有领导的员工也需要查询出来
-- 隐式内查询
SELECT t1.ename 姓名,t2.ename 上级 FROM employee t1,employee t2 WHERE t1.mgr=t2.id;
-- 左外连接查询
SELECT t1.ename 姓名,t2.ename 上级 FROM employee t1 LEFT OUTER JOIN employee t2 ON t1.mgr=t2.id;

Leave a Reply