ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql初识(二)

mysql初识(二)

原创 Linux操作系统 作者:to_be_dba 时间:2013-06-30 12:24:12 0 删除 编辑

八、存储过程

mysql在5.0版本后提供了存储过程和函数。

语法与oracle不同在于:
1)指定参数是输入、输出、输入输出的参数(in、out、 in out)放在参数名前面。
2)以begin开始,以end结束语句块。声明在begin之后。
3)用delimiter关键字来改变语句结束的标志。(默认是分号,若不修改的话,会将存储过

程当做单条语句处理,并报错)
4)可以进行dml操作


如:
mysql> delimiter //
mysql> create procedure proc_name(in cc integer)
    -> begin
    -> declare v varchar(20);
    -> if cc=1 then
    -> set v='mysql';
    -> else
    -> set v='php';
    -> end if;
    -> insert into tb(name) values(v);
    -> end;
    -> //
Query OK, 0 rows affected (0.03 sec)

执行结束后需要将语句结束符改回成分号:
mysql> delimiter ;

存储过程的调用方式为:call sp_name(parameter……);
函数的调用方式为: select function_name(……);


进行创建表操作的例子:
mysql> create procedure p_test()
    -> begin
    ->   create table tt(t_name varchar(2));
    -> end;
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> call p_test();
    -> //
Query OK, 0 rows affected (0.14 sec)

mysql> show tables;
    -> //
+----------------+
| Tables_in_test |
+----------------+
| t              |
| tb             |
| tt             |
+----------------+
3 rows in set (0.00 sec)

 

创建函数:--注意是returns,不是return
mysql> create function f_name(t_n int)
    -> returns varchar(20)
    -> begin
    -> return(select t_name from tt where t_name=t_n);
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)


存储过程中的变量:
mysql存储过程中声明的变量分为局部变量和全局变量两种。
局部变量作用域只在其声明所在的程序块或子块中。
全局变量又叫做会话变量,在存储过程中有效。以@作为起始符号。
定义变量的语句是declare variable_name1,variable_name2…… type default xxxx;
不设置默认值时默认是NULL。

为变量赋值
方法1:set variable_name=xxxx,variable_name2=xxxx……;
方法2:select xxx into variable_name from table_name ……;
两种方式都可以同时为多个变量赋值。

mysql中游标的使用方法和oracle显式游标使用方法类似,但声明不太一样。

create procedure p_test2(out var_out varchar)
begin
declare c_a cursor for select t_name from tt;
declare var_a varchar(20) default 'NO_RECORD';
open c_a;
fetch c_a into var_a;
set var_out=var_a;
close c_a;
end;
//
以上语句执行报错,不太明白。

mysql> create procedure p_test2(out var_out varchar)
    -> begin
    -> declare c_a cursor for select t_name from tt;
    -> declare var_a varchar(20) default 'NO_RECORD';
    -> open c_a;
    -> fetch c_a into var_a;
    -> set var_out=var_a;
    -> close c_a;
    -> end;
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')
begin
declare c_a cursor for select t_name from tt;
declare var_a varchar(20) ' at line 1


mysql的存储过程中,流程控制语句除if、case、loop、while外,还有iterate和leave。

if语句语法为:
if xxx then xxxx
elseif xxx then xxx
else xxx
end if;

case语法:
case  xxx
when xxx then xxx
when xxx then xxx
【else xxx】
end case;

while语法:
while xxxxxxx do
……
end while;


loop语法:
loop
……
end loop;
可以用“leave 循环标签名”退出循环


repeat:
先执行一次循环体,再判断condition:
repeat
……
until condition
end repeat:


iterate表示“再次循环”。与leave功能相反。

 

查看存储过程和函数的状态:
show {procedure|function} status [like 'xxxxx'];
查看存储过程和函数的定义:
show create {procedure|function} sp_name;


修改存储过程和函数:
alter {procedure|function} sp_name xxxxx;
删除存储过程和函数:
drop {procedure|function}[if exists] sp_name;

捕获错误


九、触发器
语法:
1)只包含一条语句
create trigger xxxx before|after [insert|update|delete]
on xxx for each row
……;
2)包含多条语句
create trigger xxxx before|after [insert|update|delete]
on xxx for each row
begin
……
end
;

查看:
show triggers;
select * from information_schema.triggers;
删除:
drop trigger xxxx;

触发器不能包含事务处理语句或call语句


十、事务
mysql有innodb和bdb两种类型的数据表支持事务。要创建事务,必须是这两类表中的一种。
因此首先需要创建或修改,以便得到Innodb或BDB表。
创建:create table xxx(……) type=INNODB/BDB;
修改:alter table xxx type innodb/BDB;


创建事务的一般过程为初始化事务、创建事务、查询数据是否被录入和提交事务。
1)初始化事务:
start transaction;
2)创建事务:
就是执行事务中的操作(insert、update、delete等)
3)查看
用select查看第二步的操作结果
4)提交事务
commit
5)撤销事务
rollback


mysql默认是事务自动提交的,也就是我们每输入一条DML语句后,自动执行commit语句,提

交当前事务。若需要手动提交,可以设置set autocommit=0;
查看该参数的方法为:
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

事务分为序列化、可重复读取、提交后读取、未提交读取四个隔离级别。
mysql默认是可重复读取,即repeatable read。
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


为了数据的一致性,可以将表锁住:
mysql> lock table t read;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t;
+------+------+
| id   | code |
+------+------+
|    1 | ACBD |
|    2 | ABCD |
|    3 | BACD |
|    4 | BADC |
+------+------+
4 rows in set (0.00 sec)

