MySQL十： DDL (数据库/数据表的增、查、改、删)

DDL (数据库/数据表的增、查、改、删)

引入

存储数据时处理数据的第一步，只有正确地把数据存储起来，我们才能有效地处理和分析

存储数据过程

一、数据库

1. 创建数据库

   方式1：单纯创建数据库

   CREATE DATABASE  数据库名;
   

   方式2：创建数据库并指定字符集

   CREATE DATABASE 数据库名 CHARACTER SET 字符集;
   

   方式3：判断数据库是否已经存在，不存在则创建数据库（推荐使用）

   CREATE DATABASE IF NOT EXISTS 数据库名;
   

2. 查看数据库

   2.1 查看当前所有数据库

   SHOW DATABASES;
   

   2.2 使用/切换数据库

   USE 数据库名;
   

   2.3 查看当前正在使用的数据库

   SELECT DATABASE();
   

   2.4 查看数据库的创建信息

   # 方式一
   SHOW CREATE DATABASE 数据库名;
   # 方式二
   SHOW CREATE DATABASE 数据库名\G;
   

3. 修改数据库

   更改数据库字符集

   ALTER DATABASE 数据库名 CHARACTER SET 字符集;
   

   **注：**数据库不能改名，一些可视化工具虽然可以改名，其实质时创建新库，把所有表复制到新库，在删除旧库完成的。

4. 删除数据库

   # 方式1
   DROP DATEBASE 数据库名;
   # 方式2 (推荐使用)
   DROP DATABASE IF EXISTS 数据库名;
   

二、数据表

1. 创建数据表

   方式一

   # 创建格式
   CREATE TABLE IF NOT EXISTS 表名(
       字段名1 数据类型1(长度),
       字段名2 数据类型2(长度),
       . . .     
   )
   # 创建举例
   CREATE TABLE IF NOT EXISTS myemp1(
   id INT,
   emp_name VARCHAR(15),
   hire_date DATE
   );
   

   方式二

   在已有表的基础上创建

   # 创建格式
   CREATE TABLE myemp2
   AS
   根据现有表的查询语句所生成的虚拟表
   # 创建举例
   CREATE TABLE myemp2
   AS
   SELECT employee_id emp_id,last_name emp_n,salary emp_sa
   FROM employees
   WHERE department_id = 80;
   

   说明1：查询语句中字段的别名，作为新创建的表的字段名称

   说明2：此时的查询语句可以结构比较丰富，使用前面章节讲过的各种SELECT 语句

   扩展

   复制employees表，包括表数据

   CREATE TABLE employees_copy
   AS
   SELECT *
   FROM employees;
   

   复制employees表，不包括表数据

   # WHERE筛选语句是个不成立的等式，所以不会有任何一行数据符合要求，达到复制表但不包含数据的目的。
   CREATE TABLEL employees_copy1
   AS
   SELECT *
   FROM employees
   WHERE 1 = 2;
   

2. 查看数据表结构

   方式一

   DESC 数据表名;
   

   方式二

   DESCRIBE 数据表名;
   

   方式三

   SHOW CREATE TABLE 数据表名\G;
   

3. 修改数据表

   3.1 添加一个字段

   # 默认添加到表中的最后一个字段
   ALTER TABLE myemp1
   ADD salary DOUBLE(10,2);
   # 添加到指定字段的后面
   ALTER TABLE myemp1
   ADD email VARCHAR(45) AFTER emp_name;
   

   3.2 修改一个字段：数据类型、长度、默认值

   ALTER TABLE myemp1
   MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';
   

   3.3 重命名一个字段（可同时修改字段的数据类型等）

   ALTER TABLE myemp1
   CHANGE email my_email VARCHAR(50);
   

   3.4 删除一个字段

   ALTER TABLE myemp1
   DROP COLUMN my_email;
   

4. 重命名表

   RENAME TABLE myemp1
   TO myemp10;
   

5. 删除表

   条件：该数据表没有与其它然和数据表形成关联关系

   # 可同时删除多张表
   DROP TABLE IF EXISTS myemp2;
   

6. 清空表

   清空表中的所有数据，但是表结构保留

   # 方式一
   TRUNCATE TABLE employees_copy;
   # 方式二
   DELETE FROM employees_copy1;
   

三、关于DDL、DCL、DML的一些说明

1. DCL中的COMMIT 和 ROLLBACK

   COMMIT：提交数据。一旦执行，则数据就被永久保存在数据库中，不可回滚。

   ROLLBACK：回滚数据。一旦执行，则可以实现数据的回滚，回滚到最近一次的COMMIT之后。

2. DDL 和 DML的回滚问题

   DDL：该类操作一旦执行，就不可回滚（不会受SET autocommit = FALSE 的影响）。

   DML：在默认情况下，一旦执行，则不可回滚。

   ​ 若在执行前，加入了 SET autocommit = FALSE; 则执行DML操作就可以实现回滚。

3. 对比 TRUNCATE 和 DELETE FROM

   相同点：都可以实现对表中所有数据的删除，同时保留表结构

   不同点：TRUNCATE 一旦执行，数据不可以回滚；

   ​ DELETE FROM：一旦执行，数据可以实现回滚

4. 测试代码

   # 第1步，提交前面添加表等数据
   COMMIT;
   # 第2步，查看提交后表中的数据
   SELECT *
   FROM myemp1；
   # 第3步，修改autocommit的默认值
   SET autocommit  = FALSE;
   # 第4步，删除表中数据
   DELETE FROM myemp1;
   # 第5步，查看执行DELETE 后的表中数据
   SELETE *
   FROM myemp1；
   # 第6步，回滚数据
   ROLLBACK;
   # 第7步，查看执行回滚操作后的表中数据
   SELECT *
   FROM myemp1；
   

四、阿里《Java开发手册》关于MySQL的一些规范

1. TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但TRUNCATE无事务且不触发TRIGGER，有可能造成事故，故不建议在开发代码中使用。

2. 表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间出现数字。（数据库字段名的修改代价很大，故字段名要慎重考虑）

3. 禁用保留字

4. 数据库的名称尽量与应用名称一致

5. 数据表的命名最好是“业务名称_表的作用”

6. 表必备三字段id，gmt_create，gmt_modified

   id：必为主键，类型为BIGINT UNSIGNED，自增，步长为1

   gmt_create：类型为DATETIME，表示主动式创建

   gmt_modified：类型为DATETIME，表示主被动式更新

7. 合适的字符存储长度，不但节约数据库表空间，节约索引存储，更重要的可提升检索速率。