多表查询

最新推荐文章于 2024-09-09 09:47:53 发布
最新推荐文章于 2024-09-09 09:47:53 发布
阅读量139 收藏
点赞数
分类专栏： JavaWeb 文章标签： 数据库 sql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Janna_woo/article/details/130046906
版权

文章目录

概念

  • 从多张表查询出数据
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
# 创建部门表
CREATE TABLE dept
(
    did   INT PRIMARY KEY AUTO_INCREMENT,
    dname VARCHAR(20)
);
# 创建员工表
CREATE TABLE emp
(
    id        INT PRIMARY KEY AUTO_INCREMENT,
    NAME      VARCHAR(10),
    gender    CHAR(1),                         -- 性别
    salary    DOUBLE,                          -- 工资
    join_date DATE,                            -- 入职日期
    dep_id    INT,
    FOREIGN KEY (dep_id) REFERENCES dept (did) -- 外键，关联部门表(部门表的主键)
);-- 添加部门数据
INSERT INTO dept (dNAME)
VALUES ('研发部'),
       ('市场部'),
       ('财务部'),
       ('销售部');
-- 添加员工数据
INSERT INTO emp(NAME, gender, salary, join_date, dep_id)
VALUES ('孙悟空', '男', 7200, '2013-02-24', 1),
       ('猪八戒', '男', 3600, '2010-12-02', 2),
       ('唐僧', '男', 9000, '2008-08-08', 2),
       ('白骨精', '女', 5000, '2015-10-07', 3),
       ('蜘蛛精', '女', 4500, '2011-03-14', 1),
       ('小白龙', '男', 2500, '2011-02-14', null);

select *
from emp,
     dept; -- 从emp和dept表中查询所有的字段数据

结果如下：
在这里插入图片描述

笛卡尔积：有A,B两个集合， 取A,B 所有的组合情况 ：6*4 =24

从上面的结果我们看到有一些无效的数据，如 孙悟空 这个员工属于1号部门，但也同时关联的2、3、4号部门。所以我们要通过限制员工表中的 dep_id 字段的值和部门表 did 字段的值相等来消除这些无效的数据，

select * from emp , dept where emp.dep_id = dept.did;

执行后结果如下：（连接查询）
在这里插入图片描述
 

分类

  • 连接查询
    在这里插入图片描述
    • 内连接查询 ：相当于查询AB交集数据
    • 外连接查询
      • 左外连接查询 ：相当于查询A表所有数据和交集部门数据
      • 右外连接查询 ： 相当于查询B表所有数据和交集部分数据
  • 子查询

 

连接查询

内连接查询

  • 语法
-- 隐式内连接 
SELECT 字段列表 FROM 表1,表2… WHERE 条件; 

-- 显示内连接 
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

 

隐式内连接
SELECT
	* 
FROM	
	emp, 
	dept 
WHERE
	emp.dep_id = dept.did;

执行上述语句结果如下：
在这里插入图片描述

  • 查询 emp的 name， gender，dept表的dname
SELECT emp.NAME,
       emp.gender,
       dept.dname
FROM emp,
     dept
WHERE emp.dep_id = dept.did;

执行语句结果如下：
在这里插入图片描述
上面语句中使用表名指定字段所属有点麻烦，sql也支持给表指别名，上述语句可以改进为：

-- 给表起别名
SELECT t1.NAME, t1.gender, t2.dname
FROM emp t1,
     dept t2
WHERE t1.dep_id = t2.did;

 

显式内连接
select *
from emp
         inner join dept on emp.dep_id = dept.did;
-- 上面语句中的inner可以省略，可以书写为如下语句
select *
from emp
         join dept on emp.dep_id = dept.did;

执行结果如下：
在这里插入图片描述
 

外连接

  • 语法
-- 左外连接 
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件; 
-- 右外连接 
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
  • 左外连接：相当于查询A表所有数据和交集部分数据
  • 右外连接：相当于查询B表所有数据和交集部分数据
     
左外连接
  • 查询emp表所有数据和对应的部门信息
select * from emp left join dept on emp.dep_id = dept.did;

执行语句结果如下：
在这里插入图片描述

结果显示查询到了左表（emp）中所有的数据及两张表能关联的数据。
 

右外连接
  • 查询dept表所有数据和对应的员工信息
