[AIVLE_AI] SQL 1일차

SQL

 

SQL문

SQL: Structured Query Language

데이터베이스에서 데이터를 조회하거나 처리(입력, 수정, 삭제)할 때 사용하는 구문

SQL-86 → SQL-92 → SQL:1999 → SQL:2003 → SQL:2008 → SQL:2011 → SQL:2016

한 제품에서 SQL 사용이 익숙하면 다른 제품에서도 사용이 가능

제품마다 호환되지 않은 SQL문도 있음

 

---

데이터 기본 조회

 

단순 조회

SELECT 문이 가장 기본이 되면서 가장 많이 사용하는 쿼리문 

대부분의 SELECT문은 FROM절을 사용해 테이블에서 데이터를 조회함 

하지만 SELECT 문에 FROM 절이 꼭 필요한 것은 아님 

특정 값, 계산식 또는 함수 결과를 조회할 수 있음 

AS를 사용해 표시되는 열의 별칭을 지정할 수 있음

SELECT 'Hello SQL World';
SELECT 'Hello SQL World' AS Start;
SELECT 10 + 20 AS Result;
SELECT CURDATE() AS Today;
SET @Today = CURDATE();
SELECT @Today;

 

테이블에 대한 이해

테이블은 다음과 같이 행과 열로 구성됨

대부분의 테이블은 제한된 개수의 열과 수 천 수 만개의 행을 가짐

SQL문을 사용해 특정 조건에 맞는 행의 몇몇 열을 조회하는 것이 일반적임

 

기본적인 데이터 조회 1

SELECT 열1, 열2, …
FROM 테이블
WHERE 조건 ;

SELECT 절에 조회할 열 이름이나 수식 등을 보고자 하는 순서로 나열

만일 모든 열을 보고자 하는 경우는 * 을 입력함

FROM 절에 조회 대상 테이블, 뷰 또는 테이블 형태를 반환하는 함수나 하위 쿼리문을 입력

WHERE 절에 행 추출을 위한 조건을 입력하며, 조건을 생략하면 모든 행이 조회 대상이 됨

employee 테이블에서 dept_id 열 값이 'SYS'인 행의 emp_Id, emp_name을 조회하는 구문 예

SELECT emp_id, emp_name FROM employee WHERE dept_id = 'SYS';                         <대상 열>                       <테이블>                   <조건>

 

기본적인 데이터 조회 2

열 이름 대신 * 를 입력하고 조건을 입력하지 않으면 테이블의 모든 행의 모든 열을 조회

모든 열을 의미하는 * 는 All 이라고 읽음

표시되는 열 순서는 테이블을 만들 때 지정한 열 순서와 동일함

SELECT * FROM employee;
SELECT * FROM department;

 

열 이름을 입력하고 조건을 입력하지 않으면 모든 행의 지정한 열을 조회

보고자 하는 순서대로 열 이름을 정확히 입력함

원하는 순서로 나열하면 되니 테이블에 정의한 열 순서는 조회에서 의미가 없음

SELECT emp_id, emp_name FROM employee;
SELECT emp_name, emp_id FROM employee;

 

기본적인 데이터 조회 3

열 이름 대신 * 를 입력하고 조건을 입력하면 조건에 맞는 행의 모든 열을 조회

SELECT * FROM employee WHERE emp_id = 'S0001';
SELECT * FROM vacation WHERE emp_id = 'S0002';

 

열 이름을 입력하고 조건을 입력하면 조건에 맞는 행의 지정한 열을 조회

가장 적절한 조회 형태라고 할 수 있음

SELECT emp_id, emp_name FROM employee WHERE emp_id = 'S0001';
SELECT dept_id, dept_name FROM department WHERE dept_id = 'SYS';

조건을 잘못 지정하면 오류 대신 의도하지 않는 결과를 얻게 됨

잘못된 결과를 조회하기 보다는 차라리 오류가 발생하는 것이 더 좋을 수도 있음

