数据库之六 多表查询

最新推荐文章于 2024-04-29 12:56:08 发布
最新推荐文章于 2024-04-29 12:56:08 发布
阅读量937 收藏 19
点赞数 20
分类专栏： 数据库 文章标签： 数据库 sql mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48183870/article/details/135925280
版权

【零】准备数据

【1】创建表

（1）部门表
  • id是部门的编号
  • name是部门的名字
# 确保表不存在
drop table if exists department;
# 创建表
create table department(
id int auto_increment primary key,
name varchar(6)
)auto_increment = 501 ;
（2）创建员工表
  • id是员工的编号
  • name是员工的姓名
  • sex是员工性别默认男性
  • department_id是所在部门的编号
# 确保表不存在
drop table if exists employee;
# 创建表
create table employee(
id int auto_increment primary key,
name varchar(10),
sex enum('female', 'male') default 'male',
department_id int
);

【2】插入数据

（1）部门信息
  • 只插入3个部门信息
insert department (name) values("销售部"), ("技术部"), ("售后部");

+-----+-----------+
| id  | name      |
+-----+-----------+
| 501 | 销售部    |
| 502 | 技术部    |
| 503 | 售后部    |
+-----+-----------+
（2）员工信息

  • 共5名员工

  • 有一个员工的部门信息为505

  • 其他员工信息均正常，但是没有503

insert employee (name, sex, department_id)values
('John', 'male', 501),
('Jane', 'female', 502),
('Mike', 'male', 502),
('Tom', 'male', 505),
('Amy', 'female', 501);

+----+------+--------+---------------+
| id | name | sex    | department_id |
+----+------+--------+---------------+
|  1 | John | male   |           501 |
|  2 | Jane | female |           502 |
|  3 | Mike | male   |           502 |
|  4 | Tom  | male   |           505 |
|  5 | Amy  | female |           501 |
+----+------+--------+---------------+

【一】子查询

【1】说明

  • 子查询是指在SQL语句中嵌套了另一个完整的查询语句。
  • 子查询可以用于检索满足外部查询条件的数据，或者用于提供外部查询的条件。
  • 还可以说是将一个查询语句的结果当做另外一个查询语句的条件去用。

【2】示例

（1）查询Jane所在的部门名称
  • 先查看Jane的部门名称
select department_id
from employee
where name='Jane';

+---------------+
| department_id |
+---------------+
|           502 |
+---------------+
  • 在查询502的部门名字
select name 
from department
where id=502;

+-----------+
| name      |
+-----------+
| 技术部    |
+-----------+
  • 整合嵌套
select name
from department
where id in (
select department_id
from employee
where name='Jane');

select name
from department
where id = (
select department_id
from employee
where name='Jane');

+-----------+
| name      |
+-----------+
| 技术部    |
+-----------+
（2）查看技术部和售后部的员工信息
  • 先查看技术部和售后部的部门id
select id
from department
where name in ('技术部', '销售部');

+-----+
| id  |
+-----+
| 501 |
| 502 |
+-----+
  • 查看部门id是501和502的员工信息
select *
from employee
where department_id in (501, 502);

+----+------+--------+---------------+
| id | name | sex    | department_id |
+----+------+--------+---------------+
|  1 | John | male   |           501 |
|  2 | Jane | female |           502 |
|  3 | Mike | male   |           502 |
|  5 | Amy  | female |           501 |
+----+------+--------+---------------+
  • 整合嵌套
select *
from employee
where department_id in(
select id
from department
where name in ("技术部", "销售部"));

+----+------+--------+---------------+
| id | name | sex    | department_id |
+----+------+--------+---------------+
|  1 | John | male   |           501 |
|  2 | Jane | female |           502 |
|  3 | Mike | male   |           502 |
|  5 | Amy  | female |           501 |
+----+------+--------+---------------+

【二】联表查询

  • 联表查询是在SQL中一种常用的操作，用于同时从多个表中检索数据，通过表之间的关联条件将它们连接在一起。
  • 联表查询允许我们在一个查询中使用多个表的数据，以便获取更全面的信息。

【1】拼表 cross join

  • 语法
from 表1 corss join 表2 on 条件
from 表1, 表2 on 条件;
（0）笛卡尔积
  • 笛卡尔积是一个数学概念，它描述的是两个集合之间所有可能的元素组合的数量。
  • 具体来说，如果集合A有n个元素，集合B有m个元素，则它们的笛卡尔积的大小为nm。
（1）显示的交叉连接
  • cross join
