SQL和HQL的區(qū)別

整體

1、存儲(chǔ)位置：Hive在Hadoop上；Mysql將數(shù)據(jù)存儲(chǔ)在設(shè)備或本地系統(tǒng)中；
2、數(shù)據(jù)更新：Hive不支持?jǐn)?shù)據(jù)的改寫(xiě)和添加，是在加載的時(shí)候就已經(jīng)確定好了；數(shù)據(jù)庫(kù)可以CRUD；
3、索引：Hive無(wú)索引，每次掃描所有數(shù)據(jù)，底層是MR，并行計(jì)算，適用于大數(shù)據(jù)量；MySQL有索引，適合在線查詢數(shù)據(jù)；
4、執(zhí)行：Hive底層是MapReduce；MySQL底層是執(zhí)行引擎；
5、可擴(kuò)展性：Hive：大數(shù)據(jù)量；MySQL:相對(duì)就很少了。

SQL執(zhí)行順序：
from -> where -> group by -> having -> select -> order by -> limit

語(yǔ)法

更多相關(guān)細(xì)節(jié)：
https://zhuanlan.zhihu.com/p/322399014
https://www.cnblogs.com/yxzfscg/p/4892124.html

SQL相關(guān)面試題

學(xué)生成績(jī)表Grade

學(xué)生班級(jí)表Class

題目一：用一條SQL 語(yǔ)句查詢出每門(mén)課都大于80分的學(xué)生姓名
法一：

select name 
from Grade 
group by name 
having min(grade) > 80

法二：

select distinct name 
from Grade 
where name not in (select distinct name 
                   from Grade 
                   where grade <= 80)

題目二：用一條SQL語(yǔ)句查詢出每個(gè)班語(yǔ)文成績(jī)排名第一的學(xué)生班級(jí)以及姓名
法一：

select class,name
from
(select Grade.name,class,grade,
 dense_rank() over(partition by class order by Grade.grade desc) as ranking
from Grade,Class
where Grade.name = Class.name and subject = "語(yǔ)文"
)
where ranking = 1

法二：

select class,name 
from
(select class,max(grade) as grade
from  Grade,Class
where Grade.name = Class.name and subject = "語(yǔ)文"
group by class
) a 
join
(select class,name,grade
 from  Grade,Class
 where Grade.name = Class.name and subject = "語(yǔ)文"
) b
 on a.class = b.class and a.grade = b.grade

注意：
dense_rank()是密集排列，結(jié)果是1、1、2、3、4、5
rank()是跳躍排列，結(jié)果是1、1、3、4、5、6
row_number()是不重復(fù)排列，結(jié)果是1、2、3、4、5、6
題目三：語(yǔ)文成績(jī)?nèi)Ｅ琶谌膶W(xué)生姓名和成績(jī)

select name,grade
from Grade
where subject = '語(yǔ)文'
order by grade desc
limit 2,1
-- 含義是跳過(guò)2條取出1條數(shù)據(jù)，limit跳過(guò)2條信息，讀取1條信息，即讀取第3條數(shù)據(jù)

題目四：將學(xué)生姓名表轉(zhuǎn)化為橫表

-- 此種寫(xiě)法某門(mén)課沒(méi)有成績(jī)記錄的學(xué)生，成績(jī)將變?yōu)?
select name,max(math),max(chinese),max(english)
from
(select name,
          case when subject = "語(yǔ)文" then grade else 0 end as chinese,
          case when subject = "數(shù)學(xué)" then grade else 0 end as math,
          case when subject = "英語(yǔ)" then grade else 0 end as english
 from Grade
 )
 group by name

若橫表轉(zhuǎn)豎表

select name , '語(yǔ)文' as subject, chinese as grade from tb1
union all
select name , '數(shù)學(xué)' as subject, math as grade from tb1
union all
select name , '英語(yǔ)' as subject, english as grade from tb1

union all與union的區(qū)別是union all不會(huì)去除重復(fù)記錄，union會(huì)去除重復(fù)記錄，為了保證數(shù)據(jù)庫(kù)效果，除非必要，還是使用union all。

題目五：查詢每門(mén)功課的及格人數(shù)和不及格人數(shù)

