1.  일련번호 필드

🍊 기본키는 레코드의 유일성을 보장할 뿐만 아니라 관계를 형성하는 중요한 역할

🍊 그러나 기본키가 강제 규정은 아니어서 의무적으로 만들어야 하는 것은 아니며 기본키로 쓸만한 필드가 없는 경우도 발생

🔖 다음은 슈퍼마켓의 매출 데이터이며 누가 어떤 상품을 구매했는지 기록

🍋 조건을 만족하는 필드를 후보키 Candidate Key라고 하며 한 테이블에 여러 개의 후보키가 있음

🍋 이 중 레코드를 가장 잘 대표하는 키 하나를 골라 기본키 Primary Key (PK)로 선정

​

  💡 기본키가 충족해야 할 조건

       1)  대표성 : 레코드를 상징하는 값이어야 한다.

       2)  자주 참조하는 속성 : 기본키에는 기본적으로 인덱스가 생성되어 검색 효율이 좋다.

       3)  가급적 짧은 속성 : 테이블 간의 연결고리가 되므로 비교 속도가 빨라야 한다.

​

INSERT INTO tCity VALUES ('춘천',1116,27,'n','강원');

2.  기본키 설정

​

👩🏻‍💻 제약을 선언하는 위치에 따라 컬럼 제약과 테이블 제약이 있다.

👩🏻‍💻 컬럼 제약은 컬럼 선언 뒤에 위치하며 테이블 제약은 모든 컬럼 선언이 끝난 후 마지막 위치에 옴.

CREATE TABLE 테이블 ( 
   필드 선언, <- 이 위치에 오면 컬럼 제약 
   필드 선언, 
   필드 선언, 
            <- 이 위치에 오면 테이블 제약 
)

⚡️ NULL 허용 여부나, 기본값 등 컬럼에 대한 속성은 컬럼 제약으로 지정.

⚡️ 기본키는 컬럼 제약으로 선언할 수 도 있고 테이블 제약으로 선언할 수도 있음.

​

💡 각 제약의 형식

    * 컬럼 기본키 제약 : [CONSTRAINT 이름] PRIMARY KEY

    * 테이블 기본키 제약 : [CONSTRAINT 이름] PRIMARY KEY(대상 필드)

​

⚡️ PRIMARY KEY 제약은 NOT NULL 속성을 겸함.

⚡️ 제약의 이름을 생략하면 서버가 자동으로 이름을 붙임.

CREATE TABLE tCity ( 
  name CHAR(10), 
  area INT NULL , 
  popu INT NULL , 
  metro CHAR(1) NOT NULL, 
  region CHAR(6) NOT NULL, 
  CONSTRAINT PK_tCity_name PRIMARY KEY(name) 
 );

3.  복합키

​

👩🏻‍💻 기본키 필드가 꼭 하나여야 한다는 법은 없으며 하나의 필드만으로 레코드를 특정하기 어려운 경우가 있다.

👩🏻‍💻 이런 경우 두 개 이상의 필드를 묶어 기본키로 지정. 이것을 복합키 Composite Key라고 한다.

​

  ⚡️ tCity 테이블의 경우, 도시명이 중복되지 않는다는 가정을 하고 있지만 현실은 다름

  ⚡️ 경기도 광주, 전라도 광주 처럼 도시 이름이 중복되는 경우가 있는데 tCity 테이블의 구조로는 두 도시 모두 입력할 수 없음

         ➡️ 이름만으로 도시를 특정할 수 없으니 지역과 함께 묶어서 기본키로 정의해야 함.

CREATE TABLE tCity ( 
   name CHAR(10) PRIMARY KEY, 
   region CHAR(6) PRIMARY KEY, 
   area INT NULL , 
   popu INT NULL , 
   metro CHAR(1) NOT NULL 
);

    📌  name, region 두 개의 필드에 PRIMARY KEY를 지정하면 에러가 남 (테이블당 기본키는 하나만 지정 가능)

    📌  복합키로 지정할 때는 테이블 제약으로 기본키를 지정하며 괄호 안에 필드 목록을 콤마로 구분하여 나열한다.

CREATE TABLE tCityCompoKey ( 
  name CHAR(10) NOT NULL, 
  region CHAR(6) NOT NULL, 
  area INT NULL , 
  popu INT NULL , 
  metro CHAR(1) NOT NULL, 
  CONSTRAINT PK_tCity_name_region PRIMARY KEY (name, region) 
);

INSERT INTO tCityCompoKey VALUES ('광주', '전라', 123, 456,'y'); 
INSERT INTO tCityCompoKey VALUES ('광주', '경기', 123, 456,'n');

​

​

4.  유니크

​

👩🏻‍💻 유니크 UNIQUE 제약은 필드의 중복값을 방지하여 모든 필드가 고유한 값을 가지도록 강제

