2 SQL 기본 및 활용 - 1장 SQL 기본

1. 관계형 데이터 베이스 개요

데이터베이스

• 특정 기업이나 조직 또는 개인이 필요에 의해(ex: 부가가치가 발생하는) 데이터를 일정한 형태로 저장해 놓은 것

DBMS (데이터베이스 관리 시스템)

• 효율적인 데이터의 관리뿐만 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고, 필요시 필요한 데이터를 복구하기 위한 강력한 기능의 소프트웨어

SQL (Structured Query Language)

• 관계형 데이터베이스에서 데이터 정의, 데이터 조작, 데이터 제어를 하기 위해 사용하는 언어

 

 

https://yganalyst.github.io/assets/images/sql/sqld/chapter2_1_1.png

TABLE

• 데이터를 저장하는 객체(Object)로서 관계형 데이터베이스의 기본 단위
• 가로 = 행 = 로우 = 튜플 = 인스턴스
• 세로 = 열 = 컬럼 = 속성?
• 테이블 관계 용어
  • 정규화 : 데이터의 정합성 확보와 데이터 입력/수정/삭제 시 발생할 수 있는 이상현상(Anomaly)을 방지하기 위해 중복제거
  • 기본키 : 테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 칼럼
  • 외부키 : 다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 칼럼
• 데이터 언어
  • 비절차적 데이터 조작이(DML) : 사용자가 무슨(What) 데이터를 원하는 지만을 명세함
  • 절차적 데이터 조작어 : 어떻게(How) 데이터를 접근해야 하는지를 명세함
    • PL/SQL(Oracle), T-SQL (SQL Server) 등이 있음

2. DDL (Data Definition Language)

데이터 유형

 

CREATE TABLE (테이블 생성)

CREATE　TABLE　테이블이름 (
칼럼명1 DATATYPE [DEFAULT 형식],
칼럼명2 DATATYPE [DEFAULT 형식],
칼럼명2 DATATYPE [DEFAULT 형식]
) ;

CREATE TABLE PLAYER (
  PLAYER_ID CHAR(7) NOT NULL,           # NOT NULL은 제약조건 (column level)
  PLAYER_NAME VARCHAR(20) NOT NULL,
  TEAM_ID CHAR(3) NOT NULL,
  E_PLAYER_NAME VARCHAR(40),
  NICKNAME VARCHAR(30),
  JOIN_YYYY CHAR(4),
  POSITION VARCHAR(10),
  BACK_NO TINYINT,
  NATION VARCHAR(20),
  BIRTH_DATE DATE,
  SOLAR CHAR(1),
  HEIGHT SMALLINT,
  WEIGHT SMALLINT,
  CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID),                        # 제약조건 (table level)
  CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID)  # 제약조건 (table level)
) ;

• 테이블 생성 시에 주의해야 할 몇 가지 규칙
  • 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.
  • 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.
  • 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.
  • 테이블 이름을 지정하고 각 칼럼들은 괄호 ( )로 묶어 지정한다.
  • 각 칼럼들은 콤마, 로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ;으로 끝난다.
  • 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.(데이터 표준화 관점)
  • 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
  • 테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.
  • 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다.
  • A-Z, a-z, 0-9, _, $, # 문자만 허용된다.
  • 테이블 생성 시 대/소문자 구분은 하지 않는다. 기본적으로 테이블이나 칼럼명은 대문자로 만들어진다.
  • DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.
  • 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.
  • 칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다.
  • 칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.

제약조건 (아래 표가, 칼럼 정의할때 쓸수 있는 것들, NOT NULL 처럼 PRIMARY KEY 도 쓸 수 있음)

• 데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 제약

 

• DEFAULT : NULL값일 경우 NULL 대신 입력할 값을 지정하는 것임
• 생성된 테이블 구조 확인
  • DESCRIBE 테이블명, DESC 테이블명

ALTER TABLE (테이블 구조 변경)

