多表联查

最新推荐文章于 2023-02-04 15:20:49 发布
最新推荐文章于 2023-02-04 15:20:49 发布
阅读量967 收藏 4
点赞数 1
分类专栏： mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/github_phpnobug/article/details/75332010
版权

多表联查

使用多表联查的场景，有些时候数据在不同的表中，这个时候我们就需要用到mysql中的多表联查。

多表联查概念

将两个或两个以上的表按某个条件连接起来，从而选取需要的数据。多表联查是同时查询两个或两个以上的表时使用的。

多表联查分类

  1. 内连接查询

    内连接查询使用关键字join或cross join 或 inner join,
    然后通过on连接表与表之间的条件

    注意： 内连接查询只能查询出两个表符合条件的数据

    语法:

    SELECT 字段,字段1,..
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

  2. 外连接查询

    2.1 左外连接

    左外连接使用关键字left join,
然后通过on连接表与表之间的条件

注意：left join 会查询出left join左边的表所有的数据，即使右表没有匹配

语法：

    SELECT 字段,字段1,...
    FROM table_name1
    LEFT JOIN table_name2 
    ON table_name1.column_name=table_name2.column_name

    2.2 右外连接

    左外连接使用关键字right join,
然后通过on连接表与表之间的条件

注意：  即使左表中没有匹配，也从右表返回所有的行

语法:

    SELECT 字段,字段1,....
    FROM table_name1
    RIGHT JOIN table_name2 
    ON table_name1.column_name=table_name2.column_name

多表联查例子

//用户表

create table user(
    id int primary key auto_increment,

    name varchar(150) unique not null,

    pass varchar(255) not null 
)engine=innodb default charset=utf8;

//用户详情表

create table user_detail(
    id int primary key auto_increment,
    uid int not null comment '关联用户表的id',
    city varchar(100) not null,
    sex tinyint not null comment '1男 0女'
)engine=innodb default charset=utf8;

//订单表

create table orders(
    id int primary key auto_increment,
    uid int not null comment '关联用户表的id',
    orderno int not null comment '订单号,关联订单详情表id',
    create_time timestamp not null comment '订单下单时间'
)engine=innodb default charset=utf8;

//插入测试数据

insert into user(name,pass) values('jack',md5('123456'));
insert into user(name,pass) values('mary',md5('123456'));
insert into user(name,pass) values('rose',md5('123456'));
insert into user(name,pass) values('test',md5('123456'));
insert into user(name,pass) values('mark',md5('123456'));

insert into user_detail(uid,city,sex) values(1,'广州',1);
insert into user_detail(uid,city,sex) values(2,'上海',0);
insert into user_detail(uid,city,sex) values(3,'北京',0);
insert into user_detail(uid,city,sex) values(4,'北京',1);

insert into orders(uid,orderno,create_time) values(2,3,now());
insert into orders(uid,orderno,create_time) values(4,5,now());

  1. 查询出用户姓名，城市，性别

    //实现方式一,这种方式等价于内连接

    select user.id,name,city,sex from user,user_detail where user.id = user_detail.uid;

    //实现方式二

    select name,city,sex from user inner join user_detail on user.id=user_detail.uid;

    //实现方式三

    select name,city,sex from user  join user_detail on user.id=user_detail.uid;

select name,city,sex from user cross  join user_detail on user.id=user_detail.uid;

    !!这几种方式都是一样的!!

  2. 查询出用户的姓名，城市。并且性别为1的用户

    select name,city,sex from user inner join user_detail on user.id=user_detail.uid where sex=1;

  3. 按照城市对用户分组，得到每个城市的所有用户名字

    select group_concat(name) from user inner join user_detail on user.id=user_detail.uid group by city;

  4. 按照城市对用户分组，得到每个城市的所有用户名字，并且只要组人数超过1个人的

    select group_concat(name) from user inner join user_detail on user.id=user_detail.uid group by city having count(*) > 1;

  5. 查询出订单表中的用户的姓名，性别，城市，用户id,以及用户的下单时间（三表联查）

已有什么，求什么？

已有uid,求姓名，性别，用户下单时间

user表，user_detail,orders

    select name,sex,city,u.id,create_time from orders as o inner join user as u on o.uid=u.id inner join user_detail d on u.id = d.uid;

  • 外连接的实例

    1. 列出所有用户的所有信息(包括name,pass,sex,city)

    //使用左连接实现

    select u.id,name,pass,sex,city from user as u left join user_detail as d on u.id = d.uid;

    //使用右连接实现

    select u.id,name,pass,sex,city from user_detail as d right join user as u on u.id = d.uid;

MySQL子查询

  • 子查询定义

子查询就是把一个查询嵌套在另一个查询中。

子查询可以包含普通select可以包括任何子句，比如：distinct,group by, order by,limit,join等

下图为子查询说明图：

  • 注意

    1. 子查询先执行里面的SQL语句，再执行外面SQL语句。
    2. 子查询的效率比较低，一般建议使用join替换子查询
    3. 子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句再临时表中查询记录。查询完毕后，MySQL需要撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

  • 使用子查询原则

    1. 一个子查询必须放在圆括号中
    2. 将子查询放在比较条件的右边，这样可以增加SQL可读性

