網(wǎng)絡(luò)圖片

1.簡介

mysql是一個快速的硬纤、多線程的、多用戶的咱士、支持健壯SQL的數(shù)據(jù)庫管理系統(tǒng)（DBMS）立由。mysql可以應(yīng)用在關(guān)鍵任務(wù)、高并發(fā)的系統(tǒng)上序厉，也可以用來部署大規(guī)模軟件锐膜。mysql最新版本5.7用戶手冊官網(wǎng)： mysql5.7用戶手冊

2.連接mysql

要使用mysql數(shù)據(jù)庫，需要用mysql客戶端連接到mysql服務(wù)器弛房。服務(wù)器可以是你自己創(chuàng)建的道盏，也可以是他人提供的主機，客戶端有三種：mysql命令行工具庭再、mysql administrator和mysql query browser捞奕。

mysq連接

1.SHOW DATABASES; #查看服務(wù)器上所有的數(shù)據(jù)庫
2.SHOW TABLES; #查看當(dāng)前數(shù)據(jù)庫上所有的表
3.USE DATABASE; #選擇所要操縱的書籍
4.DESCRIBE TABLE; #描述表結(jié)構(gòu)

3.查詢

• 檢索數(shù)據(jù)

1.SELECT * FROM table; #檢測全部列
2.SELECT column1,column2 ... FROM table; #檢索部分列
3.SELECT DISTINCT * FROM table; #檢索不同列，DISTINCT后面所有列
4.SELECT * FROM table LIMIT 3 OFFSET 0; #限制輸出結(jié)果
5.SELECT * FROM table ORDER BY column DESC/ASC恨搓；#輸出結(jié)果按序排列

• 過濾數(shù)據(jù)

檢索的數(shù)據(jù)可以通過where子句過濾院促。

比如：SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;語句檢索的是prod_price值為2.50的行記錄。SELECT prod_name, prod_price FROM products WHERE prod_price < 10;語句檢索的是prod_price小于10的行記錄常拓。

where子句操作符

此外，where子句也支持 空值檢查弄抬，SELECT cust_id FROM customers WHERE cust_email IS NULL;語句檢索的email值為空的行記錄茎辐。

where子句 IN 操作符用來指定條件范圍，SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name;語句檢索的是供應(yīng)商1002和1003制造的所有產(chǎn)品掂恕。

where子句 NOT 操作符否定它之后所跟的任何條件拖陆，SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name;語句比上述多了一個NOT操作符，檢索的是除1002和1003之外的所有供應(yīng)商制造的產(chǎn)品懊亡。

為了進行更強的過濾控制依啰，MySQL允許給出多個 WHERE 子句。這些子句可以同過 AND 或 OR 來組合使用店枣。SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price < 10;語句檢索供應(yīng)商1003制造的價格小于10的所有產(chǎn)品速警。當(dāng)AND 和OR操作符同時出現(xiàn)在一個查詢子句中，AND比OR的優(yōu)先級更高鸯两，可以用括號來顯示的指定執(zhí)行的次序闷旧。

where子句也支持通配符 LIKE 過濾，通配符包括%（匹配任意字符任意次數(shù)）和（匹配任意單個字符）甩卓。SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';將檢索產(chǎn)品名以jet起頭的產(chǎn)品鸠匀。*SELECT prod_id, prod_name FROM products WHERE prod_name LIKE ' ton anvil';*將檢索產(chǎn)品名稱1 ton anvil和2 ton anvil。注意LIKE '%'不能匹配NULL值逾柿。

where子句支持正則表達式 REGEXP 過濾≌耍基本的語法格式類似為SELECT prod_name FROM products WHERE prod_name REGEXP '1000 | 2000';將匹配產(chǎn)品名字中含有1000或2000的記錄机错。正則表達式的語法不在本文中詳述。

• 分組數(shù)據(jù)

分組允許把數(shù)據(jù)分為多個邏輯組父腕，再對每個組進行聚集運算弱匪、檢索等操作，這主要使用 GROUP BY 子句和 HAVING 子句璧亮。
SELECT vend_id, COUNT( * ) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT( * ) >= 2;語句檢索出了具有2個以上萧诫、價格為10以上的產(chǎn)品的供應(yīng)商。

• 子查詢

sql允許創(chuàng)建子查詢枝嘶，即嵌套在其它查詢中的查詢帘饶。SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

• 組合查詢

sql允許通過 UNION 操作符將多條SELECT語句組合成一個結(jié)果集。
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);
UNION默認清除了重復(fù)的行群扶，使用UNION ALL可以保留重復(fù)的行。

4.聯(lián)結(jié)表

