MySQL中的函数简单总结，以及TCL语句的简单讲解

一、函数

1、ifnull

当前⾯的值是null的时候，使⽤后⾯的默认值
ifnull(字段，默认值) – 参数就是两个，第一个参数需要判断的字段，第二个是默认值

2、if

if(条件，值1，值2) – 前⾯的条件如果成⽴，取值第⼀个，否则取值第⼆个

3、case

case when … then … else … end

-- 行转列
select sname,
 sum(case  when subject='语文' then score else 0 end) '语文',
 sum(case  when subject='数学' then score else 0 end) '数学',
 sum(case  when subject='英语' then score else 0 end) '英语',
 sum(case  when subject='历史' then score else 0 end) '历史',
 sum(case  when subject='政治' then score else 0 end) '政治',
 sum(case  when subject='体育' then score else 0 end) '体育'
from sc group by sname;

等同于：

select sname,
sum(if(subject='语文',score,0)) 语文,
sum(if(subject='数学',score,0)) 数学,
sum(if(subject='英语',score,0)) 英语,
sum(if(subject='历史',score,0)) 历史,
sum(if(subject='政治',score,0)) 政治,
sum(if(subject='体育',score,0)) 体育
 from sc group by sname;

4、exists 存在

exists的作用为判断一个表中的数据，是否在另外的一张表中能够查询到与之对应的数据

-- 查询一个数据是否存在，存在里面的结果集中，如果存在，显示出来，不存在不显示
select * from dept where exists(
    select * from emp where emp.deptno = dept.deptno
);

5、字符串函数（重点）

-- 获取字符串⻓度
select char_length('Hello');
-- 变⼤写
select upper('Hello');
-- 变⼩写
select lower('Hello');
-- 空⽩字符串切割
select trim(' Hello ');   -- 左右两边的空⽩字符全部切掉
select ltrim(' Hello ');  -- 只切除左边的空⽩字符
select rtrim(' Hello ');  -- 只切右边
-- ⽐较两个字符串是否相等，⽐较的肯定是内容
select strcmp('hello','hello');   -- 如果相等返回0，不等于返回 1 或者 -1
-- 截取⼀段字符串
select substr('hello',2,3);   -- 2 代表的是第⼆个字符的位置，3代表的是截取的⻓度
-- 将字符串进⾏反转
select reverse('hello');
-- 替换
select replace('hello','l','a');
-- 字符串的拼接，有两种
-- 1）使⽤某个拼接符进⾏拼接s，第一个参数为分隔符，第二个可以是数组或者集合
select concat_ws(':',arr/list);
-- 2) 可以不指定拼接符（默认为逗号分隔），中间也可以指定分隔符
select concat('hello','world');

6、数学函数

-- 绝对值
select abs(-1);
-- 天花板 向上取整
select ceil(1.99);
-- 地板砖 向下取整
select FLOOR(1.99);
-- 除以
select 10 div 5;
select 10/5;
-- 求最⼩值和最⼤值
select least(10,20,4,50,18);   -- 最小值
select greatest(10,20,4,50,18);  --最大值
-- 求余数
select 5%2;
select MOD(5,2);
-- 求次⽅
select POW(2,3);
-- 开根号
select sqrt(16);
-- PI 
select PI();
-- 获取0到1之间的随机数，不包含1
select rand();
-- 随机获取 [3,10)
select floor (rand() * 7 + 3 );
-- 四舍五⼊
select round(1.56); -- 2
select round(1.22); -- 1
-- 数据进⾏格式化处理 最后⼀位进⾏四舍五⼊的处理
select format(3.1415926,3);
-- 保留⼩数点后⼏位，不会四舍五⼊
select TRUNCATE(1.35675,3);

7、日期函数

--获取当前时间
select now() ;
-- 查询当前时间 年⽉⽇的形式
select current_data();
-- 时分秒的形式
select current_time();
-- 年⽉⽇时分秒
select current_timestamp();
-- 某个⽇期多少天以后
select adddtae('2022-07-21',interval 10 day);
-- 某个时间多少⼩时分钟秒之后
select addtime('2022-07-21 09:57:00','2:00:00');
-- 获取两个时间的差值
select abs(datediff('2022-07-11','2022-07-21'));
-- 将数据格式化为其他的样式 %r 可以展示上午还是下午
select date_format('2022-07-11','%y年%m⽉%d⽇');
-- 获取天
select day('2022-07-11');
select year('2022-07-11');
-- 获取给定的⽉份
select month('2022-07-11');
-- 该⽇期是这个⽉/年/周的第⼏天
select dayofmonth('2022-07-11');
select dayofweek('2022-07-11'); -- 2
select dayofyear('2022-07-11'); -- 192
-- 获取某个⽉的最后⼀天的⽇期
select last_day(current_date());
-- 获取当前时间，包含时分秒，跟current_timestamp效果⼀样
select now();
-- 专⻔⽤于减天数的函数
select subdate(now(),1);

二、TCL语句

1、创建用户

语法： create user ‘⽤户名’@‘主机名’ identified by ‘密码’;
创建了⼀个⽤户，其实就是在mysql数据库中的user表中，插⼊了⼀条记录⽽已。

2、赋予权限

-- 语法
grant 权限1,权限2 ..... on 某个数据库中的⼀些表 to '⽤户名'@'主机名'
grant insert,update,select on sql.* to 'xxxxxx'@'localhost';
-- all privileges 所有权限的意思
grant all privileges on . to 'xxxxxx'@'localhost' identified by '123456' with grant option;

-- 刷新权限
flush privileges;

-- 重置密码：
alter user 'root'@'localhost' identified by 'root';

3、修改mysql允许远程登录

-- 创建用户
create user 'root'@'%' identified by 'root';
-- 赋权限
grant all privileges on . to 'root'@'%' with grant option;