数据库基础：DDL数据定义语言

数据库基础DDL数据定义语言前言本文是“数据库基础”系列的第八篇。上一篇我们学习了 DML数据操作语言掌握了数据的增删改操作。本篇我们将进入 DDL数据定义语言Data Definition Language的学习——这是 SQL 语言中最基础的部分负责数据库和表结构的创建、修改与删除。如果说 DML 是在操作数据那 DDL 就是在搭建存放数据的仓库。掌握了 DDL才算真正学会了“造表”。目录一、库的管理二、表的管理三、常见数据类型四、常见约束五、标识列六、总结一、库的管理1.1 库的创建语法CREATEDATABASE[IFNOTEXISTS]库名;示例-- 创建数据库 mydb如果已存在则忽略CREATEDATABASEIFNOTEXISTSmydb;-- 创建数据库时指定字符集CREATEDATABASEmydb2CHARACTERSETutf8mb4;1.2 库的修改-- 修改数据库的字符集ALTERDATABASEmydbCHARACTERSETutf8mb4;1.3 库的删除-- 删除数据库如果存在则删除避免报错DROPDATABASEIFEXISTSmydb;二、表的管理2.1 表的创建语法CREATETABLE表名(列名 列的类型[(长度)][约束],列名 列的类型[(长度)][约束],...列名 列的类型[(长度)][约束]);示例以下案例涉及major表专业表和stuinfo表学生表两者存在外键关联。先创建主表major再创建从表stuinfo。-- 第一步创建主表 major专业表CREATETABLEmajor(idINTPRIMARYKEY,majornameVARCHAR(20)NOTNULL);-- 第二步创建从表 stuinfo学生表引用 major 表的 id 作为外键CREATETABLEstuinfo(idINTPRIMARYKEY,-- 学号主键stunameVARCHAR(20)NOTNULL,-- 姓名非空genderCHAR(1),-- 性别seatINTUNIQUE,-- 座位号唯一ageINTDEFAULT18,-- 年龄默认18majoridINT,-- 专业编号外键CONSTRAINTfk_stuinfo_majorFOREIGNKEY(majorid)REFERENCESmajor(id));通用的创建写法推荐CREATETABLEIFNOTEXISTSmajor(idINTPRIMARYKEY,majornameVARCHAR(20)NOTNULL);CREATETABLEIFNOTEXISTSstuinfo(idINTPRIMARYKEY,stunameVARCHAR(20)NOTNULL,genderCHAR(1),seatINTUNIQUE,ageINTDEFAULT18,majoridINT,CONSTRAINTfk_stuinfo_majorFOREIGNKEY(majorid)REFERENCESmajor(id));2.2 表的修改修改表名-- 将表名 stuinfo 改为 studentALTERTABLEstuinfoRENAMETOstudent;修改列名-- 将列名 stuname 改为 studentnameALTERTABLEstuinfo CHANGECOLUMNstuname studentnameVARCHAR(20);修改列的类型或约束-- 修改 age 列的类型为 TINYINTALTERTABLEstuinfoMODIFYCOLUMNageTINYINT;添加列-- 添加新列 phoneALTERTABLEstuinfoADDCOLUMNphoneVARCHAR(11);删除列-- 删除 phone 列ALTERTABLEstuinfoDROPCOLUMNphone;2.3 表的删除-- 删除表如果存在则删除-- 注意删除从表前需要先删除外键约束或先删除从表再删主表ALTERTABLEstuinfoDROPFOREIGNKEYfk_stuinfo_major;-- 先删外键DROPTABLEIFEXISTSstuinfo;DROPTABLEIFEXISTSmajor;2.4 表的复制仅复制表结构-- 创建一张新表 stu_copy只复制 stuinfo 的表结构不包含数据CREATETABLEstu_copyLIKEstuinfo;复制表结构数据-- 创建一张新表 stu_copy2复制 stuinfo 的结构和数据CREATETABLEstu_copy2ASSELECT*FROMstuinfo;只复制部分字段-- 只复制 id 和 stuname 字段不复制数据CREATETABLEstu_copy3ASSELECTid,stunameFROMstuinfoWHERE12;三、常见数据类型3.1 数值型整型类型字节范围有符号TINYINT1-128 ~ 127SMALLINT2-32768 ~ 32767MEDIUMINT3-8388608 ~ 8388607INT/INTEGER4-2147483648 ~ 2147483647BIGINT8-9223372036854775808 ~ 9223372036854775807-- 无符号整型不能为负数CREATETABLEtest_int(idINTUNSIGNED);小数类型说明FLOAT单精度浮点数DOUBLE双精度浮点数DECIMAL(M, D)定点数M 为总位数D 为小数位数-- DECIMAL 示例总位数5小数2位范围-999.99 ~ 999.99CREATETABLEtest_decimal(priceDECIMAL(5,2));3.2 字符型类型说明特点CHAR(M)固定长度字符串性能好但浪费空间VARCHAR(M)可变长度字符串节省空间性能稍差TEXT长文本存储大段文字BLOB二进制大对象存储图片、文件等-- CHAR 和 VARCHAR 对比CREATETABLEtest_string(nameCHAR(10),-- 固定10个字符不足补空格addressVARCHAR(50)-- 可变长度最多50个字符);3.3 日期型类型说明范围DATE日期1000-01-01 ~ 9999-12-31TIME时间-838:59:59 ~ 838:59:59DATETIME日期时间1000-01-01 00:00:00 ~ 9999-12-31 23:59:59TIMESTAMP时间戳受时区影响1970-01-01 00:00:00 ~ 2038-01-19YEAR年份1901 ~ 2155CREATETABLEtest_date(birthDATE,created_atDATETIMEDEFAULTNOW(),updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP);四、常见约束4.1 六大约束约束关键字说明非空NOT NULL字段值不能为空默认DEFAULT字段有默认值主键PRIMARY KEY唯一且非空一张表至多一个唯一UNIQUE字段值唯一可为空一张表可有多个检查CHECKMySQL 不支持语法支持但无效外键FOREIGN KEY引用主表的列值4.2 约束的添加方式列级约束写在列定义后面-- 先创建主表CREATETABLEmajor(idINTPRIMARYKEY,majornameVARCHAR(20)NOTNULL);-- 再创建从表列级约束CREATETABLEstuinfo(idINTPRIMARYKEY,-- 主键stunameVARCHAR(20)NOTNULL,-- 非空genderCHAR(1)CHECK(genderIN(男,女)),-- 检查MySQL无效seatINTUNIQUE,-- 唯一ageINTDEFAULT18,-- 默认majoridINT,-- 外键列级无效需表级CONSTRAINTfk_stuinfo_majorFOREIGNKEY(majorid)REFERENCESmajor(id));表级约束写在所有列定义之后-- 先创建主表CREATETABLEmajor(idINTPRIMARYKEY,majornameVARCHAR(20)NOTNULL);-- 再创建从表表级约束CREATETABLEstuinfo(idINT,stunameVARCHAR(20),genderCHAR(1),seatINT,ageINT,majoridINT,PRIMARYKEY(id),-- 主键UNIQUE(seat),-- 唯一FOREIGNKEY(majorid)REFERENCESmajor(id)-- 外键);4.3 主键 vs 唯一键对比项主键唯一键保证唯一性是是允许为空否是一个表可有几个至多1个多个是否允许组合允许不推荐允许不推荐4.4 外键-- 创建主表 majorCREATETABLEmajor(idINTPRIMARYKEY,majornameVARCHAR(20)NOTNULL);-- 插入主表数据INSERTINTOmajorVALUES(1,计算机科学),(2,软件工程),(3,大数据技术);-- 创建从表 stuinfo添加外键约束CREATETABLEstuinfo(idINTPRIMARYKEY,stunameVARCHAR(20),majoridINT,CONSTRAINTfk_stuinfo_majorFOREIGNKEY(majorid)REFERENCESmajor(id));-- 插入从表数据majorid 必须存在于 major 表中INSERTINTOstuinfoVALUES(1,张三,1);-- 有效INSERTINTOstuinfoVALUES(2,李四,2);-- 有效INSERTINTOstuinfoVALUES(3,王五,5);-- 报错majorid5 在 major 表中不存在外键注意事项在从表设置外键关系从表的外键列类型与主表的关联列类型要求一致或兼容主表的关联列必须是一个KEY主键或唯一键插入数据时先插主表再插从表删除数据时先删从表再删主表4.5 修改表时添加约束-- 先创建无约束的表CREATETABLEmajor(idINT,majornameVARCHAR(20));CREATETABLEstuinfo(idINT,stunameVARCHAR(20),genderCHAR(1),seatINT,ageINT,majoridINT);-- 添加非空约束ALTERTABLEstuinfoMODIFYCOLUMNstunameVARCHAR(20)NOTNULL;-- 添加默认约束ALTERTABLEstuinfoMODIFYCOLUMNageINTDEFAULT18;-- 添加主键ALTERTABLEstuinfoADDPRIMARYKEY(id);-- 添加唯一ALTERTABLEstuinfoADDUNIQUE(seat);-- 添加外键ALTERTABLEstuinfoADDCONSTRAINTfk_stuinfo_majorFOREIGNKEY(majorid)REFERENCESmajor(id);4.6 修改表时删除约束-- 删除非空约束ALTERTABLEstuinfoMODIFYCOLUMNstunameVARCHAR(20)NULL;-- 删除默认约束ALTERTABLEstuinfoMODIFYCOLUMNageINT;-- 删除主键ALTERTABLEstuinfoDROPPRIMARYKEY;-- 删除唯一ALTERTABLEstuinfoDROPINDEXseat;-- 删除外键ALTERTABLEstuinfoDROPFOREIGNKEYfk_stuinfo_major;五、标识列自增长列5.1 含义与特点标识列AUTO_INCREMENT也称为自增长列可以不用手动插入值系统提供默认的序列值。特点标识列不一定必须和主键搭配但要求是一个KEY主键或唯一键一个表最多只能有一个标识列标识列的类型只能是数值型INT、BIGINT等可以通过SET auto_increment_increment 步长设置步长MySQL 不支持设置起始值但可以通过手动插入值来设定5.2 创建表时设置标识列CREATETABLEtab_stu(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20));INSERTINTOtab_stuVALUES(NULL,shujia);SELECT*FROMtab_stu;5.3 设置步长-- 查看当前步长SHOWVARIABLESLIKE%auto_increment%;-- 设置步长为 3SETauto_increment_increment3;5.4 修改表时设置标识列ALTERTABLEtab_stuMODIFYCOLUMNidINTPRIMARYKEYAUTO_INCREMENT;5.5 修改表时删除标识列ALTERTABLEtab_stuMODIFYCOLUMNidINT;六、总结核心脉络库的管理创建CREATE DATABASE 库名修改ALTER DATABASE 库名一般改字符集删除DROP DATABASE 库名表的管理创建CREATE TABLE 表名 (字段名 字段类型 [约束], ...)常见数据类型INT、VARCHAR、CHAR、DATE、DECIMAL 等常见约束NOT NULL、DEFAULT、PRIMARY KEY、UNIQUE、FOREIGN KEY修改ALTER TABLE 表名 ADD / MODIFY / CHANGE / DROP / RENAME删除DROP TABLE 表名约束六大约束NOT NULL非空、DEFAULT默认、PRIMARY KEY主键、UNIQUE唯一、FOREIGN KEY外键、CHECK检查两种写法列级约束跟在字段后面、表级约束写在所有字段最后标识列AUTO_INCREMENT必须搭配KEY主键或唯一键字段类型必须为数值型INT、BIGINT 等可设置步长SET auto_increment_increment 步长三个关键要点数据类型VARCHAR是开发中最常用的字符串类型INT是最常用的整数类型DATETIME和TIMESTAMP需根据是否涉及时区来选择约束主键用于唯一标识一行唯一键用于保证字段值不重复外键用于维护表间关系标识列自增长列必须为数值型且必须是一个KEY一张表最多只有一个系列直达上篇数据库基础DML 数据操作语言本篇数据库基础DDL 数据定义语言本文下篇数据库基础TCL事务与DDL视图