MySQL必知必會(1-12章)

xbhog發表於2020-09-23
MySql

第一章：瞭解SQL

資料庫基礎：（概念）

  1. 資料庫軟體：
    • DBMS（資料庫管理系統）
  2. 資料庫：
    • 通過DBMS建立和操縱的容器；
    • 儲存有組織的資料的容器-->通常是一個檔案或者一組檔案；
  3. 表：
    • 某種特定型別的結構化清單；
    • 相同的資料庫中不能兩次使用想用的表名；
    • 不同的資料庫中可以使用相同的表名；
  4. 列和資料型別：
    • 表有列組成，列中儲存著表中某部分的資訊；
    • 表中的一個欄位，所有的表都是由一個或多個列組成的；
    • 每個列都有對應的資料型別；
  5. 資料型別：
    • 所容許的資料的型別，每個表列都有對應的資料型別，它限制或容許該類中儲存的資料；
    • 優點：
      • 防止在數值欄位中錄入字元值；
      • 幫助正確的排序資料，在優化磁碟使用方面有重要作用；
  6. 行：
    • 表中的一個記錄；
    • 別稱記錄，從技術上講：行才是正確的術語；
  7. 主鍵：
    • 一列或一組列，其值能夠唯一區分表中的每一行；
    • 唯一標識表中每行的這個列（或這組列）稱為主鍵；
    • 滿足主鍵的條件：
      • 任意兩行都不具備相同的主鍵值；
      • 每個行都必須具有一個主鍵值（主鍵值不允許NULL值）；
    • 關於主鍵的好習慣：
      • 不更新主鍵列的值；
      • 不重用主鍵列的值；
      • 不在主鍵列中使用可能會更改的值；
  8. 外來鍵：（略......後面補充）

第2、3章：簡介 使用MySQL

資料庫配置：

可看之前寫的部落格：

https://www.cnblogs.com/xbhog/p/13550579.html

使用MySQL：

  1. 選擇資料庫：

    #顯示資料庫
show databases;
#使用資料庫
use DatabaseName;

    mysql> show databases;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| MySQL_Crash_Course   |
| MySQL_Crash_Course_1 |
| demo                 |
| mysql                |
| performance_schema   |
| sys                  |
+----------------------+
7 rows in set (0.00 sec)

mysql> use MySQL_Crash_Course;
Database changed

  2. 瞭解資料庫和表

    返回當前選擇的資料庫內可用表的列表（show tables;）

    #顯示錶名
show tables;
mysql> show tables;
+-------------------------+
| Tables_in_MySQL_Student |
+-------------------------+
| customers               |
| orderitems              |
| orders                  |
| productnotes            |
| products                |
| vendors                 |
+-------------------------+
6 rows in set (0.00 sec)

    show columns from customers

    解析：

    • show columns要求給出一個表名
    • 對每個欄位返回一行
    • DESCRIBE customers==show columns from customers(兩者作用相同)

第4章：檢索資料

檢索資料：

  1. 檢索單個列：

    select prod_name from products;

    注：

    • 如果沒有明確的排序查詢結果，則返回的資料的順序是沒有特殊意義的；

    • 返回資料的順序可能是資料被新增到表中的順序，也可能不是；

    • 只要返回的想用數目的行就是正常的。

  2. 檢索多個列：

    select prod_id,prod_name,prod_price from products;

    注：

    • 指定了三個列名，之間用逗號，

  3. 檢索所有列：

    select * from products;

    注：

    • 使用萬用字元（*）表示；
    • 除非確定需要表中的每個列，否則最好不要使用；
    • 檢索不需要的列通常會降低檢索和應用程式的效能。、

  4. 檢索不同的行：

    關鍵字：DISTINCT--->必須放到列名的前面

    select DISTINCT vend_id from products;

    注：

    • 不能部分使用DISTINCT關鍵字；
    • 除非指定的兩個列都不同，否則所有行都將被檢索出來。

  5. 限制結果：

    關鍵字：LIMIT

    select prod_name from products LIMIT 5;

    注：

    • 指MySQL返回不多於5行
    select prod_name from products LIMIT 5，5;

    注：

    • 從第五行開始的5行；
    • 第一個資料下標為0；
    • 帶一個值的limit總是從第一行開始，給出的數為返回的行數；
    • 帶兩個值的limit可以指定從行號為第一個值的位置開始；
    • 如果行數不夠的時候，MySQL將只返回它能返回的那些行。

  6. 使用完全限定的表名：

    #同時使用表名和列名
