事务、触发器、视图、索引

本周学习知识点：

一、子查询、连接查询

    1.子查询分类

2.连接查询以及分类

二、事务、触发器、视图

 

三、索引、数据导入导出 

   1.数据库优化

   2.索引

   3.数据库导入、导出

 

 

 

一、子查询、连接查询

  1.子查询分类

    a.根据子查询返回数据的类型   

       量子查询（子查询返回一个单一的值）

       行子查询（子查询返回一行） 

       子查询（子查询返回一列）

       表子查询（一行，多行，一列，多列）

    b.根据子查询出现的位置

       where 型子查询 

       from  型子查询

  2.列子查询：（返回一个列）

    在返回列的子查询中，我们可以使用 in，not in，any，all 完成

匹配

通过分析发现：父类为 1的都是 手机类型的子类

mysql> select *from category;

+--------+-------------------+-----------+

| cat_id | cat_name          | parent_id |

+--------+-------------------+-----------+

|      1 | 手机类型          |         0 |

|      2 | CDMA手机        |         1 |

|      3 | GSM手机          |         1 |

|      4 | 3G手机            |         1 |

|      5 | 双模手机           |         1 |

select cat_id from category where cat_name=’手机类型’;

select cat_name，cat_id from goods where cat_id in(select cat_id from category  

where parent_id=1);   in ()= any()

 

  3.from型子查询

     sql语句要求，from后面必须跟一个表，我们需要将子查询作为表存在（将查询结果起个别名） 例如：from（select子查询）as 别名

   需求：取得分类为 3或 5，商品名为 ”诺基亚”开头的数据

   select goods_name,cat_id from (select *from goods where cat_id=3 or cat_id=5) as new_goods where goods_name like ‘诺基亚’;

 

 4.连接查询分类

  由于select后面的cat_id不能确定是哪个表中的字段，所以会报错

解决这类问题

   ①可以在字段前增加表名，防止冲突

   ②也可以在设计表结构的时候，就为表结构设计一个冲突性小的字段

  join连接查询的分类（两大类：内连接、外连接）：

  内连接：只有左表和右表同时存在数据，才会在表中显示

  inner join  简写成join

  左外连接：

  left join  将左表所有的数据在结果中显示：

  右外连接：

  right join 将右表所有的数据在结果中显示：

 

 5.连接查询

mysql> select *from join1;

+----+---------+

| id | name    |

+----+---------+

|  1 | join1_a |

|  2 | join1_b |

|  3 | join1_c |

+----+---------+

mysql> select *from join2;

+----+---------+

| id | name    |

+----+---------+

|  2 | join2_b |

|  3 | join2_c |

|  4 | join2_d |

+----+---------+

   a.内连接

select * from join1 join join2 on join1.id=join2.id；

   b.左外连接

   select *from join1 left join join2 on join1.id=join2.id;

   c.右外连接

select * from join1 right join join2 on join1.id=join2.id;

6.统计每个商品分类下面的商品数量：

步骤：

1. 先通过分类id将分类表与商品表关联

select c.cat_name,g.goods_name from category as c left join goods as g on c.cat_id =g.cat_id;

2. 再按照分类名称分组：

select c.cat_name,g.goods_name from category as c left join goods as g on c.cat_id =g.cat_id group by c.cat_id;

3. 最后再统计：

select c.cat_name,g.goods_name,count(goods_name) from category as left join goods as g c.cat_id group by c.cat_id;

 

二、事务、触发器、视图

1.事务的概念：

事务就是逻辑上的一组操作，组成这组操作的各个单元，要么都成功，要么都失败

1. 1事务的使用步骤

开启事务之后，执行sql语句并没有直接影响到数据库，而是都执行成功后，我们提交了，这个时候才影响到数据库

执行sql语句：

①全部成功，提交（commit），将这些sql语句影响到数据库

②某些sql语句执行过程中失败了，需要将所有的sql语句回滚（rollback）,撤销对数据库的影响

1.2事务的原理

   如果执行的sql语句，怎么才会对数据库产生影响？

   DML----> 删除、更新、增加

   走两步：执行sql语句、提交

默认情况下，mysql数据库，自动提交，执行完sql语句，会自动提交到数据库

（1）start  transaction;  开启事务相当于将默认的自动提交机制关闭了

但是当我们提交之后，commit之后，将原来的自动提交机制恢复了（重置）

（2）但是我们如果使用 set autocommit =0 可以将自动提交关闭 ，set autocommit=1可以开启，再次执行提交（commit），并没有将默认的自动提交机制恢复

 

