mysql筆記三-----sql存儲過程、事務的隔離級别和sql各查詢的差別、防黑

存儲過程

※※存儲過程※※※
定義：
create procedure 過程名(參數)
begin
  多條sql語句
end

調用：
call 過程名(實參)

執行個體--無參的存儲過程：
△小細節：要把預設的語句結束“;”号改成其它如“$$”，這樣存儲過程中定義的分号就不被看成是語句結束(否則會直接被送出)。最後再把“;”号還原成預設的結束符。

delimiter $$
create procedure p1()
begin
  insert into stud values('P0010','小李',);
  select * from stud;
end$$
delimiter ;

call p1();


執行個體2--有參的存儲過程：
delimiter $$
create procedure p2( in id varchar(), in nm varchar(), in age int )
begin
   insert into stud values(id,nm,age);
   select * from stud;
end$$
delimiter ;

call p2('P011','小五',);

執行個體3--有傳回值的存儲過程：
delimiter $$
create procedure p3( in id varchar(), in nm varchar(), in age int, out num int )
begin
   insert into stud values(id,nm,age);
   select * from stud;
   select count(*) into num from stud;
end$$
delimiter ;

CALL p3('P012','小小五',, @aa); /*調用且用aa接收結果*/

SELECT @aa;  /*顯示使用者變量aa*/
系統變量名稱：@@變量名
使用者變量名稱：@變量名

//binary
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%'
也可以在建表時，加以辨別? 
create table table_name(
  a varchar() binary
)

/事務處理
DELETE FROM stud WHERE id='P006';
START TRANSACTION;
DELETE FROM stud WHERE id='P011';
UPDATE stud SET NAME='abc' WHERE id='P003';
ROLLBACK  /   COMMIT;

說明：從"START TRANSACTION"開始 到 “ROLLBACK; 或 COMMIT; ”，這中間的那麼語句是一個整體，如果執行 “ROLLBACK”，那麼這些動作都會復原(撤消)。如果執行“COMMIT”，就全部執行成功。

Java實作事務處理的簡單模闆
// /以下示範Java中如何實作事務
// /以下示範Java中如何實作事務
try {
    con.setAutoCommit(false);// 對應mysql中的“START TRANSACTION;”的功能

    String sql = "INSERT INTO sstud VALUES('1017','楊過',,'武俠','1')";
    st.execute(sql);// 增
    sql = "delete from sstud where sno='1015' ";
    st.execute(sql);// 删
    //sql = "update sstud set saddresos='中國北京' where sname='劉備' ";// 注意，這種方式不能寫多條sql語句(中間用分号隔也不行)
    sql = "update sstud set saddress='中國北京' where sname='劉備' ";// 注意，這種方式不能寫多條sql語句(中間用分号隔也不行)
    st.execute(sql);// 改

    con.commit();
    System.out.println("事務送出了.....");
} catch (Exception e) {
    System.out.println("事務復原了.....");
    con.rollback();
}

事務隔離

查詢事務隔離級别：

select @@tx_isolation;

設定事務隔離級别(read-uncommitted)：set session transaction isolation level read uncommitted;//可以讀到沒有其他用戶端送出的

設定事務隔離級别(read-committed)：set session transaction isolation level read committed;//可以讀到其他用戶端送出的資訊

設定事務隔離級别(repeatable-read，預設)：set session transaction isolation level repeatable read;//其他用戶端操作對自己不會有影響

設定事務隔離級别(serializable，最進階别)：set session transaction isolation level serializable;//相當于單線程操作，在進行查詢時就會對表或行加上共享鎖，其他事務對該表将隻能進行讀操作，而不能進行寫操作

sql查詢，防黑

package cn.hncu.demo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import org.junit.Test;


import cn.hncu.utils.ConnectFactory;

