【SQL語言】SQL語言基礎01

標籤：利用   表示   不同的應用   ase   因此   設計者   成長   關聯式資料庫   mariadb   

1、瞭解sql1.1資料庫基礎1.1.1資料庫（database）

　　:儲存有組織的資料的容器（通常是一個檔案或一組檔案）（也可理解為某種有組織的方式儲存的集合）
　　註：資料庫軟體稱為DBMS（資料庫管理系統）

1.1.2表（table）

　　:某種特定類型資料的結構清單（同一個類型且名字唯一）
　　特性：
　　定義了資料在表中如何儲存，包含儲存什麼樣的資料，和資料如何分解命名等~
　　表名：使表名成為唯一的，實際上是資料庫名和表名等的組合。
　　模式：關於資料庫和表的布局等特性資訊。

1.1.3列（column）

　　：表中的一個欄位。所有表都是由一個或者多個列組成
　　註：
　　每個列都有相應的資料類型。
　　資料類型（datatype）
　　:定義了列可以儲存哪些資料種類。

　　分解資料：
　　正確的將資料分解為多個列極為重要。

1.1.4行（row）

　　:表中的一個記錄

1.1.5主鍵（primary key）

　　一組（或一列）其值能夠唯一標識表中每一行。
　　提示：
　　應該總是定義主鍵，雖然並不總是需要主鍵，但多數資料庫設計者都
　　會保證增他們建立的每個表都具有主鍵，以便於以後的資料操作
　　和管理。

　　表中的任何列都可以作為主鍵，只要它滿足以下條件：
　　1）任意兩行都不具有相同的主索引值
　　2）每一行都必須具有一個主索引值（主鍵列不允許NULL值）
　　3）主鍵列中的值不允許修改或更新
　　4）主索引值不能重用（如果某行從表中刪除，他的主鍵不能賦值給以後的新行）

1.2什麼是SQL

　　SQL是Structured Query Language(結構化查詢語言 (SQL)的縮寫)。SQL
　　是專門用來與資料庫溝通的語言
　　優點：
　　1）sql不是某個特定資料庫供應商准有的語言，幾乎所有重要的
　　　DBMS都支援SQL
　　2)簡單易學
　　3）可以進行非常複雜和進階的資料庫操作

2、檢索資料
2.1 SELECT語句

　　關鍵字（Keyword）：
　　作為SQL組成部分的保留字。關鍵字不能用作表或列的名字。
　　附錄E列出了某些常用的保留字
　　提示：SQL是一種語言而不是一個應用程式，具體如何寫SQL語句並顯示輸出，是隨不同的應用程式而變化的

2.2檢索單個列

　　select pro_name From Products;
　　上述語句利用select語句從Products表中檢索一個名為pro_name的列。
　　所需列名跟在select之後,from關鍵字支出從那個表中檢索資料

　　提示：
　　多數SQL語句以分號（;）分隔。
　　SQL語句不區分大小寫因此SELECT與select是相同的。許多開發人員
　　喜歡對SQL關鍵字使用大寫，而對列名和表名使用小寫，這樣做使代碼更易於閱讀和調試。
　　不過雖然SQL是不區分大小寫，但是表名、列名和值可能有所不同（具體情況依賴於DBMS的配置）
　　在處理SQL語句時，其中所有空格都被忽略。SQL可寫成長長的一行也可以分寫多行

2.3檢索多個列

　　檢索多個列時，仍使用相同的SELECT語句。唯一不同的是必須在SELECT關鍵字後給出多個列名，
　　列名之間必須以逗號分隔。

　　提示：
　　資料表示：
　　SQL語句一般返回原始的，無格式的資料。資料的格式化是表示問題，而不是檢索問題。
　　因此，表示一般顯示該資料的應用程式中規定。通常很少直接使用實際檢索出的資料（沒有應用程式提供的格式）

2.4檢索所有列

　　在實際列名的位置使用星號（*）萬用字元

　　提示：如果給定一個萬用字元（*），則返回表中所有列。列的順序一般是列在表定義中出現的物理順序
　　但並不總是如此。
　　最大的優點是檢索出未知列

2.5檢索不同的值（distinct）

　　DISTINCT它指示資料庫只返回不同（具有唯一性）的值。如果使用distinct關鍵字，它必須放在列名前面

