MySQL常用命令

学习资料来源：数据库原理与应用——MySQL从入门到实战

连接数据库

在操作数据库之前，需要连接它，输入命令：mysql -u用户名 -p密码 -h127.0.0.1

创建数据库

连接上MySQL之后就可以进行数据库的操作了，接下来我们创建一个名为TestDb的数据库：

命令：create database TestDb;

查看已存在的数据库

创建完数据库之后我们可以通过show databases;命令查看MySQL中已存在的数据库。

选择数据库

命令：USE 数据库名;

例如：use mysql;

删除数据库

在 MySQL 中删除数据库可以使用 DROP DATABASE 语句，这是一个不可逆的操作，会永久删除数据库及其所有数据。

基本语法

DROP DATABASE [IF EXISTS] 数据库名称;

使用说明

1. 基本删除命令：

   DROP DATABASE 数据库名;
   

2. 安全删除（推荐） - 先检查数据库是否存在：

   DROP DATABASE IF EXISTS 数据库名;
   

3. 示例：

   DROP DATABASE IF EXISTS my_test_db;
   

注意事项

1. 权限要求：执行此操作需要用户具有 DROP 权限

2. 不可恢复：删除操作会永久删除数据库及其所有表和数据

3. 连接限制：不能删除当前正在使用的数据库，需要先切换到其他数据库：

   USE another_database;
   DROP DATABASE database_to_drop;
   

4. 备份建议：执行前建议先备份重要数据：

   mysqldump -u 用户名 -p 数据库名 > 备份文件.sql
   

5. 查看数据库列表：删除前可以先确认数据库是否存在：

   SHOW DATABASES;
   

完整操作示例

-- 1. 查看所有数据库
SHOW DATABASES;
-- 2. 删除指定数据库（安全方式）
DROP DATABASE IF EXISTS old_database;
-- 3. 验证是否删除成功
SHOW DATABASES;

请谨慎使用此命令，特别是在生产环境中。

创建表

我们知道数据库就是存储数据的地方，那数据库中的数据到底存放在哪里呢？数据库中的数据是存放在一张一张的表中的。你可以想象数据库就像一个文件夹，而表你可以理解为一个excel表格，其实他们本来就挺像。

表的基本结构

CREATE TABLE 表名
(
 字段名,数据类型,
 字段名,数据类型,
 .....
);

例如创建一个名为t_user的表：

CREATE TABLE t_user
(
 id INT,
 username VARCHAR(32),
 password VARCHAR(32),
 phone VARCHAR(11)
);

表的数据类型

一、数值类型

1. 整数类型

2. 浮点数类型

3. 精确小数类型

二、字符串类型

1. 普通字符串

2. 文本类型

3. 二进制字符串

4. 二进制大对象(BLOB)

三、日期时间类型

四、其他特殊类型

五、布尔类型

注意：

1. 字符串类型的长度单位取决于字符集(如utf8mb4中1字符=4字节)
2. 数值类型的(M)表示显示宽度，不影响存储
3. 时间戳类型受时区影响，DATETIME则不受影响
4. 从MySQL 8.0开始，推荐使用utf8mb4字符集(完整支持Unicode)

查看表的基本结构

命令：DESCRIBE 表名;

简写：DESC 表名;

查看数据库中已创建的表

命令：show tables;

删除数据表

在 MySQL 中删除已存在的表可以使用 DROP TABLE 语句。以下是详细说明和操作步骤：

基本语法

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

常用删除方法

1. 删除单个表（安全方式）

DROP TABLE IF EXISTS 表名;

2. 删除多个表

DROP TABLE IF EXISTS 表1, 表2, 表3;

3. 强制删除（不检查是否存在）

DROP TABLE 表名;

完整操作示例

-- 1. 查看当前数据库中的所有表
SHOW TABLES;
-- 2. 安全删除表（推荐）
DROP TABLE IF EXISTS users, orders;
-- 3. 验证删除结果
SHOW TABLES;

注意事项

1. 权限要求：执行用户需要有该表的 DROP 权限

2. 数据丢失：表结构和所有数据将被永久删除

3. 外键约束：如果表被其他表的外键引用，直接删除会报错

