• 數(shù)據(jù)庫(kù)就是以一個(gè)以某種有組織的方式存儲(chǔ)的數(shù)據(jù)集合

MySql主鍵規(guī)則

• 不更新主鍵的值
• 不重用主鍵列的值
• 不在主鍵列中使用可能會(huì)更改的值

• SHOW TABLES; 獲得一個(gè)數(shù)據(jù)庫(kù)內(nèi)的表的列表

• 如果使用 DISTINCT 關(guān)鍵字,它必須直接放在列名的前面

• LIMIT 5 表示MySql返回不多于5行

• LIMIT 5,5 指示MySql返回從行5開始的5行
  第一個(gè)數(shù)為開始位置,第二個(gè)數(shù)為要檢索的行數(shù)

• 為了按多個(gè)列排序,只要指定列名,列名之間用逗號(hào)分開即可。

• 在多個(gè)列上進(jìn)行降序 如果想在多個(gè)列上進(jìn)行降序,必須對(duì)每個(gè)列指定DESC關(guān)鍵字

• OR 操作符指示MySql 檢索匹配任一條件的行

• SQL像多數(shù)語(yǔ)言一樣在處理OR操作符前,優(yōu)先處理AND操作符

• 在where子句中使用圓括號(hào)任何時(shí)候使用具有AND和OR操作符的WHERE子句,都應(yīng)該使用圓括號(hào)明確的分組操作符。不要過分依賴默認(rèn)計(jì)算次序,即使它確實(shí)是你想要的東西也是如此。

• in的操作符一般比OR操作符更快

• in 的最大優(yōu)點(diǎn)是可以包含其他的SELECT語(yǔ)句,使得能夠更動(dòng)態(tài)的建立WHERE字句

• IN WHERE 字句用來指定要匹配值的清單的關(guān)鍵字,功能與OR相當(dāng)

• 模糊查詢 %anvil%表示匹配任何位置包含文本anvil的值,而不論它之前或之后出現(xiàn)什么字符。

• _ 下劃線只匹配單個(gè)字符而不是多個(gè)字符

• 通配符搜索的處理一般要比前面討論的其他搜索所花的時(shí)間更長(zhǎng)

• 在確實(shí)需要使用通配符時(shí),除非絕對(duì)有必要,否則不要把它們用在搜索模式的開始處。把通配符置于搜索模式的開始處,搜索起來最慢

• REGEXP 正則表達(dá)式
  SELECT from products WHERE prod_name REGEXP '.000' ORDER BY prod_name
  匹配任意一個(gè)含義000的字符

• LIKE 與 REGEXP區(qū)別
  LIKE匹配整個(gè)列.如果被匹配的文本在列值中出現(xiàn),LIKE將不會(huì)找到它,相應(yīng)的行也不會(huì)被返回（除非使用通配符）。而REGEXP在列值內(nèi)進(jìn)行匹配,如果被匹配的文本在列值中出現(xiàn)，REGEXP將會(huì)找到它,相應(yīng)的行將被返回

• MySql正則表達(dá)式不區(qū)分大小寫（3.23.4）,為了區(qū)分大小寫可使用BINARY關(guān)鍵字

• 正則表達(dá)式進(jìn)行 OR匹配
  select prod_name from where prod_name REGEXP '1000|2000' ORDER BY prod_name
  表示匹配其中之一,因此1000和2000都匹配返回

• [123] 表示匹配 1或者2或者3,這是or的升級(jí)版
  [^123]匹配除含有 1或者2或者3的

-拼接: 將值聯(lián)接到一起構(gòu)成單個(gè)值
select repairshop_name, CONCAT(repairshop_name,repairshop_code), repairshop_code from t_ops_insuredorder;
將兩個(gè)字段的值連接在一起

• select也可以用來進(jìn)行計(jì)算
  SELECT 3*2 將返回6

關(guān)于MySql中的函數(shù)

如果你決定使用函數(shù),應(yīng)該保證做好代碼注釋,以便以后你（或其他人）能確切地知道編寫SQL代碼的含義
MySql中實(shí)現(xiàn)支持一下函數(shù)

• 1. 使用處理文本串（如刪除或填充值,轉(zhuǎn)換值為大寫或者小寫）的文本函數(shù)
• 2. 用于在數(shù)值數(shù)據(jù)上進(jìn)行算術(shù)操作（如返回絕對(duì)值,進(jìn)行代數(shù)）的數(shù)值函數(shù)

• 3. 用于處理日期和時(shí)間值并從這些值中提取特定成分（例如,返回兩個(gè)日期之差,檢查日期有效性）的日期時(shí)間函數(shù)
     DATE_FORMAt(date,format)時(shí)間按格式轉(zhuǎn)化函數(shù)

