《SQL必知必會》萬字精華

《SQL必知必會》萬字精華！數(shù)據(jù)庫基礎知識庫表的相關操作檢索數(shù)據(jù)的方法等思維導圖下面的思維導圖中記錄了這本書的整體目錄結構，包含內(nèi)容有：數(shù)據(jù)的檢索匯總數(shù)據(jù)分組數(shù)據(jù)…….個人增加了一章：Python操作游標一、了解SQL本章中主要是介紹了數(shù)據(jù)庫和SQL相關的基本知識和術語。數(shù)據(jù)庫數(shù)據(jù)庫是一個以某種有組織的方式存儲的數(shù)據(jù)集合。數(shù)據(jù)庫是一個保存有組織的數(shù)據(jù)容器，通常是一個文件或者一組文件表表示一種結構化的文件，可以用來存儲某種特定的數(shù)據(jù)類型。表是某種特定類型數(shù)據(jù)的結構化清單。存儲在表中的數(shù)據(jù)是同一種類型的數(shù)據(jù)或者清單數(shù)據(jù)庫中的每個表都有自己的名字，并且這個表是唯一的。列表是由列組成的，列存儲表中某部分的信息。列是表中的某個字段。所有的表都是由一個或者多個列組成的。數(shù)據(jù)庫中的每個列都應該是具有的相同數(shù)據(jù)類型datatype。數(shù)據(jù)類型定義了列可以存儲哪些數(shù)據(jù)類型。行表中的數(shù)據(jù)是按照行來進行存儲的，所保存的每個記錄存儲在自己的行內(nèi)。如果把表想象成一個網(wǎng)格，那么網(wǎng)格中垂直的列則為表列，水平則為表行。行表示的是一個記錄。行有時候也稱之為記錄。主鍵表中每一行都應該都有一列或者幾列來唯一標識自己。主鍵用來表示一個特定的行。主鍵：一列或者幾列，其值能夠標識表中每行。如果表中的列可以作為主鍵，則它必須滿足：任意兩行都不具有相同的主鍵值（主鍵列不允許NULL值）每行都必須有一個主鍵值主鍵列中的值不允許修改或者更新主鍵值不能重用（如果某行從表中刪除，則它的主鍵不能賦給以后的行記錄）什么是SQL首先我們看一段來自百度百度的解釋：結構化查詢語言(StructuredQueryLanguage)簡稱SQL，是一種特殊目的的編程語言，是一種數(shù)據(jù)庫查詢和程序設計語言，用于存取數(shù)據(jù)以及查詢、更新和管理關系數(shù)據(jù)庫系統(tǒng)。SQL是一種專門和數(shù)據(jù)庫溝通的語言SQL特點1、SQL不是某個特定數(shù)據(jù)庫供應商專有的語言，幾乎所有的DBMS都是支持SQL2、SQL簡單易學。它的語句都是由簡單的、具有描述性的英文單詞組成的3、SQL雖然簡單，但是實際上是一種很強有力的語言，靈活使用去語言元素，可以進行復雜和高級的數(shù)據(jù)庫操作二、檢索數(shù)據(jù)本章中介紹的是如何使用select語句從表中檢索一個或者多個數(shù)據(jù)列。每個SQL語句多有一個或者多個關鍵字組成，最經(jīng)常使用的就是select關鍵字。它的用途是從一個或者多個表中檢索出來信息。為了使用select檢索數(shù)據(jù)，必須至少給出兩個信息：檢索什么（字段，列）從哪里檢索（表）檢索單個列SELECT

prod_name

--

檢索什么

FROM

Products;

--

從哪里檢索下面關于SQL的語句做幾點筆記：多條SQL語句必須是以分號;結尾；如果是單條不加分號也是可以的，但是最好加上SQL語句不區(qū)分大小寫，即：SELECT和select是相同的。但是一般規(guī)范的寫法是：SQL關鍵字進行大寫，列名和表名是小寫的在處理SQL語句的時候，其中所有的空格都是忽略的；但是分行寫，語句更清晰，更好理解--

寫法1

SELECT

prod_name

--

檢索什么

FROM

Products;

--

從哪里檢索

--

寫法2

SELECT

prod_name

FROM

Products;

--

寫法3

SELECT

prod_name

FROM

Products;

--

寫法：個人習慣寫法

SELECT

prod_name

FROM

Products;

檢索多個列SELECT

pro_id,prod_name,prod_price

FROM

Products;

--

個人寫法

SELECT

pro_id,

prod_name,

prod_price

FROM

Products;檢索所有列SELECT

*

--

*代表所有列

FROM

Products;檢索不同的值SELECT

DISTINCT

vend_id

FROM

products;SQL中的DISTINCT關鍵字表示的是去重，只返回不同的值。它必須放在列的前面。不能部分使用DISTINCT關鍵字。它是作用于所有的列SELECT

DISTINCT

vend_id,

prod_price

--

DISTINCT作用于所有的列，并不僅僅是后面的列限制結果如果不加限制條件，SQL返回的是全部數(shù)據(jù)。如果我們想只顯示部分數(shù)據(jù)，該如何實現(xiàn)？1、SQLServer和AccessSELECT

TOP

5

prod_name

--

最多返回5行

FROM

Products;2、DB2SELECT

prod_name

FROM

Products

FETCH

FIRST

5

ROWS

ONLY;

--

字面意思：顯示前5行3、OracleSELECT

prod_name

FROM

Products

WHERE

ROWNUM

<=

5;4、MySQL、MariaDB、PostgreSQL、SQLite使用關鍵字limitSELECT

prod_name

FROM

Products

LIMIT

5;

--

使用LIMIT5關于LIMIT的筆記：SELECT

prod_name

FROM

Products

LIMIT

4

OFFSET

5;

--

第5行開始顯示4行數(shù)據(jù)

--

簡化版本

SELECT

prod_name

FROM

Products

LIMIT

5,4

--

效果同上第一個數(shù)字表示顯示多少行數(shù)據(jù)第二個數(shù)字表示從哪里開始顯示SQL注釋問題SQL中的注釋分為兩種：單行注釋和多行注釋單行注釋使用—符號，后面跟上注釋的內(nèi)容：SELECT

prod_name

--

這里是一條注釋，你可以寫點注釋

FROM

Products

LIMIT

4

OFFSET

5;

多行注釋使用一對/*，符號之間的內(nèi)容就是注釋：/*

注釋1：SQL語句的作用是什么

注釋2：SQL語句誰在什么時候寫的

*/

SELECT

prod_name

FROM

Products

LIMIT

4

OFFSET

5;

三、排序檢索數(shù)據(jù)排序數(shù)據(jù)（單個列）本節(jié)中介紹的是如何利用orderby子句來對select檢索的結果進行排序。為了明確地排序用select語句檢索出來的數(shù)據(jù)，可使用orderby子句取一個或者多個列的名字，來對輸出結果進行排序。使用關鍵詞orderby排序的結果默認是升序ASC，降序是DESCSELECT

prod_name

FROM

Products

ORDER

BY

prod_name;

--

