멍발자의 개발
SQL (2) 본문
SELECT 구절
* 뉴렉처 오라클 SQL 강의를 공부하고 작성한 글입니다. (29강~)
순서 기억 : SELECT, FROM , WHERE, GROUP BY, HABING, ORDER BY
정렬순서 : ASC, DESC
ex) 이름을 기준으로 역순으로 정렬해서 조회하시오.
SELECT * FROM MEMBER ORDER BY NAME DESC;
집계 함수와 GROUP BY
집계 : COUNT, SUM, AVG, MAX, MIN
ex) SELECT WRITER_ID, COUNT(ID) COUNT
FROM NOTICE
GROUP BY WRITER_ID
ORDER BY COUNT DESC;
순서 : FROM -> CONNECT BY -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
HAVING 절
WHERE 절에서는 집계 함수를 사용할 수 없다.
ex) 회원별 게시글 수를 조회하시오. 게시글이 2이하인 레코드만 출력하시오.
SELECT WRITER_ID, COUNT(ID) FROM NOTICE
GROUP BY WRITER_ID
HAVING COUNT(ID) <= 2
순위 함수
ROW_NUMBER(), RANK(), DENSE_RANK()
SELECT ROW_NUMBER() OVER(ORDER BY HIT) , ID, TITLE, WRITER_ID, REGDATE, HIT
FROM NOTICE;
=> 정렬된 상태에서 ROW_NUMBER() 함수로 인해 일련번호가 붙게 된다.
SELECT RANK() OVER(ORDER BY HIT) , ID, TITLE, WRITER_ID, REGDATE, HIT
FROM NOTICE;
=> 일련 번호가 아닌 등수로 바뀐다.
부조회(서브쿼리)
구절의 순서를 바꿔야 하는 경우
SELECT * FROM MEMBER WHERE ROWNUM BETWEEN 1 AND 10
최신 등록순으로 정렬한 결과에서 상위 열명을 원하는 경우라면?
SELECT * FROM ( SELECT * FROM MEMBER ORDER BY REGDATE DESC)
WHERE ROWNUM BETWEEN 1 AND 10;
나이가 30 이상인 회원 목록을 조회하시오
SELECT * FROM MEMBER
WHERE AGE >= 30
평균 나이 이상인 회원 목록을 조회하시오
SELECT * FROM MEMBER
WHERE AGE >= (SELECT AVG(AGE) FROM MEMBER)
INNER 조인(JOIN)
SELECT * FROM MEMBER;
SELECT * FROM NOTICE;
ex) 참조키를 기준으로 일치하는 행만 조인
SELECT *
FROM MEMBER INNER JOIN NOTICE ON MEMBER.ID = NOTICE.WRITER_ID;
서로 관계가 있을 때 : INNER
서로 관계가 없을 때 : OUTER
OUTER를 제거한 상태에서 INNER 조인을 한다.
LEFT/RIGHT/FULL - OUTER JOIN
참조키를 기준으로 일치하지 않는 행도 포함시키는 조인
LEFT : 왼쪽 OUTER 테이블 포함
RIGHT : 오른쪽 OUTER 테이블 포함
FULL : 양쪽 OUTER 테이블 포함
SELECT * FROM NOTICE LEFT OUTER JOIN MEMBER M ON N.WRITER_ID = M.ID;
OUTER JOIN을 이용한 게시글 목록 조회
조인된 테이블 쿼리하기
<내부 조인 연습>
ex) 게시글의 컬럼명을 식별하기 위해 테이블명을 사용
SELECT ID,NAME,NAME FROM
MEMBER
INNER JOIN NOTICE ON MEMBER.ID = NOTICE.WRITER_ID
ex) 컬럼을 지정하는 식별자를 줄이기 위해서 테이블의 별칭을 사용
SELECT N.ID, N.WRITER_ID, M.NAME
FROM MEMBER M INNER JOIN NOTICE N
ON M.ID=N.WRITER_ID
ex) ID, NAME 그리고 회원별 작성한 게시글 수를 조회하시오
SELECT M.ID, M.NAME, COUNT(N.ID) FROM
MEMBER M INNER JOIN NOTICE N
ON M.ID = N.WRITER ID
GROUP BY M.ID, M.NAME => 게시글 0인 회원이 빠진 잘못된 쿼리
SELECT M.ID, M.NAME, COUNT(N.ID) FROM
MEMBER M LEFT INNER JOIN NOTICE N
ON M.ID = N.WRITER ID
GROUP BY M.ID, M.NAME
SELF JOIN
자기가 자기와 합쳐진다.
데이터가 서로 포함 관계를 가지는 경우 : 담당, 구성, 연락
SELECT M.*, B.NAME BOSS_NAME(별칭)
FROM MEMBER M LEFT OUTER JOIN MEMBER B ON B.ID = M.BOSS_ID;
오라클 OLD JOIN
<Oracle INNER JOIN 구문>
SELECT N.ID, N.TITLE, M.NAME
FROM MEMBER M, NOTICE N
WHERE M.ID = N.WRITER_ID AND M.ID='newlec';
<ANSI OUTER JOIN 구문>
SELECT N.*, M.NAME WRITER_NAME
FROM NOTICE N JOIN MEMBER M ON M.ID = N.WRITER_ID;
유니온(UNION)
레코드를 합치는 작업 (JOIN은 컬럼끼리)
관련이 없어도 레코드를 합칠 수 있다.
컬럼의 갯수만 맞춰 주면 된다.
ex)
SELECT ID, NAME FROM MEMBER
UNION
SELECT WRITER_ID, TITLE FROM NOTICE;
=> 두 개의 레코드를 합치는 작업
MINUS = 레코드를 뺀다.
INTERSECT = 공통 분모만 남는다.
UNION ALL = 모두 남기고 싶다.
데이터 딕셔너리
사용자 정보 저장 공간 : 데이터 딕셔너리
SELECT * FROM DICT;
도메인 제약조건
도메인 제약 : 데이터에 제약을 걸겠다.
데이터가 제약 없이 들어가게 되면 유효하지 않은 데이터도 막 들어갈 수 있기 때문이다.
=> 무결성 유지를 위함이다.
제약 조건
1) 도메인
2) 엔티티
3) 릴레이션
속성에 도메인이 아닌 값이 올 수 없도록 하는 제약조건
NOT NULL / DEFAULT (기본값)/ CHECK
NOT NULL = 반드시 값을 넣어야 한다. 값이 없을 경우 에러.
DEFAULT = 굳이 직접 입력할 필요가 없는 컬럼들 (ex. 등록일, 조회수, 관리자가 부여하는 비번)
체크 제약조건
CHECK : 값의 제약조건 (ex. 전화번호)
PHONE VARCHAR2(200) CHECK(PHONE LIKE '010-%-____') NOT NULL
정규식을 이용한 체크 제약조건
ALTER TABLE MEMBER
DROP CONSTRAINT MEMBER_PHONE_CHK1;
SELECT * FROM USER_CONSTRAINTS;
WHERE TABLE_NAME='MEMBER';
ALTER TABLE MEMBER
ADD CONSTRAINT MEMBER_PHONE_CHK1 CHECK(REGEXP_LIKE(PHONE, '01[01]-\d{3,4}-\d{4,3});
Entity 제약조건 Primary Key / Unique
중복된 레코드가 없도록 제한한다.
식별 컬럼 = 중복이 발생하지 않도록 관리한다.
식별키 = Primary key = null이 들어가서는 안 된다. = 기본키
UNIQUE = not null이 필수적인 것은 아니지만 식별도 가능하다. = 중복 불허
ex) CONSTRAINT NOTICE_ID_PK PRIMARY KEY(ID),
CONSTRAINT NOTICE_WRITER_ID_UK UNIQUE(WRITER_ID)
시퀀스(Sequence)
= 일련번호
일련번호기 때문에 중복 x, 수가 하나씩 늘어야 한다.
만드는 법 ex) : 시퀀스 > 새 시퀀스 > 이름 설정 > 다음으로 시작 : 1 >
증분 : 1 > 캐시 : 지정되지 않음 > 주기 : 지정되지 않음 > 정렬 : 지정되지 않음