Sql

导言

要面试了，感觉这些mysql基础知识还是要看看。

SQL 基本语法

• WHERE 子句用于提取那些满足指定条件的记录。
• HAVING 子句可以让我们筛选分组后的各组数据。在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

SQL 表示 size in (30,50) 怎么写

在SQL中,要过滤大小在30到50之间的数据,可以使用以下方法表示:

SELECT *
FROM table
WHERE size BETWEEN 30 AND 50

等价的另一种表达方式是:

SELECT *
FROM table 
WHERE size >= 30 AND size <= 50

SQL 表示 size in [30,50] 列表 怎么写

使用IN运算符可以指定具体的多个值:

SELECT * 
FROM your_table_name
WHERE size IN (30, 50);

这个查询会选择表中size列的值为30或50的行。

IN运算符允许WHERE子句中指定一个值列表,如果字段的值出现在这个列表中,则选中该行。

因此,使用IN运算符可以非常方便地指定多值条件。

您的示例:

WHERE size IN (30, 50);

等价于使用OR条件:

WHERE size = 30 OR size = 50; 

使用IN运算符可以使语句更简洁易读。

IN也支持区间查询:

WHERE size IN (30, 40, 50); // 选择30、40或50

这种INTERVAL语法同样清晰简洁。

所以,SQLite确实支持在WHERE子句中使用IN运算符指定多值过滤条件。这很有助于简化SQL查询语句。

聚合函数

在SQL中,聚合函数(Aggregate Functions)是对一组值进行计算,返回一个单一值的函数。

常见的聚合函数包括:

1. COUNT - 计算行数

例如:

SELECT COUNT(*) FROM table; 

1. SUM - 求和

例如:

SELECT SUM(salary) FROM employees;

1. AVG - 平均值

例如:

SELECT AVG(age) FROM users; 

1. MAX - 最大值

例如:

SELECT MAX(price) FROM products;

1. MIN - 最小值

例如:

SELECT MIN(order_date) FROM orders;

聚合函数一般会与GROUP BY结合使用,对不同的分组进行聚合计算。

它们可以高效地从多行数据中计算出一个汇总结果,用于统计和分析,是SQL的重要组成部分。

事务

事务的特性(ACID)

• 原子性Atomicity：是指事务包含所有操作要么全部成功，要么全部失败回滚。（某项全部加一之类的
• 一致性Consistent：指事务必须使数据库从一个一致性状态变换成另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
• 拿转账来说，假设用户 A 和用户 B 两者的钱加起来一共是 5000，那么不管 A 和 B 之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是 5000，这就是事务的一致性。
• 隔离性Isolation：是当多个用户并发访问数据库时，比如操作同一张表时，数据表为每个用户开启的事务，不能被其他事务所干扰，多个并发事务之间要相互隔离。
• 导致三种并发操作问题：脏读、幻读、不可重复读
• 有四种事务隔离级别设置：包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）
• 持久性Durable：持久性是指一个事务一旦被提交，那么对数据库中的数据的改变就是永久的，即便是在数据库系统遇到故障的性况下也不会丢失提交事务的操作。

并发操作问题

• 脏读：脏读是指在一个事务处理过程中读取到了另外一个未提交事务中的数据。
• 不可重复读（前后多次读取，数据内容不一致）：不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。
• 虚读(幻读)（前后多次读取，数据总量不一致）：幻读发生在当两个完全相同的查询执行时，第二次查询所返回的结果集跟第一个查询不相同。
• 比如两个事务操作，A 事务查询状态为 1 的记录时，这时 B 事务插入了一条状态为 1 的记录，A 事务再次查询返回的结果不一样。
• 不可重复读和脏读的区别是：
• 脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。
• 不可重复读 与 幻读的区别：
• 幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。
• 不可重复读是读取了其他事务更改的数据，针对update操作
  • 解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。
• 幻读是读取了其他事务新增的数据，针对insert和delete操作
  • 解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

