当前位置：首页 > news >正文

【MySQL】库的操作+表的操作

news 来源：原创 2025/7/17 17:05:30

库的操作+表的操作

1.库的操作
- 1.1创建数据库
- 1.2删除数据库
- 1.3查找数据库
- 1.4修改数据库
- 1.5数据库备份和恢复
- 1.6查看连接情况
2.库的操作
- 2.1创建表
- 2.2查看表结构
- 2.3修改表
- 2.4删除表

在这里插入图片描述

点赞???收藏???关注???
你的支持是对我最大的鼓励，我们一起努力吧???

1.库的操作

1.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: 指定数据库字符集的校验规则

show databases; //查看当前数据库

对应的就是右边一个个蓝色的目录，这我们在前面就已经说过了

在这里插入图片描述

创建数据库

create database d1;

创建好之后，在查看就有了，同样在 /var/lib/mysql 数据目录下也有一个d1目录

在这里插入图片描述

如果不想用这个数据库了，同样也可以删掉。

删除数据库

drop database d1;

在这里插入图片描述

创建数据库：本质就是在Linux环境下MySQL特定的路径(数据路径)var/lib/mysql 创建一个目录。

删除数据库：删除目录。

那如果在该路径下创建一个目录也是数据库吗？
是的没错，删除也同样是删除数据库。但是绝对不建议这样做！

在这里插入图片描述

还有一种写法，如果当前没有这个数据库就创建，如果存在就不创建

create database if not exists d1;

在这里插入图片描述

数据库创建我们已经说完，下面谈的就是数据库的编码问题！
一般数据库相关常见数据库编码有两个重要的编码，一个是数据库字符集，一个是数据库校验集。

创建数据库的时候，有两个编码集：
1.数据库编码集
2.数据库校验集

只要是数据无论是未来的存储还是读取，数据一定是要有对应的编码格式的。数据库这种与数据打交道的软件更加注重编码集。编码集必须是统一的，就是存数据之前采用什么编码，取数据时必须以同样的编码去取。

1.数据库编码集 —> 数据库未来存储数据采用的编码集

将来把数据存到数据库里，未来要对数据做各种增、删、查、改。所有只要你这样你就是要做比较。要比较对应的某些字段一样，前提是都要先被读出来，那两个字段的被读出来的校验规则或者被读取时的编码规则必须也是一样的。要不然怎么保证是一样的呢。

2.数据库校验集 —> 支持数据库，进行字段比较使用的编码。本质也是一种读取数据库中数据采用的编码格式。

所以不管是保存时用的是什么编码还是对比读取采用的编码必须得是一样的。因此数据库不论对数据做任何操作，都必须保证操作和编码必须是编码一致的。

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

show variables like 'character_set_database'; //编码集
show variables like 'collation_database'; //校验集

在这里插入图片描述
可能你的机器并不是这个编码和校验集，这是我们在安装MySQl时就已经在配置文件中把mysql服务段对应的编码全改成utf8了。

数据连接，对数据库常规操做，服务器本身校验规则都是utf8的。

在这里插入图片描述

查看数据库支持的字符集

show charset;

数据库写的时候采用什么编码

在这里插入图片描述

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

show collation;

未来数据已经有了，想怎么读，读的时候怎么知道这是我想要的，所以对数据做特定的解释，然后才做能对比。

在这里插入图片描述

现在也知道创建数据库了，也知道编码和校验规则了。两个合并下面就可以创建出指定编码和校验规则的数据库了。

当我们创建数据库或者表没有指定编码和校验规则时，系统默认以配置文件为主，如果用户在SQL语句中手动确认了自己要采用某种编码采用的就是就近原则。也就是说用户写了就用用户的，用户没写就用默认配置文件的。

下面可以看到创建数据库没有指定，系统就用默认的。

在这里插入图片描述

指定了就用指定的，有两种写法

create database d2 charset=gbk;
create database d2 character set gbk;

在这里插入图片描述

还有一种写法就是指定编码集合校验集

create database d3 charset=gbk collate gbk_chinese_ci;

在这里插入图片描述

数据库配置文件中配置这个编码有什么意义呢？
因为曾经在配置文件中设置过，所以会影响创建数据库时默认编码和校验规则，编码和校验规则是匹配的。那问题又来了，今天为什么又要设置数据库编码呢？因为数据库里是需要存表的，在数据库里可能要建各种各样的表，这些表采用编码和校验规则是继承至它所在的数据库。数据库默认依赖于mysqld，表依赖于数据库。这样就可以理解为什么要做这样的配置。

