SQL 고급 문법

bong'59 ·2023년 10월 23일

Database mysql

DATABASE

목록 보기

2/2

SQL 고급 문법

04-1 MySQL의 데이터 형식

테이블을 만들 때는 데이터 형식을 설정해야 한다. 데이터 형식에서는 크게 숫자형, 문자형, 날짜형이 있다.

또 세부적으로는 여러 개로 나뉘기도 한다. 이렇게 다양한 데이터 형식이 존재하는 이유는 실제로 저장될 데이터의 형태가 다양하기 때문이다.

각 데이터에 맞는 데이터 형식을 지정함으로써 효율적으로 저장할 수 있다.

데이터 형식

MySQL에서 제공하는 데이터 형식의 종류는 수십 개 정도이고, 각 데이터 형식마다 크기나 표현할 수 있는 숫자의 범위가 다르다.

정수형

정수형은 소수점이 없는 숫자, 즉 인원 수, 가격, 수량 등에 많이 쓰인다. 정수형의 크기와 범위는 다음과 같다.

데이터 형식	바이트 수	숫자 범위
TINYINT	1	-128 ~ 127
SMALLINT	2	-32,768 ~ 32,767
INT	4	약 -21억 ~ +21억
BIGINT	8	약 -900경 ~ +900경

CREATE TABLE hongong4 (
	tinyint_col  TINYINT,
	smallint_col SAMLLINT,
	int_col      INT,
	bigint_col   BIGINT
);

각 열의 최대값을 입력해보자. 이상 없이 입력될 것이다.

INSERT INTO hongong4 VALUES(127, 32767, 2147483647, 9000000000000000000);

이번에는 각 숫자에 1을 더해서 입력해보자. 마지막 값에는 0을 하나 더 붙였다.

INSERT INTO hongong4 VALUES(128, 32768, 2147483648, 90000000000000000000);

Untitled

다음과 같이 오류가 발생했다. Out of range는 입력값의 범위를 벗어났다는 의미이다.

정수형을 사용하는 예제를 살펴보자.

앞에서 만들었던 인터넷 마켓의 회원의 회원 테이블(member)에서 인원수 (mem_number) 열은 INT로, 평균 키(height) 열은 SAMLLINT로 저징했다.

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member -- 회원 테이블
( mem_id        CHAR(8) NOT NULL PRIMARY KEY, -- 회원 아이디(PK)
  mem_name      VARCHAR(10) NOT NULL, -- 이름
  mem_number    INT NOT NULL,  -- 인원수
  addr          CHAR(2) NOT NULL, -- 주소(경기,서울,경남 식으로 2글자만입력)
  phone1        CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2        CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height        SMALLINT,  -- 평균 키
  debut_date    DATE  -- 데뷔 일자
);

인원수(mem_number) 열은 INT로 지정해서 -21억 ~ +21억까지 저장할 수 있따.

가수 그룹의 인원이 이렇게 많을 필요는 없다. 그래서 최대 127명까지 지정할 수 있는 TINYINT로 지정해도 충분하다.

평균 키(height) 열은 SAMLLINT 로 지정해서 -32768 ~ 32767까지 저장할 수 있다.

키 역시 30000cm가 넘을 리는 없으므로 TINYINT를 고려 할 수 있다.

하지만 TINYINT는 -128 ~ +127로 200CM가 넘는 사람도 있으므로 범위가 부족하다..

이를 해결하기 위해 값의 범위가 0부터 시작되는 UNSIGNED 예약어를 사용할 수 있다.

TINYINT와 TINYINT UNSIGNED 모두 1바이트 크기이다.

1바이트는 256개를 표현하므로 -128 ~ +127로 표현하거나, 0~255로 표현하거나 모두 256개를 표현하는 것이다.

결국 회원 테이블은 다음과 같이 구성하는 것이 더 효율적이다.

DROP TABLE IF EXISTS member;
CREATE TABLE member -- 회원 테이블
( mem_id        CHAR(8) NOT NULL PRIMARY KEY, -- 회원 아이디(PK)
  mem_name      VARCHAR(10) NOT NULL, -- 이름
  mem_number    TINYINT  NOT NULL,  -- 인원수
  addr          CHAR(2) NOT NULL, -- 주소(경기,서울,경남 식으로 2글자만입력)
  phone1        CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2        CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height        TINYINT UNSIGNED,  -- 평균 키
  debut_date    DATE  -- 데뷔 일자
);

나머지 정수형도 마찬가지로 UNSIGNED를 붙이면 0부터 범위가 지정된다.

문자형

문자형은 글자를 저장하기 위해 사용하며, 입력할 최대 글자의 개수를 지정해야 한다.

대표적인 문자형은 다음과 같다.

데이터 형식	바이트 수
CHAR(개수)	1 ~ 255
VARCHAR(개수)	1 ~ 16383

CHAR는 문자를 의미하는 Character의 약자로, 고정길이 문자형이라고 부른다.

즉, 자릿수가 고정되어 있다. 예를 들어 CHAR(10)에 ‘가나다’ 3글자만 저장해도 10자리를 모두 확보한 후에 앞에 3자리를 사용하고 뒤의 7자리는 낭비하게 된다.

이와 달리 VARCHAR(Variable Character)는 가변길이 문자형으로, VARCHAR(10)에 ‘가나다’ 3글자를 저장할 경우 3자리만 사용한다.

CHAR, VARCHAR로만 쓰면 CHAR(1), VARCHAR(1)과 동일하다.

VARCHAR가 CHAR보다 공간을 효율적으로 운영할 수 있지만, MySQL 내부적으로 성능(빠른 속도) 면에서는 CHAR로 설정하는 것이 조금 더 좋다.

예를 들어, 거주 지역을 서울/부산/경기/경북과 같이 시도만 저장할 경우에는 모두 2글자로 일정하다.

