[혼자 공부하는 SQL] Chapter03. SQL 기본 문법

Chapter03. SQL 기본 문법

SELECT문은 구축이 완료된 테이블에서 데이터를 추출하는 기능

[참고]

SELECT ~ : 조회하고자 하는 컬럼 

FROM ~ : 조회하고 싶은 테이블

WHERE ~ : 조건식

 

[데이터 준비하기]

쿼리 연습을 위해 한빛미디어 사이트의 혼공 자료실 예제소스 다운로드하고

https://www.hanbit.co.kr/support/supplement_survey.html?pcode=B6846155853 

한빛출판네트워크

예제 파일 중 market_db.sql 파일 준비하기

 

[데이터 불러오기]

[데이터 살펴보기]

- 데이터베이스 만들기

#1
DROP DATABASE IF EXISTS market_db;

#2
CREATE DATABASE market_db;

* DROP DATABASE : 데이터 베이스를 삭제하는 문장

* CREATE DATABASE : 새로운 데이터 베이스 생성

 

- 회원 테이블(member) 만들기

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  -- 데뷔 일자
);

* USE : 데이터 베이스 선택

** SQL에서 --(하이픈 두개)는 주석의 의미

 

- 구매 테이블(buy) 만들기

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)
);

* AUTO_INCREMENT : 자동으로 숫자 입력

 

- 테이블에 데이터 삽입하기

# member 데이터 삽입
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');

# buy 데이터 삽입
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);

 

[데이터 조회하기]

USE 문

USE 데이터베이스_이름;

사용할 데이터 베이스 정의

 

SELECT 문

일반적인 구조는 아래와 같음

SELECT 열_이름
FROM 테이블_이름
WHERE 조건식
GROUP BY 열_이름
HAVING 조건식
ORDER BY 열_이름
LIMIT 숫자

# 사용 예시
# 1
SELECT * FROM market_db.member;

# 2
USE market_db;
SELECT * FROM member;

1번과 2번은 동일한 쿼리 문으로 '*' 는 모든 컬럼을 의미함. 만약 특정 컬럼만 추출하고 싶으면

# 이하 코드 모두 market_db에서 실행한다고 가정

# mem_name 만 추출
SELECT mem_name FROM member;

 

[데이터 조회하기_WHERE]

WHERE 문

일반적인 구조는 아래와 같음

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

# 사용 예시
# 1 mem_name이 '블랙핑크' 인 데이터 조회
SELECT * FROM member
WHERE mem_name = '블랙핑크';

# 2 'height' 컬럼의 값이 162 이하인 mem_id, mem_name 컬럼 데이터 조회
SELECT mem_id, mem_name FROM member
WHERE height <= 162;

# 3 'height' 컬럼의 값이 165 이상이고 'mem_number'이 6 초과인 mem_name, mem_number, height 컬럼 데이터 조회
SELECT mem_name, height, mem_number FROM member
WHERE height >= 165 AND mem_number >6;

# 4 'height' 컬럼의 값이 165 이상이거나 'mem_number'이 6 초과인 mem_name, mem_number, height 컬럼 데이터 조회
SELECT mem_name, height, mem_number FROM member
WHERE height >= 165 OR mem_number >6;

# 5 'height' 컬럼의 값이 163 이상이고 165 이하인 mem_name, height 컬럼 데이터 조회
SELECT mem_name, height FROM member
WHERE height >= 163 AND height <= 165;
# 5-1
SELECT mem_name, height FROM member
WHERE height BETWEEN 163 AND 165;

# 6 문자로 표현된 데이터 조회
SELECT mem_name, addr FROM member
WHERE addr = '경기' OR addr = '전남' OR addr = '경남';
# 6-1
SELECT mem_name, addr FROM member
WHERE addr IN('경기', '전남', '경남');

# 7 mem_name 컬럼 문자열 중 '우'로 시작하는 모든 데이터 조회
SELECT * FROM member
WHERE mem_name LIKE '우%';

# 8 앞의 두 글자는 상관 없고 뒤에 두 글자가 핑크인 데이터 조회
SELECT * FROM member
WHERE mem_name LIKE '__핑크';

 

[참고] 서브쿼리

SELECT 안에 SELECT 문으로 조회

# mem_name이 '에이핑크'인 데이터의 height 컬럼 평균 값보다 큰 mem_name, heigt 데이터를 조회
SELECT mem_name, height FROM member
WHERE height > (SELECT height FROM member WHERE mem_name = '에이핑크');

 

[데이터 조회하기_ORDER BY, LIMIT, DISTINCT]

[참고] SELECT 절의 형식은 아래와 같음

SELECT 열_이름
FROM 테이블_이름
WHERE 조건식
GROUP BY 열_이름
HAVING 조건식
ORDER BY 열_이름
LIMIT 숫자

ORDER BY 문

결과 정렬을 위한 쿼리문으로 결과의 값이나 개수에 대해서는 영향을 미치지 않음

# debut_date 기준으로 정렬

SELECT mem_id, mem_name, debut_date
FROM member
ORDER BY debut_date (ASC/DESC);

ASC(오름차순), DESC(내림차순) 옵션을 통해 정렬 기준을 설정해줄 수 있음[defult : ASC]

** ORDER BY 절과 WHERE 절은 함께 사용할 수 있으나 순서에 반드시 유의!!

 

# height가 164 이상인 데이터를 debut_data 기준으로 내림차순 정렬하기

