데이터베이스와 SQL

데이터베이스

  • 데이터베이스: 데이터란 컴퓨터 안에 기록되어 있는 숫자를 의미하며, 이러한 데이터의 집합을 데이터베이스라고한다.
  • 데이터베이스는 일반적으로 데이터센터의 서버에서 운용하지만 개인용 컴퓨터나 휴대용 기기에 내장되어 있기도 하다.
  • 데이터베이스를 효율적으로 관리하는 소프트웨어를 데이터베이스 관리 시스템, 약자로 DBMS라 부른다. DBMS의 사용 목적은 다음과 같다.
    • 생산성: 시스템을 구축할 때 검색, 추가, 삭제, 갱신과 같은 기본 기능부터 구현할 필요가 없어진다.
    • 기능성: DBMS는 데이터베이스를 다루는 기능을 많이 제공한다. 복수 유저의 요청에 대응하거나, 대용량의 데이터를 저장하고 고속으로 검색하는 기능을 제공하기도 한다.
    • 신뢰성: 실제 DBMS는 컴퓨터 여러 대를 두고, 소프트웨어를 통해 확장성과 부하 분산을 구현한다. 이를 보통 클러스터 구성, 스케일 아웃이라고부른다. 또한 데이터베이스의 데이터를 다른 저장장치로 내보내거나 집어넣는 등의 기능을 갖출 수 있다.
  • SQL명령의 종류
    • DML(Data Manipulation Language): 데이터베이스에 새롭게 데이터를 추가하거나 삭제하거나 내용을 갱신하는 등, 데이터를 조작할 때 사용합니다.
    • DDL(Data Definition Language): 데이터를 정의하는 명령어. 데이터베이스는 ‘데이터 베이스 객체’라는 데이터 그릇을 이용하여 데이터를 관리하는데, 이 같은 객체를 만들거나 삭제하는 명령어다.
    • DCL(Data Control Language): 데이터를 제어하는 명령어. 트랜잭션을 제어하는 명령과 데이터 접근권한을 제어하는 명령이 포함되어 있다.

다양한 데이터베이스

  • 데이터베이스 종류
    • 계층형 데이터베이스: 폴더와 파일 등의 계층 구조로 데이터를 저장하는 방식의 데이터베이스. 현재 DBMS로서 채택되는 경우는 많지 않다.
    • 관계형 데이터베이스: 관계 대수를 착안하여 고안한 데이터베이스.
    • 객체지향 데이터베이스: 가능하면 객체 그대로를 데이터베이스의 데이터로 저장하는 것
    • XML 데이터베이스: XML이란 자료 형식으로 데이터를 저장하는 데이터베이스
    • 키-밸류 스토어: 키와 밸류의 조합으로 나타내는 데이터베이스. NoSQL이라는 슬로건으로부터 생겨난 데이터베이스로 열 지향 데이터베이스라고도 부른다.
  • 각 데이터베이스 제품별로 SQL의 고유 방언이 있다. 하지만 이 같은 방언을 없애기 위해 표준 SQL을 정하고 표준화에 동참하려고 노력하고있다.

데이터베이스 서버

  • RDBMS은 웹 시스템과 마찬가지로 클라이언트/서버 모델로 시스템이 구성된다. 하지만 웹 시스템에는 없던 사용자 인증이 필요하다. 일단 한 번 데이터베이스에 접속하면, 이를 유지하여 재접속 없이 SQL 명령을 여러 번 보낼 수 있다.
  • CGI 프로그램이 데이터베이스의 클라이언트가 된다. 클라이언트와 서버가 네트워크로 연결되어 있다면 서로 다른 노드에 두어도 무방하다.
  • PC 한 대로 클라이언트와 서버 모두 실행할 수 있지만 네트워크 기능은 필요하다. 클라언트에서 서버에 접속할 필요가 있는데, 이때 네트워크를 경유해서 PC의 서버로 되돌아오는 형태로 접속한다. ‘루프 백 접속’이라고 한다.

테이블에서 데이터 검색

Hello World 실행하기

  • 데이터베이스에는 테이블 외에 다양한 데이터를 저장하거나 관리하는 ‘어떤 것’을 만들 수 있다. 이것을 ‘데이터베이스 객체’라고 부른다.
  • 예약어와 데이터베이스 객체명은 대소문자를 구별하지 않는다.
  • 데이터는 자료형으로 분류할 수 있다. 열은 하나의 자료형만 가질 수 있다.
  • NULL은 특별한 데이터 값으로 아무거소 저장되어 있지 않은 상태를 의미한다.

