기술노트 블로그

안녕하세요, 혼자 공부하는 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을 보고 학습한 자료를 남깁니다.

SQL 프로그래밍

SQL은 C, 자바, 파이썬과 같은 프로그래밍 언어처럼 코딩이 가능하다.

스토더으 프로시저

위와 같은 형식을 통해서 SQL로 프로그래밍을 할 수 있다.

IF문

IF문은 조건식이 참이면 실행하고 거짓이면 실행하지 않는 것이다.

위와 같은 형식으로 사용할 수 있다.

SQL 문장이 여러 개이기 때문에 BEGIN과 END로 묶어준다.

손코딩 실습

IF 100 = 100 THEN

    SELECT '100은 100과 같습니다.'

END IF;

100 = 100 조건절이 참이므로 

SELECT '100은 100과 같습니다.' 문이 실행되어

100은 100과 같습니다.라는 다음의 결과가 출력된다.

하지만 위의 예제에서 IF문의 조건절이 100 = 200 이므로 거짓이므로 

IF문 내의 쿼리를 실행하지 않고

곧 바로 종료된다.

IF~ELSE문

IF~ELSE문의 경우 참이면 실행하고, 거짓이어도 실행할 문장이 따로 있다.

DECLARE문을 통해서 변수를 선언한다.

SET 명령어를 통해서 선언한 변수에 값을 200 할당한다.

조건절이 거짓이므로 ELSE문의 쿼리가 실행된다.

CALL 명령어로 프로시저를 실행하였으므로 프로시저가 실행된다.

결과 값으로 '100이 아닙니다.'가 출력된다.

SELECT INTO 명령문을 통해서 debutDate에 조회된 결과를 대입한다.

CURRENT_DATE() 함수를 통해서 curDATE에 값을 대입한다.

DATEDIFF(curDATE, debutDate); 함수를 통해서 날짜의 차이를 계산한다.

IF문을 통해서 5년이 지났는지 안 지났는지를 확인한다.

참이면 참인 결과 값을 실행하고 거짓이라면 거짓인 결과를 실행한다.

프로시저 선언을 종료한다.

CALL 명령문을 통해서 프로시저를 호출한다.

결과값이 출력된 것을 확인할 수 있다.

CURRENT_DATE() 함수와 DATEDIFF()함수를 사용해보았다.

CASE 문

CASE 문을 통해서 여러 조건절을 통해 값을 분류해서 결과값을 조회한 것을 볼 수 있다.

고객을 나누어서 혜택을 다르게 주려고 한다.

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

조인한 결과에서 GROUP BY를 통해 결과를 조회했다.

하지만 INNER JOIN을 해서 유령 고객은 조회가 되지 않는다.

그래서 RIGHT OUT JOIN을 해서 

유령고객까지 전부 다 조회를 했다.

CASE문을 활용을 해서 회원의 등급을 조회했다.

WHILE문

조건식이 참인 동안에 SQL 문장들이 계속 반복이 되요.

WHILE문 응용

4의 배수를 제외한 1~1000까지의 합의 결과를 조회하였다.

ITERATE와 LEAVE를 통해서 4의 배수라면 ITERATE를 통해서 다음 반복문으로 넘어가고 i가 1000이 넘어가면 WHILE 반복문을 종료한다.

그리고 합한 결과를 조회하는 쿼리문을 실행하도록 한다.

CALL 명령문을 통해서 whileProc2()를 호출한다.

동적 SQL

SQL문을 동적으로 즉, 실시간으로 변형시켜서 사용할 수있다는 것이다.

실행 후에는 DEALLOCATE PREPARE로 문장을 해제해주는 것이 바람직하다.

위의 드래그한 쿼리문을 3번 실행을 하면

SQL문이 동적으로 실행이 되서 

출입한 시간이 조회가 된다.

감사합니다.

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

안녕하세요, 혼자 공부하는 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

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

ORDER BY 절

SELECT문의 차례는 꼭 지켜야 합니다.

ORDER BY 절은 결과를 바꾸진 않는다. 다만 차례를 바꾼다.

작업할 때 이렇게 전체 데이터베이스 구성도를 펼쳐 놓고 프로그래밍을 하는 것이 좋다.

드래그한 부분 즉 ORDER BY 절은 제외하고 한 번 쿼리를 실행을 해보자.

Work bench를 끄고 다시 실행을 하면 USE 데이터베이스가 지워집니다. 그래서 다시 USE 문을 실행을 해주어야 합니다.

