数据库操作

记录一些常用的 SQL 操作。

数据库

数据库是一些关联表的集合。

增加

创建一个数据库很简单，只需要登录后执行下面的命令。

CREATE DATABASE 数据库名;

同时也可以添加条件判断（如果数据库不存在则创建，存在则不创建），并指定字符集和校对集合。

CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

查询

查询所有的数据库。

SHOW DATABASES;

查询匹配的数据库（%：任意个任意字符，_：某个任意字符）。

# 显示以 t 开头的数据库
SHOW DATABASES LIKE "t%";

查询某个数据库创建的指令

SHOW CREATE DATABASE 数据库名;

切换

USE 数据库名;

改变

# 对表的字符集或校对集进行改变
ALTER DATABASE 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

删除

DROP DATABASE 数据库名;

数据表

表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。

创建

基本语法。

CREATE TABLE 表格名称 (字段名称 字段类型 [字段属性]，[字段名称 字段类型 [字段属性]，...] [表格属性])

常用字段属性：

• AUTO_INCREMENT 定义列为自增的属性，数值会自动加 1，一般用于主键。
• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为 NULL，就会报错。

表格属性：

• ENGINE 设置存储引擎。
• CHARSET 设置编码。
• COLLATE 设置校对集。

其它：

• PRIMARY KEY 关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

实例

CREATE TABLE my_table (name VARCHAR(40) NOT NULL);

注意

• 表名和字段名外面的符号是英文输入法状态下的反单引号，以区分普通字符。
• 指定所在表格后才可创建，可以先切换到指定数据后进行创建，也可以在指定表名称时通过 数据库名.表名 的方式进行创建。
• 同样可以使用条件判断。

基于已有表创建

根据以及存在的表格（可以跨数据库，通过 数据库名.表名 指定）进行创建，仅复制表结构不复制表中的数据。

CREATE TABLE 新表名 LIKE 参考表名;

查询

SHOW TABLES;

查询匹配的数据表（%：任意个任意字符，_：某个任意字符）。

# 显示以 t 开头的数据表
SHOW TABLES LIKE "t%";

显示表格字段信息

# 以下三个命令是等价的
DESC 表名;
DESCRIBE 表名;
SHOW COLUMNS FROM 表名;

# 显示的信息分别表示：字段名，字段类型，是否可以为空，索引，默认值和额外的属性。

查看表的创建语句

SHOW CREATE TABLE 表名;

修改

修改表属性

ALTER TABLE 表名 CHARSET utf8;

修改表结构

重命名。

RENAME TABLE 旧表名 TO 新表名;

新增字段。

ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [字段属性] [位置];

指定位置，可以根据在已有的列之前或之后来指定。AFTER 字段名 表示在某列之后，默认的在最后一列之后添加，FIRST 字段名则直接在第一列添加。

ALTER TABLE my_table ADD COLUMN id int FIRST;
ALTER TABLE my_table ADD COLUMN age int AFTER name;

修改字段名

修改字段名后必须指定字段类型，因为它不会引用以前的。

# 语法
ALTER TABLE 表名 CHANGE [COLUMN] 旧字段名 新字段名 字段类型 [字段属性] [位置];

修改字段类型/属性

通过将上面的关键字 CHANGE 改为 MODIFY 即可更改字段类型或字段属性。

ALTER TABLE 表名 MODIFY [COLUMN] 字段名 字段类型 [字段属性] [位置];

删除

删除数据表

DROP TABLE 表名[,表名[...]];

删除字段名

ALTER TABLE 表名 DROP 字段名;

数据操作

在 SELECT、DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。

插入数据