• 컬럼 추가 (ADD)
• ALTER TABLE 테이블명 ADD 추가할 칼럼명 데이터 유형; ALTER TABLE PLAYER ADD ADDRESS VARCHAR(80);
• 컬럼 삭제 (DROP COLUMN)
  • 데이터가 있거나 없거나 모두 삭제 가능
  • 한 번에 하나의 칼럼만 삭제 가능
  • 칼럼 삭제 후 최소 하나 이상의 칼럼이 테이블에 존재해야 함
  • 한 번 삭제된 칼럼은 복구가 불가능

  ALTER TABLE 테이블명
  DROP COLUMN 삭제할 칼럼명;
      
  ALTER TABLE PLAYER
  DROP COLUMN ADDRESS;
  

• 컬럼 수정 (MODIFY) - Oracle
  • 칼럼의 데이터 유형, 디폴트(DEFAULT) 값, NOT NULL 제약조건에 대한 변경

  # 오라클표기 
  ALTER TABLE 테이블명
  MODIFY (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL],
  칼럼명2 데이터 유형 …);
      
  ALTER TABLE TEAM_TEMP
  MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);
  

• 칼럼 수정 (ALTER COLUMN) - SQL Server
  • SQL-Server에서는 여러 칼럼을 동시에 수정하는 구문 지원 하지 않음 (따로 해야 함)
  • SQL-Server에서는 괄호를 사용하지 않음
  • 주의점
    • 해당 칼럼의 크기를 늘릴 수는 있지만 줄이지는 못함 (기존 데이터 훼손 가능)
    • 해당 칼럼이 NULL 값만을 가지고 있으면 데이터 유형을 변경할 수 있다.
    • 해당 칼럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.
    • 해당 칼럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.
    • NOT NULL 제약조건이 있었는데, ALTER 할 때 넣지 않으면 NULL로 변경됨
• ALTER TABLE 기관분류 ALTER COLUMN 분류명 VARCHAR(30) NOT NULL; ALTER TABLE 기관분류 ALTER COLUMN 등록일자 DATE NOT NULL;
• 칼럼명 변경 (RENAME COLUMN)
• ALTER TABLE 테이블명 RENAME COLUMN 변경해야 할 칼럼명 TO 새로운 칼럼명; ALTER TABLE PLAYER RENAME COLUMN PLAYER_ID TO TEMP_ID;
• 제약조건 삭제 (DROP CONSTRAINT)
• ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명; ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;
• 제약조건 추가 (ADD CONSTRAINT)
• ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명); ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

RENAME TABLE (테이블 이름 변경)

RENAME 변경전 테이블명 TO 변경후 테이블명;

RENAME TEAM TO TEAM_BACKUP;

DROP TABLE (테이블 삭제)

  DROP TABLE 테이블명 [CASCADE CONSTRAINT];
  
  DROP TABLE PLAYER;

• CASCADE CONSTRAINT 옵션 : 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제

TRUNCATE TABLE (테이블의 데이터만 삭제, 테이블 구조는 재사용 가능)

  TRUNCATE TABLE PLAYER;

3. DML (Data Manipulation Language)

• INSERT INTO ~ VALUES : 테이블에 데이터 입력
  • Column과 1:1로 매핑되어야 함
  • 두 번째 방법의 경우 모든 칼럼 값을 입력해야 하고, 안 할 거면 '' 또는 NULL 입력\
  • 칼럼 데이터 유형이 CHAR, VARCHER2인 경우 ''를 입력해야 하지만, 숫자일 경우 자동 인식
  • TABLE 칼럼 중 NOT NULL 인 컬럼 있으면 무조건 함께 INSERT 해야 함. (명시를 안 하면 NULL 이 들어가야 하므로)
