MySQL——DCL与函数

一、DCL【了解】

目标

• 了解DCL的使用

分析

• 如何管理用户
• 如何管理权限

讲解

1. 语法

用户管理

• 创建用户：create user '用户名'@'主机' identified by '密码'
• 修改密码：set password for '用户名'@'主机' = password('新密码')
• 删除用户：drop user '用户名'@'主机'
• 说明：
  • 用户名：创建的用户的用户名
  • 主机：创建出来的用户，允许在哪个主机电脑上登录。例如：
    • 'tom'@'localhost'：创建用户tom，允许在本机登录
    • 'tom'@'%'：创建用户tom，允许在任何主机电脑上登录

权限管理

• 增加授权：grant 权限 on 数据库名.表名 to '用户名'@'主机'
• 查看授权：show grants for '用户名'@'主机'
• 取消授权：revoke 权限 on 数据库名.表名 from '用户名'@'主机'
• 说明：
  • 权限：select查询权限，update修改权限，… , all所有权限
  • 数据库名：要对哪些数据库进行权限操作。*表示所有库
  • 表名：要对哪些表进行权限操作。*表示所有表

2. 练习

# 注意：如果要管理数据库的用户和权限，必须要以root帐号登录

# 创建一个用户 ：帐号tom，密码123，允许在任何主机登录MySql
# 语法：create user '用户名'@'主机名' identified by '密码';
#       主机名：允许这个帐号在哪台电脑上登录。可以写ip地址，或者写%表示任意主机
CREATE USER 'tom'@'%' IDENTIFIED BY '123';

# 给用户tom重置密码：
# 语法：set password for '用户名'@'主机名' = password('新密码');
SET PASSWORD FOR 'tom'@'%' = PASSWORD('tom');
#       给自己重置密码
SET PASSWORD = PASSWORD('1234');

# 给用户tom授权：允许操作任意库、任意表
# 语法：grant 权限 on 库.表 to '用户名'@'主机名'
#       权限： select, insert, update, delete, create, drop,...  或者 all 表示所有权限
#	库名： 可以写具体的库名称，也可以写*通配符
#	表名： 可以写具体的表名称，也可以写*通配符
GRANT ALL ON day03_1.* TO 'tom'@'%';

# 查看用户tom的权限 
SHOW GRANTS FOR 'tom'@'%';

# 给用户tom取消授权
# 语法：revoke 权限 on 库.表 from '用户名'@'主机名'
REVOKE DROP ON day03_1.* FROM 'tom'@'%';

# 删除用户tom
DROP USER 'tom'@'%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

二、函数【了解】

MySql提供了大量的函数供开发者使用，这些函数可以出现在插入语句的values中、更新语句中、删除语句中、查询语句及其子句中。

准备数据，执行如下脚本：

create database if not exists demo;
use demo;
drop table if exists user;
create table user(
	uid int primary key auto_increment,
	name varchar(20) not null,
	sex int,
	birthday datetime,
	salary double,
	remark varchar(255)
) character set utf8;

insert into user (name,sex, birthday,salary,remark) values 
('tom', '1', '2000-02-20', 5500, '  Hello,  World  '),
('jerry', '0', '2000-02-20', null, 'Hello,World'),
('jack', '1', '2000-02-20', 500, 'Hello,World'),
('rose', '0', '2000-02-20', null, 'Hello,World');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

字符串函数

目标

• 能够使用字符串函数处理字符串

讲解

语法

示例

SQLyog里的快捷键：

• ctrl + 回车：代码提示
• ctrl + shift + 空格：函数参数提示

# SQLyog里的快捷键：
# ctrl + 回车：提示函数名称
# ctrl + shift + 空格：提示函数的参数说明
# ----- 字符串函数 ------
# 1. 字符串长度：
#   length()：获取字符串的字节数
#   char_length() ：获取字符串的字符数
SELECT remark, LENGTH(remark) FROM USER;
SELECT LENGTH('吴签');
SELECT CHAR_LENGTH('吴签');

# 2. 字符串拼接：concat()
SELECT CONCAT(NAME, '.....',  remark) FROM USER;

# 4. 转换成大写：upper()
# 5. 转换成小写：lower()
SELECT UPPER(remark), LOWER(remark) FROM USER;

