[SQL] SQL로 하는 데이터 분석(1)_코드잇

[목차]

- 데이터베이스 기본 개념

- 테이블 생성하기

 

데이터베이스 기본 개념

1. 데이터베이스 기본 개념

- 데이터베이스 : 일정한 체계 속에 저장된 데이터의 집합

- 테이블 : 표 형식으로 저장된 데이터의 집합 → 테이블의 행은 레코드(record), 열은 필드(field)라고도 부름

- row : 하나의 개체

- column : 개체가 갖는 속성

 

- DBMS(database management system): 데이터베이스 관리시스템 

  ㄴ 종류 : MYSQL, ORACLE, SQL Server 등

- 모든 DBMS는 SQL로 명령 : 주요 기능은 국제 표준 SQL

- SQL(Structured Query Language): 구조적 질의 언어로, DMBS를 다루기 위해 사용하는 언어

1) client(클라이언트 프로그램) : 사용자가 server에 접속해서 원하는 데이터베이스 관련 작업을 할 수 있도록, SQL을 입력할 수 있는 화면 등을 제공하는 프로그램

2) server(서버 프로그램) : client로부터 SQL문 등을 전달받아 데이터베이스 관련 작업을 직접 처리하는 프로그램

→ DBMS를 사용한다는 것은 실행되고 있는 server에 client를 이용해서 접속한 후 원하는 명령을 내린다는 뜻

 

----------------------------------------------

테이블 생성하기

데이터베이스 생성

CREATE DATABASE <데이터베이스 이름> 

 

테이블 생성 2가지 방법 

1. SQL문으로 생성

2. CSV 파일을 임포트해서 테이블 생성

   ㄴ CSV( Comma Separated Values): 모든 값들이 콤마(,)로 구분된 형식

 

[Datatype]

- INT(integer) : 정수 

- DOUBLE : 실수형 데이터 타입

- Date : 날짜형

- TEXT : 문자열 

 

[Primary Key 설정하기]

- Primary Key(기본키) : 테이블에서 하나의 row를 고유하게 식별할 수 있도록 해주는 column

  → Primary Key에 같은 값이 있는 row가 추가되는 것을 DBMS가 자동으로 막아 중복된 row 생길 위험성 제거

 

1. Natural Key

: 실제로 어떤 개체가 갖고 있는 속성을 나타내는 칼럼이 기본키가 됐을 때 → 사람은 주민등록번호로 특정 인물 식별  →  memer 테이블에서는 email 칼럼이 각 회원을 식별할 수 있는 실제 속성 

 

2. Surrogate Key

: id 칼럼같은 기본키를 의미 → id 칼럼은 어떤 회원의 속성을 직접 나타내는 칼럼은 아니며, 인위적으로 생성한 칼럼: 보통 1부터 시작해서 1씩 증가하는 정수값 - AI(Auto Increment) : 새로운 row가 추가될 때마다 id 칼럼에 이전보다 1이 더 큰 정수를 자동으로 넣어주는 기능

 

▶ 각 상황마다 적절한 키는 달라지며, Natural Key는 그 값이 나중에 변경되면 모든 row의 값을 다시 수정해야 한다는 문제 발생     → 보통은  Surrogate Key를 선택하는 경우가 많음  

 

- Not Null : 값이 존재하지 않는 상태 → Null은 0과 비어 있는 문자열과는 다르다

 → Primary Key은 Not Null이어야 함

 

[코드 힌트 대문자 설정]

- 설정하지 않을 경우, 예약어의 코드 힌트는 소문자

- SQL 예약어는 대문자 작성으로 권장

ㄴ Edit > Preferences > 빨간 박스 내역 체크

 

 

[Workbench UI]

 

1. 쿼리 창 생성 아이콘 : 신규 SQL 에디터 탭 생성

2. 쿼리 창 탭 : 영역에 존재하는 탭 개수 증가

3. 쿼리 창 : SQL 문 입력 공간

4. SQL 문 실행 아이콘 : 단축키 - 시프트+컨트롤+엔터  

5. 새로고침 아이콘 

6. 테이블 조회 아이콘

- 좌측 스패너 모양 : 칼럼과 칼럼의 데이터 타입 확인 가능

- 우측 표 모양 : 테이블 전체 row 조회 가능

7. Result Grid 영역 : 조회 결과 출력

8. Action Output 영역 : 실행 및 작동 정보 결과 영역 

 

[SQL 작성 형식]

1. SQL 문 끝에는 항상 세미콜론

- SQL 문법 상 세미콜론이 하나의 SQL 문 종결 단위

 

2. SQL 문 안에는 공백이나 개행 등 자유롭게 넣을 수 있음

