[TIL] DB-01 - SELECT 기본 문법, 연산자와 NULL 처리, 패턴 검색

1. 오늘 학습한 주제

• Database modeling(개념, 논리, 물리)과 SQL 분류
• 관계형 데이터베이스와 참조 무결성
• 인덱스와 NULL
• SELECT 기본 문법과 별칭
• IFNULL(), NULLIF()
• DISTINCT
• WHERE와 연산자
• CONCAT()
• BETWEEN ... AND
• LIKE / NOT LIKE 패턴 검색
• IS NULL, ISNULL()
• OR과 IN

2. 배운 내용

Database modeling

modeling(개념, 논리, 물리)
- 개념적 모델링(RDS) -> ERD
- 논리적 모델링 -> 정규화
- 물리적 모델링 -> 역정규화(for performance)

논리적 모델링에서 정규화를 하고, 물리적 모델링 단계에서 성능을 위해 역정규화한다. 방향이 반대인 두 작업이 단계별로 나뉘어 있다.

DBMS 종류로는 Oracle, MySQL, MariaDB, DB2를 들었다. 실습은 MySQL이다.

SQL의 분류

분류	내용
DDL	정의 (CREATE, ALTER, DROP)
DML	조작 (INSERT, UPDATE, DELETE)
DCL	제어 (COMMIT, ROLLBACK)
SELECT	데이터 검색

COMMIT, ROLLBACK을 DCL에 넣을지 따로 분류할지는 교재에 따라 다르게 잡기도 한다.

실제 데이터를 관리하는 것은 DB 안의 table이다.

관계형 데이터베이스

• 데이터 간의 상관관계
• 관계란 업무적인 연관성

주체관계 파악이 중요한데, 판단이 모호할 때는 관계를 서술해보면 쉽게 파악할 수 있다.

ex) 고객이 자동차를 소유한다 -> 고객이 부모

참조 무결성이 가장 중요하다.

인덱스

• 별도의 정렬 없이 결과 추출
• 물리적인 디스크 io 감소

null이 있는 컬럼은 인덱스가 풀린다고 들었고, 그래서 null값이 있는 컬럼은 선호하지 않는다. 다만 이 동작은 DBMS에 따라 다를 수 있어 따로 확인이 필요하다.

SELECT 문법

SELECT      (DISTINCT) 컬럼명 | * | 표현식 및 함수 | [AS] 별칭
FROM        테이블 이름
[WHERE]     행의 제한
[GROUP BY]  데이터를 그룹으로 묶음
[HAVING]    그룹에 대한 조건
[ORDER BY]  정렬(ASC, DESC)

[]는 선택 항목이다. SELECT와 FROM만 필수다.

키워드는 대소문자를 구분하지 않지만 데이터는 대소문자를 구분한다.

USE lgcns;

SHOW DATABASES;

DESC country;

SELECT *
FROM job;

별칭

별칭은 공백을 포함하는 문자 및 특수문자도 쓸 수 있지만, 그러려면 ''나 ``로 감싸야 한다.

SELECT EMP_NAME,
       SALARY,
       (SALARY + (SALARY * BONUS_PCT)) * 12 AS `(연봉)`
FROM employee;

반대로 공백이나 특수문자가 없으면 감싸지 않아도 된다.

SELECT CONCAT(EMP_NAME, '님의 급여는 ', SALARY, '(원)입니다.') AS 급여정보
FROM employee;

NULL 처리 함수

위의 연봉 계산에는 문제가 있다. BONUS_PCT가 NULL인 행은 연산 결과가 통째로 NULL이 된다.

-- NULL 처리함수 : IFNULL(NULL, 'XXXXX'), NULLIF(100, 'NOT NULL');
SELECT IFNULL(NULL, '포세이돈의 아들 외눈박이'),
       NULLIF(100, 0);

IFNULL()은 첫 인자가 NULL이면 두 번째 값으로 대체한다. 이 함수를 연산 안쪽에 넣어 보너스가 없는 사원도 급여만으로 계산되게 한다.

SELECT EMP_NAME,
       SALARY,
       (SALARY + (SALARY * IFNULL(BONUS_PCT, 0))) * 12 AS `(연봉)`
FROM employee;

DISTINCT

-- DISTINCT : 컬럼에 포함된 중복 값을 한 번씩만 출력
SELECT DISTINCT DEPT_ID,
       JOB_ID
FROM   employee;

컬럼을 두 개 적으면 각각이 아니라 두 값의 조합 기준으로 중복이 제거된다.

WHERE와 연산자

WHERE는 행을 제한한다. 쓰는 연산자는 비교(LIKE, NOT LIKE 포함), 산술, 논리(AND, OR, NOT)다.

-- Q) 부서번호가 90번인 사원의 모든 정보를 검색한다면?
SELECT *
FROM employee
WHERE DEPT_ID = '90';

-- Q) 부서번호가 90번이거나 급여가 4,000,000 이상인 사원의 모든 정보를 검색한다면?
SELECT *
FROM employee
WHERE DEPT_ID = '90' OR SALARY >= 4000000;

CONCAT

연결연산자다. 인자를 순서대로 이어붙인다.

SELECT CONCAT('오늘의', '할 일은', '과연', '무엇일까');

컬럼과 문자열을 섞어 문장 형태로 만들 수 있다.

SELECT CONCAT(EMP_NAME, '님의 급여는 ', SALARY, '(원)입니다.') AS 급여정보
FROM employee;