public class JdbcDemo {
    Connection con=ConnectFactory.getCon();
    /*Statement中有4個執行方法可用
     * 1, executeQuery() : 隻能執行查詢語句
     * 2, executeUpdate(): 隻能增、删、改, 不能執行查詢語句
     * 3, execute(): 增、删、改、查的語句都能夠執行。隻是查詢時傳回的結果是告訴成功與否，如果要擷取查詢結果，得另外用" st.getResultSet()"擷取
     * 4, executeBatch()//批處理，可以處理多條語句
     */
    @Test
    public void results() throws SQLException{
        Statement st=con.createStatement();
        String sql="select * from book";
        ResultSet rs=st.executeQuery(sql);
        while(rs.next()){
            Integer id=rs.getInt();
            String name=rs.getString("name");
            Double price=rs.getDouble();
            String birth=rs.getDate()+" "+rs.getTime();//注意擷取日期時間型資料的方式
            System.out.println(id+","+name+","+price+","+birth);
        }
        con.close();
    }
    @Test
    public void execute() throws SQLException{
        Statement st=con.createStatement();
        String sql=null;
        //      sql="INSERT INTO book(NAME,price,birth) VALUES('oracle',50.11,'2015-8-8 19:33:10')";
        //      sql="DELETE FROM book WHERE name='oracle';";
        sql="update  book set price=price*1.8 where name='sql'";
        st.execute(sql);
        results();
    }
    @Test
    public void executeUpdate() throws Exception{ 
        Statement st=con.createStatement();
        String sql=null;
        //              sql="INSERT INTO book(NAME,price,birth) VALUES('oracle',50.11,'2015-8-8 19:33:10')";
        sql="DELETE FROM book WHERE name='oracle';";
        //sql="update  book set price=price*1.8 where name='sql'";
        int num=st.executeUpdate(sql);//傳回值是影響的行數
        System.out.println(num);
        results();
    }
    @Test //容易産生bug：如輸入name值為: b'c
    public void reg() throws Exception{ 
        Statement st=con.createStatement();
        Scanner sc=new Scanner(System.in);
        String id=sc.nextLine();
        String name =sc.nextLine();
        Integer age=Integer.parseInt(sc.nextLine());
        String sql="INSERT INTO stud(id,name,age) values('"+id+"','"+name+"',"+age+")";
        boolean boo=st.execute(sql);//不能用boolean來判斷，因為傳回時ResultSet才是YES
        //      if(boo){
        //          System.out.println("注冊成功");
        //      }else {
        //          System.out.println("注冊失敗");
        //      }
    }
    @Test //容易産生bug：如輸入name值為: 'a' or '1=1
    public void login() throws Exception{ 
        Statement st=con.createStatement();
        Scanner sc=new Scanner(System.in);
        String id=sc.nextLine();
        String name =sc.nextLine();
        String sql=null;
        sql="select  count(*) from stud where id='"+id+"' and name='"+name+"'";
        ResultSet rs=st.executeQuery(sql);
        rs.next();
        int n = rs.getInt();
        System.out.println(sql);
        if(n<=){
            System.out.println("登入失敗");
        }else {
            System.out.println("登入成功");
        }
        con.close();
    }
    //采用PrepareStatement
    @Test //不會被黑：如輸入name值為: a' or '1'='1
    public void login2() throws Exception{
        Statement st=con.createStatement();
        Scanner sc=new Scanner(System.in);
        String id=sc.nextLine();
        String name =sc.nextLine();
        String sql=null;
        sql="select  count(*) from stud where id=? and name=?";
        //建立預處理語句對象
        PreparedStatement ps=con.prepareStatement(sql);
        //給占位設定值---設定參數
         //給第1個參數設定
        ps.setString(, id);
        //給第2個參數設定
        ps.setString(, name);
        ResultSet rs =ps.executeQuery();//不接受參數
        rs.next();
        int n = rs.getInt();
        if(n<=){
            System.out.println("登入失敗...");
        }else{
            System.out.println("登入成功....");
        }

        con.close();
    }
    @Test//擷取自動增長列的值
    public void  getAuto() throws SQLException{
        Statement st=con.createStatement();
        String sql="INSERT INTO book(NAME,price,birth) VALUES('紅樓夢',100.11,'2015-5-8 19:23:10');";
        st.execute(sql, Statement.RETURN_GENERATED_KEYS);//需要調用Statement.RETURN_GENERATED_KEYS
        ResultSet rs=st.getGeneratedKeys();
        while(rs.next()){
            int id=rs.getInt();
            System.out.println("自動增長的值"+id);
        }
    }
    @Test//擷取自動增長列的值PrepareStatement
    public void  getAuto2() throws SQLException{
        String sql="INSERT INTO book(NAME,price,birth) VALUES(?,?,'2015-5-8 19:23:10');";
        PreparedStatement ps=con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.setString(, "西遊記");
//      ps.setInt(2, 59);//能插入，但是會報錯！
        ps.setDouble(, );
        ps.execute();
        ResultSet rs=ps.getGeneratedKeys();
        while(rs.next()){
            int id=rs.getInt();
            System.out.println("自動增長的值"+id);
        }
    }
    @Test 
    /*執行批處理---自己本身不帶事務，如果其中某條sql挂，則後續的sql執行失敗，
    前面的還是有效的。如果要事務，另外再采用:con.setAutoCommit(false)+
    try-cacth+ rollback/commit*/
    public void batchDemo() throws SQLException{
        Statement st=con.createStatement();
        String sql="INSERT INTO book(NAME,price,birth) VALUES('aa',2,'2016-5-8 19:23:10')";//有沒有分号都一樣
        for(int i=;i<;i++){
//          if(i==3){
//              sql="INSERT INTO book(NAME,price,birth) VALUES('aa,2,'2016-5-8 19:23:10')";//有沒有分号都一樣
//          }
            st.addBatch(sql);
        }
        int[] nums=st.executeBatch();
        for(int i=;i<nums.length;i++){
            System.out.println(nums[i]);
        }
        System.out.println(nums.length);
    }
    @Test
    public void prepBatchDemo() throws SQLException{
        String sql="INSERT INTO book(NAME,price,birth) VALUES(?,?,'2016-5-8 19:23:10')";//有沒有分号都一樣
        PreparedStatement ps=con.prepareStatement(sql);
        for(int i=;i<;i++){
            ps.setString(, "大話西遊");
            ps.setDouble(, );
            ps.addBatch();
        }
        int[] nums=ps.executeBatch();
        for(int i=;i<nums.length;i++){
            System.out.println(nums[i]);
        }
        System.out.println(nums.length);
    }
}

這是Connect工具類（單例)）

package cn.hncu.utils;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class ConnectFactory {
    private static Connection con=null;
    static{
        try {
            Properties p=new Properties();
            p.load(ConnectFactory.class.getClassLoader().getResourceAsStream("jdbc.properities"));
            String url=p.getProperty("url");
            String user=p.getProperty("user");
            String password=p.getProperty("password");
            con=DriverManager.getConnection(url, user, password);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static Connection getCon(){
        return con;
    }
    public static void main(String[] args) {
        System.out.println(getCon());
    }
}

mysql筆記三-----sql存儲過程、事務的隔離級别和sql各查詢的差別、防黑

繼續閱讀

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

android SQLite事務和kotlin協程

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離