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;