MySQL多表查询语法及案例

于 2024-08-21 23:26:41 发布
阅读量686 收藏 12
点赞数 11
文章标签： 数据分析 学习 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_74355754/article/details/141370174
版权

chap1 多表查询

1.1 多表关系介绍

  • 概述

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种:

一对多(多对一)

多对多

一对一

  • 一对多(多对一)

    案例: 部门 与 员工的关系
    关系:一个部门对应多个员工，一个员工对应一个部门
    实现:在多的一方建立外键，指向一的一方的主键

    在这里插入图片描述

  • 多对多

    案例：学生与课程的关系
    关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
    实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

    在这里插入图片描述

    示例：

    -- --------------------------------多表关系 演示--------------------------------------------------------------------
-- 多对多---------------------
create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
)comment '学生表';
insert into student values(null, '张嘉琪','2000100101'),(null,'谢分','2000100102'),(null,'隐飞豪','2000100103'),(null,'张一鸣','2000100104');

create  table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
)comment '课程表';
insert into course values(null,'java'),(null,'PHP'),(null,'MYSQL'),(null,'HADOOP');

create table student_course(
    id int auto_increment primary key comment '主键',
    studentid int not null comment '学生ID',
    courseid int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course(id),
    constraint fk_studentid foreign key (studentid) references student(id)
)comment '学生课程中间表';
insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

  • 一对一

    案例：用户与用户详情的关系
    关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
    实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

在这里插入图片描述

示例：

-- ----------------------一对一---------------------------------------
create table tb_user(
    id int auto_increment primary key  comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment'年龄',
    gender char(1) comment '1:男 2:女',
    phone char(11) comment '手机号'
)comment '用户基本信息表';

create table tb_user_edu(
    id int auto_increment primary key  comment '主键ID',
    degree varchar(20) comment '学历',
    major varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool varchar(50) comment '中学',
    university varchar(50) comment '大学',
    userid int unique comment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user(id)
)comment '用户教育基本信息表';

insert into tb_user (id, name, age, gender, phone)
values (null,'黄渤',45,'1','18800001111'),
       (null,'冰冰',35,'2','18800002222'),
       (null,'马图',29,'1','18800003333'),
       (null,'张帆',55,'1','18833330000');

insert into tb_user_edu (id,degree, major, primaryschool, middleschool, university, userid)
values (null,'本科','舞蹈','静安区第一小学','静安区第一中学','上海大学',1),
       (null,'硕士','计算机科学','求精中学','求精中学','重庆邮电大学',2),
       (null,'博士','通信工程','重庆市人和街小学','巴川中学','南方科技大学',3),
       (null,'本科','英语','峨眉第一小学','成都7中','北京邮电大学',4);

###1.2 多表关系查询

  • 概述：指从多张表中查询数据
    • 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛
      上）

消除笛卡尔乘积前：
在这里插入图片描述

消除笛卡尔乘积后：

在这里插入图片描述

示例：

-- 多表查询 错误示范-笛卡尔积
select * from  emp,dept;

-- 多表查询 正确示范
select * from emp,dept where emp.dept_id = dept.id;

1.3 多表查询的分类

  • 连接查询

    • 内连接：相当于查询A、B交集部分数据
    • 外连接：
      • 左外连接：查询左表所有数据，以及两张表交集部分数据
      • 右外连接：查询右表所有数据，以及两张表交集部分数据
    • 自连接：当前表与自身的连接查询，自连接必须使用表别名

  • 子查询

1.4 内连接查询

  • 语法：
    • 隐式内连接：
      SELECT 字段列表 FROM 表1，表2 WHERE 条件;
    • 显式内连接：
      SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件;

内连接查询A、B交集部分数据：
在这里插入图片描述

示例：

-- 内连接演示
-- 1. 查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）
-- 表结构： emp ， dept
-- 连接条件： emp.dept_id = dept.id

select emp.name,dept.name  from emp,dept where emp.dept_id = dept.id;

-- 2. 查询每一个员工的姓名，及关联的部门名称（显示内连接实现）
-- 表结构： emp ， dept
-- 连接条件： emp.dept_id = dept.id

