mysql事务与索引

mysql之事务

什么是事务
数据库事务通常指对数据库进行读或写的一个操作过程。有两个目的，第一个是为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法；第二个是当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。
事务的特性(ACID)
1.原子性(Atomicity)：事务必须是原子工作单元，一个事务中的所有语句，应该做到：要么全做，要么一个都不做；
2.一致性(Consistency):让数据保持逻辑上的“合理性”，比如：小明给小红3.打10000块钱，既要让小明的账户减少10000，又要让小红的账户上增加10000块钱；
4.隔离性(Isolation)：如果多个事务同时并发执行，但每个事务就像各自独立执行一样。
持久性(Durability)：一个事务执行成功，则对数据来说应该是一个明确的硬盘数据更改（而不仅仅是内存中的变化）。
注意：使用事务，表的引擎要为innodb引擎。
事务实战
创建一个账户表模拟转账

copy
1
2
3
4
5
6
create table account (
                         id tinyint(5) zerofill auto_increment  not null comment 'id编号',
                         name varchar(20) default null comment '客户姓名',
                         money decimal(10,2) not null comment '账户金额',
                         primary key (id)
                         )engine=innodb charset=utf8;

copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
开启autocommit（临时生效）：

OFF（0）：表示关闭 ON （1）：表示开启

  mysql> set autocommit=0;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> show variables like 'autocommit';
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | autocommit    | OFF   |
  +---------------+-------+
  mysql> set autocommit=1;
  Query OK, 0 rows affected (0.00 sec)
  mysql> 
  mysql> show variables like 'autocommit';
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | autocommit    | ON    

开启autocommit（永久生效）：
修改配置文件：vi /etc/my.cnf 在[mysqld]下面加上：autocommit=1 记得重启服务才会生效

通过表中金额的增加减少，实验事务的开启与关闭结果。

mysql之视图

什么是视图？视图的作用是什么？
视图（view）是一种虚拟存在的表，是一个逻辑表，它本身是不包含数据的。作为一个select语句保存在数据字典中的。
通过视图，可以展现基表（用来创建视图的表叫做基表base table）的部分数据，说白了视图的数据就是来自于基表
视图的优点
1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响;源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
　　
4）不占用空间：视图是逻辑上的表，不占用内存空间
总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。
创建的基本语法是：

copy
1
2
3
4
5
6
7
    create view <视图名称> as select 语句;
    create view <视图名称> (字段) as select 语句;
    create or replace view <视图名称>;
修改的语法是：
alter view <视图名称> as select 语句;
视图删除语法：
drop view <视图名称> ;

mysql之触发器与存储过程

什么是触发器？
触发器就是监视某种情况，并触发某种操作

copy
1
2
3
4
5
6
7
8
9
10
11
12
create trigger 触发器名称  after/before   insert/update/delete on 表名  
        for each row
        begin
        sql语句;
        end
 /**           
after/before:可以设置为事件发生前或后
insert/update/delete:它们可以在执行insert、update或delete的过程中触发
for each row:每隔一行执行一次动作
*/
//删除触发器的语法：
drop trigger 触发器名称;

什么是存储过程？
存储过程就是把复杂的一系列操作，封装成一个过程。类似于shell，python脚本等。

copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
创建存储过程语法：
create procedure 名称 (参数....)
        begin
         过程体;
         过程体;
         end
/**
参数：in|out|inout 参数名称 类型（长度）
        in：表示调用者向过程传入值（传入值可以是字面量或变量）
        out：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
        inout：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
声明变量：declare 变量名 类型(长度) default 默认值;
给变量赋值：set @变量名=值;
调用存储命令：call 名称(@变量名);
删除存储过程命令：drop procedure 名称;
查看创建的存储过程命令：
*/
show create procedure 名称\G;
创建一个简单的存储过程：
    mysql> delimiter //
    mysql> create procedure  name(in n int)
        ->             begin
        ->             select * from employee limit n;
        ->             end
        -> //
    Query OK, 0 rows affected (0.00 sec)

    mysql> set @n=5;
        -> //
    Query OK, 0 rows affected (0.00 sec)

    mysql> 
    mysql> call name(@n);
  mysql>         create procedure  name()
        ->             begin
        ->             declare  n int default 6;
        ->             select * from employee limit n;
        ->             end
        -> //
    Query OK, 0 rows affected (0.00 sec)

    mysql> call name

mysql之索引与存储引擎

MyISAM与InnoDB引擎
（1）什么是数据库存储引擎？
数据库引擎是数据库底层软件组件，不同的存储引擎提供不同的存储机制，索引技巧，锁定水平等功能，使用不同的数据库引擎，可以获得特定的功能
(2）修改表的引擎

copy
1
2
3
4
5
6
alter table 表名 engine='MyiSAm';
修改默认引擎
•    vi /etc/my.cnf
•    [mysqld]下面
•    default-storage-engine=MyIsAM
•    记得保存后重启服务

（3）MyISAM与InnoDB的区别

MyISAM：支持全文索引（full text）;不支持事务;表级锁;保存表的具体行数;奔溃恢复不好
Innodb：支持事务;以前的版本是不支持全文索引，但在5.6之后的版本就开始支持这个功能了;行级锁（并非绝对，当执行sql语句时不能确定范围时，也会进行锁全表例如： update table set id=3 where name like 'a%';）;不保存表的具体行数;奔溃恢复好。推荐使用InnoDB引擎。
什么是索引？
索引是一个单独的，存储在磁盘中上的数据库结构，它们包含着对数据表里的所有记录的引用指针。使用索引可以快速的找出在某列或多列中有特定值的行。
- 索引的优点：
  通过创建唯一索引，来保证数据库表中的每一行数据的唯一性。
  • 可以加快数据的检索速度。
  • 可以保证表数据的完整性与准确性
- 索引的缺点：
  索引需要占用物理空间。
  • 对表中的数据进行改动时，索引也需要跟着动态维护，降低了数据的维护速度。
索引的常见类型：
• index：普通索引
• unique：唯一索引
• primary key：主键索引
• foreign key：外键索引
• fulltext: 全文索引
• 组合索引

copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
//直接为表添加索引
语法：
     alter table 表名 add index 索引名称 (字段名称);
 eg: 
     alter table test add unique unique_username (username);
//注意：假如没有指定索引名称时，会以默认的字段名为索引名称
直接创建索引
语法：create index 索引 on 表名 (字段名);
eg：create index index_createtime on test (createtime);
//删除索引
语法：drop index 索引名称 on 表名;
eg：drop index unique_username on test;
语法：alter table 表名 drop index 索引名;
eg：alter table test drop index createtime;