Java和MySQL簡建立連接

JDBC

JDBC插入多條數(shù)據(jù)

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Test6 {

    public static void main(String[] args) throws Exception {
        //如何插入多條數(shù)據(jù)呜师，減少數(shù)據(jù)庫的壓力
        
        
        //4個(gè)數(shù)據(jù)源
        //1.驅(qū)動(dòng)：連接數(shù)據(jù)庫的一個(gè)不可或缺的類娶桦，可以在com.mysql.jdbc包下找到Driver這個(gè)類
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc連接數(shù)據(jù)庫的路徑
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees";
        
        //3.用戶名
        String userName = "root";
        //4.密碼
        String psw = "123456";
        
        //五個(gè)執(zhí)行步驟
        //1.加載驅(qū)動(dòng)
        Class.forName(driver);
        
        //2.連接數(shù)據(jù)庫
        //獲取數(shù)據(jù)庫連接
        //連接對(duì)象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        
        
        String selectSql = "insert into employees (last_name) values (?)";
        
        //3.預(yù)編譯sql，獲取預(yù)編譯對(duì)象：預(yù)先編譯汁汗，在執(zhí)行之前編譯成二進(jìn)制
        PreparedStatement pre = conn.prepareStatement(selectSql);
        
        //Batch就是把所有的代碼放在一塊兒執(zhí)行
        for (int i = 0; i < 10; i++) {
            //方法括號(hào)里面的1表示第一個(gè)衷畦？
            pre.setString(1, String.valueOf(i));
            pre.addBatch();
        }
        
        int[] i = pre.executeBatch();
        
        for (int j : i) {
            System.out.println(j);
        }
        
        conn.close();
    }
}

JDBC查詢

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test6 {

    public static void main(String[] args) throws Exception {
        // 如何執(zhí)行查詢操作

        //4個(gè)數(shù)據(jù)源
        //1.驅(qū)動(dòng)：連接數(shù)據(jù)庫的一個(gè)不可或缺的類，可以在com.mysql.jdbc包下找到Driver這個(gè)類
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc連接數(shù)據(jù)庫的路徑
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees";
        
        //3.用戶名
        String userName = "root";
        //4.密碼
        String psw = "123456";
        
        //五個(gè)執(zhí)行步驟
        //1.加載驅(qū)動(dòng)
        Class.forName(driver);
        
        //2.連接數(shù)據(jù)庫
        //獲取數(shù)據(jù)庫連接
        //連接對(duì)象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        String selectSql = "select last_name, department_id from employees where employee_id = 104";
        
        //3.預(yù)編譯sql知牌，獲取預(yù)編譯對(duì)象：預(yù)先編譯祈争，在執(zhí)行之前編譯成二進(jìn)制
        PreparedStatement pre =  conn.prepareStatement(selectSql);
        
        //4.執(zhí)行查詢操作
        ResultSet res = pre.executeQuery();
        //next相當(dāng)于游標(biāo)
        while(res.next()) {
            //String也可以寫Object
            System.out.println(res.getString("last_name"));
            System.out.println(res.getObject("department_id"));
        }
        
        //5.關(guān)閉連接對(duì)象
        conn.close();
    }
}

JDBC動(dòng)態(tài)傳參

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test6 {

    public static void main(String[] args) throws Exception {
        //動(dòng)態(tài)傳參where
        
        
        //4個(gè)數(shù)據(jù)源
        //1.驅(qū)動(dòng)：連接數(shù)據(jù)庫的一個(gè)不可或缺的類，可以在com.mysql.jdbc包下找到Driver這個(gè)類
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc連接數(shù)據(jù)庫的路徑
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees";
        
        //3.用戶名
        String userName = "root";
        //4.密碼
        String psw = "123456";
        
        //五個(gè)執(zhí)行步驟
        //1.加載驅(qū)動(dòng)
        Class.forName(driver);
        
        //2.連接數(shù)據(jù)庫
        //獲取數(shù)據(jù)庫連接
        //連接對(duì)象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        String selectSql = "select last_name, department_id, salary from employees where last_name = ? or year = ?";
        
        //3.預(yù)編譯sql角寸，獲取預(yù)編譯對(duì)象：預(yù)先編譯菩混，在執(zhí)行之前編譯成二進(jìn)制
        PreparedStatement pre =  conn.prepareStatement(selectSql);
        pre.setString(1, "Ernst");
        pre.setInt(2, 50);

        //4.執(zhí)行查詢操作
        ResultSet res = pre.executeQuery();
        //next相當(dāng)于游標(biāo)
        while(res.next()) {
            //String也可以寫Object
            System.out.println(res.getString("last_name"));
            System.out.println(res.getObject("department_id"));
            System.out.println(res.getObject("salary"));
        }
        
        //5.關(guān)閉連接對(duì)象
        conn.close();
    }
}

JDBC回滾

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Test6 {

    public static void main(String[] args) throws Exception {
        //關(guān)閉自動(dòng)提交，使用rollback
        //4個(gè)數(shù)據(jù)源
        //1.驅(qū)動(dòng)：連接數(shù)據(jù)庫的一個(gè)不可或缺的類扁藕，可以在com.mysql.jdbc包下找到Driver這個(gè)類
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc連接數(shù)據(jù)庫的路徑
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees";
        
        //3.用戶名
        String userName = "root";
        //4.密碼
        String psw = "123456";
        
        //五個(gè)執(zhí)行步驟
        //1.加載驅(qū)動(dòng)
        Class.forName(driver);
        
        //2.連接數(shù)據(jù)庫
        //獲取數(shù)據(jù)庫連接
        //連接對(duì)象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        //默認(rèn)自動(dòng)提交沮峡，修改為手動(dòng)提交
        conn.setAutoCommit(false);
        
        String sql = "delete from employees where employee_id = ?";
        
        //3.預(yù)編譯sql，獲取預(yù)編譯對(duì)象：預(yù)先編譯亿柑，在執(zhí)行之前編譯成二進(jìn)制
        PreparedStatement pre =  conn.prepareStatement(sql);
        pre.setInt(1, 104);
        //4.拿到數(shù)據(jù)庫里執(zhí)行
        //i：在數(shù)據(jù)庫內(nèi)執(zhí)行了多少條數(shù)據(jù)
        int i = pre.executeUpdate();
        
        System.out.println(i);
        
        conn.rollback();
        //5.關(guān)閉連接對(duì)象
        conn.close();
    }
}

