sql중급 / DML문(insert, update, delete)

078 데이터 입력하기(INSERT)

ㅁSQL 종류

1. Query : select문의 6가지 절, 함수, 조인, 서브쿼리, 집합연산자

2. DML (Data Manpulation Language) : insert, update, delete, merge

3. DDL (Data Definition Language) : create, alter, drop, truncate, rename

4. DCL (Data Control Language) : grant, revoke

5. TCL (Transaction Control Language) : commit, rollback, savepoint

 

insert into 테이블명(컬럼명1,컬럼명2,....)    values(값1,값2,.....)

문자의 경우 양쪽에 작은따옴표로 둘러줘야함. 날짜는 to_date를 이용해 날짜형식을 맞춰줘야 데이터를 맞게 입력할 수 있음.

/* 사원 테이블에 데이터를 입력하는데 사원번호는 2812, 사원이름은 JAC,
월급은 3500, 입사일은 2021년 11월 16일, 직업은 ANALYST로 입력하시오 */

insert into emp(empno, ename, sal, hiredate, job)
 values(2812, 'JACK',3500,to_date('2021/11/16','RRRR/MM/DD'),'ANALYST') ;

문제382. 사원테이블의 아래의 데이터를 입력하시오

사원번호 : 4832 / 사원이름 : JANE / 월급 : 6800 / 부서번호 : 20 

insert into emp(empno, ename, sal, deptno)   values(4832,'JANE',6800,20);

문제383. 아래의 데이터를 부서 테이블에 입력하시오

부서번호 : 50 / 부서명 : transport / 부서위치 seoul

insert into dept(deptno, dname, loc)     values(50, 'TRANSPORT','SEOUL');

문제384. 아래의 데이터를 emp테이블에 입력하시오

사원번호 : 8392 / 사원이름 JAMES / 입사일 : 오늘날짜

insert into emp(empno, ename, hiredate)     values(8392, 'JAMES', sysdate);

문제385. 오늘 입사한 사원의 이름과 입사일을 출력하시오

select ename, hiredate
 from emp
 where hiredate = sysdate;
 
 /* 위와같이 하면 출력이 안됨. sysdate로 데이터를 입력하면 날짜뿐만 아니라 시분초도 입력이 됨.
 아까 입력했던 sysdate 시분초와 지금 검색하려고 넣은 sysdate가 달라서 검색이 안됨 
 
 to_date('2021/11/17','RRRR/MM/DD')로 넣은 경우에는 2021년 11월 17일 00시00분00초로 입력되기때문에
 날짜로 검색하면 검색됨 
 to_date(sysdate)로 하면 시분초가 들어가지만, to_date(sysdate,'RRRR/MM/DD')로 하면 00시00분00초로 들어감
 
 날짜 데이터를 입력할 때는 반드시 to_date사용해야 입력도 잘되고 검색도 잘됨
 */

ㅇ테이블에 null값을 입력하는 방법

1. 암시적으로 입력하는 방법

insert into emp(empno, ename, sal)    values(1234,'aaa',3400);

사원번호, 이름, 월급 외에는 모두 null로 데이터가 입력됨

2. 명시적으로 입력하는 방법

 - null

 - ''(작은 따옴표 2개를 붙여서)

insert into emp(empno, ename, sal)    values(3456,null,3200);

insert into emp(emono, ename, sal)    values(2345,'',3500);

 

insert into emp(empno,ename,sal)     values(4567,'     ',5400);

 

문제386. 이름이 null이 아닌 사원들의 이름과 월급을 출력하시오

select ename, sal
 from emp
 where ename is not null;
 
 -- 공백도 안나오게 할 때(내가 쓴 코드)
 select ename, sal
 from emp
 where ename is not null and ename not like '%  %';
 
 -- 다른 방법 (trim 사용)
 select ename, sal
 from emp
 where trim(ename) is not null;

 

 

079 데이터 수정하기(UPDATE)

update 테이블명

set 변경할 컬럼

where 변경하고 싶은 조건

