데이터베이스

01. SQL

saniii 2022. 12. 29. 21:20

INSERT

: 새로운 레코드를 추가하는 명령

INSERT INTO 테이블 (필드목록) VALUES (값목록)

INSERT INTO tCity (name, area, popu, region)
VALUES ('서울', 605, 974, '경기');

다음과 같이 쓰면 순서대로 대응되어 저장된다. 더 짧게 쓸 수 있다.

INSERT INTO tCity 
VALUES ('서울', 605, 974, '경기');

하지만 필드 목록이 없는 대신 값 목록이 완전하고 순서도 반드시 지켜져야 한다. 필드 목록을 같이 적는 다면 필드에 대응되는 순서로만 적는다면 테이블의 필드 순서와는 바뀌어도 상괸없다.

UPDATE

: 레코드의 필드값을 변경할 때

UPDATE 테이블 SET 필드=값 [, 필드=값] WHERE 조건;

UPDATE tCity SET popu = 1000, region = '충청'
WHERE name = '서울';

DELETE

: 레코드를 삭제

DELETE FROM 테이블 WHERE 조건

삭제 동작은 특정 조건에 맞는 레코드를 찾아 제거하는 경우가 대부분이라 WHERE 절이 뒤따라 오는 경우가 일반적이다.

DELETE 명령은 별도의 확인없이 조건에 맞는 레코드를 즉시 삭제한다.

SQL: 데이터베이스 쿼리

  • 관계 대수적 용어의 쿼리 종류
  • 단순 선택 쿼리
  • 기본 검색 조건 및 연산자 사용
  • 주문결과

관계 대수로서의 쿼리

  • 관계 대수 연산은 다른 관계를 생성하기 위해 하나 이상의 관계에 대해 작업한다.
  • 피연산자와 결과는 모두 관계이므로 한 작업의 출력이 다른 작업에 입력될 수 있습니다.
  • 연산은 중첩될 수 있습니다(산술에서와 같이).

관계 대수

  • 관계 대수의 다섯 가지 기본 연산:
    • 선택, 투영, 데카르트 곱, 결합 및 차이 설정
    • 필요한 대부분의 데이터 검색 작업을 수행합니다.
    • 또한 5개의 기본 연산으로 표현될 수 있는 결합, 교차 및 분할 연산이 있습니다.

DISTINCT

: 중복된 값을 제거

SELECT region FROM tCity;
-- 있는 region값이 다 출력된다. (중복ㅇ)

SELECT DISTINCT region FROM tCity;
-- 있는 region값을 중복은 제거하고 출력. (중복X)

-- 중복제거하고 정렬하여 출력하고 싶을 떄
SELECT DISTINCT region FROM tCity
ORDER BY region;

AS

  • 별명
필드명 AS "별명"
  • 계산값의 출력
SELECT name, popu * 1000 AS "인구(명)"
FROM tCity;

WHERE

: 읽을 레코드의 조건을 지정

WHERE 절이 없으면 모든 레코드를 다 조회한다. WHERE절에는 레코드를 선택하는 조건문이 온다.

SELECT * FROM tCity
WHERE area > 1000;

조건문은 필드와 상수, 변수 등을 비교하는 표현식이되 비교 대상끼리 타입이 호환되어야 한다. 숫자는 상수를 그냥 쓰지만 문자열과 날짜 상수는 항상 작은 따옴표로 감싸야 한다. 따옴표 없이 문자열을 그냥 쓰면 필드명으로 인식한다.

AND, OR

: 두 개 이상의 조건을 동시에 점검할 때는 논리 연산자를 사용

  • AND : 두 조건이 모두 참인 레코드를 검색
  • OR : 두 조건 중 하나라도 참인 레코드를 검색
SELECT * FROM tCity 
WHERE popu >= 100 AND area >= 700;

세 개 이상의 조건문을 지정할 떄는 조건의 우선순위에 주의해야 한다. AND의 우선순위가 OR보다 높다. 괄호를 통해서 명확하게 표현할 수 있다.

BETWEEN

BETWEEN 최소값 AND 최대값

SELECT * FROM tCity
WHERE popu BETWEEN 50 AND 100;

연속적인 범위만 검색할 수 있으며 불연속적이고 임의적인 값 여러 개를 조사하기 어렵다. → 수치값 검색에 이용

IN

