MySQL的DDL

2021-03-26 本文已影响0人程序员汪汪

DDL(数据定义语言)

DDL(Data Definition Language)：数据定义语言，用来定义数据库对象（数据库、表、列等）。

关键字：CREATE、ALTER、DROP等

CREATE：创建
ALTER：修改
DROP：删除

库的管理

创建库

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

CREATE DATABASE IF NOT EXISTS books;

修改库

alter database 库名 character set 字符集名;

修改库名

# 在较旧的版本中，可以使用该命令修改库名，现在使用该命令会报错，官方不建议这样修改库名，这样有数据丢失风险
# 如果一定要改，建议使用mysqldump将数据导出，再导入新库，数据量大，耗时会比较长
RENAME DATABASE books TO 新库名;

修改库的字符集

ALTER DATABASE books CHARACTER SET gbk;

删除库

drop database [if exists] 库名;

表的管理

创建表

create table 表名(
    列名 列的类型[(长度) 约束],
    列名 列的类型[(长度) 约束],
    列名 列的类型[(长度) 约束],
    ...
    列名 列的类型[(长度) 约束]
)

创建表 book表

CREATE TABLE book ( 
    id INT, #编号
    bName VARCHAR ( 20 ), #图书名
    price DOUBLE, #价格
    authorId INT, #作者编号
    publishDate DATETIME #出版日期
);

查看book表结构

DESC book;

创建表 author

CREATE TABLE IF NOT EXISTS author(
    id INT,
    au_name VARCHAR(20),
    nation VARCHAR(10)
)

修改表

添加列

alter table 表名 add column 列名 类型 [first|after 字段名];

修改列的类型或约束

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

修改列名

alter table 表名 change column 旧列名 新列名 类型;

删除列

alter table 表名 drop column 列名;

表名

alter table 表名 rename [to] 新表名;

删除表

drop table [if exists] 表名;

复制表

复制表的结构

create table 表名 like 旧表;

复制表的结构+数据

create table 表名 
select 查询列表 from 旧表 [where 筛选];

案例

向author表插入数据

INSERT INTO author
VALUES
    ( 1, '村上春树', '日本' ),
    ( 2, '莫言', '中国' ),
    ( 3, '冯唐', '中国' ),
    ( 4, '金庸', '中国' );

创建表copy，复制author表的结构
```
CREATE TABLE copy LIKE author;
```
创建表copy2，复制author表的结构和数据
```
CREATE TABLE copy2 
SELECT * FROM author;
```

创建表copy3，复制author表的部分数据

CREATE TABLE copy3
SELECT id,au_name
FROM author 
WHERE nation='中国';

创建表copy4，复制author表的部分字段

CREATE TABLE copy4 
SELECT id,au_name
FROM author
WHERE 0;

MySQL数据类型

数值型

整型

TINYINT、SMALLINT、MEDIUMINT、INT/INTEGER、BIGINT

特点：

都可以设置无符号和有符号，默认有符号，通过UNSIGNED设置无符号
如果超出了范围，会报out or range异常，插入临界值
长度可以不指定，默认会有一个长度，长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配ZEROFILL，并且默认变为无符号整型

如何设置无符号

CREATE TABLE tab_int(
    t1 INT,
    t2 INT unsigned 
);

显示长度

CREATE TABLE tab_int(
    t1 INT(7) ZEROFILL,
    t2 INT(7) ZEROFILL 
);

浮点型

定点数：DECIMAL(M,D)

浮点数：FLOAT(M,D)、DOUBLE(M,D)

特点：

M代表整数部位+小数部位的个数，D代表小数部位
如果超出范围，则报out or range异常，并且插入临界值
M和D都可以省略，但对于定点数，M默认为10，D默认为0
如果精度要求较高，则优先考虑使用定点数

字符型

char、varchar、binary、varbinary、enum(用于保存枚举)、set(用于保存集合)、text、blob

char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1

varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，其中M不可以省略

Enum：又称为枚举类型哦，要求插入的值必须属于列表中指定的值之一。