BETWEEN AND

AND로 범위를 잡은 조건은 BETWEEN ... AND로 대체할 수 있다.

-- Q) 급여가 3500000이상 ~ 5500000 이하인 사원의 이름, 급여, 직급을 검색한다면?
SELECT EMP_NAME, SALARY, JOB_ID
FROM employee
WHERE SALARY >= 3500000 AND SALARY <= 5500000;

SELECT EMP_NAME, SALARY, JOB_ID
FROM employee
WHERE SALARY BETWEEN 3500000 AND 5500000;

BETWEEN AND는 상한과 하한을 모두 포함한다. 위 두 쿼리의 결과가 같은 이유다. 다만 AND를 전부 BETWEEN AND로 바꿀 수 있는 것은 아니고 범위 조건인 경우에 한한다.

LIKE 패턴 검색

LIKE, NOT LIKE : 패턴검색(%, _) 연산자
% : 하나 이상의 문자와 매핑
_ : 하나의 문자와 매핑

-- Q) 김씨 성을 가지는 사원의 모든 정보를 검색한다면?
SELECT *
FROM employee
WHERE EMP_NAME LIKE '김%';

_가 와일드카드이기 때문에, 데이터에 들어 있는 _ 문자 자체를 찾으려면 \로 escape한다.

-- Q) 메일아이디 중 _앞자리가 3자리인 사원의 정보를 검색한다면?
SELECT *
FROM employee
WHERE EMAIL LIKE '___\_%';

앞의 ___는 와일드카드 3자리, \_는 문자 _, 뒤의 %는 나머지 전부다.

IS NULL과 ISNULL()

NULL은 =로 비교되지 않는다. IS NULL, IS NOT NULL을 쓴다.

-- Q) 부서배치를 받지 않은 사원의 정보를 검색한다면?
-- IS NULL, IS NOT NULL
-- ISNULL()
SELECT *
FROM employee
WHERE DEPT_ID IS NULL;

ISNULL()은 함수 형태로, NULL이면 1을 반환한다.

SELECT *
FROM employee
WHERE ISNULL(DEPT_ID);

SELECT ISNULL(NULL);

반환값이 1/0이므로 = 0을 붙이면 IS NOT NULL과 같은 자리가 된다.

-- ISNULL(DEPT_ID) = 0 이므로 NULL이 아닌 경우를 찾음
SELECT *
FROM employee
WHERE ISNULL(DEPT_ID) = 0;

OR과 IN

같은 컬럼을 OR로 나열한 조건은 IN으로 대체할 수 있다.

-- Q) 부서번호가 60번이거나 90번인 사원의 정보를 검색한다면?
SELECT *
FROM employee
WHERE DEPT_ID = '60' OR DEPT_ID = '90';

SELECT *
FROM employee
WHERE DEPT_ID IN('60', '90');

workbook

오늘 배운 것을 학사 테이블(tb_department, tb_student, tb_professor, tb_class)에 적용했다.

별칭과 CONCAT().

-- Q1)
SELECT DEPARTMENT_NAME AS `학과 명`, CATEGORY AS 계열
FROM tb_department;

-- Q2)
SELECT CONCAT(DEPARTMENT_NAME, '의 정원은 ', CAPACITY, ' 명 입니다') AS `학과별 정원`
FROM tb_department;

Q1의 학과 명은 공백이 있어 ``가 필요하고, 계열은 필요 없다.

LIKE와 IN.

-- Q3)
SELECT STUDENT_NAME
FROM tb_student
WHERE STUDENT_SSN LIKE '%\-2%' AND DEPARTMENT_NO = 001 AND ABSENCE_YN = 'Y';

-- Q4)
SELECT STUDENT_NAME
FROM tb_student
WHERE STUDENT_NO IN ('A513079', 'A513090', 'A513091', 'A513110', 'A513119');

BETWEEN AND와 IS NULL.

-- Q5)
SELECT DEPARTMENT_NAME, CATEGORY
FROM tb_department
WHERE CAPACITY BETWEEN 20 AND 30;

-- Q6)
SELECT PROFESSOR_NAME
FROM tb_professor
WHERE DEPARTMENT_NO IS NULL;

-- Q7)
SELECT COUNT(*) AS `학과가 없는 학생 수`
FROM tb_student
WHERE DEPARTMENT_NO IS NULL;

-- Q8)
SELECT CLASS_NO
FROM tb_class
WHERE PREATTENDING_CLASS_NO IS NOT NULL;

DISTINCT와 조건 조합.

-- Q9)
SELECT DISTINCT CATEGORY
FROM tb_department;

-- Q10)
SELECT STUDENT_NO, STUDENT_NAME, STUDENT_SSN
FROM tb_student
WHERE ABSENCE_YN = 'N'
AND ENTRANCE_DATE LIKE '2002%'
AND STUDENT_ADDRESS LIKE '전주%';

Q10의 입학년도는 날짜 컬럼이지만 앞 네 자리만 보면 되므로 LIKE '2002%'로 걸렀다.

3. 헷갈렸던 부분

별칭을 감싸는 기호

별칭에 공백이나 특수문자가 들어가면 감싸야 한다는 것은 알고 있었는데, '' 말고 ``도 쓸 수 있다는 것은 처음 알았다.

SELECT DEPARTMENT_NAME AS `학과 명`, CATEGORY AS 계열
FROM tb_department;