带你了解现行数据库的高级特性和新方法

最新推荐文章于 2024-07-22 15:12:37 发布
最新推荐文章于 2024-07-22 15:12:37 发布
阅读量824 收藏 11
点赞数 26
分类专栏： 数据库详细基础教程 文章标签： 数据库 sql 后端 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2301_80263861/article/details/140011254
版权

数据库的高级特性和新方法

阅读指南：
本文探讨了数据库的高级特性和新方法，详细介绍了这些高级特性及其操作方式，并涵盖了一些最新的操作方法。如果读者感兴趣，我们将会在后续的数据库教程合集里更新更多高级操作内容，欢迎大家与我们一起学习和讨论。这篇文章是本系列的最后一篇，感谢大家的阅读和支持。
合集链接：
数据库详细基础教程

数据库的高级操作

数据库事务

数据库事务概述：

  • 数据库事务是一套操作数据命令的有序集合，一个不可分割的工作单位。

  • 事务中单个命令不会立刻改变数据库数据，当内部全部的命令都生效且成功时，才算一次成功，如果有任一任务失败，可以进行状态回滚

  • 事务哟事务开始与事务结束之间执行的全部数据库操作组成

事务的作用：

  • 为数据库提供了一个从失败中恢复到正常状态的方法
  • 当对个应用程序在并发访问数据库时，可以有效避免彼此的操作互相干扰

♦️事务的ACID特性：

  1. 原子性（Automicity）

​ 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

  1. 一致性（Consistency）

​ 一致性是指事务内部的操作的状态前后一致，即成功则都成功，失败则都失败。

  1. 隔离性（Isolation）

​ 隔离性是指一个事物的执行不能被其他事物干扰，即一个事物内部的操作即使用的数据对并发的其他事务是隔离的，并发实现的各个事务之间都不能相互干扰。

  1. 持久性（Durability）

​ 持久性是指一个事物一旦被提交，他对数据库中数据的改变就是永久性的，后面的操作对其不成影响。

事务的开启，提交，回滚：

前提：

​ MySQL默认情况下是自动提交事务的。

​ 默认每一条语句都是一个独立的事务，一旦成功就提交了。语句失败报错就回滚。

​ 那要将多条语句作为一个事物，有如下方法

方法一：手动提交模式

# 开启手动提交模式，这个模式的生命周期只在一次服务时间内，也就是说，如果关闭了sql服务，则再次打开时需要再一次开启，最好在终端进行操作
SET AUTOCOMMIT = FALSE;
或 
SET AUTOCOMMIT = 0;

# 恢复自动提交
SET AUTOCOMMIT = TRUE; 
或 
SET AUTOCOMMIT = 1;

# 查看是否自动提交
SHOW VARIABLES LIKE 'AUTOCOMMIT';

# 成功 —> 提交，失败 -> 回滚
    COMMIT;     ROLLBACK;

方法二： 自动提交模式下开启独立事务

START TRANSACTION;	# 开启独立事务

..........	# 多个sql命令

COMMIT; 或 ROLLBACK;

注：
事务只支持在update，insert，delete这类对数据产生变化的方法中，不支持删表，删库那种操作。

事务的隔离性：

​ 一个书屋内部的操作即使用的数据对并发的其他事物是隔离的，并发执行的各事务之间不能相互干扰

隔离级别概述脏读不可重复读幻读
read-uncommitted读未提交事务数据√√√
read-committed读已提交事务数据（orcle默认）×√√
repeatable-read可重复度（MySQL默认）××√（小概率）
aseializable串行化和序列化×××

脏读：一个事务读取了另一个事务未提交的数据

不可重复读取：一个事务读取了另一个事务所提交的修改数据，不符合数据的一致性

幻读：一个事务读取了另一个事务新增，删除的记录情况，与实际情况不同。

# 改变隔离级别
SET TRANSACTION_ISOLATION = '隔离级别';

# 查看隔离级别
SELEC T@@TRANSACTION_ISOLATION;

注：

隔离等级越高，越安全，但是性能越低。

用户权限控制

​ 授予的权限可分为三个级别：数据库级别，表级别或特定操作上。

创建用户语法：

CREATE USER 'username' @ '服务器ip地址（%代表所有ip）' IDENTIFIED BY 'password';
# username 表示要创建的用户的用户名
# password 表示创建的用户的用户名

赋予权限语法：

# 1. 赋予全部权限
GRANT ALL PRIVIEGES ON 库名(*代表全部).表名(*代表全部) TO 'username' @ '服务器ip地址（%代表所有ip）';
# 2.指定库和权限
GRANT SELECT, INSERT ON 库名.表名 TO 'username' @ '服务器ip地址（%代表所有ip）';

回收权限语法：

