Whiteship's Note


Oracle에서 Tablespace 만들기

DB/SQL : 2008.01.09 10:10


참조한 글
Oracle Tablespace 생성 - Fedora Release 7
Oracle Tablespace에 계정추가 및 관리
Tablespace Information

테이블 스페이스 만들기
create tablespace [테이블 스페이스 이름]
datafile '[데이타 파일명]' size 400k
Autoextend on next 400k maxsize 10m;

사용자 생성하면서 테이블 스페이스 지정해주기
create user [사용자 이름] IDENTIFIED BY [비번]
DEFAULT TABLESPACE [테이블 스페이스 이름]
TEMPORARY TABLESPACE temp;

테이블 스페이스 확인
select *
from dba_data_files;

사용자의 테이블 스페이스 확인하기
select     USERNAME,
    CREATED,
    PROFILE,
    DEFAULT_TABLESPACE,
    TEMPORARY_TABLESPACE
from     dba_users
order     by USERNAME

테이블 스페이스 삭제
drop tablespace [테이블 스페이스 이름]

사용자 삭제
drop user [사용자 이름]



top

Write a comment.


Chapter 14: Case Study: Building a Product Catalog

DB/SQL : 2008.01.08 16:23


참조 : The Programmer's Guide to SQL

오라클에서 테이블 만들기

CREATE TABLE Category (
CategoryID INT NOT NULL PRIMARY KEY,
DepartmentID INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Description VARCHAR (200) NULL,
FOREIGN KEY (DepartmentID) REFERENCES Department (DepartmentID));

CREATE SEQUENCE CategoryIDSeq;

CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
BEFORE INSERT ON Category
FOR EACH ROW
BEGIN
   SELECT CategoryIDSeq.NEXTVAL
   INTO :NEW.CategoryID FROM DUAL;
END;
/
- 시퀀스 만들고, 트리거 만들어서 자동 증가값처럼 사용하기.

CREATE TABLE ProductCategory (
ProductID INT NOT NULL,
CategoryID INT NOT NULL,
PRIMARY KEY (ProductID, CategoryID),
FOREIGN KEY (ProductID) REFERENCES Product (ProductID),
FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID)
);
- Junction 테이블.

데이터 가져오기

SELECT Name, Price FROM
      (SELECT Name, Price
       FROM Product
       ORDER BY Price DESC, Name ASC)
WHERE ROWNUM<=5;
- 상위 다섯개의 데이터 가져오기.
- id를 사용하지 않는다. id가 꼭 순서대로 매겨진다는 보장이 없으니까..

SELECT ProductID, Name FROM
(
SELECT RANK() OVER (ORDER BY ProductID) As Ranking, ProductID, Name
FROM Product
ORDER BY PRODUCTID
)
WHERE Ranking BETWEEN 6 AND 8;
- 특정 범위의 값 가져오기.

SELECT C.Name as "Category Name", P.Name as "Product Name"
FROM Product P
INNER JOIN ProductCategory PC ON P.ProductID = PC.ProductID
INNER JOIN Category C ON PC.CategoryID = C.CategoryID
ORDER BY C.Name, P.Name;
=> WHERE 절을 사용하도록 변경하면...
SELECT C.Name as "Category Name", P.Name as "Product Name"
FROM Product P, ProductCategory PC, Category C
WHERE P.ProductID = PC.ProductID AND PC.CategoryID = C.CategoryID
ORDER BY C.Name, P.Name;
=> 연관된 서브쿼리를 사용하도록 변경하면..
SELECT C.Name as "Category Name", Product.Name as "Product Name"
FROM Product, Category C
WHERE Product.ProductID IN
  (SELECT ProductID FROM ProductCategory
   WHERE ProductCategory.CategoryID = C.CategoryID)
ORDER BY C.Name, Product.Name;

데이터 검색

SELECT Name, Description
FROM Product
WHERE (Description || Name LIKE '%devil%')
  AND (Description || Name LIKE '%mask%');
- 여러 컬럼에서 특정 문자열 검색할 때 꼼수로 모든 검색 대상 필드를 문자열로 변환하여 붙인다음에 검색.


top

TAG SQL, 오라클

Write a comment.


The Programmer's Guide to SQL

