내가 보여주고 싶은 결과값

위와 같이 수강편람 테이블이 있다.

맨 우측 열에서 알 수 있듯이 나는 현재 수강인원도 출력하고 싶다.

그러나 과목코드, 강의명, 교사명, 정원은 subject 테이블에 있고, 현재 수강인원은 select count(*)를 해서 attend 테이블에서 가져와야한다. 

 

1. 비조인 select / left outer join

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--내가 보여주고 싶은 결과값: 수강편람(subject) 테이블에서 과목별 현재 수강인원 컬럼(attend 테이블을 이용)도 함께 출력하기
--결국 attend 테이블에서 subno별 count를 구한 컬럼이 붙어있도록 하는 것. (조인/비조인 방법)
--1. 비조인 select
--데이터가 많은 경우 조인을 하면 속도가 걸린다고 함
select s.subno, s.subname, s.teachername, s.cnt, 
    (select count(a.subno) from attend a where s.subno = a.subno) currentCnt 
        from subject s;
 
--2. 조인 left outer join
--일치하는 레코드가 없을 경우 빈 값으로라도 나온다 
--그냥 join을 쓰면 수강신청 안된 나머지 강의들은 보이지 않음
select s.subno, s.subname, s.teachername, s.cnt, count(a.subno) currentCnt
from subject s
left outer join attend a
on s.subno = a.subno group by s.subno, s.subname, s.teachername, s.cnt;
 cs

 

 

2. 결과값을 페이징 처리하기

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--3. 결과 값을 페이징 처리하기
--(1) 원래 페이지 구할때 sql문
select * from 
    (select aa.*, rownum rn from
        (select * from subject order by subname) aa
            where rownum<=5) where rn>=1;
 
--(2) 비조인 select 페이징 처리
select * from 
 (select s.subno, s.subname, s.teachername, s.cnt, (select count(a.subno) from attend a where s.subno = a.subno) currentCnt, rownum rn from
        (select * from subject s order by subname) s
            where rownum<=5) where rn>=1;
 
            
--(3) left outer join을 사용해서 페이징 구하기
select * from
    (select aa.*, rownum rn from 
        (select s.subno, s.subname, s.teachername, s.cnt, count(a.subno) currentCnt
        from subject s
        left outer join attend a
        on s.subno = a.subno group by s.subno, s.subname, s.teachername, s.cnt) aa
            where rownum<=5) where rn>=1;
 cs

 

저작자표시 비영리 변경금지

'Learning > SQL' 카테고리의 다른 글

namecard 완성하기  (0) 2020.06.24
PreparedStatement  (0) 2020.06.23
JDBC SELECT  (0) 2020.06.23
JDBC INSERT  (0) 2020.06.23
JDBC Test  (0) 2020.06.23
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
package com.oracle.namecard;
//테이블 NAMECARD의 열들을 멤버변수로 하는 클래스 Namecard
public class Namecard {
    private int no;
    private String name;
    private String mobile;
    private String email;
    private String company;
    
    public Namecard() {}
    
    public Namecard(String name, String mobile, 
            String email, String company) {
        this.name = name;
        this.mobile = mobile;
        this.email = email;
        this.company = company;
    }
    //필드변수 초기화 생성자 (no 있음) => dao의 insert메소드 참고
    public Namecard(int no, String name, 
            String mobile, String email, String company) {
        this.no = no;
        this.name = name;
        this.mobile = mobile;
        this.email = email;
        this.company = company;
    }
    // 멤버변수들을 getter/setter 세팅
    public int getNo() {
        return no;
    }
    public void setNo(int no) {
        this.no = no;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getMobile() {
        return mobile;
    }
    public void setMobile(String mobile) {
        this.mobile = mobile;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getCompany() {
        return company;
    }
    public void setCompany(String company) {
        this.company = company;
    }
    //toString()메소드는 클래스 변수들을 일렬로 문자열로 만들어 리턴
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append("[NO: ");
        sb.append(no);
        sb.append("] ");
        sb.append(name);
        sb.append(" ,Mobile: ");
        sb.append(mobile);
        sb.append(" ,Email: ");
        sb.append(email);
        sb.append(" ,Company: ");
        sb.append(company);
     // 멤버변수의 값들을 모아서 하나의 문자열로 리턴
        return sb.toString();
    }
    
}
 
 cs

 

 

 

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
package com.oracle.namecard;
 
import java.sql.*;
import java.util.ArrayList;
 
public class NamecardDao {
    //DAO: data access object=>데이터에 접근하는 객체
    
    static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";  
    static final String USER = "scott";
    static final String PASSWORD = "1234";
    
