Mysql数据库基础第四章DDL（数据定义语言）：库表的管理、数据类型与约束条件

2023-03-04 226

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL，集群系列 2核4GB

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS PostgreSQL，集群系列 2核4GB

简介： # 1.DDL语言基本概述DDL：数据定义语言主要包括以下几部分内容：- 库的管理- 表的管理- 数据类型- 约束下面我们先介绍库的管理

Mysql数据库基础系列

1.DDL语言基本概述

DDL：数据定义语言
主要包括以下几部分内容：

库的管理
表的管理
数据类型
约束

下面我们先介绍库的管理

2. 库的管理

库的管理包含：库的创建，更改，删除
使用的关键字： creater alter drop. 注意与数据操作语言区分
基本语法：

create|alter|drop database

2.1 库的创建

创建books为例

create database books if not exists books;

因为当存在已有的数据库时，我们不能在新建相同名字的数据库，否则会报错，因此在创建新的库时加上if条件表达式

2.2 库的修改

修改名字

rename database books to new_book;

修改字符集

alter database books character set utf-8

2.3库的删除

由于不能删除不存在的库，因此加上下列条件表达式

drop database books if exists books

3.表的管理

表的管理和库的管理一样，主要包括：创建，修改，删除

3.1 表的创建

create tables 表名(
            列名 列的类型 长度 约束，
            ...
)

创建book表

CREATE TABLE book(
    id INT,
    bname VARCHAR(20),
    price DOUBLE,
    authorID INT,
    publish_date DATETIME
);

创建author表

CREATE TABLE author(
    id INT,
    au_name VARCHAR(20),
    nation VARCHAR(20)
);

3.2表的修改

基本语法:

alter table 表名 change/drop/modify/add/rename column 列名 类型

1.修改列名：change

ALTER TABLE book CHANGE COLUMN publish_date pubdate DATETIME;

2.修改列的类型和约束

ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

3.增加列

ALTER TABLE book ADD COLUMN annual DOUBLE;

4.删除列

ALTER TABLE book DROP COLUMN annual;

5.修改名

ALTER TABLE author RENAME COLUMN TO book_author

3.3 删除表

drop TABLE author if exists author

3.4 表的复制

首先将相关数据导入到author表中

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

此时author表中包含了上述信息，如果我们需要新建一个表，只需要author表的结构，不需要内容，则：
1.只复制表的结构：

create table copy like author;

2.复制表的内容和结构
此时我们不仅需要复制表的结构，还需要其内容，此时我们需要将author表的内容也复制

create table copy2 
select * from author;

3.只复制某些列

create table copy3
select id,au_name from author
where nation = '中国';

4.仅仅复制某些字段的结构
此时我们不需要内容，因此可以自己添加一个永远为错的条件

create table copy4
select id,au_name from author
where 1=0

4.数据类型

常见的数据类型主要包括：数值型、字符型、日期型。

4.1数值型

数值型包含：整数和小数

4.1.1整数型

tinyint、smallint、mediumint、int/integer、bigint
特点：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

1.如何设置无符号和有符号

CREATE TABLE tab_int(
    t1 INT(7) ZEROFILL,
    t2 INT(7) unsigned 
)
insert into tab_int values(-123,-123)

t1是有符号的，t2是无符号的.因此t1会返回-123，t2会返回0

4.1.2 小数

分类：
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M，D)
decimal(M,D)
特点：

①
M：整数部位+小数部位的个数
D：小数部位的个数
如果超过范围，则返回临界值

②
M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度

③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

DROP TABLE tab_float;
CREATE TABLE tab_float(
    f1 FLOAT(5,2),
    f2 DOUBLE(5,2),
    f3 DECIMAL
);
SELECT * FROM tab_float;
DESC tab_float;

INSERT INTO tab_float VALUES(123.456,123.456,123.456);

上述结果将为: f1:123.46 f2:123.46 f3:123

4.2字符型

较短的文本：
char
varchar
其他：
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
较长的文本：
text
blob(较大的二进制)

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

上述m不在枚举的范围内，返回空值

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');
INSERT INTO tab_set VALUES('a,c,d,e');

