혼공학습단 11기 - 혼공S (2주차)

공부 기록/혼공학습단

혼공학습단 11기 - 혼공S (2주차)

노루동산 2024. 1. 12. 22:29

혼자 공부하는 SQL

#	진도	기본 미션	선택 미션
1주차 (1/2 ~ 1/7)	Chapter 01 ~ 02	p. 80의 shop_db의 회원 테이블(member)에서 아이유 회원에 대한 정보만 추출한 후 결과 화면 인증하기	데이터베이스 개체 3가지 설명하기
2주차 (1/8 ~ 1/14)	Chapter 03	p. 138의 확인 문제 2번 풀고 인증하기	데이터 입력, 삭제하는 기본 형식 작성하기
3주차 (1/15 ~ 1/21)	Chapter 04	p. 195의 확인 문제 4번 풀고 인증하기	p. 183 [좀 더 알아보기] 손코딩 실행하고 결과화면 인증하기
4주차 (1/22 ~ 1/28)	Chapter 05	p. 226의 market_db의 회원 테이블(member) 생성하고, p. 229 데이터 입력한 후 인증하기	p. 271 확인 문제 4번 풀고 인증하기
5주차 (1/29 ~ 2/4)	Chapter 06	p. 310 인덱스 생성하고 key_name이 PRIMARY로 출력된 결과 화면 캡처하기	인덱스 생성, 제거하는 기본 형식 작성하기
6주차 (2/5 ~ 2/12)	Chapter 07 ~ 08	p. 363 market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거 작성하고 인증하기	p. 402 GUI 응용 프로그램 만들고 인증하기

이번 주 진도

#	진도	기본 미션	선택 미션
2주차 (1/8 ~ 1/14)	Chapter 03	p. 138의 확인 문제 2번 풀고 인증하기	데이터 입력, 삭제하는 기본 형식 작성하기

분량이 챕터 3 뿐이라 짧다고 생각했는데

실습이 많아서 그런지 생각보다 오래 걸렸다.

그렇지만 작아지는 스크롤 바를 보니 한 편으로는 뿌듯하기도 하다.

게다가 상상도 못 했는데 우수 혼공족까지 선정되었다!

공짜 커피도 받았으니 이번 주도 열심히 정리해야겠다!

기본 미션

p. 138의 확인 문제 2번 풀고 인증하기

2. 다음 보기 중에서 각 문항의 빈칸에 들어갈 것을 고르세요.

LIKE, DESC, ORDER BY, DISTINCT, ASC, AND, OR, >=, LIMIT

1) SELECT * FROM member _______ height;

2) SELECT * FROM member _______ 5, 2;

3) SELECT _______ phone1 FROM member;

A. 1) ORDER BY 2) LIMIT 3) DISTINCT

해설

1) 뒤에 열 이름이 오는 것은 ORDER BY 절과 GROUP BY 절이 있기 때문에 선택지에 있는 ORDER BY를 골랐다. 그리고 GROUP BY 절이 오기엔 집계 함수가 없다.

2) 뒤에 숫자가 오는 것은 LIMIT 절이기 때문에 LIMIT을 골랐다.

3) SELECT 다음, 열 이름 이전에 오는 구문은 DISTINCT로 중복 값은 1개만 남기고 제거하는 역할을 한다.

선택 미션

데이터 입력, 삭제하는 기본 형식 작성하기

- 데이터 입력: INSERT

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

- 데이터 삭제: DELETE

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

정리

03 - 1 기본 중에 기본 SELECT ~ FROM ~ WHERE

1. 실습용 데이터베이스 구축

- 우선 카페나 한빛 자료실에서 예제 소스를 다운로드한다.

File -> OpenSQL Script에서 market_db.sql을 선택

- 상세히 살펴보기

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

첫 줄은 market_db가 이미 존재할 경우 그것을 삭제하는 기능을 한다.

두 번째 줄은 새로 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 member로 회원 테이블을 만들었고

괄호() 안은 실질적으로 테이블을 채워 넣는 것인데

워크벤치로 만들 때 열 이름 지정, 데이터 타입 지정, 체크박스 체크 하던 것을 그대로 옮겨 뒀다.

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는 숫자를 (순번대로) 자동으로 입력해 준다는 것

POREIGN KEY는 5장에서 자세히 다룰 예정이라고 한다.

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;

마지막으로 SELECT문을 사용해 데이터를 확인하고 있다.

2. 기본 조회하기: SELECT ~ FROM

- USE 문

USE market_db;

USE 문은 사용할 데이터베이스를 지정하는 역할을 한다.

USE 다음에 원하는 db 이름을 입력한다.

참고로 쿼리를 작성할 때는 세미콜론;을 붙여줘야 한다.

- SELECT 문의 기본 형식

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

이 중 핵심이 되는 것은 SELECT, FROM, WHERE