ORDER BY를 사용을 하면 자동으로 오름차순을 사용을 한다.

DESC를 사용을 하면 내림차순을 사용을 한다.

ASC는 보통 생략된다.

만일 SELECT문 순서가 다르면 오류가 발생한다.

잇지와 트와이스는 키가 같다. 이렇게 동률일 때는 ORDER BY에 컬럼을 더 추가해서 추가한 컬럼으로 순서를 더 나눌 수 있다.

그래서 만일 키가 같을 시 데뷔 일자로 순서를 정렬하는 쿼리문을 만들 수 있다.

LIMIT을 사용을 하면 3개의 행을 잘라서 보여준다.

그래서 조회한 결과에서 위 3개의 행만 자르고 나머지는 안 보여준다.

WHERE 절은 생략은 가능하지만, 순서가 변경되는 것은 안 된다.

데뷔 일자 오름차순으로 정렬하여 조회한 결과를 LIMIT 명령어를 통해서 3개의 행만을 잘라서 조회한다.

LIMIT은 반드시 ORDER BY절 다음에 나와야 한다.

LIMIT에도 옵션이 있다. 

예를 들어 LIMIT 3,2를 하면

3번째 초과부터 2개를 조회한다.

예를 들어 회원이 사는 지역을 알고 싶다.

컬럼에 addr 만을 조회를 하면 여러 지역들이 나오게 된다.

그러면 DISTINCT문을 사용하면 굉장히 편리하다.

중복된 컬럼을 다 제거하고 하나씩만 보여준다.

회원들은 딱 5개의 지역에서만 살고 있다는 것을 알 수 있게 된다.

GROUP BY절

GROUP BY는 그룹으로 서로를 묶어주는 것이 바로 GROUP BY 에요.

회원들의 buy 테이블을 알아보려고 한다.

각각 회원이 몇개 구매를 했는지 알고 싶어해요.

GROUP으로 묶어서 합계를 구하고 싶다.

그러면 집계함수를 쓰면 된다.

SUM()함수를 사용을 하면 합계를 구할 수 있다.

mem_id별로 그룹을 묶어가지고 합계를 내라.

mem_id별로 묶어서 SUM() 합계를 내라의 의미이다.

이렇게는 우리가 직접 합칠 수가 없다. 그래서 GROUP BY절 및 집계 함수를 사용한다.

회원 아이디 및 총 구매 개수의 별칭을 주어서 조금 더 깔끔하게 결과를 조회할 수 있다.

총 구매 금액이 궁금할 수 있다.

이 경우 price * amount 의 SUM을 구하면 된다.

회원이 한 번 올때 마다 몇개 씩 구매하는지 평균을 구하고 싶다.

그러면 AVG()함수를 사용을 하면 된다.

그러면 각 mem_id별 평균 구매 개수를 구할 수가 있다.

COUNT() 함수를 사용을 하면 총 갯수를 구할 수가 있다.

COUNT(*)를 하면 *은 모든 행을 의미하기 때문에 모든 행의 갯수를 파악한다.

예를 들어, 만일 핸드폰이 있는 회원만 조회를 하고 싶다면 COUNT(phone1) 을 사용해서 조회를 하면 된다.

COUNT() 함수는 비어있는 행은 조회를 하지 않는다.

별칭을 사용해서 이쁘게 결과를 출력했다.

mem_id별 총 구매 금액이 1000 초과인 그룹을 구하려고 한다.

하지만 WHERE SUM(price*amount) > 1000 을 사용하면 오류가 발생하는 것을 확인할 수 있다.

그래서 HAVING절을 써야 한다.

그룹함수를 조건절로 사용하고 싶다면 

HAVING절을 사용해라

그러면 결과가 정상적으로 조회가 된 것을 볼 수 있다.

만일 결과를 ORDER BY를 하고 싶다면 ORDER BY 절에 집계 함수를 추가할 수 있다.

그러면 결과가 차례가 정렬되어서 조회된 것을 볼 수 있다.

감사합니다.

https://www.youtube.com/watch?v=6qkPy7RfLqQ&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=8

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

SELECT문

SELECT문은 SELECT~FROM~WHERE 절로 사용되는 것이 가장 보편화된 방법이다.

무엇을 선택하라는 이야기잖아요.

테이블들을 가져와서 조회를 하는 것이 바로 SELECT문이다.

