資料庫

1.概述

資料庫DB（DataBase）：資料庫是“按照資料結構來組織、存盤和管理資料的倉庫”，是一個長期存盤在計算機內的、有組織的、可共享的、統一管理的大量資料的集合，簡單的說就是用來存盤和管理資料的倉庫
分類：
關系型資料庫：存盤的格式可以直觀的反映物體之間的關系，常見的Mysql，SqlServer、Oracle
非關系型資料庫：指的是分布式的、非關系型的、不保證遵循ACID原則的資料存盤系統，常見的有NoSQL、Redis

2.Mysql資料庫的安裝

服務器安裝：埠號默認使用3306，字符集默認使用Latin1（最好使用utf-8），密碼使用root（一般）
客戶端安裝：sqlYog等軟體
客戶端有兩種：DOS視窗/可視化工具
檢驗：

3.Mysql資料庫的使用

客戶端連接服務器，使用服務器里的資料

4.資料庫的結構

資料庫的結構：資料庫–>表–>記錄（行，列（欄位）組成的）

SQL陳述句

全稱是結構化的查詢陳述句，對資料庫里的資料進行各種操作，常見操作就是增刪改查CRUD
CRUD操作：新增Create、查詢Retrieve、修改Update、洗掉Delete
結構化查詢語言是一種資料庫查詢和程式設計語言，用于存放資料以及查詢、更新和管理關系資料庫系統；同時也是資料庫腳本檔案的擴展名，

1.分類

DML（Data Manipulation Language）資料操作語言，是SQL語言中，負責對資料庫物件運行資料訪問作業的指令集，以INSERT、UPDATE、DELETE三種指令為核心，分別代表插入、更新與洗掉，
DDL：（Data Definition Language）資料庫定義語言，創建表，創建庫
是SQL語言集中，負責資料結構定義與資料庫物件定義的語言，由CREATE、ALTER與DROP三個語法所組成
DCL：（Data Control Language）資料庫控制語言，細粒度的管理操作資料庫的權限
在SQL語言中，是一種可對資料訪問權進行控制的指令，它可以控制特定用戶賬戶對資料表、查看表、預存程式、用戶自定義函式等資料庫物件的控制權，由 GRANT 和 REVOKE 兩個指令組成，DCL以控制用戶的訪問權限為主，GRANT為授權陳述句，對應的REVOKE是撤銷授權陳述句，
DQL：（Data Query Language）資料庫的查詢語言
是SQL語言中，負責進行資料查詢而不會對資料本身進行修改的陳述句，這是最基本的SQL陳述句，保留字SELECT是DQL（也是所有SQL）用得最多的動詞
注意：SQL不區分大小寫

2.常用的操作

資料庫操作

查看所有資料庫：show databases;
使用資料庫：use 庫名；
創建資料庫：create database 庫名；
洗掉資料庫：drop database 庫名；

表的操作

查看表的結構：desc 表名；
查看表：先指定使用的庫：use 庫名;然后執行show tables;
創建表：create table 表名(欄位名 欄位型別(長度)，欄位2,欄位3…);
修改表：alter table 表名 add column欄位名 欄位型別(欄位長度) where 條件;
洗掉表：drop table 表名;

欄位的操作

查詢:select * from 表名;select 欄位1，欄位2…from 表名;
創建：insert into 表名 values(欄位1的值，欄位2的值…);
注意;值的個數必須與表里欄位的個數一致，值的型別要相互對應
先解決中文亂碼問題，否則會報錯，解決方案：set names gbk;（固定格式）

修改：update 表名 set 欄位名=新值;–修改所有 （可以加where條件進行限制）
注意：如果是字串型別就要加’’或””

洗掉：delete from 表名;–洗掉表里的所有資料

3.資料型別

命名規則

1.欄位名必須以字母開頭，盡量不要使用拼音
2.長度不能超過30個字符（不同資料庫，不同版本會有不同）
3.不能使用SQL的保留字，如where,order,group
4.只能使用如下字符az、AZ、0~9、$ 等
5.Oracle習慣全大寫：USER_NAME，mysql習慣全小寫：user_name
6.多個單詞用下劃線隔開，而非java語言的駝峰規則

