2016.10.14r

1.什么是數(shù)據(jù)持久層识颊？
持久層允耿，又叫數(shù)據(jù)訪問層。是和數(shù)據(jù)庫直接打交道的層俺泣，所有增刪改查的操作全在這個(gè)層里疗认。

數(shù)據(jù)持久層是位于業(yè)務(wù)邏輯層和數(shù)據(jù)庫之間，用于存儲數(shù)據(jù)的一個(gè)模塊伏钠。把數(shù)據(jù)持久層單獨(dú)作為J2EE體系的一個(gè)層提出來的原因就是能夠在對象－關(guān)系數(shù)據(jù)庫之間提供一個(gè)成功的企業(yè)級映射解決方案横漏，盡最大可能彌補(bǔ)這兩種范例之間的差異，其目的是通過持久層的框架將數(shù)據(jù)庫存儲從服務(wù)層中分離出來是熟掂，自己接觸到的持久層框架主要是缎浇，直接自己編寫JDBC等SQL語句；

JDBC的全稱是Java DataBase Connectivity赴肚，即Java數(shù)據(jù)庫連接华畏。它是一套行業(yè)標(biāo)準(zhǔn)的API鹏秋，可以在Java應(yīng)用程序中與關(guān)系型數(shù)據(jù)庫建立連接，并執(zhí)行相關(guān)操作亡笑，例如Oracle侣夷，DB2等主流數(shù)據(jù)庫產(chǎn)品。

2.PL/sql與普通sql的區(qū)別仑乌？
普通sql它不面向過程百拓，即前一條語句與后一條語句無關(guān)。
PL/SQL晰甚，Oracle對SQL標(biāo)準(zhǔn)的擴(kuò)充衙传，增加了面向過程的功能，所以可以用來編寫存儲過程厕九、存儲函數(shù)蓖捶、觸發(fā)器等等。

3.數(shù)據(jù)庫優(yōu)化之——i/o開銷和cpu開銷分別是什么扁远？過大時(shí)如何解決俊鱼？

• i/o開銷表示的是 在寫入文件和讀取文件時(shí)候的壓力值。io大會對磁盤造成壓力影響性能畅买；
• cpu開銷并闲，當(dāng)一條SQL發(fā)送給數(shù)據(jù)庫服務(wù)器后，系統(tǒng)首先會將SQL字符串進(jìn)行hash運(yùn)算谷羞，得到hash值后再從服務(wù)器內(nèi)存里的SQL緩存區(qū)中進(jìn)行檢索帝火，如果有相同的SQL字符，并且確認(rèn)是同一邏輯的SQL語句湃缎，則從共享池緩存中取出SQL對應(yīng)的執(zhí)行計(jì)劃犀填，根據(jù)執(zhí)行計(jì)劃讀取數(shù)據(jù)并返回結(jié)果給客戶端。
  如果在共享池中未發(fā)現(xiàn)相同的SQL嗓违，**則根據(jù)SQL邏輯新生成一條執(zhí)行計(jì)劃并保存在SQL緩存區(qū)中九巡，然后根據(jù)執(zhí)行計(jì)劃讀取數(shù)據(jù)并返回結(jié)果給客戶端。如果每條SQL都會產(chǎn)生執(zhí)行計(jì)劃靠瞎，這樣會導(dǎo)致共享池耗盡，緩存命中率也很低求妹。

解決方法是：使用綁定變量

2016.10.12r

1.如何將某個(gè)字段中的值清空乏盐？如何刪除某個(gè)表中的字段？
UPDATE table_name set filed_name='';
Alter TABLE table_name DROP COLUMN field_name;
Alter TABLE table_name DROP （field_name1制恍，field_name1）;同時(shí)刪除兩個(gè)字段父能。

2.oracle的執(zhí)行計(jì)劃中表的鏈接方式有幾種，分別適用在什么情況下净神？
在日澈瘟撸基于數(shù)據(jù)庫應(yīng)用的開發(fā)過程中溉委，我們經(jīng)常需要對多個(gè)表或者數(shù)據(jù)源進(jìn)行關(guān)聯(lián)查詢而得出我們需要的結(jié)果集。那么Oracle到底存在著哪幾種連接方式爱榕？

