Tarel_base

가장 중요한 부분중 하나인 조인이다.

조인이란 두 테이블의 정보를 엮어서 추출하는 것을 말한다.

내부조인

보통 조인이라고 하면 내부조인을 말한다.

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

일대다란 무엇인가?

쉽게말해 Tarel이라는 유저는 콜라도 살 수 있고 사이다도 살 수 있고 코코아도 살 수 있다.

즉, Tarel이라는 user 하나가, 여러개의 상품 데이터에 접근한다는 것이다.

내부조인은 다음과 같이 한다.

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

참고로 INNER JOIN 대신 그냥 JOIN 이라고 써도 된다.

조인하기 앞서, 우선 user테이블과 buy 테이블을 다시한번 확인해보자

위 두 테이블을 조인해보자

WHERE은 조건을 추가해주는 용도로 사용된다.

즉, buy 테이블에서, identifier가 tarel인 것만 가져온 것이다.

WHERE을 지우면 아래처럼 나온다.

지금보니까 쓸데없이 데이터가 너무 많다.

테이블을 좀 더 간결하게 해보자

이렇게 내부조인을 통해서, 2개의 테이블에서 원하는 데이터를 가져올 수 있다.

테이블 이름에 별칭을 붙여서, SQL을 더 간결하게 사용할 수 있다.

별칭은 FROM과 JOIN에 들어간 데이터 베이스에 적는다.

즉, 2번 줄과 3번줄에서 별칭을 지정해주고, 그걸 1번줄과 4번줄에서 쓰고 있는 것이다.

많은 프로그램 언어에 자료형이 있는 것 처럼 SQL에도 자료형이 있다.

정수형

만약 범위를 벗어나는 값을 입력할 경우 Out of range 오류가 발생한다.

오버플로우가 일어나는 다른 언어들과는 다른 부분

이 형태로 테이블을 만들 때는 아래 예시처럼 만든다.

CREATE TABLE tarelExample (
	int_1 TINYINT,
	int_2 SMALLINT,
	int_3 INT,
	int_4 BIGINT
);

만들어진 모습, 사진 속 SQL이 약간 다른데, db를 선택해주는 부분이 추가되었다.

문자형

문자형은 두 가지가 있다.

괄호안의 원하는 숫자는 문자의 최대 길이라고 보면 된다.

즉 CHAR(10)은 최대 10글자라는 뜻이다.

VARCHAR는 가변길이 문자열로 VARCHAR(10)에 3글자를 저장하면 3자리만 사용하고 남은 7자리는 차지하지 않는다.

반면 CHAR(10)에 3글자를 저장하면 7자리의 공간낭비가 발생한다.

이렇게 보면 VARCHAR이 더 좋아보이지만 공간 효율은 VARCHAR가 더 좋으나 시간 효율은 CHAR이 더 좋다.

긴 문자열

만약 블로그에 장문의 소설을 작성한다면 그 많은 글자를 어떻게 저장해야 할까?

이 때 사용하는 것이 TEXT, BLOB형식으로 자주 사용하는건 총 4가지가 있다.

이 외에도 TINYTEXT, MEDIUMTEXT, TINYBLOB, MEDIUMBLOB도 있지만 잘 안쓴다.

BLOB는 글자가 아닌 이미지나 동영상 등의 데이터로 이러한 것을 이진 데이터라고 한다.

즉, 사진이나 영상등을 저장하고 싶다면 BLOB형식으로 저장해야 한다.

실수형

소숫점으로 총 2가지가 있다.

특별한 경우가 아닌 이상 FLOAT를 쓰면 된다.

날짜형

날짜와 시간으로 총 3가지가 있다.

변수

일반적인 프로그래밍 언어처럼 변수를 선언하고 사용하기 위한 용도

아래와 같이 사용한다.

SET @newVar1 = 5;
SET @newVar2 = 10;
SET @Name = "Tarel 베이스";
SELECT @newVar1 + newVar2;
SELECT @Name;

형변환

