MySQL详解3.DDL语法
点击进入我的博客
1 数据库
1.1 创建数据库
创建语句
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification]
create_specification:
- 字符集设置：
[DEFAULT] CHARACTER SET [=] charset_name或者简写为[DEFAULT] CHARSET [=] charset_name。一般为utf8或utf8mb4。 - 排序的规则：
[default] COLLATE [=] collation_name。一般为utf8_unicode_ci。对于MySQL中那些字符类型的列，如VARCHAR，CHAR，TEXT类型的列，都需要有一个COLLATE类型来告知MySQL如何对该列进行排序和比较。简而言之，COLLATE会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响DISTINCT、GROUP BY、HAVING语句的查询结果。另外，MySQL建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。
{A|B} 表示选择其中之一；[XXX]表示[]中的内容是可选的
1.2 删除数据库
DROP DATABASE 库名
1.3 选择数据库
USE 库名
2 数据类型
2.1 整型
image.png
整型后面的数字INT(M)
- 整数型的数值类型已经限制了取值范围，有符号整型和无符号整型都有，而M值并不代表可以存储的数值字符长度，它代表的是数据在显示时显示的最小长度.
- 当存储的字符长度超过M值时，没有任何的影响，只要不超过数值类型限制的范围；
- 当存储的字符长度小于M值时，只有在设置了ZEROFILL用0来填充，
ALTER TABLE tb_name MODIFY COLUMN col_name INT(5) ZEROFILL;才能够看到效果。换句话就是说，没有ZEROFILL，M值就是无用的。
放入数值超出范围
- 默认情况下不会报错，如果插入的超出了范围，那么会插入最大/最小值。
- 如果限制为严格模式
STRICT_ALL_TABLES，插入超出范围会报错
2.2 定点数
DECIMAL和NUMERIC类型在MySQL中视为相同的类型。它们用于保存必须为确切精度的值。
(M,D)的细节
- DECIMAL(M,D)，其中M表示十进制数字总的个数，D表示小数点后面数字的位数。
- M的取值范围为1~65，取0时会被设为默认值，超出范围会报错。
- D的取值范围为0~30，而且必须<=M，超出范围会报错。
- M的默认取值为10，D默认取值为0。
2.3 浮点数
浮点数是用来表示实数的一种方法，它用 M(尾数) * B( 基数)的E(指数）次方来表示实数，相对于定点数来说，在长度一定的情况下，具有表示数据范围大的特点。
- FLOAT(M,D)：占4个字节（1位符号位 8位表示指数 23位表示尾数）
- DOUBLE(M,D)：占8个字节（1位符号位 11位表示指数 52位表示尾数）
(M,D)的细节
- (M,D)表示一共显示M位整数，其中D位位于小数点后面
- FLOAT和DOUBLE中的M和D的取值默认都为0，即除了最大最小值，不限制位数；
- M取值范围为[0, 255]
- D取值范围为[0, 30]，同时必须<=M
- FLOAT只保证6位有效数字的准确性，所以FLOAT(M,D)中，M<=6时，数字通常是准确的。如果M和D都有明确定义，其超出范围后的处理同DECIMAL。
- DOUBLE只保证16位有效数字的准确性，所以DOUBLE(M,D)中，M<=16时，数字通常是准确的。如果M和D都有明确定义，其超出范围后的处理同DECIMAL。
2.4 CHAR和VARCHAR类型
CHAR类型是定长的，CHAR值会根据需要采用空格进行填充以方便比较。
VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。
VARCHAR的存储空间
- VARCHAR需要使用1或2个额外字节记录字符串的长度
- 如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。
- InnoDB引擎会把过长的VARCHAR存储为BLOB
字符串末尾的空格
- VARCHAR：在5.0或者更高版本，MySQL在存储和检索时会保留末尾空格。但在之前的版本， MySQL会剔除末尾空格。
- CHAR：会删除所有的末尾空格。
2.5 BINARY和VARBINARY
BINARY和VARBINARY类型类似于CHAR和VARCHAR类型，而是二进制字符串，所以它们没有字符集，并且排序和比较基于列值字节的数值值。二进制字符串和常规字符串非常相似，但它存储的是字节码而不是字符。
填充与裁剪
- 当保存BINARY值时，在它们右边填充0X00(零字节)值以达到指定长度。取值时不删除尾部的字节。
- 对于VARBINARY，插入时不填充字符，选择时不裁剪字节。
2.6 BLOB和TEXT
BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
具体类型
- 它们分别属于两组不同的数据类型家族，字符类型是TINYTEXT、SMALLTEXT、TEXT,、MEDIUMTEXT、LONGTEXT；对应的二进制类型是TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、L0NGBLOB
- BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。
BLOB/TEXT与VARBINARY/VARCHAR异同点
- 当保存或检索BLOB和TEXT列的值时不删除尾部空格。(这与VARBINARY和VARCHAR列相同）。
- 比较时将用空格对TEXT进行扩充以适合比较的对象，正如CHAR和VARCHAR。
- 对于BLOB和TEXT列的索引，必须指定索引前缀的长度。对于CHAR和VARCHAR，前缀长度是可选的。
- BLOB和TEXT列不能有默认值。
2.7 ENUM
MySql中的ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。ENUM最多可以有65,535个元素。
空字符串和NULL
- 如果你将一个非法值插入ENUM(即允许的值列之外的字符串)，将插入空字符串以作为特殊错误值。该字符串与“普通”空字符串不同，该字符串有数值0。
- 如果将ENUM列声明为允许NULL，NULL值则为该列的一个有效值，并且默认值为NULL。如果ENUM列被声明为NOT NULL，其默认值为允许的值列的第1个元素。
值的索引规则
- 来自列规定的允许的值列中的值从1开始编号。
- 空字符串错误值的索引值是0。
SELECT * FROM tb_name WHERE enum_col=0 - NULL值的索引是NULL。
- ENUM值根据索引编号进行排序。例如，对于ENUM('A'，'B')，'A'排在'B'前面，但对于ENUM('B'，'A')，'B'排在'A'前面。
- 空字符串排在非空字符串前面，并且NULL值排在所有其它枚举值前面。
2.8 位数据类型
SET类型
SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。SET最多可以设置64个值。
BIT类型
BIT数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1~64，默认为1。
如果存入一个BIT类型的值，位数少于M值，则左补0。
2.9 日期时间类型
DATETIME和TIMESTAMP
- 当你需要同时包含日期和时间信息的值时则使用DATETIME类型。MySQL以
YYYY-MM-DD HH:MM:SS格式检索和显示DATETIME值。支持的范围为1000-01-01 00:00:00到9999-12-31 23:59:59。 - TIMESTAMP类型同样包含日期和时间，范围从
1970-01-01 00:00:01 UTC到2038-01-19 03:14:07 UTC。
DATE
当你只需要日期值而不需要时间部分时应使用DATE类型。MySQL用YYYY-MM-DD格式检索和显示DATE值。支持的范围是1000-01-01到 9999-12-31。
TIME
MySQL以HH:MM:SS格式检索和显示TIME值(或对于大的小时值采用HHH:MM:SS格式)。
YEAR
YEAR类型是一个单字节类型用于表示年。MySQL以YYYY格式检索和显示YEAR值。范围是1901到2155。
3 表操作
3.1 创建数据表
CREATE TABLE `tb_name` (
`col_name` 字段类型 [字段约束]
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.2 删除数据表
DROP TABLE 表名;
3.3 清空数据表
TRUNCATE 表名
4 字段操作
4.1 增加字段
ALTER TABLE 表名 ADD COLUMN 列名 字段类型 [约束条件]
4.2 删除字段
ALTER TABLE 表名 DROP COLUMN 列名
4.3 修改字段
修改字段名
ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 [约束条件]
修改字段属性
ALTER TABLE 表名 MODIFY 字段名 字段类型 [约束条件]
