[혼자공부하는SQL] Summary1

1강

1. DBMS(Database Management System)

1) 의미

: 데이터베이스를 관리하고 운영하는 소프트웨어

2) 조건

• 대용량 데이터
• 여러 사용자와 동시에 공유

3) 종류

• 계층형(Hierarachical)
• 망형(Network)
• 관계형(Relational) → 가장 많이 사용되고 있다.
• 객체지향형(Object-Oriented)
• 객체관계형(Object-Relational)

2. SQL (Structured Query Language)

1) 의미

: 구조화된 쿼리 언어로, 관계형 DBMS에서 사용되는 언어이다.

2) 특징

• 국제표준화기구에서 SQL에 대한 표준을 정해서 발표하고 있다. (표준 SQL)
• 표준 SQL이 존재하지만, DBMS 제품별로 기능 차이가 존재한다.

---

3강

1. 데이터베이스 모델링

1) 의미

: 테이블의 구조를 미리 설계하는 개념

• 현실의 사물과 작업을 테이블 형태로 변환하는 과정

2) 특징

• 대표적으로 ‘폭포수 모델(waterfall model)을 사용
• 이 단계를 거쳐 데이터베이스 개체인 테이블 구조가 결정된다.

2. 폭포수 모델

1) 의미

: 소프트웨어 개발 프로세스를 순차적으로 진행하는 방법론으로, 각 단계가 완료되어야 다음 단계로 넘어가는 구조를 가진다. 이는 명확한 단계 구분을 통해 프로젝트 관리와 진행 상황을 쉽게 파악할 수 있도록 돕는다.

2) 과정

1. 프로젝트 계획 : 계획 단계
2. 업무 분석 : 업무가 어떻게 돌아가는지 파악
3. 시스템 설계 : 정리한 업무 분석을 컴퓨터에 적용시키기 위해서 알맞은 형태로 다듬는 과정
4. 프로그램 구현 : 시스템 설계의 결과를 실제 프로그래밍 언어로 코딩하는 단계
5. 테스트 : 코딩된 프로그램에 오류가 없는지 확인하는 과정
6. 유지보수 : 실제 운영하며, 문제점을 보완하고 기능을 추가하는 과정

3. 전체 데이터베이스 구성도

1) 예시

: DBMS, 열, 열 이름, 데이터 형식, 행 등을 포함한다.

---

4강

1. 데이터베이스 구축 절차

• 데이터베이스 만들기
• 테이블 만들기
• 데이터 입력/수정/삭제
• 데이터 조회/활용

2. 데이터베이스 만들기

1) 형식

CREATE SCHEMA 데이터베이스_이름;

-- 예시
CREATE SCHEMA market_db;

3. 테이블 만들기

1) 형식

CREATE TABLE 테이블_이름(
    열1_이름 열1_데이터형식,
    열2_이름 열2_데이터형식,
    PRIMARY KEY (기본키_열의이름)
);

-- 예시
CREATE TABLE member(
    member_id INT,
    age       INT,
    PRIMARY KEY (member_id)
);

3) 특징

(1) PRIMARY KEY를 지정하지 않아도 괜찮은가?

: PRIMARY KEY를 지정하지 않아도 테이블을 생성할 수 있지만, 각 레코드를 고유하게 식별하기 위해 PRIMARY KEY를 설정하는 것이 좋다. 만약 PRIMARY KEY를 지정하지 않으면, 데이터의 무결성이 보장되지 않아서 중복된 데이터가 들어갈 수 있다.

3. (미리보기) 데이터 입력 / 수정 / 삭제 / 검색

데이터 입력	테이블에 데이터 추가	INSERT INTO 사용
데이터 수정	기존 데이터 변경	UPDATE 사용
데이터 삭제	특정 데이터 제거	DELETE FROM 사용
데이터 검색	데이터 조회	SELECT 사용

---

5강

1) 의미

: 데이터를 조회할 때 결과가 나오는 속도를 획기적으로 빠르게 해준다.

2) 인덱스 생성 명령

CREATE INDEX 인덱스_이름 ON 테이블_이름(열_이름);

-- 예시
CREATE INDEX inx_member_name ON member(member_name);

3) 기타

• Primary Key와 Unique Key는 기본적으로 인덱스를 포함한다.
  • member_id가 Primary Key라면, 이미 자동으로 인덱스가 생성된다.
  • 이 경우 별도로 인덱스를 만들 필요가 없다.
• 인덱스는 특정 컬럼을 빠르게 검색하는 데 도움을 주지만, 삽입(INSERT), 수정(UPDATE), 삭제(DELETE) 성능은 저하될 수 있다.
  • 이유는 데이터 변경 시 인덱스도 같이 관리해야 하기 때문이다.

