SQL - DML ( select, insert, update, delete)

1. INSERT

DROP TABLE TT;
CREATE TABLE TT(SEQ NUMBER, VAL VARCHAR2(10));  -- DDL은 롤백XXXXX
INSERT INTO TT(SEQ,VAL) VALUES(66,'육육');
INSERT INTO TT          VALUES(66,'육육');
SELECT * FROM TT;
 
-- null을 입력하는 방법
INSERT INTO TT VALUES(3, NULL); --- 널  
INSERT INTO TT VALUES(4, '');   --- 널
INSERT INTO TT(SEQ) VALUES(77); --- 널
INSERT INTO TT VALUES(99, 'null');
INSERT INTO TT VALUES(88, ' ');
 

 

2. UPDATE

---------------------------------- UPDATE
-- emp_cp 테이블 복제
CREATE TABLE EMP_CP AS (SELECT * FROM EMP);
SELECT * FROM EMP_CP order by deptno;
 
SELECT SAL*100 FROM emp_cp WHERE DEPTNO = 10;
 
--- 테이블 전체가 다 변경 ~!!!!!!!!!주의주의주의
UPDATE EMP_CP 
SET SAL = 55555;
 
UPDATE EMP_CP 
SET SAL=55555, 
    JOB='GUGUUGU',
    MGR = NULL   --------------*************** col = null XXXX   col is null
WHERE DEPTNO=10;
 
SELECT * FROM EMP_CP order by deptno;
 
commit;
 
rollback;
 
-- EMP_CP 테이블에서 7900 사원정보 수정
-- (7844 직업과 같고, 7654의 급여와 같게)
select job from emp where empno=7844;
select sal from emp where empno=7654;
 
select * from EMP_CP where empno=7900;
 
UPDATE EMP_CP
SET JOB=(SELECT JOB FROM EMP WHERE EMPNO=7844) , 
    SAL=(select sal from emp where empno=7654)
WHERE EMPNO=7900;
 
commit;
rollback;

 

3. DELETE

---------------------------- delete
SELECT * FROM EMP_CP;
DELETE   
--select *
FROM EMP_CP
where deptno=30;
 
-- smith와 같은 부서 사원들 삭제
SELECT DEPTNO FROM EMP_CP WHERE ENAME='SMITH';
 
DELETE 
FROM EMP_CP
WHERE DEPTNO = (select deptno from emp_cp where ename='SMITH');
 
select * from emp_cp;
 
 
rollback;
 
----------------------------------- 테이블 삭제
DELETE FROM QQQ;   -- 테이블 남아있다 , 데이터만 삭제, 백업로그가 있다
truncate table QQ; -- 테이블 남아있다 , 데이터만 삭제
DROP TABLE QQQ;    -- 테이블(데이터 포함) 삭제
 
DROP TABLE QQ;