[오라클] DB 명령문 - 1

DDL

 - 관계형 데이터 베이스의 객체를 조작하는 명령문
 - 테이블, 시퀀스, 뷰, 시노님, 인덱스 등이 있다.
 - CREATE, ALTER, DROP
 - 테이블에는 (PK, FK, NN, UQ, CK : 제약조건) 
         - DEFAULT : 제약 조건은 아니지만 제약조건처럼 사용

CREATE TABLE 테이블명
(
    컬럼명 데이터 타입 제약조건
    ....
    
);

NAME	42.195
'KOREA' --> VARRCHAR2(15) VAR : 가변 길이 라는 의미 (나머지 메모리는 세이브) 'KOREA' --> CHAR(15) : 고정길이 길이가 다 동일하다면 CHAR 타입을 사용한다. 예 : 주민등록번호	NUMBER(10,3)  NUMBER(3) --> 최대 3자리 : 999

 

테이블 생성 예시

CREATE TABLE MEMBERS (
    USERID NUMBER CONSTRAINTS MEMBERS_ID_PK PRIMARY KEY, -- NN, UK
    -- 1~ 22BYTE 안에서 자유롭게
    USERNAME VARCHAR2(50) CONSTRAINTS MEMBERS_NAME_NN NOT NULL,
    BIRTH DATE,
    AGE NUMBER(3), -- 최대 글자수
    JOIN_DATE DATE DEFAULT SYSDATE 
    -- 지정하지 않으면 오늘 날짜가 기본적으로 들어가게 함
);

 

빈 테이블로 조회된다.

SELECT * FROM MEMBERS;

 

테이블 완전 삭제

DROP TABLE MEMBERS;

 

어느 테이블에 어떤 제약조건이 걸려있는지 확인하기

- 일반 테이블 외에 제약조건을 관리하는 메타 테이블로 확인 가능

SELECT *
FROM user_constraints
WHERE TABLE_NAME = 'MEMBERS';

 

키워드를 관리하는 메타 테이블 조회(DBA 가능) : system 계정 접속해서 볼 수 있음

SELECT * FROM  V$RESERVED_WORDS;

 

키워드 이름까지 포함하는 새로운 테이블 생성

CREATE TABLE FITNESS (
    USERID VARCHAR2(50) CONSTRAINTS MEMBERS_ID_PK PRIMARY KEY, 
    USERNAME VARCHAR2(50) CONSTRAINTS MEMBERS_NAME_NN NOT NULL,
    GENDER CHAR(1) CONSTRAINTS MEMBERS_GENDER_CK CHECK(GENDER IN ('F','M')),
    BIRTH DATE,
    HEIGHT NUMBER(6,2),
    WEIGHT NUMBER(6,2),
    JOIN_DATE DATE DEFAULT SYSDATE 
    -- 지정하지 않으면 오늘 날짜가 기본적으로 들어가게 함
);

확인

SELECT * FROM FITNESS;

 

새로운 테이블 생성 - 언제든지 테이블을 지울 수 있게 하기 위해 DROP 도 같이 적어 둔다.

DROP TABLE MY_TABLE;

CREATE TABLE MY_TABLE(
    USER_ID VARCHAR2(10),
    USER_NAME VARCHAR2(50)    
);

 

값을 넣는 다양한 방법

- 해당 테이블은 제약조건이 따로 없기 때문에 중복적으로 등록이 가능하다.

INSERT INTO MY_TABLE 
VALUES 
('Hong', '홍길동');

INSERT INTO MY_TABLE 
VALUES 
('저팔계');  -- 들어가지 않음

INSERT INTO MY_TABLE 
(USER_NAME) -- 들어가게 하려면 지정해주면 됨
VALUES 
('저팔계'); 

INSERT INTO MY_TABLE 
(USER_ID) 
VALUES 
('Son'); 

INSERT INTO MY_TABLE
(USER_NAME, USER_ID)
VALUES 
('전우치','Jeon'); 


INSERT INTO MY_TABLE
VALUES 
('a','김길동'); 

INSERT INTO MY_TABLE
VALUES 
('b','이길동'); 

INSERT INTO MY_TABLE
VALUES 
('c','최길동'); 

SELECT * FROM MY_TABLE;

 

저장하고 싶다면?

COMMIT;

저장 되지 않고 이전(커밋 전) 상태로 돌아감

ROLLBACK;

 

[문제 ] 테이블을 생성하시오

아이디 : 숫자 3자리 (PK)
상품명 : 가변길이 50자리 - NOT NULL
단가 : 숫자 7자리 - NOT NULL
이익률 : 숫자 (5,1) 0.1

DROP TABLE PRODUCT;

CREATE TABLE PRODUCT(
    PRODUCT_ID NUMBER(3) PRIMARY KEY,
    PRODUCT_NAME VARCHAR2(50) NOT NULL,
    UNIT_PRICE NUMBER(7) NOT NULL,
    RATIO NUMBER(5,1)
);
SELECT * FROM PRODUCT;

INSERT INTO PRODUCT
VALUES ( 1, 'TV리모콘', 15000, 0.1);

INSERT INTO PRODUCT
-- VALUES ( 2, '스마트폰', 250000, 0.2);  
VALUES (PRODUCT_SEQ.nextval, '스마트폰', 250000, 0.2);

코드	의미
VALUES (PRODUCT_SEQ.nextval, '스마트폰', 250000, 0.2);	PK는 유니크 해야하기 때문에 처음 시퀀스 객체를 만들어사용하면 오류가 난다. * 00001 : 무결성 제약조건 오류

 

시퀀스 (Sequence)

- 자동 일련번호 생성기
- 시퀀스를 사용하게 되면 다음의 번호를 요청해서 값을 받을 수 있다.
- 기본값은 1부터 시작해서 1 씩 증가

시퀀스의 컬럼 참조 변수

시퀀스명.nextval	시퀀스명.currval
1 부터 하나씩 번호 증가한다. 오류가 발생해도 번호는 증가한다.	현재 어디까지 증가했는지를 알려준다.

 

시퀀스 만드는 방법

CREATE SEQUENCE PRODUCT_SEQ
--INCREMENT BY 5
-- START WITH 5 -- 5씩 증가 
INCREMENT BY -5
START WITH 1000 -- 5씩 감소

MAXVALUE 100 -- 최대값
MINVALUE 0


CREATE SEQUENCE PRODUCT_SEQ
INCREMENT BY 1
MAXVALUE 10
CYCLE

 

시퀀스 생성

DROP SEQUENCE PRODUCT_SEQ;
CREATE SEQUENCE PRODUCT_SEQ;

사용

SELECT PRODUCT_SEQ.nextval
FROM DUAL; -- 번호 증가


SELECT PRODUCT_SEQ.currval
FROM DUAL; -- 현재 번호

 

삭제  DELETE

지정해 놓지 않으면 데이터 전체를 삭제한다.

DELETE PRODUCT;

유일한 값을 가지고 있는 것을 사용하여 WHERE 절을 사용하여 해당 데이터를 가리켜 삭제 한다.

DELETE (FROM) PRODUCT
WHERE PRODUCT_ID = 1;

 

수정 UPDATE

지정해 놓지 않으면 해당하는 컬럼을 전체 수정한다.

UPDATE PRODUCT
SET UNIT_PRICE =200000;

유일한 값을 가지고 있는 것을 사용하여 WHERE 절을 사용하여 해당 데이터를 가리켜 수정 한다.

UPDATE PRODUCT
SET UNIT_PRICE =20000
WHERE PRODUCT_ID = 1;