mysql單行函數(shù)

函數(shù)的分類：
1，單行函數(shù)：對每一條記錄輸入值進(jìn)行計算，得到相應(yīng)的計算結(jié)果，返回給用戶，也就是說，每條記錄作為一個輸入?yún)?shù)，經(jīng)過函數(shù)計算得到每條記錄的計算結(jié)果。
2，多行函數(shù)：對多條記錄輸入值進(jìn)行計算，得到多條記錄對應(yīng)的單個結(jié)果。

單行函數(shù)細(xì)分

1、字符函數(shù)
2、數(shù)學(xué)函數(shù)
3、日期函數(shù)
4、其他函數(shù)
5、流程控制函數(shù)

單行函數(shù) - 字符函數(shù)

一、字符函數(shù)

1. length 獲取參數(shù)的字節(jié)長度

SELECT LENGTH('john');

2. concat 拼接字符

SELECT CONCAT(last_name,'_',first_name) FROM employees;

3. upper,lower

SELECT UPPER('john');
SELECT LOWER('JOHN');

案例：將姓變大寫，將名變小寫，然后拼接

SELECT CONCAT(UPPER(first_name), LOWER(first_name)) AS "姓名" FROM employees;

4. substr

4個重載的方法

注意：SQL語言的索引從1開始

作用：截取從pos開始的所有字符，包括pos

SELECT SUBSTR('zhangjin', 6); # jin

作用：截取從pos開始, 字符長度為len的子串

SELECT SUBSTR('zhangjin',1,5); # zhang

案例：姓名中首字母大寫，其他字符小寫

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)), LOWER(SUBSTR(last_name,2)))
AS "姓"
FROM employees;

5. instr 返回子串第一次出現(xiàn)的起始索引，沒有出現(xiàn)則返回0

SELECT INSTR('zhangjin','shay'); # 0
SELECT INSTR('zhangjin','jin'); # 6
SELECT INSTR('zhangjinjinjinjinjinjin','jin'); # 6

6、trim

默認(rèn)是trim左右兩邊的空格

SELECT TRIM(' zhangjin ');

可以指定要trim的字符

SELECT TRIM('a' FROM 'aaaaaazhangjinaaaa');

7、lpad

使用指定字符進(jìn)行左填充，填充后的字符個數(shù)為len

len，字符個數(shù)，并不是字節(jié)長度

SELECT LPAD('張',3,'*'); # **張

最終長度由len決定

SELECT LPAD('張三豐',2,'*'); # 張三

8、rpad

使用指定字符進(jìn)行右填充，填充后的字符個數(shù)為len

len，字符個數(shù)，并不是字節(jié)長度

SELECT RPAD('張',3,''); # 張*

最終長度由len決定

SELECT RPAD('張三豐',2,'*'); # 張三

9、replace

replace(str,from_str,to_str) 全部替換

SELECT REPLACE('zhangjin', 'zhang', 'shay');
SELECT REPLACE('zhangjinzhangjinzhangjin', 'zhang', 'shay');

一些練習(xí)

-- 寫一個查詢,用首字母大寫，其它字母小寫顯示雇員的 ename，顯示名字的長度，并給每列一個適當(dāng)?shù)臉?biāo)簽，條件是滿足所有雇員名字的開始字母是J、A 或 M 的雇員，并對查詢結(jié)果按雇員的ename升序排序。（提示：使用initcap、length、substr）
select concat(SUBSTR(ename,1,1),LOWER(SUBSTR(ename,2,LENGTH(ename)-1))),LENGTH(ename) from emp
where SUBSTR(ename,1,1) in ('J','A','M') 
order by ename


-- 查詢員工姓名中中包含大寫或小寫字母A的員工姓名。
select ename from emp 
where INSTR(ename,'A') > 0


2.查詢部門編號為10或20，入職日期在81年5月1日之后，并且姓名中包含大寫字母A的員工姓名，員工姓名長度（提示，要求使用INSTR函數(shù)，不能使用like進(jìn)行判斷)

