Eine kurze Diskussion über Multi-Table-Abfragen in MySQL-Datenbanken
- 青灯夜游nach vorne
- 2019-11-29 15:47:493151Durchsuche
In diesem Artikel erfahren Sie mehr über Abfragen mit mehreren Tabellen in MySQL-Datenbanken. Es hat einen gewissen Referenzwert. Freunde in Not können sich darauf beziehen. Ich hoffe, es wird für alle hilfreich sein.
Mehrtabellenabfrage
Die Abfrageergebnisse stammen aus mehreren Tabellen, also mehreren Tabellen. Tabellenabfrage
Abfrageanweisungen sind in SQL-Anweisungen verschachtelt, mit schlechter Leistung. Abfrage basierend auf den Abfrageergebnissen einer bestimmten Anweisung.
Union-Abfrage: UNION
Cross-Join: Kartesisch Produkt
Inner Join:
Äquivalenter Join: Lassen Sie die Felder zwischen Tabellen eine Verbindungsbeziehung mit „Äquivalenz“ herstellen.
Ungleicher Join: Die ungleiche Join-Abfrage ist eine bedingungslose Beurteilung, wenn Sie die Felder in mehreren Tabellen abfragen , werden die darin enthaltenen Daten nicht synchronisiert, sondern jede zeigt ihre eigenen Daten ohne Korrelation an.
Natürlicher Join: Doppelte Spalten entfernen und gleicher Join.
Äußerer Join:
Linker äußerer Join: FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
Rechter äußerer Join: FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
Selbstverknüpfung: Verbindungsabfrage zwischen dieser Tabelle und dieser Tabelle durchführen
Unterabfrage
Unterabfragen, die häufig in WHERE-Klauseln verwendet werden
1. Wird zum Vergleichen von Unterabfragen in Ausdrücken verwendet. Unterabfragen können nur einen einzelnen Wert zurückgeben (Abfrage für Personen, die älter als das Durchschnittsalter in der s1-Tabelle sind)
MariaDB [hellodb]> select * from s1 where age > (select avg(age) from s1); +-------+--------------+-------+-----+--------+---------+-----------+ | StuID | Name | phone | Age | Gender | ClassID | TeacherID | +-------+--------------+-------+-----+--------+---------+-----------+ | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | | 4 | Ding Dian | NULL | 32 | M | 4 | 4 | | 5 | Yu Yutong | NULL | 26 | M | 3 | 1 | | 6 | Shi Qing | NULL | 46 | M | 5 | NULL | | 13 | Tian Boguang | NULL | 33 | M | 2 | NULL | | 24 | Xu Xian | NULL | 27 | M | NULL | NULL | | 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL | +-------+--------------+-------+-----+--------+---------+-----------+ 7 rows in set (0.01 sec)
2. Die Abfrageergebnisse werden in eine andere Tabelle eingebettet und die Dezimalkonvertierung in eine Ganzzahl wird gerundet
MariaDB [hellodb]> select avg(age) from s1 ; (查看s1表平均年龄) +----------+ | avg(age) | +----------+ | 25.0857 | +----------+ 1 row in set (0.00 sec) MariaDB [hellodb]> select * from teachers; （原来的表内容） +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 26 | F | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec) MariaDB [hellodb]> update teachers set age=(select avg(age) from s1); （插入查询结果的表内容，没有指定字段会改掉所有） Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 MariaDB [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 25 | M | | 2 | Zhang Sanfeng | 25 | M | | 3 | Miejue Shitai | 25 | F | | 4 | Lin Chaoying | 25 | F | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec) MariaDB [hellodb]> update teachers set age=48 where tid=4; （把tid为4的age修改为48做下面实验用） Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 25 | M | | 2 | Zhang Sanfeng | 25 | M | | 3 | Miejue Shitai | 25 | F | | 4 | Lin Chaoying | 48 | F | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec) MariaDB [hellodb]> update teachers set age=(select avg(age) from s1) where tid=4; （指定tid为4的age字段修改） Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 25 | M | | 2 | Zhang Sanfeng | 25 | M | | 3 | Miejue Shitai | 25 | F | | 4 | Lin Chaoying | 25 | F | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec)
Mehrtabellenabfrage:
Verwenden Sie eine Unterschleife, um die anzuzeigen s1-Tabelle und zeigen Sie die Lehrertabelle an. Informationen zu Personen, deren Alter über dem Durchschnittsalter der s1-Tabelle liegt.
MariaDB [hellodb]> update teachers set age=45 where tid=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [hellodb]> update teachers set age=94 where tid=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [hellodb]> update teachers set age=77 where tid=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 25 | F | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec) （以上是把年龄修改回来做实验） MariaDB [hellodb]> select * from teachers where age > (select avg(age) from s1); （多表子循环查询平均年龄大于25的人） +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | +-----+---------------+-----+--------+ 3 rows in set (0.00 sec) MariaDB [hellodb]> update teachers set age=26 where tid=4; （修改一下最后一条的年龄为26） Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [hellodb]> select * from teachers where age > (select avg(age) from s1); （最后一条也大于25就显示出来了） +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 26 | F | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec)
Union-Abfrage
Union führt zwei Tabellen vertikal zusammen, und der Tabellenkopf stammt aus dem ersten Abfragedatensatz.
MariaDB [hellodb]> select * from teachers -> union -> select stuid,name,age,gender from s1; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 26 | F | | 1 | Shi Zhongyu | 22 | M | | 2 | Shi Potian | 22 | M | | 3 | Xie Yanke | 53 | M | | 4 | Ding Dian | 32 | M | | 5 | Yu Yutong | 26 | M | | 6 | Shi Qing | 46 | M | | 7 | Xi Ren | 19 | F | | 8 | Lin Daiyu | 17 | F | | 9 | Ren Yingying | 20 | F | | 10 | Yue Lingshan | 19 | F | | 11 | Yuan Chengzhi | 23 | M | | 12 | Wen Qingqing | 19 | F | | 13 | Tian Boguang | 33 | M | | 14 | Lu Wushuang | 17 | F | | 15 | Duan Yu | 19 | M | | 16 | Xu Zhu | 21 | M | | 17 | Lin Chong | 25 | M | | 18 | Hua Rong | 23 | M | | 19 | Xue Baochai | 18 | F | | 20 | Diao Chan | 19 | F | | 21 | Huang Yueying | 22 | F | | 22 | Xiao Qiao | 20 | F | | 23 | Ma Chao | 23 | M | | 24 | Xu Xian | 27 | M | | 25 | Sun Dasheng | 100 | M | | 26 | xietingfeng | 23 | M | | 27 | liudehua | 18 | F | | 28 | mahuateng | 20 | M | | 29 | wuyanzu | 19 | M | | 30 | wuzetian | 21 | F | | 31 | Song Jiang | 18 | M | | 32 | Zhang Sanfeng | 18 | M | | 33 | Miejue Shitai | 18 | F | | 34 | Lin Chaoying | 18 | F | | 38 | abc | 20 | M | +-----+---------------+-----+--------+ 39 rows in set (0.00 sec) MariaDB [hellodb]> select tid as id ,name,age,gender from teachers union select stuid,name,age,gender from s1; (起个别名替换掉表头的tid并纵向合并两张表) +----+---------------+-----+--------+ | id | name | age | gender | +----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 26 | F | | 1 | Shi Zhongyu | 22 | M | | 2 | Shi Potian | 22 | M | | 3 | Xie Yanke | 53 | M | | 4 | Ding Dian | 32 | M | | 5 | Yu Yutong | 26 | M | | 6 | Shi Qing | 46 | M | | 7 | Xi Ren | 19 | F | | 8 | Lin Daiyu | 17 | F | | 9 | Ren Yingying | 20 | F | | 10 | Yue Lingshan | 19 | F | | 11 | Yuan Chengzhi | 23 | M | | 12 | Wen Qingqing | 19 | F | | 13 | Tian Boguang | 33 | M | | 14 | Lu Wushuang | 17 | F | | 15 | Duan Yu | 19 | M | | 16 | Xu Zhu | 21 | M | | 17 | Lin Chong | 25 | M | | 18 | Hua Rong | 23 | M | | 19 | Xue Baochai | 18 | F | | 20 | Diao Chan | 19 | F | | 21 | Huang Yueying | 22 | F | | 22 | Xiao Qiao | 20 | F | | 23 | Ma Chao | 23 | M | | 24 | Xu Xian | 27 | M | | 25 | Sun Dasheng | 100 | M | | 26 | xietingfeng | 23 | M | | 27 | liudehua | 18 | F | | 28 | mahuateng | 20 | M | | 29 | wuyanzu | 19 | M | | 30 | wuzetian | 21 | F | | 31 | Song Jiang | 18 | M | | 32 | Zhang Sanfeng | 18 | M | | 33 | Miejue Shitai | 18 | F | | 34 | Lin Chaoying | 18 | F | | 38 | abc | 20 | M | +----+---------------+-----+--------+ 39 rows in set (0.00 sec)
Union verbindet sich mit sich selbst, um Duplikate zu entfernen.
MariaDB [hellodb]> create table t2 select * from teachers; （先导一张表出来做实验不能有主键，所以只用了这种方法导了数据没有把主键导过来）
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> select * from t2;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 26 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> desc t2;
+--------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| TID | smallint(5) unsigned | NO | | 0 | |
| Name | varchar(100) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | YES | | NULL | |
+--------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
（添加重复的行做实验）
MariaDB [hellodb]> insert into t2 set tid=4,name='linchaoying',age=26,gender='F'; （这条记录添加的只有name不一样少了个空格）
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> insert into t2 set tid=4,name='lin chaoying',age=26,gender='F'; （完全一样加了一行）
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from t2;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 26 | F |
| 4 | linchaoying | 26 | F |
| 4 | lin chaoying | 26 | F |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
MariaDB [hellodb]> select * from t2 union select * from t2; （用union过滤掉重复的行，少一个空格的那条记录过滤不了）
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 26 | F |
| 4 | linchaoying | 26 | F |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
Verwenden Sie alle, um einfach zwei Tabellen ohne Deduplizierung zu verbinden
MariaDB [hellodb]> select * from t2 union all select * from t2; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 26 | F | | 4 | linchaoying | 26 | F | | 4 | lin chaoying | 26 | F | | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 26 | F | | 4 | linchaoying | 26 | F | | 4 | lin chaoying | 26 | F | +-----+---------------+-----+--------+ 12 rows in set (0.00 sec)
Cross-Join
两张表横向组合，类似于笛卡尔乘积。 （cross join）
Verwenden Sie Cross-Join zwischen zwei Tabellen A Join bedeutet, dass jede Zeile dieser Tabelle mit allen Zeilen einer anderen Tabelle zu einer neuen Zeile zusammengefasst wird.
MariaDB [hellodb]> select * from s1 cross join teachers; +-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+ | StuID | Name | phone | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+ | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | 1 | Song Jiang | 25 | M | | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | 2 | Zhang Sanfeng | 25 | M | | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | 3 | Miejue Shitai | 25 | F | | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | 4 | Lin Chaoying | 25 | F | | 2 | Shi Potian | NULL | 22 | M | 1 | 7 | 1 | Song Jiang | 25 | M | | 2 | Shi Potian | NULL | 22 | M | 1 | 7 | 2 | Zhang Sanfeng | 25 | M | | 2 | Shi Potian | NULL | 22 | M | 1 | 7 | 3 | Miejue Shitai | 25 | F | | 2 | Shi Potian | NULL | 22 | M | 1 | 7 | 4 | Lin Chaoying | 25 | F | | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | 1 | Song Jiang | 25 | M | | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | 2 | Zhang Sanfeng | 25 | M | | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | 3 | Miejue Shitai | 25 | F | | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | 4 | Lin Chaoying | 25 | F | | 4 | Ding Dian | NULL | 32 | M | 4 | 4 | 1 | Song Jiang | 25 | M | | 4 | Ding Dian | NULL | 32 | M | 4 | 4 | 2 | Zhang Sanfeng | 25 | M | | 4 | Ding Dian | NULL | 32 | M | 4 | 4 | 3 | Miejue Shitai | 25 | F | | 4 | Ding Dian | NULL | 32 | M | 4 | 4 | 4 | Lin Chaoying | 25 | F | | 5 | Yu Yutong | NULL | 26 | M | 3 | 1 | 1 | Song Jiang | 25 | M | | 5 | Yu Yutong | NULL | 26 | M | 3 | 1 | 2 | Zhang Sanfeng | 25 | M | | 5 | Yu Yutong | NULL | 26 | M | 3 | 1 | 3 | Miejue Shitai | 25 | F | | 5 | Yu Yutong | NULL | 26 | M | 3 | 1 | 4 | Lin Chaoying | 25 | F | | 6 | Shi Qing | NULL | 46 | M | 5 | NULL | 1 | Song Jiang | 25 | M | | 6 | Shi Qing | NULL | 46 | M | 5 | NULL | 2 | Zhang Sanfeng | 25 | M | | 6 | Shi Qing | NULL | 46 | M | 5 | NULL | 3 | Miejue Shitai | 25 | F | | 6 | Shi Qing | NULL | 46 | M | 5 | NULL | 4 | Lin Chaoying | 25 | F | | 7 | Xi Ren | NULL | 19 | F | 3 | NULL | 1 | Song Jiang | 25 | M | | 7 | Xi Ren | NULL | 19 | F | 3 | NULL | 2 | Zhang Sanfeng | 25 | M | | 7 | Xi Ren | NULL | 19 | F | 3 | NULL | 3 | Miejue Shitai | 25 | F | | 7 | Xi Ren | NULL | 19 | F | 3 | NULL | 4 | Lin Chaoying | 25 | F | | 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL | 1 | Song Jiang | 25 | M | | 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL | 2 | Zhang Sanfeng | 25 | M | | 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL | 3 | Miejue Shitai | 25 | F | | 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL | 4 | Lin Chaoying | 25 | F | | 9 | Ren Yingying | NULL | 20 | F | 6 | NULL | 1 | Song Jiang | 25 | M | | 9 | Ren Yingying | NULL | 20 | F | 6 | NULL | 2 | Zhang Sanfeng | 25 | M | | 9 | Ren Yingying | NULL | 20 | F | 6 | NULL | 3 | Miejue Shitai | 25 | F | | 9 | Ren Yingying | NULL | 20 | F | 6 | NULL | 4 | Lin Chaoying | 25 | F | | 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL | 1 | Song Jiang | 25 | M | | 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL | 2 | Zhang Sanfeng | 25 | M | | 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL | 3 | Miejue Shitai | 25 | F | | 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL | 4 | Lin Chaoying | 25 | F | | 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL | 1 | Song Jiang | 25 | M | | 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL | 2 | Zhang Sanfeng | 25 | M | | 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL | 3 | Miejue Shitai | 25 | F | | 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL | 4 | Lin Chaoying | 25 | F | | 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL | 1 | Song Jiang | 25 | M | | 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL | 2 | Zhang Sanfeng | 25 | M | | 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL | 3 | Miejue Shitai | 25 | F | | 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL | 4 | Lin Chaoying | 25 | F | | 13 | Tian Boguang | NULL | 33 | M | 2 | NULL | 1 | Song Jiang | 25 | M | | 13 | Tian Boguang | NULL | 33 | M | 2 | NULL | 2 | Zhang Sanfeng | 25 | M | | 13 | Tian Boguang | NULL | 33 | M | 2 | NULL | 3 | Miejue Shitai | 25 | F | | 13 | Tian Boguang | NULL | 33 | M | 2 | NULL | 4 | Lin Chaoying | 25 | F | | 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL | 1 | Song Jiang | 25 | M | | 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL | 2 | Zhang Sanfeng | 25 | M | | 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL | 3 | Miejue Shitai | 25 | F | | 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL | 4 | Lin Chaoying | 25 | F | | 15 | Duan Yu | NULL | 19 | M | 4 | NULL | 1 | Song Jiang | 25 | M | | 15 | Duan Yu | NULL | 19 | M | 4 | NULL | 2 | Zhang Sanfeng | 25 | M | | 15 | Duan Yu | NULL | 19 | M | 4 | NULL | 3 | Miejue Shitai | 25 | F | | 15 | Duan Yu | NULL | 19 | M | 4 | NULL | 4 | Lin Chaoying | 25 | F | | 16 | Xu Zhu | NULL | 21 | M | 1 | NULL | 1 | Song Jiang | 25 | M | | 16 | Xu Zhu | NULL | 21 | M | 1 | NULL | 2 | Zhang Sanfeng | 25 | M | | 16 | Xu Zhu | NULL | 21 | M | 1 | NULL | 3 | Miejue Shitai | 25 | F | | 16 | Xu Zhu | NULL | 21 | M | 1 | NULL | 4 | Lin Chaoying | 25 | F | | 17 | Lin Chong | NULL | 25 | M | 4 | NULL | 1 | Song Jiang | 25 | M | | 17 | Lin Chong | NULL | 25 | M | 4 | NULL | 2 | Zhang Sanfeng | 25 | M | | 17 | Lin Chong | NULL | 25 | M | 4 | NULL | 3 | Miejue Shitai | 25 | F | | 17 | Lin Chong | NULL | 25 | M | 4 | NULL | 4 | Lin Chaoying | 25 | F | | 18 | Hua Rong | NULL | 23 | M | 7 | NULL | 1 | Song Jiang | 25 | M | | 18 | Hua Rong | NULL | 23 | M | 7 | NULL | 2 | Zhang Sanfeng | 25 | M | | 18 | Hua Rong | NULL | 23 | M | 7 | NULL | 3 | Miejue Shitai | 25 | F | | 18 | Hua Rong | NULL | 23 | M | 7 | NULL | 4 | Lin Chaoying | 25 | F | | 19 | Xue Baochai | NULL | 18 | F | 6 | NULL | 1 | Song Jiang | 25 | M | | 19 | Xue Baochai | NULL | 18 | F | 6 | NULL | 2 | Zhang Sanfeng | 25 | M | | 19 | Xue Baochai | NULL | 18 | F | 6 | NULL | 3 | Miejue Shitai | 25 | F | | 19 | Xue Baochai | NULL | 18 | F | 6 | NULL | 4 | Lin Chaoying | 25 | F | | 20 | Diao Chan | NULL | 19 | F | 7 | NULL | 1 | Song Jiang | 25 | M | | 20 | Diao Chan | NULL | 19 | F | 7 | NULL | 2 | Zhang Sanfeng | 25 | M | | 20 | Diao Chan | NULL | 19 | F | 7 | NULL | 3 | Miejue Shitai | 25 | F | | 20 | Diao Chan | NULL | 19 | F | 7 | NULL | 4 | Lin Chaoying | 25 | F | | 21 | Huang Yueying | NULL | 22 | F | 6 | NULL | 1 | Song Jiang | 25 | M | | 21 | Huang Yueying | NULL | 22 | F | 6 | NULL | 2 | Zhang Sanfeng | 25 | M | | 21 | Huang Yueying | NULL | 22 | F | 6 | NULL | 3 | Miejue Shitai | 25 | F | | 21 | Huang Yueying | NULL | 22 | F | 6 | NULL | 4 | Lin Chaoying | 25 | F | | 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL | 1 | Song Jiang | 25 | M | | 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL | 2 | Zhang Sanfeng | 25 | M | | 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL | 3 | Miejue Shitai | 25 | F | | 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL | 4 | Lin Chaoying | 25 | F | | 23 | Ma Chao | NULL | 23 | M | 4 | NULL | 1 | Song Jiang | 25 | M | | 23 | Ma Chao | NULL | 23 | M | 4 | NULL | 2 | Zhang Sanfeng | 25 | M | | 23 | Ma Chao | NULL | 23 | M | 4 | NULL | 3 | Miejue Shitai | 25 | F | | 23 | Ma Chao | NULL | 23 | M | 4 | NULL | 4 | Lin Chaoying | 25 | F | | 24 | Xu Xian | NULL | 27 | M | NULL | NULL | 1 | Song Jiang | 25 | M | | 24 | Xu Xian | NULL | 27 | M | NULL | NULL | 2 | Zhang Sanfeng | 25 | M | | 24 | Xu Xian | NULL | 27 | M | NULL | NULL | 3 | Miejue Shitai | 25 | F | | 24 | Xu Xian | NULL | 27 | M | NULL | NULL | 4 | Lin Chaoying | 25 | F | | 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL | 1 | Song Jiang | 25 | M | | 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL | 2 | Zhang Sanfeng | 25 | M | | 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL | 3 | Miejue Shitai | 25 | F | | 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL | 4 | Lin Chaoying | 25 | F | | 26 | xietingfeng | NULL | 23 | M | 2 | 1 | 1 | Song Jiang | 25 | M | | 26 | xietingfeng | NULL | 23 | M | 2 | 1 | 2 | Zhang Sanfeng | 25 | M | | 26 | xietingfeng | NULL | 23 | M | 2 | 1 | 3 | Miejue Shitai | 25 | F | | 26 | xietingfeng | NULL | 23 | M | 2 | 1 | 4 | Lin Chaoying | 25 | F | | 27 | liudehua | NULL | 18 | F | 1 | NULL | 1 | Song Jiang | 25 | M | | 27 | liudehua | NULL | 18 | F | 1 | NULL | 2 | Zhang Sanfeng | 25 | M | | 27 | liudehua | NULL | 18 | F | 1 | NULL | 3 | Miejue Shitai | 25 | F | | 27 | liudehua | NULL | 18 | F | 1 | NULL | 4 | Lin Chaoying | 25 | F | | 28 | mahuateng | NULL | 20 | M | 3 | NULL | 1 | Song Jiang | 25 | M | | 28 | mahuateng | NULL | 20 | M | 3 | NULL | 2 | Zhang Sanfeng | 25 | M | | 28 | mahuateng | NULL | 20 | M | 3 | NULL | 3 | Miejue Shitai | 25 | F | | 28 | mahuateng | NULL | 20 | M | 3 | NULL | 4 | Lin Chaoying | 25 | F | | 29 | wuyanzu | NULL | 19 | M | 4 | NULL | 1 | Song Jiang | 25 | M | | 29 | wuyanzu | NULL | 19 | M | 4 | NULL | 2 | Zhang Sanfeng | 25 | M | | 29 | wuyanzu | NULL | 19 | M | 4 | NULL | 3 | Miejue Shitai | 25 | F | | 29 | wuyanzu | NULL | 19 | M | 4 | NULL | 4 | Lin Chaoying | 25 | F | | 30 | wuzetian | NULL | 21 | F | NULL | NULL | 1 | Song Jiang | 25 | M | | 30 | wuzetian | NULL | 21 | F | NULL | NULL | 2 | Zhang Sanfeng | 25 | M | | 30 | wuzetian | NULL | 21 | F | NULL | NULL | 3 | Miejue Shitai | 25 | F | | 30 | wuzetian | NULL | 21 | F | NULL | NULL | 4 | Lin Chaoying | 25 | F | | 31 | Song Jiang | NULL | 18 | M | 45 | NULL | 1 | Song Jiang | 25 | M | | 31 | Song Jiang | NULL | 18 | M | 45 | NULL | 2 | Zhang Sanfeng | 25 | M | | 31 | Song Jiang | NULL | 18 | M | 45 | NULL | 3 | Miejue Shitai | 25 | F | | 31 | Song Jiang | NULL | 18 | M | 45 | NULL | 4 | Lin Chaoying | 25 | F | | 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL | 1 | Song Jiang | 25 | M | | 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL | 2 | Zhang Sanfeng | 25 | M | | 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL | 3 | Miejue Shitai | 25 | F | | 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL | 4 | Lin Chaoying | 25 | F | | 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL | 1 | Song Jiang | 25 | M | | 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL | 2 | Zhang Sanfeng | 25 | M | | 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL | 3 | Miejue Shitai | 25 | F | | 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL | 4 | Lin Chaoying | 25 | F | | 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL | 1 | Song Jiang | 25 | M | | 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL | 2 | Zhang Sanfeng | 25 | M | | 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL | 3 | Miejue Shitai | 25 | F | | 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL | 4 | Lin Chaoying | 25 | F | | 38 | abc | NULL | 20 | M | NULL | NULL | 1 | Song Jiang | 25 | M | | 38 | abc | NULL | 20 | M | NULL | NULL | 2 | Zhang Sanfeng | 25 | M | | 38 | abc | NULL | 20 | M | NULL | NULL | 3 | Miejue Shitai | 25 | F | | 38 | abc | NULL | 20 | M | NULL | NULL | 4 | Lin Chaoying | 25 | F | +-------+---------------+-------+-----+--------+---------+-----------+-----+---------------+-----+--------+ 140 rows in set (0.00 sec) 第一张表 0 1 2 3 1 2 3 4 第二张表 1 3 4 5 2 3 4 5 交叉连接后结果： 0 1 2 3 1 3 4 5 0 1 2 3 2 3 4 5 1 2 3 4 1 3 4 5 1 2 3 4 2 3 4 5 两张表换下位置不影响数据只是显示效果变了而已: 1 3 4 5 0 1 2 3 1 3 4 5 1 2 3 4 2 3 4 5 0 1 2 3 2 3 4 5 1 2 3 4 MariaDB [hellodb]> select * from teachers , s1; （这个命令也可以交叉连接但是比较老了推荐使用第一种） +-----+---------------+-----+--------+-------+---------------+-------+-----+--------+---------+-----------+ | TID | Name | Age | Gender | StuID | Name | phone | Age | Gender | ClassID | TeacherID | +-----+---------------+-----+--------+-------+---------------+-------+-----+--------+---------+-----------+ | 1 | Song Jiang | 25 | M | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | | 2 | Zhang Sanfeng | 25 | M | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | | 3 | Miejue Shitai | 25 | F | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | | 4 | Lin Chaoying | 25 | F | 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 | | 1 | Song Jiang | 25 | M | 2 | Shi Potian | NULL | 22 | M | 1 | 7 | | 2 | Zhang Sanfeng | 25 | M | 2 | Shi Potian | NULL | 22 | M | 1 | 7 | | 3 | Miejue Shitai | 25 | F | 2 | Shi Potian | NULL | 22 | M | 1 | 7 | | 4 | Lin Chaoying | 25 | F | 2 | Shi Potian | NULL | 22 | M | 1 | 7 | | 1 | Song Jiang | 25 | M | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | | 2 | Zhang Sanfeng | 25 | M | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | | 3 | Miejue Shitai | 25 | F | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 | | 4 | Lin Chaoying | 25 | F | 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
Wählen Sie einzelne Felder aus den beiden Tabellen aus.
MariaDB [hellodb]> select name,age,gender from teachers cross join s1; (这里有两个字段是重复的name，age两个表都有) ERROR 1052 (23000): Column 'name' in field list is ambiguous MariaDB [hellodb]> select stuid,s1.name,tid,teachers.name from teachers cross join s1; （分别指定是哪个表的name） +-------+---------------+-----+---------------+ | stuid | name | tid | name | +-------+---------------+-----+---------------+ | 1 | Shi Zhongyu | 1 | Song Jiang | | 1 | Shi Zhongyu | 2 | Zhang Sanfeng | | 1 | Shi Zhongyu | 3 | Miejue Shitai | | 1 | Shi Zhongyu | 4 | Lin Chaoying | | 2 | Shi Potian | 1 | Song Jiang | | 2 | Shi Potian | 2 | Zhang Sanfeng | | 2 | Shi Potian | 3 | Miejue Shitai | | 2 | Shi Potian | 4 | Lin Chaoying | | 3 | Xie Yanke | 1 | Song Jiang | | 3 | Xie Yanke | 2 | Zhang Sanfeng | | 3 | Xie Yanke | 3 | Miejue Shitai | | 3 | Xie Yanke | 4 | Lin Chaoying | | 4 | Ding Dian | 1 | Song Jiang | | 4 | Ding Dian | 2 | Zhang Sanfeng | | 4 | Ding Dian | 3 | Miejue Shitai | | 4 | Ding Dian | 4 | Lin Chaoying | | 5 | Yu Yutong | 1 | Song Jiang | | 5 | Yu Yutong | 2 | Zhang Sanfeng | | 5 | Yu Yutong | 3 | Miejue Shitai | | 5 | Yu Yutong | 4 | Lin Chaoying | | 6 | Shi Qing | 1 | Song Jiang | | 6 | Shi Qing | 2 | Zhang Sanfeng | | 6 | Shi Qing | 3 | Miejue Shitai | | 6 | Shi Qing | 4 | Lin Chaoying | | 7 | Xi Ren | 1 | Song Jiang | | 7 | Xi Ren | 2 | Zhang Sanfeng | | 7 | Xi Ren | 3 | Miejue Shitai | | 7 | Xi Ren | 4 | Lin Chaoying | | 8 | Lin Daiyu | 1 | Song Jiang | | 8 | Lin Daiyu | 2 | Zhang Sanfeng | | 8 | Lin Daiyu | 3 | Miejue Shitai | | 8 | Lin Daiyu | 4 | Lin Chaoying | | 9 | Ren Yingying | 1 | Song Jiang | | 9 | Ren Yingying | 2 | Zhang Sanfeng | | 9 | Ren Yingying | 3 | Miejue Shitai | | 9 | Ren Yingying | 4 | Lin Chaoying | | 10 | Yue Lingshan | 1 | Song Jiang | | 10 | Yue Lingshan | 2 | Zhang Sanfeng | | 10 | Yue Lingshan | 3 | Miejue Shitai | | 10 | Yue Lingshan | 4 | Lin Chaoying | | 11 | Yuan Chengzhi | 1 | Song Jiang | | 11 | Yuan Chengzhi | 2 | Zhang Sanfeng | | 11 | Yuan Chengzhi | 3 | Miejue Shitai | | 11 | Yuan Chengzhi | 4 | Lin Chaoying | （省略了太长） MariaDB [hellodb]> select stuid,s1.name,s1.age,tid,teachers.name,teachers.age from teachers cross join s1; （name和age都可以加在里面指定） +-------+---------------+-----+-----+---------------+-----+ | stuid | name | age | tid | name | age | +-------+---------------+-----+-----+---------------+-----+ | 1 | Shi Zhongyu | 22 | 1 | Song Jiang | 25 | | 1 | Shi Zhongyu | 22 | 2 | Zhang Sanfeng | 25 | | 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 25 | | 1 | Shi Zhongyu | 22 | 4 | Lin Chaoying | 25 | | 2 | Shi Potian | 22 | 1 | Song Jiang | 25 | | 2 | Shi Potian | 22 | 2 | Zhang Sanfeng | 25 | | 2 | Shi Potian | 22 | 3 | Miejue Shitai | 25 | | 2 | Shi Potian | 22 | 4 | Lin Chaoying | 25 | | 3 | Xie Yanke | 53 | 1 | Song Jiang | 25 | | 3 | Xie Yanke | 53 | 2 | Zhang Sanfeng | 25 | | 3 | Xie Yanke | 53 | 3 | Miejue Shitai | 25 | | 3 | Xie Yanke | 53 | 4 | Lin Chaoying | 25 | | 4 | Ding Dian | 32 | 1 | Song Jiang | 25 | | 4 | Ding Dian | 32 | 2 | Zhang Sanfeng | 25 | | 4 | Ding Dian | 32 | 3 | Miejue Shitai | 25 | | 4 | Ding Dian | 32 | 4 | Lin Chaoying | 25 | | 5 | Yu Yutong | 26 | 1 | Song Jiang | 25 | | 5 | Yu Yutong | 26 | 2 | Zhang Sanfeng | 25 | | 5 | Yu Yutong | 26 | 3 | Miejue Shitai | 25 | | 5 | Yu Yutong | 26 | 4 | Lin Chaoying | 25 | （省略了太长） MariaDB [hellodb]> select stuid,s1.name as s1_name,tid,teachers.name as teachers_name from teachers cross join s1; (也可以加上别名来区分比较清晰) +-------+---------------+-----+---------------+ | stuid | s1_name | tid | teachers_name | +-------+---------------+-----+---------------+ | 1 | Shi Zhongyu | 1 | Song Jiang | | 1 | Shi Zhongyu | 2 | Zhang Sanfeng | | 1 | Shi Zhongyu | 3 | Miejue Shitai | | 1 | Shi Zhongyu | 4 | Lin Chaoying | | 2 | Shi Potian | 1 | Song Jiang | | 2 | Shi Potian | 2 | Zhang Sanfeng | | 2 | Shi Potian | 3 | Miejue Shitai | | 2 | Shi Potian | 4 | Lin Chaoying | | 3 | Xie Yanke | 1 | Song Jiang | | 3 | Xie Yanke | 2 | Zhang Sanfeng | | 3 | Xie Yanke | 3 | Miejue Shitai | | 3 | Xie Yanke | 4 | Lin Chaoying | （省略）
- Versehen Sie die Tabelle mit einem Alias und verwenden Sie den Alias der Tabelle, um Daten abzufragen. (Der Alias wird am Ende des Felds definiert und davor verwendet und ist nur im aktuellen Feld wirksam.) Verbinde die Stellen, an denen sich die beiden Tabellen überschneiden
MariaDB [hellodb]> select stuid,s.name as s1_name,s.age,t.name as teachers_name,t.age from teachers t cross join s1 s; +-------+---------------+-----+---------------+-----+ | stuid | s1_name | age | teachers_name | age | +-------+---------------+-----+---------------+-----+ | 1 | Shi Zhongyu | 22 | Song Jiang | 25 | | 1 | Shi Zhongyu | 22 | Zhang Sanfeng | 25 | | 1 | Shi Zhongyu | 22 | Miejue Shitai | 25 | | 1 | Shi Zhongyu | 22 | Lin Chaoying | 25 | | 2 | Shi Potian | 22 | Song Jiang | 25 | | 2 | Shi Potian | 22 | Zhang Sanfeng | 25 | | 2 | Shi Potian | 22 | Miejue Shitai | 25 | | 2 | Shi Potian | 22 | Lin Chaoying | 25 | | 3 | Xie Yanke | 53 | Song Jiang | 25 | | 3 | Xie Yanke | 53 | Zhang Sanfeng | 25 | | 3 | Xie Yanke | 53 | Miejue Shitai | 25 | | 3 | Xie Yanke | 53 | Lin Chaoying | 25 | | 4 | Ding Dian | 32 | Song Jiang | 25 | | 4 | Ding Dian | 32 | Zhang Sanfeng | 25 | | 4 | Ding Dian | 32 | Miejue Shitai | 25 | | 4 | Ding Dian | 32 | Lin Chaoying | 25 | MariaDB [hellodb]> select stuid,s1.name s1_name,s1.age,tid,t.name teacher_name,teachers.age from teachers t cross join s1; （别名定义之后不能使用原始名字） ERROR 1054 (42S22): Unknown column 'teachers.age' in 'field list'
Filter nach Inner Join: Zuerst verbinden und dann filtern, um die Personen anzuzeigen, deren s1-Tabelle größer als 30 ist. 等值连接：让表之间的字段以“等值”建立连接关系
Nach Abfrage filtern
MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid; （三个个老师各教一个学生） +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M | | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F | +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ 3 rows in set (0.00 sec) MariaDB [hellodb]> update s1 set teacherid=1 where stuid=25; （修改一下s1表的teacherid的值为1） Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid; （再次使用这条命令，查看就是songjiang教两个学生） +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M | | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M | | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F | +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ 4 rows in set (0.00 sec) MariaDB [hellodb]> select * from s1 , t1 where s1.teacherid=t1.tid; (不加 inner join 的老写法) +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M | | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M | | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F | +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ 4 rows in set (0.01 sec)Äußerer Join
Äußerer Join:
Links Äußerer Join: FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col (ganz vorne)Rechter äußerer Join: FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col (ganz hinten) Linker äußerer Join, linker äußerer Link
Die gesamte Schülertabelle bleibt erhalten, nur die des Lehrers bleibt dort übrig, wo es eine Kreuzung gibt. (Durch Nullwerte ersetzen, wenn es keinen Schnittpunkt gibt)MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid and s1.age >30; +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+ | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+ | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F | +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+ 2 rows in set (0.00 sec)Nutzung der Erweiterung des linken äußeren Joins
MariaDB [hellodb]> select * from s1 inner join t1 on s1.teacherid=t1.tid where s1.a +-------+-------------+-----+--------+---------+-----------+-----+--------------+-- | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | A +-------+-------------+-----+--------+---------+-----------+-----+--------------+-- | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | +-------+-------------+-----+--------+---------+-----------+-----+--------------+-- 2 rows in set (0.00 sec)
Rechter äußerer Link: rechter äußerer
- Verlassen Sie den gesamten Lehrertisch und den Schnittpunkt des Schülertisches.
MariaDB [hellodb]> select * from s1 left 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 | 7 | NULL | NULL | NULL | NULL | | 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F | | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M | | 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M | +-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+ 25 rows in set (0.00 sec)
Nutzung der Erweiterung des rechten äußeren Joins
MariaDB [hellodb]> select * from t1; （教师表） +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec) MariaDB [hellodb]> select * from s1 left outer join t1 on s1.teacherid=t1.tid where tid is null; （前面两个，老师表里没有这两个老师的编号） +-------+---------------+-----+--------+---------+-----------+------+------+------+--------+ | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +-------+---------------+-----+--------+---------+-----------+------+------+------+--------+ | 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL | | 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL | | 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+------+------+------+--------+ 21 rows in set (0.00 sec)
Vollständiger Outer Join
MariaDB [hellodb]> 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 | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F | | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M | | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M | | NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M | +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+ 5 rows in set (0.00 sec)
- Verwenden Sie Union für den Left Outer Join und den Right Outer Join Zusammengenommen entsprechen die Orte, an denen es einen Schnittpunkt gibt, und die Orte, an denen es keinen Schnittpunkt gibt, werden ebenfalls ausgegeben.
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 | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M | +-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+ 1 row in set (0.00 sec)
Vollständige Verwendung der Outer-Join-Erweiterung: Entfernen Sie die Schnittpunkte und lassen Sie nur Outer-Joins übrig.
和交叉连接不一样 mysql不支持full outer join
Drei-Tabellen-Abfrage
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 | 7 | NULL | NULL | NULL | NULL | | 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F | | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M | | 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M | | NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M | +-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+ 26 rows in set (0.00 sec)
MariaDB [hellodb]> select * from (select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from s1 s left outer join t1 t on s.teacherid=t.tid union select s.stuid,s.name,s.teacherid,t.tid,t.name from s1 s right outer join t1 t on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null; +-------+---------------+-----------+------+---------------+ | stuid | s_name | teacherid | tid | t_name | +-------+---------------+-----------+------+---------------+ | 2 | Shi Potian | 7 | NULL | NULL | | 3 | Xie Yanke | 16 | NULL | NULL | | 6 | Shi Qing | NULL | NULL | NULL | | 7 | Xi Ren | NULL | NULL | NULL | | 8 | Lin Daiyu | NULL | NULL | NULL | | 9 | Ren Yingying | NULL | NULL | NULL | | 10 | Yue Lingshan | NULL | NULL | NULL | | 11 | Yuan Chengzhi | NULL | NULL | NULL | | 12 | Wen Qingqing | NULL | NULL | NULL | | 13 | Tian Boguang | NULL | NULL | NULL | | 14 | Lu Wushuang | NULL | NULL | NULL | | 15 | Duan Yu | NULL | NULL | NULL | | 16 | Xu Zhu | NULL | NULL | NULL | | 17 | Lin Chong | NULL | NULL | NULL | | 18 | Hua Rong | NULL | NULL | NULL | | 19 | Xue Baochai | NULL | NULL | NULL | | 20 | Diao Chan | NULL | NULL | NULL | | 21 | Huang Yueying | NULL | NULL | NULL | | 22 | Xiao Qiao | NULL | NULL | NULL | | 23 | Ma Chao | NULL | NULL | NULL | | 24 | Xu Xian | NULL | NULL | NULL | | NULL | NULL | NULL | 2 | Zhang Sanfeng | +-------+---------------+-----------+------+---------------+ 22 rows in set (0.00 sec)Erhalten Sie den Namen, die Noten und die Fächer des Schülers in einer Verbindung.
取学生姓名，成绩，科目。rrreeSelbstverbindung
自联结顾名思义就是把一张表假设为两张一样的表，然后在做“多表查询”
先构建一张表
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(1,'huangshang',null); Query OK, 1 row affected (0.00 sec) MariaDB [hellodb]> insert emp value(2,'taihou','huangshang'); ERROR 1366 (22007): Incorrect integer value: 'huangshang' for column `hellodb`.`emp`.`leaderid` at row 1 MariaDB [hellodb]> insert emp value(2,'taihou',1); Query OK, 1 row affected (0.00 sec) MariaDB [hellodb]> insert emp value(3,'guifei',2); Query OK, 1 row affected (0.00 sec) MariaDB [hellodb]> insert emp value(4,'shufei',3); 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)
查询表里的上级的姓名。
- 想象为两张表 员工表 和上司表 起别名做成
MariaDB [hellodb]> select * from emp as e inner join emp as l on e.leaderid=l.id; +------+--------+----------+------+------------+----------+ | id | name | leaderid | id | name | leaderid | +------+--------+----------+------+------------+----------+ | 2 | taihou | 1 | 1 | huangshang | NULL | | 3 | guifei | 2 | 2 | taihou | 1 | | 4 | shufei | 3 | 3 | guifei | 2 | +------+--------+----------+------+------------+----------+ 3 rows in set (0.00 sec)
- 取出来对应的上级，但是缺失了最上级。
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)
- 取出每个人对应的上级的id。
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)
SQL语句的关键字执行顺序
推荐学习：MySQL教程
Das obige ist der detaillierte Inhalt vonEine kurze Diskussion über Multi-Table-Abfragen in MySQL-Datenbanken. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
Stellungnahme：
Dieser Artikel ist reproduziert unter:cnblogs.com. Bei Verstößen wenden Sie sich bitte an admin@php.cn löschen
Vorheriger Artikel：MySQL kopiert Tabellenstruktur und TabellendatenNächster Artikel：MySQL kopiert Tabellenstruktur und Tabellendaten