유저 생성, 권한주기, 테이블 생성
-- ho 라는 사용자 생성, 비밀번호는 1111(반드시 작은 따옴표로)
create user ho@localhost identified by '1111';
-- test 스키마를 만들고 변경할 수 있는권한을 ho에게 준다
grant all privileges on test.* to ho@localhost with grant option;
-- 테이블 만들기, check 옵션, 기본키 auto_increment, 외래키 생성할때 어떻게 하는지를 중점적으로 보기
create table publisher(
pubno char(4) check(pubno like 'p___') primary key,
pubname varchar(20) default '한양출판사' check(pubname in('asgas','asgasassa','sagasgzx')),
tel char(24) not null,
address char(20)
);
create table NewBook(
bookid int primary key auto_increment,
bookname varchar(20) not null,
price int not null check(price < 40000),
pubno char(4),
foreign key(pubno) references publisher(pubno) on update cascade on delete cascade
);
-- 컬럼 추가, 컬럼 속성 변경, 컬럼 재정의, 테이블 이름 바꾸기
alter table NewBook add column isbn varchar(13);
alter table NewBook modify column isbn int;
alter table NewBook change column isbn isbn_no_int int;
alter table NewBook modify column pubno char(4) null;
rename table NewBook to BookNew;
서브쿼리
-- 서브쿼리
-- 책을 한번도 안산 고객을 가져오기
select name from customer c
where custid not in (select custid from orders)
-- 가격이 가장 비싼 책의 이름, 가격
select bookname, price from book
where book.price = (select max(price) from book);
-- 도서테이블의 책가격의 전체평균보다 책 가격이 비싼 책의 이름, 출판사, 가격을 검색
select bookname, price, publisher from book
where book.price >= (select avg(price) from book);
-- 책제목에 축구가 있는 출판사와 같은 출판사 책의 이름과 출판사 출력
select bookname, publisher from book
where publisher in (select publisher from book where bookname like '%축구%');
-- 김연아가 주문한 책의 총합계를 부질의를 이용해서 검색한 버전과 where으로 검색한 버전
select sum(saleprice) from orders o
where custid = (select custid from customer where name = '김연아');
select sum(saleprice) from orders o, customer c
where c.custid = o.custid and c.name = '김연아';
-- 고객번호가 3번인 고객과 같은 책을 주문한 고객의 이름, 책 제목
select c.name, b.bookname from customer c, book b, orders o
where o.custid = c.custid and o.bookid = b.bookid
and b.bookid in (select bookid from orders o where custid = 3);
책에 나온 스칼라 서브쿼리 select 절에 부속질의를 넣는다
-- 고객별로 판매맥을 출력하기
select name, sum(saleprice)
from customer c, orders o
where o.custid = c.custid
group by name;
-- 위의 조인 쿼리를 아래의 서브쿼리로 변경, 고객별로 판매맥을 출력하기
select o.custid, (select name from customer c where c.custid = o.custid), sum(saleprice)
from orders o
group by o.custid;
집합연산자 union
-- 집합 연산자를 이용해서 주소에 영국이 들어가는 고객과 주문내역이 없는 고객을 함께 가져왔다
select name from customer where address like '%영국%'
union
select name from customer
where custid not in(select custid from orders);
조건절 연산자 exists
-- exits 연산자로 책을 주문한 고객의 이름과 주소를 검색
select name, address from customer c
where exists (select * from orders o where c.custid = o.custid);
트랜잭션 : 백업, 모든 작업들을 복구 : rollback, 수락 : commit
start transaction;
sql 수행
rollback; <- 반영 x
commit; <- 반영 o
insert, update delete
-- 삽입 insert into 테이블 (속성명) values ()
insert into book values(111,'데이터베이스','한양출판사',25000);
insert into book (bookid,price,bookname) values (12,30000,'프로그래밍');
-- 변경 update 테이블명 set 바꿀값, 바꿀값 where 기본키 값 alter
-- 책번호 12번인 책의 출판사를 한양출판사, 가격을 28000
update book
set publisher = '한양출판사', price = 28000
where bookid = 12;
delete from book where bookid = 111;
내장함수 책 내용, 날짜부분 시험에 내니까 꼭 다시 쳐볼것
-- round(x,0)을 하면 일의 자리 기준인 1445가 나오고 아래처럼 하면 백의 자리 기준인 1400으로 나온다
select round(1444.875,-2);
-- 고객별 평균 주문 금액을 백원 단위로 반올림한 값 구하기
select c,custid, avg(saleprice)
from customer c, orders o
where c.custid = o.custid;
-- 한글은 1글자에 3바이트가 필요해서 length는 9, char_length는 3으로 나온다
select length('테스트');
-- replace(컬럼명, '바꿀 컬럼', '바꿀 내용');
select bookid, replace(bookname,'축구', '농구'), publisher, price from book;
-- substr 1부터 시작한다
-- 날짜 함수 str_to_date('문자열 형식 날짜','포맷') 형태로 사용
select str_to_date('2023-11-15', '%Y-%m-%d');
-- 날짜형 데이터를 문자열로 반환 m과 d는 대문자로 쓰면 월은 영어, 일은 st,rd,th를 붙여서 반환
select date_format('2023-11-15', '%Y-%m-%d');
-- sysdate()로 현재 날짜를 가져와서 adddate로 10일 후에 날짜로 변환한 후 연-월-일 형태로 표현하기
select date_format(adddate(sysdate(), interval 10 day), '%Y-%m-%d');
-- 날짜 간격 비교하기
select datediff(adddate(sysdate(), interval 10 day),sysdate());
-- 주문받은 후 10일이 지나면 주문이 확정이 될 때 주문 확정 일자 구하기
select orderid as '주문번호', orderdate as '주문일', adddate(orderdate, interval 10 day) from orders;
-- ! 행번호 출력 set과 := 기호를 사용한다, set으로 정수형 변수, select 안에 temp를 증가시키는 조건으로 이해함
-- 아래는 고객 목록에서 고객번호, 이름, 전화번호를 앞의 두명만 보이게 하는 방법이다
set @temp:= 0;
select (@temp := @temp +1) '순번', custid, name, phone from customer where @temp < 2;
-- 내장함수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 = '데이타베이스';
