20230821(월)

<복습>

- select * from 테이블명 : 전체 컬럼 출력

* 출력 순서 : 테이블 정의 시 컬럼 순서

- where ~ : 내가 원하는 조건 중 참이 되는 조건만 출력됨.

- order by 컬럼명 : 컬럼명을 기준으로 정렬함.

* 가장 최근에 등록한 행을 맨 앞에 오게끔 함. (옛날 데이터는 관심도가 떨어지기 때문에)

* 기본은 오름차순 (asc - ascending의 약어)

* 내림차순 지정은 desc (descending의 약어)

- group by ~ : 통계처리가 필요한 경우에 사용 (ex - 전체 데이터 건수가 몇개인지?)

* 집계함수를 많이 사용함

* count()

* sum()

* () 안에 특정 컬럼을 넣을 수 있으며, *을 넣어서 모든 행에 대해서 작업도 가능하다.

* 집계함수 사용시 group by를 사용하지 않으면 전체 컬럼에 대해서 집계함수를 돌릴 수 있으며,

* group by를 사용하면 group by에서 사용한 컬럼을 집계함수를 돌릴 수 있다.

- having ~ 조건 : 언제 하느냐 (필터링)

- or~ : 또는

 

<오늘 배울 내용>

1. JOIN : DBMS의 성격을 드러내는 특성을 지님

2. SUBQUERY

1교시부터 6교시 : oracle, db를 다룸

7~8교시 : java 추가학습

 

emp

dept <- 부서정보 10, 20, 30

(자바의 참조와 비슷한 기능)

 

<조인>

1. 집합 연산자(건너뜀, union..)와 조인의 차이점

2. 여러 테이블을 사용할 때의 from절

select ~ from 테이블1, 테이블2, 테이블3 ...;

3. 조인 조건이 없을 때의 문제점

- 열 이름을 비교하는 조건식으로 비교하기

select *

from emp, dept

where emp.depno = dept.depno

order by empno;

4. 테이블의 별칭 설정

From 테이블 이름1 별칭1, 테이블 이름2 별칭2, ...

- 테이블 이름을 별칭으로 표현하기

select *

from emp e, dept d

where e.deptno = d.deptno //공통 컬럼이라고도 부름

order by empno;

5. 등가 조인 equi join

- 테이블을 연결한 후에 출력 행을 각 테이블의 특정 열에 일치한 데이터를 기준으로 선정하는 방식

- emp 테이블과 dept 테이블을 조인할 때 deptno 열을 사용한 것

- 내부 조인(inner join), 외부 조인(outer join)

- 두 테이블에 부서 번호가 똑같은 열 이름으로 포함되어 있을 때

select empno, ename, deptno, dname, loc

from emp e, dept d

where e.deptno = d.deptno;

- 열 이름에 각각의 테이블 이름도 함께 명시할 때

select e.empno, e.ename, d.deptno, d.dname, d.loc

from emp e, dept d

where e.deptno = d.deptno

order by d.deptno, e.deptno;

- where 절에 추가로 조건식 넣어 출력하기

select e.empno, e.ename, e.sal, d.deptno, d.dname, d.loc

from emp e, dept d

where e.deptno = d.deptno

and sal >= 3000;

6. 비등가 조인 non-equi join

select * from emp; 와 select * from salgrade; 를 조인해보자

- 급여 범위를 지정하는 조건식으로 조인하기

select *

from emp e, salgrade s

where e.sal between s.losal and s.hisal;

7. 자체 조인 (self join)

select *

from emp e, copy_emp c

where e.mgr = c.empno;

- 같은 테이블을 두 번 사용하여 자체 조인하기

select e1.empno, e1.ename, e1.mgr,

e2.empno as mgr_empno,

e2.ename as mgr_ename

from emp e1, emp e2

where e1.mgr = e2.empno;

8. 외부 조인

- 좌우를 따로 나누어 지정

* where 절에 조인 기준 열 중 한 쪽에 (+) 기호를 붙여줌 (오라클만)

** 왼쪽 외부 조인(Left Outer Join) : where table1.col1 = table2.col1(+)

** 오른쪽 외부 조인(Right Outer Join) : where table1.col1(+) = table2.col1

- 왼쪽 외부 조인 사용하기

select e1.empno, e1.ename, e1.mgr,

e2.empno as mgr_empno,

