mysql数据库操作

为什么要视图?

答:1:可以简化查询

2: 可以进行权限控制

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

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

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

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

News表

Newsid, 1,2,3,4

News1,news2,news3,news4表

把一张表的数据分散到4张表里,分散的方法很多,

最常用可以用id取模来计算. 

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

比如 $_GET[‘id‘] = 17,

17%4 + 1 = 2,  $tableName = ‘news‘.‘2‘

Select * from news2 where id = 17;

还可以用视图, 把4张表形成一张视图

Create view news as  select from n1 union select from n2 union.........

视图的修改

Alter view as select xxxxxx

视图与表的关系

视图是表的查询结果,自然表的数据改变了,影响视图的结果.

视图改变了呢?

0: 视图增删改也会影响表

1: 但是,视图并是总是能增删改的.

答: 视图的数据与表的数据 一一对应时,可以修改.

对于视图insert还应注意,

视图必须包含表中没有默认值的列. 

视图的algorithm

Algorithm = merge/ temptable/ undefined

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

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

Undefined:未定义,自动,让系统帮你选.

Merge,意味着视图只是一个规则,语句规则, 当查询视图时,

把查询视图的语句(比如where那些)与创建时的语句where子句等合并,分析.

形成一条select 语句.

例: 创建视图的语句:

mysql> create view g2 as select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc;

查询视图的语句:

 select * from g2 group by cat_id;

最终执行的语句:

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

而temptable是根据创建语句瞬间创建一张临时表,

然后查询视图的语句从该临时表查数据.

create altorethm=temptable view g2 as select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc;

查询视图的语句:

 select * from g2 group by cat_id;

最终执行的2句话: 取数据并放在临时表,然后去查临时表.

字符集与校对集

Create table 表名(

列声明

) charset utf8;

Mysql的字符集设置非常灵活

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

数据库默认字符集

表默认字符集

列字符集

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

以表声明utf8为例:

存储的数据在表中,最终是utf8;

‘中国‘,

1:告诉服务器,我给你发送的数据是什么编码的? character_set_client

2:告诉转换器,转换成什么编码? Character_set_connection

3:查询的结果用什么编码? Character_set_results 

如果以上3者都为字符集N, 则可以简写为 set names 

推论: 什么时将会乱码?

1: client声明与事实不符

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

什么时间将会丢失数据?Connetion和服务器的字符集比client小时.

校对集: 指字符集的排序规则

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

以Utf8为例, 我们默认使的utf8_general_ci 规则,也可以按二进制来排, utf8_bin

怎么样声明校对集?

Create table ()... Charset utf8 collate utf8_general_ci;

注意:声明的校对集必须是字符集合法的校对集.
触发器:trigger, 枪击,扳击,引线的意思

一触即发,

作用: 监视某种情况并触发某种操作.

观察以下场景:

一个电子商城,

商品表,  g

订单表, o

完成下单与减少库存的逻辑

Insert into o (gid,num) values (2,3);  // 插入语句

Update g set goods_num = goods_num - 3 where id = 2;// 更新过程

这两个逻辑可以看成一个整体,或者说, insert ---> 引来 update

用触发器可以解决上述问题.

我们可以监视某表的变化,当发生某种变化时,触发某个操作.

触发器: 

能监视: 增,删,改

触发操作: 增,删,改

四要素:

监视地点

监视事件

触发时间

触发事件

创建触发器的语法

Create trigger triggerName 

After/before insert/update/delete  on 表名

For each row #这句话是固定的

Begin

Sql语句;  # 一句或多句,insert/update/delete范围内

End;

删除触发器的语法:

Drop trigger 触发器名

查看触发器

Show triggers

如何在触发器引用行的值

对于insert而言, 新增的行 用new 来表示,

行中的每一列的值 ,用new.列名来表示.

对于 delete来说, 原本有一行,后来被删除,

想引用被删除的这一行,用old,来表示,  old.列名,就可以引用被删行中的值.

对于update来说,

被修改的行, 

修改前的数据 ,用 old来表示, old.列名引用被修改之前行中的值

修改后的数据,用new 来表示, new.列名引用被修改之后行中的值

触发器里after 和before的区别

After 是先完成数据的增,删,改再触发,

触发的语句晚于监视的增,删,改,无法影响前面的增删改动作.

Before是先完成触发,再增删改,

触发的语句先于监视的增,删,改发生,我们有机会判断,修改即将发生的操作.

典型案例:

对于所下订单,进行判断,如果订单的数量 > 5 ,就认为是恶意订单,

强制把所订的商品数量改成5

存储引擎

