▣ 078 데이터 입력하기(INSERT)
- SQL 의 종류 ?
* SQL 의 종류 ? 1. Query : select 문의 6가지절 2. DML (Data Manipulation Language) : insert, update, delete, merge, select 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 문
예제. 사원 테이블에 아래의 데이터를 입력하시오 !
사원번호: 9321
사원이름: JANE
월급 : 4000
입사일 : 오늘날짜
insert into emp(empno, ename, sal, hiredate )
values( 9321, 'JANE', 4000, to_date('2023/07/06', 'RRRR/MM/DD') );
설명: emp 다음에 괄호를 열고 컬럼들을 기술했으면 values 다음에 해당하는 값을 기술해줘야 합니다. 만약 아래와 같이 괄호를 열고 컬럼을 안썼으면 values 다음에 다 써줘야합니다
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno )
values( 9321, 'JANE', 'dba', 7788, to_date('2023/07/06', 'RRRR/MM/DD'), 4000,
300, 10 );
숫자를 입력할 때는 양쪽에 싱글 쿼테이션 마크를 안둘러줘도 되는데
문자와 날짜를 입력할 때는 양쪽에 싱글 쿼테이션 마크를 둘러줘야 합니다.
문제431. 이름이 JANE 인 사원의 이름과 입사일을 출력하시오 !
select ename, hiredate
from emp
where ename='JANE';
문제432. 부서테이블에 아래의 데이터를 입력하시오 !
부서번호 : 50
부서명 : data team
부서위치 : seoul
insert into dept( deptno, dname, loc )
values( 50, 'data team', 'seoul' );
select * from dept;
rollback; <---- 그동안 작업했던 데이터 입력, 수정, 삭제 작업을 취소하는 명령어
select * from dept;
문제433. 아래의 데이터를 우리반 테이블에 입력하시오 !
학생번호 : 31
학생이름 : 김인호
나이 : 30
생일 : 1994/11/04
주소 : 서울시 강남구
통신사 : sk
insert into emp17(empno, ename, age, birth, address, telecom )
values( 31, '김인호', 30, to_date('1994/11/04','RRRR/MM/DD'), '서울시 강남구',
'sk' );
rollback;
**※ null 값을 입력하는 방법 2가지**
1. 명시적 입력 : insert into emp(empno, ename, sal )
values ( 7234, null, 4000 );
insert into emp(empno, ename, sal )
values( 7456, '', 5000 );
1. null
2. '' <---- 싱글 쿼테이션 마크를 딱 붙여서
2. 암시적 입력 : insert into emp(empno, ename, sal )
values( 3933, 'aaa', 5000 );
입력하는 컬럼외에 나머지 컬럼들은 다 null 이 자동으로 입력됩니다.문제434. 이름이 null 인 사원들의 이름과 월급을 출력하시오 !
select ename, sal
from emp
where ename is null;
문제435. 아래와 같이 이름에 공백을 입력하시오 !
insert into emp(empno, ename, sal )
values( 4956, ' ', 6000 );
문제436. 이름이 null 이 아닌 사원들의 이름과 월급을 출력하시오
select ename, sal
from emp
where ename is not null;
문제437. 위의 결과를 다시 출력하는데 이름이 공백인 사원도 제외하고 출력하시오 !
select ename, sal
from emp
where trim(ename) is not null ;
설명: trim 을 사용하면 공백이 제거가 되면서 null로 변경됩니다.
select ename, sal
from emp
where rtrim(ename) = 'SCOTT';
insert into emp(empno, ename, sal )
values( 2393, 'JACK ', 4000) ;
select ename, sal
from emp
where rtrim(ename)='JACK'; <--- 이 SQL 튜닝방법은 index 에서 배웁니다.
좌변 = 우변
설명: rtrim 을 사용하게 되면 오른쪽의 공백이 제거가 됩니다.
좌변을 가공하면 SQL 검색성능이 느려집니다.
select ename,sal
from emp
where ename='JACK';
정리:
- insert 하고 나서 commit 을 안했으면 바로 rollback 이 가능합니다.
- insert 할 때 값에 양쪽에 공백이 포함되지 않도록 주의해야합니다.
문제438. (MySQL) Mysql의 emp 테이블에 아래의 데이터를 입력하시오 !
사원번호: 5969
사원이름: JACK
월급 : 3000
부서번호: 20
insert into emp(empno, ename, sal, deptno )
values( 5969, 'JACK', 3000, 20 );
**MySQL은 자동 커밋기능이 기본값으로 활성화** 되어있습니다.
**select @@autocommit;**
**※ MySQL 을 사용하기전에는 반드시 자동 커밋기능이 활성화되었는지 확인**하고 시작하세요
set autocommit=FALSE; <-- 지금 접속한 세션(session) 에서만 유효
database level 로 하려면 dba 가 해줘야합니다.
( db 관리 수업에서 배우게 됩니다. )
select @@autocommit;
0 <--------------------- 0 이면 자동 커밋기능이 꺼진겁니다.문제439. 아래의 데이터를 emp 테이블에 입력하시오 !
사원번호: 5543
사원이름 : JONE
월급 : 3999
입사일: 오늘날짜
Oracle> insert into emp(empno, ename, sal, hiredate)
values( 5543, 'JONE', 3999, to_date('2023/07/06','RRRR/MM/DD') );
MySQL> insert into emp(empno, ename, sal, hiredate)
values( 5543, 'JONE', 3999, date_format('2023/07/06','%Y/%m/%d') );
※ 위와 같이 날짜를 입력해야 db 의 날짜형식에 상관없이 날짜를 입력할 수
있습니다.
rollback;
문제440. (MySQL) 아래의 NULL 값을 mySQL의 emp 테이블에 입력하시오 !
사원번호: 3945
사원이름 : null값
월급 : 3000
insert into emp(empno, ename, sal )
values( 3945, null, 3000 );
insert into emp(empno, ename, sal )
values( 3946, '', 3000 ); <---- oracle 과는 다르게 mysql 은
null 값으로 입력되지 않습니다.
select empno, ename
from emp
where ename is not null;
▣ 079 데이터 수정하기(UPDATE)
예제. 이름이 SCOTT 인 사원의 월급을 5600 으로 수정하시오
update emp
set sal = 5600
where ename='SCOTT';
문제441. 직업이 SALESMAN인 사원들의 커미션을 9000 으로 수정하시오!
update emp
set comm = 9000
where job='SALESMAN';
문제442. 월급이 2000 이상인 사원들의 부서번호를 10번으로 변경하시오 !
update emp
set deptno = 10
where sal >= 2000;
문제443. 사원 테이블의 월급을 모두 0으로 변경하시오 !
update emp
set sal = 0 ;
where ename = ‘SCOTT’; <--- 세미콜론 이런 부분 조심해야한다.
rollback;
문제444. ALLEN 의 직업을 null 값으로 변경하시오 !
update emp
set job = null
where ename='ALLEN';
문제445. (mySQL) 직업이 SALESMAN 인 사원들의 월급을 8000 으로
변경하시오!
select @@autocommit;
update emp
set sal = 8000
where job='SALESMAN';
rollback;
문제446.(mySQL) 사원 테이블에서 커미션이 null 인 사원들의 부서번호를
90번으로 수정하시오 !
update emp
set deptno = 90
where comm is null;
점심시간 문제
[7월 6일 점심시간 문제] 이름, 월급, 부서번호, 직업,
자기가 속한 부서번호의 평균월급,
자기가 속한 직업의 평균월급을 출력하시오! (스칼라 서브쿼리로 수행해주시면 됩니다.)
select ename, sal, deptno, job,
avg(sal) over (partition by deptno) as 부서번호평균,
avg(sal) over (partition by job) as 직업평균
from emp;
select ename, sal, deptno, job, (select avg(sal)
from emp v
where v.deptno = s.deptno),
(select avg(sal)
from emp t
where t.job = s.job)
from emp s;▣ 080 데이터 삭제하기(DELETE, TRUNCATE, DROP)
책 222 페이지의 표
| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| 명령어 종류 | DML(rollback 가능) | DDL (수행이 되면서 자동 커밋 이 된다) | DDL (수행이 되면서 자동 커밋 이 된다) |
| 데이터 | 삭제 | 삭제 | 삭제 |
| 저장 공간 | 남김 | 삭제 | 삭제 |
| 저장 구조 | 남김 | 남김 | 삭제 |
| 취소 여부 | 가능 | 불가능 | 불가능 |
| 플래쉬백 여부 | 가능 | 불가능 | 가능 |
TRUNCATE는 진짜 조심해야해 flashback이 안되기 때문에!!
예제1. delete 문으로 emp 테이블을 지우시오 !
delete from emp;
rollback;
예제2. emp 테이블을 delete 로 지우고 commit 하시오
delete from emp;
commit;
예제3. 타임머신 기능으로 복구를 시도 합니다.
#1. emp 테이블을 flashback 이 가능한 상태로 구성
SQL> alter table emp enable row movement;
#2. 현재시간에서 5분전으로 emp 테이블을 되돌립니다.
SQL> flashback table emp to timestamp
SQL> ( systimestamp - interval '10' minute );
SQL> commit;
#3. flashback 이 가능한 database 의 설정 시간을 확인하시오 !
SQL> show parameter undo
undo_retention 3600
60분 안에 flashback 으로 복구 할 수 있습니다.
확실하게 60분을 보장하지는 않지만 최대한 60분을 보장을 해줍니다.
문제447. 직업이 SALESMAN인 사원들을 삭제하시오 !
delete from emp
where job='SALESMAN';
rollback;
문제448. 월급이 2400 이상인 사원들을 지우시오 !
delete from emp
where sal >= 2400;
문제449.(MySQL) 자동 커밋기능을 해제하는것을 Database 레벨로 지정하시오
답: edit ---> preference ---> SQL Execution ---> new connection auto commit 을 해제합니다.
문제450. (MySQL) emp 테이블을 모두 delete 하시오 !
delete from emp;
rollback;
문제451. emp 테이블을 truncate 하시오 !
truncate table emp;
rollback;
select * from emp;
문제452. 지금 truncate 한 emp 테이블의 데이터를 flashback 으로 복구하시오!
alter table emp enable row movement;
flashback table emp to timestamp
( systimestamp - interval '5' minute );
ORA-01466: 테이블 정의가 변경되었습니다 데이터를 읽을 수 없습니다
※ truncate 는 flashback 이 불가능 합니다.
문제453. emp_backup 으로 emp 테이블을 복구하시오 !
insert into emp
select *
from emp_backup;
select * from emp;
※ truncate 명령어를 수행하기 전에 반드시 create table as select 문으로
백업을 받고 수행하기를 권장합니다.
create table emp_backup2
as
select * from emp;
문제454.(MySQL) MySQL 에 있는 emp 테이블을 truncate 하시오 !
create table emp_backup2
as
select * from emp;
truncate table emp;
insert into emp
select *
from emp_backup2;
문제455. emp 테이블을 drop 하시오 !
drop table emp ;
select * from emp;
문제456. 휴지통속에 emp 테이블이 있는지 확인하시오 !
select *
from user_recyclebin
order by droptime desc;
문제457. 휴지통속에서 있는 emp 테이블을 복구하시오 !
flashback table emp to before drop;
select * from emp;
문제458.(MySQL) MySQL의 emp 테이블을 drop 하시오 !
create table emp_backup7
as
select * from emp;
drop table emp;
※ MySQL 은 Oracle 과 같은 flashback 이 없습니다.
문제459. backup받은 emp_backup7으로 emp 테이블을 생성하시오
create table emp
as
select * from emp_backup7;
select * from emp;
문제450. c ##scott 유져가 가지고 있는 테이블 리스트를 확인하시오 !
select table_name
from user_tables;
문제451. 지금 현재 가지고 있는 테이블 리스트의 정보를 담는 테이블을
다음과 같이 생성하시오 !
create table table_list_0706
as
select *
from user_tables;
문제452. 짝꿍하고 자리를 바꿔서 짝꿍의 테이블중 2개를 drop 하고
다시 자리로 돌아오시오 !
문제453. 짝꿍이 drop 한 테이블이 뭔지 확인하세요 ~
select table_name from table_list_0706
minus
select table_name from user_tables;
또는
select original_name, droptime
from user_recyclebin
order by droptime desc;
문제454. 짝꿍이 drop 한 테이블을 복구 합니다.
flashback table 테이블명 to before drop;
문제455. 휴지통을 비웁니다.
purge recyclebin;
select *
from user_recyclebin;
※ 디스크 공간확보를 합니다. dba 는 항상 db 에 여유공간을 모니터링
해야합니다. 공간에 data 가 꽉차면 db 에 작업이 안됩니다.
▣ 081 데이터 저장 및 취소하기(COMMIT, ROLLBACK)
1. commit : 현재까지 작업한 db 에 변경작업을 영구히 저장하겠다.
2. rollback : 현재까지 작업한 db 에 변경작업을 취소하겠다.문제456. A 세션에서 emp 테이블의 월급을 모두 0 으로 변경하시오 !
update emp
set sal = 0;
select ename, sal from emp;
commit;
문제457. A 세션에서 flashback table 로 emp 테이블을 10분전으로
복구하시오
alter table emp enable row movement;`
flashback table emp to timestamp
( systimestamp - interval '10' minute );
※ flashback 되면서 자동 커밋 되었습니다.
※ 암시적 commit
1. DDL 명령어 수행했을때 : create, alter, drop, truncate, rename
2. DCL 명령어 수행했을때 : grant, revoke
3. 정상종료(exit)
※ 암시적 rollback
1. db 가 비정상적으로 종료되었을때
국민은행 DB
예금 통장 적금 통장
100만원 ---------------------->
delete insert문제458. 아래의 DML 작업중 DB 에 반영된 작업은 무엇인가?
1. insert into emp....
2. insert into emp....
3. update emp .....
4. update emp ....
5. commit;
6. insert into emp...
7. update emp ....
8. rollback;
답: 1~4번까지 db 에 반영됩니다.문제459. savepoint 기능을 다음과 같이 테스트 하시오!
insert into dept values( 60, 'aaa', 'bbb');
savepoint a ;
insert into dept values( 70, 'bbb', 'ccc') ;
savepoint b;
insert into dept values( 80, 'ccc', 'ddd');
savepoint c;
delete from dept;
select * from dept;
rollback to b;
설명: savepoint 는 나중에 rollback 을 할 지점을 표시하는 것입니다.
▣ 082 데이터 입력, 수정, 삭제 한번에 하기(MERGE)
merge 는 insert 와 update 와 delete 를 한번에 수행하는 명령어
upsert 로 많이 알려져 있습니다. 튜닝방법으로 많이 쓰입니다.
emp 와 dept 테이블을 다시 모두 drop 하고 새로 생성하세요 ~
문제460. (복습문제) emp 와 dept 를 조인해서 이름과 부서위치를
출력하시오 !
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno;
db 모델링 중에 반정규화 ? 위와 같이 조인을 자주 해야하는 상황인데 조인을 할 때 너무 성능이 느리면 그냥 loc 컬럼을 emp 테이블에 추가 시킵니다.
그리고 조인하지 않고 다음과 같이 검색합니다.
select ename, loc
from emp;
문제461. emp 테이블에 loc 컬럼을 추가하시오 ! (컬럼추가 작업)
alter table emp
add loc varchar2(10);
select ename, loc
from emp;
문제462. 지금 방금 추가한 loc 컬럼의 데이터를 해당 사원의 부서위치로
값을 갱신하시오 !
튜닝후:
merge into emp e
using dept d
on ( e.deptno = d.deptno )
when matched then
update set e.loc = d.loc;
select * from emp;
문제463. (복습문제) emp 와 salgrade 테이블을 서로 조인해서
이름과 월급과 grade 를 출력하시오 !
select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal ;
문제464. (only dba) emp 테이블에 grade 컬럼을 추가하시오 !
alter table emp
add grade number(10) ;
문제465. (오늘의 마지막 문제) emp 테이블에 추가한 grade 컬럼에
해당 사원의 월급의 등급으로 값을 갱신하시오 !
(merge 문으로 하세요 )
merge into emp e
using salgrade s
on (e.sal between s.losal and hisal )
when matched then
update set e.grade = s.grade;
select * from emp;7월5일
유명한 SQL 튜닝 공식을 스스로 알아내보세요.
SQLP주관식) 이름, 월급, 직업이 SALESMAN인 사원들의 토탈월급,
직업이 SALESMAN인 사원들의 최대월급,
직업이 SALESMAN인 사원들의 최소월급
을 출력하시오 !
튜닝전: select ename, sal,
( select sum(sal) from emp where job='SALESMAN' ) 토월급,
( select max(sal) from emp where job='SALESMAN' ) 최대월급,
( select min(sal) from emp where job='SALESMAN' ) 최소월급
from emp;
--> emp 테이블을 4번이나 접근한다.
튜닝후: ???
select ename, sal,
sum(decode(job, 'SALESMAN', sal)) over () 토탈월급,
max(decode(job, 'SALESMAN', sal)) over () 최대월급,
min(decode(job, 'SALESMAN', sal)) over () 최소월급
from emp;
select ename,sal,substr(급여,1,4)as 합계,substr(급여,5,4)AS 최대급여,substr(급여,9,4)AS 최소월급
from(
select ename,sal,(select sum(sal)||max(sal)||min(sal) from emp where job='SALESMAN' )AS 급여
FROM EMP
);
SELECT e.ename, e.sal, s.최대월급, s.최소월급, s.토탈월급
FROM emp e, (
SELECT max(sal) 최대월급, min(sal) 최소월급, sum(sal) 토탈월급
FROM emp
WHERE job = 'SALESMAN') s;
[유명한 SQL튜닝 기법] 튜닝전 SQL
튜닝전 : select deptno, ename, sal,
( select sum(sal) from emp s where s.deptno = e.deptno ) 토탈월급,
( select max(sal) from emp s where s.deptno = e.deptno ) 최대월급,
( select min(sal) from emp s where s.deptno = e.deptno ) 최소월급
from emp e;
튜닝후 : ???
※ select 절의 서브쿼리를 용어로 ? 스칼라(scalar) 서브쿼리
메인 쿼리의 컬럼이 서브쿼리에 들어가져 있으면 ? 상호관련 서브쿼리