【MySQL】第三章 库的操作

news/2025/1/13 14:32:58/

系列文章目录

《【MySQL】第一章 MySQL 5.7的安装与卸载》
《【MySQL】第二章 初识数据库》
《【MySQL】第三章 库的操作》
《【MySQL】第四章 表的操作》
《【MySQL】第五章 数据类型》

文章目录

  • 系列文章目录
  • 库的增删查改
  • 库的备份与恢复
    • 备份
    • 恢复
    • 注意事项

目标：

  1. 了解MySQL的库级别的增删查改操作。
  2. 了解创建数据库时编码集和校验集的相关话题。
  3. 了解MySQL的库的备份操作与恢复操作。

库的增删查改

创建数据库

建库 SQL 的语法：

CREATE DATABASE [IF NOT EXISTS] db_name [[create_specification] [create_specification] ...];create_specification:[DEFAULT] CHARACTER SET charset_name[DEFAULT] COLLATE collation_name
  • CREATE、DATABASE这些是MySQL内定的关键字，不区分大小写。
  • CREATE DATABASE是固定用法，注意与show databases区分。
  • db_name新创建的数据库的名字。
  • []表示是可选项。
  • [IF NOT EXISTS]可选项表示如果数据库不存在就创建，反之不执行任何操作。
  • [create_specification]是创建数据库时的一些创建选项。
  • [[create_specification] [create_specification] ...]表示创建选项各自独立，以空格进行分割，并且可以按需组合。
  • 语法种列举了两种创建选项，分别是
    • [DEFAULT] CHARACTER SET charset_name表示指定默认字符集。
    • [DEFAULT] COLLATE collation_name表示指定默认校验集。

以下是一些创建数据库的案例，案例中暂时不涉及到[create_specification]这个内容会与后续的【字符集与校验集】一起说明：

  1. 创建一个名字为db1的数据库。
    SQL语句执行结果表示SQL语句执行成功，有1行被影响。

    mysql> create database if not exists db1;
Query OK, 1 row affected (0.00 sec)

  2. 继案例1之后再次尝试创建一个名字为db1的数据库。
    SQL语句执行结果显示，语句执行出错，名字为db1的数据库已存在，不允许重复创建。

    mysql> create database db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists

  3. 增加[if not exists]选项再次创建名字为db1的数据库。
    SQL语句执行结果显示，语句执行成功，有1行被影响，有1个警告。
    [if not exists]让案例2的报错变成了一个警告，说明操作有问题但不影响后续执行。

    mysql> create database if not exists db1;
Query OK, 1 row affected, 1 warning (0.00 sec)

删除数据库

删库的 SQL 语法：

DROP DATABASE [IF EXISTS] db_name;

该SQL语句执行之后：

  • MySQL内再也找不到该数据库。
  • 对应的数据库目录被删除，目录里的数据表也会被一并删除。
  • 在没有备份的情况下，该删除操作是不可逆的，所以千万不要随意删除数据库！

以下是一些删除数据库的案例：

  1. 删除名为helloworld的数据库。

    mysql> drop database helloworld;
Query OK, 1 row affected (0.01 sec)

  2. 继案例1之后再次删除名字为helloworld的数据库
    SQL语句执行报错，显示数据库不存在，删除操作失败。

    mysql> drop database helloworld;
ERROR 1008 (HY000): Can't drop database 'helloworld'; database doesn't exist

  3. 使用[if exists]选项，再次尝试删除名字为helloworld的数据库。
    SQL语句执行成功，0行被影响，1个警告。SQL语句本来是有问题的，但[if exists]表示不让该语句真正执行，将错误降为了警告。

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

查看数据库

查看数据库

show databases;

查看数据库创建

show create database db_name;

示例：

mysql> show create database test1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

说明：

  • 数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字。
  • /*!40100 DEFAULT CHARACTER SET utf8 */不是注释，这个是指如果当MySQL的版本大于4.01就执行这句话，这句话的意思是设置test1数据库的默认字符集为utf8。

选择数据库

use db_name;

查看当前所选数据库

select database();

查看当前MySQL服务的连接状态

show processlist;

示例：

