SQL 查询与修改

  查询 SELECT

1. 基本查询

查询一张表的所有记录

SELECT * FROM <表名>

2. 条件查询

语句

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

特点
返回的二维表结构和原表相同，即结果集的所有列与原表的所有列一一对应。

举例
查询分数在80分(含)～95分(含)之间的记录

SELECT * FROM students WHERE 80 <= score AND score <= 95   # 注意，不能用 60 <= score <= 90

3. 投影查询

语句

SELECT <列名1>, <列名2>, ..., <列名n> FROM <表名> [WHERE <条件表达式>]

特点
返回一张表中某些列的数据。

举例
从 students 表中查询 name 和 score 的记录，要求 score 大于90，name 改为 new_name；

SELECT name [AS] new_name, score FROM students WHERE score > 70  # AS 可以省略

4. 排序查询

语句

SELECT <列名1>, <列名2>, ..., <列名n> FROM <表名> ORDER BY <列名n> [DESC/ASC]

特点
使用SELECT查询时，大部分数据库的查询结果集通常是按照id排序的，也就是根据主键排序。如果我们要根据其他条件排序，就会用到ORDER BY <列名> ASC/DESC语句。默认的排序规则是ASC（ascend），“升序”，即从小到大。ASC可以省略，即ORDER BY score ASC 和 ORDER BY score效果一样。DESC，即 descend。

注意！！！如果有 WHERE子句，那么 ORDER BY 子句要放到 WHERE 子句后面。一般 ORDER BY 子句会放在一个查询的最后。

举例
查询一班的学生成绩，并按照倒序排序：

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

5. 分页查询

https://www.cnblogs.com/BodhiLeaf/p/16376997.html

6. 聚合查询

如果我们要统计一张表的数据量，例如，想查询 students 表一共有多少条记录，难道必须用SELECT * FROM students查出来，然后再数一数有多少行吗？这个方法当然可以，但是效率低。

对于统计总数、平均数这类计算，SQL提供了专门的聚合函数（Aggregate Functions）。使用聚合函数进行查询，可以快速获得结果。

仍然以查询 students 表一共有多少条记录为例，我们可以使用 SQL 内置的 COUNT() 函数查询：SELECT COUNT(*) FROM students

COUNT(*)表示查询所有列的行数，聚合计算的结果虽然是一个数字，但查询结果集仍然是一个二维表，只是这个二维表只有一行一列，并且列名是 COUNT(*)。

特点
SQL 提供的聚合函数，可以方便地计算总数、合计值、平均值、最大值和最小值等。

聚合函数

函数	说明
COUNT(列名)	计算某一列有多少条记录
SUM (列名)	计算某一列的合计值，该列必须为数值类型
AVG (列名)	计算某一列的平均值，该列必须为数值类型
MAX (列名)	计算某一列的最大值
MIN (列名)	计算某一列的最小值

举例
使用聚合查询计算男生平均成绩。

SELECT AVG(score) AS average FROM students WHERE gender = 'M'

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

7. 笛卡尔查询

语句

SELECT * FROM <表名1> <表名2> ... <表名n>

多表查询又称笛卡尔查询（Cartesian），使用笛卡尔查询时要谨慎，由于结果集行数是目标表行数的乘积，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

举例
例1：将同一张表进行笛卡尔积

SELECT *
FROM students AS a, students AS b;

注意！！！组合的新表中，来自 a 表的列在左，b 表的列在右。

例2：通过条件进行笛卡尔查询

SELECT
	s.id sid,   // 改列名，students 表中的 id 改为 sid
	s.name,
	s.gender,
	s.score,
	c.id cid,
	c.name cname
FROM students s, classes c;   // 改表名，students 改为 s
WHERE s.gender = 'M' AND c.id = 1;

8. 连接查询

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

内连接
选出所有学生，同时返回班级名称。

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender
FROM student s
INNER JOIN classes c
ON s.class_id= c.id;   # 通过 students 表的外键与 classes 表建立联系

这里，使用了一种最常见的连接方式——内连接INNER JOIN <...> ON <...>

内连接（INNER JOIN / JOIN）语法
！！！注意：INNER JOIN 可以简写为 JOIN
（1）先确定主表（FROM 后面，JOIN 前面），使用 FROM <表1> 的语法。
（2）再确定需要连接的表，使用 INNER JOIN <表2> 的语法；
（3）然后确定连接条件，使用 ON <条件...>。前面举例的条件是 s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
（4）可选：加上 WHERE 子句、ORDER BY 等子句。

外连接（Left/Right/Full OUTER JOIN）
假设查询语句如下：

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

对于多种JOIN查询，到底应该用哪种？其实用图来表示结果集就一目了然了。

  修改 Modification

1. 增加 INSERT

向数据库表中插入新记录时，就必须使用INSERT语句。

语句

INSERT INTO <表名> (列名1, 列名2, ...) VALUES (值1, 值2, ...)

举例

INSERT INTO students (class_id, name, gender, score)
VALUES(1, '大宝', 'M', 87), (2, '二宝', 'M', 81)

注意到我们并没有列出 id 字段和 id 字段对应的值，因为 id 字段是一个自增主键，它的值可以由数据库自己推算出来。此外，如果一个字段有默认值，那么在 INSERT 语句中也可以不出现。

注意！！！INSERT 中列名顺序不必和数据库表的列字段顺序一致，但值的顺序必须和列名顺序一致，也就是说，可以写 INSERT INTO students (score, gender, name, class_id) ，但是对应的VALUES就得变成 (80, 'M', '大牛', 2)

可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(...)包含的一组值。

2. 删除 DELETE

语句

DELETE FROM <表名> WHERE <条件表达式>

如果 WHERE 条件没有匹配到任何记录，DELETE 语句不会报错，也不会删除任何记录。
注意！！！不带 WHERE 条件的 DELETE 语句会删除整个表的所有记录。所以，在执行 DELETE 语句时也要非常小心，最好先用 SELECT 语句来测试 WHERE 条件是否筛选出了期望的记录集，然后再用DELETE 删除。

举例

DELETE FROM students   # 删除 students 表的所有记录，但是表依然存在
DELETE FROM students WHERE score > 90  #删除 students 表中 score 大于90记录

3. 修改 UPDATE

语句

UPDATE <表名> SET 列名1=值1, 列名2=值2, ... WHERE ...

如果 WHERE 条件没有匹配到任何记录，UPDATE 语句不会报错，也不会有任何记录被更新。
注意！！！UPDATE 语句可以没有 WHERE 条件，这时，整个表的所有记录都会被更新。所以，在执行 UPDATE 语句时要非常小心，最好先用 SELECT 语句来测试 WHERE 条件是否筛选出了期望的记录集，然后再用 UPDATE 更新。

举例
把所有80分以下的同学的成绩加10分，然后查询出来。

SELECT * FROM （
  UPDATE students SET score = score + 10
  WHERE score < 80 ）