[코리아IT아카데미] SQL 문법 MariaDB (서브쿼리, 단일행 서브쿼리, 서브쿼리 중첩, 다중행 서브쿼리, 다중열 서브쿼리, 무결성 관리, NULL 허용, 기본값)

1. 서브쿼리

 - 쿼리문 안에 또 다른 쿼리문이 포함된 구문, 복합적이고 단계적인 질문을 할 때는 여러 개의 쿼리를 중첩해서 사용

 - 단일행 서브쿼리(하나의 값만 리턴), 다중행 서브쿼리(여러개의 행을 리턴), 다중행열(여러개의 열 구성, 여러 행 리턴)

 

2. 단일행 서브쿼리

 - DB에서도 복잡한 쿼리를 실행할 수 있으며 그 방법이 바로 서브쿼리

 - 서브쿼리는 다른 쿼리문안에 내장되어 있는 SELECT문이며 연속적으로 실행할 쿼리르 하나로 합침

 - 이 때 감싸는 쿼리를 외부쿼리라고 하고, 외부쿼리와 구분하고 실행 순서를 명확히 지정하기 위해 서브쿼리를 감쌈

# 최대 인구수를 가진 도시명
SELECT name FROM tCity WHERE popu = (SELECT MAX(popu) from tCity);

# 가장 많은 아이템의 수를 구함
SELECT MAX(num) FROM tItem;
# 최대량인 80개의 상품을 조사
SELECT item FROM tItem WHERE num = 80;
# 두 명령을 하나로 합침 [서브쿼리]
SELECT item FROM titem WHERE num = (SELECT MAX(num) FROM titem);

 

3. 연습문제 1

# 1. tStaff에서 성취도가 제일 높은 직원을 조사하라
SELECT max(score) FROM tstaff;
SELECT NAME FROM tstaff WHERE score = 98.5;
SELECT NAME FROM tstaff WHERE score = (SELECT MAX(score) FROM tstaff);

# 2. tStaff에서 평균 이상의 월급을 받는 직원 목록을 출력하라
SELECT AVG(salary) FROM tstaff;
SELECT NAME FROM tstaff WHERE 393.5882 <= salary;
SELECT NAME FROM tstaff WHERE (SELECT AVG(salary) FROM tstaff) <= salary;

# 3. tEmployee와 tProject에서 노조 협상건을 맡은 직원의 이름과 월급을 구하라
SELECT employee FROM tproject WHERE project = '노조 협상건';
SELECT salary FROM temployee WHERE NAME = '권성직';
SELECT NAME, salary FROM temployee WHERE NAME = (SELECT employee FROM tproject WHERE project = '노조 협상건');

# 4. tMember에서 나이가 가장 많은 회원의 이름과 주소를 조사하라
SELECT MAX(age) FROM tmember;
SELECT addr FROM tmember WHERE age = 28;
SELECT member, addr FROM tmember where age = (SELECT MAX(age) FROM tmember);

 

4. 서브쿼리 중첩

 - 쿼리 순차적 진행이기에, 더 복잡한 형태의 질문도 가능

# 청바지 배송비가 얼마인지 조사하는 쿼리문 작성
# 먼저 Item 테이블에서 청바지가 어떤 유형의 상품인지 조사
SELECT category FROM titem WHERE item = '청바지';

# 다음은 패션 유형의 배송비를 조사
SELECT delivery FROM tcategory WHERE category = '패션';

# 두 쿼리문 합침. WHERE 절의 '패션' 자리에 상수 대신 '패션'이라는 결과를 만들어 작성
SELECT delivery FROM tcategory WHERE category = (SELECT category FROM titem WHERE item = '청바지');

# 70,000원 상품을 구매한 사람의 나이를 구하는 쿼리문 작성
# 70,000원 상품 조회
SELECT item FROM titem WHERE price = 70000;
# 70,000원 상품을 구매한 사람 조회
SELECT MEMBER FROM torder WHERE item = 
(SELECT item FROM titem WHERE price = 70000);
# 70,000원 상품을 구매한 사람의 나이 조회
SELECT age FROM tmember WHERE MEMBER = 
(SELECT MEMBER FROM torder WHERE item = 
(SELECT item FROM titem WHERE price = 70000));

 