　　提示：不能部分使用distinct
　　此關鍵字作用於所有的列，不僅僅是跟在其後的那一列。除非指定的列完全相同，否則所有的行都會被檢索出來

2.6限制結果

　　如果你想返回第一行或一定數量的行，但是在不同的資料庫中這一實現並不相同
　　在SQL sever和Access中可以使用TOP關鍵字來限制最多返回多少行
　　例：
　　　　SELECT TOP 5 pro_name FROM Products;
　　此代碼使用SELECT TOP 5語句只檢索前五行資料
　　在oracle需要基於ROWNUM(行計數器)來計算運行
　　例：
　　　　SELECT prod_name
　　　　FROM Prodects
　　　　WHERE ROWNUM <=5;
　　在mysql中需要使用LIMIT子句
　　例： SELECT prod_name
　　　　FROM Products
　　　　LIMIT 5;
　　limit 5返回不超過五行的資料
　　為了的倒後面的5行資料，需要指定從哪開始以及檢索的行數
　　例：
　　　　SELECT pro_name
　　　　FROM Products
　　　　LIMIT 5 OFFSET 5;
　　第一個5字表示返回5行，第二個5表示從第6行開始（包括第六行）

　　###經常用到在資料庫中查詢中間幾條資料的需求

　　比如下面的sql語句：

　　　　① selete * from testtable limit 2,1;

　　　　② selete * from testtable limit 2 offset 1;

　　注意：

　　1.資料庫資料計算是從0開始的

　　2.offset X是跳過X個資料，limit Y是選取Y個資料

　　3.limit X,Y 中X表示跳過X個資料，讀取Y個資料

　　這兩個都是能完成需要，但是他們之間是有區別的：

　　①是從資料庫中第三條開始查詢，取一條資料，即第三條資料讀取，一二條跳過

　　②是從資料庫中的第二條資料開始查詢兩條資料，即第二條和第三條。

　　offset就代表位移量的意思 offset num 代表從 num+1開始查詢

　　並非所有的sql實現都一樣

2.7使用注釋

　　注釋使用--（兩個連字號）嵌在行內。--之後的文本就是注釋，這種比較常用
　　還有一種注釋#，在一行的開始處使用#，這一整行都將作為注釋。
　　/*開始，到*/結束

3、排序檢索資料

　　ORDER BY(按.....順序)

3.1排序資料

　　關聯式資料庫設計理論認為，如果不明確規定順序排序，則不應該假定檢索出的
　　資料的順序有任何意義
　　提示：
　　ORDER BY子句的位置
　　在指定一條ORDER BY子句時，應保證它是SELECT語句中最後一條子句。
　　通常ORDER BY子句中使用的列將是為顯示二選擇的列。但是，用非檢索的列排序資料是完全合法的。

3.2按照多個列排序

　　例：

　　　　SELECT prod_id,prod_price,prod_name FRMO Products
　　　　ORDER BY prod_price,prodname;
　　對於上述例子中的輸出，僅在多個行具有相同的prod_price值時才對產品按照prod_name進行排序、如果
　　prod_price列中所有的值都是唯一的，則不會按prod_name排序。

3.3按列位置排序

　　例：
　　　　SELECT prod_id,prod_price,prod_name
　　　　FROM Products
　　　　ORDER BY 2,3;
　　提示：當根據不出現在SELECT清單中的列記性排序時，不能採用這項技術

3.4指定排序方向（DESC）　　

　　資料的排序不限於升序排列（從A到Z）這知識預設的排序
　　使用ORDER BY 子句+DESC可降序排列
　　　　SELECT prod_id,prod_price,prod_name
　　　　FRMO Products
　　　　ORDER BY prod_price DESC;

　　若是打算用多個列進行排序
　　　　SELECT prod_id,prod_price,prod_name
　　　　FROM Products
　　　　ORDER BY prod_price DESC,prod_name;
　　提示：
　　DESC關鍵字只應用到直接位於其前面的列名。
　　如果想在多個列上進行降序排序，必須對每一列指定DESC關鍵字
　　DESC=descending降序，ASC=ascending升序（此為DBMS預設的）
　　在字典（disctionary）排序次序中，A被視為與a相同，這是大多數DBMS的預設行為

4、過濾資料（WHERE）4.1使用WHERE子句

