[스터디 노트] 3번째 SQL (240909), 제로베이스 데이터 분석 스쿨 내용

제로베이스 데이터 분석 스쿨 내용에 대한 기록이다.

3번째도 SQL 강의이다.

 

예전에 AWS RDS 너무 대충 해보고 넘어가서 잘 기억이 안났었다.

이번에 다시 차근차근 해보면서 AWS RDS를 제대로 알게되었다.

 

그냥 원래 파이썬을 쓰던 사람으로써 SQL을 왜 굳이 쓰지? 라고만 생각했었다.

하지만 SQL을 써야만 하는 어쩔 수 없는 상황이 오는법이다.

그냥 그때 가서 구글링 해야지 라고 생각했었는데,

이번 기회에 파이썬과 SQL을 연계하여 사용하는 방법을 익힐 수 있었다.

 

[UNION 문법]

• 여러개 SQL문을 합쳐서 하나의 SQL문으로 만들기
• 컬럼의 개수가 반드시 같아야 한다.
• 컬럼의 종류가 달라도 괜찮은데 개수는 같아야 한다.
• UNION: 중복된 값을 제거하여 리턴
• UNION ALL: 중복된 값도 모두 리턴
• 실습환경 구축
  • create table test1 (no int);
  • create table test2 (no int);
  • insert into test1 values (1);
  • insert into test1 values (2);
  • insert into test1 values (3);
  • insert into test2 values (5);
  • insert into test2 values (6);
  • insert into test2 values (3);
• UNION 사용
  • select * from test1 union all select * from test2;
  • select * from test1 union select * from test2;
  • select * from celeb where sex='F' union all select * from celeb where agency ='YG엔터테이먼트';
  • select * from celeb where sex='F' union select * from celeb where agency ='YG엔터테이먼트';
    
    

[JOIN 문법]

• 두개 이상의 테이블을 결합하는 것
• table1 join table2 on 조건 외우기
• INNER JOIN: 교집합
• LEFT JOIN: 왼쪽 + 교집합
• RIGHT JOIN: 교집합 + 오른쪽
• FULL OUTER JOIN: 합집합
• SELF JOIN: 결과는 이너 조인이랑 동일
• LEFT와 RIGHT 특징
  • 교집합이 아닌 데이터는 NULL로 채워서 나옴
  • 레프트는 왼쪽이 먼저 표시, 교집합이 이어서 표시
  • 라이트는 교집합이 먼저 표시, 오른쪽이 이어서 표시
• FULL OUTER JOIN 특징
  • mysql에서는 사용 불가
  • 순서는 왼쪽 먼저 표시, 교집합 이어서 표시, 오른쪽 이어서 표시
• 실습환경 구축
  • create table snl_show (ID int not null auto_increment primary key, SEASON int not null, EPISODE int not null, BROADCAST_DATE date, HOST varchar(32) not null);
  • desc snl_show;
  • INSERT INTO snl_show VALUES (1, 8, 7, '2020-09-05', '강동원'); INSERT INTO snl_show VALUES (2, 8, 8, '2020-09-12', '유재석'); INSERT INTO snl_show VALUES (3, 8, 9, '2020-09-19', '차승원') ; INSERT INTO snl_show VALUES (4, 8, 10, '2020-09-26', '이수현'); INSERT INTO snl_show VALUES (5, 9, 1, '2021-09-04', '이병헌') ; INSERT INTO snL_show VALUES (6, 9, 2, '2021-09-11', '하지원') ; INSERT INTO snl_show VALUES (7, 9, 3, '2021-09-18', '제시'); INSERT INTO snl_show VALUES (8, 9, 4, '2021-09-25', '조정석'); INSERT INTO snl_show VALUES (9, 9, 5, '2021-10-02', '조여정') ; INSERT INTO snl_show VALUES (10, 9, 6, '2021-10-09', '옥주현');
  • select * from snl_show;
• JOIN 사용
  • select celeb.id, celeb.name, snl_show.id, snl_show.host from celeb inner join snl_show on celeb.name=snl_show.host;
  • select celeb.id, celeb.name, snl_show.id, snl_show.host from celeb left join snl_show on celeb.name=snl_show.host;
  • select celeb.id, celeb.name, snl_show.id, snl_show.host from celeb right join snl_show on celeb.name=snl_show.host;
  • select celeb.id, celeb.name, snl_show.id, snl_show.host from celeb left join snl_show on celeb.name=snl_show.host union select celeb.id, celeb.name, snl_show.id, snl_show.host from celeb right join snl_show on celeb.name=snl_show.host;