SELECT created_at,repairshop_name from t_ops_insuredorder where DATE(created_at) = '2016-12-19'
針對(duì)日期處理查詢條件函數(shù)

• 4. 返回DBMS正使用的特殊信息（如返回用戶登錄信息,檢查版本細(xì)節(jié)）的系統(tǒng)函數(shù)

Mysql中的聚集函數(shù)

運(yùn)行在組上,計(jì)算和返回單個(gè)值的函數(shù)
AVG() 返回某列的平均值
COUNT() 返回某列的行數(shù)

• 使用 count(*)對(duì)表中行的數(shù)目進(jìn)行計(jì)數(shù),不管表中包含的是空值(null)還是非空值
• 使用COUNT(column)對(duì)特定列中具有值的行進(jìn)行計(jì)算,忽略NULL值

MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

MySql中的分組函數(shù)

分組允許把數(shù)據(jù)分為對(duì)個(gè)邏輯組,以便能對(duì)每個(gè)組進(jìn)行計(jì)算

• GROUP BY 子句可以包含任意數(shù)目的列。這使得能對(duì)分組進(jìn)行嵌套,為數(shù)據(jù)分組提供更詳細(xì)的控制

• gruop by子句中列出的每個(gè)列必須是檢索列或有效的表達(dá)式(但是不能是聚集函數(shù)),如果在SELECT中使用表達(dá)式,則必須在GROUP BY子句中給出

• 如果分組中有NULL值,則 NULL將作為一個(gè)分組返回。如果列中有多行NULL值,則將他們分為一組

• gruop by 必須出現(xiàn)在 where子句后,order by子句之前

過濾分組

對(duì)分組進(jìn)行過濾使用 HAVING

• HAVING支持所有的WHERE操作符

group by不能給分組的數(shù)據(jù)進(jìn)行排序,所以必須是用 order by

SELECT order_num,SUM(quantity*item_price)
AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50

SELECT子句順序

子查詢

SELECT
    *
FROM
    vg_user_detail
WHERE
    user_id IN (
        SELECT
            COUNT(user_id) user_id
        FROM
            vg_user_info
        GROUP BY
            tenant_id
        ORDER BY
            user_id
    )

使用子查詢時(shí)候必須保證SELECT語(yǔ)句具有與WHERE子句中相同數(shù)目的列

作為計(jì)算字段使用子查詢

SELECT department_id,
(SELECT COUNT(user_id) 
FROM vg_user_info
WHERE vg_user_detail.user_id = vg_user_info.user_id
) userId 
from vg_user_detail
GROUP BY department_id
HAVING department_id > 0

注意 在子查詢中from使用了完全限定列名避免歧義

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

兩種寫法: 關(guān)于等值聯(lián)結(jié)

• 1 .

SELECT vi.user_id,vi.user_name,vd.staff_name
from vg_user_info vi,vg_user_detail vd
where vi.user_id = vd.user_id

• 2 .

SELECT vf.user_id,vf.user_name,vd.staff_name
from vg_user_info vf JOIN vg_user_detail vd
ON vf.user_id = vd.user_id

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

SELECT vf.user_id,vf.user_name,vd.staff_name
from vg_user_info vf INNER JOIN vg_user_detail vd
on vf.user_id = vd.user_id 

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

左聯(lián)結(jié)

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

LEFT JOIN 關(guān)鍵字會(huì)從左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中沒有匹配的行。
顯示左邊所有的行,右邊沒有的顯示null

有聯(lián)結(jié)

RIGHT JOIN
顯示右邊所有的行,左邊沒有的顯示null

組合查詢

適用場(chǎng)景

• 在單個(gè)查詢中從不同的表返回類似的結(jié)構(gòu)
• 對(duì)單個(gè)表執(zhí)行多個(gè)查詢,按單個(gè)查詢返回?cái)?shù)據(jù)

SELECT position_name,user_id
from vg_user_info vf where tenant_id = -1
UNION 
SELECT position_name,user_id
from vg_user_info where position_name = '人力資源經(jīng)理'

注意事項(xiàng):

• union中的每個(gè)查詢必須包含相同的列、表達(dá)式或聚合函數(shù)(每個(gè)列不需要以相同的次序出現(xiàn))

• 列數(shù)據(jù)類型必須完全兼容

union查詢結(jié)果集中自動(dòng)去掉重復(fù)的行
如果想取得所有的行則用 UNION ALL

**全文本索引只在 MyISAM數(shù)據(jù)引擎中使用