    //1. 드라이버 로드를 퍼블릭 메소드로 정의
    public NamecardDao() {
        try { // 오라클 DB 드라이버 로드
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    private Connection getConnection() throws SQLException {
        // DB 연결을 메소드로 만들기
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
            //먼저 ResultSet 이 널값이 아니면 종료, 다음 Statement 종료, 마지막으로 커넥션 종료
    private void close(ResultSet rs, PreparedStatement pstmt, Connection con) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    // 입력 메소드
    public void insert(Namecard card) {
        Connection con = null;
        PreparedStatement pstmt = null;
        
        String sql = "INSERT INTO namecard VALUES (CARD_NO.NEXTVAL, ?, ?, ?, ?)";
 
        try {
            //DB 연결 메소드
            con = getConnection();
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, card.getName());
            pstmt.setString(2, card.getMobile());
            pstmt.setString(3, card.getEmail());
            pstmt.setString(4, card.getCompany());
            pstmt.executeUpdate();
            con.commit(); //데이터 저장
            System.out.println("입력 완료!");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println(sql);
        } finally {
            close(null, pstmt, con);
        }
    }
    
    //번호 no 행 삭제    
    public void delete(int no) {
       //작성하기
        Connection con=null;
        PreparedStatement pstmt=null;
        
        String sql="DELETE FROM namecard WHERE NO=?";
        
        try {
            con=getConnection(); 
            pstmt=con.prepareStatement(sql); //sql준비
            pstmt.setInt(1,no);
            pstmt.executeUpdate(); //sql실행
            con.commit(); //데이터저장
            System.out.println("삭제완료!");
        } catch (SQLException e) {
        e.printStackTrace();
        System.out.println(sql);
        } finally {
            close(null,pstmt,con);
        }
    }
    
    //번호 no 행 검색
    public Namecard selectOne(int no) {
       //작성하기
        return null;
    }
    
    //이름으로 찾기
    public ArrayList<Namecard> selectByKeyword(String keyword) {       
        ArrayList<Namecard> matched = new ArrayList<Namecard>();
        // 작성하기  
        return matched;
    }
    
    //Namecard의 모든 행/열을 가져오기 번호순으로 
    public ArrayList<Namecard> selectAll() {
        ArrayList<Namecard> all = new ArrayList<Namecard>();
        // 작성하기
        return all; 
    }        
    
    //수정하기
    public void update(Namecard card) {
        //작성하기
    }
 
}
 
 cs

 

 

 

 

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
package com.oracle.namecard;
 
import java.util.ArrayList;
 
public class Test {
 
    public static void main(String[] args) {
        NamecardDao dao = new NamecardDao();
        //1.insert(Namecard) test: 입력하기
//        Namecard hong = new Namecard("홍길동","010-1234-1234", "hk@naver.com", "부산IT");
//        dao.insert(hong); //실행후 sql developer 확인
//        no 삭제하기
        dao.delete(2);
/*
        //2.selectAll() test: 다 가져와서 리스트에 입력한후 콘솔에 출력
        ArrayList<Namecard> list = dao.selectAll();
        int size = list.size();
        //System.out.println(size);
        for (int i = 0; i < size; i++) {
            Namecard namecard = list.get(i);
            System.out.println(namecard);
        }
 
 
 
        //3.selectByKeyword(String) test: 이름으로 찾기
        ArrayList<Namecard> matched = dao.selectByKeyword("홍길동");
        int length = matched.size();
        System.out.println(length + "명 찾음.");
        for (int i = 0; i < length; i++) {
            Namecard namecard = matched.get(i);
            System.out.println(namecard);
        }
 
 
 
        //4.selectOne(int) test : 번호로 찾기
        Namecard card = dao.selectOne(5);
        System.out.println("5번 찾음");
        System.out.println(card);
 
 
 
        //5.delete(int) test : 번호로 삭제하기
        dao.delete(4);
        ArrayList<Namecard> all = dao.selectAll();
        int cardNum = all.size();
        for (int i = 0; i < cardNum; i++) {
            Namecard namecard = all.get(i);
            System.out.println(namecard);
        }
 
 
      
        //6. update(Namecard) test :  card의 no번호와 같은 NAMECARD 행을  이름, 전화번호, 이메일, 회사 업데이트(수정하기)
        Namecard card = dao.selectOne(5);
        System.out.println(card);
        card.setEmail("alison@ggmail.org");
        card.setMobile("222-2222-2222");
        dao.update(card);
        System.out.println(card);
*/
    }
}
 
 cs
저작자표시 비영리 변경금지

'Learning > SQL' 카테고리의 다른 글

수강편람 테이블에서 과목별 현재 수강인원도 함께 출력하기  (0) 2021.03.08
PreparedStatement  (0) 2020.06.23
JDBC SELECT  (0) 2020.06.23
JDBC INSERT  (0) 2020.06.23
JDBC Test  (0) 2020.06.23
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
package com.oracle.namecardTest;
 
import java.sql.*; //자바 sql 라이브러리 
 
public class PrepareSTMT {
 
    public static void main(String[] args) {
        //(oracle) jdbc:oracle:thin:@localhost:1521:xe
        //(mySQL) jdbc:mysql://localhost:3306/db이름
//        미리 입력할 오라클 주소, 계정, 비밀번호를 변수로 저장
        String DB_URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
        String DB_USER = "scott";
        String DB_PASSWORD = "1234";
//      conn은 오라클 연결을 위한 객체
        Connection conn = null;
//      stmt는 sql문을 실행하기 위한 객체
        PreparedStatement pstmt = null;
//      rs는 sql문 실행 후 결과값을 받아오는 객체
        //ResultSet rs = null;
 
        String query = "UPDATE NAMECARD SET company = ? WHERE NO = ?";
        
        try {
            //1. 오라클 드라이버 로딩   ( ojdbc6_g.jar )            
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e ) { //클래스를 못찾았을때 (드라이버 없을때 )
            e.printStackTrace();
        }
 
        try { //DB 연결 부분은 try-catch 문으로 예외 발생시 처리
            //2. DB 연결
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            //3. SQL 문 준비
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, "daum net"); //첫번째 파라메타 (?)
            pstmt.setInt(2, 2);                //두번재 파라메타 (?)
            //4. SQL 쿼리 실행
            pstmt.executeUpdate();
            
            conn.commit(); //영구저장
            
            System.out.println("수정완료!");
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();        //State문 닫기
                conn.close();
            } catch (SQLException e) {}
        }
    }
 
}
 
cs
저작자표시 비영리 변경금지

'Learning > SQL' 카테고리의 다른 글

수강편람 테이블에서 과목별 현재 수강인원도 함께 출력하기  (0) 2021.03.08
namecard 완성하기  (0) 2020.06.24
JDBC SELECT  (0) 2020.06.23
JDBC INSERT  (0) 2020.06.23
JDBC Test  (0) 2020.06.23

<JDBC SELECT>

 

 

 

1
2
3
SELECT * FROM NAMECARD
ORDER BY NO;
commit;
 cs

 

 

 

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
package com.oracle.namecardTest;
 
import java.sql.*; //자바 sql 라이브러리 
 
public class Select {
 