建议：使用start transaction 将自动提交机制关闭，如果必须使用 set autocommit 的话，需要在提交之后 通过set autocommit = 1 将原来的自动提交机制恢复

2. 事务的特点：

   1. 原子性： 一组操作可能有多个单元(sql语句)，要么都成功，要么都失败（不可分割）

   2. 一致性： 和原子性密切相关的，全部的数据必须是一致的

   3. 隔离性：（islotion）各个事务之间 相互隔离的，互补影响

   4. 永久性： 一旦数据提交了，对数据库的影响是持久的

存储引擎：

一张数据表，处理数据的格式、以及方法------存储引擎

myisam   ----->  不支持事务，但是查询的速度快（id----主键----）

innoDB ------->    没有索引，查询的时候 速度就慢了 ，支持事务（数据比较安全）

 

2. 触发器

2.1触发器的概念？

   就是数据库中的一段程序，用来监听数据表的行为，一旦这个行为发生了，马上执行一段sql语句

2.2触发器的语法格式：

  CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW  

  trigger_stmt 

  create trigger 触发器的名字   触发器的时间   触发器监听的事件  ON 数据表名  for each row 事件发生之后执行的代码

触发器的时间：   before   after 是在事件发生之前执行动作还是在事件发生之后执行某个动作

监听的事件：

监听这些事件： 增加   删除   修改  

new  ------表示新的记录（插入之后的（下订单之后的数量），更新之后的（））

old   ------表示旧的记录（删除的记录（数量），更新之前）

2.3利用触发器完成 下订单的案例：

  步骤：

1. 创建商品表 和 订单表

mysql> select *from `order`;

+----------+----------+-----------+

| order_id | goods_id | order_num |

+----------+----------+-----------+

|        2 |        1 |        10 |

+----------+----------+-----------+

mysql> select *from store;

+----------+------------+-----------+--------------+

| goods_id | goods_name | shop_prce | goods_number |

+----------+------------+-----------+--------------+

|        1 | nuojy      |   2000.00 |           90 |

|        2 | sanx       |   1000.00 |           30 |

|        3 | lianx      |   1500.00 |           36 |

+----------+------------+-----------+--------------+

2.给订单表绑定一个触发器 用来监听订单表，一旦订单表增加了一条记录，库存表就应该相应的减少库存量

create trigger trigger_goods_num after insert on  `order` for each row update store set goods_number=goods_number-new.order_num where goods_id=new.goods_id;

3.下一个订单，用来测试触发器是否在监听 

insert into `order` values(null,2,3);

 

3.视图

  1.1提出需求

     视图的使用：如果项目上线的时候，需要把数据表更改名字

改名字非常简单，但是php的应用程序中对数据表的所有操作都需要修改

  1.2 视图概念

数据库中的视图是一个虚拟表，其内容由查询定义 

虚拟表：

可以像表一样去处理视图，但是这个表不存储数据，他的结构和数据都是由视图中select语句来决定的

   1.3 创建视图

     create view 视图名 AS 查询语句

     再看 select * from category的执行结果

   1.4视图vs子查询

     create view category as select *from catagory；

     select name from (select *from catagory) as category;

   1.5视图相关操作

     查看视图： show create view 视图名

     修改视图： alter view 视图名 as select语句

     删除视图： drop view 视图名

   1.6视图作用

     1. 简单性，看到的就是需要的，对不需要的列可以隐藏

     2. 安全性  防止未授权用户查看特定的行和列

     3. 独立性  视图可以使应用程序和数据表在一定程度上独立

 

三、索引、数据导入导出

1.数据库优化

 a. 创建表时，选择合适的列类型

b. 数据库要符合3范式：

  1范式：表中的每条记录是原子性约束，只要数据库是关系型数据库，就 满足1NF

2范式：表中的记录，不能够出现完全一样的记录，就满足了二范式，一般说是通过设置主键来完成.

3范式：

表中的某个字段的信息，可以推导得出，不要出现冗余数据.

    c. 通过explain检测，优化具体的sql语句

    d. 适当建立索引

2.索引

  2.1mysql支持4大类索引：

a.主键索引：

   如果一个字段被定义为主键（primary key），则自动在该字段上建立主键索引

b.唯一索引:一个值只能有出现一次（一个字段在整个数据表内是唯一的，可以建立唯一索引），一旦找到数据，就不会往下继续查找速度

c.普通索引：在不是唯一的字段上建立索引的话，需要建立普通索引

d.全文索引：如果在某个字段上建立全文索引，将记录内部的关键字提取出来，然后对关键字建立索引，与存储引擎相关，建立全文索引的话，必须是myisam

