十二、创建和管理表

2023-01-25 18:38 307人阅读评论(0)

前置知识：

一、数据库开发与实战专栏导学及数据库基础概念入门
 二、MySQL 介绍及 MySQL 安装与配置
 三、MySQL 数据库的基本操作
 四、MySQL 存储引擎及数据类型
 五、数据导入与基本的 SELECT 语句
 六、MySQL 数据库练习题1(包含前5章练习题目及答案)
七、MySQL 多表查询详解(附练习题及答案----超详细)
八、MySQL 常用函数汇总(1)
九、MySQL 常用函数汇总(2)
十、MySQL 聚合函数、分组查询及过滤分组
 十一、子查询详解

在对 MySQL 数据表进行操作之前，必须首先使用 USE 语句选择数据库，才可在指定的数据库中对数据表进行操作，如创建数据表、查看表结构、修改表结构，以及重命名、复制或删除数据表等，否则无法对数据表进行操作。本文将对数据表的操作方法进行详细介绍，本文知识架构及重难点如下：

一、基础知识

1.1 一条数据存储的过程

存储数据是处理数据的第一步。 只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只能是一团乱麻，无从下手。那么，怎样才能把用户各种经营相关的、纷繁复杂的数据，有序、高效地存储起来呢？在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据，如下图所示：

我们要先创建一个数据库，而不是直接创建数据表呢？因为从系统架构的层次上看，MySQL 数据库系统从大到小依次是 数据库服务器、数据库、数据表、 数据表的 行与列 。MySQL 数据库服务器之前已经安装且创建数据库以及数据类型分别在三、MySQL 数据库的基本操作和四、MySQL 存储引擎及数据类型两文中已经进行讲解，所以我们直接就从创建数据表开始。

1.2 标识符命名规则

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

1.3 数据类型及数据库操作

https://blog.csdn.net/xw1680/article/details/128526435
https://blog.csdn.net/xw1680/article/details/128503229

注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上 数据库名.
命令补充: 查看指定库下所有表 SHOW TABLES FROM 数据库名;

二、创建表

必须具备： CREATE TABLE 权限、存储空间。语法格式如下：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 数据表名
     [(create_definition,…)][table_options] [select_statement]

CREATE TABLE 语句的参数说明如下表所示：

下面介绍列属性 create_definition 部分，每一列定义的具体格式如下：

属性 create_definition 的参数说明如下表所示：

以上是创建一个数据表的一些基础知识，看起来十分复杂，但在实际的应用中使用最基本的格式创建数据表即可，具体格式如下：

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

约束条件会在后续章节进行详细讲解，本文不进行过多赘述。加上了 IF NOT EXISTS 关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

【示例1】使用CREATE TABLE语句在MySQL数据库db_admin中创建一个名为tb_admin的数据表，该表包括id、user、password和createtime等字段。

#如果不存在名称为db_admin的数据库，那么需要先创建该数据库
mysql> CREATE DATABASE db_admin;
Query OK, 1 row affected (0.01 sec)

#切换数据库
mysql> USE db_admin;
Database changed
mysql> #创建表
mysql> CREATE TABLE tb_admin(
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> user VARCHAR(30) NOT NULL,
    -> password VARCHAR(30) NOT NULL,
    -> createtime DATETIME);
Query OK, 0 rows affected (0.03 sec)

创建表的 CREATE TABLE 语句还有另外一种语法结构：在一个已经存在的数据表的基础上创建该表的备份，也就是复制表。这种用法的语法格式如下：

参数说明如下：

[IF NOT EXISTS]：可选项，如果使用该子句，表示当要创建的数据表名不存在时，才会创建。如果不使用该子句，当要创建的数据表名存在时，将出现错误。
数据表名：表示新创建的数据表的名称，该数据表名必须是在当前数据库中不存在的表名。
{LIKE源数据表名|(LIKE源数据表名)}：必选项，用于指定依照哪个数据表来创建新表，也就是要为哪个数据表创建副本。

说明: 使用该语法复制数据表时，将创建一个与源数据表结构相同的新表，源数据表的列名、数据类型和索引都将被复制，但是表的内容不会复制。因此，新创建的表是一个空表。如果想要复制表中的内容，可以通过使用 AS(查询表达式)子句 来实现。

【示例2】根据employees表分别创建emp1与emp2表。

