mariadb 数据查询：多表内外连接查询
一，数据库的连接查询类型
1，多表查询
1）cross join 交叉连接，即笛卡尔连接（无实际意义）
1 MariaDB [hellodb]> select * from students cross join teachers ;
2）内连接：两个表的相关字段满足连接条件，就从这两个表中提取数据并组合成新的记录
分类：
>等值连接 连接条件中使用等于号(=)运算符比较被连接列的列值
>不等连接 连接条件使用 >、>=、<=、<、!>、!<和<> 运算符
> 自然连接 不需要使用运算符匹配条件，隐含了某一列名称相同 的条件，可自动清除重复行
MariaDB [hellodb]> select * from students as s inner join teachers as t on s.teacherid = t.tid ;
3） 自连接 ：一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表
如某一员工表包含所属上级领导列， 既是员工又是领导的情况。
真实含义为：从s2表中拿出第一行，根据指定条件分别与s1表的每一行向比对，如果条件满足，就附加到该行之后，依次对比。
对比时需要改变表名为别名，不然报错。
1 MariaDB [hellodb]> select * from students as s1 join students as s2 on s1.classid=s2.classid ; 2 +-------+---------------+-----+--------+---------+-----------+-------+---------------+-----+--------+---------+-----------+ 3 | StuID | Name | Age | Gender | ClassID | TeacherID | StuID | Name | Age | Gender | ClassID | TeacherID | 4 +-------+---------------+-----+--------+---------+-----------+-------+---------------+-----+--------+---------+-----------+ 5 | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 6 | 3 | Xie Yanke | 53 | M | 2 | 16 | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 7 | 13 | Tian Boguang | 33 | M | 2 | NULL | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 8 | 2 | Shi Potian | 22 | M | 1 | 7 | 2 | Shi Potian | 22 | M | 1 | 7 | 9 | 12 | Wen Qingqing | 19 | F | 1 | NULL | 2 | Shi Potian | 22 | M | 1 | 7 | 10 | 16 | Xu Zhu | 21 | M | 1 | NULL | 2 | Shi Potian | 22 | M | 1 | 7 | 11 | 22 | Xiao Qiao | 20 | F | 1 | NULL | 2 | Shi Potian | 22 | M | 1 | 7 | 12 | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Xie Yanke | 53 | M | 2 | 16 | 13 | 3 | Xie Yanke | 53 | M | 2 | 16 | 3 | Xie Yanke | 53 | M | 2 | 16 | 14 | 13 | Tian Boguang | 33 | M | 2 | NULL | 3 | Xie Yanke | 53 | M | 2 | 16 | 15 | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Ding Dian | 32 | M | 4 | 4 | 16 | 15 | Duan Yu | 19 | M | 4 | NULL | 4 | Ding Dian | 32 | M | 4 | 4 | 17 | 17 | Lin Chong | 25 | M | 4 | NULL | 4 | Ding Dian | 32 | M | 4 | 4 | 18 | 23 | Ma Chao | 23 | M | 4 | NULL | 4 | Ding Dian | 32 | M | 4 | 4 | 19 | 5 | Yu Yutong | 26 | M | 3 | 1 | 5 | Yu Yutong | 26 | M | 3 | 1 | 20 | 7 | Xi Ren | 19 | F | 3 | NULL | 5 | Yu Yutong | 26 | M | 3 | 1 | 21 | 10 | Yue Lingshan | 19 | F | 3 | NULL | 5 | Yu Yutong | 26 | M | 3 | 1 | 22 | 14 | Lu Wushuang | 17 | F | 3 | NULL | 5 | Yu Yutong | 26 | M | 3 | 1 | 23 | 6 | Shi Qing | 46 | M | 5 | NULL | 6 | Shi Qing | 46 | M | 5 | NULL | 24 | 5 | Yu Yutong | 26 | M | 3 | 1 | 7 | Xi Ren | 19 | F | 3 | NULL | 25 | 7 | Xi Ren | 19 | F | 3 | NULL | 7 | Xi Ren | 19 | F | 3 | NULL | 26 | 10 | Yue Lingshan | 19 | F | 3 | NULL | 7 | Xi Ren | 19 | F | 3 | NULL | 27 | 14 | Lu Wushuang | 17 | F | 3 | NULL | 7 | Xi Ren | 19 | F | 3 | NULL | 28 | 8 | Lin Daiyu | 17 | F | 7 | NULL | 8 | Lin Daiyu | 17 | F | 7 | NULL | 29 | 18 | Hua Rong | 23 | M | 7 | NULL | 8 | Lin Daiyu | 17 | F | 7 | NULL | 30 | 20 | Diao Chan | 19 | F | 7 | NULL | 8 | Lin Daiyu | 17 | F | 7 | NULL | 31 | 9 | Ren Yingying | 20 | F | 6 | NULL | 9 | Ren Yingying | 20 | F | 6 | NULL | 32 | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 9 | Ren Yingying | 20 | F | 6 | NULL | 33 | 19 | Xue Baochai | 18 | F | 6 | NULL | 9 | Ren Yingying | 20 | F | 6 | NULL | 34 | 21 | Huang Yueying | 22 | F | 6 | NULL | 9 | Ren Yingying | 20 | F | 6 | NULL | 35 | 5 | Yu Yutong | 26 | M | 3 | 1 | 10 | Yue Lingshan | 19 | F | 3 | NULL | 36 | 7 | Xi Ren | 19 | F | 3 | NULL | 10 | Yue Lingshan | 19 | F | 3 | NULL | 37 | 10 | Yue Lingshan | 19 | F | 3 | NULL | 10 | Yue Lingshan | 19 | F | 3 | NULL | 38 | 14 | Lu Wushuang | 17 | F | 3 | NULL | 10 | Yue Lingshan | 19 | F | 3 | NULL | 39 | 9 | Ren Yingying | 20 | F | 6 | NULL | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 40 | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 41 | 19 | Xue Baochai | 18 | F | 6 | NULL | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 42 | 21 | Huang Yueying | 22 | F | 6 | NULL | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 43 | 2 | Shi Potian | 22 | M | 1 | 7 | 12 | Wen Qingqing | 19 | F | 1 | NULL | 44 | 12 | Wen Qingqing | 19 | F | 1 | NULL | 12 | Wen Qingqing | 19 | F | 1 | NULL | 45 | 16 | Xu Zhu | 21 | M | 1 | NULL | 12 | Wen Qingqing | 19 | F | 1 | NULL | 46 | 22 | Xiao Qiao | 20 | F | 1 | NULL | 12 | Wen Qingqing | 19 | F | 1 | NULL | 47 | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 13 | Tian Boguang | 33 | M | 2 | NULL | 48 | 3 | Xie Yanke | 53 | M | 2 | 16 | 13 | Tian Boguang | 33 | M | 2 | NULL | 49 | 13 | Tian Boguang | 33 | M | 2 | NULL | 13 | Tian Boguang | 33 | M | 2 | NULL | 50 | 5 | Yu Yutong | 26 | M | 3 | 1 | 14 | Lu Wushuang | 17 | F | 3 | NULL | 51 | 7 | Xi Ren | 19 | F | 3 | NULL | 14 | Lu Wushuang | 17 | F | 3 | NULL | 52 | 10 | Yue Lingshan | 19 | F | 3 | NULL | 14 | Lu Wushuang | 17 | F | 3 | NULL | 53 | 14 | Lu Wushuang | 17 | F | 3 | NULL | 14 | Lu Wushuang | 17 | F | 3 | NULL | 54 | 4 | Ding Dian | 32 | M | 4 | 4 | 15 | Duan Yu | 19 | M | 4 | NULL | 55 | 15 | Duan Yu | 19 | M | 4 | NULL | 15 | Duan Yu | 19 | M | 4 | NULL | 56 | 17 | Lin Chong | 25 | M | 4 | NULL | 15 | Duan Yu | 19 | M | 4 | NULL | 57 | 23 | Ma Chao | 23 | M | 4 | NULL | 15 | Duan Yu | 19 | M | 4 | NULL | 58 | 2 | Shi Potian | 22 | M | 1 | 7 | 16 | Xu Zhu | 21 | M | 1 | NULL | 59 | 12 | Wen Qingqing | 19 | F | 1 | NULL | 16 | Xu Zhu | 21 | M | 1 | NULL | 60 | 16 | Xu Zhu | 21 | M | 1 | NULL | 16 | Xu Zhu | 21 | M | 1 | NULL | 61 | 22 | Xiao Qiao | 20 | F | 1 | NULL | 16 | Xu Zhu | 21 | M | 1 | NULL | 62 | 4 | Ding Dian | 32 | M | 4 | 4 | 17 | Lin Chong | 25 | M | 4 | NULL | 63 | 15 | Duan Yu | 19 | M | 4 | NULL | 17 | Lin Chong | 25 | M | 4 | NULL | 64 | 17 | Lin Chong | 25 | M | 4 | NULL | 17 | Lin Chong | 25 | M | 4 | NULL | 65 | 23 | Ma Chao | 23 | M | 4 | NULL | 17 | Lin Chong | 25 | M | 4 | NULL | 66 | 8 | Lin Daiyu | 17 | F | 7 | NULL | 18 | Hua Rong | 23 | M | 7 | NULL | 67 | 18 | Hua Rong | 23 | M | 7 | NULL | 18 | Hua Rong | 23 | M | 7 | NULL | 68 | 20 | Diao Chan | 19 | F | 7 | NULL | 18 | Hua Rong | 23 | M | 7 | NULL | 69 | 9 | Ren Yingying | 20 | F | 6 | NULL | 19 | Xue Baochai | 18 | F | 6 | NULL | 70 | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 19 | Xue Baochai | 18 | F | 6 | NULL | 71 | 19 | Xue Baochai | 18 | F | 6 | NULL | 19 | Xue Baochai | 18 | F | 6 | NULL | 72 | 21 | Huang Yueying | 22 | F | 6 | NULL | 19 | Xue Baochai | 18 | F | 6 | NULL | 73 | 8 | Lin Daiyu | 17 | F | 7 | NULL | 20 | Diao Chan | 19 | F | 7 | NULL | 74 | 18 | Hua Rong | 23 | M | 7 | NULL | 20 | Diao Chan | 19 | F | 7 | NULL | 75 | 20 | Diao Chan | 19 | F | 7 | NULL | 20 | Diao Chan | 19 | F | 7 | NULL | 76 | 9 | Ren Yingying | 20 | F | 6 | NULL | 21 | Huang Yueying | 22 | F | 6 | NULL | 77 | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 21 | Huang Yueying | 22 | F | 6 | NULL | 78 | 19 | Xue Baochai | 18 | F | 6 | NULL | 21 | Huang Yueying | 22 | F | 6 | NULL | 79 | 21 | Huang Yueying | 22 | F | 6 | NULL | 21 | Huang Yueying | 22 | F | 6 | NULL | 80 | 2 | Shi Potian | 22 | M | 1 | 7 | 22 | Xiao Qiao | 20 | F | 1 | NULL | 81 | 12 | Wen Qingqing | 19 | F | 1 | NULL | 22 | Xiao Qiao | 20 | F | 1 | NULL | 82 | 16 | Xu Zhu | 21 | M | 1 | NULL | 22 | Xiao Qiao | 20 | F | 1 | NULL | 83 | 22 | Xiao Qiao | 20 | F | 1 | NULL | 22 | Xiao Qiao | 20 | F | 1 | NULL | 84 | 4 | Ding Dian | 32 | M | 4 | 4 | 23 | Ma Chao | 23 | M | 4 | NULL | 85 | 15 | Duan Yu | 19 | M | 4 | NULL | 23 | Ma Chao | 23 | M | 4 | NULL | 86 | 17 | Lin Chong | 25 | M | 4 | NULL | 23 | Ma Chao | 23 | M | 4 | NULL | 87 | 23 | Ma Chao | 23 | M | 4 | NULL | 23 | Ma Chao | 23 | M | 4 | NULL | 88 +-------+---------------+-----+--------+---------+-----------+-------+---------------+-----+--------+---------+-----------+ 89 83 rows in set (0.00 sec
4）外连接
需要有条件判定即where限定，数据表之间有相关字段关联
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
5） 联合查询 union
查询的表为不同的字段，然后统一输出，先后顺序与查询的表先后顺序有关。
1 MariaDB [hellodb]> select name,age,gender from teachers union select name,age,gender from students ; 2 +---------------+-----+--------+ 3 | name | age | gender | 4 +---------------+-----+--------+ 5 | Song Jiang | 45 | M | 6 | Zhang Sanfeng | 94 | M | 7 | Miejue Shitai | 77 | F | 8 | Lin Chaoying | 93 | F | 9 | Shi Zhongyu | 22 | M | 10 | Shi Potian | 22 | M | 11 | Xie Yanke | 53 | M | 12 | Ding Dian | 32 | M | 13 | Yu Yutong | 26 | M | 14 | Shi Qing | 46 | M | 15 | Xi Ren | 19 | F | 16 | Lin Daiyu | 17 | F | 17 | Ren Yingying | 20 | F | 18 | Yue Lingshan | 19 | F | 19 | Yuan Chengzhi | 23 | M | 20 | Wen Qingqing | 19 | F | 21 | Tian Boguang | 33 | M | 22 | Lu Wushuang | 17 | F | 23 | Duan Yu | 19 | M | 24 | Xu Zhu | 21 | M | 25 | Lin Chong | 25 | M | 26 | Hua Rong | 23 | M | 27 | Xue Baochai | 18 | F | 28 | Diao Chan | 19 | F | 29 | Huang Yueying | 22 | F | 30 | Xiao Qiao | 20 | F | 31 | Ma Chao | 23 | M | 32 | Xu Xian | 27 | M | 33 | Sun Dasheng | 100 | M | 34 +---------------+-----+--------+ 35 29 rows in set (0.00 sec)
浙公网安备 33010602011771号