국비학원 17일차) Oracle, CRUD, JDBC

 

 

사원 번호, 성, 월급, 해당 직무 최소 연봉, 최대 연봉, 부서이름
성은 Smith나 King인 사람만 가져오세요

SELECT EMPLOYEE_ID, LAST_NAME, TRUNC(SALARY/12), MIN_SALARY, MAX_SALARY,DEPARTMENT_NAME
FROM EMPLOYEES JOIN JOBS USING(JOB_ID)
JOIN DEPARTMENTS USING(DEPARTMENT_ID)
WHERE LAST_NAME in('Smith','King')
;

 

 

 

 

 

부서와 부서 관리자 정보를 얻어오자

- 관리자가 없는 부서는 No Manager 라고 표시

- 결과> 부서이름, 관리자 이름(성)

 

 

SELECT DEPARTMENT_NAME, NVL(LAST_NAME,'NO MANAGER')
FROM EMPLOYEES E,DEPARTMENTS D
WHERE D.MANAGER_ID = E.EMPLOYEE_ID(+)
;

 

 

 

 

 

 

 

EXECUTIVE 부서에 소속된 모든 사원들에 대한

DEPARTMENT_ID, LAST_NAME, JOB_ID 출력

 

 

SELECT E.DEPARTMENT_ID, E.LAST_NAME, E.JOB_ID
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.DEPARTMENT_NAME = 'Executive'
;

 

 

SELECT E.DEPARTMENT_ID, E.LAST_NAME, E.JOB_ID
FROM(SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME='Executive') d,
EMPLOYEES E
WHERE D.DEPARTMENT_ID=E.DEPARTMENT_ID
;

 

 

 

 

 

 

 

Abel보다 급여가 높은 사원들의 정보는?

 

 

SELECT *
FROM EMPLOYEES
WHERE SALARY > (
SELECT SALARY
FROM EMPLOYEES
WHERE LAST_NAME='Abel')
;

 

 

 

 

 

직무별로 최대 월급을 받는 사원의 정보는?

EMPLOYEE_ID, JOB_ID, SALARY

 

 

SELECT EMPLOYEE_ID,JOB_ID,SALARY
FROM EMPLOYEES
WHERE (JOB_ID,SALARY) IN
(SELECT JOB_ID,MAX(SALARY)
FROM EMPLOYEES
GROUP BY JOB_ID)
;

 

 

 

전체 평균보다 많이 받는 사원 중에 LAST_NAME에 'Z'가 포함 된 사원과

같은 부서에 근무하는 모든 사원의

EMPLOYEE_ID, LAST_NAME, SALARY 출력

 

 

SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY >(
SELECT AVG(SALARY)
FROM EMPLOYEES)
AND DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE LAST_NAME LIKE '%z%')
;

 

 

 

LAST_NAME에 'U'가 포함된 사원들 중에

자신의 부서 평균 급여보다

더 많이 받는 사원들의

EMPLOYEE_ID, LAST_NAME을 출력하세요

 

 

SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN
(SELECT DEPARMENT_ID
FROM EMPLOYEES
WHERE LAST_NAME LIKE '%u%') AND
SALARY >
(SELECT AVG(SALARY)
FROM EMPLOYEES)
;

 

SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM EMPLOYEES O
WHERE LAST_NAME LIKE '%u%'
AND SALARY >(
SELECT AVG(SALRY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID=O.DEPARTMENT_ID)
;

 

 

 

관리자들의 정보 출력

 

SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN(
SELECT MANAGER_ID
FROM EMPLOYEES)
;

 

 

SELECT *
FROM EMPLOYEES O
WHERE EXISTS (
SELECT 'X'
FROM EMPLOYEES
WHERE MANAGER_ID = O.EMPLOYEE_ID)
;

 

 

 

사원이 있는 부서 정보만 출력

 

 

SELECT *
FROM EMPLOYEES O
WHERE EXISTS (
SELECT 'X'
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NULL)
;

 

 

사원이 없는 부서 번호만 출력

 

 

SELECT *
FROM DEPARTMENTS
WHERE DEPARTMENT_ID NOT IN
(SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL)
;

 

 

 

부서별 평균 급여 중에 최대값을 갖고 있는 부서의

최고급여,최저급여,평균급여 출력

 

 

SELECT DEPARTMENT_ID, MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY) =
(SELECT MAX((AVG)SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID)
;

 

 

 

사원 채용 수가 제일 많은 요일, 인원 수를 출력하세요

 

 

SELECT TO_CHAR(HIRE_DATE,'DAY'),COUNT(*)
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'DAY')
HAVING COUNT(*)
=
(SELECT MAX(COUNT(*))
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'DAY'))
;

 

 

 

 

 

 

 