• （1）嵌套循環(huán)連接(nested loop)
  嵌套循環(huán)連接的工作方式是這樣的：
  1瓣喊、 Oracle首先選擇一張表作為連接的驅(qū)動(dòng)表，這張表也稱為外部表（Outer Table）黔酥。由驅(qū)動(dòng)表進(jìn)行驅(qū)動(dòng)連接的表或數(shù)據(jù)源稱為內(nèi)部表（Inner Table）藻三。
  2、 提取驅(qū)動(dòng)表中符合條件的記錄跪者，與被驅(qū)動(dòng)表的連接列進(jìn)行關(guān)聯(lián)查詢符合條件的記錄棵帽。在這個(gè)過程中，Oracle首先提取驅(qū)動(dòng)表中符合條件的第一條記錄渣玲，再與內(nèi)部表的連接列進(jìn)行關(guān)聯(lián)查詢相應(yīng)的記錄行逗概。在關(guān)聯(lián)查詢的過程中，Oracle會持續(xù)提取驅(qū)動(dòng)表中其他符合條件的記錄與內(nèi)部表關(guān)聯(lián)查詢忘衍。

• （2）哈希連接(hash join)
  哈希連接分為兩個(gè)階段逾苫，如下。
  1淑履、 構(gòu)建階段：優(yōu)化器首先選擇一張小表做為驅(qū)動(dòng)表隶垮，運(yùn)用哈希函數(shù)對連接列進(jìn)行計(jì)算產(chǎn)生一張哈希表。通常這個(gè)步驟是在內(nèi)存（hash_area_size）里面進(jìn)行的秘噪，因此運(yùn)算很快狸吞。
  2、 探測階段：優(yōu)化器對被驅(qū)動(dòng)表的連接列運(yùn)用同樣的哈希函數(shù)計(jì)算指煎，得到的結(jié)果與前面形成的哈希表進(jìn)行探測返回符合條件的記錄蹋偏。這個(gè)階段中如果被驅(qū)動(dòng)表的連接列的值沒有與驅(qū)動(dòng)表連接列的值相等的話，那么這些記錄將會被丟棄而不進(jìn)行探測至壤。
  哈希連接只適用于等值連接威始。

• （3）** 排序合并連接(merge join)**
  在排序合并連接中是沒有驅(qū)動(dòng)表的概念的，兩個(gè)互相連接的表按連接列的值先各自排序像街，排序完后形成的結(jié)果集再互相進(jìn)行合并連接提取符合條件的記錄黎棠。相比嵌套循環(huán)連接，排序合并連接比較適用于返回大數(shù)據(jù)量的結(jié)果镰绎。
  排序合并連接在數(shù)據(jù)表預(yù)先排序好的情況下效率是非常高的脓斩。

3.數(shù)據(jù)庫的性能優(yōu)化方法有哪些？
（1）傳統(tǒng)的有：

• 建立分區(qū)表畴栖；
  一般而言随静，數(shù)據(jù)庫的讀寫操作的數(shù)量級是不一樣的，讀操作要遠(yuǎn)遠(yuǎn)多于寫操作，而讀操作需要查找到對應(yīng)的數(shù)據(jù)行才行燎猛，因此為了加快讀操作恋捆，數(shù)據(jù)庫會為表數(shù)據(jù)創(chuàng)建索引。但是有利必有弊重绷，創(chuàng)建索引會帶來額外的存儲成本沸停，尤其是在海量數(shù)據(jù)的情況下，索引會很龐大论寨，增刪改操作都有可能會引發(fā)索引結(jié)構(gòu)的變動(dòng)星立，反而得不償失。而分庫分表或分區(qū)表技術(shù)的本質(zhì)葬凳，就是將龐大的數(shù)據(jù)表劃分多個(gè)表绰垂，規(guī)避了之前所說的弊端。

• 建立索引火焰；
• 適當(dāng)冗余
• 返回更少數(shù)據(jù)（不必要的字段）

（2）其他方法：

• 存儲過程劲装，如果采用存儲過程你可以將整個(gè)業(yè)務(wù)邏輯封裝在存儲過程里，然后在客戶端直接調(diào)用存儲過程處理昌简，這樣可以減少數(shù)據(jù)庫交互的成本占业。
• 減少應(yīng)用和數(shù)據(jù)庫的交互次數(shù)。如一次插入一個(gè)sql語句纯赎，若有10000條則要執(zhí)行10000次谦疾，對于DML語句（insert，delete犬金，update等）使用batch操作念恍。