一部电影, mp4,wmv,avi,rmvb,flv, 同样的一部电影,在硬盘上有不同的存储格式,

所占空间与清晰程度也不一样.

表里的数据存储在硬盘上,具体如何存储的?

存储的方式方法也有多种.

对于用户来说       同样一张表的数据,无论用什么引擎来存储,用户看到的数据是一样的.

但是对于服务器来说,有区别

常用的表的引擎

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

Innodb, 批量插入相对较慢,支持事务,锁行.

全文索引:目前5.5版本,myisam,innodb都已经支持

事务:

通俗的说事务: 指一组操作,要么都成功执行,要么都不执行.---->原子性

在所有的操作没有执行完毕之前,其他会话不能够看到中间改变的过程-->隔离性

事务发生前,和发生后,数据的总额依然匹配----->一致性

事务产生的影响不能够撤消------>持久性

如果出了错误,事务也不允许撤消, 只能通过"补偿性事务"

转账

李三: --->支出 500, 李三 -500

赵四: ---->收到500, 赵四 +500

关于事务的引擎:

选用innodb /bdb

开启事务:  start transaction;

Sql....

Sql....

Commit 提交

rollback 回滚

注意: 当一个事务commit,或者rollback就结束了

注意:有一些语句会造成事务的隐式的提交,比如 start transaction

事务 的基本原理 (了解)

目前我们所学的是系统自带的备份功能, mysqldump

Mysqldump可以导出

库

表

例1: 导出mugua库下面的表

Mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 地址/备份文件名称

导出的是建表语句及insert语句

例2:如何导出一个库下面的所有表?

Mysqldump -u用户名 -p密码 库名 > 地址/备份文件名称

例3: 如何导出以库为单位导出?

Mysqldump -u用户名 -p密码 -B 库1 库2 库3 > 地址/备份文件名称

例4: 如何导出所有库?

Mysqldump -u用户名 -p密码 -A > 地址/备份文件名称

恢复:

1:登陆到mysql命令行

对于库级的备份文件

Mysql> source 备份文件地址

对于表级的备份文件

Mysql > use 库名

Mysql> source 备份文件地址

2:不登陆到mysql命令行

针对库级的备份文件

Mysql  -u用户名 -p密码 < 库级备份文件地址

针对表级的备份文件

Mysql  -u用户名 -p密码 库名 < 表级备份文件地址

索引

索引:是针对数据所建立的目录.

作用: 可以加快查询速度

负面影响: 降低了增删改的速度.

案例:

设有新闻表15列,10列上有索引,共500W行数据, 如何快速导入?

1:把空表的索引全部删除

2:导入数据

3:数据导入完毕后,集中建索引.

索引的创建原则:

1:不要过度索引

2:在where条件最频繁的列上加.

3:尽量索引散列值,过于集中的值加索引意义不大.

索引的类型

普通索引: index 仅仅是加快查询速度.

唯一索引: unique index 行上的值不能重复

主键索引: primary key 不能重复.

主键必唯一,但是唯一索引不一定是主键.

一张表上,只能有一个主键, 但是可以用一个或多个唯一索引.

全文索引 : fulltext index

查看一张表上所有索引

Show index from 表名

建立索引

Alter table 表名 add index /unique/fulltext [索引名] (列名)

Alter table 表名  add primary key (列名)   // 不要加索引名,因为主键只有一个

删除索引

删除非主键索引:Alter table 表名 drop index 索引名;

删除主键: alter table 表名 drop primary key

关于全文索引的用法

Match (全文索引名) against (‘keyword‘);

关于全文索引的停止词

全文索引不针对非常频繁的词做索引,

如this, is, you, my等等.

全文索引:在mysql的默认情况下, 对于中文意义不大.

因为英文有空格,标点符号来拆成单词,进而对单词进行索引.

而对于中文,没有空格来隔开单词,mysql无法识别每个中文词.

存储过程: procedure

概念类似于函数,就是把一段代码封装起来,

当要执行这一段代码的时候,可以通过调用该存储过程来实现.

在封装的语句体里面,可以用if/else, case,while等控制结构.

可以进行sql编程.

查看现有的存储过程:

Show procedure status

删除存储过程

Drop procedure 存储过程的名字

调用存储过程

Call 存储过程名字();

第1个存储过程 ,体会"封装sql"

第2个存储过程, 体会"参数"

第3个存储过程,体会"控制结构"

第4个存储过程,体会"循环"

在mysql中,存储过程和函数的区别,

一个是名称不同,

二个就是存储过程没有返回值.

mysql数据库操作,布布扣,bubuko.com

mysql数据库操作

原文：http://keyue.blog.51cto.com/6559543/1438218