MySql的创建数据表、约束、外键约束的创建修改删除、级联操作

DDL 数据定义语言
创建：create
修改：alert
删除：drop

对数据库的操作

• 数据库的创建：create database 数据库名;
• 数据库如果不存在就创建：create database if not exists 数据库名;
• 更改数据库的字符集：alert database 数据库名 character set gdk/utf8;
• 数据库的删除：drop database if exists 数据库名;

数据表的操作

数据表的创建：

create table 表名(
	字段名 类型 约束,
	字段名 类型 约束,
	字段名 类型 约束,
	...
	字段名 类型 约束
)

表的修改

-- 修改字段名 
alert table 数据表名 change column 原始字段名 新字段名 类型;

-- 修改列的类型或约束
alert table 数据表名 modify column 字段名 新类型 [新约束];

-- 添加一个字段
alert table 数据表名 add column 字段名 类型 [约束];
-- 一个字段默认的添加在表的最后的，如果我想让添加的列再表的某个位置需要加上一些东西
alert table 数据表名 add column 字段名 类型 [first | after 字段名]   -- 这里是表示添加在最前面 或者是 添加在某个字段后

-- 删除一个字段
alert table 数据表名 drop column 字段名;

-- 修改表名
alert table 数据表名 rename to 新数据表名

表的删除

drop table 表名;

表的复制

-- 仅仅复制表的结构
create table 新表名 like 已存在的表名;

-- 复制表的结构+数据
create table 新表名
select * from 已存在的表名;

-- 还可以只复制部分列与部分行，就直接在select后面写要复制的字段名，想要复制部分行就在查询语句后面加where条件

数据类型

整形：

• tinyint 占用一个字节
• smallint 占用两个字节
• mediumint 占用三个字节
• ing 占用四个字节
• bigint 占用八个字节

注意点：

1. 整形的取值范围是有上面的类型决定的，并且有无符号和有符号的区别，就比如上面的tinyint类型，无符号的取值范围是256~256，而有符号的取值范围是-127~127
2. 如果在创建数据表是，设置字段类型时，如果不指定有符号还是无符号，默认是有符号的，如果想无符号则需要加上unsigned关键字
3. 如果新增的值，超过了该类型的最大取值，则会将新增的值变为该类型的临界值
4. 对于整形的数据，如果不指定长度会有默认的长度
5. 类型决定了取值范围，而长度决定是否需要在前面补0，默认情况下是不会补0的，如果想要需要加上zerofill关键字

小数

• 浮点型：
  • float(M,D) 占4字节
  • double(M,D) 占8字节
• 定点型：
  • dec(M,D) 或者 decimal(M,D) 占M+2字节, 取值范围和double相同，但是保存的精度比double要精确一点

注意点：

1. M代表小数点前和小数点后的总位数，D代表小数点后保留几位，如果超过了指定的位数则进行四舍五入
2. M和D都可以省略，如果是float和double类型默认就没有M和D了，但dec默认是dec(10,0)

字符型

• 较短的文本
  • char(M) ， M个字符数，可以省略，默认为1
  • varchar(M) ，M个字符数，不可以省略
  • binary
  • varbinary
  • enum
  • set
• 较长的文本
  • text
  • blob 该类型是保存文件的二进制

char 和varchar的区别是： char代表的是固定长度的字符， varchar代表的是可变长度的字符 ， 也就是说假如定义一个cahr(5) ，这时候插入一个字符a ，但其实它还是占5个字符的空间，但如果是varchar(5) 那么它就只占一个字符的空间。 但是使用char性能要比varchar的性能高一些。

binary和varbinary是用来保存较短的二进制

对于枚举类型，比如创建一个字段，该字段的值就只能是枚举中规定的值

create table tab_char(
	age ENUM('男','女')
);

set类型 和枚举很像，不同点就是枚举只能是我们规定的值中的一个，而set可以有多个

create table tab_set(
	s1 set('a','b','c','d')
);

-- 插入数据 如果是字母则不区分大小写，枚举也不区分大小写
insert into tab_set values('a');
insert into tab_set values('A');
insert into tab_set values('a,b,c');

日期类型

