共計 20683 個字符，預計需要花費 52 分鐘才能閱讀完成。

這篇文章給大家分享的是有關 MySQL 基礎的示例分析的內容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，一起跟隨丸趣 TV 小編過來看看吧。

MySQL 入門

mySQL（關系型數據庫管理系統）

MySQL 是一個關系型數據庫管理系統，由瑞典 MySQL AB 公司開發，目前屬于 Oracle 旗下產品。MySQL 是最流行的關系型數據庫管理系統之一，在 WEB 應用方面，MySQL 是最好的 RDBMS (Relational Database Management System，關系數據庫管理系統) 應用軟件。

MySQL 是一種關系數據庫管理系統，關系數據庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度并提高了靈活性。

MySQL 所使用的 SQL 語言是用于訪問數據庫的最常用標準化語言。MySQL 軟件采用了雙授權政策，分為社區版和商業版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網站的開發都選擇 MySQL 作為網站數據庫。
由于其社區版的性能卓越，搭配 PHP 和 Apache 可組成良好的開發環境。

1． 數據庫（Database）是按照數據結構來組織、存儲和管理數據的倉庫，每個數據庫都有一個或多個不同的 API 用于創建，訪問，管理，搜索和復制所保存的數據。

2． 使用關系型數據庫管理系統（RDBMS）來存儲和管理的大數據量。關系型數據庫是建立在關系模型基礎上的數據庫，借助于集合代數等數學概念和方法來處理數據庫中的數據。

3． RDBMS 特點：

1. 數據以表格的形式出現

2. 每行為各種記錄名稱

3. 每列為記錄名稱所對應的數據域

4. 許多的行和列組成一張表單

5. 若干的表單組成 database

4． RDBMS 術語

冗余：存儲兩倍數據，冗余可以使系統速度更快。

主鍵：主鍵是唯一的。一個數據表中只能包含一個主鍵。你可以使用主鍵來查詢數據。

外鍵：外鍵用于關聯兩個表。

復合鍵：復合鍵（組合鍵）將多個列作為一個索引鍵，一般用于復合索引。

索引：使用索引可快速訪問數據庫表中的特定信息。索引是對數據庫表中一列或多列的值進行排序的一種結構。類似于書籍的目錄。

參照完整性: 參照的完整性要求關系中不允許引用不存在的實體。與實體完整性是關系模型必須滿足的完整性約束條件，目的是保證數據的一致性

5． MySQL 是一種關聯數據庫管理系統，關聯數據庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度并提高了靈活性。

MySQL 管理 6． 啟動及關閉 MySQL 服務器:

（1）檢查 MySQL 服務器是否啟動：

ps -ef | grepmysqld

（2）啟動 MySQL 服務器:

root@host# cd/usr/bin

./safe_mysqld

（3）關閉目前運行的 MySQL 服務器：

root@host# cd/usr/bin

./mysqladmin-u root -p shutdown

Enterpassword: ******

7． MySQL 用戶設置

在 MySQL 數據庫中的 user 表添加新用戶：

