MySQL 函数

发布于 2024-03-14 00:21:52 字数 12363 浏览 48 评论 0

数学函数

#绝对值，π，平方根，去余函数（适用小数)
select abs(-1),pi(),sqrt(9),Mod(31,8),Mod(45.5,6);

#获取整数的函数
select ceil(-3.5),ceiling(3.5),floor(-3.5),floor(3.5);

#获取随机数的函数
select rand(),rand(),rand(10),rand(10);

#Round 函数（四舍五入函数)，truncate() 函数
select round(3.4),(3.6),round(3.16,1),round(3.16,0),round(232.28,-1),truncate(1.31,1),truncate(1.99,1),truncate(19.99,-1);

#符号函数，幂运算函数 pow,power,exp()//e 的 x 乘方
select sign(-21),sign(0),sign(21),pow(2,2),power(2,-2),exp(2);

#自然对数运算和以 10 为底的对数运算,弧度，角度 radians 角度转弧度，弧度转角度
select log(3),log(-3),log10(100),log10(-100),radians(180),degrees(pi()/2);

#正弦函数余弦函数
select sin(pi()/2),degrees(asin(1)),cos(pi()),degrees(acos(-1)),round(tan(pi()/4)),degrees(atan(1)),cot(pi()/4);

字符串函数

#字符串函数,concat_ws 忽略空值 null
select char_length('aab'),length('aabb'),concat('My sql ','5.7'),concat('My',null,'sql'),concat_ws('-','a','b','c'),concat_ws('*','aa',null,'bb');

#替换字符串的函数
select insert('Quest',2,4,'What') as Coll,insert('Quest',-1,4,'What') as Coll2,insert('Quest',3,100,'Wh') as Coll3;

#大小写转换,获取指定长度字符串的函数 left,right;
select lower('ZHENGXIN'),lcase('ZHENGXIN'),upper('zhengxin'),ucase('zhengxin'),left('football',5),right('football',5);

#填充字符串的函数,删除空格的函数
select lpad('hello',4,'*'),lpad('hello',10,'*'),
rpad('hello',10,'*'),concat('(',ltrim('   book   '),')'),
concat('(',rtrim('   book   '),')'),
concat('(',trim('   book   '),')'),
trim('xy' from 'xyxyabababxyxy');

#重复生成,空格函数，替换函数，比较大小的函数
select repeat('mysql',3),concat('(',space(6),')'),
replace('xxx.baidu.com','x','w'),strcmp('abc','abd');

#获取字串的函数
select substring('breakfast',5) as coll,
substring('breakfast',3,5) as coll2,
substring('breakfast',-3) as coll3, #从后面开始截取 3 个
substring('breakfast',-1,4) as coll4; #从结尾开始第一个位置截取四个

注意还有一个 MID 函数和 substring 作用是一样的

#匹配字串开始的位置,字符串逆序
select locate('ball','football'),position('ball'in'football'),
instr('football','ball'),reverse('abc');

#返回指定位置的值,返回指定字符串的位置的函数
select elt(3,'a','b','c'),elt(2,'a'),
field('Hi','hihi','Hey','Hi','bas') as coll,
field('Hi','hihi','a','b') as coll2,
find_in_set('Hi','hihi,Hey,Hi,bas'); #返回字串位置的函数

#make_set() 函数的使用
select make_set(1,'a','b','c') as coll,#0001 选第一个
make_set(1|4, 'hello','nice','word') as coll2, #0001 0100-->0101 -->选第一和第三
make_set(1|4,'hello','nice',null,'word') as coll3,#0001 0100-->0101 -->选第一和第三
make_set(0,'a','b','c') as coll4;

日期和时间函数

#获取日期时间函数
select current_date(),curdate(),curdate()+0,
current_time(),curtime(),curtime()+0,
current_timestamp(),localtime(),now(),sysdate();

#获取时间的数字,根据时间获取日期（互为反函数)
select unix_timestamp(),unix_timestamp(now()),now(),
from_unixtime(1523689758);

#返回当前时区日期和时间的函数,日期月份时间函数
select utc_time(),utc_time()+0,
utc_date(),utc_date()+0,
month('2016-03-04'),monthname('2016-03-04'),
dayname('2018-04-14'),dayofweek('2018-04-14'),
weekday('2018-04-14');

效果

注意 dayofweek 和 weekday 的差别

#返回是这一年的第几周
select week('2018-4-16'),#默认 0 表示第一天从周末开始
week('2018-04-16',1), #周一#返回是这一年的第几周
dayofyear('2018-4-16'),dayofmonth('2018-4-14'), #返回一年中的第几天
year('2018-4-14'),quarter('2018-4-14'),
minute('10:10:02'),second("10:10:02");

