or replace를 추가하면 drop -> create를 하지 않고 업데이트하여 사용할 수 있다.
create or replace view emp_view as select empno, deptno, job, ename from emp
where job<>'clerk' order by ename;
select*from emp_view;
select로 출력한 view
create or replace view emp_view as select empno, deptno, job, ename
from emp order by deptno, empno;
select*from emp_view where deptno=10 or deptno=20;
만들어두고 select로 출력할 때 조건을 줄 수도 있다!
전체 피드백
오늘은 eXERD라는 프로그램을 통해 테이블 짜는 것을 해보고 그러느라 MySQL에서 배운 것은 view밖에 없었다.
-- 제약조건 삭제
alter table member drop constraint PK_M_ID; -- oracle 기준
alter table member drop primary key; -- auto_increment 시 삭제 불가
alter table member drop constraint UQ_M_T;
PK에 Auto_increment를 설정했을 시, 삭제가 불가능하다.
-- 제약조건 설정(추가)
alter table member add constraint PK_M_ID primary key (M_ID); -- oracle 기준
alter table member add primary key(M_ID);
alter table member add constraint UQ_M_TEL unique(M_tel);
제약 조건을 줄 때 이름을 설정해야 나중에 삭제해야 할 때 용이하다.
-- not null/ null 수정
-- alter table member modify M_TEL null; -- oracle 기준
alter table member modify M_TEL char(12) null; -- 타입도 변경 가능
alter table member modify M_TEL char(11) not null;
-- 기본값 변환
alter table member modify M_age int default 1;
alter table member modify M_age tinyint unsigned default 1;
타입을 꼭 적어야 하고, 다른 타입으로 적을 시 타입이 변경된다. (Oracle은 안 적어도 된다)
맨 위의 코드는 참조되는 member(부모 테이블)에 없는 ID라서 에러가 뜨고 저장되지 않는다.
두번 코드처럼 여러개를 동시에 추가할 수 있다.
-- 'A' 회원 탈퇴
delete from member where M_ID='A'; -- 외래키 때문에 안 됨
delete from member where M_ID='D'; -- 자식 테이블에서 글이 없기 때문에 성공
delete from board where B_MID='A'; -- 자식 레코드(데이터) 삭제
delete from member where M_ID='A'; -- 자식 레코드(데이터)를 삭제했기 때문에 삭제 성공
-- member(부모) 테이블 삭제
drop table member; -- 자식 테이블이 있기 때문에 안 됨
MEMBER 테이블을 BOARD 테이블에 외래키로 넣어놨기 때문에 BOARD 테이블에서 데이터를 사용했는데 그냥 삭제하려고 하면 삭제되지 않는다.
create table MEMBER(
M_ID varchar(20),
M_PW varchar(20) not null,
M_NAME varchar(20) not null,
M_TEL varchar(13) not null,
M_EMAIL varchar(30) not null,
constraint primary key PK_M_ID (M_ID),
constraint unique UQ_M_E (M_EMAIL)
);
NULL 값을 허용하지 않는다고 했으니 PRIMARY KEY를 넣을 M_ID를 제외한 칼럼에 NOT NULL을 입력했다.
테이블 단에서 PK를 설정할 때 MySQL은 이름을 정해줄 수 없지만 Oracle에서는 정할 수 있으므로 연습하는 겸 이름도 적었다.
create table PRODUCT(
P_NO int auto_increment,
P_NAME varchar(30) not null,
P_PRICE int unsigned not null default 10000,
P_Inven int unsigned not null default 0,
P_Intro text not null,
constraint primary key PK_P_NO (P_NO)
);
select*from dept; -- 부모 테이블 1개
select*from emp; -- 자식 테이블 n개(여러개 가능)
select*from salgrade;
join: 두 개 이상의 테이블을 행끼리 결합하는 DB의 꽃
inner(내부) join: 양 쪽(테이블) 모두에 값이 있는 행 반환(not null)
기본이 inner join이기 때문에 'inner' 생략 가능!
모호성(ambiguous)에 주의할 것! 테이블명에 알리어스(as)를 넣어 별칭을 지어주기.
만족하는 모든 행을 결합(join)한다.
-- 표준(ANSI) SQL 조인
select * from dept D
join emp E
on D.deptno=E.deptno -- 조인조건 / 동일조인
where JOB='CLERK'; -- 일반조건
표준(ANSI) SQL 조인
join [join 할 테이블]
on [join 할 조건]
-- 비표준 SQL 조인
select * from dept D, emp E
where D.deptno=E.deptno;
select * from dept D, emp E
where D.deptno=E.deptno and job='CLERK';
join을 적지 않고 join하는 방법
from에 테이블을 여러개 적고, join 조건을 where절에 적는다.
밑의 코드는 where절에 join조건과 일반 조건이 함께 들어있다. where [join 조건] and [일반 조건]
예제 ㄱ
-- 테이블 2개 조인
select e.ename, e.sal, concat(s.grade, '호봉') grade
from salgrade s
join emp e on e.sal between s.losal and s.hisal; -- 비동일 조인(>= <=)
-- 테이블 3개 조인
select* from dept d
join emp e on d.deptno=e.deptno
join salgrade s on e.sal between s.losal and s.hisal;
-- 3번
select e.ename, e.sal, s.grade, s.stdsal from emp e
join salgrade2 s on truncate(e.sal,-3)=truncate(s.stdsal,-3);
인라인뷰(view): from절에 서브쿼리가 들어간 것
하드디스크가 아닌 메모리 상에서만 존재하는 논리적인 테이블
인라인끼리 혹은 내/외부 조인과도 같이 조인이 가능하다.
-- 10번 부서와 20번 부서 사원 중에서 직급이 같은 사원들의 이름, 부서번호, 직급을 검색
select e1.job, e1.deptno, e1.ename, e2.deptno, e2.ename
from (select ename, deptno, job from emp where deptno=10) e1
join (select ename, deptno, job from emp where deptno=20) e2
where e1.job=e2.job;
select e.empno '사원번호', e.ename '사원 명', e.mgr '상사번호', m.ename '상사'
from emp e join emp m on e.mgr=m.empno order by e.empno; -- 상사가 없는 경우 검색되지 않음
self join: 내부 조인. 같은 테이블끼리 join한다.
해당 코드는 상사가 없는 경우엔 출력되지 않는다.
select e.empno '사원번호', e.ename '사원 명', e.mgr '상사번호', m.ename '상사'
from emp e left join emp m on e.mgr=m.empno order by e.empno;
잉여 데이터가 있는 경우에도 출력할 수 있는 Left / Right Join
Left / Right outer join: 외부 조인. 반대쪽에 데이터가 있든 없든(null), 선택된 방향에 있으면 출력 - 행 수 결정 (Left join: 오른쪽에 잉여데이터 / Right join: 왼쪽에 잉여데이터)
-- 1) comm을 받는 사원의 부서명, 부서위치, 이름, 커미션(comm) 검색
-- 2) 'dallas'(부서위치)에서 근무하는 사원 부서번호, 부서명, 이름, 직급 검색
-- 3) 'ward'(사원이름)의 부서이름과 이름 검색
-- 4) 사원이름, 급여, 급여등급 검색
-- 5) 부서명, 사원이름, 급여등급 검색
-- 6) 부서별 부서이름, 최소급여, 최대급여, 평균급여 검색
-- 7) 부서 내에서 직급별 부서번호, 부서명, 직급, 평균급여, 인원수 검색
풀이
-- 1) comm을 받는 사원의 부서명, 부서위치, 이름, 커미션(comm) 검색
select d.dname, d.loc, e.ename, e.comm from dept d
join emp e on d.deptno=e.deptno where ifnull(comm,0)<>0;
부서명, 부서위치, 이름, 커미션은 검색했으나 comm을 받는 사람만 출력하는 것에서 실패하고 시간이 끝났다.
부서명, 부서위치가 한 테이블, 사원이름, 커미션이 한 테이블로 각 두 테이블이 있으니 join을 했다.
join 조건은 두 테이블 모두 동일하게 가지고 있는 deptno
comm을 받는 사원이 조건이므로 where절에 comm이 null이 아닐 때라는 조건을 준다.
ifnull(comm,0)<>0은 comm<>0으로도 변경이 가능하다.
-- 2) 'dallas'(부서위치)에서 근무하는 사원 부서번호, 부서명, 이름, 직급 검색
select d.deptno, d.dname, e.ename, e.job from dept d
join emp e on d.deptno=e.deptno where d.loc='dallas';
부서명은 dept 테이블에 있고 / 사원이름, 사원직급은 emp 테이블에 있고 / deptno는 두 테이블 모두에 있으므로 dept 테이블과 emp 테이블을 join해주면서 deptno를 join 조건으로 줬다.
일반 조건으로는 부서위치가 'dallas'인 조건이 있으므로 dept 테이블에 있는 loc(부서위치)가 'dallas'인 것만 이라는 조건을 준다.
-- 3) 'ward'(사원이름)의 부서이름과 이름 검색
select d.dname, e.ename from dept d join emp e on d.deptno=e.deptno where e.ename='ward';
부서명은 dept 테이블, 사원 이름은 emp 테이블에 있으므로 두 테이블을 join해주고, 조건은 동일하게 가지고 있는 칼럼인 deptno를 준다.
'ward'의 부서명과 이름을 검색하라고 했으니 emp 테이블에 있는 ename의 값이 'ward'인 것만 이라는 조건을 준다.
-- 4) 사원이름, 급여, 급여등급 검색
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
사원이름은 emp 테이블, 급여등급은 salgrade 테이블에 있으므로 두 테이블을 join해준다.
join 조건으로는 emp 테이블에 있는 sal이 salgrade 테이블에 있는 losal과 hisal의 사이일 경우로 준다.
-- 5) 부서명, 사원이름, 급여등급 검색
select d.dname, e.ename, s.grade from dept d
join emp e on d.deptno=e.deptno join salgrade s on e.sal between s.losal and s.hisal;
부서명, 사원이름, 급여등급이 각 다른 테이블에 있기 때문에 각 테이블을 모두 join해준다.
join 조건은 dept에 emp를 join할 때는 deptno가 동일할 경우로 주고, salgrade를 join할 때는 emp의 sal이 salgrade의 losal과 hisal의 사이일 경우로 준다.
-- 6) 부서별 부서이름, 최소급여, 최대급여, 평균급여 검색
select d.dname '부서명', min(e.sal) '최소급여', max(e.sal) '최대급여', round(avg(e.sal),2) '평균급여'
from dept d join emp e on d.deptno=e.deptno
group by d.deptno order by d.dname;
부서명은 dept 테이블, 나머지는 emp 테이블에서 구하면 되기 때문에 dept 테이블과 emp 테이블을 join해준다.
join 조건은 deptno가 동일할 경우로 준다.
최소 급여, 최대 급여, 평균 급여 값은 각 min, max, avg 함수를 사용하여 구한다.
-- 7) 부서 내에서 직급별 부서번호, 부서명, 직급, 평균급여, 인원수 검색
select d.deptno '부서번호', d.dname '부서명', e.job '직급',
round(avg(e.sal),2) '평균급여', count(e.job) '인원수'
from dept d join emp e on d.deptno=e.deptno group by e.deptno;
select round(avg(e.sal)) '평균급여', concat(count(*), '명') '인원 수', d.deptno, d.dname, e.job
from dept d join emp e on d.deptno=e.deptno
group by d.deptno, e.job, d.dname;
위에 코드가 내가 풀다가 실패한 코드이고, 밑에 코드가 선생님이 풀이해주신 코드이다.
내가 코드를 짤 때 그룹으로 묶을 것을 적게 줘서 오류가 났었다.
우선 필요한 테이블인 dept와 emp를 join해주고 조건은 두 테이블 모두에 있는 deptno가 동일할 경우로 준다.
그룹화를 해야 하는데 select에 여러 칼럼을 적었으므로 적은 칼럼을 모두 group by에 적는다. (SQL에선 d.dname은 그룹으로 안 묶어도 실행된다. Oracle에서는 안 됨!)
-- 문)
-- 본인의 사원 이름, 부서번호를 검색하고,
-- 같은 부서의 동료사원의 사원이름, 부서번호를 검색하시오
-- 이름순으로 오름정렬하시오
-- 실행예
-- 본인 부서번호 동료 부서번호
-- allen 30 blake 30
-- allen 30 james 30
-- ...
-- blake 30 allen 30
select e.ename '본인', e.deptno '부서번호', d.ename '동료', d.deptno '부서번호'
from emp e join emp d on e.deptno=d.deptno where e.ename<>d.ename order by e.ename, d.ename;
같은 테이블(emp) 내의 값이 필요하므로 self join을 해준다.
join 조건은 같은 부서의 동료 사원이 필요하므로 deptno로 주고, 본인이 나오면 안 되기 때문에 일반 조건에 첫번째 ename과 두번째 ename이 같을 경우를 제외해준다.
이름순으로 오름정렬하라고 했기 때문에 order by에 첫번째와 두번째 ename을 모두 적어준다.
drop table if exists member;
create table member(
M_id int auto_increment primary key,
M_nick varchar(20) unique not null,
M_name varchar(20) not null,
M_age tinyint unsigned default 0 not null,
M_birth date default (current_date) not null
);
desc member;
select*from member;
테이블을 만들기 전, 테이블이 이미 있을 수 있으므로 테이블이 존재한다면, 삭제한다는 코드를 추가한다. (drop table if exists)
desc member: 테이블 구조를 확인한다.
select*from member: 테이블에 있는 모든 칼럼과 데이터를 확인한다.
current_date / curdate는 아직 잘 모르는 코드
생성 시 제약
auto_increment: 새 행 생성 시마다 자동으로 1씩 증가 (1부터 시작)
primary key: 중복 입력이 불가능하고, null(빈 값)이 불가능하다. 테이블에 하나만 존재할 수 있다.
update member set M_name='n번째친구'; -- 1
update member set M_name='n번째친구' where M_id=1; -- 2
-- update member set M_age+=10; -- 3
update member set M_age=M_age+10 where M_id=3; -- 4
-- 첫번째 나이 10% 증가
update member set M_age=M_age*1.1 where M_id=1;
1번 코드처럼 작성하면 member 테이블에 있는 M_name은 모두 'n번째친구'로 데이터가 바뀌어버린다.
2번 코드처럼 where절에 조건을 줘서 바꿔줘야 한다.
3번 코드처럼 자바에서 사용하던 += 연산은 불가능하다. 10을 더하고 싶다면 4번 코드처럼 작성해야 한다.
마지막 코드는 %를 사용하지 못하기 때문에 곱하기(*)를 이용하여 계산해주어야 한다.
where절에서 조건을 줄 때 primary key로 주는 것이 바꾸려는 안전하게 데이터만 바꿀 수 있고, 에러가 나지 않을 수 있다. (중복값이 있을 수 있기 때문에)
