[DB]관계형 데이터베이스 정리 & DDL, DML, JOIN 사용 정리

관계형 데이터베이스

• 일반적으로 많이 사용되는 데이터베이스의 한 종류
• 테이블 간 관계를 설정하여 여러 데이터를 조작, 관리할 수 있음
• 데이터를 테이블, 행(레코드, 튜플), 열(필드, 속성) 등으로 나누어 구조화하는 방식

관계형 데이터베이스 장점

• 데이터의 분류, 정렬, 탐색 속도 빠름
• 데이터의 무결성(정확성, 일관성) 유지
• 정확성 : 데이터가 정확한 값을 갖는 것 (제약조건에 위반이 없는지, 중복이 없는지 등)
• 일관성 : DB내의 모든 데이터가 일관된 상태를 유지하는 것

관계형 데이터베이스 단점

• 기존에 작성된 스키마를 수정하기 어려움
• 데이터베이스의 부하 분석에 어려움
• 무결성 유지를 위해 정규화를 통해 테이블을 쪼개서 관리해야 함
  • 쿼리문 (SQL문)이 복잡해짐
  • 대용량 데이터 처리가 느림

관계형 데이터베이스의 구조

• 스키마 : 테이블의 구조로, 데이터베이스에서 자료의 구조, 표현 방법, 관계 등 전반적인 명세를 기술한 것
• 열(필드, 속성) : 각 열에는 고유한 데이터 타입 저장
• 행(레코드, 튜플) : 테이블의 데이터는 레코드에 저장

SQL

DDL(Definition) : 데이터 정의 언어

• 데이터베이스 구조(테이블, 스키마)를 다루기 위한 언어 (CREATE, DROP, ALTER)
• 실제로 설계단계, 유지보수 단계에서 많은 회의를 거친 후 작업
• 잘못 설계(사용)하면 시스템 성능에 치명적일 수 있음

DML(Manipulation) : 데이터 조작 언어

• 데이터를 조작(CRUD) 하기 위한 언어 (INSERT, SELECT, UPDATE, DELETE)
• 나쁜 SQL문은 시스템의 성능을 나쁘게 할 수 있음

DCL (Control) : 데이터 제어 언어

• 데이터의 보안, 수행제어, 사용자 권한 등의 작업을 하기 위한 언어 (GRANT, REVOKE, COMMIT, ROLLBACK)
• SQLite는 DB가 파일로 관리됨
• 파일 접근 권한으로 위의 작업을 수행

제약조건 종류

• NOT NULL
  • CREATE 문을 이용하여 NOT NULL 설정

  CREATE TABLE 테이블명
  {
  	필드이름 필드타입 NOT NULL
  };
  

  • 칼럼이 NULL 값을 허용하지 않도록 지정
  • 기본적으로 테이블의 모든 칼럼은 NOT NULL 제약 조건을 명시적으로 사용하는 경우를 제외하고는 NULL 값을 허용
• UNIQUE
  • CREATE 문을 이용하여 UNIQUE 설정

  CREATE TABLE 테이블명
  {
  	필드이름 필드타입,
  	제약조건이름 UNIQUE (필드이름)
  };
  

  • 칼럼의 모든 값이 서로 구별되거나 고유한 값이 되도록 함
  • 제약 조건을 설정하면, 해당 필드는 서로 다른 값을 가져야 함 (중복 불가)
• PRIMARY KEY
  • CREATE 문을 이용하여 PRIMARY KEY 설정

  CREATE TABLE 테이블명
  (
      필드이름 필드타입 PRIMARY KEY,
  );
  

  • 테블에서 행의 고유성을 식별하기 위해 사용하는 칼럼
  • 각 테이블에는 하나의 기본 키만 존재
  • 암시적으로 NOT NULL 조건이 포함되어 있음
• AUTOINCREMENT
  • CREATE 문을 이용하여 AUTOINCREMENT 설정

  CREATE TABLE 테이블명
  {
  	필드이름 필드타입 PRIMARY KEY AUTOINCREMENT,
  };
  

  • 사용되지 않은 값이나 이전에 삭제된 행의 값을 재사용하는 것을 방지
  • INTERGER PRIMARY KEY 다음에 작성되면 해당 rowid를 다시 재사용하지 못하게 함

DDL 사용 예시

-- SQL문 작성 시 주의사항
-- 세미콜론(;) 기준으로 하나의 SQL문 판별


-- id, 이름, 직럽, 능력, 국적, 소속회사, 나이, 가입날짜
-- 새로운 테이블을 생성하기
-- 필드명 타입 제약조건 순으로 작성
CREATE TABLE superheroes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    이름 TEXT NOT NULL,
    직업 TEXT NOT NULL,
    능력 TEXT,
    국적 TEXT,
    소속회사 TEXT,
    나이 INTEGER
);

