Maison >base de données >tutoriel mysql >Une brève discussion sur les requêtes multi-tables dans la base de données MySQL
Une brève discussion sur les requêtes multi-tables dans la base de données MySQL
- 青灯夜游avant
- 2019-11-29 15:47:493197parcourir
Dans cet article, nous découvrirons les requêtes multi-tables dans la base de données MySQL. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer. J'espère qu'il sera utile à tout le monde.
Requête multi-tables
Les résultats de la requête proviennent de plusieurs tables, c'est-à-dire multi- requête de table
Sous-requête : les instructions de requête sont imbriquées dans des instructions SQL, avec des performances médiocres. Requête basée sur les résultats de la requête d'une certaine instruction
Requête Union : UNION
Jointure croisée : cartésienne. product
Jointure interne :
Jointure équivalente : laissez les champs entre les tables établir une relation de connexion avec « équivalence »
Jointure inégale : la requête de jointure inégale est un jugement inconditionnel si vous interrogez les champs dans plusieurs tables Data. , les données qu'il contient ne seront pas synchronisées, chacune affichera les siennes, sans aucune corrélation.
Jointure naturelle : supprime les colonnes en double et la jointure égale
Jointure externe :
Jointure externe gauche : FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
Jointure externe droite : FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
Auto-jointure : effectuer une requête de connexion entre cette table et cette table
Sous-requête
Sous-requêtes couramment utilisées dans les clauses WHERE
1 Utilisées pour comparer les sous-requêtes dans les expressions ; les sous-requêtes ne peuvent renvoyer qu'une seule valeur (requête pour les personnes plus âgées que l'âge moyen de la table s1)
.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. Les résultats de la requête sont intégrés dans une autre table et la conversion décimale en entier sera arrondie
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)
Requête multi-table :
Utilisez une sous-boucle pour afficher le table s1 et afficher la table des enseignants Informations sur les personnes dont l'âge est supérieur à l'âge moyen de la table s1.
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)
Requête Union
l'union fusionne deux tables verticalement et l'en-tête de la table provient du premier enregistrement de requête.
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 se connecte à elle-même pour supprimer les doublons.
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)
Utiliser tout pour joindre simplement deux tables sans déduplication
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)
Jointure croisée
两张表横向组合，类似于笛卡尔乘积。 （cross join）
Utiliser le croisement entre deux tables A join signifie que chaque ligne de cette table est combinée avec toutes les lignes d'une autre table pour former une nouvelle ligne.
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 |
Sélectionnez des champs individuels dans les deux tables.
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 | （省略）
- Alias la table et utilisez l'alias de la table pour interroger les données. (L'alias est défini à la fin du champ et est utilisé avant celui-ci, et il ne prend effet que dans le champ courant) Joignez les endroits où les deux tables se croisent
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'
Filtre après jointure interne : connectez-vous d'abord puis filtrez, affichez les personnes dont la table s1 est supérieure à 30. 等值连接：让表之间的字段以“等值”建立连接关系
Filtre après requête
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)Jointure externe
Jointure externe :
Gauche jointure externe : FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col (classé à l'avant)Jointure externe droite : FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col (classé à l'arrière) Rejoindre l'extérieur gauche à l'extérieur gauche
La table entière des étudiants est conservée, seule celle du professeur est laissée là où il y a une intersection. (Remplacer par des valeurs nulles là où il n'y a pas d'intersection)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)Utilisation de l'expansion de la jointure externe gauche
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)
Lien extérieur droit : extérieur droit
- Quitter toutes les tables des professeurs et l'intersection des tables des élèves.
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)
Utilisation de l'expansion de la jointure externe droite
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)
Jointure externe complète
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)
- Utiliser l'union pour la jointure externe gauche et la jointure externe droite Combinés, les endroits où il y a une intersection correspondent et les endroits où il n'y a pas d'intersection sont également affichés.
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)
Utilisation complète de l'expansion des jointures externes : supprimez les intersections, ne laissant que les jointures externes.
和交叉连接不一样 mysql不支持full outer join
Requête à trois tables
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)Obtenez le nom, les notes et les matières de l'élève en une seule connexion.
取学生姓名，成绩，科目。rrreeAuto-connexion
自联结顾名思义就是把一张表假设为两张一样的表，然后在做“多表查询”
先构建一张表
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教程
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!
Déclaration：
Cet article est reproduit dans:. en cas de violation, veuillez contacter admin@php.cn Supprimer
Article précédent：MySQL copie la structure de la table et les données de la tableArticle suivant：MySQL copie la structure de la table et les données de la table
Articles Liés
Voir plus- Une brève discussion sur les deux méthodes et différences de suppression des données de table dans MySQL
- Explication détaillée des transactions de la base de données MySQL et du moteur de stockage
- Solution au code tronqué lorsque Java insère des données dans MySQL
- MySQL copie la structure de la table et les données de la table