本节所讲内容：
• 存储过程
• 触发器
• 事务
• mysql系统架构
• 常见引擎
• msyql 数据库用户和权限管理
• 修改帐户密码：
• 重置root密码：
一、存储过程
存储过程（StoredProcedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
定义存储过程
procedure [prəˈsi:dʒə(r)] 程序
create procedure 过程名（参数1，参数2....）
begin
sql语句：
end
调用存储过程：
call 过程名（参数1，参数2）;
默认SQL语句执行符号“; ”
使用delimiter可以修改执行符号 。
语法：
delimiter 新执行符号
例：修改；为//
mysql> delimiter //
注：因为默认执行完sql语句遇到；后，就结束了。
delimiter 分隔符 [dɪ'lɪmɪtə]
1 例子：定一个存储过程，查看category表中所有数据
delimiter // # 更改结束符号的。
创建一个存储过程：
mysql>use book；
mysql> delimiter //
mysql> createprocedure selCg()
-> begin
-> select * from category;
-> end //
Query OK, 0 rowsaffected (0.10 sec)
调用存储过程
mysql> callselCg()//
+---------+---------------+
| bTypeId |bTypeName |
+---------+---------------+
| 1 | windows应用 |
| 2 | 网站 |
| 3 | 3D动画 |
| 4 | linux学习 |
| 5 | Delphi学习 |
| 6 | 黑客 |
| 7 | 网络技术 |
| 8 | 安全 |
| 9 | 平面 |
| 10 | AutoCAD技术 |
+---------+---------------+
10 rows in set (0.15sec)
总结：一次编译，永久执行。
2、存储过程参数传递：
in 传入参数 把参数传递到过程内部。
out 传出参数
inout 传入传出参数
into 赋值
例：定义存储过程getOneBook，当输入某书籍id后，可以调出对应书籍记录
//in---传入参数 -----------------------------------------
mysql> createprocedure getOneBook(in b int)
-> begin
-> select * from books where bId=b;
-> end //
Query OK, 0 rowsaffected (0.01 sec)
mysql> callgetOneBook(3);//
+-----+-----------------------------+---------+-----------------------------+-------+------------+--------+-----------+
|bId | bName |bTypeId | publishing |price | pubDate | author | ISBN |
+-----+-----------------------------+---------+-----------------------------+-------+------------+--------+-----------+
| 3 | 网络程序与设计－asp | 2 | 北方交通大学出版社 | 43 | 2005-02-01 | 王玥 | 75053815x |
+-----+-----------------------------+---------+-----------------------------+-------+------------+--------+-----------+
1row in set (0.00 sec)
例：存储过程中 out --传出参数
into 在select 语句中表示给变量赋值。
call demo(@a); # 使用@a来接受out传出的值。 mysql中的变量表示方法： @变量名
mysql> createprocedure demo(out pa varchar(200))
begin
select bName into pa from books where bId=3;
end//
调用，执行：
mysql> calldemo(@a); //
查看变量@a中的值：
mysql> select @a//
+-----------------------------+
| @a |
+-----------------------------+
| 网络程序与设计－asp |
+-----------------------------+
1 row in set (0.00 sec)
查看存储过程：
mysql> show create procedure demo\G
*************************** 1. row***************************
Procedure: demo
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`(outpa varchar(200))
begin
select bName into pa from books wherebId=3;
end
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
mysql>create procedure getCount(out param int)
-> begin
-> select count(*) into param frombooks;
-> end//
callgetCount(@test);
showcreate procedure 过程 查看存储过程创建信息的。
set @变量名 = 值。
select @变量名称。
3 过程内的变量的使用方法：
declare 变量名称 类型 ==》过程内的变量没有@ （必须放在过程的begin下边）
declare [dɪˈkleə(r)]声明
申明：declare变量名称 类型 ==》过程内的变量没有@
赋值：set变量名 = (select 语句);
例：
mysql> createprocedure demo1()
-> begin
-> declare str varchar(200);
-> set str = (select bName from books where bId=12);
-> select str;
-> end//
mysql> calldemo1()//
+-------------------------------+
| str |
+-------------------------------+
| Fireworks 4网页图形制作 |
+-------------------------------+
1 row in set (0.06 sec)
二、触发器
1 与数据表有关，当表出现变化的时候（增、删、改），自动执行其他的特定的操作。
触发器的格式：
trigger [ˈtrɪgə(r)] 触发
语法：create trigger 触发器名称 触发的时机 触发的动作 on 表名 for each row 触发器状态。
触发器名称：phptrigger可以自己随意起
触发的时机：before /after 在执行动作之前还是之后
触发的动作:指的激发触发程序的语句类型
insert
update
delete
例： 当category表中，删除一个bTypeid=3的图书分类时，books表中也要删除对应分类的图书信息
mysql> create trigger delCategory after delete oncategory for each row
->delete from books where bTypeid='3';
-> //
在category执行删除前，查看bTypeId=3的图书分类：
mysql> selectbName,bTypeId from books where bTypeId=3;
+--------------------------------------+---------+
| bName | bTypeId |
+--------------------------------------+---------+
| 3D MAX 3.0 创作效果百例 |3 |
| 3DS MAX 4横空出世 | 3 |
| 3D MAX R3动画制作与培训教程 | 3 |
| 3D Studio Max 3综合使用 | 3 |
+--------------------------------------+---------+
删除bTypeId=3的记录
mysql> delete fromcategory where bTypeId=3;
查看：是否还有bTypeId=3的图书记录。可以看出已经删除。
mysql> selectbName,bTypeId from books where bTypeId=3;
Empty set (0.00 sec)
例2：删除触发器
drop trigger 触发器名称;
例：
mysql> drop trigger delCategory;//
触发器是不是永久保留？
三、事务（sql 增删改查、索引、事务、锁（理论））
什么是事物？
数据库事务：（database transaction）: 事务是由一组SQL语句组成的逻辑处理单元。
transaction [trænˈzækʃn]交易
事务处理：可以确保非事务性单元的多个操作都能成功完成，否则不会更新数据资源。
优点：通过将一组操作组成一个，执行时，要么全部成功，要么全部失败的单元。
使程序更可靠，简化错误恢复。
逻辑单元的要求：
原子性（Atomicity） ：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
例：
• A汇款给B1000元
• A账户-1000
• B账户+1000
• 以上操作对应数据库为两个update。这两个操作属于一个事物。否则，可能会出现A账户钱少了，B账户钱没增加的情况。
语句：
STARTTRANSACTION | BEGIN [WORK] 开启事务
COMMIT[WORK] [AND [NO] CHAIN] [[NO] RELEASE] 提交当前事务，执行永久操作。
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO]RELEASE] 回滚当前事务到开始点，取消上一次开始点后的所有操作。
SETAUTOCOMMIT = {0 | 1} 设置事务是否自动提交，默认是自动提交的。
0：禁止自动提交
1：开启自动提交。
SAVEPOINT 名称 设置起始点的。
例： 创建一个事务，只有把bId=1和bId=2两条记录的bName都改成功后，才算修改成功。
set autocommit=0;
mysql> starttransaction;
-> update books set bName="cccccc" WHERE bId=1;
-> update books setbName="dddddd" WHERE bId=2;
->commit;//
测试，查看是否完成修改：
mysql> select bNamefrom books where bId=1 or bId=2;//
+--------+
| bName |
+--------+
| cccccc |
| dddddd |
+--------+
数据回滚：
保存点标识符 identifier [aɪˈdentɪfaɪə(r)] 标识符
SAVEPOINT identifier
ROLLBACK [WORK] TOSAVEPOINT identifier
例：
start transaction
语句块
savepoint php1107;
语句
if @a<=1000000 then
rollback php1107
end if;
commit;
mysql相关的配置文件：
1、主配置文件：
[root@xuegod63 aa]# ls /etc/my.cnf
2、进程通讯sock文件：
[root@xuegod63 ~]# ll/var/lib/mysql/mysql.sock
srwxrwxrwx 1 mysql mysql 0 Mar 27 17:48/var/lib/mysql/mysql.sock
3、日志文件
错误日志文件
[mysqld_safe]
log-error=/var/log/mysqld.log
4、进程ID文件
pid-file=/var/run/mysqld/mysqld.pid
[root@xuegod63 ~]# cat/var/run/mysqld/mysqld.pid
37333
5、二进制日志文件： （二进制文件可以在读写分离时使用）
在mysql配置文件my.cnf中增加
vim /etc/my.cnf
log-bin=mysql-bin.log
6、慢查询日志文件：
在mysql配置文件my.cnf中增加
vim /etc/my.cnf
log-slow-queries=/var/lib/mysql/slowquery.log
#(指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log) ，mysql用户要对这个/var/lib/mysql/slowquery.log文件有可写的权限
long_query_time=2
long_query_time=2
# (记录超过的时间，默认为10s)
7、数据文件：
[root@xuegod63 ~]# ls /var/lib/mysql/book/
books.frm books.MYI category.MYD db.opt t.MYD
books.MYD category.frm category.MYI t.frm t.MYI
.frm meta data 存储表定义
.MYD 存储数据文件
.MYI 存储索引文件
例：
[root@xuegod63 ~]# ls/var/lib/mysql/a12306/uPwd_12306.* -lh
-rw-rw---- 1 mysql mysql 8.6K Jul 4 21:14 /var/lib/mysql/a12306/uPwd_12306.frm
-rw-rw---- 1 mysql mysql 14M Jul 4 21:15/var/lib/mysql/a12306/uPwd_12306.MYD
-rw-rw---- 1 mysql mysql 1.3M Jul 4 22:31 /var/lib/mysql/a12306/uPwd_12306.MYI
8. db.opt作用：MySQL的每个数据库目录中有一个文件db.opt,该文件主要用来存储当前数据库的默认字符集和字符校验规则。
eg.default-character-set=latin1
default-collation=latin1_swedish_ci
default-collation=latin1_swedish_ci
该文件中存储的是创建数据库时默认的字符集和字符集校验规则，则该数据库在以后创建表时如果没有指定字符集和校验规则，则该表的这两个属性将去自这两个表。
五、MySQL数据库系统的逻辑架构：服务层、核心层和存储引擎层
file:///C:/Users/Administrator/AppData/Local/Temp/msohtmlclip1/01/clip_image002.jpg
客户端
• MySQL客户端并不具体指某个客户端软件。
• 事实上MySQL客户端是一种复合概念。包含：
• 不同程序语言编写的前端应用程序
• 所调用的API接口
服务层
file:///C:/Users/Administrator/AppData/Local/Temp/msohtmlclip1/01/clip_image004.jpg
核心层
file:///C:/Users/Administrator/AppData/Local/Temp/msohtmlclip1/01/clip_image006.jpg
存储引擎层
file:///C:/Users/Administrator/AppData/Local/Temp/msohtmlclip1/01/clip_image008.jpg
主从同步强烈推荐replication、、、比利用logbin优秀太多、、手工同步也方便、、
file:///C:/Users/Administrator/AppData/Local/Temp/msohtmlclip1/01/clip_image010.jpg
Java语言有：JDBC（JavaData Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。
PHP语言有：PDO扩展为PHP访问数据库定义了一个轻量级的、一致性的接口，它提供了一个数据访问抽象层，这样，无论使用什么数据库，都可以通过一致的函数执行查询和获取数据。
file:///C:/Users/Administrator/AppData/Local/Temp/msohtmlclip1/01/clip_image012.jpg
查看当前myql服务器支持的引擎：
mysql> show engines;
file:///C:/Users/Administrator/AppData/Local/Temp/msohtmlclip1/01/clip_image014.jpg
常见存储引擎介绍：
myisam :
特性： 1、不支持事务。宕机时会破坏表 2.使用较小的内存和磁盘空间 3.基于表的锁。表级锁
4、 mysql 只缓存index， 数据由OS缓存。
典型应运：
1、 日志系统。
2、 大部分都是读是的操作。 门户网站，企业站点 www.xuegod.cn
3、 没有事务 ，低并发。
InnoDB：
file:///C:/Users/Administrator/AppData/Local/Temp/msohtmlclip1/01/clip_image016.jpg
例：修改默认引擎为innodb
[root@xuegod63 mysql]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
default-storage-engine=innodb
[root@xuegod63 mysql]# service mysqldrestart
创建表测试：
mysql> create table ca (id int,name char(8));
Query OK, 0 rows affected (0.06 sec)
mysql> show create table ca \G
*************************** 1. row***************************
Table: ca
Create Table: CREATE TABLE `ca` (
`id` int(11) DEFAULT NULL,
`name` char(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
innodb适用于：
MyISAM与InnoDB两者之间区别：
1、MyISAM不支持事务，InnoDB是事务类型的存储引擎
当我们的表需要用到事务支持的时候，那肯定是不能选择MyISAM了。
当我们的表需要用到事务支持的时候，那肯定是不能选择MyISAM了。
2、MyISAM只支持表级锁， InnoDB支持行级锁和表级锁默认为行级锁
表级锁：直接锁定整张表，在锁定期间，其他进程无法对该表进行写操作，如果设置的是写锁，那么其他进程读也不允许
MyISAM是表级锁定的存储引擎，它不会出现死锁问题
对于write操作，表锁定原理如下：
如果表上没有锁，在其上面放置一个写锁，否则，把锁定请求放在写锁队列中。
当一个锁定被释放时，表可被写锁定队列中的线程得到，然后才是读锁定队列中的线程。这意味着，如果你在一个表上有许多更新，那么你的SELECT语句将等到所有的写锁定线程执行完。
对于write操作，表锁定原理如下：
如果表上没有锁，在其上面放置一个写锁，否则，把锁定请求放在写锁队列中。
当一个锁定被释放时，表可被写锁定队列中的线程得到，然后才是读锁定队列中的线程。这意味着，如果你在一个表上有许多更新，那么你的SELECT语句将等到所有的写锁定线程执行完。
例：写操作时，加锁就像单人卫生间。 表级锁。
行级锁：只对指定的行进行锁定，其他进程还是可以对表中的其他行进行操作的。
行级锁是Mysql粒度最小的一种锁，它能大大的减少数据库操作的冲突，但是粒度越小实现成本也越大。
例：行级锁就像，公共厕所，可以多人同时使用。
行级锁是Mysql粒度最小的一种锁，它能大大的减少数据库操作的冲突，但是粒度越小实现成本也越大。
例：行级锁就像，公共厕所，可以多人同时使用。
行级锁可能会导致“死锁”：
分析原因：
1、Mysql行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，那么Mysql就会锁定这个主键索引； 如果sql语句操作的是非主键索引，那么Mysql会先锁定这个非主键索引，再去锁定主键索引。
2、在UPDATE 和 DELETE操作时Mysql不仅会锁定所有WHERE 条件扫描过得索引，还会锁定相邻的键值（被修改的字段）。
“死锁”举例分析：
例1：表Test字段结构：（ID,STATE,TIME） 主键索引：ID 非主键索引：STATE
STATE： 状态
当执行"UPDATE STATE =1011 WHERE STATE=1000" 语句的时候会锁定STATE索引，由于STATE 是非主键索引,所以Mysql还会去请求锁定ID索引
当另一个SQL语句与语句1几乎同时执行时：“UPDATE STATE=1010 WHERE ID=1” 对于语句2Mysql会先锁定ID索引，由于语句2操作了STATE字段，所以Mysql还会请求锁定STATE索引。
2、在UPDATE 和 DELETE操作时Mysql不仅会锁定所有WHERE 条件扫描过得索引，还会锁定相邻的键值（被修改的字段）。
“死锁”举例分析：
例1：表Test字段结构：（ID,STATE,TIME） 主键索引：ID 非主键索引：STATE
STATE： 状态
当执行"UPDATE STATE =1011 WHERE STATE=1000" 语句的时候会锁定STATE索引，由于STATE 是非主键索引,所以Mysql还会去请求锁定ID索引
当另一个SQL语句与语句1几乎同时执行时：“UPDATE STATE=1010 WHERE ID=1” 对于语句2Mysql会先锁定ID索引，由于语句2操作了STATE字段，所以Mysql还会请求锁定STATE索引。
这时。彼此锁定着对方需要的索引，又都在等待对方释放锁定。所以出现了"死锁"的情况。
3、MyISAM引擎不支持外键，InnoDB支持外键
4、 InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
5、MyISAM支持全文索引（FULLTEXT），InnoDB不支持
6、MyISAM引擎的表的查询、更新、插入的效率要比InnoDB高
memory 存储引擎：
mysql> CREATE TABLE t (i INT) ENGINE = MEMORY;
查看：
[root@xuegod63 mysql]# ll -h aa/tt.frm
-rw-rw---- 1 mysql mysql 8.4K Nov 27 18:50aa/tt.frm
常用工具：
mysql
mysqlcheck/myisamchk
mysqlhotcopy
mysqldump
mysqladmin
本文 暂无 评论