# mysql常用语法

## 1. group by

GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

比如：

```mysql
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（内连接）：** 会返回两个表字段匹配的行。

```sql
# 示例
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

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

```sql
# 语法如下
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

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

```sql

# 示例
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)

```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://jun-wang.gitbook.io/learnjava/ji-shu-xue-xi/mysql/mysql-chang-yong-yu-fa.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.