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和inner join

left join（左连接）： 会从左表 (table1) 那里返回所有的行，即使在右表 (table2) 中没有匹配的行。

right join（右连接）： 会从右表 (table2) 那里返回所有的行，即使在左表 (table1) 中没有匹配的行。

inner join（内连接）： 会返回两个表字段匹配的行。

# 示例
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     | 桌子   |
+------+----------+--------+

mysql> select person.id,person.name,shop.name from person inner join shop on person.id=shop.person_id;
+----+----------+--------+
| id | name     | name   |
+----+----------+--------+
|  1 | zhangsan | 衣服   |
|  1 | zhangsan | 鞋子   |
|  3 | wangwu   | 帽子   |
+----+----------+--------+
3 rows in set (0.00 sec)

4. case when

根据条件执行不同的操作。

# 语法如下
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result
END

# 示例
mysql> select * from test;
+----+----------+------+---------+
| id | name     | age  | address |
+----+----------+------+---------+
|  1 | zhangsan |  200 | beijing |
|  2 | lisi     |  222 | nanjing |
|  4 | lisi     |   21 | nanjing |
|  5 | lisi     |   21 | nanjing |
+----+----------+------+---------+
4 rows in set (0.00 sec)

mysql> select name,age,case when age>200 then "优秀" when age>100 then "良好" else "不及格" end as score  from test;
+----------+------+-----------+
| name     | age  | score     |
+----------+------+-----------+
| zhangsan |  200 | 良好      |
| lisi     |  222 | 优秀      |
| lisi     |   21 | 不及格    |
| lisi     |   21 | 不及格    |
+----------+------+-----------+
4 rows in set (0.00 sec)

4. with as

用于创建临时表,方面后面临时用

# 示例
mysql> select * from person;
+----+----------+-----------+------+
| id | name     | age       | id2  |
+----+----------+-----------+------+
|  1 | zhangsan | 10        | NULL |
|  2 | lisi     | 11        | NULL |
|  3 | wangwu   | 13        | NULL |
|  4 | qqq      | 11        | NULL |
|  5 | 12       | 你好啊    | NULL |
+----+----------+-----------+------+
5 rows in set (0.01 sec)

mysql> select * from test;
+----+----------+------+---------+
| id | name     | age  | address |
+----+----------+------+---------+
|  1 | zhangsan |  200 | beijing |
|  2 | lisi     |  222 | nanjing |
|  4 | lisi     |   21 | nanjing |
|  5 | lisi     |   21 | nanjing |
+----+----------+------+---------+
4 rows in set (0.00 sec)

mysql> with tem1 as(select name,age from test), tem2 as (select id,name from person) select tem1.name, tem2.id from tem1 left join tem2 on tem1.name=tem2.name;
+----------+------+
| name     | id   |
+----------+------+
| zhangsan |    1 |
| lisi     |    2 |
| lisi     |    2 |
| lisi     |    2 |
+----------+------+
4 rows in set (0.00 sec)