2. 뷰

1) 의미

: 테이블의 일부를 제한적으로 표현할 때 주로 사용된다.

2) 특징

• 뷰를 활용하면 보안도 강화하고, SQL문도 간단하게 사용할 수 있다.
• 뷰는 실제 데이터를 가지고 있지 않으며, 진짜 테이블에 링크된 개념이라고 생각하면 된다.

3) 뷰 생성 명령

(1) 형식

CREATE VIEW 뷰_이름
AS
    SELECT_구문

-- 예시
CREATE VIEW member_view
AS
    SELECT * from member;

3. 스토어드 프로시저

1) 의미

: SQL에서 프로그래밍이 가능하도록 해준다. 즉, SQL 안에서도 일반 프로그래밍 언어처럼 코딩을 할 수 있다.

• 비록 일반 프로그래밍보다 좀 불편하지만, 프로그래밍 조직을 작성할 수 있어서 때론 유용하게 사용된다.
• 여러 개의 SQL 문을 하나로 묵어서 편리하게 사용할 수 있다.

2) 사용 가능한 기능

• 연산식
• 조건문
• 반복문

3) 스토어드 프로시저 생성 명령

(1) 형식

DELIMITER //

CREATE PROCEDURE 프로시저명()
BEGIN
    -- SQL 명령어
END //

DELIMITER ;

-- 예시
DELIMITER //

CREATE PROCEDURE myProc()
BEGIN
    SELECT * FROM member WHERE member_name = '나훈아';
    SELECT * FROM product WHERE product_name = '삼각김밥';
END //

DELIMITER ;

CALL myProc();

• DELIMITER는 MySQL에서 스토어드 프로시저와 같은 복합 명령어를 작성할 때 기본 구분자인 세미콜론(;) 대신 다른 구분자를 설정하는 데 사용된다. 이를 통해 프로시저 내부의 SQL 명령어와 종료를 구분할 수 있다.

4. 트리거

1) 의미

: 잘못된 데이터가 들어가는 것을 미연에 방지하는 기능을 한다.

---

6강

1. USE : 데이터베이스 선택

1) 형식

USE 데이터베이스_이름;

-- 예시
USE market_db;

2) 현재 사용중인 데이터베이스 확인 명령문

SELECT DATABASE();

2. SELECT : 데이터 검색

1) 형식

SELECT 열_이름
FROM 테이블_이름
WHERE 조건식

2) 모든 열 검색

SELECT * FROM 테이블명

-- 예시
SELECT * FROM member;

3) 특정 열 검색

SELECT 열_이름 FROM 테이블명;
SELECT 열1_이름, 열2_이름 FROM 테이블명;

-- 예시 : 하나의 열
SELECT member_id FROM 테이블명;
-- 예시 : 여러 개의 열
SELECT member_id, member_name;

4) 열의 별칭 지정

SELECT 열_이름 별칭 FROM 테이블명;
SELECT 열1_이름 별칭, 열2_이름 별칭 FROM 테이블명;

-- 예시 : 하나의 열
SELECT member_id '순번' FROM 테이블명;
-- 예시 : 여러 개의 열
SELECT member_id '순번', member_name 이름 member_addr '사용자 주소' FROM 테이블명;

5) WHERE : 조건 지정

SELECT 열_이름 FROM 테이블_이름 WHERE 조건

-- 문자열
SELECT * FROM member **WHERE mem_name = '블랙핑크';**

-- 숫자
SELECT * FROM member **WHERE mem_number = 4;**

-- 부등호
SELECT mem_id, mem_name FROM member **WHERE height <= 162;**

-- AND 조건
SELECT mem_id, mem_name FROM member **WHERE height >= 165 AND mem_number > 6;**

-- OR 조건
SELECT mem_id, mem_name FROM member **WHERE height >= 165 OR mem_number > 6;**

-- 사이 조건 (ex. 숫자의 범위) -> BETWEEN A AND B;
SELECT mem_name, height FROM member WHERE height >= 163 AND height <= 165;
SELECT mem_name, height FROM member WHERE height BETWEEN 163 AND 165;

-- 선택 조건 (ex. 문자열, 카테고리) -> IN (A, B, C);
SELECT mem_name, addr FROM member WHERE addr = '경기' OR addr = '전남' OR addr = '경남';
SELECT mem_name, addr FROM member WHERE addr IN('경기', '전남', '경남');

-- 문자열 비교 -> 언더바(_)는 한 글자, 퍼센트(%)는 여러 글자
SELECT * FROM member WHERE mem_name LIKE '우%'; -- (ex. '우'로 시작하는 경우)
SELECT * FROM member WHERE mem_name LIKE '%이%'; -- (ex. '이'가 들어가는 경우)
SELECT * FROM member WHERE mem_name LIKE '__핑크'; -- (ex. 4글자이고, 핑크로 끝나는 경우)

