MySQL-数据定义语言DDL-datebase define language-create-alter-drop-约束

库的增改删

库创建create database

语法

create database [ is not exists] 库名
[character set 字符集名];

理解
is not exists 如果不存在就创建；

案例

库修改alter database

语法：修改字符集

alter database 库名 character set 字符集名;

修改库名

rename database 旧库名 to 新库名;
# 一般都是在mysql中的data文件中找到数据库文件夹直接修改名字。

库删除 trop database

语法

drop database [if exists] 库名;

案例

字符集、字符序

概念
在数据存储上，MySQL提供了不同的字符集支持；
在数据的对比操作上，提供了不同的字符序支持；

字符集 = 定义了字符以及字符的编码
字符序 = 字符的比较规则

MySQL 支持多种字符集 与 字符序。
一个字符集至少对应一个字符序（一般为一对多）；
两个不同的字符集不能有相同字符序；
每个字符集都有默认的字符序；

常见sql命令

• 查看支持的字符集

show character set;

use information_schema;
select * from character_sets;

显示字段有：character_set_name 、 default_collate_name、description、maxlen

• 查看支持的字符序

show collation; 

use information_schema;
select * from collations;

备注
information_schema 用于保存源数据的信息；

表的增改删

表创建create table

语法

create table [is not exists] 表名（
	字段名 字段类型【长度 约束】，
	字段名 字段类型【长度 约束】，
	.....
	字段名 字段类型【长度 约束】，
）;

案例
一个字符是一个字符，一个汉字也是一个字符；

表修改alter table

语法

要求	sql语句
添加新的列	alter table 表名 add column 列名 列类型 [约束]; alter table 表名 add column 列名 列类型 [ first / later 字段名] ; fisrt表示新字段添加到第一列；later字段名表示添加新字段到字段名后
删除列	alter table 表名 drop column 列名;
修改列名	alter table 表名 change column 旧列名 新列名 类型;
修改列类型 或 约束	alter table 表名 modify column 列名 新类型 新约束;
修改字段默认数据	alter table 表名 alter column 列名 set default 默认值;
修改表名	alter table 表名 rename [to] 新表名;

案例

表删除drop table

语法

drop table [if exists] 表名；

# if exists 容错需求。

案例

表的复制

语法

需求	sql语句
仅仅复制表结构	create table 新表名 like 旧表名
复制结构 + 全部数据	create table 新表名 select * from 旧表
复制部分结构 + 部分数据	create table 新表名 select 部分字段 from 旧表 where 筛选条件
仅仅复制某些字段 没有数据	create table 新表 select 某些字段 from 旧表 where false

备注
可以跨库复制，需要使用的是库名.表的形式。

案例

库表常见写法

drop database [is exists] 库名;
drop table [is exists] 表名;

create database 库名;
create table 表名;

练习

约束

概念
约束，是一种限制，用于限制表中的数据，确保表中数据的准确性和可靠性。

分类：六大约束

约束	含义	作用	案例
not null	非空	保证该字段的值不为空	比如姓名、学号等
default	默认约束	保证该字段有默认值	比如性别
primary key	主键	保证该字段的值具有唯一性，且非空	学号、员工编号
unique	唯一约束	保证该字段的值具有唯一性，可以为空	座位号
check	检查约束	检查数据表中字段值有效性	性别、年龄
foreign key	外键	限制两个表的关系，保证该字段值的值必须来自于主表的关联列的值 在从表添加外键约束，用于引用主表中某列的值。	学生表的专业编号、员工表的部分编号、员工表的工种编号

添加约束时机
创建表、修改表

创建表添加约束

列级约束、表级约束

列级约束

书写位置
直接在字段名和类型后面追加约束类型即可。

常见约束
默认（default）、非空（not null）、主键（primary key）、唯一（unique）。
六大约束语法上都支持，但是外键约束没有效果。

语法

create table 表名（
	字段名 字段类型 列级约束，
	字段名 字段类型 列级约束，
	.....
）

常见约束写法

约束	具体写法
not null	字段名 字段类型 not null
primary key	字段名 字段类型 primary key
unique	字段名 字段类型 unique
default	字段名 字段类型 default 某值
check	字段名 字段类型 check （字段 in（值1，值2））

案例

备注2022/8/1
命令show index from 表 # 输出该表中的所有索引。

表级约束

语法

在各个字段的最下面 [constraint 约束名] 约束类型 （字段名）