e2.ename as mgr_ename

from emp e1, emp e2

where e1.mgr = e2.empno(+)

order by e1.empno;

- SQL 도 "표준안"이 있다.

 

9. NATURAL JOIN

- natural join을 사용하여 조인하기

* 자동으로 공통열을 사용

select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm,

deptno, d.dname, d.loc

from emp e natural join dept d

order by deptno, empno;

 

10. JOIN~USING (등가조인)

- join ~ using을 사용하여 조인하기

select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm,

deptno, d.dname, d.loc

from emp e join dept d using(deptno)

where sal >= 3000

order by deptno, empno;

 

11. JOIN~ON (등가조인이지만, 비 등가조인도 가능)

- 가장 범용성 있는 방법

- 기존 where절에 있는 조인 조건식을 on 키워드 옆에 작성

- JOIN ~ ON 으로 등가 조인하기

select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm,

e.deptno, d.dname, d.loc

from emp e join dept d on(e.deptno = d.deptno)

where sal <= 3000

order by e.deptno, empno;

 

12. OUTER JOIN

- 왼쪽 외부 조인을 SQL-99로 작성하기

select e1.empno, e1.ename, e1.mgr,

e2.empno as mgr_empno,

e2.ename as mgr_ename

from emp e1 left outer join emp e2 on(e1.mgr = e2.empno)

order by e1.empno;

 

- 오른쪽 외부 조인을 SQL-99로 작성하기

select e1.empno, e1.ename, e1.mgr,

e2.empno as mgr_empno,

e2.ename as mgr_ename

from emp e1 right outer join emp e2 on(e1.mgr = e2.empno)

order by e1.empno;

 

13. SQL-99조인 방식에서 세 개 이상의 테이블을 조인할 때

...

from table1, table2, table3

where table1.col = table2.col

and table2.col = table3.col

...

from table1 join table2 on(조건식)

join table3 on(조건식)

 

- 1:1 관계? 1:N 관계? N:N 관계?

==> emp는 n, dept는 1의 1:N 관계이다.

salgrade 테이블

 

<서브쿼리>

1. 서브쿼리subquery란

- SQL문 내부에서 사용하는 SELECT 문

- SQL문을 실행하는데 필요한 데이터를 추가로 조회하기 위함

- 사원 이름이 JONSE인 사원의 급여 출력하기

- 급여가 2975보다 높은 사원 정보 출력하기

SELECT *

FROM EMP

WHERE sal > 2975;

 

- 서브쿼리로 JONES의 급여보다 높은 급여를 받는 사원 정보 출력하기

SELECT *

FROM EMP

WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME='JONES');

 

2. 단일행 서브쿼리

- 실행 결과가 단 하나의 행으로 나오는 서브쿼리

- 서브 쿼리의 결과 값이 날짜형인 경우

select *

from emp

where hiredate < (select hiredate from emp where ename = 'JONES');

==> 존스의 입사일보다 일찍 입사한 사람

 

3. 다중행 서브쿼리

- 실행 결과가 여러 행으로 나오는 서브쿼리

- 비교 연산자 사용 불가

- 포함 관계 여부 사용

 

- 평균 급여보다 많이 받는 사원 목록 출력하기

SELECT *

FROM EMP

WHERE sal > (SELECT AVG(sal) FROM emp);

- deptno가 20인 평균급여보다 많이 받는 사원 목록 출력하기

SELECT *

FROM EMP

WHERE

deptno = 20 and

sal > (SELECT AVG(sal) FROM emp);

 

- 평균 급여보다 많이 받는 20번 부서 사원 출력하되, 부서명, 사원이름, 월급을 출력하시오 (부서명 때문에 Join을 해줘야함.)

SELECT D.DEPTNO, E.ENAME, E.SAL

FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)

WHERE

E.DEPTNO = 20 and

E.SAL > (SELECT AVG(SAL) FROM EMP);

 

4. 다중행 연산자의 종류

- in : 메인쿼리의 데이터가 서브쿼리의 결과 중 하나라도 일치한 데이터가 있으면 true

- any, some :  메인쿼리의 조건식을 만족하는

- all

- exists

 

5. IN 연산자

- IN 연산자 사용하기

select *

from emp

where deptno in (20, 30);

- 각 부서별 최고 급여(subquery)와 동일한 급여를 받는 사원 정보 출력하기

