头图

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


48 声望
6 粉丝
0 条评论
推荐阅读
JavaWeb 项目服务器部署详细教程(MySQL + JDK + Tomcat)
本文参与了思否技术征文,欢迎正在阅读的你也加入。JavaWeb 项目部署到服务器我这里的环境是 JDK13 + tomcat8.5.601. IDEA 打包项目右击项目点击 Open Module Settings构建准备正式构建选择刚才构建的,点击 buil...

若尘阅读 370

封面图
分布式高可用Mysql数据库Percona XtraDB Cluster 8.0 与 Proxysql 史上最详尽用法指南
PXC是Percona XtraDB Cluster的缩写,是 Percona 公司出品的免费MySQL集群产品。PXC的作用是通过mysql自带的Galera集群技术,将不同的mysql实例连接起来,实现多主集群。在PXC集群中每个mysql节点都是可读可写的...

apollo0084阅读 7.2k评论 2

一次偶然机会发现的MySQL“负优化”
今天要讲的这件事和上述的两个sql有关,是数年前遇到的一个关于MySQL查询性能的问题。主要是最近刷到了一些关于MySQL查询性能的文章,大部分文章中讲到的都只是一些常见的索引失效场合,于是我回想起了当初被那个...

骑牛上青山5阅读 1.1k评论 3

MongoDB 插入时间与更新时间(create_time/update_time)
MongoDB 在数据库层面不能像 MySQL 一样设置自动创建 create_time/update_time,自动更新 update_time

qbit阅读 13.8k评论 2

Mysql索引覆盖
通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但...

京东云开发者2阅读 656

封面图
SegmentFault 思否技术周刊 Vol.70 — 深入 MySQL 实战
MySQL 软件采用了 GPL( GNU 通用公共许可证),由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。

Beverly2阅读 1.4k

封面图
MySQL 数据库索引技术原理初探
一本书 500 页的书,如果没有目录,直接去找某个知识点,可能需要找一会儿,但是借助前面的目录,就可以快速找到对应知识点在书的哪一页。这里的目录就是索引。

mylxsw1阅读 1.2k

48 声望
6 粉丝
宣传栏