Chapter3 数据定义语言(DDL)
3.1 库和表的管理
3.1.1 创建库
CREATE DATABASE IF NOT EXISTS books;
3.1.2 修改库
- 修改库名(已废弃)
RENAME DATABASE books TO 新库名;
- 更改字符集
ALTER DATABASE books CHARACTER SET gbk;
3.1.3 库的删除
DROP DATABASE IF EXISTS books;
3.1.4 表的创建
- 语法
CREATE TABLE book( 列名 类型 [(长度)(约束)], 列名 类型 [(长度)(约束)], ... )
- 示例
CREATE TABLE author( id INT, au_name VARCHAR(20), nation VARCHAR(10) )
3.1.5 表的修改
- 修改列名
ALTER TABLE 表名 CHANGE COLUMN 列名 新列名 类型;
- 修改列的类型或约束
ALTER TABLE 表名 MODIFY COLUMN 列名 类型;
- 添加新列
ALTER TABLE 表名 ADD COLUMN 列名 类型;
- 删除列
ALTER TABLE 表名 DROP COLUMN 列名;
- 修改表名
ALTER TABLE 表名 RENAME TO 新表名
3.1.6 表的删除
- 命令
DROP TABLE IF EXISTS 表名;
3.1.7 表的复制
- 仅仅复制表的结构
CREATE TABLE 新表名 LIKE 原表名
- 复制表的结构+数据
CREATE TABLE 新表名 SELECT * FROM 原表;
- 复制部分数据
CREATE TABLE 新表名 SELECT 字段1, 字段2, ... FROM 原表 WHERE 条件;
- 复制部分字段(列)
CREATE TABLE 新表名 SELECT 字段1, 字段2, ... FROM 原表 WHERE 0;
3.2 常见数据类型
3.2.1 整型
- 分类
tinyint、smallint、mediumint、int/integer、bigint
- 特点
- 默认有符号,无符号用 unsigned定义:
INT UNSIGNED
- 插入数值超出范围会报错(老版本会插入临界值)
- 若不设置长度则会有默认长度,代表显示的宽度,必须搭配
zerofill
使用(不够使用0填充)INT(7) ZEROFILL,
- 默认有符号,无符号用 unsigned定义:
3.2.2 浮点型
- 分类
- 浮点型
- float(M,D)
- double(M,D)
- 定点型
- dec(M,D)
- decimal(M,D)
- 浮点型
- 特点
- M与D
- M:整数位数+小数位数
- 小数位数
- M与D可省略
- decimal:M=10,D=0
- 浮点型:根据输入数值
- M与D
3.2.3 字符型
- 分类
- 短文本
- char:固定长度
- varchar:可变长度
- 长文本
- text
- blob
- 短文本
- 使用
- 长度
- char(M), M可省略,默认为1
- varchar(M), M不能省略
- 其他
- binary、varbinary:用于保存较短的二进制
- enum:保存枚举
- set:保存集合
- 长度
3.2.4 日期型
- 分类
- date:日期
- time:时间
- year:年
- datetime:日期+时间
- timestamp:日期+时间
- 特点
- datetime
- 8字节
- 1000-9999
- 不受时区影响
- timestamp
- 4字节
- 1970-2038
- 受时区影响
- datetime
3.3 常见约束
3.3.1 分类
NOT NULL
:非空DEFAULT
:默认,保证有默认值PRIMARY KEY
:主键,具有唯一性,非空UNIQUE
:唯一,可以为空CHECK
:检查约束(mysql不支持)FOREIGN
:外键,限制两个表的关系,保证该字段值来自主表关联列
3.3.2 使用
- 添加时机
- 创建表时
- 修改表时
- 分类
- 列级约束:六个皆可,但外键约束没有效果
- 表级约束:除了非空、默认
- 主键与唯一键
类型 唯一性 能否为空 能否多个 允许组合 主键 是 否 否 允许, 不推荐 唯一键 是 是 是 允许, 不推荐 - 外键
- 在从表设置外键关系
- 从表列与主表关联列类型要求一致或兼容
- 主表关联列必须是KEY(一般是主键或唯一)
- 插入数据时,先插入主表再插入从表,删除数据时,先删从表再删主表
3.3.3 代码示例
创建表时
# 1. 列级约束 CREATE TABLE student( id INT PRIMARY KEY, # 主键 stuName VARCHAR(20) NOT NULL, # 非空 gender CHAR(1) CHECK(gender='男' OR gender='女'), # 检查 seat INT UNIQUE, # 唯一 age INT DEFAULT 18 # 默认 )
CREATE TABLE student( id INT, stuName VARCHAR(20), gender CHAR(1), seat INT, age INT, majorid INT, CONSTRAINT pk PRIMARY KEY(id), # 主键 CONSTRAINT uq UNIQUE(seat), #唯一键 CONSTRAINT ck CHECK gender='男' OR gender='女'), # 检查 CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id), # 外键 ); # pk uq等为键名,可以省略 # 主键一般命名为 fk_从表_主表
修改表时添加约束
- 列级约束
ALTER TABLE 表名 MODIFY COLUMN 列名 类型 约束
- 表级约束
ALTER TABLE 表名 ADD [CONSTRAINT 键名] 约束
- 列级约束
修改表时删除约束
# 1. 删除非空约束 ALTER TABLE 表名 MODIFY COLUMN 列名 类型; # 2. 删除默认约束 ALTER TABLE 表名 MODIFY COLUMN 列名 类型; # 3. 删除主键 ALTER TABLE 表名 DROP PRIMARY KEY; # 4. 删除唯一 ALTER TABLE 表名 DROP INDEX 键名; # 5. 删除外键 ALTER TABLE DROP FOREIGN KEY 外键名;
3.3.4 标识列
- 说明
也叫自增长列,系统提供默认序列值 - 特点
- 必须是一个可以且只有一个
- 标识列只能是数值型
- 步长可以通过
SET auto_increment_increment = N设置
,起始值可以通过手动插入设置 - 插入数据时,标识列可以为NULL、数值或直接省略