MySQL) 스키마 생성부터 테이블 생성, 데이터 삽입, 조회까지 해보기

첫 번째 문제

baseball team 테이블을 작성

- team : 팀아이디(PK), 지역, 이름, 개설날짜, 전화번호, 홈페이지
- player : 선수번호(PK), 선수이름, 등록일. 포지션, 신장, 팀아이디(FK)

 

조건 : 

         1. TEAM을 두 개만 등록합니다.
         2. 두 개의 TEAM에 선수를 각각 3명씩 등록(추가)합니다.
         3. join 해 보로독 한다.
         4.선수를 입력하면 그 선수의 팀 명과 전화번호, 홈페이지가 출력되도록 합니다.

 

1. 스카마 & 테이블 생성

create database work;
use work;

create table team(
    teamid decimal(4) primary key,
    location varchar(30) not null,
    name varchar(30) not null,
    regdate timestamp,
    phone varchar(30),
    homepage varchar(30)
);

create table player(
    player_id int auto_increment primary key,
    name varchar(20) not null,
    joindate timestamp not null,
    position varchar(20),
    height decimal(5, 1),
    teamid decimal(4),
    foreign key(teamid) references team(teamid)
);
-- alter table player
-- add foreign key(teamid) references team(teamid);

 

2. 데이터 삽입

-- team table
insert into team(teamid, location, name, regdate, phone, homepage)
values(10, '서울시', '젠지', '2010-03-21', '02-234-4523', 'genzi@naver.com');
insert into team(teamid, location, name, regdate, phone, homepage)
values(20, '대구시', 'T1', '2009-02-12', '053-234-1283', 'T1@naver.com');
insert into team(teamid, location, name, regdate, phone, homepage)
values(30, '부산시', 'KT', '2010-03-21', '051-321-9381', 'KT@naver.com');

-- player table
insert into player(name, joindate, position, height, teamid)
values('김투버', '2010-04-23', '미드', '172.3', 10);
insert into player(name, joindate, position, height, teamid)
values('서튜빙', '2009-04-12', '탑', '180.1', 20);
insert into player(name, joindate, position, height, teamid)
values('빙빙이', '2010-05-21', '정글', '169.9', 30);

select * from team;
select * from player;

 

3. 특정 조건으로 조회하기

-- 선수 정보 출력
select p.player_id,
       p.name,
       location,
       phone,
       homepage
from team t inner join player p
    on t.teamid = p.teamid
where p.name = '서튜빙';

-- t1소속 선수만 출력
select t.name,
       p.name,
       p.height,
       p.position
from team t, player p
where t.teamid = p.teamid
    and t.name = 'T1';

---

두 번째 문제

온라인 마켓 TABLE을 작성하라.

- CONSUMER (소비자) : 소비자ID(PK), 이름, 나이
- PRODUCT (상품) : 상품번호(PK), 상품명, 상품가격, 상품설명, 소비자ID(FK)
- CART (장바구니) : 장바구니번호(PK), 수량, 소비자ID(FK), 상품번호(FK)

 

조건 : 

         1. 상품 테이블에 상품을 등록합니다.(개수는 원하는 만큼)
         2. 소비자를 등록합니다.
         3. 소비자가 쇼핑한 상품을 추가합니다.
         4. 쇼핑한 상품을 출력합니다.

 

1. 스카마 & 테이블 생성

create database market;
use market;

create table consumer(
    consumer_id varchar(30) primary key,
    name varchar(30) not null,
    age int not null
);

create table product(
    product_id int primary key,
    prodname varchar(30) not null,
    price decimal(8) not null,
    prodnote varchar(500)
);

create table cart(
    cart_id int primary key,
    prodquan int not null,
    product_id int not null,
    consumer_id varchar(30) not null,
    foreign key(product_id) references product(product_id),
    foreign key(consumer_id) references consumer(consumer_id)
);

 

2. 데이터 삽입

-- 소비자 5명
insert into consumer(consumer_id, name, age) values
('kim@naver.com', '김철수', 25),
('yeehn@nate.com', '이영희', 30),
('minsy@gmail.com', '박민수', 28),
('chaa@naver.com', '최지훈', 35),
('garam@nate.com', '한가람', 22);

-- 상품 8개
insert into product(product_id, prodname, price, prodnote) values
(1000, '노트북', 1200000, '고성능 노트북'),
(1001, '무선 마우스', 25000, '블루투스 마우스'),
(1002, '기계식 키보드', 75000, 'RGB 키보드'),
(1003, '스마트폰', 980000, '최신형 스마트폰'),
(1004, '모니터', 220000, '24인치 IPS 모니터'),
(1005, '헤드셋', 89000, '소음 차단 헤드셋'),
(1006, 'USB 허브', 33000, '다양한 포트 지원'),
(1007, '외장하드', 110000, '1TB 저장장치');

-- 장바구니
insert into cart(cart_id, prodquan, consumer_id, product_id) values
(1, 3, 'kim@naver.com', 1000), (2, 2, 'kim@naver.com', 1001), (3, 1, 'kim@naver.com', 1002),
(4, 5, 'yeehn@nate.com', 1007), (5, 5, 'yeehn@nate.com', 1006),
(6, 1, 'minsy@gmail.com', 1003), (7, 3, 'minsy@gmail.com', 1004),
(8, 3, 'chaa@naver.com', 1000), (9, 3, 'chaa@naver.com', 1005), (10, 2, 'chaa@naver.com', 1006), 
(11, 3, 'chaa@naver.com', 1002), (12, 7, 'chaa@naver.com', 1001),
(13, 5, 'garam@nate.com', 1006);

select * from cart;
select * from consumer;
select * from product;

 

3. 특정 조건으로 조회하기

select c.name as 소비자명,
       p.prodname as 상품명,
       p.prodnote as 상품설명,
       p.price as 가격,
       t.prodquan as 구매수량
from consumer c inner join cart t
	on c.consumer_id = t.consumer_id
inner join product p
	on p.product_id = t.product_id
where c.name = '최지훈';