字符

char長度固定，不足使用空格填充，最多容納2000個字符，char(11)存盤abc，占11位，查詢速度極快但浪費空間
varchar變長字串，最多容納4000個字符，varchar(11)存盤abc，只占3位，查詢稍慢，但節省空間，Oracle為varchar2
大文本: 大量文字（不推薦使用，盡量使用varchar替代）
以utf8編碼計算的話，一個漢字在u8下占3個位元組

注：不同資料庫版本長度限制可能會有不同

數字

tinyint,int整數型別
float,double小數型別
numberic(5,2) decimal(5,2)—也可以表示小數,表示總共5位,其中可以有兩位小數
decimal和numeric表示精確的整數數字

日期

date 包含年月日
time時分秒
datetime包含年月日和時分秒
timestamp時間戳，不是日期，而是從1970年1月1日到指定日期的毫秒數

圖片

blob 二進制資料，可以存放圖片、聲音，容量4g，早期有這樣的設計，但其缺點非常明顯，資料庫龐大，備份緩慢，這些內容去備份多份價值不大，同時資料庫遷移時過大，遷移時間過久，所以目前主流都不會直接存盤這樣的資料，而只存盤其訪問路徑，檔案則存放在磁盤上，

4.欄位約束

1.主鍵約束（primary key）：欄位值必須唯一且不能為null,通常情況下，每張表都會有主鍵，
添加主鍵約束，例如將id設定為主鍵
表設計的原則是：表必須有主鍵，作為每條記錄的唯一標識
主鍵自增策略：auto_increment,通常會使用自增策略，主鍵的值交給資料庫維護管理（從1開始，++）
2.非空約束：（not null）如果為一個列添加了非空約束，那么這個列的值就不能為空，但可以重復，
3.唯一約束：（unique）如果為一個列添加了唯一約束，那么這個列的值就必須是唯一的（即不能重復），但可以為空，
添加唯一約束，例如為username添加唯一約束及非空約束：
4.默認約束default，給指定欄位設定默認值
CREATE TABLE f(
id INT PRIMARY KEY AUTO_INCREMENT,
sex VARCHAR(10) DEFAULT ‘男’,#設定默認值
age INT DEFAULT 18
);
5.外鍵約束foreign key
#外鍵約束：foreign key,通過指定的sql陳述句描述兩張表的關系
#約束：子表添加記錄時id必須取自主表
#洗掉主表記錄時，必須沒有被子表用著
#怎么確定子表或者主表？子表中有外鍵的SQL
CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
sex VARCHAR(10)
);
CREATE TABLE tb_user_address(
user_id INT PRIMARY KEY AUTO_INCREMENT,
address VARCHAR(20),
#表明和那張表的那個欄位有關系
#foregin key(本表的主鍵)references 關聯表（主鍵）
FOREIGN KEY(user_id) REFERENCES tb_user(id)
);
6.檢查約束 check
#檢查約束：check給指定欄位設定合法值的檢查約束
CREATE TABLE g(
id INT PRIMARY KEY AUTO_INCREMENT,#主鍵，自動遞增
age INT,
CHECK(age>0 AND age<200)#設定檢查
);

基本函式

常用的
lower:資料全轉小寫
upper :資料全轉大寫
length:資料的長度
substr :截取字串#substr(1,2,3)包含開始位置(截取欄位名-1，開始位置-2，截取長度)
concat:拼接字串，可以有多個引數
replace:替換，#replace(要替換的欄位,原來的字符,新的的字符)
ifnull:判斷是否為null，null不參與資料的運算，例如：30000+null=null
30000+ifnull(欄位,0)=30000
#ifnull 判斷是否為null,null不參與資料的運算
#如果為null替換成0
round：（四舍五入）
ceil：（向上取整）
floor：（向下取整）
#round四舍五入 & ceil向上取整 & floor向下取整
SELECT comm ,ROUND(comm),CEIL(comm),FLOOR(comm)FROM emp;

