[Query] Day 4 - DDL / DML / DCL

윤수인·2023년 12월 19일
0

📒국비학원 [DB]

목록 보기
6/14
post-thumbnail

💻 작업

1. TRANSACTION

TRANSACTION

  • transaction lock : lock 걸면 뒤에 있는 얘한테 웨이팅번호


2. AUTO COMMIT / AUTO ROLLBACK

AUTO COMMIT (자동으로 커밋할때) :

  • DDL => CREATE,ALTER (변경), DROP (지움), RENAM 하고나서 다시 수정할때 AUTO COMMIT돼서 안됨)




DDL (Data Definition Language)

① 데이터베이스를 정의하는 언어
② 데이터를 생성, 수정, 삭제하는 등의 데이터의 전체 골격을 결정하는 역할

  • SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제할 때 사용하는 언어
  • 데이터 베이스 관리자나 데이터베이스 설계자가 사용

    create : 데이터베이스, 테이블등을 생성
    alter : 테이블을 수정
    drop : 데이터베이스, 테이블을 삭제

  • DCL => GRANT,REVOKE : 권핫뺏고 ROLLBACK 불가능 -> GRANT로 다시 줘야함


DCL (Data Control Language)

① 데이터베이스에 접근하거나 객체에 권한을 주는등의 역할을 하는 언어

grant : 특정 데이터베이스 사용자에게 특정 작업에 대한 수행 권한을 부여
revoke : 특정 데이터베이스 사용자에게 특정 작업에 대한 수행 권한을 박탈, 회수
commit : 트랜잭션의 작업을 저장
rollback : 트랜잭션의 작업을 취소, 원래대로 복구

- ROLL 객체 - ROLE 종류

  • CONNECT : DB 접속 권한
  • RESOURCE : 테이블이라든지 인덱스라든지 생성할 수 있는 권한
  • CREATE VIEW : 뷰 생성 권한
  • DBA : 모든 권한(관리자)

DML(Data Manipulation Language)
데이터 조작어란? 정의된 데이터베이스에 입력된 레코드를 조회하거나 수정하거나 삭제하는 등의 역할을 하는 언어.

select : 데이터 조회
insert : 데이터 삽입
update : 데이터 수정
delete : 데이터 삭제
※데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는데 사용하는 언어
※데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공

  • EXIT (모든 DB에 저장하고 나옴)

AUTO ROLLBACK (자동으로 rollback할때) :

  • 비정상적인 종료 (POWER OFF)


3. DDL / TABLE / CHAR / VARCHAR / NUMBER / DATE

-- DDL (CREATE,ALTER(변경),DROP(지움),RENAME(잘안씀))
-- OBJECT ( = TABLE, INDEX, SYNONYM, SEQUENCE, VIEW )

--TABLE : 영문자 + 숫자 (무조건 시작값 영문자 / TABLE과 COLUMN명이 일치하면 안됨)
--CHAR : 문자 (고정길이) - 읽는 속도 빠름
--VARCHAR (Valueable Charcter) : 문자 (가변길이) - 읽는 속도 느림 / 단편화작업은 관리자에게 맡김
--NUMBER (P,S) : 원하는 자릿값 - 숫자 EX, NUMBER(2) : 두자리
--DATE : 날짜와 시간 저장 (고정길이 - 7BYTE - 자료형 없음 - 반드시 날짜타입 YY-MM-DD)
--LONG : 문자(가변길이 - 2GB 이상), 잘안씀


CREATE TABLE BUSEO1 (DNO NUMBER(2), DANME VARCHAR2(14), ZIPCODE CHAR(7));

DESC BUSEO1;

SELECT * FROM BUSEO1; -- 내용은 없고 틀만 있음 

INSERT INTO BUSEO1 VALUES (10,'AAA','123');


4. 데이터 타입 정하기

  • NUMBER / CHAR / VARCHAR / DATE

  • NUMBER 뒤에 자리수 안정하면 맘대로 간으
  • PRIMARY KEY 자리에 나중에 이름 만들어줄거임
  • PRIMARY KEY : 모든 테이블에 반드시 만들어야하는 KEY값 - 중복값 X / NULL값 허용 X / 테이블당 1개만 지정가능
CREATE TABLE CUSTOM 
(NO NUMBER PRIMARY KEY,
NAME CHAR(10), 
AGE NUMBER(3),
JUMIN CHAR(16), 
ADDR VARCHAR(100), 
BIRTH DATE);

