SQLite

SQLite에서 데이터베이스를 생성하기 위해서는 서버는 필요하지 않다. 단순히 파일하나만을 작성하면 된다. 

 

데이터베이스 작성

touch database.db

 

 

여기서 .db확장자를 붙인 이유는 SQLite와는 관계성이 없고, SQL IDE의 Beekeeper가 인식할 수 있도록 하기 위함이다 

아무 확장자를 붙여도 상관없이 SQLite에서는 동작할 것이다

---

DDL

데이터베이스가 어떤 데이터를 가질지 정의하는 명령어

 

CREATE TABLE

데이터 베이스 안의 테이블을 작성 뒤에 STRICT를 붙이면 타입을 확인함.

-- 타입을 적어주지 않아도 생성가능
CREATE TABLE 테이블명 (
    열 이름,
    열 이름
);

-- 타입 명시, SQLite에서는 False, True같은 boolean 타입을 지원하지 않기에 Integer타입으로 0, 1로 구별
-- STRICT를 명시해주지 않을 경우, 다른 타입이 들어와도 입력가능.
CREATE TABLE 테이블명 (
    열 이름 TEXT,
    열 이름 INTEGER , -- 정수
    열 이름 REAL , -- 실수
    열 이름 BLOB -- 바이너리 데이터 ( 이미지 파일 등 ) 비권장사항
) STRICT;

 

Constraint

말 그대로 제약사항이다.

테이블을 생성할 때 Null이 들어올 수 없다던지, 유니크한 값만 들어올 수 있도록, 혹은 기본 값등을 지정할 수 있다.

Constraint끼리는 순서가 상관없지만, 타입보다 먼저 명시되서는 안된다. ( NOT NULL TEXT )

CREATE TABLE 테이블 명 (
  컬럼 명 TEXT NOT NULL UNIQUE --NULL값들어올 수 없음, 유일한 값
  컬럼 명 INTEGER DEFAULT 0 --기본값 0
)

 

CHECK Constraint

만약 값의 범위를 제한하고싶은 경우 (예를들어 0과1의 값만, 혹은 양의 정수만)  단순한 제약사항만으로는 구현하지 못한다.

이 때 필요한 것이 CHECK Constraint이다. check의 괄호안의 값이 true일 경우에만 입력이 가능하다. 

 

이때 흥미로운 점이 테이블에 STRICT를 붙이지 않는경우, 제약사항은 작동한다. ( 예를들어 NOT NULL인 경우 값을 입력해야한다던가 ) 다만, 다른 타입의 값을 입력 시 문제없이 입력되는 경우도 있다. ( 예를들어 텍스트에 정수를 입력)

CREATE TABLE 테이블 명 (
  for_kids INTEGER NOT NULL DEFAULT 0 CHECK (for_kids between 0 and 1) 
)

 

Function

CHECK Constraint만으로는 값을 보장할 수 없는 경우가 있다.

예를들어 텍스트의 길이가 100자 이하인 경우. 이 때에는 함수를 이용해야한다.

CREATE TABLE 테이블 명 (
 컬럼 명 TEXT CHECK ( length(해당 컬럼 명) < 100 )
);

 

Primary KEY

각 데이터 ( row )를 서로 구분하기 위해 unique한 값이어야하며, 변경이 불가능하도록 해야함.

자연 기본키와,대체 기본키 이 두가지의 종류가 있다.

 

기존에 존재하는 데이터의 일부의 값을 기본키로 하는 것이 자연 기본키이다.

예를들어 movie라는 테이블에 title이라는 값이 있다면 이를 기본키로 지정하는 것.

 

반대로 대체 기본키는 새로운 컬럼명을 생성한 뒤, 그를 기본키로 지정하는 것이다.

예를들어 movie라는 테이블의 movie_id라는 컬럼 명을 추가한 뒤 이를 기본키로 지정.

이 자연 기본키(title)은 실제 데이터로써 이용되는 값 중 일부이며,

대체 기본키는 실질적인 데이터의 값과는 관련이 없는 인위적인 값이다. ( 1 2 3 4 ) 단지 고유 식별자가 필요하기 때문이다.

자연 기본키를 불변하고 고유하게 유지하는 것은 어렵기 때문에 보통 대체 기본키를 사용하는 것이 일반적이다.

앞으로 말하는 기본키는 모두 대체 기본키를 의미한다.

 

앞서 테이블을 만들 때 기본키를 지정하지 않았는데, 실제로는 뒤에서 시스템이 고유 식별자를 생성해주었기 때문이다.

 

