cgb2111-day03

一,條件查詢

–1,order by

#練習:條件查詢CRUD
#練習1:修改1號部門的名稱和地址
UPDATE dept SET dname='java開發部',loc='北京'
WHERE deptno=1
#練習2:洗掉3號部門的資料
DELETE FROM dept WHERE deptno=3
#1.order by排序,字典順序,默認就是升序asc,降序desc
#練習3:把員工按照名字排序
SELECT * FROM emp ORDER BY ename #a~z
#練習4:把員工按照工資排序
SELECT * FROM emp ORDER BY sal DESC #降序
#練習5:把員工按照入職時間排序
SELECT * FROM emp ORDER BY hiredate #升序排

–2,limit

#2.limit限制,分頁
#練習6:只展示前兩條員工資訊
SELECT * FROM emp LIMIT 2 #展示的條數..
SELECT * FROM emp LIMIT 2,3
#m,n,m是從第m+1條開始展示,n是要展示的條數
#練習7:查詢工資最高的員工資訊
SELECT * FROM emp ORDER BY sal DESC LIMIT 1 
#先排序再分頁,   按照工資降序排序   只取第一條

–3,統計

#練習8:統計2019年入職的員工的工資
SELECT sal FROM emp WHERE YEAR(hiredate)=2019
#練習9:統計員工崗位的獎金
SELECT comm FROM emp WHERE job='員工'
#練習10:統計員工的年薪
SELECT sal,comm,sal*16+IFNULL(comm,0)*16 年薪 FROM emp

二,聚合函式

–1,概述

可以把一列的資料聚合起來,繼續分析
常見的聚合函式: max min sum avg count

–2,測驗

#聚合函式:count / max / min / sum / avg
#練習1:查詢員工的平均工資
SELECT sal FROM emp #5個結果
SELECT AVG(sal) FROM emp #1個結果
#練習2:統計崗位是員工的平均工資
SELECT AVG(sal) FROM emp WHERE job='員工'
#練習3:統計員工的最高工資
SELECT MAX(sal) FROM emp
#練習4:統計崗位是員工的最高工資
SELECT MAX(sal) FROM emp WHERE job='員工'
#練習5:統計員工的最低工資
SELECT MIN(sal) FROM emp
#練習6:統計19年入職的員工的最低工資
SELECT MIN(sal) FROM emp WHERE YEAR(hiredate)=2019
#練習7:統計19年入職的員工的總工資
SELECT SUM(sal) FROM emp WHERE YEAR(hiredate)=2019
#練習8:統計員工的總人數
SELECT COUNT(ename) FROM emp#不推薦使用引數是欄位名
SELECT COUNT(comm) FROM emp#不準確,不統計null元素
SELECT COUNT(1) FROM emp #推薦!!
SELECT COUNT(*) FROM emp #推薦!!
#練習9:統計2號部門的員工總人數
SELECT COUNT(1) FROM emp WHERE deptno=2
#練習10:統計2019年以前入職的員工總人數
SELECT COUNT(*) FROM emp WHERE YEAR(hiredate)<2019

#查詢平均工資
SELECT AVG(sal),sal FROM emp
#混合列:是指,查詢的結果中包含著聚合列和非聚合列
#聚合列是指用了聚合函式的列
#非聚合列是指沒用聚合函式的列
#使用分組

三,分組

–1,group by

#查詢平均工資
SELECT AVG(sal),sal FROM emp
#1.報錯,因為出現了混合列:是指,查詢的結果中包含著聚合列和非聚合列
#聚合列是指用了聚合函式的列
#非聚合列是指沒用聚合函式的列
#2.使用分組來解決報錯:job  deptno year
#練習1:統計每個崗位的員工人數
SELECT job,COUNT(1) FROM emp
GROUP BY job #分組,按照合理維度分組
#口訣1:什么時候要分組??查詢的結果中出現了混合列!
#練習2:統計每個崗位的平均薪資
SELECT AVG(sal),job FROM emp
GROUP BY job
#練習3:統計每個部門的員工人數
SELECT COUNT(1) a,deptno b FROM emp
GROUP BY b #分組時可以使用別名
#練習4:統計每年入職的員工人數
SELECT COUNT(1) a,YEAR(hiredate) b FROM emp
#GROUP BY year(hiredate)
GROUP BY b