select *
from employee
cross join department;
（2）隐式的交叉连接
  • 逗号
select *
from employee, department;
（3）结果
  • 部门表有3个
  • 员工表有5个
  • 所以最终经过笛卡尔积的运算（3*5）有15个组合，即15个结果
+----+------+--------+---------------+-----+-----------+
| id | name | sex    | department_id | id  | name      |
+----+------+--------+---------------+-----+-----------+
|  1 | John | male   |           501 | 501 | 销售部    |
|  1 | John | male   |           501 | 502 | 技术部    |
|  1 | John | male   |           501 | 503 | 售后部    |
|  2 | Jane | female |           502 | 501 | 销售部    |
|  2 | Jane | female |           502 | 502 | 技术部    |
|  2 | Jane | female |           502 | 503 | 售后部    |
|  3 | Mike | male   |           502 | 501 | 销售部    |
|  3 | Mike | male   |           502 | 502 | 技术部    |
|  3 | Mike | male   |           502 | 503 | 售后部    |
|  4 | Tom  | male   |           505 | 501 | 销售部    |
|  4 | Tom  | male   |           505 | 502 | 技术部    |
|  4 | Tom  | male   |           505 | 503 | 售后部    |
|  5 | Amy  | female |           501 | 501 | 销售部    |
|  5 | Amy  | female |           501 | 502 | 技术部    |
|  5 | Amy  | female |           501 | 503 | 售后部    |
+----+------+--------+---------------+-----+-----------+
（4）拼表进阶
  • 我们想得到的仅仅是员工表和部门对应上的结果
  • 并不想要全部的结果
  • 所以可以添加额外的条件
# 隐式交叉连接
select *
from employee, department
where employee.department_id = department.id;

# 显示交叉连接
select *
from employee
cross join department
where employee.department_id = department.id;

+----+------+--------+---------------+-----+-----------+
| id | name | sex    | department_id | id  | name      |
+----+------+--------+---------------+-----+-----------+
|  1 | John | male   |           501 | 501 | 销售部    |
|  2 | Jane | female |           502 | 502 | 技术部    |
|  3 | Mike | male   |           502 | 502 | 技术部    |
|  5 | Amy  | female |           501 | 501 | 销售部    |
+----+------+--------+---------------+-----+-----------+
  • 少了4号员工
    • 因为4号员工的部门id找不到对应的部门

【2】内连接 inner join

  • 语法
from 表1 inner join 表2 on 条件
（1）说明
  • 内连接返回两个表中满足连接条件的行
  • 只拼接两张表中共有的数据部分
（2）示例
select *
from employee
inner join department
on employee.department_id = department.id; 

+----+------+--------+---------------+-----+-----------+
| id | name | sex    | department_id | id  | name      |
+----+------+--------+---------------+-----+-----------+
|  1 | John | male   |           501 | 501 | 销售部    |
|  2 | Jane | female |           502 | 502 | 技术部    |
|  3 | Mike | male   |           502 | 502 | 技术部    |
|  5 | Amy  | female |           501 | 501 | 销售部    |
+----+------+--------+---------------+-----+-----------+
  • 结果和拼表进阶一样

【3】左连接 left join（常用）

  • 语法
from 表1 left join 表2 on 条件
（1）说明
  • 左连接返回左表中的所有行，以及与右表中满足连接条件的行。
  • 如果右表中没有匹配的行，则返回NULL。
（2）示例
select *
from employee
left join department
on employee.department_id = department.id;

+----+------+--------+---------------+------+-----------+
| id | name | sex    | department_id | id   | name      |
+----+------+--------+---------------+------+-----------+
|  1 | John | male   |           501 |  501 | 销售部    |
|  5 | Amy  | female |           501 |  501 | 销售部    |
|  2 | Jane | female |           502 |  502 | 技术部    |
|  3 | Mike | male   |           502 |  502 | 技术部    |
|  4 | Tom  | male   |           505 | NULL | NULL      |
+----+------+--------+---------------+------+-----------+
  • 4号员工的部门信息不存在
  • 所以用NULL表示

【4】右连接 right join

  • 语法
from 表1 right join 表2 on 条件
（1）说明
  • 右连接返回右表中的所有行，以及与左表中满足连接条件的行。
  • 如果左表中没有匹配的行，则返回NULL。
（2）示例
select *
from employee
right join department
on employee.department_id = department.id;

