CoCalc provides the best real-time collaborative environment for Jupyter Notebooks, LaTeX documents, and SageMath, scalable from individual users to large groups and classes!
CoCalc provides the best real-time collaborative environment for Jupyter Notebooks, LaTeX documents, and SageMath, scalable from individual users to large groups and classes!
Path: blob/master/Day36-45/37.SQL详解之DDL.md
Views: 729
SQL详解之DDL
我们通常可以将 SQL 分为四类,分别是 DDL(数据定义语言)、DML(数据操作语言)、 DCL(数据控制语言)和 TCL(事务控制语言)。DDL 主要用于创建、删除、修改数据库中的对象,比如创建、删除和修改二维表,核心的关键字包括create
、drop
和alter
;DML 主要负责数据的插入、删除、更新和查询,关键词包括insert
、delete
、update
和select
;DCL 用于授予和召回权限,核心关键词是grant
和revoke
;TCL 通常用于事务控制。
说明:SQL 是不区分大小写的语言,有人会建议将关键字大写,其他部分小写。为了书写和识别方便,下面的 SQL 我都是使用小写字母进行书写的。 如果公司的 SQL 编程规范有强制规定,那么就按照公司的要求来,个人的喜好不应该凌驾于公司的编程规范之上,这一点对职业人来说应该是常识。
建库建表
下面我们来实现一个非常简单的学校选课系统的数据库。我们将数据库命名为school
,四个关键的实体分别是学院、老师、学生和课程,其中,学生跟学院是从属关系,这个关系从数量上来讲是多对一关系,因为一个学院可以有多名学生,而一个学生通常只属于一个学院;同理,老师跟学院的从属关系也是多对一关系。一名老师可以讲授多门课程,一门课程如果只有一个授课老师的话,那么课程跟老师也是多对一关系;如果允许多个老师合作讲授一门课程,那么课程和老师就是多对多关系。简单起见,我们将课程和老师设计为多对一关系。学生和课程是典型的多对多关系,因为一个学生可以选择多门课程,一门课程也可以被多个学生选择,而关系型数据库需要借助中间表才能维持维持两个实体的多对多关系。最终,我们的学校选课系统一共有五张表,分别是学院表(tb_college
)、学生表(tb_student
)、教师表(tb_teacher
)、课程表(tb_course
)和选课记录表(tb_record
),其中选课记录表就是维持学生跟课程多对多关系的中间表。
上面的DDL有几个地方需要强调一下:
首先,上面 SQL 中的数据库名、表名、字段名都被反引号(`)包裹起来,反引号并不是必须的,但是却可以解决表名、字段名等跟 SQL 关键字(SQL 中有特殊含义的单词)冲突的问题。
创建数据库时,我们通过
default character set utf8mb4
指定了数据库默认使用的字符集为utf8mb4
(最大4
字节的utf-8
编码),我们推荐使用该字符集,它也是 MySQL 8.x 默认使用的字符集,因为它能够支持国际化编码,还可以存储 Emoji 字符。可以通过下面的命令查看 MySQL 支持的字符集以及默认的排序规则。如果要设置 MySQL 服务启动时默认使用的字符集,可以修改MySQL的配置并添加以下内容。
提示:如果不清楚如何修改 MySQL 的配置文件就先不要管它。
创建和删除数据库时,关键字
database
也可以替换为schema
,二者作用相同。建表语句中的
not null
是非空约束,它限定了字段不能为空;default
用于为字段指定默认值,我们称之为默认值约束;primary key
是主键约束,它设定了能够唯一确定一条记录的列,也确保了每条记录都是独一无二的,因为主键不允许重复;foreign key
是外键约束,它维持了两张表的参照完整性,举个例子,由于学生表中为 col_id 字段添加了外键约束,限定其必须引用(references
)学院表中的 col_id,因此学生表中的学院编号必须来自于学院表中的学院编号,不能够随意为该字段赋值。如果需要给主键约束、外键约束等起名字,可以使用constriant
关键字并在后面跟上约束的名字。建表语句中的
comment
关键字用来给列和表添加注释,增强代码的可读性和可维护性。在创建表的时候,可以自行选择底层的存储引擎。MySQL 支持多种存储引擎,可以通过
show engines
命令进行查看。MySQL 5.5 以后的版本默认使用的存储引擎是 InnoDB,它是我们推荐大家使用的存储引擎(因为更适合当下互联网应用对高并发、性能以及事务支持等方面的需求),为了 SQL 语句的向下兼容性,我们可以在建表语句结束处右圆括号的后面通过engine=innodb
来指定使用 InnoDB 存储引擎。说明:上面的 \G 是为了换一种输出方式,在命令行客户端中,如果表的字段很多一行显示不完,就会导致输出的内容看起来非常不舒服,使用 \G 可以将记录的每个列以独占整行的的方式输出,这种输出方式在命令行客户端中看起来会舒服很多。
下面的表格对MySQL几种常用的数据引擎进行了简单的对比。
特性 InnoDB MRG_MYISAM MEMORY MyISAM 存储限制 有 没有 有 有 事务 支持 锁机制 行锁 表锁 表锁 表锁 B树索引 支持 支持 支持 支持 哈希索引 支持 全文检索 支持(5.6+) 支持 集群索引 支持 数据缓存 支持 支持 索引缓存 支持 支持 支持 支持 数据可压缩 支持 内存使用 高 低 中 低 存储空间使用 高 低 低 批量插入性能 低 高 高 高 是否支持外键 支持 通过上面的比较我们可以了解到,InnoDB 是唯一能够支持外键、事务以及行锁的存储引擎,所以我们之前说它更适合互联网应用,而且在较新版本的 MySQL 中,它也是默认使用的存储引擎。
在定义表结构为每个字段选择数据类型时,如果不清楚哪个数据类型更合适,可以通过 MySQL 的帮助系统来了解每种数据类型的特性、数据的长度和精度等相关信息。
说明:在 MySQLWorkbench 中,不能使用
?
获取帮助,要使用对应的命令help
。获取 varchar 类型的帮助:
执行结果:
在数据类型的选择上,保存字符串数据通常都使用 VARCHAR 和 CHAR 两种类型,前者通常称为变长字符串,而后者通常称为定长字符串;对于 InnoDB 存储引擎,行存储格式没有区分固定长度和可变长度列,因此 VARCHAR 类型和 CHAR 类型没有本质区别,后者不一定比前者性能更好。如果要保存的很大字符串,可以使用 TEXT 类型;如果要保存很大的字节串,可以使用 BLOB(二进制大对象)类型。在 MySQL 中,TEXT 和 BLOB又分别包括 TEXT、MEDIUMTEXT、LONGTEXT 和 BLOB、MEDIUMBLOB、LONGBLOB 三种不同的类型,它们主要的区别在于存储数据的最大大小不同。保存浮点数可以用 FLOAT 或 DOUBLE 类型,FLOAT 已经不推荐使用了,而且在 MySQL 后续的版本中可能会被移除掉。而保存定点数应该使用 DECIMAL 类型,它可以指定小数点前后有效数字的位数。如果要保存时间日期,DATETIME 类型优于 TIMESTAMP 类型,因为前者能表示的时间日期范围更大,后者底层其实就是一个整数,记录了指定的日期时间和 1970-01-01 00:00:00 相差多少个毫秒,该类型在 2038-01-19 03:14:07 之后就会溢出。
对于自增字段 AUTO_INCREMENT,如果使用 MySQL 5.x 版本要注意自增字段的回溯问题,当然这个问题在 MySQL 8.x 中已经得到了很好的解决,当然,MySQL 8.x 还有很多其他的好处,不管是功能还是性能上都有很多的优化和调整,因此强烈推荐大家使用 MySQL 8.x 版本。对于高并发访问数据库的场景,AUTO_INCREMENT 不仅存在性能上的问题,还可能在多机结构上产生重复的 ID 值,在这种场景下,使用分布式 ID 生成算法(SnowFlake、TinyID等)才是最好的选择,有兴趣的读者可以自行研究。
删除表和修改表
下面以学生表为例,为大家说明如何删除表和修改表。删除表可以使用drop table
,代码如下所示。
或
需要注意的是,如果学生表已经录入了数据而且该数据被其他表引用了,那么就不能删除学生表,否则上面的操作会报错。在下一课中,我们会讲解如何向表中插入数据,到时候大家可以试一试,能否顺利删除学生表。
如果要修改学生表,可以使用alter table
,具体可以分为以下几种情况:
修改表,添加一个新列,例如给学生表添加一个联系电话的列。
注意:如果新增列的时候指定了非空约束(
not null
),那么学生表不能够有数据,否则原来的数据增加了 stu_tel 列之后是没有数据的,这就违反了非空约束的要求;当然,我们在添加列的时候也可以使用默认值约束来解决这个问题。
修改表,删除指定的列,例如将上面添加的联系电话列删除掉。
修改表,修改列的数据类型,例如将学生表的 stu_sex 修改为字符。
修改表,修改列的命名,例如将学生表的 stu_sex 修改为 stu_gender。
修改表,删除约束条件,例如删除学生表的 col_id 列的外键约束。
修改表,添加约束条件,例如给学生表的 col_id 列加上外键约束。
或
说明:在添加外键约束时,还可以通过
on update
和on delete
来指定在被引用的表发生删除和更新操作时,应该进行何种处理,二者的默认值都是restrict
,表示如果存在外键约束,则不允许更新和删除被引用的数据。除了restrict
之外,这里可能的取值还有cascade
(级联操作)和set null
(设置为空),有兴趣的读者可以自行研究。
修改表的名字,例如将学生表的名字修改为 tb_stu_info。
提示:一般情况下,请不要轻易修改数据库或表的名字。