2016.10.10r

1.事務(wù)型數(shù)據(jù)庫與分析型數(shù)據(jù)庫的特征及二者區(qū)別？

• 事務(wù)型數(shù)據(jù)庫主要是實(shí)時(shí)的晚顷，面向應(yīng)用的數(shù)據(jù)庫峰伙，響應(yīng)及時(shí)性要求很高，只關(guān)注最近一段時(shí)間的數(shù)據(jù)该默。就是平時(shí)搭建的服務(wù)都叫事務(wù)型數(shù)據(jù)庫瞳氓。

• 分析型數(shù)據(jù)庫主要是用于在大量數(shù)據(jù)中分析規(guī)律的，一般存儲的數(shù)據(jù)時(shí)間跨度長栓袖，數(shù)據(jù)量大匣摘，對實(shí)時(shí)性要求不高，通過查詢分析規(guī)律趨勢裹刮，用于產(chǎn)品決策等音榜，如數(shù)據(jù)倉庫。

兩者所面向的目標(biāo)不一樣必指。

2016.10.7r

國慶假就這樣快結(jié)束了囊咏，真不知道后面自己能夠去哪里恕洲。不過始終保持樂觀吧塔橡，總在變的更好一點(diǎn)梅割。

1.關(guān)于樂觀鎖和悲觀鎖的區(qū)別？
樂觀鎖(Optimistic Lock), 顧名思義葛家，就是很樂觀户辞，每次去拿數(shù)據(jù)的時(shí)候都認(rèn)為別人不會修改，所以不會上鎖癞谒，但是在更新的時(shí)候會判斷一下在此期間別人有沒有去更新這個(gè)數(shù)據(jù)底燎，可以使用版本號等機(jī)制。樂觀鎖適用于多讀的應(yīng)用類型弹砚，這樣可以提高吞吐量双仍。

悲觀鎖(Pessimistic Lock), 顧名思義，就是很悲觀桌吃，每次去拿數(shù)據(jù)（包括讀或者寫數(shù)據(jù)）的時(shí)候都認(rèn)為別人會修改朱沃，所以每次在拿數(shù)據(jù)的時(shí)候都會上鎖，這樣別人想拿這個(gè)數(shù)據(jù)就會block直到它拿到鎖茅诱。傳統(tǒng)的關(guān)系型數(shù)據(jù)庫里邊就用到了很多這種鎖機(jī)制逗物，比如行鎖，表鎖等瑟俭，讀鎖翎卓，寫鎖等，都是在做操作之前先上鎖摆寄。

2.如何命令啟動(dòng)數(shù)據(jù)庫失暴？
net start mysql

3.oracle中如何查看執(zhí)行計(jì)劃？
explain plan for select t.*, t.rowid from A5 t where t.l = '101214' and t.k like '%8號' order by i ;

2016.9.29r

中午等外賣的時(shí)候看到兩個(gè)問題椭迎，一是數(shù)據(jù)庫事務(wù)锐帜，二是EM算法，于是隨便查了一下畜号。

下午快四點(diǎn)缴阎，自己中期答辯結(jié)束，后面自己還是要細(xì)化一點(diǎn)简软，不然真的一點(diǎn)創(chuàng)新沒有蛮拔，缺乏思考。

圖片發(fā)自簡書App

2016.9.23r更新

今天去面金蝶的數(shù)據(jù)庫支持崗位痹升，問了很多數(shù)據(jù)庫開發(fā)方面的問題建炫，這里再整理學(xué)習(xí)一下。

• 1.java和c++都是面向?qū)ο蟮恼Z言疼蛾，為什么很多開發(fā)都用java不用c++肛跌？
  java的平臺兼容性更好，使得代碼的可利用性和移植性非常好。

