【MySQL】003. MySQL操作库

_孙同学

发布于 2025-04-17 13:04:55

16200

代码可运行

文章被收录于专栏：技术分享技术分享

运行总次数：0

代码可运行

库的操作

1. 创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, 
create_specification] ...]
create_specification:
 [DEFAULT] CHARACTER SET charset_name
 [DEFAULT] COLLATE collation_name

说明：

大写的表示关键字
[] 是可选项
CHARACTER SET: 指定数据库采用的字符集
COLLATE: 指定数据库字符集的校验规则

💦创建数据库的本质就是在/var/lib/mysql/路径下创建一个目录删除数据库

drop database db_name

💦删除数据库的本质就是删除/var/lib/mysql/路径下的一个目录注意：当我们查看数据库的时候用的是databases,而我们创建数据库的时候用的是database。

2. 创建数据库案例

创建名为 database1 的数据库

create database db1;

说明：当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则是：utf8_ general_ ci

创建数据库的时候有两个编码集：1.数据库编码集–数据库未来存储数据 2.数据库校验集–支持数据库进行字段比较使用的编码，本质是读取数据库中数据采用的编码格式数据库无论对数据进行任何操作，都必须保证数据操作和编码必须是编码一致的。

创建一个使用utf8字符集的 db2 数据库

create database db2 charset=utf8;
create database db2 character set utf8;

创建一个使用utf字符集，并带校对规则的 db3 数据库。

create database db3 charset=utf8 collate utf8_general_ci;

3. 字符集和校验规则

3.1 查看系统默认字符集以及校验规则

show variables like 'character_set_database';
show variables like 'collation_database';

3.2 查看数据库支持的字符集

show charset;

字符集主要是控制用什么语言。比如utf8就可以使用中文。

3.3 查看数据库支持的字符集校验规则

show collation;

我们为什么要对数据库进行编码配置呢？答案是我们的数据库未来是要存表的，这些表的编码规则会继承它所在的数据库的编码规则

3.4 校验规则对数据库的影响

不区分大小写创建一个数据库test1，校验规则使用utf8mb4_ general_ ci[不区分大小写]

create database test1 collate utf8mb4_general_ci;

use test1;//我们要使用数据库，先得use一下，就相当于我们要用哪个数据库

create table person(name varchar(20));//建表

insert into person values('a');
insert into person values('A');
insert into person values('b');
insert into person values('B');
insert into person values('c');
insert into person values('D');

查看数据库里有哪些表

 show tables;

表结构：

进行查询：

select * from person

select 表示从person表中去查，*通配符，表示把这个表里面的全部数据显示出来 💦：插入的时候本质上是在存数据，使用的是我们存储时的编码格式，查找的时候是按照我们的要求去查找的，采用的就是编码的校验规则。

加上匹配条件：

select * from person where name='a';

我们查的时候用utf8mb4_ general_ ci方式把我们要查的和表里的数据进行比较。

不区分大小写的查询以及结果：

说明utf8mb4_ general_ ci不区分大小写。

区分大小写创建一个数据库test2，校验规则使用utf8mb4_ bin[区分大小写]

create database test2 collate utf8mb4_bin;

use test2;//我们要使用数据库，先得use一下，就相当于我们要用哪个数据库

create table person(name varchar(20));

insert into person (name) values('a');
insert into person (name) values('A');
insert into person (name) values('b');
insert into person (name) values('B');
insert into person (name) values('c');
insert into person (name) values('D');

区分大小写的查询以及结果：

不区分大小写的排序以及结果：

区分大小写的排序以及结果：

查看编码规则和校验规则

SELECT schema_name, default_character_set_name, default_collation_name 
FROM information_schema.SCHEMATA 
WHERE schema_name = '你的数据库名';

这会直接返回数据库的字符集和排序规则。

4. 操纵数据库

4.1 查看数据库

show databases;

select database();//查当前在哪个数据库下

4.2 显示创建语句

show create database 数据库名；

示例：

+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                 |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------------------+

说明：

MySQL 建议我们关键字使用大写，但是不是必须的。
数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字
/*!40100 default… */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话

4.3 删除数据库

语法：

DROP DATABASE [IF EXISTS] db_ name;

执行删除之后的结果:

数据库内部看不到对应的数据库
对应的数据库文件夹被删除，级联删除，里面的数据表全部被删

注意：不要随意删除数据库

4.4 修改数据库

语法：

ALTER DATABASE db_name
[alter_spacification [,alter_spacification]...]
alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

说明：

对数据库的修改主要指的是修改数据库的字符集，校验规则实例：将 test2 数据库字符集改成 gbk

alter database test2 charset=gbk collate gbk_chinese_ci;

5. 备份和恢复

5.1 备份

语法：

# mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径

示例：将test1库备份到文件

sp@hcss-ecs-eaf1:~/linux.practice/MySQL$ mysqldump -P3306 -uroot -p -B test1 > test1.sql

