随笔，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

正文完
2021-11-01