【数据库系列教程】MySQL视图、事务、索引

本期教程为MySQL数据管理操作后期教程

一、视图 (View)

  • 临时表、虚表
  • 针对多表查询
  • 作用
    • 通过视图可以将经常用到的多表查询结果进行保存，下次再使用数据时，直接从视图中查询数据即可
    • “对于频繁更新的数据，不适合创建视图”

1.创建视图

语法

create view 视图名称 as 查询语句;
CREATE VIEW view_name AS SELECT;

mysql> CREATE VIEW stu_teacher
    -> AS
    -> SELECT students.Name, students.Age, tutors.Tname FROM students, tutors WHERE students.TID = tutors.TID;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_jiaowu |
+------------------+
| courses          |
| scores           |
| stu_teacher      |
| students         |
| tutors           |
+------------------+

mysql> SELECT * FROM stu_teacher;
+-----------+------+--------------+
| Name      | Age  | Tname        |
+-----------+------+--------------+
| GuoJing   |   42 | Miejueshitai |
| YangGuo   |   17 | HongQigong   |
| DingDian  |   25 | Jinlunfawang |
| HuFei     |   31 | YiDeng       |
| HuangRong |   16 | NingZhongze  |
+-----------+------+--------------+
5 rows in set (0.00 sec)

2.删除视图

drop view 视图名称

mysql> DROP VIEW stu_teacher;
Query OK, 0 rows affected (0.00 sec)

二、事务 Transaction(重要)

  • 针对修改操作
  • 保证修改操作要么同时成功执行、回滚

1.启动事务

> start transaction;

可以在事务模式下，进行创建删除修改等操作

2.提交事务

  • 提交事务以后会自动保存，并且自动退出事务
> commit;

3.回滚事务

  • 提交回滚以后，不会对原数据进行修改，但会自动退出事务
> rollback;

三、索引 Index

1.什么是索引

  • 建立合适的索引，优化、提高查询速度
  • 针对数据表
  • 不推荐使用经常变化的数据字段建立索引
  • 特点：默认会使用主键字段生成索引

2.查看索引

mysql> show index from Account;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Account |          0 | PRIMARY  |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3.创建索引

命令格式

create index 自定义索引名 on 表名(字段, 字段);

mysql> CREATE INDEX username_key ON Account(username);

4.删除索引

命令格式

drop index 索引名 on 表名;

mysql> DROP INDEX username_key ON Account;

5.查询分析器 explain

无索引情况

mysql> EXPLAIN SELECT * FROM sp WHERE price=4000;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------------------------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows                    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------------------------+----------+-------------+
|  1 | SIMPLE      | sp    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 490028 #这里查询了49万次|    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------------------------+----------+-------------+

有索引情况

创建索引
mysql> CREATE INDEX price_key ON sp(price);

mysql> EXPLAIN SELECT * FROM sp WHERE price=4000;
+----+-------------+-------+------------+------+---------------+----------------------+---------+-------+-------------------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key                  | key_len | ref   | rows              | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------------------+---------+-------+-------------------+----------+-------+
|  1 | SIMPLE      | sp    | NULL       | ref  | price_key     | price_key #使用了索引| 5       | const |   40 #只查询了40次|   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------------------+---------+-------+-------------------+----------+-------+
1 row in set, 1 warning (0.00 sec)