문자를 정수로 바꾸거나 반대로 정수를 문자로 바꾸는 것으로 직접 함수를 사용하는 명시적 변환과 그런거 없이 자련스럽게 바뀌는 암시적인 변환이 있다.

명시적인 변환

함수는 두 종류가 있고 아래와 같이 사용한다.

CAST(값 AS 데이터 형식 [길이])
CONVERT(값 또는 데이터 형식 [길이])

직접 써보자

암시적인 변환

CAST()나 CONVERT() 없이 자연스럽게 형변환이 일어나는 것이다.

SELECT '100' + '200';

문자열 100과 문자열 200을 더했다.

이때, 문자열은 자연스럽게 숫자형으로 변환되며 300을 출력한다.

그렇다면 100과 200을 이어붙이는, 100200 을 만들고 싶다면 어떨까?

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

CONCAT은 파라미터 내의 요소를 이어붙여주는 역할을 한다.

지금까지 살펴본 SELECT는 데이터를 조회하는데 사용했다.

이번엔 데이터를 만들고 수정하고 지우는 것에 대해서 알아보자

INSERT

테이블에 데이터를 입력하는 기본적인 SQL 문이다.

형태는 다음과 같다.

INSERT INTO 테이블 [(열1, 열2......)] VALUES (값1, 값2......)

이걸 이용해서 새로운 데이터를 넣어보자

여기에 id는 6, 이름은 제로환타, 가격은 2500인 데이터를 넣어보자

2번 줄에 SELECT문이 보이는데, 이건 조회를 위해서 넣은 것이다.

즉, 새로운 데이터를 추가하고, 그 결과를 조회하는 것이므로 무시해도 된다.

아무튼 제로환타가 추가된 것을 볼 수 있다.

그런데, product_id와 같은 id 값에는 AUTO_INCREMENT를 적용한다.

이건 자동으로 숫자가 1 증가한 값이 들어가는 옵션이다.

아이디 값은 아무것도 입력받지 않아도 알아서 숫자가 1 증가한 값이 적용되는 것을 알 수 있다.

이번엔 코코아를 추가해보자

자, 그런데 증가가 시작되는 값을 인위적으로 조정해 줄 수 있다.

예를 들어서, 다시 위 테이블에 새로운 데이터를 넣으면 id는 8이 될것이다.

그러나 100부터 시작할 수도 있다.

100부터 시작하는 카푸치노 데이터를 넣어보자

위 사진을 보면 ALTER라는 키워드가 보이는데, 이는 테이블의 속성을 바꿀 때 사용한다.

이 키워드는 나중에 더 자세히 알아보자

결과값을 보면 알겠지만 새로들어간 데이터인 카푸치노는 100부터 시작하는 것을 볼 수 있다.

UPDATE

자, 이번엔 데이터를 수정해보자

카푸치노를 럼주로, 가격은 45000으로 바꿔보자

는 세이프 모드를 쓰고 있어서 안 된단다.

이걸 풀어주자

저걸 체크 해제해주어야 한다.

그런 다음 워크벤치를 재시작 해주어야 한다.

그리고 실행한 모습

으악! 파일이 전부 덮어 쓰워졌다!

...생각해보니 where이 빠졌다.

조건을 빼먹어서 발생한 불상사를 빨리 수습하고 이번엔 where을 넣어서 제대로 실행했다.

보시다시피 카푸치노가 정상적으로 럼주로 바뀐 것을 볼 수 있다.

앞으로 주의하자, update를 할 때 where을 빼먹으면 모든 데이터가 덮어씌워지니 조심하도록 하자

다만 한 번에 전체를 바꿔야할 때가 있지 않을까?

모든 데이터에서, 가격을 100으로 나누어 보자

가격이 1/100로 줄어든 모습이다.

다시 원상복구를 해준 모습

DELETE

데이터를 삭제하는 DELETE는 다음과 같이 사용한다.

DELETE FROM 테이블 WHERE 조건;

만원보다 가격이 높은 데이터를 삭제했다.

전에 사용한 데이터베이스를 그대로 사용한다.

ORDER BY

특정 열을 기준으로 정렬한다.

