5 DDL

5.1 创建表

5.1.1 创建的规则

• 数据库名和表名不能超过30个字符，表名不能超过29个字符
• 只能包含a-z，A-z，0-9，_共63个字符
• 同一个MySQL软件下，数据库不能重名，同一个库中，表不能重名，同一个表下，字段不能重名
• 表名、字段等不能与预留关键字重名，必要时使用着重号“`”
• 保持字段名和类型的一致性（在查询时，多个表关联时，字段类型需要一致）

5.1.2 如何创建数据库

• 方式一

  CREATE DATABASE mysql
  #如果创建子数据库时没有声明字符集，则默认使用数据库的字符集（UTF8）
  

• 方式二

  CREATE DATABASE mysql CHARACTER SET'GBK'
  #显式的指明了创建数据库的字符集
  

• 方式三（推荐）

  CREATE DATABASE IF NOT EXOSTS mysql CHARACTER SET'UTF8'
  #如果创建的数据库已经存在，则创建不成功。不会报错
  

5.1.3 管理数据库

#查看当前连接中的数据库有哪些
SHOW DATEBASES
#切换数据库
USE mytese2
#切换当前数据库中的表
SHOW TABLES
#查看当前使用的数据库
SELECT DATEBASE()
#查看指定数据库的表
SELECT TABLES FROM XXX

5.1.4 修改数据库

#查看数据库的字符集
SHOW CREATE DATEBASE XXX
#修改数据库的字符集
ALTER DATEBASE XXX CHARACTER SET "UTF8"
#数据库是不能改名的，可视化工具的改名是重新创建数据库

5.1.5删除数据库

#方式一
DROP DATABASE XXX
#方式二（推荐）
DROP DATEBASE IF EXIST XXX
#不要删啊！不要跑路啊！

5.2 创建表

5.2.1 数据类型

类型	类型举例
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值：GEOMETRY、POINT、LINESTRING、POLYGON； 集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

其中，常用的几类类型介绍如下：

数据类型	描述
INT	从-231到231-1的整型数据。存储大小为 4个字节
CHAR(size)	定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size)	可变长字符数据，根据字符串实际长度保存，必须指定长度
FLOAT(M,D)	单精度，占用4个字节，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30，默认M+D<=6
DOUBLE(M,D)	双精度，占用8个字节，D<=M<=255,0<=D<=30，默认M+D<=15
DECIMAL(M,D)	高精度小数，占用M+2个字节，D<=M<=65，0<=D<=30，最大取值范围与DOUBLE相同。
DATE	日期型数据，格式'YYYY-MM-DD'
BLOB	二进制形式的长文本数据，最大可达4G
TEXT	长文本数据，最大可达4G

5.2.2 表的创建

#方式一
CREATE TABLE mytal
#方式二(推荐)
CREATE TABLE IF NULL EXISTS mytal(id INT,emp_name VARCHAR(15),hire_date DATE);
#VARCHAR() 必须指明长度
#方式三——基于现有的表创建表,同时可以导入数据 
CREATE TABLE mytal
AS
SELECT *
FROM employees
#查询表结构
#方法一
DESC mytal;
#方法二
SHOW CREATE TABLE mytal
#如果创表时没有指明字符集，则默认使用库的字符集

• 表的复制

  #包含数据
  CREATE TABLE mytal
  AS
  SELECT*
  FROM employees
  #只有表的框架
  CREATE TABLE mytal
  AS
  SELECT*
  FROM employees
  WHERE 1<>1;
  

5.2.3 修改表

5.2.3.1 添加字段

ALTER TABLE mytal
ADD salary DOUBLE(10,2)#小数点后两位

ALTER TABLE mytal
ADD salary DOUBLE(10,2) FIRST;

ALTER TABLE mytal
ADD salary DOUBLE(10,2) AFTER emp_name;

5.2.3.2 修改字段

修改数据类型、长度、默认值

ALTER TABLE mytal
MODIFY emp_name VARCHAR(25)#将emp_name的长度变为25

ALTER TABLE mytal
MODIFY emp_name VARCHAR(25) DEFAULT'AAA'#默认值为aaa

5.2.3.3 重命名字段

ALTER TABLE mytal
change salary monthly_salary DOUBLE(10,2)
#也可以在改名的同时修改长度
ALTER TABLE mytal
change salary monthly_salary DOUBLE(10,3)

5.2.3.4 删除字段

ALTER TABLE mytal
DROP COLUMN e-mail
#删除e-mail

5.2.4 重命名表

#方式一（推荐）
RENAME TABLE mytal
TO mytal2
#方式二
ALTER TABLE mytal
RENAME TO mytal2

5.2.5 删除表

#不光将表的结构删除，同时将表数据也删除，然后施放表空间
DROP TABLE mytal
#建议使用
DROP TABLE IF EXISTS mytal

#注意表被删除无法撤销,所以…要不跑路？

5.2.6 清空表

• TRUNCATE 和DELETE FROM都可以对表数据进行删除，同时保留表数据
• 区别：
  • TRUNCATE ，不可以回滚
  • DELETE FROM可以选择删除哪些数据，并且可以实现回滚

#删除表中数据，表结构保留
TRUNCATE TABLE mytal

5.2.7 COMMIT&ROLLBACK

• 一旦提交数据，则数据被永久存在数据库，意味着不可以回滚
• 只能再执行增删改
• 一旦执行ROLLBACK，则回滚到最近一次COMMIT之后。

5.2.8 DDL和DML

• DDL的操作一旦执行，就不可以回滚
• DML的操作默认情况下，一旦执行，也是不可以回滚。但是在DML之前执行了SET autocommit = false则可以回滚
• 执行完DDL操作后一定会执行COMMIT，此操作不受SET autocommit = false影响。

COMMIT;#提交
SET autocommit = false;
DELETE FROM mytal
ROLLBACK;#回滚
SELECT* FROM mytal;#数据回来了

COMMIT;#提交
SET autocommit = false;
TRUNCATE TABLE mytal;
ROLLBACK;#回滚
SELECT* FROM mytal;#数据没有回来

在实际开发中使用DELETE FROM以避免事故。

• 在MySQL8.0中，由于DDL的原子化，在事务执行过程中报错而停止时，会对修改内容进行回滚。

5.3 课后练习

#1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作
CREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET 'UTF8';
#2. 创建表dept01
/*
字段 类型
id INT(7)
NAME VARCHAR(25)
*/
DROP TABLE dept01;
USE test01_office;
CREATE TABLE IF NOT EXISTS dept01(id INT,name VARCHAR(25));
#3. 将表departments中的数据插入新表dept02中
CREATE TABLE IF NOT EXISTS dept02
AS
SELECT *
FROM atguigudb.departments
#4. 创建表emp01
/*
字段 类型
id INT(7)
first_name VARCHAR (25)
last_name VARCHAR(25)
dept_id INT(7)
*/
CREATE TABLE IF NOT EXISTS emp01(id INT(7),first_name VARCHAR(25),last_name VARCHAR(25),dept_id INT(7))
#5. 将列last_name的长度增加到50
ALTER TABLE emp01
MODIFY last_name VARCHAR(50)
#6. 根据表employees创建emp02
CREATE TABLE emp02
AS
SELECT* FROM atguigudb.employees
#7. 删除表emp01
DROP TABLE emp01;
#8. 将表emp02重命名为emp01
RENAME TABLE emp02
TO emp01;
#9.在表dept02和emp01中添加新列test_column，并检查所作的操作
ALTER TABLE dept02
ADD test_column INT(4);
ALTER TABLE emp01
ADD test_column INT(4);
#10.直接删除表emp01中的列 department_id
ALTER TABLE emp01
DROP COLUMN department_id;


# 1、创建数据库 test02_market
CREATE DATABASE test02_market;
# 2、创建数据表 customers
CREATE TABLE customers(
c_num int,
c_name varchar(50),
c_contact varchar(50),
c_city varchar(50),
c_birth date)
# 3、将 c_contact 字段移动到 c_birth 字段后面
ALTER TABLE customers
MODIFY c_contact varchar(50) AFTER c_birth;
# 4、将 c_name 字段数据类型改为 varchar(70)
ALTER TABLE customers
MODIFY c_name varchar(70);
# 5、将c_contact字段改名为c_phone
ALTER TABLE customers
CHANGE c_contact c_phone varchar(50);
# 6、增加c_gender字段到c_name后面，数据类型为char(1)
ALTER TABLE customers
ADD c_gender char(1) AFTER c_name;
# 7、将表名改为customers_info
RENAME TABLE customers
TO customers_info
# 8、删除字段c_city
ALTER TABLE customers_info
DROP COLUMN c_city;