第 10 章 MySQL 指令與函數

第 10 章

MySQL 指令與函數

本章宗旨



介紹主控端應用程式功能與使用說明



簡介 MySQL 之 SQL 指令型式與語法



簡介 MySQL 之專數函數型式與用法

大 綱

10-1 主控端應用程式 10-2 SQL 指令

10-3 MySQL 專屬函數

10-1 主控端應用程式



主控端應用程式（ Client Utility Program ），

是 MySQL 提供給使用者在無其他應用介面程

式輔助下，直接在 DOS 命令列操作 MySQL

伺服器或資料庫之指令

10-1 主控端應用程式

10-1-1 myisampack

 語法： c:\mysql\bin> myisampack [Options]

索引表名…

 用途：將資料表壓縮成唯讀型式，檔案較小，

讀取時間較為節省

10-1-2 mysql

 語法： c:\mysql\bin>mysql –u 帳號 – p 密碼 資料庫名 [Options]

 用途：以交談型式執行 SQL 指令，如第三章教

10-1 主控端應用程式

10-1-3 mysqladmin

 語法： c:\mysql\bin>mysqladmin [Options] 指 令 [ 指令選項參數 ] 指令 [ 指令選項參數 ]…

 用途：伺服器管理程式

10-1-4 mysqlbinlog

 語法： c:\mysql\bin>mysqlbinlog [Options] 日 誌檔 …

10-1 主控端應用程式

10-1-5 mysqlcheck

 語法： c:\mysql\bin>mysqlcheck [Options] 資 料庫名 [ 資料表名 ]

 用途：資料表檢查與修復程式

10-1-6 mysqldump

 語法： c:\mysql\bin> mysqldump [Options] 資 料庫名 [ 資料表名 ]

 用途：資料庫及 / 或資料表下載管理程式

10-1 主控端應用程式

10-1-7 mysqlimport

 語法： c:\mysql\bin>mysqlimport [Options] 資 料庫名 文字檔 1 [ 文字檔 2] …

 用途：將 TextFile 文字檔內容輸入在與文字檔 同名之資料表

10-1-8 mysqlshow

 語法： c:\mysql\bin>mysqlshow [Options] [ 資 料庫名 [ 資料表名 [ 欄名 ]]]

10-1 主控端應用程式

10-1-9 perror

 語法： c:\mysql\bin>perror [Options] 錯誤代號

…

 用途：顯示指定代號之錯誤信息

10-2 SQL 指令

10-2-1 資料操作 10-2-2 資料定義 10-2-3 公用指令

10-2-4 交易與鎖表指令

10-2-5 資料庫管理指令

10-2-1 資料操作

 DELETE ：刪除記錄

語法： DELETE [LOW_PRIORITY] [QUICK] [IG NORE] FROM tbl_name[WHERE where_defin ition][ORDER BY ...][LIMIT row_count];

 DO ：執行類似 Select 指令，但不回傳結果，

速度較快

語法： DO expr [, expr] ...

10-2-1 資料操作

 INSERT ：新增紀錄

語法： INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

VALUES ({expr | DEFAULT},...),(...),...

[ ON DUPLICATE KEY UPDATE col_name = expr, ... ]

10-2-1 資料操作

 LOAD DATA INFILE ：將文字檔載入資料表

語法： LOAD DATA [LOW_PRIORITY | CONCURREN T] [LOCAL]

INFILE 'file_name' [REPLACE | IGNORE]

INTO TABLE tbl_name

[FIELDS [TERMINATED BY '\t'] [[OPTIONALLY] ENCL OSED BY ''] [ESCAPED BY '\\' ] ]

[LINES [STARTING BY ''] [TERMINATED BY '\n'] ] [IG NORE number LINES] [(col_name,...)]

10-2-1 資料操作

 REPLACE ：與 Insert 指令相同，但若有 Pri

mary 或 Unique 索引表時，舊紀錄將會被新紀 錄取代

語法： REPLACE [LOW_PRIORITY | DELAYE D] [INTO] tbl_name [(col_name,...)]

VALUES ({expr | DEFAULT},...),(...),...

10-2-1 資料操作

 SELECT ：查詢資料表紀錄或將它取出後存成文字檔

 語法： SELECT

[ALL | DISTINCT | DISTINCTROW ]

[HIGH_PRIORITY] [STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUF FER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_

ROWS]select_expr, ... [INTO OUTFILE 'file_name' exp ort_options | INTO DUMPFILE 'file_name']

[FROM table_references

SELECT

[WHERE where_definition]

[GROUP BY {col_name | expr | position} [ASC | DES C], ... [WITH ROLLUP]]

[HAVING where_definition]

[ORDER BY {col_name | expr | position} [ASC | DES C] , ...]

[LIMIT {[offset,] row_count | row_count OFFSET offse t}]

10-2-1 資料操作

 JOIN ：結合兩個以上之關聯型資料表

語法： Select 指令內 From 之 table_reference 可搭配 下列型式：

table_reference, table_reference

table_reference [INNER | CROSS] JOIN table_referen ce [join_condition]

table_reference STRAIGHT_JOIN table_reference

table_reference LEFT [OUTER] JOIN table_reference

JOIN

table_reference NATURAL [LEFT [OUTER]] JOI N table_reference { OJ table_reference LEFT OUTER JOIN table_reference ON conditional_

expr }

table_reference RIGHT [OUTER] JOIN table_re ference [join_condition]

table_reference NATURAL [RIGHT [OUTER]] J

JOIN

 上述之 table_reference 被定義為：

tbl_name [[AS] alias] [[USE INDEX (key_list)] | [IGNORE INDEX (key_list)] | [FORCE INDEX (k ey_list)]]

 上述之 join_condition 被定義為：

ON conditional_expr | USING (column_list)

10-2-1 資料操作

 UNION ：將數個資料表結合為一個資料表，

然後輸出資料

語法： SELECT ...UNION [ALL | DISTINCT]SEL ECT ... [UNION [ALL | DISTINCT] SELECT ...]

10-2-1 資料操作



子查詢

語法：在 Select 指令之 Where 子句內，加入另 一個 Select 指令

用途：將在後面之 Select 指令的輸出結果，作為 前段 Select 指令之資料比對條件

10-2-1 資料操作

 TRUNCATE ：刪除整個資料表紀錄

語法： TRUNCATE TABLE tbl_name

10-2-1 資料操作



UPDATE ：更新單一資料表紀錄

語法一：

UPDATE [LOW_PRIORITY] [IGNORE] tbl_na me

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_definition][ORDER BY ...][LIM IT row_count]

