SQL 루틴 첫번째(내가 일하기 쉽게하기 위해만든)

SQL에서 실행하는 것들의 전제는 모두

가급적이면 같은 폴더 군 위치 안에 있는 워크스페이스를 이용한다.

 

## 0 데이터 베이스 생성

 

create database zerobase default character set utf8mb4;

 

 

=================

 

## 1 sql파일을 실행하고 싶을때 방법1

 

#1 데이터베이스를 선택해준다.

source ‘경로및 파일이름’

해주면 되지만

(내가 실행해줬던 실행문은 정확히 이러했다.)

source test01.sql

 

만약 터미널 실행된 위치와 동일 선상에 해당 sql파일이 있다면 경로없이 그냥

source 파일이름.확장자

해주면 작동된다.

 

=================

 

## 2 sql파일을 실행하고 싶을때 방법2

 

1. 터미널에 mysql이 켜져있다면 그거 exit 해서 나가준다.
2. 가급적 sql파일이 위치해 있는곳 근처에서 모든 작업이 이루어지는게 수월하다. (명령어에)
3. mysql -u root -p 데이터베이스이름 < 실행원하는sql파일

(내가 실행해줬던 실행문은 정확히 이러했다.)

mysql -u root -p zerobase < test02.sql

 

=================

 

## 3 데이터 테이블 백업 - mysql에서 특정 파일

mysqldump -u username -p dbname > backup.sql

(내가 실행해줬던 실행문은 정확히 이러했다.)

mysqldump -u root -p zerobase > zerobase.sql

 

# 약간 다르지만 schema back up

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

 

==================

 

## 4 데이터 테이블 백업 - 모든 파일 백업

mysqldump -u username -p —all-daatabases > backup.sql

 

# 모든 schema back up

mysqldump -d -u username -p username -p dbname > backup.sql

 

==================

 

## 5 백업 후 리스트 확인

ls

 

==================

 

## 6 데이터베이스 백업

# 이전의 것은 데이터베이스 > 테이블을 백업

# 이번의 것은 데이터베이스 자체이므로 덩어리가 같거나 클 수 밖에 없다.

 

mysqldump —set-gtid-purged=OFF -u root -p dbname > filename.sql

 

===================

 

## 7 데이터베이스 생성

 

create database dbname default character set utf8mb4;

 

=====================

 

## 8 (계정)유저생성

 

# 1 유저 확인

• use mysql
• select host, user from user

 

# 2 유저가 없다면 유저생성

CREATE USER ‘user_name’@‘%’ IDENTIFIED BY ‘password’;

# 여기서 주의할 점은 user_name은 원하는대로 바꿔줘라. 유저명이다.

# password 칸도 마찬가지로 말 그대로 비밀번호다.

# user_name’@‘%’ 이것에서 @는 유저 생성의 필수이고, %는 외부접속이 가능하게 한다는 뜻이다.

# 여기서 password는 차후에 ipython으로 접속할 때, 반드시 필요한 부분이니 기억해야한다.

 

=====================

 

## 9 권한부여

 

#1 권한 확인

show grants for ‘user_name’@‘%’;

 

#2 권한 부여

grant all on 데이터베이스.* to ‘user_name’@‘%’;

# 여기서 *는 “모든”을 의미한다. (sql전체적으로 *는 전체 를 의미함)

 

#3 권한 뺏기

revoke all on password.* to ‘user_name’@‘%’;

 

=======================

 

## 10 테이블 값 넣을때

 

INSERT INTO 테이블명 VALUES (해당 테이블에 부여된 디스크립션에 따라서 값 넣어주기);

# 혹시라도 파일화 하여 source 파일명 으로 풀어주려고 할때, 파일을 먼저 “저장”해야지 작동이 된다.

 

========================

 

## 11 파일화 시킨 테이블생성, insert into, 데이터베이스 생성 등 실행 명령

 

내가 넣기를 원하는 데이터베이스에 들어가서 source 파일명.확장자

 

========================

 

## 12 테이블 삭제 할때

 

#1 완전히 테이블 통째로 삭제

drop table tablename;

 

#2 테이블의 프레임은 남겨놓되, 안의 값은 모두 삭제

delete from police_station;

 

========================

 

## 13 가상환경 만들어주기

 

# 이건 왜 만들어야하는지 정확히는 아직 모르겠으나, 암튼 만드는 방법!

conda create -n 가상환경명 python=버전

 

