MySQL之DDL 数据库、表的管理

7. DDL

数据定义语言 分为库和表的管理

创建： create

修改： alter (update是修改表的数据，这个是修改表的结构)

删除： drop (删除的是整个库或表)

1. 库的管理

1. 库的创建

syntax：

create database [if not exists] 库名;

database 是库的关键字 [if not exists] 是为了提高创建库是的容错 只有在库表操作中可以，在列操作中不可以

2. 库的修改

可以更改库的字符集

syntax:

alter databse 库名 character set gbk/utf8;

还可以修改库名，但是在8.0之后就不支持修改库名的sql语句(不安全可能会导致数据丢失)，但是确实想修改也可以在上面库的新增中的路径去修改文件的名字，然后重启数据库服务。😊

3. 库的删除

syntax：

drop database [if exists] 库名;

2. 表的管理

1. 表的创建 【重点】

syntax：

create table [if not exists] 表名(

​ 列名 列的类型[(长度) 约束等], #例如varchar(20) 20个字符，一个字母是一个字符，一个汉字也是一个字符

​ …)；

2. 表的修改

syntax:

alter table 表名 add|drop|modify|change column 列名 [列的类型 约束];

分别是 新值列|删除列|修改列的类型|改变列名

column 是列的关键字

可以在修改列的时候顺便修改列的类型约束

1.修改列名

alter table 表名 change column 旧列名 新列名 这一列的类型; # 必须要将列的类型加上

其中列的关键字 column 可以省略

2.修改列的类型或约束

alter table boys modify column ip varchar(10); # 将ip列的类型修改为varchar

其中列的关键字 column 可以省略

3.添加新列

alter table boys add column ip1 int; # 默认是放到最后一列

alter table boys add column ip1 int first; # 最后的first是将新增的ip1列放到第一列的位置

alter table boys add column ip2 int after ip1; # 最后的after ip1是将新增的ip2列放到ip1列的后面

其中列的关键字 column 不可以省略

4.删除列

alter table boys drop column ip2;

其中列的关键字 column 可以省略

5.修改表名

alter table old_table_name rename to new_table_name;

alter table boys rename to boy;

3. 表的删除

drop table [if exists] 表名;

4. 表的复制

需要创建一个新表，但是和已有的一个表的结构一样，就可以使用复制了

1.仅仅复制表的结构

create table 表名 like 被复制的表名;

2.复制表的结构 + 数据

create table 表名 select * from 被复制的表名; # 相当于一个子查询

可以跨库复制表： 库名.表名

也可以只复制列： select 要复制的列

3.只复制部分数据

create table 表名 select * from 被复制的表名 where 筛选出要复制的内容; # 就是子查询加个筛选

4.仅仅复制某些字段(就是复制部分表的结构不带数据)

create table 表名 select 想要复制的字段 from 被复制的表名 select 一个永远为false的筛选条件;

筛选条件可以是 0，代表否。

例如： [狗头保命]

create table table_name select id,name from old_table_name select you_have_girlfriend;

3. 常见的数据类型

• 数值型：
  • 整型
  • 小数：
    • 定点数
    • 浮点数
• 字符型：
  • 较短的文本：char，varchar
  • 较长的文本：text，blob（较长的二进制数据）
• 日期型：

1. 整型

分类：

名称： tinyint、smallint、mediumint、int/integer、bigint

字节长度： 1 2 3 4 8

特点：

1. 如果不设置无符号还是有符号，默认是有符号。如果想设置无符号，需要添加 unsigned 关键字

2. 如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值

3. 如果不设置长度，会有默认的长度

   长度代表的是显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！如果加了zerofill就会自动将整型设置为无符号整型

create table if not exists test_table(
	t1 int,				# 有符号整型 默认长度11位
    t2 int unsigned,	# 无符号整型 默认长度10位
    t3 int(7),			# 有符号整型 不够7位不会自动填充0
    t3 int(7) zerofill,	# 无符号整型 不够7位会自动填充0
) 

2. 小数

1.浮点型

浮点数类型	字节	范围
float	4	±1.75494351E-38~±3.402823466E+38
double	8	±2.2250738585072014E-308~±1.7976931348623157E308

float(M, D) # 4个字节

double(M, D) # 8个字节

2.定点型

定点数类型	字节	范围
DEC(M,D)/DECIMAL(M,D)	M+2	最大取值范围与double形同，给定decimal的有效取值范围有M和D决定

dec(M, D) # 是下边的简写

decimal(M, D) # 是上边的全称 都是一个意思

特点：

1. M：整数部位 + 小数部位 D：小数部位

   如果超过范围，则插入临界值(就是大于最大数插入M和D指定的最大范围，小于最小数就插入0)

2. M和D都可以省略

   如果是decimal，则M默认为10，D默认为0

   如果是float、double，则会根据插入的数值的精度来决定精度

3. 定点型的精确度较高，如果要求插入数值的精度较高如 **货币运算 **等则考虑使用

选择数值使用那个类型的原则：

1. 所选择的类型越简单越好，能保存数值的类型越小越好

3. 字符型

1. 较短的文本：

	写法	M的意思	特点	空间	效率
char	char(M)	最大字符数，默认是1	固定长度	耗费	高
varchar	varchar(M)	最大字符数，不可省略	可变长度	节省	低

字符数：不是字节数，一个英文、中文都是1个字符

固定长度：就是你插入的字符数不够你指定的字符数时，也占用你指定的字符数

可边长度：是指根据你插入的字符数确定长度

位类型	字节	范围
bit(M)	1-8	bit(1)-bit(8)

binary和varbinary类型： 二进制存储

类似于char和varchar，不同的是它们包含二进制字符串而不包含非二进制字符串

enum类型： 类似单选

又称为枚举类型，要求插入的值必须属于列表中指定的值之一。如果列表成员为1-255，则需要一字节存储。如果列表成员为255-65535，则需要两个字节存储，最多需要65535个成员。

create table test_enum(
	cl enum("a","b","c")   # 创建枚举类型的列  指定值是a,b,c
) ;
insert into test_enum values("c")   # 插入成功
insert into test_enum values("2");  # 这个代表插入的是指定值的第二个，不带引号也可以
insert into test_enum values("w");  # 会报错，“w”不在指定的值中

set类型： 类似多选

和enum类型类似，可以保存0-64个成员。和enum类型最大的区别是：set类型一次可以选取多个成员，而enum只能选一个。根据成员个数不同，存储所占的字节也不同。

create table test_set(
	aihao set("唱","跳","rap","篮球")
);
insert into test_set values("唱,跳,rap,篮球");  # 这个插入只需要一个引号包裹，英文逗号分隔

2. 较长的文本：

text blob

4. 日期型

要求日期的值必须要用引号包起来

查看时区： show variables like “time_zone”;

修改时区： set time_zone="+9:00"; # 东九区，会比北京时间快一个小时

日期和时间类型	字节	最小值	最大值	时区和mysql版本
date	4	1000-01-01	9999-12-31
datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59	不受影响
timestamp	4	19700101 080001	2038年的某个时刻	受影响
time	3	-838:59:59	838:59:59
year	1	1901	2155

timestamp受时区影响指的是根据msyql的时区，会自动更改为对应时区的时间，更加准确。

create table test_date(
	t1 datetime,
    t2 timestamp
);
insert into test_date values(now(),now());

show variables like "time_zone";
set time_zone="+9:00";

少年易老学难成，一寸光阴不可轻