select products.prod_name from products;

    #假定products確實位於crashcourse資料庫中
select products.prod_name from crashcourse.products;

    第5章：排序檢索資料

    排序資料:

    平常進行查詢的話是沒有特定的順序，

    select prod_name from products;

    檢索的資料並不是純粹的隨機順序顯示的，資料後來進行過更新或者刪除，則此順序將會收到MySQL重用回收儲存空間的影響

    注：如果不明確規定排序順序，則不應該假定檢索出的資料順序有意義。

    關鍵字：order by---取出一個或多個列的名字，據此對輸出進行排序；

    select prod_name from products order by prod_name;

    prod_name以字母順序排序資料

按多個列排序:

指定列名，列名之間用逗號分割；

select prod_id, prod_name,prod_price from products order by prod_price，prod_name;

首先按照價格排序，然後按照名字排序；

僅在多個行具有相同的prod_price值時才對產品按照prod_name進行排序，如果prod_price列中所有的值都是唯一的，則不會按prod_name排序。

指定排序方向:

MySQL預設的排序時ASC（升序）；

關鍵字：desc （降序）

select prod_id,prod_name,prod_price from products order by prod_price desc;

篩選出最貴的依次放在前面

select prod_id,prod_name,prod_price from products order by prod_price desc，prod_name;

只對prod_price有效；

找到最高/最低的值：order by+limit;

select prod_price from products order by prod_price desc limit 1 ;

第6章：過濾資料

使用where:

關鍵字：where,where+搜尋條件/過濾條件；

demo:

select prod_name,prod_price from products where prod_price =2.50;

同時使用where與order by時，應該order by在where之後，否則將會產生錯誤；

where的操作符：

運算子 描述
= 等於
<> 不等於。註釋：在 SQL 的一些版本中，該操作符可被寫成 !=
> 大於
< 小於
>= 大於等於
<= 小於等於
BETWEEN 在某個範圍內
LIKE 搜尋某種模式
IN 指定針對某個列的多個可能值

檢查單個值：

demo:

select prod_name,prod_proce from products where prod_name ='fuses'

例：列出價格小於10美元的所有產品：

select prod_name,prod_price from products where prod_price<10;

列出價格小於等於10美元的所有產品：

select prod_name,prod_price from products where prod_price<=10;

不匹配檢查：

列：不是由供應商1003製造的所有產品

select vend_id,prod_name from products where vend_id <>1003;

select vend_id,prod_name from products where vend_id !=1003;

select vend_id,prod_name from products where not vend_id =1003;

範圍值檢查：

關鍵字：between 開始值 and 結束值

例：檢索價格在5美元和10美元之間的所有產品

selecy prod_name,prod_price from products where prod_price between 5 and 10;

between關鍵字匹配的範圍中所有的值，包括指定的開始值與結束值；

空值檢查：

在一個列中不包含值時，稱為包含空值NULL；

NULL 無值，它與欄位包含0，空字串或者僅僅包含空格不同；

關鍵字：is null;檢查具有NULL的列

select prod_name from products where prod_price is null;

select cust_id from customers where cust_email is null;

NULL與不匹配：

在通過過濾選擇出不具有特定值的行時，你可能希望返回具有NULL值的行。但是，不行。因為未知具有特殊的含義，資料庫不知道它們是否匹配，所以在匹配過濾或不匹配過濾時不返回它們；