역순으로 정렬한다면 끝에 DESC를 붙여주면 된다.

LIMIT

출력에 제한을 둔다.

예를 들어 3개만 출력한다고 하면 LIMIT 3 라고 적는다.

DISTINCT

중복된 결과를 제거한다.

단, 이 부분은 문법이 조금 이질적이다.

자세히 보면 SELECT 다음에 사용되었으며 user_address의 데이터만, 중복되지 않게 가져왔다.

GROUP BY

이건 조금 어렵다.

이걸 설명하기 전에, 여기에 적합한 테이블을 하나 더 만들자

GROUP BY는 단독으로 쓰기 보단 집계함수와 같이 사용되는데 그 집계함수로는 다음이 있다.

위 함수와 같이 사용하여 데이터를 원하는 부분만 가져올 수 있다.

GROUP BY는 위 함수와 같이 사용해보면서 살펴보자

GROUP BY - SUM

먼저 SUM 부터 알아보자

buy 테이블에는 어떤 사용자가 어떤 제품을 몇 개 구매했는지에 대한 정보가 들어있다.

위 사진은 사용자별로 총 몇 개의 물건을 구매했는지를 나타낸 것이다.

buy테이블을 보면 tarel 이라는 사용자는 제로콜라 5개, 콜라 3개, 사이다 2개, 사이다 2개로 총 12개를 구매한 것을 알 수 있다.

그리고 SUM 함수를 사용해서 buy_count를 정리하자, tarel은 12가 나온 것을 볼 수 있다.

SELECT 다음에 오는 값은 결국 내가 보고자 하는 데이터목록인 것을 알 수 있다.

여기서, SUM 함수를 통해서 buy_count의 값을 더한 것을 알 수 있다.

GROUP BY는 더한 것의 기준을 잡아주었다고 볼 수 있다.

GROUP BY - AVG

위의 SUM을 잘 이해했다면 여기서부턴 어렵지 않을 것이다.

AVG는 눈치 챈 사람도 있겠지만 평균 값이다.

이번엔 구매자들이 구매한 물품의 평균 값을 구해보자

SELECT에서 함수만 SUM에서 AVG로 바꾸어주고 AVG의 파라미터만 buy_price로 바꾸어 주었다.

그런데 이건 구매시, 구매했던 물건의 가격의 평균이다.

즉, 갯수와는 상관이 없는 것이다.

만약 매 쇼핑마다 쓴 금액의 평균을 구하고자 한다면 어떨까?

예를 들어, tarel은 제로콜라 5개, 콜라 3개, 사이다 2개, 사이다 2개를 구매했는데

이때, 순서대로 15000원, 3000원, 4000원, 4000원을 사용했다.

그렇다면 매 쇼핑시 평균값은 6500원인데 이런 값을 구하고자 한다면 어떨까?

보시다시피 파라미터에 식을 넣어서 해결한 것을 알 수 있다.

다른 프로그래밍 언어처럼 파라미터에 식을 넣는 것도 가능한 것을 알 수 있다.

GROUP BY - MIN, MAX

min과 max는 말 그대로 최대, 최소값이다.

각 고객이 가장 비싼 물건을 샀을 때, 그 가격을 보여준다.

역시 파라미터에 식을 넣어서 해당 고객이 가장 많이 돈을 쓴 시점을 뽑아낼 수도 있다.

여담인데 명령어 소문자로 적어도 무방하다....

어딜가나 대문자로 적혀있길래 꼭 대문자여야 했지만 굳이 그런건 아닌듯

GROUP BY - COUNT()

행의 갯수를 센다.

이때, 파라미터에 DISTINCT를 넣어서 중복을 없앨 수 있다.

이렇게 사용하면 어느 고객이 구매를 총 몇번 했는지를 알아낼 수 있다.

HAVING

Having은 where과 비슷하게 조건을 제한하지만 여기서 제한하는 조건은 집계함수에 대한 것이라고 볼 수 있다.

예를 들어, 각 사용자별 사용 금액을 가져와보자

