转自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是选出两张表都存在的记录：

image.png

LEFT OUTER JOIN是选出左表存在的记录：

image.png

RIGHT OUTER JOIN是选出右表存在的记录：

image.png

FULL OUTER JOIN则是选出左右表都存在的记录：

image.png

管理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;

sql语句

分页查询

聚合查询

分组

连接查询

管理MySQL

推荐阅读更多精彩内容