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

posted @ 2019-08-31 17:09  chalee3  阅读(338)  评论(0编辑  收藏  举报