MySQL講義

第一部分：關聯式資料庫

壹、基本關係

  1. 資料庫系統(如MySQL、MSSQL、Excel)，包含數個資料庫。
  2. 一個資料庫包含數個資料表(table)。實體上，一個資料庫是一個目錄(資料夾)，一張資料表是一個檔案。
  3. 每張資料表由「欄」(column)及「筆」(row,record,tuple)構成。
  4. 每一筆資料的每欄，都放入「值」(value)。
  5. 資料表中用來辨識筆的欄位叫「鍵」(key,primary key)，鍵值也可以由許多欄組合而成，每一筆的鍵值必須「唯一」，如姓名不是好的鍵值，身份證字號是好的鍵值。
  6. 外鍵(foreign key)，例如有一張 person 的資料表以 pid 為每個人的「唯一識別欄」，而另一張 mail 的資料表，有一欄也叫 pid ，用來表示 mail 是哪一個人的。
    • 將資料表裡的某一欄(mail.pid)，參照到另一個資料表的欄位(person.pid)。 外鍵參照的主鍵，也被稱為父鍵(parent key)，此鍵所在的的資料表，又稱為父資料表(parent table)。
    • 外鍵可以與它參照的主鍵名稱不同。
    • 外鍵的功能在於確認甲資料表裡的紀錄與乙資料表能夠對應。通過外鍵可以讓資料庫自己來保證資料的完整性和一致性。
    • 外鍵值可以是NULL，即使主鍵值不可為NULL。外鍵值為NULL，表示在父資料表裡沒有相符的主鍵。
    • 外鍵值不需要獨一無二，通常都沒有唯一性。即一個人有多個mail，所以在mail.pid中有多筆欄值相同。
    • 可透過限制條件(constraint)，管制父資料表中父鍵的刪除與更新異動，以確保外鍵參照的正確性。
    • MySQL 內設定 foreign key 需滿足的限制：
      1. 該資料表類型需設為InnoDB
      2. 要建立外鍵的欄位須建立索引(index)
      3. 外鍵的資料型態需與父鍵相同
    • MySQL 內限制條件(constraint)的類別：
      1. RESTRICT(限制)相當於no action：拒絕父表 update 或 delete 有外鍵參照到的欄值及記錄。預設值。
      2. CASCADE(關聯)：父鍵異動時，外鍵表中外鍵欄位值會被更新，或所在的列會被刪除。
      3. set null：被父表的外鍵參照欄位被 update,delete 時，子表的外鍵欄值設為null。
建立外鍵的指令：
ALTER TABLE 子表名
ADD [CONSTRAINT 外鍵名] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES 父表名 (index_col_name, ...)
[ON DELETE {CASCADE | SET Null | NO ACTION | RESTRICT}]
[ON UPDATE {CASCADE | SET Null | NO ACTION | RESTRICT}]
建立多欄外鍵的例子：
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
建立單欄外鍵的例子：
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id),
查看外鍵名的指令：
SHOW CREATE TABLE 子表名;
刪除外鍵的指令：
alter table drop foreign key '外鍵名';
如果創外鍵時沒有使用「CONSTRAINT 外鍵名」命名，刪外鍵時會出錯，但在提示出錯的資訊中會顯示foreign key的系統預設外鍵名->用它去刪除外鍵。

貳、資料庫正規化的例子

好的設計
  1. 「客戶table」的各欄：客戶編號、姓名、地址、電話。
  2. 「書價table」的各欄：ISBN、書名、書價。
  3. 「訂單table」的各欄：訂單編號、客戶編號、ISBN、訂購冊數。
  4. 外部鍵：對「訂單table」來說，客戶編號是外部鍵。
爛的設計
  1. 「客戶訂單」：客戶姓名、地址、電話、訂購書名、書價、訂購冊數、訂單編號 。
分析檢討
  1. 一個人會訂好幾次書，每一筆都要輸入姓名、地址、電話，如果改電話了，要每一筆都叫出來改。而且也容易輸錯，還浪費儲存空間。
  2. 一本書可能被很多人訂購，每一筆都要輸入書名、書價，會輸錯，也浪費空間。

第二部分：MySQL

MySQL 5.1參考手冊的正體中文翻譯在「台灣PHP users group」的 http://twpug.net/docs/mysql-5.1/ 上面。

零、基本說明

一、名詞說明

  1. SQL:Structured Query Language。
  2. RDBMS:Relational database managerment system。
  3. ODBC:Open Database Connectivity Standard。

二、檔案分布

(一)CentOS

  1. 二元執行檔是/usr/libexec/mysqld
  2. 諸命令稿在/usr/bin之下。/usr/bin/mysqld_safe 是命令稿，叫用/usr/libexec/mysqld
  3. 啟動命令稿是/etc/rc.d/init.d/mysqld 叫用/usr/bin/mysqld_safe、/etc/my.cnf、/var/run/mysqld/mysqld.pid(其中記pid是3047)
    service mysqld start是執行/etc/rc.d/init.d/mysqld，尤其是其中的下列這一行： 
    /usr/bin/mysqld_safe  --defaults-file=/etc/my.cnf --pid-file="$mypidfile"
  4. 設定檔在/etc/my.cnf
  5. 諸資料庫在/var/lib/mysql/之下。

三、基本操作

  1. MySQL中;代表執行，Enter不代表加一行，要加;再按Enter才會執行。
  2. 保留字不分大小寫：指令、運算子、函數、常數。庫、表、欄名分不分大小寫同作業系統。

四、初始較調

(一)在linux下操作

  1. telnet ip
  2. 以admin登入；無法直接以root登入，如要以root登入，以su -，再以管理員密碼登入。
  3. 在MySQL剛架好時，有兩個最高權限者root，密碼設為空值，兩個空帳號無權限，此時要趕快給root一個密碼：
    方法一：shell>mysqladmin -u root password 新通行碼
    此法只改了host為localhost那個root，host為主機名稱那個root密碼仍為空值。 方法二：此法兩個root都會改到。
    • shell>mysql -u root mysql
    • mysql>update user set password=password('新通行碼') where user='root';
    • mysql>flush privileges;
    此初始最高權限者，就叫root，不能叫其他名字，如towhite之類。
  4. 以後可以用「shell>mysqladmin -u 使用者 password 新通行碼」來改密碼，不過系統會問原密碼，答對才幫你改。
  5. 登入MySQL：mysql -h hostname -u 使用者 -p
  6. 登入MySQL並取用資料庫：mysql 資料庫名 -h hostname -u 使用者 -p
  7. 登入MySQL並執行命令稿：mysql -h hostname -u 使用者 -p < 命令稿檔名
  8. help;(\h)：列出常用命令。
  9. exit;(\e)或quit;(\q)：離開MySQL。

(二)在Windows下操作

  1. 在nt類的OS安裝，可成為服務。可以用「net start mysql」指令啟動，用「net stop mysql」指令停止。
  2. php4 連 mysql 4.1以上時會發生以下錯誤：「#1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client」
    參考網頁：http://www.kenming.idv.tw/index.php?m=20041215、http://pank.org/blog/archives/000878.html
    解決的辦法：「SET PASSWORD FOR 'root'@'localhost' = OLD_PASSWORD('Sletamup');」「FLUSH PRIVILEGES;」
  3. 松山家：「d:\Programs\Copy\Abyss Web Server\Abyssws.exe」「c:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld.exe」
  4. 在 win98 下使用 mysql ，在任何 > = i386的Intel處理器上，應該都能使用兩個不同的MySQL伺服器：
    mysqld用完整調試和自動儲存器分配檢查編譯
    mysqld-opt對Pentium 處理器最佳化。
    你應該啟動一個MSDOS視窗鍵入以下指令啟動mysqld伺服器： 
    C:\mysql\bin\mysqld
    要關閉MySQL伺服器，請執行： 
    C:\mysql\bin\mysqladmin -u root shutdown
    注意：Win95/Win98不支援命名管道的創建。在Win95/Win98上，你只能使用命名管道連接一個NT伺服器上的遠端MySQL。

五、不透過 PHP 直接操作

(一)在 OS 下操作

  1. net start mysql：啟動 MySQL
  2. mysqlshow [-h ipAddress] -u user_name -p 或 mysqlshow -u user_name@ipAddress -p：列出所有資料庫
  3. mysqlshow -u user_name -p db_name：列出該 database_name 所有資料表
  4. mysqlshow -u user_name -p db_name table_name：列出該 database_name 裡 table_name 資料表裡的欄位
  5. mysqlshow -u user_name -p db_name table_name field_name：列出該 database_name 裡 table_name 的 field_name 的欄位資訊
  6. mysqladmin password 'new_password'：更改目前系統登入使用者的密碼
  7. mysql db_name < sql_filename.sql：於資料庫中導入執行 sql 腳本檔
  8. mysql [-h ipAddress] -u user_name -ppassword 或 mysql [-h ipAddress] -u user_name -p：進入 mysql 介面
  9. mysql db_name [-h ipAddress] -u user_name -ppassword < sql_filename.sql：於資料庫中導入執行 sql 腳本檔，但不進入 mysql 介面
  10. mysql -u user_name -ppassword -e 'SQL查詢指令'：以非互動模式使用 mysql 指令，不加「;」，參數 -e 或 --execute皆可。如：「mysql -u root -pxxxxxxxx -e 'select version()'」
    可用在有大量查詢結果時，
    可配合重導至文字檔。

(二)在 MySQL 互動模式下操作

請見「基本SQL命令」，補充以下命令：
  1. select version();：查出版本
  2. select … \G;：將每筆資料分開呈現。
  3. prompt \u@\h \d>：修改提示符號，\u代表帳號、\h代表主機，\d 表資料庫。
  4. tee 檔名：將所有動作及其結果紀錄在指定的檔案，直到mysql結束，或是接到 notee 指令才會停止紀錄。
  5. \T 檔名：同上。
  6. notee：停止紀錄
  7. \t：：同上。
  8. tee：接續之前的紀錄，再接續紀錄。須在之前指定過外部紀錄檔才行。
  9. tee 也可以在 my.cnf 中的[client]加入「tee =/tmp/client_mysql.log」指示。
  10. tee 也可以在 OS 中以「mysql -uroot --tee=/tmp/client_mysql.log」在進入互動模式前就指示好。
  11. mysql 共有四種輸出格式參數：--table 或 -t 、--batch 或 -B 、--html 或 -H、--xml 或 -X ，在 OS 中以「mysql -參數 …」在進入互動模式前就要指示好，預設為 -t。



  12. help 指令名稱：如「help select」，會送出指令的語法說明。
  13. prompt、tee、notee、\T、\t、help、exit、quit 等非 SQL 查詢指令，使用時行尾均不必加「;」。
  14. SQL查詢指令，使用時行尾加「;」，代表執行。
  15. use 資料庫名;：跳入資料庫
  16. source 檔名.sql;：載入腳本檔
  17. SELECT DATABASE();：列出目前預設的資料庫名稱
  18. SHOW DATABASES LIKE 'my%';：列出所有資料庫名稱為 my 開頭的
  19. SHOW TABLES FROM db_name [LIKE ...];：列出該資料庫所有資料表名稱
  20. 列出該資料表所有欄位名稱：
    • SHOW COLUMNS FROM table_name [LIKE ...];
    • SHOW COLUMNS FROM table_name FROM db_name [LIKE ...];
    • SHOW FIELDS FROM table_name [LIKE ...];
    • DESCRIBE table_name ;
    • EXPLAIN table_name ;
    • select column_name FROM information_schema.columns where TABLE_NAME='table_name';
  21. 列出該資料表所有索引資訊：
    • SHOW INDEX FROM table_name [LIKE ...];
    • SHOW INDEX FROM table_name FROM db_name [LIKE ...];
    • SHOW KEY FROM table_name [LIKE ...];
  22. 列出資料表的相關資訊：
    • SHOW TABLE STATUS;
    • SHOW TABLE STATUS FROM db_name [LIKE ...];
  23. SHOW VARIABLES [LIKE ...];：顯示 MySQL 相關參數設定
  24. SHOW VARIABLES LIKE '%character%' ;：顯示資料庫語系設定資訊
  25. SHOW PROCESSLIST;：列出與 MySQL 連線的 threads 狀態
  26. SHOW STATUS;：列出與 MySQL 目前的狀態
  27. 鎖定資料表：當進行資料表檢查或修補時，可確保資料表的安全。READ：唯讀狀態；WRITE：無法寫入也無法讀取。
    • LOCK TABLE table_name READ;：鎖定資料表
    • FLUSH TABLES;：刷新權限設定
    • UNLOCK TABLE;：資料表解除鎖定