• # 1번째 방법 INSERT INTO 테이블명 (COLUMN_LIST) VALUES (COLUMN_LIST에 넣을 VALUE_LIST); INSERT INTO PLAYER (PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO) VALUES ('2002007', '박지성', 'K07', 'MF', 178, 73, 7); # 2번째 방법 INSERT INTO 테이블명 VALUES (전체 COLUMN에 넣을 VALUE_LIST); INSERT INTO PLAYER VALUES ('2002010', '이청용', 'K07', '', 'BlueDragon', '2002', 'MF', '17', NULL, NULL, '1',180,69);
• UPDATE ~ SET (입력한 정보를 수정)
• UPDATE 테이블명 SET 수정되어야 할 칼럼명 = 수정되기를 원하는 새로운 값; UPDATE PLAYER SET BACK_NO = 99; # 모든 BACK_NO를 99로 수정
• DELETE (FROM) (데이터 삭제)
  • DDL은 AUTO COMMIT이지만, DML은 COMMIT 명령어를 입력하여 TRANSACTION을 종료해야 실제 테이블에 반영됨
  • Oracle은 AUTOCOMMIT이지만, SQL-Server는 따로 COMMIT 해야 함
• DELETE [FROM] 삭제를 원하는 정보가 들어있는 테이블명; DELETE FROM PLAYER; # WHERE 절이 없으므로, PLAYER 테이블 전체 삭제
• SELECT ~ FROM (데이터 조회)
  • ALL : Default 옵션 중복된 데이터가 있어도 모두 출력
  • DISTINCT : 중복된 데이터가 있는 경우 1건으로 처리해서 출력
  • 와일드카드(Wild card) `` : 모든
  • SELECT * FROM 테이블명; # 모든 칼럼 출력
  • ALIAS : 일종의 별명, 컬럼 레이블을 변경하여 출력 (보기 좋게)
    • ALIAS에 공백이 있으면 ""를 사용해야 함

    SELECT PLAYER_NAME AS 선수명, POSITION AS 위치, HEIGHT AS 키, WEIGHT AS 몸무게
    FROM PLAYER; 
    
     # AS는 생략가능
        
    SELECT PLAYER_NAME "선수 이름", POSITION "그라운드 포지션", HEIGHT "키", WEIGHT "몸무게"
    FROM PLAYER;
    

• SELECT [ALL/DISTINCT] 보고 싶은 칼럼명, 보고 싶은 칼럼명,... FROM 해당 칼럼들이 있는 테이블명; SELECT PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO FROM PLAYER; SELECT DISTINCT POSITION FROM PLAYER; # UNIQUE 한 값만 출력

산술연산자

  SELECT PLAYER_NAME 이름, HEIGHT - WEIGHT "키-몸무게"
  FROM PLAYER;

• 사칙연산 등을 수행해서 출력함
• 이 경우 Label name이 길어지기 때문에 ALIAS로 별칭을 사용하는 것을 권장

합성연산자

• 문자와 문자를 연결하는 경우 2개의 수직 바(||)에 의해 이루어진다. (Oracle)
• 문자와 문자를 연결하는 경우 + 표시에 의해 이루어진다. (SQL Server)
• 두 벤더 모두 공통적으로 CONCAT(string1, string2) 함수를 사용할 수 있다.
• 칼럼과 문자 또는 다른 칼럼과 연결시킨다.
• 문자 표현식의 결과에 의해 새로운 칼럼을 생성한다.
• SELECT PLAYER_NAME || '선수, ' || HEIGHT || 'cm, ' || WEIGHT || 'kg' 체격정보 FROM PLAYER; # 예시) 예) 박지성 선수, 176 cm, 70 kg

