SQL(Structured Query Language)

SQL (2023.05.22 - 23)

 

---

[ 주요 개념]

SQL 소개

• 데이터베이스 DB : 데이터를 보관, 관리 목적으로 만든 시스템
• SQL : data 관리에 쓰이는 기능 중 하나

 

데이터베이스의 필요성

• 데이터를 저장하고 활용하기 위해 필요하다

1. In-Memory → 데이터를 영속적으로 유지, 보관하기 위해
2. File I/O → 매번 파일을 읽어와야하고, 여러 파일을 다뤄야 하는 등 복잡하기 때문에 DB 사용

 

SQL 소개

SQL = Structured Query Language

쿼리(Query) : 질의문

SQL : DB에 쿼리를 보내 원하는 데이터만 가져올 수 있게 해주는 DB 용 프로그래밍 언어

NoSQL : 데이터 구조가 고정되어 있지 않은 데이터베이스

 

SQL 종류

• DDL : CREATE, ALTER, DROP, RENAME
• DML : INSERT, UPDATE, DELETE
• DCL : GRANT, REVOKE
• DQL : SELECT → DML 안에 포함
• TCL : COMMIT, ROLLBACK

 

데이터베이스 설계

관계형 데이터베이스(Relational Database)

• 데이터 : 각 항목에 저장되는 값
• 테이블 : 행과 열로 구성되어 있는 구조화된 데이터
• 필드(컬럼) : 테이블의 열
• 레코드(튜플) : 테이블의 한 행에 저장된 정보
• 키 : 테이블의 각 레코드를 구분할 수 있는 값. 각 레코드마다 고윳값 이어야 하고 PK, FK 등 존재

 

관계 종류

• 1 : 1 관계
• 테이블의 레코드 하나당 다른 테이블의 한 레코드와 연결되어 있는 경우
• 1 : N 관계
• 테이블의 레코드 하나당 여러 개의 레코드와 연결되어 있는 경우
• M : N 관계
• 여러 개의 레코드가 여러 개의 레코드를 가지는 관계 → 조인 테이블을 만들어서 관리
• 자기 참조 관계
• 테이블 내에서 관계가 필요한 경우 ex. 추천인

 

기본 쿼리문

• SELECT - 데이터셋에 포함될 특성들 특정
• FROM - 결과를 도출해 낼 데이터베이스 테이블 명시
• WHERE - (선택적으로) 필터 역할을 하는 쿼리문
• AND, OR, NOT
• ORDER BY - 결과 정렬 (default ASC: 오름차순, DESC: 내림차순)
• LiMIT - 돌려받는 데이터 결과 개수 지정
• DISTINCT - 유니크한 값들을 받고 싶을 때 SELECT 뒤에 사용(중복 X)
• INSERT INTO
• NULL VALUES
• UPDATE
• DELETE
• COUNT
• LIKE
• WILDCARDS
• ALIASES
• JOIN
  • INNER JOIN - 서로 공통된 부분으로만 연결
  • LEFT JOIN - 왼쪽에 있는 테이블 기준 연결
  • RIGHT JOIN - 오른쪽에 있는 테이블 기준 연결
  • FULL OUTER JOIN - 전체 연결
• GROUP BY

 

데이터베이스 관련

• SQL CREATE DB
• SQL DROP DB
• SQL CREATE TABLE
• SQL DROP TABLE
• SQL ALTER TABLE
• SQL NOT NULL
• SQL UNIQUE
• SQL PRIMARY KEY
• SQL FOREIGN KEY
• SQL DEFAULT
• SQL AUTO INCREMENT
• SQL DATES

 

---

DB (데이터베이스)

→ 전자적으로 저장되고 사용되는 관련 있는 데이터들의 조직화된 집합

→ 관련있는 데이터 : 같은 출처, 목적, 서비스 아래서 생성되는 데이터들

 

DBMS(Data Base Management Systems)

→ 사용자에게 DB를 정의하고 만들고 관리하는 기능을 제공하는 SW 시스템, DB를 정의하다 보면 부가적인 데이터 발생

ex) Postgre SQL, MYSQL, Oracle, SQLServer

 

DBSystem : database + DBMS + 연관된 applications

⇒ 줄여서 database라고도 부름

⇒ 문맥에 따라서 잘 파악해야 한다.

 

트랜잭션(Transaction)

→ 일련의 작업들에 대한 연속 처리 단위

→ DB 상태를 변화시키는 작업 모음 (DB 상태: DB 내용)

→ 주로 DML(INSERT, DELETE, UPDATE)과 사용