#补充: 创建表 tb_admin_new
mysql> SELECT * FROM tb_admin;
+----+----------+-----------+---------------------+
| id | username | password  | createtime          |
+----+----------+-----------+---------------------+
|  1 | Amo      | 123456 | 2023-01-25 00:00:00 |
+----+----------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tb_admin_new LIKE tb_admin;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM tb_admin_new;
Empty set (0.00 sec)

mysql> USE mysql_study;
Database changed
mysql> CREATE TABLE emp1 AS SELECT * FROM employees;
Query OK, 107 rows affected, 2 warnings (0.01 sec)
Records: 107  Duplicates: 0  Warnings: 2

mysql> CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 2
#查看当前数据库下所有的表
mysql> SHOW TABLES;

三、查看表结构

对于一个创建成功的数据表，可以使用 SHOW COLUMNS 或 DESCRIBE 语句查看指定数据表的表结构。下面分别对这两个语句进行介绍。

3.1 使用 SHOW COLUMNS 语句查看

在 MySQL 中，使用 SHOW COLUMNS 语句可以查看表结构，SHOW COLUMNS 语句的基本语法格式如下：

SHOW [FULL] COLUMNS FROM 数据表名 [FROM 数据库名];
SHOW [FULL] COLUMNS FROM 数据库名.数据表名;

【示例3】使用SHOW COLUMNS语句查看数据表tb_admin的结构。

mysql> SHOW COLUMNS FROM tb_admin FROM db_admin;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| user       | varchar(30) | NO   |     | NULL    |                |
| password   | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

其中，各个字段的含义分别解释如下：

Null：表示该列是否可以存储 NULL 值。
Key：表示该列是否已编制索引。PRI 表示该列是表主键的一部分；UNI 表示该列是 UNIQUE 索引的一部分；MUL 表示在列中某个给定值允许出现多次。
Default：表示该列是否有默认值，若有的话指定值是多少。
Extra：表示可以获取的与给定列有关的附加信息，例如 AUTO_INCREMENT 等。

【示例4】使用SHOW FULL COLUMNS语句查看数据表tb_admin的结构。如下图所示：

3.2 使用 DESCRIBE 语句查看

在 MySQL 中，还可以使用 DESCRIBE 语句查看数据表结构。DESCRIBE 语句的基本语法格式如下：

DESCRIBE 数据表名;#DESCRIBE可以简写成DESC

在查看表结构时，也可以只列出某一列的信息。其语法格式如下：

DESCRIBE 数据表名 列名;

【示例5】使用DESCRIBE语句的简写形式查看数据表tb_admin中的某一列信息。

mysql> USE db_admin;
Database changed
mysql> DESC tb_admin user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| user  | varchar(30) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

3.3 查看表详细结构语句 SHOW CREATE TABLE

SHOW CREATE TABLE 语句可以用来显示创建表时的 CREATE TABLE 语句，语法格式如下：

SHOW CREATE TABLE <表名\G>;

提示： 使用 SHOW CREATE TABLE 语句，不仅可以查看表创建时候的详细语句，还可以查看存储引擎和字符编码。如果不加 \G 参数，显示的结果可能非常混乱，加上参数 \G 之后，可使显示结果更加直观，易于查看。

【示例6】使用SHOW CREATE TABLE查看表tb_admin的详细信息，SQL语句如下：

mysql> SHOW CREATE TABLE tb_admin\G;
*************************** 1. row ***************************
       Table: tb_admin
Create Table: CREATE TABLE `tb_admin` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user` varchar(30) NOT NULL,
  `password` varchar(30) NOT NULL,
  `createtime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

四、修改表结构

修改表结构指增加或者删除字段、修改字段名／类型以及修改表名等，可以使用 ALTER TABLE 语句实现，语法如下：

ALTER [IGNORE] TABLE 数据表名 alter_spec[,alter_spec]…|  table_options

参数说明如下：

[IGNORE]：可选项，表示如果出现重复关键的行，则只执行一行，其他重复的行被删除。
数据表名：用于指定要修改的数据表的名称。

alter_spec 子句：用于定义要修改的内容，其语法格式如下：

上面的语法中，各参数说明如下：

