转自https://www.liaoxuefeng.com/wiki/1177760294764384
分页查询
经常用到在数据库中查询中间几条数据的需求，比如下面的sql语句：
① selete * from testtable limit 2,1;
② selete * from testtable limit 2 offset 1;
注意：
1.数据库数据计算是从0开始的
2.offset X是跳过X个数据，limit Y是选取Y个数据
3.limit X,Y 中X表示跳过X个数据，读取Y个数据
这两个都是能完成需要，但是他们之间是有区别的：
①是从数据库中第三条开始查询，取一条数据，即第三条数据读取，一二条跳过
②是从数据库中的第二条数据开始查询两条数据，即第二条和第三条。
使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。
聚合查询
查询students表一共有多少条记录：
SELECT COUNT(*) FROM students;
除了COUNT()函数外，SQL还提供了如下聚合函数：
SUM 计算某一列的合计值，该列必须为数值类型
AVG 计算某一列的平均值，该列必须为数值类型
MAX 计算某一列的最大值
MIN 计算某一列的最小值
注意，MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。
要特别注意：如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL。
分组
统计一班、二班、三班的学生数量：
SELECT COUNT(*) num FROM students GROUP BY class_id;
执行这个查询，COUNT()的结果不再是一个，而是3个，这是因为，GROUP BY子句指定了按class_id分组，因此，执行该SELECT语句时，会把class_id相同的列先分组，再分别计算，因此，得到了3行结果。
但是这3行结果分别是哪三个班级的，不好看出来，所以我们可以把class_id（聚合查询的列中，只能放入分组的列）列也放入结果集中：
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
连接查询
我们希望表student查询的结果集同时包含所在班级的名称，而结果集只有class_id列，缺少对应班级的name列。存放班级名称的name列存储在classes表中，只有根据students表的class_id，找到classes表对应的行，再取出name列，就可以获得班级名称。
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;
注意INNER JOIN查询的写法是：
- 先确定主表，仍然使用FROM <表1>的语法；
- 再确定需要连接的表，使用INNER JOIN <表2>的语法；
- 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
- 可选：加上WHERE子句、ORDER BY等子句。
使用别名不是必须的，但可以更好地简化查询语句。
我们把tableA看作左表，把tableB看成右表，那么INNER JOIN是选出两张表都存在的记录：
LEFT OUTER JOIN是选出左表存在的记录：
RIGHT OUTER JOIN是选出右表存在的记录：
FULL OUTER JOIN则是选出左右表都存在的记录：
管理MySQL
- 查看一个表的结构：
DESC students;
- 查看创建表的SQL语句：
SHOW CREATE TABLE students;
- 给students表新增一列birth，使用：
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
- 修改birth列，例如把列名改为birthday，类型改为VARCHAR(20)：
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
- 删除列，使用：
ALTER TABLE students DROP COLUMN birthday;
- 插入或替换：
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。
- 插入或更新
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。
- 插入或忽略
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。
- 快照
如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT：
# 对class_id=1的记录进行快照，并存储为新表students_of_class1:*
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
- 写入查询结果集
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
