SQL(AWS RDS, SQL File, Python with MySQL, Key, 집계함수, Scalar Functions, SubQuery)

AWS RDS

• AWS RDS 사용하기:
  • %mysql -h <엔드포인트> -P <포트> -u <마스터 사용자 이름> -p

SQL File

• SQL파일을 이용해서 mysql에 실행할 수 있다.

  • SQL 파일 실행: source test01.sql
  • 외부에서 바로 실행: mysql -u username -p < </path/filename.sql>

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

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

• SQL File로 Table 단위를 백업할 수 있다.

  • Table을 백업한 SQL File을 실행하여, 해당 테이블을 복구하거나 이전할 수 있음
  • %mysqldump -u username -p dbname tablename > backup.sql

• 데이터를 제외하고 테이블 생성 쿼리만 백업할 수 있다.

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

Python with MySQL

• MySQL Driver 설치: pip install mysql -connector-python

• MySQL 접속 하기 위한 코드

mydb = mysql.connector.connect(
  host = "<hostname>", # localhost or AWS 엔드포인트
  port = "<Port>",
  user = "<username>",
  password = "<password>"
  # database = "<databasename>"
)

mydb.close() #접속 종료

• Query를 실행하기 위한 코드

mycursor = mydb.cursor()
mycursor.execute(<query>)

• SQL File을 실행하기 위한 코드

mycursor = mydb.cursor()

sql = open("<filename>.sql").read()
mycursor.execute(sql)
#or
mycursor.execute(sql, multi=True) # Query가 여러개 존재하는 경우

• fetchall() : 죄회 결과 저장
  • 읽어올 데이터 양이 많은 경우: mycursor = mydb.cursor(buffered=True)

mycursor = mydb.cursor(buffered=True)
mycursor.execute(<query>)

result = mycursor.fetchall()
for result_iterrator in result:
   print(result_iterrator)

# Pandas로 일기
df = pd.DataFrame(result)
df.head()

Python with CSV

• CSV에 있는 데이터를 Python으로 INSERT
  • 한글이 깨지는 경우 encoding값을 'euc-kr'로 설정

mycursor = mydb.cursor(buffered = True)

sql = "insert into tablename VALUES (%s, %s,...)"

for i , row in df.iterrows():
  cursor.excute(sql, tuple(row))
  print(tuple(row))
  mycursor.commit()

Primary Key, Foreign Key

Primary Key(기본키)

• Primary Key(기본키): 테이블의 각 레코드를 식별하는 키

  • 중복되지 않은 고유값을 포함
  • null값을 포함할 수 없음
  • 테이블 당 하나의 기본키를 가짐

• 생성문법

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

• 삭제문법

# 기본키가 여러개의 칼럼이어도 삭제 가능
ALTER TABLE tablename
DROP PRIMARY KEY;

• 이미 생성된 테이블에 기본키 생성

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

# or

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

Foreign Key (외래키)

• Foreign Key (외래키): 한 테이블을 다른 테이블과 연결해주는 역할
  • 참조되는 테이블의 항복은 그 테이블의 기본키(혹은 단일값)
• 생성문법

CREATE TABLE tablename
(
    column1 datatype NOT NUll,
    column2 datatype NOT NUll,
    column3 datatype,
    column4 datatype,
    ...
    CONSTRAINT constraint_name
     PRIMARY KEY (column1, column2,...),
    CONSTRAINT constraint_name #왜래키를 지정하는 경우 CONSTRAINT가 생략 할 수 있다.
     FOREIGN KEY (column3, column4,...) REFERENCES REF_tablename(REF_column)
);

# 자동 생성된 CONSTRAINT를 확인하는 방법
SHOW CREATE TABLE tablename;

• 삭제

ALTER TABLE tablename
DROP FOREIGN KEY FK_constraint;
# desc tablename;으로는 확인이 안되지만 SHOW CREATE TABLE tablename;에서 확인 가능

• 이미 생성된 테이블에서 외래키 생성

ALTER TABLE tablename
ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);

예제

• police_station과 crime_status 연결
• 경찰서 이름이 각 테이블에서 다름

SELECT c.police_station, p.name
from crime_status c, police_station p
WHERE p.name like concat('서울', c.police_station, '경찰서')
GROUP BY c.police_station, p.name;

• FOREIGN KEY 설정

ALTER TABLE police_station
ADD PRIMARY KEY (name);

• crime_status에 참조할 칼럼 추가 생성

ALTER TABLE crime_status
ADD COLUMN reference VARCHAR(16);

• 외래키 설정

ALTER TABLE crime_status
ADD FOREIGN KEY (reference) REFERENCES police_station(name);

• 참조 컬럼 update

UPDATE crime_status c, police_station p
SET c.reference = p.name
WHERE p.name like concat('서울', c.police_station, '경찰서');

• 외래키를 기준으로 연관 검색

SELECT c.police_station, p.address
FROM crime_status c, police_station p
WHERE c.reference = p.name
GROUP BY c.police_station;

집계합수

