mysql(DCL管理用户和数据库函数)

我报名参加金石计划1期挑战——瓜分10万奖池，这是我的第4篇文章

DCL-管理用户

1. 查询用户

use mysql;
select * from user;

2. 创建用户

create user '用户名'@'主机名' identified by '密码';
-- 例：创建用户cheng, 可以在任意主机访问该数据库，密码123456
create user 'cheng'@'%' identified by '123456';

3. 修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'; // 不生效
update user set password=password('新密码') where user='用户名' and host='主机名'; 

4. 删除用户

drop user '用户名'@'主机名';

注意：

• 主机名可以使用%通配。
• 这类SQL开发人员操作的比较少，主要是DBA（Database Adminstrator 数据库管理员）使用

DCL-权限控制

Mysql中定义了很多种权限，但是常用的就以下几种：

all, all privileges => 所有权限		select => 查询数据 		insert => 插入数据
update => 修改数据 		delete => 删除数据 		alter => 修改表
drop => 删除数据库/表/视图 		create => 创建数据库/表

1. 查询权限

show grants for '用户名'@'主机名';

2. 授予权限

grant 权限列表 on 数据库名.表名(*.*代表所有) to '用户名'@'主机名';

3. 撤销权限

revoke 权限列表 on 数据库名.表名(*.*代表所有) from '用户名'@'主机名';

注意：

• 多个权限之间，使用逗号分隔

• 授权时，数据库名和表名可以使用*进行通配，代表所有。

函数

字符串函数

concat(S1,S2,...Sn) => 字符串拼接，将S1, S2, ...Sn拼接成一个字符串 
-- 例：select concat('hello','mysql'); => hellomysql

lower(str) => 将字符串str全部转为小写
-- 例：select lower('Hello'); => hello

upper(str) => 将字符串全部转为大写
-- 例：select upper('Hello'); => HELLO

lpad(str,n,pad) => 左填充，用字符串pad对str的左边进行填充，达到n个字符长度
-- 例：select lpad('01',5,'-'); => ---01

rpad(str,n,pad) => 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
-- 例：select rpad('01',4,'%') => 01%%

trim(str) => 去掉字符串头部和尾部的空格
-- 例：select trim(' hello  mysql '); => (hello  mysql)

substring(str,start,len) => 返回从字符串str从start位置起的len个长度的字符串
-- 例：select substring('hello mysql',1,7); => (hello m)

案例：

-- 例：员工工号统一为5位数，不足5位数的全部在前面补0
update 表名 set workId = lpad(workId,5,'0'); 

数值函数

ceil(x) => 向上取整 	floor(x) => 向下取整

mod(x,y) => 返回x/y的求余 
-- 例：select mod(7,4); => 3（7/4的结果3）

rand() => 返回0~1的随机数

round(x,y) => 求参数x的四舍五入的值，保留y为小数（符合位数，就不进行）
-- 例：select round(2.33, 2) => 2.33

案例：

-- 例：用数据库函数，随机生成一个6位数
select lpad(round(rand()*1000000, 0),6 ,'0');

日期函数

curdate() => 返回当前日期 	
-- 例：select curdate(); => 2022-09-05

curtime() => 返回当前时间  			now() => 返回当前日期和时间
-- 例：select now(); => 2022-09-05 20:26:25

year(date) => 获取指定date的年份
-- 例：select year(now()) => 2022

month(date) => 获取指定date的月份 		day(date) => 获取指定date的天

date_add(date, interval expr type) => 返回一个日期/时间加上一个时间间隔expr后的时间值
-- 例：select date_add(now(), interval 2 year) => 2024-09-06 09:33:44

datediff(date1, date2) => 返回起始时间date1和结束时间date2之间的天数
-- 例：select datediff('2022-9-08', '2022-04-01'); => 160（天）
-- 例：select datediff('2022-04-01', '2022-9-08'); => -160（天）

综合案例：

例：查询所有员工的入职天数，并根据入职天数倒序排序
select name, datediff(curdate(), enterDate) as '入职天数' from 表名 order by '入职天数' desc;

流程函数

流程函数也是很常用的一类函数，可以在sql语句中实现条件筛选，从而提高语句的效率

if(value,t,f) => 如果value为true,则返回t，否则返回f
-- false, null, '', 0 => false
-- '有内容'（字符串不为空）, 数值不为零, true => true 

ifnull(value1,value2) => 如果value1不为空，返回value1，否则返回value2
-- ifnull(null, value2) => value2 （其他情况都返回value1）

case when [va1] then [res1] ... else [default] end => 如果val1为true, 返回res1, ... 否则返回default默认值
-- 例： 查询员工姓名和工作地址(北京/上海 => 一些城市)(其他 => 二线城市)
select name,  (case  when wordAddress in('北京', '上海') then '一线城市' else '二线城市' end) as '地址' from 表名;
-- 或 select name, if(wordAddress in('北京', '上海'), '一线城市', '二线城市') as '地址' from 表名;

case [expr] when [val1] then [res1] ... else [default] end => 如果expr的值等于val1，返回res1, ... 否则返回default默认值
-- 例：统计学员, 成绩 >= 85, 展示优秀, >= 60, 展示及格,否则, 展示不及格
select id, name, (case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end) '数学',
 (case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end) '语文' from 表名;