+------+------+--------+---------------+-----+-----------+
| id   | name | sex    | department_id | id  | name      |
+------+------+--------+---------------+-----+-----------+
|    1 | John | male   |           501 | 501 | 销售部    |
|    2 | Jane | female |           502 | 502 | 技术部    |
|    3 | Mike | male   |           502 | 502 | 技术部    |
|    5 | Amy  | female |           501 | 501 | 销售部    |
| NULL | NULL | NULL   |          NULL | 503 | 售后部    |
+------+------+--------+---------------+-----+-----------+
  • 售后部没有员工存在
  • 所以用NULL表示

【5】全连接 union

  • 语法
select *
from 表1
left join 表2
on 条件
union all
select *
from 表1
right join 表2
on 条件
（1）说明
  • 全连接返回左表和右表中的所有行。
  • 如果没有匹配的行，则用NULL填充缺失的部分。
  • 注意：UNION 会自动去重，如果你想保留所有行，包括重复的行，可以使用 UNION ALL。
（2）示例
select * 
from employee
left join department
on employee.department_id = department.id 
union
select *
from employee
right join department
on employee.department_id = department.id;

+------+------+--------+---------------+------+-----------+
| id   | name | sex    | department_id | id   | name      |
+------+------+--------+---------------+------+-----------+
|    1 | John | male   |           501 |  501 | 销售部    |
|    5 | Amy  | female |           501 |  501 | 销售部    |
|    2 | Jane | female |           502 |  502 | 技术部    |
|    3 | Mike | male   |           502 |  502 | 技术部    |
|    4 | Tom  | male   |           505 | NULL | NULL      |
| NULL | NULL | NULL   |          NULL |  503 | 售后部    |
+------+------+--------+---------------+------+-----------+
  • 售后部没有员工存在，所以用NULL表示
  • 4号员工的部门id存在对应部门，所以用NULL表示
