MySql视图，触发器，字符集，事物，存储引擎（四）

视图：view

查询每个栏目下最贵的商品

Select goods_id,goods_name,cat_id,shop_price from goods order by cat_id asc shop_price desc;

查询结果当成一张表看，如果某个查询结果出现的非常频繁，也就是拿这个结果当作子查询出现的非常频繁。

Create table g2 like goods;

Insert into g2 select……

上面两句是想保存一个查询结果到表里面，供其他查询用。

 

视图的定义：

视图是有查询结果形成的一张虚拟表，

视图的创建语法：

 Create view 视图名 select 语句；

为什么要视图?

答：

1.     可以简化查询，某一个复杂的查询，翻来覆去的查。

简化查询的例子：

2.     进行权限控制

把表的权限封闭，但是开放相应的视图权限，视图里只开放部分数据。

3.  大数据分表时可以用到

比如，表的行数超过200万时，就会变慢

可以把一张表的数据拆成四张表来存放

比如：News表

News  id，1,2,3,4

News1，news2，news3，news4表

把一张表的数据分散到四张表里，分散的方法有很多，最常用的可以用id取模来计算(把数据均匀的分到几张表里)

Id%4+1 =[1,2,3,4]

比如$_GET[‘id’] = 17, 17%4+1 =2,$tableName = ‘news’.’2’

Select * fromnews2 where id =17;

还可以用视图，把四张表形成一张视图

Create view newsas select from news2 union….

视图的删除：

Drop view；

视图的修改：

Alter view as select …

视图与表的关系：

视图的数据来源于表，若表的数据改变，则视图的数据自动改变。是表的查询结果。

修改视图的数据时，表的数据也会跟着修改。

但是视图也并不总是增删改的，视图的数据与表的数据有严格的一一对应时，可以修改。对于insert还应注意，视图必须包含表中没有默认值的列。

视图的算法

algorithm

Algorithm =merge/temptable/underfined

Merge:当引用视图时，引用视图的语句与定义视图的语句合并

Temptable：当引用视图时，根据视图的创建语句建立一个临时表

Underfined：未定义，

Merge意味着视图只是一个规则，语句规则，当查询视图时，把查询视图的语句与创建时的语句等合并，分析，形成一条语句。

而temptable是根据创建语句瞬间创建一张临时表，然后查询视图的语句从该临时表查数据。

 

字符集

字符集与校对集

Create table 表名（

列声明

）charset utf8；
mysql 的字符集设置非常灵活

可以设置服务器默认字符集

数据库默认字符集

表默认字符集

列字符集

如果某一个级别没有指定字符集，则继承上一级，

以表声明utf8为例：

存储的数据在表中，最终是utf8；

1.    告诉服务器，我发送的数据是什么编码的。character_set_client

2.    告诉转换器，转换成什么编码的character_set_connection

3.    查询的结果用什么编码character_set_results

如果以上三者都为字符集N，则可以简写为

Set names N;

推论：什么时候会乱码？

1.    Client声明与事实不符

2.    Result与客户端页面不符的时候

什么时间将会丢失数据？

 

校对集：字符集的排序规则

一个字符集可以有一个或者多个排序规则

Utf8默认utf8_general_ci不区分大小写

 

阶段总结（三）

Union的用法

合并查询的结果，取select结果的并集

对于重复的行去掉，如果不去重复，可以用union all

Union的要求：

各select查出的列数一致

如果子句中用了order by limit

那么子句要用小括号（）抱起来

如果子句只用order by 没有limit

Orderby被优化掉，不起作用

 

左连接、右连接、内连接

Select ta.列,tb.列

Ta left/right/inner join tb

On ta.列 = tb.列 （关系不一定是等于）

Where…

 

左连接与右连接：
可以相互转化

A left b à b right a

内连接：inner join

左右连接的交集，两张表能相互匹配上的行

表的管理à列的增删改

给表增加列：

Alter table 表名 add 列声明  [after/first]

修改表的列：

Alter table 表名 change 待修改列名 列声明

 删除别的列：

 Alter table 表名 drop 列名

 

视图 view

视图是一张虚拟的表，没有真实的数据存在，只是一张与表的一种查询产生的关系。

语法：

Create [algorithm =merge/temptable/underfined] view viewname as select…..;

Merge是将创建视图时的语句和查询视图的语句合并成一条语句。

Temptable：是创建一张临时表，merge是一条语句，用的时候较多。

 

字符集和校对集

客户端—》【转换器】--》服务器

客户端使用的字符集：set character_set_client = gbk/utf8

转换器转换后的字符集:set character_set_connection = gbk/utf8

返回给客户端的字符集：set character_set_results = gbk/utf8

总和：set names gbk/utf8;

 

校对集：就是对排序的规则;

