기술노트 블로그

안녕하세요, 혼자 공부하는 SQL을 보고 학습한 자료를 남깁니다.

인덱스의 개념

책의 찾아보기라고 보면 쉽다.

UNIQUE라는 단어를 찾아본다고 보자.

찾아보기에 없다면 책 전체를 다 넘겨야만 이 글자를 찾을 수가 있다.

엄청난게 오랜 시간이 걸리게 된다.

하지만 찾아보기에 있다면 'U'자를 찾아서 75페이지에서 금방 찾을 수가 있다.

찾아보기가 없을 때 찾는 것과 찾아보기가 있을 때 찾는 것은 차이가 크게 나면 수백배 차이가 난다.

인덱스의 필요성

인덱스는  반드시 필요한 것은 아니다.

하지만 현실적으로 실무에서는 INDEX가 없이는 사용하기가 굉장히 어렵다.

실제로 데이터가 엄청나게 많기 때문이다.

실무에서는 INDEX를 반드시 사용한다고 보면 된다.

실습에서는 데이터가 워낙 작기 때문에 INDEX가 없어도 괜찮은 것이다.

실제로는 INDEX가 있어야만 찾아보기가 가능하다.

찾는 기능은 바로 SELECT문이다.

SELECT를 빨리 하기 위해서는 찾아보기가 필요하다.

INDEX를 통해서 SELECT문을 하는 것이 현실적인 실무에서의 상황이다.

인덱스의 문제점

하지만 IDNEX의 이해 없이 무리하게 SELECT문을 날리면

시스템에 문제가 생기거나, 더 느려지는 경우가 있다.

그래서 정확히 이해를 해야한다.

만일 INDEX를 전부 만든다면 

찾아보기가 더 커져서 찾아보기에서 찾기가 어려울 것이다.

특히 책과 찾아보기를 왔다 갔다를 너무 많이 반복하면 당연히 어려울 것이다.

그래서 INDEX를 만든다고 다 좋은 것은 아니다.

대량의 데이터만 INDEX를 만들면 된다.

인덱스의 장단점

장점) SELECT문의 결과가 정말 빠르다.

한 명의 처리가 빠르면 다른 사람의 처리도 빨라 진다.

그러면 전반적인 컴퓨터의 시스템이 빨라진다.

컴퓨터의 부담 또한 줄어든다.

단점) INDEX는 추가적인 공간을 차지한다.

10~20% 정도를 차지한다.

만들어진 테이블의 INDEX를 만드는데 시간이 오래걸릴 수도 있다.

그래서 가능하면 처음부터 INDEX를 만드는 것이 좋다.

인덱스의 종류

1. 클러스터형 인덱스

영어 사전, 국어 사전

2. 보조 인덱스

책의 맨 뒤의 찾아보기

자동으로 생성되는 인덱스

클러스터형 인덱스가 자동 생성될 수 있고, 보조 인덱스가 자동 생성될 수 있다.

PK 생성 시 PK열에 자동으로 클러스터형 인덱스가 생성이 된다.

영어사전과 같은 의미이다.

ABCD 순으로 되어있다.

SELECT를 하면 PK열로 정렬이 되어서 조회가 된다. (ABCD~ 순으로)

PK가 있는 테이블의 경우,

아무렇게나 입력을 해도

SELECT 시 PK 열로 ABCD~ 순으로 정렬이 되어서 조회가 되는 것을 볼 수 있다.

PK로 지정한 열에 자동으로 클러스터형 인덱스가 생성이 되었다.

SHOW INDEX FROM

table1 테이블을 생성하고, col1은 PK로 생성한다.

SHOW INDEX FROM 명령문을 통해 해당 table1 테이블의 인덱스를 조회하는 것을 확인할 수 있다.

Non_unique가 0이면 유일하다는 의미이다. PK는 중복값이 입력될 수 없기 때문이다.

Key_name에 PRIMARY로 입력되어 있다면 클러스터형 인덱스이다.

Column_name에 col1이라면 col1 컬럼에 클러스터형 인덱스가 생성되어 있다.

table2 테이블을 생성하고, 인덱스를 확인해보겠다.

이번에는 인덱스가 3개가 생긴 것을 볼 수 있다. 클러스터형 인덱스 1개(PK) 그리고 보조 인덱스 2개(UNIQUE)가 자동으로 생성되었다.

Key_name에 col2, col3가 입력된 이 두개의 컬럼은 보조 인덱스가 생성된 것이다.

UNIQUE는 테이블에 여러 개를 만들 수 있다. 즉 보조 인덱스는 여러 개 만들 수 있다.

그림(1)은 인덱스가 없이 데이터를 INSERT 시킨 것

정렬이 되어 있지 않다.

그리고 그림(2)는 인덱스가 있이 데이터를 INSERT 시킨 것이다. 

정렬이 되어 있다.

PK로 자동 클러스터형 인덱스를 생성하거나, 클러스터형 인덱스를 생성해주어야 한다.

인덱스 없이 테이블을 생성한 경우

INSERT한 순서 그대로 SELECT 되는 것을 볼 수 있다.

인덱스를 생성한 테이블의 경우

ALTER TABLE member ADD CONSTRAINT PRIMARY KEY(mem_id);

명령어를 통해서 member 테이블의 mem_id를 PK로 선언할 수 있다.

mem_id열에 자동 클러스터형 인덱스가 생성된다.

SELECT 시 mem_id열로 알파벳 순으로 정렬이 된 것을 볼 수 있다. 영어사전 처럼 정렬이 된다.

클러스터형 인덱스는 테이블에 하나밖에 지정을 못 한다.

두개가 지정이 안 되는 것이 말이 안 되는 것은, 한 개의 컬럼으로 정렬이 되어야지 두개의 테이블을 동시에 정렬을 할 수는 없기 때문이다.

PK를 지우고 다시 이번에는 member 테이블의 mem_name열을 PK로 지정을 하였다.

그리고 SELECT시 mem_name열로 정렬되어 조회가 되는 것을 확인할 수 있다.

그리고 소녀시대를 INSERT 시 

