backdrop
background

SQL简明手册进阶篇

编程语言/SQL 编程SQLMySQL
2021年2月18日
银河渡舟

本文最后一次更新于 11 个月前，文中内容可能已经过时，请注意甄别。

SQL 简明手册 进阶篇

Toc

  1. SQL 数据库操作
    1. 创建数据库 CREATE DATABASE
    2. 删除数据库 DROP DATABASE
    3. 重命名数据库 RENAME DATABASE
    4. 选择数据库 USE
    5. 显示语句 SHOW
  2. SQL 表操作
    1. 创建表 CREATE TABLE
    2. 更改表结构 ALTER TABLE
      1. 添加新字段
      2. 修改字段属性
      3. 删除字段
    3. 删除表 DROP TABLE
    4. 清空表 TRUNCATE TABLE
    5. 重命名表 RENAME TABLE
  3. SQL 约束
    1. 主键约束
    2. 外键约束
    3. 唯一约束
    4. 非空约束
    5. 默认约束
    6. 检查约束
  4. SQL 索引
    1. 创建索引
    2. 删除索引
  5. SQL 中的数据类型
    1. 整数
    2. 浮点数
    3. 定点数
    4. 字符串
    5. 日期
    6. 其他
  6. 参考

SQL 数据库操作

创建数据库 CREATE DATABASE

CREATE DATABASE [IF NOT EXISTS] <数据库名称>;

数据库名称在RDBMS中必须是唯一的。

删除数据库 DROP DATABASE

DROP DATABASE [IF EXISTS] <数据库名称>;

重命名数据库 RENAME DATABASE

RENAME DATABASE [IF EXISTS] <旧名称> TO <新名称>;
-- 这是MySQL的语法，不同数据库管理系统可能有不同的语法

选择数据库 USE

选择当前要进行操作的数据库

USE <数据库名称>;

显示语句 SHOW

MySQL使用 SHOW 命令实际上就是对 information_schema 表执行 SELECT 。

SHOW DATABASES; -- 显示主机所有数据库
SHOW TABLES; -- 显示当前数据库的所有表
SHOW COLUMNS; -- 显示当前表的结构
DESCRIBE; -- 和SHOW COLUMNS等价
SHOW CREATE DATABASE|SCHEMA <数据库名>\G --显示创建某个数据库的命令
-- \G为按列打印 \g等同于;
SHOW CREATE TABLE <表名>\G -- 显示创建某个表的命令
SHOW INDEX FROM <表名>\G -- 显示某个表的索引

更多用法请参考官方手册：https://dev.mysql.com/doc/refman/8.0/en/show.html

SQL 表操作

创建表 CREATE TABLE

CREATE TABLE [IF NOT EXISTS] <表名>(
    <字段1> <类型> <默认值> <字段约束>,
    <字段2> <类型> <默认值> <字段约束>,
    ...,
    <表约束>
);

更改表结构 ALTER TABLE

  • 使用 ADD 子句添加新字段。
  • 使用 MODIFY 子句修改字段的属性，例如：约束，默认值等。
  • 使用 DROP 子句删除字段。

添加新字段

ALTER TABLE [IF EXISTS] <表名>
ADD <新字段> <类型> <字段约束> [AFTER <已有字段>];

如果要在表中指定新字段的顺序，可以使用可选子句 AFTER <已有字段> ，使新字段添加在已有字段之后。默认添加在最后一列。

修改字段属性

ALTER TABLE [IF EXISTS] <表名>
MODIFY <字段名> <类型> <默认值> <字段约束>;

-- 在MySQL中还可以使用CHANGE语法，可以直接重命名
ALTER TABLE [IF EXISTS] <表名>
CHANGE <旧字段名> <新段名> <类型> <默认值> <字段约束>;

应该修改没有数据的表的字段的属性。 因为更改已包含数据的表中字段的属性可能会导致永久性数据丢失。 例如，如果列的数据类型为VARCHAR，并且将其更改为 INT ，则数据库系统必须将数据从 VARCHAR 转换为 INT 。 如果转换失败，数据库系统可能会使用列的默认值，这可能与预期不符。

删除字段

ALTER TABLE [IF EXISTS] <表名>
DROP <字段1>,
DROP <字段2>,
...;

