[SQL] 6. 데이터베이스 및 테이블 만들기

🖥 시작하며

🔍 CREATE

CREATE TABLE table_name (
  column_name TYPE column_constraint,
  column_name TYPE column_constraint,
   table_constraint table_constraint
) INHERITS existing_table_name;

CREATE TABLE players(
  player_id SERIAL PRIMARY KEY,
  age SMALLINT NOT NULL
   );
-- PK는 SERIAL로 지정한다.

CREATE TABLE account(
	user_id SERIAL PRIMARY KEY,
	username VARCHAR(50) UNIQUE NOT NULL,
	password VARCHAR(50) NOT NULL,
	email VARCHAR(250) UNIQUE NOT NULL,
	created_on TIMESTAMP NOT NULL,
	last_login TIMESTAMP
)

CREATE TABLE job (
	job_id SERIAL PRIMARY KEY,
	job_name VARCHAR(200) UNIQUE NOT NULL
)

CREATE TABLE account_job (
	user_id INTEGER REFERENCES account(user_id),
	job_id INTEGER REFERENCES job(job_id),
	hire_date TIMESTAMP
)

🔍 INSERT

INSERT INTO account(username, password, email, created_on)
VALUES
('Jose', 'password', 'jose@mail.com', CURRENT_TIMESTAMP)

INSERT INTO job(job_name)
VALUES
('President')

INSERT INTO job(job_name)
VALUES
('Astronaut')

INSERT INTO account_job(user_id, job_id, hire_date)
VALUES
(1, 1, CURRENT_TIMESTAMP)

🔍 UPDATE

UPDATE account
SET last_login = CURRENT_TIMESTAMP
    WHERE last_login IS NULL;

📌 UPDATE JOIN

UPDATE TableA
SET original_col = TableB.new_col
FROM tableB
WHERE tableA.id = TableB.id

📌 영향을 받은 부분 탐색

UPDATE account
SET last_login = created_on
RETURNING account_id,last_login

UPDATE account
SET last_login = CURRENT_TIMESTAMP

UPDATE account
SET last_login = created_on

UPDATE account_job
SET hire_date = account.created_on
FROM account
WHERE account_job.user_id = account.user_id

UPDATE account
SET last_login = CURRENT_TIMESTAMP
RETURNING email, created_on, last_login

🔍 DELETE

DELETE FROM job
WHERE job_name = 'Cowboy'
RETURNING job_id, job_name

🔍 ALTER

-- 기본 ALTER TABLE 구문
ALTER TABLE table_name action

-- 새 열 추가
ALTER TABLE table_name 
ADD COLUMN new_col TYPE

-- 열 삭제
ALTER TABLE table_name 
DROP COLUMN col_name

-- 열의 기본값 설정
ALTER TABLE table_name 
ALTER COLUMN col_name 
SET DEFAULT value

-- 열의 기본값 제거
ALTER TABLE table_name 
ALTER COLUMN col_name 
DROP DEFAULT

-- 열을 NOT NULL로 설정
ALTER TABLE table_name 
ALTER COLUMN col_name 
SET NOT NULL

-- 열의 NOT NULL 제약 조건 제거
ALTER TABLE table_name 
ALTER COLUMN col_name 
DROP NOT NULL

-- 열에 제약 조건 추가
ALTER TABLE table_name 
ALTER COLUMN col_name 
ADD CONSTRAINT constraint_name

ALTER TABLE information
RENAME TO new_info

ALTER TABLE new_info
RENAME COLUMN person TO people

ALTER TABLE new_info
ALTER COLUMN people DROP NOT NULL

-- 위 명령어 하면 아래 가능해짐, 권한 뺐으므로
INSERT INTO new_info(title)
VALUES
('some new title')

🔍 DROP

-- 기본적인 열 삭제: 지정된 열을 테이블에서 제거합니다.
ALTER TABLE table_name 
DROP COLUMN col_name;

-- CASCADE 옵션을 사용한 열 삭제: 해당 열에 의존하는 모든 객체(예: 뷰, 제약 조건)도 함께 삭제합니다.
ALTER TABLE table_name 
DROP COLUMN col_name CASCADE;

-- IF EXISTS 옵션을 사용한 열 삭제: 지정된 열이 존재할 경우에만 삭제를 수행하며, 존재하지 않아도 오류를 발생시키지 않습니다.
ALTER TABLE table_name 
DROP COLUMN IF EXISTS col_name;

-- 여러 열 동시 삭제: 한 번의 명령으로 여러 열을 삭제합니다.
ALTER TABLE table_name 
DROP COLUMN col_one, 
DROP COLUMN col_two;

🔍 CHECK

CREATE TABLE example(
    ex_id SERIAL PRIMARY KEY,
    -- SERIAL: 자동 증가하는 정수 (1, 2, 3, ...)
    -- PRIMARY KEY: 이 열을 테이블의 기본 키로 지정

    age SMALLINT CHECK (age > 21),
    -- SMALLINT: 작은 범위의 정수 (-32,768 to 32,767)
    -- CHECK (age > 21): 나이가 21보다 큰 값만 허용

    parent_age SMALLINT CHECK ( parent_age > age)
    -- CHECK ( parent_age > age): 부모의 나이가 자녀의 나이보다 큰 값만 허용
);

CREATE TABLE employees (
	emp_id SERIAL PRIMARY KEY,
	first_name VARCHAR(50) NOT NULL,
	last_name VARCHAR(50) NOT NULL,
	birthdate DATE CHECK (birthdate > '1900-01-01'),
	hire_date DATE CHECK (hire_date > birthdate),
	salary INTEGER CHECK (salary > 0)
)