========================

 

## 14 mysql을 터미널이 아닌, ipython (쉽게 주피터노트북)으로 접속하는 코드

 

import mysql.connector

 

mydb = mysql.connector.connect(

host = ‘엔드포인트(aws-rds기준) or root등 hostname(로컬 mysql기준)’,

# 만약에 aws-rds로 접속한다면

port = ‘port’,

user = ’username’,

password = ‘password’,

# 만약 접속 희망하는 데이터베이스를 지정한다면

database = ‘database’

)

 

내가 하고자 하는 코드 입력

 

# 이거는 반드시 쓸 필요는 없다만, 켜둔상태로 내비두면 과부하 걸릴 수도 있다고 함.

mydb.close()

 

===========================

 

## 15 - 14번과 연계기로 쓰이는 건데 테이블 생성 이다.

 

14번 내용 이후에

 

cur = 접속코드.cursor()

cur.execute(‘CREATE TABLE 테이블명 (테이블 속성)’)

(예를 들자면)

cur = local.cursor()

cur.execute(‘CREATE TABLE sql_file (id int, filename varchar(32))’)

 

=============================

 

## 16 연계기 테이블 삭제

 

15번의 내용과 동일하며

 

cur = local.cursor()

cur.execute(‘DROP TABLE sql_file’)

 

이렇게 바뀐다.

 

==============================

 

## 17 연계기 SQL 파일 실행 코드

 

15번의 내용과 동일하며

 

#1 단일 쿼리 SQL 일때

cur = local.cursor()

sql = open(‘filename.sql’).read()

cur.execute(sql)

 

이렇게 바뀐다.

 

#2 복수 쿼리 SQL 일때

cur = local.cursor()

sql = open(‘filename.sql’).read()

result = cur.execute(sql, multi=True)

# 여기서 result는 차후에 반복문 처리를 하여 따로 빼기 때문에 이렇게 일단 만들어 준다.

 

#2-1 복수 쿼리 SQL 일때 (반복문 버전)

#2의 result = cur.execute(sql, multi=True) 대신에

 

for result_iterator in cur.execute(sql,multi=True):

    if result_iterator.with_rows:

        print(result_iterator.fetchall())

    else:

        print(result_iterator.statement)

 

 

# 쿼리는 연속된 입력문을 의미한다.

insert into values ; 이렇게 하나만 있으면 단일이고

insert into values ; 이렇게 ;로 끊기는게 하나 더 있으면 복수다.

# 즉, ;마다 1쿼리라고 보면 된다.

 

==============================

 

## 18 SQL에서 읽어올 양이 많은 경우

 

# 이렇게 처리해준다.

cur = local.cursor(buffered=True)

 

# 좀 더 구체적으로는 

 

cur = local.cursor(buffered=True)

cur.execute(‘SELECT * FROM sql_file’)

 

# 여기서 fatchall()은 row를 포함하고 있는값? 이라고는 하는데, 아직 명확히 잘 모르겠다.

result = cur.fetchall()

for result_iterator in result:

print(result_iterator)

 

local.close()

 

==================================

 

## 19 커밋 (데이터베이스에 적용하기 위한 명령 이라고 한다.)

 

보통 14번의 mydb와 같이 mysql 접속문과 함께 쓰인다.

mydb.commint()

 

===================================

 

## 20 이미 SQL화 되어있는 입력문들을 반복문으로 확인 및 가져온다. 

(이것은 이미 SQL화가 되어있든 아니든 상관없다. 원리는 같으니깐)

 

import mysql.connector

 

remote = mysql.connector.connect(

    host = ‘엔드포인트’,

    port = 3306,

    user = 'admin',

    password = ‘패스워드’,

    database = ‘접속희망데이터베이스’

)

 

cur = remote.cursor(buffered=True)

cur.execute('SELECT * FROM sql_file')

result = cur.fetchall()

result

# 여기서 이미 result라는 변수에 해당 내용들을 담아주고

 

for result_iterator in result:

    print(result_iterator)

# 이렇게 print해주지만, 사실상 print가 아니라, 어디에 넣는것도 상관은 없다.

 

remote.close()

 

========================================

 

## 21 내용 판다스 전환 작업

 

20번에서 진행되었던 내용을 통해서 result에 무슨 값이 있었는지 확인하고나서

내가 판다스로 데이터프레임화 시키겠다고 결심했다면

 

import pandas as pd

 