根據(jù)產(chǎn)品名稱的字母進行排序筆記：我們需要注意orderby子句的位置，一定要保證它是select語句的最后一條子句。如果它不是最后的子句，那么就會報錯。按多個列排序在實際的需求中，我們經(jīng)常會遇到根據(jù)多個列進行排序。比如根據(jù)員工的姓名排序，如果姓相同，再根據(jù)名字進行排序。要按多個列進行排序，指定列名即可，列名之間使用逗號隔開。SELECT

prod_id,prod_price,prod_name

--

選擇3個列

FROM

Products

ORDER

BY

prod_price,prod_name;

--

先根據(jù)價格排序，如果有相同的價格再根據(jù)姓名排序筆記：只有當prod_price有相同的值，才會根據(jù)prod_name進行排序按列位置進行排序除了可以使用列名指出排序順序外，orderby還支持使用相對位置進行排序。SELECT

prod_id,

prod_price,

prod_name

--

選擇3個列

FROM

Products

ORDER

BY

2,3;

--

2,3就是相對位置2表示的是第2個列（prod_price）3表示的是第3個列名（prod_name）如果想在多個列上進行降序排列，則對每個列都要指定DESC關鍵詞：--

正確寫法

SELECT

prod_id,

prod_price,

prod_name

FROM

Products

ORDER

BY

prod_price

DESC,

prod_name

DESC;

--

每個列都指定DESC

--

錯誤寫法！??！

SELECT

prod_id,

prod_price,

prod_name

FROM

Products

ORDER

BY

prod_price,

prod_name

DESC;

--

DESC只對最近的prod_name起作用，那么prod_price仍然是升序排列的四、過濾數(shù)據(jù)本節(jié)中講解的是使用where關鍵詞來過濾數(shù)據(jù)。數(shù)據(jù)庫中一般存在大量的數(shù)據(jù)，一般我們只需要檢索表中少量的行。只檢索所需數(shù)據(jù)需要指定搜索條件，搜索條件也稱之為過濾條件。使用where子句SELECT

prod_name,

prod_price

FROM

Products

WHERE

prod_price=5;

--

指定條件筆記：當ORDERBY和WHERE子句同時存在的時候，ORDERBY子句應該位于WHERE子句之后。WHERE子句操作符常用的where子句操作符：操作符說明操作符說明=等于>大于<>不等于>=大于等于!=不等于!>不大于<小于BETWEEN…AND...在指定的兩個值之間>大于ISNULL為NULL值!<不小于注：上面表格中的某些操作符是等價的。檢查單個值SELECT

prod_name,

prod_price

FROM

Products

WHERE

prod_price

<=

5;

--

指定1個條件SELECT

vend_id,

prod_name,

prod_price

FROM

Products

WHERE

vend_id

<>

'DLL01'

--

指定不等于條件筆記：1、上面where子句中，可以看到有的只會在單引號內(nèi)，但是有的沒有括起來。2、單引號用來限定字符串。如果將值和字符串類型的比較，需要使用限定符號3、用來與數(shù)值列進行比較的值，則不用括號。不匹配檢查SELECT

vend_id,

prod_name,

prod_price

FROM

Products

WHERE

vend_id

<>

'DLL01'

--

不匹配檢查條件

范圍值檢查要檢查某個范圍的值，可以使用BETWEEN操作符。BETWEEN操作符要搭配AND同時使用，指定范圍的最大值和最小值：SELECT

vend_id,

prod_name,

prod_price

FROM

Products

WHERE

prod_price

BETWEEN

100

AND

600

--

BETWEEN

...

AND...聯(lián)合使用空值檢查當我們創(chuàng)建表的時候，可以指定其中的列是否包含空值。在一個列不包含值時，稱其包含空值NULL。注：NULL（無值，novalue），它和字段包含0、空字符串或僅僅包含空格是不同的。SELECT中一個特殊的WHERE子句用來檢查具有NULL值的列：SELECT

prod_name,

prod_price

FROM

Products

WHERE

prod_price

IS

NULL;

--

找出價格為NULL的數(shù)據(jù)五、高級數(shù)據(jù)過濾本節(jié)中介紹的是如何組合WHERE子句以建立功能更強、更高級的搜索條件組合WHERE子句操作符operator：用來聯(lián)結或改變WHERE子句中的子句的關鍵字，也稱之為邏輯操作符logicaloperator。AND操作符同時滿足AND操作符兩邊的條件SELECT

vend_id,

prod_name,

prod_price

FROM

Products

WHERE

vend_id

>=

'DLL01'

AND

prod_price

<=

20;

--

AND操作符指定2個條件

OR操作符滿足OR操作符兩邊的一個條件即可SELECT

vend_id,

prod_name,

prod_price

FROM

Products

WHERE

vend_id

>=

'DLL01'

OR

prod_price

<=

20;

--

AND操作符指定2個條件

注：當?shù)谝粋€條件滿足的時候，第二個條件便不會執(zhí)行了。AND和OR聯(lián)用在WHERE子句中同時使用AND和OR操作符：??：AND操作符的優(yōu)先級是高于OR操作符??：AND操作符的優(yōu)先級是高于OR操作符??：AND操作符的優(yōu)先級是高于OR操作符SELECT

prod_name,prod_price

FROM

Products

WHERE

vend_id

=

'DLL01'

OR

vend_id

=

'BRS01'

AND

prod_price

>=

10;上面的語句原本表達的含義是先挑選滿足兩個vend_id的條件；但是SQL在執(zhí)行的時候，AND操作符先處理。如果我們想先執(zhí)行AND前面的部分，可以加上括號：SELECT

prod_name,prod_price

FROM

Products

WHERE

(vend_id

=

'DLL01'

OR

vend_id

=

'BRS01')

AND

prod_price

>=

10;括號具有比AND或者OR更高的優(yōu)先級，能夠消除歧義。IN操作符IN操作符用來指定條件范圍，范圍中的每個條件都可以進行匹配。SELECT

prod_name,prod_price

FROM

Products

WHERE

vend_id

IN

('DLL01','BRS01')

--

功能類似于OR操作符

ORDER

BY

name：筆記：IN操作符的主要優(yōu)點1、IN操作符的語法更清楚、更直觀2、使用了IN操作符更容易管理求值順序3、IN操作符一般比OR操作符執(zhí)行的更快4、在IN操作符中可以包含其他SELECT子句，能夠動態(tài)地建立where子句NOT操作符NOT操作符只有一個功能：就是否定后面所跟的任何條件。SELECT

prod_name

FROM

Products

WHERE

NOT

vend_id

=

'DLL01'

--

找出不是DLLO1名字的數(shù)據(jù)

ORDER

BY

prod_name上面的語句的功能也可以用<>來實現(xiàn)：SELECT

prod_name

FROM

Products

WHERE

vend_id

<>

'DLLO1'

--

不等于，效果同上

ORDER

BY

