【数据库系列教程】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)
评论