처음 SQL을 배우며 헷갈릴 수 있는 부분들을 정리

WHERE

DESC 사용

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name DESC;

• ORDER BY는 기본적으로 오름차순, 내림차순은 DESC로 표현, SQL 문 가장 뒤에 배치
• 여러 열 내림차순 정렬 시에는 각각의 열에 모두 DESC를 적어야 표현

💡  대소문자 구분과 데이터 정렬

• DBMS 설정에 따라 달라지겠지만, 대부분의 DBMS 기본 값에서 A와 a를 똑같이 취급
• 일부 DBMS에서는 데이터베이스 관리자가 이 설정을 변경할 수 있게 도와주나, 정렬 순서를 변경할 때 ORDER BY로는 원하는 결과를 얻기 어려울 수 있기 때문에 그런 경우에는 데이터베이스 관리자에게 문의
  

‼️  WHERE 절 연산자

= : 같다.          <> : 같지 않다.          != : 같지 않다.
< : -보다 작다.     <= : -보다 작거나 같다.    !< : -보다 작지 않다.
> : -보다 크다.     >= : -보다 크거나 같다.    !> : -보다 크지 않다.
BETWEEN ? AND ? : 두 개의 특정한 값 사이      IS NULL : 값이 NULL이다.
	OR : 지정된 조건을 하나라도 만족하는 경우       NOT : (뒤에 나오는 조건)이 아니다.

⚠️  ‘’(작은따옴표)의 사용 : 문자열 데이터인 경우에만 사용, 수치형이면 사용할 필요 X

‼️  WHERE 절 연산자 우선순위

AND가 OR보다 더 높다.

⚠️  따라서 AND와 OR을 같이 사용하는 경우, 괄호를 사용

‼️  OR 대신 IN 사용

WHERE vend_id IN ('DLL01', 'BRS01')

WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'

   IN을 사용하는 이유는…

• 조건이 많을 때 OR 보다 훨씬 가독성이 좋다.
• 연산자 우선순위를 관리하기 편하다.
• OR 보다 성능상 속도가 빠르다.
• SELECT 문을 포함할 수 있다.

LIKE 술어

⚠️  와일드카드 검색 : 문자열에서만 사용 가능

‼️  % 와일드카드

WHERE prod_name LIKE 'Fish%';
: Fish로 시작하는 이름

WHERE prod_name LIKE '%bean bag%';
: bean bag을 포함한 이름

WHERE prod_name LIKE 'F%y';
: F로 시작하고 y로 끝나는 단어를 가진 이름
(값에 공백이 있는 'Fish bean bag toy'와 같은 단어는 X)

• 검색할 문자열에서 %는 임의의 수의 문자를 의미
• NULL인 행은 가져오지 X

‼️  _ 와일드카드

WHERE prod_name LIKE '__ inch teddy bear'
: 후행 공백이 있는 경우 아무 결과도 출력되지 않을 수 있으므로 아래와 같이 수정
WHERE prod_name LIKE '__ inch teddy bear%'
: 이때는 12, 18 등 두 개의 문자와만 매칭이 되므로 아래와 같이 수정
WHERE prod_name LIKE '% inch teddy bear%'

⚠️  %와 달리 단 한 개의 문자를 대신

💡  와일드카드 사용 TIP

• 다른 검색 연산자를 이용해서 검색이 가능하다면 남용 금지
• 처리가 가장 느리므로 꼭 필요한 경우 아니면 사용 금지
• 기호의 위치 선정에 주의

계산 필드(열) 생성

SELECT vend_name || '(' || vend_country || ')'
: 오른쪽에 있는 모든 공백 제거가 필요한 경우에는 아래와 같이 작성
SELECT vend_name || '(' || RTRIM(vend_country) || ')'
: 별칭을 사용하려면 아래와 같이 작성
SELECT vend_name || '(' || RTRIM(vend_country) || ')' vend_title

‼️  TRIM 함수

RTRIM() : 오른쪽에 있는 모든 공백 제거
LTRIM() : 왼쪽에 있는 모든 공백 제거
TRIM() : 양쪽에 있는 모든 공백 제거

데이터 조작 함수 사용

‼️  가장 많이 사용하는 함수

SYSDATE : 현재 날짜

‼️  문자열 조작 함수

UPPER() : 문자열을 모두 대문자로 변환           LEFT() : 왼쪽부터 문자열 일부 추출  
LOWER() : 문자열을 모두 소문자로 변환           RIGHT() : 오른쪽부터 문자열 일부 추출
>> LEFT()와 RIGHT()는 이들 또는 문자열 추출 함수 사용

LENGHT() : 문자열 길이 반환                  SUBSTR() : 문자열 일부 추출
SOUNDEX() : 문자열 SOUNDEX 값 반환 (비슷한 소리의 값 반환)

‼️  날짜와 시간 조작 함수

SELECT order_num
FROM Orders
WHERE EXTRACT(year FROM order_date) = '2020';
또는
SELECT order_num
FROM Orders
WHERE order_date BETWEEN to_date('2020-01-01', 'yyyy-mm-dd') 
  AND to_date('2020-12-31', 'yyyy-mm-dd');
-- to_date()는 문자열을 날짜로 변환되기 위해 사용

‼️  수치 조작 함수

ABS() : 숫자의 절댓값 반환       EXP() : 숫자의 지숫값 반환       SQRT() : 숫자의 제곱근 반환
TAN() : 숫자의 탄젠트 반환       COS() : 숫자의 코사인 반환       SIN() : 숫자의 사인 반환
PI() : 숫자의 파이값 반환 

그룹 함수 (집계 함수)

