MySQL 转 PostgreSQL (holo)

语法区别

3. 关于日期、时间的提取

将 timestamp 类型的数据，把小时、分钟等提取出来。

--mysql
    SELECT hour(time) AS timeHour
    FROM   cart
    WHERE  ......;

--PostgreSQL (holo) 方法一
    SELECT DATE_PART('hour', time) as timeHour
    FROM   cart
    WHERE  ......;

--PostgreSQL (holo) 方法二
    SELECT EXTRACT(hour FROM time) AS timeHour
    FROM   cart
    WHERE  ......;

其中DATE_PART(field, source) 与 extract() 两者等效，第一个参数可选以下内容：
* century 世纪
* decade 年份 ÷ 10
* year 年
* month 对于timestamp值，它是一年里的月份数(1-12)；对于interval值，它是月的数目，然后对 12 取模(0-11)
* day 日
* hour 小时
* minute 分钟
* second 喵
* microseconds 秒域(包括小数部分)乘以 1,000,000 。请注意它包括全部的秒。
* milliseconds 秒域(包括小数部分)乘以 1,000 。请注意它包括完整的秒。
* dow 每周的星期几，星期日（0），星期六（1）
* doy 一年的第 x 天
* epoch
* isodow
* isoyear
* timezone
* timezone_hour
* timezone_minute

(参考资料 / 详细资料连接)

GROUP BY

将MySQL中包含GROUP BY的代码，直接迁移到PGSQL中，有概率会遇到这个问题：
[42803] ERROR: column "table.colName" must appear in the GROUP BY clause or be used in an aggregate function

错误：”cart.id”列必须出现在GROUP BY子句中或在聚合函数中使用

2021-02-082021-09-30

SQL 基础语法

点我下载原图（SQL必知必会.png）

如果下面的内容看不清，可以点击图片放大哦~

2021-01-162021-09-30

数据库的三范式

范式概念

在关系型数据库中，设计数据库时要考虑遵循一定的规则，这种规则就是范式。

实际上，数据库一共有六种范式，但一般设计数据库的时候，只需要遵守到3NF（第三范式）即可。
1NF 2NF 3NF BCNF 4NF 5NF

第一范式 1NF

第一范式主要强调原子性，即数据库的每一列都是一个不可再分割的基本数据项。

例：下表不满足第一范式

学号	姓名	家庭电话及住址
23333	马猴烧酒	166 xxxx xxxx，唐辛子星

修改后即可满足第一范式

学号	姓名	家庭电话	住址
23333	马猴烧酒	166 xxxx xxxx	唐辛子星

第二范式 2NF

第二范式主要强调唯一性，即数据表中每行都必须可以被唯一地区分，主要是为了防止数据重复，通常是使用一个主键来唯一标识一条记录。
满足第二范式就肯定满足第一范式。

完全依赖、部分依赖：
因为主键可以由多列共同组成，
当且仅当该行数据由主键中所有列共同确定时，我们说这条数据完全依赖于主键。
当该行数据仅需要主键中的部分列即可确定时，我们说这条数据部分依赖于主键。

例：完全依赖

学号（主键）	科目（主键）	分数
23333	数学	99

分数完全依赖于学号和科目，二者缺一不可。

例：部分依赖

学号（主键）	姓名（主键）	科目（主键）	分数
23333	张三	数据库原理及应用	88

分数部分依赖于上述三个主键，原因：学号就可以确定出某位学生的姓名，分数依赖于科目和(学号或姓名)~~在没有重名的情况下~~。

第三范式 3NF

非主属性之间不能相互依赖，必须直接依赖候选关键字。
例：下表不满足第三范式

学号（主键）	姓名	班级	学生电话	班主任姓名	班主任电话
23333	张三	985211	168 xxxx xxxx	李四	189 xxxx xxxx
23334	王五	985211	199 xxxx xxxx	李四	189 xxxx xxxx

班主任的姓名、班主任的电话跟主键学号没有直接的关系，只有间接的关系（可以通过学生的班级了解到班主任的姓名电话）。故该表的非主属性班主任姓名、班主任电话依赖该表的非主属性班级，且间接依赖关键字学号。

修改后满足第三范式：

学号（主键）	姓名	班级	学生电话
23333	张三	985211	168 xxxx xxxx
23334	王五	985211	199 xxxx xxxx

班级	班主任姓名	班主任电话
985211	李四	189 xxxx xxxx

直接依赖、传递依赖（函数依赖、间接依赖）：
a依赖b，b依赖c，则a肯定依赖c，这就是传递依赖。

例：

