Study/database

DATABASE 6일차 (insert)

됵이 2022. 8. 18. 17:39

트랜잭션

논리적 작업 단위를 하나의 모음으로 구성 한 것

 

 

DML : 조작 언어 : 트랜잭션의 시작 종료 안에 값이 들어가 있다.

ex) delete(행 단위로 삭제)

delete 에서 whare 를 빼게 되면 테이블 전체 삭제 이므로 whare절은 써주는 것이 좋다.

delete from table

whare

※subqurey 사용 가능

롤백 가능 COMMIT 명령어를 입력해야 트랜잭션이 종료

 

DDL : 정의 언어 : 트랜잭션이 자동으로 시작되고 종료된다.

- 엔터를 치는 순간 트랜잭션이 종료 됨으로 롤백 불가능

ex) truncate (테이블 통째로 삭제)

 

insert into values

  • 새로운 행 추가
    • 키워드 : insert into / values
    • 사용법 : insert into table (컬럼1, 컬럼2) values(컬럼1에 대한 값 , 컬럼2에 대한 값)
  • 특징
    • insert into 후 나열한 컬럼대로 values값을 넣지만 컬럼지정을 하지 않고 테이블만 입력 했을 경우 values값은 table의 원래 컬럼수 대로 값을 넣어줘야 한다.
    • null 값을 넣기 위해서는 values 값에 null이라고 넣어주면 된다.
    • values에는 특수값ex) sysdate , current_date 등 특수값을 넣을 수도 있다.
    • 테이블 명을 넣고 컬럼지정을 하지 않았을 경우 desc 명령어를 통해 컬럼순서를 확인 할 수 있다.
    • to_date 등 문자변환도 가능

 

  • 다른 테이블에서 복사
    • values 대신 select 문을 넣어서 다른 테이블에서 값을 가져올 수도 있다(필자 생각으로 for문과 비슷)

update / set

  • 덮어쓰기
    • 키워드 : update / set
    • whare절은 생략이 가능하지만 생략하게 되면 테이블 전체를 업데이트 하기 때문에 whare절은 써주는 것이 좋다.
    • 사용법 : update table set column = values

※subquery를 사용하여 두개의 열 갱신

set 값에 subquery 사용가능

  •  

savepoint to (트랜잭션 제어)

트랜잭션 제어

  • savepoint를 만들어서 책갈피 처럼 rollback 할 위치 지정
  • 사용법 : inserte 또는 update 후
  • savepint 이름 : 이름은 savepoint 이름
  • rollback to 이름
  • rollback 이나 commit을 하게 되면 트랜잭션 종료 상태가 된다.

for update (잠금기능)

  • for update
  • select 문에서 for update하게 되면 잠그게 되어 다른 사용자가 접근을 하지 못한다.
  • ex) job_id 가 sa_rep라는 것을 수정중이라 한다면
  • where job_id = 'sa_rep'
  • for update
  • 라고 하게 되면 다른 사용자가 select 하게 되더라도 출력 되지 않는다.
    •  화장실 문을 잠그고 볼일을 보는것과 동일하다

 

1. MY_EMPLOYEE라는 테이블을 생성합니다.

CREATE TABLE my_employee
(id NUMBER(4) CONSTRAINT my_employee_id_pk PRIMARY Key,
last_name VARCHAR2(25),
first_name VARCHAR2(25),
userid VARCHAR2(8),
salary NUMBER(9,2));

2. 열 이름을 식별하도록 MY_EMPLOYEE 테이블의 구조를 기술합니다.
desc my_employee
3. 다음 예제 데이터 행을 MY_EMPLOYEE 테이블에 추가하는 INSERT 문을 작성합니다.
id last_name first_name userid salary
1 Patel Ralph rpatel 895
2 Dancs Betty bdancs 860
3 Biri Ben bbiri 1100
4 Newman Chad cnewman 750
5 Repeburn Audrey aropebur 1550

