【MySQL】MySQL操作库

【MySQL】MySQL操作库

​ 本文带着大家一起了解MySQL的基本使用与操作库。首先我们连接MySQL的服务器：

# 如果没有写 -h 127.0.0.1 默认是连接本地
# 如果没有写 -P 3306 默认是连接3306端口号
mysql -h 127.0.0.1 -P 3306 -u root -p
1
2
3

​ 然后我们开始了解MySQL中关于库的操作。

库的操作

创建数据库

创建数据库实例

​ 首先让我们来简单认识一下MySQL中的数据库创建命令。

# 创建一个数据库db1
create database db1;
# 创建一个使用utf8字符集的 db2 数据库
create database db2 charset=utf8;
# 创建一个使用utf8字符集，并带校对规则的 db3 数据库。
create database db3 charset=utf8 collate utf8_general_ci;
1
2
3
4
5
6

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

​ 操作过程如下：

我们首先使用show databases;的命令看看当前系统中存在哪些数据库：

然后我们使用创建数据库的语句创建出一个名为db的数据库，并手动设置字符集和校验集：

最后我们再使用show databases;的命令观察现象：

发现系统中多了一个名称为db的数据库，创建完毕。

​ 除此之外，MySQL会有一个存放数据的数据目录，我们在使用MySQL时会在数据目录下产生相应的结果。在我的Linux环境下该目录是’/var/lib/mysql’，如果我们按照上面的操作创建一个名为db的数据库，那么在该数据目录下就会创建出一个叫做db的目录：

字符集与校验集

​ 数据库的字符集和校验集都是在处理和存储数据时起着重要作用的概念。

1. 字符集（Character Set）：字符集定义了数据库中可以存储的字符的集合，它包括了支持的字符以及它们的编码方式。常见的字符集包括 ASCII、UTF-8、UTF-16 等。选择适当的字符集对于确保数据的正确存储和处理至关重要。如果数据库的字符集不支持某些特定的字符，那么在存储这些字符时可能会导致数据损坏或者乱码。

2. 校验集（Collation）：校验集定义了对字符的比较和排序规则。在某些语言中，同一个字符的不同形式可能会被认为是相等的，比如在英语中，大小写字母可能被视为相等。校验集决定了在进行比较和排序时字符的处理方式。选择适当的校验集可以确保在查询时返回正确的结果，比如按字母顺序排列字符串或执行基于字符串的搜索操作时。

​ 总的来说，字符集和校验集都是数据库中确保数据正确性和一致性的重要组成部分，它们影响着数据的存储、处理和检索。因此，在创建数据库时，选择合适的字符集和校验集是至关重要的。

​ 下面是一些关于字符集与校验集的指令：

# 查看系统默认字符集以及校验规则
show variables like 'character_set_database';
show variables like 'collation_database';
# 查看数据库支持的字符集
show charset;
# 查看数据库支持的字符集校验规则
show collation;
1
2
3
4
5
6
7

操纵数据库

查看数据库

show databases;
1

显示创建语句

show create database 数据库名;
1

示例：

说明：

• 这里显示出的内容是我们创建出这个数据库可以使用的语句(优化后)，MySQL会记录我们的所有操作痕迹。
• MySQL 建议我们关键字使用大写，但是不是必须的。（本文中常用小写字母）
• 数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字。
• /*!40100 default… */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话。

修改数据库

​ 这里对于数据库的修改主要指的是修改数据库的字符集，校验规则。

# 语法：
ALTER DATABASE db_name
[alter_spacification [,alter_spacification]...] 


alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name
1
2
3
4
5
6
7
8

示例：

数据库删除

# 语法：
DROP DATABASE [IF EXISTS] db_ name;
1
2

执行删除之后的结果:

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

​ 注意，在我们平常使用数据库时不能随意删除数据库，这会导致使用了该数据库的上层的服务出现很大的问题。

数据库的备份和恢复

# 备份数据库
mysqldump -P3306 -u root -p 密码 -B 数据库名1 数据库名2 ... > 数据库备份存储的文件路径;
# 还原数据库
source 数据库备份存储的文件路径;

# 如果备份的是数据库中的表：
mysqldump -u root -p 数据库名 表名1 表名2 > 备份存储的文件路径;
source 备份存储的文件路径;
1
2
3
4
5
6
7
8

查看连接情况

#语法
show processlist;
1
2

示例：

表的操作

创建表

# 语法
CREATE TABLE [IF NOT EXISTS] table_name (
    field1 datatype,
    field2 datatype,
    field3 datatype #（最后一列不需要加逗号）
    # ...
) [character set 字符集 collate 校验规则 engine 存储引擎];
# 说明：
# [ ]表示可选项
# field 表示列名
# datatype 表示列的类型
# character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准
# collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准
1
2
3
4
5
6
7
8
9
10
11
12
13

示例：

​ 通过这样的SQL语句，我们在数据库db创建出一个想要到得到的表。当我们在一个数据库中添加一个表之后，MySQL就会在对应的数据目录下添加一些表的文件，这些文件根据搜索引擎的不同而不同。我们创建出上述的表之后产生的是如下的一些文件：

查看表

#语法
desc 表名;
1
2

示例：

​ 这些表结构的信息从左到右分别是字段名字，字段类型，是否允许为空，索引类型，默认值，扩充。

​ 除此之外，我们还可以使用show create table 表名 这样的语句显示出我们当初创建表的语句：

​ 如果你觉得需要去掉这条语句中显示的很多不必要的地方，可以以‘\G’作为语句的结尾，它可以去掉这些内容：

修改表

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

# 语法：
ALTER TABLE 表名 ADD (column datatype [DEFAULT expr][,column
datatype]...);

ALTER TABLE 表名 MODIfy (column datatype [DEFAULT expr][,column
datatype]...);

ALTER TABLE 表名 DROP (column)
1
2
3
4
5
6
7
8

示例：
向表中插入两行数据：

​ 向表中添加新的字段：

​ 插入新的字段后，对于原来的数据是没有影响的，新的字段下的值都为NULL。

​ 如果我们这时使用show create table user \G这条语句，将会显示如下结果：

​ 修改name列的属性，将其长度改为60：

​ 需要注意的是，这里的修改是对原定义的直接覆盖，而并不是你想要修改一下类型就只修改类型：

​ 我们可以看到，原来的comment已经没有了，只剩下我们当时修改时定义的类型。

​ 删除图片路径(image_path)列：

​ 需要注意的是，如果删除了这一列，那么这一列之前的数据都会被删除。

​ 修改表名为User：

​ 修改name列为Name：

删除表

# 语法：
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...
1
2

示例：