csv 파일 import / DDL(CREATE, ALTER, DROP), DML(SELECT, UPDATE, INSERT, DELETE)

테이블에 csv 파일 데이터를 import 해서 사용하기

$ sqlite3
SQLite version 3.39.4 2022-09-29 15:55:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> .open mydb.sqlite3
sqlite> .mode csv
sqlite> .import users.csv users

데이터베이스 파일을 열고 

mode를 csv로 설정한다.

import 명령어를 사용하여 csv 데이터를 테이블로 가져온다. (테이블명 users)

 

 

 

1. DDL (CREATE TABLE, ALTER TABLE, DROP TABLE)

DDL 데이터 정의 언어 (Data Definition Language)

관계형 데이터 베이스의 구조를 정의하기 위한 명령어 (생성, 수정, 삭제)

 

 

1. CREATE TABLE

CREATE TABLE 테이블명(
컬럼1 데이터타입 제약조건,
컬럼2 데이터타입 제약조건);

형식으로 데이터베이스에서 새 테이블을 만든다.

 

 

 

CREATE TABLE contacts(
name TEXT NOT NULL, --문자열 타입
age INTEGER NOT NULL, --정수 
email TEXT NOT NULL UNIQUE);

테이블을 생성하면서 테이블 내의 각 컬럼에 데이터 타입, 제약조건을 지정했다.

PK 기본키를 정의하지 않으면 자동으로 rowid라는 컬럼이 만들어 진다.

 

 

CREATE TABLE contacts(
name NOT NULL, --문자열 타입
age NOT NULL, --정수 
email NOT NULL UNIQUE);

name은 TEXT로 데이터타입이 지정되었지만 name에 저장된 데이터 값에 따라서 타입이 동적으로 결정된다. 

그래서 데이터 타입을 선언하지 않아도 된다. 문자 '홍길동'을 데이터로 넣을 경우 TEXT타입으로 지정된다.

하지만, 데이터베이스 호환성을 위해서 데이터타입을 지정해주도록 한다.

 

 

2. ALTER TABLE

기존테이블의 구조를 수정

테이블명 변경(RENAME TO)

컬럼명 변경(RENAME COLUMN TO)

컬럼 추가(ADD COLUMN)

컬럼 삭제 (DROP COLUMN)

테이블 삭제 (DROP TABLE)

 

 

1. 테이블명 변경(RENAME TO)

ALTER TABLE table_name RANAME TO new_table_name;

 

 

2. 컬럼명 변경(RENAME COLUMN TO)

ALTER TABLE table_name RANAME COLUMN column_name TO new_column_name;

 

 

3. 컬럼 추가(ADD COLUMN)

ALTER TABLE table_name ADD COLUMN column_name2 TEXT NOT NULL;

추가해주는 컬럼에 NOT NULL 제약조건이 있다. 기존 데이터가 있을 경우에는 기존 데이터에 새롭게 추가되는 컬럼에 값이 없기 때문에 에러가 발생한다. 

 

 

ALTER TABLE table_name ADD COLUMN column_name2 TEXT NOT NULL DEFAULT 'NO DATA';

DEFAULT로 기본값을 넣어주면서 기존에 있던 데이터의 컬럼도 지정되며 오류를 해결할 수 있다.

 

 

 

4.컬럼 삭제 (DROP COLUMN)

ALTER TABLE table_name DROP DOLUMN column_name;

 

컬럼 삭제를 못하는 경우

- 컬럼이 다른 부분에서 참조되는 경우 (FOREIGN KEY 외래키)

- PRIMARY KEY인 경우

- UNIQUE 제약 조건이 있는 경우

 

 

3. DROP TABLE

DROP TABLE contacts;

존재하지 않는 테이블을 제거하면 오류가 발생한다.

여러 테이블을 제거하려면 여러 DROP TABLE문을 실행해야 한다.

DROP TABLE은 실행 취소하거나 복구할 수 없다.

 

 

 

2. DML (INSERT, SELECT, UPDATE, DELETE)

DML 데이터 조작 언어 (Data Manipulation Language)

데이터를 조작(추가, 조회, 변경, 삭제)하기 위한 명령어

 

 