10-2-1 資料操作



UPDATE ：以關聯型式更新資料表紀錄

語法二：

UPDATE [LOW_PRIORITY] [IGNORE] tbl_nam e [, tbl_name ...] SET col_name1= expr1 [, col _name2 =expr2 ...] [WHERE where_definitio n]

10-2-2 資料定義

1. ALTER DATABASE

語法：

ALTER {DATABASE | SCHEMA} [db_name]alte r_specification [, alter_specification] ...

其中 alter_specification 有下述選項：

[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

用途：修改資料庫定義及屬性

10-2-2 資料定義

2. ALTER TABLE

語法： ALTER [IGNORE] TABLE tbl_name alter _specification [, alter_specification] ...

其中 alter_specification 有下述選項：

ADD [COLUMN] column_definition [FIRST | AF TER col_name ]

| ADD [COLUMN] (column_definition,...)

ALTER TABLE ^{( 續 )}

| ADD [CONSTRAINT [symbol]]

PRIMARY KEY [index_type] (index_col_nam e,...)

| ADD [CONSTRAINT [symbol]] UNIQUE [index_na me] [index_type] (index_col_name,...)

| ADD [FULLTEXT|SPATIAL] [index_name] (index_col_

name,...)

| ADD [CONSTRAINT [symbol]]

FOREIGN KEY [index_name] (index_col_name,...)

ALTER TABLE( 續 )

| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

| CHANGE [COLUMN] old_col_name

column_definition [FIRST|AFTER col_name]

| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

ALTER TABLE ^{( 續 )}

| DROP [COLUMN] col_name

| DROP PRIMARY KEY

| DROP INDEX index_name

| DROP FOREIGN KEY fk_symbol

| DISABLE KEYS

| ENABLE KEYS

| RENAME [TO] new_tbl_name

| ORDER BY col_name

ALTER TABLE ^{( 續 )}

| CONVERT TO CHARACTER SET charset_na me [COLLATE collation_name]

| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]

| DISCARD TABLESPACE | IMPORT TABLESPACE | table_options

10-2-2 資料定義

3. CREATE DATABASE ：建立資料庫 ( 或綱要

集 )

語法： CREATE {DATABASE | SCHEMA} [IF N OT EXISTS] db_name [create_specification [, create_specification] ...]

其中 create_specification 有下述選項： [D EFAULT] CHARACTER SET charset_name

| [DEFAULT] COLLATE collation_name

10-2-2 資料定義

4. CREATE INDEX ：建立索引表

語法： CREATE [UNIQUE|FULLTEXT|SPATIA L] INDEX index_name [USING index_type] O N tbl_name (index_col_name,...)

其中 index_col_name 可定義為： col_name [(l ength)] [ASC | DESC]

10-2-2 資料定義

5. CREATE TABLE ：建立資料表

語法： CREATE [TEMPORARY] TABLE [IF NOT EXI STS] tbl_name [(create_definition,...)] [table_optio ns] [select_statement]

其中 create_definition 由下列元素組成：

5.1 column_definition 5 .2 column_definition

5.3 type

5.6 reference_option 5.7 table_options:

table_option table_option] ...

CREATE TABLE

5.1 column_definition 可定義為：

| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_na me,...)

| KEY [index_name] [index_type] (index_col_name,...) | INDEX [index_name] [index_type] (index_col_name,...)

| [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_typ e] (index_col_name,...)

| [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...) | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_

name,...) [reference_definition]

CREATE TABLE

5.2 column_definition 可定義為：

col_name type [NOT NULL | NULL] [DEFAULT default_v alue]

[AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'st ring']

[reference_definition]

CREATE TABLE

5.3 type 可定義為：

TINYINT[(length)] [UNSIGNED] [ZEROFILL]

| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]

| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]

| INT[(length)] [UNSIGNED] [ZEROFILL]

| INTEGER[(length)] [UNSIGNED] [ZEROFILL]

| BIGINT[(length)] [UNSIGNED] [ZEROFILL]

| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]

5.3 type

| DATE | TIME | TIMESTAMP | DATETIME | CHAR(length) [BINARY | ASCII | UNICODE]

| VARCHAR(length) [BINARY]

| TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT | TEXT | MEDIUMTEXT | LONGTEXT | ENUM(value1,value2,value3,...)

| SET(value1,value2,value3,...) | spatial_type

CREATE TABLE

5.4 index_col_name 可定義為：

col_name [(length)] [ASC | DESC]

5.5 reference_definition 可定義為：

REFERENCES tbl_name [(index_col_name,...)]

[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]

[ON DELETE reference_option]

CREATE TABLE

5.6 reference_option 可定義為：

RESTRICT | CASCADE | SET NULL | NO ACTI ON | SET DEFAULT

5.7 table_options:

table_option [table_option] ...

5.9 select_statement 可定義為：

[IGNORE | REPLACE] [AS] SELECT ...