member 테이블에 순서가 맞추어서 INSERT가 되는 것을 볼 수 있다.

클러스터형 인덱스를 만들면 그 클러스터형 인덱스로 정렬이 된다.

내용적인 측면은 전혀 변함이 없다. 차례만 바귀는 것이다.

정렬되지 않는 보조 인덱스

보조 인덱스는 책의 내용은 그대로 유지가 되면서 찾아보기가 만들어 지는 것이다.

찾아보기는 가나다 순으로 되어 있다.

찾아보기를 통해서 페이지를 찾아보는 기능이다.

다시 인덱스를 제거하기 위하여

DROP TABLE IF EXTISTS member; 를 실행하여 member 테이블을 제거하고

CREATE TABLE 을 통해서 member 테이블을 인덱스가 없이 생성한다.

그리고 데이터를 아무렇게나 입력을 한다.

SELECT를 하면 데이터가 INSERT한 순서대로 입력된 것을 볼 수 있다.

이번에는 mem_id에 UNIQUE라는 CONSTRAINT를 추가하고 

다시 member 테이블을 조회를 한 것을 볼 수 있다.

내용 및 차례는 아무런 변화도 없는 것을 볼 수 있다.

여러개의 보조 인덱스

보조 인덱스는 한 테이블에 여러개 만들 수 있다.

예를 들어 동식물에 대한 책이 있다고 가정을 해보자.

그러면 당연히 찾아보기에는 동물에 관한 찾아보기와, 식물에 관한 찾아보기

이렇게 두개의 찾아보기가 있어야 편리하다.

mem_name열을 보조 인덱스로 만들었다.

그리고 SELECT를 해보니 결과의 내용과 순서에는 변함이 없다.

똑같다.

이번에는 소녀시대라는 새로운 데이터를 추가해보겠다.

그리고 SELECT를 해보면 

새로 추가된 데이터는 맨 뒤에 입력이 되어서 조회가 되는 것을 볼 수가 있다.

찾아보기만 나중에 추가가 되는 것이다.

내용은 그대로이다.

감사합니다.

https://www.youtube.com/watch?v=KZmW6VaY5BU&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=16

안녕하세요, 혼자 공부하는 SQL을 보고 학습한 자료를 남깁니다.

인덱스의 개념

책의 찾아보기라고 보면 쉽다.

UNIQUE라는 단어를 찾아본다고 보자.

찾아보기에 없다면 책 전체를 다 넘겨야만 이 글자를 찾을 수가 있다.

엄청난게 오랜 시간이 걸리게 된다.

하지만 찾아보기에 있다면 'U'자를 찾아서 75페이지에서 금방 찾을 수가 있다.

찾아보기가 없을 때 찾는 것과 찾아보기가 있을 때 찾는 것은 차이가 크게 나면 수백배 차이가 난다.

인덱스의 필요성

인덱스는  반드시 필요한 것은 아니다.

하지만 현실적으로 실무에서는 INDEX가 없이는 사용하기가 굉장히 어렵다.

실제로 데이터가 엄청나게 많기 때문이다.

실무에서는 INDEX를 반드시 사용한다고 보면 된다.

실습에서는 데이터가 워낙 작기 때문에 INDEX가 없어도 괜찮은 것이다.

실제로는 INDEX가 있어야만 찾아보기가 가능하다.

찾는 기능은 바로 SELECT문이다.

SELECT를 빨리 하기 위해서는 찾아보기가 필요하다.

INDEX를 통해서 SELECT문을 하는 것이 현실적인 실무에서의 상황이다.

인덱스의 문제점

하지만 IDNEX의 이해 없이 무리하게 SELECT문을 날리면

시스템에 문제가 생기거나, 더 느려지는 경우가 있다.

그래서 정확히 이해를 해야한다.

만일 INDEX를 전부 만든다면 

찾아보기가 더 커져서 찾아보기에서 찾기가 어려울 것이다.

특히 책과 찾아보기를 왔다 갔다를 너무 많이 반복하면 당연히 어려울 것이다.

그래서 INDEX를 만든다고 다 좋은 것은 아니다.

대량의 데이터만 INDEX를 만들면 된다.

인덱스의 장단점

장점) SELECT문의 결과가 정말 빠르다.

한 명의 처리가 빠르면 다른 사람의 처리도 빨라 진다.

그러면 전반적인 컴퓨터의 시스템이 빨라진다.

컴퓨터의 부담 또한 줄어든다.

단점) INDEX는 추가적인 공간을 차지한다.

10~20% 정도를 차지한다.

만들어진 테이블의 INDEX를 만드는데 시간이 오래걸릴 수도 있다.

그래서 가능하면 처음부터 INDEX를 만드는 것이 좋다.

인덱스의 종류

1. 클러스터형 인덱스

영어 사전, 국어 사전

2. 보조 인덱스

책의 맨 뒤의 찾아보기

감사합니다.

https://www.youtube.com/watch?v=KZmW6VaY5BU&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=16

안녕하세요, 혼자 공부하는 SQL를 보고 학습한 자료를 남깁니다.

뷰 개체란

데이터베이스 개체 중 하나

바로가기 아이콘과 비슷

실체가 없다.

테이블은 진짜 데이터가 있고, 뷰를 만들면 실체가 없다.

하지만 실재 테이블로 접근이 가능한 것이다.

뷰는 가상의 테이블이다.

테이블이라고 보아도 크게 무방하다.

뷰 설명

뷰를 만드는 형식은 다음과 같습니다.

SELECT문을 사용을 해서 똑같이 VIEW를 만들 수 있다.

뷰의 작동 원리

VIEW를 SELECT를 하면 실제 테이블의 결과를 조회해준다.

가상의 테이블이라고 한다.

VIEW에는 진짜 데이터가 없고, SELECT문만 존재한다.

실습

다음과 같이 먼저 market_db에 있는 member에서 테스트를 한다.

VIEW 이름을 v_member로 만든 이유는 

v_를 붙여야 view인지 아니면 table인지 알 수 있기 때문이다.

다음은 VIEW를 만들어서 조회를 한 결과이다.

