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

视图：view

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

Selectgoods_id,goods_name,cat_id,shop_price from goods order by cat_id asc shop_pricedesc;

查询结果当成一张表看，

如果某个查询结果出现的非常频繁，也就是拿这个结果当作子查询出现的非常频繁

Create table g2 like goods;

Insert into g2 select……

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

 

视图的定义：

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

视图的创建语法：

 Create view 视图名 select 语句；

为什么要视图?

答：

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

简化查询的例子：

2.     进行权限控制

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

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

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

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

比如：News表

Newsid，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 asselect …

视图与表的关系：

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

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

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

视图的算法algorithm

Algorithm =merge/temptable/underfined

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

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

Underfined：未定义，

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

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

 

字符集

字符集与校对集

Create table 表名（

列声明

）charsetutf8；
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是一条语句，用的时候较多。

 

字符集和校对集

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

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

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

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

总和：set names gbk/utf8;

 

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

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

Create table（）charsetutf8；

触发器：

学习目标：

触发器的定义：

触发器的应用场合：

掌握触发器的创建语法：

会创建简单的触发器：

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是先完成触发，再增删改，触发的语句先于监视增删改，我们有机会判断，修改即将发生的操作

 

查看所有的触发器

Showtriggers;

 

存储引擎

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

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

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

常用的表的引擎有myisam和innodb

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

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

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

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

 

事务：

事务的acid特性

通俗的说，一组操作要么都成功执行，要么都不执行。

原子性（atomicity）在所有操作都没有执行完毕之前，其他会话不能够看到中间改变的过程。

隔离性（isolation）事务发生之前和发生之后数据的总额依然匹配。

一致性（consistency）事务产生的影响不能够撤销

持久性（durability）如果出现了错误，事务也不允许撤销，只能够”补偿事务”

原子性体现的是不可分割性，不可见性。

转账

李三 à支出500，李三-500

赵四 à收到500，赵四+500

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

 

查看mysql服务器的模式：

Showvariables like ‘zmodez’;

语法：

开启事务：

Sql…

Sql…

Commit提交/rollback回滚

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

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

事物的基本原理（了解）