[lesson] DBMS - 파생 문법 1탄 (문자연결, LIKE, DUAL, 집계함수, GROUP BY, ALIAS, ORDER BY)

<NULL>  
정의되지 않은 값.
빈 값 대신 미정 값을 부여할 때 사용
PK는 불가능, FK 가능, UK 가능

<NOT NULL 제약조건>
ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL;

<DEFAULT 제약조건>
ALTER TABLE 테이블명 MODIFY 컬럼명 DEFAULT 초기값;

<제약조건 삭제>
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건 이름;

<NULL 값을 다른 값으로 변경>
NVL(컬럼명, '값') : NULL 닶 대신 다른 값으로 변경 후 검색
NVL2(컬럼명, 'NULL이 아닐 때 값, 'NULL일 때 값') : NULL일 때의 값, NULL이 아닐때의 값을 각각 설정

<조건식>

컬럼명 IS NULL : 컬럼값이 NULL이면 참
컬럼명 IS NOT NULL : 컬럼 값이 NULL이 아니면 참

※ 컬럼명 IS NULL 과 컬럼명 = 'NULL';은 상황에 따라 결과가 다르게 나오기 때문에 값이 아예없는 NULL이라면 IS NULL을 사용해야 한다.

--누구누구의 포지션은 머머이다.
--'NULL'은 문자열 값이지만 
SELECT PLAYER_NAME || '의 포지션은 ' || "POSITION" || '이다.' "포지션 설명" FROM PLAYER WHERE "POSITION" IS NOT NULL;
SELECT PLAYER_NAME || '의 포지션은 ' || "POSITION" || '이다.' "포지션 설명" FROM PLAYER WHERE "POSITION" <> 'NULL';



-- NULL이라는 값을 'NULL'로 !=, ^=, <> 연산을 하게 되면
-- 동일한 값으로 취급된다.
SELECT * FROM TBL_STUDENT
WHERE MAJOR <> 'NULL';

-- 하지만 = 연산자로는 'NULL'을  문자열 'NULL'로만 취급한다.
SELECT * FROM TBL_STUDENT
WHERE MAJOR = 'NULL';

-- IS NULL, IS NOT NULL은 NULL과 'NULL'을 정확히 구분한다.
-- 정확히 구분할 수 있는 IS 연산자를 사용해야 한다.
SELECT * FROM TBL_STUDENT
WHERE MAJOR IS NOT NULL;

 

<CONCATENATION (문장 연결)>

SELECT PLAYER_NAME || '의 영어 이름은 ' || E_PLAYER_NAME || '입니다.' AS 자기소개 FROM PLAYER;

 

<LIKE>

포함된 문자열의 값을 찾음, 문자의 개수도 제한을 줄 수 있습니다. (WHERE절에서 사용)

1. % : 모든 것
2. '%A' : A로 끝나는 모든 값(FDSFA, FDMK213A,..)
3. 'A%' : A로 시작하는 모든 값(AA, AFD, ADFS,..)
4. 'A__': A로 시작하며 3글자인 값(ABC, AAA, A12,..)
5. '_A' : A로 끝나며 2글자인 값(FA, CA, DA,...)
6. '%A%' : A가 포함된 값(APPLE, BANANA, TASK,...)

 

-- TEAM 테이블에서 팀이름 중 '천마'로 끝나는 팀이름 찾기
SELECT * FROM TEAM
WHERE TEAM_NAME LIKE '%천마';

-- PLAYER테이블에서 김씨 찾기
SELECT * FROM PLAYER
WHERE PLAYER_NAME LIKE '김%';

-- PLAYER테이블에서 김씨 두자 찾기
SELECT * FROM PLAYER
WHERE PLAYER_NAME LIKE '김_'

-- PLAYER테이블에서 김씨와 이씨 찾기;
SELECT * FROM PLAYER
WHERE PLAYER_NAME LIKE '김%' OR PLAYER_NAME LIKE '이%';

-- PLAYER테이블에서 이씨 아닌사람 찾기;
SELECT * FROM PLAYER
WHERE NOT PLAYER_NAME LIKE '이%'

-- PLAYER테이블에서 외자가 아닌 김씨가 아닌 사람 찾기;
SELECT * FROM PLAYER
WHERE NOT PLAYER_NAME LIKE '김__' AND PLAYER_NAME LIKE '김%'

 

<숫자형 함수 (DUAL)>

1. 절대값

2. 양/0/음의 정수 판단 -> 결과값이 양 (1), 0 (0), 음 (-1)으로 나옵니다.

3. 나머지

4. 올림

5. 내림

6. 반올림

-- 절대값
SELECT ABS(-10) 절대값 FROM DUAL;

-- 양의 정수(1), 영(0), 음의 정수 판단(-1)
SELECT SIGN(4), SIGN(0), SIGN(-4) FROM DUAL;

-- 나머지
SELECT MOD(10, 3) FROM DUAL;

-- 값보다 큰 최근접 정수
SELECT CEIL(3.14), CEIL(-3.14) FROM DUAL;

-- 값보다 작은 최근접 정수
SELECT FLOOR(3.14), FLOOR(-3.14) FROM DUAL;

-- 반올림
SELECT ROUND(3.555555556616) FROM DUAL;
SELECT ROUND(3.555555556616,2) FROM DUAL;
SELECT ROUND(3.354516,1) FROM DUAL;

-- 버림
SELECT TRUNC(3.6) FROM DUAL;

 

<집계 함수>

집계함수는 WHERE절에서는 사용이 불가하며, 여러 개의 값을 하나의 값으로 집계하여 나타냅니다.

또한 NULL은 포함하지 않으니 집계시 NULL을 포함하고 싶으면 NVL/NVL2 또는 COUNT(*)과 같이 다른 함수들과 함께 사용하여 결과를 만들어야 합니다.

 

1. 평균 (AVG)

2. 최대값 (MAX)

3. 최소값 (MIN)

4. 합계 (SUM)

5. 개수 (COUNT)

-- AVG, MAX, MIN, SUM, COUNT
SELECT AVG(HEIGHT), MAX(HEIGHT), MIN(HEIGHT), SUM(HEIGHT), COUNT(HEIGHT) FROM PLAYER
GROUP BY "POSITION"
HAVING "POSITION" IS NOT NULL;

-- COUNT
SELECT COUNT(*) FROM PLAYER;

SELECT COUNT(HEIGHT) FROM PLAYER;

-- PLAYER 테이블에서 HEIGHT 개수 검색 (NULL 포함해서 COUNT하기)
SELECT COUNT(NVL(HEIGHT,0)) 총합 FROM PLAYER;

 

 

<ORDER BY>

정렬방식을 정해주는 문법 (DEFAULT는 오름차순입니다. (DEFAULT는 생략가능!) )

1. ASC(Ascending) : 오름차순
2. DESC(Descending) : 내림차순

SELECT * FROM PLAYER ORDER BY HEIGHT ASC;
SELECT * FROM PLAYER ORDER BY HEIGHT;
--ASC은 생략이 가능

SELECT * FROM PLAYER ORDER BY HEIGHT DESC;

-- 12는 컬럼 순서 (맨왼쪽부터 12번째 컬럼(이름을 모를때 사용하며, 첫 인덱스는 1로 시작한다.)
SELECT * FROM PLAYER ORDER BY 12 ASC


-- 2개 이상이면 HEIGHT를 오름차순으로 하고 동일한 값이면 WEIGHT가 오름차순으로 추가로 된다.
SELECT * FROM PLAYER ORDER BY HEIGHT, WEIGHT;

 

<AS (ALIAS)>

테이블 선언 또는 SELECT하여 나온 결과 TABLE 뒤에 AS 를 사용하여 이름을 작성하면 그이름으로 사용할수 있다.

-- CREATE 가 되었다고 가정했을때 ALIAS 사용
CREATE TABLE TBL_CREATE_TABLE_SUCCESS AS S_TABLE;

-- ALIAS를 사용한 이름으로 SELECT
SELECT * FROM S_TABLE;

 

<CASE문>

자바로 치면 IF ELSE문으로 보시면됩니다.

기본 문법 CASE WHEN THEN ELSE END

 - CASE : 문법의 시작을 알림

 - WHEN : 조건식

 - THEN : 조건이 맞을 때 나오는 값

 - ELSE : 조건이 거짓일때 

 - END : 문법의 종료를 알림 (END 뒤에 AS를 붙일 수 있습니다.)

-- STADIUM테이블에서 SEAT_COUNT가 0이상 30000이하면 'S'
-- 30001이상 50000이하면 'M' 다 아니면 'L'

SELECT STADIUM_NAME, SEAT_COUNT,
	CASE
		-- WHEN SEAT_COUNT BETWEEN 0 AND 30000 THEN 'S'로
		-- 
		WHEN SEAT_COUNT <= 30000 THEN 'S'
		WHEN SEAT_COUNT <= 50000 THEN 'M'
		ELSE 'L'
	END AS "좌석수 수준"
FROM STADIUM;

----------------다른 풀이----------------------------------------------
SELECT STADIUM_NAME 경기장, SEAT_COUNT 좌석수,
	CASE
		WHEN SEAT_COUNT BETWEEN 0 AND 30000 THEN 'S'
		ELSE(
			CASE
				WHEN SEAT_COUNT BETWEEN 30001 AND 50000 THEN 'M'
				ELSE 'L'
			END
		)
		END AS 규모
FROM STADIUM

 

<GROUP BY>

~별 (팀별 평균 몸무게, 포지션별 평균 키 등등) 의 의미를 가지고 있습니다.

 

(기본 문법)

GROUP BY 컬럼명 HAVING 조건식

-- PLAYER 테이블에서 포지션 검색
-- WHERE절에서 뺄거 다 빼고 GROUP 에서 처리

-- 1번 처리
SELECT "POSITION" FROM PLAYER
WHERE HEIGHT > 180
GROUP BY "POSITION"
HAVING "POSITION" IS NOT NULL;

-- 2번 처리
SELECT "POSITION" FROM PLAYER
WHERE "POSITION" IS NOT NULL
GROUP BY "POSITION"

-- 1번보다 2번이 처리속도가 빠름
-- WHERE절에서 조건을 처리할수 있으면 반드시 WHERE에서 처리해주기!!

SELECT * FROM PLAYER

-- PLAYER 테이블에서 몸무게가 80이상인 선수들의 평균 키가 180 이상인 포지션 검색
SELECT "POSITION" 포지션, AVG(HEIGHT), MIN(WEIGHT) FROM PLAYER
WHERE WEIGHT >= 80
GROUP BY "POSITION"
HAVING AVG(HEIGHT) >= 180;


-- EMPLOYEES 테이블에서 JOB_ID 별 평균 SALARY가 10000미만인 JOB_ID 검색
-- JOB_ID는 알파벳 순으로 정력 (오름차순)

SELECT JOB_ID, AVG(SALARY) FROM EMPLOYEES
GROUP BY JOB_ID
HAVING AVG(SALARY) < 10000
ORDER BY JOB_ID ASC;