● INLINE VIEW => 서브쿼리를 이용해 가상의 테이블을 만들어 검색
문제) 자기 자신의 부서별 평균 급여보다 더 많은 급여를 사원의 이름,급여,부서번호부서별 평균급여를 검색하여 출력
mysql> select p.pname,p.pay,p.dno,d.avgpay from person p,(select dno,avg(pay) avgpay from person group by dno) d where p.dno=d.dno && p.pay > d.avgpay;
※ delete 또는 update에서는 서브쿼리를 아직 사용할 수 없다.
● insert와 SubQuery : 데이타 복사
형식)
insert into 테이블명1 select 검색대상 from 테이블명2;
=> 테이블2에서 검색된 데이타를 테이블1에 저장
=> select와 달리 서브쿼리에 괄호를 치지 않는다.
문제) 사번,이름,급여 필드를 가진 salesman 테이블을 만들고 person에서 업무가 salesman인 사원의 정보를 저장
mysql> create table salesman(pno int not null primary key,pname varchar(10) not null,pay int not null);
mysql> insert into salesman select pno,pname,pay from person where job=’salesman’;
mysql> select * from salesman;
● create table과 SubQuery : 테이블 생성 및 데이타 복사
형식)
create table 테이블명1 as select 검색대상 from 테이블명2;
=> 테이블2에서 검색된 데이타를 이용해 테이블1을 생성하고 데이타를 저장
=> insert 서브쿼리의 경우 2번에 걸쳐 테이블 생성과 복사를 하지만 필드명을 임의로 바꿀 수 있다.
문제) 사번,이름,급여 필드를 가진 manager 테이블을 만들고 person에서 업무가 manager인 사원의 정보를 저장
mysql> create table manager select pno,pname,pay from person where job=’manager’;
mysql> desc manager; (primary key 제약조건은 복사되지 않는다)
mysql> select * from manager;
■ TCL(Transaction Control Language)
– 트랜젝션 : DML 명령(insert, delete, update) 명령의 집합
=> 저장된 데이타를 변경시키는 명령의 모임
– 트랜젝션의 시작 : DML명령을 수행하면 자동으로 트랜젝션이 만들어 진다.
– 트랜젝션 적용 : DML명령을 실제 테이블에 적용
mysql> commit;
– 트랜젝션 취소 : DML명령으로 이루어진 결과를 취소
mysql> rollback;
– 자동 commit 되는 경우
=> SQL을 정상적으로 종료한 경우
=> DDL또는 DCL 명령을 사용한 경우
– 자동으로 rollback되는 경우
=>SQL이 비정상적으로 종료된 경우
※ mysql의 테이블을 만들어 주는 엔진(프로그램)
1) MYISAM (버전 5.0 이하)
– 데이타의 저장 및 변경, 삭제 속도가 좋다
– Transaction이 존재하지 않는다. commit과 rollback을 사용하지 못한다.
2) Innodb (버전 5.1 이상)
– 데이타의 검색 속도가 좋다
– Transaction이 존재한다. commit과 rollback을 사용가능.
mysql> show table status; 로 Engine의 종류 확인
– Transaction 예제
mysql> begin ; => 트랜젝션 시작
mysql> delete from person;
mysql> select * from person;
mysql> rollback;
mysql> select * from person;
mysql> select @@autocommit ;
@@autocommit : 환경변수. 값이 1이 나오면 자동으로 commit 하도록 설정됨
mysql> set @@autocommit=0;
자동으로 commit이 되지 않도록 설정. commit을 실행하거나 정상적으로 종료하면 적용됨. 대부분 autocommit을 적용하고 begin을 실행해서 사용함.
– Transaction 예제 2
첫번째 Session
mysql> begin;
mysql> update person set pay=0 where pname=’kim’;
두번째 Session
mysql> delete from person where pname=’kim’;
=> 첫번째 세션에서 commit 또는 rollback을 하기 전까지 데이타 Lock 걸려있다.
● View
– 하나 또는 여러 개의 테이블에서 원하는 데이타만 정확하게 가지고 올 수 있도록 미리 원하는 필드로만 만든 가상의 테이블
– View을 만드는 목적
=> 여러 개의 테이블을 보다 쉽게 검색하기 위해 만든 가상의 테이블(편리성)
=> 특정 계정이 모든 테이블의 정보를 보여주는 것이 아니라 특정 데이타만 제어할수 있도록 만든 가상의 테이블(보안성)
형식)
create view 뷰이름 as select 검색대상 from 테이블명;
ex) person에서 사번,이름,월급만 보여주는 view001을 생성
mysql> create view view001 as select pno,pname,pay from person;
mysql> select * from view001;
ex) person에서 업무가 salesman인 사원의 사번,이름,월급만 보여주는 view002을 생성
mysql> create view view002 as select pno,pname,pay from person where job=’salesman’;
mysql> select * from view002;
mysql> begin;
mysql> delete from view002 where pname=’ally’;
mysql> select * from view002;
mysql> select * from person;
주목할 부분 : view테이블의 테이타를 삭제할 경우 원본 테이블의 데이타도 삭제되어 있다. 즉, 원본 테이블에 영향이 미친다.
mysql> rollback;
문제) person에서 사원의 사번,이름,월급만 보여주는 view003을 생성하되 급여순으로 내림차순으로 정렬하여 저장
mysql> create view view003 as select pno,pname,pay from person order by pay desc;
mysql> begin;
mysql> update person set pay=800 where pname=’bill’;
mysql> select * from view003;
=> bill의 위치가 맨 아래로 내려가 있음을 볼 수 있다. order by 명령이 지속되어 있다.
mysql> rollback;
문제) 부서별 최대 급여목록을 view004에 만들어 저장
mysql> create view view004 as select dno,max(pay) maxpay from person group by dno;
문제) 사원의 이름에 ‘a’가 있는 사번,이름,급여,부서명을 가진 view005를 만들어 저장
mysql> create view view005 as select pno,pname,pay,dname from person inner join division using(dno) where pname like ‘%a%’;
mysql> delete from view005 where pname=’ally’; => 삭제 불가(복합 view)
문제) 사원의 이름이 ‘park’인 사원보다 급여를 많이 받는 사원의 집합인 view006을 만들어 사번,이름,급여 저장
mysql> create view view006 as select pno,pname,pay from person where pay>(select pay from person where pname=’park’);
※ view 삭제
형식)
drop view 뷰이름;
create view 로 만든 가상의 테이블은 drop view 로 테이블을 삭제한다.
● mysql 백업 및 복구
▶ 백업
mysqldump -u 계정명 -h 서버명 -p DB명 table명 > 백업파일명.sql
table명은 삭제 가능
▶ 복구
mysql -u 계정명 -h 서버명 -p DB명 table명 < 백업파일명.sql
=> 백업파일 안의 명령을 서버의 DB안에서 실행
