mysq复习

（一）mysql复习

1.mysql函数

mysql函数

------------------

1.函数和存储过程相似，只是多了返回值声明.

2.创建函数

mysql>create function sf_add(a int ,b int) returns int

begin

return a + b ;

end

//

 

3.显式创建的函数

mysql>show function status --

mysql>show function status like '%add%' --

mysql>select sf_add(1,2) --

4.java调用函数

@Test

public void testFunction() throws Exception {

long start = System.currentTimeMillis();

//创建连接

String driverClass = "com.mysql.jdbc.Driver";

String url = "jdbc:mysql://localhost:3306/big4";

String username = "root";

String password = "root";

Class.forName(driverClass);

Connection conn = DriverManager.getConnection(url, username, password);

//关闭自动提交

conn.setAutoCommit(false);

 

//创建可调用语句，调用存储过程

CallableStatement cst = conn.prepareCall("{? = call sf_add(?,?)}");

cst.setInt(2,100);

cst.setInt(3,200);

cst.registerOutParameter(1,Types.INTEGER);

//注册输出参数类型

cst.execute();

System.out.println(cst.getInt(1));

conn.commit();

conn.close();

System.out.println(System.currentTimeMillis() - start);

}

2.事物并发执行出现的现象和隔离级别

一．事务的并发执行，容易出现的几个现象

-------------------------

1.脏读

读未提交,一个事务读取了另外一个事务改写还没有提交的数据，如果另外一个

事务在稍后的时候回滚。

2.不可重复读

一个事务进行相同条件查询连续的两次或者两次以上，每次结果都不同。

有其他事务做了update操作。

 

3.幻读

和(2)很像，其他事务做了insert操作.

二．隔离级别

了避免出现哪种并发现象的。

1 //read uncommitted ,读未提交

2 //read committed ,读已提交

4 //repeatable read ,可以重复读

8 //serializable ,串行化(悲观锁).演示mysql事务隔离级别

------------------------

1.开启mysql客户端

mysql>

2.关闭自动提交

mysql>set autocommit 0 ;

3.每次操作数据,都要开启事务，提交事务。

 

脏读现象

----------------

[A]

1)mysql>start transaction ; -- 开始事务

2)msyql>update users set age = age + 1 where id = 1 ; -- 更新数据,没有提交

6)mysql>rollback ; -- 回滚

7)mysql>select * from users ;

 

[B]

3)mysql>set session transaction isolation level read uncommitted ; -- 读未提交

4)msyql>start transaction ; -- 开始事务

5)mysql>select * from users ; -- 13

 

避免脏读

----------------

[A]

1)mysql>start transaction ; -- 开始事务

2)msyql>update users set age = age + 1 where id = 1 ; -- 更新数据,没有提交

6)mysql>rollback ; -- 回滚

7)mysql>select * from users ;

 

[B]

3)mysql>set session transaction isolation level read committed ; -- 读已提交

4)msyql>start transaction ; -- 开始事务

5)mysql>select * from users ; -- 13

 

 

测试不可重复读(隔离级别设置为读已提交不能避免不可重复读。)

------------------

[A]

1)mysql>commit ;

2)mysql>set session transaction isolation level read committed ; -- 读已提交

3)mysql>start transaction ; -- 开始事务

4)mysql>select * from users ; -- 查询

9)mysql>select * from users ;

 

[B]

5)mysql>commit;

6)mysql>start transaction ;

7)mysql>update users set age = 15 where id = 1 ; -- 更新

8)mysql>commit;

 

测试避免不可重复读(隔离级别设置为读已提交不能避免不可重复读。)

------------------

[A]

1)mysql>commit ;

2)mysql>set session transaction isolation level repeatable read ; -- 可以重复读

3)mysql>start transaction ; -- 开始事务

4)mysql>select * from users ; -- 查询

9)mysql>select * from users ;

 

[B]

5)mysql>commit;

6)mysql>start transaction ;

7)mysql>update users set age = 15 where id = 1 ; -- 更新

8)mysql>commit;

 

 

 

测试幻读(隔离级别设置为repeatable)

------------------

[A]

1)mysql>commit ;

2)mysql>set session transaction isolation level serializable; -- 串行化

3)mysql>start transaction ; -- 开始事务

4)mysql>select * from users ; -- 查询

9)mysql>select * from users ;

 

[B]

5)mysql>commit;

6)mysql>start transaction ;

7)mysql>insert into users(name,age) values('tomas',13); -- 更新

8)mysql>commit;

 

 

ANSI SQL

---------------------

美国国家标准结构SQL组

select * from users for update ;

 

MySQL

----------------------

1.支持四种隔离级别。

2.默认隔离级别是可以重复读。

3.隔离级别是seriable,不支持并发写。

 

 

表级锁

-------------------------

LOCK TABLE t WRITE; -- 加锁(表级锁,read)

UNLOCK TABLES ; -- 解除自己所有的所有表级锁

 

 

编程实现脏读现象

--------------------

package com.it18zhang.jdbcdemo.test;

import org.junit.Test;

import java.sql.*;

/**

 * 测试隔离级别

 */

