个人博客
0%

【MySQL】DDL使用

发表于 分类于

DDL：Data Denfinition Language，数据定义语言

1 创建表

1.1 语法格式

1
2
3
4
5
6
create table 表名 (
	字段名1 数据类型 约束,
	字段名2 数据类型 约束,
	字段名3 数据类型 约束,
	...
);
  • 一般表名都是以t_或tbl_来表示这是表，与后面学习的视图进行区分

1.2 常用数据类型

类型 描述
char(长度) 定长字符串，存储空间大小固定，适合作为主键或外键
varcher(长度) 变长字符串，存储空间等于实际数据空间
double(有效数字位数, 小数位) 数值型
float(有效数字位数, 小数位) 数值型
int(长度) 整型
bigint(长度) 长整型
date 日期型：年月日
datetime 日期型：年月日 时分秒 毫秒
blob 二进制大对象(binart large object)
clob 字符大对象(character large object)
  • char 和 varchar 选择
  • char固定长度，无论传入数据的字符串多长，都会分配指定长度的空间来存储
  • varchar可变长度，根据传入的数据的大小，分配对应的空间进行存储
  • 一般情况都是使用varchar，但例如性别只有男、女两种选项就可以使用char(1)的数据类型，速度较快

1.3 表的复制

1
2
create table 表名 
as 查询结果
  • 将查询结果作为一张新表并创建出来，该表的数据为查询结果数据

1.4 常见约束

  • （1）非空约束（not null）
    • 不允许数据为null
1
2
3
create table 表名(
	字段名 数据类型 not null
)
  • （2）唯一性约束（unique）
    • 修饰的字段不能重复，但可以为null
1
2
3
4
5
6
7
8
9
10
11
12
# 正常使用（列级约束）
create table 表名(
	字段名 数据类型 unique
)

# 多字段联合唯一性（表级约束）
# （不能字段1和字段2完全一样，允许字段1一样，字段2不一样）
create table 表名(
	字段名1 数据类型,
	字段名2 数据类型,
	unique(字段名1,, 字段名2)
)
  • （3）主键约束（primary key）
    • 主键不能为空，并不能重复
    • 主键是一个行数据的唯一标识，类似身份证作用
    • 一张表只能有一个主键
1
2
3
4
5
6
7
8
9
10
11
# 单一主键
create table 表名(
	字段名 数据类型 primary key [auto_increment(主键自增)]
)

# 复合主键（不建议用，违背表的三范式）
create table 表名(
	字段名1 数据类型,
	字段名2 数据类型,
	primary key(字段名1, 字段名2)
)
  • （4）外键约束（foregin key）
    • 添加外键约束，规定该字段的值是其他表上的某字段的值，不能随意填写
    • 外键可以为null
    • 外键引用的键必须要有唯一性
    • 使用外键的表称为子表，被引用外键的表被称为父表
    • 注意：
      • 删除数据的时候，先删除子表，再删除父表
      • 添加数据的时候，先添加父表，再添加子表
      • 创建表的时候，先创建父表，再创建子表
      • 删除表的时候，先删除子表，再删除父表
1
2
3
4
5
6
7
8
9
10
# 父表
create table 父表(
	字段1 数据类型
)

# 子表
create table 子表(
	字段2 数据类型,
	foregin key(字段2) references 父表(字段1)
)

1.5 存储引擎

  • 存储引擎：表的存储方式
1
2
3
create table 表名(
	xxxxxx
)engine = 存储引擎 default charset = 字符集;
  • show engines \g：可查看mysql支持的存储引擎
  • 常用存储引擎
  • MyISAM：
    • 是mysql最常用的存储引擎，但不是默认引擎
    • 使用是三个文件表示每个表：（1）格式文件-存储表结构的定义(frm)；（2）数据文件-存储表行的内容（MYD）；（3）索引文件-存储表上索引（MYI）
    • 优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率
    • 缺点：不支持事务
  • InnoDB：
    • 表的结构存储在frm文件中，数据逻辑存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读
    • InnoDB在MySQL数据库崩溃之后提供自动恢复机制
    • 优点：支持事务，行级锁，外键，数据的安全得到保障
  • MEMORY：
    • （1）每个表均以.frm格式的文件表示
    • （2）表数据及索引被存储在内存中
    • （3）表级锁机制
    • （4）不能包含text或blob字段

2 修改表

  • （1）修改字段名
1
2
alter table 表名
change column 旧字段名 新字段名 数据类型;
  • （2）修改字段的数据类型 或 约束
1
2
alter table 表名
modify column 字段名 新数据类型;
  • （3）添加或删除字段
1
2
3
4
5
6
7
# 添加字段
alter table 表名
add column 字段名 数据类型 约束;

# 删除字段
alter table 表名
drop column 字段名;
  • （4）修改表名
1
2
alter table 表名
rename to 新表名
  • （5）总结
1
2
alter table 表名
add|drop|modify|change column 列名 【列类型 约束】;

3 删除表

  • 格式：drop table 表名

  • 删除常用指令：drop table if exists 表名，如果表名存在才删除