3.查詢每個職工的編號,姓名，工資
要求將查詢到的數(shù)據(jù)按照一定的格式合并成一個字符串.
前10位：編號,不足部分用*填充,左對齊
中間10位：姓名，不足部分用*填充,左對齊
后10位：工資，不足部分用*填充,右對齊

二、數(shù)學(xué)函數(shù)

round 四舍五入

SELECT ROUND(1.45);
SELECT ROUND(-1.65);
SELECT ROUND(1.567,2); #保留2位小數(shù)

ceil 向上取整, 返回大于等于該參數(shù)的最小整數(shù)

SELECT CEIL(1.002); #2
SELECT CEIL(1.00); #1

floor 向下取整，返回小于等于該參數(shù)的最大整數(shù)

SELECT FLOOR(1.002); #1

truncate 截斷

SELECT TRUNCATE(1.65,1); #保留1位小數(shù)

mod 取余數(shù)

SELECT MOD(10,3); # 10%3=1
SELECT MOD(-10,-3); # -1, 被除數(shù)為負(fù)數(shù)，結(jié)果為負(fù)數(shù)

一些練習(xí)

1.寫一個查詢，分別計算100.456 四舍五入到小數(shù)點后第2位，第1位，整數(shù)位的值。
2.寫一個查詢，分別計算100.456 從小數(shù)點后第2位，第1位，整數(shù)位截斷的值。

三、日期函數(shù)

now 返回當(dāng)前系統(tǒng)日期+時間

select now();

curdate 返回當(dāng)前系統(tǒng)日期，不包括日期

select curdate();

curtime 返回當(dāng)前時間，不包括日期

select curtime();

可以獲取指定的部分：年，月，日，時，分，秒

獲取年

select year(now()) as 年;
select year('1986-1-1');
select year(hiredate) from employees;

獲取月

select month(now()) as 月; # 11
select monthname(now()) as 月; # November

獲取日

select day(now()) as 日; # 24
select dayname(now()) as 星期; # Friday

其他一些日期函數(shù)

-- datediff(date1,date2)：兩個日期相減 date1 - date2，返回天數(shù)  
SELECT DATEDIFF('2017-06-05','2017-05-29');-- 7  

SELECT LAST_DAY('2016-02-01');-- 2016-02-29 (返回月份中最后一天)  
SELECT LAST_DAY('2016-05-01');-- 2016-05-31  

-- DATE_ADD(date,INTERVAL expr type) 從日期加上指定的時間間隔  
-- type參數(shù)可參考：http://www.w3school.com.cn/sql/func_date_sub.asp  
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 YEAR);-- 表示：2018-05-15 10:37:14.123456  
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 QUARTER);-- 表示：2017-08-15 10:37:14.123456  
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 MONTH);-- 表示：2017-06-15 10:37:14.123456  
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 WEEK);-- 表示：2017-05-22 10:37:14.123456  
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 DAY);-- 表示：2017-05-16 10:37:14.123456  
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 HOUR);-- 表示：2017-05-15 11:37:14.123456  
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 MINUTE);-- 表示：2017-05-15 10:38:14.123456  
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 SECOND);-- 表示：2017-05-15 10:37:15.123456  
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 MICROSECOND);-- 表示：2017-05-15 10:37:14.123457  

-- DATE_SUB(date,INTERVAL expr type) 從日期減去指定的時間間隔  
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 YEAR);-- 表示：2016-05-15 10:37:14.123456  
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 QUARTER);-- 表示：2017-02-15 10:37:14.123456  
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 MONTH);-- 表示：2017-04-15 10:37:14.123456  
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 WEEK);-- 表示：2017-05-08 10:37:14.123456  
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 DAY);-- 表示：2017-05-14 10:37:14.123456  
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 HOUR);-- 表示：2017-05-15 09:37:14.123456  
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 MINUTE);-- 表示：2017-05-15 10:36:14.123456  
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 SECOND);-- 表示：2017-05-15 10:37:13.123456  
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 MICROSECOND);-- 表示：2017-05-15 10:37:14.123455  

SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY);-- 前一天：2017-05-11  
SELECT DATE_SUB(CURDATE(),INTERVAL -1 DAY);-- 后一天：2017-05-13  
SELECT DATE_SUB(CURDATE(),INTERVAL 1 MONTH);-- 一個月前日期：2017-04-12  
SELECT DATE_SUB(CURDATE(),INTERVAL -1 MONTH);-- 一個月后日期：2017-06-12  
SELECT DATE_SUB(CURDATE(),INTERVAL 1 YEAR);-- 一年前日期：2016-05-12  
SELECT DATE_SUB(CURDATE(),INTERVAL -1 YEAR);-- 一年后日期：20178-06-12

一些練習(xí)

0.查詢每個員工截止到現(xiàn)在一共入職多少天？
1.查詢服務(wù)器當(dāng)前時間
-- 查詢部門10,20的員工截止到2000年1月1日，工作了多少個月，入職的月份。
select ename,month(hiredate), (year('2000-01-01') -year(hiredate))*12 + month('2000-01-01') -month(hiredate) from emp;
-- 如果員工試用期6個月，查詢職位不是MANAGER的員工姓名，入職日期，轉(zhuǎn)正日期，入職日期后的第一個星期一,入職當(dāng)月的最后一天日期。
select ename,hiredate,DATE_ADD(hiredate,INTERVAL 6 MONTH),DATE_ADD(hiredate,INTERVAL 7-DAYOFWEEK(hiredate)+2 DAY),LAST_DAY(hiredate)from emp

類型轉(zhuǎn)換函數(shù)

image

將日期格式的字符串 -> 日期

str_to_date('24-11-2017', '%d-%m-%Y');

select str_to_date('24-11-2017', '%d-%m-%Y');

案例：將用戶輸入的4-3 1992作為查詢依據(jù)，找出在1992年4月3日入職的員工

select * from emp
where hiredate = str_to_date('12-17 1980', '%m-%d %Y');

日期 -> 字符

date_format('2018/6/6', '%Y年%m月%d日')

select date_format('2018/6/6', '%Y年%m月%d日');

案例：查詢有獎金的員工名和入職日期（XX月/XX日 XX年）

select ename,date_format(hiredate, '%m月/%d日 %y年') as 入職日期
from emp
where comm is not null;

mysql 字符串?dāng)?shù)字轉(zhuǎn)換

//字符串轉(zhuǎn)數(shù)字

1 方法一：SELECT CAST('123' AS SIGNED);
2 方法二：SELECT CONVERT('123',SIGNED);
3 方法三：SELECT '123'+0;

//數(shù)字轉(zhuǎn)字符串

CONCAT()

FORMAT函數(shù)

FORMAT函數(shù)在mysql中是數(shù)據(jù)內(nèi)容格式化的，格式化后得到結(jié)果：＃＃＃，＃＃＃，＃＃＃＃＃。

SELECT FORMAT(100000,2);//100,000.00

一些練習(xí)

1.顯示服務(wù)器系統(tǒng)當(dāng)前時間，格式為2007-10-12 17:11:11
2.顯示ename、hiredate 和 雇員開始工作日是星期幾，列標(biāo)簽DAY
3.查詢員工姓名，工資，格式化的工資（￥999,999.99） 
4.把字符串2015-3月-18 13:13:13 轉(zhuǎn)換成日期格式，并計算和系統(tǒng)當(dāng)前時間間隔多少天。

case控制結(jié)構(gòu)的使用一：switch case的效果

