MySQL基本命令及参数(DDL)

数据库的基本操作

创建数据库

查看当前所有数据库:

SHOW DATABASES;

创建数据库是在系统磁盘上划分一块区域用于数据的存储和管理，如果管理员在设置权限的时候为用户创建了数据库，就可以直接使用，否则需要自己创建数据库:

CREATE DATABASE 数据库名;

数据库创建好之后，可以查看数据库的定义:

SHOW CREATE DATABASE 数据库名;

删除数据库

DROP DATABASE 数据库名;

数据表的基本操作

创建数据表

CREATE TABLE 表名
(
    字段名1 数据类型 列级别约束条件 默认值,
    字段名2 数据类型 列级别约束条件 默认值,
    ...
    表级别约束条件
);

主键约束

主键又称主码，是表中一列或多列的组合。
主键约束(Primary Key Constraint)要求主键列的数据存在且唯一。主键能够唯一的标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。

单字段主键

• 在定义列的同时指定主键

CREATE TABLE 表名
(
    字段名1 数据类型 PRIMARY KEY,
    字段名2 数据类型,
    ...
);

• 在定义完所有列之后指定主键

CREATE TABLE 表名
(
    字段名1 数据类型,
    字段名2 数据类型,
    ...,
    PRIMARY KEY(字段名)
);

多字段联合主键

CREATE TABLE 表名
(
    字段名1 数据类型,
    字段名2 数据类型,
    ...,
    PRIMARY KEY(字段名i,字段名j,...)
);

外键约束

外键约束用来在两个表的数据之间建立链接,它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另外一个表中主键的某个值。
外键：首先是表中的一个字段，它可以不是本表的主键，但对应另一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。
主表(父表)：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。
从表(子表)：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

CREATE TABLE 从表
(
    字段名1 数据类型,
    字段名2 数据类型,
    ...
    CONSTRAINT 外键名 FOREIGN KEY(字段名i,字段名k,...) REFERENCES 主表名(主键列)
);

删除表的外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

非空约束

非空约束(Not Null Constraint)指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库就会报错。

CREATE TABLE 表名
(
    字段名1 数据类型 not null,
    字段名2 数据类型,
    ...
);

唯一约束

唯一约束(Unique Constraint)要求该列唯一，允许为空，但是仅能出现一个空值。唯一约束可以确保一列或者多列不出现重复值，多列时多列同时相同判定为相同。

CREATE TABLE 表名
(
    字段名1 数据类型,
    字段名2 数据类型,
    ...,
    CONSTRAINT 约束名 UNIQUE(字段名i,字段名j,...)
);

UNIQUE和PRIMARY KEY的区别：

一个表中可以有多个字段声明为UNIQUE,但只能有一个PRIMARY KEY声明；声明为PRIMARY KEY的列不允许有空值，但声明为UNIQUE的字段允许空值存在。

默认约束

默认约束(Default Constraint)指定某列的默认值。如果插入一条新的记录时没有为这个值赋值，则自动补充为默认值。

CREATE TABLE 表名
(
    字段名1 数据类型,
    字段名2 数据类型,
    ...,
    字段名i 数据类型 DEFAULT 默认值,
    ...
);

设置表的属性值自动增加

在数据库中，可以通过设置属性值自动增加来实现系统自动生成字段的主键值。一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数类型(TINYINT、SMALLIN、INT、BIGINT等)。

CREATE TABLE 表名
(
    id INT PRIMARY KEY AUTO_INCREAMENT,
    字段名2 数据类型,
    ...
);

查看数据表结构

DESCRIBE 表名;
DESC 表名;

输出结果释义:

列名	含义
NULL	表示该列是否可以存储NULL值
Key	表示该列是否已编制索引。PRI表示该列是表主键的一部分;UNI表示该列是UNIQUE索引的一部分;MUL表示在列中某个给定值允许出现多次
Default	表示该列是否有默认值，如果有的话值是多少
Extra	表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等

查看表详细结构

SHOW CREATE TABLE 表名;

表的修改

修改表名

ALTER TABLE 旧表名 RENAME TO 新表名;

其中TO为可选参数，使用与否不影响结果。

修改字段的数据类型

ALTER TABLE 表名 MODIFY 字段名 数据类型;

修改字段名

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;

添加字段

ALTER TABLE 表名 ADD 新字段名 数据类型 约束条件 FIRST|AFTER已存在的字段名;

其中FIRST为可选参数，作用是将新添加的字段设置为表的第一个字段；AFTER为可选参数，作用是将新添加的字段添加到指定已存在的字段名的后面。

删除字段

ALTER TABLE 表名 DROP 字段名;

修改字段的排列位置

ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2;

字段名1指要修改该位置的字段；数据类型指字段名1的数据类型；FIRST为可选参数，可将字段名1修改为表的第一个字段；AFTER 字段名2，可将字段名1放到字段名2的后面。

更改表的存储引擎

ALTER TABLE 表名 ENGINE=存储引擎名;

MySQL支持的存储引擎

引擎名	是否支持
FEDERATED	否
MRG MYISAM	是
MyISAM	是
BLACKHOLE	是
CSV	是
MEMORY	是
ARCHIVE	是
InnoDB	是，默认的存储引擎
PERFORMANCE_SCHEMA	是

删除数据表

删除没有被关联的表

DROP TABLE IF EXISTS 表1,表2,..,表n;

参数IF EXISTS用于在删除前判断被删除的表是否存在，加上参数后，在删除表时，如果表不存在，SQL语句可以顺利执行，但是会发出警告。

删除被其他表关联的主表