테이블 구조 참조하기

  • INTEGER: 정수값
  • CHAR: 고정 길이 문자열
  • VARCHAR: 가변 길이 문자열
  • DATE: 날짜값을 저장할 수 있는 자료형
  • TIME: 시간을 저장할 수 있는 자료형

검색 조건 지정하기

  • SELECT 구에서 표시하고 싶은 열을 지정할 수 있다.
  • WHERE 구에서 표시하고 싶은 행을 지정할 수 있다.
  • 연산자: =, <>, >, ≥, <. ≤, IS NULL

조건 조합하기

  • AND, OR: AND가 OR 보다 연산 우선 순위가 높다. 괄호()로 연산 순서를 바꿀 수 있다.
  • NOT

패턴 매칭에 의한 검색

  • LIKE 예약어를 사용하여 패턴 매칭으로 검색할 수 있다.
  • %: 임의의 문자열과 매치하며, 빈 문자열에도 매치된다.
  • _: 임의의 문자 한 개
  • %나 _ 문자를 LIKE로 검색할 경우에는 이스케이프 ‘' 를 사용한다.
  • ‘를 검색하고 싶으면 ‘를 두 번 써서 ‘‘로 찾는다.

정렬과 연산

정렬 - ORDER BY

 SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명 [ASC|DESC]
  • ASC: 오름차순, DESC: 내림차순
  • 수치형 데이터와 날짜시간형 데이터는 숫자의 크기로 대소 관계를 판별한다.
  • 문자열형 데이터는 사전식 순서로 대소 관계를 판별한다.
  • ORDER BY는 테이블에 영향을 주지 않는다.

복수의 열을 지정해 정렬하기

 SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명1 [ASC|DESC], 열명2 [ASC|DESC], ...
  • 복수의 열로 정렬을 원하면 콤마(,)로 열명을 구분해서 나열하면 된다. 먼저 정렬하려고 하는 열을 앞에 오도록 한다.
  • 복수 열을 지정하는 경우에 정렬방법을 생략하면 기본값은 ASC가 된다. 하지만 데이터베이스 제품에 따라 기본값을 변경 할 수도 있으므로 주의해야된다. 문장의 가독성을 높이기 위해서 가능하면 정렬방법을 생략하지 말고 지정하도록 한다.
  • NULL은 표준 SQL에 규정되어 있지 않아 데이터베이스 제품에 따라 기준이 다르다.
  • MySQL의 경우에는 NULL을 가장 작은 값으로 취급해 ASC에서는 가장 먼저, DESC에서는 가장 나중에 표시된다.

결과 행 제한하기 - LIMIT

SELECT 열명 FROM 테이블명 LIMIT 행수 OFFSET 위치
  • LIMIT 구는 표준 SQL 이 아니다. MySQL과 PostgreSQL은 LIMIT를 사용할 수 있다. SQL Server에서는 SELECT 뒤에 TOP을 붙여 사용한다. Oracle에는 WHERE 구에 ROWNUM을 사용한다. ROWNUM은 클라이언트에게 결과가 반환될 때 각 행에 할당되는 행 번호다. (이는 정렬전에 처리 된다는 문제가 있다)
  • LIMIT의 경우 WHERE → OREDER BY → LIMIT의 순서대로 처리된다.
  • OFFSET으로 pagination 기능을 구현할 수 있다. OFFSET은 0부터 시작하므로 유의해서 사용해야된다.

수치 연산

  • +, -, *, /, % (MOD)
  • 연산은 SELECT, WHERE, ORDER BY 에서 사용 가능하다.
  • AS 로 열의 별명을 지을 수 있다.
  • NULL은 어떤 연산을 해도 NULL이 나온다.
  • SQL 처리 순서는 WHERE → SELECT → ORDER BY 이므로 SELECT에서 AS 로 지은 별명을 WHERE에서는 쓸 수 없지만 ORDER BY에서는 쓸 수 있다.
  • 함수
    • ROUND(numeric_expression, lenght)
      • length를 지정하지 않으면 기본적으로 소수점 첫째 자리에서 반올림한다.
      • 소수점 (length + 1)째 자리에서 반올림한다.
      • -1이면 일의 자리, -2이면 십의 자리에서 반올림한다.
    • SIN, COS, LOG 등의 함수가 있다.