1. SELECT Statement (SELECT문)

특정 테이블에서 데이터를 조회

SELECT col1, col2
FROM table_name;

SELECT 절(clause)에서 컬럼 또는 쉼표로 구분된 컬럼 목록 지정

FROM 절(clause) 에서 데이터를 가져올 테이블 지정

 

 

SELECT rowid FROM users;

정의되지 않은 rowid도 조회할 수 있다.

 

 

- ORDER BY 절

SELECT 문에 추가하여 결과를 정렬

ORDER BY절은 FROM절 뒤에 위치한다.

 

ASC (오름차순, 기본값)

DESC(내림차순)

 

SELECT * FROM USERS ORDER BY age DESC; --나이 많은 순으로
SELECT * FROM USERS ORDER BY age ASC; --나이 어린 순으로
SELECT * FROM USERS ORDER BY age; --나이 어린 순으로 (기본값 ASC)

 

 

- Sorting NULLS

NULL의 정렬 방식으로 SQLite는 NULL을 다른 값보다 작은 것으로 간주한다. ASC를 사용하는 경우 시작부분에 NULL이 표시되고 DESC를 사용하는 경우는 끝에 NULL이 표시된다.

 

 

 

- Filtering data

데이터를 필터링하여 중복 제거, 조건 설정 등 쿼리를 제어

clause : SELECT DISTINCT, WHERE, LIMIT

operator : LIKE, IN, BETWEEN

 

 

 

- SELECT DISTICT

SELECT DISTINCT select_list FROM table_name;

SELECT DISTINCT는 조회결과에서 중복된 행을 제거 한다.

DISTINCT 키워드 뒤에 컬럼 또는 컬럼 목록을 작성한다.

 

 

SELECT DISTINCT name, age FROM table_name;

각 컬럼의 중복을 따로 계산하는 것이 아니라 두 컬럼을 하나의 집합으로 보고 중복을 제거한다.

name, age가 동시에 중복된 것을 제외한다.

중복되는 name, 중복되는 age는 나올 수 있다.

 

NULL 값을 중복으로 간주하고 NULL이 있는 컬럼에 DISTINCT 절을 사용하면 NULL값의 한 행을 유지한다.

 

 

- WHERE

조회시 특정 검색 조건을 지정한다.

SELECT 문 외에도 UPDATE, DELETE문에서 사용할 수 있다.

FROM 절 뒤에 사용한다.

 

WHERE col=1
WHERE col LIKE 'Ko%'
WHERE col IN (1,2)
WHERE col BETWEEN 10 AND 20

위와 같이 연산자와 사용할 수 있다.

 

* 비교연산자 (SQLite comparison operators)

=

<>, !=

<

>

<=

>=

 

 

* 논리연산자 (SQLite logical operators)

일부 표현식의 truth를 테스트 한다. 1, 0 또는 NULL을 반환한다. 1은 true를 의미하고 0은 false를 의미한다.

ALL

AND

ANY

BETWEEN

IN

LIKE

NOT

OR

.....등

 

 

SELECT * FROM users WHERE age>=30;

나이가 30살이상인 유저를 조회한다.

 

 

 

LIKE

SELECT * FROM users WHERE name LIKE '%준';

준으로 끝나는 이름(컬럼 name)을 가진 데이터를 조회한다.

 

 

SELECT * FROM users WHERE age LIKE '2_';

age 나이가 20대인 사람들을 조회한다.

 

 

LIKE는 기본적으로 대소문자를 구분하지 않는다.

패턴 구성을 위한 와일드카드(wildcards)

% : 0개이상의 문자가 올 수 있음

_ : 단일(1개) 문자가 있음

 

와일드카드 예시로는 위와 같이 볼 수 있다.

 

 

IN 

값이 값 목록 결과에 있는 값과 일치하는 지 확인한다.

부정하려면 NOT IN 으로 사용한다.

 

SELECT * FROM users WHERE country IN ('경기도','강원도')
SELECT * FROM users WHERE country = '경기도' OR country='강원도';

경기도, 강원도에 살고 있는 사람을 조회할 수 있다.

