一、MySQL五子句
1、基本概念
select語句中包含一個查詢子句（五子句，==包括where、group by、having、order by和limit==）
基本文法：
select */字段 from 表 where ... group by ... having ... order by ... limit ...
2、where子句
案例1：查詢姓名為"波仔"的學員資訊（精準查詢）
select * from it_student where name='波仔';
或
select * from it_student where name="波仔";
案例2：查詢id>5的所有産品資訊
select * from it_goods where id > 5;
案例3：查詢産品價格在3000到5000的所有産品資訊
select * from it_goods where price > 3000 and price < 5000;
或
select * from it_goods where price between 3000 and 5000;
案例4：查詢學科subject不為運維的其他學員資訊
select * from it_student where subject != 'yunwei';
或
select * from it_student where subject <> 'yunwei';
案例5：查詢學科為ui與yunwei的所有學員資訊
select * from it_student where subject = 'ui' or subject = 'yunwei';
或
select * from it_student where subject in ('ui','yunwei');
案例6：查詢id不等于1,3,5的學員資訊
select * from it_student where id not in (1,3,5);
案例7：查詢學生表中姓"王"的所有學員資訊
select * from it_student where name like '王%';
案例8：查詢學生表中姓"王"且名字為二個字的學員資訊，如王一
select * from it_student where name like '王_';
案例9：查詢産品表中包含Apple的所有産品資訊
select * from it_goods where title like '%Apple%';
案例10：查詢标題中包含數字的産品資訊（建議少用，效率較低）
select * from it_student where title regexp '[0-9]';
案例11：空值NULL，查詢subject資訊為NULL的所有學員資訊
空值一般表示資料未知、不确定或以後再添加。空值不同于0，也不同于空字元串。當需要查詢某字段内容是否為空值時，可以使用關鍵字IS NULL來實作，不為空則一使用IS NOT NULL來實作。
select * from it_student where subject is null;
擴充：查詢學生表中的所有學科資訊（去重）
select distinct subject from it_student;
3、group by子句
GROUP BY我們可以先從字面上來了解，GROUP表示分組，BY後面寫字段名，就表示根據哪個字段進行分組，GROUP BY必須得配合聚合函數來用，分組之後你可以計數（COUNT），求和（SUM），求平均數（AVG）等。
案例：求it_student學生表中，男女的人數比例資訊
第一步：select sex from it_student group by sex;
+------+
| sex |
+------+
| 男 |
| 女 |
+------+
第二步：結合統計（聚合）函數配合使用
select sex,count(*) from it_student group by sex;
原理圖：
由上圖可知，使用GROUP BY對sex進行分組時，系統會自動對資料記錄進行周遊（一個一個查詢），如果sex='男'，則系統自動放入sex='男'的分組，COUNT(id) + 1，反之，sex='女'的分組進行+1操作，當所有記錄周遊結束後，系統自動呈現分組後的結果。
4、having子句
① having與where類似，根據條件對==資料==進行過濾篩選。（能使用where查詢的都可以使用having代替）
② where==針對表中的列==發揮作用，查詢資料
③ having==針對查詢結果集==發揮作用，篩選資料
select */字段 from 資料表 where子句 group by子句 having子句
案例：求學科中，學科人數大于2以上的學科資訊
select subject,count(*) from it_student group by subject having count(*) > 2;
5、order by子句
由小到大，正序排列，1 2 3 4 5 ... => order by 字段 asc
由大到小，倒序排列，5 4 3 2 1 ... => order by 字段 desc
案例：列出it_student表中所有學員的資訊，要求，按薪資進行排序(由大到小)
select * from it_student order by salary desc;
如果不指定order by 排序條件，則計算機預設根據主鍵由小到大進行正序排列
6、limit子句
案例1：查詢學生表中，年齡最大的3名學員資訊
select * from it_student order by age desc limit 3;
案例2：資料分頁核心技術分析
一共7條記錄，每頁顯示2條記錄：
第一頁，顯示id=1和id=2 select * from lamp limit 0,2;
第二頁，顯示id=3和id=4 select * from lamp limit 2,2;
第三頁，顯示id=5和id=6 select * from lamp limit 4,2;
第四頁，顯示id=7和id=8 select * from lamp limit 6,2;
7、常用函數
- IFNULL函數擴充
IFNULL()函數：
MySQL中的IFNULL函數類似于Oracle中的NVL函數，其表達式為：IFNULL(expr1,expr2)，如果第一個參數不為空，則傳回第一個參數，否則傳回第二個參數。
ifnull(comm,0)
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.
IF()函數：
IF(expr1,expr2,expr3)類似于Oracle中的NVL2函數，如果第一個表達式的值為TRUE（不為0或null），則傳回第二個參數的值，否則傳回第三個參數的值
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3.
NULLIF()函數：
NULLIF(expr1,expr2),如果expr1=expr2為真，傳回null；否則傳回expr1
NULLIF(expr1,expr2)
Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
- 合并列函數(concat)
select concat(user,'-',host) as '使用者名-主機資訊' from mysql.user;
create table it_home(path varchar(255),homedir varchar(255));
insert into it_home set path='/home',homedir='itcast';
select concat(path,'/', homedir) from it_home;
8、四則運算
select 1+2;
select 1-3;
select 1*4;
select 1/4;
select 1 + 1 from dual; dual表,俗稱萬能表
二、MySQL進階查詢
1、基本概念
使用UNION關鍵字可以把來自多個SELECT語句的結果組合到一個結果集中，這種查詢就稱之為并（UNION）運算或聯合查詢。合并時，多個SELECT子句中對應的字段數和資料類型必須相同。
基本文法：
SELECT...FROM...WHERE
UNION [ALL]
SELECT...FROM...WHERE
[...UNION [ALL]
SELECT...FROM...WHERE]
注：其中，不适用關鍵字ALL，執行的時候去掉重複的記錄，所有傳回的行都是唯一的；使用關鍵字ALL的作用是不去掉重複的記錄，也不對結果進行自動排序。
2、UNION案例
在實際項目開發中，當一個表的資料很大，比如200G，這時我們需要進行分表處理（如水準分表），核心思想：把一個大表，分割N個小表，小表和大表結構一樣，隻是把資料分散到不同的表中。
案例：把資料表1與表2進行聯合查詢
3、交叉連接配接查詢（了解）
建立資料庫db_shop，在資料庫中建立分類表與産品表：
建立分類表tb_category：
建立産品表tb_goods：
基本文法：
SELECT * FROM 表1 CROSS JOIN 表2;
或
SELECT * FROM 表1, 表2;
交叉連接配接傳回的查詢結果集的記錄行數等于其所連接配接的兩張表記錄行數的乘積。例如tb_category表中有5條記錄，tb_goods表中有4條記錄，這兩個表交叉連接配接後結果集的記錄行數将是5 x 4 = 20條。
由此可見，倘若所關聯的兩張表的記錄行數很多時，交叉連接配接的查詢結果集會非常大，且查詢執行時間非常長，甚至有可能因為傳回資料過多而造成系統的停滞不前。是以，對于存在大量資料的表，應該避免使用交叉連接配接。同時，也可以在FROM子句的交叉連接配接後面，使用WHERE子句設定過濾條件，減少傳回的結果集。
4、内連接配接查詢
内連接配接（INNER JOIN）通過在查詢中設定連接配接條件來移除交叉連接配接查詢結果集中某些資料行。具體而言，内連接配接就是使用比較運算符進行表間某些字段值的比較操作，并将與連接配接條件相比對的資料行組成新的記錄，其目的是為了消除交叉連接配接中某些沒有意義的資料行。也就是說，在内連接配接查詢中，隻有滿足條件的記錄才能出現在結果集中。
内連接配接所對應的SQL語句由兩種表示形式：
① 使用INNER JOIN的顯式文法結構為：
SELECT 目标清單達式1, 目标清單達式2, 目标清單達式n
FROM table1 [INNER] JOIN table2
ON 連接配接條件
[WHERE 過濾條件];
② 使用WHERE子句定義聯結條件的隐式文法結構為：
SELECT 目标清單達式1, 目标清單達式2, ..., 目标清單達式n
FROM table1, table2
WHERE 連接配接條件 [AND 過濾條件];
上述兩種表示形式的差别在于：使用INNER JOIN連接配接後，FROM子句中的ON子句可用來設定連接配接表的連接配接條件，而其他過濾條件則可以在SELECT語句中的WHERE子句中指定；而使用WHERE子句定義連接配接條件的形式，表與表之間的連接配接條件和查詢時的過濾條件均在WHERE子句中指定。
案例1：
5、表别名
當表的名稱很長或需要多次使用相同的表時，可以為表指定别名，用别名代表原來的表名。
基本格式：
6、外連接配接查詢
☆ 左外連接配接
左外連接配接，也稱左連接配接（LEFT OUTER JOIN或LEFT JOIN），用于傳回該關鍵字左邊表（基表）的所有記錄，并用這些記錄與該關鍵字右邊表（參考表）中的記錄進行比對，如果左表的某些記錄在右表中沒有比對的記錄，就和右表中的“萬能行”連接配接，即右表對應的字段值均被設定為空值NULL。
使用内連接配接查詢，查詢所有産品的分類資訊：
由上圖可知，如果使用内連接配接進行連接配接查詢，系統隻會傳回catid與tb_category分類表中catid所比對的産品資訊，但是我們剛才插入的"神舟戰神"筆記本并沒有顯示出來，因為其catid為NULL。這個時候如果我們需要顯示所有産品資訊的分類資訊，就需要使用左外連接配接了。
使用外連接配接查詢，查詢所有産品的分類資訊：
☆ 右外連接配接
右外連接配接，也稱右連接配接（RIGHT OUTER JOIN或RIGHT JOIN），以右表為基表，其連接配接方法和左外連接配接完全一樣，即傳回右表的所有記錄，并用這些記錄與左邊表（參考表）中的記錄進行比對，如果右表的某些記錄在坐标中沒有比對的記錄，左表對應的字段值均被設定為空值NULL。
7、子連接配接查詢
子查詢也稱嵌套查詢，是将一個查詢語句嵌套在另一個查詢語句的WHERE子句或HAVING短語中，前者被稱為内層查詢或子查詢，後者被稱為外層查詢或父查詢。在整個SELECT語句中，先計算子查詢，然後将子查詢的結果作為父查詢的過濾條件。嵌套查詢可以用多個簡單查詢構成一個複雜的查詢，進而增強SQL的查詢能力。
案例1：查詢具有分類的産品資訊
① 第一步：擷取所有産品分類編号catid
② 第二步：查詢所有産品資訊（要求産品的catid出現在第一步的catid清單中）
案例2：查詢學生表中薪資最高的學員資訊
第一步：求出每個學科薪資最高是多少
select subject,max(salary) from it_student group by subject;
第二步：查詢班級中學員的資訊
select * from it_student where (subject,salary) in (?);
第三步：把第二步中的問号，替換成第一步中的SQL語句（完成）
select * from it_student where (subject,salary) in (select subject,max(salary) from it_student group by subject);
案例3：查詢每個學科中薪資最高的學員資訊
第一步：求出每個學科薪資最高是多少
select subject,max(salary) from it_student group by subject;
第二步：查詢學生表中的學員資訊
select * from it_student where (subject,salary) in (?);
第三步：把第二步中的問号，替換成第一步中的SQL語句（完成）
select * from it_student where (subject,salary) in (select subject,max(salary) from it_student group by subject);
三、事務處理
1、什麼是事務處理
事務(transaction)：一系列将要發生或正在發生的連續操作。
事務安全，是一種保護連續操作同時實作（完成）的機制。事務安全的意義就是，保證資料操作的一緻性與完整性。
2、舉個栗子：金錢交易
A最近手頭緊，沒錢，找B借錢。 A：0.00元 銀行轉賬 B：1000.20元
要完成一系列操作：轉賬（關聯很多SQL語句） B銀行卡扣錢-1000 update 資料表 set money=money-1000 where name='B';
A：0.00元 銀行機器突然故障、斷電了，問題：導緻資料不一緻，資料不完整。(事務安全) B：0.20元
A銀行卡增錢+1000 update 資料表 set money=money+1000 where name='A'; 當以上兩個SQL語句同時完成，則認為交易成功，交易成功才能寫入資料庫。
3、事務處理的前提
MySQL引擎：MyISAM引擎（查）與InnoDB引擎（安全） 記住：事務處理隻能發生在InnoDB引擎上，從MySQL5.5以後版本開始，系統預設的引擎都是InnoDB引擎。
實驗：事務處理必須使用InnoDB引擎
create table it_bank(
id int not null auto_increment primary key,
name varchar(40),
money decimal(11,2)
) engine=innodb default charset=utf8;
插入測試資料
insert into it_bank values (null,'A',1000.20);
insert into it_bank values (null,'B',0.00);
4、事務處理步驟
第一步：開啟事務
start transaction;
第二步：執行SQL語句
update it_bank set money=money-1000 where id=1;
update it_bank set money=money+1000 where id=2;
第三步：如果成功，則送出。反之，則復原
commit; #送出
或
rollback; #復原
5、事務的ACID
A:原子性（Atomicity）：事務作為一個整體被執行，包含在其中的對資料庫的操作要麼全部被執行，要麼都不執行。
C:一緻性（Consistency）：事務應確定資料庫的狀态從一個一緻狀态轉變為另一個一緻狀态。一緻狀态的含義是資料庫中的資料應滿足完整性限制。
I:隔離性（Isolation）：多個事務并發執行時，一個事務的執行不應影響其他事務的執行。
D:持久性（Durability）：已被送出的事務對資料庫的修改應該永久儲存在資料庫中。
四、使用者管理
1、建立使用者(create user)
注意：MySQL中不能單純通過使用者名來說明使用者，必須要加上主機。如
[email protected]① 文法
建立使用者設定密碼
create user 'user'@'host' identified by 'password';
說明：使用者的資訊儲存在mysql資料庫中的user表中，驗證使用者是否建立成功如下：
select user,host from mysql.user;
② 示例
create user 'tom'@'localhost' identified by '123';
create user 'harry'@'localhost' identified by '123';
create user 'tony'@'10.1.1.1' identified by '123';
create user 'jack'@'%' identified by '123';
③ 使用者主機表示方式
'user'@'localhost' 表示user隻能在本地通過socket登入資料庫
'user'@'192.168.0.1' 表示user使用者隻能在192.168.0.1登入資料庫
'user'@'192.168.0.0/24' 表示user使用者可以在該網絡任意的主機登入資料庫
'user'@'%' 表示user使用者可以在所有的機器上登入資料庫
2、使用者權限管理
① 權限說明
USAGE 無權限,隻有登入資料庫,隻可以使用test或test_*資料庫
ALL 所有權限
以下權限為指定權限
select/update/delete/super/slave/reload...
with grant option 選項表示允許把自己的權限授予其它使用者或者從其他使用者收回自己的權限
② 權限儲存位置
mysql.user 所有mysql使用者的賬号和密碼，以及使用者對全庫全表權限（*.*）
mysql.db 非mysql庫的授權都儲存在此(db.*)
mysql.table_priv 某庫某表的授權(db.table)
mysql.columns_priv 某庫某表某列的授權(db.table.col1)
mysql.procs_priv 某庫存儲過程的授權
③ 給使用者授權
1) 文法
grant 權限1,權限 on 庫.表 to 使用者@主機
grant 權限(列1,列2,...) on 庫.表 to 使用者@主機
2) 使用者授權示例
給[email protected]使用者授予檢視db01庫裡所有表權限
mysql> grant select on db01.* to 'tom'@'10.1.1.1';
重新整理權限表
mysql> flush privileges;
給[email protected]使用者授予修改db01庫的ID字段的權限
mysql> grant update(ID) on db01.tt1 to 'tom'@'10.1.1.1';
mysql> flush privileges;
檢視目前使用者權限
mysql> show grants;
檢視指定使用者權限
mysql> show grants for 'tom'@'10.1.1.1';
3) 使用grant建立使用者
grant all on *.* to 'harry'@'10.1.1.%' identified by '123';
4) with grant option選項
with grant option:使用者是否可以下放和回收權限
grant all on *.* to 'amy'@'10.1.1.%' identified by '123' with grant option;
grant all on *.* to 'harry'@'10.1.1.%' identified by '123';
測試harry使用者和amy使用者是否可以将自己的權限下放：
harry使用者登入：
mysql> grant select on db01.* to 'tom'@'10.1.1.1';
ERROR 1044 (42000): Access denied for user 'harry'@'10.1.1.%' to database 'db01'
amy使用者登入：
mysql> grant select on db01.* to 'tom'@'10.1.1.1';
5) 總結
- 建立使用者方法
-
create user...
-
grant xxx
- 擴充補充
- 從MySQL 5.7.6開始，不贊成使用grant修改密碼;使用 ALTER USER來代替。
ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
④ 回收使用者權限
revoke 權限 on 庫.表 from 使用者;
撤消指定的權限
mysql> revoke update on db01.tt1 from 'tom'@'10.1.1.1';
撤消所有的權限
mysql> revoke select on db01.* from 'tom'@'10.1.1.1';
3、删除使用者( ==drop user== )
① 文法
drop user 使用者;
② 示例
删除'user01'@'localhost'使用者
mysql> drop user 'user01'@'localhost';
預設删除user01從任意主機登入
mysql> drop user 'user01';
mysql> drop user 'user01'@'%';
重命名使用者名
mysql> rename user 'harry'@'10.1.1.%' to 'harry'@'10.1.1.1';
删除一個匿名使用者
mysql> drop user ''@'localhost';
删除mysql中的匿名使用者
mysql> delete from mysql.user where user='';
删除root使用者從本機::1登入（::1表示IPv6位址）
mysql> delete from mysql.user where user='root' and host='::1';
mysql> flush privileges;
注意：如果tcp/ip登入，伺服器端口不是預設3306，則需要加端口号