[SQLite] Database

한결·2023년 4월 5일
db
0

WEB

목록 보기
16/63

데이터

  • 데이터
    • 저장이나 처리에 효율적인 형태로 변환된 정보 (information)
  • 데이터 생성량 점점 증가함
    -> 데이터 센터의 성장 & 데이터 저장 및 관리 기술 필요

파일을 이용한 데이터 관리

  • 장점
    • 운영체제 관계 없이 어디서나 사용 가능
    • 이메일이나 메신저를 통해 간편하게 전송 가능
  • 단점
    • 성능과 보안 측면에서 한계가 있음
    • 대용량 데이터 다루기 어려움
    • 확장 불가
    • 구조화 어려움

표를 이용한 데이터 관리

  • 한계
    • 무한하게 커질 수 없음
    • 보안 측면
    • 무결성 측면

데이터 베이스

"A databse is an organized collection of data"

DBMS (Database Management System)

"DBMS are specially designed software applications that interact with the user, other applications, and the database itself to capture and analyze data."
데이터베이스를 쉽게 조작할 수 있게 해주는 소프트 웨어

DB 종류

  • 관계형 DB (SQL)

    • 표 형식의 데이터 베이스
    • 여전히 메인 DB로 사용

  • 비관계형 DB(NoSQL)

    • 관계형의 한계를 극복하기 위해 조금 더 유연해짐
    • 보통 서브 DB로 사용

  • SQL

    데이터베이스를 조작하는 언어 SQL(Structured Query Language)

관계형 데이터베이스(RDB)

  • 데이터를 테이블 행, 열 등으로 나누어 구조화 하는 방식
  • 구조화해서 저장하므로 보다 체계적으로 데이터 저장 및 관리 가능
  • 자료를 여러 테이블로 나누어 관리
  • 테이블간 관계를 설정해 여러 데이터 조작 가능
  • 데이터의 무결성(정확성, 일관성) 유지에 장점이 있음
  • SQL을 사용하여 데이터를 조회하고 조작

구조

  1. 스키마
  2. 테이블
  • 필드
  • 레코드
  • 기본 키

스키마(Schema)

  • 테이블의 구조
  • 데이터베이스에서 자료의 구조, 표현 방법, 관계 등 전반적인 명세를 기술한 것

테이블(Table)

  • 필드와 레코드를 사용해 조직된 데이터 요소들의 집합
  • 관계(Relation)라고도 부름
  1. 필드(field)
  • 속성, 칼럼
  • 각 필드에는 고유한 데이터 형식(타입)이 지정됨
  1. 레코드(record)
  • 튜블, 행
  • 테이블의 데이터는 레코드에 저장됨

PK(Primary Key)

  • 기본 키
  • 각 레코드의 고유한 값
    • 각각의 데이터를 구분할 수 있는 고유값
  • 기술적으로 다른 항목과 절대로 중복될 수 없는 단일 값(unique)
  • 데이터베이스 관리 및 테이블 간 관계 설정 시 주요하게 활용

FK(Foreign Key)

  • 외래 키
  • 다른 테이블의 레코드를 식별할 수 있는 키
    == 다른 테이블의 PK
  • 각 레코드에서 서로 다른 테이블 간의 관계를 만드는 데 사용할 수 있음

SQL

Structured Query Language
관계형 데이터베이스에서 데이터를 관리하기 위해 사용하는 언어
== 데이터베이스 관리 + CRUD 하는 언어

SQL Syntax

SELECT column_name FROM table_name;

  • 모든 SQL문(statement)은 SELECT, INSERT, UPDATE 등과 같은 키워드로 시작하고 하나의 statement는 세미콜론(;)으로 끝남

  • SQL 키워드는 대소문자 구분 안함
    하지만 대문자로 작성 권장

Commands 종류

특성에 따라 3가지로 분류
1. DDL
2. DML
3. DCL

DDL

사전 준비

  1. SQlite 설치
  2. Vscode SQLite확장 프로그램 설치
  3. 데이터베이스 mydb.sqlite3 파일 생성
  4. DDL.sql 파일 생성
  5. vscode 실행 후 DDL.sql 화면에서 마우스 우측 버튼 클릭
    • USE Database 선택
  6. 데이터 베이스 목록에서 mydb.sqlite3 선택