SELECT mem_id, mem_name, debut_date
FROM member
WHERE height >= 164
ORDER BY debut_date DESC;

 

LIMIT 절

출력하는 개수 제한

# height가 164 이상인 데이터를 debut_data 기준으로 내림차순 정렬하고 4개 데이터만 출력하기

SELECT mem_id, mem_name, debut_date
FROM member
WHERE height >= 164
ORDER BY debut_date DESC
LIMIT 4;

 

DISTINCT 절

조회된 결과에서 중복된 데이터 제거하여 1개만 출력할 수 있도록 설정

SELECT DISTINCT addr FROM member ORDER BY addr;

** 출력하고 싶은 열 앞에 작성하여 중복 제거

 

GROUP BY 절

지정한 열의 데이터들을 같은 데이터 끼리 묶어서 결과 추출 즉, 그룹으로 묶어주는 쿼리문

+) 집계함수

GROUP BY와 함께 사용되는 함수

- SUM() : 합계

- AVG() : 평균

- MIN() : 최솟값

- MAX() : 최댓값

- COUNT() : 행의 개수

- COUNT(DISTINCT) : 중복 제외한 행의 개수(중복은 1개만 인정)

# mem_id기준으로 그룹핑 후 amount 개수 합하기
SELECT mem_id, SUM(amount)
FROM buy
GROUP BY mem_id;

#전체 회원이 구매한 물품 개수의 평균 구하기
SELECT AVG(amount)
FROM buy

# 회원 테이블에 연락처가 있는 회원 수 카운트 하기
SELECT COUNT(*) FROM member;

 

HAVING 절

GROUP BY 절과 함께 사용되는 WHERE 절의 개념[집계 함수에 대한 조건 제한]

# 총 구매액이 1000 이상인 회원 출력하기

SELECT mem_id as "회원 아이디", SUM(price*amount) "총 구매 금액" -- 출력하고싶은 열 as "a"를 통해 별칭 설정
FROM buy
GROUP BY mem_id
HAVING SUM(price*amount) > 1000;

 

[데이터 변경하기_INSERT, UPDATE, DELETE]

데이터 입력 : INSERT 문

테이블에 새로운 데이터를 삽입하는 명령어

# 기본 형식
INSERT INTO 테이블 [(열1, 열2, ...)] VALUES (값1, 값2, ...)

# 예시 1 : market_db에 새로운 데이터 생성
USE market_db
CREATE TABLE hongong1 (toy_id INT, toy_name CHAR(4), age INT);
INSERT INTO hongong1 VALUES (1, '우디', 25);

# if age 입력하고 싶지 않다면 자동으로 NULL 값 할당
INSERT INTO hongong1 VALUES (2, '버즈');

# 다른 테이블의 데이터를 한번에 입력하고 싶을 떄
INSERT INTO city_popul
SELECT Name, Population FROM world.city

* 자동으로 증가하느 AUTO_INCREMENT

열 정의할 떄 1부터 증가하는 값을 입력! [주의!AUTO_INCREMENT로 지정하는 열은 PRIMARY KEY로 반드시 지정]

CREATE TABLE hongong2(
	toy_id INT AUTO_INCREMENT PRIMARY KEY,
	toy_name CHAR(4),
    	age INT);
    
# 자동으로 증가하는 부분은 NULL로 채워넣어주면 됨
INSERT INTO hongong2 VALUES (NULL, '슬링키', 22);
INSERT INTO hongong2 VALUES (NULL, '렉스', 21);

# 기존 테이블에서 AUTO_INCREMENT 부분을 100 부터 증가시켜주고 싶다면 ALTER 문을 통해 할당
ALTER TABLE hongong2 AUTO_INCREMENT = 100;
INSERT INTO hongong2 VALUES (NULL, '재남', 35);

# AUTO_INCREMENT 설정 값을 1000부터 시작해서 3배수로 설정해주고 싶다면
CREATE TABLE hongong3(
	toy_id INT AUTO_INCREMENT PRIMARY KEY,
	toy_name CHAR(4),
    	age INT);

ALTER TABLE hongong3 AUTO_INCREMENT = 1000; -- 시작값을 1000으로 설정
SET @@auto_increment_incremet = 3; -- 증가값을 3으로 지정

 

 

데이터 수정 : UPDATE 문

데이터 값이 변경되는 경우 행 데이터 수정할 때 사용

# 기본 형식
UPDATE 테이블_이름
	SET 열1 = 값1, 열2 = 값2, ...
    WHERE 조건;
    
# city_popul 테이블의 도시 이름(city_name) 중에서 'Seoul'을 '서울'로 변경학
USE market_db;
UPDATE city_popul
	SET city_name = '서울'
    WHERE city_name = 'Seoul'; -- WHERE절이 없으면 모든 행의 값이 변경됨
SELECT * FROM city_popul WHERE city_name = '서울';

 

데이터 삭제 : DELETE 문

테이블의 행 데이터 삭제하는 경우 이용

# 기본 구조
DELETE FROM 테이블이름 WHERE 조건;

# city_popul 테이블에서 New로 시작하는 도시를 삭제
DELETE FROM city_popul
WHERE city_name LIKE 'NEW%';

[참고] DROP, TRUNCATE 문

DELETE 문 : 테이블 내에서 하나하나 모두 조회 후 삭제 → 시간이 매우 오래 걸림&빈테이블이 남음

DROP 문 : 테이블 자체 삭제 → 영구 삭제

TRUNCATE 문 : DELETE 문과 동일한 효과