mysql> show processlist;
+----+------+-----------+--------+---------+------+----------+------------------+
| Id | User | Host      | db     | Command | Time | State    | Info             |
+----+------+-----------+--------+---------+------+----------+------------------+
| 11 | root | localhost | gomoku | Query   |    0 | starting | show processlist |
+----+------+-----------+--------+---------+------+----------+------------------+
1 row in set (0.00 sec)

修改数据库

ALTER DATABASE 数据库名 [[alter_spacification] [alter_spacification]...];alter_spacification:[DEFAULT] CHARACTER SET charset_name[DEFAULT] COLLATE collation_name

示例：

-- 修改前
mysql> show create database test1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)-- 将test1数据库字符集改成gbk。
mysql> alter database test1 charset=gbk;
Query OK, 1 row affected (0.00 sec)-- 修改后
mysql> show create database test1;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

字符集与校验集

在关于数据库创建的语法介绍中，提到了“字符集”和“校验集”这样的字眼，另外我们还发现，当一个数据库被新建，哪怕我们什么也没做，MySQL服务也会默认生成一个名称为db.opt的配置文件

在这里插入图片描述

配置文件中，default-character-set=utf8和“字符集”有关；default-collation和“校验集”有关。

下面就来详细说明一下什么字符集、什么是校验集，这两个集合对于数据库有什么影响。

概念

字符集（Character Set）是用于表示字符的编码标准。它定义了一组符号和对应的数值编码，用于存储文本数据。不同的字符集支持不同的字符集合，例如拉丁字母、汉字、阿拉伯字母等。

常见的字符集：

  • utf8mb4：UTF-8 编码的变种，最多使用 4 个字节来表示一个字符。它可以表示几乎所有语言的字符，是目前推荐的字符集，也是mysql默认使用的字符集。
  • latin1：西欧字符集，使用单字节编码（ISO-8859-1），仅支持基本的拉丁字母。
  • ascii：美国信息交换标准代码，使用单字节编码，只包含 128 个字符，适用于英文文本。

校验集（Collation）定义了字符集中的字符如何比较和排序。它包括字符的排序顺序和比较方式，决定了数据库在执行排序和比较操作时的行为。

常见的校验集：

  • utf8mb4_general_ci：UTF-8 字符集的通用不区分大小写的排序规则，比较性能较好，但处理某些特殊字符可能不够准确。
  • utf8mb4_unicode_ci：UTF-8 字符集的 Unicode 不区分大小写的排序规则，支持更精确的字符比较，特别是对多语言支持更好。

举一个通俗的例子来理解字符集与校验集：

现在有一本书，我们可以把这本书看作一个数据库（目录），书中的内容就是这个数据库的数据（目录里的文件），假设中文就是这本书的字符集，我们（数据库管理系统）可以把这个中文字符集理解成一本字典，我们看到书里的某个字或者符号就要到字典里查，只有在字典里定义好的字或者符号，才是我们能够理解的，如果这时候出现了一个字典里不存在的火星文字，我们就无法识别它的意思，这就叫做 “字符不在字符集的范围内，数据库管理系统无法正确识别和处理这些字符”。

至于校验集，我们可以用比较“中文”和“中國”的例子来说明。在读书的过程中，我们会将“中文”和“中國”理解成是等价的，这个就是校验集中的比较。校验集决定了在比较和排序时，哪些字符或字符串被认为是等价的，哪些字符或字符串是不同的。

而字符集和校验集必须要对应，毕竟火星文的校验集对于中文字符集没什么意义。

查看MySQL默认字符集

show variables like 'character_set_database';

SQL语句执行结果

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.01 sec)

当前创建的数据库如果没有手动指定字符集，MySQL 默认使用 utf8，不过， MySQL 的 utf8 编码也不是真正的 utf8 ，没有包含某些复杂的中文字符。MySQL真正的 utf8 是使用 utf8mb4。

查看MySQL默认校验集

show variables like 'collation_database';

SQL语句执行结果

+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_general_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)

当前创建的数据库如果没有手动指定字符集，MySQL 默认使用 utf8_general_ci。

查看MySQL可支持的字符集

show charset;

SQL语句输出结果过长，这里仅截取一部分

+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
-- (省略)

查看MySQL 可支持的校验集

show collation;

SQL语句输出结果过长，这里仅截取一部分