e.复合索引：我们在建立索引的时候，可以针对多个字段在一起建立索引，当使用多个字段在一起建立索引时，称之为复合索引

3.索引的语法

 create [UNIQUE|FULLTEXT]  index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..);

 alter table table_name ADD INDEX [index_name] (index_col_name,...)

默认是普通索引，指定主键后，自动创建主键索引create [指定索引类型] index 索引名 on 表名 （列名，在哪个字段上建立索引）

4.删除索引

DROP INDEX index_name ON tbl_name;

alter table table_name drop index index_name;

5. 查看索引

show index from table_name;   查看某个表上的索引

show keys from table_name;

desc table_Name;

6.查看索引的效果

possible_keys    当前查询可能用到的索引

key            实际用到的索引

rows           获得当前的结果，需要遍历的记录行数

7.索引的代价

索引可以提高查询速度，但是也有代价：

dml语句会变慢（update、delete、insert），因为你创建数据后，还要维护索引，给新增加的数据创建索引，而且索引文件会占用磁盘空间

8. 哪些字段适合建索引

   a.较频繁的作为查询条件的字段应该创建索引

   select * from emp where empno = 1; name

   b.唯一性太差的字段不适合建立索引（即使频繁作为查询条件）

   select * from emp where sex = ‘男’; 

   c.更新非常频繁的字段不适合建立索引（登录状态）

   select * from emp where logincount = 1;

   d.不会出现在where 字句中的字段不该创建索引

3.数据库导入、导出

a.数据库一些特殊操作

1. distinct  去除重复的操作  select distinct 字段 from 表

2. delete  vs  truncate 

3. insert into 表名  select语句

  （需要注意，select查询的字段需要和插入的表结构一致）

b.数据库导入

1. load data infile '地址' into table 表名

   加载进来的文件默认是 以制表符 分割开的

   适合导入大量数据

2. mysql  -u root -p  数据库名 <back.sql 

3. source 文件名  如我输入的命令行：mysql>source school.sql;  

    c.数据库导出:mysqldump -u root -p school>school.sql

     1. 在数据库的配置文件中  my.ini   datadir 保存了数据库数据表的保存位置，对应每个数据库都会有一个子目录，进入到每个数据库中，会看到3个文件

 

 

 

但是以上只适用于 myisam存储引擎，innoDB 存储引擎只存储一个 

 

 

 

将这些文件复制到我们的数据库文件夹下，即可完成备份

3. 使用mysql自身的mysqldump这个命令 备份

  dump---->导出，倒垃圾    

  mysqldump  把数据库导出

 

 

 

这个命令和mysql这个命令，属于一个级别，执行这个命令时，和执行mysql -u 这个命令时一样的

 

如何导入数据库或者数据表？

加载进来的文件默认是 以制表符 分割开的(适合导入大量数据)

 

学习总结： 一、需求：主队id   主队名称   结果   客队id   客队名称   比赛时间 mysql> select  *from mch; +---------+------------+-------------+-------------+------------+ | matchID | hostTeamID | guestTeamID | matchResult | matchTime  | +---------+------------+-------------+-------------+------------+ |       1 |          1 |           2 | 2:1       | 2006-06-01 | |       2 |          3 |           2 | 2:2       | 2006-07-01 | |       3 |          1 |           3 | 0:2       | 2006-06-21 | +---------+------------+-------------+-------------+------------+ mysql> select  *from team; +--------+----------+ | teamId | teamName | +--------+----------+ |      1 | 拜仁     | |      2 | 热火     | |      3 | 湖人     | +--------+----------+ 步骤： a.  mysql> select t1.hostTeamID,t2.teamName from mch as t1 left join team as t2 on t1.hostTeamID=t2.teamid;   b.  mysql> select t1.hostTeamID,t2.teamName,t1.guestTeamID,t3.teamName from mch as t1 left join team as t2 on t1.hostTeamID=t2.teamid left join team as t3 on t1.guestTeamID=t3.teamid;   c.  mysql> select t1.hostTeamID,t2.teamName,t1.matchResult,t1.guestTeamID,t3.teamName matchTime from mch as t1 left join team as t2 on t1.hostTeamID=t2.teamid left join team as t3 on t1.guestTeamID=t3.teamid;   d.  mysql> select t1.hostTeamID,t2.teamName,t1.matchResult,t1.guestTeamID,t3.teamName,t1.matchTime from mch as t1 left join team as t2 on t1.hostTeamID=t2.teamid left join team as t3 on t1.guestTeamID=t3.teamid;   最后的结果： mysql> select t1.hostTeamID,t2.teamName,t1.matchResult,t1.guestTeamID,t3.teamName,t1.matchTime from mch as t1 left join team as t2 on t1.hostTeamID=t2.teamid left join team as t3 on t1.guestTeamID=t3.teamid where atchTime between '2006-06-01' and '2006-07-01'; +------------+----------+-------------+-------------+----------+------------+ | hostTeamID | teamName | matchResult | guestTeamID | teamName | matchTime  | +------------+----------+-------------+-------------+----------+------------+ |          1 | 拜仁     | 2:1         |           2 | 热火   | 2006-06-01 | |          3 | 湖人     | 2:2         |           2 | 热火   | 2006-07-01 | +------------+----------+-------------+-------------+----------+------------+

