DDL (数据库/数据表的增、查、改、删)
引入
存储数据时处理数据的第一步,只有正确地把数据存储起来,我们才能有效地处理和分析
存储数据过程
一、数据库
-
创建数据库
方式1:单纯创建数据库
CREATE DATABASE 数据库名;
方式2:创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
方式3:判断数据库是否已经存在,不存在则创建数据库(推荐使用)
CREATE DATABASE IF NOT EXISTS 数据库名;
-
查看数据库
2.1 查看当前所有数据库
SHOW DATABASES;
2.2 使用/切换数据库
USE 数据库名;
2.3 查看当前正在使用的数据库
SELECT DATABASE();
2.4 查看数据库的创建信息
# 方式一 SHOW CREATE DATABASE 数据库名; # 方式二 SHOW CREATE DATABASE 数据库名\G;
-
修改数据库
更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集;
**注:**数据库不能改名,一些可视化工具虽然可以改名,其实质时创建新库,把所有表复制到新库,在删除旧库完成的。
-
删除数据库
# 方式1 DROP DATEBASE 数据库名; # 方式2 (推荐使用) DROP DATABASE IF EXISTS 数据库名;
二、数据表
-
创建数据表
方式一
# 创建格式 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;
-
查看数据表结构
方式一
DESC 数据表名;
方式二
DESCRIBE 数据表名;
方式三
SHOW CREATE TABLE 数据表名\G;
-
修改数据表
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;
-
重命名表
RENAME TABLE myemp1 TO myemp10;
-
删除表
条件:该数据表没有与其它然和数据表形成关联关系
# 可同时删除多张表 DROP TABLE IF EXISTS myemp2;
-
清空表
清空表中的所有数据,但是表结构保留
# 方式一 TRUNCATE TABLE employees_copy; # 方式二 DELETE FROM employees_copy1;
三、关于DDL、DCL、DML的一些说明
-
DCL中的COMMIT 和 ROLLBACK
COMMIT:提交数据。一旦执行,则数据就被永久保存在数据库中,不可回滚。
ROLLBACK:回滚数据。一旦执行,则可以实现数据的回滚,回滚到最近一次的COMMIT之后。
-
DDL 和 DML的回滚问题
DDL:该类操作一旦执行,就不可回滚(不会受SET autocommit = FALSE 的影响)。
DML:在默认情况下,一旦执行,则不可回滚。
若在执行前,加入了 SET autocommit = FALSE; 则执行DML操作就可以实现回滚。
-
对比 TRUNCATE 和 DELETE FROM
相同点:都可以实现对表中所有数据的删除,同时保留表结构
不同点:TRUNCATE 一旦执行,数据不可以回滚;
DELETE FROM:一旦执行,数据可以实现回滚
-
测试代码
# 第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的一些规范
-
TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少,但TRUNCATE无事务且不触发TRIGGER,有可能造成事故,故不建议在开发代码中使用。
-
表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间出现数字。(数据库字段名的修改代价很大,故字段名要慎重考虑)
-
禁用保留字
-
数据库的名称尽量与应用名称一致
-
数据表的命名最好是“业务名称_表的作用”
-
表必备三字段id,gmt_create,gmt_modified
id:必为主键,类型为BIGINT UNSIGNED,自增,步长为1
gmt_create:类型为DATETIME,表示主动式创建
gmt_modified:类型为DATETIME,表示主被动式更新
-
合适的字符存储长度,不但节约数据库表空间,节约索引存储,更重要的可提升检索速率。