insert into my_employee (id,last_name,first_name,userid,salary) values (1,'Patel','Ralph','rpatel',895);
insert into my_employee(id,last_name,first_name,userid,salary) values (2,'Dancs','Betty','bdancs',860);
insert into my_employee(id,last_name,first_name,userid,salary) values (3,'Biri','Ben','bbiri',1100);
insert into my_employee(id,last_name,first_name,userid,salary) values (4,'Newman','Chad','cnewman',750);
insert into my_employee(id,last_name,first_name,userid,salary) values (5,'Repeburn','Audrey','aropebur',1550);

4. 테이블에 추가한 내용을 확인합니다.
select *
from my_employee;

5. 데이터 추가 내용이 영구적으로 적용되도록 합니다.
commit

6. 사원 3의 성을 Drexler로 변경합니다
update my_employee
set last_name = 'Drexler'
where id like 3;

7. 급여가 $900 미만인 모든 사원에 대해 급여를 $1000로 변경합니다.
update my_employee
set salary = 1000
where salary < 900;

8. 테이블에 대한 변경 사항을 확인합니다.
commit;

9. MY_EMPLOYEE 테이블에서 Betty Dancs를 삭제합니다.
delete from my_employee
where id = 2;

10. 테이블에 대한 변경 사항을 확인합니다.
commit;

11. 보류 중인 모든 변경 사항을 커밋합니다.
commit;

12. 테이블에 추가한 내용을 확인합니다.
commit

13. 트랜잭션 처리의 중간 지점에 표시합니다.
savepoint update_done;

14. 그런 다음 MY_EMPLOYEE 테이블에서 모든 행을 삭제합니다.
delete from my_employee;

15. 테이블이 비어 있는지 확인합니다.
select *
from my_employee;

16. 이전의 INSERT 작업을 삭제하지 않은 채로 최근의 DELETE 작업을 삭제합니다.
rollback to update_done;

17. 새 행이 여전히 원래 상태를 유지하는지 확인합니다.
select *
from my_employee;

18. 데이터 추가 내용이 영구적으로 적용되도록 합니다.
commit;

19. 다음을 추가합니다.
추가할 내용 ==> 6 Anthony Mark manthony 1230

insert into my_employee
values(6,'Anthony','Mark','manthony',1230);

20. 새 행이 올바른 USERID로 추가되었는지 확인합니다
select userid
from my_employee;

 

 

테이블 생성

규칙 

  • 문자로 시작해야한다
  • oracle 예약어는 사용 할 수 없다.

table 생성

create table table_Name
(컬럼1,   컬럼속성,
컬럼2,    컬럼속성)

 

 

 

Default 

값을 입력하지 않으면 자동으로 Default값이 입력 되도록 하는 명령어

아래 그림의 사용 예시는 hire_date 에 값을 입력하지 않으면 sysdate(현재 날짜) 가 입력 된다. 

사용예시

제약조건

테이블 레벨에서 강제적으로 규칙을 생성 하는 것

  • not null : null값이 들어가면 안된다, 즉 무조건 값이 있어야한다. 
  • unique : 오직 하나의 값만 들어가야한다. (값이 중복되면 안된다)
  • primary key : not null 과 unique의 속성을 모두 가지며 기본키라고 한다, 테이블의 데이터를 쉽고 빠르게 찾도록 도와준다. 
  • foreign key : 외래 키 라고 부르며 테이블 끼리 연결 해주는 역할을 한다, 
  • check : 값이 입력될때 조건에 맞는지 확인

문법

NOT NULL

CREATE TABLE Test(    
ID INT NOT NULL,    
Name VARCHAR(30),    
ReserveDate DATE,    
RoomNum INT);

UNIQUE

CREATE TABLE Test (    
ID INT UNIQUE,    
Name VARCHAR(30),    
ReserveDate DATE,    
RoomNum INT);

PRIMARY KEY

CREATE TABLE Test (    
ID INT PRIMARY KEY,    
Name VARCHAR(30),    
ReserveDate DATE,    
RoomNum INT);