👩🏻‍💻 유니크는 기본키를 보조하는 중복 방지 제약

​

 💡 기본키 제약과의 차이점

      1)  기본키는 NULL을 허용하지 않지만 유니크는 NULL을 허용

            ➡️  단 NULL끼리도 중복 할 수 없어 딱 하나의 NULL만 존재할 수 있음

      2)  UNIQUE와 NOT NULL을 동시에 지정하면 기본키와 유사해짐.

            ➡️  기본키는 테이블당 하나만 지정할 수 있지만 유니크는 개수에 상관없이 얼마든지 지정 가능

      3)  기본키는 자동으로 인덱스를 생성하여 레코드의 정렬 순서를 결정하지만 유니크는 그렇지 않다

             ➡️  인덱스를 생성하더라도 기본키의 인덱스와는 종류와 효율이 다름

​

  📌  만일 tCity 테이블이 도시끼리 인구수가 같아서는 안된다는 규칙이 있다면 popu필드에 대해 UNIQUE 제약을 설정

CREATE TABLE tCityUnique ( 
   name CHAR(10) PRIMARY KEY, 
   area INT NULL , 
   popu INT UNIQUE NULL, 
   metro CHAR(1) NOT NULL, 
   region CHAR(6) NOT NULL 
);

  📌  이 테이블의 도시끼리는 인구가 같아서는 안됨. NULL은 가능하지만 하나만 가능.

  📌  NULL을 허가하지 않으려면 UNIQUE NOT NULL로 지정. 두 개 이상의 키를 묶어 복합 UNIQUE 제약을 걸 수도 있음

CREATE TABLE tCityUnique ( 
   name CHAR(10) PRIMARY KEY, 
   area INT NULL , 
   popu INT NULL, 
   metro CHAR(1) NOT NULL, 
   region CHAR(6) NOT NULL, 
   CONSTRAINT Unique_tCity_area_popu UNIQUE(area, popu) 
 );

5.  체크

​

👩🏻‍💻  체크 제약은 필드의 값 종류를 제한

👩🏻‍💻  모든 속성은 유의미한 범위가 있고 상식적으로 가능한 값과 그렇지 않은 값이 있음.

       ➡️ 예를 들어 도시가 아무리 거대해도 인구 100억을 넘길 수는 없고 면적이 음수가 될 수 없음.

       ➡️ 체크 제약은 이런 무의미한 값을 걸러냄

👩🏻‍💻 타입은 물리적인 형식을 점검하는데 비해 체크는 논리적인 값의 형식을 점검

​

  ⚡️ 필드 선언문에 CHECK 키워드와 함께 필드값으로 가능한 값을 조건문으로 지정

  ⚡️ WHERE 절의 조건을 지정하는 모든 문법을 쓸 수 있다.

CREATE TABLE tCheckTest ( 
   gender CHAR(3) NULL CHECK(gender = '남' OR gender = '여'), 
   grade INT NULL CHECK(grade >= 1 AND grade <= 3), 
   origin CHAR(3) NULL CHECK(origin IN ('동','서','남','북')), 
   name CHAR(10) NULL CHECK(name LIKE '김%') 
);

  📌  각 필드에 조건문으로 체크 제약을 지정

  📌  성별을 저장하는 gender 필드는 '남', 아니면 '여'만 가능

         ➡️ gender가 둘 중 하나임을 명시하여 이 두 값 이외에는 입력을 금지

  📌  grade는 중고등학교의 학년을 표현하는데 1 ~ 3까지만 유효

         ➡️ 초등학교라면 6까지, 대학교라면 4까지 범위를 늘이면 됨.

         ➡️ 일정 범위내일 때는 최소값, 최대값을 지정하는 대신 BETWEEN AND 조건문이 편리

              ex. grade INT NULL CHECK (grade BETWEEN 1 AND 3)

  📌  origin 필드는 방위를 나타내는데 동서남북 넷 중 하나만 가능

         ➡️ 여러 개의 임의값 중 하나를 지정할 때는 IN 연산자가 편리

  📌  name 필드는 성씨가 김씨인 경우만 받아들임

         ➡️ 부분 문자열을 점검할 때는 LIKE 연산자를 사용

1. 무결성 관리