select subject,
     sum(case when grade >= 60 then 1 else 0 end) as 及格人數(shù)，
     sum(case when grade < 60 then 1 else 0 end) as 不及格人數(shù)
from Grade
group by subject

題目五： 將每個(gè)學(xué)生的成績(jī)按照由大到小寫(xiě)在一起，用逗號(hào)隔開(kāi)

select name,
group_concat(cast(grade as char) order by grade desc separator ',')
from Grade
group by name

題目六：按營(yíng)業(yè)額倒序，累計(jì)超過(guò)3000w的前面的所有公司，用sum() over

題目七：獲取每個(gè)學(xué)生的成績(jī)均值和中位數(shù)
方法一：

select t.name,avg_gade,mid_grade
from
(select name,avg(grade) as mid_grade
from
(select name,grade,
row_number() over(partition by name order by grade asc, id **asc**) as 'id1',
row_number() over(partition by name order by grade desc, id **desc**) as 'id2'
from student) as newtable
where abs(id1-id2)=1 or id1=id2;
group by name
) t
join
(select name,avg(grade) as avg_grade
from student
) tt
on t.name = tt.name

參考：https://zhuanlan.zhihu.com/p/162089174

MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
以下寫(xiě)法可以，將id = 改為 id in 則不可以。

select Candidate.Name
from Candidate
where id = 
    (
     select CandidateId
     from Vote
     group by CandidateId
     order by count(*) desc
     limit 1
    )

題目八：分割字符串，將grade中的數(shù)值分割為數(shù)學(xué)、語(yǔ)文、英語(yǔ)
id|name|grade|
|–|–|–|–|
001|張三|81,80,88||
002|李四 | 75,80,90|
003|王五 | 80,90,100|
方法一：

select name,
          substring_index(grade,',',1) as 數(shù)學(xué)',
          substring_index(substring_index(grade,',',2),',','-1') as '語(yǔ)文',
          substring_index(grade,',',-1) as 英語(yǔ)
from Grade

字符串函數(shù)：
SUBSTRING_INDEX（str, delim, count）
delim:表示分割字符串，count:表示第幾個(gè)分割字符串，當(dāng) count 為正數(shù)，取第 n 個(gè)分隔符之前的所有字符； 當(dāng) count 為負(fù)數(shù)，取倒數(shù)第 n 個(gè)分隔符之后的所有字符。
SUBSTRING(string,position):獲取Position之后的所有字符
SUBSTRING(string,position,length)：獲取position之后的長(zhǎng)度為L(zhǎng)ength的字符串，其中position的長(zhǎng)度從1開(kāi)始。

疑問(wèn)：
order by 1 和 order by 字段在效率上的區(qū)別，實(shí)際運(yùn)行時(shí)，order by 1的效率比order by 字段的效率要高。

詳細(xì)字符串操作的四種方式：
https://blog.csdn.net/qq_37260640/article/details/79731295?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-5.no_search_link&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-5.no_search_link

數(shù)據(jù)庫(kù)常見(jiàn)面試題：https://www.cnblogs.com/diffrent/p/8854995.html

SQL使用問(wèn)題
問(wèn)題1：A left join B join C 和 A left join (B join C)的區(qū)別？
多表相連不符合交換律，即從左到右執(zhí)行和從右到左執(zhí)行的結(jié)果是不一樣的，
A left join B join C = (A left join B) join C != A left join (B join C)

2、sort by 和order by的區(qū)別
order by實(shí)現(xiàn)的是全局排序，在hive引擎中將會(huì)只有1個(gè)reduce。而使用sort by會(huì)起多個(gè)reduce，只會(huì)在每個(gè)reduce中排序，如果不指定分組的話，跑出來(lái)的數(shù)據(jù)看起來(lái)是雜亂無(wú)章的，如果指定reduce個(gè)數(shù)是1，那么結(jié)果和order by是一致的。
order by一般配合group by使用，而group by需要配合聚合函數(shù)使用。
sort by分組時(shí)需要使用distribute by，和group by類似，但是它不需要配合聚合函數(shù)使用，也就不影響原數(shù)據(jù)的函數(shù)，這點(diǎn)和開(kāi)窗函數(shù)有點(diǎn)類似。
參考：https://www.pianshen.com/article/17082054431/