子查询分类

  1. where 型子查询(重点掌握)

    where型子查询把内层查询结果当作外层查询的比较条件

    例如：

    SELECT * FROM user WHERE id in (SELECT uid FROM user_detail );

  2. exists/not exists型子查询

    EXISTS关键字表示存在。使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值，如果内层查询语句查询到满足条件的记录，只要子查询中至少返回一个值，则EXISTS语句的值就为True。就返回true，否则返回false。当返回的值为true时，外层查询语句将进行查询，否则不进行查询

  3. 使用IN/NOT IN的子查询

    SELECT * FROM user WHERE id not in (SELECT uid FROM user_detail );

  4. 使用比较运算符的子查询 ( = > < >= <= != )

    SELECT * FROM user WHERE id  = (SELECT uid FROM user_detail limit 1 );

子查询实例

//球员信息表
CREATE TABLE players(
playerno int NOT NULL comment ‘球员编号’,
name CHAR(15) NOT NULL,
initials CHAR(3) NOT NULL comment ‘名字首字母’,
birth_date DATE,
sex CHAR(1) NOT NULL,
joined SMALLINT NOT NULL comment ‘参入球队年份’,
street VARCHAR(30) NOT NULL comment ‘所在街道’,
houseno CHAR(4) comment ‘房子编号’,
postcode CHAR(6) comment ‘邮政编码’,
town VARCHAR(30) NOT NULL comment ‘小镇名’,
phoneno CHAR(13) comment ‘电话号码’,
leagueno CHAR(4) comment ‘在球队中的号码’,
PRIMARY KEY (playerno)
)engine=innodb default charset=utf8;

//有过罚款记录的球员信息表

CREATE  TABLE penalties(
    paymentno      int   NOT NULL,
    playerno      int    NOT NULL comment '球员编号',
    payment_date   DATE  NOT NULL comment '罚款时间',
    amount         DECIMAL(7,2) NOT NULL comment '罚款金额',
    PRIMARY KEY    (paymentno)
)engine=innodb default charset=utf8;

//插入测试数据

