2015 IT 웹 기반 개발자과정/SQL
SQL 기초 정리
한여름밤의코딩
2015. 11. 21. 12:25
명령어의종류
DQL
(데이터검색조회)
-------------------------------------------------------------------------
SELECT 칼럼명[ALIAS] , 테이블명[ALIAS].*
FROM 테이블명[ALIAS]
WHERE 조건식; => 조건식에서 AND 가 OR보다 우선순위가 높다
SELECT 칼럼명[ALIAS], 칼럼명 + 수식 [ALIAS]
FROM 테이블명;
* from dual; => junk table
----------------------------------------------------------------------------------------------------
FROM => WHERE => SELECT 순으로 진행
----------------------------------------------------------------------------------------------------
SELECT COUNT(*) : 테이블의 한 컬럼에 대한 레코드 개수 출력(NULL 은 개수포함 안됨)
FROM 테이블명;
----------------------------------------------------------------------------------------------------
SELECT COUNT(칼럼명) : 조건식에 맞는 컬럼에 대한 레코드 개수 출력
FROM 테이블명;
WHERE 조건식;
----------------------------------------------------------------------------------------------------
SELECT MAX(컬럼명) : 최대값 => 단, 집합함수이므로 최대값에 해당하는 다른 레코드는 출력불가
, MIN(컬럼명) : 최소값
, ROUND(AVG(컬럼명), 2) : 평균값
FROM 테이블명;
----------------------------------------------------------------------------------------------------
SELECT 컬럼1, 컬럼2, .....
, CASE WHEN 컬럼1 >= 20000 THEN '출력값1' if
WHEN 컬럼1 >= 10000 THEN '출력값2' else if(여러 개 가능)
ELSE '출력값3' else
END AS "ALIAS"
FROM 테이블명;
----------------------------------------------------------------------------------------------------
CREATE SEQUENCE 시퀀스명; : 시퀀스 생성
CREATE SEQUENCE 시퀀스명 : 시퀀스 생성 후 시작숫자 및 증가숫자 설정
START WITH 숫자
INCREMENT BY 숫자;
ALTER SEQUENCE 시퀀스명 : 시퀀스 증가숫자 변경
INCREMENT BY 숫자;
INSERT INTO 테이블명(NO, 컬럼2, 컬럼3) : INSERT에 적용하면 자동으로 다음수 입력
VALUES(SEQUENCE명.NEXTVAL, '컬럼2값', 컬럼3값);
ALIAS 의 사용
1. SELECT 컬럼명 AS ALIAS사용: SELECT ID AS 아이디
2. SELECT 컬럼명 ALIAS사용 : SELECT ID 아이디
3.ALIAS에서 이중부호("")의 사용은 공백이나 대소문자 구분시 사용한다
4.테이블에도 ALIAS를 사용할 수 있다
(테이블명[ALIAS])
=> 칼럼명 선택시 테이블명.칼럼명 으로 사용 가능
*단, 테이블명에 ALIAS를 사용한 경우 ALIAS.칼럼명만 사용가능
산술 연산자의 사용
1. 종류: +, -, *, /, ( )
합성 연산자의 사용
1. 2개의 수직바(||)를 사용한다.
2. 칼럼과 문자 또는 다른 칼럼과 연결시킨다.
3. 문자 표현식의 결과에 의해 새로운 칼럼의 결과를 보여준다.
<SQL> 연산자의 사용
1. BETWEEN a AND b : a와b 사이의 값을 찾습니다.
2. IN( list ) : list에 나열되는 값 중하나만 일치되면 됩니다.
3. LIKE ‘비교문자열’ : 비교문자열과 형태가 일치하면 됩니다.
% : 0개이상의어떤문자를의미합니다.
예> name like ‘김%’;
_ : 1개인단일문자를의미합니다.
예> name like ‘김_ _’;
%,_ 둘다 같이 중복사용 가능
* %t% 처럼 앞에 %를 사용할 경우 indexing이 안되어 느릴 수 있음, 사용을 지양
4. IS NULL : NULL값을추출합니다.
IS NOT NULL : NULL이 아닌 값을추출합니다.
5. NVL(칼럼명, 반환값)[ALIAS] : NULL을 지정한 VALUE로 반환하는 함수
* 칼럼명과 반환값의 데이터 타입이 다를경우 TO_CHAR()함수를 사용가능
6. 부정논리 연산자 !=, ^=, <>
7. 부정 SQL 비교 연산자
NOT BETWEEN A AND B
NOT IN ( list )
NOT LIKE ‘비교문자열’
SELECT[DISTINCT] 칼럼명[ALIAS] : 중복값 제거
FROM테이블명
ORDER BY 칼럼명 또는 표현식[ASC or DESC]
----------------------------------------------------------------------
-ASC : 조회한 데이터를 오름차순으로 정렬한다( 디폴트값 생략가능)
-DESC : 조회한 데이터를 내림차순으로 정렬한다.
-ORDER BY 절은 SQL문에서 마지막에 위치한다.
-컬럼 ALIAS명의 사용이 가능하다.
ORDER BY 는 가장 마지막에 실행됨
WHERE => SELECT => ORDER BY
SELECT 컬럼명1, 컬럼명2 FROM 테이블명
WHERE 조건식
ORDER BY 정렬기준 컬럼명;
ex) SELECT * FROM EMPLOYEES : 출력 기대값은 salary별로 정렬해서 10개값을 출력
WHERE ROWNUM <= 10 : BUT 정렬이 가장 마지막에 실행되므로 ROWNUM 10개를 먼저 가져와서 SALARY별로 정렬,
ORDER BY SALARY;
SELECT * FROM ( : 정렬이 먼저 일어나도록 서브쿼리 이용
SELECT * FROM EMPLOYEES
ORDER BY SALARY)
WHERE ROWNUM <= 10;
----------------------------------------------------------------------
집합 함수
SELECT 그룹화할 열 이름1, 그룹화할 열 이름 2, 집계함수 : GROUP BY절도 열 위치를 지정하여 그룹화할 수 있다
FROM 테이블 명
WHERE 조건절
GROUP BY 1, 2
그룹화 데이터 필터링하기
WHERE => GROUP BY => 집합함수 => HAVING
: HAVING은 WHERE 조건절과 마찬가지로 조건을 주는 역할을 한다. 차이는 HAVING은 그룹화된 변수에 대해 조건을 준다는 것이다
SELECT 그룹화할 열 이름1, 집계함수
FROM 테이블 명
WHERE 조건절
GROUP BY 열 이름1
HAVING 집계 함수 조건;
----------------------------------------------------------------------
ex)
SELECT E.* : rownum값을 인식하기 위해 ALIAS 사용하기
FROM( WHERE문이 SELECT문보다 먼저 실행되기 때문
SELECT ROWNUM RNUM, EMPLOYEES.*
FROM EMPLOYEES) E
WHERE RNUM BETWEEN 6 AND 10;
----------------------------------------------------------------------
paging 쿼리
SELECT E.*
FROM(
SELECT ROWNUM RNUM, E1.*
FROM (SELECT *
FROM EMPLOYEES ORDER BY SALARY DESC)
E1
WHERE ROWNUM <=10 -- PAGE * (화면에 보여줄 개수), 글목록이 10만개라면 과부하, 성능 높이기 위해 일부값만
) E 질문 : 페이지가 넘어갈 때마다 rownum값이 늘어나는데 결국 과부하가 될거 같지 않음???
WHERE RNUM BETWEEN 6 AND 10;
SELECT RN, E1.SALARY, FIRST_NAME, LAST_NAME
FROM(
SELECT ROWNUM RN, E.*
FROM
(SELECT * FROM EMPLOYEES ORDER BY SALARY DESC) E
WHERE ROWNUM <=10 : RN 을 안쓰고 ROWNUM을 사용한 이유는 SELECT절의 ALIAS정의가 WHERE절보다 늦게 실행되기 때문,
) E1; 즉, WHERE 문 실행때는 RN이라는 ALIAS가 존재하지 않음
--WHERE RN BETWEEN 5 AND 7;
----------------------------------------------------------------------
ROW_NUMBER() OVER([PARTITION BY 컬럼명] ORDER BY 컬렴명)
RANK() OVER([PARTITION BY 컬럼명] ORDER BY 컬렴명) 순위가 중첩됨
PARTITION BY : 위치/ 순위를 지정하는 그룹 설정
ORDER BY : 어떤 항목에 대해 위치/순위 지정;
ex)
SELECT 컬럼명1, 컬럼명2, 컬럼명3,
ROW_NUMBER() OVER(PARTITION BY 그룹화할 컬럼명 ORDER BY 정렬기준 컬럼명)
FROM 테이블명;
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID
, ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID DESC) RNUM
FROM EMPLOYEES;
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID
, ROW_NUMBER() OVER ( PARTITION BY DEPARTMENT_ID ORDER BY EMPLOYEE_ID DESC) RNUM
FROM EMPLOYEES;
----------------------------------------------------------------------
-NULL값은 오름차순일 경우 가장 마지막에 내림차순은 맨 처음온다.
-날짜형 데이터는 오름차순일 경우 가장 빠른 날이 먼저 출력된다.
-기본적인 정렬방식은 오름차순이다.
----------------------------------------------------------------------
=========================================================================
DML
(데이터조작어)
-------------------------------------------------------------------------
INSERT INTO 테이블명(컬럼명,.....)
VALUES(값,......);
INSERT INTO 테이블명
VALUES(값,.....마지막 값); => 반드시 칼럼의 순서및 갯수와 일치해야함
-------------------------------------------------------------------------
DELETE [FROM] 테이블명 => where 안쓸경우 truncate 와 동일 효과
[WHERE조건]; => 조건식에 부합하는 레코드 삭제
-------------------------------------------------------------------------
UPDATE 테이블명
SET 컬럼명 = 변경할값.....
[WHERE조건];
=========================================================================
=========================================================================
DDL
(데이터정의어)
-------------------------------------------------------------------------
CREATE :
CREATE TABLE 테이블명(
칼럼명1 DATATYPE [DEFAULT 형식],
칼럼명2 DATATYPE [DEFAULT 형식],
. . . .
);
-------------------------------------------------------------------------
ALTER : add, modify, drop
ALTER TABLE 테이블명
1. ADD ( 추가할 칼럼명1 DATATYPE [DEFAULT 형식]
, 추가할 칼럼명2 DATATYPE [DEFAULT 형식] ...
);
2. MODIFY 수정할 칼럼명 DATATYPE [DEFAULT 형식];
3. RENAME COLUMN 기존 칼럼명 TO 수정할 칼럼명;
3. DROP COLUMN 삭제할 칼럼명;
-------------------------------------------------------------------------
RENAME 테이블명 TO 바꿀 테이블명
-------------------------------------------------------------------------
DROP TABLE 테이블명;
-------------------------------------------------------------------------
TRUNCATE TABLE 테이블명;
-------------------------------------------------------------------------
DESCRIBE 테이블명;
DESC 테이블명;
-------------------------------------------------------------------------
TCL
(트랜잭션제어어)
COMMIT, ROLLBACK,
SAVEPOINT
=========================================================================
함수
TO_CHAR(날짜, '포맷문자') yy(년) m(월) d(일) h(시) mi(분) s(초)
TO_CHAR(숫자, '포맷문자') 9(자리수) 0(비어있는 자리 0으로 채움)
,(액수 표현시 3자리마다 , 붙임)
.(실수의 자리수표시)
-------------------------------------------------------------------------
UPPER('abc') : 대문자로 변경
LOWER('ABC') : 소문자로 변경
LENGTH('ABC') : 글자 수 반환
SUBSTR('abc',시작, 반환개수) : 시작하는 수부터 반환개수 만큼 문자열 반환
TRIM(' trim a ') : 좌우 공백 제거
REPLACE('abc', 'a', 'b') : abc 문자열에서 a를 b로 바꿈
INSTR('abcc','c') : c가 처음으로 시작하는 인덱스 반환 // 없으면 0반환
ROUND(123.456, 2) : 소수점 이하 2째짜리까지 표시하고 반올림
(123.456, 0) : 소수점 이하 표시 안함, 반올림
(123.456, -1) : 정수 일의자리 표시, 반올림
TRUNC() : 사용법은 ROUND와 동일, 단 버림
-------------------------------------------------------------------------
ADD_MONTHS(SYSDATE, X) : 현재날짜에서 X개월 더함 //해가 바뀌면 연도+
X가 음수도 가능하다
LAST_DAY(날짜) : 날짜의 마지막 날짜를 반환 ex) 2015.11.31
=> day 만 출력하고 싶다면
TO_CHAR(LAST_DAY(날짜), DD) 마지막 날
NEXT_DAY('날짜', '요일') : 가장 가까운 요일의 날짜 반환
TO_DATE('20151103', 'YYYYMMDD') : 날짜형으로 반환
=========================================================================
JOIN
1) INNER JOIN : 교집합의 의미
| A1 | B1 | C1 | + | C1 | D1 | E1 |
| A2 | B2 | C2 | | C3 | D3 | E3 |
==> | A1 | B1 | C1| D1 | E1 |
-------------------------------------------------------------------------
2) LEFT OUTER JOIN
==> | A1 | B1 | C1 | D1 | E1 |
| A2 | B2 | C2 |NULL|NULL|
-------------------------------------------------------------------------
3) RIGHT OUTER JOIN
==> | A1 | B1 | C1 | D1 | E1 |
|NULL|NULL| C3 | D3 | E3 |
-------------------------------------------------------------------------
4) FULL OUTER JOIN
==> | A1 | B1 | C1 | D1 | E1 |
| A2 | B2 | C2 |NULL|NULL|
|NULL|NULL| C3 | D3 | E3 |
-------------------------------------------------------------------------
ex)
SELECT A.컬럼1, B.컬럼1, A.컬럼2(A와 B테이블의 공유키)
FROM 테이블A a, 테이블B b
WHERE a.컬럼2 = b.컬럼2[(+)];
SELECT A.컬럼1, B.컬럼1, A.컬럼2(A와 B테이블의 공유키)
FROM 테이블A a INNER JOIN 테이블B b
ON a.컬럼2 = b.컬럼2;