我在广州学 Mysql 系列之 数据“表”的基本操作

ℹ️大家好，我是😆练小杰，今天主要讲得是Mysql数据表的基本操作内容~~
昨天讲了“Mysql 数据“库“的基本操作”~~
想要了解更多🈶️MYSQL 数据库的命令行总结！！！

“真相永远只有一个”——工藤新一 😎

前言

⚠️温馨提醒：最后有详细实例操作，前面文字概念比较多，需要给点耐心才能看完哦！！！
同时，如果需要练习可以直接点击文章目录—>“综合案例”

创建数据表

• 创建数据表的语句为 create table

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

主键约束

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

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

• 多字段联合主键

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

外键约束

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

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

非空约束

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

字段名 数据类型 not null

唯一性约束

• 唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

字段名 数据类型 UNIQUE

默认约束

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

  字段名 数据类型 DEFAULT 默认值

设置表的属性值自动增加

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

字段名 数据类型 AUTO_INCREMENT

查看数据表结构

查看表基本结构语句describe

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

DESCRIBE <表名>
或者
DESC <表名>

表详细结构语句show create table

• 用来显示数据表的所有创建语句

 SHOW CREATE TABLE <表名\G>

修改数据表

修改表名

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

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

修改字段的数据类型

• 把字段的数据类型转换成另一种数据类型

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

修改字段名

• 修改表字段名的语法：

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

添加字段

• 在已有的表中添加新的字段

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

删除字段

• 将数据表中的某个字段从表中移除

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

修改字段的排列位置

• 改变表中字段的相对位置

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

更改表的存储引擎

存储引擎是指MySQL数据库中表的存储类型。可以根据自己的需要，选择不同的引擎。（昨天讲过“引擎内容”）

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

删除表的外键约束

• 外键一旦删除，就会解除主表和从表间的关联关系。

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

删除数据表

删除无关联的表

• 使用DROP TABLE可以一次删除一个或多个没有被其它表关联的数据表

 DROP TABLE [IF EXISTS]表1, 表2, . . . 表n;

删除被其它表关联的主表

• 在数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示失败。因为直接删除，将破坏表的对照完整性。
• 若必须要删除，可以先删除与之关联的子表，再删除父表，但是这样就同时删除了两个表中的数据。
• 有些情况下可能要保留子表，这时如要单独删除父表，只需将关联的表的外键约束条件取消，然后就可以删除父表。

综合案例(菜就多练)

创建员工表tb_emp1

• 首先创建数据库，SQL语句如下：

CREATE  DATABASE test_db;

• 选择创建表的数据库，SQL语句如下：

USE test_db;

• 创建tb_emp1表，SQL语句为：

CREATE TABLE tb_emp1
(
id      INT(11),    //员工编号
name   VARCHAR(25), //员工名字
deptId  INT(11),  //所属部门
salary  FLOAT  //工资);

语句执行完，就创建了一个名称为tb_emp1的数据表

• 使用SHOW TABLES； 查看数据表是否创建成功

SHOW TABLES;
+-----------------------+
| Tables_in_ test_db |
+----------------------+
| tb_emp1         |
+----------------------+
1 row in set (0.00 sec)

可以看到，test_db数据库中已经有了数据表tb_tmp1

建立数据表tb_emp2，设置主键为id

• SQL语句如下：

CREATE TABLE tb_emp2
(
id    	INT(11) PRIMARY KEY,
name  	VARCHAR(25),
deptId 	INT(11),
salary 	FLOAT
);

建立数据表tb_emp 3，其主键为id

• SQL语句如下：

CREATE TABLE tb_emp3 
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id)
);

上述两个例子结果一样，都会在id字段上设置主键约束

定义数据表tb_emp4

• 假设表中间没有主键id，为了唯一确定一个员工，可以把name、deptId 联合起来做为主键。
  SQL语句如下：

CREATE TABLE tb_emp4(
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(name,deptId)
);

定义数据表tb_emp5，且在tb_emp5表上创建外键约束

