MySQL第五天---存储过程、查询区分大小写、事务(MySQL及Java实现的简单模板)

 

※存储过程

存储过程是保存在数据库上的一段可执行代码。
1、定义存储过程的语法是：
Create procedure sp_name (参数..)
Begin
   SQL语句
End;

2、调用它的方法:
Call sp_name(参数…);   //空参时则括号中不填内容

 

3、演示
1)不带参数
定义(delimiter用于定义结束符):

DELIMITER $$CREATE PROCEDURE p1()BEGIN INSERT INTO person2 VALUES('P05','李四',0); SELECT * FROM person2;END$$DELIMITER ;调用:call p1();

 

 

2)带输入参数

<span style="font-size:14px;">DELIMITER $$CREATE PROCEDURE p2(in id varchar(32), in nm varchar(30) )BEGIN INSERT INTO person2 VALUES(id,nm,0); SELECT * FROM person2;END$$DELIMITER ;DROP PROCEDURE p1;//删除call p2('P06','张飞');CALL p2('P07','刘备');</span>

 

 

具有返回值的存储过程：

DROP PROCEDURE IF EXISTS a;

DELIMITER $$

CREATE PROCEDURE a(IN id INT,IN NAMEVARCHAR(20),OUT cont INT)

BEGIN

  INSERTINTO stud(id,NAME) VALUES(id,NAME);

  SELECTCOUNT(*) INTOcont FROM stud;

END$$

DELIMITER ;

//调用存储过程写入一行记录，同时返回目前一共有多少行数据

使用一个@符声明的是用户变量。

CALL a(9,'Jack',@cont);

SELECT  @cont;

//在Mysql中声明一个自定义的变量需要使用前缀@.

 

3) 带输入、输出参数
定义:

DELIMITER $$CREATE PROCEDURE p3(in id varchar(32), in nm varchar(30), out num int )BEGIN INSERT INTO person2 VALUES(id,nm,0); SELECT count(*) into num FROM person2;END$$DELIMITER ;调用:CALL p3('P08','关羽',@aa); //一个@为用户变量，两个@为系统变量输出返回值(输出参数)SELECT @aa;

 

 

//输出系统变量“@@version”的值
SELECT @@VERSION

 

※查询内容区分大小写

mysql查询默认是不区分大小写的 如：

select * from table_name where a like 'a%' select * from table_name where a like 'A%' select * from table_name where a like 'a%'select * from table_name where a like 'A%'

效果是一样的。

要让mysql查询区分大小写，可以：
select * from table_name where binary a like 'a%'  
select * from table_name where binary a like 'A%'   
select * from table_name where binary a like 'a%'
select * from table_name where binary a like 'A%'

 

区分了大小写以后name为a,select name from table_name where binary a like 'A%'就查不出来了

 

也可以在建表时，加以标识 
create table table_name(
 a varchar(20) binary
);

 

 

事务transaction: ：

 

原子性(atomicity)：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。

一致性(consistency)：在事务处理执行前后，数据库是一致的(数据库数据完整性约束)。

隔离性(isolcation)：一个事务处理对另一个事务处理的影响。

持续性(durability)：事务处理的效果能够被永久保存下来 。

一个事务只会有一个结果：要么成功、要么失败。

 

Start transaction;开始一个事务。

Commit;提交所做的修改。

Rollback;回滚所做的修改。如果在操作时出错，应该从新开始一个事务。

 

 

※事务: 一个事务只会有一个结果，要么成功，要么失败。
举例:
P09,'赵子龙' 一次买了两辆车，需要把赵子龙的信息存储到person2表，同时还要把两辆车的信息存储到car表-----两个表的存储动作就是一个事务，要么成功(两个表的信息都成功存储), 要么失败(只要其中一个表的一条数据存储失败，其它表的信息都不能存储进去，如果存进去则要还原)。
类似的例子，还有：银行转账！   销售单和销售明细!

SQL演示:

START TRANSACTION; //开启事务 DELETE FROM person2 WHERE id='P04'; DELETE FROM person2 WHERE id='P05';ROLLBACK/COMMIT; //回滚/提交START TRANSACTION; //开启事务 INSERT INTO person2 VALUES('P09','赵子龙','1'); INSERT INTO car VALUES('C007','BMW',50,'P09'); INSERT INTO car VALUES('C008','QQ',7,'P09');ROLLBACK/COMMIT; //回滚/提交DELETE FROM car WHERE id='C007';DELETE FROM car WHERE id='C008';DELETE FROM person2 WHERE id='P09';

 

注：只要执行了开始事务的代码，只要不commit，之后的操作都可以回滚

执行事务之前的数据库：

删除id为'P04','P05'的人，以后查表

然后rollback进行事务回滚，又会回到之前的数据库

但要注意的事，一个事务没有rollback前不要再开始一个新事务，否则就会默认commit，无法回滚了。

 

※事务的隔离

１、在MySql中，可以通过select @@tx_isolation来查询它目前默认的隔离级别。

　　查询的结果为：REPEATABLE-READ，即４，可重复读的隔离级别。

２、可以通过以下语法修改mysql数据库的隔离级别：

settransaction isolation level read uncommitted; --设置读未提交的级别  //版本5.5以上则在set后要加“session”

　　它的具体语法如下：

SETTRANSACTION ISOLATION LEVEL

  { READ UNCOMMITTED | READ COMMITTED |REPEATABLE READ | SERIALIZABLE }

３、在客户端通过starttransaction直接打开一个事务。

4、注意：当通过start transaction开启一个事务，并执行了rollback或commit之后，隔离级别都将会

　　重新设置成之前的默认级别，在mysql中为：repeatable read;

5、要通过代码来演示事务的隔离级别，必须使用两个不同的Connection对像。

 

※Java实现事务处理的简单模板

<span style="font-size:14px;">package cn.hncu;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;public class TxDemo {public static void main(String[] args) throws Exception {Class.forName("com.mysql.jdbc.Driver");//驱动String url = "jdbc:mysql://127.0.0.1:3306/hncu?useUnicode=true&characterEncoding=UTF-8";Connection con = DriverManager.getConnection(url, "root", "1234");Statement st = con.createStatement();//创建语句对象---StateMent/* * ※Java实现事务处理的简单模板 */try {con.setAutoCommit(false);//SQL: START TRANSACTION; //开启事务 st.execute("INSERT INTO person2 VALUES('P09','赵子龙','1'    st.execute("INSERT INTO person2 VALUES('P10','赵铁柱','1')");    //st.execute("INSERT INTO person2 VALUES('P09','王二牛','1')"); //用于测事务回滚 System.out.println("成功提交一个事务....");con.commit(); //SQL: COMMIT; //提交} catch (Exception e) {System.out.println("出错了，进行事务回滚");con.rollback(); //SQL: ROLLBACK; //回滚 }//查询String sql = "select * from person2";ResultSet rs = st.executeQuery(sql);while(rs.next()){//表示有没有移到数据行,有则返回trueString id= rs.getString("id");//字段名String name = rs.getString("name");System.out.println(id+","+name);}}}</span>

虽然前面添加到数据库成功，但是后面失败了，就要全部回滚

 

全部成功提交以后，添加到数据库中保存