개요

  • "Data definition"
  • SQL 데이터 정의 언어를 사용하여 테이블 데이터베이스 개체를 만드는 방법을 학습
  • DDL은 테이블 구조를 관리
    • CREATE, ALTER, DROP

CREATE TABLE

"create a new table in the database."

CREATE TABLE contacts (
  name TEXT NOT NULL,
  age INTEGER NOT NULL,
  email TEXT NOT NULL UNIQUE
);
  • Query 실행하기
    • 실행하고자 하는 명형문에 커서를 두고 마우스 우측버튼
      -> "Run Selected Query" 선택
    • 실행하고자 하는 명령문 안에 커서 올려서 하면됨

SQLite Data Types

name TEXT NOT NULL,

  • "TEXT" 부분
종류
  1. NULL
    • NULL value
    • 정보가 없거나 알수 없음
  2. INTEGER
    • 정수
  3. REAL
    • 실수
  4. TEXT
    • 문자 데이터
  5. BLOB (Bianry Large Object)
    • 입력된 그대로 저장된 데이터 덩어리
    • 바이너리 등 멀티미디어 파일
    • 예시
      • 이미지 데이터
  • SQLite 에는 별도의 Bollean 타입이 없다
  • 대신 0과 1로 저장됨
Type Affinity

"타입 선호도"

  • 특정 column에 저장된 데이터에 권장되는 타입

  • 존재 이유

    • 다른 데이터베이스 엔진 간의 호환성 최대화
    • 정적이고 엄격한 타입을 사용하는 데이터베이스의 SQL문을 SQLite에서도 작동하도록 하기 위함

Constraints

개요
  • "제약조건"
  • 입력하는 자료에 대해 제약을 정함
  • 제약에 맞지 않다면 입력이 거부됨
  • 사용자가 원하는 조건의 데이터만 유지하기 위한
    즉, 데이터의 무결성을 유지하기 위한 보편적인 방법으로 테이블의 특정 컬럼에 설정하는 제약
데이터 무결성
  • 데이터 베이스 내의 데이터에 대한 정확성, 일관성을 보장하기 위해 데이터 변경 혹은 수정 시 여러 제한을 두어 데이터의 정확성을 보증하는 것
    • 무결성이란 데이터의 정확성, 일관성을 나타냄
  • 데이터베이스에 저장된 데이터의 무결성을 보장하고 데이터 베이스의 상태를 일관되게 유지하는 것이 목적
종류

  1. NOT NULL

    • column이 NULL 값을 허용하지 않도록 지정
    • 기본적으로 NOT NULL 제약조건을 명시적으로 사용하는 경우가 아니면 NULL값을 허용함

  2. UNIQUE

    • column의 모든 값이 서로 구별되거나고유한 값이 되도록 함

  3. PRIMARY KEY

    • 테이블에서 행의 고유성을 식별하는 데 사용되는 column
    • 각 테이블에는 하나의 기본 키만 있음
    • 암시적으로 NOT NULL 제약 조건이 포함되어 있음

  4. AUTOINCREMENT

    • 사용되지 않은 값이나 이전에 삭제된 행의 값을 재사용하는 것을 방지
    • INTEGER PRIMARY KEY 다음에 작성하면 해당 rowid를 다시 재사용하지 못하도록 함
    • Django에서 테이블 생성 시 id column에 기본적으로 사용하는 재약조건
  • rowid
    • 테이블 생성할 때마다 rowid라는 암시적 자동 증가 column이 자동적으로 생성
    • 테이블의 행을 고유하게 식별하는 정수 값
    • 테이블에 새 행을 삽입할 때마다 정수 값으 자동 할당 (1에서 시작)
    • 만약 INTEGER PRIMARY KEY 키워드를 가진 column을 직접 만들면 rowid column의 별칭(alias)이 됨

ALTER TABLE

