存储过程-触发器-事务-mysql体系架构-创建用户-找回root密码
本节所讲内容:
•      存储过程  
•      触发器
•      事务
•      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   
# (记录超过的时间,默认为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
  该文件中存储的是创建数据库时默认的字符集和字符集校验规则,则该数据库在以后创建表时如果没有指定字符集和校验规则,则该表的这两个属性将去自这两个表。
五、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了。
2、MyISAM只支持表级锁, InnoDB支持行级锁和表级锁默认为行级锁

表级锁:直接锁定整张表,在锁定期间,其他进程无法对该表进行写操作,如果设置的是写锁,那么其他进程读也不允许
MyISAM是表级锁定的存储引擎,它不会出现死锁问题
对于write操作,表锁定原理如下:
如果表上没有锁,在其上面放置一个写锁,否则,把锁定请求放在写锁队列中。
当一个锁定被释放时,表可被写锁定队列中的线程得到,然后才是读锁定队列中的线程。这意味着,如果你在一个表上有许多更新,那么你的SELECT语句将等到所有的写锁定线程执行完。
例:写操作时,加锁就像单人卫生间。  表级锁。


行级锁:只对指定的行进行锁定,其他进程还是可以对表中的其他行进行操作的。

行级锁是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索引。
这时。彼此锁定着对方需要的索引,又都在等待对方释放锁定。所以出现了"死锁"的情况。
  


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

本文 暂无 评论

回复给

Top