3、如果group by的key中有null，會(huì)怎么樣
group by 不對(duì) null 進(jìn)行分組統(tǒng)計(jì)。在使用 group by某列名進(jìn)行分組統(tǒng)計(jì)時(shí)，該列名的數(shù)據(jù)有些為 null, 因而會(huì)出現(xiàn) null 的數(shù)據(jù)行全部分成一組最終導(dǎo)致數(shù)據(jù)錯(cuò)誤。
解決方法：為null值隨機(jī)生成一個(gè)獨(dú)一無(wú)二的數(shù)，這樣為null的記錄將不會(huì)被分組，維持原樣?？梢允褂肬UID() 函數(shù)生成這個(gè)獨(dú)一無(wú)二的數(shù)。
group by IFNULL(‘列名’, UUID())

參考：https://www.cnblogs.com/CF1314/p/14132397.html
4、日期相關(guān)函數(shù)
date_format()
date_diff()
date_add()
select date_add(‘2021-05-01’, interval +1 day) as result

5、了解數(shù)據(jù)傾斜&解決辦法
數(shù)據(jù)傾斜有三種形式得傾斜：
一是分區(qū)不均，某幾個(gè)分區(qū)對(duì)應(yīng)的key太多。多數(shù)情況都是這種傾斜。
二是單個(gè)key對(duì)應(yīng)的數(shù)據(jù)量太多
三是單條記錄數(shù)據(jù)太大（比如數(shù)組中的值太多）
（1）加并行度
這是一種很簡(jiǎn)單的處理方案，將分區(qū)增多，數(shù)據(jù)打得更散，充分發(fā)揮分布式的優(yōu)勢(shì)。但是分區(qū)增量task也會(huì)增多，帶來(lái)的額外的管理成本就更多了，分的太多反而跑得更慢，存儲(chǔ)結(jié)果的成本也增加了，不是一個(gè)很好的解決方案。
可以在以下幾個(gè)地方增加分區(qū)。
1.在傾斜的stage之前使用reparation重分區(qū)。
2.設(shè)置shuffle的并行度，大部分情況都使用這個(gè)。
（2）處理特殊case
這種就比較常見(jiàn)了，經(jīng)常會(huì)發(fā)現(xiàn)很多stage跑到剩下一個(gè)task死活跑不過(guò)或者耗時(shí)非常久。傾斜的key我們可以通過(guò)group by key進(jìn)行count來(lái)尋找，一般都是空值、空字符串、還有特別熱點(diǎn)的key。如何處理這就看你的業(yè)務(wù)需求咯。
（3）利用小trick打散key
針對(duì)第二種傾斜的形式，我們可以在key上加隨機(jī)前綴或后綴這樣加鹽的方式來(lái)將一個(gè)key變成多個(gè)key先進(jìn)行一次shuffle，最后再還原回來(lái)。
例如我們需要進(jìn)行分組統(tǒng)計(jì)，但是數(shù)據(jù)傾斜了，我們可以對(duì)key加隨機(jī)前綴，把一個(gè)key變成多個(gè)進(jìn)行count，最后sum。
這種方式比較麻煩特別是在join的情況下，要考慮的東西比較多。
加鹽的方式也會(huì)數(shù)據(jù)量不是那么多的key也打的更散了，計(jì)算起來(lái)有點(diǎn)浪費(fèi)資源。
（4）自定義分區(qū)方案
這種就更高端了些，需要自己去實(shí)現(xiàn)一個(gè)partitioner，不多說(shuō)，還不如構(gòu)造key來(lái)實(shí)現(xiàn)自定義分區(qū)。
參考：https://www.jianshu.com/p/3635cd26b26a

6、HIVE的抽樣方法有哪些
數(shù)據(jù)塊抽樣（tablesample()函數(shù)）
按照hive表的比例、大小、行數(shù)對(duì)hive表進(jìn)行隨機(jī)抽樣，在測(cè)試過(guò)程中發(fā)現(xiàn)，select語(yǔ)句不能帶where條件且不支持子查詢，可通過(guò)新建中間表或使用隨機(jī)抽樣解決，具體語(yǔ)句如下：
create table xxx_new as select * from xxx tablesample(10 percent)