이때는 CHAR(2)로 설정하는 것이 좋다. 반면 가수 그릅의 이름은 ‘잇지’처럼 2글자도 있지만, ‘방탄소년단’과 같이 좀 더 긴 글자도 있다. 그래서 그릅 이름은 VARCHAR로 설정하는 것이 좋다.

회원 테이블의 문자형을 확인해보자.

DROP TABLE IF EXISTS member;
CREATE TABLE member -- 회원 테이블
( mem_id        CHAR(8) NOT NULL PRIMARY KEY, -- 회원 아이디(PK)
  mem_name      VARCHAR(10) NOT NULL, -- 이름
  mem_number    TINYINT  NOT NULL,  -- 인원수
  addr          CHAR(2) NOT NULL, -- 주소(경기,서울,경남 식으로 2글자만입력)
  phone1        CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2        CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height        TINYINT UNSIGNED,  -- 평균 키
  debut_date    DATE  -- 데뷔 일자
);

회원 아이디(mem_id)는 BLK, APK, GRL 등 3글자로 입력되는데, 데이터 형식은 CHAR(8)로 설정되어 있다.

CHAR(8)을 CHAR(3)으로 줄여도 되지만 향후에 더 긴 회원 아이디를 만들 수 있다고 가정하고 CHAR(8)로 설정했다. VARCHAR(8)로 변경해도 별 문제는 없다.

또, 관심있게 볼 부분이 연락처 국번(phone1)과 연락처 전화번호(phone2)이다.

연락처 국번은 02, 031, 055 등과 같이 제일 앞에 0이 붙어야 하는데 정수형으로 지정하면 0이 사라진다. 그래서 CHAR로 지정했다.

전화번호 역시 모두 숫자로 이루어져서 정수형으로 지정해야 할 것 같지만 CHAR로 지정되어 있다. 이유는 전화번호가 숫자로서 의미가 없기 때문이다.

숫자로서 의미를 가지려면 다음 2가지 중 1가지는 충족해야 한다.

더하기 / 빼기 등의 연산에 의미가 있다.
크다 / 작다 또는 순서에 의미가 있다.

전화번호는 위 2가지 중 어떤 것에도 해당하지 않는다. 그래서 전화번호는 숫자가 아닌 문자로 지정했다.

대량의 데이터 형식

문자형인 CHAR은 최대 255자까지, VARCHAR는 최대 16383자까지 지정이 가능하다.

즉, 다음과 같은 테이블 만들기는 오류가 발생한다. 열의 길이를 너무 크게 설정했다는 오류이다.

CREATE TABLE big_table (
	data1 CHAR(256),
	data2 VARCHAR(16384)
);

Untitled

그래서 더 큰 데이터를 저장하려면 다음과 같은 형식을 사용한다.

TEXT 형식
- TEXT
  - 1 ~ 65535
- LONGTEXT
  - 1 ~ 4294967295
BLOB 형식
- BLOB
  - 1 ~ 65535
- LONGBLOB
  - 1 ~ 4294967295

TEXT로 지정하면 최대 65535자까지, LONGTEXT로 지정하면 최대 약 42억자까지 저장된다.

또, BLOB라는 용어가 등장했는데 BLOB는 Binary Long Object의 약자로 글자가 아닌 이미지, 동영상 등의 데이터라고 생각하면 된다. 이런 것을 이진(Binary) 데이터라고 부른다.

테이블에 사진이나 동영상과 같은 것을 저장하고 싶다면 BLOB이나 LONGBLOB로 데이터 형식을 지정해야 한다.

CREATE DATABASE netflix_db;
USE netflix_db;
CREATE TABLE movie 
  (movie_id        INT,
   movie_title     VARCHAR(30),
   movie_director  VARCHAR(20),
   movie_star      VARCHAR(20),
   movie_script    LONGTEXT,
   movie_film      LONGBLOB
);

다른 열은 정수형이나 문자형으로 지정하면 되며, 자막 (movie_script) 열은 LONGTEXT, 동영상(movie_film) 열은 LONGBLOB로 설정해야 대용량의 텍스트와 이진 데이터를 저장할 수 있다.

LONGTEXT 및 LONGBLOB로 설정하면 각 데이터는 최대 4GB까지 입력할 수 있다.

실수형

실수형은 소수점이 있는 숫자를 저장할 때 사용한다.

데이터 형식	바이트 수	설명
FLOAT	4	소수점 아래 7자리까지 표현
DOUBLE	8	소수점 아래 15자리까지 표현

날짜형

날짜형은 날짜 및 시간을 저장할 때 사용한다.

데이터 형식	바이트 수	설명
DATE	3	날짜만 저장. YYYY-MM-DD 형식으로 사용
TIME	3	시간만 저장. HH:MM:SS 형식으로 사용
DATETIME	8	날짜 및 시간을 저장. YYYY-MM-DD HH:MM:SS 형식으로 사용

DATE는 날짜만, TIME은 시간만 저장한다.

날짜와 시간을 둘 다 저장하고 싶으면 DATETIME을 사용한다.

변수의 사용

SQL도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다.

변수의 선언과 값의 대입은 다음 형식을 따른다.

SET @변수이름 = 변수의 값 ; -- 변수의 선언 및 값 대입
SELECT @변수이름 ;        -- 변수의 값 출력

변수는 MySQL 워크벤치를 재시작할 때까지는 유지되지만, 종료하면 없어진다. 그러므로 임시로 사용한다고 생각하면 된다.

USE market_db;
SET @myVar1 = 5 ;
SET @myVar2 = 4.25 ;

SELECT @myVar1 ;
SELECT @myVar1 + @myVar2 ;

SET @txt = '가수 이름==> ' ;
SET @height = 166;
SELECT @txt , mem_name FROM member WHERE height > @height ;

변수를 선언하고 정수 또는 실수를 대입했다.
변수의 내용을 출력한다.
변수끼리 연산한 후에 출력한다.
변수를 선언하고 문자열 또는 정수를 대입했다.
테이블을 조회하면서 변수를 활용했다. 조건문은 height > 166와 동일하다.

