MYSQL学习3
SQL最强大的功能之一就是能在数据检索查询的执行中联结(join)表。联结是利用SQL的select能执行的最重要的操作，很好地理解联结及其语法是学习SQL的一个极为重要的组成部分。

1 .联结表
① 两表关联查询

如下有两张表，用户表与角色表，用户表的外键为角色表的主键

在前面的学习中我们使用了嵌套查询，但是我们不建议使用嵌套查询，因为它效率低，推荐多表关联查询

例如，我们要查询角色为管理员的用户,并且按照user表的id排序

select user.id as user_id,username,role.name as role_name
from user,role
where role.id=user.rid and role.id=1
order by user.id;
1
2
3
4

②笛卡尔积

由于没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘第二个表中的行数。

select user.id,username,role.name as role_name
from user,role
order by username;
1
2
3

③内连接

查询两个表的数据

select user.id as user_id,username,role.name as role_name
from user inner join role #内连接两个表
on user.rid = role.id; #on关键字做条件
1
2
3

性能考虑：MySQL在运行时关联指定的每个表以处理联结，这种处理可能时非常消耗资源，因仔细考虑，不要联结不必要的表，联结的表越多，性能下降越厉害。

2 .高级联结
①使用表别名

user表与role表关联查询

select username,r.name as role_name
from user as u,role as r
where u.rid=r.id;
1
2
3

②自联结

select u1.id,u1.username
from user as u1,user as u2
where u1.id=u2.id and u1.id=3;
1
2
3

用自联结而不用子查询：自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句，虽然最终的结果一样，但有时处理联结远比处理子查询快的多。

③ 外部联结

分为左外联结和右外联结

左外联结

select user.id,username,role.name
from user left outer join role
on user.rid=role.id;
1
2
3

④使用带聚集函数的联结

查询管理员用户与普通用户的数量

select role.id,role.name,count(user.id) as role_num
from user inner join role
on user.rid=role.id
group by role.id;
1
2
3
4

3 .组合查询
①使用union

select id,username from user where rid=2
union #等同与条件中的or
select id,username from user where id<6;
1
2
3

②组合查询进行排序

select id,username from user where rid=2
union
select id,username from user where id<6
order by username;
1
2
3
4

组合不同的表，为表述比较简单，我这里使用的是相同的表，但是使用union也可以用于不同的表。

