SeSAC SQL로 데이터 베이스 다루기 - 1일차 MySQL, DDL, DML
2023, Jul 20
Database
- 데이터베이스(Database, DB)는 ‘데이터의 집합’
- DBMS(Database Management System)은 데이터베이스를 관리하고 운영하는 소프트웨어다.
- DBMS에는 Oracle, MySQL, MariaDB 등이 있다.
- DBMS는 분류에 따라 계층형, 망형, 관계형, 객체지향형, 객체관계형 등의 종류가 있으나, 우리가 사용할 SQL을 포함해서 대부분의 DBMS는 관계형(Relational) DBMS(RDBMS)다.
| [관계형 데이터베이스(RDBMS)란 무엇인가요? | Google Cloud](https://cloud.google.com/learn/what-is-a-relational-database?hl=ko) |
MySQL, MySQL Workbench 설치(mac)
(MacOS BigSur를 사용하고 있어서 버전이 다를수 있습니다.)
MySQL :: Download MySQL Community Server
- MySQL Community Server 8.0.34 선택 후 다운로드(제목 옆 버전 꼭 확인할것!)
- 로그인 필요없음. 아래의 No thanks, just start my download 선택
- 다운로드 받은 패키지 파일 실행
- 설치 진행, 설치 과정 중간에 root 비밀번호 설정 필수
- 아래 링크를 클릭해서 MySQL Workbench 설치
MySQL :: Download MySQL Workbench (Archived Versions)
- Workbench 아이콘을 Applications에 드래그
- 설치완료
MySQL World database설치
- Mac에는 샘플 데이터가 없어서 샘플 데이터를 따로 받아서 설치합니다.
MySQL :: Setting Up the world Database :: 2 Installation
- 화면의 링크 클릭
- world database, sakila database 다운로드
샘플
- 데이터 폴더로 이동해서 sql파일 각각 실행 후 쿼리실행
- 쿼리 실행버튼은 번개모양 ⚡️
스키마(Schema) 생성하기
- Schemas 클릭
- 마우스 우클릭 후 Create Schema 클릭
- 스키마 이름 작성 후 apply
테이블(table) 생성하기(혼자 공부하는 SQL 71p, 72p 실습)
- 만들어진 shop_db의 Table 탭 마우스 우클릭 후 Create Table
- 제약조건에 맞게 테이블 칼럼 생성 후 apply
- 내용 확인 후 apply
데이터 입력하기(75p 실습)
- 생성된 테이블에 마우스 오른쪽 클릭 후 Select Rows 클릭
- Result Grid에 데이터를 입력하고 apply
- 입력한 데이터 확인 후 apply
- (참고) 테이블의 내용을 바꾸고 싶을때
/* 주소가 경기 고양시 장항동인 박진영의 주소를 장향동으로 바꾸기 위해선 다음과 같이 구문을 작성한다.*/
UPDATE shop_db.member
SET member_addr = '경기 고양시 장향동'
WHERE (member_id = 'jyp');
- (참고) 테이블의 내용을 삭제하고 싶을때
/* 박진영을 삭제하고 싶을땐 member_id가 jyp인 값을 찾아 삭제한다.
여기서 where 절에 member_id를 사용하는 이유는 이 테이블에서 member_id가
유일한 값을 가지는 PK이기 때문이다.(유일한 값이기 때문에 데이터를 구분할 수 있음) */
DELETE
FROM shop_db.member
WHERE (member_id = 'jyp');
뷰(View)
뷰는 가상 테이블이다. 뷰는 실제 데이터를 가지고 있지 않고 진짜 테이블에 링크된 개념이다.
윈도우를 예로 들면 아이콘이 있고, 바로가기 아이콘을 생성해서 사용하는것과 비슷하다고 볼수 있다. 뷰를 생성하는 방법은 다음과 같다.
CREATE VIEW member_view -- 이름은 원하는대로 설정
AS
SELECT * FROM member;
생성 후 조회 했을때는 member 테이블과 같이 조회가 가능하다.
실습 - 인터넷 마켓 DB
아래의 sql 다운로드 후 쿼리 실행
샘플데이터의 내용
DROP DATABASE IF EXISTS market_db; -- 만약 market_db가 존재하면 우선 삭제한다.
CREATE DATABASE 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 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, -- 수량
-- buy 테이블의 mem_id는 member 테이블의 mem_id를 가리킨다.
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;
데이터 조회를 위한 SQL문
SELECT 와 FROM
(SQL의 명령문은 대문자로 작성하는것이 좋지만 편의상 소문자로 작성했습니다.)
- select, from에 앞서 SQL문법은 다음과 같은 순서로 작성된다.
- 여기서 실제 작동 순서는 주석의 숫자대로 흘러간다.
- 아래에 적어둔 내용처럼 생각해보면 순서를 알기 쉽다.
SELECT 열_이름 -- 5
FROM 테이블_이름 -- 1
WHERE 조건식 -- 2
GROUP BY 열_이름 -- 3
HAVING 조건식 -- 4
ORDER BY 열_이름 -- 6
LIMIT 숫자 -- 7
어떤 테이블에서(from), 내가 정한 조건으로(where), 그룹화를 하는데(group by),
그룹을 묶기위한 조건을 설정해서(having) 뽑아낸다(select).
뽑아낸 열은 (order by)로 정렬하고, 출력의 개수는 (limit)으로 제한한다.
use market_db; -- 처음에만 사용
-- market_db의 member 테이블 전체를 조회
select *
from member;
-- member 테이블의 mem_name, addr을 조회
select mem_name, addr
from member;
WHERE
- where절에서는 조회의 조건을 정할수 있다.
-- member 테이블의 키가 165이상, 멤버의 숫자가 4명 초과인 정보 전체를 조회
select *
from member
where height >= 165 and mem_number > 4;
-- or은 둘 중 하나만 참이어도 가져온다.
-- where height >= 165 or mem_number > 4;
-- 키가 165이상, 169미만
-- where height >= 165 and height < 169
-- where height between 165 and 168 도 가능하다.
IN
select mem_name, addr
from member
where addr = '경기' or addr = '전남' or addr = '경남';
-- in()을 사용해서 아래와 같이 나타낼 수 있다.
select mem_name, addr
from member
where addr in('경기', '전남', '경남');
LIKE
- 문자열의 일부 글자를 검색하기 위해 사용한다.
-- 첫 글자가 '우'로 시작하는 회원 무엇이든(%) 허용
select *
from member
where mem_name like '우%';
서브쿼리(subquary)
-
서브쿼리(subquery)란 다른 쿼리 내부에 포함되어 있는 SELETE 문을 의미한다.
-
서브쿼리도 종류가 있는데 서브쿼리의 종류는 다음과 같다.
SELECT col1, (SELECT ...) -- 스칼라 서브쿼리(Scalar Subquery): 하나의 컬럼처럼 사용 (표현 용도) FROM (SELECT ...) -- 인라인 뷰(Inline View): 하나의 테이블처럼 사용 (테이블 대체 용도) WHERE col = (SELECT ...) -- 일반 서브쿼리: 하나의 변수(상수)처럼 사용 (서브쿼리의 결과에 따라 달라지는 조건절)
-- 그룹 이름이 에이핑크인 그룹의 평균키보다 큰 그룹의 이름과 평균키
select mem_name, height
from member
where height > (select height from member where mem_name = '에이핑크' );
서브쿼리 응용해보기
-- 이름이 핑크로 끝나는 그룹의 인원 수(6, 4)와 하나라도 같거나, 평균키가 167 이하인 그룹(에이핑크, 블랙핑크, 잇지, 마마무, 오마이걸, 레드벨벳, 우주소녀, 트와이스, 여자친구)의 이름을 출력
select mem_name
from member
where mem_number in(select mem_number from member where mem_name like '%핑크') or height <= 167;
-- 이름이 핑크로 끝나는 그룹들의 각 인원수(6, 4)와 다르고,
-- 평균키가 167 이하인 그룹(잇지, 오마이걸, 우주소녀, 트와이스)의 이름을 출력
select mem_name
from member
where mem_number not in(select mem_number from member where mem_name like '%핑크')
and height <= 167;
-- 이름이 핑크로 끝나는 그룹의 인원 수와 하나라도 같거나, 평균키가 167 이하인 그룹의 이름을 출력
select mem_name
from member
where mem_number IN (select mem_number from member where mem_name like '%핑크');
ORDER BY
select mem_id, mem_name, debut_date
from member
order by debut_date desc; -- desc는 내림차순, asc는 오름차순, 기본은 오름차순이라 적지 않아도 된다.
LIMIT
- limit은 출력의 개수를 입력한 숫자만큼 제한한다.
- limit의 형식은 LIMIT 시작, 개수다.
-- limit에 숫자 1개만 넣으면 개수만 제한한다.
select mem_id, mem_name, debut_date
from member
order by debut_date, desc limit 1;
- limit의 시작을 정할때에, 시작 인덱스는 0이다.
select mem_name, height
from member
order by height desc limit 0, 1; -- 시작지점 0부터 1개만 출력
-- 출력결과를 보면 평균키가 168로 가장 큰 소녀시대가 출력된다.
-- 키가 2번째로 큰 그룹의 키 보다 작은 그룹들의 그룹 명을 출력
select mem_name
from member
where height < (select height from member order by height desc limit 1, 1);
DISTINCT
- 중복을 제거한다.
select distinct addr
from member;
GROUP BY
- 데이터를 그룹으로 묶어주는 역할을 한다.
- GROUP BY와 함께 집계함수를 사용하는데, 주로 사용하는 함수의 종류는 다음과 같다.
| SUM() | 합계 |
|---|---|
| AVG() | 평균 |
| MIN() | 최소 |
| MAX() | 최대 |
| COUNT() | 행의 개수 |
| COUNT(DISTINCT) | 중복이 없는 행의 개수 |
select mem_id, sum(amount)
from buy
group by mem_id; -- mem_id로 그룹화
HAVING
- 집계함수는 조건절(where)에서 사용할 수 없다.
- 이 때에 where 대신 사용하는것이 having이다.
- having은 집계 함수와 관련된 조건을 제한하며, group by 다음에 나온다.
select mem_id, sum(price*amount)
from buy
group by mem_id
having sum(price*amount) > 1000
order by sum(price*amount) desc;
-- 집계함수를 사용하다보면 이름이 길어지기때문에 다음과 같이 사용이 가능하다.
select mem_id, sum(price*amount) buy_sum
from buy
group by mem_id
having buy_sum > 1000
order by buy_sum desc;
실습 문제
-- buy 테이블로 진행
-- 분류 별로 가장 많이 판매된 순으로 정렬해서 출력
select group_name 분류, sum(amount) 판매량
from buy
group by 분류
order by 판매량 desc;
-- 가장 매출이 높은 분류의 상품명을 출력
-- 매출을 분류별로 다 합쳐본다
select distinct prod_name 상품명
from buy
where group_name = (select group_name from buy group by group_name order by
sum(amount*price) desc limit 1);
데이터 변경을 위한 SQL문
INSERT
- 데이터 입력을 위해 사용된다.
- 사용 방법은 다음과 같다.
-- 테이블 생성, toy_id, toy_name, age
create table hongong1 (toy_id INT, toy_name CHAR(4), age INT);
-- 1. 순서와 조건에 맞게 values 안에 입력
insert into hongong1 values(1, "우디", 25);
-- 2. 칼럼 직접 써서 정한 순서에 맞게 values 안에 입력, 이 경우 지정하지 않은 칼럼은 null 처리된다.
insert into hongong1 (toy_name, toy_id, age) values("우디", 2, 25);
-- toy_id는 자동으로 증가하면서 생성된다.
-- AUTO_INCREMENT로 지정하는 열은 반드시 PK로 지정해줘야한다.
create table hongong2 (toy_id INT AUTO_INCREMENT PRIMARY KEY,
toy_name CHAR(4),
age INT);
-- null을 넣어도 자동으로 숫자가 증가하며 생성된다.
insert into hongong2 values(null, "TES1", 1);
-- 값을 넣지 않아도 자동으로 숫자가 증가하며 생성된다.
insert into hongong2 (toy_name, age) values("TES2", 2);
UPDATE
- 기존에 입력되어 있는 값을 수정하는 명령
- 주의할점은 where을 지정하지 않으면 모든 값이 변경될 수 있으므로 where절을 꼭 사용해야한다.
update hongong1
set toy_name = "우디3"
where toy_id = 1;
DELETE
- 테이블의 행 데이터를 삭제해야하는 경우 사용
- UPDATE문과 마찬가지로 where절 사용을 주의해야한다.
delete
from hongong1
where toy_id = 1;
연습문제
-- 가장 돈을 많이 사용한 걸그룹의 그룹명과 인원수 출력
select mem_name 그룹명, mem_number 인원수
from member
where mem_id = (select mem_id from buy group by mem_id order by sum(amount*price) desc limit 1);
-- join을 이용하여 아래와 같이 풀수 있다.
select m.mem_name 그룹명, m.mem_number 인원수
from member m, buy b
where m.mem_id = b.mem_id -- pk인 mem_id로 각 테이블을 join
group by 그룹명, 인원수
order by sum(b.price*b.amount) desc
limit 1;