因此，再過濾資料時，一定要驗證返回資料中確實給出了被過濾列具有的NULL的行。

第7章：資料過濾

組合hwere子句：

關鍵字：and or not in (括號)-->計算優先順序

操作符：用來聯結或改變where子句中的子句的關鍵字，也成為邏輯操作符

AND操作符：

關鍵字：and 用來指示檢索滿足所有給定條件的行；

select prod_id,prod_name,prod_price from products where vend_id =1003 and prod_price <=10;

注：還可以新增多個過濾條件，每新增一個就要使用一個and;

or操作符：

關鍵字：or 檢索任意給定的條件,而不是同時匹配兩個或多個條件。

select prod_id,prod_name,prod_price from products where vend_id =1003 or vend_id =1002;

計算次序：

and在計算次序中優先順序更高

列出價格為10美元以上並且由1002或者1003製造的所有產品：

select vend_id,prod_price,prod_name from products where vend_id =1002 or vend_id =1003 and prod_price >=10

輸出：

+---------+------------+----------------+
| vend_id | prod_price | prod_name      |
+---------+------------+----------------+
|    1003 |      13.00 | Detonator      |
|    1003 |      10.00 | Bird seed      |
|    1002 |       3.42 | Fuses          |
|    1002 |       8.99 | Oil can        |
|    1003 |      50.00 | Safe           |
|    1003 |      10.00 | TNT (5 sticks) |
+---------+------------+----------------+

上面的輸出並沒有滿足我們的條件；原因是；and的優先順序高，所以執行的時候是（vend_id =1002）or(vend_id =1003 and prod_price >=10);而不是從左到右的方式執行；

整解：使用括號明確的分組

select vend_id,prod_price,prod_name from products where (vend_id =1002 or vend_id =1003) and prod_price >=10;

+---------+------------+----------------+
| vend_id | prod_price | prod_name      |
+---------+------------+----------------+
|    1003 |      13.00 | Detonator      |
|    1003 |      10.00 | Bird seed      |
|    1003 |      50.00 | Safe           |
|    1003 |      10.00 | TNT (5 sticks) |
+---------+------------+----------------

不要過分的依賴預設的計算次序，使用圓括號沒有什麼壞處，他能消除歧義。

IN操作符：

關鍵字：IN 用來指定條件範圍，範圍中的每個條件都可以進行匹配

IN取合法值的由逗號分割的清單，全部包括在圓括號中；

select prod_name,prod_price from products where vend_id in(1002,1003) order by prod_name;

+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Detonator      |      13.00 |
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Safe           |      50.00 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
9 rows in set (0.00 sec)

IN操作符的作用與OR有相同的功能；

select prod_name,prod_price from products where vend_id=1002 or vend_id=1003 order by prod_name;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Detonator      |      13.00 |
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Safe           |      50.00 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+

IN操作符的好處：

  1. 在使用長的合法選項清單時，IN操作符的語法更清楚且更直觀。

  2. 在使用IN時，計算的次序更容易管理（因為使用的操作符更少）。

  3. IN操作符一般比OR操作符清單執行更快。

  4. IN的最大優點是可以包含其他SELECT語句，使得能夠更動態地建立WHERE子句。第11章將對此進行詳細介紹。

  5. IN WHERE子句中用來指定要匹配值的清單的關鍵字，功能與OR相當。

NOT操作符：

關鍵字：not 用來否定後跟條件的關鍵字

例：列出除1002和1003之外的所有供應商製造的產品

select prod_name,prod_price from products where vend_id not in (1002,1003) order by prod_name;

+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| .5 ton anvil |       5.99 |
| 1 ton anvil  |       9.99 |
| 2 ton anvil  |      14.99 |
| JetPack 1000 |      35.00 |
| JetPack 2000 |      55.00 |
+--------------+------------+
5 rows in set (0.00 sec)

