mysq复习

心已赠人 2022-09-30 12:56 100阅读 0赞

# （一）**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 ;

mysq复习

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MYSQ数据类型选择原则。

相关 mysq联表查询

相关 mysq复习

相关 mysq数据库web远程管理

相关 mysq截取字符串的办法

相关 Mysq 数据库（1）

相关 Mysq 年，月，日

相关 mysq服务器配置文件说明

相关 mysq慢查询sql分析

相关 MySq（十六）经典案例