第六章 DDL语言

淩亂°似流年 2023-01-20 03:00 2阅读 0赞

第六章  DDL语言

**目录**

1 库的管理

一、创建库

二、修改库

三、删除库

2 表的管理

一、表的创建

二、修改表

1、添加列

2、修改列的类型或约束

3、修改列名

4、删除列

5、修改表名

6、删除表

7、复制表

3 常见的数据类型

一、整型

二、小数

浮点型

定点型

特点

三、字符型

四、日期型

4  常见约束（重要）

一、六大约束有哪些？

二、列级约束、表级约束

1、认识一下它们？

2、什么时候采用表级，什么时候采用列级呢？

三、添加约束

1、创建表时

2、修改表时

修改表时添加约束

修改表时删除约束

四、

1、主键和唯一不同之处

2、外键特点

3、级联操作

级联删除

级联置空

5 标识列

一、定义

二、怎么设置标识列？

1、创建表时设置

2、修改表时设置

3、删除标识列

三、标识列设置起点、步长

1、设置标识列的起始值

2、设置标识列的步长

--------------------

> **数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。**
> 
> **主要由create（添加）、alter（修改）、drop（删除）和 truncate（删除） 四个关键字完成。**

--------------------

# 1 库的管理 #

## 一、创建库 ##

> **create  database  \[if not exists\]  库名  \[character set 字符集名\]；**

--------------------

## 二、修改库 ##

> **alter  database  库名  character  set  字符集名;**

--------------------

## 三、删除库 ##

> **drop  database  \[if exists\]  库名;**

--------------------

# 2 表的管理  #

## 一、表的创建 ##

create table  [if not exists]  表名(
    字段名 字段类型  [约束],
    字段名 字段类型  [约束],
    ...
    字段名 字段类型  [约束]
    )

--------------------

## 二、修改表 ##

### 1、添加列 ###

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

--------------------

### 2、修改列的类型或约束 ###

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

--------------------

### 3、修改列名 ###

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

--------------------

### 4、删除列 ###

> **alter table 表名 drop column 列名;**

--------------------

### 5、修改表名 ###

> **alter table 表名 rename  新表名;**

--------------------

### 6、删除表 ###

> **drop  table  \[if exists\]  表名;**

--------------------

### 7、复制表 ###

*  复制表的结构

> create table 表名 like 旧表;

*  **复制表的结构+数据**

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

--------------------

# 3 常见的数据类型 #

*  数值型
    
     *  整型
     *  浮点型
        
         *  定点数
         *  浮点数
 *  字符型
    
     *  较短的文本：char、varchar
     *  较长的文本：text、blob（较长的二进制数据）
 *  日期型

--------------------

## 一、整型 ##

分类：**Tinyint、Smallint、Mediumint、int/integer、bigint**

特点：

*  如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
 *  如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
 *  如果不设置长度，会有默认的长度，长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用，并且默认变为无符号的整型！

--------------------

## 二、小数 ##

### 浮点型 ###

*  float（M，D）
 *  double（M，D）

### 定点型 ###

*  dec（M，D）
 *  decimal（M，D）

--------------------

### 特点 ###

*  M：整数部位+小数部位
 *  D：小数部位
 *  如果超过范围，则插入临界值
 *  M和D都可以省略，如果是decimal，则M默认为10，D默认为0
 *  如果是float和double，则**会根据插入的数值的精度来决定精度**
 *  定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用定点型

> **原则：所选择的类型越简单越好，能保存数值的类型越小越好**

--------------------

## 三、字符型 ##

> **char、varchar、binary、varbinary、enum、set、text、blob**

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

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

其他类型

*  **binary和varbinary用于保存较短的二进制。**
 *  **enum：枚举，          eg. `enum('男', '女')`**
 *  **set：集合，              eg. `set('a', 'b', 'c', 'd')`**

--------------------

## 四、日期型 ##

分类：

*  date：日期
 *  time：时间
 *  year：年
 *  datetime：日期+时间
 *  timestamp：日期+时间，更能反映真实时间

字节 、范围、时区等所带来的影响

*  datetime 8 1000-9999 不受
 *  timestamp 4 1970-2038 受

--------------------

# 4  常见约束（重要） #

> **含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性。**

## 一、六大约束有哪些？ ##

*  not null：非空，用于保证该字段的**值不能为空**。
 *  default：默认，用于保证该**字段有默认值**。
 *  **primary key**：主键，用于保证**该字段的值具有唯一性，并且非空**。
 *  unique：唯一，用于保证该字段的**值具有唯一性，可以为空**。
 *  check：检查约束【mysql中不支持】。
 *  **foreign key：**外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于应用主表中某列的值。

--------------------

## 二、列级约束、表级约束 ##

### 1、认识一下它们？ ###

先展示列级约束：