DB/SQL : 2008.01.07 17:38


사용자 삽입 이미지

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를 조작하려면 지켜야 하는 조건들이 있는데, 거기서 벗어나더라도 옵션으로 수정 가능하게 하면 테이블에 영향을 줄 수 있는건가? 그러진 않을 것 같다.
top

TAG SQL

Write a comment.


Chapter 12: Working with Database Objects

DB/SQL : 2008.01.07 17:26


참조 : 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));

top

TAG SQL, 오라클

Write a comment.


Chapter 11: Users and Security

DB/SQL : 2008.01.07 12:55


참조 : 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)데, 이 권한을 취소할 수 있음.
top

TAG SQL, 오라클

Write a comment.


Chapter 10: Transactions

DB/SQL : 2008.01.06 20:46


참조 : The Programmer's Guide to SQL

트랜잭션의 특징 ACID
    - Atomic
    - Consistency
    - Isolation
    - Durable

오라클은 기본으로 Auto Transaction 상태.
    - 첫 번째 SQL문을 실행할 때, 트랜잭션이 자동으로 시작 됨.
    - 따라서 START TRANSACTION(SQL-99 표준) 을 사용하지 않아도 됨.
    - 그러나 10g에서는 기본으로 오토커밋 상태임.

사용자 삽입 이미지

오토커밋
    - 이 상태에서는 모든 SQL문을 개별 트랜잭션으로 처리함.
    - SET AUTOCOMMIT ON/OFF 오라클에서 오토커밋 상태로 전환 하는 방법.

롤백하기
    - ROLLBACK [세이브포인트 이름]
    - SAVEPOINT 이름 생략하면, 트랜잭션 시작 이전 상태로 돌아감.

세이브포인트 만들기
    - SAVEPOINT [이름];

커밋하기
    - COMMIT

트랜잭션 예제

BEGIN

   INSERT INTO Student (StudentID, Name) VALUES (101, 'Dave');
   INSERT INTO Student (StudentID, Name) VALUES (102, 'Claire');

   SAVEPOINT BeforeAddingAnne;
   INSERT INTO Student (StudentID, Name) VALUES (103, 'Anne');
   ROLLBACK TO BeforeAddingAnne;

   COMMIT;

EXCEPTION
   WHEN OTHERS
      THEN ROLLBACK;
END;
/

오라클의 Isolation level
    - 기본은 Read Commited
    - Read Uncommited와 Repeatable Read는 지원하지 않음.
    - Isolaction Level 변경하는 방법
    SET TRANSACTION
    { { READ ONLY | READ WRITE }
      | ISOLATION LEVEL
        { READ COMMITTED
        | SERIALIZABLE } };

동기화 테스트 하려면, 오토커밋 상태가 아닌 상태에서 두 개의 창을 띄워 놓고 하면 됨.(READ COMMITED 상태)
    - 한 쪽 창에서 한 개의 레코드를 추가한다.
    - 추가 됐는지 SELECT 문으로 확인한다.
    - 다른 쪽 창에서 SELECT 해본다.
    - 다른 쪽 창에는 첫 번째 창에서 추가한 레코드가 보이지 않는다.
    - 첫 번째 창의 SQL이 아직 커밋되지 않았기 때문이다.
    - 첫 번째 창에서 COMMIT 을 실행한다.
    - 두 번째 창에서 SELECT로 확인한다. 이번에는 보인다.

top

Write a comment.


Chapter 8: Hiding Complex SQL with Views

DB/SQL : 2008.01.06 18:13


참조 : The Programmer's Guide to SQL

뷰 사용하면 좋은 점.
    - 같은 쿼리를 매번 작성하지 않아도 됨.
    - 데이터를 좀 더 편한 상태로 포맷해서 볼 수 있다.
    - 데이터가 아닌 뷰에 접근하게 함으로써 보안.
    - 데이터의 변경을 별도의 작업 필요없이 뷰에 반영할 수 있다.

뷰 만들기
    CREATE VIEW ViewName AS Query [WITH CHECK OPTION]
뷰 수정하기(오라클)
    CREATE OR REPLACE VIEW ViewName AS NewQuery
뷰 삭제하기
    DROP VIEW ViewName

