mySQL基礎入門

• 2020 年 4 月 11 日
• 筆記

mySQL

安裝

windows

windows https://www.runoob.com/mysql/mysql-install.html

mysqld –initialize –console 報錯 — 出現Failed to find valid data directory.
https://blog.csdn.net/github_38832708/article/details/83037241
mysqld --initialize --console初始化資料庫：

mysqld --initialize-insecure
程式會在動MySQL文件夾下創建data文件夾以及對應的文件

登錄

mysql -h 主機名 -u 用戶名 -p  mysql -u root -p    -h : 指定客戶端所要登錄的 MySQL 主機名, 登錄本機(localhost 或 127.0.0.1)該參數可以省略;  -u : 登錄的用戶名;  -p : 告訴伺服器將會使用一個密碼來登錄, 如果所要登錄的用戶名密碼為空, 可以忽略此選項。    輸入 exit 或 quit 退出登錄  

啟動 mysqld --console
關閉 mysqladmin -uroot shutdown

mysql服務
移除 mysqld -remove MySQL
關閉 net stop MySQL
開啟 net start MySQL

圖形介面下載
https://downloads.mysql.com/archives/workbench/
mysql workBench, 最新版好像和win10不兼容

ubuntu

安裝

sudo apt-get install mysql-server / 密碼：123
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev

啟動

mysql -uroot -p / 123

設置遠程訪問

參考 https://blog.csdn.net/james_nan/article/details/82053430

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf  修改bind-address    mysql> grant all privileges on *.* to root@"%" identified by "password" with grant option;    *.*，表示資料庫.表，也就是指所有資料庫下的所有表。%表示任意的ip地址。password為root帳號的密碼。    service mysql restart  

192.168.157.139
ip addr

關閉

關閉目前運行的 MySQL 伺服器, 你可以執行以下命令:

root@host# cd /usr/bin  ./mysqladmin -u root -p shutdown  Enter password: ******  

用戶設置

需要添加 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)             VALUES ('localhost', 'guest',             PASSWORD('guest123'), 'Y', 'Y', 'Y');  Query OK, 1 row affected (0.20 sec)    mysql> FLUSH PRIVILEGES;  Query OK, 1 row affected (0.01 sec)    mysql> SELECT host, user, password FROM user WHERE user = 'guest';  +-----------+---------+------------------+  | host      | user    | password         |  +-----------+---------+------------------+  | localhost | guest | 6f8c114b58f2ce9e |  +-----------+---------+------------------+  1 row in set (0.00 sec)  

在添加用戶時，請注意使用MySQL提供的 PASSWORD() 函數來對密碼進行加密。 你可以在以上實例看到用戶密碼加密後為： 6f8c114b58f2ce9e.

注意：在 MySQL5.7 中 user 表的 password 已換成了authentication_string。

注意：password() 加密函數已經在 8.0.11 中移除了，可以使用 MD5() 函數代替。

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

如果你不使用該命令，你就無法使用新創建的用戶來連接mysql伺服器，除非你重啟mysql伺服器。

你可以在創建用戶時，為用戶指定許可權，在對應的許可權列中，在插入語句中設置為 ‘Y’ 即可

管理MySQL

USE 資料庫名 :
選擇要操作的Mysql資料庫，使用該命令後所有Mysql命令都只針對該資料庫。

SHOW DATABASES:
列出 MySQL 資料庫管理系統的資料庫列表。（所有資料庫）

SHOW TABLES:
顯示指定資料庫的所有表(所有關係，即所有表格)，使用該命令前需要使用 use 命令來選擇要操作的資料庫。

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

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

SHOW TABLE STATUS [FROM db_name] [LIKE ‘pattern’] G:
該命令將輸出Mysql資料庫管理系統的性能及統計資訊。

mysql> SHOW TABLE STATUS  FROM RUNOOB;   # 顯示資料庫 RUNOOB 中所有表的資訊    mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%';     # 表名以runoob開頭的表的資訊  mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'G;   # 加上 G，查詢結果按列列印  

資料庫的增刪

CREATE DATABASE 資料庫名;    drop database <資料庫名>;  

數據類型

數值類型

MySQL支援所有標準SQL數值數據類型。這些類型包括嚴格數值數據類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值數據類型(FLOAT、REAL和DOUBLE PRECISION)。

關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞。BIT數據類型保存位欄位值，並且支援MyISAM、MEMORY、InnoDB和BDB表。

作為SQL標準的擴展，MySQL也支援整數類型TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個整數類型的存儲和範圍。

對於decimal, 整數的位數必須小於等於m-d，不然報錯。小數的位數可以大於d位。多出d位時會做四捨五入，截取到d位

日期和時間類型

表示時間值的日期和時間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每個時間類型有一個有效值範圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值。

TIMESTAMP類型有專有的自動更新特性，將在後面描述。

字元串類型

字元串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節描述了這些類型如何工作以及如何在查詢中使用這些類型。

注意：char(n) 和 varchar(n) 中括弧中 n 代表字元的個數，並不代表位元組個數，比如 CHAR(30) 就可以存儲 30 個字元。

CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲或檢索過程中不進行大小寫轉換。

BINARY 和 VARBINARY 類似於 CHAR 和 VARCHAR，不同的是它們包含二進位字元串而不要非二進位字元串。也就是說，它們包含位元組字元串而不是字元字元串。這說明它們沒有字符集，並且排序和比較基於列值位元組的數值值。

BLOB 是一個二進位大對象，可以容納可變數量的數據。有 4 種 BLOB 類型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區別在於可容納存儲範圍不同。

有 4 種 TEXT 類型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對應的這 4 種 BLOB 類型，可存儲的最大長度不同，可根據實際情況選擇。

注意

MySQL 5.0 以上的版本：

• 1、一個漢字佔多少長度與編碼有關：
  UTF－8：一個漢字＝3個位元組
  GBK：一個漢字＝2個位元組

• 2、varchar(n) 表示 n 個字元，無論漢字和英文，Mysql 都能存入 n 個字元，僅是實際位元組長度有所區別

char、varchar 與 text

關於 char、varchar 與 text 平時沒有太在意，一般來說，可能現在大家都是用 varchar。但是當要存儲的內容比較大時，究竟是選擇 varchar 還是 text 呢？

這三種類型比較：

• （1）char: char 不用多說了，它是定長格式的，但是長度範圍是 0~255. 當你想要儲存一個長度不足 255 的字元時，Mysql 會用空格來填充剩下的字元。因此在讀取數據時，char 類型的數據要進行處理，把後面的空格去除。
• （2）varchar: 關於 varchar，有的說最大長度是 255，也有的說是 65535，查閱很多資料後發現是這樣的：varchar 類型在 5.0.3 以下的版本中的最大長度限制為 255，而在 5.0.3 及以上的版本中，varchar 數據類型的長度支援到了 65535，也就是說可以存放 65532 個位元組（注意是位元組而不是字元！！！）的數據（起始位和結束位佔去了3個位元組），也就是說，在 5.0.3 以下版本中需要使用固定的 TEXT 或 BLOB 格式存放的數據可以在高版本中使用可變長的 varchar 來存放，這樣就能有效的減少資料庫文件的大小。
• （3）text: 與 char 和 varchar 不同的是，text 不可以有默認值，其最大長度是 2 的 16 次方-1

總結起來，有幾點：

• 經常變化的欄位用 varchar
• 知道固定長度的用 char
• 盡量用 varchar
• 超過 255 字元的只能用 varchar 或者 text
• 能用 varchar 的地方不用 text

按照查詢速度： char最快， varchar次之，text最慢。

數據表

創建數據表

以下為創建MySQL數據表的SQL通用語法：

CREATE TABLE table_name (column_name column_type);    CREATE TABLE IF NOT EXISTS `runoob_tbl`(     `runoob_id` INT UNSIGNED AUTO_INCREMENT,     `runoob_title` VARCHAR(100) NOT NULL,     `runoob_author` VARCHAR(40) NOT NULL,     `submission_date` DATE,     PRIMARY KEY ( `runoob_id` )  )ENGINE=InnoDB DEFAULT CHARSET=utf8;  

• 如果你不想欄位為 NULL 可以設置欄位的屬性為 NOT NULL， 在操作資料庫時如果輸入該欄位的數據為NULL ，就會報錯。
• AUTO_INCREMENT定義列為自增的屬性，一般用於主鍵，數值會自動加1。
• PRIMARY KEY關鍵字用於定義列為主鍵。 您可以使用多列來定義主鍵，列間以逗號分隔。
• ENGINE 設置存儲引擎，CHARSET 設置編碼。

查看數據表

desc 數據表名稱;

MySQL 欄位屬性應該盡量設置為 NOT NULL

除非你有一個很特別的原因去使用 NULL 值，你應該總是讓你的欄位保持 NOT NULL。

1、首先，我們要搞清楚空值 "" 和 NULL 的概念：

• 1）空值是不佔用空間的
• 2）MySQL中的NULL其實是佔用空間的

所謂的 NULL 就是什麼都沒有，連 都沒有， 在字元串中是結束符，但是在物理記憶體是占空間的，等於一個位元組，而 NULL 就是連這一個位元組都沒有。

2、其次，在資料庫里是嚴格區分的，任何數跟 NULL 進行運算都是 NULL, 判斷值是否等於 NULL，不能簡單用 =，而要用 IS NULL關鍵字。

3、資料庫的欄位 col1 設為 NOT NULL, 僅僅說明該欄位不能為 NULL, 也就是說只有在:

INSERT INTO table1(col1) VALUES(NULL);  

這種情況下資料庫會報錯，而:

INSERT INTO table1(col1) VALUES('');  

不會報錯。

（如果欄位是自增ID，第一句不會報錯，這不能說明是可以為NULL,而是 資料庫系統會根據ID設的預設值填充，或者如果是自增欄位就自動加一等預設操作。）

