정형데이터 (2) - SQL
1. SQL 구문 기본 구조
- 문(Statement), 절(Clause), 식(Expression), 술어(Predicate)
- SQL문을 사용할 때 굳이 대문자를 쓰지는 않아도 되지만, 명령에 해당하는 것들은 관습적으로 대문자로 쓰며, 테이블명, 파라미터 등은 소문자로 쓴다.
- 하나의 문을 끝낼 때 ;을 사용한다.
-- Statement : 전체구문
-- Expression : population + 1, 'USA'
-- Predicate : name = 'USA'
UPDATE country -- UPDATE clause
SET population = population + 1 -- SET clause
WHERE name = 'USA'; -- WHERE clause
2. Data Type
RDBMS마다 조금씩 차이가 있을 수 있다.
- Boolean (BOOLEAN)
- Character (CHAR, VARCHAR)
- 길이 제한: CHAR(3)을 사용하면 ‘IN’, ‘OUT’은 원래대로 출력되지만 ‘INOUT’은 제대로 출력되지 않는다.
- Exact numeric (NUMERIC, DECIMAL, INTEGER, SMALLINT, BIGINT)
- Approximate numeric (REAL, FLOAT, DOUBLE)
- Datetime (DATE, TIME, TIMESTAMP)
- Large Object (CLOB, BLOB)
- CLOB: Character String
- BLOB: Binary String
3. DDL, DML
(1) DDL
- CREATE
- DROP
- 테이블 자체를 제거
- TRUNCATE
- 테이블 내의 모든 데이터를 삭제할 때 사용
- DELETE보다 빠름
- ALTER
(2) DML
-
SELECT
-
SELECT [ALL | DISTINCT] 컬럼명 [,컬럼명...] FROM 테이블명 [,테이블명...] [WHERE 조건식] [GROUP BY 컬럼명[,컬럼명...] [HAVING 조건식]] [ORDER BY 컬럼명[,컬럼명...]] - 모든 컬럼을 가져오고자 할 때는
*사용 -
WHERE절에 사용할 수 있는 연산자
Operator Description = Equal <> Not equal > Greater than < Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern (%, _) IN To specify multiple possible values for a column
-
- INSERT
-
INSERT INTO 테이블명 (컬럼명) VALUES 입력값 - 컬럼의 수와 입력값의 수는 일치해야 함
- 컬럼명을 입력하지 않으면 모든 컬럼에 입력된다는 뜻
-
- UPDATE
-
UPDATE 테이블 SET 컬럼명 = 값, 컬럼명 = 값,... WHERE 조건 - WHERE절을 쓰지 않으면 모든 데이터가 바뀔 수 있으므로 조심할 것
-
- DELETE
-
DELETE FROM 테이블명 WHERE 조건 - 하나 이상의 row를 삭제할 때 사용
- WHERE절을 쓰지 않으면 모든 데이터가 삭제될 수 있음
- 데이터만 지워지는 것이므로 모든 데이터를 지워도 auto increment가 되어 있으면 새로운 값을 입력했을 때 1부터 시작 안할 수 있음.
TRUNCATE는 1부터 시작 가능
-
4. JOIN
- INNER JOIN
-
SELECT 테이블명.컬럼명, 테이블명.컬럼명... FROM 테이블명 INNER JOIN 테이블명 ON 테이블명.컬럼명 = 테이블명.컬럼명
-
- LEFT JOIN
-
SELECT 테이블명.컬럼명, 테이블명.컬럼명... FROM 테이블명 LEFT OUTER JOIN 테이블명 ON 테이블명.컬럼명 = 테이블명.컬럼명
-
- RIHGT JOIN
-
SELECT 테이블명.컬럼명, 테이블명.컬럼명... FROM 테이블명 RIGHT OUTER JOIN 테이블명 ON 테이블명.컬럼명 = 테이블명.컬럼명
-
- FULL OUTER JOIN
-
SELECT 테이블명.컬럼명, 테이블명.컬럼명... FROM 테이블명 FULL OUTER JOIN 테이블명 ON 테이블명.컬럼명 = 테이블명.컬럼명
-
- CROSS JOIN
- 조합 가능한 모든 종류의 쌍 생성
- 사용하지 않는 게 좋음
-
SELECT * FROM 테이블명 CROSS JOIN 테이블명;
5. SQLite
- fileDB
- 서버가 필요 없다.
- 다른 RDBMS들보다 간단하지만, 속도는 좀 더 느리다.
- 동시에 여러 명이 작업하거나 접근권한을 나누는 등의 기능은 지원하지 않는다.
- 아나콘다를 설치하면 기본적으로 탑재되어 있다.
- jupyter notebook에서 작업할 수 있다.
- 데이터 타입은 NULL, INTEGER, REAL, TEXT, BLOB을 지원한다.
import sqlite3
(1) Database 연결, Cursor 생성
데이터베이스 연결을 파일로 한다면, DB Browser for SQLite를 사용해 데이터가 제대로 입력/수정/삭제되고 있는지 확인해볼 수 있다. 단, 데이터 입력/수정/삭제의 작업을 하고 나서 conn.commit()을 해줘야 해당 내용이 DB에 반영된다.
conn = sqlite3.connect('경로/이름' or ':memory:')
cur = conn.cursor()
(2) execute / executemany / executescript
sql문을 실행시키는 방법이며, qmark style, named style을 사용할 수 있다.
execute
하나의 sql문을 하나의 parameter sequence에 대해 실행한다.
cur.execute(sql문[,parameters])
cur.execute('CREATE TABLE people (name_last, age);')
-
qmark style
# 데이터 2개가 들어간다는 뜻 who = 'Eunkyung' age = 25 cur.execute('INSERT INTO people VALUES (?, ?)', (who, age)) -
named style
# dictionary 형태 cur.execute('INSERT INTO people VALUES(:a, :b)',{'a': who, 'b': age}) cur.execute('SELECT * FROM people WHERE name_last=:who AND age=:age', {'who': who, 'age': age})
executemany
하나의 sql문을 여러 개의 parameter sequences에 대해 실행한다.
sql = 'INSERT INTO people VALUES (?, ?)'
curData = [('A', 1), ('B', 2), ('C', 3)]
cur.executemany(sql, curData)
executescript
여러 개의 sql문을 한 번에 실행할 수 있다.
cur.executescript('''
DROP TABLE IF EXISTS person;
CREATE TABLE person (
first_name TEXT PRIMARY KEY
last_name TEXT NOT NULL
);
INSERT INTO person VALUES ('name', 'kim')
''')
(3) fetchone / fetchmany / fetchall
cur.fetchone(): 쿼리 결과의 다음 row를 하나의 sequence로 반환한다.cur.fetchmany(size): 쿼리 결과이 다음 row의 집합들을 리스트로 반환한다.-
cur.fetchall(): 쿼리 결과의 남아있는 모든 row들을 리스트로 반환한다. - 작업이 모두 끝나면
cur.close(),conn.close()를 해주는 것이 좋다.
(4) 기타 명령어
cur.lastrowid: 가장 최근에 입력된 row의 pk값을 가져온다.- SELECT문을 수행한 후
cur.fetchone()[0]으로 가져온 결과와 같다.
- SELECT문을 수행한 후
cur.rowcount: 수정한 row의 개수를 볼 수 있다.list(conn.iterdump()): 커서가 작업했던 결과물을 볼 수 있다.
Leave a comment