学号	姓名	班级	班主任姓名
23333	张三	985211	李四

班主任姓名依赖于班级，班级依赖于学号，所以班主任姓名传递依赖学号，直接依赖班级。

2020-12-102021-09-30

MySQL之事务与隔离级别（概要篇）

事务

事务的概念

事务就是一组原子性的SQL查询，或者说是一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么久执行该组查询。如果其中有任何一条语句因为崩溃或者其他原因无法执行，那么所有的语句都将不会执行。
也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。

经典例子：“银行应用”

假设要从用户A的银行卡账户转到用户B的银行卡账户200元钱，应该怎么做？

应该至少需要三个步骤：
1. 检查用户A的账户余额是否高于200元？
1. 从用户A的银行卡账户中减去200元。
1. 在用户B的银行卡账户中加上200元。

上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。

可以用 START TRANSACTION 语句开始一个事务，然后要么使用 CONNIT 提交事务将修改的数据并持久保留，要么使用 ROLLBACK 撤销所有的修改。

事务的SQL样本如下：

START TRANSACTION;
SELECT balance FROM savings WHERE customer_ id = 111;
UPDATE savings balance = balance - 200. 00 WHERE customer_ id = 111;
UPDATE savings SET balance = balance + 200. 00 WHERE customer_ id = 111;
COMMIT;

试想一下，如果执行到第4条语句的时候，服务器奔溃了，会发生什么？
也许用户A因此损失了200元。也许执行到第3到第4条语句之间，服务器奔溃了，用户B因此白得到了200元。

除非系统通过严格的ACID测试，否则空谈事务的概念是远远不够的。

什么是ACID？

ACID表示 原子性（atomicity） 、 一致性（consistency） 、 隔离性（isolation） 和 持久性（durability） 。一个运行良好的事务处理系统，必须具备这些标准的特征。

原子性（atomicity）：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一个部分操作，这就是事务的原子性。
一致性（consistency）：数据库总是从一个一致性的状态转换到另外一个一致性的状态。在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，A账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。
隔离性（isolation）：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外一个关于A账户付款程序开始运行，则其看到的A账户的余额并没有被减去200美元。
持久性（durability）：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

在实际应用中，若想实现ACID特效非常地难，甚至可以说是一个不可能完成的任务。

就像各种锁一样，实现ACID的数据库相比没有实现的数据库，需要花费更多的CPU时间。

事务的隔离级别

2020-12-092021-09-30

MySQL的并发控制（概要篇）

并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制问题。

如果一个用户正在读取一张表，而另一位用户正在试图删除这张表中第25行，会产生什么结果？结论是不确定的，读取的用户可能会因此报错退出，也有可能读到了信息不一致的表。

如何解决这类问题呢？

在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为 共享锁（shared lock） 和 排他锁（exclusive lock），也叫 读锁（read lock） 和 写锁（write lock）

读锁：读锁是共享的，是相互不阻塞的，多个客户在同一时刻可以同时读取同一个资源，而不互相干扰。
写锁：写锁是排他的，一个写锁会阻塞其他写锁和读锁，只有这样，才能确保在同一时间里只有一个用户能执行写入操作，并防止其他用户读取正在写入的同一资源。
写锁比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面（写锁可以插入到锁队列中读锁的前面，反之读锁则不能插入到写锁的前面）。

锁粒度

加锁、获得锁、检查锁、释放锁等都会增加系统的开销，且在任何时候，在给定的资源商，锁定的数据越少，则系统的并发程度越高。
由上，我们应该寻找到合适的锁策略，即在锁的开销和数据的安全性之间寻求平衡，以达到最高的性能。
在MySQL中，两种最重要的锁策略是 表锁（Table lock） 和 行级锁（row lock）。

表锁：表锁是MySQL中最基本的锁策略，并且是开销最小的策略。尽管存储引擎可以管理自己的锁，但是MySQL本身还是会使用各种有效的锁来实现不同目的。
行级锁：行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。行级锁只在存储引擎层实现，而MySQL服务器层没有实现。

2020-12-092021-09-30

MySQL逻辑结构

MySQL逻辑架构

第一层：服务不是MySQL独有的，大多数基于网络和客户端/服务器的工具或者服务都有类似的架构。每个客户端连接都会在服务器进程中拥有一个线程。

第二层：大多数MySQL核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如时间、日期、数学和加密函数等），所有跨存储引擎的功能都在这一层实现（例如存储过程、触发器、视图等）。

第三层：存储引擎。其负责MYSQL中数据的存储和提取。服务器通过API与存储引擎进行通信。存储引擎不会去解析SQL，不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器的请求。