4、 含有空值的列很難進行查詢優化，而且對錶索引時不會存儲 NULL 值的，所以如果索引的欄位可以為 NULL，索引的效率會下降很多。因為它們使得索引、索引的統計資訊以及比較運算更加複雜。你應該用 0、一個特殊的值或者一個空串代替空值。

5、聯表查詢的時候，例如 LEFT JOIN table2，若沒有記錄，則查找出的 table2 欄位都是 null。假如 table2 有些欄位本身可以是 null，那麼除非把 table2 中 not null 的欄位查出來，否則就難以區分到底是沒有關聯記錄還是其他情況。

刪除數據表

針對 表結構，innodb 和 MyISAM有4種級別的刪除。

DROP TABLE table_name;  刪除表全部數據和表結構，立刻釋放磁碟空間，不管是 Innodb 和 MyISAM;      truncate table table_name;  刪除表全部數據，保留表結構，立刻釋放磁碟空間 ，不管是 Innodb 和 MyISAM;      delete from table_name;  刪除表全部數據，表結構不變，對於 MyISAM 會立刻釋放磁碟空間，InnoDB 不會釋放磁碟空間;      delete from table_name where xxx : 帶條件的刪除，表結構不變，不管是 innodb 還是 MyISAM 都不會釋放磁碟空間;  實例，刪除學生表中姓名為 "張三" 的數據：  delete from student where T_name = "張三";  delete 操作以後，使用 optimize table table_name 會立刻釋放磁碟空間，不管是 innodb 還是 myisam;    

MySQL 資料庫刪除數據的三種方式：

delete from table where

直接刪除表中的某一行數據，並且同時將該行的刪除操作作為事務記錄在日誌中保存以便進行進行回滾操作。所以 delete 相比較 truncate 更加佔用資源，數據空間不釋放，因為需回滾。對 table 和 view 都能操作。

truncate table

一次性地從表中刪除所有的數據(釋放存儲表數據所用的數據頁來刪除數據)並不把單獨的刪除操作記錄記入日誌保存(只在事務日誌中記錄 頁的釋放)，因此也不能回滾，不能恢複數據，在刪除的過程中不會激活與表有關的刪除觸發器，佔用資源更加少，速度更快。數據空間會釋放，這個表和索引所佔用的空間會恢復到初始大小。只能操作沒有關聯視圖的 table。

truncate table 不能用於參與了索引視圖的表。

drop table

刪除的是整個表，包括表的結構，數據，定義。永久抹去，空間釋放。對 table 和 view 都能操作。由於 TRUNCATE TABLE 不記錄在日誌中，所以它不能激活觸發器，對於外鍵（foreignkey ）約束引用的表，不能使用 truncate table，而應使用不帶 where 子句的 delete 語句。

增刪改查

增

以下為向MySQL數據表插入數據通用的 INSERT INTO SQL語法：

