SQL 심화

Binny·2023년 5월 31일
0

10. AWS RDS

1) Amazon Relational Database Service

  • AWS 에서 제공하는 관계형 데이터베이스 서비스

(1) 터미널 실행, my sql 원격 접속

  • 대문자 P : 포트 정보
  • 소문자 p : 패스워드
mysql -h "엔드포인트" -P 포트 -u admin -p비밀번호

(2) AWS RDS 중지

  • 7일간 일시 중지

11. SQL File

1) 실습환경 만들기

(1) workspace 생성

  • sql_ws 폴더를 만들고 그 하위에서 실습
Document % mkdir sql_ws
Document % cd sql_ws
sql_ws %

(2) SQL File 생성 방법

  • sql_ws 폴더 하위에서 다음을 실행
    • 현재 폴더를 VSCode로 열라는 뜻
sql_ws % code .

2) SQL File 실행

  • SQL 쿼리를 모아 놓은 파일

(1) SQL File 실행1 - 로그인 이후

  • 경찰서 정보를 테이블로 만들어봅니다.

  • sql_ws 폴더 위치로 이동하여 zerobase 에 접속

% cd sql_ws
sql_ws % mysql -u root -p zerobase
  • SQL File을 실행
source test01.sql
  • 결과 확인
desc police_station ;

(2) SQL File 실행1 - 외부에서 바로 실행

  • 터미널에서 바로 실행
% mysql -u username -p <database> < </path/filename.sql>

  • zerobase Database 에 접속하면서 SQL File 을 실행
sql_ws % mysql -u root -p zerobase < test02.sql
  • Zerobase 에 접속하여 결과 확인

3) Database Backup

  • SQL File 로 Database 를 백업할 수 있다.

(1) 문법

  • mysqldump

  • 특정 Database Backup

mysqldump -u username -p dbname > backup.sql
  • 모든 Database Backup
    • --all-databases
mysqldump -u username -p --all-databases > backup.sql

(2) 예제

  • zerobase Database Backup
mysqldump -u root -p zerobase > zerobase.sql

(3) 참고 : AWS RDS 서비스 시작

  • AWS 콘솔에 로그인
  • 서비스 > 데이터베이스 > RDS 선택
  • database 시작 상태 : 사용 가능

4) Database Restore

  • 백업한 파일 다시 복구
    • 데이터베이스를 백업합 SQL File 을 실행하여 그 시점으로 복구 이전 가능
    • SQL File 을 실행하는 방법과 동일

(1) 복원 예제

  • AWS RDS 서비스가 사용 가능한 상태로 접속
mysql -h 엔드포인트 -P 3306 -u admin -p

  • zerobase Database 이동
use zerobase;
  • AWS RDS 의 zerobase Database 를 복원
source zerobase.sql
  • 결과 확인
show tables;

5) Table Backup / Restore

(1) 문법

  • Table 단위로도 백업할 수 있다.
  • tablename
mysqldump -u username -p dbname tablename > backup.sql

(2) 테이블 백업 예제

  • Local Database 에서 celeb Table 을 백업
mysqldump -u root -p zerobase celeb > celeb.sql

(3) Table Restore 복구

  • table 을 백업한 SQL File 을 실행하여, 해당 테이블을 복구하거나 이전할 수 있다.
  • SQL File 을 실행하는 방법과 동일함

(4) 테이블 복원 예제

  • AWS RDS 의 zerobase 에서 celeb 테이블을 삭제 (다시 복원하기 위해)


6) Table Schema Backup

  • 데이터를 제외하고 테이블 생성 쿼리만 백업 가능
    • -d 옵션

(1) 문법

  • 특정 Table Schema Backup
mysqldump -d -u username -p dbname tablename > backup.sql
  • 모든 Table Schema Backup
mysqldump -d -u username -p dbname > backup.sql

(2) 실습

  • Local database 에서 snl_show Table Schema 백업
mysqldump -d -u root -p zerobase snl_show > snl.sql

12. Python with MySQL

1) 실습환경

  • VSCode 혹은 Web Browser 를 통해 실행
    • ds_study 환경에서 작업
  • Jupyter notebook 과 mysql 은 모두 sql_ws 폴더에서 시작
  • AWS RDS 로 생성한 database 사용

2) Python with MySQL connect

(1) Python으로 MySQL 접속

  • MySQL Driver 설치
pip install mysql-connector-python
  • 설치 확인
import mysql.connector
  • create connection

(2) create connection 예제1

  • local Database 연결