CREATE TABLE

5.8 table_option 可定義為：

{ENGINE|TYPE} = {BDB|HEAP| ISAM|InnoDB| MERGE| MRG _MYISAM| MYISAM}

| AUTO_INCREMENT = value

| AVG_ROW_LENGTH = value

| CHECKSUM = {0 | 1}

| COMMENT = 'string'

| MAX_ROWS = value

| MIN_ROWS = value

5.8 table_option

| ROW_FORMAT = {DEFAULT| DYNAMIC| FIXED| COM PRESSED| REDUNDANT| COMPACT}

| RAID_TYPE = { 1 | STRIPED | RAID0 } RAID_CHUNKS = value

RAID_CHUNKSIZE = value

| UNION = (tbl_name[,tbl_name]...)

| INSERT_METHOD = { NO | FIRST | LAST }

| DATA DIRECTORY = 'absolute path to directory'

| INDEX DIRECTORY = 'absolute path to directory'

| [DEFAULT] CHARACTER SET charset_name [COLLAT

10-2-2 資料定義

6. DROP DATABASE ：刪除資料庫 ( 或綱要集 )

語法： DROP {DATABASE | SCHEMA}

[IF EXISTS] db_name)

7. DROP INDEX ：刪除索引表

語法： DROP INDEX index_name ON tbl_name

10-2-2 資料定義

8. DROP TABLE ：刪除資料表

語法： DROP [TEMPORARY] TABLE [IF EXISTS]

tbl_name [, tbl_name] ...

[RESTRICT | CASCADE]

9. RENAME TABLE ：資料表改名

語法： RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...

10-2-3 公用指令

1. DESCRIBE ：顯示資料表欄位資訊

語法： {DESCRIBE | DESC} tbl_name [col_nam e | wild]

2. USE ：啟用（切換）資料庫

語法： USE db_name

10-2-4 交易與鎖表指令

1. START TRANSACTION/COMMIT/ROLLBACK

語法：指令範例

SET AUTOCOMMIT=0; （將自動確認開關關閉）

START TRANSACTION; （與上列指令等效）

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;

UPDATE table2 SET summary=@A WHERE type=1;

（更新資料）

COMMIT; （更新確認）

ROLLBACK; 取消更新）

用途：用於交易型資料表紀錄更新，例如 InnoDB 或 BDB 資

10-2-4 交易與鎖表指令

2. LOCK TABLES/UNLOCK TABLES

語法： LOCK TABLES tbl_name [AS alias] {RE AD [LOCAL] | [LOW_PRIORITY] WRITE} [, t bl_name [AS alias] {READ [LOCAL] | [LOW_P RIORITY] WRITE}] ...;UNLOCK TABLES;

用途：異動資料時鎖定資料表，資料處理完後再 予以解鎖

10-2-4 交易與鎖表指令

3. SET TRANSACTION

語法： SET [GLOBAL | SESSION] TRANSACTI ON ISOLATION LEVEL{ READ UNCOMMITT ED | READ COMMITTED | REPEATABLE RE AD | SERIALIZABLE }

用途：設定交易隔離模式，下一個交易指令正式 生效

10-2-5 資料庫管理指令

1. 帳號管理指令 2. 資料表管理

3. SET( 設定變數值 )

4. SHOW( 顯試示環境參數 ) 5. 其他管理類

6. 伺服器管理指令

1. 帳號管理指令

DROP USER ：刪除帳號

語法： DROP USER user [, user] ...

REVOKE ：撤除權限

語法： REVOKE priv_type [(column_list)] [, priv_type [(column _list)]] ...

ON {tbl_name | * | *.* | db_name.*}

FROM user [, user] ...

或： REVOKE ALL PRIVILEGES, GRANT OPTION FROM us er [, user] ...

SET PASSWORD ：設定或變更密碼

語法： SET PASSWORD = PASSWORD (' 密碼’ )

1. 帳號管理指令

GRANT ：授與權限

語法： GRANT priv_type [(column_list)] [, priv_type [(colum n_list)]] ...

ON {tbl_name | * | *.* | db_name.*}

TO user [IDENTIFIED BY [PASSWORD] 'password']

[, user [IDENTIFIED BY [PASSWORD] 'password']] ...

[REQUIRE NONE | [{SSL| X509}] [CIPHER 'cipher' [AN D]] [ISSUER 'issuer' [AND]] [SUBJECT 'subject']]

[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR

2. 資料表管理

ANALYZE TABLE ：分析資料表

語法： ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] T ABLE tbl_name [, tbl_name] ...

BACKUP TABLE ：備份資料表

語法： BACKUP TABLE tbl_name [, tbl_name] ... TO '/pa th/to/backup /directory'

CHECK TABLE ：檢查資料表

語法： CHECK TABLE tbl_name [, tbl_name] ... [option]

...option = {QUICK | FAST | MEDIUM | EXTENDED | C

2. 資料表管理

CHECKSUM TABLE ：提示 MyISAM 資料表之 checks um 值

語法： CHECKSUM TABLE tbl_name [, tbl_name] ... [ Q UICK | EXTENDED ]

OPTIMIZE TABLE ：整理資料表

語法： OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] T ABLE tbl_name [, tbl_name] ...

2. 資料表管理

REPAIR TABLE ：修復資料表

語法： REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TA BLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED]

[USE_FRM]

RESTORE TABLE ：將 Backup 資料表重新載入

語法： RESTORE TABLE tbl_name [, tbl_name] ... FRO M '/path/to/backup/directory'

3. SET

語法： SET variable_assignment [, variable_ass ignment] ...

其中 variable_assignment 可定義為：

user_var_name = expr

| [GLOBAL | SESSION] system_var_name = expr

| @@[global. | session.]system_var_name = expr