# 撤销全部权限
REMOVE ALL PRIVILEGES ON database_name.* FROM 'username' @ 'IP';
# 撤销部分权限
REMOVE SELECT, INSERT,UPDATE ON database_name.table_name FROM 'username' @ 'IP';

查看权限语法：

# 查看权限
SHOW GRANTS FOR 'username' @ 'IP';

# 查看有用户列表
SELECT User, Host FROM mysql.user;

删除用户语法：

# 删除用户
DROP USER '用户名';

数据的备份与还原

全量备份

# 备份单库和单表的数据
mysqldump -u username -p database_name 表名>backup.sql

# 备份单库和多表
mysqldump -u username -p database_name 表名1 表名2...>backup.sql

# 备份单库的所有表
mysqldump -u username -p database_name > backup.sql

#-p如果写密码，必须紧贴着
# 以上命令必须在未连mysql的状态下执行（CMD执行）

全量恢复

# 还原数据
mysql -u username -p database_name < backup.sql

# 需要提前准备数据库，导入已存在的数据库，同时，数据库的MySQ版本要兼容

Binlog运行日志

​ Binlog日志是一个MySQL的二进制的日志记录，里面记录了数据库所有的增删改查的操作，同时也包括了操作的执行时间，所以，我们可以通过该日志查询误删除数据的恢复，增量复制，主从同步等。

Binlog配置文件的地址

​ 默认在：MySQL服务/my.ini

​ 其中有一部分是

[mysqld]
……
datadir = MySQL服务/Data    # 默认的存储详细Binlog的二进制文件地址
……
log-bin = Binlog的文件名	# 此处Binlog的文件名默认是以电脑名称命名，可以修改

对日志的操作（以下操作在 SQLyog 中使用）：

示例：

# 清空原有日志文件
RESET MASTER;

# 准备数据，插入数据 --> 00000001日志文件
CREATE DATABASE text_binlog;
USE text_binlog;
CREATE TABLE table_binlog(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL
);
INSERT INTO table_binlog (NAME) VALUES ('二狗子'),('驴蛋蛋');

# 重启一个新的日志文件 --> 00000002日志文件
FLUSH LOGS;		# 创建一个新日志

# 将删除数据和插入数据植入到第二个日志文件中-->00000002日志文件
DELETE FROM table_binlog WHERE id = 2;	  # 删除驴蛋蛋
INSERT INTO table_binlog(NAME) VALUES('狗剩子');
SELEC *FROM table_binlog;

# 查看日志文件目录
SHOW BINARY LOGS;

# 查兰某个日志的命令清单
# 语法：
SHOW BINARY EVENTS;		查看第一个日志的清单
SHOW BINARY EVENTS IN '清单名' [FROM pos(具体信息位置) LIMIT OFSET, NUMBER]0; 
# 实例：
SHOW BINARY EVENTS IN 'MY_LOGIN.00000002' FROM 391 LIMIT 1, 3;

CMD下的日志操作：

注：在binlog的文件目录下进行cmd

# 跳过步骤找回数据
mysqlbinlog my-logbin.000001> d:/my_binlog.000001.sql # 将其他的日志完整导出
mysqlbinlog --stop-POSITION=删除命令的开始的pos my-logbin.000002> d:/my_binlog.391.sql # 02日志删除之前
mysqlbinlog --start-POSITION=删除命令的下一个命令开始pos my-logbin.000002> d:/my_binlog.441.sql # 02日志删除之后

数据库的新特性

窗口函数的使用

窗口函数：

  • 序号函数: row_number()

  • 序号函数: rank()

  • 序号函数: dense_rank()

  • 分步函数: PERCENT_RANK()

    (rank - 1) 1 / (rows - 1)

  • 前后函数: LAG(expr,n)

    LAG(expr,n)函数返回当前行的前n行的expr的值。

  • 首尾函数: FIRST_VALUE(expr)

    FIRST_VALUE(expr)函数返回第一个expr的值。

示例：

数据的准备

CREATE TABLE goods(
 id INT PRIMARY KEY AUTO_INCREMENT,
 category_id INT,
 category VARCHAR(15),
 NAME VARCHAR(30),
 price DECIMAL(10,2),
 stock INT,
 upper_time DATETIME
);


