头图

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

阅读 175

13 声望
0 粉丝
0 条评论
你知道吗?

13 声望
0 粉丝
宣传栏