SQL Introduction

In computer programming, create, read, update, and delete (CRUD) are the four basic functions of persistent storage. Alternate words are sometimes used when defining the four basic functions of CRUD, such as retrieve instead of read, modify instead of update, or destroy instead of delete. CRUD is also sometimes used to describe user interface conventions that facilitate viewing, searching, and changing information, often using computer-based forms and reports.

1. 查询语句

select ... from ... where ... group by ... having ... order by ... limit ...
次序 4 1 2 3 5 6 7

2. Group by

GROUP BY 语句用于结合聚合函数,根据一个或多个列对结果集进行分组。

实例:

 1mysql> SELECT * FROM access_log;
 2+-----+---------+-------+------------+
 3| aid | site_id | count | date       |
 4+-----+---------+-------+------------+
 5|   1 |       1 |    45 | 2016-05-10 |
 6|   2 |       3 |   100 | 2016-05-13 |
 7|   3 |       1 |   230 | 2016-05-14 |
 8|   4 |       2 |    10 | 2016-05-14 |
 9|   5 |       5 |   205 | 2016-05-14 |
10|   6 |       4 |    13 | 2016-05-15 |
11|   7 |       3 |   220 | 2016-05-15 |
12|   8 |       5 |   545 | 2016-05-16 |
13|   9 |       3 |   201 | 2016-05-17 |
14+-----+---------+-------+------------+
159 rows in set (0.00 sec)
 1SELECT site_id, SUM(access_log.count) AS nums
 2FROM access_log GROUP BY site_id;
 3+---------+------+
 4| site_id | nums |
 5+---------+------+
 6|   1     |  275 |
 7|   2     |   10 |
 8|   3     |  521 |
 9|   4     |   13 |
10|   5     |  750 |
11+---------+------+
12

3. 聚集函数

1 2 3 4 5 6
count sum max min avg group_concat

4. Having

HAVING 子句可以让我们筛选分组后的各组数据。

1查询每个班中人数大于2的班级号:
2select count(1) as n, classid from stu group by classid having n>2;
3or
4select classid from stu group by classid having count(1)>2;

5. Order by

1... order by n, classid;
21.先按n排序
32.在不改n排序的情况下排classid

6. Limit

1... limit 1, 10;
2# 检索记录行 2-10

7. Join

1stu join class on classid = class.id
2# join 会把左表的每一行分别与右表每一行拼接
3# on 做筛选