• 创建一个部门表tb_dept1，SQL语句如下：

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

• 定义数据表tb_emp5，让它的键deptId作为外键关联到tb_dept1的主键id，SQL语句为：

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)
);

以上语句都执行完后，在表tb_emp5上添加了名称为fk_emp_dept1的外键约束，外键名称为deptId，其依赖于表tb_dept1的主键id

定义数据表tb_emp6，指定员工的名称不能为空

SQL语句如下：

CREATE TABLE tb_emp6 
(
id     INT(11) PRIMARY KEY,
name   VARCHAR(25) NOT NULL,
deptId  INT(11), 
salary  FLOAT
);

定义数据表tb_dept2，指定部门的名称唯一

SQL语句如下：

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

定义数据表tb_dept3，指定部门的名称唯一（两种不同方式）

SQL语句如下：

CREATE TABLE tb_dept3 
(
id      INT(11) PRIMARY KEY,
name    VARCHAR(22),
location  VARCHAR(50),
CONSTRAINT STH UNIQUE(name)
);

定义数据表tb_emp7，指定员工的部门编号默认为1111

SQL语句如下：

CREATE TABLE tb_emp7 
(
id      INT(11) PRIMARY KEY,
name   VARCHAR(25) NOT NULL,
deptId  INT(11) DEFAULT 1111, 
salary  FLOAT
);

定义数据表tb_emp8，指定员工的编号自动递增

SQL语句如下：

CREATE TABLE tb_emp8 
(
id      INT(11) PRIMARY KEY AUTO_INCREMENT,
name   VARCHAR(25) NOT NULL,
deptId  INT(11), 
salary  FLOAT
);

• 举个🌰，执行如下插入语句：

INSERT INTO tb_emp8 (name,salary)
VALUES('Dzq',0001), ('Lxj',0002),('Enson',0003);

语句执行完后，tb_emp8表中增加3条记录，在这里并没有输入id的值，但系统已经自动添加该值。

• 使用SELECT命令查看记录，如下所示

SELECT * FROM tb_emp8;

分别使用DESCRIBE和DESC查看表tb_dept1和表tb_emp1的表结构

• 查看tb_dept1表结构
  SQL语句如下：

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 |      |
+-----------+---------------+------+-----+---------+--------+

• 查看tb_emp1表结构，
  SQL语句如下：

 DESC tb_emp1;
+--------+--------------+------+-----+---------+-------+
| Field  | Type      | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int (11)    | YES |    | NULL  |     |
| name  | varchar(25) | YES |    | NULL  |     |
| deptId | int (11)     | YES |    | NULL  |     |
| salary  | float      | YES |    | NULL  |     |
+---------+-------------+-------+-----+---------+-------+

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

 SHOW CREATE TABLE tb_emp1;
+--------+----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------+
| Table  | Create Table|
+--------+----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------+
| fruits | CREATE TABLE `fruits` (`f_id` char(10) NOT NULL,`s_id` int(11) NOT NULL,`f_name` char(255) NOT NULL,`f_price` decimal(8,2) NOT NULL,PRIMARY KEY (`f_id`),KEY `index_name` (`f_name`),KEY `index_id_price` (`f_id`,`f_price`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+--------+----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------+

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

 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=gb2312

将数据表tb_dept3改名为tb_deptment3

• 使用ALTER TABLE将表tb_dept3改名为tb_deptment3
  SQL语句如下：

ALTER TABLE tb_dept3 RENAME tb_deptment3;

将数据表tb_dept1中name字段的数据类型由VARCHAR(22)修改成VARCHAR(30)

• 执行修改表名操作之前，使用DESC查看tb_dept1表结构，结果如下：

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

可以看到现在name字段的数据类型为VARCHAR(22)

• 下面修改其类型，输入如下语句：

ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);

将数据表tb_dept1中的location字段名称改为loc，数据类型不变

SQL语句如下：

ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);

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

SQL语句如下：

ALTER TABLE tb_dept1 CHANGE loc location VARCHAR(60);

