오라클 데이터베이스 Oracle Database SQL Reference

샘플 스키마의 개념 알아보기 -> 테이블 9개가 있으면 데이터의 용도 확인 가장 먼저 한다.

관계형 데이터베이스 =  RDB

 

SQL - 집합적 언어, 데이터베이스를 다루는 데 사용하는 표준 언어 

언어 - 데이터를 다루는 언어(문법, 키워드 -> 명령) 

 

테이블 - 데이터 넣고 수정하고 삭제하는 , 데이터를 담고 있는 그릇으로 처리 저장 구조 기본 단위 

테이블 만들면서 로우(행)과 컬럼(열) 생성, 2차원 형태 (행렬)

 

단일값 - scalar

여러개값 - 1차원, vector -배열 

행열 - matrix -ML/DL -> numpy(python), library (tensor를 다루는 고속) 

다차원 - tensor (1차원 2차원... ) 

시퀀스 - 오라클에만 존재 , 일련번호 채번을 할때 사용하는 객체 (순서를 만들다) 

 

 코딩시 관습적으로 명령어는 대문자, 이름은 소문자

 

DDL : Data Definition Language/Types of SQL Statements

데이터를 정의, table 구간정의하는 언어이다.

테이블을 생성하거나 삭제 등 데이터베이스 스키마(Scheme)를 정의 또는 관리하는 언어이다.

 

• CREATE : 정의, 구조 (테이블이나 인덱스 뷰등) 객체 생성
• ALTER : 수정, 구조 수정 변경
• DROP : 삭제,  구조 영구히 삭제
• TRUNCATE : DROP 후, CREATE , 테이블 통쨰로 삭제 

Create, alter, and drop schema objects Grant and revoke privileges and roles Analyze information on a table, index, or cluster Establish auditing options Add comments to the data dictionary

DML : Data Manipulation Language /Types of SQL Statements

테이블 등의 데이터를 조작(추가/조회/수정/삭제) 하는 언어이다.

응용 프로그램과 데이터베이스 사이에서 실질적인 데이터 처리를 위해 사용된다.

transaction 연결 commit /rollback 

• INSERT : 추가
• SELECT : 조회
• UPDATE : 수정
• DELETE : 삭제

Data manipulation language (DML) statements access and manipulate data in existing schema objects. 

These statements do not implicitly commit the current transaction. 

 

---

** 메모

테이블 생성 => create table 테이블명( 컬럼, 컬럼, ...)

CREATE TABLE ex2_2(
      col1 VARCHAR2(3)
    , col2 VARCHAR2(3 byte)
    , col3 VARCHAR2(3 char)
);
DESC ex2_2;

ex2_2 테이블에 값을 추가

INSERT INTO ex2_2 -- (col1,co12, col3) 생략 가능, 전체컬럼 3개고 값도 3개
VALUES('aaa','bbb','ccc');

검색 => SELECT

SELECT col1,col2,col3
 FROM ex2_2 -- 1번 실행
;

SELECT * -- 컬럼자리, 모든 (*)컬럼 =>col1,col2,col3
 FROM ex2_2 -- 1번 실행
 ;

SELECT LENGTH(col1) AS len1
         , LENGTH(col2) AS len2
         , LENGTH(col3) AS len3
 FROM ex2_2
 ;

SELECT LENGTH(col1)  len1
         , LENGTH(col2)  len2
         , LENGTH(col3)  len3
 FROM ex2_2
;

한글 추가 => '홍길동'
col1 (3)<= '홍길동' => 6 byte필요
col2 (3 byte)<= '홍길동' => 6 byte필요
col3 (3 char)<= '홍길동'
col1 먼저 '홍길동' 추가

INSERT INTO ex2_2(col1)
VALUES('홍길동'); -- ERROR다 (3*3=9 byte필요)

col3 (3 char) <= '홍길동'

INSERT INTO ex2_2(col3)
VALUES('홍길동');

SELECT *
  FROM ex2_2
;

데이터타입 => 숫자

col_int	INTEGER
col_dec	DECIMAL
col_num	NUMBER