这里主要是讲解datatime 和 timestamp 的区别，timestamp主要的区别就是会受时区的影响来改变数据库中存储的值。

案例如下

-- 创建一个表
create table tab_date(
	t1 datatime,
	t2 timestamp
)

-- 新增数据，两个字段都插入当前时间
insert into tab_date values(now(),now());

-- 这个时候查询数据库得到的结果都是一样的 2021-09-21 17:54:12
select * from tab_date;

-- 查看当前数据库使用的时区
show variables like 'time_zone';
-- 再改变时区
set time_zone='+9:00';

-- 这个时候再执行一遍查询语句，就会发现第二个字段的值自动变化了，
-- 还有就是timestamp类型的范围是1970年--2038年

约束

基本知识

常见约束如下：

• primary key 主键约束
• unique 唯一约束
• not null 非空约束
• default 默认约束
• check 检查约束 mysql不支持 比如年龄和性别都可以使用该约束
• auto_increment 主键自增

约束的分类：

• 列级约束：上面的约束都支持 但外键约束没有效果
• 表级约束：除非空约束，默认约束。其他都支持

列级约束语法：

create table 表名(
	字段名 类型 约束,
    字段名 类型 约束,
    ...
    字段名 类型 约束
)

表级约束语法：

create table 表名(
	字段名 类型,
    字段名 类型,
    ...
    字段名 类型，
    
    [constraint 自己随便起一个约束名] 约束类型(字段名)
)

约束名也可以省略不写，也会有默认的名字。

查看约束的语法：show index from 表名

主键约束和唯一约束的区别是：

• 主键约束不能存在null，但唯一约束允许存在一个null
• 主键约束表中最多只能有一个，唯一约束有多个。

组合

就拿主键来举例，可以把两个字段组合为一个主键，也就是当两行数据中，这两列都要相同才会冲突。但是不推荐使用组合

create table 表名(
	id int,
    name varchar(10),
    ...
    
    constraint XXX约束名 PRIMARY EKY(id,name)
)

修改表是添加约束

1. 添加列级外键约束

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

2. 添加表级约束

alert table 表名 [constraint 自己随便起一个约束名] 新约束(字段);

外键约束

外键约束其实就是让表与表之间产生关系，进而保证数据的正确性。也可以说成保证数据的参照完整性。

-- 创建外键约束
create table 表名(
	字段名 类型,
    字段名 类型,
    ...
    字段名 类型，
    
    constraint 外键约束名称 foreign key(外键列名称) references 主表名称(主表列名称)
    -- 外键约束名称一般的命名规则： fk_当前表名_主表名_外键列名称
);

-- 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 我们随便起的外键名称

-- 创建表之后添加外键
ALTER TABLE 表名 ADD constraint 随便起一个外键名称 foreign key (外键列名称) references主表名称 (主表列名称)

注意事项：

• 要求在从表上设置外键约束
• 从表外键约束字段类型要和主表的那个字段类型相同
• 要去主表上的关联列必须是一个key，一般是主键或唯一
• 插入数据时，应该删插入主表再插入从表，删除数据时，应该先删除从表在删除主表

级联操作

就是有两个表，添加了外键约束，当其中一个表的外键那个字段修改了，另一个表也就跟着自动修改

-- 添加级联的语句是在添加外键语句的后面添加 ON UPDATE CASCADE  设置级联更新
-- 具体代码如下：
ALTER TABLE 表名 ADD constraint 
外键名称 foreign key (外键列名称) references主表名称 (主表列名称) ON UPDATE CASCADE;

-- 添加外键 设置级联更新 然后再设置级联删除
ALTER TABLE 表名 ADD constraint 
外键名称 foreign key (外键列名称) references主表名称 (主表列名称) ON UPDATE CASCADE ON DELECT CASCADE;

-- 级联置空 删除主表时不要删除对应从表的数据 而是将外键列置空
ALTER TABLE 表名 ADD constraint 
外键名称 foreign key (外键列名称) references主表名称 (主表列名称) ON DELECT SET NULL;

• 级联更新：就是修改这个表外键的数值，另一个表也跟着修改
• 级联删除：就是删除这个表外键的一行，另一个表所有用到了这个外键行的数据也跟着修改