VIEW를 접근하는데 WHERE절로도 접근이 가능하다.

기존에 테이블에 접근했던 것과 동일하게 접근이 가능하다.

뷰를 사용하는 이유

1) 보안(Security)에 도움이 됩니다.

v_member 뷰에는 사용자의 아이디, 이름, 주소만 있을 뿐 사용자의 중요한 개인 정보인 연락처, 평균 키, 데뷔 일자 등의 정보는 들어 있지 않다. (정보를 가림), 실제 중요한 정보는 공개를 하지 않는 것이다.

예를 들어, 아르바이트생이 회원 정보를 수정하고 싶다. 다른 정보까지 보게 되면 '개인정보유출'이 된다. 그래서 아르바이트생이 뷰만 접근하게 만드는 것이다. 

사용자는 뷰에만 접근할 뿐 실제 테이블에는 접근을 할 수 없다.

2) 복잡한 SQL을 단순하게 만들 수 있다.

복잡한 쿼리를 VIEW로 만들면

간단한 SELECT문으로 접근이 가능하다.

이렇게 조인한 테이블을 

VIEW로 만들어서 

단순하게 SELECT문으로 조회가 가능하도록 만들었다.

WHERE절로 접근이 가능하다.

다음은 그 결과이다.

뷰의 실제 작동

열 이름을 바꾸어서 VIEW를 만들 수 있다.

