SQL 简明手册 进阶篇

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。

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

删除索引

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

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

SQL 中的数据类型

以MySQL为例

整数

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

浮点数

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

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

定点数

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

字符串

日期

其他

参考

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/