보시다시피 사용자별 총 구매 금액이 나왔다.

여기서 만원을 초과해서 쓴 사람들만 가져와보자

이럴 때 이렇게 having을 써서 조건을 걸어줄 수 있다.

SELECT ~ FROM ~ WHERE

SELECT는 테이블에서 데이터를 추출하는 기능을 한다.

SELECT 다음에는 열의 이름이 나와야 한다.

위 테이블에서 열은 product_ id, product_name, product_price에 해당한다.

이 테이블은 열이 총 3개인 것이다.

그런데 보통 SELECT를 사용할 때는 모든 열을 사용하는 경우가 많기 때문에 *을 자주 사용한다.

*은 '모두'를 뜻한다.

즉 SELECT * 은 모든 열을 선택한다는 뜻이다.

SELECT product_id는 product_id라는 열을 선택하라는 뜻이다.

이 product_id는 product라는 테이블에 있다.

그리고 product 테이블은 tarel_db라는 DB에 있다.

즉, tarel_db에 있는 product 테이블에 있는 product_id 라는 열을 가져와라 라는 명령을 하기 위해서

SELECT product_id FROM tarel_db.product;

이와 같이 코드를 작성하고 실행하면 된다.

만약 product 테이블의 모든 열을 가져오고 싶다면

SELECT * FROM tarel_db.product;

라고 입력하면 된다.

만약 product_id와 product_name 을 가져오고 싶다면

SELECT product_id, product_name FROM tarel_db.product;

라고 입력하면 된다.

이제 SELECT의 기능을 알았으니, 그 하위개념에 대해서 살펴보자

FROM은 선술했듯이 해당 열이 있는 테이블을 말해준다.

설명은 이미 했으므로 이건 넘어가고 다음 키워드부터 하나하나 살펴보자

WHERE

WHERE은 불러올 데이터에 조건을 부여한다.

예를 들어, 맨 위에, product 테이블에서 제로콜라를 가져와 보자

SELECT * FROM tarel_db.product WHERE product_name = '제로콜라';

제로콜라 데이터만 가져온 모습

이번엔 다르게 써보자, 가격이 3000원 이하인 것만 가져오자

SELECT * FROM tarel_db.product WHERE product_price <= 3000;

조건을 두 개 만들어 보자, product_id가 2 이상이면서 가격이 4000원 이하인 것만 불러와 보자

SELECT * FROM tarel_db.product
	WHERE product_price <= 4000 AND product_id >= 2;

여기서 SQL 문을 두 줄로 작성했는데

사실 몇 줄로 작성하든 세미콜론(;)을 만나기 전까진 한 줄로 인식하기 때문에

명령문이 너무 길다 싶으면 보기 좋게 줄바꿈을 하는 것도 좋다.

여기서 AND 연산을 사용한걸 알 수 있는데 마찬가지로 OR연산도 할 수 있다.

조건에 범위를 넣어줄 때는 BETWEEN ~ AND를 사용한다.

예를 들어 id가 2~3으로 범위를 정해준다면

SELECT * FROM tarel_db.product WHERE product_id BETWEEN 2 AND 3;

요렇게 사용할 수 있다.

이번엔 IN과 LIKE에 대해서 알아보자

테이블을 user로 바꾸고 테이블 내용을 편집하겠다.

아무튼 여기서 주소가 서울인 사람들과 부산인 사람들을 가져와보자

SELECT * FROM tarel_db.user WHERE user_address IN('서울','부산')

이번엔 이름의 첫글자가 d인 데이터를 가져와보자

이때 사용하는 키워드가 바로 LIKE다.

SELECT * FROM tarel_db.user WHERE user_name LIKE 'd%';

d 뒤에 붙은 %는 무엇이든 허용한다는 뜻이다.

즉, 첫글자만 d이면 그 뒤에 뭐가와도 된다는 뜻이다.

다음에는 ORDER BY, LIMIT등 여기서 다루지 못했던 것을 다루어 보겠다.

DB 모델링

데이터베이스 모델링이란 테이블의 구조를 미리 설계하는 개념이다.

