IN 연산자
SELECT *
FROM employees
WHERE department IN ('Sales', 'Marketing', 'HR');employees 테이블에서 department 의 값이 위의 셋 중하나 인 것을 출력
IN 연산자 & 서브쿼리
SELECT title
FROM songs
WHERE artist IN (
SELECT name
FROM artists
WHERE genre = 'Pop'
);서브쿼리 ( ) 부터 시작
artists 테이블에서 genre 가 pop 인 아티스트의 이름을 선택
외부쿼리 : songs 테이블에서 해당 아티스트의(해당 값을 가지고 있는) 노래 제목을 선택
LIKE 연산자
집계 쿼리
별칭 붙이기 : AS ~~
HAVING
: 각 열의 개별 값이 아니라 그룹으로 묶인 결과에 대해서 조건을 적용
그룹으로 묶인 행에는 SUM, MIN, MAX, AVG 등 일반적으로 작동하는 어떤 집계 함수도 적용
SELECT author, AVG(words) AS avg_words
FROM books
GROUP BY author
HAVING avg_words >= 150000;AS 를 사용하여 AVG(words) 의 별칭을 avg_words 로 설정
books 테이블에서 데이터를 가져옴
GROUP BY 를 사용하여 author 열을 기준으로 그룹화
HAVING 을 사용하여 조건에 맞는 데이터 선택, HAVING 절은 GROUP BY 결과에 대한 조건을 지정하는데 사용됨
CASE
SELECT count(*) AS student_count,
CASE
WHEN number_grade > 90 THEN 'A'
WHEN number_grade > 80 THEN 'B'
WHEN number_grade > 70 THEN 'C'
ELSE 'F'
END AS letter_grade
FROM student_grades
GROUP BY letter_grade;
student_count | letter_grade
--------------|--------------
2 | A
2 | B
1 | C
1 | F- count(*) : 그룹의 학생 수 계산
- CASE : if 나 switch 같은 조건문
- AS : 별칭 설정
- GROUP BY : 그룹화 하여 등급별 학생 수 계산
INNER JOIN
SELECT persons.name AS person_name, hobbies.name AS hobbies
FROM persons
JOIN hobbies
ON persons.id = hobbies.person_id
WHERE persons.name = 'Bobby McBobbyFace';
id | name
1 | Bobby McBobbyFace
2 | Alice Wonderland
id | person_id | name
1 | 1 | Swimming
2 | 1 | Reading
3 | 2 | Hiking
persons.name | hobbies.name
--------------------|--------------
Bobby McBobbyFace | Swimming
Bobby McBobbyFace | Reading- FROM persons 주체가 될 테이블 하나 정하기
- INNER JOIN 할 테이블
- ON : 조건 설정
- 결합한 테이블에서 출력 조건 설정
LEFT OUTER JOIN
SELECT customers.name, customers.email,
sum(orders.price) AS total_spent
FROM customers
LEFT OUTER JOIN orders
ON customers.id = orders.customer_id
GROUP BY customers.id
ORDER BY total_spent DESC;
id | name | email
1 | Alice | alice@example.com
2 | Bob | bob@example.com
3 | Charlie | charlie@example.com
id | customer_id | price
1 | 1 | 100.00
2 | 1 | 150.00
3 | 2 | 200.00
4 | 3 | 50.00
5 | 3 | 75.00
name | email | total_spent
---------|--------------------|-------------
Bob | bob@example.com | 200.00
Alice | alice@example.com | 250.00
Charlie | charlie@example.com| 125.00- 동작방식
- LEFT 왼쪽 테이블에서 모든 행을 가져오라고 명령
- OUTER 가져오려는 오른쪽 테이블에 일치하는 항목이 없어도 행을 유지해야한다고 명령
- ON 조인 조건
- GROUP BY 고객 별로 그룹화, 행에서 고유할 가능성이 가장 높은 열에서는 항상 사용
- ORDER BY ~ DESC; 내림차순 정렬
SELF JOIN
SELECT released.title AS released_title,
sequel.title AS sequel_title
FROM movies AS released
LEFT OUTER JOIN movies AS sequel
ON released.sequel_id = sequel.id;
id | title | sequel_id
1 | The Matrix | 2
2 | The Matrix Reloaded | 3
3 | The Matrix Revolutions | NULL
4 | Inception | NULL
released_title | sequel_title
---------------------|-----------------------
The Matrix | The Matrix Reloaded
The Matrix Reloaded | The Matrix Revolutions
The Matrix Revolutions | NULL
Inception | NULL- 두개의 별칭을 사용하여 각 영화와 그 후속작이 있는 테이블
- 조인 조건으로 현재의 released.id 와 후속작 sequel.id 를 연결
- 다음 후속작이 없는 경우 NULL (left outer join)
UPDATE
UPDATE documents
SET author = 'Jackie Draper'
WHERE author = 'Jackie Paper';- documents 테이블에서 author 컬럼의 값을 찾아서 수정
DELETE
DELETE FROM documents
WHERE title = "Things I'm Afraid Of";- documents 테이블에서 title 의 컬럼의 값을 찾아서 삭제
- 참고로 실제로 삭제하는 일이 드물고 '삭제됨' 이라는 행을 하나 추가해서 true, false 로 표기하는 경우가 많음
ALTER
ALTER TABLE clothes ADD price INTEGER;- 이미 생성되어 있는 테이블이 price 라는 컬럼을 추가
INSERT INTO clothes VALUES (4, "pants", "mydesign", 40);- 새로운 행를 추가
Drop
잘가고
안전하게 SQL 사용하기
잘못된 업데이트 / 삭제 방지
update 문을 실행하기 전에 올바른 열과 행을 업데이트 하는지 확인하기
// 아래의 sql 을 실행하기 전에
UPDATE users SET deleted = true WHERE id = 1;
// 먼저 확인해보기
SELECT id, deleted FROM users WHERE id = 1;
업데이트를 하기로 결정하면 LIMIT 을 사용해서 실수로 너무 많은 행을 업데이트 하지 않도록 조정
// 업데이트의 경우
UPDATE users SET deleted = true WHERE id = 1 LIMIT 1;
// 삭제의 경우
DELETE users WHERE id = 1 LIMIT 1;
트랜잭션
db를 어떤 식으로든 변경하는 SQL 명령을 내리면 트랜잭션이 시작됨
트랜잭션은 단일 논리적 작업(은행 거래..)으로 처리되는 일련의 작업, 트랜잭션은 안정적으로 처리되도록하기 위해
ACID원칙을 준수해야함.
ACID - 오류, 정전 및 기타 사고에도 데이터 유효성을 보장하기 위한 db 트랜잭션의 속성 집합
- 원자성 : 트랜잭션이 완전히 성공하거나 완전히 실패하도록 되어야함
- 일관성 : 트랜잭션이 db를 하나의 일관된 상태에서 다른 일관된 상태로만 가져올 수 있도록 보장, 불변성을 보존
- 고립성 : 트랜잭션의 동시 실행이 트랜잭션이 순차적으로 실행된 경우와 동일한 상태로 db를 유지하도록 보장
- 내구성 : 트랜잭션이 커밋되면 시스템 장애가 발생하더라도 커밋된 상태로 유지 (비휘발성)
한 트랜잭션 안에 여러 명령을 래핑하는법
BEGIN TRANSACTION;
UPDATE people SET husband = "Winston" WHERE user_id = 1;
UPDATE people SET wife = "Winnefer" WHERE user_id = 2;
COMMIT;- 에러가 생겨서 db가 두 명령을 실행하지 못하면 원래대로 롤백됨
- 동일한 데이터에서 작동하지 않게 하려고 할때도 사용
백업 만들기
주기적으로 데이터를 백업해 두는게 좋음, 만약 날라가서 없는 것 보단 예전거라도 있는게 훨 나음
복제
db의 사본을 저장, 하여 예측불가한 상황에 놓였을 때 바로 사용가능할수 있도록 하는 대비책 but 성능저하 생각해얗마
권한 부여
db 전체에 대한 액세서 권한은 몇명정도만 유지