Database Some Forgotten Points

DML、DDL、DCL

• DML(data manipulation language): 操作数据CRUD
• DDL(data definition language): 修改表结构
• DCL(data control language): 设置数据库用户权限

开窗函数

分窗函数形如:

1
2
3

SELECT
function_name OVER ( window_definition )
FROM (...)

看一个具体的SQL

1
2
3
4
5

SELECT
  f.id, f.release_year, 
  f.category_id, f.rating,
  AVG(rating) OVER (PARTITION BY release_year) AS year_avg
FROM films f

partition by告诉数据库 把数据集合分成更小的部分，

把release_year相同的放在一起 成为一个窗口，

函数avg会对每个窗口数据进行计算，然后把结果放到每一行中。

分窗操作过程如图:

reference: Mysql 8 Window function

SQL中on条件与where条件的区别

注意：要理解join的含义：

通过on条件让两个表 连接 , 生成一张中间表给用户。

连接操作可以想象成，两个表的行 通过一条线 连接在一起。而选哪些行连，则是通过on的条件。

1. left/rignt/full join 不管on上的条件是否为真 都会返回left或right表中的记录，full则具有left和right的特性的并集。

2. inner join，在表连接的时候，没有主次表之分，所以满足on条件的 才会进行连接。就相当于where。

下面通过示例更深刻理解：

表tb1

表tb2

例1：正常where过滤

select * from tb1 left join tb2 on tb1.size = tb2.size where tb2.name='AAA'

中间表

可以看出，通过size连接两表，相像成tb1的行 用线 连接tb2的行 ，tb1.size=20时 连接了两条线到tb2.size=20。如下:

通过left join保留主表的所有行，就有了上面中间表。通过where去过滤中间表 得到结果表。

结果表

例2：left join

select * from tb1 left join tb2 on tb1.size = tb2.size and tb2.name='AAA' order by tb1.id

此时，连接的条件有2个。即在连接的时候 还要加上tb2.name = ‘AAA’的条件，相当于上面图中 只有一条绿线。如 而left join要保留主表的所有行，所以 没连接上的就为NULL

例3：inner join

select * from tb1 inner join tb2 on tb1.size = tb2.size and tb2.name='AAA'

连接条件 和 上面的left join一样，但是 inner join没有主次表之分，它是找出两表中关联上的行。所以，就相当于最后where。

总结：在join的时候，时刻想着 连接的条件，如有：tb2.name='xxx’这样的，相当于 只选tb2.name='xxx’的数据 进行连接。如：tb1.size=tb2.size，相当于 取两表相同size值连接。

如果想要过滤，就要想好 是在连接的时候 过滤，还是连接完形成中间表后 再过滤。

若在连接的时候 过滤，就要考虑到 left/right/full时候 保留主表所有数据后，没连接上 NULL的情况。