• SELF JOIN 사용
  • 쿼리에서는 생략 가능. 알아서 돌아간다.
  • from 뒤에 테이블을 2개 다 적어야 한다.
  • select celeb.id, celeb.name, snl_show.id, snl_show.host from celeb, snl_show where celeb.name=snl_show.host;
  • select celeb.name, celeb.job_title from celeb, snl_show where celeb.name=snl_show.host and celeb.agency='안테나';
  • select celeb.name, celeb.age, celeb.job_title, snl_show.season, snl_show.episode from celeb, snl_show where celeb.name=snl_show.host and ((not celeb.job_title like '%영화배우%' and celeb.agency='YG엔터테이먼트') or (celeb.age>=40 and agency!='YG엔터테이먼트'));
  • select snl_show.id, snl_show.season, snl_show.episode, celeb.name, celeb.job_title from snl_show, celeb where snl_show.host=celeb.name;
  • select snl_show.host from snl_show, celeb where snl_show.host=celeb.name and (snl_show.episode in (7, 9, 10) or celeb.agency like 'YG______') and broadcast_date>='20200915';
    
    

[CONCAT 문법]

• 여러 문자열을 하나로 합치거나 연결
• CONCAT 사용
  • select concat('concat', ' ', 'test')
  • select concat('이름:', name) from celeb;
    
    

[ALIAS 문법]

• 컬럼이나 테이블 이름에 별칭 생성
• as라고 사용하면 되는데 생략도 가능
• ALIAS 사용
  • select name as '이름' from celeb;
  • select name as '이름', agency as '소속사' from celeb;
  • select concat(name, ': ', job_title) as profile from celeb;
  • select s.season, s.episode, c.name, c.job_title from celeb as c, snl_show as s where c.name=s.host;
  • select concat(s.season, '-', s.episode, '(', s.broadcast_date, ')') as '방송정보', concat (c.name, '(', c.job_title, ')') as '출연자정보' from celeb as c, snl_show as s where c.name=s.host;
  • select concat(s.season, '-', s.episode, '(', s.broadcast_date, ')') as '방송정보', concat (c.name, '(', c.job_title, ')') as '출연자정보' from celeb c, snl_show s where c.name=s.host;
    
    

[DISTINCT 문법]

• 검색한 결과의 중복 제거
• select 바로 뒤에 넣기
• DISTINCT 사용
  • select distinct agency from celeb;
  • select sex, job_title from celeb where job_title like '%가수%';
  • select distinct sex, job_title from celeb where job_title like '%가수%';
    
    

[LIMIT 문법]

• 검색결과를 정렬된 순으로 주어진 숫자만큼의 행만 조회
• 가장 마지막에 적어서 사용
• LIMIT 사용
  • select * from celeb limit 3;
  • select * from celeb order by age limit 4;
    
    

[AWS RDS]

[AWS RDS 정의]

• Amazon Web Service, Relational Database Service
• 클라우드 상에 데이터베이스를 구축
  
  

[AWS RDS 생성]

• 회원가입
  • AWS 들어가서 회원가입
  • 개인으로 선택
  • 리전은 대한민국 선택
  • 서포트 플랜 선택 (무료 버전)
• MySQL RDS 생성
  • AWS 관리 콘솔 -> 왼쪽 상단 서비스 선택
  • 데이터베이스 - RDS 클릭
  • 데이터베이스 생성 누르기
  • 표준 생성 선택
  • MySQL 선택
  • 템플릿은 프리티어로 선택
  • DB 인스턴스 식별자는 그대로 사용
  • 마스터 사용자 이름 입력 (예시, root)
  • 마스터 암호 입력 (예시, root)
  • 프리티어로 사용 가능한 클래스 선택
  • 버스터블 클래스(t 클래스 포함) 선택
  • 스토리지 SSD로 선택
  • 스토리지 자동 조정은 반드시 비활성화
  • 나머지는 기본으로 선택
  • 연결 설정은 모두 기본값, 퍼블릭 액세스는 예 선택
  • 퍼블릭 액세스 가능은 외부에서 연결 가능하도록 하는 것
  • 보안 그룹, 포트 모두 기본
  • 데이터베이스 인증은 암호 인증 (test 위한 것. 주의.)
  • 추가구성 모두 기본값, 백업은 자동 백업 비활성화 선택
  • 모니터링 비활성화
  • 유지관리 모두 기본값, 삭제 방지는 활성화
  • 데이터베이스 생성
  • 상태가 사용 가능이 되면 생성 완료
