MySQL學習小結
這一篇最主要是記錄下命令，方便以後查找
使用Mysql
創建資料庫
create database mysql_test;
展示所有資料庫
show databases;
選擇要使用的資料庫
use Typeidea_db
獲得表tables
show tables;
顯示伺服器狀信息
show status;
更多show命令查詢
help show
檢索數據
- select + 列名 from + 表
- selcet、from 都為關鍵字
- 使用select語句從products表中檢索一個名為prod_name的列
SELECT prod_name FROM products;
檢索多個多個列
select prodname, prodid, prod_price from products;
檢索所有列
select * from products;
檢索不同的行
- DISTINCT關鍵字 「不同」- 告訴MySQL只返回不同的vend_id行selcet DISTINCT vend_id from products;
檢索限制結果
- limit- limit 5,5:第一個5開始位置，第二個5要檢索的行數;- limit 4 OFFSET 3 :從行3開始取4行- 返回不多於5行
select prod_name from products LIMIT 5;
select prod_name from products LIMIT 5,5;
排序檢索數據
- order by :排序
- 以字母順序排序數據（A-Z）> select prodname from products ORDER BY prodname;
按多個列排序
select prodname, prodid, prodprice from products ORDER BY prodprice, prod_name;
指定排序方向
- DESC 降序排序
- Z-A
- 對產品價格進行排序
select prodid, prodprice, prodname from products ORDER BY prodprice DESC;
- 降序排序產品價格，再對產品名字排序> select prodid, prodprice, prodname from products ORDER BY prodprice DESC, prod_name;
使用order by 和limit 找出列中最高或最低的值
- 去掉DESC就是最低值- limit 1 ：僅返回一行- 使用order by 和limit子句時，limit子句必須在order by 子句之後
select prodprice from products ORDER BY prodprice DESC LIMIT 1;
過濾數據
where子句
- 從products表中檢索兩個列， 過濾（只返回prod_price = 2.5的行）
- 使用order by 和 where子句時，order by 必須在where之後> select prodname, prodprice from products WHERE prod_price = 2.50;
檢查單個值
where字句操作符
- =
- <
- >
- !=
- <=
- >=
- BETWEEN ：在指定的兩個值之間
檢查where prodname = fuses，返回prodname 的值為fuses的一行
- 值為字元串時需要使用單引號
select prodname, prodprice from products WHERE prod_name = fuses;
列出小於等於10美金的所有產品
select prodprice, prodname from products WHERE prod_price <= 10;
不匹配檢查
列出不是由供應商1003製造的所有產品
select vendid, prodname from products WHERE vend_id != 1003;
範圍值檢查
查詢價格在5美金至10美金之間的所有產品
- ADN ：兩個值之間必須要使用AND來連接- BETWEENselect prodname, prodprice from products WHERE prod_price BETWEEN 5 AND 10;
空值檢查
NULL：空值select prodname from products WHERE prodprice IS NULL;
文件中沒有某位顧客的電子郵件
select custid from customers WHERE custemail IS NULL;
組合WHERE子句
AND操作符檢索供應商1003製造且價格小於等於10美金的所有產品的價格名稱、價格、IDselect prodid, prodprice, prodname from products WHERE vendid = 1003 AND prod_price <=10;
OR操作符
- 指示MySQL檢索匹配任一條件的行- 檢索任一個指定供應商製造的所有產品的名稱和價格select prodname, prodprice from products WHERE vendid = 1002 OR vendid = 1003;
計算次序
例子：檢索出10美金以上且由1002或者1003製造的所有產品select prodname, prodprice from products WHERE vendid = 1003 OR vendid = 1002 AND prod_price >= 10;
- sql語句理解為：檢索1003供應商製造的所有產品或者1002供應商製造的大於10美金的所有產品
- and 在計算次序中優先順序更高
- 這裡應該使用圓括弧
select prodname, prodprice from products WHERE(vendid = 1002 or vendid = 1003) AND prod_price >= 10;
IN 操作符
IN - 用來指定範圍檢索由1002或1003供應商提供的所有產品的名稱和價格，名稱以升序排序
select prodname, prodprice from products WHERE vendid IN (1002, 1003) ORDER BY prodname;
NOT操作符
- 用來否定它之後的所跟的任何條件
- 匹配除開1002和1003外所有的供應商的產品名字和價格，排序以名稱升序排序
select prodname, prodprice from products WHERE vendid NOT IN (1002, 1003) ORDER BY prodname;
用通配符進行過濾
- LIKE操作符: 匹配未知值
- %通配符：表示任何字元出現任意次數
- 通配符無法匹配以null值作為產品名的行
查找以jet開頭的產品
select prodid, prodname from products WHERE prod_name LIKE jet%;
搜索模式『%anvil%』表示匹配任何位置包含文本anvil的值，無論它之前或之後出現什麼字元
找到以s起頭e結尾的所有產品
select prodname from products WHERE prodname LIKE s%e;
下劃線（_）通配符
- 至匹配單個字元 - 下劃線後有個空格 - .5 ton anvil沒有被匹配，因為搜索模式要求匹配兩個匹配符而不是一個」.一個「 」5一個「select prodid, prodname from products WHERE prodname LIKE ton anvil;
### 用正則表達式進行搜索
- REGEXP- LIKE :如果匹配值在列中被找到將不會返回數據，like匹配未知值檢索prod_name文本1000的所有行
select prodname from products WHERE prodname REGEXP 1000 ORDER BY prod_name;
匹配所有.000
select prodname from products WHERE prodname REGEXP .000 ORDER BY prod_name;
OR匹配
- 使用 | :為正則表達式中的OR操作，表示匹配其中之一> select prodname from products WHERE prodname REGEXP 1000|2000 ORDER BY prod_name;
匹配幾個字元之一
[123] Ton
- [123]定義一組字元，匹配1或2或3select prodname from products WHERE prodname REGEXP [123] Ton ORDER BY prod_name;
或者這樣寫
select prodname from products WHERE prodname REGEXP [1|2|3] Ton ORDER BY prod_name;
匹配範圍
- 用 - 來匹配- [1-5]- [a-z]select prodname from products WHERE prodname REGEXP [1-5] Ton ORDER BY prod_name;
匹配特殊字元
- 必須使用』『 - . 轉義> select vendname from vendors WHERE vendname REGEXP . ORDER BY vend_name;
匹配多個實例
select prodname from products WHERE prodname REGEXP ([0-9]) sticks?) ORDER BY prod_name;
- ()
- [0-9]；匹配任意數字
select prodname from products WHERE prodname REGEXP [[:digit:]]{4} ORDER BY prod_name;
- [:digit:]；任意數字集合
- {4}：任意4位數
定位符
- 在集合中[^.....]表示否定該集合或者指定字元的開始處
創建計算欄位
拼接欄位
- concate()函數來實現拼接
名字和地址拼接起來
select Concat(vendname, (, vendcountry, )) from vendors ORDER BY vend_name;
- 刪除右側多餘空格
- Concat()
- RTrim()函數
select Concat(RTrim(vendname), (, RTrim(vendcountry), )) from vendors ORDER BY vend_name;
使用別名
- alias 別名 ASselect Concat(RTrim(vendname), (, RTrim(vendcountry), )) AS vendtitle from vendors ORDER BY vendname;
執行算術計算
select prodid, quantity, itemprice from orderitems WHERE order_num = 2005;
使用數據處理函數
文本處理函數
select vendname, Upper(vendname) AS vendnameupcase from vendors ORDER BY vend_name;
- AS(alias) 使用別名
- Upper()函數：將文本轉換為大寫
select custname, custcontact from customers WHERE Soundex(cust_contact) = Soundex(Y Lie);
- Soundex 檢索所有發音類似『Y Lie』的聯繫名（cust_contact）
日期和時間處理函數
select custid, ordernum from orders WHERE order_date = 2005-09-01;
如果要的是日期，就使用Date()
select custid, ordernum from orders WHERE Date(order_date) = 2005-09-01;
檢索2005年9月份下的所有訂單
1.select custid, ordernum from orders WHERE Date(order_date) BETWEEN 2005-09-01 AND 2005-09-30;
- > select custid, ordernum from orders WHERE Year(orderdate) = 2005 AND Month(orderdate) = 9;
匯總數據
AVG（）函數
- AVG（）函數：返回某列的平均值- AVG（）函數忽略列值為NULL的行- AVG（）函數只能確定特定數值列的平均值使用AVG函數f返回products表中所有產品的平均價格
select AVG(prodprice) AS avgprice from products;
返回特定供應商所提供產品的平均價格
- 使用where來過濾出vend_id = 1003的產品
- AS 給prodprice取別名> select AVG(prodprice) AS avgprice from products WHERE vendid = 1003;
COUNT（）函數
- COUNT(*)對表中數目進行計數，不管表列中包含的是空值還是非空值 - COUNT(column)對特定列中具有值的行進行計數，忽略NULL值統計customers表中所有的行，不管各列有什麼值
select COUNT(*) AS num_cust from customers;
只對具有電子郵箱地址的客戶計數
select COUNT(custemail) AS numcust from customers;
MAX函數
- 返回指定列中的最大值- 要求指定列名檢索products表中最貴物品的價格
select MAX(prodprice) AS maxprice from products;
MIN函數
- 返回指定列的最小值
檢索products表中最便宜i物品的價格
select MIN(prodprice) AS minprice from products;
SUM函數
- 返回指定列值的和
檢索所訂購物品的總數
- SUM(quantity):返回訂單中所有物品數量之和
- WHERE子句保證只統計某個物品訂單中的物品
select SUM(quantity) AS itemsordered from orderitems WHERE ordernum = 20005;
檢索20005訂單中所有物品價格之和
- SUM(item_price*quantity)select SUM(itemprice*quantity) AS totalprice from orderitems WHERE order_num = 20005;
DISTINCT
使用distinct後，平均值值考慮各個不同的價格
select AVG(DISTINCT prodprice) AS avgprice from products WHERE vend_id = 1003;
組合聚集函數
返回4個值- 物品的所有數目- 最低價格- 最高價格- 平均價格select COUNT(*) AS numitems,
-> MIN(prodprice) AS pricemin, -> MAX(prodprice) AS pricemax, -> AVG(prodprice) AS price_avg from products;
分組數據
- GROUP BY 子句
- HAVING 子句
創建分組
- vendid；包含供應商的ID- AS 使用別名- GROUP BY vend-id 對vendid進行分組select vendid, COUNT(*) AS numprods from products GROUP BY vend_id;
ROLLUP
- 可以得到每個分組以及每個分組匯總級別（針對每個分組）的值
- null 值 也算了進去> select vendid, COUNT(*) AS numprods from products GROUP BY vend_id WITH ROLLUP;
過濾分組
- having ：過濾分組
- where ：過濾行
過濾那些COUNT()2的那些分組
select custid, COUNT(*) AS orders from orders GROUP BY custid HAVING COUNT(*) >=2;
同時使用WHERE和HAVING子句
- 價格大於等於10- vend_id分組- 過濾COUNT(*)>=2分組select vendid, COUNT(*) AS numprods from products WHERE prodprice >= 10 GROUP BY vendid HAVING COUNT(*) >= 2;
分組和排序
檢索訂總計訂單價格大於等於50的訂單的訂單號和總計訂單的價格
SELECT ordernum, SUM(quantity*itemprice) AS ordertotal FROM orderitems GROUP BY ordernum HAVING SUM(quantity*itemprice) >= 50;
按總計訂單價格排序輸出，需要添加ORDER BY子句
SELECT ordernum, SUM(quantity*itemprice) AS ordertotal FROM orderitems GROUP BY ordernum HAVING SUM(quantity*itemprice) >= 50 ORDER BY ordertotal;
使用子查詢
需要列出訂購物品TNT2的所有客戶，應該怎樣檢索？
1. 檢索包含物品TNT的所有訂單的編號 2. 檢索具有前一步聚列出的訂單編號的所有客戶的ID 3. 檢索前一步聚返回的所有客戶ID的客戶信息對於prodid 為TNT2的所有訂單物品，從orderitems表中檢索其ordernum列
SELECT ordernum FROM orderitems WHERE prodid = TNT2;
查詢具有訂單20005和20007的客戶ID
- IN 範圍設定
SELECT custid FROM orders WHERE ordernum IN (20005, 20007);
把第一步的查詢變為子查詢，組合兩個查詢
mysql實際執行了兩個操作- 執行查詢：SELECT ordernum FROM orderitems WHERE prodid = TNT2; 返回20005和20007兩個訂單號- IN操作，外部查詢變成；SELECT custid FROM orders WHERE ordernum IN (20005, 20007);SELECT custid FROM orders WHERE ordernum IN (SELECT ordernum FROM orderitems WHERE prodid = TNT2);
檢索客戶ID信息
SELECT custname, custcontact FROM customers WHERE cust_id IN (10001, 10004);
轉換為子查詢
SELECT custname, custcontact FROM customers WHERE custid IN (SELECT custid FROM orders WHERE ordernum IN (SELECT ordernum FROM orderitems WHERE prod_id = TNT2));
作為計算欄位使用子查詢
對每個客戶執行COUNT()計算，應該將COUNT()作為一個子查詢
SELECT custname, custstate, (SELECT COUNT(*) FROM orders WHERE orders.custid = customers.custid) AS orders FROM customers ORDER BY cust_name;
聯結表
- 這裡有兩張表， vendors和products，這就是聯結的兩個表的名字
- 用WHERE子句正確聯結，過濾條件
- WHERE子句指示MYSQL匹配vendors表中的vendid和products表中的vendid
SELECT vendname, prodname, prodprice
-> FROM vendors, products -> WHERE vendors.vendid = products.vendid -> ORDER BY vendname, prod_name;
笛卡兒積
- 由沒有聯結條件的表關係返回的結果為笛卡兒積- 檢索出的行的數目將是第一個表中的行數乘以第二個表中的行數內部聯結
- from子句的的組成由INNER JOIN指定- 聯結條件使用ON，而不是WHERESELECT vendname, prodname, prodprice
-> FROM vendors INNER JOIN products -> ON vendors.vendid = products.vend_id;
聯結多個表
顯示編號為20005訂單中的物品
SELECT prodname, vendname, prodprice, quantity
-> FROM orderitems, products, vendors -> WHERE products.vendid = vendors.vendid -> AND orderitems.prodid = products.prodid -> AND ordernum = 20005;
創建高級聯結
使用表別名
- concat :拼接欄位- RTrim :刪除右側多餘空格- AS使用別名
SELECT Concat(RTrim(vendname), (, RTrim(vendcountry), )) AS
-> vendtitle -> FROM vendors -> ORDER BY vendname;
使用不同類型的聯結
- 自聯結- 自然聯結- 外部聯結自聯結
- 兩張相同的表products- 使用別名p1,p2- 聯結表- 按p2過濾數據SELECT p1.prodid, p1.prodname
-> FROM products AS p1, products AS p2 -> WHERE p1.vendid = p2.vendid -> AND p2.prod_id = DTNTR;
自然聯結
SELECT c.*, o.ordernum, o.orderdate, oi.prodid, oi.quantity, oi.itemprice
-> FROM customers AS c, orders AS o, orderitems AS oi -> WHERE c.custid = o.custid -> AND oi.ordernum = o.ordernum -> AND prod_id = FB;
外部聯結
聯結包含了那些在相關表中沒有關聯行的行
- OUTER JOIN: 指定聯結類型- 必須使用RIGHT或者LEFT關鍵字指定包括其所有行的表- RIGHT :OUTER JOIN右邊的表- LEFT：OUTER JOIN左邊的表- 從from子句的左邊表(customers)中選擇所有行
SELECT customers.custid, orders.ordernum
-> FROM customers LEFT OUTER JOIN orders -> ON customers.custid = orders.custid;
使用帶聚集函數的聯結
檢索所有客戶及每個客戶所下的訂單數，使用COUNT（）函數
- INNER JOIN將customers和orders兩張表關聯起來
- GROUP BY 按客戶分組數據
- COUNT(orders.order_num)對每個客戶的訂單技術
SELECT customers.custname, customers.custid,
-> COUNT(orders.ordernum) AS numord -> FROM customers INNER JOIN orders -> ON customers.custid = orders.custid -> GROUP BY customers.cust_id;
組合查詢
創建組合查詢
需要價格小於等於5的所有物品的一個列表，而且包括供應商1002和1001生產的所有物品
- union 操作符
SELECT vendid, prodid, prodprice FROM products WHERE prodprice <= 5
-> UNION -> SELECT vendid, prodid, prodprice FROM products WHERE vendid IN (1001, 1002);
包含或取消重複的行
使用union all不取消重複行
SELECT vendid, prodid, prodprice
-> FROM products -> WHERE prodprice <= 5 -> UNION ALL -> SELECT vendid, prodid, prodprice -> FROM products -> WHERE vendid IN (1001, 1002);
對組合查詢結果排序
- ORDER BY 不允許使用多條子句
- 組合查詢中，雖然ORDER BY放在最後一條語句中，但實際上它可以排序所有SELECT語句
SELECT vendid, prodid, prodprice
-> FROM products -> WHERE prodprice <= 5 -> UNION -> SELECT vendid, prodid, prodprice -> FROM products -> WHERE vendid IN (1001, 1002) -> ORDER BY vendid, prodprice;
全文本搜索
Match()指定被搜索的列
Against()指定要使用的搜索表達式- Match(notetext)： 對notetext列進行搜索
- Against(rabbit)： 指定詞rabbit作為搜索文本
SELECT notetext
-> FROM productnotes -> WHERE Match(notetext) Against(rabbit);
實用查詢擴展
- with query expansionSELECT notetext
-> FROM productnotes -> WHERE Match(notetext) Against(anvils WITH QUERY EXPANSION);
布爾文本搜索
- IN BOOLEAN MODE
SELECT notetext FROM productnotes WHERE Match(notetext) Against(heavy IN BOOLEAN MODE);
匹配包含heavy但不包含以rope開始的行
- -排除一個詞
- *截斷操作符
SELECT notetext FROM productnotes WHERE Match(notetext) Against(heavy -rope* IN BOOLEAN MODE);
插入數據
插入完整的行
- 存儲到每個列表中的值在VALUESINSERT INTO customers
-> VALUES( -> NULL, -> Pep E. LaPew, -> 100 Main Street, -> Los Angeles, -> CA, -> 90046, -> USA, -> NULL, -> NULL); Query OK, 1 row affected (0.01 sec)
編寫更加安全的INSERT語句
- 插入行時，用VALUES列表中的相應值填入列表中的對應項> INSERT INTO customers(custname,-> custaddress,-> custcity,-> custstate.-> custzip,-> custcountry,-> custcontact,-> custemail)-> VALUES(Pep E. La Pew,-> 100 Main Street,-> Los Angeles,-> CA,-> 90046,-> USA,-> NULL,-> NULL);
插入多個行
- 列名相同的情況下INSERT INTO CUSTOMERS(custname,
-> custaddress, -> custcity, -> custstate, -> custzip, -> custcountry) -> VALUES( -> treehl, -> baoshan, -> shanghai, -> sh, -> 0000, -> cn -> ), -> ( -> yang, -> baoshan, -> shanghai, -> sh, -> 1111, -> cn -> );
插入檢索出的數據
INSERT SELECT
INSERT INTO customers (custid,
.............. ) SELECT custid, ............... FROM ...;
更新和刪除數據
更新數據
UODATE
- 更新表中所有行- 更新表中特定行更新表customers
set命令用來將新值賦給被更新的列，set子句設置cust_email為指定的列WHERE子句：告訴MySQL更新哪一行UPDATE customers
-> SET custemail = elmer@fudd.com -> WHERE custid = 10005;
使用NULL來刪除某個列的值
UPDATE customers SET custemail = NULL WHERE custid = 10005;
刪除數據
DELETE
- 從表中刪除特定的行- 從表中刪除所有行- 不需要列名或通配符- 刪除整行而不是刪除列DELETE FROM - 指定從哪個表刪除
WHERE- 子句過濾要刪除的行從customers表中只刪除id為10006的
DELETE FROM customers WHERE cust_id = 10006;
創建和操作表
表創建基礎
NOT NULL - 阻止插入沒有值的列
CREATE TABLE customers
-> ( -> custid int NOT NULL AUTOINCREMENT, -> custname char(50)NOT NULL, -> custaddress char(50)NOT NULL, -> custcity char(50)NULL, -> custstate char(5) NULL, -> custzip char(10)NULL, -> custcountry char(50)NULL, -> custcontact char(50)NULL, -> custemail char(255)NULL, -> PRIMARY KEY (cust_id) -> )ENGINE=InnoDB
使用NULL值
主鍵
定義主鍵例子
PRIMARY KEY(vend_id)
由多個列組成的主鍵
CREATE TABLE orderitems
-> ( -> ordernum int NOT NULL, -> orderitem int NOT NULL, -> prodid char(10) NOT NULL, -> quantity int NOT NULL, -> itemprice decimal(8, 2) NOT NULL, -> PRIMARY KEY (ordernum, orderitem) -> )ENGINE=InnoDB;
AUTOINCREMENT
autoincrement- 自動增量- auto_increment告訴MySQL，本列每當增加一行時自動增量，執行一個INSERT操作，自動對該列增量- 每個表只允許一個AUTO_INCREMENT
custid int NOT NULL AUTOINCREMENT
指定默認值
- default ；表示在沒有給出數據時，默認就是1CREATE TABLE orderitems
-> ( -> ordernum int NOT NULL, -> orderitem int NOT NULL, -> prodid char(10) NOT NULL, -> quantity int NOT NULL DEFAULT 1, -> itemprice decimal(8, 2) NOT NULL, -> PRIMARY KEY (ordernum, orderitem) -> )ENGINE=InnoDB;
引擎類型
- InnoDB 一個可靠的事務處理引擎，它不支持全文本搜索- MyISAM 一個性能極高的引擎，它支持全文本搜索，但不支持事務處理- MEMORY 功能等同於MyISAM，但由於數據存儲在內存中，速度很快（適合臨時表）ENGINE=InnoDB
更新表
- ALTER TABLE + 表名
給vendors表增加vend_phone列，必須明確其數據char(20)
ALTER TABLE vendors ADD vend_phone CHAR(20);
刪除剛剛添加的列
- DROP COLUMN
ALTER TABLE vendors DROP COLUMN vend_phone;
刪除表
假設有這個表DROP TABLE customers2;
重命名表
RENAME TABLE customers2 TO customers;
重命名多個表
RENAME TABLE backupcustomers TO customers,
-> backupvendors TO vendors, -> backup_products TO products;
使用視圖
- 用CREATE VIEW來創建
- 使用SHOW CREATE VIEW viewname 來查看創建視圖的語句
- 用DROP刪除視圖
使用視圖簡化複雜的聯結
創建了一個名為productcustomers的視圖，它聯結三個表
CREATE VIEW productcustomers AS
-> SELECT custname, custcontact, prodid -> FROM customers, orders, orderitems -> WHERE customers.custid = orders.custid -> AND orderitems.ordernum = orders.order_num;
檢索訂購了產品TNT2的客戶
- 它將指定的WHERE子句添加到試圖查詢中的已有的WHERE子句中，以便正確過濾數據SELECT custname, custcontact FROM productcustomers WHERE prod_id = TNT2;
用視圖重新格式化檢索出的數據
創建視圖
CREATE VIEW vendorlocations AS
-> SELECT Concat(RTrim(vendname), (, RTrim(vendcountry), )) AS vendtitle -> FROM vendors -> ORDER BY vendname;
檢索出以創建所有郵件標籤的數據
SELECT * FROM vendorlocations;
用視圖過濾不想要的數據
定義customeremaillist視圖，過濾沒有郵箱的客戶
CREATE VIEW customeremaillist AS
-> SELECT custid, custname, custemail -> FROM customers -> WHERE custemail IS NOT NULL;
檢索由郵箱的客戶
SELECT * FROM customeremaillist;
使用試圖與計算欄位
檢索某個特定訂單中的物品，計算每種物品的總價格
CREATE VIEW orderitemsexpanded AS
-> SELECT ordernum, prodid, quantity, itemprice, quantity*itemprice AS expanded_orice -> FROM orderitems;
檢索訂單20005的詳細內容
SELECT * FROM orderitemsexpanded WHERE order_num = 20005;
使用存儲過程
執行存儲過程
執行名為productpricing的存儲過程，它計算並返回產品的最低、最高和平均價格CALL productpricing(@pricelow,
@pricehigh, @priceaverage);
創建存儲過程
- 存儲過程名為productpricing()
- BEGIN END用來限定過程存儲體
- 這裡SELECT語句結束後用分號有可能會報錯
- DELIMITER // 分隔符
DELIMITER //
mysql> CREATE PROCEDURE productpricing() -> BEGIN -> SELECT Avg(prod_price) AS priceaverage -> FROM products; -> END // Query OK, 0 rows affected (0.00 sec)
使用存儲過程
- 使用分隔符（DELIMITER // 後需要再輸入 DELIMITER ;）CALL productpricing();
刪除存儲過程
DROP PROCEDURE productpricing;
使用參數
- decimal 十進位
- pl 存儲最低價
- ph 存儲最高價
- pa 存儲產品平均價
- OUT 指出相應的參數用來從存儲過程傳出一個值
- 存儲過程的代碼位於BEGIN和END之間
- 指定INTO保存到相應的變數
DELIMITER //
mysql> CREATE PROCEDURE productpricing( -> OUT pl DECIMAL(8,2), -> OUT ph DECIMAL(8,2), -> OUT pa DECIMAL(8,2) -> ) -> BEGIN -> SELECT Min(prodprice) -> INTO pl -> FROM products; -> SELECT Max(prodprice) -> INTO ph -> FROM products; -> SELECT Avg(prod_price) -> INTO pa -> FROM products; -> END //
為調用此修改過的存儲過程，必須指定三個變數名
- 所有MySQL變數名都必須以@開始
CALL productpricing(@pricelow,
-> @pricehigh, -> @priceaverage);
檢索出產品的平均價格
SELECT @priceaverage;
檢索平均價格，最高價格，最低價格
SELECT @pricehigh, @pricelow, @priceaverage;
IN 和OUT參數
- onumber定義為IN- ototal定義為OUT- where子句使用onumber選擇正確的行- INTO使用ototal存儲計算出來的合計ordertotal接受訂單並返回該訂單的合計
- onumber定義為IN
- ototal定義為OUT
- where子句使用onumber選擇正確的行
- INTO使用ototal存儲計算出來的合計
CREATE PROCEDURE ordertotal(
-> IN onumber INT, -> OUT ototal DECIMAL(8,2) -> ) -> BEGIN -> SELECT Sum(itemprice*quantity) -> FROM orderitems -> WHERE ordernum = onumber -> INTO ototal; -> END //
調用新存儲過程
- 第一個參數為訂單號- 第二個參數為包含計算出來的合計的變數名CALL ordertotal(20005, @total);
顯示合計
SELECT @total;
建立智能存儲過程
檢查存儲過程
- 獲得何時、由誰創建等詳細信息的存儲過程列表
SHOW CREATE PROCEDURE ordertotal;
使用游標
游標（cursor）：是一個存儲在MySQL伺服器上的資料庫查詢
創建游標
定義了ordernumbers的游標- DECLARE語句定義和命名游標ordernumbers
- 存儲過程處理完成後，游標就消失（因為它僅限於存儲過程）
CREATE PROCEDURE processorders()
-> BEGIN -> DECLARE ordernumbers CURSOR -> FOR -> SELECT order_num FROM orders; -> END //
打開或關閉游標
OPEN ordernumbers;
CLOSE ordernumbers;
使用游標數據
- FETCH語句分別訪問它的每一行
從游標中檢索單個行
CREATE PROCEDURE
使用觸發器
創建觸發器
CREATE TRIGGER - 建立觸發
CREATE TRIGGER newproduct AFTER INSERT ON products
-> FOR EACH ROW SELECT Product added;
刪除觸發器
DROP TRIGGER newproduct;
管理事務處理
- InnoDB引擎支持事務處理管理
- transaction 事務處理 指一種SQL語句
- rollback 回退 指撤銷指定SQL語句的過程
- commit 提交 指將未存儲的SQL語句結果寫入資料庫表
- savepoint 保留點 指事務處理中設置的臨時佔位符
- START TRANSACTION 標識事務的開始
使用ROLLBACK
SELECT * FROM ordertotals;
START TRANSACTION; DELETE FROM ordertotals; SELECT * FROM ordertotals; ROLLBACK; SELECT * FROM ordertotals;
- 執行一條select語句顯示該表不為空
- 開始食物處理
- 刪除表中所有的行
- select 驗證表確實為空
- 回退到START TRANSACTION之後的所有語句
- 最後一條select顯示該表不為空
使用COMMIT
使用保留點
SAVEPOINT delete1;
更改默認的提交行為
- autocommit 標誌決定是否自動提交更改SET autocommit=0;
全球化和本地化
使用字符集和校對順序
SHOW CHARACTER SET;
查看所支持校對的完整列表
SHOW COLLATION
給表指定字符集和校對
- 創建一個包含兩個列的表
- 指定一個字符集
- 制定一個校對順序
CREATE TABLE mytable
-> ( -> column1 INT, -> column2 VARCHAR(10) -> ) DEFAULT CHARACTER SET hebrew -> COLLATE hebrewgeneralci;
安全管理
獲得所有用戶賬號的列表
USE mysql
SELECT user FROM user;
創建用戶賬號
- identified 確定
CREATE USER treehl IDENTIFIED BY 密碼;
重新命名一個帳戶
RENAME USER treehl TO treehl1;
刪除用戶賬號
DROP USER treehl1;
設置訪問許可權
查詢用戶許可權SHOW GRANTS FOR treehl;
USAGE表示沒有許可權
用GRANT設置許可權
GRANT SELECT ON MySQL_test.* TO treehl;
顯示更改後的許可權
show grants for treehl;
撤銷許可權
REVOKE SELECT ON MySQL_test.* FROM treehl;
GRANT和REVOKE可在幾個層次上控制訪問許可權
- 整個伺服器，GRANT ALL REVOKE ALL
- 整個資料庫 使用ON databases.*
- 特定的表 使用ON database.table
更改口令
SET PASSWORD FOR treehl = Password(密碼);
資料庫維護
刷新數據
FLUSH TABLES;
進行資料庫維護
analyze table - 分析表
ANALYZE TABLE orders;
check table - 檢查表
CHECK TABLE ORDERS, ORDERITEMS;
爭端啟動問題
mysqld
改善性能
查看所有活動進程
SHOW PROCESSLIST;
查看當前設置
SHOW VARIABLES;
SHOW STATUS;
GitHub
歡迎訪問博客Treehl的博客推薦閱讀：
※高性能mysql優化一之配置篇
※MySQL時間序列存儲引擎的設計與實現
※利用主鍵快速聚合單表數千萬MySQL的非索引欄位
※InnoDB存儲引擎MVCC實現原理
※每次登錄mysql都報：無能通過 '/var/lib/mysql/mysql.sock' 登錄