一茅逮、關(guān)于MySQL
MySQL（Structured Query Language）是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)臼寄，在數(shù)據(jù)庫管理系統(tǒng)中“表（table）”表示某種特定類型數(shù)據(jù)的結(jié)構(gòu)化清單，模式（schema）是關(guān)于數(shù)據(jù)庫和表的布局及特性的信息，列（column）為表中的一個字段描焰，數(shù)據(jù)類型（datatype） 為所容許的數(shù)據(jù)的類型咐容，主鍵（primary key）是表中每一行可以唯一標識自己的一列或一組列。
- 注意：在選擇主鍵時庄撮，不要在主鍵列中使用可能會更改的值背捌。
二、檢索數(shù)據(jù)
1.檢索單個列
輸入：SELECT prod_name
FROM products;
- 注意：多條SQL語句必須以分號(;)分隔洞斯，單條也可以使用毡庆；SQL語句不區(qū)分大小寫。
2.檢索多個列
輸入：SELECT prod_id, prod_name, prod_price
FROM products;
- 注意：選擇多列時烙如，要在列名之間加上逗號么抗，但最后一個列名不加
3.檢索所有列
輸入：SELECT *
FROM products;
4.檢索不同的行
輸入：SELECT DISTINCT vend_id
FROM products;
- 注意：DISTINCT關(guān)鍵字應用于所有列而不僅是前置它的列，不能部分使用DISTINCT
5.限制結(jié)果
輸入：SELECT prod_name
FROM products
LIMIT 5; (不多于5行)
輸入：SELECT prod_name
FROM products
LIMIT 5亚铁，5;（返回從行5開始的5行）
- 注意：檢索出來的第一行為行0而不是行1蝇刀，因此，LIMT 1徘溢，1 將檢索出第二行而不是第一行吞琐；如果沒有足夠行仰迁，則返回能返回的那么多行；LIMT 2顽分，3=LIMT 2 OFFSET 3
三徐许、排序檢索數(shù)據(jù)
1.排序數(shù)據(jù)
輸入：SELECT prod_name
FROM products
ORDER BY prod_name;
2.按多個列排序
輸入：SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price,prod_name
3.指定排序方向
輸入：SELECT prod_id,prod_price,prod_nam
FROM products
ORDER BY prod_price DESC;
如果是用多個列排序，第一個列用降序卒蘸，第二個列用升序
輸入：SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC,prod_name;
- 如果想在多個列上進行降序排序雌隅，必須對每個列制定DESC關(guān)鍵字（升序為默認，ASC缸沃，ascending）
四恰起、過濾數(shù)據(jù)
1.使用where子句
輸入：SELECT prod_name,prod_price
FROM products
WHERE prod_price = 2.50;
WHERE 子句操作符
- 注意：WHERE子句中，如果將值與串類型的列進行比較趾牧，則需要限定引號检盼。用來與數(shù)值列進行比較的值不用引號
2.范圍值檢查
輸入：SELECT prod_name,prod_price
FROM products
WHERE pro_price BETWEEN 5 AND 10;
- 注意：數(shù)據(jù)匹配過濾或不匹配過濾時，不返回具有NULL的行翘单。因此吨枉，在過濾數(shù)據(jù)時，需要驗證返回數(shù)據(jù)中確實給出了被過濾列具有NULL的行哄芜。
3.AND操作符
輸入：SELECT prod_id,prod_price,prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;
4.OR操作符
輸入：SELECT prod_id,prod_price,prod_name
FROM products
WHERE vend_id = 1003 OR prod_price <= 10;
- 在計算次序中的優(yōu)先級：（）> AND > OR
5.IN操作符（指定條件范圍）
輸入：SELECT prod_name,prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;
- IN可以包含其他SELECT語句
6.NOT操作符（WHERE子句中的NOT只能用來否定它之后所跟的任何條件）
輸入：SELECT prod_name,prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;
- MySQL僅支持使用NOT對IN貌亭、BETWEEN和EXISTS取反
五、用通配符進行過濾（通配符：用來匹配值的一部分的特殊字符）
1.LIKE操作符
LIKE指示MySQL认臊，后跟的搜索模式利用通配符匹配而
不是直接相等匹配進行比較
2.百分號（%）通配符（%表示任何字符出現(xiàn)任意次數(shù)）
輸入：SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE ‘ jet%’;
-
通配符可在搜索模式中任意位置使用圃庭，并且可以使用多個通配符
輸入：SELECT prod_id,prod_name
FROM products WHERE prod_name LIKE ‘%anvil%’;
- %可以匹配0個字符，但不能匹配NULL值
3.下劃線（_）通配符（用途與%一樣失晴，但下劃線只匹配單個字符而不是多個字符）
輸入：SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE ‘_ ton anvil’;
- 通配符搜索的處理比其他搜索花的時間更長剧腻，所以不要把通配符用在搜索模式的開始處
六、用正則表達式過濾數(shù)據(jù)（正則表達式是用來匹配文本的特殊的串（字符集合））
1.基本字符匹配
輸入：SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘1000’
ORDER BY prod_name;
正則表達式中.000表示匹配任意一個字符
LIKE與REGEXP的區(qū)別：LIKE匹配整個列涂屁，如果被匹配的文本在列值中出現(xiàn)书在，LIKE將不會找到它，相應的行也不會被返回(除非使用通配符)胯陋；而REGEXP在列值內(nèi)進行匹配蕊温，如果被匹配的文本在列值中出現(xiàn)，REGEXP將會找到它遏乔，相應的行將被返回
MySQL不區(qū)分大小寫义矛，若想?yún)^(qū)分大小寫，可使用BINARY關(guān)鍵字：WHERE prod_name REGEXP BINARY ‘JetPack .000'
2.進行OR匹配
輸入：SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘1000 | 2000’
ORDER BY prod_name;
- | 為正則表達式的OR操作符
3.匹配幾個字符之一
輸入：SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘[123] Ton’
ORDER BY prod_name;
[123]定義一組字符盟萨，表示1或2或3凉翻；[ ]是另一種形式的OR語句
要否定一個字符集，在集合的開始處放置一個捻激，[123]制轰，否定（1或2或3）
-
[1-9]表示范圍1-9前计，.匹配任意字符，若要匹配特殊字符須用\為前導垃杖，\-表示查找-男杈，\\表示查找\；正則表達式內(nèi)具有特殊意義的所有字符都必須以這種方式轉(zhuǎn)義调俘。另外伶棒，\也用來引用元字符
- 通過正則表達式重復字符可以對匹配數(shù)目進行更強的控制
輸入：SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘\\([0-9] sticks?\\)’
ORDER BY prod_name;
分析：正則表達式\\([0-9] sticks?\\)，\\(匹配(彩库，[0-9]匹配任
意數(shù)字肤无，sticks?匹配stick和sticks（s后的？使s可選骇钦，因
為宛渐？匹配它前面的任何字符的0次或1次出現(xiàn))，\\)匹配）
輸入：SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘[[:dight:]]{4}’
ORDER BY prod_name;
分析：[:dight:]匹配任意數(shù)字眯搭，{4}確切地要求它前面的字符出現(xiàn)四次窥翩，所以[[:dight:]]{4}匹配連在一起的任意4位數(shù)字
4.定位符
輸入：SELECT prod_name
FROM products
WHERE prod_name REGEXP’^[0-9\\.]’
ORDER BY prod_name
- LIKE和REGEXP的不同在于，：LIKE匹配整個串而REGEXP匹配子串坦仍，利用定位符鳍烁，通過用^開始每個表達式叨襟，用$結(jié)束每個表達式繁扎，可以使REGEXP的作用與LIKE一樣
七、創(chuàng)建計算字段
1.拼接字段
拼接（concatenate糊闽，將值聯(lián)結(jié)到一起構(gòu)成單個值）梳玫，這
個拼接的字段還需要用括號將vend_country括起來
SELECT Concat (vend_name,’(‘,vend_country,’)’)
FROM vendors
ORDER BY vend_name;
-
多數(shù)DBMS使用+或||來實現(xiàn)拼接，MySQL則使用Concat()函數(shù)來實現(xiàn)右犹，把SQL語句轉(zhuǎn)換成MySQL語句時要記住這個區(qū)別
輸入:SELECT Concat(RTrim(vend_name),’(‘,RTrim(vend_country),’)’)
FROM vendors ORDER BY vend_name; RTrim( )去掉串右邊的空格提澎，LTrim()去掉串左邊的空格，Trim()去掉串左右兩邊的空格
2.別名
別名（alias）是一個字段或值的替換名念链，別名用AS關(guān)鍵字賦予
輸入：SELECT Concat(vend_name),’(‘,RTrim(vend_country),’)’) AS
vend_title
FROM vendors
ORDER BY vend_name
3.執(zhí)行計算
輸入：SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 2005;
八盼忌、數(shù)據(jù)處理函數(shù)
1.文本處理函數(shù)
輸入：SELECT vend_name,Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;
- Upper()是將文本轉(zhuǎn)換為大寫
輸入：SELECT cust_name,cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex(‘Y Lie’);
- WHERE子句使用Soundex()函數(shù)來轉(zhuǎn)換cust_contact列值和搜索串為它們的SOUNDEX值，SOUNDEX是一個將任何文本串轉(zhuǎn)換為描述其語音表示的字母數(shù)字模式的算法掂墓，也考慮了類似的發(fā)音音符和音節(jié)谦纱。
2.日期和時間處理函數(shù)
輸入：SELECT cust_id,order_num
FROM orders
WHERE order_date = ‘2005-09-01’;
3.數(shù)值處理函數(shù)
九、匯總數(shù)據(jù)（匯總數(shù)據(jù)用到聚集函數(shù)君编，運行在行組上跨嘉，計算和返回單個值的函數(shù)）
1.AVG()函數(shù)
輸入：SELECT AVG(prod_price) AS avg_price
FROM products;
2.COUNT()函數(shù)
輸入：SELECT COUNT(*) AS num_cust
FROM customers;
- 如果制定列名，則制定列的值為空的行被COUNT()函數(shù)斛律吃嘿，但如果COUNT()函數(shù)中用*祠乃，則不忽略
3.MAX()函數(shù)
輸入：SELECT MAX(prod_price) AS max_price
FROM products梦重；
- 用于文本數(shù)據(jù)時，如果數(shù)據(jù)按相應的列排列亮瓷，則MAX()返回最后一行
4.MIN()函數(shù)
MAX() MIN()函數(shù)都忽略為NULL的行
5.SUM()函數(shù)
輸入：SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 2005;
輸入：SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 2005
- SUM()函數(shù)忽略列值為NULL的行
十琴拧、分組數(shù)據(jù)
-
創(chuàng)建分組
輸入：SELECT vend_id COUNT(*) AS num_prods FROM products GROUP BY vend_id;  -
過濾分組
輸入：SELECT cust_id, count(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2; 
- 大部分類型的WHERE子句都可以用HAVING子句來替代，唯一的區(qū)別在于WHERE過濾行嘱支，而HAVING過濾分組艾蓝。WHERE在數(shù)據(jù)分組前進行過濾，HAVING在數(shù)據(jù)分組后進行過濾
- 分組和排序
- SELECT子句順序
十一斗塘、使用子查詢
-
子查詢過濾
輸入：SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2’);
在SELECT語句中赢织，子查詢總是從內(nèi)向外處理
-
在WHERE子句中使用子查詢，應保證SELECT語句具有與WHERE子句中相同數(shù)目的列
輸入：SELECT cust_name馍盟， cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM orders ORDER BY cust_name; 
十二于置、聯(lián)結(jié)表（聯(lián)結(jié)分為：等值聯(lián)結(jié)、自聯(lián)結(jié)贞岭、自然聯(lián)結(jié)和外部聯(lián)結(jié)）
外鍵（foreign key）八毯，外鍵為某個表中的一列，它包含另一個表的主鍵值瞄桨，定義了兩個表之間的關(guān)系话速。
笛卡爾積，由沒有聯(lián)結(jié)條件的表關(guān)系返回的結(jié)果為笛卡爾積芯侥。
-
創(chuàng)建等值聯(lián)結(jié)（內(nèi)部聯(lián)結(jié)）
輸入：SELECT vendor_name,prod_name,prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name,prod_name;  -
使用表別名
輸入：SELECT cust_name,cust_contact FROM customers AS c,orders AS o,orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = ’TNT2’; -
自聯(lián)結(jié)
假如你發(fā)現(xiàn)某物品（ID為DTNTR）存在問題泊交，因此想知道生產(chǎn)該物品的供應商生產(chǎn) 的其他物品是否也存在這些問題。此查詢要求首先找到ID為DTNTR的物品的供應商柱查， 然后找到這個供應商生產(chǎn)的其他物品廓俭。 輸入：SELECT prod_id,prod_name FROM products WHERE vend_id=(SELECT vend_id FROM products WHERE prod_id = ‘DTNTR’); 
使用自聯(lián)結(jié)進行相同的查詢：
輸入：SELECT p1.prod_id,p1.prod_name
FROM products AS p1,product AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = ‘DTNTR’;
-
自然聯(lián)結(jié)
無論何時對表進行聯(lián)結(jié)，至少有一個列出現(xiàn)在不止一個表中唉工，標準的聯(lián)結(jié)返回所有的數(shù)據(jù)研乒，甚至相同的列多次出現(xiàn)。自然聯(lián)結(jié)排除多次出現(xiàn)淋硝，使每個列都只返回一次雹熬。自然聯(lián)結(jié)是這樣一種聯(lián)結(jié)， 你只能選擇那些唯一的列谣膳，這一般是通過對表使用通配符（SELECT*）竿报，對其他表的列使用明確的子集來完成的。  -
外部聯(lián)結(jié)
聯(lián)結(jié)包含了那些在相關(guān)表中沒有關(guān)聯(lián)行的行参歹，這種類型的聯(lián)結(jié)稱為外部聯(lián)結(jié) 輸入：SELECT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id; 
LEFT OUTER JOIN一個滿足條件的行與另一個表的所有行進行匹配關(guān)聯(lián)
on是關(guān)聯(lián)條件仰楚，where是查詢條件
-
帶聚集函數(shù)的聯(lián)結(jié)
輸入：SELECT customers.cust_name, customers .cust_id, COUNT(orders.order_num) AS num_order FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;  
十三、組合查詢
輸入：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);
相同的查詢使用WHERE語句時：
輸入：SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
OR vend_id IN(1001,1002)
十四、全文本搜索
-
全文本搜索
輸入：SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘rabbit’)  Match()指定被搜索的列 Against()指定要使用的搜索表達式 -
布爾文本搜索
輸入：SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘heavy -rope* IN BOOLEAN MODE); 
十五僧界、插入數(shù)據(jù)
-
插入行
SELECT INTO Customers VALUES（NULL侨嘀， ‘Pep E. LaPew’, ‘100 Main Street’, ‘Los Angeles’, ‘CA’, ‘90046’ NULL, NULL); 更為安全的寫法  -
插入多行
   插入檢索出的數(shù)據(jù)