조회된 결과에 대한 정합성을 항상 확인하는 습관이 필요함

 

비교 연산자 1

크기를 비교할 때 사용하는 연산자이며, 대부분 WHERE 절에서 조건을 정의할 때 사용

정확한 조회 결과를 얻기 위해 쿼리문 작성 시 가장 신중해야 할 부분

일반적으로 숫자의 크기를 비교하지만, 문자와 날짜도 크기를 비교할 수 있음

같지 않다 즉, 다르다는 <> 또는 != 연산자를 사용함

SELECT * FROM Employee WHERE dept_id = 'SYS';
SELECT * FROM Employee WHERE salary > 7000;
SELECT * FROM Employee WHERE hire_date < '2015-02-28';
SELECT * FROM Employee WHERE dept_id <> 'SYS';

 

비교 연산자 2

문자열 데이터는 숫자나 날짜 데이터와는 달리 다양한 조건의 검색이 요구됨

LIKE 연산자로 문자 패턴을 비교해 원하는 데이터를 조회할 수 있음

 

이름에 '국'이 들어간 직원

SELECT * FROM Employee WHERE emp_name LIKE '%국%'

 

이름이 '국'으로 끝나는 직원

SELECT * FROM Employee WHERE emp_name LIKE '%국';

 

이름 중간에 '국'이 들어가는 직원

SELECT * FROM Employee WHERE emp_name LIKE '_%국%_';

 

이름 중간에 공백이 들어가는 고객

SELECT *
FROM customer
WHERE customer_name LIKE '% %'

 

이메일 아이디가 4글자인 직원(_ 하나가 글자 하나를 의미함)

SELECT * FROM Employee WHERE email LIKE '____@%';

 

논리 연산자

WHERE 절에서 여러 개의 조건을 연결하는 연산자

AND	연산자는 조건을 만족할 수록 결과 행이 줄어듦	성능면에서 긍정적임
OR	연산자는 조건을 만족할 수록 결과 행이 늘어남	성능면에서 부정적임(처리할 데이터가 많아짐)
NOT	연산자는 조건에 대한 부정을 의미	IS NOT NULL, NOT LIKE, NOT IN, NOT BETWEEN 등

여러 조건을 나열할 경우에는 괄호를 사용해 조건을 명확히 식별할 수 있도록 해야 함

SELECT * FROM Employee WHERE salary > 5000 AND hire_date <= '2006-12-31';
SELECT * FROM Employee WHERE dept_id = 'SYS' OR dept_id = 'MKT';

 

where 절에 함수 사용 - 권장되지 않는 방법

SELECT * FROM Employee WHERE YEAR(hire_date) = 2016;

함수 사용은 성능면에서 부정적임

 

범위 조건과 리스트 조건

WHERE 절에 조건을 간결하게 입력할 수 있는 방법 → 가독성 향상 

조건에 열 이름을 반복해서 입력하는 번거로움을 해결함

SELECT * FROM Employee WHERE salary >= 5000 AND salary <= 8000;
SELECT * FROM Employee WHERE dept_id = 'SYS' OR dept_id = 'MKT' OR dept_id = 'HRD';

SELECT * FROM Employee WHERE salary BETWEEN 5000 AND 8000;
SELECT * FROM Employee WHERE dept_id IN ('SYS', 'MKT', 'HRD');

 

NOT 연산자를 사용해 조건에 대한 부정을 쉽게 지정할 수 있음

SELECT * FROM Employee WHERE salary NOT BETWEEN 5000 AND 8000;
SELECT * FROM Employee WHERE dept_id NOT IN ('SYS', 'MKT', 'HRD');

 

NULL값 1

NULL 값은 0도 아니고 공백도 아닌 알 수 없는 값 → Unknown Value 

NULL 값과 문자열 'NULL'은 완전히 다름 

= 사용하지 않는다.

IS NULL, IS NOT NULL을 사용해 NULL 값을 식별할 수 있음