생성된 테이블의 구조 조회(select)
테이블의 구조에 대한 정보를 저장하고 있는 테이블이 존재 : user_tab_cols table
user_table_cols : 일반 유저가 생성한 테이블의 컬럼들 정보를 가지고 있는 테이블
user_table_cols는 우리가 생성한 테이블이 아니다 => system 생성한 테이블이 된다

SELECT *  -- 모든 컬럼
	FROM user_tab_cols -- 테이블(뷰)
 WHERE table_name = 'EX2_3' --조건, = : 비교연산자
;

SELECT table_name, column_name,data_type
  FROM user_tab_cols
  WHERE table_name = 'EX2_3'
  ORDER BY table_name -- 정렬, default ASC (오름차순)
;

테이블 생성  => 날짜, 시간을 저장하는 테이블 생성

CREATE TABLE ex2_5(
    col_date 			DATE
  , col_timestemp timestamp
);

값을 입력(삽입) = insert

INSERT INTO ex2_5(col_date,col_timestemp)
VALUES(sysdate,systimestamp)

sysdate,systimestamp => function => ()
function name(parameter value)

-=>(parameter value) => parameter value X 없는 경우 =>() 생략

sysdate(),systimestamp()
sysdate => 현재 날짜 시간 값을 생성 함수
systimestamp =>현재 날짜 시간, 밀리초 값을 생성 함수

ex2_5 table값을 조회 =>select

SELECT *
 FROM ex2_5
;

 

---

DCL : Data Control Language 

데이터를 제어하는 언어이다.

사용자 권한, 작업의 취소 등 DML과 비교하여 상위 개념의 언어

db 제어, user생성, 권한부여 특정권한을 주는 grant와 회수하는 revoke

 

• COMMIT : 작업 결과 반영
• ROLLBACK : 트랜잭션의 작업을 취소 / 복구(undo)
• GRANT : 사용자에게 권한 부여
• REVOKE : 권한 취소
  
  

---

테이블 생성

CREATE TABLE 테이블명 (
컬럼명1 데이터타입 DEFAULT형식,
컬럼명2 데이터타입 DEFAULT형식,
컬럼명3 데이터타입 DEFAULT형식
);

 

테이블의 컬럼 추가

• ALTER TABLE 테이블명 ADD(컬럼명 데이터타입(사이즈));

테이블의 컬럼 수정

• ALTER TABLE 테이블명 MODIFY(컬럼명 테이타타입(사이즈));

테이블의 컬럼 삭제

• ALTER TABLE 테이블명 DROP COLUMN 컬럼명

테이블의 컬럼 이름 변경

• ALTER TABLE 테이블명 RENAME 기존 컬럼명 TO 바꿀 새로운 컬럼명
  
  

  -- 1. 컬럼명 변경  col1 => col11
  ALTER TABLE ex2_10 RENAME COLUMN col1 TO col11;
  
  
  -- 2. 컬럼 타입(길이)  변경
  -- col2 varchar2(10) => varchar2(30)
  ALTER TABLE ex2_10 MODIFY col2 VARCHAR2(30);
  DESC ex2_10;
  
  -- 3. 컬럼 추가 : col3 number 추가
  ALTER TABLE ex2_10 ADD col3 NUMBER ;
  DESC ex2_10;
  
  -- 4. 컬럼 삭제 : col3 삭제
  ALTER TABLE ex2_10 DROP COLUMN col3;
  DESC ex2_10;
  
  -- 5. 제약조건 추가 : col1 => 기본키 지정
  ALTER TABLE ex2_10 -- 수정할 테이블 선택
  ADD CONSTRAINTS ex2_10_col11_pk --action => add constraints
  PRIMARY KEY(col11);
  DESC ex2_10;
  
  SELECT
   FROM user_constraints -- 일반 유저가 생성한 제약조건 정보가 저장한 테이블
  WHERE table_name = 'EX2_10'
  ;
  
  -- 6. col1 PK 삭제
  -- ex2_10_col11_pk 삭제
  
  ALTER TABLE ex2_10
  DROP CONSTRAINTS EX2_10_COL11_pk;
  
  SELECT *
  FROM user_constraints
  WHERE table_name = 'EX2_10'
  ;