-- AUTOINCREMENT를 이용하면, 삭제 된 값을 재 이용하지 않고, 마지막 값의 다음 값으로 추가
-- 이는 SQLite에 특화된 기능이다. 
CREATE TABLE 테이블 명 (
  컬럼 명 INTEGER PRIMARY KEY AUTOINCREMENT 
) ;

 

AUTOINCREMENT를 지정하면 sqlite_sequence테이블이 자동으로 생성된다.

 

 

 

DROP TABLE

 

따로 확인메시지 등이 나오지 않기때문에 테이블을 삭제할 때는 주의할 것.

DROP TABLE 테이블명;

 

 

---

DML

데이터 조작언어. 데이터 베이스의 데이터를 조작할 수 있음. ( 조회, 입력 ... )

실제로 데이터베이스에서 가장 많이쓰이는 언어.

 

Update 쿼리 ( INSERT, UPDATE, DELETE )

 

INSERT 

데이터를 테이블에 입력하는 것.

-- 단일 데이터
INSERT INTO 테이블 명 VALUES (
    값,
    값
);

-- 복수 데이터
INSERT INTO 테이블 명 VALUES 
	( 값, 값 ),
	( 값, 값 );
    
-- 열 지정 단일 데이터, 지정 하지 않은 값에는 NULL이 들어간다.
INSERT INTO 테이블 명 (열, 열) VALUES 
	(값, 값);

 

컬럼 명을 명시하지 않았을 경우에는 값 순서를 컬렴 명 순서와 일치하도록 적을 것.

텍스트를 입력할 때에는 싱글쿼터('')를 이용함에 주의하자.

TEXT타입에 숫자가 입력되었을 경우에, 자동으로 숫자로 변환해줌 ( 타 데이터베이스에선 불가 )

 

UPDATE

입력된 데이터의 값을 변경

UPDATE 테이블 명 SET 컬럼 명 = 변경할 값
-- 위의 업데이트 쿼리는 테이블의 모든 값을 변경

UPDATE 테이블 명 SET 컬럼 명 = 변경할 값 WHERE 컬럼 명 = 값;
-- 위의 업데이트 쿼리는 특정 값을 가진 값만 변경

 

 

DELETE

입력된 데이터의 값을 삭제

DELETE FROM 테이블명;
-- 위의 DELETE 쿼리를 실행하면 테이블안의 모든 데이터가 삭제됨

DELETE FROM 테이블명 WHERE 컬럼 명 = 값;
-- 위의 DELETE 쿼리를 실행하면 특정 값을 가진 데이터만 삭제

 

DATA 쿼리 ( SELECT )

 

SELECT

select 명령어는 table을 반환하는 명령어이다.

SELECT 컬럼 명, 컬럼 명 FROM 테이블 명;
-- FROM에 적힌 테이블의 모든 데이터가 working table이라는 메모리로 옮겨진다.
-- 그 중 필요한 컬럼 명의 속한 데이터만을 추출해서 테이블 형식으로 제공한다.

SELECT 컬럼 명, 컬럼 명, 1+1 AS two FROM 테이블 명;
-- 또한 테이블 안에 속한 데이터뿐만아니라 새로운 값을 추가할 수도 있다. 
-- two라는 컬럼 명안에 2라는 값이 들어가게된다.

 

아래의 25만 편이 넘는 영화에 대한 샘플 데이터를 가지고 SELECT문을 체험해볼 것이다.

이로인해 최적화 된 빠른 query의 작성방법, DB의 내부 작동방식에 대해 배울 수 있다.

https://pub-f13217639d6446309ebabc652f18d0ad.r2.dev/movies_download.db

 

CASE

CASE문은 SQL에서 조건에 따라 다른 값을 표시할 때 사용합니다.

이를 활용하면 셀렉트 쿼리에서 새로운 컬럼을 생성하거나, 기존 컬럼 값을 조건에 따라 다르게 표시할 수 있습니다.

SELECT
    CASE
        WHEN 조건1 THEN 값1
        WHEN 조건2 THEN 값2
        ELSE 기본값
    END AS 새_컬럼명
FROM
    테이블명;

 

ORDER BY

ORDER BY는 특정 컬럼의 값들을 정렬해서 데이터를 조회하는 데 사용되는 SQL 명령어입니다.

• 오름차순 정렬 ( ASC): 컬럼 이름 뒤에 아무것도 적지 않으면 기본적으로 오름차순(ASC)으로 정렬됩니다.
• 내림차순 정렬 (DESC): 만약 내림차순으로 정렬하고 싶다면, 컬럼 이름 뒤에 DESC를 붙이면 됩니다.