INSERT INTO table_name ( field1, field2,...fieldN )                         VALUES                         ( value1, value2,...valueN );    INSERT INTO table_name  (field1, field2,...fieldN)  VALUES  (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;  

如果所有的列都要添加數據可以不規定列進行添加數據
如果數據是字元型，必須使用單引號或者雙引號，如："value"。

mysql> INSERT INTO runoob_tbl      -> (runoob_title, runoob_author, submission_date)      -> VALUES      -> ("學習 PHP", "菜鳥教程", NOW());  

我們並沒有提供 runoob_id 的數據，因為該欄位我們在創建表的時候已經設置它為 AUTO_INCREMENT(自動增加) 屬性。 所以，該欄位會自動遞增而不需要我們去設置。實例中 NOW() 是一個 MySQL 函數，該函數返回日期和時間。

讀取表的數據

select * from runoob_tbl;

查

以下為在MySQL資料庫中查詢數據通用的 SELECT 語法：

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

• 查詢語句中你可以使用一個或者多個表，表之間使用逗號(,)分割，並使用WHERE語句來設定查詢條件。
• SELECT 命令可以讀取一條或者多條記錄。
• 你可以使用星號（*）來代替其他欄位，SELECT語句會返回表的所有欄位數據
• 你可以使用 WHERE 語句來包含任何條件。
• 你可以使用 LIMIT 屬性來設定返回的記錄數。limit N,M : 相當於 limit M offset N , 從第 N 條記錄開始, 返回 M 條記錄 (-1表最後)
• 你可以通過OFFSET指定SELECT語句開始查詢的數據偏移量。默認情況下偏移量為0。

where

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

• 查詢語句中你可以使用一個或者多個表，表之間使用逗號, 分割，並使用WHERE語句來設定查詢條件。
• 你可以在 WHERE 子句中指定任何條件。
• 你可以使用 AND 或者 OR 指定一個或多個條件。
• WHERE 子句也可以運用於 SQL 的 DELETE 或者 UPDATE 命令。
• WHERE 子句類似於程式語言中的 if 條件，根據 MySQL 表中的欄位值來讀取指定的數據。

使用主鍵來作為 WHERE 子句的條件查詢是非常快速的。
如果給定的條件在表中沒有任何匹配的記錄，那麼查詢不會返回任何數據。

MySQL 的 WHERE 子句的字元串比較是不區分大小寫的。 你可以使用 BINARY 關鍵字來設定 WHERE 子句的字元串比較是區分大小寫的。

mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author='runoob.com';

like

類似正則匹配

LIKE 子句中使用百分號 %字元來表示任意字元，類似於UNIX或正則表達式中的星號 *****。

SELECT field1, field2,...fieldN  FROM table_name  WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'  

like 匹配/模糊匹配，會與 % 和 _ 結合使用。

'%a'     //以a結尾的數據  'a%'     //以a開頭的數據  '%a%'    //含有a的數據  '_a_'    //三位且中間字母是a的  '_a'     //兩位且結尾字母是a的  'a_'     //兩位且開頭字母是a的  

在 where like 的條件查詢中，SQL 提供了四種匹配方式。

1. %：表示任意 0 個或多個字元。可匹配任意類型和長度的字元，有些情況下若是中文，請使用兩個百分號（%%）表示。
2. _：表示任意單個字元。匹配單個任意字元，它常用來限制表達式的字元長度語句。
3. []：表示括弧內所列字元中的一個（類似正則表達式）。指定一個字元、字元串或範圍，要求所匹配對象為它們中的任一個。
4. [^] ：表示不在括弧所列之內的單個字元。其取值和 [] 相同，但它要求所匹配對象為指定字元以外的任一個字元。
5. 查詢內容包含通配符時,由於通配符的緣故，導致我們查詢特殊字元 「%」、「_」、「[」 的語句無法正常實現，而把特殊字元用 「[ ]」 括起便可正常查詢。

正則

查找name欄位中以母音字元開頭或以’ok’字元串結尾的所有數據：

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

改

UPDATE table_name SET field1=new-value1, field2=new-value2  [WHERE Clause]  

• 你可以同時更新一個或多個欄位。
• 你可以在 WHERE 子句中指定任何條件。
• 你可以在一個單獨表中同時更新數據。

當我們需要將欄位中的特定字元串批量修改為其他字元串時，可已使用以下操作：

UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string')  [WHERE Clause]  

刪

DELETE FROM table_name [WHERE Clause]  

• 如果沒有指定 WHERE 子句，MySQL 表中的所有記錄將被刪除。
• 你可以在 WHERE 子句中指定任何條件
• 您可以在單個表中一次性刪除記錄。

執行的速度上，drop>truncate>delete

union

SELECT expression1, expression2, ... expression_n  FROM tables  [WHERE conditions]  UNION [ALL | DISTINCT]  SELECT expression1, expression2, ... expression_n  FROM tables  [WHERE conditions];  

有點自然連接的意思，但是select的屬性必須相同

參數

• expression1, expression2, … expression_n: 要檢索的列。
• tables: 要檢索的數據表。
• WHERE conditions: 可選， 檢索條件。
• DISTINCT: 可選，刪除結果集中重複的數據。默認情況下 UNION 操作符已經刪除了重複數據，所以 DISTINCT 修飾符對結果沒啥影響。
• ALL: 可選，返回所有結果集，包含重複數據。

實例

下面的 SQL 語句從 "Websites" 和 "apps" 表中選取所有不同的country（只有不同的值）：

SELECT country FROM Websites  UNION  SELECT country FROM apps  ORDER BY country;  

排序

SELECT field1, field2,...fieldN FROM table_name1, table_name2...  ORDER BY field1 [ASC [DESC][默認 ASC]], [field2...] [ASC [DESC][默認 ASC]]  

• 你可以使用任何欄位來作為排序的條件，從而返回排序後的查詢結果。
• 你可以設定多個欄位來排序。
• 你可以使用 ASC 或 DESC 關鍵字來設置查詢結果是按升序或降序排列。 默認情況下，它是按升序排列。
• 你可以添加 WHERE…LIKE 子句來設置條件。

MySQL 拼音排序

如果字符集採用的是 gbk(漢字編碼字符集)，直接在查詢語句後邊添加 ORDER BY：

SELECT *  FROM runoob_tbl  ORDER BY runoob_title;  

如果字符集採用的是 utf8(萬國碼)，需要先對欄位進行轉碼convert然後排序：

SELECT *  FROM runoob_tbl  ORDER BY CONVERT(runoob_title using gbk);  

分組

GROUP BY 語句根據一個或多個列對結果集進行分組。相同的分一組

在分組的列上我們可以使用 COUNT, SUM, AVG,等函數。

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

使用 GROUP BY 語句 將數據表按名字進行分組，並統計每個人有多少條記錄：

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;  

WITH ROLLUP

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

例如我們將以上的數據表按名字進行分組，再統計每個人登錄的次數：

mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;  +--------+--------------+  | name   | singin_count |  +--------+--------------+  | 小麗 |            2 |  | 小明 |            7 |  | 小王 |            7 |  | NULL   |           16 |  +--------+--------------+  4 rows in set (0.00 sec)  

其中記錄 NULL 表示所有人的登錄次數。

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

select coalesce(a,b,c);  

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

以下實例中如果名字為空我們使用總數代替：

mysql> SELECT coalesce(name, '總數'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;  +--------------------------+--------------+  | coalesce(name, '總數') | singin_count |  +--------------------------+--------------+  | 小麗                   |            2 |  | 小明                   |            7 |  | 小王                   |            7 |  | 總數                   |           16 |  +--------------------------+--------------+  4 rows in set (0.01 sec)  

SELECT c.*  FROM (      SELECT IFNULL(b.城市,"總計") AS 城市,SUM(IF(b.年月=201607,b.金額,NULL)) AS 7月金額,SUM(IF(b.年月=201608,b.金額,NULL)) AS 8月金額,SUM(IF(b.年月=201609,b.金額,NULL)) AS 9月金額      FROM (          SELECT IFNULL(city,'空城市') AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金額          FROM test_a03order AS a          GROUP BY city,DATE_FORMAT(order_time,"%Y%m")      ) AS b      GROUP BY b.城市 WITH ROLLUP  ) AS c  ORDER BY FIELD(城市,'總計'),c.9月金額 DESC  

連接

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

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

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

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;    mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;  

left join 與 join 有所不同。 MySQL LEFT JOIN 會讀取左邊數據表的全部數據，即便右邊表無對應數據。

嘗試以下實例，以 runoob_tbl 為左表，tcount_tbl 為右表，理解 MySQL LEFT JOIN 的應用：

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;  

用集合去理解

NULL

MySQL 使用 SQL SELECT 命令及 WHERE 子句來讀取數據表中的數據,但是當提供的查詢條件欄位為 NULL 時，該命令可能就無法正常工作。

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

• IS NULL: 當列的值是 NULL,此運算符返回 true。
• IS NOT NULL: 當列的值不為 NULL, 運算符返回 true。
• <=>: 比較操作符（不同於 = 運算符），當比較的的兩個值相等或者都為 NULL 時返回 true。

關於 NULL 的條件比較運算是比較特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。在 MySQL 中，NULL 值與任何其它值的比較（即使是 NULL）永遠返回 NULL，即 NULL = NULL 返回 NULL 。

MySQL 中處理 NULL 使用 IS NULL 和 IS NOT NULL 運算符。

select * , columnName1+ifnull(columnName2,0) from tableName;  

columnName1，columnName2 為 int 型，當 columnName2 中，有值為 null 時，columnName1+columnName2=null， ifnull(columnName2,0) 把 columnName2 中 null 值轉為 0。

mysql> SELECT * from runoob_test_tbl WHERE runoob_count IS NOT NULL;

事務

MySQL 事務主要用於處理操作量大，複雜度高的數據。比如說，在人員管理系統中，你刪除一個人員，你既需要刪除人員的基本資料，也要刪除和該人員相關的資訊，如信箱，文章等等，這樣，這些資料庫操作語句就構成一個事務！

• 在 MySQL 中只有使用了 Innodb 資料庫引擎的資料庫或表才支援事務。
• 事務處理可以用來維護資料庫的完整性，保證成批的 SQL 語句要麼全部執行，要麼全部不執行。
• 事務用來管理 insert,update,delete 語句

一般來說，事務是必須滿足4個條件（ACID）：：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability）。

• 原子性：一個事務（transaction）中的所有操作，要麼全部完成，要麼全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。
• 一致性：在事務開始之前和事務結束以後，資料庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及後續資料庫可以自發性地完成預定的工作。
• 隔離性：資料庫允許多個並發事務同時對其數據進行讀寫和修改的能力，隔離性可以防止多個事務並發執行時由於交叉執行而導致數據的不一致。事務隔離分為不同級別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重複讀（repeatable read）和串列化（Serializable）。
• 持久性：事務處理結束後，對數據的修改就是永久的，即便系統故障也不會丟失。

在 MySQL 命令行的默認設置下，事務都是自動提交的，即執行 SQL 語句後就會馬上執行 COMMIT 操作。因此要顯式地開啟一個事務務須使用命令 BEGIN 或 START TRANSACTION，或者執行命令 SET AUTOCOMMIT=0，用來禁止使用當前會話的自動提交。

事務控制語句

• BEGIN 或 START TRANSACTION 顯式地開啟一個事務；
• COMMIT 也可以使用 COMMIT WORK，不過二者是等價的。COMMIT 會提交事務，並使已對資料庫進行的所有修改成為永久性的；
• ROLLBACK 也可以使用 ROLLBACK WORK，不過二者是等價的。回滾會結束用戶的事務，並撤銷正在進行的所有未提交的修改；
• SAVEPOINT identifier，SAVEPOINT 允許在事務中創建一個保存點，一個事務中可以有多個 SAVEPOINT；
• RELEASE SAVEPOINT identifier 刪除一個事務的保存點，當沒有指定的保存點時，執行該語句會拋出一個異常；
• ROLLBACK TO identifier 把事務回滾到標記點；
• SET TRANSACTION 用來設置事務的隔離級別。InnoDB 存儲引擎提供事務的隔離級別有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事務處理

1、用 BEGIN, ROLLBACK, COMMIT來實現

• BEGIN 開始一個事務
• ROLLBACK 事務回滾
• COMMIT 事務確認

2、直接用 SET 來改變 MySQL 的自動提交模式:

• SET AUTOCOMMIT=0 禁止自動提交
• SET AUTOCOMMIT=1 開啟自動提交

表的修改

alter

當我們需要修改數據表名或者修改數據表欄位時，就需要使用到MySQL ALTER命令。

刪

如下命令使用了 ALTER 命令及 DROP 子句來刪除以上創建表的 i 欄位：

mysql> ALTER TABLE testalter_tbl  DROP i;  

如果數據表中只剩餘一個欄位則無法使用DROP來刪除欄位。

增

MySQL 中使用 ADD 子句來向數據表中添加列，如下實例在表 testalter_tbl 中添加 i 欄位，並定義數據類型:

mysql> ALTER TABLE testalter_tbl ADD i INT;  

執行以上命令後，i 欄位會自動添加到數據表欄位的末尾。

移位

如果你需要指定新增欄位的位置，可以使用MySQL提供的關鍵字 FIRST (設定位第一列)， AFTER 欄位名（設定位於某個欄位之後）。

嘗試以下 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 與 MODIFY 子句，所以如果你想重置數據表欄位的位置就需要先使用 DROP 刪除欄位然後使用 ADD 來添加欄位並設置位置。

刪除外鍵約束

keyName是外鍵別名

alter table tableName drop foreign key keyName;  

修改名稱和類型

如果需要修改欄位類型及名稱, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

例如，把欄位 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;  mysql> ALTER TABLE testalter_tbl CHANGE j j INT;  

Null 值和默認值的影響

當你修改欄位時，你可以指定是否包含值或者是否設置默認值。

以下實例，指定欄位 j 為 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;  

使用 ALTER 命令及 DROP子句來刪除欄位的默認值，如下實例：

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;  

數據表類型

修改數據表類型，可以使用 ALTER 命令及 TYPE 子句來完成。嘗試以下實例，我們將表 testalter_tbl 的類型修改為 MYISAM ：

注意：查看數據表類型可以使用 SHOW TABLE STATUS 語句。

mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;  mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'G  

修改表名

如果需要修改數據表的名稱，可以在 ALTER TABLE 語句中使用 RENAME 子句來實現。

嘗試以下實例將數據表 testalter_tbl 重命名為 alter_tbl：

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;  

索引

索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索引包含多個列。

創建索引時，你需要確保該索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。

實際上，索引也是一張表，該表保存了主鍵與索引欄位，並指向實體表的記錄。

上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。建立索引會佔用磁碟空間的索引文件。

普通索引

創建

CREATE INDEX indexName ON mytable(username(length));  

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

創建表的時候直接指定

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

添加

ALTER table tableName ADD INDEX indexName(columnName)  

刪除

DROP INDEX [indexName] ON mytable;  

唯一索引

它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。

創建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))  

修改表結構

ALTER table mytable ADD UNIQUE [indexName] (username(length))  

創建表的時候直接指定

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

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

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

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):該語句添加一個主鍵，這意味著索引值必須是唯一的，且不能為NULL。
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 這條語句創建索引的值必須是唯一的（除了NULL外，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 命令添加和刪除主鍵

