데이터베이스와 SQL
DB, Database
데이터의 집합, 데이터의 저장소
DBMS, DataBase Management System
대량의 데이터베이스를 효율적으로 관리하고 운영하기 위한 시스템 또는 소프트웨어
MySQL, Oracle, MariaDB
RDBMS, Relational DataBase Management System
테이블이라는 최소 단위로 이루어진 관계형 DBMS
SQL, Structured Query Language
RDBMS에서 데이터를 구축, 관리하고 활용하기 위해 사용되는 언어
root
MySQL의 모든 권한이 있는 관리자 이름
MySQL을 설치할 때 기본적으로 생성
SQL 훑어보기
project
현실 세계의 업무를 컴퓨터 시스템으로 옮겨 놓는 일련의 과정
database modeling
데이터베이스 구축 전 테이블의 구조를 미리 설계하는 과정
waterfall model
소프트웨어 개발 절차 중 하나로
위에서 아래로 폭포가 떨어지듯 개발 단계가 진행되기 때문에 붙여진 이름
data type
테이블의 열에 저장될 데이터의 형식
문자형(CHAR), 정수형(INT), 날짜형(DATE) 등이 있음
column
테이블의 세로, 테이블은 여러 개의 열로 구성되며, 각 열을 구분하기 위해 열 이름을 사용
row
테이블의 가로, 실질적인 데이터(행 데이터)를 의미
schema
MySQL 안에 들어 있는 데이터베이스
reserves word
기존에 약속된 SQL, SELECT, FROM, WHERE 등이 있음
쿼리 창에서 입력하면 자동으로 파란색으로 표시됨
Null
비어 있는 값
NN, Not Null
빈 값을 허용하지 않는, 즉 반드시 입력해야 하는 값
USE
사용할 데이터베이스를 선택하는 구문으로, 한 번 지정하면 계속 유지된다. MySQL 워크벤치를 재시작하거나 쿼리 창을 새로 열면 다시 USE를 실행해야 함
SQL 기본 문법
SELECT~
FROM~
WHERE
가장 기본적인 SQL문
SELECT 다음에는 열 이름이
FROM 다음에는 테이블 이름이
WHERE 다음에는 다양한 조건식이 나옴
relational operator
WHERE 절에서 2가지 이상의 조건을 지정할 때 사용. AND, OR 등으로 참/거짓을 판별
LIKE
문자열의 일부 글자를 검색할 때 사용
모두 허용할 때는 % 를, 하나로 지정할 때는 _ 를 사용
ORDER BY
결과가 출력되는 순서를 조절
오름차순인 ASC(Ascending)와 내림차순인 DESC(Descending) 중 선택할 수 있음
LIMIT
출력하는 개수를 제한하며, 주로 ORDER BY와 함께 사용됨
DISTINCT
조회된 결과에서 중복된 데이터를 1개만 남김
DISTINCT를 열 이름 앞에 붙여 사용함
GROUP BY
데이터를 그룹으로 묶어주는 기능을 함
주로 집계 함수와 함께 사용됨
alias
SELECT 문에서 실제 열 이름 대신 출력되도록 설정하는 문자
aggregate function
데이터를 그룹화(grouping)해주는 기능을 함
주로 GROUP BY와 함께 사용함
집계 함수 종류
| 함수명 | 설명 |
|---|---|
| SUM() | 합계 구하기 |
| AVG() | 평균 구하기 |
| MIN() | 최소값 구하기 |
| MAX() | 최대값 구하기 |
| COUNT() | 행의 개수 세기 |
| COUNT(DISTINCT) | 행 개수 세기 (중복은 1개만 인정) |
HAVING
집계 함수와 관련된 조건을 제한하며, 반드시 GROUP BY 절 다음에 나와야 함
INSERT
테이블에 데이터를 입력(삽입)하는 기본적인 SQL 문
AUTO_INCREMENT
열을 정의할 때 1부터 증가하는 값을 자동으로 입력해줌
해당 열은 반드시 PRIMARY KEY로 지정해야 함
INSERT INTO
~SELECT
다른 테이블의 데이터를 가져와 한 번에 입력
단, SELECT 문의 열 개수는 INSERT를 할 테이블의 열 개수와 같아야 함
UPDATE
기존에 입력되어 있는 값을 수정하는 명령
주로 WHERE 절과 함께 사용
WHERE 절을 생략하면 테이블의 모든 행의 값이 변경되므로 주의해야 함
DELETE
행 단위로 데이터를 삭제함
WHERE 절을 생략하면 전체 행 데이터가 삭제되므로 주의해야 함
테이블 삭제
- DELETE: 행 단위로 삭제하고 빈 테이블은 남김, 데이터의 양이 많을 경우 시간이 오래 걸릴 수 있음
- DROP: 테이블 자체를 삭제함
- TRUNCATE: WHERE 문을 사용할 수 없어 조건 없이 전체 행을 삭제하고 빈 테이블은 남김
대량의 행 데이터를 모두 삭제할 때는 DELETE보다 TRUNCATE가 효율적
SQL 고급 문법
정수형
소수점이 없는 숫자
TINYINT, SMALLINT, INT, BIGINT 등이 있음
UNSIGNED
정수형에 붙이면 범위가 0부터 지정됨
문자형
글자를 저장하기 위해 사용
입력할 최대 글자의 개수를 지정해야 함
고정길이 문자형인 CHAR와 가변길이 문자형인 VARCHAR가 있음
실수형
소수점이 있는 숫자를 저장할 때 사용
소수점 아래 7자리까지 표현되는 FLOAT와 소수점 아래 15자리까지 표현되는 DOUBLE이 있음
날짜형
날짜를 저장하는 DATE
시간을 저장하는 TIME
날짜와 시간을 둘 다 저장하는 DATETIME이 있음
variable
MySQL에서는 변수 이름 앞에 @를 붙이고 SET 문으로 변수에 값을 대입함
type conversion
문자형, 정수형 등의 데이터 형식을 다른 형태로 변경하는 것
직접 함수를 사용해서 변환하는 명시적 변환과 별도의 지시 없이 자연스럽게 변환되는 암시적 변환이 있음
명시적 변환에는 CAST(), CONVERT() 등을 사용함
join
두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것
one to many
두 테이블이 조인할 경우 한쪽 테이블에는 하나의 값만 존재해야 하지만,
연결된 다른 테이블에는 여러 개의 값이 존재할 수 있는 관계를 말함
주로 기본 키(PK)와 외래 키(FK) 관계로 맺어져 있어 ‘PK-FK 관계’라고 부르기도 함
inner join 내부 조인
두 테이블을 조인할 때, 두 테이블에 모두 데이터가 있어야 결과가 바로 추출됨
조인 중에 가장 많이 사용되며, 일반적으로 조인이라 하면 바로 이 내부 조인을 뜻함
outer join 외부 조인
두 테이블을 조인할 때, 필요한 데이터가 한쪽 테이블에만 있어도 결과를 추출할 수 있음
cross join 상호 조인
한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능
따라서 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행 개수를 곱한 개수가 됨
self join 자체 조인
자신이 자신과 조인된다는 의미로, 하나의 테이블에 서로 다른 별칭을 붙여 사용
IF 문
다양한 조건을 처리할 때 사용
조건식이 참이라면 결과가 실행되지만, 그렇지 않으면 실행되지 않음
IF ~ ELSE 문
조건식이 참일 때와 거짓일 때 각각 다른 부분을 실행함
CASE 문
2가지 이상의 여러 가지 조전에 따라 처리가 가능
‘다중 분기’라고 부름
WHILE문
조건식이 참인 동안에는 계속 같은 내용을 반복
지정한 레이블로 가서 계속 실행하는 ITERATE 문과 지정한 레이블을 빠져나가는 LEAVE 문을 함께 사용할 수 있음
동적 SQL 문
미리 SQL 문을 준비한 후에 필요할 때 실행하는 것
PREPARE는 SQL 문을 준비만 해놓고, EXECUTE는 준비한 SQL문을 실행함
실행 후에는 DEALLOCATE PREPARE로 문장을 해제해야 함
테이블과 뷰
table
표 형태로 구성된 2차원 구조로
열과 행으로 구성되어 있음
CREATE TABLE
테이블을 생성하는 SQL로, 테이블 이름, 열 이름, 데이터 형식 등을 지정함
Primary key ~ Foreign key 기본 키-외래 키
두 테이블이 일대다로 연결되는 관계로 기본 키-외래 키 관계를 맺으면 오류가 없는 데이터가 됨
기본 키가 있는 테이블을 기준 테이블, 외래 키가 있는 테이블을 참조 테이블이라 함
constraint 제약조건
데이터의 무결성, 즉 데이터에 결함을 없애기 위해 제한하는 조건
PRIMARY KEY Constraint 기본 키 제약조건
많은 행 데이터 중 특정 데이터를 구분할 수 있는 식별자, 기본 키를 지정하여 중복된 데이터가 입력되지 않도록 제한하는 제약조건임
DESCRIBE
테이블 또는 뷰의 정보를 조회하는 SQL 문, 줄여서 DESC라고 써도 됨
ALTER TABLE
이미 만들어진 테이블을 수정하는 SQL 문
FOREIGN KEY Constraint 외래 키 제약조건
외래 키로 두 테이블 사이를 기본 키-외래 키 관계로 연결하여 무결한(오류가 없는) 데이터로 만드는 제약조건임
ON UPDATE CASCADE
기준 테이블의 기본 키를 변경하면 참조 테이블의 외래 키도 자동으로 변경되는 기능
ON DELETE CASCADE
기준 테이블의 기본 키를 삭제하면 참조 테이블의 외래 키도 자동으로 삭제되는 기능
UNIQUE Constraint 고유 키 제약조건
중복되지 않는 유일한 값을 입력해야 하는 제약조건
기본 키와는 다르게 NULL값을 허용하며 여러 개를 설정해도 됨
CHECK Constraint 체크 제약조건
입력되는 데이터를 점검하는 제약조건
해당 열의 정의 뒤에 CHECK(조건)을 추가하여 사용
DEFAULT 기본값 정의
값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 제약조건
view
가상의 테이블
테이블처럼 실제 데이터를 가지고 있지는 않고, SELECT 문으로 만들어져 있기 때문에 뷰에 접근하는 순간 SELECT가 실행되면서 그 결과가 화면에 출력되는 방식
CREATE VIEW
뷰를 실행하는 SQL 문
ALTER VIEW
뷰를 수정하는 SQL 문
DROP VIEW
뷰를 삭제하는 SQL 문
WITH CHECK OPTION
뷰에 설정된 범위에 따라 그에 해당되는 값만 입력되도록 지정하는 SQL 문
인덱스
index
테이블에서 데이터를 빠르게 조회할 수 있도록 도와주는 도구
Clustered Index 클러스터형 인덱스
기본 키로 지정하면 자동으로 생성되고 테이블당 1개만 만들 수 있음
해당 열을 기준으로 자동 정렬됨
Secondary Index 보조 인덱스
고유 키(Unique)로 지정하면 자동으로 생성되며, 테이블당 여러 개를 만들 수 있음
자동 정렬은 되지 않음
B-tree 균형 트리, Balanced tree
자료 구조에서 범용적으로 사용되는 데이터 구조
클러스터형 인덱스와 보조 인덱스 모두 내부적으로 이 균형 트리로 만들어짐
Node 노드
균형 트리 구조에서 데이터가 저장되는 공간
가장 상위부터 루트 노드, 중간 노드, 리프 노트로 구분함
Page
MySQL에서는 노드를 페이지라고 부름
16Kbyte(16384byte) 크기를 가진 최소한의 저장 단위로,
데이터를 1건만 입력해도 1개 페이지가 필요
페이지 분할
데이터 입력 시 입력할 페이지에 공간이 없는 경우 새로운 페이지를 준비해서 데이터를 나누는 작업
페이지 분할이 일어날수록 MySQL이 느려지기 때문에 성능에 나쁜 영향을 줄 수 있음
Index Scan 인덱스 검색
클러스터형 또는 보조 인덱스를 이용해서 데이터를 검색하는 것
속도는 인덱스를 사용하지 않았을 때보다 빠르고
클러스터형 인덱스가 보조 인덱스보다 조금 더 빠름
CREATE INDEX
인덱스를 직접 생성하는 구문
보조 인덱스가 생성됨
DROP INDEX
CREATE INDEX 문으로 생성한 인덱스를 제거함
기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못함
SHOW INDEX
테이블에 어떤 인덱스가 설정되어 있는지 확인하는 SQL 문
SHOW TABLE STATUS
테이블에 생성된 인덱스의 크기를 확인하는 SQL 문
Simple Secondary Index 단순 보조 인덱스
중복을 허용하는 보조 인덱스
CREATE INDEX 문으로 생성함
Unique Secondary Index 고유 보조 인덱스
중복을 허용하지 않는 보조 인덱스
CREATE UNIQUE INDEX 문으로 생성함
ANALYZE TABLE
인덱스를 생성한 후 ANALYZE TABLE 문을 실행해서 테이블을 분석/처리해줘야 실제로 적용됨
Execution Plan 실행 계획
MySQL 워크벤치에서 SQL 실행 시 인덱스의 사용 여부를 확인할 수 있음
인덱스를 효과적으로 사용하는 방법
- 인덱스는 열 단위에 생성함
- WHERE절에서 사용되는 열에 인덱스를 만들어야 함
- WHERE절에 사용되더라도 자주 사용해야 가치가 있음
- 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없음
- 클러스터형 인덱스는 테이블당 하나만 생성할 수 있음
- 사용하지 않는 인덱스는 제거함
스토어드 프로시저
stored procedure 스토어드 프로시저
MySQL에서 제공하는 프로그래밍 기능
쿼리 문의 집합으로 어떤 동작을 일괄 처리하기 위한 용도로 사용
SQL를 묶는 개념 외에 연산식, 조건문, 반복문 등을 사용할 수도 있음
BEGIN ~ END
스토어드 프로시저는 BEGIN ~ END 사이에 코드를 구현
DELIMITER
MySQL에서는 기본적으로 세미콜론(;)을 구분자로 사용하는데,
스토어드 프로시저 안에 있는 SQL의 끝에도 세미콜론을 사용하면 혼동될 수 있으므로
구분자를 $$, ##, %%, && 등으로 바꿔 사용하는 것이 좋음
CALL
작성된 스토어드 프로시저를 실행(호출)하는 명령문
입력 매개변수
스토어드 프로시저에 값을 전달
형식은 IN 구문을 사용
출력 매개변수
스토어드 프로시저에서 처리된 결과를 돌려받음
형식은 OUT 구문을 사용
stored function
MySQL에서 제공하는 다양한 내장 함수 외에 사용자가 직접 만들어서 사용하는 함수
RETURNS
스토어드 함수에서 반환할 값의 데이터 형식을 미리 지정할 때 사용함
RETURN
스토어드 함수에서 값을 반환할 때 사용함
cursor 커서
테이블의 모든 행을 한 행씩 처리할 때 사용하는 방식
DECLARE로 선언하며, 그 내용은 SELECT 문임
endOfRow
행의 끝을 파악하기 위한 변수로 endOfRow를 준비함
행의 끝에 다다르면 TRUE 값인지 체크하는 방식을 사용
trigger 트리거
테이블에서 DML 문(Inset, Update, Delete 등)의 이벤트가 발생할 때 자동으로 실행되는 프로그래밍 기능
참고
[혼자 공부하는 SQL]