테이블들을 변경을 하는 것은 아니다.

읽기만 한다.

이 그림을 갖고 같이 공부를 해볼 것이다.

회원은 10건 그리고 구매는 12건이 있다.

샘플 데이터베이스가 MySQL에도 있고, 다른 데이터베이스도 샘플 데이터베이스를 제공을 해주는데 문제는 영문으로 제공을 해준다. 그래서 초보자 분들께서 사용을 하실 때 감이 안오는 경우가 많다.

인터넷 쇼핑몰이라고 보시면 되겠다.

인터넷 쇼핑몰을 가정해서 회원을 관리한다.

회원이 10명 있다. 이 회원이 물건을 구매한게 12건 있다.

이렇게 두 가지 테이블을 가지고 실습을 진행을 하겠다.

market_db 라고 데이터베이스 이름을 줄 것이다.

member 그리고 buy 이 두 가지 테이블이 있다.

회원 테이블과 구매 테이블은 1:N의 관계이다.

실습

File > Open SQL Script 를 클릭하면 

다음과 같이 SQL Script를 열 수 있다.

어떠한 SQL문도 드래그를 하지 않고 위의 번개 표시를 클릭을 하면

SQL Script가 실행된 것을 볼 수 있다.

만일 데이터에 문제가 생긴다면

다시 이와 같은 스크립트 파일을 실행을 하면 

DB를 DROP하고 다시 데이터 베이스를 만들고 DB를 적용하고 테이블들을 만들고, 데이터를 입력하고 마지막으로 SELECT문으로 테스트까지 한다.

_를 사용을 해서 컬럼명을 알아보기 쉽게 만든다.

-- 를 통해서 주석을 달아 각 컬럼에 대한 설명을 적을 수 있다.

핸드폰 번호의 경우 숫자이지만 계산을 하거나 하는 용도가 아니면 010처럼 숫자와 다른 형식도 들어가므로 문자열로 타입을 설정한다.

키는 SMALLINT로 작은 정수로 데이터 타입을 명시한다.

전화번호가 없는 회원이 있을 수 있다.

그래서 스크립트를 실행을 하면 항상 초기화가 된다.

다운로드를 하여서 저장해 놓는다.

SCHEMAS 창에서 우클릭을 하고 'Refresh All' 버튼을 클릭을 하면

SCHEMAS 창이 새로고침이 된다. 

그러면 market_db가 생성된 것을 볼 수가 있다.

모든 쿼리문의 끝은 ;(세미콜론)으로 끝난다.

*를 통해서 member 테이블의 모든 열들을 조회할 수 있다.

SELECT문을 실행을 하면 result창에 결과가 조회된다.

조회된 결과는 목록으로 나오며, 차례가 다를 수 있다.

알파벳 순으로 정렬이 된 것이다.

USE를 잘못 사용을 하면 다른 데이터베이스를 사용하게 될 수 있다.

USE문을 사용하면 다시 USE문을 사용할 필요는 없다.

하지만 USE문을 여러번 사용을 했다고 해서 문제가 생기는 것은 아니다. 그냥 다시 실행될 뿐이다.

실제로 데이터가 정말 많다면, WHERE 조건문을 사용해서 일부 내용만 파악을 해야 한다.

주로 SELECT문은 WHERE 조건절과 같이 세트로 사용이 된다.

이렇게 Enter를 쳐서 절을 나누어도 무방하다.

테이블은 market_db 데이터베이스 안에 있는 테이블을 실질적으로 조회를 하는 것이다.

따라서 위의 USE market_db문이 없더라도 market_db 데이터베이스 안에 있는 테이블을 조회가 가능하다.

예제에서는 market_db를 USE문을 사용하였으므로 SELECT문으로 조회할 시 market_db를 테이블 명 앞에 명시해줄 필요는 없다.

USE sys; 문을 사용을 하였다.

market_db를 테이블 명 앞에 명시를 해주었으므로 SELECT문을 조회를 하면 다시 market_db 안에 있는 member 테이블에서 데이터가 조회가 된다.

하지만, 여러 데이터베이스를 오가면서 데이터를 조회할 일은 거의 없다.

만일 오류가 발생하면 Output창에 조회가 발생한다.

초록색은 정상적으로 쿼리가 실행된 것이고,

만일 오류가 발생을 하면

이와 같이 빨간색으로 오류가 발생한 것을 볼 수 있다.

sys 데이터베이스에 member 테이블이 없다는 의미이다.

