学习笔记——MYSQL 视图 事务 索引

视图

通俗的讲，视图就是一条SELECT语句执行后返回的结果集。
所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
视图是对若干张基本表的引用，一张虚表，只能查询或删除，不存储或改变具体的数据。
基本表数据发生了改变，视图也会跟着改变。

• 用法：

create view 视图名称 as select 语句

视图名称一般用v_开头

• 查询
  select * from 视图名称;
• 删除
  drop view 视图名称；

select * from students as s inner join classes as c on s.cls_id=c.id;
+----+-----------+------+--------+--------+--------+-----------+----+--------------+
| id | name      | age  | height | gender | cls_id | is_delete | id | name         |
+----+-----------+------+--------+--------+--------+-----------+----+--------------+
|  1 | 小明      |   18 | 180.00 | 女     |      1 |           |  1 | python_01期  |
|  2 | 小月月    |   18 | 180.00 | 女     |      2 |          |  2 | python_02期  |
|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           |  1 | python_01期  |
|  4 | 刘德华    |   59 | 175.00 | 男     |      2 |          |  2 | python_02期  |
|  5 | 黄蓉      |   38 | 160.00 | 女     |      1 |           |  1 | python_01期  |
|  6 | 凤姐      |   28 | 150.00 | 保密   |      2 |          |  2 | python_02期  |
|  7 | 王祖贤    |   18 | 172.00 | 女     |      1 |          |  1 | python_01期  |
|  8 | 周杰伦    |   36 |   NULL | 男     |      1 |           |  1 | python_01期  |
|  9 | 程坤      |   27 | 181.00 | 男     |      2 |           |  2 | python_02期  |
| 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 |           |  2 | python_02期  |
+----+-----------+------+--------+--------+--------+-----------+----+--------------+
create view  v_students as select * from students as s inner join classes as c on s.cls_id=c.id;
ERROR 1060 (42S21): Duplicate column name 'id'

注意：创建的视图不能存在相同列名

create view  v_students as select s.*, c.name as class_name from students as s inner join classes as c on s.cls_id=c.id;
Query OK, 0 rows affected (0.08 sec)

show tables;
+-------------------------+
| Tables_in_python_test_1 |
+-------------------------+
| classes                 |
| students                |
| v_students              |
+-------------------------+
 select * from v_students;
+----+-----------+------+--------+--------+--------+-----------+--------------+
| id | name      | age  | height | gender | cls_id | is_delete | class_name   |
+----+-----------+------+--------+--------+--------+-----------+--------------+
|  1 | 小明      |   18 | 180.00 | 女     |      1 |           | python_01期  |
|  2 | 小月月    |   18 | 180.00 | 女     |      2 |          | python_02期  |
|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           | python_01期  |
|  4 | 刘德华    |   59 | 175.00 | 男     |      2 |          | python_02期  |
|  5 | 黄蓉      |   38 | 160.00 | 女     |      1 |           | python_01期  |
|  6 | 凤姐      |   28 | 150.00 | 保密   |      2 |          | python_02期  |
|  7 | 王祖贤    |   18 | 172.00 | 女     |      1 |          | python_01期  |
|  8 | 周杰伦    |   36 |   NULL | 男     |      1 |           | python_01期  |
|  9 | 程坤      |   27 | 181.00 | 男     |      2 |           | python_02期  |
| 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 |           | python_02期  |
+----+-----------+------+--------+--------+--------+-----------+--------------+

事务

事务广泛的运用于订单系统、银行系统等多种场景
例如：
A用户和B用户是银行的储户，现在A要给B转账500元，那么需要做以下几件事：

    检查A的账户余额>500元；
    A 账户中扣除500元;
    B 账户中增加500元;

正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。
那如果A账户扣了钱之后，系统出故障了呢？A白白损失了500，而B也没有收到本该属于他的500。

以上的案例中，隐藏着一个前提条件：A扣钱和B加钱，要么同时成功，要么同时失败。事务的需求就在于此
所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

事务的四大特性（ACID)

• 原子性(Atomicity)
• 一致性(Consistency)
• 隔离性(Isolation)
• 持久性(Durability)

事务命令

表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎
查看表的创建语句show create tabel 表名，可以看到engine=innodb

• 开启事务
  开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

begin;
或者
start transaction;

• 提交事务
  将缓存中的数据变更维护到物理表中

commit;

• 回滚事务
  放弃缓存中变更的数据

rollback;

注意：

1. 修改数据的命令会自动的触发事务，包括insert、update、delete
2. 使用终端数据库的时候 也是默认开始事务的
3. 在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

索引

当数据库中数据量很大时，查找数据会变得很慢。
索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

打开运行时间检测
set profiling=1;

正常方法查询
select * from test_index where title='ha-9999';

为表title_index的title列创建索引：
执行查询语句：
create index title_index on test_index(title(10));
select * from test_index where title='ha-9999';

查看执行的时间
show profiles;

注意：
建立太多的索引将会影响更新和插入的速度，对于一个经常需要更新和插入的表格，就没有必要单独建立索引了。
对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
建立索引会占用磁盘空间。