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

【MySQL初阶】--- 库和表的操作

article 2024/12/24 19:03:39

 Welcome to 9ilk's Code World

       

(๑•́ ₃ •̀๑) 个人主页:        9ilk

(๑•́ ₃ •̀๑) 文章专栏：     MySQL  

🏠 库的操作

📌 创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,
create_specification] ...]

create_specification:
    [DEFAULT] CHARACTER SET charset_name
    [DEFAULT] COLLATE collation_name

说明：

1. 创建数据库时是DATABASE 不是 DATABASES。

2. SQL中，中括号[]限定的区域称为可选项目，可选可不选。

3. 大写的表示关键字。

4. CHARACTER SET: 指定数据库采用的字符集。

5. COLLATE: 指定数据库字符集的校验规则。

示例 :

IF NOT EXISTS：

create database if not exists db_name;

现象 : 当创建不存在的数据库时无什么差别;当创建已经存在的数据库时,如果加上IF NOT EXISTS此时只会报警告,否则会报错。

📌系统编码与创建数据库

（1）数据库的编码问题：

1. 创建数据库时有两个编码集，一个是数据库编码集，一个是数据库校验集。

2. 数据库编码集对应数据库未来存储所采用的编码集;数据库校验集是用来支持数据库进行字段比较的编码,本质也是一种读取数据库中数据的采用的编码格式。

🌰 : 比如你在纸上用中文(数据库编码集)写上一段话,拿到段话的可能是韩国人,美国人,中国人,但是你读取时也必须得是中国人或者说是懂中文的人才能看懂。

3. 数据库无论对数据库做任何操作，都必须保证操作和编码必须是编码一致的。

（2）查看系统默认字符集以及校验规则：

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

（3）查看不同校验场景的校验规则：

show variables like 'collation_%';

（4）查看数据库支持的字符集

show charset;

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

show collation;

（6）使用指定编码来创建数据库

创建数据库时指明字符集：

create database db_name charset=utf8;
create database db_name character set utf8;

创建数据库时指明校验规则：

create database db_name collate utf8_general_ci;
create database db_name charset=utf8 collate utf8_general_ci;//指定字符集和校验规则

注：如果用户创建数据库时没有指定字符集或校验规则，此时默认以配置文件为主，如果如上用户手动指定了就用用户指定的(就近原则)，否则就使用默认配置文件的。

Q：有什么方式能查看数据库设置的字符集和校验规则吗？

