【DataBase】SQL汇总

SQL 汇总

基本查询

SELECT * FROM <表名>

使用SELECT * FROM students时，SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询，SELECT 查询的结果是一个二维表

条件查询

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

条件表达式可以用<条件1> AND <条件2>表示满足条件 1 并且满足条件 2
条件表达式可以用<条件1> OR <条件2>表示满足条件 1 或者满足条件 2
条件表达式可以用NOT <条件> / <键> <> <值>表示不符合该条件的记录

投影查询

SELECT <列1>, <列2>, <列3> FROM <表名>

还可以给每一列起个别名

SELECT <列1> <别名1>, <列2> <别名2>, <列3> <别名3> FROM <表名>

投影查询同样可以接 WHERE 条件，实现复杂的查询

排序

查询到结果集通常是按照主键排序，这也是大部分数据库的做法。如果要根据其他条件排序可以加上ORDER BY子句

SELECT <列1>, <列2>, <列3>, <列4> FROM <表名> ORDER BY <列X>

值的默认的排序规则是ASC：“升序”，即从小到大。ASC可以省略，即ORDER BY <列> ASC和ORDER BY <列>效果一样。

如果要使值相反排序，可以加上DESC，表示“倒序”

SELECT <列1>, <列2>, <列3>, <列4> FROM <表名> ORDER BY <列X> DESC

如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面

分页查询

使用 SELECT 查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，分页实际上就是从结果集中“截取”出第 M~N 条记录。这个查询可以通过LIMIT <M> OFFSET <N>子句实现，表示从N号记录开始，每页最多取M条，若是希望查询第 n 页，则跳过头(n-1)*M条 ==> SELECT <列>, <列>, <列>, <列> FROM students LIMIT <M> OFFSET <(n-1)*M>

SELECT <列>, <列>, <列>, <列> FROM students LIMIT <M> OFFSET <N>

可见，分页查询的关键在于，首先要确定每页需要显示的结果数量 pageSize，然后根据当前页的索引pageIndex（从 1 开始），确定LIMIT和OFFSET应该设定的值

LIMIT总是设定为pageSize
OFFSET计算公式为pageSize * (pageIndex - 1)

注意：

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

使用 LIMIT OFFSET 可以对结果集进行分页，每次查询返回结果集的一部分；分页查询需要先确定每页的数量和当前页数，然后确定 LIMIT 和 OFFSET 的值

聚合查询

想查询 students 表一共有多少条记录，可以直接使用SELECT * FROM students查出来然后再数一数有多少行，但是比较弱智。对于统计总数、平均数这类计算，SQL 提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

SELECT COUNT(*) FROM <表名>

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

通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果

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

除了COUNT()函数外，SQL 还提供了如下聚合函数：

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

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

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

分组查询

SELECT COUNT(*) FROM <表名> GROUP BY <指定列>

GROUP BY子句指定了按指定列分组，因此，执行该SELECT语句时，会把指定列先分组，再分别计算

为了便于区分，可以将指定列也放入结果集中

SELECT <指定列>, COUNT(*) num FROM <表名> GROUP BY <指定列>
// 将指定列放入到结果集中，并设置 COUNT(*)别名为 num

注意：聚合查询的列中，只能放入分组的列，但是可以使用多个列进行分组

SELECT <指定列1>, <指定列2>, COUNT(*) num FROM <表名> GROUP BY <指定列1>, <指定列2>

多表查询（笛卡尔查询）

SELECT 查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：

SELECT * FROM <表1> <表2>

使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有 100 行记录的表进行笛卡尔查询将返回 1 万条记录，对两个各自有 1 万行记录的表进行笛卡尔查询将返回 1 亿条记录

注意，多表查询时，要使用表名.列名这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。但是，用表名.列名这种方式列举两个表的所有列实在是很麻烦，所以 SQL 还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点

多表查询也是可以添加WHERE条件的

连接查询

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

注意 INNER JOIN 查询的写法是：

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

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;

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

假设查询语句是：

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

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

INNER JOIN

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

LEFT OUTER JOIN

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

RIGHT OUTER JOIN

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

FULL OUTER JOIN

修改数据

关系数据库的基本操作就是增删改查，即 CRUD：Create、Retrieve、Update、Delete

而对于增，删，改对应的 SQL 语句分别是：

INSERT：插入新纪录
UPDATE：更新已有记录
DELETE：删除已有记录

INSERT

当我们需要向数据库表中插入一条新记录时，就必须使用 INSERT 语句

INSERT 语句的基本语法是：

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

自增主键的值可以由数据库自己推算出来。此外，如果一个字段有默认值，那么在 INSERT 语句中也可以不出现；要注意：字段顺序不必和数据库表的字段顺序一致，但值得顺序必须和字段顺序一致。

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

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

