SQL과 데이터베이스

tidy데이터 : 깔끔한 데이터. 행과 열의 역활이 명확한 데이터.
데이터 분석, 머신러닝을 하기 위해서는 데이터를 tidy하게 관리해야한다.

create table Franchisee(
						franchiseeID integer primary key,
                        franchiseename text not null );
insert into franchisee values(111, 'John');
insert into franchisee values(222, 'Paul');
insert into franchisee values(333, 'David');

create table store(
				storeid integer primary key,
                storename text not null,
                franchiseeid integer not null,
                foreign key(franchiseeid) references franchisee(franchiseeId) );

foreign key를 이용하여 테이블 사이의 관계를 설정한다. store 테이블의 franchiseeid와 franchisee테이블의 franchiseeid연결

insert into store values(1, '711 gangnam', 111);
insert into store values(2, '711 MUNJEONG', 113);
insert into store values(3, '711 YOUIDO', 114); -- NOT OK
DROP TABLE FRANCHISEE; -- NOT OK
DROP TABLE STORE;

외래키는 연결되어 있는 테이블의 있는 값만 사용가능하다.
연결되어 있는 테이블을 먼저 삭제는 불가하다. 연결되어있는 테이블이 하나도 없을 경우 테이블 삭제가 가능하다.

JOIN

왼쪽 테이블과 오른쪽 테이블을 합친다. 기본적으로 두개의 테이블을 합친다.
가장 기본적인 조인은 inner join이다.

select a.name, b.item_id, b.quantity, b.price from customer as a inner join sale as b on a.id = b.customer_id; -- inner join

select a.name, b.item_id, b.quantity, b.price from customer as a full left join sale as b on a.id = b.customer_id; -- full left join

SQLite에서는 full left join과 inner join만 지원한다.
inner join은 왼쪽 테이블과 오른쪽 테이블의 공통값만을 이용하여 왼쪽 오른쪽 붙여서 새로운 테이블을 만드는 것
full left join은 오른쪽 테이블의 공통된 값만 빼고 나머지는 지운다. 이는 오른쪽에 결측치가 생길 수 있다.
right join은 left join 과 반대로 왼쪽의 공통된 값만 빼고 나머지는 지우는 것이다.
full outer join은 공통된 부분만 연결하고, 나머지 값은 이어붙여준다.

view 생성과 삭제

view는 테이블이 아니라 보여주는 방식이다. 보여주는 방식만을 저장해둔다. 이를 실행시 테이블 형식으로 보여주나, 할당되어있지 않다.

create vuiew myView1 as select name as country, population / 1000 as 
'pop(1000s)' from country;
create view myView2 as select name, continent from country where name like '_a%';
select * from myView1;
drop view myView1;

view를 생성하는 방법. view는 출력결과를 테이블의 형태로 저장하는 것이다.
select를 통해서 view를 부를수 있으며, drop을 이용하여 삭제도 가능하다.

SQL함수

문자열 함수

x || y : 문자열 x와 y를 이어붙여준다.
LENGTH(x) : 문자열 x의 길이. 각 행별로 적용된다.
SUBSTR(x, n, m) : x=문자열, n=시작위치, m=가져올 문자 개수
TRIM(x) : x 문자열에서 공백(스페이스)를 제거해준다.(LTRIM(), RTRIM())
UPPER(x), LOWER(x) : 문자열 x를 모두 대문자, 소문자로 바꿔서 출력
REPLACE(x, y, z) : 문자열 x에서 y를 z로 대체

수치 연산용 함수

ROUND(x, n) : x소수점 이하 n자리까지 출력.
ABS(x) : x의 절대값 출력.

날짜와 시간 관련 함수

DATE('now') : 현재 날짜 출력
TIME('now') : 현재 시각 출력. 한국시각으로 출력하고자 할때는 'localtime'이라는 인수를 추가해주면 된다.
DATETIME('now') : 날짜와 시각 출력.
STRFTIME(<%d(일), %m(월), %Y(년), %w(요일)>, 'now') : 원하는 포멧으로 날짜, 시각 출력.(요일은 일요일(0)부터 1씩증가하며 숫자로 표현한다)

집계 함수(컬럼 = 열)

AVG(X) : 그룹별 X컬럼의 평균을 구한다.
SUM(X) : 그룹별 X컬럼의 합을 구한다.
COUNT(X) : X컬럼의 카운트를 세어준다.
MIN(X), MAX(X) : X컬럼의 최대값, 최소값을 구해준다.

PYTHON에서의 접근

python 에는 SQLite가 이미 설치되어 있다(anaconda로 설치한 경우)

import sqlite3
conn = sqlite3.connect('customer.db') # 신규 생성 또는 접속
cur = conn.cursor() # 커서 객체
cur.execute("""CREATE TABLE customers(
				first_name text, last_name text, email text)""")
conn.commit() # 확인

sql = "INSERT INTO test VALUES (?,?,?)
x = [2, 'This', 'That']
cur.execute(sql, x)
conn.commit() # 확인
conn.close()  # 연결 종료

커서 객체를 만들면 SQL커맨드를 쉽게 처리가 가능하다.
?를 사용하면 외부 변수로부터 값을 입력받아 사용이 가능하다.
commit()을 실행하면 DB에 적용되어 SQLite에서 볼 수 있다.

sql = 'SELECT * FROM test"
cur.execute(sql)
res = cur.fetchall()
for x in res:
	print(x)

fetchall을 이용하여 출력된 값들을 모두 가져온다. res는 리스트 타입이지만, 리스트의 요소는 한 행당 튜플로 되어있다.