MySQL5.0x：我们可以在var/lib/mysql路径下查找对应数据库目录，进入目录查找opt文件。(db.opt 文件被用来存储数据库的字符集和排序规则）

MySQL8.0x : 在现代版本的MySQL中，字符集和排序规则等信息已经被转移到数据字典中进行管理。因此，MySQL 不再需要 db.opt 文件。但是我们可以使用SQL语句查询。

USE information_schema;
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM SCHEMATA 
WHERE SCHEMA_NAME = 'your_database_name';
//SCHEMA_NAME：数据库名称。
//DEFAULT_CHARACTER_SET_NAME：默认字符集（编码方式），如 utf8mb4。
//DEFAULT_COLLATION_NAME：默认校验规则，如 utf8mb4_general_ci。

Q：数据库层面文件中配置编码有什么意义？数据库层面操作设置编码又有什么操作？

1. 在数据库配置文件中设置编码会影响创建数据库时的默认编码和检验规则，两者是匹配的。

2.数据库未来需要建各种各样的表，有存各种各样数据的需求，而这些表采用的编码方式继承于各自所在的数据库，而数据库默认依赖于mysqld，表依赖于数据库。

（7）验证不同校验规则的影响

我们要验证的是采用相同的字符集但是不同校验规则有什么影响，也就是存一样，取出用不同方式。我们会插入数据，而插入本质是存，采用character;接着是查找，也就是取，采用校验规则。

1. 不区分大小写：

创建一个数据库，校验规则使用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');

select * from person; //从表查所有数据 *是通配符号
select * from person where name='a';

我们要查找a但是返回给我们的是Aa,此时说明该校验规则不区分大小写。

2. 区分大小写：

创建一个数据库，校验规则使用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');

select * from person; //从表查所有数据 *是通配符号
select * from person where name='a';

此时说明该校验规则区分大小写。

3. 区分和不区分大小写排序

//test2
select * from person order by name; //默认升序大写比小写小

//test1
select * from person order by name; //默认升序大写比小写小

此时校验时a和A不做区分，整体从小到大。

总结：校验集会影响比较结果。我们往后数据库创建自由按照系统默认配置创建就行了。

📌 查看数据库

(1) 查看数据库列表：

show databases;

数据库对应的是/var/lib/mysql下的一个个目录。

注：你查看但不代表你能使用这个数据库，应该use使用。

(2) 查看选中了哪个数据库：

select database();

(3) 查看创建数据库时的一些设定选项

show create database 数据库名；

  • MySQL 建议我们关键字使用大写，但是不是必须的。

  • 数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字。
  • /*!40100 DEFAULT CHARACTER SET gbk */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话。

📌 删除数据库

DROP DATABASE [IF EXISTS] db_ name;

执行删除之后的结果：

1. 数据库内部看不到对应的数据库。

2. 对应的数据库文件夹被删除，级联删除，里面的数据表全部被删除。

总结：特别不建议对数据库直接删除，最好做好备份再删除！

📌 修改数据库

ALTER DATABASE db_name
[alter_spacification [,alter_spacification]...]

alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name
  • 对数据库的修改主要指的是修改数据库的字符集以及校验规则。

示例：将myset数据库字符集修改为gbk。

📌 数据库备份与还原

（1）备份数据库

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

打开test1.sql之后我们发现，文件大部分内容都是我们见过的，比如create database，use test1,create table person等。其实所谓备份，它不只是把数据备份，还把在数据库里做的有效操作全备份了，把我们整个创建数据库，建表，导入数据等语句都装载进这个文件。

（2）还原数据库

source 还原的路径;

我们可以发现不仅把数据还原,还把操作也还原了。

（3）注意事项

  • 如果备份的不是整个数据库，而是其中的一张表
mysqldump -u root -p 数据库名 表名1 表名2 > D:/mytest.sql
  • 同时备份多个数据库
mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径
  • 如果备份一个数据库时，没有带上-B参数， 在恢复数据库时，需要先创建空数据库，然后使用(use)数据库，再使用source来还原。

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

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

(5) 查看连接情况

可以用来查看当前哪些人在使用数据库。

show processlist;

说明 :

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

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

总结库的操作:

1. 创建数据库: create database;

2. 删除数据库: drop database;

3. 修改数据库: alter database;

4. 查看数据库: show databases;

5. 选中数据库: use db_name;

6. 查看选中哪个数据库: select database();

7. 查看创建数据库时设定的选项: show create database;

8. 数据库备份: mysqldump -P3306 -u root -p 密码 -B 数据库名 > 备份路径

9. 数据库还原: source 还原路径

🏠 表的操作

📌 创建表

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

说明 :

  • field 表示列名。

  • datatype 表示列的类型。
  • character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准。

  • collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准。

示例：

create table users (
  id int,
  name varchar(20) comment '用户名',
  password char(32) comment '密码是32位的md5值',
  birthday date comment '生日'
) character set utf8 engine MyISAM;
//comment是描述

我们发现使用不同存储引擎，创建表的文件不一样。不同存储引擎对磁盘文件的个数要求不同user3 表存储引擎是 MyISAM ，在数据目中有三个不同的文件，分别是：user3.sdi：表结构 user3.MYD：表数据 user3.MYI：表索引。

📌 查看表

（1） 查看库中有哪些表

show tables;

（2）查看表的详细信息

desc 表名;

说明 : 

  • Field列: 代表字段名字。
  • Type列: 代表字段类型
  • Null列: 代表是否为空。
  • Key列：代表索引类型。
  • Default列: 代表默认值。
  • Extra列: 代表扩充。

(3) 查看创建表时的详细信息

show create table 表名;
show create table 表名 \G

📌 修改表

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

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 table1 rename to table2;

注：to可以省略。

  • 在表中插入记录
 insert into user2 values(1,'a','b','1982-01-04'),(2,'b','c','1984-01-
04');

  • 在表中添加一个字段
alter table user2 add image_path varchar(128) comment '用户头像路径' after birthday;

插入新字段后，对原来表中的数据没有影响,原来数据依然存在。

  • 修改列中字段
alter table user2 modify name varchar(60);

注：

1. modify后不仅要跟改的列还要跟新属性。改完不影响该字段原有数据。

2. 改完字段属性之后我们发现name后面comment没有了，这说明并不是定向改，而是把新的属性直接覆盖到原来的name字段，属性等全部覆盖。 

  • 删除表中的列
alter table user2 drop password;

注：删除字段一定要小心，删除字段及其对应的列数据都没了，也是最好做完备份再删！

  • 修改列名
mysql> alter table employee change name xingming varchar(60); --新字段需要完整定义

注：修改列名不仅要提供新列名还要提供相关字段。

📌 删除表

drop table 表名;

注：删除表跟删除库的操作虽然简单，但是都需要慎重考虑，决定删之前先做好备份!

总结表的操作：

1. 创建表：create table;

2. 查看库中所有表 : show tables;

3. 查看表详细信息: desc table_name;

4. 查看创建表时选项: show create table;

5. 修改表: alter table + add / modify / drop / rename / change  

6. 删除表: drop table;


http://www.kler.cn/a/449684.html

相关文章：

  • Cherno C++学习笔记 P46 箭头运算符
  • Vue3中路由跳转之后删除携带的query参数
  • 如何根据一系列提交文件，匹配对应的git提交记录？用ai
  • JVM系列(十三) -常用调优工具介绍
  • 数据流图和流程图的区别
  • java全栈day19--Web后端实战(java操作数据库3)
  • （2024.12）Ubuntu20.04安装openMVS＜成功＞.colmap＜成功＞和openMVG＜失败＞记录
  • gitlab克隆仓库报错fatal: unable to access ‘仓库地址xxxxxxxx‘
  • 【国产NI替代】基于国产FPGA+兆易创新GD32F450的全国产16振动+2转速（24bits）高精度终端采集板卡
  • DeepFaceLab技术浅析（五）：面部替换
  • Git(9)之创建新空白分支
  • 【爬虫开发】爬虫开发从0到1全知识教程第11篇：Mongodb数据库,介绍【附代码文档】
  • 力扣第114题：二叉树展开为链表
  • 使用MATLAB判断矩阵是否正定的方法与例程
  • Spring Boot注解总结大全【案例详解，一眼秒懂】
  • Linux网络——网络基础
  • 基于Spring Boot的图书管理系统
  • C语言基础——指针（4）
  • WebRTC服务质量（09）- Pacer机制（01) 流程概述
  • Nautilus源码编译傻瓜式教程一
  • 20241230 基础数学-线性代数-（1）求解特征值（numpy, scipy）
  • 如何调大unity软件的字体
  • 大恒相机开发（3）—大恒相机工业检测的实际案例
  • css 裁剪 clip-path
  • STM32MP1linux根文件系统目录作用
  • 深入探索鸿蒙NEXT：设计原理、架构揭秘与ArkTS应用开发【书籍推荐】