【MySQL数据库】10 创建和管理表

---

第10章 创建和管理表

创作日期：2021-11-25

---

1.基础知识

1.1 一条数据存储的过程

        存储数据是处理数据的第一步。只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只能说一团乱码，无从下手。

        那么，怎样才能把用户各种经营相关的，纷繁复杂的数据，有序，高校的存储起来呢？在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库，确认字段，创建数据表，插入数据。

我们为何要先创建一个数据库，而不是直接创建数据表呢？

        因为从系统架构的层次上看，MySQL 数据库系统从大到小依次是 数据库服务器，数据库，数据表，数据表的行与列。MySQL 数据库服务器之前已经安装。所以，我们就从创建数据库开始。

1.2 标识符命名规则

• 数据库名，表名不得超过30个字符，变量名限制为29个
• 必须只能包含 A-Z，a-z，0-9，_共63个字符
• 数据库名，表名，字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
• 必须保证你的字段没有和保留字，数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
• 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

1.3 MySQL中的数据类型

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

---

 2.创建和管理数据库（CREATE DATABASE）

2.1 创建数据库

• 方式1：创建数据库

CREATE DATABASE 数据库名;

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

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

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

CREATE DATABASE IF NOT EXISTS 数据库名;

        如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

        注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。

2.2 使用数据库

• 查看当前所有的数据库

SHOW DATABASES;

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

SELECT DATABASE();

• 查看指定库下所有的表

SHOW TABLES FROM atguigudb;

• 查看数据库的创建信息

SHOW CREATE DATABASE atguigudb;

• 使用/切换数据库

USE atguigudb;

        注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名”。

2.3 修改数据库

• 更改数据库字符集

alter database 数据库名 CHARACTER SET 字符集类型;

2.4 删除数据库

• 方式1：删除指定的数据库

DROP DATABASE 数据库名;

• 方式2：删除指定的数据库（推荐） 

DROP DATABASE IF EXISTS 数据库名;

---

3.创建表（CREATE TABLE）

3.1 创建方式1

• 必须具备：
  • CREATE TABLE权限
  • 存储空间
• 语法格式：

CREATE TABLE [IF NOT EXISTS] 表名(
    字段1 数据类型 [约束条件] [默认值],
    字段2 数据类型 [约束条件] [默认值],
    字段3 数据类型 [约束条件] [默认值],
...
    [表的约束条件]
);

        加上了IF NOT EXISTS关键字表示：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

• 必须指定：

  • 表名

  • 列名（或字段名），数据类型，长度

• 可选指定：

  • 约束条件

  • 默认值

CREATE TABLE IF NOT EXISTS employees(
    employee_id INT,
    last_name VARCHAR(15),
    hire_date DATE
);

3.2 创建方式2 

• 基于现有的表创建新的表，实现表的复制，导入其数据

USE atguigudb;
CREATE TABLE employees1
AS
SELECT employee_id,last_name,hire_date
FROM employees;

•  基于现有的表创建新的表，实现表的复制，不导入其数据
  • 设置一个无法到达的条件

create table employees01
as
select employee_id,last_name,hire_date
from employees
where 1 = 2;

3.3 查看数据结构

• 查看表结构

• 查看创建表的语句结构

---

4.修改表（ALTER TABLE）

4.1 追加一个字段（ADD）

ALTER TABLE employees
ADD salary DOUBLE(10,2);

•  添加字段时默认添加到表的最后一个字段的后面

• FIRST：添加字段到表的开头位置

ALTER TABLE employees
ADD department_id int first;

• AFTER：添加字段到表的某一字段后面

ALTER TABLE employees
ADD first_name VARCHAR(15) AFTER employee_id;

4.2 修改一个字段（MODIFY）

• 修改长度

ALTER TABLE employees
MODIFY first_name VARCHAR(25);

• DEFAULT：添加默认值

ALTER TABLE employees
MODIFY first_name VARCHAR(15) DEFAULT "NAME";

4.3 重命名一个字段（CHANGE）

ALTER TABLE employees
CHANGE salary M_Salary DOUBLE(10,2);

4.4 删除一个字段（DROP）

ALTER TABLE employees
DROP column department_id;

---

5.重命名表

• 方式一：使用RENAME

​RENAME TABLE employees TO Emp;

• 方式二：

ALTER TABLE employees
RENAME TO Emp;

• 必须是对象的拥有者

---

6.删除表

• 在MySQL中，当一张数据表没有与其他任何数据表形成关联时，可以将当前数据表直接删除。
• 数据和结构都被删除
• 所有正在运行的相关事务被提交
• 语法格式：

DROP TABLE [IF EXISTS] 数据表1 [,数据表2,...,数据表n]

        IF EXISTS 的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。

DROP table if exists employees01;

• DROP TABLE语句不能回滚

---

7.清空表

•  TRUNCATE TABLE 语句：
  • 删除表中的所有数据
  • 释放表的存储空间
• 举例：

TRUNCATE TABLE employees;

• TRUNCAT 语句不能回滚，而使用 DELETE 语句删除数据，可以回滚
• 对比：
  • 相同点：都可以实现对表中所有数据的删除，同时保留表结构
  • 不同点：TRUNCAT  不支持回滚，DELETE 支持回滚

