【MySQL】库操作和表操作

￡神魔★判官ぃ 2024-04-27 07:44 90阅读 0赞

#### 文章目录 ####

*  一、库操作
 *   *  1. 创建数据库
     *  2. 数据库的编码问题
     *   *  查看系统默认支持的字符集
         *  查看数据库支持的字符集
         *  查看数据库支持的字符集校验规则
         *  校验规则对数据库的影响
     *  3. 操纵数据库
     *   *  查看数据库
         *  显示创建语句
         *  修改数据库
         *  删除数据库
     *  4. 数据库的备份和恢复
     *   *  数据库的备份
         *  数据库的恢复
         *  表的删除与恢复
         *  5. 查看连接情况
 *  二、表操作
 *   *  创建表
     *  查看表结构
     *  修改表
     *  删除表

--------------------

## 一、库操作 ##

### 1. 创建数据库 ###

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

*  `create database` 表示为 固定用法
 *  `db_name` 表示为数据库名称
 *  `[ IF NOT EXISTS ]` 表示可选项
 *  `CHARACTER SET` : 指定数据库采用的字符集
 *  `COLLATE` : 指定数据库字符集的校验规则

? **创建数据库**

create database if not exists test1;

![在这里插入图片描述][53e929ac2ef54a339cce06ab371d705b.png]

`IF NOT EXISTS` 表示：当前数据库不存在 test1，则创建数据库。

![在这里插入图片描述][5c92e4a200aa4b62bae22dd867f9d863.png]

如果已经存在同名的数据库，则会报警，并且不会创建一个同名的数据库。

如果我们想要删除一个数据库，直接输入 `drop database 数据库名`，当我们删除一个数据库后，该数据库对应的目录也会被删除，若该目录下有表存在，则也会被直接删除。因此不建议随意删除数据库。

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

--------------------

### 2. 数据库的编码问题 ###

创建数据库时，有两个编码集：**`数据库的字符集`** 和 **`数据库的校验集`**

> 数据库的字符集 —— 数据库存数据时，采用什么编码

![在这里插入图片描述][876b49e3bb4a4dbdae40727a470d7c20.png]

如果没有对MySQL的配置文件进行过修改，则默认的编码格式时utf8，默认的校验规则是utf8\_general\_ci。

> 数据库的校验集 —— 支持数据库，进行字段比较使用的编码，本质是读取数据库中数据的采用的编码格式 ( 查找或匹配数据库，就需要进行比较字段，想要被比较，两者就需要先被读出来，此时两者的校验规则应该相同)

![在这里插入图片描述][f60862fdf532458083c22c0c50682e36.png]

#### 查看系统默认支持的字符集 ####

? **系统默认支持的字符集**

show variables like 'character_set_database';

![在这里插入图片描述][0b41bcf9bc4c4d97a611262b63937a63.png]

通过查看MySQL系统变量variables 当中的 character\_set\_database ，可以看到系统默认的字符集。

? **系统默认支持的字符集校验规则**

show variables like 'collation_database';

![在这里插入图片描述][ea946191ad1240b4b7d26f2ee9f1daa8.png]

通过查看MySQL系统变量variables当中的collation\_database，可以得知系统默认的字符集校验规则。

--------------------

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

show charset;

![在这里插入图片描述][e245c8d67f4d42388676b963830185a5.png]

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

--------------------

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

show collation;

![在这里插入图片描述][419edef701a84dad8e684311861d7ec2.png]

--------------------

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

? **字符集编码格式和字符集校验规则的区别**

*  字符集编码格式指的是在存储数据时各个字符的底层编码，用于指定数据的存储格式。
 *  字符集校验规则是在字符集内用于比较字符的一套规则，用于对数据进行对比。

例如我们在存储数据时是按照utf8的格式进行存储的，那么将来在对数据进行做对比时也必须按照utf8的格式进行对比，因为 “存数据” 和 “取数据” 的方式必须保持一致。

? **校验规则对数据库的影响**

字符集编码格式和校验规则必须是对应的，但实际上同一种字符集编码格式一般会有多种可选择的校验规则，比如 utf8编码格式的校验规则有 utf8\_general\_ci、utf8\_bin、utf8\_unicode\_ci等校验规则。使用不同的校验规则操作数据库中的数据可能会得到不同的结果。例如：utf8\_general\_ci校验规则在进行比对数据时是不区分大小写的，而utf8\_bin校验规则在对比数据时是区分大小写的。

--------------------

### 3. 操纵数据库 ###