👩🏻‍💻 모든 데이터가 결함없이 완벽한 상태를 무결성 Intergrity 이라고 함

  📍 시스템이 완벽해도 무결성이 깨지는 경우가 종종 발생

        ➡️ 실수나 에러로 잘못된 데이터를 입력할 수도 있고 불가항력적인 요인으로 멀쩡하던 데이터가 파괴되기도 함

  📍 무결성이 깨지면 데이터를 사용하는 응용 프로그램도 제대로 돌아가지 x

    🔎  예를 들어 컴퓨터 부품을 파는 쇼핑몰의 주문 테이블을 살펴보자

  📍 첫 번째 레코드만 정상이고 이후의 레코드는 이상해 보임

        ➡️ 나종원의 노트북 32,767개는 정상 주문으로 보기 힘들고,

             다민기의 경우 주문날짜도 2042년이고 주문 개수도 음수

             라진규는 주문한 제품의 이름을 입력하지 않았음.

👩🏻‍💻  무결성이 깨지는 원인은 다양. 디스크가 기계적으로 손상되거나 네트워크의 불안정으로 데이터가 손실되는 경우, 가장 큰 원인은 사람의 실수

👩🏻‍💻  발생 가능한 모든 오류에 대처하는 방어 체계로 데이터를 처리하는 단계별로 서버측과 클라이언트측이 각자의 무결성 관리 정책을 제공

  📍 '클라이언트측'에서는 입력할 정보에 꼭 맞는 컨트롤로 오입력을 원천 차단, 유효한 값만 서버로 보냄.

  📍 '서버측'의 무결성 처리는 DBMS가 규칙을 기억하고 있다가 비정상 데이터의 입력을 거부하는 방식

[ 적용 범위에 따른 무결성 구분 ]

1.  단일행 서브쿼리

💡 서브쿼리란? 쿼리문 안에 또 쿼리문이 들어 있는 것

💡 서브쿼리는 다른 쿼리문안에 내장되어 있는 SELECT 문이며 연속적으로 실행할 쿼리를 하나로 합침

💡 SELECT로 할 수 있는 질문은 짧은 단문만 가능, FROM 절이 하나밖에 없어 한 테이블에 있는 정보만 조사할 수 있다.

      ➡️ 실제 작업을 할때는 복잡한 여러 단계의 질문을 한꺼번에 하는 경우가 많이 생김.

     ➡️ 복잡한 쿼리를 실행할 수 있는 방법이 바로 서브쿼리.

  📖  최대 인구수를 가진 도시를 구하는 문제

      🔎  MAX 함수의 결과는 집계한 하나의 값이고 name 필드는 도시명 여러 개여서 이 둘을 같이 출력 불가

      🔎  MAX(popu)는 최대 인구수를 집계한 것이 맞지만 name 도시중 어떤 도시가 그 인구수를 가진 것인지는 알 수 없음.

             ➡️ 즉, 왼쪽 열과 오른쪽 열이 연관성이 없음.

  📖  기계가 이 명령을 알아 들으려면 문법적으로 가능한 질문을 순서대로 해야 한다.

  1. 전체 도시의 인구 중 가장 큰 값을 조사

  2. 이 인구수를 가진 도시를 찾음

  📖  최대 인구수를 조사하고 이 수로부터 도시의 이름을 구해야 하니 두 번의 쿼리가 필요

        ➡️ 두 개의 쿼리를 하나로 묶어 실행할 때 쓰는 것이 서브쿼리

  💡 이 때 서브쿼리를 감싸는 전체 쿼리를 외부쿼리라 부름

       ➡️ 외부쿼리와 구분하고 실행 순서를 명확히 지정하기 위해 서브쿼리를 괄호로 감쌈

  📖  서브쿼리로 최대 인구수를 가진 도시명을 구함

        1. 괄호 안의 서브쿼리를 먼저 실행하여 최대 인구수를 구함.

        2. 서브쿼리를 포함하고 있는 외부쿼리를 실행하여 popu가 서브쿼리가 구한 최대 인구수와 같은 도시를 조사하여 출력.

💡 단일행 서브쿼리는 하나의 결과만 리턴하며 주로 WHERE, HAVING 등의 조건절에 사용

💡 복합 질문의 앞쪽 질문에 해당하는 값을 서브 쿼리로 조사해 놓고 외부쿼리에서 그 결과값을 사용하는 식으로 작성한다.

2.  서브쿼리 중첩

💡 서브쿼리는 독립적인 하나의 명령이기 때문에 외부쿼리와는 '다른 테이블을 읽을 수도 있음'

💡 순차적으로 실행되므로 두 쿼리문의 FROM 절에 각각 다른 테이블을 지정해도 된다.

💡 서브쿼리의 중첩 횟수에는 제약이 없어 서브쿼리내에 또 다른 서브쿼리를 포함할 수 있음

💡 리턴 값이 여러 개면 에러 처리되는데 이때는 TOP 1 (LIMIT 1) 을 넣어 첫 해당 값을 찾으면 된다.

3.  다중행 서브쿼리