CREATE TABLE stuinfo (
      id INT PRIMARY KEY,
      stuname VARCHAR (20) NOT NULL,	# 非空
      gender CHAR(1) ,
      seat INT UNIQUE,	# 唯一
      age INT DEFAULT 18,	# 默认
    ) ;

再展示表级约束：

CREATE TABLE stuinfo (
      id INT,
      stuname VARCHAR (20) NOT NULL,	# 非空
      gender CHAR(1) ,
      seat INT,
      age INT DEFAULT 18,	# 默认
      major_id INT,
      CONSTRAINT pk PRIMARY KEY(id), #主键
      CONSTRAINT un UNIQUE(seat), #唯一键
      CONSTRAINT fk foreign key(major_id) references major(id)
    ) ;

--------------------

### **2、什么时候采用表级，什么时候采用列级呢？** ###

> **一般我们提倡主键Primary key、唯一 unique、检查 checck 、默认default 、非空/空值 not null/ null使用列级约束。**

> **提倡主键、外键、唯一、检查使用表级约束**

> ** 补充：*主键等写成列级约束，注意外键只能写成表级约束才能生效。*****列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求。**

--------------------

### 三、添加约束 ###

我们如何添加约束？主要分为两种方式。

> **第一，在创建表时添加约束。**

> **第二，在修改表时添加约束。**

--------------------

### 1、创建表时 ###

如图所示

CREATE TABLE stuinfo (
      id INT PRIMARY KEY,
      stuname VARCHAR (20) NOT NULL,	# 非空
      gender CHAR(1) ,
      seat INT UNIQUE,	# 唯一
      age INT DEFAULT 18,	# 默认
      foreign key(外键字段名) references 表名(关联字段名)
    ) ;

--------------------

### 2、修改表时 ###

### 修改表时添加约束 ###

>  *  **添加列级约束：alter table 表名 modify column 字段名 字段类型 新约束；**
>  *  **添加表级约束：alter  table  表名  add  constraint  自定义约束名   约束名（字段名）；**

举几个例子：

添加主键

> **列级约束：ALTER  TABLE  表名称   MODIFY  COLUMN  字段名  字段类型   PRIMARY KEY；  **
> 
> **表级约束：ALTER  TABLE  表名称   ADD   PRIMARY KEY (字段名) ；**

添加唯一键

> **列级约束：ALTER  TABLE  表名称   MODIFY  COLUMN  字段名  字段类型  UNIQUE；  **
> 
> **表级约束：ALTER  TABLE  表名称   ADD UNIQUE (字段名) ；**

添加非空约束**（非空约束只有列级约束）**

> **ALTER  TABLE  表名  MODIFY  COLUMN  字段名  VARCHAR(20)  NOT NULL;**

添加默认约束**（默认约束只有列级约束）**

> **ALTER  TABLE  表名  MODIFY  COLUMN  字段名  INT  DEFAULT  18;**

添加外键约束**（外键只有表级约束）**

> ** ALTER TABLE  需要添加外键表名称  ADD  \[CONSTRAINT  自定义约束名\]   FOREIGN KEY  外键名(设为外键的字段)  REFERENCES  被添加外键表名称(设为被添加外键的字段)**
> 
> **\[CONSTRAINT  自定义约束名\] 可以省略。**
> 
> **需要添加外键表名称一般为次表；**
> 
> **被添加外键表名称一般为主表。**

--------------------

### 修改表时删除约束 ###

删除非空约束

> **ALTER  TABLE  表名  MODIFY  COLUMN  字段名  VARCHAR(20)  NULL;**

删除默认约束

> **ALTER  TABLE  表名  MODIFY  COLUMN  字段名  INT;**

删除主键

> **ALTER  TABLE  表名  DROP  PRIMARY  KEY;**

删除唯一

> **ALTER TABLE 表名  DROP  INDEX  字段名;    **
> 
> **删除唯一性约束，实际就是删除索引。**

删除外键

> **ALTER  TABLE  表名  DROP  FOREIGN  KEY  外键名;**

--------------------

## 四、 ##

### 1、主键和唯一不同之处 ###

> **唯一键：保证唯一性，允许为空， 一个表中可以有多少个。    **

> **主键：保证唯一性，不允许为空， 一个表中最多一个。 **

--------------------

### 2、外键特点 ###

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

这就需要引出级联操作。

--------------------

### 3、级联操作 ###

### 级联删除 ###

> **ALTER  TABLE  次表名  次表别名  CONSTRAINT  约束名  FOREIGN KEY(次字段名)   REFERENCES   主表名(主字段名) ** ** ON  DELETE  CASCADE;**

删除的时候，主表和从表对应的行都删了。

--------------------

### 级联置空 ###

> **ALTER  TABLE  次表名  次表别名  CONSTRAINT  约束名  FOREIGN KEY(次字段名)   REFERENCES   主表名(主字段名)   ON  DELETE  SET  NULL;**

删除的时候，主表对应的行被删除了，从表引入的地方变为空值null。

--------------------

# 5 标识列 #