MySQL 데이터베이스의 다중 테이블 쿼리에 대한 간략한 토론
- 青灯夜游앞으로
- 2019-11-29 15:47:493118검색
이 글에서는 MySQL 데이터베이스의 다중 테이블 쿼리에 대해 알아봅니다. 도움이 필요한 친구들이 모두 참고할 수 있기를 바랍니다.
다중 테이블 쿼리
쿼리 결과가 여러 테이블, 즉 다중 테이블 쿼리
하위 쿼리: 쿼리 문이 SQL 문에 중첩되어 성능이 저하됩니다. 특정 문을 기반으로 한 쿼리 결과 다시 쿼리
Union 쿼리: UNION
Cross Join: Cartesian product
Inner Join:
Equijoin: 테이블 사이의 필드가 "같은 값"으로 연결 관계를 설정하도록 합니다.
Unequal Join: Unequal Join 쿼리는 무조건 판단. 여러 테이블의 데이터를 쿼리하면 데이터가 서로 연관되지 않고 자체 정보를 표시합니다.
자연 조인: 중복 열 제거 및 등가 조인
외부 조인:
왼쪽 외부 조인: FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
오른쪽 외부 조인: FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2. col
Self-join: 이 테이블과 이 테이블 사이에 조인 쿼리를 수행합니다
Subquery
WHERE 절에서 일반적으로 사용되는 하위 쿼리
1 비교 표현식에 사용되는 하위 쿼리는 단일 값만 가능합니다. 반환됨(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. 쿼리 결과는 다른 테이블에 포함되고 소수점 이하 정수로의 변환은 반올림됩니다.
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)
다중 테이블 쿼리:
하위 루프 사용 s1 테이블을 보려면 Teacher 테이블의 나이가 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)
Union query
union은 두 테이블을 수직으로 병합하며, 테이블 헤더는 첫 번째 쿼리 레코드에서 나옵니다.
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은 자신을 자신에게 연결하여 중복을 제거할 수 있습니다.
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)
모두를 사용하여 중복 제거 없이 두 테이블을 간단히 조인
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）
두 테이블 간에 교차 조인을 사용한다는 것은 이 테이블의 각 행이 다른 테이블의 모든 행과 결합된다는 의미입니다. 열.
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 |
두 테이블의 개별 필드를 선택하세요.
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 | （省略）
- 테이블에 별칭을 지정하고 해당 테이블의 별칭을 사용하여 데이터를 쿼리합니다. (별칭은 필드 끝에서 정의되어 앞에 사용되며 현재 필드에만 적용됩니다.)
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'
Inner Join
等值连接：让表之间的字段以“等值”建立连接关系
두 테이블의 교차점 연결
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)
- 내부 조인 후 필터링: 먼저 연결한 다음 필터링하여 s1 테이블이 30보다 큰 사람을 표시합니다.
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)
쿼리 후 필터링
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)
외부 조인
외부 조인:
왼쪽 외부 조인: FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col (위)
오른쪽 외부 연결: FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col (뒤에 순위)
left Outer Join left Outer
학생 테이블 전체는 유지되고, 선생님 테이블은 교차점이 있는 곳만 유지됩니다. (교차점이 없는 경우 Null값 대체)
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)
왼쪽 외부 조인 사용 확대
- 교사가 가르치지 않는 학생 제거(어디로 지정)
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)
오른쪽 외부 링크: right external
선생님 테이블 전체가 남고, 학생 테이블이 교차하는 곳도 남습니다.
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)
오른쪽 외부 조인 확장 사용법
- 학생을 가르치지 않는 교사는 남습니다. (왼쪽 외부 조인과 논리는 동일)
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)
완전한 외부 조인
和交叉连接不一样 mysql不支持full outer join
Union을 사용하여 왼쪽 외부 조인과 오른쪽 외부 조인을 결합하면 교차점이 있는 곳이 서로 대응됩니다. 교차점이 없는 곳도 출력됩니다.
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)
3개의 테이블 쿼리
取学生姓名，成绩，科目。
먼저 학생의 이름과 성적을 꺼내세요. (더 명확한 아이디어를 위해 두 단계로 진행하세요.)
MariaDB [hellodb]> select st.name,sc.courseid,sc.score from s1 st inner join scores sc on st.stuid=sc.stuid; +-------------+----------+-------+ | name | courseid | score | +-------------+----------+-------+ | Shi Zhongyu | 2 | 77 | | Shi Zhongyu | 6 | 93 | | Shi Potian | 2 | 47 | | Shi Potian | 5 | 97 | | Xie Yanke | 2 | 88 | | Xie Yanke | 6 | 75 | | Ding Dian | 5 | 71 | | Ding Dian | 2 | 89 | | Yu Yutong | 1 | 39 | | Yu Yutong | 7 | 63 | | Shi Qing | 1 | 96 | | Xi Ren | 1 | 86 | | Xi Ren | 7 | 83 | | Lin Daiyu | 4 | 57 | | Lin Daiyu | 3 | 93 | +-------------+----------+-------+ 15 rows in set (0.00 sec)
학생의 이름, 성적, 과목을 한 번에 가져옵니다.
rrreerrree자체 연결
自联结顾名思义就是把一张表假设为两张一样的表，然后在做“多表查询”
先构建一张表
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教程
위 내용은 MySQL 데이터베이스의 다중 테이블 쿼리에 대한 간략한 토론의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
성명：
이 기사는 cnblogs.com에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제