DATABASE(2) - SQL Command Line/SQL developer/SQL(Structured Query Language)/DDL/DML/DCL/TABLESPACE /제약 조건/Constraint/NOT NULL/UNIQUE/CHECK/DEFAULT/PRIMARY KEY/FOREIGN KEY

SQL Command Line을 활용하여 데이터 베이스 구축 가능하지만 더 편리한 SQL DEVELOPER를 사용한다.

SQL Command Line에서 sys 로그인 창

conn : connect 명령어

비밀번호는 화면에 표시되지 않음

 

1. SQL(Structured Query Language)

: 구조적 질의 언어

2. SQL 종류

1. DDL(Data Definition Language) - 정의어
  CREATE : 생성(계정, 테이블, 뷰 등..)
  DROP : 삭제(계정, 테이블, 뷰 등..)
  ALTER : 수정

2. DML(Data Manipulation Language) - 조작어
  INSERT : 데이터 삽입
  DELETE : 데이터 삭제
  UPDATE : 데이터 수정
  SELECT : 데이터 읽어오기

3. DCL(Data Control Language) - 제어어
  GRANT : 권한 부여
  REVOKE : 권한 취소

 

3. SQL 작성

 

사용자(계정) 생성

CREATE USER 이름 
IDENTIFIED BY "비밀번호" 
DEFAULT TABLESPACE USERS 
TEMPORARY TABLESPACE TEMP 
QUOTA UNLIMITED ON USERS; 

-- 마지막에 ';'으로 문장의 끝을 나타냄

생성된 계정에 권한(DB Adminstrator, DB 관리자) 부여

GRANT DBA TO 이름; 

* TABLESPACE 란?
  오라클 DB가 데이터를 저장하는 논리 구조(파일)
  하나 또는 여러개의 파일로 구성.

생성한 계정으로 SQL Developer에 '접속' 생성.


제약 조건(Constraint)이란
  테이블에 부적절한 자료(데이터)가 입력되는 것을
  방지하기 위해서 여러가지 규칙을 적용하는 것.

  - 데이터의 무결성 유지를 위해 사용자가 지정할 수 있는
    성질
  - 의미있는 이름을 부여하여 쉽게 참조할 수 있음.
  - 제약조건은 테이블 생성 시에 지정하거나
    수정(ALTER) 명령으로 추가할 수 있음.
  
제약 조건의 종류
  1. NOT NULL : 반드시 값을 입력해야하는 제약조건
  2. UNIQUE : 중복된 값을 입력하지 못하는 제약조건
  3. CHECK : 특정 범위의 값만 입력하도록 하는 제약조건
  4. DEFAULT : 입력이 없을 경우 지정된 값을 입력
  5. PRIMARY KEY : 기본키 설정
  6. FOREIGN KEY : 외래키 설정

 

 

 

/*
테이블 생성 : CREATE TABLE 테이블명(
속성명1 타입 제약조건,
속성명2 타입 제약조건,
.....
);
*/

CREATE TABLE EMP2(
    EMPNO NUMBER CONSTRAINT emp_pk_empno PRIMARY KEY,
    ENAME VARCHAR2(10) NOT NULL, --NULL 허용하지 않음 
    JOB VARCHAR2(9),  -- 업무
    MGR NUMBER, -- 부서장 직원 번호
    HIREDATE DATE, -- 고용일자
    SAL NUMBER, -- 급여
    COMM NUMBER, -- 수수료
    DEPTNO NUMBER -- 부서번호
);

-- 부서 테이블 DEPT2를 만들어 주세요.
-- 속성 : 부서번호(DEPTNO, 숫자, 기본키),
--       부서명(DNAME, 문자열 40자)
--       위치(LOC, 문자열 50자)

CREATE TABLE DEPT2(
    DEPTNO NUMBER CONSTRAINT dept_pk_deptno PRIMARY KEY,
    DNAME VARCHAR2(40),
    LOC VARCHAR2(50)
);

-- 모든 테이블 조회
SELECT table_name FROM USER_TABLES;