mysql> delete from t where code='ACBD';
ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t where code='ACBD';
Query OK, 1 row affected (0.13 sec)

这是一种mysql的伪事务。 设置lock的类型为read后,用户只能读数据,不能进行其他操作

;若设置lock类型为write,则当前用户可以修改数据,而其他会话中的用户不能进行任何

读操作。


十一、mysql系统管理

工具:
mysqld_safe  启动、监控、重启mysqld服务器程序的工具
mysqladmin   关闭服务器或服务器不正常时检查运行状态等
mysqlcheck、isamchk、myisamchk  对数据表进行分析、优化
mysqldump、mysqlhotcopy  备份或复制工具

数据目录的位置:
mysql> show variables like 'datadir';
+---------------+---------------------------------------------------------------
--------------------+
| Variable_name | Value
                    |
+---------------+---------------------------------------------------------------
--------------------+
| datadir       | D:\Documents and Settings\All Users\Application Data\MySQL\MyS
QL Server 5.5\Data\ |
+---------------+---------------------------------------------------------------
--------------------+
1 row in set (0.00 sec)

也可以用mysqladmin的命令:
mysqladmin variables

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


mysql也有数据字典,可以通过information_schema数据库下的tables表查看:
mysql> select table_schema,table_name from tables;//
+--------------------+----------------------------------------------+
| table_schema       | table_name                                   |
+--------------------+----------------------------------------------+
| information_schema | CHARACTER_SETS                               |
| information_schema | COLLATIONS                                   |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY        |
| information_schema | COLUMNS                                      |
| information_schema | COLUMN_PRIVILEGES                            |
| information_schema | ENGINES                                      |
| information_schema | EVENTS                                       |
| information_schema | FILES                                        |
| information_schema | GLOBAL_STATUS                                |
| information_schema | GLOBAL_VARIABLES                             |
| information_schema | KEY_COLUMN_USAGE                             |
| information_schema | PARAMETERS                                   |
| information_schema | PARTITIONS                                   |
| information_schema | PLUGINS                                      |
| information_schema | PROCESSLIST                                  |
| information_schema | PROFILING                                    |
| information_schema | REFERENTIAL_CONSTRAINTS                      |
| information_schema | ROUTINES                                     |
| information_schema | SCHEMATA                                     |
| information_schema | SCHEMA_PRIVILEGES                            |
| information_schema | SESSION_STATUS                               |
| information_schema | SESSION_VARIABLES                            |
| information_schema | STATISTICS                                   |
| information_schema | TABLES                                       |
| information_schema | TABLESPACES                                  |
| information_schema | TABLE_CONSTRAINTS                            |
| information_schema | TABLE_PRIVILEGES                             |
| information_schema | TRIGGERS                                     |
| information_schema | USER_PRIVILEGES                              |
| information_schema | VIEWS                                        |
| information_schema | INNODB_BUFFER_PAGE                           |
| information_schema | INNODB_TRX                                   |
| information_schema | INNODB_BUFFER_POOL_STATS                     |
| information_schema | INNODB_LOCK_WAITS                            |
| information_schema | INNODB_CMPMEM                                |
| information_schema | INNODB_CMP                                   |
| information_schema | INNODB_LOCKS                                 |
| information_schema | INNODB_CMPMEM_RESET                          |
| information_schema | INNODB_CMP_RESET                             |
| information_schema | INNODB_BUFFER_PAGE_LRU                       |
| mysql              | columns_priv                                 |
| mysql              | db                                           |
| mysql              | event                                        |
| mysql              | func                                         |
| mysql              | general_log                                  |
| mysql              | help_category                                |
| mysql              | help_keyword                                 |
| mysql              | help_relation                                |
| mysql              | help_topic                                   |
| mysql              | host                                         |
| mysql              | ndb_binlog_index                             |
| mysql              | plugin                                       |
| mysql              | proc                                         |
| mysql              | procs_priv                                   |
| mysql              | proxies_priv                                 |
| mysql              | servers                                      |
| mysql              | slow_log                                     |
| mysql              | tables_priv                                  |
| mysql              | time_zone                                    |
| mysql              | time_zone_leap_second                        |
| mysql              | time_zone_name                               |
| mysql              | time_zone_transition                         |
| mysql              | time_zone_transition_type                    |
| mysql              | user                                         |
| performance_schema | cond_instances                               |
| performance_schema | events_waits_current                         |
| performance_schema | events_waits_history                         |
| performance_schema | events_waits_history_long                    |
| performance_schema | events_waits_summary_by_instance             |
| performance_schema | events_waits_summary_by_thread_by_event_name |
| performance_schema | events_waits_summary_global_by_event_name    |
| performance_schema | file_instances                               |
| performance_schema | file_summary_by_event_name                   |
| performance_schema | file_summary_by_instance                     |
| performance_schema | mutex_instances                              |
| performance_schema | performance_timers                           |
| performance_schema | rwlock_instances                             |
| performance_schema | setup_consumers                              |
| performance_schema | setup_instruments                            |
| performance_schema | setup_timers                                 |
| performance_schema | threads                                      |

从名称上初步理解为:
“information_schema”是所有数据库对象的属性信息
“mysql”数据库是用户、权限、时区、日志等基本信息
“performance_schema”存储性能相关的设置信息

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-765148/,如需转载,请注明出处,否则将追究法律责任。

下一篇: dbms_scheduler
请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    148
  • 访问量
    390724