　　WHERE子句在表名（FROM子句）之後給出
　　在同時使用ORDER BY和WHERE子句時，讓ORDER BY位於WHERE之後
　　例子：
　　　　SELECT prod_id,prod_price,prod_name
　　　　FROM Products
　　　　WHERE 3<prod_price AND prod_price<10
　　　　ORDER BY prod_price DESC,prod_name;

4.2 WHERE子句操作符號

　　操作符　　說明
　　=　　　　等於
　　<>　　　　不等於
　　<　　　　小於
　　<=　　　　小於等於
　　!<　　　　不小於
　　>　　　　大於
　　>=　　　　大於等於
　　!=　　　　不大於
　　BETWEEN在指定的兩個指之間
　　IS NULL為NULL值

4.2.1檢查單個值

　　例：
　　　　SELECT prod_name,prod_price
　　　　FROM Products
　　　　WHERE pro_price<10;

4.2.2 不匹配檢查

　　　　SELECT prod_id,prod_price,prod_name,vend_id
　　　　FROM Products
　　　　WHERE vend_id <> ‘DLL01‘;
　　檢索出所有供應商不是DLL01製造的商品
　　提示：如果將值與字串類型的列進行比較，就需要限定引號。
　　　　　　用來與數值比較不用引號

4.2.3 範圍值檢查

　　BETWEEN
　　例：
　　　　SELECT prod_id,prod_name,prod_price
　　　　FROM Prudcts
　　　　WHERE prod_price BETWEEN 3 AND 9;
　　在使用BETWEEN時，必須制定兩個值————所需範圍的低端值和高端值。
　　這兩個值必須用AND關鍵字分隔

4.2.4 空值檢查

　　在建立表時,表的列可指定其中的列能否不包含值，在一個列不包含值時
　　稱其包含空值NULL
　　NULL:
　　無值（no value）,它與欄位包含0，Null 字元串貨僅僅包含空格不同

　　例：
　　　　SELECT prod_name
　　　　FROM Products
　　　　WHERE prod_prcie IS NULL;
　　提示：各個DBMS特有的操作符
　　注意：NULL和非匹配
　　過濾資料時，一定要驗證被過濾列中含NULL的行確實出現在返回資料中

　　SQL語句中AND時且的意思，OR是或者即滿足一個即為真

5、進階過濾方式

　　WHERE子句的進階過濾方式和NOT和IN的

5.1 組合WHERE子句

　　SQL允許給出多個WHERE子句，這個子句有兩種使用方式即AMD和
　　OR子句使用方式。
　　操作符號（operator）
　　用來連接或改變WHERE子句中的子句的關鍵字，也稱為邏輯操作符
　　（logical operator）

5.1.1 AND操作符

　　用在WHERE子句中的關鍵字，用來指示檢索滿足所有給定條件的行（
　　即同時滿足多行），可以增加多個
　　過濾條件，每個條件間都要使用AND關鍵字
　　例：
　　　　SELECT prod_id,prod_name,prod_price
　　　　FROM Products
　　　　WHERE vend_id=‘DLL01‘ AND prod_price <= 4
　　　　ORDER BY prod_price DESC;

5.1.2 OR 操作符

　　用在WHERE子句中的關鍵字，與AND相反，許多DBMS在第一個條件滿足時
　　不管第二個條件是否滿足，相應的行都會被檢索出來。
　　例：
　　　　SELECT prod_id,prod_name,prod_price,vend_id
　　　　FROM Products
　　　　WHERE vend_id=‘DLL01‘ OR prod_price BETWEEN 1 AND 4
　　　　ORDER BY prod_price DESC , prod_name;

5.1.3 求值順序

　　例：假如需要列出價格為10及以上，且由DLL01或
　　BRS01製造的所有產品。
　　　　SELECT prod_name, prod_price
　　　　FROM Products
　　　　WHERE vend_id = ‘DLL01‘ OR vend_id = ‘BRSO1‘ AND prod_price >= 10
　　　　ORDER BY prod_price DESC ,prod_name;
　　++輸出結果為：
　　　　prod_name 　　 prod_price
　　　　-------------------------------------------------
　　　　Bird bean bag toy　　 3.49
　　　　Fish bean bag toy 　　 3.49
　　　　Rabbit bean bag toy　　 3.49
　　　　Raggedy Ann 　　 　　 4.99
　　　　18 inch teddy bear 1　　1.99