AVG() : 열의 평균값 반환           COUNT() : 열에 있는 행의 개수 반환
MAX() : 열의 최댓값 반환           MIN() : 열의 최솟값 반환           SUM() : 열의 합계 반환

• 모든 집계 함수는 NULL 값을 가진 행은 무시하여 계산, 표준 수학 연산자(+, -, *, /) 사용 가능
• COUNT()는 애스터리스크(*) 사용 시에 NULL 값을 포함하여 계산
• MAX()가 문자열 데이터에서 사용되는 경우, 열로 정렬된 경우 맨 끝에 있는 행 반환
• MIN()이 문자열 데이터에서 사용되는 경우, 열로 정렬된 경우 맨 앞에 있는 행 반환

‼️  중복되는 값에 대한 그룹 함수 (집계 함수)

ALL : 기본값, 모든 행에 대해 계산 수행
DISTINCT : 중복되는 값 제거 시 사용

• DISTINCT는 COUNT()에 열 이름이 지정된 경우에만 함께 사용 또한, 계산이나 수식과는 사용 불가하고 반드시 열 이름과 함께 사용
  
• MIN(), MAX()와도 DISTINCT가 사용 가능하나, 효과 없어서 사용할 필요 X

데이터 그룹핑

‼️  GROUP BY 절

SELECT vend_id, COUNT(*) num_prods
FROM Products
GROUP BY vend_id;

• 원하는 만큼의 열로 중첩(nested) 그룹 생성 가능
• 중첩된 그룹이 있다면, 데이터는 마지막으로 지정된 그룹에서 요약 즉, 지정된 열은 그룹핑할 때 같이 측정(각 열 단위로는 데이터 얻지 X)
  
• GROUP BY 절에 있는 열은 가져오는 열이거나, 그룹 함수는 아니면서 유효한 수식 SELECT 절에서 수식을 사용한다면, GROUP BY 절도 같은 수식 사용(별칭 X)
  
• 문자나 메모와 같은 가변형 길이의 데이터형 사용 불가
• 그룹 함수 명령문을 제외하고 SELECT 절에 있는 모든 열은 GROUP BY 절에 존재
• 그룹핑하는 열의 행에 NULL 값이 있을 때, 그 NULL 값도 그룹으로 가져온다. 여러 행이 NULL 값을 가져도 모두 함께 그룹핑
  
• WHERE 절 뒤, ORDER BY 절 앞에 위치

‼️  HAVING 절

SELECT cust_id, COUNT(*) orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

• WHERE 절은 행을 필터링, HAVING 절이 그룹을 필터링
• WHERE 절은 데이터가 그룹핑 되기 전에 필터링, HAVING 절은 데이터가 그룹핑된 후에 필터링
• WHERE 절은 사용 불가 : 특정 행의 값이 아니라 그룹핑된 집곗값으로 필터링하기 때문

‼️  하나의 문장에 WHERE 절과 HAVING 절을 모두 사용

SELECT vend_id, COUNT(*) num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;

⚠️  HAVING 절과 WHERE 절의 사용

• GROUP BY 절이 있을 때만 HAVING 절 사용
• 행 단위로 필터링할 때는 WHERE 절 사용

‼️  그룹핑과 정렬

SELECT order_num, COUNT(*) items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

ORDER BY	GROUP BY
결과를 정렬	행을 그룹핑
어떤 열이라도 (가져오지 않은 열 포함) 사용 가능	결과는 그룹 순서대로 출력되지 않기도
필수 항목 X	선택된 열이나 수식만 사용 가능, 선택된 열이나 수식 꼭 사용
	그룹 함수와 함께 사용하는 열이 (또는 수식) 있는 경우 필수 항목

⚠️  GROUP BY 절을 사용할 때마다 ORDER BY 절을 명시해야 제대로 정렬

‼️  SELECT 문 순서

SELECT     (가져올 열이나 수식) : 필수
FROM       (데이터를 가져올 테이블) : 테이블에서 데이터를 가져올 때 사용
WHERE      (행 레벨 필터링) : 필수X
GROUP BY   (그룹 지정) : 그룹핑한 데이터로 집계 계산 시 사용
HAVING     (그룹 레벨 필터링) : 필수X
ORDER BY   (정렬 순서) : 필수X

서브쿼리 사용

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
									FROM Orders
									WHERE Order num IN (SELECT order_num
																			FROM OrderItems
																			WHERE prod_id = 'RGAN01')); 

• ‘RGAN01’이라는 제품을 구매한 고객의 목록
  1. RGAN01을 주문한 주문 번호를 가져온다.
  2. 이전 단계에서 가져온 주문 번호로 고객 ID를 가져온다.
  3. 이전 단계에서 가져온 고객 ID로 고객의 상세 정보를 가져온다.

⚠️  하나의 열만 : 서브쿼리는 하나의 열만 검색 가능

SELECT cust_name,
			 cust_state,
			 (SELECT COUNT(*)
			  FROM Orders
			  WHERE Orders.cust_id = Customers.cust_id) orders
FROM Customers
ORDER BY cust_name;

• 계산 필드로 서브쿼리 사용 : 주문 수량을 Customers 테이블에 있는 고객별로 보고 싶을 때
  1. Customers 테이블에서 고객 목록을 가져온다.
  2. Orders 테이블에서 각각의 고객이 주문한 수를 센다.

테이블 조인

‼️  EQUI JOIN

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id
-- WHERE 절이 없는 경우 훨씬 많은 데이터를 가져오므로 WHERE 절을 잊지 말자!

‼️  INNER JOIN

SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
	ON Vendors.vend_id = Products.vend_id
>> EQUI JOIN 보다 권장!

‼️  여러 개의 테이블 조인

SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
			AND OrderItems.prod_id = Products.prod_id
			AND order_num = 20007

‼️  서브쿼리 VS. 조인

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
									FROM Orders
									WHERE Order num IN (SELECT order_num
																			FROM OrderItems
																			WHERE prod_id = 'RGAN01')); 
																			
=

SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
	AND OrderItems.order_num = Orders.order_num
	AND prod_id = 'RGAN01';

고급 테이블 조인 생성

💡  테이블 별칭을 사용하는 이유

• 사용하는 SQL 명령문의 수를 줄이기 위해
• 하나의 SELECT 문 내에서 같은 테이블을 여러 번 사용하기 위해

SELF JOIN

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
									 FROM Customers
									 WHERE cust_contact = 'Jim Jones');
									 
=

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers c1, Customers c2
WHERE c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones';

• Jim Jones라는 사람과 같은 회사에서 일하는 모든 직원에게 메일을 보내려고 할 때
  1. Jim Jones가 어느 회사에서 일하는지
  2. 그 회사에서 일하는 직원 연락처를 알아내기

⚠️  서브쿼리보다는 SELF JOIN이 빠르다!

NATURAL JOIN

SELECT C.*, O.order_num, O.order_date,
			 OI.prod_id, OI.quantity, OI.item_price
FROM Customers C, Orders O, OrderItems OI
WHERE C.cust_id = O.cust_id
	AND OI.order_num = O.order_num
	AND prod_id = 'RGAN01';

OUTER JOIN

• 고객의 주문 수량을 계산할 때 아직 주문하지 않은 고객도 포함
• 제품과 주문 수량을 함께 나열하며, 아무도 주문하지 않은 제품도 포함
• 주문하지 않은 고객도 고려하여, 평균 세일 규모를 계산

SELECT Customers.cust_id, Orders.order_num
FROM Customers INNER JOIN Orders
	ON Customers.cust_id = Orders.cust_id;
	
=

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
	ON Customers.cust_id = Orders.cust_id;
	
LEFT / RIGHT / FULL OUTER JOIN : 왼쪽 / 오른쪽 / 양쪽에 있는 테이블의 모든 행을 가져온다.

그룹 함수와 조인 사용

SELECT Customers.cust_id,
			 COUNT(Orders.order_num) num_ord
FROM Customers INNER JOIN Orders
	ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

SELECT Customers.cust_id,
			 COUNT(Orders.order_num) num_ord
FROM Customers LEFT OUTER JOIN Orders
	ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
-- 내부 조인을 사용할 때와는 달리 왼쪽 외부 조인을 사용했을 때 주문한 적이 없는 고객도 검색되었다.

⚠️  조인과 조인 조건 올바르게 사용

• 사용할 조인 유형을 신중히 결정
• 올바른 조인 조건을 사용했는지 확인
• 조인 조건을 쓰는 것을 잊지 말고 항상 확인 >> 안쓰면 카티전 곱의 결과가 반환
• 하나의 조인에 여러 개의 테이블을 포함하거나 테이블별로 조인 유형을 다르게 할 수 있으나, 같이 묶어서 테스트하기 전에 각각의 조인을 따로따로 테스트해야 문제 해결에 용이
  

쿼리 결합

UNION 사용

SELECT cust_name, cust_contact, cust_email
FROM Customers 
WHERE cust_state IN ('IL', 'IN', 'MI');

+

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

=

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI');
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

AND

SELECT cust_name, cust_contact, cust_email
FROM Customers 
WHERE cust_state IN ('IL', 'IN', 'MI');
	 OR cust_name = 'Fun4All';

💡  UNION 규칙

• 반드시 두 개 이상의 SELECT 문으로 구성, 각각의 명령문은 UNION으로 구분
• 각 쿼리는 같은 열이나 수식, 그룹 함수를 가져야 하며 열 순서에 주의
• 열 데이터형은 호환될 수 있으며, 정확히 같은 데이터형일 필요는 없지만 DBMS가 내부적으로 변환 가능해야
• 열 이름이 다르다면, 첫 번째 이름으로 반환! 따라서 별칭 설정도 첫 번째 열에
• 쿼리 결과에서 자동으로 중복 행을 제거

⚠️  UNION 사용 후, 중복되는 행을 포함해서 모든 행을 가져오고 싶을 때

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI');
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

💡  UNION VS. WHERE

  UNION은 대부분 여러 개의 WHERE 조건을 사용하는 것과 같은 결과를 얻을 수 있으나,

  UNION ALL은 WHERE 절에서 갖고 올 수 없는 결과를 반환하므로

중복된 행을 포함해서 모든 조건과 일치하는 행을 가져오고 싶다면 반드시 UNION ALL 사용!

결합 쿼리 결과 정렬

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI');
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
ORDER BY cust_name, cust_contact;

• ORDER BY 절이 마지막 SELECT 문에 나오지만, 실제로는 모든 SELECT 문에 적용한 정렬 결과 반환

다른 UNION 타입

1. EXCEPT : 두 번째 테이블에는 없지만, 첫 번째 테이블에 있는 행을 가져올 때
2. INTERSECT : 두 개의 테이블에 모두 존재하는 행을 가져올 때
-- INTERSECT 대신 JOIN 사용해도 같은 결과 도출 가능, 거의 사용X

데이터 삽입

완전한 행 삽입

INSERT INTO Customers (cust_id,
											 cust_name,
											 cust_address,
											 cust_city,
											 cust_state,
											 cust_zip,
											 cust_country,
											 cust_contact,
											 cust_email)
