MySQL学习（四）——MySQL的高级使用

最新推荐文章于 2023-06-12 19:08:25 发布

weixin_42073290

最新推荐文章于 2023-06-12 19:08:25 发布

阅读量148

点赞数

分类专栏：读书笔记系列数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_42073290/article/details/110009681

版权

读书笔记系列同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

数据库

5 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL中的视图、事务和索引。视图提供了一种简化复杂查询和数据访问的方法，允许重用SQL语句并保护数据。事务保证了数据操作的原子性、一致性、隔离性和持久性，提供了数据安全性。索引则加速了查询速度，通过减少数据扫描提高了数据库性能。同时，文章通过实例演示了如何创建、使用和管理这些概念。

摘要由CSDN通过智能技术生成

1 视图

1.1 视图的概念

视图是虚拟的表。与包含数据的表不一样，视图是包含使用时动态监测数据的查询。

下例辅助理解：

SELECT cust_name, cust_contact
    FROM customers, orderd, orderitems
    WHERE customers.cust_id = orders.cust_id
    AND orderitems.order_num = orders.oder_num
    AND prod_id = 'TNT2';

用此查询订购了某个特定产品的客户，任何需要这个数据的人都必须理解相关表的结构，并且知道如何创建查询和对表进行连结。为了检索其他产品（或多个产品）的相同数据，必须修改最后的WHERE子句。

现在假定可以把整个查询包装成一个名为productcustomers的虚拟表，则可以如下轻松的检索出相同的数据：

SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

这就是视图的作用！

1.2 使用视图的原因

重用SQL语句
简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节
使用表的组成部分而不是整个表
保护数据。可以给用户授权表的特定部分的访问权而不是整个表的访问权限
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

创建视图之后，可以用与表基本相同的方式利用它们。（SELECT 操作，过滤和排序数据，将视图连结到其他视图或表，甚至可以添加和更新数据（添加和更新数据存在某些限制，后面说明））

视图仅仅是用来查看和存储在别处的数据的一种设施，视图本身并不包含数据，因此他们再返回的数据是从其它表中检索出来的。在添加或更改这些表的数据时，视图将返回改变过的数据。

因为视图不包含数据，每次使用视图时，都必须处理查询时所需的任一个检索。如果用多个连结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能严重下降。因此在部署使用了大量视图的应用前，应该进行测试！

1.3 视图的规则和限制

视图必须唯一命名（不能给视图取与别的视图或表相同的名字）
对于可以创建的视图数目没有限制
为了创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予
视图可以嵌套，可以利用从其它视图中检索数据的查询来构造一个视图
ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也包含ORDER BY，那么该视图中的ORDER BY将被覆盖
视图不能索引，也不能有关联的触发器或默认值
视图可以和表一起使用。例如，编写一条连结表和视图的SELECT语句

1.4 视图操作

视图用CREATE VIEW语句来创建（定义视图），建议以v_开头

create view 视图名称 as select语句;

使用SHOW CREATE VIEW viewname;来查看创建视图的语句;查看表会将所有的视图也列出来show tables;
使用视图:视图的用途就是查询select * from v_stu_score;
用DROP删除视图，语法为DROP VIEW viewname;
更新视图时，可先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第二条语句更新会创建一个视图；如果要更新的视图存在，则第二条更新语句会替换原由视图。

CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE custmers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;

这条语句创建了一个名为productcustomers的视图，它连结3个表，以返回订购了任意产品的所有客户的列表。如果执行SELECT * FROM productcustomers，将列出订购了任意产品的客户。

为检索产品为TNT2的客户，可以如下进行

SELECT cust_name, cust_contact, prod_id
FROM productcustomers
WHERE prod_id = 'TNT2';

这条语句通过WHERE子句从视图中检索特定数据。在MYSQL处理此查询时，将指定的WHERE子句添加到视图查询中的已有WHERE字句中，以便正确过滤数据。

2 事务

2.1 事务的概念

事务是一个操作序列，这些操作要么都执行要么都不执行，是一个不可分割的工作单位。

2.2 事务四大特性(简称ACID)

下面举一个银行应用是解释事务必要性的一个经典例子。假如一个银行的数据库有两张表：支票表（checking）和储蓄表（savings）。现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那么至少需要三个步骤：

    2 从支票账户余额中减去200美元。
    3 在储蓄帐户余额中增加200美元。
    上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。

可以用START TRANSACTION语句开始一个事务，然后要么使用COMMIT提交将修改的数据持久保存，要么使用ROLLBACK撤销所有的修改。事务SQL的样本如下：

start transaction;
select balance from checking where customer_id = 10233276;
update checking set balance = balance - 200.00 where customer_id = 10233276;
update savings set balance = balance + 200.00 where customer_id = 10233276;
commit;

一个好的事务处理系统，必须具备这些标准特性：

原子性（atomicity）:
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
一致性（consistency）:
数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）
隔离性（isolation）:
通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）
持久性（durability）:
一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

2.3 事务命令

表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎.

查看表的创建语句，可以看到engine=innodb
开启事务：开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中begin;或者start transaction;
提交事务，将缓存中的数据变更维护到物理表中commit;
回滚事务:放弃缓存中变更的数据rollback;