4. SHOW

用於顯示系統參數設定值或狀態值

4.1 SHOW CHARACTER SET

語法： SHOW CHARACTER SET [LIKE ‘pattern’]

用途：顯示所有可用字元集

4.2 SHOW COLLATION

語法： SHOW COLLATION [LIKE ‘pattern’]

用途：顯示所有可用字元集及其附屬字元集

4.3 SHOW COLUMNS

語法： SHOW [FULL] COLUMNS FROM tbl_name [FRO M db_name] [LIKE 'pattern']

用途：顯示資料表欄位資訊

4. SHOW

4.4 SHOW CREATE DATABASE

語法： SHOW CREATE {DATABASE | SCHEMA} db_name 用途：顯示建立資料庫指令

4.5 SHOW CREATE TABLE

語法： SHOW CREATE TABLE tbl_name 用途：顯示建立資料表指令

4.6 SHOW DATABASES

語法： SHOW {DATABASES | SCHEMAS} [LIKE 'pattern']

4. SHOW

4.7 SHOW ENGINES

語法： SHOW [STORAGE] ENGINES 用途：顯示儲存引擎資訊

4.8 SHOW ERRORS

語法一： SHOW ERRORS [LIMIT [offset,] row_count]

用途：顯示錯誤信息

語法二： SHOW COUNT(*) ERRORS 用途：計算錯誤個數

4.9 SHOW GRANTS

語法： SHOW GRANTS FOR user 用途：顯示指定帳戶之權限

4. SHOW

4.10 SHOW INDEX

語法： SHOW INDEX FROM tbl_name [FROM db_name]

用途：顯示指定資料表之索引表資訊

4.11 SHOW INNODB STATUS

語法： SHOW INNODB STATUS

用途：顯示 INNODB 資料表之完整資訊

4.12 SHOW LOGS

語法： SHOW [BDB] LOGS

4. SHOW

4.13 SHOW PRIVILEGES

語法： SHOW PRIVILEGES

用途：顯示目前伺服器支援的權限型態資訊

4.14 SHOW PROCESSLIST

語法： SHOW [FULL] PROCESSLIST 用途：顯示目前連線中的執行緒

4.15 SHOW STATUS

語法： SHOW STATUS [LIKE 'pattern']

4. SHOW

4.16 SHOW TABLE STATUS

語法： SHOW TABLE STATUS [FROM db_name] [LIKE 'patt ern']

用途：顯示資料表的狀態資訊，類似 Show Table 指令但更完 整

4.17 SHOW TABLES

語法： SHOW [FULL|OPEN] TABLES [FROM db_name] [LIK E 'pattern']

用途：顯示資料表（不包含暫存表）的狀態資訊

4. SHOW

4.19 SHOW WARNINGS