# 6. 去除空格：trim()
SELECT TRIM(remark) FROM USER;

# 7. 字符串截取：substring(str, 起始序号)。序号从1开始的
SELECT SUBSTRING(remark, 3), remark FROM USER;

# 8. 字符串截取：substring(str, 起始序号, 截取长度)
SELECT SUBSTRING(remark, 3, 2), remark FROM USER;

# 9. 字符串定位：locate(子串, str)
SELECT LOCATE('o', remark), remark FROM USER;

# 10. 字符串替换：replace()
SELECT REPLACE(remark, 'l', 'x'), remark FROM USER;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

日期函数

目标

• 能够使用日期函数处理日期

讲解

语法

• 附：MySql日期格式化，常用的有

示例

# ----- 日期函数 -----
# 1. 获取当前日期时间。now()
SELECT NOW();

# 2. 获取当前日期。current_date()
# 3. 获取当前时间。current_time()
SELECT CURRENT_DATE(), CURRENT_TIME();

#   从日期里获取年、月、日： year(日期), month(日期), day(日期)
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());

# 4. 获取当前时间，以2000/02/02格式显示 。
SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i:%s');

# 5. 计算距离十一还有几天 
SELECT DATEDIFF('2021-10-01', NOW());

#    日期基础上加指定的天数
SELECT ADDDATE(NOW(), 100);
#    日期基础上加100小时
SELECT DATE_ADD(NOW(), INTERVAL 100 HOUR);

#    日期基础上减指定的天数 
SELECT SUBDATE(NOW(), 100);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

数值函数

目标

• 能够使用数值函数处理数值

讲解

语法

示例

# ----- 数值函数 -----
# 1. 向上取整：ceil()
SELECT CEIL(3.001);
# 2. 向下取整: floor()
SELECT FLOOR(3.99);
# 3. 四舍五入: round()
SELECT ROUND(3.4);
SELECT ROUND(3.49, 1);
# 4. 求绝对值：abs()
SELECT ABS(-3);
# 5. 幂运算：power()
SELECT POWER(2, 10);
# 6. 求随机数：rand()
SELECT RAND();
1
2
3
4
5
6
7
8
9
10
11
12
13
14

条件判断

目标

• 能够使用条件判断函数和语法

讲解

准备数据：执行《练习/mysql函数练习素材.sql》

if判断

语法

示例

# 操作user表
# 1. 如果用户的sex是1：显示成“男”，否则显示成“女”
# 语法： if(判断表达式, true的取值, false的取值)
SELECT NAME, IF(sex=1, '男', '女') FROM USER;

# 2. 如果工资为null，按0计算。 ifnull(表达式, 默认值)  如果表达式的值为null，就取默认值；否则取原值
SELECT NAME, IF(salary IS NULL, 0, salary) FROM USER;
SELECT NAME, IFNULL(salary, 0) FROM USER;
1
2
3
4
5
6
7
8

case表达式

语法

CASE 表达式
    WHEN value1 THEN result1
    [WHEN value2 THEN result2] ...
    [ELSE resultn]
END 

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END
1
2
3
4
5
6
7
8
9
10
11

示例

-- 从emp相关的表里查询每个员工的姓名、工资等级并按工资等级降序
--   要从emp和salarygrade表里查询，表的关联条件是：  emp.salary 在 salarygrade.losalary and salarygrade.hisalary
-- 工资等级在1显示为 '努力赚钱'
-- 工资等级在2显示为 '小康生活'
-- 工资等级在3显示为 '可以买车'
-- 工资等级在4显示为 '可以买房'
-- 工资等级在5显示为 '可以娶媳妇'
-- 工资等级不在以上列表中显示为  '土豪'
SELECT e.ename, e.salary, sg.grade,
	CASE sg.grade
		WHEN 1 THEN '努力赚钱'
		WHEN 2 THEN '小康生活'
		WHEN 3 THEN '可以买车'
		WHEN 4 THEN '可以买房'
		WHEN 5 THEN '可以娶媳妇'
		ELSE '土豪'
	END AS '收入'
  FROM emp e
  LEFT JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalary
  ORDER BY sg.grade DESC

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21