mysql基础 day03

4/11/2018 8:30:39 AM

MySQL03

晨测

1.写出MySQL事务的常用命令
2.写出MySQL中的常见约束
3.写出多表查询的几种分类
4.MyISAM、InnoDB区别

回顾

一、TCL   
    set autocommit = true/false;
    rollback;
    commit;
    savepoint xx;
    rollback xx;    
    ACID

二、约束
    default
    not null
    unsined
    zerofill

    unqiue
    primary key

    foreign key         

三、多表查询（重点）
    合并
    连接
    子查询

四、用户创建和授权
    grant 权限  on  数据库(数据表) to  用户名@ip identified  by 密码；

作业

15 .查询同时参加英语和计算机考试的学生信息
    select * from student where sid in(select sid from score where sid in   (select sid     from score where    cname='计算机') and cname='英语');

    select  s.* from student s ,score g1 , score g2 
            where s.sid=g1.sid 
            and g1.name='计算机'
            and s.sid=g2.sid
            and g2.name='英语';   

18. 查询姓张或者姓王的同学的姓名、院系和考试科目及成绩   
    select s.name s.department,g.name,g.score from student s,score g 
        where s.sid=g.sid
        and （s.name like '张%' or s.name like '王%'）；

今日概要

一、MySQL中的索引
二、MySQL中的函数
三、MySQL数据库的备份和恢复
四、数据库设计和优化（重点）

一、MySQL中的索引

<1>索引的概念

索引就是一种数据结构（高效获取数据），在mysql中以文件的方式存在。存储建立了索引列的地址或者指向。

文件  ：（以某种数据 结构存放） 存放地址  指向数据

类似一本书的目录，能够提高检索效率。

<2>特点

提高数据的获取效率，也是数据库优化的一部分。
在多表连接时，作为条件加速连接的速度  。
在分组和排序查询时，作为条件，能够减少分组和排序所消耗的时间。

创建和维护索引 会消耗时间，随着数据量的增加消耗的时间也增加
索引文件会占用物理空间
数据表中的数据变化时，索引也需要动态的维护

<3>分类

3.1 普通索引
    1. 创建的语法格式  
        CREATE INDEX index_name ON table_name(column列表);

        ALTER　TABLE table_name ADD INDEX index_name(column列表);

        例如：
            create table tt1(
                id int primary key auto_increment,
                name varchar(20) not null
            )

            create index index_name on tt1(name);
            alter table tt1 add index index_name(name)

        2. 查看创建的索引
            show index from 表名;
            show create table 表名；
            show keys from 表名；

        3. 删除索引
            DROP INDEX index_name ON tablename;

3.2 唯一索引
3.3 聚焦索引（主键索引）  

3.4 全文索引
    1. 创建的语法格式
        Create Fulltext Index fullindex_name On table_name(字段列表);
        Alter Table table_name Add Fulltext [Index]  fullindex_index(字段列表);

        例如：create table tt2(
                id int,
                name varchar(100) ,
                //fulltext key fulltext_name_index(name)
            ) engine=myisam;

            Alter Table tt2 Add Fulltext   fullindex_index(name);   

    2. 使用方式
        match(索引列表) against("内容" in boolean mode)

        insert tt2 value(1,'我');    
        insert tt2 value(1,'我是');   
        insert tt2 value(1,'我是好');  
        insert tt2 value(1,'我是好人'); 
        insert tt2 value(1,'我是一个大好人');  

        select * from tt2 where match(name) against('我' in boolean mode);
        select * from tt2 where match(name) against('我是' in boolean mode);

<4>应用场景

建议使用：
    主键自动建立索引
    查询中统计或者分组字段
    排序字段
    与其他表关联的字段
    频繁的作为查询条件的字段    

不建议使用：
    表的记录较少
    频繁的更新字段
    where条件表达式中不常用的字段

二、MySQL中的函数

1. 加密函数

password(str):通常用来给用户密码进行加密
    select password('123');

md5(str):使用散列的方式对数据进行加密
    select md5('123');

2. 流程控制的函数

* if(expr,r1,r2):
        判断expr是否成立，结果为true返回r1的值，否则返回r2的值
    select if(1>0,"大于","小于")；

* Case value When compare-value Then result 
           When compare-value Then result 
            ...
            Else result
            End
    select case 666 when 777 then "one" 
             when 777 then "one" 
             when 666 then "two" 
             else "three" end

* ifnull(v1,v2)
        判断v1的值是不是null，如果不为null,返回v1的值，否则返回v2的值
    ifnull(null,0)  

* nullif(e1,e2) 
        判断e1=e2是否成立，成立返回null,不成立返回e1
    select nullif(1+1,2-1);

3. 数学相关的函数

* rand()
    返回0-1的随机数

* round(num)
    返回num的近似值

* abs(num)
    取num的绝对值

4. 日期相关的函数