聯(lián)結(jié)的創(chuàng)建很簡單暑劝，規(guī)定要聯(lián)結(jié)的所有表以及他們?nèi)绾侮P(guān)聯(lián)（通過外鍵）即可担猛。SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;語句中WHERE子句的聯(lián)結(jié)條件非常重要傅联，如果沒有聯(lián)結(jié)條件纺且，將返回笛卡爾積载碌，檢索出的行數(shù)為第一個表中的行數(shù)乘以第二個表中的行數(shù)衅枫。

• 內(nèi)部聯(lián)結(jié)

SELECT customers.cust_id, orders.order_num FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id;

• 外部聯(lián)結(jié)

SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

內(nèi)部聯(lián)結(jié)又稱等值聯(lián)結(jié)步咪，和上面所用的where子句效果相同猾漫。而外部聯(lián)結(jié)分為左聯(lián)結(jié)和右聯(lián)結(jié)悯周，它還包括沒有關(guān)聯(lián)的行禽翼。

5.mysql聚合函數(shù)

• 拼接字段（Concat）

SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;

• 文本處理函數(shù)

SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

常用文本處理函數(shù)

常用文本處理函數(shù)續(xù)

• 數(shù)字處理函數(shù)

常用數(shù)值處理函數(shù)

• 時間和日期處理函數(shù)

SELECT cust_id, order_num FROM orders WHERE Data(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

常用日期和時間處理函數(shù)

• 聚合函數(shù)

我們經(jīng)常需要匯總分析數(shù)據(jù)而不用實際把它們檢索出來礁哄，比如找出表列的最大值、最小值花枫、平均值等劳翰。

SELECT AVG(prod_price) AS avg_price FROM products;

sql聚合函數(shù)

6.插入马胧、更新佩脊、刪除數(shù)據(jù)

• 插入數(shù)據(jù)

INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES ('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA'), ('M.Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');

INSERT除了可以直接插入一個指定的行威彰，還可以通過所謂的INSERT SELECT將一條SELECT語句的結(jié)果插入表中歇盼。

INSERT INTO customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) SELECT cust_id, cust_contact, cust_emial, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country FROM custnew;

• 更新數(shù)據(jù)

UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;

• 刪除數(shù)據(jù)

DELETTE FROM customers WHERE cust_id = 10006;
若沒有WHERE子句伯复，UPDATE和DELETE語句都將更新或刪除整個表記錄邢笙。
DELETE不會刪除表鸣剪，只會刪除表記錄筐骇，可以使用TRUNCATE TABLE語句完成相同的工作铛纬，且速度更快。

7.視圖

視圖不包含表中任何列和數(shù)據(jù)擒悬，它包含的是一個SQL查詢懂牧，用于重用SQL語句僧凤，簡化復(fù)雜的SQL操作躯保，保護數(shù)據(jù)途事。

1.CREATE VIEW #創(chuàng)建視圖
2.SHOW CREATE VIEW viewname #查看創(chuàng)建視圖的語句
3.DROP VIEW viewname #刪除視圖

CREATE VIEW productcustomers AS SELECT cust_name, cust_contact, prod_id FORM customers, orders, orderitems WHERE custmoer.cust_id = order.cust_id AND orderitems.order_num = orders.order_num; ##創(chuàng)建productcustomers視圖

SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2'; ##使用productcustomers視圖

8.存儲過程

簡單來說盯孙，存儲過程是一條或多條mysql語句的集合振惰。通過封裝在容易使用的單元中骑晶，簡化復(fù)雜的操作桶蛔，同時保證了數(shù)據(jù)的完整性仔雷，而且提高了性能（使用存儲過程比使用單獨的sql語句要快）。

• 創(chuàng)建存儲過程

CREATE PROCEDURE productpricing(
  OUT p1 DECIMAL(8,2),
  OUT ph DECIMAL(8,2),
  OUT pa DECIMAL(8,2)
)
BEGIN
  SELECT Min(prod_price)
  INTO p1
  FROM products;
  SELECT Max(prod_price)
  INTO ph
  FROM products;
  SELECT Avg(prod_price)
  INTO pa
  FROM products;
END;

• 執(zhí)行存儲過程

CALL productpricing(@pricelow, @pricehigh, @priceaverage);

• 刪除存儲過程

DROP PROCEDURE productpricing;

9.游標(biāo)

游標(biāo)可以對檢索出來的行進行批處理，主要用于交互式應(yīng)用竖共，比如用戶需要滾動屏幕上的數(shù)據(jù)公给，對數(shù)據(jù)進行瀏覽或更改。游標(biāo)在Mysql中只能用于存儲過程肺然。

• 創(chuàng)建游標(biāo)

CREATE PROCEDURE processorders()
BEGIN 
  DECLARE ordernumbers CURSOR
  FOR
  SELECT order_num FROM orders;
END;

• 打開和關(guān)閉游標(biāo)

1. OPEN ordernumbers; #打開游標(biāo)
2. CLOSE ordernumbers; #關(guān)閉游標(biāo)

• 使用游標(biāo)數(shù)據(jù)

