前言
DDL,即Data Define Language, 数据库定义语言。
数据库定义语言包含库和表的管理,当然我认为还包括索引等。但这篇文章中只是介绍数据库的增删改、表的增删改、数据类型以及常见的约束。内容比较简单,适合了解mysql的同学了解。
一、库的管理
1 创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名;
如:
CREATE DATABASE IF NOT EXISTS books;
IF NOT EXISTS
是可选项,这是为了防止当前mysql实例中已存在当前数据库而报错。
一般创建数据库是通过下面方式
DROP DATABASE IF EXISTS books
CREATE DATABASE books;
也就是先删除再创建,这样就不会出现导入数据库不一致的情况了。
2 修改数据库名
RENAME DATABASE 旧数据库名 TO 新数据库名; #不推荐修改
3 更改数据库的字符集
ALTER DATABASE 数据库名 CHARACTER SET 字集名 # 字集名,如gdk、utf8
4 删除数据库
DROP DATABASE [IF EXISTS] 数据库名
IF EXISTS
是可选项,它的意思是如果存在则执行,这是为了防止mysql实例中不存在要删除的数据库而报错。
二、表的管理
1 创建表
CREATE TABLE 表名(
别名 列的类型[(长度) 约束],
别名 列的类型[(长度) 约束],
别名 列的类型[(长度) 约束],
...
别名 列的类型[(长度) 约束]
)
这里有两个地方涉及的东西是比较多的,如数据类型、约束,这两块在下面是进行专门的讲解的。这里先引入一个例子吧
CREATE TABLE `grade` (
`id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '唯一识别码',
`studentId` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'student唯一标识码',
`class` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '户口性质',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
这个例子约束条件比较多啊,感兴趣的朋友可以去查一下,当然直接看也不是很难懂。
这个和上面创建库一样,也是有个比较通用的写法的
DROP TABLE IF EXISTS 表名;
CREATE TABLE 表名();
这样也是为了导入表的正确性
2 修改表
其实修改表除了修改表名以外,更多对表的调整集中在修改表中的列,下面这些语法同样如此
2.1 修改表名
ALTER TABLE 旧表名 RENAME TO 新表名;
2.2 修改列名
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型 约束;
这里一定要将类型和约束带上,因此其实也是可以同时修改类型和约束的。
2.3 修改列的类型或者约束
ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 新约束条件
如修改上面新建表中的class的长度和注释
ALTER TABLE `grade_new` MODIFY COLUMN `class` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '年级';
可以看出来,我修改了字段长度和注释,原则上修改哪部分就改哪部分就可以了,但是其他部分也需要原样写在上面。
2.4 添加列
ALTER TABLE 表名 ADD COLUMN 新列名 类型 约束;
2.5 删除列
ALTER TABLE 表名 DROP COLUMN 列名;
三、复制表
复制表的含义是通过已有的表去创建新表,可以仅仅复制表的结构、复制表的结构+数据、复制表的部分字段或部分字段、仅仅复制表的部分结构。
下面被复制的表称为旧表,因此创建的新表称之为新表
1 复制表结构
CREATE TABLE 新表名 LIKE 旧表;
CREATE TABLE `grade_new_2` LIKE `grade_new`; # 例子
如上,使用旧表的表结构来创建新表。
2 复制表结构和数据
同时复制表结构和数据给新表
CREATE TABLE 新表
SELECT * FROM 旧表;
例子:
CREATE TABLE `grade_new_2`
SELECT * FROM `grade_new`;
这里用到了类似子查询的方式,根据目前我的理解,下面的SELECT查询会生成一个虚拟表,然后会将这个虚拟表直接来创建一个新表,因此也就延伸出了下面的复制部分字段、复制部分数据、只创建部分数据结构,只需要加个筛选就可以了。
其实这里还可以继续延伸,比如SQL中的连接查询、分组、排序、分页等,下面只举例部分
3 延伸复制表结构
# 复制部分数据
CREATE TABLE `grade_new_3`
SELECT * FROM `grade_new` WHERE id = 10;
# 复制部分字段和数据
CREATE TABLE `grade_new_4`
SELECT id,class FROM `grade_new` WHERE id = 10;
# 复制部分字段,并修改新表字段名
CREATE TABLE `grade_new_4`
SELECT id AS ID,class AS class_new FROM `grade_new` WHERE id = 10;
# 只复制部分表结构,不复制数据
CREATE TABLE `grade_new_5`
SELECT id,class FROM `grade_new` WHERE 1=2;
# 复制多个表数据及结构到新表中
CREATE TABLE `grade_new_6`
SELECT grade.id AS id, grade.class AS class, stu.`name` AS `name`, stu.sex AS sex, stu.address AS address
FROM `grade_new` grade
LEFT JOIN `student` stu
ON grade.studentId = stu.id;
四、数据类型
sql中常见的数据类型:
数值型:
- 整型
- 小数:定点数、浮点数
字符型:
- 较短文本:char、varchar
- 较长文本:text、blob(较长二进制数据)等
日期型
1 数值型
1.1 整型
sql中有多个类型可以体现整型,下面这个表格是不全介绍,但是常用的都是有的。
类型 | 占字节数 |
---|---|
TINYINT | 1 |
SMALLINT | 2 |
MEDIUMINT | 3 |
INT/INTEGER | 4 |
BIGINT | 8 |
特点
- 如果不设置符号,默认是有符号的数值,也就是说二进制第一位是符号位;如需要设置无符号整数,只需要加上
UNSIGNED
关键字就可以了; - 如果插入的数值超过了当前设置整型类型的范围,会报一个
Out of range
警告,并且插入一个临界值; - 如果不设置长度,会有默认的长度,这种默认长度,我感觉不用记;
- 长度代表了显示的最小宽度,如果不够会用0在左边填充,但是必须搭配
ZEROFILL
使用。注:使用ZEROFILL
后默认强制就是UNSIGNED
,而且长度无法显示整型的范围,它只是单纯的限制整型的显示方式。
1.2 小数
小数可以分为浮点型、定点型。
- 浮点型:float(M,D)、double(M,D)
- 定点型:dec(M,D)、decimal(M,D) 注:这两个是一个类型
特点:
- M:整数+小数部分长度;D:小数部分;如果超过范围则插入临界值
- M和D都可以省略,如果是decimal,则M默认为10,D默认为0;如果是float和double,则会根据插入的数值的精度来决定精度。
- 定点型的精度准确度要求较高,如果要求插入数值的精度较高,如货币运算等则考虑使用定点型。
这里需要和上面的整型区分开,这里的D和M确实是可以控制数值的取值范围的,如小数位2位,超出2位则四舍五入,123.499
会变成123.50
。如整数位就是M-D,若超出则去临界值。
2 字符型
较短文本:
- char
- varchar
- enum
- set
- binary
- varbinary
较长文本:
- text
- blob
2.1 char和varchar
这两个类型是平时比较常用的数据类型,也正是因为常用,这里应该更详细一些。
类型 | 写法 | 特点 | 空间消耗 | 效率 |
---|---|---|---|---|
char | char(M),M最大字符数,可以省略,默认为1 | 固定的字符长度 | 比较消耗空间 | 高 |
varchar | varchar(M),M最大字符数,不可以省略 | 可变长度字符 | 比较节省 | 低 |
这里需要注意,这里的所谓节省、消耗、高、低都是对比char、varchar而得来的。
固定字符长度也就是指,对于char
而言,这个字段的固定字符就是设置的这些,无论内容实际长度是多长,若超出了则插入失败,若低于则实际也占用两个字符的存储空间。
可变长度字符是指varchar
类型插入的数据字符数低于所设置长度,则只是实际存储对应长度的字符,不会占用过多的内存。
2.2 binary和varbinary
这两个类型是存储二进制数据的,他们的差别和上面char
和varchar
是一样的。
2.3 enum
和java中的enum
类型类似,叫做枚举类型,要求插入的值必须属于列表中指定的值之一。
如果列表成员为1-255,则需要1个字节存储。
如果列表成员为255-65535,则需要2个字节存储。
2.4 set
和Enum类型类似,里面可以保存0-64个成员;
和Enum类型最大的区别在于,Set类型一次性可以选取多个成员,而Enum只能选一个。
2.5 text
TEXT类型一般分为 TINYTEXT(255长度)、TEXT(65535)、 MEDIUMTEXT(int最大值16M),和LONGTEXT(long最大值4G)这四种,它被用来存储非二进制字符集。
text列不允许拥有默认值
这个可以参考这篇文章
2.6 blob
MySQL有四种BLOB类型,这块偷懒了,以后用到再整理。
3 日期型
日期和时间类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
date | 4 | 1000-01-01 | 9999-12-31 |
datetime | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
timestamp | 4 | 19700101080001 | 2038年某个时刻 |
time | 3 | -838:59:59 | 838:59:59 |
year | 1 | 1901 | 2155 |
上边这些类型其实没啥好说的,主要介绍一下timestamp
这个类型吧
timestamp 和实际时区有关,更能反映实际的日期,而其他类型都只能反映插入时的当地时区。timestamp
属性受Mysql版本和SQLMode影响很大。
关于数据库时区可以通过下面语句进行查看和设置
SHOW VARIABLES LIKE 'time_zone'; # 查看当前数据库所在时区,这个语句也是查询mysql值常用的查询语句
SET time_zone='+9:00'; # 设置当前数据库所在时区,设置mysql中属性值
timestamp
类型查询出来的结果集确实会根据时区的不同而改变,但是如果去表中查看实际值的话,仍然显示插入时的时间。
五、约束条件
表字段的约束就是一种限制,用于限制表中数据,为了保证表中数据的准确和可靠性。
六大约束分类:
- NOT NULL:非空,用于保证该字段的值不能为空;
- DEFAULT:默认值,用于保障该字段在不输入的情况下自动填入默认值;
- PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空;
- UNIQUE:唯一键,用于保证该字段值具有唯一性,可以为空;
- CHECK:检查约束(mysql中不支持,但是设置的话,也不会报错)
- FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值,在从表添加外键约束,用于指向主表中某列的值。
主键和唯一键的区别:
name | 唯一性 | 允许为空 | 表中可以有多少个 | 是否允许组合 |
---|---|---|---|---|
主键 | 保证唯一性 | 不允许为空 | 至多有一个 | 可以,不推荐 |
唯一键 | 保证唯一性 | 允许为空 | 可以有多个 | 可以,但不推荐 |
所谓的组合,就是指是否可以将多个字段一起作为一个key,比如上面grade
表,我们可以将id
和studentId
统一作为主键或者唯一键来使用。
外键:
- 要求在从表设置外键关系,从表的外键列类型和主表的关联列类型要求一致或兼容,名称无要求;
- 主表的关联列必须是一个key,一般是主键或者唯一键;
- 插入数据时,先插入主表,再插入从表,删除数据时,先删除从表,在删除主表。
关于外键约束,现在工作中用的比较少,主要是因为外键约束会影响数据库的迁移。
1 添加约束
根据添加约束的方式不同可以分为:
- 列级约束:六大约束除了外键约束都可以支持;
- 表级约束:除了非空、默认,其他都支持
1.1 添加列级约束
CREATE TABLE `student`(
`id` INT PRIMARY KEY, # 主键
`stu_name` VARCHAR(20) NOT NULL, # 非空
`seat` INT UNIQUE, # 唯一键
`age` INT DEFAULT 18 # 默认值
);
1.2 添加表级约束
CREATE TABLE `grade_new_7`(
`id` INT ,
`stu_name` VARCHAR(20) NOT NULL, # 非空
`seat` INT,
`age` INT DEFAULT 18, # 默认值
`gradeId` varchar(32),
CONSTRAINT pk PRIMARY KEY(id), # 主键
CONSTRAINT uq UNIQUE(seat), # 唯一键
CONSTRAINT fk_grade FOREIGN KEY(gradeId) REFERENCES grade_new(id) # 外键
);
可以看出来,所谓的表级约束和列级约束,只是添加方式的不同,是可以混合使用的。
这里注意一下,CONSTRAINT
是设置key的别名(可以省略),另外观察一下也可以发现在mysql中,表级约束就是在指key。
外键约束这里要求比较多,上面也是有对外键约束的条件的。按照这个去写问题不大。
2 修改约束条件
上面是在创建表的时候添加约束,而这里是在表已经创建后,修改表的约束条件。
问题不大,直接上约束实例吧
添加约束
# 添加非空约束
ALTER TABLE `grade_new` MODIFY COLUMN `class` varchar(200) NOT NULL COMMENT '年级';
# 添加默认约束
ALTER TABLE `grade_new` MODIFY COLUMN `class` varchar(200) DEFAULT 18 COMMENT '年级';
# 添加主键
# 列级约束
ALTER TABLE `grade_new` MODIFY COLUMN id INT PRIMARY KEY;
# 表级约束
ALTER TABLE `grade_new` ADD PRIMARY KEY(id);
# 添加唯一键
# 列级约束
ALTER TABLE `grade_new` MODIFY COLUMN `studentId` varchar(32) DEFAULT NULL UNIQUE COMMENT 'student唯一标识码';
# 表级约束
ALTER TABLE `grade_new` ADD [CONSTRAINT `studentId`] UNIQUE(`studentId`);
# 添加外键
ALTER TABLE `grade_new` ADD [CONSTRAINT fk_grade] FOREIGN KEY(gradeId) REFERENCES grade_new(id);
删除约束
# 删除非空约束
ALTER TABLE `grade_new` MODIFY COLUMN `class` varchar(200) NULL COMMENT '年级';
# 删除默认约束
ALTER TABLE `grade_new` MODIFY COLUMN `class` varchar(200)
# 删除主键
ALTER TABLE `grade_new` DROP PRIMARY KEY;
/*
删除唯一键,`studentId`是key的名字,当没有指定key的别名时,默认字段名就是key名
只有表级约束才能添加别名
*/
ALTER TABLE `grade_new` DROP INDEX `studentId`;
# 删除外键,同样是别名,与上面同理
ALTER TABLE `grade_new` DROP FOREIGN KEY fk_grade;
可以看到删除约束时,表级约束和列级约束删除不太一样,表级约束的删除方式还是通俗易懂的,列级约束其实就是修改表字段,在修改的过程中,我认为它内部实现应该是全覆盖的方式,也就是将新的列的DDL直接覆盖原来的DDL,这样的话,添加就直接加上对应的约束,删除就减少对应的约束就可以了。纯粹猜测,助于理解,尚未求证
六、标识列
我初次看到这个名字的时候感觉很陌生,但是了解这个概念后,发现还是比较简单的,它就是自增长列,这个在一些工作中可以遇到,具体含义:当某些字段被设置成标识列后,如果在插入数据的时候,该字段为空,则默认添加一个自增长的数值,这个自增长的数值可以通过设置mysql的变量来定义步长。
特点:
- 标识列必须要要求是一个key,并且一个表至多有一个标识列;
- 标识列只能是数值型;
- 标识列可以通过
SET auto_increment_increment=n
来设置,n就是具体步长。
设置步长例子
查看当前mysql的自增长步长
SHOW VARIABLES LIKE '%auto_increment%';
# 设置步长为3
SET auto_increment_increment = 3;
设置字段为自增长列
CREATE TABLE `student`(
`id` INT PRIMARY KEY AUTO_INCREMENT, # 主键
`stu_name` VARCHAR(20) NOT NULL, # 非空
`seat` INT UNIQUE, # 唯一键
`age` INT DEFAULT 18 # 默认值
);
结语
本节其实总结得并不全面,我是根据视频和工作中涉及到的去整理的,如果涉及不到的内容,不去深究,以后再处理。
所以如果感觉文章哪里有问题,麻烦指点一下,谢谢!