DML-数据操作语言

DML

DML 语句主要针对数据表的增删改查操作，即对数据表的 INSERT、DELETE、UPDATE、SELECT 操作

1.SELECT查询
查询可以使用AND OR NOT

//查询
SELECT * FROM students;
SELECT * FROM students WHERE score >= 80;
SELECT * FROM students WHERE NOT class_id = 2;
SELECT * FROM students WHERE score >= 80 OR gender = 'M';
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

使用别名

//以下SELECT语句将列名score重命名为points，而id和name列名保持不变：
SELECT id, score points, name FROM students;

排序

SELECT id, name, gender, score FROM students ORDER BY score;
SELECT id, name, gender, score FROM students ORDER BY score DESC;
//如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
//ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序：
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

分页查询

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
//跳过三条数据，查询第二页的数据
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

聚合查询

//计数
SELECT COUNT(*) FROM students;
//取个别名方便查看
SELECT COUNT(*) num FROM students;
//分组聚合
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
//也可以使用多个列进行分组。例如，我们想统计各班的男生和女生人数：
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

除了COUNT

多表查询

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;

连接查询

//内连接——INNER 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;

对于这么多种JOIN查询，到底什么使用应该用哪种呢？其实我们用图来表示结果集就一目了然了。
假设查询语句是：

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

我们把tableA看作左表，把tableB看成右表，那么INNER JOIN是选出两张表都存在的记录：

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

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

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