校验规则对数据库的影响

我们创建两个数据库和表，不指定编码使用的是系统默认的，但是校验集不一样。也就是说存大家用的都是utf8，但是取的时候采用的是不同的编码，就可以看到不同的现象。

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

create database test1 collate utf8_general_ci;use test1; #选择数据库 可以理解成进入数据库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');

插入的本质存数据，采用的是存储数据所用的编码。查找的时候采用的是校验规则

在这里插入图片描述

从person表中拿出来添加符合name名称符合a的信息，查的时候你怎么比较表中的值和我们设定的name=‘a’ 值是一样的，是不是要统一编码啊，所以它们它们俩个用的都是utf8_general_ci的方式。这就是我们的校验编码。然后我们可以看到结构。看到utf8_general_ci是不区分大小写的。

在这里插入图片描述

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

create database test2 collate utf8_bin;use test2;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');

这里只给我把a显示出来，这就叫做严格匹配，区分大小写

在这里插入图片描述

通过这点我们已经可以看出校验规则的不同查出来的结果可能不同，进一步理解了校验规则对我们的影响。

这里还有一个排序可以看看

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

在这里插入图片描述

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

在这里插入图片描述

1.2删除数据库

DROP DATABASE [IF EXISTS] db_ name;

执行删除之后的结果:

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

注意：不要随意删除数据库，建议做个备份。然后再删除！

在这里插入图片描述

1.3查找数据库

show databases;

在这里插入图片描述

如果你要使用某个数据库，必须要先use选择数据库

use 数据库名;

在这里插入图片描述

如果有时候数据库用久了，但是不清楚自己目前在那个数据库里，可以查看一下

select database();

在这里插入图片描述

显示创建数据库时对应的命令

show create database 数据库名;

在这里插入图片描述

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

1.4修改数据库

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

对数据库的修改主要指的是修改数据库的字符集，校验规则

如将下面数据库字符集修改成gbk

alter database test1 charset=gbk collate gbk_chinese_ci;

在这里插入图片描述

只要指明编码是什么，校验码就会自动去匹配。

1.5数据库备份和恢复

在linux中如果要对一个文件或者目录做备份实际上就是拷贝一份。有一种相当简单粗暴的做法，直接把数据库目录打个包备份一下，但是实际在用的时候一定会存在问题，比如你的数据库是5.7的，但是曾经那个数据库和表是在5.4建立的。我们严重不推荐这种做法。

还有我们不能对数据库rename做重命名，万一别人正在用这个数据库呢，什么情况下你才会对数据库进行重命名呢，有人说备份重新命名一下。那不就搞定了吗。但是这种方法还是不推荐。

我们推荐使用mysql自备的备份工具来进行备份

备份

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

示例：

mysqldump -P3306 -uroot -p -B test1 > test1.sql

在这里插入图片描述

把test1.sql打开后，可以看到里面是大部分内容我们就见过的，如craete database test1、use test1还有后面插入数据等等。其实它备份的时候，是把在这个数据库里做的有效操作全备份起来了。所以说备份的不是只有数据，还把历史上所有有效操作全部备份起来了。

在这里插入图片描述

现在把test1数据库删掉了

在这里插入图片描述

现在把这个testq.sql备份拿给别人，它的本地是没有test1的。所以它现在想恢复test1怎么恢复呢？

还原

source /home/wdl/MySQL/test1.sql

此时它可以将备份文件test1.sql里面的sql语句全部在新的MySQL里在执行一遍。

在这里插入图片描述

注意事项

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

mysqldump -u root -p 数据库名 表名1 表名2 > 数据库表备份存储的文件路径

同时备份多个数据库

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

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

备份时带了 -B 这个备份文件其实是带了create databdase test1，use test1

在这里插入图片描述

如果没有带 -B 备份的时候只会把这个数据库里面所有表信息数据信息全部备份出来，如果需要还原需要自己先把数据库建好。

所以要对数据库重命名的话，其实直接它库中表的内容备份一下，也就是备份时不要带 -B ，还原的时候创建好数据库然后source一下就好了。

1.6查看连接情况

如果在使用数据库的睡觉哦就，发现数据库在访问时卡了一些，可以去查一查当前有哪些人正在使用数据库

show processlist

在这里插入图片描述

最后说一下数据库建立好了，数据库轻易不要删！名字轻易不要改，因为上层在用！！！

2.库的操作

2.1创建表

语法：

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

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

create table if not exists user1 (
id int,
name varchar(20) comment ‘用户名’, #commen 表示对该列的文字说明
password char(32) comment ‘用户的密码’,
birthday date comment ‘用户的生日’
) charset=utf8 collate utf8_general_ci engine MyISAM;