DESC CUSTOM;

INSERT INTO CUSTOM VALUES (10, 'SUZI', 28, '941010-2123456', '서울', '94-10-10');

SELECT * FROM CUSTOM;

INSERT INTO CUSTOM VALUES (20, 'INNA', 42, '801010-2123456', '부산', '80-10-10'); -- TRANSACTION이 되어있는 중 

COMMIT;

SELECT * FROM USER_CONSTRAINTS; -- SYS관리자모드


5. 이미 있는 테이블에 새로운거 넣을때

  • CREATE TABLE

  • 이미 있는 테이블에는 INSERT INTO 쓰느라 AS안써도되는데, 새로운거 만들땐 AS써야함
CREATE TABLE SALESMAN1
AS 
SELECT PNO,PNAME,JOB,PAY FROM PERSONNEL
WHERE JOB='SALESMAN';

SELECT * FROM SALESMAN1;


CREATE TABLE MANAGER1
AS
SELECT * FROM PERSONNEL WHERE JOB = 'MANAGER';

SELECT * FROM MANAGER1;


6. 새로운 테이블을 만들고 복사하는 법

  • CREATE TABLE로 만들고 복사하기

--PAY가 3000이상인 사람들

CREATE TABLE PAY3000
AS
SELECT * FROM PERSONNEL WHERE PAY >= 3000;

SELECT * FROM PAY3000;


-- 12월달에 입사한 직원들을 MON12라는 테이블로 복사

CREATE TABLE MON12
AS
SELECT * FROM PERSONNEL WHERE TO_CHAR(STARTDATE,'MM') = 12; 

SELECT * FROM MON12;


-- 부서번호가 10인 (부서별) 부서의 총 급여 정보를 DIV10테이블로 저장

CREATE TABLE DIV10
AS
SELECT DNO, SUM(PAY) S_PAY FROM PERSONNEL
GROUP BY DNO
HAVING DNO = 10;

SELECT * FROM DIV10;


7. 컬럼변경

  • CREATE TABLE (여기에 써주기) 로 만들고 복사하기

CREATE TABLE ANALYST(NUM,NAME,JOB,DAME) 
AS
SELECT PNO,PNAME,JOB,DNO FROM PERSONNEL
WHERE JOB = 'ANALYST';

SELECT * FROM ANALYST;


8. 틀만 복사하기

  • WHERE 0=1; 마지막 조건에 이런식으로 쓰기

  • 조건) 거짓으로 주면 틀만 가지고 올 수 있음 (제약조건은 복사되지 않음 - 프라이머리키)
CREATE TABLE SAWON1
AS
SELECT * FROM PERSONNEL
WHERE 0=1; 

SELECT * FROM SAWON1;
DESC SAWON1; 
DESC PERSONNEL;


9. TABLE 수정

  • TABLE 수정

  • TABLE은 오브젝트인데 오브젝트르 수정할땐 ALTER
  • ALTER (ADD, MODIFY, DROP) - 별도로 작업
SELECT * FROM SALESMAN1;

ALTER TABLE SALESMAN1
ADD (DNO NUMBER(2)); -- 데이터타입추가

DESC SALESMAN1;

INSERT INTO SALESMAN1 VALUES(1111,'SUZI','SALESMAN',3000,10);

SELECT * FROM SALESMAN1;
  • 두개 이상의 컬럼 (반드시 괄호로 묶어야함) , 변경 추가

ALTER TABLE SALESMAN1 ADD (JUMIN CHAR(14), ZIP CHAR(7));

SELECT * FROM SALESMAN1;

10. 제약조건 3가지

  • PRIMARY KEY / DEFAULT 값 / F.K

ALTER TABLE SALESMAN1 ADD BIGO VARCHAR2(10) DEFAULT('AA'); -- 값을 넣지 않으면 DEFAULT 기본값 : 제약조건 

SELECT * FROM SALESMAN1;


INSERT INTO SALESMAN1 (PNO,BIGO) VALUES (1122, 'BB'); --아니면 내가 입력한 값이 실행됨

SELECT * FROM SALESMAN1;


INSERT INTO SALESMAN1 (PNO) VALUES (1133);--값을 넣지 않으면 DEFAULT 기본값

  • CHECK 제약조건

ALTER TABLE SALESMAN1 ADD (MARRIAGE CHAR(8) CONSTRAINT SALESMAN1_MARRIAGE_CK
CHECK (MARRIAGE IN ('SINGLE', 'MARRIED')));

