目录
字符串函数
1.char_length(' '):计算字符串字符的长度
select char_length('abcd我');
运行结果如下:
2.length(' '):计算字符串的长度。这里需要注意的是,一个汉字长度是3。
select length('abcd我');
运行结果如下:
3.concat(' ',' '...):字符串拼接
select concat('我','ni');
运行结果如下:
4.concat_WS('分隔符',' ',' '...):用指定的符号拼接
select concat_WS('-','ni','我')
运行结果如下:
5.group_concat(distinct FIELD_NAME order by FIELD_NAME[DESC]) :配合分组查询,进行行转列
举例说明:比如在一张成绩表中,查询学生学了哪些课程。
select student_id,course_id from score;
这句话会将所有学生所有学的课程列出来,导致会有很多行。
如果使用group_concat,按照student_id分组,将每个学生的课程行转列,变成一行来显示,就会看得一目了然。
6.insert(s1,x,len,s2):从字符串s1的x位置开始,被字符串s2取代len个字符。
select insert('我爱你',2,2,'哈哈哈');
运行结果如下:
7.lower(' '):转小写
select lower('A我A');
运行结果如下:
8.upper(''):转大写
select upper('a我a');
运行结果如下:
9.left('',n):字符串从最左边开始的n个字符
select left('abcd',3);
运行结果如下:
10.right('',n):字符串从最右边开始的n个字符
select right('efgh',3);
运行结果如下:
11.mid('',n,len):从字符串的第n个位置开始,取len个字符
select mid('efgh',2,2);
运行结果如下:
12.lpad(s1,len,s2):左填充
select lpad('abc',5,'cd');
运行结果如下:
13.rpad(s1,len,s2):右填充
select rpad('abc',5,'cd');
运行结果如下:
14.ltrim(''):左去空
select ltrim(' abc');
运行结果如下:
15.rtrim(''):右去空
select rtrim('abc ');
运行结果如下:
16.trim(''):两端去空
select trim(' abc ');
运行结果如下:
17.trim(s1 from s):删除s两端的所有s1
select trim('ab' from 'bcdabefhab');
运行结果如下:
18.repeat('',n):让字符串重复n次
select repeat('abc',3);
运行结果如下:
19.space(n):返回由n个空格组成的字符串
select concat('我爱你',space(4),'他爱她');
运行结果如下:
20.replace('1','2','3'):将字符串1包含字符串2的部分由参数3替代
select replace('abcd','bc','-');
运行结果如下:
21.strcmp(s1,s2):比大小,返回0,1,-1。若为-1,则s1<s2;若为1,则s1>s2;若为0,则s1=s2
select strcmp('ab','ad');
运行结果如下:
select strcmp('afce','adcd');
运行结果如下:
select strcmp('ad','ad');
运行结果如下:
22.locate('1','2'):判断2中是否有1,有则返回其第一次出现的位置
select locate('ab','bcdabefgab');
运行结果如下:
23.reverse(''):反转字符串
select reverse('abc');
运行结果如下:
24.elt(N,'',''...):返回第N个字符串。根据参数1指定的位置,提取以参数2为1起,之后的对应位置的值,若位置不存在返回null
select elt(2,'a','v','c','d');
运行结果如下:
25.field('1','2','3',...):返回2,3之后的所有字符串中第一次出现与1匹配的位置,没有返回0。
select field('ab','cd','ab');
运行结果如下:
26.find_in_set('1',',,,...'):返回1在后面很多字符串(以逗号分割的字符串)中能匹配上的位置,匹配不上,返回0
select find_in_set('b','abbcd,abc,b');
运行结果如下:
27.make_set(x,'1','2'...):根据参数1转换成二进制后的结果,保留和集合元素数量位低位值,然后逆序匹配提取子集合,1就提取,0不提取
select make_set(25,'aa','bb','cc','dd','ee');
运行结果如下:
因为25转成二进制为11001,将二进制转一下为10011,所以结果为aa,dd,ee。
28.substring_index('str','分隔符','截取位置'):截取字符串
从左边开始截取到第三个逗号
select substring_index('我,你,他,她,它',',',3);
运行结果如下:
数学函数
1.abs(v NUMBER):绝对值
select abs(-1);
运行结果如下:
2.floor(v DECIMAL):去掉小数(不四舍五入)
select floor(3.456);
运行结果如下:
3.ceil(v DECIMAL):有小数就进1
select ceil(0.000001);
运行结果如下:
4.truncate(v DECIMAL,n INT):截断(不四舍五入)
select truncate(3.35567,1);
运行结果如下:
5.round(v DECIMAL):返回离参数最近的整数,四舍五入
select round(3.56);
运行结果如下:
6.round(v DECIMAL,n INT):保留几位小数,四舍五入
select round(3.564,1);
运行结果如下:
7.rand():返回0~1之间的随机小数
select rand();
运行结果如下:
8.sign(v NUMBER):返回数字是否为正数还是负数。正数为1,负数为-1
select sign(3.56);
运行结果如下:
9.pi():返回圆周率的值(共7位)
select pi();
运行结果如下:
10.pow(b NUMBER,p NUMBER):幂运算
select pow(2,3);
运行结果如下:
11.sqrt(b NUMBER):开平方根
select sqrt(4);
运行结果如下:
12.exp(n NUMBER):返回e的x乘方后的值
select exp(2);
运行结果如下:
13.mod(n NUMBER,m NUMBER):n%m取余
select mod(3,5);
运行结果如下:
14.log2(x):返回x相对于基数2的对数
select log2(16);
运行结果如下:
15.log10(x):返回x相对于基数10的对数
select log10(10);
运行结果如下:
16.radians(d NUMBER):角度转弧度
select radians(30);
运行结果如下:
17.degree(r NUMBER):弧度转角度
select degrees(0.5235987755982988);
运行结果如下:
日期函数
1.now():返回当前日期时间
select now();
运行结果如下:
2.curdate():返回当前日期
select curdate();
运行结果如下:
3.curtime():返回当前时间
select curtime();
运行结果如下:
4.unix_timestamp():获取到当前日期的时间的秒数
select unix_timestamp();
运行结果如下:
5.unix_timestamp(['yyyy-MM-dd hh:mm:ss sss']):获取到指定日期的时间的秒数
select unix_timestamp('2020-2-13');
运行结果如下:
select UTC_DATE();
运行结果如下:
6.from_unixtime(v BIGINT):返回秒数对应的日期时间
select from_unixtime(1581523200);
运行结果如下:
7.UTC_DATE():返回格式为"YYYY-MM-DD"的当前UTC日期值
select UTC_DATE();
运行结果如下:
8.UTC_TIME():返回格式为"hh:mm:ss"的当前UTC时间值
select UTC_TIME();
运行结果如下:
9.year('日期'):返回日期中的年份值,范围是1~12
select year('2020-10-3');
运行结果如下:
10.month('日期'):返回日期中的月份值,范围是1~12
select month('2020-10-3');
运行结果如下:
11.week('日期'):计算日期是本年的第几个星期,范围是0~53
select week('2020-10-3');
运行结果如下:
12.weekofyear('日期'):计算日期是本年的第几个星期,1~53
select weekofyear('2020-10-3');
运行结果如下:
13.day('日期'):返回日期中的天数
select day('2020-10-3');
运行结果如下:
14.monthname('日期'):返回日期中的月份名称
select monthname('2020-10-3');
运行结果如下:
15.dayname('日期'):返回日期是星期几
select dayname('2020-10-3');
运行结果如下:
16.quarter('日期'):日期是第几季度
select quarter('2020-10-3');
运行结果如下:
17.hour('时间'):时间的小时值
select hour(curtime());
运行结果如下:
18.minute('时间'):时间的分钟值
select minute(curtime());
运行结果如下:
19.second('时间'):时间的秒钟值
select second(curtime());
运行结果如下:
20.extract(type from date):从日期中提取一部分,type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND
select extract(year from curtime());
运行结果如下:
21.time_to_sec('时间'):将时间转换为秒
select time_to_sec(curtime());
运行结果如下:
22.sec_to_time(秒数):将以秒为单位的时间转换为时分秒的格式
select sec_to_time(27524);
运行结果如下:
23.to_days('日期'):计算日期d至0000年1月1日的天数
select to_days('0001-1-1');
运行结果如下:
24.from_days(n):计算从0000年1月1日开始n天后的日期
select from_days(729669);
运行结果如下:
25.dayofyear('日期'):本年的第几天
select dayofyear('2020-10-3');
运行结果如下:
26.dayofmonth('日期'):计算日期是本月的第几天
select dayofmonth('2020-10-3');
运行结果如下:
27.dayofweek('日期'):本周的第几天。1为星期日
select dayofweek('2020-10-3');
运行结果如下:
28.weekday('日期'):日期是星期几。0为星期一
select weekday('2020-10-3');
运行结果如下:
29.datediff(d1,d2):计算d1和d2间隔的天数
select datediff('2020-10-3','2020-10-20');
运行结果如下:
30.adddate(date,'正负值' YEAR|QUARTER|YEAR_MONTH|MONTH|DAY|...)
select adddate('2020-10-3',interval 1 year);
运行结果如下:
31.adddate('日期',n):在日期后加n天后的日期
select adddate('2020-10-3',5);
运行结果如下:
32.subtime('时间',n);
select subtime('12:03:45',10);
运行结果如下:
33.date_format(now(),'%Y-%m-%d %H:%i:%s'):格式化日期时间
select date_format('2020-10-03','%Y/%m/%d');
运行结果如下:
34.time_format('时间','格式'):格式化时间
select time_format(curtime(),'%H:%i:%s');
运行结果如下:
35.计算自己的周岁
select floor(datediff(now(),'1995-10-20')/365);
运行结果如下:
36.计算本周第一天:select adddate(date,interval concat('-',dayofweek(date))+2 day);
select adddate(curdate(),interval concat('-',dayofweek(curdate()))+2 day);
运行结果如下:
37.指定日期所在季度的第一天
select concat_WS('-',year(now()),(quarter(now())-1)*3+1,'1');
运行结果如下:
系统级函数
1.if(expr,v1,v2):如果表达式expr成立,执行v1,否则执行v2。
select if(2>1,1,2);
运行结果如下:
2.ifnull(v1,v2):如果v1不为空,则显示v1的值;否则显示v2的值
select ifnull(null,3);
运行结果如下:
select ifnull(2,3);
运行结果如下:
3.case when expr1 then v1 [when expr2 then v2 …][else vn] end:case表示函数开始,end表示函数结束。
如果表达式expr1成立,则返回v1的值;如果表达式expr2成立,则返回v2的值。依次类推,最后遇到else时,返回vn的值。
举例说明:按各科平均成绩从低到高和及格率的百分数从高到低顺序
select
course_id,avg(score) avg,sum(case when score>60 then 1 else 0 end)/count(1)*100 lv
from
score
group by
course_id
order by
avg,lv DESC;
运行结果如下:
4.case expr when e1 then v1 [when e2 then v2 …][else vn] end:case表示函数开始,end表示函数结束。
如果表达式expr取值为e1,则返回v1的值;如果表达式expr取值为e2,则返回v2的值,依次类推,最后遇到else,则返回vn的值
举例说明:学生成绩表中,按照学生ID,列出其语文数学英语成绩。
select
SC.student_id 学生ID,
max(case C.cname when '语文' then SC.score else 0 end) '语文',
max(case C.cname when '数学' then SC.score else 0 end) '数学',
max(case C.cname when '英语' then SC.score else 0 end) '英语'
from
score SC
left join
course C
on
SC.course_id=C.cid
group by SC.student_id;
运行结果如下:
5.database():查询当前所使用的数据库
select database();
运行结果如下:
6.password(src_str):加密
select password('abc');
运行结果如下:
7.md5(src_str):单向加密
select md5('abc');
运行结果如下:
8.encode(src_str,pass_str):用参数2对参数1进行加密
select encode('abc','def');
运行结果如下:
9.decode(crypt_str,pass_str):使用参数2来为参数1解密
select decode(encode('abc','def'),'def');
运行结果如下:
聚合函数
1.min(NUMBER_FIELD_NAME):最小值
2.max(NUMBER_FIELD_NAME):最大值
3.sum(NUMBER_FIELD_NAME):求和
4.avg(NUMBER_FIELD_NAME):求平均值
5.count(1/distinct FIELD_NAME):计数