CREATE PROCEDURE processorders()
BEGIN 
    -- Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8, 2);

    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    -- Create a table to store the results;
    CREATE TABLE IF NOT EXISTS ordertotals
        (order_num INT, total DECIMAL(8, 2));

    -- Open the cursor
    OPEN ordernumbers;

    -- Loop through all rows
    REPEAT
        FETCH ordernumbers INTO o;
        CALL ordertotal(o, 1, t);
        INSERT INTO ordertotals(order_num, total) 
        VALUES(o, t);
        UNTIL done END REPEAT;

    -- Close the cursor
    CLOSE ordernumbers;

END;

10.觸發(fā)器

當(dāng)想要某條語句在事件發(fā)生時自動執(zhí)行捂龄，可以使用觸發(fā)器倦沧。
創(chuàng)建觸發(fā)器時展融，需要給出4條信息：

1.唯一的觸發(fā)器名;
2.觸發(fā)器關(guān)聯(lián)的表;
3.觸發(fā)器應(yīng)該響應(yīng)的活動（DELETE告希、INSERT或UPDATE）;
4.觸發(fā)器何時執(zhí)行（處理之前或之后）燕偶；

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

使用語句 DROP TRIGGER newproduct; 刪除觸發(fā)器指么。

11.事物處理

事物處理可以用來維護數(shù)據(jù)庫的完整性伯诬，它保證成批的MySQL操作要么完全執(zhí)行盗似，要么完全不執(zhí)行平项。

• 使用commit

在事物塊處理中闽瓢，提交不會隱含的進行鸳粉，為了明確的提交届谈，使用COMMIT語句：

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

• 使用rollback

MySQL的ROLLBACK命令用來回退MySQL語句：

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

• 使用保留點

ROLLBACK回退整個事物，對復(fù)雜的事物可能需要部分回退湖雹，這個時候需要在事物塊中放置占位符摔吏，以便回退到這個占位符征讲，也稱為保留點橡娄。

SAVEPOINT delete1;    #創(chuàng)建保留點
ROLLBACK TO delete1;    #回退到保留點

12.數(shù)據(jù)庫維護

• 備份數(shù)據(jù)

1.使用命令行實用程序mysqldump轉(zhuǎn)儲所有數(shù)據(jù)庫內(nèi)容到某個外部文件挽唉；
2.使用命令行實用程序mysqlhotcopy從一個數(shù)據(jù)庫賦值所有數(shù)據(jù)（并非所有數(shù)據(jù)庫引擎都支持這個使用程序）瓶籽；
3.使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE轉(zhuǎn)儲所有數(shù)據(jù)到某個外部文件塑顺。

• 查看日志文件

MySQL維護管理員依賴的一系列日志文件主要有以下幾種：

13.改善性能

1. MySQL具有特定的硬件建議竣蹦，關(guān)鍵的生產(chǎn)DBMS應(yīng)該運行在自己的專用服務(wù)器上沧奴。
2. MySQL使用一系列的默認設(shè)置預(yù)先配置的，這些設(shè)置開始通常是很好的挠日。但過一段時間后你可能需要調(diào)整內(nèi)存分配嚣潜、緩沖區(qū)大小等懂算。
3. MySQL是一個多用戶多線程的DBMS庇麦，它經(jīng)常執(zhí)行多個任務(wù)女器。如果這些任務(wù)中的某一個執(zhí)行緩慢驾胆，則所有請求都會執(zhí)行緩慢丧诺。如果遇到顯著的性能不良，可使用 SHOW PROCESSLIST 顯示所有活動進程抗愁，用KILL命令終止某個特定的進程蜘腌。
4. 總有不止一種方法編寫同一條SELECT語句饵隙，應(yīng)該試驗聯(lián)結(jié)金矛、并、子查詢等娶耍，找出最佳的方法榕酒。
5. 使用EXPLAIN語句讓MySQL解釋它將如何執(zhí)行一條SELECT語句。
6. 一般來說澜掩，存儲過程執(zhí)行的比一條一條的執(zhí)行其中的各條MySQL語句塊。
7. 絕不要檢索比需求還要多的數(shù)據(jù)惩妇。
8. 必須索引數(shù)據(jù)庫表以改善數(shù)據(jù)檢索的性能歌殃。
9. 你的SELECT語句有一系列復(fù)雜的OR條件嗎氓皱？通過使用多條SELECT語句和連接他們的UNION語句波材，你能看到極大的性能改善身隐。
10. 索引改善數(shù)據(jù)檢索的性能隙轻，但損害數(shù)據(jù)插入垢揩、刪除和更新的性能叁巨。
11. LIKE很慢秤标，一遍來說最好是使用FULLTEXT而不是LIKE苍姜。
12. 位于http://dev.mysql.com/doc/的MySQL文檔有許多提示和技巧（甚至有用戶提供的評論和反饋）悬包，一定要查看這些有價值的資料。

ENDING!