SELECT 문에서 행의 개수를 제한하는 LIMIT에도 변수를 사용해보겠다. 이 SQL은 SELECT문에서 오류가 발생한다.

LIMIT에는 변수를 사용할 수 없기 때문에 문법상 오류이다.

SET @count = 3;
SELECT mem_name, height FROM member ORDER BY height LIMIT @count;

Untitled

이를 해결하는 것이 PREPARE와 EXECUTE이다.

PREPARE는 실행하지 않고 SQL 문만 준비해 놓고 EXECUTE에서 실행하는 방식이다.

SET @count = 3;
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?';
EXECUTE mySQL USING @count;

Untitled

@Count 변수에 3을 대입했다.
PREPARE는 ‘SELECT ~~ LIMIT ?’ 문을 실행하지 않고 mySQL이라는 이름으로 준비만 해놓는다. 주의해서 볼 것은 LIMIT 다음에 오는 물음표 (?)이다. ?는 ‘현재는 모르지만 나중에 채워짐’ 정도로 이해하면 된다.
EXECUTE로 mySQL에 저장된 SELECT 문을 실행할 때, USING으로 물음표(?)에 @count 변수의 값을 대입하는 것이다. 결론적으로 다음과 같은 SQL이 실행되는 것이다.

SELECT mem_name, height FROM member ORDER BY height LIMIT 3;

데이터 형 변환

문자형을 정수형으로 바꾸거나, 반대로 정수형을 문자형으로 바꾸는 것을 데이터의 형 변환type conversion이라고 부른다.

형 변환에는 직접 함수를 사용해서 변환하는 명시적인 변환explicit conversion과 별도의 지시 없이 자연스럽게 변환되는 암시적인 변환implicit conversion이 있다.

함수를 이용한 명시적인 변환

데이터 형식을 변환하는 함수는 CAST(), CONVERT()이다. CAST(), CONVERT()는 형식만 다를 뿐 동일한 기능을 한다.