---

데이터형 : Oracle Data Types 

CHAR(크기)	고정길이 문자 데이터
VARCHAR2(크기)	가변길이 문자 데이터 (최대 4,000Byte)
NUMBER	숫자 데이터 (최대 40자리)
NUMBER(길이)	숫자 데이터로, 길이 지정 가능 (최대 38자리)
DATE	날짜 데이터 (BC 4712년 1월 1일 ~ AD 4712년 12월 31일)
LONG	가변 길이 문자형 데이터 (최대 2GB)
LOB	2GB까지의 가변길이 바이너리 데이터 저장 가능 (이미지, 실행파일 등 저장 가능)
ROWID	DB에 저장되지 않는 행을 식별할 수 있는 고유 값
BFILE	대용량의 바이너리 데이터 저장가능 (최대 4GB)
TIMESTAMP	DATE형의 확장된 형태
INTERVAL YEAR TO MONTH	년과 월을 이용하여 기간 저장
INTERVAL DAY TO SEOCND	일, 시, 분, 초를 이용하여 기간 저장

 

CREATE TABLE ex2_1 (

      column1  CHAR(10) -- 10자 들어가는 문자공간 생성

    , column2 VARCHAR2(10) -- 10자 들어가는 문자공간 생성

    , column3 NVARCHAR2(10) -- 유니코드 10자 들어가는 문자공간 생성

    , column4 NUMBER -- 숫자 저장 공간 생성

);

 

-- column1 : 컬럼 이름  CHAR : 문자저장 공간 (10) : 10자, 길이 

---

Data Dictionary 명령어

데이터 베이스를 운영하기 위한 정보들을 모두 특정한 테이블에 모아두고 관리 하는데 그 테이블을 Data dictionary 라고 한다. (사용자에 대한 정보, 메모리 구조, 제약조건 정보 등을 담고 있음) 

USER_TABLES	사용자가 작성한 테이블을 확인하는 뷰
USER_TAB_COLUMNS	테이블, 뷰의 컬럼들과 관련된 정보 조회
USER_CONSTRAINTS	테이블에 적용되어 있는 제약조건 조회
DESC (TABLE NAME)	테이블 구조 표시

 

---

제약조건(CONSTRAINTS) :  Oracle Database SQL Reference

• 테이블 작성 시 각 컬럼에 기록될 데이터에 대해 제약조건을 설정 할 수 있는데 이는 데이터 무결성 보장을 주 목적
  데이터 무결성이란 ? 데이터의 정확성, 일관성, 유효성이 유지되는것
• 입력 데이터에 문제가 없는지에 대한 검사와 데이터 수정/삭제 가능 여부 검사 등을 위해 사용
• 컬럼이 여러개일때 제약조건이 여러개일 수 있다.
• 제약조건은 테이블을 처음 만들 때 지정해도 되고 나중에 테이블을 만들고 지정해도 된다.

NOT NULL	데이터에 NULL을 허용하지 않음 A NOT NULL constraint prohibits a database value from being null.
UNIQUE	중복된 값을 허용하지 않음 A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
PRIMARY KEY	NULL과 중복 값을 허용하지 않음(컬럼의 고유 식별자로 사용하기 위해) A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
FOREIGN KEY	참조되는 테이블의 컬럼의 값이 존재하면 허용 A foreign key constraint requires values in one table to match values in another table.
CHECK	저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만 허용 A check constraint requires a value in the database to comply with a specified condition.

oracle CONSTRAINTS

 

---

** 메모

제약조건 지정방법
-- 1. 테이블 생성시  컬럼 선언하면서 제약조건 지정 => 가장 간단하다
-- 2. 테이블 생성시 컬럼 선언후 제약조건 지정 => 조금 복잡
-- 3. 테이블 생성후 제약조건 지정 => 테이블 객체, 제약조건 객체 => 각각 별도 존재 가능

NOT NULL
-- 해당 컬럼에 null 허용하지 않겠다 => 반드시 입력해라 (필수입력)
-- 입력(수정) => insert, update(수정)