select *

from emp

where sal in (select max(sal) from emp group by deptno);

(항상 서브쿼리 먼저 잘 작성됐는지 확인 후 나머지를 작성하는게 좋다.)

 

6. any, some 연산자

- 서브쿼리가 반환한 여러 결과 값 중 메인쿼리와 조건식을 사용한 결과가 하나라도 true라면

메인쿼리 조건식을 true로 반환해주는 연산자

- any 연산자 사용하기

select *

from emp

where sal = any (select max(sal) from emp group by deptno);

 

- some 연산자 사용하기

select *

from emp

where sal = some(select max(sal) from emp group by deptno);

 

- 30번 부서 사원들의 최대 급여보다 적은 급여를 받는 사원 정보 출력하기

select *

 from emp

where sal < any(select sal from emp where deptno=30)

order by sal, empno;

 

- 부서 번호가 30인 사원들의 급여 출력하기

select sal

from emp

where deptno = 30;

 

7. ALL 연산자

- 서브쿼리의 모든 결과가 조건식에 맞아야

- 부서 번호가 30번인 사원들의 최소 급여보다 더 적은 급여를 받는 사원 출력하기

select *

from emp

where sal < all (select sal from emp where deptno = 30);

 

8. EXISTS 연산자

- 서브쿼리에 결과 값이 하나 이상 존재하면 조건식이 true, 존재하지 않으면 false

- 서브쿼리 결과 값이 존재하는 경우

select *

from emp

where exists(select dname from dept where deptno = 10);

 

- 서브쿼리 결과 값이 존재하지 않는 경우

select *

from emp

where exists(select dname from dept where deptno = 50);

 

9. 다중열 서브쿼리 multiple-column subquery

- 서브쿼리의 select 절에 비교할 데이터를 여러 개 지정하는 방식

- 다중열 서브쿼리 사용하기

select *

from emp

where (deptno, sal) in (select deptno, max(sal) from emp group by deptno);

 

<From 절에 사용하는 서브쿼리와 with절>

1. from절에 사용하는 서브쿼리

- 인라인뷰 inline view

- 주로 별칭을 붙여 사용

- 인라인 뷰 사용하기

SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC

FROM (SELECT * FROM EMP WHERE DEPTNO = 10) E10,

(SELECT * FROM DEPT) D

WHERE E10.DEPTNO = D.DEPTNO;

 

2. with 절

- 가독성을 위해 인라인 뷰를 from 절에서 분리해서 사용

- with절 사용

with

E10 as (select * from EMP where DEPTNO = 10),

D as (select * from DEPT)

select E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC

from E10, D

where E10.DEPTNO = D.DEPTNO;

 

<데이터를 추가, 수정, 삭제하는 데이터 조작어>

CRUD

R : select

 

1. 테이블 생성하기

- DDL(Data Definition Language) 명령어

- DEPT 테이블을 복사해서 DEPT_TEMP 테이블 만들기

create table DEPT_TEMP as select * from DEPT;

 

select * from DEPT_TEMP;

 

2. INSERT문 실습 전 유의점

- 테이블을 잘못 만들었을 때

drop table 테이블이름;

 

<테이블에 데이터 추가하기>

1. 테이블에 데이터를 추가하는 Insert문 ( [] 의미 : 옵션.. 즉 생략 가능함)

insert into 테이블이름 [(열1, 열2, ... , 열N)]

values (열1에 들어갈 데이터, 열2에 들어갈 데이터, ... , 열N에 들어갈 데이터);

 

- DEPT_TEMP 테이블에 데이터 추가하기

insert into dept_temp(deptno, dname, loc) values (50, 'database', 'seoul'); // 제일 많이 사용하는 형태 라고 함.

 

select 의 결과 => 표,

insert, update, delete의 결과 => 정수

 

- INSERT 문 에러 발생하는 경우

* 필드 개수와 값의 개수가 다른 경우

* 데이터 타입이 다른 경우

* 필드의 최대 길이보다 데이터의 길이가 더 큰 경우

 

- insert문으로 데이터 입력하기 (열 지정을 생략할 떄)

* 테이블을 만들 때 설정한 열 순서대로 값을 나열

 

- insert 문에 열 지정없이 데이터 추가하기