뷰를 통해서 데이터를 수정/삭제 하려면 다음의 조건을 만족해야 한다.
    1. 어떤 aggregate functions(COUNT나 MAX) 또는 GROUP BY 를 사용하지 않아야 한다. 서브쿼리에서 사용하는 것은 괜찮다.
    2. DISINT를 사용하지 않아야 한다.
    3. 계산 식을 사용한 컬럼을 사용하지 않아야 한다.
    4. 명시적으로 설정한 값이 아닌, 데이블에서 가져온 데이터야만 한다.

뷰 예제
CREATE VIEW ClassAttendees AS
SELECT Class.ClassID,
   SUBSTR(Professor.Name, INSTR(Professor.Name, ' ') + 1)
           || ', '
           || SUBSTR(Professor.Name, 1,
              INSTR(Professor.Name, ' ') - 1)
   AS Name, 'Professor' AS Role
FROM Professor
   INNER JOIN Class ON Professor.ProfessorID =
      Class.ProfessorID
UNION
SELECT Enrollment.ClassID,
   SUBSTR(Student.Name, INSTR(Student.Name, ' ') + 1)
           || ', '
           || SUBSTR(Student.Name, 1,
              INSTR(Student.Name, ' ') - 1)
   AS Name, 'Student'
FROM Student
   INNER JOIN Enrollment ON Student.StudentID = Enrollment.StudentID;
- 문자열 연산을 사용했다.
- 명시적으로 값을 사용했다.
- 따라서 위의 뷰를 통해서 테이블의 데이터를 수정/삭제/추가 할 수 없다.
top

TAG SQL, 오라클

Write a comment.


Chapter 7: Querying Multiple Tables

DB/SQL : 2008.01.06 14:17


참조 : The Programmer's Guide to SQL

조인은 두 집합에 대한 곱셈연산인 데카르트 곱이다. 데카르트 곱이란 예를 들어, 집합 {a, b, c} 와 집합 {a, b}가 있을 때 그 결과는 {(a, a), (a, b), (b, a), (b, b), (c, a), (c, b)}다.

기본 문법(데카르트 곱)
    SELECT Class.ClassID, Class.Time, Room.RoomID
    FROM Room, Class;

크로스 조인
    - 기본 문법과 동일한 결과를 보여주지만, 다음과 같이 사용할 수 있다.
    - SQL-92 표준이다.
    SELECT Table1.Column1, Table1.Column2, Table2.Column3
    FROM Table1 CROSS JOIN Table2
    - 대부분 데카르트 곱을 결과로 원하지는 않을 것이다. Table1의 어떤 레코드가 Table2의 어떤 레코드와 조인되는지 명시할 필요가 있다. 이 때 보통 테이블의 관계를 이용한다.

Equi 조인
    - 다음과 같이 WHERE 절을 사용하여 컬럼 값을 동일성으로 레코드를 연결할 수 있다.
    SELECT Table1.Column1, Table1.Column2, Table2.Column3 FROM Table1, Table2
    WHERE Table1.Column1 = Table2.Column2
    - 좀 더 명시적으로 JOIN ON을(보다 명시적으로는 INNER JOIN ON) 사용할 수 있다.
    SELECT Table1.Column1, Table1.Column2, Table2.Column3 FROM Table1 JOIN Table2
    ON Table1.Column1 = Table2.Column2
    - 오라클에서 NATUAL JOIN을 사용할 수 있다.
    SELECT Class.ClassID,
        Class.CourseID,
        Class.Time,
        Room.Comments AS RoomName
    FROM Class NATURAL JOIN Room
    ORDER BY ClassID;
    - 오라클에서 USING을 사용할 수 있다.
    SELECT Room.Comments, Class.Time
    FROM Class
       JOIN Room
       USING (RoomID);

Non-Equi 조인
    - 두 개 이상의 테이블을 꼭 동일성으로 연결하지 않아도 된다.
    SELECT * FROM Table1, Table2
    WHERE Table1.Column1 < Table2.Column2
    - 이것도 물론 JOIN ON 문법을 사용할 수 있다.
    SELECT Room.RoomID, Class.Time
    FROM Room JOIN Class ON Room.RoomID <> Class.RoomID;

INNER 조인
    - 두 개 이상의 테이블에 모두 나타나는 결과만을 보여준다.