CREATE TABLE ex2_6(
    col_null 			VARCHAR2(10) NULL
  , col_not_null 	VARCHAR2(10) NOT NULL
);

 insert

INSERT INTO ex2_6(col_null, col_not_null)
VALUES('AA','');

INSERT INTO ex2_6(col_null, col_not_null)
VALUES('','BB');

ex2_6 테이블에 걸려 있는 제약조건을 조회 (select)

SELECT constraint_name
	, constraint_type
	, table_name
  from user_constraints
  WHERE table_name = 'EX2_6'
;

SELECT *
  from user_constraints uc -- 테이블에 alias(별칭) 부여, as쓰면 에러
  WHERE table_name = 'EX2_6'
;

SELECT a.COL_NULL
	, a.COL_NOT_NULL
  from EX2_6 a
  WHERE table_name = 'EX2_6'
;

unique : 중복 방지  => 유일해진다

CREATE TABLE ex2_7(
		col_unique_null 	VARCHAR2(10)
	, col_unique_nnull 	VARCHAR2(10) UNIQUE NOT NULL
	, col_unique			 	VARCHAR2(10)
	-- 컬럼 선언 완료
	, CONSTRAINTS ex2_7_col_unique_null_uk
	  UNIQUE(col_unique_null)
	, CONSTRAINTS ex2_7_col_unique_nnull_uk
	  UNIQUE(col_unique_nnull)
	, CONSTRAINTS ex2_7_col_unique_uk
	  UNIQUE(col_unique)
);

SELECT *
 FROM user_constraints
 WHERE table_name = 'EX2_7'
;

 

---

제약조건(CONSTRAINTS) :  Oracle Database SQL Reference 

1) NOT NULL

NOT NULL constraints must be declared inline. 

All other constraints can be declared either inline or out of line.

 

• 해당 컬럼에 반드시 값이 기록되어야 하는 경우 사용
• 특정 컬럼에 값을 저장/수정할 때는 NULL값을 허용하지 않음.
• 컬럼 레벨에서만 제한 가능
• NOT NULL 제약조건이 설정된 컬럼에 NULL값이 입력되면, 행 자체를 삽입하지 않음
  

A NOT NULL constraint prohibits a column from containing nulls. 

The NULL keyword by itself does not actually define an integrity constraint,

 but you can specify it to explicitly permit a column to contain nulls. 

 

You must define NOT NULL and NULL using inline specification.

 

If you specify neither NOT NULL nor NULL, 

then the default is NULL. 

 

NOT NULL constraints are the only constraints you can specify inline on XMLType and VARRAY columns.

2) UNIQUE

• 컬럼 입력 값에 대해 중복을 제한하는 제약조건
• 컬럼 레벨과 테이블 레벨에 설정 가능
  • 왠만하면 테이블 레벨에 설정하는것이 좋다.

A unique constraint designates a column as a unique key. 

A composite unique key designates a combination of columns as the unique key. 

 

When you define a unique constraint inline, you need only the UNIQUE keyword.

When you define a unique constraint out of line, you must also specify one or more columns. 

You must define a composite unique key out of line.

3) PRIMARY KEY

• 테이블에서 한 행의 정보를 구분하기 위한 고유 식별자 역할 NOT NULL의 의미와 UNIQUE의 의미를 둘 다 가지고 있으며 한 테이블 당 하나만 설정 가능 컬럼 레벨과 테이블 레벨 둘 다 지정 가능
• 여러컬럼을 묶어서 PRIMARY KEY로 만들수도 있다.

A primary key constraint designates a column as the primary key of a table or view.

 

A composite primary key designates a combination of columns as the primary key.

When you define a primary key constraint inline, you need only the PRIMARY KEY keywords.

 

When you define a primary key constraint out of line, you must also specify one or more columns.

You must define a composite primary key out of line.

4) FOREIGN KEY

• 참조 무결성을 위한 제약조건으로 참조된 다른 테이블이 제공한 값만 사용하도록 제한을 거는 것 참조되는 컬럼과 참조된 컬럼을 통해 테이블 간에 관계가 형성되는데 참조되는 값은 제공되는 값 외에 NULL을 사용 가능하며 참조할 테이블의 참조할 컬럼 명을 생략할 경우 PRIMARY KEY로 설정된 컬럼이 자동으로 참조할 컬럼이 됨