local = mysql.connector.connect(
    host = 'localhost',
    user = "root",
    password = "*****"
)

(3) create connection 예제2

  • AWS RDS 연결
remote = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "*****"
)

(4) close database

local.close()

remote.close()

(5) Local MySQL 의 zerobase 에 연결 (특정 데이터베이스의 연결)

local = mysql.connector.connect(
    host = 'localhost',
    user = "root",
    password = "*****"
    database = "zerobase"
)

3) Python with MySQL execute

(1) Query를 실행하기 위한 코드

(2) 실습

  • 테이블 생성
remote = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "*****",
    database = "zerobase"
)

cur = remote.cursor()
cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")

remote.close()
  • 결과 확인
desc sql_file;

  • 테이블 삭제
remote = mysql.connector.connect(
    host = "database-2.c15ppmahjx3n.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "zerobase",
    database = "zerobase"
)

cur = remote.cursor()
cur.execute("DROP TABLE sql_file")

remote.close()
  • 결과 확인
desc sql_file;

(3) SQL File 을 실행하기 위한 코드

(4) 실습

  • test03.sql 생성

  • test03.sql 실행

remote = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "*****",
    database = "zerobase"
)

cur = remote.cursor()
sql = open("test03.sql").read()
cur.execute(sql)

remote.close()

(5) SQL File 내에 Query 가 여러개 존재하는 경우

  • (sql, multi = True)

(6) 실습

  • test04.sql 생성

  • 실행

remote = mysql.connector.connect(
    host = "database-2.c15ppmahjx3n.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "*****",
    database = "zerobase"
)

cur = remote.cursor()
sql = open("test04.sql").read()
cur.execute(sql, multi = True)

remote.close()

4) Python with fetch all

  • 변수에 담기

(1) fetch all

(2) 실습

  • sql_file 테이블 조회

  • 읽어올 데이터 양이 많은 경우 : buffered = True

  • 참고 : 검색 결과를 Pasndas 로 읽기

    • 데이터프레임으로
import pandas as pd

df = pd.DataFrame(result)
df.head()

5) Python with CSV

(1) Read CSV

  • CSV에 있는 데이터를 python으로 INSERT

(2)

13. PRIMARY KEY, FOREIGN KEY

1) PRIMARY KEY

(1) 기본키

  • 테이블의 각 레코드 식별
  • 중복되지 않은 고유값을 포함
  • NULL 값을 포함할 수 없음
  • 테이블 당 하나의 기본키를 가짐

(2) 생성 문법

CREATE TABLE tablename
(
	column1 datatype NOT NULL,
    column2 datatype NOT NULL,
	...
    CONSTRAINT 	constraint_name
    PRIMARY KEY (column1, column2, ...)
);

(3) 예제1

  • 하나의 컬럼을 기본키로 설정하는 경우

(4) 예제2

  • 여러개의 컬럼을 기본키로 설정하는 경우

2) PRIMARY KEY 삭제

(1) 문법

  • PRIMARY KEY는 하나이기 때문에 별도의 이름 No
ALTER TABLE tablename
DROP PRIMARY KEY;

(2) 예제1

  • 하나의 컬럼이 기본키로 설정된 경우

(2) 예제2

  • 여러개의 컬럼이 기본키로 설정된 경우
  • 삭제하는 방법은 동일

3) PRIMARY KEY 속성 추가

  • 이미 만들어진 테이블에 KEY 속성 생성(추가) 하기

(1) 문법

ALTER TABLE tablename
ADD PRIMARY KEY(column1, column2, ...);

(2) 예제


4) FOREIGN KEY

(1) 외래키

  • 한 테이블을 다른 테이블과 연결해주는 역할
  • 참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일값)

(2) 문법

(3) 예제1

  • CREATE TABLE에서 FOREIGN KEY를 지정하는 경우
CREATE TABLE orders
(
	oid int not null,
    order_no varchar(16),
    pid int,
    primary key (oid),
    constraint FK_person FOREIGN KEY (pid) REFERENCES person(pid)
);

(4) 예제2

  • CREATE TABLE에서 FOREIGN KEY를 지정하는 경우, CONSTRAINT를 생략할 수 있다.

5) FOREIGN KEY 삭제

(1) 문법

  • foreign key는 여러개일 수 있으므로, 이름을 지정해줘야 함
ALTER TABLE tablename
DROP FOREIGN KEY FK_constraint ;

6) 생성된 테이블에 FOREIGN KEY 지정

(1) 문법