5. 연습문제 2

# 1. 대추를 구입한 회원의 이름과 이 회원의 예치금을 구하라
SELECT MEMBER FROM torder WHERE item = '대추';
SELECT money, member FROM tmember WHERE MEMBER = '향단';
SELECT MEMBER, money FROM tmember WHERE MEMBER = (SELECT MEMBER FROM torder WHERE item = '대추');

# 2. 춘향이가 구입한 상품의 가격을 조사하라
SELECT item FROM torder WHERE MEMBER = '춘향';
SELECT price FROM titem WHERE item = '청바지';
SELECT price FROM titem WHERE item = (SELECT item FROM torder WHERE MEMBER = '춘향');

# 3. 배송비가 2000원인 상품을 구매한 회원의 주소를 조사하라
SELECT category FROM tcategory WHERE delivery = 2000;
SELECT item FROM titem WHERE category = '패션';
SELECT MEMBER FROM torder WHERE item = '청바지';
SELECT addr FROM tmember WHERE MEMBER = '춘향';
SELECT addr FROM tmember WHERE MEMBER = 
(SELECT MEMBER FROM torder WHERE item =
(SELECT item FROM titem WHERE category = 
(SELECT category FROM tcategory WHERE delivery = 2000)));

# 4. 배송비가 3000원인 상품을 구매한 회원의 주소 하나를 조사하라
# 값이 1개 초과로 나오는 상황일 때, LIMIT 적용!
SELECT addr FROM tmember WHERE MEMBER = 
(SELECT MEMBER FROM torder WHERE item = 
(SELECT item FROM titem WHERE category = 
(SELECT category FROM tcategory WHERE delivery = 3000) LIMIT 1));

 

6. 다중행 서브쿼리

 - 여러 개의 결과를 리턴하는 다중행 서브쿼리

 - 연산자로 비교하려면 조건절의 쿼리문은 반드시 하나의 단일 값을 리턴해야 함.

 - 꼭 조사하려면 결과 셋 중 하나의 값만 조사해서 비교하면 됨

SELECT price FROM titem WHERE item =
(SELECT item FROM torder WHERE MEMBER = '향단');

조건절 연산자 사용하고 값이 다수일 때, 에러가 나옴

 

 - 구입한 상품 중 하나의 상품만 리턴하면 = 연산자로 비교 가능. 다수의 값을 원한다면 IN 연산자 사용

 - IN은 = 과 달리 여러 개의 값과 비교. 

 

SELECT item, price FROM titem WHERE item IN
(SELECT item FROM torder WHERE MEMBER = '향단');

 

 

7. 다중열 서브쿼리 

 - 결과셋의 컬럼이 여러 개이며 한꺼번에 여러 값과 비교.

 - 오라클과 마리아디비는 다중열 서브쿼리를 잘 지원하지만 SQL Server는 아직 지원하지않음

 - 다중열이면서 다중행인 결과셋을 리턴하면 복수 레코드의 복수 필드를 한꺼번에 비교 가능

SELECT * FROM tstaff WHERE depart = (SELECT depart FROM tstaff WHERE NAME = '윤봉길')
AND gender = (SELECT gender FROM tstaff WHERE NAME = '윤봉길');

# 쿼리 보다 축약
SELECT * FROM tstaff WHERE (depart, gender) = 
(SELECT depart, gender FROM tstaff WHERE NAME = '안중근');

# 다중행 다중열
SELECT * FROM tstaff WHERE (depart, salary) IN
(SELECT depart, MAX(salary) FROM tstaff GROUP BY depart);

 

8. 연습문제 3 

1. 판매 데이터베이스에서 달콤비스킷을 생산한제조업체가 만든 제품들의 제품명과 단가를 검색해보자.
SELECT `제품명`, `단가` FROM `제품` WHERE `제조업체` = 
(SELECT `제조업체` FROM `제품` WHERE `제품명` = '달콤비스킷');

