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