prod_name六、使用通配符進行過濾什么是通配符通配符wildcard是用來匹配值的一部分的特殊字符；利用通配符，可以創(chuàng)建比較特定數(shù)據(jù)的搜索模式。筆記：由字面值、通配符或者兩者組合構成的搜索條件。為了在搜索子句中使用通配符，必須使用LIKE操作符??通配符搜索只能用于文本字段（字符串），對于非文本數(shù)據(jù)類型不能使用通配符搜索。百分號%功能：匹配任意字符出現(xiàn)的任意次數(shù)，即任意內(nèi)容SELECT

prod_id,

prod_name

FROM

Products

WHERE

prod_name

LIKE

"Fish%"

--

找出所有Fish開頭的產(chǎn)品，不管后面是什么內(nèi)容通配符可以在任意位置使用，可以使用多次：SELECT

prod_id,

prod_name

FROM

Products

WHERE

prod_name

LIKE

"%bean

bag%"

--

匹配名字中包含bean

bag的任意名字的數(shù)據(jù)，不管前后通配符出現(xiàn)在中間：SELECT

prod_name

FROM

Products

WHERE

prod_name

LIKE

'F%y'

--

找出F開頭y結尾的數(shù)據(jù)??：百分號%能夠匹配任意位置的0個、1個或者多個字符，但是不能匹配NULL下劃線_下劃線通配符的用途和百分號類似，但是它只能匹配一個字符，百分號是匹配多個字符，這是二者的區(qū)別。SELECT

prod_id,

prod_name

FROM

Products

WHERE

prod_name

LIKE

'__

inch

teddy

bear'

--

前面是兩個下劃線_

--

結果

prod_id

prod_name

-------

---------

BR02

12

inch

teddy

bear

--

12或者18剛好是兩個字符

BR03

18

inch

teddy

bear一個下劃線剛好只能匹配一個字符，不能多也不能少方括號[]方括號[]通配符用來指定一個字符集，它必須匹配指定位置的一個字符。SELECT

cust_contact

FROM

Customers

WHERE

cust_contact

LIKE

'[JM]%'

--

匹配JM當中一個字母開頭，后面是任意字符的內(nèi)容

ORDER

BY

cust_contact[JM]：匹配其中一個字符%：匹配任意內(nèi)容取反字符^使用取反符號^來否定內(nèi)容：SELECT

cust_contact

FROM

Customers

WHERE

cust_contact

LIKE

'[^JM]%'

--

匹配不是JM開頭的任意內(nèi)容

ORDER

BY

cust_contact;使用NOT操作符可以得到類似上面的結果：SELECT

cust_contact

FROM

Customers

WHERE

NOT

cust_contact

LIKE

'[JM]%'

--

匹配不是JM開頭的任意內(nèi)容；NOT表示取反，否定內(nèi)容

ORDER

BY

cust_contact;通配符使用技巧??通配符使用的幾點注意事項：不要過度使用通配符如果確實需要使用通配符，盡可能不要將它們放在搜索模式的開始位置。通配符處于開始處，搜索是最慢的。仔細注意通配符的位置。如果放錯地方，可能得不到我們想要的結果。通配符應該要細心使用，不要過度使用。七、創(chuàng)建計算字段計算字段存儲在數(shù)據(jù)庫表中的字段一般不是應用程序中所需要的格式。我們需要直接從數(shù)據(jù)庫中檢索出來進行轉換、計算或者格式化過的數(shù)據(jù)。計算字段并不實際存在于數(shù)據(jù)庫表中。計算字段是運行時在select語句內(nèi)創(chuàng)建的。拼接字段將多個字段聯(lián)結在一起構成單個字段。根據(jù)不同的DBMS，使用+或者||來進行聯(lián)結。但是在MySQL中必須使用特殊的函數(shù)來進行操作。拼接字段函數(shù)Concat的使用：SELECT