그리고 여러 개의 컬럼으로 정렬할 수도 있는데, 이 경우 첫 번째로 지정한 컬럼을 기준으로 먼저 정렬됩니다. 만약 첫 번째 컬럼의 값이 동일하다면, 두 번째 컬럼을 기준으로 정렬됩니다. 이런 방식으로 여러 컬럼을 기준으로 순차적으로 정렬할 수 있습니다.

SELECT
...
FROM
...
ORDER BY 컬럼 명 ASC or DESC

 

WHERE

 

WHERE
  컬럼 명 BETWEEN a and b -- a와 b사이의 숫자(각 숫자 포함)에 해당하는 데이터들만,

WHERE
  컬럼 명 IN ('text1','text2') -- 컬럼의 값이 text1 이거나 text2에 해당하는 데이터들만, NOT IN도 가능
 
WHERE
  컬럼 명 LIKE 'The%' -- %는 아무값이나를 뜻하며, The로 시작하는 값의 데이터를 반환, NOT LIKE도 가능
  -- 와일드 카드
  -- % 아무 값
  -- _ 아무 하나의 값

 

LIMIT , OFFSET

LIMIT과 OFFSET을 사용하면 전체 데이터를 한 번에 읽어오지 않고, 필요한 만큼만 데이터를 읽어올 수 있습니다. 이렇게 하면 쿼리를 보다 효율적으로 실행할 수 있습니다. 페이지네이션 등을 사용할 때 유용합니다.

SELECT 
  컬럼 명
FROM
  테이블 명
LIMIT 5
OFFSET 5;

--OFFSET이 5, LIMIT이5일 경우, 6번째 데이터부터 5개의 데이터를 출력합니다.
--OFFSET이 5, LIMIT이10일 경우, 6번째 데이터부터 10개의 데이터를 출력합니다.
--OFFSET이 10, LIMIT이5일 경우, 11번째 데이터부터 5개의 데이터를 출력합니다.

 

GROUP BY

GROUP BY는 두 단계로 나뉘어 수행된다. 먼저 컬럼에 같은 값을 가진 데이터들을 하나의 그룹으로 묶는다. 이 후 각 컬럼의 값들을 집계 함수를 사용하여 묶은 데이터들을 병합시킬 수 있다.

 

SELECT
  SUM(컬럼 명)
  AVG(컬럼 명)
FROM
  테이블 명
GROUP BY 컬럼 명

 

 

집계함수를 사용하지 않아도 row는 병합자체는 문제없이 되지만, 실제 값은 그룹의 마지막 row의 값만 표시하게된다.

따라서 GROUP BY하지 않은 항목을 집계함수를 사용하지 않고 단순히 SELECT하는 것은 좋은 생각은 아니다.

 

만약 GROUP BY를 사용하지 않고 집계 함수를 사용하면, 테이블 전체를 대상으로 작동하게되며 하나의 출력 값만을 내보내게 된다.

 

HAVING

GROUP. BY와 자매 격이며, GROUP BY를 사용할 때 많이 사용하게 된다. row들을 필터할 수 있으며 WHERE과 비슷하다.

다만 다른 점은 실행하는 순간이다.

 

WHERE은 모든 row를 대상으로 실행하지만, HAVING은 GROUP에 의해 그룹화 된 row들을 대상으로한다.

예를들어 영화 평점 평균이 7점 보다 높은 감독들만 확인하고 싶은 경우에, 먼저 감독을 기준으로 그룹화시키고, 이 후에 HAVING을 이용해서 필터링을 해야한다. 

 

이 경우 HAVING을 사용하지 않고 WHER만으로는 구분하기가 힘들다.

SELECT diretort, rating
FROM moives
GROUP BY director
HAVING AVG(rating) > 7;

 

 

 

VIEW

 

점점 쿼리가 복잡해지고 길어질 경우 매번 같은 코드를 복붙할 필요 없이 VIEW를 만들어두면, 편하게 확인 및 이용할 수 있다.

이 후 VIEW가 생성이 되는데, 마치 쿼리를 실행했을 때의 데이터를 기준으로 생성되었을 것 같지만, 실제로는 이 VIEW를 사용할 때마다 그 때 그 때 입력된 쿼리를 실행시켜서 보여주는 것이다.

 

따라서 새로운 데이터가 추가되거나, 삭제되거나 해도 최신화 된 데이터가 반영되어있을 것이다.

CREATE VIEW VIEW이름 AS 
SELECT ....