    public static void main(String[] args) {
        //(oracle) jdbc:oracle:thin:@localhost:1521:xe
        //(mySQL) jdbc:mysql://localhost:3306/db이름
//        미리 입력할 오라클 주소, 계정, 비밀번호를 변수로 저장
        String DB_URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
        String DB_USER = "scott";
        String DB_PASSWORD = "1234";
//      conn은 오라클 연결을 위한 객체
        Connection conn = null;
//      stmt는 sql문을 실행하기 위한 객체
        Statement stmt = null;
//      rs는 sql문 실행 후 결과값을 받아오는 객체
//      select절이 실행되었을때 출력되는 값이 있기 때문에 rs객체 필요
        ResultSet rs = null; 
 
        String query = "SELECT * FROM NAMECARD ORDER BY NO";
        
        
        try {
            //1. 오라클 드라이버 로딩   ( ojdbc6_g.jar )            
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e ) { //클래스를 못찾았을때 (드라이버 없을때 )
            e.printStackTrace();
        }
 
        try { //DB 연결 부분은 try-catch 문으로 예외 발생시 처리
            //2. DB 연결
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            //3. SQL 문 준비
            stmt = conn.createStatement();
            //4. SQL 쿼리 실행 하고 rs에 테이블데이터를 받아옴
            rs=stmt.executeQuery(query);
            
            while(rs.next()) {
                int no = rs.getInt("no");
                String name = rs.getString("name");
                String mobile = rs.getString("mobile");
                String email = rs.getString("email");
                String company = rs.getString("company");
                System.out.println(no+"|"+name+"|"+mobile+"|"+
                        email+"|"+company);
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                stmt.close();        //State문 닫기
                conn.close();
            } catch (SQLException e) {}
        }
    }
 
}
 
저작자표시 비영리 변경금지

'Learning > SQL' 카테고리의 다른 글

namecard 완성하기  (0) 2020.06.24
PreparedStatement  (0) 2020.06.23
JDBC INSERT  (0) 2020.06.23
JDBC Test  (0) 2020.06.23
DCL: 데이터 제어어  (0) 2020.06.23

<JDBC INSERT>

 

  • SCOTT 계정에서 NAMECARD라는 명함 테이플을 만들기

 

DROP TABLE NAMECARD;

 

CREATE TABLE NAMECARD(

    NO      NUMBER CONSTRAINT PK_NAMECARD PRIMARY KEY,

    NAME    VARCHAR2(20) NOT NULL,

    MOBILE  VARCHAR2(20) NOT NULL,

    EMAIL   VARCHAR2(40),

    COMPANY VARCHAR2(60)

);

 

 

 

  • 시퀀스 만들기

 

DROP SEQUENCE CARD_NO;

 

CREATE SEQUENCE CARD_NO

INCREMENT BY 1

START WITH 1;

 

 

 

 

  • NAMECARD 테이블에 값 입력

 

INSERT INTO NAMECARD VALUES (

    CARD_NO.NEXTVAL,

    'LALA',

    '010-1234-5678',

    'LALA@naver.com',

    'Naver Com'

);

 

SELECT * FROM NAMECARD;

 

 

 

 

 

  • 자바 실행 파일로 INSERT 입력

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
package com.oracle.namecardTest;
 
import java.sql.*; //자바 sql 라이브러리 
 
public class Insert {
 
