The Programmer’s Guide to SQL

사용자 삽입 이미지

Chapter 1: Understanding SQL and Relational Databases
Chapter 2: Retrieving Data with SQL
Chapter 3: Modifying Data
Chapter 4: Summarizing and Grouping Data
Chapter 5: Performing Calculations and using Functions
Chapter 6: Combining SQL Queries
Chapter 7: Querying Multiple Tables
Chapter 8: Hiding Complex SQL with Views
Chapter 10: Transactions
Chapter 11: Users and Security
Chapter 12: Working with Database Objects

프로시져(9장), 트리거(13장), 예제(14, 15장)은 빼고 오라클 중심으로 봤더니 빨리 볼 수 있었습니다.

SQL 기본 서적으로 매우 좋은 것 같습니다. 쉽게 잘 설명해줘서 머리에 잘 들어옵니다. SQL문을 그냥 외우는 거에 비하면 훨씬 재밌습니다.

그리고 각종 DB 벤더들의 RDBMS 특징들도 다루고 있어서 오라클에서는 이런게 되는데 MySQL에서는 이런게 안되고 이렇게 해야 한다는 등의 내용이 들어있습니다. 다만 2003년 책이라서 그동안 바뀐 내용들이 엄청 많을 것 같습니다. 개정판이 나오면 좋겠습니다. PostgreSQL이나 HSQL은 다루고 있질 않아서 조금 아쉽습니다.

다음은 읽으면서 생각했던 것들입니다.

  • 트랜잭션 상태 바꾸기: SET TRANSACTION …
  • 서브쿼리에서 ALL, ANY 사용하기: 뭐가 햇갈렸더라..
  • 서브쿼리와 JOIN의 성능차이: 서브쿼리는 외부 쿼리의 레코드 갯수마다 서브쿼리를 수행해야 하기 때문에,
    외부 쿼리 결과 레코드의 갯수와 서브쿼리 소요시간에 비례하여 전체 소요 시간이 증가할 것이다. JOIN은 데카르트 곱을 사용하여
    관계를 맺는 테이블들의 레코드 갯수에 비례하여 소요되는 시간이 증가할 것이다. 단순비교를 할 수는 없겠다. 실제 쿼리와 레코드
    갯수를 가지고 비교해봐야겠다.
  • INNER JOIN과 OUTER JOIN 성능차이: 거의 차이가 없을 것이다. 부가작업(대응하는 필드가 없는
    경우 NULL로 채워주는 일)이 필요한 OUTER JOIN의 경우 조금 더 오래 걸릴 수도 있겠지만, 어차피 둘 다 데카르트 곱
    연산은 수행하기 때문이다.
  • 뷰 만들 때 옵셥주기: CREATE VIEW AS 쿼리 옵션. 여기서 옵션을 어떻게 주는건지는 책에 안
    나왔네..나중에 찾아봅세. 그리고 VIEW를 조작하려면 지켜야 하는 조건들이 있는데, 거기서 벗어나더라도 옵션으로 수정 가능하게
    하면 테이블에 영향을 줄 수 있는건가? 그러진 않을 것 같다.

Chapter 12: Working with Database Objects

참조 : The Programmer’s Guide to SQL

데이터베이스 만들기
    – CREATE DATABASE db이름
    – 오라클은 보통 DB를 한 개만 사용하고 스키마를 이용해서 관련있는 테이블들과 다른 객체를 그룹핑한다.
데이터베이스 제거
    – 오라클에서는DROP DATABASE가 아니라 CREATE DATABASE 로 제거

테이블 만들기
    – CREATE TABLE <table name>
   (<column name> <column data type> [<column constraints>]);
테이블 제거
    – DROP TABLE <table name>
    – FOREIGN KEY 제약으로 인해 참조 되고 있는 테이블이면 제거가 되지 않는다. 제거하기 전에 이 제약부터 제거해야 한다.

컬럼에 기본값 주기
    – DEFAULT <기본값>
    – 컬럼 기본값을 함수나 키워드를 사용할 수 있다.
    GETDATE() = SYSDATE = CURRENT_DATE
주키 설정하기
    – 주키 이름 설정하고 싶을 때

  CREATE TABLE Friend (
   Name    VARCHAR(50) NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’ NOT NULL,
   CONSTRAINT MyPrimaryKey PRIMARY KEY (Name));

    – 주키 이름에 관심없을 때

   CREATE TABLE Friend (
   Name    VARCHAR(50) NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’ NOT NULL,
   PRIMARY KEY (Name));

    – 이것을 좀 더 간단하게

  CREATE TABLE Friend (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’ NOT NULL);

    – 주키 제약을 설정하면 자동으로 고유한 인덱스가 생성된다.
UNIQUE 설정
    – 오라클에서는 기본으로 UNIQUE 속성의 컬럼에 다수의 NULL 넣을 수 있슴.
컬럼 제약 설정
    – CHECK 사용하기

    CREATE TABLE Friend (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’,
   Age     INT,
   CONSTRAINT CheckAge CHECK (Age BETWEEN 10 and 100));

    – 제약 사항의 이름이 필요 없다면

    CREATE TABLE Friend (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’,
   Age     INT CHECK (Age BETWEEN 10 and 100));

