10주차

실기, 오픈북인데 소스 복붙안됨, 

 

• 내부함수 예제

use madang;

-- 내장함수fh 도서 테이블의 출판사의 가격 평균을 백원단위 반올림

select publisher, round(avg(price),-2) from book group by publisher;

select publisher, truncate(avg(price),-2) from book group by publisher;

-- 고객 테이블 주소에서 대한민국을 한국으료 변경해서 검색

select replace(address,'대한민국','한국') from customer;

-- 같은 성을 가진 사람이 몇 명이나 되는지 성별, 인원수를 구하기 <- substr로 성만 빼와서 비교하기
	select count(*), substr(name,1,1) from customer group by substr(name,1,1); 
    select count(*), left(name,1) from customer group by left(name,1);

select * from customer;
-- 이름, 전화번호가 포함된 고객 구하기, 전화번호가 없으면 연락처 없음

select name '이름', ifnull(phone,'연락처없음') as '전화번호' from customer;

-- 내장함수를 사용해서 도서 목록에 앞에 1번부터 번호를 붙여서 검색
-- set 연산자를 사용해서 검색한다
select * from book;

set @num := 0;
select (@num:=@num+369) '순번', custid, name from customer;

-- 내장함수를 사용해서 현재 시간 검색하기
select date_format(sysdate(),'%Y/%m/%d');
-- ! 시험에 나온다 dataformat(날짜형식, 문자형식)  <- 날짜를 문자로 변경, str_to_date(문자형식, 날짜형식) <- 문자를 날짜로 저장

-- 내장함수를 사용해서 대한미디어에서 출판한 도서의 제목과 제목의 문자수, 바이트수를 검색하기
select bookname, char_length(bookname), length(bookname) from book where publisher = '대한미디어';

-- 내장함수를 사용해서 주문 테이블의 고객명별 산 책의 평균을 구하되 백단위에서 잘라 주기
select * from customer;
select * from orders;

select name, truncate(avg(saleprice),-2)
from customer c, orders o
where c.custid = o.custid
group by name;

-- 고객이 산 책 제목과 개격을 검색하되 고객 이름 앞의 첫 글자만 출력하기
select rpad(left(c.name,1),3,'*'), o.saleprice from customer c ,orders o
where c.custid = o.custid;

-- 주문 테이블의 주문한 고객명 주문 날짜보다 한 달 후의 날짜를 검색하기 <- interval을 사용

select name, orderdate, adddate(orderdate,interval 1 month) '한달후'
from orders o, customer c
where o.custid = c.custid

 

•뷰 사용

 

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;
select * from book;

-- 뷰를 변경시 원본이 변경, 뷰에 삽입하니까 원본인 book 테이블에도 값이 삽입됬다, v2에는 2만원 이상 제약이 걸려 있어서 아래의 insert 문을
-- 사용하면 뷰에는 안들어가지만 원본 테이블인 book에는 들어간다.. 
insert into v2 values(23, '프로그래밍', '한양출판사', 10000);

-- with check option을 사용해서 뷰에 제약조건을 건다, 가격이 만원 이하라서 뷰에 안들어간다

create view v3 
as select * from book 
where price >= 20000
with check option;
insert into v3 values(23, '프로그래밍', '한양출판사', 10000);

-- alter view
alter view v3 
as select * from book 
where price >= 20000
with check option;
insert into v3 values(23, '프로그래밍', '한양출판사', 10000);

drop view v1,v2,v3;

 

• 뷰의 단점, 별칭

 

-- 시험에 나올수 있음!
-- 뷰 단점 1. 기본키나 널을 허용하지 않은 뷰는 삽입,변경이 불가능, 2. 계산된 뷰나 집합함수로 계산된 뷰는 삽입,변경 안됨
-- 뷰 단점 3. 조인된 테이블의 뷰는 삽입,변경 안됨


select * from customer;
-- 고객 테이블에서 고객번호, 고객의 이름, 전화번호만 가져오는 뷰를 작성
create view v4 as select custid, name, phone from customer;
select * from v4;
select * from customer;

-- 뷰에서 삽입하지 않은 컬럼은 원본 테이블에선 널이 된다, 기본키를 널로 삽입하는건 불가능하다
insert v4 values(6, '길동', '010-1234-1234');

select * from customer;

-- ! 시 험 에 나 온 다 !
-- 기본키가 없이 고객의 이름, 전화번호만 가져오는 뷰를 작성, 시험에 나오는 기본키나 널을 허용하지 않은 뷰는 삽입, 변경이 불가능한 뷰의 단점이다
create view v5 as select name, phone from customer;
select * from v5;
select * from customer;

insert v5 values('길동', '010-1234-1234');


select * from book;
-- ! 시험때 꼭 반드시 별칭이나 테이블 별칭 넣자! 출판사별로 책의 평균을 검색하는 뷰를 정의 v6(출판사, 평균)은 어트리뷰트 각각에 별칭을 해서 가져온다
create view v6(출판사, 평균) 
as select publisher, avg(price) 
from book 
group by publisher;

-- 위에서 만든 출판사, 평균의 별칭으로 조회가 가능하다
select * from v6 where 평균 >= 20000;
update book set price = 50000 where bookid = 22;

 

-- 어떤 고객이 어떤 책을 얼마에 구매했는지를 검색하는 뷰
-- 당연하지만 했갈릴수도 있는거 : 뷰를 다 지워야 원본을 지울 수 있다

select * from orders;

create view v8(이름,책이름,가격) 
as select c.name, b.bookname, o.saleprice
from book b, orders o, customer c
where b.bookid = o.bookid and c.custid = o.custid;

select * from v8 where 이름 = '박지성';

select 이름, avg(가격) from v8 group by 이름;

 

•인덱스

 

-- 인덱스를 만들어두면 원래는 기본키로만 삭제되던 데이터를 기본키가 아닌 컬럼으로도 삭제할 수 있다!
create index ix_book on book(bookname);
-- 인덱스를 삭제할 때도 테이블을 on으로 명시해줘야한다!
drop index ix_book on book;
-- 인덱스가 있을때는 아래 쿼리문으로 삭제가 되지만 인덱스가 없으면 아래 쿼리문으로 삭제가 되지 않는다
delete from book where bookname = '데이타베이스';