MySQL快速學習入門

mysql> drop database yiibai_tutorials1;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| yiibai             |
+--------------------+
6 rows in set (0.00 sec)

到目前為止，我們只是從一個表讀取資料。這是相對簡單的，但在大多數現實中的MySQL使用，需要從多個表中，在單個查詢獲得資料。

可以在單個SQL查詢中使用多個表。連線MySQL中的行在兩個或多個表到一個表。

可以使用Join在SELECT，UPDATE和DELETE語句加入MySQL表。我們將看到LEFT JOIN的例子, 這與簡單的MySQL JOIN有所不同。

假設我們有兩個表 tcount_tbl 和 tutorials_tbl，在資料庫：test ，完整列表如下：

範例

試試下面的例子：

root@host# mysql -u root -p password;
Enter password:
mysql> use test;
Database changed
mysql> create table tcount_tbl(
    -> tutorial_author varchar(24) not null,
    -> tutorial_count int(10) not null default 0 );
Query OK, 0 rows affected

mysql> INSERT INTO tcount_tbl (tutorial_author, tutorial_count) VALUES('mahran', 10);
mysql> INSERT INTO tcount_tbl (tutorial_author, tutorial_count) VALUES('mahnaz', 0);
mysql> INSERT INTO tcount_tbl (tutorial_author, tutorial_count) VALUES('Jen',    0);
mysql> INSERT INTO tcount_tbl (tutorial_author, tutorial_count) VALUES('Gill', 20);
mysql> INSERT INTO tcount_tbl (tutorial_author, tutorial_count) VALUES('Paul', 10);
mysql> INSERT INTO tcount_tbl (tutorial_author, tutorial_count) VALUES('yiibai', 10);

mysql> SELECT * FROM tcount_tbl;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran          |             10 |
| mahnaz          |              0 |
| Jen             |              0 |
| Gill            |             20 |
| Paul            |             10 |
| yiibai          |             10 |
+-----------------+----------------+
6 rows in set (0.01 sec)
mysql> SELECT * from tutorials_tbl;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | Learn PHP      | Paul            | 2015-07-17      |
|           2 | Learn MySQL    | Saya            | 2015-07-17      |
|           3 | Learning JAVA  | yiibai          | 2007-05-06      |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.00 sec)
mysql>

現在，我們可以寫一個SQL查詢來連線這兩個表。此查詢將從表tutorials_tbl和tcount_tbl 選擇所有作者的教學數量。

mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
    -> FROM tutorials_tbl a, tcount_tbl b
    -> WHERE a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
|           1 | Paul            |             10 |
|           3 | Yiibai          |             10 |
+-------------+-----------------+----------------+
2 rows in set (0.01 sec)
mysql>

18、MySQL NULL值

我們已經看到SQL SELECT命令和WHERE子句一起使用，來從MySQL表中提取資料，但是，當我們試圖給出一個條件，比較欄位或列值設定為NULL，它確不能正常工作。

為了處理這種情況，MySQL提供了三大運算子

• IS NULL: 如果列的值為NULL，運算結果返回 true

• IS NOT NULL: 如果列的值不為NULL，運算結果返回 true

• <=>: 運算子比較值，(不同於=運算子)即使兩個空值它返回 true

涉及NULL的條件是特殊的。不能使用= NULL或！= NULL來匹配查詢列的NULL值。這樣的比較總是失敗，因為它是不可能告訴它們是否是true。 甚至 NULL = NULL 也是失敗的。

要查詢列的值是或不是NULL，使用IS NULL或IS NOT NULL。

在命令提示字元，使用NULL值：

假設在 test 資料庫中的表 tcount_tbl 它包含兩個列 tutorial_author 和 tutorial_count， 其中 tutorial_count 的值為NULL表明其值未知：

範例

試試下面的例子：

root@host# mysql -u root -p password;
Enter password:
mysql> use test;
Database changed

