9주차

수강한 분량

SQL, SQL 고급 내용을 공부했다. MySQL, AWS 등을 익혔다.

각 강의별 학습한 핵심 내용 정리

DataBase 설치하기

• MySQL 설치
• server
• workbench
  - server에 접속해 명령을 던지는 프로그램
• shell
• router
• connector

DataBase

• DataBase
  - 여러 사람이 공유하여 사용할 목적으로 체계화해 통합, 관리하는 데이터의 집합체
• DBMS
  - 사용자와 데이터베이스 사이에서 사용자의 요구에 따라 정보를 생성해주고 데이터베이스를 관리해주는 소프트웨어
• RDB, Relational Database
  - 서로간에 관계가 있는 데이터 테이블들을 모아둔 데이터 저장공간
• SQL
  - 데이터베이스에서 데이터를 정의, 조작, 제어하기 위해 사용하는 언어
  - DDL, Data Definition Language
  - CREATE, ALTER, DROP
  - DML, Data Manipulation Language
  - INSERT, UPDATE, DELETE, SELECT
  - DCL, Data Control Language
  - GRANT, REVOKE, COMMIT, ROLLBACK
• root 계정으로 접속
  - % mysql -u root -p
• 현재 DB 목록 확인
  - SHOW DATABASES;
• DB 이름 지정하여 생성
  - CREATE DATABASE dbname;
• DB로 이동
  - USE dbname;
• DB 삭제
  - DROP DATABASE dbname;

User

• 사용자 정보는 mysql에서 관리하므로 mysql DB로 이동 후 조회
  - use mysql;
  - SELECT host, user FROM user;
• User 생성
  - 현재 PC에서만 접속 가능한 사용자를 비번과 함께 생성
  - CREATE USER 'username'@'localhost' identified by 'password';
  - 외부에서 접속 가능한 사용자를 비번과 함께 생성
  - CREATE USER 'username'@'%' identified by 'password';
• User 삭제
  - 접근 범위에 따라 이름이 같은 사용자여도 별도로 삭제
  - DROP USER 'username'@'localhost'
  - DROP USER 'username'@'%'