JDBC將數(shù)據(jù)庫中的信息裝入List

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class Test6 {

    public static void main(String[] args) throws Exception {
        //如何將數(shù)據(jù)庫中的數(shù)據(jù)裝入List
        
        //4個(gè)數(shù)據(jù)源
        //1.驅(qū)動(dòng)：連接數(shù)據(jù)庫的一個(gè)不可或缺的類邢疙，可以在com.mysql.jdbc包下找到Driver這個(gè)類
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc連接數(shù)據(jù)庫的路徑
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees";
        
        //3.用戶名
        String userName = "root";
        //4.密碼
        String psw = "123456";
        
        //五個(gè)執(zhí)行步驟
        //1.加載驅(qū)動(dòng)
        Class.forName(driver);
        
        //2.連接數(shù)據(jù)庫
        //獲取數(shù)據(jù)庫連接
        //連接對(duì)象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        String selectSql = "select last_name, department_id, salary from employees";
        
        //3.預(yù)編譯sql，獲取預(yù)編譯對(duì)象：預(yù)先編譯，在執(zhí)行之前編譯成二進(jìn)制
        PreparedStatement pre = conn.prepareStatement(selectSql);
        
        //4.執(zhí)行查詢操作
        ResultSet res = pre.executeQuery();
        
        List<Employees> list = new ArrayList<Employees>();
        //next相當(dāng)于游標(biāo)
        while(res.next()) {
            Employees employees = new Employees();
            employees.setLast_name(res.getString("last_name"));
            list.add(employees);
        }
        for (Employees employees : list) {
            System.out.println(employees.getLast_name());
        }
        System.out.println(list);
        
        //5.關(guān)閉連接對(duì)象
        conn.close();
    }
}

JDBC模糊查詢

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test6 {

    public static void main(String[] args) throws Exception {
        // 如何進(jìn)行模糊查詢
        
        
        //4個(gè)數(shù)據(jù)源
        //1.驅(qū)動(dòng)：連接數(shù)據(jù)庫的一個(gè)不可或缺的類疟游，可以在com.mysql.jdbc包下找到Driver這個(gè)類
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc連接數(shù)據(jù)庫的路徑
        String url = "jdbc:mysql://127.0.0.1:3306/sale";
        
        //3.用戶名
        String userName = "root";
        //4.密碼
        String psw = "123456";
        
        //五個(gè)執(zhí)行步驟
        //1.加載驅(qū)動(dòng)
        Class.forName(driver);
        
        //2.連接數(shù)據(jù)庫
        //獲取數(shù)據(jù)庫連接
        //連接對(duì)象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        String name = "金";
        //這種寫法不常用
//      String selectSql = "select username from user where username like '%" + name + "%'";
        
        
        String selectSql = "select username from user where username like ?";
        
        //3.預(yù)編譯sql呼畸，獲取預(yù)編譯對(duì)象：預(yù)先編譯，在執(zhí)行之前編譯成二進(jìn)制
        PreparedStatement pre = conn.prepareStatement(selectSql);
        
        pre.setString(1, "%" + name + "%");

        //4.執(zhí)行查詢操作
        ResultSet res = pre.executeQuery();
        //next相當(dāng)于游標(biāo)
        while(res.next()) {
            //String也可以寫Object
            System.out.println(res.getString("username"));
        }
        
        //5.關(guān)閉連接對(duì)象
        conn.close();
    }
}

JDBC如何寫多條SQL語句

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test6 {

    public static void main(String[] args) throws Exception {
        //如何在1個(gè)JDBC中寫入多個(gè)SQL語句
        //4個(gè)數(shù)據(jù)源
        //1.驅(qū)動(dòng)：連接數(shù)據(jù)庫的一個(gè)不可或缺的類颁虐，可以在com.mysql.jdbc包下找到Driver這個(gè)類
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc連接數(shù)據(jù)庫的路徑
        String url = "jdbc:mysql://127.0.0.1:3306/school";
        
        //3.用戶名
        String userName = "root";
        //4.密碼
        String psw = "123456";
        
        //五個(gè)執(zhí)行步驟
        //1.加載驅(qū)動(dòng)
        Class.forName(driver);
        
        //2.連接數(shù)據(jù)庫
        //獲取數(shù)據(jù)庫連接
        //連接對(duì)象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        String sql = "select * from student where classid = ?";
        String sql2 = "select * from teacher where class_id = ?";
        
        //3.預(yù)編譯sql役耕，獲取預(yù)編譯對(duì)象：預(yù)先編譯，在執(zhí)行之前編譯成二進(jìn)制
        PreparedStatement pre = conn.prepareStatement(sql);
        PreparedStatement pre2 = conn.prepareStatement(sql2);
        pre.setInt(1, 1);
        pre2.setInt(1, 1);
        
         //4.執(zhí)行查詢操作
         ResultSet res = pre.executeQuery();
         ResultSet res2 = pre2.executeQuery();
                
        //next相當(dāng)于游標(biāo)
                while(res.next()) {
                    System.out.println(res.getObject("name"));
                }
                while(res2.next()) {
                    System.out.println(res2.getObject("name"));
                }
        //5.關(guān)閉連接對(duì)象
        conn.close();
    }
}

JDBC如何用res.nex()判斷是否在數(shù)據(jù)庫中有重復(fù)的值

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test6 {

    public static void main(String[] args) throws Exception {
        // 如何用res.next()判斷是否重復(fù)
        
        //4個(gè)數(shù)據(jù)源
        //1.驅(qū)動(dòng)：連接數(shù)據(jù)庫的一個(gè)不可或缺的類聪廉，可以在com.mysql.jdbc包下找到Driver這個(gè)類
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc連接數(shù)據(jù)庫的路徑
        String url = "jdbc:mysql://127.0.0.1:3306/sale";
        
        //3.用戶名
        String userName = "root";
        //4.密碼
        String psw = "123456";
        
        //五個(gè)執(zhí)行步驟
        //1.加載驅(qū)動(dòng)
        Class.forName(driver);
        
        //2.連接數(shù)據(jù)庫
        //獲取數(shù)據(jù)庫連接
        //連接對(duì)象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        String selectSql = "select username from user where username = '劉吉'";
        
        //3.預(yù)編譯sql瞬痘，獲取預(yù)編譯對(duì)象：預(yù)先編譯，在執(zhí)行之前編譯成二進(jìn)制
        PreparedStatement pre =  conn.prepareStatement(selectSql);
        
        //4.執(zhí)行查詢操作
        ResultSet res = pre.executeQuery();
        //next相當(dāng)于游標(biāo)
        //res.next()就是查看sql語句的查詢結(jié)果板熊，如果有值框全，則返回true；如果沒有干签，則返回false
        if (res.next()) {
            System.out.println("用戶名重復(fù)");
        }
        else {
            System.out.println("用戶名不重復(fù)");
        }
        
        
        //5.關(guān)閉連接對(duì)象
        conn.close();
    }
}