mysql> drop table tcount_tbl;
Query OK, 0 rows affected (0.05 sec)
mysql> create table tcount_tbl
    -> (
    -> tutorial_author varchar(40) NOT NULL,
    -> tutorial_count  INT
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO tcount_tbl
    -> (tutorial_author, tutorial_count) values ('mahran', 20);
mysql> INSERT INTO tcount_tbl
    -> (tutorial_author, tutorial_count) values ('mahnaz', NULL);
mysql> INSERT INTO tcount_tbl
    -> (tutorial_author, tutorial_count) values ('Jen', NULL);
mysql> INSERT INTO tcount_tbl
    -> (tutorial_author, tutorial_count) values ('Gill', 20);

mysql> SELECT * from tcount_tbl;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran          |             20 |
| mahnaz          |           NULL |
| Jen             |           NULL |
| Gill            |             20 |
+-----------------+----------------+
4 rows in set (0.00 sec)

mysql>

可以看到，= 及 != 不能與 NULL值不能正常工作(匹配)如下：

mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL;
Empty set (0.00 sec)
mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL;
Empty set (0.01 sec)

要查詢記錄中，其中 tutorial_count 列的值是或不是NULL，查詢應該這樣寫：

mysql> SELECT * FROM tcount_tbl 
    -> WHERE tutorial_count IS NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahnaz          |           NULL |
| Jen             |           NULL |
+-----------------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT * from tcount_tbl 
    -> WHERE tutorial_count IS NOT NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran          |             20 |
| Gill            |             20 |
+-----------------+----------------+
2 rows in set (0.00 sec)

19、MySQL正規表示式

在前面我們已經看到了MySQL的模式匹配：LIKE ...%. MySQL支援另一種型別的模式匹配的操作基於正規表示式和正規表示式運算子。如果知道PHP或Perl，那麼它是非常簡單的，因為這匹配非常相似於指令碼正規表示式。

以下是模式的表格，其可以連同REGEXP運算子使用。

範例

現在根據上面的表格，可以在不同的裝置型別用SQL查詢來滿足要求。在這裡，列出一些的理解。考慮有一個表稱為 person_tbl，它是有一個 name 欄位：

查詢查詢所有以 “st” 開頭的名字：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查詢找到所有以 'ok' 結尾的名字

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查詢查詢其中包含 'mar' 所有的名字

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查詢查詢以元音 'ok' 結尾的所有名稱

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

20、MySQL事務

事務是資料庫處理操作，其中執行就好像它是一個單一的一組有序的工作單元。換言之，事務將永遠不會是完全的，除非在組內每個單獨的操作是成功的。如果事務中的任何操作失敗，整個事務將失敗。

實際上，許多SQL查詢組成到一個組，將執行所有這些一起作為事務的一部分。

事務性質

事務具有以下四個標準屬性，通常由首字母縮寫ACID簡稱：

• 原子性: 確保了工作單位中的所有操作都成功完成; 否則，事務被中止，在失敗時會被回滾到事務操作以前的狀態。

• 一致性：可確保資料庫在正確的更改狀態在一個成功提交事務。

• 隔離: 使事務相互獨立地操作。

• 永續性: 確保了提交事務的結果或系統故障情況下仍然存在作用。

在MySQL中，事務以BEGIN WORK語句開始開始工作，並使用COMMIT或ROLLBACK語句結束。SQL命令在開始和結束語句之間構成大量事務。

提交和回滾

這兩個關鍵字Commit和Rollback主要用於MySQL的事務。

• 當一個成功的事務完成後，COMMIT命令發出的變化對所有涉及的表將生效。

• 如果發生故障，ROLLBACK命令發出後，事務中參照的每個表將恢復到事務開始之前的狀態。

可以通過設定AUTOCOMMIT這個對談變數控制事務的行為。如果AUTOCOMMIT設定為1(預設值)，那麼每個SQL語句(在事務或不在事務)被認為是一個完整的事務並提交，在預設情況下是在當它完成時。當AUTOCOMMIT設定為0，通過發出SET AUTOCOMMIT=0命令, 隨後的一系列語句就像一個事務，但並沒有任何活動被提交直到一個明確的發出 COMMIT 語句。

可以通過使用PHP 的 mysql_query()函式執行這些SQL命令。

關於事務通用範例

此事件序列是獨立於所使用的程式設計語言; 邏輯路徑可以是任何程式設計語言，使用它在建立應用程式中建立。

可以通過使用 mysql_query()函式執行這些PHP SQL命令

• 開始事務是通過發出SQL命令BEGIN WORK

• 類似發出的一個或多個SQL命令 SELECT, INSERT, UPDATE 或 DELETE.

• 根據要求，檢查是否有任何錯誤

• 如果有錯誤，發出ROLLBACK命令，否則執行COMMIT命令。

在MySQL的事務安全表型別

不能直接使用事務，可以使用但它們沒有安全保障。如果打算使用事務在MySQL程式設計，那麼需要使用一個特殊的方式來建立表。 有許多型別的表其支援事務，但目前最流行的一種是：InnoDB.

支援InnoDB表需要特定的編譯引數，在原始碼編譯MySQL時。如果MySQL版本不支援InnoDB，得要求網際網路服務提供商建立一個版本的MySQL的InnoDB錶型別的支援，或 下載並安裝MySQL-Max二進位制分發的Windows版本，或者Linux/UNIX開發環境中工作的表型別。

如果你的MySQL安裝支援InnoDB表，簡直就是一個TYPE= InnoDB的定義新增到表建立語句後面。例如，下面的程式碼建立一個名為 tcount_tbl 的 InnoDB 型別表：

root@host# mysql -u root -p password;
Enter password:
mysql> use test;
Database changed
mysql> create table tcount_tbl
    -> (
    -> tutorial_author varchar(40) NOT NULL,
    -> tutorial_count  INT
    -> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

請檢查下面的連結了解更多有關: InnoDB

可以使用其它型別的表 GEMINI 或 BDB, 但它取決於安裝MySQL時，是否支援這兩種型別。

21、MySQL Alter命令

當想改變表名，MySQL的ALTER命令非常有用, 在新增或刪除任何表欄位到現有列在表中。

讓我們開始建立一個表為 testalter_tbl。

root@host# mysql -u root -p password;
Enter password:
mysql> use test;
Database changed
mysql> create table testalter_tbl
    -> (
    -> i INT,
    -> c CHAR(1)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

刪除，新增或重新定義列

假設想要從上面MySQL的表中刪除一個現有列，那麼使用ALTER命令以及DROP子句如下：

mysql> ALTER TABLE testalter_tbl  DROP i;

如果在表中列是唯一的一個，那麼DROP將無法正常工作。

要新增列，使用ADD並指定列定義。下面的語句將儲存到 i 列：testalter_tbl:

mysql> ALTER TABLE testalter_tbl ADD i INT;

發出這條語句後，testalter將包含當第一次建立表時的兩列, 但不會有完全一樣的結構。這是因為新的列被新增到表中時，預設排在最後一列。因此，即使 i 列原來是 mytbl 的第一列，但現在它是最後一列。

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

若要指示列在表中的特定位置，要麼使用FIRST把它放在第一列或AFTER col_name 來指定新的列應放置col_name之後。 試試下面的ALTER TABLE語句，使用 SHOW COLUMNS 看看有什麼影響：

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

FIRST 和 AFTER 只能在ADD子句中使用。這意味著，如果你想在表中重新排位現有列，首先必須刪除(DROP )它，然後在新的位置新增(ADD)它。

更改列定義或名稱

要改變列的定義，使用MODIFY 或CHANGE 子句以及ALTER命令。 例如, 要改變欄位 c 從 CHAR(1) 修改為 CHAR(10), 那麼可以這樣做：

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

CHANGE語法可能有點不同。CHANGE關鍵字後的名稱是要修改的列，然後指定新的定義，其中包括新的名稱。試試下面的例子：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

如果現在使用CHANGE轉換i列從BIGINT為INT，但不改變列名，該語句執行預期：

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE影響Null和預設值屬性

當使用 MODIFY 或 CHANGE修改列，還可以指定該列是否能為NULL值以及它的預設值。事實上，如果你不這樣做，MySQL也會自動分配這些屬性值。

下面是一個例子，這裡 NOT NULL列將使用100作為預設值。

mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

如果不使用上面的命令，那麼MySQL將填補使用NULL 來填充所有列值。

更改列的預設值

可以使用ALTER命令更改任何列的預設值。嘗試下面的例子。

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | 1000    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

從任何一列刪除預設的約束，可以使用ALTER命令以及DROP子句。

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

更改表型別

可以通過使用ALTER命令以及TYPE子句修改表的型別。試試下面的例子，將 testalter_tbl 的型別更改為MyISAM 表型別。

若想要知道一個表的當前型別，那麼可使用 SHOW TABLE STATUS 語句。

mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;
mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G
*************************** 1. row ****************
           Name: testalter_tbl
           Type: MyISAM
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 25769803775
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-06-03 18:35:36
    Update_time: 2015-06-03 18:35:36
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

重新命名表

要重新命名表，使用ALTER TABLE語句的RENAME選項。試試下面的例子是用來重新命名testalter_tbl為alter_tbl。

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

可以使用ALTER命令來建立並在MySQL的檔案刪除索引。我們將在下一章看到此功能。

22、MySQL索引

資料庫索引是一種資料結構，目的是提高表的操作速度。可以使用一個或多個列，提供快速隨機查詢和存取記錄的高效排序來建立索引。

要建立的索引，應當認為哪列將用於使SQL查詢，建立對這些列的一個或多個索引。

實際上，索引也是表，其中儲存主鍵或索引欄位的指標並指向每個記錄到實際的表的型別。

使用者無法看到索引，它們只是用來加速查詢，並將被用於資料庫搜尋引擎在查詢記錄時提高速度。

INSERT和UPDATE語句需要更多的時間來建立索引，作為在SELECT語句快速在這些表上操作。其原因是，在執行插入或更新資料時，資料庫需要將插入或更新索引值也更新。

簡單和唯一索引

可以在表上建立唯一值索引。唯一索引意味著兩行不能有相同的索引值。下面是在表上建立索引的語法

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...);

可以使用一個或多個列來建立索引。例如，我們可以使用tutorial_author 來建立一個 tutorials_tbl 索引。

CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author)

可以在表上建立一個簡單的索引。建立簡單的索引只是省略UNIQUE關鍵字。簡單的索引可以在表中重複的值。

如果想索引的列的值按降序排列，可以列名之後新增保留字DESC。

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author DESC)