Now()–獲取當前的日期：年月日時分秒
Curdate()–獲取年月日
Curtime()–獲取時分秒
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW())
,SECOND(NOW());
Year年/month月/day日/hour時/minute分/second秒
Select UUID();

轉義字符：\ 例如查詢xi’an select ‘xi\’an’;

#練習--函式
#UPPER全轉大寫 LOWER全轉小寫
SELECT ename,UPPER(ename),LOWER(ename) FROM emp;
#length求長度(一個字母算一個字符,一個漢字算三個字符)
SELECT ename,LENGTH(ename),LENGTH(job) FROM emp;
#concat(1,2,3)拼接字串-1是欄位名2和3都是要拼的內容
SELECT CONCAT(ename,'123','上班族') FROM emp;
#查部門名稱
SELECT dname FROM dept;
#substr(1,2,3)截取字串-1是欄位名2是開始位置(數欄位里的個數，從1開始，與下標無關)3是總長度
SELECT dname,SUBSTR(dname,2,2) FROM dept;
#replace(1,2,3)替換-1是欄位名2是要被替換的3是新的資料
SELECT dname,REPLACE(dname,'e','888') FROM dept;
#ifnull(1,2)判斷是否為null,如果是null就替換成0-1是欄位名2是要替換的值
SELECT sal,SUM(sal+IFNULL(comm,0))salary FROM emp  
GROUP BY empno;
#round四舍五入/ceil向上取整/floor向下取整
SELECT comm,ROUND(comm) FROM emp;
SELECT comm,CEIL(comm)FROM emp;
SELECT comm,FLOOR(comm)FROM emp;
#now/CURDATE/CURTIME
SELECT NOW();#年月日時分秒
SELECT CURDATE();#年月日
SELECT CURTIME();#時分秒
#year/month/day/hour/minute/second
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),
MINUTE(NOW()),SECOND(NOW());

條件查詢

Distinct:使用distinct關鍵字，去除重復的記錄行
Where:滿足條件的才查出來，先過濾，再查詢
注意：where不能使用列別名
多條件查詢：
Where and:同時滿足兩個條件
Where or:滿足其中一個條件即可
select * from emp where 1=1 --類似沒條件

select * from emp where 1=0 --條件不成立

select * from emp where empno=100 --唯一條件

select * from emp where ename=‘tony’ and deptno=2 --相當于兩個條件的&關系

select * from emp where ename=‘tony’ or deptno=1 --相當于兩個條件的|關系

select name, sal from emp where sal=1400 or sal=1600 or sal=1800;
in()：在滿足條件里，相當于or
select name, sal from emp where sal in(1400,1600,1800);
select name, sal from emp where sal not in(1400,1600,1800);
Like:模糊查詢
通配符%代表0到n個字符，通配符下劃線_代表1個字符
Null：is null：查詢是null的，過濾欄位值為空的
Is not null：查詢不是null的，過濾欄位值不為空的
Between and：介于…到…之間 （包含區間值）
Limit:分頁查詢
select * from emp limit 2 --列出前兩條
select * from emp limit 1,2 --從第二條開始,展示2條記錄
select * from emp limit 0,3 --從第一條開始,展示3條記錄–前三條
#第一個數字是開始位置（從n+1條資料開始）第二條資料是要列出的條數
Order by:排序
SELECT * FROM emp ORDER BY sal;#默認升序（ASC）
SELECT * FROM emp ORDER BY sal DESC;#desc倒序（降序）

#練習--查詢
#查詢部門地址
SELECT loc FROM dept;
#DISTINCT用來給結果去重distinct
SELECT DISTINCT loc FROM dept;

#where滿足條件的才查出來
#查詢deptno等于1的部門記錄
SELECT * FROM dept WHERE deptno=1;
#查詢地址在一區的部門記錄\
SELECT * FROM dept WHERE loc='一區';
#查詢地址在二區的部門名稱
SELECT dname FROM dept WHERE loc='二區';
#根據名稱查詢部門記錄
SELECT * FROM dept WHERE dname='operations';
#根據名稱和地址 查詢部門記錄
SELECT * FROM dept WHERE dname='operations' AND loc='二區';
#WHERE loc='二區' and dname='research2'#并且關系
#或者關系
SELECT * FROM dept WHERE dname='operations' OR loc='二區';