SELECT * FROM USER_CONSTRAINTS;

INSERT INTO SALESMAN1 VALUES (1144,'INNA','SALESMAN',2000,10 ,'111-111','12345','CC','SINGLE');
SELECT * FROM SALESMAN1;
  • 제약조건(PK) 추가하기 [TABLE명 _어디컬럼?_PK PRIMARY KEY(컬럼명)]
  • OIN문 쓸 때 DISTINCT는 중복된 행을 제거하는 역할
SELECT DISTINCT e1.EMPLOYEE_ID, e1.EMPLOYEE_NAME, e2.EMPLOYEE_NAME AS MANAGER_NAME
FROM EMPLOYEE e1
JOIN EMPLOYEE e2 ON e1.MANAGER_ID = e2.EMPLOYEE_ID;

DESC SALESMAN1; 

ALTER TABLE SALESMAN1 ADD CONSTRAINT SALESMAN1_PNO_PK PRIMARY KEY(PNO); -- 1122값 두번들어가있어서 PK법에 위배됨 -> SELFJOIN의 중복값 DISTINCT 쓰기 

DELETE FROM SALESMAN1 WHERE PNO = 1122; -- DELETE는 행을 삭제하는 데 사용되며, DROP은 테이블 자체를 삭제하는 데 사용

COMMIT;

SELECT * FROM SALESMAN1;

DESC SALESMAN1;

SELECT * FROM SALESMAN1;

INSERT INTO SALESMAN1 VALUES (1144,'INNA','SALESMAN',2000,10 ,'111-111','12345','CC','SINGLE');

11. 컬럼 - ADD추가 / MODIFY수정 / DROP제약조건삭제

  • 컬럼 데이터타입 추가 (ADD)
SELECT * FROM MANAGER1;
DESC MANAGER1;

ALTER TABLE MANAGER1
ADD COMM NUMBER(5);


-- PNO에 PK만들어주기
ALTER TABLE MANAGER1 
ADD CONSTRAINT MANAGER1_PNO_PK PRIMARY KEY(PNO);

DESC MANAGER1; -- PK (NOT NULL)이 들어감

SELECT * FROM USER_CONSTRAINTS; -- 잘 만들어졌는지 확인
  • 컬럼 데이터타입 수정 (MODIFY)
SELECT * FROM MANAGER1;
DESC MANAGER1;

ALTER TABLE MANAGER1
MODIFY PNAME VARCHAR2(16);

ALTER TABLE MANAGER1
MODIFY PNAME VARCHAR2(7); --에러? =>수정 OK : 이미 데이터가 들어가있는데, 데이터 최대자리수보다 작을 순 없음
  • 컬럼 / 제약조건 삭제 (DROP)
SELECT * FROM USER_CONSTRAINTS;

--프라이머리키 삭제
ALTER TABLE MANAGER1
DROP CONSTRAINT MANAGER1_PNO_PK;
-- (=같은 명령어)
ALTER TABLE MANAGER1
DROP PRIMARY KEY ; 

ALTER TABLE DIVISION
DROP PRIMARY KEY CASCADE; -- D.PK가 PER.FK와 엮여있으므로 - CASCADE : 강제 

12. DROP 삭제

  • 컬럼삭제 (DROP)
SELECT * FROM SALESMAN1;
DESC SALESMAN1;

ALTER TABLE SALESMAN1
DROP COLUMN MARRIAGE;

SELECT * FROM SALESMAN1;

--지워진게 아니라 아직도 DNO는 존재함 (잠시 숨겨줌)
ALTER TABLE SALESMAN1
SET UNUSED COLUMN DNO;

--비활성화한 컬럼들 다 지우는 명령어
ALTER TABLE SALESMAN1
DROP UNUSED COLUMNS;
  • 테이블...등 삭제 (DROP)
SELECT * FROM MANAGER1;
SELECT * FROM TAB;

DROP TABLE MANAGER1; --테이블 삭제

SELECT * FROM RECYCLEBIN; -- 휴지통 검색

-- 휴지통 복구하기

SELECT * FROM "BIN$2hV9QS2rQVGqNUG9ARwkOQ==$0";
- 지워졌을 때의 객체 이름을 쓰면 안에 내용이 보임

