OLTP、OLAP的区别以及应用场景（数据库总结）

目录

1.OLTP、OLAP的区别以及应用场景

2.牛客数据库笔试题

3.SQL

1.OLTP、OLAP的区别以及应用场景

OLTP：

联机事务处理OLTP（on-line transaction processing） 主要是执行基本日常的事务处理，比如数据库记录的增删查改。比如在银行的一笔交易记录，就是一个典型的事务。

1. 实时性要求高。我记得之前上大学的时候，银行异地汇款，要隔天才能到账，而现在是分分钟到账的节奏，说明现在银行的实时处理能力大大增强。
2. 数据量不是很大，生产库上的数据量一般不会太大，而且会及时做相应的数据处理与转移。
3. 交易一般是确定的，比如银行存取款的金额肯定是确定的，所以OLTP是对确定性的数据进行存取
4. 高并发，并且要求满足ACID原则。比如两人同时操作一个银行卡账户，比如大型的购物网站秒杀活动时上万的QPS请求。

OLAP：

联机分析处理OLAP（On-Line Analytical Processing） 是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。典型的应用就是复杂的动态的报表系统。

1. 实时性要求不是很高，比如最常见的应用就是天级更新数据，然后出对应的数据报表。
2. 数据量大，因为OLAP支持的是动态查询，所以用户也许要通过将很多数据的统计后才能得到想要知道的信息，例如时间序列分析等等，所以处理的数据量很大;
3. OLAP系统的重点是通过数据提供决策支持，所以查询一般都是动态，自定义的。所以在OLAP中，维度的概念特别重要。一般会将用户所有关心的维度数据，存入对应数据平台。

（OLTP即联机事务处理，就是我们经常说的关系数据库，增删查改就是我们经常应用的东西，这是数据库的基础；TPCC(Transaction Processing Performance Council)属于此类。

OLAP即联机分析处理，是数据仓库的核心部心，所谓数据仓库是对于大量已经由OLTP形成的数据的一种分析型的数据库，用于处理商业智能、决策支持等重要的决策信息；数据仓库是在数据库应用到一定程序之后而对历史数据的加工与分析，读取较多，更新较少，TPCH属于此类。}

2.牛客数据库笔试题 

复习之我在牛客上遇到不会的题

这里有数学公式的应用

• 字符串的截取：substring(字符串，起始位置，截取字符数）
• 字符串的拼接：concat(字符串1，字符串2，字符串3,...)
• 字母大写：upper(字符串）

 

date_format

用法：

返回日期的一部分---date_format(日期,'%Y%m%d)

• 

1.聚合查询

-- 聚合查询
select 
order_num, count(order_num)  order_lines
from OrderItems
-- 分组 
group by order_num
-- 排序（默认升序）
order by order_lines asc;

select  
vend_id , min(prod_price) cheapest_item
from Products
group by vend_id
order by cheapest_item

min()没有和到一起编译会报错（应该是牛客编译器的问题）

min（）求最小值

max() 求最大值

where---过滤过滤指定的行

having--过滤分组，与group by连用

select order_num
from OrderItems
group by order_num
having sum(quantity)>=100
order by order_num

1. where条件语句后面不能加聚合函数（分组函数）
2. having 不能单独使用，必须和group by 联合使用

select order_num,sum(item_price*quantity) total_price
from OrderItems
group by order_num
having total_price>=1000
order by order_num

上面不可以用where，因为用了之后没办法用group分组，这样的话，就没有聚合的效果

SELECT order_num, COUNT(order_num) AS items 
FROM OrderItems 
GROUP BY order_num 
HAVING items >= 3 
ORDER BY items, order_num;

这里我要提到count、和sum的区别，因为我容易混

count（）是统计一共有几行

而sum是数据的总和，也就是把数据加在一起了

select cust_id,order_date
from Orders
where order_num in
-- 括号都要紧贴着关键词要不然会报错
(select order_num
from OrderItems
 where prod_id = 'BR01'
)
order by order_date asc

下面涉及三个表查询的牛客题

 

select cust_email
from Customers
where Cust_id in
(select Cust_id
 from Orders
 where order_num in
 (select order_num 
  from OrderItems
  where prod_id = 'BR01'
 )
)

select cust_id,order_date
from OrderItems oi
join Orders o 
on oi.order_num =o.order_num
-- 把两张表连起来了，然后再筛选
where prod_id='BR01'
order by order_date

左外联

select prod_name,order_num
from Products p 
-- 我理解的是 加不加outer 都是外联
left outer join OrderItems oi on p.prod_id =oi.prod_id
 order by prod_name
 

 

select cust_name,oi.order_num,quantity*item_price OrderTotal
-- 这里oi.order_num 或者o.order,num都可以，但是一定要写
from Customers c
join  Orders o
on c.cust_id = o.cust_id
join OrderItems oi
 on o.order_num = oi.order_num
order by cust_name,oi.order_num

这道题我和41题有点混，但是区别就是这道题不需要聚合，41需要聚合之后才可以联表

 

select cust_name,total_price
from(select order_num,sum(item_price * quantity) total_price
from OrderItems 
 group by order_num
)oi
inner join Orders o on oi.order_num = o.order_num
inner join Customers c on o.cust_id = c.cust_id
where total_price >= 1000
order by total_price;

if null(表达式，0)

表达式==null?0：表达式

如果正确返回0，不正确返回表达式

union 合集 （会合并重复行）

union all (不合并重复行)

select prod_id,quantity
from OrderItems
where quantity =100
union 
select prod_id,quantity
from OrderItems
where prod_id like'BNBG%'
ORDER BY prod_id

做题总结：

order by 排序

limit offset 分页

outer、inner，在默认情况下，只写join，是内联

因为左外联右外联只有外联才有，所以可以只写为left join、right join。

还有就是聚合查询必须有聚合函数和聚合字段，group by 为分组聚合

having(聚合后过滤)必须与group by 连用

子查询是这个表要先筛选，筛选之后再把子表过滤

联表是把两个表连接起来查询（inner join...on...  left outer join...on... right outer join...on...）

3.SQL（Structure Query Language）结构化查询语言

SQL分为： 

DDL：数据定义语言（新建一个数据库、删一个库、新建/修改/删除一个表）

DCL：数据控制语言（添加一个DBMS用户、修改一个DBMS的密码重启，每个DBMS都不一样）

DML分为增删查改操作，而具体的查的操作可以分为基本查询、聚合、联表、子查询、合并查询，前三个主要是OLTP业务，后面四个主要是OLAP业务