public class TestIsolationLevel {

 

/**

 * 执行写，不提交

 */

@Test

public void testA() throws  Exception{

//创建连接

String driverClass = "com.mysql.jdbc.Driver";

String url = "jdbc:mysql://localhost:3306/big4";

String username = "root";

String password = "root";

Class.forName(driverClass);

Connection conn = DriverManager.getConnection(url, username, password);

conn.setAutoCommit(false);

Statement st = conn.createStatement();

st.execute("update users set age = 80 where id = 1");

 

System.out.println("===============");

conn.commit();

conn.close();

}

 

/**

 * 查询，查到别人没有提交的数据

 */

@Test

public void testB() throws  Exception{

//创建连接

String driverClass = "com.mysql.jdbc.Driver";

String url = "jdbc:mysql://localhost:3306/big4";

String username = "root";

String password = "root";

Class.forName(driverClass);

Connection conn = DriverManager.getConnection(url, username, password);

 

//设置隔离级别读未提交==>导致脏读

/************************** 设置隔离级别 ***************************************/

conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

conn.setAutoCommit(false);

Statement st = conn.createStatement();

 

 

ResultSet rs = st.executeQuery("select age from users where id = 1");

rs.next();

int age = rs.getInt(1) ;

System.out.println(age);

System.out.println("===============");

conn.commit();

conn.close();

}

 

共享读锁

-------------

独占写锁

-------------

一个事务写操作，另一个塞住。

 

SQL

--------------------

//

insert into users(name,age,...) values('',12,..) ; -- insert

update users set name = 'xxx',age = xxx ,... where id = xxx ; -- update

delete from users where id = xxx -- delete

 

-- 投影查询 projection.

select id,name from users where ... order by limit xxx --select

 

-- 查询时直接上独占写锁

select * from users for update ;

 

连接查询

---------------------

1.准备表[mysql.sql]

drop table if exists customers; -- 删除表

drop table if exists orders ; -- 删除表

create table customers(id int primary key auto_increment , name varchar(20) , age int); --创建customers表

create table orders(id int primary key auto_increment , orderno varchar(20) , price float , cid int); --创建orders表

-- 插入数据

insert into customers(name,age) values('tom',12);

insert into customers(name,age) values('tomas',13);

insert into customers(name,age) values('tomasLee',14);

insert into customers(name,age) values('tomason',15);

 

-- 插入订单数据

insert into orders(orderno,price,cid) values('No001',12.25,1);

insert into orders(orderno,price,cid) values('No002',12.30,1);

insert into orders(orderno,price,cid) values('No003',12.25,2);

insert into orders(orderno,price,cid) values('No004',12.25,2);

insert into orders(orderno,price,cid) values('No005',12.25,2);

insert into orders(orderno,price,cid) values('No006',12.25,3);

insert into orders(orderno,price,cid) values('No007',12.25,3);

insert into orders(orderno,price,cid) values('No008',12.25,3);

insert into orders(orderno,price,cid) values('No009',12.25,3);

insert into orders(orderno,price,cid) values('No0010',12.25,NULL);

 

 

2.查询

mysql>-- 笛卡尔积查询,无连接条件查询

mysql>select a.*,b.* from customers a , orders b ;

mysql>-- 内连接,查询符合条件的记录.

mysql>select a.*,b.* from customers a , orders b where a.id = b.cid ;

 

mysql>-- 左外连接,查询符合条件的记录.

mysql>select a.*,b.* from customers a left outer join orders b on a.id = b.cid ;

 

mysql>-- 右外连接,查询符合条件的记录.

mysql>select a.*,b.* from customers a right outer join orders b on a.id = b.cid ;

 

mysql>-- 全外连接,查询符合条件的记录.

mysql>select a.*,b.* from customers a full outer join orders b on a.id = b.cid ;

 

 

2.查询--分组

 字段列表  表       条件        分组        组内条件       排序     分页

mysql>select ... from ... where ... group by ... having ...  order by ... limit ..

 

 

mysql>-- 去重查询

mysql>select distinct price,cid from orders ;

 

mysql>-- 条件查询

mysql>select price,cid from orders where price > 12.27 ;

 

mysql>-- 聚集查询

mysql>select max(price) from orders ;

mysql>select min(price) from orders ;

mysql>select avg(price) from orders ;

mysql>select sum(price) from orders ;

mysql>select count(id) from orders ;

 

mysql>-- 分组查询

mysql>select max(price) from orders where cid is not null group by cid ;

 

mysql>-- 分组查询(组内过滤)

mysql>select cid ,orderno,max(price) as max_price,min(price) from orders where cid is not null group by cid having max_price > 20 ;

 

mysql>-- 降序查询

mysql>select cid ,orderno,max(price) as max_price,min(price) from orders where cid is not null group by cid having max_price > 20 order by max_price desc;

 

mysql>-- 模糊查询

mysql>select  * from customers where name like 'toma%'

mysql>select  * from customers where name not like 'toma%'

 

mysql>-- 范围查询

mysql>select  * from customers where id in (1,2,3)

mysql>select  * from customers where id not in (1,2,3)

 

mysql>-- between 1 and 10,闭区间

mysql>select  * from customers where id between 1 and 3 ;

mysql>select  * from customers where id >= 1 and id <= 3 ;

 

mysql>-- 嵌套子查询(查询没有订单的客户)

mysql>select  * from customers where id not in (select distinct cid from orders where cid is not null);

 

mysql>-- 嵌套子查询(查询订单数量>2的客户)

mysql>select * from customers where id in (select cid from orders group by cid having count(cid) > 2);

mysql>select * from customers where id in ( select t.cid from (select cid,count(*) as c from orders group by cid having c > 2) as t);

 

mysql>-- 嵌套子查询(查询客户id,客户name,订单数量,最贵订单，最便宜订单，平均订单价格where 订单数量>2的客户)

mysql>select a.id,a.name,b.c,b.max,b.min,b.avg

  from customers a,((select cid,count(cid) c , max(price) max ,min(price) min,avg(price) avg from orders group by cid having c > 2) as b)

  where a.id = b.cid ;