문자열 연산

  • 문자열 결합
    • SQL Server: +
    • Oracle, DB2, PostgreSQL:  
    • MySQL: CONCAT
  • SUBSTRING: 문자열의 일부분을 계산해서 반환해주는 함수.
    • SUBSTRING ( expression ,start , length )
    • start는 1부터 시작한다.
  • TRIM: 문자열의 앞뒤로 여분의 스페이스가 있을 경우 이를 제거해준다.
  • CHARACTER_LENGTH: 문자열의 길이를 반환
  • OCTET_LENGTH: 문자열의 길이를 바이트 단위로 계산해 돌려준다.
    • ‘ASCII’, ‘EUC-KR’, ‘UTF-8’ 등의 인코딩 방식(문자세트)에 따라 한 문자의 바이트 수가 다르다.
    • VARCHAR에서 지정하는 최대 길이는 바이트 단위다.

날짜 연산

  • CURRENT_TIMESTAMP: 현재 시스템 날짜와 시간을 반환한다.
  • TO_DATE(‘2014/01/25’, ‘YYYY/MM/DD’): 문자열 데이터를 날짜형 데이터로 변환할 수 있으며 서식 또한 별도로 지정할 수 있다.
  • 날짜시간형 데이터는 수치데이터와 덧셈 및 뺄셈을 할 수 있다.
  • 날짜시간형 데이터 간에 뺄셈을 할 수 있다.

CASE 문으로 데이터 변환하기

  • 검색 CASE
CASE WHEN 조건식1 THEN 식1
[WHEN 조건식2 THEN 식2 ...]
[ELSE 식3]
END
  • 단순 CASE
CASE 식1
	WHEN 식2 THEN 식3
	[WHEN 식4 THEN 식5 ... ]
	[ELSE 식6]
END
  • COALESCE: 여러 개의 인수 중에 NULL이 아닌 값중 가장 먼저 지정된 값을 반환한다. ex) COALESCE(a, 0) ⇒ a가 NULL이면 0 반환
  • CASE 문의 ELSE는 생략하지 않는 편이 좋다.
  • 단순 CASE에서 NULL 값인지 아닌지 판정하기 위해서느 IS NULL을 사용해야된다. 그냥 NULL을 사용하면 원하는대로 작동하지 않는다.

데이터의 추가, 삭제, 갱신

행 추가하기 - INSERT

INSERT INTO 테이블명 VALUES (값1, 값2, ...)
  • 위의 방법으로 데이터를 추가할 경우 열의 순서대로 값을 입력해야된다.
INSERT INTO 테이블명 (열1, 열2, ...) VALUES (값1, 값2, ...)
  • 위의 방법으로 값을 저장한 열을 지정할 수 있다.
  • DEFAULT: 명시적으로 값을 지정하지 않을 경우 초기값을 의미한다. 디폴트값을 데이터로 사용하는 방법에는 명시적으로 저장하는 방법과 암묵적으로 디폴트 저장하는 방법이 있다.
    • 명시적
      INSERT INTO sample411 (no, d) VALUES (2, DEFAULT)
    • 암묵적
      INSERT INTO sample411 (no) VALUES (3)
  • NOT NULL 제약이 걸려있는 열은 NULL 값을 허용하지 않고, DEFAULT 값으로 NULL을 사용할 수 없다.

삭제하기 - DELETE

DELETE FROM 테이블명 WHERE 조건식
  • DELETE 명령에서도 SELECT 명령처럼 WHERE 구를 지정할 수 있다. 하지만 ORDER BY 구는 사용할 수 없다.

데이터 갱신하기 - UPDATE

UPDATE 테이블명 SET 열1 = 값1, 열2 = 값2, ... WHERE 조건식
  • SET 구의 실행 순서는 데이터베이스 제품에 따라 처리 방식이 다르다.
  • NULL로도 갱신이 가능하다.

물리삭제와 논리삭제

  • 데이터 삭제에는 두 가지의 사고 방식이 있다.
  • 물리삭제: DELETE 명령을 상용해 직접 데이터를 삭제하는 방법
  • 논리삭제: 테이블에 삭제플래그의 열을 두고 UPDATE 명령을 통해 삭제 플래그를 갱신한다.
  • 논리삭제의 장점으로는 데이터를 삭제하지 않기 떄문에 삭제되기 전의 상태로 간단히 돌아갈 수 있다. 단점으로는 삭제해도 데이터베이스의 저장공간이 늘어나지 않고, 데이터베이스의 크기가 증가함에 따라 검색속도가 떨어질 수 있다는 점을 들 수 있다.