alter table tablename
add foreign key (column) references REF_tablename(REF_column) ;

(2) 예제1

ALTER TABLE orders
ADD FOREIGN KEY (pid) REFERENCES person(pid) ;

7) 실습 예제

(1) 두 테이블 확인

  • p.name 과 c.police_staion 컬럼 개수가 같음

  • 테이블 컬럼 확인 후, concat 이용하여 group by

(2) primary key, foreign key

  • police_station 테이블 / primary key 지정

  • crime_status 컬럼 추가 / foreign key 지정

  • police_station 지역명 이름을 reference 오른쪽 이름으로 만듦

(3) JOIN

8) 혼자서 해봅시다

(1) 문제

(2) 풀이 과정

  • root 계정으로

  • 1번 답 : 테이블 생성

  • 2번 답 : primary key 삭제

  • primary key가 여러개 설정이어도 같음

  • 3번 답 : foreign key 삭제

  • foreign key 이름 FK_study

  • 4번 답 : foreign key 연결

  • 5번 답 : primary key 연결

14. Aggregate Functions(집계함수)

  • 실습 환경
    : AWS RDS (database-2)
    : police_station 테이블 사용
    : crime_status 테이블 사용

  • 집계함수

1) Count

  • 총 갯수를 계산해주는 함수

(1) 문법

select count(column)
from tablename
where condition ;

(2) 실습

  • police_station 테이블에서 경찰서는 총 몇 군데?

  • crime_status 테이블에서 경찰서는 총 몇 군데? (중복 제외)

  • 참고 / 비교

  • crime_type은 총 몇 가지?

2) Sum

  • 숫자 컬럼의 합계를 계산

(1) 문법

select sum(column) from tablename
where condition ;

(2) 실습

  • 범죄 총 발생 건수는?

  • 살인의 총 발생 건수는?

  • 중부 경찰서에서 검거된 총 범죄 건수는?

3) Avg

  • 숫자 컬럼의 평균을 계산해주는 함수

(1) 문법

select avg(column) from tablename
where condition ;

(2) 예제

4) Min, Max

  • 숫자 컬럼 중 가장 작은/큰 값을 찾아주는 함수

(1) 문법

select min(column) from tablename
where condition ;


select max(column) from tablename
where condition ;

(2) 예제

5) Group By

  • 그룹화하여 데이터를 조회
  • 그룹별 데이터 조회

(1) 문법

select column1, column2, ...
from tablename
where condition;
group by column1, column2, ...
order by column1, column2, ... ;

(2) 예제

  • 정렬(order by) 가능

  • distinct는 정렬할 수가 없음

  • group by + 집계함수

6) Having

  • 조건에 집계함수가 포함되는 경우
  • where 대신 having 사용 : where 절에서는 집계함수 사용할 수 X

(1) 문법

  • 조건의 집계함수를 having 절에 입력
select column1, column2, ...
from table
where condition
group by col1, col2, ...
having condition (aggregate functions)
order by col1, col2, ... ;

(2) 예제

  • count로 alias

15. Scalar Functions

  • 실습환경
    : AWS RDS (database-2) zerobase
    : sandwich 데이터 (50 rows)

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

1) UCASE

(1) 문법

SELECT ucase(string) ;

(2) 예제

2) LCASE

(1) 문법

SELECT lcase(string) ;

(2) 예제

3) MID

  • 문자열 부분을 반환하는 함수

(1) 문법

  • string : 원본 문자열
  • start : 문자열 반환 시작 위치 (첫 글자는 1, 마지막 글자는 -1)
  • length : 반환할 문자열 길이
select mid(string, start_position, length) ;

(2) 예제

  • mid(cafe, -4, 4) 와 같은 값 반환

4) LENGTH

  • 문자열의 길이를 반환하는 함수

(1) 문법

SELECT length(string) ;

(2) 예제

  • 공백도 포함
  • '' : 0
  • ' ' : 1

  • 실습할 때는 랭킹도 같이 찍어보기

5) ROUND

  • 지정한 자리에서 숫자를 반올림하는 함수
  • 0 이 소수점 첫째 자리를 의미

(1) 문법

  • number : 반올림할 대상
  • decimals : 반올림할 소수점 위치(option)
SELECT round(number, decimals_place) ;

(2) 예제

  • decimals_place(소수점 위치) 옵션을 주지 않으면,
    디폴트 값으로 소수점 첫번째 자리에서 반올림

  • 소수점 첫번째 위치는 0

  • 소수점 두번째 자리는 1 / 세번째 자리는 2

  • 일 단위 위치는 -1

  • 십 단위 위치는 -2

  • sandwich 테이블 실습