2. 판매 데이터베이스에서 적립금이 가장 많은 고객의 고객이름과 적립금을 검색해보자.
SELECT `고객이름`, `적립금` FROM `고객` WHERE `적립금` = 
(SELECT MAX(`적립금`) FROM `고객`)

# 3. 판매 데이터베이스에서 banana 고객이 주문한 제품의 제품명과 제조업체를 검색해보자
SELECT 제품명, 제조업체 FROM 제품 WHERE 제품번호 IN
(SELECT 주문제품 FROM 주문 WHERE 주문고객 = 'banana');

# 4. 판매 데이터베이스에서 banana 고객이 주문하지않은 제품의 제품명과 제조업체를 검색해보자
SELECT 제품명, 제조업체 FROM 제품 WHERE 제품번호 NOT IN
(SELECT 주문제품 FROM 주문 WHERE 제조업체 = '대한식품');

 

9. 무결성 관리

 - 데이터베이스 또한 결함이 생길 수 있어, 이를 방지하기 위해 최선의 노력을 해야됨

 

10. NULL 허용

 - NULL은 아무것도 입력되어 있지 않은 것이며 알 수 없거나 결정되지 않은 특수한 상태

 - 필수 입력 필드는 NULL 허용해서는 안되며, 없어도 괜찮은 옵션 필드는 NULL 허용해도 상관없음

 - 필드의 NULL 허용 속성은 테이블을 생성할 때 필드 속성 뒤에 지정

 - NULL이라 지정하면 NULL을 허용하지 않는다는 뜻, NOT NULL이라고 적으면 NULL을 허용하지 않는다는 뜻

CREATE TABLE tCity (
	name CHAR(10) PRIMARY KEY,
    area INT NULL,
    popu INT NULL,
    metro CHAR(1) NOT NULL,
    region CHAR(6) NOT NULL
);

# 다음 두 쿼리문은 정상 실행
INSERT INTO tcity (NAME, popu, metro, region) VALUES ('울산', 114, 'y', '경상');
INSERT INTO tcity (NAME, metro, region) VALUES ('삼척', 'n', '강원');

# 그러나 다음 두 쿼리문은 에러가 남
INSERT INTO tcity (AREA, popu, metro, region) VALUES (456, 123, 'n,','충청');
INSERT INTO tcity (NAME, AREA, popu) VALUES ('의정부', 456, 123);

- NULL 허용 속성을 DBMS 마다 다르기에 항상 NULL, NOT NULL을 명시하는 것이 바람직하다.

CREATE TABLE tNullable (
NAME CHAR(10) NOT NULL,
age INT
);

INSERT INTO tNullable (NAME, age) VALUES ('흥부', 36);
INSERT INTO tNullable (NAME) VALUES ('놀부');
INSERT INTO tNullable (age) VALUES (37);

 

11. 기본값

 - NULL 허용 속성은 데이터베이스 성능을 저해하는 주범 (항상 NULL인지 점검해야 해서 느릴 수 밖에 없음)

 - NULL 허용 대신 기본값 DEFAULT을 사용하는 것이 성능상 유리

 - 기본값 : 필드값을 지정하지 않을 때 자동으로 입력할 값

CREATE TABLE tCityDefault (
	NAME CHAR(10) PRIMARY KEY,
	AREA INT NULL,
	popu INT NULL,
	metro CHAR(1) DEFAULT 'n' NOT NULL,
	region CHAR(6) NOT NULL);
    
INSERT INTO tCityDefault (NAME,AREA, popu, region) VALUES ('진주', 712, 34, '경상');
INSERT INTO tCityDefault (NAME, AREA, popu, metro, region) VALUES ('인천', 1063, 295, 'y', '경기');

# 에러
INSERT INTO tCityDefault VALUES ('강름', 1111, 22, '강원');
#정상 실행
INSERT INTO tCityDefault VALUES ('강름', 1111, 22, DEFAULT, '강원');
# 기본값을 사용할 때, DEFAULT 키워드 사용가능
UPDATE tCityDefault SET metro = DEFAULT WHERE name = '인천';

 - 기본값의 유무와 NULL 허용 여부는 완전히 별개의 속성임을 주의.

 - UPDATE 명령으로 NULL로 바꿀 수도 있음