mysql系列之数据定义语言(DDL)

miloyang
0 评论
/ /
587 阅读
/
13021 字
18 2023-09

数据库对于程序员来说，如同桌上的水杯，每天都在打交道，但踏入社会后许久，很多概念都已经模糊，趁此机会，全面复习一下。
安装卸载就略过吧。

MySql

分类 全称 说明
DDL Data Definition Language 数据定义语言，用来定义数据库对象，如：数据库、表、字段等等
DML Data Manipulation Language 数据操作语言，用来对数据表中的数据进行增、删、改
DQL Data Query Language 数据查询语言，用来查询 数据库中表的记录
DCL Data Control Language 数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

mysql> SHOW DATABASES;  # 查看当前用户下所有的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| milo_blog          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> CREATE DATABASE study; # 创建数据库
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE study1;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| milo_blog          |
| mysql              |
| performance_schema |
| study              |
| study1             |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

mysql> DROP DATABASE  if exists study1;  # 删除数据库
Query OK, 0 rows affected (0.00 sec)

mysql> USE study; # 选择数据库
Database changed

mysql> show tables; # 查看当前库下所有表
Empty set (0.00 sec)

mysql> create table tb_user(id int comment '序号', name varchar(50) comment '名称') comment '用户表'; # 建表
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+-----------------+
| Tables_in_study |
+-----------------+
| tb_user         |
+-----------------+
1 row in set (0.00 sec)

mysql> desc tb_user; # 查看表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

从上可看，DDL就创建：

1.创建数据库：

CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];

如：
CREATE DATABASE shopping default character set utf8mb4 collate utf8mb4_unicode_ci;

但是上述里面，我没指定字符集，那么mysql则会根据你的版本去指定默认字符集，有的版本是utf8mb4，有的是utf8，更老的甚至是latin1。 可以通过：

mysql> SHOW VARIABLES LIKE 'character_set_database';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set, 1 warning (0.00 sec)

mysql数据库支持很多字符集，常用的包括：

  • utf8mb4: 用于支持Unicode字符集，包括各种语言的字符、特殊符号和表情符号。这是在处理多语言文本时常用的字符集。
  • utf8: 也用于支持Unicode字符集，但只支持部分Unicode字符范围，适用于较少的语言字符需求。
  • latin1: 也称为ISO 8859-1，用于表示西欧语言字符集，包括英语、法语、西班牙语、德语等。
  • utf16: 支持Unicode字符集，使用16位编码，用于表示更广泛的字符范围，包括一些较少使用的字符。
  • gbk: 用于表示汉字和简化字，主要在中国的数据库中使用。
  • big5、utf32、binary、cp1251、sjis、eucjpms、koi8r等等。

而通常 utf8mb4 是一个良好的选择，因为它可以支持多种语言和字符(表情)，并促进国际化。
可以修改为utf8mb4，前天是先进行备份，防止发生意外，如下： 

mysql> ALTER DATABASE study CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT DATABASE(); # 查看当前所在的数据库
+------------+
| DATABASE() |
+------------+
| study      |
+------------+

mysql> SELECT @@character_set_database, @@collation_database; # 确认下是否修改成功，需要先USE进库才可以。
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_unicode_ci   |
+--------------------------+----------------------+
1 row in set (0.00 sec)

排序规则

用于指定数据库中字符数据的排序规则，具体来说，排序规则定义了字符之间的比较方式，以确定它们在排序操作中的顺序。不同的排序规则可以影响字符串比较的结果，尤其是在涉及到大小写、重音符号、特殊字符和语言特定规则时。 

mysql> SELECT @@collation_database;
+----------------------+
| @@collation_database |
+----------------------+
| utf8mb4_unicode_ci   |
+----------------------+

如utf8mb4_unicode_ci就是utf8mb4字符集的排序规则，其中：

  • unicode:这部分表示采用采用Unicode排序规则，Unicode几乎包含了世界上所有的字符，允许跨语言和跨文化的字符比较和排序。第二段还有很多选项，常用的如general，表示通用排序规则，不考虑语言的排序规则，偏向基本的字符比较，。比如(以下由ChatGPT提供)：