二、留言板 1.删除记录： delete from 数据表   保留了原来的索引关系  清空记录： truncate table 数据表名   将索引关系（编号）也给清空了 2.script代码： function redirect_url($message,$url){ $str = <<<STR <script type='text/javascript'> alert('$message'); window.location.href='$url'; </script> STR;       echo $str;} 3.串截取函数：  string mb_substr ( string str, int start [, int length [, string encoding]] )  string substr_replace (string string, string replacement, int start [, int length])  string substr (string string, int start [, int length]) 编码： utf8编码下：中文占 3个字节，英文和数字占 1个字节（可变编码）（变长编码） gbk编码下：所有的中文、英文、数字都占2个字节（定长编码） 自定义函数：   怎么判断是字母还是数字呢？ 通常根据一个字符的第一个字节，如果这个字节 的ASCII码 大于224 占3个字节，如果第一个字节的ASCII码，大于192 占2个字节，如果第一个字节的ASCII码小于192，占1个字节 具体代码： function getpartstr($str,$len){  $index=0;  $result=''; for($i=1;$i<=10;$i++){ $first_b=substr($str,$index,1);//截取字符 if(ord($first_b)>224){ $result.=substr($str,$index,3); $index+=3; }elseif(ord($first_b)>192){     $result.=substr($str,$index,2); $index+=2; }elseif(ord($first_b)<192){     $result.=substr($str,$index,1); $index+=1; } } return $result;  }  echo getpartstr('314235sd你zdfd23irhjid',10);   三、事务 存储引擎： MyISAM   ----->  不支持事务，但是查询的速度快（id----主键----） InnoDB ------->    没有索引，查询的时候 速度就慢了 ，支持事务（数据比较安全） 关闭自动提交机制：set autocommit = 0; 开启自动提交机制：set autocommit=1; 存储引擎：engine innoDB 开启事务：start transaction; sql语句都成功：commit; 有一条不成功：rollback; use phone; create table bank( name char(3) not null default '', money decimal(10,2) not null default 0.00 )engine InnoDB  default charset utf8; insert into bank values('戴茂飞',1000),('吕国老',100); update bank set money=money-100 where name='戴茂飞'; update bank set money=money+100 where name='吕国老'; 四、触发器 1.查看触发器：触发器保存到 information_schema 数据库的  TRIGGERS这个表上 new : 新的记录（新插入的（购买的），更新订单之后的） old ：旧的记录（删除的记录，更新之前的） 触发器的时间:after /  before drop trigger alter_goods_num; 2.创建触发器： CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW  trigger_stmt  create trigger 触发器的名字   触发器的时间   触发器监听的事件  ON 数据表名  for each row 事件发生之后执行的代 码 增加： create trigger trigger_goods_num after insert on  `order` for each row update store set  goods_number=goods_number-new.order_num where goods_id=new.goods_id; 删除： create trigger goods_num after delete on `order` for each row update store set goods_number=goods_number +old.order_num where goods_id=old.goods_id;   delete from `order` where order_id=3; 更新:(同一商品，不同数量) create trigger goods_num after update on `order` for each row update store set goods_number=goods_number +old.order_num-new.order_num where goods_id=old.goods_id/new.goods_id;   更新：（不同商品，不同数量） delimiter $$ begin create trigger update_num after update on `order` for each row  update store set goods_number=goods_number+old.order_num where goods_id=old.order_id; update store set goods_number=goods_number-new.order_num where goods_id=new.order_id; end delimiter ;   五、索引 语法： create [UNIQUE|FULLTEXT]  index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..); create [索引类型]  index  索引名   on   表名（列的名字）   索引类型:unique、fulltext、不选为普通索引   create index index_name on goods (goods_name);   show index from goods ;   drop index index_name on goods;