df = pd.DataFrame(result)

 

이렇게 된다.

df라는 변수 안에 result를 판다스 데이터프레임화를 시켜준 것이다.

 

=========================================

 

## 22 mysql 관련 궁금한점 구글링 키워드

 

mysql.connector execute
이런식으로 쳐주고 본다.

 

=========================================

 

## 23 cursor 만들기

 

20번에 이어서

cursor = remote.cursor(buffered=True)

 

==========================================

 

## 24 insert 문 만들기

 

# 컬럼의 수만큼 포매팅을 해준다.

# 내가 진행하는 실습 예제파일의 컬럼이 2개 였기 때문에 %s를 2개 넣어준것이다.

# 그 내용이 숫자든 뭐든 상관없다. 무조건 %s로 가면 된다.

sql = 'insert into 테이블명 values (%s, %s)'

 

===========================================

 

## 25 SQL 루틴 1

 

# 첫번째

# 변수 설정 및 데이터를 데이터베이스에 넣기

import pandas as pd

 

# 사용하고자 하는 csv파일을 df라는 변수에 이렇게 담아준다.

df = pd.read_csv('police_station.csv')

 

cursor = remote.cursor(buffered=True)

 

sql = 'insert into 테이블명 values (%s, %s)'

 

# 위에서 설정된 df, sql을 토대로

# cursor에 설정된 데이터베이스 공간에 execute(실행) 해준다.

# 이를 지저분하게 들어가면 안되고, 변형되면 안되기 때문에 tuple 형태로 넣어주는 거고

# 20번에 설정된 remote라는 데이터 베이스 접속 창구에 commit()시켜주는 것이다.

# commit()이 쉽게, 데이터를 집어 넣는거라고 보면 된다.

 

for i, row in df.iterrows():

    cursor.execute(sql, tuple(row))

    print(tuple(row))

    remote.commit()

 

 

# 두번째

# 첫번째에서 넣어준 변수결과를 확인및 다음 작업을 위한 변수설정

 

cursor.execute('select * from police_station')

result = cursor.fetchall()

result

 

# 잘 담겼다면, 다시 판다스화 해준다.

df = pd.DataFrame(result)

df.tail()

 

 

# 즉, 이 SQL루틴1을 풀이하자면

#1 csv파일을 판다스로 전환하고

#2 판다스로 전환된 것을 SQL의 데이터베이스 공간에 넣어주고

#3 데이터베이스 공간에 들어온 판다스를 내용 확인하고

#4 해당 데이터베이스 공간 안의 판다스를 다시 변수로 빼내어

#5 다시 제 2의 판다스로 설정해준다. 고 볼 수 있다.

 

판다스 결과값 자체에는 컬럼값, 행 값은 변형이 생길 수 있으며,

데이터 벨류(값)자체에는 변함이 없다.

다만 “데이터프레임”화 시켜준다.

물론, 1. 읽어오고, 2. 바로 데이터프레임화 시켜주거나, 이걸

하나의 실행 안에 녹여낼 수도 있지만, 아무튼 단계별로 나누어서

단계를 보고 과정을 실험하는게 중요하다고 생각한다.

 

그리고 이 과정에서 sql로 데이터베이스에 데이터 저장되는 결과

이렇게 2가지 결과를 초래할 수 있다.

 

====================================

 

## 26 SQL 루틴 2

 

import pandas as pd

# 이것도 읽어올때, 컬럼이 4개짜리였고, 행이 310개 였다. utf-8로는 읽히지가 않아서 euc-kr을 이용했다.

df = pd.read_csv('2020_crime.csv', encoding='euc-kr')

 

# insert 쿼리 작성 준비

# 20번의 remote에 설정된 영역에 데이터를 넣어줄 준비

# 앞서 말했다시피, 컬럼이 4개이기 때문에 %s를 4번써주었다.

# 하지만 %s 앞의 2020는 %s는 별개로 컬럼추가로 들어간다.

# 즉, 원본파일은 컬럼값이 4개였을지언정, 새로 remote로 쏘는 데이터는 컬럼값이 5개가 된다.

# 여기서는 연도별 구분을 위해 이런 값을 설정해주었던것 같다.

 

sql = 'insert into crime_status values ("2020", %s, %s, %s, %s)'

cursor = remote.cursor(buffered=True)

 

# 데이터를 crime_status 테이블에 insert

 

