【MYSQL】 mysql库和表的操作--详解

一.库的操作1.1 创建数据库创建数据库create database db_name; -- 本质就是在 /var/lib/mysql 创建一个目录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: 指定数据库字符集的校验规则创建数据库案例创建名为 db1 的数据库create database db1;当我们创建数据库没有指定字符集和校验规则时系统使用默认字符集utf8校验规则是utf8_general_ci创建一个使用utf8字符集的db2数据库create database db2 charsetutf8;创建一个使用utf字符集并带校对规则的db3数据库。create database db3 charsetutf8 collate utf8_general_ci;1-2删除数据库DROP DATABASE [IF EXISTS] db_ name;执行删除之后的结果:数据库内部看不到对应的数据库对应的数据库文件夹被删除级联删除里面的数据表全部被删注意不要随意删除数据库1-3认识mysql的编码创建数据库的时候有两个编码集数据库编码集 —— 数据库未来存储数据数据库校验集 —— 支持数据库进行字段比较使用的编码本质也是一种读取数据库中数据所采用的编码格式数据库无论对数据做任何操作都必须保证操作和编码是一致的1-3-1字符集和校验规则查看系统默认字符集以及校验规则show variables like character_set_database; show variables like collation_database;collation_connection当前连接使用的校验规则collation_database当前数据库的默认校验规则collation_server服务器级别的默认校验规则utf8mb4_0900_ai_ci的含义utf8mb4编码集支持完整Unicode包括emoji0900基于Unicode 9.0标准ai口音不敏感accent insensitiveci大小写不敏感case insensitiveshow variables like collation_database;这条指令用于查看当前数据库的校验规则。show variables like collation_%;这条指令用于查看所有以collation开头的系统变量。这里的%是一个通配符代表任意字符所以它会显示出包括连接、数据库、服务器等各级别的校验规则。1-3-2查看数据库支持的字符集show charset;字符集主要是控制用什么语言。比如utf8就可以使用中文。1-3-3查看数据库支持的字符集校验规则show collation;1-4创建数据库时的编码设置1. 默认创建不指定编码create database d1;当创建数据库没有指定字符集和校验规则时系统使用默认字符集utf8校验规则是utf8_general_ci在 Linux 底层MySQL 会在/var/lib/mysql/目录下创建对应的数据库文件夹里面会生成一个db.opt文件记录编码信息centos7[rootVM-8-5-centos mysql]# cat d1/db.opt default-character-setutf8 default-collationutf8_general_ci2. 指定字符集创建数据库create database d2 charsetutf8; create database d3 charsetutf8;两种写法效果相同查看底层文件[rootVM-0-3-centos mysql]# cat d2/db.opt default-character-setutf8 default-collationutf8_general_ci3. 指定字符集和校验规则创建数据库create database d4 charsetutf8 collate utf8_general_ci;4. 使用 GBK 字符集创建数据库create database d5 charsetgbk collate gbk_chinese_ci;查看 GBK 编码的数据库配置[rootVM-0-3-centos mysql]# cat d5/db.opt default-character-setgbk default-collationgbk_chinese_ciubbuntu22.04ubuntu22.04不能进行cat操作所以我选用的是mysql操作这个可以进行MySQL数据库 db.opt 文件缺失问题排查CentOS7 vs Ubuntu 22.04问题背景我在学习MySQL数据库时执行 CREATE DATABASE d1; 创建了数据库想通过查看 /var/lib/mysql/d1/db.opt 文件来确认字符集和校验规则但在Ubuntu 22.04环境中遇到了问题MySQL中 SHOW DATABASES 能看到 d1 且数据库内有数据、可正常使用Bash中 /var/lib/mysql/ 下存在 d1 目录但目录内没有 db.opt 文件修改目录权限也无法解决而CentOS 7环境中可以正常看到该文件环境差异说明环境 MySQL版本 系统特性 db.opt 文件表现CentOS 7中的MySQL 5.7 传统发行版MySQL数据目录权限严格 创建数据库后自动生成 db.optUbuntu 22.04 MySQL 5.7或8.0 Debian系发行版MySQL默认配置有差异 可能出现 db.opt 未生成的情况补充MySQL 8.0版本彻底取消了 db.opt 文件字符集配置统一存储在系统表 mysql.schemata 中这是Ubuntu 22.04默认安装MySQL 8.0时的常见情况。原因分析核心原因db.opt 是MySQL 5.7及之前版本中创建数据库时自动生成的物理文件用于记录该数据库的默认字符集和校验规则。CentOS 7中MySQL 5.7的默认安装流程完整创建数据库时会正常生成该文件Ubuntu 22.04中可能因为安装配置差异、权限初始化问题、创建时异常中断导致 db.opt 未生成即使 db.opt 缺失 SHOW DATABASES 从系统表读取数据所以仍能显示数据库存在d1 不是系统自带数据库是手动创建的不会默认生成 db.opt修改权限无法生成 db.opt 因为该文件仅在创建数据库时自动生成后期权限调整不会触发重新生成解决方案方案1用SQL命令直接查看字符集无需依赖 db.opt 这是最稳妥、跨版本通用的方法无论是否有 db.opt 都能生效-查看d1数据库的字符集和校验规则SHOW CREATE DATABASE d1;方案2手动创建 db.opt 文件MySQL 5.7适用如果需要恢复 db.opt 文件可以手动创建并配置权限# 进入d1数据库目录 cd /var/lib/mysql/d1 # 写入字符集配置 echo default-character-setutf8 db.opt echo default-collationutf8_general_ci db.opt # 修正文件权限必须为mysql:mysql否则MySQL无法读取 chown mysql:mysql db.opt chmod 660 db.opt # 重启MySQL服务生效 systemctl restart mysql方案3重建数据库适合有数据备份的情况# 1. 备份d1数据库数据 mysqldump -u root -p d1 d1_backup.sql # 2. 登录MySQL删除d1 mysql -u root -p DROP DATABASE d1; # 3. 重新创建d1指定字符集确保生成db.opt CREATE DATABASE d1 CHARACTER SET utf8 COLLATE utf8_general_ci; # 4. 恢复数据 mysql -u root -p d1 d1_backup.sql总结:1. 版本差异MySQL 8.0不再生成 db.opt 字符集查看请用 SHOW CREATE DATABASE2. 环境差异CentOS和Ubuntu的MySQL安装配置不同可能导致 db.opt 生成异常3. 最佳实践优先使用SQL命令查看字符集避免依赖物理文件 db.opt 缺失不影响数据库正常使用4. 思路先确认数据库逻辑存在→再查物理目录→最后针对性解决1-5校验规则对数据库的影响不区分大小写创建一个数据库校验规则使用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);不区分大小写的查询以及结果mysql use test1; mysql select * from person where namea;插入数据并查询数据结果排序不区分大小写排序以及结果mysql use test1; mysql select * from person order by name;select是从表里查询数据通配符*代表将表里的所有列数据都显示出来。插入的本质是存储数据采用default-character-setutf8也就是插入数据时所用的编码格式。查询的本质是按照校验集的要求来查找和比较数据。具体来说插入数据时使用编码集将数据编码后存入磁盘查询数据时使用校验集的规则来比较字段如where条件、order by排序、group by分组等两者必须匹配如果插入时用的编码集和查询时用的校验集不兼容就会导致比较结果出错甚至查不到本该存在的数据。区分大小写创建一个数据库校验规则使用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);进行查询区分大小写的查询以及结果mysql use test2; mysql select * from person where namea;结果排序区分大小写排序以及结果mysql use test2; mysql select * from person order by name;注意这里一般默认是升序排序。1-6数据库的删改查数据库删除DROP DATABASE [IF EXISTS] db_ name;执行删除之后的结果:数据库内部看不到对应的数据库对应的数据库文件夹被删除级联删除里面的数据表全部被删注意不要随意删除数据库显示创建语句show create database 数据库名;说明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将 test2 数据库字符集改成 gbk这个是在centos5.7下运行的可以用cat查到mysql alter database test2 charsetgbk collate gbk_chinese_ci; Query OK, 1 row affected (0.00 sec)[rootVM-0-3-centos mysql]# cat test2/db.opt default-character-setutf8 default-collationutf8_bin [rootVM-0-3-centos mysql]# cat test2/db.opt default-character-setgbk default-collationgbk_chinese_ciubuntu22.04直接在mysql上查-- 查看当前数据库的字符集和校验规则 show variables like character_set_database; show variables like collation_database; -- 或者查看数据库的创建语句会显示编码信息 show create database test2;说明对数据库的修改主要指的是修改数据库的字符集校验规则查看数据库show databases;查询当前所在数据库select database();1-7数据库备份和恢复1.备份语法# mysqldump -P3306 -u root -p 密码 -B 数据库名 数据库备份存储的文件路径;# -P 端口号大写P默认3306可省略 # -p 密码小写p直接跟密码中间没有空格 mysqldump -P3306 -u root -p123456 -B test1 test1.sql 为了避免密码暴露在命令行中会被 history 记录更安全的做法是 # 不写密码执行后会提示交互式输入 mysqldump -P3306 -u root -p -B test1 D:/test1.sql # 然后系统会提示Enter password: 你输入密码不显示我们将test1进行备份这个文件实际上就是把我们的数据库相关的所有数据以及表等放到这个文件当中也可以称为物理文件2.还原语法mysql source /root/MySQL/test1.sql我刚直接输入指令西显示没有文件存在我就先if了一下确定恢复时没有先确保没有任何数据库占用冲突你直接source的时候如果test1数据库已经存在可能会报错或者跳过。两种恢复方式的区别情况一备份时带了-Bmysqldump -u root -p -B test1 test1.sql;恢复时直接source /root/MySQL/mytest.sql;不需要先建库、不需要use因为文件里已经有CREATE DATABASE和USE语句。情况二备份时没带-Bmysqldump -u root -p test1 test1.sql;恢复时必须先手动创建数据库create database mytest; use mytest; source /root/MySQL/mytest.sql;如果你直接source而没有先建库和use就会出问题。3. 注意事项如果备份的不是整个数据库而是其中的一张表怎么做mysqldump -u root -p 数据库名 表名1 表名2 test1.sql;同时备份多个数据库mysqldump -u root -p -B 数据库名1 数据库名2 ... 数据库存放路径;如果备份一个数据库时没有带上-B参数 在恢复数据库时需要先创建空数据库然后使用数据库再使用source来还原。4. 查看连接情况语法show processlist;列名含义你的例子中的具体值解读Id连接的线程ID唯一标识一个客户端连接5、23、27User连接所用的用户名event_scheduler系统内部事件调度器、root你的管理员账户Host客户端连接来源localhost表示本机连接localhost:57982表示本机的某个端口Db当前连接正在使用的数据库如果已选中test1表示正在使用 test1 库NULL表示没有选中任何库Command当前连接正在执行的命令类型Daemon后台守护进程、Sleep空闲、Query正在执行查询Time当前状态持续的时间秒5秒、5545秒约1.5小时、0秒State线程当前的状态Waiting on empty queue等待任务、NULL、init初始化Info正在执行的SQL语句如果Command是Queryshow processlist就是你刚执行的这条命令二.表的操作2-1创建表语法CREATE TABLE table_name ( field1 datatype, field2 datatype, field3 datatype ) character set 字符集 collate 校验规则 engine 存储引擎;说明field 表示列名datatype 表示列的类型character set 字符集如果没有指定字符集则以所在数据库的字符集为准collate 校验规则如果没有指定校验规则则以所在数据库的校验规则为准说明不同的存储引擎创建表的文件不一样。users 表存储引擎是 MyISAM 在数据目中有三个不同的文件分别是users.frm表结构users.MYD表数据users.MYI表索引示例MySQL 5.7不同的存储引擎创建表的文件不一样。(1) user1 表存储引擎是 MyISAM在数据目录中有三个不同的文件[rootVM-0-3-centos user_db]# ll total 128 -rw-r--r-- 1 mysql mysql 61 May 19 9:00 db.opt -rw-r--r-- 1 mysql mysql 8645 May 19 9:02 user1.frm -rw-r--r-- 1 mysql mysql 0 May 19 9:02 user1.MYD -rw-r--r-- 1 mysql mysql 1024 May 19 9:02 user1.MYIuser1.frm表结构user1.MYD表数据user1.MYI表索引(2) user2 表存储引擎是 InnoDB在数据目录中有两个不同的文件[rootVM-0-3-centos user_db]# ll total 128 -rw-r--r-- 1 mysql mysql 61 May 19 9:00 db.opt -rw-r--r-- 1 mysql mysql 8645 May 19 9:02 user1.frm -rw-r--r-- 1 mysql mysql 0 May 19 9:02 user1.MYD -rw-r--r-- 1 mysql mysql 1024 May 19 9:02 user1.MYI -rw-r--r-- 1 mysql mysql 8645 May 19 9:02 user2.frm -rw-r--r-- 1 mysql mysql 98304 May 19 9:02 user2.ibduser2.frm表结构user2.ibd表数据和索引创建 user3 表默认存储引擎mysql create table if not exists user3(name char(32)); Query OK, 0 rows affected (0.02 sec)查看文件[rootVM-0-3-centos user_db]# ll total 236 -rw-r--r-- 1 mysql mysql 61 May 19 9:02 db.opt -rw-r--r-- 1 mysql mysql 8645 May 19 9:02 user1.frm -rw-r--r-- 1 mysql mysql 0 May 19 9:02 user1.MYD -rw-r--r-- 1 mysql mysql 1024 May 19 9:02 user1.MYI -rw-r--r-- 1 mysql mysql 8645 May 19 9:02 user2.frm -rw-r--r-- 1 mysql mysql 98304 May 19 9:02 user2.ibd -rw-r--r-- 1 mysql mysql 8560 May 19 9:03 user3.frm -rw-r--r-- 1 mysql mysql 98304 May 19 9:03 user3.ibd表默认的存储引擎是 InnoDB。ubuntuUbuntu 输出可以看到user1表同时存在user1.MYD、user1.MYI和user1_369.sdi文件user2表只有user2.ibd文件这说明在 MySQL 8.0 中即使你指定engineMyISAM创建了表它依然会生成一个.sdi文件替代旧版的.frm同时也会生成.MYD和.MYI文件。但默认存储引擎是 InnoDB所以不指定引擎时创建的表如user3、person只会生成.ibd文件。你实际看到的文件对应关系表名存储引擎生成的文件user1MyISAMuser1.MYDuser1.MYIuser1_369.sdiuser2InnoDBuser2.ibdpersonInnoDB默认person.ibd为什么 user1 没有.frm而是.sdiUbuntu 装的是MySQL 8.0而笔记里的 CentOS 是MySQL 5.7。MySQL 5.7 及之前表结构存在.frm文件MySQL 8.0表结构存在数据字典中同时导出为.sdi序列化字典信息文件所以找不到user1.frm是正常的8.0 里对应的是user1_369.sdi。备注创建一个engine是innodb的数据库观察存储目录2-2查看表查看表结构desc 表名;显示表的详细信息show create table user1;show create table user1 \G;\G格式化显示把不需要的符号去掉。\G格式化显示去掉不需要的符号。2-3修改表在项目实际开发中经常修改某个表的结构比如字段名字字段大小字段类型表的字符集类型表的存储引擎等等。我们还有需求添加字段删除字段等等。这时我们就需要修改表。语法LTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]...); ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column datatype]...); ALTER TABLE tablename DROP (column);将user1改为user(to可以省略在表中添加记录insert into tablename values(插入各项内容);在表中添加一个字段alter table tablename add (column datatype [DEFAULT expr][,column datatype]...);插入新字段后对原来表中的数据没有影响2-4修改属性alter table tablename modify(column datatype[DEFAULT expr][,column datatype]...);不是指定进行修改所以要将所修改属性后面的内容也加上否则将直接覆盖原有内容。2-5删除password列注意删除字段一定要小心删除字段及其对应的列数据都没了alter table tablename drop(column);2-6修改列名alter table tablename change(column newcolumn datatype[DEFAULT expr][,column datatype]...);新字段需要完整定义属性也要带上2-7删除表语法格式DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...