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

-- 插入語句的一些補(bǔ)充
create table emp_copy4
AS
select  * from emp where sal=null
-- 子查詢插入多條數(shù)據(jù)
INSERT into emp_copy4
select * from emp where deptno=20

INSERT into emp_copy4(empno,neme)
select empno,ename from emp where deptno=20

索引與視圖

 索引 index -- 優(yōu)點(diǎn)：加快查詢速度总寒；
--                           缺點(diǎn)：占內(nèi)存，降低了增刪改速度（因?yàn)樗饕硇枰剑?-- 主鍵自帶索引
-- 經(jīng)常需要作為條件的列最好建索引
create table test4(
id int(20) auto_increment,
name VARCHAR(20) default '' not NULL,
primary key (id),
INDEX(name)
)

-- 視圖 view  命名的查詢
-- 視圖不存數(shù)據(jù)理肺，存的是查詢摄闸，視圖是一個(gè)虛表
-- with check option-->視圖可以查詢到的數(shù)據(jù)，才能進(jìn)行增刪改
CREATE view emp_10
AS
select * from emp where  deptno=10

連接數(shù)據(jù)庫

    public static void main(String[] args) {
        // 將聲明放在外面妹萨，以便finally中關(guān)閉
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            // 1.加載數(shù)據(jù)庫驅(qū)動(dòng)程序贪薪，（需要把驅(qū)動(dòng)加載到方法區(qū)）
            Class.forName("com.mysql.jdbc.Driver");
            //2.利用驅(qū)動(dòng)管理器獲取數(shù)據(jù)庫連接
            //localhost=127.0.0.1 本地，如果需要連接他人數(shù)據(jù)眠副，修改成對方的id
            // 3306 端口號  mysql默認(rèn)端口號
            //java2demo  數(shù)據(jù)庫名稱
            //useUnicode=true&characterEncoding=utf8-->支持中文
            String url="jdbc:mysql://localhost:3306/java2demo?useUnicode=true&characterEncoding=utf8";
            conn = DriverManager.getConnection(url, "root", "root");
            //3.獲取SQL語句
            String sql="select*from dept";
            ps=conn.prepareStatement(sql);
            //4.執(zhí)行語句等到結(jié)果集
            rs= ps.executeQuery();
            while(rs.next()) {
                //編號
                int deptno=rs.getInt("deptno");
                //部門名稱
                String dname=rs.getString("dname");
                //獲取工作地點(diǎn)
                String loc=rs.getString("loc");
                System.out.println(deptno+","+dname+","+loc);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(conn !=null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if( rs !=null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps !=null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        
        
    }

控制臺輸入画切，與保密

public static void main(String[] args) {
        Scanner scan=new Scanner(System.in);
        System.out.println("請輸入部門號");
        int a=scan.nextInt();
        Connection cnno=null;
        
        //Statement 是 PreparedStatement 的父類接口，只能執(zhí)行靜態(tài)接口
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/java2demo?useUnicode=true&characterEncoding=utf8";
            cnno=DriverManager.getConnection(url,"root","root");
            String sql="select * from emp where deptno = ?";
            ps= cnno.prepareStatement(sql);
            ps.setInt(1, a);
            rs=ps.executeQuery();
            while(rs.next()) {
                int empno = rs.getInt("empno");
                String ename=rs.getString("ename");
                int sal=rs.getInt("sal");
                Date hiredate=rs.getDate("hiredate");
                System.out.println(empno +","+ename+","+sal+","+hiredate);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            if(cnno !=null) {
                try {
                    cnno.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                if(ps !=null) {
                    try {
                        ps.close();
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
                if(rs !=null) {
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
            }
            
        }

    }