    public static void main(String[] args) {
        //(oracle) jdbc:oracle:thin:@localhost:1521:xe
        //(mySQL) jdbc:mysql://localhost:3306/db이름
//        미리 입력할 오라클 주소, 계정, 비밀번호를 변수로 저장
        String DB_URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
        String DB_USER = "scott";
        String DB_PASSWORD = "1234";
//      conn은 오라클 연결을 위한 객체
        Connection conn = null;
//      stmt는 sql문을 실행하기 위한 객체
        Statement stmt = null;
//      rs는 sql문 실행 후 결과값을 받아오는 객체
        //ResultSet rs = null;
 
        String query = "INSERT INTO NAMECARD VALUES (" + 
                "    CARD_NO.NEXTVAL," + 
                "    'LALA'," + 
                "    '010-1234-5678'," + 
                "    'LALA@naver.com'," + 
                "    'Naver Com'" + 
                ")" ;
        
        try {
            //1. 오라클 드라이버 로딩   ( ojdbc6_g.jar )            
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e ) { //클래스를 못찾았을때 (드라이버 없을때 )
            e.printStackTrace();
        }
 
        try { //DB 연결 부분은 try-catch 문으로 예외 발생시 처리
            //2. DB 연결
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            //3. SQL 문 준비
            stmt = conn.createStatement();
            //4. SQL 쿼리 실행
            stmt.executeUpdate(query);
            
            System.out.println("입력완료!");
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();        //State문 닫기
                conn.close();
            } catch (SQLException e) {}
        }
    }
 
}
 
 cs

 

 

  • SQL developer에서 INSERT문이 실행되었는지 확인

 

1
SELECT * FROM NAMECARD;
 cs

 

 

저작자표시 비영리 변경금지

'Learning > SQL' 카테고리의 다른 글

PreparedStatement  (0) 2020.06.23
JDBC SELECT  (0) 2020.06.23
JDBC Test  (0) 2020.06.23
DCL: 데이터 제어어  (0) 2020.06.23
시퀀스  (0) 2020.06.23

 

  • 자바 웹 애플리케이션 (이클립스)과 오라클을 연결해주는 JDBC (Java DataBase Connectivity) 필요

  • 오라클 JDBC 드라이버를 다운로드 후 C:\Program Files\Java\jre1.8.0.251\jre\lib\ext에 옮겨놓음

  • 이클립스에서 확인

 

 

 

 

  • JDBC 테스트

1. 드라이버 로딩

Class.forName("oracle.jdbc.driver.OracleDriver");

 

2. DB 연결

Connection conn=null;

conn=DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);

 

3. SQL문 준비

stmt=conn.createStatement();

 

4. SQL쿼리 실행하고 결과 받기

stmt.executeUpdate();

ResultSet rs=null;

rs=stmt.executeQuery(query);

 

 

 

 

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
package com.oracle.test;
 
import java.sql.*; //자바 sql 라이브러리 
 
public class GetEmp {
 
    public static void main(String[] args) {
        //(oracle) jdbc:oracle:thin:@localhost:1521:xe
        //(mySQL) jdbc:mysql://localhost:3306/db이름
//        미리 입력할 오라클 주소, 계정, 비밀번호를 변수로 저장
        String DB_URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
        String DB_USER = "scott";
        String DB_PASSWORD = "1234";
//      conn은 오라클 연결을 위한 객체
        Connection conn = null;
//      stmt는 sql문을 실행하기 위한 객체
        Statement stmt = null;
//      rs는 sql문 실행 후 결과값을 받아오는 객체
        ResultSet rs = null;
 
        String query = "SELECT * FROM emp";
        String query2 = "SELECT * FROM emp WHERE EMPNO=7369";
        
        try {
            //1. 오라클 드라이버 로딩   ( ojdbc6_g.jar )            
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e ) { //클래스를 못찾았을때 (드라이버 없을때 )
            e.printStackTrace();
        }
 
        try { //DB 연결 부분은 try-catch 문으로 예외 발생시 처리
            //2. DB 연결
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            //3. SQL 문 준비
            stmt = conn.createStatement();
            //4. SQL 쿼리 실행하고 결과 받기
            rs = stmt.executeQuery(query2);
 
            while (rs.next()) { // 테이블의 한 행씩 실행
                String empno = rs.getString("EMPNO");  // 첫번째 열
                String ename = rs.getString(2);
                String job = rs.getString(3);
                String mgr = rs.getString(4);
                String hiredate = rs.getString(5);
                String sal = rs.getString(6);
                String comm = rs.getString(7);
                String depno = rs.getString(8);
 
                System.out.println(empno + " : " + ename + " : " + job + " : " + mgr + " : " + hiredate + " : " + sal + " : " + comm + " : " + depno); 
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();         //ResultSet (쿼리 결과) 닫기
                stmt.close();        //State문 닫기
                conn.close();
            } catch (SQLException e) {}
        }
    }
 
}
 
 cs

 

 

 

저작자표시 비영리 변경금지

'Learning > SQL' 카테고리의 다른 글

