[2024.04.02] SQL

체리마루·2024년 4월 2일
SSAFY

Database

  • 데이터베이스: 체계적인 데이터 모음
  • 데이터: 저장이나 처리에 효율적인 형태로 변환된 정보
  • 기존의 데이터 저장 방식
    1) 파일 이용
    - 어디에서나 쉽게 사용 가능
    - 데이터를 구조적으로 관리하기 어려움
    2) 스프레드 시트 이용
    - 테이블의 열과 행을 사용해 데이터를 구조적으로 관리 가능
    - 일반적으로 약 100만 행까지만 저장 가능
    - 단순히 파일이나 링크 소유 여부에 따른 단순한 접근 권한 기능 제공
    - 정확성 문제
  • 데이터베이스 역할: 데이터를 저장하고 조작(CRUD)

Relational Database

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

  • 관계: 여러 테이블 간의 (논리적) 연결

  • 관계로 할 수 있는 것: 이 관계로 인해 두 테이블을 사용하여 데이터를 다양한 형식으로 조회할 수 있음

  • 관계형 데이터베이스 관련 키워드

  1. Table(a.k.a Relation): 데이터를 기록하는 곳
  2. Field(a.k.a Column, Attribute): 각 필드에는 고유한 데이터 형식(타입)이 지정됨
  3. Record(a.k.a Row, Tuple): 각 레코드에는 구체적인 데이터 값이 저장됨
  4. Database(a.k.a Schema): 테이블의 집합
  5. Primary Key(기본 키, PK): 각 레코드의 고유한 값, 관계형 데이터베이스에서 레코드의 식별자로 사용
  6. Foreign Key(외래 키, FK): 테이블의 필드 중 다른 테이블의 레코드를 식별할 수 있는 키, 다른 테이블의 기본 키를 참조, 각 레코드에서 서로 다른 테이블 간의 관계를 만드는 데 사용

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

  • RDBMS(Releational Database Management System): 관계형 데이터베이스를 관리하는 소프트웨어 프로그램 (SQLite, MySQL, PostgreSQL, Oracle Database...)

SQL

  • SQL(Structure Query Language): 데이터베이스에 정보를 저장하고 처리하기 위한 프로그래밍 언어(데이터베이스에서만 쓰는 언어!) => 테이블 형태로 구조화된 관계형 데이터베이스에게 요청을 질의(요청)

  • SQL Syntax

  1. SQL 키워드는 대소문자 구분 x. 하지만 대문자 작성을 권장 (명시적 구분)
  2. 각 SQL statements의 끝에는 세미콜론(';')이 필요. 세미콜론은 각 SQL Statemenets을 구분하는 방법(명령어의 마침표)

  • SQL statements: SQL을 구성하는 가장 기본적인 코드 블록

    => 해당 예시 코드는 SELECT Statement라고 부름
    => 이 Statement는 SELECT, FROM 2개의 keyword로 구성됨

  • 수행 목적에 따른 SQL Statements 4가지 유형

  1. DDL - 데이터 정의 (테이블 생성/삭제/수정)
  2. DQL - 데이터 검색
  3. DML - 데이터 조작 (데이터 CRUD)
  4. DCL - 데이터 제어 (데이터베이스 접근 권한)
  • Query: 데이터베이스로부터 정보를 요청하는 것. 일반적으로 SQL로 작성하는 코드를 쿼리문(SQL문)이라 함

Single Table Queries

Querying data

  • SELECT statement: 테이블에서 데이터를 조회

    SELECT 키워드 이후 데이터를 선택하려는 필드를 하나 이상 지정
    FROM 키워드 이후 데이터를 선택하려는 테이블의 이름을 지정
-- 01. Querying data
SELECT 
    LastName 
FROM 
    employees;

SELECT
    LastName, FirstName
FROM
    employees;

SELECT
    *
FROM
    employees;

SELECT
    FirstName AS '이름'
FROM
    employees;

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

Sorting data

  • ORDER BY statement: 조회 결과의 레코드를 정렬

    FROM clause 뒤에 위치
    하나 이상의 컬럼을 기준으로 결과를 오름차순(ASC, 기본 값), 내림차순(DESC)으로 정렬
-- 02. Sorting data
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;

SELECT 
    Name, 
    Milliseconds / 60000 AS '재생 시간(분)'
FROM
    tracks
ORDER BY
    Milliseconds DESC;
    SELECT DISTINCT
    Country
FROM
    customers
ORDER BY
    Country;
  • 정렬에서의 NULL: NULL값이 존재할 경우, 오름차순 정렬 시 결과에 NULL이 먼저 출력됨
  • SELECT statement 실행 순서
  1. 테이블에서 (FROM)
  2. 조회하여 (SELECT)
  3. 정렬 (ORDER BY)

Filtering data

  • DISTINCT statement: 조회 결과에서 중복된 레코드를 제거

    SELECT 키워드 바로 뒤에 작성해야 함
    SELECT DISTINCT 키워드 다음에 고유한 값을 선택하려는 하나 이상의 필드를 지정

  • WHERE statement: 조회 시 특정 검색 조건을 지정

    FROM clause 뒤에 위치
    search_condition은 비교연산자 및 논리연산자(AND, OR, NOT 등)를 사용하는 구문이 사용됨

-- 03. Filtering data
SELECT
    LastName, FirstName, City
FROM
    customers
WHERE
    City = 'Prague';

SELECT
    LastName, FirstName, City
FROM
    customers
WHERE
    City != 'Prague';