語法一： SHOW WARNINGS [LIMIT [offset,] row_count 用途：顯示錯誤、警告及備註等信息

語法二： SHOW COUNT(*) WARNINGS 用途：顯示錯誤、警告及備註等信息之個數

5. 其他管理類

CACHE INDEX

語法： CACHE INDEX tbl_index_list [, tbl_index_list] ... IN ke y_cache_name

其中 tbl_index_list 可定義為： tbl_name [[INDEX|KEY] (ind ex_name[, index_name] ...)]

用途：將索引表上存至指定之快取內，只適用於 MyISAM 資 料表

KILL

5. 其他管理類

FLUSH

語法： FLUSH [LOCAL | NO_WRITE_TO_BINLOG] flush_op tion [, flush_option] ...

其中 flush_option 可以設定為： HOSTS ， DES_KEY_FILE

， LOGS ， PRIVILEGES ， QUERY CACHE ， STATUS

， {TABLE | TABLES} [tbl_name [, tbl_name] ...] ， TABLE S WITH READ LOCK ， USER_RESOURCES

用途：將快取資訊沖回至對應檔案

5. 其他管理類

LOAD INDEX INTO CACHE

語法： CACHE INDEX tbl_index_list [, tbl_index_list] ... IN ke y_cache_name

其中 tbl_index_list 可以設定為： tbl_name [[INDEX|KEY]

(index_name[, index_name] ...)]

用途：將索引表預存至指定之快取內，只適用於 MyISAM 資 料表

RESET

語法： RESET reset_option [, reset_option] ...

6. 伺服器管理指令

6.1 主伺服器控制指令 6.2 備用伺服器控制指令

6.1 主伺服器控制指令

6.1.1 PURGE MASTER LOGS

語法一： PURGE {MASTER | BINARY} LOGS TO ‘log_n ame’

語法二： PURGE {MASTER | BINARY} LOGS BEFORE ‘ date’

用途：清除日誌檔（效期可指定為檔案序號或建檔日期）

6.1.2 RESET MASTER

語法： RESET MASTER

6.1 主伺服器控制指令

6.1.3 SET SQL_LOG_BIN

語法： SET SQL_LOG_BIN = {0|1}

用途：重設連線中（ local ）的日誌檔記錄功能（ 0 ： dis able ， 1 ： enable ）

6.1.4 SHOW BINLOG EVENTS

語法： SHOW BINLOG EVENTS [IN 'log_name'][FROM pos] [LIMIT [offset,] row_count]

用途：顯示指名日誌檔內之事件資訊

6.1.5 SHOW MASTER LOGS

語法： SHOW MASTER LOGS / SHOW BINARY LOGS

6.1 主伺服器控制指令

6.1.6 SHOW MASTER STATUS

語法： SHOW MASTER STATUS

用途：顯示主伺服器內之日誌檔狀態資訊

6.1.7 SHOW SLAVE HOSTS

語法： SHOW SLAVE HOSTS

用途：列示登錄在主伺服器之備用伺服器

6.2 備用伺服器控制指令

6.2.1 CHANGE MASTER TO

語法： CHANGE MASTER TO master_def [, master_def] … 其中 master_def 可定義為：

MASTER_HOST = 'host_name'

| MASTER_USER = 'user_name' | MASTER_PASSWOR D = 'password'

| MASTER_PORT = port_num

| MASTER_CONNECT_RETRY = count

| MASTER_LOG_FILE = 'master_log_name' | MASTER_LOG_POS = master_log_pos

6.2.1 CHANGE MASTER TO^{( 續 )}

| RELAY_LOG_FILE = 'relay_log_name'

| RELAY_LOG_POS = relay_log_pos

| MASTER_SSL = {0|1}

| MASTER_SSL_CA = 'ca_file_name'

| MASTER_SSL_CAPATH = 'ca_directory_name'

| MASTER_SSL_CERT = 'cert_file_name'

| MASTER_SSL_KEY = 'key_file_name'

| MASTER_SSL_CIPHER = 'cipher_list‘

6.2 備用伺服器控制指令

6.2.2 LOAD DATA FROM MASTER

語法： LOAD DATA FROM MASTER

用途：從主伺服器備份資料至備用伺服器，並更新 MAST

ER_LOG_FILE 和 MASTER_LOG_POS 兩個檔案，使 得備用伺服器可從正確位置開始更新資料

6.2.3 LOAD TABLE TBL_NAME FROM MASTER

語法： LOAD TABLE tbl_name FROM MASTER 用途：從主伺服器資料轉至備用伺服器

6.2 備用伺服器控制指令

6.2.4 RESET SLAVE

語法： RESET SLAVE 用途：清除 master.info ， relay-log.inf o files ，以及所有的 relay logs 檔，後重新建立新的 relay l og 檔。

6.2.5 SET GLOBAL SQL_SLAVE_SKIP_COUNTER

語法： SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n 用

途：跳過主伺服器接下來的 n 個事件，以便復原由於某些指

令造成備份作業之中斷，只適用於備用伺服器停止時

6.2.6 SHOW SLAVE STATUS

6.2 備用伺服器控制指令

6.2.7 START SLAVE

語法一： START SLAVE [thread_type [, thread_type] ... ] 語法二： START SLAVE [SQL_THREAD] UNTIL MASTE

R_LOG_FILE = 'log_name', MASTER_LOG_POS = log _pos

語法三： START SLAVE [SQL_THREAD] UNTIL RELAY _LOG_FILE = 'log_name', RELAY_LOG_POS = log_p os 其中 thread_type 可設為 IO_THREAD 或 SQL_TH READ

用途：啟動備用伺服器，其中 I/O thread 從主伺服器讀 取查詢指令並儲存在 relay log 檔，而 SQL thread 從 r

6.2 備用伺服器控制指令

6.2.8 STOP SLAVE

語法： STOP SLAVE [thread_type [, thread_type] ... ]

其中 thread_type 可設為 IO_THREAD 可設為 SQL_THR EAD

用途：停止備用伺服器

7 預儲指令

7.1 設定指令

語法： PREPARE stmt_name FROM preparable_stmt;

用途：設定指令並儲存在記憶體

7.2 執行預儲

指令語法： EXECUTE stmt_name [USING @var_name [,

@var_name] ...];

用途：執行預儲指令

7.3 刪除預儲

指令語法： {DEALLOCATE | DROP} PREPARE stmt_na me;

10-3 MySQL 專屬函數

10-3-1 比較函數與運算子 10-3-2 流程控制函數

10-3-3 字串函數

10-3-4 字串比較函數 10-3-5 數值函數

10-3-6 日期與時間函數 10-3-7 全文檢索

10-3-8 轉型函數 10-3-9 加解密函數

10-3-10 信息處理與通訊函數 10-3-11 GROUP BY 綜合函數

10-3-1 比較函數與運算子

 數值及字串比較結果將回傳 1 (TRUE) ， 0 (FALSE)

，或 NULL 。 若運算式同時包含數值及字串時，必要 的話，字串將自動轉型為數值，或數值將自動轉型為 字串

 字串比較時，將不區分大小寫。

 數值資料可用 CAST() 函數轉型為字串，字串可用 CO NVERT() 函數轉型為其他字元集。

10-3-1 比較函數與運算子

 運算式若包含函數時，例如 LEAST() 、 GREATEST() 等

，則回傳值不一定是 1 (TRUE), 0 (FALSE), 或 NULL ， 視傳入之引數而定，其比較規則如下述：

 若兩個引數有一或兩個為 NULL 時 , 則比較結果為 NULL ， 除非是使用 <=> （ NULL-safe 型）等號運算子

 若比較運算事中之兩個引數為字串，則以字串作比較

 若比較運算事中之兩個引數為整數，則以整數作比較

10-3-1 比較函數與運算子

 十六進位數值資料將以二進位字串而非以數值作比較

 若其中一個引數為 TIMESTAMP 或 DATETIME 型，另 一個為常數，則常數將被轉為 TIMESTAMP ，但此規則 不適用於 IN() 型式。為了安全起見，儘可能以相同型式 之資料作比較

 其餘型式之引數將使用浮點數（實數）作比較

10-3-1 比較函數與運算子



常用比較運算子：

= (Equal) ：等於

<=> ( NULL-safe) ：等於，但結果與 = 不完全一致

<>, != (Not equal) ：不等於

<= (Less than or equal) ：小於等於

< (Less than) ：小於

>= (Greater than or equal) ：大於等於

10-3-1 比較函數與運算子 ( 續 )



常用比較運算子：

IS Not boolean_value ：不屬於 True, False, 或 Null ( 未知 )

IS Null ：屬於虛值

IS Not Null ：不屬於虛值

expr BETWEEN min AND max ：介於 min 和 max 之間

expr Not BETWEEN min AND max ：不介於 min 和 max 之間

10-3-1 比較函數與運算子 ( 續 )



常用比較運算子：

expr IN (value,...) ：若 expr 在 (value,...) 之內傳回 1

，否則為 0

expr Not IN (value,...) ：若 expr 不在 (value,...) 之內則 傳回 1 ，否則為 0

ISNULL(expr) ：若運算式為虛值則傳回，否則為 0

INTERVAL(N,N1,N2,N3,...) ： N 大於後續整數值之順

10-3-2 流程控制函數

1. Case … When … Then … Else …End

語法： CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result]

