MySQL数据库笔记六：数据定义语言及数据库的备份和修复

1. mysql中的函数

<1>加密函数

password(str)
    该函数可以对字符串str进行加密，一般情况下，此函数给用户密码加密。

    select password('tlxy666'); 

md5(str)
    对字符串str进行散列加密，可用户对于一些普通的不需要进行解密的数据进行加密。

<2>聚合函数

avg()
count()
min()
max()
sum()

    例如：
        select avg(sal) from emp;   平均薪资
        select count(*) from emp;   员工数量
        select min(sal) from emp;   最低工资
        select max(sal) from emp;   最高工资
        select sum(sal) from emp;   工资求和

<3>数学相关的函数

rand()
    返回0~1的随机数
    select rand();

abs()
    获取num的绝对值
    select abs(-2);
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.5)

round(num,n)
    保留num小数点后的n位
    select round(10.12345,3);

least(num,num2,.....)
    求最小值
    select least(10,5,18,3,20)

<4>字符相关的函数

char_length(str)
        返回字符串str的长度，单位是字符
        select char_length('nihaoya');

    length(str) 
        返回字符串str的长度
        select length('nihaoya')

    instr(str,substr)
        返回字符串str中子字符串substr的位置
        select instr('123456','34');

    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)

<5>日期函数

now()
    获取当前日期和时间
    select now();

curdate()/current_date();
    获取当前日期
    select curdate();

curtime()/current_time();
    select current_time();

year(date)
    获取年份
    select year('1995-01-02');

<6>流程控制函数

 if(expr,v1,v2)
        如果表达式expr成立，则返回v1的值，否则返回v2的值
        select if(5>3,'大于','小于')

    ifnull(v1,v2)
        如果v1的值不为null，则返回v1的值，否则返回v2的值
        select ifnull(4,0);

    nullif(expr1,expr2)
        如果expr1=expr2，则返回null值，否则返回expr1的值
        select nullif(2,2)

2. 用户的创建和授权（dcl）

<1>授权的语法格式

grant 权限 on 数据库.表 to 用户名@登录的主机  identify by 密码;


1. 新建数据库testdb
    create database 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; -- 刷新权限

3.mysql中的索引

<1>概念：

是一种高效获取数据的数据结构(树)，以文本的形式存在。以某种数据结构存储特定的数据(地址)。
好比是一本书的目录，能够加快数据库查询的数据。

<2>作用

提高查询的效率
在多表连接的时候，作为条件加速连接的速度
在分组和排序检索数据的时候，减少分组和排序所消耗的时间。

<3>类型

1.普通索引(最基本的索引)
    1.1 创建索引
        格式： 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);

    1.2 查询索引 
            格式：show index from table_name;

    1.3 删除索引
            格式：drop index index_name on table_name;

2.唯一索引
3.主键索引
    注意：使用整型优于字符型，额为维护一个与其他字段不相关、与业务不相关的列，一般设置为整数类型并且自增长。

4.mysql数据库的备份和恢复

<1>数据库的备份

使用mysqldump 命令可以生成指定的数据库脚本文件。

语法：
    mysqldump -uroot -p密码 数据库名称 > 生成的脚本文件存放的路径

例如：
    mysqldump -uroot -proot tlxy > g:\tlxy.sql (sql脚本文件) 不需要登录到mysql数据库中

<2>数据库的恢复

语法格式：
    source 脚本文件存放的路径        (先选择恢复到目的数据库，需要登录到mysql数据库中)

    mysql -uroot -p密码   新数据库 < 脚本文件存放的路径 (不需要登录到mysql数据中)

    注意：指定数据存放的数据库
         表名称不要重复。

5.数据库的优化

1.遵循设计范式

第一范式：对属性的原子性的约束，属性不能再分解
第二范式：对记录的唯一性约束，要求每一条记录都有唯一标识，实体完整性。
第三范式：对字段的冗余约束，字段没有冗余。 允许字段适当的冗余，提高数据操作的效率。

2.选择合适的存储引擎

innodb      支持事务        行级别的锁   外键          推荐使用
myisam      不支持     表级别的锁   不支持外键   查询效率更高

3.选择合适的数据类型

优先考虑整型、日期、字符
推荐使用枚举enum('女','男')

4.适当的建立索引

where       order by    group by 后面经常出现的字符
多表的连接的字段

5.避免使用 select *

6.为每一张表设置id并且自增长

7.只需要查询一条数据， limit 1