---

8.内容扩展

DCL 中 COMMIT 和 ROLLBACK

• COMMIT：提交数据。一旦执行 COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。
• ROLLBACK：回滚数据。一旦执行 ROLLBACK，则可以实现数据的回滚。回滚到最近一次 COMMIT 之后。

DDL 和 DML 的说明

• DDL的操作一旦执行，就不可以回滚。
• DML的操作默认情况，一旦执行，也是不可以回滚的。但是 ，如果在执行 DML 之前，执行了 SET autocommit = FALSE，则执行的 DML 操作就可以实现回滚了。

演示 DELETE FROM 操作

#设置 COMMIT
COMMIT;
#查看数据-有数据
SELECT * FROM employees;
#执行 SET 操作
SET autocommit = FALSE;
#执行 DELETE 操作删除指定表
DELETE FROM employees;
#查看数据-无数据
SELECT * FROM employees;
#回滚数据
ROLLBACK;
#查看数据-有数据
SELECT * FROM employees;

演示 TRUNCATE TABLE 操作

#设置 COMMIT
COMMIT;
#查看数据-有数据
SELECT * FROM employees;
#执行 SET 操作
SET autocommit = FALSE;
#执行 DELETE 操作删除指定表
TRUNCATE TABLE employees;
#查看数据-无数据
SELECT * FROM employees;
#回滚数据
ROLLBACK;
#查看数据-有数据
SELECT * FROM employees;

9.创建和管理表练习

9.1 练习一

• 创建数据库test01_office，指明字符集为utf8.并在此数据库下执行下述操作：

CREATE DATABASE test01_office CHARACTER SET 'UTF8';

#查看当前数据库信息,查看字符集
SHOW CREATE DATABASE test01_office;

• 创建表 dept01

# 创建表 dept01
#在当前数据库下执行sql操作
USE test01_office;
CREATE TABLE IF NOT EXISTS dept01(
	id INT(7),
	NAME VARCHAR(25)
);
#查看当前数据库的表
SHOW TABLES FROM test01_office;
#查看表结构
DESC dept01;

• 将表departments中的数据插入新表 dept02中去 

CREATE TABLE dept02
AS
SELECT *
FROM atguigudb.departments;
#查看表是否插入复制成功
select * from dept02;

• 创建表 emp01

#创建表 emp01
create table IF NOT EXISTS emp01(
	id int(7),
	first_name varchar(25),
	last_name varchar(25),
	dept_id int(7)
);
#查看表是否创建成功
DESC dept01;

• 将列last_name的长度增加到50

ALTER TABLE emp01
modify last_name VARCHAR(50);
#查看表结构
DESC emp01;

• 根据表 employees 创建 emp02

#根据表 employees 创建 emp02
CREATE TABLE emp02
AS
SELECT *
FROM atguigudb.employees;
#查看表是否插入复制成功
SELECT * FROM emp02;

• 删除表 emp01

DROP TABLE IF EXISTS emp01;

• 将表 emp02 重命名为 emp01

#将表 emp02 重命名为 emp01
ALTER TABLE emp02
RENAME TO emp01;
#查看表结构
DESC emp01;

• 在表dept02和emp01中添加新列test_column，并检查所有的操作

ALTER TABLE dept02
ADD test_column VARCHAR(10);
DESC dept02;

ALTER TABLE emp01
ADD test_column VARCHAR(10);
DESC emp01;

• 直接删除表 emp01 中的列 department_id

#直接删除表 emp01 中的列 department_id
ALTER TABLE emp01
DROP COLUMN department_id;

9.2 练习二

• 创建数据库 test02_market

#创建数据库 test02_market
CREATE DATABASE test02_market;
SHOW CREATE DATABASE test02_market;

• 创建数据表 customers

#创建数据表 customers
CREATE TABLE IF NOT EXISTS customers(
	c_num INT,
	c_name VARCHAR(50),
	c_contact VARCHAR(50),
	c_city VARCHAR(50),
	c_birth DATE
);
#查看表是否创建成功
DESC customers;

• 将 c_contact 字段移动到 c_birth 字段后面

#将 c_contact 字段移动到 c_birth 字段后面
ALTER TABLE customers
MODIFY c_contact VARCHAR(50) AFTER c_birth;
#查看表结构
DESC customers;

• 将 c_name 字段数据类型改为 varchar(70)

ALTER TABLE customers
MODIFY c_name VARCHAR(70);
#查看表结构
DESC customers;

• 将c_contact字段改名为c_phone 

#将 c_contact 字段改名为c_phone 
ALTER TABLE customers
CHANGE c_contact c_phone VARCHAR(50);
#查看表结构
DESC customers;

• 增加c_gender字段到c_name后面，数据类型为char(1)

#增加c_gender字段到c_name后面，数据类型为char(1)
ALTER TABLE customers
ADD c_gender CHAR(1) AFTER c_name;
#查看表结构
DESC customers;

• 将表名改为customers_info

#将表名改为 customers_info
RENAME TABLE customers TO customers_info;
#显示数据库下属表名称
SHOW TABLES;

---

上一节：【MySQL数据库】09 子查询

下一节：【MySQL数据库】11 数据处理之增删改