25.12.10 데이터베이스, SELECT문

1. 테이블 설계하기 

■ 회원 테이블(member)

 

열 이름(한글)	영문 이름	데이터 형식	최대 길이	Not Null
아이디(기본 키)	member_id	문자(CHAR)	8	Yes
회원 이름	member_name	문자(CHAR)	5	Yes
주소	member_addr	문자(CHAR)	20	No

 

■ 제품 테이블(product)

열 이름(한글)	영문 이름	데이터 형식	최대 길이	Not Null
제품 이름(기본 키)	product_name	문자(CHAR)	4	Yes
가격	cost	숫자(INT)	-	Yes
제조일자	make_date	날짜(DATE)	-	No
제조회사	company	문자(CHAR)	5	Yes
남은 수량	amount	숫자(INT)	-	No

---

2. 테이블 생성하기 (MySQL Workbench)

■ Step 01. 테이블 생성 메뉴 열기

• 왼쪽 메뉴에서 Schemas 패널 열기
• shop_db 클릭
• Tables 우클릭
• Create Table 선택

 

■ Step 02. 테이블 이름 입력

• 화면 중앙에 Table Name 입력
  예) member
• Column Name 첫 줄 더블 클릭해서 열 입력 시작

 

■ Step 03. 첫 번째 열 입력 (member_id)

• Column Name : member_id
• Datatype : CHAR(8)
• PK 체크
• NN(Not Null) 체크

 

■ Step 04. 나머지 열 추가

(두 번째 줄부터 입력)

• member_name / CHAR(5) / NN 체크
• member_addr / CHAR(20)

→ 입력이 끝났으면 화면 오른쪽 아래 Apply 클릭

 

■ Step 05. 자동 생성된 SQL 확인

 

■ Step 06. 제품 테이블 생성(product)

위의 01~05 단계 그대로 반복해서 product 테이블 생성
교재의 표 그대로 열을 입력하면 됨.

 

---

3. 데이터 입력하기

■ Step 01. Select Rows 실행

• Tables 목록에서 member 우클릭
• Select Rows - Limit 1000 선택
  → 빈 그리드(표) 화면이 나타남

 

■ Step 02. 데이터 입력

member_id	member_name	member_addr
tess	나훈아	경기 부천시 중동
hero	임영웅	서울 은평구 증산동
iyou	아이유	인천 남구 주안동
jyp	박진영	경기 고양시 장항동

 

제품 이름	가격	제조일자	제조회사	남은 수량
바나나	1500	2021-07-01	델몬트	17
카스	2500	2022-03-01	OB	3
삼각김밥	800	2023-09-01	CJ	22

입력이 끝나면 오른쪽 아래 Apply 클릭

 

■ Step 03. 자동 생성된 INSERT SQL 확인

 

---

4. 데이터 수정하기 (UPDATE)

   ■ Step 01. 데이터 수정하기 위해 데이터 추가하기

• Schemas 패널 → member 테이블 우클릭
• Select Rows – Limit 1000 선택
• NULL 칸을 클릭하여 연습용 데이터 추가 
• 입력 후 Apply 연속 2회 클릭 → Finish

→ 입력된 데이터가 테이블에 반영됨.

 

■ Step 02. 수정하기

• 수정할 셀을 클릭
• 내용을 직접 수정 
• 변경 후 아래쪽 Apply 버튼 클릭
• SQL UPDATE 문 확인 후 Finish

 

■ Step 03. 자동 생성된 UPDATE SQL 확인

 

---

▶  SQL의 대소문자 정리 

• SQL 문장은 대소문자를 구분하지 않는다.
• SQL 문장의 끝에는 세미콜론(;)을 붙이는 것이 기본이다.
• 일부 상황에서는 세미콜론이 없어도 되지만, 항상 붙인다고 생각하는 게 편하다.

 

---

데이터 베이스 구축

• market_db.sql 파일 내용

DROP DATABASE IF EXISTS market_db; -- 만약 market_db가 존재하면 우선 삭제한다.
CREATE DATABASE market_db;

USE market_db;
CREATE TABLE member -- 회원 테이블
( mem_id  		CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  mem_name    	VARCHAR(10) NOT NULL, -- 이름
  mem_number    INT NOT NULL,  -- 인원수
  addr	  		CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  phone1		CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2		CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height    	SMALLINT,  -- 평균 키
  debut_date	DATE  -- 데뷔 일자
);
CREATE TABLE buy -- 구매 테이블
(  num 		INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
   mem_id  	CHAR(8) NOT NULL, -- 아이디(FK)
   prod_name 	CHAR(6) NOT NULL, --  제품이름
   group_name 	CHAR(4)  , -- 분류
   price     	INT  NOT NULL, -- 가격
   amount    	SMALLINT  NOT NULL, -- 수량
   FOREIGN KEY (mem_id) REFERENCES member(mem_id)
);

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015.10.19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016.08.08');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015.01.15');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울', NULL, NULL, 160, '2015.04.21');
INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울', '02', '44444444', 168, '2007.08.02');
INSERT INTO member VALUES('ITZ', '잇지', 5, '경남', NULL, NULL, 167, '2019.02.12');
INSERT INTO member VALUES('RED', '레드벨벳', 4, '경북', '054', '55555555', 161, '2014.08.01');
INSERT INTO member VALUES('APN', '에이핑크', 6, '경기', '031', '77777777', 164, '2011.02.10');
INSERT INTO member VALUES('SPC', '우주소녀', 13, '서울', '02', '88888888', 162, '2016.02.25');
INSERT INTO member VALUES('MMU', '마마무', 4, '전남', '061', '99999999', 165, '2014.06.19');