#获取指定日期的指定值的函数
select extract(year from '2018-07-06') as coll,
extract(year_month from '2018-08-06') as coll2,
extract(day_minute from '2018-07-06 10:11:05') as coll3;

#时间和秒钟转换的函数
select time_to_sec('01:00:40'),
sec_to_time(3600);

#计算日期和时间的函数
select date_add('2010-12-31 23:59:59',interval 1 second) as coll,
adddate('2010-12-31 23:59:59',interval 1 second) as coll2,
date_add('2010-12-31 23:59:59',interval '0:0:1' hour_second) as coll3, #后面的 hour_second 要看表决定
date_sub('2011-01-02',interval 31 day) as coll4,
subdate('2011-01-02',interval 31 day) as coll5,
date_sub('2011-01-02 00:01:00',interval '0 0:1:1' day_second) as coll6; #对应位置的相减

#直接输入两个时间，计算
select addtime('2000-12-31 23:59:59','1:1:1') as coll,
subtime('2000-12-31 23:59:59','1:1:1')as coll2,
datediff('2000-12-28','2001-01-03') as coll3; #前面的减后面的

注意看表

日期和时间格式化的函数

#时间日期格式化函数
select date_format('1997-10-04 22:23:00','%W %M %Y') as coll,
date_format('1997-10-04 22:23:00','%D %y %a %d %m %b %j'),
time_format('16:00:00','%H %k %h %I %l'),
date_format('2000-10-05 22:23:00',get_format(date,'USA'));

条件约束函数

#条件约束函数
select if(1>2,2,3),
ifNull(null,10),ifNull(1/0,100),
case 2 when 1 then 'one' when 2 then 'two' when 3 then 'three' else 'more' end, #2 等于后面的 2 返回后面的 then
case when 1>2 then 'a' else 'b' end;

系统信息函数

#系统信息函数
select version(),connection_id(),#版本号，连接次数
database(),schema(), #查看当前的数据库名
user(),current_user(),system_user(),session_user();
show processlist;#输出当前用户的连接信息

#获取字符串的字符集和排列方式的函数
select charset('abc'),charset(convert('abc' using latin1)),
charset(version()), #获取字符集
collation('abc'),collation(convert('abc' using utf8));#获取排列方式

还要注意 Last_insert_id 最后自动生成的 ID 值

加/解密函数

#加密解密函数
select password('newpwd'),MD5('mypwd'), 
encode('secret','cry'),length(encode('secret','cry')),
decode(encode('secret','cry'),'cry');#加密后解密

其他函数

#其他函数
select format(123.1234,2),format(123.1,3),format(123.123,0),#格式化函数
#不同进制数之间的转换
conv('a',16,2),conv(15,10,2),conv(15,10,8),conv(15,10,16);

#IP 地址与数字相互转换的函数
select inet_aton('209.207.224.40'),inet_ntoa(3520061480),
#枷锁函数和解锁函数
get_lock('lock1',10),#这个锁持续 10 秒
is_used_lock('lock1'),  #返回当前连接 ID
is_free_lock('lock1'), #是否是可用的
release_lock('lock1');

#重复执行指定操作的函数
select benchmark(5000,password('newpad')),
charset('abc'),charset(convert('abc' using latin1)),#改变字符集的函数
cast(100 as char(2)),convert('2010-10-11 12:12:12',time);#改变数据类型的函数

综合案列-Mysql 函数的使用

select round(rand() * 10),round(rand() * 10),round(rand() * 10);#产生三个 1-10 之间的随机数
select pi(),sin(pi()),cos(0),round(tan(pi()/4)),floor(cot(pi()/4));

create database test_db3;
use test_db3;
show tables;
create table member
(
	m_id int(11) primary key auto_increment,
	m_FN varchar(15),
	m_LN varchar(15),
	m_brith datetime,
	m_info varchar(15) null
);

insert into member values(null,'Halen','Park','1970-06-29','GoodMan');

select length(m_FN),#返回 m_FN 的长度
concat(m_FN,m_LN),#返回第一条记录中的全名
lower(m_info),#将 m_info 转换成小写
reverse(m_info) from member;

select year(curdate())-year(m_brith) as age,#计算年龄
dayofyear(m_brith) as days,
date_format(m_brith,'%W %D %M %Y') as birthDate from member;

insert into member values(null,'Samuel','Green',now(),null);

select last_insert_id(); #输出最后插入的自增的编号

select m_brith,case when year(m_brith) < 2000 then 'old' 
when year(m_brith) > 2000 then 'young' 
else 'not born' end as status from member;

分享到QQ

分享到微博