概念: 類似java中的方法,将一組邏輯語句封裝在方法體中,對外暴露方法名
好處:1. 隐藏了函數的實作細節,2.提高了代碼的重用性
調用:select 函數名(實參清單) [from 表];
特點 ①叫什麼(函數名)
②幹什麼(函數的功能)
分類:
功能:做統計使用,又稱為統計函數,聚合函數,組函數
length : 擷取參數值的位元組個數(中文utf-8是三個位元組,gbk是兩個位元組)
concat : 拼接字元串
upper,lower 大小寫切換
substr | substring 切割字元 注意mysql中的索引是從1開始
#截取從指定索引處後面的所有字元 select substr(“zhangbushuai” from 1 for 5); select substr(“zhangbushuai”,6);
instr 傳回子串第一次出現的索引,如果找不到就傳回0
select instr(“zhangbushuai”,“bu”) as ‘index’;
trim 去除前後空格
select length(trim(" zyd ")) as name; 聲明去除指定字元 select length(trim(“a” from “aaaaaaaaazydaaaaaaaa”)) as name;
lpad rpad 用指定的字元實作左/右填充
select lpad(“zyd”,5,"*") as name; 結果 : **zyd 其中5表示總長度如果初始字元超過了總長度,會被自動截斷
replace 替換原先字元串中的字元,為指定的字元串
select replace(“zydhahaha”,“ha”,“shuai”) as name;
round 四舍五入
select round(-1.55); //-2
負數可以看做先取整,四舍五入,然後加上符号
ceil 向上取整,傳回>= 該參數的最小整數 -1
select ceil(-1.02);
floor 向下取整,傳回<= 該參數的的最大整數-2
select floor(-1.02);
truncate 截斷
select truncate(1.6999,2);
保留小數點後兩位 1.69
mod 取餘
select mod(-10,3);
結果 -1
餘數的正負性和被除數有關,
now() 傳回目前系統的日期和時間
select now();
curdate 傳回目前系統的日期,不包含時間
select curdate();
curtime 傳回目前時間,不包含日期
select curtime();
擷取指定的部分,年,月,日,小時,分鐘,秒
select year(now()) 年; select year(“1998-1-1”) 年
select year(hiredate) 年 from employees;
select month(now()) 月; select monthname(now()) 月; 傳回的是英文
str_to_date 将字元通過指定的格式轉換成日期
select * from employees where hiredate = str_to_date(“4-3 1992”,"%c-%d %y");
有的時候日期格式不是我們想要的,轉換
date_format 将日期轉換成字元
select date_format(now(),’%y年%m月%d日’) as 日期;
案例: 查詢有獎金的員工名和入職日期(xx月/xx日 xx年)
select last_name,date_format(hiredate, ‘%m月/%d日 %y年’) 入職時間 from employees where commission_pct is not null;
檢視版本
select version();
檢視目前資料庫
select database();
檢視目前使用使用者
select user();
if函數
相當于三元運算符 參數1 條件表達式,ture執行的表達式,false執行的表達式
案例 : 查詢有 獎金的員工
select last_name,commission_pct, if(commission_pct is null,“沒有獎金”,“有獎金”) 備注
case: 和java中的switch case 的效果
文法
case 要判斷的字段或表達式 when 常量1 then 要顯示的值1或語句1; when 常量 2 then 要顯示的值2或語句2; … else 要顯示的值n或語句n; end
案例: 查詢員工的工資,要求
部門号=30,顯示的工資為1.1倍
部門号=40,顯示的工資為1.2倍
部門号=50,顯示的工資為1.3倍
其他部門,顯示的工資為原工資
select salary 原始工資, department_id, case department_id when 30 then salary1.1 when 40 then salary1.2 when 50 then salary*1.3 else salary end as 新工資
case 函數的使用二,類似于,多if
case when 條件1 then 要顯示的值1或語句2 when 條件2 then 要顯示的值2或語句2 else 要顯示的值n或語句n
案例:查詢員工的工資情況
如果工資>20000,顯示a級别
如果工資>15000,顯示b級别
如果工資>10000,顯示c級别
否則,顯示d級别
select salary, when salary>20000 then “a” when salary>15000 then ‘b’ when salary>20000 then ‘c’ else ‘0’ end as 工資級别
功能:用作統計使用,又稱為聚合函數或統計函數或組函數
sum 求和 avg 平均值,max 最大值, min 最小值, count 計算個數
簡單的使用
select sum(salary) from employees; select avg(salary) from employees; select min(salary) from employees; select max(salary) from employees; select count(salary) from employees;
參數支援哪些類型
sum,avg 處理數值型 max,min,count 可以處理任何的類型 以上的分組函數都忽略了null值
和distinct搭配使用
select sum(distinct salary), sum(salary)
count函數
查詢表的總列數
select count(*) from employees; select count(1) from employees;
在mysql的存儲引擎中
myisam存儲引擎下,count(*)的效率高 ,因為程式内部有一個計數器 innodb存儲引擎下啊,count(*)和count(1)差不多,比count(字段高)
注意:和分組函數一同查詢的字段,要求group by 後的字段
案例: 查詢員工表中的最大入職時間和最小入職時間的相差天數(difference),用到日期相差的函數
select datediff(max(hiredate),min(hiredate)) diff
文法:
select 分組函數,列(要求出現在 group by的後面) from 表 [where 篩選條件] group by 分組的清單 [order by 子句]
查詢每個工種的最高工資
select max(salary), job_id group by job_id
查詢每個位置上的部門個數
select count(*), location_id from departments group by location_id;
添加篩選條件的案例
案例1:查詢郵箱中包含a字元的,每個部門的平均工資
select avg(salary), department_id where email like ‘%a%’ group by department_id
案例2:查詢有獎金的,每個上司手下員工的最高工資
select max(salary),manager_id where commission_pct is not null group by manager_id
添加複雜分組查詢的篩選
案例1:查詢哪個部門的員工個數>2
當原始表中沒有字段,分步操作
①查詢每個部門的員工個數
select count(*) department_id group by department_id;
②根據①的結果進行篩選,查詢哪個部門的員工個數>2
select count(), department_id having count()>2;
使用having,where隻能對于表中的字段進行選擇
案例2: 查詢每個工種有獎金的員工的最高工資>12000的工種編号和最高工資
①查詢每個工種有獎金的員工的最高工資
group by job_id;
②根據①結果繼續篩選,最高工資>12000
select max(salary)>12000, job_id having max(salary)>12000;
案例3 查詢上司編号>102的每個上司手下的最低工資>5000的上司編号是哪個,以及其最低工資
分割①查詢每個上司手下的員工固定最低工資
select min(salary),manager_id
②添加篩選條件,編号>102
where manager_id>102
③添加篩選條件,最低工資大于5000
having min(salary)>5000;
特點:
分組查詢中的篩選條件分為兩類
資料源
位置
關鍵字
分組前篩選
原始表
group by 的前面
where
分組後篩選
分組後的結果集
group by 的後面
having
分組函數做的條件肯定是放在having子句中****
**性能方面:**能用分組前篩選的盡量使用分組前篩選
group by 後面支援語句使用别名以及表達式
案例: 按員工姓名的長度分組,查詢每一組的員工個數,篩選員工個數>5的有哪些
①查詢每個長度的員工個數
count(*),length(last_name) len_name from employees group by length(last_name);
②添加篩選條件
count(*) c,length(last_name) len_name group by length(last_name) having c>5;
按多個字段進行分組
查詢每個部門每個工種的員工的平均工資
select avg(salary),department_id,job_id group by job_id,department_id;
特點2: group by 子句支援單個字段分組,多個字段分組(多個字段之間用逗号隔開,沒有順序要求),表達式或函數(用的較少)
特點3 使用order by 子句,放在最後面