cgb2109-day03

一,MySQL資料的備份

–1,用工具直接備份就行

1,如果是SQL,直接ctrl s保存就行
2,也可以把表或者資料庫一整個匯出–右鍵–匯出–以SQL轉儲檔案…

–2,練習

#練習SQL陳述句:
#練習1:查詢部門地址在二區的部門名稱
SELECT dname FROM dept WHERE loc='二區'
#練習2:查詢部門地址在二區名字里有o的部門編號
SELECT deptno FROM dept WHERE 
loc='二區' AND dname LIKE '%o%'
#練習3:查詢名字以l開頭的員工崗位并去重
SELECT DISTINCT job FROM emp WHERE ename LIKE 'l%'
#練習4:查詢15年到19年入職的員工姓名和崗位
SELECT ename,job FROM emp WHERE 
#year(hiredate)>=2015  and YEAR(hiredate)<=2019
hiredate>='2015-1-1' AND hiredate<='2019-12-31'
#練習5:查詢副總的年薪
SELECT sal*12+IFNULL(comm,0)*12 FROM emp WHERE job='副總'

二,條件查詢

–1,null

#1.null的資料用is /is not關鍵字來過濾
#練習1:查詢沒有獎金的員工資訊
SELECT * FROM emp WHERE comm IS NULL
#練習2:查詢有獎金的員工資訊
SELECT * FROM emp WHERE comm IS NOT NULL

–2,between and

#練習3:查詢工資5000~10000的員工資訊
SELECT * FROM emp WHERE 
#sal>=5000 and sal<10000
sal BETWEEN 5000 AND 10000 #[5000,10000]
#練習4:查詢2019年入職的員工姓名
SELECT ename FROM emp WHERE 
#hiredate between '2019-1-1' and '2019-12-31'
YEAR(hiredate)=2019

–3,limit分頁

#練習5:展示前兩條員工資料
SELECT * FROM emp LIMIT 2 #只取前n條
SELECT * FROM emp LIMIT 0,2 #從0+1的資料開始,總共展示2條
#練習6:查詢崗位是員工的第一條記錄
SELECT * FROM emp WHERE job='員工' LIMIT 0,1#limit通常放最后 

–4,order by

#4.order by排序,按照字典順序排,默認是升序ASC
#練習7:按照工資排序
SELECT * FROM emp ORDER BY sal DESC #按數值降序
#練習8:按照名字排序
SELECT * FROM emp ORDER BY ename DESC #按字母降序
#練習9:按照崗位排序
SELECT * FROM emp ORDER BY job ASC #按漢字對應的編號升序
#練習10:查詢15年到19年入職的員工資訊只取前兩條并按照工資升序排
SELECT * FROM emp WHERE 
YEAR(hiredate) BETWEEN 2015 AND 2019 #區間[2015,2019]
ORDER BY sal #排序,默認的升序
LIMIT 2 #分頁,只取前兩條,通常放最后

–5,統計案例

#統計需求
#練習1:統計每個員工的年薪
SELECT sal,comm,sal*16+IFNULL(comm,0)*16 FROM emp
#練習2:統計19年以前入職的員工資訊
SELECT * FROM emp WHERE 
YEAR(hiredate)<2019

三,聚合函式

–1,概述

基礎函式:
upper lower length concat substr replace ifnull now()/date()/year()…
聚合函式:
把一個欄位的值聚合起來,進行獲取最大值,最小值,平均數,求和,求個數…
max() min() sum() avg() count()

–2,測驗

#聚合函式max min sum avg count
     #最大值     最小值   求和    求平均數  
SELECT MAX(sal),MIN(sal),SUM(sal),AVG(sal) FROM emp 
#求總記錄數
SELECT COUNT(*) FROM emp#低效
SELECT COUNT(1) FROM emp#高效
SELECT COUNT(sal) FROM emp#了解,不推薦
SELECT COUNT(comm) FROM emp#按照欄位統計時不統計null
#練習1:統計名字里包含a的總記錄數
SELECT COUNT(1) FROM emp WHERE ename LIKE '%a%'
#練習2:統計19年入職的員工里的最高薪
SELECT MAX(sal) FROM emp WHERE YEAR(hiredate)=2019
#練習3:統計2號部門的平均薪資
SELECT AVG(sal) FROM emp WHERE deptno=2
#練習4:統計2號部門一年的工資支出總數
SELECT SUM(sal)*12 FROM emp WHERE deptno=2

四,分組

把表里的所有資料 按照 合理的維度 分成不同的組

–1,group by

#分組:使用group by 
#練習1:統計每個部門的平均薪資
SELECT AVG(sal),deptno FROM emp 
GROUP BY deptno #按照部門分組
#練習2:統計每個部門的最高薪資
SELECT MAX(sal),deptno FROM emp 
#口訣1:查詢結果中出現了混合列,包括著聚合列和非聚合列,必須分組
GROUP BY deptno
#口訣2:通常按照非聚合列分組
#練習3:統計每年入職的總人數
SELECT YEAR(hiredate),COUNT(1) FROM emp 
GROUP BY YEAR(hiredate)
#練習4:統計每個崗位的平均薪資
SELECT AVG(sal),job FROM emp
GROUP BY job

–2,having

把分組后的資料還想繼續過濾,使用group by的固定搭配having

#having
#練習5:統計每個崗位的平均薪資,而且只要>=10000的
SELECT AVG(sal) a,job FROM emp
#where AVG(sal)>=10000 
#注意:::where里不能用別名,也不能出現聚合函式
GROUP BY job  #按照非聚合列分組
HAVING a>=10000 #分組后的過濾,a是別名
#練習6:統計每年入職的人數,而且只要19年的
SELECT YEAR(hiredate) ,COUNT(1) FROM emp
WHERE YEAR(hiredate)=2019 
GROUP BY YEAR(hiredate) #分組
#having a=2019 #分組后的過濾
# where比having 高效,因為執行時機要早一些

五,事務

–1,概述

1,資料庫事務用來保證 多個SQL 要么全成功,要么全失敗.英文叫transaction.
MySQL資料庫會自動管理事務,Oracle資料庫需要程式員管理事務.
MySQL也允許程式員手動管理事務
2,事務的四大特征:ACID
原子性: 把多條SQL看做一個原子密不可分,要么全成功,要么全失敗
一致性: 保證資料守恒,將來廣泛的應用到分布式系統里
隔離性: MySQL資料庫可以支持高并發,可能會有資料安全的隱患,也有復雜的隔離級別
持久性: 對資料庫的操作增刪改,有持久影響
3,隔離級別:
read uncommitted讀未提交:性能好,但是安全性差
read commited讀已提交:是Oracle資料庫默認的隔離級別,性能較差,但是安全性較好
repeatable read可重復讀:是MySQL資料庫默認的隔離級別,性能再差一點,但是安全性特別好
Serializable串行化:性能非常差,但是安全性比較好
也可以查:SELECT @@tx_isolation;

–2,測驗

視窗1:

mysql> use  cgb210901; #使用資料庫,準備往表里插入資料
mysql> start transaction; #開啟事務start transaction
mysql> insert into g values(null,'jack',20);#多條執行增刪改的SQL
mysql> commit;#提交事務commit,不提交的話別人查不到的,沒有持久影響

視窗2:

mysql> use cgb210901;
mysql> select * from g; #視窗1不執行commit,這里永遠查不到的