• MySQL数据表的基本操作和基于 MySQL数据表的基本操作的综合实例项目

    MySQL数据表的基本操作和基于 MySQL数据表的基本操作的综合实例项目

    1、创建数据表

    1.1、创建表的语法形式

    创建数据表的语句为： CREATE TABLE。

    	CREATE  TABLE <表名>
      (
	列名1，数据类型 [列级别约束条件] [默认值],
	列名2，数据类型 [列级别约束条件] [默认值],
	……
	[表级别约束条件]
       );

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    创建数据表的举例：(语句在cmd环境中运行)
    创建数据库

    mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.01 sec)

    • 1
    • 2

    使用数据库

    mysql> USE test_db;
Database changed

    • 1
    • 2

    创建数据表：员工表tb_emp1。

    mysql> CREATE TABLE tb_emp1
    -> (
    -> id      INT(11),
    -> name   VARCHAR(25),
    -> deptId  INT(11),
    -> salary  FLOAT
    -> );
Query OK, 0 rows affected, 2 warnings (0.02 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    1.2、使用主键约束

    主键，又称主码，是表中一列或多列的组合。主键约束（Primary Key constraint）要求主键列的数据唯一，并且不允许为空。
    1、单字段主键

    字段名 数据类型 PRIMARY KEY [默认值]

    • 1

    （1）、在定义列的同时制定主键。
    定义数据表tb_emp 2，其主键为id，SQL语句如下：

    mysql> CREATE TABLE tb_emp2
    -> (
    -> id    INT(11) PRIMARY KEY,
    -> name  VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT
-> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    （2）、在定义完所有的列后定义制定的主键。
    定义数据表tb_emp 3，其主键为id，SQL语句如下：

    mysql> CREATE TABLE tb_emp3
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected, 2 warnings (0.02 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    2、多字段联合主键

     PRIMARY KEY [字段1, 字段2,. . ., 字段n]

    • 1

    主键由多个主键联合而成。
    定义数据表tb_emp4，假设表中间没有主键员工的id，为了唯一确定一个员工，可以把员工的name、deptId联合起来做为主键，SQL语句如下：

    mysql> CREATE TABLE tb_emp4
    ->  (
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(name,deptId)
    -> );
Query OK, 0 rows affected, 1 warning (0.02 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    1.3、使用外键约束

    外键用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。
    一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

    [CONSTRAINT <外键名>] FOREIGN KEY 列名1 [ ,列名2,…] 
REFERENCES <主表名> 主键列1 [ ,主键列2,…]

    • 1
    • 2

    外键要对应其他表的主键，使两个表之间相互关联起来。外键主要启动保证数据的完整性，它不可以删除在另一个表中具有关联关系的行，保证了数据的完整性、一致性。
    定义数据表tb_emp5，让它的键deptId作为外键关联到tb_dept1的主键id：

    1、	创建一个部门表tb_dept1，SQL语句如下
mysql> CREATE TABLE tb_dept1
    -> (
    -> id       INT(11) PRIMARY KEY,
    -> name    VARCHAR(22)  NOT NULL,
    -> location  VARCHAR(50)
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    2、 创建数据表tb_emp5SQL语句为

    mysql> CREATE TABLE tb_emp5
    -> (
    -> id      INT(11) PRIMARY KEY,
    -> name   VARCHAR(25),
    -> deptId  INT(11),
    -> salary   FLOAT,
    -> CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    1.4、使用非空约束

    非空约束（Not Null constraint）指字段的值不能为空。对于使用了非空约束的字段如果用户在添加数据时，没有指定值，数据库系统会报错。

    字段名 数据类型 not null

    • 1

    定义数据表tb_emp6，指定员工的名称不能为空，SQL语句如下：

    mysql> CREATE TABLE tb_emp6
    -> (
    -> id     INT(11) PRIMARY KEY,
    -> name   VARCHAR(25) NOT NULL,
    -> deptId  INT(11),
    -> salary  FLOAT
    -> );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    1.5、使用唯一性约束

    唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。
    1、在定义完列之后，直接制定唯一约束

    字段名 数据类型 UNIQUE

    • 1

    定义数据表tb_dept2，指定部门的名称唯一，SQL语句如下：

    mysql> CREATE TABLE tb_dept2
    -> (
    -> id      INT(11) PRIMARY KEY,
    -> name    VARCHAR(22) UNIQUE,
    -> location  VARCHAR(50)
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    2、在定义完所有的列之后，制定唯一约束

    [CONSTRAINT <约束名>] UNIQUE(<字段名>)

    • 1

    定义数据表tb_dept3，指定部门的名称唯一，SQL语句如下：

    mysql> CREATE TABLE tb_dept3
    -> (
    -> id      INT(11) PRIMARY KEY,
    -> name    VARCHAR(22),
    -> location  VARCHAR(50),
    -> CONSTRAINT STH UNIQUE(name)
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    1.6、使用默认约束

    默认约束（Default Constraint）指定某列 的默认值。

    字段名 数据类型 DEFAULT 默认值

    • 1

    定义数据表tb_emp7，指定员工的部门编号默认为1111，SQL语句如下：

    mysql> CREATE TABLE tb_emp7
    -> (
    -> id      INT(11) PRIMARY KEY,
    -> name   VARCHAR(25) NOT NULL,
    -> deptId  INT(11) DEFAULT 1111,
    -> salary  FLOAT
    -> );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    唯一UNIQUE约束和主键PRIMARY KEY约束的区别：
一个表中可以有多个字段声明为UNIQUE，但是只能有一个PRIMARY KEY	 声明。
声明为PRIMAY KEY的列不可以有空值，但是声明为UNIQUE的字段可以有空值存在。

    • 1
    • 2
    • 3

    1.7、设置表的属性值自动增加

    在数据库应用中，经常希望在每次插入新记录时，系统就会自动生成字段的主键值。可以通过为表主键添加AUTO_INCREMENT关键字来实现。
    一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。

    字段名 数据类型 AUTO_INCREMENT

    • 1

    定义数据表tb_emp8，指定员工的编号自动递增，SQL语句如下：

    mysql> CREATE TABLE tb_emp8
    -> (
    -> id      INT(11) PRIMARY KEY AUTO_INCREMENT,
    -> name   VARCHAR(25) NOT NULL,
    -> deptId  INT(11),
    -> salary  FLOAT
    -> );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    2、查看数据表结构

    2.1、查看表基本结构语句DESCRIBE/DESC

    该语句可以查看表的字段信息，其中包括：字段名、字段数据类型、是否为主键、是否有默认值等。

    DESCRIBE <表名>/DESC <表名>

    • 1

    1、DESCRIBE
    使用DESCRIBE查看表tb_dept1的表结构，SQL语句如下：

    mysql> DESCRIBE tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    2、DESC
    使用DESC查看表tb_emp1的表结构，SQL语句如下：

    mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int         | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    2.2、查看数据表详细的结构语句SHOW CREATE TABLE

    SHOW CREATE TABLE语句可以用来显示数据表的创建语句。

    SHOW CREATE TABLE <表名\G>

    • 1

    使用SHOW CREATE TABLE查看表tb_emp1的详细信息，SQL语句如下：

    使用参数’\G’之后的结果如下

    mysql> SHOW CREATE TABLE tb_emp1\G;
*************************** 1. row ***************************
       Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
  `id` int DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    从查询创建数据表详细的查询语句来看：使用了‘\G’可以使显示更加的清晰。

    3、修改数据表

    3.1、修改数据表名称

    MySQL是通过ALTER TABLE语句来实现表名的修改的。

    ALTER TABLE <旧表名> RENAME [TO] <新表名>;

    • 1

    将数据表tb_dept3改名为tb_deptment3。

    mysql> ALTER TABLE tb_dept3 RENAME tb_deptment3;
Query OK, 0 rows affected (0.01 sec)

    • 1
    • 2

    3.2、修改字段的数据类型

    修改字段的数据类型，就是把字段的数据类型转换成另一种数据类型。

     ALTER TABLE <表名> MODIFY <字段名>  <数据类型>

    • 1

    将数据表tb_dept1中name字段的数据类型由VARCHAR(22)修改成VARCHAR(30)。
    1、执行修改表名操作之前，使用DESC查看tb_dept表结构，结果如下：

    mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    2、执行修改表名操作

    mysql> ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

    • 1
    • 2
    • 3

    3、执行修改表名操作之后，使用DESC查看tb_dept表结构，结果如下：

    mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    可以看到数据表tb_dept1的name字段的数据类型由VARCHAR(22)修改成了VARCHAR(30)。

    3.3、修改字段名称

    MySQL中修改表字段名的语法规则如下：

    ALTER TABLE <表名> 
CHANGE <旧字段名> <新字段名> <新数据类型>;

    • 1
    • 2

    将数据表tb_dept1中的location字段名称改为loc，数据类型保持不变，SQL语句如下：

    mysql> ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb_dept1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| loc   | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    将数据表tb_dept1中的loc字段名称改为location，同时将数据类型变为VARCHAR(60)，SQL语句如下：

    mysql> ALTER TABLE tb_dept1 CHANGE loc location VARCHAR(60);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| location | varchar(60) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    3.4、添加字段

    在已经存在的表添加新的字段。

    ALTER TABLE <表名> 
ADD <新字段名> <数据类型> 
[约束条件] [FIRST | AFTER 已存在字段名];

    • 1
    • 2
    • 3

    1、添加无完整性约束条件的字段
    在数据表tb_dept1中添加一个没有完整性约束的INT类型的字段managerId，SQL语句如下：

    mysql> ALTER TABLE tb_dept1 ADD managerId INT(10);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  |     | NULL    |       |
| location  | varchar(60) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    2、添加有完整性约束条件的字段
    在数据表tb_dept1中添加一个不能为空的VARCHAR(12)类型的字段column1，SQL语句如下：

    mysql> ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  |     | NULL    |       |
| location  | varchar(60) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
| column1   | varchar(12) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    3、在数据表的第一列添加一个字段
    在数据表tb_dept1中添加一个INT类型的字段column2，SQL语句如下：

    mysql>  ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql>  DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| column2   | int         | YES  |     | NULL    |       |
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  |     | NULL    |       |
| location  | varchar(60) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
| column1   | varchar(12) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    4、在数据表的制定的列之后添加一个字段
    在数据表tb_dept1中name列后添加一个INT类型的字段column3，SQL语句如下：

    mysql> ALTER TABLE tb_dept1 ADD column3 INT(11) AFTER name;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| collumn2  | int         | YES  |     | NULL    |       |
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  |     | NULL    |       |
| column3   | int         | YES  |     | NULL    |       |
| location  | varchar(60) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
| column1   | varchar(12) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    3.5、删除字段

    删除字段是将数据表中的某个字段从表中移除。

    ALTER TABLE <表名> DROP <字段名>;

    • 1

    删除数据表tb_dept1表中的column2字段，，SQL语句如下：

    mysql> ALTER TABLE tb_dept1 DROP column2;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  |     | NULL    |       |
| column3   | int         | YES  |     | NULL    |       |
| location  | varchar(60) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
| column1   | varchar(12) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    3.6、修改字段排列的位置

    ALTER TABLE来改变表中字段的相对位置。

    ALTER TABLE <表名> 
MODIFY <字段1> <数据类型>  FIRST|AFTER <字段2>;

    • 1
    • 2

    1、修改字段为数据表的第一个字段
    将数据表tb_dept中的column1字段修改为表的第一个字段，SQL语句如下：

    mysql> ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| column1   | varchar(12) | YES  |     | NULL    |       |
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  |     | NULL    |       |
| column3   | int         | YES  |     | NULL    |       |
| location  | varchar(60) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    2、修改字段到数据表制定的列之后
    将数据表tb_dept1中的column1字段插入到location字段后面，SQL语句如下：

    mysql> ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER location;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  DESC tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  |     | NULL    |       |
| column3   | int         | YES  |     | NULL    |       |
| location  | varchar(60) | YES  |     | NULL    |       |
| column1   | varchar(12) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    3.7、修改数据表的存储引擎

    在MySQL中，存储引擎是指MySQL数据库中表的存储类型。可以根据自己的需要，选择不同的引擎，甚至可以为每一张表选择不同的存储引擎。

    qALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;

    将数据表tb_deptment3的存储引擎修改为MyISAM。
    1、在修改存储引擎之前，先使用SHOW CREATE TABLE查看表tb_deptment3当前的存储引擎，结果如下。

    在这里插入图片描述

    2、修改存储引擎

    mysql>  ALTER TABLE tb_deptment3 ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

    • 1
    • 2
    • 3

    3、在修改存储引擎之后，使用SHOW CREATE TABLE查看表tb_deptment3当前的存储引擎，结果如下。
    在这里插入图片描述

    3.8、修改数据表的外键约束

    对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主表和从表间的关联关系。

    ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

    • 1

    删除数据表tb_emp9中的外键约束。
    1、创建表tb_emp9，创建外键deptId关联tb_dept1表的主键id，SQL语句如下：

    mysql> CREATE TABLE tb_emp9
    -> (
    -> id      INT(11) PRIMARY KEY,
    -> name   VARCHAR(25),
    -> deptId  INT(11),
    -> salary   FLOAT,
    -> CONSTRAINT fk_emp_dept  FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
    -> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    使用SHOW CREATE TABLE查看表tb_emp9的结构，结果如下：
    在这里插入图片描述

    2、删除数据表tb_emp9中的外键约束。

    mysql> ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

    • 1
    • 2
    • 3

    3、使用SHOW CREATE TABLE查看表删除tb_emp9中外键约束的结构，结果如下：
    在这里插入图片描述

    4、删除数据表

    4.1、删除没有被关联的表

    删除数据表tb_dept2，SQL语句如下：

    mysql> DROP TABLE IF EXISTS tb_dept2;
Query OK, 0 rows affected (0.01 sec)
 

    • 1
    • 2
    • 3

    4.2、删除被其它表关联的主表

    在数据库中创建两个关联表。
    1、创建表tb_dept2，SQL语句如下：

    mysql> CREATE TABLE tb_dept2
    -> (
    -> id       INT(11) PRIMARY KEY,
    -> name    VARCHAR(22),
    -> location  VARCHAR(50)
    -> );
Query OK, 0 rows affected, 1 warning (0.02 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    2、创建表tb_emp，SQL语句如下：

    mysql> CREATE TABLE tb_emp
    -> (
    -> id       INT(11) PRIMARY KEY,
    -> name    VARCHAR(25),
    -> deptId   INT(11),
    -> salary   FLOAT,
    -> CONSTRAINT fk_emp_dept  FOREIGN KEY (deptId) REFERENCES tb_dept2(id)
    -> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    使用SHOW CREATE TABLE命令查看表tb_emp的外键约束，结果如下：
    在这里插入图片描述

    删除被数据表tb_emp关联的数据表tb_dept2。

    mysql>  DROP TABLE tb_dept2;
ERROR 3730 (HY000): Cannot drop table 'tb_dept2' referenced by a foreign key constraint 'fk_emp_dept' on table 'tb_emp'.

    • 1
    • 2

    删除会出现错误，因为存在外键的约束，主表不能直接删除。
    解除子表的外键约束

    mysql> ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

    • 1
    • 2
    • 3

    删除数据表tb_dept2;

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

    • 1
    • 2

    消除外键约束后，主表就可以删除了。

    5、综合案例-数据表的基本操作

    1、案例要求

    创建数据库company，按照下面数据表格的要求创建两个数据表：offices和employees。

    1.1、Offices表结构

    字段名数据类型主键外键非空唯一自增
    officeCodeINT(10)是否是是否
    cityVARCHAR(50)否否是否否
    addressVARCHAR(50)否否否否否
    countryVARCHAR(50)否否是否否
    postalCodeVARCHAR(15)否否否是否

    1.2、Employees表结构

    字段名数据类型主键外键非空唯一自增
    employeeNumberINT(11)是否是是是
    lastNameVARCHAR(50)否否是否否
    firstNameVARCHAR(50)否否是否否
    mobileVARCHAR(25)否否否是否
    officeCodeINT(10)否是是否否
    jobTileVARCHAR(50)否否是否否
    birthDATETIME否否是否否
    noteVARCHAR(255)否否否否否
    sexVARCHAR(5)否否否否否

    通过数据表的创建、查看、修改和删除等操作，来达到掌握有关数据表的基本操作的能力。其具体要求如下所示：
    1、创建数据库company
    2、创建数据表offices
    3、创建数据表employees
    4、将数据表employees的mobile字段修改到officeCode字段后面
    5、将数据表employees的birth字段修改名称为employee_birth
    6、修改sex字段的数据类型为CHAR(1)，非空约束
    7、删除字段note
    8、增加字段名称为favorite_activity，数据类型为VARCHAR(100)
    9、修改数据表employees存储引擎为MyISAM
    10、将数据表employees名称修改为employees_info
    11、删除数据表offices

    2、案例实现过程

    一、cmd环境实现案例过程

    1、创建数据库company

    登入数据库：

    C:\WINDOWS\system32>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    创建数据库：

    mysql> CREATE DATABASE company;
Query OK, 1 row affected (0.01 sec)

    • 1
    • 2

    2、创建数据表offices

    1、使用数据库company

    mysql> USE company;
Database changed

    • 1
    • 2

    2、创建数据表offices

    mysql> CREATE TABLE offices
    -> (
    -> officeCode INT(10) NOT NULL UNIQUE,
    -> city VARCHAR(50) NOT NULL,
    -> address VARCHAR(50),
    -> country VARCHAR(50) NOT NULL,
    -> postalCode VARCHAR(15),
    -> PRIMARY KEY(officeCode)
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    可以看到，数据表offices已经创建成功。

    3、创建数据表employees

    mysql> CREATE TABLE employees
    -> (
    -> employeeNumber INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> lastName VARCHAR(50) NOT NULL,
    -> firstName VARCHAR(50) NOT NULL,
    -> mobile VARCHAR(25) UNIQUE,
    -> officeCode INT(10) NOT NULL,
    -> jobTile VARCHAR(50) NOT NULL,
    -> birth DATETIME NOT NULL,
    -> note VARCHAR(255),
    -> sex VARCHAR(5),
    -> CONSTRAINT office_fk FOREIGN KEY(officeCode) REFERENCES offices(officeCode)
    -> );
Query OK, 0 rows affected, 2 warnings (0.02 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    可以看到，数据表employees已经创建成功。

    4、将数据表employees的mobile字段修改到officeCode字段后面

    mysql> ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeeNumber | int          | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50)  | NO   |     | NULL    |                |
| firstName      | varchar(50)  | NO   |     | NULL    |                |
| officeCode     | int          | NO   | MUL | NULL    |                |
| mobile         | varchar(25)  | YES  | UNI | NULL    |                |
| jobTile        | varchar(50)  | NO   |     | NULL    |                |
| birth          | datetime     | NO   |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(5)   | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    可以看到，数据表employees的mobile字段已经修改到了officeCode字段后面。

    5、将数据表employees的birth字段修改名称为employee_birth

    mysql> ALTER TABLE employees CHANGE birth emoloyee_birth DATETIME;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeeNumber | int          | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50)  | NO   |     | NULL    |                |
| firstName      | varchar(50)  | NO   |     | NULL    |                |
| officeCode     | int          | NO   | MUL | NULL    |                |
| mobile         | varchar(25)  | YES  | UNI | NULL    |                |
| jobTile        | varchar(50)  | NO   |     | NULL    |                |
| emoloyee_birth | datetime     | YES  |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(5)   | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    可以看到，数据表employees的birth字段已经修改名称为了employee_birth 。

    6、修改sex字段的数据类型为CHAR(1)，非空约束

    mysql> ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeeNumber | int          | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50)  | NO   |     | NULL    |                |
| firstName      | varchar(50)  | NO   |     | NULL    |                |
| officeCode     | int          | NO   | MUL | NULL    |                |
| mobile         | varchar(25)  | YES  | UNI | NULL    |                |
| jobTile        | varchar(50)  | NO   |     | NULL    |                |
| emoloyee_birth | datetime     | YES  |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | char(1)      | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    可以看到，数据表employees的sex字段的数据类型已经修改为了CHAR(1)。

    7、删除字段note

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

mysql> DESC employees;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| employeeNumber | int         | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50) | NO   |     | NULL    |                |
| firstName      | varchar(50) | NO   |     | NULL    |                |
| officeCode     | int         | NO   | MUL | NULL    |                |
| mobile         | varchar(25) | YES  | UNI | NULL    |                |
| jobTile        | varchar(50) | NO   |     | NULL    |                |
| emoloyee_birth | datetime    | YES  |     | NULL    |                |
| sex            | char(1)     | NO   |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    可以看到，数据表employees的note字段的已经删除了。

    8、增加字段名称为favorite_activity，数据类型为VARCHAR(100)

    mysql> ALTER TABLE employees ADD favorite_activity VARCHAR(100);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC employees;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| employeeNumber    | int          | NO   | PRI | NULL    | auto_increment |
| lastName          | varchar(50)  | NO   |     | NULL    |                |
| firstName         | varchar(50)  | NO   |     | NULL    |                |
| officeCode        | int          | NO   | MUL | NULL    |                |
| mobile            | varchar(25)  | YES  | UNI | NULL    |                |
| jobTile           | varchar(50)  | NO   |     | NULL    |                |
| emoloyee_birth    | datetime     | YES  |     | NULL    |                |
| sex               | char(1)      | NO   |     | NULL    |                |
| favorite_activity | varchar(100) | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    可以看到，数据表employees已经增加字段名称为favorite_activity，数据类型为VARCHAR(100)。

    9、修改数据表employees存储引擎为MyISAM

    1、删除外键约束

    mysql> ALTER TABLE employees DROP FOREIGN KEY office_fk;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

    • 1
    • 2
    • 3

    2、修改数据表employees存储引擎为MyISAM

    mysql> ALTER TABLE employees ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE employees\G;
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `employeeNumber` int NOT NULL AUTO_INCREMENT,
  `lastName` varchar(50) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `officeCode` int NOT NULL,
  `mobile` varchar(25) DEFAULT NULL,
  `jobTile` varchar(50) NOT NULL,
  `emoloyee_birth` datetime DEFAULT NULL,
  `sex` char(1) NOT NULL,
  `favorite_activity` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`employeeNumber`),
  UNIQUE KEY `mobile` (`mobile`),
  KEY `office_fk` (`officeCode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    可以看到存储引擎已经变为了

    ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

    • 1

    可以看到，已经将数据表employees的存储引擎修改为了MyISAM。

    10、将数据表employees名称修改为employees_info

    mysql> ALTER TABLE employees RENAME employees_info;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+-------------------+
| Tables_in_company |
+-------------------+
| employees_info    |
| offices           |
+-------------------+
2 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    可以看到，已经将数据表employees的名称修改为employees_info。

    11、删除数据表employees_info

    mysql> DROP TABLE employees_info;
Query OK, 0 rows affected (0.00 sec)

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    可以看到，已经将数据表employees删除了。

    二、用Navicat实现案例过程

    1、新建查询

    进入Navicat软件好，新建一个创建数据库的查询。
    在查询中编写如下的SQL语句，如下所示。

    -- 1、创建数据库company
CREATE DATABASE company;

-- 2、创建数据表offices
USE company;

CREATE TABLE offices
(
	officeCode INT(10) NOT NULL UNIQUE,
	city VARCHAR(50) NOT NULL,
	address VARCHAR(50),
	country VARCHAR(50) NOT NULL,
	postalCode VARCHAR(15),
	PRIMARY KEY(officeCode)
);


-- 3、创建数据表employees
CREATE TABLE employees
(
	employeeNumber INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
	lastName VARCHAR(50) NOT NULL,
	firstName VARCHAR(50) NOT NULL,
	mobile VARCHAR(25) UNIQUE,
	officeCode INT(10)			NOT NULL,
	jobTile VARCHAR(50) NOT NULL,
	birth DATETIME NOT NULL,
	note VARCHAR(255),
	sex VARCHAR(5),
	CONSTRAINT office_fk FOREIGN KEY(officeCode) REFERENCES offices(officeCode)
);

-- 4、将数据表employees的mobile字段修改到officeCode字段后面
ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;
DESC employees;


-- 5、将数据表employees的birth字段修改名称为employee_birth
ALTER TABLE employees CHANGE birth employee_birth DATETIME;
DESC employees;

-- 6、修改sex字段的数据类型为CHAR(1)，非空约束
ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;
DESC employees;

-- 7、删除字段note
ALTER TABLE employees DROP note;
DESC employees;


-- 8、增加字段名称为favorite_activity，数据类型为VARCHAR(100)
ALTER TABLE employees ADD favorite_activity VARCHAR(100);
DESC employees;

-- 9、修改数据表employees存储引擎为MyISAM
ALTER TABLE employees DROP FOREIGN KEY office_fk;
ALTER TABLE employees ENGINE=MyISAM;
SHOW CREATE TABLE employees;


-- 10、将数据表employees名称修改为employees_info
ALTER TABLE employees RENAME employees_info;
SHOW TABLES;

-- 11、删除数据表employees_info;
DROP TABLE employees_info;
SHOW TABLES;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68

    2、运行过程

    依次选择要运行的SQL语句。运行过程如下所示：
    1、创建数据库company
    在这里插入图片描述

    2、创建数据表offices
    在这里插入图片描述
    在这里插入图片描述

    3、创建数据表employees
    在这里插入图片描述

    4、将数据表employees的mobile字段修改到officeCode字段后面

    在这里插入图片描述
    在这里插入图片描述

    5、将数据表employees的birth字段修改名称为employee_birth
    在这里插入图片描述
    在这里插入图片描述

    6、修改sex字段的数据类型为CHAR(1)，非空约束
    在这里插入图片描述
    在这里插入图片描述

    7、删除字段note
    在这里插入图片描述
    在这里插入图片描述

    8、增加字段名称为favorite_activity，数据类型为VARCHAR(100)
    在这里插入图片描述
    在这里插入图片描述

    9、修改数据表employees存储引擎为MyISAM
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    10、将数据表employees名称修改为employees_info
    在这里插入图片描述
    在这里插入图片描述

    11、删除数据表offices
    在这里插入图片描述
    在这里插入图片描述

    总结

    本文主要介绍了MySQL中数据表的各种操作：
    创建数据表、添加各类约束、查看数据表表结构，修改数据表和删除数据 表。使读者可以很快掌握MySQL数据库中关于创建数据表、添加各类约束、查看数据表表结构，修改数据表和删除数据表的基本使用方法。另外还介绍了分别使用cmd环境和Navicat软件环境中创建数据表表、添加各类约束、查看数据表表结构，修改数据表和删除数据表的使用方法。

  • 相关阅读:
    IDEA代码替换
    网络基础知识100问！答完秒变网络高手，你能答对多少题？
    1--Linux:环境安装与Linux常识
    Flutter开发好用插件url_launcher详解-启动 URL
    金仓数据库KingbaseES ksql工具用户指南及参考--3. Ksql入门
    伊朗黑客对以色列科技行业发起恶意软件攻击
    李沐67_自注意力——自学笔记
    C语言 实现 链 显示 效果 查找 修改 删除
    【OpenGL】八、帧缓冲
    layui select多选下拉显示 以及多值回显
  • 原文地址：https://blog.csdn.net/m0_47419053/article/details/126083790