MySQL多表查询

多表查询

MariaDB [test]> create table xx (a varchar(20),b varchar(20),c varchar(20));
Query OK, 0 rows affected (0.004 sec)

MariaDB [test]> desc xx;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | varchar(20) | YES  |     | NULL    |       |
| b     | varchar(20) | YES  |     | NULL    |       |
| c     | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

MariaDB [test]> select * from xx;
+------+------+------+
| a    | b    | c    |
+------+------+------+
| 1    | a    | ss   |
| 1    | a    | ss   |
| 1    | a    | ss   |
| 1    | a    | ss   |
| 1    | a    | ss   |
| 1    | b    | qq   |
| 1    | b    | qq   |
| 1    | b    | qq   |
| 1    | b    | qq   |
+------+------+------+
9 rows in set (0.000 sec)

以b列来进行分组(count groupby 的应用)
统计b这一列中a和b出现的次数

MariaDB [test]> select count(a),b from xx group by b;
+----------+------+
| count(a) | b    |
+----------+------+
|        5 | a    |
|        4 | b    |
+----------+------+
2 rows in set (0.001 sec)
统计c这一列中qq和ss出现的次数
MariaDB [test]> select count(a),c from xx group by c;
+----------+------+
| count(a) | c    |
+----------+------+
|        4 | qq   |
|        5 | ss   |
+----------+------+
2 rows in set (0.001 sec)

统计xx这个表中c这一列qq和ss出现的次数
MariaDB [test]> select c,count(c) from xx group by c;   //括号里的c可以是这个表里的其他数字或字母
+------+----------+
| c    | count(c) |
+------+----------+
| qq   |        4 |
| ss   |        5 |
+------+----------+
2 rows in set (0.000 sec)

求age这一列的平均值
MariaDB [lzx]> select avg(age) from student;
+----------+
| avg(age) |
+----------+
|  33.0000 |
+----------+
1 row in set (0.000 sec)

求age这一列的和
MariaDB [lzx]> select sum(age) from student;
+----------+
| sum(age) |
+----------+
|      297 |
+----------+
1 row in set (0.000 sec)

内连接

MariaDB [test]> select a.*,b.* from a inner join b on a.id=b.parent_id;
+------+----------+------+------+-----------+
| id   | name     | id   | job  | parent_id |
+------+----------+------+------+-----------+
|    1 | zhangsan |    1 |   23 |         1 |
|    2 | lisi     |    2 |   34 |         2 |
+------+----------+------+------+-----------+
2 rows in set (0.001 sec)

左连接
意思是a表和b的表的左连接中以a表为标准进行比较，显示b表与a表的相同和不同的地方有哪些

MariaDB [test]> select a.*,b.* from a left join b on a.id=b.parent_id;
+------+----------+------+------+-----------+
| id   | name     | id   | job  | parent_id |
+------+----------+------+------+-----------+
|    1 | zhangsan |    1 |   23 |         1 |
|    2 | lisi     |    2 |   34 |         2 |
|    3 | wangwu   | NULL | NULL |      NULL |
+------+----------+------+------+-----------+

右连接
意思是a表和b的表的左连接中以b表为标准进行比较，显示b表与a表的相同和不同的地方有哪些

MariaDB [test]> select a.*,b.* from a right join b on a.id=b.parent_id;
+------+----------+------+------+-----------+
| id   | name     | id   | job  | parent_id |
+------+----------+------+------+-----------+
|    1 | zhangsan |    1 |   23 |         1 |
|    2 | lisi     |    2 |   34 |         2 |
| NULL | NULL     |    3 |   34 |         4 |
+------+----------+------+------+-----------+
3 rows in set (0.000 sec)