cgb2106-day03

一,統計年薪

#統計員工的年薪
SELECT sal,comm,(sal+comm)*12 FROM emp
SELECT sal,comm,
(sal+IFNULL(comm,0))*16 AS 年薪 #as用來設定別名
FROM emp

二,聚合函式

–1,概述

把一列的值全都取出來,聚合起來,分析最大值,最小值,平均值,求和,求個數
常見的聚合函式: max   min   avg    sum   count 

–2,測驗

#聚合函式:把查出來的列聚合起來分析資料
#求最高薪max 
SELECT sal FROM emp ORDER BY sal DESC LIMIT 1
SELECT MAX(sal) FROM emp #獲取sal這列里的最大值
#求最低薪min 
SELECT sal FROM emp ORDER BY sal LIMIT 1
SELECT MIN(sal) MIN FROM emp
#求和sum 
SELECT SUM(sal) FROM emp
#求平均數avg
SELECT AVG(sal) FROM emp
SELECT MAX(sal),MIN(sal),SUM(sal),AVG(sal) 
FROM emp
#求總個數count
SELECT COUNT(comm) FROM emp#不統計comm欄位值是null的-低效
SELECT COUNT(*) FROM emp#都統計
SELECT COUNT(1) FROM emp#都統計-高效
#統計工資>8000的人數
SELECT COUNT(1) FROM emp 
WHERE sal>8000
#統計2019年入職的人數
SELECT COUNT(1) FROM emp
WHERE YEAR(hiredate)=2019

三,分組

–1,測驗

#什么時候必須分組?當查詢結果中出現了聚合列和非聚合列時
#按照什么分組合理?通常按照非聚合列分組
#什么是聚合列非聚合列?使用了聚合函式max min sum avg count的就是聚合列
#統計每個部門的平均薪資
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno #按照部門編號分組
#統計每個崗位的最高薪資
SELECT MAX(sal),job FROM emp
GROUP BY job#按照崗位分組
#統計每年的入職人數
#count是聚合函式,year只是普通函式
SELECT COUNT(1),YEAR(hiredate) FROM emp
GROUP BY YEAR(hiredate)#按照非聚合列分組

#having的作用:用來對分組后的資料,進一步過濾
#統計每個部門的平均薪資,只要>10000的記錄
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal)>10000
#統計每個崗位的最高薪資,只要>8000的記錄
SELECT job,MAX(sal) FROM emp
#先過濾再分組高效,但是where里不能用聚合函式
#where MAX(sal)>8000  #會報錯
GROUP BY job
HAVING MAX(sal)>8000
ORDER BY MAX(sal) #升序排序

#統計每年的入職人數,只要人數>1的記錄
SELECT COUNT(1),YEAR(hiredate) FROM emp
GROUP BY YEAR(hiredate)
HAVING COUNT(1) > 1 
#不能改成where,后面出現了聚合函式

#統計每年的入職人數,只要2017年以后的記錄
SELECT COUNT(1),YEAR(hiredate) FROM emp
WHERE YEAR(hiredate) > 2017 #高效,只是where里不能出現聚合函式
GROUP BY YEAR(hiredate)
#having YEAR(hiredate)> 2017 
#having里使用的過濾條件必須是查到的結果

四,事務

-1,概述

保證SQL陳述句,要么全執行成功,要么全失敗
有四個特征ACID:
A是原子性: 是指多條SQL是一個原子,密不可分.如果都正確,就操作了資料.如果有錯誤的都會發生回滾,回到事務執行之前.
C是一致性: 保證了資料的一致性和完整性.
I是隔離性: 保證多執行緒并發時的資料安全,多個操作之間是被隔離的.
D是持久性: 是指對資料CDU的影響是持久生效的.
隔離級別:讀未提交 讀已提交 可重復讀 串行化
從前往后,性能越來越差,安全性越來越高.MySQL默認是可重復讀

-2,測驗

先開啟事務,然后執行一批SQL,再結束事務

mysql> start transaction; #開啟事務
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept values(18,'java','shanghai');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept values(19,'java2','shanghai2');
Query OK, 1 row affected (0.00 sec)
mysql> commit; #提交事務
Query OK, 0 rows affected (0.03 sec)

五,欄位約束

–1,默認約束和檢查約束

#默認約束:給指定欄位設定默認值
CREATE TABLE f(
id INT PRIMARY KEY AUTO_INCREMENT,#主鍵,自動遞增
sex VARCHAR(10) DEFAULT '男' #設定默認值
)

#檢查約束:給指定欄位設定合法值的檢查規則
CREATE TABLE g(
id INT PRIMARY KEY AUTO_INCREMENT,#主鍵,自動遞增
age INT,
CHECK(age>0 AND age<200)#設定檢查
)

–2,外鍵約束

#默認約束:給指定欄位設定默認值
CREATE TABLE f(
id INT PRIMARY KEY AUTO_INCREMENT,#主鍵,自動遞增
sex VARCHAR(10) DEFAULT '男' #設定默認值
)

#檢查約束:給指定欄位設定合法值的檢查規則
CREATE TABLE g(
id INT PRIMARY KEY AUTO_INCREMENT,#主鍵,自動遞增
age INT,
CHECK(age>0 AND age<200)#設定檢查
)

#外鍵:通過指定的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),
#表明和哪張表的哪個欄位有關系
#foreign key(本表的主鍵) references 關聯表(主鍵)
FOREIGN KEY(user_id) REFERENCES tb_user(id)
)

六,索引

–1,概述

為了提高資料庫的查詢效率,提供了索引的技術

–2,測驗

#查看索引
SHOW INDEX FROM emp