L
L
LearnJava
Search…
mysql常用语法

1. group by

GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
比如:
MariaDB [test]> select * from class;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | a | 80 |
| 2 | a | 70 |
| 3 | a | 85 |
| 4 | b | 85 |
| 5 | b | 80 |
| 6 | b | 90 |
| 7 | c | 90 |
| 8 | c | 90 |
| 9 | c | 100 |
| 10 | d | 60 |
| 11 | d | 70 |
| 12 | e | 90 |
+----+------+-------+
12 rows in set (0.000 sec)
MariaDB [test]> select name from class group by name;
+------+
| name |
+------+
| a |
| b |
| c |
| d |
| e |
+------+
5 rows in set (0.000 sec)
# 找出平均分大于80的人
MariaDB [test]> select name, avg(score) from class group by name having avg(score) > 80;
+------+------------+
| name | avg(score) |
+------+------------+
| b | 85.0000 |
| c | 93.3333 |
| e | 90.0000 |
+------+------------+

2. having

having字句可以让我们筛选成组后的各种数据,where字句在聚合前先筛选记录,也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。

3. left join和right join

left join(左连接)会从左表 (table1) 那里返回所有的行,即使在右表 (table2) 中没有匹配的行。
right join(右连接)会从右表 (table2) 那里返回所有的行,即使在左表 (table1) 中没有匹配的行。
# 示例
mysql> select * from person;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 10 |
| 2 | lisi | 11 |
| 3 | wangwu | 13 |
+----+----------+------+
mysql> select * from shop;
+----+--------+-----------+
| id | name | person_id |
+----+--------+-----------+
| 1 | 衣服 | 1 |
| 2 | 鞋子 | 1 |
| 3 | 帽子 | 3 |
| 4 | 桌子 | 4 |
+----+--------+-----------+
mysql> select person.id,person.name,shop.name from person left join shop on person.id=shop.person_id;
+----+----------+--------+
| id | name | name |
+----+----------+--------+
| 1 | zhangsan | 衣服 |
| 1 | zhangsan | 鞋子 |
| 2 | lisi | NULL |
| 3 | wangwu | 帽子 |
+----+----------+--------+
mysql> select person.id,person.name,shop.name from person right join shop on person.id=shop.person_id;
+------+----------+--------+
| id | name | name |
+------+----------+--------+
| 1 | zhangsan | 衣服 |
| 1 | zhangsan | 鞋子 |
| 3 | wangwu | 帽子 |
| NULL | NULL | 桌子 |
+------+----------+--------+
Copy link
On this page
1. group by
2. having
3. left join和right join