FOREIGN KEY 

CREATE TABLE Test2(    
ID INT,    
ParentID INT,    
FOREIGN KEY (ParentID)    
REFERENCES Test1(ID) 
ON UPDATE CASCADE);

CHECK 

CREATE TABLE Customer (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CHECK (Age>=18));

 

ALLTER

  • 새열 추가 
  • 기존 열 수정
  • 새 열에 기본값 정의
  • 열 삭제
  • 열 이름 변경
  • 일기전용 상태로 테이블 변경

새 열 추가 / 기본값 변경 / 열 삭제

alter table dept80
add		(job_id varchar2(9));

마지막에 새 열이 추가 됨

alter table dept80
modify		(last_name varchar2(30));

 

기본값을 변경하면 이후에 테이블에 삽입하는 항목에만 적용된다. 

alter table dept80
drop (job_id);

job_id 삭제됨

테이블 삭제

테이블을 recycle bin 으로 이동

purge 절이 지정되면 테이블 및 해당 데이터를 완전히 제거 

drop table dept80;

 

[문제]
1. 다음 테이블 instance 차트를 기준으로 DEPT 테이블을 생성합니다. 
열이름   ID      NAME
키유형   Primary Key   
Nulls/Unique
FK테이블
FK 열
데이터 유형   number      varchar2
길이   7      25

2. 다음 테이블 instance 차트를 기준으로 EMP 테이블을 생성합니다. 
열이름   ID      LAST_NAME   FIRST_NAME   DEPT_ID   
키유형   
Nulls/Unique
FK테이블                     DEPT
FK 열                     ID
데이터 유형   number      varchar2   varchar2   NUMBER
길이   7      25      25      7   

3. EMP 테이블을 수정합니다. 전체 자릿수가 2이고 소수점 이하 자릿수가 2인 NUMBER 데이터
유형의 COMMISSION 열을 추가합니다. 수정을 확인합니다.

4. 긴 사원 성을 허용하도록 EMP 테이블을 수정합니다. 수정한 내용을 확인합니다.

5. EMP 테이블에서 FIRST_NAME 열을 삭제합니다. 테이블 설명을 검사하여 수정 사항을
확인합니다.

6. EMP 테이블에서 DEPT_ID 열을 UNUSED로 표시합니다. 테이블 설명을 검사하여 수정
사항을 확인합니다.

7. EMP 테이블에서 UNUSED 열을 모두 삭제합니다.

8. EMPLOYEES 테이블의 구조를 기준으로 EMPLOYEES2 테이블을 생성합니다. EMPLOYEE_ID,
FIRST_NAME, LAST_NAME, SALARY 및 DEPARTMENT_ID 열만 포함합니다. 새 테이블에서
열 이름을 각각 ID, FIRST_NAME, LAST_NAME, SALARY 및 DEPT_ID로 지정합니다.
테이블이 생성되었는지 확인합니다.

9. EMPLOYEES2 테이블 상태를 읽기 전용으로 변경합니다.

10. EMPLOYEES2 테이블에서 JOB_ID 열을 추가해 보십시오.
주: “Update operation not allowed on table” 오류 메시지가 나타납니다. 테이블의 상태가
읽기 전용으로 지정되어 있으므로 열을 추가할 수 없습니다.

11. EMPLOYEES2 테이블을 읽기/쓰기 상태로 복귀시킵니다. 이제 동일한 열을 다시 추가해
봅니다.
테이블의 상태가 READ WRITE로 지정되어 있으므로 열을 테이블에 추가할 수 있습니다.

12. EMP, DEPT 및 EMPLOYEES2 테이블을 삭제합니다.
주: READ ONLY 모드인 테이블도 삭제할 수 있습니다. 이를 테스트하려면 테이블을 다시
READ ONLY 상태로 변경한 다음 DROP TABLE 명령을 실행합니다. 테이블이 삭제됩니다.

 

 