#查詢工資>8000的員工資訊
SELECT * FROM emp WHERE sal>8000;
#查詢工資是8000的或者工資是3000的員工資訊
SELECT * FROM emp WHERE sal=8000 OR  sal=3000;
# sal=8000 or sal=3000
 #作用意義同上
SELECT * FROM emp WHERE sal IN(8000,3000);

#like 像,模糊查詢,通常配合%作為占位符
#查詢名字里包含a的員工資訊
SELECT * FROM emp WHERE ename LIKE '%a%';
#ename like '%a%' #包含a,動態匹配0~n個字符
#以l開頭,動態匹配0~n個字符

#查詢mgr是null的員工資訊
SELECT * FROM emp WHERE mgr IS NULL;
#查詢sal在3000到10000內的員工資訊
SELECT * FROM emp WHERE sal BETWEEN 3000 AND 10000;
#查詢2017年到2019年入職的員工資訊
SELECT * FROM emp WHERE YEAR(hiredate) BETWEEN 2017 AND 2019;
#limit分頁
SELECT * FROM emp LIMIT 3;#查詢前三條資料
SELECT * FROM emp LIMIT 1,3;#查詢從第二個資料開始，并且查詢3條資料
#order by 排序
SELECT * FROM emp ORDER BY sal;#默認升序(ASC)
SELECT * FROM emp ORDER BY sal DESC;#倒序

聚合函式（5個）

把一列的值全部取出來，聚合起來，分析最大值，最小值，平均值，求和，求個數
常見的聚合函式：max() min() avg() sum() count()
聚合列與非聚合列不能同時使用
SELECT sal,SUM(sal),AVG(sal) FROM emp; 錯誤
#count()求總個數
SELECT COUNT(comm) FROM emp;#不統計null–低效
SELECT COUNT(*)FROM emp;#都統計–低效
SELECT COUNT(1)FROM emp;#都統計–高效

分組函式

group by:用于對查詢結果進行分組統計
having ：分完組之后再進行過濾
什么時候必須分組？
查詢的結果中出現聚合列和非聚合列，就必須要分組
按照什么分組合理？
通常按照非聚合列分組
什么是聚合列？使用了聚合函式max min count sum avg

SELECT job,MAX(sal)FROM emp 
WHERE MAX(sal)>8000 #先過濾再分組高效，但是where里不能用聚合函式
GROUP BY job
#HAVING MAX(sal)>8000 
ORDER BY MAX(sal) ;#升序排序

多表連接

業務需求：一張表已經無法滿足業務需求，需要兩張表或者多張表進行關聯

笛卡爾積

方式一：笛卡爾積：必須描述兩張表的關聯關系，不然會造成冗余

#方式一：笛卡爾積，
SELECT * FROM emp,dept;#查出所有資料
SELECT * FROM emp,dept
#select * from emp,dept WHERE 兩張表的關聯關系;
#表名.欄位名
WHERE dept.deptno=emp.deptno AND dept.dname='accounting';

join

方式二：join
內連接：inner join 兩邊都滿足的交集
左連接：left join 是以左表為基礎，根據ON后給出的兩表的條件將兩表連接起來，結果會將左表所有的查詢資訊列出，而右表只列出ON后條件與左表滿足的部分，左連接全稱為左外連接，是外連接的一種，
右連接：right join 是以右表為基礎，根據ON后給出的兩表的條件將兩表連接起來，結果會將右表所有的查詢資訊列出，而左表只列出ON后條件與右表滿足的部分，右連接全稱為右外連接，是外連接的一種，