NOT NULL : 컬럼값으로 NULL 허용 안함
UNIQUE : 테이블 내에서 해당 컬럼 값은 항상 유일한 값
PRIMARY KEY : NULL 허용 안 하고 중복값도 허용 안 함 -> ID만들때
(UNIQUE + NOT NULL)
FOREIGN KEY : 참조하는 테이블의 컬럼값이 존재하면 허용
CHECK : 저장된 값의 범위나 조건을 지정하여 설정한 값만 허용


--테이블 생성
CREATE TABLE MEMBER(
ID VARCHAR2(50) PRIMARY KEY,
PASSWORD VARCHAR2(50) NOT NULL,
NAME VARCHAR2(50) NOT NULL,
ADDRESS VARCHAR(50)
)
;

--컬럼 이름 변경
ALTER TABLE MEMBER
RENAME COLUMN ID TO ID2
;


--자료형 변경
ALTER TABLE MEMBER
MODIFY(PASSWORD NUMBER(2))
;


--컬럼 추가
ALTER TABLE MEMBER
ADD(A VARCHAR2(50))
;


--컬럼 삭제
ALTER TABLE MEMBER
DROP COLUMN ADDRESS
;


--테이블 삭제
DROP TABLE MEMBER;





트랜잭션- 더 이상 분할할 수 없는 최소 수행 단위
계좌이체 -> 출금이 되어야만 입금이 되어야함, 출금이 안 되면 입금도 되면 안 됨
계좌이체 같이 하나의 작업, 또는 밀접하게 관련된 수업을 수행하기 위해
한 개 이상의 데이터 조작 명령어로 이루어진다(SQL문 덩어리)


트랜잭션 취소
ROLLBACK;

트랜잭션 저장(영원히 반영)
COMMIT;
-> 영원히 변경하는 것이기 때문에 ROLLBACK 불가, 신중히 써야 함




세션이란?
데이터 베이스 접속을 시작으로, 여러 데이터베이스에서 관련 작업을 수행한 후
접속을 종료하기까지의 전체 기간(로그인과 로그아웃하기 전까지 기간)


읽기 일관성의 중요성
어떤 특정 세션에서 테이블 데이터를 변경중일 때,
다른 세션에서는 데이터 변경이 확정되기 전까지 변경 사항을 알 필요가 없음
데이터를 변경 중인 세션을 제외한 나머지 세션에서는
현재 진행 중인 변경과 무관한 본래의 데이터를 보여주는 특성



LOCK 수정 중인 데이터 접근 막기
특정 세션에서 조작 중인 데이터는, 트랜잭션이 완료(COMMIT, ROLLBACK)될 때까지
다른 세션에서 조작할 수 없음





자료형
VARCHAR, VARCHAR2, CHAR 문자
VARCHAR 문자 길이 고정
VARCHAR2 문자 길이 가변(길이 변경 가능)
NUMBER 숫자





데이터 정의어(DDL)


CREATE 새 테이블을 생성
CREATE TABLE 소유 계정.데이터 이름 (
컬럼명, 자료형,
컬럼명, 자료형
...
)


컬럼 이름 생성 규칙
1.컬럼 이름 시작은 문자
2.컬럼 이름은 30바이트 이하
3.한 테이블의 컬럼 이름은 중복 불가
4.컬럼명은 영문자(한글가능),숫자(0~9)와 특수문자 $,#,_ 사용 가능
5.SQL키워드는 컬럼명으로 사용 불가


