sqld 공부 ( 2 과목 : SQL 기본 )

SELECT SUBSTR('123456789', -4, 2) FROM DUAL;

답: 67

 

1앞 0

8 ~ 9 사이 -1

 

실제 인덱스는 다르다

 

아무리 복잡한 쿼리문이 나와도 순서대로 보자

 

F -> W -> G -> H -> S -> O

 

SELECT 다음 ORDER BY 자주 실수 함

 

 

문자열의 비교는 아스키 값으로 비교한다

 

 

연산자 우선순위

산술(+, -) -> 연결( | | ) -> 비교 -> NOT -> AND -> OR

 

 

 

DDL (Data Definition Language)

데이터 정의어

• CREATE
• ALTER
• DROP
• RENAME
• TRUNCATE

DML (Data Manipulation Language)

데이터 조작어

• SELECT
• INSERT
  • INSERT INTO TBL VALUES(1, 100)  ->  TBL 매개변수가 없어 오류
  • INSERT INTO TBL(ID, AMT) VALUES(1, 100)  ->  SUCCESS
• UPDATE
• DELETE
• MERGE

DCL (Data Control Language)

데이터 제어어

• GRANT
• REVOKE

TCL (Transaction Control Language)

트랜잭션 제어어

• COMMIT
• ROLLBACK
• SAVEPOINT

 

 

 

 

 

 

데이터 무결성 종류

• 개체 무결성
  • 기본키는 NULL 또는 중복값 가질 수 없음
• 참조 무결성
  • 외래키 값은 NULL 이거나 참조 테이블의 기본키 값과 동일해야 함.
• 도메인 무결성
  • 속성 값은 정의된 도메인에 속한 값 이어야 함
• NULL 무결성
  • 특정 속성에 대해, NULL 허용하지 않는 특징
• 고유 무결성
  • 특정 속성에 대해, 값이 중복되지 않는 특징
• 키 무결성
  • 하나의 릴레이션에는 적어도 하나의 키가 존재해야 함

 

 

 

 

 

TRIM

• DEFAULT -> BOTH
• LTRIM == LEADING
• RTRIM == TRAILING

• SELECT TRIM(' GOOD ') FROM DUAL;
  • 결과: GOOD
  • 양쪽 공백제거
• SELECT TRIM ( LEADING '가' FROM '가나다라' ) FROM DUAL;
  • 결과: 나다라
  • 앞에서 '가' 제거
• SELECT TRIM ( BOTH '가' FROM '가나다라가' ) FROM DUAL;
  • 결과: 나다라

 

 

 

 

 

 

NULL

• NVL(arg1, arg2)
  • arg1: 컬럼
  • arg2: NULL 치환값
• NULLIF(arg1, arg2)
  • 두 인자가 같으면 null로 반환 
  • arg1: 컬럼
  • arg2: NULL 로 바꿀값
• COALESCE
  • 입력된 순으로 NULL 이 아닌  첫 번째 인자 반환

 

 

 

 

 

 

 

SELECT 문

• FROM 절의 Alias 는 띄어쓰기
• SELECT 절의 Alias 는 AS ( 생략 가능 )

• F W G H S O

 

 

 

 

 

 

WHERE 절

• SELECT, UPDATE, DELETE 에 사용
• INSERT 사용 불가

 

 

 

 

 

 

 

 

GROUP BY 절

• 조건에 따라 그룹으로 분리하여 계산하도록 하는 절
• SELECT 절에 집계함수를 사용하여 그룹연산 결과 표현

• 집계 함수
  • GROUP BY 절로 그룹핑한 후에는 해당 그룹에 대해 집계 함수를 사용하여 개수, 합, 평균등을 계산
  • 그룹 상으로 연산 수행
  • GROUP BY 절 없이 집계함수 사용하면 전체 행 연산 수행
• COUNT
  • 행 수를 계산
• SUN
• AVG
• MIN
• MAX

*를 사용할 수 있는 함수는 COUNT뿐

 

 

 

 

 

 

 

 

HAVING 절

• 그룹 함수 결과를 조건으로 사용할 때 사용
• GROUP BY 연산이 끝난 결과에 대해서 HAVING 절의 조건을 만족하는 그룹만 추출
• 연산부하 ↑

