MYSQL基礎學習筆記

• 關系型資料庫與非關系型資料庫

1、關系型資料庫（SQL）：主要依據“一對一、一對多、多對多”等關系模型來創建的資料庫，

2、非關系型資料庫（NoSQL）：主要是基于“列模型、鍵值對模型、檔案類模型”等非關系模型的資料庫，

• MySQL服務端的登錄、退出與使用：

1、登錄：mysql –h 主機名 –u用戶名 –p密碼

2、退出：exit

3、使用：

（1）、進入mysql，在命令列中輸入：mysql –uroot –p密碼

（2）、查看mysql中有哪些個資料庫：show databases;

（3）、使用一個資料庫：use 資料庫名稱;

（4）、新建一個資料庫：CREATE DATABASE IF NOT EXISTS 資料庫名

（5）、查看指定的資料庫中有哪些資料表：show tables;

（6）、建表：CREATE TABLE [IF NOT EXISTS] t_表名｛欄位名1 資料型別 [約束] , .... ｝

（7）、查看表的結構：desc 表名

（8）、清空表（不能回滾）：TRUNCATE TABLE 表名

（9）、洗掉表：DROP TABLE [IF EXISTS] 資料表1 [, 資料表2, …, 資料表n];

（10）、洗掉資料庫：DROP DATABASE IF EXISTS 資料庫名;

注：mysql不區分大小寫

• SQL陳述句的分類

1、DQL（資料查詢語言）：select陳述句

語法：

（1）、查詢：

select <欄位名> [as 別名] from <表或視圖名>（ join <表名> on <連接條件> ） 
　　　　　　where <查詢條件> group by <分組欄位> having <額外添加欄位條件> 
　　　　　　order by <欄位名（升序asc / 降序desc）> limit（ startIndex ，length ）；

2、DML（資料操作語言）：insert、update、delete陳述句

語法：

（2）、新增：

insert into  t_表名 （欄位名1，欄位名2...） values （值1，值2...）；

（3）、修改：

update t_表名 set 欄位名1 = 值1 ，欄位名2 = 值2 ...where 條件；

（3）、洗掉：

delete from t_表名 where 條件；

3、DDL（資料定義語言）：drop、create、alter

語法：

（1）、創建：

create tables IF NOT EXISTS t_表名｛欄位名1 資料型別 [約束]， 欄位名2 資料型別 [約束] .... ｝；

（2）、洗掉：

drop table IF EXISTS t_表名；

4、TCL（事物控制語言）：事務提交commit，事務回滾rollback

5、DCL（資料控制語言）：grant授權，revoke撤銷權限

• 1、基本的SELECT陳述句的使用：

（1）、使用關鍵字DISTINCT去除重復行：

SELECT DISTINCT name FROM t_users;

（2）、空處理函式IFNULL(expr1,expr2)過濾參與運算欄位：

所有資料庫規定只要有null參與的運算結果都為null；

IFNULL(expr1,expr2)：若expr1不是NULL，IFNULL()回傳expr1，否則它回傳expr2；

SELECT IFNULL(t_users.number,1) FROM t_users;

（3）、著重號（`  `）解決沖突：

保證表中的欄位、表名等沒有和保留字、資料庫系統名或常用方法名沖突

SELECT * FROM `STUDENT`;

（4）、LEAST(值1，值2，...，值n)

兩個或多個引數的情況下，回傳最小值

（5）、GREATEST(值1，值2，...，值n)

兩個或多個引數時，回傳值為最大值，假如任意一個自變數為NULL，則GREATEST()的回傳值為NULL

• 2、where子句的運算子：

• 3、Where子句中使用正則運算式（REGEXP）查詢：

（1）、查詢以特定字符或字串開頭的記錄：‘^’

在t_user表中，查詢name欄位以字母‘a’開頭的記錄

SELECT * FROM t_user WHERE name REGEXP '^a';

（2）、查詢以特定字符或字串結尾的記錄：‘$’

在t_user表中，查詢name欄位以字母‘z’結尾的記錄

SELECT * FROM t_user WHERE name REGEXP 'z$';

（3）、替代字串中的任意一個字符： "."

在t_user表中，查詢name欄位值包含字母‘a’與‘z’且兩個字母之間只有一個字母的記錄

SELECT * FROM t_user WHERE name REGEXP 'a.z';

• 4、MySQL函式的使用：

MySQL常使用的函式大概有四類：時間函式、數學函式、字符函式、控制函式，

注：

（1）、分組函式：count（）、avg（）、sum（）、max（）、min（）

（2）、分組函式不能在where后面執行：分組函式通常與group by一起使用，在group by后面執行，而group by是在where后面執行的

• 5、連接查詢的方式

（1）、內連接inner join on（無主副之分）：等值連接、非等值連接、自連接

（2）、外連接（有主副之分）：

　　1）、left join on：左外連接（左邊主表）、

　　2）、right join on：右外連接（右邊主表）