--SCOTT의 월급을 3900으로 수정하시오
update emp
set sal = 3900
where ename = 'SCOTT';

문제387. 부서번호가 10번인 사원들의 커미션을 9000으로 수정하시오

update emp
set comm = 9000
where deptno = 10;

문제388. 부서번호가 10,20인 사원들의 직업을 ANALYST로 변경하시오

update emp
 set job = 'ANALYST'
 where deptno in ( 10, 20);

문제389. 직업이 null인 사원들의 직업을 SALESMAN으로 변경하시오

update emp
set job = 'SALESMAN'
where job is null ;

ㅇupdate 사용시 주의사항

where절을 잘못작성해서 where절을 안쓴다거나 잘못쓰면 안됨

 

 

080 데이터 삭제하기(DELETE, TRUNCATE, DROP)

delete from 테이블명

where 조건

-- SCOTT의 데이터를 지우시오
delete from emp
where ename = 'SCOTT';

문제390. 월급이 3000이상인 사원들을 지우시오

delete from emp
where sal >= 3000;

ㅇ만약 실수로 테이블을 전부 다 지워버렸다면,

타임머신을 타고 과거로 가서 emp테이블을 살려보기(오라클 기본설정 골든타임 15분(회사마다 설정 다름))

-- flashback query문
select *
 from emp as of timestamp to_timestamp('2021/11/17:15:24:00','RRRR/MM/DD:HH24:MI:SS');
 
-- 복구할 수 있는 시간 확인 방법
select name, value
 from v$parameter
 where name = 'undo_retention';
 -- undo_retention	900  = 15분
 
-- 특정시간으로 emp테이블 복구하기
-- 1. emp테이블을 flashback이 가능한 상태로 변경
alter table emp enable row movement;
-- 2. emp테이블을 2021/11/17:15:24:00로 되돌리기
flashback table emp to timestamp to_timestamp('2021/11/17:15:24:00','RRRR/MM/DD:HH24:MI:SS');
-- 3. 커밋으로 완료

문제391. 사원테이블의 월급을 전부 0으로 변경하고 커밋하시오

update emp
set sal = 0;

문제392. 사원테이블을 원래대로 복구하시오

select *
 from emp as of timestamp to_timestamp('2021/11/17:16:00:00','RRRR/MM/DD:HH24:MI:SS');
 
alter table emp enable row movement;

flashback table emp to timestamp to_timestamp('2021/11/17:16:00:00','RRRR/MM/DD:HH24:MI:SS');

 

ㅇ 오라클에서 데이터를 삭제하는 3가지 방법

	delete	truncate	drop
데이터	삭제	삭제	삭제
저장공간	유지	삭제	삭제
저장구조	유지	유지	삭제
취소여부 (rollback)	가능	불가능	불가능
플래쉬백 여부	가능	불가능	가능

truncate와 drop의 차이

truncate는 테이블의 구조를 남겨둠. 테이블의 구조를 남겨두면 나중에 데이터를 새로 입력할 때 테이블을 생성하지 않아도 됨. 데이터를 모두 삭제할거라면 delete보다는 truncate가 속도가 더 빠름.

 

ㅇ테이블 백업하기

-- emp_backup이라는 이름으로 emp테이블과 똑같은 테이블을 생성
create table emp_backup
as
 select *
  from emp;
  
select *
 from emp_backup;
 
-- sqldeveloper로 백업하는 방법
접속창의 테이블 > 해당 테이블 선택 > 익스포트

 

 

문제393. 부서번호, 부서번호별 평균월급을 출력하시오

select deptno, avg(sal)
 from emp
 group by deptno;

문제394. (오늘의 마지막 문제) 부서번호, 이름, 월급, 자기가 속한 부서번호의 평균월급을 출력하는데, 자기의 월급이 자기가 속한 부서번호의 평균월급보다 더 많은 월급을 받는 사원들만 출력하시요.

select *
 from (
          select deptno, ename, sal, avg(sal) over (partition by deptno) as avg
           from emp
        )
 where sal >= avg;