主鍵只能作用於一個列上，添加主鍵索引時，你需要確保該主鍵默認不為空（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;  

刪除主鍵時只需指定PRIMARY KEY，但在刪除索引時，你必須知道索引名。

顯示索引

mysql> SHOW INDEX FROM table_name; G  

表操作

臨時表

MySQL 臨時表在我們需要保存一些臨時數據時是非常有用的。臨時表只在當前連接可見，當關閉連接時，Mysql會自動刪除表並釋放所有空間。

如果你使用了其他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  );  

當你使用 SHOW TABLES命令顯示數據表列表時，你將無法看到 SalesSummary表。

如果你退出當前MySQL會話，再使用 SELECT命令來讀取原先創建的臨時表數據，那你會發現資料庫中沒有該表的存在，因為在你退出時該臨時表已經被銷毀了。

用 DROP TABLE 命令來手動刪除臨時表。

用查詢直接創建臨時表的方式：

CREATE TEMPORARY TABLE 臨時表名 AS  (      SELECT *  FROM 舊的表名      LIMIT 0,10000  );  

複製表

• 使用 SHOW CREATE TABLE 命令獲取創建數據表(CREATE TABLE) 語句，該語句包含了原數據表的結構，索引等。
• 複製以下命令顯示的SQL語句，修改數據表名，並執行SQL語句，通過以上命令 將完全的複製數據表結構。
• 如果你想複製表的內容，你就可以使用 INSERT INTO … SELECT 語句來實現。

