SQL常用命令

查询

条件查询

SELECT * FROM <表名> WHERE <条件>

SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

投影查询

SELECT 列1, 列2, 列3 FROM ...

SELECT id, score points, name FROM students WHERE gender = 'M';
//可以重命名score为points

排序

ORDER BY

加上DESC表示“倒序

ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

分页查询

LIMIT <M> OFFSET <N>

对结果集从N号记录开始，最多取M条。注意SQL记录集的索引从0开始。

OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。

• OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0
• 在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。
• 使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。

聚合查询

SELECT COUNT(*) <别名> FROM <表名>;

COUNT(*)或COUNT(id)表示查询所有列的行数

SELECT COUNT(*) boys FROM students WHERE gender = 'M';

函数	说明
AVG	计算某一列的合计值，该列必须为数值类型
SUM	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL

请使用一条SELECT查询查出每个班级男生和女生的平均分：

SELECT AVG(score) average FROM students GROUP BY class_id , gender;

分组聚合

统计各班的男生和女生人数:

SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

笛卡尔查询

SELECT * FROM <表1> <表2>

结果集的列数是表1和表2的列数之和，行数是表1和表2的行数之积。

可利用投影查询“设置列的别名”：

SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;    //表名也可以起别名
WHERE s.gender = 'M' AND c.id = 1;    //可以添加WHERE条件

连接查询

连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

选出所有学生，同时返回班级名称：

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只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根据条件s.class_id = c.id返回的结果集仅包含1，2，3。

1. 先确定主表，仍然使用FROM <表1>的语法；
2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；
3. 然后确定连接条件，使用ON <条件…>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
4. 可选：加上WHERE子句、ORDER BY等子句。

RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

LEFT OUTER JOIN则返回左表都存在的行。如果我们给students表增加一行，并添加class_id=5，由于classes表并不存在id=5的行，所以，LEFT OUTER JOIN的结果会增加一行，对应的class_name是NULL

假设查询语句是：

SELECT ... FROM tableA XX 
JOIN tableB 
ON tableA.column1 = tableB.column2;

修改

INSERT

UPDATE

DELETE

管理