MySQL Chapter2/Chapter3[13-1]

Chapter2

 데이터베이스 모델링

데이터베이스 모델링은 테이블의 구조를 미리 설계하는 개념으로 건축 설계도를 그리는 과정과 비슷합니다.

보통 프로젝트를 진행하기 위해서는 대표적으로 폭포수 모델을 사용하는데, 데이터베이스 모델링은 폭포수 모델의 업무 분석과 시스템 설계 단계에 해당합니다.

 

1. 프로젝트 계획
2. 업무 분석
3. 시스템 설계
4. 프로그램 구현
5. 테스트
6. 유지보수

전체 데이터베이스의 구성도

1. 데이터
2. 테이블
3. 데이터베이스
4. DBMS
5. 열
6. 열 이름
7. 데이터 형식
8. 행
9. 기본 키
10. SQL

데이터베이스 시작부터 끝

데이터 베이스의 구축 절차

1. 데이터베이스 만들기(DB생성)
2. 테이블 만들기(Table 생성)
3. 데이터 입력/수정/삭제하기(CUD)
4. 데이터 조회/활용하기(R)

데이터베이스 개체

테이블은 데이터베이스의 핵심 개체입니다. 하지만 데이터베이스에서는 테이블 외에도 인덱스,뷰,스토어드 프로시저, 트리거, 함수, 커서 등의 개체도 필요로 합니다.

인덱스
:인덱스는 찾아보기와 비슷한 개념입니다. 찾아보기를 통해 먼저 해당 단어를 찾고 바로 옆에 적혀 있는 페이지로 이동하는 효율적인 방법을 사용할 수 있습니다.

예를 들기 전에 테이블을 먼저 생성을 합니다.

-- 데이터 베이스 생성 /인코딩
create database shop_db default character set utf8 collate utf8_bin;
use shop_db;

-- 테이블 생성
create table member
(
	member_id char(8) not null,
    member_name char(5) not null,
    member_addr char(20) null,
    primary key(member_id)
);

-- 값 입력하기
insert into member values('testt', '나훈아', '경기 부천시 중동');
insert into member values('hero', '임영웅', '서울 은평구 증산동');
insert into member values('uyou', '아이유', '인천 남구 주안동');
insert into member values('jyp', '박진영', '경기 고양시 장항동');

create table product
(
	product_name char(4) not null,
    cost int not null,
    make_date date null,
    company char(5) null,
    amount int not null,
    primary key(product_name)
);


-- 값 입력하기
insert into product values('바나나', 1500, '2021-07-01', '델몬트', 17);
insert into product values('카스', 2500, '2022-03-01', 'OB', 3);
insert into product values('삼각김밥', 800, '2023-09-01', 'CJ', 22);


-- 만든 테이블 조회하기
SELECT * FROM member; 
select * from product; 

select member_name, member_addr from member;​

인덱스 예시
ex)

--  member_name이 아이유 인것을 셀렉트
select * from member where member_name = '아이유';

-- 이걸 하고 위 select 아이유를 다시 검색해보면 더 빠르다.
create index idx_member_name on member(member_name);​

뷰
: 뷰를 한마디로 정의한다면 가상의 테이블이라고 할 수 있습니다. 일반 사용자의 입장에서는 테이블과 뷰를 구분할 수가 없습니다. 즉, 일반 사용자는 테이블과 동일하게 뷰를 취급하면 됩니다. 여기서 뷰는 테이블의 링크된 개념이라고 생각하면 됩니다.


ex)

--  view
create view member_view as select * from member;
select * from member_view;
-- 이름 column들만 있는 가상 테이블을 만들어서 조회하는 방법.
create view member_view_name as select member_name from member;
select * from member_view_name;​

스토어드 프로시저
: 스토어드 프로시저는 MySQL에서 제공하는 프로그래밍 기능으로, 여러 개의 SQL문을 하나로 묶어서 편리하게 사용할 수 있습니다. SQL을 묶는 개념 외에 C, 자바, 파이썬과 같은 프로그래밍 언어에서 사용되는 연산식, 조건문, 반복문 등을 사용할 수 있습니다.

ex)