#### 查看数据库 ####

使用`show databases` SQL可以查看系统中所有的数据库。

show databases;

![在这里插入图片描述][efbc9b6c3dde4650a9f37131e6024149.png]

--------------------

#### 显示创建语句 ####

使用 `show create database 数据库名` SQL 可以查看对应数据库的创建语句。

show create database test1;

![在这里插入图片描述][ae3eeb8247b644a7bd2b0bb01deb2339.png]

`/*!40100 DEFAULT CHARACTER SET utf8 */` 不是表示注释，而是表示当前MySQL的版本如果大于 4.10，则执行后面的SQL语句。

--------------------

#### 修改数据库 ####

ALTER DATABASE db_name [[DEFAULT] CHARSET=character_name] [[DEFAULT] COLLATE=collation_name];

这里的修改数据库指的是修改数据库的字符集或校验规则。

![在这里插入图片描述][8b41f9532f744a83b39a8b916d1c5053.png]

--------------------

#### 删除数据库 ####

DROP DATABASE [IF EXISTS] db_name;

删除数据库后该数据库对应的文件夹就被删除了。

![在这里插入图片描述][519b9b246d6e4ef8a0da08a1e5c9dbd2.png]

这里需要说明的是，删除数据库后，该数据库下的所有表也会都被级联删除，因此不要随便删除数据库。

--------------------

### 4. 数据库的备份和恢复 ###

#### 数据库的备份 ####

首先，我们先创建一个数据库并且向其中插入一张表。

![在这里插入图片描述][c739027e9acc4206b8d61878f9a898a5.png]

**对指定数据库进行备份的命令：**

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

![在这里插入图片描述][d5930635e3da43bbb39f94d654aaa7e1.png]

打开test.sql，我们可以发现文件中的内容就是我们在该数据库中执行的各种SQL命令，包括创建数据库、创建表、插入数据等SQL语句。

![在这里插入图片描述][8b667a8ee49049c491afe41ad6ef1382.png]

--------------------

#### 数据库的恢复 ####

**恢复数据库的SQL语句：**

source 数据库备份存储的文件路径

先将指定数据库删除：

![在这里插入图片描述][1da1f14f6eec4e4ca20025b3feb702d8.png]

恢复数据库：

source /root/MySQL/test.sql

![在这里插入图片描述][3decc7f78080470d942b2353371ebee7.png]

这里我们可以看到数据库已经被恢复出来了。

![在这里插入图片描述][21b084b7677645fba49a722c2b0e058b.png]

--------------------

#### 表的删除与恢复 ####

? **表备份**

mysqldump -P3306 -u root -p Test student > /root/MySQL/table.sql

![在这里插入图片描述][e6b00e2ff1a44af79cf270a1e0bdaeb3.png]

将表删除掉：

![在这里插入图片描述][0fae833a5a304c3a9b4f38c9181e6df6.png]

? **表恢复**

source /root/MySQL/table.sql;

![在这里插入图片描述][05369a7b9e1d47c4a0674ddd3d4191d3.png]  
![在这里插入图片描述][a880168b9981465282567508a4efa6c0.png]

这里我们看到表已经被完全恢复出来了。

--------------------

#### 5. 查看连接情况 ####

使用 `show processlist` SQL来查看当前连接MySQL的用户。

show processlist;

![在这里插入图片描述][3acf8b5519bd4350b545eb91757b44e1.png]

*  Id列：一个标识，可以在MySQL中通过kill id杀死指定id的线程。
 *  User列：显示当前用户，如果不是root，这个命令就只显示你权限范围内的SQL语句。
 *  Host列：显示这个语句是从哪个IP的哪个端口上发出的，可用来追踪出问题语句的用户。
 *  db列：当前执行的命令是在哪一个数据库上，如果没有指定数据库，则该值为NULL。
 *  Command列：显示当前连接执行的命令，一般就是休眠（Sleep）、查询（Query）和连接（Connect）。
 *  Time列：表示该线程处于当前状态的时间，单位是秒。\\nState列：显示使用当前连接的SQL语句的状态。
 *  Info列：一般记录的是线程执行的语句，默认只显示前100个字符，如果要看全部信息，需要使用show full processlist。

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

--------------------

## 二、表操作 ##

表操作至少需要会下面两类的SQL语句：

*  DDL（Data Definition Language）数据定义语言：比如建表、删表、该表、新增列、删除列等。
 *  DML（Data Manipulation Language）数据操作语言：比如插入记录、删除记录、修改记录等。