select emp.name,dept.name from  emp inner join dept on emp.dept_id = dept.id;

1.5 外连接查询

  • 语法：
    • 左外连接:
      SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件…；
      相当于查询表1（左表）的所有数据包含表1和表2交集部分的数据
    • 右外连接:
      SELECT 字段列表 FROM 表1 RIGHT [OUTER] jOIN 表2 ON 条件…；
      相当于查询表2（右表）的所有数据包含表1和表2交集部分的数据

在这里插入图片描述

示例:

-- 外连接演示

-- 1．查说emp表的所有数据，和对应的部门信总(左外连接)
-- 表结构: emp, dept
-- 连接条件: emp . dept_id = dept.id

select e.*,d.name from   emp e left outer join dept d on e.dept_id = d.id;
select e.*,d.name from   emp e left       join dept d on e.dept_id = d.id;

-- 2.香询dept表的所有数据。和对应的员工信品(右外连接)

select d.*,e.name from   emp e right outer join dept d on e.dept_id = d.id;
select d.*,e.name from   emp e right       join dept d on e.dept_id = d.id;

5.6 自连接查询

  • 语法:

    SELECT 字段列表 FROM 表 A 别名 A JOIN 表 A 别名 B ON 条件…;

    自连接查询，可以是内连接查询，也可以是外连接查询。

示例:

自连接的关系有点像递归,如下图所示a表的managerid应该连接b表的id,这样就可以查询出领导从属关系了,自连接就是自己连接自己.

注意:自连接必须取别名

在这里插入图片描述

-- 自连接
-- 1.查询员工及式所厨领导的名字表结构:emp

 select a.name , b.name from emp a , emp b where a.managerid = b.id;
-- 2．查询所有员工 emp及其领导的名宁 emp ，如果员工没有领导，也斋要查诲出来表经构:emp a , emp b

select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

1.7 联合查询

  • 概念：对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
  • 语法：SELECT 字段列表 FROM 表A
    UNION [ALL]
    SELECT 字段列表 FROM 表B；

注意：

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

示例：

-- 联合查询 union all ， union
-- 1.将薪资低于10000的员工，和年龄大于50的员工全部查询出来

select * from   emp where salary<10000
union all
select * from emp where age>50;

1.8 子查询

  • 概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

  • 语法:SELECT FROM t1 WHERE column1 =SELECT column1 FROM t2 )

注意: 子查询外部的语句可以是 INSERT/ UPDATE/ DELETE/ SELECT 的任何一个。

根据子查询结果不同，分为：

标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）
行子查询子查询结果为一行)
表子查询（子查询结果为多行多列

根据子查询位置，分为：HERE之后、FROM之后、SELECT之后。

1.8.1 标量子查询
  • 概念:子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。
  • 常用的操作符：= <> > >= < <=

示例：

-- 标量子合询
-- 1.查询”研发部”的所有员工信息
-- a.查询'研发部'的部门id
select id from dept where name = '研发部';

-- b.根据销售部门id, 查询员工信息
select * from emp where dept_id=1;

select * from emp where dept_id=(select id from dept where name = '研发部');

-- 2. 查询在’韦一笑‘之后入职之后的员工信尽
-- a. 查询 '韦一笑'的入职日期
select emp.entrydate from emp where name='韦一笑';

-- b. 查询指定入职日期之后的入职员工信息
select * from emp where entrydate>'2002-02-05';

select * from emp where entrydate>(select emp.entrydate from emp where name='韦一笑');
1.8.2 列子查询
  • 概念：子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
  • 常用的操作符：IN、NOT IN、ANY、SOME、ALL

在这里插入图片描述

示例：

-- 列子查询
-- 1. 查询“研发部”和"总经办”的所有员工信总
-- a. 查询”研发部”和"总经办”的部门id
        select id from dept where name='研发部'or name='总经办';
-- b.根指部门ID,查部员工信恳
        select * from emp where dept_id in (1,5);
        select * from emp where dept_id in (select id from dept where name='研发部'or name='总经办');