• AWS RDS 외부 접속
  • AWS 관리 콘솔 -> 왼쪽 상단 서비스 선택
  • 데이터베이스 - RDS 클릭
  • Amazon RDS의 데이터베이스 클릭
  • 생성된 데이터베이스 클릭
  • 연결 및 보안
    • VPC 보안 그룹 클릭
    • 보안 그룹 ID 클릭
    • 인바운드 규칙 편집
    • 규칙 추가
    • MySQL/Aurora 선택, AnywhereIPv4 선택
    • 규칙 저장 클릭
    • 외부 접근 권한 발급 완료
      
      

[AWS RDS 사용]

• AWS RDS 접속
  • AWS 관리 콘솔 -> 왼쪽 상단 서비스 선택
  • 데이터베이스 - RDS 클릭
  • Amazon RDS의 데이터베이스 클릭
  • 생성된 데이터베이스 클릭
  • 연결 및 보안
  • 엔드포인트, 포트 복사 해놓기
  • (터미널)
  • mysql -h <엔드포인트> -P <포트> -u <마스터 사용자 이름> -p
  • 마스터 암호 입력
  • show databases;
  • use mysql
  • select host, user from user;
• AWS RDS 중지
  • AWS 관리 콘솔 -> 왼쪽 상단 서비스 선택
  • 데이터베이스 - RDS 클릭
  • Amazon RDS의 데이터베이스 클릭
  • 중지하려는 데이터베이스 목록 체크
  • 작업 버튼 -> 중지 클릭
  • 스냅샷 아니오, 중지합니다 클릭
  • 시간이 많이 걸림
  • 7일 중지되고 자동으로 다시 시작되니 주의
• AWS RDS 시작
  • AWS 관리 콘솔 -> 왼쪽 상단 서비스 선택
  • 데이터베이스 - RDS 클릭
  • Amazon RDS의 데이터베이스 클릭
  • 시작하려는 데이터베이스 클릭
  • 오른쪽 상단에 작업 -> 시작 클릭
  • 시간이 많이 걸림
    
    

[SQL File]

[SQL File 정의]

• sql 확장자
• SQL 쿼리를 모아놓은 파일
• 쿼리를 한번에 실행시킬 수 있음
• 실습 환경
  • 깃허브-로컬 레포지토리 하나 파기
  • vscode로 해당 폴더 실행
  • test01.sql 파일 하나 생성
    
    

[SQL File 실행]

• mysql 안에서 실행
  • source </path/filename.sql>
  • source 대신 \. 사용 가능
  • 경로가 같다면 <filename.sql>만 쓰기 가능
  • 실행 해보기
    • mysql -u root -p zerobase (데이터베이스로 바로 접속)
    • source test01.sql
    • desc police_station;
• mysql 밖에서 실행
  • 괄호 여는 꺾새(<) 사용
  • mysql -u root -p zerobase < test02.sql
  • desc crime_status;
    
    

[데이터베이스 백업]

• mysqldump 명령어
• 괄호 닫는 꺾새(>) 사용
• 백업 완료되면 파일이 하나 생성 된다.
• 백업한 파일을 실행하면 그 시점으로 복구 가능
• 백업 해보기
  • mysqldump -u root -p zerobase > zerobase.sql
  • 이걸 source로 사용 가능
• AWS RDS 서비스에서 백업
  • AWS 관리 콘솔 -> 왼쪽 상단 서비스 선택
  • 데이터베이스 - RDS 클릭
  • Amazon RDS의 데이터베이스 클릭
  • 데이터베이스 중지라면 시작으로 만들기
  • 연결 및 보안에서 엔드포인트, 포트 확인
  • mysql -h <엔드포인트> -P <포트> -u <마스터 사용자 이름> -p
  • 마스터 암호 입력
  • show databases;
  • use zerobase;
  • source zerobase.sql
  • show tables;
    
    

[테이블 백업]

• mysqldump 명령어
• 백업 해보기
  • mysqldump -u root -p zerobase celeb > celeb.sql
  • 이걸 source로 사용 가능
    
    

[테이블 스키마 백업]

• 데이터 없이 테이블 생성 쿼리만 백업 가능
• mysqldump 뒤에 -d 넣어서 사용
• 백업 해보기
  • mysqldump -d -u root -p zerobase snl_show > snl.sql
    
    

[파이썬 with MySQL]

[mysql 접속]