删除表 DROP TABLE

DROP TABLE [IF EXISTS] <表名>;

为了防止删除不存在的表的错误，使用可选子句 IF EXISTS 。 如果使用 IF EXISTS 选项，如果删除一个不存在的表，数据库系统将不会抛出任何错误。 某些数据库系统会发出警告或通知。

清空表 TRUNCATE TABLE

DELETE 对删除百万行的大表效率较低，使用 TRUNCATE 更高效。

TRUNCATE TABLE [IF EXISTS] <表名>;
TRUNCATE TABLE [IF EXISTS] <表1>,<表2>,...;

TRUNCATE [IF EXISTS] <表名>;
-- 有些数据库（如MySQL）允许省略TABLE

重命名表 RENAME TABLE

RENAME TABLE [IF EXISTS] <旧表名> TO <新表名>;
-- 下面是一个等价的写法
ALTER TABLE [IF EXISTS] <旧表名> RENAME <新表名>;

SQL 约束

主键约束

对于关系表，通过主键字段可以唯一区分不同的记录，任意两条记录的主键值不能相同，主键设定好后一般不要再修改，作为主键最好是完全业务无关的字段，我们一般把这个字段命名为 id。常见的可作为 id 字段的类型有自增整数类型、全局唯一 GUID 类型。

  • 创建主键约束
-- 创建表时设置主键
CREATE TABLE <表名> (
    <字段1> <类型> PRIMARY KEY,
    <字段2> <类型>,
    ...
);

-- 使用表级约束
CREATE TABLE <表名> (
    <字段1> <类型>,
    <字段2> <类型>,
    ...
    PRIMARY KEY(字段1 [,字段2,...]) -- 可以设置多个字段为联合主键
);

-- 使用表级约束并对约束起名
CREATE TABLE <表名> (
    <字段1> <类型>,
    <字段2> <类型>,
    ...
    CONSTRAINT <约束名称> PRIMARY KEY(字段1 [,字段2,...])
);

-- 向已创的表添加主键约束
ALTER TABLE <表名>
ADD PRIMARY KEY (字段1 [,字段2,...]);

-- 添加约束时也可以起名
ALTER TABLE <表名>
ADD CONSTRAINT <约束名> PRIMARY KEY (字段1 [,字段2,...]);
  • 删除主键约束
-- 有名称的约束都可以这样删除
ALTER TABLE <表名>
DROP CONSTRAINT <约束名>;

-- 或者直接这样
ALTER TABLE <表名>
DROP PRIMARY KEY;

外键约束

外键用来在两张关系表之间建立联系，将当前表一些值对应到另一张表的主键上，形成对应关系，这样就创建的两张表之间的链接，称第二张表的主键为当前表的外键。

  • 创建外键约束
-- 定义表时创建外键约束
CREATE TABLE <表1> (
    <字段> <类型>,
    ...
    FOREIGN KEY (字段)
        REFERENCES <表2> (外键字段)
);

-- 为约束起名
CREATE TABLE <表1> (
    <字段> <类型>,
    ...
    CONSTRAINT <约束名称> FOREIGN KEY (字段)
        REFERENCES <表2> (外键字段)
);

-- 向已创的表添加外键约束
ALTER TABLE <表1>
ADD FOREIGN KEY (字段)
    REFERENCES <表2> (外键字段)

-- 添加约束时也可以起名
ALTER TABLE <表1>
ADD CONSTRAINT <约束名> FOREIGN KEY (字段)
    REFERENCES <表2> (外键字段);
  • 删除外键约束
ALTER TABLE <表名>
DROP CONSTRAINT <约束名>;

-- 或者这样
ALTER TABLE <表名>
DROP FOREIGN KEY <约束名>; 
-- 如果创建时没有定义约束名，数据库系统会自动生成一个，可以用其他命令查询

唯一约束

唯一约束用来确保某个或某组字段在每个记录中都是唯一的。

  • 创建唯一约束
-- 创建表时设置唯一约束
CREATE TABLE <表名> (
    <字段1> <类型> UNIQUE,
    <字段2> <类型>,
    ...
);

