第一部分關(guān)系型數(shù)據(jù)庫和SQL

第1章何謂關(guān)系型數(shù)據(jù)庫.......................................2

1.1數(shù)據(jù)庫類型..........................................................2

1.2關(guān)系模型簡(jiǎn)史......................................................2

1.2.1起源..........................................................3

1.2.2關(guān)系型數(shù)據(jù)庫系統(tǒng)...................................3

1.3關(guān)系型數(shù)據(jù)庫剖析...............................................4

1.3.1表..............................................................4

1.3.2列..............................................................5

1.3.3行..............................................................5

1.3.4鍵..............................................................5

1.3.5視圖..........................................................6

1.3.6關(guān)系..........................................................6

1.4學(xué)習(xí)關(guān)系型數(shù)據(jù)庫有何好處...............................9

1.5小結(jié)...................................................................10

第2章確保數(shù)據(jù)庫結(jié)構(gòu)合理.................................11

2.1為何在本書開頭討論數(shù)據(jù)庫設(shè)計(jì)......................11

2.2為何要關(guān)心數(shù)據(jù)庫結(jié)構(gòu)是否合理......................11

2.3微調(diào)列................................................................12

2.3.1列名的組成............................................12

2.3.2消除不完善的地方.................................13

2.3.3分解多部分列.........................................14

2.3.4分解多值列............................................15

2.4微調(diào)表................................................................17

2.4.1表名的組成............................................17

2.4.2確保結(jié)構(gòu)合理.........................................18

2.4.3消除多余的重復(fù)列.................................18

2.4.4標(biāo)識(shí)是關(guān)鍵............................................23

2.5建立合理的關(guān)系................................................25

2.5.1指定刪除規(guī)則.........................................26

2.5.2指定參與類型.........................................27

2.5.3指定參與程度.........................................28

2.6就這些嗎............................................................29

2.7小結(jié)...................................................................29

第3章SQL簡(jiǎn)史....................................................31

3.1SQL的起源.......................................................31

3.2早期的廠商實(shí)現(xiàn)................................................32

3.3標(biāo)準(zhǔn)應(yīng)運(yùn)而生...................................................32

3.4ANSI/ISO標(biāo)準(zhǔn)的發(fā)展歷程..............................33

3.5商用實(shí)現(xiàn)...........................................................36

3.6展望未來...........................................................36

3.7為何要學(xué)習(xí)SQL...............................................36

3.8本書基于哪個(gè)SQL版本...................................37

3.9小結(jié)...................................................................37

第二部分SQL基礎(chǔ)

第4章創(chuàng)建簡(jiǎn)單查詢.............................................40

4.1SELECT簡(jiǎn)介....................................................40

4.2SELECT語句....................................................41

4.3說點(diǎn)題外話：數(shù)據(jù)和信息................................42

4.4將請(qǐng)求轉(zhuǎn)換為SQL...........................................43

4.4.1更廣闊的視野........................................45

4.4.2使用簡(jiǎn)寫請(qǐng)求所有列............................46

4.5消除重復(fù)行.......................................................47

4.6對(duì)信息進(jìn)行排序................................................48

4.6.1重要的事先說：排序序列.....................49

4.6.2排序.......................................................49

4.7保存所做的工作................................................51

4.8語句舉例...........................................................51

4.9小結(jié)...................................................................57

4.10練習(xí).................................................................57

第5章獲取除簡(jiǎn)單列外的其他信息...................59

5.1何謂表達(dá)式.......................................................59

5.2你要表示哪些類型的數(shù)據(jù)................................60

5.3修改數(shù)據(jù)類型：CAST函數(shù).............................61

5.4指定顯式值.......................................................62

5.4.1字符串字面量........................................62

5.4.2數(shù)值字面量............................................63

5.4.3日期時(shí)間字面量....................................63xvi目錄

5.5表達(dá)式類型........................................................65

5.5.1拼接表達(dá)式............................................65

5.5.2數(shù)學(xué)表達(dá)式............................................66

5.5.3日期和時(shí)間算術(shù)表達(dá)式.........................68

5.6在SELECT子句中使用表達(dá)式.........................71

