[Oracle] SQL 기초 - DDL

DDL (data definition language)

사용자 생성/ 권한 부여, 박탈/사용자 삭제

-CREATE USER

-GRANT, REVOKE

테이블 생성/수정/삭제

 

[cmd]

DBA(database administrator) 계정으로 로그인

> sqlplus system/1234

sys계정 : 최상위 계정, 만능 권한!

system 계정 : 관리자 계정 (하나의 관리자)

 

> sqlplus 아이디/패스워드 as sysdba

normal 접속 :

sysoper 접속 : DB open, close에 관하여 open 및 mount, momout 명령을 내릴 수 있는 권한과 DB shitdown, backup, archive에 대한 권한(as sysoper 붙인다)

sysdba 접속 : sysoper에 대한 모든 권한 + create database 권한 (as sysdba 붙인다)

 

현재 접속 사용자 확인하기

SQL> show user; 

USER is "SYSTEM"

* 가끔 놓치면서 실수하는 경우가 있으니 수시로 확인하며 작업하기

 

접속 종료 (DB로의 접속이 종료된 것, DB가 종료된 것이 아니다)

SQL> quit;

 

새로운 사용자 생성하기

CREATE USER [아이디] IDENTIFIED BY [비밀번호];

SQL> CREATE USER scott726 IDENTIFIED BY tiger726;

 

생성된 사용자에게 권한을 부여하기

GRANT [권한들] TO [사용자 아이디];

SQL> GRANT connect, resource, create view, create procedure TO scott726;

connect - DB 접속을 위한 권한

resource - 테이블 (등) 생성 권한

create view - view를 생성할 수 있는 권한

create procedure - procedure를 생성할 수 있는 권한

 

사용자 권한 제거하기

REVOKE [권한들] FROM [사용자 아이디];

SQL> REVOKE CREATE PROCEDURE FROM scott726;

 

사용자 비밀번호 변경

ALTER USER [사용자 아이디] INDENTIFIED BY [변경할 비밀번호];

 

사용자 삭제

DROP USER [사용자 아이디] CASCADE;

* 해당 사용자가 사용하던 모든 database 객체들이 모두 삭제되어 복구가 불가능하니 주의하기

 

타 계정으로 접속 전환

CONN [사용자 아이디]/[비밀번호]

SQL> CONN scott/tiger;

SQL> CONN system/1234;

* command line에서 DB 접속 시 show user 명령어를 통해 틈틈히 '현재 접속 계정' 확인하기!!

 

oracle DBMS 내 사용자 계정 목록

SQL> SELECT username FROM DBA_USERS;

OR

SQL> SELECT username FROM ALL_USERS;

 

 

[내가 작성한 cmd]

C:\Users\hch76>sqlplus system/1234
SQL*Plus: Release 11.2.0.2.0 Production on 화 8월 30 10:07:53 2022

SQL> show user
USER is "SYSTEM"

SQL> ALTER USER scott726 IDENTIFIED BY tiger726;
User altered.

SQL> show user;
USER is "SYSTEM"

SQL> GRANT connect, resource, create view, create procedure TO scott726;
Grant succeeded.

SQL> CONN scott726/tiger726;
Connected.

SQL> show user;
USER is "SCOTT726"

SQL> SELECT tname FROM tab;
no rows selected

SQL> CREATE TABLE phonebook (
  2  pb_uid number PRIMARY KEY,
  3  name VARCHAR2(10) NOT NULL,
  4  phone VARCHAR2(14) DEFAULT '010-0000-0000',
  5  memo CLOB,
  6  regdate DATE
  7  );
Table created.

SQL> SELECT tname FROM tab;

-- DESC : 테이블 조회
SQL> DESC phonebook;

SQL> ALTER TABLE phonebook
  2  MODIFY(
  3  name VARCHAR2(15),
  4  phone VARCHAR2(20)
  5  );
Table altered.

SQL> ALTER TABLE phonebook
  2  ADD (
  3  email VARCHAR2(20),
  4  addr VARCHAR2(100),
  5  age NUMBER DEFAULT 21
  6  );
Table altered.

SQL> DESC phonebook;

SQL> ALTER TABLE phonebook DROP (addr, age);
Table altered.

-- 연쇄적으로 삭제하는 것 테이블 다 날라간다
-- PURGE : 오라클 특수 옵션, 휴지통이 있는데 이거 쓰면 휴지통 거치지 않고 삭제한다
SQL> DROP TABLE phonebook CASCADE CONSTRAINT PURGE;
Table dropped.

SQL> SELECT tname FROM tab;
no rows selected

 

 

 

테이블 생성 : CREATE TABLE 

CREATE TABLE [테이블 이름] (

   [컬럼 이름][컬럼 타입]{제약 조건},

   .....

);

 

테이블 생성 -> field 구조 설계

* column과 column  사이에 콤마(,) 잊지 말기

주요 제약조건(constraint) 

PRIMARY KEY - 고유한 키 값을 갖는 field

NOT NULL - 반드시 입력되어야 하는 field (즉 NULL로 두어서는 안되는 필수 요소)

DEFAULT - 입력이 안되면 기본값으로 지정된 값이 field에 저장된다

 

테이블 구조(schema) 확인 - DESC

SQL> DESC phonebook;

 

테이블 생성 - CREATE TABLE 

1. 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.
2. 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.
3. 한 테이블 내에서는 컬럼명이 중복되게 지정될 수 없다.
4. 테이블 이름을 지정하고 각 컬럼들은 괄호 "( )" 로 묶어 지정한다.
5. 각 컬럼들은 콤마 ","로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ";"로 끝난다.
6. 컬럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.
7. 컬럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
8. 테이블명과 컬럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.
9. 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다.
10. A-Z, a-z, 0-9, _, $, # 문자만 허용된다.
11. 테이블 생성시 대/소문자 구분은 하지 않는다. (기본적으로 테이블이나 컬럼명은 대문자로 만들어진다.)
12. DATE 유형은 별도로 크기를 지정하지 않는다.
13. 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.
14. 컬럼과 컬럼의 구분은 콤마로 하되, 마지막 컬럼은 콤마를 찍지 않는다.
15. 컬럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.

 

oracle에서 사용하는 field data type

문자형	VARCHAR2(n)	가변 길이 문자열 데이터 (최대 4K)
	CHAR(n)	고정 길이 문자열 데이터
	CLOB	대용량 텍스트 (최대 4G)
숫자형	NUMBER NUMBER(p, s)	p : 1 ~ 38 (precision : 유효자리) s : -84 ~ 127 (scale : 소수점 유효 자리)
날짜형	DATE	고정 길이 날짜 (시간 데이터도 포함)
이진데이터	BLOB	대용량 이진데이터(최대 4G)

 

 

테이블 구조 변경 (column 수정) 

ALTER TABLE [테이블 이름]

   MODIFY (

            [컬럼 이름] [컬럼 타입]

            ....

);

 

 

테이블 구조 변경(column 추가)

ALTER TABLE [테이블 이름]

   ADD (

            [컬럼 이름] [컬럼 타입]

            ....

);

 

테이블 구조 변경(column 삭제)

ALTER TABLE [테이블 이름]

   DROP ( [컬럼 이름], ... [컬럼 타입] );

SQL> ALTER TABLE phonebook DROP (addr, age);

 

현재 계정이 소유한 테이블 목록 확인

SQL> SELECT tname FROM tab;
SQL> SELECT table_name FROM all_tables WHERE OWNER = '사용자명(대문자)';

 

table 삭제

DROP TABLE [테이블명] CASCADE CONSTRAINT PURGE;