在飞行-米龙
关注
  • 20
    点赞
  • 踩
  • 19
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
数据库表的设计
m0_65551781的博客
07-20 2021
sql的简单语句的书写
Mysql练习——建立一张员工信息表employees，字段包括员工姓名、员工号、所属部门、职位、奖金（可为空）、每月薪资。
浪漫的诗人博客
05-02 1万+
●GROUP BY子句:对查询结果按指定列的值分组，该属性列值相等的元组为一个组。忽略所有列，1表示一个固定值，也可以用count(2)、count(3)代替(不会忽略列值为NULL的记录)查询缺少成绩的学生的学号和相应的课程号。只包括列名指定列，返回指定列的不同值的记录数，在统计结果的时候，在统计结果的时候，查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。返回表中的记录数(包括所有列)，相当于统计表的行数(返回列名指定列的记录数，在统计结果的时候，
MYSQL多表查询面试题三
sinat_41902940的博客
03-28 4261
1.显示所有员工的姓名，部门号和部门名称 SELECT e.last_name,e.department_id,dep.department_name FROM employees e LEFT JOIN departments dep ON e.department_id =dep.department_id; 2.查询90号部门员工的job_id和90号部门的location_id DESC locations; #查看表的组成 DESC departments; SELECT e.job_id,
MySQL-----多表查询（二）
最新发布
2302_79862386的博客
04-29 2580
MySQL-----多表查询（二）详解~~~
MySQL数据库（二）
GiGi_Princess的博客
10-19 822
目录 1、去除查询结果重复记录 distinct 2、连接查询 2.1 什么是连接查询？ 2.2 连接查询的分类？ 2.3 当两张表进行连接查询时，没有任何条件的限制会发生什么现象？ 2.5 内连接之等值连接。 2.6 内连接之非等值连接 2.7 内连接之自连接 2.8 外连接 2.9 三张表，四张表怎么连接？ 3、子查询 3.1 什么是子查询 3.2 子查询都可以出现在哪里呢 3.3 where子句中的子查询 3.4 from子句中的子查询 3.5 select后面...
MySQl入门基础操作
weixin_73943382的博客
08-09 1160
指令： select *from mydb.worker;
数据库的简单查询.doc
03-25
数据库简单查询 数据库简单查询是数据库管理系统中的一个基本...本实验报告对数据库简单查询的概念和方法进行了详细的介绍，并对查询命令窗口的使用方法、单表查询的 SELECT 语句、聚合函数的使用进行了详细的解释。
java编写创建数据库和表的程序
08-29
该程序展示了 Java 中创建数据库和表的基本操作，包括连接数据库、创建数据库、创建表、插入数据、查询数据和关闭数据库连接等。通过该程序，我们可以了解 Java 中的数据库操作，并学习如何使用 JDBC 驱动程序来访问...
海大数据库原理综合实验六
06-23
实验六 数据库原理综合实验 1 实验目的 （1） 运用所学的数据库设计技术，针对一个具体的应用系统，完成系统数据库的概念模型、逻辑模型和物理模型的设计。以巩固理论课程上所学的知识，更好地掌握数据库设计技术...
数据库系统概念 第六版 习题答案
06-28
"数据库CH (9).pdf"可能涵盖查询语言SQL，讲解如何创建表、插入数据、更新数据、删除数据以及执行复杂的查询操作。 通过深入研究这些章节的习题答案，读者不仅能够掌握基本的数据库操作，还能理解高级主题，如视图...
数据库设计之递归树查询
02-20
在数据库设计中，递归查询是一种处理层级关系数据的关键技术，尤其在存在多级关联的数据结构时显得尤为重要。本文将深入探讨如何通过递归查询来解决这类问题，并着重讲解使用`WITH`语句来实现递归查询的方法，适用...
员工表,部门表和工资表
06-21
包含员工表,部门表,工资表的建表语句和数据,语句适用于mysql数据库
员工部门信息表-myemployees
qq_44349459的博客
12-28 862
目的 　　描述的是员工、部门、工资等级、工种、部门地区的关系表 数据视图 数据源 /* Navicat MySQL Data Transfer Source Server : 135数据库 Source Server Version : 50650 Source Host : 192.168.209.135:3306 Source Da...
mySQL基础之连接查询
weixin_45342806的博客
11-14 3145
进阶6：连接查询 含义：又称多表查询,当查询的字段来自于多个表时，就会用到连接查询 笛卡尔乘积现象：表1 有m行、表2 有n行，结果 = m*n 行 发生原因：当查询多个表时，没有有效的连接条件 如何避免：添加有效的连接条件 分类： 按年代分类： 1. sql 92标准：仅仅支持内链接 等值连接 非等值连接 自连接 不稳定 也支持一部分外连接(用于oracle、sqlserver\mysql不支持)
MySQL练习题
weixin_44446626的博客
05-18 3765
MySQL的课后练习题一、基本的SELECT语句1. 查询员工12个月工资总和，并起别名为ANNUAL SALARY2. 查询employees表中去除重复的job_Id以后的数据3. 查询工资大于12000的员工姓名和工资4. 查询员工号为176的员工的姓名和部门号5. 显示表departments的结构并查询全部数据二、运算符1. 查询工资不在5000到12000的员工姓名和工资2. 查询在20或50号部门工作的员工姓名和部门号3. 查询公司中没有管理者的姓名员工及job_Id4. 查询公司中有奖金的员
oracle员工表和部门表基本操作
热门推荐
coco_astrids的博客
05-23 1万+
emp 员工表（empno 员工号/ename 员工姓名/job 工作/mgr 上级编号/hiredate 受雇日期/sal 薪金/comm 佣金/deptno 部门编号） dept 部门表（deptno 部门编号/dname 部门名称/loc 地点） 工资 = 薪金 + 佣金 题目： 　　1、列出至少有一个员工的所有部门。 　　2、列出薪金比“SMITH”多的所有员工。（大于最大薪
mysql 部门表_数据库学习笔记之MySQL(10)
weixin_32452447的博客
01-29 399
目录01. MySQL中的多表联查_笛卡尔积02. MySQL中的多表联查_内连接03. MySQL中的多表联查_外连接正文01. MySQL中的多表联查_笛卡尔积先创建两个表//学生表CREATE TABLE emp(id INT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号',age T...
Oracle数据库练习
weixin_43303023的博客
10-12 379
多表查询练习 1.显示所有员工的姓名，部门号和部门名称。 select last_name,e.department_id,department_name from employees e,departments d where e.department_id = d.department_id(+); 2.查询90号部门员工的job_id和90号部门的location_id select distinct job_id,location_id from employees e left outer j
SQL语法之高级查询
cicier的专栏
03-21 1017
SELECT {table_alias.column}[,...n]FROM table_name [AS] table_alias [,...n] 内连接SELECT select_list FROM table1,table2 WHERE table1.column1=table.column2或SELECT select_list FROM table1 [INNER] JOIN table
实验六数据库单表查询
11-16
以下是实验六数据库单表查询的介绍： 单表查询是指只查询一个表的数据，不涉及到多个表之间的关联查询。单表查询可以分为以下几种类型： 1.单表投影查询：只查询表中的某些列，不查询所有列。 ```sql SELECT ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值