create table 表名（
	字段名 字段类型，
	字段民 字段类型，
	[constraint 约束名] 约束类型 （字段名），
	[constraint 约束名] 约束类型 （字段名），
）

常见约束
除了非空（not null）、默认（default），其他都支持

约束书写

约束	具体书写
primary key	primary key（字段名）
unique	unique （字段名）
foreign key	foreign key（字段名）
check	check（字段名 in（值1，值2））

案例

通用写法

列级约束：unique、not null、default、primary key、check
表级约束：foreign key

create table 表名（
	字段名 字段类型 primary key，
	字段名 字段类型 default，
	字段名 字段类型 not null，
	字段名 字段类型 unique，
	检查字段名 字段类型 check（检查字段 in（值1，值2））
	外键字段 字段类型，
	constraint 约束名 foreign key（外键字段）references 所依赖的表（字段）
）;

列级约束与表级约束比较

约束	列级	表级
位置	列字段类型后面	所有列的后面
支持约束	所有约束都支持，但外键没有效果	默认和非空不支持，其他都支持
约束起别名	不可以	可以，但主键约束没有效果

主键与唯一比较

约束	主键	唯一
是否具有唯一性	是	是
是否允许为空	否	是（只允许一个为空）
表中存在个数	最多一个	可以有多个
是否允许组合	是，但不推荐	是，但不推荐

理解
唯一性中可以为空。只允许列中有一个为空，如果出现多个则会报错。

字段组合

• 概念
  就是表级约束书写中共括号内是多个字段。

• 语法：

primary key （字段1，字段2...）
unique（字段1，字段2....）

• 案例：
  

外键的特点

语法

create table 从表（
	从表字段名 字段类型，
	[constraint 约束名] foreign key（从表字段名） references 主表（主表字段名）
）;

特点

用于限制两个表的关系，从表的字段值引用了主表的某字段值。
从表外键列的类型和主表的被引用列的类型要求一致或兼容，名称无所谓。
主表的被引用列必须是一个key（一般是主键或唯一键）。
插入数据时，先插入主表，再插入从表。（先得有信息）
删除信息时，先删除从表，再删除主表。（先得删除引用）

删除主表的记录

• 方式1 级联删除

alter table 从表名 add constraint 约束名 foreign key（外键列） references 主表（被引用列） on delete cascade;

删除从表的外键列的某个值（a值）
效果：从表外键列的a值被删除；主表被引用列中含有a值的行被删除；

• 方式2 级联置空

alter table 从表名 add constraint 约束名 foreign key（外键列） references 主表（被引用列） on delete set null;

修改表添加约束

添加列级约束 语法

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

添加表级约束 语法

alter table 表名 add[constraint 约束名] 约束类型（字段名） [外键的引用references 主表（主表列）];

具体约束添加

# 主键约束
alter table 表名 modify column 字段名 字段类型 primary key;
# 非空性约束
alter table 表名 modify column 字段名 字段类型 not null;
# 默认约束
alter table 表名 modify column 字段名 字段类型 default 默认值;

# 唯一性约束 列级
alter table 表名 modify column 字段名 字段类型 unique;
# 唯一性约束 表级
alter table 表名 add unique（字段名）;

# 外键约束
alter table 从表 add foreign key（字段名） references 主表（主表字段名）;

案例

修改表时删除约束

语法

# 删除not null、default、primary key
alter table 表名 modify column 字段名 字段类型;

# 删除primary key、unique、foreign key
alter table 表名 drop primary key/index 字段名 /foreign key 约束名;

案例

标识列（自增长列）

概述

含义

可以不用手动的插入值，系统提供默认的序列值。

特点

• 标识列必须是一个key。
• 一个表至多有一个自增长列。
• 标识列的类型：只能是数值型，int、float、double都可以。
• 不用手动插入值，可以自动提供序列值，默认从1开始，步长为1，
  通过这个（set auto_increment_increment）可以看到。
• 如果要更改起始值：手动插入值；
  如果要更改步长：更改系统变量。
  set auto_increment_increment = 3；设置步长

设置标识列

创建表时设置 语法

create table 表名（
	字段 字段类型 约束 auto_increment,
	.....
）

创建表时设置 案例

备注

# 查询 auto_increment标识列
show variables like "%auto_increment%"

# 修改自增长的步长
set auto_increment_increment = 具体值;

修改表时设置标识列 语法

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

删除表时删除标识列 语法

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