"Modify the structure of an existing table"

  • 기존 테이블의 구조를 수정
  • 기존 테이블을 다음과 같이 변경할 수 있음
    1. Rename a table
    2. Rename a column
    3. Add a new column to a table
    4. Delete a column
Table Rename
CREATE TABLE contacts (
  name TEXT NOT NULL,
  age INTEGER NOT NULL,
  email TEXT NOT NULL UNIQUE
);

ALTER TABLE contacts RENAME TO new_contacts;

Rename Column
ALTER TABLE new_contacts RENAME COLUMN name TO last_name;

ADD COLUMN
ALTER TABLE new_contacts ADD COLUMN address TEXT NOT NULL;

DROP COLUMN

"Delete a column"

ALTER TABLE new_contacts DROP COLUMN address;

DROP Table

"Remove a table from the database."

DROP TABLE new_contacts;

  • 한 번에 하나의 테이블만 삭제 가능
  • DROP TABLE문은 실행 취소나 복구 불가
    • 각별히 주의 해야함

DDL 정리

  • "데이터 정의 언어"
  • CREATE TABLE
    • 데이터 타입과 제약 조건
  • ALTER TABLE
    • RENAME
    • RENAME COLUMN
    • ADD COLUMN
    • DROP COLUMN
  • DROP TABLE

DML

  • DML을 통해 데이터 조작하기 (CRUD)
  • INSERT, SELECT, UPDATE, DELETE

사전준비

  • 테이블에 csv파일 데이터를 inport 해서 사용
  1. 테이블 생성
CREATE TABLE users (
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
age INTEGER NOT NULL,
country TEXT NOT NULL,
phone TEXT NOT NULL,
balance INTEGER NOT NULL
);
  1. 데이터베이스 파일 열기
    sqlite3 db.sqlite3
  2. .mode 를 csv로 설정
    sqlite> .mode csv
  3. .import 명령어를 사용하여 csv 데이터를 테이블로 가져오기
    sqlite> .import users.csv users
  4. 결과

Simple query

SELECT statement
  • select문을 사용하여 간단하게 단일 테이블에서 데이터 조회

SELECT column1, colum2 FROM table_name;

  • 특정 테이블에서 데이터를 조회하기 위해 사용
  • 문법 규칙
    1. SELECT 절에서 컬럼 또는 쉼표로 구분된 컬럼 목록을 지정
    2. FROM 절에서 데이터를 가져올 테이블을 지정
  • SELECT 문은 SQLite에서 가장 복잡한 문
  • 다양한 절과 함꼐 사용할 수 있으며 하나씩 학습할 예정
    • ORDER BY
    • DISTINCT
    • WHERE
    • LIMIT
    • LIKE
    • GROUP BY
예시
  • 이름과 나이 조회하기
    SELECT first_name,age FROM users

Sorting rows

  • ORDER BY 절을 사용하여 쿼리의 결과를 정렬하기
ORDER BY clause
SELECT select_list FROM table_name
ORDER BY column_1 ASC, column_2 DESC;
  • SELECT 문에 추가하여 결과를 정렬
  • ORDER BY 절은 FROM 절 뒤에 위치함
  • 하나 이상의 컬럼을 기준으로 결과를 오름차순, 내림차순으로 정렬할 수 있음
  • 이를 위해 ORDER BY 절 다음에 'ASC' 또는 'DESC' 키워드를 사용
    • ASC : 오름차순 (기본 값)
    • DESC : 내림차순
예시
  • 이름과 나이를 나이 순서대로 오름차순 정렬
SELECT first_name,age 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 DISTINCT clause

SELECT DISTINCT select_list FROM table_name;

  • 조회 결과에서 중복된 행을 제거
  • DISTINCT 절은 SELECT 에서 선택적으로 사용할 수 있는 절
  • 문법 규칙
    1. DISTINCT 절은 SELECT 키워드 바로 뒤에 나타나야 함
    2. DISTINCT 키워드 뒤에 컬럼 또는 컬럼 목록을 작성
예시
  • 모든 지역 조회

    -> 중복이 있음
    -> 중복 제거하자

    -> 오름차순까지 하자
참고

