MySQL筆記
一、前期準(zhǔn)備
-
SQL語句分類
DQL - 數(shù)據(jù)查詢語言 --> 查詢語句，select 語句都是DQL
DML - 數(shù)據(jù)操作語言 --> insert、delete、update 等等對表數(shù)據(jù)進(jìn)行增刪改
DDL - 數(shù)據(jù)定義語言 --> create、 drop、 alter 等等對表結(jié)構(gòu)的增刪改
TCL - 事務(wù)控制語言 --> Transaction Commit - 提交事務(wù) Rollback - 回滾事務(wù)
DCL - 數(shù)據(jù)控制語言 --> grant - 授權(quán) revoke - 撤銷權(quán)限
-
MySQL卸載
去安裝目錄找到my.ini文件
復(fù)制文件中配置的路徑：【 datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/" 】
控制面板卸載MySQL
去第二步里復(fù)制的路徑中刪除 C:/ProgramData下的MySQL文件夾
-
常見的cmd命令
打開服務(wù)窗口 services.msc
打開/關(guān)閉MySQL服務(wù) net start/shut mysql
登錄MySQL mysql -uroot -p密碼
訪問別人的MySQL mysql -h別人IP -uroot -p別人密碼
退出MySQL exit/quit
二、DDL -- 對數(shù)據(jù)庫、表的操作
-
操作數(shù)據(jù)庫
-
C（Create）：創(chuàng)建
create database 數(shù)據(jù)庫名;create database if not exists 數(shù)據(jù)庫名;-- 判斷不存在再創(chuàng)建create database 數(shù)據(jù)庫名 character set 字符集名;-- 創(chuàng)建庫并指定字符集練習(xí)：
create database if not exists db0 character set utf8;
-
R（Retrieve）：查詢
show databases;-- 查詢所有的數(shù)據(jù)庫名show create database 數(shù)據(jù)庫名;-- 查看創(chuàng)建庫的語句
-
U（Update）：修改
alterdatabase 數(shù)據(jù)庫名 character set 字符集名;
-
D（Delete）：刪除
drop database 數(shù)據(jù)庫名;drop database if exists 數(shù)據(jù)庫名;-- 先判斷是否存在
-
使用數(shù)據(jù)庫
-
查詢當(dāng)前正使用的數(shù)據(jù)庫
selsect database();
-
使用數(shù)據(jù)庫
use 數(shù)據(jù)庫名;
-
-
-
操作表
-
C（Create）：創(chuàng)建
-
語法：create table 表名(列名1 類型1,
列名2 類型2,
列名3 類型3);
-
數(shù)據(jù)庫數(shù)據(jù)類型
-
int-- 整數(shù)類型age int
-
double-- 小數(shù)類型-
score double(3, 1);-- 最多三位，一位小數(shù)
-
date-- 日期 只包含年月日 yyyy- MM-dddatetime-- 時間戳類型 包含年月日時分秒 yyyy- MM-dd HH:mm:ss-
timestamp-- 包含年月日時分秒 yyyy- MM-dd HH:mm:ss- 如果不給這個字段賦值或賦值為null，則默認(rèn)值為系統(tǒng)當(dāng)前時間
-
varchar-- 字符串name varchar(20);張三 8個字符 zhangsan 兩個字符
-
復(fù)制表 --
create table 表名 like 源表名練習(xí)：
create table t_student( id int, name varchar(30), age int, score double(4,1), birthday date, insert_time timestamp); -
-
R（Retrieve）：查詢
-
show tables;-- 查詢當(dāng)前數(shù)據(jù)庫的所有表-
desc 表名;-- 查詢表結(jié)構(gòu)-
U（Update）：修改
-
修改表名
alter table 表名 rename to 新表名
-
修改表字符集
alter table 表名 character set 字符集名;
-
添加一列
alter table 表名 add 列名 類型;
-
修改列名稱、類型
alter table 表名 change 列名 新列名 新類型;alter table 表名 modify 列名 新類型;
-
刪除列
-
-
alter table 表名 drop 列名;-
D（Delete）：刪除
drop table if exists 表名;
-
三、DML -- 增刪改表中數(shù)據(jù)
-
添加數(shù)據(jù)
-
語法：
insert into 表名(列名1, 列名2, 列名3...) values(數(shù)據(jù)1, 數(shù)據(jù)2, 數(shù)據(jù)3...);
-
注意??！
前后括號的 列名和值（類型）要一一對應(yīng)
-
如果表名后不定義列名，則默認(rèn)給所有列添加值 （也要對應(yīng)上所有坑，不然報錯）
- insert into stu values(2, 'Jones', 18, 90.7, null,null);
除了數(shù)值類型，其他的類型都需要是喲個引號括起來【單雙引號都可】
-
-
刪除數(shù)據(jù)
-
語法：
delete from 表名 [where 條件 ];
-
注意??！
如果不寫條件則會刪除表中所有記錄
-
如果要刪除所有記錄，有兩種方式：
delete from 表名;-- 有多少條記錄都會一條一條刪除掉truncate table 表名;-- 先刪除整張表【包括其數(shù)據(jù)】，然后創(chuàng)建一張一模一樣的空表，效率高
-
-
修改數(shù)據(jù)
-
語法：
update 表名 set 列名1 = 值1, 列名2 = 值2...[where 條件]
-
注意??！
- 如果不加任何條件，則會將表中所有記錄全部修改
-
四、DQL -- 查詢語句
-
查詢表中記錄
-
語法：
select 字段列表 from 表名列表 where 條件列表 group by 分組字段 having 分組之后補(bǔ)充的條件 order by 排序 limit 分頁查詢 -
基礎(chǔ)查詢
-
多個字段的查詢
-
去除重復(fù)
-
select distinct address from stu;-- 地址重復(fù)去除
-
-
起別名
-
SELECT s.name AS 姓名,(IFNULL(s.math, 0) + IFNULL(s.english, 0)) AS 總分 FROM stu s ;-- as 看省略
-
-
-
條件查詢
where子句后跟條件
-
運算符
-
<、>、<=、>=、=、<>-
查詢年齡大于20歲的
select * from stu where age > 20;
-
-
beteen...and查詢年齡位于20-30歲的 SELECT * FROM stu WHERE age <= 30 && age >= 20; SELECT * FROM stu WHERE age BETWEEN 20 AND 30; #推薦寫法 -
like模糊查詢
-
占位符
_表示單個任意字符%表示多個任意字符
舉例：
# 查詢姓馬的記錄 第一個字是“馬”，其余任意多字無所謂 SELECT s.name, s.math, s.english FROM stu s WHERE s.name LIKE '馬%'; # 查詢姓名中第二個字是'化'的人 SELECT * from stu where name like '_化%'; # 第一個任意 # 查詢姓名有三個字的人 SELECT * FROM stu WHERE NAME LIKE '___'; # 三個下劃線 # 查詢姓名中包含“馬”和“德”的人 SELECT * FROM stu WHERE NAME LIKE "%德%" OR "%馬%";is null
#查詢英語成績?yōu)閚ull的列 與不為null的列 SELECT s.name FROM stu s WHERE s.english IS NULL; SELECT s.name FROM stu s WHERE s.english IS NOT NULL;and 或者 &&or 或者 ||
# 查詢年齡為19 22 25 的 SELECT * FROM stu WHERE age = 19 or age = 22 or age = 25; SELECT * FROM stu WHERE age in(18, 22, 25); #推薦寫法not 或者 !
-
-
排序查詢
-
語法：
order by 子句order by 排序字段1 排序方式1, 排序字段2 排序方式2...
-
排序方式：
asc：升序【默認(rèn)】desc：降序
注意??！如果有多個排序條件，則當(dāng)前邊的條件值一樣時，才會執(zhí)行第二判斷條件
-
練習(xí)：
# 按照數(shù)學(xué)成績排名，如果數(shù)學(xué)一樣，則按照英語成績排名 SELECT * FROM stu ORDER BY math ASC, english ASC; # 先按照前面的升序，如果前面的一樣則再按照后面的升序
-
-
聚合函數(shù) -- 將一列數(shù)據(jù)作為一個整體，進(jìn)行縱向的計算
-
count-- 計算個數(shù)- 一般選擇主鍵
max-- 計算最大值min-- 計算最小值sum-- 計算和avg-- 計算平均數(shù)
# 看下英語成績平均值 SELECT AVG(IFNULL(english, 0)) FROM stu;</pre>-
注意??！聚合函數(shù)計算，是排除null值的
解決方案一：選擇不包含null的列進(jìn)行計算
解決方案二：使用ifnull函數(shù)
SELECT COUNT(IFNULL(english, 0)) FROM stu;
-
-
分組查詢 -- 統(tǒng)計某一類具有相同特征的數(shù)據(jù)，把他們看做一個整體
-
語法：
group by分組字段# 按照性別分組，看下男女同學(xué)的平均分 SELECT sex 性別, AVG(IFNULL(math, 0)) 數(shù)學(xué)平均分, AVG(IFNULL(english, 0)) 英語平均分 FROM stu GROUP BY sex; # 按照性別分組，看下男女同學(xué)的平均分 分?jǐn)?shù)低于70的同學(xué)不參與分組 SELECT sex, AVG(IFNULL(math, 0)), AVG(IFNULL(english, 0)), COUNT(id) FROM stu WHERE IFNULL(math, 0) > 70 GROUP BY sex; # 按照性別分組，看下男女同學(xué)的平均分 分?jǐn)?shù)低于70的同學(xué)不參與分組， # 且分組后組內(nèi)個數(shù)小于2的不要了 SELECT sex, AVG(IFNULL(math, 0)), AVG(IFNULL(english, 0)), COUNT(id) FROM stu WHERE IFNULL(math, 0) > 70 GROUP BY sex HAVING COUNT(id) > 2; -
注意??！
分組之后查詢的字段只能是分組字段或者聚合函數(shù)了，不能有與個人有關(guān)的字段
-
where與having的區(qū)別where在分組之前【order by】進(jìn)行限定，而having在分組之后進(jìn)行限定where后不可以跟聚合函數(shù)，而having可以
-
-
分頁查詢
語法：
limit index, nindex -- 開始的索引； n -- 每頁查詢的條數(shù)-
公式：【當(dāng)前頁開始索引 = (當(dāng)前頁數(shù) - 1) * 每頁記錄條數(shù)】
# 每頁顯示三條,第一頁 SELECT * FROM stu LIMIT 0, 3; # 第二頁 SELECT * FROM stu LIMIT 3, 3; # 第三頁 SELECT * FROM stu LIMIT 6, 3; # 每頁開始索引 (當(dāng)前頁數(shù) - 1) * 每頁顯示條數(shù) = 每頁開始索引 分頁操作是一個方言 【在MySQL中分頁是
limit，其他數(shù)據(jù)庫有各自額關(guān)鍵字】
-
-
約束
概念：對表中數(shù)據(jù)進(jìn)行限定，保證數(shù)據(jù)的正確性、有效性與完整性
-
約束分類：
主鍵約束：
primary key非空約束：
not null唯一約束：
unique外鍵約束：
foreign key
-
主鍵 -- 該字段值非空且唯一
-
注意?。?/p>
一張表只能有一個字段為主鍵
主鍵就是表中記錄的唯一標(biāo)識
-
創(chuàng)建表時添加主鍵
# 創(chuàng)建一個新表,id是主鍵 create table stu1( id int primary key, name varchar(30) not null # name字段非空 ); # 一個表只能有一個主鍵 -
刪除主鍵
alter table stu1 drop primary key; # 一個表只有一個主鍵 -
創(chuàng)建表之后添加主鍵
# 給 id 字段添加約束主鍵 alter table stu1 modify id int primary key;- 添加前刪除所有重復(fù)字段
-
-
非空 -- 值不能為null
-
創(chuàng)建表時添加非空約束
# 創(chuàng)建一個新表,name字段非空 create table stu1( id int, name varchar(30) not null -- name非空 ); -
刪除已經(jīng)創(chuàng)建的表的非空約束
# 給name字段添加約束非空 alter table stu1 modify name varchar(30) not null;
-
-
唯一 -- 值不能重復(fù)
-
創(chuàng)建表時添加
# 創(chuàng)建一個新表,phoneName唯一 CREATE TABLE stu1( id INT, phoneName VARCHAR(20) UNIQUE # phoneName唯一 ); -
創(chuàng)建表之后添加
# 給 phoneName 字段添加約束唯一 alter table stu1 modify phoneName varchar(20) unique; -
刪除表字段的的唯一約束
alter table stu1 drop index phoneName; # 刪除表phoneName字段唯一約束 -
注意??！
MySQL中
unique限定的值可以有多個null刪除唯一約束的語法特殊
給已經(jīng)創(chuàng)建的表的某個字段添加唯一約束時，需要刪除掉該字段所有的重復(fù)的值
-
-
自動增長
概念：如果某一字段是數(shù)值類型，使用
auto_increment可以實現(xiàn)其自增【一般配合主鍵id一起使用】-
創(chuàng)建表時添加自增約束
# 創(chuàng)建一個新表,id是主鍵，且自增 create table stu1( id int primary key auto_increment, name varchar(30) not null -- name字段非空 );- 注意！！自增的數(shù)據(jù)只跟上一條記錄有關(guān)，如id自增到6，插入數(shù)據(jù)時，指定了id為20 則下一次自增從21開始
-
刪除自增約束
# 刪除自增約束 alter table stu1 modify id int; # id的主鍵約束不會被刪除掉 -
添加自增約束
# 給 id 字段添加約束自增 alter table stu1 modify id int auto_increment;
-
外鍵 -- 添加外鍵前，外鍵表刪除數(shù)據(jù)不會做檢查【比如小李還在A部門，刪除A部門不會做檢查】
- 在創(chuàng)建表時?？梢蕴砑油怄I
create table 表名( ... 外鍵列, constraint 外鍵名 foreign key (外鍵列名) references 主表名(主表列名) ); - 刪除外鍵
# 刪除外鍵 alter table 本表名 drop foreign key 外鍵名稱; - 創(chuàng)建表之后，添加外鍵
alter table 本表名 add constraint 外鍵名 foreign key (要關(guān)聯(lián)的本表列名) references 外鍵表名(要關(guān)聯(lián)的外鍵表列名); # 如下 alter table emp add constraint emp_edp_fk foreign key (dep_id) references dep(id); - 添加外鍵，并設(shè)置級聯(lián)更新[ -- 【更改/刪除部門的id時，在員工表里自動更改了對應(yīng)員工的部門號】用的時候謹(jǐn)慎
# 添加外鍵，并設(shè)置級聯(lián)更新與級聯(lián)刪除 ALTER TABLE emp ADD CONSTRAINT emp_edp_fk FOREIGN KEY (dep_id) REFERENCES dep(id) ON UPDATE CASCADE ON DELETE CASCADE;
- 在創(chuàng)建表時?？梢蕴砑油怄I
五、數(shù)據(jù)庫的設(shè)計
-
多表之間的關(guān)系
-
分類
-
一對一（了解）
如：人與身份證
一個人只能有一個身份證，一個身份證只能對應(yīng)一個人
-
一對多（多對一）
如：部門與員工
一個部門可以有多個員工，一個員工只能對應(yīng)一個部門
-
多對多
如：學(xué)生與課程
一個學(xué)生可以選擇多個課程，一個課程也可以被很多學(xué)生選擇
-
-
如何實現(xiàn)
-
一對多（多對一）
如：部門與員工
-
MySQL實現(xiàn)：
在多的一方【員工表】建立外鍵，指向一的一方【部門表】的主鍵
總結(jié) 一對多兩張表，多的一方加外鍵
-
多對多
如：學(xué)生與課程
-
MySQL實現(xiàn)：
多對多關(guān)系實現(xiàn)需要借助第三張表，此第三話張表至少包含兩個字段，這兩個字段作為第三張表的外鍵，分別指向兩張表的主鍵【選課表】
總結(jié) 多對多,三張表、關(guān)系表倆外鍵
-
一對一【了解】
- 一對一關(guān)系實現(xiàn)，在任意一方添加唯一外鍵指向另一方主鍵【直接放一張表吧。。。。】
-
-
-
范式
概念：設(shè)計數(shù)據(jù)庫時，遵循不同的規(guī)范要求，設(shè)計出合理的關(guān)系型數(shù)據(jù)庫，這些不同的規(guī)范要求被稱為不同的范式，各種范式呈遞次規(guī)范，越高的范式數(shù)據(jù)庫冗余越小
-
分類：（目前關(guān)系型數(shù)據(jù)庫有六種范式）
-
第一范式（1NF）： 任何一張表應(yīng)該有主鍵，且每一個字段原子性不可再分
-
第二范式（2NF）： 建立在1NF之上 另外要求所有非主鍵字段完全依賴主鍵，不能產(chǎn)生部分依賴 【消除非主字段對主鍵的部分函數(shù)依賴】
-
幾個概念
-
函數(shù)依賴：A --> B ,如果通過A列的值可以唯一確定列B的值，則稱B依賴于A
如 學(xué)號 --> 姓名 姓名依賴于學(xué)號
（學(xué)號，課程） --> 分?jǐn)?shù) 則分?jǐn)?shù)依賴于屬性組
-
完全函數(shù)依賴： A --> B 如果A是一個屬性組，則B值的確認(rèn)依賴于A中每一個屬性
- 如：（學(xué)號，課程） -- > 分?jǐn)?shù) 分?jǐn)?shù)的確定單由一個學(xué)號無法確定，單由課程也無法確定，一個確定的學(xué)號+一個確定的課程確定唯一一個分?jǐn)?shù)
-
部分函數(shù)依賴： A --> B 如果A是一個屬性組，則B值的確認(rèn)依只需要依賴于A中某一部分屬性
- 如：（學(xué)號，系名） --> 姓名。我們發(fā)現(xiàn)前面屬性組里，通過一個學(xué)號可以確定一個姓名，不需要系名，用這個屬性組局多余
傳遞依賴：A --> B, B -- > C，如果通過A屬性（組）可以唯一確定B屬性的值，再通過B屬性（組）的值可以唯一確定C屬性（組）的值，則C傳遞依賴于A
-
-
如：
-
-
第三范式（3NF）： 建立在2NF基礎(chǔ)上，所有外鍵字段直接依賴主鍵，不可產(chǎn)生傳遞依賴
-
-
數(shù)據(jù)庫的備份與還原
-
命令行形式
-
備份：mysqldump -u用戶名 -p密碼 要備份的數(shù)據(jù)庫 > 保存的路徑
mysqldump -uroot -padmin db1 > D:\JavaSETestDirectory01\MySQLBackup\db1.sql -
還原：
登錄數(shù)據(jù)庫
-
創(chuàng)建數(shù)據(jù)庫【還原的數(shù)據(jù)裝在此數(shù)據(jù)庫】
create database backupdb; -
使用數(shù)據(jù)庫【進(jìn)入這個數(shù)據(jù)庫，把數(shù)據(jù)還原進(jìn)來】
use backupdb; -
執(zhí)行文件。source 文件路徑
source D:\JavaSETestDirectory01\MySQLBackup\ddd.sql
-
圖形化工具
-
六、多表查詢
-
查詢語法：
select 列名列表 from 表名列表 where ...笛卡爾積：兩個集合，其所有情況為兩個集合列的乘積
消除笛卡爾積：下面三種方式
-
多表查詢分類：
-
內(nèi)連接：
- 隱式內(nèi)連接：使用where條件消除無用數(shù)據(jù)
SELECT e.name, # 員工表姓名 e.gender, # 員工表性別 d.name # 部門表名 FROM emp e, dep d WHERE e.dep_id = d.id; # 當(dāng)員工的部門id與部門的id相等時才返回結(jié)果- 顯示內(nèi)連接：顯示地指定連接方式
- 語法：
select 字段列表 from 表1 inner join 表2 on 條件# 顯示連接 查詢所有的員工信息與對應(yīng)的部門信息 SELECT * FROM emp e INNER JOIN dep d ON e.dep_id = d.id; # inner可省略 - 可以看出內(nèi)連接，查詢的是兩張表的交集
-
內(nèi)連接注意事項：
從哪些表中查詢數(shù)據(jù)
條件是什么
查詢哪些字段
-
外連接
-
左外連接
- 語法：
select 字段列表 from 表1 left outer join 表2 on 條件 # outer可省略# 查詢所有的員工信息，如果員工有部門，則查詢部門名稱。沒有則不顯示部門名 SELECT * FROM emp e LEFT OUTER JOIN dep d ON e.`dep_id` = d.`id`;-
可以看出--左外連接是查詢的左邊的表另外與右邊表的交集image
-
右外連接
- 語法：
select 字段列表 from 表1 right outer join 表2 on 條件 # outer可省略- 右外連接查詢的就是右邊的表及其交集
-
-
子查詢
概念：查詢中嵌套查詢，稱嵌套查詢?yōu)樽硬樵?/p>
-
案例： -- 查詢工資最高的員工信息
# 先查詢最高工資是多少 select * from emp where salary = 9000; # 帶著最高工資查詢信息 # 查詢工資最高的員工信息，使用子查詢 SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp); -
子查詢不同情況
-
子查詢是單行單列的
- 子查詢可以作為條件，使用運算符取判斷，運算符【>, <, >=, <=, =】
# 查詢工資小于平均工資的員工信息 SELECT * from emp where salary < (SELECT avg(salary) e from emp);-
子查詢是多行單列的
- 子查詢作為條件，使用運算符
in作為判斷
- 子查詢作為條件，使用運算符
# 查詢在開發(fā)部或者市場部工作的員工 SELECT * FROM emp WHERE dep_id IN (( SELECT id FROM dep WHERE NAME = "開發(fā)部" ), ( SELECT id FROM dep WHERE NAME = "市場部" )); -
-
* 子查詢是多行多列的
# 子查詢可以作為一張臨時表參與查詢
# 查詢員工入職日期是 2011-11-11 日之后的員工信息和部門信息
# 使用普通的內(nèi)連接
SELECT * from emp e, dep d where e.dep_id = d.id and e.join_date > "2011-11-11";
# 使用子查詢 多行多列
SELECT * from (select * from emp where emp.join_date > "2011-11-11") t, dep d where t.dep_id = d.id;
-
多表查詢練習(xí)：
查詢所有員工信息。查詢員工編號，員工姓名，工資，職務(wù)名稱，職務(wù)描述
查詢員工編號，員工姓名，工資，職務(wù)名稱，職務(wù)描述，部門名稱，部門位置
查詢員工姓名，工資，工資等級
查詢員工姓名，工資，職務(wù)名稱，職務(wù)描述，部門名稱，部門位置，工資等級
查詢出部門編號、部門名稱、部門位置、部門人數(shù)
查詢所有員工的姓名及其直接上級的姓名，沒有領(lǐng)導(dǎo)的員工也需要查詢
答案：
# 1. 查詢所有員工信息。查詢員工編號，員工姓名，工資，職務(wù)名稱，職務(wù)描述 select e.id 員工編號, e.ename 員工姓名, e.salary 工資, j.jname 職務(wù)名稱, j.de 職 務(wù)描述 from emp e, job j where e.job_id = j.id; # 2. 查詢員工編號，員工姓名，工資，職務(wù)名稱，職務(wù)描述，部門名稱，部門位 置 select e.id, e.ename, e.salary, j.jname, j.de, d.dname, d.loc from emp e, dep d, job j where e.dep_id = d.id and e.job_id = j.id; # 3. 查詢員工姓名，工資，工資等級 SELECT e.ename, e.salary, s.grade from emp e, salarygrade s where e.salary between s.losalary and s.hissalary; # 4. 查詢員工姓名，工資，職務(wù)名稱，職務(wù)描述，部門名稱，部門位置，工資等 級 select e.ename, e.salary, j.jname, j.de, d.dname, d.loc, s.grade from emp e, dep d, job j, salarygrade s where e.dep_id = d.id and e.job_id = j.id and e.salary BETWEEN s.losalary and s.hissalary; # 5. 查詢出部門編號、部門名稱、部門位置、部門人數(shù) /* 分析： · 1. 使用分組查詢，對 emp.dep_id 字段進(jìn)行分組，查詢部 門的id與其對應(yīng)數(shù)量 COUNT(emp.id) 作為臨時表 t 2. 把 1 中的表拿出來 子查詢與 dep 表連接查詢部門信息 條件：當(dāng)臨時表的id與部門表id相同時 */ SELECT dep.id 部門編號, dep.dname 部門, dep.loc 部門位置, t.tcount 部門人數(shù) FROM dep, ( SELECT emp.dep_id tid, COUNT(id) tcount FROM emp GROUP BY dep_id ) t WHERE t.tid = dep.id # 6. 查詢所有員工的姓名及其直接上級的姓名，沒有領(lǐng)導(dǎo)的員工也需要查詢 select t1.t1ename 員工姓名, emp.ename 上級領(lǐng)導(dǎo) from (select ename t1ename,mgr t1mgr from emp) t1 LEFT OUTER JOIN emp on emp.id = t1.t1mgr; -- 更簡單的，起別名把員工表看做另一張領(lǐng)導(dǎo)表 SELECT e.ename, e.mgr, m.id, m.ename FROM emp e LEFT OUTER JOIN emp m ON e.mgr = m.id;
七、事務(wù)
-
事務(wù)介紹
概念一個包含多個步驟的業(yè)務(wù)操作，被事務(wù)管理，那么這些事務(wù)要么同時成功，要么同時失敗【開啟事務(wù)后中間某一步出現(xiàn)問題就回滾到所有步驟之前，沒有任何異常則提交事務(wù)】
-
操作
開啟事務(wù)：start transaction
回滾：rollback
提交：commit 【如果手動開啟了事務(wù)但沒有手動提交，事務(wù)會回滾】
-
MySQL中數(shù)據(jù)庫事務(wù)默認(rèn)自動提交
一條DML（增刪改）語句會自動提交一次事務(wù)
-
事務(wù)提交的兩種方式：
自動提交 -- MySQL是自動提交的【Oracle數(shù)據(jù)庫時默認(rèn)手動提交的（需要寫commit）】
手動提交 -- 需要手動start開啟事務(wù)，然后手動commit提交
-
修改事務(wù)的提交方式
-
查看事務(wù)默認(rèn)提交方式：
select @@autocommit; -- 1為自動提交 0 為手動提交image-20200303135300042.png 修改默認(rèn)提交方式：
SET @@autocommit = 0
-
-
事務(wù)的四大特征
原子性：事務(wù)是不可分割的最小操作單位，要么同時成功，要么同時失敗
持久性：事務(wù)提交或回滾后，數(shù)據(jù)會持久化保存【關(guān)機(jī)，重啟應(yīng)用都已經(jīng)保存】
隔離性：多個事務(wù)之間相互獨立【期望互不影響】
一致性：事務(wù)操作前后，事務(wù)的總量不變【轉(zhuǎn)錢前A100，B100。轉(zhuǎn)錢后A20，B180】
-
事務(wù)的隔離級別（了解）
概念：多個事務(wù)之間是隔離的、獨立的。但是如果多個事務(wù)曹組同一批數(shù)據(jù)，則會引發(fā)一些問題，設(shè)置不同的隔離級別可以解決這些問題
-
存在的問題：
臟讀：一個事務(wù)，讀取到另一個事務(wù)匯總沒有提交的數(shù)據(jù)
（虛 讀）不可重復(fù)讀：在同一個事務(wù)中，兩次讀取到的數(shù)據(jù)不一樣
幻讀：一個事務(wù)操作（DML）數(shù)據(jù)表中所有記錄，另一個事務(wù)添加了一條數(shù)據(jù)，則第一個事務(wù)查詢不到自己的修改
-
隔離級別：
-
read uncommitted：讀未提交
-
產(chǎn)生的問題：臟讀、不可重復(fù)讀、幻讀
- 左邊事務(wù)更改信息還未提交或者回滾，右邊讀取到更新的信息【比如AB開啟事務(wù)，A給B轉(zhuǎn)賬500，但未提交，B可以看到自己的賬戶多了500，但是A此時回滾可以把這500不轉(zhuǎn)過去】
-
-
read committed ：讀已提交【Oracle默認(rèn)】
-
產(chǎn)生的問題：不可重復(fù)讀、幻讀
- 左邊事務(wù)提交更新信息，右邊事務(wù)在提交之前可以看到更新的信息，而不是保持不變
-
-
repeatable read：可重復(fù)讀【MySQL默認(rèn)】
- 產(chǎn)生的問題：幻讀【左邊事務(wù)提交更新的信息，但右邊事務(wù)未提交之前，右邊事務(wù)讀到的數(shù)據(jù)是不會改變的（實際上已經(jīng)改變了）。只有在右邊事務(wù)提交之后才會看到更新的數(shù)據(jù)信息】
-
serializable：串行化
可以解決所有的問題
一個鎖表的操作，一個事務(wù)在開啟并操作【增刪改查】時，其他任何事物不得操作【比如查詢時會卡住不動】，只有當(dāng)前操作事務(wù)提交/回滾，別的事務(wù)才能操作【效率低】
注意??！隔離級別從小到大安全性愈高，但是效率愈低
-
-
MySQL查詢隔離級別
查詢：
select @@tx_isolation修改：
set global transaction isolation level 級別字符串【級別字符串是上述的read uncommitted等】
八、DCL -- 控制權(quán)限與管理用戶
DBA ：數(shù)據(jù)庫管理員 -- 管理用戶，授權(quán)
-
管理用戶
-
添加用戶：
# 創(chuàng)建用戶【使用DCL語句】 # create user "用戶名"@"主機(jī)名" IDENTIFIED by "密碼"; create user "aaa"@"localhost" IDENTIFIED by "123"; -- 這樣就可以使用 mysql -uaaa -p123訪問這個數(shù)據(jù)庫 -
刪除用戶：
# 刪除用戶 # drop user "用戶名"@"主機(jī)名"： drop user "aaa"@"localhost"; -
修改用戶密碼：
#可行的 # set password for "用戶名"@"主機(jī)名" = password("新密碼"); set password for "aaa"@"localhost" = password("345");-
```# update user set PASSWORD = PASSWORD("新密碼") where user = "用戶名"; update user set PASSWORD = PASSWORD("123") where user = "aaa"; #### 實測不行 =.= -
如果忘記了自己的登錄密碼... =.=
停止MySQL服務(wù) cmd -->
net stop mysql需要管理員權(quán)限使用無驗證啟動MySQL服務(wù)：
mysqld --skip-grant- tables再新打開一個cmd窗口 輸入
mysql回車登錄使用 mysql 表，改密碼
use mysql; set password for "root"@"localhost" = password("新密碼"); # 或者下面這條命令 update user set password = password('新密碼') where user = 'root';關(guān)閉兩個窗口
打開任務(wù)管理器，手動結(jié)束
mysqld.exe進(jìn)程啟動mysql服務(wù)。使用新密碼登錄
-
-
查詢用戶：
-
切換到
mysql數(shù)據(jù)庫，用戶表是mysql下的user表use mysql; show tables; select * from user; #可以發(fā)現(xiàn)有兩個用戶，上面這個是本機(jī)登錄，第二個是%通配符 任意的【遠(yuǎn)程登錄】有兩個用戶記錄，上面的是本地主機(jī)，下面的是 % 通配符，表示可以在任意主機(jī)使用用戶登錄數(shù)據(jù)庫image-20200303164817539
-
-
-
權(quán)限管理（權(quán)限授予、添加）
-
查詢權(quán)限
# 查詢權(quán)限 普通創(chuàng)建的用戶只有登錄的權(quán)限 # show grants for '用戶名'@'主機(jī)名'; SHOW GRANTS FOR 'aaa'@'localhost'; -
授予權(quán)限
# 授予權(quán)限 給誰授予哪個數(shù)據(jù)庫的哪個表什么權(quán)限 # grant 權(quán)限列表 on 數(shù)據(jù)庫名.表名 to '用戶名'@'主機(jī)名'; GRANT SELECT, UPDATE, DELETE ON db3.emp TO 'aaa'@'localhost';2.1 授予全部權(quán)限
# 授予所有權(quán)限，在所有數(shù)據(jù)庫的所有的表上【謹(jǐn)慎使用】 GRANT SELECT, UPDATE, DELETE ON db3.emp TO 'aaa'@'localhost'; -
撤銷權(quán)限
# 撤銷權(quán)限 從誰撤銷哪個數(shù)據(jù)庫的哪個表什么權(quán)限 # revoke 權(quán)限列表 on 數(shù)據(jù)庫名.表名 from '用戶名'@'主機(jī)名'; revoke select on db3.emp from 'aaa'@'localhost';- 撤銷所有權(quán)限
revoke all on *.* from 'aaa'@'localhost';
-