在数据表tb_dept1中添加一个没有完整性约束的INT类型的字段managerId（部门经理编号）

SQL语句如下：

ALTER TABLE tb_dept1 ADD managerId INT(10);

在数据表tb_dept1中添加一个不能为空的VARCHAR(12)类型的字段column1

SQL语句如下：

ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) not null;

数据表tb_dept1中添加一个INT类型的字段column2

SQL语句如下：

ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;

数据表tb_dept1中name列后添加一个INT类型的字段column3

SQL语句如下：

ALTER TABLE tb_dept1  ADD column3 INT(11) AFTER name;

删除数据表tb_dept1表中的column2字段

SQL语句如下：

ALTER TABLE tb_dept1 DROP column2;

将数据表tb_dept中的column1字段修改为表的第一个字段

SQL语句如下：

ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;

将数据表tb_dept1中的column1字段插入到location字段后面

SQL语句如下：

ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER location;

将数据表tb_deptment3的存储引擎修改为MyISAM

• 在修改存储引擎之前，先使用SHOW CREATE TABLE查看表tb_deptment3当前的存储引擎，如下所示：

  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=gb2312
1 row in set (0.00 sec)

可看到，表tb_deptment3当前的存储引擎为ENGINE=InnoDB

• 接下来修改存储引擎类型，输入如下SQL语句:

 ALTER TABLE tb_deptment3 ENGINE=MyISAM;

• 使用SHOW CREATE TABLE再次查看表tb_deptment3的存储引擎，发现表tb_dept的存储引擎变成了“MyISAM”，如下所示：

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=gb2312
1 row in set (0.00 sec)

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

• 首先创建表tb_emp9，创建外键deptId关联tb_dept1表的主键id，SQL语句如下：

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)
);

• 使用SHOW CREATE TABLE查看表tb_emp9的结构，如下：

 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=gb2312
1 row in set (0.00 sec)

可看到，已经成功添加了表的外键

• 下面删除外键约束，SQL语句如下：

ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;

执行完之后，会删除表tb_emp9的外键约束

• 使用SHOW CREATE TABLE再次查看表tb_emp9结构，结果如下：

 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=gb2312
1 row in set (0.00 sec)

删除数据表tb_dept2

SQL语句如下：

DROP TABLE IF EXISTS tb_dept2;

数据库中创建两个关联表

• 首先，创建表tb_dept2

SQL语句如下：

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

• 接下来创建表tb_emp

SQL语句如下：

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)
);

• 用SHOW CREATE TABLE命令查看表tb_emp的外键约束，如下：

 SHOW CREATE TABLE tb_emp\G
*************************** 1. row ***************************Table: tb_emp
Create Table: CREATE TABLE `tb_emp` (`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_dept2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

可看到，以上执行结果创建了两个关联表tb_dept2和表tb_emp，其中tb_emp表为子表，具有名称为fk_emp_dept的外键约束，tb_dept2为父表，其主键id被子表tb_emp所关联

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

• 首先直接删除父表tb_dept2，输入删除语句如下：

DROP TABLE tb_dept2;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

可看到，如前所述，在存在外键约束时，主表不能被直接删除。

• 接下来，解除关联子表tb_emp的外键约束，
  SQL语句如下：

ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept;

语句成功执行后，将取消表tb_emp和表tb_dept2之间的关联关系

• 这时，可以输入删除语句，将原来的父表tb_dept2删除，SQL语句如下：

DROP TABLE tb_dept2;

• 最后通过SHOW TABLES;查看数据表列表，如下所示：

 show tables;
+---------------------+
| Tables_in_test_db |
+---------------------+
| tb_dept         |
| tb_deptment3    |
.....省略部分表

可看到，数据表列表中已经不存在名称为tb_dept2的表

好了，快乐的时光总是过得很快，mysql数据表的基本操作内容到此结束，下周再见啦！！！
😆想要了解更多内容请点击“练小杰的CSDN”
ℹ️欢迎各位在评论区踊跃讨论，积极提出问题，解决困惑！！！