• !pip install mysql-connector-python
• import mysql.connector 확인
• 접속 많으면 안되니 그때 그때 종료하기
• 파이썬에서 mysql 접속
  • mydb = mysql.connector.connect( host = 'localhost', user = 'root', password = 'root' )
  • mydb.close()
• 데이터베이스 지정해서 접속
  • mydb = mysql.connector.connect( host = 'localhost', user = 'root', password = 'root', database = 'zerobase', )
  • mydb.close()
    
    

[쿼리 실행]

• 테이블 생성
  • excute 안에 똑같이 쿼리 치기
  • mydb = mysql.connector.connect( host = 'localhost', user = 'root', password = 'root', database = 'zerobase', )
  • cur = mydb.cursor()
  • cur.execute('create table sql_file (id int, filename varchar(16))')
  • mydb.close()
  • 터미널 mysql 접속
  • desc sql_file;
• 테이블 삭제
  • excute 안에 똑같이 쿼리 치기
  • mydb = mysql.connector.connect( host = 'localhost', user = 'root', password = 'root', database = 'zerobase', )
  • cur = mydb.cursor()
  • cur.execute('drop table sql_file')
  • mydb.close()
  • 터미널 mysql 접속
  • desc sql_file;
• sql 파일 실행
  • 실행은 open().read() 함수 사용
  • mydb = mysql.connector.connect( host = 'localhost', user = 'root', password = 'root', database = 'zerobase', )
  • cur = mydb.cursor()
  • sql = open('test03.sql').read()
  • cur.execute(sql)
  • mydb.close()
  • 터미널 mysql 접속
  • desc sql_file;
• sql 파일에 쿼리가 많은 경우 실행법
  • multi=True로 변경
  • for문 돌리면서 패치 만들고 커밋
  • mydb = mysql.connector.connect( host = 'localhost', user = 'root', password = 'root', database = 'zerobase', )
  • cur = mydb.cursor()
  • sql = open('test04.sql').read()
  • for i in cur.execute(sql, multi=True): if i.with_rows: print(i.fetchall()) else: print(i.statement)
  • mydb.commit()
  • mydb.close()
  • 터미널 mysql 접속
  • desc sql_file;
    
    

[Fetch All]

• 데이터를 가져온 경우에는 변수에 데이터를 담을 수 있음.
• 결과값을 변수에 모두 담을 때 사용
• fetch all 사용
  • cur = mydb.cursor(buffered=True)
  • cur.execute('select * from sql_file')
  • result = cur.fetchall()
  • 프린트 해보기
• 판다스로 읽기
  • df = pd.DataFrame(result)
  • df.head()
    
    

[csv 파일 사용]

• csv 파일 데이터를 파이썬을 활용해 insert
• police_station.csv 파일 읽기
  • df = pd.read_csv('police_station.csv')
  • df.read()
• 파이썬 활용해 insert
  • sql = 'insert into police_station values (%s, %s)'
  • print('### INSERT ###')
  • for i, row in df.iterrows(): cur.execute(sql, tuple(row)) print(tuple(row)) mydb.commit()
• 한글이 깨지는 경우
  • pd.read_csv() 파라미터에 encoding='euc-kr' 추가
    
    

[MySQL 문법 2]

[KEY 문법]

• PRIMARY KEY
  • 기본 키
  • 테이블의 각 레코드를 식별
  • 중복 없는 고유값 포함
  • NULL 불가능
  • 테이블 당 하나의 기본키를 가짐
  • 컬럼 하나를 PRIMARY KEY로 지정 가능
  • 여러 컬럼을 하나의 PRIMARY KEY로 지정 가능
  • 한개의 컬럼을 설정
    • 마지막 줄에 무엇을 키로 할건지 선언
    • create table persons (pid int not null, name varchar(16), age int, sex char, primary key (pid));
    • desc persons;
    • 키값에 PRI라고 적혀 있는 것이 PRIMARY KEY
  • 여러개의 컬럼을 설정
    • 마지막 줄에 키를 2개 선언
    • create table animal (name varchar(16) not null, type varchar(16) not null, age int, primary key (name, type));
    • desc animal;
  • PRIMARY KEY 삭제
    • drop을 사용
    • alter table persons drop primary key;
    • desc persons;
    • alter table animal drop primary key;
    • desc animal;
  • 이미 생성된 테이블에 PRIMARY KEY 설정
    • add를 사용
    • alter table persons add primary key (pid);
    • desc persons;
    • alter table animal add primary key (name, type);
    • desc animal;

깃허브 링크

 

깃허브 SQL 공부 부분 링크