上述第三条和第四条返回相同的结果，因为e不在集合内。

4.3日期型

分类：
date保存日期
time 只保存时间
year只保存年

datetime保存日期+时间
timestamp保存日期+时间

特点：

                字节                    范围                    是否受时区影响
datetime         8                    1000——9999                      不受
timestamp          4                    1970-2038                      受


CREATE TABLE tab_date(
    t1 DATETIME,
    t2 TIMESTAMP

);


INSERT INTO tab_date VALUES(NOW(),NOW());
SET time_zone='+9:00';#设置时区为东九区
SELECT * FROM tab_date;

当设置时区为东九区后，t2的时间会相应的加一个小时，t1的时间不变

5.约束条件

添加约束条件主要是用于限制表中的数据，保证表中的数据的准确和可靠性

分类：六大约束
    NOT NULL：非空，用于保证该字段的值不能为空
    比如学号等
    DEFAULT:默认，用于保证该字段有默认值
    比如性别
    PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
    比如学号、员工编号等
    UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
    CHECK:检查约束【mysql中不支持】
    FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
        在从表添加外键约束，用于引用主表中某列的值

可以在创建表和修改表时设置约束条件
约束的添加分类：

列级约束：
    六大约束语法上都支持，但外键约束没有效果
    
表级约束：
    
    除了非空、默认，其他的都支持

基本语法：


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

5.1列级约束

只支持：默认、非空、主键、唯一
创建students表

CREATE DATABASE students;
USE students;
DROP TABLE stuinfo;
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)#外键

);

创建major表

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

查看stuinfo中的所有索引，包括主键、外键、唯一

SHOW INDEX FROM stuinfo;

5.2添加表级约束

语法：在各个字段的最下面
【constraint 约束名】约束类型(字段名)
同样的创建stuinfo表

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)#外键
   
);

DESC stuinfo;

SHOW INDEX FROM stuinfo;

上述两种方法都有自己的优缺点，列级约束不支持外键，我们在创建表时可以综合两种方法，通用代码如下。

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)

);

5.3修改表时添加约束

有时我们需要对现有表进行约束的添加，此时需要在修改表时添加约束。首先创建没有约束条件的表

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

DESC stuinfo;

1.添加非空约束

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

2.添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

3.添加主键

①列级约束

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

②表级约束

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

4.添加唯一

①列级约束

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

②表级约束

ALTER TABLE stuinfo ADD UNIQUE(seat);

5.添加外键

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

Mysql数据库基础第四章DDL（数据定义语言）：库表的管理、数据类型与约束条件

Mysql数据库基础系列

1.DDL语言基本概述

2. 库的管理

2.1 库的创建

2.2 库的修改

2.3库的删除

3.表的管理

3.1 表的创建

3.2表的修改

3.3 删除表

3.4 表的复制

4.数据类型

4.1数值型

4.1.1整数型

1.如何设置无符号和有符号

4.1.2 小数

4.2字符型

4.3日期型

5.约束条件

5.1列级约束

查看stuinfo中的所有索引，包括主键、外键、唯一

5.2添加表级约束

5.3修改表时添加约束

1.添加非空约束

2.添加默认约束

3.添加主键

①列级约束

②表级约束

4.添加唯一

①列级约束

②表级约束

5.添加外键

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Mysql数据库基础第四章DDL（数据定义语言）：库表的管理、数据类型与约束条件

Mysql数据库基础系列

1.DDL语言基本概述

2. 库的管理

2.1 库的创建

2.2 库的修改

2.3库的删除

3.表的管理

3.1 表的创建

3.2表的修改

3.3 删除表

3.4 表的复制

4.数据类型

4.1数值型

4.1.1整数型

1.如何设置无符号和有符号

4.1.2 小数

4.2字符型

4.3日期型

5.约束条件

5.1列级约束

查看stuinfo中的所有索引，包括主键、外键、唯一

5.2添加表级约束

5.3修改表时添加约束

1.添加非空约束

2.添加默认约束

3.添加主键

①列级约束

②表级约束

4.添加唯一

①列级约束

②表级约束

5.添加外键

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像