使用 utf8mb4_general_ci 排序规则的排序结果是：

"apple"
"Banana"
"éclair"
"Éclair"
使用 utf8mb4_unicode_ci 排序规则的排序结果是：

"apple"
"Banana"
"Éclair"
"éclair"
注意，在 utf8mb4_unicode_ci 规则下，"Éclair" 排在 "éclair" 前面，因为它考虑了重音符号的大小写差异。
这个示例显示了两种排序规则的不同行为，具体使用哪种规则取决于你的应用程序需要处理的文本和排序需求。
  • ci：case-insensitive缩写，表示不区分大小写，如A和a在排序时认为是相等的

2.删除数据库：

DROP DATABASE [ IF EXISTS ] 数据库名;

3.创建表

CREATE TABLE 表名(
    字段1 字段1类型 [COMMENT 字段1注释],
    字段2 字段2类型 [COMMENT 字段2注释],
    字段3 字段3类型 [COMMENT 字段3注释],
    ...
    字段n 字段n类型 [COMMENT 字段n注释]
)[ COMMENT 表注释 ];

字段类型 可以来跳转查看，下面列举下载工作中容易出现的问题：

  • 很多人定义年龄age，都是 int (占用4个字节)，但可以使用 tinyint unsigned (0-255)，无符号的小整数值，这样只会占用1个字节，当然，需求如果说精确到月那另说。
  • 金钱，很多人都直接定义float或者double，但更加建议使用 decimal。它允许明确指定小数点后的位数，也明确控制舍入规则，避免精度问题。与浮点数不同，它不会引入意外的舍入误差，可以确保准确的金融数据处理。
  • char(定长字符串，性能高)和varchar(变长字符串，性能较差，因为使用的时候需要根据内容去计算空间)，也就是说char(10),哪怕你存1个字符，也使用了10个字符的空间，剩下的使用空格补位，所以存定长的如身份证号，使用char，性能高。
    varchar(10),你存1个字符，就只占1个字符的空间。所以根据业务来，我们使用varchar的时候，后面长度大部分是用于限制大小，比如标题不能超过20个字，varchar(20)，但是，这个应当代码限制，更加建议定义成varchar(255)，这样后续修改了标题限制，是不用写迁移的。
    为什么我们一般都是定义varchar为255呢？难不成varchar只能支持255吗？其实不然，只是当长度大于255的时候，长度标识就需要两个字节了，无形中浪费了存储空间，因为两个字节可以表达256-65525个长度，这样还不如直接定义成text更加方便扩展呢。
  • 时间定义，如创建时间，一般有两种，一种是时间戳，采用bigint，一种是datetime，混合日期和时间。得需要看情况，如果你的产品要做国际化，那么涉及到时区的概念，建议使用bigint，因为时间戳都是0时区的相对，可以转换成任何时区的日期。如果不做国际化，那直接上datetime吧，简单清晰明了。千万不要选timestamp，那个只能到2038年。

4.修改表

  • 添加一个字段
    ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
mysql> alter table tb_user add age tinyint unsigned comment '年龄';  # 添加字段
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc tb_user;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | int(11)             | YES  |     | NULL    |       |
| name  | varchar(50)         | YES  |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • 修改数据类型
    ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
mysql> alter table tb_user modify name varchar(100); # name字段修改为varchar(100)
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_user;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | int(11)             | YES  |     | NULL    |       |
| name  | varchar(100)        | YES  |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • 修改字段名和字段类型
    ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
mysql> alter table tb_user change name username varchar(50);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_user;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id       | int(11)             | YES  |     | NULL    |       |
| username | varchar(50)         | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+

  • 删除字段：

    ALTER TABLE 表名 DROP 字段名;

  • 修改表名：

    ALTER TABLE 表名 RENAME TO 新表名

  • 删除表，表和数据都没了：

    DROP TABLE [IF EXISTS] 表名;

  • 清除表，表结构还在，数据没了：

    TRUNCATE TABLE 表名;

约束

作用在表字段上的规则，用于限制存储在表中的数据。
保证数据库中数据的正确、有效、完整性。

常用约束

约束主要有以下常用：