--테이블명 변경하기
ALTER TABLE superheroes RENAME TO superhero;

-- 새로운 컬럼 추가
ALTER TABLE superhero ADD COLUMN 가입날짜 Date;

-- 임시 컬럼 추가 후 이름 변경
ALTER TABLE superhero ADD COLUMN 임시 TEXT;

ALTER TABLE superhero RENAME COLUMN 임시 to 삭제2;

ALTER TABLE superhero DROP COLUMN 삭제

DML 사용 예시

-- 예시 DB파일 -> superhero.sqlite3

-- 전체 필드 조회
SELECT * FROM superhero;

-- 특정 필드 조회
SELECT 이름 FROM superhero;
SELECT 이름, 소속회사 FROM superhero;

-- 없는 필드 조회시 : no such column 에러 출력
SELECT 없음 FROM superhero;

-- 별칭 지어주기 (Alias)
SELECT 이름 AS 활동명 FROM superhero;
SELECT 이름 AS 활동명, 소속회사 AS 팀 FROM superhero;

-- 나이가 적은순으로 정렬하여 출력 (오름차순)
-- SELECT <필드> FROM <테이블>
-- ORDER BY <필드> ASC
SELECT * FROM superhero ORDER BY 나이 ;
SELECT * FROM superhero ORDER BY 이름 ;

-- 나이가 많은순으로 정렬하여 출력 (내림차순)
-- SELECT <필드> FROM <테이블>
-- ORDER BY <필드> DESC
SELECT * FROM superhero ORDER BY 나이 DESC;
SELECT * FROM superhero ORDER BY 이름 DESC;

-- 중복 제거하기 (DISTINCE)
SELECT DISTINCT 소속회사 FROM superhero;

-- 여러 필드 사용 시 동일 데이터 삭제
SELECT DISTINCT 직업, 소속회사 FROM superhero;

-- 나이, 소속회사가 겹치지 않는 사람들 중
-- 소속회사, 나이 순으로 정렬
SELECT DISTINCT 나이, 소속회사 FROM superhero ORDER BY 소속회사, 나이;

-- 여러 필드로 정렬하기
-- 소속회사 별로 나이가 많은 순으로 정렬하기
SELECT * FROM superhero ORDER BY 소속회사, 나이 DESC;

-- 조건문(WHERE)
-- 직업이 악당인 사람들만 조회
SELECT * FROM superhero WHERE 직업 = '악당';

-- 나이가 50살이 넘는 사람들만 조회
SELECT * FROM superhero WHERE 나이 > 50;

-- 가입날짜가 2000년 1월 1일 이전인 사람 조회 (DATE 필드)
SELECT * FROM superhero WHERE 가입날짜 < DATE('2000-01-01');

--소속회사가 마블이고 직업이 영웅인 사람들만 조회
SELECT * FROM superhero WHERE 소속회사 = '마블' AND 직업 = '영웅';

-- 국적이 미국이거나 러시아인 사람들만 조회
SELECT * FROM superhero WHERE 국적 = '미국' OR 국적 = '러시아';

--특정 패턴에 만족하는 데이터를 조회
-- % : 글자수 제한 없이 패턴을 만족하는 조회
-- ex) 2글자인 데이터, ~~맨으로 끝나는 데이터
SELECT * FROM superhero WHERE 이름 LIKE '%맨';

-- _ : 개수 만큼 글자 수 제한하여 패턴을 만족하면 조회
-- ex) 이름이 두 글자인 사람들만 조회
SELECT * FROM superhero WHERE 이름 like '__';

-- 특정 데이터에 포함여부(IN)
SELECT * FROM superhero WHERE 소속회사 IN ('마블','DC');

-- 특정 데이터에 포함여부(NOT IN)
SELECT * FROM superhero WHERE 소속회사 NOT IN ('마블','DC');

-- 특정 조건 사이에 존재하는 데이터 조회(BETWEEN <> AND <> )
-- 나이가 100~500살 사이의 사람들을 조회
SELECT * FROM superhero WHERE 나이 BETWEEN 100 and 500;

----------------------------------------

-- 원하는 행 개수만큼만 조회(LIMIT)
SELECT * FROM superhero LIMIT 1;
SELECT * FROM superhero LIMIT 3;

-- 나이가 가장 적은 사람 1명
SELECT * FROM superhero ORDER BY 나이 LIMIT 1;