CAST ( 값 AS 데이터_형식 [ (길이) ]
CONVERT ( 값, 데이터_형식 [ (길이) ]

다음은 market_db의 구매 테이블(buy)에서 평균 가격을 구하는 SQL이다. 결과가 실수로 나왔다.

SELECT AVG(price) AS '평균 가격' FROM buy;

Untitled

가격은 실수보다 정수로 표현하는 것이 보기에 좋다. 다음과 같이 CAST()나 CONVERT() 함수를 사용해서 정수로 표현할 수 있다.

CAST()나 CONVERT() 함수 안에 올 수 있는 데이터 형식은 CHAR, SIGNED, UNSIGNED, DATE, TIME, DATETIME 등이다.

SIGNED는 부호가 있는 정수, UNSIGNED는 부호가 없는 정수를 의미한다.

SELECT CAST(AVG(price) AS SIGNED) '평균 가격' FROM buy;
-- 또는
SELECT CONVERT(AVG(price) , SIGNED)  '평균 가격'  FROM buy;

이번에는 날짜를 확인해보자. 다양한 구분자를 날짜형으로 변경할 수도 있다.

SELECT CAST('2022$12$12' AS DATE);
SELECT CAST('2022/12/12' AS DATE);
SELECT CAST('2022%12%12' AS DATE);
SELECT CAST('2022@12@12' AS DATE);

SQL의 결과를 원하는 형태로 표현할 때도 사용할 수 있다.

가격(price)과 수량(amount)을 곱한 실제 구매액을 표시하는 SQL을 다음과 같이 작성할 수 있다.

SELECT num, CONCAT(CAST(price AS CHAR), 'X', CAST(amount AS CHAR) ,'=' ) '가격X수량', price*amount '구매액' FROM buy;

Untitled

가격(price)과 수량(amount)은 정수지만, CAST() 함수를 통해 문자로 바꿨다. CONCAT() 함수는 문자를 이어주는 역할을 하며, 여기서는 ‘30X2=’과 같은 형태의 문자로 만들어서 출력했다.

암시적인 변환

암시적인 변환은 CAST()나 CONVERT() 함수를 사용하지 않고도 자연스럽게 형이 변환되는 것을 말한다.

다음 예를 보자. 문자 ‘100’과 ‘200’을 더했다. 문자는 더할 수 없으므로 자동으로 숫자 100과 200으로 변환해서 덧셈을 수행했다.

SELECT '100' + '200';

Untitled

만약에 문자 ‘100’과 ‘200’을 연결한 ‘100200’으로 만들려면 앞에서 배운 CONCAT() 함수를 사용해야 한다.

SELECT CONCAT('100', '200');

Untitled

숫자와 문자를 연산할 때, CONCAT()을 사용하면 숫자가 문자로 변하고, 더하기만 사용하면 문자가 숫자로 변한 후에 연산된다.

04-2 두 테이블을 묶는 조인

조인join이란 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 말한다.

내부 조인

두 테이블을 연결할 때 가장 많이 사용되는 것이 내부 조인다. 그냥 조인이라 부르면 내부 조인을 의미하는 것이다.

일대다 관계의 이해

두 테이블의 조인을 위해서는 테이블이 일대다(one to many) 관계로 연결되어야 한다.

데이터베이스의 테이블은 하나로 구성되는 것보다는 여러 정보를 주제에 따라 분리해서 저장하는 것이 효율적이다. 이 분리된 테이블은 서로 관계relation를 맺고 있다.

일대다 관계란 한쪽 테이블에는 하나의 값만 존재해야 하지만, 연결된 다른 테이블에는 여러개의 값이 존재할 수 있는 관계를 말한다.

예를 들어, 회원 테이블에서 블랙핑크의 아이디는 ‘BLK’로 1명(1, one) 밖에 없다. 그래서 회원 테이블의 아이디를 기본 키 Primary Key, PK로 지정했다.

구매 테이블의 아이디에서는 3개의 BLK를 찾을 수 있다. 즉, 회원은 1명이지만 이 회원은 구매를 여러 번(다, many) 할 수 있는 것이다.

그래서 구매 테이블의 아이디는 기본 키가 아닌 외래 키 Foreign Key, FK로 설정했다.

일대다 관계는 주로 기본 키(PK)와 외래 키(FK) 관계로 맺어져 있다. 그래서 일대다 관계를 ‘PK-FK 관계’라고 부르기도 한다.

두 테이블의 조인을 위해서는 기본 키 - 외래 키 관계로 맺어져야 하고, 이를 ‘일대다 관계’라고 부른다.

내부 조인의 기본

일반적으로 조인이라고 부르는 것은 내부 조인 inner join을 말하는 것으로, 조인 중에서 가장 많이 사용된다.

내부 조인의 형식은 다음과 같다.

SELECT <열 목록>
FROM <첫 번째 테이블>
	INNER JOIN <두 번째 테이블>
	ON <조인될 조건>
[WHERE 검색 조건>

INNER JOIN을 그냥 JOIN이라고만 써도 INNER JOIN으로 인식한다.

구매 테이블에는 물건을 구매한 회원의 아이디와 물건 등의 정보만 있다. 이 물건을 배송하기 위해서는 구매한 회원의 주소 및 연락처를 알아야 한다.

이 회원의 주소, 연락처를 알기 위해 정보가 있는 회원 테이블과 결합하는 것이 내부 조인이다.

구매 테이블에서 GRL이라는 아이디를 가진 사람이 구매한 물건을 발송하기 위해 다음과 같이 조인해서 이름/주소/연락처 등을 검색할 수 있다.

SELECT * 
   FROM buy
     INNER JOIN member
     ON buy.mem_id = member.mem_id
   WHERE buy.mem_id = 'GRL';

Untitled

만약, WHERE buy.mem_id = ‘GRL’을 생략하면 구매 테이블의 모든 행이 회원 테이블과 결합한다.

SELECT * 
   FROM buy
     INNER JOIN member
     ON buy.mem_id = member.mem_id;

Untitled

내부 조인의 간결한 표현

SELECT mem_id, mem_name, prod_name, addr, CONCAT(phone1, phone2) '연락처'
FROM buy
INNER JOIN member
ON buy.mem_id = member.mem_id;

Untitled

오류가 발생했다. 열 이름인 mem_id가 불확실하다는 오류 메시지이다.

즉, 회원 아이디(mem_id)는 회원 테이블, 구매 테이블에 모두 들어 있어서 어느 테이블의 mem_id인지 헷갈린다는 의미이다.

이럴 때는 어느 테이블의 mem_id를 추출할지 정확하게 작성해야 한다.

SELECT buy.mem_id, mem_name, prod_name, addr, CONCAT(phone1, phone2) '연락처'
FROM buy
INNER JOIN member
ON buy.mem_id = member.mem_id;

Untitled

SQL을 좀 더 명확히 하기 위해서 SELECT 다음의 열 이름(컬럼 이름)에도 모두 테이블이름.열이름 형식으로 작성해보자. 결과는 동일하다.

SELECT buy.mem_id, member.mem_name, buy.prod_name, member.addr, CONCAT(member.phone1, member.phone2) '연락처'
	FROM buy
		INNER JOIN member
		ON buy.mem_id = member.mem_id;

각 열이 어느 테이블에 속한 것인지 명확해졌지만 코드가 너무 길어져서 오히려 복잡해 보인다.

이를 간결하게 표현하기 위해서는 다음과 같이 FROM 절에 나오는 테이블의 이름 뒤에 별칭alias을 줄 수있다.

SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(M.phone1, M.phone2) '연락처'
	FROM buy B
		INNER JOIN member M
		ON B.mem_id = M.mem_id;

내부 조인의 활용

전체 회원의 아이디/이름/구매한 제품/주소를 출력해보겠다.

결과는 보기 쉽게 회원 아이디 순으로 정렬해보겠다.

SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
   FROM buy B
     INNER JOIN member M
     ON B.mem_id = M.mem_id
   ORDER BY M.mem_id;

구매 테이블의 목록이 12건이었으므로 이상 없이 잘 나왔다.

결과는 아무런 이상이 없지만, 조금 전에 말했던 ‘전체 회원’과는 차이가 좀 있다.

결과는 ‘전체 회원’이 아닌 ‘구매한 기록이 있는 회원들’의 목록이다.

Untitled

결과에 한 번도 구매하지 않은 회원의 정보가 없다.

우리가 원하는 결과는 구매한 회원의 구매기록과 더불어 구매하지 않은 회원의 이름/주소가 같이 검색되도록 하는 것이다.

지금가지 사용한 내부 조인은 두 테이블에 모두 있는 내용만 조인되는 방식이다.

만약, 양쪽 중에 한곳이라도 내용이 있을 때 조인하려면 외부 조인을 사용해야 한다.

중복된 결과 1개만 출력하기

내부 조인이 양쪽에 모두 있는 내용만 나오기 때문에 유용한 경우도 있다.

예를 들어, 인터넷 마켓 운영자라면 다음과 같이 생각할 수 있다.

“우리 사이트에서 한 번이라도 구매한 기록이 있는 회원들에게 감사의 안내문을 발송하자.”

이런 경우라면 앞의 SQL 처럼 내부 조인을 사용해서 추출한 회원에게만 안내문을 발송하면 된다.

그리고 어차피 중복된 이름은 필요 없으므로 DISINCT 문을 활용해서 회원의 주소를 조회할 수 있ㄷ다.

SELECT DISTINCT M.mem_id, M.mem_name, M.addr
   FROM buy B
     INNER JOIN member M
     ON B.mem_id = M.mem_id
   ORDER BY M.mem_id;

Untitled

외부 조인

내부 조인은 두 테이블에 모두 데이터가 있어야만 결과가 나온다.

이와 달리 외부 조인은 한쪽에만 데이터가 있어도 결과가 나온다.

외부 조인의 기본

외부 조인outer join은 두 테이블을 조인할 때 필요한 내용이 한쪽 테이블에만 있어도 결과를 추출할 수 있다.

외부 조인의 형식은 다음과 같다.

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
	<LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
	ON <조인될 조건>
[WHERE 검색 조건];

SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
   FROM member M -- 왼쪽에 있는 회원 테이블을 기준으로 외부 조인한다.
     LEFT OUTER JOIN buy B
     ON M.mem_id = B.mem_id
   ORDER BY M.mem_id;

LEFT OUTER JOIN을 줄여서 LEFT JOIN이라고만 써도 된다.

Untitled

LEFT OUTER JOIN 문의 의미를 ‘왼쪽 테이블(member)의 내용은 모두 출력되어야 한다.’ 정도로 해석하면 기억하기 쉽다.

RIGHT OUTER JOIN 으로 동일한 결과를 출력하려면 다음과 같이 단순히 왼쪽과 오른쪽 테이블의 위치만 바꿔주면 된다.

SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
   FROM buy B  -- 오른쪽에 있는 회원 테이블을 기준으로 외부 조인한다.
     RIGHT OUTER JOIN member M
     ON M.mem_id = B.mem_id
   ORDER BY M.mem_id;

외부 조인의 활용

내부 조인으로 구매한 기록이 있는 회원들의 목록만 추출해서 감사문을 보냈다.

이번에는 반대로 회원으로 가입만 하고, 한 번도 구매한 적이 없는 회원의 목록을 추출해보자.

SELECT DISTINCT M.mem_id, B.prod_name, M.mem_name, M.addr
   FROM member M
     LEFT OUTER JOIN buy B
     ON M.mem_id = B.mem_id
   WHERE B.prod_name IS NULL
   ORDER BY M.mem_id;

한 번도 구매하지 않았으므로 조인된 결과의 물건 이름(prod_name)이 당연히 비어있을 것이다.

IS NULL 구문은 널(NULL) 값인지 비교한다. 한 번도 구매하지 않은 6명의 회원이 나왔다.

Untitled

FULL OUTER JOIN 은 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것이라고 생각하면 된다.

왼쪽이든 오른쪽이든 한쪽에 들어 있는 내용이면 출력한다.

기타 조인

내부 조인이나 외부 조인처럼 자주 사용되지는 않지만 가끔 유용하게 사용되는 조인으로 상호 조인과 자체 조인도 있다.

상호 조인

상호 조인cross join은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능을 말한다.

그래서 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 개수가 된다.

회원 테이블의 첫 행은 구매 테이블의 모든 행과 조인된다. 나머지 행도 마찬가지이다.

즉, 회원 테이블의 첫 행이 구매 테이블의 12개 행과 결합된다.

또 회원 테이블의 두 번째 행이 구매 테이블의 12개 행과 결합된다. 이런 식으로 회식 테이블의 모든 행이 구매 테이블의 모든 행과 결합된다.

최종적으로 회원 테이블의 10개 행과 구매 테이블의 12개 행을 곱해서 총 120개의 결과가 생성되는 것이다.

Untitled

상호 조인을 카티션 곱(cartesian product)이라고도 부른다.

회원 테이블과 구매 테이블의 상호 조인은 다음과 같다.

SELECT * FROM buy CROSS JOIN member;

Untitled

상호 조인은 다음과 같은 특징이 있다.

ON 구문을 사용할 수 없다.
결과의 내용은 의미가 없다. 랜덤으로 조인하기 때문이다.
- 예) BLK를 에이핑크, 잇지, 소내시대 등과도 조인한다.
상호 조인의 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때이다.

예를 들어, 샘플 데이터베이스인 sakila의 inventory 테이블에는 4,581건, world의 city 테이블에는 4,079건이 있다.

두 테이블을 상호 조인시키면 4,581 x 4,079 = 18,685,899 건의 데이터를 생성할 수 있다.

결과는 18,685,899이다.

SELECT COUNT(*) "데이터 개수" FROM sakila.inventory CROSS JOIN world.city;

진짜로 대용량의 테이블을 만들고 싶으면 CREATE TABLE ~ SELECT 문을 사용한다.

자체 조인

내부 조인, 외부 조인, 상호 조인은 모두 2개의 테이블을 조인했다.

자체 조인self join 은 자신이 자신과 조인한다는 의미이다.

그래서 자체 조인은 1개의 테이블을 사용한다. 또, 별도의 문법이 있는 것은 아니고 1개로 조인하면 자체 조인이 되는 것이다.

정리

일대다 관계란 한쪽 테이블에는 하나의 값만, 다른 쪽 테이블에는 여러 개의 값이 존재할 수 있는 관계를 말한다.
조인은 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 말한다.
내부 조인은 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다.
외부 조인은 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나온다.
상호 조인은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능이다.
자체 조인은 자신이 자신과 조인된다는 의미로, 1개의 테이블을 사용한다.

관련 중요 용어

용어	설명
관계	두 테이블이 서로 연관되는 것
기본 키 - 외래 키 관계	두 테이블이 일대다 관계로 연결되기 위한 조건
별칭(alias)	조인에서 테이블의 이름을 짧게 표현하는 이름
DISTINCT 문	중복된 열의 값을 1개만 표현하는 구문
LEFT OUTER JOIN	왼쪽 테이블의 모든 값이 출력되는 조인
RIGHT OUTER JOIN	오른쪽 테이블의 모든 값이 출력되는 조인
FULL OUTER JOIN	왼쪽 또는 오른쪽 테이블의 모든 값이 출력되는 조인
CRAETE TABLE ~ SELECT	SELECT의 결과가 테이블로 생성되는 구문

04-3 SQL 프로그래밍

스토어드 프로시저는 MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체이다.

SQL 프로그래밍은 기본적으로 스토어드 프로시저 안에 만들어야 한다.

스토어드 프로시저는 다음과 같은 구조를 갖는다.

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
	이 부분에 SQL 프로그래밍 코딩
END $$ -- 스토어드 프로시저 종료
DELIMETER;  -- 종료 문자를 다시 세미콜론(;)으로 변경
CALL 스토어드_프로시저_이름();  -- 스토어드 프로시저 실행

스토어드 프로시저는 DELIMETER $$ ~ END $$ 안에 작성하고 CALL로 호출한다.

IF 문

IF문은 조건문으로 가장 많이 사용되는 프로그래밍 문법 중 하나이다.

IF문의 기본 형식

IF 문은 조건식이 참이라면 ‘SQL 문장들’을 실행하고, 그렇지 않으면 그냥 넘어간다.

기본 IF문의 형식을 살펴보자.

IF <조건식> THEN
	SQL문장들
END IF;

‘SQL 문장들’이 한 문장이라면 그 문장만 써도 되지만, 두 문장 이상이 처리되어야 할 때는 BEGIN~END로 묶어줘야 한다.

DROP PROCEDURE IF EXISTS ifProc1; -- 기존에 만든적이 있다면 삭제
DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
   IF 100 = 100 THEN  
      SELECT '100은 100과 같습니다.';
   END IF;
END $$
DELIMITER ;
CALL ifProc1();

만약 기존에 ifProc1()을 만든 적이 있다면 삭제한다.
세미콜론(;)으로는 SQL의 끝인지, 스토어드 프로시저의 끝인지 구별할 수 없어서 $$을 사요ㅕㅇ한다.
스토어드 프로시저의 이름을 ifProc1()로 지정
조건식으로 100과 100이 같은지 비교했다. 당연히 참(True) 일 테니 다음 행이 실행된다.
CALL로 호출하면 ifProc1()이 실행된다.

다른 프로그래밍 언어에서는 같다는 의미로 == 을 사용하지만, SQL은 = 을 사용한다. 그리고 SELECT 뒤에 문자가 나오면 그냥 화면에 출력해준다.
다른 언어의 print()와 비슷한 기능을 한다.

IF ~ ELSE 문

IF ~ ELSE 문은 조건에 따라 다른 부분을 수행한다.

조건식이 참이라면 ‘SQL문장들1’을 실행하고, 그렇지 않으면 ‘SQL문장들2’를 실행한다.

DROP PROCEDURE IF EXISTS ifProc2; 
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
   DECLARE myNum INT;  -- myNum 변수선언
   SET myNum = 200;  -- 변수에 값 대입
   IF myNum = 100 THEN  
      SELECT '100입니다.';
   ELSE
      SELECT '100이 아닙니다.';
   END IF;
END $$
DELIMITER ;
CALL ifProc2();

DECLARE 예약어를 사용해서 myNum 변수를 선언했다. 제일 뒤에는 변수의 데이터 형식을 INT로 지정했다.
SET 예약어로 myNum 변수에 200을 대입했다.
myNum이 100인지 아닌지를 구분한다.

IF 문의 활용

기존 테이블과 함께 IF 문을 활용해보자.

아이디가 APN인 회원의 데뷔 일자가 5년이 넘었는지 확인해보고 5년이 넘었으면 축하 메시지를 출력해보자.

DROP PROCEDURE IF EXISTS ifProc3; 
DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
    DECLARE debutDate DATE; -- 데뷰일
    DECLARE curDate DATE; -- 오늘
    DECLARE days INT; -- 활동한 일수

    SELECT debut_date INTO debutDate -- debut_date 결과를 hireDATE에 대입
       FROM market_db.member
       WHERE mem_id = 'APN';

    SET curDATE = CURRENT_DATE(); -- 현재 날짜
    SET days =  DATEDIFF(curDATE, debutDate); -- 날짜의 차이, 일 단위

    IF (days/365) >= 5 THEN -- 5년이 지났다면
          SELECT CONCAT('데뷔한지 ', days, '일이나 지났습니다. 핑순이들 축하합니다!');
    ELSE
          SELECT '데뷔한지 ' + days + '일밖에 안되었네요. 핑순이들 화이팅~' ;
    END IF;
END $$
DELIMITER ;
CALL ifProc3();

데뷔 일자는 debutDate에, 오늘 날짜는 curDate에, 데뷔 일자부터 오늘까지 몇일이 지났는지는 days에 저장할 예정이다.
APN의 데뷔 일자(debut_date)를 추출하는 SELECT 문이다.
그런데 그냥 SELECT와 달리 INTO 변수가 붙어 있다. 이럴 경우 결과를 변수에 저장한다. 결국 에이핑크의 데뷔 일자가 debutDate에 저장된다.
CURRENT_DATE() 함수로 현재 날짜를 curDate에 저장했다.
DATEDIFF() 함수로 데뷔 일자부터 현재 날짜까지 일수를 days에 저장했다.
일자가 저장된 days를 365로 나눠서 연으로 변환한 후 5년이 넘는 것과 그렇지 않은 경우에 메시지를 다르게 출력했다.

❕날짜 관련 함수

CURRENT_DATE(): 오늘 날짜를 알려준다.
CURRENT_TIMESTAMP(): 오늘 날짜 및 시간을 함께 알려준다.
DATEDIFF(날짜1, 날짜2): 날짜2부터 날짜1까지 일수로 몇일인지 알려준다.

SELECT CURRENT_DATE(), DATEDIFF('2021-12-31', '2000-1-1');

CASE 문

여러 가지 조건 중에서 선택해야 하는 경우도 있다. 이럴 때 CASE 문을 사용해서 조건을 설정할 수 있다.

CASE 문의 기본 형식

IF 문은 참 아니면 거짓 두 가지만 있기 때문에 2중 분기라는 용어를 사용한다.

CASE 문은 2가지 이상의 여러 가지 경우일 때 처리가 가능하므로 ‘다중 분기’라고 부른다.

CASE 문의 형식은 다음과 같다.

CASE
	WHEN 조건1 THEN
		SQL 문장들1
	WHEN 조건2 THEN
		SQL 문장들2
	WHEN 조건3 THEN
		SQL 문장들3
	ELSE
		SQL 문장들4
END CASE;

CASE와 END CASE 사이에는 여러 조건들을 넣을 수 있다. WHEN 다음에 조건이 나오는데, 조건이 여러 개라면 WHEN을 여러 번 반복한다.

그리고 모든 조건에 해당하지 않으면 마지막 ELSE 부분을 수행한다.

DROP PROCEDURE IF EXISTS caseProc; 
DELIMITER $$
CREATE PROCEDURE caseProc()
BEGIN
    DECLARE point INT ;
    DECLARE credit CHAR(1);
    SET point = 88 ;
    
    CASE 
        WHEN point >= 90 THEN
            SET credit = 'A';
        WHEN point >= 80 THEN
            SET credit = 'B';
        WHEN point >= 70 THEN
            SET credit = 'C';
        WHEN point >= 60 THEN
            SET credit = 'D';
        ELSE
            SET credit = 'F';
    END CASE;
    SELECT CONCAT('취득점수==>', point), CONCAT('학점==>', credit);
END $$
DELIMITER ;
CALL caseProc();

시험 점수 point 변수에 88을 입력했고, 학점을 저장한 credit 변수를 준비했다.
point에 따라서 credit을 A부터 F까지 설정했다.
앞의 모든 조건에 해당하지 않으면 F 학점으로 처리한다.
결과를 출력한다.

CASE문의 활용

인터넷 마켓 데이터베이스의 회원들은 물건을 구매한다.

회원들의 총 구매액을 계산해서 회원의 등급을 다음과 같이 4단계로 나누려 한다.

총 구매액	회원 등급
1500 이상	최우수고객
1000 ~ 1499	우수고객
1 ~ 999	일반고객
0 이하 (구매한적 없음)	유령고객

먼저 구매 테이블(buy)에서 회원별로 총 구매액을 구해보자.

이전에 배운 GROUP BY를 이용해서 다음과 같이 만들 수 있다.

SELECT mem_id, **SUM(price * amount) "총구매액"**
	FROM buy
	**GROUP BY mem_id;**

Untitled

구매 테이블에서 회원의 아이디(mem_id) 별로 가격과 수량을 곱해서 총 구매액의 합계를 구했다.

추가로 ORDER BY를 사용해서 총 구매액이 많은 순서로 정렬해보자.

SELECT mem_id, SUM(price * amount) "총구매액"
	FROM buy
	GROUP BY mem_id
	**ORDER BY SUM(price * amount) DESC;**

Untitled

이번에는 회원의 이름도 출력해보자. 그런데 회원의 이름은 회원 테이블(member)에 있으므로 구매 테이블(buy)과 조인해야 한다.

SELECT B.mem_id, M.mem_name, SUM(price*amount) "총구매액"
   FROM buy B
         INNER JOIN member M
         ON B.mem_id = M.mem_id
   GROUP BY B.mem_id
   ORDER BY SUM(price*amount) DESC ;

Untitled

이번에는 구매하지 않은 회원의 아이디와 이름도 출력해보자. 내부 조인 대신에 외부 조인을 시키면 된다.

그리고 구매 테이블에는 구매한 적이 없어도 회원 테이블에 있는 회원은 모두 출력해야 하므로 INNER JOIN을 RIGHT OUTER JOIN으로 변경한다.

주의할 점은 구매 테이블에는 4명만 구매했으므로, 나머지 6명에 대한 아이디 등의 정보가 없다.

그래서 SELECT에서 회원 테이블의 아이디인 M.mem_id를 조회하고 GROUP BY도 M.mem_id로 변경했다.

SELECT M.mem_id, M.mem_name, SUM(price * amount) "총구매액"
	FROM buy B
		RIGHT OUTER JOIN member M
		ON B.mem_id = M.mem_id
	GROUP BY M.mem_id
	ORDER BY SUM(price * amount) DESC;

Untitled

이제는 계획한 대로 총 구매액에 따라 회원 등급을 구분해보겠다.

CASE 문을 사용해면 다음과 같다.

CASE
	WHEN (총구매액 >= 1500) THEN '최우수고객'
	WHEN (총구매액 >= 1000) THEN '우수고객'
	WHEN (총구매액 >= 1) THEN '일반고객'
	ELSE '유령고객'
END

SELECT M.mem_id, M.mem_name, SUM(price * amount) "총구매액",
		**CASE
			WHEN (SUM(price * amount) >= 1500) THEN '최우수고객'
			WHEN (SUM(price * amount) >= 1000) THEN '우수고객'
			WHEN (SUM(price * amount) >= 1) THEN '일반고객'
			ELSE '유령고객'
		END "회원등급"**
	FROM buy B
		RIGHT OUTER JOIN member M
		ON B.mem_id = M.mem_id
	GROUP BY M.mem_id
	ORDER BY SUM(price * amount) DESC;

Untitled

WHILE 문

WHILE 문의 기본 형식

WHILE 문은 조건식이 참인 동안에 ‘SQL 문장들’을 계속 반복한다.

WHILE 문의 형식은 다음과 같다.

WHILE <조건식> DO
	SQL 문장들
END WHILE;

1에서 100까지의 값을 모두 더하는 기능을 WHILE문으로 구현해보자.

DROP PROCEDURE IF EXISTS whileProc; 
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
    DECLARE i INT; -- 1에서 100까지 증가할 변수
    DECLARE hap INT; -- 더한 값을 누적할 변수
    SET i = 1;
    SET hap = 0;

    WHILE (i <= 100) DO
        SET hap = hap + i;  -- hap의 원래의 값에 i를 더해서 다시 hap에 넣으라는 의미
        SET i = i + 1;      -- i의 원래의 값에 1을 더해서 다시 i에 넣으라는 의미
    END WHILE;

    SELECT '1부터 100까지의 합 ==>', hap;   
END $$
DELIMITER ;
CALL whileProc();

1, 2, 3 …으로 증가할 변수 i와 합계를 누적할 변수 hap을 준비했다.
i가 100 이하인 동안에 계속 반복한다.
i를 계속 hap에 누적시키고, i는 1씩 증가시켰다.

WHILE 문의 응용

ITERATE [레이블] : 지정한 레이블로 가서 계속 진행한다.
LEAVE [레이블] : 지정한 레이블을 빠져나간다. 즉 WHILE 문이 종료된다.

ITERATE 문은 프로그래밍 언어의 CONTINUE와, LEAVE 문은 BREAK 문과 비슷한 역할을 한다.

DROP PROCEDURE IF EXISTS whileProc2; 
DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
    DECLARE i INT; -- 1에서 100까지 증가할 변수
    DECLARE hap INT; -- 더한 값을 누적할 변수
    SET i = 1;
    SET hap = 0;

    myWhile: 
    WHILE (i <= 100) DO  -- While문에 label을 지정
       IF (i%4 = 0) THEN
         SET i = i + 1;     
         ITERATE myWhile; -- 지정한 label문으로 가서 계속 진행
       END IF;
       SET hap = hap + i; 
       IF (hap > 1000) THEN 
         LEAVE myWhile; -- 지정한 label문을 떠남. 즉, While 종료.
       END IF;
       SET i = i + 1;
    END WHILE;

    SELECT '1부터 100까지의 합(4의 배수 제외), 1000 넘으면 종료 ==>', hap; 
END $$
DELIMITER ;
CALL whileProc2();

WHILE 문을 myWhile이라는 레이블로 지정했다.
i가 4의 배수라면 i를 1 증가시키고 ITERATE를 만나서 myWhile 레이블로 올라간다. 즉, WHILE 문을 계속 진행한다.
i가 4의 배수가 아니면 hap에 누적시킨다.
hap이 1,000을 초과하면 LEAVE를 만나서 myWhile 레이블을 빠져 나간다.

동적 SQL

SQL 문은 내용이 고정되어 있는 경우가 대부분이다.

하지만 상황에 따라 내용 변경이 필요할 때 동적 SQL을 사용하면 변경되는 내용을 실시간으로 적용시켜 사용할 수 있다.

PREPARE와 EXECUTE

PREPARE는 SQL 문을 실행하지는 않고 미리 준비만 해놓고, EXECUTE는 준비한 SQL 문을 실행한다.

그리고 실행한 후에는 DEALLOCATE PERPARE로 문장을 해제해주는 것이 바람직하다.

use market_db;
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;

PREPARE 문에서 SELECT * FROM member WHERE mem_id = ‘BLK’ 를 바로 실행하지 않고 myQuery에 입력만 시켜놓는다.
실행이 필요한 시점에서 EXECUTE myQuery 문으로 실행한다.

이렇게 미리 SQL을 준비한 후에 나중에 실행하는 것을 동적 SQL이라고 부른다.

동적 SQL의 활용

PREPARE 문에서는 ?로 향후에 입력될 값을 비워 놓고, EXECUTE에서 USING ?에 값을 전달할 수 있다.

그러면 실시간으로 필요한 값들을 전달해서 동적으로 SQL이 실핸된다.

보안이 중요한 출입문에서는 출입한 내역을 테이블에 기록해 놓는다.

이때 출입증을 태그하는 순간의 날짜와 시간이 INSERT 문으로 만들어져서 입력되도록 해야 한다.

DROP TABLE IF EXISTS gate_table;
CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);

SET @curDate = CURRENT_TIMESTAMP(); -- 현재 날짜와 시간

PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL, ?)';
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;

