五、DDL语言
简介
DDL:数据定义语言,库和表的管理
库的管理:
- 创建
- 修改
- 删除
表的管理:
- 创建
- 修改
- 删除
命令:
- 创建:create
- 修改:alter
- 删除:drop
库的管理
创建
语法
普通:create datebase 库名
提高容错性:create datebase if not exists 库名
修改(一旦创建库,一般不修改)
语法
重命名:rename database 库名 to 新库名; (已经废弃)
修改字符集:alter database 库名 character set 字符集;
删除
语法
删除库:
- drop database 库名;(不安全)
- drop database if exists 库名;(安全)
表的管理
创建
语法
create table is not exists 表名(
列名 列的数据类型【(类型长度) 约束】,
列名 列的数据类型【(类型长度) 约束】,
......
列名 列的数据类型【(类型长度) 约束】
);
修改
修改列名
alter table 表名 change column 旧列名 新列名 数据类型;
列的类型或约束
修改某一列的数据类型:
alter table 表名 modify column 列名 新数据类型;
添加列
alter table 表名 add column 新列名 数据类型;
alter table 表名 add column 新列名 数据类型 【first|after 字段名】; 调整字段顺序
删除列
alter table 表名 drop column 列名;
修改表名
alter table 表名 rename to 新表名;
删除
语法
drop table if exists 表名;
复制
仅复制表结构
语法
create table 新表 like 已存在的表;
复制表结构+数据
语法
create table 新表 select * from 旧表;
复制表结构+部分数据
语法
create table 新表 select * from 旧表 where 筛选条件;
常见约束
含义
一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性
分类
六大约束
- not null(该字段不能为空约束)
- default(默认,用于保证该字段有默认值)
- primary key(主键,用户保证该字段值具有唯一性,非空字段)
- unique(唯一,用于保证该字段的值具有唯一性,可为空)
- check(检查约束,【Mysql不支持,语法不报错,但无效果】)
- poreign key(外键约束,用于限制两个表的关系,该字段值必须来自主表关联字段的值,在从表添加外键约束,用于引用主表中某列的值)
添加分类
- 列级分类
- 六大约束语法上都支持,但外键约束没效果
- 表级分类
- 除了非空约束(not null)和默认约束(default),其它都支持
创建表时添加约束
添加列级约束
语法:
直接在字段名和类型后面追加 约束类型 即可。
支持的约束:默认(default)、非空(not null)、主键(primary key)、唯一(unique)
示例:
create database students; # 创建数据库
use students; # 进入数据库
# 学生表
create table stu_info(
id int(11) primary key, # 主键约束
stu_name varchar(20) not null, # 非空约束
sex char(1) check(sex ='男' or sex='女'), # 检查约束,Mysql不生效,仅作演示
seat int(11) unique, # 唯一约束
age int(3) default 18, # 默认约束,默认18岁
# major_id int(11) foreign key references major(id) # 外键约束
major_id int(11) references major(id) # 外键约束
);
# 专业表
create table major(
id int(11) primary key, # 主键约束
major_name varchar(50)
);
查看 学生表 信息:
查看 学生表 所有索引:
可以看到,外键约束并没有起作用。
添加表级约束
语法:
- 在各个字段最下面
- constraint 约束名 约束类型(字段名)
- constraint关键字就是为添加的约束起个别名不写也行,
- 注意:主键约束别名为primary,改了也不生效
示例:
create database students; # 创建数据库
use students; # 进入数据库
# 学生表
create table stu_info(
id int(11) ,
stu_name varchar(20),
sex char(1) ,
seat int(11),
age int(3),
major_id int(11),
constraint pk primary key(id), #主键
constraint uq unique(seat), #唯一
constraint ck check(sex='男' or sex='女'), #检查约束
constraint fk_major foreign key(major_id) references major(id) #外键
);
# 专业表
create table major(
id int(11) primary key, # 主键约束
major_name varchar(50)
);
查看 学生表 信息:
查看 学生表 所有索引:
可以看到,表级约束中外键约束起到了作用。
列、表级约束一起写
语法:
create database students; # 创建数据库
use students; # 进入数据库
# 学生表
create table stu_info(
id int(11) primary key, # 主键约束
stu_name varchar(20) not null, # 非空约束
sex char(1) check(sex ='男' or sex='女'), # 检查约束,Mysql不生效,仅作演示
seat int(11) unique, # 唯一约束
age int(3) default 18, # 默认约束,默认18岁
major_id int(11),
constraint fk_major foreign key(major_id) references major(id) #外键
);
# 专业表
create table major(
id int(11) primary key, # 主键约束
major_name varchar(50)
);
主键约束和唯一约束对比
保证唯一性 | 是否允许为空 | 表中是否可有多个 | 是否允许组合 | |
---|---|---|---|---|
主键约束 | √ | × | 主键只能有一个;但是可创建多个列联合主键 | 两个列组合成一个主键:允许 |
唯一约束 | √ | √ | 可以有多个 | 两个列组合成一个唯一:允许 |
多个列联合主键示例
create table stu_info(
id int(11) ,
stu_name varchar(20) not null, # 非空约束
sex char(1) check(sex ='男' or sex='女'), # 检查约束,Mysql不生效,仅作演示
seat int(11) unique, # 唯一约束
age int(3) default 18, # 默认约束 默认18岁
primary key(`id`,`stu_name`) # id和stu_name组合成主键
);
# 成功添加:
truncate table stu_info;
insert into stu_info value(1, '张三', '男', 12, 18);
insert into stu_info value(2, '张三', '男', 13, 19);
# 成功添加:
truncate table stu_info;
insert into stu_info value(1, '张三', '男', 12, 18);
insert into stu_info value(1, '李四', '男', 13, 19);
# 失败添加:
truncate table stu_info;
insert into stu_info value(1, '张三', '男', 12, 18);
insert into stu_info value(1, '张三', '男', 13, 19);
外键
特点
- 在从表上设置外键关系
- 从表的外键列的类型和主表的关联列的类型要求一致或兼容
- 主表中关联列必须是一个key(一般是主键、唯一键)
- 插入数据要先插入主表再插入从表
- 删除数据先删除从表再删除主表
修改表时添加约束
示例
# 修改非空约束
alter table 表 modify column字段 varchar(20) not null;
# 默认约束,默认值:'demo'
alter table 表 modify column字段 varchar(20) default 'demo';
# 添加主键
alter table 表 modify column 字段 int(11) primary key;
alter table 表 add primary key(字段) ;
# 添加唯一键
①列级约束
alter table 表 modify column 字段 int(11) unique;
②表级约束
alter table 表 add unique(字段);
# 添加外键
alter table 表 add foreign key(字段) references 【外键的作用】;
修改表时删除约束
示例
# 删除非空约束
alter table 表 modify column字段 varchar(20) null;
# 删除主键
alter table 表 drop primary key;
# 删除唯一约束
alter table 表 drop index;
标识列(自增长列)
含义
可以不用手动插入值,系统提供默认序列值
特点
- 标识列必须和主键搭配吗?不一定,但要求必须是一个key
- 一个表可有多个标识列吗?至多一个
- 标识列类型只能是数值型,如 int、float、double
举例
create table demo(
id int primary key auto_increment, # id为主键并设为自增
name varchar(10)
)
# 插入五条数据
insert into demo values(null, 'AAA'),(null, 'BBB'),(null, 'CCC'),(null, 'DDD');
# 查看
select * from demo;
+----+------+
| id | name |
+----+------+
| 1 | AAA |
| 2 | BBB |
| 3 | CCC |
| 4 | DDD |
+----+------+