04.DDL语言

2022-06-17 约 2409 字预计阅读 5 分钟

DDL语言

数据定义语言

库的管理：创建、修改、删除
表的管理：创建、修改、删除
- 创建: create
- 修改: alter
- 删除: drop

1 库的管理

语法: create database 库名;

# 创建库books
create database if not exists books;
# 库的修改
# 更改库的字符集
alter database books character set gbk;
# 库的删除
drop database if exists books;

2 表的管理

语法: create table 表名(列名列的类型(长度) 约束, 列名列的类型(长度) 约束,...);

2.1 表的创建

# 创建book表
create table book(
    id int,             #编号
    bname varchar(20),  #图书名
    price double,       #作者
    authorId int,       #作者编号
    publishDate datetime#出版日期
);
# 创建author表
create table author(
    id int,
    au_name varchar(20),
    nation varchar(10)
);

3 表的修改

语法: alter table 表名 add|drop|modify|change column 列名列的类型约束;

# 修改列名
alter table book change column publishDate pubDate datetime;
# 列的类型或约束
alter table book modify column pubDate timestamp;
# 添加列
alter table author add column annual double;
alter table author add column annual double first|after 字段名;
# 删除列
alter table author drop column annual;
# 修改表名
alter table author rename to book_author;

4 表的删除

drop table author;

通用的写法

drop databases if exists 旧库名;
create databases 新库名;

drop table if exists 旧表名;
create table 表名();

4.1 表的复制

# 复制表的结构
create table copy like author;
# 复制表的结构和数据
create table copy2 select * from author;
# 只复制部分数据
create table copy3 select id, au_name from author where nation='china';
# 仅复制某些字段(不要数据)
create table copy4 select id, au_name from author where 0;

5 常见的数据类型

原则：

所原则的类型越简单越好
能保存数值的类型越小越好

分类：

数值型
- 整型
- 小数
  - 定点数
  - 浮点数
字符型
- 较短的文本：char、varchar
- 较长的文本：text、blob(较长的二进制数据)
日期型

整型

tinyint(1)、smallint(2)、mediumint(3)、int\integer(4)、bigint(8)
默认是有符号。如果想设置无符号，需要添加 unsigned 关键字
如果不设置长度，会有默认长度
长度代表显示的最大宽度，如果不够会用0在左边填充，但必须搭配 zerofill 使用

# 如何设置无符号和有符号
drop table if exists tab_int;
create table tab_int(
    t1 int,
    t2 int unsigned
);
insert into tab_int values(-123456, 1);

# 如果超出整型的范围


# 宽度
create table tab_int(
    t1 int,
    t2 int unsigned
);
insert into tab_int values(-123456, 1);

小数

浮点型：float(M, D) 4字节, double(M, D) 8字节
定点型：DEC(M, D), DECIMAL(M, D) 最大取值范围与double相同
M 代表：整数部位 + 小数部位
D 代表：小数部位
如果超过范围，插入临界值
都可以省略
- decimal M默认为10，D默认为0
定点型的精确度较高(货币运算)

create table tab_float(
    f1 float(5, 2),
    f2 double(5, 2),
    f3 dec(5, 2)
);
insert into tab_float values(123.45, 123.45, 123.45);
insert into tab_float values(123.456, 123.456, 123.456);
insert into tab_float values(123.4, 123.4, 123.4);
insert into tab_float values(1523.4, 1523.4, 1523.4);

create table tab_float(
    f1 float,
    f2 double,
    f3 dec
);

字符型

较短的文本：char(M) 固定长度的字符 | varchar(M) 可变长度的字符
- M 最大的字符数，char可以省略，默认为1
- varchar 节省空间
- char 效率较高
较长的文本：text blob(较大的二进制)
binary 和 varbinary 用于保存较短的二进制
enum 用于保存枚举
set 用于保存集合

create table tab_char(
    c1 enum('a', 'b', 'c')
);
insert into tab_char values('a');
insert into tab_char values('b');
insert into tab_char values('c');
insert into tab_char values('m');
insert into tab_char values('A');

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

日期型

date 只保存日期
time 只保存时间
year 只保存年
datetime 保存日期+时间
- 8字节
- 1000-9999
timestamp 保存日期+时间
- 4字节
- 1970-2038
- 和实际时区有关

create table tab_date(
    t1 datetime,
    t2 timestamp
);

insert into tab_date values(now(), now());
show variables like 'time_zone';
set time_zone='+9:00';

6 常见约束

