SQL琐碎知识

1、【悲观锁】

总是假设最坏的情况，每次在拿数据的时候，都认为会被别人修改，所以每次拿数据都会上锁，这样别人想拿这个数据的时候就会阻塞。

2、【乐观锁】

总是假设最好的情况，每次在拿数据的时候，都认为不会被别人修改，所以拿数据的时候都不会上锁，但是在更新的时候会判断一下，在此期间有没有别人更新这个数据。

场景：适用于多读少写的情况，省去了锁的开销。

3、【ACID】

ACID指数据库管理系统（DBMS）中事物所具有的四个特性：原子性、一致性、隔离性、持久性。

1. 原子性(Atomicity)：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。

2. 一致性(Consistency)：在一个事务执行之前和执行之后数据库都必须处于一致性状态。如果事务成功完成，那么系统中所有变化将正确应用。如果事务出错，那么系统中的所有变化自动回滚。

3. 隔离性(Isolation)：在并发环境中，当不同的事务同时操作相同的数据时，每个事务都有各自的完成数据空间。数据更新时，数据所处的状态要么是更新前的状态，要么是更新后的状态。

4. 持久性(Durability)：只要事务成功结束，它对数据库所做的更新就必须永久保存下来。

4、表连接

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

left保留左表的值，右表无值填null right相反

A inner join B 取交集。

A left join B 取 A 全部，B 没有对应的值为 null。

A right join B 取 B 全部 A 没有对应的值为 null。

A full outer join B 取并集，彼此没有对应的值为 null。

5、聚合函数：

count()

select count( ) from Company --包括空值

select count(*) from Company --不包括空值，包括""

avg()

会忽略带有null值的那一行

如果想要把null当做0，那么可以使用IsNull函数把null转换成0，语法如下：

avg(IsNull(score，0)） as 'Average Score'

sum()

max()

min()

...

6、SQL里面各个语句的执行顺序：

（8）select 
（9）distinct
（11）top 1    //top num表示显示前num行，先distinct，后top
（6）Table1.id,COUNT(Table1.name) as nameCount 
（1）from Table1
（3）inner join Table2
（2）on Table1.id=Table2.id
（4）where Table1.id<4
（5）group by Table1.id
（7）having Table1.id<3
（10）order by Table1.id desc

（1）from：对Table1和Table2执行笛卡尔积，也就是两个表的行的各种组合，共5*5=25行，生成虚拟表VT1.

（2）on：选择VT1中的那些Table1.id=Table2.id的所有行，生成虚拟表VT2.

（3）inner join：这里是内部连接，直接就是VT2，如果是outer join，如left join、right join、full join，那么还需要按照外部连接的规则，把VT1中没有匹配的行添加到VT2，生成VT3.

（4）where：选出VT3中Table1.id<4的表格，给虚拟表VT4.

（5）group by：按照Table1.id进行分组.

（6）COUNT：执行聚合函数，选出对应Table1.id的行数，生成的结果给虚拟表VT5

（7）having：选择VT5中Table1.id<3的所有结果，给虚拟表VT6

（8）select：选择VT6中相应的列，给虚拟表VT7

（9）distinct：将VT7中重复的行去除，生成VT8

（10）order by：将VT8的结果按照Table1.id进行排序，这里没有生成一个新的表 VT9 ，而是生成游标VC9。

（11）top：从游标VC9的开始处选择指定的行数，这里是1行，生成虚拟表VT10. 经过上面的过程，最终的SQL语句将VT10返回给用户使用。

7、通过 SQL，如何按字母顺序选取 Persons 表中 LastName 介于 Adams 和 Carter 的所有记录？

正确答案：SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'

8、通过 SQL，如何向 "Persons" 表中的 "LastName" 列插入 "Wilson" ？

正确答案：INSERT INTO Persons (LastName) VALUES ('Wilson')

9、如何把 "Persons" 表中 "LastName" 列的 "Gates" 改为 "Wilson" ？

正确答案：UPDATE Persons SET LastName='Wilson' WHERE LastName='Gates'

10、SQL选择第二多的用户

（1）limit 1,1 //limit m,n 从第m位开始选择n个

（2）不是max中的max就是第二多的

11、like

like '王%'表示姓王的

not like '王%'表示除了姓王的

12、窗口函数

窗口函数语法：

<窗口函数> over (partition by <用于分组的列名> #先用partition分组再order排序 order by <用于排序的列名>)

<窗口函数>的位置，可以放以下两种函数：

1） 专用窗口函数，比如rank(), dense_rank(), row_number()等

rank()排出来是1,2,3,3,5

dense_rank()排出来是1,2,3,3,4

row_number()排出来是1,2,3,4,5  //它相当于显示的是每条数据的行号

2） 聚合函数，如sum. avg, count, max, min等

窗口函数有以下功能：

1）同时具有分组（partition by）和排序（order by）的功能

2）不减少原表的行数，所以经常用来在每组内排名

注意事项：

窗口函数原则上只能写在select子句中

13、SQL用查询出的数据创建新表

select <表达式列表> into <新表名> from 原来的表