数据定义语言(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 TABLE1 AS SELECT 字段1, 字段2 FROM2 LIMIT 0;

(2)拷贝表

CREATE TABLE1 AS SELECT * FROM2;

 

查看数据表结构

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 错误日志中

posted @   半条咸鱼  阅读(230)  评论(0编辑  收藏  举报
(评论功能已被禁用)
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示