MySQL由浅至深(七).ppt

《MySQL由浅至深(七).ppt》由会员分享，可在线阅读，更多相关《MySQL由浅至深(七).ppt（25页珍藏版）》请在三一办公上搜索。

1、,MySQL课程,高级2第七章,要做就做最好，要不就不做！,2023/6/28,MySQL视图,ALTER VIEW语法 CREATE VIEW语法 DROP VIEW语法 SHOW CREATE VIEW语法,在5.1版MySQL服务器中提供了视图功能（包括可更新视图）。本章讨论了下述主题：使用CREATE VIEW或ALTER VIEW创建或更改视图。使用DROP VIEW销毁视图。使用SHOW CREATE VIEW显示视图元数据。,2023/6/28,MySQL-CREATE VIEW语法,CREATE OR REPLACE ALGORITHM=UNDEFINED|MERGE|TEMP

2、TABLE VIEW view_name(column_list)AS select_statement WITH CASCADED|LOCAL CHECK OPTION该语句能创建新的视图，如果给定了OR REPLACE子句，该语句还能替换已有的视图。select_statement是一种SELECT语句，它给出了视图的定义。该语句可从基表或其他视图进行选择。该语句要求具有针对视图的CREATE VIEW权限，以及针对由SELECT语句选择的每一列上的某些权限。对于在SELECT语句中其他地方使用的列，必须具有SELECT权限。如果还有OR REPLACE子句，必须在视图上具有DROP权限。

3、,2023/6/28,MySQL创建视图,视图属于数据库。在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，创建时，应将名称指定为db_name.view_name。mysql CREATE VIEW test.v AS SELECT*FROM t;表和视图共享数据库中相同的名称空间，因此，数据库不能包含具有相同名称的表和视图。视图必须具有唯一的列名，不得有重复，就像基表那样。默认情况下，由SELECT语句检索的列名将用作视图列名。要想为视图列定义明确的名称，可使用可选的column_list子句，列出由逗号隔开的ID。column_list中的名称数目必须等于SELEC

4、T语句检索的列数。SELECT语句检索的列可以是对表列的简单引用。也可以是使用函数、常量值、操作符等的表达式。对于SELECT语句中不合格的表或视图，将根据默认的数据库进行解释。通过用恰当的数据库名称限定表或视图名，视图能够引用表或其他数据库中的视图。能够使用多种SELECT语句创建视图。视图能够引用基表或其他视图。它能使用联合、UNION和子查询。SELECT甚至不需引用任何表。在下面的示例中，定义了从另一表选择两列的视图，并给出了根据这些列计算的表达式：,mysql CREATE TABLE t(qty INT,price INT);mysql INSERT INTO t VALUES(3

5、,50);mysql CREATE VIEW v AS SELECT qty,price,qty*price AS value FROM t;mysql SELECT*FROM v;,2023/6/28,MySQL视图的定义,视图定义服从下述限制：SELECT语句不能包含FROM子句中的子查询。SELECT语句不能引用系统或用户变量。SELECT语句不能引用预处理语句参数。在存储子程序内，定义不能引用子程序参数或局部变量。在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。在定义中不能引用TEMPO

6、RARY表，不能创建TEMPORARY视图。在视图定义中命名的表必须已存在。不能将触发程序与视图关联在一起。,2023/6/28,MySQL-WITH CHECK OPTION,对于可更新视图，可给定WITH CHECK OPTION子句来防止插入或更新行，除非作用在行上的select_statement中的WHERE子句为“真”。在关于可更新视图的WITH CHECK OPTION子句中，当视图是根据另一个视图定义的时，LOCAL和CASCADED关键字决定了检查测试的范围。LOCAL关键字对CHECK OPTION进行了限制，使其仅作用在定义的视图上，CASCADED会对将进行评估的基表进

7、行检查。如果未给定任一关键字，默认值为CASCADED。请考虑下述表和视图集合的定义：mysql CREATE TABLE t1(a INT);mysql CREATE VIEW v1 AS SELECT*FROM t1 WHERE a CREATE VIEW v2 AS SELECT*FROM v1 WHERE a0 WITH LOCAL CHECK OPTION;mysql CREATE VIEW v3 AS SELECT*FROM v1 WHERE a0 WITH CASCADED CHECK OPTION;,注：这里，视图v2和v3是根据另一视图v1定义的。v2具有LOCAL检查选项，

8、因此，仅会针对v2检查对插入项进行测试。v3具有CASCADED检查选项，因此，不仅会针对它自己的检查对插入项进行测试，也会针对基本视图的检查对插入项进行测试。在下面的语句中，介绍了这些差异：mysql INSERT INTO v2 VALUES(2);mysql INSERT INTO v3 VALUES(2);ERROR 1369(HY000):CHECK OPTION failed test.v3,2023/6/28,MySQL-LOCAL与CASCADED,这里，视图v2和v3是根据另一视图v1定义的。v2具有LOCAL检查选项，因此，仅会针对v2检查对插入项进行测试。v3具有CASC