- SELECT

모든 열 중에서. SELECT addr, debut_date, mem_name 이런 식으로 원하는 열들만 지정할 수도 있다.

height 키 이런 식으로 열의 별명을 지어 줄 수도 있다. 공백이 들어간다면 debut_date "데뷔 일자" 이런 식으로 큰 따옴표를 사용하면 된다.

- FROM

member 테이블 중에서. 원칙적으로는 market_db.member라고 사용해야 하지만, 이미 USE market_db로 데이터베이스를 지정해 줬기 때문에 붙이지 않았다.

3. 특정한 조건만 조회하기: SELECT ~ FROM ~ WHERE

- WHERE

조회하는 결과에 특정한 조건을 추가해서 원하는 데이터만 보고 싶을 때 사용한다.

기본적으로 데이터가 무엇 무엇과 같은지 조회할 때는 =을 사용한다.

문자는 '작은따옴표' 안에 쓰면 된다.

관계 연산자

관계 연산자에는 >, <, >=< <=, = 등이 있다.

데이터의 범위를 지정할 때 사용한다.

논리 연산자

여러 조건을 만족하거나(AND), 여러 조건 중 하나를 만족(OR) 하는 데이터를 찾을 때 사용한다.

BETWEEN ~ AND

특정 범위에 있는 데이터를 구할 때는 왼쪽과 같은 표현 대신 BETWEEN ~ AND를 사용할 수도 있다.

IN()

여러 값 중 하나를 만족하는 데이터를 구할 때는 왼쪽과 같은 표현 대신 IN()을 사용할 수도 있다.

LIKE

문자열의 일부 글자를 검색할 때는 LIKE를 사용한다.

이 조건은 첫 글자가 '우'이고 그 뒤는 무엇이든이라는 뜻이다.

여기서 %는 글자 수는 신경 쓰지 않는데, 한 글자와 매치하기 위해서는 언더바(_)를 사용한다.

예시의 '우주소녀'를 찾기 위해서는 '우___'라고 검색하면 될 것이다.

03 - 2 좀 더 깊게 알아보는 SELECT 문

1. ORDER BY 절

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

앞에서 나왔던 SELECT 문의 형식 중 ORDER BY에 대해서 알아보자.

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

기본적으로 오름차순이지만

debut_date 뒤에 DESC를 입력하면 내림차순으로 정렬한다.

ORDER BY와 WHERE 절은 같이 사용할 수 있다.

여기서 WHER 절과 ORDER BY 절의 순서는 위의 SELECT 문의 형식을 지켜야 한다.

순서가 바뀌면 오류가 발생한다.

정렬 조건이 동일한 경우 다음 조건을 추가할 수도 있다.

조건을 살필 순서대로 ,를 넣어 작성하면 된다.

- LIMIT 절

LIMIT 절은 출력하는 개수를 제한한다.

전체 중 앞에서 3건만 조회할 수 있다.

LIMIT 절은 ORDER BY 절 뒤에 와야 한다.

리밋에도 옵션을 줄 수 있다.

이 구문의 뜻은 3번째 줄부터 2건이라는 뜻이다.

- DISTINCT 절

DISTINCT는 조회된 결과에서 중복된 데이터를 1개만 남긴다.

2. GROUP BY 절

GROUP BY 절은 데이터를 그룹으로 묶어주는 역할을 한다.

예를 들어 회원들이 각각 물건을 구매한 개수를 구한다고 할 때

GROUP BY를 사용하여 묶을 기준을 정해두고

값을 어떻게 묶을 건지는 SUM()과 같은 집계 함수(aggregate function)를 사용한다.

주로 사용되는 집계 함수

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

예제에서는 구매한 물건의 합을 구하기 위해 SUM()을 사용하였다.

이번에는 구매한 총액을 구해보자.

연산식을 사용해 price 열과 amount 열의 데이터를 곱한 값을 얻었다.

책에는 다른 집계 함수의 예시도 있지만 사용 방법은 동일하기 때문에 생략한다.

- HAVING 절

HAVING 절은 WHERE 절과 비슷한 개념이지만, 집계 함수에 대하여 조건을 제한할 때 쓰인다.

HAVING 절은 GROUP BY 절 뒤에 위치해야 한다.

03 - 3 데이터 변경을 위한 SQL 문

1. 데이터 입력: INSERT

INSERT는 테이블에 데이터를 삽입하는 명령이다.

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

USE market_db;
CREATE TABLE hongong1 (toy_id INT, toy_name CHAR(4), age INT);
INSERT INTO hongong1 VALUES (1, '우디', 25);

테이블을 생성하고 데이터를 넣었다.

위의 기본 형태와 조금 다르긴 한데, 테이블 이름 다음에 나오는 열은 생략이 가능하기 때문이다.

