三、MySQL数据类型与操作数据表

1、数据类型

• 数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的数据类型

• 整形

数据类型	存储范围	字节
TINYINT	0~255	1
SMALLINT	0~65535	2
MEDIUMINT	0~16777215	3
INT	0~4294967295	4
BIGINT	0~18446744073709551615	8

• 浮点型

数据类型	存储范围
FLOAT[(M,D)]	01.175494351E-383.402823466E+38
DOUBLE[(M,D)]	02.2250738585072014E-3081.7976931348623157E+308

• M是数字总位数，D是小数点后面的位数、如果M和D被省略，根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位

• 日期时间型

列类型	存储需求
YEAR	1
TIME	3
DATE	3
DATETIME	8
TIMESTAMP	4

• 字符型

列类型	存储需求
CHAR(M)	M个字节，0 <= M <= 255
VARCHAR(M)	L+1个字节，其中 L <= M 且 0 <= M <= 65535
TINYTEXT	L+1个字节，其中 L < 256
TEXT	L+2个字节，其中 L < 65536
MEDIUMTEXT	L+3个字节，其中 L < 16777216
LONGTEXT	L+4个字节，其中 L < 4294967296
ENUM('value1','value2',...)	1或2个字节，取决于枚举值的个数（最多65,535个值）
SET('value1','value2',...)	1、2、3、4或者8个字节，取决于set成员的数目（最多64个成员）

• 数据表（或称表）是数据库最重要的组成部分之一，是基础对象的基础
• 打开数据库
• USE 数据库名称
• 创建数据表

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type,
    ....
)

• 查看数据表列表

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

• 查看数据表结构

SHOW COLUMNS FROM tbl_name

• 插入记录

INSERT [INTO] tbl_name [(col_name),...] VALUES(val,...)

• 记录查找

SELECT expr,... FROM tbl_name

• NULL，字段值可以为空
• NOT NULL，字段值禁止为空
• AUTO_INCREMENT
• 自动编号，且必须与主键组合使用
• 默认情况下，起始值为1，每次的增量为1
• PRIMARY KEY
• 主键约束
• 每张数据表只能存在一个主键
• 主键保证记录的唯一性
• 主键自动为NOT NULL
• UNIQUE KEY
• 唯一约束
• 唯一约束可以保证记录的唯一性
• 唯一约束的字段可以为空值（NULL)
• 每张数据表可以存在多个唯一约束
• DEFAULT
• 默认值
• 当插入记录时，如果没有明确为字段赋值，则自动赋予默认值

2、约束

• 约束保证数据的完整性和一致性
• 约束分为表级约束和行级约束
• 约束类型包括：
  • NOT NULL（非空约束)
  • PRIMARY KEY（主键约束）
  • UNIQUE KEY（唯一约束）
  • DEFAULT（默认约束）
  • FOREIGN KEY（外键约束）
• FOREIGN KEY（外键约束）
  • 保持数据一致性，完整性
  • 实现一对一或一对多关系
• 外键约束的要求
  • 父表和子表必须使用相同的存储引擎，而且禁止使用临时表
  • 数据表的存储引擎只能为InnoDB
  • 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同
  • 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。
• 外键约束的参照操作
  • CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行
  • SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
  • RESTRICT：拒绝对父表的删除或更新操作
  • NO ACTION：标准SQL关键字，在MySQL中与RESTRICT相同
• 表级约束与列级约束
  • 对一个数据列建立的约束，称为列级约束
  • 对多个数据列建立的约束，称为表级约束
  • 列级约束既可以在列定义时声明，也可以在列定义后声明
  • 表级约束只能在列定义后声明
• 添加单列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definiton [FIRST|AFTER col_name]

• 添加多列

ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...)

• 删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name

• 添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) 

• 添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,..)

• 添加/删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

• 删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY

• 删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

• 删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

• 修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]

• 修改列名称

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

• 数据表更名
  • 方法一

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name

• ​ 方法二

RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]...

• 约束
  • 按功能划为：NOT NULL，PRIMARY KEY，UNIQUE KEY，DEFAULT，FOREIGN KEY
  • 按数据列的数目划为：表级约束、列级约束
• 修改数据表
  • 针对字段的操作：添加/删除字段、修改列定义，修改列名称等
  • 针对约束的操作：添加/删除各种约束
  • 针对数据表的操作：数据表更名（两种方式）

​