Mysql两张表连接多字段查询_【连接查询】mySql多表连接查
询与union与union。。。
1.准备两个表
表a:
结构:
mysql> desca;+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(40) | NO | PRI | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
数据
表b:
结构
mysql> descb;+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| nameB | varchar(40) | YES | | NULL | |
| ageB | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
数据:
2.进⾏连接查询测试:
(1)交叉连接(笛卡尔积) cross join
mysql> select * froma,b;  #第⼀种+------+------+-------+------+
| name | age | nameB | ageB |
+------+------+-------+------+
| A1 | 1 | B1 | 1 |
| A2 | 2 | B1 | 1 |
| A1 | 1 | B2 | 22 |
| A2 | 2 | B2 | 22 |
+------+------+-------+------+
4 rows in set (0.00sec)
mysql> select * from a cross joinb;  #第⼆种+------+------+-------+------+
| name | age | nameB | ageB |
+------+------+-------+------+
| A1 | 1 | B1 | 1 |
| A2 | 2 | B1 | 1 |
| A1 | 1 | B2 | 22 |
| A2 | 2 | B2 | 22 |
+------+------+-------+------+
4 rows in set (0.00sec)
mysql> select a.*,b.* from a cross joinb;  #第⼆种的⼜⼀个写法+------+------+-------+------+
| name | age | nameB | ageB |
+------+------+-------+------+
| A1 | 1 | B1 | 1 |
| A2 | 2 | B1 | 1 |
| A1 | 1 | B2 | 22 |
| A2 | 2 | B2 | 22 |
+------+------+-------+------+
4 rows in set (0.00 sec)
(2)内连接    join 或 inner join(在笛卡尔积的基础上过滤)
显⽰内连接
(1)不带条件的内连接
mysql> select a.*,b.* from a inner join b on a.age=b.ageb;　#第⼀种 inner join+------+------+-------+------+
| name | age | nameB | ageB |
+------+------+-------+------+
| A1 | 1 | B1 | 1 |
+------+------+-------+------+
1 row in set (0.00 sec)
mysql> select a.*,b.* from a join b on a.age=b.ageb;  #第⼆种 join (默认是inner join)+------+------+-------+------+ | name | age | nameB | ageB |
+------+------+-------+------+
| A1 | 1 | B1 | 1 |
+------+------+-------+------+
1 row in set (0.00 sec)
三个表的显⽰内连接:
SELECTa.*,
b.*,
c.*
FROMexampaper aINNER JOINbigquestion bINNER JOINexampaperquestion cON a.paperId =b.paperIdAND b.bigQuertionId = c.bigQuertionId
四个表的显⽰内连接:
train.majorName,
tc.*, course.*, type.*
FROMtrainschemeinfo trainJOIN train_course tc ainingSchemeID =tc.trainningSchemeIDINNER JOIN
t_course_base_info course urseID =urseIdINNER JOIN coursetypeinfo type peNum
=ainningSchemeID= '661ecb064b164d1ea133956f89beddb7'
与之等价的隐⼠内连接:
train.majorName,
tc.*, course.*, type.*
FROMtrainschemeinfo train,
train_course tc,
t_course_base_info course,
urseID =peNum =ainningSchemeID = '661ecb064b164d1ea133956f89beddb7'
(2)显⽰内连接带条件
mysql> select a.*,b.* from a join b on a.age=b.ageb having a.name='A1';  #having从查出的数据中挑选满⾜条件的元祖+------+------+-------+------+
| name | age | nameB | ageB |
+------+------+-------+------+
| A1 | 1 | B1 | 1 |
+------+------+-------+------+
1 row in set (0.00sec)
mysql> select a.*,b.* from a join b on a.age=b.ageb where a.name='A1';　#where查询满⾜条件的元素+------+------+-------+------+
| name | age | nameB | ageB |
+------+------+-------+------+
| A1 | 1 | B1 | 1 |
+------+------+-------+------+
1 row in set (0.00 sec)
隐⼠内连接:
mysql> select * from a,b where a.age=b.ageb;+------+------+-------+------+
| name | age | nameB | ageB |
+------+------+-------+------+
| A1 | 1 | B1 | 1 |
+------+------+-------+------+
1 row in set (0.00sec)
mysql> select * from a,b where a.age=b.ageb and a.name='A1';+------+------+-------+------+
| name | age | nameB | ageB |
+------+------+-------+------+
| A1 | 1 | B1 | 1 |
+------+------+-------+------+
1 row in set (0.00sec)
mysql> select * from a,b where a.age=b.ageb having a.name='A1';+------+------+-------+------+
| name | age | nameB | ageB |
+------+------+-------+------+
| A1 | 1 | B1 | 1 |
sql left join 多表连接+------+------+-------+------+
1 row in set (0.00 sec)
where是从本地磁盘查询满⾜条件的元素，having是从查出的数据中挑选满⾜条件的元素。执⾏权限  where>sum()..聚合函数>having (3)左外连接:(拿左边的匹配右边的，没有到右边的为null)
mysql> select * from a left join b on a.age =b.ageb;  #第⼀种 left join+------+------+-------+------+
| name | age | nameB | ageB |
+------+------+-------+------+
| A1 | 1 | B1 | 1 |
| A2 | 2 | NULL | NULL |
+------+------+-------+------+
2 rows in set (0.00sec)
mysql> select * from a left outer join b on a.age =b.ageb;  #第⼆种 left outer join+------+------+-------+------+
| name | age | nameB | ageB |
+------+------+-------+------+
| A1 | 1 | B1 | 1 |
| A2 | 2 | NULL | NULL |
+------+------+-------+------+
2 rows in set (0.00 sec)
(4)右外连接:(拿右边的匹配左边的，没有到左边的为null)
mysql> select * from a right join b on a.age =b.ageb;　#第⼀种 right join+------+------+-------+------+
| name | age | nameB | ageB |
+------+------+-------+------+
| A1 | 1 | B1 | 1 |
| NULL | NULL | B2 | 22 |
+------+------+-------+------+
2 rows in set (0.00sec)
mysql> select * from a right outer join b on a.age =b.ageb;　#第⼆种 right outer join+------+------+-------+------+ | name | age | nameB | ageB |
+------+------+-------+------+
| A1 | 1 | B1 | 1 |
| NULL | NULL | B2 | 22 |
+------+------+-------+------+
2 rows in set (0.00 sec)
3.Union 和 union all  取两个表的并集测试
修改b表，加⼀条和a表重复的数据
b表数据:
a表数据:
(1)  union:  ⾃动去掉重复元素
mysql> select * from a union select * fromb;+------+------+
| name | age |
+------+------+
| A1 | 1 |
| A2 | 2 |
| B1 | 1 |
| B2 | 22 |
+------+------+

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。