Outer 조인
    LEFT OUTER 조인
       - 관계에서 왼쪽에 위치한 테이블의 모든 레코드를 보여주며, 그에 대응하는 값이 없을 때는 NULL을 표시한다.
    RIGHT OUTER 조인
       - 관계에서 오른쪽에 위치한 테이블의 모든 레코드를 보여주며, 그에 대응하는 값이 없을 때는 NULL을 표시한다.
    FULL OUTER 조인
       - 관계를 맺는 테이블의 모든 레코드를 보여주며, 그에 대응하는 값이 없을 때는 NULL을 표시한다.


top

TAG SQL, 오라클

Write a comment.


Chapter 6: Combining SQL Queries

DB/SQL : 2008.01.06 10:58


참조 : The Programmer's Guide to SQL

두 개 이상의 테이블의 데이터가 필요할 때 서브쿼리나 조인을 사용할 수 있다. 이번 챕터는 서브쿼리에 대해 공부한다.

서브쿼리에는 두종류가 있다.
  • 독립적인 서브쿼리 non-correlated subquery : 포함된 쿼리가 한 번만 수행되며, 그 결과가 외부 쿼리에 전달된다.
  • 연관된 서브쿼리 correlated : 외부 쿼리의 데이터를 필요로 하며, 외부 쿼리의 결과 레코드 마다 한 번씩 수행하게 된다.
성능은 당연히 독립적인 서브쿼리가 좋아보인다. 그렇다고 연관된 서브쿼리를 안 쓸 수도 없을테고 외부 쿼리Outter Query의 레코드 수를 줄이면 그나마 Inner Query 수행 횟수가 줄어 들테니 이 점을 유의해야겠다.

연관된 서브쿼리 예제1

SELECT CustomerID, CustomerName, (
   SELECT COUNT(*) FROM CreditCards
   WHERE CreditCards.CustomerID = Customers.CustomerID)
   AS NumberOfCreditCards
FROM Customers

- 고객ID, 고객이름, 고객의 신용카드 거래 합계를 보여준다.
- Customers 테이블의 레코드 갯 수 만큼 서브 쿼리가 실행된다.

연관된 서브쿼리 예제2

SELECT e1.StudentID, e1.ClassID, (
   SELECT COUNT(*) FROM Enrollment e2
   WHERE e1.ClassID = e2.ClassID)—1
   AS OtherStudentsInClass
FROM Enrollment e1
WHERE StudentID = 6;

- ID가 6인 학생이 등록한 수업에 참여하는 다른 학생들의 합계를 구한다.
- Enrollment 테이블에서 SudentID가 6인 레코드 갯수 만큼 서브쿼리를 수행한다.

독립적인 서브쿼리 예제 1

SELECT ExamID, SustainedOn FROM Exam
WHERE SustainedOn <= (
   SELECT SustainedOn FROM Exam WHERE ExamID = 5)
ORDER BY SustainedOn DESC;

- 시험 ID가 5인 시험이 치뤄지기 이전에 치뤄진 모든 시험 목록을 역순으로 보여준다.
- 내부 쿼리는 한 번만 수행되고 그 결과를 외부 쿼리에서 사용한다.

독립적인 서브쿼리 예제 2

SELECT StudentID, Name FROM Student WHERE StudentID IN
  (SELECT StudentID FROM Enrollment WHERE ClassID IN
        (SELECT ClassID FROM Class WHERE ProfessorID IN
          (SELECT ProfessorID FROM Professor
          WHERE Name LIKE '%Williams%')));

- Williams 라는 이름이 들어가는 교수가 가르치는 학급에 등록한 학생들의 정보를 보여준다.
- 역시 전부 독립적인 서브쿼리로 여러개의 결과를 반환하기 때문에 IN을 사용했다.

EXISTS 연산자
- 외부 쿼리의 결과 레코드가 내부 쿼리 레코드 집합안에 포함되어 있다면, true.
- 따라서 연관된 쿼리로 작성해야 함.
- 따라서 a.커럼id = b.컬럼id 와 같은 공식이 내부 쿼리에 추가되어야 함.
- 그래야 외부 쿼리와 내부 쿼리가 연결 됨.