-- member_name이 '나훈아'인 column의 member table 전체 조회
select * from member where member_name = '나훈아';
-- product_name이 '삼각김밥'인 column의 product table의 전체 조회.
select * from product where product_name = '삼각김밥';​

--  스토어드 프로시저 작성,호출
delimiter //
create procedure myProc()
begin
		select * from member where member_name = '나훈아';
		select * from product where product_name = '삼각김밥';
end //
delimiter ;

call myProc();​

여기서 delimiter // ~ delimiter ; 까지 스토어드 프로시저의 내용으로 작성을 합니다. 
안에 내용을 통해 해당 함수 처럼 생긴 명령어를 사용한다면 해당 내용이 실행이 됩니다.

Chapter3

select ~ from ~ where

select문은 구축이 완료된 테이블에서 데이터를 추출하는 기능을 합니다. 따라서 select를 아무리 많이 사용을 한다고 해도 기존의 데이터가 변경이 되질 않습니다.

위 제목과 같이 select문의 가장 기본적인 형태는 select~(column)~from~(table명)~where~(조건식)문입니다.  

select을 하기 이전에 테이블부터 만들어줍니다.

create database market_db default character set utf8 collate utf8_bin;

use market_db;
create table member
(
	mem_id char(8) not null primary key,
    mem_name varchar(10) not null,
    mem_number int not null,
    addr char(2) not null,
    phone1 char(3),
    phone2 char(8),
    height smallint,
    debut_date date
);
drop table member;
create table buy
(
	num int auto_increment not null primary key,
    mem_id char(8) not null,
    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)
);
set foreign_key_checks=1;
select * from member;
select * from buy;