-- 使用表级约束
CREATE TABLE <表名> (
    <字段1> <类型>,
    <字段2> <类型>,
    ...
    UNIQUE(字段1 [,字段2,...]) -- 可以设置多个字段联合
);

-- 使用表级约束并对约束起名
CREATE TABLE <表名> (
    <字段1> <类型>,
    <字段2> <类型>,
    ...
    CONSTRAINT <约束名称> UNIQUE(字段1 [,字段2,...])
);

-- 向已创的表添加唯一约束
ALTER TABLE <表名>
ADD UNIQUE (字段1 [,字段2,...]);

-- 添加约束时也可以起名
ALTER TABLE <表名>
ADD CONSTRAINT <约束名> UNIQUE (字段1 [,字段2,...]);
  • 删除唯一约束
ALTER TABLE <表名>
DROP CONSTRAINT <约束名>;

-- 或者这样
ALTER TABLE <表名>
DROP UNIQUE <约束名>; 
-- 如果创建时没有定义约束名，数据库系统会自动生成一个，可以用其他命令查询

非空约束

非空约束用来确保插入记录的某个字段不能为空（即 NULL ）

-- 创建约束，非空约束没有表级定义
CREATE TABLE <表名> (
    <字段1> <类型> NOT NULL,
    <字段2> <类型>,
    ...
);
-- 添加约束
ALTER TABLE <表名>
MODIFY <字段名> <类型> NOT NULL; -- 先要确保字段没有NULL值
-- 删除约束
ALTER TABLE <表名>
MODIFY <字段名> <类型> NULL;

默认约束

默认约束用来确定字段未指定时的默认值。

-- 创建约束，默认约束没有表级定义
CREATE TABLE <表名> (
    <字段1> <类型> DEFAULT <值>,
    <字段2> <类型>,
    ...
);
-- 添加约束
-- MySQL用法:
-- ALTER COLUMN这种语法仅见于修改DEFAULT值和一些其他不常用地方
ALTER TABLE <表名>
ALTER <字段名> SET DEFAULT <值>;
-- 删除约束
ALTER TABLE <表名>
ALTER <字段名> DROP DEFAULT;
-- 当然也可以通过MODIFY添加和删除

检查约束

检查约束允许我们自定义新添加值是否合法。MySQL 8.0.16 之前的版本是默认忽略检查约束的。

  • 创建检查约束
-- 创建表时设置检查约束
CREATE TABLE <表名> (
    <字段1> <类型> CHECK(条件表达式),
    <字段2> <类型>,
    ...
);

-- 使用表级约束
CREATE TABLE <表名> (
    <字段1> <类型>,
    <字段2> <类型>,
    ...
    CHECK(条件表达式)
);

-- 使用表级约束并对约束起名
CREATE TABLE <表名> (
    <字段1> <类型>,
    <字段2> <类型>,
    ...
    CONSTRAINT <约束名称> CHECK(条件表达式)
);

-- 向已创的表添加检查约束
ALTER TABLE <表名>
ADD CHECK(条件表达式);

-- 添加约束时也可以起名
ALTER TABLE <表名>
ADD CONSTRAINT <约束名> CHECK(条件表达式);
  • 删除检查约束
ALTER TABLE <表名>
DROP CONSTRAINT <约束名>;

-- 或者这样
ALTER TABLE <表名>
DROP CHECK <约束名>; 
-- 如果创建时没有定义约束名，数据库系统会自动生成一个，可以用其他命令查询

SQL 索引

数据库索引或索引，有助于加速从表中检索数据。当从表查询数据时，首先MySQL会检查索引是否存在，然后MySQL使用索引来选择表的精确物理对应行，而不是扫描整个表。所有主键列都是表的主索引。对于数据量小的表不需要建立索引。

创建索引

  • 创建表时创建索引

MySQL自动将声明为PRIMARY KEY，KEY，UNIQUE或INDEX的任何列添加到索引。

  • 向已有表添加索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX <索引名>
[USING BTREE|HASH|RTREE] 
ON <表> (<字段> [(长度)] [ASC | DESC],...)

UNIQUE索引会对字段创建唯一约束，FULLTEXT、SPATIAL索引仅由MyISAM存储引擎支持，其中FULLTEXT索引仅在数据类型为CHAR，VARCHAR或TEXT的列中接受，SPATIAL索引列值不能为NULL。