mysql> SHOW CREATE TABLE runoob_tbl G;  *************************** 1. row ***************************         Table: runoob_tbl  Create Table: CREATE TABLE `runoob_tbl` (    `runoob_id` int(11) NOT NULL auto_increment,    `runoob_title` varchar(100) NOT NULL default '',    `runoob_author` varchar(40) NOT NULL default '',    `submission_date` date default NULL,    PRIMARY KEY  (`runoob_id`),    UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)  ) ENGINE=InnoDB  1 row in set (0.00 sec)    ERROR:  No query specified        mysql> CREATE TABLE `clone_tbl` (    -> `runoob_id` int(11) NOT NULL auto_increment,    -> `runoob_title` varchar(100) NOT NULL default '',    -> `runoob_author` varchar(40) NOT NULL default '',    -> `submission_date` date default NULL,    -> PRIMARY KEY  (`runoob_id`),    -> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)  -> ) ENGINE=InnoDB;  Query OK, 0 rows affected (1.80 sec)        mysql> INSERT INTO clone_tbl (runoob_id,      ->                        runoob_title,      ->                        runoob_author,      ->                        submission_date)      -> SELECT runoob_id,runoob_title,      ->        runoob_author,submission_date      -> FROM runoob_tbl;  Query OK, 3 rows affected (0.07 sec)  Records: 3  Duplicates: 0  Warnings: 0  