END

說明：若 compare-value 等於 value ，則傳回對應段 Then 之 result ，否則傳回 Else 之 result

2. Case When … Then … Else …End

語法： CASE WHEN [condition] THEN result [WHEN [co ndition] THEN result ...] [ELSE result] END

說明：若 condition 為真，則傳回對應段 Then 之 result ， 否則傳回 Else 之 result

10-3-2 流程控制函數

3. IF

語法： IF(expr1, expr2, expr3)

說明：若 expr1 運算式為真，則傳回 expr2 ，否則傳回 ex pr3

4. IFNULL

語法： IFNULL(expr1,expr2)

說明：若 expr1 運算式非虛值，則傳回 expr1 ，否則傳回 expr2

10-3-3 字串函數

 ASCII (str) ：將 str 轉成 ASCII 碼

 BIN (N) ：將數值 N 轉成二進制碼

 BIT_LENGTH (str) ：計算 str 位元數

 CHAR (N,...) ：將數列 N 轉成字元組

 CHAR_LENGTH (str) ：計算 str 字元數，若為多位元字元 時，一個字元計數一次

 CHARACTER_LENGTH (str) ：與 CHAR_LENGTH 函數 用法相同

10-3-3 字串函數

 COMPRESS (string_to_compress) ：將 string_to_com press 字串壓縮， MySQL 需要有類似 zlib 壓縮函數

 CONCAT (str1,str2,...) ：將第二個及以後字串串接在第 一個字串後面

 CONCAT_WS (separator,str1,str2,...) ：將第二個及以 後字串串接在第一個字串後面，但是字串間須以 separato r 間隔

 CONV (N,from_base,to_base) ：將數值 N 從 from_bas e 進制轉成 to_base 進制表示，若 to_base 為負值則以

10-3-3 字串函數

 ELT (N,str1,str2,str3,...) ：以數值 N 來挑選後續對應 之字串

 FIELD (str,str1,str2,str3,...) ：以索引鍵 str 搜尋後續 之字串集合位置

 FIND_IN_SET (str,strlist) ：以索引鍵 str 搜尋後續字 串之對應位置

 HEX(N_or_S) ：與 CONV(N, 10, 16) 用法相同

 INSERT(str,pos,len,newstr) ：以 newstr 取代 str ， 取代位置從 pos 開始，長度為 len

 INSTR (str,substr) ：尋找 substr 在 str 之出現位置

10-3-3 字串函數

 LCASE(str) ：將 str 轉小寫，與 LOWER() 用法相同

 LEFT (str,len) ：取出字串左端字元，長度為 len

 LENGTH (str) ：計算字串 str 之位元數，雙位元字元計數 為 2

 LOAD_FILE (file_name) ：將文字檔匯入成字串，可直接 轉至文字型欄位

 LOCATE(substr,str) ， LOCATE(substr,str,pos) ：尋 找 substr 在 str 之出現位置，若有指定 pos ，則從 pos 開

10-3-3 字串函數

 LPAD (str,len,padstr) ：將 padstr 貼在 str 左方， 總長 度為 len ，若 str 長度大於 len ，則大於 len 之右方字元將 被剪除

 LTRIM (str) ：去除左方空白字元

 MID (str,pos,len) ：與 SUBSTR(str, pos, len) 用法相同

 OCT (N) ：將 N 轉成八進制表示

 ORD (str) ：若 str 為多位元組字元，則傳回 (1st byte cod e+2nd byte code*256+3rd byte code*256^2) 之編碼，若 為單位元組字元，則直接傳回 ASCII 碼

10-3-3 字串函數

 POSITION(substr IN str) ：與 LOCATE(substr,str) 用法相同

 QUOTE(str) ：將 str 前後加上單引號後再輸出

 REPEAT (str,n) ：將 str 重複輸出 n 次

 REPLACE (str,from_str,to_str) ：將 str 內之字元 fro m_str 以字元 to_str 取代

 REVERSE(str) ：將 str 之字元反向輸出

 RIGHT (str,len) ：取右端字元，長度為 len

10-3-3 字串函數

 RPAD (str,len,padstr) ：將 padstr 貼在 str 右方， 總 長度為 len ，若 str 長度大於 len ，則大於 len 之右方 字元將被剪除

 RTRIM (str) ：去除右方空白字元

 SPACE (N) ：產生 N 個空白字元

 SUBSTRING(str,pos) ， SUBSTRING(str,pos,len)

：取子字串，從 pos 開始取，若有指定 len ，則取 len 長度字元

 SUBSTRING_INDEX (str,delim,n) ：以分界字元 deli m 分割字串，取出 str 字串之 n 個元素

10-3-3 字串函數

 TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM]

str) ：

 將字串前後空白去除

 或將前後 (BOTH) 字元 remstr 去除

 或將前導 (LEADING) 字元去除

 或將尾端 (TRAILING) 字元去除

 UCASE(str) ：將 str 轉大寫

 UNCOMPRESS (str) ：將以 COMPRESS 壓縮之字串 s

