[ 멋쟁이사자처럼부트캠프 그로스마케팅 부트캠프 ] SQL 기초 문법

1. SQL 용어 정리

1) 데이터베이스 (Database)

• 데이터를 체계적으로 저장하고 관리하는 공간
• 여러 개의 테이블(Table)과 관계(Relationship)를 포함할 수 있음

2) 스키마 (Schema)

• 데이터베이스의 구조(테이블, 뷰, 인덱스 등)를 정의하는 설계도
• 데이터베이스 내에서 테이블의 구성, 관계 및 제약 조건을 포함
• 데이터베이스가 하나의 가방이라면, 스키마는 가방 안 여러 주머니. 데이터베이스 내 데이터를 목적에 맞게 관리하는 논리도구.

3) 테이블 (Table)

• 데이터를 행(Row)과 열(Column)으로 저장하는 구조
• 관계형 데이터베이스의 기본 단위

4) 레코드 (Record)

•  테이블에서 하나의 데이터 행(Row)을 의미
• 테이블의 한 개 데이터 항목을 표현

 

2. SQL 기초 문법

1) 데이터베이스 생성 ( CREATE DATABASE )

CREATE DATABASE mydb;  --mydb 데이터베이스 생성

 

2) 데이터베이스 사용 ( USE )

USE mydb;   -- 사용 중인 데이터베이스를 mydb로 변경

 

3) 테이블 생성 ( CREATE TABLE )

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 자동 증가하는 기본 키(중복 허용 x)
    name VARCHAR(10),                   -- 이름(문자열, 10자까지 저장 가능)
    position VARCHAR(50)                -- 직급(문자열, 50자까지 저장 가능)
    );

 

4) 데이터 삽입 ( INSERT INTO )

INSERT INTO employees (name, position)   -- 이름, 직급 컬럼에 데이터 삽입
VALUES
('김철수', '디자이너'),
('이영희', '마케팅'),
('박민수', '개발자'),
('최수영', 'HR 매니저');

• id 컬럼의 경우 자동 증가하는 기본 키이기 때문에 따로 데이터를 삽입하지 않음

5) 데이터 조회 ( SELECT )

SELECT * FROM employees;    -- 모든 데이터 조회 (*는 모든 데이터를 의미)

SELECT * FROM employees WHERE position = '개발자';   -- position이 개발자인 레코드만 조회

 

6) 데이터 수정 ( UPDATE )

-- 특정 직원의 position 수정
UPDATE employees                 -- employees table을 업데이트
SET position = '시니어 개발자'   -- 바꿀 컬럼의 값 설정
WHERE name = '박민수'            -- 해당 레코드 설정 (name이 박민수인 레코드로 설정)

-- 여러 컬럼 동시에 수정
UPDATE employees
SET position = '그로스 마케터', name = '이영현'
WHERE id = 2;

 

7) 데이터 삭제 ( DELETE )

-- 특정 직원 삭제
DELETE FROM employees
WHERE name = '김철수';

 

8) 테이블 전체 데이터 삭제 ( TRUNCATE )

TRUNCATE TABLE employees;   -- TRUNCATE는 모든 데이터를 삭제하지만 테이블 구조는 유지됨

 

9) 테이블 삭제 ( DROP TABLE )

DROP TABLE employees;   -- 테이블이 완전히 삭제됨

 

10) 칼럼 추가 ( ADD COLUMN )

ALTER TABLE employees ADD COLUMN email VARCHAR(100) AFTER name;
-- TABLE 수정 시 ALTER TABLE 문 
-- employees 테이블에서 email 칼럼(자료형 VARCHAR(100))을 name 칼럼 다음에 추가하라

 

11) 칼럼 삭제 ( DROP COLUMN )

ALTER TABLE employees DROP COLUMN department;
-- employees 테이블에서 department 칼럼을 삭제하라

 

12) 칼럼 이름 변경 ( CHANGE COLUMN )

ALTER TABLE employees CHANGE COLUMN salary monthly_salary VARCHAR(50);
-- employees 테이블의 salary 칼럼을 monthly_salary 칼럼(자료형(VARCHAR(50))으로 바꾸어라

 

13) 칼럼 데이터 타입 변경 ( MODIFY COLUMN )

ALTER TABLE employees MODIFY COLUMN monthly_salary INT;
-- employees 테이블의 monthly_salary 칼럼의 자료형을 INT로 바꾸어라

 

14) 기본 키 변경 ( ADD/DROP PRIMARY KEY )

ALTER TABLE employees DROP PRIMARY KEY;
-- employees 테이블의 PRIMARY KEY 제거
-- PRIMARY KEY는 테이블 당 한 칼럼에만 적용할 수 있으므로 굳이 PRIMARY KEY가 어떤 칼럼인지 설정하지 않음

