MySQL之DDL（定义）语句

怼烎@ 2023-07-03 02:55 29阅读 0赞

## 库的管理 ##

①创建： CREATE DATABASE \[IF NOT EXISTS\] 库名

②查看：SHOW DATABASES

③修改：库名可以直接修改相对应的存储文件名  
ALTER DATABASE 库名+  
CHARACTER SET 编码方式  
COLLATE 检验方式 设置检验方式（防止出现中文乱码）  
Tips：show character set； 查看mysql所支持的字符及校验规则

④删除：DROP DATABASE IF EXISTS 库名

## 表的管理 ##

PS：所有的表都存储在information\_schema.TABLES表中，可根据TABLE\_SCHEMA（数据库名）和TABLE\_NAME（表名）来查看相关表信息。

①创建：CREATE TABLE \[IF NOT EXISTS\] 表名（列名，列的类型（长度）\[约束\]), … )  
TIPS：在创建表时，可在每一字段后加 COMMENT ‘注释’作解释

②查看：  
所有表：SHOW TABLES  
表结构：DESC 表名 || SHOW CREATE TABLE 表名\[\\G\] \#可以查看具体创建过程

③修改 ：（ADD 、MODIFY、 CHANGE 、DROP 、RENAME TO）

<table> 
 <thead> 
  <tr> 
   <th>效用</th> 
   <th>表示方式</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>添加列或添加列级约束</td> 
   <td>ALTER TABLE 表名 ADD COLUMN (列名 列的类型（长度）[约束]) … ) [添加位置 first/after 列名];<br>Ps：可添加表级约束（ADD [CONSTRAINT pk/uq/ck（约束名）] 约束类型(列名)）；</td> 
  </tr> 
  <tr> 
   <td>修改列类型或列级约束</td> 
   <td>ALTER TABLE 表名 MODIFY COLUMN 列名 类型 [约束] [修改位置 first/after 列名];</td> 
  </tr> 
  <tr> 
   <td>修改列名</td> 
   <td>ALTER TABLE 表名 CHANGE COLUMN 旧名 新名 类型;</td> 
  </tr> 
  <tr> 
   <td>删除列</td> 
   <td>ALTER TABLE 表名 DROP COLUMN 列名;</td> 
  </tr> 
  <tr> 
   <td>修改表名称</td> 
   <td>ALTER TABLE 表名 RENAME TO 新名;</td> 
  </tr> 
 </tbody> 
</table>

④表的删除：DROP TABLE IF EXISTS 表名；  
通常流程：先删除，后新建

⑤表的复制： 可以跨库操作：库名.表名

<table> 
 <thead> 
  <tr> 
   <th>格式</th> 
   <th>结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CREATE TABLE 新表名 LIKE 所复制的表名</td> 
   <td>复制表的格式（包含key和自增长），不复制表中的数据</td> 
  </tr> 
  <tr> 
   <td>CREATE TABLE 新表名 SELECT 字段 FROM 所复制的表名</td> 
   <td>可以复制部分的字段和数据（但不会复制表中的key和自增长列的标识）</td> 
  </tr> 
 </tbody> 
</table>

tips：若需要只复制部分结构，不复制数据，可以使用一个永假的筛选条件

## 常见约束 ##

约束：限制表中数据，保证数据的一致性。  
修改：在创建表的时候和修改表的时候进行设置，数据插入之前。

<table> 
 <thead> 
  <tr> 
   <th>六大约束</th> 
   <th>效用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>①NOT NULL</td> 
   <td>非空</td> 
  </tr> 
  <tr> 
   <td>②DEFAULT</td> 
   <td>默认，使用update进行修改时，也可使得 set *** = deafult</td> 
  </tr> 
  <tr> 
   <td>③PRIMARY KEY</td> 
   <td>主键，保证字段的值具有唯一性，并且一定非空</td> 
  </tr> 
  <tr> 
   <td>④UNIQUE KEY</td> 
   <td>唯一性，但可以为空</td> 
  </tr> 
  <tr> 
   <td>⑤CHECK</td> 
   <td>检查约束（mysql不支持），例，CHECK（列名=’ ’ OR 列名=’ '）对插入数据进行限制</td> 
  </tr> 
  <tr> 
   <td>⑥FOREIGN KEY</td> 
   <td>外键（从表添加）</td> 
  </tr> 
 </tbody> 
