Mysql数据库多表查询

你的名字 2024-03-30 15:07 46阅读 0赞

**目录**

一、总述

二、连接查询

三、子查询

四、多表查询的案例

--------------------

## 一、总述 ##

## ![364b4b368d984df1a75974801a1f21ad.png][]二、连接查询 ##

**1.内连接**：相当于查询A、B交集数据

> 语法：
> 
> \-- 隐式内连接
> 
> select 字段列表 from 表1，表2... where 条件;
> 
>     -- 查询emp 和 dept 的数据 ，emp.dep_id = dept.did
>     -- 隐式内连接：
>     SELECT
>     	* 
>     FROM
>     	emp,
>     	dept 
>     WHERE
>     	emp.dep_id = dept.did;-- 查询emp表的name,gender，dept表的danme:
>     SELECT
>     	emp.`name`,
>     	emp.gender,
>     	dept.dname 
>     FROM
>     	emp,
>     	dept 
>     WHERE
>     	emp.dep_id = dept.did;
>     	
>     -- 给表起别名
>     SELECT
>     	t1.`name`,
>     	t1.gender,
>     	t2.dname 
>     FROM
>     	emp t1,
>     	dept t2 
>     WHERE
>     	t1.dep_id = t2.did;
> 
>  \-- 显示内连接（inner可省略）
> 
>  select 字段列表 from 表1 \[inner\] join 表2 on 条件;
> 
>     -- 显式内连接
>     select * from emp inner join dept on emp.dep_id = dept.did;
>     -- 省略inner
>     select * from emp join dept on emp.dep_id = dept.did;

**内连接相当于查询A、B交集数据。**

![b3e3c24f35ca42cf9935273aec778a89.png][]

**2.外连接**：

**左外连接：**相当于查询A表所有数据和交集部分数据

> 语法：\-- 左外连接
> 
> select 字段列表 from 表1 left \[outer\] join 表2 on 条件
> 
>     -- 左外连接
>     -- 查询emp表所有数据和对应的部门信息
>     select * from emp left join dept on emp.dep_id = dept.did;

**右外连接：**相当于查询B表所有数据和交集部分数据

> 语法：\-- 右外连接
> 
> select 字段列表 from 表1 right \[outer\] join 表2 on 条件
> 
>     -- 右外连接
>     -- 查询dept表所有数据和对应的部门信息
>     select * from emp right join dept on emp.dep_id = dept.did;

![b3e3c24f35ca42cf9935273aec778a89.png][]

## 三、子查询 ##

1.子查询概念

查询中嵌套查询，称**嵌套查询**为**子查询**。

eg:

-- 查询工资高于猪八戒的员工信息
    select * from emp;
    
    -- 1.查询猪八戒的工资
    select salary from emp where name = "猪八戒" ;
    
    -- 2.查询工资高于猪八戒的员工信息
    select * from emp where salary > 3600;
    
    select * from emp where salary > (select salary from emp where name = "猪八戒") ;

2.子查询根绝查询结果不同，作用不同。

*  单行单列
 *  多行单列
 *  多行多列

**单行单列：作为条件值，使用= ！= > <等进行条件判断**

>  select 字段列表 from 表 where 字段名 = （子查询）;
> 
>     -- 查询工资高于猪八戒的员工信息
>     SELECT
>     	* 
>     FROM
>     	emp 
>     WHERE
>     	salary > ( SELECT salary FROM emp WHERE NAME = "猪八戒" );

**多行单列：作为条件值，使用in等关键字进行条件判断**

>  select 字段列表 from 表 where 字段名 in（子查询）;
> 
>     -- 查询“财务部”和“市场部”所有员工信息
>     SELECT
>     	* 
>     FROM
>     	emp 
>     WHERE
>     	dep_id IN ( SELECT did FROM dept WHERE dname = "财务部" OR dname = "市场部" );

**多行多列：作为虚拟表**

> select 字段列表 from （子查询） where 条件;
> 
>     -- 查询入职日期是‘2011-11-11’之后的员工信息和部门信息
>     SELECT
>     	* 
>     FROM
>     	-- 虚拟表t1
>     	( SELECT * FROM emp WHERE join_date > '2011-11-11' ) t1,
>     	dept 
>     WHERE
>     	t1.dep_id = dept.did;

## 四、多表查询的案例 ##

![4fe310bf20bc4b268d4a84461be268c5.png][]

**案例数据：**

