SQL
SQL이란??
- rdbms에 보관된 데이터를 관리하기 위해 설계된 언어
- structed data(정형 데이터)를 처리할 때 유용하다.
- IBM에서 SEQUEL이라는 관계형 데이터베이스 조작용 언어를 기반으로 만들어졌다.
- sql은 사실 프로그래밍 언어는 아닙니다. 왜냐하면 flow를 control하지 않기 때문이다.
- 단지, 테이블 내의 데이터를 식별하거나 선택하는 논리 방정식
SQL 실행순서
- SQL문 실행
- SQL Parsing
- SQL이 메모리에 있는지 확인
- 있으면 바로 실행
- 없으면: 최적화 > Row 소스 생성 > 실행
DML
- DML은 Data Manipulation Language로 데이터 조작에 관한 명령어
- data 자체를 조작하는 명령어
- INSERT, UPDATE, DELETE
- SELECT도 오라클 SQL 공식문서를 보면 DML로 분류하고 있다. 그러나, SELECT는 실제로 조작은 하지 않으나 SQL에서 가장 핵심이고 복잡한 구문이기 때문에 DQL(Database Query Language)로 분류하기도 한다.
DDL
- DDL은 Data Definition Language로 구조를 만들거나, 구조 변경, 삭제 등 데이터 구조에 관한 명령어
- 이 때, 뭘 정의하는거냐면
객체를 정의하는 것이다. - CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT
DCL
- DCL은 Data Control Language로 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 받는 명령어
- GRANT, REVOKE
참조 무결성
- 관계가 맺어진 테이블에서 관계를 유지하는것
- 참조하는 테이블은 참조할 수 없는 외래키를 가져서는 안된다.
- 참조되는 테이블은 외래키가 존재하는한 데이터를 삭제, 변경할 수 없다.
CASCADE 설정
-
참조 무결성때문에 PK값을 수정하거나 레코드를 삭제할 수 없다.
-
방법이 있다면 FK의 참조값을 null로 설정하여 참조를 끊는 수 밖에 없다.
-
상당히 비효율적이다.
-
이 때, CASCADE로 해결이 가능하다.
-
외래키 제약 조건에
CASCADE키워드를 포함시키면 된다.
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
CustomerID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
ON DELETE CASCADE
);
VIEW
-
원래라면 객체로 등록할 수 없는 SELECT 명령을 객체로서 이름을 붙여 관리할 수 있도록 한 것
-
객체이기 때문에 DDL로 작성, 삭제한다.
-
뷰의 작성
CREATE VIEW 뷰명 AS SELECT명령; -
뷰의 삭제
DROP VIEW 뷰명;
-
SELECT 절의 처리순서
- WHERE > GROUP BY > HAVING > SELECT > ORDER BY
- 따라서, 집계함수를 사용할 경우에 HAVING 구로 검색 조건을 지정한다.
SELECT ~ FOR UPDATE 구문
-
트랜잭션 처리 중에 지정한 레코드를 락을 하는 구문입니다.
-
행단위로 동시 접근을 제어
-
항공편 예약 시스템 case
BEGIN; -- 특정 항공편 좌석 조회 및 잠금 SELECT * FROM Flights WHERE FlightID = 1234 AND SeatNumber = '12A' FOR UPDATE; -- 예약 가능 여부 확인 후 예약 처리 UPDATE Flights SET IsBooked = 1 WHERE FlightID = 1234 AND SeatNumber = '12A'; COMMIT; -
한 사람이 예약을 하는동안 다른 사람이 동일한 좌석을 예약하는 것을 막아준다.
-
즉, 먼저 락을 구한 커넥션은 실행을 지속하고 락을 구하지 못한 커넥션은 트랜잭션이 종료되기 전까지 대기한다.
-
그 락을 구한 커넥션이 커밋을 하면 그 때 실행이 된다.
참고: DB 동시 수정 막기 - select … for update 이용
by 최범균
GROUP BY절
-
SELECT 문에서 여러 개의 열을 그룹핑하기 위한 절
SELECT expression1, expression2, ... expression_n, aggregate_function (expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n;- aggregate_function: SUM, COUNT, MIN, MAX, AVG 같은 집계함수를 말한다.
ORDER BY절
-
어떤 기준으로 정렬을 시킬 때 사용되는 절
SELECT expressions FROM tables [WHERE conditions] ORDER BY expression [ ASC | DESC ]; -
ASC | DESC: ASC는 오름차순, DESC는 내림차순을 얘기한다.
INNER JOIN과 OUTER JOIN의 차이점
- 모든 JOIN의 시작은 카르티지안 프로덕트에서 시작
- INNER JOIN은 그 중에서 교집합 영역
- OUTER JOIN은 그 중에서 교집합 + A - B(차집합)
LEFT OUTER JOIN, RIGHT OUTER JOIN
- 위의 차집합이 A - B인지 B - A인지
CROSS JOIN
- 위의 카르티지안 프로덕트가 CROSS JOIN의 결과
- 가능한 모든 경우의 조합을 생성한다.
서브쿼리
- 서브쿼리는 SQL명령문 안에 지정하는 하부 SELECT 명령으로 괄호로 묶어 지정한다.
- 주로 WHERE 구에서 사용한다.
- 스칼라 서브쿼리: 하나의 값을 반환하는 걸 스칼라 서브쿼리라 하고, 이처럼 하나의 값을 반환할 때 서브 쿼리를 자주 쓴다.
- 상관 서브쿼리: 외부 쿼리의 데이터를 참조하는 서브쿼리
DROP, TRUNCATE, DELETE
- DROP: 테이블 자체를 아예 통으로 삭제
- TRUCATE: 테이블 구조는 남아있고 모든 데이터 삭제, 트랜잭션 O, But 롤백이 불가능
- DELETE: WHERE절을 사용하여 특정 행을 삭제, 트랜잭션 O, 롤백 가능
DISTINCT
- 중복행을 제거한다.
- SELECT 구문에서 쓰인다.
SQL Injection 공격
-
입력할 수 있는 폼(ex. 로그인 비밀번호)에 조작된 질의문을 넣어 정보를 해킹하는 걸 뜻한다.
-
비밀번호 입력란에
123 or '1'='1'을 입력하면 true로 평가('1' = '1' 이 true)가 되서 로그인이 가능해진다. -
원래 등록되어 있던 아이디가 user라고 하자. 위와 비슷한 논리로 아이디칸에
user--라고 입력하면where절에서 user 뒤에 부분이 --로 인해 주석처리가 되어서 비밀번호가 입력을 안해도 되버린다. -
Query String을 쓰면 안되고 Parameter Binding을 써야한다.
mybatis의 경우
${}(String Interpolation)를 쓸 때
- 사용자의 입력을 그대로 쿼리 문자열에 삽입
- 외부의 데이터가 검증없이 바로 쿼리에 입력되어 보안에 취약
#{}(Parameter Substitution)을 쓸 때
- 파라미터 치환을 위해 사용
- MyBatis에 의해 PreparedStatement의 파라미터로 처리된다.
- 즉, 미리 컴파일된 SQL문을 사용하기 때문에 보다 안전하다.
- 예를 들어, 위의 '1'='1' 과 같은 SQL 인젝션코드가 포함되더라도 단순하게 문자열로 처리되어 쿼리 실행에 영향을 전혀 안주기 때문에 sql injection 공격을 막을 수 있다.
참고1: [10분 테코톡] 🙋♂️로비의 SQL 인젝션
참고2: SQL injection 공격 by 코딩애플
SQL 안티패턴
-
패스워드를 생으로 넣는 저장
패스워드에 salting을 하여 해쉬 값을 쓰게하여 해킹을 막도록 한다. -
무분별한 SELECT *
필요없는 데이터까지 불러오게 하여 메모리 사용량을 불필요하게 늘리게 된다. 따라서, 내가 원하는 데이터만 명시를 해주는 것이 좋다. -
PK AUTOINCREAMENT
기본키는 각 행을 식별하기 위한 것이기 때문에 연속할 필요가 없다.