FLASHBACK TABLE MANAGER1 TO BEFORE DROP; 
FLASHBACK TABLE "BIN$2hV9QS2rQVGqNUG9ARwkOQ==$0" TO BEFORE DROP;

DROP TABLE SALESMAN1;

--휴지통내용 전부 삭제
PURGE RECYCLEBIN; 

SELECT * FROM TAB; -- 확인해보니 삭제됨

--휴지통을 안거치고 바로 지워지는 것
DROP TABLE ANALYST PURGE;

13. DELETE / TRUNCATE

  • DELETE 행 (데이터 - record) 지우는것 / TRUNCATE
SELECT * FROM PERSONNEL;

DELETE PERSONNEL; -- TRANSACTION이 AUTO 실행됨

SELECT * FROM PERSONNEL;

ROLLBACK; -- COMMIT하지 않았다면 ROLLBACK해서 살아남


-- TRUNCATE :데이터 (record) 지우는 것 

TRUNCATE TABLE PERSONNEL;

ROLLBACK;--완전 끝남 / 복구 못함

14. RENAME

  • RENAME
SELECT * FROM TAB;

SELECT * FROM PAY3000;

RENAME PAY3000 TO HIGHPAY; -- 컬럼이름 변경

15. DCL (GRANT / REVOKE)

  • GRANT : USER나 OBJECT에 권한을 부여
  • REVOKE : USER나 OBJECT에 권한을 회수
SELECT * FROM TAB;

CREATE USER suzi 
IDENTIFIED BY a123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

DROP USER suzi;

--OBJECT 권한 (사용자가 사용자한테 권한부여)
GRANT SELECT ON PERSONNEL TO inna;
GRANT SELECT,INSERT,DELETE ON DIVISION TO inna;

SELECT * FROM USER_TAB_PRIVS_MADE;

REVOKE SELECT ON PERSONNEL FROM inna; --인나한테 다 뺏김

REVOKE SELECT,INSERT,DELETE ON DIVISION FROM INNA; -- KIM 권한없음 확인 -> 다 INNA한테 감

16. 권한의 종류 (SYSTEM / OBJECT)

-- SYSTEM권한 : DATABASE OBJECT를 생성 (CREATE), 수정(ALTER), 삭제(DROP) 할 수 있는 권한
-- DBA -> USERS (CREATE USER,,,)

-- OBJECT( TABLE, INDEX, SYNONYM, SEQUENCE, VIEW)
-- OBJECT 권한 : OBJECT 내용을 추가(INSERT),변경(UPDATE), 삭제(DELETE) ,검색(SELECT) 할 수 있는 권한
-- USER -> USERS

SELECT * FROM SYSTEM_PRIVILEGE_MAP; 

--DROP USER : 계정삭제권한


  • 잠시 숨겨둔걸 확인시켜줌
SELECT * FROM DBA_UNUSED_COL_TABS;

17. 계정만들기

CREATE USER inna 
IDENTIFIED BY a123 --패스워드
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

CREATE USER edam 
IDENTIFIED BY a123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

SELECT * FROM DBA_USERS; -- dictionary 백과사전같은 느낌 (계정찾기)

-- dba dictionary : system / 관리자만 권한있음
--all dictionary  : 권한을 부여하면 다른 user꺼 정보까지 볼 수 있음
-- user DICTIONARY : 자기자신 정보만 볼 수 있음

SELECT * FROM V$SESSION -- 동적 dictionary : (서버접속) 현재 서버에 접속한 사용자들이 누구인지?
WHERE USERNAME IS NOT NULL; --NULL이 아닌 데이터


--권한 하나씩 부여


GRANT CREATE USER TO KIM;-- 권한을 반드시 받아야만 CREATE USER 로써 계정이 만드는 거다

GRANT DROP USER TO KIM; -- CONNECT : CREATE SESSION권한 / --DROP USER : 계정삭제권한

REVOKE CREATE USER, DROP USER FROM KIM; -- RESOURCE : CREATE TABLE권한

GRANT CREATE USER TO KIM;-- 권한을 반드시 받아야만 CREATE USER 로써 계정이 만드는 거다

GRANT CREATE SESSION TO INNA; -- CONNECT : CREATE SESSION권한 / --DROP USER : 계정삭제권한

GRANT CREATE TABLE TO INNA; -- RESOURCE : CREATE TABLE권한

GRANT UNLIMITED TABLESPACE TO INNA;

--합쳐서
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO INNA;
profile
어제보다 조금 더 성장하기!

0개의 댓글