Set：和Enum类型类似，里面可以保存0~64个成员。和Enum类型最大的区别是：SET类型一次可以选取多个成员，而Enum只能选一个

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');    -->  会插入 '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,d');

日期型

year年 date日期 time时间 datetime 日期+时间 timestamp 日期+时间，比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间

常见的约束

NOT NULL：非空，该字段的值必填
UNIQUE：唯一，该字段的值不可重复，可以为空
DEFAULT：默认，该字段的值不用手动插入有默认值
CHECK：检查，mysql不支持（但是设置并不会报错，只是没有效果）
PRIMARY KEY：主键，该字段的值不可重复并且非空 unique + not null
FOREIGN KEY：外键，该字段的值引用了另外的表的字段

添加约束的时机

创建表时
修改表时(在添加数据之前)

约束的添加的分类：

列级约束：

六大约束语法上都支持，但外键约束没有效果
表级约束：

除了非空、默认，其他的都支持

主键和唯一

区别：

一个表至多有一个主键，但可以有多个唯一
主键不允许为空，唯一可以为空

主键和唯一的区别

外键：

要求在从表设置外键关系
从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
主表的关联列必须是一个key（一般是主键或唯一）
插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表

创建表时添加约束

CREATE TABLE 表名(
    字段名 字段类型 列级约束,
    字段名 字段类型,
    表级约束
)

添加列级约束

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

只支持：默认、非空、主键、唯一

# 创建一个数据库
CREATE DATABASE students;
USE students;

CREATE TABLE stuinfo (
    id INT PRIMARY KEY,#主键
    stuName VARCHAR ( 20 ) NOT NULL UNIQUE,#非空并且唯一
    gender CHAR ( 1 ) CHECK ( gender = '男' OR gender = '女' ),#检查
    seat INT UNIQUE,#唯一
    age INT DEFAULT 18,#默认约束
    majorId INT REFERENCES major ( id ) #外键，(没有效果)
);

CREATE TABLE major(
    id INT PRIMARY KEY,
    majorName VARCHAR(20)
);

# 查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;

添加表级约束

在各个字段的最下面

【constraint 约束名】约束类型(字段名)

DROP TABLE IF EXISTS 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 = '男' OR gender = '女' ),#检查
    CONSTRAINT fk_stuinfo_major FOREIGN KEY ( majorid ) REFERENCES major ( id ) #外键

);

通用写法

CREATE TABLE IF NOT EXISTS stuinfo (
        id INT PRIMARY KEY,
        stuname VARCHAR ( 20 ),
        sex CHAR ( 1 ),
        age INT DEFAULT 18,
        seat INT UNIQUE,
        majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY ( majorid ) REFERENCES major ( id ) 
);

修改表时添加约束

添加列级约束

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

添加表级约束

alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用];

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT
);

添加非空约束

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);

修改表时删除约束

删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT;

删除主键
```
ALTER TABLE stuinfo DROP PRIMARY KEY;
```
删除唯一
```
ALTER TABLE stuinfo DROP INDEX seat;
```

删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

自增长列

不用手动插入值，可以自动提供序列值，默认从1开始，步长为1

auto_increment_increment

如果要更改起始值：手动插入值

如果要更改步长：更改系统变量：set auto_increment_increment=值;

一个表至多有一个自增长列
自增长列只能支持数值型
自增长列必须为一个key

创建表时设置自增长列

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

修改表时设置自增长列

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

删除自增长列

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

MySQL的DDL

DDL(数据定义语言)

库的管理

创建库

修改库

修改库名

修改库的字符集

删除库

表的管理

创建表

修改表

添加列

修改列的类型或约束

修改列名

删除列

表名

删除表

复制表

复制表的结构

复制表的结构+数据

案例

MySQL数据类型

数值型

整型

浮点型

字符型

日期型

常见的约束

主键和唯一

创建表时添加约束

添加列级约束

添加表级约束

通用写法

修改表时添加约束

修改表时删除约束

自增长列

创建表时设置自增长列

修改表时设置自增长列

删除自增长列

猜你喜欢

热点阅读