分桶抽樣
hive中分桶其實(shí)就是根據(jù)某一個(gè)字段Hash取模，放入指定數(shù)據(jù)的桶中，比如將表table_1按照ID分成100個(gè)桶，其算法是hash(id) % 100，這樣，hash(id) % 100 = 0的數(shù)據(jù)被放到第一個(gè)桶中，hash(id) % 100 = 1的記錄被放到第二個(gè)桶中。具體語(yǔ)法是TABLESAMPLE (BUCKET x OUT OF y [ON colname])
例如：將表隨機(jī)分成10組，抽取其中的第一個(gè)桶的數(shù)據(jù)
select * from table_01 tablesample(bucket 1 out of 10 on rand())

隨機(jī)抽樣（rand()函數(shù)）
1）使用rand()函數(shù)進(jìn)行隨機(jī)抽樣，limit關(guān)鍵字限制抽樣返回的數(shù)據(jù)，其中rand函數(shù)前的distribute和sort關(guān)鍵字可以保證數(shù)據(jù)在mapper和reducer階段是隨機(jī)分布的，案例如下：
select * from table_name where col=xxx distribute by rand() sort by rand() limit num;
2）使用order 關(guān)鍵詞
案例如下：
select * from table_name where col=xxx order by rand() limit num;
經(jīng)測(cè)試對(duì)比，千萬(wàn)級(jí)數(shù)據(jù)中進(jìn)行隨機(jī)抽樣 order by方式耗時(shí)更長(zhǎng)，大約多30秒左右。
參考：https://www.cnblogs.com/w-j-q/p/14139007.html

數(shù)據(jù)庫(kù)相關(guān)知識(shí)點(diǎn)

1、等值連接與自然連接的區(qū)別和聯(lián)系
（1）自然連接一定是等值連接，但等值連接不一定是自然連接。
（2）等值連接要求相等的分量，不一定是公共屬性；而自然連接要求相等的分量必須是公共屬性。
（3）等值連接不把重復(fù)的屬性除去；而自然連接要把重復(fù)的屬性除去。
2、數(shù)據(jù)庫(kù)的三范式分別是什么，有什么區(qū)別？



參考：http://www.blogjava.net/hijackwust/archive/2007/10/21/154793.html

3、什么是視圖？和表的區(qū)別是什么？
（1）視圖是已經(jīng)編譯好的sql語(yǔ)句，而表不是；
（2）視圖沒(méi)有實(shí)際的物理記錄，而表有；
（3）表是內(nèi)容，視圖是窗口；
（4）表占用物理空間而視圖不占用物理空間，表可以及時(shí)對(duì)它進(jìn)行修改，但視圖只是邏輯概念的存在，只能用創(chuàng)建的語(yǔ)句來(lái)修改；
（5）表是三級(jí)模式結(jié)構(gòu)中的概念模式，視圖是外模式；

4、什么是事務(wù)？什么是鎖？
事務(wù)就是被綁定在一起作為一個(gè)邏輯工作單元的SQL 語(yǔ)句分組，如果任何一個(gè)語(yǔ)句操作失敗那么整個(gè)操作就被失敗，以后操作就會(huì)回滾到操作前狀態(tài)，或者是上有個(gè)節(jié)點(diǎn)。為了確保要么執(zhí)行，要么不執(zhí)行，就可以使用事務(wù)。事務(wù)具有四個(gè)特性，分別是原子性，一致性，隔離性和持久性。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-453056.html

特性	解釋
原子性	一個(gè)原子事務(wù)要么完整執(zhí)行，要么干脆不執(zhí)行。
一致性	底層數(shù)據(jù)存儲(chǔ)的完整性。在一次轉(zhuǎn)賬過(guò)程中，從某一賬戶中扣除的金額必須與另一賬戶中存入的金額相等。
隔離性	事務(wù)必須在不干擾其他進(jìn)程或事務(wù)的前提下獨(dú)立執(zhí)行
持久性	在某個(gè)事務(wù)的執(zhí)行過(guò)程中，對(duì)數(shù)據(jù)所作的所有改動(dòng)都必須在事務(wù)成功結(jié)束前保存至某種物理存儲(chǔ)設(shè)備。

到了這里，關(guān)于mySQL和Hive的區(qū)別的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！