doi

doing something

3 minute read


데이터베이스 언어

데이터베이스 언어 (Database Language) 분류 방법: 이용 대상과 이용 형태

  • 이용 대상
    • 데이터 정의 언어 (DDL: Data Definition Language)
      • 데이터베이스 관리자(Database Administrator)용
      • 데이터베이스에 대한 접근 권한을 관리함
      • 예시
        • CREATE USER admin WITH LOGIN PASSWORD 'pass' VALID UNTIL '2023-12-31'
    • 데이터 조작 언어 (DML: Data Manipulation Language)
      • 일반 사용자용
      • 데이터베이스의 검색/갱신에 사용
      • 예시
        • SELECT product_name FROM product WHERE price > 100000;


  • 이용 형태
    • 직접 이용
      • 터미널에서 온라인으로 DDL/DML을 사용하여 데이터베이스 정의, 검색, 갱신을 수행함
    • 주언어(Host Language)와 데이터 부언어(Data Sublanguage)
      • C로 작성된 애플리케이션 프로그램 내에서 데이터 부언어를 사용함
      • 프리컴파일러로 DDL/DML을 추출함
      • 데이터베이스와의 상호작용을 주언어의 변수에 바인딩함


SQL에 대해

  • 기능
    • 데이터 정의 및 조작 언어(DDL, DML)
    • 검색・통계(집계) 연산, 갱신 등
  • 이용 형태
    • 대화형(Direct SQL), 내장형(Embedded SQL)
  • 기본 구문
    • SELECT 속성의 나열 FROM 릴레이션 이름의 나열 WHERE 검색 조건;
  • 최신 표준화 버전은 SQL:2023
    • SQL:2023 그래프 쿼리
    • SQL:2016 JSON
    • SQL:2011 시간 데이터베이스
    • SQL:2006 XQuery
    • SQL:2003 XML
    • SQL:1999 객체 지향, 재귀적 질의


SQL 질의 각종

데이터 정의: 제약

  • 일관성 제약(Integrity Constraint)
    • 갱신 시 데이터베이스 내용에 모순이 생기지 않도록 함
    • 키 제약
      • 키 속성에 저장되는 데이터는 null 값이 되어서는 안 됨
    • 외래 키 참조 제약 (Foreign Key Referential Constraint)
      • 외부에서 키로 참조되는 값은 삭제되어서는 안 됨


데이터 정의: 테이블 작성

  • CREATE TABLE product(id TEXT PRIMARY KEY, pname TEXT, price INTEGER);
  • CREATE TABLE client(id TEXT PRIMARY KEY, cname TEXT);
  • CREATE TABLE delivery(pid TEXT, cid TEXT, num INTEGER, PRIMARY KEY(pid, cid), FOREIGN KEY(pid) REFERENCES product(id), FOREIGN KEY(cid) REFERENCES client(id));


(SQLite) SQL에 의한 외부 키의 설정

  • 사전에 외래 키 설정을 활성화하는 설정이 필요함: sqlite> pragma foreign_keys=on
  • 다른 파라미터들과 함께 환경 설정하는 것이 좋음
    • .sqliterc 파일을 사용자 폴더에 둠
      • C:\Users\username\.sqliterc (Windows)
      • /Users/username/.sqliterc (MacOS)
      • /home/username/.sqliterc (Linux)


데이터 정의: 삭제, 갱신

  • 테이블 삭제
    • DELETE FROM product;
    • DROP TABLE product;
  • 속성 추가・변경・삭제
    • ALTER TABLE product ADD COLUMN maker char(8);
    • ALTER TABLE product RENAME COLUMN price TO fullPrice Integer;
    • ALTER TABLE product RENAME COLUMN fullprice TO price Integer;
    • ALTER TABLE product DROP COLUMN maker;


데이터 삽입

  • 환경에 따라, ”를 ’로 지정할 필요성이 있음
  • product 테이블에 데이터 삽입하기
    • INSERT INTO product VALUES('P100', 'smartphone', 140000);의 실행
  • client 테이블에 데이터 삽입하기
    • INSERT INTO client VALUES('C1', 'Ochanomizu Ltd.');의 실행
  • delivery 테이블에 데이터 삽입하기
    • INSERT INTO delivery VALUES('P100', 'C1', 3);의 실행