- 예제
SELECT StudentID, Name FROM Student s
WHERE EXISTS (
   SELECT StudentID FROM StudentExam e
   WHERE Mark < 40 AND e.StudentID = s.StudentID);
- Mark가 40점 이하인 학생 정보.
- AND e.StudentID = s.StudentID 없으면 그냥 Student 테이블의 모든 레코드 보여줌.

ALL 연산자
- 외부쿼리가 ALL 뒤에 있는 내부 쿼리의 결과를 모두 만족 시키는 경우 true.

-예제
SELECT StudentID, Grade FROM Enrollment e
WHERE Grade > ALL (
   SELECT Mark FROM StudentExam s
   WHERE s.StudentID = e.StudentID);
- 등록 정보 중에서 Grade가 그 학생이 치룬 모든 시험의 Mark보다 높은 학생 정보를 보여줌.

ANY 연산자
- 외부쿼리가 ANY 뒤에 있는 내부 쿼리의 결과에 하나라도 만족하는 경우 true.

- 예제
SELECT StudentID, Grade FROM Enrollment e
WHERE Grade < ANY (
   SELECT Mark/2 FROM StudentExam s
   WHERE s.StudentID = e.StudentID);
-등록 정보 중에서 Grade가 그 학생이 치룬 모든 시험의 Mark의 절반도 안되는 학생 정보를 보여줌.

쿼리 결과 데이터 조합하기
SELECT ColumnA, ColumnB FROM TableA
<Operator>
SELECT ColumnC, ColumnD FROM TableB;

Operator(오라클)
  • UNION : 합집합
  • UNION ALL : 합집합 + 교집합
  • INTERSECT : 교집합
  • MINUS : 차집합

흠.. EXISTS, ALL, ANY 어렵... -_-;;
top

TAG SQL, 오라클

Write a comment.


오라클 10g의 제한 사항

DB/SQL : 2008.01.05 23:15


참조 : Oracle Database 10g Express Edition: Not Just for Learners

무료인 대신에 다음과 같은 제한 사항들이 존재합니다.

첫 번째 제한은 오라클 XE가 차지할 수 있는 최대 메모리가 1G라는 것입니다. 이 제약사항은 해당 DB에 동시 접속자 수에 영향을 줍니다.

두 번째 제한은 오직 하나의 CPU만 사용한다는 것입니다. 따라서 CPU를 늘린다고 해서 DB 성능 향상은 기대할 수 없습니다. 그러길 원한다면 오라클 데이터베이스 SE 나 EE를 구매하세요.

세 번째 제한은 오직 하나의 XE 데이버테이스만을 실핼할 수 있다는 것입니다. 오라클은 스키마라는 개념을 사용해서 애플리케이션을 구별하기 때문에 애플리케이션마다 데이터베이스를 만들지 않아도 괜찮습니다.

마지막으로 오라클 XE가 사용할 디스크 공간을 4G로 제한합니다.

top

Write a comment.


Chapter 5: Performing Calculations and using Functions

DB/SQL : 2008.01.04 18:04


참조 : The Programmer's Guide to SQL

  • 사칙연산/비트연산/괄호 사용법 모두 자바와 동일
  • SELECT 절의 조건은 컬럼 이거나 어떤 값들이든지 될 수 있다.
  • WHERE 절의 조건은 Boolean 형태여야 한다.
  • 숫자 다루기 (오라클)
    • 내림 FLOOR()
    • 올린 CEIL()
    • 반올림 ROUND(컬럼, 자릿수)
  • 문자 다루기 (오라클)
    • SUBSTR(컬럼, x, y) : x는 시작 인덱스, y는 길이. y를 빼면, x부터 끝까지 잘라옴.
    • INSTR(원문, 검색어) : 원문에서 검색어가 시작하는 인덱스를 반환해 줌.
    • LENGTH(컬럼)
    • UPPER(컬럼)
    • LOWER(컬럼)
  • 날짜/시간 다루기(오라클)
    • DATE TIME TIMESTAMP 를 시간이나 날짜를 나타내는 문자열 앞에 붙여야 한다.
    • DATE '2003-05-02'
    • CURRENT_DATE 으로 현재 날짜/시간을 가져올 수 있다.
  • 데이터 타입 변환하기(오라클)
    • 문자열로 변환하기
      • TO_CHAR(DATE '2003-05-23', 'DD MONTH YYYY')
    • 숫자로 변환하기
      • TO_NUMBER('$5,102.25', '$9,999.99') -- Returns 5102.25
    • 일반적인 형변환
      • CAST('3.521' AS DECIMAL(3,2)) -- Returns 3.52
  • 사용자 정의 함수(UDF) 만들기
    • CREATE [OR REPLACE] FUNCTION function_name
      (parameter_list)
      RETURN data_type
      IS
      variable_list
      BEGIN
         [<SQL statements>]
         RETURN expression;
      END;
      /
    • Baik Keesun 이라는 값을 Keesun, Baik로 변환해주는 함수
    • CREATE OR REPLACE FUNCTION FormatName(FullName IN varchar)
      RETURN varchar
      IS
      FormattedName varchar(50);
      BEGIN
         FormattedName :=
            SUBSTR(FullName, INSTR(FullName, ' ') + 1) || ', ' ||
            SUBSTR(FullName, 1, INSTR(FullName, ' ') - 1);
         RETURN(FormattedName);
      END;
      /