Concat(vend_name,

'

(',

vend_country,

')')

FROM

Vendors

ORDER

BY

vend_name;代碼解釋：存在列vend_name列中的名字包含一個空格和一個左圓括號的字符串存在vend_country列中的國家包含一個右圓括號的字符串小知識：MySQL中如何去掉空格？RTRIM(col)

--

去掉值右邊的所有空格

LTRIM(col)

--

去掉值左邊的所有空格

TTRIM(col)

--

去掉字符串左右兩邊的所有空格使用別名別名（alias）是一個字段或者值的替換明，別名是使用關鍵詞AS來賦予的。SELECT

Concat(vend_name,

'

(',

vend_country,

')')

AS

vend_title

--

使用別名

FROM

Vendors

ORDER

BY

vend_name;筆記：AS關鍵詞語是可選的，但是最好使用執(zhí)行算術運算計算字段的另一個常見運算是對檢索出來的數(shù)據(jù)進行算術運算。SELECT

prod_id

,quantity

,item_price

,quantity

*

item_price

AS

expanded_price

--

計算字段執(zhí)行算術運算，并使用別名

FROM

OrderItems

WHERE

order_num

=

2008;SQL算術操作符SQL中支持的算術操作符：操作符說明+加-減*乘/除八、使用函數(shù)處理數(shù)據(jù)常用函數(shù)與其他計算機語言一樣，SQL中也提供函數(shù)來處理數(shù)據(jù)。用于處理文本字符串：刪除或填充值、轉換值或者大小寫轉化用于在數(shù)值數(shù)據(jù)上進行算術操作：返回絕對值、代數(shù)運算等用于處理日期和時間，并從中提取出特定成分的日期和時間函數(shù)等返回DBMS正使用的特殊信息的系統(tǒng)函數(shù)文本處理函數(shù)函數(shù)說明LEFT()返回字符串左邊的字符LENGTH()返回字符串的長度LOWER()將字符串轉換為小寫LTRIM()去掉值左邊的所有空格RIGHT()返回字符串右邊的字符RTRIM()去掉值右邊的所有空格SOUNDEX()返回字符串的SOUNDEX值UPPER()將字符串轉換為大寫SOUNDE(X)是一個將任何文本串轉成描述其語音表示的字母數(shù)字模式的算法。SELECT

cust_name

,cust_contact

FROM

Customers

WHERE

SOUNDEX(cust_contact)

=

SOUNDEX('Michael

Green')

--

轉化成對應的值日期和時間處理函數(shù)日期和時間采用相應的數(shù)據(jù)類型存儲在表中，以特殊的格式來存儲。SELECT

order_num

FROM

Orders

WHERE

YEAR(order_date)

=

2012;

--

提取年份數(shù)值處理函數(shù)MySQL中常用的數(shù)值處理函數(shù)：函數(shù)說明ABS()返回一個數(shù)的絕對值COS()返回一個角度的余弦值EXP()返回一個數(shù)的指數(shù)值PI()返回圓周率SIN()返回一個角度的正弦SQRT()返回一個數(shù)的平方根TAN()返回一個角度的正切值九、匯總數(shù)據(jù)聚集函數(shù)聚集函數(shù)指的是對某些行運行的一個函數(shù)，并且返回一個值，常用的聚集函數(shù)有：函數(shù)作用AVG()返回列的平均值COUNT()返回列的函數(shù)MAX()返回列的最大值MIN()返回列的最小值SUM()返回某列值之和1、AVG()函數(shù)SELECT

AVG(prod_price)

AS

avg_price

--

求平均值

FROM

Products;上面求解的是所有行各自的平均值，也可以指定某個特定的行來求解：SELECT

AVG(prod_price)

AS

avg_price

--

求平均值

FROM

Products

WHERE

vend_id

=

'DLLO1';

--

指定特定的行筆記：AVG()函數(shù)會忽略掉值NULL的行2、COUNT()函數(shù)COUNT()函數(shù)進行計數(shù)，可以使用它來確定表中的函數(shù)或者符合特定條件的行的數(shù)目，兩種使用情況：count(*)：不管是空值（NULL）還是非空值，都會統(tǒng)計進去count(column)：對特定的列進行計數(shù)，會忽略表該列的NULL值SELECT

COUNT(*)

AS

num_cust

FROM

Customers;

num_cust

--------

5

SELECT

COUNT(cust_email)

AS

num_cust

FROM

Customers;

num_cust

--------

3筆記：如果指定列名，則COUNT()函數(shù)會忽略指定列的值為空的行，但是如果COUNT()函數(shù)使用的是星號，則不會忽略3、MAX()/MIN()函數(shù)返回指定列中的最大值或者最小值SELECT

MAX(prod_price)

AS

MAX_price

--

求最大值

SELECT

MAX(prod_price)

AS

MIN_price

--

求最小值

FROM

Products;筆記：上面的兩個最值函數(shù)會自動忽略掉值為NULL的行4、SUM()函數(shù)返回指定列值的和（總計）SELECT

SUM(quantity)

AS

items_ordered

FROM

OrderItems

WHERE

order_num

=

20005;SUM()函數(shù)也可以用來合計計算值：SELECT

SUM(item_price

*

quantity)

AS

total_price

--

返回所有物品的價錢之和

FROM

OrderItems

WHERE

order_num

=

20005;筆記：SUM()函數(shù)會自動忽略值為NULL的行聚集不同值上面的5個聚集函數(shù)都可以如下使用：對所有的行執(zhí)行計算，指定ALL參數(shù)或不指定參數(shù)（因為ALL是默認行為）只包含不同的值，指定DISTINCT參數(shù)，表示去重之后再進行計算筆記：ALL參數(shù)不需要指定，是默認行為SELECT

AVG(DISTINCT

prod_price)

AS

avg_price

--

去重之后再求平均值

FROM

Products

WHERE

vend_id

=

'DLLO1';

--

指定特定的行筆記：1、DISTINCT不能用于COUNT(*)；如果指定列名，則DISTINCT只能用于COUNT()2、DISTINCT必須使用列名，不能用于計算或者表達式3、DISTINCT用于MAX()和MIN()意義不大，因為最值不管是否考慮去重，都是一樣的組合聚集函數(shù)在SELECT子句中是可以包含多個聚集函數(shù)SELECT

AVG(prod_price)

AS

avg_price

--

求平均值

,MAX(prod_price)

AS

max_price

--

求最大值

,MIN(prod_price)

AS

min_price

--

求最小值

,COUNT(*)

AS

num_items

--

物品的數(shù)目

FROM

Products;十、分組數(shù)據(jù)分組使用的是兩個子句：GROUPBY()HAVING()創(chuàng)建分組分組是使用SELECT子句的GROUPBY子句建立的，看個例子：SELECT

vend_id

,COUNT(*)

AS

num_prods

FROM

Products

GROUP

BY

vend_id;

--

分組的列GROUPBY子句使用時候的常見規(guī)定：GROUPBY子句可以包含任意數(shù)目的列，可以對分組進行嵌套GROUPBY子句中列出的每一列都是檢索列或者有效的表達式（但是不能是聚集函數(shù)）如果在SELECT中使用表達式，則必須在GROUPBY子句中使用相同的表達式，而不是使用別名除了聚集函數(shù)外，SELECT語句中的每列都必須在GROUPBY子句中列出如果分組中包含具有NULL的行，則NULL將作為一個分組返回；如果列中出現(xiàn)多個NULL，它們將分成一個組GROUPBY子句必須在WHERE子句之后，ORDERBY子句之前GROUPBY子句中可以使用相對位置：GROUPBY2,1表示先根據(jù)第二個列分組，再根據(jù)第一個列分組過濾分組在WHERE子句中指定過濾的是行而不是分組；實際上WHERE種并沒有分組的概念。在SQL使用HAVING來實現(xiàn)過濾分組；筆記：WHERE過濾行，HAVING過濾分組SELECT

cust_id

,COUNT(*)

AS

orders

FROM

Orders

GROUP

BY

cust_id

HAVING

COUNT(*)

>=

2;

--

過濾分組WHERE和HAVING的區(qū)別：WHERE在數(shù)據(jù)過濾前分組，排除的行不在分組統(tǒng)計中HAVING在數(shù)據(jù)分組后進行過濾SELECT

vend_id

,COUNT(*)

AS

num_prods

FROM

Products

WHERE

prod_price

>=

4

--

分組前先執(zhí)行，找出符合條件的數(shù)據(jù)

GROUP

BY

vend_id

HAVING

COUNT(*)

>=

2;

--

分組后再執(zhí)行，找出數(shù)目大于2的數(shù)據(jù)分組和排序ORDERBY和GROUPBY的差異：ORDERBYGROUPBY對產(chǎn)生的輸出排序對行分組，但輸出可能不是分組的順序任意列都可以使用（非選擇的列也可以使用）只可能使用選擇列或者表達式列，而且必須使用每個選擇列表達式不一定需要如果和聚集函數(shù)一起使用列，則必須使用SELECT

order_num

,COUNT(*)

AS

items

FROM

OrderItems

GROUP

BY

order_num

HAVING

COUNT(*)

>=

3

ORDER

BY

items,

order_num;

--

先分組再過濾，最后排序輸出SELECT子句順序在這里總結一下SELECT子句的相關順序：子句說明是否必須使用SELECT要返回的列或者表達式是FROM從中檢索數(shù)據(jù)的表僅在從表選擇數(shù)據(jù)時使用WHERE行級過濾否GROUPBY分組說明僅在按照組計算聚集時使用HAVING組級過濾否ORDERBY輸出排序順序否十一、使用子查詢?nèi)魏蜸ELECT語句都是查詢，SQL還允許在查詢中嵌套查詢。SELECT

cust_id

--

再根據(jù)子查詢中的order_num找出符合要求的cust_id

FROM

Orders

WHERE

order_num

IN

(SELECT

order_num

--

先根據(jù)WHERE條件找出滿足符合要求的order_num

FROM

OrderItems

WHERE

prod_id

=

'RGAN01');筆記：子查詢總是從內(nèi)向外處理SELECT

Customers

--

最后根據(jù)找出的cust_id查詢Customers

FROM

cust_id

IN(SELECT

cust_id

--

再根據(jù)子查詢中的order_num找出符合要求的cust_id

FROM

Orders

WHERE

order_num

IN

(SELECT

order_num

--

先根據(jù)WHERE條件找出滿足符合要求的order_num

FROM

OrderItems

WHERE

prod_id

=

'RGAN01'));作為計算字段使用子查詢使用子查詢的另一個方法是創(chuàng)建計算字段SELECT

cust_name

,cust_state

,(SELECT

COUNT(*)

--

將子查詢作為一個計算字段輸出：統(tǒng)計每個cust_id的數(shù)量

FROM

Orders

WHERE

Orders.cust_id

=

Customers.cust_id)

AS

orders

--

Orders.cust_id

=

Customers.cust_id

使用完全限定列名來避免歧義

FROM

Customers

ORDER

BY

cust_name;十二、聯(lián)結表SQL最強大的功能就是數(shù)據(jù)查詢的過程中使用聯(lián)結表（join）。創(chuàng)建聯(lián)結通過指定要聯(lián)結的表和它們的聯(lián)結方式即可創(chuàng)建聯(lián)結。SELECT

vend_name,

prod_name,

prod_price

FROM

Vendors,

Products

WHERE

Vendors.vend_id

=

Products.vend_id;

--

指定聯(lián)結條件如果上面的代碼中沒有WHERE子句來指定聯(lián)結條件，則返回的是笛卡爾積，返回出來數(shù)的行就是第一個表中的行乘以第二個表中的行。筆記：返回笛卡爾積的聯(lián)結，也稱做叉聯(lián)結crossjoin內(nèi)聯(lián)結innerjoin使用最廣泛的聯(lián)結是等值聯(lián)結，也稱之為內(nèi)聯(lián)結innerjoin。實現(xiàn)上面語句的內(nèi)聯(lián)結代碼：SELECT

vend_name,

prod_name,

prod_price

FROM

Vendors

INNER

JOIN

Products

--

內(nèi)聯(lián)結

ON

Vendors.vend_id

=

Products.vend_id;

--

指定聯(lián)結條件聯(lián)結多個表SELECT

vend_name,

prod_name,

prod_price

FROM

OrderItems,

Products,

Vendors

WHERE

Products.vend_id

=

Vendors.vend_id

--

多個表的聯(lián)結

AND

OrderId_id

=

Pd_id

AND

order_num

=

20007;我們通過聯(lián)結方式來實現(xiàn)子查詢的結果：--

子查詢

SELECT

Customers

--

最后根據(jù)找出的cust_id查詢Customers

FROM

cust_id

IN(SELECT

cust_id

--

再根據(jù)子查詢中的order_num找出符合要求的cust_id

FROM

Orders

WHERE

order_num

IN

(SELECT

order_num

--

先根據(jù)WHERE條件找出滿足符合要求的order_num

FROM

OrderItems

WHERE

prod_id

=

'RGAN01'));

--

內(nèi)聯(lián)結

SELECT

cust_name,

cust_contact

FROM

Customers,

Orders,

OrderItems

WHERE

Customers.cust_id

=

Orders.cust_id

--

多個表聯(lián)結查詢

AND

OrderItems.order_num

=

Orders.order_num

AND

prod_id

=

'RGAN01'十三、創(chuàng)建高級聯(lián)結使用表別名在SQL語句中可以給表取別名：SELECT

cust_name,

cust_contact

FROM

Customers

AS

C,

Orders

AS

O,

OrderItems

AS

OI

--

取別名，看上去更簡潔

WHERE

C.cust_id

=

O.cust_id

--

多個表聯(lián)結查詢

AND

OI.order_num

=

O.order_num

AND

prod_id

=

'RGAN01'使用不同類型的聯(lián)結介紹3種不同的聯(lián)結：自聯(lián)結selfjoin自然聯(lián)結naturaljoin外聯(lián)結outerjoin1、自聯(lián)結selfjoin--

子查詢

SELECT

cust_id,cust_name,cust_contact

FROM

Customers

WHERE

cust_name

=

(SELECT

cust_name

FROM

Customers

WHERE

cust_contact

=

'Jim

Jones');

--

內(nèi)聯(lián)結

SELECT

c1.cust_id,

c2.cust_name,

c1.cust_contact

FROM

Customers

AS

c1,

Customers

AS

c2

--

相同的表使用兩次

WHERE

c1.cust_name

=

c2.cust_name

AND

c2.cust_contact

=

'Jim

Jones';上面使用了Customers表兩次，為了避免歧義，必須使用不同的別名加以區(qū)分。2、自然聯(lián)結無論何時對表進行聯(lián)結，應該至少有一列不止出現(xiàn)在一個表中（被聯(lián)結的列）。自然聯(lián)結排除多次出現(xiàn)，是每一列只返回一次。SELECT

C.*

,O.order_num

,O.order_date

,OI.prod_id

,OI.quantity

,OI.item_price

FROM

Customers

AS

C,

Orders

AS

O,

OrderItems

AS

OI

WHERE

C.cust_id

=

O.cust_id

--

多個表聯(lián)結查詢

AND

OI.order_num

=

O.order_num

AND

prod_id

=

'RGAN01'3、外聯(lián)結有時候我們需要將一個表中的行和另一個表中行相關聯(lián)，但是有時候也需要包含那些沒有關聯(lián)行的行記錄，比如下面的場景中：對每個顧客下的訂單數(shù)進行統(tǒng)計，包含那些至今尚未下單的顧客列出所有產(chǎn)品以及訂購數(shù)量，包含沒有人訂購的產(chǎn)品計算平均銷售規(guī)模，包含那些至今尚未下訂單的顧客當聯(lián)結中包含了那些在相關表中沒有關聯(lián)行的行，這種聯(lián)結稱之為外聯(lián)結。比如：檢索出包括沒有訂單顧客在內(nèi)的所有顧客。SELECT

C.cust_id

,O.order_num

FROM

Customers

AS

C

LEFT

OUTER

JOIN

Orders

AS

O

--

外連接

ON

Customers.cust_id

=

Orders.cust_id上面的代碼中表示包含左邊所有行的記錄；如果是右邊，使用RIGHTOUTER。因此外聯(lián)結實際上有兩種形式，它們之間可以互換左外聯(lián)結右外聯(lián)結還有一種比較特殊的外聯(lián)結，叫做全外聯(lián)結fullouterjoin，它檢索的是兩個表中的所有行并關聯(lián)那些可以關聯(lián)的行。全外聯(lián)結包含兩個表的不關聯(lián)的行SELECT

C.cust_id

,O.order_num

FROM

Customers

AS

C

FULL

OUTER

JOIN

Orders

AS

O

--

外連接

ON

Customers.cust_id

=

Orders.cust_id帶有聚集函數(shù)的聯(lián)結檢索所有顧客及每個顧客所有的訂單數(shù)：SELECT

C.cust_id

,COUNT(O.order_num)

AS

num_ord

--

使用聚集函數(shù)統(tǒng)計訂單數(shù)

FROM

Customers

AS

C

INNER

JOIN

Orders

ON

C.cust_id

=

O.cust_id

--

關聯(lián)兩個表

GROUP

BY

Customers.cust_id

--

分組使用聯(lián)結和聯(lián)結條件總結一下聯(lián)結和使用要點：注意使用聯(lián)結的類型：一般是使用內(nèi)聯(lián)結，有時候外聯(lián)結有有效要保證使用正確的聯(lián)結條件，否則會返回不正確的數(shù)據(jù)記得提供聯(lián)結條件，否則返回的是笛卡爾積一個聯(lián)結中可以包含多個表，甚至可以對不同的表使用不同的聯(lián)結類型。要注意測試每個聯(lián)結十四、組合查詢UNION什么是組合查詢SQL允許執(zhí)行多個查詢（多條SELECT語句），并將結果作為一個查詢結果集返回，這些組合通常稱為并union或者復合查詢；通常兩種情況下需要使用組合查詢：在一個查詢中從不同的表返回結構數(shù)據(jù)對一個表執(zhí)行多個不同的查詢，按照一個查詢返回數(shù)據(jù)創(chuàng)建組合查詢可以用UNION操作符來組合數(shù)條SQL查詢。--

語句1

SELECT

cust_name,

cust_contact,cust_email

FROM

Customers

WHERE

cust_state

IN

('IL','IN','MI');

--

語句2

SELECT

cust_name,

cust_contact,cust_email

FROM

Customers

WHERE

cust_name

=

'Fun4ALL';通過組合查詢將上面兩個查詢組合在一起：--

組合查詢

SELECT

cust_name,

cust_contact,cust_email

FROM

Customers

WHERE

cust_state

IN

('IL','IN','MI')

UNION

--

關鍵字

SELECT

cust_name,

cust_contact,cust_email

FROM

Customers

WHERE

cust_name

=

'Fun4ALL';我們也可以使用多個WHERE條件來實現(xiàn)：--

語句1

SELECT

cust_name,

cust_contact,cust_email

FROM

Customers

WHERE

cust_state

IN

('IL','IN','MI')

AND

cust_name

=

'Fun4ALL';UNION使用規(guī)則總結UNION使用規(guī)則：UNION必須由兩條或者兩條以上的SELECT語句組成；語句之間通過UNION關鍵字隔開UNION中的每個查詢必須包含相同的列、表達式或者聚集函數(shù)列數(shù)據(jù)類型必須兼容：類型不必完全相同UNION從查詢結果集中會自動消除重復的行；但是如果想保留所有的行，使用UNIONALL實現(xiàn)對組合結果進行排序SELECT語句的輸出用ORDERBY子句排序。--

組合查詢

SELECT

cust_name,

cust_contact,cust_email

FROM

Customers

WHERE

cust_state

IN

('IL','IN','MI')

UNION

--

關鍵字

SELECT

cust_name,

cust_contact,cust_email

FROM

Customers

WHERE

cust_name

=

'Fun4ALL'

ORDER

BY

cust_name,

cust_contact;

--

組合之后再進行排序十五、插入數(shù)據(jù)插入數(shù)據(jù)INSERT用來將行插入（或者添加）到數(shù)據(jù)庫表中，3種插入方式：插入完整的行插入行的一部分插入某些查詢的結果下面通過實際的例子來說明：1、插入完整的行INSERT

INTO

Customers

VALUES('1000000006',

'Tony',

'123

Any

Street',

'New

York',

'NY',

'1111',

'USA',

NULL,

NULL

)將上面的數(shù)據(jù)插入到Customers表中，每列對應一個值。如果值不存在，則用NULL代替。同時插入數(shù)據(jù)的順序必須和表中定義的相同。安全寫法：列出每個字段名稱INSERT

INTO

Customers(cust_id,

--

明確列出列名

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country,

cust_contact,

cust_email

)

VALUES('1000000006',

--

和上面的列名一一對應

'Tony',

'123

Any

Street',

'New

York',

'NY',

'1111',

'USA',

NULL,

NULL

)上面列名和下面插入的數(shù)據(jù)必須一一對應，我們改變插入順序：INSERT

INTO

Customers(cust_id,

--

明確列出列名

cust_zip,

cust_country,

cust_contact,

cust_email,

cust_name,

cust_address,

cust_city,

cust_state

)

VALUES('1000000006',

--

和上面的列名一一對應

'1111',

'USA',

NULL,

NULL,

'Tony',

'123

Any

Street',

'New

York',

'NY'

)2、插入部分數(shù)據(jù)上面的例子中我們插入的是全部列名的數(shù)據(jù)，現(xiàn)在指定部分列名進行插入：INSERT

INTO

Customers(cust_id,

--

明確列出列名

cust_zip,

cust_country,

cust_name,

cust_address,

cust_city,

cust_state

)

VALUES('1000000006',

--

和上面的列名一一對應

'1111',

'USA',

'Tony',

'123

Any

Street',

'New

York',

'NY'

)3、插入檢索出來的數(shù)據(jù)INSERT的另一種使用是將SELECT檢索出來的結果插入到表中，使用INSERTSELECT語句INSERT

INTO

Customers(cust_id,

--

2、將SELECT檢索的結果插進來

cust_zip,

cust_country,

cust_contact,

cust_email,

cust_name,

cust_address,

cust_city,

cust_state

)

SELECT

cust_id,

--

1、SELECT先檢索結果

cust_zip,

cust_country,

cust_contact,

cust_email,

cust_name,

cust_address,

cust_city,

cust_state

FROM

CustNew;INSERTSELECT中SELECT語句也是可以包含WHERE子句，以便過濾插入的數(shù)據(jù)。從一個表復制到另一個表還有一種數(shù)據(jù)插入不需要使用INSERT語句，要將一個表的內(nèi)容復制到另一個表，可以使用SELECTINSERT語句SELECT

*

INTO

CustCopy

FROM

Customers;需要注意4點：在復制的過程中，任何SELECT選項和子句都可以使用，包含WHERE和GROUPBY子句可以利用聯(lián)結從多個表插入數(shù)據(jù)不管從多少個表中檢索數(shù)據(jù)，數(shù)據(jù)最終只能插入到一個表中INSERTINTO是插入數(shù)據(jù)；SELECTINSERT是導出數(shù)據(jù)十六、更新和刪除數(shù)據(jù)更新數(shù)據(jù)更新（修改）表中的數(shù)據(jù)，可以使update語句。常見的有兩種update方式：更新表中特定的行更新表中所有的行update語句的3個組成部分：要更新的表列名和它們的新值確定要更新哪些行的過濾條件UPDATE

Customers

--

1、待更新的表

SET

cust_email

=

'28173497@'

--

2、需要更細的列名和它的新值

WHERE

cust_id

=

'10000000005';

--

3、過濾條件同時更新多個值：UPDATE

Customers

--

1、待更新的表

SET

--

2、同時更新多個值

cust_email

=

'28173497@',

cust_contact

=

'Sam

Roberts'

WHERE

cust_id

=

'10000000005';

--

3、過濾條件在更新多個列的值時，只需要使用提交SET命令，每個列=值對之間通過逗號隔開，最后一個列不同。如果想刪除某個列的值，可以將其設置成NULL（假如表定義允許NULL值）。空字符串用''表示，是一個值NULL是沒有值的UPDATE

Customers

SET

cust_email

=

NULL

WHERE

cust_id

=

'100000000005';刪除數(shù)據(jù)從表中刪除數(shù)據(jù)使用DELETE語句。有兩種刪除方式：從表中刪除特定的行從表中刪除所有的行DELETE

FROM

Customers

WHERE

cust_id

=

'011111111116';DELETE是刪除整行而不是刪除列。要刪除列請使用UPDATE語句更新和刪除的指導原則請一定要帶上WHERE子句，否則會修改全部的數(shù)據(jù)；除非我們的確是需要更新全部記錄（少見）要保證每個表都有主鍵，可以指定各個主鍵、多個值或者值的范圍在UPDATE或者DELETE語句使用WHERE語句之前，先用SELECT進行測試，保證它過濾掉的是正確的記錄十七、創(chuàng)建和操作表創(chuàng)建表SQL中一般有兩種創(chuàng)建表的方法：多數(shù)DBMS都具有交互式創(chuàng)建和管理數(shù)據(jù)庫表的工具表也可以直接使用SQL語句來操控；通過createtable來實現(xiàn)CREATE

TABLE

Products

--

創(chuàng)建表

(

prod_id

CHAR(10)

NOT

NULL,

vend_id

CHAR(10)

NOT

NULL,

prod_name

CHAR(254)

NOT

NULL,

prod_price

DECIMAL(8,2)

NOT

NULL,

prod_desc

VARCHAR(1000)

NULL

);上面代碼的解釋：表名緊跟CREATETABLE關鍵字列名在圓括號中，各個列之間通過逗號隔開每列的定義以列名開始，后緊跟數(shù)據(jù)類型，是否允許控制等整條語句是以分號結束使用NULL值NULL值就是沒有值或者缺失值。每個表中的列要么是NULL列，要么是NOTNULL列。主鍵是其值唯一標識表中每一行的列。只有不允許NULL值的列可作為主鍵，允許NULL值的列不能作為唯一標識。筆記：NULL是默認設置。如果不指定NOTNULL，則認為指定的就是NULL。注意NULL和空字符串的區(qū)別：NULL值沒有值，不是空字符串空字符串值一個有效的值，它不是無值NULL值使用關鍵字NULL而不是空字符串指定指定默認值SQL中創(chuàng)建表的時候可以指定通過關鍵字DEFAULT來指定：CREATE

TABLE

OrderItems

(

order_num

INTEGER

NOT

NULL,

order_item

INTEGER

NOT

NULL,

prod_id

CHAR(10)

NOT

NULL,

quantity

INTEGER

NOT

NULL

DEFAULT

1,

--

指定默認值

item_price

DECIMAL(8,2)

NOT

NULL

);默認值一般是用于日期或者時間戳列。更新表更新表中的數(shù)據(jù)使用關鍵詞ALTERTABLE。ALTER

TABLE

Vendors

ADD

vend_phone

CHAR(20);刪除表刪除整個表而不是其內(nèi)容，使用DROPTABLE。DROP

TABLE

CustCopy;

--

執(zhí)行這條語句將會永久刪除該表重命名表通過關鍵字RENAME來實現(xiàn)RENAME

TABLE

old_name

TO

new_name;舊表(old_name)必須存在，新表(new_name)必須不存在。如果新表new_name存在，則該語句將失敗。十八、視圖VIEW什么是視圖視圖是虛擬的表，與包含數(shù)據(jù)的表不一樣，視圖只包含使用時動態(tài)檢索數(shù)據(jù)的查詢。之前的例子：檢索訂購了某種產(chǎn)品的顧客SELECT

cust_name,

cust_contact

FROM

Customers

AS

C,

Orders

AS

O,

OrderItems

AS

OI

WHERE

C.cust_id

=

O.cust_id

--

多個表聯(lián)結查詢

AND

OI.order_num

=

O.order_num

AND

prod_id

=

'RGAN01'現(xiàn)在我們將上面的結果包裝成一個名ProductsCustomers的虛擬表，可以得到相同的數(shù)據(jù)：SELECT

cust_name,

cust_contact

FROM

ProductsCustomers

WHRE

prod_id

=

'RGAN01'ProductsCustomers并不是一個表，只是一個視圖，它不包含任何列或者數(shù)據(jù)，包含的是一個查詢。為什么使用視圖總結以下幾點使用視圖的原因：重用SQL語句簡化復雜的SQL操作使用表的一部分而不是整個表保護數(shù)據(jù)?？梢允谟栌脩粼L問表的特定部分數(shù)據(jù)，而不是整個表的數(shù)據(jù)更改數(shù)據(jù)格式和表示、視圖可以返回和底層表的表示和格式不同的數(shù)據(jù)筆記：視圖本身不包含數(shù)據(jù)，使用的是從別處檢索出來的數(shù)據(jù)。視圖規(guī)則和限制關于視圖創(chuàng)建和使用的一些常見規(guī)則和限制：與表一樣，視圖命名必須唯一創(chuàng)建視圖的數(shù)目沒有限制創(chuàng)建視圖必須具有足夠的訪問權限視圖是可以嵌套的視圖不能索引，也不能有關聯(lián)的觸發(fā)器或者默認值創(chuàng)建視圖1、利用視圖簡化復雜的聯(lián)結CREATE

VIEW

ProductsCustomers

AS

--

創(chuàng)建視圖

SELECT

cust_name,

cust_contact

FROM

Customers

AS

C,

Orders

AS

O,

OrderItems

AS

OI

WHERE

C.cust_id

=

O.cust_id

AND

OI.order_num

=

O.order_num上面代碼創(chuàng)建了一個名為ProductsCustomers的視圖，我們查詢一條信息：SELECT

cust_name,

cust_contact

FROM

ProductsCustomers

WHRE

prod_id

=

'RGAN01'2、利用視圖重新格式化檢索出的數(shù)據(jù)CREATE

VIEW

VendorLocations

AS

--

創(chuàng)建視圖

SELECT

RTRIM(vend_name)

+

'

('

+

RTRIM(vend_country)

+

')'

AS

vend_title

FROM

Vendors;

--

從視圖中檢索數(shù)據(jù)

SELECT

*

FROM

VendorLocations;3、使用視圖過濾數(shù)據(jù)CREATE

VIEW

CustomerEmailList

AS

SELECT

cust_id,

cust_name,

cust_email

FROM

Customers

WHERE

cust_email

IS

NOT

NULL;

--

檢索數(shù)據(jù)

SELECT

*

FROM

CustomerEmailList;4、使用視圖和計算字段CREATE

VIEW

OrderItemExpanded

AS

--

創(chuàng)建視圖

SELECT

order_num,

prod_id,

quantity,

item_price,

quantity

*

item_price

AS

expanded_price

FROM

OrderItems;

--

使用視圖查詢數(shù)據(jù)

SELECT

*

FROM

OrderItemExpanded

WHERE

order_num

=

2008;十九、使用存儲過程什么是存儲過程存儲過程（StoredProcedure）是一種在數(shù)據(jù)庫中存儲復雜程序，以便外部程序調(diào)用的一種數(shù)據(jù)庫對象。存儲過程是為了完成特定功能的SQL語句集，經(jīng)編譯創(chuàng)建并保存在數(shù)據(jù)庫中，用戶可通過指定存儲過程的名字并給定參數(shù)(需要時)來調(diào)用執(zhí)行。筆記：簡答來說，存儲過程就是為以后使用而保存的一條或者多條SQL語句。為什么要使用存儲過程通過把處理封裝在一個易用的單元中，可以簡化復雜的操作存儲過程保證里數(shù)據(jù)的一致性，降低出錯可能性簡化對變動的關管理。如果表名、列名或者業(yè)務邏輯有變化，那么只需要更改存儲過程中的代碼，使用它的人員無需知道變化存儲過程通常是以編譯過的形式進行存儲，所以DBMS處理命令所需的工作量少，提高了性能筆記：總結存儲過程的3個優(yōu)點：簡單、安全、高性能創(chuàng)建存儲過程MySQL中創(chuàng)建存儲過程：CREATE

[DEFINER

=

{

user

|

CURRENT_USER

}]

PROCEDURE

sp_name

([proc_parameter[,...]])

[characteristic

...]

routine_body

proc_parameter:

[

IN

|

OUT

|

INOUT

]

param_name

type

characteristic:

COMMENT

'string'

|

LANGUAGE

SQL

|

[NOT]

DETERMINISTIC

|

{

CONTAINS

SQL

|

NO

SQL

|

READS

SQL

DATA

|

MODIFIES

SQL

DATA

}

|

SQL

SECURITY

{

DEFINER

|

INVOKER

}

routine_body:

Valid

SQL

routine

statement

[begin_label:]

BEGIN

[statement_list]

……

END

[end_label]MYSQL存儲過程中的關鍵語法聲明語句結束符，可以自定義:DELIMITER

$$

或

DELIMITER

//聲明存儲過程:CREATE

PROCEDURE

demo_in_parameter(IN

p_in

int)

存儲過程開始和結束符號:BEGIN

....

END

變量賦值:SET

@p_in=1

變量定義:DECLARE

l_int

int

unsigned

default

40000;

創(chuàng)建MySQL存儲過程、存儲函數(shù):CREATE

procedure

存儲過程名(參數(shù))存儲過程體:CREATE

function

存儲函數(shù)名(參數(shù))創(chuàng)建一個實際的存儲過程：mysql>

delimiter

$$

--

將語句的結束符號從分號;臨時改為兩個$$(可以是自定義)

mysql>

CREATE

PROCEDURE

delete_matches(IN

p_playerno

INTEGER)

->

BEGIN

->

DELETE

FROM

MATCHES

->

WHERE

playerno

=

p_playerno;

->

END$$

Query

OK,

0

rows

affected

(0.01

sec)

mysql>

delimiter;

--

將語句的結束符號恢復為分號執(zhí)行存儲過程EXECUTE

AddNewProduct('JS01',

'Stuffed

Eiffel

Tower',

9.83,

'Plush

stuffed

toy

with

the

text

La

Tour

Tower'

)關鍵詞是EXECUTE，后面緊跟的是存儲過程的名字名字后面有4個參數(shù)作用：這個存儲過程將行添加到Products表中，并將傳入的屬性賦給相應的列二十、管理事務處理這一章介紹的是MySQL中事務的相關知識點，包含什么是事務處理，怎樣利用COMMIT和ROLLBACK語句管理事務處理事務處理事務Transaction，一個最小的、不可再分的工作單元，通常一個事務對應一個完整的業(yè)務。InnoDB引擎是支持事務的，MyISAM不支持事務。事務是針對數(shù)據(jù)庫中DML數(shù)據(jù)操作語言的。事務處理（transactionprocessing）是一種機制，用來管理必須成批執(zhí)行的SQL操作。利用事務處理，可以保證一組操作不會中途停止，要么完全執(zhí)行，要么完全不執(zhí)行，來維護數(shù)據(jù)庫的完整性。在MySQL中，一條SQL語句就是一個事務。一個完整的業(yè)務需要大量的DML（insert、update、delete）語句來共同完成。只有DML數(shù)據(jù)操作語句才有事務。事務保證一組SQL語句要么全部執(zhí)行成功，要么全部失敗，以此來維護數(shù)據(jù)庫的完整性。如果沒有發(fā)生錯誤，整個語句提交到數(shù)據(jù)庫表中；如果發(fā)生錯誤，則進行回退（撤銷），將數(shù)據(jù)庫恢復到某個已知且安全的狀態(tài)栗子：銀行轉賬業(yè)務比如在兩個表中，A（原有400）給B（原有200）轉200塊錢，包含兩個過程：A轉出200，B轉進200只有當兩個過程全部完成才算真正的執(zhí)行了一個完整的事務過程。update

user

set

fee=200

where

id=1;

#

語句1

--

由于斷網(wǎng)、安全限制、超出磁盤空間等不可控制原因，下面的語句可能無法成功執(zhí)行

update

user

set

fee=400

where

id=2;

#

語句2語句1的成功執(zhí)行，并不能將底層數(shù)據(jù)庫中的第一個賬戶的數(shù)據(jù)進行修改，只是單純地記錄操作，記錄在內(nèi)存中完成第二條語句成功執(zhí)行之后，和底層數(shù)據(jù)庫文件中的數(shù)據(jù)完成同步若第二條數(shù)據(jù)執(zhí)行失敗，清空所有的歷史記錄事務相關術語事務處理相關的術語：事務transaction：一組SQL語句。只能處理DML語句：insert、update、delete語句回退rollback：指撤銷指定SQL的語句提交commit：指將未存儲的SQL語句結果寫入到數(shù)據(jù)庫表；只有進行了commit操作，數(shù)據(jù)才會從內(nèi)存中寫入磁盤中保留點savepoint：指事務處理過程中設置的臨時占位符plaveholder，可以對它發(fā)布回退（與整個事務回退處理不同）；保留點越多越好：越多的話，能夠更加靈活地處理回退問題CREATE

DATABASE

IF

NOT

EXISTS

employees;

--

創(chuàng)建數(shù)據(jù)庫

USE

employees;

CREATE

TABLE

`employees`.`account`

(

--

數(shù)據(jù)庫中創(chuàng)建表account

`id`

BIGINT

(11)

NOT

NULL

AUTO_INCREMENT,

`p_name`

VARCHAR

(4),

`p_money`

DECIMAL

(10,

2)

NOT

NULL

DEFAULT

0,

PRIMARY

KEY

(`id`)

--

設置主鍵

)

;

INSERT

INTO

`employees`.`account`

(`id`,

`p_name`,

`p_money`)

VALUES

('1',

'tim',

'200');

--

插入兩條記錄

INSERT

INTO

`employees`.`account`

(`id`,

`p_name`,

`p_money`)

VALUES

('2',

'bill',

'200');

START

TRANSACTION;

--

開啟事務

SELECT

p_money

FROM

account

WHERE

p_name="tim";

--

三條語句必須完整執(zhí)行

UPDATE

account

SET

p_money=p_money-100

WHERE

p_name="tim";

UPDATE

account

SET