VALUES (1000000006,
			 'Toy Land',
			 '123 Any Street',
			 'New York',
			 'NY',
			 '11111',
			 'USA',
			 NULL,
			 NULL);
			 
=

INSERT INTO Customers (cust_id,
											 cust_contact,
											 cust_email,
											 cust_name,
											 cust_address,
											 cust_city,
											 cust_state,
											 cust_zip,
											 cust_country)
VALUES (1000000006,
				NULL,
			  NULL,
			 'Toy Land',
			 '123 Any Street',
			 'New York',
			 'NY',
			 '11111',
			 'USA');

• 항상 열 목록을 사용하여 테이블이 변경되는 상황에도 계속 동작

⚠️  동일 레코드의 중복 INSERT 불가

⚠️  VALUES를 사용할 때 주의점

• VALUES에 있는 값은 열의 개수와 항상 동일하게
• 열 이름을 생략할 때는 테이블에 있는 모든 열의 수에 맞게, 열 목록을 사용하면 목록에 있는 열의 수에 맞도록 값을 작성
  

부분 행 삽입

INSERT INTO Customers (cust_id,
											 cust_name,
											 cust_address,
											 cust_city,
											 cust_state,
											 cust_zip,
											 cust_country)
VALUES (1000000006,
			 'Toy Land',
			 '123 Any Street',
			 'New York',
			 'NY',
			 '11111',
			 'USA');

⚠️  필수적인 값 누락 불가

⚠️  열 생략 조건

• 열을 정의할 때 NULL 값을 허용
• 테이블 정의에 기본값이 설정되어, 값이 명시되지 않으면 기본값으로 삽입

검색 결과 삽입 : INSERT SELECT 문

INSERT INTO Customers (cust_id,
											 cust_contact,
											 cust_email,
											 cust_name,
											 cust_address,
											 cust_city,
											 cust_state,
											 cust_zip,
											 cust_country)
SELECT cust_id,
			 cust_contact,
			 cust_email,
			 cust_name,
			 cust_address,
			 cust_city,
			 cust_state,
			 cust_zip,
			 cust_country
FROM CustNew;
-- CustNew에 있는 모든 데이터를 Customers로 가져온다.
-- VALUES로 넣을 데이터를 나열하는 대신에, SELECT 문으로 CustNew에서 값을 가져온다.
-- SELECT 절에 있는 각 열은 열 목록에 대응된다.
-- 이 문장을 수행하면 몇 개의 행이 삽입될까? CustNew 테이블에 있는 행의 수에 따라 다르다!
-- 테이블이 비어 있다면, 아무런 행도 삽입되지 않으나 작업은 유효하므로 에러는 발생하지 않는다.
-- 반면 테이블에 데이터가 있다면, 모든 데이터는 Customers에 삽입된다.

💡  INTER SELECT 안의 열 이름

열 이름이 반드시 같아야 할 필요는 없다. 그보다는 열의 위치를 사용하므로 순서가 중요하다!

💡  여러 개의 행 삽입

INSERT 문은 보통 하나의 행을 삽입하기 때문에 여러 개의 행을 삽입하려면 INSERT 문을 여러 번 실행

INSERT SELECT 문을 사용하면, 결과로 가져오는 행을 모두 삽입하므로 하나의 문장으로 여러 행 삽입 가능

다른 테이블로 복사 : CREATE SELECT 문

CREATE TABLE CustCopy AS SELECT * FROM Customers;

데이터 업데이트와 삭제

데이터 업데이트 : UPDATE 문

• 테이블에 있는 특정 행 업데이트
• 테이블에 있는 모든 행 업데이트

UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = 1000000005;
>> WHERE 절을 통해 특정 행 업데이트, WHERE 절이 없다면 모든 행을 새로운 이메일 주소로 업데이트

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = 1000000005;
>> 테이블이 NULL 값을 허용하도록 정의된 경우, 위와 같은 방식으로 데이터 업데이트 가능

💡  UPDATE 문의 기본 형태

• 업데이트할 테이블
• 열 이름과 새로운 값
• 어떤 행이 업데이트되어야 하는지를 지정하는 필터 조건

⚠️  실수로 테이블에 있는 모든 행을 업데이트할 수 있으므로 WHERE 절 누락하지 말기!

데이터 삭제 : DELETE 문

• 테이블에 있는 특정 행 삭제
• 테이블에 있는 모든 행 삭제

DELETE FROM Customers
WHERE cust_id = 1000000006;
>> 고객 아이디가 1000000006인 고객만 삭제

⚠️  실수로 테이블에 있는 모든 행을 삭제할 수 있으므로 WHERE 절 누락하지 말기!

💡  외래키

• 참조 무결성을 보장하기 위해 관계에 필요한 행을 삭제하지 못하도록 막는다.
• 그 경우, DELETE 문은 에러가 발생하고 해당 행은 삭제되지 않는다.

💡  DELETE는 테이블이 아닌 테이블 내용을 삭제

💡  모든 행을 삭제하고 싶을 때 빠른 삭제

• TRUNCATE TABLE 문을 사용!

업데이트와 삭제에 관한 가이드라인

• 모든 행을 업데이트하거나 삭제하려는 의도가 없는 한, WHERE 절 없이 사용X
• 모든 테이블이 기본 키를 가졌는지 확인하고, 가능한 한 WHERE 절에서 기본 키를 사용 (하나 또는 여러 개의 기본 키 지정, 범위 지정 사용 가능)
  