+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
| dec8_bin                 | dec8     |  69 |         | Yes      |       1 |
| cp850_general_ci         | cp850    |   4 | Yes     | Yes      |       1 |
-- (省略)

创建指定编码的数据库

  1. 创建一个名字为dp1的数据库，查看它的默认字符集和校验集。
create database if not exists db1;

查看dp1的字符集和校验集有两种方式：

  1. select语句，这个尚未了解；
  2. dp1的配置文件，就是创建数据库时MySQL服务自动生成的那个叫做db.opt的文件。

关于db.opt的一点说明：在用户没有主动设定的时候，db.opt的内容是拷贝自/etc/mysql里的配置文件，而一旦我们主动设定了，就以用户设定的内容为准，相当于就近原则。

新启动终端2（参考之前【见一见数据库】的操作，该终端在后续案例直接使用），使用root用户登录，进入/var/lib/mysql目录下的db1目录下，读取db.opt文件的内容，能够看到db1的默认使用的字符集是utf8，默认使用的校验集是utf8_general_ci。

root@iZ7xvhuhaslv4h5dm1d1svZ:/var/lib/mysql/db1# ll
total 12
drwxr-x--- 2 mysql mysql 4096 Oct 30 10:55 ./
drwxr-x--- 8 mysql mysql 4096 Oct 30 10:55 ../
-rw-r----- 1 mysql mysql   61 Oct 30 10:55 db.opt
root@iZ7xvhuhaslv4h5dm1d1svZ:/var/lib/mysql/db1# cat db.opt 
default-character-set=utf8
default-collation=utf8_general_ci

  1. 创建一个名字为db2，使用gbk字符集的数据库，再次查看dp2的配置文件。
    创建数据库时字符集的设定是通过创建选项来指定的。
    设置字符集的创建选项的正式写法是character set，可以简写成charset。
    标准写法是空格字符集名称，也可以写成=字符集名称，这个也适用于其他创建选项。

    -- 写法1
create database if not exists db2 charset gbk;
-- 写法2
create database if not exists db2 charset=gbk;
-- 写法3
create database if not exists db2 character set gbk;
-- 写法4
create database if not exists db2 character set=gbk;

    切换回终端2，进入db2数据库对应的目录，然后读取db.opt的内容，显然能够看到配置文件中的字符集相较于默认的配置发生了变化。

    root@iZ7xvhuhaslv4h5dm1d1svZ:/var/lib/mysql/db2# ll
total 12
drwxr-x--- 2 mysql mysql 4096 Oct 30 11:19 ./
drwxr-x--- 9 mysql mysql 4096 Oct 30 11:19 ../
-rw-r----- 1 mysql mysql   59 Oct 30 11:19 db.opt
root@iZ7xvhuhaslv4h5dm1d1svZ:/var/lib/mysql/db2# cat db.opt 
default-character-set=gbk
default-collation=gbk_chinese_ci

  2. 创建一个名字为db3，使用gbk字符集，使用gbk_chinese_ci校验集的数据库。
    设置校验集的创建选项是collate，该选项没有简写。

    -- 写法1
create database if not exists db3 charset=gbk collate=gbk_chinese_ci;
-- 写法2
create database if not exists db3 charset=gbk collate gbk_chinese_ci;

    切换回终端2，进入db3数据库对应的目录，然后读取db.opt的内容，显然能够看到配置文件中的字符集和校验集相较于默认的配置都发生了变化。

    root@iZ7xvhuhaslv4h5dm1d1svZ:/var/lib/mysql/db3# ll
total 12
drwxr-x---  2 mysql mysql 4096 Oct 30 11:23 ./
drwxr-x--- 10 mysql mysql 4096 Oct 30 11:23 ../
-rw-r-----  1 mysql mysql   59 Oct 30 11:23 db.opt
root@iZ7xvhuhaslv4h5dm1d1svZ:/var/lib/mysql/db3# cat db.opt 
default-character-set=gbk
default-collation=gbk_chinese_ci

验证不同校验集的影响

现在来做一个实验，创建两个字符集相同，但是校验规则不同的数据库，分别建立一个相同的表，插入一些相同的数据，然后进行查询，观察不同校验编码对于查询结果的影响。

注：这部分内容会出现一些表的插入、查询相关的SQL语句，后续会有详细讲解。