top

TAG SQL, 오라클

Write a comment.


Chapter 4: Summarizing and Grouping Data

DB/SQL : 2008.01.04 15:04


참조 : The Programmer's Guide to SQL
  • 레코드 갯수 세기
    • COUNT(*) : 모든 레코드 갯수
    • COUNT(DISTINCT 컬럼) : 해당 컬럼의 값이 NULL이 아니고 중복되지 않는 레코드 갯수
    • COUNT(ALL 컬럼) : COUNT(컬럼) 형태로 쓰면 default로 ALL을 붙여서 사용함. 중복 허용.
  • 합계 구하기
    • SUM(DISTINCT 컬럼)
    • SUM(ALL 컬럼)
  • 평균 구하기
    • AVG(컬럼)
    • 오라클은 숫자 타입이 NUMBER라서 정수가 아닌 수도 담을 수 있다. 따라서 정수만 담긴 컬럼의 평균 값이 소수가 나올 수 있다.
  • 최소/최대값 구하기
    • MIN(컬럼)
    • MAX(컬럼)
  • 그룹핑 하기
    • SELECT ColumnA, AggFunc(AggFuncSpec) FROM Table
      WHERE WhereSpec
      GROUP BY ColumnA;
    • 주의할 것 : SELELT 절에 있는 ColumnA는 써도 되고 안 써도 되지만, 다른 컬럼은 쓰면 안 됌.
  • 그룹핑 후에 조건 주기
    • SELECT StudentID, AVG(Mark) AS AverageMark
      FROM StudentExam
      GROUP BY StudentID
      HAVING AVG(Mark) < 50 OR AVG(Mark) > 70;
    • WHERE 절은 그룹핑 하기 전에 실행되기 때문에, 그룹핑의 결과를 다룰 수는 없슴.
    • 따라서 HAVING을 사용하여 그룹핑 후에 조건을 줄 수 있슴.
  • 상위에서 N개의 레코드 가져오기
    • 오라클의 ROWNUM 사용하기.
    • SELECT StudentID, AverageMark FROM (
         SELECT StudentID, AVG(Mark) AS AverageMark
         FROM StudentExam
         GROUP BY StudentID
         ORDER BY AverageMark DESC
      )
      WHERE ROWNUM <= 5;
    • ROWNUM 키워드는 WHERE 절에서만 사용할 수 있다.
  • 랭킹 매기기
    • 오라클에서 RANK() OVER () 사용하기
    • SELECT Ranking, StudentID, AverageMark FROM (
         SELECT RANK() OVER (ORDER BY AVG(Mark) DESC) AS Ranking,
                 StudentID, AVG(Mark) AS AverageMark
         FROM StudentExam
         GROUP BY StudentID)
      WHERE Ranking <= 4;
    • 소수점 이하를 모두 버렸을 때, 순위를 매기면 누가 더 높은 것인지 알 수 있다.
  • Analytic Functions
    • 함수() OVER () 문법으로 SQL 표준은 아닌데 오라클에서 사용 가능한 문법.
    • SELECT StudentID, Mark, AVG(Mark) OVER
      (PARTITION BY StudentID
       ORDER BY StudentID, Mark) Running_Avg_by_Student
      FROM StudentExam
      ORDER BY StudentID, Mark;
    • OVER는 어렵다 어려워;;