第8章：用萬用字元進行過濾

like操作符：

關鍵字：LIKE 從技術上講，LIKE是謂詞而不是操作符；

萬用字元：用來匹配值的一部分的特殊字元；

搜尋模式：由字面值、萬用字元或者兩者組合構成的搜尋條件；

LIKE指示MySQL後跟的搜尋模式利用萬用字元匹配而不是直接相等匹配進行比較。

百分號（％）萬用字元：

%表示任何字元出現的次數;

例:找出所有以jet開頭的產品

select prod_id,prod_name from products where prod_name like 'jet%';

+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| JP1000  | JetPack 1000 |
| JP2000  | JetPack 2000 |
+---------+--------------+
2 rows in set (0.00 sec)

萬用字元可以在搜尋模式中任意位置應用,並且可以使用多個萬用字元

select prod_id,prod_name from products where prod_name like '%anvil%';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| ANV01   | .5 ton anvil |
| ANV02   | 1 ton anvil  |
| ANV03   | 2 ton anvil  |
+---------+--------------+
3 rows in set (0.00 sec)

萬用字元也可以出現在搜尋模式的中間

mysql> select prod_id,prod_name from products where prod_name like 's%e';
+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
| SAFE    | Safe      |
+---------+-----------+
1 row in set (0.00 sec)

%除了一個或者多個字元外,還可以匹配0個字元;%代表搜尋模式中給定位置的0 個 1個或者多個字元;

注:尾空格可能會干擾萬用字元匹配(%anvil)

解決:在搜尋模式後附加一個%,或者使用函式---11章介紹

注:但是萬用字元不能匹配NULL

下劃線(_)萬用字元:

作用:只能匹配單個字元而不是多個字元;

(_)

mysql> select prod_id,prod_name from products where prod_name like '_ ton anvil';
+---------+-------------+
| prod_id | prod_name   |
+---------+-------------+
| ANV02   | 1 ton anvil |
| ANV03   | 2 ton anvil |
+---------+-------------+
2 rows in set (0.00 sec)

對照(%):

mysql> select prod_id,prod_name from products where prod_name like '% ton anvil';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| ANV01   | .5 ton anvil |
| ANV02   | 1 ton anvil  |
| ANV03   | 2 ton anvil  |
+---------+--------------+
3 rows in set (0.00 sec)

使用萬用字元的技巧:

萬用字元很有用，但也是有代價的：萬用字元搜尋的處理一般要比操作符搜尋要花更長的時間。
萬用字元使用需要注意的幾點：

  • 不要過度使用萬用字元，如果其他操作可以達到相同的目的，使用其他操作符
  • 在確實需要使用萬用字元時，除非絕對必要，否則不要把他們用在搜尋模式的開始處。萬用字元置於開始處，搜尋最慢
  • 仔細注意萬用字元的位置，如果放錯地方，可能不會返回想要的資料。

第9章：用正規表示式進行搜尋

什麼是正規表示式：

  1. 用來匹配文字的特殊字符集合
  2. 關鍵字：regexp(REGEXP)

基本字元匹配：

例1：檢索列prod_name包含文字1000的所有行

mysql> select prod_name from products where prod_name regexp '1000' order by prod_name;

+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.01 sec)

例2：

mysql> select prod_name from products where prod_name regexp '.000' order by prod_name;

+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

正則語法："."匹配任意一個字元

比較：LIKE

mysql> select prod_name from products where prod_name like '1000' order by prod_name;
Empty set (0.00 sec)

mysql> select prod_name from products where prod_name like '%1000' order by prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.00 sec)

解釋：

​ LIKE匹配的是整個列，如果被匹配的文字在列值中出現，LIKE將不會找到它，相應的行也不被返回（除非使用萬用字元 －％　_）；

​ 而REGEXP在列值內進行匹配，如果匹配的文字在列值中出現，REGEXP將會找到它，相應的行將被返回

