• Post author:
  • Post category:mysql
  • Post comments:0评论

一、简介

  DDL即数据定义语言,用于定义数据库、表和视图等。
  常用DDL命令有:
   create:  创建数据库或者表
   alter:  修改数据库或者表结构
   drop:   删除数据库或者表
   truncate: 删除表中的数据不删除表结构。
   rename:  修改表名或者列名

  在详细了解DDL语句时,有必要了解以下内容:

1、字符集和校对规则

字符集:多个字符的集和。
校对规则:是在字符集内用于字符比较和排序的一套规则,比如有的规则区分大小写,有的则无视。

(1)查看字符集
mysql> show charset;

(2)查看默认字符集
mysql> show variables like '%char%';
+--------------------------+---------------------------------------------------------------+
| Variable_name            | Value                                                         |
+--------------------------+---------------------------------------------------------------+
| character_set_client     | utf8                                                          |
| character_set_connection | utf8                                                          |
| character_set_database   | latin1                                                        |
| character_set_filesystem | binary                                                        |
| character_set_results    | utf8                                                          |
| character_set_server     | latin1                                                        |
| character_set_system     | utf8                                                          |
| character_sets_dir       | /data/app/mysql-5.7.28-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------------+

  以上我们只看utf8和utf8mb4
    utf8:字符最大长度为3字节
    utf8mb4:字符最大长度为4字节,支持emoji表情字符。一般常用此字符集。

(3)查看校对规则
mysql> show collation;

  • 不同的字符集有着自己对应的校对规则。
  • 每个字符集有一个默认校对规则,例如utf8mb4字符集默认校对规则为utf8mb4_general_ci 
  • ci表示不区分大小写、cs表示区分大小写、bin区分大小写。

2、常用的数据类型

不同的数据类型可以为表的每列限定数据的取值范围,实现数据的域完整性约束。

(1) 数字类型
  • int:整数。字节大小为4。范围,正:0~2^32-1;负:-2^31 ~2^31-1
  • tinyint:更小的整数。字节大小为1。范围,正:0~255;负:-127~128。
  • decimal(M,D):小数类型。M 浮点型数值的总位数;D 浮点型数值的小数保留位数。

(2) 字符串类型
  • char(10):定长类型的字符串类型。最多存储10个字符。如果存了5个,剩余空间用空格填充。 
  • varchar(10):变长类型的字符串类型。最多存储10个字符。如果存了5个,按需分配存储空间,另外需要1-2字节,存储字符长度。 

  一般情况下变长字符串就用varchar,固定长度一般采用char类型。

(3)枚举类型
  • enum():其值是从事先指定的一系列值中选出,适用在某列的取值范围已经固定,例如性别、省份。

(4)时间日期类型
  • date:范围从 1000-01-01 至 9999-12-31
  • datetime:范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
  • timestamp:范围为从 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999,受到时区的影响。

3、约束和其它列属性

    Primary key:主键约束 ,要求设置为主键的列,储值时,非空且唯一。每张表只有一个主键。
    not null:      非空约束,必须输入值。
    unique key: 唯一约束,不能输入重复值。
    unsigned:    数字类型约束,无符号。
    default:       设置默认值,一般配合not null 使用。
    auto_increment: 针对数字列,自动增长,一般配合主键。
    comment:    列或者表进行注释

二、库定义

1、 创建库 
mysql> create database test charset utf8;

2、 查询库
mysql> show databases;
mysql> show create database test;

3、 修改库
mysql> alter database test charset utf8mb4;

4、 删除库
mysql> drop database test;

三、表定义

1、创建表
mysql> create table stu (
id int not null primary key auto_increment comment '学号',
name varchar(20) not null comment '姓名',
age tinyint unsigned not null default 0 comment '年龄',
sex enum('male','female') not null comment '性别',
adtime datetime not null default '2020-9-1' comment '入学时间'
)engine=innodb charset=utf8mb4 comment '学生表';

2、 查表定义
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| stu            |
+----------------+

mysql> desc stu;
+--------+-----------------------+------+-----+---------------------+----------------+
| Field  | Type                  | Null | Key | Default             | Extra          |
+--------+-----------------------+------+-----+---------------------+----------------+
| id     | int(11)               | NO   | PRI | NULL                | auto_increment |
| name   | varchar(20)           | NO   |     | NULL                |                |
| age    | tinyint(3) unsigned   | NO   |     | 0                   |                |
| sex    | enum('male','female') | NO   |     | NULL                |                |
| adtime | datetime              | NO   |     | 2020-09-01 00:00:00 |                |
+--------+-----------------------+------+-----+---------------------+----------------+

mysql> show create table stu;

3、修改表定义

(1)在表中添加列
mysql> alter table stu add column telnum char(11) not null unique key comment '手机号码';

(2 在第一列前添加a列
mysql> alter table stu add column a int first;

(2)在name后添加b列
mysql> alter table stu add column b int after name;

(4)删除添加的a,b列
mysql> alter table stu drop column a;
mysql> alter table stu drop column b;

(5)修改数据类型 
mysql> alter table stu modify name varchar(50) not null comment '姓名';

(6)修改列名及数据类型
mysql> alter table stu change name sname varchar(20) not null comment '姓名';

(7)修改表名
mysql> rename table stu to student;
mysql> alter table student rename to stu;

4、删除表
mysql> drop table stu;

四、DDL语句开发规范

库:
    1. 库名要与业务有关。
    2. 库名不使用大写字母、数字开头。
    3. 不要使用内置关键字。
    4. 建库要指定字符集。
    5. 生产中禁止使用删库操作。

表: 
    1. 表名:与业务有关,例如:wp_users,不使用大写字母、数字开头,不要太长(16以下)。
    2. 设置存储引擎、字符集、表注释。
    3. 表名、列名不要使用内置关键字。
    4. 列名要有意义,长度(16以下)。
    5. 数据类型:合适的、足够的、简短。
    6. 每个表要有主键,一般是自增长、无关列数字列。
    7. 每个列尽量是not null ,可以配合default。
    8. 每个列要有注释。
    9. 修改定义的操作,要在业务不繁忙期间去做。如果紧急可以使用pt-osc 。

发表评论

验证码: 1 + 7 =