（3）、全連接full join on

（4）、合并查詢結果

　　1）、去除重復記錄：UNION關鍵字

　　2）、不去除重復記錄：UNION ALL關鍵字

SELECT name,... FROM t_table1

UNION

SELECT name,... FROM t_table2

（5）、SQL99語法新特性

　　1）、自然連接NATURAL join ：自動查詢兩張連接表中所有相同的欄位

SELECT id, name FROM employees e NATURAL JOIN departments d;

　　2）、JOIN...USING：指定資料表里的具體同名欄位進行等值連接

SELECT employee_id, department_name FROM employees e

JOIN departments d

USING (department_id);

• 6、MySql建表常用的資料型別

• 7、SQL表中常見約束

（1）、非空約束（not null）：規定某個欄位不能為空

（2）、唯一約束（unique）：同一個表可以有多個唯一約束，

（3）、主鍵約束（primary key）：相當于唯一約束與非空約束的組合，不允許為空，每個表有且最多只允許一個主鍵約束；

（4）、外鍵約束（foreign key）：構建于一個表的兩個欄位或是兩個表的兩個欄位之間的參照關系，保證一個或兩個表之間的參照完整性；

（5）、檢查約束（check）：MySQL 不支持 check 約束，但可以使用 check 約束，而沒有任何效果；

（6）、預設約束（Default）：在插入資料時某列如果沒指定其他的值,那么會將默認值添加到新記錄，

（7）、解釋欄位（COMMENT）：沒有任何實際含義，專門用來解釋欄位的含義

• 事務的理解與事務的四大特性：

1、事務：一組完整的DML業務邏輯單元

2、特性：原子性、一致性、隔離性、持久性

（1）、原子性（Atomicity）

原子性是指事務是一個不可分割的作業單位，事務中的操作要么都成功，要么都失敗，

（2）、一致性（Consistency）

事務必須使資料庫從一個一致性狀態變換到另外一個一致性狀態，

（3）、隔離性（Isolation）

事務的隔離性是指一個事務的執行不能被其他事務干擾，即一個事務內部的操作及使用的資料對并發的其他事務是隔離的，并發執行的各個事務之間不能互相干擾，

（4）、持久性（Durability）

持久性是指一個事務一旦被提交，它對資料庫中資料的改變就是永久性的，接下來的其他操作和資料庫故障不應該對其有任何影響，

3、四大隔離級別：

讀未提交——READ UNCOMMITED、讀已提交(Oracle默認)——READ COMMITED、

可重復讀(MySQL默認)——REPEATABLE READ、串行化讀——SERIALZABLE

（1）、查看當前的隔離級別:

SELECT @@tx_isolation;

（2）、設定當前 mySQL 連接的隔離級別：

set transaction isolation level 隔離級別；

（3）、設定資料庫系統的全域的隔離級別：

set global transaction isolation level 隔離級別;

• 視圖：

1、視圖是一個虛擬表（非真實存在），其本質是根據SQL陳述句獲取動態的資料集，并為其命名，用戶使用時只需使用名稱即可獲取結果集，并可以將其當作表來使用，

2、視圖可以提高檢索效率，隱藏表的實作細節，只對視圖物件進行CRUD操作，保護資料，提高安全性；

（1）、創建視圖：

create view 視圖名 as select陳述句；

（2）、洗掉視圖：

drop view if exists 視圖名;

• 存盤程序/存盤函式/游標/觸發器：

1、理解：

　　存盤程序是一個預先編譯過SQL陳述句的代碼塊，存盤程序可以設定用戶的操作權限，具有較強的安全性，并且每次使用時只需要呼叫存盤程序，降低網路的通信量，提高通信速率，但存盤程序的編碼復雜度更高，可移植性較差，除錯困難，不適合高并發的場景，只能說，針對某些專案比較好用，（阿里開發規范強制禁止使用），

2、語法：

DELIMITER 新的結束標記符 #可省

CREATE PROCEDURE 存盤程序名( IN | OUT | INOUT 引數名 引數型別，...)

[characteristics ...]

BEGIN

存盤程序體

END 新的結束標記符

DELIMITER ；#恢復默認結束符

注：

（1）、引數

　　1）、IN ：表示輸入引數，

　　2）、OUT ：表示輸出引數，

　　3）、INOUT ：表示當前引數既可以為輸入引數，也可以為輸出引數，

（2）、characteristics包括

　　1）、LANGUAGE SQL：表示當前系統支持的語言為SQL，

　　2）、{ DETERMINISTIC  |  NOT DETERMINISTIC }：

DETERMINISTIC：表示相同的輸入會得到相同的輸出，

NOT DETERMINISTIC：表示相同的輸入可能得到不同的輸出，