匹配大小寫的問題：

​ MySQL在本本3.23.4後不區分大小寫，為區分大小寫使用關鍵字：BINARTY

進行OR匹配：

為搜尋兩個串之一使用---'|'

例：

mysql> select prod_name from products where prod_name REGEXP '1000 | 2000' order by prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 2000 |
+--------------+
1 row in set (0.00 sec)

mysql> select prod_name from products where prod_name REGEXP '1000|2000' order by prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

注：注意書寫正則的格式，嚴格按照規範，否則匹配的完全不同結果。

拓：

可以給出多個條件：

select prod_name from products where prod_name REGEXP '1000|2000|.ton' order by prod_name;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
| Detonator    |
| JetPack 1000 |
| JetPack 2000 |
+--------------+
6 rows in set (0.00 sec)

匹配幾個字元之一：

匹配特定的的字元，可以指定一組用''[]"括起來的字元完成；

select  prod_name from products where prod_name REGEXP '[123] Ton' order by prod_name;

+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)

[]是另一種形式的OR語句，最好使用[],雖然功能與|相同，但是|有時候有歧義，

select  prod_name from products where prod_name REGEXP '1|2|3 Ton' order by prod_name;

+---------------+
| prod_name     |
+---------------+
| 1 ton anvil   |
| 2 ton anvil   |
| JetPack 1000  |
| JetPack 2000  |
| TNT (1 stick) |
+---------------+
5 rows in set (0.00 sec)

正確的寫法需要加（）：

select  prod_name from products where prod_name REGEXP '(1|2|3) Ton' order by prod_name;
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)

與匹配[123]相反的是匹配[123]之外的：[^123]

select  prod_name from products where prod_name REGEXP '[^123] Ton' order by prod_name;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
+--------------+
1 row in set (0.00 sec)

拓：[123]--->[1-3] (定義範圍)

匹配特殊字元：

例：匹配包含 “ . ”字元的值

為匹配特殊字元，必須用\\作為前導；\\-表示查詢-，\\.表示查詢 .;---->轉義

select vend_name from vendors where vend_name regexp '\\.' order by vend_name;

+--------------+
| vend_name    |
+--------------+
| Furball Inc. |
+--------------+
1 row in set (0.00 sec)

假設我們需要匹配常用特殊字元即可這麼寫：

\\[ 匹配左方括號
\\. 匹配點號
\\] 匹配右方括號
\\| 匹配豎線
\\\ 匹配反斜槓自己本身

依次類推，其他特殊的字串也可以使用這麼方式處理。

雙反斜槓加上一些字母還可以表示特殊的含義。

比如：

\\f 換頁
\\n 換行
\\r 回車
\\t 製表符
\\v 縱向製表符

在一般的程式語言中，轉義一般使用一個反斜線，在Mysql中為什麼是兩個才行？原因是：Mysql自己需要一個來識別，然後Mysql會將扣除了一個反斜槓的剩餘的部分完全的交給正規表示式庫解釋，所以加起來就是兩個了。

匹配字元類：

我們直接給出表直接參閱。

類 說明
[:alnum:] 任意數字和字母。相當於[a-zA-Z0-9]
[:alpha:] 任意字元。相當於[a-zA-z]
[:blank:] 空格和製表。相當於[（雙斜槓，segmentfault這裡雙斜槓打不出來）t]
[:cntrl:] ASCII控制字元（ASCII 0 到31和127）
[:digit:] 任意數字。相當於[0-9]
[:graph:] 與[:print:]相同，但是不包含空格
[:lower:] 任意的小寫字母。相當於[a-z]
[:print:] 任意可列印字元
[:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字元
[:space:] 包括空格在內的任意空白字元。
[:upper:] 任意大寫字母。相當於[A-Z]
[:xdigit:] 任意十六進位制的數字。相當於[a-fA-F0-9]

匹配多個例項：

正規表示式重複元字元：

元字元 作用
* 重複0次或者多次
+ 重複一次或者多次。相當於{1,}
? 重複0次或者1次
{n} 重複n次
{n,} 重複至少n次
{n,m} 重複n-m次

例：

select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)' order by prod_name;