–2,having

#分組后的過濾:group by ... having
#練習5:統計每年入職的員工人數,只要2015年以后的
SELECT COUNT(1) a,YEAR(hiredate) b FROM emp
GROUP BY b
HAVING b>2015
#練習6:統計每個部門的平均薪資,只要>5000的
SELECT AVG(sal),deptno FROM emp
GROUP BY deptno
HAVING AVG(sal)>8000
#練習7:統計員工表中deptno出現的次數
SELECT deptno,COUNT(1) FROM emp
#口訣2:按照什么分組合理呢??通常按照非聚合列分組
GROUP BY deptno
HAVING COUNT(1)>1

四,事務

–1,概述

1,英文叫transaction,主要作用是用來保證多條SQL,要么全成功要么全失敗.
2,四大特征:ACID
原子性: 同一個事務里的多條SQL陳述句,是一個原子密不可分,要不全成功,要不全失敗
一致性: 保證多臺服務器里的資料是一致的(分布式系統)
隔離性: 資料庫為了提高操作的效率允許高并發的 訪問,并采用了隔離性保證了資料的安全性(采用鎖機制)
持久性: 是指,我們對資料庫的操作(增刪改)是持久生效的
3,隔離級別:
read uncommitted: 讀未提交,安全性最差,但是效率高.
read committed: 讀已提交,安全性有所提升,但是效率降低一些.也是Oracle資料庫的默認隔離級別
repeatable read : 可重復讀,安全性有所提升,但是效率又會低一些.也是MySQL資料庫的默認隔離級別
Serializable: 串行化,安全性最高,但是性能最低

–2,事務管理的方式

方式1, 使用MySQL資料庫為我們提供的,自動事務管理. 默認會為每條SQL提供事務.
方式2, 手動管理事務,必須有兩個程序: 開啟事務 … 結束事務(commit / rollback)
測驗3, 采用 方式2 來模擬事務的管理程序:
視窗1:

mysql> start transaction;  #開啟事務
mysql> insert into dept values(10,'php','bj');  #執行SQL
mysql> commit;  #提交事務

視窗2:

mysql> use cgb211101;
mysql> select * from dept; #查詢(1號視窗提交后,2號視窗才能查到)

五,欄位約束

–1,默認約束

哪個欄位添加了默認約束,哪個欄位的值就有了默認值,使用default來實作.

#1.默認約束:給欄位添加默認值--用的少!
CREATE TABLE test01(
 id INT PRIMARY KEY AUTO_INCREMENT,
 sex CHAR(3) DEFAULT '男'#默認約束
)
#雖然sex設定了默認值,但是只是手動錄入時有效,發起insert陳述句時還是要寫具體值的
INSERT INTO test01 VALUES(NULL,'男')

–2,檢查約束

哪個欄位添加了檢查約束,哪個欄位的值,就要通過檢查才能夠保存成功.用的更少!了解就行!

#2.檢查約束:檢查欄位的值的合理性
CREATE TABLE test02(
 id INT PRIMARY KEY AUTO_INCREMENT,
 age INT,
 CHECK(age>0) #檢查約束,了解即可!
)
INSERT INTO test02 VALUES(NULL,10) #ok的
INSERT INTO test02 VALUES(NULL,-10) #會報錯,沒有通過檢查約束

–3,外鍵約束

#3.外鍵約束:為了省記憶體,使用對方表的主鍵來描述兩張表的關系
#情況1:子表里的主鍵的值 必須 取自于 主表
#情況2:主表里的記錄想要洗掉時,必須保證子表沒有參考才行
CREATE TABLE tb_user(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(10),
 age INT,
 phone VARCHAR(11)
)
CREATE TABLE tb_user_address(
 user_id INT PRIMARY KEY ,#不能自增!!!
 address VARCHAR(100),
 #1.創建外鍵FK,描述和1號表的關系
 #foreign key(本表的主鍵) references 對方表名(對方的主鍵)
 FOREIGN KEY(user_id) REFERENCES tb_user(id)
)