JDBC SELECT  (0) 2020.06.23
JDBC INSERT  (0) 2020.06.23
DCL: 데이터 제어어  (0) 2020.06.23
시퀀스  (0) 2020.06.23
인덱스  (0) 2020.06.22

<DCL: 데이터 제어어>

 

  • DCL: DATA CONTROL LANGUAGE 데이터 제어 언어

  • 데이터의 사용권한을 관리

  • GRANT: 특정 사용자에게 수행 권한을 부여

  • REVOKE: 특정 사용자에게 수행 권한을 회수 및 박탈

 

 

  • 새 유저 만들기

1
CREATE USER John Identified by 1234; 
--새유저 존을 만들고 비밀번호 1234
 cs

 

 

 

HR 계정에서 나오고 Oracle-XE로 연결

 

 

 

데이터베이스 접속 선택 시 접속권한이 없어서 실패함

 

 

 

 

 

  • 접속 권한 주기

 

1
2
GRANT CREATE SESSION TO John; 
--John 유저에게 접속 권한 주기
 cs

 

 

 

 

존 계정으로 접속 성공

 

 

 

 

  • 테이블 생성

 

 

CREATE TABLE t1 (

    id NUMBER PRIMARY KEY

);

 

1
2
3
CREATE TABLE t1 (
    id NUMBER PRIMARY KEY
);
 cs

 

 

테이블을 만들수 있는 권한도 따로 필요함

 

 

 

  • 테이블 생성 권한 부여

 

 

1
GRANT CREATE TABLE TO John;
 cs

 

 

 

존계정에서 테이블이 안만들어짐

 

 

 

 

테이블 스페이스 생성 필요

 

경로복사

 

 

 

 

  • 테이블 스페이스 (테이블을 저장할 수 있는 공간)

1
2
3
4
5
6
CREATE TABLESPACE johnSpace
DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\john.dbf' SIZE 10M
AUTOEXTEND ON NEXT 5M;
--johnSpace라는 테이블 저장공간 생성 (테이블 스페이스)
--파일의 경로 설정, 최초사이즈는 10mbyte
--자동으로 테이블공간이 부족하면 5mbyte씩 추가됨
 cs

 

 

 

 

 

  • 존계정이 이 테이블스페이스를 사용할 수 있도록

1
2
3
4
ALTER USER John DEFAULT TABLESPACE johnSpace;
--디폴트 존스페이스로
ALTER USER John QUOTA UNLIMITED ON johnSpace;
--테이블 스페이스 사용권한
 cs

 



 

 

 

  • 롤(ROLE) 부여

 

1
2
3
GRANT CONNECT, RESOURCE to John;
--Connect 롤은 접속관련 권한들의 모음
--Resource 롤은 여러객체들(테이블, 뷰등)을 생성및 사용 권한
 cs

 

 

 

 

1
2
3
4
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
 
SELECT * FROM t1;
 cs

 

 

 

  • DBA: 관리자 권한

 

 

  • 권한을 제거 및 회수 REVOKE + 권한 + FROM + 유저

 

1
REVOKE CREATE TABLE FROM John;
 cs

 

 

 

 

 

1
2
3
CREATE TABLE t2 (
    id NUMBER PRIMARY KEY
);
 cs

 

John은 더이상 테이블을 만들 수 있는 권한이 없기에 에러 발생

 

 

 

 

  • 롤을 제거 및 회수

1
REVOKE RESOURCE FROM John;
 cs

 

 

 

  • John 계정을 삭제

1
DROP USER John CASCADE;
 cs

 

 

 

 

  • 테이블스페이스 삭제

1
DROP TABLESPACE johnSpace;
 cs

 

저작자표시 비영리 변경금지

'Learning > SQL' 카테고리의 다른 글

JDBC INSERT  (0) 2020.06.23
JDBC Test  (0) 2020.06.23
시퀀스  (0) 2020.06.23
인덱스  (0) 2020.06.22
뷰  (0) 2020.06.22

<시퀀스>

 

  • 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성해주는 오라클 객체

  • 테이블과는 독립적으로 저장, 생성 (테이블을 삭제해도 관련 시퀀스는 삭제되지 않음)

 

  • 시퀀스 생성

1
2
3
4
5
6
7
CREATE SEQUENCE 시퀀스1; --생성
--DROP SEQUENCE 시퀀스1; --삭제
--시퀀스 이름으로 시퀀스 정보출력
SELECT * FROM USER_SEQUENCES
WHERE sequence_name = '시퀀스1';
--MIN_VALUE부터 MAX_VALUE까지 사용 가능
--INCREMENT_BY씩 증가
 cs

 

 

 

1
2
3
4
--시퀀스 테스트 듀얼 테이블에서
SELECT 시퀀스1.NEXTVAL FROM DUAL; --시퀀스 사용.nextval
 
실행하는 만큼 증가
 cs

 

 

 

 

1
2
3
SELECT 시퀀스1.CURRVAL FROM DUAL; 
--시퀀스의 현재값(값을 증가하지 않고 현재값)
 
