大家好呀，這節(jié)課我們學(xué)習(xí) Hive 核心技能中最難的部分——窗口函數(shù)。窗口函數(shù)我們之前在學(xué) MySQL 的時(shí)候有學(xué)過(guò)一些，但是只學(xué)了三個(gè)排序的窗口函數(shù)。這節(jié)課我們會(huì)學(xué)習(xí)更多的窗口函數(shù)，包括累計(jì)計(jì)算、分區(qū)排序、切片排序以及偏移分析。
曲陽(yáng)ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景，ssl證書未來(lái)市場(chǎng)廣闊！成為成都創(chuàng)新互聯(lián)公司的ssl證書銷售渠道，可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：13518219792（備注：SSL證書合作）期待與您的合作！
在正式學(xué)習(xí)之前，我們需要先明確一下窗口函數(shù)和GROUP BY分組的區(qū)別。二者在功能上有相似之處，但是它們存在本質(zhì)區(qū)別。
1. 分組會(huì)改變表的結(jié)構(gòu)，而窗口函數(shù)不會(huì)改變表的結(jié)構(gòu)。比如原表有10行數(shù)據(jù)，分成兩組后只有兩行，而窗口函數(shù)仍然返回十行數(shù)據(jù)。
2. 分組只能查詢分組后的字段，包括分組字段（組名）和聚合函數(shù)字段。而窗口函數(shù)對(duì)查詢字段沒(méi)有限制，也就是可以查詢?cè)淼娜我庾侄?，再加上窗口函?shù)新增的一列值。
好啦，現(xiàn)在讓我們一起進(jìn)入窗口函數(shù)的世界吧~
本節(jié)課主要內(nèi)容：
1、累計(jì)計(jì)算窗口函數(shù)
（1）sum(…) over(……)
（2）avg(…) over(……)
（3）語(yǔ)法總結(jié)
2、分區(qū)排序窗口函數(shù)
（1）row_number()
（2）rank()
（3）dense_rank()
3、切片排序窗口函數(shù)
（1）ntile(n) over(……)
4、偏移分析窗口函數(shù)
5、重點(diǎn)練習(xí)
大家在做報(bào)表的時(shí)候，經(jīng)常會(huì)遇到計(jì)算截止某月的累計(jì)數(shù)值，通常在EXCEL里可以通過(guò)函數(shù)來(lái)實(shí)現(xiàn)。
那么在HiveSQL里，該如何實(shí)現(xiàn)這種累計(jì)數(shù)值的計(jì)算呢？那就是利用窗口函數(shù)！
關(guān)于窗口函數(shù)的幾點(diǎn)說(shuō)明：
需求分析 ：既然要進(jìn)行按月累計(jì)，我們就先要把2018年的每筆交易時(shí)間轉(zhuǎn)換成月并按月分組聚合計(jì)算，得出一個(gè)2018年每月支付金額總合表，再基于這張表用窗口函數(shù)進(jìn)行累計(jì)計(jì)算。
2018年每月支付金額總和表：
再用窗口函數(shù)進(jìn)行月度累計(jì)：
年度進(jìn)行匯總。
這個(gè)需求比需求1多了一個(gè)需求，那就是年度匯總。那我們只需要在上個(gè)需求的子查詢中加一個(gè) year 字段即可。
說(shuō)明：
1、over 中的 partition by 起到了窗口內(nèi)將數(shù)據(jù)分組的作用。事實(shí)上，加上partition by之后，可以理解為分成了多個(gè)窗口，并在每個(gè)窗口內(nèi)進(jìn)行累加計(jì)算或者分區(qū)。
如果不加 partition by a.year 的話，運(yùn)行結(jié)果就是這樣單純按月份進(jìn)行分組的：
2、order by 按照什么順序進(jìn)行累加，升序ASC、降序DESC，默認(rèn)是升序。
大家看股票的時(shí)候，經(jīng)常會(huì)看到這種K線圖，里面經(jīng)常用到的就是7日、30日移動(dòng)平均的趨勢(shì)圖，那如何使用窗口函數(shù)來(lái)計(jì)算移動(dòng)平均值呢？
需求分析 ：這個(gè)需求要求每個(gè)月近三個(gè)月的移動(dòng)平均支付金額，這里我們要用到一個(gè)新知識(shí)點(diǎn)，在窗口函數(shù) avg over 的 order by a.month 之后加一句 rows between 2 preceding and current row 來(lái)設(shè)定計(jì)算移動(dòng)平均的范圍，這個(gè)語(yǔ)句的含義就是包含本行及前兩行。其他部分的寫法跟前面的需求類似，先取出2018年每個(gè)月的支付金額總和，再用窗口函數(shù)求移動(dòng)平均。
注意：
sum(…A…) over(partition by …B… order by …C… rows between …D1… and …D2…)
avg(…A…) over(partition by …B… order by …C… rows between…D1… and …D2…)
A：需要被加工的字段名稱
B：分組的字段名稱
C：排序的字段名稱
D：計(jì)算的行數(shù)范圍
rows between unbounded preceding and current row
——包括本行和之前所有的行
rows between current row and unbounded following
——包括本行和之后所有的行
rows between 3 preceding and current row
——包括本行以內(nèi)和前三行
rows between 3 preceding and 1 following
——從前三行到下一行(5行)
max(……) over(partition by …… order by …… rows between ……and ……)
min(……) over(partition by …… order by …… rows between ……and ……)
row_number() 、rank()、dense_rank()
用法：這三個(gè)函數(shù)的作用都是返回相應(yīng)規(guī)則的排序序號(hào)
row_number() over(partition by …A… order by …B… )
rank() over(partition by …A… order by …B… )
dense_rank() over(partition by …A… order by …B… )
A：分組的字段名稱
B：排序的字段名稱
注意： 這3個(gè)函數(shù)的括號(hào)內(nèi)是不加任何字段名稱的！
row_number ：它會(huì)為查詢出來(lái)的每一行記錄生成一個(gè)序號(hào)，依次排序且不會(huì)重復(fù)。
rankdense_rank ：在各個(gè)分組內(nèi)， rank() 是跳躍排序，有兩個(gè)第一名時(shí)接下來(lái)就是第三名， dense_rank() 是連續(xù)排序，有兩個(gè)第一名時(shí)仍然跟著第二名。
實(shí)例練習(xí)：
再眼熟一下 user_trade 的表結(jié)構(gòu)：
需求分析 ：先限定時(shí)間范圍，然后根據(jù) user_name 進(jìn)行分組，接著選出 分組去重后的 user_name，并計(jì)算每個(gè)用戶 goods_category 的數(shù)量（記得 distinct 去重），再然后就是用窗口函數(shù)對(duì) goods_category 的數(shù)量進(jìn)行排序，當(dāng)然選擇哪一種排序方法要看具體要求，這里我們可以三種方法都試一下看看結(jié)果：
注意 ：窗口函數(shù)中的 order by 字段不能用 select 中字段的重命名，因?yàn)槎呤峭瑫r(shí)執(zhí)行的。
需求分析 ： 先用窗口函數(shù)將2019年每個(gè)用戶的支付總金額算出來(lái)并進(jìn)行排序，再以此作為子查詢，從中取出排名在第10、20、30名的用戶名、支付總金額以及排名次序。企業(yè)一般會(huì)使用 dense_rank 進(jìn)行排序，所以我們這里直接用 dense_rank。
2019年每個(gè)用戶的支付總金額排名：
2019年支付金額排名在第10、20、30名的用戶：
ntile(n) over(partition by …A… order by …B… )
n：切分的片數(shù)
A：分組的字段名稱
B：排序的字段名稱
需求分析 ：這個(gè)需求很簡(jiǎn)單，把需求5第一步的排序窗口函數(shù)變成切片即可。注意時(shí)間篩選條件變成2019年1月。
需求分析 ： 排名前10%，也就是一共分成10組，取第1組。那么我們先切片分組：
然后再取第一組：
說(shuō)明：Lag和Lead分析函數(shù)可以在同一次查詢中取出同一字段的前N行的數(shù)據(jù)(Lag)和后N行的數(shù)據(jù)(Lead)作為獨(dú)立的列。
在實(shí)際應(yīng)用當(dāng)中，若要用到取今天和昨天的某字段差值時(shí)，Lag和Lead函數(shù)的應(yīng)用就顯得尤為重要。當(dāng)然，這種操作可以用表的自連接實(shí)現(xiàn)，但是LAG和LEAD與left join、right join等自連接相比，效率更高，SQL語(yǔ)句更簡(jiǎn)潔。
lag(exp_str,offset,defval) over(partion by ……order by ……)
lead(exp_str,offset,defval) over(partion by ……order by ……)
lag() 函數(shù)示例：
lead() 函數(shù)示例：
需求分析： 先要從 user_trade 表中取出每個(gè)用戶的支付時(shí)間，把每個(gè)用戶放到一個(gè)窗口中，按照支付時(shí)間進(jìn)行排序，取出偏移列： lead(dt,1,dt) over(partition by user_name order by dt)。接著基于該子查詢，篩選出時(shí)間間隔大于100天的用戶，并計(jì)算數(shù)量。
注意 ： 如果上面偏移分析函數(shù)寫成 lead(dt,1,dt) 就不用加后面的 dt is not null 了，因?yàn)橛心J(rèn)值的話，間隔就是0，肯定是不滿足條件的。
需求分析 ：
第一步 ：這個(gè)需求要用到 user_trade 和 user_info 兩張表，前者取支付時(shí)間和金額，后者取城市和性別。先對(duì)這兩張表基于 user_name 進(jìn)行左連接，并取出相應(yīng)字段，用窗口函數(shù)進(jìn)行分組排序：
這一步的運(yùn)行結(jié)果是這樣的：
第二步 ：基于上述結(jié)果取出TOP3：
需求分析：
第一步 ：這個(gè)需求同樣要用到兩張表 user_refund 和 user_info。我們先把每個(gè)退款用戶的退款金額和手機(jī)品牌取出來(lái)，并用窗口函數(shù)進(jìn)行切片排序，25%就是分成4片：
注意 ：這里之所以要加 WHERE dt is not null 是因?yàn)?user_refund 是一個(gè)分區(qū)表，分區(qū)表要對(duì)分區(qū)字段進(jìn)行限制，否則 hive 會(huì)報(bào)錯(cuò)。
第二步 ：選擇前25%，也就是第一片：
最后補(bǔ)充一個(gè)從 hive 導(dǎo)出結(jié)果數(shù)據(jù)的命令：
以上就是這節(jié)課的全部?jī)?nèi)容了。做完整個(gè)練習(xí)，真的半條命都沒(méi)了。窗口函數(shù)果然很難，不過(guò)掌握方法、多多練習(xí)，學(xué)會(huì)拆解需求，一步一步來(lái)做，就能明顯降低難度。希望以后有機(jī)會(huì)能用到這么復(fù)雜的技能，哈哈~！
由于需要提升項(xiàng)目的搜索質(zhì)量，最近研究了一下Elasticsearch，一款非常優(yōu)秀的分布式搜索程序。最開始的一些筆記放到github，這里只是歸納總結(jié)一下。
首先，為什么要使用Elasticsearch？最開始的時(shí)候，我們的項(xiàng)目?jī)H僅使用MySQL進(jìn)行簡(jiǎn)單的搜索，然后一個(gè)不能索引的like語(yǔ)句，直接拉低MySQL的性能。后來(lái)，我們?cè)紤]過(guò)sphinx，并且sphinx也在之前的項(xiàng)目中成功實(shí)施過(guò)，但想想現(xiàn)在的數(shù)據(jù)量級(jí)，多臺(tái)MySQL，以及搜索服務(wù)本身HA，還有后續(xù)擴(kuò)容的問(wèn)題，我們覺(jué)得sphinx并不是一個(gè)最優(yōu)的選擇。于是自然將目光放到了Elasticsearch上面。
根據(jù)官網(wǎng)自己的介紹，Elasticsearch是一個(gè)分布式搜索服務(wù)，提供Restful API，底層基于Lucene，采用多shard的方式保證數(shù)據(jù)安全，并且提供自動(dòng)resharding的功能，加之github等大型的站點(diǎn)也采用 Elasticsearch作為其搜索服務(wù)，我們決定在項(xiàng)目中使用Elasticsearch。
對(duì)于Elasticsearch，如果要在項(xiàng)目中使用，需要解決如下問(wèn)題：
索引，對(duì)于需要搜索的數(shù)據(jù)，如何建立合適的索引，還需要根據(jù)特定的語(yǔ)言使用不同的analyzer等。
搜索，Elasticsearch提供了非常強(qiáng)大的搜索功能，如何寫出高效的搜索語(yǔ)句？
數(shù)據(jù)源，我們所有的數(shù)據(jù)是存放到MySQL的，MySQL是唯一數(shù)據(jù)源，如何將MySQL的數(shù)據(jù)導(dǎo)入到Elasticsearch？
對(duì)于1和2，因?yàn)槲覀兊臄?shù)據(jù)都是從MySQL生成，index的field是固定的，主要做的工作就是根據(jù)業(yè)務(wù)場(chǎng)景設(shè)計(jì)好對(duì)應(yīng)的mapping以及search語(yǔ)句就可以了，當(dāng)然實(shí)際不可能這么簡(jiǎn)單，需要我們不斷的調(diào)優(yōu)。
而對(duì)于3，則是需要一個(gè)工具將MySQL的數(shù)據(jù)導(dǎo)入Elasticsearch，因?yàn)槲覀儗?duì)搜索實(shí)時(shí)性要求很高，所以需要將MySQL的增量數(shù)據(jù)實(shí)時(shí)導(dǎo)入，筆者唯一能想到的就是通過(guò)row based binlog來(lái)完成。而近段時(shí)間的工作，也就是實(shí)現(xiàn)一個(gè)MySQL增量同步到Elasticsearch的服務(wù)。
Lucene
Elasticsearch底層是基于Lucene的，Lucene是一款優(yōu)秀的搜索lib，當(dāng)然，筆者以前仍然沒(méi)有接觸使用過(guò)。:-)
Lucene關(guān)鍵概念：
Document：用來(lái)索引和搜索的主要數(shù)據(jù)源，包含一個(gè)或者多個(gè)Field，而這些Field則包含我們跟Lucene交互的數(shù)據(jù)。
Field：Document的一個(gè)組成部分，有兩個(gè)部分組成，name和value。
Term：不可分割的單詞，搜索最小單元。
Token：一個(gè)Term呈現(xiàn)方式，包含這個(gè)Term的內(nèi)容，在文檔中的起始位置，以及類型。
Lucene使用Inverted index來(lái)存儲(chǔ)term在document中位置的映射關(guān)系。
譬如如下文檔：
Elasticsearch Server 1.0 （document 1）
Mastring Elasticsearch （document 2）
Apache Solr 4 Cookbook （document 3）
使用inverted index存儲(chǔ)，一個(gè)簡(jiǎn)單地映射關(guān)系：
Term
Count
Docuemnt
1.0 1
4 1
Apache 1
Cookbook 1
Elasticsearch 2 .
Mastering 1
Server 1
Solr 1
對(duì)于上面例子，我們首先通過(guò)分詞算法將一個(gè)文檔切分成一個(gè)一個(gè)的token，再得到該token與document的映射關(guān)系，并記錄token出現(xiàn)的總次數(shù)。這樣就得到了一個(gè)簡(jiǎn)單的inverted index。
Elasticsearch關(guān)鍵概念
要使用Elasticsearch，筆者認(rèn)為，只需要理解幾個(gè)基本概念就可以了。
在數(shù)據(jù)層面，主要有：
Index：Elasticsearch用來(lái)存儲(chǔ)數(shù)據(jù)的邏輯區(qū)域，它類似于關(guān)系型數(shù)據(jù)庫(kù)中的db概念。一個(gè)index可以在一個(gè)或者多個(gè)shard上面，同時(shí)一個(gè)shard也可能會(huì)有多個(gè)replicas。
Document：Elasticsearch里面存儲(chǔ)的實(shí)體數(shù)據(jù)，類似于關(guān)系數(shù)據(jù)中一個(gè)table里面的一行數(shù)據(jù)。
document由多個(gè)field組成，不同的document里面同名的field一定具有相同的類型。document里面field可以重復(fù)出現(xiàn)，也就是一個(gè)field會(huì)有多個(gè)值，即multivalued。
Document type：為了查詢需要，一個(gè)index可能會(huì)有多種document，也就是document type，但需要注意，不同document里面同名的field一定要是相同類型的。
Mapping：存儲(chǔ)field的相關(guān)映射信息，不同document type會(huì)有不同的mapping。
對(duì)于熟悉MySQL的童鞋，我們只需要大概認(rèn)為Index就是一個(gè)db，document就是一行數(shù)據(jù)，field就是table的column，mapping就是table的定義，而document type就是一個(gè)table就可以了。
Document type這個(gè)概念其實(shí)最開始也把筆者給弄糊涂了，其實(shí)它就是為了更好的查詢，舉個(gè)簡(jiǎn)單的例子，一個(gè)index，可能一部分?jǐn)?shù)據(jù)我們想使用一種查詢方式，而另一部分?jǐn)?shù)據(jù)我們想使用另一種查詢方式，于是就有了兩種type了。不過(guò)這種情況應(yīng)該在我們的項(xiàng)目中不會(huì)出現(xiàn)，所以通常一個(gè)index下面僅會(huì)有一個(gè) type。
在服務(wù)層面，主要有：
Node: 一個(gè)server實(shí)例。
Cluster：多個(gè)node組成cluster。
Shard：數(shù)據(jù)分片，一個(gè)index可能會(huì)存在于多個(gè)shards，不同shards可能在不同nodes。
Replica：shard的備份，有一個(gè)primary shard，其余的叫做replica shards。
Elasticsearch之所以能動(dòng)態(tài)resharding，主要在于它最開始就預(yù)先分配了多個(gè)shards（貌似是1024），然后以shard為單位進(jìn)行數(shù)據(jù)遷移。這個(gè)做法其實(shí)在分布式領(lǐng)域非常的普遍，codis就是使用了1024個(gè)slot來(lái)進(jìn)行數(shù)據(jù)遷移。
因?yàn)槿我庖粋€(gè)index都可配置多個(gè)replica，通過(guò)冗余備份的方式保證了數(shù)據(jù)的安全性，同時(shí)replica也能分擔(dān)讀壓力，類似于MySQL中的slave。
Restful API
Elasticsearch提供了Restful API，使用json格式，這使得它非常利于與外部交互，雖然Elasticsearch的客戶端很多，但筆者仍然很容易的就寫出了一個(gè)簡(jiǎn)易客戶端用于項(xiàng)目中，再次印證了Elasticsearch的使用真心很容易。
Restful的接口很簡(jiǎn)單，一個(gè)url表示一個(gè)特定的資源，譬如/blog/article/1，就表示一個(gè)index為blog，type為aritcle，id為1的document。
而我們使用http標(biāo)準(zhǔn)method來(lái)操作這些資源，POST新增，PUT更新，GET獲取，DELETE刪除，HEAD判斷是否存在。
這里，友情推薦httpie，一個(gè)非常強(qiáng)大的http工具，個(gè)人感覺(jué)比curl還用，幾乎是命令行調(diào)試Elasticsearch的絕配。
一些使用httpie的例子:
# create
http POST :9200/blog/article/1 title="hello elasticsearch" tags:='["elasticsearch"]'
# get
http GET :9200/blog/article/1
# update
http PUT :9200/blog/article/1 title="hello elasticsearch" tags:='["elasticsearch", "hello"]'
# delete
http DELETE :9200/blog/article/1
# exists
http HEAD :9200/blog/article/1
索引和搜索
雖然Elasticsearch能自動(dòng)判斷field類型并建立合適的索引，但筆者仍然推薦自己設(shè)置相關(guān)索引規(guī)則，這樣才能更好為后續(xù)的搜索服務(wù)。
我們通過(guò)定制mapping的方式來(lái)設(shè)置不同field的索引規(guī)則。
而對(duì)于搜索，Elasticsearch提供了太多的搜索選項(xiàng)，就不一一概述了。
索引和搜索是Elasticsearch非常重要的兩個(gè)方面，直接關(guān)系到產(chǎn)品的搜索體驗(yàn)，但筆者現(xiàn)階段也僅僅是大概了解了一點(diǎn)，后續(xù)在詳細(xì)介紹。
同步MySQL數(shù)據(jù)
Elasticsearch是很強(qiáng)大，但要建立在有足量數(shù)據(jù)情況下面。我們的數(shù)據(jù)都在MySQL上面，所以如何將MySQL的數(shù)據(jù)導(dǎo)入Elasticsearch就是筆者最近研究的東西了。
雖然現(xiàn)在有一些實(shí)現(xiàn)，譬如elasticsearch-river-jdbc，或者elasticsearch-river-mysql，但筆者并不打算使用。
elasticsearch-river-jdbc的功能是很強(qiáng)大，但并沒(méi)有很好的支持增量數(shù)據(jù)更新的問(wèn)題，它需要對(duì)應(yīng)的表只增不減，而這個(gè)幾乎在項(xiàng)目中是不可能辦到的。
elasticsearch-river-mysql倒是做的很不錯(cuò)，采用了python-mysql-replication來(lái)通過(guò)binlog獲取變更的數(shù)據(jù)，進(jìn)行增量更新，但它貌似處理MySQL dump數(shù)據(jù)導(dǎo)入的問(wèn)題，不過(guò)這個(gè)筆者真的好好確認(rèn)一下？話說(shuō)，python-mysql-replication筆者還提交過(guò)pull解決了minimal row image的問(wèn)題，所以對(duì)elasticsearch-river-mysql這個(gè)項(xiàng)目很有好感。只是筆者決定自己寫一個(gè)出來(lái)。
為什么筆者決定自己寫一個(gè)，不是因?yàn)楣P者喜歡造輪子，主要原因在于對(duì)于這種MySQL syncer服務(wù)（增量獲取MySQL數(shù)據(jù)更新到相關(guān)系統(tǒng)），我們不光可以用到Elasticsearch上面，而且還能用到其他服務(wù)，譬如cache上面。所以筆者其實(shí)想實(shí)現(xiàn)的是一個(gè)通用MySQL syncer組件，只是現(xiàn)在主要關(guān)注Elasticsearch罷了。
項(xiàng)目代碼在這里go-mysql-elasticsearch，現(xiàn)已完成第一階段開發(fā)，內(nèi)部對(duì)接測(cè)試中。
go-mysql-elasticsearch的原理很簡(jiǎn)單，首先使用mysqldump獲取當(dāng)前MySQL的數(shù)據(jù)，然后在通過(guò)此時(shí)binlog的name和position獲取增量數(shù)據(jù)。
一些限制：
binlog一定要變成row-based format格式，其實(shí)我們并不需要擔(dān)心這種格式的binlog占用太多的硬盤空間，MySQL 5.6之后GTID模式都推薦使用row-based format了，而且通常我們都會(huì)把控SQL語(yǔ)句質(zhì)量，不允許一次性更改過(guò)多行數(shù)據(jù)的。
需要同步的table最好是innodb引擎，這樣mysqldump的時(shí)候才不會(huì)阻礙寫操作。
需要同步的table一定要有主鍵，好吧，如果一個(gè)table沒(méi)有主鍵，筆者真心會(huì)懷疑設(shè)計(jì)這個(gè)table的同學(xué)編程水平了。多列主鍵也是不推薦的，筆者現(xiàn)階段不打算支持。
一定別動(dòng)態(tài)更改需要同步的table結(jié)構(gòu)，Elasticsearch只能支持動(dòng)態(tài)增加field，并不支持動(dòng)態(tài)刪除和更改field。通常來(lái)說(shuō)，如果涉及到alter table，很多時(shí)候已經(jīng)證明前期設(shè)計(jì)的不合理以及對(duì)于未來(lái)擴(kuò)展的預(yù)估不足了。
更詳細(xì)的說(shuō)明，等到筆者完成了go-mysql-elasticsearch的開發(fā)，并通過(guò)生產(chǎn)環(huán)境中測(cè)試了，再進(jìn)行補(bǔ)充。
總結(jié)
最近一周，筆者花了不少時(shí)間在Elasticsearch上面，現(xiàn)在算是基本入門了。其實(shí)筆者覺(jué)得，對(duì)于一門不懂的技術(shù)，找一份靠譜的資料（官方文檔或者入門書籍），蛋疼的對(duì)著資料敲一遍代碼，不懂的再問(wèn)google，最后在將其用到實(shí)際項(xiàng)目，這門技術(shù)就算是初步掌握了，當(dāng)然精通還得在下點(diǎn)功夫。
現(xiàn)在筆者只是覺(jué)得Elasticsearch很美好，上線之后鐵定會(huì)有坑的，那時(shí)候只能慢慢填了。話說(shuō)，筆者是不是要學(xué)習(xí)下java了，省的到時(shí)候看不懂代碼就慘了。:-)
-
樓上的言語(yǔ)太過(guò)簡(jiǎn)單了！數(shù)據(jù)表都已經(jīng)創(chuàng)建起來(lái)了，假設(shè)我們已經(jīng)插入了許多的數(shù)據(jù)，我們就可以用自己喜歡的方式對(duì)數(shù)據(jù)表里面的信息進(jìn)行檢索和顯示了，比如說(shuō)：可以象下面這樣把整個(gè)數(shù)據(jù)表內(nèi)的內(nèi)容都顯示出來(lái)
select * from president;
也可以只選取某一個(gè)數(shù)據(jù)行里的某一個(gè)數(shù)據(jù)列
select birth from president where last_name=’Eisenhower’;
select語(yǔ)句的通用形式如下：
select 你要的信息
from 數(shù)據(jù)表(一個(gè)或多個(gè))
where 滿足的條件
select語(yǔ)句有幾個(gè)子句，他們的各種搭配能幫你查出最感興趣的信息，這些子句可以很簡(jiǎn)單，也可以很復(fù)雜,看看作者是如何詳細(xì)講解的
１， 用各種操作符來(lái)設(shè)定檢索條件
要想讓select語(yǔ)句只把滿足特定條件的記錄檢索出來(lái)，就必須給它加上where字句來(lái)設(shè)置數(shù)據(jù)行的檢索條件。只有這樣，才能有選擇地把數(shù)據(jù)列的取值滿足特定要求的那些數(shù)據(jù)行挑選出來(lái)?？梢葬槍?duì)任何類型的值進(jìn)行查找，比如說(shuō)，對(duì)數(shù)值進(jìn)行搜索
select * from score where score95; //顯示所有分?jǐn)?shù)在９５分以上的信息
也可以針對(duì)字符串值進(jìn)行查找
select last_name,first_name from president where last_name=’Tom’; //找出所有姓tom的總統(tǒng)
還可以對(duì)不同類型的值進(jìn)行組合查找
select last_name,first_name,birth,state from president
where birth’1950-1-1’ and (state=’VA’ or state=’BA’);
//找出1950年前出生于VA州或ＢＡ州的總統(tǒng)
可見(jiàn) where子句中可以使用的是算術(shù)操作符（+-*/%）,比較操作符(=)以及邏輯運(yùn)算符，我們應(yīng)該熟練理解這些操作符的含義（都很簡(jiǎn)單）
２， NULL 值的特別處理
這是一種不屬于任何類型的值。它通常用來(lái)表示“沒(méi)有數(shù)據(jù)”“數(shù)據(jù)未知”“數(shù)據(jù)缺失”“數(shù)據(jù)超出取值范圍”“與本數(shù)據(jù)列無(wú)關(guān)”“與本數(shù)據(jù)列的其它值不同”等多種含義。在許多情況下，NULL 值是非常有用的。
我們的各種操作符是不能對(duì)NULL 值進(jìn)行處理的，如果相對(duì)NULL 值進(jìn)行查找，用的是 is null 或 is not null 來(lái)進(jìn)行判斷，舉例如下：
select last_name,first_name,birth,state from president
where death is null; //找出所有沒(méi)死的總統(tǒng)
在某些情況下，NULL 值是很有用的類型，大家慢慢就會(huì)理解的。
３， 查詢結(jié)果進(jìn)行排序
一般說(shuō)來(lái)，如果創(chuàng)建了一個(gè)數(shù)據(jù)表并向里面插入了一些記錄，當(dāng)發(fā)出一條select * from name命令的時(shí)候，數(shù)據(jù)記錄在查詢結(jié)果中的先后順序通常與它們被插入時(shí)的先后順序一樣．這當(dāng)然符合我們的思維習(xí)慣．但這只是一種＂想當(dāng)然＂的假設(shè)而已，事實(shí)上，但記錄被刪除時(shí)，數(shù)據(jù)庫(kù)中會(huì)產(chǎn)生一些空的區(qū)域，MYSQL會(huì)用新的記錄來(lái)填補(bǔ)這些區(qū)域，也就是說(shuō)，這個(gè)時(shí)候本假設(shè)就不正確了．因此我們必須記住一點(diǎn)，從服務(wù)器返回的記錄行的先后順序是沒(méi)有任何保證的！如果想要按照一定的順序，就必須使用order by 子句來(lái)設(shè)置這個(gè)順序．
select last_name,first_name,birth,state from president
order by last_name; //讓總統(tǒng)們的名字按字母順序排列
還可以設(shè)置排列的升序降序
select last_name,first_name from president
order by state DESC,last_name ASC;
//先按照出生地的降序排列，同出生地的按照姓氏的升序排列
注意：如果結(jié)果中含有NULL 值，默認(rèn)情況下他們總是出現(xiàn)在查詢結(jié)果的開頭。
４， 限制查詢結(jié)果中數(shù)據(jù)行個(gè)數(shù)
這個(gè)簡(jiǎn)單，只要用limit 子句就可以了，看兩個(gè)例子：
select last_name,first_name,birth,state from president
order by birth limit 5; //只想看前５個(gè)
order by birth limit 10,5; //返回從第11個(gè)記錄開始的5個(gè)記錄（跳過(guò)了10個(gè)）
小技巧：從president表中隨機(jī)找出一個(gè)總統(tǒng)來(lái)玩：
select last_name,first_name,birth,state from president
order by rand() limit 1; //這是用了表達(dá)式求值的方法，在哪里都管用
５， 對(duì)輸出列進(jìn)行求值和命名
為了提高效率，MYSQL還可以把表達(dá)式的計(jì)算結(jié)果當(dāng)作輸出列的值。表達(dá)式可以很簡(jiǎn)單，也可以很復(fù)雜。例如：下面這個(gè)查詢有兩個(gè)輸出列，前一個(gè)輸出列對(duì)應(yīng)一個(gè)非常簡(jiǎn)單的表達(dá)式（一個(gè)常數(shù)），而后一個(gè)輸出列則對(duì)應(yīng)著一個(gè)使用了多個(gè)算術(shù)運(yùn)算符和兩個(gè)函數(shù)調(diào)用的復(fù)雜表達(dá)式。
Select 17,format(sqrt(3*3+4*4),0))
輸出：17 5
再看這個(gè)命令：把兩個(gè)輸出列合并成一個(gè)
select concat(first_namem,’ ‘,last_name),concat(city,’,’,state) from president;
如果合并之后輸出列的標(biāo)題過(guò)長(zhǎng)，則可以給其一個(gè)別名，如：
select concat(first_namem,’ ‘,last_name) as name,
concat(city,’,’,state) as birth place
from president;這樣就比較美觀了。
６， 和日期有關(guān)的問(wèn)題
首先記?。涸贛YSQL中，年份是放到最前面的！我們通常對(duì)日期進(jìn)行下列操作：
按日期進(jìn)行排序
查找某個(gè)日期或日期范圍
提取日期中的年，元，日各個(gè)部分
計(jì)算兩個(gè)日期的間隔
用一個(gè)日期求出另外一個(gè)日期
看例子：
select * from event where date=’2002-10-01’ //看看這天有何考試信息？
select last_name,first_name,birth,state from president
where death’1900-01-01’ and death’2000-01-01’; //看看上個(gè)世紀(jì)死了幾個(gè)？
三個(gè)函數(shù)year,month,dayofmonth可以分別分離出日期中的年月日來(lái)。
select last_name,first_name,birth from president
where month(birth)=3; //誰(shuí)生在3月 ？
where month(birth)=7 and dayofmonth(birth) =6; //誰(shuí)生在7月6日？(湯姆克魯斯？)
函數(shù)to_days可以把日期轉(zhuǎn)換為天數(shù)。
select last_name,first_name,birth to_days(death)-to_days(birth) as age from president
可以看看這幫家伙都活了多少天！你自己把它改為年吧。
日期值的減法運(yùn)算還能幫我們計(jì)算出現(xiàn)在距離某個(gè)特定日期還有多長(zhǎng)的時(shí)間，這正是我們用來(lái)找到需要在近期內(nèi)繳納會(huì)費(fèi)的會(huì)員的辦法：
select last_name,first_name,expiration from member
where (to_days(expiration)-to_days(curdate())60; //有些人60天內(nèi)需要花錢了！
７， 模式匹配
有些情況下，模糊查詢是很必要的，我們使用like和not like加上一個(gè)帶通配符的字符串就可以了。共有兩個(gè)通配符”_”(單個(gè)字符)和””（多個(gè)字符）
select concat(first_namem,’ ‘,last_name) as name,
where last_name like ‘W%’; //找到以Ｗ或w開頭的人
where last_name like ‘％W%’; //找到名字里面Ｗ或w開頭的人
８， 設(shè)置和使用SQL變量
MYSQL 3.23.6以上的版本可以使用查詢結(jié)果來(lái)設(shè)置變量，我們就能夠方面的把一些結(jié)果保存起來(lái)以供他用。變量的命名規(guī)格是：@name, 賦值語(yǔ)法是 @name:=value ( pascal?) 使用起來(lái)也簡(jiǎn)單：
select @birth:=birth from president
where last_name =’adsltiger’; //執(zhí)行完成后我們就就會(huì)有一個(gè)@birth變量可用
用一下試試：
select concat(first_namem,’ ‘,last_name) as name from president
where birth@birth order by birth; //看看那些人比我大！
＊9， 生成統(tǒng)計(jì)信息
單純依靠手工來(lái)生成統(tǒng)計(jì)信息是一項(xiàng)既艱苦又耗時(shí)還容易出錯(cuò)的工作，如果我們能熟練掌握用數(shù)據(jù)庫(kù)來(lái)生成各種統(tǒng)計(jì)信息的技巧，他就會(huì)成為很有威力的信息處理工具。作者在這里用了許多篇幅講這個(gè)主題，為了便于大家理解，我分解開來(lái)論述：
9.1 找出一組數(shù)據(jù)中到底有多少種不同的值是一項(xiàng)比較常見(jiàn)的統(tǒng)計(jì)工作，而關(guān)鍵字distinct就可以把查詢結(jié)果中的重復(fù)數(shù)據(jù)清除掉。如
select distinct state from president //看看美國(guó)總統(tǒng)們都來(lái)自那些州？(重復(fù)的不計(jì))
9.2用count()函數(shù)來(lái)統(tǒng)計(jì)相關(guān)記錄的個(gè)數(shù)，注意其使用方法：count(*)計(jì)算所有的，NULL也要；count(數(shù)據(jù)列名稱) NULL值不計(jì)算在內(nèi)。
select count(*) from president;
9.3如果我們想知道班級(jí)內(nèi)的男女生數(shù)目？該如何查詢呢？最簡(jiǎn)單的方法是
select count(*) from student where sex=’f’;
select count(*) from student where sex=’m
但是如果使用count函數(shù)結(jié)合group by關(guān)鍵字，一行命令就搞定了
select sex,count(*) f rom student group by sex;
我們可以看到，與反復(fù)使用彼此類似的查詢來(lái)分別統(tǒng)計(jì)某數(shù)據(jù)列不同取值出現(xiàn)次數(shù)的做法相比， 把count(*)和group by字句相結(jié)合使用有許多優(yōu)點(diǎn)，主要表現(xiàn)在：
在開始統(tǒng)計(jì)自前，不必知道被統(tǒng)計(jì)的數(shù)據(jù)列里面有多少種不同的取值
因?yàn)橹挥昧艘粋€(gè)查詢命令，我們可以對(duì)輸出做排序的處理
select state,count(*) as count from president
group by state order by count desc limt4; //看看出生總統(tǒng)最多的前四個(gè)州是哪幾個(gè)？
9.4除了count()，我們還用其他一些統(tǒng)計(jì)函數(shù)，如求出最小值的min()，求最大值的max()，求和的sum()，求平均值的avg()，在實(shí)際工作中，這些函數(shù)時(shí)經(jīng)常用到的！
*10, 從多個(gè)表提取信息
我們目前的例子都是從一個(gè)表里面提取信息，但數(shù)據(jù)庫(kù)的真正威力還在于用“關(guān)系”來(lái)綜合多個(gè)數(shù)據(jù)表里面的記錄，這種操作稱之為“關(guān)聯(lián)”或“結(jié)合”我們可以看到，select需要給出多個(gè)數(shù)據(jù)表里面的信息（不可重復(fù)）；from需要知道從哪幾個(gè)表里面做事；where則對(duì)幾個(gè)表之間的關(guān)聯(lián)信息作出詳細(xì)的描述。
首先我們要學(xué)習(xí)最可靠的數(shù)據(jù)列引用方式：數(shù)據(jù)表名.數(shù)據(jù)列名。這樣在查詢中就一定不會(huì)混淆這個(gè)數(shù)據(jù)列到底在哪一個(gè)表里。
例子１:查詢某一天內(nèi)的學(xué)生們的考試成績(jī)，用學(xué)號(hào)列出。
select scroe.student_id,event_date,score.score.event.type
from event,score
where event.date=’2003-09-12’
and event.event_id=score.event_id
首先，利用event數(shù)據(jù)表把日期映射到一個(gè)考試事件編號(hào)，在利用這個(gè)編號(hào)把score表內(nèi)相匹配的考試分?jǐn)?shù)找出來(lái)。關(guān)聯(lián)兩個(gè)表，一個(gè)查詢搞定。
例子2:查詢某一天內(nèi)的學(xué)生們的考試成績(jī)，用姓名列出。
select student.name event.name,score.score,event.type
form event,score,student
where event.date=’2003-09-12’
and event.event_id= score.event_id
and scroe.student_id=student.student_id;
關(guān)聯(lián)三個(gè)表，一個(gè)查詢搞定。
例子３:查詢一下缺席學(xué)生的名字，學(xué)號(hào)，缺席次數(shù)
select student.student_id,student_name
count(absence.date) as absences
from student,absence
where student.student_id=absence.student_id //關(guān)聯(lián)條件
group by student.student_id;
簡(jiǎn)單的關(guān)聯(lián)操作就介紹到這里。事實(shí)上，對(duì)于關(guān)聯(lián)的知識(shí)我們需要學(xué)的很多很多，比如說(shuō)，我們懷疑某一個(gè)數(shù)據(jù)表內(nèi)不存在和我們相關(guān)的數(shù)據(jù)，把么在關(guān)聯(lián)查詢的時(shí)候如何處理這個(gè)表呢？這就涉及到內(nèi)聯(lián)接，外聯(lián)接，左聯(lián)接，右聯(lián)接的許多新概念了。不知道大家還有沒(méi)有信心向下看我的筆記？在本書第四章里面，對(duì)關(guān)聯(lián)進(jìn)行了十分詳細(xì)的論述，看來(lái)“在SQL里面，干粗活的是select”的說(shuō)法再對(duì)不過(guò)了。
我們了解了select命令的如此之多的用法，感到了它的靈活性，許多字句的組合能夠形成一個(gè)非常“精妙”的ＳＱＬ語(yǔ)句，在基礎(chǔ)沒(méi)有打好之前，我等初學(xué)者目前還沒(méi)有必要去鉆研那些技巧性很高的東西，“一定程度的創(chuàng)造性是必要的，但太專業(yè)或充滿技巧的代碼則是各種 bug的發(fā)源地，同時(shí)也是若干個(gè)不眠之夜的前奏”
注意:Group by 里面沒(méi)有的字段,在Select字段列表里就必須加上聚合函數(shù)(sum,max,min,count...),不然那句SQL就是錯(cuò)的
mysql筆記：數(shù)據(jù)導(dǎo)入導(dǎo)出 mysql自帶有mysqldump命令，可以把數(shù)據(jù)庫(kù)做備份用。 MySQL有很多可以導(dǎo)入數(shù)據(jù)的方法，然而這些只是數(shù)據(jù)傳輸中的一半，另外的一般是從MySQL數(shù)據(jù)庫(kù)中導(dǎo)出數(shù)據(jù)。有許多的原因我們需要導(dǎo)出數(shù)據(jù)。一個(gè)重要的原因是用于備份數(shù)據(jù)庫(kù)。數(shù)據(jù)的造價(jià)常常是昂貴的，需要謹(jǐn)慎處理它們。經(jīng)常地備份可以幫助防止寶貴數(shù)據(jù)地丟失；另外一個(gè)原因是，也許您希望導(dǎo)出數(shù)據(jù)來(lái)共享。 在這個(gè)信息技術(shù)不斷成長(zhǎng)的世界中，共享數(shù)據(jù)變得越來(lái)越常見(jiàn)。 比方說(shuō)Macmillan USA維護(hù)護(hù)著一個(gè)將要出版的書籍的大型數(shù)據(jù)庫(kù)。這個(gè)數(shù)據(jù)庫(kù)在許多書店之間共享，這樣他們就知道哪些書將會(huì)很快出版。醫(yī)院越來(lái)越走向采用無(wú)紙病歷記錄，這樣這些病歷可以隨時(shí)跟著你。世界變得越來(lái)越小，信息也被共享得越來(lái)越多。有很多中導(dǎo)出數(shù)據(jù)得方法，它們都跟導(dǎo)入數(shù)據(jù)很相似。因?yàn)椋吘?，這些都只是一種透視得方式。從數(shù)據(jù)庫(kù)導(dǎo)出的數(shù)據(jù)就是從另一端導(dǎo)入的數(shù)據(jù)。這里我們并不討論其他的數(shù)據(jù)庫(kù)各種各樣的導(dǎo)出數(shù)據(jù)的方法，您將學(xué)會(huì)如何用MySQL來(lái)實(shí)現(xiàn)數(shù)據(jù)導(dǎo)出。 注意:mysqldump比直接拷貝數(shù)據(jù)庫(kù)文件夾速度要慢 但,直接復(fù)制文件夾不能100%轉(zhuǎn)移到其它機(jī)子上用使用mysqldump工具，基本用法是：shell mysqldump [OPTIONS] database [tables]如果你不給定任何表，整個(gè)數(shù)據(jù)庫(kù)將被導(dǎo)出。通過(guò)執(zhí)行mysqldump --help，你能得到你mysqldump的版本支持的選項(xiàng)表。注意，如果你運(yùn)行mysqldump沒(méi)有--quick或--opt選項(xiàng)，mysqldump將在導(dǎo)出結(jié)果前裝載整個(gè)結(jié)果集到內(nèi)存中，如果你正在導(dǎo)出一個(gè)大的數(shù)據(jù)庫(kù)，這將可能是一個(gè)問(wèn)題。
重慶分公司
重慶分公司