数据表存在外键关联的情况下，如果直接删除父表，结果会显示失败，原因是直接删除将破坏表的参照完整性。如果必须删除，可先删除子表再删除父表。但很多情况需要保留子表，所以就需要先将关联的表的外键约束条件取消，就可以正常操作了。

ALTER TABLE 子表 DROP FOREIGN KEY 外键名;
DROP TABLE 父表;

MySQL的数据类型

MySQL支持多种数据类型:

数据类型	类型	内容
数值数据类型	整数类型	TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
	浮点小数	FLOAT、DOUBLE
	定点小数	DECIMAL
日期/时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
字符串类型	CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET

整数类型

类型名称	说明	存储需求	有符号	无符号
TINYINT	很小的整数	1字节	-128~127	0~255
SMALLINT	小的整数	2字节	-32768~32767	0~65535
MEDIUMINT	中等大小的整数	3字节	-8388608~8388607	0~16777215
INT(INTEGER)	普通大小的整数	4字节	-2147483648~2147483647	0~4294967295
BIGINT	大整数	8字节	-9223372036854775808~9223372036854775807	0~18446744073709551615

浮点数类型和定点数类型

类型名称	说明	存储需求	有符号	无符号
FLOAT	单精度浮点数	4字节	-3.402823466E+38~-1.175494351E-38	0~3.402823466E+38
DOUBLE	双精度浮点数	8字节	-1.7976931348623157E+308~-2.2250738585072014E-308	0和2.2250738585072014E-308~-1.7976931348623157E+308
DECIMAL(M,D)	压缩的“严格”定点数	M+2字节

DECIMAL类型不同于FLOAT和DOUBLE，DECIMAL实际是以串存放的，DECIMAL可能的最大取值范围与DOUBLE一样，但是起有效的取值范围是由M和D决定的。

日期与时间类型

类型名称	日期格式	日期范围	存储需求	介绍
YEAR	YYYY	1901~2155	1字节	(1)以4位字符串或4位数字格式表示的YEAR，范围为'1901'~'2155'输入'YYYY'或YYYY均可 (2)以2位字符串格式表示的YEAR，范围为'00'~'99'，'00'~'69'被转换为2000~2069，'70'~'99'被转换为'1970'~'1999'
TIME	HH:MM:SS	-838:59:59~838:59:59	3字节	(1)'D HH:MM:SS'格式的字符串可以以以下任何一种非严格的语法表示:'HH:MM:SS'、'HH:MM'、'D HH:MM'、'D HH'或'SS'。这里D表示日，可以取0~34之间的值，在插入数据库时，D被转换为小时保存，格式为"D*24+HH" (2)'HHMMSS'格式的、没有间隔字符的字符串或者HHMMSS格式的数值，假定是有意义的时间可以被插入，不合法的存储为00:00:00
DATE	YYYY-MM-DD	1000-01-01~9999-12-3	3字节	(1)以'YY-MM-DD'或者'YYMMDD'字符串格式表示的日期，MySQL对于两位年值得规则:'00~69'表示'2000~2069','70~99'表示'1970~1999' (2)使用CURRENT_DATE或者NOW()插入当前系统日期
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59	8字节	同上时间2位年值转换方法
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC	4字节	除了与DATETIME存储字节和支持范围不同外，DATETIME存储日期数据时，按实际输入格式存储与时区无关；TIMESTAMP存储以UTC(世界标准时间)格式存储，存储时对当前时区进行转换

文本字符串类型

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此L<=M和1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此L<2^8
TEXT	小的非二进制字符串	L+2字节，在此L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目(最大值为65535)
SET	一个设置，字符串对象可以有零个或多个SET成员	1、2、3、4或8字节，取决于集合成员的数量(最多64个成员)

CHAR和VARCHAR类型

CHAR(M)为固定长度字符串，在定义时指定字符串列长。VARCHAR(M)是长度可变的字符串，M表示最大列长度。

CHAR(4)与VARCHAR(4)存储的区别

插入值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
''	'   '	4字节	''	1字节
'ab'	'ab '	4字节	'ab'	3字节
'abc'	'abc'	4字节	'abc'	4字节
'abcd'	'abcd'	4字节	'abcd'	5字节
'abcdef'	'abcd'	4字节	'abcd'	5字节

ENUM类型

ENUM是一个字符串对象，其值为表创建时规定中枚举的一列值。

CREATE TABLE 表名
(
    字段名 ENUM('值1','值2','值3',...,'值n'),
    ...
);

ENUM类型的取值范围

值	索引
NULL	NULL
''	0
值1	1
值2	2
值3	3
...

SET类型

SET是一个字符串对象，可以有零个或多个值，SET列最多可以有64个成员，其值为创建时规定的一列值。

CREATE TABLE 表名
(
    字段1 SET('值1','值2',...,'值n')，
    ...
);

例：

CREATE TABLE tmp1(s SET('a','b,'c','d'));
INSERT INTO tmp1 values('a'),('a,b,a'),('c,a,d');
INSERT INTO tmp1 values('a,x,b,y');
-- 插入'a'结果为;'a,b,a'结果为'a,b';'c,a,d'结果为'a,c,d';'a,x,b,y'直接报错

二进制字符串类型

类型名称	说明	存储需求
BIT(M)	位字段类型	大约(M+7)/8字节
BINARY(M)	固定长度二进制字符串	M字节
VARBINARY(M)	可变长度二进制字符串	M+1字节
TINYBLOB(M)	非常小的BLOB	L+1字节，在此L<2^8
BLOB(M)	小BLOB	L+2字节，在此L<2^16
MEDIUMBLOB(M)	中等大小的BLOB	L+3字节，在此L<2^24
LONGBLOB(M)	非常大的BLOB	L+4字节，在此L<2^32