현재값이 출력됨
 cs

 

 

 

  • 시퀀스 테스트용 테이블 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE 부서_테스트 (
    부서번호 NUMBER PRIMARY KEY,
    부서이름 VARCHAR2(100)
);
--시퀀스 초기화
DROP SEQUENCE 시퀀스1; --삭제
CREATE SEQUENCE 시퀀스1; --생성
 
INSERT INTO 부서_테스트 (부서번호, 부서이름)
VALUES (시퀀스1.NEXTVAL, '영업부');
INSERT INTO 부서_테스트 (부서번호, 부서이름)
VALUES (시퀀스1.NEXTVAL, '개발부');
INSERT INTO 부서_테스트 (부서번호, 부서이름)
VALUES (시퀀스1.NEXTVAL, '회계부');
COMMIT;
SELECT * FROM 부서_테스트;
 cs

 

 

 

 

  • 시퀀스 옵션 사용

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--시퀀스2 생성 - 시작값을 10, 증가값을 20
CREATE SEQUENCE 시퀀스2
START WITH 10 --10번부터 시작
INCREMENT BY 20; --증가값이 20
 
INSERT INTO 부서_테스트 (부서번호, 부서이름)
VALUES (시퀀스2.NEXTVAL, '영업부');
INSERT INTO 부서_테스트 (부서번호, 부서이름)
VALUES (시퀀스2.NEXTVAL, '개발부');
INSERT INTO 부서_테스트 (부서번호, 부서이름)
VALUES (시퀀스2.NEXTVAL, '회계부');
INSERT INTO 부서_테스트 (부서번호, 부서이름)
VALUES (시퀀스2.NEXTVAL, '마케팅부');
INSERT INTO 부서_테스트 (부서번호, 부서이름)
VALUES (시퀀스2.NEXTVAL, '교육부');
COMMIT; --영구저장
 
SELECT * FROM 부서_테스트;
 cs

 

 

 

 

  • 시퀀스 1로 부서번호를 업데이트

1
2
UPDATE 부서_테스트
SET 부서번호 = 시퀀스1.NEXTVAL;
 cs

 

 

 

 

1
SELECT * FROM 부서_테스트;
 cs

 

 

 

1
2
3
4
5
6
7
8
9
10
11
12
DROP TABLE 부서_테스트;
DROP SEQUENCE 시퀀스1;
DROP SEQUENCE 시퀀스2;
DROP TABLE DEPT CASCADE CONSTRAINTS; --제약조건도 같이 삭제
DROP TABLE EMP;
DROP TABLE EMP1;
DROP TABLE EMP2;
DROP TABLE E_EMP2;
DROP TABLE EX_DATE;
DROP TABLE 직원테이블;
DROP TABLE STMANS;
DROP TABLE MEMBERS;
 cs

 

 

 

 

 

저작자표시 비영리 변경금지

'Learning > SQL' 카테고리의 다른 글

JDBC Test  (0) 2020.06.23
DCL: 데이터 제어어  (0) 2020.06.23
인덱스  (0) 2020.06.22
뷰  (0) 2020.06.22
테이블 컬럼(열)의 추가/수정/삭제  (0) 2020.06.22

<인덱스>

 

  • 인덱스: RDBMS에서 검색속도를 높이기 위해 사용하는 하나의 기술

  • WHERE절로 행을 찾을때 전체 테이블을 다 훑어서 비효율적. 자주 찾는 열은 별개의 공간에 인덱스를 만들어서 검색하도록 함

  • 키 값을 기초로 하여 테이블에서 검색과 정렬 속도 향상시킴

  • 테이블의 기본키는 자동으로 인덱스됨

 

 

  • 인덱스 확인하기

1
2
3
4
5
6
SELECT
    TABLE_NAME 테이블명,
    INDEX_NAME 인덱스이름,
    COLUMN_NAME 컬럼이름
FROM ALL_IND_COLUMNS --오라클에서 자동 생성됨
WHERE TABLE_NAME = 'DEPARTMENTS'; --테이블 이름을 대문자로
 cs

 

 

 

 

 

1
2
3
4
5
6
7
8
9
10
11
--실습으로 테이블 만들기
CREATE TABLE members (
    member_id NUMBER,
    first_name VARCHAR2(100) NOT NULL,
    last_name VARCHAR2(100) NOT NULL,
    gender CHAR(1) NOT NULL,
    dob DATE NOT NULL,
    email VARCHAR2(255) NOT NULL,
    PRIMARY KEY (member_id)
);
DESC members;
 cs

 

 

 

 

 

1
2
3
4
5
6
7
--인덱스 확인하기
SELECT
    TABLE_NAME 테이블명,
    INDEX_NAME 인덱스이름,
    COLUMN_NAME 컬럼이름
FROM ALL_IND_COLUMNS --오라클에서 자동 생성됨
WHERE TABLE_NAME = 'MEMBERS'; --테이블 이름을 대문자로
 cs

 

 

 

 

1
2
SELECT * FROM MEMBERS
WHERE last_name='Harse'; --테이블 전체 검색
 cs

 

 

 

 

