Mysql表结构管理(约束的添加删除修改)

MySQL数据类型

数据类型	取值范围	说明	单位
TINYINT	有符号值： -128 ~ 127 无符号值：0~255	最小的整数	1字节
BIT	有符号值： -128 ~ 127 无符号值：0~255	最小的整数	1字节
BOOL	有符号值： -128 ~ 127 无符号值：0~255	最小的整数	1字节
SMALLINT	有符号值： -32768~ 32767 无符号值：0~65535	小型整数	2字节
MODIUMINT	有符号值： -8388608~ 8388607 无符号值：0~16777215	中型整数	3字节
TINYINT	有符号值： -2147683648~ 2147683647 无符号值：0~4294967295	标准整数	4字节
TINYINT	有符号值： -9223372036854775808~ 9223372036854775807 无符号值：0~18446774073709551615	大整数	8字节

浮点数据类型

数据类型	单位
FLOAT	4或者8字节
DOUBLE	双精度8字节
DECIMAL	可变，自定义长度

此处省略n行…

创建表

创建表使用CREATE TABLLE 语句，语法

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 数据表名
[(create_definition,...)] [table_options] [select_statement]

参数说明

关键字	说明
TEMPORARY	如果使用该关键字则创建一个临时表
IF NOT EXISTS	避免存在表错误
create_definition	这是表的列属性部分。MySQL要求在创建表的时候，表至少要包含一列
table_options	表的一些特性参数
select_statement	SELECT 语句描述部分，用它可以快速的创建表

下面介绍列属性create_definition部分

col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]

属性说明

参数	说明
col_name	字段名
type	字段类型
NOT NULL	NULL
DEFAULT default_value	表示默认值
AUTO_INCREMENT	表示是否自动编号，每一个表只能有一个AUTO_INCREMENT列，并且必须被索引
PRIMARY KEY	表示是否为主键，一个表只能有一个主键。如果没有主键当外部程序需要时，会返回一个没有任何NULL 列的UNIQUE 键作为主键
reference_definition	为字段添加注释

基本的创建表实例

具体格式

CREATE TABLE table_name (列名1 属性，列名2 属性，···);

CREATE TABLE student(
name varchar(20),
number int,
age int NOT NULL,
sex bool
);

执行效果如下图

设置默认的储存引擎

ENGINE=储存引擎名称

CREATE TABLE student(
name varchar(20),
number int,
age int NOT NULL,
sex bool
)ENGINE=MyISAM;

设置自增类型字段

CREATE TABLE student(
name varchar(20),
number int AUTO_INCREMENT,
age int NOT NULL,
sex bool
)ENGINE=MyISAM;

设置字符集

CREATE TABLE student(
name varchar(20),
number int AUTO_INCREMENT,
age int NOT NULL,
sex bool
)DEFAULT CHARSET=GBK;

复制表结构

CREATE TABLE [IF NOT EXISTS] 数据表名
{LIKE 源数据表名 | （LIKE　源数据表名）}

参数	说明
IF NOT EXISTS	可选项，表示要创建的表是否存在
数据表名	表示要创建的数据表的名称
{LIKE　源数据表名｜（LIKE 源数据表名）}	必选项，用于指定依照那个数据表来创建新表

example

CREATE  TABLE new_table
	LIKE tb_old;

修改表结构

修改字段

ALTER [IGNORE] TABLE 数据库表名 alter_space[,alter_space]... 

其中的IGNORE指定时，如果出现重复关键的行，则只会执行一次，多余的行会删除

其中alter_space字段摘要及语法

摘要	解释
ADD [COLUMN] create_definition [FIRST l AFTER column_name	添加新字段
ADD INDEX [index_name] (index_col_name,…)	添加索引名称
ADD PRIMARY KEY [index_col_name,…]	添加主键名称
ADD UNIQUE [index_col_name,…]	添加唯一索引
ALTER [COLUMN] col_name {SET DEAFAULT literal} l DROP DEAFAULT}	修改字段名称
MODIFY [COLUMN] create_definition	修改子句定义字段
‘’‘	’‘’

