[DB] SQL

Jingi·2024년 4월 2일
DatabaseRDBMSsqlite

Web

목록 보기
16/40
post-thumbnail

DB

Database

  • 체계적인 데이터 모음

데이터

  • 저장이나 처리에 효율적인 형태로 변환된 정보

기존의 데이터 저장 방식

  • 파일 이용
    • 어디에서나 쉽게 사용 가능
    • 데이터를 구조적으로 관리하기 어려움
  • 스프레드 시트 이용
    • 테이블의 열과 행을 사용해 데이터를 구조적으로 관리 가능
    • 일반적으로 약 100만 행까지만 저장가능
    • 단순한 파일이나 링크 소유 여부에 따른 단순한 접근 권한 기능 제공

데이터 베이스 역할

  • 데이터를 저장하고 조작(CRUD)

Realtional Database

  • 데이터 간에 관계가 있는 데이터 항목들의 모음
  • 테이블, 행, 열의 정보를 구조화하는 방식
  • 서로 관련된 데이터 포인터를 저장하고 이에 대한 액세스를 제공

관계

  • 여러 테이블 간의 논리적 연결
  • 관계를 함으로써 다양한 형식으로 조회 가능
  • Table : 데이터를 기록하는 것
  • Field : 각 필드에는 고유한 데이터 형식이 지정됨
  • Record : 각 레코드에는 구체적인 데이터 값이 저장됨
  • Database : 테이블의 집합
  • Primary Key : 각 레코드의 고유한 값, 관계형 데이터베이스에서 레코드의 식별자로 활용
  • Foreign Key : 테이블의 필드 중 다른 테이블의 레코드를 식별할 수 있는 키, 다른 테이블의 기본키를 참조

RDBMS

DBMS

  • 데이터베이스를 관리하는 소프트웨어 프로그램
  • 데이터 저장 및 관리를 용이하게 하는 시스템
  • 데이터베이스와 사용자 간의 인터페이스 역할
  • 사용자가 데이터 구성, 업데이트, 모니터링, 백업, 복구 등을 할 수 있도록 도움

RDBMS

  • 관계형 데이터베이스를 관리하는 소프트웨어 프로그램
  • SQLite, MySQL, Oracle 등

데이터 베이스 정리

  • Table은 데이터가 기록되는 곳
  • Table에는 행에서 고유하게 식별 가능한 기본 키라는 속성이 있으며, 외래 키를 사용하여 각 행에서 서로 다른 테이블 간의 관계를 만들 수 있음
  • 데이터는 기본 키 또는 외래 키를 통해 결합될 수 있는 여러 테이블에 걸쳐 구조화 됨

SQL

  • 데이터베이스에 정보를 저장하고 처리하기 위한 프로그래밍 언어
  • 대소문자를 구분하지 않음
  • SQL Statements의 끝에는 세미콜론이 필요

SQL Statements

  • SQL을 구성하는 가장 기본적인 코드 블록
  • DDL : 데이터 정의
    • CREATE, DROP, ALTER
  • DQL : 데이터 검색
    • SELECT
  • DML : 데이터 조작
    • INSERT
    • UPDATE
    • DELETE
  • DCL : 데이터 제어
    • COMMIT
    • ROLLBACK
    • GRANT
    • REVOKE

SELECT

  • 테이블에서 데이터를 조회
  • 테이블의 데이터를 조회 및 반환
  • '*'를 사용하여 모든 필드 선택
  • SELECT FIRSTNAME AS '이름' FROM employees;
  • SELECT NAME, Milliseconds / 60000 AS '재생시간(분)' FROM tracks;

ORDER BY

  • 조회 결과의 레코드를 정렬
  • 기본 값은 ASC
  • SELECT FIRSTNAME FROM employees ORDER BY FirstName;
  • SELECT FIRSTNAME FROM employees ORDER BY FirstName DESC;
  • SELECT Country, City FROM customers ORDER BY Country DESC, City;

Filtering data 관련 keywords

ClauseOperator
DISTINCTBETWEEN
WHEREIN
LIMITLIKE, Comparison, Logical

Distinct

  • 조회결과에서 중복된 레코드를 제거
  • Select 키워드 바로 뒤에서 작성해야 함
  • SELECT DISTINCT 키워드 다음에 고유한 값을 선택하려는 하나 이상의 필드를 지정
  • SELECT DISTINCT select_list FROM table_name;
  • SELECT DISTINCT FIRSTNAME FROM employees ORDER BY FirstName;