root@host# mysql -u root –p // 選擇數據庫
Enter password:*******
mysql  use mysql;
Database changed
mysql  INSERT INTO user
(host, user, password,
select_priv, insert_priv,update_priv) // 設置權限 Y
VALUES ( localhost ,  guest ,
PASSWORD(guest123),  Y ,  Y , Y 
Query OK, 1 row affected (0.20 sec)
mysql  FLUSH PRIVILEGES;

注意需要執行 FLUSH PRIVILEGES 語句。這個命令執行后會重新載入授權表。

另外一種添加用戶的方法為通過 SQL 的 GRANT 命令

mysql  GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-  ON TUTORIALS.*
-  TO  zara @ localhost 
-  IDENTIFIED BY zara123

8． 管理 MySQL 的命令

USE 數據庫名 : 選擇要操作的 MySQL 數據庫：

mysql use W3CSCHOOL;

Database changed

SHOW DATABASES: 列出 MySQL 數據庫管理系統的數據庫列表：

mysql SHOWDATABASES;

SHOW TABLES: 顯示指定數據庫所有表，用該命令前需用 use 命令選擇操作的數據庫。

mysql useW3CSCHOOL;

Database changed

mysql SHOW TABLES;

SHOW COLUMNS FROM 數據表: 顯示數據表的屬性，屬性類型，主鍵信息，是否 NULL，默認值等其他信息。

mysql SHOW COLUMNSFROM W3Cschool_tbl;

+—————–+————–+——+—–+———+——-+

| Field | Type | Null | Key | Default | Extra |

+—————–+————–+——+—–+———+——-+

SHOW INDEX FROM 數據表: 顯示數據表的詳細索引信息，包括 PRIMARY KEY（主鍵）。

SHOW TABLE STATUS LIKE 數據表 \G: 該命令將輸出 MySQL 數據庫管理系統的性能及統計信息。

mysql SHOW TABLESTATUS FROM W3CSCHOOL; # 顯示數據庫 W3CSCHOOL 中所有表的信息

mysql SHOW TABLESTATUS from W3CSCHOOL LIKE W3Cschool% #表名以 W3Cschool 開頭的表的信息

mysql SHOW TABLESTATUS from W3CSCHOOL LIKE W3Cschool% #加上 \G，查詢結果按列打印

9． PHP MySQL 函數格式：mysql_function(value,value,…);

MySQL 連接 10．使用 MySQL 二進制方式連接 [root@host]# mysql -uroot -p
Enter password:******
登錄成功后會出現 mysql 命令提示窗口，你可以在上面執行任何 SQL 語句。
退出 mysql 命令提示窗口可以使用 exit 命令：mysql exit

11．使用 PHP 腳本連接 MySQL

PHP 提供了 mysql_connect() 函數來連接數據庫。
connectionmysql_connect(server,user,passwd,new_link,client_flag);5 各參數均可選
使用 PHP 的 mysql_close() 函數來斷開與 MySQL 數據庫的鏈接。
bool mysql_close (resource $link_identifier);
通常不要用 mysql_close()，因為已打開的非持久連接會在腳本執行完畢后自動關閉。
mysql_close() 不會關閉由 mysql_pconnect() 建立的持久連接

$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
die(Could not connect:   .mysql_error());
echo  Connected successfully 
mysql_close($conn);

MySQL 創建 / 刪除數據庫 12．使用 mysqladmin 創建數據庫

用 root 用戶登錄，root 用戶擁有最高權限，可以使用 mysql mysqladmin 命令來創建數據庫。
[root@host]#mysqladmin -u root -pcreate/drop W3CSCHOOL
Enter password:***** 

13．使用 PHP 腳本創建數據庫

PHP 使用 mysql_query 函數來創建或者刪除 MySQL 數據庫。

bool mysql_query( sql, connection);
 
$conn = mysql_connect($dbhost,$dbuser, $dbpass);
if(! $conn )
 die(連接錯誤:   . mysql_error());
echo  連接成功 br / 
$sql=  CREATE/DROP DATABASE W3CSCHOOL 
$retval= mysql_query( $sql, $conn );
if(! $retval )
 die(創建數據庫失敗:   . mysql_error());
echo  數據庫  W3CSCHOOL 創建成功 \n 
mysql_close($conn);

MySQL 選擇數據庫 14．使用 PHP 腳本選擇 MySQL 數據庫

PHP 提供了函數 mysql_select_db 來選取一個數據庫。

bool mysql_select_db( db_name,connection );
$conn = mysql_connect($dbhost,$dbuser, $dbpass);
if(! $conn )
 die(連接失敗:   . mysql_error());
echo  連接成功 
mysql_select_db(W3CSCHOOL  );
mysql_close($conn);

MySQL 創建 / 刪除數據表 15．MySQL 創建數據表

創建 MySQL 數據表需要以下信息：

表名

表字段名

定義每個表字段

創建語法：CREATE TABLE table_name (column_name column_type);

刪除語法：DROP TABLE table_name ;

以下例子中我們將在 W3CSCHOOL 數據庫中創建數據表 w3cschool_tbl：

tutorials_tbl(
 tutorial_id INT NOT NULL AUTO_INCREMENT,
 tutorial_title VARCHAR(100) NOT NULL,
 tutorial_author VARCHAR(40) NOT NULL,
 submission_date DATE,
 PRIMARY KEY ( w3cschool_id )
);

16．通過命令提示符創建表

使用 SQL 語句 CREATE TABLE 來創建數據表。

mysql  CREATE TABLE w3cschool_tbl(
 -  w3cschool_id INT NOTNULL AUTO_INCREMENT,
 -  w3cschool_titleVARCHAR(100) NOT NULL,
 -  w3cschool_authorVARCHAR(40) NOT NULL,
 -  submission_date DATE,
 -  PRIMARY KEY (w3cschool_id )
 -  );

MySQL 命令終止符為分號 (;)。 

17．使用 PHP 腳本創建 / 刪除數據表或插入數據

語法：bool mysql_query(sql, connection);

$sql =  CREATE TABLEtutorials_tbl(  創建
  tutorial_id INT NOTNULL AUTO_INCREMENT,  .
  tutorial_titleVARCHAR(100) NOT NULL,  .
  tutorial_authorVARCHAR(40) NOT NULL,  .
  submission_dateDATE,  .
  PRIMARY KEY (tutorial_id ));  
$sql =  DROP TABLEw3cschool_tbl 刪除
 
mysql_select_db(  TUTORIALS  );
$retval = mysql_query( $sql, $conn); // 判斷是否成功而設置的參數；if(! $retval )
 die(數據表創建失敗:   . mysql_error());
echo  數據表創建成功 \n 
mysql_close($conn);

MySQL 插入數據

18．向 MySQL 數據表插入數據通用的 INSERT INTO SQL 語法：

INSERT INTO table_name (field1, field2,…fieldN)
  VALUES
  (value1,value2,…valueN);

如果數據是字符型，必須使用單引號或者雙引號，如：value。

w3cschool_tbl 表插入一條數據:

mysql INSERT INTOw3cschool_tbl
  – (w3cschool_title, w3cschool_author,submission_date)
  – VALUES
  – (Learn PHP , JohnPoul , NOW());

(-)不是 SQL 語句的一部分，它僅表示一個新行，如 SQL 語句太長，可通過回車鍵創建一個新行編寫 SQL 語句，SQL 語句的命令結束符為分號（;）。

19．使用 PHP 腳本插入數據

  $sql = INSERT INTO w3cschool_tbl .
  (w3cschool_title,w3cschool_author,submission_date) .
  VALUES .
  ($w3cschool_title , $w3cschool_author , $submission_date)

MySQL 查詢數據

20．為在 MySQL 數據庫中查詢數據通用的 SELECT 語法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M][LIMIT N]