5.6.1使用拼接表達(dá)式.....................................71

5.6.2給表達(dá)式命名........................................72

5.6.3使用數(shù)學(xué)表達(dá)式.....................................73

5.6.4使用日期表達(dá)式.....................................73

5.6.5說點(diǎn)題外話：值表達(dá)式.........................74

5.7空值：Null........................................................75

5.7.1Null簡(jiǎn)介................................................76

5.7.2Null帶來的問題....................................76

5.8語句舉例............................................................77

5.9小結(jié)...................................................................81

5.10練習(xí).................................................................82

第6章篩選數(shù)據(jù)......................................................84

6.1使用WHERE提煉信息.....................................84

6.1.1WHERE子句.........................................85

6.1.2使用WHERE子句.................................86

6.2定義查找條件....................................................87

6.2.1比較........................................................87

6.2.2范圍........................................................91

6.2.3集成員資格............................................92

6.2.4模式匹配................................................93

6.2.5Null........................................................95

6.2.6使用NOT排除行...................................96

6.3使用多個(gè)條件....................................................98

6.3.1AND和OR簡(jiǎn)介....................................98

6.3.2再談排除行..........................................102

6.3.3優(yōu)先級(jí)..................................................103

6.3.4檢查兩個(gè)范圍是否重疊.......................105

6.4再談Null：一個(gè)注意事項(xiàng)...............................106

6.5以不同的方式表示條件...................................109

6.6語句舉例..........................................................109

6.7小結(jié).................................................................114

6.8練習(xí).................................................................115

第三部分使用多個(gè)表

第7章集合思維....................................................118

7.1何謂集合..........................................................118

7.2集合運(yùn)算..........................................................119

7.2.1交集......................................................119

7.2.2差集......................................................122

7.2.3并集......................................................126

7.3SQL集合運(yùn)算.................................................128

7.3.1經(jīng)典集合運(yùn)算和SQL...........................128

7.3.2查找都有的值：INTERSECT..............128

7.3.3查找沒有的值：EXCEPT（差集）.....130

7.3.4合并集合：UNION..............................132

7.4小結(jié).................................................................133

第8章內(nèi)連接........................................................135

8.1何謂連接..........................................................135

8.2內(nèi)連接..............................................................135

8.2.1可基于什么進(jìn)行連接...........................136

8.2.2列引用..................................................136

8.2.3語法......................................................136

8.2.4檢查表間關(guān)系.......................................144

8.3內(nèi)連接的用途..................................................145

8.3.1查找相關(guān)的行.......................................145

8.3.2查找匹配的值.......................................145

8.4語句舉例..........................................................146

8.4.1兩個(gè)表..................................................146

8.4.2超過兩個(gè)表..........................................149

8.4.3查找匹配的值.......................................153

8.5小結(jié).................................................................158

8.6練習(xí).................................................................158

第9章外連接........................................................161

9.1何謂外連接......................................................161

9.2左/右外連接.....................................................162

9.3全外連接..........................................................173

9.3.1語法......................................................173

9.3.2基于非鍵值的全外連接.......................174

9.3.3UNIONJOIN........................................175

9.4外連接的用途..................................................175

9.4.1查找缺失值..........................................175

9.4.2查找部分匹配信息...............................175

9.5語句舉例..........................................................176

9.6小結(jié).................................................................184

9.7練習(xí).................................................................184

第10章UNION.....................................................186

10.1何謂UNION..................................................186

10.2使用UNION編寫查詢..................................187

10.2.1使用簡(jiǎn)單的SELECT語句...............188

10.2.2合并復(fù)雜的SELECT語句...............189

10.2.3多次使用UNION.............................191

10.2.4對(duì)UNION的結(jié)果集進(jìn)行排序.........193

10.3UNION的用途..............................................194

10.4語句舉例........................................................194

10.5小結(jié)...............................................................200

10.6練習(xí)...............................................................200目錄xvii

第11章子查詢......................................................202

11.1何謂子查詢....................................................202

11.1.1行子查詢..........................................203

11.1.2表子查詢..........................................203

11.1.3標(biāo)量子查詢.......................................203

11.2作為列表達(dá)式的子查詢.................................203