SELECT *
from vg_user_info vf 
WHERE MATCH(user_id) AGAINST ("abc")

match匹配列, AGSINST搜索含有abc的列

視圖

• 創(chuàng)建視圖 view
  CREATE VIEW 視圖名 AS 子查詢虛表

**視圖為虛擬的表. 它們包含的不是數(shù)據(jù)而是根據(jù)需要檢索數(shù)據(jù)的查詢。視圖提供了一種MySql的SELECT 語(yǔ)句層次的封裝，可用來簡(jiǎn)化數(shù)據(jù)的處理以及重新格式化數(shù)據(jù)或保護(hù)基礎(chǔ)數(shù)據(jù)

存儲(chǔ)過程

就是為了以后的使用而保存的一條或多條MySql語(yǔ)句的集合?？蓪⑵湟暈榕募?雖然他們的作用不僅限于批處理

• 執(zhí)行存儲(chǔ)過程
  MySql 執(zhí)行存儲(chǔ)過程稱為調(diào)用,使用 CALL命令

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

• 創(chuàng)建存儲(chǔ)過程
  CREATE PROCEDURE productpricing() BEGIN SELECT Avg(prod_price) AS price average FROM products; END;

• 刪除存儲(chǔ)過程
  DROP PROCEDURE productpricing;

• 變量
  內(nèi)存中一個(gè)特定的位置,用來存儲(chǔ)臨時(shí)數(shù)據(jù)。

  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 Mac(prod_price)
  from products
  SELECT Avg(prod_price)
  INTO pa
  from products;
 END

關(guān)鍵字 OUT 指出相應(yīng)的參數(shù)用來從存儲(chǔ)過程傳出一個(gè)值(返回給調(diào)用者).
MySql支持 IN(傳遞給存儲(chǔ)過程)、OUT(對(duì)存儲(chǔ)過程傳入和傳出)類型的參數(shù)

為調(diào)用此存儲(chǔ)過程必須使用3個(gè)變量

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

為了顯示值

 select @priceaverage;

下面這個(gè)例子使用 IN和OUT參數(shù)。
ordertotal接受訂單號(hào)并返回該訂單的合計(jì)

 CREATE PROCEDURE ordertotal(
     IN onnumber INT,
     OUT otatal DECIMAL(8,2)
                            )
 BEGIN
     SELECT Sum(item_price*quantity)
     FROM orderitems
     WHERE order_num = onnumber
     INTO ototal;
     END;

分析:
onnumber定義為IN，因?yàn)橛唵翁?hào)被傳入存儲(chǔ)過程。otatal定位為OUT,因?yàn)橐獜拇鎯?chǔ)過程返回合計(jì)。SELECT 語(yǔ)句使用這兩個(gè)參數(shù),WHERE子句使用onumber選擇正確的行,INTO使用ototal存儲(chǔ)計(jì)算出來的合計(jì)

為調(diào)用這個(gè)新存儲(chǔ)過程,可以使用一下語(yǔ)句
CALL ordertotal (20005,@total)
必須給ordertotal傳遞兩個(gè)參數(shù):第一個(gè)參數(shù)為訂單號(hào)，第二個(gè)參數(shù)為包含計(jì)算出來合計(jì)的變量名。
為了顯示次合計(jì)
select @total

為了得到一個(gè)訂單的合計(jì)顯示,需要再次調(diào)用存儲(chǔ)過程,然后重新顯示變量。

CALL ordertotal(20009,@total)
SELECT @total

游標(biāo)

應(yīng)用場(chǎng)景：
有時(shí)需要在檢索出來的行中前進(jìn)或后退一行或多行。
游標(biāo)主要用于交互式應(yīng)用,其中用戶需要滾動(dòng)屏幕上的數(shù)據(jù),并對(duì)數(shù)據(jù)進(jìn)行瀏覽或作出更改
游標(biāo)只能用于存儲(chǔ)過程(和函數(shù))

關(guān)于游標(biāo)的注意事項(xiàng):

• 在使用游標(biāo)前,必須先聲明（定義）它。
  這個(gè)過程實(shí)際上沒有檢索數(shù)據(jù),它只是定義要使用的SELECT 語(yǔ)句

• 一旦聲明后,必須打開游標(biāo)以供使用。這個(gè)過程用前面定義的SELECT 語(yǔ)句把數(shù)據(jù)檢索出來

• 對(duì)于填有數(shù)據(jù)的游標(biāo),根據(jù)需要去除（檢索）各行

• 在結(jié)束游標(biāo)使用時(shí),必須關(guān)閉游標(biāo)

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

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