CREATE TABLE EMP_DOL(
EMPNO NUMBER(4),
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
EMP_DOL 이름의 테이블을 만들어줘
컬럼 EMPNO은 4글자 숫자
컬럼 ENAME은 10글자 문자
컬럼 HIREDATE는 날짜
컬럼 SAL은 소수점 이하 두 자리 숫자를 포함한 7자리 숫자(12345.67)


데이터를 복사하여 테이블 생성
CREATE TABLE DEPT_DOL
AS SELECT * FROM DEPT
;


기존 테이블 열 구조와 일부 데이터만 복사하여 새 테이블 생성
CREATE TABLE EMP_DOL_30
AS SELECT *
FROM EMP
WHERE DEPTNO=30
;
EMP_DOL_30 테이블 만들 건데
EMP의 DEPNO이 30인 행들만 복사해서 만들어줘


기존 테이블의 컬럼 구조만 복사하여 새 테이블 생성
CREATE TABLE EMPDEPT_DDL
AS SELECT
E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE,
E.SAL, E.COMM, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE 1 <> 1
;
EMPDEPT_DDL 테이블을 만들 건데
EMP의 이것저것 DEPT의 이것저것 컬럼 만들고
행은 추가하지 마
( 1 <> 1 ) <-조건이 무조건 달라서 행 추가 안 됨



ALTER 테이블 변경


ADD 추가
ALTER TABLE EMP_ALTER
ADD HP VARCHAR2(20)
;
EMP_ALTER 테이블에 HP라는 컬럼 추가해줘, 걔는 문자20개까지 들어갈 수 있어


RENAME 이름 수정
ALTER TABLE EMP_ALTER
RENAME COLUMN HP TO TEL
;
EMP_ALTER 테이블의 HP라는 칼럼 이름을 TEL로 바꿔줘



MODIFY 자료형 수정
ALTER TABLE EMP_ALTER
MODIFY EMPNO NUMBER(5)
;
EMPNO의 자료형 길이를 5로 바꿔줘


DROP COLUMN 삭제
ALTER TABLE EMP_ALTER
DROP COLUMN TEL
;
TEL 컬럼 삭제해줘



RENAME 테이블명 변경
RENAME 원래이름 TO 바꿀이름

RENAME EMP_ALTER TO EMP_RENAME;
EMP_ALTER 테이블명을 EMP_RENAME으로 바꿔줘



TRUNCATE 테이블 모든 데이터 삭제(컬럼X)
TRUNC TABLE 테이블명

TRUNCATE TABLE EMP_RENAME;
EMP_RENAME에 있는 데이터 모두 삭제해줘



DROP 테이블 삭제
DROP TABLE EMP_RENAME;
EMP_RENAME 테이블을 삭제해줘



CREATE TABLE SHOP_MEMBER(
ID VARCHAR2(20) PRIMARY KEY,
PWD VARCHAR2(20),
NAME VARCHAR2(20),
EMAIL VARCHAR2(20),
ADDR VARCHAR2(50),
TYPE NUMBER
)
;


CREATE TABLE SHOP_PRODUCT(
NUM NUMBER PRIMARY KEY,
NAME VARCHAR2(500) NOT NULL,
QUANTITY NUMBER NOT NULL,
PRICE NUMBER NOT NULL,
IMG VARCHAR2(500),
CONTENT VARCHAR2(500),
S_ID VARCHAR2(20) REFERENCES SHOP_MEMBER(ID) ON DELETE CASCADE
)
S_ID는 SHOP_MEMBER 테이블의 ID값을 참조해서 넣는다


DROP TABLE SHOP_MEMBER CASCADE CONSTRAINTS;
SHOP_PRODUCT에서 SHOP_MEMBER의 ID를 참조하고 있기 때문에
그냥 삭제하면 삭제가 안 됨,
그래서 CASCADE CONTRAINTS <-이걸 붙여야 삭제 가능


이미지는 DB에 안 집어 넣음
DB에는 경로만 집어 넣음
그 경로를 읽어서 띄워옴



SEQUENCE 키 값을 생성해주는 객체(HASHMAP 이런 느낌)
하나의 시퀀스를 여러가지 테이블에서 사용 가능

CREATE SEQUENCE 테이블명_시퀀스이름 --생성
MINVALUE 1 --최소값
NOMAXVALUE --최대값(제한없음)
INCREMENT BY 1 --1씩 증가
NOCYCLE
CACHE
옵션 생성 안 하면 기본값으로 설정됨

DROP SEQUENCE 테이블명_시퀀스이름 --삭제


INSERT 행 추가
INSERT INTO MEMBER(ID,PASSWORD,NAME,ADDRESS)
VALUES('JAVA','1234','손연재','수서');


UPDATE 수정
UPDATE MEMBER SET ADDRESS='수서'
WHERE NAME='백박사'
;
MEMBER테이블에 NAME이 백박사인 행의 ADDRESS를 수서로 수정해줘


DELETE 행 삭제
DELETE FROM MEMBER
WHERE NAME='황정민'
;
MEMBER테이블에서 NAME이 황정민인 행 삭제


끝에 저장 해줘야 함
COMMIT; <-이거 해줘야 JDBC 연동해서 뜸, 저장개념

 

 

JDBC란?

1. driver loading
2. Connection ( import java.sql.* )
ㄴ오라클과 자바의 다리 역할
3. Statement (자동차)
sql문을 담아서 DB로 직접적으로 전달
4.Query 실행
5.결과
6.Connection 종료

 

 

 

DB와 연동해서 실행

package step1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestJDBC1 {

	public static void main(String[] args) {
		
		try {
			
			Class.forName("oracle.jdbc.driver.OracleDriver"); //JDBC 드라이버 경로
			System.out.println("driver ok");
			
			//URL작성, 해당하는 오라클DB 아이디 패스워드
			Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521","hr","hr");
			System.out.println("connection ok");
			
			//SQL문을 옮겨주는 자동차(Statement) 만들기
			Statement stmt = con.createStatement(); //컬럼명 무조건 일치시켜야함
			String sql = "SELECT * FROM MEMBER";
			
			//얘가 호출되면 테이블에서 데이터 가지고 옴
			ResultSet rs = stmt.executeQuery(sql); //Iterator랑 비슷
			
			while(rs.next()) { //데이터가 있으면 true
				//가져온 데이터 출력
				System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)); //숫자 대신 컬럼명도 가능
				
			}
			
			rs.close();
			stmt.close();
			con.close();
			
		} catch(ClassNotFoundException e) {
			e.printStackTrace();
		} catch(SQLException e) {
			e.printStackTrace();
		}
		
	}
}

 

 

 

