Databases

CREATE - 创建DB

create database db_nane DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Ref

删除数据库

DROP DATABASE <数据库名称>;

表结构或数据

CREATE - 创建表

以下为创建MySQL数据表的SQL通用语法：

CREATE TABLE table_name (column_name column_type);

以下例子中我们将在 RUNOOB 数据库中创建数据表runoob_tbl：

CREATE TABLE
IF
	NOT EXISTS `runoob_tbl` (
		`runoob_id` INT UNSIGNED AUTO_INCREMENT,
		`runoob_title` VARCHAR ( 100 ) NOT NULL,
		`runoob_author` VARCHAR ( 40 ) NOT NULL,
		`submission_date` DATE,
	PRIMARY KEY ( `runoob_id` ) 
	) ENGINE = INNODB DEFAULT CHARSET = utf8;

实例解析：

NULL | NOT NULL：如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，这意味着，如果在insert 或者 update时，不向字段添加值，就无法插入新纪录或者更新记录
PRIMARY KEY：定义该列为主键
- 主键可以是某一个列或多个列的组合
- PRIMARY KEY 拥有自动定义的 UNIQUE 约束
- 每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束
UNIQUE：约束唯一标识数据库表中的每条记录
- 用于指定基本表在某一个列或多个列的组合上取值必须唯一。定义了UNIQUE约束的那些列称为唯一键。如果为基本表的革一列或多个列的组合指定了UNIQUE约束，则系统将为这些列建立唯一索引，从而保证在表中的任意两行记录在指定的列或列组合上不能取同样的值。
- UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证
KEY
FOREIGN KEY
DEFAULT: Specifies a default value for a column
AUTO_INCREMENT：定义该列为自增的属性
- An integer or floating-point column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is *value*+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1.
  - There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value
ENGINE 设置存储引擎
CHARSET 设置编码

UNIQUE

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row.

For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix length.

下面的 SQL 在 “Persons” 表创建时在 “Id_P” 列创建 UNIQUE 约束：

CREATE TABLE Persons (
	Id_P INT NOT NULL,
	LastName VARCHAR ( 255 ) NOT NULL,
	FirstName VARCHAR ( 255 ),
	Address VARCHAR ( 255 ),
	City VARCHAR ( 255 ),
UNIQUE ( Id_P ) 
)

如果需要命名 UNIQUE 约束，以及为多个列定义 UNIQUE 约束：

CREATE TABLE Persons (
	Id_P INT NOT NULL,
	LastName VARCHAR ( 255 ) NOT NULL,
	FirstName VARCHAR ( 255 ),
	Address VARCHAR ( 255 ),
	City VARCHAR ( 255 ),
CONSTRAINT uc_PersonID UNIQUE ( Id_P, LastName ) 
)

PRIMARY KEY

用于定义基本表的主键。与UNIQUE约束类似，PRIMARY KEY 约束也是通过建立唯一索引来保证基本表在主键列（某一个列或多个列的组合）上取值的唯一性。然而它们之间也存在着很大差别：在一个基本表中只能定义一个 PRIMARY KEY 约束，却能定义多个UNIQUE约束。如果为基本表的某一个列或多个列的组合指定了 PRIMARY KEY 约束，那么其中在任何一个列都不能出现空值；而 UNIQUE 约束允许出现空值。

主键必须包含唯一的值。
主键列不能包含 NULL 值
每个表应该都一个主键，并且每个表只能有一个主键

在 “Persons” 表创建时在 “Id_P” 列创建 PRIMARY KEY 约束：

CREATE TABLE Persons (
	Id_P INT NOT NULL,
	LastName VARCHAR ( 255 ) NOT NULL,
	FirstName VARCHAR ( 255 ),
	Address VARCHAR ( 255 ),
	City VARCHAR ( 255 ),
PRIMARY KEY ( Id_P ) 
)

A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(*key_part*, ...) clause.

如果需要命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束，请使用下面的 SQL 语法：

CREATE TABLE Persons (
	Id_P INT NOT NULL,
	LastName VARCHAR ( 255 ) NOT NULL,
	FirstName VARCHAR ( 255 ),
	Address VARCHAR ( 255 ),
	City VARCHAR ( 255 ),
CONSTRAINT uc_PersonID PRIMARY KEY ( Id_P, LastName ) 
)

CHECK

CHECK 约束用于限制列中的值的范围，既可以定义列也可能定义表。

语法：CONSTRAINT 约束名 CHECK (约束条件);

说明：用于指定基本表中的每一条记录必须满足的条件，可以对基本表在各个列上的值做进一步的约束，如成绩列的取值既不能大于100，也不能小于0。

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制

下面的 SQL 在 “Persons” 表创建时为 “Id_P” 列创建 CHECK 约束。CHECK 约束规定 “Id_P” 列必须只包含大于 0 的整数。

CREATE TABLE Persons (
	Id_P INT NOT NULL,
	LastName VARCHAR ( 255 ) NOT NULL,
	FirstName VARCHAR ( 255 ),
	Address VARCHAR ( 255 ),
	City VARCHAR ( 255 ),
CHECK ( Id_P > 0 ) 
)

如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束，请使用下面的 SQL 语法：

CREATE TABLE Persons (
	Id_P INT NOT NULL,
	LastName VARCHAR ( 255 ) NOT NULL,
	FirstName VARCHAR ( 255 ),
	Address VARCHAR ( 255 ),
	City VARCHAR ( 255 ),
CONSTRAINT chk_Person CHECK ( Id_P > 0 AND City = 'Sandnes' ) 
)