使用ALTER命令來新增和刪除索引

有四種型別的索引可以新增到一個表：

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 此語句新增一個主鍵(PRIMARY KEY)，這意味著索引值必須是唯一的，而不能為空。

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 此語句建立一個索引的量的值必須是唯一的(除了不能使用NULL值，其它的可以出現多次)。

• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 這增加普通的索引，其中的任何值的出現多次。

• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): 這將建立一個用於文字搜尋目的一種特殊的FULLTEXT索引。

下面是一個新增索引到現有表的例子。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

您可以通過使用ALTER命令以及DROP子句來刪除索引。試試下面的例子，用來刪除上面建立的索引。

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

使用ALTER命令來新增和刪除PRIMARY KEY

也可以用同樣的方法新增主鍵。但要在列確保主鍵正常使用，需要指定使用 NOT NULL。

下面是一個例子新增主鍵在現有的表。列需要新增 NOT NULL 屬性，然後再新增為一個主鍵。

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

可以使用ALTER命令刪除主鍵如下：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

要刪除索引的不是主鍵，必須指定索引名。

顯示索引資訊

可以使用SHOW INDEX命令，列出所有與表相關的索引。 垂直格式輸出(由\G指定)，這是經常有用的語句，以避免長線概括輸出：

試試下面的例子：