select * from emp right join dept on emp.dep_id = dept.did;

执行语句结果如下：
在这里插入图片描述
结果显示查询到了右表（dept）中所有的数据及两张表能关联的数据。

要查询出部门表中所有的数据，也可以通过左外连接实现，只需要将两个表的位置进行互换：

select * from dept left join emp on emp.dep_id = dept.did;

执行语句结果如下：
在这里插入图片描述
 

子查询

  • 概念
    • 查询中嵌套查询，称嵌套查询为子查询。

什么是查询中嵌套查询呢？我们通过一个例子来看：
需求：查询工资高于猪八戒的员工信息。

第一步：先查询出来 猪八戒的工资

select salary
from emp
where name = '猪八戒';

第二步：查询工资高于猪八戒的员工信息

select *
from emp
where salary > 3600;

在这里插入图片描述
第二步中的3600可以通过第一步的sql查询出来，所以将3600用第一步的sql语句进行替换
这就是查询语句中嵌套查询语句。

select *
from emp
where salary > (select salary from emp where name = '猪八戒');

这就是查询语句中嵌套查询语句

  • 子查询根据查询结果不同，作用不同
    • 单行单列
      • 子查询语句作为条件值，使用 = != > < 等进行条件判断
SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);
    • 多行单列
      • 子查询语句作为条件值，使用 in 等关键字进行条件判断
SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);
    • 多行多列
      • 子查询语句作为虚拟表
SELECT 字段列表 FROM (子查询) WHERE 条件;

案例：

  • 查询 ‘财务部’ 和 ‘市场部’ 所有的员工信息
-- 查询 '财务部' 或者 '市场部' 所有的员工的部门did
select did
from dept
where dname = '财务部'
   or dname = '市场部';
   
select *
from emp
where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');

在这里插入图片描述

  • 查询入职日期是 ‘2011-11-11’ 之后的员工信息和部门信息
-- 查询入职日期是 '2011-11-11' 之后的员工信息
select *
from emp
where join_date > '2011-11-11';

在这里插入图片描述

-- 将上面语句的结果作为虚拟表和dept表进行内连接查询
select *
from (select * from emp where join_date > '2011-11-11') t1,
     dept
where t1.dep_id = dept.did;

在这里插入图片描述
 

案例

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);

SELECT *
FROM emp;

SELECT *
FROM job;

在这里插入图片描述
需求：

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

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

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

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

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

实现：

-- 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.dept_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 dept.id = emp.dept_id
;

在这里插入图片描述

