Mysql中的DDL语句

序言

DDL 的英文全称是 Data Definition Language。定义了数据库的结构和数据表的结构。DDL 是 DBMS 的核心组件，也是 SQL 的重要组成部，DDL的正确性和稳定性是整个SQL运行的重要基础。所以首先应该从建表上优化数据库

数据库对库的定义

创建

-- 创建的基本格式
create database 数据库名称;

-- 创建数据库，判断不存在，再创建：
create database if not exists 数据库名称;

-- 创建数据库，并指定字符集。字符集常用UTF8
create database 数据库名称 character set 字符集名;

查询

-- 查询所有数据库的名称:
show databases;
-- 查询某个数据库的字符集:查询某个数据库的创建语句
show create database 数据库名称;

修改

-- 修改数据库的字符集
alter database 数据库名称 character set 字符集名称;

删除

-- 删除数据库
drop database 数据库名称;

-- 判断数据库存在，存在再删除
drop database if exists 数据库名称;

使用数据库

use 数据库名称;

查询当前正在使用的库名

select database();

数据库对表的定义

创建

格式

create table 表名(
    列名1 数据类型1 约束,
    列名2 数据类型2 约束,
    ....
    列名n 数据类型n 约束
);

注意

最后一列不加,

建表最后加;

表名如何和保留字冲突，加s，比如表名user则应该创建为users

mysql默认不区分大小写，列明如果要区分大小写就用_分隔单词

如果设置的字符集是UTF8,那么排序规则是utf8_general_ci，代表对大小写不敏感，如果设置为utf8_bin,代表对大小写敏感

常见的数据类型

整数 int

小数 double(小数的最大长度,小数点后保留几位)。比如分数score(5,2) 分数100.00

日期

​ date:日期，只包含年月日，yyyy-MM-dd

​ datetime:日期，包含年月日时分秒,yyyy-MM-dd HH:mm:ss

​ timestamp:时间错类型,包含年月日时分秒,yyyy-MM-dd HH:mm:ss。如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值

字符串

​ varchar 长度会随着实际存储数据的变化而改变

​ char 长度固定。

举例

比如我们想创建一个球员表，表名为 player，里面有两个字段，一个是 player_id，它是 int 类型，另一个 player_name 字段是varchar(255)类型。这两个字段都不为空，且 player_id 是递增的。

CREATE TABLE player  (
  player_id int(11) NOT NULL AUTO_INCREMENT,
  player_name varchar(255) NOT NULL
);

需要注意的是，语句最后以分号（;）作为结束符，最后一个字段的定义结束后没有逗号。数据类型中 int(11) 代表整数类型，显示长度为 11 位，括号中的参数 11 代表的是最大有效显示长度，与类型包含的数值范围大小无关。varchar(255)代表的是最大长度为 255 的可变字符串类型。NOT NULL表明整个字段不能是空值，是一种数据约束。AUTO_INCREMENT代表主键自动增长。

查询表结构

desc 表名

修改表结构

修改表名

alter table 表名 rename to 新的表名;

修改字符集

alter table 表名 character set 字符集名称;

添加一列

alter table 表名 add 列名 数据类型;

修改列名

alter table 表名 change 列名 新列别 新数据类型;
alter table 表名 modify 列名 新数据类型;

删除列

alter table 表名 drop 列名;

3. U(Update):修改
    1. 修改表名
        alter table 表名 rename to 新的表名;
    2. 修改表的字符集
        alter table 表名 character set 字符集名称;
    3. 添加一列
        alter table 表名 add 列名 数据类型;
    4. 修改列名称 类型
        alter table 表名 change 列名 新列别 新数据类型;
        alter table 表名 modify 列名 新数据类型;
    5. 删除列

表中常见的约束类型

当我们创建数据表的时候，还会对字段进行约束，约束的目的在于保证 RDBMS 里面数据的准确性和一致性。下面，我们来看下常见的约束有哪些。

主键约束

​ 主键起的作用是唯一标识一条记录，不能重复，不能为空，即 UNIQUE+NOT NULL。一个数据表的主键只能有一个。主键可以是一个字段，也可以由多个字段复合组成。在上面的例子中，我们就把 player_id 设置为了主键。