INSERT INTO buy VALUES(NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES(NULL, 'BLK', '맥북프로', '디지털', 1000, 1);
INSERT INTO buy VALUES(NULL, 'APN', '아이폰', '디지털', 200, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '아이폰', '디지털', 200, 5);
INSERT INTO buy VALUES(NULL, 'BLK', '청바지', '패션', 50, 3);
INSERT INTO buy VALUES(NULL, 'MMU', '에어팟', '디지털', 80, 10);
INSERT INTO buy VALUES(NULL, 'GRL', '혼공SQL', '서적', 15, 5);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 2);
INSERT INTO buy VALUES(NULL, 'APN', '청바지', '패션', 50, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 1);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 4);

SELECT * FROM member;
SELECT * FROM buy;

 

 

 

• 회원 테이블

 

• IN( )

 

 

---

SELECT문

SELECT 열_이름     → 조회할 컬럼
FROM 테이블_이름   → 조회할 테이블
WHERE 조건식       → 조건 
GROUP BY 열_이름   → 그룹화 
HAVING 조건식      → 그룹 조건 
ORDER BY 열_이름   → 정렬 
LIMIT 숫자         → 결과 개수 제한

■ ORDER BY 절

• 조회된 데이터를 정렬하는 기능
• 기본은 오름차순(ASC)
• 내림차순(DESC) 사용 가능

예)

 

SELECT mem_id, mem_name, debut_date
   FROM member
   ORDER BY debut_date DESC;

 

---

■ LIMIT 절

• 결과에서 가져올 행(row)의 개수를 제한

예)

SELECT * 
   FROM member 
   LIMIT 3;

---

■ DISTINCT

• 중복된 값을 제거하고 고유한 값만 조회

 

예)

SELECT DISTINCT addr FROM member;

---

■ GROUP BY

• 특정 기준으로 데이터를 묶어 집계(합계, 평균 등)를 구할 때 사용

<집계함수>

함수명	설명
SUM()	합계를 구합니다.
AVG()	평균을 구합니다.
MIN()	최소값을 구합니다.
MAX()	최대값을 구합니다.
COUNT()	행의 개수를 셉니다.
COUNT(DISTINCT)	중복을 제외한 개수를 셉니다. (중복은 1개만 인정)

 

예)

SELECT mem_id, SUM(amount) FROM buy GROUP BY mem_id;

 

예) 별칭 사용

SELECT mem_id "회원 아이디", SUM(amount) "총 구매 개수"
   FROM buy GROUP BY mem_id;

 

---

■ HAVING

1) HAVING 절이 필요한 이유

집계 함수(SUM, AVG, COUNT 등)는 WHERE 절에서 사용할 수 없다.
즉, 아래처럼 쓰면 오류가 발생한다.

WHERE SUM(price * amount) > 1000 ← ❌ 오류!

 

     왜?

• WHERE 절은 그룹화(GROUP BY) 전에 행(row) 단위 조건을 검사함
• 하지만 집계 함수는 그룹화 후에 계산되는 값
  ➡ 따라서 WHERE에서 집계 함수를 쓸 수 없음

 

2) 집계 함수 조건을 사용할 때는 HAVING 절 사용

집계 결과에 조건을 걸고 싶을 때는 반드시 HAVING을 사용해야 함.

 

예)

SELECT mem_id, SUM(price * amount) FROM buy GROUP BY mem_id HAVING SUM(price * amount) > 1000;

 

✔ GROUP BY로 묶은 그룹 단위 결과에 조건을 적용
✔ WHERE은 “행 조건”, HAVING은 “그룹 조건”

 

 

3) HAVING 절은 반드시 GROUP BY 뒤에 위치

SELECT … 
FROM … 
WHERE … 
GROUP BY … 
HAVING … 
ORDER BY …

 

 

4) HAVING + ORDER BY

 

총 구매액이 1000원 이상인 회원을 구하고, 그 중에서 구매 금액이 큰 순서대로 나열하기

예)

SELECT mem_id "회원 아이디", SUM(price * amount) "총 구매 금액"
   FROM buy 
   GROUP BY mem_id 
   HAVING SUM(price * amount) > 1000 
   ORDER BY SUM(price * amount) DESC;