01. 데이터베이스와 SQL
데이터베이스: 데이터의 집합
DBMS(Database Management System): 데이터베이스를 관리하고 운영하는 소프트웨어
대용량 데이터를 관리하거나 여러 사용자와 공유하는 특성
SQL(Structured Query Language): 데이터베이스를 구축, 관리하고 활용하기 위한 언어
RDBMS(Relational DBMS): 관계형 DBMS. 최소 단위 테이블, 테이블은 열과 행으로 이루어짐
MySQL 8.0.21 버전 설치
MySQL Workbench: SQL 개발과 관리, 데이터베이스 설계, 생성, 그리고 유지를 위한 단일 개발 통합 환경을 제공하는 비주얼 데이터베이스 설계 도구
02. 실전용 SQL 미리 맛보기
데이터베이스 모델링
프로젝트: 현실 세계에서 일어나는 업무를 컴퓨터 시스템으로 옮겨놓는 과정 -> 대규모 소프트웨어를 작성하기 위한 전체 과정
데이터베이스 모델링: 현실 세계를 데이터베이스의 테이블 구조로 만드는 과정
DBMS > 데이터베이스 > 테이블
행(row, record)의 개수 = 데이터 건수
테이블 설계: 테이블의 열 이름과 데이터 형식을 지정
열 이름(+PK), 데이터 형식, 최대 길이, NULL 허용
데이터베이스 개체
테이블, 인덱스, 뷰, 스토어드 프로시저, 트리거, 함수, 커서 등
인덱스: 데이터를 빠르게 조회할 수 있게
Full Table Scan: 모든 칼럼을 뒤져서 조건에 맞는 데이터 조회 (인덱스 사용X)
뷰: 가상의 테이블. 뷰를 활용하면 보안을 강화하고 SQL문을 간단화 할 수 있다.
사용자 -> 뷰 -> 테이블
뷰는 테이블과 내부적으로 연결되어 있어 사용자는 테이블에 접근했다고 생각
테이블 조회 방법과 동일하게 뷰에 접근
스토어드 프로시저: MySQL에서 제공하는 프로그래밍 기능.
사용 예) 여러 개의 SQL 문을 하나로 묶기(like 메서드), 연산식, 조건문, 반복문 등
03. SQL 기본 문법
SELECT ~ FROM ~ WHERE
데이터 조회, 읽기 쿼리
USE (DB명) ;
SELECT (칼럼명),... FROM (테이블명) WHERE (조건) ;
SELECT * FROM (DB명).(테이블명) ;
칼럼과 별칭(alias)
SELECT height 키, addr 주소 FROM member ;
height의 별칭 = '키'
addr의 별칭 = '주소'
WHERE 절
WHERE height >= 162 AND height <= 165 ;
WHERE height BETWEEN 162 AND 165 ;
=> 동일 쿼리 (범위)
WHERE addr = '경기' OR addr = '전남' OR addr = '경남' ;
WHERE addr IN ( '경기', '전남', '경남') ;
=> 동일 쿼리 (동등)
WHERE mem_name LIKE '우%' ;
우로 시작하는 1글자 이상의 문자열
%: 0개 이상의 문자
WHERE mem_name LIKE '__핑크' ;
핑크로 끝나는 4글자의 문자열
_: 1개의 문자
SELECT 문 심화편
SELECT 문의 형식
SELECT 칼럼명
FROM 테이블명
WHERE 조건식
GROUP BY 칼럼명
HAVING 조건식
ORDER BY 칼럼명
LIMIT 숫자
순서가 맞지 않으면 오류 발생!
ORDER BY
ORDER BY 절의 유무에 따라 결과 값이나 개수에 영향 없음.
결과(row) 출력 순서를 결정
ORDER BY (칼럼명) ASC/DESC
*기본값: ASC (오름차 순)
복수 칼럼에 대해 정렬 가능. 앞에 나온 칼럼이 우선 순위를 가진다. (동일 값에 한해 다음 정렬 조건 적용)
예) ORDER BY height DESC, debut_date ASC
LIMIT~(OFFSET)
조회할 데이터 수(row) 결정
ORDER BY 와 함께 사용해야 의미를 가진다.
ORDER BY height DESC LIMIT 2 OFFSET 3
ORDER BY height DESC LIMIT 3,2
offset: 3 번째 row 부터 출력 (1,2,3, ...)
limit: 2 개의 row 출력
DISTINCT
중복은 제거하고 하나씩만 출력
SELECT DISTINCT (칼럼명) FROM (테이블명)
GROUP BY
집계 함수
SUM() : 합계
AVG() : 평균
MIN() : 최소값
MAX() : 최대값
COUNT() : 행의 개수
COUNT(DISTINCT) : 행의 개수 (중복은 1개로)
쿼리 예) 구매 테이블(buy)
SELECT mem_id, SUM(amount) FROM buy GROUP BY mem_id
1. mem_id 로 그룹화
2. 그룹별 amount 칼럼의 합계(sum)
SELECT mem_id, price, amount, SUM(price*amount) FROM buy GROUP BY mem_id
회원 별 총 구매 금액
SELECT COUNT(*) FROM member ;
회원 테이블 행의 개수
SELECT COUNT(phone1) "연락처가 있는 회원" FROM member ;
phone1 != NULL 인 회원의 수
WHERE + GROUP BY
SELECT mem_id, SUM(price*amount) "총 구매 금액"
FROM buy
WHERE SUM(price*amount) > 1000
GROUP BY mem_id ;
=> 오류 발생. 그룹에 대한 조건은 WHERE 가 아닌 HAVING 절에서
SELECT mem_id, SUM(price*amount) "총 구매 금액"
FROM buy
GROUP BY mem_id
HAVING SUM(price*amount) > 1000 ;데이터 변경을 위한 INSERT, UPDATE, DELETE 문
데이터 입력: INSERT
모든 열 입력
INSERT INTO 테이블명 VALUES (값1, 값2, ...) ;
열 지정 입력
INSERT INTO 테이블명(열1, 열2, ...) VALUES (값1, 값2, ...) ;
AUTO_INCREMENT
테이블을 생성할 때 칼럼에 AUTO_INCREMENT PRIMAY KEY 옵션을 주면 DBMS 가 값을 생성하여 넣어준다.
즉, 데이터 입력시에 NULL 입력
ALTER INTO 테이블명 AUTO_INCREMENT=100 ;
SET @@auto_increment_increment=3 ;
대부분 테이블 생성 시점에 함께 쓰인다.
시퀀스 값: 1(기본값) -> 100,
자동 증가 값: 1(기본값) -> 3
100, 103, 106, ...
INSERT INTO ~ SELECT
다른 테이블의 데이터를 입력
INSERT INTO new_table
SELECT col1, col2 FROM table ;
칼럼의 이름은 서로 같지 않아도 된다. 순서랑 타입만 맞추기
데이터 수정: UPDATE
UPDATE 테이블명
SET 열1=값1, 열2=값2, ...
WHERE 조건 ;
주의: WHERE 절이 없다면 모든 칼럼에 대한 변경을 의미
데이터 삭제: DELETE
행 단위 삭제
DELETE FROM 테이블명
WHERE 조건 ;
예)
DELETE FROM city_popul
WHERE city_name LIKE 'New%'
LIMIT 5 ;
04. SQL 고급 문법
MySQL의 데이터 형식
정수형
TINYINT(1) < SMALLINT(2) < INT(4) < BIGINT(8)
UNSIGNED 예약어가 있다.
문자형
CHAR(n) : n 글자만큼 고정된 저장 공간을 미리 할당. 고정된 길이의 문자열에 적합
VARCHAR(n) : 최대 n글자 입력 가능하지만, 실제 입력된 글자 수 만큼 저장 공간 할당. 가변적인 길이의 문자열에 적합
전화번호 국번 등 맨 앞이 0인 숫자의 경우 문자열로 받는 것이 적합할 수 있다.
더불어 연산이나 크기에 대한 의미도 없다.
즉, 데이터가 숫자로 이루어져 있다고 하더라도 성질에 따라 문자형도 고려해보자.
대용량 데이터
LONGTEXT: 대용량 문자 데이터
LONGBLOB: 대용량 이진 데이터
실수형
FLOAT(4) < DOUBLE(8)
날짜형
DATE(3), TIME(3), DATETIME(8)
변수
사용자 정의 변수
SET @변수명 = 값;
*조회 결과를 변수에 대입: SELECT ~ INTO @변수명
*변수 출력: SELECT @변수이름 ;
임시로 값을 저장하는 기능 정도만 담당한다. 선언이 필요 없음
지역 변수
DECLARE 변수명 데이터타입 [DEFAULT 값];
SET 변수명 = 값;
스토어드 프로시저에서 선언 및 초기화. 변수의 범위는 BEGIN ~ END 블록으로 제한된다.
선언만 하고 초기화 하지 않으면 DEFAULT 작동
시스템 변수 (글로벌)
SET GLOBAL 변수명 = 값;
참고: LIMIT 문에는 변수를 사용할 수 없다. (문법 오류)
PREPARE, EXECUTE ~ USING
SET @count = 3;
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?' ;
EXECUTE mySQL USING @count ;
사전에 정의한 mySQL의 ? 를 @count 값으로 치환하고 해당 SQL을 실행한다.
데이터 형 변환
명시적 형 변환: 함수 사용. CAST(), CONVERT(). 문법만 약간 다를 뿐 기능은 유사하다.
암시적 형 변환: DBMS에 의한 자동 형 변환
두 테이블을 묶는 JOIN
일대다 관계: 주로 기본키(PK)와 외래키(FK) 관계로 연결되어 있기 때문에 'PK-FK' 관계라고 하기도.
한 테이블의 PK가 다른 테이블에서는 여러 번 존재할 수 있다. ex) 회원(1)은 여러 번 주문(n) 할 수 있다.
INNER JOIN (JOIN)
일반적으로 'JOIN' 이라고 지칭하는 조인 방법
SELECT 열 목록
FROM 테이블1
INNER JOIN 테이블2
ON 조인 조건
[WHERE 검색 조건]
일대다 관계에서 보통 ON table1.PK = table2.FK
열 목록 중 두 테이블에 같은 이름의 열이 포함될 경우 테이블 이름도 같이 명시해주자. (table1.mem_id)
특징: 일종의 교집합. 조인 조건에 해당하지 않는 행은 출력하지 않는다.
OUTER JOIN
SELECT 열 목록
FROM 테이블1 (LEFT 테이블)
[LEFT | RIGHT | FULL] OUTER JOIN 테이블2 (RIHGT 테이블)
ON 조인 조건
[WHERE 검색 조건]
LEFT OUTER JOIN: LEFT 테이블 기준으로 조인. LEFT의 데이터는 모두 출력
RIGHT OUTER JOIN: RIGHT 테이블 기준으로 조인. RIGHT의 데이터는 모두 출력
FULL OUTER JOIN: LEFT, RIGHT 테이블의 모든 데이터 출력
*조인 쿼리에서 별칭
FROM member M
INNER JOIN buy B
쿼리 문에서 칼럼에 테이블을 명시할 때 별칭 사용. 코드 간결화
CROSS JOIN
상호 조인. 테스트를 위한 대용량 데이터를 생성할 때 사용
테이블1의 m개 컬럼과 테이블2의 n개 컬럼이 모두 조인 (m x n 개의 행 생성)
ON 구문이 없음
결과의 내용은 의미가 없다. 랜덤 조인
SELECT * FROM 테이블1 CROSS JOIN 테이블2 ;
*CREATE TABLE ~ 조인문 : 조인 결과를 테이블로 생성
SELF JOIN
자체 조인. 1개의 테이블을 사용하고 별칭을 달아 테이블이 다르다고 가정하고 조인
SELECT 열 목록
FROM 테이블 별칭A
INNER JOIN 테이블 별칭B
ON 조인 조건
[WHERE 검색 조건]
SQL 프로그래밍
SQL 프로그래밍은 기본적으로 스토어드 프로시저 안에서 정의해야 한다.
DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
이 부분에 SQL 프로그래밍 작성
END $$
DELIMITER ;
CALL 스토어드_프로시저_이름(); -- 스토어드 프로시저 호출IF 문
IF 조건식 THEN
조건이 참일 때 실행할 SQL 문장들
END IF;
IF ~ ELSE 문
IF 조건식 THEN
조건이 참일 때 실행할 SQL 문장들
ELSE
조건이 참이 아닐 때 실행할 SQL 문장들
END IF;
*저장 프로시저 안에서 변수 선언 'DECLARE'
DECLARE myNum INT; --INT형 변수 myNum 선언
SET myNum = 200; --변수에 값 대입
*SELECT (칼럼명) INTO (변수명) : 조회한 결과 값을 변수에 대입
CASE 문
CASE
WHEN 조건1 THEN
SQL 문장들
WHEN 조건2 THEN
SQL 문장들
ELSE
SQL 문장들
END CASE;
SELECT M.mem_id, M.mem_name, SUM(price*amount) '총 구매액',
CASE
WHEN SUM(price*amount) >= 1500 THEN '최우수고객'
WHEN SUM(price*amount) >= 1000 THEN '우수고객'
WHEN SUM(price*amount) >= 1 THEN '일반고객'
ELSE '유령고객'
END '회원등급'
FROM buy B
RIGHT OUTER JOIN member M
ON B.mem_id = M.mem_id
GROUP BY M.mem_id
ORDER BY SUM(price*amount) DESC;
WHILE 문
WHILE 조건식 DO
SQL 문장들
END WHILE;
ITERATE문과 LEAVE문
ITERATE 레이블명 -> 지정한 레이블로 이동함
LEAVE 레이블명 -> 지정한 레이블을 빠져나옴
myWhile; -> 레이블 정의
WHILE i <= 100 DO
IF (i %4 = 0) THEN
SET i = i+1;
ITERATE myWhile; --myWhile 로 이동 후 계속 진행
IF (i%10 = 0) THEN
LEAVE myWhile; --myWhile 을 떠남. 즉, while 문 종료
END WHILE;동적 SQL 문
PREPARE 와 EXECUTE
PREPARE mySQL FROM 'SELECT * FROM member WHERE mem_id = 'BLK'' ;
EXECUTE mySQL ;
DEALLOCATE PREPARE mySQL;
USING 변수
SET @cur_date = CURRENT_TIMESTAMP(); --현재 날짜와 시간
PREPARE mySQL FROM 'INSERT INTO gate_table VALUES (NULL, ?)';
EXECUTE mySQL USING @cur_date;
DEALLOCATE PREPARE mySQL;
'Book > 혼자 공부하는 SQL' 카테고리의 다른 글
| 5장~7장 (0) | 2022.10.05 |
|---|