NULL with DISTINCT

  • SQLite는 NULL 값을 중복으로 간주
  • NULL 값이 있는 컬럼에 DISTINCT 절을 사용하면 SQLite는 NULL 값의 한 행을 유지
WHERE clause
SELETE column_list FROM table_name
WHERE search_condition
  • 조회 시 특정 검색 조건을 지정
  • WHERE 절은 SELECT 문에서 선택적으로 사용할 수 있는 절
    • SELECT 문 외에도 UPDATE 및 DELETE문에서 WHERE 절을 사용할 수 있음
  • FROM 절 뒤에 작성
형식
WHERE column_1 = 10
WHERE column_2 LIKE 'Ko%'
WHERE column_3 IN (1,2)
WHERE column_4 BETWEEN 10 AND 20
SQLITE comparison operators
  • 두 표현식이 동일한지 테스트
    • =
    • <> or !=
    • <
    • <=

    • =

SQLite logical operators
  • 일부 표현식의 truth를 테스트할 수 있음
  • 1,0 또는 NULL 값을 반환
  • SQLite는 Bollean 데이터 타입을 제공하지 않으므로 1은 TRUE를 의미하고 0은 FALSE를 의미
  • ALL, AND, ANY, BETWEEN, IN, LIKE, NOT, OR 등
예시
  • 나이가 30살 이상인 사람들의 이름, 나이, 계좌잔고 조회 (나이순으로 오름차순)
  • 계좌 잔고 50만원 초과 까지 조건 추가
LIKE operator
  • 패턴 일치를 기반으로 데이터를 조회
  • WHERE 절에서 사용
  • 기본적으로 대소문자를 구분하지 않음
    • 'A' LIKE 'a' == True
  • SQLite는 패턴 구성을 위한 두 개의 와일드 카드를 제공
  1. %
  • 0개 이상의 문자가 올 수 있음을 의미
  • '영%' : 영으로 시작하는 모든 문자열과 일치 (영, 영미, 영미리 등)
  • '%도' : 도로 끝나는 모든 문자열과 일치 (도, 수도, 경기도 등)
  • '%강원%' : 강원을 포함하는 모든 문자열과 일치 (강원, 강원도, 강원도에 살아요 등)
  1. _
  • 단일 문자가 있음을 의미
  • '영_' : 영으로 시작하고 총 2자리인 문자열과 일치 (영미, 영수, 영호 등)
  • '_도' : 도로 끝나는 총 2자리인 문자열과 일치 (수도, 과도 등)
예시
  • 이름에 '호'가 포함되는 사람들의 이름과 성 조회
IN operator
  • 값이 값 목록 결과에 있는 값과 일치하는 지 확인
  • WHERE 뒤에 작성
  • 표현식이 값 목록의 값과 일치하는 지 여부에 따라 true 또는 False를 반환
  • 부정 표현은 NOT IN
예시
  • 경기도 혹은 강원도에 사는 사람들의 이름과 지역 조회하기
  • 부정
BETWEEN operator

test_expression BETWEEN low_expression AND high_expression

  • 값이 값 범위에 있는지 테스트
  • 값이 지정된 범위에 있으면 true를 반환
  • WHERE절에 사용
  • 부정은 NOT BETWEEN
예시
  • 나이가 20살 이상 30살 이하인 사람들의 이름과 나이 조회
LIMIT clause

SELECT column_list FROM table_name LIMIT row_count

  • 쿼리에서 반환되는 행 수를 제한
  • SELECT 문에서 선택적으로 사용할 수 있는 절 - row_count는 반환되는 행 수를 지정하는 양의 정수를 의미
예시
  • 첫 번째부터 열번 째 데이터 rowid와 이름 조회
OFFSET keyword
  • LIMIT 절을 사용하면 첫 번째 데이터부터 지정한 수 만큼의 데이터를 받아올 수 있지만, OFFSET과 함께 사용하면 특정 지정된 위치에서 부터 데이터를 조회할 수 있음
예시
  • 11번쨰 부터 20번째 rowid 이름 조회
profile
한결
이전 포스트

[Django] App URL mapping

다음 포스트

[SQLite] DB조작과 모델링

0개의 댓글