MYSQL復習筆記（第五天）
MYSQL復習筆記目錄
目錄索引：
MYSQL復習筆記（第四天）主要復習視圖、觸發(fā)器、事務、備份還原
接前面的內(nèi)容，繼續(xù)復習！
MYSQL復習筆記正文
-- 變量聲明
declare var_name[,...] type [default value]
這個語句被用來聲明局部變量。要給變量提供一個默認值，請包含一個default子句。值可以被指定為一個表達式，不需要為一個常數(shù)。如果沒有default子句，初始值為null。
-- 賦值
使用 set 和 select into 語句為變量賦值。
- 注意：在函數(shù)內(nèi)是可以使用全局變量（用戶自定義的變量）
-- 定義、賦值
set 語句可以定義并為變量賦值。
set @var = value;
也可以使用select into語句為變量初始化并賦值。這樣要求select語句只能返回一行，但是可以是多個字段，就意味著同時為多個變量進行賦值，變量的數(shù)量需要與查詢的列數(shù)一致。
還可以把賦值語句看作一個表達式，通過select執(zhí)行完成。此時為了避免=被當作關(guān)系運算符看待，使用:=代替。（set語句可以使用= 和 :=）。
select @var:=20;
select @v1:=id, @v2=name from t1 limit 1;
select * from tbl_name where @var:=30;
select into 可以將表中查詢獲得的數(shù)據(jù)賦給變量。
-| select max(height) into @max_height from tb;
-- 自定義變量名
為了避免select語句中，用戶自定義的變量與系統(tǒng)標識符（通常是字段名）沖突，用戶自定義變量在變量名前使用@作為開始符號。
@var=10;
- 變量被定義后，在整個會話周期都有效（登錄到退出）
-- if語句
if search_condition then
statement_list
[elseif search_condition then
statement_list]
...
[else
statement_list]
end if;
-- case語句
CASE value WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ELSE result]
END
-- while循環(huán)
[begin_label:] while search_condition do
statement_list
end while [end_label];
- 如果需要在循環(huán)內(nèi)提前終止 while循環(huán)，則需要使用標簽；標簽需要成對出現(xiàn)。
-- 退出循環(huán)
退出整個循環(huán) leave
退出當前循環(huán) iterate
通過退出的標簽決定退出哪個循環(huán)
-- 數(shù)值函數(shù)
abs(x) -- 絕對值 abs(-10.9) = 10
format(x, d) -- 格式化千分位數(shù)值 format(1234567.456, 2) = 1,234,567.46
ceil(x) -- 向上取整 ceil(10.1) = 11
floor(x) -- 向下取整 floor (10.1) = 10
round(x) -- 四舍五入去整
mod(m, n) -- m%n m mod n 求余 10%3=1
pi() -- 獲得圓周率
pow(m, n) -- m^n
sqrt(x) -- 算術(shù)平方根
rand() -- 隨機數(shù)
truncate(x, d) -- 截取d位小數(shù)
-- 時間日期函數(shù)
now(), current_timestamp(); -- 當前日期時間
current_date(); -- 當前日期
current_time(); -- 當前時間
date('yyyy-mm-dd hh:ii:ss'); -- 獲取日期部分
time('yyyy-mm-dd hh:ii:ss'); -- 獲取時間部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化時間
unix_timestamp(); -- 獲得unix時間戳
from_unixtime(); -- 從時間戳獲得時間
-- 字符串函數(shù)
length(string) -- string長度，字節(jié)
char_length(string) -- string的字符個數(shù)
substring(str, position [,length]) -- 從str的position開始,取length個字符
replace(str ,search_str ,replace_str) -- 在str中用replace_str替換search_str
instr(string ,substring) -- 返回substring首次在string中出現(xiàn)的位置
concat(string [,...]) -- 連接字串
charset(str) -- 返回字串字符集
lcase(string) -- 轉(zhuǎn)換成小寫
left(string, length) -- 從string2中的左邊起取length個字符
load_file(file_name) -- 從文件讀取內(nèi)容
locate(substring, string [,start_position]) -- 同instr,但可指定開始位置
lpad(string, length, pad) -- 重復用pad加在string開頭,直到字串長度為length
ltrim(string) -- 去除前端空格
repeat(string, count) -- 重復count次
rpad(string, length, pad) --在str后用pad補充,直到長度為length
rtrim(string) -- 去除后端空格
strcmp(string1 ,string2) -- 逐字符比較兩字串大小
-- 流程函數(shù)
case when [condition] then result [when [condition] then result ...] [else result] end 多分支
if(expr1,expr2,expr3) 雙分支。
-- 聚合函數(shù)
count()
sum();
max();
min();
avg();
group_concat()
-- 其他常用函數(shù)
md5();
default();
-- 新建
CREATE FUNCTION function_name (參數(shù)列表) RETURNS 返回值類型
函數(shù)體
- 函數(shù)名，應該合法的標識符，并且不應該與已有的關(guān)鍵字沖突。
- 一個函數(shù)應該屬于某個數(shù)據(jù)庫，可以使用db_name.funciton_name的形式執(zhí)行當前函數(shù)所屬數(shù)據(jù)庫，否則為當前數(shù)據(jù)庫。
- 參數(shù)部分，由'參數(shù)名'和'參數(shù)類型'組成。多個參數(shù)用逗號隔開。
- 函數(shù)體由多條可用的mysql語句，流程控制，變量聲明等語句構(gòu)成。
- 多條語句應該使用 begin...end 語句塊包含。
- 一定要有 return 返回值語句。
-- 刪除
DROP FUNCTION [IF EXISTS] function_name;
-- 查看
SHOW FUNCTION STATUS LIKE 'partten'
SHOW CREATE FUNCTION function_name;
-- 修改
ALTER FUNCTION function_name 函數(shù)選項
-- 定義
存儲存儲過程 是一段代碼（過程），存儲在數(shù)據(jù)庫中的sql組成。
一個存儲過程通常用于完成一段業(yè)務邏輯，例如報名，交班費，訂單入庫等。
而一個函數(shù)通常專注與某個功能，視為其他程序服務的，需要在其他語句中調(diào)用函數(shù)才可以，而存儲過程不能被其他調(diào)用，是自己執(zhí)行 通過call執(zhí)行。
-- 創(chuàng)建
CREATE PROCEDURE sp_name (參數(shù)列表)
過程體
參數(shù)列表：不同于函數(shù)的參數(shù)列表，需要指明參數(shù)類型
IN，表示輸入型
OUT，表示輸出型
INOUT，表示混合型
注意，沒有返回值。
存儲過程是一段可執(zhí)行性代碼的集合。相比函數(shù)，更偏向于業(yè)務邏輯。
調(diào)用：CALL 過程名
-- 注意
- 沒有返回值。
- 只能單獨調(diào)用，不可夾雜在其他語句中
-- 參數(shù)
IN|OUT|INOUT 參數(shù)名 數(shù)據(jù)類型
IN 輸入：在調(diào)用過程中，將數(shù)據(jù)輸入到過程體內(nèi)部的參數(shù)
OUT 輸出：在調(diào)用過程中，將過程體處理完的結(jié)果返回到客戶端
INOUT 輸入輸出：既可輸入，也可輸出
-- 語法
CREATE PROCEDURE 過程名 (參數(shù)列表)
BEGIN
過程體
END
用戶信息表：mysql.user
-- 刷新權(quán)限
FLUSH PRIVILEGES
-- 增加用戶
CREATE USER 用戶名 IDENTIFIED BY [PASSWORD] 密碼(字符串)
- 必須擁有mysql數(shù)據(jù)庫的全局CREATE USER權(quán)限，或擁有INSERT權(quán)限。
- 只能創(chuàng)建用戶，不能賦予權(quán)限。
- 用戶名，注意引號：如 'user_name'@'192.168.1.1'
- 密碼也需引號，純數(shù)字密碼也要加引號
- 要在純文本中指定密碼，需忽略PASSWORD關(guān)鍵詞。要把密碼指定為由PASSWORD()函數(shù)返回的混編值，需包含關(guān)鍵字PASSWORD
-- 重命名用戶
RENAME USER old_user TO new_user
-- 設(shè)置密碼
SET PASSWORD = PASSWORD('密碼') -- 為當前用戶設(shè)置密碼
SET PASSWORD FOR 用戶名 = PASSWORD('密碼') -- 為指定用戶設(shè)置密碼
-- 刪除用戶
DROP USER 用戶名
-- 分配權(quán)限/添加用戶
GRANT 權(quán)限列表 ON 表名 TO 用戶名 [IDENTIFIED BY [PASSWORD] 'password']
- all privileges 表示所有權(quán)限
- *.* 表示所有庫的所有表
- 庫名.表名 表示某庫下面的某表
-- 查看權(quán)限
SHOW GRANTS FOR 用戶名
-- 查看當前用戶權(quán)限
SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();
-- 撤消權(quán)限
REVOKE 權(quán)限列表 ON 表名 FROM 用戶名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用戶名 -- 撤銷所有權(quán)限
-- 權(quán)限層級
-- 要使用GRANT或REVOKE，您必須擁有GRANT OPTION權(quán)限，并且您必須用于您正在授予或撤銷的權(quán)限。
全局層級：全局權(quán)限適用于一個給定服務器中的所有數(shù)據(jù)庫，mysql.user
GRANT ALL ON *.*和 REVOKE ALL ON *.*只授予和撤銷全局權(quán)限。
數(shù)據(jù)庫層級：數(shù)據(jù)庫權(quán)限適用于一個給定數(shù)據(jù)庫中的所有目標，mysql.db, mysql.host
GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤銷數(shù)據(jù)庫權(quán)限。
表層級：表權(quán)限適用于一個給定表中的所有列，mysql.talbes_priv
GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤銷表權(quán)限。
列層級：列權(quán)限適用于一個給定表中的單一列，mysql.columns_priv
當使用REVOKE時，您必須指定與被授權(quán)列相同的列。
-- 權(quán)限列表
ALL [PRIVILEGES] -- 設(shè)置除GRANT OPTION之外的所有簡單權(quán)限
ALTER -- 允許使用ALTER TABLE
ALTER ROUTINE -- 更改或取消已存儲的子程序
CREATE -- 允許使用CREATE TABLE
CREATE ROUTINE -- 創(chuàng)建已存儲的子程序
CREATE TEMPORARY TABLES -- 允許使用CREATE TEMPORARY TABLE
CREATE USER -- 允許使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW -- 允許使用CREATE VIEW
DELETE -- 允許使用DELETE
DROP -- 允許使用DROP TABLE
EXECUTE -- 允許用戶運行已存儲的子程序
FILE -- 允許使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX -- 允許使用CREATE INDEX和DROP INDEX
INSERT -- 允許使用INSERT
LOCK TABLES -- 允許對您擁有SELECT權(quán)限的表使用LOCK TABLES
PROCESS -- 允許使用SHOW FULL PROCESSLIST
REFERENCES -- 未被實施
RELOAD -- 允許使用FLUSH
REPLICATION CLIENT -- 允許用戶詢問從屬服務器或主服務器的地址
REPLICATION SLAVE -- 用于復制型從屬服務器（從主服務器中讀取二進制日志事件）
SELECT -- 允許使用SELECT
SHOW DATABASES -- 顯示所有數(shù)據(jù)庫
SHOW VIEW -- 允許使用SHOW CREATE VIEW
SHUTDOWN -- 允許使用mysqladmin shutdown
SUPER -- 允許使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL語句，mysqladmin debug命令；允許您連接（一次），即使已達到max_connections。
UPDATE -- 允許使用UPDATE
USAGE -- “無權(quán)限”的同義詞
GRANT OPTION -- 允許授予權(quán)限
-- 分析和存儲表的關(guān)鍵字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 檢查一個或多個表是否有錯誤
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理數(shù)據(jù)文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
1. 可用反引號（`）為標識符（庫名、表名、字段名、索引、別名）包裹，以避免與關(guān)鍵字重名！中文也可以作為標識符！
2. 每個庫目錄存在一個保存當前數(shù)據(jù)庫的選項文件db.opt。
3. 注釋：
單行注釋 # 注釋內(nèi)容
多行注釋 /* 注釋內(nèi)容 */
單行注釋 -- 注釋內(nèi)容 (標準SQL注釋風格，要求雙破折號后加一空格符（空格、TAB、換行等）)
4. 模式通配符：
_ 任意單個字符
% 任意多個字符，甚至包括零字符
單引號需要進行轉(zhuǎn)義 \'
5. CMD命令行內(nèi)的語句結(jié)束符可以為 ';', '\G', '\g'，僅影響顯示結(jié)果。其他地方還是用分號結(jié)束。delimiter 可修改當前對話的語句結(jié)束符。
6. SQL對大小寫不敏感
7. 清除已有語句：\c
MYSQL復習 就到這里了！休息一下！