⇒ 여러 개의 작업들을 하나의 실행 유닛으로 묶어준 것

⇒ 성공/실패 두 결과만 가진다 → 끝까지 실행되어야 완료됨

 

COMMIT

→ 트랜잭션 실행 후 확정 신호를 보내 DB에 반영하는 것

CREATE TABLE user(
			id VARCHAR(10) PRIMARY KEY,
			name VARCHAR(10)
			);

INSERT INTO user VALUES ('id1', 'user1');
INSERT INTO user VALUES ('id2', 'user2');

COMMIT;

COMMIT을 안 하면 반영되지 않음

 

ROLLBACK

→ 수행한 내용 취소

INSERT INTO user VALUES ('id3', 'user3');
ROLLBACK;

 

ACID = Atomicity, Consistency, Isolation, Durability

• Atomicity(원자성)
  • 하나의 트랜잭션을 구성하는 작업들은 전부 성공 또는 전부 실패해야 한다.
  • 트랜잭션에 묶인 과정이 모두 성공적으로 진행되거나 하나라도 실패하면 실패하게 만드는 것이 원자성의 핵심
  ⇒ ALL OR NOT
• Consistency(일관성)
  • 하나의 트랜잭션 이전과 이후 DB 상태는 이전과 같이 유효해야 한다.
  • DB의 제약이나 규칙에 의거한 DB여야 한다는 의미

  CREATE TABLE user (
  		id VARCHAR(10) PRIMARY KEY,
  		value VARCHAR(10)
  		);

  ⇒ 만약 id가 없는 새로운 고객을 추가하는 쿼리를 입력하면 Consistency에 위반됨
• id는 PK이기 때문에 있어야 함
• Isolation(독립성/고립성)
  • 하나의 트랜잭션이 다른 트랜잭션과 독립되어야 한다.
  • 동시에 여러 개의 트랜잭션이 수행될 때, 각 트랜잭션은 고립되어 있어 연속으로 실행된 것과 같은 결과를 낸다.
• Durability(지속성)
  • 하나의 트랜잭션이 성공적으로 수행되어 commit 되었다면 해당 트랜잭션에 대한 로그가 남고 오류가 발생해도 해당 기록은 영구적이어야 한다.
  • ↔️ 트랜잭션 도중에 오류가 발생하여 종료되면 원자성에 의해 실패로 돌아가고 이전 상태로 돌아간다.

 

SQL 내장함수

• GROUP BY : 묶어서 조회(기준칼럼, 집계함수 필요)
• HAVING : GROUP BY로 조회한 결과에 대한 필터

 

집계함수

• COUNT() : 개수 카운트
• SUM() : 합
• AVG() : 평균
• MAX(), MIN() : 최대 값, 최소 값

 

SELECT 문 실행 순서

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

SELECT CustomerId, AVG(Total)
FROM invoices
WHERE CustomerId >= 10
GROUP BY CustomerId
HAVING SUM(Total) >= 30
ORDER BY 2

 

CASE 문 (→ SQL IF 문)

SELECT CASE
			WHEN CustomerId <= 25 THEN 'GROUP 1'
			WHEN CustomerId <= 50 THEN 'GROUP 2'
			ELSE 'GROUP 3'
		END
	FROM customers

 

SUBQUERY

→ 쿼리문 작성 시 다른 쿼리문 포함. 소괄호로 감싼다.

SELECT CustomerId, CustomerId = (SELECT CustomerId FROM customers WHERE CustomerId = 2)
FROM customers
WHERE CustomerId < 6

→ 변수, 테이블, 칼럼처럼 사용됨

→ JOIN으로 쓸 수 있는 경우가 많고, JOIN으로 쓸 수 있으면 JOIN이 더 깔끔하다.

 

IN, NOT IN

→ 특정한 값들이 서브쿼리에 있는지 확인할 수 있다.

SELECT *
FROM customers
WHERE CustomerId IN (SELECT CustomerId FROM customers WHERE CustomerId < 10)

서브쿼리에서는 'CustomerId'가 10 이하인 데이터를 돌려주기 때문에 최종 조회된 데이터 또한 'CustomerId' 가 10 이하

만약 NOT IN을 사용하게 된다면 서브쿼리에서 조회된 10 미만을 제외한 레코드를 조회하게 된다.

 

FROM

→ 서브쿼리를 FROM 에도 사용할 수 있다.

SELECT *
FROM (
	SELECT CustomerId
	FROM customers
	WHERE CustomerId < 10
	)