① create_definition：用于定义列的数据类型和属性，与第二节CREATE TABLE语句中的语法相同。
② [FIRST | AFTER column_name ]：用于指定位于哪个字段的前面或者后面，使用FIRST关键字时，表示位于指定字段的前面；
使用AFTER关键字时，表示位于指定字段的后面。其中的column_name表示字段名。
③ [index_name]：可选项，用于指定索引名。
④ (index_col_name,...)：用于指定索引列名。
⑤ {SET DEFAULT literal | DROP DEFAULT}子句：为字段设置或者删除默认值。其中literal参数为要设置的默认值。
⑥ old_col_name：用于指定要修改的字段名。
⑦ new_tbl_name：用于指定新的表名。

table_options：用于指定表的一些特性参数，其中大多数选项涉及的是表数据如何存储及存储在何处，如 ENGINE 选项用于定义表的存储引擎。多数情况下，用户不必指定表选项。

说明： ALTER TABLE 语句允许指定多个动作，其动作间使用逗号分隔，每个动作表示对表的一个修改。

4.1 添加新字段和修改字段定义

在 MySQL 的 ALTER TABLE 语句中，可以通过使用 ADD [COLUMN] create_definition [FIRST |AFTER column_name ] 子句来添加新字段；使用 MODIFY [COLUMN] create_definition 子句修改已定义字段的定义。下面将通过一个具体实例演示如何为一个已有表添加新字段，并修改已有字段的定义。
【示例7】添加一个新的字段email，类型为varchar(50)，not null；将字段user的类型由varchar(30)改为varchar(40)。

mysql> USE db_admin;
Database changed
mysql> DESC tb_admin;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| user       | varchar(30) | NO   |     | NULL    |                |
| password   | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE tb_admin ADD email VARCHAR(50) NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE tb_admin MODIFY user VARCHAR(40);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

#或者
ALTER TABLE tb_admin ADD email VARCHAR(50) NOT NULL, MODIFY user VARCHAR(40);
mysql> DESC tb_admin;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| user       | varchar(40) | YES  |     | NULL    |                |
| password   | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
| email      | varchar(50) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

补充：在表的第一列添加一个字段以及在表的指定列之后添加一个字段。

mysql> DESC tb_admin;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| username   | varchar(40) | YES  |     | NULL    |                |
| password   | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE tb_admin ADD first_column INT FIRST;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE tb_admin ADD after_column INT AFTER username;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb_admin;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| first_column | int         | YES  |     | NULL    |                |
| id           | int         | NO   | PRI | NULL    | auto_increment |
| username     | varchar(40) | YES  |     | NULL    |                |
| after_column | int         | YES  |     | NULL    |                |
| password     | varchar(30) | NO   |     | NULL    |                |
| createtime   | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

4.2 修改字段名／类型

在 MySQL 的 ALTER TABLE 语句中，使用 CHANGE [COLUMN] old_col_name create_definition 子句可以修改字段名或者字段类型。【示例8】将数据表tb_admin的字段名user修改为username。

mysql> DESC tb_admin;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| user       | varchar(50) | YES  |     | NULL    |                |
| password   | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
| email      | varchar(50) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE tb_admin CHANGE COLUMN user username VARCHAR(40) NULL DEFAULT NULL;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESC tb_admin;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| username   | varchar(40) | YES  |     | NULL    |                |
| password   | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
| email      | varchar(50) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

4.3 删除字段

在 MySQL 的 ALTER TABLE 语句中，使用 DROP [COLUMN] col_name 子句可以删除指定字段。【示例9】将数据库db_admin中的数据表tb_admin中的字段email删除。

mysql> DESC tb_admin;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| username   | varchar(40) | YES  |     | NULL    |                |
| password   | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
| email      | varchar(50) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql>ALTER TABLE tb_admin DROP COLUMN email;
mysql>ALTER TABLE tb_admin DROP email;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb_admin;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| username   | varchar(40) | YES  |     | NULL    |                |
| password   | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

4.4 修改表名

在 MySQL 的 ALTER TABLE 语句中，使用 ALTER TABLE table_name RENAME AS new_table_name 子句可以修改表名。【示例10】将数据库db_admin中的数据表tb_admin更名为tb_admin_old。

mysql> ALTER TABLE tb_admin RENAME AS tb_admin_old;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+--------------------+
| Tables_in_db_admin |
+--------------------+
| tb_admin_old       |
+--------------------+
1 row in set (0.00 sec)

【示例11】将数据库db_admin中的数据表tb_admin_old更名为tb_admin。

#语法: RENAME TABLE 数据表名1 To 数据表名2
#该语句可以同时对多个数据表进行重命名，多个表之间以逗号,分隔。
mysql> SHOW TABLES;
+--------------------+
| Tables_in_db_admin |
+--------------------+
| tb_admin_old       |
+--------------------+
1 row in set (0.00 sec)