WHERE

  • 조회 시 특정 검색 조건을 지정
  • FROM caluse 뒤에 위치
  • 비교연산자 및 논리연사를 사용하는 구문이 사용됨
  • SELECT LastName, FirstName, City FROM customers WHERE CITY = 'Prague';
  • SELECT LastName, FirstName, City FROM customers WHERE CITY != 'Prague';
  • SELECT LastName, FirstName, City FROM customers WHERE Company IS NULL AND CITY = 'USA';
  • SELECT LastName, FirstName FROM customers WHERE FirstName LIKE '__a';

Comparison Operators

  • 비교연산자
  • =, >=, <=, !=, IS,Like, IN, BETWEEN,... , AND

Logical Operators

  • 논리연산자
  • AND(&&), OR(||), NOT(!)
  • '%' : 0개 이상의 문자열과 일치하는 확인
  • '_ ' 단일 문자와 일치하는지 확인

LIMIT clause

  • 조회하는 레코드 수를 제한
  • 하나 또는 두 개의 인자를 사용
  • SELECT TrackId, Name, Bytes FROM tracks ORDER BY Bytes DESC LIMIT 7;
  • 테이블 tracks에서 TrackId, Name, Bytes 필드 데이터를 Bytes 기준 내림 차순으로 4번째부터 7번째 데이터만 조회
  • SELECT TrackId, Name, Bytes FROM tracks ORDER BY Bytes DESC LIMIT 3, 4;

Grouping data

GROUP BY clause

  • 레코드를 그룹화하여 요약본 생성
  • Aggregation Functions : 값에 대한 계산을 수행하고, 단일한 값을 반환하는 함수 -> SUM, AVG, MAX, MIN, COUNT
  • 테이블 tracks에서 Composer 필드를 그룹화하여 각 그룹에 대한 Bytes의 평균 값을 내림차순 조회
  • SELECT Composer, AVG(Bytes) FROM tracks GROUP BY Composer ORDER BY AVG(Bytes) DESC;
  • SELECT Composer, AVG(Bytes) as bt FROM tracks GROUP BY Composer HAVING bt < 10 ;

실행 순서

  • FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

Managing Tables

Create

  • 각 필드에 적용할 데이터 타입 작성
  • 테이블 및 필드에 대한 제약 조건 작성
CREATE TABLE examples(
    ExamID INTEGER PRIMARY KEY AUTOINCREMENT,
    LastName VARCHAR(50) NOT NULL,
    FirstName VARCHAR(50) NOT NULL
)

데이터 타입

  • NULL : 아무런 값도 포함하지 않음을 나타냄
  • INTEGER : 정수
  • REAL : 부동 소수점
  • TEXT : 문자열
  • BLOB : 이미지, 동영상, 문서 등의 바이너리 데이터

Constraints

  • 테이블의 필드에 적용되는 규칙 또는 제한 사항
  • 데이터의 무결성을 유지하고 데이터베이스의 일관성을 보장
  • PRIMARY KEY
    • 해당 필드를 기본 키로 지정
    • INTERGER 타입에만 적용되며 INT, BIGINT 등과 같은 다른 정수 유형은 적용되지 않는다
  • NOT NULL
    • 해당 필드에 NULL 값을 허용하지 않도록 지정
  • FOREIGN KEY
    • 다른 테이블과의 외래 키 관계를 정의

AUTOINCREMENT keyword

  • 자동으로 고유한 정수 값을 생성하고 할당하는 필드 속성
  • 필드의 자동 증가를 나타내는 특수한 키워드
  • 주로 PRIMARY KEY 필드에 적용
  • INTEGER PRIMARY KEY AUTOINCREMENT가 작성된 필드는 항상 새로운 레코드에 대해 이전 최대 값보다 큰 값을 할당
  • 삭제된 값은 무시되며 재사용할 수 없게 됨

Modifiying table fields

ALTER TABLE

  • 테이블 및 필드 조작
    |명령어 | 역할|
    |------|-----|
    ALTER TABLE ADD COLUMN | 필드 추가|
    ALTER TABLE RENAME COLUMN | 필드 이름 변경|
    ALTER TABLE RENAME TO | 테이블 이름 변경|
  • ALTER TABLE examples ADD COLUMN Age INTEGER NOT NULL DEFAULT 0;
    • ADD COLUMN 키워드 이후 추가하고자 하는 새 필드 이름과 데이터 타입 및 제약 조건 작성
    • 추가하고자 하는 필드에 NOT NULL 제약조건이 있을 경우 NULL이 아닌 기본 값 설정 필요
  • ALTER TABLE table_name RENAME COLUMN current_name TO new_name
    • RENAME COLUMN 키워드 뒤에 이름을 바꾸려는 필드의 이름을 지정하고 TO 키워드 뒤에 새 이름을 지정
  • ALTER TABLE table_name RENAME TO new_table_name
    • RENAME TO 키워드 뒤에 새로운 테이블 이름 지정

