-
CREATE TABLE T1STUDENT1 ( SNO NUMBER(12), --NULL값 허용 안한다, NAME VARCHAR2(60) NOT NULL, -- 한글 20글자까지 허용 PHONE NUMBER(12), MNO NUMBER(12), CONSTRAINT PK_SNO PRIMARY KEY (SNO), -- 정석 CONSTRAINT FK_MNO foreign KEY (MNO) REFERENCES MAJOR1 (MNO) ON DELETE SET NULL -- 특정 부분 NULL로 SET. / CASCCADE -전부 삭제 ) ;처음 TABLE을 생성할 때, 제한 조건을 설정하는 방법이야
CONSTRAINT (NAME) PRIMARY KEY (COLUMN) -- Primary key는 unique 와 not null의 특징을 자동으로 갖는다.
CREATE TABLE MAJOR1 ( MNO NUMBER(12) PRIMARY KEY, MNAME VARCHAR(20) NOT NULL UNIQUE, --쉼표 사용 유의하자. PHONE NUMBER(12), LOCATION VARCHAR(20) ) ;쉼표를 쓰는 타이밍을 잘 알아야해
하나의 Column에 대한 수식에는 띄어쓰기만 하고
Column을 구분하는 것이 쉼표라는 것 잘 숙지해
-- CONSTRAINT 이름 ---- -- PRIMARY KEY [COLUMN] ---- NOT NULL ---- UNIQUE --FOREIGN KEY [COLUMN] ---- REFERENCES 참조테이블 [참조COLUMN] ---- ON DELETE CASCADE / SET NULL -- CONSTRAINT PK_SNO기본형의 모습
JOIN ?
- 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법
- 일반적으로 테이블의 식별 값인 PRIMARY KEY와 테이블 간 공통 값인 FOREIGN KEY값을 사용하여 JOIN한다.
--SELF JOIN -- SELECT E.ENAME, M.ENAME "MANAGER" FROM EMP E, EMP M WHERE M.MGR = E.EMPNO ; SELECT E.ENAME, M.ENAME MANAGER FROM EMP E JOIN EMP M ON E.EMPNO = M.MGR ; SELECT E.ENAME, M.ENAME MANAGER FROM EMP E JOIN EMP M ON E.MGR = M.EMPNO -- 이거 출처 다르게 하면 값 완전 이상하게 나오네! ;테이블을 연결하는 데, 스스로랑 연결을 해서 뽑아내는 방법이다
사원 담당 매니저가 사번으로 출력이 되어있어서, 이를 명확히 보고자 이러한 SELF JOIN을 이용한다.
EMP를 E와 M으로 정의하면서, M에 있는 MGR의 VALUE값이 숫자이고, E에 있는 EMPNO의 VALUE값도 숫자이기에
WHERE문 속 연산자로 조건을 주어 두 테이블(하나의 테이블을 복제해 두 개로 만든)을 JOIN 한다.
하지만 주의해야할 것은 어떤 테이블의 값을 가져오느냐인데,
앞의 2개의 TRANSACTION은 매니저의 이름들이 E.ENAME으로 출력이 된다.
그러나 내가 원하는 출력의 모습은
E.ENAME에는 직원들의 이름이 나열이 되고, M.ENAME MANAGER에 각 직원들에게 해당되는 매니저의 이름들이 나열이 되는 것이다.
그렇기에 WHERE 문에 (바꿀 곳이 여기 뿐이기에) 테이블들을 바꿔야 한다.
1, 2번의 TRANSACTION은
-- E.ENAME = E.EMPNO = M.MGR
-- M.ENAME = M.MGR = E.EMPNO.3번의 TRANSACTION은
-- E.ENAME = E.MGR = M.EMPNO
-- M.ENAME = M.EMPNO = E.MGR.
의 과정을 거치게 되면서 각각의 출력값이 나타난다.
그러므로,
마지막 TRANSACTION이 우리가 예상할 수 있는 데이터가 출력이 된다.
--OUTER JOIN-- (어디를 기준으로 할것인지?) SELECT E.EMPNO, E.ENAME, D.DNAME FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO = D. DEPTNO ; SELECT E.EMPNO, E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO(+) = D. DEPTNO -- (+) 가 의미하는 바? ; SELECT E.EMPNO, E.ENAME, D.DNAME FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D. DEPTNO ; SELECT E.EMPNO, E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D. DEPTNO(+) -- (+) 가 의미하는 바? ;EQUI JOIN을 사용할 경우, 한 쪽 COLUMN에 VALUE가 없다면 해당 행은 조회되지 않는데,
이 문제를 해결하기 위해 OUTER JOIN을 사용한다.
JOIN 조건을 만족하지 못하는 행들도 조회할 수 있기 때문인데,
이러한 만족하지 못하는 VALUE가 있어도 조회하려는 쪽에 '(+)'를 붙인다.
SELECT TEST1.NO NO1, TEST2.NO NO2 FROM TEST1 FULL OUTER JOIN TEST2 ON TEST1.NO = TEST2.NO --같은 것끼리 묶어주고, 아닌거 ~ ; SELECT TEST1.NO NO1, TEST2. NO NO2 FROM TEST1 LEFT OUTER JOIN TEST2 ON TEST1.NO = TEST2.NO ; SELECT TEST1.NO NO1, TEST2. NO NO2 FROM TEST1 RIGHT OUTER JOIN TEST2 ON TEST1.NO = TEST2.NO ; -- 이 3개 차이점 확실히 인지하고 넘어가기 SELECT TEST1.NO NO1, TEST2.NO NO2 FROM TEST1 CROSS JOIN TEST2 ; -- 이거까지보통 JOIN은 두 테이블을 연결할 수 있는 연결고리로 ON을 사용하는데, CROSS JOIN 은 필요 없다.
FULL은 말 그대로 다 표현해준다고 보면 된다.
양 쪽에 (+)를 붙인거랑 같은 모양일 듯!!
SELECT E.EMPNO, E.ENAME, D.DNAME FROM EMP E JOIN DEPT D --ON E.DEPTNO = D.DEPTNO USING(DEPTNO)--반드시 같은 이름 column ;USING을 쓰게되면 테이블명을 쓰면 안되니까, SELECT에 있는 COLUMN들에게서도 그걸 빼야해!
SELECT E.EMPNO, E.ENAME, D.DNAME FROM EMP E NATURAL JOIN DEPT D ; -- 같은 이름의 COLUMN이면 사용 가능, where절 없이 join 가능, -- 그래서 join 조건의 제어가 어렵다는 단점NATURAL JOIN도 ON 필요 없는데, 그래서 제어가 잘 안된대;;
<SUB QUERY>
정의는
하나의 SQL 문장 내에 또 다른 SQL 문장이 있는 쿼리문
주절, 종속절 처럼, 주된 문장을 MAIN QUERY or OUTER QUERY ,
종속된 문장을 SUB QUERY or INNER QUERY 라고 칭한다.
--SUB QUERY -- --평균 급여보다 많은 급여를 받는 사원 조회 SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL >= ( SELECT AVG(SAL) FROM EMP ) ; SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL >= AVG(SAL) --이게 안되는 이유: AVG(SAL)의 출처를 알 수 없다는 이유! 그래서 SELECT로 EMP안에서 꺼내서 써. ;단일 행의 SUB QUERY로, 그 결과 값이 1개의 행만 나오는 것.
MAIN QUERY의 WHERE절에서 단일 행 연산자 ( =, <>, >, >=, <, <=)를 이용
--다중 행 SUB QUERY -- --부서 별 급여를 제일 많이 받는 사원 조회 SELECT DEPTNO, EMPNO, ENAME, SAL FROM EMP -- JOIN DEPT USING (DEPTNO) 굳이왜? WHERE SAL IN ( SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO) ; --이럴 경우 20에 최대값이 아닌데, 30에 최대값과 그 값이 같으면 출력되는 오류가 발생. -- > 어떻게 수정해야 '유일값'이 등장을 할까? -- 각각의 DEPT에 각각의 식을 넣는경우 -- 서브쿼리의 위치에 따라 변화되니까 잘 생각해봐 -- 정확히 2개가 맞아 떨어져야 하는 조건 (DEPT과 SAL이 맞는 경우) -- 테이블 형태로 2개를 한꺼번에?!다중 행의, SUB QUERY의 결과가 두 건 이상 출력되는 것
앞서 언급한 단일 행 연산자는 사용할 수 없으며,
다중 행 연산자 (IN, NOT IN, ANY(SOME), ALL, EXISTS)만 사용 가능
- 문제에 대한 해답을 생각해보는데, 각 기능들에 대한 개념이 잘 잡혀있지 않아
내가 원하는 기능들을 뽑아낼 수가 없었다.
기본이 되는 개념들에 대한 지식을 좀 더 견고히 다져야할 필요가 느껴졌다.
SELECT DEPTNO, EMPNO, ENAME, SAL FROM EMP WHERE SAL = ( SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO --여기서 나올값이 3개여서 IN을 쓸 수 밖에 없어 ) AND DEPTNO = ( SELECT DEPTNO FROM EMP GROUP BY DEPTNO) ;단일 행 연산자로 시도, BUT FAIL
SELECT E.DEPTNO, E.EMPNO, E.ENAME, E.SAL FROM EMP E, (SELECT MAX(SAL) MAXSAL, DEPTNO FROM EMP GROUP BY DEPTNO) EX -- 굳이 여기 이미 존재하는 테이블이 안와도 되네 WHERE E.DEPTNO = EX.DEPTNO AND E.SAL = EX.MAXSAL ;단일 행 연산자로 시도, BUT SUCCESS
WHY? - SUB QUERY의 위치를 변경해서 하나의 테이블로 인식하게끔 만들었어
SELECT E.DEPTNO, E.EMPNO, E.ENAME, E.SAL FROM EMP E JOIN (SELECT MAX(SAL) MAXSAL, DEPTNO FROM EMP GROUP BY DEPTNO) EX ON E.DEPTNO = EX.DEPTNO --JOIN이면 ON 쓰고 그 다음 WHERE WHERE E.SAL = EX.MAXSAL ;위에 있는 TRANSACTION을 그냥 JOIN문으로 만들었는데, ON은 하나밖에 못쓰니까 그 다음은 WHERE절로 추가한 것을 보여주기 위해 가져왔어.
SELECT DEPTNO, E.EMPNO, E.ENAME, E.SAL --USING 썼으면 여기도 고쳐야돼. USING( )안에는 테이블안들어가거든 FROM EMP E JOIN (SELECT MAX(SAL) MAXSAL, DEPTNO FROM EMP GROUP BY DEPTNO) EX USING(DEPTNO) --ON E.DEPTNO = EX.DEPTNO --JOIN이면 ON 쓰고 그 다음 WHERE WHERE E.SAL = EX.MAXSAL ;ON을 빼고 USING을 썼는데, USING안에는 테이블 명이 들어가면 안돼,
그래서 SELECT에 있는 것도 테이블 빼야해 근데 첫 번째만 빼면 되나? 나머지 것도 다 빼야하나? -- 질문
답: 해당되는 값의 테이블만 빼면 된다!
SELECT DEPTNO, EMPNO, ENAME, SAL FROM EMP E WHERE SAL = ( SELECT MAX(SAL) FROM EMP WHERE E.DEPTNO = DEPTNO ) ; -- 안에 있는것만 실행시키면 안돌아가,ㅇ
요건 조금 더 복잡한 거야
조건을 하나 더 추가해서 MAX(SAL)값에 넣고 이와 같은 것들을 실행하는 거지,
대신 그 MAX(SAL) 값의 DEPTNO와 MAIN QUERY의 DEPTNO가 같아야하는 거지!
--한 번 더 보자 여긴
--ANY,SOME SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL > ANY(1600, 1250, 1500) --IN이랑 비슷한 유형이라고 보면 된다 ; SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL > SOME(1600, 1250, 1500) --IN이랑 비슷한 유형이라고 보면 된다 ;ANY와 SOME은 값이 같아,,,
ANYTHING이나 SOMETHING이나 어감의 차이니까,,,?
--EXISTS SELECT EMPNO, ENAME, SAL FROM EMP E WHERE EXISTS(SELECT EMPNO FROM EMP WHERE E.EMPNO=MGR) ;존재하는 것만 뽑아낸다는 뜻인 것 같아.
아주 직관적이야.
--다중열 서브쿼리 --부서 번호가 30인 사원들의 급여와 부서번호를 묶어 메인쿼리로 전달 SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE (DEPTNO, SAL) IN ( SELECT DEPTNO, SAL FROM EMP WHERE DEPTNO = 30 ) ;WHERE문에 이제 2개가 들어가기 시작해서, 더 많은 조건을 간편하게 추가할 수 있을 듯한 느낌이 들어,,,,