top

TAG SQL, 오라클

Write a comment.


Chapter 3: Modifying Data

DB/SQL : 2008.01.04 11:23


참조 : The Programmer's Guide to SQL

  • 새로운 값 추가하기
    • INSERT INTO 테이블 (컬럼들) VALUES (값들);
    • INTO는 MySQL에서는 생략가능 하지만, SQL-99 스팩이며 오라클에서는 필수다.
    • 컬럼명을 안 써도 되는 경우
      • RDBMS가 값을 반드시 자동 생성 해주는 경우. ex) 주키 컬럼
      • RDBMS가 갑을 자동으로 채워줄 수 있는 경우. ex) 기본값을 설정해둔 컬럼
      • 컬럼이 timestamp 타입임 경우. RDBMS가 알아서 현재 날짜와 시각을 넣어 줌.
      • NULL을 허용하는 컬럼.
    • 기본값으로 전부 채우기
      • INSERT INTO Author DEFAULT VALUES;
    • 여러 줄 한 번에 넣기
      • VALUES 대신에 SELECT 문 사용.
  • 수정하기
    • UPDATE 테이블 SET 컬럼 = 값, 컬럼 = 값 WHERE 조건;
    • 만약에 WHERE 절 빼면 해당 테이블의 모든 컬럼이 바뀌게 되니까 UPDATE 문 실행하기 전에 UPDATE를 SELECT로 바꿔서 변경 대상을 확인하는 것이 좋다.
  • 삭제하기
    • DELETE FROM 테이블 WHERE 조건;
      • FROM도 선택적인데 사용하는 것이 좋다.
      • 주키 컬럼을 조건절에 사용하는 것이 좀 더 안전하다. 유일한 컬럼을 지칭하니까.
    • TRUNCATE TABLE 테이블;
      • DELETE FROM과는 달리 삭제에 대한 로그를 남기지 않아서 좀 더 빠르다.
      • 대신 롤백이 불가능하다.
top

TAG SQL, 오라클
  1. Favicon of http://gerions.egloos.com BlogIcon 윤걸 2008.01.05 17:53 PERM. MOD/DEL REPLY

    delete 와 truncate 의 가장 큰 차이점은 index 의 공간을 사용하는지 여부에 있지용~
    delete 로 날렸을경우 삭제된 데이터의 index 영역은 그대로 남아있게되고
    truncate 는 테이블 자체의 데이터를 다 날려버린 초기화 상태라고 할까나?
    구래서 데이터가 많이 생기고 delete 가 많이 일어나게되면 index 영역이 커질 수 있게된다.
    그거이~ 시스템의 성능에 영향을 미칠 수 있기 땜시로, 가끔 index rebuild 를 해주는게지요~

    갑자기 db 에 삘~ 받았나 보구만..

    Favicon of http://whiteship.tistory.com BlogIcon 기선 2008.01.05 22:42 PERM MOD/DEL

    우어.. 이런 고급정보까지.. 감사합니다.
    아직 인덱스까지도 못 갔는데 책 뒷부분에 나오겠죠.

    DB 재밌어요.

Write a comment.


Chapter 2: Retrieving Data with SQL

DB/SQL : 2008.01.04 09:41