十六、更新和刪除數(shù)據(jù)
-
更新數(shù)據(jù)
輸入：UPDATE customers SET cust_email = ‘[element@163.com](mailto:element@163.com)’ WHERE cust_id = 1005; -
刪除數(shù)據(jù)
輸入：DELETE FROM customers WHERE cust_id = 10006;
十七捂襟、創(chuàng)建和操縱表
-
創(chuàng)建表
每個表只允許一個Auto_INCREMENT
可以指定默認值
- 各種引擎（外鍵不能跨引擎）
InnoDB咬腕，事務處理引擎，不支持全文本搜索葬荷；
MEMORY在功能等同于MyISAM涨共，但由于數(shù)據(jù)儲存在內(nèi)存（不是硬盤）中，速度很快宠漩，適用臨時表
MyISAM举反，高性能引擎，支持全文本搜索扒吁，不支持事務處理
-
更新表
輸入：ALTER TABLE vendors ADD vend_phone CHAR(20); 輸入：ALTER TABLE Vendors DROP COLUM vend_phone; 輸入：RENAME TABLE customers TO customers;
十八火鼻、使用視圖
視圖是虛擬的表，與包含數(shù)據(jù)的表不一樣雕崩，視圖只包含使用時動態(tài)檢索數(shù)據(jù)的查詢
-
創(chuàng)建視圖
輸入：CREATE VIEW productcustomers AS SELECT cust_name,cust_contact,prod_id FROM customers, orders,orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num; 檢索視圖的語句與檢索表格相同 -
重用視圖
普通聯(lián)結(jié)查詢：  創(chuàng)建重用視圖：  檢索視圖： 
十九魁索、使用存儲
存儲過程簡單來說，就是為以后的使用而保存起來的一條或多條MySQL語句的集合
變量盼铁，內(nèi)存中一個特定的位置粗蔚，用來臨時存儲數(shù)據(jù)
-
執(zhí)行存儲過程
輸入：CALL productpricing(@pricelow, @pricehigh, @priceaverage);
- 所有MySQL變量都必須以@開始
-
創(chuàng)建存儲過程
輸入：CREATE PROCEDURE productpricing() BEGIN SELECT Avg(prod_price0 AS priceaverage FROM products; END; -
刪除存儲過程
輸入：DROP PROCEDURE productpricing;
- 刪除存儲過程，沒有使用后面的()
-
使用參數(shù)
-
記錄集是不允許的類型饶火，不能通過一個參數(shù)返回多個行和列
輸入：CREATE PROCEDURE ordertotal( IN onumber INT, OUT ototal DECIMAL(8,2) ) BEGIN SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO ototal; END ; 存儲的完整過程如下：  
-
檢查存儲過程
輸入：SHOW CREATE PROCEDURE ordertotal;
二十鹏控、使用游標
-
創(chuàng)建游標
輸入：CREATE PROCEDURE processorders() BEGIN DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; END;
- 使用游標前，必須聲明它趁窃，這個過程沒有檢索數(shù)據(jù)牧挣，只是定義要使用的SELECT語句
-
打開、關(guān)閉游標
輸入：OPEN ordernumbers; 輸入：CLOSE ordernumbers;
- 如果不明確關(guān)閉游標醒陆，MySQL將會在達到END語句時自動關(guān)閉它
-
使用游標數(shù)據(jù)
在游標被打開后，可以使用FETCH語句分別訪問每一行 裆针，F(xiàn)ETCH指定檢索什么數(shù)據(jù)刨摩，檢索出來的數(shù)據(jù)存儲在什么地方