11.2.1語法..................................................204

11.2.2聚合函數(shù)COUNT和MAX簡(jiǎn)介.....205

11.3作為篩選器的子查詢.....................................207

11.3.1語法..................................................207

11.3.2可與子查詢一起使用的特殊謂詞

關(guān)鍵字..............................................208

11.4子查詢的用途................................................214

11.4.1將子查詢用作列表達(dá)式...................214

11.4.2將子查詢用作篩選器.......................215

11.5語句舉例........................................................215

11.5.1表達(dá)式中的子查詢...........................215

11.5.2篩選器中的子查詢...........................219

11.6小結(jié)...............................................................222

11.7練習(xí)...............................................................223

第四部分?jǐn)?shù)據(jù)匯總和分組

第12章簡(jiǎn)單匯總..................................................226

12.1聚合函數(shù)........................................................226

12.1.1使用COUNT計(jì)算行或值的個(gè)數(shù).....227

12.1.2使用SUM計(jì)算總計(jì).........................229

12.1.3使用AVG計(jì)算平均值.....................230

12.1.4使用MAX找出最大值....................231

12.1.5使用MIN找出最小值......................232

12.1.6使用多個(gè)函數(shù)...................................232

12.2在篩選器中使用聚合函數(shù).............................233

12.3語句舉例........................................................234

12.4小結(jié)...............................................................238

12.5練習(xí)...............................................................238

第13章數(shù)據(jù)分組..................................................240

13.1為何要將數(shù)據(jù)分組.........................................240

13.2GROUPBY子句...........................................242

13.2.1語法..................................................242

13.2.2混合使用列和表達(dá)式.......................245

13.2.3在WHERE子句中的子查詢中

使用GROUPBY..............................246

13.2.4模擬SELECTDISTINCT語句........247

13.3一些限制........................................................247

13.3.1列方面的限制...................................247

13.3.2根據(jù)表達(dá)式分組...............................248

13.4GROUPBY的用途.......................................249

13.5語句舉例.......................................................250

13.6小結(jié)...............................................................255

13.7練習(xí)...............................................................255

第14章篩選分組數(shù)據(jù)........................................257

14.1一種新的篩選方式........................................257

14.2在哪里篩選更好............................................259

14.2.1在WHERE還是HAVING子句中

篩選.................................................259

14.2.2避開HAVINGCOUNT陷阱..........261

14.3HAVING的用途...........................................263

14.4語句舉例.......................................................264

14.5小結(jié)...............................................................268

14.6練習(xí)...............................................................269

第五部分修改數(shù)據(jù)集

第15章更新數(shù)據(jù)集.............................................272

15.1何謂更新.......................................................272

15.2UPDATE語句...............................................272

15.2.1使用簡(jiǎn)單的UPDATE表達(dá)式.........273

15.2.2說點(diǎn)題外話：事務(wù)..........................275

15.2.3更新多列..........................................275

15.2.4使用子查詢篩選行..........................276

15.3有些數(shù)據(jù)庫系統(tǒng)允許在UPDATE子句中

使用連接.......................................................278

15.4UPDATE的用途...........................................280

15.5語句舉例.......................................................281

15.6小結(jié)...............................................................289

15.7練習(xí)...............................................................290

第16章插入數(shù)據(jù)集.............................................291

16.1何謂插入.......................................................291

16.2INSERT語句................................................292

16.2.1插入值.............................................292

16.2.2生成下一個(gè)主鍵值..........................294

16.2.3使用SELECT插入數(shù)據(jù)..................295

16.3INSERT的用途.............................................299

16.4語句舉例.......................................................299

16.5小結(jié)...............................................................305

16.6練習(xí)...............................................................305

第17章刪除數(shù)據(jù)集.............................................307

17.1何謂刪除.......................................................307

17.2DELETE語句...............................................307

17.2.1刪除所有行......................................308xviii目錄

17.2.2刪除某些行......................................309

17.3DELETE的用途............................................311

17.4語句舉例........................................................312

17.5小結(jié)...............................................................317

17.6練習(xí)...............................................................317

第六部分解決棘手問題

第18章否定型問題和多條件型問題...............320