DROP TABLE

  • DROP TABLE statment 이후 삭제할 테이블 이름 작성
  • new_examples 테이블 삭제

참고

SQLite 타입 선호도의 목적

  • 유연한 데이터 타입 지원
    • 데이터 타입을 명시적으로 지정하지 않고도 데이터를 저장하고 조회할 수 있음
    • 컬럼에 저장되는 값의 특성을 기반으로 데이터 타입을 유추
  • 간편한 데이터 처리
    • INTEGER Type Affinity를 가진 열에 문자열 데이터를 저장해도 SQLite는 자동으로 숫자로 변환하여 처리
  • SQL 호환성
    • 다른 데이터 베이스 시스템과 호환성을 유지

반스시 NOT NULL 제약을 사용?

  • NO
  • 하지만 데이터베이스를 사용하는 프로그램에 따라 NULL을 저장할 필요가 없는 경우가 많으므로 NOT NULL을 정의
  • 값이 없다 라는 표현을 테이블에 기록하는 것은 0이나 빈 문자열 등을 사용하는 것으로 권장

Modifiying DATA

INSERT

  • 테이블에 레코드 삽입
  • INSERT INTO table_name(C1, C2, ..) VALUES (v1, v2, ...);
    • INSERT INTO 절 다음에 테이블 이름과 괄호 안에 필드 목록 작성
    • VALUES 키워드 다음 괄호 안에 해당 필드에 삽입할 값 목록 작성
  • INSERT INTO articles( title, content, createdAT) VALUES('title1', 'content1', '1900-01-01'),('title2', 'content2', '1800-01-01'),('title3', 'content3', '1700-01-01');

UPDATE

  • 테이블 레코드 수정
  • UPDATE table_name SET column_name = expression, [WHERE condition];
    • SET 절 다음에 수정 할 필드와 새 값을 지정
    • WHERE 절에서 수정 할 레코드를 지정한느 조건 작성
    • WHERE 절을 작성하지 않으면 모든 레코드를 수정
  • UPDATE articles SET title = 'udpate Title' WHERE id = 1;

DELETE

  • 테이블 레코드 삭제
  • DELTE FROM table_name [WHERE condition];
    • DELTE FROM 절 다음에 테이블 이름 작성
    • WHERE 절에서 삭제할 레코드를 지정하는 조건 작성
    • WHERE 절을 작성하지 않으면 모든 레코드를 삭제
  • 작성일이 오래된 레코드 2개 삭제
    • DELETE FROM articles WHERE id IN(
      SELECT id FROM articles ORDER BY createdAT LIMIT 2
      );

Join

관계

  • 여러 테이블 간의 논리적 연결
  • 테이블 분리 시 데이터 관리는 용이하나 출력 시에 문제 발생
  • 테이블 한 개만을 출력할 수 밖에 없어 다른 테이블과 결합하여 출력하는 것이 필요해진 상황

JOIN

  • 둘 이상의 테이블에서 데이터를 검색하는 방법
  • INNER JOIN
  • LEFT JOIN

INNER JOIN

  • 두 테이블 값이 일치하는 레코드에 대해서만 결고 반환
  • SELECT select_list FROM table_a INNER JOIN table_b ON table_b.fk = table_a.pk;
    • FROM 절 이후 메인 테이블 지정
    • INNER JOIN 절 이후 메인 테이블과 조인할 테이블을 지정
    • ON 키워드 이후 조인 조건을 작성
    • 조인 조건은 table_a 와 table_b간의 레코드를 일치시키는 규칙을 지정

LEFT JOIN

  • 오른쪽 테이블의 일치하는 레코드와 함께 왼쪽 테이블의 모든 레코드 반환
  • SELECT select_list FROM table_a LEFT JOIN table_b ON table_b.fk = table_a.pk;
    • FROM 절 이후 왼쪽 테이블 지정
    • LEFT JOIN 절 이후 오른쪽 테이블 지정
    • ON 키워드 이후 조인 조건을 작성
      • 왼쪽 테이블의 각 레코드를 오른쪽 테이블의 모든 레코드와 일치시킴
  • 왼쪽 테이블의 모든 레코드를 표기
  • 오른쪽 테이블과 매칭되는 레코드가 없으면 NULL을 표시
profile
Jingi
데이터 분석에서 백엔드까지...
이전 포스트

[Django] Authentication system 2

다음 포스트

[DB] Many to one relationships 1

0개의 댓글