고급 SQL
Timestamps and Extract
- 현재 시각 정보를 표시
# 내가 속한 타임존 표시
SHOW TIMEZONE
# 현재 시간대 표시
SELECT NOW()
# 타임스탬프 형식이 아닌 Thu Mar 19 ... 이런 식
SELECT TIMEOFDAY()
- DATE 년월 SELECT 할때 MONTH() 사용해도됨
AGE()
AGE(date_col)TO_CHAR()
- 일자 유형을 글자로 바꿔줌
TO_CHAR(date_col, 'mm-dd-yyyy')
SELECT TO_CHAR(payment_date,'MONTH - YYYY')
FROM payment
SELECT DISTINCT(TO_CHAR(payment_date,'MONTH'))
FROM paymentEXTRACT
SELECT EXTRACT(YEAR FROM payment_date)
FROM paymentDOW (Day Of Week)
-
날짜에서 요일을 가져올 수 있습니다
-
보통 0은 일요일
SELECT COUNT(*) FROM payment
WHERE EXTRACT(dow FROM payment_date)= 1
문자열
문자열 합치기 ||
SELECT first_name || last_name FROM customer
SELECT first_name || ' ' || last_name AS full_name FROM customer
LEFT(string,n)
- 첫글자만
- 문자열의 n번째까지 도출
서브쿼리
- 괄호 안의 서브 쿼리가 먼저 작동
SELECT title,rental_rate FROM film
WHERE rental_rate >(SELECT AVG(rental_rate) FROM film)EXISTS
SELECT first_name, last_name
FROM customer AS c
WHERE EXISTS
(SELECT * FROM payment as p
WHERE p.customer_id = c.customer_id
AND amount >11)데이터베이스 및 테이블
기본키
정의
- 기본 키는 테이블의 각 행을 고유하게 식별하는 테이블의 열(또는 열 집합)입니다.
고유성
-각 기본 키는 테이블 내에서 고유해야 합니다.
Null 허용 여부
- NULL 값을 가질 수 없음.
목적
-모든 레코드를 고유하게 식별할 수 있도록 합니다.
외래키
정의
- 외래 키는 두 테이블을 함께 연결하는 데 사용되는 테이블의 열(또는 열 집합)입니다.
참조
- 기본 키 또는 다른 테이블의 고유 키를 참조합니다.
목적
- 두 테이블 간의 관계가 일관되게 유지되도록 하여 참조 무결성을 유지하는 데 사용됩니다.
Null 허용 여부
- 외래 키는 NULL 값을 가질 수 있으며 이는 관계가 선택 사항임을 나타냅니다.
예시
Students Table
- StudentID (Primary Key)
- StudentName
- ClassID (Foreign Key)
Classes Table
- ClassID (Primary Key)
- ClassName
테이블 제약
CHECK 제약조건
- 컬럼에 들어갈 수 있는 값의 범위를 제한
- 행의 모든 값이 특정 조건을 만족하도록 한다.
CREATE TABLE Employee (
ID int NOT NULL,
Name varchar(255) NOT NULL,
Age int,
CHECK (Age >= 18)
);
CREATE TABLE employees(
emp_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
birthday DATE CHECK (birthday > '1900-01-01'),
hire_date DATE CHECK (hire_date > birthday),
salary INTEGER CHECK (salary >0)
)
- 직원의 연령이 18세보다 큰지 확인하기 위해 CHECK 제약 조건을 적용
EXCLUSION 제약조건
-
지정된 열이나 표현식에서 두 행을 비교하는 경우 이러한 비교 중 적어도 하나가 false 또는 null을 반환하는지 확인하는 데 사용
-
지정된 값 범위에 대해 중복이 발생하지 않도록 하는 데 주로 사용됩니다.
CREATE TABLE RoomReservations (
RoomID int NOT NULL,
TimeSlot tsrange,
EXCLUDE USING gist (RoomID WITH =, TimeSlot WITH &&)
);
UNIQUE
- 열에 있는 모든 값이 달라야한다.
NOT NULL
- 열이 NULL 값을 가질수 없도록
추가 기능
RETURNING
- 방금 삽입, 업데이트 또는 삭제한 행의 열 값을 반환
- 어떤 변경 사항이 적용되었는지 확인하는 용도
- 별도의 SELECT 쿼리를 실행할 필요 없다
INSERT INTO Employees (Name, Department)
VALUES ('John Doe', 'Finance')
RETURNING EmployeeID;
-- 새 행을 삽입하고 새 직원의 'EmployeeID'를 즉시 반환
UPDATE Employees
SET Department = 'HR'
WHERE Name = 'John Doe'
RETURNING Name, Department;
--'John Doe' 부서가 'HR'로 업데이트되고 직원의 이름과 새 부서가 반환
SERIAL(MySql 의 AUTO_INCREMENT)
-
PostgreSQL과 같은 특정 SQL 데이터베이스 시스템에만 적용
-
테이블에 자동 증가 정수 열 생성
-
smallserial, serial,bigserial
CREATE TABLE Employees (
EmployeeID SERIAL PRIMARY KEY,
Name VARCHAR(100),
Department VARCHAR(50)
);
CRUD
CREATE
- 테이블 생성
CREATE TABLE account_job(
user_id INTEGER REFERENCES account(user_id),
job_id INTEGER REFERENCES job(job_id),
hire_date TIMESTAMPS
)
- REFERENCES 는 다른 테이블 account, job 과 외래키 관계를 설정하는데 사용
UPDATE
- 테이블의 기존 레코드를 수정하는 데 사용됩니다.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
- SET: 업데이트할 컬럼과 새로운 값을 지정하는 절입니다.
DELETE
- 테이블에서 하나 이상의 행을 제거하는 데 사용
- WHERE 절을 생략하면 테이블의 모든 행이 제거
DELETE FROM Orders
WHERE OrderID = 12345;
ALTER
- 기존 데이터를 잃지 않고 이미 존재하는 테이블 구조를 바꿈
ALTER TABLE Employees
ADD Email VARCHAR(255);
- 새 열 추가: 기존 테이블에 새 열을 추가합니다.
ALTER TABLE Employees
DROP COLUMN Department;
- 열 삭제: 테이블에서 열을 제거합니다.
ALTER TABLE Employees
MODIFY COLUMN Department VARCHAR(100);
- 열 수정: 열의 데이터 유형이나 속성을 변경합니다.
ALTER TABLE Employees
ADD CONSTRAINT unique_email UNIQUE (Email);
- 제약조건 추가: 테이블에 제약조건(예: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK)을 추가합니다.
DROP
-
테이블, 데이터베이스, 인덱스 또는 뷰와 같은 데이터베이스 객체를 제거하는 데 사용
-
객체가 존재하는 것이 확실할 때, 종속성이 없을때 단독 사용
DROP TABLE Employees;
CASCADE
- DROP과 함께 삭제되는 객체에 종속된 객체(예: 외래 키 제약 조건, 보기 또는 인덱스)를 자동으로 제거
DROP TABLE Employees CASCADE;
IF EXISTS
- 존재하지 않는 객체를 삭제하려고 할 때 발생하는 오류를 방지하기 위해.
- 개체를 찾을 수 없는 경우 작업 수행X 오류 발생X
DROP TABLE IF EXISTS Employees;
