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)
Last updated