---

7강

1. ORDER BY

1) 역할

: 결과의 값이나 개수에 대해서는 영향을 미치지 않지만, 결과가 출력되는 순서를 조절한다.

2) 형식

SELECT 열_이름
FROM 테이블_이름
WHERE 조건식
ORDER BY 열_이름;

-- 예시1 : 20세 이상 결과를 오름차순으로
SELCET *
FROM member
WHERE member_age > 20
ORDER BY member_age;

-- 예시2 : 20세 이상 결과를 내림차순으로
SELCET *
FROM member
WHERE member_age > 20
ORDER BY member_age DESC:

3) 정렬 방식

• ASC 오름차순 : 작은 것에서 큰 것으로 (기본값)
• DESC 내림차순 : 큰 것에서 작은 것으로

2. LIMIT

1) 역할

: 검색한 결과에서 결과의 개수를 제한한다.

2) 기본 형식

SELECT 열_이름
FROM 테이블_이름
LIMIT 개수;

-- 예시 : member 테이블의 검색 결과 중 세 개의 행만 검색
SELECT *
FROM member
LIMIT 3;

3) 특정 열의 개수를 건너 띄고, 특정 개수의 행을 보고 싶을 때

SELECT 열_이름
FROM 테이블_이름
LIMIT 건너뛸_행의_수, 가져올_행의_수;

-- 예시 : 키를 내림차순으로 정렬하고, 3개를 건너 띄고 2개의 행을 보고 싶을 경우
Select * from member ORDER BY height DESC LIMIT 3, 2;

• 정렬된 결과에서 4번째 행부터 시작하여 2개의 행을 가져온다.
• (첫 번째 숫자는 건너뛸 행의 수, 두 번째 숫자는 가져올 행의 수)

3. DISTINCT

: 여러 개의 중복된 것을 하나만 보여주는 구문이다.

1) 형식

SELECT DISTINCT 열_이름
FROM 테이블_이름

-- 예시 : addr(주소) 중 중복을 제거해서 보여준다.
SELECT DISTINCT addr from member;

4. GROUP_BY

: 그룹으로 묶어주는 역할을 한다.

1) 형식

SELECT 열_이름
FROM 테이블_이름

-- 예시 : market_db의 구매 테이블(buy)에서 회원(mem_id)이 구매한 물품의 개수를 구할 수 있다.
SELECT mem_id, SUM(amount) FROM buy GROUP BY mem_id;

2) GROUP BY와 함께 사용되는 집계함수(aggregate function)

함수명	설명
SUM()	합계
AVG()	평균
MIN()	최소값
MAX()	최댓값
COUNT()	행의 개수
COUNT(DISTINCT)	행의 개수 (중복은 1개만)

3) 집계함수 별 예시

-- 1. 합계(SUM): 각 상품의 총 판매 금액
SELECT prod_name, SUM(price * amount) AS total_sales 
FROM buy 
GROUP BY prod_name;

-- 2. 평균(AVG): 각 상품의 평균 가격
SELECT prod_name, AVG(price) AS average_price 
FROM buy 
GROUP BY prod_name;

-- 3. 최소값(MIN): 각 상품의 최소 가격
SELECT prod_name, MIN(price) AS minimum_price 
FROM buy 
GROUP BY prod_name;

-- 4. 최댓값(MAX): 각 상품의 최대 가격
SELECT prod_name, MAX(price) AS maximum_price 
FROM buy 
GROUP BY prod_name;

-- 5. 행의 개수(COUNT): 각 상품의 구매 건수
SELECT prod_name, COUNT(*) AS purchase_count 
FROM buy 
GROUP BY prod_name;

-- 6. 행의 개수 (중복은 1개만)(COUNT(DISTINCT)): 각 상품의 구매한 회원 수
SELECT prod_name, COUNT(DISTINCT mem_id) AS unique_members 
FROM buy 
GROUP BY prod_name;

4) 특징

• WHERE를 사용할 수 없다.
  • GROUP 함수에서 조건을 사용할 때에는 HAVING을 사용
• COUNT의 경우 Null인 것을 빼고 계산한다.

---

8강

1. INSERT : 데이터 입력

1) 형식

INSERT INTO 테이블 [(열1, 열2, ...] VALUES (값1, 값2, ...);

-- 예시
INSERT INTO member (member_name, member_age, member_id) VALUES ('제임스', 25, 1);

• 테이블 이름 다음에 나오는 열은 생략이 가능하다.
• 열 이름을 생략할 경우에 VALUES 다음에 나오는 값들의 순서 및 개수는 테이블을 정의할 때의 열 순서 및 개수와 동일해야 한다.

2) 여러 줄을 한 줄로 작성