【第一阶段：数据的准备】

创建test1的数据库，字符集使用utf8，校验集使用utf8_general_ci（该校验集不区分字母大小写），创建person表，依次插入a、A、b、B。

-- 创建test1数据库
create database if not exists test1 charset=utf8 collate=utf8_general_ci;
-- 选择test1数据库
use test1;
-- 创建Person表
create table if not exists person (name varchar(10));
-- 依次插入a、A、b、B
insert into person (name) values ('a');
insert into person (name) values ('A');
insert into person (name) values ('b');
insert into person (name) values ('B');

创建test2数据库，字符集使用utf8，校验集使用utf8_bin（该校验集区分字母大小写），创建person表，依次插入a、A、b、B。

-- 创建test2数据库
create database if not exists test2 charset=utf8 collate=utf8_bin;
-- 选择test2数据库
use test2;
-- 创建Person表
create table if not exists person (name varchar(10));
-- 依次插入a、A、b、B
insert into person (name) values ('a');
insert into person (name) values ('A');
insert into person (name) values ('b');
insert into person (name) values ('B');

【第二阶段：以**name='a'**为条件进行查询】

不区分大小写的查询结果

mysql> use test1;
Database changed
mysql> select name from person where name='a';
+------+
| name |
+------+
| a    |
| A    |
+------+
2 rows in set (0.00 sec)

区分大小写的查询结果

mysql> use test2;
Database changed
mysql> select name from person where name='a';
+------+
| name |
+------+
| a    |
+------+
1 row in set (0.00 sec)

【第三阶段：查询升序排序结果】

不区分大小写的查询结果。
在不区分大小写的情况下，a、A按照插入顺序进行排列。

mysql> use test1;
Database changed
mysql> select * from person order by name;
+------+
| name |
+------+
| a    |
| A    |
| b    |
| B    |
+------+
4 rows in set (0.00 sec)

区分大小写的查询结果。

在区分大小写的情况下，a、A严格按照ASCII码进行排列。

mysql> use test2;
Database changed
mysql> select * from person order by name;
+------+
| name |
+------+
| A    |
| B    |
| a    |
| b    |
+------+
4 rows in set (0.00 sec)

【第四阶段：总结】

本实验展示了数据库校验规则对数据查询和排序的显著影响。使用不区分大小写的校验集时，相同字符的不同大小写会被视为相同，而在区分大小写的校验集中，这些字符则会被视为不同的值。选择合适的校验规则对于确保数据处理的准确性和预期结果至关重要。

字符集和校验集虽然重要，但是创建数据库的时候没有特地设置字符集和编码集的必要，MySQ 的默认字符集通常是utf8（utf8mb4），默认校验集为utf8mb4_general_ci，适用于大多数语言和字符，可以无缝处理多语言数据，并且对大小写不敏感。这适合大多数应用需求，如存储文本、用户名和标签等。

综上，除非应用需求特别要求，对于一般应用，使用默认字符集和校验集是更简便的选择。

库的备份与恢复

备份

mysqldump -P 端口号 -u 用户名 -p 密码 -B 数据库名 > 数据库备份存储的文件路径

示例：将 mytest 库备份到文件 mytest.sql。

mysqldump -P 3306 -u root -p123456 -B mytest > ./mytest.sql

需要注意的一点，mysqldump 是一个在命令行（Shell）中运行的工具，而不是一个在 MySQL 命令行客户端（即 mysql> 提示符下）中运行的 SQL 命令，像下面这样的使用方法是有问题的：

mysql> mysqldump -P 3306 -u root -p -B test1 > ~/temp/test1.sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump -P 3306 -u root -p -B test1 > ~/temp/test1.sql' at line 1

正确的做法是：

  1. 退出 MySQL 命令行客户端：

    mysql> quit;
Bye

  2. 在终端中运行 mysqldump 命令：

    # temp 不存在要先创建
# -bash: /root/temp/test1.sql: No such file or directory
# 创建命令：mkfir temp
mysqldump -P 3306 -u root -p -B test1 > ~/temp/test1.sql

这时，可以打开看看备份文件里的内容，它其实不是把我们要备份的数据库复制一份，而是把我们整个创建数据库，建表，导入数据的语句都装载这个文件中，如果我们将来要恢复该数据库，就把这个备份文件中的 SQL 语句全部执行一次。