집계와 서브쿼리

행 개수 구하기 - COUNT

COUNT(집합) FROM 테이블명 WHERE 조건문;
  • COUNT는 NULL값을 포함하여 세지 않는다.
  • DISTINCT로 중복제거
    • ALL로 지정하면 모든 행이 반환된다. 아무 것도 입력하지않으면 ALL로 간주된다.
    • 집계함수에서도 DISTINCT를 사용할 수 있다.

    예)

      SELECT COUNT(DISTINCT name) FROM sample51;

COUNT 이외의 집계함수

SUM([ALL|DISTINCT] 집합)
AVG([ALL|DISTINCT] 집합)
MIN([ALL|DISTINCT] 집합)
MAX([ALL|DISTINCT] 집합)
  • 모두 NULL은 제외되고 처리된다.
  • 만약 NULL을 0으로 간주해서 계산하고싶다면, CASE 문을 사용하면 된다.

그룹화 - GROUP BY

  • GROUP BY 를 사용하면 그룹 단위로 집계함수를 사용할 수 있다.
  • 집계함수를 WHERE에서 사용하기 위해서는 HAVING 구를 사용해야된다. sql의 연산 순서는 아래와 같기 때문이다.
WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
  • SELECT 보다 HAVING이 먼저 처리되기 때문에 SELECT에서 AS로 지정한 별명은 사용할 수 없다.
  • GROUP BY에 복수열을 지정하여 그룹화할 수도 있다.
  • ORDER BY에서는 그룹화 한 내용의 집계 함수를 사용할 수 있다.

서브쿼리

  • 서브쿼리를 사용할 때 하나의 값만 반환하는 것을 ‘스칼라 값을 반환한다’고 한다.

  • DELETE의 WHERE 구에서 서브 쿼리 사용 예시

      DELETE FROM sample54 WHERE a = (SELECT MIN(a) FROM sample54);
  • = 연산자를 사용하여 비교할 경우에는 스칼라 값끼리 비교할 수 있다.

  • SELECT 구에서 서브쿼리 사용하기(항상 스칼라 서브쿼리어야한다)

      SELECT
  	(SELECT COUNT(*) FROM sample51) AS sq1,
  	(SELECT COUNT(*) FROM sample54) AS sq2;

  • SET 구에서 서브쿼리 사용하기(항상 스칼라 서브쿼리어야한다)

      UPDATE sample54 SET a = (SELECT MAX(a) FROM sample54);

  • FROM 구에서 서브쿼리 사용하기(항상 스칼라일 필요 없다)

      SELECT * FROM (SELECT * FROM sample54) AS sq;

  • INSERT에서 서브쿼리 두 가지 방법

      INSERT INTO sample541 VALUES (
  	(SELECT COUNT(*) FROM sample51),
  	(SELECT COUNT(*) FROM sample54)
  );

      // 테이블 복사
  INSERT INTO sample 541 SELECT * FROM sample543;

상관 서브쿼리

  • EXISTS
    • 데이터가 존재하는지 아닌지 판별하기 위해 조건을 지정할 수 있다.
    • EXISTS 술어에 서브쿼리를 지정하면 서브쿼리가 행을 반환할 경우에 참을 돌려준다. 한 줄이상이라도 참이 된다.
      UPDATE sample551 SET a = '있음' WHERE EXISTS ...
  • NOT EXISTS
  • 상관 서브쿼리에서는 부모 명령과 연관되어 처리되기 때문에 서브쿼리 부분만을 따로 떼어내서 실행시킬 수 없다.
  • IN
    • 집합 안의 값이 존재하는지 조사할 수 있다.
      SELECT * FROM sample551 WHERE no IN (SELECT no2 FROM sample552);
  • 집계함수에서는 집합 안의 NULL 값을 무리하고 처리했다. 하지만 IN 에서는 집합 안에 NULL 값이 있어도 무시하지 않는다.

데이터베이스 객체 작성과 삭제

