10. 创建和管理表

基础知识
- 标识符命名规则
- MySQL中的数据类型
创建和管理数据库
创建表
- 创建方式一
修改表
重命名表
删除表
清空表

基础知识

在MySQL中，一个完整的数据存储过程总共有4步，分别是创建数据库、确认字段、创建数据表、插入数据

标识符命名规则

数据库名、表名不得超过30个字符，变量名限制为29个
必须只能包含A-Z，a-z，0-9，_共63个字符
数据库名、表名、字段名等对象名中间不要包含空格
同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了
MySQL中的数据类型
| 类型 | 类型举例 | | —- | —- | | 整数类型 | TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT | | 浮点类型 | FLOAT、DOUBLE | | 定点数类型 | DECIMAL | | 位类型 | BIT | | 日期时间类型 | YEAR、TIME、DATE、DATETIME、TIMESTAMP | | 文本字符串类型 | CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT | | 枚举类型 | ENUM | | 二进制字符串类型 | BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB | | JSON类型 | JSON对象、JSON数组 | | 空间数据类型 | 单值：GEOMETRY、POINT、LINESTRING、POLYGON
集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGO、GEOMETRYCOLLECTION |

创建和管理数据库

创建数据库

//方式一：创建数据库
CREATE DATABASE 数据库名;
//方式二：创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
//方式三：判断数据库是否已经存在，不存在则创建数据库(推荐)
CREATE DATABASE IF NOT EXISTS 数据库名;

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

使用数据库

//查看当前所有的数据库
SHOW DATABASES;

//使用or切换数据库
USE 数据库名;

//查看当前数据库中保存的数据表
SHOW TABLES;

//查看指定数据库下保存的数据表
SHOW TABLES FROM 数据库名;

//查看当前正在使用的数据库
SELECT DATABASE();

//查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;

//查看表的创建信息、存储引擎、字符编码
SHOW CREATE TABLE 表名;

//查看表结构
DESC 表名;

修改数据库

//更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集; //比如：gbk、utf8等

删除数据库

//方式一：删除指定的数据库
DROP DATABASE 数据库名;

//方式二：删除指定的数据库(推荐)
DROP DATABASE IF EXISTS 数据库名;

创建表

创建方式一

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

必须指定：
- 表名
- 字段名，数据类型，长度
可选指定
- 约束条件
- 默认值 ```sql CREATE TABLE emp ( — int类型 emp_id INT, — 最多保存20个中英文字符 emp_name VARCHAR(20), — 总位数不超过15位 salary DOUBLE, — 日期类型 birthday DATE );

CREATE TABLE dept( — int类型，自增 deptno INT(2) AUTO_INCREMENT, dname VARCHAR(14), loc VARCHAR(13), — 主键 PRIMARY KEY (deptno) );

<a name="iK3qh"></a>
#### 创建方式二
基于现有的表，同时导入数据<br />说明：查询语句中字段的别名，可以作为新创建的表的字段的名称
```sql
CREATE TABLE emp1 AS SELECT * FROM employees;

//创建的emp2是空表
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2;

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

修改表

添加一个字段

ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [FIRST|AFTER 字段名];

//举例
ALTER TABLE dept ADD job_id VARCHAR(15);
ALTER TABLE myemp ADD phone_number VARCHAR(20) FIRST;
ALTER TABLE myemp ADD email VARCHAR(45) AFTER emp_name;

修改一个字段

可以修改字段的数据类型、长度、默认值和位置

ALTER TABLE 表名 MODIFY [COLUMN] 字段名 字段类型 [DEFAULT 默认值] [FIRST|AFTER 字段名];

//举例
ALTER TABLE myemp MODIFY emp_name VARCHAR(25);
ALTER TABLE myemp MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';

重命名一个字段

ALTER TABLE 表名 CHANGE [COLUMN] 字段名 新字段名 新数据类型;

//举例
ALTER TABLE myemp CHANGE email my_email VARCHAR(50);

删除一个字段

ALTER TABLE 表名 DROP [COLUMN] 字段名;

//举例
ALTER TABLE myemp DROP COLUMN my_email;

重命名表

//方式一
RENAME TABLE emp TO myemp;

//方式二
ALTER table emp RENAME [TO] myemp;

删除表

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

所有相关索引被删除

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, ..., 数据表n];
//注意：DROP TABLE语句不能回滚

清空表

删除表中的所有数据

释放表的存储空间

TRUNCATE TABLE detail_dept;
DELETE FROM detail_dept;

补充：
COMMIT 和 ROLLBACK

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

对比TRUNCATE TABLE 和 DELETE FROM

相同点：
- 都可以实现对表中所有数据的删除，同时保留表结构
不同点：
- TRUNCATE TABLE：清除表的全部数据，且数据不可以回滚
- DELETE FROM：可以清除表的全部数据，也可以清除表的部分数据，且数据可以回滚
- DDL的操作一旦执行，就不可以回滚，指令SET autocommit = FALSE;操作失效（因为在执行完DDL操作之后，一定会执行一次COMMIT，而此COMMIT操作不受set指令影响）
- DML的操作默认情况下也是不可以回滚的，但是如果在执行DML之前，执行了SET autocommit = FALSE;则执行的DML操作就可以实现回滚