常見函式之單行函式

發表於2020-07-04
函式
#進階4：常見函式

/*
概念：類似於java中的方法，將一組邏輯語句封裝子方法體中，對外暴露名
好處：1.隱藏了實現細節 2.提高程式碼的重用性
呼叫：
            selcet 函式名(實參列表) 【from 表】;
特點：
        ①叫什麼（函式名）
        ②幹什麼（函式功能）
分類：
        1.單行函式
        如 concat 、length、ifnull等
        ·字元函式數學函式
        ·日期函式
        ·其他函式【補充】
        流程控制函式【補充】
        2.分組函式
        功能：做統計使用，由稱為統計函式、聚合函式、組函式
常見函式:
字元函式：length concat substr instr trim upper lower lpad rpad replace
數學函式:round ceil floor truncate mod 
日期函式: now curdate curtime year month monthname day hour minute second str_to_date date_format
其他函式：version database USER
控制函式：if case
*/

#一、字元函式

#LENGTH()
SELECT LENGTH('john');
SELECT LENGTH('張三丰hahaha');

SHOW VARIABLES LIKE '%char%'

#2.CONCAT(str1,str2,...) 拼接字串
SELECT CONCAT(last_name,'_',first_name)
FROM employees;

#3.upper、lower
SELECT UPPER('john');
SELECT LOWER('john');
#例項：將姓變大寫，名變小寫，然後拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;

4.SUBSTR|SUBSTRING
#注意：索引從1開始
SELECT SUBSTR('李莫愁愛上了陸湛遠',7) out_put;

#擷取從指定索引處指定字元長度的字元
SELECT SUBSTR('李莫愁愛上了陸湛遠',1,3) out_put;

#案例：姓名中首字元大寫，其他字元小寫然後用_拼接，顯示出來
SELECT CONCAT(upper(substr(last_name,1,1)),'_',LOWER(first_name)) out_name
FROM employees;

#5.instr
SELECT INSTR('楊不悔愛上了殷六俠客','殷') as out_put;

#6.TRIM()

SELECT LENGTH(TRIM('    張翠山    ')) as out_put;

SELECT TRIM('a' FROM 'aaaaaaaaaa張aaa翠山aaaaaaaaaaaaaaaaaaa') as out_put;

#7.LPAD()用指定的字元實現左填充指定長度
SELECT lpad('殷素素',10,'*') out_put;

#8。replace全部替換
SELECT REPLACE('周芷若12334','周芷若','趙敏');


#二、數學函式
#round 四捨五入
SELECT ROUND(1.45);
SELECT ROUND(1.567,2);

#CALL 向上取整,返回>=該引數的最大整數
SELECT CEIL(1.52);

#floor 向下取整，返回<=該引數的最大整數
SELECT FLOOR(-9.99);

#`TRUNCATE 截斷
SELECT truncate(1.699999,2);

#mod取餘
/*
mod(a,b) :a-a/b*b

mod(-10,-3) :-10-(-10)/(-3)*(-3)=-1
*/
SELECT MOD(10,-3);
SELECT 10%3;

#三、日期函式
#now 返回當前系統日期+時間
SELECT now();

#CURDATE()返回當前系統日期，不包含時間
SELECT CURDATE();

#CURTIME()返回當前時間，不包含日期
SELECT CURTIME();

#可以活得指定的部分，年year、月month、日day、小時hour、分鐘minute、秒second
SELECT year(now()) 年;
SELECT year('1998-1-1') 年;
SELECT month('1998-1-1') 月;

SELECT year(hiredate) 年 FROM employees;

SELECT month(NOW())  月;
SELECT MONTHNAME(NOW()) 月;
SELECT minute(NOW()) 分鐘;

#str_to_date 將字元通過指定的格式轉換成成日期

SELECT STR_TO_DATE('1999-1-2','%Y-%m-%d') as out_put;

#查詢入職日期為1992-4-3的員工資訊
SELECT * FROM employees WHERE hiredate ='1992-4-3';

SELECT * FROM employees WHERE hiredate =str_to_date('4-3-1992','%m-%d-%Y');

#data_format 將日期轉換成字元

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') as out_put;

#查詢有獎金的員工名和入職日期（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();

#五、流程控制函式
#1.if函式;if else 的效果
SELECT if(10>5,'大','小');

SELECT last_name,commission_pct,if(commission_pct is null,'沒獎金，呵呵','有獎金，嘻嘻') as 獎金
FROM employees
ORDER BY commission_pct DESC;

#2.case函式的使用一:switch case的效果
/*
switch(變數或表示式）{
case 常量1：語句1； break；
。。。
default：語句n；break；
}
myq1中
case要判斷的欄位或表示式
when常量1 then要顯示的值1或語句1;
when常量2 then要顯示的值2或語句2； 
...
e1se要顯示的值n玻語句n
end
*/
/*案例：查詢員工的工資，要求
部門號=30，顯示的工資為1.1倍
部門號=40，顯示的工資為1.2倍
部門號=50，顯示的工資為1.3倍
其他部門，顯示的工資為原工資
*/
SELECT salary 原始工資,department_id,
case department_id
when 30 THEN salary*1.1
when 40 THEN salary*1.2
when 50 THEN salary*1.3
else salary
END as 新工資
from employees;

#3.case函式的使用二：類似於 多重if
/*
java中：
if(條件1）{
                        語句1：
}else if (條件2) {
語句2;
}
...
else{ 語句n; }
mysql中：
CASE
WHEN 條件1 then 要顯示的值1或語句1
WHEN 條件2 then 要顯示的值2或語句2
.。。
ELSE 要顯示的值n或語句n
END
*/

#案例：查詢員工的工資的情況
如果工資>20000 ,顯示A級別
如果工資>15000 ,顯示B級別
如果工資>10000 ,顯示C級別
否則，顯示D級別

SELECT salary,
CASE 
when salary>20000 THEN 'A'
when salary>15000 THEN 'B'
when salary>10000 THEN 'C'
else 'D'
END as 工資級別
FROM employees;

#------------------------------
#練習：
#顯示系統時間（注：日期+時間）
SELECT now() as 系統時間;
#查詢員工號、姓名、工資，以及工資提高百分之20%後的結果（new salary）
SELECT  employee_id,last_name,salary,salary*1.2 as "new salary"
from employees;
#3.將員工的姓名按首字母排序，並寫出姓名的長度(length)
SELECT LENGTH(last_name) 長度,substr(last_name,1,1) 首字元,last_name
FROM employees
ORDER BY 首字元 ;
#

 

相關文章