데이터베이스 객체

  • 데이터베이스 객체: 테이블이나 뷰, 인덱스 등 데이터베이스 내에 정의하는 모든 것
  • 객체 이외에도 테이블의 열 또한 이름을 가진다. 객체의 이름은 중복될 수 없다.
  • 데이터베이스 객체는 스키마라는 그릇 안에 만들어진다. 객체의 이름이 같아도 스키마가 서로 다르다면 상관없다.
  • 실제로 데이터베이스에 테이블을 작성해서 구축해가는 작업을 ‘스키마 설계’라고 부른다.

테이블 작성,삭제,변경

  • 테이블 작성

      CREATE TABLE 테이블명 (
  	열명 자료형 [DEFAULT 기본값] [NULL|NOT NULL],
  	열명 자료형 [DEFAULT 기본값] [NULL|NOT NULL],
  	...
  )

  • 테이블 삭제

      DROP TABLE 테이블명
    • 데이터 행 전체를 삭제할 때 DELETE 명령을 사용하면 행단위로 처리되기 때문에 처리속도가 느리다. GRUNCATE TABLE 을 사용하면 테이블 내의 모든 행을 삭제할 수 있다.

  • 테이블 변경

    • 테이블의 모든 변경의 ALTER TABLE 명령을 통해 이루어진다.

    • 열 추가

        ALTER TABLE 테이블명 ADD 열 정의

      시스템의 기능 확장등의 이유로 열 추가를 한다. 하지만 테이블 정의가 바뀌면 기존의 모든 INSERT 명령을 확인해야된다.

    • 열 속성 변경

        ALTER TABLE 테이블명 MODIFY 열 정의

      데이터 최대길이 연장할 때 사용되기도 한다.

    • 열 이름 변경

        ALTER TABLE 테이블명 CHANGE [기존 열 이름] [신규 열 정의]

    • 열 삭제

        ALTER TABLE 테이블명 DROP 열명ABLE 테이블명 DROP 열명

제약

  • 제약: 테이블 저장된 데이터들의 제한되는 조건
  • 제약의 종류
    • 열 제약: 열 하나에 정의되는 제약
    • 테이블 제약: 복수개의 열에 정의하는 제약

  • 제약에 이름을 붙이면 나중에 관리하기 쉬워진다.

      CONSTRAINT pkey_sample PRIMARY KEY (no, sub_no)
  • 제약 추가: 제약을 추가할 때는 기존 데이터에 제약을 위반하는 것이 있는지 검사한다.
    • 열 제약 추가
      ALTER TABLE sample631 MODIFY c VARCHAR(30) NOT NULL
    • 테이블 제약 추가
      ALTER TABLE sample631 ADD CONSTRAINT pkey_sample631 PRIMARY KEY(a)
  • 제약 삭제
    • 열 제약 삭제는 열 제약 추가와 같은 방법이다.
    • 테이블 제약 삭제
      ALTER TABLE sample631 DROP CONSTRAINT pkey_smap631
    • PRIMARY KEY 제약은 테이블 당 하나만 설정할 수 있기 때문에 이름이 없어도 삭제가 가능하다.
      ALTER TABLE sample631 DROP PRIMARY KEY;
  • 기본키: 기본키는 테이블의 행 한 개를 특정할 수 있는 검색키다. 또한 기본키는 복수의 열로도 구성이 가능하다.

인덱스 구조

  • 인덱스는 데이터베이스 객체 중 하나로 검색속도를 향상 시킬 수 있다.
  • 인덱스가 지정되지 않은 테이블은 검색할 때 ‘풀 테이블 스캔’으로 모든 행을 확인한다.
  • 인덱스가 지정되어 있는 테이블은 이진 탐색이 가능하다. 인덱스가 이진 트리 구조로 작성되기 때문이다.

인덱스 작성과 삭제

  • 인덱스 작성
CREATE INDEX 인덱스명 ON 테이블명 (열명1, 열명2, ... )
  • 인덱스 삭제(스키마 객체의 경우)
DROP INDEX 인덱스명
  • 인덱스 삭제(테이블 내 객체의 경우)
DROP INDEX 인덱스명 ON 테이블명
  • 인덱스를 사용해 검색하는지 확인하기 위해 EXPLAIN을 사용할 수 있다
EXPLAIN SELECT * FROM sample62 WHERE a = 'a';
  • 최적화
    • SELECT 명령을 실행하면 내부 처리에서는 SELECT 명령을 실행하기 전에 실행 계획을 세운다. 실행계획에서는 ‘인덱스가 지정된 열이 WHERE 조건으로 지정되어 있으니 인덱스를 사용하자’와 같은 처리가 이루어진다.
    • 인덱스의 유무뿐만 아니라 인덱스를 사용할 것인지 여부에대해서도 데이터베이스 내부의 최적화 처리를 통해 판단한다. 판단 기준으로는 인덱스의 품질도 있다.
    • 중복되는 데이터가 많아질수록 인덱스의 효율이 떨어진다. 이런 경우 인덱스의 품질이 나빠진다.