# 语法
INSERT INTO 表名 [(字段，[字段，...])] VALUES (字段值[,字段值[,...]);
# 实例
INSERT INTO my_table (age, name) VALUES (18, 'sharon');

注意：

• 插入的数据必须与字段的字段类型一致。
• 可见字段是可以省略的，但是省略字段时插入的值个数必须与表格中的字段个数和顺序一致（除了自增的主键）。
• 当同时键入字段和对应的值时，插入的字段的个数可以是小于等于表格中的字段数，并且顺序也是可变的，只要值与其对应。

查询数据

查询表中所有数据，多张表以逗号隔开。

SELECT * from 表名表名[,表名...];

查询某个或多个字段。

SELECT 字段名[,字段名[,...]] FROM 表名[,表名...];

条件查询。

SELECT 字段名[,字段名[,...]] FROM 表名[,表名...] WHERE [WHERE condition1 [AND [OR]] condition2...;

修改数据

UPDATE 表名 SET 字段名=字段值[, 字段名=字段值...]  [WHERE Clause];

删除数据

DELETE FROM 表名 [WHERE Clause];

数据类型

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数字

包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

整型

指定整型类型时，紧挨着括号中的数字表示显示长度。

整型字段有个 ZEROFILL 属性，根据显示长度在数字长度不够的数据前面填充 0，以达到设定的长度。

如果给整型字段加上 UNSIGNED 属性则确定为无符号整数，浮点类型不能是 UNSIGNED 的。

小数

用户插入的数据不能超过指定范围，但系统自动进位是可以的。

定点数(DECIMAL)进位超出范围会报错。

日期和时间类型

表示时间值的日期和时间类型为 DATETIME、DATE、TIMESTAMP、TIME 和 YEAR。

YEAR 字段用一个字节保存，对应的范围为 1901-2155。有两种录入方法，一种是直接键入 4 位数表示年份，另一种是键入两位数，当键入的两位数小于等于 69 时默认的前缀是 20，也就是最终录入的是 2069，而键入 70-99，则默认的前缀为 19。

TIMESTAMP 不能为空，其对应的一行数据改变时，它会自动变更为当前的时间戳。

TIME 一般用来表示时间段，表示的范围可以是 -838:59:59'/'838:59:59，在指定时可以在时间的前面加上一个数字和一个空格，数字相当于天数，结果会以 24 乘上天数再加上后面的时间，可以指定负数。

字符串类型

字符串类型指 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

CHAR/VARCHAR

指定类型后面紧挨着的括号中的数字表示字符数。

CHAR(L) 其中字符数范围为 0-255，指定长度后，系统一定会分配指定的空间用于存储数据。

因为 VARCHAR 要记录数据的长度（系统根据长度来自动分配空间），所以每个数据产生后，系统都会再后面增减 1-2 个字节的额外开销，用来保存数据所占用的空间长度。如果数据本身小于 127 个字符，则额外开销一个字节，超过后开销两个字节。

CHAR 的效率通常要比 VARCHAR 高，因为后者需要通过其后的记录进行计算。

字符长度在 255 内，如果确定数据的长度则使用 CHAR，否则使用 VARCHAR。

TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT

在选择对应的存储文本时，不需要可以选择 TEXT 类型，系统会自动根据存储的数据长度选择合适的文本类型，通常我们会使用 TEXT。

字符长度超过 255 个时，那么必须选择使用 TEXT 类型中之一来存储。

ENUM

枚举类型存储的数据事实上并不是看到的数据，而是数据的下标，这个下标是从一开始的。

枚举类型规范了输入，限定只能插入列举的选项，由于存储的是下标，所以插入数据时可以直接插入下标。

节省存储空间。

SET

实际存储的数据是根据插入数据的二进制表示（对应位选中置为壹，否则置为零），进行颠倒后转换为的十进制数。

同样规范了输入，节省了存储空间。

通常在使用方面 ENUM 类似单选框，SET 则类似复选框。

记录长度

记录长度（行）的总长度不能超过 65535 个字节。

汉字占多少长度与编码有关：UTF－8：一个字符需要 3 个字节，GBK：一个字符需要 2 个字节。

VARCHAR 最多需要使用两个字节来保存数据所占用的空间长度，所以 UTF－8 编码中 VARCHAR 最多存储 21844（Math.round(65535/3 - 1)） 个字符，GBK 编码则可以存储 32766（Math.round(65535/2 - 1)） 个字符。

字段属性（列属性）

MySQL 关键字	含义	备注
NULL	数据列可包含 NULL 值	MySQL 的记录长度最大位 65535 个字节，而当表中某个字段允许为空时，系统会设计保留一个字节来存储 NULL，最终有效的存储长度则为 65534 个字节。
NOT NULL	数据列不允许包含 NULL 值
DEFAULT	默认值	使用：DEFAULT 默认的内容（文字使用引号包裹），对应字段未填写插入值（或者显示的使用插入 DEFAULT 关键字）时，使用该默认值。
COMMENT	列描述	使用：COMMENT 描述内容（文字使用引号包裹），查看时需要通过查看表的创建语句来查看。
PRIMARY KEY	主键	每张表有且只有一个字段作为主键，里面的值具有唯一性。
AUTO_INCREMENT	自动递增，适用于整数类型	-
UNSIGNED	无符号	-
CHARACTER SET name	指定一个字符集	-

主键

主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。

业务主键（自然主键）：在数据库表中把具有业务逻辑含义的字段作为主键，称为“自然主键(Natural Key)”。

逻辑主键（代理主键）：在数据库表中采用一个与当前表中逻辑信息无关的字段作为其主键，称为“代理主键”。

复合主键（联合主键）：通过两个或者多个字段的组合作为主键。

创建

# 1、直接在表字段之后增加唯一键标识符：PRIMARY KEY
# 2、在所有的字段之后使用 PRIMARY KEY(字段名)
# 3、在创建完表之后增加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

查看主键

# 1、通过查看表的创建语句来查看
# 2、查看字段
DESC 表名;
DESCRIBE 表名;
SHOW COLUMNS FROM 表名;

删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

自增长（AUTO_INCREMENT）

具有自增长属性的字段在插入数据时没有指定值，或者值为 NULL 时，系统会根据已有数据进行增长后填充。

修改

ALTER TABLE 表名 AUTO_INCREMENT = 指定的值。

删除

删除自动增长就是通过修改字段的形式，取消掉字段属性中的自增长属性。

变量

在系统中，有一组变量用来维护自动增长的起始值(auto_increment_offset)和步长(auto_increment_increment)。

SHOW VARIABLES LIKE 'AUTO_INCREMENT%';

因为 AUTO_INCREMENT 同时也是表选项，我们在创建表格时，在表选项部分可以直接通过 AUTO_INCREMENT=起始值 来指定自增长的起始值。

注意

• 自动增长只适用于数值类型。
• 每张表只能拥有一个自增长，自增长会上升到表选项中。
• 如果插入的数据中指定了自增长列的值，那么自增长特性不会被激发，它会将插入的值（插入的值不能比该列中已有的值小）作为新的起点并插入表中。

唯一键（UNIQUE KEY）

唯一键可以有一个名字，默认的是以字段名为名，其目的主要用于对非空值保证唯一。

创建

# 1、直接在表字段之后增加唯一键标识符：UNIQUE
# 2、在所有的字段之后使用 UNIQUE KEY [名称] （字段列表）
# 3、在创建完表之后也可以用增加唯一键
ALTER TABLE 表名 ADD UNIQUE KEY [名称] (字段名);

删除

# 唯一键也是一种索引
ALTER TABLE 表名 DROP INDEX 唯一键名字;

复合唯一键

唯一键和主键一样可以使用多个字段来共同保证唯一性。

通常，主键都是单一字段（逻辑主键），而其他需要保证唯一性的内容都使用唯一键来完成。

注意

• 每张表中可以有多个唯一键，唯一键列中的值可以为 NULL，并且可以有多个。

高级操作

多列插入

INSERT INTO 表名 [(字段，[字段，...])] VALUES (字段值[,字段值[,...])[,(字段值[,字段值[,...])...];

主键冲突

在表中如果使用的是业务主键，而要插入的数据不知道是否会产生主键冲突。

# 更新方法：如果产生冲突，则将指定要更新的字段的值在已存在主键所在行进行替换
INSERT INTO 表名 [(字段，[字段，...])] VALUES (字段值[,字段值[,...]) ON DUPLICATE KEY UPDATE 字段名 = 新值;

# 替代方法：如果产生冲突，直接替换整行数据（除了主键）
# 因为要判断是否存在冲突，所以效率比 INSERT 低，不宜处理大量数据
REPLACE INTO 表名 [(字段，[字段，...])] VALUES (字段值[,字段值[,...])[,(字段值[,字段值[,...])...];

蠕虫复制

以已有的数据进行插入，通常用于产生大量数据来进行测试，过程中需要注意主键冲突。

INSERT INTO 表名 [(字段，[字段，...])] SELECT 字段，[字段，...] FROM 表名;

更新数据

使用 LIMIT 关键字限制更新的条数。

UPDATE 表名 SET 字段名=字段值[, 字段名=字段值...]  [WHERE Clause] LIMIT 数字;

删除数据

使用 TRUNCATE 关键字删除表内所有数据并重置 AUTO_iNCREMENT 的值。

TRUNCATE 表名;

查询数据

SELECT [选项] 字段名[,字段名[,...]] FROM 数据源 [where 条件] [分组] [having 条件] [排序] [limit 限制];

可选的选项包括：ALL（默认，现实所有的结果）和 DISTINCT（两行间所有的字段相同时，进行去重后显示）。

字段名可以是大于等于一个，多个字段用英文逗号分隔，也可以是用星号（*）表示所有的字段。

数据源只要是符合二维表的数据结构即可。

当从多个表中获取数据时，结构会将两张表的字段进行拼接，记录数进行相乘（也就是取前一张表的每一条数据与后一张表的每一条数据组成新的一行数据插入到结果集中 => 笛卡尔积）。

在不同数据源中获取数据时，不同数据源存在同名字段时可以给同名字段指定别名。

SELECT 表1.字段名 [as] 别名,表2.字段名 [as] 别名 FROM 表1,表2;

我们可以从查询的结果进行查询。

SELECT 字段名[,字段名[,...]] FROM (SELECT 字段名[,字段名[,...]] FROM 数据源) [as] 别名;

分组可以对查询的结果按照字段进行分组，通常配合一些聚合函数来达到统计的效果。

# 单分组
SELECT 字段名[,字段名[,...]],[聚合函数] FROM 数据源 GROUP BY 字段名;
# 多分组
SELECT 字段名[,字段名[,...]],[聚合函数] FROM 数据源 GROUP BY 字段名,字段名[,...];

常用聚合函数：

• count() 统计分组中的数量，如果统计的是字段则不统计为 NULL 的数据，如果参数为星号则统计结果条数。
• avg() 求平均值。
• sum() 求和。
• max() 求最大值。
• min() 求最小值。

使用 GROUP_CONCAT() 可以查看分组中某字段的成员。

分组排序，分组后可以直接在其字段后指定升降排序，默认为升序。

SELECT 字段名[,字段名[,...]],[聚合函数] FROM 数据源 GROUP BY 字段名 [ASC],字段名[,...] [DESC];

回溯统计的概念：利用 WITH ROLLUP 关键字（书写在 SQL 语句末尾），可以在每次分组过后，根据当前分组的字段进行统计，并向上一级分组进行汇报。

HAVING 字句，不同于 WHERE 字句是在从磁盘中读取数据时直接对数据进行判断，而是在内存中经过分组后进行判断，所以它可以使用字段别名和聚合函数等特性，所以通常我们可以把分组后统计的结果用来进行判断。

SELECT 字段名[,字段名[,...]],[聚合函数] FROM 数据源 GROUP BY 字段名 [HAVING 判断];

排序，使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。可以设定多个字段来排序。

SELECT 字段名[,字段名[,...]] FROM 数据源 ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]];

限制（分页），使用 LIMIT 关键字可以指定查询结果显示的条数，更好的是它可以实现分页效果。

# 当后面指定一个数字 n 时，则最多从结果中的第一条开始选中 n 条。当后面跟着两个数字（以英文逗号隔开：m,n）时，则从结果的第 m 条开始选择 n 条（实际结果不足可以小于 n 条）。
SELECT 字段名[,字段名[,...]] FROM 数据源 [LIMIT 数字[,数字]];