불연속적인 값 여러 개의 목록을 제공하여 이 목록과 일치하는 레코드를 검색한다. 여러 값 중 하나라도 해당하는지 점검하므로 각각의 비교문을 OR 연산자로 연결하여 표현할 수 있다.

SELECT * FROM tCity 
WHERE region  NOT IN ('경상', '전라');

SELECT * FROM tCity
WHERE region LIKE '%천%' 
		OR region LIKE '경%';

LIKE

: 패턴으로 부분 문자열을 검색

e.g. 성이 김씨인 사람, 주소가 강남구인 사람 등을 검색할 때

# 와일드카드

SELECT * FROM tCity 
WHERE name LIKE '%천%';

!LIKE → NOT LIKE

IS NULL

NULL은 값이 아니라 상태이기 때문에 필드명 = NULL 과 같은 식으로 비교할 수 없다. 따라서 IS NULL 연산자를 제공하여 NULL 상태를 비교할 수 있도록 한다.

SELECT * FROM tCity WHERE score IS NULL;
SELECT * FROM tCity WHERE score IS NOT NULL;

ORDER BY

관계형 DB에서 레코드의 물리적인 순서는 별로 중요하지 않지만 ORDER BY를 이용해서 원하는 대로 정렬할 수 있다.

ORDER BY 필드명 [ASC|DESC]

SELECT * FROM tCity ORDER BY popu DESC;

SELECT * FROM tCity 
		ORDER BY region, name DESC;
-- region은 지정하지 않았으므로 오름차순 name은 
-- DESC로 지정했으므로 내림차순으로 정렬된다. 

SELECT name, popu * 10000 / area
FROM tCity
ORDER BY popu * 10000 / area;

SELECT * FROM tCity 
WHERE region = '경기'
ORDER BY area;
-- 경기도에 있는 도시만 골라 면적별로 정렬

레코드 수가 많으면 동률이 생길 확률이 높아 다수의 정렬 기준이 필요하다.

subquery

[참고]

서브쿼리는 쿼리문 안에 또 다른 쿼리문이 포함된 구문이다. 복합적이고 단계적인 질문을 할 때는 여러 개의 쿼리를 중첩해서 사용한다. SELECT로 할 수 있는 질문은 아주 짧은 단문이고, 하나의 테이블에 있는 정보만 조사할 수 있어서 실생활에 맞는 복잡한 질문을 할 수 없다. (e.g. 너희 학교 교장 선생님의 전화번호는?)

서브쿼리 예시

SELECT name FROM tCity WHERE popu = (SELECT MAX(popu) FROM tCity);

단일행 서브쿼리는 하나의 결과만 리턴하며 주로 WHERE, HAVING 등의 조건절에 사용한다.

  • 결과열이 일부 테이블에서 오는 경우 서브쿼리 사용
  • 결과열이 둘 이상의 테이블에서 오는 경우 조인 사용
  • 조인을 수행하려면 FROM 절에 둘 이상의 테이블을 포함합니다.
  • 쉼표를 구분 기호로 사용하고 일반적으로 WHERE 절을 포함하여 조인 열을 지정합니다.

별명

테이블의 필드명은 구분 가능하고 입력하기 쉬운 짧은 명칭일 뿐이어서 사용자가 읽기에 직관적이지 않다. 따라서 필드에 대한 별명을 지정하여 결과셋의 헤더에 필드 이름 대신 별명을 출력한다.

필드명 AS "별명"

별명은 명칭이 아니며 공백이나 특수문자를 포함할 수 있다. 선언시 큰 따옴표로 감싸는 것이 일반적이지만 평이한 단어라면 생략해도 된다.

Join

  1. 단순 조인 (Cross join)

단순 조인은 두 개 이상의 테이블을 특별한 조건없이 논리곱으로 조합한다.

FROM절에 출력 대상 테이블들을 콤마로 구분하여 나열한다.

SELECT * FROM tCar, tMaker;

SELECT * FROM tCar CROSS JOIN tMaker;

단순히 조인하기만 하면 각 테이블의 레코드 개수를 곱한 결과(논리곱, cartestian Product)가 나온다. → WHERE 조건절에 조건을 달아서 원하는 조합만 표시할 수 있다.

양쪽 체이블의 중복 필드를 없애고 원하는 필드만 골라 출력하는 방식을 자연 조인이라고 한다.

  1. 내부조인 (Inner join)

: 각 테이블의 필드값을 비교하여 조건에 맞는 레코드만 선택적으로 가져오는 명령