특정 테이블 컬럼을 조회해야 할 때는 먼저 SELECT * 구문을 사용하여 모든 테이블을 result 창에서 조회를 한 다음

필요한 컬럼만 다시 조회해서 SELECT문을 만들면 된다.

이렇게 특정 컬럼만을 하여 조회를 한 것을 볼 수 있다.

하지만 컬럼명이 영어로 되어있기 때문에 우리는 어떤 컬럼에서 정보를 가지고 왔는지 그리고 어떤 데이터를 가지고 왔는지 파악하기가 한눈에 어려울 수 있다.

이런 경우 Alias를 사용한다.

키 처럼 띄어쓰기가 없는 경우 Alias를 그냥 입력해도 되고,

데뷔 일자 처럼 띄어쓰기가 있는 경우 ""(쌍따옴표)로 묶어주어야 한다.

result에 조회된 결과는 컬럼명이 실제로 한글로 표기가 되었지만, 이는 참조일 뿐이며, height 또는 debut_date 컬럼명으로 결과가 조회된 것이다.

별명을 줄 수 있다.

다음과 같이 여러 WHERE 절을 이용한 구문들이 가능하다.

WHERE height <= 162 는 회원 테이블에서 평균 키가 162 이하인 그룹들을 조회하는 것이다.

만일 WHERE height >= 165 AND mem_number > 6 라면 두 조건을 모두 만족하는 그룹들이 조회가 될 것이다.

WHERE height >= 165 OR mem_number > 6 이라면 두 조건 중 하나라도 만족을 하는 그룹들이 조회가 될 것이다.

WHERE height >= 163 AND height <= 165 라면 평균키가 163이상이며 165이하인 그룹을 결과로 조회한다.

이는 WHERE height BETWEEN 163 AND 165와 완전히 일치하다.

WHERE addr = '경기' OR addr = '전남' OR addr = '경남' 은 AND로 엮는 것이 말이 안된다. 왜냐하면 한 멤버가 주소가 경기에서 살거나 전남에서 살거나 경남에서 사는 것은 말이 안되기 때문이다. 그래서 OR로 묶어주어야 경기에서 사는 멤버, 전남에서 사는 멤버 그리고 경남에서 사는 멤버가 모두 조회가 된다.

WHERE addr IN('경기', '전남', '경남'); 숫자가 아니라 문자 같이 띄어져 있는 것 떨어져 있는 것은 IN을 사용하면 됩니다.

LIKE의 경우 문자를 비교하는 것이다.

WHERE mem_name LIKE '우%' 자가 들어간다.

'우' 자가 들어가고 뒤에는 무엇이든지 관계가 없어요. =(이퀄)을 사용을 하면 결과가 조회가 안된다. 

한 개의 글자는 _(언더바)이다.

감사합니다.

https://www.youtube.com/watch?v=_JURyg_KzHE&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=7

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

데이터베이스 개체

뷰, 인덱스, 스토어드 프로시저, 트리거 등 데이터베이스 내에 있을 수 있는 오브젝트이다.

테이블이 대표적인 데이터베이스의 개체이다.

테이블을 돌리기 위해 나머지 필요한 개체들이 있다.

이 내용 하나 하나가 공부할 것이 있다.

인덱스

데이터를 조회할 때 데이터가 빠르게 나올 수 있도록 만드는 것이다.

실무라면 데이터가 몇백만건, 몇천만건 되면 컴퓨터가 느려지게 된다.

책의 찾아보기 개념과 거의 동일한 개념이다.

폭포수 모델이라는 글자를 찾아보라고 하면 1페이지부터 다 찾아봐야 한다.

그런데 찾아보기로 ㅍ을 찾으면

1페이지를 다 뒤지는 것 보다 두 세번 만에 찾을 수가 있다.

이것이 바로 인덱스의 도움 때문이다.

인덱스는 없어도 관계는 없다.

책 내용이 바뀌는 것은 아니다.

빨리 찾을 수 있는 도움을 주는 것이지 실무에서는 대용량의 데이터를 사용할 때는 너무 오래 걸려요.

실무에서는 인덱스를 반드시 사용한다.

SELECT * FROM member WHERE member_name = '아이유'; 를 실행을 한다.

result의 우측에 밑의 화살표를 클릭을 하면 여러 탭들을 볼 수가 있다.

그 중 Execution Plan을 클릭을 하면 다음과 같이 실행계획을 볼 수 있다.

