《SQL必知必会》个人笔记(四)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 《SQL必知必会》个人笔记(四)

评价


这本书可以说是能让初学SQL的技术人员用最快速度入门的一本书,对于很多一上来不知道SQL语法觉得概念很抽象的人来说也能对与SQL领域有一个大致的了解,作为一个过来人当初觉得SQL真的挺神奇的。

这本书当然只适合新手,对于老手来说更多是快速回顾和查漏补缺,所以这一篇读书笔记将会简单提炼一些忽视的部分记录,以及工作实践之后对于书中一些建议的补充和解释。

个人已经看过非常老的小绿本的版本,在看到出到第五本之后再看看这本书还挺感慨的。

最后千万不要认为SQL很简单,其实越是看似简单的东西越是复杂


存储过程


存储过程这个东西让人又爱又恨,如果对于SQL十分了解,就会发现存储过程可以完成许多很复杂的操作,并且执行效率非常高,但是代价是极其难以阅读并且维护难度大,对于存储过程细究可以用一本书来讲述,对于大部分业务开发者来说存储过程通常是不建议或者不允许的使用的。

存储过程通常有下面的特点:

  • 因为不需要外部保证数据的一致性,所以可以简化控制并发问题
  • 存储过程保证了出错的可能性越低,因为嵌套的层级越多越容易出错
  • 简化管理,所有的逻辑都可以通过存储过程了解。
  • 安全,因为是操作数据库本身。

下面是执行存储过程的语句:


EXECUTE AddNewProduct('JTS01',
'Stuffed Eiffel Tower',
6.49,
'Plush stuffed toy with
➥the text La Tour Eiffel in red white and blue');


存储过程可能会因为下面的原因产生差异:

  • 参数可选,具有不提供参数时的默认值。
  • 不按次序给出参数,以“参数=值”的方式给出参数值。
  • 输出参数,允许存储过程在正执行的应用程序中更新所用的参数。
  • 用 SELECT 语句检索数据。
  • 返回代码,允许存储过程返回一个值到正在执行的应用程序。

创建存储过程

创建存储过程的步骤如下:


CREATE PROCEDURE MailingListCount (
ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
SELECT COUNT(*) INTO v_rows
FROM Customers
WHERE NOT cust_email IS NULL;
ListCount := v_rows;
  END;


执行这个存储过程如下:


var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;


事务(第 20课)


关于事务的相关理论这里不做过多的解释,我们只需要关注事务的4个常见术语:

  • 事务(transaction)指一组 SQL语句;
  • 回退(rollback)指撤销指定 SQL语句的过程;
  • 提交(commit)指将未存储的 SQL语句结果写入数据库表;
  • 保留点(savepoint)指事务处理中设置的临时占位符(placeholder),

可以对它发布回退(与回退整个事务处理不同)。

对于一个事务的开启通常可以使用语法begin transaction;或者begin开启一个事务,使用rollback回滚当前的事务,或者使用 保存点的方式回滚到指定保存点的位置,最后通过commit或者commit trainsaction;提交事务。

当然这些原生的事务操作接触机会比较少,更多情况下我们在框架中使用事务比较常见,这里可以参考过去过去写的文章进行回顾:

Spring 事务机制的个人总结 - 掘金 (juejin.cn)

游标

说实话对于这种几乎用不到的东西没啥学的价值,这里就直接跳过了,因为真的碰不到使用场景。


高级特性(第22课)


我认为高级特性里面都是比较重要的内容,但是不知道作者为什么要安排到最后一章节。

高级特性包含下面几点:

  • 约束
  • 索引
  • 触发器
  • 数据库安全

约束和索引

约束指的是针对某个字段的限制,而索引则是约束的具体实现。多数时候可能比较模糊的是唯一索引和唯一约束区别,主键索引和唯一索引的区别

唯一索引和唯一约束的不同点

  • 唯一约束通过唯一索引实现
  • 删除唯一约束可以删除约束但是对应的索引结构不会一并删除,所以对应列在删除约束之后依然不能删除,但是如果约束和索引一起删除就可以插入重复值。
  • 有些数据库供应商会针对构建的唯一约束创建唯一索引,但是如果单纯的创建唯一索引不会创建唯一约束。也就是说虽然实现的效果都是不能插入唯一,但是本质还是有差别的。

主键约束和唯一约束的区别

  • 表可包含多个唯一约束,但每个表只允许一个主键
  • 唯一约束列可包含 NULL 值。
  • 唯一约束列可修改或更新。
  • 唯一约束列的值可重复使用,主键值则不可以二次使用。
  • 与主键不一样,唯一约束不能用来定义外键

检查约束

检查约束比较经典的使用场景是通过乐观锁和检查约束限制交易金额不能为负数,这样可以从数据库层面保证数据的安全和完整性。

创建检查约束的案例如下:


CREATE TABLE OrderItems
(
  order_num INTEGER NOT NULL,
  order_item INTEGER NOT NULL,
  prod_id CHAR(10) NOT NULL,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  item_price MONEY NOT NULL
);


索引

索引和其他的高级特性不同,索引使用非常灵活并且没有一套非常固定的规则,在不同的业务中使用索引的方式都不同,但是在创建索引的时候依然可以遵循下面的规则:

  • 尽量保证索引的可选择性:可选择性指的这里举两个简单例子,比如性别就不适合做索引,他只有1/2的选择性,而流水订单号就很适合做索引因为他在定义上就不允许重复。
  • 查多写少的表比较适合加索引:维护索引需要维护底层的Btree树,这对于性能影响是比较大的,同时索引的直接影响是插入和更新以及删除的性能影响
  • 一张表的索引不建议超过5个。
  • 谨慎对待varchar或者char类型的索引,字符长度越长创建索引的开销和代价越大
  • 索引应该在设计表的一开始就考虑好,而不是等业务跑了很久之后突然添加索引。

关于索引的使用经验和案例可以查看下面的内容:

# 三高Mysql - Mysql索引和查询优化(偏实战部分)

# 三高Mysql - Mysql索引和查询优化讲解(偏理论部分)


数据库安全


数据库安全通常指下面的内容:

  • 对数据库管理功能(创建表、更改或删除已存在的表等)的访问;
  • 对特定数据库或表的访问;
  • 访问的类型(只读、对特定列的访问等);
  • 仅通过视图或存储过程对表进行访问;
  • 创建多层次的安全措施,从而允许多种基于登录的访问和控制;
  • 限制管理用户账号的能力。


附录


对于初学者来说可以参考下面的附录学习,另外附录中作者提到的表连接已经失效的了,建议按照附录A的内容自己创建表(使用SQL语句创建)来当做练习了。

  • 附录 B SQL 语句的语法:常见一些SQL语法速查,在自己没有编写SQL思路的时候可以参考学习。
  • 附录C - SQL 数据类型:数据类型在不同的数据库实现细节不同,附录C的数据类型虽然不值得深究但是作为对比和参考是比较合适的
  • 附录 - 保留字:保留字看起来多的吓人,但是实际上也是有技巧的,直接下载一个可视化的DB管理工具,
  • 常用 SQL 语句速查:这个不用多说,其实相当于作者告诉你哪些章节重要了
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
7月前
|
SQL 关系型数据库 MySQL
【MySQL实战笔记】02.一条SQL更新语句是如何执行的-1
【4月更文挑战第4天】SQL更新语句执行涉及查询和日志模块,主要为`redo log`和`binlog`。`redo log`先写日志再写磁盘,保证`crash-safe`;`binlog`记录逻辑日志,支持所有引擎,且追加写入。执行过程分为执行器查找数据、更新内存和`redo log`(prepare状态)、写入`binlog`、提交事务(`redo log`转commit)。两阶段提交确保日志逻辑一致,支持数据库恢复至任意时间点。
57 0
|
6月前
|
SQL 存储 关系型数据库
技术笔记:MYSQL常用基本SQL语句总结
技术笔记:MYSQL常用基本SQL语句总结
39 0
|
7月前
|
SQL 关系型数据库 MySQL
【MySQL系列笔记】常用SQL
常用SQL分为三种类型,分别为DDL,DML和DQL;这三种类型的SQL语句分别用于管理数据库结构、操作数据、以及查询数据,是数据库操作中最常用的语句类型。 在后面学习的多表联查中,SQL是分析业务后业务后能否实现的基础,以及后面如何书写动态SQL,以及完成级联查询的关键。
249 6
|
7月前
|
SQL 存储 关系型数据库
【MySQL系列笔记】SQL优化
SQL优化是通过调整数据库查询、索引、表结构和配置参数等方式,提高SQL查询性能和效率的过程。它旨在减少查询执行时间、减少系统资源消耗,从而提升数据库系统整体性能。优化方法包括索引优化、查询重写、表分区、适当选择和调整数据库引擎等。
292 3
|
7月前
|
SQL 存储 Oracle
《SQL必知必会》个人笔记
《SQL必知必会》个人笔记
46 1
|
7月前
|
SQL 存储 缓存
【MySQL实战笔记】01.一条SQL查询语句是如何执行的?
【4月更文挑战第3天】MySQL执行SQL的流程包括连接器、查询缓存、分析器、优化器和执行器。连接器负责建立连接、权限验证,查询缓存(MySQL 8.0已移除)存储查询结果,分析器解析SQL确保语法正确,优化器选择最佳索引和查询路径,执行器执行查询并管理权限。连接器使用长连接可能导致内存问题,可定期断开或使用`mysql_reset_connection`。注意,更新操作会导致查询缓存失效。
42 3
|
7月前
|
SQL 存储 关系型数据库
【MySQL实战笔记】02.一条SQL更新语句是如何执行的-2
【4月更文挑战第5天】两阶段提交是为确保`redo log`和`binlog`逻辑一致,避免数据不一致。若先写`redo log`, crash后数据可能丢失,导致恢复后状态错误;若先写`binlog`,crash则可能导致重复事务,影响数据库一致性。一天一备相较于一周一备,能缩短“最长恢复时间”,但需权衡额外的存储成本。
51 1
|
7月前
|
SQL 存储 数据库
【数据库SQL server】自学终极笔记
【数据库SQL server】自学终极笔记
212 0
|
7月前
|
SQL 存储 安全
《SQL必知必会》个人笔记(四)
《SQL必知必会》个人笔记(四)
47 0
|
7月前
|
SQL 关系型数据库 数据库
《SQL必知必会》个人笔记(三)
《SQL必知必会》个人笔记(三)
53 0