11주차

실기 테이블 생성 부터 시작

10주차 확인학습

-- 1. 내장함수를 사용하여 도서 테이블의 출판사별 가격의 평균을 백 원 단위로 반올림한 값을 검색하시오
select publisher, round(avg(price),-2) from book group by publisher;
-- 2. 내장함수를 사용하여 고객 테이블의 주소에서 대한민국을 한국으로 변경하여 검색하시오
select replace(address,'대한민국','한국') as address from customer;
-- 3. 내장함수를 사용하여 고객 테이블에서 같을 성(姓)을 가진 사람이 몇 명이나 되는지 성별 인원수를 구하시오
select left(name,1) '성', count(*) from customer group by left(name,1);
select substr(name,1,1) as '성', count(*) from customer group by substr(name,1,1);
-- 4. 내장함수를 사용 하여 이름, 전화번호가 포함된 고객을 보이시오(단 전호번호가 없는 고객은 ‘연락처 없음’ 으로 표시하시오)
select name, ifnull(phone,'연락처 없음') from customer;
-- 5. 내장함수를 사용하여 도서 목록에서 앞에 1번부터 번호를 붙여 검색하시오
set @num := 0;
select (@num := @num + 1) as '순번', custid,name from customer;
-- 6. 내장함수를 사용하여  현재의 시간을 검색하시오
select sysdate(),date_format(sysdate(),'%Y/%m/%d');
-- data_format(날짜형식, 문자형식) str_to_date(문자형식, 날짜형식)
-- 7. 내장함수를 사용하여  대한미디어에서 출판한 도서의 제목과 제목의 문자수 바이트수를 검색하시오 공백은 1byte
select bookname, char_length(bookname), length(bookname) from book where publisher ='대한미디어';
-- 8.내장함수를 사용하여 주문 테이블의 고객명별 산 책의 평균을 구하되 백단위에서 잘라 주세요
select name, truncate(avg(saleprice),-2) from customer c, orders o where c.custid = o.custid group by name;
-- 9. 고객이 산 책 제목 과 가격을 검색하되 고객 이름의 앞의 첫 글자만 출력해 주세요. 뒤에 이름을 ‘*’로 숨기세요
select  rpad(left(name,1),3,'*') from customer;
-- 10, 주문 테이블의 주문한 고객명 주문 날짜보다 한 달 후의 날짜를 검색하시오
select name orderdate, adddate(orderdate, interval 1 month) '한달후' from orders o, customer c where o.custid = c.custid;

 

뷰

• 메모리에 영향이 없어서 막 만들어도 상관 없음

-- boook 테이블에서 축구라는 제목이 있는 도서만 가져오는 뷰 를 작성alter
create view v1
as
select * from book
where bookname like '%축구%';

select * from v1;
-- 원본을 변경하면 뷰토 같이 변경
insert into book values(11,'축구는 박지성','한양출판사',30000);
update book set price = 25000 where bookid = 11; 
delete from book where bookid = 11;

-- 도서테이블 도서의 가격이 20000이상인 책만 뷰로 정의 검색 확인
create view v2
as
select * from book where price >= 20000;
select * from v2;
-- 뷰를 변경시 원본도 변경
insert into v2 values(22,'집가고 싶다','한양출판사',30000);
-- 여기서 뷰의 조건을 불만족해도 원본은 들어감 대충 끔찍함
select * from book;

-- 해결법
create view v3
as
select * from book
where price >= 20000
with check option;
insert into v3 values(22,'집가고싶다','한양출판사',10000);

-- 잘못 만든 뷰 재설정
alter view v2
as
select * from book where price >= 20000
with check option;
insert into v3 values(22,'진짜 집가고싶다','한양출판사',10000);

-- 뷰 삭제
drop view v2;

-- 뷰 단점 대충 기말에 나올것이다 아마도
-- 기본키를 가져오지 않는 뷰는 삽입,삽입,변경 불가
-- 계산된 뷰나 집합함수 계산된 뷰는 삽입, 변경 불가
-- 두개이상의 테이블이 조인된 뷰는 삽입 변경 불가

-- 기본키를 안가져오는 경우 => 뷰에서 작성 안된것은 원본에서 null로 됨
-- 고객 테이블에서 고객의 고객번호,이름 전화번호만 가져오는 뷰를 작성

create view v4
as
select custid, name, phone from customer;
select * from v4;
insert v4 values(6,'나야나','010-1111-0000');
select * from customer;

alter view v4
as
select name, phone from customer;
select * from v4;
insert v4 values('너야나','010-2121-0000');

-- 출판사별로 책의 평균을 검색하는 뷰를 정의
create view v5(출판사,평균)
as
select publisher, avg(price) from book group by publisher;
select * from v5;
select * from v5 where 평균 >= 20000;
update book set price = 50000 where bookid = 22;
select * from book;

인덱스

• 적당히 사용하세요
• 메모리를 잡아먹음
• 락 걸림