FROM 절의 INNER JOIN 양쪽에 조인할 테이블명을 지정하고 ON 다음에 조인 조건을 작성한다.

SELECT 필드 FROM A [INNER] JOIN B
		ON 조건;

조건문에는 주로 두 테이블의 필드를 비교하는 연산문이 온다.

단순 조인은 조건절을 생략하고 논리곱을 볼 수 있지만 내부 조인은 ON절이 필수이다.

단순조인은 표준에서 권장하지 않아 내부조인을 쓰는 것이 바람직하다.

조인 조건문 ON에서 주로 외래키를 비교하기 때문에 양쪽 테이블의 필드명이 같은 경우가 많다.

조인은 한번에 3,4개의 테이블을 조인하지 않고 2개의 테이블만 조합한다. 따라서 조인을 중첩하여 여러 개의 테이블을 조인한다. ⇒ 다중 조인

다중 조인을 하려면 최소 3개 이상의 테이블이 관계형으로 묶여 있어야 한다.

GROUP BY

: 기준이 되는 필드를 뒤에 적어주면 기준 필드가 같은 레코드를 모아준다.

기준별로 그룹을 모아 집계함수를 같이 사용할 수 있다.

SELECT depart, AVG(salary) 
FROM tStaff GROUP BY depart;

GROUP BY는 중복값이 있을 때만 의미가 있다. → 그룹화를 하려면 같은 값을 가진 레코드가 2이상 있어야 하기 때문

GROUP BY 절이 있으면 필드 목록에는 기준 필드나 집계 함수만 올 수 있으며 그룹과 상관없는 필드는 집계 함수 없이 단독으로 출력할 수 없다.

외부 조인, OUTER JOIN

내부 조인은 조건에 맞지 않으면 레코드를 출력하지 않는다. 외부 조인은 이와 달리 조건에 맞지 않는 레코드도 같이 출력한다.

외부 조인은 주종 관계에 있는 테이블에서 주테이블의 모든 레코드를 보여주고 조건을 만족하는 부테이블의 필드를 같이 출력한다.

  • LEFT OUTER JOIN : 주 → 왼 , 종 → 오
  • RIGHT OUTER JOIN : 주 → 오 , 종 → 왼
  • FULL OUTER JOIN : 주 → 왼 , 오

완전조인 FULL OUTER JOIN : 양쪽 테이블의 모든 레코드를 다 출력하고 조인 조건에 맞는 레코드는 반대쪽의 정보도 같이 보여준다.

조건에 맞지 않아 대응되는 정보가 없을때는 Null로 표시

사실 완전 외부 조인은 실용적 가치가 없어서 굳이 없어도 된다. 만일 정 필요하다면 다음과 같은 방법으로 대체할 수 있다.

왼쪽 외부 조인 UNION 오른쪽 외부 조인

조인 Join

조인, JOIN : 복수 개의 테이블에서 조건에 맞는 데이터를 조회하는 방법

  • 구문이 길고 처리 시간이 오려 걸려 정확성과 효율성을 항상 고려해야한다.

HAVING

: GROUP BY 다음에 오며 통계 결과 중 출력할 그룹의 조건을 지정한다. → GROUP BY의 조건절

SELECT depart, AVG(salary) FROM tStaff
GROUP BY depart HAVING AVG(salary) >= 340
ORDER BY AVG(salary);

GROUP BY 다음에 HAVING이 오고 ORDER BY는 항상 제일 마지막이다. 집계를 해야 조건을 걸 수 있고 출력할 레코드를 선정해야 순서를 결정할 수 있다.

SELECT .. FROM .. WHERE .. GROUP BY 
... HAVING .. ORDER BY

# WHERE / HAVING

SELECT depart, MAX(salary) FROM tStaff
WHERE depart IN ('인사과', '영업부') 
	GROUP BY depart;

SELECT depart, MAX(salary) FROM tStaff
WHERE GROUP BY depart HAVING depart
		IN ('인사과', '영업부');

두 쿼리 모두 인사과와 영업부의 최대 월급을 조사하지만 실행과정에 차이가 있다. WHERE은 집계 전에 총무부를 제외하여 꼭 필요한 계산만 하지만 HAVING은 모든 부서의 집계를 다 끝낸 후 총무부를 제외하고 출력하기 때문에 출력하지도 않을 총무부의 집계까지 하여 비효율적이다.

서브쿼리

청바지의 배송비가 알고 싶을 때