#inner join/left join/right join
#作業中，常用的是小表 left join 大表，小表驅動大表
#inner join(內連接)#兩邊都滿足的交集
SELECT * FROM emp e
INNER JOIN dept d #兩個表的關聯
ON e.deptno=d.deptno#描述欄位
WHERE d.dname='accounting';
#left join(左連接)左邊的所有和右邊滿足了的
SELECT * FROM emp e
LEFT JOIN dept d #兩個表的關聯
ON e.deptno=d.deptno#描述欄位
WHERE d.dname='accounting';
#right join（右連接）右邊的所有和左邊滿足了的
SELECT * FROM emp e
RIGHT JOIN dept d #兩個表的關聯
ON e.deptno=d.deptno#描述欄位
WHERE d.dname='accounting';

子查詢

方式三：子查詢
將查詢的結果作為條件，即當一個查詢是另一個查詢的條件時，稱之為子查詢，
可以允許多層嵌套查詢

#練習1:查詢李軍的總得分
#笛卡爾積
SELECT SUM(degree) FROM scores s1,students s2
WHERE s1.sno=s2.`sno`
AND s2.sname='李軍';
#join
SELECT SUM(degree)FROM scores s1
LEFT JOIN students s2
ON s1.sno=s2.sno
WHERE s2.sname='李軍';
#子查詢
SELECT SUM(degree) FROM scores s1 WHERE sno=(
SELECT sno FROM students WHERE sname='李軍'
);
#練習2:查詢易天負責的課程名稱
#笛卡爾積
SELECT cname FROM courses c,teachers t
WHERE c.tno=t.tno
AND t.tname='易天';
#join
SELECT cname FROM courses c
LEFT JOIN teachers t
ON c.tno=t.tno 
WHERE t.tname='易天';
#子查詢
SELECT cname FROM courses c WHERE tno=(
SELECT tno FROM teachers t
WHERE t.tname='易天'
);

事務（Transaction）

1.概述

在關系資料庫中，一個事務可以是一條SQL陳述句，一組SQL陳述句或整個程式，
保證SQL陳述句要么全執行成功，要么全執行失敗

2.事務的特性

有四個特性ACID
A是原子性：是指多條SQL是一個院子，密不可分，如果正確，就操作了資料；如果有錯誤，就會發生回滾，回到事務執行之前，不會結束在中間的某個環節
C是一致性：保證了資料的一致性和完整性
在事務開始之前和事務結束以后，資料庫的完整性沒有被破壞，這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及后續資料庫可以自發性地完成預定的作業，
I是隔離性：保證多執行緒并發時的資料安全，多個操作之間是被隔離的
一個事務的執行不能被其他事務干擾，即一個事務內部的操作及使用的資料對并發的其他事務是隔離的，并發執行的各個事務之間不能互相干擾，
D是持久性：是指資料CDU的影響是持久生效的
持久性也稱永久性（permanence），指一個事務一旦提交，它對資料庫中資料的改變就應該是永久性的，接下來的其他操作或故障不應該對其有任何影響，

3.事務的隔離級別

隔離級別:讀未提交 讀已提交 可重復讀 串行化
從前往后,性能越來越差,安全性越來越高.MySQL默認是可重復讀

讀未提交（Read uncommitted） 安全性最差,可能發生并發資料問題,性能最好
讀提交（read committed） Oracle默認的隔離級別
可重復讀（repeatable read）MySQL默認的隔離級別,安全性較好,性能一般
串行化（Serializable） 表級鎖，讀寫都加鎖，效率低下,安全性高,不能并發

MySQL的隔離級別說明

在默認情況下，MySQL每執行一條sql陳述句，都是一個單獨的事務，如果需要在一個事務中包含多條sql陳述句，那就需要手動開啟事務和結束事務

開啟事務：start transaction；
結束事務：commit（提交事務）或rollback（回滾事務）
在執行sql陳述句之前，先執行start transaction，這就開啟了一個事務（事務的起點），然后可以執行多條sql陳述句，最后要結束事務，commit表示提交，即事務中的多條sql所做出的影響會持久到資料庫中，或者rollback，表示回滾，即回滾到事務的起點，之前所做的所有操作都被撤銷了！

索引（index）

作用