JDBC手動(dòng)提交

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test6 {

    public static void main(String[] args) throws Exception {
        //關(guān)閉自動(dòng)提交
        //4個(gè)數(shù)據(jù)源
        //1.驅(qū)動(dòng)：連接數(shù)據(jù)庫的一個(gè)不可或缺的類津辩，可以在com.mysql.jdbc包下找到Driver這個(gè)類
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc連接數(shù)據(jù)庫的路徑
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees";
        
        //3.用戶名
        String userName = "root";
        //4.密碼
        String psw = "123456";
        
        //五個(gè)執(zhí)行步驟
        //1.加載驅(qū)動(dòng)
        Class.forName(driver);
        
        //2.連接數(shù)據(jù)庫
        //獲取數(shù)據(jù)庫連接
        //連接對(duì)象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        //默認(rèn)自動(dòng)提交
        conn.setAutoCommit(false);
        
        String sql = "delete from employees where employee_id = ?";
        
        //3.預(yù)編譯sql，獲取預(yù)編譯對(duì)象：預(yù)先編譯容劳，在執(zhí)行之前編譯成二進(jìn)制
        PreparedStatement pre =  conn.prepareStatement(sql);
        pre.setInt(1, 121);
        //4.拿到數(shù)據(jù)庫里執(zhí)行
        //i：在數(shù)據(jù)庫內(nèi)執(zhí)行了多少條數(shù)據(jù)
        int i = pre.executeUpdate();
        
        System.out.println(i);
        
        conn.commit();
        //5.關(guān)閉連接對(duì)象
        conn.close();
    }
}

JDBC增刪改

package com.easy.view;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test6 {

    public static void main(String[] args) throws Exception {
        //如何執(zhí)行增刪改的操作
        //4個(gè)數(shù)據(jù)源
        //1.驅(qū)動(dòng)：連接數(shù)據(jù)庫的一個(gè)不可或缺的類喘沿，可以在com.mysql.jdbc包下找到Driver這個(gè)類
        String driver = "com.mysql.jdbc.Driver";
        //2.jdbc連接數(shù)據(jù)庫的路徑
        String url = "jdbc:mysql://127.0.0.1:3306/myemployees";
        
        //3.用戶名
        String userName = "root";
        //4.密碼
        String psw = "123456";
        
        //五個(gè)執(zhí)行步驟
        //1.加載驅(qū)動(dòng)
        Class.forName(driver);
        
        //2.連接數(shù)據(jù)庫
        //獲取數(shù)據(jù)庫連接
        //連接對(duì)象
        java.sql.Connection conn = DriverManager.getConnection(url, userName, psw);
        
        String sql = "delete from employees where employee_id = 120";
        
        //3.預(yù)編譯sql，獲取預(yù)編譯對(duì)象：預(yù)先編譯竭贩，在執(zhí)行之前編譯成二進(jìn)制
        PreparedStatement pre =  conn.prepareStatement(sql);
        
        //4.拿到數(shù)據(jù)庫里執(zhí)行
        //i：在數(shù)據(jù)庫內(nèi)執(zhí)行了多少條數(shù)據(jù)
        int i = pre.executeUpdate();
        
        System.out.println(i);
        //5.關(guān)閉連接對(duì)象
        conn.close();
    }
}

MyBatis

main方法里面寫

//獲取一個(gè)sql的會(huì)話蚜印，連接數(shù)據(jù)庫
SqlSession sqlSession = MyBatisTools.getSqlSession();

//獲取接口對(duì)象，以class結(jié)尾
//Mapper叫作映射
EmployeesMapper emp = sqlSession.getMapper(EmployeesMapper.class);

//中間用emp就可以調(diào)用EmployeesMapper接口里面的方法

//提交：Mybatis不是自動(dòng)提交
sqlSession.commit();
//關(guān)閉會(huì)話
sqlSession.close();

查詢最高工資

//⑤查詢部門最高工資留量，取出的max(salary)不可能用一個(gè)屬性來接窄赋，因此定義int類型就好了
    int selectMaxsalary();

<!-- //⑤查詢部門最高工資，取出的max(salary)不可能用一個(gè)屬性來接楼熄，因此定義int類型就好了 -->
    <select id = "selectMaxsalary" resultType = "java.lang.Integer">
        select max(salary) from employees;
    </select>

//      int i = emp.selectMaxsalary();
//      System.out.println(i);

傳多個(gè)參數(shù)用對(duì)象

//④傳多個(gè)參數(shù)忆绰，用對(duì)象。保證實(shí)體類里面的屬性和數(shù)據(jù)庫中表的字段名一致
    Employees selectBysalaryAndyear(Employees employees);

    <!-- //④傳多個(gè)參數(shù)可岂，用對(duì)象 -->
    <select id = "selectBysalaryAndyear"  parameterType = "com.easyup.model.Employees" resultType = "com.easyup.model.Employees">
        <!-- {}里面寫實(shí)體類里面屬性的名字 -->
        select last_name from employees where salary = #{salary} and year = #{year};
    </select>

Employees employees = new Employees();
//      employees.setYear(22);
//      employees.setSalary(42000);
//      Employees i = emp.selectBysalaryAndyear(employees);
//      System.out.println(i.getLast_name());

動(dòng)態(tài)SQL

    //⑥動(dòng)態(tài)SQL
    //（1）where if幢泼，可以省略前面的and
    List<Employees> selectByNameAndJobId(Employees emp);
    //（2）set if：可以省略掉后面的逗號(hào)
    int updateLastName(Employees emp);
    //（3）foreach————SQL:list(1,2,3,4)：遍歷集合
    List<Employees> selectEmpByids(List<Integer> ids);
    //（4）<sql><include>標(biāo)簽可以將select后面要查詢的語句重復(fù)使用仗嗦，不必每次都寫
    Employees selectEmpById(Employees emp);


