Shulou(Shulou.com)06/01 Report--

The teacher who didn't teach the students stayed. (same logic as left outer connection) MariaDB [hellodb] > select * from S1 right outer join T1 on s1.teacherid=t1.tid where s1.teacherid is null +-+ | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +- -- + | NULL | 2 | Zhang Sanfeng | 94 | M | +- +-+ 1 row in set (0.00 sec) full external connection and cross connection are different mysql does not support full outer join

Combine the left outer connection with the right outer connection with union, corresponding where there is intersection, and local output where there is no intersection.

MariaDB [hellodb] > select * from S1 left outer join T1 on s1.teacherid=t1.tid-> union-> select * from S1 right outer join T1 on s1.teacherid=t1.tid +-+ | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +-+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F | 2 | Shi Potian | 22 | M | 1 | NULL | 3 | Xie Yanke | 53 | M | 2 | NULL | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F | 5 | Yu Yutong | 26 | M | 3 | 1 | Song Jiang | 45 | M | 6 | Shi Qing | 46 | M | NULL | 7 | Xi Ren | 19 | F | 3 | NULL | 8 | Lin Daiyu | 17 | F | 7 | NULL | 9 | Ren Yingying | 20 | F | 6 | NULL | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 12 | Wen Qingqing | 19 | F | 1 | NULL | 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | 14 | Lu Wushuang | 17 | F | 3 | NULL | 15 | Duan Yu | 19 | M | 4 | NULL | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | 18 | Hua Rong | 23 | M | 7 | NULL | 19 | Xue Baochai | 18 | Xue Baochai | 18 | NULL | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | 22 | Xiao Qiao | 20 | F | 1 | NULL | 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | | | NULL | NULL | | 24 | Xu Xian | 27 | M | NULL | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M | NULL | 2 | Zhang Sanfeng | 94 | M | +-| -+ 26 rows in set (0.00 sec)

Full external connection extension usage: remove where there is intersection, leaving only external connections.

Take out the students who have no teachers and the teachers who have no students. MariaDB [hellodb] > select * from (select s. Stuidcamera s. Name swatches s right outer join t 1 on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null. Name t_name from S1 s left outer join t 1 t on s.teacherid=t.tid union select s. Name stuidparentin s.teacheridPart. +-+ | stuid | s_name | teacherid | tid | t_name | + -- +-+ | 2 | Shi Potian | 7 | NULL | NULL | | 3 | Xie Yanke | 16 | NULL | NULL | 6 | Shi Qing | NULL | | 7 | Xi Ren | NULL | 8 | Lin Daiyu | NULL | NULL | | NULL | | 9 | Ren Yingying | NULL | | 10 | Yue Lingshan | NULL | 11 | Yuan Chengzhi | NULL | | 12 | Wen Qingqing | NULL | 13 | Tian Boguang | NULL | 14 | Lu Wushuang | NULL | | 15 | Duan Yu | NULL | | 16 | Xu Zhu | NULL | 17 | Lin Chong | NULL | 18 | Hua Rong | NULL | 19 | Xue Baochai | NULL | 20 | Diao Chan | NULL | | 21 | | | Huang Yueying | NULL | | 22 | Xiao Qiao | NULL | | 23 | Ma Chao | NULL | 24 | Xu Xian | NULL | 2 | Zhang Sanfeng | +-+-+ | -+ 22 rows in set (0.00 sec)

Query with three tables

Take students' names, grades and subjects.

First take out the students' names and grades (think clearly in two steps)

MariaDB [hellodb] > select st.name,sc.courseid,sc.score from S1 st inner join scores sc on st.stuid=sc.stuid +-+ | name | courseid | score | +-+ | Shi Zhongyu | 2 | 77 | | Shi Zhongyu | 6 | 93 | Shi Potian | 2 | 47 | | Shi Potian | 5 | 97 | Xie Yanke | 2 | 88 | | Xie Yanke | 6 | 75 | Ding Dian | 5 | 71 | Ding Dian | 2 | 89 | Yu Yutong | 1 | 39 | Yu Yutong | 7 | 63 | Shi Qing | 1 | 96 | Xi Ren | 1 | 86 | Xi Ren | 7 | 83 | | Lin Daiyu | 4 | 57 | | Lin Daiyu | 3 | 93 | +-+ 15 rows in set (0.00 sec)

Take out the student's name, grade, and subject at one time.

MariaDB [hellodb] > select st.name,co.course,sc.score from S1 st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid +-+ | name | course | score | +-+ | Shi Zhongyu | Kuihua Baodian | 77 | | Shi Zhongyu | Weituo Zhang | 93 | | Shi Potian | Kuihua Baodian | 47 | | Shi Potian | Daiyu Zanghua | 97 | Xie Yanke | Kuihua Baodian | 88 | Xie Yanke | Weituo Zhang | 75 | Ding Dian | Daiyu Zanghua | 71 | Ding Dian | Kuihua Baodian | 89 | Yu Yutong | Hamo Gong | 39 | Yu Yutong | Dagou Bangfa | 63 | Shi Qing | Hamo Gong | 96 | Xi Ren | Hamo Gong | 86 | Xi Ren | Dagou Bangfa | 83 | Lin Daiyu | Taiji Quan | 57 | | Lin Daiyu | Jinshe Jianfa | 93 | +-+ 15 rows in set (0.00 sec) four-table join is strictly prohibited

Self-connection

Self-join, as the name implies, assumes a table as two identical tables, and then makes a "multi-table query".

Build a table first

MariaDB [hellodb] > create table emp (id int, name char (20), leaderid int); Query OK, 0 rows affected (0.01 sec) MariaDB [hellodb] > insert emp value; Query OK, 1 row affected (0.00 sec) MariaDB [hellodb] > insert emp value; ERROR 1366 (22007): Incorrect integer value: 'huangshang' for column `hellodb`.`emp`emp`leaderid` at row 1MariaDB [hellodb] > insert emp value Query OK, 1 row affected (0.00 sec) MariaDB [hellodb] > insert emp value; Query OK, 1 row affected (0.00 sec) MariaDB [hellodb] > insert emp value; Query OK, 1 row affected (0.00 sec) MariaDB [hellodb] > select * from emp +-+ | id | name | leaderid | 1 | huangshang | NULL | | 2 | taihou | 1 | 3 | guifei | 2 | | 4 | shufei | 3 | +- -+ 4 rows in set (0.00 sec)

Look up the names of the superiors in the table.

Imagine making MariaDB [hellodb] > select * from emp as e inner join emp as l on e.leaderid=l.id with aliases for two tables, the employee table and the superior table. +-+ | id | name | leaderid | id | name | leaderid | +- -+-+ | 2 | taihou | 1 | 1 | huangshang | NULL | 3 | guifei | 2 | taihou | 1 | 4 | shufei | 3 | 3 | guifei | 2 | +- -+-+ 3 rows in set (0.00 sec) take out the corresponding superior But without the supreme superior. MariaDB [hellodb] > select e.name emp,l.name leader from emp as e inner join emp as l on e.leaderid=l.id +-+-+ | emp | leader | +-+-+ | taihou | huangshang | | guifei | taihou | | shufei | guifei | +-+-+ 3 rows in set (0.00 sec) take out the id of each person's superior. MariaDB [hellodb] > select e.name emp,l.name leader from emp as e left join emp as l on e.leaderid=l.id +-+-+ | emp | leader | +-+-+ | taihou | huangshang | | guifei | taihou | | shufei | guifei | | huangshang | NULL | +-+-+ 4 rows in set (0.00 sec)

Keyword execution order of SQL statements

This is the end of the multi-table query in the MySQL database. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.