### 创建表 ###

创建表的SQL语句如下：

CREATE TABLE [IF NOT EXISTS] table_name(
    	field1 datatype1 [COMMENT '注释信息'],
    	field2 datatype2 [COMMENT '注释信息'],
    	field3 datatype3  [COMMENT '注释信息']
    )[CHARSET=charset_name] [COLLATE=collation_name] [ENGINE=engine_name];

1.  SQL中大写的表示关键字，\[ \]中代表的是可选项。
2.  field表示列名，datatype表示列的类型。
3.  CHARSET用于指定表所采用的编码格式，如果没有指定则以所在数据库的编码格式为准。
4.  COLLATE用于指定表所采用的校验规则，如果没有指定则以所在数据库的校验规则为准。
5.  ENGINE用于指定表所采用的存储引擎。
6.  COMMENT用于对指定列添加注释信息。

这里我们需要注意的是：MySQL默认使用的是InnoDB存储引擎，如果没有指定使用哪种存储引擎那么就会默认使用InnoDB存储引擎。

--------------------

? **创建表**  
![在这里插入图片描述][1b49d1dd2516426393d2fd9009164fe2.png]

--------------------

### 查看表结构 ###

使用 `desc 表名` SQL可以查看表的结构。

desc user;

![在这里插入图片描述][281cdcae2f044feab4f465f9ce1928ce.png]

*  Field表示该字段的名字。
 *  Type表示该字段的类型。
 *  Null表示该字段是否允许为空。
 *  Key表示索引类型，比如主键索引为PRI。
 *  Default表示该字段的默认值。
 *  Extra表示该字段的额外信息说明。

如果想要查看创建表时的相关细节，可以使用 `show create table 表名`SQL。如下：

show create table user\G

![在这里插入图片描述][dda882bda1f44fb58fe6ec761da400ff.png]

--------------------

### 修改表 ###

修改表的 SQL如下：

ALTER TABLE table_name ADD 新增列名 新增列的属性;
    
    ALTER TABLE table_name MODIFY 列名 修改后的列属性;
    
    ALTER TABLE table_name DROP 列名;
    
    ALTER TABLE table_name RENAME [TO] 新表名;
    
    ALTER TABLE table_name CHANGE 列名 新列名 新列属性;

先看一下表中原有的信息：

![在这里插入图片描述][c52445ee227c4fdf9ae657926fd2c79b.png]

? **新增列**

alter table user add path varchar(128) comment '照片路径' after password;

![在这里插入图片描述][2d383c097cbb4298be8e66fa0a0ea0eb.png]

新增一列SQL语句中的after表示将该列新增到哪一列之后，如果想要将新增列放在第一列，可以将after及其之后的语句换成 `not null first`。

通过`desc`命令查看确认新增成功。

![在这里插入图片描述][2723ad09721244feba37d65836a70dcf.png]

这时我们插入一条用户信息后查看表中的信息就一目了然了。

![在这里插入图片描述][ae5bed4488e24ec29c6d2fa6b76f29e8.png]

--------------------

? **修改列类型**

将user表中的password列的类型由`char(30)`改成`char(50)`。如下：

alter table user modify password char(50) comment '用户密码';

![在这里插入图片描述][224bf36007384d409ba04fa9659c6c64.png]

这里需要注意的是，如果需要修列类型后仍然保留comment字段，需要在修改时重新指定comment字段。

--------------------

? **修改列名**

将user表中的password列的列明改成passwd。如下：

alter table user change password passwd char(50) comment '用户密码';

![在这里插入图片描述][74098fa7a2fe421c83abeb44c2e91ce2.png]

--------------------

? **修改表名**

将表user的名字改成People

alter table user rename People;

![在这里插入图片描述][ff25b144d3f44b72b83d315f495df076.png]

--------------------

? **删除列**

将user表中的birthday列删除。

alter table user drop birthday;

![在这里插入图片描述][c8a8d984b174412abdd2b70524d2e6e3.png]

删除列后，该列所对应的内容全部都没有了。

--------------------

### 删除表 ###

DROP [TEMPORARY] TABLE [IF EXISTS] table_name;

*  在创建表语句中加上TEMPORARY关键字，那么服务器将创建出一个临时表，该表会在你与服务器的会话终止时自动消失。
 *  TEMPORARY表的名字可以与某个已有的永久表相同，当有TEMPORARY表存在时，对应的永久表会隐藏起来（即无法访问）。
 *  为了避免重新连接后（TEMPORARY已经不存在），在未做检测的情况下调用DROP误删了对应永久表，因此在使用DROP删除临时表时需要带上TEMPORARY关键字。