可以通過 SELECT 命令讀取一條或者多條記錄。

可以通過 OFFSET 指定 SELECT 語句開始查詢的數據偏移量默認情況下偏移量為 0。

可以使用 LIMIT 屬性來設定返回的記錄數。

你可以使用星號（*）來代替其他字段，SELECT 語句會返回表的所有字段數據

mysql SELECT * from w3cschool_tbl

 
21．使用 PHP 腳本來獲取數據

使用 PHP 函數的 mysql_query()及 SQL SELECT 命令來獲取數據。
該函數用于執行 SQL 命令，然后通過 PHP 函數 mysql_fetch_array() 來使用或輸出所有查詢的數據。

嘗試以下實例來顯示數據表 w3cschool_tbl 的所有記錄

$sql = SELECT w3cschool_id,w3cschool_title,
  w3cschool_author,submission_date
  FROM w3cschool_tbl
mysql_select_db(W3CSCHOOL
$retval = mysql_query($sql, $conn);
if(! $retval)
{
  die(Could not get data: .mysql_error());
}
while($row =mysql_fetch_array($retval, MYSQL_ASSOC))
while($row =mysql_fetch_assoc($retval))

用 MYSQL_NUM 參數顯示數據表

while($row =mysql_fetch_array($retval, MYSQL_NUM))

用 MYSQL_NUM 參數顯示數據表

{
  echo Tutorial ID:{$row[ w3cschool_id]}  br .
  Title:{$row[ w3cschool_title]} br .
  Author:{$row[ w3cschool_author]} br .
  Submission Date : {$row[ submission_date]} br .
  ——————————– br
}
mysql_free_result($retval);  釋放游標內存
echo Fetched data successfully\n
mysql_close($conn);

MYSQL_ASSOC，設置該參數查詢結果返回關聯數組，你可以使用字段名稱來作為數組的索引。

MySQL 選擇數據

22．從 MySQL 表中使用 SQL SELECT 語句來讀取數據。

如需有條件地從表中選取數據，可將 WHERE 子句添加到 SELECT 語句中
以下是 SQL SELECT 語句使用 WHERE 子句從數據表中讀取數據的通用語法：

SELECT field1,field2,…fieldN FROM table_name1, table_name2…
[WHERE condition1 [AND[OR]] condition2…..

WHERE 子句也可以運用于 SQL 的 DELETE 或者 UPDATE 命令。
使用 LIKE 來比較字符串，否則 MySQL 的 WHERE 子句的字符串比較是不區分大小寫的。你可以使用 BINARY 關鍵字來設定 WHERE 子句的字符串比較是區分大小寫的。

 23．使用 PHP 腳本讀取數據

使用 PHP 函數的 mysql_query()及相同的 SQL SELECT 帶上 WHERE 子句的命令來獲取數據。該函數用于執行 SQL 命令，然后通過 mysql_fetch_array() 來輸出所有查詢的數據。

$sql = SELECT w3cschool_id,w3cschool_title,
  w3cschool_author, submission_date
  FROM w3cschool_tbl
  WHEREw3cschool_author= Sanjay

 MySQL UPDATE

24．修改或更新 MySQL 中的數據，我們可以使用 SQL UPDATE 命令來操作。

通用 SQL 語法：

UPDATE table_name SET field1=new-value1, field2=new-value2

[WHERE Clause]

更新數據表中 w3cschool_id 為 3 的 w3cschool_title 字段值：

mysql UPDATE w3cschool_tbl
  – SET w3cschool_title= Learning JAVA
–  WHERE w3cschool_id=3;

使用 PHP 腳本更新數據

$sql = UPDATE w3cschool_tbl
  SETw3cschool_title= Learning JAVA
  WHERE w3cschool_id=3

MySQL DELETE

25．DELETE FROM table_name[WHERE Clause

如果沒有指定 WHERE 子句，MySQL 表中的所有記錄將被刪除。

可以在 WHERE 子句中指定任何條件

刪除 w3cschool_tbl 表中 w3cschool_id 為 3 的記錄

mysql DELETE FROMw3cschool_tbl WHERE w3cschool_id=3;

用 PHP 腳本刪除數據

$sql = DELETE FROMw3cschool_tbl
  WHERE w3cschool_id=3

MySQL LIKE 子句

QL LIKE 子句中使用百分號 (%) 字符來表示任意字符

沒有使用百分號(%),LIKE 子句與等號（=）的效果是一樣的。
26．QL SELECT 語句使用 LIKE 子句從數據表中讀取數據的通用語法：
SELECT field1,field2,…fieldN table_name1, table_name2…
WHERE field1 LIKEcondition1 [AND [OR]] filed2 = somevalue
LIKE 通常與 % 一同使用，類似于一個元字符的搜索

在 PHP 腳本中使用 LIKE 子句

$sql = SELECTw3cschool_id, w3cschool_title,
  w3cschool_author,submission_date
  FROM w3cschool_tbl
  WHERE w3cschool_author LIKE %jay%

MySQL 排序

SELECT field1,field2,…fieldN table_name1, table_name2…

ORDER BY field1,[field2…] [ASC [DESC]]

ASC 或 DESC 關鍵字來設置查詢結果是按升序或降序排列。默認情況下，它是按升排列。

MySQL 分組

SELECT column_name,function(column_name)
FROM table_name
WHERE column_nameoperator value
GROUP BY column_name;

WITH ROLLUP 可以實現在分組統計數據基礎上再進行相同的統計（SUM,AVG,COUNT…）。

coalesce 來設置一個可以取代 NUll 的名稱，coalesce 語法：

select coalesce(a,b,c);

參數說明：如果 a ==null, 則選擇 b；如果 b ==null, 則選擇 c；如果 a!=null, 則選擇 a；如果 a b c 都為 null，則返回為 null（沒意義）。

mysql SELECTcoalesce(name, 總數), SUM(singin) as singin_countFROM  employee_tbl GROUP BY name WITHROLLUP;

+————————–+————–+

| coalesce(name, 總數) | singin_count |

+————————–+————–+

| 小麗   |  2 |

| 小明   |  7 |

| 小王   |  7 |

| 總數   |  16 |

+————————–+————–+

 MySQL 多表查詢

27．在 SELECT, UPDATE 和 DELETE 語句中使用 Mysql 的 JOIN 來聯合多表查詢。

JOIN 按照功能大致分為如下三類：

INNER JOIN（內連接, 或等值連接）：獲取兩個表中字段匹配關系的記錄。
LEFT JOIN（左連接）：獲取左表所有記錄，即使右表沒有對應匹配的記錄。
RIGHT JOIN（右連接）：與 LEFT JOIN 相反，用于獲取右表所有記錄，即使左表沒有對應匹配的記錄。

| w3cschool_author |w3cschool_count |

+—————–+—————-+

| mahran  |  20 |

| mahnaz  |  NULL |

| Jen  |  NULL |

| Gill  |  20 |

| John Poul  |  1 |

| Sanjay  |  1 |

+—————–+—————-+

mysql SELECT * fromw3cschool_tbl;

+————-+—————-+—————–+—————–+

| w3cschool_id | w3cschool_title | w3cschool_author |submission_date |

+————-+—————-+—————–+—————–+

|  1 | Learn PHP  | John Poul  |2007-05-24  |
|  2 | LearnMySQL  | Abdul S  | 2007-05-24  |
|  3 | JAVATutorial  | Sanjay  | 2007-05-06  |

連接以上兩張表來讀取 w3cschool_tbl 表中所有 w3cschool_author 字段在 tcount_tbl 表對應的 w3cschool_count 字段值：

mysql SELECTa.w3cschool_id, a.w3cschool_author, b.w3cschool_count FROM w3cschool_tbl aINNER JOIN tcount_tbl b ON a.w3cschool_author = b.w3cschool_author;

+———–+—————+————–+

| w3cschool_id | w3cschool_author | w3cschool_count |

+———–+—————+————–+

|  1 | John Poul  |  1 |

|  3 | Sanjay  |  1 |

w3cschool_tbl 為左表，tcount_tbl 為右表，

mysql SELECTa.w3cschool_id, a.w3cschool_author, b.w3cschool_count FROM w3cschool_tbl a LEFTJOIN tcount_tbl b ON a.w3cschool_author = b.w3cschool_author;

+————-+—————–+—————-+

| w3cschool_id | w3cschool_author | w3cschool_count |

+————-+—————–+—————-+

|  1 | John Poul  |  1 |

|  2 | Abdul S  |  NULL |

|  3 | Sanjay  |  1 |

左邊的數據表 w3cschool_tbl 的所有選取的字段數據，即便在右側表 tcount_tbl 中沒有對應的 w3cschool_author 字段值 Abdul S。

MySQL NULL

IS NULL: 當列的值是 NULL, 此運算符返回 true。
IS NOT NULL: 當列的值不為 NULL, 運算符返回 true。

NULL 值與任何其它值的比較（即使是 NULL）永遠返回 false，

使用 PHP 腳本處理 NULL 值:

PHP 腳本中你可以在 if…else 語句來處理變量是否為空，并生成相應的條件語句。

MySQL 正則表達式

28．MySQL 中使用 REGEXP 操作符來進行正則表達式匹配。

^  匹配輸入字符串的開始位置。如果設置了 RegExp 對象的 Multiline 屬性，^ 也匹配 \n 或 \r 之后的位置。
$  匹配輸入字符串的結束位置。如果設置了 RegExp 對象的 Multiline 屬性，$ 也匹配 \n 或 \r 之前的位置。
.  匹配除 \n 之外的任何單個字符。要匹配包括 \n 在內的任何字符，請使用象 [.\n] 的模式。

實例 (表名：person_tbl) 來加深我們的理解：

查找 name 字段中以 st 為開頭的所有數據：

mysql SELECT name FROM person_tbl WHERE name REGEXP ^st

查找 name 字段中以 ok 為結尾的所有數據：

mysql SELECT name FROM person_tbl WHERE name REGEXP ok$

查找 name 字段中包含 mar 字符串的所有數據：

mysql SELECT name FROM person_tbl WHERE name REGEXP mar

查找 name 字段中以元音字符開頭或以 ok 字符串結尾的所有數據：

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

 MySQL 事務

29．MySQL 事務主要用于處理操作量大，復雜度高的數據。

在 MySQL 中只有使用了 Innodb 數據庫引擎的數據庫或表才支持事務

事務處理可以用來維護數據庫的完整性，保證成批的 SQL 語句要么全部執行，要么全部不執行

事務用來管理 insert,update,delete 語句

事務必須滿足 4 個條件（ACID）：Atomicity（原子性）、Consistency（穩定性）、Isolation（隔離性）、Durability（可靠性）

1、事務的原子性：一組事務，要么成功；要么撤回。

2、穩定性：有非法數據（外鍵約束之類），事務撤回。

3、隔離性：事務獨立運行。一個事務處理后的結果，影響了其他事務，那么其他事務會撤回。事務的 100% 隔離，需要犧牲速度。

4、可靠性：軟、硬件崩潰后，InnoDB 數據表驅動會利用日志文件重構修改。可靠性和高速度不可兼得，innodb_flush_log_at_trx_commit 選項 決定什么時候吧事務保存到日志里。

  在 MySQL 控制臺使用事務來操作:

1，開始一個事務

start transaction

2, 做保存點

savepoint 保存點名稱

3, 操作

4，可以回滾，可以提交，沒有問題，就提交，有問題就回滾。

 PHP 中使用事務實例

mysql_query(SETAUTOCOMMIT=0 // 設置為不自動提交，因為 MYSQL 默認立即執行 mysql_query( BEGIN // 開始事務定義

if(!mysql_query( insertinto trans (id) values(2) ))
mysql_query( ROOLBACK // 判斷當執行失敗時回滾
mysql_query( COMMIT // 執行事務
mysql_close($handler);
MySQL ALTER

30．修改數據表名或者修改數據表字段時，就需要使用到 MySQL ALTER 命令。

使用了 ALTER 命令及 DROP 子句來刪除以上創建表的 i 字段：

mysql ALTER TABLEtestalter_tbl  DROP i;

數據表中只剩余一個字段則無法使用 DROP 來刪除字段。

ADD 子句來想數據表中添加列，在表 testalter_tbl 中添加 i 字段，并定義數據類型:

mysql ALTER TABLEtestalter_tbl ADD i INT;

以下 ALTERTABLE 語句, 在執行成功后，使用 SHOW COLUMNS 查看表結構的變化：

ALTER TABLEtestalter_tbl DROP i;
ALTER TABLEtestalter_tbl ADD i INT FIRST;
ALTER TABLEtestalter_tbl DROP i;
ALTER TABLEtestalter_tbl ADD i INT AFTER c;

FIRST 和 AFTER 關鍵字只占用于 ADD 子句，所以如果你想重置數據表字段的位置就需要先使用 DROP 刪除字段然后使用 ADD 來添加字段并設置位置。

修改字段類型及名稱:

31．在 ALTER 命令中使用 MODIFY 或 CHANGE 子句。

把字段 c 的類型從 CHAR(1) 改為 CHAR(10)，可以執行以下命令:
mysql ALTER TABLEtestalter_tbl MODIFY c CHAR(10);
使用 CHANGE 子句, 語法有很大的不同。在 CHANGE 關鍵字之后，緊跟著的是你要修改的字段名，然后指定新字段的類型及名稱。嘗試如下實例：
mysql ALTER TABLEtestalter_tbl CHANGE i j BIGINT;
p 如果你現在想把字段 j 從 BIGINT 修改為 INT，SQL 語句如下：
mysql ALTER TABLEtestalter_tbl CHANGE j j INT;

ALTER 修改字段的默認值，mysql ALTER TABLEtestalter_tbl ALTER i SET DEFAULT 1000;
ALTER 及 DROP 刪除字段的默認值, ALTER TABLEtestalter_tbl ALTER i DROP DEFAULT;
ALTER 及 TYPE 修改數據表類型，mysql ALTER TABLEtestalter_tbl TYPE = MYISAM;
ALTER TABLE 使用 RENAME 修改數據表的名稱，mysql ALTER TABLEtestalter_tbl RENAME TO alter_tbl;

MySQL 索引

索引可以大大提高 MySQL 的檢索速度
索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索包含多個列。
創建索引，確保該索引是應用在 SQL 查詢語句的條件 (一般作為 WHERE 子句的條件)。
索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄。
缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行 INSERT、UPDATE 和 DELETE。因為更新表時，MySQL 不僅要保存數據，還要保存一下索引文件。
3 方式創建普通索引

CREATE INDEX indexName ONmytable(username(length));

如果是 CHAR，VARCHAR 類型，length 可以小于字段實際長度；如果是 BLOB 和 TEXT 類型，必須指定 length。

修改表結構

ALTER mytable ADD INDEX[indexName] ON (username(length))

創建表的時候直接指定

CREATE TABLE mytable( 
ID INT NOT NULL, 
username VARCHAR(16) NOTNULL, 
INDEX [indexName](username(length)) 
);

刪除索引的語法

DROP INDEX [indexName] ONmytable;

  唯一索引：前面加 UNIQUE

  使用 ALTER 命令添加和刪除索引

有四種方式來添加數據表的索引：

ALTER TABLE tbl_name ADD PRIMARYKEY (column_list): 該語句添加一個主鍵，這意味著索引值必須是唯一的，且不能為 NULL。
ALTER TABLE tbl_name ADD UNIQUEindex_name (column_list): 這條語句創建索引的值必須是唯一的（除了 NULL 外，NULL 可能會出現多次）。
ALTER TABLE tbl_name ADD INDEXindex_name (column_list): 添加普通索引，索引值可出現多次。
ALTER TABLE tbl_name ADD FULLTEXTindex_name (column_list): 該語句指定了索引為 FULLTEXT，用于全文索引。

  使用 ALTER 命令添加和刪除主鍵

主鍵只能作用于一個列上，添加主鍵索引時，你需要確保該主鍵默認不為空（NOT NULL）。實例如下：

mysql ALTER TABLEtestalter_tbl MODIFY i INT NOT NULL;

mysql ALTER TABLEtestalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令刪除主鍵：

mysql ALTER TABLEtestalter_tbl DROP PRIMARY KEY;

SHOW INDEX 命令列出表中的相關的索引信息。可以通過添加 \G 來格式化輸出信息。

mysql SHOW INDEX FROMtable_name\G

MySQL 臨時表

臨時表只在當前連接可見，當關閉連接時，MySQL 會自動刪除表并釋放所有空間。

mysql CREATE TEMPORARY TABLE SalesSummary

mysql DROP TABLE SalesSummary;

MySQL 復制表

創建新的克隆表 clone_tbl。如果你想拷貝數據表的數據你可以使用 INSERT INTO… SELECT 語句來實現。

mysql INSERT INTOclone_tbl (w3cschool_id,
  –   w3cschool_title,
  –   w3cschool_author,
  –   submission_date)
  – SELECT w3cschool_id,w3cschool_title,
  –   w3cschool_author,submission_date
– FROM w3cschool_tbl;

 MySQL 元數據

想知道 MySQL 以下三種信息：

查詢結果信息：SELECT,UPDATE 或 DELETE 語句影響的記錄數。
數據庫和數據表的信息：包含了數據庫及數據表的結構信息。
MySQL 服務器信息：包含了數據庫服務器的當前狀態，版本號等。

（1）使用 do() 執行   $query

my $count = $dbh- do($query);

（2）使用 prepare()及 execute() 執行   $query

my $sth =$dbh- prepare ($query);

my $count =$sth- execute (); 

在 PHP 中，使用 mysql_affected_rows() 函數獲取查詢語句影響的記錄數。

$result_id =mysql_query ($query, $conn_id);
#  如果查詢失敗返回
$count = ($result_id ?mysql_affected_rows ($conn_id) : 0);
print ($countrows were affected\n

  數據庫和數據表列表

PERL 實例

# 獲取當前數據庫中所有可用的表。

my @tables =$dbh- tables ( );
foreach $table (@tables){ print  Table Name $table\n}

PHP 實例：

$db_list =mysql_list_dbs($con);
while ($db = mysql_fetch_object($db_list))
 echo $db- Database .  br/ 
}

MySQL 序列

MySQL 序列是一組整數：1, 2, 3, …，

用 MySQLAUTO_INCREMENT 來定義列。

mysql CREATE TABLEinsect

  – (

  – id INT UNSIGNED NOT NULLAUTO_INCREMENT,

用 SQL 中的 LAST_INSERT_ID() 函數來獲取最后的插入表中的自增列的值。

 PERL 實例

使用 mysql_insertid 屬性來獲取 AUTO_INCREMENT 的值。實例如下：

$dbh- do(INSERT INTO insect (name,date,origin)

VALUES(moth , 2001-09-14 , windowsill)

my $seq =$dbh- {mysql_insertid};

PHP 實例

PHP 通過 mysql_insert_id ()函數來獲取執行的插入 SQL 語句中 AUTO_INCREMENT 列的值。

mysql_query(INSERT INTO insect (name,date,origin)
VALUES(moth , 2001-09-14 , windowsill) ,$conn_id);
$seq = mysql_insert_id($conn_id);

重置序列

刪除了數據表中的多條記錄，并對剩下數據的 AUTO_INCREMENT 列進行重新排列，那么你可以通過刪除自增的列，然后重新添加來實現。

mysql ALTER TABLEinsect DROP id;

mysql ALTER TABLEinsect

  – ADD id INT UNSIGNED NOT NULLAUTO_INCREMENT FIRST,

  – ADD PRIMARY KEY (id);

一般情況下序列的開始值為 1，但如果你需要指定一個開始值 100：

– id INT UNSIGNEDNOT NULL AUTO_INCREMENT = 100,

或在表創建成功后，通過以下語句來實現：

mysql ALTER TABLE tAUTO_INCREMENT = 100;

 MySQL 處理重復數據

防止表中出現重復數據

在 MySQL 數據表中設置指定的字段為 PRIMARY KEY（主鍵）或者 UNIQUE（唯一）索引保證數據的唯一性。

設置表中字段 first_name，last_name 數據不能重復，你可以設置雙主鍵模式來設置數據的唯一性，如果你設置了雙主鍵，那么那個鍵的默認值不能為 NULL，可設置為 NOT NULL。如下所示：

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

INSERT IGNOREINTO 與 INSERT INTO 的區別就是 INSERT IGNORE 會忽略數據庫中已經存在的數據，如果數據庫沒有數據，就插入新的數據，如果有數據的話就跳過這條數據。這樣可以保留數據庫中已經存在數據，達到在間隙中插入數據的目的。

用了 INSERT IGNORE INTO，執行后不會出錯，也不會向數據表中插入重復數據：

mysql INSERT IGNORE INTO person_tbl(last_name, first_name)

  – VALUES(Jay , Thomas

Query OK, 1 row affected (0.00 sec)

REPLACE INTO into 如果存在 primary 或 unique 相同的記錄，則先刪除掉。再插入新記錄。

  UNIQUE(last_name, first_name)

查詢重復記錄

select user_name,count(*) as count fromuser_table group by user_name having count

select * from people

where peopleId in (select peopleId from peoplegroup by peopleId having count(peopleId) 1)

統計重復數據

統計表中 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;

  過濾重復數據

讀取不重復的數據可以在 SELECT 語句中使用 DISTINCT 關鍵字來過濾重復數據。

mysql SELECT DISTINCT last_name, first_name

  – FROM person_tbl

  – ORDER BY last_name;

也可以使用 GROUP BY 來讀取數據表中不重復的數據：

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

  刪除重復數據

刪除數據表中的重復數據，你可以使用以下的 SQL 語句：

mysql  CREATE TABLE tmp SELECT last_name,first_name, sex
 -  FROMperson_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);

MySQL 及 SQL 注入

沒有過濾特殊字符時，出現的 SQL 情況：

// 設定 $name 中插入了我們不需要的 SQL 語句

$name = Qadir DELETE FROM users;

mysql_query(SELECT* FROM users WHERE name= {$name}

以上的注入語句中，我們沒有對 $name 的變量進行過濾，$name 中插入了我們不需要的 SQL 語句，將刪除 users 表中的所有數據。

防止 SQL 注入，注意以下幾個要點：

1. 永遠不要信任用戶的輸入。對用戶的輸入進行校驗，可以通過正則表達式，或限制長度；對單引號和雙 – 進行轉換等。

2. 永遠不要使用動態拼裝 sql，可以使用參數化的 sql 或者直接使用存儲過程進行數據查詢存取。

3. 永遠不要使用管理員權限的數據庫連接，為每個應用使用單獨的權限有限的數據庫連接。

4. 不要把機密信息直接存放，加密或者 hash 掉密碼和敏感的信息。

5. 應用的異常信息應該給出盡可能少的提示，最好使用自定義的錯誤信息對原始錯誤信息進行包裝

6.sql 注入的檢測方法一般采取輔助軟件或網站平臺來檢測，軟件一般采用 sql 注入檢測工具 jsky，網站平臺就有億思網站安全平臺檢測工具。MDCSOFT SCAN 等。采用 MDCSOFT-IPS 可以有效的防御 SQL 注入，XSS 攻擊等。

  在腳本語言，如 Perl 和 PHP 可以對用戶輸入的數據進行轉義從而來防止 SQL 注入。

PHP 的 MySQL 擴展提供了 mysql_real_escape_string()函數來轉義特殊的輸入字符。

if (get_magic_quotes_gpc())
 $name = stripslashes($name);
$name =mysql_real_escape_string($name);
mysql_query(SELECT * FROMusers WHERE name= {$name}

Like 語句中的注入

like 查詢時，如用戶輸入的值有 _ 和 %，則會出現這種情況：用戶本只想查詢 abcd_，查詢結果中卻有 abcd_、abcde、abcdf 等等；用戶要查詢 30%（注：百分之三十）時也會出現問題。

在 PHP 腳本中我們可以使用 addcslashes()函數來處理以上情況，如下實例：

$sub =addcslashes(mysql_real_escape_string( %something_), %_

// $sub == \%something\_

mysql_query(SELECT * FROMmessages WHERE subject LIKE {$sub}%

addcslashes() 函數在指定的字符前添加反斜杠。

語法格式:

addcslashes(string,characters) 

MySQL 導出數據

使用 SELECT …INTO OUTFILE 語句導出數據

將數據表 w3cschool_tbl 數據導出到 /tmp/tutorials.txt 文件中:

mysql SELECT * FROMtutorials_tbl

  – INTO OUTFILE /tmp/tutorials.txt

生成一個文件，各值用逗號隔開。這種格式可以被許多程序使用。

SELECT a,b,a+b INTOOUTFILE  /tmp/result.text 
FIELDS TERMINATED BY ,  OPTIONALLY ENCLOSED BY  
LINES TERMINATED BY \n 
FROM test_table;

SELECT … INTO OUTFILE 語句有以下屬性:

LOAD DATA INFILE 是 SELECT … INTO OUTFILE 的逆操作，SELECT 句法。為了將一個數據庫的數據寫入一個文件，使用 SELECT … INTO OUTFILE，為了將文件讀回數據庫，使用 LOAD DATA INFILE。

SELECT…INTO OUTFILE file_name 形式的 SELECT 可以把被選擇的行寫入一個文件中。該文件被創建到服務器主機上，因此您必須擁有 FILE 權限，才能使用此語法。

輸出不能是一個已存在的文件。防止文件數據被篡改。

你需要有一個登陸服務器的賬號來檢索文件。否則 SELECT … INTO OUTFILE 不會起任何作用。

在 UNIX 中，該文件被創建后是可讀的，權限由 MySQL 服務器所擁有。這意味著，雖然你就可以讀取該文件，但可能無法將其刪除

  導出表作為原始數據

mysqldump 是 MySQL 用于轉存儲數據庫的實用程序。

將數據表 tutorials_tbl 導出到 /tmp 目錄中：

$ mysqldump -u root -p–no-create-info \

  –tab=/tmp W3CSCHOOL w3cschool_tbl

password ******

導出整個數據庫的數據，可以使用以下命令：

$ mysqldump -u root -pW3CSCHOOL database_dump.txt

password ******

備份所有數據庫，可以使用以下命令：

$ mysqldump -u root -p–all-databases database_dump.txt

password ******

在 mysqldump 命令中指定數據庫名及數據表。

在源主機上執行以下命令，將數據備份至 dump.txt 文件中:

$ mysqldump -u root -pdatabase_name table_name dump.txt

password *****

將備份的數據庫導入到 MySQL 服務器中，可以使用以下命令，使用以下命令你需要確認數據庫已經創建：

$ mysql -u root -pdatabase_name dump.txt password *****

以下命令將導出的數據直接導入到遠程的服務器上，但請確保兩臺服務器是相通的，是可以相互訪問的：/p

$ mysqldump -u root -pdatabase_name \

  | mysql -h other-host.com database_name

 MySQL 導入數據

從當前目錄中讀取文件 dump.txt，將該文件中的數據插入到當前數據庫的 mytbl 表中。

mysql LOAD DATALOCAL INFILE dump.txt INTO TABLE mytbl;

如果用戶指定一個 FIELDS 子句，它的子句（TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可選的，不過，用戶必須至少指定它們中的一個。

mysql LOAD DATALOCAL INFILE dump.txt INTO TABLE mytbl

  – FIELDS TERMINATED BY :

  – LINES TERMINATED BY \r\n

指定列的順序。

如，在數據文件中的列順序是 a,b,c，但在插入表的列順序為 b,c,a，則數據導入語法如下：

mysql LOAD DATALOCAL INFILE dump.txt

  – INTO TABLE mytbl (b, c, a);

使用 mysqlimport 導入數據

mysqlimport 客戶端提供了 LOADDATA INFILEQL 語句的一個命令行接口。mysqlimport 的大多數選項直接對應 LOAD DATA INFILE 子句。

從文件 dump.txt 中將數據導入到 mytbl 數據表中, 可以使用以下命令：

$ mysqlimport -u root-p –local database_name dump.txt

password *****

mysqlimport 命令可以指定選項來設置指定格式, 命令語句格式如下：

$ mysqlimport -u root-p –local –fields-terminated-by= : \

  –lines-terminated-by= \r\n   database_name dump.txt

password *****

mysqlimport 語句中使用 –columns 選項來設置列的順序：

$ mysqlimport -u root-p –local –columns=b,c,a \

  database_name dump.txt

password *****

感謝各位的閱讀！關于“MySQL 基礎的示例分析”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！