约束 描述 关键字
非空约束 限制该字段的数据不能为null NOT NULL
唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE
主键约束 主键是一行数据的唯一标识，要求非空且唯一 PRIMARY KEY
默认约束 保存数据时，如果未指定该字段的值，则采用默认值 DEFAULT
检查约束（8.0.1版本后） 保证字段值满足某一个条件 CHECK
外键约束 用来让两张图的数据之间建立连接，保证数据的一致性和完整性 FOREIGN KEY

举例：
新增一张表，需要有如下字段： 

id：主键，且自增长
name:不为空
age:大于0，且小于130
email:不为空，且唯一
status:默认为1
gender:无要求

create table tb_person
(
    id     bigint(20) primary key auto_increment,
    name   varchar(255) not null,
    age    int check (age > 0 && age <= 130),
    email  varchar(255) not null unique,
    status int default 1,
    gender int
) ENGINE = INNODB
  DEFAULT CHARSET = utf8 COMMENT = '人员表';

注意点：

  • id自增，假设当某一条数据插入不成功，则此次id也会自增，如当前已一条数据id为1，后续再插入一条email有重复的数据，插入报错，修改后插入成功，此时id已经自增到了3。因为在插入的时候，无论成功与否，id都已经申请到了值。
  • 检查约束check，在8.0.1之后才生效，之前版本无感知。
  • 在工作中，一般都不会采用主键自增，因为id一般作为唯一识别码，如果采用自增，会被黑客猜到id而有安全隐患。

外键约束

涉及到外键约束，肯定就有两张表
举例：
部门表 

create table dept (
    id int auto_increment primary key ,
    name varchar(50) not null
)ENGINE = INNODB
 DEFAULT CHARSET = utf8 COMMENT = '部门';

员工表

create table emp(
    id int primary key auto_increment,
    name varchar(50) not null ,
    dept_id int not null 
)ENGINE = INNODB
 DEFAULT CHARSET = utf8 COMMENT = '员工表';

可见员工表有个dept_id和dept关联了，通常我们把dept称之为父表，emp称之为子表。但子表和父表只是逻辑上关联，我可以把dept中的一个字段进行删除，哪怕已经被emp给关联上了。这样就会造成数据的不一致性，容易有脏数据，此时可以进行外键约束。
添加外键的约束一般有两种，一种是建表的时候添加，一种是修改表。

  • 建表的时候添加 

    create table emp(
  id int primary key auto_increment,
  name varchar(50) not null ,
  dept_id int not null ,
  foreign key (fk_emp_dept_id)references dept(id) # 约束一般都是写在字段全部创建完之后
)ENGINE = INNODB
 DEFAULT CHARSET = utf8 COMMENT = '员工表';

  • 建表后通过修改表结构添加外键

    一般都不这么干，都是在建表的时候添加

    alter table emp add constraint fk_emp_dept_id foreign key (dept_id)references dept(id)

    这样，dept的数据就不能随便删除了。因为在删除dept的一行时，mysql会判断是否存在关联关系，如果存在则不允许删除。

  • 当然咯，删除外键也是有命令的

    alter table emp drop foreign key fk_emp_dept_id

    我在工作中，几乎就没用到过外键删除。

外键约束，也可以设置删除/更新的行为

行为 说明
NO ACTION 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与RESTRICT一致）
RESTRICT 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与NO ACTION一致）
CASCADE 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录
SET NULL 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（要求该外键允许为null）
SET DEFAULT 父表有变更时，子表将外键设为一个默认值（Innodb不支持）

更改删除/更新行为： ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;

本文总结

当然咯，这些命令在工作中是需要知道，但是一般我们都是采用工具做这些事情，如：Navicat(很多公司都不能使用这个，因为Navicat公司查盗版比较严重)、DataGrip(推荐)。
感谢：https://www.runoob.com/mysql/mysql-data-types.html

转载请注明来源： miloyang
本文链接： boxiaoyang.cn/article/126
<<mysql系列之数据操作语言(DML)
数据结构之二叉树>>
人未眠
工作数十年
脚步未曾歇，学习未曾停
乍回首
路程虽丰富，知识未记录
   借此博客，与之共进步

分类