不同的存储引擎有不同的索引类型

存储引擎允许的索引类型
MyISAMBTREE, RTREE
InnoDBBTREE
MEMORY/HEAPHASH, BTREE
NDBHASH

删除索引

DROP INDEX <索引名> ON <表名>

-- 或者使用ALTER TABLE语法
ALTER TABLE <表名>
DROP INDEX <索引名>

SQL 中的数据类型

以MySQL为例

整数

数据类型字节数最小值最大值
TINYINT1-128127
SMALLINT2-3276832767
MEDIUMINT3-83886088388607
INT4-21474836482147483647
BIGINT8-92233720368547758089223372036854775807
TINYINT UNSIGNED10255
SMALLINT UNSIGNED2065535
MEDIUMINT UNSIGNED3016777215
INT UNSIGNED404294967295
BIGINT UNSIGNED8018446744073709551616

可以指定整数的显示宽度INT(N) UNSIGNED ZEROFILL，宽度不足用0补全，宽度超过N则无视规则。注意只有设定了UNSIGNED ZEROFILL 才有效。

浮点数

数据类型字节数描述
FLOAT4单精度浮点型
DOUBLE8双精度浮点型

可以设定浮点数小数位精度和有效数字精度 FLOAT(P,D) ：

  • P是表示有效数字数的精度。 P范围为1〜65。
  • D是表示小数点后的位数。D的范围是0~30。MySQL要求D<=P。

定点数

DECIMAL 数据类型用于在数据库中存储精确的数值。有 UNSIGNED 和 ZEROFILL 属性，也可以和浮点数一样设置小数位精度和有效数字精度，用法同浮点数。不指定精度默认为 DECIMAL(10, 0) 。

字符串

数据类型长度描述
CHAR(N)0-255字节定长字符串，用空格补全
VARCHAR(N)0-65536字节可变长字符串，最大长N，N最大值与字符集有关
TINYTEXT0-255字节可变长字符串，数据储存在磁盘中
TEXT0-65535字节可变长文本，数据储存在磁盘中
MEDIUMTEXT0-16777215字节可变长文本，数据储存在磁盘中
LONGTEXT0-4294967295字节可变长文本，数据储存在磁盘中
TINYBLOB0-255字节二进制字符串，数据储存在磁盘中
BLOB0-65535字节二进制字符串，数据储存在磁盘中
MEDIUMBLOB0-16777215字节二进制字符串，数据储存在磁盘中
LONGBLOB0-4294967295字节二进制字符串，数据储存在磁盘中
SET(<列表>)最多64个成员集合（多选）类型
ENUM(<列表>)最多65535个成员枚举（单选）类型，列表成员从1开始编号

日期

数据类型字节数格式描述
DATE3yyyy-MM-dd存储日期值
TIME3HH:mm
存储时分秒
YEAR1yyyy存储年
DATETIME8yyyy-MM-dd HH:mm
存储日期+时间
TIMESTAMP4yyyy-MM-dd HH:mm
存储日期+时间，可作时间戳

其他

数据类型描述
GEOMETRY任何类型的空间值
POINT一个点(一对X-Y坐标)
LINESTRING曲线(一个或多个POINT值)
POLYGON多边形
GEOMETRYCOLLECTIONGEOMETRY值的集合
MULTILINESTRINGLINESTRING值的集合
MULTIPOINTPOINT值的集合
MULTIPOLYGONPOLYGON值的集合
JSONJSON数据类型

参考

  1. https://www.w3cschool.cn/sql/
  2. https://www.runoob.com/sql/sql-tutorial.html
  3. https://www.yiibai.com/sql/
  4. https://www.yiibai.com/mysql/
  5. https://dev.mysql.com/doc/refman/8.0/en/

SQL简明手册进阶篇

https://suborbit.net/posts/concise-sql-advanced/

作者

银河渡舟

发布于

2021年2月18日

编辑于

2023年10月19日

许可协议
CC BY-NC-SA 4.0

转载或引用本文时请注明作者及出处，不得用于商业用途。

Prev Post Cover
SQL简明手册入门篇
Next Post Cover
TensorRT踩坑教程