恢复

方法一：

mysql> source ~/temp/test1.sql;

方法二：

root@iZ7xvhuhaslv4h5dm1d1svZ:~# mysql -u root -p < ~/temp/test1.sql

注意事项


http://www.ppmy.cn/news/1562821.html

相关文章

实训云上搭建集群

实训云上搭建集群

文章目录 1. 登录实训云1.1 实训云网址1.2 登录实训云 2. 创建网络2.1 网络概述2.2 创建步骤 3. 创建路由器3.1 路由器名称3.1 创建路由器3.3 查看网络拓扑 4. 连接子网5. 创建虚拟网卡5.1 创建原因5.2 查看端口5.3 创建虚拟网卡 6. 管理安全组规则6.1 为什么要管理安全组规则6…
阅读更多...
Leetcode 967 Numbers With Same Consecutive Differences

Leetcode 967 Numbers With Same Consecutive Differences

题意 给定n&#xff0c;代表整数的长度&#xff0c;给定k代表两个相邻数字之间的间隔。求所有的值构成的组合 题目链接 https://leetcode.com/problems/numbers-with-same-consecutive-differences/description/ 题解 dfs&#xff0c;有k位置要选&#xff0c;第一个位置我…
阅读更多...
【JVM-2.1】如何使用JMC监控工具：详细步骤与实战指南

【JVM-2.1】如何使用JMC监控工具：详细步骤与实战指南

Java Mission Control&#xff08;JMC&#xff09;是Oracle提供的一个高级图形化监控工具&#xff0c;专为Java应用程序的性能分析和故障排查设计。JMC不仅提供了实时监控功能&#xff0c;还支持飞行记录器&#xff08;Flight Recorder&#xff09;功能&#xff0c;能够记录JVM…
阅读更多...
深入剖析 Java 设计模式之观察者模式

深入剖析 Java 设计模式之观察者模式

一、开篇语 在 Java 编程的广袤天地里&#xff0c;设计模式宛如一盏盏明灯&#xff0c;照亮我们构建高效、灵活且可维护代码体系的道路。其中&#xff0c;观察者模式作为一种极具影响力的行为型设计模式&#xff0c;在众多实际开发场景中展现出非凡的价值。它就如同现实世界中…
阅读更多...
Apache和PHP：构建动态网站的黄金组合

Apache和PHP：构建动态网站的黄金组合

在当今的互联网世界&#xff0c;网站已经成为了企业、个人和机构展示自己、与用户互动的重要平台。而在这些动态网站的背后&#xff0c;Apache和PHP无疑是最受开发者青睐的技术组合之一。这一组合提供了高效、灵活且可扩展的解决方案&#xff0c;帮助您快速搭建出强大的网站&am…
阅读更多...
LKT4304新一代算法移植加密芯片，守护物联网设备和云服务安全

LKT4304新一代算法移植加密芯片，守护物联网设备和云服务安全

凌科芯安作为一家在加密芯片领域深耕18年的企业&#xff0c;主推的LKT4304系列加密芯片集成了身份认证、算法下载、数据保护和完整性校验等多方面安全防护功能&#xff0c;可以为客户的产品提供一站式解决方案&#xff0c;并且在调试和使用过程提供全程技术支持&#xff0c;针对…
阅读更多...
Maven多模块项目如何灵活构建

Maven多模块项目如何灵活构建

Maven多模块项目如何灵活构建 Maven多模块项目如何灵活构建示例项目构建所有模块构建parent和core模块构建parent和extension模块 Maven多模块项目如何灵活构建 在Java开发中使用maven来处理包管理是很便捷的&#xff0c;然而开发过程中多模块开发是常规操作。那么多模块开发过…
阅读更多...
ref() 和 reactive() 区别

ref() 和 reactive() 区别

ref() 和 reactive() 都是 Vue 3 中用于创建响应式数据的方法&#xff0c;但它们之间存在一些关键差异。 首先&#xff0c;ref() 用于创建响应式的标量值&#xff0c;比如数字、字符串、布尔值等基本数据类型&#xff0c;以及对象和数组等复杂数据类型。当你使用 ref() 时&…
阅读更多...
最新文章

Copyright @ 2022~2023