五、数据库之DDL（数据定义语言）

1、库的管理

一、创建库

create database 【if not exists】 库名【 character set 字符集名】;

二、修改库

alter database 库名 character set 字符集名;

• 不是修改库名，而是修改字符集

三、删除库

drop database 【if exists】 库名;

2、表的管理

一、创建表 ★

create table 【if not exists】 表名(
	字段名 字段类型（长度） 【约束】,
	字段名 字段类型（长度） 【约束】,
	。。。
	字段名 字段类型（长度） 【约束】 #这里没有逗号
)

二、修改表

1. 添加列

   alter table 表名 add column 列名 类型 【first|after 字段名】;
   

2. 修改列的类型或约束

   alter table 表名 modify column 列名 新类型 【新约束】;
   

3. 修改列名

   alter table 表名 change column 旧列名 新列名 类型;
   

4. 删除列

   alter table 表名 drop column 列名;
   

5. 修改表名

   alter table 表名 rename 【to】 新表名;
   

三、删除表

drop table【if exists】 表名;

四、复制表

1、复制表的结构

create table 表名 like 旧表;

2、复制表的结构+数据

create table 表名 
select 查询列表 
from 旧表
【where 筛选】;

• 用*就是复制所以字段+数据；
• 字段1，字段2…就是复制部分字段+数据；
• 把where条件恒不成立（1=2或0或 false）就可以复制字段而不复制数据
• 夸库执行的时候，加上前缀★

3、数据类型

一、数值型

1、整型

类型	占用字节
tinyint	1byte=8bit
smallint	2
mediumint	3
int/integer	4
bigint	8

• 特点：

  ①、都可以设置无符号和有符号，默认有符号，通过int unsigned设置无符号
  ②、如果超出了范围，会报out of range异常，会插入临界值
  ③、长度可以不指定，默认会有一个长度
  ④、长度不代表范围（因为范围已经通过不同的int决定了），代表显示的最大宽度，如果不够则左边用0填充，但需要搭配int zerofill，并且默认变为无符号整型

2、浮点型

• 定点数：decimal/dec(M,D)

• 浮点数:
  float(M,D) 4
  double(M,D) 8

• 特点：
  ①、M代表整数部位+小数部位的个数，D代表小数部位
  ②、如果超出范围，则报out or range异常，并且插入临界值
  ③、M和D都可以省略，浮点数插入不受限制，只要在范围内就原样输入，但对于定点数，M默认为10，D默认为0
  ④、如果精度要求较高，则优先考虑使用定点数

3、使用原则

• 简单
• 根据范围选择类型越小越好

二、字符型

char、varchar、binary、varbinary、blob（较长的二进制）enum（枚举）、set（集合）、text（文本）

字符	特点	空间耗费	效率	应用场景
char	固定长度，M省略时默认为1	耗费多	高	固定长度，如性别
varchar	可变长度，M不能省略	耗费少	低	长度变化多，如姓名

三、日期型

• year年
• date日期
• time时间
• datetime 日期+时间：8字节
• timestamp 日期+时间：4 字节，比较容易受时区、语法模式、mysql版本的影响，更能反映当前时区的真实时间

4、约束

一、常见的约束

1. NOT NULL：非空，该字段的值必填
2. UNIQUE：唯一，该字段的值不可重复
3. DEFAULT：默认，该字段的值不用手动插入，有默认值
4. CHECK：检查，mysql不支持（语法不报错，但是没效果），比如限制性别为男女
5. PRIMARY KEY：主键，该字段的值不可重复并且非空 unique+not null
6. FOREIGN KEY：外键，从表该字段的值引用了并且必须来自于主表的字段

• 主键和唯一键的异同【面试题】★
  1、区别：
  ①、一个表至多有一个主键，但可以有多个唯一
  ②、主键不允许为空，唯一可以为空（但是也只能有一个null）
  2、相同点
  都具有唯一性
  都支持组合键，但不推荐
• 外键：
  1. 在从表设置外间关系
  2. 用于限制两个表的关系，从表的字段值引用了主表的某字段值
  3. 外键列和主表的被引用列要求类型一致，意义一样，名称无要求
  4. 主表的被引用列要求是一个key（一般就是主键）
  5. 插入数据，先插入主表；删除数据，先删除从表
  6. 可以通过以下两种方式来删除主表的记录

二、创建表时添加约束

create table 表名(
	字段名 字段类型 not null,#非空
	字段名 字段类型 primary key,#主键
	字段名 字段类型 unique,#唯一
	字段名 字段类型 default 值,#默认
	constraint 约束名 foreign key(字段名) references 主表（被引用列）
)
#设置通用的写法，即，外键约束用标记约束，其他的都用列级约束
#约束名建议写成：自己起的名字_本表名_引用表名

注意：

约束分类	支持类型	可否起约束名
列级约束	都支持（但是外键在myqsl无效）	不可
标记约束	不支持非空、默认	可以（对主键无效）

说明：列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求

三、修改表时添加或删除约束

1、非空

添加非空

alter table 表名 modify column 字段名 字段类型 not null;

删除非空

alter table 表名 modify column 字段名 字段类型【null】 ;

2、默认

添加默认

alter table 表名 modify column 字段名 字段类型 default 值;

删除默认

alter table 表名 modify column 字段名 字段类型 ;

3、主键

添加主键

alter table 表名 add【 constraint 约束名】 primary key(字段名);

删除主键

alter table 表名 drop primary key;

4、唯一

添加唯一

alter table 表名 add【 constraint 约束名】 unique(字段名);

删除唯一

alter table 表名 drop index 索引名;

5、外键

添加外键

alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）;

删除外键

alter table 表名 drop foreign key 约束名;

6、总结

添加列级约束

alter table 表名 modify column 字段名 字段类型 新约束

添加表级约束

alter table 表名 add 【constraint 约束名】 约束类型（字段名）【外键的引用】

四、自增长列/标识列

1、特点：

​ 1、不用手动插入值，可以自动提供序列值，默认从1开始，步长为1

• 如果要更改起始值：手动插入值

• 如果要更改步长：更改系统变量：set auto_increment_increment=值;

  2、一个表至多有一个自增长列
  ​ 3、自增长列只能支持数值型
  ​ 4、自增长列必须为一个key

2、语法

#一、创建表时设置自增长列
create table 表(
	字段名 字段类型 约束 auto_increment
)

#二、修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment

#三、删除自增长列
alter table 表 modify column 字段名 字段类型 约束