4. 删除有外键约束的表：

   -- 先禁用外键检查
   SET FOREIGN_KEY_CHECKS = 0;
   -- 执行删除操作
   DROP TABLE IF EXISTS 表名;
   -- 重新启用外键检查
   SET FOREIGN_KEY_CHECKS = 1;
   

5. 备份建议：删除前建议备份表数据：

   -- 使用mysqldump备份单表
   mysqldump -u 用户名 -p 数据库名 表名 > 表备份.sql
   

6. 临时表：删除临时表需使用：

   DROP TEMPORARY TABLE IF EXISTS 临时表名;
   

删除表的最佳实践

1. 始终使用 IF EXISTS 避免错误
2. 重要数据表删除前先备份
3. 在事务中执行删除以便可以回滚
4. 考虑使用 RENAME TABLE (ALTER TABLE 旧表名 RENAME 新表名; )先重命名表作为临时措施

-- 安全删除表的完整示例
START TRANSACTION;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS old_data;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

主键约束

主键，是表中一列或者多列的组合，主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空，主键能够唯一的标识表中的每一条记录，可以结合外键来定义与不同数据表之间的关系。怎么理解主键呢？我们知道数据库中的表可以存放很多数据，如果把表中的每一个数据比作一个人的话，那么表的主键就可以看做是人的身份证。

在MySQL中，主键约束（PRIMARY KEY）用于确保表中每一行的唯一性。主键可以是单个列或多个列的组合，这些列的值组合在表中必须是唯一的，并且不允许包含NULL值。每个表只能有一个主键，通常主键列会有自动增长（AUTO_INCREMENT）属性，以便自动为新行生成唯一的序列号。

添加主键约束

有两种方式可以添加主键约束：
1.在定义列的同时指定主键；
2.在定义完字段之后再指定主键。

(1) 在定义列的同时指定主键：

(2) 在定义完所有列之后指定主键。

多字段联合主键

在开发中还有一种情况很常见，就是将几个字段联合在一起作为主键，那这个在代码中怎么实现呢？
举个例子，我们将员工的名字和部门ID联合起来作为主键：

CREATE TABLE t_emp2
(
 name VARCHAR(32),
 deptId INT,
 salary FLOAT,
 PRIMARY KEY(name,deptId)
);