<!-- //⑥動(dòng)態(tài)SQL
    //（1）where if，可以省略前面的and -->
    <select id = "selectByNameAndJobId" resultType = "com.easyup.model.Employees">
        select last_name, job_id
        from employees
        <where>
        <!-- where if 是去掉字if里面前面的and的，但是不能刪除if里面后面的and -->
        <!-- where if 至少傳入一個(gè)參數(shù) -->
        
            <if test = "last_name != null and last_name != ''">
                last_name = #{last_name}
            </if>
            
            <if test= "job_id != null and job_id != ''">
                and job_id = #{job_id}
            </if>
        </where>
    </select>


    <!-- //（2）set if：可以省略掉后面的逗號(hào) -->
    <update id = "updateLastName" >
        update employees
        <!--  刪除語句后面的逗號(hào) -->
        <set>
            <if test = "last_name != null and last_name != ''">
                last_name = #{last_name},
            </if >
            
            <if test= "job_id != null and job_id != ''">
                job_id = #{job_id}
            </if>
        </set>
        where employee_id = #{employee_id}
    </update>
    
    <!-- //（3）foreach————SQL:list(1,2,3,4)：遍歷集合 -->
    <!-- 此處參數(shù)是int類型的集合虱饿，可以寫參數(shù)也可以不寫 -->
    <select id = "selectEmpByids" parameterType = "java.lang.Integer" resultType = "com.easyup.model.Employees">
        select last_name
        from employees
        where employee_id
        in
        <朗伶！-- item表示in（）里面的內(nèi)容 -->
        <foreach collection = "list" open = "(" close = ")" separator = "," item = "item">
            #{item}
        </foreach>
    </select>
    
    
    <!-- //（4）<sql><include>標(biāo)簽可以將select后面要查詢的語句重復(fù)使用悼嫉，不必每次都寫 -->
    <sql id = "empSql">
    last_name, job_id, salary, employee_id, year
    </sql>

    <select id = "selectEmpById" parameterType = "com.easyup.model.Employees"  resultType = "com.easyup.model.Employees">
        select 
        <include refid = "empSql"></include>
        from employees
        where employee_id = #{employee_id}
    </select>


//      Employees empl = new Employees(); 
        
        //where if方法的調(diào)用
//      empl.setLast_name("Ernst");
//      empl.setJob_id("IT_PROG");
        
//      List<Employees> list = emp.selectByNameAndJobId(empl);
//      for (Employees employees : list) {
//          System.out.println(employees.getLast_name());
//          System.out.println(employees.getJob_id());
//      }
        
        //set if方法的調(diào)用
//      empl.setLast_name("Pataballa11111");
//      empl.setEmployee_id(106);
//      int i = emp.updateLastName(empl);
//      System.out.println(i);
        
        //foreach方法的調(diào)用
//      List<Integer> list = new ArrayList<Integer>();
//      list.add(105);
//      list.add(106);
//      list.add(107);
//      list.add(108);
//      List<Employees> selectEmpByids = emp.selectEmpByids(list);
//      for (Employees employees : selectEmpByids) {
//          System.out.println(employees.getLast_name());
//      }
        
        //<include>方法的調(diào)用
//      empl.setEmployee_id(105);
//      Employees selectEmpById = emp.selectEmpById(empl);
//      System.out.println(selectEmpById);

動(dòng)態(tài)傳參需要寫入?yún)?shù)

//③動(dòng)態(tài)傳參需要寫入?yún)?shù)
    Employees selectById(int employee_id);


<!-- //③動(dòng)態(tài)傳參需要寫入?yún)?shù) -->
    <!-- 動(dòng)態(tài)傳參 -->
    <select id = "selectById"  parameterType = "java.lang.Integer" resultType = "com.easyup.model.Employees">
        <!-- {}里面寫實(shí)體類里面屬性的名字解孙，或者參數(shù)的名字 -->
        select last_name from employees where employee_id = #{employee_id};
    </select>


//查看一個(gè)字段的單個(gè)信息
//      Employees employees= emp.selectById(108);
//      System.out.println(employees.getLast_name());

多表查詢：一對(duì)多

    //多表查詢：查詢一個(gè)表和另一個(gè)表中相同字段有相同值，因此主表也有多個(gè)值双戳，需要用List來接
    //（2）一對(duì)多
    List<Departments> selectDepEmp();


    <!-- //（2）一對(duì)多 -->
    <resultMap type = "com.easyup.model.Departments" id = "DepMap">
        <result column = "department_name" property = "departmentName"/>
        <collection property = "empList" ofType = "com.easyup.model.Employees">
            <result column = "last_name" property = "lastName"/>
        </collection>
    </resultMap>

    <select id = "selectDepEmp" resultMap = "DepMap">
        select departments.department_name, employees.last_name
        from departments, employees
        where departments.department_id = employees.department_id;
    </select>


        //一對(duì)多
//      List<Departments> list = dep.selectDepEmp();
//      for (Departments d : list) {
//          System.out.println(d.getDepartmentName());
//          for (Employees e : d.getEmpList()) {
//              System.out.println(e.getLastName());
//          }
//      }

多表查詢：一對(duì)一

    //⑨多表查詢
    //（1）一對(duì)一：兩張表分別由同一字段虹蒋。要明白是從誰出發(fā)去找誰。
    //注意事項(xiàng)：①分清楚多表查詢中的一對(duì)一和懶加載的一對(duì)一的區(qū)別：多表查詢的一對(duì)一只需在該類中有另外一個(gè)類的變量，②如果
    //從employees找card
    List<Employees> selectCard();
    //從card找employees
    List<Card> selectCardEmp();


    <!-- //⑨多表查詢 -->
    <!-- //（1）一對(duì)一：兩張表分別由同一字段魄衅。要明白是從誰出發(fā)去找誰峭竣。 -->
    <!-- 從Employees去取Card -->
    <resultMap type = "com.easyup.model.Employees" id = "EmpCard">
        <result column = "last_name" property = "lastName"/>
        <!-- 一對(duì)一的關(guān)系 -->
        <!-- 從員工表出發(fā)，和card是一對(duì)一的關(guān)系 -->
        <association property = "card" javaType = "com.easyup.model.Card">
            <result column = "card_num" property = "num"/>
        </association>
    </resultMap>
    
    <select id = "selectCard" resultMap = "EmpCard">
        select employees.last_name, card.card_num, employees.salary
        from employees,card
        where employees.card_id = card.id;
    </select>
    
    
    <!-- 從Card去取Employees -->
    <resultMap type = "com.easyup.model.Card" id = "CardEmp">
        <result column = "card_num" property = "num"/>
        <result column = "id" property = "Id"/>
        <!-- 一對(duì)一的關(guān)系 -->
        <!-- 從員工表出發(fā)晃虫，和card是一對(duì)一的關(guān)系 -->
        <!-- association里面的property寫的是Employees里面的一個(gè)成員變量 -->
        <association property = "employees" javaType = "com.easyup.model.Employees">
            <result column = "last_name" property = "lastName"/>
            <result column = "salary" property = "salary"/>
            <result column = "year" property = "year"/>
            <result column = "employee_id" property = "employee_id"/>
        </association>
    </resultMap>
    
    <select id = "selectCardEmp" resultMap = "CardEmp">
        select employees.last_name, card.card_num, employees.salary, employees.year, employees.employee_id
        from employees,card
        where employees.card_id = card.id;
    </select>


        //從員工表找Card