10-3-4 字串比較函數

 CAST(N AS CHAR) ：將數值 N 轉為字元

 CONCAT(N) ：將數值 N 轉為字元

 expr LIKE pat [ESCAPE 'escape-char'] ：搜尋 exp r 是否有 pat 樣板字串，樣板字串可搭配” %” 或”

_” 使用。若元字串內有” %” 或” _” 字元，則需使 用” \%” 或” \_” 當樣板

 STRCMP(expr1,expr2) ：比較 expr1 與 expr2 是否 相同

10-3-5 數值函數

 三角函數： SIN(X) ， ASIN(X) ， COS(X) ， ACOS(X)

， TAN(X) ， ATAN(X) COT(X) ， DEGREES(X) ， RA DIANS(X)

 指數與對數： EXP(X) ， LN(X) ：自然對數， LOG(B, X)

：以 B 為底自然對數， LOG2(X) ， LOG10(X)

 ABS(X) ：求絕對值

 CEIL(X) ：比 X 大之整數

 CRC32(expr) ：求 CRC 檢查碼

10-3-5 數值函數

 FORMAT(X, D) ：將數值 X 格式化，採千位加逗號形 式，小數留 D 位

 MOD(N,M) ， N % M ， N MOD M ：求餘數

 PI() ：求圓周率

 POW(X,Y) ， POWER(X,Y) ：求 X 的 Y 次方值

 RAND() ：求亂數值， RAND(N) ：以 N 當種子之亂數 值

 ROUND(X) ：將 X 四捨五入 ， ROUND(X,D) ：將 X 四捨五入至小數 D 位

10-3-5 數值函數

 SIGN(X) ：符號函數，小於 0 時傳回 -1 ， 0 時傳回 0

，大於 0 時傳回 1

 SQRT(X) ：求方根

 TRUNCATE(X,D) ：捨去 D 位小數，若 D 為負值，

則 D 位以下之值取為 0

10-3-6 日期與時間函數

 ADDDATE(expr, days) ：求算 expr 加上 days 之日期

 ADDTIME(expr,expr2) ：求算 expr ( 日期時間 ) 加上 e xpr2 ( 時間 ) 之日期時間

 CONVER T_TZ(dt, from_tz, to_tz) ：將 dt 從時區 fro m_tz 轉換至時區 to_tz

 CURDATE() ：目前日期，此函數與 CURRENT_DATE 和 CURRENT_DATE() 同義

 CURTIME() ：目前時間，此函數與 CURRENT_TIME 和 CURRENT_TIME () 同義

10-3-6 日期與時間函數

 CURRENT_TIMESTAMP/ CURRENT_TIMESTAMP() ： 目前時戳，與 NOW() 同義

 DATE(expr) ：取出 expr 之日期

 DATEDIFF(expr,expr2) ：求算起始日期時間 (expr) 與截 止日期時間 expr2 之間隔日期，只計算日期部份

 DATE_ADD(date,INTERVAL expr type) ：計算 date 加 上間隔天數（或時、分、秒數）後之日期

 DATE_SUB(date,INTERVAL expr type) ：計算 date 減 去間隔天數（或時、分、秒數）後之日期

10-3-6 日期與時間函數

 DATE_FORMAT(date, format) ：將 date 轉換成對 應 format 之日期格式

 DAY(date) ：將 date 轉成數字型天數，與 DAYOFM ONTH() 同義

 DAYNAME(date) ：將 date 轉成文字型星期

 DAYOFWEEK(date) ：將 date 轉成數字型星期， 1 為星期日

 DAYOFYEAR(date) ： date 年度之天序

10-3-6 日期與時間函數

 EXTRACT(type FROM date) ：以 type 格式抽出數字型 日期資料，

type 可為： YEAR 、 YEAR_MONTH 、 DAY_MINUTE 、 MICROSECOND

 GET_FORMAT(DATE| TIME| DATETIME, ‘EUR’| ‘USA’|

‘JIS’| ‘ISO’| ‘INTERNAL’) ：取出指定格式化之日期

 HOUR(time) ：取出 time 引數之時數， 24 小時制

 LAST_DAY(date) ：取出 date 引數所屬月份最後一天之 日期

10-3-6 日期與時間函數

 MAKEDATE(year,dayofyear) ：將 dayofyear 天數 轉為 year 年度之日期

 MAKETIME(hour,minute,second) ：將三個引數轉 為時間型資料

 MICROSECOND(expr) ：取出 expr 之微秒數

 MINUTE(time) ：取出 time 之分鐘數

 MONTH(date) ：取出 date 之月數

 MONTHNAME(date) ：取出 date 之文字型月份

 QUARTER(date) ：取出 date 之季數

10-3-6 日期與時間函數

 SECOND(time) ：取出 time 之秒數

 STR_TO_DATE(str,format) ：將傳入之 str 轉成日期 時間， format 用法與 DATE_FORMAT() 相同

 SUBDATE(expr,days) ：將 expr 減去 days （天數）

之日期

 SUBTIME(expr,expr2) ：將 expr 減去 expr2 （時 間）之日期時間

 SYSDATE() ：與 NOW() 同義

10-3-6 日期與時間函數

 TIMEDIFF(expr,expr2) ：將 expr 減去 expr2 （時 間）之日期時間

 TIMESTAMP(expr) ：將 expr 轉為時戳

 TIME_TO_SEC(time) ：將 time 轉為秒數

 WEEK(date[,mode]) ：取出 date 之週數 WEEKDAY (date) ：傳回 date 之 週星期序毎 (0=Monday,..)

 WEEKOFYEAR(date) ：傳回 date 之週次

 YEAR(date) ：傳回 date 之年份，四位數

10-3-7 全文檢索



MyISAM 資料表可以執行全文檢索，前提是已 建立全文型索引表（ FULLTEXT ）