뷰 작성과 삭제

  • FROM 구에서 기술된 서브쿼레이 이름을 붙이고 데이터베이스 객체화 하여 쓰기 쉽게 한 것을 뷰라고한다.
  • 뷰는 실체가 존재하지 않아서 ‘가상 테이블’이라 불리기도 한다.
  • 뷰의 작성과 삭제
-- 뷰 생성
CREATE VIEW sample_view_67 AS SELECT * FROM sample54; 
-- 뷰 생성 (열 지정)
CREATE VIEW sample_view_672(n,v,v2) AS SELECT no, a, a*2 FROM sample54;
-- 뷰 삭제
DROP VIEW sample_viee_67;
  • 뷰의 약점
    • 일반적인 뷰는 데이터를 일시적으로 저장했다가 쿼리가 실행 종료될 때 함께 삭제된다. 따라서 근원이 되는 테이블에 데이터가 많은 경우 처리 속도가 느려진다.
    • 이를 해결하기 위해 머티리얼라이즈드 뷰(Materialized View)를 사용한다. 머티리얼라이즈드 뷰는 처음 참조되었을 때 데이터를 저장해두고 다시 참조할 때 저장해둔 데이터를 불러와 사용한다. 만약 뷰에 지정된 테이블에 값이 변한다면 SELECT 명령이 왔을 때 재실행하여 데이터를 다시 저장한다. 머티리얼라이즈드 뷰는 아쉽게도 MySQL에서는 사용할 수없다. Oracle과 DB2에서만 사용가능하다.
  • 뷰는 상관 서브쿼리는 만들 수 없다는 단점을 가지고 있다. 이를 해결하기 위해 함수 테이블을 사용할 수 있다. 함수에는 인수를 지정할 수 있기 때문에 인수의 값에따라 WHERE 조건을 붙여 결과값을 바꿀 수 있다.

복수의 테이블 다루기

집합 연산

  • UNION 키워드로 SELECT 명령 결과의 합집합을 구할 수 있다.
SELECT * FROM sample71_a
UNION
SELECt * FROM sample71_b;
  • UNION을 사용할 때는 두 SELECT 명령이 같은 수와 같은 자료형의 열을 가져야된다.
  • UNION을 사용할 때 정렬을 하고싶다면 마지막 SELECT 명령에 지정해야된다. 이 때 합칠 열들에 이름을 지정해줘야된다.
SELECT a AS c FROM sample71_a
UNION
SELECT b AS c FROm sample71_b ORDER BY c;
  • UNION에 중복을 제거하지 않고 합치고싶다면 UNION ALL 을 사용할 수 있다. 이미 존재하는 값인지 검사 처리가 필요 없기 때문에 더 좋은 성능을 보여준다.
  • 교집합은 INTERSECT, 차집합은 EXCEPT를 사용할 수 있다. 하지만 MySQL을 지원하지 않는다.

테이블 결합

  • FROM 구에 여러 개의 테이블 명을 기입하면, 교차 결합(곱집합)한다.
  • 기존의 내부 결합
SELECT product.name, stock.stock FROM product, stock
WHERE product.id = stock.product_id
  • 최근의 내부 결합
SELECT * FROM 테이블명1 INNER JOIN 테이블명2 ON 결합조건
  • 다른 테이블의 기본키를 참조하는 열을 외부키라고 한다.
  • 어느 한 쪽에만 존재하는 데이터행을 결합 하는 경우를 외부결합이라고 부른다.
  • 외부결합
SELECT * 
	FROM 테이블명1 LEFT JOIN 테이블명2
	ON 테이블명1.컬렴명1 = 테이블명2.컬럼명2
  • 왼쪽이 기준이 되면 LEFT JOIN , 오른쪾이 기준이 되면 RIGHT JOIN 이 된다. 기준이 되지 않는 쪽에서는 NULL일 수도 있다.