`
语句执行后，会将name和deptId字段组合成为表t_emp2的多字段组合主键。

外键约束

在以后我们统一将外部关键字叫做外键，外键就是另一张表中的主键。

问：外键有啥用啊？
答：外键的主要作用就是保持数据的一致性，完整性。

再问：怎么保证的呢？
答：看图。

如图有两张表，classId 是T_Student的外键，是T_class 表的主键， 如果我们要删除T_class 表中classId为1的字段，程序是会报错的，因为t_student表中有数据和classId为1的字段关联了，是不能删除的，这样子就保证了数据的一致性和完整性。就相当于学生班级Id和班级绑定了，不能单独删除班级信息

继续问：那怎么才能删除呢？
接着答：需要先删除T__student表中classId为1的两个字段。

如何给表添加外部关键字
在MySQL中给表中字段添加外键约束的语法规则如下：

CONSTRAINT 外键名 FOREIGN KEY 字段名 REFERENCES 主表名(主键名)
举个例子：现在有两张表，t_emp、t_dept如下。

举个例子：现在有两张表，t_emp、t_dept如下。

t_dept表

t_emp表

我们来创建两张表，并给员工表(t_emp)添加外键：

CREATE TABLE t_dept
(
 deptId INT PRIMARY KEY,
 name VARCHAR(22),
 location VARCHAR(50)
);
CREATE TABLE t_emp
(
 id INT PRIMARY KEY,
 name VARCHAR(22),
 deptId INT,
 CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES t_dept(deptId)
);

实际操作如图：

添加常用约束

在数据库的使用过程中我们经常要限制字段的取值，比如有些字我们不能让它为空，我们就需要添加非空约束。

怎么添加唯一约束

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

定义部门表的部门名称唯一，SQL语句如下：关键词 UNIQUE。

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

怎么添加非空约束

关键词：NOT NULL；

例如：

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

怎么使用默认约束

默认约束：即给字段一个默认值。 关键词：DEFAULT；

例如：

CREATE TABLE t_emp(
 id INT PRIMARY KEY,
 name VARCHAR(22),
 sex VARCHAR(2) DEFAULT '男'
) DEFAULT CHARSET=utf8;

注意：

• 如果是添加字符串型默认值要使用单引号，如果是整型则不需要加任何符号；
• 如果要添加的是中文默认值，则需要加上DEFAULT CHARSET=utf8;使用英文字符则不需要。

设置表的属性值自动增加

在数据库应用中，经常有个需求就是，在每次插入新纪录时，系统自动生成字段的主键值，即：

关键词：AUTO_INCREMENT，默认情况下初始值和增量都为1。

例如：

CREATE TABLE t_tmp
(
 id int PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(32)
);

查看表结构与修改表名

查看口令：DESCRIBE

语法规则为:DESCRIBE 表名；

简写：DESC 表名;

大小写不敏感哟！

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

比如本岛主新建了一个名叫Products的表，结构如下：

现在来看看“现场”，用DESCRIBE查表的结构是这样的。

其中，该结构中各个字段的含义分别为：

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

查看数据表详细结构

什么？你发现表建错了，你想知道错在哪里了？ 那就用SHOW CREATE TABLE语句吧。

语法规则为：SHOW CREATE TABLE 表名; 。

还是刚才那个例子，让我们看看SHOW CREATE TABLE有什么能耐。

可以看到，使用 SHOW CREATE TABLE 语句，不仅仅可以返回给我们建表时所写的详细语句，而且还可以查看存储引擎和字符编码。

再告诉大家一个小诀窍，是不是觉得返回的结果排版有点乱。我们加上\G后效果就会有所改善哟，来看看！

修改表名

随着表越来越多，有时候你会后悔之前的数据表的名字根本没有认真斟酌过，想要重新命名一下，可以做到吗？

答案是：可以！MySQL是通过ALTER TABLE语句来修改表名的。

语法规则为：ALTER TABLE 旧表名 RENAME 新表名; 。

举个例子，我们先用SHOW TABLES看看我们的数据库中有什么表吧。

可以看到，我们目前已经有了两张表，分别叫Products和tb_emp。

接下来，我们使用ALTER TABLE为Products改名。

改名成功！Products成功变成了Mall_products。

修改字段名与字段数据类型

修改字段名

有时，在我们建好一张表后会突然发现，哎呀！字段名貌似写错了！怎么办？要删了表再重新建一个新表吗？还是要删了这个字段再新建一个新的字段？ 都不用，MySQL 中修改字段名称也有专门的语句，接近于自然语言，很好记忆。

语法规则为： ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型; 。

举个例子： 现在我们要把表Mall_products中的字段prod_id改名为ID。表结构如下：

输入命令：ALTER TABLE Mall_products CHANGE prod_id ID varchar(20);。执行过程如下所示。简单吧！

小提示： 如果不需要修改字段的数据类型，可以把新字段的数据类型设置为和原来一样，但是！千万不要空着它！

修改字段数据类型

随着存储的数据越来越多，数据库新人可能会发现，哎呀，当初预留的数据类型已经不能满足要求了，或者当初预留的字符太少了，怎么办？怎么修改？规则来了。

语法规则为： ALTER TABLE 表名 MODIFY 字段名 数据类型; 。

举个例子： 现在我们要把表Mall_products中的字段ID的数据类型改为varchar(30)。表结构如下：

输入命令：ALTER TABLE Mall_products MODIFY ID varchar(30);。执行过程如下所示。

ID的数据类型varchar(20)成功变成了varchar(30)。

添加与删除字段

添加字段

因为甲方的业务需求是不停变化的，所以在数据库操作中，添加字段可是常有的事。一个完整的字段包括：字段名、数据类型和完整性约束。

语法规则为： ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件] [FIRST|AFTER] 已存在字段名; 。

以下是在 MySQL 中常用的约束。

NOT NULL 约束：确保某列不能有 NULL 值。

DEFAULT 约束：当某列没有指定值时，为该列提供默认值。

UNIQUE 约束：确保某列中的所有值是不同的。

PRIMARY Key 约束：唯一标识数据库表中的各行/记录。

CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

在表的最后一列添加字段

只要不做[FIRST|AFTER]的位置说明，在添加字段时MySQL会默认把新字段加入到表的最后一列。

举个例子： 现在我们要把字段prod_country添加到表Mall_products的最后一列。表结构如下：

输入命令： ALTER TABLE Mall_products ADD prod_country varchar(30); 执行结果如下所示：

在表的第一列添加字段

如果我们想在第一列添加新的字段，只需做FIRST的位置说明。

举个例子： 现在我们要把字段prod_country添加到表Mall_products的第一列。

输入命令： ALTER TABLE Mall_products ADD prod_country varchar(30) FIRST; 执行结果如下所示：

在表的指定列后添加字段

如果我们想在某一列后面添加新的字段，只需做AFTER的位置说明，然后注明你想让它添加在哪个字段的后面即可。

举个例子： 现在我们要把字段prod_country添加到表Mall_products的 prod_name字段的后面。

输入命令： ALTER TABLE Mall_products ADD prod_country varchar(30) AFTER prod_name; 执行结果如下所示：

总之，想要添加新的字段，记住语法规则就能记住三种位置的添加方式。

删除字段

有添加的需求就会有删除的需求。删除一个字段就是将数据表中的某个字段从表中移除。

语法规则为： ALTER TABLE 表名 DROP 字段名; 。

举个例子： 现在我们要把字段prod_price从表Mall_products中删除。表结构如上图结果所示。

输入命令： ALTER TABLE Mall_products DROP prod_price; 执行结果如下所示：

字段prod_price成功删除！

修改字段的排列位置

在一个数据表被创建的时候，其字段的排列顺序就已被确定了。不能变了吗？怎么可能，一锤子买卖没人敢做。

我们可以通过ALTER TABLE来改变表中字段的相对位置。

语法规则为： ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST|AFTER 字段2; 。

其中，字段1指要修改位置的字段，FIRST与AFTER 字段2为可选参数。

修改字段为表的第一个字段

如果我们想把字段的位置调整到第一列，只需做FIRST的位置说明。

举个例子 现在我们要把字段prod_price调整到表Mall_products的第一列。Mall_products表结构如下所示：

输入命令： ALTER TABLE Mall_products MODIFY prod_price FLOAT FIRST;

执行过程如下所示：

修改字段到表的指定列之后

还有一种位置调整的方法可以让你把想调整的字段放在除了第一列的任何位置。调整的时候需要做AFTER 字段2的位置说明。

举个例子 现在我们要把字段prod_price调整到字段prod_country的后面。Mall_products表结构如下所示：

输入命令： ALTER TABLE Mall_products MODIFY prod_price FLOAT AFTER prod_country;

执行过程如下所示：

调整成功！

删除表的外键约束

建立了外键我们就建立起了两张表的关联关系，那如果我想删除主表呢？为了确保数据库的正确性，我们必须先解除两个表之间的关联关系，那就是删除外键约束啦！让我们先来看看删除外键约束的语法规则。

语法规则为： ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名; 。

举个例子： 我们先建立了一个主表country，表结构如下图所示：

然后建立一个有外键约束的子表Mall_products2，让它的键country_id作为外键关联到country的主键id。SQL语句如下：

CREATE TABLE Mall_products2
(
id INT(11) PRIMARY KEY,
country_name VARCHAR(20) NOT NULL,
country_id INT(11) NOT NULL,
CONSTRAINT prod_country FOREIGN KEY(country_id) REFERENCES country(id)
);

上述语句成功执行后，在表Mall_products2上添加了名称为prod_country的外键约束，外键名称为prod_country，依赖于表country的主键id，从下图中可以看到，已经成功添加了表的外键：

Mall_products2表结构如下图所示：

下面开始删除外键约束，语句如下： ALTER TABLE Mall_products2 DROP FOREIGN KEY prod_country;

使用SHOW CREATE TABLE查看表 Mall_products2 的结构，结果如下：

我们可以看到，FOREIGN KEY不见了，外键约束删除成功！

插入数据

为表的所有字段插入数据

向表中插入数据最简单的方法就是使用INSERT语句。INSERT语句需要你声明要插入内容的表(table)名和内容(values)。

语法规则：INSERT INTO 表名 (字段名) VALUES (内容);

举个例子：

假设我们现在有一张空表MyUser如下：

接下来，我们要往空表中插入一条内容，插入(INSERT)语句为：

insert into MyUser(name,age) values('zhnagsan',18);

结果为：

插入过程如下：

我们检索到了一条数据的结果，一条完整的内容已经被填入了空表中。

分析：

在插入数据时，我们指定了所有的字段名，这样的好处是：即使我们打乱了原本字段的排列顺序，只要插入的数据与之匹配，插入都不会出错。其实，还有一种偷懒的办法，就是我们可以不填字段名，但是这样的话，就必须老老实实的按字段顺序来填入相应的数据。

为表的指定字段插入数据

有的时候，我们并不想往某个字段内插入完完整整的值，或者说，有的时候我们并没有完整的数据可以整整齐齐插入某个字段。这时候就需要我们只为指定好的字段插入数据了，其他的我们都不用管。换句话说，就是： 为表的指定字段插入数据，就是在INSERT中只向部分插入值，而其他字段的值为表定义时的默认值。

举个例子

假设我们现在有一张空表 MyUser 如下：

我们忽略字段 age，为表 MyUser 插入内容:

insert into MyUser(name) values('lisi'),('fawaikuangtu'),('zhangsan');

结果为：

插入过程如下：

可以看到，由于我们建表时给字段 age 设置的默认值为 Null，所以插入数据时忽略该字段，就会默认给它赋值为空。

更新数据

通过上一关的学习，我们已经掌握了INSERT操作！同样，UPDATE操作也很简单，只要记住UPDATE语句三要素就能轻松掌握，它们分别是:

• 需要更新的表（table）名；
• 需要更新的字段（column）名和它的新内容（value）；
• 决定更新哪一条内容（value）的过滤条件。

语法规则为： UPDATE 表名 SET 字段名1 = 内容1, 字段名2 = 内容2, 字段名3 = 内容3 WHERE 过滤条件; 。

举个例子 我们现在有一张表Mall_products2，内容如下图所示：

现在我们想把Span换成Pakistan，当然，地区代码也要换为92 。

更新(UPDATE)语句为：

UPDATE Mall_products2SET country_name = "Pakistan", country_id = 92WHERE id = 2;

结果为：

插入过程如下：

删除数据

删除表中的指定行

从数据表中删除数据内容需要使用DELETE语句，它需要WHERE语句来配合它来指定我们究竟应该删除哪些数据内容。

语法规则为： DELETE FROM 表名 WHERE 条件语句; 。

我们可以指定删除某一行的数据内容，当然，我们还可以指定删除很多行的数据内容，区别就在于条件语句。那么在接下来的例子里，我们来看看很多行内容是怎么删除的。

举个例子 我们现在有一张表Mall_products2，内容如下图所示：

现在我们想把包含Span和Italy的这两行数据内容同时删除，删除（DELETE）语句为：

 DELETE FROM Mall_products2 WHERE id=2 OR id=3;

结果为：

删除过程如下：

聪明如你，删除多行内容的例子都看懂了，我想删除某一行的数据内容对你们来说简直易如反掌！

删除表中的所有行

删除表中的所有行就更简单了。如果需要删除表中所有的行，只需要省略WHERE语句即可。

语法规则为： DELETE FROM 表名; 。

举个例子 我们现在有一张表Mall_products2，内容如下图所示：

现在我们想把表Mall_products2的所有数据内容同时删除，删除（DELETE）语句为：

 DELETE FROM Mall_products2;

结果为：

删除过程如下：

小提示： TRUNCATE TABLE 语句也可以用来删除表中的所有记录。但是与 DELETE 不同的是，TRUNCATE TABLE 语句直接删除的是表，而不是表中的内容，删除结束后还会重新创建一个表。所以它的执行速度会比 DELETE 语句快。 语法为：TRUNCATE TABLE 表名;