insert into member values('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015.10.19');
insert into member values('APN', '에이핑크', 6, '경기', '031', '32133311', 111, '2014.10.19');
insert into member values('BLK', '블랙핑크', 4, '경님', '055', '22222222', 168, '2013.01.19');
insert into member values('GRL', '소녀시대', 8, '서울', '02', '32132111', 166, '2012.03.19');
insert into member values('ITZ', '잇지', 5, '경남', null, '53212346', 164, '2011.10.19');
insert into member values('MMU', '마마무', 4, '전남', '033', '55555555', 162, '2018.12.19');
insert into member values('OMY', '오마이걸', 7, '서울', '022', '77777777', 161, '2017.11.19');
insert into member values('RED', '레드벨벳', 4, '경북', '032', '55523366', 160, '2016.01.19');
insert into member values('SPC', '우주소녀', 13, '서울', null, '12312311', 169, '2003.03.19');
insert into member values('WMN', '여자친구', 6, '경기', '053', '66666666', 150, '2016.05.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);

기본 조회하기

-- member table의 모든 것을 조회
select * from member;
-- member table에서 mem_name column만 조회
select mem_name from member;​

column명 바꿔서 보여주기

-- column명 바꾸기
select mem_name as 그룹이름 from member; 
-- as 생략가능 
select addr 주소, debut_date "데뷔 날짜",mem_id  from member;​

where문 사용

-- mem_name이 블랙핑크인 column의 행을 조회
select * from member where mem_name = '블랙핑크';​

연산자 사용

-  멤버넘버가 5보다 큰것을 조회
select * from member where mem_number>5;

-- 키가 162보다 작거나 같은 행들에서 mem_id, mem_name column만 출력.
select mem_id, mem_name from member where height <= 162;
-- and조건 키가 165이상이고, 멤버넘버가 6초과인사람의 mem_name, height, mem_number column만 조회.
select mem_name, height, mem_number from member where height >= 165 and mem_number > 6;
-- or조건 키가 165이상이고, 멤버넘버가 6초과인 사람의 mem_name, height, mem_number의 column만 조회
select mem_name, height, mem_number from member where height >= 165 or mem_number > 6;

between~and 사용

-- between~and 평균키 163~165인 회원 조회
select mem_name, height from member where height >=163 and height <=165;
-- 위와 동일
select mem_name, height from member where height between 163 and 165;​

in()사용

-- in()
select mem_name, addr from member where addr = '경기' or addr = '전남' or addr = '경남';
select mem_name, addr from member where addr in('경기', '전남', '경남');​

like사용

-- like
select * from member where mem_name like '우%';
select * from member where mem_name like '__핑크';​

서브 쿼리

-- 서브 쿼리 select을 한번했을 때 select를 한번 더 사용해 조건을 두는 것. 
select mem_name, height from member where height > (select height from member where mem_name = '에이핑크');​

조금 더 깊게 들어가는 select문

select문에는 결과의 정렬을 위한 order by, 결과의 개수를 제한하는 limit, 중복된 데이터를 제거하는 distinct  등을 사용할 수 있습니다. 또, 그룹을 지정해서 결과를 보여주는 group by와 함께 쓰이는 having이 존재합니다.

 order by절

-- order by절 select문의 정렬을 위한 order by문
-- 기본적으로 오름차순으로 되고, 뒤에 desc를 붙여주면 내림차순으로 변경이 된다.
select mem_id, mem_name, debut_date from member order by debut_date;
select mem_id, mem_name, debut_date from member order by debut_date desc;​

-- order by절은 where 뒤에 온 뒤에 정렬을 해주는 것이다.
-- select mem_id, mem_name, debut_date from member order by debut_date where height >= 164;
select mem_id, mem_name, debut_date, height from member where height >= 164 order by debut_date, height ;

-- 키가 164이상인 column의 mem_id,mem_name,debut_date,height를 보여주는데, 키는 내림차순, debut_date는 오름차순으로 정렬
select mem_id, mem_name, debut_date, height from member where height >= 164 order by height desc, debut_date asc;​

limit

-- member table의 위쪽 3개부터 보여주기
select * from member limit 3;

-- 3번쨰부터 2개 (3이 스타트순서가 된다)
select mem_name, height from member order by height desc limit 3,2;​

distinct

-- distinct 요약하고 싶은, 중복을 제거하고 싶은 column의 앞에 붙인다.
select addr from member;
select distinct addr from member;​

group by절  / having

-- group by 절
-- mem_id를 오름차순으로 정렬하고, mem_id,amount column만 보여주기
select mem_id, amount from buy order by mem_id;
-- group by절로 mem_id열을 그룹화 시키고, amount를 집계함수 sum을 사용해 result 를 나타내준다.
select mem_id, sum(amount) from buy group by mem_id;
-- 앞에서 배운 별칭을 통해 결과를 보기 좋게 수정 
select mem_id "회원 아이디", sum(amount) "총 구매 개수" from buy group by mem_id;
-- 회원에 구매한 금액의 총합을 출력, 구매한 금액은 가격(price)*수량(amount)입니다. 합계는 sum()을 사용
select mem_id "회원 아이디", sum(price*amount) "총 구매 금액" from buy group by mem_id;
-- 전체 회원이 구매한 개수(amount)의 평균 구하기. 
select avg(amount) "평균 구매 개수" from buy;
-- 각 회원이 한번 구매 시 평균 몇개를 구매했는지 구하기. 회원(mem_id)별로 구해야 하기 때문에 group by를 사용.
select mem_id, avg(amount) "평균 구매 개수" from buy group by mem_id;
-- 이번에는 회원 테이블(member)에서 연락처가 있는 회원의 수를 카운트
select count(*) from member;


-- having절
select mem_id "회원 아이디" ,sum(price*amount) "총 구매 금액" from buy group by mem_id;
-- 결과 중에 총 구매 금액 1000이상의 회원에게만 사은품 증정시 조건식
-- select mem_id "회원 아이디" ,sum(price*amount) "총 구매 금액" from buy where sum(price*amount) > 1000; group by mem_id;
SELECT 
    mem_id '회원 아이디',
    SUM(price * amount) '총 구매 금액'
FROM
    buy
GROUP BY mem_id
HAVING SUM(price * amount) > 1000;

-- 총 구매액이 큰 것부터 나타내려면 내림차순을 사용하면 됩니다.
SELECT 
    mem_id '회원 아이디',
    SUM(price * amount) '총 구매 금액'
FROM
    buy
GROUP BY mem_id
HAVING SUM(price * amount) > 1000
ORDER BY SUM(price * amount) DESC;

스토어드 함수

스토어드 함수는 앞에서 배운 스토어드 프로시저와 비슷한데, 사용 방법이나 용도가 조금 다릅니다.