관계형 모델

  • SQL 용어와 관계형 모델 용어는 일치하지 않는다.
    • 릴레이션: 테이블
    • 속성: 열
    • 튜플: 행
  • 관계대수: 릴레이션에 대한 연산이 집합의 연산에 대응된다는 이론. 관계대수의 기본규칙은 다음과 같다
    • 하나 이상의 관계를 바탕으로 연산한다.
    • 연산한 결과, 반환되는 것 또한 릴레이션이다.
    • 연산을 중첩 구조로 실행해도 상관없다.
  • 관계형 모델과 SQL
    • 합집합: UNION
    • 차집합: EXCEPT
    • 교집합: INTERSECT
    • 곱집합: CROSS JOIN, FROM 구에 복수의 테이블
    • 선택: WHERE 구에 조건 지정
    • 투영: SELECT 구에 결과로 반환할 열 지정
    • 결합: 내부결합

데이터베이스 설계

  • 데이터베이스를 설계한다는 것은 데이터베이스의 스키마 내에 테이블, 인덱스, 뷰 등의 데이터베이스 객체를 정의하는 것을 말한다.
  • 물리명은 CREATE TABLE에 지정하는 테이블 이름이나 열 이름이다. 논리명은 설계상의 이름이다.
  • 데이터에 따라서 ‘1, 2, 3 중에 하나’ 또는 ‘yes, no중에 하나’만 데이터 값으로 취급하년 경우가 생기기도 한다. 이런 경우에는 데이터베이스 기능으로 제약(CHECK 제약)을 걸 수 있다.
  • 일반적으로 데이터베이스 시스템에서 데이터 정합성을 체크할 수 있다면 데이터베이스에 맡겨버리는 편이 가장 확실하고 편리하다. 애플리케이션에서 따로 구현하면 개발비용도 상승한다.
  • VARCHAR 형으로 지정할 수 있는 최대 크기는 기껏해야 수천 바이트다. 조금 큰 파일은 LOB을 사용한다. LOB 형은 큰 데이터를 다루는 자료형이지만 인덱스를 지정할 수 없다는 제약이 있다.
  • 기본키로 지정할 열이 생각나지 않는 경우 자동증가(AUTO_INCREMENT) 열을 사용해서 기본키로 지정하면 간단하게 해결할수있다.
  • ER다이어그램: 테이블 간의 관계를 명확히 하기 위한 설계도
  • ER다이어그램에서 연계를 표기할 때 세 가지가 있다.
    • 일대일
    • 일대다
    • 다대다
  • ER다이어그램의 연계는 데이터베이스에서는 외부키 제약으로 지정하는 경우가있다. 외부키 제약을 설정하면 데이터 정합성이 엄격히 관리되어 번거로워진다는 이유로 이를 채용하지 않는 시스템도 있다.

정규화

  • 정규화란 데이터베이스의 테이블을 규정된 올바른 형태로 개선해나가는 것입니다.
  • 제1 정규형: 테이블의 하나의 셀에는 하나의 값만 저장할 수 있다. 반복되는 데이터를 가로가 아닌 세로로 늘린다. 그리고 테이블 분할을 한후 기본키를 구성한다.
  • 제2 정규형: 기본키에 의해 특정되는 열과 그렇지 않은 열로 나누어 테이블을 분리한다. 즉, 함수종속성을 찾아내서 테이블을 분할하는 것이다.
  • 제3 정규형: 기본키 이외의 부분에 중복이 없는지 조사한 훈 테이블로 분리한다.
  • 정규화는 중복되는 부분을 찾아내서 테이블을 분할하고 기본키를 작성해 사용하는 것을 기본 개념으로 삼는다. 이는 하나의 테이블을 한 곳에 있어야 한다는 규칙에 근거한다. 하나의 데이터가 한 곳에만 저장되어 있따면 데이터를 변경하더라도 한 곳만 변경하는 것으로 끝낼 수 있다.

트랜잭션

  • 몇 단계로 처리를 나누어 SQL 명령을 실행하는 경우에 트랜잭션을 자주 사용한다. 트랜잭션을 사용해서 데이터를 추가할때 에러가 발생하면 트랜잭션을 롤백해서 종료할 수 있다. 롤백하면 트랜잭션 내에서 행해진 모든 변경사항을 없던 것으로 할 수 있다.
  • INSERT, UPDATE, DELETE는 자동커밋이 켜져 있는 상태이다.
  • START TRANSACTION 명령을 사용해 자동커밋을 끌 수 있다.
START TRANSACTION;
COMMIT;
ROLLBACK;

댓글남기기