[PostgreSQL] 테이블 조작

psql로 접속

sudo su – postgres

psql -U postgres DB이름

이렇게 하면 콘솔이 DB이름이 됨. 이제 여기서 할 일을 하면 되는데..

  • \h를 입력하면 사용할 수 있는 SQL을 보여주고
  • \?를 입력하면 사용할 수 있는 psql 커맨드를 보여준다.
  • 그리고 쿼리를 실행하려면 세미콜론으로 끝내면 된다.

컬럼 타입 변경하기

ALTER TABLE 테이블이름 ALTER COLUMN 컬럼이름 TYPE 원하는타입;

예) Comment 테이블의 comment 컬럼 타입을 text로 변경하기

ALTER TABLE Comment ALTER COLUMN comment TYPE text;

테이블 목록 출력

\dt

테이블 스키마 확인

\d 테이블이름

DB 목록 확인

\l (엘)

Oracle에서 Tablespace 만들기

참조한 글
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 [사용자 이름]

Chapter 14: Case Study: Building a Product Catalog

참조 : 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%’);
– 여러 컬럼에서 특정 문자열 검색할 때 꼼수로 모든 검색 대상 필드를 문자열로 변환하여 붙인다음에 검색.

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