SQLite 커맨드 모음

  • .read: SQL 파일을 읽어 옴
  • .csv 파일로부터의 일괄 삽입
    • .mode csv의 지정
    • --skip 1 옵션을 포함한 .import <file.csv> table의 실행
  • 출력 제어의 수행
    • .mode column|tabs|list의 지정
  • 데이터 삭제, 갱신
    • product 테이블에서 id가 P100인 튜플의 삭제
      • DELETE FROM product WHERE id='P100';의 실행
    • id가 P100인 항목의 정가(price)의 갱신
      • UPDATE product SET price=145000 WHERE id='P100';의 실행
  • 간단한 검색: 사영 射影
    • delivery 테이블의 cid 값을 전부 검색하기
      • SELECT cid FROM delivery;의 실행
    • delivery 테이블에서 중복 없는 cid 값을 전부 검색하기
      • SELECT DISTINCT cid FROM delivery;의 실행
  • 간단한 검색: 선택
    • id, 해당 price, 그리고 price의 10% 할인 가격의 계산
      • SELECT id, price, price * 0.9 FROM product;의 실행
    • product 테이블에서 name이 TV인 product의 모든 데이터의 검색
      • SELECT * FROM product WHERE pname='TV';의 실행
    • name이 TV이고 price가 300,000 이하인 product의 모든 데이터의 검색
      • SELECT * FROM product WHERE pname='TV' AND price < 300000;의 실행
    • delivery 테이블에서 num이 5 이하이거나 cid가 C2인 데이터의 검색
      • SELECT * FROM delivery WHERE num < 5 OR cid='C2';의 실행


결합 질의 結合問い合わせ

  • product의 id와 delivery된 product의 id가 같은 경우의 product의 모든 데이터와 delivery의 모든 데이터의 검색
    • SELECT product.*, delivery.* FROM product, delivery WHERE product.id = delivery.pid;의 실행
    • SELECT product.*, delivery.* FROM product JOIN delivery ON product.id=delivery.pid;


  • product의 id는 모두 남기고, product의 id와 delivery된 product의 id가 같은 경우의 product의 모든 데이터와 delivery의 모든 데이터의 검색 (LEFT JOIN)
    • SELECT product.*, delivery.* FROM product LEFT JOIN delivery ON product.id = delivery.pid;의 실행


  • delivery 상황을 나타내는 모든 product의 pname, client의 cname, delivery의 num으로 이루어진 튜플의 검색
    • SELECT product.pname, client.cname, delivery.num FROM product JOIN delivery ON product.id = delivery.pid JOIN client ON delivery.cid = client.id;의 실행


중첩 질의 入れ子問い合わせ (Nested Query)

  • 질의 지정의 WHERE절 검색 조건에 또 다른 질의 지정을 넣는 것
  • P100 제품을 납품하고 있는 client의 id와 name을 구하는 것
SELECT id, cname  
    FROM client  
    WHERE id IN  
    (SELECT cid  
    FROM delivery  
    WHERE pid = "P100");


집계 연산: COUNT, SUM, AVG, MAX, MIN

SELECT COUNT(*)  
FROM delivery  
WHERE pid = "P100";

SELECT SUM(num)  
FROM delivery  
WHERE cid = "C3";


편리한 검색 기능

Group by

  • 납품한 상품의 총수를 상품 번호별로 구함
SELECT pid, SUM(num)  
FROM delivery  
GROUP BY pid;


  • 상품의 평균 정가를 상품명별로 구함
SELECT pname, AVG(product.price) AS averagePrice  
FROM product  
GROUP BY pname;


Having

  • 납품 총수가 10 이상인 상품에 한해 상품 번호와 총수의 쌍을 구함
SELECT pid, SUM(num)  
FROM delivery  
GROUP BY pid  
HAVING SUM(num) >= 10;


Order by

  • 상품을 정가가 낮은(높은) 것부터 높은(낮은) 것으로 순서대로 정렬
SELECT *  
FROM product  
ORDER BY price ASC(DESC);


질의 언어가 가져야 하는 성질

  • 관계 완비성(Relational Completeness)
    • 관계 대수 또는 관계 논리의 질의 기술 능력을 갖추는 것
  • SQL은 관계 완비성을 만족


You may also enjoy