• WHERE 과 HAVING 의 차이점
  • WHERE
    • 집계함수 사용 x
  • HAVING
    • 집계함수 사용 가능
    • GROUP BY 결과를 필터링

 

 

 

 

 

 

 

 JOIN

• SELECT * FROM TEAM A INNER JOIN STADIUM B ON A.ID = B.ID;
• 컬럼명만 사용시 USING 사용
  • SELECT * FROM TEAM A INNER JOIN STADIUM B USING(ID)
• SELECT * FROM TEAM A INNER JOIN STADIUM B ON(ID)
  • 이건 오류다 ON 절에는 조건식이 따라와야 한다.
•  표준조인
  • DBMS 종류에 따라 문법이 달라 그에 맞는 SQL문을 작성해야 하므로 관리가 어려워진다는 문제 
  • 이러한 어려움을 줄이기 위해 ANSI 표준으로 작성  ->  ANSI SQL의 문법에 따른 조인 쿼리
  • INNER JOIN, OUTER JOIN, NATURAL JOIN, CROSS JOIN

 

• INNER JOIN ( 교집합 )
  
  • 조건에 맞는 행들만 병합 
  • FROM 테이블1 INNER JOIN 테이블2 ON 조건식 
    • EQUI JOIN ( 등가 JOIN )
      • =
    • NON EQUI JOIN ( 범위 )
      • 부등식 ( BETWEEN, >, < , >=, <= )
      • BETWEEN A AND B
        • A 보다 크거나 같고 B 보다 작거나 같은  

 

조인 조건에 맞지 않는 행까지 병합

• OUTER JOIN ( 합집합 )
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

 

• NATURAL JOIN
  • 두 테이블에서 같은 이름의 칼럼에 대해서는 동일한 칼럼값을 가지는 행만 병합

NAME	CLUB
김유찬	탁구부
이에나	농구부
정시우	축구부
박준영	야구부

NATURAL JOIN

NAME	CLASS	CLUB
이예나	A	농구부
박준영	B	야구부
정유진	C	탁구부

 

결과

NAME	CLUB	CLASS
이예나	농구부	A
박준영	야구부	B

 

 

 

• CROSS JOIN
  • 모든 경우 다 조합

 

 

 

 

 

함수

 

 

• CHR(arg)
  • arg: ASCII 코드값
  • CHR(97) -> a

 

•  ABS()
  • 절대값
  • ABS(-2) -> 2

 

• MOD()
  • 나머지 반환
  • MOD(9, 2) -> 1

 

• ROUND()
  • 반올림
  • ROUND(2.67, 1) -> 2.7
  • ROUND(2.67) -> 3

 

• TRUNKC()
  • 특정 자릿수 버림 후 그 결과 반환
  • TRUNKC(2.67, 1) -> 2.6
  • TRUNKC(2.67) -> 2

 

• SIGN()
  • 양수면 1, 음수면 -1, 0이면 0 반환
  • SIGN(2.3) -> 1

 

• CEIL()
  • 크거나 같은 최소의 정수 반환
  • CEIL(2.3) -> 3

 

• FLOOR()
  • 작거나 같은 최대의 정수 반환
  • FLOOR(2.3) -> 2

 

 

 

 

 

 

 

1. SELECT TRIM('     가나다라      ') FROM DUAL;

 

2. SELECT TRIM(LEADING '가' FROM '가나다라') FROM DUAL;

 

3. SELECT TRIM(TRAILING '라' FROM '가나다라') FROM DUAL;

 

4. SELECT TRIM('라' FROM '라가나다라') FROM DUAL;

 

5. SELECT LTRIM('    가   ') FROM DUAL;

 

5. SELECT LTRIM('가나다라', '가') FROM DUAL;

 

6. LENGTH('Good M')

 

7. SUBSTR('Good M', 1, 4)

 

8. SUBSTR('??_ERROR_CODE??', 3)

 

 

• SUBSTR(기준, 시작점, 끝점)
• 3 : 시작점이다.
• 리턴값 : _ERROR_CODE??

 

8. REPLACE('GOOD MORNING', 'MORNING', 'Afternoon' )

 

9. REPLACE('GOOD MORNING', 'GOOD ')

 