INSERT INTO goods(category_id,category,NAME,price,stock,upper_time)
VALUES
(1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00'),
(2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00'),
(2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00'),
(2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
(2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00'),
(2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00'),
(2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00');

使用聚合函数的方式

# 聚合函数也是窗口函数 [理解]
# 查询所有的商品编号,价格,和类别名以及整体平均价格

SELECT id , NAME ,price, category , AVG(price) OVER () FROM goods;

SELECT id , NAME , price , category , AVG(price) OVER() FROM goods ;

# 查询所有的商品编号,价格,和类别名以及类别平均价格
SELECT id , NAME ,price, category , AVG(price) OVER (PARTITION BY category) FROM goods;

SELECT id , NAME , price , category , AVG(price) OVER(PARTITION BY category_id) FROM goods ;

使用窗口函数：

# 查询 goods 数据表中每个商品分类下价格降序排列的各个商品信息。
SELECT ROW_NUMBER() OVER(PARTITION BY category ORDER BY price DESC) AS num,id , category , NAME ,price FROM goods;

SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id,
 category_id, category, NAME, price, stock FROM goods;
# 窗口函数 over (partition by 分组 order by 排序 )

# 查询 goods 数据表中每个商品分类下价格最高的3种商品信息。
SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, 
id, category_id, category, NAME, price, stock FROM goods) t
 WHERE row_num <= 3;
  

# 使用RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
    id, category_id, category, NAME, price, stock
    FROM goods;

# 使用RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息
 SELECT * FROM(
    SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
    id, category_id, category, NAME, price, stock
    FROM goods) t
    WHERE category_id = 1 AND row_num <= 4;


# 使用DENSE_RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id, category_id, category, NAME, price, stock  FROM goods;

# 使用DENSE_RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息。
SELECT * FROM( SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,  id, category_id, category, NAME, price, stock FROM goods) t WHERE category_id = 1 AND row_num <= 3;



# 计算 goods 数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值。
# 写法一：
SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,PERCENT_RANK() 
OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,id, category_id, category, 
NAME, price, stock FROM goods WHERE category_id = 1;

# 写法二:
SELECT RANK() OVER w AS r,
     PERCENT_RANK() OVER w AS pr,
     id, category_id, category, NAME, price, stock
     FROM goods
     WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);
     


# 查询goods数据表中前一个商品价格与当前商品价格的差值。
SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
   FROM (SELECT  id, category, NAME, price,LAG(price,1) OVER w AS pre_price FROM goods
   WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;


# 按照价格排序，查询第1个商品的价格信息。
SELECT id, category, NAME, price, stock,FIRST_VALUE(price) OVER w AS first_price
    FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
Alfredorw
关注
  • 26
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
计算机三级数据库技术
weixin_64870046的博客
03-14 1113
一、考试内容及要求 1. 掌握数据库技术的基本概念、原理、方法和技术。 2. 能够使用SQL语言实现数据库操作。 3. 具备数据库系统安装、配置及数据库管理与为维护的基本技能。 4. 掌握数据库管理与维护的基本方法。 5. 掌握数据库性能优化的基本方法。 6. 了解数据库应用系统的生命周期及其设计、开发过程。 7. 熟悉常用的数据库管理和开发工具，具备用指定的工具开发简单数据库应用系统的能力。 8. 了解数据库技术的最新发展。 二、考试系统使用说明 ## 题型及内容 范围预测 1. 数据库应用系统分
数据库设计坏味道
weixin_52405713的博客
09-23 149
数据库设计坏味道： 之前讨论了代码中的坏味道，这个很多人讲过，也比较常见。本文讨论的是数据库设计中的坏味道。 很多人会忽略数据库设计中的坏味道，而实际上我个人经验而言，不好的数据库结构，会造成灾难性的后果，比不合理的代码的危害程度高很多。因为在业务运行以后，数据库的修改成本非常高，会出现修修补补的情况，以至于屎山越堆越高。 常见的数据库坏味道： 大宽表 开玩笑讲，如果可以，我能用一张表搞定一个业务，当然是不行的。但有时候也不需要完全按三范式拆分得特别零散，那怎样判断呢？我个人的经验是：如果模型A与模型B是
数据库系统工程师——第一章 计算机系统知识
✨ 欢迎来到【Seal ^_^ 的CSDN博客】！这里是我记录技术心得、分享经验的地方。✨
04-07 2546
计算机的基本硬件系统由运算器、控制器、存储器、输入设备和输出设备 5 大部件组成运算器、控制器等部件被集成在一起统称为中央处理单元(Central Processig Unit，CPU)CPU 是硬件系统的核心，用于数据的加工处理，能完成算术运算、逻辑运算及控制功能。
HTTP 和 HTTPS 的区别，带你全面掌握高级知识点
m0_60607838的博客
09-01 114
四、HTTPS 相对于 HTTP 的改进 1、双向的身份认证 客户端和服务端在传输数据之前，会通过基于X.509证书对双方进行身份认证 。具体过程如下： 客户端发起 SSL 握手消息给服务端要求连接。 服务端将证书发送给客户端。 客户端检查服务端证书，确认是否由自己信任的证书签发机构签发(客户端内置了所有受信任 CA 的证书)。 如果不是，将是否继续通讯的决定权交给用户选择 ( 注意，这里将是一个安全缺陷 )。如果检查无误或者用户选择继续，则客户端认可服务端的身份。 服务端要求客户端发
数据库基础
m0_57321930的博客
04-19 2035
目录 基本知识与关系模型 基础概念 从用户角度来看数据库管理系统的功能 从系统实现角度看数据库管理系统的功能 数据库语言 数据库系统的标准结构 三级模式： 两个印象： 两个独立性： 数据模型 三大经典数据模型 其他数据模型 数据库系统的演变与发展 关系模型与关系数据库语言的关系 关系模式与关系 关系的特性 关系代数基本运算 关系代数的扩展操作 关系代数的基本书写思路 集合操作思维训练注意点 书写关系代数的基本思路 复杂扩展操作 关系演算的重难点 数据库语言
系统架构设计师 6：数据库设计
拉里小猪的博客
06-29 1627
怎么还越写越少了？
一、快速入门 MongoDB 数据库
Amo Xiang的博客
03-15 903
快速入门 MongoDB 数据库
数据库系统-SQL
ifeng
03-15 216
DDL（Data Definition Language）数据库定义语言：Create Alter DropDML（Data Manipulation Language）数据操纵语言：Insert Delete UpdateDCL（Data Control Language）数据库控制语言 ：Grant、RevokeTCL（Transaction Control Language）事务控制语言char(n):固定长度的字符串varchar(n):可变长度的字符串。
计算机三级 数据库技术 学习笔记
RanLZ的博客
02-09 5876
计算机三级 数据库技术 第一章 数据库应用系统开发方法 1.1 数据库应用系统生命周期 1.1.1 软件工程与软件开发方法 瀑布模型 快速原型模型 螺旋模型 1.1.2 DBAS生命周期模型 ps.ps.ps.按照瀑布模型原理设计 DBASDBASDBAS 的生命周期由以下五个基本活动组成: 项目规划 需求分析 系统设计 实现与部署 运行与维护 333条设计主线: 数据组织与存储设计 数...
财税实务：软件和集成电路产业现行优惠政策汇总.pdf
11-29
财税实务：软件和集成电路产业现行优惠政策汇总.pdf
二手车价格评估方法的比较和应用.docx
02-04
二手车价格评估是汽车行业不可或缺的一部分，尤其在买卖、投资和保险理赔等场合，精确的估价至关重要。...了解和掌握这些评估方法，对于购车者、卖家、保险公司和汽车行业从业者来说，都是至关重要的。
一种有效获取染料光吸收和散射系数的新方法
02-11
提出一种获取染料光吸收和散射系数的新方法。较之国际现行的库贝尔卡-芒克(K-M)法更有效地消除了在实际测量中，由于存在着光透射和界面内、外反射等因素而造成的数据误差，改善了各染料之间的加和性。实验结果表明，...
GB 14048 低压开关设备和控制设备 全22部分最新版现行有效
05-24
GB 14048 低压开关设备和控制设备 全22部分最新版现行有效 GB 14048.1-2012 低压开关设备和控制设备 第1部分：总则 GB 14048.2-2008 低压开关设备和控制设备 第2部分：断路器 GB/T 14048.3-2017 低压开关设备和...
OAuth2.0 or Spring Session or 单点登录流程
qq_53374893的博客
07-20 440
一句话精辟解释： 在过滤器放行的时候，偷偷的把原生的 request 和 response 对象换成了它的实现，也就是 调用getSession 的时候拿到的 其实是对redis 操作的Session。但其他的操作还是使用原生的，只不过重写的方法，调用的是子类的，也就是往 redis 里放入 Session，把原生的操作给替换了！分布式Session原理，使用子域名，的时候放入父域名的 JsessionId 然后将这个ID 的所存的内容放入Redis ，这样实现不同域名共享同一sessionID.
redis+spring面试题
github_36510643的博客
07-21 412
redis+spring面试题
基于JSP的高校信息资源共享平台
最新发布
heye0910032的博客
07-22 659
高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。通过使用JSP技术和MySQL数据库，本系统实现了一个稳定、安全、易用的高校信息资源管理平台。虽然在开发过程中遇到了一些技术挑战，但通过不断学习和实践，最终完成了一个能够满足用户需求的系统。未来，我们将继续优化系统功能，提升用户体验，以期达到更高的应用价值和社会效益。高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。
docker容器
2401_83945639的博客
07-20 950
root@lvs-server ~]# docker start 8a1e7f458daf --手动start启动。-v 目录映射【宿主机的目录映射给容器的某个目录，将容器中的目录挂载到宿主机的目录中】docker rm -f $(docker ps -aq) 删除所有的容器。docker rm -f cname 强制删除，可以删除正在运行的容器。-p 端口映射【宿主机的端口映射给容器的某个端口】创建容器并运行 --这个是mysql的容器。docker ps -q 查看所有容器id。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值