sc
service console,服务控制台程序,可以启动,停止,删除等服务
sc start MySQL
sc stop MySQL
sc delete MySQL //删除,从注册表删除服务记录
MySQL常用指令
mysql -h hostname -u root -p password //连接远程数据库
mysql -u root -p password //连接localhost
mysql --help //查看帮助
mysql>select current_date; //
mysql>select now(); //函数
mysql>-- 这是注释
mysql>show databases; //显示所有数据库
mysql>drop database mydatabase; //删除某个数据库
mysql>create database mydatabase; //创建数据库
mysql>use mydatabase; //指定使用数据库
mysql>show tables; //显示表
mysql>create table tablename testtable(id varchar(20), psw varchar(20));//创建表
mysql>describe testtable; 或 desc testtable; //查看表结构
mysql>drop table testtable; //删除表
mysql>select * from testtable; //全字段,全表扫描
mysql>select * from testtable where id > 3;
mysql>select * from testtable where id >3 and id <5;
mysql> select * from testtable where id = '1';
mysql> select * from testtable where psw like '1%'; //以1开头的,模糊查询
mysql> select * from testtable where psw not like '1%'; //不是以1开头的
mysql> select * from testtable where psw like '1_'; //以1开头的接着是任意一个字符
mysql> select * from testtable where psw like '1\_'; //以1开头的接着是任意一个_(转义字符查询特殊字面量)
mysql> select * from testtable order by id desc; //j降序查询
mysql> select * from testtable order by id asc;
mysql> select * from testtable order by age asc, id desc; //组合排序
mysql>select id, psw from testtable; //投影查询
mysql>select * from testtable where psw is null; //查询psw为null
mysql>select * from testtable where psw is not null; //查询psw非null
mysql>select count(*) from testtable where psw = "123456";//聚集函数查询
mysql>select max(age) from testtable;
mysql>select min(age) from testtable;
mysql>select avg(age) from testtable;
mysql>select sum(age) from testtable where id < 5;
mysql>select * from testtable limit 1, 3;
mysql>select * from testtable limit 0, 3; =====select * from testtable limit 0. 3;//分页查询
mysql>insert into testtable(id, psw) values('1', 'pass');//插入
mysql>insert into testtable(id) values('1');//插入部分字段
mysql>insert into testtable values('1', 'pass');//插入全部字段
mysql>update testtable set psw = '123456' where id = '1';//修改
mysql>update testtable set age = 20 where id in (5, 6); //in
CRUD
[create]
insert into table_name(field_name,...) values(value,...);
[retrieve]
selete id,... from table_name where id = xxx,...;
[update]
update table_name set id = xxx, ... where id = xxx, ...;
[delete]
delete from table_name where ...;
source d:/findtest.sql //执行脚本文件
MySQL约束
1.primary key (非空,不能重复)
create tabe testtable(id int primary key, ...);
2.自增
create tabe testtable(id int primary key auto_increment, ...);
3.带条件创建
create database if not exists testbase;
create table if not exists test;
drop database if exists;
JDBC
1. java database connection, socket
2. 规范,都是接口
3. 引入驱动程序
jar , 类库, 对SQL规范的实现
4. Connection
连接, Session == Socket
5. Statement
语句,InputStream
6. 使用JDBC的过程
a. 注册驱动程序
Class.forName("com.mysql.jdbc.Driver");
b. 获得连接
String url = "";
DriverManager.getConnection(url, user, pass);
c. 创建Statement语句
Statement st = cnn.cerateStatement();
d. 调用Statement执行sql语句
st.execute(sql); //insert delete update
ResultSet rs = st.executeQuery(sql); //select
e. 遍历ResultSet
while (rs.next()) {
// 从1开始
rs.getString(1);
rs.getInt("id");
}
//JDBCDemo.java
1 import java.sql.Connection; 2 import java.sql.DriverManager; 3 import java.sql.Statement; 4 5 public class JDBCDemo { 6 7 public static void main(String[] args) { 8 // 注册驱动程序 9 String url = "jdbc:mysql://localhost:3306/mytestbase"; 10 String user = "root"; 11 String password = "root"; 12 // 驱动器管理器 13 try { 14 Class.forName("com.mysql.jdbc.Driver"); 15 Connection connec = DriverManager.getConnection(url, user, password); 16 // 创建语句对象 17 Statement statement = connec.createStatement(); 18 String sql = "insert into testtable values('6', 'opii')"; 19 statement.execute(sql); 20 System.out.println("over"); 21 // 释放资源 22 statement.close(); 23 connec.close(); 24 } catch (Exception e) { 25 e.printStackTrace(); 26 } 27 } 28 29 }
//TestCRUD.java
1 import java.sql.Connection; 2 import java.sql.DriverManager; 3 import java.sql.Statement; 4 5 import org.junit.Before; 6 import org.junit.Test; 7 8 import com.mysql.jdbc.ResultSet; 9 10 /** 11 * 测试增删查改 12 * 13 */ 14 public class TestCRUD { 15 16 private Connection connec; 17 18 /** 19 * 先执行 20 */ 21 @Before 22 public void initConn() { 23 String url = "jdbc:mysql://localhost:3306/mytestbase"; 24 String user = "root"; 25 String password = "root"; 26 // 驱动器管理器 27 try { 28 // 注册驱动程序 29 Class.forName("com.mysql.jdbc.Driver"); 30 connec = DriverManager.getConnection(url, user, password); 31 } catch (Exception e) { 32 e.printStackTrace(); 33 } 34 } 35 /** 36 * insert 37 */ 38 @Test 39 public void insert() { 40 try { 41 // 创建语句对象 42 Statement statement = connec.createStatement(); 43 String sql = "insert into testtable values('7', 'opiill')"; 44 statement.execute(sql); 45 System.out.println("over"); 46 // 释放资源 47 statement.close(); 48 connec.close(); 49 } catch (Exception e) { 50 e.printStackTrace(); 51 } 52 } 53 54 /** 55 * update 56 */ 57 @Test 58 public void update() { 59 try { 60 // 创建语句对象 61 Statement statement = connec.createStatement(); 62 String sql = "update testtable set psw='newpass' where id = '7'"; 63 statement.execute(sql); 64 System.out.println("over"); 65 // 释放资源 66 statement.close(); 67 connec.close(); 68 } catch (Exception e) { 69 e.printStackTrace(); 70 } 71 } 72 73 /** 74 * delete 75 */ 76 @Test 77 public void delete() { 78 try { 79 // 创建语句对象 80 Statement statement = connec.createStatement(); 81 String sql = "delete from testtable where id = '7'"; 82 statement.execute(sql); 83 System.out.println("over"); 84 // 释放资源 85 statement.close(); 86 connec.close(); 87 } catch (Exception e) { 88 e.printStackTrace(); 89 } 90 } 91 92 /** 93 * query 94 */ 95 @Test 96 public void query() { 97 try { 98 // 创建语句对象 99 Statement statement = connec.createStatement(); 100 String sql = "select * from testtable"; 101 ResultSet rs = (ResultSet) statement.executeQuery(sql); 102 while (rs.next()) { 103 String id = rs.getString("id"); 104 String psw = rs.getString("psw"); 105 System.out.println(id + ":" + psw); 106 } 107 System.out.println("over"); 108 // 释放资源 109 rs.close(); 110 statement.close(); 111 connec.close(); 112 } catch (Exception e) { 113 e.printStackTrace(); 114 } 115 } 116 }
DAO
1. Data Access Object, 数据访问对象
//App.java
1 import com.hzg.jdbc.dao.PersonDao; 2 3 public class App { 4 5 @SuppressWarnings("unused") 6 public static void main(String[] args) { 7 PersonDao personDao = new PersonDao(); 8 /*Person p = new Person(); 9 p.setId(7); 10 p.setName("jerry"); 11 personDao.insert(p ); 12 */ 13 // 2 14 15 /*List<Person> all = personDao.findAll(); 16 for (Person p0: all) { 17 System.out.println(p0.getId() + "," + p0.getName()); 18 }*/ 19 20 /*Person p = new Person(); 21 p.setId(6); 22 p.setName("tomas"); 23 personDao.update(p);*/ 24 25 System.out.println("over"); 26 } 27 28 }
//Person.java
1 package com.hzg.jdbc.domain; 2 3 public class Person { 4 private int id; 5 private String name; 6 public int getId() { 7 return id; 8 } 9 public void setId(int id) { 10 this.id = id; 11 } 12 public String getName() { 13 return name; 14 } 15 public void setName(String name) { 16 this.name = name; 17 } 18 19 }
//JDBCUtil.java
1 package com.hzg.jdbc.util; 2 3 4 import java.sql.Connection; 5 import java.sql.DriverManager; 6 import java.sql.ResultSet; 7 import java.sql.Statement; 8 9 /** 10 * 工具类 11 * @author zhengguohuang 12 * 13 */ 14 public class JDBCUtil { 15 16 static { 17 // 注册驱动 18 try { 19 Class.forName("com.mysql.jdbc.Driver"); 20 } catch (ClassNotFoundException e) { 21 e.printStackTrace(); 22 } 23 } 24 /** 25 * 开启连接 26 * @return 27 */ 28 public static Connection connectDB() { 29 try { 30 // 获得连接 31 String url = "jdbc:mysql://localhost:3306/mytestbase"; 32 String user = "root"; 33 String pass = "root"; 34 return (Connection) DriverManager.getConnection(url, user, pass); 35 } catch (Exception e) { 36 e.printStackTrace(); 37 } 38 return null; 39 } 40 41 /** 42 * 关闭连接 43 * @param connection 44 */ 45 public static void closeConnection(Connection connection) { 46 try { 47 if (connection != null && !connection.isClosed()) 48 connection.close(); 49 } catch (Exception e) { 50 e.printStackTrace(); 51 } 52 } 53 54 /** 55 * 关闭语句 56 * @param st 57 */ 58 public static void closeStatement(Statement st) { 59 try { 60 if (st != null ) 61 st.close(); 62 } catch (Exception e) { 63 e.printStackTrace(); 64 } 65 } 66 67 /** 68 * 关闭ResultSet 69 * @param resultSet 70 */ 71 public static void closeResultSet(ResultSet resultSet) { 72 try { 73 if (resultSet != null ) 74 resultSet.close(); 75 } catch (Exception e) { 76 e.printStackTrace(); 77 } 78 } 79 80 81 }
//PersonDao.java
1 package com.hzg.jdbc.dao; 2 3 4 5 import java.sql.Connection; 6 import java.sql.ResultSet; 7 import java.util.ArrayList; 8 import java.util.List; 9 10 import com.hzg.jdbc.domain.Person; 11 import com.hzg.jdbc.util.JDBCUtil; 12 13 14 /** 15 * Person表的Dao类 16 * 17 * @author zhengguohuang 18 * 19 */ 20 public class PersonDao { 21 22 private Connection connection; 23 private java.sql.Statement st; 24 private ResultSet resultSet; 25 26 /** 27 * insert 28 * 29 * @param name 30 */ 31 public void insert(Person p) { 32 try { 33 34 connection = JDBCUtil.connectDB(); 35 st = connection.createStatement(); 36 String sql = "insert into persons(name) values('" + p.getName() + "')"; 37 st.execute(sql); 38 } catch (Exception e) { 39 e.printStackTrace(); 40 } finally { 41 JDBCUtil.closeConnection(connection); 42 JDBCUtil.closeStatement(st); 43 } 44 } 45 46 /** 47 * update 48 * 49 * @param name 50 */ 51 public void update(Person p) { 52 try { 53 connection = JDBCUtil.connectDB(); 54 st = connection.createStatement(); 55 String sql = "update persons set name = '" + p.getName() + "' where id = " + p.getId(); 56 st.execute(sql); 57 } catch (Exception e) { 58 e.printStackTrace(); 59 } finally { 60 JDBCUtil.closeConnection(connection); 61 JDBCUtil.closeStatement(st); 62 } 63 } 64 65 /** 66 * delete 67 * 68 * @param name 69 */ 70 public void deleteById(Integer id) { 71 try { 72 connection = JDBCUtil.connectDB(); 73 74 // 创建st对象 75 st = connection.createStatement(); 76 String sql = "delete from persons where id = " + id; 77 st.execute(sql); 78 } catch (Exception e) { 79 e.printStackTrace(); 80 } finally { 81 JDBCUtil.closeConnection(connection); 82 JDBCUtil.closeStatement(st); 83 } 84 } 85 86 /** 87 * query 88 * 89 * @param name 90 */ 91 public Person findById(Integer id) { 92 try { 93 connection = JDBCUtil.connectDB(); 94 95 // 创建st对象 96 st = connection.createStatement(); 97 String sql = "select * from person where id = " + id; 98 resultSet = st.executeQuery(sql); 99 100 while (resultSet.next()) { 101 Person p = new Person(); 102 p.setId(resultSet.getInt("id")); 103 p.setName(resultSet.getString("name")); 104 return p; 105 } 106 } catch (Exception e) { 107 e.printStackTrace(); 108 } finally { 109 JDBCUtil.closeResultSet(resultSet); 110 JDBCUtil.closeConnection(connection); 111 JDBCUtil.closeStatement(st); 112 } 113 return null; 114 } 115 116 /** 117 * query 118 * 119 * @param name 120 */ 121 public List<Person> findAll() { 122 try { 123 connection = JDBCUtil.connectDB(); 124 125 // 创建st对象 126 st = connection.createStatement(); 127 String sql = "select * from persons"; 128 resultSet = st.executeQuery(sql); 129 List<Person> persons = new ArrayList<Person>(); 130 Person p = null; 131 while (resultSet.next()) { 132 p = new Person(); 133 p.setId(resultSet.getInt("id")); 134 p.setName(resultSet.getString("name")); 135 persons.add(p); 136 137 } 138 return persons; 139 } catch (Exception e) { 140 e.printStackTrace(); 141 } finally { 142 JDBCUtil.closeResultSet(resultSet); 143 JDBCUtil.closeConnection(connection); 144 JDBCUtil.closeStatement(st); 145 } 146 return null; 147 } 148 }
SQL注入
String sql = "select * from persons where name = ' 1 ' or 1=1 -- ' and password = 'xxxxx' ";
1 /** 2 * 测试SQL注入 3 */ 4 @Test 5 public void testSQLInject1() { 6 try { 7 // 正常登录 8 /*String id = "2"; 9 String psw = "123";*/ 10 // 注入 11 12 String id = "1' or 1=1 -- "; 13 String psw = "123"; 14 Statement st = connec.createStatement(); 15 String sql = "select * from testtable where id = '" + id + "' and psw = '"+ psw+"'"; 16 ResultSet rs = st.executeQuery(sql); 17 if (rs.next()) { 18 String sid = rs.getString(1); 19 System.out.println("success"); 20 } else { 21 System.out.println("用户名/密码错误"); 22 } 23 24 // 释放资源 25 st.close(); 26 connec.close(); 27 } catch (Exception e) { 28 e.printStackTrace(); 29 } 30 }
1 /** 2 * 测试SQL注入 3 */ 4 @Test 5 public void testSQLInject2() { 6 try { 7 // 正常登录 8 /*String id = "2"; 9 String psw = "123";*/ 10 // 注入 11 12 String id = "'1' or 1=1 -- "; 13 String psw = "123"; 14 String sql = "select * from testtable where id = ? and psw = ?"; 15 PreparedStatement ppst = connec.prepareStatement(sql); 16 ppst.setString(1, id); 17 ppst.setString(2, psw); 18 ResultSet rs = ppst.executeQuery(); 19 if (rs.next()) { 20 String sid = rs.getString(1); 21 System.out.println("success"); 22 } else { 23 System.out.println("用户名/密码错误"); 24 } 25 26 // 释放资源 27 ppst.close(); 28 connec.close(); 29 } catch (Exception e) { 30 e.printStackTrace(); 31 } 32 }
用Statement插入10万条记录
1 /** 2 * insert use Statement 3 */ 4 @Test 5 public void insertInStatement() { 6 try { 7 // 创建语句对象 8 Statement st = connec.createStatement(); 9 String sql = null; 10 for (int i = 1; i <= 100000; i++) { 11 sql = "insert into testtable(id,psw) values('" + "" + i + "','"+ "" +(i % 50)+"')"; 12 st.execute(sql); 13 } 14 15 // 释放资源 16 st.close(); 17 connec.close(); 18 } catch (Exception e) { 19 e.printStackTrace(); 20 } 21 }
PreparedStateent
1 /** 2 * insert use PrepareStatement 3 */ 4 @Test 5 public void insertInPreparedStatement() { 6 try { 7 // 创建语句对象 8 9 String sql = "insert into testtable(id,psw) values(?,?)"; 10 PreparedStatement ppst = connec.prepareStatement(sql); 11 for (int i = 1; i <= 100000; i++) { 12 ppst.setString(1, i + ""); 13 ppst.setString(2, i % 50 + ""); 14 ppst.executeUpdate(); 15 } 16 17 // 释放资源 18 ppst.close(); 19 connec.close(); 20 } catch (Exception e) { 21 e.printStackTrace(); 22 } 23 }
事务
Transaction 一组不可分割的操作
事务的特点
ACID
Atomic:原子性,不可分割。整体
Consistent:一致性,数据不被破坏
Isolate:隔离性,事务之间独立,不能干扰
Durable:永久性,数据被永久保存起来
commit() 提交
rollback() 回滚
1 /** 2 * 测试commit 3 */ 4 @Test 5 public void test1() { 6 try { 7 // 创建语句对象 8 9 String sql = "insert into testtable(id,psw) values(?,?)"; 10 PreparedStatement ppst = connec.prepareStatement(sql); 11 connec.setAutoCommit(false); 12 ppst.setString(1, "4"); 13 ppst.setString(2, 50+""); 14 ppst.executeUpdate(); 15 // 手动提交 16 //connec.commit(); 17 // 释放资源 18 ppst.close(); 19 connec.close(); 20 } catch (Exception e) { 21 e.printStackTrace(); 22 } 23 }
1 /** 2 * 测试commit 3 */ 4 @Test 5 public void test1() { 6 try { 7 // 创建语句对象 8 9 String sql = "insert into testtable(id,psw) values(?,?)"; 10 PreparedStatement ppst = connec.prepareStatement(sql); 11 connec.setAutoCommit(false); 12 ppst.setString(1, "4"); 13 ppst.setString(2, 50+""); 14 ppst.executeUpdate(); 15 // 手动提交 16 connec.commit(); 17 // 释放资源 18 ppst.close(); 19 connec.close(); 20 } catch (Exception e) { 21 e.printStackTrace(); 22 } 23 }
1 /** 2 * insert use Statement 3 */ 4 @Test 5 public void insertInStatement() { 6 try { 7 // 创建语句对象 8 Statement st = connec.createStatement(); 9 String sql = null; 10 connec.setAutoCommit(false); 11 for (int i = 1; i <= 100000; i++) { 12 sql = "insert into testtable(id,psw) values('" + "" + i + "','" + "" + (i % 50) + "')"; 13 st.execute(sql); 14 } 15 connec.commit(); 16 // 释放资源 17 st.close(); 18 connec.close(); 19 } catch (Exception e) { 20 e.printStackTrace(); 21 } 22 } 23 24 /** 25 * insert use PrepareStatement 26 */ 27 @Test 28 public void insertInPreparedStatement() { 29 try { 30 // 创建语句对象 31 32 String sql = "insert into testtable(id,psw) values(?,?)"; 33 PreparedStatement ppst = connec.prepareStatement(sql); 34 connec.setAutoCommit(false); 35 for (int i = 1; i <= 100000; i++) { 36 ppst.setString(1, i + ""); 37 ppst.setString(2, i % 50 + ""); 38 ppst.executeUpdate(); 39 } 40 connec.commit(); 41 // 释放资源 42 ppst.close(); 43 connec.close(); 44 } catch (Exception e) { 45 e.printStackTrace(); 46 } 47 }
1 /** 2 * insert use PrepareStatement 3 */ 4 @Test 5 public void insertInPreparedStatement() { 6 try { 7 // 创建语句对象 8 9 String sql = "insert into testtable(id,psw) values(?,?)"; 10 PreparedStatement ppst = connec.prepareStatement(sql); 11 12 connec.setAutoCommit(false); 13 14 for (int i = 1; i <= 100000; i++) { 15 ppst.setString(1, i + ""); 16 ppst.setString(2, i % 50 + ""); 17 // 添加批量命令 18 ppst.addBatch(); 19 if (i % 4000 == 0) { 20 ppst.executeBatch(); 21 ppst.clearBatch(); 22 } 23 24 } 25 ppst.executeBatch(); 26 connec.commit(); 27 // 释放资源 28 ppst.close(); 29 connec.close(); 30 } catch (Exception e) { 31 e.printStackTrace(); 32 } 33 }
存储过程
store procedure 存放在数据库
1. 创建存储过程
mysql> delimiter //
mysql>create procedure simpleproc (OUT param1 INT)
BEGIN
select count(*) into param1 from t;
END
//
1 mysql> create procedure sp_count(out param int) 2 -> begin 3 -> select count(*) into param from testtable; 4 -> end 5 -> //
2. 在java中调用存储过程
1 /** 2 * query 3 */ 4 @Test 5 public void test1() { 6 try { 7 String sql = "{call sp_count(?)}"; 8 // 创建cst对象 9 CallableStatement cst = connec.prepareCall(sql); 10 // 注册输出参数 11 cst.registerOutParameter(1, Types.INTEGER); 12 // 执行存储过程 13 cst.execute(); 14 // 取得输出参数 15 int count = cst.getInt(1); 16 System.out.println(count); 17 } catch (Exception e) { 18 e.printStackTrace(); 19 } 20 }
3.两个整数加法的存储过程
mysql>delimiter //
mysql>create procedure sp_add(in a int, in b int, out c int)
mysql>begin
mysql> set c:=a+b;
mysql>end
mysql>//
create procedure sp_biginsert(in num int)
begin
declare i int default 0;
start transaction;
while i < num do
insert into testtable(id, psw) values (concat('tom', i), concat('', i));
set i = i + 1;
end while;
commit;
end
1 /** 2 * biginsert in sp 3 */ 4 @Test 5 public void test2() { 6 try { 7 String sql = "{call sp_biginsert(?)}"; 8 // 创建cst对象 9 CallableStatement cst = connec.prepareCall(sql); 10 cst.setInt(1, 100000); 11 // 执行存储过程 12 cst.execute(); 13 14 15 } catch (Exception e) { 16 e.printStackTrace(); 17 } 18 }
函数
create function f_hello() returns varchar(20)
return 'hello'//
调用:select function f_hello()
tinyint //1 byte
smallint //2 short
MEDIUMINT//3
int //4 int
bigint //8 long
char(20)
varchar(255)
blob //binary large object,二进制大对象
longblob
longtext //大文本
mysql大字段处理
1. 增加longblob字段
alter table persons add column photo longblob;
alter table persons add column memo longtext;
1 /** 2 * insert 3 */ 4 @Test 5 public void blob() { 6 try { 7 // 创建语句对象 8 String sql = "insert into persons (name,photo,memo) values(?,?,?)"; 9 PreparedStatement ppst = connec.prepareStatement(sql); 10 // 绑定参数 11 ppst.setString(1, "tom"); 12 FileInputStream fis = new FileInputStream("d:/2.png"); 13 ppst.setBinaryStream(2, fis, fis.available()); 14 ppst.setString(3, "hjhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); 15 ppst.executeUpdate(); 16 17 // 释放资源 18 ppst.close(); 19 connec.close(); 20 } catch (Exception e) { 21 e.printStackTrace(); 22 } 23 }
1 /** 2 * 读取blob 3 */ 4 @Test 5 public void findBlob() { 6 try { 7 // 创建语句对象 8 String sql = "select * from persons where id = ?"; 9 PreparedStatement ppst = connec.prepareStatement(sql); 10 // 绑定参数 11 ppst.setInt(1, 8); 12 ResultSet rs = ppst.executeQuery(); 13 if (rs.next()) { 14 String name = rs.getString("name"); 15 InputStream is = rs.getBinaryStream("photo"); 16 byte[] buffer = new byte[1024]; 17 int len = -1; 18 FileOutputStream fos = new FileOutputStream("d:/"+name+".png"); 19 while ((len = is.read(buffer)) != -1) { 20 fos.write(buffer, 0, len); 21 } 22 fos.close(); 23 is.close(); 24 } 25 26 // 释放资源 27 ppst.close(); 28 connec.close(); 29 } catch (Exception e) { 30 e.printStackTrace(); 31 } 32 }
在cli下关闭mysql的自动提交
mysql>set autocommit = 0;
mysql>start transaction; //开启事务
mysql>rollback; //回滚
mysql>commit; //提交
事务并发执行导致的3个现象
1. 脏读
读脏,dirty read
读未提交
一个事务读取了另一个事务改写还未提交的数据,如果另外一个事务在稍后时刻回滚,则该事务出现了脏读。
2. 不可重复读
一个事务在进行相同条件的查询,连续两次或两次以上每次结果不同
读不回去
3. 幻读
一个事务在进行相同条件的查询,连续两次或两次以上,在稍后的查询中会出现一些原来没有的记录
ANSI SQL隔离级别
1-read uncommitted,读未提交 --0001
2-read committed,读已提交 --0010(oracle 默认是2)
4-repeatable read,可重复读 --0100(mysql 默认是4)
8-Serializable,串行化 --1000
设置mysql隔离级别
SET[GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
脏读演示
1. 关闭A B的 autocommit
mysql>set autocommit=0;
2. A开启事务
mysql>start transaction; --开启事务
mysql>update persons set name = 'tomas' where id = 8; --更新
3. B设置隔离级别(read uncommitted)
mysql>set session transaction isolation level read uncommitted; --设置隔离级别
mysql>select * from persons where id = 8; -- 查询
4. A回滚
mysql>rollback;
mysql repetable read 可以避免幻读
Oracle不支持repetable read
查看mysql事务隔离级别
select @@global.tx_isolation; --全局的隔离级别
select @@session.tx_isolation; --当前会话的隔离级别
select @@tx_isolation; --当前会话
行级锁
表级锁
lock tables xxx write , yyy read; --上锁
unlock tables; --解锁
分组查询
mysql>create table customers(id int primary key auto_increatement, name varchar(20), int age, int salary )
mysql>select * from customers group by age;
mysql>select min(salary), max(salary), count(*) from customers group by age;
mysql> select max(salary), min(salary), age from customers group by age having age > 10; --用having进行过滤
连接查询
1. customers表, 插入数据
2. 订单表
create table orders (id int primary key auto_increment, orderno varchar(20), price float, cid int);
3. 添加一个外键约束
alter table orders add constraint foreign key (cid) references customers(id);
4. select a.*, b.* from customers a, orders b (where a.id = b.cid);
select a.*, b.* from customers a, orders b where a.id = b.cid order by a.id, b.id;(等值连接)
笛卡尔积
5. 使用join连接查询(内连接)
mysql>select a.id, a.name, b.id, b.orderno from customers a (inner) join orders b on a.id = b.cid;
6. 外连接
[可以补齐]
mysql>-- 有些customers没有orders,有些orders没有customers
mysql>-- 左外连接(向左看齐)
mysql>select a.id, a.name, b.id, b.orderno, b.price from customers a left outer join orders b on a.id = b.cid;
+----+----------+------+----------------+-------+
| id | name | id | orderno | price |
+----+----------+------+----------------+-------+
| 1 | tom | 1 | 201710121404kl | 4.5 |
| 3 | tomas | 3 | 201710121304kl | 4.5 |
| 4 | tomaslee | 4 | 201710121304dm | 4.5 |
| 5 | jerry | 5 | 201710121404dm | 4.5 |
| 2 | tom1 | NULL | NULL | NULL |
| 6 | cherry | NULL | NULL | NULL |
| 7 | alice | NULL | NULL | NULL |
| 8 | su | NULL | NULL | NULL |
| 9 | petter | NULL | NULL | NULL |
| 10 | hobe | NULL | NULL | NULL |
+----+----------+------+----------------+-------+
mysql> insert into orders (orderno, price, cid) values('201710122020ke', 20.8,NULL);
mysql>-- 右外连接(向右看齐)
mysql>select a.id, a.name, b.id, b.orderno, b.price from customers a left outer join orders b on a.id = b.cid;
+------+----------+----+----------------+-------+
| id | name | id | orderno | price |
+------+----------+----+----------------+-------+
| 4 | tomaslee | 1 | 201710121404kl | 4.5 |
| 10 | hobe | 3 | 201710121304kl | 4.5 |
| 1 | tom | 4 | 201710121304dm | 4.5 |
| 2 | tom1 | 5 | 201710121404dm | 4.5 |
| NULL | NULL | 6 | 201710122020ke | 20.8 |
+------+----------+----+----------------+-------+
mysql>-- 全外连接 (mysql不支持)
集合查询
包括union, union all , minus(差集A-B mysql不支持), intersect mysql不支持
union:联合查询
1. 纵向合成若干查询的结果
2. mysql>select id, name from customers union select id, orderno from orders;
mysql>select * from customers where id <= 3 union select * from customers where id >= 3;-- 自动过滤重复
mysql>select * from customers where id <= 3 union all select * from customers where id >= 3;-- 防止自动过滤重复
范围运算
1. and or
2. between ... and ...
mysql>-- 闭区间
mysql>select * from customers where id between 4 and 8;
3.in 等价于 or or or or
mysql>select * from orders where id in (1,2,3,4);
4.any集合中的任何一个
mysql> select * from orders where cid > any(select id from customers where name like 't%');
5. all集合中的全部元素
mysql> select * from orders where cid > all(select id from customers where name like 't%');
去除重复元素
1. distinct
mysql>select distinct age from customers;
mysql> select count(distinct age) as agecount from customers;
池化模式
使用有限的对象数量服务于大量的客户端请求
Datasource
1. 内部是连接池
2. java.sql.Datasource
Connection.getConnection();
Connection.close();
使用装饰模式实现数据源
// MyDataSource.java
1 package com.hzg.jdbc; 2 3 import java.io.PrintWriter; 4 import java.sql.Connection; 5 import java.sql.DriverManager; 6 import java.sql.SQLException; 7 import java.sql.SQLFeatureNotSupportedException; 8 import java.util.logging.Logger; 9 10 import javax.sql.DataSource; 11 /** 12 * 自定义数据源(连接池) 13 * @author zhengguohuang 14 * 15 */ 16 public class MyDataSource implements DataSource { 17 18 static { 19 try { 20 Class.forName("com.mysql.jdbc.Driver"); 21 } catch (Exception e) { 22 e.printStackTrace(); 23 } 24 } 25 private String url = "jdbc:mysql://localhost:3306/mytestbase"; 26 private String user = "root"; 27 private String pass = "123456"; 28 private static final int MAX = 3; 29 30 public MyDataSource() { 31 initPool(); 32 } 33 34 /** 35 * 初始化连接池 36 */ 37 private void initPool() { 38 try { 39 for (int i = 0; i < MAX ; i++) { 40 Connection conn = DriverManager.getConnection(url, user, pass); 41 pool.addConnection(new MyConnectionWrapper(conn, pool)); 42 } 43 } catch (Exception e) { 44 e.printStackTrace(); 45 } 46 } 47 48 private ConnectionPool pool = new ConnectionPool(); 49 50 @Override 51 public PrintWriter getLogWriter() throws SQLException { 52 return null; 53 } 54 55 @Override 56 public void setLogWriter(PrintWriter out) throws SQLException { 57 58 } 59 60 @Override 61 public void setLoginTimeout(int seconds) throws SQLException { 62 63 } 64 65 @Override 66 public int getLoginTimeout() throws SQLException { 67 return 0; 68 } 69 70 @Override 71 public Logger getParentLogger() throws SQLFeatureNotSupportedException { 72 return null; 73 } 74 75 @Override 76 public <T> T unwrap(Class<T> iface) throws SQLException { 77 return null; 78 } 79 80 @Override 81 public boolean isWrapperFor(Class<?> iface) throws SQLException { 82 return false; 83 } 84 85 /** 86 * 重点实现 87 */ 88 @Override 89 public Connection getConnection() throws SQLException { 90 return pool.getConnection(); 91 } 92 93 /** 94 * 重点实现 95 */ 96 @Override 97 public Connection getConnection(String username, String password) throws SQLException { 98 return pool.getConnection(); 99 } 100 101 }
// ConnectionPool.java
1 package com.hzg.jdbc; 2 3 import java.sql.Connection; 4 import java.util.LinkedList; 5 import java.util.List; 6 /** 7 * 连接池 8 * @author zhengguohuang 9 * 10 */ 11 public class ConnectionPool { 12 13 private List<Connection> pool = new LinkedList<Connection>(); 14 private static final int MAX = 3; 15 16 /** 17 * 从连接池中取连接 18 */ 19 public synchronized Connection getConnection() { 20 try { 21 while (pool.isEmpty()) { 22 this.wait(); 23 } 24 return pool.remove(0); 25 } catch (Exception e) { 26 e.printStackTrace(); 27 } 28 return null; 29 } 30 31 /** 32 * 放置连接 33 */ 34 public synchronized void addConnection(Connection conn) { 35 try { 36 while (pool.size() >= MAX ) { 37 this.wait(); 38 } 39 } catch (Exception e) { 40 e.printStackTrace(); 41 } 42 pool.add(conn); 43 this.notifyAll(); 44 } 45 }
// MyConnectionWrapper.java
1 package com.hzg.jdbc; 2 3 import java.sql.Array; 4 import java.sql.Blob; 5 import java.sql.CallableStatement; 6 import java.sql.Clob; 7 import java.sql.Connection; 8 import java.sql.DatabaseMetaData; 9 import java.sql.NClob; 10 import java.sql.PreparedStatement; 11 import java.sql.SQLClientInfoException; 12 import java.sql.SQLException; 13 import java.sql.SQLWarning; 14 import java.sql.SQLXML; 15 import java.sql.Savepoint; 16 import java.sql.Statement; 17 import java.sql.Struct; 18 import java.util.Map; 19 import java.util.Properties; 20 import java.util.concurrent.Executor; 21 22 /** 23 * 连接包装类 24 * @author zhengguohuang 25 * 26 */ 27 public class MyConnectionWrapper implements Connection { 28 29 private Connection conn; 30 31 private ConnectionPool pool; 32 public MyConnectionWrapper(Connection conn,ConnectionPool pool ) { 33 this.conn = conn; 34 this.pool = pool; 35 } 36 @Override 37 public <T> T unwrap(Class<T> iface) throws SQLException { 38 return null; 39 } 40 41 @Override 42 public boolean isWrapperFor(Class<?> iface) throws SQLException { 43 return false; 44 } 45 46 @Override 47 public Statement createStatement() throws SQLException { 48 return conn.createStatement(); 49 } 50 51 @Override 52 public PreparedStatement prepareStatement(String sql) throws SQLException { 53 return conn.prepareStatement(sql); 54 } 55 56 @Override 57 public CallableStatement prepareCall(String sql) throws SQLException { 58 return null; 59 } 60 61 @Override 62 public String nativeSQL(String sql) throws SQLException { 63 return null; 64 } 65 66 @Override 67 public void setAutoCommit(boolean autoCommit) throws SQLException { 68 69 } 70 71 @Override 72 public boolean getAutoCommit() throws SQLException { 73 return false; 74 } 75 76 @Override 77 public void commit() throws SQLException { 78 conn.commit(); 79 } 80 81 @Override 82 public void rollback() throws SQLException { 83 conn.rollback(); 84 } 85 86 /** 87 * 重写关闭方法 88 */ 89 @Override 90 public void close() throws SQLException { 91 // 把自己重新放到池子中 92 pool.addConnection(this); 93 } 94 95 @Override 96 public boolean isClosed() throws SQLException { 97 return false; 98 } 99 100 @Override 101 public DatabaseMetaData getMetaData() throws SQLException { 102 return null; 103 } 104 105 @Override 106 public void setReadOnly(boolean readOnly) throws SQLException { 107 108 } 109 110 @Override 111 public boolean isReadOnly() throws SQLException { 112 return false; 113 } 114 115 @Override 116 public void setCatalog(String catalog) throws SQLException { 117 118 } 119 120 @Override 121 public String getCatalog() throws SQLException { 122 return null; 123 } 124 125 @Override 126 public void setTransactionIsolation(int level) throws SQLException { 127 128 } 129 130 @Override 131 public int getTransactionIsolation() throws SQLException { 132 return 0; 133 } 134 135 @Override 136 public SQLWarning getWarnings() throws SQLException { 137 return null; 138 } 139 140 @Override 141 public void clearWarnings() throws SQLException { 142 143 } 144 145 @Override 146 public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException { 147 return conn.createStatement(resultSetType, resultSetConcurrency); 148 } 149 150 @Override 151 public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) 152 throws SQLException { 153 return conn.prepareStatement(sql, resultSetType, resultSetConcurrency); 154 } 155 156 @Override 157 public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { 158 return null; 159 } 160 161 @Override 162 public Map<String, Class<?>> getTypeMap() throws SQLException { 163 return null; 164 } 165 166 @Override 167 public void setTypeMap(Map<String, Class<?>> map) throws SQLException { 168 169 } 170 171 @Override 172 public void setHoldability(int holdability) throws SQLException { 173 174 } 175 176 @Override 177 public int getHoldability() throws SQLException { 178 return 0; 179 } 180 181 @Override 182 public Savepoint setSavepoint() throws SQLException { 183 return null; 184 } 185 186 @Override 187 public Savepoint setSavepoint(String name) throws SQLException { 188 return null; 189 } 190 191 @Override 192 public void rollback(Savepoint savepoint) throws SQLException { 193 194 } 195 196 @Override 197 public void releaseSavepoint(Savepoint savepoint) throws SQLException { 198 199 } 200 201 @Override 202 public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) 203 throws SQLException { 204 return null; 205 } 206 207 @Override 208 public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, 209 int resultSetHoldability) throws SQLException { 210 return null; 211 } 212 213 @Override 214 public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, 215 int resultSetHoldability) throws SQLException { 216 return null; 217 } 218 219 @Override 220 public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException { 221 return null; 222 } 223 224 @Override 225 public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException { 226 return null; 227 } 228 229 @Override 230 public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException { 231 return null; 232 } 233 234 @Override 235 public Clob createClob() throws SQLException { 236 return null; 237 } 238 239 @Override 240 public Blob createBlob() throws SQLException { 241 return null; 242 } 243 244 @Override 245 public NClob createNClob() throws SQLException { 246 return null; 247 } 248 249 @Override 250 public SQLXML createSQLXML() throws SQLException { 251 return null; 252 } 253 254 @Override 255 public boolean isValid(int timeout) throws SQLException { 256 return false; 257 } 258 259 @Override 260 public void setClientInfo(String name, String value) throws SQLClientInfoException { 261 262 } 263 264 @Override 265 public void setClientInfo(Properties properties) throws SQLClientInfoException { 266 267 } 268 269 @Override 270 public String getClientInfo(String name) throws SQLException { 271 return null; 272 } 273 274 @Override 275 public Properties getClientInfo() throws SQLException { 276 return null; 277 } 278 279 @Override 280 public Array createArrayOf(String typeName, Object[] elements) throws SQLException { 281 return null; 282 } 283 284 @Override 285 public Struct createStruct(String typeName, Object[] attributes) throws SQLException { 286 return null; 287 } 288 289 @Override 290 public void setSchema(String schema) throws SQLException { 291 292 } 293 294 @Override 295 public String getSchema() throws SQLException { 296 return null; 297 } 298 299 @Override 300 public void abort(Executor executor) throws SQLException { 301 302 } 303 304 @Override 305 public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException { 306 307 } 308 309 @Override 310 public int getNetworkTimeout() throws SQLException { 311 return 0; 312 } 313 314 }
// App2.java
1 package com.hzg.jdbc; 2 3 import java.sql.Connection; 4 import java.sql.ResultSet; 5 import java.sql.Statement; 6 7 public class App2 { 8 9 public static void main(String[] args) { 10 try { 11 MyDataSource ds = new MyDataSource(); 12 Connection conn = ds.getConnection(); 13 Statement st = conn.createStatement(); 14 ResultSet rs = st.executeQuery("select * from customers"); 15 while (rs.next()) { 16 System.out.println(rs.getString("name")); 17 } 18 rs.close(); 19 st.close(); 20 conn.close(); 21 System.out.println("over"); 22 } catch (Exception e) { 23 e.printStackTrace(); 24 } finally { 25 26 } 27 28 } 29 30 }
使用第三方数据源(c3p0)
1. 下载jar包
2.
1 package com.hzg.jdbc.test; 2 3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 6 import org.junit.Test; 7 8 import com.mchange.v2.c3p0.ComboPooledDataSource; 9 10 public class TestC3p0 { 11 12 public static void main(String[] args) throws Exception{ 13 insert(); 14 } 15 16 17 public static void insert() throws Exception { 18 System.out.println("over"); 19 ComboPooledDataSource ds = new ComboPooledDataSource(); 20 ds.setDriverClass("com.mysql.jdbc.Driver"); 21 ds.setJdbcUrl("jdbc:mysql://localhost:3306/mytestbase"); 22 ds.setUser("root"); 23 ds.setPassword("root"); 24 25 Connection conn = ds.getConnection(); 26 /*PreparedStatement ppst = conn.prepareStatement("insert into customers(name, age) values('alice2', 12)"); 27 ppst.executeUpdate(); 28 29 ppst.close(); 30 conn.close();*/ 31 System.out.println(conn+"slf;dkfj;sl"); 32 System.out.println("over"); 33 } 34 }
控制台输出:
十月 14, 2017 5:27:33 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
十月 14, 2017 5:27:33 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1-pre6 [built 23-March-2006 16:11:59 +0200; debug? true; trace: 10]
十月 14, 2017 5:27:33 下午 com.mchange.v2.c3p0.PoolBackedDataSource getPoolManager
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource@3941a79c[ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, allUsers -> [], autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 312b1dae, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 3941a79c, idleConnectionTestPeriod -> -1, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost:3306/mytestbase, maxIdleTime -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 300, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, usesTraditionalReflectiveProxies -> false ]
com.mchange.v2.c3p0.impl.NewProxyConnection@7a4f0f29