IN으로 사용한 것을 OR로도 표현하였다.

 

 

BETWEEN

SELECT * FROM users WHERE age BETWEEN 20 AND 30;

나이가 20이상 30이하인 데이터를 조회한다.

 

 

LIMIT 

쿼리에서 반환되는 행 수를 제한한다.

LIMIT 양의정수 : 반환되는 행 수를 지정한다.

 

 

SELECT * FROM users ORDER BY age DESC LIMIT 10;

나이가 가장 많은 10명을 조회한다.

 

 

OFFSET

LIMIT 절을 사용하면 첫번째 데이터부터 지정한 수 만큼의 데이터를 받아올 수 있다.

OFFSET 과 사용하면 지정된 위치에서부터 데이터를 조회할 수 있다.

 

SELECT rowid, name FROM users LIMIT 10 OFFSET 10;

rowid 11번째 부터 20번째 데이터를 가져왔다.

 

 

 

GROUP BY

선택한 컬럼을 기준으로 데이터의 공통 값을 묶어서 결과로 나타낸다.

특정 그룹으로 묶인 결과를 생성한다.

SELECT문의 FROM 절 뒤에 작성한다. (WHERE절이 포함된 경우 WHERE절 뒤에)

각 그룹에 대해 집계함수를 사용하여 각 그룹의 추가적인 정보를 제공한다.

 

 

SELECT country, COUNT(*) FROM users GROUP BY country;

지역별 인원을 조회할 수 있다.

 

 

- Aggregate function (집계함수)

집합의 최대값, 최소값, 평균, 합계 및 개수를 계산

AVG(), COUNT(), MAX(), MIN(), SUM()

 

 

SELECT COUNT(*) FROM users;

테이블의 전체컬럼수를 조회한다.

 

 

SELECT COUNT(*) FROM users GROUP BY country;

COUNT(*), COUNT(age), COUNT(name) 등 어떤 컬럼을 넣어도 결과는 같다.

그룹 기준으로 카운팅 될 뿐이다.

 

 

SELECT COUNT(*) AS number_of_country FROM users GROUP BY country;

AS 키워드를 이용해 컬럼명을 임시로 변경해서 조회할 수 있다.

 

 

 

2. INSERT

INSERT INTO 테이블명(컬럼1, 컬럼2...)
VALUES (값1, 값2...);

새 행을 테이블에 삽입한다.

 

 

INSERT INTO classmates (name, age, address)
VALUES ('홍길동',23,'서울);

INSERT INTO classmates
VALUES('홍길동, 23,'서울');

하나의 데이터를 추가했다.

 

 

INSERT INTO classmates
VALUES ('홍길동, 23,'서울'),('김철수',22,'경기');

여러개의 데이터도 이어서 한번에 넣어줄 수있다.

 

 

 

3. UPDATE

UPDATE table_name
SET column1=new1, column2=new2
WHERE search_condition;

테이블에 있는 기존 행의 데이터를 업데이트한다.

SET절에서 테이블의 각 컬럼에 대해 새 값을 지정한다.

WHERE절의 조건을 사용하여 업데이트할 행을 지정한다. WHERE절은 선택사항으로 생략하면 테이블의 모든 행에 있는 데이터를 수정한다.

ORDER BY 및 LIMIT절을 이용해서 업데이트할 행 수를 지정할 수도 있다.

 

 

UPDATE classmates
SET name='ㅇㅇㅇ'
WHERE rowid=2;

rowid가 2인 데이터를 수정했다.

 

 

 

 

4.DELETE

테이블에서 행을 제거

테이블의 한 행, 여러 행, 모든 행을 삭제할 수 있다.

WHERE절의 조건을 사용하여 삭제할 행을 지정한다. WHERE절은 선택사항으로 생략하면 테이블의 모든 행에 있는 데이터를 삭제한다.

ORDER BY 및 LIMIT절을 이용해서 삭제할 행 수를 지정할 수도 있다.

 

DELETE FROM classmates WHERE rowid=5;

 rowid가 5인 데이터를 삭제헀다.

 

 

DELETE FROM classmates;

테이블의 모든 데이터를 삭제했다. 테이블은 남아있다.