• 2.數(shù)據(jù)庫中字段的冗余會導(dǎo)致什么問題衍慎，屬于違反了第幾范式转唉？
  第一范式（1NF）：指數(shù)據(jù)庫表的每一列都是不可分割的基本數(shù)據(jù)項(xiàng)。
  第二范式（2NF）：要求非主鍵列必須依賴于主鍵列稳捆。（在有些時(shí)候赠法，表連接會讓計(jì)算機(jī)做大量的匹配和計(jì)算，是比較消耗資源（資源主要是指CPU和內(nèi)存）乔夯。為了提高效率砖织，所以可以違反2NF，出現(xiàn)字段冗余末荐，比如班級（班級編號侧纯，班級名稱）表，對應(yīng)帶來的問題是可能導(dǎo)致數(shù)據(jù)的不一致性甲脏。）
  第三范式（3NF）： 非主鍵列之間必須相互獨(dú)立 - 3NF可以不滿足

• 3.數(shù)據(jù)庫的優(yōu)化
  主要有基于規(guī)則的優(yōu)化（rules）茂蚓、基于成本的優(yōu)化（cost）
  1）基于規(guī)則的優(yōu)化
  通常是基于表查詢的優(yōu)化，方法有：選擇最有效率的表順序剃幌，避免select *的出現(xiàn)聋涨，用TRUNCATE替代DELETE等
  2）基于成本的優(yōu)化
  適當(dāng)?shù)娜哂啵黾佑?jì)算列负乡，提高數(shù)據(jù)庫的運(yùn)行效率;
  建立索引牍白；
  存儲過程、視圖抖棘、函數(shù)的適當(dāng)使用茂腥；
  數(shù)據(jù)庫的表越少越好，表的字段越少越好切省；

• 4.刪除一個(gè)基本表后最岗，則與基本表相關(guān)的：數(shù)據(jù)和索引也會被刪除，視圖還存在只是不能用了朝捆。

2016.9.22r更新

以后關(guān)于sql的知識點(diǎn)就都在這里更新了般渡，今天早上做了數(shù)據(jù)庫支持的一些題目，有以下知識點(diǎn)需要注意芙盘。

• 1驯用、下面兩個(gè)語法是錯(cuò)誤的：
  age=NULL；age =(select age from table where...)

• 2儒老、系統(tǒng)自定義的存儲過程
  sp_helpdb:用于報(bào)告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息蝴乔。

• 3、視圖中進(jìn)行insert驮樊、delete薇正、update是否可行
  1）insert片酝， 視圖中不能包含多個(gè)字段值的組合，或者包含使用統(tǒng)計(jì)函數(shù)的結(jié)果挖腰。 視圖中不能包含DISTINCT或GROUP BY子句钠怯。
  2）delete， 當(dāng)一個(gè)視圖由兩個(gè)以上基表構(gòu)成時(shí)曙聂，不允許刪除視圖中的數(shù)據(jù)。
  3）update鞠鲜，當(dāng)Oracle數(shù)據(jù)庫中對于單一表查詢創(chuàng)建視圖宁脊，且不帶統(tǒng)計(jì)函數(shù)，可以進(jìn)行update贤姆；對于多表查詢創(chuàng)建視圖榆苞，不能直接update。

• 4霞捡、統(tǒng)計(jì)函數(shù)sum(),avg(),max()均能忽略字段中的空值坐漏，但是count()不能

• 5、sql中變量的賦值
  1）局部變量以一個(gè)@符號開頭碧信。
  set @variable_name = value
  select @variable_name = value
  2）全局變量以兩個(gè)@@符號作為開頭赊琳。
  set @@variable_name = value，改變?nèi)肿兞康那疤崾怯衧uper權(quán)限

那么使用select和set賦值的區(qū)別在于：
返回一行值時(shí)用select和set均可以砰碴，返回多行時(shí)只能用select躏筏。

1.png

2016.9.20r更新

問題：如果數(shù)據(jù)庫中某張表的數(shù)據(jù)量很大，那么在建表的時(shí)候就要去考慮查詢優(yōu)化的問題呈枉，有什么辦法趁尼？

可以建立索引、建立分區(qū)表猖辫、設(shè)計(jì)并行查詢酥泞，增快查詢速度。
（主要講建立索引為什么可以增快查詢速度）
首先明白為什么索引會增加速度啃憎，DB在執(zhí)行一條Sql語句的時(shí)候芝囤，默認(rèn)的方式是根據(jù)搜索條件進(jìn)行全表掃描，遇到匹配條件的就加入搜索結(jié)果集合辛萍。如果我們對某一字段增加索引凡人，查詢時(shí)就會先去索引列表中一次定位到特定值的行數(shù)，大大減少遍歷匹配的行數(shù)叹阔，所以能明顯增加查詢的速度挠轴。
那么在任何時(shí)候都應(yīng)該加索引么？或者有什么缺點(diǎn)耳幢？
這里有幾個(gè)反例：