另一種完整複製表的方法:

CREATE TABLE targetTable LIKE sourceTable;  只複製表結構到新表    INSERT INTO targetTable SELECT * FROM sourceTable;  

複製表結構及數據到新表  create table 新表 select * from 舊錶  # 有*號沒AS  

可以拷貝一個表中其中的一些欄位:

CREATE TABLE newadmin AS  (      SELECT username, password FROM admin  )  

可以將新建的表的欄位改名:

CREATE TABLE newadmin AS  (      SELECT id, username AS uname, password AS pass FROM admin  )  

可以在創建表的同時定義表中的欄位資訊:

CREATE TABLE newadmin  (      id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY  )  AS  (      SELECT * FROM admin  )  

序列

AUTO_INCREMENT

MySQL 序列是一組整數：1, 2, 3, …，由於一張數據表只能有一個欄位自增主鍵， 如果你想實現其他欄位也實現自動增加，就可以使用MySQL序列來實現。

MySQL 中最簡單使用序列的方法就是使用 MySQL AUTO_INCREMENT 來定義列。

在MySQL的客戶端中你可以使用 SQL中的LAST_INSERT_ID( ) 函數來獲取最後的插入表中的自增列的值。

UUID

在mysql5.0之前，如果是多個master複製的環境，無法用自增主鍵，因為可能重複。在5.0以及之後的版本通過配置自增偏移量解決了整個問題。

uuid全球唯一。

什麼情況下我們希望用uuid

1. 避免重複，便於scale，這就是我們做cloud service的時候選擇uuid的主要原因
2. 入庫之前可以知道id
3. 相對安全，不能簡單的從uuid獲取資訊，但是如果自增，則容易暴露資訊，如果一個客戶id是123456，很容易猜到有客戶id是123456.

在我們實際到項目中會碰到問題，歷史數據表的主鍵id會與數據表的id重複，兩張自增id做主鍵的表合併時，id一定會有衝突，但如果各自的id還關聯了其他表，這就很不好操作。

如果使用UUID，生成的ID不僅是表獨立的，而且是庫獨立的。對以後的數據操作很有好處，可以說一勞永逸。

UUID有什麼問題

1.uuid有16個位元組，比int（4 byte）和bigint（8 byte）佔用更多存儲空間

2.由於size和無序性，可能引起性能問題

缺點:

1. 影響插入速度， 並且造成硬碟使用率低
2. uuid之間比較大小相對數字慢不少， 影響查詢速度。
3. uuid占空間大， 如果你建的索引越多， 影響越嚴重

優點：出現數據拆分、合併存儲的時候，能達到全局的唯一性

uuid 與 自增主鍵

InnoDB主索引：葉節點包含了完整的數據記錄。這種索引叫做聚集索引。InnoDB 的索引能提供一種非常快速的主鍵查找性能。不過，它的輔助索引也會包含主鍵列，所以，如果主鍵定義的比較大，其他索引也將很大。如果想在表上定義 、很多索引，則爭取盡量把主鍵定義得小一些。InnoDB 不會壓縮索引

聚集索引這種實現方式使得按主鍵的搜索十分高效，但是輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄。

(1).如果InnoDB表的數據寫入順序能和B+樹索引的葉子節點順序一致的話，這時候存取效率是最高的。為了存儲和查詢性能應該使用自增長id做主鍵。 （如果自增索引作為主鍵構造B+樹，那B+樹的創建過程將會很「均勻」，每層鋪滿才會下一層）

(2).對於InnoDB的主索引，數據會按照主鍵進行排序，由於UUID的無序性，InnoDB會產生巨大的IO壓力，此時不適合使用UUID做物理主鍵，可以把它作為邏輯主鍵，物理主鍵依然使用自增ID。為了全局的唯一性，應該用uuid做索引關聯其他表或做外鍵。

如果是uuid的形式，頻繁的插入會使innodb頻繁地移動磁碟塊，寫入性能就比較低了。

