데이터베이스 수업 기록
22-11-15 DML 구문, 트랜잭션, DDL문
JadeStone
2022. 11. 16. 10:09
DML 구문 문제5번 못푼거 풀던 내용 복습할 때 마저 풀기.
<DML (Data Manipulation Language)>
#insert
--insert
desc departments;
--1st
insert into departments values(280, '개발자', null, 1700);
--2st
insert into departments(department_id, department_name, location_id) values(290, 'DBA', 1700);
select * from departments;
rollback; --되돌리기
insert into departments (Department_ID, Department_NAME, MANAGER_ID, LOCATION_ID)
values(290, '디자이너', null , 1700);
insert into departments (Department_id, department_name, manager_id, location_id)
values(300, 'DB관리자', null, 1800);
insert into departments (Department_id, department_name, manager_id, location_id)
values(310, '데이터분석가', null, 1800);
insert into departments (Department_id, department_name, manager_id, location_id)
values(320, '퍼블리셔', 200, 1800);
insert into departments (Department_id, department_name, manager_id, location_id)
values(330, '서버관리자', 200, 1800);
rollback;
--실습을 위한 사본테이블
create table emps as (select * from employees where 1 = 2); --데이터x 구조만 복사
SELECT *FROM EMPS;
--3nd (서브쿼리절) -values를 서브쿼리절로 대체
INSERT INTO EMPS (SELECT * FROM EMPLOYEES WHERE JOB_ID = 'IT_PROG');
--위에랑 같은 구문
DESC Emps;
INSERT INTO EMPS (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID)
(SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID FROM EMPLOYEES WHERE JOB_ID = 'FI_MGR');
SELECT *FROM EMPS;
--
INSERT INTO EMPS (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID)
VALUES ((select max(employee_id)+100 from emps),'test','test','test',sysdate,'test');
#UPDATE
--update
update emps set salary = 10000; --where절이 없으면 salary의 모든행이 10000이 됨. 이렇게 하면 안됨 ★
rollback; -- 잘못된거 취소
update emps set salary = 10000 where employee_id = 103;--값
update emps set salary = salary * 1.1 where employee_id = 103; --연산된 결과
--여러행 UPDATE
update emps set phone_number = '515.123.4566', manager_id = 102 where employee_id = 103; --여러컬럼일 경우 ,로 나열
--update 서브쿼리
update emps set salary = (select salary from emps where employee_id =104) where employee_id = 103;
update emps set (salary ,phone_number) = (select salary, phone_number
from emps
where employee_id =104)
where employee_id = 103;
--
update emps set commission_pct = 0.2 where department_id = (select department_id from emps where first_name = 'Diana');
#Delete
*행 삭제는 실행하기 전에 반드시 확인하는 습관을 가지세요.
지울 때 웬만하면 기본키(PM)로 지우기, 서브쿼리같은거 쓸 생각 하지도 말기.
--delete
delete from emps where employee_id = 208;
delete from emps where job_id = 'FI_MGR';
--delete서브쿼리
delete from emps where department_id = (select department_id from emps where first_name = 'Diana');
rollback;
--다른 테이블에 fk(foreign key)로 쓰이고 있는 pk는 삭제되지 않음.
select * from departments;
select * from employees;
delete from departments where department_id = 50;
#MERGE
*기록을 남기는 테이블을 주기적으로 주 테이블에 업데이트할 때 사용.
rollback;
select * from emps;
delete from emps where employee_id in (104, 105, 106, 107, 108, 208);
--merge(있으면 업데이트, 없으면 인서트)
select * from employees where job_id = 'IT_PROG';
--동일한 형식의 구조를 가진 테이블로 부터 merge
merge into emps a --(타겟 테이블)
using (select * from employees where job_id = 'IT_PROG') b --조인구문(적용할 데이터)
on (a.employee_id = b.employee_id) --조인조건(키)
when matched then --조건에 일치할경우 타겟테이블에 실행
update set
a.phone_number = b.phone_number,
a.hire_date = b.hire_date,
a.salary = b.salary,
a.commission_pct = b.commission_pct,
a.manager_id = b.manager_id,
a.department_id = b.department_id
when not matched then --조건에 일치하지 않는 경우 타겟테이블에 실행
insert values --컬럼만 지정도 가능
(b.employee_id, b.first_name, b.last_name,
b.email, b.phone_number, b.hire_date, b.job_id,
b.salary, b.commission_pct, b.manager_id, b.department_id);
select * from emps;
--다른 테이블에서 데이터를 비교하여 가져오는것이 아니라, 직접 값을 넣고자 한다면 DUAL을 사용할 수 있습니다.
--(단 on절 은 키를 통한 연결이 들어가야 합니다.)
MERGE INTO EMPS A
USING DUAL
ON (A.EMPLOYEE_ID = 1000)
WHEN MATCHED THEN
UPDATE SET FIRST_NAME = 'TEST1',
LAST_NAME = 'TEST2'
WHEN NOT MATCHED THEN
INSERT(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
HIRE_DATE,
JOB_ID)
VALUES (1000, 'ADMIN1', 'ADMIN2', 'ADMIN', SYSDATE, 'ADMIN');
#CTAS(사본테이블)
*실습을 위해서 가상의 테이블 만들어서 쓸 때 사용.
<트랜잭션>
--트랜잭션
--commit, rollback;
--오토커밋 여부
show autocommit;
--오토커밋 on
set AUTOCOMMIT on;
set AUTOCOMMIT off;
select * from depts;
delete from depts where department_id = 10;
SAVEPOINT delete10;
delete from depts where department_id = 20;
SAVEPOINT delete20;
--savepoint로 돌아가기
ROLLBACK to SAVEPOINT delete10;
Rollback; --마지막 commit시점
commit; --(이전 시점으로 돌아가지 못함)
*COMMIT 과 ROLLBACK 기억해야함.
*롤백의 적용 되는 모습
<CRUD> DDL문
#테이블 생성(CREATE TABLE)
*테이블명 생성시 오라클은 대소문자 구분하지않음. 하지만 다른 데이터베이스들은 구분함.
*스키마는 데이터베이스명을 말하는거.
*데이터 타입은 알아둬야함.
-char 타입은 정확히 한글자나 혹은 특정한 글자 수만 넣을 때 사용.
-NUMBER(p,s) -> p는 정수의 자리수, s는 소수의 자리수.
-CLOB -> 문자 저장할 때 사용하는 것.
-BLOB -> 파일이나 동영상을 넣을 떄 사용, 하지만 정책에 따라서 다름(db의 용량을 사용하는데 비용이들기 때문)
CLOB,BLOB 는 대용량 데이터를 저장할 때 사용.
★사진에 빨간거 체크 한거랑 위에 써놓은거 말고는 안씀.
--DDL문 CREATE문
CREATE TABLE DEPT2 (
DEPT_NO NUMBER(2), --숫자 타입의 정수 두자리만 저장가능.
DEPT_NAME VARCHAR2(20), --가변문자(20BYTE, 영어=1BYTE,한글=2BYTE)
DEPT_YN CHAR(1), --고정문자(1BYTE) 쓰던, 쓰지않던 1BYTE 고정으로 사용한다는 뜻
DEPT_DATE DATE,
DEPT_BONUS NUMBER(10,3) --(정수자리수, 소수자리수)
);
DESC DEPT2;
INSERT INTO DEPT2 VALUES(99, '영업', 'Y', SYSDATE, 3.14);
INSERT INTO DEPT2 VALUES(100, '회계', 'Y', SYSDATE, 14.123); --DEPT_NO 자리수가 2자리수까지임
COMMIT;
SELECT * FROM DEPT2;
#테이블 구조 변경(ALTER TABLE)
-ALTER-
*ADD
COLUMN 추가
ALTER TABLE DEPT2 ADD (DEPT_COUNT NUMBER(3));
DESC DEPT2;
*RENAME
COLUMN 이름 바꾸기
ALTER TABLE DEPT2 RENAME COLUMN DEPT_COUNT TO EMP_COUNT;
DESC DEPT2;
*MODIFY
COLUMN 사이즈 조정 , 단 데이터가 이미 담겨있을경우 데이터 크기보다 작게 바꾸는건 안됨.
--컬럼 타입 변경(데이터가 있을 때, 손상되는 크기로 줄일 수 없음)
ALTER TABLE DEPT2 MODIFY (EMP_COUNT NUMBER(5));
DESC DEPT2;
*DROP
COLUMN 삭제
--컬럼 삭제
ALTER TABLE DEPT2 DROP COLUMN EMP_COUNT;
DESC DEPT2;
-테이블 삭제-
--테이블 삭제
DROP TABLE DEPT2;
DROP TABLE EMPLOYEES; --제약조건을 삭제 불가 (CASECADE로 제약조건을 지우면서 삭제는 가능)
<기억할 내용>
*insert 구문에 *링크가 없음.
*테이블 확인하는 명령문 : DESC 테이블명;
*DDL문은 커밋없어도 됨 , 바로 적용되기 때문.