데이터베이스는 말 그대로 정보의 저장고라고 할 수 있는데

도서관에 책이 아무런 질서도 없이 정리되지 않는 것 처럼 데이터 베이스도 뭔가 규칙과 질서를 가지고 설계된다.

도서관 책이 잘 정리되어 있다면 책을 찾는 것도 쉬운 것처럼 데이터 베이스도 정보를 차곡차곡 정리해 놓을 필요가 있다.

효율적인 데이터베이스 설계는 안정성과 성능을 동시에 가져갈 수 있다.

그러나 DB는 도서관과 달리 우리가 직접 책을 꽂아서 정리하는게 아니라 컴퓨터에 명령을 시켜야한다.

때문에 효율적인 데이터베이스에 대해서 고찰하기 전에, 먼저 간단한 용어와 조작법부터 알아보자

데이터 베이스 용어

SQL 데이터베이스에 저장된 내용을 보면 위 사진처럼 나올 것이다.

보시다시피 액셀과 비슷한 형식을 취하고 있는 것을 알 수 있다.

DBMS: DB를 관리하는 시스템이나 소프트웨어로 말한다. 도서관으로 치자면 관리직원 및 관리지침서라 할 수 있다.

데이터베이스: 위 사진에서 왼쪽 위 목록의 main_project다. 도서관으로 치자면 도서관 이름에 해당한다.

테이블: 위 사진에서 왼쪽의 목록이 바로 테이블이다. 도서관으로 치자면 분야(문학, 소설 등)에 속한다.

기본키: 위 사진에서 오른쪽 액셀에서 adviceId이다. 도서관으로 치자면 모든 책에 부여된 일련번호에 속한다.

SQL: 시퀄 또는 에스큐엘이라고 읽으며 쉽게 말해 명령어다. 도서관을 관리할 사서에게 내리는 명령이라고 볼 수 있다.

그 외 나머지는 각 데이터가 가지고 있는 정보들이다.

위 사진을 보면 advice테이블에 userKey 라는 정보와 title, categoryId, viewCount 등의 정보가 있는 것을 알 수 있다.

책으로 치지면 책 이름, 책의 카테고리, 이 책의 조회수 라고 할 수 있다.

여기서 내가 공부할 핵심이 바로 SQL 이다.

데이터 베이스를 조작하기 위한 쿼리문에 대해서 공부하는 것이 이번 공부의 핵심이다.

WorkBench

워크벤치의 설치법은 흔한 소프트웨어 설치법과 크게 다르지 않으니 생략하고 일단 로컬 인스턴스로 접속하자

이미 SCHEMAS에 여러개가 보인다.

이중 sakila, sys, world는 누구에게나 있는 기본 파일이고 나머지는 내가 예전에 쓰면서 만들었던 것이므로 무시하자

 SCHEMAS칸을 우클릭 -> CreateSCHEMAS를 하면 새로운 DB를 만들 수 있다.

이름을 정해주고, 아래의 Apply가 뜨면 아래 사진처럼 뜬다.

눈치가 빠른사람들은 창에 뜬 CREATE SCHEMA 'tarel_db'; 가 쿼리문이라는 것을 알것이다.

tarel_db목록을 펼치고, 아래 나온 목록에서 Tables를 우클릭, Create Table을 통해서 새로운 테이블을 만들자

그런데 만들기 이전에, 어떤 DB를 만들지 생각해야 하는데

보통 연습용으로 쇼핑몰을 만들곤 하니 쇼핑몰 DB를 만들어 보자

쇼핑몰에는 회원 테이블과 제품 테이블이 필요하다.

우선 회원테이블을 만들어보자

회원테이블은 대략 아래 처럼 만들 것이다.

프로그래밍 언어를 공부해봤다면 컴퓨터에는 데이터 자료형이 있다는 것을 알 것이다.

마찬가지로, DB에 자료를 저장할 때, 여기에 들어갈 자료형도 같이 지정해주어야 한다.

예를 들면 일련번호의 자료형은 정수형(INT), 아이디는 문자열(CHAR)이다.

