## ANSI 조인 : SQL의 국제적 표준
1) [INNER] JOIN
형식)
SELECT 컬럼명1,..
FROM 테이블1 [INNER] JOIN 테이블2
ON 조인조건절
[WHERE절]
예1) 사원번호,이름,부서명,부서위치를 조회하시오.
SELECT EMPNO,ENAME,DNAME,LOC
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO;
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7369 SMITH RESEARCH DALLAS
7499 ALLEN SALES CHICAGO
7521 WARD SALES CHICAGO
7566 JONES RESEARCH DALLAS
7654 MARTIN SALES CHICAGO
7698 BLAKE SALES CHICAGO
7782 CLARK ACCOUNTING NEW YORK
7839 KING ACCOUNTING NEW YORK
7844 TURNER SALES CHICAGO
7900 JAMES SALES CHICAGO
7902 FORD RESEARCH DALLAS
..
Q1) 부서번호,부서명,이름,급여를 조회해 보세요.
SELECT E.DEPTNO,D.DNAME,E.ENAME,E.SAL
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO;
DEPTNO DNAME ENAME SAL
---------- -------------- ---------- ----------
20 RESEARCH SMITH 800
30 SALES ALLEN 1600
30 SALES WARD 1250
20 RESEARCH JONES 2975
30 SALES MARTIN 1250
30 SALES BLAKE 2850
10 ACCOUNTING CLARK 2450
10 ACCOUNTING KING 5000
..
Q2) 사원이름 'ALLEN'인 사원의 사원번호,이름,급여,부서명을 조회하시오.
SELECT E.EMPNO,E.ENAME,E.SAL,D.DNAME
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
WHERE E.ENAME='ALLEN';
EMPNO ENAME SAL DNAME
---------- ---------- ---------- --------------
7499 ALLEN 1600 SALES
예2) 사원번호,이름,부서명,급여,급여등급(GRADE)를 조회하시오.(3개 테이블 조인)
SELECT E.EMPNO,E.ENAME,D.DNAME,E.SAL,S.GRADE
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL;
EMPNO ENAME DNAME SAL GRADE
---------- ---------- -------------- ---------- ----------
7369 SMITH RESEARCH 800 1
7499 ALLEN SALES 1600 3
7521 WARD SALES 1250 2
7566 JONES RESEARCH 2975 4
7654 MARTIN SALES 1250 2
7698 BLAKE SALES 2850 4
7782 CLARK ACCOUNTING 2450 4
7839 KING ACCOUNTING 5000 5
7844 TURNER SALES 1500 3
...
## USING(공통컬럼)
예3) 사원번호,이름,부서명,부서위치를 조회하시오.
SELECT E.EMPNO,E.ENAME,D.DNAME,D.LOC
FROM EMP E JOIN DEPT D
USING(DEPTNO);
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7369 SMITH RESEARCH DALLAS
7499 ALLEN SALES CHICAGO
7521 WARD SALES CHICAGO
7566 JONES RESEARCH DALLAS
7654 MARTIN SALES CHICAGO
7698 BLAKE SALES CHICAGO
7782 CLARK ACCOUNTING NEW YORK
7839 KING ACCOUNTING NEW YORK
7844 TURNER SALES CHICAGO
7900 JAMES SALES CHICAGO
7902 FORD RESEARCH DALLAS
...
## NATURAL JOIN (공통컬럼 조건 생략)
예4) 사원번호,이름,부서명,부서위치를 조회하시오.
SELECT E.EMPNO,E.ENAME,D.DNAME,D.LOC
FROM EMP E NATURAL JOIN DEPT D;
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7369 SMITH RESEARCH DALLAS
7499 ALLEN SALES CHICAGO
7521 WARD SALES CHICAGO
7566 JONES RESEARCH DALLAS
7654 MARTIN SALES CHICAGO
7698 BLAKE SALES CHICAGO
7782 CLARK ACCOUNTING NEW YORK
7839 KING ACCOUNTING NEW YORK
..
2) OUTER JOIN
형식)
SELECT 컬럼명,..
FROM 테이블1 LEFT OUTER JOIN | RIGHT OUTER JOIN | FULL OUTER JOIN 테이블2
ON 조인조건절
[WHERE]
예1) 모든 부서번호,부서명,근무사원이름,급여조회
SELECT D.DEPTNO,D.DNAME,E.ENAME,E.SAL
FROM EMP E RIGHT OUTER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO;
DEPTNO DNAME ENAME SAL
---------- -------------- ---------- ----------
20 RESEARCH SMITH 800
30 SALES ALLEN 1600
30 SALES WARD 1250
20 RESEARCH JONES 2975
30 SALES MARTIN 1250
30 SALES BLAKE 2850
10 ACCOUNTING CLARK 2450
10 ACCOUNTING KING 5000
30 SALES TURNER 1500
30 SALES JAMES 950
20 RESEARCH FORD 3000
DEPTNO DNAME ENAME SAL
---------- -------------- ---------- ----------
10 ACCOUNTING MILLER 1300
40 OPERATIONS
예2) 학생번호,이름,과목,점수를 조회해 보세요.(모든 학생을 조회)
SELECT S.SNUM, S.NAME, G.SUBJECT, G.SCORE
FROM STUDENT S LEFT OUTER JOIN GRADE G
ON S.SNUM=G.SNUM;
Q1. EMP 와 DEPT TABLE을 JOIN하여 부서번호,부서명,이름,급여를 출력하라.
SELECT E.DEPTNO, D.DNAME, E.ENAME, E.SAL
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO;
Q2. 'ALLEN'의 직무와 같은 사람의 이름,부서명,급여,직무를 출력하라.
SELECT E.ENAME, D.DNAME, E.SAL, E.JOB
FROM EMP E JOIN DEPT D
USING(DEPTNO)
WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='ALLEN');
Q3. 전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름, 부서명, 입사일, 지역, 급여를 출력하라.
SELECT E.EMPNO, E.ENAME, D.DNAME, E.HIREDATE, D.LOC, E.SAL
FORM EMP E NATURAL JOIN DEPT D
WHER SAL>(SELECT AVG(SAL) FROM EMP);
(8) UNION,UNION ALL
- 두 집합의 결과를 더함
- UNION : 중복데이터를 제거(중복값을 제거하기 위해 정렬해서 수행하므로 성능이
느려진다)
- UNION ALL : 중복데이터를 포함
- 규칙 : 두 집합의 SELECT절에 오는 컬럼의 갯수와 타입이 동일해야 하며 컬럼명은
달라도 상관없다.
예1)
SELECT DEPTNO, ENAME, SAL
FROM EMP WHRE DEPTNO=20
UNION -- 이거 쓰면 위, 아래 두개(결과값?)가 합쳐짐
SELECT DEPNO, ENAME, SAL
FROM EMP WHERE DEPTNO=10;
예2)
SELECT DEPTNO, ENAME, JOB
FROM EMP WHERE DEPTNO=30
UNION
SELECT DEPTNO, DNAME, LOC
FROM DEPT;
예3)
SELECT DEPTNO, ENAME, SAL
FROM EMP WHERE DEPTNO=20
UNION ALL -- 중복데이터 포함
SELECT DEPTNO, ENAME, SAL
FROM EMP WHERE DEPTNO=10 OR DEPTNO=20
ORDER BY DEPTNO;-
DML - 데이터 조작어(데이터입력-INSERT, 수정-UPDATE, 삭제_DELETE)
<1> INSERT
-
데이터를 추가할때 사용
-
INSERT,UPDATE,DELETE 구문은 DB에 반영할때는 COMMIT,
취소할때는 ROLLBACK을 사용한다. -
형식
INSERT INTO 테이블명(컬럼명1,컬럼명2,..)
VALUES(값1,값2,..)예1) 부서테이블에 부서정보 추가하기
INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES(50, '개발부', '종로');
COMMIT;예2) 모든 컬럼값을 추가할때는 컬럼명을 생략해도 됨
INSERT INTO DEPT VALUES(51, '인사부', '강남');
COMMIT;예3) 제약조건에 주의하기
INSER INTO DEPT VALUES(51, '부서1', '강남'); -- 에러남예4) EMP테이블에 사원정보 추가하기
INSERT INTO EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES(8000, '홍길동', '영업', 7369, SYSDATE, 4000, 0, 50);
COMMIT;INSERT INTO EMP(EMPNO, ENAME, SAL, DEPTNO) VALUES(8001, '김길동', 3000, 30); COMMIT; INSERT INTO EMP(EMPNO, ENAME, SAL, DEPTNO) VALUES(8002, '삼길동', 3000, 60); -- 오류 --> 부모테이블(DEPT)에 60번 부서(DEPTNO)가 존재하지 않으므로 오류Q1) STUDENT테이블과 GRADE테이블에 각각 데이터를 2개이상 추가해 보세요.
--학생정보테이블
CREATE TABLE STUDENT
(
SNUM NUMBER(5) PRIMARY KEY, -- 기본키(중복X, NULL 값X)
NAME VARCHAR2(10),
PHONE VARCHAR2(20)
);
--성적테이블(성적번호, 학생번호, 과목명, 점수)
DROP TABLE GRADE;
CREATE TABLE GRADE;
(
GNUM NUMBER(5) PRIMARY KEY,
SNUM NUMBER(5) PEFERENCES STUDENT(SNUM), -- 참조키 설정
SUBJECT VARCHAR2(10),
SCORE NUMBER(3)
);
INSERT INTO STUDENT VALUES(103, '이길동', '010');
INSERT INTO STUDENT VALUES(104, '삼길동', '011');
COMMIT;INSERT INTO GRADE VALUES(4,103, '영어', 100); INSERT INTO GRADE VALUES(5,103, '수학', 50); COMMIT;
<2> DELETE문
- 데이터를 삭제할때 사용
- 형식)
DELETE FROM 테이블명 조건절;예1) 51번 부서 삭제하기
DELETE FROM DEPT WHERE DEPTNO=50; 예2) 'ALLEN' 사원삭제하기 -> ROLLBACK
-
예3) 제약조건에 주의하기
DELETE FROM DEPT WHERE DEPTNO=50; -- 에러
--> 자신을 참조하는 자식레코드가 존재하면 삭제할 수 없음
--> 자식레코드 삭제 후 자신레코드 삭제하기
DELETE FROM EMP WHERE DEPTNO=50; -- 자식레코드 삭제
DELETE FROM DEPT WHERE DEPTNO=50; -- 자신레코드 삭제
COMMIT;
Q1) DEPT테이블에서 부서번호가 41번 이상인 데이터를 모두
삭제하시오.(COMMIT)
DELETE FROM DEPT WHERE DEPTNO>=41;
COMMIT;
Q2) EMP테이블에서 사원이름이 'ALLEN'이거나 급여가 3000이상인
데이터를 삭제하세요.(ROLLBACK)
DELETE FROM EMP WHERE ENAME='ALLEN' OR SAL>=3000;
ROLLBACK;
Q3) DEPT테이블에서 부서번호가 20번인 부서를 삭제해 보세요(ROLLBACK)
DELETE FROM EMP WHERE DEPTNO=20;
DELETE FROM DEPT WHERE DEPTNO=20;
ROLLBACK;
<3> UPDATE
- 데이터를 수정할때 사용
- 형식
UPDATE 테이블명
SET 컬럼명=수정값,컬럼명=수정값,..
WHERE 조건절;
예1) 사원이름이 'ALLEN'인 사원의 부서를 20번으로 급여를
3000으로 수정(ROLLBACK)
UPDATE EMP SET DEPTNO=20, SAL=3000 WHERE ENAME='ALLEN';
ROLLBACK;
예2) 부서번호가 10번이고 급여가 2000이상인 사원들의 부서번호를
30번으로 수정하기(ROLLBACK)
UPDATE EMP SET DEPTNO=30 WHERE DEPTNO=10 AND SAL>=2000;
ROLLBACK;
Q1) DEPT테이블의 10번 부서의 위치(LOC)를 '서울'로 변경해
보세요.
UPDATE DEPT SET LOC='서울' WHERE DEPTNO=10;
Q2) 사원번호가 7369인 사원의 JOB,SAL,COMM을 임의의 값으로
변경해 보세요.
UPDATE EMP SET JOB='영업', SAL=2000, COMM=100 WHERE EMPNO=7369;
Q3) 10번 부서 사원들의 급여를 10% 인상하세요.
UPDATE EMP SET SAL=SAL*1.1 WHERE DEPTNO=10;
ROLLBACK;
#참고->DML 작업시 자동형변환이 이루어짐
UPDATE EMP SET JOB='영업', SAL='2000', COMM=100, HIREDATE='2021/04/12' WHERE EMPNO=7369;
-> 작업 후 ROLLBACK 하세요.-
TCL (Transaction Control Language)
-
트랜잭션 : 분리되어서는 안되는 논리적 작업 단위
-
TCL : DML(INSERT,UPDATE,DELETE) 문이 실행되어
DBMS에 저장되거나 되돌리기 위해 실행해야 하는 SQL -
트랜잭션의 시작
1) DBMS를 처음 접속했을때
2) COMMIT 또는 ROLLBACK했을때
3) DDL(Create,drop,..)등이 실행되었을때 -
트랜잭션 종료
1) COMMIT 또는 ROLLBACK했을때
2) DDL(Create,drop,..)등이 실행되었을때
3) DB가 정상적으로 종료될때 -
TCL의 특성
1) 읽기일관성/잠금현상
어떤 사용자가 변경중인 행을 다른 사용자가 변경할 수 없게
하는 기술로써 변경중인 사용자에 의해 COMMIT 또는 ROLLBACK
이 실행된 후 변경되는 특성
예)
-- 사용자A
UPDATE DEPT SET LOC='서울' WHERE DEPTNO=65;--사용자B
DELETE FROM DEPT WHERE DEPTNO=65;
==> 사용자 A가 COMMIT 또는 ROLLBACK을 하기전까지 LOCK이 걸린다.-
TCL의 종류
1) COMMIT : SQL문의 결과를 영구적으로 DB에 반영
2) ROLLBACK : SQL문의 결과를 취소
3) SAVEPOINT : 트랜잭션의 한 지점에 표시하는 임시 저장점예1)
-- DB 접속 (트랜잭션 시작)
INSERT INTO DEPT VALUES(60, '부서1', '서울');
INSERT INTO DEPT VALUES(61, '부서2', '부산');
COMMIT; -- (트랜잭션 종료/새로운 트랜잭션 시작)
INSERT INTO DEPT VALUES(62, '부서2', '부산');
INSERT INTO DEPT VALUES(63, '부서2', '부산');
ROLLBACK; -- 62번/53번 부서 작업 취소
예2)
INSERT INTO DEPT VALUES(62, '부서3', '제주');
CREATE TABLE TEST(NUM NUMBER); -- DDL 구문이 실행되면 트랜잭션이 종료되면서 자동 COMMIT이 실행된다.예3)
INSERT INTO DEPT VALUES(64, '부서5', '제주');
INSERT INTO DEPT VALUES(65, '부서6', '제주');
SAVEPOINT A; -- SAVEPOINT 설정INSERT INTO DEPT VALUES(66, '부서7', '경기');
INSERT INTO DEPT VALUES(67, '부서8', '대전');
ROLLBACK TO A; -- A지점 이후(66번/67번 부서)부터 작업 취소 -
-
DDL구문 ( CREATE,DROP,ALTER,..)
-
테이블생성
1) 형식
CREATE TABLE 테이블명
(
컬럼명 데이터형,
컬럼명 데이터형,
...
);
2) 기본자료형
- NUMBER : 숫자(정수,실수)
예)
NUM NUMBER(5) --> 정수:5자리
AVER NUMBER(6,2) --> 실수:전체 6자리,소수점이하 2자리-
CHAR : 고정길이 문자열
예) JUMIN CHAR(13) -
VARCHAR2 : 가변길이 문자열(4000 Bytes)
예) ADDR VARCHAR2(100) -
DATE : 날짜,시간
예) HIREDATE DATE -
TIMESTAMP : 날짜,시간(시/분/초/밀리초)
-
LONG : 가변길이 문자열(2G Bytes)
-
RAW : 가변길이 바이너리 데이터(2000 bytes)
-
LONG RAW : 가변길이 바이너리 데이터(2G Bytes)
-
BLOB : 바이너리 데이터(4G Bytes)
-
CLOB : 문자데이터(4G Bytes)
예) DATA CLOB예) 회원테이블만들기
- 아이디 (기본키) -
비밀번호
-
이메일
-
나이
-
가입일
CREATE TABLE MEM
(
ID VARCHAR2(20) PRIMARY KEY,
PWD VARCHAR2(15),
EMAIL VARCHAR2(20),
AGE NUMBER(3),
REGDATE DATE
);-- 임의의 회원정보를 3명 추가 해보세요.
-- 회원정보를 임의의 데이터로 수정해 보세요.
-- 임의의 조건에 맞는 회원을 삭제해 보세요.3) 테이블삭제
DROP TABLE 테이블명;DROP TABLE MEM;
DROP TABLE STUDENT; -- 에러
-- 자식테이블이 존재하면 삭제가 안됨 -> 자식테이블을 먼저 삭제한다.DROP TABLE GRADE; -- 자식테이블 삭제
DROP TABLE STUDENT; -- 부모테이블 삭제4) 테이블 구조 변경하기
<1> 컬럼추가ALTER TABLE 테이블명 ADD(컬럼명 타입);
예) ALTER TABLE EMP ADD(EMAIL VARCHAR2(15));<2> 컬럼변경ALTER TABLE 테이블명 MODIFY(컬럼명 타입);
예) ALTER TABLE EMP MODIFY(EMAIL VARCHAR2(20));<3> 컬럼명 변경ALTER TABLE 테이블명 RENAME COLUMN 컬럼명 TO 바꿀컬럼명;
예) ALTER TABLE EMP RENAME COLUMN EMAIL TO MAIL;<4> 컬럼삭제ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
예) ALTER TABLE EMP DROP COLUMN MAIL; -
