Oracle SQL: ALTER
create table salesperson( name varchar2(20) primary key, age number, salary number );
create table customer( name varchar2(20) primary key, city varchar2(20), industrytype varchar2(20) );
create table orders( no number, custname varchar2(20) references customer(name), salesperson varchar2(20) references salesperson(name), amount number, primary key(custname, salesperson) );
insert into salesperson values(‘김수연’,26,500); insert into salesperson values(‘임하연’,37,500); insert into salesperson values(‘유하림’,40,600); insert into salesperson values(‘이건우’,50,700); insert into salesperson values(‘박지예’,27,500);
insert into customer values(‘최현호’,’서울’,’공무원’); insert into customer values(‘김지현’,’서울’,’IT’); insert into customer values(‘윤서우’,’서울’,’IT’); insert into customer values(‘정자영’,’서울’,’교사’); insert into customer values(‘윤태인’,’인천’,’서비스업’); insert into customer values(‘김시아’,’인천’,’서비스업’); insert into customer values(‘노유나’,’대전’,’건설’); insert into customer values(‘박성미’,’광주’,’건설’); insert into customer values(‘임상진’,’대전’,’교사’);
insert into customer values(‘안철수’,’서울’,’기업인’); insert into customer values(‘윤석열’,’서울’,’정치인’);
insert into salesperson values(‘이재명’,50,400);
시퀀스
자동으로 1부터 1씩 증가하는 값으로 순번을 매겨야 하는 경우에 사용
시퀀스를 만드는 방법
create sequence 시퀀스이름;
시퀀스를 사용하는 방법
insert into 테이블이름 values(시퀀스이름.nextval,…)
create sequence seq_orders;
insert into orders values(seq_orders.nextval,’최현호’,’김수연’,1); insert into orders values(seq_orders.nextval,’김지현’,’김수연’,1); insert into orders values(seq_orders.nextval,’윤서우’,’김수연’,1);
insert into orders values(seq_orders.nextval,’정자영’,’임하연’,1); insert into orders values(seq_orders.nextval,’윤태인’,’임하연’,1);
insert into orders values(seq_orders.nextval,’김시아’,’유하림’,1);
insert into orders values(seq_orders.nextval,’노유나’,’이건우’,1); insert into orders values(seq_orders.nextval,’박성미’,’박지예’,1); insert into orders values(seq_orders.nextval,’임상진’,’박지예’,1);
insert into orders values(seq_orders.nextval,’최현호’,’박지예’,1); insert into orders values(seq_orders.nextval,’최현호’,’이건우’,1);
insert into orders values(seq_orders.nextval,’김시아’,’박지예’,1);
** 시퀀스를 이용하여 만약 sql명령 자체에 결함이 있어 실패했을 때에는 시퀀스를 이용한 순번은 이미 발행되었기 때문에 순서대로 되지 않을 수도 있다!**
** 꼭 순서대로 해야한다면 max(컬럼)+1을 사용한다. **
시퀀스는 겹치지 않도록 번호를 매기는 용도로 사용한다.
2) 모든 판매원의 이름과, 급여 출력 단, 중복행은 제거 select name, salary from salesperson; 3) 30세 미만인 판매원의 이름 출력 select name from salesperson where age<30; 4) ‘주’로 끝나는 도시에 사는 고객의 이름 출력 select name from customer where city like ‘%주’; 5) 주문을 한 고객의 수 (서로다른 고객) 출력 select count(distinct custname) from orders; 6) 판매원 각각에 대하여 주문의 수를 계산하시오.(주문건수) select salesperson, count(*) from orders group by salesperson;
-각 판매원별로 총 주문수량을 출력(총 주문수량) select salesperson, sum(amount) from orders group by salesperson;
7) 서울 사는 고객으로부터 주문을 받은 판매원의 이름과 나이를 출력
서브쿼리 풀이
-서울에 사는 고객이름 select name from customer where city = ‘서울’; ->A
- A에게 판매한 판매원의 이름 출력 select distinct salesperson from orders where custname in (select name from customer where city = ‘서울’); -> B
- 이름이 B에 해당하는 판매원의 이름과 나이 출력 select name, age from salesperson where name in (select distinct salesperson from orders where custname in (select name from customer where city = ‘서울’));
NAME AGE ———- —– 김수연 26 임하연 37 이건우 50 박지예 27
조인식 풀이
select distinct s.name, age from salesperson s, orders o , customer c where s.name = o.salesperson and c.name=o.custname and city = ‘서울’;
NAME AGE ———- —– 김수연 26 임하연 37 이건우 50 박지예 27
9) 두번 이상 주문을 받은 판매원의 이름을 출력 select salesperson, count(salesperson) from orders group by salesperson having count(salesperson)>=2;
10) 판매원 ‘박지예’의 급여를 600으로 변경 update salesperson set salary = 600 where name = ‘박지예’;
데이터 명령어의 종류 1.DCL(Data Control Language) 데이터 제어어 권한부여, 권한을 제거하는 명령어 grant, revoke 2.DDL 데이블(뷰,인덱스) 생성, 수정, 삭제 CREATE, ALTER, DROP 3.DML SELECT , DELETE, UPDATE, INSERT
- 서브쿼리 SQL문 안에 있는 또다른 sql문
select from where
sql문의 결과가 다중행일때 사용하는 연산자..? in not in..등
- 다중행 연산자 서브쿼리가 where절에 사용될 때에 서브쿼리의 건수가 여러건일때 사용되는 연산자
in, not in, any(some), all exists, not exists
연습) 대한민국에 거주하는 고객에게 판매한 도서의 총판매액을 출력
- 대한민국에 거주하는 고객의 번호 출력 -> A select custid from customer where address like ‘대한민국%’;
- 주문 테이블로부터 고객번호가 A에 해당하는 판매액의 총합 select sum(saleprice) from orders where custid in (select custid from customer where address like ‘대한민국%’);
연습) 3번 고객이 주문한 도서의 최고금액보다 더 비싼 도서를 구입한 주문의 주문번호와 금액을 출력 -3번 고객이 주문한 주문금액 확인 select saleprice from orders where custid = 3;
SALEPRICE
10,000 10,000 10,000 6,000 12,000 13,000 ->a
- 주문 테이블로부터 주문가격이 a보다 큰 주문의 주문번호, 주문금액 출력 select orderid, saleprice from orders where saleprice > (select saleprice from orders where custid = 3); => 서브쿼리의 건수가 여러건이어서 비교연산 >을 사용할 수 없다. 다중행 연산자인 any(some), all과 같이 사용해야 한다.
select orderid, saleprice from orders where saleprice > all (select saleprice from orders where custid = 3);
select orderid, saleprice from orders where saleprice > (select max(saleprice) from orders where custid = 3);
==> > all 은 max함수를 사용하여 단일행 연산자로 표현할 수 있다!
select orderid, saleprice from orders where saleprice > any (select saleprice from orders where custid = 3);
select orderid, saleprice from orders where saleprice > some (select saleprice from orders where custid = 3);
==> some(any)는 min 함수를 사용하여 단일행 연산자로 표현할 수 있다! select orderid, saleprice from orders where saleprice > (select min(saleprice) from orders where custid = 3);
연습) 출판사별 평균가격의 최대값보다 더 비싼 도서의 정보를 출력
- 출판사별 도서의 평균가격 출력
select avg(price) from book group by publisher;
max함수를 사용하여 단일행 연산자 이용
select * from book where price > (select max(avg(price)) from book group by publisher);
다중행연산자 이용
select * from book where price > all(select avg(price) from book group by publisher);
다중행 연산자
in , not in , any(some), all에 대하여 설명하시오
in, not in, any(some), all 모두 서브쿼리의 건수가 여러건일때 사용하며
in 은 = 을 대신 not in 은 != 을 대신
any(some), all은
,<,>=,<= 연산자와 함께 사용
any(some), all은 min 함수나 max 함수를 사용하면 단일행 연산자로도 표현할 수 있다.
exists, not exists
다중행 연산자 종류중의 하나이며 서브쿼리의 건수가 존재하는지 존재하지 안는지 여부를 판별하는 연산자이다.
exists 는 서브쿼리가 반환하는 조건에 맞는 메인쿼리를 실행하고 not exists 는 서브쿼리의 조건에 만족하지 않는 메인쿼리를 실행한다.
exists, not exists 다중행 연산자를 사용할 떄는 반드시 상관서브쿼리 여야 한다.
연습) 대한민국에 거주하는 고객에게 판매한 도서의 총판매액을 출력
join
select sum(saleprice) from customer c, orders o where c.custid = o.custid and address like ‘대한민국%’;
SUM(SALEPRICE)
--------------
231500
in 연산자 이용 서브쿼리
select sum(saleprice) from orders where custid in (select custid from customer where address like ‘대한민국%’);
| select custid from customer where address like ‘대한민국%’;
SUM(SALEPRICE)
--------------
231500
exits 연산자 이용 서브쿼리
select sum(saleprice) from orders o where exists (select custid from customer c where address like ‘대한민국%’ and c.custid = o.custid); 서브쿼리의 건수가 존재한다면 ~ 그 customer의 saleprice를 sum한걸 반환해라 SUM(SALEPRICE) ————– 231500 -> in으로 표현하는게 간결해서 잘 사용은 안한다.
View
- 실제로는 존재 하지 않는 논리적인 가상 테이블을 말한다.
- 자주 사용하는 복잡한 조건식을 갖는 select문이 있다면 그것을 조회하는 sql명령어를 뷰로 만들어 놓으면 사용이 용이하다.
- 보안유지상 특정 테이블의 칼럼을 제한하여 접근할 수 있도록 할때에도 뷰를 사용한다.
뷰를 만드는 방법
create view 뷰이름 as select 문
연습) 오늘 날짜의 출판사별 총판매수량, 총판매금액을 출력 select publisher, count(publisher) cnt, nvl(sum(saleprice),0) sum from book b, orders o where b.bookid = o.bookid(+) and to_char(orderdate,’yyyy/mm/dd’) = to_char(sysdate,’yyyy/mm/dd’) group by publisher; ==>A
select distinct publisher, 0 cnt from book; ==>B
A의 cnt + B의 cnt
select B.publisher, nvl(A.cnt,0) cnt , nvl(A.sum,0) sum from (select publisher, count(publisher) cnt, nvl(sum(saleprice),0) sum from book b, orders o where b.bookid = o.bookid(+) and to_char(orderdate,’yyyy/mm/dd’) = to_char(sysdate,’yyyy/mm/dd’) group by publisher) A right outer join (select distinct publisher, 0 cnt from book) B on A.publisher = B.publisher;
=> 위와 같이 조회할 일이 많다고 할때에 뷰 생성
create view today_sale as select B.publisher, nvl(A.cnt,0) cnt , nvl(A.sum,0) sum from (select publisher, count(publisher) cnt, nvl(sum(saleprice),0) sum from book b, orders o where b.bookid = o.bookid(+) and to_char(orderdate,’yyyy/mm/dd’) = to_char(sysdate,’yyyy/mm/dd’) group by publisher) A right outer join (select distinct publisher, 0 cnt from book) B on A.publisher = B.publisher order by nvl(A.cnt,0) desc;
연습) 도서명에 “축구”를 포함하고 있는 도서의 정보를 조회하는 뷰를 생성한다. 뷰 이름은 vw_book
select * from book where bookname like ‘%축구%’;
create view vw_book as select * from book where bookname like ‘%축구%’;
연습) create view vw_customer as select * from customer where address like ‘대한민국%’;
create view vw_orders as select o.orderid, c.custid, name, b.bookid, b.bookname, saleprice, orderdate from orders o, customer c, book b where o.custid = c.custid and o.bookid = b.bookid;
select * from vw_orders where name = ‘김연아’;
view 사용하는 목적
- 자주 사용하는 복잡한 sql을 대신
- 보안유지상 사용자별로 조회할 수 있는 컬럼을 제한
create user c##hong identified by hong; grant connect, resource to c##hong
연습) c##madang이 자신의 테이블인 emp중에서 사원번호, 사원명, 부서번호, 주소, 전화번호만 조회할 수 있도록 뷰를 생성하여 c##hong에게 권한을 부여해보자.
create view vw_emp as select eno, ename, dno, addr, phone from emp;
권한 부여
grant select on c##madang.vw_emp to c##hong;
권한 제거
revoke select on c##madang.vw_emp from c##hong;
연습) 20번 부서에 근무하는 직원들의 사원번호, 사원명, 주소, 전화, 부서번호를 조회하는 뷰를 생성 (vw_emp20)
create view vw_emp20 as select eno, ename,addr,phone,dno from emp where dno = 20;
뷰를 통하여 insert, update, delete를 실험
insert into vw_emp20 values(2000, ‘홍길동’,’서울’, ‘010-7777-7777’,20); => 뷰를 통하여 insert 할 수 있다. 실제로 레코드는 뷰를 생성할 때 사용한 모테이블에 추가된다. 그렇기 때문에 뷰생성시에 사용한 컬럼 이외의 컬럼들은 null을 허용하거나 default값이 설정되어 있어야 한다.
update vw_emp20 set addr=’서울’ where eno=1010;
delete vw_emp20 where eno=1014;
연습) 뷰를 생성시의 조건에 맞지 않는 레코드를 추가할 수 있는지 insert into vw_emp20 values(3000,’이순신’,’서울’,’010-9999-9999’,30);
==> 뷰 생성시에 조건에 맞지 않는 레코드를 추가할 수는 있다 그러나 뷰에는 나타나지 않고 모테이블에 추가가 된다.
연습) 뷰를 생성시의 조건에 맞지 않는 레코드로 수정할 수 있는지
update vw_emp20 set dno=30 where eno = 2000; => 뷰 생성시 설정한 조건에 맞지 않는 값을 뷰를 통해 update할 수 있다. 모테이블의 내용이 변경되고 뷰에는 조건에 맞지 않기 때문에 나타나지 않는다.
뷰 삭제
drop view 뷰이름;
** 뷰를 생성시에 설정한 조건에 맞지 않는 레코드를 추가할 수 없도록 조건에 맞지 않는 값으로 수정할 수 없도록 하고 싶다. -> with check option
create view 뷰이름 as select.. 조건식 with check option;
with check option을 설정하여 뷰를 생성한 다음 조건에 맞지 않는 레코드를 추가하고 조건에 맞지 않는 값으로 수정해보자.
create view vw_emp20 as select eno, ename,addr,phone,dno from emp where dno = 20 with check option;
insert into vw_emp20 values(4000,’유관순’,’서울’,’010-0000-0000’,20); insert into vw_emp20 values(4000,’문재인’,’서울’,’010-0000-0000’,30);
update vw_emp20 set dno = 30 where eno = 1010;
조회(읽기)만 가능한 뷰를 생성
create view 뷰이름 as select ..... with read only;
create view vw_emp20 as select eno, ename,addr,phone,dno from emp where dno = 20 with read only;
update vw_emp20 set addr = ‘인천’ where dno = 1010;
view에 대하여 설명하시오
시스템 뷰 => 오라클이 제공하는 데이터사전을 말한다.
user_objects 사용자가 만든 모든 객체의 정보 user_tables 사용자가 만든 모든 테이블에 대한 정보 user_constraints 사용자가 만든 모든 제약의 정보를 갖고 있다
=> 제약의 비활성화 alter table 테이블이름 disable constraint 제약명;
=> 제약의 활성화 alter table 테이블이름 enable constaint 제약명;
select constraint_name, constraint_type, table_name, status from user_constraints;
SYS_C008320 R ORDERS ENABLED SYS_C008321 R ORDERS ENABLED SYS_C008318 P BOOK
alter table orders disable constraint SYS_C008320; alter table orders disable constraint SYS_C008321; alter table book disable constraint SYS_C008318;
insert into book values(14,’즐거운 오라클’,’쌍용미디어’,30000); -> 제약이 풀렸기 떄문에 레코드 들어감
제약을 활성화를 하려면 활성화 하려는 제약에 대하여 만족하는 상태로 만들어야 합니다.
alter table orders enable constraint SYS_C008320; alter table orders enable constraint SYS_C008321; alter table book enable constraint SYS_C008318;
ORA-02437: (C##MADANG.SYS_C008318)을 검증할 수 없습니다 - 잘못된 기본 키입니다 => 활성화 하려는 제약에 대한 만족하지 않는 데이터가 있어서 오류가 발생한다.
delete book where bookname=’즐거운 오라클’;
인덱스
조건식에 자주 사용되는 칼럼에 대하여 미리 색인표를 만들어 두는 것을 말한다 인덱스를 만들어두면 검색시에 빠른 성능을 기대할 수 있다.
인덱스 만드는 방법
create index 인덱스이름 on 테이블이름(컬럼이름, [컬럼이름])
연습)bookname을 대상으로 인덱스를 생성(idx_book) 인덱스를 만들지 않고 책이름으로 검색 select * from book where bookname = ‘야구가좋아’;
인덱스를 만든 후 책이름으로 검색 create index idx_book on book(bookname);
member에 dummy 데이터 500000명의 데이터를 insert하는 프로그램을 만들어보자.
create table member100 ( id varchar2(50), pwd varchar2(50), name varchar2(50));