+----------------+
| prod_name      |
+----------------+
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
2 rows in set (0.00 sec)

解釋：

\\( 匹配 \\) 【0-9】-->匹配範圍中的任意數字，sticks?匹配stick+sticks,(s後面的？使s可選)

例：

select prod_name from products where prod_name regexp '[[:digit:]]{4}' order by prod_name;

--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

解釋：

​ [[:digit:]]{4}匹配連在一起的任意4位數字。

定位符：

^ 文字開始
$ 文字結束
[[:<:]] 詞的開始
[[:>:]] 詞的結束

^ 的雙重作用：（1）集合中，否定集合；（2）表示文字開始

例：找出一個數（包括以小數點開始的數）開始的所有產品；

select prod_name from products prod_name regexp '^[0-9\\.]' order by prod_name;

+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
3 rows in set (0.00 sec)

對照沒有^:

select prod_name from products where prod_name regexp '[0-9\\.]' order by prod_name;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| JetPack 1000   |
| JetPack 2000   |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
7 rows in set (0.00 sec)

解釋：^匹配串的開始，因此[0-9\.]只在.或者任意數字為串中第一個字元時菜匹配他們。

^的雙重用途：

在集合中[^]用來否定該集合，其他則用來指串的開始處。

第10章：建立計算欄位

計算欄位相關概念：

計算欄位與列不同，計算欄位並不實際存在與資料庫表中，計算欄位是執行時在select語句中建立的；

欄位：基本與列的意思相同，經常互換使用，不過資料庫列一般稱為列。

拼接欄位：

拼接（concatenate）將值聯結到一起構成單個值;

函式：Concat（）

注：多數DBMS使用+或者||拼接

mysql> select concat(vend_name,"(",vend_country,")") from vendors order by vend_name ;
+----------------------------------------+
| concat(vend_name,"(",vend_country,")") |
+----------------------------------------+
| ACME(USA)                              |
| Anvils R Us(USA)                       |
| Furball Inc.(USA)                      |
| Jet Set(England)                       |
| Jouets Et Ours(France)                 |
| LT Supplies(USA)                       |
+----------------------------------------+
6 rows in set (0.01 sec)

Concat()拼接串，需要一個或多個指定的串，各個串之間用逗號分割；

RTrim（）函式：刪除資料右側多餘的空格來整理資料；

select concat(rtrim(vend_name),"(",rtrim(vend_country),")") from vendors order by vend_name ;
+------------------------------------------------------+
| concat(rtrim(vend_name),"(",rtrim(vend_country),")") |
+------------------------------------------------------+
| ACME(USA)                                            |
| Anvils R Us(USA)                                     |
| Furball Inc.(USA)                                    |
| Jet Set(England)                                     |
| Jouets Et Ours(France)                               |
| LT Supplies(USA)                                     |
+------------------------------------------------------+
6 rows in set (0.02 sec)

LTrim()去掉串左邊的空格，Trim()除去串左右兩邊的空格

使用別名：（alias）

關鍵字：AS

mysql> select concat(rtrim(vend_name),"(",rtrim(vend_country),")")  as vend_title from vendors order by vend_name ;
+------------------------+
| vend_title             |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+
6 rows in set (0.00 sec)

AS指示SQL建立一個包含指定計算的名為vend_title 的計算欄位，與實際的表一樣可以呼叫;

別名有時也稱匯出列，代表的內容相同。

執行算數計算：

首先檢索訂單號20005中的所有物品：