/*
適合：等值判斷
mysql中case控制結(jié)構(gòu)：
case 要判斷的字段或表達(dá)式
when 常量1 then 要顯示的值1或語句1;
when 常量2 then 要顯示的值2或語句2;
when 常量3 then 要顯示的值3或語句3;
when 常量4 then 要顯示的值4或語句4;
else 要顯示的值n或語句n;
end
*/

/案例：查詢員工的工資，要求
部門號=30，顯示的工資為1.1倍
部門號=40，顯示的工資為1.2倍
部門號=50，顯示的工資為1.3倍
其他部門，顯示工資為原工資
/
SELECT salary,department_id,
CASE department_id
WHEN 30 THEN 1.1salary
WHEN 40 THEN 1.2salary
WHEN 50 THEN 1.3*salary
ELSE salary
END
AS "新工資"
FROM employees;

case控制結(jié)構(gòu)的使用二：類似多重if

/*
mysql中case控制結(jié)構(gòu)：
適合：區(qū)間判斷（大?。?/p>

case
when 條件1(true,false) 要顯示的值1（尾部不加分號）或語句1（尾部加分號）
when 條件2(true,false) 要顯示的值2（尾部不加分號）或語句2（尾部加分號）
....
else 要顯示的值n(尾部不加分號）或語句n(尾部加分號）
end
*/

案例：查詢員工的工資情況

/*
如果工資大于2萬，顯示A級別
如果工資大于1萬5，顯示B級別
如果工資大于1萬，顯示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;

課后作業(yè)

1.計算2000年1月1日到現(xiàn)在有多少月，多少周（四舍五入）。
2.查詢員工ENAME的第三個字母是A的員工的信息(使用2個函數(shù))。
3.使用trim函數(shù)將字符串‘hello’、‘  Hello ’、‘bllb’、‘ hello    ’分別處理得到下列字符串ello、Hello、ll、hello。
4.將員工工資按如下格式顯示：123,234.00 RMB 。
5.查詢員工的姓名及其經(jīng)理編號，要求對于沒有經(jīng)理的顯示“No Manager”字符串。
6.將員工的參加工作日期按如下格式顯示：月份/年份。 
7.在員工表中查詢出員工的工資，并計算應(yīng)交稅款：如果工資小于1000,稅率為0，如果工資大于等于1000并小于2000，稅率為10％，如果工資大于等于2000并小于3000，稅率為15％，如果工資大于等于3000，稅率為20％。
8.創(chuàng)建一個查詢顯示所有雇員的 ename和 sal。格式化sal為 15 個字符長度，用 $ 左填充，列標(biāo)簽 SALARY。
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 2017/3/14 RDBMS:關(guān)系型數(shù)據(jù)庫管理系統(tǒng) 關(guān)系模型獨立于語言 SQL有幾種不同類型的語言：數(shù)據(jù)定義語言...
    ancherl閱讀 1,800評論 0贊 6
  • 1. Java基礎(chǔ)部分 基礎(chǔ)部分的順序：基本語法，類相關(guān)的語法，內(nèi)部類的語法，繼承相關(guān)的語法，異常的語法，線程的語...
    子非魚_t_閱讀 34,767評論 18贊 399
  • 1.簡介 數(shù)據(jù)存儲有哪些方式？電子表格，紙質(zhì)文件，數(shù)據(jù)庫。 那么究竟什么是關(guān)系型數(shù)據(jù)庫？ 目前對數(shù)據(jù)庫的分類主要是...
    喬震閱讀 2,025評論 0贊 2
  • 配置文件 - Config 注: 本章需要讀者了解關(guān)于 java.io 的一些基本知識, 如 File, Inpu...
    Zoyn_閱讀 6,473評論 0贊 2
  • 那一年秋天 楓葉正紅 相遇的畫面 能否在心間 烙上深深的印記 刻苦銘心 再見，也許再也不見 你是否還會記得 我們彼...
    橙子在陽光下閱讀 209評論 1贊 2

友情鏈接更多精彩內(nèi)容