SELECT
    LastName, FirstName, Company, Country
FROM
    customers
WHERE
    Company IS NULL
    AND Country = 'USA';

SELECT
    LastName, FirstName, Company, Country
FROM
    customers
WHERE
    Company IS NULL
    OR Country = 'USA';

SELECT
    Name, Bytes
FROM
    tracks
WHERE
    Bytes BETWEEN 100000 AND 500000;

SELECT
    LastName, FirstName, Country
FROM
    customers
WHERE
    Country IN ('Canada', 'Germany', 'France');

SELECT
    LastName, FirstName, Country
FROM
    customers
WHERE
    Country NOT IN ('Canada', 'Germany', 'France');

SELECT
    LastName, FirstName
FROM
    customers
WHERE
    LastName LIKE '%son';

SELECT
    LastName, FirstName
FROM
    customers
WHERE
    FirstName LIKE '___a';

  • Operators
    Comparision Operators(비교 연산자)
    : =, >=, <=, !=, IS, LIKE, IN, BETWEEN...AND
    Logical Operators(논리 연산자)
    : AND(&&), OR(||), NOT(!)

  • IN Operator: 값이 특정 목록 안에 있는지 확인

  • LIKE Operator: 값이 특정 패턴에 일치하는지 확인(Wildcards와 함께 사용)

  • Wildcard Characters
    '%': 0개 이상의 문자열과 일치하는지 확인
    '_': 단일 문자와 일치하는지 확인

  • LIMIT clause: 조회하는 레코드 수를 제한

    하나 또는 두 개의 인자를 사용 (0 또는 양의 정수)
    row_count는 조회하는 최대 레코드 수를 지정

SELECT
    TrackId, Name, Bytes
FROM
    tracks
ORDER BY
    Bytes DESC
LIMIT
    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)

    FROM 및 WHERE 절 뒤에 배치
    GROUP BY 절 뒤에 그룹화 할 필드 목록을 작성
-- 4. Grouping data
SELECT
    Composer, AVG(BYTES)
FROM
    tracks
GROUP BY
    Composer
ORDER BY
    AVG(Bytes) DESC;

SELECT
    Composer,
    AVG(Bytes) AS avgOfBytes
FROM
    tracks
GROUP BY
    Composer
ORDER BY
    avgOfBytes DESC;

SELECT
    Composer,
    AVG(Milliseconds / 60000) AS avgOfMinute
FROM
    tracks
GROUP BY
    Composer
HAVING
    avgOfMinute < 10;

  • HAVING clause: 집계 항목에 대한 세부 조건을 지정. 주로 GROUP BY와 함께 사용되며 GROUP BY가 없다면 WHERE처럼 동작

  • SELECT statement 실행 순서

  1. 테이블에서 (FROM)
  2. 특정 조건에 맞추어 (WHERE)
  3. 그룹화 하고 (GROUP BY)
  4. 만약 그룹화 조건이 있다면 맞추고 (HAVING)
  5. 조회하여 (SELECT)
  6. 정렬하고 (ORDER BY)
  7. 특정 위치의 값을 가져옴 (LIMIT)

Managing Tables (DDL)

Create a table (CREATE)

  • 데이터베이스에 새 테이블을 만듦

  • Data Types 종류

  • 데이터 무결성
    데이터베이스 내의 데이터에 대한 정확성, 일관성을 보장하기 위해 데이터 변경 혹은 수정 시 여러 제한을 두어 데이터의 정확성을 보증하는 것
    데이터베이스에 저장된 데이터의 무결성을 보장하고 데이터베이스의 상태를 일관되게 유지하는 것이 목적

  • Constraints 종류

  1. NOT NULL: 해당 필드에 NULL 값을 허용하지 않도록 지정
  2. UNIQUE: 컬럼의 모든 값이 서로 구별되거나 고유한 값이 되도록 함 ex) 주민번호, 유저 아이디, 이메일
  3. PRIMARY KEY
    테이블에서 행의 고유성을 식별하는데 사용되는 컬럼
    각 테이블에는 하나의 기본 키만 있음
    암시적으로 NOT NULL 제약 조건이 포함되어 있음
  4. AUTOINCREMENT
    사용되지 않은 값이나 이전에 삭제된 행의 값을 재사용하는 것을 방지
    INTEGER PRIMARY KEY 다음에 작성하면 해당 rowid를 다시 재사용하지 못하도록 함

    Django에서 테이블 생성 시 id 컬럼에 기본적으로 사용하는 제약조건
  • rowid의 특징
    -테이블 생성할 때마다 rowid라는 암시적 자동 증가 컬럼이 자동으로 생성됨
    -테이블의 행을 고유하게 식별하는 64비트 부호 있는 정수 값
    -테이블에 새 행을 삽입할 때마다 정수 값을 자동으로 할당 (값은 1에서 시작. 데이터 삽입 시 rowid 또는 INTEGER PRIMARY KEY 컬럼에 명시적으로 값이 지정되지 않은 경우, SQLite는 테이블에서 가장 큰 rowid보다 하나 큰 다음 순차 정수를 자동으로 할당(AUTOINCREMENT와 관계없이))

Modifying table fields (ALTER)







Delete a table (DROP)



  • DDL 요약

Modifying Data (DML)

Insert data

Update data

Delete data

Multi table queries

Join

Joining tables

profile
체리마루
멋쟁이 토마토 개발자 🍅
이전 포스트

[2024.03.25] ORM

다음 포스트

[2024.04.03] N:1 (1)

0개의 댓글