-- NOT NULL 제약조건 : 반드시 값을 입력해야 한다는 제약조건
CREATE TABLE EMP3(
    --ENAME VARCHAR(20) CONSTRAINT emp_nn_ename NOT NULL : 정식적으론 이렇게 쓰는게 맞지만 아래와 같이 간단하게 쓴다
    ENAME VARCHAR(20) NOT NULL
);

-- UNIQUE 제약조건 : 데이터의 유일성 보장 조건(중복 불가)
CREATE TABLE DEPT3(
    --DEPTNO NUMBER CONSTRAINT dept_uk_deptno UNIQUE
    DEPTNO NUMBER UNIQUE
);

-- CHECK : 데이터를 특정 범위로 제한
CREATE TABLE EMP4(
    GENDER VARCHAR2(1) CONSTRAINT emp_ck_gender CHECK (GENDER IN ('M','F'))
);

INSERT INTO EMP4 VALUES ('M');

COMMIT;

-- DEFAULT : 값을 입력하지 않아도 지정한 값으로 입력 처리.
CREATE TABLE MEMBERTBL (
    MID VARCHAR2(20) CONSTRAINT mpk_mid PRIMARY KEY,
    MNAME VARCHAR2(20) NOT NULL,
    MPASS VARCHAR2(16) NOT NULL,
    MGRADE VARCHAR2(10) DEFAULT 'silver'
);

INSERT INTO MEMBERTBL (MID, MNAME, MPASS)
-- 실제 테이블의 컬럼 이름.. 테이블 순서대로 쓸 필요는 없음.
VALUES ('tester', '홍길동', '1234');
-- 앞에 작성한 컬럼의 순서대로 넣어주면 됨
commit;

select * from MEMBERTBL;

-- 기본키 만드는 두번째 방법
CREATE TABLE EMP10 (
    EMPNO NUMBER,
    ENAME VARCHAR2(10) NOT NULL,
    DEPTNO NUMBER, -- 부서번호
    CONSTRAINT emp10_pk_empno PRIMARY KEY (EMPNO),
    CONSTRAINT emp10_fk_deptno FOREIGN KEY (DEPTNO)
    REFERENCES DEPT(DEPTNO)
-- 제약조건을 밑에 줄에 쓰게되면 여러개 컬럼중 어떤 속성의 키로 사용할건지 지정을 해주어야함
);
-- no matching unique or primary key for this column-list
-- : 외래키는 반드시 다른 테이블의 기본키가 와야함. 가지고올 테이블의 기본키를 설정을 해주어야함.
-- 중복되는 데이터를 외래키로 사용할 수 없음.

-- 기본키를 설정하는 세번째 방법
ALTER TABLE DEPT 
ADD CONSTRAINT dept1_pk_deptno PRIMARY KEY (DEPTNO);



--학생 주소록 만들기
CREATE TABLE ADDRESSBOOK(
    STUDENT NVARCHAR2(3), 
    BIRTH DATE, 
    PHONE NVARCHAR2(11),
    EMERGENCY NVARCHAR2(11)
);

-- 데이터의 유일성을 보장하는 컬럼 부재 (기존 속성들은 중복 가능하기 때문에)
-->> 코드화된 컬럼 추가 : SNUMBER NCHAR(3)
ALTER TABLE ADDRESSBOOK ADD SNUMBER NCHAR(3);

-- (가정) 비상연락번호 필요 없어짐 -> EMERGENCY 컬럼 삭제
ALTER TABLE ADDRESSBOOK DROP COLUMN EMERGENCY;

SELECT * FROM ADDRESSBOOK;

-- 유일성 코드 SNUMBER의 크기 변경 : 3 >> 6
ALTER TABLE ADDRESSBOOK MODIFY SNUMBER NVARCHAR2(6);

ALTER TABLE ADDRESSBOOK MODIFY SNUMBER NCHAR(10) NOT NULL; -- 수정할 때 제약조건도 추가할 수 있음.

-- SNUMBER의 타입 변경
ALTER TABLE ADDRESSBOOK MODIFY SNUMBER NUMBER(10); --타입을 수정할 수 있으나 데이터가 들어있는 경우엔 변경 불가 (BUT! CHAR<->VARCHAR는 가능)--확인해보기

-- 테이블 삭제 DROP 명령어 
DROP TABLE ADDRESSBOOK;