MySQL——数据表的基本操作

最新推荐文章于 2022-09-26 07:00:00 发布

KFC质检员

最新推荐文章于 2022-09-26 07:00:00 发布

阅读量321

点赞数 1

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45373345/article/details/110052899

版权

数据库专栏收录该内容

26 篇文章 0 订阅

订阅专栏

创建数据表

在创建完数据库之后，接下来就需要创建数据表。创建数据表是指在已经创建好的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性约束的过程。

1、创建表的语法形式

数据表属于数据库，在创建数据表之前，应该使用语句“USE <数据库名>” 指定操作是从哪个数据库中进行，如果没有选择数据库，会报错

语法： CREATE TABLE <表名> （字段名1，数据类型 [列级别约束条件] [默认值]，字段名2，数据类型 [列级别约束条件] [默认值]， … … ）：

例如：

mysql> create table tb_emp1
    -> (id int(11),
    -> name varchar(25),
    -> deptId INT(11),
    -> salary FLOAT);

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| tb_emp1          |
+------------------+

2、使用主键约束

主键约束要求主键列的数据唯一，并且不允许为空。
1）单字段主键

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

例如：

mysql> create table tb_emp2(
    -> id int(11) primary key,
    -> name varchar(25),
    -> deptID INT(11),
    -> salary FLOAT);
Query OK, 0 rows affected (0.00 sec)

mysql> desc tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptID | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2）在定义完所有列之后指定主键

语法： [CONSTRAINT <约束名>] PRIMARY KEY [字段名]

例如：

mysql> create table tb_emp3(
    -> id int(11),
    -> name varchar(25),
    -> deptid int(11),
    -> salary float,
    -> primary key(id));
Query OK, 0 rows affected (0.01 sec)

mysql> desc tb_emp3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptid | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3）多字段联合主键

语法：PRIMARY KEY [字段1，字段2，....]

例如：

mysql> create table tb_emp4(
    -> name varchar(25),
    -> deptid int(11),
    -> salary float,
    -> primary key(name,deptid));
Query OK, 0 rows affected (0.01 sec)

mysql> desc tb_emp4;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(25) | NO   | PRI | NULL    |       |
| deptid | int(11)     | NO   | PRI | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3、使用外键约束

外键用来在两个表数据之间建立连接，它可以是一列或者多列

语法：[CONSTRAINT<外键名>] FOREIGN KEY [字段名1，字段名2...] REFERENCES<主表名> 主键列1[主键列2...]

例如：

mysql> create table tb_dept1(
    -> id int(11) primary key,
    -> name varchar(22) not null,
    -> location varchar(50));
Query OK, 0 rows affected (0.01 sec)

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 (0.02 sec)