for i, row in df.iterrows():

    cursor.execute(sql, tuple(row))

    print(tuple(row))

    conn.commit()

 

위의 과정은 중요하며, 과정확인을 위해 print가 쓰인것이지

print가 필수요소는 아니다.

 

 

# crime_status 테이블의 데이터 조회

 

# 이건 왜 굳이 해줘야하는지 잘 모르겠다.

# 이걸 사용하지 않고 실행하자 'InterfaceError: No result set to fetch from' 에러가 떴다.

# 아무래도 ‘cursor’ 라는 변수 자체에 값을 설정해주는 기능을 하는게 있는것 같다.

cursor.execute('select* from crime_status')

 

# fetchall도 정확히 무슨 기능인지는 모르겠다.

result = cursor.fetchall()

 

# 이건 역시 확인을 위해 쓰인것이라 필수 요소는 아니고.

for row in result:

    print(row)

 

# 판다스 데이터프레임화

 

df = pd.DataFrame(result)

 

 

# 여기서 깊이, 확실하게 알고 넘어갔음 하는 부분은 iterrows, cursor, fetchall

# 이렇게 3개다.

 

====================================================

 

## 27 SQL 루틴 3

 

import mysql.connector

 

conn = mysql.connector.connect(

    host = ‘엔드포인트’,

    port = 3306,

    user = ‘유저(계정)명’,

    password = ‘비밀번호’,

    database = ‘데이터베이스 명’

)

 

# cctv 테이블 생성시, 나는 쿼리를 먼저 만들려고 했으나,

# 쿼리를 만드려고 한다면 반드시 기반하는 파일의 형태를 먼저 알아야한다.

import pandas as pd

 

df = pd.read_csv('Seoul_CCTV.csv', encoding='utf-8')

df.tail(2)

 

# **** 여기 셀은 실행 안하기 ******

# 컬럼이 6개이고, 행이 25개인건 확인했다.

# 확인한 상태에서 내가 만들고자 했던 쿼리다.

# 물론 맞지 않는 방향성을 띄고 있다.

sql = 'create table cctv ("%s, %s, %s, %s, %s, %s")'

# **** 여기 셀은 실행 안하기 ******

 

# csv 데이터를 데이터베이스에 넣어주기 전 밑작업 및 “테이블 프레임”넣어주기

sql = 'create table cctv (기관명 varchar(8), 소계 int, 2013년도이전 int, 2014년 int, 2015년 int, 2016년 int)'

# cursor를 작성하는것도 기억이 안났다.

cursor = conn.cursor(buffered=True)

# cursor를 어떻게 실행해줘야 하는지도 기억이 안났다. execute(sql, cursor) 이렇게 생각하고 있었다.

cursor.execute(sql)

 

# cctv 테이블에 데이터를 넣어주기

# 바로 위에서 “테이블프레임”을 미리 넣어줬기 때문에 이 작업이 가능한거다.

sql = 'insert into cctv values (%s, %s, %s, %s, %s, %s)'

cursor = conn.cursor(buffered=True)

 

for i, row in df.iterrows():

    cursor.execute(sql, tuple(row))

    print(tuple(row))

    conn.commit()

 

# 데이터베이스에 있는 파일 읽기 및 result 변수에 저장

 

cursor.execute('select* from cctv')

result = cursor.fetchall()

 

for row in result:

    print(row)

 

# 데이터프레임화

 

df = pd.DataFrame(result)

df.tail(2)

 

============================================

 

## 28 프라이머리키 포함하여 테이블 생성하기

 

create table animal 

    -> (

    -> name varchar(16) NOT NULL,

    -> type varchar(16) NOT NULL,

    -> age int,

    -> primary key (name, type)

    -> );

 

==========================================

 

## 29 프라이커리키 삭제

# 단일도 복수도 똑같다.

alter table 테이블명

drop primary key;

 

============================

 

##30 프라이머리키 지정

 

# 단일

alter table 테이블명

add primary key (프라이머리키 지정 희망하는 필드값)

 

# 복수

alter table 테이블명 add constraint PK_테이블명 primary key (id값1, id값2);

(예시)

alter table animal add constraint PK_animal primary key (name, type);

 

===============================

 

## 31 foreign key 생성

# foreign key는 테이블당 하나가 한계가 아니라, 2개 이상이 쓰일 수도 있다.