一种字符集对应一种或多种校对集；

Create table（）charset utf8；

触发器：

学习目标：

触发器的定义：

触发器的应用场合：

掌握触发器的创建语法：

会创建简单的触发器：

1.    触发器：trigger，（枪击，扳机，引线）见识某种情况，并触发某种操作。能监视增删改，触发操作：增删改

2.    当向一张表中添加或删除记录时，需要在相关表中进行同步操作。比如：当一个订单产生时，订单所购的商品的库存量相应减少。

当表上某列数据的值与其他表中的数据有联系时。

比如：当某客户进行欠款消费，可以在生成订单时通过设计触发器判断该客户的累计欠款是否超过了最大限度。

当需要对某张表进行跟踪时。

比如：当有新订单产生时，需要及时通知相关人员进行处理，此时可以在订单表上设计添加触发器加以实现。

3.    监视地点：table，监视事件：insert/update/delete，触发时间：after/before，触发事件：insert/update/delete

 

首先需要修改：

Delimiter$,遇到$结束语句开始执行。

创建触发器语法：

Create trigger triggerName

After/before

insert/update/delete on 表名

For  each row

Begin

Sql语句

End

 

删除触发器的语法：

 Drop trigger triggerName

如何在触发器中引用行的值，对于insert而言，新增的行用new来表示，行中的每一列的值，用new.列名来表示。

对于delete来说，原本有一行，后来被删除想引用被删除的这一行，用old来表示，old列名就可以引用被删行中的值。

对于update来说，原本有一行，修改后还是那一行，修改前的数据用old来表示，old列名引用被修改之前行中的值，修改后的数据用new来表示，new列名引用被修改之前前行中的值，

例：

删除一个订单，库存量相应增加

修改订单的数量时，库存相应改变

这里只是修改了订单的数量，若也要修改gid，即订单商品类别，则需要修改触发器：

触发器里after和before的区别

After是先完成数据的增删改，再触发，触发中的语句晚于增删改，不能对前面的增删改产生影响

Before是先完成触发，再增删改，触发的语句先于监视增删改，我们有机会判断，修改即将发生的操作

查看所有的触发器

Show  triggers;

 

存储引擎

表里的数据存储在硬盘上，具体是如何存储的？

存储的方式有很多，同样的一张表的数据对于用户来说，无论什么样的存储引擎，用户看到的数据是一样的，对于服务器来说是不同的。

数据库对同样的数据，有不同的存储方式和管理方式，在mysql中，称为存储引擎。

常用的表的引擎有myisam和innodb

Myisam：批量插入速度快，不支持事务，锁表。

innoDB：批量插入速度相对较慢，支持事务，锁行。

全文索引，目前mysql5.5都已经支持。

讨论innoDB支持事务，myisam不支持事务。

 

事务：

事务的acid特性

原子性：

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
比如说，一个事务开始更新100行记录，但是在更新了20行之后（因为某种原因）失败了，那么此时数据库会回滚（撤销）对那20条记录的修改。

一致性：
在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
事务将数据库从一个一致性状态带入另一个一致性状态。 比如说，在一个银行事务（在描述关系数据库事务的特性时，基本上都是用银行事务来作为描述对象的）中，需要从存储账户扣除款项，然后在支付账户中增加款项。 如果在这个中转的过程发生了失败，那么绝对不能让数据库只执行其中一个账户的操作，因为这样会导致数据处于不一致的状态（这样的话，银行的账目上，借贷就不平衡了）。
如果数据库系统运行中发生故障，有些事物尚未完成就被迫中断了，系统就将此事务中对数据库的所有已经完成的操作全部撤销，滚回到事务开始时的一致状态。

隔离性：
两个事务的执行是互不干扰的，一个事务不可能看到其他事务运行时，中间某一时刻的数据。
这个特性是说，直到事务结束时（commit/rollback），其他事务（或者会话）对此事务所操作的数据都不可见（但并不是说其他会话的读取会被阻塞）。 比如说，一个用户正在修改hr.employees表，但是没有提交，那么其他用户在这个修改没有提交之前是看不到这个修改的。

持久性：
在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。
被提交的更改会永久地保存到数据库中（并不是说以后就不可以修改）。 事务提交之后，数据库必须通过“恢复机制”来确保事务更改的数据不会丢失。

转账

李三 à支出500，李三-500

赵四 à收到500，赵四+500

关于事务的引擎：选用innoDB/bdb

 

查看mysql服务器的模式：

Showvariables like ‘zmodez’;

语法：

开启事务：

Sql…

Commit提交/rollback回滚

注意：当一个事务commit或者roolback之后，事务就已经结束。

只要事务没有提交或回滚，事务处理都会看不见.

事物的基本原理（了解）