ZHANGKB

【SQL】SQL语法

2022/11/11 16:07:02

创建表

语法：CREATE TABLE 表名(字段名 1 数据类型，字段名 2 数据类型，字段名 3 数据类型);

CREATE TABLE tbl (col TEXT)

查询

语法：SELECT [column-name] FROM [table-name];。

  • column-name：定义结果集的列。
    • * 表示所有列。
    • 可以以逗号分割传入多个列名以返回指定列的数据，id, score, name。
    • 可以在列名后面设置别名，用空格分开，score points。
  • table-name：要查询的表。
    • 可以以逗号分割传入多个表名进行多表查询，students, classes，需注意多表查询为笛卡尔查询open in new window。
    • 可以在表名后面设置别名，用空格分开，students s, classes c，注意为表设置别名后默认的列名会变为 [表别名]![列名]。

例子：

  • SELECT * FROM students;：查询 students 表中的所有数据。
  • SELECT name FROM students;：查询 students 表中的 name 列。
  • SELECT id, score, name FROM students;：查询 students 表中的 id, score, name 列。
  • SELECT id, score points, name FROM students; 将 score 列重命名为 points。

在多表查询时为列名设置别名的方法如下：

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

条件查询

语法：WHERE [exp] AND [exp];。

  • exp：条件表达式。

如果不加括号，条件运算按照 NOT、AND、OR 的优先级进行，即 NOT 优先级最高，其次是 AND，最后是 OR。加上括号可以改变优先级。

例子：

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

关键字

  • WHERE 关键字进行条件查询。
  • AND 关键字实现 且。
  • OR 关键字实现 或。
  • NOT 关键字实现 非，等价于 <>，NOT class_id = 2 等价于 class_id <> 2。
  • LIKE 判断类似

常用条件表达式

条件表达式举例 1表达式举例 2说明
使用 = 判断相等score = 80name = 'abc'字符串需要用单引号括起来
使用 > 判断大于score > 80name > 'abc'字符串比较根据 ASCII 码，中文字符比较根据数据库设置
使用 >= 判断大于或相等score >= 80name >= 'abc'
使用 < 判断小于score < 80name <= 'abc'
使用 <= 判断小于或相等score <= 80name <= 'abc'
使用 <> 判断不相等score <> 80name <> 'abc'等价于 NOT 关键字
使用 LIKE 模糊查询name LIKE 'ab%'name LIKE '%bc%'%表示任意字符，例如 'ab%' 将匹配 'ab'，'abc'，'abcd'

分页查询

语法：LIMIT [record-quantity] OFFSET [record-index];

  • record-quantity：要从表中获取的最大数据数量，相当于 pageSize。
  • record-index：从第几条数据开始，索引从 0 开始，相当于 pageSize * (pageIndex - 1)。

例子：

  • SELECT * FROM students LIMIT 3 OFFSET 0; 从第 0 条数据开始获取 3 条数据。
  • SELECT * FROM students LIMIT 3 OFFSET 5; 从第 5 条数据开始获取 3 条数据。

聚合查询

SQL 教程——聚合查询open in new window。

对于统计总数、平均数这类计算可以使用聚合查询。

聚合查询的结果仍然是一个二维表，因此，在使用聚合查询时，可以给列名设置别名，便于处理结果。

例子：

  • SELECT COUNT(*) num FROM students; 使用聚合查询并设置结果集的列名为 num。
  • SELECT CEILING(COUNT(*) / 3) num FROM students; 计算每页 3 条记录时的总页数。

聚合函数

函数说明WHERE 条件未匹配时的返回值
COUNT计算某一列的记录总数0
SUM计算某一列的合计值，该列必须为数值类型NULL
AVG计算某一列的平均值，该列必须为数值类型NULL
MAX计算某一列的最大值NULL
MIN计算某一列的最小值NULL

分组

语法 GROUP BY [column-id]。

例子：

  • SELECT class_id, COUNT(*) num FROM students GROUP BY class_id; 先按照 class_id 分组（相同的归于一组），再分别计算，结果如下：

    class_idnum
    14
    23

  • SELECT class_id,gender, AVG(score) score FROM students GROUP BY gender,class_id; 多个分组，查询查每个班级男生和女生的平均分。

连接查询

SQL 教程——连接查询open in new window。

先确定一个主表作为结果集，然后把其他表的行有选择性地“连接”在主表结果集上。

  • 从 students 表中查询数据，并将 classes 表的指定数据填充进结果集中：

    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 查询的写法是：

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

连接类型

FROM 关键字后跟的表为左表，INNER JOIN 后跟的表为右表。

  • INNER JOIN 内连接，只返回同时存在于两张表的行数据，如果 students 表的 class_id 包含 1，2，3，classes 表的 id 包含 1，2，3，4，则INNER JOIN 根据条件 s.class_id = c.id 返回的结果集仅包含 1，2，3。

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

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

  • FULL OUTER JOIN 外连接，把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为 NULL。

增

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

  • 字段：指定要在哪些列中插入数据，字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致。。

例子：

  • 插入一条数据：
    INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);
  • 一次插入多条数据：
    INSERT INTO students (class_id, name, gender, score) VALUES
(1, '大宝', 'M', 87),
(2, '二宝', 'M', 81);

改

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

注意：没有 WHERE 语句时表中的所有记录都会被更新。

例子：

  • 想更新 students 表 id=1 的记录的 name 和 score 这两个字段，先写出 UPDATE students SET name='大牛', score=66;，然后在 WHERE 子句中写出需要更新的行的筛选条件 id=1：

    UPDATE students SET name='大牛', score=66 WHERE id=1;

  • 一次更新多条记录：

    UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;

  • 使用表达式：

    UPDATE students SET score=score+10 WHERE score<80;

删

语法：DELETE FROM <表名> WHERE ...;

用法与 UPDATE 相同。

例子：

  • 删除 students 表中 id=1 的记录：

    DELETE FROM students WHERE id=1;

排序

语法：ORDER BY [order-column-name];

使用 SELECT 查询时，查询结果集通常是按照 id 排序的，也就是根据主键排序。

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

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

例子：

  • SELECT * FROM students ORDER BY score; 根据 score 列升序排序。
  • SELECT * FROM students ORDER BY score DESC; 根据 score 列降序排序。
  • SELECT * FROM students ORDER BY score DESC, gender; 先按 score 列倒序排序，如果有相同数据，再按 gender 列倒序排序。

事务

事务open in new window。

在一些业务场景下，需要执行多条 SQL 语句，这些语句必须全部执行完毕才能算作一次成功的操作，如果有一条 SQL 语句执行失败，则视为一次失败的操作，不应该修改数据。

要手动把多条 SQL 语句作为一个事务执行，使用 BEGIN 开启一个事务，使用 COMMIT 提交一个事务，这种事务被称为显式事务：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

有些时候，我们希望主动让事务失败，这时，可以用 ROLLBACK 回滚事务，整个事务会失败：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

参考

SQL 教程——廖雪峰open in new window