mysql数据库多表查询的语法_MySQL数据库篇之多表查询

最新推荐文章于 2024-07-13 21:05:50 发布
最新推荐文章于 2024-07-13 21:05:50 发布
阅读量1.1k 收藏 1
点赞数
文章标签： mysql数据库多表查询的语法
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42119866/article/details/113188392
版权
本文详细介绍了MySQL数据库的多表查询操作，包括交叉连接、内连接、外连接（左连接、右连接）及复合条件连接查询，并通过实例演示了如何使用子查询进行复杂的数据筛选。此外，还讲解了EXISTS子查询的关键应用。最后，通过解决查询每个部门最新入职员工的问题，加深了对多表查询的理解。
摘要由CSDN通过智能技术生成

主要内容：

一、多表连接查询

二、复合条件连接查询

三、子查询

1️⃣  多表连接查询

一、准备表

#建表

create table department(

id int,

name varchar(20)

);

create table emp(

id int primary key auto_increment,

name varchar(20),

sex enum('male','female') not null default 'male',

age int,

dep_id int

);

#插入数据

insert into department values

(200,'技术'),

(201,'人力资源'),

(202,'销售'),

(203,'运营');

insert into empl(name,sex,age,dep_id) values

('egon','male',18,200),

('alex','female',48,201),

('wupeiqi','male',38,201),

('yuanhao','female',28,202),

('liwenzhou','male',18,200),

('jingliyang','female',18,204)

;

#查看表结构和数据

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

mysql>desc department;+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| name | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+mysql>desc emp;+--------+-----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------+-----------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(20) | YES | | NULL | |

| sex | enum('male','female') | NO | | male | |

| age | int(11) | YES | | NULL | |

| dep_id | int(11) | YES | | NULL | |

+--------+-----------------------+------+-----+---------+----------------+mysql> select * fromdepartment;+------+--------------+

| id | name |

+------+--------------+

| 200 | 技术 |

| 201 | 人力资源 |

| 202 | 销售 |

| 203 | 运营 |

+------+--------------+mysql> select * fromemp;+----+------------+--------+------+--------+

| id | name | sex | age | dep_id |

+----+------------+--------+------+--------+

| 1 | egon | male | 18 | 200 |

| 2 | alex | female | 48 | 201 |

| 3 | wupeiqi | male | 38 | 201 |

| 4 | yuanhao | female | 28 | 202 |

| 5 | liwenzhou | male | 18 | 200 |

| 6 | jingliyang | female | 18 | 204 |

+----+------------+--------+------+--------+

View Code

二、多表连接查询

#重点：外链接语法

SELECT 字段列表

FROM 表1 INNER|LEFT|RIGHT JOIN 表2

ON 表1.字段= 表2.字段;

1、交叉连接：不适用任何匹配条件。生成笛卡尔积

mysql> select * from emp,department;

连接结果：

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

+----+------------+--------+------+--------+------+--------------+

| id | name | sex | age | dep_id | id | name |

+----+------------+--------+------+--------+------+--------------+

| 1 | egon | male | 18 | 200 | 200 | 技术 |

| 1 | egon | male | 18 | 200 | 201 | 人力资源 |

| 1 | egon | male | 18 | 200 | 202 | 销售 |

| 1 | egon | male | 18 | 200 | 203 | 运营 |

| 2 | alex | female | 48 | 201 | 200 | 技术 |

| 2 | alex | female | 48 | 201 | 201 | 人力资源 |

| 2 | alex | female | 48 | 201 | 202 | 销售 |

| 2 | alex | female | 48 | 201 | 203 | 运营 |

| 3 | wupeiqi | male | 38 | 201 | 200 | 技术 |

| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |

| 3 | wupeiqi | male | 38 | 201 | 202 | 销售 |

| 3 | wupeiqi | male | 38 | 201 | 203 | 运营 |

| 4 | yuanhao | female | 28 | 202 | 200 | 技术 |

| 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 |

| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |

| 4 | yuanhao | female | 28 | 202 | 203 | 运营 |

| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |

| 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 |

| 5 | liwenzhou | male | 18 | 200 | 202 | 销售 |

| 5 | liwenzhou | male | 18 | 200 | 203 | 运营 |

| 6 | jingliyang | female | 18 | 204 | 200 | 技术 |

| 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 |

| 6 | jingliyang | female | 18 | 204 | 202 | 销售 |

| 6 | jingliyang | female | 18 | 204 | 203 | 运营 |

+----+------------+--------+------+--------+------+--------------+

View Code

2、内连接：只连接匹配的行

#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果#department没有204这个部门，因而emp表中关于204这条员工信息没有匹配出来