• 1岸晦、如果每次都需要取到所有表記錄欧啤，無論如何都必須進(jìn)行全表掃描了，那么是否加索引也沒有意義了启上。
• 2邢隧、對非唯一的字段，例如“性別”這種大量重復(fù)值的字段（這種情況叫可選擇性不強(qiáng)）冈在，增加索引也沒有什么意義倒慧。
  其解決方法是：使用聯(lián)合索引
  普通建立索引：
  create index Stuname on student(name);
  以title中的name和title創(chuàng)建索引：
  CREATE INDEX idxtitle ON title (title,name)
• 3、對于記錄比較少的表包券，增加索引不會帶來速度的優(yōu)化反而浪費(fèi)了存儲空間纫谅，因?yàn)樗饕切枰鎯臻g的。
• 4溅固、最后還有個(gè)致命缺點(diǎn)是對于update/insert/delete的每次執(zhí)行付秕，字段的索引都必須重新計(jì)算更新。

2016.9.19r更新

• 1）左連接侍郭、右連接询吴、inner連接，full連接
• 2）修改表：
  alter table 教師 add 獎(jiǎng)金 int
  alter table 教師 drop 獎(jiǎng)金
  alter table 教師 rename 獎(jiǎng)金 to 津貼

• 3）表權(quán)限的賦予：

  
  
  1.png
• 4）怎樣清空表數(shù)據(jù)亮元，但不刪除表結(jié)構(gòu)
  delete from tablename或者delete * from table_name
  truncate table tablename
• 5）外鍵能不能為空
  外鍵可以為空猛计，為空表示其值還沒有確定；
  如果不為空爆捞，剛必須為主鍵相同有滑。

1、根據(jù)原始表嵌削，找出有重復(fù)

購買行為的用戶id毛好，產(chǎn)品代碼，行為類型（申購苛秕，認(rèn)購肌访，定投等）

#其中知識點(diǎn)：
-直接select distinct * 可以篩去所有重復(fù)的記錄；
-統(tǒng)計(jì)每一條重復(fù)記錄出現(xiàn)的次數(shù)艇劫，直接用：
    select xx,xx,xx,xx,count(*) from table group by xx,xx,xx,xx

--由于導(dǎo)入到數(shù)據(jù)庫之后吼驶，所有的數(shù)據(jù)不知道怎么全增加了兩倍，所以先去重并放入ceshi_temp1表中
/*create table ceshi_temp1 as
(select distinct * from ceshi);*/

--找出多次購買的用戶id店煞，和基金代碼
--create table ceshi_temp2 as
select cus_id,jjdm from ceshi_temp1
where ywdm in ('120','122','139','137')
group by cus_id,jjdm
having count(cus_id)>=2;

--根據(jù)有多次購買行為的用戶id蟹演，反過來找出所有的購買行為信息
select a.cus_id, a.jjdm, a.ywdm,count(*)
from ceshi_temp1 a,
     ceshi_temp2 b
where a.cus_id =b.cus_id
group by a.cus_id, a.jjdm, a.ywdm
order by a.cus_id

2、group by 和 over partition by的區(qū)別
二者均是統(tǒng)計(jì)函數(shù)顷蟀，但 over partition by能夠更詳細(xì)的展現(xiàn)篩選出來的函數(shù)的明細(xì)酒请。

NAME DEPT SALARY
A         10     1000
B         10     2000
C         20     1500
D         20     3000
E         10     1000   
用over partition by 我就可以查詢到每位員工本來的具體信息和它所在部門的總工資：
select name,dept,salary,sum(salary) over (partition by dept) total_salary from salary;  
name       dept         salary      tatal_salary
A        10        1000        4000
B        10        2000        4000
E        10        1000        4000
C        20        1500        4500
D        20        3000        4500

用goup by 就沒辦法做到這點(diǎn)，只能查詢到每個(gè)部門的總工資：
select dept,sum(salary) total_salary from salary group by dept
dept        total_salary
10        4000
20        4500