6) NOW

  • 현재 날짜 및 시간을 반환하는 함수

(1) 문법

SELECT now() ;

(2) 예제

7) FORMAT

  • 숫자를 천단위 콤마가 있는 형식으로 반환
  • 문법이 round랑 비슷

(1) 문법

  • number : 포맷을 적용할 문자 혹은 숫자
  • decimals : 표시할 소수점 위치
  • 표시할 소수점 위치 이후는 반올림하여 잘라준다
SELECT round(number, decimal_place) ;

(2) 예제

  • 0 위치(소수점 첫째자리) 이후를 반올림하여 잘라준다 => 12,346

  • 소수점 자리가 비워져있으면 0으로 채워서 반환

  • oil_price 테이블 실습
    : 반올림 => round / 백원단위 => -3
    : format(가격, 0)

16. SQL Subquery

  • 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문을 말한다.

  • 메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.
    : 서브쿼리는 메인쿼리의 컬럼 사용 가능
    : 메인쿼리는 서브쿼리의 컬럼 사용 불가

  • Subquery는 괄호로 묶어서 사용

  • 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능

  • subquery 에서는 order by 사용X

1) Scalar Subquery

  • 스칼라 서브쿼리
  • SELECT 절에 사용

(1) 문법

  • SELECT 절에서 사용하는 서브쿼리
  • 결과는 하나의 Column 이어야 한다.
select column1, (select column2, from table2 where condition)
from table1
where condition ;

(2) 예제

2) Inline View

  • 인라인 뷰
  • FROM 절에 사용

(1) 문법

  • FROM 절에 사용하는 서브쿼리
  • 메인쿼리에서는 인라인 뷰에서 조회한 Column만 사용 가능
select a.column, b.column
from table1 a, (select column1, coulmn2 from table2) b
where condition ;

(2) 예제

3) Nested Subquery

  • 중첩 서브쿼리
  • WHERE 절에 사용

(1) 종류

  • Single Row : 하나의 행을 검색 (+ 비교연산자)

  • Multiple Row : 하나 이상의 행을 검색

    • in
    • exists
    • any
    • all

  • Mutiple Column : 하나 이상의 열을 검색

4) Single Row Subquery (Nested Subquery)

(1) 문법

  • 서브쿼리가 비교연산자( =, >, >=, <, <=, <>, !=)와 사용되는 경우,
    서브쿼리의 검색 결과는 한 개의 결과 값을 갖는다.
  • 두 개 이상인 경우 에러
select column_names
from table_name
where column_name = (select column_names
                     from table_name
                     where condition)
order by column_name ;

(2) 예제

5) Multiple Row Subquery (Nested Subquery)

  • in
  • exists
  • any
  • all

(1) + In 문법

  • 서브쿼리 결과 중에 포함될 때
  • Multiple Row + In
select column_names
from table_name
where column_name in (select column_names
                      from table_name
                      where condition)
order by column_name ;
  • join 과 비슷 : join을 더 많이 사용

(2) + In 예제

(3) + Exists 문법

  • 서브쿼리 결과에 값이 있으면 반환
  • Multiple Row + Exists
select column_names
from table_name
where exists (select column_names
              from table_name
              where condition)
order by column_name ;

(4) + Exists 예제

(5) + Any 문법

  • 서브쿼리 결과 중에 최소한 하나라도 만족하면 반환
  • 비교 연산자 사용
  • Multiple Row + Any
select column_names
from table_name
where column_name = any (select column_names
                         from table_name
                         where condition)
order by column_name ;

(6) + Any 예제

(7) + All 문법

  • 서브쿼리 결과를 모두 만족하면 반환
  • 비교 연산자 사용
  • Multiple Row + All
select column_names
from table_name
where column_name = All (select column_names
                         from table_name
                         where condition)
order by column_name ;

(8) + All 예제

6) Multiple Column Subquery (Nested Subquery)

(1) 문법

  • 연관 서브쿼리
  • 서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우
  • 메인쿼리의 컬럼을 가져와서 사용 가능
select column_names
from table_name a
where (a.column1, a.column2, ...) in (select b.column1, b.column2, ...
                                      from table_name b
                                      where a.column_name = b.column_name)
order by column_names ;

(2) 예제

profile
Binny
물음표 말고 느낌표 !
이전 포스트

SQL 기초

다음 포스트

SQL 심화

0개의 댓글

관련 채용 정보