MySQL中有关系的表如何干掉？表的基础管理简单剖析。

TtrOpsStack 2022-06-24

311

一、创建数据表

案例1：创建tb_emp1表

# 切换数据库
mysql> use ttrdb;
Database changed

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

mysql>

# 查看表
mysql> show tables;
+-----------------+
| Tables_in_ttrdb |
+-----------------+
| tb_emp1         |
+-----------------+
1 row in set (0.00 sec)

mysql>

案例2：创建users表

CREATE TABLE `users` (
 `userid`                 bigint unsigned                           NOT NULL,
 `alias`                  varchar(100)    DEFAULT ''                NOT NULL,
 `name`                   varchar(100)    DEFAULT ''                NOT NULL,
 `surname`                varchar(100)    DEFAULT ''                NOT NULL,
 `passwd`                 varchar(60)     DEFAULT ''                NOT NULL,
 `url`                    varchar(255)    DEFAULT ''                NOT NULL,
 `autologin`              integer         DEFAULT '0'               NOT NULL,
 `autologout`             varchar(32)     DEFAULT '15m'             NOT NULL,
 `lang`                   varchar(5)      DEFAULT 'en_GB'           NOT NULL,
 `refresh`                varchar(32)     DEFAULT '30s'             NOT NULL,
 `type`                   integer         DEFAULT '1'               NOT NULL,
 `theme`                  varchar(128)    DEFAULT 'default'         NOT NULL,
 `attempt_failed`         integer         DEFAULT 0                 NOT NULL,
 `attempt_ip`             varchar(39)     DEFAULT ''                NOT NULL,
 `attempt_clock`          integer         DEFAULT 0                 NOT NULL,
 `rows_per_page`          integer         DEFAULT 50                NOT NULL,
 PRIMARY KEY (userid)
) ENGINE=InnoDB;

案例3：创建hosts表

create table hosts
(
hostid int(11),
name varchar(25),
ipv4 varchar(64),
ipv6 varchar(128)
);

二、修改数据表

其语法格式如下：

ALTER TABLE <表名> [修改选项]

修改选项的语法格式如下：

{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名> }

案例：将表名tb_emp1修改为emp1表名

alter table tb_emp1 rename to emp1;

案例：给hosts表添加字段

alter table hosts add column region varchar(100);

案例：修改hosts表中的name字段改为hostname

alter table hosts change column name hostname varchar(30);

案例：设置hosts表中的region字段的默认值为GZ

alter table hosts alter column region set default "GZ";

案例：修改hosts表中的hostid字段的数据类型为bigint unsigned

alter table hosts modify column hostid bigint unsigned;

案例：删除hosts表中的字段ipv6

alter table hosts drop column ipv6;

三、删除数据表

在删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。

1. 删除没有关系的表

案例：删除表emp1

mysql> drop table emp1;
Query OK, 0 rows affected (0.02 sec)

mysql>

2. 删除有关系的表

数据表之间经常存在外键关联的情况，这时如果直接删除父表，会破坏数据表的完整性，也会删除失败。

删除父表有以下两种方法：

先删除与它关联的子表，再删除父表；但是这样会同时删除两个表中的数据。
将关联表的外键约束取消，再删除父表；适用于需要保留子表的数据，只删除父表的情况。

下面介绍了如何取消关联表的外键约束并删除主表，也就是上面所说的删除父表的第二种方法。

在数据库中创建两个关联表。创建表 tb_emp4 的 SQL 语句如下：

CREATE TABLE tb_emp4
(
id INT(11) PRIMARY KEY,
name VARCHAR(22),
location VARCHAR (50)
);

接下来创建表 tb_emp5，SQL 语句如下：

CREATE TABLE tb_emp5
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp4_emp5 FOREIGN KEY (deptId) REFERENCES tb_emp4(id)
);

使用 SHOW CREATE TABLE 命令查看表 tb_ emp5 的外键约束

# 表tb_emp4
mysql> show create table tb_emp4\G;
*************************** 1. row ***************************
       Table: tb_emp4
Create Table: CREATE TABLE `tb_emp4` (
  `id` int NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

# 表tb_emp5
mysql> show create table tb_emp5\G;
*************************** 1. row ***************************
       Table: tb_emp5
Create Table: CREATE TABLE `tb_emp5` (
  `id` int NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp4_emp5` (`deptId`),
  CONSTRAINT `fk_emp4_emp5` FOREIGN KEY (`deptId`) REFERENCES `tb_emp4` (`id`) # 外键约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

“
由运行结果可以看出，tb_emp5 表为子表，具有名称为 fk_emp4_emp5 的外键约束；tb_emp4 为父表，其主键 id 被子表 tb_ emp5 所关联。
”

直接删除主表tb_emp4，肯定报错，因为还有下边还有子表tb_tmp5呢

mysql> drop table tb_emp4;
ERROR 3730 (HY000): Cannot drop table 'tb_emp4' referenced by a foreign key constraint 'fk_emp4_emp5' on table 'tb_emp5'.
mysql>

当主表在存在外键约束时，不能被直接删除。

下面解除子表 tb_emp5 的外键约束

# 先查看表tb_emp5的创建表的语句，从中可以看到约束信息
mysql> show create table tb_emp5\G;
*************************** 1. row ***************************
       Table: tb_emp5
Create Table: CREATE TABLE `tb_emp5` (
  `id` int NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp4_emp5` (`deptId`),
  CONSTRAINT `fk_emp4_emp5` FOREIGN KEY (`deptId`) REFERENCES `tb_emp4` (`id`) # 约束信息这里看
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 

# 搞清楚父子表关系后，现在开始删除子表tb_emp5的外键约束
mysql> alter table tb_emp5 drop foreign key fk_emp4_emp5;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

“
从约束信息看到有外键约束，约束名为fk_emp4_emp5，在本表将deptId字段作为外键，参考的表是tb_emp4的id字段，因此父表是tb_emp4，子表是tb_emp5
”

解除关联关系后，可以使用 DROP TABLE 语句直接删除父表 tb_emp4

mysql> drop table tb_emp4;
Query OK, 0 rows affected (0.01 sec)

mysql>

可以发现，删除成功

四、查看表结构

查看表结构

mysql> desc hosts;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| hostid   | int          | YES  |     | NULL    |       |
| hostname | varchar(30)  | YES  |     | NULL    |       |
| ipv4     | varchar(64)  | YES  |     | NULL    |       |
| ipv6     | varchar(128) | YES  |     | NULL    |       |
| region   | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql>

查看创建表的语句

mysql> show create table hosts;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hosts | CREATE TABLE `hosts` (
  `hostid` int DEFAULT NULL,
  `hostname` varchar(30) DEFAULT NULL,
  `ipv4` varchar(64) DEFAULT NULL,
  `ipv6` varchar(128) DEFAULT NULL,
  `region` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql 外键 mysql创建数据库 mysql创建表

文章转载自TtrOpsStack，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。