18.1簡(jiǎn)單地復(fù)習(xí)集合............................................320

18.1.1滿足多個(gè)條件的集合.......................320

18.1.2不滿足多個(gè)條件的集合...................321

18.1.3滿足一些條件同時(shí)不滿足另一些

條件的集合.......................................322

18.2解決否定型問題............................................323

18.2.1使用外連接......................................323

18.2.2使用NOTIN....................................324

18.2.3使用NOTEXISTS...........................325

18.2.4使用GROUPBY/HAVING.............326

18.3根據(jù)多個(gè)肯定條件查找.................................327

18.3.1使用內(nèi)連接......................................328

18.3.2使用IN.............................................329

18.3.3使用EXISTS....................................330

18.3.4使用GROUPBY/HAVING.............331

18.4語句舉例........................................................333

18.5小結(jié)...............................................................343

18.6練習(xí)...............................................................343

第19章條件測(cè)試.................................................346

19.1條件表達(dá)式（CASE）..................................346

19.1.1為何要使用CASE...........................346

19.1.2語法..................................................347

19.2使用CASE解決問題....................................349

19.2.1使用簡(jiǎn)單型CASE解決問題...........349

19.2.2使用查找型CASE解決問題...........352

19.2.3在WHERE子句中使用CASE........353

19.3語句舉例........................................................354

19.4小結(jié)...............................................................361

19.5練習(xí)...............................................................361

第20章使用非連接數(shù)據(jù)和“驅(qū)動(dòng)”表...........363

20.1何謂非連接數(shù)據(jù)............................................363

20.2使用非連接數(shù)據(jù)解決問題.............................366

20.3使用“驅(qū)動(dòng)”表解決問題.............................367

20.3.1建立驅(qū)動(dòng)表......................................367

20.3.2使用驅(qū)動(dòng)表......................................369

20.4語句舉例........................................................371

20.4.1非連接表使用示例...........................372

20.4.2驅(qū)動(dòng)表使用示例...............................377

20.5小結(jié)...............................................................381

20.6練習(xí)...............................................................382

第21章執(zhí)行復(fù)雜的分組計(jì)算............................384

21.1不同分組的合計(jì)............................................384

21.2擴(kuò)展GROUPBY子句...................................386

21.3使用ROLLUP獲取分層合計(jì)........................387

21.4使用CUBE計(jì)算各種組合的匯總.................393

21.5使用GROUPINGSETS合并匯總.................396

21.6分組技術(shù)變種................................................398

21.7語句舉例........................................................400

21.7.1ROLLUP使用示例...........................401

21.7.2CUBE使用示例...............................402

21.7.3GROUPINGSETS使用示例............404

21.8小結(jié)...............................................................405

21.9練習(xí)...............................................................405

第22章將數(shù)據(jù)劃分到窗口中............................407

22.1將數(shù)據(jù)劃分到窗口中有何用.........................407

22.2計(jì)算行號(hào)........................................................419

22.3數(shù)據(jù)排名........................................................420

22.4將數(shù)據(jù)劃分到五分位區(qū)間中.........................424

22.5結(jié)合使用窗口和聚合函數(shù).............................426

22.6語句舉例........................................................429

22.6.1ROW_NUMBER使用示例..............430

22.6.2RANK、DENSE_RANK和

PERCENT_RANK使用示例............432

22.6.3NTILE使用示例..............................434

22.6.4聚合函數(shù)使用示例...........................435

22.7小結(jié)...............................................................439

22.8練習(xí)...............................................................439

附錄ASQL標(biāo)準(zhǔn)語法圖.......................................441

附錄B示例數(shù)據(jù)庫的結(jié)構(gòu)...................................453

附錄C與日期和時(shí)間相關(guān)的數(shù)據(jù)類型、

運(yùn)算和函數(shù)................................................460

附錄D推薦讀物....................................................469

結(jié)語.............................................................................470

資源下載

隱藏內(nèi)容，微信掃碼關(guān)注,免費(fèi)獲取密碼。

提交

請(qǐng)關(guān)注【51教學(xué)網(wǎng)】官方微信公眾號(hào)，回復(fù)“密碼”，獲取密碼。