</table>

##### 1）、创建表时添加约束： #####

**列级约束**：六大约束都支持，外键约束无效，直接在字段和类型后进行追加

**表级约束**：除了①②都支持  
语法：\[CONSTRAINT pk/uq/ck（约束名）\] 约束类型 （具体约束列）（可以起别名）  
外键：FOREIGN KEY（列名） REFERENCES 主表（列名））\[on update 等级 on delete 等级\]  
tips：外键约束名一般为fk\_当前表\_主表，外键约束的等级：

<table> 
 <thead> 
  <tr> 
   <th>等级名</th> 
   <th>效用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Cascade（级联）</td> 
   <td>主动权在主表，主表修改/删除，从表对应进行修改/删除</td> 
  </tr> 
  <tr> 
   <td>Set null</td> 
   <td>主动权在主表，主表修改/删除，从表对应外键设为null（外键子段不能有非空约束）</td> 
  </tr> 
  <tr> 
   <td>No action</td> 
   <td>主表不作为，同Restrict</td> 
  </tr> 
  <tr> 
   <td>Restrict</td> 
   <td>严格，主表对该字段的修改和删除完全被限制，必须先处理从表对应的值</td> 
  </tr> 
  <tr> 
   <td>Set default</td> 
   <td>Mysql Innodb引擎不支持</td> 
  </tr> 
 </tbody> 
</table>

##### 2）、修改表时添加约束: #####

①使用MODIFY关键字添加列级约束  
②使用ADD关键字添加表级约束

##### 3）、删除约束： #####

非空和默认可用MODIFY修改列，不加约束，会进行覆盖  
对于主键、唯一（INDEX）、外键可以使用：ALTER TABLE 表名 DROP 约束类型 列名（主键不需要提供列名）

PS：所有约束存储在information\_schema数据库的table\_consraints表中  
也可使用：SHOW INDEX FROM 表名 \#查看表中所有的键(索引）

##### 4）、三个key的特点 #####

<table> 
 <thead> 
  <tr> 
   <th>key</th> 
   <th>特点</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主键</td> 
   <td>保证唯一性，不可为空，一个表至多一个，可以组合（复合主键，不推荐）</td> 
  </tr> 
  <tr> 
   <td>唯一</td> 
   <td>保证唯一性，可为空（只能允许一个null），一表可有多个，可以组合（不推荐）</td> 
  </tr> 
  <tr> 
   <td>外键</td> 
   <td>限制两个表的关系，也可以是一个表中的两个字段<br> 保证该字段的值必须来自于主表关联列（必须是一个键，一般为主键或唯一键）的值，和主表中的类型一致或兼容<br>插入数据时，先插入主表，再插入从表，删除数据时，先删除从表，再删除主表</td> 
  </tr> 
 </tbody> 
</table>

PS：一般项目用表都不会设置外键，因为外键所在表插入字段时必须要求主表存在，每次都会查看，数据过多时，影响效率，并且在某种特殊需求下，会要求删除主表信息而保留从表信息，有很大影响。

## 标识列/自增长列（不用手动插入值，系统提供默认的序列值） ##

①创建表时设置标识列：直接跟在主/唯一/外键（即跟key搭配，数据类型为数值型，且一个表只能有一个）约束之后，AUTO\_INCREMENT

ps：SHOW VARIABLES LIKE ‘%auto\_increment%’  
标识列的起始值 ：auto\_increment\_offset  
步长：auto\_increment\_increment

TIPS：mysql中不支持对起始值进行修改，但可以在插入数值时指定值，此后数据便会根据此值增长

②修改表时设置标识列：通过MODIFY关键字设置标识列

③修改表时删除标识列：通过MODIFY关键字删除标识列（不设置，进行覆盖）