mysql> show create table tb_emp5;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                     |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_emp5 | CREATE TABLE `tb_emp5` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptid` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept1` (`deptid`),
  CONSTRAINT `fk_emp_dept1` FOREIGN KEY (`deptid`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4、使用非空约束

非空约束指字段的值不能为空。

语法：字段名 数据类型 not null

例如：

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 (0.01 sec)

5、唯一

语法：[CONSTRATIN <约束名>] UNIQUE （<字段名>）

例如：

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 (0.01 sec)

6、使用默认约束

默认约束指定某列的默认值。

语法：字段名 数据类型 DEFAULT 默认值

例如：

mysql> create table tb_emp7(
    -> id int(11) primary key,
    -> name varchar(25) not null,
    -> deptid int(11) default 1111,
    -> salary float,
    -> info varchar(50));
Query OK, 0 rows affected (0.02 sec)

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

语法：字段名 数据类型 AUTO_INCREMENT

例如：

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 (0.01 sec)

插入数据验证：

mysql> insert into tb_emp8(name,salary)
    -> values('zh',1000),('li',1000),('wang',1500);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看：

mysql> select * from tb_emp8;
+----+------+--------+--------+
| id | name | deptid | salary |
+----+------+--------+--------+
|  1 | zh   |   NULL |   1000 |
|  2 | li   |   NULL |   1000 |
|  3 | wang |   NULL |   1500 |
+----+------+--------+--------+
3 rows in set (0.00 sec)

查看数据表结构

查看表基本结构语句DESCRIBE

语法：DESCRIBE 表名； 或 DESC 表名；

例如：

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

或者

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

查看表详细结构语句

语法：SHOW CREATE TABLE <表名\G>

mysql> show create table tb_emp1\G
*************************** 1. row ***************************
       Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptid` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

修改数据表

修改表名

语法：ALTER TABLE <旧表名> RENAME [TO] <新表名>.

例如：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_dept1       |
| tb_dept3       |
| tb_emp1        |
| tb_emp2        |
| tb_emp3        |
| tb_emp4        |
| tb_emp5        |
| tb_emp6        |
| tb_emp7        |
| tb_emp8        |
+----------------+
10 rows in set (0.00 sec)

mysql> alter table tb_dept3 rename tb_deptment3;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_dept1       |
| tb_deptment3   |
| tb_emp1        |
| tb_emp2        |
| tb_emp3        |
| tb_emp4        |
| tb_emp5        |
| tb_emp6        |
| tb_emp7        |
| tb_emp8        |
+----------------+
10 rows in set (0.00 sec)

修改字段的数据类型

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

例如：

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

mysql> alter table tb_dept1 modify name varchar(30);
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(11)     | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改字段名

语法：ALTER TABLE <表名> CHANGE<旧字段名><新字段名> <新数据类型>

例如：

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

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(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| loc   | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

添加字段

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

例如：

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

mysql> alter table tb_dept1 add column1 varchar(12) not null;
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(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| loc     | varchar(50) | YES  |     | NULL    |       |
| column1 | varchar(12) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

或者

mysql> desc tb_dept1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| loc     | varchar(50) | YES  |     | NULL    |       |
| column1 | varchar(12) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table tb_dept1 add column2 int(11) first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| column2 | int(11)     | YES  |     | NULL    |       |
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| loc     | varchar(50) | YES  |     | NULL    |       |
| column1 | varchar(12) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

或者

mysql> desc tb_dept1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| column2 | int(11)     | YES  |     | NULL    |       |
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| loc     | varchar(50) | YES  |     | NULL    |       |
| column1 | varchar(12) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table tb_dept1 add column3 int(11) after name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| column2 | int(11)     | YES  |     | NULL    |       |
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| column3 | int(11)     | YES  |     | NULL    |       |
| loc     | varchar(50) | YES  |     | NULL    |       |
| column1 | varchar(12) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

删除字段

语法：ALTER TABLE <表名> DROP <字段名>

例如：

mysql> desc tb_dept1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| column2 | int(11)     | YES  |     | NULL    |       |
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| column3 | int(11)     | YES  |     | NULL    |       |
| loc     | varchar(50) | YES  |     | NULL    |       |
| column1 | varchar(12) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table tb_dept1 drop column2;
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(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| column3 | int(11)     | YES  |     | NULL    |       |
| loc     | varchar(50) | YES  |     | NULL    |       |
| column1 | varchar(12) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改字段的排列位置

语法：ALTER TABLE <表名> MODIFY <字段名> <数据类型> FIRST | AFTER <字段2> 例如：

mysql> desc tb_dept1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| column3 | int(11)     | YES  |     | NULL    |       |
| loc     | varchar(50) | YES  |     | NULL    |       |
| column1 | varchar(12) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table tb_dept1 modify column1 varchar(12) first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| column1 | varchar(12) | YES  |     | NULL    |       |
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| column3 | int(11)     | YES  |     | NULL    |       |
| loc     | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

或者

mysql> desc tb_dept1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| column1 | varchar(12) | YES  |     | NULL    |       |
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| column3 | int(11)     | YES  |     | NULL    |       |
| loc     | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table tb_dept1 modify column1 varchar(12) after loc;
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(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| column3 | int(11)     | YES  |     | NULL    |       |
| loc     | varchar(50) | YES  |     | NULL    |       |
| column1 | varchar(12) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

更改表的存储引擎

语法：ALTER TABLE <表名> ENGINE=<更改后的存储引擎>

例如：

mysql> show create table tb_deptment3\G
*************************** 1. row ***************************
       Table: tb_deptment3
Create Table: CREATE TABLE `tb_deptment3` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sth` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table tb_deptment3 engine=myisam;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb_deptment3\G
*************************** 1. row ***************************
       Table: tb_deptment3
Create Table: CREATE TABLE `tb_deptment3` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sth` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

删除表的外键约束

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

例如：

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 (0.01 sec)

mysql> show create table tb_emp9\G
*************************** 1. row ***************************
       Table: tb_emp9
Create Table: CREATE TABLE `tb_emp9` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptid` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept` (`deptid`),
  CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptid`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table tb_emp9 drop foreign key fk_emp_dept;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb_emp9\G
*************************** 1. row ***************************
       Table: tb_emp9
Create Table: CREATE TABLE `tb_emp9` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptid` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept` (`deptid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

删除数据表

删除没有被关联的表

语法：DROP TABLE [IF EXISTS]表1，表2...

例如：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_dept1       |
| tb_deptment3   |
| tb_emp1        |
| tb_emp2        |
| tb_emp3        |
| tb_emp4        |
| tb_emp5        |
| tb_emp6        |
| tb_emp7        |
| tb_emp8        |
| tb_emp9        |
+----------------+
11 rows in set (0.01 sec)

mysql> drop table if exists tb_dept2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_dept1       |
| tb_deptment3   |
| tb_emp1        |
| tb_emp2        |
| tb_emp3        |
| tb_emp4        |
| tb_emp5        |
| tb_emp6        |
| tb_emp7        |
| tb_emp8        |
| tb_emp9        |
+----------------+
11 rows in set (0.00 sec)

删除被其他表关联的主表

先创建表tb_dept2

mysql> create table tb_dept2(
    -> id int(11) primary key,
    -> name varchar(22),
    -> location varchar(50));
Query OK, 0 rows affected (0.01 sec)

创建表tb_emp

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 (0.01 sec)

直接删除父表tb_dept2

mysql> drop table tb_dept2;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

可以看到在外键约束时，主表不能直接删除。

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

解除关联子表tb_dept的外键约束

mysql> drop table tb_dept2;
Query OK, 0 rows affected (0.00 sec)

表就可以被删除

KFC质检员

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录