참조 : The Programmer's Guide to SQL

  • '모든 컬럼 가져오기(SELECT * FROM Student;)'의 안 좋은 점.
    • 해당 테이블에 몇 개의 컬럼이 있는 DB가 알아봐야 하는 부과작업 필요해짐.
    • 컬럼들이 어떤 순으로 어떤게 올지 예측할 수 없다.
    • 간단한 테스트 용도 외에는 잘 안써.
  • 예약어Reseved나 SQL 키워드를 테이블 명이나 컬럼 명으로 했을 경우.
    • 오라클은 " "를 사용하여 감싸주며 그 안에는 모두 대분자로 표시해야함.
      • 자동으로 예약어나 키워드를 대문자로 변경하기 때문에...
  • AS 로 별칭 사용하기. SELECT s.name AS StudentName FROM Student AS s
    • FROM 절의 테이블 별칭에서 AS는 생략하는 것이 SQL-99 스팩.
    • 하지만 가독성을 위해서 명시적으로 표기해주는 것이 좋다.
  • 문자열 연결할 때 오라클은 || ' ' || 이것 사용.
    • SELECT name || '은 ' || age || '살 입니다.' FROM Student;
    • 문자열 연결 용도라서 숫자 타입도 자동으로 문자로 변환해줌.
  • 정렬하기
    • ORDER BY 컬럼 ACS/DESC
  • 중복제거
    • SELECT DISTINCT 컬럼명 FROM 테이블;
    • 컬럼 두 개 이상을 지정하면, 두 개를 하나의 묶음으로 생각한 유일한 레코드Record 들을 가져온다.
  • WHERE 절로 조건 주기
    • 조건이 TRUE를 만족하는 레코드만 가져오고 FALSE나 UNKNOWN인 레코드는 무시한다.
    • 날짜 비교하기
      • 'yyyy-mm-dd' 형태의 문자열은 모든 밴더들이 날짜로 변환해준다.
    • AND 나 OR로 여러개의 조건 표시 가능.
    • IN 을 사용하여 특정 컬럼의 값 범위 지정 가능.
  • 패턴 매칭
    • WHERE comments LIKE '%greate%' = 컬럼에 greate가 들어가는 레코드.
    • WHERE UPPER(comments) LIKE '%GREATE%' = 대소문자 구분 없이 greate가 들어가는 레코드.
    • 만약에 값에 %가 포함되어 있는 경우.
      • WHERE CustomerRating LIKE '%50p%%' ESCAPE 'p';
      • 이런식으로 ESCAPE 문자 지정해서 바로 다음 문자 무시하도록 하기.
top

TAG SQL, 오라클

Write a comment.


Chapter 1: Understanding SQL and Relational Databases

DB/SQL : 2008.01.03 19:16


참조 : The Programmer's Guide to SQL (중고로 2달러 짜리도 있네요.)
  • SQL을 DDL, DML, DCL로 나눠볼 수 있다.
  • SQL의 역사 : SQL-89 => SQL-92 => SQL-99
  • 비교 연산의 결과는 세 가지. True, False, Unknown.
  • NULL 인지 확인할 때 = NULL 이 아니라 is NULL 로 물어봐야 해.
    • = 는 비교 연산자인데, NULL 과 다른 값들을 비교하면 결과는 UNKNOWN.
  • 1대다 관계 맺기
    • '다'쪽에 외례키 컬럼(상대방의 주키를 가지고 있는 컬럼)추가.
      • 그래서 '다'쪽의 테이블을 referencing table이라고 함.
      • 그래서 '1'쪽의 테이블을 referenced table이라고 함.
    • 무결성 제약 사항 생김.
  • 다대다에서 연관 테이블은 영어로 junction table, linking table, associate table, bridge table
  • 오라클에 접속하기
    • Connect username/password
  • 접속 끝내기
    • Disconnet
  • 10g는 Database가 한 개이기 때문에, Create Database XXX; 사용 못함.
사용자 삽입 이미지
ORA-01501: CREATE DATABASE 문 오류입니다 ORA-01100: 데이터베이스가 이미 마운트되었습니다
만들려고 하면, 이런 메시지를 만나게 됩니다.
  • 오라클에서 한 번에 여러 Statement 실행하기.
    • BEGIN END;/ 사용.
사용자 삽입 이미지



top

  1. Favicon of http://gerions.egloos.com BlogIcon 윤걸 2008.01.05 17:47 PERM. MOD/DEL REPLY

    뭔가 착각한거 아닌가??
    10g 에서 데이터베이스를 하나밖에 생성 못 하는 건 아닐터인데..
    일단 unmout 시키고 생성해야 하는거 같은데..
    글구, 데이터베이스를 구지 여러개 만들지 않고 테이블스페이스만 늘려도 상관없을거 같네~

    Favicon of https://whiteship.tistory.com BlogIcon 기선 2008.01.05 23:17 신고 PERM MOD/DEL

    http://whiteship.tistory.com/1363

    오라클은 스키마라는게 있어서 굳이 DB가 하나여도 별로 상관없나봐요. 스키마가 뭐죠?

Write a comment.