-- 소속회사가 마블인 사람 중 나이가 가장 적은 1명
SELECT * FROM superhero WHERE 소속회사 = '마블' ORDER BY 나이 LIMIT 1;

-- 나이가 많은 10명
SELECT * FROM superhero ORDER BY 나이 DESC LIMIT 10;

-- N 번째 데이터부터 조회 - 기준점 변경 (OFFSET)
-- 검색 기준점 : OFFSET + 1 부터
-- 나이가 10번째로 많은 사람
SELECT * FROM superhero ORDER BY 나이 DESC LIMIT 1 OFFSET 9;

-- 전체 데이터 수를 구하기
SELECT COUNT(*) AS COUNT FROM superhero;

-- 조건문과 함께 활용
SELECT COUNT(*) AS COUNT FROM superhero WHERE 직업 = '악당';

-- 전체 평균(숫자 o)
SELECT AVG(나이) AS 평균나이 FROM superhero;

-- 전체 평균(문자열은 x)
SELECT AVG(국적) AS 평균국적 FROM superhero;

-- 마블 영웅들의 평균 나이
SELECT 소속회사, AVG(나이) AS 마블평균나이 FROM superhero WHERE 소속회사 = '마블';

-- 각 소속회사의 평균 나이 구하기
SELECT 소속회사, AVG(나이) AS 마블평균나이 FROM superhero GROUP BY 소속회사;


-- 각 소속회사의 40살 이상인 영웅들의 평균나이 구하기
SELECT 소속회사, avg(나이) AS 평균나이 FROM superhero WHERE 나이 >= 40 GROUP BY 소속회사;

-- 그룹화 후에 조건 추가하기
-- 그룹화 이전 조건문은 WHERE / 그룹화 이후 조건문은 HAVING
SELECT 소속회사, avg(나이) AS 평균나이 FROM superhero GROUP BY 소속회사 HAVING avg(나이) >= 40;

JOIN 예시

-- JOIN 을 테스트 하기 위해 랜덤으로 NULL 값을 넣음
UPDATE hero SET 가입날짜 = NULL WHERE id = 10;
UPDATE hero SET 가입날짜 = NULL WHERE id = 20;
UPDATE hero SET 가입날짜 = NULL WHERE id = 25;
UPDATE hero SET job_id = NULL WHERE id = 30;
UPDATE hero SET job_id = NULL WHERE id = 40;
UPDATE hero SET job_id = NULL WHERE id = 50;
UPDATE hero SET company_id = NULL WHERE id = 64;
UPDATE hero SET company_id = NULL WHERE id = 75;
UPDATE hero SET company_id = NULL WHERE id = 88;
UPDATE hero SET country_id = NULL WHERE id = 16;
UPDATE hero SET country_id = NULL WHERE id = 46;
UPDATE hero SET country_id = NULL WHERE id = 57;

-- JOIN: 여러 테이블을 합쳐서 원하는 데이터를 조회하는 기법

-- CROSS JOIN
-- 두 테이블 간 가능한 모든 조합을 선택
SELECT * FROM hero, job;

-- INNER JOIN
-- 두 테이블에서 일치하는 값을 가진 행들만 선택
-- SELECT <필드> FROM 테이블1
-- INNER JOIN 테이블2
-- ON 조건
-- 전체 사람들의 id, 이름, 직업을 조회
SELECT hero.id, hero.이름, job.직업
FROM hero
INNER JOIN job
ON hero.job_id = job.id;

-- LEFT JOIN
-- 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 값을 가진 행을 선택
-- 일치하는 값이 없는 경우에는 NULL 값을 가짐
SELECT hero.id, hero.이름, job.직업
FROM hero
LEFT JOIN job
ON hero.job_id = job.id;

-- 똑같은 코드 다른 표현 방식1
-- 필드명이 겹치지 않는 부분은 명시하지 않을 수 있음
SELECT hero.id, 이름, 직업
FROM hero
LEFT JOIN job
ON hero.job_id = job.id;

-- 똑같은 코드 다른 표현 방식2
-- 각 테이블에 별칭을 주어서 표현
SELECT A.id, A.이름, B.직업
FROM hero AS A
LEFT JOIN job AS B
ON A.job_id = B.id;

-- 영웅들의 id, 이름, 능력을 조회
SELECT hero.id, hero.이름, power.능력
FROM hero
LEFT JOIN power
ON hero.id = power.hero_id;

-- 영웅들의 id, 이름, 직업, 소속회사를 조회
SELECT hero.id, hero.이름, job.직업, company.소속회사
FROM hero
    LEFT JOIN job
    ON hero.job_id = job.id
    LEFT JOIN company
    ON hero.company_id = company.id;