mysql> SHOW INDEX FROM table_name\G
........

23、MySQL臨時表

臨時表可能在某些情況下是非常有用的，以保持臨時資料。 臨時表的最重要的事情是，當前用戶端對談結束時，它們將會被刪除。

臨時表是在MySQL版本3.23中增加的。如果使用MySQL 3.23之前的舊版本，是不能使用臨時表的，但可以使用堆表。

如前所述，臨時表將只持續在對談存在時。如果在執行一個PHP指令碼程式碼，臨時表會自動在指令碼執行完畢時刪除。如果是通過MySQL用戶端程式連線到MySQL資料庫伺服器, 那麼臨時表會一直存在，直到關閉用戶端或手動銷毀表。

範例

下面是一個例子，顯示臨時表的使用。

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

當發出SHOW TABLES命令，臨時表不會被列在表的列表中。現在，如果登出MySQL對談，然後發出SELECT命令，那麼會發現在資料庫中沒有可用的資料。即使是臨時表也不存在了。

刪除臨時表

預設情況下，當資料庫連線被終止，所有的臨時表被MySQL刪除。儘管如此，如果想在結束對談前刪除它們，那麼可通過發出DROP TABLE命令。

以下是刪除一個臨時表的例子：

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table 'test.SalesSummary' doesn't exist

24、MySQL複製表

可能有一種情況，當需要一個表精確的副本，那麼CREATE TABLE... SELECT可能達不到目的，因為副本必須包含相同的索引，預設值等等。

可以通過以下步驟處理這種情況：

• 使用 SHOW CREATE TABLE以獲得CREATE TABLE語句用於指定源表的結構，索引和所有其它的。

• 修改語句用來更改表名為克隆表並執行該語句。通過這種方式，將有確切的克隆表。

• 或者，如果需要複製以及表的內容，再發出一個INSERT INTO... SELECT語句。

範例

試試下面的例子來建立表tutorials_tbl的一個克隆表。

步驟1:

獲取有關表的完整結構。

mysql> SHOW CREATE TABLE tutorials_tbl \G;
*************************** 1. row ***************************
       Table: tutorials_tbl
Create Table: CREATE TABLE `tutorials_tbl` (
  `tutorial_id` int(11) NOT NULL auto_increment,
  `tutorial_title` varchar(100) NOT NULL default '',
  `tutorial_author` varchar(40) NOT NULL default '',
  `submission_date` date default NULL,
  PRIMARY KEY  (`tutorial_id`),
  UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) TYPE=MyISAM
1 row in set (0.00 sec)

ERROR:
No query specified

步驟2:

重新命名該表，並建立另一個表。

