5일차 Shop_DB.sql (4일차 복습, 이중조인, 함수)

 

-- db 생성
create database shop;

-- db 선택
use shop;

-- 테이블 생성
create table student
(
     id char(10) primary key not null, -- 학번 (8자리 고정문자, 기본키)
    name varchar(10) not null, -- 이름 (10자리 가변문자)
    dept varchar(20) not null -- 학과 (2자리 가변문자)
);

-- 테이블 삭제
drop table student;

-- 데이터 입력
insert into student(id, name, dept) values('2014071023', '최규식', '스마트산업');
insert into student(id, name, dept) values('2014072008', '박태수', '스마트산업');
insert into student(id, name, dept) values('2014071020', '정상혁', '스마트산업');

-- 데이터 검색
select * from student;

-- 데이터 삭제
delete from student where id='2014071023'; -- 기본키인 id로만 삭제가 가능하며 다른 키로 삭제하려면 별도 설정해줘야 한다.

-- 데이터 수정
update student set dept='임베디드공학' where id='2014071023';
update student set dept='스마트산업' where id='2014071023';


-- ----------------------------------------------------------------------------------------

-- 상품 테이블 생성
create table goods
(
     g_id char(8) not null primary key, -- 상품코드
    g_name varchar(20) not null, -- 상품명
    maker varchar(20) not null, -- 가격
    price int not null, -- 가격
    color varchar(10) null -- 상품색상
);

-- 상품 입력
insert into goods values('00000001', '갤럭시노트4', '삼성전자', 957000, 'Black');
insert into goods values('00000002', '아이폰6+', '애플', 790000, null);
insert into goods(g_id, g_name, maker, price) values('00000003', 'G3 cat.6', 'LG전자', 924000);
-- 테이블명(칼럼명) 을 써주면 작성된 칼럼에 해당되는 값만 작성해주면 된다. (디폴트값은 모든 값 작성)

-- 상품 검색
select * from goods;

-- 주문 테이블 생성
create table orders
(
     id char(10), -- 학생 학번
    g_id varchar(8) not null, -- 상품코드
    ea int not null, -- 상품개수 (정수)
    totalPrice int not null, -- 총금액 (정수)
    foreign key(id) references student(id), -- 참조키
    foreign key(g_id) references goods(g_id) -- 참조키
);

-- 주문 테이블 삭제
drop table orders;

-- 데이터 입력
insert into orders values('2014071023', '00000001', 2, 1914000);
insert into orders values('2014071020', '00000002', 1, 790000);

-- 데이터 검색
select * from orders;

-- 학생명, 상품번호, 개수, 총액
select s.name, o.g_id, o.ea, o.totalPrice
from student s, orders o
where s.id=o.id;

-- 학생명, 상품명, 개수, 총액
select s.name, g.g_name, o.ea, o.totalPrice
from student s, orders o, goods g
where s.id=o.id
and o.g_id=g.g_id;

-- 정렬 (이름을 가나다 순으로)
select * from student order by name; -- 오름차순 (디폴트값 : asc)
select * from student order by name desc; -- 내림차순

-- 합계
select sum(totalPrice) as 총구매금액 from orders; -- as는 생략가능

-- 평균
select avg(price) as 가격평균 from goods;

-- 최대값
select max(price) as 최대가격 from goods;

-- 최소값
select min(price) as 최소가격 from goods;

-- 개수
select count(g_id) as 상품개수 from goods;

select count(id) as 임베디드학과생 from student where dept='임베디드공학';

select sum(o.ea)
as 갤노트판매량
from orders o, goods g
where g.g_id=o.g_id
and g.g_name='갤럭시노트4';