mysql多表查询简单使用

最新推荐文章于 2022-11-12 15:33:31 发布

逐流·

最新推荐文章于 2022-11-12 15:33:31 发布

阅读量181

点赞数

分类专栏： linux 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_46115601/article/details/119935616

版权

linux 专栏收录该内容

71 篇文章 2 订阅

订阅专栏

文章目录

多表查询

创建张表

mysql> select * from biao1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
| 1    | a    | one  |
| 1    | a    | one  |
| 1    | a    | one  |
| 1    | a    | one  |
| 1    | b    | two  |
| 1    | b    | two  |
| 1    | b    | two  |
| 1    | b    | two  |
| 1    | b    | two  |
+------+------+------+
9 rows in set (0.00 sec)

计算b出现的次数

mysql> select count(a),b from biao1 group by b;
+----------+------+
| count(a) | b    |
+----------+------+
|        4 | a    |
|        5 | b    |
+----------+------+
2 rows in set (0.00 sec)

count里仅仅是个别称，但不能为空

mysql> select c,count(0) from biao1 group by c; ##相当于将c当成一个组去统计
+------+----------+
| c    | count(0) |
+------+----------+
| one  |        4 |
| two  |        5 |
+------+----------+

mysql> select * from zuoye ;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.00 sec)

mysql>  select age,count(0) from zuoye group by age;
+------+----------+
| age  | count(0) |
+------+----------+
|   10 |        1 |
|   15 |        1 |
|   20 |        2 |
|   23 |        1 |
|   25 |        1 |
|   26 |        1 |
|   28 |        1 |
|   50 |        1 |
|  100 |        1 |
+------+----------+
9 rows in set (0.00 sec)

算平均数

mysql> select avg(age) from zuoye;
+----------+
| avg(age) |
+----------+
|  31.7000 |
+----------+
1 row in set (0.01 sec)

算总和

mysql> select sum(age) from zuoye;
+----------+
| sum(age) |
+----------+
|      317 |
+----------+
1 row in set (0.00 sec)

mysql> select count(age) from zuoye;
+------------+
| count(age) |
+------------+
|         10 |
+------------+
1 row in set (0.00 sec)
统计age的个数，重复的不会列入其中，所以只有10个

多表查询

创建张表

mysql> select * from A;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tom   |
|    2 | jerry |
|    3 | smith |
+------+-------+
3 rows in set (0.00 sec)

mysql> select * from b;
ERROR 1146 (42S02): Table 'test1.b' doesn't exist
mysql> select * from B;
+------+------+---------+
| id   | job  | some_id |
+------+------+---------+
|    1 |   20 |       1 |
|    2 |   34 |       2 |
|    3 |   34 |       4 |
+------+------+---------+
3 rows in set (0.00 sec)

内连接

取出A,B两张表中，A表的id=B表的some-id的所有数据，也就是取交集，因为是内部已经存在的数据所以称为内连接

mysql> select A.*,B.* from  A join B on A.id = B.some_id;
+------+-------+------+------+---------+
| id   | name  | id   | job  | some_id |
+------+-------+------+------+---------+
|    1 | tom   |    1 |   20 |       1 |
|    2 | jerry |    2 |   34 |       2 |
+------+-------+------+------+---------+
2 rows in set (0.00 sec)
# join可以写为inner join 不影响输出

左连接

取出A,B两张表中，A表的id=B表的some-id的所有数据，left(左)即以左表A为主表匹配B与a条件相同的，不同的显示为null

mysql> select A.*,B.* from  A left join B on A.id = B.some_id;
+------+-------+------+------+---------+
| id   | name  | id   | job  | some_id |
+------+-------+------+------+---------+
|    1 | tom   |    1 |   20 |       1 |
|    2 | jerry |    2 |   34 |       2 |
|    3 | smith | NULL | NULL |    NULL |
+------+-------+------+------+---------+
3 rows in set (0.01 sec)

右连接

mysql> select A.*,B.* from  A right join B on A.id = B.some_id;
+------+-------+------+------+---------+
| id   | name  | id   | job  | some_id |
+------+-------+------+------+---------+
|    1 | tom   |    1 |   20 |       1 |
|    2 | jerry |    2 |   34 |       2 |
| NULL | NULL  |    3 |   34 |       4 |
+------+-------+------+------+---------+
3 rows in set (0.00 sec)
右表数据不变，左表做补充

完全连接

将两表连在一起查

mysql> select A.*,B.* from  A left join B on A.id = B.some_id union select A.*,B.* from  A right join B on A.id = B.some_id;
+------+-------+------+------+---------+
| id   | name  | id   | job  | some_id |
+------+-------+------+------+---------+
|    1 | tom   |    1 |   20 |       1 |
|    2 | jerry |    2 |   34 |       2 |
|    3 | smith | NULL | NULL |    NULL |
| NULL | NULL  |    3 |   34 |       4 |
+------+-------+------+------+---------+
4 rows in set (0.00 sec)

相当于用union将左右两种查询语句连在一起查，因为mysql不支持full join