SELECT * FROM Employee WHERE eng_name = 'NULL' #오류 
SELECT * FROM Employee WHERE eng_name IS NULL;
SELECT * FROM Employee WHERE retire_date IS NULL;
SELECT * FROM Employee WHERE retire_date IS NOT NULL;

 

NULL값 2

IFNULL() 함수를 사용해 NULL값 대신 다른 값을 표시할 수 있음(전처리, 결측치 처리)

SELECT emp_name, emp_id, IFNULL(eng_name, '') AS nick_name, gender, dept_id, hire_date
FROM employee
WHERE retire_date IS NULL;

eng_name이 NULL 값인 경우 빈칸으로 처리함

 

 

DBMS 마다 다른 함수를 사용함

- MySQL: IFNULL() / MSSQL: ISNULL() / ORACLE: NVL()

IFNULL() 함수 대신 표준 함수인 COALESCE() 함수를 사용하기를 권고

SELECT emp_name, emp_id, COALESCE(eng_name, '') AS nick_name, gender, dept_id, hire_date
FROM employee
WHERE retire_date IS NULL;

 

NULL값 3

DBMS마다 다른 NULL값 처리 함수

MySQL	IFNULL()
MSSQL	ISNULL()	MySQL에서 ISNULL() -> 전달된 값이 NULL이면 1, 아니면 0 반환
ORACLE	NVL()

-> 쿼리의 범용성을 위해서 사용ㅇ르 권고

*COALESCE() 함수: 나열 된 값 중에서 첫 번째 NULL이 아닌 값

 

자동 형 변환

문자 데이터가 연산에 사용되면 자동으로 숫자로 변환됨 (자동 형 변환) 

숫자로 변환되지 못하는 문자는 0으로 변환됨

SELECT '10' + '20'; -- 30 문자열 숫자로 변환
SELECT 10 + '20'; -- 30, 문자열 숫자로 변환
SELECT 10 + '20AX'; -- 30, 숫자로 변환 문자열 무시
SELECT 10 + 'LX20'; -- 10, 문자열 무시

30

30

30

10

자동 형 변환은 DBMS에 따라 다르게 수행됨을 유의해야 함

※ 참고: MS SQL은 +, ORACLE은 || 를 사용해 문자열 연결이 가능함

 

데이터 결합

CONCAT 함수를 사용해 데이터 결합

결합되는 값에 NULL 값이 포함되면 결합 결과가 NULL이 됨

문자열 데이터를 + 연산자로 결합할 수 없음 → CONCAT 함수를 사용하여 결합함(연산이 아님 그냥 붙여버림)

SELECT CONCAT('10', '20'); -- 1020
SELECT CONCAT(10, '20'); -- 1020
SELECT CONCAT(10, 20); -- 1020
SELECT CONCAT(10, NULL); -- NULL

1020

1020

1020

NULL

 

SELECT CONCAT(emp_name, '(', emp_id, ')') AS emp_name, 
dept_id, gender, hire_date, email
FROM employee;

AS 는 생략 가능하지만 가독성을 위해 써주는게 좋다.

SELECT CONCAT(emp_name, ＇(＇, salary, ＇)＇) AS emp_name, 
dept_id, gender, hire_date, email
FROM employee;

 

데이터 정렬

SELECT 열1, 열2, … 
FROM 테이블
WHERE 조건
ORDER BY 정렬 기준 열 ASC | DESC ;

ORDER BY 절을 사용해 정렬된 결과를 표시할 수 있음

ASC: 오름차순 → 1, 2, 3, 4 / 가, 나, 다, 라 / A, B, C, D 

DESC: 내림차순 → 4, 3, 2, 1 / 라, 다 , 나, 가 / D, C, B, A

꼭 필요한 경우만 정렬하기를 권고 (성능 문제 발생 가능)

숫자는 물론 문자, 날짜형 데이터 정렬이 가능함

복합 정렬은 콤마(,)로 구분해서 정렬 방식을 지정함