六、救援

(一)修復資料表

  1. 方法1，進MySQL使用repair指令：
    mysql -uroot -p密碼
use 資料庫名;
repair table 資料表名;
  2. 方法2，在MySQL外用myisamchk指令，對資料庫中所有的資料表進行檢查：
    /etc/init.d/mysqld stop
/usr/sbin/myisamchk -f /var/lib/mysql/資料庫名稱/*.MYI
/etc/init.d/mysqld start

(二)忘掉密碼重設密碼

  1. 先停止 MySQL deamon
    #/etc/rc.d/init.d/mysqld stop
  2. 跳過授權表重新啟動 MySQL(注意此時啟動的pid不是正確的pid 3047，以後會無法kill)
    #/usr/bin/mysqld --skip-grant-tables
  3. 無帳號登入MySQL
    #mysql
  4. 修改密碼
    mysql>use mysql ;
mysql>update user set password=password('new_password') where user = 'root' ;
mysql>FLUSH PRIVILEGES;
mysql>exit;
  5. 重新啟動 MySQL
    #/etc/init.d/mysqld restart
  6. 此時由mysqld_safe所啟動的mysqld行程還在跑，可由「service mysqld status」得知，用「/usr/bin/mysqld_safe stop」加上「kill xxxx」都無法把mysqld行程殺掉，要重開機才能殺掉。

(三)4.1前後的密碼驗證

  1. 一般使用雜湊(Hash)函數來編製密碼，md5就是一種雜湊函數。雜湊函數雖不可逆，但可以事先算好雜湊表(Rainbow Hash Tables)來比對密碼。所以可以用諸如hash=md5('deliciously-salty-'+password)的方法編製密碼，這樣就無法用Rainbow Tables來攻擊你了。
  2. MySQL使用的雜湊函數是password()，此函式在4.1.1之後改變(不包括4.1.0)，但大家還是簡稱4.1版後改變。舊函式產生16字元(字首無*號)，新函式產生41字元(含字首的*)。
  3. MySQL4.1以上還是保留舊版的雜湊函數但改名為old_password()。
  4. 只要MySQL的客戶端版本大於4.1以上(不是伺服器版本大於4.1)，則在my.cnf中的[mysqld]段中，不管「old_passwords=」設為0或1，user資料表中，不管放新舊版密碼，連線驗證都會過。
    當MySQL的伺服器版本大於4.1而客戶端版本小於4.1，存的又是舊版密碼，驗證會過；但進去之後執行 PASSWORD()、GRANT 及 SET PASSWORD 命令時，系統會用新函式演算，造成此帳號無法再以舊版的客戶端軟體登入。要避免此情況發生，可在此MySQL中使用 OLD_PASSWORD() 函數代替 PASSWORD() 函數。

七、基本SQL命令

小括號( )是指令的一部分；中括號[ ]代表可選用的選項，指令中不一定要下這些指示。

  1. show databases;：秀出現有的資料庫。
  2. create database 資料庫名;：建資料庫。
  3. use 資料庫名;：取用資料庫
  4. show tables;：秀出現用資料庫有那些資料表。
  5. create table 表格名 (欄名 資料型別(欄寬) 預設值,name char(10),);：建資料表。
  6. create table 表格名 like 既有表格名：使用既有表格的格式創建新表
    接著可用「insert 新表 select * from 舊表;」複製資料。
  7. create table 表格名 as select col1,col2… from 既有表格名 definition only：使用既有表格部分欄位創建新表。如果省略 definition only ，則結構和資料都一起複製到新表。新表沿用預設的 Engine、語系編碼，而不是舊表的。
  8. describe 表格名;：秀表格結構。
  9. insert into 表格名 values ('值1','值2',…);：循欄序加一筆資料。
  10. insert into 表格名 values ('值1','值2',…),('值1','值2',…),('值1','值2',…)…;：循欄序加很多筆資料。
  11. insert into 表格名 (欄名1,欄名2,…) values ('值1','值2',…);：只對指定數欄填值，加一筆資料。
  12. insert into 表格名 (欄名1,欄名2,…) select (欄名a,欄名b,…) from…;：批次插入表格內容，select子句中可以含有WHERE、GROUP BY、HAVING、表格連接及別名等等。
  13. insert into 表格名 select * from 另一表格;：表格結構相同，直接照抄內容。
  14. insert into 表格名 set 欄名1='值1',欄名2='值2',…;：只對指定數欄填值，加一筆資料。
  15. REPLACE 的用法與 INSERT INTO 非常接近，不同的地方是：
    在 INSERT INTO 中如果你插入的欄位有唯一性質的索引，例如：PRIMARY KEY, AUTO_INCREMENT, UNIQUE，如果插入資料時真的發生唯一性質的欄位資料重複，那麼這個 INSERT INTO 命令會被忽略而不執行，但是在 REPLACE 中的新資料卻會把重複資料中的舊資料蓋掉。因此 REPLACE 比 INSERT INTO 更具強制性。
  16. 在insert、update命令中，插入欄值如果是123,456等數值，不論欄位的資料型態為何，皆可不加引號而插入；但如果插入之欄值不是數值，則一定要加引號。
  17. select 欄名1,欄名2,… from 表格名;：找出指定欄位，全部筆數。
  18. select DISTINCT 欄名1,欄名2,… from 表格名;：找出指定欄位，全部筆數，但所有欄值都相等的諸筆合併為一筆。DISTINCT翻譯為「不同」。
  19. select * from 表格名;：找出全部欄位，全部筆數。
  20. select * from 表格名 where 條件(如name='丁志仁');：找出全部欄位，合條件的筆數。
  21. update 表格名 set 欄名=欄值,欄名=欄值… [where 條件][limit 範圍];：將合條件合範圍的所有筆數，指定欄都換成指定欄值，欄值可以為公式。
    如：「update 表格名 set 欄名=TRIM(BOTH '\r\n' FROM 欄名)」去掉頭尾的空白行。
    「update 表格名 set 欄名=concat(字串1,char(10),char(13),字串2) where tid = xxxxx」插入換行
    「update 表格名 set 欄名=replace(欄名,'abc','char(10)abc') where tid = xxxxx」插入換行
  22. insert與update之值可以是運算，該運算可包含現有欄值(以欄名表示)。
  23. alter table 表格名 add 欄名及欄位描述;：對表格加欄。
  24. alter table 表格名 modify 欄名及欄位描述;：對表格改欄之屬性、欄寬等欄位描述。
  25. delete from 表格名 where 條件 limit 幾筆;：刪合條件的幾筆。
  26. 不可用delete from 表格名 where 條件 limit 自第幾筆始,以下幾筆;：因在delete指令下，limit是指幾筆，不是指範圍。
  27. 4.0版之後開始支援刪多表，其指令類似select，只是以「delete 諸表」取代「select 諸欄」，可以使用left join、on、where組合。
  28. drop table [if exists] 表格名;：刪除資料表。如加「if exists」則找到則刪，避免出現錯誤訊息。
  29. truncate table 表格名;：清空資料表。
  30. drop database [if exists] 資料庫名;：刪除資料庫。
  31. grant all on *.* to 使用者 identified by '通行碼';：給所有權力，在所有資料庫所有表格，給某使用者以某password辨識時。
  32. load data local infile '路徑及檔名' into table 表格名 [fields [欄名1,欄名2…] [terminated by ','] [ENCLOSED BY '"'] [ESCAPED BY '\\']] [LINES TERMINATED BY '\r\n'];：從檔案吃資料進表格。省略欄名則循序給；省略分隔符號則以TAB分隔資料。
    1. 進mysql的預設路徑在/var/lib/mysql
    2. use 資料庫;後預設路徑在/var/lib/mysql/資料庫。但要load data infile一定要use 資料庫。
    3. 省不省略「local」，都可以用絕對路徑(從/寫起)、相對路徑(用.或..)、預設路徑(沒指定路徑)。
    4. 檔名一定要外包引號。
    5. 「fields」管欄名列、terminated by、ENCLOSED BY、ESCAPED BY，四個操作項
    6. 省略「fields 欄名1,欄名2…」等欄名列，循序抓欄值，抓到欄位數完或數據檔中的數據抓完，一定吃。和insert不同。
    7. 省略「terminated by ','」，用TAB(\t)分隔欄值，兩個TAB連在一起，兩個TAB之間還是要算一個空欄值。
    8. 省略「ENCLOSED BY '"'」，數據檔中的"被當作欄值存起來。如下此令，要看檔中數據前後有無成對的"包起來，有就去掉；如果沒有或引號不是前後成對，不去引號，但認定的欄位就很長，長到把欄位分隔符號包進來，導致把好幾欄當一欄。數據也可以改用其他字符來包欄值。
    9. 下「OPTIONALLY」，不知何意，本版本也不支援。
    10. 省略「ESCAPED BY '\\'」，認\為特殊字元，會去反斜。可指定反斜以外的字元為跳脫字元。用「ESCAPED BY ''」時，去反斜的功能被抑制，恰可解決許功蓋問題。
    11. 省略「LINES TERMINATED BY」，以\n(換行)為換筆，Windows下的換行「\r\n」，\r會被吃進欄位值。所以要指定「LINES TERMINATED BY '\r\n'」才會正常。
  33. OPTIMIZE TABLE 表名;：最佳化資料表。
  34. flush privileges;：手動讓權限更改生效。

八、轉義字元

轉義字元意義備註
\0ASCII 字元 0會使欄值無法顯示
\n換行符號
\t一個 TAB 字元
\r換行字元
\b倒退字元會使欄值無法顯示
\'單引號字元
\"雙引號字元
\\反斜線字元
\%百分比字元實測無效
\_底線字元實測無效

壹、權限

一、使用者權限

  1. select 資料表,欄位 ：允許使用者選出資料錄。
  2. insert 資料表,欄位 ：允許使用者選出資料錄。
  3. update 資料表,欄位 ：允許使用者修改資料錄。
  4. delete 資料表 ：允許使用者刪除資料錄。
  5. index 資料表 ：允許使用者對指定資料表建立或刪除索引。
  6. alter 資料表 ：允許使用者對指定資料表改變結構。
  7. create 資料庫,資料表：允許使用者建立資料庫或資料表。如在Grant中指定，那就只能建那個名字的資料庫或資料表；也就是要先Drop它。
  8. drop 資料庫,資料表：允許使用者刪除資料庫或資料表。

二、管理員權限

  1. reload ：允許管理員再讀入授權表，重新顯示權限、主機、記錄檔、資料表。
  2. shutdown：允許管理員關掉MySQL伺服器 。
  3. process ：允許管理員顯示或結束執行緒。
  4. file ：允許資料可以從檔案讀入資料表中，反之亦然。

三、其他權限

  1. all ：授予使用者和管理員的全部權限。
  2. usage：不授予任何權限。

四、授予權限

  1. grant 用逗點隔開的權限列表 [用逗點隔開的欄名列表] on 資料庫表 to 使用者 [identified by '通行碼'] [with grant option];
  2. 資料表庫：「*.*」代表所有的資料庫、資料表；「資料庫名.*」代表某資料庫中的全部資料表；「資料庫名.資料表名」代表某一張資料表；「資料表名」代表現用資料庫中的一張資料表。
  3. 使用者可以含主機名稱，也可以不含主機名稱。含主機名稱時「使用者@主機名」，籍此可管制來自某些主機的人能有那些權限。
  4. 使用者可以不含主機。不含主機名稱時mysql.user.host填入萬用字元「%」，以後要用所在的DN如www.jendo.org來連MySQL。
  5. 使用者可以用「使用者@localhost」，此時mysql.user.host填入「localhost」，以後要連MySQL時，-h可省略，也可用-h localhost。
  6. 通行碼由大小寫字母及非字母構成。
  7. 通行碼經password()函數加密，填入mysql.user.password。password()為一單向函數，無法由mysql.user.password反推通行碼；只能由通行碼經password()演算後，再與mysql.user.password之值比對。
  8. 有with grant option可以將權限再授予別人。

五、權限表格

mysql資料庫中有五張表，登錄著權限管理資訊：

(一)五權限表格的結構：

  1. user表格：各使用者對全局(所有資料庫)的權限。
    1. Host：char(60)，主機。
    2. User：char(16)，使用者。
    3. Password：char(16)，通行碼。
    4. 14欄，分別登錄select,insert,update,delete,create,drop,reload,shutdown,process,file,grant,references,index,alter等14種權限，每欄只有Y,N兩值。
    5. Host不同，User相同，視為兩個使用者，可以各有各的通行碼。
  2. db 表格：那些使用者對那些資料庫有那些權限。
    1. Host：char(60)，主機。
    2. Db ：char(32)，資料庫。
    3. User：char(16)，使用者。
    4. 10欄，分別登錄select,insert,update,delete,create,drop,grant,references,index,alter等10種權限，每欄只有Y,N兩值。
  3. host表格：從那些主機連過來時，對那些資料庫有那些權限。
    1. Host：char(60)，主機。
    2. Db ：char(32)，資料庫。
    3. 10欄，分別登錄select,insert,update,delete,create,drop,grant,references,index,alter等10種權限，每欄只有Y,N兩值。
  4. tables_priv：那些使用者對那些資料表有那些權限。
    1. Host：char(60)，主機。
    2. Db ：char(60)，資料庫。
    3. User：char(16)，使用者。
    4. Table_name：char(60)，資料表。
    5. Grantor ：char(77)，授權者。
    6. Timestamp：timestamp(14)，時間戳記。
    7. Table_priv：set，從select,insert,update,delete,create,drop,grant,references,index,alter十權限中，取若干種。
    8. column_priv：set，從select,insert,update,references四權限中取若干種。
  5. Columns_priv：那些使用者對那幾欄有那些權限。
    1. Host：char(60)，主機。
    2. Db ：char(60)，資料庫。
    3. User：char(16)，使用者。
    4. Table_name：char(60)，資料表。
    5. Column_name：char(60)，欄名。
    6. Timestamp：timestamp(14)，時間戳記。
    7. column_priv：set，從select,insert,update,references四權限中取若干種。
  6. func：。
    1. name：char(64)，BINARY
    2. ret ：tinyint(1)，預設0
    3. dl ：char(128)，
    4. type：enum('function', 'aggregate')，預設function

(二)驗證機制：

  1. 主機名稱可以使用萬用字元%，%代表所有主機，%.DN代表某網域上的所有主機。
  2. 使用者空白，代表所有使用者皆被允許。
  3. 通行碼空白，代表不需要密碼。
  4. 使用者送出請求時，系統先檢查全局權限(user表)，不足再去檢查db表和host表，再不足去檢查table_priv，再不足去檢查columns_priv表。

(三)讓手動更改生效：

手動更改權限表格，而不透過grant、revoke指令時，MySQL伺服器不會知道權限已改，可透過以下三種方式告知MySQL伺服器：

  1. 在MySQL中，下flush privileges;
  2. 在作業系統中，下mysqladmin flush-privileges
  3. 在作業系統中，下mysqladmin reload

六、授權原則

  1. 最低權限原則。沒必要的人不給。
  2. mysql資料庫，不授權給管理員以外的人。
  3. alter權限小心給，可以用以推翻權限系統。

七、撤回權限

  1. revoke 用逗點隔開的權限列表 [用逗點隔開的欄名列表] on 資料庫表 from 使用者;
  2. revoke grant option on 資料庫表 from 使用者;

貳、欄

一、欄的屬性

  1. 資料型別，見下段。
  2. null及not null：預設為null，未給欄值時，會給空值(null)。not null令欄位值不可為空值。
  3. auto_increment：加入新一筆時，若沒給值，則自動產生一個比此欄最大值還大的值。這種欄可以做主鍵。每張表只能有一個auto_increment欄位，此欄必須做為索引。
  4. primary key：主鍵，各筆之值必須唯一。會自動將其索引。
  5. primary key(欄名1,欄名2…)：結合數欄為主鍵。

二、資料型別

(一)整數型別

設定欄位長度無效。
  1. TINYINT ：1位元組，-127..128或0..255。
  2. SMALLINT ：2位元組，-32767..32768或0..65535。
  3. MEDIUMINT ：3位元組，-8388607..8377608或0..16777215。
  4. INT ：4位元組，
  5. BIGINT ：8位元組，
數值型別後可加屬性：
  1. unsigned ：無正負。
  2. signed ：有正負。
  3. 預設為有正負。
  4. zerofill ：必為unsigned。

(二)浮點數型別

  1. FLOAT(精密度)：如FLOAT(3)，即用3位元組表示一個數字，其小數位數為變動。
  2. FLOAT[(寬,小數位)]：即FLOAT(4)，卻能指定顯示寬度及小數位數。
  3. DOUBLE[(寬,小數位)]：即FLOAT(8)，卻能指定顯示寬度及小數位數。
  4. DECIMAL[(寬,小數位)]：以char型態儲存浮點數，儲存長度為寬加兩位。 
DECIMAL中寬與小數位對DECIMAL(寬,小數位)取值範圍的影響：
類型說明          取值範圍（MySQL < 3.23）     取值範圍（MySQL >= 3.23）
DECIMAL(1, 1)        -9.9 到 99.9                    -0.9 到 0.9
DECIMAL(2, 1)        -9.9 到 99.9                    -9.9 到 9.9
DECIMAL(4, 1)        -9.9 到 99.9                  -999.9 到 999.9
DECIMAL(5, 1)       -99.9 到 999.9                -9999.9 到 9999.9
DECIMAL(6, 1)      -999.9 到 9999.9              -99999.9 到 99999.9
DECIMAL(6, 2)      -99.99 到 999.99              -9999.99 到 9999.99
DECIMAL(6, 3)      -9.999 到 99.999              -999.999 到 999.999

在MySQL 3.23 及以後的版本中，DECIMAL(M, D) 的取值範圍等於早期版本中的DECIMAL(M + 1, D) 的取值範圍。

(三)日期時間型別

新版的DATETIME和TIMESTAMP格式已經一樣，長度都是19，值也都只接受now()或字串，差別在預設值設定而已。
  1. DATE ：YYYY-MM-DD。送入非時間會送入0000-00-00；
  2. TIME ：HH:MM:SS
  3. DATETIME ：YYYY-MM-DD HH:MM:SS，長度為19。可用now()函式，送入目前時間。格式和TIMESTAMP相同，但無法設定預設插入時送入目前時間，而TIMESTAMP欄可以。
  4. TIMESTAMP ：時間戳記，YYYY-MM-DD HH:MM:SS，長度為19。可用now()函式，送入目前時間。送入非時間會送入0；送入20060230會轉成20060302。now()和PHP的date("YmdHis")效果相當。預設值可為CURRENT_TIMESTAMP。
    新版已不能設各種長度的TIMESTAMP，舊版中的TIMESTAMP有以下各種不同長度：
    1. TIMESTAMP ：YYYYMMDDHHMMSS
    2. TIMESTAMP(14)：YYYYMMDDHHMMSS
    3. TIMESTAMP(12)：YYMMDDHHMMSS
    4. TIMESTAMP(10)：YYMMDDHHMM
    5. TIMESTAMP(8)：YYYYMMDD
    6. TIMESTAMP(6)：YYMMDD
    7. TIMESTAMP(4)：YYMM
    8. TIMESTAMP(2)：YY
  5. YEAR ：
    1. YEAR(2) ：70-69，1970-2069。
    2. YEAR(4) ：1901-2155。
※MySQL 5.1之後TIMESTAMP的欄位屬性(管編)與預設值(管插)各有兩種變化：

(四)字串型別

  1. CHAR(寬) ：固定長，不管有沒有用到給定長。
  2. VARCHAR ：可變長，依字串長加一位元組。
  3. BLOB ：二元資料，binary large objects，可存影音。65535字元組以內。2的16次方減一。
  4. TEXT ：文章，65535位元組以內。2的16次方減一。
  5. TINYBLOB ：255位元組以內。2的8次方減一。
  6. TINYTEXT ：255位元組以內。2的8次方減一。
  7. MEDIUMBLOB ：166777215位元組以內。2的24次方減一。
  8. MEDIUMTEXT ：166777215位元組以內。2的24次方減一。
  9. LONGBLOB ：2的32次方減一個位元組以內。
  10. LONGTEXT ：2的32次方減一個位元組以內。
字串型別後可加屬性：
  1. 空白 ：分大小寫。
  2. binary：二元，分大小寫。
  3. 預設為空白。

(五)特別型別

  1. ENUM(值1,值2…) ：從列出來的值選一個，或null。最多可列65535個值。
  2. SET(值1,值2…) ：從列出來的值選一組，或null。最多可列64個值。

三、建立欄

  1. create table 表格名 (欄1 資料型別(欄寬) [null|not null] [auto_increment] [primary key],欄2…,欄3…,[primary key(欄A,欄B…)]);：primary key設為某欄時，置於該欄說明；primary key由數欄共構時，置於各欄說明之後，並以","與各欄說明隔開。

參、辨識器

一、辨識器的名稱

  1. 資料庫：64字元，分不分大小寫同作業系統，允許的字元同作業系統目錄名但不可含/及.。
  2. 資料表：64字元，分不分大小寫同作業系統，允許的字元同作業系統檔案名但不可含/及.。
  3. 欄名：64字元，不分大小寫，允許所有字元。
  4. 別名：255字元，不分大小寫，允許所有字元。
  5. 不能用ASCII(0)及ASCII(255)。

二、別名

  1. 可以為「表」取別名，可以為「欄」取別名。
  2. 用as，表別名一樣不能含/及.。
  3. select 別名.欄名 from 表1 as 別名1,表2 as 別名2… where 別名.欄名=…;
  4. 「表」把自己當成另一張表，自己和自己進行結合時，一定要用別名。
  5. select 別名1.name,別名2.name,別名1.city from 表 as 別名1,表 as 別名2 where 別名1.city=別名2.city and 別名1.name != 別名2.name;：name,city是該表的欄，此命令會找出本表中所有同城市的人。

肆、設定、系統變數、會期變數連結

一、設定

設定檔為 my.cnf 。其中一般不會寫資料庫連結上限 Max_connections ，連結上限使用預設值 100 ，這也是 Apache 連結上限設定 MaxKeepAliveRequests 的預設值(設定於httpd.conf中)。一般性內容如下：

[mysqld]	# 設定行程
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1	# 預設使用舊密碼格式，以與 mysql 3.x 的客戶相容
     
[mysql.server]	# 設定伺服器
user=mysql
basedir=/var/lib

[mysqld_safe]	# 設定資安
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

二、系統變數

mysqld伺服器維護兩種系統變數。全域變數影響伺服器整體操作。會期變數影響具體用戶端連接的操作。

當伺服器啟動時，它將一群系統運作要用到的變數，以預設值化為全域變數。這些變數的值可以在配置檔中或在命令行中進行更改。伺服器啟動後，通過連接伺服器並執行「SET GLOBAL var_name」語句，可以動態更改這些全域變數。但想要更改全域變數，必須具有SUPER權限。

全域變數值任何用戶端都可以看見。然而，它只影響更改後連接的客戶的從該全域變數初始化的相應會期變數。不影響之前已經連接的用戶端的會期變數(即使用戶端執行「SET GLOBAL」語句也不影響)。

當然重新啟動 MySQL ，全域變數會回到預設值及配置檔中的設定。

伺服器還為每個連接的用戶端維護一組一組會期變數。在連接時使用相應全域變數的現有值對用戶端的會期變數進行初始化。對於動態的會期變數，用戶端可以通過「SET SESSION var_name」語句更改它們。設置會期變數不需要特殊權限，但用戶端只能更改自己的會期變數，而不能更改其它用戶端的會期變數。

LOCAL是SESSION的同義詞。如果設置變數時不指定GLOBAL、SESSION或者LOCAL，預設使用SESSION。

MySQL5.1手冊 5.3.3.1 節中已列出所有可設定的「動態系統變數」，本人已將其整合在本文附件中。

select系統變數，變數名前一定要加「@@」，有「SELECT @@global.變數名」「SELECT @@session.變數名」「SELECT @@變數名」。

set 系統變數值時，變數名前有不加前綴的「SET GLOBAL 變數名=值」「SET SESSION 變數名=值」「SET 變數名=值」；有加前綴「@@」的「SET @@global.變數名=值」「SET @@session.變數名=值」。

@@global和@@session可以視為特別的二維陣列，分別代表全域和會期的系統變數，其諸欄為各系統變數，可以select可以set。

(一)取變數值及設定變數值指令：

  1. SHOW GLOBAL VARIABLES;：取全部全域系統變數值
  2. SHOW GLOBAL VARIABLES like '變數名';：取全域系統變數值，傳回兩欄，欄名分別為「Variable_name」及「Value」
  3. SELECT @@global.變數名;：取全域系統變數值，傳回單筆一欄，欄名為變數名，筆值為變數值。
  4. SET GLOBAL 變數名=值;：設為全域系統變數
  5. SET @@global.變數名=值;：設為全域系統變數
  6. SHOW SESSION VARIABLES;：取全部會期系統變數值
  7. SHOW SESSION VARIABLES like '變數名';：取會期系統變數值，傳回兩欄，欄名分別為「Variable_name」及「Value」
  8. SELECT @@session.變數名;：取會期系統變數值，傳回單筆一欄。
  9. SET SESSION 變數名=值;：設為會期系統變數
  10. SET @@session.變數名=值;：設為會期系統變數
  11. SHOW VARIABLES;：取全部會期系統變數值
  12. SELECT @@變數名;：如果會期系統變數存在取會期系統變數值，否則返回全域系統變數值。傳回單筆一欄，欄名為「@@變數名」。
  13. SET 變數名=值;：設為會期系統變數

附錄是系統變數的一覽表。

(二)運用系統變數值，調整資料的字集編碼：

假設資料庫整體設定採預設值，而JJ資料庫連線校對採utf8_general_ci，選用資料庫的前後變化如下：

系統變數character_set_databasecollation_database
use JJ前latin1 latin1_swedish_ci
use JJ後utf8 utf8_general_ci

而「set names utf8」前後變化如下：

系統變數character_set_clientcharacter_set_connectioncharacter_set_resultscollation_connectioncharacter_set_system
use JJ前latin1 latin1 latin1 latin1_swedish_ci utf8
use JJ後utf8 utf8 utf8 utf8_general_ci utf8

在程式撰寫上一般是去偵測資料庫的「character_set_database」用以決定隨後的「set names」要採用那一種揙碼。對資料解讀來說：欄位的字集設定優先於資料表的字集設定，再優先於資料庫的字集設定，再優先於整體的字集設定。

  1. 敘述中的每個字串，可以指定該字串自己的字集和校對，語法為「[_字集名]'string' [COLLATE 校對名]」，如：
    SELECT 'string';
SELECT _latin1'string';
SELECT _latin1'string' COLLATE latin1_danish_ci;
  2. 客→character_set_client→character_set_connection→字串比較：MySQL
    Server
    戶←←←character_set_results←←←欄的校對設定或
    collation_connection
    上述三組「客戶」和「伺服器」之間的字集變數，彼此間編碼要維持協調，才不會出現錯誤和亂碼
  3. 「SET NAMES '某字集'」相當於「SET character_set_client = 某字集;SET character_set_results = 某字集;SET character_set_connection = 某字集;」。
  4. 客戶端送給伺服器的敘述，如字串中有字集聲明依其字集聲明，其他的字串轉成character_set_connection。
  5. 每個字元型別的欄(CHAR、VARCHAR或TEXT)有其專屬的字集和校對，每個字串有專屬的字集和較對。
  6. 敘述中字串的比較，如果是「非欄位」比較，使用collation_connection(連線校對)的比對模式；如果欄值比較，使用各欄位自己的校對(collation)設定。
  7. character_set_results指定了伺服器送回查詢結果時用的字集，包括了欄名和欄值。
  8. 如果你使用mysql客戶端的自動重連(不推薦使用)功能，最好用charset命令，而不是SET NAMES。例如：「charset utf8」。在斷開後自動重連時，charset命令會發出了一個SET NAMES語句，並且修改預設字集。
  9. 建資料庫時沒有指定字集(character_set_database)和校對(collation_database)，則使用整體設定的字集(character_set_server)和校對(collation_server)為預設值；建資料表時沒有指定字集和校對，那麼使用資料庫的字集和校對；建欄位時沒有指定字集和校對，那麼使用資料表的字集和校對。

(三)一些可以額外設定的系統變數：

對以下各系統變數進行設定時，前面要加「set」動詞。

  1. PASSWORD = PASSWORD('some password')：設定當前用戶的密碼。任何非匿名的用戶能改變他自己的密碼！
  2. PASSWORD FOR user = PASSWORD('some password')：設定當前伺服器上一個特定用戶的密碼。只有可以存取mysql資料庫的用戶能這樣做。用戶應該以user@hostname格式給出，這裡user和hostname完全與他們列在mysql.user資料表的User和Host欄值一樣。例如，如果你有一筆其User和Host欄值是'bob'和'%.loc.gov'，指令須寫成：「SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("newpass");」或「UPDATE mysql.user SET password=PASSWORD("newpass") where user="bob' and host="%.loc.gov";」
  3. SQL_AUTO_IS_NULL = 0 | 1：如果設定為1(預設)，那麼對於一個具有一個自動加1欄的表，用下列條件能找出最後插入的行：WHERE auto_increment_column IS NULL。一些 ODBC 程式會這樣用。
  4. SQL_BIG_TABLES = 0 | 1：如果設定為1，所有臨時表存在在磁碟上而非記憶體中。這會慢一些，但是對需要大的臨時表的大SELECT操作，你將不會得到The table tbl_name is full的錯誤。對於一個新連接，預設值是0(即使用記憶體中的臨時表)。
  5. SQL_BIG_SELECTS = 0 | 1：如果設定為0，MySQL將放棄一個搞太久的SELECT。當WHERE語句不妥當時，這樣設很有用。所謂大的查詢是指檢驗多於max_join_size行的SELECT。對一個新連接，預設值是1(它將允許所有SELECT語句)。
  6. SQL_LOW_PRIORITY_UPDATES = 0 | 1：如果設定為1，所有INSERT、UPDATE、DELETE和LOCK TABLE WRITE語句，要等到受影響的表上沒有未解決的SELECT或LOCK TABLE READ時，才會執行。
  7. SQL_SELECT_LIMIT = value | DEFAULT：從SELECT語句返回的的最大筆數。如果一個SELECT有一個LIMIT子句，LIMIT優先與SQL_SELECT_LIMIT值。對一個新連接，預設值是「無限」。如果你改變了限制，預設值能用SQL_SELECT_LIMIT的一個DEFAULT值恢複。
  8. SQL_LOG_OFF = 0 | 1：如果設定為1，且客戶有process權限，則不將該客戶記載到標準日誌檔中。這不影響更新日誌檔！
  9. SQL_LOG_UPDATE = 0 | 1：如果設定為0，且客戶有process權限，則不將該客戶記載到更新日誌檔中。這不影響標準日誌檔！
  10. TIMESTAMP = timestamp_value | DEFAULT：為該客戶設定時間。如果你使用更新日誌恢復行，這被用來得到原來的時間標記。
  11. LAST_INSERT_ID = 整數：設定從LAST_INSERT_ID()返回的值。當你在更新一個表的命令中使用LAST_INSERT_ID()時，它存儲在更新日誌中。
  12. INSERT_ID = 整數：設定當插入一個AUTO_INCREMENT值時，由INSERT命令使用的值。這主要與更新日誌一起使用。

三、用戶變數：非系統的會期變數

在一次連結中設定一組會期變數值然後在以後引用它；這樣可以將值從一個語句傳遞到另一個語句。一個客戶端定義的變數不能被其它客戶端看到或使用。當客戶端退出時，該客戶端連接的所有變數將自動釋放。

用戶變數的形式為@變數名，其中變數名可以使用當前字元集的文字字元、數字字元、『.』、『_』和『$』組成，不分大小寫。預設字元集是cp1252 (Latin1)。可以用mysqld的--default-character-set選項更改字元集。

設置用戶變數的方法是執行SET語句：「SET @變數名 = 演算式 [, @變數名 = 演算式] ...」。對於SET，可以使用=或:=作為派值符號。派給每個變數的演算式其算出值可以為整數、實數、字串或者NULL。如果使用沒有初始化的變數，其值是NULL。

也可以用其他語句代替SET來為會期變數派一個值。在這種情況下，派值符號必須為:=而不能用=，因為在非SET語句中=被視為一個比較操作符號，如： 

SET @t1=0, @t2=0, @t3=0;
SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
傳回四欄 
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

另一個表現用戶變數優點的例子： 

SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
傳回最高價和最低價兩筆： 
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

四、局部變數

局部變數不加前綴「@」，命名規則同用戶變數，但有效範圍比用戶變數更小，只在其所在的「BEGIN ... END」區塊內。宣告指令為「DECLARE var_name[,...] type [DEFAULT value]」；設值指令為「SET var_name = expr [, var_name = expr] ...」；由欄值派入變數值為「SELECT col_name[,...] INTO var_name[,...] table_expr」；取變數值為「SELECT 變數名」。例如：

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

從t1表中拿出id,data的欄值塞入x,y變數中。

局部變數名不能和欄名一樣。如果SELECT ... INTO ... 中參考到一個欄名，同時也參考到一個與欄名相同名字的局部變數，MySQL會把參考解釋為一個局部變數的名字，不會解釋成欄名。

伍、索引

一、簡介

各家資料庫的索引實作並不相同，所以如果要讓SQL指令跨資料庫，最好避免資料庫的邏輯操作依賴索引。所幸，在大多數的資料操作指令中，索引只影響速度，不影響指令可否執行。除了unique類的索引以外，這一點後面會有示例講解。使用索引會加快蒐尋，但會拖慢增、改、刪資料。

在MySQL中索引有以下限制：

  1. 索引附屬於資料表，不支援跨表索引。
  2. 每張資料表只能有一個 primary key 索引，其索引名為「PRIMARY」。
  3. 一個索引最多可以包括15欄。
  4. 可以在建資料表「create table …」時，改資料表時「alter table 表名 add …」時建立，也可以用「create index …」單獨建立。上述指令中指定欄的索引長度，指的是字元數，utf8字元為3byte。
  5. 索引由那些欄如何組構是不能修改的，只能刪除重建。
  6. BLOB和TEXT列也可以編製索引，但是必須給出索引長度，即指定出使用前幾字元去編製索引。
  7. 對於MyISAM和InnoDB資料表，欄索引長度可以指定到1000byte，不是1000個字元。
  8. 只有MyISAM儲存引擎支援FULLTEXT索引，並且只為CHAR、VARCHAR和TEXT欄。
  9. 只有MyISAM儲存引擎支援空間類型。空間索引使用R-樹。

單獨建立索引的完整命令如下：

create [unique|fulltext|spatial] index 索引名 [USING 索引型別] on 表名(欄名 [(長度)] [asc|desc],….)
  1. unique代表「唯一約束」索引，其作用後面會用例示說明。
  2. fulltext代表「全文索引」只能對CHAR, VARCHAR和TEXT欄編製索引，並且只能在MyISAM資料表中編製。
  3. spatial索引只能對空間欄編製索引，並且只能在MyISAM資料表中編製。空間欄是針對 openGIS 配合的新應用。
  4. 索引型別有 BTREE, FULLTEXT, HASH, RTREE 預設使用 BTREE 。不同的儲存引擎所支援的索引型別並不相同。
  5. 不需要欄值全文做為索引，可以指定該欄的前幾字做為索引，這可以使索引檔案大大減小，提高INSERT操作的速度；短索引可以在索引緩衝中放更多索引，減少硬碟的存取。CREATE INDEX語句中的指定欄使用的索引長度指的是字元的數目，對於使用多byte字元編碼(如utf8一字元3byte)，在指定欄的索引長度時，要考慮這一點。
  6. ASC或DESC指示索引欄的排列方式為升序或降序。

如果建索引時使用「前欄+後欄」，以下查詢會使用索引：

  1. select … from … where 前欄='某值'
  2. select … from … where 前欄='某值' and 後欄='某值'
  3. select … from … where 前欄='某值' and (後欄='某值' OR 後欄='某值')
  4. select … from … where 前欄='某值' and 後欄>='某值' and 後欄<'某值')

以下查詢不會使用索引：

  1. select … from … where 後欄='某值'
  2. select … from … where 前欄='某值' or 後欄='某值'

二、唯一約束的示例

create table menus(id tinyint(4) not null auto_increment,label varchar(10) null,url varchar(20) null,unique key(id));
insert into menus(label,url) values('Home','home.html');
insert into menus(label,url) values('About us','aboutus.html');
insert into menus(label,url) values('Services','services.html');
insert into menus(label,url) values('Feedback','feedback.html');
select * from menus;
+---+----------+---------------+
| id| label    | url           |
+---+----------+---------------+
| 1 | Home     | home.html     |
| 2 | About us | aboutus.html  |
| 3 | Services | services.html |
| 4 | Feedback | feedback.html |
+---+----------+---------------+

如果現在插入一條違背唯一約束的記錄，MySQL會中斷操作，提示出錯：

insert into menus(id,label,url) values(4,'Contact us','contactus.html');
回應：ERROR 1062 (23000): Duplicate entry '4' for key 'id'

如在前述INSERT語句添加IGNORE關鍵字，且違背唯一約束，MySQL不會嘗試去執行這條語句，因此也不會返回錯誤：

insert ignore into menus(id,label,url) values(4,'Contact us','contactus.html');
回應：Query OK, 0 rows affected (0.00 sec)

當有很多的INSERT語句需要循序執行時，IGNORE關鍵字可以保證不管哪一個INSERT包含了重複的鍵值，MySQL都回跳過它，而不是放棄全部操作。

另一種避免出現程式中斷的方法是使用「ON DUPLICATE KEY UPDATE…」子句：

insert into menus(id,label,url) values(4,'Contact us','contactus.html') on duplicate key update label='Contact us',url='contactus.html';

這時違背唯一約束的那一筆，雖沒有依指示插入新筆，但會依指示更新為新欄值。當然那一筆的舊欄值會被取代掉了。

select * from menus;
+---+------------+----------------+
| id| label      | url            |
+---+------------+----------------+
| 1 | Home       | home.html      |
| 2 | About us   | aboutus.html   |
| 3 | Services   | services.html  |
| 4 | Contact us | contactus.html |
+---+------------+----------------+

三、速度

布署資料表時，加快查詢速度的要領：

  1. 儘量讓資料表小，例如更少位數的整數型別，
  2. 儘量將欄位指定為 NOT NULL，會使每一筆省一byte，而且在很多運算中較快。但須要用NULL時還是要用，只是避免預設在所有筆都用它。
  3. MyISAM 搜尋固定大小欄位比較快；也就是說，也就是用 CHAR 會比 VARCHAR快，但可能浪費空間。
  4. 每張資料表的主索引應該盡可能短。
  5. 只建立您確實需要的索引。索引對找資料有好處，卻拖慢儲存。
  6. 不要取不需要的資料，如「SELECT * ...」。
  7. 使用 LIMIT ，對速度影響頗重要。
  8. ENUM 欄(從諸值中選一個)，資料處理速度很快。
  9. 善用索引，在「SELECT … FROM … LEFT JOIN … ON 某欄=某欄 WHERE 某欄=…;」中SELECT 的欄位不必索引；JOIN兩表所須諸欄位需索引，如ON中用到的欄位；WHERE 的欄位需索引。
  10. 使用 LIKE 時，避免把 % 放在字串開頭，蒐索標的沒辦法由頭往下逐字找起，就無法利用索引。
  11. 注意 WHERE 子句內的運算式，「SELECT … WHERE `col` < 100/10;」可以利用col的索引會很快；但「SELECT … WHERE `col`*10 < 100;」無索引可用就慢。

陸、指令

SQL指令分為三群：

  1. DDL—數據定義語言(Create，Alter，Drop，DECLARE)
  2. DML—數據操作語言(Select，Delete，Update，Insert)
  3. DCL—數據控制語言(GRANT，REVOKE，COMMIT，ROLLBACK)

數據操作語言

一、SELECT

(一)基本用法

  1. SELECT @@[global.|session.]系統變數;或SELECT @用戶變數;
  2. select 演算式,演算式…;
  3. select 欄名 from 表名;
  4. select 欄名 from 表名 where 條件 group by 分組諸欄 order by 排序諸欄 limit 幾筆。 
    SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
        [WHERE where_definition]
        [GROUP BY col_name,...]
        [HAVING where_definition]
        [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
        [LIMIT [offset,] rows]
        [PROCEDURE procedure_name] ]
  5. 表名可以一張表，也可以多張表，多張表時可以來自不同的資料庫。
  6. 資料庫名.表名.欄名
  7. 不會弄混時，可以省略上位辨識器。

(二)where，找出符合條件的筆數：

  1. =,>,<,>=,<=,!=,<>,：比大小
  2. is not null ：非空值
  3. is null ：空值
  4. between 值1 and 值2：介於兩值之間
  5. in ('值1','值2',…)：在集合中
  6. not in ('值1','值2',…)：不在集合中
  7. like 字串 ：%代表任意長萬用字元，_代表一字元的萬用字元。
  8. not like 字串 ：
  9. regexp 表示式 ：POSIX正規表示式。如「SELECT * FROM 表名 WHERE 欄名 REGEXP '.-..1'」把x-xx1者找出來。

(三)結合兩張表(完全結合)：

  1. select * from 表1,表2;：產生一張新表，其欄是表1各欄＋表2各欄，其筆是表1各筆×表2各筆。
  2. 設表1與表2有共通欄位name
  3. select * from 表1,表2 where 表1.name=表2.name;：等結合，結合欄須是其中一張表的主鍵。結合兩張表，須要一個等結合，結合N張表須要N-1個等結合。
  4. select * from 表1,表2 where 表1.name=表2.name and 表1.address='丁志仁';：等結合後，各筆才有意義，從有意義的筆數中下蒐尋條件才有意義。
  5. 結合更多表，產生一張新表，其欄是各表相加，其筆是各表相乘。

(四)結合兩張表(左側結合，較完全結合為多)：

  1. select * from 表1 left join 表2 on 表1.欄A=表2.欄B;：產生一張新表，其欄是表1各欄＋表2各欄，其筆是先列出表1所有的筆，然後對表1中的每一筆：
    1. 表2有1-N筆合條件者，表1該表複製1-N次，每一個複製，將一筆表2合條件者抄在右邊。這部分和完全結合一樣。
    2. 表2中沒有合條件者，將表2欄位以空值(null)抄在右邊。這部分是較完全結合多出來的部分。
  2. select * from 表1 left join 表2 using(共通欄);：同上項，如欄A、欄B的欄名相同，可以用using。
  3. on和using兩個必須選用一個，而且只能用一個，不能都不用，也不能都用。不可以用where取代on。
  4. left join兩測都可以多表，兩側的多表先做等結合，兩張大表再做左側結合。
  5. 如「select * from 表1,表2 left join 表3 on 表2.欄A=表3.欄B;」，如在MySQL4，寫成「表1,表2」或「表2,表1」都可以。但是到了MySQL5，on使用表2，則表2就要臨近left join，所以一定要寫成「表1,表2」不可寫成「表2,表1」，否則會發生「unknow 表2.欄A」的錯誤。如果要寫成「表2,表1」就用括號把兩個表括起來：「(表2,表1)」。 
JOIN(結合)
CROSS JOIN (笛卡兒積)：「select * from 表1,表2」或「「select * from 表1 join 表2」
INNER JOIN (等結合)：「select * from 表1,表2 where 欄=欄」或「「select * from 表1 join 表2 on 欄=欄」
LEFT JOIN：「select * from 表1,表2 where 欄*=欄」或「select * from 表1 left join 表2 on 欄=欄」
NATURAL LEFT JOIN：「select * from 表1 natural left join 表2」自動找兩表中同名欄對應起來。
RIGHT JOIN：其他關聯式資料庫支援，但MySQL不支援，因為對調就成了LEFT JOIN，反而影響效能。

(五)聚合函數(以下的欄也可以用演算式取代)：

句法：
  1. select avg(欄) from 表;
  2. select 欄1,avg(欄2) from 表 group by 欄1;：欄1同值者為1組，一組一組算出各組平均。
    group by 只能放在 where 之後，不可放在 where 之前。
  3. select 客戶,avg(金額) from 訂單 group by 客戶 having avg(金額)>50;：將同一客戶的訂單金額平均，找出平均每張訂單超過50元者。
函式：
  1. avg(欄名)：該欄的平均值。
  2. sum(欄名)：該欄合計。
  3. count(欄名)：算出該欄有值的筆數，有值是指非null，含空白及0。
  4. count(distinct 欄名)：算出該欄有多少種不同的值。
  5. count(*)：不管是不是null值的所有筆數。
  6. max(欄名)：該欄最大值。
  7. min(欄名)：該欄最小值。
  8. std(欄名)、stddev(欄名)：該欄標準偏差(standard deviation)。
  9. VARIANCE(欄名)：expr 的標準方差(standard variance)。 4.1 或更新的版本中可用。
  10. BIT_OR(欄名)：返回 expr 中所有比特位的位 OR。
  11. BIT_AND(欄名)：返回 expr 中所有比特位的位 AND。
  12. GROUP_CONCAT(欄名)：該欄同群中有幾種值。
    完整命令:GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]] [SEPARATOR str_val])：分隔符號預設為空白。
    例： 
    mysql> SELECT * FROM `ta`;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  1 | b    |
|  1 | c    |
|  1 | d    |
|  2 | a    |
|  2 | b    |
|  2 | c    |
|  3 | d    |
+----+------+
8 rows in set (0.00 sec)

mysql> SELECT `id`,GROUP_CONCAT(`name`) FROM `ta` GROUP BY `id`;
+----+----------------------+
| id | GROUP_CONCAT(`name`) |
+----+----------------------+
|  1 | a c b d              |
|  2 | a c b                |
|  3 | d                    |
+----+----------------------+

(六)order排序：必在where、group之後，之前會錯。

  1. select * from 表 order by 欄名;：按該欄值字母排，由a-z。
  2. select * from 表 order by 欄名 asc;：按該欄值的ASC值排，由小到大。
  3. select * from 表 order by 欄名 desc;：按該欄值的ASC值排，由大到小。

(七)limit範圍：

  1. select * from 表 limit 自第幾筆始,以下幾筆;：取一定範圍。
  2. 首筆為第0筆，次筆為第1筆。

(八)用於SELECT和WHERE子句的函數：

非特定資料型別
  1. ( ... )先運算
  2. 比較規則：
    • 任何一個標準運算符(=,<>...,但除了LIKE)對一個忽略大小寫的字串進行比較，尾部的空白空間(空格、TAB和換行)均被忽略。
    • 如果一個或兩個參數是NULL，比較的結果是NULL，除了<=>運算符。
    • 如果在一個比較操作中兩個參數均是字串，他們將作為字串被比較。如果在一個比較操作中兩個參數均是字串，他們將作為字串被比較。
    • 如果兩個參數均是整數，他們作為整數被比較。
    • 十六進位值如果不與一個數字進行比較，那麼它將當作一個二進位字串。
    • 如果參數之一是一個TIMESTAMP或DATETIME列，而另一參數是一個常數，在比較執行之前，這個常數被轉換為一個時間戳記。
    • 在所有其它情況下，參數作為浮點(real)數字被比較。
    • 預設字串使用當前字元集以忽略字母大小寫的方式進行比較(預設的字集為 ISO-8859-1 Latin1，它對英語處理得很出色)。
  3. =( 等於 ),<>,!=( 不等於 ),<=( 小於或等於 ),<<( 小於 ),>=( 大於或等於 ),>( 大於 ),<=>(NULL 值全等於 ),IS NULL,IS NOT NULL(測試一個值是或不是 NULL)
    • SELECT * FROM 表名 WHERE 自動欄 IS NULL：找到最後插入筆
    • SELECT * FROM 表名 WHERE 日期欄或日期時間欄 IS NULL：找到0000-00-00
  5. ISNULL(演算式)
  6. COALESCE(值,…)：傳回諸值中第一個非null元素。
  7. 演算式 between 值1 and 值2：介於兩值之間，兩值可為英文字母。
  8. 演算式 not between 值1 and 值2：在兩值之外。
  9. in ('值1','值2',…)：在集合中
  10. not in ('值1','值2',…)：不在集合中
  11. INTERVAL(N,N1,N2,N3,...)：N1-Nn中有幾個比N大(不含等於)，N1-Nn須由小到大排序。非常快。
  12. NOT,!：邏輯非，如果操作數為0，返回1；如果操作數為非零，返回0；如果操作數為NOTNULL，返回NULL。
  13. AND,&&：邏輯與，所有的操作數都是非零或非NULL的，返回1；如果有一個或多個操作數為0，則返回0，只要操作數中有NULL返回值就為NULL。
  14. OR,||：邏輯或，如果任何一個操作數是非零的，返回值為1，如果任一操作數為NULL，返回值為NULL，否則返回0。
  15. XOR：邏輯異或。邏輯異或。 如果任一操作數為NULL，返回值為NULL。如果任一操作數為NULL，返回值為NULL。 對於非NULL的操作數，如果有奇數個非零的操作數，結果返回為1，否則返回0。對於非NULL的操作數，如果有奇數個非零的操作數，結果返回為1，否則返回0。
  16. IFNULL(expr1,expr2)：如果expr1為非NULL的，IFNULL()返回expr1，否則返回expr2。如果expr1為非NULL的，IFNULL()返回expr1，否則返回expr2。
  17. NULLIF(expr1,expr2)：如果expr1 = expr2為真，返回NULL，否則返回expr1。如果expr1=expr2為真，返回NULL，否則返回expr1。
  18. IF(expr1,expr2,expr3)：如果expr1為真(expr1 <> 0以及expr1 <> NULL)，那麼IF()返回expr2，否則返回expr3。如果expr1為真(expr1<>0以及expr1<>NULL)，那麼IF()返回expr2，否則返回expr3。
    字串函數
  19. ASCII(str)：返回字串str最左邊的那個字元的 ASCII 碼值。返回字串str最左邊的那個字元的ASCII碼值。 如果str是一個空字串，那麼返回值為0。如果str是一個空字串，那麼返回值為0。 如果str是一個NULL，返回值也是NULL：如果str是一個NULL，返回值也是NULL。
  20. ORD(str)：如果字串str的最左邊的字元是一個多位元組的字元，根據多位元組字元的成分字元的 ASCII 碼值通過下面的公式計算返回那個的編碼：((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]。如果最左邊的字元不是一個多位元組字元，返回值與ASCII()函數相同。
  21. CONVERT(字串或演算式或欄位或* using 編碼)：轉換編碼方式，編碼有utf8,gb2312…。
  22. CONV(N,原進位制底數,新進位制底數)：轉換進位制。底數為2-36，數字元為0-9,a-z。如CONV("a",16,2)得到'1010'。
  23. BIN(N)：將10進位數字變為二進位字串。
  24. OCT(N)：將10進位數字變為八進位字串。
  25. HEX(數字或字串)：如數字將其轉為16進位字串，如為字串每個字元均被轉換為2位十六進位數字，並以十六進位的字串形式返回。
  26. UNHEX('數字')：將16進位ASCII碼，轉成相對應的字元。
  27. CHAR(數值,數值,...)：每個數值均轉為代表的ASCII字元，各字元組成的字串。 NULL值將被忽略。
  28. CONCAT(字串,字串,...)：將各字串接起來。
  29. CONCAT_WS(分隔字串,字串,字串,...)：各字串間加上分隔字串後接起來。如果分隔符號是NULL，返回值也將為NULL。這個函數會跳過分隔符號參數後的任何NULL和空字串。
  30. LENGTH(str)、OCTET_LENGTH(str)、CHAR_LENGTH(str)、CHARACTER_LENGTH(str)：返回字串str的長度。CHAR_LENGTH()和CHARACTER_LENGTH()對於多位元組字元只計數一次。
  31. BIT_LENGTH(str)：返回字串str的比特長度。
  32. LOCATE(欲找字串,原字串)、POSITION(欲找字串 IN 原字串)：返回欲找子串在原字串中第一次出現的位置。如果子串substr在str中不存在，返回值為0。
  33. LOCATE(substr,str,pos)：返回子串substr在字串str中的第pos位置後第一次出現的位置。如果substr不在str中返回0。
  34. INSTR(str,substr)：返回子串substr在字串str中第一次出現的位置。這與有兩個參數形式的LOCATE()相似，只是參數的位置被顛倒了。
  35. LPAD(str,len,padstr)：用字串padstr對str進行左邊填補直至它的長度達到len個字元長度，然後返回str。如果str的長度長於len，那麼它將被截除到len個字元。
  36. RPAD(str,len,padstr)：用字串padstr對str進行右邊填補直至它的長度達到len個字元長度，然後返回str。如果str的長度長於len，那麼它將被截除到len個字元。
  37. LEFT(str,len)：返回字串str中最左邊的len個字元。此函數對多字節是安全的。
  38. RIGHT(str,len)：返回字串str中最右邊的len個字元。此函數對多字節是安全的。
  39. SUBSTRING(str,pos,len)、SUBSTRING(str FROM pos FOR len)、MID(str,pos,len)：從字串str的pos位置起返回len個字元的子字串，字串的第一個字元其 pos 算 1 。使用FROM的變體形式是 ANSI SQL92 的句法。此函數對多字節是安全的。
  40. SUBSTRING(str,pos)、SUBSTRING(str FROM pos)：從字串str的pos位置起返回子串。此函數對多字節是安全的。
  41. SUBSTRING_INDEX(str,delim,count)：返回字串 str 中在第 count 個出現的分隔符號 delim 之前的子串。如果 count 是一個正數，返回從最後的(從左邊開始計數)分隔符號到左邊所有字元。如果 count 是負數，返回從最後的(從右邊開始計數)分隔符號到右邊所有字元
  42. LTRIM(str)：返回移除了領頭的空格字元後的str。
  43. RTRIM(str)：返回移除了尾部的空格字元後的str。
  44. TRIM([[BOTH|LEADING|TRAILING][remstr]FROM]str)：移除字串str中所有的remstr前綴或後綴，然後將其返回。 如果沒有任何BOTH、LEADING或TRAILING修飾符被給出，BOTH被假定。如果remstr沒有被指定，空格將被移除。
  45. SOUNDEX(str)：返回str的近音字。
  46. SPACE(N)：返回有N空格字元組成的字串。
  47. REPLACE(str,from_str,to_str)：在字串 str 中所有出現的字串 from_str 均被 to_str替換，然後返回這個字串。
  48. REPEAT(str,count)：返回一個由重複了 count 次的字串 str 組成的字串。如果 count <= 0，返回一個空字串。如果 str 或 count 是 NULL，返回值也為 NULL。
  49. REVERSE(str)：以顛倒的字元順序返回字串 str。
  50. INSERT(str,pos,len,newstr)：在字串 str 中，將從 pos 位置開始，len 個字元長的子串替換為字串 newstr ，然後將結果返回。
  51. ELT(N,str1,str2,str3,...)：傳回第N個字串。如果 N 小於 1 或大於參數的數量，返回 NULL。
  52. FIELD(str,str1,str2,str3,...)：查出 str 是列表 str1, str2, str3, ... 中的第幾個。如果 str 沒有發現，返回 0。
  53. FIND_IN_SET(str,strlist)：傳回str是列表中的第幾個。FIND_IN_SET('b','a,b,c,d')得2。str 在不 strlist 中或者如果 strlist 是一個空串，返回值為 0。如果任何一個參數為 NULL，返回值也是 NULL。如果第一個參數包含一個 「,」，這個函數將完全不能工作 。
  54. MAKE_SET(bits,str1,str2,...)：選諸字串，構成set。
  55. EXPORT_SET(數值,開代表符號,關代表符號,[分隔符號,[位數]])：將數值表示為0,1開關，其間用間隔符號連起來，共排上幾位數。
  56. LCASE(str)、LOWER(str)：改為小寫。
  57. UCASE(str)、UPPER(str)：改為大寫。
  58. LOAD_FILE(file_name)：讀入文件，並將文件內容作為一個字串返回。這個文件必須在伺服器上，必須指定文件完整的路徑名，並且你必須有 FILE 權限。文件必須完全可讀，並且小於 max_allowed_packet。 如果該文件不存在，或因為上面的任一原因而不能被讀出，函數返回 NULL。
  59. QUOTE(str)：修飾後傳回字串。字串被單引號包圍著返回，並且在該字串中每個單引號(「'」)、反斜線符號(「\」)、ASCII NUL 和 Control-Z 出現的地方，在該字元之前均被加上了一個反斜線。如果參數是 NULL，那麼結果值是一個沒有單引號包圍的單詞 「NULL」。
  60. like 字串 ：%代表任意長萬用字元，_代表一字元的萬用字元。
  61. not like 字串 ：
  62. regexp 表示式 ：POSIX正規表示式。如「SELECT * FROM 表名 WHERE 欄名 REGEXP '.-..1'」把x-xx1者找出來。
  63. STRCMP(expr1,expr2)：如果字串是相同，返回 0，如果第一個參數包含第二個參數傳回 1，如果第二個參數包含第一個參數傳回鄉 -1。
  64. MATCH (col1,col2,...) AGAINST (expr)、MATCH (col1,col2,...) AGAINST (expr IN BOOLEAN MODE)：全文檢索。
  65. BINARY 字串：BINARY 將跟在它後面的字串強製作為一個二進位字串。
    數字函數：所有的數學函數在發生錯誤的情況下，均返回 NULL。
  66. -：變號
  67. ABS(X)：絕對值。
  68. SIGN(X)：以 -1、0 或 1 方式返回參數的符號，它取決於參數 X 是負數、0 或正數。
  69. MOD(N,M)、%：返回 N 被 M 除後的餘數。
  70. FLOOR(X)：返回不大於 X 的最大整數值。
  71. CEILING(X)：返回不小於 X 的最小整數。
  72. ROUND(X)、ROUND(X,D)：四捨五入到最近的整數。四捨五入到 D 個小數。
  73. DIV：求整數商。類似於 FLOOR()，但是它可安全地用於 BIGINT 值。
  74. EXP(X)：返回值 e (自然對數的底) 的 X 次方。
  75. LN(X)：返回 X 的自然對數。
  76. LOG(X)、LOG(B,X)：省略底數，會返回 X 的自然對數。
  77. LOG2(X)：返回 X 的以 2 為底的對數。
  78. POW(X,Y)、POWER(X,Y)：返回 X 的 Y 次方。
  79. PI()：返回 PI 值(圓周率)。預設顯示 5 位小數，但是在 MySQL 內部，為 PI 使用全部的雙精度。
  80. COS(X)：返回 X 的餘弦，在這裡，X 以弧度給出。
  81. SIN(X)：返回 X 的正弦，在這裡，X 以弧度給出。
  82. TAN(X)：返回 X 的正切，在這裡，X 以弧度給出。
  83. ACOS(X)：返回 X 的反餘弦，更確切地說，返回餘弦值為 X 的值。如果 X 不在 -1 到 1 之間的範圍內，返回 NULL。
  84. ASIN(X)：返回 X 的反正弦，更確切地說，返回正弦值為 X 的值。如果 X 不在 -1 到 1 之間的範圍內，返回 NULL。
  85. ATAN(X)：返回 X 的反正切， 更確切地說，返回正切值為 X 的值。
  86. ATAN(Y,X)、ATAN2(Y,X)：返回兩個變數 X 和 Y 的反正切。它類似於計算 Y / X 的反正切，除了兩個參數的符號用於決定結果的象限。
  87. COT(X)：返回 X 的余切。
  88. RAND()、RAND(N)：返回一個範圍在 0 到 1.0 之間的隨機浮點值。如果一個整數參數 N 被指定，它被當做種子值使用(用於產生一個可重複的數值)。
    從 MySQL 3.23 開始，你可以使用：SELECT * FROM table_name ORDER BY RAND()，取得隨機樣本。例如：「SELECT * FROM table1,table2 WHERE a=b AND c注意，在一個 WHERE 子句中的 RAND() 將在每次 WHERE 執行時被重新計算。 RAND() 並不是預期完美的隨機數發生器，但是可以代替做為產生特別的隨機數一個快速的方法。
  89. LEAST(X,Y,...)：有兩個或更多個參數，返回最小的參數。參數使用下列規則進行比較：
    • 如果返回值用於一個 INTEGER 語境，或所有的參數是整數值，它們作為整數比較。
    • 如果返回值用於一個 REAL 語境，或所有的參數均是實數值，它們作為實數被比較。
    • 如果任何一個參數是字母大小寫敏感的，參數作為大小寫敏感的字串進行比較。
    • 在其它情況下，參數做為忽略大小寫的字元中進行比較：
  90. GREATEST(X,Y,...)、：返回最大的參數。規則同 LEAST 。
  91. DEGREES(X)：將參數 X 從弧度轉換為角度。然後返回。
  92. RADIANS(X)：將參數 X 從角度轉換為弧度，然後返回。
  93. TRUNCATE(X,D)：將數值 X 截到 D 個小數，然後返回。如果 D 為 0，結果將不包含小數點和小數部分。
    日期和時間函數
  94. DAYOFWEEK(date)：傳回星期幾，(1 = Sunday, 2 = Monday, ... 7 = Saturday)。索引值符合 ODBC 的標準。
  95. WEEKDAY(date)：傳回星期幾，(0 = Monday, 1 = Tuesday, ... 6 = Sunday)。
  96. DAYOFMONTH(date)：返回 date 是一月中的第幾天，範圍為 1 到 31。
  97. DAYOFYEAR(date)：返回 date 是一年中的第幾天，範圍為 1 到 366。
  98. DATEDIFF(date1,date2)：兩日期之間相隔幾日。兩日期相同為0，date1晚於date2為正，date1早於date2為負。
  99. MONTH(date)：返回 date 中的月份，範圍為 1 到 12。
  100. DAYNAME(date)：返回 date 的英文星期名。
  101. MONTHNAME(date)：返回 date 的英文月份名。
  102. QUARTER(date)：返回 date 在一年中的第幾季，範圍為 1 到 4。
  103. WEEK(date,格式參數)：一年中的第幾週。
  104. YEAR(date)：返回 date 的年份，範圍為 1000 到 9999。
  105. YEARWEEK(date)、YEARWEEK(date,first)：傳回年週。
  106. HOUR(time)：返回 time 的小時值，範圍為 0 到 23。
  107. MINUTE(time)：返回 time 的分鐘值，範圍為 0 到 59。
  108. SECOND(time)：返回 time 的秒值，範圍為 0 到 59。
  109. PERIOD_ADD(P,N)：在P上再加N個月。P(格式為 YYMM 或 YYYYMM)中。以 YYYYMM 格式返回值。
  110. PERIOD_DIFF(P1,P2)：返回時期 P1 和 P2 之間的月數。
  111. DATE_ADD(date,INTERVAL expr type)、DATE_SUB(date,INTERVAL expr type)、ADDDATE(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)：加減日數。
  112. EXTRACT(提取什麼 FROM date)：從日期中提取年、年月、日分…
  113. TO_DAYS(date)：傳回西元0 年到此日的總天數。
  114. FROM_DAYS(N)：給出一個天數 N，返回一個日期。
  115. DATE_FORMAT(date,format)：格式化日期及時間。date可表示成'年-月-日 時:分:秒'或'年-月-日'
  116. TIME_FORMAT(time,format)：格式化時間。如果格式不是時分秒相關的，傳回null或0。
  117. CURDATE()、CURRENT_DATE：傳回今天。
  118. CURTIME()、CURRENT_TIME：傳回此時之時分秒。
  119. NOW()、SYSDATE()、CURRENT_TIMESTAMP：傳回年月日時分秒。
  120. UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)：傳回從 1970-01-01 00:00:00 GMT 開始經過的秒數值。
  121. FROM_UNIXTIME(秒數 [,format])：由秒數算日期。
  122. SEC_TO_TIME(seconds)：由秒數換算時分秒。
  123. TIME_TO_SEC(time)：由時分秒換算秒數。
    型別轉換函數
  124. CAST(欄名 AS 型別)：將某欄轉換為指定的資料型別，可用於 order by 。
    • CAST(欄名 AS DECIMAL(10,2))：將某欄轉換型別為數值。
    • CAST('2000-01-01' AS DATE)：轉換為日期。
    • CAST(string AS BINARY) 與 BINARY string 是相同的。BINARY型別下，大小寫不相等。
    • CAST(expr AS CHAR) 表示一個使用當前預設字集的字串。
    • CAST(1-2 AS UNSIGNED)得到 18446744073709551615 ：1-2 為 -1，負數轉換為無符號，會得到一個長整數 264-1。
    • CAST(1-3 AS UNSIGNED)得到 18446744073709551614 ：1-3 為 -2，轉換為無符號，會比上一個長整數少 1 ，即 264-2。
    • CAST(正整數 AS UNSIGNED)仍得到原來的正整數。
    • CAST(CAST(1-2 AS UNSIGNED) AS SIGNED)得到 -1 。

    其他 Cast 函數：略
    其他函式
  125. 位元演算：|(位或),&(位與),^(位異或),<<(左移),>>(右移),~(置反所有位)。
    例如：○○○○，由左數來代表8,4,2,1四個位。○●○○代表4，左起第2位為1，其他三位為0。「欄名&4=4」的條件(即「?●??&○●○○→○●○○」)，會從該欄中挑出左起第2位為1的所有欄值，包括:12,13,14,15,4,5,6,7。原因是左起第2位以外的其他三位，不管其值是0或1，&0之後都會得0。而左起第2位原值如為0，&1之後還得0；左起第2位原值如為1，&1之後還得1。
  126. BIT_COUNT(N)：位元數。
  127. DATABASE()：資料庫名。
  128. USER()、SYSTEM_USER()、SESSION_USER()：用戶名，含用戶名和客戶機的主機名。可以用SUBSTRING_INDEX(USER(),"@",1)找出純的用戶名。
  129. PASSWORD(str)、OLD_PASSWORD(str)：算出加密的通行碼。
  130. ENCRYPT(str[,salt])：使用 Unix crypt() 加密。不可用時 ENCRYPT() 返回 NULL。
  131. ENCODE(str,pass_str)：使用 pass_str 做為密鑰加密 str。
  132. DECODE(crypt_str,pass_str)：使用 pass_str 作為密鑰解密加密後的字串 crypt_str。
  133. MD5(string)：MD5加密。
  134. 其他加密法:略。
  135. LAST_INSERT_ID([expr])：返回被插入到一個 AUTO_INCREMENT 列中的最後一個自動產生的值。
  136. FORMAT(X,D)：將數字 X 格式化為一個 '#,###,###.##' 的形式，四捨五入到 D 位小數。如果 D 為 0，返回的結果將沒有小數點和小數部分
  137. VERSION()：MySQL 的版本。
  138. CONNECTION_ID()：連接 ID(thread_id)。
  139. GET_LOCK(str,timeout)：鎖定。阻塞其它的客戶端用同樣的名字的鎖定請求；遵從一個給定鎖定字串名的客戶端可以使用這個字串來執行子協定。
  140. RELEASE_LOCK(str)：釋放鎖定。
  141. IS_FREE_LOCK(str)：檢查以 str 命名的鎖定是否可以自由使用(也就是說，還未鎖定)。
  142. BENCHMARK(count,expr)：演算式 expr 重複運行 count 次。可以用於計算 MySQL 處理演算式有多快。
  143. INET_NTOA(expr)：將數值換算為網路地址(ip)。
  144. INET_ATON(expr)：將網路地址(ip)換算為數值。
  145. FOUND_ROWS()：返回最後一個 SELECT SQL_CALC_FOUND_ROWS ... 命令如果沒有以 LIMIT 進行限制結果時將返回記錄行數。
    實用：先用SELECT SQL_CALC_FOUND_ROWS 諸欄 FROM 表名 WHERE 條件 LIMIT 0;執行很快，因為不用輸出。再用SELECT FOUND_ROWS();查出該sql指令結果會有幾筆。
    用於 GROUP BY 子句的函式：見前面

二、insert,delete,update

  1. insert 不可雙表。
  2. insert進資料表時會去反斜，兩個反斜代表「\」字元。
  3. MySQL3:delete 不可雙表；MySQL4:delete可以多表，其指令類似select，只是以「delete 諸表」取代「select 諸欄」，可以使用left join、on、where組合。
  4. update可雙表，回報影響列數，是兩表影響列數之和。如：
    「update AA,BB set AA='AA3',BB='BB3' where AA.AB=BB.AB and AA.AB=3」影響兩列，分別屬於兩表。
  5. update、delete的limit只可以指示要更新幾筆，不可以指示從第幾筆開始，所以limit後只有一個參數，不能有兩個參數。

數據定義語言

三、ALTER

  1. 改資料庫：alter database 庫名 default character set utf8 collate utf8_general_ci;改資料庫預設字集及校對方式。
  2. 改表名：alter table 表名 rename 新表名;。
  3. 改結構：alter table 表名 修改動作1,修改動作2…;：修改動作有add,alter,change,modify,drop。
    1. add 欄名及欄位描述 [first|after 某欄]：欄位描述和建欄時一樣，並且可指定欄的位置。例如「alter table 表名 add 欄名 mediumint(8) unsigned NOT NULL auto_increment PRIMARY KEY」。
    2. add (欄名及欄位描述,欄名及欄位描述…)：將數欄加到表的最後。
    3. add index (欄名,欄名…)：增加索引。
    4. add primary key (欄名,欄名…)：增加主鍵。
    5. add unique (欄名,欄名…)：增加唯一索引欄。
    6. alter 欄名 set default 值：設定欄的預設值。
    7. alter 欄名 drop default ：取消欄的預設值。
    8. change 舊欄名 新欄名及欄位描述：改變欄名及欄位描述。
    9. modify 欄名及欄位描述：不改變欄名及但修改欄位描述。
    10. drop 欄名：刪除欄。
    11. drop primary key：刪除該表的主鍵，但不刪除欄位。
    12. drop index 索引名：刪除索引。
    13. drop index 索引名：刪除索引。
  4. 改筆之排序：alter table 表名 order by 欄名;：以某欄為序重排資料表。
  5. 改自動加1之指標：alter table 表名 auto_increment =某數;：指定自動加1欄下一個加1指標。如果指定值小於目前最大數值加1，仍設為目前最大數加1。所以永遠可以設1，使其自動調到最大值加1。
  6. 其他：alter table 表名 PACK_KEYS =0 CHECKSUM =0 DELAY_KEY_WRITE =0

四、Create

  1. Create DATABASE database-name：創建數據庫
  2. create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)：創建新表
  3. create table tab_new like tab_old：使用舊表創建新表
  4. create table tab_new as select col1,col2… from tab_old definition only：使用舊表部分欄位創建新表
  5. create [unique|fulltext|spatial] index 索引名 [USING 索引型別] on 表名(欄名 [(長度)] [asc|desc],….)：創建索引。索引無法更改，想更改必須刪除重新建。
  6. create view viewname as select statement：創建視圖

五、Drop

  1. drop database dbname：刪除資料庫
  2. drop table tabname：刪除資料表
  3. drop index idxname：刪除索引
  4. drop view viewname：刪除視圖

其他

六、SHOW

  1. 秀諸庫：SHOW DATABASES
  2. 秀諸表：show tables;：秀出現用資料庫有那些資料表。
  3. 秀引擎諸狀態：SHOW [STORAGE] ENGINES
  4. 秀錯誤訊息：SHOW ERRORS [LIMIT [offset,] row_count]
  5. 秀錯誤訊息總數：SHOW COUNT(*) ERRORS
  6. 秀授權：SHOW GRANTS FOR user;、SHOW GRANTS;、SHOW GRANTS FOR CURRENT_USER;、SHOW GRANTS FOR CURRENT_USER();
  7. 秀權限：SHOW PRIVILEGES
  8. 查索引結構：SHOW INDEX FROM 表名 [FROM 庫名];。每種索引的每一索引欄構成一筆，每筆各欄解釋如下：
    1. Table :表名
    2. Non_unique :是否非唯一，是1，否是0 表示該索引不能有重複的記錄。
    3. Key_name :索引名，由建立索引時命名，如PRIMARY…等等
    4. Seq_in_index:該欄在該種索引中的次序，從 1 開始。
    5. Column_name :欄名
    6. Collation :該欄在索引中是怎麼排序的。A (升序) 或 NULL (無序)
    7. Cardinality :基數，該索引中唯一值的數量，當執行 ANALYZE TABLE 或 myisamchk -a. Cardinality 時會更新它的值。 Cardinality(基數)是用整數存儲的，基於統計的結果，因此如果是小表，那麼它的結果可能未必精確。基數越大，則 MySQL 在做表連接時使用索引的幾率越大。
    8. Sub_part :某欄用多長的字元做索引。當整個欄都做索引了，那麼它的值是 NULL。
    9. Packed :表示鍵值是如何壓縮的，NULL 表示沒有壓縮。
    10. Null :當欄包括 NULL 的記錄是 YES，它的值為，反之則是 ''。
    11. Index_type :使用了哪種索引算法(有BTREE, FULLTEXT, HASH, RTREE)。
    12. Comment :備註
  9. 查各欄：SHOW FULL FIELDS FROM 表名;同SHOW COLUMNS FROM 表名;。每欄得一筆。
    1. Field :欄名
    2. Type :欄型(欄長)
    3. Null :YES表示可以有null值，空值表示不可以有null值。
    4. Key :PRI代表為主鍵或唯一索引鍵。MUL代表其他鍵，即使唯一也一樣。
    5. Default :預設值。沒設則定為null或0。
    6. Extra :
    7. Privileges:權限，通常有select,insert,update,references
  10. 查日誌：SHOW LOGS;
    1. File:日誌文件的完整路徑。
    2. Type:日誌文件的類型(例如：BDB 表示Berkeley DB類型的日誌文件)。
    3. Status:文件狀態(FREE 表示文件可以刪除，IN USE 表示在事務子系統中需要用到該日誌文件)。
  11. 查建表指令：SHOW CREATE TABLE 表名。
    1. Table:表名。
    2. Create Table:建表指令。
  12. SHOW STATUS;：秀出現用伺服器狀態變數。
    1. Variable_name:變數名。
    2. Value:變數值。
  13. SHOW VARIABLES;或SHOW GLOBAL VARIABLES或SHOW SESSION VARIABLES：秀出現用動態變數。
    1. Variable_name:變數名。
    2. Value:變數值。

七、set option(舊版)或set(新版)

此處的set是敘述句的主動詞，不是select、update中的set，或欄位類別中的set。

SET OPTION設定伺服器的系統變數或客戶操作的會期變數。設定的任何選擇有效到當前會期結束，或直到你設定不同的值。新版建議用SET不建議用SET OPTION。

  1. SET GLOBAL 變數名=值;：設為全域變數
  2. SET @@global.變數名=值;：設為全域變數
  3. SET SESSION 變數名=值;：設為會期變數
  4. SET @@session.變數名=值;：設為會期變數
  5. SET 變數名=值;：設為會期變數

八、配份

備份sql server 
USE master
# 建立備份數據的 device
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
BACKUP DATABASE pubs TO testBack	# 開始備份

柒、觸發器(trigger)

SQL觸發器的基本觀念是：程式邏輯不在 PHP 等前端程式做處理，而是推遲到資料庫這層來處理。

MySQL 的 Trigger 功能可以在對 record 進行處理時，觸發特定的事件。舉個例子，若是資料表中有個欄位(eg. dt)，型別是 datetime ，那麼它的預設值就必定要是常數。這樣一來若是要利用這個欄位來記錄這筆資料的的時間，就勢必要在程式端(PHP等)修改 record 的時候自行取得現在的 "時間"，然後再寫入到資料庫中。若是改用 Trigger 來處理，我們可以告訴資料庫當某個資料表的 record 被 update 時，請資料庫自動取得現在的 "時間" 並寫入 dt 欄位中，這樣子一來就等同於讓 dt 這個欄位的預設值變成 CURRENT_TIMESTAMP。

指令格式「Create Trigger 觸發名稱 before|after insert|update|delete on 資料表名稱 for each row 指令」「DROP TRIGGER 觸發名稱」。

範例：如 test 表中有一欄 dt 其格式為 datetime ，所以無法設定預設值為目前時間，可以建一叫test_bu觸發器「CREATE TRIGGER `test_bu` before UPDATE ON `test` FOR EACH ROW SET NEW.dt=CURRENT_TIMESTAMP」

「CREATE TRIGGER default_for_timestamp_update BEFORE UPDATE ON mytable FOR EACH ROW IF NEW.hidetype != OLD.hidetype AND NEW.hidetype != 0 THEN SET NEW.charge_dateline = UNIX_TIMESTAMP(); END IF;」

MySQL自5.0 開始提供觸發功能，5.1之後才較為完整。以下是觸發器的使用限制：

  1. 只有MySQL Super 權限者才能建立觸發器。
  2. 每個資料表最多只有2×3=6個觸發器，before和after兩種情形配insert,update,delete三種情形。每種情形只能建一個觸發器。
  3. MySQL 5.0.10後規定一個資料庫中不能有兩個同名的觸發器。
  4. 觸發器中 OLD.欄名，代表 update 或 delete 之前的原值，insert 不能用 OLD 。
  5. 觸發器中 NEW.欄名，代表 update 或 insert 之後的新值，delete 不能用 NEW 。
  6. 觸發器實體檔案放在資料庫目錄下的 表名.TRG 中。各 觸發名稱.TRN 中則放觸發器作用的資料表名稱。
  7. 觸發器中超過一個指令就要用begin … end 來封裝指令。
  8. 不能在觸發器指令中用 insert,update,delete 來存取資料表。
  9. 無法在觸發器指令中取消 insert,update,delete 等動作。
  10. 不能在觸發器指令中用 call 呼叫 SP(Stored procedures 預在程序)。
  11. 不能在觸發器指令中呼叫交易指令。

捌、早期的中文問題

  1. 欄值不分大小寫，即找「范(AD53)」其中內碼53是S，卻可找出「貞(AD73)」其中內碼73是s。
  2. 「秋(ACEE)」和「施(AC49)」互找；「志(A7D3)」和「李(A7F5)」互找；原因不明，用bit平移也不對，也沒大小寫關係。
  3. \(5C)代表…，加入資料表時要加\再存入，取出資料表時則會自動去\，以得到原來的值。
  4. 「許(B35C)」「功(A55C)」「蓋(BB5C)」，都含\(5C)。
  5. 如欄值為「許淑貞」，其實資料表真正存的是(許\淑貞)：
    1. 「where uname = '許\淑貞'」才比得到。
    2. 「where uname like '%許\\\淑貞%'」或「where uname like '許\\\淑貞'」才比得到，因為like函數會多剝一次反斜。
    3. 用「INSERT INTO 表名 (欄名) VALUES ('許\淑貞');」使欄值變「許淑貞」。其實資料表真正存的是(許\淑貞)。
    4. 用「UPDATE 表名 SET 欄名 = '許\淑貞_' WHERE 欄名 = '許\淑貞';」可將欄值'許淑貞'換成欄值'許淑貞_'。
    5. data.txt內容放「,許\淑貞」，用「LOAD DATA INFILE '…/data.txt' INTO TABLE 表名 FIELDS TERMINATED BY ',';」，不管從遠端還是本地端匯入，得到的欄值都是「許淑貞」。

附錄：系統變數一覽表：

※標記為string的變數採用字串值。標記為numeric的變數採用數字值。標記為boolean的變數可以設置為0、1、ON或OFF。標記為enumeration的變數一般情況應設置為該變數的某個可用值，但還可以設置為對應期望的枚舉值的數字。對於枚舉系統變數，第1個枚舉值應對應0。這不同於ENUM列，第1個枚舉值對應1。