📔SQL 첫걸음
SQL 개념을 간단하게 훑으며 메모할만한 부분을 적습니다.
Last updated
SQL 개념을 간단하게 훑으며 메모할만한 부분을 적습니다.
Last updated
젤 참고할만한건 역시 내부처리 순서다.
내부처리 순서
FROM, JOIN구-> WHERE 구 -> GROUP BY 구 -> HAVING 구 -> SELECT 구 -> DISTINCT구 -> ORDER BY 구 -> LIMIT / OFFSET 구
자주 쓸만한 패턴도 메모메모.
SELECT name, COUNT(name), SUM(quantity) FROM sample1 GROUP BY name ORDER BY SUM(quantity) DESC;
SELECT * FROM sample21;
: sample21 테이블 내의 모든 열을 불러온다.
메모
예약어와 데이터베이스 객체명은 대소문자를 구별하지 않는다.
데이터는 자료형으로 분류할 수 있다.
열은 하나의 자료형만 가질 수 있다.
NULL은 데이터가 들어있지 않은 것을 의미하는 특별한 값이다.
DESC sample21;
: sample21 테이블 내에 어떤 열이 정의되어 있는지 확인한다.
문자열 형에서는 열의 최대 길이를 지정해야한다. 가령 CHAR(10)으로 자료형을 지정했다면 최대 10문자로 된 문자열을 저장할 수 있다. 가변길이로 문자열을 저장하려면 VARCHAR 자료형을 쓰자.
SELECT 구에서 열 지정하기
SELECT 열1, 열2 FROM 테이블명;
WHERE 구에서 행 지정하기
SELECT 열 FROM 테이블명 WHERE 조건식;
조건식에 일치하는 행이 없으면 아무것도 반환되지 않는다.
SQL에서는 구의 순서가 정해져있다. 가령 WHERE문 뒤에 FROM문을 적으면 에러가 난다.
조건식은 참 또는 거짓의 진리값을 반환하는 식으로 비교 연산자를 사용해 표현한다.
조건식에 문자열 리터럴을 넣을 때는 홀따옴표로 둘러싸야한다.
조건식에 날짜시간형일 때도 홀따옴표로 둘러싼다. 이 때 연원일은 하이픈(-)으로, 시각은 시분초를 콜론(:)으로 표기한다.
NULL 값은 '=' 연산자로 검색할 수 없다. 대신 IS NULL을 사용한다. NULL이 아닌 값을 검색하고 싶다면 IS NOT NULL을 사용하자. ex) SELECT * FROM sample21 WHERE day IS NULL;
솔직히 상식적으로 조건문에 AND, OR를 쓰면된다.
하지만 아래 경우는 주의하자.
숫자만 쓰면 참이다. 아래처럼 쓰면 '2'는 항상 참이므로 모든 행을 불러온다. no =1 OR no = 2라고 써야 의도한 결과가 나올 것이다.
SELECT * FROM sample24 WHERE no = 1 OR 2;
연산자 우선순위를 주의하자. AND가 OR보다 높다.
NOT 연산자는 오른쪽에만 항목을 지정하는 '단항 연산자'이다. 아래처럼 쓴다. SELECT * FROM sample24 WHERE NOT(a<>0 OR b<>0);
= 연산자는 열 값이 완전히 일치할 때 참이 된다. 반면 LIKE 술어는 열 값이 부분적으로 일치하는 경우에도 참이 된다.
검색에 쓰이는 와일드카드 aka 메타문자는 아래와 간다. 메타문자를 패턴 검색하려면 이스케이프 문자\를 쓰자.
%: 임의의 문자열과 매칭된다. 빈 문자열과도 매칭된다.
_: 임의의 문자 하나와 매칭된다.
문자열 상수 '의 이스케이프는 '' 으로 쓴다.
더 복잡한거 검색하려면 정규표현식을 활용하자.
오름차순 정렬: SELECT * FROM sample31 ORDER BY address; orSELECT * FROM sample31 ORDER BY address ASC;
내림차순 정렬: SELECT * FROM sample31 ORDER BY address DESC;
문자열형 데이터의 대소관계는 사전식 순서에 의해 결정된다.
ORDER BY는 테이블에 영향을 주지 안흔ㄴ다. 단지 행 순서를 바꾸어 결과를 반환하는 것 뿐.
만약 ORDER BY를 생략하면 순서는 어떻게 될까? 답은 '일정하지 않다'이다. 데이터베이스 서버의 당시 상황에 따라 어떤 순서로 행을 반환할지 결정된다.
SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명1, 열명2
NULL 값에 관해서는 대소비교 방법이 표준 SQL에 규정되어있지 않다. 따라서 데이터베이스 제품마다 다른데 보통 NULL 값을 가지는 행이 가장 먼저 표시되거나 가장 나중에 표시된다.
SELECT 열명 FROM 테이블명 LIMIT 행수 [OFFSET 시작행]
WHERE 구로 검색한 후, ORDER BY로 정렬된 뒤 최종적으로 처리된다.
LIMIT 구는 표준 SQL이 아니다. 제품마다 비슷한 기능을 다르게 표현할 것이다.
OFFSET은 0에서부터 시작한다.
SQL은 데이터베이스를 조작하는 언어지만 컴퓨터를 조작하는 언어이기도 한 만큼 기본적으로 계산기능을 포함한다.
연산자는 아래와 같다.
+-*/% 제품에 따라 % 대신 MOD를 쓰는 경우도 있다.
SELECT 구, WHERE 구, ORDER BY 구에서도 연산이 가능하다.
ex) SELECT *, price * quantity As amount FROM sample1;
: 모든 열을 표시하고, price 열과 quantity 열을 곱한 결과를 amount라는 이름의 열로 표시한다.
함수도 연산자와 표기 방법이 다를 뿐 같은 것이다.
ex) SELECT amount, ROUND(amount, 1) FROM sample1; 하면 amount 데이터를 1의 소수 둘째자리에서 반올림한 값을 열로 표시할 수 있다.
열 이름으로 ASCII 이외의 것을 포함할 경우에는 더블쿼트로 둘러사서 지정한다.
이름은 숫자로 시작할 수 없다. 제품에 따라 더블쿼트로 둘러싸면 쓸 수 있긴한데 왠만하면 그러지 말자.
표준 SQL에는 내부처리 순서가 따로 정해져있지 않으나 보통 WHERE 구에서의 행 선택, SELECT 구에서의 열 선택은 데이터베이스 서버 내부에서 전자 -> 후자의 순서로 처리된다. 따라서 SELECT 구에서 지정한 별명은 WHERE 구 안에서 사용할 수 없는 것이 보통이다.
ex) SELECT *, price * quantity AS amount FROM sample1 WHERE amount >= 2000; 처럼 쓰면 에러가 발생한다. 내부적으로 amount라는 별칭이 지정되기 전에 WHERE 문이 실행되기 때문!
NULL 로 연산하면 결과는 NULL이 된다.
한편 ORDER_BY는 서버에서 내부적으로 가장 나중에 처리된다. 따라서 SELECT 구에서 지정한 별명을 ORDER_BY에서 사용할 수 있다.
문자열 결합 데이터베이스 제품에 따라 + 연산자나 || 연산자, CONCAT 함수로 문자열을 결합할 수 있다.
SUBSTRING 함수
TRIM 함수 문자열 앞뒤로 여분의 스페이스가 있을 경우 이를 제거해줌. 인수를 지정해서 스페이스 외 다른 문자를 제거할 수 도 있다.
CHARACTER_LENGTH 함수
문자열 길이를 계산하여 반환하는 함수. VARCHAR 형의 문자열 처럼 가변길이일 경우 유용하다.
한편 OCTET_LENGTH 함수는 문자열의 길이를 바이트 단위로 계산해 돌려준다. 같은 문자 수라도 문자열 데이터의 바이트 수는 달라질 수 있다. 왜냐하면 문자 하나의 데이터가 몇 바이트의 저장공간을 필요로 하는지는 '인코드 방식' 에 따라 결정되기 때문이다.
표준 SQL에서는 CURRENT_TIMESTAMP 함수를 실행한 시점을 기준으로 시간을 표시한다.
데이터베이스별로 이 날짜를 서식을 지정해서 출력시킬 수 있다.
날짜 연산 예시는 아래와 같다. 데이터베이스 메뉴얼을 참고하자.
SELECT CURRENT_DATE + INTERVAL 1 DAY;
예시) SELECT a, CASE WHEN a IS NULL THEN 0 ELSE a END "a(null=0)" FROM sample1;
NULL 값을 변환하는 경우라면 COALESCE 함수를 사용하면 쉽다.
ELSE 를 생략하면 ELSE NULL이 된다.
INSERT INTO 테이블명 VALUES (값 1, 값 2, ...)
아래처럼 열을 지정해줄 수도 있다. 값을 따로 안 적어준 열은 DEFAULT 값이 들어갈 것이다.
INSERT INTO 테이블명(열1, 열2, ...) VALUES (값1, 값2, ...)
DELETE FROM 테이블명 WHERE 조건식
조건식에 맞으면 다 삭제해버린다.
UPDATE 테이블명 SET 열명1 = 값1, 열명2 = 값2, ... WHERE 조건식
테이블에 존재하지 않는 열을 지정하면 에러가 발생하며 UPDATE 명령은 실행되지 않는다.
데이터베이스 제품에 따라 처리 순서가 다를 수 있다.
데이터베이스에서 데이터를 삭제할 때는 물리삭제와 논리삭제의 두 가지 방법을 고려할 수 있다.
물리삭제: SQL의 DELETE 명령을 사용해 직접 데이터를 삭제하자는 사고 방식.
논리삭제: 테이블에 '삭제플래그'와 같은 열을 미리 준비해두고 삭제할 때 이 삭제플래그를 '삭제'로 만들고, 데이터를 남겨둔다. SELECT 명령을 실행하더라도 '삭제플레그'가 '삭제'로 설정된 행은 제외하고 SELECT 명령을 실행한다.
장점: 데이터를 삭제하지 않기 때문에 삭제되기 전의 상태로 간단히 되돌릴 수 있다.
단점: 삭제해도 데이터 베이스의 저장공간이 늘어나지 않는다. 그리고 데이터베이스의 크기가 증가함에 따라 검색속도가 떨어진다.
집계함수는 아래 5개가 대표적이다.
COUNT(집합)
SUM(집합)
AVG(집합)
MIN(집합)
MAX(집합)
DISTINCT로 열 내의 중복값을 제거할 수 있다. 중복 여부는 SELECT 구에 지정된 모든 열을 비교하여 판단한다.
ex) SELECT COUNT(ALL name), COUNT(DISTINCT name), FROM sample1;
집계함수 중 *을 인수로 사용할 수 있는 것은 COUNT 함수 뿐이다.
집계함수는 집합 안에 NULL 값이 있을 겨우 무시한다.
..은 별 내용 없으니 생략
집계함수로 넘겨줄 집합을 그룹으로 나눌 수 있다.
ex) SELECT name, COUNT(name), SUM(quantity) FROM sample1 GROUP BY name;
한편 내부처리 순서 때문에 아래처럼 WHERE 구에서는 집계함수를 쓸 경우 에러가 발생한다.
SELECT name, COUNT(name) FROM sample1 WHERE COUNT(name) = 1 GROUP BY name;
아래처럼 HAVING 구를 쓰자. 그럼 WHERE로 검색하고 HAVING 조건으로 검색하는 2단 구조가 된다.
SELECT name, COUNT(name) FROM sample1 GROUP BY name HAVING COUNT(name) = 1;
집계함수를 사용할 경우 HAVING 구로 검색조건을 지정한다.
GROUP BY에 지정한 열 이외의 열은 집계함수를 사용하지 않은 채 SELECT 구에 기술할 수 없다.
서브쿼리는 SELECT 명령에 의한 데이터 질의로, 상부가 아닌 하부의 부수적인 질의를 의미한다.
서브쿼리는 WHERE 구에서 주로 사용된다.
가령 최솟값을 가지는 행을 삭제하고자한다면, 아래처럼 SELECT와 DELETE를 결합시킬 수 있다.
DELETE FROM sample1 WHERE a = (SELECT a FROM (SELECT MIN(a) FROM sample1) AS x);
SELECT 는 테이블을 반환하는데 아래 4가지 패턴이 있다.
하나의 값을 반환하는 패턴 --> 요게 스칼라.
복수의 행이 반환되지만 열은 하나인 패턴
하나의 행이 반환되지만 열이 복수인 패턴
복수의 행, 복수의 열이 반환되는 패턴
SELECT 명령이 하나의 값만 반환하는 것을 '스칼라 값을 반환한다'고 한다. 왜 따로 스칼라 값을 반환하는 SELECT 명령을 특별취급하냐면 서브쿼리로 사용하기 쉽기 떄문이다!
= 연산자를 사용하여 비교할 경우에는 스칼라 값끼리 비교할 필요가 있다.
부모 명령과 자식인 서브쿼리가 특정 관계를 맺는 것을 '상관 서브쿼리'라고 한다.
데이터베이스 객체(이하 객체)는 데이터베이스 내에 실체를 가지는 어떤 것을 말한다.
객체는 이름을 가진다. 이 때 아래와 같은 제약 사항을 따른다.
기존 이름이나 예약어와 중복하지 않는다.
숫자로 시작할 수 없다.
언더스코어(_) 이외의 기호는 사용할 수 없다.
한글을 사용할 때는 더블쿼트(MySQL에서는 백쿼트)로 둘러싼다.
시스템이 허용하는 길이를 초과하지 않는다.
데이터베이스 객체는 스카마라는 그릇 안에 만들어진다. 따라서 객체의 이름이 같아도 스키마가 서로 다르다면 상관없다.
테이블과 스키마는 무엇인가를 담는 그릇 역할을 한다는 점에서 비슷하고, 테이블 안에는 열을 정의할 수 있고, 스키마 안에는 테이블을 정의할 수 있다. 각각의 그릇 안에서는 중복하지 않도록 이름을 지정한다. 이처럼 이름이 충돌하지 않도록 기능하는 그릇을 '네임스페이스'라고 부르기도 한다.
DDL은 데이터를 정의하는 명령으로, 스키마 내의 객체를 관리할 때 사용한다.
시스템을 운용하다 보면 처음에는 한 자리로 충분했던 용량이 부족해질 수 있다. 이 경우 열의 최대길이를 늘려서 대응할 수 있다. ALTER TABLE sample MODIFY col VARCHAR(30) 처럼!
단, 열의 길이를 줄이는 일은 잘 없다. 기존 데이터의 일부가 잘리며 에러가 생길 수 있고, 열의 최대길이를 줄였다고 해서 실제 저장공간이 늘어나는 경우도 적기 때문이다.
열을 추가할 경우 시스템에 별 영향을 안줄 것 같아도 테이블 정의가 바뀌어버리는 일인만큼 꽤 영향을 준다. 적어도 변경한 테이블에 행을 추가하는 INSERT 명령은 확인해야 한다.
제약은 테이블에 설정하는 것이다.
기본키 제약이 설정된 열에는 중복된 값을 저장할 수 없다.
인덱스는 데이터베이스 객체 중 하나이다.
인덱스의 역할은 검색속도의 향상이다. 일반적으로 테이블에 인덱스를 작성하면 테이블 데이터와 별개로 인덱스용 데이터가 저장장치에 만들어진다. 이 때 이진트리로 저장된다. 이진 트리에 중복하는 값을 등록할 수 없어야 이진 검색이 가능하므로, 기본키 제약은 이진 트리로 인덱스를 작성하는 데이터베이스가 많다.
EXPLAN 명령으로 인덱스 사용을 확인할 수 있다.
SELECT 명령을 실행할 때 인덱스의 사용 여부를 선택한다. 이는 데이터베이스 내부의 최적화에 의해 처리되는 부분이다.
뷰는 테이블과 같은 부류의 데이터베이스 객체 중 하나이다. SELECT 명령은 본래 데이터베이스 객체로 등록할 수 없는데 이를 객체로서 이름을 붙여 관리할 수 있도록 한 것이 뷰이다.
뷰는 테이블처럼 취급할 수 있지만 실체가 존재하지 않는다는 의미로 가상 테이블이라 불리기도 한다.따라서 데이터를 쓰거나 지울 수 있는 저장공간을 따로 가지지 않는다. 고로 SELECT 명령에서만 사용하는 것이 권장된다. INSERT나 UPDATE, DELETE 명령에서도 조건이 맞으면 가능하지만 사용에 주의할 필요가 있다.
한편 뷰는 저장공간 자원을 쓰지 않는 대신 CPU 자원을 사용한다. 즉, 뷰를 조회할 때마다 SELECT문을 실행시킨다.
머터리얼라이즈드 뷰(Materialized View)의 경우 테이블처럼 저장장치에 저장해두고 사용한다. 다만 뷰에 지정된 테이블의 데이터가 변경된 경우에는 SELECT 명령을 재실행하여 데이터를 다시 저장한다.
UNION: (중복을 제거한) 합집합
열 구성이 다른 테이블을 UNION으로 엮을 순 없다. 열을 따로 지정해서 각 SELECT 명령에서 집합의 요소가 될 데이터를 서로 맞춰주면 UNION으로 실행할 수 있는 쿼리가 된다.
UNION을 쓸 때 ORDER BY는 마지막 SELECT 명령에 지정할 수 있다. 이 경우 ORDER BY 구에 지정하는 열은 별명을 붙여 이름을 일치시킨다.
UNION ALL: (중복을 제거하지 않은) 합집합
INTERSECT: 교집합
EXCEPT: 차집합 (Oracle은 MINUS)
FROM 구에 복수의 테이블을 지정하면 교차결합을 한다. 그럼 곱집합이 구해진다.
이렇게 교차결합으로 계산된 곱집합에서 WHERE문 등으로 원하는 조합을 검색하는 것을 '내부결합(Inner Join)'이라 부른다.
이걸 한번에 해결하는 것이 INNER JOIN 문이다.
SELECT * FROM 테이블명1 INNER JOIN 테이블명2 ON 결합조건;
한편 외부결합은 내부결합처럼 교차결합으로 결합 조건을 지정하여 검색한다는 기본적인 컨셉은 같다. 이 때 '어느 한쪽에만 존재하는 데이터행을 어떻게 다룰지'를 변경하는 것이 외부결합이다. LEFT JOIN, RIGHT JOIN이 외부결합에 속한다.
SQL은 관계형 모델에 의해 구축된 데이터베이스를 조작하는 체계적인 명령의 집합이다.
관계형 모델의 용어를 몰라도 딱히 상관은 없다고 하는데, 훑어보자.
Relation: SQL의 테이블을 뜻한다.
Attribute: SQL에서 말하는 열을 듯한다.
Tuple: SQL의 행을 뜻한다.
관계대수: 릴레이션은 튜플의 집합이며 릴레이션에 대한 연산이 집합에 대한 연산에 대응된다는 이론
데이터베이스 설계는 데이터베이스의 스키마 내에 테이블, 인덱스, 뷰 등의 데이터베이스 객체를 정의하는 것을 말한다.
테이블 정의서로 열명,자료형, NULLABLE, 기본값, 논리명 등을 정의하자.
ER 다이어그램으로 관계성을 나타내자.
큰 자료형은 LOB(Large object) 형으로 저장가능하다.
AUTO_INCREMENT를 지정하여 자동증가 열을 만들 수 있따. 이는 PRIMARY_KEY 또는 UNIQUE로 유일성을 지정해야한다.
정규화는 테이블을 올바른 형태로 변경하고 분할하는 것을 뜻한다. 여기서 올바른 형태는 주관적 기준이다.
정규화는 보통 데이터베이스의 설계 단계에서 행해지며 잘 활용하면 효율적인 데이터베이스를 설계할 수 있다.
정규화는 하나의 데이터는 한 곳에 있어야 한다.는 규칙에 근거한다. 하나의 데이터가 반드시 한 곳에만 저장되어 있다면 데이터를 변경하더라도 한 곳만 변경하는 것으로 끝낼 수 있다.
일반적인 정규화 순서는 아래와 같다.
제1정규형
반복되는 데이터를 가로(열 방향)가 아닌 세로(행 방향)로 늘리는 것.
중복을 제거하는 방향으로 테이블 분할도 진행한다.
분할한 테이블을 다시 합칠 수 있도록 기본키 지정도 진행한다.
제2정규형
기본키에 의해 특정되는 열과 그렇지 않은 열을 나눈다. 즉, 함수종속성을 찾아내서 테이블을 분할하는 것!
함수종속성: 키 값을 이용해 데이터를 특정지을 수 있는 것
제3정규형
기본키 이외의 부분에서 중복이 없는지 조회한다.
SQL로 여러개의 테이블에 insert하다가 어느 한곳에서 에러가 나고, 이를 취소하기 위해 insert가 성공한 테이블 하나하나에 접근하여 다시 delete하는 상황을 가정해보자. 너무 번거롭지 않은가.
트랜잭션을 사용해서 데이터를 추가한다면 에러가 발생해도 트랜잭션을 롤백해서 종료할 수 있다. 그럼 트랜잭션 내에서 행해진 모든 변경사항을 없었던 것으로 할 수 있다. 아무런 에러가 발생하지 않는다면 변경사항을 적용하고 트랜잭션을 종료하는데, 이 때 커밋을 사용한다.
세트로 실행하고 싶은 SQL 명령은 트랜잭션 내에서 실행시키자.