FULL OUTER JOIN
ex) SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.col_match=TableB.col_match
이렇게 하면 모든 정보가 검색됨, 두 테이블에 모두 나오든 한 테이블에만 존재하든 다 포함됨
테이블B와 테이블A의 순서가 바뀌어도 완전 대칭이라 상관없음, 또한 대칭이기 때문에 서로 테이블 순서도 바꿀 수 있음
ex2) SELECT * FROM customer
FULL OUTER JOIN payment
ON customer.customer_id = payment.customer_id
WHERE customer.customer_id IS null
OR payment.payment_id IS null -> 실행하면 빈 결과가 나옴(새로운 개인정보 보호정책 준수한다는 말)
customer 테이블과 payment 테이블을 완전히 결합해 customer_id를 결합할 것이라는 의미
이걸 필터링해 customer 테이블에만 고유한 행을 찾아본다 -> 우리에게 있는 고객 정보 중 결제한 적이 없거나 payment 테이블에만 고유한 것이 있다는 의미
고객과 관련 없는 결제 정보가 없고 더 중요한건 결제한 적이 없는 사람의 성이나 이름을 포함한 고객 이메일 정보가 없음
이것을 확인할 수 있는 또다른 방법은
customer 테이블과 payment 테이블 모두에서 중복되지 않는 고객 ID를 살펴보며 확인할 수 있음
customer 테이블과 payment 테이블 중 하나에만 완전히 고유한 고객 ID는 없음
만약 어떤 고객에 관한 정보가 있다면 고객이 과거에 결제를 했기 때문임
따라서 우리는 이것을 선택하고 FULL OUTER JOIN을 적용해 테이블 중 하나에만 고유한 것이 없도록 필터링이 가능함
이 개념을 이해하기 위해 SELECT COUNT(DISTINCT customer_id) FROM customer를 실행하면 고유한 고객 ID(중복되지 않는 ID)가 나옴
-> 하지만 이전의 개인정보 보호 정책 준수에 대한 완전한 답변은 아님, 여러 테이블에 여러 ID가 있을 수 있기 때문
답변할 수 있는 실질적 답변은 결제 정보가 없는 고객 정보가 없도록 하기 위해 FULL OUTER JOIN을 사용하고 추가적으로 WHERE 조건으로 필터링해 고객 ID 및 결제 ID의 고유 식별자에 null 문을 사용하는 것임
-> 이 자체는 기술적으로 마지막으로 수행한 쿼리를 추가 검증할 뿐 실제로 질문에 완전한 답변을 하는 것이 아님, 왜냐하면 고객 ID에는 문자가 있을 수 있고 한 테이블에는 599개의 고유 문자 또는 연속 문자가 있고 다른 테이블에는 599개의 고유 숫자가 있을 수 있기 때문
- 결론 : 이것은 우리가 한 것을 명확히 하고 검증하지만 개인정보 보호 정책을 100% 확인하진 않는다는 뜻
-> 유일한 방법은 WHERE에 FULL OUTER JOIN을 사용하는 것뿐임
LEFT OUTER JOIN
LEFT OUTER JOIN은 왼쪽 테이블에 있는 레코드 세트를 결과로 출력함
오른쪽 테이블에 일치하는 내용이 없으면 결과는 null임
나중에는 WHERE문을 추가해 LEFT OUTER JOIN을 추가로 수정하는 방법을 배움
LEFT OUTER JOIN은 LEFT JOIN으로 줄여서 작성할 수 있음, 따라서 LEFT JOIN이든 LEFT OUTER JOIN이든 상관X
LEFT OUTER JOIN(LEFT JOIN)은 순서가 중요함
어떤 것이 왼쪽 테이블이 될지 지정해야 하기 때문임
ex) SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.col_match = TableB.col_match
TableA는 테이블A에만 있거나 테이블 A와 B 모두에 있는 것을 가져온다는 것, 테이블 B에만 있는 것은 반환하지 않음
LEFT OUTER JOIN with WHERE
WHERE문으로 조건을 추가해 왼쪽 테이블에만 고유한 행을 추가할 수 있음
예를 들어, 테이블A의 고유 항목만 구하고 싶을 때 테이블 B에 LEFT OUTER JOIN을 적용하고 WHERE TableB.id IS null이라고 작성함
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.col_match = TableB.col_match WHERE TableB.id IS null
ex) SELECT film.film_id,title,inventory_id
FROM film
LEFT OUTER JOIN inventory ON
inventory.film_id = film.film_id
film이 왼쪽에 있기 때문에 film 테이블에만 있거나 film과 inventory 모두에 있는 행만 확인할 것이라는 의미
ex2) SELECT film.film_id,title,inventory_id,store_id
FROM film
LEFT OUTER JOIN inventory ON
inventory.film_id = film.film_id
WHERE inventory.film_id IS null
RIGHT JOIN
LEFT OUTER JOIN과 완전히 동일하지만 테이블이 서로 바뀐다는 점만 다름
ex) SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.col_match = TableB.col_match
ex2) SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.col_match = TableB.col_match WHERE TableA.id IS null
UNION 연산자
JOIN과 UNION의 기본적 차이는 UNION은 두 결과를 직접 붙인다는 것
구문
ex) SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
ex2) 이름을 기준으로 자료를 정렬하거나 분류하고 싶다면
SELECT FROM Sales2021_Q1 UNION SELECT FROM Sales2021_Q2 ORDER BY name;
ON
PostgreSQL에서 ON은 JOIN 절에서 사용되는 조건절입니다.
일반적인 JOIN 절은 두 개의 테이블을 연결하여 결과를 반환합니다. 그러나 때로는 두 테이블의 결합 조건이 간단한 등식 연산자(=)가 아닌 경우가 있습니다. 이때 ON 절을 사용하여 두 테이블 간의 결합 조건을 명시적으로 지정할 수 있습니다.
예를 들어, 다음 쿼리는 employees 테이블과 departments 테이블을 조인하여 부서 이름과 함께 각 직원의 이름을 반환합니다.
SELECT employees.name, departments.name
FROM employees
JOIN departments
ON employees.department_id = departments.id;
위의 쿼리에서 ON 절은 employees.department_id = departments.id와 같이 두 테이블 간의 결합 조건을 명시적으로 지정합니다. 이 조건은 employees 테이블의 department_id 열과 departments 테이블의 id 열이 일치해야 함을 의미합니다.
ON 절은 INNER JOIN, LEFT JOIN, RIGHT JOIN 및 FULL OUTER JOIN과 함께 사용될 수 있으며, 각각 다른 결과를 반환합니다. INNER JOIN은 조인 조건에 따라 두 테이블의 공통된 행만 반환하며, LEFT JOIN은 왼쪽 테이블의 모든 행을 포함하고, RIGHT JOIN은 오른쪽 테이블의 모든 행을 포함합니다. FULL OUTER JOIN은 양쪽 테이블의 모든 행을 포함합니다.
JOIN 연습문제
- 캘리포니아에 살고 있는 고객의 이메일은 무엇인가?
Hint : district 열 검토, address와 customer 테이블을 사용해야 할 것
SELECT district,email FROM address
INNER JOIN customer
ON customer.address_id = address.address_id
WHERE district='California'
- 닉 윌버그(Nick Wahlberg)라는 배우가 출연한 모든 영화 목록을 찾고 싶음
Hint : 하나의 쿼리에서 연달아 2개의 JOIN 함수를 적용해야함, 사용해야 하는 테이블은 actor, film, film_actor (어려움)
내 답안
SELECT title,first_name,last_name FROM film_actor
INNER JOIN actor
ON film_actor.actor_id = actor.actor_id
INNER JOIN film
ON film_actor.film_id = film.film_id
WHERE first_name = 'Nick' AND last_name = 'Wahlberg'
강의 답안
SELECT title,first_name,last_name FROM actor
INNER JOIN film_actor
ON actor.actor_id = film_actor.actor_id
INNER JOIN film
ON film_actor.film_id = film.film_id
WHERE first_name = 'Nick' AND last_name = 'Wahlberg'
-> actor에서 결과를 얻은 후 actor_id를 기준(ON)으로 film_actor과 결합(ON)하고 그 결과들을 film_id 기준으로 film과 결합(JOIN)한다. 순서는 바꿀 수 있음
- 두 번째 문제는 복습 해야할듯함
<Timestamp & Extract> - Advanced Part
시간 데이터 유형 - 시,분,초
날짜 데이터 유형 - 년,월,일,요일
Timestamp - 둘을 합친 것으로 날짜 정보와 시간 정보를 포함함
TIME - Contains only time
DATE - Contains only date
TIMESTAMP - Contains date and time
TIMESTAMPTZ - Contains date,time and timezone
TIMESTAMPTZ는 타임스탬프 표준시간대로 날짜,시간,표준시간대 정보를 포함함
TIMEZONE,NOW,TIMEOFDAY,CURRENT_TIME,CURRENT_DATE - 표준시간대,현재,세계시각,현재시각
SHOW ALL - 실행 시간 값을 보여주는 매개 변수
SELECT NOW() - 현재 타임스탬프 정보를 달라는 명령
SELECT TIMEOFDAY() - 위와 거의 같은 정보를 알려주지만 날짜,시간,연도와 표준시간대를 알려줌(문자열로 되어있음)
SELECT CURRENT_TIME - 표준시간대의 시간
SELECT CURRENT_DATE - 단순히 현재 날짜를 보여줌
EXTRACT
YEAR,MONTH,DAY,WEEK,QUARTER
추출 방법
EXTRACT 괄호 안에 추출하고자 하는 서브 컴포넌트를 입력하면 됨
ex) EXTRACT(YEAR FROM date_col)
AGE - 타임스탬프 내에서 현재까지 시기를 계산해서 알려줌
ex) AGE(date_col)
TO_CHAR() - 일자 유형을 글자로 바꿔주는 일반적인 함수
ex) TO_CHAR(date_col, 'mm-dd-yyyy')
Timestamp and Extract 사전과제
- 어떤 달에 지급이 이뤄졌나?(약어 말고 전체 용어로 풀어서 서식처리)
Hint : 추출 함수를 사용할 필요X
SELECT DISTINCT(TO_CHAR(payment_date,'MONTH'))
FROM payment
- 월요일에는 지급이 얼마나 발생했나?
Hint : 추출 함수를 쓰길 권장함, DOW 키워드 검색, 일요일을 한 주의 시작으로 생각해 0으로 인식
SELECT COUNT(*)
FROM payment
WHERE EXTRACT(DOW FROM payment_date) = 1
수리 함수
ex) SELECT ROUND(rental_rate/replacement_cost,4)*100 FROM film
ex2) SELECT ROUND(rental_rate/replacement_cost,4)*100 AS percent_cost
FROM film
ex3) SELECT 0.1 * replacement_cost AS deposit
FROM film
문자열 함수와 연산자
ex) SELECT LENGTH(first_name) FROM customer
ex2) SELECT first_name || last_name FROM customer - 이름과 성을 합쳤음
ex3) SELECT first_name || ' ' || last_name FROM customer - 이름과 성을 공백으로 구분함
ex4) SELECT upper(first_name) || '--' || upper(last_name) AS full_name
FROM customer
left - n개 만큼의 문자를 첫 글자부터 나타냄
ex) left('abcde',2) - 첫 번째와 두 번째 알파벳이 도출됨
- 문자열 함수와 오퍼레이터를 사용하는 보편적인 예시 -
ex) SELECT LOWER(LEFT(first_name,1)) || LOWER(last_name) || '@gmail.com'
AS custom_email
FROM customer
서브 쿼리
서브 쿼리를 쓰면 더 복잡한 쿼리를 만들 수 있는데 다른 쿼리의 결과에 대한 쿼리를 실행하거나 다른 쿼리의 결과를 사용할 수 있음
ex) SELECT student,grade FROM test_scores WHERE grade > (SELECT AVG(grade) FROM test_scores)
괄호 안에 서브 쿼리가 먼저 작동함, 표의 평점 평균을 낸 결과를 활용해 그 평균보다 높은 학점의 학생을 선택함
서브 쿼리를 사용할 때 오퍼레이터 내에서 사용해도 됨
서브 쿼리로 같은 값을 도출할 수 있음
ex) SELECT student,grade FROM test_scores WHERE student IN (SELECT student FROM honor_roll_table)
-> 학생 이름 도출
서브 쿼리 도출 후, 학생 목록 내 학생들의 시험 성적표에서 학생과 점수가 선택될 것
- 복잡한 쿼리일 수록 길게 쓰기보다는 서브 쿼리를 활용하려는 것임
EXISTS
서브 쿼리에서 행의 존재를 테스트하는 데 사용됨
서브 쿼리가 EXISTS 함수 뒤 괄호에 입력되어, 어떤 행이 서브 쿼리로 도출되었는지 확인함
ex) SELECT column_name FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
ex2) SELECT title,rental_rate
FROM film
WHERE rental_rate > (SELECT AVG(rental_rate) FROM film)
-> 평균을 계산하기 위해 괄호 속의 서브 쿼리가 먼저 실행되었음, 그리고 나서 전체 쿼리가 실행됨
또 중요한 점은 본 특정 서브 쿼리인 평균 대여료가 단독 값으로 도출된다는 점임
그러니 비교 오퍼레이터로 등호나 부등호를 쓸 수 있음
하지만 서브 쿼리가 다양한 값으로 도출된다면 IN 오퍼레이터를 사용해야함
ex) SELECT inventory.film_id
FROM rental
INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
WHERE return_date BETWEEN '2005-05-29' AND '2005-05-30'
재고표에서 영화 이름을 불러오려는 것(임대표에는 안 나옴)
그리고 재고표와 임대표 모두에 있는 재고 아이디 행을 사용함(둘을 합치기 위해)
반납일을 기준으로 필터를 걸었음
제목을 알고 싶을때
ex2) SELECT film_id,title
FROM film
WHERE film_id IN
(SELECT inventory.film_id
FROM rental
INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
WHERE return_date BETWEEN '2005-05-29' AND '2005-05-30')
위 세줄을 적고 WHERE film_id 뒤에 IN을 적으면 서브 쿼리 내에서 값이 검색됨 -> 영화 이름, 제목 행 둘다 나옴(순서는 바뀔 수 있음)
마지막에 ORDER BY title하면 제목으로 정렬, film_id라 하면 이름으로 정렬되는데 괄호 안에 들어가지 않게 주의
ex3) SELECT film_id,title
FROM film
WHERE film_id IN
(SELECT inventory.film_id
FROM rental
INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
WHERE return_date BETWEEN '2005-05-29' AND '2005-05-30')
ORDER BY title
EXISTS 사용 예시
11달러 초과로 한 번 이상 지급한 고객의 이름과 성을 알고 싶을때 - EXISTS 오퍼레이터 활용하기
ex) 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) ---> (amount대신 p.amount도 가능)
고객표의 각 고객에 대해 서브 쿼리가 지급표를 확인해 한 번 이상의 지급을 했는지 확인함
기준에 맞게 한 번 이상의 지급을 한 특정 고객이 있고, 금액이 11을 초과했는지를 확인
반대의 경우를 찾고 싶을땐 11 이하의 금액을 지급한 고객을 찾기 위해선 EXISTS 앞에 NOT을 붙이면 됨
ex2) SELECT first_name,last_name
FROM customer AS c
WHERE NOT EXISTS
(SELECT * FROM payment as p
WHERE p.customer_id = c.customer_id
AND amount > 11)
셀프 조인
표가 자체에 합쳐져 있는 쿼리를 셀프 조인이라고 함.
셀프 조인은 같은 표 내 여러 열의 열 속 여러 값을 비교할 때 유용함(사용하는 경우가 표 구조 및 실제 해결하려는 문제와 연관이 많음)
셀프 조인은 같은 표의 두 복사본 합처럼 보일 수 있음
셀프 조인을 위한 특별 키워드가 없음
하지만, 같은 표를 사용하면서 셀프 조인을 사용한다면 표에 에일리어스를 사용하면 됨(그렇지 않으면, 셀프 조인 안의 표 이름이 애매해짐)
ex) SELECT tableA.col, tableB.col FROM table AS tableA JOIN table AS tableB ON tableA.some_col = tableB.other_col
주목해야 할 점은 표들의 이름임
FROM 뒤 table을 tableA, JOIN 뒤 table은 tableB로 지정함(같은 표를 두 이름으로 지정함) -> 셀프 조인 가능
- table, tableA, tableB라고 적힌 것은 모두 같은 표를 의미한다는 것을 기억해야함
예시Q : 같은 길이를 가진 영화 짝을 모두 찾아라
ex2) SELECT f1.title, f2.title, f1.length
FROM film AS f1
INNER JOIN film AS f2 ON
f1.film_id != f2.film_id
AND f1.length = f2.length
프라이머리 키, 기타 키
표에 있는 줄을 특별하게 지정하기 위해 사용하는 세로단을 프라이머리 키라고 함
DVD 데이터베이스에서 고객에게 null이 아닌 독특한 고객 아이디로 된 세로단이 있었는데 그것이 프라이머리 키임
독특하다는 것은 모든 칸이 구별 가능하고 null은 프라이머리 키에 뭐든 들어가야 함
기타 키는 다른 표의 프라이머리 키에 대해 레퍼런스를 주도록 규정되어 있음
기타 키를 포함하고 있는 표는 레퍼런싱 표나 차일드 표라고 부름
기타 키가 레퍼런스 하는 표는 레퍼런스 표나 페어런트 표라고 부름, 다른 표와 얼마나 많은 관계를 맺고 있는지에 따라 다양한 기타 키를 갖고 있을지가 결정됨
프라이머리 키와 기타 키를 보고 어떤 세로단을 선택해야 할 지 알 수 있음 -> 여러 표를 JOIN 할 때 고려해야 될 사항
표를 만들고 세로단을 규정할 때 제약 조건을 만들 수 있음 -> 규정하는 세로단을 프라이머리 키로 하거나 다른 표와의 기타 키 관계성을 설정하면 됨
Constraints
표에 있는 데이터 열에 적용되는 규칙을 제약조건이라 함, 표에 있는 모든 것에 제약을 걸 수가 있음
제약 조건을 사용하면 데이터베이스에 유효하지 않은 데이터가 쓰이지 않도록 방지할 수 있음
데이터베이스의 데이터에 대한 정확도와 신뢰도를 보장
제약 조건은 보통 두 가지 주요 카테고리로 나눠짐
특정 조건에 연결된 하나의 열에 있는 데이터만 제약하는 세로단 제약 조건이 있고, 개별 세로단이 아닌 전체 표에 적용되는 표 대상 제약 조건이 있음
제일 많이 사용되는 경우
- NOT NULL 제약 조건
본 제약 조건을 사용하면 칸에 null이 입력될 수 없음
본 세로단이 포함된 열에 데이터를 입력하려면 특정 값이 들어가야 함
ex) 고객 정보표를 만들 때 이메일 항목이 있는데 null 값이 입력되지 못하게 제약 조건을 걸면 고객의 이메일을 입력해야함
그렇지 않음 무효한 칸으로 인식해 표에 입력이 거부됨
-
UNIQUE 제약 조건
열에 있는 모든 값이 달라야 한다는 명령
고객 아이디 열에 본 제약 조건을 걸면 좋음 -> 고객 아이디는 다 달라서 고유해야 하기 때문
세로단의 모든 값이 구별되고 달라야 한다는 것 -
프라이머리 키와 기타 키 제약 조건
표를 제작할 때 실제로 두 키를 규정하는 방법이 제약 조건
프라이머리 키는 데이터베이스 표 자료나 각 행을 고유하게 식별하고 기타 키는 세로단이나 다른 표의 데이터에 제약 조건을 검
기타 키를 세팅하려면, 만든 다른 표와의 관계도 셋업 해줘야 함
행 관련 제약 조건 - CHECK, EXCLUSION
-
CHECK 제약 조건
행의 모든 값이 특정한 조건을 만족하도록 함
ex) '20보다 작을 것'과 같은 조건을 거는 것 -
EXCLUSION 제약 조건
특정 오퍼레이터를 사용한 특정 열이나 식에서 어떤 두 열이 비교될 때 모든 비교 값이 참으로 판명되지 않아야 한다는 조건임
- 전체 표에 적용할 수 있는 제약 조건
표에 부가적인 CHECK 제한을 걸 수 있음, 괄호 안에 조건을 적으면 됨 -> 전체 표에 데이터를 삽입하거나 업데이트할 때 조건을 적용할 수 있도록
REFERENCES 제한을 걸 수 있음
세로단의 값에 제한을 거는 것으로 다른 표 세로단에 존재해야 한다는 조건
UNIQUE 제한 -> 세로단을 UNIQUE로 보내는 것
표의 다양한 세로단에 적용되고 괄호 속 세로단에 저장된 값은 UNIQUE 하게 됨
한 세로단 안에서만이 아닌 다중 열에서 독특하게 되는 것임 -> 한 열에만 적용되는 것이 아니라 표 제한 조건에 속함
보통 한 표에 하나의 프라이머리 키 세로단이 있지만 다중 세로단도 가능 -> 하고 싶으면 표 제한 조건을 사용하면 됨
프라이머리 키와 함께 세로단 목록에 다중 세로단이 들어가면 됨
CREATE
CREATE TABLE table_name (column_name TYPE column_constraint, column_name TYPE column_constraint, table_constraint_table_constraint) INHERITS existing_table_name;
각 세로단을 분리하기 위해 쉼표 사용(세로단을 다룰 땐 꼭 심표 사용)
그 다음엔 원하는 제약 조건 사용
다른 표와 연관 관계가 있는 경우 그 표와 INHERITS를 사용
ex) CREATE TABLE table_name (column_name TYPE column_constraint, column_name TYPE column_constraint);
CREATE TABLE을 적은 후 표 이름을 정한다
먼저 할 것은 PK가 될 세로단을 결정하는 것임, 일반적으로 각 표는 PK를 가짐
그 다음에 유형을 결정함
PK를 고려할 때 유형은 항상 SERIAL임, SERIAL 데이터 유형에 주목해야함, 특별한 프로퍼티들이 있음
시퀀스는 특별한 종류의 데이터베이스 오브젝트, 정수의 시퀀스를 발생시킴, 표의 PK 세로단에서 자주 쓰임
SERIAL은 시퀀스 오브젝트를 만들고 세로단의 디폴트 값으로써 시퀀스에 의해 발생한 다음 값을 세팅함
표에 더 많은 데이터를 삽입할수록 고유한 플레이어 아이디를 써야 한다고 생각할 수 있는데 그 대신 SERIAL이 대신해줌
SERIAL의 장점은 만약 줄이 제거되어도 SERIAL 데이터 유형의 세로단에는 영향이 없음
SERIAL의 모든 것을 재포맷하지 않음, 적용하지 않는 대신 시퀀스에서 해당 줄이 삭제되었다는 것을 표시해줌
ex) CREATE TABLE job(
job_id SERIAL PRIMARY KEY,
job_name VARCHAR(200) UNIQUE NOT NULL
)
ex2 )CREATE TABLE account_job(
user_id INTEGER REFERENCES account(user_id),
job_id INTEGER REFERENCES job(job_id),
hire_date TIMESTAMP
)
INSERT
표에 줄을 삽입할 수 있는 기능
ex) INSERT INTO table(column1, column2,...) VALUES(value1, value2,...);
다른 표의 값을 삽입할 수 있는 구문
ex) INSERT INTO talbe(column1,column2,...) SELECT column1,column2,... FROM another_table WHERE condition;
- 기억해야 할 점 : 삽입되는 줄 값이 제약 조건을 포함해 표와 일치해야 한다는 것임
ex) 제약 조건에 NOT NULL이 있다면 삽입되는 값도 빈칸이면 안 됨 -> 아니면 에러 발생
다른 표에서 정보를 가져와 현재 사용하는 표에 삽입할 때 세로단끼리 데이터 유형 등이 동일한지 확인해야 함
프라이머리 키와 연관된 SERIAL 세로단에는 값이 없어도 됨
기타 키 정보 제공을 위반하면 에러 마주함
NOT NULL인 정보를 제공햐야 하듯, 다른 표에 존재해야 한다는 기타 키 제약 조건이 있는지 확인 필요
제약 조건은 제약 조건을 위반하면 정보 삽입을 막음
UPDATE
표의 값을 바꿀 수 있도록 해주는 키워드
ex) UPDATE table SET column1 = value1, column2 = value2,... WHERE condition;
ex2) UPDATE account SET last_login = CURRENT_TIMESTAMP WHERE last_login IS NULL; -> 마지막 접속 일 값이 없으면 값을 현 타임스탬프로 업데이트하는 명령
WHERE 조건 없이 모든 것을 리셋할 수 있음
모든 지난 접속 기록을 업데이트 하고 싶을 때 -> UPDATE account SET last_login = CURRENT_TIMESTAMP라고 해도됨
다른 세로단에 기초해 세팅할 수도 있음
작성 일자 세로단이 있으니 UPDATE account하고 SET last_login = created_on이라 해도 됨(WHERE 조건 붙여도 됨)
다른 표의 값을 사용할 수도 있음(UPDATE join이라 불리지만 join은 쓰지 않음)
일반 구문에서 쓰는 법은 표 A와 같이 업데이트할 표를 고르고 쓰는 것임
ex) UPDATE TableA SET original_col = TableB.new_col FROM tableB WHERE tableA.id = TableB.id
영향을 받은 특정 세로단을 확인하고 싶으면 명령을 내리면 됨
RETURNING 뒤에 보고 싶은 세로단을 적음
ex2) UPDATE account SET last_login = created_on RETURNING account_id, last_login
ex3) UPDATE account_job
SET hire_date = account.created_on
FROM account
WHERE account_job.user_id = account.user_id
표 A를 UPDATE하기 위해 업데이트하려는 것을 SET하고, FROM 표 B라고 명시한 뒤 무엇과 맞춰야 하는지 WHERE로 표시함
그래서 UPDATE JOIN이라고 부름
RETURNING
ex) UPDATE account
SET last_login = CURRENT_TIMESTAMP
RETURNING email,created_on,last_login
결과를 보기 위해 SELECT를 이용해 다시 확인할 필요 X
DELETE
표에서 줄을 삭제할 때 DELETE를 사용
ex) DELETE FROM table WHERE row_id = 1
다른 표에 존재하는지 여부에 따라 줄을 삭제할 수 있음
ex2) DELETE FROM tableA USING tableB WHERE tableA.id = TableB.id -> 두 표에서 값이 같다면 표 A에서 그 줄을 삭제할 것(DELETE JOIN과 같음)
ex3) DELETE FROM job
WHERE job_name = 'Cowboy'
RETURNING job_id,job_name
Cowboy 줄을 지움
ALTER Table
이미 존재하는 표 구조를 바꾸는 방법
더하기,드롭,세로단 이름 바꾸기,세로단 데이터 유형 바꾸기 등으로 다양하게 쓸 수 있음
세로단의 디폴트 값 세팅, CHECK 제약 조건 더하기, 표 이름 바꾸기도 가능함
ex) ALTER TABLE table_name action
표에 세로단을 더하고 싶을 때
ex) ALTER TABLE table_name ADD COLUMN new_col TYPE
세로단을 없앨 때
ex) ALTER TABLE table_name DROP COLUMN col_name
이미 존재하는 세로단의 제약 조건을 바꿀 수 있음
ex) ALTER TABLE table_name ALTER COLUMN col_name SET DEFAULT value -> ALTERTABLE 표 이름이나 ALTER COLUMN 세로단 이름을 활용하거나, DEFAULT 값을 세팅하거나 드롭할 수 있음, NOT NULL 제한 조건도 제거하거나 더할 수 있음
ex2) ALTER TABLE table_name ALTER COLUMN SET NOT NULL
제약 조건을 더할 수 있음
ex) ALTER TABLE table_name ALTER COLUMN col_name ADD CONSTRAINT constraint_name
세로단 이름을 바꾸고 싶을 때
ex) ALTER TABLE new_info
RENAME COLUMN person TO people
INSERT INTO new_info(title)
VALUES
('some new title') -> 실행하면 people의 NOT NULL 조건에 어긋났다는 에러 발생
두 가지 옵션
1. title 뒤에 people을 추가하고 값도 추가하거나, ALTER를 사용해 NOT NULL 조건을 없앨 수 있음
ex) ALTER TABLE new_info
ALTER COLUMN people DROP NOT NULL -> 조건을 빼려면 드롭, NOT NULL 대신 UNIQUE 등 어떤 조건도 올 수 있음
DROP Table
드롭을 활용하면 표에서 세로단을 완전히 삭제할 수 있음, 그럼 본 프로그램은 해당 세로단에 연결된 인덱스와 제약 조건도 자동으로 삭제함
CHECK, NOT NULL, UNIQUE 제약 조건 등도 삭제(해당 세로단과 관련된 모든 것)
그러나 비독립적인 다른 지점에 사용되는 세로단을 제거하지는 않음(보기, 유도 장치, 저장 절차 등에 사용할 때)
보기, 유도 장치, 저장 절차에 연결된 비독립적인 세로단을 삭제하고 싶다면 CASCADE 명령을 추가하면 됨
DROP을 위한 예시(일반 구문)
ex) ALTER TABLE table_name DROP COLUMN col_name
연결 고리를 끊으려면
ex) ALTER TABLE table_name DROP COLUMN col_name CASCADE
존재하지 않는 세로단을 드롭하려 하면 프로그램 에러 발생 -> 에러를 방지하기 위해 세로단 이름 전에 IF EXISTS 키워드 사용
ex) ALTER TABLE table_name DROP COLUMN IF EXISTS col_name -> 표에 해당 세로단이 있을 때만 삭제됨
여러 세로단을 드롭하려면 다양한 세로단을 입력하면 됨
ex) ALTER TABLE table_name DROP COLUMN col_one, DROP COLUMN col_two
CHECK 제약 조건
CHECK로 특정 조건에 맞춤화한 제약 조건을 쓸 수 있음
ex) example이라는 표를 만든다 하고 PK로 ex_id라는 세로단과 age세로단을 둠, 나이가 21세를 초과해야 한다고 가정, 데이터 유형은 SMALLINT로 두고 CHECK 제약 조건을 덧붙임, 그 다음엔 특정 조건을 적으면 됨
ex) CREATE TABLE example(ex_id SERIAL PRIMARY KEY, age SMALLINT CHECK (age>21), parent_age SMALLINT CHECK(parent_age >age));
다른 세로단과 연결된 조건을 세팅할 수도 있음
CHECK 조건으로 부모의 나이 세로단의 값은 나이 세로단의 값보다 크도록 설정, 21세보다 어린 사람의 나이를 입력하거나 부모 나이가 자기 나이보다 적은 값을 입력하라고 하면 에러 발생