mysql> select * from orderitems where order_num =20005;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
|     20005 |          1 | ANV01   |       10 |       5.99 |
|     20005 |          2 | ANV02   |        3 |       9.99 |
|     20005 |          3 | TNT2    |        5 |      10.00 |
|     20005 |          4 | FB      |        1 |      10.00 |
+-----------+------------+---------+----------+------------+
4 rows in set (0.01 sec)

彙總物品的價格：單價乘以數量

mysql> select prod_id,quantity,item_price,quantity*item_price AS expanded_price from orderitems where order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+

第11章：使用資料處理函式

SQL支援型別的函式：

  1. 用於處理文字串的文字函式；
  2. 用於在數值資料上進行算數操作的數值函式；
  3. 用於處理熱氣和時間值並從這些值中提取特定成分的日期和時間函式；
  4. 返回DBMS正是用的特殊資訊的系統函式；

文字處理函式：

關鍵字：upper（）

作用：將文字轉換成大寫

select vend_name,Upper(vend_name) as vend_name_upase from vendors order by vend_name;
+----------------+-----------------+
| vend_name      | vend_name_upase |
+----------------+-----------------+
| ACME           | ACME            |
| Anvils R Us    | ANVILS R US     |
| Furball Inc.   | FURBALL INC.    |
| Jet Set        | JET SET         |
| Jouets Et Ours | JOUETS ET OURS  |
| LT Supplies    | LT SUPPLIES     |
+----------------+-----------------+
6 rows in set (0.00 sec)

常用的文字處理函式：（詳細解釋待補充）

函式 說明
left() 返回串左邊的字元
length() 返回串的長度
locate() 找出串的一個字串
lower() 將串轉換成小寫
LTrim() 去掉左邊的空格
Right() 返回串右邊的字元
RTrim() 去掉串右邊的空格
Soundex() 返回串的SOUNDEX的值
SubString() 返回字串的字元
Upper() 將串轉換成大寫

其中SOUNDEX解釋：

​ 個人理解模糊匹配相同的發音；

mysql> select cust_name , cust_contact from customers;
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Mouse House    | Jerry Mouse  |
| Wascals        | Jim Jones    |
| Yosemite Place | Y Sam        |
| E Fudd         | E Fudd       |
+----------------+--------------+
5 rows in set (0.00 sec)

假如我們想查詢Y Lee聯絡人，但實際我們查詢輸入的使L Lie,那麼無法得到所需要的；

mysql> select cust_name,cust_contact from customers where cust_contact ="Y.Lie";
Empty set (0.00 sec)

我們可以使用SOUNDEX進行音節的模糊匹配：

mysql> select cust_name,cust_contact from customers where soundex(cust_contact) =soundex("Y.Lie");
+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+
1 row in set (0.00 sec)

日期和時間處理函式：

使用形式：大多數被用來讀取、統計和處理這些值

常用日期和時間處理函式：(待補充)

使用日期格式的注意點：

  1. 不管插入還是更新還是用where進行過濾，日期格式必須yyy-mm-dd;

    mysql> select cust_id,order_num from orders where order_date ="2005-09-01";
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+
1 row in set (0.00 sec)

mysql> select * from orders;
+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20005 | 2005-09-01 00:00:00 |   10001 |
|     20006 | 2005-09-12 00:00:00 |   10003 |
|     20007 | 2005-09-30 00:00:00 |   10004 |
|     20008 | 2005-10-03 00:00:00 |   10005 |
|     20009 | 2005-10-08 00:00:00 |   10001 |
+-----------+---------------------+---------+
5 rows in set (0.00 sec)

    關鍵字：date()

    作用：僅提取列的日期部分

    mysql> select cust_id,order_num from orders where date(order_date) ="2005-09-01";
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+
1 row in set (0.00 sec)

    日期的範圍搜尋：

    關鍵字：between and

    例：檢索2005年9月下的所有訂單

    mysql> select cust_id ,order_num from orders where date(order_date ) between "2005-09-01" and "2005-09-30";
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+
3 rows in set (0.00 sec)

    解2：

    mysql> select cust_id ,order_num  from orders where Year(order_date ) = 2005 and Month(order_date )=9;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+