Ref

https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

FOREIGN KEY

Don’t use foreign keys

MySQL foreign key implementation is quite simple and crude, and its performance is not good
It is not recommended to place any computation logic onto MySQL. We take MySQL as backend storage and won’t allow foreign keys or stored routines.
- If our applications rely on the computation logic running on MySQL server, it will be much more difficult for us to do database/table sharding.

KEY / INDEX

KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.

Ref

https://dev.mysql.com/doc/refman/8.0/en/create-table.html

CHARSET - Table collation

Table collation should be utf8mb4_unicode_ci if in doubt.

utf8 vs utf8mb4:

utf8 is max 3 bytes, stores characters in Basic Multilingual Plane (includes most languages).
utf8mb4 is max 4 bytes, stores characters outside BMP, including emojis.
For VARCHAR, no difference when characters in BMP.
For CHAR(M), storage space increased from 3M to 4M bytes regardless, be careful.

general vs unicode:

unicode has accurate string comparison and sorting, but is slightly slower than general.

ci vs cs vs bin:

case insensitive vs case sensitive vs binary.

Ref

http://dev.mysql.com/doc/refman/en/charset.html

`LIKE`

Use CREATE TABLE ... LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:

CREATE TABLE new_tbl LIKE orig_tbl;

The copy is created using the same version of the table storage format as the original table.

通常可用于创建进行了table sharding的table，比如

-- shard this table with sharding factor: 100
CREATE TABLE `seller_rule_channel_map_tab_00000000`
(
    `id`         BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `group_id`   BIGINT(20) UNSIGNED NOT NULL,
    `channel_id` BIGINT(20) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_group_id` (`group_id`)
) ENGINE = INNODB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

create table `seller_rule_channel_map_tab_00000001` like `seller_rule_channel_map_tab_00000000`;
...
create table `seller_rule_channel_map_tab_00000099` like `seller_rule_channel_map_tab_00000000`;

Ref

https://dev.mysql.com/doc/refman/5.7/en/create-table-like.html

DROP - 删除数据表结构和清空数据

以下为删除MySQL数据表的通用语法：

DROP TABLE table_name;

DELETE FROM - 清空数据（不删除数据表）

DELETE FROM table_name [WHERE Clause];

1 删除指定数据

删除表test中年龄等于30的且国家为US的所有数据：

DELETE FROM test WHERE age=30 AND country='US';

2 删除整个表中的数据

删除表test中所有的数据（但保留表的定义）。

DELETE FROM test 或者 DELETE FROM test;
DELETE * FROM test 或者 DELETE * FROM test;

3 总结

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。

TRUNCATE - 清空数据（不删除数据表）

TRUNCATE test;

删除表test中的所有数据，并释放空间，但不删除表结构。

三者区别

delete，drop，truncate 都有删除表中数据的作用，区别在于：

delete 和 truncate 仅仅删除表中所有数据，drop 连表数据和表结构一起删除（此后这张表就不存在了）。
delete 是 DML 语句，因此删除操作完成后，如果不想提交事务还可以回滚。而truncate 和 drop 是 DDL 语句，因此操作完马上生效，不能回滚。
delete 和 truncate相比：
- delete 语句中可以增加 where 条件，而truncate 语句中不能；
- delete 和 truncate 虽然都仅仅删除表中所有数据，但是由于truncate 是 DDL 语句，因此执行完 truncate 语句后，再插入数据时，自增长的数据id又重新从1开始（因为 DDL 语句相当于一切都重新定义了，因此主键 id 计数器会被重置为 1）。

ALTER - 修改数据表名/数据表字段类型及名称

Ref

对表操作

修改表名称

To rename the table from t1 to t2:

ALTER TABLE t1 RENAME t2;

对列操作

删除，添加或修改表字段

删除 column

To delete a column in a table, use the following syntax (notice that some database systems don’t allow deleting a column):

ALTER TABLE *table_name*
DROP COLUMN *column_name*;

使用 ALTER 及 DROP 子句来删除表的 i 字段：

mysql> ALTER TABLE testalter_tbl DROP i;

如果数据表中只剩余一个字段则无法使用DROP来删除字段。

添加 column

MySQL 中使用 ADD 子句来向数据表中添加列，

ALTER TABLE table_name
ADD <column_name> <datatype>;

如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:

mysql> ALTER TABLE testalter_tbl ADD i INT;

执行以上命令后，i 字段会自动添加到数据表字段的末尾。

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

尝试以下 ALTER TABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化：

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

修改字段类型及名称

Ref

https://dev.mysql.com/doc/refman/5.7/en/alter-table-examples.html

修改类型

例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

To change column a from INTEGER to TINYINT NOT NULL (leaving the name the same),

ALTER TABLE t2 MODIFY a TINYINT NOT NULL;

To add a new AUTO_INCREMENT integer column named c:

ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (c);

We indexed c (as a PRIMARY KEY) because AUTO_INCREMENT columns must be indexed, and we declare c as NOT NULL because primary key columns cannot be NULL.

修改类型+名称

使用 CHANGE 子句, 语法有很大的不同。在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Change column b from CHAR(10) to CHAR(20) as well as renaming it from b to c:

ALTER TABLE t2 CHANGE b c CHAR(20);

修改列名

alter table <表名> change <原名> <新名> <类型>;

【MySQL】常用 SQL 语句（Common SQL）- 创建 + 修改