　　上面的結果，返回的行中有4行價格小於10，顯然，返回的行
　　未按照預期的進行過濾，由於AND在求值過程中優先順序更高，操作符被
　　符被錯誤的組合了

　　此問題解決方案是使用圓括弧對操作符進行明確分組
　　　　SELECT prod_name,prod_price
　　　　FROM Products
　　　　WHERE vend_id=‘DLL01‘ OR vend_id =‘BRS01‘ AND prod_price >= 10
　　　　ORDER BY prod_price DESC , prod_name;
　　輸出結果為：
　　　　prod_name prod_price
　　　　----------------------------------------------------
　　　　18 inch teddy bear 11.99

　　提示：
　　任何時候使用具有AND和OR操作符的WHERE子句，
　　都應該使用圓括弧明確地分組操作符。不要過分依賴預設求值順序。
　　使用圓括弧也沒什麼壞處，它能消除歧義。

5.2 IN操作符

　　IN操作符指定條件範圍，範圍或者能夠的每個條件都可以進行匹配。IN取一組由逗號分隔，
　　括在圓括弧中的合法值。

　　例：SELECT prod_id,prod_name,prod_price,vend_id
　　　　FROM Products
　　　　WHERE prod_price IN (‘DLL01‘,‘BRS01‘)
　　　　ORDER BY prod_name DESC;

5.3 not操作符

　　有且只有一個功能，WHERE 子句中用來否定其後條件的關鍵字

　　列：SELECT prod_name
　　　　FROM Products
　　　　WHERE NOT vend_id =‘DLL01‘
　　　　ORDER BY prod_name;
　　匹配非DLL01之外的所有東西
　　這個例子也可以使用操作符號<>來完成
　　給：
　　　　SELECT prod_name
　　　　FROM Prodcuts
　　　　WHERE vend_id <> ‘DLL01‘
　　　　ORDER BY prod_name

6、 用萬用字元進行過濾
6.1 LIEK 操作符

　　萬用字元（wildcard）
　　用來匹配值的一部分字元

　　搜尋模式（search pattern）
　　由字面值、萬用字元或兩者的組合構成的搜尋條件

　　謂詞（predicate）
　　操作符何時不是操作符？當她作為謂詞時。從技術上說LIKE
　　是謂詞而不是操作符。雖然最終的結果是相同的，但應該對此術語有所瞭解

　　注意：萬用字元搜尋只能用於文字欄位（字串），非文本資料類型
　　欄位不能使用萬用字元搜尋

6.1.1 百分比符號（%）萬用字元

　　最常使用的萬用字元是百分比符號（%），%百分比符號表示任何字元出現的次數
　　例子：
　　　　SELECT prod_id,prod_name
　　　　FROM Products
　　WHERE prod_name LIKE ‘Fish%‘
　　執行這條語句時將檢索任意以Fish起頭
　　的詞。
　　注意NULL:
　　萬用字元%看起來像是可以匹配任何東西，但有個例外
　　就是NULL子句 WHERE prod_name LIKE ‘%‘ 不會匹配產品
　　名稱為NULL的行

6.1.2 底線（_）萬用字元

　　底線的用途與%一樣，但它只匹配單個字元，而不是多個字元
　　即加幾個底線匹配幾個字元
　　例子：
　　　　SELECT * FROM Products
　　　　WHERE prod_name LIKE ‘__ inch tedy bear‘

6.1.3 方括弧（[]）萬用字元

　　方括弧（[]）萬用字元用來指定一個字元集
　　她必須匹配指定位置（萬用字元的位置）的一個字元

　　說明：並不總是支援集合
　　並不是所有的DBMS都支援用來建立集合的[]
　　。只有微軟的Access和SQL Sever支援集合。
　　例子：找出所有名字以J或M起頭的連絡人！
　　　　SELECT cust_contact
　　　　FROM Customers
　　　　WHERE cust_contact LIKE ‘[JM]%‘
　　　　ORDER BY cust_contact;
　　輸出：
　　　　cust_contact
　　　　--------------
　　　　Jim Jones
　　　　John Smith
　　　　Michelle Green

