浅谈MySQL数据库多表查询

本篇文章和大家一起了解一下MySQL数据库多表查询。有一定的参考价值，有需要的朋友可以参考一下，希望对大家有所帮助。

多表查询

查询结果来自于多张表，即多表查询

子查询：在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)

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: 0MariaDB [hellodb]> select * from teachers;+-----+---------------+-----+--------+| TID | Name          | Age | Gender |+-----+---------------+-----+--------+|   1 | Song Jiang    |  25 | M      ||   2 | Zhang Sanfeng |  25 | M      ||   3 | Miejue Shitai |<p style="color:transparent">本文来源gao!daima.com搞$代!码网</p>  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: 0MariaDB [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: 0MariaDB [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)

喜欢 (0)赏

[搞代码]

分享 (0)