ALTER TABLE employees ADD PRIMARY KEY (email);
-- employees 테이블의 email 칼럼에 PRIMARY KEY 설정

• PRIMARY KEY와 함께 AUTO_INCREMENT가 설정되어 있는 경우 AUTO_INCREMENT부터 제거해야 함
• AUTO_INCREMENT 부여 조건이 PRIMARY KEY이기 때문, 먼저 제거하지 않으면 PRIMARY KEY 또한 제거되지 않음

ALTER TABLE employees MODIFY id INT;
-- id 칼럼의 자료형을 INT로 수정 ( INT AUTO_INCREMENT -> INT )

 

15) 테이블 이름 변경 ( RENAME TO )

ALTER TABLE employees RENAME TO staff;
-- employees 테이블의 이름을 staff로 변경

 

16) 인덱스 삭제 ( ADD/DROP INDEX )

ALTER TABLE staff ADD INDEX idx_name (name);
-- name 칼럼에 인덱스 추가, 이 인덱스 이용 시 검색속도 증가
ALTER TABLE staff DROP INDEX idx_name;
-- 인덱스 삭제

 

 

3. 추가 기능

 

1) 기본적인 제약조건

• PRIMARY KEY: 고유한 값 (중복 불가, NULL 불가)
• UNIQUE: 중복 불가
• NOT NULL: NULL 값 허용 안함
• DEFAULT: 기본값 설정
• AUTO_INCREMENT: 자동 증가 숫자

2) 정렬 ( ORDER BY )

SELECT * FROM employees ORDER BY name ASC;  -- employees 표의 모든 데이터를 name 칼럼 값을 중심으로 오름차순 정렬 (내림차순은 DESC)

 

3) 그룹화 ( GROUP BY )

SELECT name, position FROM employees GROUP BY name;   -- name 칼럼 기준으로 그룹화

 

4) 데이터 집계 함수

• COUNT( ): 행의 개수 계산

SELECT COUNT(position) FROM employees WHERE position = '개발자'
-- position 칼럼의 데이터가 '개발자'인 레코드의 개수 계산

• SUM( ): 숫자 컬럼의 합계

SELECT SUM(salary) FROM employees;
-- salary 칼럼의 데이터(정수형)의 합 계산

• AVG( ): 평균값

SELECT AVG(salary) FROM employees;
-- salary 칼럼의 데이터(정수형)의 평균 계산

• MAX( ) / MIN( ): 최댓값과 최솟값

SELECT MAX(salary), MIN(salary) FROM employees;
-- salary 칼럼 데이터의 최댓값과 최솟값 계산

 

4. 예제

0) 고객 세그먼트 별 매출 분석

• 문제: 각 고객별 평균 주문 금액(Average Order Value, AOV)을 계산하고, 가장 높은 평균 주문 금액을 가진 고객을 찾는 SQL 쿼리 작성하기 ( AOV 계산: total_revenue / total_orders )
• 고객 세그먼트 별 매출 분석 표

customer_id	total_orders	total_revenue
1	15	1,200,000
2	10	700,000
3	20	1,800,000
4	8	500,000
5	25	2,000,000
6	12	900,000
7	18	1,500,000
8	14	1,200,000
9	22	2,400,000

 

1) DATABASE와 TABLE 만들기

CREATE DATABASE mydb;
use mydb;

CREATE TABLE top_revenue (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    total_orders INT,
    total_revenue INT
    );

INSERT INTO top_revenue ( total_orders, total_revenue )
VALUES
( 15, 1200000 ),
( 10, 700000 ),
( 20, 1800000 ),
( 8, 500000 ),
( 25, 2000000 ),
( 12, 900000 ),
( 18, 1500000 ),
( 14, 1200000 ),
( 22, 2400000 );

 

2) TABLE 확인

SELECT * FROM top_revenue;

반환값

 

3) 고객별 평균 주문 금액 계산 후 가장 높은 고객 찾기

SELECT customer_id, (total_revenue / total_orders) AS avg_revenue
FROM top_revenue
ORDER BY avg_revenue DESC;

반환값

 

5. 덧붙이는 말

• '쿼리'는 데이터베이스에 정보를 요청하거나 조작하는 명령어를 의미함.
• 줄바꿈 혹은 공백은 쿼리의 논리적인 종결을 의미하지 않음. 즉 ;가 나오기 전까지 한 쿼리임.
• 쿼리 종결 시 ; 대신 \G를 입력하면 데이터가 세로로 정렬되어 출력됨.
• 명령어를 대문자로 적는 이유는 가독성을 위해서임. sql은 명령어에서 대소문자 구분하지 않음.