mysql> RENAME TABLE tb_admin_old TO tb_admin;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+--------------------+
| Tables_in_db_admin |
+--------------------+
| tb_admin           |
+--------------------+
1 row in set (0.00 sec)

4.5 修改字段的排列位置

对于一个数据表来说，在创建的时候，字段在表中的排列顺序就已经确定了，但表的结构也是可以改变的，可以通过 ALTER TABLE 来改变表中字段的相对位置。语法格式如下：

 ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;
#其中，字段1 指要修改位置的字段 数据类型 指 字段1 的数据类型 FIRST 为可选参数指将 字段1 修改为表的第一个字段 
#AFTER 字段2 指将 字段1 插入到 字段2 后面。

示例：修改字段为表的第一个字段以及修改字段到表的指定列之后。

mysql> DESC tb_admin;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| first_column | int         | YES  |     | NULL    |                |
| id           | int         | NO   | PRI | NULL    | auto_increment |
| username     | varchar(40) | YES  |     | NULL    |                |
| after_column | int         | YES  |     | NULL    |                |
| password     | varchar(30) | NO   |     | NULL    |                |
| createtime   | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> ALTER TABLE tb_admin MODIFY id INT FIRST;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE tb_admin MODIFY first_column INT AFTER after_column;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb_admin;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int         | NO   | PRI | NULL    |       |
| username     | varchar(40) | YES  |     | NULL    |       |
| after_column | int         | YES  |     | NULL    |       |
| first_column | int         | YES  |     | NULL    |       |
| password     | varchar(30) | NO   |     | NULL    |       |
| createtime   | datetime    | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

五、删除表

删除数据表的操作很简单，同删除数据库的操作类似，使用 DROP TABLE 语句即可实现。DROP TABLE 语句的基本语法格式如下：

DROP TABLE [IF EXISTS] 数据表名;

参数说明如下：

[IF EXISTS]：可选项，用于在删除表前先判断是否存在要删除的表，只有存在时，才执行删除操作，这样可以避免要删除的表不存在时出现错误信息。
数据表名：用于指定要删除的数据表名，可以同时删除多个数据表，多个数据表名之间用英文半角的逗号 , 分隔。

【示例12】删除数据表tb_admin_new。

mysql> SHOW TABLES;
+--------------------+
| Tables_in_db_admin |
+--------------------+
| tb_admin           |
| tb_admin_new       |
+--------------------+
2 rows in set (0.00 sec)

mysql> DROP TABLE tb_admin_new;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+--------------------+
| Tables_in_db_admin |
+--------------------+
| tb_admin           |
+--------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE tb_admin_new;
ERROR 1051 (42S02): Unknown table 'db_admin.tb_admin_new'
#在删除数据表的过程中，删除一个不存在的表将会产生错误，如果在删除语句中加入IF EXISTS关键字就不会出错了
mysql> DROP TABLE IF EXISTS tb_admin_new;
Query OK, 0 rows affected, 1 warning (0.00 sec)

注意： 删除数据表的操作应该谨慎使用。一旦删除了数据表，那么表中的数据将会全部清除，没有备份则无法恢复。删除表的外键约束以及删除被其他表关联的主表在后续讲解约束之后再进行演示。

六、课后练习

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

# 创建表dept01
#字段      类型
#id	 INT(7)
#NAME	 VARCHAR(25)

2、将表 departments 中的数据插入新表 dept02 中。
3、创建表 emp01。

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

4、将列 last_name 的长度增加到 50。
5、根据表 employees 创建 emp02。
6、删除表 emp01。
7、将表 emp02 重命名为 emp01。
8、在表 dept02 和 emp01 中添加新列 test_column，并检查所作的操作。
9、直接删除表 emp01 中的列 department_id。

课后练习2如下：

#1.创建数据库 test02_market
#2.创建数据表 customers
#c_num int
#c_name varchar(50)
#c_contact varchar(50)
#c_city varchar(50)
#c_birth date
# 3、将 c_contact 字段移动到 c_birth 字段后面
# 4、将 c_name 字段数据类型改为 varchar(70)
# 5、将c_contact字段改名为c_phone
# 6、增加c_gender字段到c_name后面，数据类型为char(1)
# 7、将表名改为customers_info
# 8、删除字段c_city

课后练习3如下：

