零、VsCode操作MySQL
大贊?。?！vscode果然還是一如既往的優(yōu)秀，沒(méi)有讓我失望~
1. windows下的插件連接
0、解決密碼認(rèn)證方式問(wèn)題
8.0以上版本特殊的密碼認(rèn)證方式會(huì)導(dǎo)致登錄失敗，解決方案如下：
控制臺(tái)輸入：
alter user 'root'@'localhost' identified with mysql_native_password by 'root';
flush privileges;
1、下載下列插件
2、在左側(cè)的資源管理器中找到MySQL入口，點(diǎn)擊+
host輸入MySQL服務(wù)器名或ip （根據(jù)實(shí)際情況輸入）
（本地服務(wù)器就輸入localhost或者127.0.0.1）
user是輸入數(shù)據(jù)庫(kù)用戶名（根據(jù)實(shí)際情況輸入）
password是數(shù)據(jù)庫(kù)密碼（根據(jù)實(shí)際情況輸入）
port是數(shù)據(jù)庫(kù)端口號(hào) 默認(rèn)3306（根據(jù)實(shí)際情況輸入）
登陸成功：
3、建立新的query文件
4、這種結(jié)構(gòu)下軟件分窗就顯得非常實(shí)用了
5、執(zhí)行query語(yǔ)句快捷鍵：
執(zhí)行所有語(yǔ)句：不框選語(yǔ)句情況下按F9
執(zhí)行單個(gè)語(yǔ)句：選擇語(yǔ)句情況下按F9
6、執(zhí)行代碼格式化：alt +shift +F
一、數(shù)據(jù)庫(kù)的設(shè)計(jì)
1、多表間的關(guān)系
①、一對(duì)一
例如：人和身份證的關(guān)系
實(shí)現(xiàn)方式：在任意一方添加唯一外鍵指向另一方主鍵。
②、一對(duì)多/多對(duì)一
例如：一個(gè)部門有多個(gè)員工，一個(gè)員工只有一個(gè)部門
實(shí)現(xiàn)方式：
在多的一方建立外鍵并指向一的一方的主鍵
③、多對(duì)多
例如：一個(gè)學(xué)生有多門課程，一門課程有多個(gè)學(xué)生
實(shí)現(xiàn)方式：多對(duì)多需要借助第三張中間表，中間表至少包含兩個(gè)字段，這兩個(gè)字段作為作為第三張表的外鍵，分別指向兩張表的主鍵
2、數(shù)據(jù)設(shè)計(jì)范式
設(shè)計(jì)數(shù)據(jù)庫(kù)需要遵循的規(guī)范
六種范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、
巴斯科特范式（BCNF）、第四范式（4NF）、第五范式（5NF）
要遵循后面的范式，要先遵循前面的范式
我們一般只需要滿足前三個(gè)范式就可以了
關(guān)于范式. 請(qǐng)參考如下視頻:
https://www.bilibili.com/video/BV1nx411G7NX?p=86
https://www.bilibili.com/video/BV1p7411A7Lp?p=16
①、第一范式（1NF）：
每一列都是不可分割的基本數(shù)據(jù)項(xiàng),且必須有主鍵
意思是列不可以嵌套及混合
只按照第一范式設(shè)計(jì)當(dāng)然不行，問(wèn)題如下：
②、第二范式（2NF）：
解決非主屬性部分依賴于主關(guān)鍵字。（在1NF基礎(chǔ)上消除非主屬性對(duì)主碼的部份依賴）
第二范式要求所有非主鍵字段完全依賴主鍵, 不能部分依賴
幾個(gè)概念：
a：函數(shù)依賴
A-->B
在一張表中，如果通過(guò)A屬性（屬性組）的值可以唯一確定B屬性的值，則B依賴于A
如：
學(xué)號(hào)-->姓名 學(xué)號(hào)可以唯一確定姓名 姓名依賴于學(xué)號(hào)
學(xué)號(hào) ！-->分?jǐn)?shù) 只靠學(xué)號(hào)無(wú)法確定分?jǐn)?shù) 分?jǐn)?shù)不依賴于學(xué)號(hào)
(學(xué)號(hào),課程名稱)-->分?jǐn)?shù) (學(xué)號(hào),課程名稱)可以唯一確定分?jǐn)?shù)，分?jǐn)?shù)依賴于(學(xué)號(hào),課程名稱)
b：完全函數(shù)依賴
A-->B
在一張表中，如果通過(guò)A的屬性組中的所有值可以唯一確定B屬性的值，則B完全依賴于A
例如：
(學(xué)號(hào),課程名稱)-->分?jǐn)?shù) (學(xué)號(hào),課程名稱)可以唯一確定分?jǐn)?shù)，分?jǐn)?shù)依賴于(學(xué)號(hào),課程名稱)
c: 部分函數(shù)依賴
A-->B
在一張表中，如果通過(guò)A的屬性組中的部分值可以唯一確定B屬性的值，則B部分依賴于A
例如：
(學(xué)號(hào),課程名稱)-->姓名 (學(xué)號(hào),)可以唯一確定姓名，姓名部分依賴于(學(xué)號(hào),課程名稱)
d：傳遞函數(shù)依賴
A-->B-->C
在一張表中，如果通過(guò)A屬性（屬性組）的值可以唯一確定B屬性的值，通過(guò)B屬性（屬性組）的值可以唯一確定C屬性的值，則C傳遞函數(shù)依賴于A
例如：學(xué)號(hào)-->系名-->系主任
e：碼
在一張表中，如果一個(gè)表中的一個(gè)屬性（屬性組）被其他所有屬性完全依賴，則稱這個(gè)屬性（屬性組）為該表的碼。
f：主屬性
碼中的屬性
g：非主屬性
非碼中的屬性
現(xiàn)在我們來(lái)消除例子中的部分依賴：
拆分表格后，數(shù)據(jù)冗余問(wèn)題解決了，但還有2、3問(wèn)題
③、第三范式（3NF）：
一個(gè)數(shù)據(jù)庫(kù)表中不包含已在其它表中已包含的非主關(guān)鍵字信息 （在2NF基礎(chǔ)上消除傳遞依賴）
還應(yīng)該添加課程表
二、數(shù)據(jù)庫(kù)備份還原
1、命令行
①備份
mysqldump -u用戶名 -p密碼 數(shù)據(jù)庫(kù)名 > 保存的路徑
②還原
1）登錄數(shù)據(jù)庫(kù)服務(wù)器
2）創(chuàng)建數(shù)據(jù)庫(kù)
3）使用數(shù)據(jù)庫(kù)
4）執(zhí)行： source 文件路徑。
2.圖形化
①、備份
② 還原
三、多表查詢
1、查詢笛卡爾積
select * from 表名1,表名2;
這樣查詢出來(lái)的結(jié)果是笛卡爾積（A和B的所有組合可能性的集合）。
這樣的結(jié)果很多無(wú)用數(shù)據(jù)。
2、多表查詢的分類
①、內(nèi)連接查詢 //查詢兩表交集
a）隱式內(nèi)連接
select 表1.列名，表2.列名 from 表1 別名1，表2 別名2 where 條件；
b）顯式內(nèi)連接
select 字段列表 from 表名1 join 表2 on 條件；
注意：要用內(nèi)連接查詢需要確定：
a）確定從哪些表中查數(shù)據(jù)
b）條件是什么（判斷有效數(shù)據(jù)）
c）確定需要查詢哪些字段
②、外鏈接查詢
a）左外鏈接 //查詢左表所有記錄及交集
select 字段列表 from 表1 left join 表2 on 條件;
例：假設(shè)有一位新員工還沒(méi)有部門
如果查內(nèi)連接交集就查不到了，查左連接就可以查到
b）右外鏈接 //查詢右表所有記錄及交集
select 字段列表 from 表1 right join 表2 on 條件;
例：
③、子查詢
子查詢就是查詢中嵌套查詢
a）子查詢情況1：子查詢的結(jié)果是單行單列的，此時(shí)子查詢可以作為條件，使用運(yùn)算符進(jìn)行計(jì)算。
如：
b）子查詢情況2：查詢的結(jié)果是多行單列的 可以使用運(yùn)算符in
例如：
c）子查詢情況2：查詢的結(jié)果是多行多列的 把子查詢當(dāng)作虛擬表供查詢
四、事務(wù)
1、事務(wù)
①、概念
如果一個(gè)包含多個(gè)步驟的業(yè)務(wù)操作，被事務(wù)管理，那么這些操作要么同時(shí)成功，要么同時(shí)失敗。
A給B要?jiǎng)濆X，A的賬戶-500元，B的賬戶就要+500元，這兩個(gè)update語(yǔ)句必須作為一個(gè)整體來(lái)執(zhí)行，不然A扣錢了，B沒(méi)有加錢這種情況很難處理。
②、操作
a）開(kāi)啟手動(dòng)事務(wù)：start transaction
b）提交：commit
c）回滾：rollback
注意：1.即使沒(méi)有提交，你在本query最后查詢數(shù)據(jù)也會(huì)發(fā)現(xiàn)變化了，但當(dāng)你退出軟件重啟數(shù)據(jù)庫(kù)發(fā)現(xiàn)沒(méi)有改變，你看到的只是臨時(shí)數(shù)據(jù)。只有commit后才是真正的數(shù)據(jù)。上圖中的數(shù)據(jù)是我重啟后的數(shù)據(jù)
2.commit和rollback只能執(zhí)行一個(gè)，需要類似于PHP類的后臺(tái)程序做判斷，數(shù)據(jù)庫(kù)本身只提供語(yǔ)句，不能再query中寫(xiě)判斷。
③、mysql不開(kāi)啟手動(dòng)提交事務(wù)時(shí)，是默認(rèn)自動(dòng)提交的，每條語(yǔ)句都會(huì)提交一次。
修改自動(dòng)提交：
a）查看事務(wù)的默認(rèn)提交方式：
SELECT @@autocommit;
b）修改為手動(dòng)提交
set @@autocommit=0;
c）如果改為手動(dòng)提交 必須commit才會(huì)提交數(shù)據(jù)
2、事務(wù)的四大特征
a）原子性：不可分割的最小操作單位，要么同時(shí)成功，要么同時(shí)失敗
b）持久性：當(dāng)數(shù)據(jù)提交或回滾時(shí)，會(huì)持久化的改變數(shù)據(jù)
c）隔離性：多個(gè)事務(wù)之間項(xiàng)目獨(dú)立
d）一致性：事務(wù)操作前后總量不變。
3、事務(wù)的隔離級(jí)別
事務(wù)有隔離性，但是多個(gè)事務(wù)同時(shí)操作同一批數(shù)據(jù)，會(huì)引發(fā)一些問(wèn)題，設(shè)置不同的隔離級(jí)別可以解決這些問(wèn)題
引發(fā)的問(wèn)題：
a）臟讀：一個(gè)事務(wù)讀取到另一個(gè)事務(wù)沒(méi)有提交的數(shù)據(jù)
b）虛讀（不可重復(fù)讀）：同一個(gè)事務(wù)中兩次讀取到的同一數(shù)據(jù)不一樣
c）幻讀：一個(gè)事務(wù)操作數(shù)據(jù)表中所有數(shù)據(jù)，另一個(gè)事務(wù)添加數(shù)據(jù)，則第一個(gè)事務(wù)查詢不到自己的修改（MySQL中不存在）
隔離級(jí)別：
a）read uncommited 讀未提交
此級(jí)別下，可出現(xiàn)：臟讀、虛讀、幻讀
b）read commited 讀已提交
此級(jí)別下，可出現(xiàn)：虛讀、幻讀
c）repeatable read 可重復(fù)讀 （MySQL默認(rèn)）
此級(jí)別下，可出現(xiàn) 幻讀
d）serializable：串行化
此級(jí)別下，無(wú)問(wèn)題。但效率最低。
查詢隔離級(jí)別：
select @@tx_isolation; /*mysql 8.0以后棄用*/
select @@transaction_isolation;/*mysql 8.0以后用*/
設(shè)置隔離級(jí)別：
set global transaction isolation level read committed;
注意：設(shè)置后需要重啟生效，上圖中我已經(jīng)重啟。
五、DCL
DCL用來(lái)管理數(shù)據(jù)庫(kù)用戶、授權(quán)
1、管理用戶
①、添加用戶：
create user '用戶名'@'主機(jī)名' identified by "密碼"; /*注意，必須用單引號(hào)！*/
②、刪除用戶：
drop user'用戶名'@'主機(jī)名';
③、修改用戶密碼：（8.0以后新方法）
alter user 'root'@'localhost'IDENTIFIED BY 'MyNewPass';
flush privileges;
④、查詢用戶：
a）切換到mysql數(shù)據(jù)庫(kù)
我不知道為什么workbench里不顯示這個(gè)數(shù)據(jù)庫(kù)，但他真的有
b）查詢user表
use mysql;
show databases;
select * from user;
⑤、忘記root密碼怎么辦？
1.cmd執(zhí)行（管理員權(quán)限） ：net stop MySQL
2.無(wú)驗(yàn)證方式啟動(dòng)mysql服務(wù)：mysql --skip-grant-tables
3.打開(kāi)新的cmd窗口，輸入mysql 直接回車登錄成功
4.再新cmd中使用mysql數(shù)據(jù)庫(kù) ：use mysql；
5.修改root密碼。
6.關(guān)閉所有cmd
7.打開(kāi)任務(wù)管理器 結(jié)束 mysqld.exe 這個(gè)進(jìn)程
8.重啟服務(wù)，新密碼登錄
2、權(quán)限管理
①、查詢權(quán)限
show grants for '用戶名'@'服務(wù)器';
<figcaption class="Image-caption" style="margin-top: 0.66667em; padding: 0px 1em; font-size: 0.9em; line-height: 1.5; text-align: center; color: rgb(153, 153, 153);">這個(gè)用戶除了登錄沒(méi)有任何權(quán)限</figcaption>
查詢r(jià)oot發(fā)現(xiàn)他的權(quán)限無(wú)限大
②、授予權(quán)限
grant select on 數(shù)據(jù)庫(kù).表名 to '用戶名'@'主機(jī)名';
授權(quán)后只有這張表他可以看
授予所有權(quán)限
grant ALL on *.* to '用戶名'@'主機(jī)名';
常用權(quán)限有：
show privileges;顯示結(jié)果---(權(quán)限操作分類)
all(謹(jǐn)慎，包含管理數(shù)據(jù)庫(kù)的權(quán)限) 授予某個(gè)級(jí)別特定的所有權(quán)限，除了grant option權(quán)限:表示自己有的權(quán)限授權(quán)給別人
alter 允許使用alter table語(yǔ)句 級(jí)別：global、database、table(1、2、3)
alter routine 允許修改或刪除存儲(chǔ)例程 級(jí)別：global、database、table(1、2、3)
create 允許創(chuàng)建數(shù)據(jù)庫(kù)和表
create tablespace 允許創(chuàng)建、修改、刪除表空間和日志文件組 級(jí)別：global
create temporary tablespace 允許創(chuàng)建臨時(shí)表 級(jí)別：global、database
create user 允許使用create user、drop user、rename user、revoke all privileges語(yǔ)句 級(jí)別：global
create viwe 允許創(chuàng)建和修改視圖
shutdown 允許使用mysqldmin shutdown 級(jí)別：global
delete 允許刪除表中的行
drop 允許刪除數(shù)據(jù)庫(kù)、表、視圖
event 允許使用事件 mysql的時(shí)間調(diào)度-linux下的crontab
execute 允許用戶執(zhí)行存儲(chǔ)過(guò)程
file 允許用戶使用load data infile讀寫(xiě)文件 級(jí)別：global
grant option 允許把自己有的權(quán)限授權(quán)給其他用戶或者從其他用戶那里廢除 級(jí)別：global、database、table、procedure、proxy
index 允許創(chuàng)建或刪除索引
insert 允許向表中插入行
lock tables 允許使用lock tables語(yǔ)句 級(jí)別：global、database
process 允許使用show processlist語(yǔ)句查看正在運(yùn)行的進(jìn)程 級(jí)別：global
proxy 允許用戶代理 級(jí)別
references 允許使用外鍵 級(jí)別：global、database、table、column
reload 允許使用flush語(yǔ)句 級(jí)別：global
select 允許查詢表 級(jí)別：global、database、table、column
show databases 允許使用show databases語(yǔ)句 級(jí)別：global
show view 允許使用show create view語(yǔ)句 級(jí)別：global
super 允許使用其他管理語(yǔ)句
trigger 允許觸發(fā)器操作
update 允許更新表中的行 級(jí)別：global、database、table、column
usage "no privileges"的同義詞
③、撤銷權(quán)限
revoke select on 數(shù)據(jù)庫(kù).表名 from '用戶名'@'主機(jī)名';