mysql> select emp.id,emp.name,emp.age,emp.sex,department.name from emp inner join department on emp.dep_id=department.id;

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

+----+-----------+------+--------+--------------+

| id | name | age | sex | name |

+----+-----------+------+--------+--------------+

| 1 | egon | 18 | male | 技术 |

| 2 | alex | 48 | female | 人力资源 |

| 3 | wupeiqi | 38 | male | 人力资源 |

| 4 | yuanhao | 28 | female | 销售 |

| 5 | liwenzhou | 18 | male | 技术 |

+----+-----------+------+--------+--------------+

View Code

3、外链接之左连接：优先显示左表全部记录

#以左表为准，即找出所有员工信息，当然包括没有部门的员工#本质就是：在内连接的基础上增加左边有右边没有的结果

mysql> select emp.id,emp.name,department.name as depart_name from emp left join department on emp.dep_id=department.id;+----+------------+--------------+

| id | name | depart_name |

+----+------------+--------------+

| 1 | egon | 技术 |

| 5 | liwenzhou | 技术 |

| 2 | alex | 人力资源 |

| 3 | wupeiqi | 人力资源 |

| 4 | yuanhao | 销售 |

| 6 | jingliyang | NULL |

+----+------------+--------------+

4、外链接之右链接：优先显示右表全部记录

#以右表为准，即找出所有部门信息，包括没有员工的部门#本质就是：在内连接的基础上增加右边有左边没有的结果

mysql> select emp.id,emp.name,department.name as depart_name from emp right join department on emp.dep_id=department.id;+------+-----------+--------------+

| id | name | depart_name |

+------+-----------+--------------+

| 1 | egon | 技术 |

| 2 | alex | 人力资源 |

| 3 | wupeiqi | 人力资源 |

| 4 | yuanhao | 销售 |

| 5 | liwenzhou | 技术 |

| NULL | NULL | 运营 |

+------+-----------+--------------+

5、全外连接：显示左右两个表全部记录

全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果#注意：mysql不支持全外连接 full JOIN#强调：mysql可以使用此种方式间接实现全外连接

select * from employee left join department on employee.dep_id =department.id

union

select* from employee right join department on employee.dep_id =department.id

;

+------+------------+--------+------+--------+------+--------------+

| id | name | sex | age | dep_id | id | name |

+------+------------+--------+------+--------+------+--------------+

| 1 | egon | male | 18 | 200 | 200 | 技术 |

| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |

| 2 | alex | female | 48 | 201 | 201 | 人力资源 |

| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |

| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |

| 6 | jingliyang | female | 18 | 204 | NULL | NULL |

| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |

+------+------------+--------+------+--------+------+--------------+

注意：union与union all 的区别：union会去掉相同的记录

2️⃣  复合条件连接查询

#示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

select employee.name,department.name fromemployee inner join department

on employee.dep_id=department.id

where age> 25;

View Code

#示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

select employee.id,employee.name,employee.age,department.name fromemployee,department

where employee.dep_id=department.idand age > 25order by age asc

View Code

3️⃣  子查询

#1：子查询是将一个查询语句嵌套在另一个查询语句中。#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字#4：还可以包含比较运算符：= 、 !=、> 、

1、带IN关键字的子查询

#查询平均年龄在25岁以上的部门名

select id,name fromdepartment

where idin(select dep_idfrom employee group by dep_id having avg(age) > 25);#查看技术部员工姓名

select name fromemployee

where dep_idin(select idfrom department where name='技术');#查看不足1人的部门名

select name fromdepartment

where idin(select dep_idfrom employee group by dep_id having count(id) <=1);

2、带比较运算符的子查询

#比较运算符：=、!=、>、>=、#查询大于所有人平均年龄的员工名与年龄

mysql> select name,age from emp where age > (select avg(age) fromemp);+---------+------+

| name | age |

+---------+------+

| alex | 48 |

| wupeiqi | 38 |

+---------+------+rowsin set (0.00sec)#查询大于部门内平均年龄的员工名、年龄

select t1.name,t1.age fromemp t1

inner join

(select dep_id,avg(age) avg_agefromemp group by dep_id) t2

on t1.dep_id=t2.dep_id

where t1.age> t2.avg_age;

3、带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。

而是返回一个真假值。True或False

当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

#department表中存在dept_id=203，Ture

mysql> select * fromemployee->where exists-> (select id from department where id=200);+----+------------+--------+------+--------+

| id | name | sex | age | dep_id |

+----+------------+--------+------+--------+

| 1 | egon | male | 18 | 200 |

| 2 | alex | female | 48 | 201 |

| 3 | wupeiqi | male | 38 | 201 |

| 4 | yuanhao | female | 28 | 202 |

| 5 | liwenzhou | male | 18 | 200 |