CHAR 、 VARCHAR 、 TEXT 等欄位皆可建 立全文型索引



全文檢索含數為 MATCH()

10-3-7 全文檢索

1. 單純型全文檢索

語法： MATCH (col1,col2,...) AGAINST (expr) 說明：依據 expr 之條件，依序比對 col1, col2,…

是否有符合 expr 之字串。 col1, col2,… 必須與 全文索引表鍵值欄位相同，比對時相關性高的 紀錄優先列出

10-3-7 全文檢索

2. 複合型全文檢索

語法： MATCH (col1,col2,...) AGAINST (expr IN BOOLEAN M ODE)

說明：依據 expr 之條件，依序比對 col1, col2,… 是否有符合 ex pr 之字串。 expr 可搭配下述控制符號來建立樣板：

+ ：必須包含此字串 - ：排除此字串

> < ：調整查詢結果之相關性， > 提升， < 降低

10-3-7 全文檢索

3. 隱含型全文檢索

語法： MATCH (col1,col2,...) AGAINST (expr W ITH QUERY EXPANSION)

說明：依據 expr 之條件，依序比對 col1, col2,…

是否有符合 expr 之字串，然後再以優先順位最 高之鍵值欄當鍵值，進行第二次查詢，符合一 和二次搜尋條件之紀錄，全部列出。

10-3-8 轉型函數

1. BINARY ：強制改變欄位或字串之比對方式，改為逐位元 組而非逐字比對方式，因此會區分大小寫

2. Lower() ， Upper() ：大小寫轉換函數

3. CAST(expr AS type) ， CONVERT(expr,type)

說明：將 expr 轉型為指定之 type 型態， type 可指定為下列資 料型態：

BINARY / CHAR / DATE / DATETIME /

SIGNED/ [INTEGER]/ TIME/ UNSIGNED [INTEGER]

10-3-9 加解密函數

1. AES_ENCRYPT(str, key_str) ：使用 AES （ Advanced Encryption Standard ）加密演算法將 str 字串加密， key_

str 預設長度為 128 位元，可放大為 256 位元，但加解密 時非常耗

2. AES_DECRYPT(crypt_str, key_str) ：為 AES_ENCRY PT 之解密函數。

註：為了避免連線時，需要將 key_str 在網路上傳送，事前可 將 key_str 預存在伺服端，然後以 @ 變數型式將 key_str 取 出，例如：

Select @key=”My_Key”;

10-3-9 加解密函數

3. ENCODE(str,pass_str) ：將 str 加密，加密後字元長度 與元字串相同，需搭配 BLOB 欄位儲存加密結果

4. DECODE(crypt_str,pass_str) ：為 ENCODE 之解密函 數

5. DES_ENCRYPT(str[,(key_num | key_str)]) ：使用 DES 加密演算法，前提為 MySQL 有支援 SSL 通訊協定。可直 接指定 key_str 當加密之 Key ，或從 DES 之 Key File 依指定的 key_num 參數選用對應之 key_str 。可在伺服器 參數 – des-key-file 指定 Key File

10-3-9 加解密函數

6. DES_DECRYPT(crypt_str[,key_str]) ：為 DES_EN CRYPT 之解密函數

7. MD5(str) ：使用 MD5 演算法計算 str 之檢查碼，回傳 值為 32 位元組字串

8. PASSWORD(str) ：伺服器認證用密碼函數

9. SHA(str) ：使用 160 位元之 key 計算 str 之檢查碼，

回傳值為 40 位元組字串

10-3-10 信息處理與通訊函數

1. BENCHMARK(count,expr) ：測試運算式 expr 共 count 次所需時間

2. CHARSET(str) ：傳回所使用之字元集

3. COLLATION(str) ：傳回所使用之字元集之子集

4. CONNECTION_ID() ：傳回執行緒代號

5. CURRENT_USER() ：傳回使用者帳號及登入主機

6. DATABASE() ：傳回工作中的資料庫名稱

10-3-10 信息處理與通訊函數

7. FOUND_ROWS() ：傳回前次 Select 指令之影響紀錄筆 數，為了正確計算筆數，需使用下列型式指令：

Select SQL_CALC_FOUND_ROWS * From TBL;

Select FOUND_ROWS();

8. LAST_INSERT_ID() ：取回自動遞增欄位（ AUTO_INCR EAMENT ）最末筆序號

9. LAST_INSERT_ID(id+1) ：取出 id 欄最末筆序號，加上 1 後傳回

10-3-10 信息處理與通訊函數

10. ROW_COUNT() ：傳回前次資料更新（ Update, Ins ert, Delete ）之紀錄筆數

11. SESSION_USER() ：與 USER() 同義 12. SYSTEM_USER() ：與 USER() 同義

13.USER() ：傳回連線主機與使用者帳號名稱 14.VERSION() ：傳回伺服器版次號碼

10-3-11 GROUP BY 綜合函數

1. AVG(expr) ：求 expr 之平均值

2. COUNT(expr) ：求 expr 之筆數， expr 可使用 * 表示 3. COUNT(DISTINCT expr,[expr...]) ：針對 expr,[exp

r...] 等資料，各只計算一次之紀錄筆數

4. GROUP_CONCAT(expr) ：依 Group By 子句，將 ex pr 欄值串接至指定長度，長度可在 group_concat_ma x_len 系統變數設定

10-3-11 GROUP BY 綜合函數

( 續 )

5. MIN(expr) ：求 expr 之最小值 6. MAX(expr) ：求 expr 之最大值

7. STD(expr) ， STDDEV(expr) ：求 expr 之標準差 8. SUM(expr) ：求 expr 之總和

9. VARIANCE(expr) ：求 expr 之變異數