不同的存储引擎，创建表的文件不一样。

在这里插入图片描述

user1 表存储引擎是 MyISAM ，在数据目中有三个不同的文件，分别是：

user1.frm：表结构
user1.MYD：表数据
user1.MYI：表索引

在创建一张表，发现存储引擎是InnoDB，只有两张表。为什么在建表时出现不同的表结构呢，是因为用来不同的存储引擎。具体原因后面说。

在这里插入图片描述

默认存储引擎是InnoDB，因为我们配置过

在这里插入图片描述

2.2查看表结构

我当前在直接的数据库里，我怎么知道我有那些表？每一个表的详细信息我该怎么查呢？

show tables; #查看当前数据库中的表

在这里插入图片描述

desc 表名; #查看对应表的详细结构信息

在这里插入图片描述

当时在建表时，还有表的编码、校验码、存储引擎还有描述字段，这个表更为详细的详细我应该怎么查呢？

show create table user1; #显示创建表的详细信息

在这里插入图片描述

但是这样看起来不太舒服，

show create table user1 G;

格式化显示，把不需要的符合全部去掉

在这里插入图片描述

上面和我们当时创建表写的不一样，这是因为我们写的SQL语句这是经过mysqld内词法，语法分析，把我们写的不标准的改成标准的写法。

MySQL会记录下来用户的所有操作痕迹的，包括建表、删表等行为都会被记录下来。这里看到的和曾经不一样不奇怪，因为数据库会把优化只会调整之后的操作语句记录下来。

2.3修改表

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

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);

更改表的名字

alter table user1 rename to user;

在这里插入图片描述

下面先对表进行插入

insert into user values (1,'张三','12345','2021-3-22');

如果向一个数据库中表插入数据时，理论上是在values 前面带上一个个列名称，如果省略了代表你想把这个表里面的每一个字段都要插入。

在这里插入图片描述

现在表有了数据，我们对表进行一下修改

在user表新增一列，用于保存图片路径

add后面跟的是你想新增那一列，列的类型是什么，描述是什么，after表示你想指定新增到那一列的后面

alter table user add image_path varchar(128) comment '用户头像路径' after birthday;

在这里插入图片描述

新增一列两行受影响，这是因为以前表中原始数据是没有这个头像路径默认为null。
新增字段之后对原来表中的数据没有影响。但新增字段为null。

此时我们看到创建表的语句也改过来了

在这里插入图片描述
现在问题是name 类型大小是20，现在想对指定列中的属性做修改，name 列长度由20变成60

修改列中某一字段

modify后面跟你要改谁，后面在跟新的属性

alter table user modify name varchar(60);

我们发现name字段大小确实已经变成60了，也没有影响该字段原有数据。

在这里插入图片描述

但是更为重要的是，如果刚才改字段属性就是只改了字段大小，你会发现以前name后面的comment没有了。说明并不是定向你要改那个字段就给你改那个字段，它是把新的属性直接覆盖把原来创建name字段，属性等全部覆盖掉。换句话说如果未来你想改某一列，你肯定要把这一列曾经的所有属性全部复制下来，在代码中对要改的字段修改，在重新提交一下。

在这里插入图片描述

删除某列

drop后面跟着要删除那一列

alter table user drop password;

注意：删除字段一定要小心，删除字段及其对应的列数据都没了，所有一定要保证这个数据是真的不要了。

在这里插入图片描述

修改表名

to：可以省掉

alter table user1 rename to user;
alter table user1 rename  user;

修改列名

将name列修改为xingming

 alter table user change name xingming varchar(60) DEFAULT NULL;#新字段需要完整定义

change后面跟着旧列名，再跟新列名和属性。列名称要改不仅仅需要提供新列名称，这个列的相关属性也需要都给我。相当于把这一列重新设置。

在这里插入图片描述

2.4删除表

语法:

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...drop table user;

在这里插入图片描述

像create，drop都是在做表操作，但是select并不是操作表结构，操作的是表的内容。
前面学过SQL分类有：DDL、DML、DCL。像我们目前学到的库的操和表的操作，属于那种类型的SQL呢？DDL 数据定义语言。

库的操作+表的操作

1.库的操作

1.1创建数据库

1.2删除数据库

1.3查找数据库

1.4修改数据库

1.5数据库备份和恢复

1.6查看连接情况

2.库的操作

2.1创建表

2.2查看表结构

2.3修改表

2.4删除表

相关文章：