• UPDATE나 DELETE 문에서 WHERE 절을 사용하기 전, SELECT 문으로 올바른 행이 검색되는지 확인하여 잘못된 WHERE 사용을 막자!
  
• 참조 무결성을 사용하여 다른 테이블과 연결된 행을 함부로 삭제하지 못하도록 막자!
• 일부 DBMS는 데이터베이스 관리자가 WHERE 절 없는 UPDATE나 DELETE 문 수행을 막는다.

테이블 생성과 조작

기본 테이블 생성

• CREATE TABLE 문 뒤에 새로운 테이블 이름 작성
• 테이블 이름과 정의를 콤마(,)로 구분
• 일부 DBMS에서는 테이블의 위치도 명시

CREATE TABLE Products
(
  prod_id       CHAR(10)       NOT NULL,
  vend_id       CHAR(10)       NOT NULL,
  prod_name     CHAR(254)      NOT NULL,
  prod_price    DECIMAL(8, 2)  NOT NULL,
  prod_desc     VARCHAR(1000)  NULL
);

⚠️  존재하는 테이블 이름 사용 금지

💡  문장 형식

  SQL 문은 공백 문자를 무시하기 때문에 SQL 문을 하나의 긴 문장으로 작성 또는 여러줄로 나눠서 입력 가능

여러 줄로 SQL을 작성하는 것은 SQL 문을 사용하기 편이(열에 대한 정의 수정 편리, 가시적)

NULL 값 사용

CREATE TABLE Products
(
  prod_id       CHAR(10)       NOT NULL,
  vend_id       CHAR(10)       NOT NULL,
  prod_name     CHAR(254)      ,
  prod_price    DECIMAL(8, 2)  ,
  prod_desc     VARCHAR(1000)  
);

⚠️  NULL을 명시해야하는 DBMS도 존재

⚠️  기본 키와 NULL 값

기본 키는 테이블에서 각 행을 구별해주는 고유한 값을 가지는 열 (NULL 값 불허)

기본값 지정 : DEFAULT

CREATE TABLE Products
(
  prod_id       CHAR(10)       NOT NULL,
  vend_id       CHAR(10)       NOT NULL,
  prod_name     CHAR(254)      NOT NULL,
  prod_price    DECIMAL(8, 2)  NOT NULL DEFAULT 1,
  prod_desc     VARCHAR(1000)  NOT NULL
);

테이블 변경 : ALTER TABLE

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

ALTER TABLE Vendors
DROP COULMN vend_phone;

⚠️  ALTER TABLE 문 신중히 사용

필요 없는 열이 추가되었다면 그 열을 제거할 순 있으나, 실수로 필요한 열을 삭제했을 때는 복구 불가!

💡  복잡한 TABLE 구조 변경 시 진행 절차

1. 새로운 열 구조를 가진 새 테이블 생성

2. INSERT SELECT 문을 사용하여 이전의 테이블에 있는 데이터를 새로운 테이블에 복사

   필요하다면 변환 함수나 계산 필드를 사용

3. 새로운 테이블에 원하는 데이터가 있는 지 확인

4. 이전 테이블명을 변경 또는 과감히 삭제

5. 새로운 테이블을 이전 테이블명으로 변경

6. 필요하다면 트리거, 저장 프로시저, 인덱스, 외래 키 등을 다시 생성

💡  TABLE을 사용할 때 고려해야 할 점

• 기본적으로 데이터가 있는 테이블은 변경X 테이블 설계 시 충분한 시간 할애로 미래의 필요를 예측(나중에 많은 변경이 필요하지 않도록)
  
• 모든 DBMS는 존재하는 테이블에 열을 추가하는 것은 허용, 추가하는 열의 데이터형에 몇 가지 제약 추가
• 다수의 DBMS는 테이블에 있는 열을 제거하거나 변경하는 것을 불허, 열 이름 변경은 허용
• 다수의 DBMS는 데이터가 있는 열을 변경하는 것은 제한, 데이터가 없는 열에 대해서는 적은 제한

테이블 삭제

DROP TABLE CustCopy;

⚠️  실수로 테이블을 삭제하지 못하도록 관계 규칙 사용

테이블 이름 변경

ALTER TABLE Table1 RENAME TO Table2;

뷰 사용

: 데이터를 가진 테이블과는 달리 사용될 때 동적으로 데이터를 가져오는 쿼리들을 보유한 가상 테이블

💡  뷰를 사용하는 이유

1. SQL 문 재사용
2. 복잡한 SQL 작업을 단순화 (쿼리 그 자체에 대한 상세 내용을 몰라도 작성된 쿼리를 쉽게 재사용)
3. 테이블의 일부만 노출
4. 데이터 보호를 위해 테이블의 특정 부분에만 접근
5. 데이터 형식 변경

⚠️  뷰 규칙과 제한

1. 고유한 이름을 가질 것 (다른 테이블이나 뷰 이름 사용 불가)

2. 생성할 수 있는 뷰의 수 제한X

3. 뷰를 생성하기 위해 보안 권한 보유 필요

4. 뷰는 뷰를 포함 가능. 즉, 뷰는 다른 뷰에서 데이터를 가져오는 쿼리를 사용하여 제작 가능

   중첩 횟수는 DBMS마다 상이

5. 많은 DBMS에서 뷰 쿼리에서의 ORDER BY 절 사용 금지

6. 일부 DBMS에서는 가져오는 모든열에 이름 부여 필수

   열이 계산 필드라면, 별칭 사용

7. 뷰는 인덱스 사용 불가 및 트리거 또는 그와 연관된 기본값 사용 불가

8. Orcle에서는 읽기 전용 뷰 따로 생성 가능