- 구분되어야할 키워드들이 최소한 하나 이상의 공백, 세미콜론으로 마무리 > 실행에 이상 없음 

 

3. SQL 문의 대소문자 구분 문자

- 기본으로 내장된 키워드(예약어)는 대문자 > 가독성을 위해 예약어를 대문자로 적는 것이 관례 

- 나머지 부분(데이터베이스 이름, 칼럼 이름 등)은 소문자

 

4. 데이터베이스 이름과 테이블 이름

- 데이터베이스 이름 뒤에 점(.)을 붙이고 그 다음에 테이블 이름 기재

  ㄴ 실무에서 서로 다른 데이터베이스에 같은 이름의 테이블이 존재할 수 있기에 구분하여 기재

- SCHEMAS 패널 부분의 copang_main을 클릭하여 활성화 >> 해당 데이터베이스 사용 지정

  ㄴ 이런 케이스의 경우, 그냥 member라고 적어도 가능

- USE copang_main; 은 copang_main이라는 데이터베이스를 사용하겠다고 선언 하는 방식

 

[데이터 조회의 핵심 구문]

- SELECT, WHERE

 

[조건을 나타내는 다양한 방법]

1. 같지 않음(!=,<>)

- memver 테이블 회원들 중 남자가 아닌 회원 조회

1) !=

 

2) <>

 

2. 이 중에 있는~ (IN) 

- 연속된 범위 : BETWEEN a AND b (a부터 b까지)

- 여러 값들 중 해당하는 값 추리기 : IN

 

3. 한 글자를 나타내는_

- LIKE 뒤 %는 임의의 길이를 가진 문자열(0자도 포함)

- LIKE 뒤 언더바(_) 하나는 문자 하나 

  ㄴ EX) member 테이블에서 이메일주소가 c로 시작하고, 뒤에 여섯 글자가 있는 row 조회

 

 

[DATE 타입의 값을 다루는 대표적 함수]

1. 연도, 월, 일 추출하기

- DATE 타입의 값에서 연도(year)만, 혹은 월(month)만, 혹은 일(day) 추출 가능

 

(1) 1992년에 태어난 회원들만 조회하기

 

(2) 여름(6,7,8월)에 가입한 회원들만 조회하기

- MONTH() 함수 사용 → 날짜 값에서 월 뽑아내기 가능 

 

(3) 각 달의 후반부(15일~31일)에 가입했던 회원들만 조회하기

- DAYOFMONTH() 함수는 날짜값에서 일만 뽑아내기 

 

2. 날짜 간의 차이 구하기

- DATEDIFF() : DATE > 날짜 / DIFF > Difference 줄임말 → 차이

  ㄴ DATEDIFF(날짜 a, 날짜 b) : 날짜 a-날짜 b 차이 일수 → DATEDIFF('2018-01-05', '2018-01-03') >> 값 2

 

- CURDATE() : 오늘 날짜 구하기 

 

- 회원들이 몇 살일 때 가입했는지 확인 가능

 

3. 날짜 더하기 빼기 

- DATE_ADD() : 더하기 함수 

- DATE_SUB() : 빼는 함수

 

- 가입일 기준으로 300일 이후의 날짜 구하기

 

- 가입일 기준 250일 이전의 날짜 구하기 

 

4. UNIX Timestamp 값 

- DATETIME : 날짜 + 시간 포함 '2018-12-31 23:54:59'

- UNIX Timestamp : 특정 날짜의 특정 시간을, 1970년 1월 1일 기준으로, 총 몇 초가 지났는지 나타낸 값

  ㄴ FROM_UNIXTIME() : 읽을 수 있는 날짜 형태로 변경

* UNIX Timestamp : 유닉스 계열의 운영체제에서 시간을 나타내기 위해 주로 사용되는 DB 뿐 아니라, 그 자체로 IT분야에서 중요 개

 

※ 참고 자료

- 날짜, 시간 관련 데이터 타입(링크)

- 날짜, 시간 관련 함수(링크)

 

[여러 개의 조건 걸기]

 

1. OR를 사용할 때 주의사항

- 주의할 점 : 옳은 방식( WHERE id = 1 OR id = 2)

  ㄴ 잘 못된 방식 (WHERE id = 1 OR 2)

  ㄴ 1) id=1이 TRUE 2) 2 >> WHERE id = 1 OR TRUE : MySQL에서는 0을 False, 0이외의 숫자는 True로 간주 >> 모든 조건 만족하는 상황

 

2. AND와 OR 간의 우선순위

- AND가 OR 보다 우선순위가 높음 >> AND가 OR보다 먼저 실행

▶ 먼저 실행되기를 원하는 조건에 괄호 표기