//      List<Employees> selectCard = emp.selectCard();
//      for (Employees employees : selectCard) {
//          System.out.println(employees);
//      }
        
        //從Card找員工表
//      List<Card> selectCardEmp = emp.selectCardEmp();
//      
//      for (Card card : selectCardEmp) {
//          System.out.println(card);
//      }

剛插入一條數(shù)據(jù)皆撩，取出id

    //⑩剛插進(jìn)去一條信息，然后取出主鍵：只能用于使用對(duì)象作為參數(shù)的情況
    int insertEmp(Employees emp);


    <!-- //⑩剛插進(jìn)去一條信息哲银，然后取出主鍵 -->
    <insert id = "insertEmp" parameterType = "com.easyup.model.Employees">
        <selectKey order = "AFTER" resultType = "java.lang.Integer" keyProperty = "employee_id">
            //固定方法名
            select last_insert_id()
        </selectKey>
            insert into employees
            (last_name) values (#{lastName})
    </insert>


        //向數(shù)據(jù)庫中添加數(shù)據(jù)扛吞，然后馬上取到對(duì)應(yīng)的主鍵
//      Employees employees = new Employees();
//      employees.setLastName("小明33333333333");
//      int insertEmp = emp.insertEmp(employees);
//      System.out.println("執(zhí)行了：" + insertEmp);
//      System.out.println("id：" + employees.getEmployee_id());

高級(jí)映射

    //⑦高級(jí)映射
    //增刪改不需要結(jié)果集映射，只有查詢的時(shí)候需要用到結(jié)果集映射
    Employees selectById11();


    <!-- //⑦高級(jí)映射 -->
    <!--//增刪改不需要結(jié)果集映射荆责，只有查詢的時(shí)候需要用到結(jié)果集映射 -->
    <resultMap type = "com.easyup.model.Employees" id = "empMap">
        <result column = "last_name" property = "lastName"/>
    </resultMap>
    <select id = "selectById11" resultMap = "empMap">
        select <include refid = "empSql"></include>
        from employees 
        where employee_id = 108;
    </select>


//      Employees selectById11 = emp.selectById11();
//      System.out.println(selectById11);

懶加載滥比，一對(duì)一

//懶加載，又叫延遲加載做院、按需加載
//把多表查詢盲泛，拆成單表查詢，比如從員工表找card表键耕，只有調(diào)用getcard的時(shí)候才會(huì)去查card表
//注意事項(xiàng)：①Employees里面要有Card類型的card寺滚；②主查詢要查詢那個(gè)與子查詢相關(guān)聯(lián)的字段；③子查詢的參數(shù)要寫與主查詢相關(guān)聯(lián)的字段屈雄；④log文件要放在src的目錄下

    //主查詢的方法定義
    Employees selectCardlazy(@Param("empId")int employeeId);


    //主查詢的XML
    <resultMap type = "com.easyup.model.Employees" id = "cc">
        <result column = "last_name" property = "lastName"/>
        <association property = "card" javaType = "com.easyup.model.Card"
        select = "com.easyup.mapper.CardMapper.selectCard" column = "card_id"
        fetchType = "lazy">
        </association>
    </resultMap>
    
    
    <select id = "selectCardlazy" resultMap = "cc">
        select last_name,card_id
        from employees
        where employee_id = #{empId}
    </select>


    //子查詢的方法定義
    Card selectCard(@Param("id")int id);



    //子查詢的XML
    <resultMap type = "com.easyup.model.Card" id = "aa">
        <result column = "id" property = "Id"/>
        <result column = "card_num" property = "num"/>
    </resultMap>

    <select id = "selectCard" resultMap = "aa">
        select id,card_num
        from card
        where id = #{id}
    </select>


        //調(diào)用方法
        Employees selectCardlazy = emp.selectCardlazy(104);
//      System.out.println(selectCardlazy.getLastName());
        System.out.println(selectCardlazy.getCard().getNum());

懶加載：一對(duì)多

//懶加載：一對(duì)多
//注意事項(xiàng)：①department類里面要有employees泛型的List村视；②collection里面的property要寫empList；③子查詢的篩選條件要寫與主查詢相關(guān)聯(lián)的字段棚亩；④log文件要放在src的目錄下

//從department查員工表蓖议，用懶加載
Departments selectFromDepToEmp(@Param("depId")int depId);


<resultMap type = "com.easyup.model.Departments" id = "Deplazy">
<result column = "department_name" property = "departmentName"/>
<result column = "department_id" property = "departmentId"/>
<collection property="empList" ofType = "com.easyup.model.Employees"
select = "com.easyup.mapper.EmployeesMapper.selectFromDepToEmp" column = "department_id"
fetchType = "lazy"
></collection>
</resultMap>

<select id = "selectFromDepToEmp" resultMap = "Deplazy">
    select department_name, department_id
    from departments
    where department_id = #{depId}
</select>


//從部門表找員工表的懶加載
List<Employees> selectFromDepToEmp(@Param("depId")int depId);


<resultMap type = "com.easyup.model.Employees" id = "hh">
    <result column = "last_name" property = "lastName"/>
</resultMap>

<select id = "selectFromDepToEmp" resultMap = "hh">
    select last_name
    from employees
    where department_id = #{depId}
</select>


    Departments selectFromDepToEmp = dep.selectFromDepToEmp(60);
    System.out.println(selectFromDepToEmp.getDepartmentName());
    
    for(Employees employees : selectFromDepToEmp.getEmpList()) {
        System.out.println(employees.getLastName());
    }

懶加載：一對(duì)多對(duì)一

//根據(jù)job_id可以找到多個(gè)員工
//接口中的方法定義
List<Jobs> selectJobsEmpLazy(@Param("jobId")String jobId);

//XML的寫法
<resultMap type = "com.easyup.model.Jobs" id = "ff">
    <result column = "job_id" property = "jobId"/>
    <result column = "job_title" property = "jobTitle"/>
    <collection property = "empList" ofType = "com.easyup.model.Employees"
        select = "com.easyup.mapper.EmployeesMapper.selectFromJobsToEmployees" column = "job_id"
        fetchType = "lazy">
    </collection>
</resultMap>

<select id = "selectJobsEmpLazy" resultMap = "ff">
    select job_id, job_title
    from jobs
    where job_id = #{jobId}
</select>



//從Jobs表找員工表的懶加載
//方法的定義
List<Employees> selectFromJobsToEmployees(@Param("JobId")String jobId);
//XML的寫法
<resultMap type = "com.easyup.model.Employees" id = "gg">
    <result column = "last_name" property = "lastName"/>
    <result column = "salary" property = "salary"/>
    <result column = "employee_id" property = "employee_id"/>
    <association property = "jobGrades" javaType = "com.easyup.model.JobGrades"
    select = "com.easyup.mapper.JobGradesMapper.selectSalaryLevel" column = "salary"
    fetchType = "lazy">
    </association>
</resultMap>

<select id = "selectFromJobsToEmployees" resultMap = "gg">
    select last_name, salary
    from employees
    where job_id = #{jobId}
</select>


//根據(jù)查到的員工的工資，去工資登記表里面查對(duì)應(yīng)的工資等級(jí)
//方法的定義
JobGrades selectSalaryLevel(@Param("salary")int salary);

//XML的寫法
<resultMap type = "com.easyup.model.JobGrades" id = "kk">
    <result column = "grade_level" property = "gradeLevel"/>
</resultMap>

<select id = "selectSalaryLevel" resultMap = "kk">
    select grade_level
    from job_grades
    where #{salary} between lowest_sal and highest_sal
</select>


    //main方法的寫法
    //用jobs查員工表一對(duì)多
    List<Jobs> selectJobsEmpLazy = job.selectJobsEmpLazy("IT_PROG");
    for (Jobs jobs : selectJobsEmpLazy) {
        System.out.println(jobs.getJobId() + "  " + jobs.getJobTitle());
        for (Employees ee : jobs.getEmpList()) {
            System.out.println(ee.getLastName() + "  " + ee.getSalary());
            System.out.println(ee.getJobGrades().getGradeLevel());
        }
    }

模糊查詢

    //⑧模糊查詢
    //（1）老師教的第一種
    List<Employees> selectLike(String name);
    //（2）我們的第二種
    List<Employees> selectLikeName(String name);


    <!-- //⑧模糊查詢-->
    <!-- //（1）老師教的第一種 -->
    <select id = "selectLike" parameterType = "java.lang.String" resultMap = "empMap">
        select last_name
        from employees
        where last_name like concat('%', #{name}, '%');
    </select>
    
    <!-- //（2）我們的第二種 -->
    <select id = "selectLikeName" parameterType = "java.lang.String" resultMap = "empMap">
        select <include refid = "empSql"></include>
        from employees
        where last_name like #{lastName};
    </select>


//      List<Employees> selectLike = emp.selectLike("Er");
//      for (Employees employees : selectLike) {
//          System.out.println(employees);
//      }
        
//      String name = "sti";
//      List<Employees> selectLikeName = emp.selectLikeName("%" + name + "%");
//      for (Employees employees : selectLikeName) {
//          System.out.println(employees);

取出一個(gè)表中一個(gè)字段所有的數(shù)據(jù)要用List來接

//②取出一個(gè)表中一個(gè)字段所有的數(shù)據(jù)要用List來接
    List<Employees> selectAll();


<!-- //②取出一個(gè)表中一個(gè)字段所有的數(shù)據(jù)要用List來接 -->
    <!-- 查詢所有信息的時(shí)候不能寫* -->
    <select id = "selectAll" resultType = "com.easyup.model.Employees">
        select last_name from employees;
    </select>


//查看一個(gè)字段的所有信息
//      List<Employees> list = emp.selectAll();
//      
//      for (Employees employees : list) {
//          System.out.println(employees.getLast_name());
//      }

增刪改

    //①增刪改的返回值l類型都是int
    int add();
    int delete();
    int update();


    <!-- id表示和哪一個(gè)方法進(jìn)行聯(lián)系 -->
    <!-- //①增刪改的返回值都是int -->
    <insert id = "add">
        insert into employees (last_name)
        values ('小明22222222')
    </insert>
    
    <delete id = "delete">
        delete from employees where employee_id = 152;
    </delete>
    
    <update id = "update">
        update employees set last_name = '大吉哥' where employee_id = 152;
    </update>


//調(diào)用接口里面的方法讥蟆，并接取返回值
//      int i = emp.add();
//      int j = emp.update();
//      int k = emp.delete();

注解查詢：查詢（一個(gè)參數(shù)，多個(gè)參數(shù)均可）

//注解查詢：查詢一個(gè)字段
@Select("select id, card_num from card where id = #{id}")
@Results({
    @Result(column = "card_num", property = "num")
})
Card selectCardId(@Param("id")int id);


    //查詢
//      Card selectCardId = car.selectCardId(1);
//      System.out.println(selectCardId.getNum());


//注解查詢：查詢多個(gè)字段
@Select("select employee_id, last_name, salary, year, job_id from employees where employee_id = #{employee_id}")
@Results({
    @Result(column = "employee_id", property = "employee_id"),
    @Result(column = "last_name", property = "lastName"),
    @Result(column = "salary", property = "salary"),
    @Result(column = "year", property = "year"),
    @Result(column = "job_id", property = "job_id")
})
Employees selectemployees(@Param("employee_id") int employee_id);


//      Employees selectemployees = emp.selectemployees(104);
//      System.out.println(selectemployees);

注解查詢：多個(gè)參數(shù)纺阔，一半注解瘸彤，一半XML

//注解查詢：多個(gè)參數(shù)，一半注解笛钝，一半XML
int insertEmp1(@Param("salary") int salary, @Param("lastName")String last_name);


<insert id = "insertEmp1">
        insert into employees
        (last_name, salary) values (#{lastName}, #{salary})
</insert>


//      int insertEmp1 = emp.insertEmp1(80000, "小花1111111111");
//      System.out.println(insertEmp1);

注解查詢：增刪改（無參）

    //注解查詢
    @Insert("insert into card (card_num) values (211302)")
    int insertCard();
    
    @Delete("delete from card where card_num = 211302")
    int deleteCard();
    
    @Update("update card set card_num = 777 where id = 3")
    int updateCard();


        //注解查詢：添加數(shù)據(jù)
//      int insertCard = car.insertCard();
//      System.out.println(insertCard);
        
        //注解查詢：刪除數(shù)據(jù)
//      int deleteCard = car.deleteCard();
//      System.out.println(deleteCard);
        
        //注解查詢：更新數(shù)據(jù)
//      int updateCard = car.updateCard();
//      System.out.println(updateCard);

注解查詢：增刪改：多個(gè)參數(shù)

    //多個(gè)參數(shù)的寫法：
    //更新
    @Update("update card set card_num = #{num} where id = #{id}")
    int updateCard2(@Param("id") int id, @Param("num") int num);
    //刪除
    @Delete("delete from card where id = #{id} and card_num = num")
    int deleteCard2(@Param("id") int id, @Param("num") int num);
    //添加
    @Insert("insert into card (id, card_num) values (#{id}, #{num})")
    int insertCard3(@Param("id") int id, @Param("num") int num);


        //多個(gè)參數(shù)
        //更新
//      int updateCard2 = car.updateCard2(1, 1111111111);
//      System.out.println(updateCard2);

注解查詢：增刪改：使用對(duì)象動(dòng)態(tài)傳參

    //動(dòng)態(tài)傳參：使用對(duì)象
    //更新
    @Update("update card set card_num = #{num} where id = #{id}")
    int updateCard1(Card card);
    //刪除
    @Delete("delete from card where id = #{id}")
    int deleteCard1(Card card);
    //添加
    @Insert("insert into card (card_num) values (#{num})")
    int insertCard1(Card card);


        //注解查詢——?jiǎng)討B(tài)傳參：更新數(shù)據(jù)
//      Card card = new Card();
//      card.setId(1);
//      card.setNum(9);
//      int updateCard1 = car.updateCard1(card);
//      System.out.println(updateCard1);
        
        //注解查詢——?jiǎng)討B(tài)傳參：刪除數(shù)據(jù)
//      Card card = new Card();
//      card.setId(3);
//      int deleteCard1 = car.deleteCard1(card);
//      System.out.println(deleteCard1);
        
        //注解查詢——?jiǎng)討B(tài)查詢：添加數(shù)據(jù)
//      Card card = new Card();
//      card.setNum(1203);
//      int insertCard1 = car.insertCard1(card);
//      System.out.println(insertCard1);

存儲(chǔ)過程

INOUT參數(shù)的使用以及PLSQL

-- 傳入一個(gè)參數(shù)质况，如果大于3則返回100，否則返回500
-- 用戶變量作用于當(dāng)前會(huì)話玻靡，局部變量作用于begin和end之間
-- INOUT的使用
delimiter $$
create procedure my_pro8(INOUT in_param varchar(20))
begin
    declare param varchar(20);
    if in_param > 3
    then set param := 100;
    else set param := 500;
    end if;
    
    set in_param := param;

end $$
delimiter ;

set @param = 3;
call my_pro8(@param);

select @param;



-- PLSQL while
-- PLSQL：過程語言结榄，在存儲(chǔ)過程中使用的語言，也就是和Java一樣有循環(huán)囤捻、分支這些語句臼朗，但是很少用到

delimiter $$
create procedure my_pro11_while()
begin
    declare i int;
    set i := 0;
    
    while i < 10 do
    
    insert into boss (name) values (i);
        set i = i + 1;
        end while;


end $$
delimiter ;

call my_pro11_while();

select *
from boss;

既有輸入又有輸出參數(shù)的存儲(chǔ)過程

-- 輸出模式

delimiter $$
create procedure my_por2( IN in_param  varchar(30),
                 OUT out_param varchar(30))
-- 存儲(chǔ)過程體
begin
-- 查詢員工表中id為103號(hào)員工的信息
select last_name into out_param
from employees
where employee_id = in_param;
end $$
delimiter ;


set @param := null;
call my_por2(103, @param);

select @param;

有多個(gè)輸出參數(shù)的存儲(chǔ)過程

-- 如果要返回一行多列的數(shù)據(jù) 
delimiter $$
create procedure my_por5( IN in_param  varchar(30),
              OUT out_param varchar(30),
                          OUT out_param1 varchar(30),
                          OUT out_param2 varchar(30))

begin
select d.department_name, d.department_id, d.location_id   into out_param, out_param1, out_param2
from employees as e inner join departments as d
on e.department_id = d.department_id
and e.last_name = in_param;
end $$
delimiter ;

set @my_out1 := null;
set @my_out2 := null;
set @my_out3 := null;
call my_por5('Hunold', @my_out1, @my_out2, @my_out3);

select @my_out1, @my_out2,@my_out3;

只有輸入?yún)?shù)的存儲(chǔ)過程

delimiter $$
create procedure my_por1( IN in_param  varchar(30))
-- 存儲(chǔ)過程體
begin
-- 查詢員工表中id為103號(hào)員工的信息
select *
from employees
where employee_id = in_param;
end $$
delimiter ;

-- 調(diào)用存儲(chǔ)過程
call my_por1(103);

子查詢

from后子查詢

-- from后子查詢：子句當(dāng)成一個(gè)表來使用，要有字段名字和表名
-- 一般將分組查詢的表格作為from后子查詢的子表
-- select后子查詢，兩表之間的關(guān)聯(lián)寫在子句里面视哑，from后子查詢兩表之間的關(guān)聯(lián)寫在外面

-- 每個(gè)部門的平均工資的工資等級(jí)
select *
from (select avg(salary) as ag
from employees
group by department_id) as ag_emp inner join job_grades as j
on ag_emp.ag between j.lowest_sal and j.highest_sal;


-- 查詢員工工資比本部門平均工資高（不包含臨界值）的員工的員工號(hào)绣否，姓名和工資
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary > ag_dep.ag;

select后子查詢

-- select后子查詢：子句當(dāng)成一個(gè)字段來使用

-- 利用部門表查詢每個(gè)部門的員工個(gè)數(shù)、部門編號(hào)挡毅、部門名稱
select d.department_id, d.department_name, 
(select count(employee_id)
from employees
where d.department_id = employees.department_id)
from departments as d;

標(biāo)量子查詢

-- 標(biāo)量子查詢：子查詢的結(jié)果是一個(gè)標(biāo)量
-- 查詢工資比105號(hào)員工高的所有人信息

select *
from employees
where salary > (select salary
from employees
where employee_id = 105);

行子查詢

-- 行子查詢：子句返回結(jié)果集是一行多列
-- 行子查詢可以使用等號(hào)蒜撮，但是不可以使用大于、大于等于跪呈、小于段磨、小于等于這類符號(hào)
-- 查詢與103號(hào)年齡和工資都相同的人的信息alter

select *
from employees
where (year, salary) =
(select year, salary
from employees
where employee_id = 1);

列子查詢

-- 列子查詢：子查詢的結(jié)果是一列多行
-- 查詢比103號(hào)或104號(hào)員工高的員工的信息

select *
from employees
where salary > any(select salary
from employees
where employee_id in(103,104));

相關(guān)子查詢

-- 相關(guān)子查詢：子句有值的話返回true，主句才會(huì)進(jìn)行比對(duì)和顯示耗绿。如果為空的話就不會(huì)顯示苹支。
-- 相關(guān)子查詢的子表寫在where后面
-- 相關(guān)子查詢的效率特別高，輸出只有兩個(gè)布爾值缭乘，ture和false

-- 查詢部門表是否存在年齡大于21歲的員工沐序，如果存在則輸出該部門的信息。
select *
from departments
where exists (
select *
from employees
where employees.department_id = departments.department_id
and year > 21
);

其它

不等值內(nèi)連接

-- 不等值內(nèi)連接
-- 查詢員工所對(duì)應(yīng)的工資等級(jí)alter
select j.grade_level,e.last_name
from employees as e inner join job_grades as j
on e.salary between j.lowest_sal and j.highest_sal
and e.last_name = 'Hunold';

將數(shù)據(jù)按照第一個(gè)排序堕绩，如果第一個(gè)相同的情況下按照第二個(gè)排序

select * 
from rank 
order by times asc, time asc;

游標(biāo)的使用

-- 游標(biāo)

DELIMITER $$
create PROCEDURE my_pro12()
begin
-- 聲明一個(gè)標(biāo)志done策幼， 用來判斷游標(biāo)是否遍歷完
DECLARE done INT DEFAULT 0;
-- 聲明一個(gè)變量，用來存放從游標(biāo)中提取的數(shù)據(jù)
-- 特別注意這里的名字不能與由游標(biāo)中使用的列明相同奴紧，
-- 否則得到的數(shù)據(jù)都是NULL
DECLARE lastName varchar(30) DEFAULT NULL;
DECLARE salaryParam int DEFAULT NULL;
-- 聲明游標(biāo)對(duì)應(yīng)的 SQL 語句
DECLARE cur CURSOR FOR
select last_name, salary from employees;
-- 在游標(biāo)循環(huán)到最后會(huì)將 done 設(shè)置為 1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 執(zhí)行查詢
open cur;
-- 遍歷游標(biāo)每一行
REPEAT
-- 把一行的信息存放在對(duì)應(yīng)的變量中
FETCH cur INTO lastName, salaryParam;
if not done then
-- 這里就可以使用 lastName特姐， salaryParam 對(duì)應(yīng)的信息了
select lastName, salaryParam;
end if;
UNTIL done END REPEAT;
CLOSE cur;
end $$
DELIMITER ;

call my_pro12();
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市黍氮，隨后出現(xiàn)的幾起案子唐含，更是在濱河造成了極大的恐慌，老刑警劉巖沫浆，帶你破解...
    沈念sama閱讀 218,386評(píng)論 6贊 506
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件捷枯，死亡現(xiàn)場離奇詭異，居然都是意外死亡专执，警方通過查閱死者的電腦和手機(jī)淮捆，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,142評(píng)論 3贊 394
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來本股，“玉大人攀痊，你說我怎么就攤上這事≈粝裕” “怎么了苟径？”我有些...
    開封第一講書人閱讀 164,704評(píng)論 0贊 353
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長躬审。 經(jīng)常有香客問我棘街，道長蟆盐，這世上最難降的妖魔是什么？ 我笑而不...
    開封第一講書人閱讀 58,702評(píng)論 1贊 294
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任蹬碧，我火速辦了婚禮舱禽，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘恩沽。我一直安慰自己誊稚，他們只是感情好，可當(dāng)我...
    茶點(diǎn)故事閱讀 67,716評(píng)論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布罗心。 她就那樣靜靜地躺著里伯，像睡著了一般。 火紅的嫁衣襯著肌膚如雪渤闷。 梳的紋絲不亂的頭發(fā)上疾瓮，一...
    開封第一講書人閱讀 51,573評(píng)論 1贊 305
  • 城市分裂傳說
    那天，我揣著相機(jī)與錄音飒箭，去河邊找鬼狼电。 笑死，一個(gè)胖子當(dāng)著我的面吹牛弦蹂，可吹牛的內(nèi)容都是我干的肩碟。 我是一名探鬼主播，決...
    沈念sama閱讀 40,314評(píng)論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼凸椿，長吁一口氣：“原來是場噩夢(mèng)啊……” “哼削祈！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起脑漫，我...
    開封第一講書人閱讀 39,230評(píng)論 0贊 276
  • 萬榮殺人案實(shí)錄
    序言：老撾萬榮一對(duì)情侶失蹤髓抑，失蹤者是張志新（化名）和其女友劉穎，沒想到半個(gè)月后优幸，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體吨拍，經(jīng)...
    沈念sama閱讀 45,680評(píng)論 1贊 314
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,873評(píng)論 3贊 336
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年网杆，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了密末。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 39,991評(píng)論 1贊 348
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡跛璧，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出新啼，到底是詐尸還是另有隱情追城，我是刑警寧澤，帶...
    沈念sama閱讀 35,706評(píng)論 5贊 346
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布燥撞，位于F島的核電站座柱，受9級(jí)特大地震影響迷帜，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜色洞，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,329評(píng)論 3贊 330
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一戏锹、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧火诸，春花似錦锦针、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,910評(píng)論 0贊 22
  • 一樁弒父案奈搜，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至盯荤，卻和暖如春馋吗，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背秋秤。 一陣腳步聲響...
    開封第一講書人閱讀 33,038評(píng)論 1贊 270
  • 情欲美人皮
    我被黑心中介騙來泰國打工宏粤， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人灼卢。 一個(gè)月前我還...
    沈念sama閱讀 48,158評(píng)論 3贊 370
  • 代替公主和親
    正文 我出身青樓绍哎，卻偏偏與公主長得像，于是被迫代替她去往敵國和親芥玉。 傳聞我的和親對(duì)象是個(gè)殘疾皇子蛇摸，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,941評(píng)論 2贊 355