SELECT * FROM students;

UPDATE

如果要更新数据库表中的记录，我们就必须使用 UPDATE 语句

UPDATE 语句的基本语法是：

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

UPDATE 语句的 WHERE 条件和 SELECT 语句的 WHERE 条件其实是一样的，因此完全可以一次更新多条记录
在 UPDATE 语句中，更新字段时可以使用表达式
如果 WHERE 条件没有匹配到任何记录，UPDATE 语句不会报错，也不会有任何记录被更新

注意：要特别小心的是，UPDATE 语句可以没有 WHERE 条件，这时，整个表的所有记录都会被更新。所以，在执行 UPDATE 语句时要非常小心，最好先用 SELECT 语句来测试 WHERE 条件是否筛选出了期望的记录集，然后再用 UPDATE 更新。

在使用 MySQL 这类真正的关系数据库时，UPDATE 语句会返回更新的行数以及 WHERE 条件匹配的行数

DELETE

如果要删除数据库表中的记录，我们可以使用 DELETE 语句

DELETE 语句的基本语法是：

DELETE FROM <表名> WHERE ...;

DELETE 语句的 WHERE 条件也是用来筛选需要删除的行，因此和 UPDATE 类似，DELETE 语句也可以一次删除多条记录，如果 WHERE 条件没有匹配到任何记录，DELETE 语句不会报错，也不会有任何记录被删除
要特别小心的是，和 UPDATE 类似，不带 WHERE 条件的 DELETE 语句会删除整个表的数据，这时，整个表的所有记录都会被删除。所以，在执行 DELETE 语句时也要非常小心，最好先用 SELECT 语句来测试 WHERE 条件是否筛选出了期望的记录集，然后再用 DELETE 删除

在使用 MySQL 这类真正的关系数据库时，DELETE 语句也会返回删除的行数以及 WHERE 条件匹配的行数，若没有匹配到要删除的行号，则返回 0

MySQL

在一个运行 MySQL 的服务器上，实际上可以创建多个数据库（Database）。要列出所有数据库，使用命令：mysql> SHOW DATABASES;

其中，information_schema、mysql、performance_schema 和 sys 是系统库，不要去改动它们。其他的是用户创建的数据库

要创建一个新数据库，使用命令：mysql> CREATE DATABASE <数据库名称>;
要删除一个数据库，使用命令：mysql> DROP DATABASE <数据库名称>;

注意：删除一个数据库将导致该数据库的所有表全部被删除。

对一个数据库进行操作时，要首先将其切换为当前数据库：mysql> USE <数据库名称>;

表

列出当前数据库的所有表，使用命令：mysql> SHOW TABLES;

要查看一个表的结构，使用命令：mysql> DESC <表名>;

还可以使用以下命令查看创建表的 SQL 语句：mysql> SHOW CREATE TABLE <表名>;

创建表使用 CREATE TABLE 语句，而删除表使用 DROP TABLE语句：mysql> DROP 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;

退出 MySQL

使用 EXIT 命令退出 MySQL：mysql> EXIT

注意 EXIT 仅仅断开了客户端和服务器的连接，MySQL 服务器仍然继续运行。

实用 SQl 语句

插入或替换

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。此时，可以使用 REPLACE 语句，这样就不必先查询，再决定是否先删除再插入：

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若 id=1的记录不存在，REPLACE 语句将插入新记录，否则，当前 id=1 的记录将被删除，然后再插入新记录。

插入或更新

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用 INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句：

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

插入或忽略

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO ...语句：

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;

新创建的表结构和 SELECT 使用的表结构完全一致。

写入查询结果集

如果查询结果集需要写入到表中，可以结合 INSERT 和 SELECT，将 SELECT 语句的结果集直接插入到指定表中。

例如，创建一个统计成绩的表 statistics，记录各班的平均成绩：

CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

然后，我们就可以用一条语句写入各班的平均成绩：

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

确保 INSERT 语句的列和 SELECT 语句的列能一一对应，就可以在 statistics 表中直接保存查询的结果：

> SELECT * FROM statistics;
+----+----------+--------------+
| id | class_id | average      |
+----+----------+--------------+
|  1 |        1 |         86.5 |
|  2 |        2 | 73.666666666 |
|  3 |        3 | 88.333333333 |
+----+----------+--------------+
3 rows in set (0.00 sec)

强制使用指定索引

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用 FORCE INDEX 强制查询使用指定的索引。例如：

> SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

指定索引的前提是索引 idx_class_id 必须存在。

参考自：廖雪峰 SQL 教程

posted @ 2020-05-15 18:36 [ABing] 阅读(168) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

ABing

MAKE SOMETHING EPIC