ASC 가 기본값 → 정렬 방식을 지정되지 않으면 오름차순으로 정렬됨

SELECT * FROM Employee ORDER BY salary DESC;

 

복합정렬: 정렬 시 중복이 되는 경우

SELECT * FROM Employee ORDER BY dept_id ASC, emp_id DESC;

dept id를 ASC 정렬 하고 (ASC 생략 가능), 중복되는 dept id인 경우 emp_id 를 기준으로 DESC 정렬 

 

컬럼 순서로 정렬 명령이 가능

SELECT * FROM Employee ORDER BY 1 ASC, 2 DESC;

첫번째 컬럼을 정렬하고 중복되는 경우 두번째 컬럼으로 내림차순 정렬

 

 

강사님 코멘트: 쿼리는 빼기 예술!!!!

 

CASE 문 - 여러 조건에 대해`

CASE WHEN 조건1 THEN 값1
WHEN 조건2 THEN 값2
WHEN 조건3 THEN 값3 
…
ELSE 값N END

CASE 문을 사용하면 쿼리문 안에서 조건에 따른 처리(값 연산, 변환 등)를 수행할 수 있음

기본 구문이 다소 길다고 느낄 수 있으나, SELECT 문에서 매우 중요한 역할을 수행함

gender 열 값이 'M'이면 '남자'로,'F'면 '여자', 나머지는 공백으로 표시하는 예

SELECT emp_name, emp_id, 
CASE WHEN gender = 'M' THEN '남자'
WHEN gender = 'F' THEN '여자'
ELSE '' END AS gender, hire_date, retire_date, salary
FROM employee;

 

IF 함수 - 한 조건에 대해

IF (조건, 값1, 값2)

조건이 참이면 값1, 거짓이면 값2가 선택됨

CASE 문 처럼 IF 함수를 사용해 쿼리문 안에서 조건에 따른 처리를 수행할 수 있음

CASE 문 보다는 상대적으로 구문이 짧아 간결한 쿼리문을 작성할 수 있음

하지만 CASE 문처럼 여러 조건에 따라 분기하여 처리할 수는 없음

여러 조건에 따른 처리는 CASE 문, 한 번의 조건 비교로 참/거짓에 따른 처리는 IF 함수 사용

---

데이터 집계

 

집계 함수

합(SUM), 평균값(AVG), 최댓값(MAX), 최솟값(MIN), 개수(COUNT)를 구함

합과 평균값은 숫자에 대해서만 구할 수 있음

최댓값, 최솟값, 개수는 숫자는 물론 문자와 날짜에 대해서도 사용 가능

날짜의 최솟값 → 가장 빠른(오래된) 날짜

날짜의 최댓값 → 가장 최근 날짜 

행 수를 구할 때는 COUNT(*)를 사용함

SELECT COUNT(*) AS emp_count
FROM employee WHERE retire_date IS NOT NULL;

 

전체 집계

WHERE 절의 조건을 만족하는 모든 행(조건이 없으면 전체 행)에 대한 집계 수행

  예: 전체 판매 수량, 오늘 하루 전체 판매 금액, 서울 지역 전체 고객 수 집계 등

전체 에 대한 집계이므로 출력되는 값이 한 행이 됨

합계, 평균, 최솟값, 최댓값, 개수 등을 개별적으로 또는 함께 집계할 수 있음

시스템부서 직원들의 급여 합 집계 예

SELECT SUM(salary) AS tot_salary
FROM employee WHERE dept_id = 'SYS';

 

전 직원 급여 합, 급여 평균 집계 예

SELECT SUM(salary) AS tot_salary, AVG(salary) AS avg_salary
FROM employee WHERE dept_id = 'SYS';

 

강사님 말씀

수치형 범주형 의 각 개념과 대비되는 개념의 명확성이 필요하다. 숫자형의 반대는 문자형 범주형의 반대는 수치형 (수치형은 이산형과 연속으로 나누어진다)

 

집계 함수와 NULL 값1

집계 함수는 NULL값을 무시함 (없는 값으로 간주) → 의도하지 않은 결과를 얻을 수 있음

SUM()	NULL 값을 제외한 나머지 값의 합을 구함
AVG()	NULL 값을 제외한 나머지 값의 합을 NULL 값을 제외한 값의 개수로 나눔 - 주의
MIN(), MAX()	NULL 값을 제외한 값 중에서 가장 작은/큰 값
COUNT()	NULL 값을 제외한 값의 개수

COUNT(*)만이 특정 열을 기준으로 하지 않으므로 NULL 값이 무시되지 않음

NULL값이 있는 열에 대한 집계 시, 특히 AVG() 함수 사용 시 주의가 필요

 

집계 함수와 NULL 값2

NULL 값을 무시하고 집계가 수행되기를 원하는 경우도 있을 것임

그렇지 않은 경우는 NULL 값을 0으로 대체해 집계를 수행해야 함

다음 구문은 salary 열에 NULL 값이 있으면 평균을 집계할 때 무시함

SELECT AVG(salary) AS avg_salary
FROM employee WHERE retire_date IS NULL;

 

방법1: COUNT(*)을 사용해 얻은 행 수를 분모로 사용함

SELECT SUM(salary) / COUNT(*) AS avg_salary
FROM employee WHERE retire_date IS NULL;

 

방법2: NULL 값을 0으로 대체해서 평균을 집계함

SELECT AVG(IFNULL(salary, 0)) AS avg_salary
FROM employee WHERE retire_date IS NULL;

 

그룹별 집계

SELECT 집계 기준 열, 집계함수1, 집계함수2, … 
FROM 테이블
WHERE 조건
GROUP BY 집계 기준 열
ORDER BY 정렬 기준 열 ASC | DESC ;

GROUP BY를 사용해 그룹별 집계를 수행

  예: 지역별 구매 수량 합계, 남녀별 점수 평균, 영업점별 판매 수량과 금액 집계 등

집계 결과에 대한 정렬이 필요한 경우 ORDER BY 절을 사용함 

기본적으로 집계 기준 열을 기준으로 오름차순 정렬이 됨

 

집계 예

SELECT dept_id, COUNT(*) AS emp_count
FROM employee
WHERE retire_date IS NULL
GROUP BY dept_id;

 

그룹별 집계와 조건

SELECT 집계 기준 열, 집계함수1, 집계함수2, … 
FROM 테이블
WHERE 조건
GROUP BY 집계 기준 열
HAVING 조건
ORDER BY 정렬 기준 열 ASC | DESC ;

WHERE 절 조건 → GROUP BY 하기 전 조건 → 집계 대상 행 필터링

HAVING 절 조건 → GROUP BY 한 후 조건 → 집계 후 표시할 행 필터링

HAVING 없는 GROUP BY는 존재하지만, GROUP BY 없는 HAVING은 존재하지 않음

 

집계 예

SELECT dept_id, COUNT(*) AS cnt
FROM employee
WHERE retire_date IS NULL
GROUP BY dept_id
HAVING cnt >= 3
ORDER BY emp_count DESC;;

 

 

MySQL은 다음이 가능

SELECT dept_id, emp_name, COUT(*) AS emp_count
FROM employee
WHERE retire_date IS NULL
GROUP BY dept_id;

emp_name이 어떤게 나올지 정해지지 않았는데도 불구하고 emp_name에 대해 출력이 된다.

다른 DBMS 경우에는 ERROR 발생 "emp_name에 대해서 출력할 수 없습니다."

 

---

 

가변 데이터와 고정 데이터

VARCHAR: 가변 / 공간 효율적으로 사용

CHAR: 고정 / 고정된 공간 활용

 무조건 물리적으로 10byte 공간 확보

 

 

개수 조회

중복 허용: SELECT COUNT

중복 제거: SELECT COUNT(DISTINCT )