mysql學習筆記之十一（常用函數）
能運行在多個系統上的代碼具有可移植性，在數據庫軟件中，多數sql語句時可移植的，可移植性比較強；而函數的移植性不強，主要是由於各種數據庫軟件都支持自己所特有的函數。因此許多sql用戶不認同使用數據庫軟件所特有的的函數
字符串函數
concat(str1,...,strn) : 連接n個字符串為一個完整的字符串
insert(str,x,y,instr) : 將字符串str從第x位置開始，y個字符長的子串替換為instr
lower(str) : 所有字符變為小寫
upper(str) ：所有字符變為大寫
left(str) : 返回字符串最左邊的x個字符
right(str) : 返回字符串str最右邊的x個字符
lpad(str,n,pad) : 使用pad字符串對字符串最左邊進行填充，直到長度為n個字符
rpad(str,n,pad) : 使用pad字符串對字符串最右邊進行填充，直到長度為n個字符
ltrim(str) : 去掉字符串str最左邊的空格
rtrim(str) : 去掉字符串str最右邊的空格
repeat(str,x) : 返回字符串str重復x次的結果
replace(str,a,b) : 使用字符串b替換字符串str中所有出現的字符串
strcmp(str1,str2) : 比較字符串str1和str2
trim(str) : 去掉字符串str行頭和行尾的空格
substring(str,x,y) : 返回字符串str中從x位置起y個字符串長度的字符串
1、concat(str1,...,strn)
mysql> select concat('M','y','sql');
+-----------------------+
| concat('M','y','sql') |
+-----------------------+
| Mysql |
+-----------------------+
mysql> select concat('M','y','sql',null);
+----------------------------+
| concat('M','y','sql',null) |
+----------------------------+
| NULL |
+----------------------------+
注意：如果傳入的參數有null，則返回的結果將是null
2、insert(str,x,y,instr)
mysql> select insert('mysql learning',3,5,'miss you');
+-----------------------------------------+
| insert('mysql learning',3,5,'miss you') |
+-----------------------------------------+
| mymiss youearning |
+-----------------------------------------+
3、strcmp(str1,str2)
mysql> select strcmp('my','sql');
+--------------------+
| strcmp('my','sql') |
+--------------------+
| -1 |
+--------------------+
1 row in set (0.01 sec)
mysql> select strcmp('mysd','mysql');
+------------------------+
| strcmp('mysd','mysql') |
+------------------------+
| -1 |
+------------------------+
1 row in set (0.00 sec)
mysql> select strcmp('mysd','mysal');
+------------------------+
| strcmp('mysd','mysal') |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
mysql> select strcmp('mysd','mysd');
+-----------------------+
| strcmp('mysd','mysd') |
+-----------------------+
| 0 |
+-----------------------+
4、獲取字符串長度length()和字符數函數char_length()
mysql> select 'mysql' as '英文字符串字節長度',
-> length('mysql') as '字符串長度',
-> '常建功' as '中文字符串',
-> length('常建功') as '字符串字節長度';
+--------------------+------------+------------+----------------+
| 英文字符串字節長度 | 字符串長度 | 中文字符串 | 字符串字節長度 |
+--------------------+------------+------------+----------------+
| mysql | 5 | 常建功 | 6 |
+--------------------+------------+------------+----------------+
select 'mysql' as '英文字符串',char_length('mysql') as '字符串字符數', '常建功' as '中文字符串',char_length('常建功') as '字符串字符數';
+------------+--------------+------------+--------------+
| 英文字符串 | 字符串字符數 | 中文字符串 | 字符串字符數 |
+------------+--------------+------------+--------------+
| mysql | 5 | 常建功 | 6 |
+------------+--------------+------------+--------------+
理論上是3，但是實際上我的顯示時6.
5、大小字母轉換upper()和lower()
select upper('aBcD') as 'aBcD',lower('HJjdIUE') as 'HJjdIUE';
+------+---------+
| aBcD | HJjdIUE |
+------+---------+
| ABCD | hjjdiue |
+------+---------+
6、查找字符串
find_in_set(),field(),locate(),position(),instr(),ELT()
find_in_set(str1，str2)獲取相匹配字符串的位置，參數str2中將包含若干個用逗號隔開的字符串
mysql> select find_in_set('mysql','I,love,mysql,and,you?') as '位置';
+------+
| 位置 |
+------+
| 3 |
+------+
filed(str,str1,str2,...):返回第一個與字符串str相匹配的字符串的位置
mysql> select field('mysql','oracle','sql server','mysql') as '位置';
+------+
| 位置 |
+------+
| 3 |
+------+
locate(str1,str):返回參數str中字符串str1的開始位置
position(str1 in str)
instr(str,str1)
mysql> select locate('sql','mysql') as'locate',position('sql' in 'mysql') as 'position',instr('mysql','sql') as 'instr';
+--------+----------+-------+
| locate | position | instr |
+--------+----------+-------+
| 3 | 3 | 3 |
+--------+----------+-------+
elt(n,str1,str2,...)
返回第n個字符串
mysql> select elt(1,'mysql','oracle','sql server') as ELT;
+-------+
| ELT |
+-------+
| mysql |
+-------+
make_set(num,str1,str2,...,strn)
首先將數值num轉換成二進制數，然後按照二進制從參數str1，str2，...，strn中選取相應的字符串。在通過二進制來選擇字符串時，會從右到左讀取該值，如果值為1選擇該字符串，否則將不選擇字符串。
mysql> select bin(13) as BIN, make_set(13,'a','b','c','d','e','f','g') as make_set;
+------+----------+
| BIN | make_set |
+------+----------+
| 1101 | a,c,d |
+------+----------+
1 row in set (0.00 sec)
mysql> select bin(23) as BIN, make_set(23,'a','b','c','d','e','f','g') as make_set;
+-------+----------+
| BIN | make_set |
+-------+----------+
| 10111 | a,b,c,e |
+-------+----------+
1 row in set (0.00 sec)
mysql> select bin(23) as BIN, make_set(23,'a','b') as make_set;
+-------+----------+
| BIN | make_set |
+-------+----------+
| 10111 | a,b |
+-------+----------+
7、從現有字符串中截取子字符串
left(str,num)
mysql> select left('algfdg',3) as 'left';
+------+
| left |
+------+
| alg |
+------+
right(str,num)
mysql> select right('algfdg',3) as 'right';
+-------+
| right |
+-------+
| fdg |
+-------+
substring(str,num,len)和mid(str,num,len)：截取指定位置和長度的子字符串
mysql> select substring('algfdg',3,4) as 'substring',mid('algfdg',3,4) as 'mid';
+-----------+------+
| substring | mid |
+-----------+------+
| gfdg | gfdg |
+-----------+------+
8、去除字符串的首尾空格
ltrim(str)
mysql> select ltrim(' mysql') as ' mysql';
+-------+
| mysql |
+-------+
| mysql |
+-------+
1 row in set, 1 warning (0.00 sec)
mysql> select ' mysql' as ' mysql';
+----------+
| mysql |
+----------+
| mysql |
+----------+
1 row in set, 1 warning (0.00 sec)
right(str)
mysql> select rtrim('mysql ') as 'mysql';
+-------+
| mysql |
+-------+
| mysql |
+-------+
1 row in set (0.00 sec)
mysql> select 'mysql ' as 'mysql';
+-------------+
| mysql |
+-------------+
| mysql |
+-------------+
trim(str)
mysql> select trim(' mysql ') as ' mysql ';
+---------+
| mysql |
+---------+
| mysql |
+---------+
9、替換字符串
insert(str,pos,len,newstr):
mysql> select '這是mysql數據庫' as 'oldstring',insert('這是mysql數據庫',3,5,'oracle') as 'newstring';
+-----------------+------------------+
| oldstring | newstring |
+-----------------+------------------+
| 這是mysql數據庫 | 這oracleql數據庫 |
+-----------------+------------------+
replace(str,substr,newstr)
mysql> select '這是mysql數據庫' as 'oldstring',replace('這是mysql數據庫','mysql','oracle') as newstring;
+-----------------+------------------+
| oldstring | newstring |
+-----------------+------------------+
| 這是mysql數據庫 | 這是oracle數據庫 |
+-----------------+------------------+
數值函數
abs(x) 返回數值x的絕對值
ceil(x) 向上取整
float(x) 向下取整
mod(x,y) 返回x模y的值
rand() 返回0~1內的隨機數
rand(n) 指定種子
round(x,y) 返回x的四捨五入後有y位小數的數值
truncate(x,y) 返回數值x截斷y位小數的數值
mysql> select truncate(903.2432,2),truncate(902.324534,-2);
+----------------------+-------------------------+
| truncate(903.2432,2) | truncate(902.324534,-2) |
+----------------------+-------------------------+
| 903.24 | 900 |
+----------------------+-------------------------+
日期和時間函數
curdate()
curtime()
now()
unix_timestamp(date)
from_uinxtime()
week(date)
year(date)
hour(time)
minute(time)
monthname(date)
1、獲取當前日期和時間
now(),current_timestamp(),localtime(),sysdate();
推薦使用now()
mysql> select now() as 'now',current_timestamp() as 'current_timestamp',localtime() as 'localtime',sysdate() as 'sysdate';
+---------------------+---------------------+---------------------+---------------------+
| now | current_timestamp | localtime | sysdate |
+---------------------+---------------------+---------------------+---------------------+
| 2015-04-29 21:28:38 | 2015-04-29 21:28:38 | 2015-04-29 21:28:38 | 2015-04-29 21:28:38 |
+---------------------+---------------------+---------------------+---------------------+
2、獲取當前日期
curdate(),current_date()
推薦使用curdate()
mysql> select current_date() as 'current_date',curdate() as 'curdate';
+--------------+------------+
| current_date | curdate |
+--------------+------------+
| 2015-04-29 | 2015-04-29 |
+--------------+------------+
3、獲取當前時間
curtime()和current_time()
推薦使用curtime()
mysql> select current_time() as 'current_time',curtime() as 'curtime';
+--------------+----------+
| current_time | curtime |
+--------------+----------+
| 07:00:27 | 07:00:27 |
+--------------+----------+
4、通過各種方式顯示日期和時間
(1)unix方式顯示
unix_timestamp():返回時間戳格式的時間
from_unixtime():將時間戳格式時間轉換成普通格式的時間
mysql> select now() as '當前時間',unix_timestamp(now()) 'unix格式',from_unixtime(unix_timestamp(now())) as '普通格式';
+---------------------+------------+---------------------+
| 當前時間 | unix格式 | 普通格式 |
+---------------------+------------+---------------------+
| 2015-04-30 07:04:21 | 1430348661 | 2015-04-30 07:04:21 |
+---------------------+------------+---------------------+
注意：unix_timestamp()函數沒有參數傳入，則會顯示出當前時間和日期的時間戳形式，如果傳入了某個時間參數，則會顯示所傳入時間的時間戳。
(2)通過UTC方式顯示日期和時間
UTC，即國際協調時間
utc_date()：實現日期
utc_time()：實現時間
mysql> select now() as 'now',utc_date() as 'utc date',utc_time() as 'utc time';
+---------------------+------------+----------+
| now | utc date | utc time |
+---------------------+------------+----------+
| 2015-04-30 07:14:49 | 2015-04-29 | 23:14:49 |
+---------------------+------------+----------+
注意：返回的時間與現在的時間有8小時之差
5、獲取日期和時間各部分值
year() :日期的年份
quarter() :日期所屬第幾個季度
month() :月
week() :日期屬於第幾個星期
dayofmonth() :屬於當前月的第幾天
hour() :時間的小時
minute() :分鐘
second() :秒
mysql> select
-> now() as 'now',
-> quarter(now()) as 'quarter',
-> month(now()) as 'month',
-> week(now()) as 'week',
-> dayofmonth(now()) as 'day',
-> hour(now()) as 'hour',
-> minute(now()) as 'minute',
-> second(now()) as 'second';
+---------------------+---------+-------+------+------+------+--------+--------+
| now | quarter | month | week | day | hour | minute | second |
+---------------------+---------+-------+------+------+------+--------+--------+
| 2015-04-30 07:29:19 | 2 | 4 | 17 | 30 | 7 | 29 | 19 |
+---------------------+---------+-------+------+------+------+--------+--------+
1、月
month() :1~12
monthname() :月份的英文名字
mysql> select now() as 'now',
-> month(now()) as 'month',
-> monthname(now()) as 'month';
+---------------------+-------+-------+
| now | month | month |
+---------------------+-------+-------+
| 2015-04-30 07:33:08 | 4 | April |
+---------------------+-------+-------+
2、星期
week()和weekofyear() :返回日期和時間中星期是當前年的第幾個星期，范圍為1~53
實際應用中，經常不需要實現上述功能
mysql> select now() as 'now',week(now()) as 'week',weekofyear(now()) as 'weekofyear';
+---------------------+------+------------+
| now | week | weekofyear |
+---------------------+------+------------+
| 2015-04-30 07:41:59 | 17 | 18 |
+---------------------+------+------------+
dayname() :星期的英文名
dayofweek() :星期幾，1~7，星期日為1
weekday() :星期幾，0~6,0表示星期一，依次類推
mysql> select now() as 'now',dayname(now()) as 'dayname',dayofweek(now()) as 'dayofweek',weekday(now()) as 'weekday';
+---------------------+----------+-----------+---------+
| now | dayname | dayofweek | weekday |
+---------------------+----------+-----------+---------+
| 2015-04-30 07:45:08 | Thursday | 5 | 3 |
+---------------------+----------+-----------+---------+
mysql> select now() as 'now',dayname(now()) as 'dayname',dayofweek(now())-1 as 'dayofweek',weekday(now())+1 as 'weekday';
+---------------------+----------+-----------+---------+
| now | dayname | dayofweek | weekday |
+---------------------+----------+-----------+---------+
| 2015-04-30 08:07:24 | Thursday | 4 | 4 |
+---------------------+----------+-----------+---------+
3、天
dayofmonth() :月中的第幾天
dayofyear() :年中的第幾天
mysql> select now() as 'now',dayofmonth(now()) as 'dayofmonth',dayofyear(now()) as 'dayofyear';
+---------------------+------------+-----------+
| now | dayofmonth | dayofyear |
+---------------------+------------+-----------+
| 2015-04-30 07:52:37 | 30 | 120 |
+---------------------+------------+-----------+
1 row in set (0.00 sec)
4、獲取指定時間域的值
extract() :extract,提取，獲取
extract(type from date)
type:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND :天和毫秒
DAY_SECOND :天和秒
DAY_MINUTE :天和分鐘
DAY_HOUR :天和小時
YEAR_MONTH :年和月
mysql> select now() as 'now',extract(year from now()) as 'year',extract(month from now()) as 'month',extract(day from now()) as 'day',extract(hour from now()) as 'hour',extract(minute from now()) as 'minute',extract(second from now()) as 'second',extract(week from now()) as 'week',extract(hour_minute from now()) as 'hour_minute',extract(quarter from now()) as 'quarter',extract(day_second from now()) as 'day_second' ;
+---------------------+------+-------+------+------+--------+--------+------+-------------+---------+------------+
| now | year | month | day | hour | minute | second | week | hour_minute | quarter | day_second |
+---------------------+------+-------+------+------+--------+--------+------+-------------+---------+------------+
| 2015-04-30 08:14:22 | 2015 | 4 | 30 | 8 | 14 | 22 | 17 | 814 | 2 | 30081422 |
+---------------------+------+-------+------+------+--------+--------+------+-------------+---------+------------+
6、計算日期和時間的函數
1、計算與默認日期和時間（0000年1月1日）相互操作的函數，
to_days(date)
計算日期參數date與默認日期和時間(0000年1月1日)之間相隔天數
from_days(number)
該函數計算從默認日期和時間(0000年1月1日)開始經歷number天後的日期和時間
datediff(date1,date2)
計算date1和date2之間相隔天數
mysql> select now() as 'now',to_days(now()) as 'toDays',from_days(to_days(now())+7) as 'from_days',datediff(now(),'2000-12-01') 'datediff';
+---------------------+--------+------------+----------+
| now | toDays | from_days | datediff |
+---------------------+--------+------------+----------+
| 2015-04-30 08:27:11 | 736083 | 2015-05-07 | 5263 |
+---------------------+--------+------------+----------+
2、計算與指定日期和時間相互操作的函數
adddate(date,n)
subdate(date,n)
mysql> select curdate() as 'curdate',adddate(curdate(),5) as 'five day after',subdate(curdate(),5) as 'five day before';
+------------+----------------+-----------------+
| curdate | five day after | five day before |
+------------+----------------+-----------------+
| 2015-04-30 | 2015-05-05 | 2015-04-25 |
+------------+----------------+-----------------+
adddate(d,interval expr type)
subdate(d,interval expr type)
interval:間隔，區間
type的值
SECOND 秒 ss
MINUTE 分鐘 mm
HOUR 小時 hh
DAY 日 DD
MONTH 月 MM
YEAR 年 YY
MINUTE_SECOND 分鐘和秒 mm和ss之間用任意符號隔開
HOUR_SECOND 小時和秒 hh和ss之間用任意符號隔開
HOUR_MINUTE
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
mysql> select curdate() as 'curdate',adddate(curdate(),interval '2,3' year_month) as 'two year three month after';
+------------+----------------------------+
| curdate | two year three month after |
+------------+----------------------------+
| 2015-04-30 | 2017-07-30 |
+------------+----------------------------+
1 row in set (0.00 sec)
mysql> select curdate() as 'curdate',adddate(curdate(),interval '2,3' day_minute) as 'two year three month after';
+------------+----------------------------+
| curdate | two year three month after |
+------------+----------------------------+
| 2015-04-30 | 2015-04-30 02:03:00 |
+------------+----------------------------+
注意：第二個查詢語句沒有出現想要的結果
addtime()
subtime()
mysql> select curtime() as 'curtime',addtime(curtime(),5) as 'five second after',subtime(curtime(),5) as 'five second before';
+----------+-------------------+--------------------+
| curtime | five second after | five second before |
+----------+-------------------+--------------------+
| 08:42:08 | 08:42:13 | 08:42:03 |
+----------+-------------------+--------------------+
1 row in set (0.01 sec)
mysql> select curtime() as 'curtime',addtime(curtime(),5*60) as 'five minute after',subtime(curtime(),5*60) as 'five minute before';
+----------+-------------------+--------------------+
| curtime | five minute after | five minute before |
+----------+-------------------+--------------------+
| 08:42:54 | 08:45:54 | 08:39:54 |
+----------+-------------------+--------------------+
系統信息函數
version()
database()
user()
last_insert_id()
mysql> select version() as 'version',database() as 'database',user() as 'user',last_insert_id() as 'auto_increment_id'
+--------------------------+----------+----------------+-------------------+
| version | database | user | auto_increment_id |
+--------------------------+----------+----------------+-------------------+
| 5.0.51b-community-nt-log | test3 | root@localhost | 0 |
+--------------------------+----------+----------------+-------------------+
