【MySQL】第10章_创建和管理表

1. 基础知识

1.1 一条数据存储的过程

1.2 标识符命名规则

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

1.3 MySQL中的数据类型

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

2. 创建和管理数据库

2.1 创建数据库

• 方式1：创建数据库

CREATE DATABASE 数据库名;

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

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

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

CREATE DATABASE IF NOT EXISTS 数据库名;

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

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

2.2 使用数据库

查看当前所有的数据库

SHOW DATABASES; #有一个S，代表多个数据库

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

SELECT DATABASE(); #使用的一个 mysql 中的全局函数

查看指定库下所有的表

SHOW TABLES FROM 数据库名;

查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
或者：
SHOW CREATE DATABASE 数据库名\G

使用/切换数据库

USE 数据库名;

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

2.3 修改数据库

更改数据库字符集

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

2.4 删除数据库

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

DROP DATABASE 数据库名;

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

DROP DATABASE IF EXISTS 数据库名;

3. 创建表

3.1 创建方式1

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6SpfwhvC-1670526028072)(https://qiuyusy-img.oss-cn-hangzhou.aliyuncs.com/img/image-20221209023801388.png)]

3.2 创建方式2

基于现有的表创建新表，数据也会复制过去

等于把查询的结果放到新表中

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bYRg5x5A-1670526028073)(https://qiuyusy-img.oss-cn-hangzhou.aliyuncs.com/img/image-20221209024013380.png)]

3.3 查看数据表结构

4. 修改表结构 ALTER

修改表指的是修改数据库中已经存在的数据表的结构。
使用ALTERTABLE 语句可以实现：

• 向已有的表中添加列
• 修改现有表中的列
• 删除现有表中的列
• 重命名现有表中的列

4.1 追加一个列

4.2 修改一个列

4.3 重命名一个列

4.4 删除一个列

5. 重命名表

6. 删除表

7. 清空表

8. 内容拓展

拓展1：阿里巴巴《Java开发手册》之MySQL字段命名

拓展2：如何理解清空表、删除表等操作需谨慎？！

拓展3：MySQL8新特性—DDL的原子化

在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即DDL操作要么成功要么回滚。DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log（该表是隐藏的表，通过show tables无法看到）中，用于回滚操作。通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。

分别在MySQL 5.7版本和MySQL 8.0版本中创建数据库和数据表，结果如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ieD1cvYX-1670526028078)(https://qiuyusy-img.oss-cn-hangzhou.aliyuncs.com/img/image-20221209025231850.png)]

9.练习

创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作

CREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET 'utf8';
USE test01_office;

创建表dept01

/*
字段 类型
id INT(7)
NAME VARCHAR(25)
*/

CREATE TABLE dept01 (
	id INT(7),
	NAME VARCHAR(25)
);

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

CREATE TABLE dept02
AS
SELECT *
FROM atguigudb.departments;

创建表emp01

/*
字段 类型
id INT(7)
first_name VARCHAR (25)
last_name VARCHAR(25)
dept_id INT(7)
*/

CREATE TABLE emp01(
id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7)
);

将列last_name的长度增加到50

DESC emp01;

ALTER TABLE emp01
MODIFY last_name VARCHAR(50);

根据表employees创建emp02

CREATE TABLE emp02
AS
SELECT *
FROM atguigudb.`employees`;
SHOW TABLES FROM test01_office;

删除表emp01

DROP TABLE IF EXISTS emp01;

将表emp02重命名为emp01

RENAME TABLE emp02 TO emp01;

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

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

直接删除表emp01中的列 department_id

ALTER TABLE emp01
DROP COLUMN department_id;

创建数据库 test02_market

CREATE DATABASE IF NOT EXISTS test02_market CHARACTER SET 'utf8';

将c_contact字段插入到c_birth字段后面

ALTER TABLE customers MODIFY c_contact VARCHAR(50) AFTER c_birth;

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

ALTER TABLE customers MODIFY c_name VARCHAR(70);

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

ALTER TABLE customers ADD c_gender CHAR(1) AFTER c_name;