INSERT INTO players VALUES (2, 'Everett', 'R', '1948-09-01', 'M', 1975, 'Stoney Road','43', '3575NH', 'Stratford', '070-237893', '2411');
INSERT INTO players VALUES (6, 'Parmenter', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane','80', '1234KK', 'Stratford', '070-476537', '8467');
INSERT INTO players VALUES (7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'Edgecombe Way','39', '9758VB', 'Stratford', '070-347689', NULL);
INSERT INTO players VALUES (8, 'Newcastle', 'B', '1962-07-08', 'F', 1980, 'Station Road','4', '6584WO', 'Inglewood', '070-458458', '2983');
INSERT INTO players VALUES (27, 'Collins', 'DD', '1964-12-28', 'F', 1983, 'Long Drive','804', '8457DK', 'Eltham', '079-234857', '2513');
INSERT INTO players VALUES (28, 'Collins', 'C', '1963-06-22', 'F', 1983, 'Old Main Road','10', '1294QK', 'Midhurst', '010-659599', NULL);
INSERT INTO players VALUES (39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square','78', '9629CD', 'Stratford', '070-393435', NULL);
INSERT INTO players VALUES (44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street','23', '4444LJ', 'Inglewood', '070-368753', '1124');
INSERT INTO players VALUES (57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'Edgecombe Way','16', '4377CB', 'Stratford', '070-473458', '6409');
INSERT INTO players VALUES (83, 'Hope', 'PK', '1956-11-11', 'M', 1982, 'Magdalene Road','16A', '1812UP', 'Stratford', '070-353548', '1608');
INSERT INTO players VALUES (95, 'Miller', 'P', '1963-05-14', 'M', 1972, 'High Street','33A', '5746OP', 'Douglas', '070-867564', NULL);
INSERT INTO players VALUES (100, 'Parmenter', 'P', '1963-02-28', 'M', 1979, 'Haseltine Lane','80', '6494SG', 'Stratford', '070-494593', '6524');
INSERT INTO players VALUES (104, 'Moorman', 'D', '1970-05-10', 'F', 1984, 'Stout Street','65', '9437AO', 'Eltham', '079-987571', '7060');
INSERT INTO players VALUES (112, 'Bailey', 'IP', '1963-10-01', 'F', 1984, 'Vixen Road','8', '6392LK', 'Plymouth', '010-548745', '1319');

INSERT INTO penalties VALUES (1,  6, '1980-12-08',100);
INSERT INTO penalties VALUES (2, 44, '1981-05-05', 75);
INSERT INTO penalties VALUES (3, 27, '1983-09-10',100);
INSERT INTO penalties VALUES (4,104, '1984-12-08', 50);
INSERT INTO penalties VALUES (5, 44, '1980-12-08', 25);
INSERT INTO penalties VALUES (6,  8, '1980-12-08', 25);
INSERT INTO penalties VALUES (7, 44, '1982-12-30', 30);
INSERT INTO penalties VALUES (8, 27, '1984-11-12', 75);

实例

  1. 获取和100号球员性别相同的球员号码(where型子查询)

    select sex from players where playerno = 100;


select playerno from  players where sex = 'M'


select playerno from  players where sex =  (select sex from players where playerno = 100)

  2. 获取和100号球员性别相同并且居住在同一城市的球员号码。(where型子查询)

    select sex from players where playerno = 100;

    select town from players where playerno = 100;

    select playerno from players where sex = ‘M’ and city = ‘Stratford’;

    select playerno from players where sex = (select sex from players where playerno = 100) and town = (select town from players where playerno = 100);

    select playerno   from players   where (sex, town) = (  
    select sex, town from players  where playerno = 100
);

  3. 获取到所有罚过款的球员信息(重点)

    select playerno from penalties;//1,2,3,4

    select * from players where playerno in (1,2,3,4);

    select * from players where playerno in ( select playerno from penalties);

  4. 获取最老球员的号码，名字及生日。（即出生日期数值小于或等于所有其它球员的球员）

    select playerno, name, birth_date  from players  where birth_date <= all   
(select birth_date  from players);

  5. 获取那些至少支付了一次罚款的球员的名字和首字母。(exists型子查询) 

    select name, initials  from players where exists   
(select * from penalties  where playerno = players.playerno);

  6. 当存在Wise球员就查询出所有球员数据

    select name, initials  from players where exists  (select name from players where name ='Wise');

  7. 查询出被罚款超过50的球员名字与电话

    select name, phoneno from players where playerno in ( select playerno from penalties where amount > 50);
github_phpnobug
关注
  • 1
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
多表联合查询
09-16
多表联合查询的Hibernate应用
mysql 联合查询
m0_62377363的博客
06-30 1943
什么是多表联合查询 前面所讲的查询语句都是针对一个表的，但是在关系型数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询。多表查询就是同时查询两个或两个以上的表。 在 MySQL 中，多表查询主要有交叉连接、内连接、外连接、分组查询与子查询等5种。交叉连接(CROSS JOIN) 3.2.1 笛卡尔积 集合 A×B 和 B×A 的结果集分别表示为： 以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。并且，从以上结果我们可以看出3.2.2 交叉连接 交叉连接的语法格式如下： 或 语法说明
Mybatis-Plus 多表联查分页的实现代码
08-27
本篇文章主要介绍了Mybatis-Plus 多表联查分页的实现代码，小编觉得挺不错的，现在分享给大家，也给大家做个参考。一起跟随小编过来看看吧
微信小程序云开发多表联查
05-29
微信小程序云开发多表联查
多表联查分页显示
04-16
简单的servlet多变联查
SpringBoot多表联查(测试可用)
08-29
主要介绍了SpringBoot多表联查(测试可用)的相关资料,需要的朋友可以参考下
SQL多表联查学习用SQL语句
最新发布
01-17
SQL多表联查学习用SQL语句
Mysql进阶—二进制安装、联合查询、破解与备份
m0_67758799的博客
06-28 867
1. 二进制格式安装mysql 环境说明 安装流程 相关报错及解决 2. mysql配置文件 3. mysql数据库密码破解步骤 4. 多表联合查询 5. mysql数据库备份与恢复
SQL-多表联查
Ariel_欢
04-27 2765
多表联查：由内向外一层层的查询 先把其中一个表想要的数据查出来，并把查询结果命名为一张新表，然后外层查询这张新表与别的表（内连接等） 例子： 表1：tb_score_record 先将这张表中每个人score求和： 使用group by 按照stu_id分组，就Sum（score）： SELECT stu_id, SUM( score ) AS score FROM `tb_score_record` GROUP BY stu_id 结果：求出每个人的总分，但是这张表中...
数据库的多表联查
weixin_43469486的博客
09-26 914
文章转自http://www.zsythink.net/archives/1105 交叉连接：cross join 既然是多表查询，那么我们先来看看两张非常简单的表，我们就以这两张表为例，进行演示。 上图中，我们通过两条语句分别查询了表1与表2的内容，t1表中有3条数据，t2表中有2条数据，那么同时查两张表，会查询出什么内容呢？我们来实验一下。 上图中，我们只是单纯的将两张表使用同一条select语句查询了出来，并没有添加任何额外的过滤条件，仔细观察查询出的数据，可以发现，当使用上图中.
多表关联查询
m0_73852362的博客
02-04 779
select *from 表1,表二 where 表1.字段1=表2.字段2 （声明这两张表中的字段存在外键关联关系）若还有其他外键关联关系用 and…select *from 表1 u inner join 表2 d on(u.字段1=d.字段2) 若还有条件则用where。| 06 | 吴兰 | 1992-01-01 00:00:00 | 女 || 07 | 郑竹 | 1989-01-01 00:00:00 | 女 |
mysql 多表联查
10-24
MySQL多表联查是MySQL多表联查是指在查询中同时涉及到多个表，通过连接操作将这些表中的数据进行MySQL多表联查是指在查询中同时涉及到多个表，通过连接操作将这些表中的数据进行关联，从而得到需要的结果。常见的多...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值