9、ADED检查选项，因此，不仅会针对它自己的检查对插入项进行测试，也会针对基本视图的检查对插入项进行测试。在下面的语句中，介绍了这些差异：mysql INSERT INTO v2 VALUES(2);mysql INSERT INTO v3 VALUES(3);ERROR 1369(HY000):CHECK OPTION failed test.v3,注：视图的可更新性可能会受到系统变量updatable_views_with_limit的值的影响。,2023/6/28,MySQL-ALTER VIEW语法,ALTER ALGORITHM=UNDEFINED|MERGE|TEMPTABLE VI

10、EW view_name(column_list)AS select_statement WITH CASCADED|LOCAL CHECK OPTION 该语句用于更改已有视图的定义。其语法与CREATE VIEW类似。mysql alter view v3 as select*from t1 where a 1 with local check option;,注：CREATE VIEW和DROP权限，也需要针对SELECT语句中引用的每一列的某些权限。,2023/6/28,MySQL视图关联性,某些视图是可更新的。也就是说，可以在诸如UPDATE、DELETE或INSERT等语句中使用它

11、们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。更具体地讲，如果视图包含下述结构中的任何一种，那么它就是不可更新的：聚合函数（SUM(),MIN(),MAX(),COUNT()等）。DISTINCT GROUP BY HAVING UNION或UNION ALL 位于选择列表中的子查询Join FROM子句中的不可更新视图WHERE子句中的子查询，引用FROM子句中的表。mysql alter view v3 as select count(*)from t1 where a 1 with local

12、 check option;,ERROR 1368(HY000):CHECK OPTION on non-updatable view test.v3,2023/6/28,MySQL-DROP VIEW语法,DROP VIEW IF EXISTS view_name,view_name.RESTRICT|CASCADE DROP VIEW能够删除1个或多个视图。必须在每个视图上拥有DROP权限。可以使用关键字IF EXISTS来防止因不存在的视图而出错。给定了该子句时，将为每个不存在的视图生成NOTE。,2023/6/28,MySQL-SHOW CREATE VIEW语法,SHOW CREAT

13、E VIEW view_name 该语句给出了1个创建给定视图的CREATE VIEW语句。mysql SHOW CREATE VIEW v;mysql SHOW CREATE VIEW vG;,2023/6/28,MySQL分区,分区概述 分区类型分区管理,2023/6/28,MySQL分区概述,SQL语言的使用独立于它所使用的任何数据结构或图表、表、行或列下的介质。但是，大部分高级数据库管理系统已经开发了一些根据文件系统、硬件或者这两者来确定将要用于存储特定数据块物理位置的方法。在MySQL中，InnoDB存储引擎长期支持表空间的概念。分区又把这个概念推进了一步，它允许根据可以设置为任意大

14、小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。函数根据用户指定的分区类型来选择，把用户提供的表达式的值作为参数。该表达式可以是一个整数列值，或一个作用在一个或多个列值上并返回一个整数的函数。这个表达式的值传递给分区函数，分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数，也不能是任意数。它不能包含任何查询，但是实际上可以使用MySQL 中任何可用的S

15、QL表达式，只要该表达式返回一个小于MAXVALUE（最大可能的正整数）的正数值。可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区，例如：mysql SHOW VARIABLES LIKE%partition%;,2023/6/28,MySQL-PARTITION,要为某个分区表配置一个专门的存储引擎，必须且只能使用STORAGE ENGINE 选项，这如同为非分区表配置存储引擎一样。但是，必须记住STORAGE ENGINE（和其他的表选项）必须列在用在CREATE TABLE语句中的其他任何分区选项之前。下面的例子给出了怎样创建一个通过HASH分成6个分区、使用In