* curdate()/current_date()
    返回当前日期
    select curdate(),current_date()

* curtime()/current_time()
    返回当前时间
    select curtime(),current_time()

* now()/current_timestamp()/localtime()/sysdate()/localtimestamp()
    返回当前日期+时间

5. 字符处理相关的函数

* char_length(str)
    返回当前str字符串的长度       
    select char_length('你好123');

* length(str)
    返回当前str字符串的长度
    select length('你好123');

三、MySQL数据库的备份和恢复

<1>数据库的备份

语法格式：使用mysqldump命令可以生成指定的数据库脚本文件
    mysqldump -u用户名 -p密码  数据库名称  > 生成的脚本文件的路径

例如：mysqldump -uroot -p123 db1 >C:\mydb1.sql     

<2>数据库的恢复

恢复的语法格式1：
    source 数据库脚本文件的路径

例如：在mysql数据库中
    mysql> source c:\mydb1.sql

注意：必须登陆到mysql数据库中，先选择数据库

恢复的语法格式1：
    mysql -uroot -p123 db2 <C:\mydb1.sql        

四、数据库的设计和优化

<1>数据库的设计:小型的电子商务网站

1.1 需求分析
    1.1.1 核心的模块
        用户模块：
            作用：存储用户的信息
            属性：用户名，密码，联系方式...
            唯一标识：用户编号，电话号码，用户的身份证号码等等
            特点：随着系统上线时间增长，用户模块的数据需要永久存储         

        商品模块：
            作用：存储商品的信息
            属性：商品名，价格，商品的类型...
            唯一标识：商品编号...
            特点：对于下线的商品进行归档存储    

        购物车模块：

        订单模块：
            作用：存储用户购买的商品信息
            属性：订单号，用户名，用户的联系方式，商品编号，订单状态，价格，数量...
            唯一标识：订单号...
            特点：随着系统上线时间增长，订单模块的数据需要永久存储

        供应商模块：
            作用：存储商品供应商的信息
            属性：供应商的编号，名称，联系人，电话，营业执照，法人代表，地址...
            唯一标识：供应商的编号...
            特点：随着系统上线时间增长，供应商模块的数据需要永久存储                

        ... ... 模块

    1.1.2 关系

1.2 逻辑设计
    1. 将需求转化为逻辑模型
    2. 通过ER图的方式来展示逻辑模型
       ER描述现实世界的概念模型

        矩形：实体
        菱形：关系，联系
        椭圆：属性
        线条：连接属性和实体

1.3 设计表，遵循设计规范（三大范式）
    第一范式：对属性的原子性约束，要求属性具有原子性，不可在分割。
    第二范式：对记录的唯一性约束，要求每一条数据具有唯一标识
    第三范式：对字段的冗余性的约束，要求字段不能出现冗余

1.4 物理设计
    建立数据库，表和字段
    选择合适的数据库软件
    操作系统的选择
    配置数据库的参数
        innodb_buffer_pool_size 
        innodb_file_per_table   

<2>数据库的优化

良好的数据库设计和操作能够提高使用数据库的效率。

1. 选择合适的存储引擎

2. 选择合适的数据类型
    少的使用text,blob类型
    尽量使用简单的数据类型，int enum date  varchar()
    多使用not null 定义字段（innodb存储引擎）
    建议使用enum        

    存储时间的函数：
        from_unixtime()和unix_timestramp()函数可以对时间类型进行转换

        insert test(timestr) value(unix_timestramp('2018-01-01 01:01:01'));
        select  from_unixtime(timestr)   from test;

3. 创建合适的索引
    字段越小越好
    字段在where从句，group by从句，order by从句中 等等    
    字段作为连接关系

4. 尽量避免select * 
    只查询需要的数据

5. 反范式化
    订单表：订单id , 用户id , 商品id, 状态, 价格 , [商品名称 , 用户名称 ,联系方式]

6. 表的拆分
    垂直拆分：
        解决表的宽度问题        
        <1>把不常用的字段单独放置到一张表中
        <2>把字段类型大的字段单独放置到一张表中
        <3>把常用的字段放置到一张表中        

    水平拆分：
        解决表的数据量过大带来的问题，将一张表拆成多份

7. explain :查询SQL的执行计划
        select_type:查询的类型
        type:   显示使用了何种类型
            ALL:全表扫描
            index:全索引扫描
            range:基于索引的范围查找
            const:常数查找      
        key:此次查询中使用到的索引，没有用到，显示null

        rows:显示此次查询一共扫描了多少行，原则上扫描越少越好

        extra:额外的信息
            using filesort:需要优化

作业：

标题：电子商城数据库设计
要求：
    1. 分析数据库功能模块，列出所需要的表，和表与表之间的关系
    2. 将需求转化为数据库的逻辑模型，使用ER图表示
    3. 根据逻辑模型设计表
    4. 创建表并插入模拟数据
    5. 编写测试sql