-- 2.查面比研发部所有人工资都高的员工信息
-- a.查询研发部人员工资
    select id from dept where  name ='研发部';

    select emp.salary from emp where dept_id ='1';

    select emp.salary from emp where dept_id =(select id from dept where  name ='研发部');
-- b.比 研发部所有人工资都高的员工信息
    select * from emp where salary>all(select emp.salary from emp where dept_id =(select id from dept where  name ='研发部'));
-- 3.查询比研发部其中任意一人工资高的员工信息
    select * from emp where salary>any(select emp.salary from emp where dept_id =(select id from dept where  name ='研发部'));
1.8.3 行子查询
  • 概念：子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
  • 常用的操作符：=、<>、IN、NOT IN

示例：

-- 行子查询
-- 1.查询’与张无忌‘的薪资及直属领导相同的员工信息；
-- a.查询‘张无忌’的薪资及直属领导
    select emp.salary,emp.managerid from    emp where name='张无忌';

-- b.查询与‘张无忌’的薪资及直属领导信息相同的员工信息h
    select * from emp where (salary,managerid)=(12500,1);
    select * from emp where (salary,managerid)=(select emp.salary,emp.managerid from    emp where name='张无忌');
1.8.4 表子查询
  • 概念：子查询返回的结果是多行多列，这种子查询称为表子查询。
  • 常用的操作符：IN

示例：

-- 表子查询
-- 1.查询与‘鹿杖客‘，’宋远桥‘的职位与薪资相同的员工信息
-- a.查询‘鹿杖客‘，’宋远桥‘的职位与薪资
select job, salary from emp where name='鹿杖客' or name='宋远桥' ;

-- b.查询与‘鹿杖客‘，’宋远桥‘的职位与薪资相同的员工信息
select * from emp where (job,salary) in (select job, salary from emp where name='鹿杖客' or name='宋远桥' );

-- 2.查询入职日期是'2006-01-01'之后的员工信息，及其部门信息
-- a.入职日期是'2006-01-01'之后的员工信息
select * from emp where entrydate>'2006-01-01';

-- b.查询这部分员工，对应的部门信息
select e.* , d.* from (select * from emp where entrydate>'2006-01-01') e left join dept d on e.dept_id=d.id

1.9 多表查询案例