事务的隔离级别

• Serializable(串行化)：可避免脏读、不可重复读、幻读。（就是串行化读数据）
• 仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。
• Repeatable read(可重复读)：可避免脏读、不可重复读的发生。
• 读取数据的事务不允许写事务继续访问该行数据(避免了不可重复读)，但是未提交的写事务将会禁止其他事务访问该行。
• 但还是有幻读，因为表项的操作没有被避免。
• Read committed(读已提交)：可避免脏读的发生。
• 授权读取：允许不可重复读取，但不允许脏读取。
• 这可以通过“瞬间共享读锁”和“排他写锁”实现。
  • 读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。
• Read uncommitted(读未提交)：最低级别，任何情况都无法保证。
• 未授权读取：允许脏读取，但不允许更新丢失。
• 如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。
• 该隔离级别可以通过“排他写锁”实现。

Trade off: 隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

在 MySQL 数据库中，支持上面四种隔离级别，默认的为 Repeatable read (可重复读)；而在 Oracle 数据库中，只支持 Serializable (串行化)级别和 Read committed (读已提交)这两种级别，其中默认的为 Read committed 级别。

悲观锁或乐观锁

• 对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。
• 读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。
• 乐观锁机制采取了更加宽松的加锁机制，大多是基于数据版本（ Version ）记录机制实现。
• 将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

索引

• 当表中的数据量越来越大时，我们可以通过额外存储数据结构- 索引
• 更通俗的说，索引就相当于书的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。从而实现将查询性能提高好几个数量级
• 缺点
• 时间方面：创建索引和维护索引要耗费时间，
  • 具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间。

为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

红黑树

• 是每个结点都带有颜色属性的二叉查找树，颜色或红色或黑色。在二叉查找树强制一般要求以外，对于任何有效的红黑树我们增加了如下的额外要求:
• 性质1. 结点是红色或黑色。
• 性质2. 根结点是黑色。
• 性质3. 所有叶子都是黑色。（叶子是NIL结点）
• 性质4. 每个红色结点的两个子结点都是黑色。（从每个叶子到根的所有路径上不能有两个连续的红色结点）
• 性质5. 从任一结点到其每个叶子的所有路径都包含相同数目的黑色结点。

B树

• 内部（非叶子）节点可以拥有可变数量的子节点（数量范围预先定义好）
• 当数据被插入或从一个节点中移除，它的子节点数量发生变化。为了维持在预先设定的数量范围内，内部节点可能会被合并或者分离。
• 因为子节点数量有一定的允许范围，所以B树不需要像其他自平衡查找树那样频繁地重新保持平衡，但是由于节点没有被完全填充，可能浪费了一些空间。
• B树中每一个内部节点会包含一定数量的键，键将节点的子树分开。
• 例如，如果一个内部节点有3个子节点（子树），那么它就必须有两个键： a1 和 a2 。
• 左边子树的所有值都必须小于 a1 ，中间子树的所有值都必须在 a1 和a2 之间，右边子树的所有值都必须大于 a2 。
• 注意：B树的非叶子节点也存储了数据

B树变种

• B-树
• 没有B减树，就是B树，减号其实是横杠。
• B+树
• 数据都在叶子节点上：值的拷贝被存储在内部节点；键值和记录存储在叶子节点；
  • 数据对象的插入和删除仅在叶节点上进行。
• 并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址，以加速顺序访问。
• 相比BTree来说，进行范围查找时只需要查找两个节点，进行遍历即可。
• B*树

N个节点的B+树，高度最优是多少

• 假设B+树的度为d（节点中最大子节点数目），根节点的高度为H
• 由于数据都存储在叶子结点，所以高度为log_d(N)

对比

• HASH
• 查询等值很快，但是没有顺序，不支持范围查询和排序
• 哈希碰撞时，效果差
• 二叉树
• 相对于多叉树的B树，树高过高。

参考文献

评论