Java二十五篇： 數(shù)據(jù)庫
不忘初心110
前提常識：
內(nèi)存條 臨時記憶 ? ? 速度比較快 ? ? 不能永久保存數(shù)據(jù)
硬盤 文件夾 文件 U盤 持久化數(shù)據(jù)信息 ?可以對于數(shù)據(jù)進行增刪改查操作（能夠同時操作大批量數(shù)據(jù)）
市面上常見的數(shù)據(jù)庫
Oracle 收費型數(shù)據(jù)庫 性能高 服務(wù)額外收費 貴
MySql ?開源免費數(shù)據(jù)庫 Sun公司 Oracle收購 6.0以后開始收費
SqlServer 微軟數(shù)據(jù)庫 收費數(shù)據(jù)庫
DB2 IBM公司提供數(shù)據(jù)庫 收費的 銀行系統(tǒng)
SQLite ?嵌入式小型數(shù)據(jù)庫 移動端 Android系統(tǒng)
分類：四大類
1DDL(Data Definition Language)數(shù)據(jù)定義語言
用來定義數(shù)據(jù)庫對象：數(shù)據(jù)庫 表 列 Create
2DML(Data Manipulation Language)數(shù)據(jù)操作語言
對于數(shù)據(jù)的增刪改 ?insert delete update
3DQL(Data Query Language)數(shù)據(jù)操作語言
對于數(shù)據(jù)的查詢 select
4DCL(Data Control Language)數(shù)據(jù)控制語言
定義數(shù)據(jù)庫訪問權(quán)限 定義新的用戶及權(quán)限 密碼等等
Grant
數(shù)據(jù)庫四大類的語言
DDL:CRUD ? create drop alter show
操作數(shù)據(jù)庫
1.創(chuàng)建
*創(chuàng)建數(shù)據(jù)庫
createdatabase 數(shù)據(jù)庫名稱;
*先判斷是否存在 不存在才創(chuàng)建
createdatabaseifnotexists 數(shù)據(jù)庫名稱;
*邊創(chuàng)建邊確定數(shù)據(jù)庫編碼集
createdatabase 數(shù)據(jù)庫名稱 characterset utf8;
createdatabaseifnotexists 數(shù)據(jù)庫名稱 characterset utf8;
2.查詢
showdatabases;查詢所有的數(shù)據(jù)庫
? ? ? showcreatedatabase 數(shù)據(jù)庫名稱; 查詢某個數(shù)據(jù)庫的編碼集
3.修改
alterdatabase 數(shù)據(jù)庫名稱 characterset 新的字符編碼集;
*修改原有數(shù)據(jù)庫的字符編碼集 （在錄入數(shù)據(jù)前改好）
4.刪除
dropdatabase 數(shù)據(jù)庫名稱;
? *刪除數(shù)據(jù)可
? dropdatabaseifexists 數(shù)據(jù)庫名稱;
*先判斷是否存在 再刪除
5.使用數(shù)據(jù)庫
use 數(shù)據(jù)庫名稱;
*操作數(shù)據(jù)之前 先確定數(shù)據(jù)庫
selectdatabase();
*查詢當(dāng)前使用的數(shù)據(jù)庫名
操作表
6.創(chuàng)建表（同時應(yīng)該創(chuàng)建列）
createtable 表名(列名1 數(shù)據(jù)類型1,列名2 數(shù)據(jù)類型2,.。。);
如果定義varchar 就一定要在后面小括號中 定義長度 varchar(255)
int、double、date(yyyy-MM-dd)、dateTime(yyyy-MM-dd HH:mm:ss)
char、float
復(fù)制一張表
createtable 新表名 like 舊表名;
7.查詢表
showtables;
*查詢當(dāng)前數(shù)據(jù)庫中所有的表
desc 表名;
*查詢當(dāng)前表的結(jié)構(gòu)
8.修改
altertable 表名 renameto 新表名;
*修改表名
altertable 表名 add 列名 類型;
*添加一列
altertable 表名 change 列名 新列名 新類型;
*修改列名
altertable 表名 modify 列名 新類型;
? ? *修改列的類型
9.刪除
altertable 表名 drop 列名;
*刪除表中列
droptable 表名;
*刪除表
droptableifexists 表名;
? ?*先判斷 再刪除
DML：數(shù)據(jù)的增刪改操作 insert ?into ? delete ? ?update
1.添加數(shù)據(jù)
insertinto 表名(列名1,列名2,。。。) values (值1,值2,。。。);
注意：
列名要和值一一對應(yīng)
除了形式為數(shù)字的值 其他都應(yīng)該加''或者""
如果整行添加 可以省略列名 inster into 表名 values(值1,值2,。。。);
如果有自增列
**insertinto 表名 values(null,值2,值3,。。);
insertinto 表名 (列名2,列名3,。。。) values(值2,值3,。。。);
2.刪除操作
deletefrom 表名;
刪除整張表
deletefrom 表名 [where goodName = "筆記本"];
注意：
做刪除操作一定要編寫刪除條件 否則整表刪除
如果確實要刪除整表信息 truncatetable 表名; 刪除整表 重新復(fù)制一張原表結(jié)構(gòu) 效率高
deletefrom 表名 where goodName = "筆記本"orid = 1;
多行刪除
3.修改數(shù)據(jù)
update 表名 set 列名 = 值;
修改所有的行此列的值
update 表名 set 列名 = 值 where 條件;
update goods set goodName = "書包"where goodName = "文具盒";
同時改變多行中某一列的值 可以在條件中添加多行的查詢
? ? ? ?update goods set goodName = "書包"where goodName = "文具盒"orid = 3;
? ? ? ?同時改變多列的值
? ? ? ?update goods set goodName = "書包",price = 20.8 where...
? ? ? ?批量新增
? ? ? ?INSERTINTO
goods
VALUES
? ? ? ? ? ?(2,'鋼筆',5,1)
? ? ? ? ? ?,(3,'作業(yè)本',0.5,1)
? ? ? ? ? ?,(4,'文具盒',10.6,1)
? ? ? ? ? ,(5,'籃球',58.8,2)
? ? ? ? ? ,(6,'羽毛球',3,2)
? ? ? ? ? ?,(7,'羽毛球拍',80,2)
? ? ? ? ? ,(8,'面包',5,3)
? ? ? ? ? ?,(9,'牛奶',4.5,3)
? ? ? ? ? ,(10,'辣條',0.5,3);
DQL:數(shù)據(jù)的查詢操作 ?select
select * from 表名;
* 查詢整張表
selectid,goodName,price from 表名;
* 查詢表中的某些列
select 列1,列2,列3,...from 表名 [where 條件];
* 查詢滿足條件的行中的某些列
-- 查詢中去除列中重復(fù)的數(shù)據(jù)
SELECTDISTINCTtypeFROM goods;
使用去重查詢 不應(yīng)該再和其他列一起操作
-- 如果希望以某個值代替price列中的null
? ?SELECT
? ? ? ?idasID,goodName [as] 商品名,IFNULL(price,0)+2as 價錢
? ?FROM
? ? goods;
DCL：對于數(shù)據(jù)庫用戶權(quán)限的相關(guān)設(shè)置
查詢數(shù)據(jù)庫用戶：
USE mysql;
SELECT * FROM`user`;
新增用戶：
-- 新增數(shù)據(jù)庫管理
CREATEUSER'用戶名'@'localhost'IDENTIFIEDBY'密碼';
修改密碼
UPDATEUSERSETPASSWORD = PASSWORD('333') WHEREUSER = 'abc';
FLUSHPRIVILEGES; -- 刷新操作
-- 簡化方案
SETPASSWORDFOR'abc'@'localhost' = PASSWORD('1234567');
當(dāng)遺忘了root密碼 如何重置root密碼
1.使用管理員方式打開CMD-> net stop mysql 停止mysql服務(wù)
2.使用無驗證方式登入mysql ? ? mysqld --skip-grant-tables
3.打開新的cmd窗口 直接輸入mysql 回車 登入mysql ? ?mysql>
4.use mysql;
5.UPDATE USERSETPASSWORD = PASSWORD('新密碼') WHEREUSER = 'root';
6.關(guān)閉兩個窗口
7.開啟服務(wù) net start mysql
8.使用root及新密碼登陸
刪除用戶
-- 刪除用戶
DROPUSER'用戶名'@'localhost';
權(quán)限管理：
查詢權(quán)限
-- 查詢權(quán)限
SHOWGRANTSFOR'用戶名'@'localhost';
-- 列表式賦權(quán)限
GRANTSELECT,UPDATE,INSERT,DELETEON test1.users TO'abc'@'localhost';
? ? ? -- 列表式移除權(quán)限
? ? ? ?REVOKESELECT,UPDATE,INSERT,DELETEON test1.users FROM'abc'@'localhost';
? ? ? ?-- 擁有所有權(quán)限
? ? ? GRANTALLON *.* TO'abc'@'localhost';
? ? ? ?-- 刪除所有權(quán)限
? ? ? ?REVOKEALLON *.* FROM'abc'@'localhost';
? ? ? ?
? ? ? ?** grant引導(dǎo)賦權(quán)限的語句 ?revoke引導(dǎo)刪除權(quán)限語句
? ? ? ?** 使用列表時賦予的權(quán)限 只能使用列表式刪除 使用all賦予的權(quán)限 只能使用all刪除
約束
約束
概念：對表中的數(shù)據(jù)進行限定，保證數(shù)據(jù)的正確性、有效性、完整性。
分類：
主鍵約束+自增列
非空約束
唯一約束
外鍵約束
主鍵約束：
給表中每一行添加唯一標(biāo)識 一般設(shè)為int類型 進行自增
* createtable 表名(
idint primary key-- 設(shè)置id列為本表的主鍵列
,namevarchar(255) -- 姓名
,stuType int-- 專業(yè)
)
效果：不允許重復(fù) 不能為null ? 非空且唯一
刪除表中的主鍵列
ALTERTABLE students DROP PRIMARY KEY;
在表創(chuàng)建后設(shè)置主鍵列 **設(shè)置前 要保證內(nèi)部已有數(shù)據(jù)是符合要求的
ALTERTABLE students MODIFY stuId int PRIMARY KEY;
添加主鍵 設(shè)置自增 ** 自增列只能添加在主鍵上
&&&& createtable 表名(
idint primary key auto_increament ? -- 將主鍵設(shè)為自增
,namevarchar(255) -- 姓名
,stuType int-- 專業(yè)
)
單獨添加自增
ALTERTABLE students MODIFY stuId int auto_increment;
有主鍵自增的表添加數(shù)據(jù) 自增列的值用NULL代替
? ? ? ?INSERTINTO
? ? ? ? ? ?students
? ? ? ?VALUES(NULL,'王五',1);
? ? ? ?
? ? ? ?刪除自增
? ? ? ?ALTERTABLE students MODIFY stuId int;
非空約束：
? ? 在設(shè)計表時 給表中某些列設(shè)置非空約束
* createtable 表名(
idint primary key-- 設(shè)置id列為本表的主鍵列
,namevarchar(255) notnull-- 姓名 ?設(shè)置非空約束
,stuType int-- 專業(yè)
)
效果：不允許重復(fù) 不能為null ? 非空且唯一
單獨添加非空約束
? ? ALTERTABLE students MODIFY stuName VARCHAR(255) NOTNULL;
? ?
? ? 刪除非空約束
? ? ALTERTABLE students MODIFY stuName VARCHAR(255);
? ? ?唯一約束：
? ? ? ?在設(shè)計表時 給表中某些列設(shè)置唯一約束
? ?* createtable 表名(
idint primary key-- 設(shè)置id列為本表的主鍵列
,namevarchar(255) notnull-- 姓名 ?設(shè)置非空約束
,tel varchar(255) unique-- 電話號碼 設(shè)置唯一約束
,stuType int-- 專業(yè)
)
單獨添加唯一約束
ALTERTABLE students MODIFY stuTel VARCHAR(255) UNIQUE;
ALTERTABLE students ADDUNIQUE(stuTel);
刪除唯一約束 通過刪除這個列的索引(index)來刪除唯一約束
ALTERTABLE students DROPINDEX stuTel;
-- ALTER TABLE students MODIFY stuTel VARCHAR(255); -- 錯誤方式外鍵約束：
至少涉及到兩張表 描述表與表之間列的取值的依賴關(guān)系
? ? ? ?設(shè)置 從表 的某一列的外鍵 指向 主表的主鍵列
? ? * createtable 表名(
idint primary key-- 設(shè)置id列為本表的主鍵列
,namevarchar(255) notnull-- 姓名 ?設(shè)置非空約束
,tel varchar(255) unique-- 電話號碼 設(shè)置唯一約束
,stuType int-- 專業(yè)
,CONSTRAINT 外鍵名稱 FOREIGNKEY(從表列) REFERENCES 主表(主鍵列);
) ? ?
? ? ? ?
-- 設(shè)置從表的某個列的外鍵
ALTERTABLE students ADDCONSTRAINTtype【外鍵名稱】 FOREIGNKEY(stuType) REFERENCES classType(classId);
-- 刪除外鍵
ALTERTABLE students DROPFOREIGNKEY 外鍵名稱;
數(shù)據(jù)庫的設(shè)計
表與表之間的關(guān)系 外鍵約束
* 一對一
一對一的情況一般建議描述在一張表中
例如：學(xué)生和學(xué)號 人和身份證
如何描述一對一的關(guān)系：
任意一方添加外鍵約束 指向另一方的主鍵
（無所謂方向）
* 一對多
建議拆成兩張或更多表
例如：學(xué)生（多）對班級（一） ? ?人（多）對家庭住址（一）
給多表的列添加外鍵 指向一表的主鍵列（必須有方向性）
* 多對多
建議添加第三張中間表
例如：玩家（多）對 游戲（多）
添加一張表 這張表至少包含兩個列 這兩個列分別添加外鍵 指向另外兩張多表的主鍵
（演示）
如何使用設(shè)計軟件來添加外鍵關(guān)系
選擇從表右鍵選擇設(shè)計 在上方選擇外鍵
1.起個外鍵名稱 2.當(dāng)前從表的要做主外鍵的列 3.數(shù)據(jù)庫的名稱 4.主表名 5.主表主鍵 后面是否級聯(lián)
范式（設(shè)計多張表）
概念：設(shè)計數(shù)據(jù)庫時 應(yīng)該遵循的一些規(guī)范 要遵循后面的范式要求 就必須遵循前面的所有范式要求 各種范式呈遞次規(guī)范 滿足越高的范式的數(shù)據(jù)庫 冗余越小 越優(yōu)秀
目前數(shù)據(jù)庫遵循六大范式：第一范式1NF ?第二范式2NF 第三范式3NF 巴斯-科德范式BCNF 第四范式4NF 第五范式5NF ? 123845
第一范式1NF：（各列不可再拆分）
數(shù)據(jù)庫表的每一列都是不可分割的原子數(shù)據(jù)項 而不能是集合 數(shù)組 記錄等非原子數(shù)據(jù)項
第二范式2NF：（每張表應(yīng)該選擇一個合適的主鍵）
非碼屬性必須完全依賴于候選碼（主鍵）（在1NF基礎(chǔ)上消除非主屬性對主碼的部分函數(shù)依賴）
間接依賴到主鍵 滿足二范式
第三范式3NF：（其他列能夠直接依賴于主鍵）
任何非主屬性不依賴于其他的非主屬性（其他列只依賴于主鍵 包括間接依賴都不行）（在1NF和2NF基礎(chǔ)上）
第三范式就是屬性不依賴于其它非主屬性，也就是在滿足2NF的基礎(chǔ)上，任何非主屬性不得傳遞依賴于主屬性。
事務(wù) 一次要執(zhí)行的一系列操作的總稱
通過添加事務(wù) 可以控制這一系列操作的成功或失敗 有一個sql失敗則全部還原到開啟事務(wù)之前 只有全部的sql成功 才會正常提交（提交還是回滾的主動權(quán)在程序員手里）
語法：
STARTTRANSACTION;開啟事務(wù)（在數(shù)據(jù)庫編碼中）
事務(wù)中的一系列操作
如果其中的操作都符合需求 則 可以使用COMMIT; 真正的去正常提交更新
如果其中的操作有不符合需求的 則可以使用ROLLBACK;使數(shù)據(jù)回滾到開啟事務(wù)之初。
事務(wù)提交的兩種方式：
自動提交：MySql 每條SQL就是一個事務(wù) 默認開啟和提交
手動提交：Oracle 需要顯式開啟事務(wù)和提交事務(wù)
查詢事務(wù)提交方式：
select @@autocommit;
1:自動提交
0:手動提交
SET @@autocommit =0;-- 設(shè)置提交方式為手動提交
SELECT @@autocommit;-- 查詢提交方式
驗證：直接執(zhí)行修改語句 會發(fā)現(xiàn)數(shù)據(jù)庫中數(shù)據(jù)并沒有被修改
只能通過添加事務(wù)及提交操作 才能實現(xiàn)修改
自動提交的SQL 只需要添加事務(wù)及提交事務(wù) 即可變?yōu)槭謩犹峤?不需要單獨修改提交方式
事務(wù)四大特性
1.原子性：我們定義好的一個事務(wù) 就應(yīng)該是不可分割的最小操作單位 要么同時成功 要么同時失敗
2.持久性：只有當(dāng)事務(wù)執(zhí)行了commit或者rollback后 數(shù)據(jù)才會持久的保存在數(shù)據(jù)庫中
3.隔離性：事務(wù)與事務(wù)之間是相互獨立的（理想狀態(tài)）
4.一致性：事務(wù)操作前后 數(shù)據(jù)總量是不變的 1》不可重復(fù)讀 2》數(shù)據(jù)完整性
讀取異常：查詢 當(dāng)一方開啟事務(wù)操作數(shù)據(jù)后 沒有提交或者回滾之前 查詢到的數(shù)據(jù)和真實數(shù)據(jù)是不同的 這就造成了讀取異常。
1.臟讀:一個事務(wù)讀取到另一個事務(wù)還沒有保存的數(shù)據(jù)
2.不可重復(fù)讀：在同一個事務(wù)中兩次讀取到的信息不一致
3.幻讀：一個事務(wù)在操作增刪改（DML）數(shù)據(jù)時 另一個事務(wù)添加了一條數(shù)據(jù) 則第一個事務(wù)查詢不到自己的修改 出現(xiàn)幻覺
事務(wù)隔離級別：
1.read uncommited 讀未提交
問題：臟讀、不可重復(fù)讀、幻讀
2.read commited 讀已提交 （oracle默認隔離級別）
問題：不可重復(fù)讀、幻讀
3.repeatable read 可重復(fù)讀 （MySql默認隔離級別）
問題：幻讀 ?（暫不演示）
4.serializable 串行化
解決以上所有問題 暫停其他事務(wù) 效率最低
查看隔離級別
select @@tx_isolation;
設(shè)置隔離級別
set global transaction isolation level 隔離級別名稱;
修改完隔離級別后 要重新創(chuàng)建連接
鎖
悲觀鎖
事務(wù)中的業(yè)務(wù)大概率會出現(xiàn)并發(fā)異常時 我們處于一種悲觀狀態(tài) 可以使用悲觀鎖
先關(guān)閉默認提交模式
SET @@autocommit =0;-- 設(shè)置提交方式為手動提交
STARTTRANSACTION;
-- 加鎖
select money from`Users`whereid = 1forupdate;
update`Users`set money =50000whereid = 1 ;
-- 在關(guān)閉這個事務(wù)之前 其他的事務(wù)依然查詢的是數(shù)據(jù)庫中真實的數(shù)據(jù) （沒有臟讀）
-- 對比事務(wù)隔離級別serializable 在第二個事務(wù)中查詢也會被暫停 而使用悲觀鎖 第二個事務(wù)是可以查詢的
-- 如果其他事務(wù)使用的是DML（增刪改）操作 會被暫停 出于等待 直到第一個事務(wù)提交或者回滾 其他事務(wù)才能進行（DML）
樂觀鎖
評估事務(wù)中發(fā)生并發(fā)異常的可能性比較低 但又不完全保證不會發(fā)生 使用樂觀鎖
樂觀鎖是通過SQL本身的語法去進行校驗
-- 在修改之前查詢一下數(shù)據(jù) 在修改的同時回去判斷這個值和剛才查詢到的數(shù)據(jù)是否一致 如果一致則繼續(xù)執(zhí)行更新 否則不更新
? ? ? ? ? ?
? ? ? ? ? ?SELECT MONEY FROMUSERSWHEREID = 1;
? ? ? ? ? ?UPDATEUSERSSET MONEY = 500WHEREID = 1AND MONTY = 前面語句查出來的值;
合成一句
UPDATE`Users`SET money = 500
WHEREid = 1
AND`Users`.money = (SELECT m1 FROM (SELECT money AS m1 FROM`Users`WHEREid = 1 ) AS t1);
Every derived table must have its own alias 給虛擬表命名
總結(jié)：
1、對數(shù)據(jù)庫要有一定的認知：關(guān)系型數(shù)據(jù)庫和非關(guān)系型數(shù)據(jù)庫
2、數(shù)據(jù)庫的四大類型語言要有一定的了解
1DDL(Data Definition Language)數(shù)據(jù)定義語言
用來定義數(shù)據(jù)庫對象：數(shù)據(jù)庫 表 列 Create
2DML(Data Manipulation Language)數(shù)據(jù)操作語言
對于數(shù)據(jù)的增刪改 ?insert delete update
3DQL(Data Query Language)數(shù)據(jù)操作語言
對于數(shù)據(jù)的查詢 select
4DCL(Data Control Language)數(shù)據(jù)控制語言
定義數(shù)據(jù)庫訪問權(quán)限 定義新的用戶及權(quán)限 密碼等等
Grant
3、在設(shè)計數(shù)據(jù)庫的時候要應(yīng)用：約束和范式，考慮表與表之間的關(guān)系，是一對一、一對多、多對多
怎么加索引才合適、外鍵等，先有一定的設(shè)計才去建表創(chuàng)建屬性
4、在數(shù)據(jù)庫的層面要對事務(wù)一定的認知和理解
要知道什么是事務(wù)隔離級別、什么是臟讀、不可重復(fù)讀、幻讀和串行化，這也是面試常常被問到的。
5、對數(shù)據(jù)庫的鎖要有自己的理解：悲觀鎖、樂觀鎖
事務(wù)中的業(yè)務(wù)大概率會出現(xiàn)并發(fā)異常時 我們處于一種悲觀狀態(tài) 可以使用悲觀鎖
評估事務(wù)中發(fā)生并發(fā)異常的可能性比較低 但又不完全保證不會發(fā)生 使用樂觀鎖