这时，可以打开看看 test1.sql 文件里的内容，其实把我们整个创建数据库，建表，导入数据的语句都装载这个文件中。

5.2 还原

我们将test1删掉后进行还原

source /home/sp/linux.practice/MySQL/test1.sql

将test1.sql全部跑一次

5.3 注意事项

如果备份的不是整个数据库，而是其中的一张表，怎么做？

# mysqldump -u root -p 数据库名 表名1 表名2 > D:/test.sql

同时备份多个数据库

# mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径

如果备份一个数据库时，没有带上-B参数，在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source来还原。

6. 查看连接情况

语法：

show processlist;

示例：

可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是你正常登陆的，很有可能你的数据库被人入侵了。以后大家发现自己数据库比较慢时，可以用这个指令来查看数据库连接情况。

表的操作

1. 创建表

语法：

CREATE TABLE table_name (
 field1 datatype,
 field2 datatype,
 field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

说明：

field 表示列名
datatype 表示列的类型
character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准
collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准

2. 创建表案例

create table if not exists user1(
    id int,
    name varchar(20) comment '用户名',
    password char(32) comment '用户的密码',
    birthday date comment '用户的生日'
    )character set utf8 collate utf8_general_ci engine MyIsam;

create table if not exists user2(
    id int,
    name varchar(20) comment '用户名',
    password char(32) comment '用户的密码',
    birthday date comment '用户的生日'
    )charset=utf8 collate=utf8_general_ci engine=InnoDB;

说明：

不同的存储引擎，创建表的文件不一样。 users1 表存储引擎是 MyISAM ，在数据目中有三个不同的文件，分别是： users.sdi：表结构 users.MYD：表数据 users.MYI：表索引

3. 查看表结构

desc 表名; //查看表的详细信息

查看创建表时候的详细信息：

show create table user1 \G;

\G的作用是让其格式化输出。

4. 修改表

在项目实际开发中，经常修改某个表的结构，比如字段名字，字段大小，字段类型，表的字符集类型，表的存储引擎等等。我们还有需求，添加字段，删除字段等等。这时我们就需要修改表。

ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column 
datatype]...);
ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column 
datatype]...);
ALTER TABLE tablename DROP (column);

案例：

修改表名将user1修改为user 本质是修改/var/lib/mysql/user_db下的文件名，相当于move

mysql> alter table user1 rename to user;

to： 可以省掉

修改列名称

mysql> alter table employee change name xingming varchar(60); --新字段需要完整
定义

🎯注意： 更改列名称的时候，不仅仅要提供新的列名称，也要提供这个列的相关属性。相当于把这一列重新设置。

在users表添加一个字段，用于保存图片路径

mysql> alter table user add image_path varchar(128)  comment '这个是用户的头像路径' after birthday;

插入新字段后，对原来表中的数据没有影响：

修改name，将其长度改成60

mysql> alter table users modify name varchar(60);

删除password列

注意：删除字段一定要小心，删除字段及其对应的列数据都没了

mysql> alter table users drop password;

5. 删除表

基本语法

DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name2, ...] [RESTRICT | CASCADE];

参数解释

TEMPORARY
- 可选参数，表示删除的是临时表（仅对当前会话有效）。
- 例如：DROP TEMPORARY TABLE temp_table;
IF EXISTS
- 可选参数，避免表不存在时报错（推荐使用）。
- 例如：DROP TABLE IF EXISTS users;
table_name
- 要删除的表名，可同时删除多个表（逗号分隔）。
- 例如：DROP TABLE table1, table2;
RESTRICT | CASCADE
- 默认行为是 RESTRICT（如果表被其他对象引用，拒绝删除）。
- CASCADE 会级联删除依赖对象（如外键关联的表），但 MySQL 不支持此选项（语法保留，实际无效）。

示例

删除单个表

-- 安全删除（推荐）
DROP TABLE IF EXISTS employees;

删除多个表

-- 同时删除多个表
DROP TABLE IF EXISTS customers, orders, products;

删除临时表

-- 删除当前会话的临时表
DROP TEMPORARY TABLE IF EXISTS temp_log;

注意事项

权限要求
- 需要当前用户对表有 DROP 权限。
物理文件删除
- 如果表使用独立表空间（innodb_file_per_table=ON），删除表会同时删除对应的 .ibd 文件和 .sdi 文件。
不可恢复
- 删除操作会永久删除表结构和数据，务必提前备份。
外键约束
- 如果表被其他表的外键引用，默认会删除失败。需先删除外键约束或关联表。

错误处理

表不存在 若未使用 IF EXISTS 且表不存在，会报错：

ERROR 1051 (42S02): Unknown table 'database_name.table_name'

总结

核心语法：DROP TABLE IF EXISTS table_name;
生产环境操作前务必确认备份，避免数据丢失

最后想强调的是：不要轻易的改或者删除一个表

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2025-04-16，如有侵权请联系 cloudcommunity@tencent.com 删除

数据

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度