数据定义语言(DDL)
事项
1、反引号 `` 中的内容可以规避关键字
2、命名规范:所有标识符全部小写,单词之间使用下划线 _ 连接
创建数据库
CREATE DATABASE IF NOT EXISTS `数据库名` CHARACTER SET `字符集` COLLATE `校对规则`;
1、IF NOT EXISTS(建议):若存在该语句,且已存在重名数据库,则不会创建数据库;若不存在该语句,且已存在重名数据库,则报错
2、CHARACTER SET:指定数据库所采用的字符集,若不指定,默认 utf8
3、COLLATE:指定数据库字符集的校对规则,常用:utf8_general_ci(不区分大小写)、utf8_bin(区分大小写),若不指定,默认 utf8_general_ci
4、注意:DATABASE 不能改名,一些可视化工具可以改名,实际是建新库,把所有表复制到新库,再删旧库完成的
删除数据库
1、方式一:删除指定的数据库
DROP DATABASE 数据库名;
2、方式二:删除指定的数据库(建议)
DROP DATABASE IF EXISTS 数据库名;
查看数据库
1、查看当前数据库服务器中的所有数据库
SHOW DATABASES;
2、查看当前正在使用的数据库
SELECT DATABASE();
3、查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
SHOW CREATE DATABASE 数据库名\G
MySQL 系统自带数据库
1、information_schema:主要保存 MySQL 数据库服务器的系统信息,如:数据库的名称、数据表的名称、字段名称、存取权限、数据文件所在的文件夹和系统使用的文件夹,等等
2、performance_schema:监控 MySQL 的各类性能指标
3、sys:以一种更容易被理解的方式,展示 MySQL 数据库服务器的各类性能指标,帮助系统管理员和开发人员监控 MySQL 的技术性能
4、mysql:保存 MySQL 数据库服务器运行时需要的系统信息,如:数据文件夹、当前使用的字符集、约束检查信息,等等
备份数据库
1、不需要登录 MySQL,在命令提示符输入
2、备份多个数据库:mysqldump -u (用户名) -p(密码) -B (数据库1) (数据库2) > (保存文件路径):\……\(文件名).sql
3、-B 表示所备份的全为数据库
4、备份指定数据库的指定表:mysqldump -u (用户名) -p(密码) (数据库1) (表1) (表2) > (保存文件路径):\……\(文件名).sql
恢复数据库
1、从命令提示符登录 MySQL
2、再输入 source (备份文件路径):\……\(文件名).sql
3、第二种方式:将 .sql 中的所有语句放入查询编辑器并执行
使用 / 切换数据库
USE 数据库名;
1、要操作表格和数据之前必须先说明是对哪个数据库进行操作,否则就要对所有对象加上数据库名
2、如果没有使用 use 语句,后面针对数据库的操作也没有加“数据名”的限定,那么会报错“ERROR 1046(3D000): No database selected”(没有选择数据库)
3、使用完 use 语句后,如果接下来的 SQL 都是针对一个数据库操作的,那就不用重复 use,如果要针对另一个数据库操作,那么要重新 use
查看某个库的所有表格
#要求前面有USE语句
SHOW tables;
show tables from 数据库名;
修改数据库
1、更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集;
创建表
1、方式一
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
)CHARACTER SET 字符集 COLLATE 校对规则 ENGINE 引擎;
(1)必须具备:CREATE TABLE 权限,存储空间
(2)加上 IF NOT EXISTS,则表示:如果当前数据库中不存在要创建的数据表,则创建数据表;如果当前数据库中已经存在要创建的数据表,则忽略建表语句,不再创建数据表
(3)必须指定:表名,列名(字段名),数据类型,长度
(4)可选指定:约束条件,默认值
(5)数据库的新建表若不指定字符集、校对规则,默认跟随表所在数据库
2、方式二:将表 2 完整的字段结构和索引,复制到表 1 中
CREATE TABLE `表1` LIKE `表2`;
3、使用 AS subquery 选项,将创建表和插入数据结合起来
(1)只将表 2 的字段结构复制到表 1 中,但不会复制表 2 中的索引到表 1 中,这种方式比较灵活,可以同时指定要复制哪些字段,并且自身复制表也可以根据需要增加字段结构
CREATE TABLE 表1 AS SELECT 字段1, 字段2 FROM 表2 LIMIT 0;
(2)拷贝表
CREATE TABLE 表1 AS SELECT * FROM 表2;
查看数据表结构
1、在 MySQL 中创建好数据表之后,可以查看数据表的结构
2、MySQL 支持使用 DESCRIBE / DESC 语句查看数据表结构,也支持使用 SHOW CREATE TABLE 语句查看数据表结构
DESC 表名;
SHOW CREATE TABLE 表名\G
(1)使用 SHOW CREATE TABLE 语句不仅可以查看表创建时的详细语句,还可以查看存储引擎和字符编码
修改表
1、修改数据库中已经存在的数据表的结构
2、重命名表
(1)必须是对象的拥有者
(2)方式一:使用 RENAME
RENAME TABLE 旧表名 TO 新表名;
(3)方式二:TO 是可选参数,不影响语句执行
ALTER TABLE 旧表名 RENAME [TO] 新表名;
3、修改表的校对规则
ALTER TABLE `表名` COLLATE `校对规则`;
4、修改表的引擎
ALTER TABLE `表名` ENGINE=引擎;
5、修改表的字符集
ALTER TABLE `表名` CHARACTER SET `字符集`;
删除表
1、在 MySQL 中,当一张数据表没有与其他任何数据表形成关联关系时,可以将当前数据表直接删除
(1)数据和结构都被删除
(2)所有正在运行的相关事务被提交
(3)所有相关索引被删除2
2、格式
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
(1)IF EXISTS:如果当前数据库中存在相应的数据表,则删除数据表;如果当前数据库中不存在相应的数据表,则忽略删除语句,不再执行删除数据表的操作
(2)DROP TABLE 语句不能回滚
清空表
TRUNCATE TABLE 表名;
1、删除表中所有的数据
2、释放表的存储空间
3、TRUNCATE 不能回滚,而 DELETE 删除数据,可以回滚
4、开发规范
(1)TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少,但 TRUNCATE 无事务且不触发 TRIGGER,有可能造成事故,故不建议在开发代码中使用此语句
(2)说明:TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同
5、DELETE、TRUNCATE、DROP
(1)DELETE 是 DML 语句,可以选择删除部分数据,也可以选择删除全部数据;删除的数据可以回滚;不会释放空间(逻辑删除);进入事务、触发 trigger
(2)DROP 是DDL 语句,删除表结构和所有数据,同时删除表结构所依赖的约束、触发器和索引;删除的数据无法回滚;会释放空间
(3)TRUNCATE 是 DDL 语句,删除表的所有数据,不能删除表的部分数据,也不能删除表的结构;删除的数据无法回滚;会释放空间;不进入事务、不触发 trigger
(4)执行速度:DROP > TRUNCATE > DELETE
(5)一般使用场景:如果一张表确定不再使用,使用 DROP;如果只是删表中的全部数据,使用 TRUNCATE;如果删除的是表中的部分数据,使用 DELETE
列
1、增加列
ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 完整性约束条件 [FIRST|AFTER 字段名];
(1)可选参数:完整性约束条件、FIRST、AFTER 字段名
(2)FIRST:将新增字段设为表的第一个字段
(3)AFTER `列名`:将新增字段设在指定列名之后
(4)没有指定位置,默认为表的最后一个字段
2、修改列
ALTER TABLE 表名 MODIFY [COLUMN] 字段名1 字段类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称] [DEFAULT 默认值] [FIRST|AFTER 字段名2];
(1)可修改列的数据类型,长度、默认值和位置
(2)MODIFY:只能修改数据类型,不能修改列名
(3)FIRST、AFTER 列名,可以修改列的位置
3、重命名列
ALTER TABLE 表名 CHANGE [COLUMN] 列名 新列名 新数据类型;
(1)CHANGE:可以同时修改数据类型、列名,若只修改数据类型,两个列名必须相同
4、删除列
ALTER TABLE 表名 DROP [COLUMN] 字段名;
5、修改 / 删除列的默认值
ALTER TABLE 表名 ALTER [COLUMN] <列名> [SET DEFAULT <默认值> | DROP DEFAULT];
Java 开发手册 MySQL 字段命名
1、表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间只出现数字
(1)数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑
2、禁用保留字,如 desc、range、match、delayed 等,请参考 MySQL 官方保留字
3、表必备三字段:id, gmt_create, gmt_modified
(1)id 必为主键,类型为 BIGINT UNSIGNED、单表时自增、步长为 1
(2)gmt_create,gmt_modified 的类型均为 DATETIME 类型,前者现在时表示主动式创建,后者过去分词表示被动式更新
4、表的命名最好是遵循:业务名称_表的作用
5、库名与应用名称尽量一致
6、合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度
清空表、删除表等操作需谨慎
1、表删除操作将把表的定义和表中的数据一起删除,并且 MySQL 在执行删除操作时,不会有任何的确认信息提示,因此执行删除操时应当慎重
2、在删除表前,最好对表中的数据进行备份 ,这样当操作失误时可以对数据进行恢复,以免造成无法挽回的后果
3、同样,在使用 ALTER TABLE 进行表的基本修改操作时,在执行操作过程之前,也应该确保对数据进行完整备份,因为数据库的改变是无法撤销的,如果删除了一个需要的列,该列下面的所有数据都将会丢失
MySQL 8 新特性:DDL 原子化
1、在 MySQL 8.0 中,InnoDB 表的 DDL 支持事务完整性,即 DDL 操作要么成功要么回滚
2、DDL 操作回滚日志写入到 data dictionary(数据字典表):mysql.innodb_ddl_log(该表是隐藏表,通过 show tables 无法看到),用于回滚操作
3、通过设置参数,可将 DDL 操作日志打印输出到 MySQL 错误日志中
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战