MySQL库操作

一、SQL语句

• DDL(data definition language)：数据定义语言，用来维护数据表、数据库的属性结构。代表指令有 create/drop/alter
• DML(data manipulation language)：数据操纵语言，用来修改文件内容
• DCL(Data Control Language)：数据控制语言，主要负责数据库的安全和权限管理

​ 在本文中，我们将重点介绍对数据库的增删查改操作。需要注意的是，我们只是修改数据库的属性结构，并没有修改数据库的内容，所以接下来介绍的SQL语句都属于DDL语句。

​ 不同版本的MySQL实际可能会有所不同，本教程所使用地MySQL版本为5.7

二、创建数据库

1.语法

CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET   charset_name] [COLLATE collation_name]

说明：

• []表示可加可不加
• CHARACTER SET: 指定数据库采用的字符集。没有则采用配置文件中默认选用的
• COLLATE: 指定数据库字符集的校验规则。没有则采用配置文件中所默认选用的
• create、database属于mysql中的关键字。mysql对关键字的大小写不敏感（注意仅仅是对关键字不敏感，但是比如对数据库名，数据表名还是敏感的），为了便于阅读，之后都用小写呈现

（字符集和校验集在后面会介绍）

2.案例

• 创建一个名为 db1 的数据库

  create database db1;
  

• 加上 if not exists。只有当目标数据库不存在时才会创建

  

• 创建一个使用 utf-8 字符集的数据库

  create database db2 charset=utf8;  -- 写法1
  create database db2 character set utf8;  -- 写法2
  

• 创建一个使用 utf-8 字符集，校验集为 utf8_general_ci 的数据库

  create database db3 charset=utf8 collate utf8_general_ci;
  create database db3 charset=utf8 collate=utf8_general_ci;
  

• 创建一个名字为 create 的数据库。因此create是mysql中的关键字，如果我们需要将以它作为数据库名的话就需要加上 ``。

  create database `create`
  

​ 字符集和校验集如果没有被设置，则采用默认设置的；否则根据就近原则，采用用户所指定的。在一般情况下，都不推荐用户自己设置字符集和校验集

3.极其不推荐的方式

​ 当我们创建一个数据库的时候，在物理层面上，本质就是在 var/lib/mysql 路径下为我们为我们创建对应的文件夹：

​ 如果我们手动的在这个路径下创建目录，虽然也会被mysql识别成数据库，但这样的做法是不推荐的。就好比你的领导的领导直接给你发任务，这样显然乱了套。

三、查看数据库

1.语法

show databases;   -- 显示所有的数据库
show create database db_nams  -- 显示创建一个数据库的指令

四、修改数据库

基本语法：

ALTER DATABASE db_name [alter_spacification……]

常用修改：

• 修改字符集与校验集

  alter database db1 charset=gbk;
  

  

  ​ 对比修改前和修改后，我们发现虽然我们只修改了字符集，编码集也会自动调整为相对应的。而且数据库创建语句也会发生变化

  

• 注意在mysql 5.7下是不支持修改数据库名称的。本身修改数据库名称的行为就是极其不推荐的，在开始时就应该明确，否则容易影响到上层业务。

五、删除数据库

drop database [if exists] db_ name;

删除数据库时，会将数据库中的所有内容都删除。需要慎重！

六、字符集与校验规则

1.是什么

• 字符集是指用于表示字符的编码规则。在 MySQL 中，字符集决定了如何存储字符数据，以及如何将字符数据传递给客户端。MySQL 支持多种字符集，这些字符集的选择会影响到数据存储的可靠性、数据传输的正确性以及文本比较、排序等操作的结果。​
• 校验规则是用于规定字符数据的比较方法。在 MySQL 中，校验规则决定了如何比较字符串，包括如何区分大小写、如何对不同字符进行排序等。MySQL 支持多种校验规则，这些规则在不同场合下可能会产生不同的比较结果。正确选择校验规则可以保证数据比较、排序等操作的正确性

数据库文件夹中的 dp.opt 文件就保存着该数据库所采用的字符集和校验集

2.相关指令

• 查看数据库默认使用的字符集以及校验规则

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

  

• 使用模糊查询看到更多内容

  show variables like 'character%';
  show variables like 'collation%';
  

  

• 查看数据库支持的所有字符集。可以看到每个字符集默认匹配一个校验集

  show charset;
  

  

  （部分）

• 查看数据库支持的所有校验集。一个字符集实际上可以对应多个校验集

  show collation;
  

  

3.校验规则的影响

​ 我们在前面提到，校验集用于字符数据之间的比较。校验集的不同也会影响大小写区分和排序等。

• 创建两个不同字符集的数据库

create database db2 collate utf8_general_ci; -- 不区分大小写	
create database test2 collate utf8_bin; -- 区分大小写	

• 分别插入以下数据

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

• 查询结果：

select * from person where name='a';

• 排序结果

select* from person order by name

七、备份数据库

1.基本语法

​ 我们前面提到，数据库在物理层面上，本质就是 var/lib/mysql路径下的一个文件夹，而数据表则是文件夹中的一个文件。我们要想拷贝一个数据库，虽然也可以粗暴的使用cp指令复制文件夹来实现对数据库的复制，但同样这样的行为是极其不推荐的。

​ MySQL提供了一个命令行工具 mysqldump，专门用来备份和还原mysql数据库。其使用方法如下：

mysqldump -P 3306 -uroot -p password -B database > backup.sql

说明：

• -P说明mysql服务端的端口号
• -uroot 等价于 -u root，写法上都可以
• -p说明数据库的密码
• -B说明需要备份的数据库名
• > backup.sql 表示将数据结果重定向到backup.sql文件中，否则会输出到屏幕上

​ 我们从下面的保存结果中不难发现，数据库备份本质上并不是为我们备份具体的数据，而是为我们保存创建数据库的历史SQL语句（MySQL会为我们做优化）。这样做除了可以备份数据本身之外，还可以包含数据库的完整结构、约束、触发器、存储过程等信息，这样在备份还原时可以更好的保证数据的一致性与可靠性。

复原的方式如下(mysql下的指令)：

source xxx  -- 复原xx路径下的文件

2.注意事项

• 如何只备份数据库中的一张或多张表？

   mysqldump -u root -p 数据库名 表名1 表名2 > /mytest.sql
  

• 如何同时备份多个数据库

  mysqldump -u root -p -B 数据库名1 数据库名2 ... > /mytest.sql
  

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