5주차

DB 임포트하기

워크벤치 좌측 권한 admin으로 하고 import 클릭

default target db에는 기존에 존재하는 스키마를 넣지 않고 새 db를 만들어서 임포트

 

show tables; <-  테이블들 확인
desc guestbook; <- 해당 테이블 구조 조회

 

!시험에 낼거 - 4주차에도 했다

create 문

primary key

not null

unique

check(dept in(목록))

check (price >= 500)

default '기본값'

varchar와 char의 차이

 

foreign key (dept) reference,

 

department on update cascade 

on delete cascade 

 

!기본키를 제공하는 테이블을 먼저 만들고 참조하는 테이블을 나중에 만들어야 foreignkey로 참조 가능하다

 

데이터 정의어 alter로 테이블 수정

 

alter table member add column addr char(20); <- member 테이블에 addr 어트리뷰트를 char 속성으로추가했다

alter table member modify addr varchar(20) unique; <- modify로 addr 어트리뷰트의 속성을 varchar로 변경

alter table member change column addresss address char(20) unique; <- change로 컬럼 drop하고 다시 만들어줌

alter table member drop column addr; <- addr 컬럼 삭제

 

@테이블 이름을 변경하기

rename table member to mem; <- member 테이블을 mem으로 변경

 

 

madang 테이블에서만 시험 문제를 낸다!

 

@group by 문으로 특정 조건으로 묶어서 가져오기

group by 묶을 그룹 having 조건 식으로 나온다

!group by 뒤에는 where절이 아니라 having이 조건문이다 

 

select publisher, sum(price) as 'bookprice', avg(price) as 'avgprice' 
from book
group by publisher
having sum(price) >= 20000;

select * from book;

 

!고객번호별로 총 책 구입한 금액을 출력하기

select custid, sum(saleprice) 

from orders
group by custid 
having sum(saleprice) >= 30000; 

 

5주차 확인 문제 - 테이블 생성, 수정, 삭제

 

1. 자신만의 계정을 만들어 exdb를 생성한 후 다음과 같은 속성을 가진 NewBook(도서) 테
이블과 publisher(출판사) 테이블을 을 생성하시오. 정수형은 int 를 사용하며 문자형은 가변
형 문자타입인 VARCHAR을 사용한다. 기본키는 bookid로 정의 bookid는 번호가 1부터 차
례로 삽입되도록 정의 도서이름은 null을 허용하지 않는다.
pubno 은 출판사 테이블의 pubno을 참조하는 외래키로 준다. (외래키의 삽입 변경 삭제 충
족 조건을 만족한다.)
[NewBook(도서) 테이블]
•bookid(도서번호)-INT : 기본키 ,1부터번호 자동 저장
•bookname(도서이름)-VARCHAR(20) : 필수입력
•pubno(출판사번호)-CHAR(4)
•price(가격)-INT : 필수입력(40000 이상 입력할 수 없음)
[publisher(출판사테이블)]
•pubno(출판사번호)- CHAR((4) : 입력형식 P001
•pubname(출판사이름)-VARCHAR(20) : 기본값 :한양출판사
•tel(전화번호)-CHAR(14) : 필수입력
•addr(주소) - CHAR(20)
2. NewBook 테이블에 VARCHAR(13)의 자료형을 가진 isbn 속성을 추가하시오
3. NewBook 테이블의 isbn 속성의 데이터 타입을 INT 형으로 변경하시오.
4. NewBook 테이블의 isbn 의 이름을 isb_no로 바꾸어 주세요.
5. NewBook 테이블의 publisher 속성에 NOT NULL 제약조건을 적용하시오.
6. NewBook 테이블의 isb_no 속성을 삭제하시오
7. NewBook 테이블에 데이터를 삽입하여 bookid가 저절로 저장되는 것을 확인하시오
8. NewBook 테이블의 이름을 Booknew 로 바꾼다.
9. Booknew 테이블을 삭제한다
10. exdb 데이터베이스를 삭제한다.

생각보다 삽질을 많이 했다.. 특히 fk 연결할때 기본키가 아니면 연결이 안되서 publisher 테이블의 pubno를 pk로 만들고 연결했다 

create database exdb;
use exdb;

create table publisher(
	pubno char(4) check(pubno like 'P___'),
    pubname varchar(20) default '한양출판사',
    tel char(14) not null,
    addr char(20)
    );

create table NewBook(
	bookid int primary key auto_increment,
    bookname varchar(20) not null,
    pubno char(4),
    foreign key (pubno) references publisher(pubno)
    on update cascade on delete cascade,
    price int not null check(price < 40000)
    );

alter table publisher add primary key(pubno);

alter table NewBook add column isbn varchar(13);
alter table NewBook modify isbn int;
alter table NewBook change column isbn isbn_no int;
alter table NewBook modify column pubno char(4) not null;
alter table NewBook drop column isbn_no;

desc publisher;
desc NewBook;

insert into publisher (pubno, tel, addr) values('P001','010-0000-0000','한양대학교 에리카');
insert into NewBook (bookname, pubno, price) VALUES ('testbook', 'P001', 39999);

select * from publisher;

alter table Booknew rename Booknew;
drop table Booknew;
drop database exdb;