(课后作业）

根据需求，完成SQL语句的编写
1.查询员工的姓名、年龄、职位、部门信息。
2.查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
3.查询拥有员工的部门D、部门名称。
4.查询所有年龄大于40岁的员工，及其归属的部门名称：如果员工没有分配部门，也需要展示出来。
5.查询所有员工的工资等级。
6.查询"研发部"所有员工的信息及工资等级。
7.查询"研发部”员工的平均工资。
8.查询工资比"灭绝"高的员工信息。
9.查询比平均薪资高的员工信息。
10.查询低于本部门平均工资的员工信息。
11.查询所有的部门信息，并统计部门的员工人数。
12.查询所有学生的选课情况，展示出学生名称，学号，课程名称

数据分析小歪
关注
MySQL多表查询
m0_51779342的博客
03-14 2126
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
mysql 查询 表别名_MySQL-复杂查询及条件-起别名-多表查询-04
weixin_31955599的博客
01-18 1979
基本查询语句及方法测试数据创建如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk(或者参照我安装配置MySQL的博客，将所有字符编码设置为 utf8)创建数据库与表create database db1;use db1;create table emp(id int not null unique auto_increment,# 表内没有字段是...
SQL多表连接查询实例分析（详细图文）
09-10
本文主要列举两张和三张表来讲述多表连接查询,需要的朋友可以参考下
多表查询及其案例
LJL1999的博客
08-27 1134
多表查询及其案例
多表查询(含完整例子)
atu1111的博客
05-06 6432
多表查询 1.多表联合查询 1.1 语法 select 字段1,字段2... from 表1,表2... [where 条件]   不加等值条件直接进行查询,则会出现以下效果,这种结果我们称之为 笛卡尔乘积   笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积. select * from person,dept   上述语句查询结果： 1.2 等值连接 ...
mysql多表查询示例_mysql多表查询示例
weixin_42422648的博客
01-28 151
本篇文章就多表查询进行介绍，以下是数据表和其操作sql语句。推荐课程：MySQL教程。创建示例数据表：#学生表create table stu(sid int primary key,sname varchar(10) not null);#成绩表create table score (sid int, score int , cid int);#科目表create table subjects ...
多表查询案例
芋粥菜菜子的博客
04-23 184
多表查询案例 #1.创建student&score表 CREATE table student ( id INT(10) not NULL PRIMARY KEY UNIQUE, name VARCHAR(20) not NULL, sex VARCHAR(4), birth YEAR, department VARCHAR(20), address VARCHAR(50) ); CREATE TABLE score ( id INT(10) not NULL PRIMARY KEY AUTO_IN
mysql查询语句汇总语法及示例
06-13
本文将深入探讨MySQL中的数据查询语言（DQL），包括其基本语法、常见操作以及具体应用实例。 #### 1. 基本语法结构 一条典型的SQL查询语句由以下几个部分组成： - **SELECT**：指定要选择的数据列。 - **FROM**：...
MySQL多表查询详解下
09-08
本篇文章将详细讲解MySQL的多表查询，特别是1999年的SQL标准语法。 首先，多表查询的核心是连接（JOIN）操作。根据连接类型，我们可以将它们分为四类：内连接（INNER JOIN）、左外连接（LEFT [OUTER] JOIN）、右外...
MySQL多表查询(四).pdf
05-01
### MySQL多表查询详解 #### 一、多表关系与设计原则 在数据库设计中，为了反映业务逻辑的真实情况，通常需要定义不同表之间的关系。这些关系主要包括：一对多（或多对一）、多对多以及一对一。 - **一对多(多对...
多表查询实例
weixin_30894389的博客
01-20 121
查询练习 2.models.py 创建表关系 from django.db import models # Create your models here. # 1.老师表 class Teacher(models.Model): tid = models.AutoField(primary_key=True) tname = models....
多表查询实例演示
qq_43665434的博客
09-09 254
多表查询 union（联合查询） select name,age from people union (distinct) select id,name from score; inner join(联合查询) select name,score from student inner join result on student.id=result.stuId; 注：查询student表中的name与result表中的score，两表的公共字段为id,stuId。 l
mysql数据库多表查询示例_mysql数据库——多表查询
weixin_31921223的博客
01-28 282
本节重点：多表连接查询符合条件连接查询子查询准备工作：准备两张表，部门表(department)、员工表(employee)create table department(id int,name varchar(20));create table employee(id int primary key auto_increment,name varchar(20),sex enum('male',...
MySQL多表查询实例
学亮编程手记
02-22 630
查询班级名称，和班级总人数 SELECT c.cname,COUNT(*) FROM classes c,student s WHERE c.cid = s.cno GROUP BY c.cname; 查询学生的姓名和学生所选的总课程平均成绩。 select s.sname,avg(sc.score) from student s,stu_cour sc where s.sid = s
【MySQL】多表查询 —— 多表查询案例
最新发布
qq_39921135的博客
07-22 302
连接条件 : emp.salary between salgrade.losal and salgrade.hisal , emp.dept_id = dept.id。连接条件: student.id = student_course.studentid , course.id = student_course.courseid。连接条件 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal。
MySQL多表查询及案例
weixin_47884900的博客
09-26 328
多表查询 一、多表查询的常见分类 1.1内连接： 隐式内连接 显示内连接 1.1.1隐式内连接 语法： select * from A,B where A.主键 = B.主键; select * from A  a,B b where a.主键 =b.主键; 1.1.2显示内连接 语法: select * from A a inner join B b a.主键 =b.主键; ...
数据库多表查询（新手实用案例）
longge94685的博客
11-11 450
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。 ...
MySQL1999语法多表查询详解
"本文主要介绍了SQL1999标准下的MySQL多表查询语法，包括内连接、外连接（左外、右外）以及全外连接和交叉连接的使用方法，并通过具体的案例解析了各种连接类型在实际应用中的操作。" 在SQL1999语法中，多表查询是...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值