SELECT delivery FROM tCategory 
WHERE category = 
	(SELECT category FROM tItem 
			WHERE item = '청바지');

tItem테이블에서 item필드가 청바지인 레코드의 카테고리 값과 카테고리 필드의 값이 같으며 tCategory테이블에 있는 레코드의 delivery 필드 값을 출력하시오.

→ 청바지는 상품 테이블에 있지만 배송비는 유형 테이블에 있어 두 테이블을 읽어야했다. 먼저 tItem테이블에서 청바지가 어떤 유형의 상품인지 조사하고 이 유형의 배송비를 출력하도록한다.

서브쿼리 중첩

서브쿼리의 중첩 횟수에는 제한이 없다.

7만원짜리 상품을 구입한 사람의 나이를 구해보

SELECT age FROM tMember WHERE name =
(SELECT name FROM tOrder WHERE item = 
(SELECT item FROM tProduct 
				WHERE price = 70000));

가격이 70000인 상품을 상품테이블에서 찾아서 그 상품의 이름을 알아내고 주문 페이지에서 해당 상품을 산 사람의 이름을 알아내고 고객 테이블에서 해당 고객의 나이를 출력한다.

다중행 서브쿼리

서브쿼리의 결과가 하나뿐인 유형을 단일행 서브쿼리, 여러 개의 결과를 리턴하는 것을 다중행 서브쿼리라고 한다. 다중행 서브쿼리는 단일값을 출력하지 않기 때문에 비교 연산자 = 를 사용할 수 없다. IN은 하나하나 비교하기 때문에 사용할 수 있다.

ANY / ALL //// 이거 좀 더 찾아보기 예시

IN 연산자는 괄호 안의 결과셋과 순차적으로 상등비교하지만 ANY, ALL은 결과셋 전체와 비교한다.

ANY : 하나라도 만족하는 값이 있으면 참, OR

ALL : 모두 만족하면 참, AND

SELECT name FROM tStaff WHERE salary > ANY
(SELECT salary FROM tStaff 
						WHERE depart = '영업부');

SELECT name FROM tStaff WHERE salary > ALL
(SELECT salary FROM tStaff 
						WHERE depart = '영업부');

SOME

ANY와 동의어지만 잘 사용하지 않는다.

UNION

: 복수의 결과셋에 대한 합집합 생성

-- (1)
SELECT * FROM tItem WHERE category = '식품'
UNION
SELECT * FROM tItem WHERE category = '가전';

-- (2)
SELECT member FROM tMember
UNION
SELECT name FROM tStaff
UNION
SELECT name FROM tEmployee;

UNION은 두 쿼리에 대한 결과값을 합쳐서 보여주며 만일 중복되는 레코드가 있으면 중복을 제거해서 출력한다. (1)과 같이 같은 테이블에서도 사용할 수 있지만 이런 경우는 굳이 UNION을 사용하지 않고 조건을 OR로 묶는 것이 더 간단하다. UNION은 (2)와 같이 다른 테이블, 쿼리의 결과도 합칠 수 있다.

다만 합칠 결과셋에 대해 논리적 의미와 필드 타입을 맞춰야한다.

  • 관리 목적상 테이블을 일정한 기준에 따라 나누어 놓은 경우 잠시 합집합이 필요할 때 사용 e.g. 1월 매출과 2월 매출을 함께 보고 싶을 때
  • 다른 DB, 다른 서버의 테이블을 합쳐서 보고 싶을 때. e.g. 지점별로 매출 테이블을 관리할 때 물리적으로는 분리되어 있으나 테이블 구조는 동일하므로 각 지점의 매출을 합쳐 보고 싶을 때
  • UNION ALL 을 사용하면 중복을 제거하지 않고 출력할 수 있다. 중복 제거 과정이 없어서 더 빠르다.

INTERSECT

: 교집합, 두 결과셋에 모두 포함된 레코드만으로 집합을 구성,중복은 제거한다.

-- (1)
SELECT name FROM tStaff WHERE depart = '영업부'
INTERSECT
SELECT name FROM tStaff WHERE gender = '여';

-- (2)
SELECT name FROM tStaff
INTERSECT
SELECT member FROM tMember;

(1)의 경우 같은 테이블에서 이루어지는 간단한 교집합이므로 AND 연산자를 사용하는 것이 더 효율적이다. INTERSECT는 (2)의 경우 같이 다른 테이블끼리 교집합을 구할 수 있다.

EXCEPT

저작자표시 비영리 변경금지