重置序列

如果你刪除了數據表中的多條記錄，並希望對剩下數據的AUTO_INCREMENT列進行重新排列，那麼你可以通過刪除自增的列，然後重新添加來實現。 不過該操作要非常小心，如果在刪除的同時又有新記錄添加，有可能會出現數據混亂

mysql> ALTER TABLE insect DROP id;  mysql> ALTER TABLE insect      -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,      -> ADD PRIMARY KEY (id);  

設置序列的開始值

一般情況下序列的開始值為1，但如果你需要指定一個開始值100，那我們可以通過以下語句來實現：

mysql> CREATE TABLE insect      -> (      -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,      -> PRIMARY KEY (id),      -> name VARCHAR(30) NOT NULL,      -> date DATE NOT NULL,      -> origin VARCHAR(30) NOT NULL  )engine=innodb auto_increment=100 charset=utf8;  

或者你也可以在表創建成功後，通過以下語句來實現：

mysql> ALTER TABLE t AUTO_INCREMENT = 100;  

處理重複數據

防止表中出現重複數據

你可以在 MySQL 數據表中設置指定的欄位為 PRIMARY KEY（主鍵） 或者 UNIQUE（唯一） 索引來保證數據的唯一性。

如果你設置了雙主鍵，那麼那個鍵的默認值不能為 NULL，可設置為 NOT NULL。如下所示：

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 INTO 與 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)  mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)      -> VALUES( 'Jay', 'Thomas');  Query OK, 0 rows affected (0.00 sec)  

INSERT IGNORE INTO 當插入數據時，在設置了記錄的唯一性後，如果插入重複數據，將不返回錯誤，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的記錄，則先刪除掉。再插入新記錄。

另一種設置數據的唯一性方法是添加一個 UNIQUE 索引，如下所示：

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;  

你也可以使用 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 FROM person_tbl  GROUP BY (last_name, first_name, sex);  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);  

導出/入數據

SELECT … INTO OUTFILE 語句導出數據

以下實例中我們將數據表 runoob_tbl 數據導出到 /tmp/runoob.txt 文件中:

mysql> SELECT * FROM runoob_tbl      -> INTO OUTFILE '/tmp/runoob.txt';  

你可以通過命令選項來設置數據輸出的指定格式，以下實例為導出 CSV 格式：

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'      -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'      -> LINES TERMINATED BY 'rn';  

在下面的例子中，生成一個文件，各值用逗號隔開。這種格式可以被許多程式使用。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  LINES TERMINATED BY 'n'  FROM test_table;  

mysqldump導出表

mysqldump 是 mysql 用於轉存儲資料庫的實用程式。它主要產生一個 SQL 腳本，其中包含從頭重新創建資料庫所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 導出數據需要使用 –tab 選項來指定導出文件指定的目錄，該目標必須是可寫的。

以下實例將數據表 runoob_tbl 導出到 /tmp 目錄中  $ mysqldump -u root -p --no-create-info               --tab=/tmp RUNOOB runoob_tbl  password ******      導出 SQL 格式的數據到指定文件  $ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt  password ******      需要備份所有資料庫，可以使用以下命令：  $ mysqldump -u root -p --all-databases > database_dump.txt  password ******  

導入表

如果你需要將備份的資料庫導入到MySQL伺服器中，可以使用以下命令，使用以下命令你需要確認資料庫已經創建：

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

使用 mysql 命令導入語法格式為：

mysql -u用戶名    -p密碼    <  要導入的資料庫數據(runoob.sql)  

MySQL 中提供了LOAD DATA INFILE語句來插入數據。 以下實例中將從當前目錄中讀取文件 dump.txt ，將該文件中的數據插入到當前資料庫的 mytbl 表中。

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

　如果指定LOCAL關鍵詞，則表明從客戶主機上按路徑讀取文件。如果沒有指定，則文件在伺服器上按路徑讀取文件。

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

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl    -> FIELDS TERMINATED BY ':'    -> LINES TERMINATED BY 'rn';      mysql> LOAD DATA LOCAL INFILE 'dump.txt'    -> INTO TABLE mytbl (b, c, a);  

mysqlimport

函數

含義：一組預先編譯好的SQL語句的集合，理解成批處理語句

函數：有且僅有1 個返回，合適做處理數據後返回一個結果 做查詢一個值

​

注意：  1.參數列表包含兩部分：參數名 參數類型  2.函數體：肯定會有return語句，如果沒有會報錯  如果return語句放在函數的最後也不報錯，但不建議  3.函數題中僅有一句話，則可以省略begin end  4.使用 delimter語句設置結束標記              #二、調用語法  SELECT 函數名(參數列表)  執行函數中所有語句 函數中有顯示的語句他也能顯示 有插入修改也能做，執行完把顯示值返回出來  CREATE FUNCTION myf1() RETURNS INT  BEGIN  	DECLARE c INT DEFAULT 0;#定義局部變數  	SELECT COUNT(*) INTO c #賦值與c  	FROM employees;  	RETURN c;  END$