#1、创建数据库test03_company

# 2、创建表offices
#officeCode int
#city varchar(30)
#address varchar(50)
#country varchar(50)
#postalCode varchar(25)

#3、创建表employees
#empNum int
#lastName varchar(50)
#firstName varchar(50)
#mobile varchar(25)
#code int
#jobTitle varchar(50)
#birth date
#note varchar(255)
#sex varchar(5)

# 4、将表employees的mobile字段修改到code字段后面
# 5、将表employees的birth字段改名为birthday
# 6、修改sex字段，数据类型为char(1)
# 7、删除字段note
# 8、增加字段名favoriate_activity，数据类型为varchar(100)
# 9、将表employees的名称修改为 employees_info

七、内容拓展

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

1、表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。(强制)

正 例: aliyun_admin，rdc_config，level3_name 
反例: AliyunAdmin，rdcConfig，level_3_name

2、禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。(强制)
3、表必备三字段：id, gmt_create, gmt_modified。说明：其中 id 必为主键，类型为 BIGINT UNSIGNED、单表时自增、步长为 1。gmt_create,gmt_modified 的类型均为 DATETIME 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新。(强制)
4、表的命名最好是遵循 业务名称_表的作用。正例：alipay_task 、 force_project、 trade_config。 (推荐)
5、库名与应用名称尽量一致。(推荐)
6、合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。(参考) 正例：无符号值可以避免误存负数，且扩大了表示范围。

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

表删除 操作将把表的定义和表中的数据一起删除，并且 MySQL 在执行删除操作时，不会有任何的确认信息提示，因此执行删除操时应当慎重。在删除表前，最好对表中的数据进行 备份 ，这样当操作失误时可以对数据进行恢复，以免造成无法挽回的后果。同样的，在使用 ALTER TABLE 进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进行完整的 备份，因为数据库的改变是 无法撤销 的，如果添加了一个不需要的字段，可以将其删除；相同的，如果删除了一个需要的列，该列下面的所有数据都将会丢失。

7.3 拓展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 版本中创建数据库和数据表，结果如下：

CREATE DATABASE mytest;
USE mytest;
CREATE TABLE book1(
book_id INT , book_name VARCHAR(255)
);
SHOW TABLES;

在 MySQL 5.7 版本中，测试步骤如下：删除数据表 book1 和数据表 book2，结果如下：

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

再次查询数据库中的数据表名称，结果如下：

mysql> SHOW TABLES;
Empty set (0.00 sec)

从结果可以看出，虽然删除操作时报错了，但是仍然删除了数据表 book1。

在 MySQL 8.0 版本中，测试步骤如下：删除数据表 book1 和数据表 book2，结果如下：

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

再次查询数据库中的数据表名称，结果如下：

mysql> SHOW TABLES;
+------------------+
| Tables_in_mytest |
+------------------+
| book1	|
+------------------+
1 row in set (0.00 sec)

从结果可以看出，数据表 book1 并没有被删除。

小结： 本文主要介绍了创建数据表、查看表结构、修改表结构、重命名表、复制表和删除表等内容。其中，创建和修改表这两部分内容比较重要，需要不断地练习才会对这两部分内容理解得更加透彻。而且，这两部分内容很容易出现语法错误，必须在练习中掌握正确的语法规则。创建表和修改表后一定要查看表的结构，这样可以确认操作是否正确。删除表时一定要特别小心，因为删除表的同时会删除表中的所有数据。

至此今天的学习就到此结束了，笔者在这里声明，笔者写文章只是为了学习交流，以及让更多学习数据库的读者少走一些弯路，节省时间，并不用做其他用途，如有侵权，联系博主删除即可。感谢您阅读本篇博文，希望本文能成为您编程路上的领航者。祝您阅读愉快！

好书不厌读百回，熟读课思子自知。而我想要成为全场最靓的仔，就必须坚持通过学习来获取更多知识，用知识改变命运，用博客见证成长，用行动证明我在努力。
如果我的博客对你有帮助、如果你喜欢我的博客内容，请 点赞、评论、收藏 一键三连哦！听说点赞的人运气不会太差，每一天都会元气满满呦！如果实在要白嫖的话，那祝你开心每一天，欢迎常来我博客看看。
编码不易，大家的支持就是我坚持下去的动力。点赞后不要忘了 关注 我哦！

转载：https://blog.csdn.net/xw1680/article/details/128758808

查看评论

飞道的博客