　　3）、{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程式使用SQL陳述句的限制，

CONTAINS SQL：表示當前存盤程序的子程式包含SQL陳述句，但是并不包含讀寫資料的SQL陳述句；

NO SQL：表示當前存盤程序的子程式中不包含任何SQL陳述句；

READS SQL DATA：表示當前存盤程序的子程式中包含讀資料的SQL陳述句；

MODIFIES SQL DATA：表示當前存盤程序的子程式中包含寫資料的SQL陳述句，

　　4）、SQL SECURITY { DEFINER | INVOKER } ：執行當前存盤程序的權限，即指明哪些用戶能夠執行當前存盤程序，

DEFINER：表示只有當前存盤程序的創建者或者定義者才能執行當前存盤程序；

INVOKER：表示擁有當前存盤程序的訪問權限的用戶能夠執行當前存盤程序，

　　5）、COMMENT '輸入的字串'

（3）、存盤程序體

中間包含了多個陳述句，每個陳述句都以分號作為結束符，

　　1）、DECLARE：宣告變數

　　2）、SET：對變數進行賦值，

　　3）、SELECT… INTO：把從資料表中查詢的結果存放到變數中，也就是為變數賦值，

3、例子：

（1）、查看最高工資：

CREATE PROCEDURE show_max_salary()

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT '查看最高薪資'

BEGIN

SELECT MAX(salary) FROM emps;

END //

DELIMITER ;

（2）、將最低薪資通過OUT引數“ms”輸出：

DELIMITER //

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)

BEGIN

SELECT MIN(salary) INTO ms FROM emps;

END //

DELIMITER ;

（3）、實作累加運算，計算 1+2+…+n：

DELIMITER //

CREATE PROCEDURE `add_num`(IN n INT)

BEGIN

DECLARE i INT;

DECLARE sum INT;

SET i = 1;

SET sum = 0;

WHILE i <= n DO

SET sum = sum + i;

SET i = i +1;

END WHILE;

SELECT sum;

END //

DELIMITER ;

4、呼叫格式：CALL 存盤程序名(實參串列)

（1）、呼叫in模式的引數：

CALL add_num ('值');

（2）、呼叫out模式的引數：

SET @ms;

CALL show_min_salary (@ms);

SELECT @ ms;

（3）、呼叫inout模式的引數：

SET @name=值;

CALL sp1(@name);

SELECT @name;

注：

MySQL中的系統變數以兩個“@”開頭，用戶變數以一個“@”開頭

5、存盤函式：

（1）、語法：

CREATE FUNCTION 函式名(引數名 引數型別,...)

RETURNS 回傳值型別

[characteristics ...]

BEGIN

RETURN (函式體)   #函式體中肯定有 RETURN 陳述句

END

（2）、對比：

6、游標

（1）、理解：

對結果集中的每一條記錄進行定位，充當指標的作用，mysql中游標只適用于存盤程序以及函式，

（2）、語法：

　　1）、定義游標：

declare 游標名 cursor for select陳述句;

　　2）、打開游標：

open 游標名;

　　3）、獲取結果：

fetch 游標名 into 變數名[,變數名];

　　4）、關閉游標：

close 游標名;

7、觸發器：

確保操作的原子性（要么全部成功，要么全部失敗），但存在最大問題是可讀性差，

（1）、語法：

CREATE TRIGGER 觸發器名稱

{ BEFORE（注：事件前觸發）| AFTER（注：事件后觸發）}

{INSERT | UPDATE | DELETE觸發事件} ON 表名

FOR EACH ROW

觸發器執行的陳述句塊;

• Mysql的流程控制結構——實作多分支

1、case結構

（1）、語法一：

case 運算式或欄位

when 值1 then 陳述句1

when 值2 then 陳述句2

…

else 陳述句n

end [case]

（2）、語法二：

case

when 條件1 then 陳述句1

when 條件2 then 陳述句2

…

else 陳述句n

end [case]

2、if結構

語法一：

if(條件運算式，運算式1，運算式2)；

如果條件運算式成立，回傳運算式1，否則回傳運算式2

語法二（存盤程序中使用）：

if 條件1 then 陳述句1;

elseif 條件2 then 陳述句2;

…

else 陳述句n;

end if;

 3、回圈結構（存盤程序中使用）

（1）、while： 類似java中的while回圈

語法格式：

【名稱】 while 回圈條件 do

回圈體;

end while 【名稱】;

（2）、loop ：需要在回圈體中添加結束的條件，不然就是死回圈，

語法格式：

【名稱】 loop

回圈體;

end loop 【名稱】;

 （3）、repeat：類似java中的do while

語法格式：

【名稱】 repeat

回圈體;

until 結束回圈的條件

end repeat 【名稱】；

注：

回圈控制陳述句：

1）、iterate關鍵字：類似于java中的continue，結束本次回圈，繼續下一次回圈，

2）、leave關鍵字：類似于java中的break，跳出回圈，執行之后的陳述句，

標籤：MySQL

上一篇：MySQL快速創建800w條測驗資料表&深度分頁

下一篇：Kafka Eagle分布式模式