대신 테이블을 정의했을 때의 열 순서, 개수와 동일해야만 한다.

INSERT INTO hongong1 (toy_name, toy_id) VALUES ('버즈', 2);

열을 지정해 주면

데이터를 넣을 열만, 원하는 순서로 쓸 수 있다.

참고로 데이터를 넣지 않은 열에는 NULL 값이 들어가게 된다.

2. 자동으로 증가하는 AUTO_INCREMENT

CREATE TABLE hongong2 (
	toy_id INT AUTO_INCREMENT PRIMARY KEY,
    toy_name CHAR(4),
    age INT);
    
INSERT INTO hongonh2 VALUES (NULL, '보핍', 25);
INSERT INTO hongonh2 VALUES (NULL, '슬링키', 22);
INSERT INTO hongonh2 VALUES (NULL, '렉스', 21);

AUTO_INCREMENT를 사용하면 자동으로 숫자를 채워 넣어 준다.

대신 이 열은 기본 키로 지정해야 한다.

이후 자동으로 숫자를 채울 자리는 INSERT 할 때 NULL로 비워놓으면 된다.

SELECT LAST_INSERT_ID();

숫자를 채워 넣다가 지금 어디까지 올라갔는지 확인하고 싶으면 해당 구문을 사용한다.

제일 최신으로 넣어진 ID를 확인 가능하다.

이 숫자를 중간에 바꾸고 싶다면?

ALTER TABLE hongong2 AUTO_INCREMENT = 100;

ALTER TABLE 구문을 사용하자.

4번째 값의 id가 100이 된 것을 볼 수 있다.

처음부터 시작하는 숫자를 지정하려면? & 증가값을 1 말고 다른 값으로?

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;

이번엔 처음부터 값을 1000으로 설정하고,

시스템 변수인 @@auto_increment_increment를 3으로 변경하였다.

이렇게 초기 값과 증가값 또한 설정할 수 있다.

*시스템 변수란 MySQL에서 자체적으로 가지고 있는 설정값이 저장된 변수다.

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

- 다른 테이블에 이미 입력 된 데이터를 가져와 한 번에 입력할 수 있다.

우선 잘 모르는 테이블의 구조를 살펴보자.

world 데이터베이스의 city 테이블은 기본 제공되는 테이블이다.

우선 잘 모르는 테이블은 DESC 명령을 사용하면 구조가 출력 되어 테이블을 어떻게 만들었는지 추측 가능하다.

한 번 여기서 이름, 인구만 가져올 테이블을 새로 만든다고 해보자.

CREATE TABLE city_popul (
	city_name CHAR(35),
    population INT);
    
INSERT INTO city_popul
	SELECT Name, Population FROM world.city;

city_popul이라는 새로운 테이블을 만들었다.

그리고 원래는 INSERT 문에서 열과 데이터를 입력해야 하는 자리에 SELECT 문을 넣는다.

21:48:50 INSERT INTO city_popul SELECT Name, Population FROM world.city 4079 row(s) affected Records: 4079 Duplicates: 0 Warnings: 0 0.016 sec

엄청난 양의 데이터가 넣어졌다는 로그를 확인 가능하다.

4. 데이터 수정: UPDATE

우선 UPDATE 기능이 기본적으로 막혀있기 때문에 허용할 필요가 있다.

Edit -> Preferences로 들어간다.

하단의 Safe Updates를 해제하고 확인, 워크벤치를 재실행한다.

- UPDATE 문의 기본 문법

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

WHERE 절의 조건에 해당하는 행의

원하는 열 값을 변경한다.

해당 예제에선 city_name이 Seoul인 행의 city_name 열의 데이터를 '서울'로 바꿨다.

이번엔 여러 값을 변경해보았다.

- 참고로 WHERE 절을 빼면 모든 데이터가 일괄적으로 바뀐다.

5. 데이터 삭제: DELETE

- DELETE 문의 기본 문법

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

조건에 해당하는 행을 삭제한다.

DELETE FROM city_popul
	WHERE city_name LIKE 'New%'
    LIMIT 5;

예제에서는 앞에 New가 붙는 city_name을 가진 데이터를 삭제하였는데,

여기서 뒤에 LIMIT 절이 붙어있는 걸 확인 가능하다.

이는 5개의 데이터만 삭제하겠다는 뜻이다.

이전 글 보기

1주차

https://mountain-noroo.tistory.com/94

혼공학습단 11기 - 혼공S (1주차)

혼자 공부하는 SQL # 진도 기본 미션 선택 미션 1주차 (1/2 ~ 1/7) Chapter 01 ~ 02 p. 80의 shop_db의 회원 테이블(member)에서 아이유 회원에 대한 정보만 추출한 후 결과 화면 인증하기 데이터베이스 개체 3가

mountain-noroo.tistory.com