外键约束

​ 外键确保了表与表之间引用的完整性。一个表中的外键对应另一张表的主键。外键可以是重复的，也可以为空。比如 player_id 在 player 表中是主键，如果你想设置一个球员比分表即 player_score，就可以在 player_score 中设置 player_id 为外键，关联到 player 表中。

字段约束

唯一性约束

​ 唯一性约束表明了字段在表中的数值是唯一的，即使我们已经有了主键，还可以对其他字段进行唯一性约束。比如我们在 player 表中给 player_name 设置唯一性约束，就表明任何两个球员的姓名不能相同。需要注意的是，唯一性约束和普通索引（NORMAL INDEX）之间是有区别的。唯一性约束相当于创建了一个约束和普通索引，目的是保证字段的正确性，而普通索引只是提升数据检索的速度，并不对字段的唯一性进行约束。

NOT NULL 约束

​ 对字段定义了 NOT NULL，即表明该字段不应为空，必须有取值。

DEFAULT

​ 表明了字段的默认值。如果在插入数据的时候，这个字段没有取值，就设置为默认值。比如我们将身高 height 字段的取值默认设置为 0.00，即DEFAULT 0.00。

CHECK 约束

​ 用来检查特定字段取值范围的有效性，比如我们可以对身高 height 的数值进行 CHECK 约束，必须≥0，且＜3，即CHECK(height>=0 AND height<3)。

设计数据表的原则

​ 我们在设计数据表的时候，经常会考虑到各种问题，比如：用户都需要什么数据？需要在数据表中保存哪些数据？哪些数据是经常访问的数据？如何提升检索效率？ 如何保证数据表中数据的正确性，当插入、删除、更新的时候该进行怎样的约束检查？ 如何降低数据表的数据冗余度，保证数据表不会因为用户量的增长而迅速扩张？ 如何让负责数据库维护的人员更方便地使用数据库？ 除此以外，我们使用数据库的应用场景也各不相同，可以说针对不同的情况，设计出来的数据表可能千差万别。那么有没有一种设计原则可以让我们来借鉴呢？这里我整理了一个“三少一多”原则：

数据表的个数越少越好

​ RDBMS 的核心在于对实体和联系的定义，也就是 E-R 图（Entity Relationship Diagram），数据表越少，证明实体和联系设计得越简洁，既方便理解又方便操作。

数据表中的字段个数越少越好

​ 字段个数越多，数据冗余的可能性越大。设置字段个数少的前提是各个字段相互独立，而不是某个字段的取值可以由其他字段计算出来。当然字段个数少是相对的，我们通常会在数据冗余和检索效率中进行平衡。

数据表中联合主键的字段个数越少越好

​ 设置主键是为了确定唯一性，当一个字段无法确定唯一性的时候，就需要采用联合主键的方式（也就是用多个字段来定义一个主键）。联合主键中的字段越多，占用的索引空间越大，不仅会加大理解难度，还会增加运行时间和索引空间，因此联合主键的字段个数越少越好。

使用主键和外键越多越好

​ 数据库的设计实际上就是定义各种表，以及各种字段之间的关系。这些关系越多，证明这些实体之间的冗余度越低，利用度越高。这样做的好处在于不仅保证了数据表之间的独立性，还能提升相互之间的关联使用率。 你应该能看出来“三少一多”原则的核心就是简单可复用。

总结

​ 建表的优化思路就是指用更少的表、更少的字段、更少的联合主键字段来完成数据表的设计。可复用则是通过主键、外键的使用来增强数据表之间的复用率。因为一个主键可以理解是一张表的代表。键设计得越多，证明它们之间的利用率越高。

​ 很多互联网的公司，尤其是超大型的数据应用场景，大量的插入，更新和删除在外键的约束下会降低性能，同时数据库在水平拆分和分库的情况下，数据库端也做不到执行外键约束。另外，在高并发的情况下，外键的存在也会造成额外的开销。因为每次更新数据，都需要检查另外一张表的数据，也容易造成死锁。
所以在这种情况下，尤其是大型项目中后期，可以采用业务层来实现，取消外键提高效率