| 6 | jingliyang | female | 18 | 204 |

+----+------------+--------+------+--------+

#department表中存在dept_id=205，False

mysql> select * fromemployee->where exists-> (select id from department where id=204);

Empty set (0.00 sec)

练习：查询每个部门最新入职的那位员工

company.employee

员工id id int

姓名 emp_name varchar

性别 sex enum

年龄 age int

入职日期 hire_date date

岗位 post varchar

职位描述 post_comment varchar

薪水 salary double

办公室 office int

部门编号 depart_id int

#创建表

create table employee(

id intnotnull unique auto_increment,

name varchar(20) notnull,

sex enum('male','female') not null default 'male', #大部分是男的

age int(3) unsigned not null default 28,

hire_date datenotnull,

post varchar(50),

post_comment varchar(100),

salary double(15,2),

office int,#一个部门一个屋子

depart_id int

);

#插入记录#三个部门：教学，销售，运营

insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values

('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部

('alex','male',78,'20150302','teacher',1000000.31,401,1),

('wupeiqi','male',81,'20130305','teacher',8300,401,1),

('yuanhao','male',73,'20140701','teacher',3500,401,1),

('liwenzhou','male',28,'20121101','teacher',2100,401,1),

('jingliyang','female',18,'20110211','teacher',9000,401,1),

('jinxin','male',18,'19000301','teacher',30000,401,1),

('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门

('丫丫','female',38,'20101101','sale',2000.35,402,2),

('丁丁','female',18,'20110312','sale',1000.37,402,2),

('星星','female',18,'20160513','sale',3000.29,402,2),

('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门

('程咬金','male',18,'19970312','operation',20000,403,3),

('程咬银','female',18,'20130311','operation',19000,403,3),

('程咬铜','male',18,'20150411','operation',18000,403,3),

('程咬铁','female',18,'20140512','operation',17000,403,3)

;

答案：

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

select * fromemployee as t1 inner join(

select post,max(hire_date) as last_datefromemployee

group by post

) as t2 on t1.post=t2.post

where t1.hire_date= t2.last_date;

View Code

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+-----------------------------------------+------------+

| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | post | last_date |

+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+-----------------------------------------+------------+

| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | 老男孩驻沙河办事处外交大使 | 2017-03-01 |

| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | teacher | 2015-03-02 |

| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | sale | 2017-01-27 |

| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | operation | 2016-03-11 |

+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+-----------------------------------------+------------+

4 rows in set (0.02 sec)

View Code

西西里上尉
关注
python多表联合查询_Python MySQL 多表查询
weixin_39653442的博客
12-18 2972
Python MySQL 多表查询两个或更多表查询两个或多个表基于关联字段联全查询， 使用JOIN语句。如下是"users"表和"products"表:users表{ id: 1, name: 'John', fav: 154},{ id: 2, name: 'Peter', fav: 154},{ id: 3, name: 'Amy', fav: 155},{ id: 4, name: 'Ha...
mysql 数据库所有的表结构_mysql 查看数据库表结构
weixin_29170089的博客
01-27 1458
Mysql技术知识点Mysql技术知识点，首先来讲一下Mysql语句精进安装数据库，然后导入tigerfive.sql。 sql见文档结尾部分MySQL示例数据库模式由以下表组成：customers: 存储客户的数据。products: 存储汽车的数据。productLines: 存储产品类别数据。orde...文章好程序员2019-12-031962浏览量什么是mysql数据库安全 简单又通俗的...
python怎样实现多表连接_Python Day45多表连接查询
weixin_39715834的博客
12-04 451
一、多表连接查询1 交叉连接：不适用任何匹配条件。生成笛卡尔积mysql> select * fromemployee,department;+----+------------+--------+------+--------+------+--------------+| id | name | sex | age | dep_id | id | name ...
MySql多表查询
最新发布
Very_a的博客
07-13 1060
多表查询：查询时从多张表中获取所需数据单表查询的SQL语句：select 字段列表 from 表名;那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;查询用户表和部门表中的数据：此时,我们看到查询结果中包含了大量的结果集，总共85条记录，而这其实就是员工表所有的记录(17行)与部门表所有记录(5行)的所有组合情况，这种现象称之为笛卡尔积。笛卡尔积：笛卡尔乘积是指在数学中，两个集合(A集合和B集合)的所有组合情况。
mysql的多表查询
andy的博客
12-04 346
多表查询 /* SELECT ...,....,.... FROM .... WHERE .... AND / OR / NOT.... ORDER BY .... (ASC/DESC),....,... LIMIT ...,... */ #1. 熟悉常见的几个表 DESC employees; DESC departments; DESC locations; #查询员工名为'Abel'的人在哪个城市工作？ SELECT * FROM employees WHERE last_name = '.
（五）MySQL语法笔记（多表查询）
m0_50990607的博客
03-12 938
1、关系概述 一对多（多对一） 案例“：部门与员工的关系 关系：一个部门对应多个员工，一个员工对应一个部门 实现：在多的一方建立外键，指向一的一方的主键 多对多 案例：学生与课程的关系 关系：一个学生可以选修多问课程，一门课程也可以供给多个学生选择 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键 一对一 案例：用户与用户详情的关系 关系：一对一关系，多用于单表差分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率 实现：在任意一方家兔外键，关联另一方的主键，并且设
MySQL数据库语法-多表查询练习一
weixin_34270865的博客
05-12 233
　　　　　　　　　　　　　　　　MySQL数据库语法-多表查询练习一 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰 版权声明：原创作品，谢绝转载！否则将追究法律责任。   　　本篇博客主要介绍的多表查询的外键约束，以及如何使用外链接和内连接查询数据信息。   一.数据表和测试数据准备 1 /* 2 @author :yinzhengjie 3 B...
MYSQL官方最新测试数据库 test_db.zip
01-28
这个数据库可能包含了各种示例表、数据和预设的查询，以帮助开发者和管理员熟悉MySQL的功能和性能。 描述中提到的"最新版"指的是这个测试数据库与MySQL的当前稳定版本保持同步，包含了最新的特性和改进。这可能包括...
report_test.zip_access mysql数据库查询
09-24
综上所述，"report_test.zip_access mysql数据库查询"这个主题涵盖了数据库查询的基础知识，包括Access和MySQL的查询语法、报表生成以及数据导出。无论是小型企业还是大型机构，掌握这些技能都能有效地管理和利用...
易语言-易语言查询mysql数据库表
06-25
本主题将深入探讨如何使用易语言查询MySQL数据库表，同时解决可能出现的乱码问题。 首先，我们要了解易语言中的两种主要的MySQL数据库连接方式：ODBC（Open Database Connectivity）和API直接连接。 1. ODBC方式...
mysql数据库数量太大优化_mysql数据库表太大查询慢优化的几种方法？
weixin_34061121的博客
01-27 2844
优化方案：主从同步+读写分离：这个表在有设备条件的情况下，读写分离，这样能减少很多压力，而且数据稳定性也能提高纵向分表：根据原则，每个表最多不要超过5个索引，纵向拆分字段，将部分字段拆到一个新表通常我们按以下原则进行垂直拆分:(先区分这个表中的冷热数据字段)把不常用的字段单独放在一张表;把text，blob等大字段拆分出来放在附表中;经常组合查询的列放在一张表中;缺点是：很多逻辑需要重写，带来很大...
多表查询代码
weixin_30483495的博客
01-14 213
import os if __name__ == '__main__': os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day82.settings") import django django.setup() from app01 import ...
mysql里多表关系及查询语法
weixin_56863206的博客
08-15 345
关于mysql里多表关系及其语法
MySQL多表查询，语法+示例，非常详细！！！
m0_62951223的博客
09-01 360
MySQL多表查询
MySQL数据库的多表查询
men_wen的博客
02-22 1764
MySQL数据库的多表查询1. 交叉连接 定义：交叉连接是不带WHERE 子句的多表查询，它返回被连接的两个表所有数据行的笛卡尔积。返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。 关键字：CROSS JOIN 格式：SELECT * from 表1 CROSS JOIN 表2 或者将两个表之间用逗号连接 mysql> select * fro
MySQL多表查询
阿年的博客
01-27 2552
MySQL多表查询 ## 交叉连接查询 语法：selectfrom 表1 cross join 表2; 或  selectfrom 表1,表2; 这样查询到的结果为两个表中所有数据行的乘积即笛卡尔积。 内连接查询： 注意：要查询的表，查询的条件，查询的字段。 隐式内连接：使用where条件消除无用数据 select * from emp,dept where emp.dept_id = dept.id; 查询emp表和dept表中dept_id和id相等的数据 常用写法： select
多表查询 以些简单应用的代码
陈志远的博客
07-24 226
先创建三个表： create table manger( id int not null auto_increment primary key, emp_id int, foreign key(emp_id) references emp(id), dep_id int, foreign key(dep_id) references dep(id) ); create table emp( id...
MySQL的多表查询
weixin_55734444的博客
07-05 4191
MySQL的多表查询
Mysql多表查询语句整理
定格空间
09-25 418
在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型： INNER JOIN：取交集，如果表中有至少一个匹配，则返回行 LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行（在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN） RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行 FULL JOIN：只要其中一个表中存在匹配，则返回行...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值