MySQL 基础篇（二）：数据库和数据表的基本操作

目录本文内容概要一、数据库的基本操作1.1 创建数据库1.2 数据库字符集和校验集1.2.1 查看 MySQL 支持的字符集和校验集查看字符集查看校验集1.3 删除数据库1.4 查看数据库1.4.1 查看当前 MySQL Server 有哪些数据库1.4.2 查看数据库的字符集和校验集1.4.3 切换/选择数据库1.4.4 查看当前所处的数据库1.5 修改数据库1.6 数据库的备份和恢复1.7 查看数据库的连接情况二、数据表的基本操作2.1 创建数据表2.2 删除数据表2.3 查看数据表2.3.1 查看当前数据库中的所有数据表2.3.2 查看数据表结构2.3.3 查看建表 SQL2.3.4 查看表的详细状态2.4 修改数据表2.4.1 向数据表中添加字段2.4.2 删除字段2.4.3 修改字段类型/属性2.4.4 修改字段名和类型2.4.5 修改表名本文内容概要本文主要介绍 MySQL 中数据库与数据表的基本操作。通过本文的学习需要掌握数据库和数据表的创建、删除、查看与修改操作理解 MySQL 中字符集、校验规则以及存储引擎的基本概念与相关操作掌握数据表字段的添加、删除、类型修改以及表名修改等操作为后续学习 MySQL 中的数据增删查改以及 SQL 查询打下基础。一、数据库的基本操作1.1 创建数据库SQL 语句Linux 文件系统创建数据库create database db_name;在 /var/lib/mysql 创建一个目录文件如果创建已经存在的同名数据库MySQL 会直接拦截此操作不让用户执行。对于上述情况我们创建数据库时可以带上 if not exists 选项如果数据库不存在则创建如果数据库存在MySQL 不会报错但依旧不会创建它。1.2 数据库字符集和校验集数据库字符集数据库存储数据的格式数据库校验集数据库读取数据的格式对于任何一个数据库它都有对应的字符集和校验集。进入/var/lib/mysql路径下对应的数据库目录即可查看该数据库的字符集和校验集。在 MySQL 客户端中也可以查看数据库的字符集和校验集。1.2.1 查看 MySQL 支持的字符集和校验集查看字符集查看校验集创建数据库时指定字符集和校验集在 1.1 小节中我们创建数据库时并没有指定字符集和校验集为什么创建好的数据库会自动带上字符集和校验集呢那是因为在 MySQL 配置文件/etc/my.cnf中配置了创建数据库默认采用的字符集和校验集。对于没有指定校验集MySQL 会自动采用该字符集对应的校验集。1.3 删除数据库SQL 语句Linux 文件系统删除数据库drop database db_name;在 /var/lib/mysql 删除一个目录文件1.4 查看数据库1.4.1 查看当前 MySQL Server 有哪些数据库mysql show databases; -------------------- | Database | -------------------- | information_schema | | mysql | | performance_schema | | sys | | test | -------------------- 5 rows in set (0.00 sec)1.4.2 查看数据库的字符集和校验集mysql show create database test; ------------------------------------------------------------------------- | Database | Create Database | ------------------------------------------------------------------------- | test | CREATE DATABASE test /*!40100 DEFAULT CHARACTER SET utf8 */ | ------------------------------------------------------------------------- 1 row in set (0.00 sec)1.4.3 切换/选择数据库mysql use test; Database changed1.4.4 查看当前所处的数据库mysql select database(); ------------ | database() | ------------ | test | ------------ 1 row in set (0.00 sec)1.5 修改数据库修改数据库的字符集和校验集1.6 数据库的备份和恢复mysqldump -P 3306 -u root -p 密码 -B 数据库名 数据库备份存储的文件路径;source 数据库文件路径;备份数据库cat mytest.sql -- MySQL dump 10.13 Distrib 5.7.44, for Linux (x86_64) -- -- Host: localhost Database: test -- ------------------------------------------------------ -- Server version 5.7.44 /*!40101 SET OLD_CHARACTER_SET_CLIENTCHARACTER_SET_CLIENT /; /!40101 SET OLD_CHARACTER_SET_RESULTSCHARACTER_SET_RESULTS /; /!40101 SET OLD_COLLATION_CONNECTIONCOLLATION_CONNECTION /; /!40101 SET NAMES utf8 /; /!40103 SET OLD_TIME_ZONETIME_ZONE /; /!40103 SET TIME_ZONE00:00 /; /!40014 SET OLD_UNIQUE_CHECKSUNIQUE_CHECKS, UNIQUE_CHECKS0 /; /!40014 SET OLD_FOREIGN_KEY_CHECKSFOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS0 /; /!40101 SET OLD_SQL_MODESQL_MODE, SQL_MODENO_AUTO_VALUE_ON_ZERO /; /!40111 SET OLD_SQL_NOTESSQL_NOTES, SQL_NOTES0 */; -- -- Current Database: test CREATE DATABASE /!32312 IF NOT EXISTS/ test /*!40100 DEFAULT CHARACTER SET gbk */; USE test; /*!40103 SET TIME_ZONEOLD_TIME_ZONE */; /*!40101 SET SQL_MODEOLD_SQL_MODE /; /!40014 SET FOREIGN_KEY_CHECKSOLD_FOREIGN_KEY_CHECKS /; /!40014 SET UNIQUE_CHECKSOLD_UNIQUE_CHECKS /; /!40101 SET CHARACTER_SET_CLIENTOLD_CHARACTER_SET_CLIENT /; /!40101 SET CHARACTER_SET_RESULTSOLD_CHARACTER_SET_RESULTS /; /!40101 SET COLLATION_CONNECTIONOLD_COLLATION_CONNECTION /; /!40111 SET SQL_NOTESOLD_SQL_NOTES */; -- Dump completed on 2026-09-30 15:21:23mytest.sql 文件记录着对数据库的所有有效的历史操作当还原数据库时MySQL 服务端会将这些 SQL 语句重新执行一遍完成数据库的还原。还原数据库补充如果备份的不是整个数据库而是数据库的一张表mysqldump -u root -p 数据库名 表名1 表名2 数据库备份存储的文件路径;同时备份多个数据库mysqldump -u root -p -B 数据库名1 数据库名2 ... 数据库存放路径;如果备份一个数据库时没有带上 -B 选项在恢复数据库时需要先创建空数据库然后进入该数据库再使用 source 还原。1.7 查看数据库的连接情况mysql show processlist; ---------------------------------------------------------------------- | Id | User | Host | db | Command | Time | State | Info | ---------------------------------------------------------------------- | 12 | root | localhost | test | Query | 0 | starting | show processlist | ---------------------------------------------------------------------- 1 row in set (0.00 sec)作用查看当前 MySQL 有哪些用户连接。如果查出某些用户不是合法用户那么说明数据库被其他人入侵了。对于使用 MySQL 数据库出现卡顿、缓慢时也可以通过该指令查看是否是因为数据库连接情况而导致的。二、数据表的基本操作2.1 创建数据表create table 表名称 (字段 类型, 字段 类型) [charset字符集 collate校验集 engine存储引擎];mysql create table person(name char(20), age int); Query OK, 0 rows affected (0.02 sec) mysql show tables; ---------------- | Tables_in_test | ---------------- | person | ---------------- 1 row in set (0.00 sec)在创建数据表时可以指定数据表的字符集、校验集、存储引擎。如果不指定字符集和校验集数据表的字符集和校验集会继承数据库的字符集和校验集。对于存储引擎它会采用 MySQL 数据库配置中的存储引擎。查看 MySQL 支持的存储引擎不同的存储引擎在 Linux 文件系统中的差异mysql create table test_InnoDB(id int) engineInnoDB; Query OK, 0 rows affected (0.01 sec) mysql create table test_MyISAM(id int) engineMyISAM; Query OK, 0 rows affected (0.00 sec) [rootiZ2zebg189x52z3hbfc0t4Z mysql]# ls test/ db.opt person.ibd test_InnoDB.ibd test_MyISAM.MYD person.frm test_InnoDB.frm test_MyISAM.frm test_MyISAM.MYI创建数据表的本质就是在 Linux 文件系统中创建普通文件。不同存储的数据表它们创建普通文件的数量可能不同。2.2 删除数据表drop table table_name;mysql show tables; ---------------- | Tables_in_test | ---------------- | person | | test_InnoDB | | test_MyISAM | ---------------- 3 rows in set (0.01 sec) mysql drop table test_InnoDB; Query OK, 0 rows affected (0.01 sec) mysql drop table test_MyISAM; Query OK, 0 rows affected (0.00 sec) mysql show tables; ---------------- | Tables_in_test | ---------------- | person | ---------------- 1 row in set (0.00 sec)删除数据表本质是在 Linux 文件系统中删除普通文件[rootiZ2zebg189x52z3hbfc0t4Z mysql]# ls test/ db.opt person.frm person.ibd2.3 查看数据表2.3.1 查看当前数据库中的所有数据表mysql show tables; ---------------- | Tables_in_test | ---------------- | person | ---------------- 1 row in set (0.00 sec)2.3.2 查看数据表结构mysql desc person; -------------------------------------------- | Field | Type | Null | Key | Default | Extra | -------------------------------------------- | name | char(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | -------------------------------------------- 2 rows in set (0.01 sec)2.3.3 查看建表 SQLmysql show create table person; ---------------------------------------------------------------------------------------------------------------------------------- | Table | Create Table | ---------------------------------------------------------------------------------------------------------------------------------- | person | CREATE TABLE person ( name char(20) DEFAULT NULL, age int(11) DEFAULT NULL ) ENGINEInnoDB DEFAULT CHARSETgbk | ---------------------------------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec) mysql show create table person \G; *************************** 1. row *************************** Table: person Create Table: CREATE TABLE person ( name char(20) DEFAULT NULL, age int(11) DEFAULT NULL ) ENGINEInnoDB DEFAULT CHARSETgbk 1 row in set (0.00 sec)2.3.4 查看表的详细状态mysql show table status like person; ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | person | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2026-09-30 15:40:58 | NULL | NULL | gbk_chinese_ci | NULL | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec)2.4 修改数据表alter table 表名 操作;2.4.1 向数据表中添加字段mysql desc person; -------------------------------------------- | Field | Type | Null | Key | Default | Extra | -------------------------------------------- | name | char(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | -------------------------------------------- 2 rows in set (0.02 sec) mysql alter table person add id int after age; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql desc person; -------------------------------------------- | Field | Type | Null | Key | Default | Extra | -------------------------------------------- | name | char(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | -------------------------------------------- 3 rows in set (0.00 sec)2.4.2 删除字段mysql desc person; -------------------------------------------- | Field | Type | Null | Key | Default | Extra | -------------------------------------------- | name | char(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | -------------------------------------------- 3 rows in set (0.00 sec) mysql alter table person drop id; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql desc person; -------------------------------------------- | Field | Type | Null | Key | Default | Extra | -------------------------------------------- | name | char(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | -------------------------------------------- 2 rows in set (0.00 sec)2.4.3 修改字段类型/属性mysql desc person; -------------------------------------------- | Field | Type | Null | Key | Default | Extra | -------------------------------------------- | name | char(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | -------------------------------------------- 2 rows in set (0.01 sec) mysql alter table person modify age varchar(40); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql desc person; ----------------------------------------------- | Field | Type | Null | Key | Default | Extra | ----------------------------------------------- | name | char(20) | YES | | NULL | | | age | varchar(40) | YES | | NULL | | ----------------------------------------------- 2 rows in set (0.01 sec)2.4.4 修改字段名和类型mysql desc person; ----------------------------------------------- | Field | Type | Null | Key | Default | Extra | ----------------------------------------------- | name | char(20) | YES | | NULL | | | age | varchar(40) | YES | | NULL | | ----------------------------------------------- 2 rows in set (0.00 sec) mysql alter table person change age id int; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql desc person; -------------------------------------------- | Field | Type | Null | Key | Default | Extra | -------------------------------------------- | name | char(20) | YES | | NULL | | | id | int(11) | YES | | NULL | | -------------------------------------------- 2 rows in set (0.00 sec)2.4.5 修改表名mysql show tables; ---------------- | Tables_in_test | ---------------- | person | ---------------- 1 row in set (0.00 sec) mysql alter table person rename to employee; Query OK, 0 rows affected (0.01 sec) mysql show tables; ---------------- | Tables_in_test | ---------------- | employee | ---------------- 1 row in set (0.00 sec)