SQL基础⑧ | 表格篇

0 序言

本文围绕MySQL中表的创建与管理展开，涵盖数据存储过程、命名规则、数据类型，以及数据库和表的创建、修改、删除等操作。

通过学习，你将掌握数据库与表的核心操作方法、规范及注意事项，能正确执行各类相关操作。

1 基础知识

1.1 一条数据存储的过程

1.1.1 存储的重要性

存储数据是处理数据的基础，只有正确存储，才能进行有效处理和分析，否则数据会杂乱无章。

1.1.2 数据存储的四步过程

在MySQL中，完整的数据存储过程包括：

• 创建数据库
• 确认字段
• 创建数据表
• 插入数据

1.1.3 先创建数据库的原因

从系统架构层次看，MySQL数据库系统从大到小依次为数据库服务器、数据库、数据表、数据表的行与列。由于MySQL服务器已安装，故从创建数据库开始。

1.2 标识符命名规则

1.2.1 长度限制

• 数据库名、表名不超过30个字符
• 变量名限制为29个字符

1.2.2 字符限制

只能包含A–Z、a–z、0–9、_共63个字符

1.2.3 空格限制

数据库名、表名、字段名等对象名中间不包含空格

1.2.4 唯一性限制

• 同一MySQL软件中，数据库不能同名
• 同一库中，表不能重名
• 同一表中，字段不能重名

1.2.5 与保留字冲突处理

字段名不能和保留字、数据库系统或常用方法冲突。若坚持使用，在SQL语句中用`（着重号）引起来

1.2.6 一致性要求

保持字段名和类型的一致性，如一个表中为整数类型，其他表中不应变为字符型

1.3 MySQL中的数据类型

1.3.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）

1.3.2 常用数据类型描述

数据类型	描述
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

2 创建和管理数据库

2.1 创建数据库

2.1.1 创建方式

• 方式1：创建数据库

CREATE DATABASE 数据库名;

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

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

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

CREATE DATABASE IF NOT EXISTS 数据库名;

例如说，

-- 创建并使用数据库
CREATE DATABASE IF NOT EXISTS demo_hr;
USE demo_hr;

2.1.2 注意事项

DATABASE不能直接改名，一些可视化工具的改名操作实际是建新库、复制表、删旧库

2.2 使用数据库

2.2.1 查看当前所有数据库

SHOW DATABASES;

能够直接看到当前的所有数据库。

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

SELECT DATABASE();

2.2.3 查看指定库下所有的表

SHOW TABLES FROM 数据库名;

2.2.4 查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;

或

SHOW CREATE DATABASE 数据库名\G

2.2.5 使用/切换数据库

USE 数据库名;

我们再对数据库进行一系列操作之前，都需要通过这个语句去选择你要的数据库，

然后才能执行相关操作。

2.2.6 注意事项

操作表格和数据前必须说明对哪个数据库操作，否则需对所有对象加数据库名.

2.3 修改数据库

2.3.1 更改数据库字符集

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

（例如：gbk、utf8等）

2.4 删除数据库

2.4.1 删除方式

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

DROP DATABASE 数据库名;

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

DROP DATABASE IF EXISTS 数据库名;

3 创建表

3.1 创建方式1

3.1.1 必备条件

• 具有CREATE TABLE权限
• 有存储空间

3.1.2 语法格式

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

例如，

-- 1. 部门表
CREATE TABLE IF NOT EXISTS departments (department_id INT PRIMARY KEY,department_name VARCHAR(50),location_id INT
);

3.1.3 关键字说明

• IF NOT EXISTS：若数据库中不存在该表则创建，存在则忽略

3.1.4 必须指定内容

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

3.1.5 可选指定内容

• 约束条件
• 默认值

3.1.6 创建表举例1

-- 创建表
CREATE TABLE emp (
-- int类型
emp_id INT,
-- 最多保存20个中英文字符
emp_name VARCHAR(20),
-- 总位数不超过15位
salary DOUBLE,
-- 日期类型
birthday DATE
);
DESC emp;

3.1.7 说明

MySQL中INT类型默认显示宽度为11，也可创建时指定，但MySQL 8.x版本不推荐为INT类型指定显示长度，未来可能会去掉该语法。

3.2 创建方式2

3.2.1 语法

使用AS subquery选项，将创建表和插入数据结合

CREATE TABLE table AS subquery [(column,column...)];

3.2.2 说明

• 指定的列和子查询中的列要一一对应
• 可通过列名和默认值定义列

3.2.3 举例

CREATE TABLE emp1 AS SELECT * FROM employees;
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表
CREATE TABLE dept80
AS 
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;
DESCRIBE dept80;

这个程序什么意思呢？
看下面图片分析。

3.3 查看数据表结构

3.3.1 查看方式

• 使用DESCRIBE/DESC语句
• 使用SHOW CREATE TABLE语句，语法：

SHOW CREATE TABLE 表名\G

3.3.2 说明

SHOW CREATE TABLE语句可查看表创建的详细语句、存储引擎和字符编码

4 修改表

4.1 追加一个列

4.1.1 语法格式

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

4.1.2 举例

ALTER TABLE dept80 ADD job_id varchar(15);

4.2 修改一个列

4.2.1 可修改内容

数据类型、长度、默认值和位置

4.2.2 语法格式

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;

4.2.3 举例

ALTER TABLE dept80
MODIFY last_name VARCHAR(30);
ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;

注意：

对默认值的修改只影响今后对表的修改，也可通过此方式修改列的约束

4.3 重命名一个列

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);

运行后把原字段名department_name改成dept_name并重置字段类型，明确指定新类型为 varchar(15)。

4.4 删除一个列

ALTER TABLE 表名 DROP 【COLUMN】字段名;

例如，

ALTER TABLE dept80
DROP COLUMN job_id;

这里column就是列的意思。

5 重命名表

5.1 方式一

RENAME TABLE emp TO myemp;

5.2 方式二

ALTER table dept RENAME [TO] detail_dept; -- [TO]可以省略

这里要注意：
必须是对象的拥有者！！！

6 删除表

当表未与其他表形成关联关系时可直接删除，删除后数据和结构都被删除，所有正在运行的相关事务被提交，所有相关索引被删除

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

IF EXISTS：若存在该表则删除，不存在则忽略

DROP TABLE dept80;

7 清空表

7.1 语法

TRUNCATE TABLE 表名;

删除表中所有的数据，释放表的存储空间!!!

7.2 与DELETE对比

• TRUNCATE语句不能回滚，DELETE语句删除数据可以回滚
• TRUNCATE TABLE比DELETE速度快，使用的系统和事务日志资源少，但无事务且不触发TRIGGER，可能造成事故，不建议在开发代码中使用
• TRUNCATE TABLE功能与不带WHERE子句的DELETE语句相同

TRUNCATE TABLE detail_dept;

8 总结

本文详细介绍了MySQL中创建和管理表的相关知识，从基础知识（数据存储过程、命名规则、数据类型）到数据库的创建、管理，再到表的创建、修改、重命名、删除、清空。

通过学习，可以掌握数据库和表的完整操作流程，了解命名和类型选择的规范，以及不同操作的注意事项，能有效进行MySQL中表的管理工作。