example

ALTER TABLE student ADD TRANSLATOR varchar（30）NOT NULL,
MODIFY inTime DATETIME(6);

修改约束条件

约束类型	说明
PRIMARY KEY	主键约束
DEAFAULT	默认约束
UNIQUE KEY	唯一约束
NOT　NULL	非空约束
FOREＩGN KEY	外键约束

添加约束条件

example

ALTER　TABLE　td_name　ADD　CONSTRAINT mrprimary PRIMARY KEY(col_name);

删除约束条件

• 删除主键约束条件

ALTER　TABLE　表名　DROP　PRIMARY　KEY

• 删除外键约束

ALTER TABLE 表名 　DROP　FOREIGN　KEY　约束名

example

ALTER　TABLE　student　DROP　FOREIGN KEY mrfkey;

• 删除唯一约束

ALTER　TABLE　表名　DROP　INDEX　唯一索引名

example

ALTER TABLE　student　DROP　INDEX mrindex;

修改其他选项

• 修改表的储存引擎

ALTER　TABLE　表名　ENGINE＝新的储存引擎类型

• 修改表的字符集

ALTER　TABLE　表名　DEFAULT　CHARSET＝新的字符集

• 修改表的自增类型字段的初始值

ALTER TABLE 表名  AUTO_INCREMENT ==新的初始值

修改表名

RENAME TABLE 表名1 TO 表名2

删除表

DROP TABLE 表名

设置索引

索引是一种数据库中单列或者多列的值进行排序的结构.由单列或多列组成,可以优化查询速度

创建索引

• 创建表时创建索引

CREATE TABLE　table_name(
属性名 数据类型 [约束条件],
属性名 数据类型 [约束条件],
...
属性名 数据类型 
[UNIQUE | FULLTEXT |SPATIAL] INDEX | KEY
[别名](属性名1[(长度)] [ ASC | DESC])
);

参数说明

参数	说明
UNIQUE	可选项,表明是否为唯一索引
FULLTEXT	可选,表明索引为全文搜索
SPATIAL	可选,表明索引为空间索引
属性名1	指索引对应的字段名称,该字段必须被预先定义才可以使用
长度	可选参数,指定索引的长度,必须是字符串类型才可使用
ASC ｜DESC	可选参数,ASC表示升序排列,DESC表示降序排列

example:创建student表,并为id字段上建立索引

CREATE TABLE student(
id int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
name varchar(50),
math int(5),
index(id)
);

结束后可以使用SHOW CREATE TABLE 表名查看表结构

• 在已经创建的表中创建索引

CREATE [UNIQUE | FULLTEXT |SPATIAL ] INDEXindex_name
ON　table_name(属性 [(length)] [ASC | DESC]);

• 删除索引

 DROP INDEX　index_name ON table_name;

定义约束

• 定义主键约束

每个表只能有一个主键约束,可以是一个列也可以是多个列构成的一个组合.多个列构成的称为复合主键,而且遵循唯一性原则不能为空,也不能重复

example

CREATE TABLE student (
 name varchar(10) PRIMARY KEY
 );

• 定义候选约束

如果一个属性集能唯一标识元组,且又不含多余的属性,那么这个属性集称为关系的候选键,值也必须是唯一的,不能为空,使用UNIQUE关键字来定义

• 定义非空约束

可以使用: NULL | NOT NULL

• 定义CHECK约束

可以按照需求对列和表约束

CHECK(expr)

其中expr是一个SQL表达式,用于指定需要检查的限定条件

• 对列实施CHECK约束

在创建表的时候在相应的字段属性里面加上CHECK语句就行了

• 对于表的CHECK约束

直接在表的字段中添加CHECK约束字段

参考书籍: MySQL数据库管理与开发(慕课版)