A foreign key constraint (also called a referential integrity constraint) designates a column as the foreign key and establishes a relationship between that foreign key and a specified primary or unique key, called the referenced key. 

 

A composite foreign key designates a combination of columns as the foreign key.

5) CHECK

• 해당 컬럼에 입력 되거나 수정되는 값을 체크하여 설정된 값 이외의 값이면 에러 발생 비교 연산자를 이용하여 조건을 설정하며 비교 값을 리터럴만 사용 가능하고 변하는 값이나 함수 사용은 불가능

A check constraint lets you specify a condition that each row in the table must satisfy. 

 

To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null). When Oracle evaluates a check constraint condition for a particular row, any column names in the condition refer to the column values in that row.

 

CREATE TABLE emp(
     empno      CHAR(8)-- 사원번호(입사연도 4자리 +일련번호 4자리) => 숫자 X, 문자열 O
   , empnm    VARCHAR2(50) -- 사원명
   , deptno    NUMBER -- 부서번호 => FK
   , CONSTRAINTS emp_empno_pk
     PRIMARY KEY(empno) -- empno column PK 선언
   , CONSTRAINTS emp_deptno_fk
     FOREIGN KEY(deptno) -- 참조할 컬럼 지정
     REFERENCES dept(deptno) -- 참조당할 테이블과 컬럼 지정
);

-- 부서 테이블(부서번호, 부서명 컬럼)
CREATE TABLE dept( -- 부모테이블 : 참조당하는 테이블
     deptno   NUMBER -- 부서번호(10 ~ 90)
   , deptnm   VARCHAR2(10) -- 부서명, 길이 => 한글 5자이내
   , CONSTRAINTS dept_deptno_pk
     PRIMARY KEY(deptno) -- deptno => unique + NN(필수입력)
);

-- pk 선언
-- 2번 방식
-- 컬럼 선언후 제약조건 지정하는 방식
CREATE TABLE ex2_8(
     col1   VARCHAR2(10)
   , col2    VARCHAR2(10)
   , CONSTRAINTS ex2_8_col1_pk
     PRIMARY KEY(col1)
);

SELECT constraint_name
     , constraint_type
     , table_name
  FROM user_constraints
 WHERE table_name = 'EX2_8'
  -- 일반 유저가 생성한 제약조건 정보를 저장 테이블다
;
INSERT INTO ex2_8(col1, col2)
VALUES('','AA'); -- error

INSERT INTO ex2_8(col1,col2)
VALUES('AA','AA');

SELECT * -- col1, col2
  FROM EX2_8
;
INSERT INTO ex2_8(col1,col2)
VALUES('AA','BB'); -- Error

-- PK = UK + NN

-- 테이블 2개 생성
-- 사원 테이블, 부서 테이블
-- 사원 테이블 (사원번호, 사원명, 부서번호 컬럼)
CREATE TABLE emp(
     empno      CHAR(8)-- 사원번호(입사연도 4자리 +일련번호 4자리) => 숫자 X, 문자열 O
   , empnm    VARCHAR2(50) -- 사원명
   , deptno    NUMBER -- 부서번호 => FK
   , CONSTRAINTS emp_empno_pk
     PRIMARY KEY(empno) -- empno column PK 선언
   , CONSTRAINTS emp_deptno_fk
     FOREIGN KEY(deptno) -- 참조할 컬럼 지정
     REFERENCES dept(deptno) -- 참조당할 테이블과 컬럼 지정
);

-- 부서 테이블(부서번호, 부서명 컬럼)
CREATE TABLE dept( -- 부모테이블 : 참조당하는 테이블
     deptno   NUMBER -- 부서번호(10 ~ 90)
   , deptnm   VARCHAR2(10) -- 부서명, 길이 => 한글 5자이내
   , CONSTRAINTS dept_deptno_pk
     PRIMARY KEY(deptno) -- deptno => unique + NN(필수입력)
);