본래 문자열은 CHAR을 사용하지만 여기선 VARCHAR를 사용하겠다.

위 표를 토대로 만든 테이블, 체크란이 많은 것을 알 수 있는데 PK는 일련번호 키라는 뜻이고 NN은 NotNull을 말한다.

NotNull이란 반드시 필요한 정보라는 뜻으로 책에 제목이 반드시 필요하지만 추천수는 그렇지 않은 것처럼 꼭 필요한 정보에 NN을 체크해준다.

UQ는 유니크함을 뜻하는데 이는 중복되면 안 된다는걸 뜻한다.

가장 오른쪽의 디폴트는 말 그대로 기본적으로 주어지는 값이다.

Apply를 하면 보시다시피 이에 맞는 쿼리문이 나온다.

위 방법을 반복하여, product 테이블도 만들어보자

product 테이블은 보다 심플하게 만들었다.

빨간 원을 눌러서, 해당테이블에 어떤 정보가 있는지를 볼 수 있는데

오른쪽 아래를 보면 알겠지만 전부 비어있다.

일단 유저부터 만들어주자

아까 만든 표대로 작성한 모습

Apply를 눌러서 적용하면

마찬가지로 쿼리문이 뜬다.

역시 Apply를 눌러서 적용해주어야 한다.

product 테이블도 체워주자

데이터를 지울 때는 지우고자 하는 데이터를 선택하고 빨간 원 버튼으로 지워줄 수 있다.

단, 지운 후에 반드시 Apply버튼을 눌러줘야 적용된다.

지울 때도 쿼리문이 뜨는걸 볼 수 있다.

이로써 간단한 테이블 제작 및 조작법에 대해 알아봤고

다음엔 핵심인 SQL에 대해 알아보자

내가 지금까지 벡엔드 웹개발 공부를 하면서, SQL DB(MySQL)를 조작할 때는 시퀄라이즈를 주로 사용했으며

딱히 쿼리문을 공부하지는 않았다.

그런데 최근 코딩테스트를 봤는데 쿼리문을 사용하라는 테스트가 나온 것이 아닌가

순간 뒷통수를 맞은 느낌이었다.

물론 SQL 프로그램을 쓰면서 쿼리문을 대략적으로나마 접하기도 했고 간단한 쿼리문으로 DB를 조작하기도 했지만

본격적으로 제대로 배우지는 않았다.

고로 이번 실패를 교훈삼아 SQL과 쿼리문에 대해서 제대로 공부해보고자 한다.

설치방법은 간단하지만 Select Product에 필요한 부분이 있다.

이것만 기억해

여기서 오른쪽에 있는 항목들을 누르면 아래 어드벤스 설정이 나오며 여기서 설치 경로를 수정해 줄 수 있다.

설치 항목중에 다른건 상관없지만 connector는 경로를 그대로 유지해주어야 한다.

기본 용어

DB

각종 정보를 저장해 놓는 데이터 베이스다.

DBMS

데이터 베이스를 운영하고 관리하는 소프트웨어 (ManagementSystem)이다.

SQL

SQL 데이터 베이스를 사용하기 위한 언어로 후술한다.

SQL(Structured Query Language)

SQL은 관계형 데이터베이스에서 사용되는 언어로 에스큐엘, 또는 시퀄 이라고 읽는다.

SQL은 특정 회사에서 만든 것이 아닌 국제 표준화 기구에서 SQL에 대한 표준을 정해서 발표하였으니

이를 표준SQL이라고 한다.

이 때문에 유명한 SQL 서버는 대부분 표준 SQL을 사용한다.

가장 비싸면서도 유명한 오라클, 나같은 가난한 이들의 구세주인 MySQL, MS사의 SQL 모두 표준 SQL을 쓴다.

여기서 나는 늘 사용해온 MySQL을 사용할 것이며

MySQL Workbench를 사용할 것이다.

이것은 구글링만 해도 쉽게 설치할 수 있으므로 설치과정은 생략한다.

이제 처음부터 다시 시작해보자