1
2
3
EXPLAIN PLAN FOR --다음 나오는 select문을 실행하여 보고서 작성
SELECT * FROM members
WHERE last_name = 'Harse';
 cs

 

 

 

 

1
2
3
작성된 보고서를 보기 위하여 출력하라는 명령문
SELECT PLAN_TABLE_OUTPUT --설명문을 출력
FROM TABLE (DBMS_XPLAN.DISPLAY());
 cs

 

 

 

 

 

 

  • 인덱스 만들기

1
2
CREATE INDEX members_last_name_i
ON members (last_name);
 cs

 

 

 

 

1
2
3
4
5
6
7
--인덱스 확인하기
SELECT
    TABLE_NAME 테이블명,
    INDEX_NAME 인덱스이름,
    COLUMN_NAME 컬럼이름
FROM ALL_IND_COLUMNS --오라클에서 자동 생성됨
WHERE TABLE_NAME = 'MEMBERS'; --테이블 이름을 대문자로
 cs

 

 

 

 

1
2
3
4
5
6
EXPLAIN PLAN FOR 
SELECT * FROM members
WHERE last_name = 'Harse';
 
SELECT PLAN_TABLE_OUTPUT 
FROM TABLE (DBMS_XPLAN.DISPLAY());
 cs

 

 

 

 

  • 인덱스 삭제하기

 

1
DROP INDEX members_last_name_i;
 cs

 

 

 

 

1
2
3
SELECT *
FROM members
WHERE first_name LIKE 'M%' AND last_name LIKE 'A%';
 cs

 

 

 

 

  • 멀티 인덱스 만들기

1
2
CREATE INDEX name_i
ON members(first_name, last_name);
 cs

 

 

 

1
2
3
4
--성능평가
EXPLAIN PLAN FOR
SELECT * FROM members
WHERE first_name LIKE 'M%' AND last_name LIKE 'A%';
 cs

 

 

 

 

1
2
SELECT PLAN_TABLE_OUTPUT --설명문을 출력
FROM TABLE (DBMS_XPLAN.DISPLAY());
 cs

 

 

 

  • 인덱스 삭제 후 성능평가

1
2
3
4
5
6
7
8
9
DROP INDEX name_i;
 
EXPLAIN PLAN FOR
SELECT * FROM members
WHERE first_name LIKE 'M%' AND last_name LIKE 'A%';
 
SELECT PLAN_TABLE_OUTPUT --설명문을 출력
FROM TABLE (DBMS_XPLAN.DISPLAY());
--cost가 2에서 5가 됨 (인덱스가 있어야 속도가 빠르다.)
 cs

 

 

 

 

 

 

저작자표시 비영리 변경금지

'Learning > SQL' 카테고리의 다른 글

DCL: 데이터 제어어  (0) 2020.06.23
시퀀스  (0) 2020.06.23
뷰  (0) 2020.06.22
테이블 컬럼(열)의 추가/수정/삭제  (0) 2020.06.22
제약조건의 삭제  (0) 2020.06.22

<뷰>

 

  • 뷰: 가상의 테이블. 직접 테이블에 접근하는 것이 아니라 테이블에서 사용자가 필요로 하는 부분만 선택하여 만들어놓은 데이터의 집합. 가상의 테이블

  • 원본 테이블에 직접 접근하지 않아도 사용자가 임의의 뷰를 구성하여 별도의 이름을 붙이거나 접근 가능한 사람들 지정할 수 있음 -> 데이터의 보안 유지

  • 복잡한 SQL문을 매번 작성하지 않도록 할 수 있음

 

 

<뷰의 특징>

 

  • 기본테이블로부터 유도된 테이블이기 때문에 기본 테이블과 같은 형태의 구조를 사용. 조작도 거의 비슷

  • 가상 테이블이기 때문에 물리적으로 구현되어 있지 않음

  • 필요한 데이터만 뷰로 정의해서 처리하여 관리가 용이

  • 뷰를 통해서만 데이터에 접근하게 하면 뷰에 나타나지 않는 데이터를 안전하게 보호가능

 

 

 

  • 단순한 view 만들기 EMP_V1 (하나의 원본테이블 사용, 함수나 그룹 사용 안함, DML 사용가능)

1
2
3
4
5
6
7
8
9
DROP VIEW EMP_V1; --미리 뷰 삭제 명령
 
--employees 테이블에서 필요한 열들을 골라서 뷰로 생성
CREATE VIEW EMP_V1
AS
SELECT employee_id, last_name, email, hire_date, job_id
FROM employees;
 
SELECT * FROM emp_v1;
 cs

 

 

 

 

 

1
2
3
4
5
6
7
--테이블처럼 DML 사용가능 (뷰는 가상의 테이블=>원본 테이블에 입력됨)
INSERT INTO EMP_V1
    (employee_id, last_name, email, hire_date, job_id)
VALUES (333, 'David', 'DAVID', SYSDATE,'IT_PROG');
 
--뷰에 입력된 데이터 확인
SELECT * FROM emp_v1;

 

 

 