INSERT INTO DEPT_TEMP VALUES(60, 'NETWORK', 'BUSAN');

SELECT * FROM DEPT_TEMP;

 

2. 테이블에 NULL 데이터 입력하기

- NULL을 지정하여 입력하기

insert into dept_temp(DEPTNO, DNAME, LOC) values(70, 'WEB', NULL);

insert into dept_temp(DEPTNO, DNAME, LOC) values(80, 'MOBILE', '');

insert into dept_temp(DEPTNO, LOC) values(80, 'INCHEON');

SELECT * FROM DEPT_TEMP;

 

3. 테이블에 날짜 데이터 입력하기

- EMP 테이블을 복사해서 EMP_TEMP 테이블 만들기

create table emp_temp as select * from emp where 1 <> 1; // <>는 다르다 (!=) 의 뜻. 항상 false.

==> 골격만 복사하라(컬럼명만 복사하라) 라는 뜻.

select * from emp_temp;

 

- insert 문으로 날짜 데이터 입력하기 (날짜 사이에 / 입력)

insert into emp_temp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (9999, '홍길동', 'PRESIDENT', NULL, '2001/01/01', 5000, 1000, 10);

select * from emp_temp;

 

- insert 문으로 날짜 데이터 입력하기 (날짜 사이에 - 입력)

insert into emp_temp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (9999, '홍길동', 'PRESIDENT', 9999, '2001-01-01', 4000, NULL, 20);

 

- 날짜 데이터를 입력할 때 유의점

* 년/월/일 순서 유지, 다르면 에러 발생

* 다른 순서인 경우 TO_DATE() 함수 사용

insert into emp_temp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (1111, '이순신', 'MANAGER', 9999, TO_DATE('07/01/2001', 'DD/MM/YYYY'), 4000, NULL, 20);

 

- SYSDATE를 사용하여 날짜 데이터 입력하기 => 현재 가장 많이 쓰이는 형태

insert into emp_temp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (3111, '심청이', 'MANAGER', 9999, SYSDATE, 4000, NULL, 20);

 

'2023/08/21 13:55:20' -> 이런식으로 년/월/일 시:분:초 를 다 지정해서 넣을 수 있다.

 

4. 서브쿼리를 사용하여 한 번에 여러 데이터 추가하기

- 서브쿼리로 여러 데이터 추가하기 => 짝이 잘 맞춰져야 함.

INSERT INTO EMP_TEMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO

FROM EMP E, SALGRADE S
WEHRE E.SAL BETWEEN S.LOSAL AND S.HISAL

AND S.GRADE = 1;

 

- 날짜 표기법은 나라마다 다름.

로케일 정보가 다르면 값이 안들어가거나 에러가 날 수도 있음.

그럴때는 아래의 명령어로 확인..!!

select * from NLS_SESSION_PARAMETERS;

 

5. UPDATE문의 기본 사용법

UPDATE [변경할 테이블]

SET [변경할 열1]=[데이터], [변경할 열2]=[데이터], ... , [변경할 열n]=[데이터]

[WHERE 데이터를 변경할 대상 행을 선별하기 위한 조건];

==> where을 넣으면 true인 행만 수정이 됨.

 

6. 데이터 전체 수정하기

- DEPT_TEMP2 테이블 업데이트하기

UPDATE DEPT_TEMP2

SET LOC = 'SEOUL';

// ==> 모든 열이 다 SEOUL로 값이 바뀜

//다행스럽게도 취소 시킬 수 있음.

- DEPT_TEMP2 테이블 업데이트 취소하기

ROLLBACK;

 

7. 데이터 일부분만 수정하기

- WHERE절과 조건식을 사용

* 조건식이 true인 행만 수정됨

- 테이블 데이터 중 일부분만 수정하기 => 운영할때 가장 많이 쓰이는 형태임.

UPDATE DEPT_TEMP2

SET

DNAME = 'DATABASE'

LOC = 'SEOUL'

WHERE DEPTNO = 40;

 

8. 서브쿼리로 데이터 일부분 수정하기

UPDATE DEPT_TEMP2

SET (DNAME, LOC) = (SELECT DNAME, LOC FROM DEPT WHERE DEPTNO=40)

WHERE DEPTNO = 40;

 

SELECT * FROM DEPT_TEMP2;

 

- UPDATE 의 WHERE절에 서브쿼리 사용하기

