内置函数
- 一. 日期函数
- 案例1：创建生日表
- 案例2：创建留言表
- 二. 字符串函数
- 三. 数学函数
- 四. 其它函数
- 案例：创建登入表
- 五. SQL 实战
一. 日期函数
| 函数名称 | 描述 |
|---|---|
| current_date() | 当前日期：年月日 |
| current_time() | 当前时间：时分秒 |
| currrent_timestamp() | 当前时间戳：年月日时分秒 |
| date(datetime) | 返回datetime参数的日期部分 |
| date_add(date, interval d_value_type) | 在date中添加日期或时间，interval后的数值单位可以是：年月日时分秒 |
| date_sub(date, interval d_value_type) | 在date中添加日期或时间，interval后的数值单位可以是：年月日时分秒 |
| datediff(date1, date2) | 两个日期相差的天数 |
| now() | 当前日期时间，与时间戳的返回值相同 |
current_date、current_time、current_timestamp 函数
# 查看当前 日期
select current_date();# 查看当前 时间
select current_time();# 查看当前 日期时间
select current_timestamp();
now、date 函数
# 查看当前 日期时间
select now();# 根据 日期时间 查看 日期
select date(current_timestamp());
select date(now());
date_add、date_sub、datediff 函数
# 日期+日期
select date_add(current_date(), interval 10 day);# 日期+时间
select date_add(now(), interval 10 hour);# 日期-日期
select date_sub(current_date(), interval 10 day);# 日期-时间
select date_sub(now(), interval 10 hour);# 日期相差的天数
select datediff(date(now()), '1949-10-1');
案例1：创建生日表
create table bir(id bigint primary key auto_increment,birthday date not null
);insert into bir (birthday) values ('2010-1-1');
insert into bir (birthday) values ('2010-1-3');
insert into bir (birthday) values (current_date());
insert into bir (birthday) values (current_time());
insert into bir (birthday) values (current_timestamp());
insert into bir (birthday) values (now());desc bir;
select * from bir;
案例2：创建留言表
create table msg(id bigint primary key auto_increment,content varchar(100) not null,sendtime datetime
);insert into msg (content, sendtime) values ('大江东去，浪淘尽，千古风流人物', now());
insert into msg (content, sendtime) values ('故垒西边，人道是，三国周郎赤壁', now());select * from msg;
# 显示所有留言信息，发布日期只显示日期，不用显示时间
select content, date(sendtime) from msg;
insert into msg (content, sendtime) values ('乱石穿空，惊涛拍岸，卷起千堆雪', now());
insert into msg (content, sendtime) values ('江山如画，一时多少豪杰', now());# 请查询在两分钟内发布的帖子
select * from msg where date_add(sendtime, interval 2 minute) > now();
二. 字符串函数
| 函数名称 | 描述 |
|---|---|
| charset(string) | 返回字符串的编码集（字符集） |
| concat(string1, string2, …) | 连接字符串 |
| instr(string, substring) | 返回substring在string出现的位置，没有返回0 |
| ucase(string) | 转换成大写 |
| lcase(string) | 转换成小写 |
| left(string, length) | 从string的左边起，提取length个字符 |
| right(string, length) | 从string的右边起，提取length个字符 |
| length(string) | 返回string的字节数 |
| strcmp(string1, string2) | 比较两字符串的大小 |
| substring(string, pos, length) | 从string的pos开始，提取length个字符 |
| replace(string, search_str, replace_str) | 在string中，用replace_str替换search_str |
| ltrim(string) | 去除左空格 |
| rtrim(string) | 去除右空格 |
| trim(string) | 去除左右空格，但是无法去除中间部分的空格 |
charset函数
获取emp表的ename列的字符集。
select * from emp;
select charset(ename) from emp;
concat 函数
要求显示exam表中的信息，显示格式：“…的语文成绩…，数学成绩…，英语成绩…分，总成绩…”：
select * from exam;
select name, Chinese, math, English, Chinese + math + English as 总成绩 from exam;select concat('考生姓名：', name, ' 语文成绩：', Chinese, ' 数学成绩：', math, ' 英语成绩：',
English, ' 总成绩：', Chinese + math + English) as msg from exam;
instr 函数
ucase、lcase 函数
以首字母大写，其余字母小写的方式显示emp表中所有员工的姓名。
select ename, concat(ucase(substring(ename, 1, 1)), lcase(substring(ename, 2))) from emp;
left、right 函数
length 函数
求exam表学生姓名占用的字节数。
select name, length(name) from exam;
注意：
- length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数。
- 如果是单字节字符则算作一个字节。比如：字母，数字算作一个字节，中文表示多个字节数（与字符集编码有关）
strcmp 函数
逐字符按照ASCII码比较两个字符串的大小，两个字符串大小相等返回0，前者大返回1，后者大返回-1
substring 函数
截取emp表中ename字段的第二个到第三个字符。
select ename, substring(ename, 2, 2) from emp;
注意：在 MySQL 中字符串下标从1开始。
replace 函数
将emp表中所有名字中有S的替换成 “上海”
select ename, replace(ename, 'S', '上海') from emp;
ltrim、rtrim、trim 函数
# 去除左空格
select ltrim(' 你好 ') as res;# 去除右空格
select rtrim(' 你好 ') as res;# 去除左右空格
select trim(' 你好 ') as res;# 无法去除中间的空格
select trim(' 你 好 ') as res;
三. 数学函数
| 函数名称 | 描述 |
|---|---|
| abs(number) | 返回 number 的绝对值 |
| bin(decimal_number) | 十进制转换二进制 |
| hex(decimal_number) | 十进制转换十六进制 |
| conv(number, from_base1, to_base2) | 将number从base1进制转换为base2进制 |
| ceiling(number) | 向上取整 |
| floor(number) | 向下取整 |
| format(number, n) | 保留 n 位小数 |
| rand() | 返回随机浮点数，范围[0, 1) |
| mod(number, n) | 返回number % n |
abs 函数
bin、hex 函数
conv 函数
将 10 从十进制转换为二进制。
ceiling、floor 函数
format 函数
保留 3 位小数
rand 函数
mod 函数
四. 其它函数
| 函数名称 | 描述 |
|---|---|
| user() | 查询当前用户 |
| md5(string) | 对字符串进行md5摘要，获得一个32位字符串 |
| database() | 显示当前正在使用的数据库 |
| version() | 查看MySQL数据库的版本号 |
| password() | MySQL数据库使用该函数对用户的密码进行加密操作 |
| ifnull(val1, val2) | 如果val1是null，返回val2，否则返回val1 |
user 函数
md5 函数
- 一般情况下公司内部数据库不会存储用户的明文密码，而会将用户密码形成摘要后存储对应的摘要，当用户登录账号时，将用户输入的的密码形成摘要后与数据库中存储的摘要做对比，如果对比成功则允许登录。
- 这么做的好处主要有两个，第一个好处就是公司内部数据库中存储的不是用户的明文信息，就算用户信息泄露了也不会产生太大影响，第二个好处就是形成的摘要是定长的，这样有利于数据库表结构的设计。
database 函数
version 函数
password 函数
注意：在 MySQL 8.0 及以上版本中，password 函数已经被移除或不可用。
ifnull 函数
案例：创建登入表
create table login(id bigint primary key auto_increment,name varchar(20) not null,password char(32) not null
);insert into login (name, password) values ('雷伊', 'leiyi123456');
insert into login (name, password) values ('盖亚', 'gaiya123456');select * from login;
这里需要考虑的是：密码可以直接通过查询表进行查看，数据泄露时对用户不安全。
insert into login (name, password) values ('布莱克', md5('bulaike123456'));
insert into login (name, password) values ('卡修斯', md5('kaxiusi123456'));# 这里由于MySQL数据库版本大于8.0，插入失败
insert into login (name, password) values ('缪斯', password('miusi123456'));select * from login;
# 直接输入密码查看用户时失败
select name from login where name = '布莱克' and password = 'bulaike123456';# 需要将密码进行md5函数转换
select name from login where name = '布莱克' and password = md5('bulaike123456');
password() 函数比 md5() 函数更加常用！
五. SQL 实战
牛客：查找字符串中逗号出现的次数