您的位置: 首页  >  文章  >  MySQL学习：多表查询（带实例）

MySQL学习：多表查询（带实例）

分类: 文章 • 2022-10-18 08:56:19

#创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES (‘开发部’),(‘市场部’),(‘财务部’);

#创建员工表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1),-- 性别
salary DOUBLE,
join_date DATE,-- 入职日期
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(id)-- 外键，关联部门表（部门表的主键）
);
INSERT INTO emp (NAME,gender,salary,join_date,dept_id) VALUES (‘花千骨’,‘女’,7000,‘2020-02-09’,1);
INSERT INTO emp (NAME,gender,salary,join_date,dept_id) VALUES (‘白子画’,‘男’,8000,‘2020-05-08’,2);
INSERT INTO emp (NAME,gender,salary,join_date,dept_id) VALUES (‘弥漫天’,‘女’,9000,‘2020-1-30’,2);
INSERT INTO emp (NAME,gender,salary,join_date,dept_id) VALUES (‘糖宝’,‘女’,7500,‘2020-02-14’,3);
INSERT INTO emp (NAME,gender,salary,join_date,dept_id) VALUES (‘范闲’,‘男’,8500,‘2020-02-01’,1);

#查看部门表
SELECT * FROM dept;

MySQL学习：多表查询（带实例）
#查看员工表
SELECT * FROM emp;
MySQL学习：多表查询（带实例）
SELECT * FROM dept,emp;
MySQL学习：多表查询（带实例）
SELECT * FROM emp,dept;
MySQL学习：多表查询（带实例）
显示两表的笛卡儿积
那么，如何消除不需要的数据?

多表查询的分类:

1、内连接查询：

  1. 隐式内连接：使用where条件来清除无用数据
    – 查询所有员工信息和对应的部门信息
    SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;
    MySQL学习：多表查询（带实例）
    – 查询员工的名称，性别，对应的部门名称
    SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;
    – 给表起别名
    SELECT t1.name,t1.gender,t2.id
    FROM emp t1,dept t2
    WHERE t1.dept_id = t2.id
    MySQL学习：多表查询（带实例）
  2. 显式内连接
    语法：
    SELECT 字段列表 FROM 表名1 INNER JOIN 表名2 ON 条件（inner）可以省略
    如：
    SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
  3. 注意：查询哪些表，条件是什么，展示字段有哪些

2、外连接查询：

  1. 左外连接：
    语法：
    SELECT 字段列表 FROM 表1 LEFT OUTER JOIN 表2 ON 条件;（outer也可选）
    如：
    SELECT t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id=t2.id;
    左外连接查询的是左表所有数据及其交集部分。
    内连接查询交集部分
    右外连接查询的是右表所有数据及其交集部分。
    左右相对而言
  2. 右外连接：
    语法：
    SELECT 字段列表 FROM 表1 RIGHT OUTER JOIN 表2 ON 条件;（outer也可选）
    如：
    SELECT t1.*,t2.name FROM emp t1 RIGHT JOIN dept t2 ON t1.dept_id=t2.id;

3、子查询：

查询中嵌套查询，称为子查询
– 查询工资最高的员工信息
– 1、查询最高的工资是多少 9000
SELECT MAX(salary) FROM emp;
– 2、查询员工信息，并且工资等于最大值的
SELECT * FROM emp WHERE emp.salary = 9000;
– 一步到位
SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);

子查询的不同情况

  1. 子查询的结果是单行单列的
    子查询可以作为条件，使用运算符去判断
    – 查询员工工资小于平均工资的人
    SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

  2. 子查询的结果是多行单列的
    – 查询财务部的所有员工信息
    SELECT id FROM dept WHERE NAME = ‘财务部’;
    SELECT * FROM emp WHERE dept_id = 3;
    – 查询财务部和市场部的所有员工信息
    SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’;
    SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
    SELECT id FROM dept WHERE NAME IN (‘财务部’,‘市场部’);
    SELECT * FROM emp WHERE dept_id IN (3,2);
    – 子查询
    SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN (‘财务部’,‘市场部’));
    子查询可作为条件，使用运算符in来判断

  3. 子查询的结果是多行多列的
    – 查询员工入职日期是2020-02-09之后的员工信息和部门信息
    SELECT * FROM emp WHERE emp.join_date>‘2020-02-09’;
    SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.join_date>‘2020-02-09’) t2
    WHERE t1.id = t2.dept_id;
    ————————————
    – 普通内连接也可实现
    SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > ‘2020-02-09’;
    子查询可作为一张虚拟表

相关推荐