SQL_DAY4

MySQL Data Type (p237)

SQL문은 크게 DDL, DML, DCL로 총 세 가지로 나뉜다.

1. DDL
   Data Definition Language, 테이블 구조 정의에 사용
   CREATE, ALTER, DROP 등의 명령어가 여기에 해당한다.

2. DML
   Data Manipulating Language, 테이블 데이터 조작에 사용
   INSERT, DELETE, UPDATE 등의 명령어가 여기에 해당하며, SELECT도 DML에 속한다고 보는 관점도 존재한다.

3. DCL
   Data Control Language, 테이블 데이터 제어에 사용
   Transaction 처리와 관련된 Language이다. COMMIT, ROLLBACK, SAVEPOINT, SET AUTOCOMMIT, GRANT 등의 명령어가 여기에 해당한다.

• 숫자 데이터 형식
  TINYINT: -128 ~ 127, 1바이트
  SMALLINT: -32788 ~ 32767, 2바이트
  INT: 약 -21억 ~ 21억, 4바이트
  FLOAT: -3.40E+38 ~ -1.17E-38, 4바이트
  DECIMAL(m, [d]): m자릿수 숫자에서 소숫점 이하 d자릿수 까지를 갖는 숫자형, FLOAT 보다 자주 쓰인다. 5~17바이트,
  ex) 163.4는 DECIMAL(4, 1), 132.12는 DECIMAL(5, 2).

• 문자 데이터 형식
  CHAR: 고정 문자형, 1~255바이트
  VARCHAR: 가변 문자형, 1~65535 바이트

  CHAR와 VARCHAR 모두 UTF-8을 사용하기 떄문에, 한글 영어 모두 1글자에 1바이트이다. 물론 내부적으로는 다르지만, 그거까지 신경쓸 필요는 없다.

• 날짜 데이터 형식
  DATE: 날짜, 3바이트
  DATETIME: 날짜 + 시간, 8바이트
  YEAR: 연도, 1바이트

명령어 예시

• CREATE / INSERT / UPDATE

CREATE TABLE custom(
	num SMALLINT AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL,
    addr VARCHAR(50) NOT NULL,
    birthday DATE,
    PRIMARY KEY(num));

INSERT INTO custom(name, addr, birthday) VALUES ('아이유', '제주시', '1989-02-22');
INSERT INTO custom(name, addr, birthday) VALUES ('홍길동', '서울시', '1985-03-31');
INSERT INTO custom(name, addr, birthday) VALUES ('김갑동', '수원시', '1984-04-21');

UPDATE custom SET addr='방배동' WHERE num=1;
UPDATE custom SET addr='여의도', birthday='1985-01-13' WHERE num=3;

PK는 수정의 대상이 아니다. UPDATE로 수정하지 말자. PK를 수정하는 것은 레코드 자체가 없어지고, 새로운 것을 만드는 개념이기 때문에, 관계가 다 깨지게 된다.

• ALTER

ALTER TABLE custom CHANGE addr address VARCHAR(30); -- 컬럼 명 변경

• DELETE / TRUNCATE / DROP

DELETE FROM custom; -- 전체 행 삭제
DELETE FROM custom WHERE num=1; -- 조건 맞는 행 삭제

위 쿼리를 잘못 쓰면 진짜 큰일날 수 있다. 그래서 현업에서는 SET AUTOCOMMIT = 0을 해주고 작업하는 것이 좋다. 물론 없어져도 DELETE로 실행된 쿼리는 ROLLBACK이 가능하긴 하지만, 조심해서 나쁠 것 없다.

TRUNCATE TABLE custom;

모든 데이터를 삭제하지만, ROLLBACK이 불가능하다. 내부적으로는 테이블을 DROP하고 다시 CREATE하는 방식으로 진행된다.

DROP TABLE custom; -- 테이블 객체 전체 삭제, 구조도 남지 않는다.

CT(A)S (Create Table (As) Select)

대용량의 샘플 데이터를 로드하고, 테이블 생성과 동시에 데이터를 INSERT 시킬 수 있다. 이것은 subquery로 진행된다.

-- 1)
CREATE TABLE emp1 AS (SELECT * FROM emp);

-- 2)
CREATE TABLE emp2 SELECT * FROM emp;

-- 3)
CREATE TABLE emp3 SELECT empno, ename, sal, deptno FROM emp;

-- 4)
CREATE TABLE emp4 SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10;

Primary Key 속성은 복사되지 않음에 주의하자.

기본 키(PK), 외래 키(FK) 제약조건 추가하기

