MySQL 数据库函数、DCL 详解（及备份恢复操作）
MySQL 中的函数
<1> 加密函数
password(str)
该函数可以对字符串 str 进行加密，一般情况下，此函数给用户密码进行加密
select password('ruochen666');
select PASSWORD(ename) from emp;
md5(str)
对字符串 str 进行散列加密，可用户对于一些普通的不需要解密的数据进行加密
select MD5('ruochen666');
select MD5(ename) from emp;
<2> 聚合函数
avg()
select avg(sal) from emp; # 平均薪资
count()
select count(*) from emp; # 员工数量
min()
select MIN(sal) from emp; # 最低薪资
max()
select MAX(sal) from emp; # 最高薪资
sum()
select SUM(sal) from emp; # 薪资求和
<3> 数学相关的函数
rand()
返回 0～1 的随机数
select RAND();
ads(num)
获取 num 的绝对值
select ABS(-1);
mod(num1, num2)
取余数
select MOD(10, 3);
ceiling(num)
向上取整
select CEILING(10.1);
floor(num)
向下取整
select FLOOR(10.1);
round(num)
返回离 num 最近的数 --- 四舍五入
select ROUND(10.4);
round(num, n)
保留 num 小数点后的 n 位
select ROUND(10.12345, 2);
least(num, num2 ....)
求最小值
select LEAST(10, 5, 11, 23, 54);
<4> 字符相关的函数
char_length(str)
返回字符串 str 的长度，单位是字符
select CHAR_LENGTH('ruochen');
length(str)
返回字符串 str 的长度
select LENGTH('ruochen');
instr(str, substr)
返回字符串 str 中子字符串 substr 的位置
select INSTR('ruochen', 'chen');
insert(str1, index, len, str2)
将字符串 str2 替换 str1 的 index 开始长度为 len 的数据
select INSERT('123456', 1, 3, 'abc');
replace(str, oldstr, newstr)
字符串替换
select replace('hello', 'll', 'oo');
left(str, len) / right(str, len)
返回字符串 str 的前 / 后 len 的字符串
select LEFT('helloword',5);
select RIGHT('helloword',4);
<5> 日期函数
now()
获取当前日期和时间
select now();
curdate() / current_date();
获取当前日期
select curdate();
select current_date();
curtime() / current_time();
获取当前时间
select curtime();
select current_time();
year(date)
获取年份
select year('1995-01-01');
<6> 流程控制函数
if(expr, v1, v2)
如果表达式 expr 成立，则返回 v1 的值，否则返回 v2 的值
select if(5>3, '大于', '小于');
ifnull(v1, v2)
如果 v1 的值不为 null，则返回 v1 的值，否则返回 v2 的值
select ifnull('ruochen', '');
nullif(expr1, expr2)
如果 expr1=expr2，则返回 null 值，否则返回 expr1 的值
select nullif(2, 2);
MySQL 用户创建和授权(DCL)
<1> 授权
语法格式
GRANT 权限 ON 数据库.表 TO 用户名 @登陆的主机 IDENTIFIED BY 密码;
新建数据库 testdbcreate datebase testdb;
新建用户 laowang 并赋予 testdb 数据库相应的权限 grant all privileges on testdb.* to laowang@localhost identified by '123';
如果想指定部分的权限给用户 grant select, update on testdb.* to laowang@localhost identified by '123';
赋予用户 laowang 所有数据库的某些权限 grant select, update, insert, create, drop on . to laowang@"%" identified by '123';
注意：all privileges, ., "%"(所有的登陆主机)
常用的权限 select 对所有表进行查询操作 insert 对所有表进行插入操作 update 对所有表进行更新操作 delete 对所有表进行删除操作 create 数据库、表、索引 drop 数据库和表的删除操作 alter 对所有表进行更改
<2> 取消权限
revoke 权限 on 数据库.表 from 用户名 @登陆的主机;
revoke select on testdb.* from laowang@localhost;
<3> 显示授予的权限
show grants;
<4> 删除用户
use mysql;
delete from user where user='laowang';
flush privileges; -- 刷新权限
MySQL 中的索引
<1> 概念
是一种高效获取数据的数据结构(树), 以文本的形式存在，以某种数据结构存储特定的数据(地址)。
好比是一本书的目录，能够加快数据库查询的速度。
<2> 作用
提高查询的效率
在多表连接的时候，作为条件加速连接的速度
在分组和排序检索数据的时候，减少分组和排序所消耗的时间
<3> 类型
普通索引(最基本的索引)
创建索引
语法格式：create index index_name on table_name(column)
查看索引
语法格式：show index from table_name;
删除索引
语法格式：drop index index_name on table_name;
drop index index_name on index_tb1;
唯一索引
主键索引
注意：使用整型优于字符型，额外维护一个与其他字段不相关的列，一般设置为整数类型并且自增长。
MySQL 数据库的备份和恢复
<1> 数据库的备份
使用 mysqldump 命令可以生成指定的数据库脚本文件
语法：
mysqldump -uroot -p 密码 数据库名称 > 生成的脚本文件存放的路径
例如；
mysqldump -uroot -proot ruochen > D:\ruochen.sql（sql 脚本文件） 不需要登录到 mysql 数据库中
<2> 数据库的恢复
语法格式：
soure 脚本文件存放的路径 (先选择恢复到目的数据库，需要登录到 mysql 数据库中)
mysql -uroot -p 密码 新数据库 < 脚本文件存放的路径（不需要登陆到 mysql 数据库中）
注意
指定数据存放的数据库
表名称不要重复
数据库的优化
1. 遵循设计的范式
第一范式：对属性的原子性的约束，属性不能再分解
第二范式：对记录的唯一性的约束，要求每一条记录都有唯一标识，实体完整性
第三范式：对字段的冗余约束，字段没有冗余。允许字段适当的冗余，提高数据操作的效率。
2. 选择合适的存储引擎
Innodb
支持事务
行级别的锁
外键
推荐使用
MyISAM
不支持
表级别的锁
不支持外键
查询效率更高
3. 选择合适的数据类型
优先考虑整型、日期、字符
推按使用枚举，enum('男', '女')
4. 适当的建立索引
where、order by、group by 后面经常出现的字段
多表的连接的字段
5. 避免使用 select *
6. 为每一张表设置 id 并且自增长
7. 只需要查询一条数据，limit 1
版权声明: 本文为 InfoQ 作者【若尘】的原创文章。
原文链接:【http://xie.infoq.cn/article/f5bf3fc7e87bbc39d8c69a6d4】。文章转载请联系作者。
还未添加个人签名 2021.01.11 加入
还未添加个人简介
评论