-- 3.查询员工姓名，工资，工资等级
/*
	分析：
		1. 员工姓名，工资 信息在emp 员工表中
		2. 工资等级 信息在 salarygrade 工资等级表中
		3. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/

SELECT emp.ename,
       emp.salary,
       t2.*
FROM emp,
     salarygrade t2
WHERE emp.salary >= t2.losalary
  AND emp.salary <= t2.hisalary

在这里插入图片描述

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
	分析：
		1. 员工编号，员工姓名，工资 信息在emp 员工表中
		2. 职务名称，职务描述 信息在 job 职务表中
		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

		4. 部门名称，部门位置 来自于 部门表 dept
		5. dept 和 emp 一对多关系 dept.id = emp.dept_id
		6. 工资等级 信息在 salarygrade 工资等级表中
		7. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.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 dept.id = emp.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 *
from dept;

select dept_id, count(*)
from emp
group by dept_id;


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

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

追光而遇
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
【MyBatis系列】Mybatis多表查询与动态SQL
未见花闻的博客
10-20 2780
本篇文章将介绍使用MyBatis进行多表查询以及MyBatis的动态SQL特性。
十、多表查询
天行健，君子以自强不息
06-05 1096
多表查询
MySQL多表关系及多表查询
HaiJaine的博客
03-17 1597
准备数据-- 删除员工表（若存在） drop table if exists emp;-- 删除部门表（若存在） drop table if exists dept;-- 创建dept表，并插入数据 create table dept(id int auto_increment comment 'ID' primary key , name varchar(50) not null comment '部门名称') comment '部门表';
mysql 语句
weixin_30756499的博客
08-15 146
单表查询（增删改） 1. 添加数据： * 语法： * insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n); * 注意： 1. 列名和值要一一对应。 2. 如果表名后，不定义列名，则默认给所有列添加值 insert into 表名 values(值1,值2,.....
SQL多表查询
weixin_45951205的博客
03-20 4947
概述：从多张表中进行数据查询例如：有以下两张表dept和emp：多表查询分类1、连接查询内连接：相当于查询A、B交集部分数据外连接：左外连接：查询左表所有数据，以及两张表交集部分数据（用的更多）右外连接：查询右表所有数据，以及两张表交集部分数据自连接：当前表与自身的连接查询，自连接必须使用表别名2、子查询。
MySQL的多表查询
qq_44111805的博客
04-28 4969
sql多表查询
热门推荐
Finneab的博客
05-07 3万+
多表查询，MySQL
MySQL多表查询操作
二哈喇子！
07-06 2864
MySQL数据库的多表查询操作
MySQL笔记-多表查询
2201_75533641的博客
05-13 5896
自连接,外连接,联合查询,嵌套查询,子查询,事务四大特性,并发事务问题,事务隔离级别
MySQL多表查询
m0_51779342的博客
03-14 2099
MySQL多表查询 多表查询就是从多张表查询数据 若不加限制条件，直接使用select语句进行多表查询，将会产生笛卡尔积 笛卡尔积就是把所有集合元素的所偶有组合都列举出来，即A的个数乘上B的个数 -- 删除表 DROP TABLE IF EXISTS emp; DROP TABLE IF EXISTS dept; -- 部门表 CREATE TABLE dept ( id INT PRIMARY KEY auto_increment, -- 部门编号,主键自增 dep_n
java数据库多表查询
SSSS__AS的博客
09-26 1905
但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。这个条件大多数情况下都是使用主外键关系去除。连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。
多表查询（整理）
weixin_43294936的博客
03-06 3856
一、多表关系 概述：项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间互相关联，所以各个表结构之间也存在着各种联系。 基本上有三种：一对多（多对一），多对多，一对一。 一对多（多对一） 多对多 一对一 辅助建表内容： -- 多表查询 -- create table student( id int auto_increment primary key comment '主键id', name varchar(10...
数据库的多表查询
KooCoolX的博客
06-29 2827
1.多表查询-数据准备 SQL语句 -- 创建db6数据库 CREATE DATABASE db6; -- 使用db6数据库 USE db6; -- 创建user表 CREATE TABLE USER( id INT PRIMARY KEY AUTO_INCREMENT, -- 用户id NAME VARCHAR(20), -- 用户姓名 age INT -- 用户年龄 ); -- 添加数据 INSERT INTO US
spring事务详细讲解-深入浅出
小电玩
09-08 436
Spring 事务是本身就是对数据库的事务的支持，没有数据库的事务 Spring 是本身无法实现事务的。Spring只提供了统一事务管理接口，具体的实现还是由各数据库自己实现。在使用 Spring 事务时，可以通过注解或编程方式将需要进行事务管理的方法和代码块标记为事务性操作，当这些操作被执行时，Spring 会负责开启时根据当前环境中设置的隔离级别，调整数据库隔离级别。
数据同步是如何实现的？为什么需要数据同步？
oOBubbleX的博客
09-05 901
实时数据同步，确保数据的一致性和实时更新性，将有利于企业各部门之间高效协作，从而支持企业的业务运作和决策。本文将介绍数据同步概念、数据同步的步骤、数据同步的方式，从而加深对数据同步的认识。
【提效工具】AI工作流的1-10个实际落地场景
最新发布
万物皆有灵
09-09 690
在这个快速发展的数字时代，人工智能（AI）正在以惊人的速度改变我们的工作方式。无论你是开发者、教育工作者还是内容创作者，AI工作流提示词助手都能为你提供无与伦比的支持。今天，我们将深入探讨，20个实际落地场景，帮助你更好地理解如何利用这些工具提升工作效率和创造力。💡。
【MYSQL】
ABC1234567890ABM的博客
09-06 935
单列索引情况：根据下图可以看到，查询条件中有phone和name，他们都有单列索引，但是执行explain后发现真正只走了phone的索引，因为phone索引中没有name值，故还需回表走一次聚簇索引。为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。表级锁，每次操作锁住整张表。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值