MySQL 内置函数

函数

日期函数

1. current_date()显示年月日

select current_date();

2. current_time()显示时分秒

select current_time();

3. current_timestamp()显示年月日时分秒
4. now()显示当前日期时间，其实和timestamp是一样的

select current_timestamp();
select now();

5. date(datetime)返回datetime的日期部分

// 结果是1991-10-01
select date('1991-10-01 00:00:00');
// 拿到当前年月日
select date(now());

6. date_add(date，interval d_value_type)，date可以是日期或者是时间，interval后面可以是年，分钟，秒，天
7. date_sub(date,interval d_values_type)，减时间和加时间是差不多的

// 加时间
select date_add('2050-01-01',interval 10 day);
select date_add(now(),interval 10 minute);
select date_add(now(),interval 10 second);
select date_add(now(),interval 10 year);
// 减时间
select date_sub(now(),interval 10 day);
select date_sub('2050-01-01',interval 10 day);

8. datediff(date1,date2)是用前者减后者的，单位是天

select datediff('2050-01-01','2049-01-01');
select datediff(date(now()),'1949-10-01');

10. 这个日期函数有什么作用呢？
    在公司中，可以用当前时间减去入职时间等于你的工龄

11. 日期：年月日，时间：时分秒

日期函数的案例

• 创建一张表，记录生日

create table tmp(
id bigint primary key auto_increment,
birthday date not null
);
insert into tmp (birthday) values(current_date());
insert into tmp (birthday) values('1990-01-01');
insert into tmp (birthday) values(date(current_timestamp()));

1.虽然current_date()只显示时分秒，但是所有的时间函数都是有年月日时分秒的，所以插入当前时间和时间戳是可以当成日期插入的，可以理解隐式类型的转换

• 创建一张留言表

create table msg (
id int primary key auto_increment,
content varchar(30) not null,
sendtime datetime
);

insert into msg(content,sendtime) values('恐惊天上人',now());
// 显示所有留言信息，发布日期只显示日期，不用显示时间
select content,date(sendtime) from msg; 

请查询在2分钟内发布的帖子

// 第一种写法:
select content,sendtime from msg where sendtime > date_sub(now(),interval 2 minute);
// 第二种写法:
select content,sendtime from date_add(sendtime,interval 2 minute) > now();

字符串函数

1. charset(str)查看字符串的字符集

select charset('abcd');
select charset('中国');
// 获取emp表的ename列的字符集
// 可以通过查询列的字符集确定表中乱码的问题
select charset(salary);// 二进制 binary
select charset(ename) from emp;// utf8

2. concat连接字符串

select concat('a','b');// ab
select concat('a','b','c') as res;// abc
select concat('a','b','c',123,11.23);// abc12311.23
转为字符串进行拼接

3. instr(string,substring)，判断substring是否在string中，如果在返回substring在string中出现的位置，否则返回0

// 下标从1开始计数
select instr('abcd12345','1234');

4. ucase(string)，转换为大写
5. lcase(string)，转换为小写

select ucase('abcd1234ABCD');
select lcase('abcd1234ABCD');

5. left(string,lenth)从string的左边起取lenth个字符
6. right(string,lenth)从string的左边到结尾取lenth个字符

select left('1234abcd',5);// 1234a
select right('1234abcd',5)// 4abcd

7. length(string)求string的长度

select length('abcdef');// 6
select length('');// 0 空串
select length(' ');// 1 有一个空格的字符串

字符串函数样例

• 案例

1. 要求显示exam_result表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”

select concat(name,'的语文成绩是',chinese,'的数学成绩是',math,'的英语成绩是',english) as '分数' from student;

2. 求学生表中学生姓名占用的字节数

// length计算出学生姓名的字节数
select name,length(name) from exam_result
// 一个汉字是3个字节
// 一个数字，1是一个字节
// 一个字母，a是一个字节

3. 将EMP表中所有名字中有S的替换成’上海’
4. replace(str,search_str,replace_str),在str中用replace_str替换search_str

select ename,replace(ename,'S','上海') from emp;

5. 截取EMP表中ename字段的第二个到第三个字符
6. substring(str,position,length)，从str的position位置起，取length个字符

select substring(ename,2,2),ename from emp;

7. 以首字母小写的方式显示所有员工的姓名
8. 不会对原本的数据做更改，只是改了你查找出来的临时表的内容，update可以对原表做更改

// 把第一个字符和后面字符做分割，把第一个字母转为小写
// 最后拼接二个字符串
select ename,concat(lcase(substring(ename,1,1)),substring(ename,2)) from emp;

9. ltrim(string)，rtrim(string),trim(string) 去除左空格，去除右空格，左右空格都去除

10. 中间空格不做去除

11. 去除空格的作用：可以限制用户前面不能加空格，后面不能加空格，只能中间加空格，第二就是网络传输过程中多出了空格，并把它加到了数据库中，不做去除空格的话，用户是登入不了的

12. strcmp(string1,string2),逐字符地比较字符串的大小

数学函数及其样例

1. abs(number)，绝度值函数

select abs(12);// 12
select abs(-12);// 12
select abs(-12.3);// 12.3

2. bin(decimal_number),十进制转二进制，只能转整数，浮点数也会被当成整数

select bin(7);// 111
select bin(3.14)// 11

3. hex(decimal_number)，将十进制转为16进制

select hex(11);// B
select hex(16);// 10

4. conv(number,from_base,to_base)，进制转换，把一个数的一个进制转换为另一个进制

// 将10从10进制转为2进制
select conv(10,10,2);// 1010

5. format(number,decimal_places),四舍五入保留小数的精度

select format(3.1415926,2);// 3.14
select format(3.1415926,3);// 3.142

6. mod(number,denominator),取模

select mod(10,3);// 1
select mod(-10,3);// -1
select mod(-10,-3);// -1 

7. rand();返回随机数，[0.0,1.0)

select rand();
// 生成0到99的随机数并保留0为小数的精度
select format(rand()*100,0);

8. ceiling(number),向上取整
9. floor(number),向下取整

select ceiling(-3.1);// -3
select ceiling(3.1);// 4
select ceiling(3.0);// 3

select floor(3.1);// 3
select floor(6.2);// 6
select floor(-3.1);// -4

10. 四中取整方式：
    

其他函数

1. select use();查看当前用户名
2. @前面是用户名，后面是登入机

3. select databae();显示当前正在使用的数据库
4. md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串（用户密码不能在数据库表里面明文保存！！万一表结构被盗取了密码就泄密了）
5. 包含password字样的，数据库中翻历史命令是找不到的

// 形成一个32位的等长字符串，进行了加密处理 
select md5('123');

6. 用户输入密码的时候，它的密码也会被转为md5，查看是否匹配
   
7. 数据库内部也有自己的加密函数，password()，加密后的函数也是等长的
   
8. ifnull(str1,str2)，如果str1为null，返回str2，如果str1不为null，返回str1，不管str2是任何东西

select ifnull(null,20);// 20
select ifnull(20,30);// 20
select ifnull(20,null);// 20
select ifnull(20,30);// 20