title: mysql必知必會
date: 2019-11-21
tags:
- 筆記
categories:
- sql
複製程式碼第四章
4.6限制結果
limit 5,6; (從第下標為5 的行開始返回6條資料)
LIMIT 6 OFFSET 2; 返回6個從下表1開始複製程式碼第八章用萬用字元匹配
like操作符號
{% : N個}
｛ _ ：一個｝
第九章 正規表示式
例子：where name REGEXP ' 字串 '
9.2.1基本字串匹配
【 . 】可以匹配任何字串
匹配不區分大小寫MySQL中的正規表示式匹配（ 自版本 3.234後）不區分大小寫（即，大寫和小寫都匹配）。為區分大 小寫，可使用BINARY關鍵字，如WHERE prod_ name REGEXP BINARY ' JetPack . 000 '
LIKE和REGEXP之間有一個重要的差別。 LIKE匹配整個列。如果被匹配的文字在列值 中出現，LIKE將不會找到它，相應的行也不被返回（除非使 用萬用字元）。而REGEXP在列值內進行匹配，如果被匹配的文 本在列值中出現，REGEXP將會找到它，相應的行將被返回， 這是一個非常重要的差別。 REGEXT能不能用來匹配整個列值（從而起與LIKE相同的作用） ？答案是肯定的，使用^和$定位符（ anchor）即可， 本章後面介紹。
9.2.2 or匹配
為搜尋兩個串可以使用 | 來匹配字串。可以使用兩個以上 的 【 | 】符號。
SELECT prod_nameFROM productsWHERE prod_name REGEXP '1000|2000'ORDER BY prod_name複製程式碼+------------------+ | JetPack 1000| | JetPack 2000| +------------------+
9.2.3[]匹配幾個字元之一
[123456789] ==[1-9]
集合中的一個，用[ 123 ] 等價於1|2|3
SELECT prod_nameFROM productswhere prod_name REGEXP '1|2|3 Ton'ORDER BY prod_name複製程式碼輸出 1 ton anvil 2 ton anvil JetPack 1000 JetPack 2000 TNT （1 stick） 字符集合也可以被否定:匹配該集合意外的集合
例如：【^123】
9.2.5 \\匹配特殊字元
\\ ：兩個斜槓表示轉義
\\k 換頁\\n 換行\\r 回車\\t 製表\\v 縱向製表複製程式碼9.2.6[:digit:]匹配字元類
字元類[:alnum:] = [a-zA-Z1-9][:alpha:] = [a-zA-z][:blank:] = [\\t][:cntrl:] = asc110-31和127的控制符[:digit:] = [0-9][:graph:] = 同[:print:][:lower:] = 任意小寫字母[:punct:] = 既不在[:alnum:]又不在[:cntrl:]中的任意字元[:space:] 包括空格在內的任意空白字元（同[\\f\\n\\r\\t\\v]）[:upper:] 任意大寫字母（同[A-Z]）[:xdigit:] 任意十六進位制數字（同[a-fA-F0-9]）複製程式碼9.2.7 * + ？匹配多個例項
* : 0或多+ : 1或多（等於{1,}）? : 0個或1個匹配（等於{0,1}）{n} : 指定數目的匹配{n,} : 不少於指定數目的匹配{n,m} : 匹配數目的範圍（m不超過255）SELECT prod_nameFROM productsWHERE PROD_NAME REGEXP '[0-9]{4}' 等價 '[[:digit:]]{4}'ORDER BY prod_name複製程式碼9.2.8 ^￥定位符
^ 文字的開始$ 文字的結尾[[:<:]] 詞的開始[[:>:]] 詞的結尾複製程式碼第十章建立計算欄位
10.1計算欄位
中間欄位
10.2拼接欄位
CONCAT（） ：中間用逗號分隔開
RTrim()刪除右空白
LTrim()刪除左空白，
拼接欄位沒名字可用AS
SELECT CONCAT(vend_name,' ( ',RTRIM(VEND_COUNTRY) ,' ) ') AS 國家FROM VENDORSORDER BY VEND_NAME DESC複製程式碼10.3執行計算
計算欄位：
SELECT prod_ID,QUANTITY,ITEM_PRICE,QUANTITY*ITEM_PRICE AS EXPANDED_PRICEFROM ORDERITEMSWHERE ORDER_NUM = 20005複製程式碼QUANTITY * ITEM_PRICE AS EXPANDED_PRICE 是計算欄位
第十一章使用資料處理函式
11.2使用函式
11.2.1文字處理函式
Upper（）轉換大寫，是將查詢出的文字
Left() 返回串左邊的字元Length() 返回串的長度Locate() 找出串的一個子串Lower() 將串轉換為小寫LTrim() 去掉串左邊的空格Right() 返回串右邊的字元RTrim() 去掉串右邊的空格Soundex() 返回串的SOUNDEX值 找出發音相似的SubString() 返回子串的字元Upper() 將串轉換為大寫複製程式碼11.2.2日期和時間處理函式
AddDate() 增加一個日期（天、周等）AddTime() 增加一個時間（時、分等）CurDate() 返回當前日期CurTime() 返回當前時間Date() 返回日期時間的日期部分DateDiff() 計算兩個日期之差Date_Add() 高度靈活的日期運算函式Date_Format() 返回一個格式化的日期或時間串Day() 返回一個日期的天數部分DayOfWeek() 對於一個日期，返回對應的星期幾Now() 返回當前日期和時間Time() 返回一個日期時間的時間部分Year() 返回一個日期的年份部分Month() 返回一個日期的月份部分Hour() 返回一個時間的小時部分Minute() 返回一個時間的分鐘部分Second() 返回一個時間的秒部分複製程式碼SELECT cust_id,order_numFROM ordersWHERE Data（order_date）='2005-09-01'匹配欄位中的日期Time（）可以提取出日期複製程式碼11.2.3數值處理函式
Abs() 返回一個數的絕對值Cos() 返回一個角度的餘弦Exp() 返回一個數的指數值Mod() 返回除操作的餘數Pi() 返回圓周率Rand() 返回一個隨機數Sin() 返回一個角度的正弦Sqrt() 返回一個數的平方根Tan() 返回一個角度的正切複製程式碼第十二章彙總資料
12.1.1AVG（）函式
AVG（）求平均值函式也可返回特定行和列的特定值
12.1.2 count( )函式
count( *)/count( )計算行數，不分空值還是null都計算在內。
使用COUNT(column)對特定列中具有值的行進行計數，忽略
NULL值。
12.1.3 MAX（）函式
返回指定列中最大值。max（column）忽略列值為null的行。
在用於文字資料時，如果資料按相應的列排序，則MAX(column)返回最後一行。
12.2.4 min（）函式
12.2.5 SUM（）函式
SUM()函式忽略列值為NULL的行。
12.2 聚集不同值
DISTINCT（去重）
ALL的使用
note : 不指定DISTINCT就是ALL，
12.3 組合聚集函式
多個聚合函式並行使用。
第十三章分組資料
13.1資料分組
group by
SELECT vend_id , count(*) AS num_prodsfrom productsGROUP BY vend_id1001 31002 21003 71005 2複製程式碼13.2建立分組
GROUP BY子句可以包含任意數目的列。這使得能對分組進行巢狀， 為資料分組提供更細緻的控制。
如果在GROUP BY子句中巢狀了分組，資料將在最後規定的分組上 進行彙總。換句話說，在建立分組時，指定的所有列都一起計算 （所以不能從個別的列取回資料）。
GROUP BY子句中列出的每個列都必須是檢索列或有效的表示式 （但不能是聚集函式）。如果在SELECT中使用表示式，則必須在 GROUP BY子句中指定相同的表示式。不能使用別名。
除聚集計算語句外，SELECT語句中的每個列都必須在GROUP BY子 句中給出。
如果分組列中具有NULL值，則NULL將作為一個分組返回。如果列 中有多行NULL值，它們將分為一組。
GROUP BY子句必須出現在WHERE子句之後，ORDER BY子句之前。
13.3 過濾分組
where是過濾行
Having是過濾分組
note：WHERE在資料 分組前進行過濾，HAVING在資料分組後進行過濾。這是一個重要的區別
13.4 分組和排序
分組不保證輸出順序
13.5 select子句順序
| 子句 | 說明 | 是否必須 |
|---|---|---|
| SELECT | 要返回的列或者表示式 | |
| FROM | 從中檢索資料的表 | |
| WHERE | 行級過濾 | |
| GROUP BY | 分組 | |
| HAVING | 分組過濾 | |
| ORDER BY | 輸出排序 | |
| LIMIT | 要檢索的行 |
第十四章使用子查詢
14.2 利用子查詢 進行過濾
子查詢的主要作用就是過濾where in 組合查詢
14.3 作為計算欄位使用子查詢
表.類名=全限定名
第十五章聯結表
15.1聯結
用一條select語句查詢多張表
15.1.1關係表
主外來鍵約束
15.1.2為什麼要使用聯結
用一條select語句查詢多張表
15.2建立聯結
通過主外來鍵進行聯結，where能夠起到過濾的作用
交叉連線 = 笛卡爾積
cross join，交叉聯接返回左表中的所有行，左表中的每一行與右表中的所有行組合。交叉聯接也稱作笛卡爾積
內部連線 = 笛卡爾積
等值連線（交叉連線**）：主表 INNER JOIN 從表 on 條件（主外來鍵）在連線條件中使用等於號(=)運算子比較被連線列的列值，其查詢結果中列出被連線表中的所有列，包括其中的重複列。
不等值連線：在連線條件使用除等於運算子以外的其它比較運算子比較被連線的列的列值。這些運算子包括>、>=、<=、<、!>、!<和<>。
外連線
左外聯結：表1 left outer join 表2 on 表1.主鍵=表2.副鍵 右邊補空
右外聯結：表1 right outer join 表2 on 表1.主鍵=表2.副鍵 左邊補空
全外連線
MySQL目前不支援此種方式，可以用其他方式替代解決。
左邊的null會顯示，右邊的null也會顯示
15.2.3 聯結多個表-自然連線
select prod_name,vend_name,prod_price,quantity from orderitems,products,vendorswhere products.vend_id = vendors.vend_idand orderitems.prod_id = products.prod_id and order_num=20005複製程式碼第十六章 建立高階聯結
16.1使用表別名 mysql
關鍵字：AS
16.2使用不同型別的聯結
16.2.1自聯結
兩張表 用 on
select prod_id ,prod_namefrom productswhere vend_id = (select vend_id from products where prod_id = 'DNTTR')複製程式碼select p1.prod_id ,p1,prod_id from products as p1,products as p2where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR'複製程式碼16.2.2 自然聯結
三張表不用on
from 表A,表B,表C where 條件1 and 條件2 and 條件3 -- 自然連線複製程式碼16.2.3 外部聯結
left outer join right outer join複製程式碼16.3 使用帶聚集函式的聯結
檢索所有客戶以及客戶下單的總數
select customers.cust_name, customers.cust_id,count(orders.order_num) as num_ord from customers inner join orderson customers.cust_id = orders.cust_id group by customers.cust_id複製程式碼第十七章組合查詢
17.1UNION規則（並）
select語句用UNION關鍵字分隔；
查詢的列相同
列資料型別必須相容：型別不必完全相同，但必須是DBMS可以隱含地轉換的型別（例如，不同的數值型別或不同的日期型別）
全集：union all 取消去重
交集：intersect
差集：minus
17.2多組合結果排序
只能使用一條ORDER BY子句，且必須出現在最後一行
18.全文檢索
19.建立表
create table 表名 if not exists （
列名1 資料型別 not null auto_increment, 列名 2 資料型別 null 列名 3 資料型別 null
primary key(列名1)
）engine=innodb
19.1 使用null值
19.2 主鍵再介紹
19.3 使用auto_increment
19.4 使用預設值
19.5 更新表
alter table verdors add vend_phone char(20)//新增欄位alter table vendors drop vend_phone; //刪除欄位alter table orderitems add constraint fk_orderitems_orders foreign key (order_num)references orders (order_num)複製程式碼19.6 刪除表
19.7 重新命名錶
rename table customers2 to customers
20.插入資料
不安全：需插入完整資料，不寫資料的用null，按順序新增資料，mysql自增的也要制定為null
安全：需要指定列名然後面新增值，表結構改變也
可省略列：但是必須是允許為null值，或者設定預設值
插入多行資料：insert into (列名) values (),();
插入select的結果到表中：（只關心列的資料型別，不關心名字）
inset into (列名) select （列名）from 表
21.更新資料
更新要指定某一行，否則將更新所有
update 表名 set cust_email = '------------'
刪除：TRUNCATE實際是刪除原來的表並重新建立一個表，而不是逐行刪除表中的資料
更新和刪除的指導原則 ：。
一定加where條件
保證都有主鍵
先用select測試
22 檢視（子查詢的升級版）
檢視只包含使用時動態檢索資料的查詢，作為試圖它不包含任何列或資料，
22.1 為什麼使用檢視
重用複雜sql語句
使用表的組成部分而不是整個表
保護資料給表的部分資料授予許可權，而不是整張表的許可權
可以更改原表的資料型別
只可用於 查詢、過濾、排序、新增、更新資料（更新檢視將更新原表的資料）
效能：使用巢狀檢視可能影響效能
22.2 檢視的規則和限制
檢視名字唯一，可以巢狀，可以和表一起使用
需要相應的許可權
外層的order by 會把裡面的order by 覆蓋掉
檢視不能有索引和相關的觸發器
22.3 使用檢視
create view
show create view 名字
drop view 名字
create view productcustomers AS 複雜查詢語句複製程式碼建立檢視相當於某個中間表，進而免的頻繁建立表
檢視有以下情況不能更新
分組
聯結
子查詢
並 （union）
聚合函式
distinct
匯出列
23 使用儲存過程
建立過程
create procedure 名字（in 引數1，out 引數2）begin sql語句end複製程式碼執行儲存過程
call productpricing(@pricelow@pricehigh@priceaverage)複製程式碼刪除儲存過程
drop procedure 名字複製程式碼note：不要加（）
使用引數
建立儲存過程 create prodecure 名字（in 引數1，out 引數2）使用儲存過程 call 名字（@引數1，@引數2，@引數3）刪除儲存過程 drop prodecurecreate procedure ordertotal( IN onnumber INT, //傳給儲存過程 IN taxable BOOEAN, OUT OTOTAL DECIMAL //從儲存過程傳出 //inout:對儲存過程傳入和傳出)BEGIN DECLARE total DECIMAL(8,2); DECLARE taxrate INT default 6 ; select SUM(item_price*quantity) from orderitems where orderitems into total; -- if和end if 是一套語法 if taxable then select total+(total/100*taxrate) into total ; end if; select total into ototal;end;declare： 宣告瞭兩個區域性變數decimal (8,1)：小數點前面8位，後面是2位且在0-8之間into:將前面的後再後面的變數內。in out declare 等變數-----------------------call productpricing(20005，0，@total);select @total;複製程式碼引數的資料型別：儲存過程的資料型別和表中的資料型別一樣
24遊標
遊標：是結果集可以在選擇的瀏覽和更改其中的資料，且只能用於儲存過程
建立遊標
create procedure processorders()begin declare done BOOLEAN default 0; declare o int; declare t decimal(8,2); declare ordernumbers cursor for --給這個查詢語句設定遊標 select order_num from orders; declare continue handkler for sqlstate '02000' set done =1; create table if not exists ordertotals(order_num int,total decimal(8,2)); --使用遊標 open ordernumbers; --迴圈檢索 REPEAT --遊標檢索 FETCH ordernumbers INTO o; call ordertotal(o,1,t); insert into ordertotal(o,1,t) values(o,t); until done end repeat --關閉遊標 close ordernumbersend;複製程式碼
25 觸發器
名稱空間：表內
刪除觸發器：
drop trigger newproduct //先刪除後改複製程式碼note:before 用於資料的淨化
delete刪除觸發器
1.在old裡面使用一個名為old的虛擬表，訪問被刪除的行
2.old表是隻讀的
create trigger deleteorder before delete on ordersfor each row begin insert into archive_orders(order_num,order_date,cust_id)values(old.order_num,old.order_date,old.cust_id); end --麼名字，跟哪個表有關係，什麼語句觸發（增刪改），什麼時候觸發 --觸發器失效：後面的相應的sql語句不執行複製程式碼update觸發器
old訪問以前的值，new訪問新的值
new是跟新值跟新別人。
26管理事務
mysql不沒人支援事務
--開啟事務
start transaction;
--保留點
savepoint delete1
--回滾
rollback to delete1
--提交
commit
--更改預設提交行為
set autocommit =0(假)
29全球化和本地化
顯示字符集：show character set
校對：show collation
create table mytable( columnn1 int, colummn2 varchar(10))default charactor set hbrewcollate hebrew_general_ci;--不指定則使用預設的--排序時指定不同的校對順序可以人為的指定，也可使用在havin 聚集函式複製程式碼30 安全管理
1.建立一個新使用者賬號
create user Kirkzhang identified by '123'複製程式碼2.重新命名錶
rename user Kirkzhang to shiming複製程式碼3.刪除表
drop user shiming 複製程式碼訪問許可權設定
grant select on crashcourse.* to KirkzhangKirkzhang使用者對crashcourse資料庫種具有隻讀許可權（select）複製程式碼
revoke select on crashcourse.* from Kirkzhang複製程式碼作用範圍：
整個伺服器：grant all和revoke all
整個資料庫：on database.*
特定表：on database.table
特定列；
特定儲存過程
許可權表
簡化授權：
grant select ,insert on crashcourse.* to Kirkzhang複製程式碼更改口令
set password for Kirkzhang = password('new123');
31 資料庫的維護
flush table :保證所有資料寫入磁碟（備份資料）
32 改善效能
檢視設定檢視執行緒也可以用explain解釋然後殺死他們。
檢視設定：show variables ；show status；
所有執行緒：show processlist
殺死執行緒：kill
讓mysql 解釋語句：explain
delayed關鍵字
複雜or影響效能
like很慢
儲存過程比一條語句要快
在匯入資料時，應該關閉自動提交。你可能還想刪除索引（包括 FULLTEXT索引），然後在匯入完成後再重建它們。
索引改善效能，但是會影響插入，刪除，更新，速度，
經過一些列操作，表的結構會改變
最重要的規則就是，每條規則在某些條件下都會被打破。
33 資料型別
串資料型別char-varchar：存名字，地址 ,用單引號
CHAR 1～255個字元的定長串。它的長度必須在建立時指定，否則MySQL假定為CHAR(1)ENUM 接受最多64 K個串組成的一個預定義集合的某個串LONGTEXT 與TEXT相同，但最大長度為4 GB MEDIUMTEXT 與TEXT相同，但最大長度為16 K SET 接受最多64個串組成的一個預定義集合的零個或多個串TEXT 最大長度為64 K的變長文字TINYTEXT 與TEXT相同，但最大長度為255位元組VARCHAR 長度可變，最多不超過255位元組。如果在建立時指定為VARCHAR(n)，則可儲存0到n個字元的變長串 （其中n≤255複製程式碼數值型別
BIT 位欄位，1～64位。（在MySQL 5之前，BIT在功能上等價於TINYINTBIGINT 整數值，支援9223372036854775808～9223372036854775807（如果是UNSIGNED，為0～ 18446744073709551615）的數BOOLEAN （或BOOL） 布林標誌，或者為0或者為1，主要用於開/關（on/off）標誌DECIMAL （或DEC） 精度可變的浮點值DOUBLE 雙精度浮點值FLOAT 單精度浮點值INT （或INTEGER） 整數值，支援2147483648～2147483647（如果是UNSIGNED， 為0～ 4294967295）的數MEDIUMINT 整數值，支援8388608～8388607（如果是UNSIGNED，為0～16777215）的數REAL 4位元組的浮點值SMALLINT 整數值，支援32768～32767（如果是UNSIGNED，為0～65535）的數TINYINT 整數值，支援128～127（如果為UNSIGNED，為0～255）的數複製程式碼日期型別
DATE 表示1000-01-01～9999-12-31的日期，格式為YYYY-MM-DDDATETIME DATE和TIME的組合TIMESTAMP 功能和DATETIME相同（但範圍較小）TIME 格式為HH:MM:SSYEAR 用2位數字表示，範圍是70（1970年）～69（2069年），用4位數字表示，範圍是1901年～2155年複製程式碼二進位制型別
BLOB Blob最大長度為64 KB MEDIUMBLOB Blob最大長度為16 MB LONGBLOB Blob最大長度為4 GB TINYBLOB Blob最大長度為255位元組複製程式碼第34章 mysql進階
34.1 case when
case ///end複製程式碼CASE sex
when ' 1 ' then ' 男 '
when ' 2 ' then ' 女 '
else ' 其他' end
CASE
when sex = ' 1 ' then ' 男 '
when sex = ' 2 ' then ' 女 '
else ' 其他 ' end
CASE
when col_1 in ('a','b') then '第一類'
when col_2 in ('a') then '第二類'
else '其他' end
和sum一起使用
select sum( case u.sex when 1 then 1 else 0 end)男性 sum( case u.sex when 2 then 1 else 0 end)女性 sum( case u.sex <>1 and u.sex <>2 then 1 else 0 end) 性別為空from user as u 男性 女性 性別為空---------- ---------- ---------- 3 2 0複製程式碼
select count(case when u.sex=1 then 1 end)男性, count(case when u.sex=2 then 1 end)女, count(case when u.sex <>1 and u.sex<>2 then 1 end)性別為空from users as u; 男性 女 性別為空---------- ---------- ---------- 3 2 0複製程式碼