빨간색으로 Full Table Scan 즉 전체 테이블을 뒤져서 아이유를 찾아냈다.

이것 사실 무지하게 오래 걸리는 일이다.

처음에 Index는 없다.

Index를 만들어준 다음에 진행을 해야 한다.

CREATE INDEX idx_member_name ON member(member_name); 명령어를 통해서 실행을 하면 눈에 보이는 결과는 없지만 

맨 아래 Output 창에 초록색 체크 표시로 인덱스 생성 쿼리문이 완료된 것을 볼 수가 있다.

이렇게 찾아보기를 만든 것이다.

다시 SELECT문을 실행을 하면

다음과 같이 Execution Plan(실행 계획)이 조금 바뀐 것을 볼 수가 있다.

원래 Full Table Scan인데

지금은 Non-Unique Key Lookup 인데 이를 인덱스를 사용해서 아이유를 찾아냈다는 의미이다.

엄청나게 빠른 결과이다.

진짜 데이터 건수가 많더라도 진짜 빠른 결과를 낼 수 있다.

뷰

뷰는 가상의 테이블이다. 진짜 데이터가 없다.

뷰는 마치 윈도우에 바로가기와 비슷한 개념이다.

바로가기 프로그램인 Microsoft Edge를 보면 다음과 같이 대상의 위치가 나온다.

이 디렉터리의 실행파일이 실제로 실행되는 것이다.

뷰 또한 비슷하다.

뷰도 내가 뷰를 실행한 것 처럼, 테이블을 실행한 것 처럼 착각하지만

실제로는 뷰에서 테이블을 가져와서 보여주는 것이다.

뷰가 아니라 테이블에 접근한 것과 동일한 효과를 내는 것이다.

뷰는 테이블을 SELECT하는 구문이다.

뷰에 SELECT가 작동을 해서 

뷰의 SELECT가 작동을 해서

다시 테이블에 접근을 해서 데이터를 가져와서 실행이 되는 것이다.

뷰를 직접 만들어보겠다.

SQL+ 버튼을 클릭을 하면 새 쿼리 창을 만들 수 있다.

CREATE VIEW 하고 뷰 이름을 지어줍니다. member_view

그리고 그 밑에 AS 하고

SELECT문을 입력을 하면

VIEW는 실제 SELECT문이에요

Output창의 #10번의 result를 보면 초록색 표시로 체크된 것을 볼 수 있다. Action과 Message 그리고 Duration/Fetch 창을 볼 수 있다. 실행된 쿼리문, 이에 대한 메시지 그리고 처리 시간 / Fetch 등을 확인해 볼 수 있다.

뷰 생성문이 완료된 것이다.

뷰를 다시 실행을 하면

데이터에 있는 SELECT문이 결과로 나오는 것을 볼 수가 있다.

스토어드 프로시저

SQL문은 프로그래밍 언어와는 조금 다르다.

하지만 이를 가능하게 해주는 것이 바로 스토어드 프로시저이다.

예를 들어 IF문 또는 반복문등을 SQL문으로 만들 수 있도록 도와주는 것이 바로 스토어드 프로시저이다.

만일 쿼리 탭을 닫을 때 실행도중 이와 같은 창이 나온다면 

저장하지 않을 것이므로 Don't Save 버튼을 클릭하면 된다.

만일 두 개의 쿼리문을 동시에 아주 자주 많이 사용한다고 가정을 해보자.

자주 까먹을 수가 있다. 그래서 이를 스토어드 프로시저를 만들어서 사용할 수 있다.

DELIMITER //과 DELIMITER; 는 두 명령문 사이의 쿼리문을 묶어주는 역할을 수행 한다.

다음 화면은 예약어로 이루어진 프로시저의 예시이다.

이름은 myProc이다. 

이를 실행을 하면 다음과 같은 Output 창에서 프로시저 생성문이 완료된 것을 볼 수 있다.

CALL 예약어를 통해서 프로시저를 실행할 수 있다.

실행을 하면 다음과 같이 두 개의 쿼리문이 실행된 것을 볼 수 있다.

탭이 두개이다. (Result3, Result4)

CALL만 갖고도 이 두개의 쿼리문을 실행한 것이다.

저장 프로시저가 프로그래밍 용어에 들어가서 사용할 수 있는 일이 많다.

감사합니다.

https://www.youtube.com/watch?v=lBk5YhLZevs&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=6