SELECT * FROM gate_table;

Untitled

일반 SQL에서 변수는 @변수명으로 지정하는데 별도의 선언은 없어도 된다. 스토어드 프로시저에서 변수는 DECLARE로 선언한 후에 사용해야 한다.

출입용 테이블을 간단히 만들었다. 아이디는 자동으로 증가되도록하고, 출입하는 시간을 DATETIME형으로 준비했다.

현재 날짜와 시간은 @curDate 변수에 넣었다.

?를 사용해서 entry_time에 입력할 값을 비워 놓는다.

USING 문으로 앞에서 준비한 @curDate 변수에 넣은 후에 실행된다. 결국 이 SQL을 실행한 시점의 날짜와 시간이 입력된다.

출처 - https://hongong.hanbit.co.kr/sql/

bong'59

Backend Developer

이전 포스트

SQL 고급 문법

DATABASE

SQL 고급 문법

04-1 MySQL의 데이터 형식

데이터 형식

정수형

문자형

대량의 데이터 형식

실수형

날짜형

변수의 사용

데이터 형 변환

함수를 이용한 명시적인 변환

암시적인 변환

04-2 두 테이블을 묶는 조인

내부 조인

일대다 관계의 이해

내부 조인의 기본

내부 조인의 간결한 표현

내부 조인의 활용

중복된 결과 1개만 출력하기

외부 조인

외부 조인의 기본

외부 조인의 활용

기타 조인

상호 조인

자체 조인

정리

04-3 SQL 프로그래밍

IF 문

IF문의 기본 형식

IF ~ ELSE 문

IF 문의 활용

CASE 문

CASE 문의 기본 형식

CASE문의 활용

WHILE 문

WHILE 문의 기본 형식

WHILE 문의 응용

동적 SQL

PREPARE와 EXECUTE

동적 SQL의 활용

인덱스

0개의 댓글