💡 서브쿼리의 '결과가 하나뿐인 유형'을 단일행 서브쿼리

      ➡️ 단일값이므로 조건절에서 =, <, > 등의 비교 연산자와 함께 사용할 수 있음.

      ➡️ 비교 연산자는 필드의 값과 비교하는 것이어서 '우변이 반드시 하나의 확정된 값'이어야함.

💡 이에 비해 여러 행을 리턴하는 것이 다중행 서브쿼리

      ➡️ 단일값이 아닌 목록을 리턴하기 때문에 값끼리 비교하는 비교 연산자와 함께 사용할 수 없음

  📖  서브쿼리는 구입목록인 tOrder 테이블에서 향단이가 뭘 샀는지 조사

        ➡️ 안쪽 서브쿼리만 실행해 보면 향단이가 산 것은 대추와 사과 2개

        ➡️ = 연산자는 필드값을 두 개의 결과와 비교할 수 없어 다음 에러를 출력.

  📖 ' = '연산자로 비교하려면 조건절의 쿼리문은 반드시 하나의 단일 값을 리턴해야만 한다.

  📖  꼭 조사하려면 결과 셋 중 하나의 값만 조사해서 비교하면 됨

📖 구입한 상품 중 하나의 상품만 리턴하면 = 연산자로 비교할 수 있음.

📖 그러나 질문대로 향단이가 구입한 모든 상품의 가격을 알고 싶다면 IN 연산자를 사용

💡  IN은 = 과 달리 여러 개의 값과 비교

  📌 서브쿼리가 두 개 이상의 값을 리턴하면 결과셋을 괄호 안에 나열하며 IN 연산자는 이 값을 순서대로 비교

💡 결과가 없으면 NULL을 리턴하며 조건절에서 필드와 NULL을 비교하는 것은 문법적으로 가능.

      ➡️ 아무 결과셋도 출력하지 않을 뿐 에러는 아니다.

4.  다중열 서브쿼리

💡 단일행, 다중행 서브쿼리는 '결과셋의 컬럼이 하나'밖에 없으며 그래서 특정값과 비교할 수 있음.

💡 다중열 서브쿼리는 결과셋의 컬럼이 여러 개이며 한꺼번에 여러 값과 비교

  📌  윤봉길과 같은 부서에 근무하는 같은 성별의 직원 목록을 조사

  📌  먼저 윤봉길이 어느 부서에 근무하고 성별이 무엇인지 조사해야 하며 그 결과로부터 조건에 맞는 직원을 찾는다

  📌  WHERE 절에 비교 대상 필드를 괄호 안에 (depart, gender) 적으면 서브쿼리의 컬럼과 1:1로 비교하여 두 필드가 일치하는 레코드를 검색

  📌  단 일괄 비교가 성립하려면 비교 대상과 서브쿼리의 컬럼 개수는 반드시 일치해야 함.

💡  서브쿼리가 다중열이면서 다중행인 결과셋을 리턴하면 복수 레코드의 복수 필드를 한꺼번에 비교 가능

  📌  부서별 최고 월급자의 목록을 조사. 여러 개의 부서가 있고 각 부서명과 최고 월급을 비교

1. DELETE

👩🏻‍💻 레코드를 삭제할 때 사용한다.

👩🏻‍💻 삭제는 항상 레코드 단위로 수행하므로 필드에 대한 지정은 없음.

👩🏻‍💻 삭제 동작은 특정 조건에 맞는 레코드를 찾아 제거하는 경우가 대부분이어서 WHERE 절이 항상 뒤따라 온다.

2. 삭제 안전 장치

👩🏻‍💻  삭제시 문제가 되는 부분은  1) 조건절을 빼거나 2) 잘못된 조건을 사용하는 경우

👩🏻‍💻  조회만 하는 SELECT 명령은 테이블을 변경하지 않아 실수해도 조건을 바꿔 다시 조회하면 됨.

       INSERT 명령은 테이블을 변경하지만 혹시라도 잘못 삽입했으면 다시 지우면 되어서 그다지 위험하지 않음

       하지만 잘못 내린 DELETE 명령은 즉시처리해 버림.

👩🏻‍💻  DELETE문은 특정 레코드를 삭제하기 위해 WHERE 절과 함께 사용하지만 실수로 WHERE 절을 생략해 버리면 모든 데이터가 삭제됨

📌  만일 월급이 300 초과인 직원을 삭제한다면 다음 절차대로 작업

3. TRUNCATE

👩🏻‍💻  테이블 초기화 할 때 쓰는 명령어

👩🏻‍💻  DELETE 명령은 수십만건의 레코드를 일일이 지우게 되면 서버에 부하가 생긴다.

       ➡️ 이 경우 TRUNCATE 명령을 사용하는 것이 효율적

       ➡️ 물리적인 기억 장소를 깨끗이 비우고 임시 영역에 로그도 남기지 않아 훨씬 빠르다