MySQL小结（五）连表查询
一、普通连表查询
普通的连表查询，把一个select的结果当作另外一个select的参数
1SELECT * FROM personnel.person_info
2where personnel.person_info.part_nid in
3(SELECT nid from personnel.part WHERE personnel.part.caption="XO股份有限公司公司-技术部-Python开发");
显示结果
1+-----+---------+------------------+-------------+----------+----------+---------+
2| nid | name | email | phone | part_nid | position | caption |
3+-----+---------+------------------+-------------+----------+----------+---------+
4| 1 | as | as@361way.com | 13800138000 | 5 | Python | NULL |
5| 2 | ansheng | as@361way.com | 13800138000 | 5 | Python | NULL |
6| 3 | a | as@361way.com | 13800138000 | 5 | Python | NULL |
7| 4 | v | as@361way.com | 13800138000 | 5 | Python | NULL |
8| 5 | b | as@361way.com | 13800138000 | 5 | Python | NULL |
9| 6 | w | as@361way.com | 13800138000 | 5 | Python | NULL |
10+-----+---------+------------------+-------------+----------+----------+---------+
116 rows in set (0.00 sec)
二、join连表查询
join查询如下：
1select * from personnel.person_info left join personnel.part
2on personnel.person_info.part_nid = personnel.part.nid
3where personnel.part.caption = 'XO股份有限公司公司-技术部-Python开发';
显示结果
1+-----+---------+---------------+-------------+----------+----------+---------+------+----------------------------------------------+
2| nid | name | email | phone | part_nid | position | caption | nid | caption |
3+-----+---------+---------------+-------------+----------+----------+---------+------+----------------------------------------------+
4| 1 | as | as@361way.com | 13800138000 | 5 | Python | NULL | 5 | XO股份有限公司公司-技术部-Python开发 |
5| 2 | ansheng | as@361way.com | 13800138000 | 5 | Python | NULL | 5 | XO股份有限公司公司-技术部-Python开发 |
6| 3 | a | as@361way.com | 13800138000 | 5 | Python | NULL | 5 | XO股份有限公司公司-技术部-Python开发 |
7| 4 | v | as@361way.com | 13800138000 | 5 | Python | NULL | 5 | XO股份有限公司公司-技术部-Python开发 |
8| 5 | b | as@361way.com | 13800138000 | 5 | Python | NULL | 5 | XO股份有限公司公司-技术部-Python开发 |
9| 6 | w | as@361way.com | 13800138000 | 5 | Python | NULL | 5 | XO股份有限公司公司-技术部-Python开发 |
10+-----+---------+---------------+-------------+----------+----------+---------+------+-----------------------------------------------+
116 rows in set (0.00 sec)
上面的意思是查询personnel.person_info.part_nid列的字段和personnel.part.nid一样的数据，并且personnel.part.caption = ‘XO股份有限公司公司-技术部-Python开发’：
参数
| 实例 | 描述 |
|---|---|
| left join | tb1 left join tb2 | 左外链接 |
| right join | tb1 right join tb2 | 右外链接 |
| inner join | tb1 inner join tb2 | 内链接 |
| Full Join | – | 全外连接 |
| CROSS | – | 交叉链接，又称笛卡尔链接或叉乘 |
left join
- tb1为主，tb2为辅，将A中所有的数据罗列出来
- tb2则只显示与tb1对应的数据
执行以下语句在person_info表中插入一条数据
1INSERT INTO personnel.person_info (NAME,email,phone,part_nid,position) VALUES("aa","a@ansheng.me",13800138000,3,"DBA");
通过left jion进行查询
1mysql> use personnel
2Database changed
3mysql> select * from person_info LEFT JOIN part on person_info.part_nid = part.nid WHERE part.nid = 3;
4+-----+------+--------------+-------------+----------+----------+---------+------+------------------------------------------+
5| nid | name | email | phone | part_nid | position | caption | nid | caption |
6+-----+------+--------------+-------------+----------+----------+---------+------+------------------------------------------+
7| 9 | aa | a@ansheng.me | 13800138000 | 3 | DBA | NULL | 3 | XO股份有限公司公司-技术部-DBA |
8+-----+------+--------------+-------------+----------+----------+---------+------+------------------------------------------+
91 row in set (0.00 sec)
这样他就只把我们刚刚插入的那条数据查询了除了，即查询person_info表中的内容，part表中的列作为person_info表的查询条件，如果person_info表中的part_nid列如果等于part表中nid列，那么就显示数据。
inner join
- 自动忽略两张表没有建立关联数据
- 只返回两个表中链接字段相等的数据
1select * from person_info inner JOIN part on person_info.part_nid = part.nid;
显示结果
1+-----+---------+---------------+-------------+----------+----------+---------+-----+---------------------------------------------------+
2| nid | name | email | phone | part_nid | position | caption | nid | caption |
3+-----+---------+---------------+-------------+----------+----------+---------+-----+---------------------------------------------------+
4| 1 | as | as@361way.com | 13800138000 | 5 | Python | NULL | 5 | XO股份有限公司公司-技术部-Python开发 |
5| 2 | ansheng | as@361way.com | 13800138000 | 5 | Python | NULL | 5 | XO股份有限公司公司-技术部-Python开发 |
6| 3 | a | as@361way.com | 13800138000 | 5 | Python | NULL | 5 | XO股份有限公司公司-技术部-Python开发 |
7| 4 | v | as@361way.com | 13800138000 | 5 | Python | NULL | 5 | XO股份有限公司公司-技术部-Python开发 |
8| 5 | b | as@361way.com | 13800138000 | 5 | Python | NULL | 5 | XO股份有限公司公司-技术部-Python开发 |
9| 6 | w | as@361way.com | 13800138000 | 5 | Python | NULL | 5 | XO股份有限公司公司-技术部-Python开发 |
10| 9 | aa | a@ansheng.me | 13800138000 | 3 | DBA | NULL | 3 | XO股份有限公司公司-技术部-DBA |
11+-----+---------+------------ --+-------------+----------+----------+---------+-----+---------------------------------------------------+
127 rows in set (0.00 sec)
MySQL Full Join的实现
把左右两个表的数据都取出来，不管是否匹配MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法。语法：
1select * from A left join B on A.id = B.id (where 条件）
2union --all可选
3select * from A right join B on A.id = B.id （where条件);
三、CROSS连接
如果A和B是两个集合，他们的交叉连接就记为：AxB
1mysql> use personnel
2Database changed
3mysql> SELECT * from course;
4+-----+--------+
5| Cno | Cname |
6+-----+--------+
7| 1 | 足球 |
8| 2 | 篮球 |
9| 3 | 排球 |
10+-----+--------+
113 rows in set (0.00 sec)
12mysql> SELECT * from student;
13+-----+--------+
14| Sno | Name |
15+-----+--------+
16| 1 | 张三 |
17| 2 | 李四 |
18| 3 | 王五 |
19+-----+--------+
203 rows in set (0.00 sec)
21mysql> SELECT * FROM course CROSS JOIN student;
22+-----+--------+-----+--------+
23| Cno | Cname | Sno | Name |
24+-----+--------+-----+--------+
25| 1 | 足球 | 1 | 张三 |
26| 2 | 篮球 | 1 | 张三 |
27| 3 | 排球 | 1 | 张三 |
28| 1 | 足球 | 2 | 李四 |
29| 2 | 篮球 | 2 | 李四 |
30| 3 | 排球 | 2 | 李四 |
31| 1 | 足球 | 3 | 王五 |
32| 2 | 篮球 | 3 | 王五 |
33| 3 | 排球 | 3 | 王五 |
34+-----+--------+-----+--------+
359 rows in set (0.00 sec)
四、一对多，多表查询
创建color表
1CREATE TABLE `color` (
2 `nid` int(11) NOT NULL AUTO_INCREMENT,
3 `title` varchar(32) DEFAULT NULL,
4 PRIMARY KEY (`nid`)
5) ENGINE=InnoDB DEFAULT CHARSET=utf8;
往color表中添加两条数据
1insert into color(title) values('red'),('yellow');
person_info表添加color_nid列，类型是int
1alter table person_info add color_nid int;
把person_info表中的color_nid列和color表中的nid列做一个外键关联
1alter table person_info add constraint person_ibfk_2 foreign key person_info(`color_nid`) REFERENCES color(`nid`);
往person_info表中插入一条数据：
1INSERT INTO personnel.person_info (NAME,email,phone,part_nid,position,color_nid) VALUES("b", "b.ansheng.me",13800138000,3,"DBA",1)
查询职位是XO股份有限公司公司-技术部-DBA，颜色是yellow的的人员
1SELECT * FROM person_info LEFT JOIN part ON person_info.part_nid = part.nid LEFT JOIN color ON person_info.color_nid = color.nid WHERE color.title = "yellow";
五、多对多关系及查询
先创建三张表
1#student(学生表)
2CREATE TABLE `student` (
3 `Sno` int(11) NOT NULL AUTO_INCREMENT,
4 `Name` char(20) NOT NULL,
5 PRIMARY KEY (`Sno`)
6) ENGINE=InnoDB DEFAULT CHARSET=utf8;
7#course(课程表)
8CREATE TABLE `course` (
9 `Cno` int(11) NOT NULL AUTO_INCREMENT,
10 `Cname` char(10) NOT NULL,
11 PRIMARY KEY (`Cno`)
12) ENGINE=InnoDB DEFAULT CHARSET=utf8;
13#student_course(关系表)
14CREATE TABLE `student_course` (
15 `ID` int(11) NOT NULL AUTO_INCREMENT,
16 `Sno` int(11) NOT NULL,
17 `Cno` int(11) NOT NULL,
18 PRIMARY KEY (`ID`)
19) ENGINE=InnoDB DEFAULT CHARSET=utf8;
设置外键关联
1ALTER TABLE student_course ADD CONSTRAINT student_course_to_student_ibfk_2 FOREIGN KEY student_course (`Sno`) REFERENCES student (`Sno`);
2ALTER TABLE student_course ADD CONSTRAINT student_course_to_course_ibfk_1 FOREIGN KEY student_course (`Cno`) REFERENCES course (`Cno`);
往三个表中分别插入数据：
1#往course(课程表)中插入数据
2INSERT INTO course(Cname) VALUES("足球"),("篮球"),("排球");
3#往student(学生表)中插入数据
4INSERT INTO student(Name) VALUES("张三"),("李四"),("王五");
5#student_course(关系表)插入数据
6INSERT INTO student_course(Sno,Cno) VALUES(2,1),(2,3),(3,3),(3,1),(1,2),(3,2);
显示学生所选的课程SQL指令
1SELECT s.Name,C.Cname FROM student_course AS sc LEFT JOIN student AS s ON s.Sno=sc.Sno LEFT JOIN course AS c ON c.Cno=sc.Cno;
结果如下:
1+--------+--------+
2| Name | Cname |
3+--------+--------+
4| 张三 | 篮球 |
5| 李四 | 足球 |
6| 李四 | 排球 |
7| 王五 | 排球 |
8| 王五 | 足球 |
9| 王五 | 篮球 |
10+--------+--------+
116 rows in set (0.00 sec)
捐赠本站(Donate)
如您感觉文章有用，可扫码捐赠本站！(If the article useful, you can scan the QR code to donate))
- Author: shisekong
- Link: https://blog.361way.com/table-join-query/5387.html
- License: This work is under a 知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议. Kindly fulfill the requirements of the aforementioned License when adapting or creating a derivative of this work.