含义：一种限制，用于限制表中的数据，为了保证表中数据的准确和可靠性
六大约束
- not null：非空约束，保证该字段的值不能为空，比如姓名、学号等
- default：默认约束，保证该字段有默认值，比如性别
- primary key：主键约束，保证该字段的值具有唯一性，并且非空，比如学号、员工编号
- unique：唯一约束，保证该字段的值具有唯一性，可以为空，比如座位号
- check：检查约束，比如年龄，性别 (mysql中不支持)
- foreign key：外键约束，用于限制两个表的关系。保证该字段的值必须来自于主表的关联列的值。比如学生表的专业编号，员工表的部门编号，员工表的工种编号
添加约束的时机
1. 创建表时
2. 修改表时
约束的添加分类
1. 列级约束：六大约束语法上都支持，但外键约束没有效果，不可以起约束名
2. 表级约束：除了非空、默认，其他都支持，可以起约束名(主键没有效果)

create table 表名(
    字段名 字段类型 列级约束,
    字段名 字段类型,
    表级约束
);

6.1 创建表时添加约束

# 添加列级约束
create database students;
use students;
CREATE TABLE stuinfo (
    id INT PRIMARY KEY,
    stuName VARCHAR(20) NOT NULL,
    gender CHAR(1) CHECK (gender = 'boy' OR gender = 'girl'),
    seat INT UNIQUE,
    age INT DEFAULT 18,
    majorId INT REFERENCES major (id)
);
CREATE TABLE major (
    id INT PRIMARY KEY,
    majorName VARCHAR(20)
);

desc stuinfo;
show index from stuinfo;
drop table stuinfo;

# 添加表级约束
CREATE TABLE stuinfo (
    id INT,
    stuName VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorId INT,
    constraint pk primary key(id),
    constraint uq unique(seat),
    constraint ck check(gender = 'boy' or gender = 'girl'),
    constraint fk_stuinfo_major foreign key(majorid) references major(id)
);

# 通用的写法
create table if not exists stuinfo(
	id int primary key,
    stuName varchar(20) not null,
    age int default 18,
    seat int unique,
    majotId int,
    constraint fk_stuinfo_major foreign key(majotId) references major(id)
);

主键和唯一的对比

都保证唯一性
允许组合，但不推荐
主键不允许为空，唯一允许为空
一个表中至多有1个主键，可以有多个唯一

外键

用于限制两个表的关系，从表的字段值引用了主表的某字段值
要求在从表设置外键关系
从表的外键列的类型和主表的关联列的类型要求类型一致或兼容，意义一样，名称无要求
主表中的关联列必须是一个key (一般是主键或唯一键)
插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表

# 通过以下两种方式来删除主表的记录
# 级联删除
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references major(id) on delete cascade;
# 级联置空
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references major(id) on delete set null;

7 修改表时添加约束

添加列级约束: alter table 表名 modify column 字段名字段类型新约束;
添加表级约束：alter table 表名 add constraint 约束名约束类型(字段名) 外键的引用;

# 添加非空约束
alter table stuinfo modify column stuname varchar(20) not null;
# 添加默认约束
alter table stuinfo modify column age int default 18;
# 添加主键
# 列级约束
alter table stuinfo modify column id int primary key;
# 表级约束
alter table stuinfo add primary key(id);
# 添加唯一
# 列级约束
alter table stuinfo modify column seat int unique;
# 表级约束
alter table stuinfo add unique(seat);
# 添加外键
alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);

8 修改表时删除约束

# 删除非空约束
alter table stuinfo modify column stuname varchar(20) null;
# 删除默认约束
alter table stuinfo modify column age int;
# 删除主键约束
alter table stuinfo drop primary key;
# 删除唯一
show index from stuinfo;
alter table stuinfo drop index seat;
# 删除外键约束
alter table stuinfo drop foreign key fk_stuinfo_major;

9 标识列(自增长列)

含义：可以不用手动的插入值，系统提供默认的序列值。默认从1开始，步长也为1。

特点：

标识列必须和主键搭配吗？不一定，但要求是一个key
一个表中可以有几个标识列？至多1个
标识列的类型只能是数值型
标识列可以通过 set auto_increment_increment=值'; 设置步长。可以通过手动插入值，设置起始值。

1. 创建表时设置标识列

create table tab_identity(
    id int primary key auto_increment,
    name varchar(20)
);
# 创建表时设置标识列
insert into tab_identity(id, name) values(null, 'john');
show variables like '%auto_increment%';

2. 修改表时设置标识列

alter table tab_identity modify column id int primary key auto_increment;

3. 修改表时删除标识列

alter table tab_identity modify column id int;

目录