MySQL数据库多表查询

时间：2021-10-15 04:35:39

 

目录

MySQL数据库多表查询

多表查询

  1. 查询结果来自于多张表，即多表查询
子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
联合查询：UNION   
交叉连接：笛卡尔乘积   
内连接：
       等值连接：让表之间的字段以“等值”建立连接关系  
       不等值连接:不等值连接查询就是无条件判断，若查询多个表内的数据，其中的数据不会同步，各自把各自的展现出来，没有任何关联。
       自然连接:去掉重复列的等值连接   
外连接：
   左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col 右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col 自连接：本表和本表进行连接查询

子查询

常用在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)
  1. 查询结果嵌入到另一个表里,小数转换整数会四舍五入
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)
  1. 多表查询:
    用子循环查看s1表，显示teachers表年龄大于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)

联合查询

  1. 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 |

相关文章