ALTER TABLE employee ADD CONSTRAINT employee_empno_pk PRIMARY KEY(empno);
ALTER TABLE employee ADD CONSTRAINT employee_deptno_fk FOREIGN KEY(deptno) REFERENCES department(deptno);

DELETE FROM department WHERE deptno=10; 
-- 자식 ROW가 있기 때문에, 실행되지 않는다.
-- deptno=10인 사원을 모두 지우거나, 모두 NULL로 바꾸면 실행이 가능하다. 하지만, 일일이 하기 힘들다.

CASCADE 추가하기

ALTER TABLE employee ADD CONSTRAINT employee_deptno_fk 
	FOREIGN KEY(deptno) REFERENCES department(deptno) ON DELETE CASCADE;
    -- 이렇게 하면, 부모를 삭제 시도할 때, 자식이 먼저 다 지워지고 부모도 지울 수 있다.
    
DELETE FROM department WHERE deptno=10; -- 작동됨

Transaction

Transaction의 처리는 보통 프로그램이 한다. 서버 관리자가 하게 될 것이다.
SAVEPOINT는 기능별로 세이브포인트를 찍어서 그쪽으로 ROLLBACK이 가능하게 한다.

원자성(Atomicity), 일관성(Consistency), 독립성(Isolation), 영속성(Durability)가 보장이 되어야 한다.

SET AUTOCOMMIT = 0;

-- client 1
UPDATE emp SET sal=1000 WHERE ename='SMITH'; -- 실행

-- client 2
UPDATE emp SET sal=1200 WHERE ename='SMITH'; -- 실행 안됨

COMMIT; -- 하는 순간 client 2의 쿼리문 실행됨

데이터베이스 객체

Table, View, Sequence, Index 총 4가지가 있다.

VIEW

사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상테이블.
권한이 있는 상태로 저장장치 내에 물리적으로 존재하지 않지만, 사용자에게는 있는 것으로 간주된다.

사용자의 입장에서는 테이블과 거의 동일한 개체로 여겨진다.
VIEW를 생성하고 나서는 그냥 테이블처럼 생각하고 접근하면 원래의 테이블을 접근한 것과 동일한 결과를 얻을 수 있다.

VIEW는 기본적으로 읽기 전용이지만, 수정도 가능은 하다. 그러나 권장하진 않는다.

• VIEW를 사용할 때의 장점
  1. 보안에 도움이 된다.
  2. 복잡한 쿼리를 단순화 시켜줄 수 있다.

CREATE VIEW empView AS
	(SELECT empno, ename, job FROM emp);
    
CREATE OR REPLACE VIEW eview AS
	(SELECT deptno, SUM(sal) AS ssal FROM emp GROUP BY deptno ORDER BY ssal);
    
SELECT * FROM empView;
DELETE FROM empView WHERE empno=7369; -- 정상작동 한다.

INDEX

색인. 대용량의 데이터를 다룬다고 가정할 때, 특정한 값을 매번 찾으려면 시간이 엄청 오래 걸린다. 하지만, 색인을 미리 해 둔다면 불과 몇 초만에 검색 완료를 할 수 있다.

단점은, 미리 검색해둘 데이터가 데이터베이스에서 계속적으로 많이 나타난다면, 오히려 더 비효율적일 수도 있다는 것이다.
또, 데이터 변경 작업(INSERT, UPDATE, DELETE)을 자주 사용할수록, 성능이 매우 나빠진다.

모델링

가장 전통적인 모델링 기법: 폭포수(Waterfall) 모델

• 마일스톤

1. 프로젝트 착수단계: 아키텍쳐 구조, 사용할 기술스택, 기간 등..
2. 분석: 사용자의 입장에서 시스템 활용도 이해(개발 관련 X) -> 요구사항 명세서(굉장히 중요하다. 중심을 잡은 누군가가 던져주는게 제일 낫다.) -> Use Case Diagram
3. 설계: 요구사항 명세서 기반 설계, 방사형 진행(SRS) -> UI, UX에 대한 고민, ERWin(DB 모델링 - 개념적 설계, 논리적 설계, 물리적 설계), Business Logic Template, Vo 설계 등 풀스택이 다나온다.
   3-1. 개념적 설계: Schema 설계, Entity 추출 - 주로 업무 분석 단계
   3-2. 논리적 설계: PK 지정, 일반 속성 부여, 정규화 진행(권장사항: 3정규화를 넘지 말아라) - 업무 분석의 후반부, 시스템 설계의 전반부
   3-3. 물리적 설계: 실제 테이블 생성 - 시스템 설계의 후반부
4. 구현
5. 단위테스트

L자형 테이블은 만들면 빈공간이 많아 자원이 낭비된다. 이럴 바에야 테이블을 두 개로 나누는게 좋다.