删除掉user表如下：

![在这里插入图片描述][5d408642f5864ae8960fa61c457328c9.png]

--------------------

[53e929ac2ef54a339cce06ab371d705b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/eccb6340fc8945beafd003ed75ac0502.png
[5c92e4a200aa4b62bae22dd867f9d863.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/708345c30042471e995c38763e61a63d.png
[876b49e3bb4a4dbdae40727a470d7c20.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/c47b7fbd9f65463a823cfcaaa0c38988.png
[f60862fdf532458083c22c0c50682e36.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/bf1e37ba348c45d6af0bc576b3ef11b4.png
[0b41bcf9bc4c4d97a611262b63937a63.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/113119eb5faf45ee85d9480b7093743c.png
[ea946191ad1240b4b7d26f2ee9f1daa8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/fa57ec89d5c341fb8443030bb9244f12.png
[e245c8d67f4d42388676b963830185a5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/d0f0643578aa4375b0cdd41edb9d7bf6.png
[419edef701a84dad8e684311861d7ec2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/82762687a9d84ed7ba38bfe7a5dc5de2.png
[efbc9b6c3dde4650a9f37131e6024149.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/e3bd375464ea46298f26c3e0f2d80c26.png
[ae3eeb8247b644a7bd2b0bb01deb2339.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/def1868c555247449c351e958f7bb34d.png
[8b41f9532f744a83b39a8b916d1c5053.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/e6f5562be42e45c0b23c9cbd3e89149a.png
[519b9b246d6e4ef8a0da08a1e5c9dbd2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/fadd15599e904118b478686d3de4e45f.png
[c739027e9acc4206b8d61878f9a898a5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/8a73fec2cd974075b0a6789ed3808e05.png
[d5930635e3da43bbb39f94d654aaa7e1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/40da258834ff48609b07dfa5de75511f.png
[8b667a8ee49049c491afe41ad6ef1382.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/acb070a8ffb745b3b600e6fb2fc201d4.png
[1da1f14f6eec4e4ca20025b3feb702d8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/794055607edb4c319c34a169b2615cbc.png
[3decc7f78080470d942b2353371ebee7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/816d5d56185a4d05a7dcf4660d3d3217.png
[21b084b7677645fba49a722c2b0e058b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/3502169483664c5b9f26938a2c69be38.png
[e6b00e2ff1a44af79cf270a1e0bdaeb3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/296da8bd484043cba738ebcd15c24ffe.png
[0fae833a5a304c3a9b4f38c9181e6df6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/28b55151b6af400fbea5f76005b949dd.png
[05369a7b9e1d47c4a0674ddd3d4191d3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/69cc0375774345529660c6f7505f495a.png
[a880168b9981465282567508a4efa6c0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/f057485eaa27454284d4296f899b2af7.png
[3acf8b5519bd4350b545eb91757b44e1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/dcc74cd737774fa8afe6e63476f1a72a.png
[1b49d1dd2516426393d2fd9009164fe2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/9584f3b4f6f244d489fdd035403bcf39.png
[281cdcae2f044feab4f465f9ce1928ce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/a0d77cefd8d545cab75bd24e0a07bdd2.png
[dda882bda1f44fb58fe6ec761da400ff.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/4d2480777d214213a77941d341ea7af6.png
[c52445ee227c4fdf9ae657926fd2c79b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/4a385c8c593c4afbbdb9da63926e2514.png
[2d383c097cbb4298be8e66fa0a0ea0eb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/a7b8297df33b4abdb7edbae9686e1fe0.png
[2723ad09721244feba37d65836a70dcf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/ccd4d64bb3f14e219e527a9dab881edb.png
[ae5bed4488e24ec29c6d2fa6b76f29e8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/05e86ee6f0ab4e08a7c9739c189ae9ac.png
[224bf36007384d409ba04fa9659c6c64.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/69caea2f840147039eed12cc83096f6b.png
[74098fa7a2fe421c83abeb44c2e91ce2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/34aca932e7214b45be2fe21fa27310e0.png
[ff25b144d3f44b72b83d315f495df076.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/774715421aee40d9b76f4cc67a26a906.png
[c8a8d984b174412abdd2b70524d2e6e3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/189e10d5618240099e143fa07185e579.png
[5d408642f5864ae8960fa61c457328c9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/7839a63a1c8749b683407d14da06f55a.png