9. 일부 DBMS에서는 삽입 또는 업데이트한 데이터가 뷰의 범위를 벗어나는 경우,

   삽입과 업데이트를 불허하는 뷰 생성 가능

뷰 생성

CREATE VIEW ProductCustomers
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
	AND OrderItems.order_num = Orders.orer_num;

가져온 데이터의 형식 변경을 위해 뷰 사용

SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
			 vend_title
FROM Vendors
ORDER BY vend_name;

>>

CREATE VIEW VendorLocations
SELECT RTRIM(vend_name) + '(' + + RTRIM(vend_country) + ')'
			 vend_title
FROM Vendors;

SELECT *
FROM VendorLocations;

⚠️  SELECT 제약 사항이 그대로 적용

원하지 않는 데이터를 필터링하기 위해 뷰 사용

CREATE VIEW CustomerEmailList
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

SELECT *
FROM CustomerEmailList;

⚠️  뷰에서 데이터를 가져올 때, WHERE 절을 사용하면 뷰에서 사용한 WHERE 절과 자동 결합

계산 필드와 함께 뷰 사용

SELECT prod_id,
			 quantity,
			 item_price,
			 quantity*item_price expanded_price
FROM OrderItems
WHERE order_num = 20008;

>>

CREATE VIEW OrderItemsExpanded
SELECT order_num,
			 prod_id,
			 quantity,
			 item_price,
			 quantity*item_price expanded_price
FROM OrderItems;

SELECT *
FROM OrderItemsExpanded
WHER order_num = 20008;

저장 프로시저 사용

💡  저장 프로시저를 사용하는 이유

1. 여러 단계로 이루어진 과정을 사용하기 쉬운 하나의 단위로 캡슐화하여 복잡한 작업을 단순화

2. 여러 단계를 반복해서 만들 필요가 없어 데이터 일관성 보장

3. 오류 방지에도 도움을 주어 데이터 일관성 보장

4. 테이블, 열 이름, 비즈니스 로직 등이 변경되면 저장 프로시저 코드 변경만 하면 되어 변경 관리를 단순화

5. 기본 데이터에 대한 접근을 제한하여 데이터 손상 가능성 저하, 보안성 증가

6. 대개 컴파일 형태로 저장, 명령 처리를 위해 DBMS가 하는 일이 줄어 성능 향상

7. SQL 언어 요소와 기능 중 하나의 요청 안에서만 사용해야 하는 것이 있는데,

   저장 프로시저는 좀 더 강력하고 유연한 코드를 작성하기 위해 이런 언어 요소와 기능을 사용

⚠️  저장 프로시저의 단점

1. DBMS마다 문법이 달라 다른 DBMS로 바꿔야 하는 경우 이식 난이

2. 기본 SQL 문 작성보다 복잡하여 저장 프로시저 작성 시 고수준의 기술과 경험 필요

   작성할 수 없더라도, 사용할 수는 있다!

저장 프로시저 실행