　　[JM]匹配括弧中任意一個字元，它也只能匹配單個字元
　　[JM]之後的%萬用字元匹配第一個字元之後的任意數目的字元，返回所需的結果

　　此萬用字元可以用前置詞字元^（脫字型大小）來否定。
　　列子：
　　查詢匹配J和M之外的任一字元起頭的任意連絡人名
　　　　SELECT cust_contact
　　　　FROM Customers
　　　　WHERE cust_contact LIKE ‘[^JM]%‘
　　　　ORDER BY cust_custact;

　　說明：Access中的否定集合
　　需要用！而不是^

　　註：當然也可以使用NOT操作符得出類似的結果。
　　^的唯一優點是在使用多個WHERE 子句時可以簡化文法
　　例子：
　　　　SELECT cust_contact
　　　　FROM Customers
　　　　WHERE NOT cust_contact LIKE ‘[JM]‘
　　　　ORDER BY cust_contact;

6.2 使用萬用字元的技巧

　　不要過度使用萬用字元。如果其他動作符能達到相同的目的
　　應該使用其他動作符

　　確實需要使用萬用字元時，也盡量不要把他們用在搜尋模式的開始處
　　把萬用字元置於開始處，搜尋起來是最慢的

　　仔細注意萬用字元的位置。如果放錯地方，可能不會返回想要的資料！

7. 建立計算欄位
7.1 計算欄位

　　儲存在資料庫表中的資料一般不是應用程式所需要的格式

　　欄位（field）
　　基本上與列（column）的意思相同，經常互換使用
　　不過資料庫列一般稱為列，而術語欄位通常與計算欄位一起使用

　　從用戶端（如應用程式）來看，計算欄位的資料與其他列的資料返回方式相同

　　提示：用戶端與伺服器的格式
　　在SQL語句內可完成的許多的轉換和格式化工作可都可以
　　直接在用戶端內完成。但一般來說，在資料庫伺服器上完成操作
　　要比在用戶端快的多

7.2 拼接欄位

　　拼接（concatenate）
　　將值連接到一起（將一個值附加到另一個值）構成單個值

　　題設：建立由兩列組成的標題
　　　　Vendors 表包含供應商名和地址資訊、假如要產生一個供應商報表
　　　　需要在格式化的名稱（位置）中列出供應商的位置
　　　　此報表需要一個值，而表中的資料存放區在兩個列：vend_name
　　　　和vend_country中。此外，需要用括弧將vend_country括起來，

　　　　在SQL中的SELECT語句中，可使用一個特殊的操作符來拼接兩個列。
　　　　根據你所使用的DBMS,此操作符可用加號（+）或者兩個豎杠（||）表示

　　說明：+與||
　　　　Access和SQL Sever使用+號。DB2,ORCALE,PostgreSQL,SQLite和Open Office Base使用||
　　列子：
　　　　SELECT vend_name+_‘(‘+vend_country+‘)‘
　　　　FROM Vendors
　　　　ORDER BY vend_name;
　　輸出：
　　　　（無列名）
　　---------------------------------------
　　　　Bear Enporium(USA)
　　　　Bears R Us(USA)
　　　　Doll House Inc(USA)
　　　　Fun and Games(England)
　　　　Fuball Inc(USA)
　　　　Jouets et ours(France)

　　　　下面是相同的語句，但使用的是||文法
　　　　SELECT vend_name ||‘(‘|| vend_country ||‘)‘
　　　　FROM Vendors
　　　　ORDER BY vend_name;
　　　　輸出如上~

　　下面是使用
　　MY SQL或MariaDB時需要使用的語句：
　　　　SELECT Concat(vend_name,‘(‘,vend_country,‘)‘)
　　　　FROM Vendors
　　　　ORDER BY vend_name;

　　從上述輸出中可以看到，SELECT 語句返回包含上述
　　四個元素的一個列（計算欄位）。
　　在看看上述SELECT 語句返回的輸出。結合成一個計算欄位的兩個列用空格填充，
　　許多資料庫（不是所有）儲存填充為列寬的文本值，而實際上
　　你要的結果不需要這些空格。所以為正確返回格式化資料
　　，必須去掉這些空格。這可以使用
　　SQL的RTRIM()函數來完成
　　例子：
　　　　SELECT RTRIM(vend_name)+‘(‘+RTRIM(vend_country)+‘)‘
　　　　FROM Vendors
　　　　ORDER BY vend_name;
　　輸出：
　　　　---------------------------------
　　　　Bear Emporium (USA)
　　　　Bears R Us (USA)
　　　　Doll House Inc. (USA)
　　　　Fun and Games (England)
　　　　Furball Inc. (USA)
　　　　Jouets et ours (France)