mysql> CREATE TABLE `clone_tbl` (
  -> `tutorial_id` int(11) NOT NULL auto_increment,
  -> `tutorial_title` varchar(100) NOT NULL default '',
  -> `tutorial_author` varchar(40) NOT NULL default '',
  -> `submission_date` date default NULL,
  -> PRIMARY KEY  (`tutorial_id`),
  -> UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected (1.80 sec)

步驟3:

執行步驟2後，將在資料庫中建立一張克隆表。如果想從舊表複製資料到新表，那麼可以通過使用INSERT INTO... SELECT語句來做到這一點。

mysql> INSERT INTO clone_tbl (tutorial_id,
    ->                        tutorial_title,
    ->                        tutorial_author,
    ->                        submission_date)
    -> SELECT tutorial_id,tutorial_title,
    ->        tutorial_author,submission_date,
    -> FROM tutorials_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

最後，這是想要確切複製的一張表。

25、MySQL資料庫資訊

有三個資訊，經常要從MySQL獲取。

• 有關查詢結果的資訊: 這包括任何SELECT，UPDATE或DELETE語句所影響的記錄數量。

• 有關表和資料庫的資訊: 這包括關於表和資料庫的結構的資訊。

• 關於MySQL伺服器的資訊: 這包括資料庫伺服器的當前狀態，版本號等。

在mysql的提示符下，很容易得到這些資訊，但如果使用Perl或PHP的API，需要顯式呼叫各種API來獲取這些資訊。 下面的部分將說明如何獲取這些資訊。

獲取通過查詢影響的行數量

PERL 範例

在DBI指令碼，受影響的行數是通過do( )或execute( )返回，這取決於如何執行查詢：

# Method 1
# execute $query using do( )
my $count = $dbh->do ($query);
# report 0 rows if an error occurred
printf "%d rows were affected\n", (defined ($count) ? $count : 0);

# Method 2
# execute query using prepare( ) plus execute( )
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d rows were affected\n", (defined ($count) ? $count : 0);

PHP 範例

在PHP中，呼叫mysql_affected_rows()函式，以找出查詢多少行改變：

$result_id = mysql_query ($query, $conn_id);
# report 0 rows if the query failed
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affected\n");

列出表和資料庫

這很容易列出資料庫伺服器中的所有可用的資料庫和表。但如果結果可能為null，那麼可能沒有足夠的許可權。

除了下面提到的方法，還可以用SHOW TABLES或SHOW DATABASES來查詢獲得表或資料庫列表，無論是在 PHP 或 Perl 中。

PERL 範例

# Get all the tables available in current database.
my @tables = $dbh->tables ( );
foreach $table (@tables ){
   print "Table Name $table\n";
}

PHP 範例

<?php
$con = mysql_connect("localhost", "user", "password");
if (!$con)
{
  die('Could not connect: ' . mysql_error());
}

$db_list = mysql_list_dbs($con);

while ($db = mysql_fetch_object($db_list))
{
  echo $db->Database . "<br />";
}
mysql_close($con);
?>

獲取伺服器後設資料

有哪些可以在mysql提示符下執行，或使用任何如PHP指令碼來獲取各種有關資料庫伺服器的重要資訊。

26、MySQL序列的使用

序列是一組整數如1，2，3，...為了在需要時生成的。資料庫中序列是常用的，因為很多應用都需要在表的每行中，包含一個唯一的值，並且序列提供了一種簡單的方法來生成它們。本章將介紹如何在MySQL中使用序列。

使用AUTO_INCREMENT列

在MySQL中最簡單使用序列的方式是定義一個列AUTO_INCREMENT，然後其餘事情由MySQL來打理。

範例

嘗試下面的例子。在建立表之後，它會插入幾行此表中，但在這裡不給出記錄ID，因為它由MySQL自動增加。

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES
    -> (NULL,'housefly','2003-09-19','kitchen'),
    -> (NULL,'millipede','2004-09-11','driveway'),
    -> (NULL,'grasshopper','2015-02-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2003-09-19 | kitchen    |
|  2 | millipede   | 2004-09-11 | driveway   |
|  3 | grasshopper | 2015-02-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

獲取AUTO_INCREMENT值

LAST_INSERT_ID( )是一個SQL函式，這樣可以了解如何發出SQL語句，從任何用戶端中使用它。 以其他方式，Perl 和 PHP 指令碼提供了獨有的函式來獲取最後一條記錄的自動遞增值。

27、MySQL重複處理

表或結果集有時含有重複記錄。有時，它是允許的，但有時它被要求停止使用重複記錄。有時，需要識別重複記錄並從表中刪除它們。本章將介紹如何防止在一個表中，以及如何刪除已有的重複記錄。

防止在一個表發生重複記錄

可以使用適當表欄位的PRIMARY KEY 或 UNIQUE 來防止重複記錄。讓我們來看看下面的例子：下表中沒有這樣的索引或主鍵，所以這裡允許 first_name 和last_name 記錄重複。

CREATE TABLE person_tbl
(
    first_name CHAR(20),
    last_name CHAR(20),
    sex CHAR(10)
);

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

如果插入一條與現有記錄重複到表，在列或定義索引列，表中一個唯一索引的存在通常會導致錯誤的發生。

應該使用 INSERT IGNORE 而不是INSERT。如果記錄與現有現有不重複時，MySQL將其正常插入。如果記錄是一個重複的，則 IGNORE 關鍵字告訴MySQL丟棄它而不會產生錯誤。

下面的例子不會有錯誤，也不會插入重複的記錄。

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

使用REPLACE而不是INSERT。如果記錄是新的，它插入就像使用 INSERT。如果它是重複的，新的記錄將取代舊的記錄：

mysql> REPLACE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Ajay', 'Kumar');
Query OK, 1 row affected (0.00 sec)
mysql> REPLACE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Ajay', 'Kumar');
Query OK, 2 rows affected (0.00 sec)

INSERT IGNORE和REPLACE應根據實現的重複處理行為來選擇。INSERT忽略保持第一套重複記錄，並丟棄剩下的。REPLACE保持最後一組重複的和擦除任何較早的記錄。

另一種方法是強制唯一性是增加唯一(UNIQUE)索引，而不是一個主鍵(PRIMARY KEY)。

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

統計和標識重複

以下是查詢以統計first_name和last_name 在表中的重複記錄數。

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

這個查詢將返回person_tbl表中的所有重複記錄的列表.在一般情況下，識別的集合值重複，執行以下步驟：

• 確定哪些列包含可重複值

• 列出這些列中的列選擇列表，使用COUNT(*)

• 列出的列也可以使用 GROUP BY 子句

• 新增一個HAVING子句，通過分組計算出唯一值數大於1的記錄重複

從查詢結果消除重記錄

可以使用SELECT語句以及DISTINCT一起在一個表中找出可用唯一記錄。

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl
    -> ORDER BY last_name;

替代DISTINCT方法是新增GROUP BY子句列名稱到選擇的列。這有刪除重複並選擇在指定的列值的唯一組合的效果：

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);

使用表的更換刪除重複

如果一個表中重複的記錄，並要刪除該表中的所有重複的記錄，那麼可以參考下面的程式：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
    ->                  FROM person_tbl;
    ->                  GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

從表中刪除重複記錄的一個簡單的方法就新增索引(INDEX) 或 主鍵(PRIMAY KEY)到該錶。即使該表已經提供，可以使用此技術來刪除重複的記錄。

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);

