mysql 自身参照自身_Mysql建表语句个人帮助文档

关于mysql建表的一些帮助，包括mysql的各种例如AUTO_INCREMENT，PRIMARY KEY，UNIQUE KEY，FOREIGN KEY，CASCADE，SET NULL，RESTRICT，NO ACTION，ENGINE=InnoDB等解释。

建表语句：

CREATE TABLE [IF NOT EXISTS] table_name(

column_name data_type,

……

);

列

类型、取值范围设定

VARCHAR(20)：设定该字段的类型为可变字符串，可以储存20长度的字符。

FLOAT(8,2)：设定该字段的类型为浮点型，一共有8位，小数点后2位。

UNSIGNED：无符号位。接在数据类型关键字之后，只出现在整数类型和浮点数据类型中，表示该值不存在负值。设置该属性后该字段的正数范围可以扩大一倍。(例如：TINYINT有符号值储存范围为-128到127，TINYINT无符号值储存范围为0到255)

非空约束

NOT NULL：表示该字段不可为NULL，即不可为空。

自动增长

AUTO_INCREMENT：只能用于修饰主键字段，同时数据类型必须为整形或无小数位的浮点型。

默认约束

DEFAULT：为字段设置默认值。

CREATE TABLE tb1(

id INT DEFAULT 0,

name VARCHAR(20) DEFAULT NULL

);

主键约束

PRIMARY KEY：规定一个字段为主键，主键要求有以下特点：

每张表只能存在一个主键

主键保证记录的唯一性

主键自动为NOT NULL

使用方式示例1：

CREATE TABLE tb1(

id INT UNSIGNED AUTOINCREMENT PRIMARY KEY,

name VARCHAR(20)

);

使用方式示例2：

CREATE TABLE tb1(

id INT UNSIGNED AUTO_INCREMENT,

name VARCHAR(20),

PRIMARY KEY(id)

)

唯一约束

UNIQUE KEY：规定的字段受到唯一约束，唯一约束有以下特点：

唯一约束可以保证记录的唯一性

唯一约束的字段可以为空值(只能存在一个空值)

每张数据表尅存在多个唯一约束

使用方式示例：

CREATE TABLE tb1(

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(20) NOT NULL UNIQUE KEY

);

外键约束(物理外键约束，日常开发推荐使用逻辑外键约束)

UNIQUE KEY：为一个列添加外键约束，外键约束有以下特点：

父表和子表必须使用相同的储存引擎，而且禁止使用临时表。

数据表的储存引擎只能为InnoDB

外键列和参照列必须有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。

外键列和参照列必须创建索引。如果外键列不存在索引的话，Mysql向自动创建索引。

使用方式示例：

先创建一个省份表(父表)：

CREATE TABLE provinces(

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

pname VARCHAR(20) NOT NULL

);

再创建一个用户表(子表)：

CREATE TABLE users(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(10) NOT NULL,

pid SMALLINT UNSIGNED,

FOREIGN KEY(pid) REFERENCES provinces(id)

);

外键约束的参照条件

CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行。

SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL。

RESTRICT：拒绝对父表的删除或更新操作。

NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同。

使用方式示例：

先创建一个省份表(父表)：

CREATE TABLE provinces(

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

pname VARCHAR(20) NOT NULL

);

再创建一个用户表(子表)：

CREATE TABLE users(

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(10) NOT NULL,

pid SMALLINT UNSIGNED,

FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE

);

以上用户表在删除时，将受到CASCADE形式的外键约束。

查看索引

SHOW INDEXES FROM (columeName);

查看索引信息

SHOW INDEXES FROM (columeName)\G;

表

ENGINE=InnoDB：指定储存使用InnoDB引擎。

InnoDB引擎是一个提供外键约束的数据存储引擎，提供事务和行锁等。尽管当前很多版本中InnoDB为默认的储存引擎，但是在建表时加入该配置仍然是一个好习惯。

AUTO_INCREMENT=100001：当表中的主键设置了自动增长，可以在这里设置自动增长的初始值，默认为1。

DEFAULT CHARSET=utf8：设置表的编码为utf8。

示例：

CREATE TABLE tb1(

id INT AUTOINCREMENT PRIMARY KEY,

name VARCHAR(20)

) ENGINE=InnoDB AUTOINCREMENT=100001 DEFAULT CHARSET=utf8;

查看数据表

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]

查看数据表结构(1)：

SHOW COLUMNS FROM tb1_name

查看数据表结构(2)：

DESC tb1_name

查看建表语句：

SHOW CREATE TABLE (columeName)

删除表：

DROP TABLE tb1_name

设置数据库的默认储存引擎为InnoDB：

1. 打开mysql安装路径下的my.ini配置文件，加入或修改如下项：

default-storage-engine=INNODB

2. 重新启动mysql