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

MySql主鍵規(guī)則

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

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

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

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

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

• 為了按多個列排序,只要指定列名,列名之間用逗號分開即可殴泰。

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

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

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

• 在where子句中使用圓括號任何時候使用具有AND和OR操作符的WHERE子句,都應該使用圓括號明確的分組操作符走触。不要過分依賴默認計算次序,即使它確實是你想要的東西也是如此。

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

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

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

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

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

• 通配符搜索的處理一般要比前面討論的其他搜索所花的時間更長

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

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

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

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

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

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

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

• select也可以用來進行計算
  SELECT 3*2 將返回6

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

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

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

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

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

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

Mysql中的聚集函數(shù)

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

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

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

MySql中的分組函數(shù)

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

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

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

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

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

過濾分組

對分組進行過濾使用 HAVING

• HAVING支持所有的WHERE操作符

group by不能給分組的數(shù)據(jù)進行排序,所以必須是用 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
    )

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

作為計算字段使用子查詢

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)鍵字會從左表 (table_name1) 那里返回所有的行围橡，即使在右表 (table_name2) 中沒有匹配的行。
顯示左邊所有的行,右邊沒有的顯示null

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

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

組合查詢

適用場景

• 在單個查詢中從不同的表返回類似的結(jié)構(gòu)
• 對單個表執(zhí)行多個查詢,按單個查詢返回數(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)理'

注意事項:

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

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

union查詢結(jié)果集中自動去掉重復的行
如果想取得所有的行則用 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 語句層次的封裝，可用來簡化數(shù)據(jù)的處理以及重新格式化數(shù)據(jù)或保護基礎(chǔ)數(shù)據(jù)

存儲過程

就是為了以后的使用而保存的一條或多條MySql語句的集合晾咪∈詹粒可將其視為批文件,雖然他們的作用不僅限于批處理

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

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

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

• 刪除存儲過程
  DROP PROCEDURE productpricing;

• 變量
  內(nèi)存中一個特定的位置,用來存儲臨時數(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 指出相應的參數(shù)用來從存儲過程傳出一個值(返回給調(diào)用者).
MySql支持 IN(傳遞給存儲過程)谍倦、OUT(對存儲過程傳入和傳出)類型的參數(shù)

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

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

為了顯示值

 select @priceaverage;

下面這個例子使用 IN和OUT參數(shù)塞赂。
ordertotal接受訂單號并返回該訂單的合計

 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，因為訂單號被傳入存儲過程昼蛀。otatal定位為OUT,因為要從存儲過程返回合計宴猾。SELECT 語句使用這兩個參數(shù),WHERE子句使用onumber選擇正確的行,INTO使用ototal存儲計算出來的合計

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

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

CALL ordertotal(20009,@total)
SELECT @total

游標

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

關(guān)于游標的注意事項:

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

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

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

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

創(chuàng)建游標

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

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

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

隱含關(guān)閉 如果不明確關(guān)閉游標,MySql將會在到達END語句時自動關(guān)閉它

例子:

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

觸發(fā)器

應用場景: 如果你想要某條語句(或某些語句)在事件發(fā)生時自動執(zhí)行
在每個表發(fā)生更改時自動處理

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

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

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

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

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

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

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

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

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

事務管理

• 保留點: 指事務處理中設(shè)置的臨時占位符

例子:

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

使用保留點
SAVEPOINT delete1;

更改默認的提交行為
SET commint 0;

管理用戶

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

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

重命名一個表

RENAME USER ben TO bforta;

刪除一個表

DROP USER bforta

授予權(quán)限

GRANT SELECT ON crashcourse.* TO beforta;

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

取消用戶權(quán)限

REVOKE SELECT ON crashcourse.* FRO M beforta

兩個命令在幾個層次上的控制

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

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

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

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

查看日志文件

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

改善性能

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

• 查看當前所有線程ID和活動時間 SHOW PROCESS LIST

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

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