10. CHR(97)

 

11. ABS(-2)

 

12. MOD(9, 2)

 

13. ROUND(2.67, 1)

 

14. TRUNKC(2.67, 1)

 

15. SIGN(2.3)

 

16. CEIL(2.3)

 

17. FLOOR(2.3)

 

 

 

 

 

 

 

 

• 가나다라
• 나다라
• 가나다
• 가나다
• '가   '
• 나다라
• 6
• Good
• GOOD Afternoon
• MORNING

 

 

 

 

 

 

 

변환함수

 

암시적 형변환과 명시적 형변환

 

명시적 형변환

• 작성자가 형변환을 명시

 

암시적 형변환

• 데이터베이스가 연산 수행하면서 내부적으로 형변환 수행한 것
• 결과를 예측할 수 없음
• 성능 저하
• 에러 발생

 

가급적 명시적 형변환

 

 

 

1. TO_NUMBER

숫자형 변환

 

2. TO_CHAR

문자열형 변환

 

3. TO_DATE

날짜형 변환

 

4. SYSDATE

오늘의 날짜를 날짜형으로 변환

 

5. EXTRACT

• 날짜로부터 년, 월, 일 추출
• EXTRACT(A FROM B)
• A: YEAR / MONTH  / DAY  / HOUR  / MINUTE  / SECOND
• B: 날짜형값
• EXTRACT(DAY FROM SYSDATE)
  • 쿼리문 실행한 시간의 일 추출

 

 

 

 

 

 

 

 

CASE - WHEN - THEN - ELSE - END

 

형식1

CASE 

WHEN 칼럽1 = 값1 THEN 값A

 

형식2
CASE 칼럽1

WHEN 값1 THEN 값A

SELECT DEPTNO,

CASE DEPTNO WHEN 10 THEN 'NEW YORK'

ELSE 'ETC'

END AS CITY

FROM EMP;

 

값이 10 이면 NEW YORK 으로 치환

다른 모든 값은 ETC

컬럼 이름은 CITY

 

 

 

 

IF)

TBL

NUM

100

200

200

500

999

999

 

SELECT NUM FROM TBL

GROUP BY NUM

HAVING COUNT (*) = 2

ORDER BY (CASE WHEN NUM = 999 THEN 0 ELSE NUM END);

 

답)

NUM

999

200

ORDER BY 절에서는 999 가 0 으로 처리

출력은 999 그대로 출력

 

 

 

 

CASE 3 WHEN 1 THEN 'Y' ELSE NULL END

3 이 1 이면 Y 

아님 NULL

 

DECODE(2, 1, 'Y', NULL)

2 == 1 -> Y   아님 NULL

 

 

비교 연산자

 

단일행 비교연산자

• =
• <
• >
• >=
• <=
• IS NULL

 

 

다중행 비교연산자

• IN
  • 리스트 중 하나라도 있음 참
  • IN(10, 20, 30)....
• EXISTS
  • 하나라도 존재하면 참
• ALL
  • 모두 참이면 참
• ANY
  • 하나라도 참이면 참

 

 

 

 

 

부정 비교연산자

 

단일행 부정 비교연산자

 

두 값이 다르면 참

• !=
• ^=
• <>

NULL 이 아니면 참

• IS NOT NULL

 

다중행 부정 비교연산자

 

• NOT IN
  • 리스트 중 동일한 값이 하나도 없으면 참
• NOT EXISTS
  • 하나라도 존재하지 않으면 참

 

IN 차이점 NOT IN

 

C1 IN (10, 20, 30) -> C1 = 10 OR C1 = 20 OR  C1 = 30  

C1 NOT IN (10, 20, 30) -> C1 != 10 AND C1 != 20 AND C1 != 30  

 

 

 

 

 

 

 

ORDER BY 
ASC / DESC

 

ORDER BY 절에는 앞 컬럼 먼저 정렬 후 다름 컬럼 정렬 

 

ORDER BY 총점 DESC, 수학 -> 총점 내림차순으로 정렬 후 동점은 수학 오름차순으로 정렬

 

ASC 는 생략 가능

 

NULL 값을 어떻게 처리할지 지정 가능

NULLS FIRST

NULLS LAST