使用union,可以把多条查询的结果作为一条组合查询返回，不管它们的结果中包含还是不包含重复，使用union可极大地简化复杂的where子句，简化从多个表检索数据的工作。
————————————————
版权声明：本文为CSDN博主「kenewstar」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_45840947/article/details/107127177
https://weavi.com/22698411
https://weavi.com/22698411?q8=20200706
https://weavi.com/22698411?q8
https://weavi.com/22698412
https://weavi.com/22698412?y0=20200706
https://weavi.com/22698412?y0
https://weavi.com/22698413
https://weavi.com/22698413?q5=20200706
https://weavi.com/22698413?q5
https://weavi.com/22698422
https://weavi.com/22698422?m5=20200706
https://weavi.com/22698422?m5
https://weavi.com/22698424
https://weavi.com/22698424?q4=20200706
https://weavi.com/22698424?q4
https://weavi.com/22698423
https://weavi.com/22698423?q8=20200706
https://weavi.com/22698423?q8
https://weavi.com/22698421
https://weavi.com/22698421?k3=20200706
https://weavi.com/22698421?k3
https://weavi.com/22698425
https://weavi.com/22698425?s6=20200706
https://weavi.com/22698425?s6
https://weavi.com/22698431
https://weavi.com/22698431?b6=20200706
https://weavi.com/22698431?b6
https://weavi.com/22698434
https://weavi.com/22698434?o7=20200706
https://weavi.com/22698434?o7
https://weavi.com/22698432
https://weavi.com/22698432?e5=20200706
https://weavi.com/22698432?e5
https://weavi.com/22698433
https://weavi.com/22698433?v0=20200706
https://weavi.com/22698433?v0
https://weavi.com/22698435
https://weavi.com/22698435?r7=20200706
https://weavi.com/22698435?r7
https://weavi.com/22698441
https://weavi.com/22698441?h1=20200706
https://weavi.com/22698441?h1
https://weavi.com/22698442
https://weavi.com/22698442?k4=20200706
https://weavi.com/22698442?k4
https://weavi.com/22698443
https://weavi.com/22698443?z4=20200706
https://weavi.com/22698443?z4
https://weavi.com/22698444
https://weavi.com/22698444?o9=20200706
https://weavi.com/22698444?o9
https://weavi.com/22698445
https://weavi.com/22698445?y8=20200706
https://weavi.com/22698445?y8
https://weavi.com/22698453
https://weavi.com/22698453?n2=20200706
https://weavi.com/22698453?n2
https://weavi.com/22698454
https://weavi.com/22698454?x9=20200706
https://weavi.com/22698454?x9
https://weavi.com/22698456
https://weavi.com/22698456?c7=20200706
https://weavi.com/22698456?c7
https://weavi.com/22698457
https://weavi.com/22698457?i5=20200706
https://weavi.com/22698457?i5
https://weavi.com/22698459
https://weavi.com/22698459?o2=20200706
https://weavi.com/22698459?o2
https://weavi.com/22698465
https://weavi.com/22698465?x4=20200706
https://weavi.com/22698465?x4
https://weavi.com/22698467
https://weavi.com/22698467?v4=20200706
https://weavi.com/22698467?v4
https://weavi.com/22698477
https://weavi.com/22698477?x1=20200706
https://weavi.com/22698477?x1
https://weavi.com/22698478
https://weavi.com/22698478?r4=20200706
https://weavi.com/22698478?r4
https://weavi.com/22698480
https://weavi.com/22698480?t6=20200706
https://weavi.com/22698480?t6
https://weavi.com/22698481
https://weavi.com/22698481?s3=20200706
https://weavi.com/22698481?s3
https://weavi.com/22698483
https://weavi.com/22698483?r5=20200706
https://weavi.com/22698483?r5
https://weavi.com/22698484
https://weavi.com/22698484?f5=20200706
https://weavi.com/22698484?f5
https://weavi.com/22698485
https://weavi.com/22698485?w2=20200706
https://weavi.com/22698485?w2
https://weavi.com/22698486
https://weavi.com/22698486?v1=20200706
https://weavi.com/22698486?v1
https://weavi.com/22698488
https://weavi.com/22698488?g3=20200706
https://weavi.com/22698488?g3
https://weavi.com/22698490
https://weavi.com/22698490?t1=20200706
https://weavi.com/22698490?t1
https://weavi.com/22698499
https://weavi.com/22698499?k4=20200706
https://weavi.com/22698499?k4
https://weavi.com/22698489
https://weavi.com/22698489?e5=20200706
https://weavi.com/22698489?e5
https://weavi.com/22698498
https://weavi.com/22698498?n0=20200706
https://weavi.com/22698498?n0
https://weavi.com/22698508
https://weavi.com/22698508?o7=20200706
https://weavi.com/22698508?o7
https://weavi.com/22698507
https://weavi.com/22698507?f3=20200706
https://weavi.com/22698507?f3
https://weavi.com/22698509
https://weavi.com/22698509?t9=20200706
https://weavi.com/22698509?t9
https://weavi.com/22698513
https://weavi.com/22698513?z7=20200706
https://weavi.com/22698513?z7
https://weavi.com/22698515
https://weavi.com/22698515?j2=20200706
https://weavi.com/22698515?j2
https://weavi.com/22698516
https://weavi.com/22698516?k0=20200706
https://weavi.com/22698516?k0
https://weavi.com/22698517
https://weavi.com/22698517?j9=20200706
https://weavi.com/22698517?j9
https://weavi.com/22698519
https://weavi.com/22698519?s7=20200706
https://weavi.com/22698519?s7
https://weavi.com/22698520
https://weavi.com/22698520?a1=20200706
https://weavi.com/22698520?a1
https://weavi.com/22698521
https://weavi.com/22698521?w4=20200706
https://weavi.com/22698521?w4
https://weavi.com/22698523
https://weavi.com/22698523?o1=20200706
https://weavi.com/22698523?o1
https://weavi.com/22698524
https://weavi.com/22698524?b5=20200706
https://weavi.com/22698524?b5
https://weavi.com/22698526
https://weavi.com/22698526?a0=20200706
https://weavi.com/22698526?a0
https://weavi.com/22698527
https://weavi.com/22698527?q3=20200706
https://weavi.com/22698527?q3
https://weavi.com/22698529
https://weavi.com/22698529?q9=20200706
https://weavi.com/22698529?q9
https://weavi.com/22698534
https://weavi.com/22698534?w3=20200706
https://weavi.com/22698534?w3
https://weavi.com/22698501
https://weavi.com/22698501?csrcm
https://weavi.com/22698502
https://weavi.com/22698502?ahrxp
https://weavi.com/22698504
https://weavi.com/22698504?dshdi
https://weavi.com/22698505
https://weavi.com/22698505?doobw
https://weavi.com/22698506
https://weavi.com/22698506?njsyw
https://weavi.com/22698511
https://weavi.com/22698511?ujrsp
https://weavi.com/22698512
https://weavi.com/22698512?vjwpj
https://weavi.com/22698522
https://weavi.com/22698522?lguvv
https://weavi.com/22698530
https://weavi.com/22698530?cryuj
https://weavi.com/22698533
https://weavi.com/22698533?oggcx
https://weavi.com/22698531
https://weavi.com/22698531?jaqsd
https://weavi.com/22698532
https://weavi.com/22698532?fdnoh
https://weavi.com/22698539
https://weavi.com/22698539?coyan
https://weavi.com/22698542
https://weavi.com/22698542?fjeqn
https://weavi.com/22698549
https://weavi.com/22698549?raqel
https://weavi.com/22698544
https://weavi.com/22698544?hqkrf
https://weavi.com/22698551
https://weavi.com/22698551?qully
https://weavi.com/22698552
https://weavi.com/22698552?efnog
https://weavi.com/22698554
https://weavi.com/22698554?efinn
https://weavi.com/22698555
https://weavi.com/22698555?ywxnc
https://weavi.com/22698556
https://weavi.com/22698556?wedig
https://weavi.com/22698558
https://weavi.com/22698558?gkucf
https://weavi.com/22698559
https://weavi.com/22698559?sbcca
https://weavi.com/22698560
https://weavi.com/22698560?glfug
https://weavi.com/22698562
https://weavi.com/22698562?vzqaw
https://weavi.com/22698563
https://weavi.com/22698563?vduua
https://weavi.com/22698565
https://weavi.com/22698565?rvanq
https://weavi.com/22698569
https://weavi.com/22698569?ecapb
https://weavi.com/22698571
https://weavi.com/22698571?akhhn
https://weavi.com/22698573
https://weavi.com/22698573?eatxv
https://weavi.com/22698572
https://weavi.com/22698572?hwfwn
https://weavi.com/22698575
https://weavi.com/22698575?zpcqf
https://weavi.com/22698576
https://weavi.com/22698576?ciudz
https://weavi.com/22698577
https://weavi.com/22698577?uinow
https://weavi.com/22698579
https://weavi.com/22698579?wlbdm
https://weavi.com/22698581
https://weavi.com/22698581?rmyly
https://weavi.com/22698584
https://weavi.com/22698584?yalht
https://weavi.com/22698582
https://weavi.com/22698582?djzpb
https://weavi.com/22698585
https://weavi.com/22698585?kdpfc
https://weavi.com/22698586
https://weavi.com/22698586?ufytt
https://weavi.com/22698588
https://weavi.com/22698588?kxcsg
https://weavi.com/22698593
https://weavi.com/22698593?kgdwq
https://weavi.com/22698594
https://weavi.com/22698594?oocqt
https://weavi.com/22698595
https://weavi.com/22698595?rdjre
https://weavi.com/22698598
https://weavi.com/22698598?jtfwv
https://weavi.com/22698599
https://weavi.com/22698599?wphst
https://weavi.com/22698596
https://weavi.com/22698596?wamit
https://weavi.com/22698602
https://weavi.com/22698602?lbkcx
https://weavi.com/22698605
https://weavi.com/22698605?ievol
https://weavi.com/22698603
https://weavi.com/22698603?jgwob
https://weavi.com/22698607
https://weavi.com/22698607?mlicb
https://weavi.com/22698611
https://weavi.com/22698611?eldgp
https://weavi.com/22698612
https://weavi.com/22698612?hpbxt
https://weavi.com/22698614
https://weavi.com/22698614?kefhx
https://weavi.com/22698615
https://weavi.com/22698615?etjeu
https://weavi.com/22698618
https://weavi.com/22698618?svxcw
https://weavi.com/22698619
https://weavi.com/22698619?zugie
https://weavi.com/22698620
https://weavi.com/22698620?frsbp
https://weavi.com/22698624
https://weavi.com/22698624?xtfcg
https://weavi.com/22698630
https://weavi.com/22698630?chmnm
https://weavi.com/22698628
https://weavi.com/22698628?iozpl
https://weavi.com/22698631
https://weavi.com/22698631?wyniw
https://weavi.com/22698627
https://weavi.com/22698627?jcmsj
https://weavi.com/22698632
https://weavi.com/22698632?cihfk
https://weavi.com/22698636
https://weavi.com/22698636?jjyeb
https://weavi.com/22698637
https://weavi.com/22698637?kwovu
https://weavi.com/22698639
https://weavi.com/22698639?jwpfk
https://weavi.com/22698638
https://weavi.com/22698638?jsrro
https://weavi.com/22698641
https://weavi.com/22698641?yfvni
https://weavi.com/22698645
https://weavi.com/22698645?jinct
https://weavi.com/22698646
https://weavi.com/22698646?uyvqd
https://weavi.com/22698648
https://weavi.com/22698648?jlpyr
https://weavi.com/22698649
https://weavi.com/22698649?yiomp
https://weavi.com/22698654
https://weavi.com/22698654?zwelr
https://weavi.com/22698650
https://weavi.com/22698650?udyqy
https://weavi.com/22698655
https://weavi.com/22698655?vzwrn
https://weavi.com/22698652
https://weavi.com/22698652?bkvsk
https://weavi.com/22698659
https://weavi.com/22698659?gufam
https://weavi.com/22698660
https://weavi.com/22698660?meuwq
https://weavi.com/22698661
https://weavi.com/22698661?iimhr
https://weavi.com/22698669
https://weavi.com/22698669?yfwcy
https://weavi.com/22698675
https://weavi.com/22698675?aatry
https://weavi.com/22698678
https://weavi.com/22698678?qetso
https://weavi.com/22698680
https://weavi.com/22698680?ujbgm
https://weavi.com/22698677
https://weavi.com/22698677?agobj
https://weavi.com/22698682
https://weavi.com/22698682?wlgtw