DROP TABLE IF EXISTS emp;
    DROP TABLE IF EXISTS dept;
    DROP TABLE IF EXISTS job;
    DROP TABLE IF EXISTS salarygrade;
    
    
    -- 部门表
    CREATE TABLE dept (
      id INT PRIMARY KEY PRIMARY KEY, -- 部门id
      dname VARCHAR(50), -- 部门名称
      loc VARCHAR(50) -- 部门所在地
    );
    
    
    
    -- 职务表，职务名称，职务描述
    CREATE TABLE job (
      id INT PRIMARY KEY,
      jname VARCHAR(20),
      description VARCHAR(50)
    );
    
    -- 员工表
    CREATE TABLE emp (
      id INT PRIMARY KEY, -- 员工id
      ename VARCHAR(50), -- 员工姓名
      job_id INT, -- 职务id
      mgr INT , -- 上级领导
      joindate DATE, -- 入职日期
      salary DECIMAL(7,2), -- 工资
      bonus DECIMAL(7,2), -- 奖金
      dept_id INT, -- 所在部门编号
      CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
      CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
    );
    -- 工资等级表
    CREATE TABLE salarygrade (
      grade INT PRIMARY KEY,   -- 级别
      losalary INT,  -- 最低工资
      hisalary INT -- 最高工资
    );
    				
    -- 添加4个部门
    INSERT INTO dept(id,dname,loc) VALUES 
    (10,'教研部','北京'),
    (20,'学工部','上海'),
    (30,'销售部','广州'),
    (40,'财务部','深圳');
    
    -- 添加4个职务
    INSERT INTO job (id, jname, description) VALUES
    (1, '董事长', '管理整个公司，接单'),
    (2, '经理', '管理部门员工'),
    (3, '销售员', '向客人推销产品'),
    (4, '文员', '使用办公软件');
    
    
    -- 添加员工
    INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
    (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
    (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
    (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
    (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
    (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
    (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
    (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
    (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
    (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
    (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
    (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
    (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
    (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
    (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
    
    
    -- 添加5个工资等级
    INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
    (1,7000,12000),
    (2,12010,14000),
    (3,14010,20000),
    (4,20010,30000),
    (5,30010,99990);

**需求：**

1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

/*
    	分析：
    				1.员工编号，员工姓名，工资信息在emp员工表中
    				2.职务名称，职务描述信息在job职务表中
    				3.job职务表和emp员工表是“一对多”的关系，emp.job_id = job.id
    */
    -- 隐式内连接
    SELECT
    	emp.id,
    	emp.ename,
    	emp.salary,
    	job.jname,
    	job.description
    FROM
    	emp,
    	job 
    WHERE
    	emp.job_id = job.id ;
    	
    -- 显示内连接
    SELECT
    	emp.id,
    	emp.ename,
    	emp.salary,
    	job.jname,
    	job.description 
    FROM
    	emp
    	INNER JOIN job ON emp.job_id = job.id;

2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

/*
    	分析：
    				1.员工编号，员工姓名，工资信息在emp员工表中
    				2.职务名称，职务描述信息在job职务表中
    				3.job职务表和emp员工表是“一对多”的关系，emp.job_id = job.id
    				4.部门名称，部门位置来自于部门表dept
    				5.dept 和 emp 是一对多的关系 dept.id = emp.dep_id
    	
    */
    	
    -- 隐式内连接
    SELECT
    	emp.id,
    	emp.ename,
    	emp.salary,
    	job.jname,
    	job.description,
    	dept.dname,
    	dept.loc
    FROM
    	emp,
    	job,
    	dept
    WHERE
    	emp.job_id = job.id 
    	and dept.id = emp.dept_id;
    
    -- 显式内连接
    SELECT
    	emp.id,
    	emp.ename,
    	emp.salary,
    	job.jname,
    	job.description,
    	dept.dname,
    	dept.loc
    FROM
    	emp
    	INNER JOIN job ON emp.job_id = job.id
    	INNER JOIN dept ON emp.dept_id = dept.id;

3.查询员工姓名，工资，工资等级

/*
    		分析：
    				1.员工姓名，工资信息在emp员工表中
    				2.工资等级信息在salarygrade工资等级表中
    				3.emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
    */
    SELECT
    	emp.ename,
    	salary,
    	t2.* 
    FROM
    	emp,
    	salarygrade t2 
    WHERE
    	emp.salary >= t2.losalary 
    	AND emp.salary <= t2.hisalary;
    /*可用 between and 来代替*/

4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

-- 隐式内连接
    SELECT
    	emp.id,
    	emp.ename,
    	emp.salary,
    	job.jname,
    	job.description,
    	dept.dname,
    	dept.loc,
    	t2.grade
    FROM
    	emp,
    	job,
    	dept,
    	salarygrade t2
    WHERE
    	emp.job_id = job.id 
    	and dept.id = emp.dept_id
    	and emp.salary >= t2.losalary and emp.salary <= t2.hisalary;
    
    -- 显式内连接
    SELECT
    	emp.id,
    	emp.ename,
    	emp.salary,
    	job.jname,
    	job.description,
    	dept.dname,
    	dept.loc,
    	t2.grade
    FROM
    	emp
    	INNER JOIN job ON emp.job_id = job.id
    	INNER JOIN dept ON emp.dept_id = dept.id
    	INNER JOIN salarygrade t2 ON emp.salary between t2.losalary and t2.hisalary;

5.查询出部门编号、部门名称、部门位置、部门人数

/*
    		分析：
    				1.部门编号、部门名称、部门位置来自于dept部门表
    				2.部门人数：
    									在emp表中按照dept_id进行分组
    									再count(*)统计数量			
    				3. 使用子查询让部门表和分组后的表进行内连接
    */
    
    select dept_id,count(*) from emp group by dept_id;
    
    select * from dept;
    
    SELECT
    	dept.id,
    	dept.dname,
    	dept.loc,
    	t1.count 
    FROM
    	dept,(
    	SELECT
    		dept_id,
    		count(*) count 
    	FROM
    		emp 
    	GROUP BY
    		dept_id 
    	) t1 
    WHERE
    	dept.id = t1.dept_id;

[364b4b368d984df1a75974801a1f21ad.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/fa68866e8df94e66ba0fd7b8d4cc9a37.png
[b3e3c24f35ca42cf9935273aec778a89.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/b79a979b1f9a4d1dad20b4e1456b7a38.png
[4fe310bf20bc4b268d4a84461be268c5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/5790825fc8594e16b64775e38be6ca0c.png