mysql dcl_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 密码;

1. 新建数据库testdb

create datebase testdb;

2. 新建用户 laowang 并赋予 testdb数据库相应的权限

grant all privileges on testdb.* to laowang@localhost identified by '123';

3. 如果想指定部分的权限给用户

grant select, update on testdb.* to laowang@localhost identified by '123';

4. 赋予用户 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)

create table index_tb1(

id int primary key auto_increment,

name varchar(32)

);

create index index_name on index_tb1(name);

格式：alter table table_name add index index_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