[해답]
1. 다음 테이블 instance 차트를 기준으로 DEPT 테이블을 생성합니다. 
열이름   ID      NAME
키유형   Primary Key   
    Nulls/Unique
FK테이블
FK 열
데이터 유형   number      varchar2
길이   7      25

CREATE TABLE dept
(id NUMBER(7)CONSTRAINT department_id_pk PRIMARY KEY,
name VARCHAR2(25));


2. 다음 테이블 instance 차트를 기준으로 EMP 테이블을 생성합니다. 
열이름   ID      LAST_NAME   FIRST_NAME   DEPT_ID   
키유형   
         Nulls/Unique
FK테이블                     DEPT
FK 열                     ID
데이터 유형   number      varchar2                 varchar2                NUMBER
길이   7      25      25      7   

CREATE TABLE emp
(id NUMBER(7),
last_name VARCHAR2(25),
first_name VARCHAR2(25),
dept_id NUMBER(7)
CONSTRAINT emp_dept_id_FK foreign key(dept_id) REFERENCES dept (id)
);

3. EMP 테이블을 수정합니다. 전체 자릿수가 2이고 소수점 이하 자릿수가 2인 NUMBER 데이터 유형의 COMMISSION 열을 추가합니다. 수정을 확인합니다.
ALTER TABLE emp
ADD commission NUMBER(2,2);
DESCRIBE emp

4. 긴 사원 성을 허용하도록 EMP 테이블을 수정합니다. 수정한 내용을 확인합니다.
ALTER TABLE emp
MODIFY (last_name VARCHAR2(50));
DESCRIBE emp

5. EMP 테이블에서 FIRST_NAME 열을 삭제합니다. 테이블 설명을 검사하여 수정 사항을
확인합니다.
ALTER TABLE emp
DROP COLUMN first_name;
DESCRIBE emp

6. EMP 테이블에서 DEPT_ID 열을 UNUSED로 표시합니다. 테이블 설명을 검사하여 수정
사항을 확인합니다.
ALTER TABLE emp
SET UNUSED (dept_id);
DESCRIBE emp

7. EMP 테이블에서 UNUSED 열을 모두 삭제합니다.
ALTER TABLE emp
DROP UNUSED COLUMNS;

8. EMPLOYEES 테이블의 구조를 기준으로 EMPLOYEES2 테이블을 생성합니다. 
EMPLOYEE_ID,FIRST_NAME, LAST_NAME, SALARY 및 DEPARTMENT_ID 열만 포함합니다. 
새 테이블에서 열 이름을 각각 ID, FIRST_NAME, LAST_NAME, SALARY 및 DEPT_ID로 지정합니다. 테이블이 생성되었는지 확인합니다.

create table employees2(id,first_name,last_name,salary,dept_id)
as
select employee_id,first_name,last_name,salary,department_id
from employees;
DESCRIBE employees2

9. EMPLOYEES2 테이블 상태를 읽기 전용으로 변경합니다.
ALTER TABLE employees2 READ ONLY;

10. EMPLOYEES2 테이블에서 JOB_ID 열을 추가해 보십시오.
ALTER TABLE employees2
ADD (job_id VARCHAR2(9));

11. EMPLOYEES2 테이블을 읽기/쓰기 상태로 복귀시킵니다. 이제 동일한 열을 다시 추가해
봅니다. 테이블의 상태가 READ WRITE로 지정되어 있으므로 열을 테이블에 추가할 수 있습니다.
ALTER TABLE employees2 READ WRITE;
ALTER TABLE employees2
ADD job_id VARCHAR2(9);
DESCRIBE employees2

12. EMP, DEPT 및 EMPLOYEES2 테이블을 삭제합니다.
주: READ ONLY 모드인 테이블도 삭제할 수 있습니다. 이를 테스트하려면 테이블을 다시
READ ONLY 상태로 변경한 다음 DROP TABLE 명령을 실행합니다. 테이블이 삭제됩니다.
DROP TABLE emp;
DROP TABLE dept;
DROP TABLE employees2;