好處：
索引是資料庫優化
表的主鍵會默認自動創建索引
每個欄位都可以被索引
大量降低資料庫的IO磁盤讀寫成本，極大提高了檢索速度
索引事先對資料進行了排序，大大提高了查詢效率
為了提高資料庫的查詢效率，可以使用索引
那么給那些欄位設計索引呢？查詢需要量比較大，欄位值比較大的時候，，
壞處：索引本身也是一張表，表里的資料和真正的表里的資料是重復的，浪費了空間
雖然索引大大提高了查詢的速度，但對資料的增、刪、改的操作需要更新索引表資訊，如果資料量非常巨大，更新效率就很慢,因為更新表時,MySQL不僅要保存資料,也要保存一下索引檔案
隨著業務的不斷變化，之前建立的索引可能不能滿足查詢需求，需要消耗我們的時間去更新索引

定義

索引是一種排好序的快速查找的資料結構，它幫助資料庫高效的進行資料的檢索，在資料之外，資料庫系統還維護著滿足特定查詢演算法的資料結構（額外的存盤空間），這些資料結構以某種方式指向資料，這樣就可以在這些資料結構上實作高效的查找演算法，這種資料結構就叫做索引

一般來說索引本身也很大，不可能全部存盤在記憶體中，因此往往以索引檔案的 形式存放在磁盤中，目前大多數索引都采用BTree樹方式構建

分類

單值索引：一個索引只包括一個列，一個表可以有多個列
唯一索引：索引列的值必須唯一，但允許有空值；主鍵會自動創建唯一索引
復合索引：一個索引同時包括多列

索引的操作

查看索引

#查看索引，主鍵會自動創建索引
#show index from 表名;
show index from dept;

創建索引

#創建單值索引
#create index 索引名字 on 表名(欄位名); #創建索引
create index loc_index on dept(loc); #創建索引
#創建唯一索引
#alter table 表名 add unique(欄位);
alter table emp add unique(ename);
#創建復合索引
#alter table 表名 add index 索引名(欄位1，欄位2）;
Alter table emp add index many_index(ename,job,hiredate);

洗掉索引

#alter table 表名 drop index 索引名;
Alter table emp drop index many_index;

使用索引

explain#用來觀察SQL的執行計劃，主要看有沒有用索引
#（觀察兩列結果：key和possible）
SELECT * FROM emp WHERE ename=‘leo’;#背后會用ename的索引

視圖（view）

視圖是指計算機資料庫中的視圖，是一個虛擬表，其內容由查詢定義，同真實的表一樣，視圖包含一系列帶有名稱的列和行資料，但是，視圖并不在資料庫中以存盤的資料值集形式存在，行和列資料來自由定義視圖的查詢所參考的表，并且在參考視圖時動態生成，
簡單的說，視圖就是由一個或多個表組成的虛擬表

視圖的作用

• 簡單性，看到的就是需要的，視圖不僅可以簡化用戶對資料的理解，也可以簡化他們的操作，那些被經常使用的查詢可以被定義為視圖，從而使得用戶不必為以后的操作每次指定全部的條件，
• 安全性，通過視圖用戶只能查詢和修改他們所能見到的資料，但不能授權到資料庫特定行和特定的列上，通過視圖，用戶可以被限制在資料的不同子集上：
  使用權限可被限制在另一視圖的一個子集上，或是一些視圖和基表合并后的子集上，
• 邏輯資料獨立性，視圖可幫助用戶屏蔽真實表結構變化帶來的影響，

創建視圖

Create view 視圖名 as 查詢陳述句

使用視圖

SELECT * FROM emp_view;
#直接查的視圖，視圖就是一張特殊的表，最好用于查詢

SQL優化

1.盡量使用欄位名替換*
2.做表設計時，欄位的型別最好是varchar代替char
3.欄位里的值盡量數字代替字串
4.盡量把過濾條件精細，能用and不用or
5.索引的設計，最多5個，不能太多
6.模糊查詢，盡量要確定開始元素，讓索引生效
7.查詢盡量避免回傳大量資料
8.避免在where子句中使用！=或<>運算子
9.去重distract過濾欄位要少
10.where中使用默認值代替null
11.不要有超過5個以上的表連接
12.多表連接時盡量是小表驅動大表
13.盡量使用union all替代union
14.洗掉冗余和重復的索引