　　RTRIM()函數去掉值右邊的所有空格。各個列都進行了整理

　　說明：TRIM函數
　　大多數DBMS都支援RTRIM()去掉字元右邊的空格
　　　　LTRIM() 去掉字元左邊的空格
　　　　TRIM() 去掉字串左右兩邊的空格

　　使用別名（alias）：
　　原因：SELECT語句可以很好地拼接地址欄位。但是這個新計算資料行的名字實際上是沒有的
　　它只是一個值。r如果僅在SQL查詢工具中查看一下結果，這樣沒什麼不好。
　　但是一個未命名的列不能用於用戶端應用中，因為用戶端沒有辦法引用它
　　且使用別名可以讓SQL語句更簡短。

　　SQL支援別名，是一個欄位或值的替換名。用關鍵字AS賦予
　　例子：
　　　　SELECT RTRIM(vend_name)+‘(‘+RTRIM(vend_country)‘)‘
　　　　AS vend_title
　　　　FROM Vendors
　　　　ORDER BY vend_name;

　　輸出：
　　　　vend_title
　　　　--------------------
　　　　Bear Emporium (USA)
　　　　Bears R Us (USA)
　　　　Doll House Inc. (USA)
　　　　Fun and Games (England)
　　　　Furball Inc. (USA)
　　　　Jouets et ours (France)

　　下面是相同的語句，但是使用的是||文法；
　　　　SELECT RTRIM(vend_name) || ‘(‘||RTRIM(vend_country)||‘)‘
　　　　AS vend_title
　　　　FROM Vendors
　　　　ORDER BY vend_name;

　　下面是MySQL合MariaDB中使用的語句
　　　　SELECT Concat(vend_name,‘(‘,vend_country,‘)‘)
　　　　AS vend_title
　　　　FROM Vendors
　　　　ORDER BY vend_name；

　　使用別名可以讓SQL語句更簡短
　　例子：表的別名執行個體
　　下面的 SQL 陳述式選取 "菜鳥教程" 的所訪問記錄。我們使用 "Websites" 和 "access_log" 表，
　　並分別為它們指定表別名 "w" 和 "a"（通過使用別名讓 SQL 更簡短）：

　　執行個體
　　　　SELECT w.name, w.url, a.count, a.date
　　　　FROM Websites AS w, access_log AS a
　　　　WHERE a.site_id=w.id and w.name="菜鳥教程";

　　說明：AS通常可選
　　提示：別名的其他用途
　　別名還有其他用途。常見的用途包括
　　在實際的表列名包含不合法的字元（如空格）時
　　重新命名它，在原來的名字含混或容易誤解時擴充它。
　　別名有時也稱為匯出列（derived volumn）是相同的東西

7.3 執行算術計算

　　計算欄位的另一常見用途是對檢索出的資料進行算術計算。
　　例子：
　　　　SELECT prod_id,
　　　　quantity,
　　　　item_price
　　　　,quantity*item_price AS expanded_price
　　　　FROM OrderItems
　　　　WHERE order_num=20008;
　　輸出如下：
　　　　prod_id quantity item_price expanded_price
　　　　---------- ----------- ------------ -----------------
　　　　RGAN0154.990024.9500
　　　　BR03511.990059.9500
　　　　BNBG01103.490034.9000
　　　　BNBG02103.490034.9000
　　　　BNBG03103.490034.9000

　　基本算術操作符如下：圓括弧可以用來區分優先順序
　　操作符　　說明
　　+　　　　　　加
　　-　　　　　　減
　　*　　　　　　乘
　　/　　　　　　除

　　提示：如何測試計算
　　　　SELECT語句為測試、檢驗函數和計算提供了很好的方法·。雖然
　　　　SELECT通常用於從表中檢索資料，但是省略了FROM子句後就是簡單地
　　　　訪問和處理運算式。

 

【SQL語言】SQL語言基礎01