# 전제조건으로는 테이블이름1,2가 같은 데이터베이스 안에 있어야 하는것 같으며 (참조만 잘 해주면 달라도 괜찮지 않을까 추측은 해본다)

# 해당 테이블이름1,2가 속해있는 데이터베이스를 미리 선택해줘야한다.

 

CREATE TABLE 테이블이름2

(

oid int not null,

order_no varchar(16),

pid int,

PRIMARY KEY (aid),

# 여기서부터 foreign key의 유효한 부분인건데

# CONSTRAINT 는 잘 모르겠다. 그리고 필수 기입사항은 아니다.

# 현재 테이블인 테이블이름2의 pid를 foreign key로 지정해 줬다.

# 그리고 참조할 것은 테이블이름1의 필드를 참조한다.

CONSTRAINT constraint로지정하는이름 FOREIGN KEY (pid) REFERENCES 테이블이름1 (테이블이름1의필드)

)

 

CONSTRAINT 값을 지정 안해줄 경우, 어쨌든 임의로 생긴다.

이를 확인하는 방법은

show create table 테이블명; 으로 확인 가능하다.

# 예시 코드 1

# constraint 있는 버전

 

 

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) );

 

 

# 예시 코드 2

# constraint 없는 버전

 

create table job ( jid int not null, name varchar(16), pid int, primary key (jid), foreign key (pid) references person(pid) );

 

=======================================

 

## 32 constraint 조회 방법

 

show create table 테이블명;

 

=======================================

 

## 33 foreign key 삭제

 

# 31에 연계된다는 전제하에 쓰여진 코드

 

ALTER TABLE 테이블이름2

DROP FOREIGN KEY constraint로지정하는이름;

# constraint는 뭔가 foreign key의 태그네임 같은 느낌 같다. 없어도 되지만, 있으면 그게 대표화되는.

 

# 처음에 지정된 constraint 값을 모르겠다면

# show create table 테이블명을 했을때 constraint값에 해당 내용이 나온다.

 

예시

alter table study

drop foreign key study_ibfk_1;

 

=========================================

 

## 34 foreign key 지정

 

alter table 테이블명1

add foreign key (테이블명1의필드 중 1) references 테이블명2 (테이블명2의 필드중 1);

 

# 예시

 

alter table orders

add foreign key (pid) references person(pid);

 

=========================================

 

 

## 35 몇개의 행이 있는지 세는 코드 (count)

 

select count(distinct 컬럼명) from 컬럼명이속해있는테이블명;

 

# distinct는 “중복을 제거” 한다는 의미다.

 

==========================================

 

## 36 판다스의 head와 같이 상위 출력을 지정한 수 만큼 끊어주는 코드

 

# 3개만 끊어서 출력해주는것. 포인트는 limit 3; 이다.

select * from 테이블명 limit 3;

 

==========================================

 

## 37 group by 로 합쳐주기

 

# group by를 위해서 보통 count 로 확인 후 진행한다.

# 행의 수가 같아야 병합이 가능하기 때문이다.

 

# 이건 코드 그대로 썼으니, 활용점은 충분히 찾을 수 있으리라고 본다.

# 활용점을 못찾겠다면, sql 기초부터 다시 배워야 한다.

 

# 2번째 줄에 있는 알리아스(alias = as) 되어있는것을 쓰고있는 것이다.

# 의미인 즉슨 crime_status 테이블의 police_station 컬럼, police_station 테이블의 name 이다.

select c.police_station, p.name

 

# 파일을 crime_status, police_station으로부터 가져오겠다는 의미이며,

# 각각 알리아스로 c, p로 불러주겠다는 의미다.

from crime_status c, police_station p

 

# police_station 테이블의 name 컬럼을

# 서울 + crime_status의 police_station 컬럼 + 경찰서 라는 이름으로 바꿔주겠다.

where p.name like concat(‘서울’, c.police_station, ‘경찰서’)

 

# crime_status 테이블의 police_station 컬럼과 

# 3번째 줄에서 변경된 p.name을 병합(group by)해서 보여주겠다.

group by c.police_station, p.name;

 

=============================================

 

## 38 update

 

# 이걸 하면 어떻게 바뀌는지 잘 모르겠다.

# 머리에 정리가 되기도 전에 다음으로 넘어가네;

# 더 공부하자 이 부분은

 

update crime_status c, police_station p

set c.reference = p.name

where p.name like concat('서울', c.police_station, '경찰서');

 

=============================================

 

챕터15 2강부터