UPDATE DEPT_TEMP2

SET LOC = 'SEOUL'

WHERE DEPTNO = (SELECT DEPTNO FROM DEPT_TEMP2 WHERE DNAME = 'OPERATIONS');

SELECT * FROM DEPT_TEMP2;

 

<테이블에 있는 데이터 삭제하기>

1. 실습준비

create table EMP_TEMP2 as select * from EMP;

select * from emp_temp;

2. DELETE문

DELETE [FROM] [테이블이름] //from은 가급적 쓰자. : 왜냐하면 다른 SQL에서는 from을 생략 못하기 때문.

[WHERE 삭제할 대상 행을 선별하기 위한 조건식];

3. 데이터 일부분만 삭제하기

- WHERE절을 사용하여 데이터 일부분만 삭제하기

DELETE FROM EMP_TEMP2

WHERE JOB = 'MANAGER';

 

select * from EMP_TEMP2;

4. 서브쿼리를 사용하여 데이터 삭제하기

- WHERE절에 서브쿼리를 사용하여 데이터 일부만 삭제하기

DELETE FROM EMP_TEMP2

WHERE EMPNO IN (SELECT E.EMPNO

FROM EMP_TEMP2 E, SALGRADE S

WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL

AND S.GRADE = 3

AND DEPTNO = 30);

==> 오라클의 조인임. 표준이 아님.

 

==> 서브쿼리안에 표준조인으로 다시 변경해보면..?

DELETE FROM EMP_TEMP2

WHERE EMPNO IN (

SELECT E.EMPNO FROM EMP_TEMP2 E JOIN SALGRADE S

ON(E.SAL BETWEEN S.LOSAL AND S.HISAL)

WHERE

S.GRADE = 3

AND DEPTNO = 30

);

 

5. 테이블 전체 삭제하기

- 테이블에 있는 전체 데이터 삭제하기

delete from emp_temp2;

select * from emp_temp2;

 

6. 데이터 일부분만 삭제하기

- where절을 사용하여 데이터 일부분만 삭제하기 ==> 이게 가장 많이 쓰임.

delete from emp_temp2

where job="MANAGER";

 

CRUD(select, insert, update, delete) => DML (Data Manipulation Language) ==> 시험문제로 가끔 나옴.

 

DDL (Data Definition Language)

- drop table

- drop user

 

-- ROWID, ROWNUM --> 의사(PSEUDO) 컬럼

SELECT * FROM EMP;

SELECT ROWID, E.* FROM EMP E; // ROWID = 칼럼들의 실제 참조값 (값이 안바뀜)

SELECT ROWID, ROWNUM, E.* FROM EMP E; // ROWNUM = 행들이 만들어진 순번 (쿼리마다 값이 항상 바뀜)

SELECT ROWID, ROWNUM, E.* FROM EMP E

ORDER BY E.HIREDATE; //등수..? ㅇㅇ...

==> ROWID는 우리가 쓸 일이 많이 없지만, ROWNUM은 쓸 일이 많다.

==> 오라클에만 존재하는 개념임.

 

<자바 프로젝트>

자바 프로젝트 생성 - 패키지 생성 - 마우스 오른쪽 버튼 properties - JavaBuildPath - add folder - create new folder - test 폴더 생성 - src, test 클릭 하여 생성 - test패키지 폴더 맨 하단 메뉴 NO -> OK로 변경 -> 

 

1. 추상화

- 메뉴의 항목과 그때 실행할 메서드(혹은 클래스) => 인터페이스로 추상화

- 메뉴 목록 관리 : ArrayList

- 메뉴 목록 출력과 실행할 메뉴 선택하기

- 전체 흐름 관리

 

- 우리가 만들 메서드의 시그니처는 무엇일까? : 리턴타입은 void가 될 것이고, 파라미터는 안받을 것이다.

- 인터페이스는 형태만 잡아주는 것.

- 추상화 : 공통점을 뽑아내서 클래스로 표현하는 것

- 생성자, getter, setter, toString()

 

<오늘 우리가 한 작업>

1. Command Interface 생성 후 execute 메소드 생성

2. ListCommand, SearchCommand, ExitCommand 자식 클래스 생성

==> OCP의 기본 구조

3. MenuItem클래스가 Command 를 주입받아서 사용하는 aggregation 형식을 만듦.