추가로 정리 (https://docu94.tistory.com/141)

인덱스 생성하는 방법

  CREATE INDEX 생성될 인덱스명 ON 스키마(속성명);

외래키에 대한 설명

• 테이블 생성 시 설정할 수 있다.
• 외래키 값은 NULL 값을 가질 수 있다.
• 한 테이블에 여러 개가 존재할 수 있다.
• 외래키 값은 참조 무결성 제약을 받을 수 있다.

참조 무결성 규정

• 예시
• FOREIGN KEY (외래키가 될 칼럼) REFERENCES 참조할 스키마(속성명) ON DELETE CASCADE;
• DELETE(MODIFY) Action
  • CASCADE : Master 삭제 시, Child 같이 삭제
  • SET NULL : Master 삭제 시, Child 해당 필드 Null
  • SET DEFAULT : Master 삭제 시, Child 해당 필드 Default 값으로 설정
  • RESTRICT : Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용
  • No Action : 참조 무결성을 위반하는 삭제 / 수정 액션을 취하지 않음
• INSERT Action
  • AUTOMATIC : Master 테이블에 PK가 없는 경우, Master 테이블 PK 생성 후 Child 입력
  • SET NULL : Master 테이블에 PK가 없는 경우, Child 외부키를 Null값으로 처리
  • SET DEFAULT : Master 테이블에 PK가 없는 경우, Child 외부키를 지정된 기본값으로 입력
  • DEPENDENT : Master 테이블에 PK가 존재할 때만 Child 입력 허용
  • No Action : 참조 무결성을 위반하는 입력 액션을 취하지 않음
• DELETE vs TRUNCTUATE vs DROP
  • TRUNCTUATE과 DROP 은 DDL로, 로그를 남기지 않지만(AUTO COMMIT), DELETE는 로그를 남김(COMMIT 해야 함)
  • DELETE : 테이블의 데이터를 모두 삭제함, 디스크 사용량을 초기화(없앰) 하지 않음
  • DROP : 테이블의 데이터를 모두 삭제함, 디스크 사용량도 초기화(없앰) 함, 테이블의 스키마 정의 삭제함
  • TRUNCTUATE : 테이블의 데이터를 모두 삭제함, 디스크 사용량도 초기화(없앰) 함, 테이블의 스키마 정의 삭제하지 않음

 

4. TCL (Transaction Control Language)

트랜잭션

• 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작, 데이터베이스의 논리적 연산단위
• 최소 단위이므로 전부 적용 or 전부 취소임 ⇒ All or Nothing (ex. 계좌이체)
• COMMIT : 올바르게 반영된 데이터를 데이터베이스에 반영시키는 것 (ROLLBACK 불가)
• ROLLBACK : 트랜잭션 시작 이전의 상태로 되돌리는 것, COMMIT 되지 않은 모든 트랜잭션을 롤백함
• SAVEPOINT : 저장 지점

트랜잭션의 특성

 

트랜잭션에 대한 격리성이 낮은 경우 발생할 수 있는 문제 유형

• Dirty Read : 다른 트랜잭션에 의해 수정되었지만, 아직 커밋되지 않은 데이터를 읽는 것
• Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상
• Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 없던 유령 레코드가 두 번째 쿼리에서 나타나는 현상

COMMIT

  DELETE FROM PLAYER;
  480개의 행이 삭제되었다.
  
  COMMIT;
  커밋이 완료되었다.

• SQL server는 AUTO COMMIT이며, 오류가 발생하면 자동 ROLLBACK

ROLLBACK

  DELETE FROM PLAYER;
  480개의 행이 삭제되었다.
  
  ROLLBACK;
  롤백이 완료되었다.

SAVEPOINT

  SAVEPOINT SVPT1;
  저장점이 생성되었다.
  
  INSERT INTO PLAYER
  (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
  VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1);
  1개의 행이 만들어졌다.
  
  ROLLBACK TO SVPT1;
  롤백이 완료되었다.

ROLLBACK과 SAVEPOINT 원리

 

• 그냥 ROLLBACK은 모든 변경사항 취소 (마지막 COMMIT으로 돌아감)
• ROLLBACK A → ROLLBACK B (미래로 가기 불가)

정리

• CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 전후 시점에 자동으로 커밋된다.
• 부연하면, DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋된다.
• 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋된다.
• 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.
• SQL Server의 트랜잭션은 DBMS가 트랜잭션을 컨트롤하는 방식인 AUTO COMMIT이 기본 방식이다. 다음의 경우는 Oracle과 같이 자동으로 트랜잭션이 종료된다.
• 애플리케이션의 이상 종료로 데이터베이스(인스턴스)와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.

Reference

[데이터 전문가 포럼] SQL 개발자 스터디 교재

https://yurimac.tistory.com/40