EXECUTE AddNewProduct ('JTS01',
											 'Stuffed Eiffel Tower',
											 6.49,
											 'Plush stuffed toy with the text La Tour Eiffel
											 in red white and blue');

💡  AddNewProduct가 하는 일

• 네 개의 매개변수 모두에 값이 있는지 확인, 데이터 전달
• 기본 키로 사용하는 고유한 ID 생성
• Products 테이블에 새로운 행 추가, 생성한 기본 키와 전달한 데이터를 적절한 열에 삽입
• DBMS에 따라 아래의 실행 옵션 추가 사용 가능
  • 매개변수가 없으면, 기본값으로 설정할 것인지를 결정하는 선택적 매개변수
  • ‘매개변수-값’ 형식으로 지정하는 비순차적 매개변수
  • 저장 프로시저에서 프로그램 실행 시 사용하는 매개변수를 업데이트할 수 있는 출력 매개변수
  • SELECT 문을 이용한 데이터 검색
  • 저장 프로시저에서 결괏값을 실행 프로그램으로 전달하는 데 사용하는 반환 코드

저장 프로시저 생성

IS
v_rows INTEGER;
BEGIN
		SELECT COUNT(*) INTO v_rows
		FROM Customers
		WHERE NOT cust_email IS NULL;
		ListCount := v_rows;
END;

-- 호출 :

var ReturnValue NUMBER;
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;

• 위의 저장 프로시저는 ListCount라는 이름의 매개변수 한 개 보유 이 매개변수는 저장 프로시저로 값을 전달하는 데 사용하는 게 아니라, 결괏값을 가져오는 데 사용 OUT 키워드는 이런 행동을 명시하기 위해 사용

  • IN : 저장 프로시저로 값을 전달

  • OUT : 저장 프로시저에서 값을 반환

  • INOUT : 위의 두 용도 모두

    저장 프로시저 코드는 BEGIN과 END 문으로 묶여 있고, 여기에서는 이메일 주소를 가진 고객을 가져오기 위해 간단한 SELECT 문을 수행 후 ListCount가 가져온 행의 수로 설정

트랜잭션 처리 관리

: DB의 부분적인 작업 수행을 막기 위해 여러 SQL 작업을 일괄적으로 처리하는 메커니즘

• 트랜잭션(Transaction) : 일괄 처리할 SQL 명령어들을 묶은 블록(Block)
• 롤백(Rollback) : 변경된 작업 내용을 모두 취소하는 절차
• 커밋(Commit) : 변경된 작업 내용을 DB에 저장
• 저장점(Savepoint) : 부분적으로 롤백하기 위한 임시 지점

💡  어떤 문을 롤백할 수 있을까?

트랜잭션 처리는 INSERT, UPDATE, DELETE 문을 관리하기 위해 사용

SELECT 문은 롤백 불가, (그럴 이유도 없지만) CREATE나 DROP 작업도 롤백 불가

트랜잭션 블록 안에서 사용은 되나, 롤백하더라도 작업을 되돌릴 수X

트랜잭션 통제

SET TRANSACTION
...

롤백 사용

DELETE FROM Orders;
ROLLBACK;

커밋 사용

SET TRANSACTION
DELETE OrderItems WHERE order_num = 12345;
DELETE Orders WHERE order_num = 12345;
COMMIT;

저장점 사용

SAVEPOINT delete1;

ROLLBACK TO delete1;

⚠️  저장점이 많이 사용될 수록 정확히 필요한 곳으로 롤백 가능

커서 사용

행을 앞뒤로 이동하며 데이터를 가져와야 할 필요가 있을 때 사용

💡  커서의 옵션과 기능

• 커서에 읽기 전용으로 표시
• 방향과 위치를 제어하는 기능(전방, 후방, 첫 번째, 마지막, 절대 위치, 상대 위치 등)
• 특정한 열만 수정 가능하게 표시, 그 외의 열은 수정하지 못하게 하는 기능
• 커서를 특정한 요청이나 모든 요청에 접근할 수 있게 하는 범위 지정 기능

커서 사용

• 커서는 반드시 사용 전에 선언, 이는 실제 어떤 데이터도 가져오지 않고 사용할 SELECT 문과 커서 옵션 정의
• 선언된 커서 사용 전 먼저 커서를 열어야하며, 이전에 정의한 SELECT 문으로 데이터를 공수
• 필요 시마다 데이터를 가진 커서에서 개별 행을 공수
• 커서를 다 사용했으면 커서를 닫고, 가능하면 해제

커서 제작

DECLARE CURSOR CustCursor
IS
SELECT * FROM Customers
WHERE cust_email IS NULL;

커서 사용

OPEN CURSOR CustCursor

-- 1. 커서에서 맨 위에 있는 한 행을 가져오기
DECLARE TYPE CustCursor IS REF CURSOR
	RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
	OPEN CustCursor;
	FETCH CustCursor INTO CustRecord;
	CLOSE CustCursor;
END;

-- 2. 커서의 첫 번째 행부터 마지막 행까지 루프돌기
DECLARE TYPE CustCursor IS REF CURSOR
	RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
	OPEN CustCursor;
	LOOP
	FETCH CustCursor INTO CustRecord;
	EXIT WHEN CustCursor%NOTFOUND;
	...
	END LOOP;
	CLOSE CustCursor;
END;

커서 닫기

CLOSE CustCursor

고급 데이터 조작 옵션

제약 조건

DB 데이터를 어떻게 삽입하고 조작할 것인지 통제하는 규칙

기본 키

💡  기본 키 설정 조건

• 두 개 이상의 행이 같은 기본 키 값을 가질 수X
• 모든 행은 기본 키 값을 반드시 가져야 한다(기본 키 열은 NULL 값을 허용하면 X)
• 기본 키 값을 가진 열은 변경 또는 업데이트 불가
• 기본 키 값은 재사용 불가 테이블에서 행이 삭제되어도, 그 값이 다른 행에 재할당X
  

CREATE TABLE Products
(
  prod_id       CHAR(10)       NOT NULL  PRIMARY KEY,
  vend_id       CHAR(10)       NOT NULL,
  prod_name     CHAR(254)      NOT NULL,
  prod_price    DECIMAL(8, 2)  NOT NULL,
  prod_desc     VARCHAR(1000)  NOT NULL
);

ALTER TABLE Products ADD CONSTRAINT PRIMARY KEY(prod_id);

외래 키

CREATE TABLE Orders
(
   order_num   INTEGER   NOT NULL   PRIMARY KEY,
   order_date  DATETIME  NOT NULL,
   cust_id     CHAR(10)  NOT NULL   REFERENCES   Customers(cust_id)
);

ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY(cust_id) REFERENCES Customers(cust_id);

⚠️  외래 키는 실수로 데이터를 삭제하는 것을 방지

고유 키 무결성 제약 조건

• 테이블은 여러 고유 키 무결성 제약 조건을 가질 수 있지만, 기본 키는 한 테이블에 하나만 정의
• 고유 키 무결성 제약 조건 열은 NULL 값 가능
• 고유 키 무결성 제약 조건 열은 변경 또는 업데이트 가능
• 고유 키 무결성 제약 조건 열의 값은 재사용 불가
• 기본 키와 달리 고유 키 무결성 제약 조건은 외캐 키로 정의되어 사용 불가

체크 무결성 제약 조건

• 최솟값이나 최댓값 확인
• 범위 지정
• 특정 값만 허용

CREATE TABLE OrderItems 
(
   order_num    INTEGER   NOT NULL,
   order_items  INTEGER   NOT NULL,
   prod_id      CHAR(10)  NOT NULL,
   quantity     INTEGER   NOT NULL   CHECK (quantity>0),
   item_price   MONEY     NOT NULL
);

ADD CONSTRAINT CHECK (gender LIKE '[MF]')

💡  사용자 정의 데이터형

일부 DBMS에서는 사용자가 직접 데이터형 정의 가능

이는 체크 무결성 제약 조건이나 다른 제약 조건을 사용하며 만든 데이터형

장점은 데이터형을 정의할 때 제약 조건을 한 번만 사용해도 데이터형이 사용될 때마다 자동으로 적용

인덱스 이해

💡  인덱스 제작 시 염두할 것

• 인덱스는 검색 성능을 개선하지만, 데이터 삽입, 수정, 삭제 성능은 저하 작업 수행 시마다 DBMS는 인덱스를 동적으로 업데이트 필요
  
• 인덱스 데이터는 저장 공간 다수 차지
• 모든 데이터가 인덱스에 적합한 것은 아니며 충분히 고유하지 않은 데이터는 성과 이름 같은 데이터보다 인덱스로 정의하여 얻는 이득 약소
  
• 인덱스는 데이터 필터링과 정렬에 사용되며 특정 순서로 데이터를 자주 정렬한다면, 인덱싱 후보 가능
• 여러 열을 하나의 인덱스로 정의 가능하며, 이는 도 이름 + 도시명 순서로 데이터 정렬 시에만 사용

CREATE INDEX prod_name_ind
ON Products (prod_name)

⚠️  정기적인 인덱스 점검 필요

트리거 이해

CREATE TRIGGER customer_state
AFTER INSERT OR UPDATE
FOR EACH ROW
BEGIN
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id
END;

💡  트리거에서 접근 가능한 데이터

• INSERT 작업으로 추가된 데이터
• UPDATE 작업으로 처리한 이전 데이터와 새로운 데이터
• DELETE 작업으로 삭제한 데이터

💡  트리거의 일반적인 용도

• 데이터 일관성 보장
• 테이블의 변화를 감지하여 특정한 작업을 수행
• 추가적인 데이터 유효성 검사나 데이터 롤백 수행
• (총액처럼) 다른 열들의 값을 기초로 어떠한 계산을 하거나 타입스탬프를 갱신

DB 보안

• 테이블 생성, 변경, 삭제와 같은 DB 관리 기능에 대한 접근
• 특정 DB나 테이블에 대한 접근
• ‘읽기 전용’, ‘특정 열에만 접근’과 같은 접근 유형
• 뷰나 저장 프로시저를 통해서만 접근할 수 있는 테이블 지정
• 로그인한 계정에 따라 접근과 제어 권한을

데이터형

문자열 데이터형

데이터형	설명
CHAR	1 - 255 길이의 문자열을 저장할 수 있는 고정 길이 문자열, 크기는 테이블을 생성할 때 정립
NCHAR	멀티바이트나 유니코드 문자를 지원하기 위해 고안된 특별한 형태의 고정 길이 문자열 데이터형(정확한 사양은 DBMS마다 상이)
NVARCHAR	멀티바이트나 유니코드 문자를 지원하기 위해 고안된 특별한 형태의 가변 길이 문자열 데이터형
TEXT(LONG, MEMO, VARCHAR)	가변 길이 문자형

• NCHAR나 NVARCHAR는 모든 언어의 문자 1개를 1바이트로 계산(실제 저장 공간 크기는 2배) CHAR나 VARCHAR가 영어 한 글자를 1바이트, 한글은 2바이트로 계산하는 것과 상이

  다국어 지원이 필요한 데이터베이스에 주로 사용

⚠️  숫자 값이 숫자가 아닐 때

만약 우편번호 01234를 수치형 열에 저장하면, 숫자 1234가 저장되어 숫자 0를 상실

숫자가 계산에 사용된다면 수치형 열에 저장하고, 아닌 경우에는 문자열애 저장하자!

수치형 데이터형

데이터형	설명
BIT	단일 BIT 값으로 0 또는 1, 주로 BOOLEAN(True, False)으로 사용
DECIMAL (NUMERIC)	고정 정밀도를 가진 값
FLOAT (NUMBER)	부동 소수점 값
INT (INTEGER)	4바이트의 정숫값으로 -2147483648 - 2147483647 범위의 수 지원
REAL	4바이트 부동 소수점 값
SMALLINT	2바이트 정숫값으로 -32768 - 32767 범위의 수 지원
TINYINT	1바이트 정숫값으로 0 - 255 범위의 수 지원

• 통화 데이터형은 화폐값을 저장하기에 적당한 형태로 만든 DECIMAL 데이터형

날짜와 시간 데이터형

데이터형	설명
DATE	날짜
DATETIME (TIMESTAMP)	날짜와 시간
SMALLDATETIME	날짜와 시간(초나 밀리세컨드(1/1000초) 단위가 아니라 분 단위 까지 저장)
TIME	시간

• ODBC 날짜 ODBC(Open DataBase Connectivity)는 MS가 만든 DB에 접근하기 위한 SW의 표준 규격 모든 DBMS가 날짜를 지정하는 형식이 상이하므로, ODBC는 모든 DB에서 동작하는 형식 제작 형식은 날짜의 경우 {d, ‘2020-12-30’}, 시간의 경우 {t, ‘21:46:29’}, 날짜와 시간의 경우 {ts, ‘2020-12-30 21:46:29’}
  

⚠️ 날짜 지정

모든 DBMS가 이해할 수 있게 날짜를 정의하는 표준 방법은 X

대부분의 실행 환경은 20202-12-30이나 Dec 30th, 2020과 같은 형태

바이너리 데이터형

데이터형	설명
BINARY	고정 길이 바이너리 데이터(최대 길이는 255-8000바이트로 실행 환경마다 상이)
LONG LAW	2GB까지 저장할 수 있는 가변 길이 바이너리 데이터
RAW (BINARY)	255바이트까지 저장할 수 있는 고정 길이 바이너리 데이터
VARBINARY	가변 길이 바이너리 데이터(최대 길이는 255-8000바이트로 실행 환경마다 상이)

---

출처 : 손에 잡히는 10분 SQL / 벤 포터 저자(글) · 박남혜 번역