28、MySQL和SQL隱碼攻擊

如果通過網頁需要使用者輸入一些資料資訊，並將其插入到MySQL資料庫，這是一個引入SQL隱碼攻擊安全問題的機會。這一節將學習如何防止這種情況的發生，並幫助保護指令碼和MySQL語句。

通常注入是在當要求使用者輸入時，類似他們的姓名，只是一個名字，他們給出，會在不知不覺中包含MySQL的語句會在資料庫執行。

永遠不要信任使用者提供的資料，這個過程只有在資料驗證後，作為一項規則，這是通過模式匹配進行。在下面的例子中，使用者名被限制在字母+數位+字元加下劃線，並在8-20個字元之間的長度 - 可以根據需要修改這些規則。

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
{
   $result = mysql_query("SELECT * FROM users 
                          WHERE username=$matches[0]");
}
 else 
{
   echo "username not accepted";
}

為了說明問題，考慮這個片段：

// supposed input
$name = "Qadir'; DELETE FROM users;";
mysql_query("SELECT * FROM users WHERE name='{$name}'");

該函式呼叫從表中檢索使用者記錄，其中名稱列匹配由使用者指定的名稱。 在正常情況下，$name將只包含字母數位字元，或可能是空格，如字串ilia。 但在這裡，通過附加一個全新的查詢到$name，在呼叫資料庫變成災難：注入DELETE查詢刪除所有的使用者記錄。

幸運的是，如果使用MySQL，mysql_query()函式不允許查詢堆疊或一個函式呼叫執行多個查詢。如果嘗試堆疊查詢，呼叫失敗。

然而，其他PHP資料庫擴充套件，如SQLite和PostgreSQL，它們會樂意地進行堆查詢，執行一個字串提供的查詢，並建立一個嚴重的安全問題。

防止SQL隱碼攻擊

可以在指令碼語言，如 Perl和PHP巧妙地處理所有跳脫字元。MySQL擴充套件為PHP提供mysql_real_escape_string()函式來跳脫輸入的特殊字元。

if (get_magic_quotes_gpc()) 
{
  $name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM users WHERE name='{$name}'");

LIKE的困境

為了解決LIKE困境，自定義的跳脫機制必須把使用者提供%和_字元到常數。使用addcslashes()函式，它可以讓指定的字元範圍跳脫。

$sub = addcslashes(mysql_real_escape_string("%something_"), "%_");
// $sub == \%something\_
mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

29、MySQL資料庫匯出(備份方法)

匯出表資料到一個文字檔案的最簡單方法是使用SELECT... INTO OUTFILE語句直接將匯出查詢結果匯出到伺服器主機上的檔案。

使用匯出資料SELECT... INTO OUTFILE語句

語句的語法結合了常規的SELECT INTO與OUTFILE filename 末尾。預設的輸出格式與LOAD DATA是一樣的，所以下面的語句匯出 tutorials_tbl 表到C:\tutorials.txt 並使用製表符分隔，換行結尾的檔案：

mysql> SELECT * FROM tutorials_tbl 
    -> INTO OUTFILE 'C:\tutorials.txt';

可以利用選項來說明如何引號和分隔列，更改記錄輸出格式。 使用CRLF為結束行匯出tutorial_tbl 為CSV格式表格，使用以下語句：

mysql> SELECT * FROM passwd INTO OUTFILE 'C:\tutorials.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

SELECT... INTO OUTFILE具有以下屬性：

• 輸出的檔案是直接由MySQL伺服器建立的，因此，檔案名應指明想要的檔案名，它會被寫到伺服器主機上。還有就是語句類似於沒有LOCAL版本的LOAD DATA的本地版本。

• 必須有MySQL的FILE許可權來執行SELECT ... INTO語句。

• 輸出檔案必須還不存在。 這防止MySQL弄錯檔案很重要。

• 應該有伺服器主機或某種方式來檢索該主機上登入帳戶的檔案。否則，SELECT ... INTO OUTFILE可能沒有任何值給出。

• 在UNIX下，檔案建立所有人都是可讀的，由MySQL伺服器所擁有。這意味著，雖然能夠讀取該檔案，可能無法將其刪除。

匯出表作為原始資料

mysqldump程式用於複製或備份表和資料庫。它可以寫入表輸出作為一個原始資料檔案，或為一組重新建立表中的INSERT語句的記錄。

轉儲一個表作為一個資料檔案，必須指定一個--tab 選項指定目錄，讓MySQL伺服器寫入檔案。

例如，從資料庫test中的tutorials_tbl表轉儲到一個檔案在C:\tmp目錄，可使用這樣的命令：

$ mysqldump -u root -p --no-create-info \
            --tab=c:\tmp TEST tutorials_tbl
password ******

以SQL格式匯出表內容或定義

以SQL格式的表匯出到一個檔案，使用這樣的命令：

$ mysqldump -u root -p test tutorials_tbl > dump.txt
password ******

這將建立一個具有以下內容折檔案，如下：

-- MySQL dump 8.53
--
-- Host: localhost    Database: test
---------------------------------------------------------
-- Server version       5.5.58

--
-- Table structure for table `tutorials_tbl`
--

CREATE TABLE tutorials_tbl (
  tutorial_id int(11) NOT NULL auto_increment,
  tutorial_title varchar(100) NOT NULL default '',
  tutorial_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (tutorial_id),
  UNIQUE KEY AUTHOR_INDEX (tutorial_author)
) TYPE=MyISAM;

--
-- Dumping data for table `tutorials_tbl`
--

INSERT INTO tutorials_tbl 
       VALUES (1,'Learn PHP','John Poul','2012-01-04');
INSERT INTO tutorials_tbl 
       VALUES (2,'Learn MySQL','Abdul S','2015-05-14');
INSERT INTO tutorials_tbl 
       VALUES (3,'JAVA Tutorial','Sanjay','2014-05-10');

要轉儲多個表，所有資料庫名稱引數後跟它們的名字。要轉儲整個資料庫，不需要在資料庫之後命名(附加)任何表：

$ mysqldump -u root -p test > database_dump.txt
password ******

要備份所有可用的資料庫在主機上，使用以下命令：

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

--all-databases選項可在MySQL 3.23.12之後的版本使用。

該方法可用於實現資料庫的備份策略。

複製表或資料庫到另一台主機

如果想從一個MySQL伺服器複製表或資料庫到另一台，使用mysqldump以及資料庫名和表名。

在源主機上執行下面的命令。將轉儲完整的資料庫到檔案dump.txt：

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

可以複製完整的資料庫，而無需使用特定的表名，如上所述。

現在ftp dump.txt檔案在另一台主機上，並使用下面的命令。執行此命令之前，請確保已建立資料庫名稱在目標伺服器上。

$ mysql -u root -p database_name < dump.txt
password *****

另一種方式來實現這一點，無需使用一個中間檔案是來傳送，mysqldump輸出直接通過網路到遠端MySQL伺服器。如果可以從源資料庫所在的主機那裡連線兩個伺服器，使用此命令(請確保兩個伺服器可以存取)：

$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

命令mysqldump的一半連線到本地伺服器，並轉儲輸出寫入管道。另一半MySQL連線到other-host.com遠端MySQL伺服器。它讀取管道輸入並行送每條語句到other-host.com伺服器。

30、MySQL資料庫匯入(恢復資料方法)

MySQL中有兩種簡單的方法可從以前備份的檔案資料載入(恢復)到MySQL資料庫。

使用LOAD DATA匯入資料

MySQL提供了一個大容量資料載入的LOAD DATA語句。下面是一個讀取檔案C:\dump.txt 並將其載入到當前資料庫表mytbl的例子宣告：

mysql> LOAD DATA LOCAL INFILE 'C:\dump.txt' INTO TABLE mytbl;

• 如果LOCAL關鍵詞不存在，MySQL查詢使用絕對路徑在伺服器主機上的資料檔案，完全指定檔案的位置，從檔案系統的根開始。 MySQL讀取從給定的位置的檔案。

• 預設情況下，LOAD DATA假設資料檔案包含一個行由製表符分隔範圍內被換行(新行)分割行和資料值。

• 要明確指定一個檔案格式，使用FIELDS子句來描述一行內欄位的特徵，LINES子句指定的行結束序列。下面的LOAD DATA語句指定的資料檔案包含由冒號和行，是由回車和新行字元結束分隔其值：

mysql> LOAD DATA LOCAL INFILE 'C:\dump.txt' INTO TABLE mytbl
  -> FIELDS TERMINATED BY ':'
  -> LINES TERMINATED BY '\r\n';

• LOAD DATA假定資料檔案中的列具有相同的順序在表中的列。如果不是這樣，可以指定一個列表來指示哪些表列資料檔案列應該被裝入。 假設表中的列A，B和C，但在資料檔案連續列對應於列B，C，和A。可以像這樣載入檔案：

mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
    -> INTO TABLE mytbl (b, c, a);

使用mysqlimport匯入資料

MySQL還包括一個實用程式名：mysqlimport，它充當圍繞LOAD DATA包裝器 這樣就可以直接從命令列輸入載入檔案。

從 dump.txt 載入資料到表mytbl，使用下面的命令在命令列提示符。

$ mysqlimport -u root -p --local database_name dump.txt
password *****

如果使用mysqlimport，命令列選項提供的格式說明。對應於上述兩個LOAD DATA 語句的 mysqlimport 命令如下：

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  database_name dump.txt
password *****

在 mysqlimport 中指定的選項順序並不重要，但它們都應該在資料庫名的前面。

mysqlimport語句使用--columns選項來指定列的順序：

$ mysqlimport -u root -p --local --columns=b,c,a \
    database_name dump.txt
password *****

處理引號和特殊字元

除了TERMINATED，FIELDS子句可以指定其他格式選項。預設情況下，LOAD DATA假設值是加引號的，並解釋反斜線(\)作為特殊字元跳脫字元。要明確註明參照字元值， 使用ENCLOSED BY; MySQL將剝離字元的資料值末端在輸入處理期間。要更改預設的跳脫字元，可使用ESCAPED BY。

當指定ENCLOSED BY，表明引號字元應該從資料值被剝離，有可能通過加一次或通過跳脫字元，確實包含引號字元在資料值前。例如，如果引號和跳脫字元是" 和 \，輸入 "a""b\"c" 將被解釋為：a"b"c

對於 mysqlimport，相應的命令列選項引號和跳脫值是通過 --fields-enclosed-by 和 --fields-escaped-by來指定。

31、MySQL實用函式

這裡是所有重要的 MySQL 函式的列表。每個函式都用合適的例子來說明如下。

• MySQL Group By 子句 - MySQL的GROUP BY語句以及SQL聚合函式，用於類似SUM提供某些資料庫表的列來分組結果資料集

• MySQL IN 子句 - 這是一個子句，它可以用來連同任何MySQL查詢語句以指定條件

• MySQL BETWEEN 子句 - 這是一個子句，它可以用來與任何MySQL查詢來指定條件

• MySQL UNION關鍵字 - 使用UNION操作多個結果集組合成一個結果集

• MySQL COUNT函式 - MySQL的COUNT聚合函式用於計算一個資料庫表中的行數

• MySQL MAX 函式 - MySQL的MAX聚合函式允許我們選擇某些列的最高(最大)值

• MySQL MIN函式 - MySQL的MIN聚合函式允許我們選擇某些列的最低(最小)值

• MySQL AVG函式 - MySQL的AVG聚合函式是用來對某些表的列求它的平均值

• MySQL SUM函式 - MySQL的SUM聚合函式允許選擇某列的總和

• MySQL SQRT函式 - 這是用來生成給定數的平方根

• MySQL RAND函式 - 使用MySQL命令產生一個亂數

• MySQL CONCAT函式 - 這是用來連線MySQL命令中的任何字串

• MySQL DATE 和 Time 時間日期函式 - MySQL日期和時間相關的函式完整列表

• MySQL數位函式 - 在MySQL中運算元字的MySQL函式完整列表

• MySQL字串函式 - 在MySQL中的字串操作的MySQL函式的完整列表