例一，开启事务的操作，可以将没有commit的改动回滚回来

mysql> start transaction;   # 开启事务，标志着事务的开始
Query OK, 0 rows affected (0.00 sec)

mysql> select * from classes;   # 查看表内数据
+----+------+
| id | name |
+----+------+
|  3 | math |
|  4 | art  |
|  6 | sing |
+----+------+
3 rows in set (0.00 sec)

mysql> insert into classes value(0,'computer'); # 向表内插入数据
Query OK, 1 row affected (0.04 sec)

mysql> select * from classes;   # 查看插入数据后的表数据（没提交前，其他终端时看不到数据改动的）
+----+----------+
| id | name     |
+----+----------+
|  3 | math     |
|  4 | art      |
|  6 | sing     |
|  7 | computer |
+----+----------+
4 rows in set (0.00 sec)

mysql> rollback;    # 回滚事务
Query OK, 0 rows affected (0.04 sec)

mysql> select * from classes;   # 查看回滚事务后表数据
+----+------+
| id | name |
+----+------+
|  3 | math |
|  4 | art  |
|  6 | sing |
+----+------+
3 rows in set (0.00 sec)

mysql> insert into classes value(0,'computer');
Query OK, 1 row affected (0.09 sec)

mysql> commit;  # 提交事务，将缓存中的数据变更维护到物理表中
Query OK, 0 rows affected (0.00 sec)

mysql> select * from classes;
+----+----------+
| id | name     |
+----+----------+
|  3 | math     |
|  4 | art      |
|  6 | sing     |
|  8 | computer |
+----+----------+
4 rows in set (0.00 sec)

mysql>

例二，没有开启事务的改动会直接将数据写入物理表（无法回退）

mysql> select * from classes;
+----+----------+
| id | name     |
+----+----------+
|  3 | math     |
|  4 | art      |
|  6 | sing     |
|  8 | computer |
|  9 | english  |
+----+----------+
5 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from classes;
+----+----------+
| id | name     |
+----+----------+
|  3 | math     |
|  4 | art      |
|  6 | sing     |
|  8 | computer |
|  9 | english  |
+----+----------+
5 rows in set (0.00 sec)

mysql> update classes set name='linux' where id=4;
Query OK, 1 row affected (0.36 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from classes;
+----+----------+
| id | name     |
+----+----------+
|  3 | math     |
|  4 | linux    |
|  6 | sing     |
|  8 | computer |
|  9 | english  |
+----+----------+
5 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from classes;
+----+----------+
| id | name     |
+----+----------+
|  3 | math     |
|  4 | linux    |
|  6 | sing     |
|  8 | computer |
|  9 | english  |
+----+----------+
5 rows in set (0.00 sec)

注意!

(开启事务后)修改数据的命令会自动的触发事务，包括insert、update、delete
在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

3 索引

3.1 索引是什么

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

3.2 索引目的

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

3.3 索引原理

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7urydyUz-1606141957522)(pics/索引.jpg)]

3.4 索引的使用

查看索引 show index from 表名;
创建索引:如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致;字段类型如果不是字符串，可以不填写长度部分create index 索引名称 on 表名(字段名称(长度))
删除索引：drop index 索引名称 on 表名;

3.5 索引demo

3.5.1 创建测试表testindex

mysql> create table test_index(title varchar(10));
Query OK, 0 rows affected (0.71 sec)

3.5.2 向表中加入十万条数据(模拟大数据量)

from pymysql import connect

def main():
    # 创建Connection连接
    conn = connect(host='localhost',port=3306,database='foo',user='fancy',password='mysql',charset='utf8')
    # 获得Cursor对象
    cursor = conn.cursor()
    # 插入10万次数据
    for i in range(100000):
        cursor.execute("insert into test_index values('ha-%d')" % i)
    # 提交数据
    conn.commit()

if __name__ == "__main__":
    main()

3.5.3 查询

开启运行时间监测：set profiling=1;
查找第1万条数据ha-99999 select * from test_index where title='ha-99999';
查看执行的时间： show profiles;
为表title_index的title列创建索引： create index title_index on test_index(title(10));
执行查询语句： select * from test_index where title='ha-99999';
再次查看执行的时间 show profiles;

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from test_index where title='ha-99999';
+----------+
| title    |
+----------+
| ha-99999 |
+----------+
1 row in set (0.05 sec)

mysql> show profiles;
+----------+------------+-------------------------------------------------+
| Query_ID | Duration   | Query                                           |
+----------+------------+-------------------------------------------------+
|        1 | 0.05387250 | select * from test_index where title='ha-99999' |
+----------+------------+-------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> create index title_index on test_index(title(10));
Query OK, 0 rows affected (3.95 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_index where title='ha-99999';
+----------+
| title    |
+----------+
| ha-99999 |
+----------+
1 row in set (0.01 sec)

mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.05387250 | select * from test_index where title='ha-99999'   |
|        2 | 3.94901325 | create index title_index on test_index(title(10)) |
|        3 | 0.00046925 | select * from test_index where title='ha-99999'   |
+----------+------------+---------------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

mysql>