-- 여러 줄 입력
INSERT INTO 테이블_이름 VALUES (값1, 값2, ...);
INSERT INTO 테이블_이름 VALUES (값3, 값4, ...);
INSERT INTO 테이블_이름 VALUES (값5, 값6, ...);

-- 한 줄 입력
INSERT INTO 테이블_이름 VALUES (값1, 값2, ...), (값3, 값4, ...), (값5, 값6, ...);

3) 다른 테이블의 데이터를 한 번에 입력하는 INSERT INTO ~ SELECT

• 다른 테이블에 이미 데이터가 입력되어 있다면, INSERT INTO ~ SELECT 구문을 사용해 해당 테이블의 데이터를 가져와서 한 번에 입력할 수 있다.
• 형식
• INSERT INTO 테이블_이름 (열_이름1, 열_이름2, ...) SELECT 문;
• 예시
• -- 나의 DB에 정보를 복사하기 위해 테이블 생성 CREATE TABLE city_popul ( city_name CHAR(35), population INT ); -- SELECT한 검색을 INSERT하기 INSERT INTO city_popul SELECT Name, Population FROM world.city;

2. AUTO_INCREMENT

1) 테이블 생성 시, 속성으로 지정

CREATE TABLE 테이블_이름(
    열1_이름 열1_데이터_타입 AUTO_INCREMENT PRIMARY KEY,
        열2_이름 열2_데이터_타입
        열3_이름 열3_데이터_타입
);

-- 예시
CREATE TABLE hongong2(
    toy_id INT AUTO_INCREMENT PRIMARY KEY,
    toy_name CHAR(4),
    age INT
);

• 번호가 크게 의미 있는 것은 아니고, 자동 생성만 된다고 하면 AUTO_INCREMENT를 사용한다.

2) 마지막으로 생성된 AUTO_INCREMENT 확인

SELECT LAST_INSERT_ID();

3) AUTO_INCREMENT의 시작 값 조정

ALTER TABLE 테이블_이름 AUTO_INCREMENT = 원하는_숫자;

-- 예시
ALTER TABLE hongong2 AUTO_INCREMENT = 100;

4) AUTO_INCREMENT의 증가하는 수 지정

• 테이블 생성 후에 ‘SET @@auto_increment_increment = 원하는값'를 지정
• 현재 세션 내에서 모든 테이블의 AUTO_INCREMENT 증가값이 3으로 설정된다.

SET @@auto_increment_increment = 원하는값;

-- 예시
CREATE TABLE hongong3(
    toy_id INT AUTO_INCREMENT PRIMARY KEY,
    toy_name CHAR(4),
    age INT
)

ALTER TABLE hongong3 AUTO_INCREMENT = 1000;
SET @@auto_increment_increment = 3;

3. UPDATE : 데이터 수정

: 기존에 입력되어 있는 값을 수정하는 명령이다.

1) 형식

UPDATE 테이블_이름
    SET 열1 = 값1, 열2 = 값2, ...
    WHERE 조건;

-- 예시 : 영문 Seoul을 한국 서울로 변경하는 구문
UPDATE city_popul
    SET city_name = '서울'
    WHERE city_name = 'Seoul';
SELECT * FROm city_popul WHERE city_name = '서울';

-- 예시 : 열을 여러 개 수정할 수도 있다.
UPDATE city_popul
    SET city_name = '뉴욕', population = 0
    WHERE city_name = 'New York';
SELECT * FROm city_popul WHERE city_name = '뉴욕';

-- 예시 : 인구를 만 단위로 바꾸고 싶을 때 (WHERE 절 없이 사용)
UPDATE city_popul
    SET population = population / 10000;
SELECT * FROM city_popul LIMIT 5;

2) 특징

• 주의해야 할 점
• : WHERE 절을 빼게 되면, 모든 행의 값이 다 수정이 된다.

4. DELETE : 데이터 삭제

: 행 데이터를 삭제하는 명령이다.

1) 형식

DELETE FROM 테이블_이름 WHERE 조건;

-- 예시 : city_name이 New로 시작하는 행의 (전체)를 삭제하고 싶은 경우
DELETE FROm city_popul
    WHERE city_name LIKE 'New%';

-- 예시 : city_name이 New로 시작하는 행의 (다섯 개만)를 삭제하고 싶은 경우
DELETE FROm city_popul
    WHERE city_name LIKE 'New%';
    LIMIT 5;

2) 특징

• 주의해야 할 점
• : WHERE 절을 빼게 되면, 모든 행의 값이 다 수정이 된다.