16、noDB存储引擎的表：mysql CREATE TABLE t2(id INT,amount DECIMAL(7,2),tr_date DATE)ENGINE=INNODB PARTITION BY HASH(MONTH(tr_date)PARTITIONS 6;,注：分区适用于一个表的所有数据和索引；不能只对数据分区而不对索引分区，反之亦然，同时也不能只对表的一部分进行分区。,2023/6/28,MySQL分区的优点,分区的一些优点包括：与单个磁盘或文件系统分区相比，可以存储更多的数据。对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些

17、情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL 分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。涉及到例如SUM()和 COUNT()这样聚合函数的查询，可以很容易地进

18、行并行处理。这种查询的一个简单例子如“SELECT salesperson_id,COUNT(orders)as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。,2023/6/28,MySQL分区类型,RANGE 分区：基于一个给定连续区间的列值，把多行分配给分区。LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。HASH分区：基于用户定

19、义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。,注：无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录，记住这一点非常重要。当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区。例如，如果你的表使用4个分区，那么这些分区就编号为0,1,2,和3。对于RANGE和LIST分区类型，确认每个分区编号都定义了一个分

20、区，很有必要。对HASH分区，使用的用户函数必须返回一个大于0的整数值。对于KEY分区，这个问题通过MySQL服务器内部使用的 哈希函数自动进行处理。,2023/6/28,MySQL分区命名名称,分区的名字基本上遵循其他MySQL 标识符应当遵循的原则，例如用于表和数据库名字的标识符。但是应当注意，分区的名字是不区分大小写的。例如，下面的CREATE TABLE语句将会产生如下的错误：mysql CREATE TABLE t2(val INT)PARTITION BY LIST(val)(PARTITION mypart VALUES IN(1,3,5),PARTITION MyPart VA

21、LUES IN(2,4,6);错误1488(HY000):表的所有分区必须有唯一的名字。这是因为MySQL认为分区名字mypart和MyPart没有区别。,2023/6/28,MySQL-RANGE分区,按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。在下面的几个例子中，假定你创建了一个如下的一个表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。mysql CREATE TABLE temp(id INT NOT NULL,na

22、me VARCHAR(30)PARTITION BY RANGE(id)(PARTITION p0 VALUES LESS THAN(6),PARTITION p1 VALUES LESS THAN(11),PARTITION p2 VALUES LESS THAN(16),PARTITION p3 VALUES LESS THAN(21);,注：按照这种分区方案，在商店1到5工作的雇员相对应的所有行被保存在分区P0中，商店6到10的雇员保存在P1中，依次类推。注意，每个分区都是按顺序进行定义，从最低到最高。这是PARTITION BY RANGE 语法的要求；在这点上，它类似于C或Java中

23、的“switch.case”语句。,2023/6/28,MySQL-RANGE分区,按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。在下面的几个例子中，假定你创建了一个如下的一个表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。,但是如果增加了一个编号为第21的商店，将会发生什么呢？,2023/6/28,MySQL-RANGE分区,按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连

24、续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。在下面的几个例子中，假定你创建了一个如下的一个表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。mysql CREATE TABLE temp(id INT NOT NULL,name VARCHAR(30)PARTITION BY RANGE(id)(PARTITION p0 VALUES LESS THAN(6),PARTITION p1 VALUES LESS THAN(11),PARTITION p2 VALUES LESS THAN(16),PARTITION p3

25、VALUES LESS THAN(21);,注：按照这种分区方案，在商店1到5工作的雇员相对应的所有行被保存在分区P0中，商店6到10的雇员保存在P1中，依次类推。注意，每个分区都是按顺序进行定义，从最低到最高。这是PARTITION BY RANGE 语法的要求；在这点上，它类似于C或Java中的“switch.case”语句。,但是如果增加了一个编号为第21的商店，将会发生什么呢？MAXVALUE,2023/6/28,MySQL-LIST分区,MySQL中的LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区

26、的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN(value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。假定有20个音像店，分布在4个有经销权的地区，如下表所示：mysql CREATE TABLE temp(id INT NOT NULL,name VARCHAR(30)PARTITION BY LIST(id)(P

27、ARTITION p0 VALUES IN(3,5,6,9,17),PARTITION p1 VALUES IN(1,2,10,11,19,20),PARTITION p2 VALUES IN(4,12,13,14,18),PARTITION p3 VALUES IN(7,8,15,16);,注：LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。,2023/6/28,MySQL-HASH分区,HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而

28、在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。例如，下面的语句创建了一个使用基于“id”列进行 哈希处理的表，该表被分成了4个分区：mysql CREATE TABLE temp(id INT NOT NULL,name VARCHAR(30)PARTITION BY HASH(id)PARTITIONS 4;MySQL还支持线性哈希LINEAR HASH分区功能。,注：如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。例外：对于NDB Cluster（簇）表，默认的分区数量将

29、与簇数据节点的数量相同，这种修正可能是考虑任何MAX_ROWS 设置，以便确保所有的行都能合适地插入到分区中。,2023/6/28,MySQL-KEY分区,按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的 哈希函数，这些函数是基于与PASSWORD()一样的运算法则。mysql CREATE TABLE temp(id INT NOT NULL,name VARCHAR(30)PARTITION

30、 BY LINEAR KEY(id)PARTITIONS 4;,注：在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。,2023/6/28,MySQL分区管理,RANGE和LIST分区的管理 HASH和KEY分区的管理 分区维护 获取关于分区的信息,注：要改变一个表的分区模式，只需要使用带有一个“partition_options”子句的ALTER TABLE 的命令。这个子句和与创建一个分区表的CREATE TABLE命令一同使用的子句有相同的语法，并且总是以关键字PARTITION B

31、Y 开头。例如，假设有一个使用下面CREATE TABLE语句建立的按照RANGE分区的表：现在，要把这个表按照使用id列值作为键的基础，通过KEY分区把它重新分成两个分区，可以使用下面的语句：ALTER TABLE trb3 PARTITION BY KEY(id)PARTITIONS 2；这和先删除这个表、然后使用“CREATE TABLE trb3 PARTITION BY KEY(id)PARTITIONS 2；”重新创建这个表具有同样的效果。,2023/6/28,MySQL-分区查看,计划用于分区表的、两个附加的SHOW命令是：目前还不能使用计划中,等待MySQL 升级。SHOW PARTITIONSSHOW PARTITION STATUS目前可以使用以下命令查看分区信息mysql show create table temp;/分区表信息mysql explain partitions select*from temp;/查看分区情况mysql select*from information_schema.partitions where table_schema=schema()and table_name=temp;/字典查看分区,