순서

 

Class.format("oracle.jdbc.driver.OracleDriver") //Oracle JDBC 드라이버 경로

Connection con = DriverManager.getConnection("URL","아이디","비밀번호"); //DB계정

Statement stmt = con.createStatement() //연결통로

String sql = "SQL문"

ResultSet rs = stmt.executeQuery(sql); //위에 쓴 SQL문을 이용해 데이터 가져오기



//출력

while(rs.next()) { //데이터가 있으면 true
System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)); //숫자 대신 컬럼명도 가능
}

//마지막에 연 순서대로 닫기(닫기 필수)
rs.close()
stmt.close()
con.close()

 

Class.format("oracle.jdbc.driver.OracleDriver") //Oracle JDBC 드라이버 경로

Connection con = DriverManager.getConnection("URL","아이디","비밀번호"); //DB계정

Statement stmt = con.createStatement() //연결통로, SQL 실행 시키기

String sql = "SQL문"

ResultSet rs = stmt.executeQuery(sql); //위에 쓴 SQL문을 이용해 데이터 가져오기

 

//출력

while(rs.next()) //데이터가 있으면 true
System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)); //숫자 대신 컬럼명도 가능

 

rs.close()

stmt.close()

con.close()

마지막에 연 순서대로 닫기(필수)

 

 

 

내일 할 거
jdbc에 insert, delete, update 코드 짜기?만들기?
executeUpdate <-특정 값에 변화가 있으면, update씀
executeQuery <- 변화 없을때, 그냥 값만 가져올 때

끝나면? 클래스로 만들기

반복작업을 어떻게 하면 짧게 만들 수 있을까 생각해보기