3 rows in set (0.00 sec)

    數值處理函式：

    定義：數值處理僅處理數值資料，一般用於代數或者幾個運算；(詳細解釋待補充)

    第12章：彙總資料

    聚集函式：

    定義：聚集函式執行在行組上，計算和返回單個值的函式；

    函式 說明
    AVG() 返回某列的平均值
    COUNT() 返回某列的行數
    MAX() 返回某列的最大值
    MIN() 返回某列的最小值
    SUN() 返回某列值之和

    AVG()函式：

    作用：返回所有列的平均值，也可返回特定列的平均值

    mysql> select avg(prod_price) as avg_price from products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.06 sec)

    mysql> select prod_price  from products;
+------------+
| prod_price |
+------------+
|       5.99 |
|       9.99 |
|      14.99 |
|      13.00 |
|      10.00 |
|       2.50 |
|       3.42 |
|      35.00 |
|      55.00 |
|       8.99 |
|      50.00 |
|       4.49 |
|       2.50 |
|      10.00 |
+------------+
14 rows in set (0.00 sec)

    注：

    1. 為獲得多個列的平均值，必須使用多個AVG()函式；

    2. NULL值，AVG()函式忽略列值為NULL的行；

    COUNT()函式：

    兩種使用方法：

    1. 使用COUNT（*）對錶中行的數目進行計數，不管列表中包含的是空值還是非空值；
    2. 對特定列中具有值的行進行技術，忽略NULL值；
    #返回客戶數量--行
mysql> select count(*) from customers;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

    統計具有電子郵件地址的客戶數量：

    初始狀態：

    mysql> select cust_email from customers;
+---------------------+
| cust_email          |
+---------------------+
| ylee@coyote.com     |
| NULL                |
| rabbit@wascally.com |
| sam@yosemite.com    |
| NULL                |
+---------------------+
5 rows in set (0.00 sec)

    篩選：

    mysql> select count(cust_email )from customers;
+--------------------+
| count(cust_email ) |
+--------------------+
|                  3 |
+--------------------+
1 row in set (0.00 sec)

    MAX()與MIN()函式：

    特點：都需要指定列名；

    mysql> select max(prod_price ) as max_price from products;
+-----------+
| max_price |
+-----------+
|     55.00 |
+-----------+
1 row in set (0.00 sec)

mysql> select min(prod_price ) as min_price from products;
+-----------+
| min_price |
+-----------+
|      2.50 |
+-----------+
1 row in set (0.00 sec)

    SUM()函式：

    作用：指定列值的和

    相似：count計算行值的個數

    mysql> select sum(quantity ) as items_ordered from  orderitems where order_num = 20005;
+---------------+
| items_ordered |
+---------------+
|            19 |
+---------------+
1 row in set (0.00 sec)

    mysql> select quantity,order_num from  orderitems;
+----------+-----------+
| quantity | order_num |
+----------+-----------+
|       10 |     20005 |
|        3 |     20005 |
|        5 |     20005 |
|        1 |     20005 |
|        1 |     20006 |
|      100 |     20007 |
|       50 |     20008 |
|        1 |     20009 |
|        1 |     20009 |
|        1 |     20009 |
|        1 |     20009 |
+----------+-----------+
11 rows in set (0.00 sec)

    注：

    sum()函式忽略列值為NULl的行；

    聚集不同值：

    計算不同的值需要指定DISTINCT引數；

    mysql> select avg(distinct prod_price ) as avg_price from products where vend_id =1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.00 sec)

    作用的函式：count(),但是不能用於count(*)

    distinct()必須用於列名，不能用於計算或者表示式；

    組合聚集函式：

    mysql> select count(*) as num_items,min(prod_price ) as price_min,max(prod_price ) as price_max
    -> ,avg(prod_price ) as price_avg from products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)

相關文章