심화 1
AWS RDS 접속
(1) 예시
mysql -h "엔드포인트" -P 3306 -u 유저 -p 비밀번호
데이터베이스 만들기
(1) 예시
create database "zerobase" default character set utf8mb4;
유저 만들기
(1) 예시
create user "zero"@"%" identified by "비밀번호"
새로 만든 유저가 안 보일 때
(1) 새로고침
(2) 예시
flush privileges
현재 데이터베이스에서 나가기
(1) system clear
심화 2
유저 권한 확인
(1) 예시
show grants for "유저"
유저에게 데이터베이스의 모든 권한 부여
(1) 예시
grant all on 데이터베이스.* to "유저"@"%";
유저에게 데이터베이스의 모든 권한 박탈
(1) 예시
revoke all on 데이터베이스.* to "유저"@"%";
SQL File
(1) SQL 쿼리를 모아놓은 파일
SQL File 실행1 - 로그인 이후
(1) 예시
source /path/filename.sql
. /path/filename.sql
. filename.sql <- 현재 폴더에 있는 경우 path 생략 가능
SQL File 열기 - 내부
(1) cd Documents
(2) mkdir sql_ws
(3) cd sql_ws
(4) code .
(5) sql_ws에서 파일명.sql 만든 후 테이블 만들기
(6) 터미널에서 zerobase 데이터베이스로 이동
(7) source 파일명.sql
SQL File 열기 - 외부
(1) Get-Content 파일명.sql | mysql -u root -p zerobase
데이터베이스 복구
(1) 데이터베이스를 백업한 SQL Flie을 실행하여 그 시점으로 복구하거나 이전할 수 있다.(SQL Flie 실행 방법과 동일)
테이블 백업
(1) 예시
sql_ws % mysqldump -u username -p dbname tablename > tablename.sql
SQL File로 테이블 복구
(1) 데이터베이스를 백업한 SQL Flie을 실행하여 해당 테이블을 복구하거나 이전할 수 있다.(SQL Flie 실행 방법과 동일)
(2) 테이블이 없는 곳에 실행하며 이전이 되는 것
테이블 스키마 백업
% mysqldump -d -u username -p dbname tablename > 파일명.sql <- 특정 테이블 스키마 백업
% mysqldump -d -u username -p dbname > 파일명.sql <- 모든 테이블 스키마 백업
테이블 백업하고 RDS에 이전
(1) Documents\sql_ws>mysqldump -u root -p zerobase celeb > celeb.sql
(2) mysql -h "엔트포인트" -P 3306 -u admin -p zerobase <- RDS의 zerobase 데이터베이스로 바로 이동
(3) source celeb.sql
테이블 스키마 백업
(예시)
mysql -d -u root -p zerobase snl_show > snl.sql
심화 3~4
파이썬으로 MySQL 접속 후 사용
(1) MySQL Driver 설치
pip install mysql-connector-python
(2) 설치 확인
import mysql.connector
(3) MySQL 접속하기 위한 코드
mydb = mysql.connector.connect(
host = "hostname",
user = "username",
password = "password"
database = "database" <- 데이터베이스로 바로 접속하려면
)
(4) AWS RDS 접속하기 위한 코드
위의 코드 + port = 3306
연결 종료
(1) mydb.close( )
(2) remote.close( )
execute sql
(1) 쿼리를 실행하기 위한 코드
(2) 커넥트 후에
mycursor = mydb.cursor( )
mycursor.execute(query);
SQL 파일을 실행하기 위한 코드
(1) 커넥트 후에
mycursor = mydb.cursor( )
sql = open(filename.sql).read( )
mycursor .execute(sql)
SQL 파일 내에 쿼리가 여러 개 존재하는 경우
(1) 커넥트 후에
mycursor = mydb.cursor( )
sql = open(filename.sql).read( )
result = mycursor .execute(sql, multi=True)
remote.commit( )
remote.close( )
fectch all
(1) 실행하는 쿼리가 아니라 조회하는 셀렉트문을 실행한 경우에는 데이터를 가지고 오는데 그때 fectch all을 사용해서 변수에 담을 수 있다..
읽어올 데이터 양이 많은 경우
(1) 예시
cur = remote.cursor(buffered=True)
검색 결과를 판다스로 읽기
(1) 예시
import pandas as pd
df = pd.DataFrame(result)
df.head( )
csv에 있는 데이터를 파이썬으로 insert
(1) csv를 판다스로 읽어와서 데이터 확인
(2) 커넥트
(3) cursor 선언(buffered=True)
(4) sql 선언
(5) for 문
(6) cursor 실행하고 for문으로 결과 확인(fetchall( ))
commit( )
(1) 재밌는 애
(2) 데이터베이스에 적용하기 위한 명령
(3) for문 안에 넣었을 시 5번째에서 오류가 나도 4번째까지 데이터는 데이터베이스에 들어가는데, for문 밖에 있을 시 아예 데이터가 담기지 않는다.
판다스로 읽을 때 csv 한글이 깨지는 경우
(1) encoding 값을 "euc-kr"
https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html
심화 5
Primary Key(기본 키)
(1) 테이블의 각 레코드를 식별
(2) 중복되지 않은 고유값을 포함
(3) NULL 값을 포함할 수 없음
(4) 테이블 당 하나의 기본 키를 가짐
(5) 컬럼 하나를 지정할 수도 있고 여러 개의 컬럼을 기본 키로 지정할 수도 있다.
기본 키 생성
(1) 예시
create table tablename
(
column1 datatype not null,
column2 datatype not null,
constraint constraint_name <- 생략 가능(자동 생성)
primary key (column1, column2)
);
기본 키 삭제
(1) 예시
alter table tablename
drop primary key;
기존 테이블에 기본 키 생성
(1) 예시
alter table tablename
add primary key (column1, column2)
Foreign Key(외래키)
(1) 한 테이블을 다른 테이블과 연결해주는 역할이며, 참조되는 테이블의 항목은 그 테이블의 기본 키(혹은 단일값)
외래키 생성
(1) 예시
create table tablename
(
column1 datatype not null,
column2 datatype not null,
column3 datatype,
column4 datatype,
constraint constraint_name <- 생략 가능(자동 생성)
primary key (column1, column2)
foreign key (column3, column4) references ref_tablename(ref_column)
);
자동 생성된 constraint를 확인하는 방법
(1) 예시
show create table tablename;
외래키 삭제
(1) 예시
alter table tablename
drop foreign key FK_constraint;
(2) 삭제하면 외래키가 아니라 키 속성만 남아있다.
기존 테이블에 외래키 생성
(1) 예시
alter table tablename
add foreign key (column) references REF_tablename(REF_column);