打開或關(guān)閉游標(biāo)
游標(biāo)使用 OPEN CURSOR語(yǔ)句來打開

• 打開:
  OPEN ordernumbers;
• 關(guān)閉
  CLOSE ordernumbers;

隱含關(guān)閉 如果不明確關(guān)閉游標(biāo),MySql將會(huì)在到達(dá)END語(yǔ)句時(shí)自動(dòng)關(guān)閉它

例子:

CREATE procedure processorders()
BEGIN
   -- 定義一個(gè)游標(biāo)
   DECLARE ordernumbers CURSOR
   FOR 
   SELECT irder_num FROM orders;
   
   --打開游標(biāo)
   OPEN ordernumbers
   
   --檢索游標(biāo)
   FETCH ordernumbers INTO o;
   
   --關(guān)閉游標(biāo)
   CLOSE ordernumbers;
   
   END;

觸發(fā)器

應(yīng)用場(chǎng)景: 如果你想要某條語(yǔ)句(或某些語(yǔ)句)在事件發(fā)生時(shí)自動(dòng)執(zhí)行
在每個(gè)表發(fā)生更改時(shí)自動(dòng)處理

• DELETE
• UPDATE
• INSERT
  操作可使用觸發(fā)器

創(chuàng)建觸發(fā)器需要提供的信息:

• 唯一的觸發(fā)器名;
• 觸發(fā)器關(guān)聯(lián)的表
• 觸發(fā)器應(yīng)該響應(yīng)的活動(dòng)(DELETE、INSERT或UPDATE);
• 觸發(fā)器何時(shí)執(zhí)行

保持每個(gè)觸發(fā)器的名稱在每個(gè)表中的唯一

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

創(chuàng)建一個(gè)名為 newproduct的觸發(fā)器 在INSERT語(yǔ)句成功執(zhí)行后執(zhí)行。這個(gè)觸發(fā)器還指定 FOR EACH ROW 因此代碼對(duì)每個(gè)差入行執(zhí)行

觸發(fā)器僅僅支持表 且每個(gè)表最多支持6個(gè)觸發(fā)器

CREATE TRIGGER neworder AFTER INSERT ON
orders
FOR EACH ROW SELECT NEW.order_num;

生成一個(gè)neworder觸發(fā)器,再插入一個(gè)新的訂單并保存到order表時(shí),MySql生成一個(gè)新的訂單號(hào)并保存到order_num取得這個(gè)值并返回它

事務(wù)管理

• 保留點(diǎn): 指事務(wù)處理中設(shè)置的臨時(shí)占位符

例子:

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

使用保留點(diǎn)
SAVEPOINT delete1;

更改默認(rèn)的提交行為
SET commint 0;

管理用戶

• MySql中有一張 user表 用戶管理所有的用戶

CREATE USER ben IDENTIFIED BY 'p@$$wOrd';

重命名一個(gè)表

RENAME USER ben TO bforta;

刪除一個(gè)表

DROP USER bforta

授予權(quán)限

GRANT SELECT ON crashcourse.* TO beforta;

表示: 用戶bforta對(duì)crashcourse數(shù)據(jù)庫(kù)中的所有數(shù)據(jù)具有只讀訪問權(quán)限

取消用戶權(quán)限

REVOKE SELECT ON crashcourse.* FRO M beforta

兩個(gè)命令在幾個(gè)層次上的控制

• 整個(gè)服務(wù)器,使用GRANT ALL和 REVOKE ALL;
• 整個(gè)數(shù)據(jù)庫(kù),使用ON database;
• 特定的表,使用ON database.table
• 特定的列, 特定的存儲(chǔ)過程。

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

數(shù)據(jù)庫(kù)維護(hù)

• ANALYZE TABLE orders;
• CHECK TABLE 快速發(fā)現(xiàn)和修復(fù)問題

查看日志文件

• 錯(cuò)誤日志: data目錄下 hostname.err
• 查詢?nèi)罩荆篽ostname.log
• 二進(jìn)制日志：hostname-bin
• 緩慢查詢?nèi)罩? hostname-slow.log

改善性能

• 查看當(dāng)前設(shè)置 SHOW VARIABLES
  SHOW STATUS

• 查看當(dāng)前所有線程ID和活動(dòng)時(shí)間 SHOW PROCESS LIST

• 你的SELECT語(yǔ)句中有一系列復(fù)雜的OR條件嗎？通過使用多條SELECT 語(yǔ)句和連接它們的UNION語(yǔ)句

• LIKE很慢,一般來說,最好使用FULLTEXT(全文索引)