MySQL视图&导入导出&索引&执行计划

目录

一、视图 

         二、数据的导入导出

三、索引

四、执行计划-Explain

---

一、视图 

1.什么是视图
1) 视图是一种虚拟的表，是从数据库中一个或多个表中导出来的表。    
   2) 数据库中存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。
   3) 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。    

 2.视图的作用
   1) 使操作简便化。
   2) 增加数据的安全性。
   3) 提高表的逻辑独立性。

3.基本语法
CREATE VIEW 视图名 AS SELECT 语句;

     例如：CREATE VIEW
     
     CREATE VIEW v_student as
     select s.*,sc.score,c.*,t.tname from t_student s,t_score sc,t_course c,t_teacher t
     where s.sid=sc.sid and sc.cid-c.cid and c.tid=t.tid
     
     select * from v_student
      

二、数据的导入导出

1.使用工具，类似Sqlyog、Navicat等导入导出数据。

选中表右键运行SQL文件

 选择要导入的sql文件，点击开始

2.使用mysqldump导入导出
  导出

    2.1.1 导出表数据和表结构
    mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql(这个名字随便叫)

    #/usr/local/mysql/bin mysqldump -uroot -pabc >abc.sql
    敲回车之后提示输出密码

    2.1.2 只导出表结构
  
    mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql

    #mysqldump -uroot -p -d abc > abc.sql

  
    注：导出的数据在mysql的bin目录下

  导入
批量导入：LOAD DATA INFILE（推荐）

   可先通过SELECT INTO OUTFILE方式，将数据导出到Mysql的C:\ProgramData\MySQL\MySQL Server 5.5\data目录下，再通过LOAD DATA INFILE方式导入。 

   1) select * from 表名 into outfile '/文件名.sql';
   2) load data infile '/文件名.sql' into table 表名(列名1,...); 

    注意：首先建立空数据库

    mysql>create database abc;

    2.2.1 方法一

    mysql>use abc;                   #选择数据库
    mysql>set names utf8;            #设置数据库编码
    mysql>source /home/abc/abc.sql;  #导入数据

    2.2.2 方法二

    mysql -u用户名 -p密码 数据库名 < 数据库名.sql
    #mysql -uabc_f -p abc < abc.sql

3.LOAD DATA INFILE
  可先通过SELECT INTO OUTFILE方式，将数据导出到Mysql的C:\ProgramData\MySQL\MySQL Server 5.5\data目录下，再通过LOAD DATA INFILE方式导入。 

  1) select * from 表名 into outfile '/文件名.sql';
  2) load data infile '/文件名.sql' into table 表名(列名1,...); 

三、索引

  1.什么是索引
   索引是由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。
类似于图书的目录，方便快速定位，寻找指定的内容。

   2.索引的优缺点
   优点：提高查询数据的速度。
   缺点：创建和维护索引的时间增加了，同时占用硬盘空间。

   3.索引分类
   1) 普通索引：是最基本的索引，它没有任何限制；

   2) 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；

   3) 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；

   4) 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合；

   5) 全文索引：使用FULLTEXT参数可以设置，全文索引只能创建在CHAR,VARCHAR,TEXT类型的字段上。主要作用是提高查询较大字符串类型的速度；只有MyISAM引擎支持该索引，MySQL默认引擎不支持；mysql5.7+

   分词器  中华/人民/共和国

   4.创建索引
   CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名[(长度)][ASC|DESC])

   5.修改索引
   ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT] INDEX 索引名(字段名[(长度)][ASC|DESC])

   6.删除索引
   DROP INDEX 索引名 ON 表名

案例：
1) 普通索引案例

create index 索引名 on 表名(字段名)

2) 唯一索引案例

create unique index 索引名 on 表名(字段名)

3) 主键索引案例

create table 表名(
   id int not null auto_increment primary key,
   ...
)

4) 组合索引案例

create index 索引名 on 表名(字段名1,字段名2,...)

四、执行计划-Explain

 

 

1.什么是执行计划？
     使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理你的sql语句的。分析你的查询语句或是表结构的性能瓶颈。

     语法: Explain + sql

2.执行计划的作用
     1) 表的读取顺序
     2) 数据读取操作的操作类型
     3) 哪些索引可以使用
     4) 哪些索引被实际使用
     5) 表之间的引用
     6) 每张表有多少行被优化器查

3.执行计划包含的信息 
示例一：explain

EXPLAIN select * from t_user
EXPLAIN select * from t_user u left join t_role r on u.roleid = r.roleid
EXPLAIN select * from t_user u where u.roleid = (select roleid from t_role 
where rolename = '管理员')

示例二：id

id相同，从上往下一次执行
目的：在于知道sql语句的执行步骤是什么左外联

EXPLAIN select * from t_user u left join t_role r on u.roleid = r.roleid

右外联

EXPLAIN select * from t_user u right join t_role r on u.roleid = r.roleid

示例三：select_type

（1）SIMPLE(简单SELECT,不使用UNION或子查询等)

EXPLAIN select * from t_user

（2）PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
SUBQUERY(子查询中的第一个SELECT)

EXPLAIN select * from t_user u where u.roleid = (select roleid 
from t_role where rolenmae = '管理员')

（3）UNION(UNION中的第二个或后面的SELECT语句)
UNION RESULT: 从 union 表获取结果的 SELECT

EXPLAIN select * from t_user where id=3
union
select * from t_user where roleid = 3

示例四：正确使用索引

（1）使用 like 语句时，%在右边才会使用索引。
有效
提高查询效率

EXPLAIN select * from t_user where username like '张'

（2）or条件中有未建立索引的列才索引失效
无效

EXPLAIN select * from t_user where password = 123456 or username='张三'

有效

EXPLAIN select * from t_user where password = 123456 and username='张三'

（3）条件的类型不一致
无效

EXPLAIN select * from t_user where username = 1

有效

EXPLAIN select * from t_user where username = '1'