• User 권한 관리
  - 권한 확인
  - SHOW GRANTS FOR 'username'@'localhost'
  - 권한 부여
  - GRANT ALL ON dbname.* to 'username'@'localhost'; - 권한 삭제 - REVOKE ALL ON dbname.* from 'username'@'localhost';`
• 새로고침
  - FLUSH PRIVILEGES;

DB 요소 및 명령어

• 실습용 데이터 베이스 만들어 두기
  - CREATE DATABASE zerobase DEFAULT CHARACTER SET utf8mb4
  - utf8
  - 다국어
  - mb4
  - 이모지 문자 지원

Table

• Table
  - DB 안에서 실제 데이터가 저장되는 형태이고, 행(row)과 열(column)로 구성된 데이터 모음
• Table 생성
  - CREATE TABLE tablename(columnname datatype, columnname datatype, ...);
• Table 목록 확인
  - SHOW TABLES;
• Table 정보 확인
  - DESC tablename;
• Table 변경
  - 아래 문구 뒤에 변경할 내용에 맞추어 다음 문구를 넣는다.
  - ALTER TABLE tablename
  - table 이름 변경
  - RENAME new_tablename;
  - column 추가
  - ADD COLUMN columnname databtype;
  - column 데이터 타입 변경
  - MODIFY COLUMN columnname datatype;
  - column 이름, 데이터 입 변경
  - CHANGE COLUMN old_columnname new_columnname datatype;
  - column 삭제
  - DROP COLUMN columnname
• Table 삭제
  - DROP TABLE tablename;

DML

• INSERT
  - 데이터 추가
  - INSERT INTO tablename (col1, col2, ...) VALUES (val1, val2, ...);
  - 컬럼 이름 순서와 값 순서가 일치하도록 주의
  - 모든 컬럼 값 추가하는 경우 컬럼 이름 생략해도 괜찮음
  - INSERT INTO tablename VALUES (val1, val2, ...);
• SELECT
  - 테이블 내 특정 컬럼에 대한 데이터를 조회
  - SELECT col1, col2 FROM tablename;
  - 테이블 내 모든 컬럼에 대한 데이터를 조회
  - SELECT * FROM tablename;
  - WHERE
  - SQL문에 조건을 추가하여 SELECT 뿐 아니라 UPDATE, DELETE에도 사용
  - WHERE condition;
• UPDATE
  - 테이블 내 데이터 수정
  - UPDATE tablename SET col1 = val1, col2 = val2, ... WHERE condition;
• DELETE
  - 테이블 내 내용 삭제
  - DELETE FROM tablename WHERE condition;

ORDER BY

• 정렬
• SELECT col1, col2 FROM tablename
• ORDER BY col1, col2, ... ASC | DESC;

비교 연산자

• =
  - 같은

• 	- 보다 큰(초과)

• <
  - 보다 작은(미만)

• =
  - 보다 크거나 같은(이상)

• <=
  - 보다 작거나 같은(이하)
• <>
  - 보다 크거나 작은(같지 않은)
• !=
  - 같지 않은

논리 연산자

AND 우선순위가 OR 보다 높아 먼저 처리되므로 괄호 활용을 잘 해야한다.

• AND
  - 조건을 모두 만족하면 TRUE
  - SELECT col1, col2 FROM tablename WHERE cond1 AND cond2 AND cond3 ...;
• OR
  - 하나의 조건이라도 만족하면 TRUE
  - SELECT col1, col2 FROM tablename WHERE cond1 OR cond2 OR cond3 ...;
• NOT
  - 조건을 만족하지 않으면 TRUE
  - SELECT col1, col2 FROM tablename WHERE NOT cond
• BETWEEN
  - 조건값이 범위 사이에 있으면 TRUE
  - SELECT col1, col2 FROM tablename WHERE col1 BETWEEN val1 AND val2;
• IN
  - 조건값이 목록에 있으면 TRUE
  - SELECT col1, col2 FROM tablename WHERE col1 IN (val1, val2, ...)
• LIKE
  - 조건값이 패턴에 맞으면 TRUE
  - SELECT col1, col2 FROM tablename WHERE col LIKE pattern
  - %
  - 어떤 문자가 와도 괜찮다
  - _
  - 어떤 문자 한 개

UNION

• UNION
  - 여러 개의 SQL문을 합쳐서 하나의 SQL문으로 만들어주는 방법
  - 칼럼 갯수가 같아야 함
• UNION
  - 중복된 값을 제거하여 알려준다
• UNION ALL
  - 중복된 값도 모두 보여준다
• SELECT col1, col2, ... FROM tableA UNION | UNION ALL SELECT col1, col2, ... FROM tableB;

JOIN

• JOIN
  - 두 개 이상의 테이블을 결합하는 것
• INNER JOIN
  - 두 개의 테이블에서 공통된 요소들을 통해 결합하는 조인 방식
  - SELECT tableA.col1, tableB.col2, ... FROM tableA INNER JOIN tableB ON tableA.col = tableB.col WHERE condition;
• LEFT JOIN
  - 두 개의 테이블에서 공통 영역을 포함해 왼쪽 테이블의 다른 데이터를 포함하는 조인 방식
  - SELECT tableA.col1, tableB.col2, ... FROM tableA LEFT JOIN tableB ON tableA.col = tableB.col WHERE condition;
• RIGHT JOIN
  - 두 개의 테이블에서 공통 영역을 포함해 오른쪽 테이블의 다른 데이터를 포함하는 조인 방식
  - SELECT tableA.col1, tableB.col2, ... FROM tableA RIGHT JOIN tableB ON tableA.col = tableB.col WHERE condition;
• FULL OUTER JOIN
  - 두 개의 테이블에서 공통 영역을 포함하여 양쪽 테이블의 다른 영역을 모두 포함하는 조인 방식
  - SELECT tableA.col1, tableB.col2, ... FROM tableA FULL OUTER JOIN tableB on tableA.col = tableB.col WHERE condition;
  - MySQL은 동일 쿼리를 제공하지 않고 비슷한 다른 것으로 사용가능
  - SELECT tableA.col1, tableB.col2, ... FROM tableA LEFT JOIN tableB on tableA.col = tableB.col UNION SELECT tableA.col1, tableB.col2, ... FROM tableA RIGHT JOIN tableB ON tableA.col = tableB.col WHERE condition;
• SELF JOIN
  - 가장 많이 사용되며, INNER JOIN과 같은 결과를 낸다.
  - JOIN되는 table들의 공통되는 데이터를 가져온다.
  - SELECT tableA.col1, tableB.col2, ... FROM tableA, tableB, ... WHERE condition;
  - 기준을 where 절에 명시한다

유용한 기능

• CONCAT
  - 여러 문자열을 하나로 합치거나 연결하는 함수
  - SELECT CONCAT('string1', 'string2', ...);
• ALIAS
  - 칼럼이나 테이블 이름에 별칭 생성
  - SELECT column AS alias FROM tablename
  - SELECT col1, col2, ... FROM tablename AS alias
  - as 생략 가능
• DISTINCT
  - 검색한 결과의 중복 제거
  - SELECT DISTINCT co1, co2, ... FROM tablename;
• LIMIT
  - 검색결과를 정렬된 순으로 주어진 숫자만큼만 조회
  - SELECT col1, col2, ... FROM tablename WHERE condition LIMIT number;

AWS RDS

• AWS RDS, Amazon Relational Database Service
  - AWS에서 제공하는 관계형 데이터베이스 서비스
  - Cloud 상에 Database를 구축한다
• AWS RDS 생성
  - 콘솔 > 서비스 > 데이터베이스 > RDS > 데이터베이스 생성
  - 표준 생성 > MySQL > 프리 티어(무료) > 스토리지 자동 조정 활성화 해제(임계값 초과 시 스토리지 늘어나면 돈 냄) > 퍼블릭 액세스 가능 체크(외부에서 접속할 때 필요) > 자동 백업 비활성화(용량 많이 차지함) > 삭제 방지 활성
• 외부 접속 설정
  - 해당 DB > 보안에 VPC 보안 그룹 아래 default 값 눌러 이동
  - 보안 그룹 내 보안 그룹 ID 눌러 보안 페이지로 이동
  - 인바운드 규칙 내 하나를 체크한 후 인바운드 규칙 편집
  - 규칙 추가
  - MySQL/Aurora
  - Anywhere-IPv4
• 접속
  - 앤드포인트 및 포트 정보를 활용
  - % mysql -h <엔드포인트> -P <포트> -u <마스터 사용자 이름> -p
  - use mysql
  - select host user from user;
• 중지
  - DB 체크하고, 작업 > 일시 정지
• 다시 시작
  - DB 내부로 들어와 작업 > 시작

SQL file

• SQL File
  - SQL 쿼리를 모아놓은 파일
  - DB, Table을 Restore, back up하는데에 이용함

SQL File 실행

• 로그인 이후
  - source /path/filename.sql
  - \. /path/filename.sql
  - \. filename.sql
  - 로그인할 때 맨 뒤에 database 를 적으면 바로 그곳에서 시작한다
• 외부에서 바로 실행
  - mysql -u username -p databaseName < /path/filename.sql

Database Backup

• 특정 DB backup
  - mysqldump -u username -p dbname > backup.sql
• 모든 DB backup
  - mysqldump -u username -p --all-databases > backup.sql

Database Backup Restore

• DB를 백업한 SQL File을 실행하여 그 시점으로 복구하거나 이전할 수 있다.
  - SQL File 실행하는 방법과 동일함
• AWS RDS 서비스가 사용 가능한 상태에서 접속
• zerobase로 이동
• source filename.sql

Database Backup Table Restore

• mysqldump -u username -p dbname tablename > backup.sql

Table Schema Backup

• 데이터를 제외하고 테이블 생성 쿼리만 백업
• 특정 table schema backup
  - mysqldump -d -u username -p dbname tablename > backup.sql
• 모든 table schema backup
  - mysqldump -d -u username -p dbname > backup.sql

Python with MySQL

• ds_study 가상환경에서 진행
• 테이블은 그대로 두고 내용만 삭제
  - delete from police_station;
• 라이브러리 설치
  - pip install mysql-connector-python

connect

• 연결

db변수 = mysql.connector.connect(
	host = '주소',
	port = 3306,
	user = 'admin',
	password = '*****',
	database = 'zerobase'
)


db변수 = mysql.connector.connect(
	host = 'localhost',
	user = 'root',
	password = '*****',
	database = 'zerobase'
)

• 닫기
  - 변수.close()

execute

• 커서 생성
  - 커서변수 = db변수.cursor()
  - 커서변수.execute(<query>);
• SQL File 실행 코드
  - 파일변수 = open("filename.sql").read()
  - 커서변수.execute(파일변수)
  - 쿼리가 여러개 존재하는 경우
  - 커서변수.execute(파일변수, multi = Ture)

fectch all

• 조회하는 쿼리를 실행할 경우 데이터를 가지고 오는데, 그 데이터를 변수에 담는 함수
• 데이터변수 = 커서변수.fetchall()
• 읽어올 데이터 양이 많은 경우
  - 커서 변수 선언시, buffered = True 옵션을 넣어준다
• 데이터변수의 데이터 타입은 튜플이 여러 개 담긴 리스트
• 데이터변수, 검색 결과를 Pandas의 DF로 변환하여 읽을 수 있다

csv

• csv에 있는 데이터를 python으로 INSERT 하기
• commit()
  - database에 적용하기 위한 명령
• MySQL.connector execute 공식문서 참조
  - operation 옵션에 query 구문을 쓰고, params 옵션에 query 구문에서 데이터로 받을 부분을 쓰는데, 이때 paramas 옵션을 명시하지 않아도 위치상 두 번째면 paramas 값으로 받는다.

CONSTRAINT

• 자동 생성된 CONSTRAINT 를 확인
  - show create table tablename;

PPRIMARY KEY

• 테이블의 각 레코드를 식별
• 중복되지 않은 고유값을 포함
• NULL 값을 포함할 수 없음
• 테이블 당 하나의 기본키를 가짐
• 생성
  - 기존 테이블을 수정
  - ALTER TABLE tablename ADD PRIMARY KEY (col1, col2);
  - 새로운 테이블에 생성

CREATE TABLE tablename
(
	col1 datatype NOT NULL,
	col2 datatype NOT NULL,
	...
	CONSTRAINT constaint_name # 생략가능, 자동 생성됨
		PRIMARY KEY (co1, col2, ...)
);

• 삭제
  - ALTER TABLE tablename DROP PRIMARY KEY;

FOREIGN KEY

• 한 테이블을 다른 테이블과 연결해주는 역할이며 참조되는 테이블의 항목은 그 테이블의 기본키(혹은 단일값)
• 생성
  - 기존 테이블 수정
  - ALTER TABLE tablename ADD FOREIGN KEY (col) REFERENCES REF_tablename(REF_col);
  - 새로운 테이블에 생성

CREATE TABLE tablename
(
	col1 datatype NOT NULL,
	col2 datatype NOT NULL,
	col3 datatype,
	col4 datatype,
	...
	CONSTRAINT constraint_name
	 PRIMARY KEY (col1, col2, ...),
	CONSTRAINT constraint_name # 생략 가능
	 FOREIGN KEY (col3, col4, ...) REFERENCES REF_tablename(REF_col)
);

• 삭제
  - ALTER TABLE tablename DROP FOREIGN KEY FK_constraint;

AGGREGATE Functions

• 여러 칼럼 혹은 테이블 전체 칼럼으로부터 하나의 결과값을 반환하는 함수
• COUNT
  - 총 갯수를 계산해주는 함수
  - SELECT COUNT(col) FROM tablename WHERE condition;
• SUM
  - 합계를 계산해주는 함수
  - SELECT SUM(col) FROM tablename WHERE condition;
• AVG
  - 평균을 계산해주는 함수
  - SELECT AVG(col) FROM tablename WHERE condition;
• MIN
  - 가장 작은 값을 찾아주는 함수
  - SELECT MIN(col) FROM tablename WHERE condition;
• MAX
  - 가장 큰 값을 찾아주는 함수
  - SELECT MAX(col) FROM tablename WHERE
• FIRST
  - 첫 번째 결과값을 리턴하는 함수
• LAST
  - 마지막 결과값을 리턴하는 함

조건

GROUP BY

• 그룹화하여 데이터를 조회
• SELECT col1, col2, ... FROM tablename WHERE condition GROUP BY col1, col2, ... ORDER BY col1, col2, ...
• distinct는 비슷한 효과를 내지만 ORDER BY를 사용할 수 없다

HAVING

• 조건에 집계함수가 포함되는 경우 WHERE 대신 사용함
• SELECT col1, col2, ... FROM tablenumber WHERE condition GROUP BY col1, col2, ... HAVING condition (Aggregate Functions) ORDER BY col1, col2, ...

SCALAR Functions

입력값을 기준으로 단일 값을 반환하는 함수

• UCASE
  - 영문을 대문자로 변환하는 함수
  - SELECT UCASE(string);
• LCASE
  - 영문을 소문자로 변환하는 함수
  - SELECT LCASE(string);
• MID
  - 문자열 부분을 반환하는 함수
  - SELECT MID(string, start_position, length);
  - string
  - 원본 문자열
  - start
  - 문자열 반환 시작 위치
  - 첫 글자, 1
  - 마지막 글자, -1
  - length
  - 반환할 문자열 길이
• LENGTH
  - 문자열의 길이를 반환하는 함수
  - SELECT LENGTH(string);
  - 문자열 없음
  - 0
  - 공백
  - 1
  - NULL
  - NULL
• ROUND
  - 지정한 자리에서 숫자를 반올림하는 함수(0이 소수점 첫째 자리)
  - SELECT ROUND(number, deciamls_place)
  - number
  - 반올림할 대상
  - decimals
  - 반올림할 소수점 위치(option)
  - 0
  - 첫 번째 소수점 위치
  - -1
  - 일단위 위치
• NOW
  - 현재 날짜 및 시간을 반환하는 함수
  - SELECT NOW();
• FORMAT
  - 숫자를 천단위 콤마가 있는 형식(문자열)으로 반환하는 함수
  - SELECT FORMAT(number, decimal_place);
  - number
  - 포맷을 적용할 문자 혹은 숫자
  - decimals
  - 표시할 소수점 위치
  - 잘리면 자동으로 반올림하고
  - 원래보다 더 있으면 0으로 채운다
  - 0
  - 소수점 표시 X

SQL Subquery

• 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문
• 메인쿼리가 서브쿼리를 포함하는 종속적인 관계
  - 서브쿼리는 메인쿼리의 칼럼 사용 가능
  - 메인쿼리는 서브쿼리의 칼럼 사용 불가
  - 서브쿼리는 괄호로 묶어서 사용
  - 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능
  - 서브쿼리에서는 order by 사용불가
• Scalar Subquery
  - SELECT 절에서 사용된다
  - 결과는 하나의 칼럼이어야 한다
  - SELECT col1, (SELECT col2 FROM table2 WHERE condition) FROM table1 WHERE condition;
• Inline view
  - FROM 절에서 사용되며, 메인쿼리에서는 인라인 뷰에서 조회한 칼럼만 사용 가능
  - 내가 필요한 값들을 모아둔 결과를 inline view로 활용하는 것
  - SELECT a.col, b.col FROM tableA a, (SELECT col1, col2 from tableB) b WHERE condition;
• Nested Subquery
  - WHERE 절에서 사용
  - Single Row
  - 하나의 행을 검색하는 서브쿼리
  - 서브쿼리가 비교연산자와 사용되는 경우, 검색결과는 한 개의 결과값을 가져야 한다
  - SELECT col1 FROM tableA WHERE col1 = (SELECT col2 FROM tableB WHERE condition) ORDER BY col1;
  - Multiple Row
  - 하나 이상의 행을 검색하는 서브쿼리
  - IN
  - 서브쿼리 결과 중에 포함될 때
  - SELECT col1 FROM tableA WHERE col IN (SELECT col2 FROM tableB WHERE condition) ORDER BY col1;
  - JOIN을 써도 괜찮고, 효율도 JOIN이 더 좋음
  - EXISTS
  - 서브쿼리 결과에 값이 있으면 반환
  - SELECT col1 FROM tableA WHERE EXISTS (SELECT col2 FROM tableB WHERE condition) ORDER BY col1;
  - ANY
  - 서브쿼리 결과 중에 최소한 하나라도 만족하면(비교연산자 사용)
  - SELECT col1 FROM tableA WHERE col1 = ANY (SELECT col2 FROM tableB WHERE condition) ORDER BY col1
  - ALL
  - 서브쿼리 결과를 모두 만족하면(비교연산자 사용)
  - SELECT col1 FROM tableA WHERE col1 = ALL (SELECT col2 FROM tableB WHERE condition) ORDER BY col1
  - Multiple Column
  - 하나 이상의 열을 검색하는 서브쿼리
  - 서브쿼리 내에 메인쿼리 칼럼이 같이 사용되는 경우
  - SELECT col1 FROM tableA a WHERE (a.col1, a.col2, ...) IN (SELECT b.col1, b.col2, ... FROM tableB b WHERE a.col1 = b.col2) ORDER BY col1;

느낀점

SQL 코딩 테스트 연습은 많이 했지만, 직접 DB를 만들고 table을 만들어 내용을 정리하고, 합하고, 그것을 또 AWS에 올리는 것은 처음 해봤다. 처음에는 따라하는것이 벅차서 내용을 이해 못하다가, sql 백업 파일에서 오류가 나면서 갑자기 많은 것이 이해되었다.
잘 안되어야 머리를 굴리는 이유는 무엇일까. 그래도 지금까지 한 공부 중에 가장 재미있다. 직관적이고, 실습 과정이 안전하다고 느껴진다.

이 글은 제로베이스 데이터 취업 스쿨의 강의 자료 일부를 발췌하여 작성되었습니다.