띄어쓰기가 들어간 열을 조회할 때는 `(백틱)을 써서 조회가 가능하다.

VIEW 수정을 위해서는 ALTER VIEW 명령어를 통해서 수정이 가능하다.

별칭으로 열의 이름을 한글로 사용을 해서 조회한 결과이다.

열 이름을 한글로 별칭을 통해서 바꾸어서 조회를 하는 것은 권장을 하지는 않는다.

하지만 필요에 따라서는 한글로 열이름을 해서 조회가 가능하다.

DROP VIEW 명령어를 통해서 뷰 삭제가 가능하다.

뷰의 정보 확인

CREATE OR REPLACE VIEW 를 통해서 VIEW가 없으면 생성을 하고, 있으면 바꾼다.

DESCRIBE 를 통해서 뷰 세부정보 확인이 가능하다.

하지만 보시다 싶이 PK는 확인이 안 되는 것을 볼 수 있다.

실제 member 테이블에서는 DESCRIBE 명령어로 PK 확인이 가능한 것을 볼 수 있다.

SHOW CREATE VIEW 명령어를 통해서 사용자가 만든 뷰의 CREATE문을 비슷하게 볼 수 있다.

Form Editor를 통해서 이렇게 확인이 가능하다.

뷰의 데이터 수정 작업

다음은 v_member의 데이터를 UPDATE한 결과이다.

정상적으로 UPDATE 되는 것을 볼 수 있다.

INSERT를 실패한 것을 볼 수 있다.

NOT NULL 제약조건이 존재하기 때문이다.

해결하는 방법은 NOT NULL 제약조건의 컬럼들에 DEFAULT 값을 주는 것이다.

일반적으로 VIEW를 통해서 INSERT문을 하는 것은 바람직하지 않다.

키가 167이상인 회원들을 VIEW로 만들어보았다.

이번에는 v_height167에서 키가 167보다 작은 회원들을 삭제를 해보았다.

정상적으로 처리가 되는 것을 볼 수 있다. 총 0건의 데이터가 처리되었다.

INSERT문이 정상적으로 처리가 되었다. 조건이 맞았기 때문이다.

하지만 뷰에서는 조회가 되지 않는다. 

바람직하지는 않다.

WITH CHECK OPTION의 사용

그래서 WITH CHECK OPTION을 사용을 하면 

167미만은 뷰에 INSERT가 불가능하다.

그래서 다음과 같이 키가 140인 텔레토비 회원은 뷰에 INSERT가 안 되는 것을 볼 수 있다.

WITH CHECK OPTION이 훨씬 더 안정적인 상황이고 일반적이다.

참조하고 있는 실제 테이블이 지워진 경우

이번에는 member, buy 테이블을 지워보겠다.

그리고 나서 다시 v_height167 뷰를 조회를 하면 오류가 나는 것을 볼 수 있다.

뷰가 참조하고 있는 테이블을 지웠기 때문이다.

뷰가 참조하고 있다고 해서 테이블이 안 지워지는 것은 아니다.

CHECK TABLE

CHECK TABLE v_height167; 명령어를 통해서 뷰가 왜 조회가 안되는지 체크가 가능하다.

Msg_text에 뷰가 참조하는 실제 테이블이 없다고 나온다.

만약 뷰 조회가 안된다면 CHECK TABLE을 통해서 확인이 가능하다.

감사합니다.

https://www.youtube.com/watch?v=JrXWxku7ZIM&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=15

안녕하세요, 혼자 공부하는 SQL를 보고 학습한 자료를 남깁니다.

제약조건

기본 키와 외래 키가 대표적인 제약조건입니다.

데이터의 무결성을 유지합니다. (완전무결한 코드)

예를 들어, 제약조건을 통해서 중복된 아이디가 NAVER에 접속하는 것을 방지한다.

기본키 제약조건

그 많은 회원 중에서 아이디만 알면 그 회원이 누구인지를 알 수가 있다.

그것이 바로 기본키(PRIMARY KEY)이다.

중복 X, NULL X

기본키로 생성한 것은 자동으로 '클러스터 인덱스'가 생성이 됩니다.

한 테이블에는 하나의 기본키(PRIMARY KEY)만 등록이 가능하다.

열들을 줄여서 다시 실습을 해보도록 하겠습니다.

실습

#1번째 방식

member, buy 테이블을 다시 생성하고

열들을 줄여서 생성한다.

그리고 DESCRIBE member; 명령어를 통해서 

result 창에서 member테이블을 파악할 수 있다.

#2번째 방식

member 테이블을 다시 생성해본다.

이번에는 PRIMARY KEY 문법을 다른 문법으로 바꿔서 사용을 해본다.

PRIMARY KEY(mem_id)

이 결과는 첫번째 방식과 동일하다.

#3번째 방식

member 테이블에 PRIMARY KEY를 

ALTER TABLE 명령문을 통해서

ADD CONSTRAINT PRIMARY KEY (mem_id);

를 추가할 수 있다.

#4번째 방식

주로, PRIMARY KEY 추가를 위해서는 첫번째 방식을 주로 사용을 한다.

하지만 다른 곳에서는 다른 방식을 선호할 수도 있기 때문에

기본키 제약조건 설정 방식을 모두 소개한다.

외래키 제약조건

회원 테이블을 기준 테이블이라고 하고,

구매 테이블을 참조 테이블이라고 한다.

이와 같은 방식으로 외래키 제약조건을 만들고 데이터의 무결성은 만든다.

그래서 조인을 할 수 있도록 설계가 되어있다.

#1번째 방식

다음과 같이 테스트해보기 위해

다시 member, buy 테이블을 DROP하고 CREATE한다.

buy 테이블에서 FOREIGN KEY(mem_id) REFERENCES member(mem_id);

#2번째 방식

user_id 라는 컬럼명으로 FOREIGN KEY를 만들어보았다.

user_id 처럼 이렇게 꼭 mem_id처럼 이름이 같지 않아도 무방하다. 하지만 권장하지는 않는다.

동일하게 쓰는 것을 권장을 합니다.

#3번째 방식

ALTER TABLE을 통해서 이후에 

ADD CONSTRAINT FOREIGN KEY(mem_id) REFERENCES member(mem_id);

이렇게 외래키를 추가할 수 있다.

만약에 기준 테이블의 아이디를 BLK에서 PINK로 바꾸었다.

그러면 이전 참조 테이블의 아이디가 BLK인 행들은 어떻게 될까?

해당 화면과 같이 member, BLK를 하나 추가 시켰다.

그리고 buy에 지갑과 맥북을 추가 시켰다.

이렇게 회원을 한 명 등록하고, 두개의 물품을 구매를 했다.

구매목록을 확인을 하였다.

내부 조인을 통해서 회원 정보도 일부 같이 확인을 할 수 있다.

이번에는 아이디를 바꾸어 보겠다.

오류가 발생하는 것을 확인할 수 있다.

DELETE문도 적용이 안 된다.

이번에는 회원 탈퇴를 해볼까요?

근데 회원이 탈퇴도 되지 않아요.

왜냐하면 그냥 회원을 삭제해버리면 연관이 있는 구매 테이블에 있던 데이터가 결함이 있는 데이터가 된다.

기준 테이블의 데이터를 수정할 때

자동으로 참조 테이블도 변경된다면 문제가 없지 않을까요?

ON UPDATE CASCADE 그리고 ON DELETE CASCADE를 사용할 수 있다.

ALTER TABLE 시

ADD CONTRAINT 를 통해서

FOREIGN KEY(mem_id) REFERENCES member(mem_id)

ON UPDATE CASCADE 

ON DELETE CASCADE

를 추가해줄 수 있다.

그러면 회원 테이블을 UPDATE 하였는데도 참조 테이블인 구매 테이블의 정보들도 모두 UPDATE 된 것을 확인할 수 있다.

또한 회원 테이블을 DELETE 시 참조 테이블에서도 DELETE가 이루어지는 것을 볼 수 있다.

기타 제약조건

고유키는 중복을 허용하지 않지만, NULL은 허용한다.

NULL은 여러값이 입력되어도 괜찮다.

에이핑크를 INSERT할 때 오류가 발생하는 것을 볼 수 있다.

고유키 제약조건을 범했기 때문이다.

체크 제약조건

예를 들어 연락처의 국번에 02, 031, 041, 055 중 하나만 입력되도록 해야 할 때

또는 평균키는 100이상의 값만 입력되도록 해야 할 때

CHECK(조건)을 사용한다.

CHECK 제약조건을 범하였으므로

트와이스는 INSERT되지 않은 것을 확인할 수 있다.

기본값 정의

입력을 안 하면 자동으로 넣어줘 하는 값이다.

ALTER TABLE member 

ALTER COLUMN phone1 SET DEFAULT '02';

를 통해서 member 테이블의 기본값 제약조건에 '02'를 추가한다.

INSERT문으로 입력할 때는 반드시 default로 입력을 한다.

그 결과로 우주소녀, 160과 02가 입력된 것을 볼 수 있다.

널 값 허용

예를 들어 이름 없는 사람은 없다.

회원가입했는데 이름이 없으면 이름을 못 찾으니까 반드시 NOT NULL을 추가해서 

NULL값 입력을 방지할 수 있다.

감사합니다.

https://www.youtube.com/watch?v=BUHj-behLyc&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=14

안녕하세요, 혼자 공부하는 SQL을 보고 학습한 자료를 남깁니다.

엑셀과 데이터베이스 비교

데이터베이스의 테이블은 엑셀과 비슷한 구조로 되어 있습니다.

테이블을 만드는 방법

1) 마우스로 클릭을 해서 만드는 GUI 방식이 있다.

2) SQL로 만들기

테이블 설계

집을 짓기 전에 설계도를 그려야 한다.

첫번째 테이블은 회원이고

두번째 테이블은 구매이다.

실습

CREATE DATABASE naver_db; 명령어를 통해 naver_db 데이터베이스를 만듭니다.

그리고 SCHEMAS창에 우클릭을 하고 Refresh All 버튼을 클릭합니다.

naver_db가 생성되었는지 확인합니다.

SCHEMAS창에서 naver_db를 더블클릭하여 naver_db를 사용합니다.

왼쪽에는 데이터베이스 구성도 그리고 오른쪽에는 풀화면으로 테이블들을 만들 예정입니다.

SCHEMAS창에서 naver_db를 우클릭하고 Create Table 버튼을 클릭하여 위 화면과 같이 만듭니다.

PK는 중복을 절대 허용 않기.

Not Null은 반드시 입력하기.

마지막에 생성된 행은 우클릭 후 'Delete Selected'를 통해 삭제를 할 수 있다.

완료된 후 Apply 버튼을 클릭을 하면

그러면 GUI환경에서 테이블을 만들었지만, 실제 SQL로 만든 것과 동일한 효과가 되는 것이다.

익숙하다면 SQL문을 사용하셔도 되고,

익숙하지 않다면 GUI환경에서 테이블을 만들면 된다.

Apply 버튼을 클릭을 하고 다음 화면에서 Finsih 버튼을 클릭을 하면 

SCHEMAS창에서 naver_db > Tables > member 테이블이 만들어진 것을 확인할 수 있다.

동일한 방법으로 buy 테이블을 만든다.

이후 SQL이 만들어지는 창에서 자동으로 만들어준 쿼리문을 수정해야 한다.

관계를 맺어주어야 하기 때문이다. FOREIGN KEY(mem_id) REFERENCES member(mem_id) 를 추가한다.

member 테이블의 pk를 buy 테이블이 참조하므로, member 테이블과 buy 테이블은 서로 1:N의 관계를 형성한다.

이렇게 관계 형성을 마친다.

이후 SCHEMAS 창을 통해서

다음과 같이 buy 테이블 또한 만들어진 것을 확인한다.

다음으로 데이터 입력을 해주어야 한다.

SCHEMAS 창에서 member 테이블을 우클릭 하고 'Select Rows - Limit 1000'을 클릭하여 데이터를 조회한다.

입력된 정보가 있는지 확인하기 위함이다.

result 창에 행들을 더블 클릭하여 직접 데이터를 입력해본다.

완료되면 result 창에 'Apply' 버튼을 클릭하여 반영한다.

SQL 문을 통해서 직접 확인해 본다.

자료형이 숫자인 컬럼도 데이터를 문자의 형태로 입력을 해주는 것을 볼 수 있다.

'Apply' 버튼을 클릭한다.

표를 참고하여

다음으로 buy 테이블에 데이터를 입력해보겠다.

순번은 AUTO INCREMENT 이므로 입력을 하지 않고 데이터를 입력하겠다.

다음과 같이 result 창에 행을 더블클릭하여 직접 데이터를 3건 입력해주고

'Apply' 버튼을 클릭해보겠다.

하지만 오류가 발생하는 것을 볼 수 있다.

1:N 관계이기 때문에 구매 테이블에 없는 회원을 데이터로 입력하려고 하였기 때문이다.

일대다의 관계가 이와 같은 의미이다.

회원에 데이터가 있어야만 구매에 데이터를 입력할 수 있다.

회원이 있어야 구매도 가능하기 때문이다.

'Cancel' 버튼을 클릭을 하고

result 창에서 삭제할 행을 우클릭 하고 'Delete Row(s)'를 클릭하면 된다.

다음과 같이 데이터 입력을 성공한 것을 확인할 수 있다.

앞에서 3건의 실패가 있었으므로

num값이 4부터 시작한 것을 알 수가 있다.

이렇게 직접 GUI로 테이블을 만들고, 데이터를 입력해보았다.

직접 SQL문으로 테이블을 만들고, 데이터를 입력하길 권장한다.

SQL로 테이블 만들기

실제로 CREATE문은 굉장히 복잡하다.

실습을 통해서 알아보겠다.

DROP DATABASE IF EXISTS naver_db; 명령어를 통해서 만일 naver_db가 존재한다면 데이터베이스를 삭제한다.

그리고 CREATE DATABASE naver_db; 명령어를 통해서 다시 naver_db 데이터베이스를 삭제한다.

다시 naver_db를 사용하기 위해 USE naver_db; 명령어를 사용한다.

SQL로 작성을 한 결과 

GUI 환경에서 실행한 것과 동일한 결과가 발생하는 것을 볼 수 있다.

감사합니다.

https://www.youtube.com/watch?v=DMNpkj_bZIs&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=13

안녕하세요, 혼자 공부하는 SQL을 보고 학습한 자료를 남깁니다.

조인

두 개의 테이블을 통해서 새로운 정보를 추출하는 것.

내부 조인

내부 조인은 가장 많이 사용된다.

일반적으로 조인은 내부조인을 이야기 한다.

일대다 관계의 이해

회원 테이블과 구매 테이블은 1:N 관계로 이루어져 있다.

member 테이블의 아이디는 PK이다. 

1이라고 표현을 하는 이유가 아이디는 하나밖에 없기 때문에 1이라고 표현을 한다.

회원이 구매를 할 때 

구매 테이블에 아이디를 기록을 한다.

만일 member 테이블의 아이디를 구매 테이블의 PK로 지정을 하면 회원은 한 번밖에 구매를 못한다.

그래서 구매 테이블의 member 아이디는 FK로 잡힌 것이다.

그래서 일대다 관계를 'PK-FK 관계'라고도 부릅니다. 현실에서 굉장히 많이 쓰이는 관계이다.

예를 들어, 직원과 급여 테이블을 들면 직원은 여러번 급여를 받는다.

예를 들어, 학생과 수강신청 테이블을 들면 학생은 여러번 수강신청을 한다.

SELECT <열 목록>

FROM <첫 번째 테이블>

    INNER JOIN <두 번째 테이블>

    ON <조인될 조건>

[WHERE 검색 조건]

조인을 하기 위해 데이터베이스 구성도 표를 보면서 진행하겠습니다.

mem_id = 'GRL' 인 회원의 정보와 구매 내역을 합쳐서 결과를 조회한 것이다.

만일, 회원이 없다면 NULL이 나올 것이고, 구매 내역이 없다면 또한 NULL이 나올 것이다.

다음 예제는 오류가 발생했다.

왜냐하면 mem_id가 회원 테이블에도 있고, 구매 테이블에도 있다.

그래서 *를 사용을 안 하고, 조회할 컬럼을 명시한다면 어떤 테이블의 컬럼인지를 명시해야 한다. -> member.mem_id

수정을 하면 이렇게 잘 실행되는 것을 볼 수 있다.

연락처의 경우 CONCAT()함수로 전부 다 이어서 처리를 했다.

컬럼명 앞에 테이블의 이름을 다 적어주는 것이 좋다.

하지만 이렇게 하면 코드가 너무 길어진다.

뒤에 별명을 주어서 해결이 가능하다.

조인의 결과가 중복되는 경우가 DISTINCT로 처리가 가능하다.

DISTINCT를 통해서 member 테이블의 아이디를 중복을 제거하고 하나씩만 출력을 할 수 있다.

외부 조인

내부 조인은 조인할 수 있는 정보만 결과로 조회가 된다.

하지만 외부 조인의 경우 기준이 되는 테이블은 조인이 되지 않더라도 NULL로 채워져서 전부 다 조회가 된다.

LEFT/RIGHT/FULL 모두 다 같은 의미이다.

잇지는 구매한 적이 없다.

그런데도 왼쪽에 있는 (기준이 되는 테이블)은 전부 다 조회가 된다.

RIGHT OUTTER JOIN으로 테이블의 위치만 서로 바꾸어서 조인을 했다.

결과는 LEFT OUTTER JOIN과 동일하다.

WHERE 절에 B.prod_name IS NULL 을 통해서 기준이 되는 테이블인 회원에 구매 내역이 없는 데이터들을 조회할 수가 있다.

구매 내역이 없는 회원들을 조회한 결과이다.

기타조인

■ 상호조인

조인이라고 표현하기는 애매하다.

각 행별로 모든 테이블을 조인한다.

이를 CROSS JOIN(상호조인)이라고 한다.

대용량의 데이터를 만들 때 자주 사용된다.

CROSS JOIN은 ON 절이 없다.

각각의 테이블이 서로 전부 다 조인이 된다.

서로 곱한 결과 만큼 출력이 된다.

만약에 10억 건의 데이터를 만들고 싶다면 INSERT는 어렵다.

하지만 두개의 테이블을 CROSS JOIN 하면 굉장히 편리하다. 

엄청나게 많은 데이터를 생성할 수 있다.

이렇게 1,800만 데이터 건을 생성할 수 있다.

Output 결과 창에 노란색 느낌표 세모 창이 뜨긴 하지만 

총 데이터를 47,800 건의 데이터를 담은 테이블이 만들어졌다.

테스트 결과 데이터들이 정상적으로 조회가 되는 것을 확인할 수 있다.

■  자체 조인 (SELF JOIN)

자체 조인(SELF JOIN)은 자주 사용 되지는 않지만, 직원과 직속 상관을 예로 들 수 있다.

경리 부장의 직속 상관인 관리 이사의 사내 연락처를 알아내려면 

테이블이 스스로 테이블을 조인해야 값을 알아낼 수 있다.

쉽게 하는 방법은 똑같은 테이블을 복사해서 옆에 두고

두 테이블을 조인한다 이렇게 생각해도 상관 없다.

같은 테이블을 조인하기 때문에 별칭을 주어서 구분해서 테이블을 조인한다.

다음과 같이 데이터를 조인해서 직원과 그에 대한 직속 상관에 대한 정보를 조인해서 결과를 얻어낸 것을 볼 수 있다.

기존 셀프조인에 사용했던 emp_table 테이블의 전체 조회 결과이다.

총 9건이 조회가 되었다.

셀프 조인시 기존의 연락처를 덮어쓰고, 조인된 결과만 반환을 하므로, 8건의 결과가 조회된 것을 볼 수 있다.

감사합니다.

https://www.youtube.com/watch?v=tuQFkzjqEGw&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=12

안녕하세요, 혼자 공부하는 SQL를 보고 학습한 자료를 남깁니다.

데이터 형식

데이터베이스는 SELECT, INSERT, UPDATE, DELETE문을 수행하기 위해 

데이터 형식을 제공을 합니다.

각 데이터 형식은 세분화되어 있습니다.

정수형 (소수점이 아닌 수)

INT는 가장 무난하게 사용할 수 있는 데이터 형식

하지만 예를 들어, 카카오톡에서는 이용자수가 5,000만명이 넘습니다.

나이를 INT로 저장을 하면 각 나이는 4byte를 차지를 하는데

5,000만 * 4byte를 하면 상상도 못할 정도의 용량을 차지하게 됩니다.

하지만 나이는 굳이 -21억부터 +21억까지 저장될 필요는 없습니다. 이와 같은 경우 

TINYINT를 사용할 수 있습니다.

각 정수형 자료형은 다음과 같이 데이터를 저장할 수 있습니다.

확인 차원에서 테이블을 만들어 보겠습니다.

hongong4라는 테이블을 만듭니다.

TINYINT, SMALLINT, INT, BIGINT 이렇게 네 자료형을 각각 갖은 컬럼들을 갖은 테이블을 만듭니다.

그리고 최대값을 INSERT 해보고

최대값의 1을 넘긴 수들을 INSERT 해봅니다.

최대값을 넣은 경우네느 성공하였다는 것을 Output 창에서 확인할 수 있습니다.

하지만 최대값의 1을 넘긴 수는 오류가 발생한 것을 확인할 수 있습니다.

인원수는 현재 INT로 되어 있다. 하지만 INT는 최대 21억까지 된다.

그래서 TINYINT로 바꾸는 것이 좀 더 효율적이다.

그리고 키는 SMALL INT로 데이터 형식을 지정을 했는데 키는 -32768~32767까지의 저장공간이 필요가 없으므로 TINYINT로 바꾸어도 괜찮을 것 같다.

하지만 TINYINT는 -128~127까지는 키가 200이 넘는 경우도 있으므로

이를 UNSIGNED로 해결할 수 있다. 그러면 0~255까지 저장이 가능하다.

그러면 이렇게 mem_number를 TINYINT로 바꾸고, height은 TINYINT UNSIGNED로 바꿀 수 있다.

문자형

대표적인 문자형은 CHAR(개수), VARCHAR(개수)가 있습니다.

CHAR(10)과 VARCHAR(10)은 다르다.

CHAR(10)인 자료형에 3글자만 저장을 하면 공간이 낭비된다.

하지만 VARCHAR(10)인 자료형에 3글자만 저장을 하면 공간이 줄어든다. 공간을 효율적으로 사용할 수 있다.

하지만 CHAR로 설정을 하면 내부적으로는 빠른 성능(빠른 속도)를 내므로 CHAR로 설정하면 조금 더 좋습니다.

그래서 CHAR는 글자의 개수가 고정된 경우에 사용하는 것이 좋다.

VARCHAR는 글자의 개수가 변동될 경우에 사용하는 것이 좋다.

예를 들어 거주지역의 경우 정확히 2글자만 저장하도록 계획을 했다. 고정된 값을 저장하는 것이기에 그러면 CHAR(2)를 사용하는 것이 좋다.

하지만 방탄소년단이나 잇지 처럼 서로 다른 문자열의 길이가 들어가면 VARCHAR(10)로 저장하는 것이 좋다.

전화번호의 경우 더하기/빼기 또는 크다/작다가 의미가 없다. 그러면 문자로 취급을 하는 것이 좋다.

숫자로 한다고 해서 틀리지는 않는다. 효율성이 떨어진다. 의미가 없기 때문이다.

숫자의 모양을 하고 있지만 연산의 의미도 없고 크다/작다의 의미도 없다면 문자로 취급하는 것이 일반적이다.

대량의 데이터 형식

보다 더 큰 데이터를 저장하려면 다음과 같은 데이터 형식을 사용을 해야 한다.

대량의 데이터 형식 _손코딩 실습

big_table을 생성하려고 시도를 하는데 오류가 발생한다.

데이터 형식보다 더 큰 데이터를 저장하려고 했기 때문이다.

DROP문을 실행하여 다시 big_table을 다시 CREATE를 할 수 있다.

NETFLIX와 같은 데이터베이스를 만들어 보았다.

동영상 테이블이 있고 자막 테이블이 있다고 가정을 한다.

자막 VARCHAR(가변형 문자)로 잡아도 안 되고 동영상 BLOB(이진 데이터)으로 잡아도 안 된다.

그럴 때 지원하는 데이터 형식이 바로 

LONGTEXT와 LONGBLOB이다.

실수형

FLOAT은 바이트 수가 4이다. (소수점 아래 7자리까지 표현)

DOUBLE은 바이트 수가 8이다. (소수점 아래 15자리까지 표현)

날짜형

DATE는 날짜만 그리고 TIME은 시간만 저장

예를 들어, 출퇴근 시간의 경우 DATETIME이 필요한 것이다.

변수

데이터베이스에서 변수는 워크벤치가 재시작할 때 까지는 유지되지만 종료하면 없어집니다.

그러므로 임시로 사용한다고 생각하면 됩니다.

간단한 예를 살펴보겠습니다.

SET @myVar1 = 5;

SET @myVar2 = 4.25;

@ + 변수명 = 대입할 값;

의 형식으로 변수를 선언하면 됩니다.

변수 선언_손코딩 실습 

임시적으로 변수를 선언하여서 값을 사용할 수 있습니다.

SET 명령문을 통해서 변수를 선언하고 값을 할당할 수 있고,

SELECT문을 사용해서 변수의 값을 조회하거나, 계산을 하는 등의 변수간의 연산 결과 값이 조회가 가능합니다.

하지만 지금 접속된 root계정에서는 가능하지만, 다른 계정에서는 변수를 사용할 수 없다.

워크 벤치를 끄고 다시 실행을 하면 다음과 같이 @myVar1의 값이 조회가 안 된다.

변수는 현재 상황에서만 쓰는 임시적이다.

79번줄 코드와 80번줄 코드는 서로 같은 것이다.

변수에 해당 값들이 대입이 된 것이다.

LIMIT의 값에 변수를 대입을 해주려고 하면 되지 않는다.

이후에 오류가 발생하는 쿼리이기 때문이다.

그래서 미리 오류가 발생된다.

이와 같은 경우 PREPARE 명령문과 EXECUTE 명령문을 통해서 실행이 가능하다.

?에 @count의 값이 대입이 되는 것이다.

데이터 형 변환

데이터의 형변환이 필요하다.

우리가 명시적으로 데이터를 형변환 해줄 수도 있고, 암시적으로 데이터를 형변환 해줄 수도 있다.

다음과 같이 AVG() 함수를 사용해서 전체 컬럼의 평균값 조회가 가능하다.

다음과 같이 CAST(AVG(price) AS SIGNED) 를 하면 평균값을 낸 해당 실수를 SIGNED 즉 부호가 있는 정수형으로 바꾸어라는 의미이다. 

결과 값으로 평균가격 143이 출력된 것을 볼 수 있다.

CONVERT함수를 사용해서 실수형을 정수형으로 바꾼 것이다.

문자를 날짜 형식으로 바꾸는 것도 이와 같은 형식으로 사용이 가능하다.

CAST함수를 통해서 price를 CHAR로, amount를 CHAR로 바꿀 수가 있다. 그리고 'X' 그리고 '=' 등의 문자들을 CONCAT() 함수를 통해서 서로 이은 것이다.

그러면 다음과 같이 결과가 출력되는 것을 볼 수가 있다.

암시적 형변환

SELECT '100' + '200' ; -- 문자와 문자를 더함 (정수로 변환되서 연산됨)

결과 값으로 문자열이 정수형으로 암시적으로 형변환 되고 + 연산자를 통해서 300이라는 연산된 결과값이 출력이 된다.

문자를 직접 잇고 싶다. 그러면 CONCAT()으로 처리하면 된다.

CONCAT(100, '200'); 은 정수와 문자를 연결하는 함수이기 때문에 정수가 문자로 변환되서 처리가 된다.

SELECT 1 > '2mega';  

정수인 2로 변환되어서 비교가 된다.

1 > 2 의 결과값으로 0이 출력이 된다.

정수인 2로 변환되어서 비교가 된다.

결과값은 3 > 2 인 1이 출력된다.

문자는 0으로 변환된다.

0 = 0의 결과값으로 1이 출력된다.

감사합니다.

https://www.youtube.com/watch?v=1YmWy-7-OhQ&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=10

안녕하세요, 혼자 공부하는 SQL를 보고 학습한 자료를 남깁니다.

데이터 변경을 위한 SQL문

테이블의 내용을 변경하는 구문이다.

손코딩 실습

먼저, USE market_db; 를 실행한다.

■ CREATE문 및 INSERT문

1) CREATE TABLE hongong1 VALUES (toy_id INT, toy_name CHAR(4), age INT);

hongong1 테이블을 다음과 같은 열들을 갖게 해서 생성한다.

2) INSERT INTO hongong1 VALUES (1, '우디', 25);

hongong1 테이블에 다음과 같은 쿼리문을 수행해서 데이터를 입력할 수 있다.

3) INSERT INTO hongong1(toy_id, toy_name) VALUES (2, '버즈');

만일 나이는 입력하지 않고 싶다. 그러면 컬럼명을 직접 입력하고 값을 입력할 수 있다.

4) INSERT INTO hongong1 (toy_id, age, toy_name) VALUES ('제시', 20, 3);

컬럼명을 직접 입력해서 데이터를 입력하면 임의대로 값의 순서를 변경해서 맞춰서 데이터를 입력할 수 있다.

■  AUTO_INCREMENT

AUTO_INCREMENT는 반드시 PRIMARY KEY로 지정을 해주어야 한다.

AUTO_INCREMENT 를 많이 사용을 한다.

NULL 값을 id값으로 입력을 해도 자동으로 증가하는 값을 입력해준다.

■ SELECT LAST_INSERT_ID();

마지막 AUTO_INCREMENT된 값을 확인할 수 있다.

■ AUTO_INCREMENT=${VALUE};

ALTER TABLE hongong2 AUTO_INCREMENT=100; 

을 입력을 하면 테이블의 AUTO_INCREMENT 값이 변경된 것을 확인할 수 있다.

값을 입력을 하고 조회를 해보면 

다음과 같이 toy_id값에 100이 입력된 것을 볼 수 있다.

hongong3이라는 테이블을 새로 만들고

toy_id 값을 AUTO_INCREMENT로 해준다.

ALTER TABLE로 AUTO_INCREMENT 값을 1000으로 입력을 해주면

1000부터 입력이 된다.

만일 3씩 건너뛰고 싶다면

SET @@auto_increment_increment=3;

을 입력해주면 된다.

다시 이전에 만들었던 hongong2 테이블에 데이터를 입력해 보면 

SET @@auto_increment_increment=3;이 적용되어서

103, 106, 109 이렇게 ID값이 자동으로 입력되는 것을 볼 수 있다.

■ INSERT INTO ~ SELECT

INSERT문을 수행할 때 SELECT문을 사용해서도 INSERT문이 실행될 수 있다.

대량의 데이터를 빠르게 SELECT문을 이용하여 INSERT문으로 데이터를 입력할 수 있다.

world 데이터베이스의 city의 수를 조회하면 다음과 같다.

4079개의 도시의 수를 전부 다 조회할 수 있다.

■ DESC world.city;

DESC를 사용하면 해당 테이블의 정보를 조회할 수 있다.

모두 조회를 하면 검색 속도가 오래 걸리니

LIMIT을 사용해서 데이터를 조회한다.

우리는 world의 city테이블의 정보를 가져오고 싶은 것이다.

먼저 새 테이블을 생성해 볼 것이다.

city_popul 이라는 테이블을 만든다.

그리고 INSERT INTO ~ SELECT문을 사용해서 

약 4,000건이 넘는 데이터를, world.city 테이블의 데이터를 city_popul 테이블에 저장한다.

SELECT * FROM city_popul 을 드래그 해서 번개 모양 버튼을 클릭 해서 실행을 하면 다음과 같은 결과를 얻어낼 수 있다.

■ UPDATE 문의 기본 문법

열 값의 순서를 마음대로 입력해도 무방하다.

city_popul 의 각 데이터들을 한글로 바꾸고 싶다.

그러면 먼저 city_name = 'Seoul' 인 값을 조회한다.

이 값을 한글로 바꿀 것이다.

그러기위해서 UPDATE문을 사용한다.

하지만 UPDATE문을 사용하는데 오류가 발생하는 것을 볼 수 있다.

Work bench에서 UPDATE문이 실행이 안 되도록 설정이 되어 있는 것이다.

다음과 같이 하여 이러한 오류를 해결할 수 있다.

Preferences > SQL Editor > Safe updates (rejects UPDATEs and DELETEs with no restrictions)

를 해제하고 OK 버튼을 클릭한다.

그리고 Work bench를 끄고 다시 실행한다.

USE market_db; 를 다시 실행하여 사용할 데이터베이스를 market_db로 설정해준다.

그리고 UPDATE문을 실행하여 city_name = '서울' 로 변경해준다.

그리고 SELECT문을 실행을 하면 다음과 같이 조회가 정상적으로 되는 것을 볼 수가 있다.

다음은 뉴욕을 한글로 바꾸어 보겠다.

UPDATE문을 실행을 하고 다시 SELECT문을 실행을 해보면

다음과 같이 city_name과 population이 변경된 것을 볼 수가 있다.

UPDATE문에 WHERE절을 빠뜨린 경우

절대로 실행하면 안 됩니다.

인구수를 너무 복잡하니 10,000 단위로 바꾸고 싶다.

다음과 같이 인구 단위가 10,000단위로 바뀐 것을 볼 수가 있다.

DELETE문

DELETE 문을 실행을 해서 city_name의 앞 글자가 'New'로 시작하는 행들을 삭제해보겠습니다.

그러면 총 11건이 실행된 것을 볼 수가 있습니다.

DELETE문에 LIMIT 문이 추가되어서 city_name이 앞에서 'New'글자로 시작하는 city_popul 테이블의 행을 삭제할 수 있다.

LIMIT문이 추가되었으므로 앞에서 5건만 삭제가 되고 남은 6건은 남아야 한다.

앞에서 이미 총 11건을 모두 삭제했으므로

이번에는 총 0건이 삭제됨을 확인할 수 있다.

감사합니다.

https://www.youtube.com/watch?v=WWAFAm9op2U&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=9