테이블 복사 하기
    – CREATE TABLE My_Friends AS SELECT * FROM Friend;
    – 단 제약 사항들(주키나 유니크 같은 것들)은 복사 되지 않는다. 그렇기 때문에 테이블을 만들 때는 기본 구조만 정의하고 Alter Table로 실행할 스크립트를 만들어 두는 것이 유용하다.
    – 데이터는 빼고 구조만 복사하고 싶다면 WHERE 절에 false가 되도록 설정해준다.
    CREATE TABLE My_Friends AS SELECT * FROM Friend
    WHERE 1=0;

테이블 변경하기
    – 컬럼 추가하기 : ALTER TABLE Friend ADD (EMail VARCHAR(25), ICQ VARCHAR(15));

        – NOT NULL 인 컬럼을 추가하려면, 테이블에 데이터가 들어있지 않아야 한다.
    – 컬럼 없애기 : ALTER TABLE Friend DROP COLUMN PhoneNo;

    – 제약 사항 삭제 : ALTER TABLE Friend DROP CONSTRAINT unq_name;
    – 제약 사항 추가 : ALTER TABLE Friend ADD CONSTRAINT PK_FriendName PRIMARY KEY (Name);

시퀀스 사용하기
    – CREATE SEQUENCE FriendIDSeq INCREMENT BY 1 START WITH 1000;
    – 테이블에서 사용하기
    INSERT INTO Friend (FriendID, Name, PhoneNo)
             VALUES (FriendIDSeq.NextVal, ‘Mike’, ‘123’);
    – 시퀀즈 제거하기 : DROP SEQUENCE FriendIDSeq;
    – 현재 시퀀스 값 가져오기 : SELECT FriendIDSeq.CurrVal FROM DUAL;

자동 증가 사용하기
    – 오라클에서는 트리거를 이용해야 함.

    CREATE OR REPLACE TRIGGER AUTONUMBER
    BEFORE INSERT ON Friend
    FOR EACH ROW
    BEGIN
       SELECT FriendIDSeq .NEXTVAL
       INTO :NEW.FriendID FROM DUAL;
    END;
    /

    – 시퀀스에서 값을 가져온 다음 새로운 row 생성 될 때 주키에 값을 집어넣는 트리거인듯.

인덱스 사용하기
    – 인덱스를 사용하면 SELECT 문의 속도는 증가하지만, INSERT, DELELTE, UPDATE의 속도는 느려진다.
    – 조회가 자주 발생하는 테이블의 경우 인덱스를 사용하는 것이 단점에 비해 눈에띄게 좋다.
    – 따라서 WHERE 나 ORDER BY 절에서 자주 사용하는 컬럼의 인덱스를 만들어 두는 것이 좋다.
    – 제약사항(주키나 유니크) 때문에 자동으로 생성된 인덱스는 제약사항이 제거 되면 같이 제거 된다.
    – 인덱스에는 유니크 인덱스와 논 유니크 인덱스 두 종류가 있다.
    – 인덱스 만들기 : CREATE UNIQUE INDEX NameIndex ON Friend (Name);
    – 유니크 인덱스를 사용하는 컬럼에 중복되는 값을 넣을 수 없다.
    – 텍스트에 인덱스는 단어의 왼쪽 글자부터 오른쪽 글자순으로 찾는다. 따라서 ‘%기선’ 과 같은 검색어는 데이블의 모든 데이터를 찾아보게 한다.
    – 인덱스 삭제하기 : DROP INDEX NameIndex;

참조 무결성
    – 외례키 제약 설정하기

    CREATE TABLE Friend (FriendID INT PRIMARY KEY NOT NULL,
                      Name VARCHAR(50));

    CREATE TABLE Phone (
       PhoneID INT PRIMARY KEY NOT NULL,
       FriendID INT,
       PhoneNo VARCHAR(20),
       CONSTRAINT FID_FK FOREIGN KEY(FriendID)
          REFERENCES Friend(FriendID));

Chapter 11: Users and Security

참조 : The Programmer’s Guide to SQL

인증 : 이부분에 대한 SQL-99 표준은 없다. 따라서 DBMS마다 각기 다른 방법을 사용하지만 대부분 비슷하다.
권한 : SLQ-99 표준에 두 개의 키워드를 정의하고 있다. GRANT, REVOKE

사용자 계정 만들기
    CREATE USER username IDENTIFIED BY password;
ROLE 종류
    CONNECTION : DB에 연결 가능
    RESOURCE
    DBA
ROLE 또는 권한 주기
    GRANT 권한 TO username
ROLE 또는 권한 제거
    REVOKE 권한 FROM username
사용자 정의 ROLE 만들기
    CREATE ROLE rolename
ROLE 삭제하기
     DROP ROLE rolename
Statement 단위로 권한 부여하기
    GRANT <privilege type> ON <resource> TO <username>
Statement 단위로 권한 취소하기
    – REVOKE <privilege type> ON <resource> FROM <username>
    – Role을 사용하여 부여된 여러 권한 중에서 특정 권한만 취소할 경우에 유용하게 사용할 수 있음.
    – RESOURCE로 인해 부여받는 권한 중에 테이블이 디스크의 공간을 무한대로 사용할 수 있는(UNLIMITED TABLESPACE)데, 이 권한을 취소할 수 있음.