• 집계함수: 여러 칼럼 혹은 테이블 전체 칼럼으로부터 하나의 결과값을 반환하는 함수
  • COUNT: 총 갯수를 계산
  • SUM: 합계를 계산
  • AVG: 평균을 계산
  • MIN: 가장 작은 값
  • MAX: 가장 큰 값
  • FIRST: 첫번째 결과값
  • LAST: 마지막 결과값

SELECT COUNT(column)# SUM, AVG, MIN, MAX, FIRST, LAST
FROM tablename
WHERE condition;

GROUP BY

• GROUP BY: 그룹화 하여 데이터를 조회

SELECT column1, column2,...
FROM table
WHERE condition
GROUP BY column1, column2,...
ORDER BY column1, column2,...

#EX
SELECT police_station, status_type, AVG(case_number)
FROM crime_status
GROUP BY police_station, status_type
LIMIT 5;

HAVING

• HAVING: 조건에 집계함수가 포함되는 경우 WHERE 대신 HAVING사용

SELECT column1, column2,...
FROM table
WHERE condition
GROUP BY column1, column2,...
HAVING condition (Aggregate Functions)
ORDER BY column1, column2,...

Scalar Functions

• Scalar Functions: 입력값을 기준으로 단일 값을 반환하는 함수
  • UCASE: 영문을 대문자로 변환
  • LCASE: 영문을 소문자로 변환
  • MID: 문자열 부분을 반환
  • LENGTH: 문자열의 길이를 반환
  • ROUND: 지정한 자리에서 숫자 반올림(0이 소주점 첫째자리)
  • NOW: 현재 날짜 및 시간을 반환
  • FORMAT: 숫자를 천단위 콤마가 있는 형식으로 변환

SELECT UCASE(string);
SELECT LCASE(string);

SELECT MID(string, start_position, lenth); # 첫글자는 1, 마지막 글자는 -1

SELECT LENGTH(string);# 공백은 1, 문자가 없는 경우 0, null은 null

SELECT ROUND(number, decimals_place); # 1단위 위치는 -1

SELECT NOW();

SELECT FORMAT(number, decimal_place); # decimal: 표시할 소수점 위치

SQL Subquery

• Subquery: 하나의 sql문 안에 포함되어 있는 다른 sql문
  • 메인 쿼리가 서브쿼리를 포함하는 종속적 관계
  • 서브쿼리는 메인쿼리의 칼럼 사용 가능
  • 메인쿼리는 서브쿼리의 칼럼 사용 불가
  • Subquery는 괄호로 묶어서 사용
  • 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능
  • Subquery에서는 order by 사용 불가
  • 스카라 서브쿼리(Scalar Subquery)- SELECT 절에 사용
  • 인라인 뷰(Inline View): FROM 절에 사용
  • 중첩 서브쿼리(Nested Subquery): WHERE절에 사용

스카라 서브쿼리(Scalar Subquery)

• 스카라 서브쿼리(Scalar Subquery): SELECT 절에서 사용하는 서브쿼리
  • 결과는 하나의 COLUMN

SELECT column1, (SELECT column2 FROM table2 WHERE condition)
FROM table1
WHERE condition;

인라인 뷰(Inline View)

• 인라인 뷰(Inline View): FROM 절에 사용
  • 메인 쿼리에서는 인라인 뷰에서 조회한 Column만 사용가능

SELECT a.column1, b.column
FROM table1 a, , (SELECT column1, column2 FROM table2) b
WHERE condition;

중첩 서브쿼리(Nested Subquery)

• 중첩 서브쿼리(Nested Subquery): WHERE절에서 사용
  • Single Row : 하나의 열을 검색하는 서브쿼리
  • Multiple Row: 하나 이상의 열을 검색하는 서브쿼리
  • Multiple Column: 하나 이상의 행을 검색하는 서브쿼리

#Single Row
# 비교연산자에 사용되는 경우 하나의 결과값을 가져야한다.
SELECT column_names
FROM table_name
WHERE column_name = (SELECT column_name
                    FROM table_name
                    WHERE condition)
ORDER BY column_name;

#Multiple Row
# in
SELECT column_names
FROM table_name
WHERE column_name IN (SELECT column_name
                    FROM table_name
                    WHERE condition)
ORDER BY column_name;

#exists
# 서브쿼리 결과에 값이 있으면 반환
SELECT column_names
FROM table_name
WHERE EXISTS (SELECT column_name
                    FROM table_name
                    WHERE condition)
ORDER BY column_name;

#ANY
# 결과중 하나라도 만족하면(비교연산자 사용)
SELECT column_names
FROM table_name
WHERE column_name = ANY (SELECT column_name
                    FROM table_name
                    WHERE condition)
ORDER BY column_name;

#ALL
# 결과중 모두 만족하면(비교연산자 사용)
SELECT column_names
FROM table_name
WHERE column_name = ALL (SELECT column_name
                    FROM table_name
                    WHERE condition)
ORDER BY column_name;

#Multiple Column
# 서브쿼리 내에 메인쿼리 칼럼이 사용되는 경우
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_name;