一、数据库相关介绍
1）文件存储缺点
2）数据库优点
层次模型
缺点：存储资源利用率低
网状模型
缺点：后期维护复杂，各个数据之间关系较为复杂
关系型模型 RDBMS Oracle MySQL 达梦
1）关系型数据库RDBMS
2）非关系型数据库NoSQL
2）时序数据库：Prometheus（TSDB）
二、MySQL相关
I/O thread：I/O线程，用于监听客户端连接
binlog：二进制日志，记录主服务器上的操作记录
I/O thread：I/O线程，用于读写操作，即监听并读取主服务上的二进制日志、并写到从服务器的中继日志上
sql thread：sql线程，用于将中继日志装换为sql语句、并运行
relay log：中继日志，连接master和slave，是同步的核心
| 参数名称 | rpm包 | 源码包 |
| 软件包 | C7:mariadb、mariadb-server C6:mysql、mysql-server | mysql、ncurses |
| 服务名 | C7:mariadb C6:mysqld |
|
| 进程名 | mysqld |
|
| 端口 | TCP 3306 | TCP 3306 |
| 配置文件 | /etc/my.cnf | /etc/my.cnf |
| 数据位置 | /var/lib/mysql | 安装目录下/data |
| 启停管理 | systemctl | 安装目录下/bin |
| 连接命令 | mysql -uroot -p | 安装目录下/bin/mysql -uroot -p |
上层：文件
中层：文件系统
下层：以二进制方式存储的数据块
数据库系统
上：表结构的数据体
中：存储引擎
下：文件
查看存储引擎，连接数据库后，输入：‘show engines；’，每种数据库都支持多种存储引擎，比如：
守护线程：MySQL 不需要跟用户进行交互
应用线程：MySQL 需要跟用户进行交互
注：创建主键或唯一键后自动生成对应索引
常用来作为查询条件的自段可以设置为索引
| 索引类型/约束 | 允许插入重复值 | 允许插入空值 |
| 普通索引（辅助索引） | 允许 | 允许 |
| 唯一索引（辅助索引） | 不允许 | 允许 |
| 主键索引（聚簇索引） | 不允许 | 不允许 |
含义：把存储引擎存储的文件以二维表的形式展现出来
注：开始是美国国家标准局（ANSI）制定，现在是国际化标准组织（ISO）制定，一般都是由标准语句和各厂商的方言组成
问题描述：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
解决方法：使用find命令在根下查找mysql.sock文件，将其软链接至/tmp下
#记录操作时间过长，超时的命令，帮助开发人员或运维人员，了解数据库的性能问题，来调整优化数据库。
#对数据库的操作行为做记录，只要对数据库数据数据和结构造成变化的过程的都记录
mysqlbinlog --start-datetime 'yyyy-mm-dd hh:mm:ss' --stop-datetime 'yyyy-mm-dd hh:mm:ss' 二进制日志 | mysql -uroot -p
列：mysqlbinlog --start-datetime '2023-10-15 15:02:00' --stop-datetime '2023-10-15 15:09:00' mariadb-bin.000001 |mysql -uroot -p123456
10.数据库备份
二进制日志备份： #数据库备份逻辑工具
将产生这些数据的命令按照时间和大小记录在日志文件中
mysqldump命令备份： #数据库备份命令
将现有数据库中的数据内容，逆向化为产生这些数据的命令
备份格式：mysqldump --database 库名 > 备份文件名
恢复格式：mysql < 备份文件名
--databases #同时备份多个数据库
--all-databases #备份服务器所有数据库
mysqlhotcopy（热复制）： 相当于cp -a /var/lib/mysql/hongfu/ /tmp/
直接复制数据库文件进行备份
三、语言语法tmp
select 指定列名 from 表名
#指定列名
select 指定列名 from 表名 where 字段名1=值 and 字段名2=值；
语法：create view 视图名 as 查询语句; #创建视图
select * from 视图名； #使用视图
语法：desc 表名 #查询表结构，包含表头、数据类型、非空约束、默认值约束以及其他
| 子类型 | 大小 | 用途 |
| tinyint | 1 byte | 小整数 |
| smallint | 2 bytes | 大整数 |
| int | 4 bytes | 大整数 |
| bigint | 8 bytes | 极大整数 |
| float | 4 bytes | 浮点型 |
| double | 8 bytes | 浮点型 |
| decimal | 可指定 | 小数值 |
| 子类型 | 大小 | 用途 |
| char | 0-255 bytes | 定长字符串 |
| varchar | 0-65535 bytes | 变长字符串 |
| text | 0-65535 bytes | 长文本数据 |
| blob | 0-65535 bytes | 二进制形式的长文本 |
注：定长字符串和变长字符串区别
char(10)：长了截取前10个；短了补空格，适用于固定长度需求，比如学号
varchar(10)：长了截取前10个；短了不补，此种方式比较节省空间，查询时无空格方式查询
| 子类型 | 大小 | 格式 | 用途 |
| date | 3 bytes | YYYY-MM-DD | 日期值 |
| time | 3 bytes | HH:MM:SS | 时间值 |
| year | 1 byte | YYYY | 年份值 |
| datetime | 8 bytes | YYYY-MM-DD HH:MM:DD | 混合日期和时间 |
| timestamp | 4 bytes | YYYYMMDD HHMMSS | 时间戳 |
语法：
insert into 表名 values(值1，值2….); #默认表名后方有所有列名
insert into 表名 values(值1,值2…),(值1,值2…);
insert into 表名(字段名1,字段名2…) values(值1,值2…) #指定列和值需一一对应
语法：
update 表名 set 字段名=数值; #此种方式设置该列参数值全部相同
update 表名 set 字段名=数值 where 字段名1=值1；
例如：update domain_manager set active=1 where username='jimmy';
语法：
delete from 表名；
delete from 表名 where 字段名=值；
create table 表名(
字段1 数据类型 非空约束 默认值约束,
字段2 数据类型 非空约束 默认值约束,
…
字段n 数据类型 非空约束 默认值约束
);
drop table 表名；
#注：如果不加first或者after 字段名，默认加在最后
drop index 外键索引名 on 副表名； #删除外键索引
create index 索引名 on 表名(字段名)； #创建普通索引MUL
create unique index 索引名 on 表名(字段名)； #创建唯一键索引
create index 索引名 on 表名(字段名1,字段名2)；
drop index 索引名 on 表名;
show index from 表名;
select * from 主表名 inner join 副表名 on 主表.字段名=副表.字段名; #查询两表相同部分
select * from 主表名 left join 副表名 on 主表.字段名=副表.字段名; #查询以主表为主的联表信息
select * from 主表名 right join 副表名 on 主表.字段名=副表.字段名; #查询以副表为主的联表信息
select 主表名.字段名1,主表名.字段名2,副表名.字段名1 from 主表名 right join 副表名 on 主表.字段名=副表.字段名; #查询指定字段的联表查询
注：本机访问时可省略-P、-h
mysql> create user '用户名'@'登录地址' identified by '用户密码';
注：登录地址可以以%代替，表示在任何IP地址上登录，不包括本机；新用户登录、默认只拥有test数据库权限
mysql> drop user '用户名'@'登录地址';
$ mysql -h 服务器IP -P端口 -u用户名 -p密码
mysql> grant all (privileges) on 数据库名.数据库表 to '用户名'@'登录地址';
grant all on *.* to 'root'@'localhost';
注:数据库名和数据表名可以使用*代替，表示所有
mysql> show grants for '用户名'@'登录地址';
show grants for 'root'@'localhost';
mysql> grant all on 数据库名.数据库表 to '用户名'@'登录地址' identified by '用户密码';
mysql> revoke all on 数据库名.数据库表 from '用户名'@'登录地址';
revoke select on hf2306.* from 'root'@'localhost'
注：同时赋予多个权限时，以逗号相隔，替代all
三个真实的服务器标签，和两个读写分离和负载均衡的虚拟标签。
Mysql>create table a2 (
->id int unsigned not null auto_increment, #字段要求为正数、且自增长、主键
->name char(30) not null default ‘’, #字符型长度30字节，默认值为空格
->age int not null default 0, #字段默认值为0
->primary key (id)); #设置id为主键
- 权限分类
- all (privileges)：所有权限。
- select：读取权限。
- delete：删除权限。
- update：更新权限。
- create：创建权限。
- alter：修改权限。
- drop：删除权限。
- 取消授权
- 语法
- 创建新用户的同时直接授权
- 语法
- 查看用户的授权情况
- 语法
- 用户授权
- 语法
- 远程登录MySQL
- 语法
- 删除用户
- 语法
- 创建远程连接用户
- 语法
- 数据库连接
- 可登录数据库用户控制：在mysql数据库下的user表中创建或删除用户以及对应的登录地址
- 语法1：mysql -u 用户名 -p密码 -P 端口 -h 主机 #主机为用户登录IP地址
- 语法2：mysql -u 用户名 -p密码 -S 套接字（ps:套接字是一个文件、指定了登录地址和端口）
- DCL语言
- 联表查询（无外键）
- 查看索引
- 删除索引
- 组合索引
- 索引
- 创建索引
- 单列索引
- 创建索引
- 查看数据表相关信息
- 查看数据库中所有表：show tables;
- 查看表中所有数据：select * from 表名；
- 查看表结构：desc 表名；
- 查询表中键的情况：show index from 表名;
- 查询创建表信息：show create table 表名；
- 删除列：alter table 表名 drop 字段名;
- 修改列名：alter table 表名 change 原字段名 新字段名 数据类型 [约束 默认值]；
- 修改列的类型：alter table 表名 modify 字段名 数据类型 行约束;
- 添加主键：alter table 表名 add primary key(字段1，字段2…);
- 删除主键：alter table 表名 drop primary key;
- 添加唯一键：alter table 表名 add unique key(字段1，字段2)；
- 删除唯一键：
- show index from 表名；#显示约束键索引
- drop index 约束字段名 on 表名；
- 添加自增主键：alter table 表名 modify 字段名 数值类型 约束值 auto_increment;
- 删除自增主键：alter table 表名 modify 字段名 数值类型 约束值；
- 添加外键：alter table 副表名 add constraint 外键索引名 foreign key(字段名) references 主表名(字段名) 注：两个字段名必须相同，且主表字段值必须唯一
- 删除外键：alter table 副表名 drop foreign key 外键索引名；
- 修改数据表
- 修改表名：alter table 原表名 rename 新表名;
- 增加列：alter table 表名 add 字段名 数据类型 约束值 first | after 字段名;
- 删除数据表
- 数据表级别的增删改查
- 创建数据表
- 数据库级别的增删改查
- 查看所有数据库： show databases;
- 创建数据库：create database 名字;
- 删除数据库：drop database 名字;
- 切换数据库：use 名字;
- 更改数据库名：更改对应的目录名
- /var/lib/mysql/目录名
- /usr/local/data/目录名
- DDL语言
- 删除where后指定字段所在行
- 删除表中所有数据
- 删除数据记录
- 修改指定行记录
- 修改所有记录
- 修改数据记录
- 添加指定列
- 同时添加多行数据
- 添加所有列
- 添加数据记录
- 非空约束NULL（列约束之一）
- NO：表示不能为空，如果添加数据记录时某列或者某字段未设定参数，有默认值约束时，以默认值约束参数为准；未设置默认值约束时，系统会自动填写当前类型的默认值
- 字符串 ：默认值是空字符串
- 日期 ：默认值是0000-00-00
- 数值：默认值是0
- YES：表示可以为空，如果添加数据记录时某列或者某字段未设定参数，系统自动填写当前参数为NULL，不受默认值约束影响
- NO：表示不能为空，如果添加数据记录时某列或者某字段未设定参数，有默认值约束时，以默认值约束参数为准；未设置默认值约束时，系统会自动填写当前类型的默认值
- 默认值约束DEFAULT（列约束之一）
- 非空约束为NO时，可以通过设置DEFAULT来更改对应数据类型的默认值，比如：数值类型原有默认值为0，可根据需求将某列或某字段的DEFAULT设为1，来更改参数的默认值
- 日期和时间类型
- 字符串类型
- 数据类型（用于语句中时，除数值类型外，都要用一对单引号包裹）
- 数值类型
- 查询表结构
- DML语言
- 存储过程(stored procedure)：是将一组SQL语句作为一个整体来执行的数据库对象，通过被调用来运行；和函数一样，是一条或多条SQL语句的集合，区别函数是已经定义好的、存储过程是用户自己定义的；
- 视图：将查询结果集的sql语句封装成一张可视化的表，动态SQL语句、根据数据表的数据而更新
- 查询指定列指定行数据
- 单列：列名
- 多列：列名1,列名2,…
- 所有：*
- 查询表中所有列数据
- DQL（查询）语言
- 中继日志：relay_log，主从结构中的同步的二进制日志（从服务器）
- 二进制日志：log_bin，记录改变数据结构的语句（主服务器）
- 错误日志：log-error,记录服务器启动，关闭，运行过程中错误的信息。
- 查询日志：general_log，记录所有查询语句（不启用）
- 慢查询日志：log-slow-queries，显示时间较慢的查询，数据库调优时可以使用此日志
- 数据库日志分类
- 问题解决
- SQL名称大小写规则
- 对语句关键字忽略大小写；
- 对数据库名和表名严格区分大小写；
- 对列名、列名别名忽略大小写；
- 对变量名严格区分大小写；
- 每句语句后面需有;结尾
- DDL（数据定义语言）：数据库（database）的增删改查、数据表（table）的增删改查
- DML（数据操作语言）：数据记录的增删改
- DQL（数据查询语言）：数据记录的查
- DCL（数据控制语言）：数据用户的权限，包含用户的创建、授权等
- SQL语句(结构化查询语言)分类
- 数据库文件
- 表结构文件：*.frm
- 数据文件：*.MYD
- 数据表索引文件：*.MYI
- 索引文件存储的key-value
- 主键索引的key是主键的值、value是改行的数据记录,主键索引文件中存储了索引值和该行所有数据
- 辅助索引的key是索引列的值、value是改行主键的值，辅助索引文件中只存储了索引值和id
- 组合索引：在表中多个字段组合上创建索引
- 作用：加快sql查询
- 分类：
- 单列索引：在表中某个字段上创建索引
- 索引
MySQL存在缺陷：单用户请求无法分担至多核心调用- 垂直扩展：更换服务器硬件
- 水平扩展：增加服务器节点，比如 amoeba
- 缓存：利用 redis 集群添加缓存，减轻压力
- 线程重用：默认开启
- 分区（不同服务器中跑不同的数据库）、分表（将同一服务器中一个库分为两个不同的DBMS中，但是需要理清数据类型之间的关联）、读写分离
MySQL优化方案
MySQL：单进程多线程的工作模式，每个线程维持一个连接- MyISAM是mysql最开始时的默认存储引擎
- 优：插入和查询数据较快
- 缺：不支持事务、行级锁及外键约束等功能
- InnoDB是5.5版本之后的默认存储引擎，对比MyISAM具有一些新特性：
- 行级锁：对比之前的锁表，行级锁表示使用者编辑当前行时，对其锁定，除使用者外其余人不得查看和修改当前行内容；
- 事务：一起执行多条sql语句，是一个整体，要成功则都成功；若失败都失败；
- 外键：是表和表之间的关联
- 缺点：插入数据和查询数据不如MyISAM快
- Memory是内存式数据库
- 优：数据存取速度非常快
- 缺：数据不能持久化
- 作用：
- 存储数据
- 建立索引
- 展示数据
- 更新数据
存储引擎
文件系统- 相关参数
- 从服务器：
- 主服务器：
- 主从服务过程
- 主从服务器原理
- 原理
- 版本
- 社区版：开源、免费版，不提供官方技术支持
- 企业版：开源、收费版，可以适用30天
- 集群版：开源、免费版
- 高级集群版：收费版
- 表间约束（外键）
- 副表添加外键，且添加外键字段的值必须在主表中存在
- 无外键时，主表、副表可独立的增删改查
- 添加外键后
- 主表可随意添加，副表必须添加主表中存在的数据；
- 主表和副表不能更新已关联字段的数据；
- 先删除副表中关联数据，再删除主表中关联的数据
- 唯一键约束
- 作用：设置唯一键后的列字段值不能重复，但可以为空（优先级低于非空约束，只校验非空参数）
- 设置：一个数据表中可以有多个唯一键，唯一键可以设置在一列或者多列上
- 域约束(域完整性)
- 非空约束
- 默认值约束
- 行约束（实体完整性）
- 主键约束（建议在创建表时创建主键）
- 作用：设置主键后的列字段值不能重复，插入的数据不能为空，优先级大于非空约束；设为联合主键的列字段值不能完全重复，比如将姓名和学号两列设为联合主键，可以姓名相同，同时学号不能相同；或者学号可以相同，但是姓名不能相同
- 设置：一个表只有一个主键，但这个主键可以设置在多列（字段）上，设在多列上时称为联合主键
- 自增主键
- 作用：用于字段值自动增加，数据类型必须是数值，比如学号、序号相关参数
- 设置：自增主键基础要求字段首先是主键，方才可设为自增主键
- 主键约束（建议在创建表时创建主键）
- 关系型 数据库约束
- 非关系型数据库种类
- 键值存储：Redis（内存级数据库）
- -持久化（将内存空间中的数据存储在外部存储介质中）
- 每个单独的项都存储为键值对
- 面向文档：MongoDB
- 每个键与称之为文档的复杂数据结构配对
- 宽列存储：HBase
- 将数据列存储、取代行
- 键值存储：Redis（内存级数据库）
- 优势
- 支持存储更多的格式：key-value形式、文档形式、列存储等，更灵活
- 善于处理海量数据的维护和处理
- 速度快、效率高，内存式存储，可做持久化（存储在硬盘中）
- 扩展简单、高并发、稳定、成本低
- RDBMS是所有现代数据库系统的基础
- 代表：MySQL、SQLServer、Oracle等
- 数据存储在被称为表(table)的数据库对象中
- 表是由相关的数据项的集合，由行和列组成
- 数据结构RDBMS – database – table - row | column - 字段（属于自己的数据类型）
- 文件存储在磁盘或硬盘中
- 数据库分类（基于存储数据格式）
数据库系统模型（数据库中存储的是元数据）- 支持加密技术、提供安全性保证
- 支持多用户环境，允许用户并行访问和操作数据
- 支持为不同用户设置不同权限
- 数据共享
- 读写速度太慢
- 文件内容明文存储
- 不能多人同时操作
- 数据量大时索引效率低下
- 多文件存储时数据关联差
- 数据库诞生原因
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