1
SELECT * FROM employees;
 cs

뷰를 통해서 입력한 데이터가 employees 테이블에 들어가 있다.

 

 

 

 

  • 뷰 삭제하기

 

DELETE FROM emp_v1

WHERE employee_id = 333;

commit;

 

1
2
3
DELETE FROM emp_v1
WHERE employee_id = 333;
commit;
 cs

 

 

 

 

 

  • 뷰 테이블 emp_v2 만들기

1
2
3
4
5
6
7
CREATE VIEW emp_v2
AS
SELECT employee_id 직원번호, last_name 이름,
       email, hire_date, job_id
FROM employees;
 
SELECT * FROM emp_v2;
 cs

 

 

 

 

 

 

  • 뷰 테이블 emp_v3 만들기

1
2
3
4
5
6
CREATE VIEW emp_v3 (직원번호, 이름, 이메일, 고용일자, 직종)
AS
SELECT employee_id, last_name, email, hire_date, job_id
FROM employees;
 
SELECT * FROM emp_v3;
 cs

 

 

 

 

  • DML 사용 (emp_v3)

1
2
3
4
INSERT INTO emp_v3 (직원번호, 이름, 이메일, 고용일자, 직종)
VALUES (334, 'LaLa', 'LaLa', SYSDATE, 'IT_PROG');
 
SELECT * FROM emp_v3;
 cs

 

 

 

 

emp_v1, emp_v2, emp_v3, 원본인 employees 테이블에도 334번이 들어가 있음.

 

 

 

 

 

  • 복잡한 뷰 만들기 (그룹함수를 사용한 뷰는 DML 사용 불가)

1
2
3
4
5
6
7
8
CREATE VIEW 부서별_직원_보고서
AS
SELECT department_id 부서번호, count(employee_id) 직원수,
    MAX(salary) 최고급여, MIN(salary) 최저급여
FROM employees
GROUP BY department_id;
 
SELECT * FROM 부서별_직원_보고서;
 cs

 

 

 

 

 

1
2
INSERT INTO 부서별_직원_보고서
VALUES (200, 4, 5000, 1000);
 cs

 

 

 

  • 부서번호 기준으로 오름차순 정렬을 하고 싶다면? 이미 만든 테이블을 삭제하고 다시 만들어야 한다. 그럴때 OR REPLACE 명령어를 통해 중복된 테이블이 있다면 삭제할 필요 없이 대체하여 만들 수 있다.

1
2
3
4
5
6
7
CREATE OR REPLACE VIEW 부서별_직원_보고서
AS
SELECT department_id 부서번호, count(employee_id) 직원수,
    MAX(salary) 최고급여, MIN(salary) 최저급여
FROM employees
GROUP BY department_id
ORDER BY 부서번호;
 cs

 

 

 

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE VIEW 부서별_직원_보고서
AS
SELECT department_id 부서번호, count(employee_id) 직원수,
    MAX(salary) 최고급여, MIN(salary) 최저급여,
    ROUND(AVG(salary)) 평균급여
FROM employees
GROUP BY department_id
ORDER BY 부서번호;
SELECT * FROM 부서별_직원_보고서;
 cs

 

 

 

  • 읽기 전용 뷰 (옵션 WITH READ ONLY)

 

1
2
3
4
5
6
7
8
CREATE OR REPLACE VIEW EMP_V_read
AS
SELECT employee_id, last_name, email,
   hire_date, job_id, department_id
FROM employees
WHERE department_id = 90
WITH READ ONLY; --읽기 전용
SELECT * FROM emp_v_read;
 cs

 

 

 

1
2
--DML 사용 불가
DELETE FROM EMP_V_READ; --모든 행 삭제
 cs

 

 

 

 

  • 체크 옵션 뷰

1
2
3
4
5
6
7
CREATE OR REPLACE VIEW emp_v_check
AS
SELECT employee_id, last_name, email,
    hire_date, job_id, department_id
FROM employees
WHERE department_id = 90
WITH check option; --where절의 조건에서만 수정 및 입력 가능
 cs

 

 

1
SELECT * FROM emp_v_check;
 cs

 

 

 

 

  • 90번 부서 일때만 DML 가능

1
2
3
INSERT INTO emp_v_check (employee_id, last_name, email,
    hire_date, job_id, department_id)
VALUES (444, '알리', 'ALI', SYSDATE, 'IT_PROG', 10);
 cs

 

 

 

1
2
3
INSERT INTO emp_v_check (employee_id, last_name, email,
    hire_date, job_id, department_id)
VALUES (444, '알리', 'ALI', SYSDATE, 'IT_PROG', 90);
 cs

 

 

 

 

저작자표시 비영리 변경금지

'Learning > SQL' 카테고리의 다른 글

시퀀스  (0) 2020.06.23
인덱스  (0) 2020.06.22
테이블 컬럼(열)의 추가/수정/삭제  (0) 2020.06.22
제약조건의 삭제  (0) 2020.06.22
제약조건의 추가 및 수정  (0) 2020.06.22

+ Recent posts

티스토리툴바