20.07.14 DDL, DML, DCL, 문자열 데이터 저장하는 칼럼, SELECTION, PROJECTION, 명명규칙, PK

오라클 접속 아이디 값

-- TableSpace 생성(Create)
-- TableSpace는 오라클에서 Data를 저장할 물리적 공간을 설정하는 것
-- myTS : 앞으로 SQL을 통해서 사용할 TableSpace의 Alis(이름)
-- '.../MyTS.dbf' : 저장할 파일 이름
-- Size : 오라클에서는 성능의 효율성을 주기위해
--  일단 빈 공간을 일정부분 설정하게 된다.
--  크기는 최초에 저장할 데이터의 크기등을 계산하여 설계하고 설정한다.
--  너무 작으면, 효율성이 떨어지고, 너무 크면 불필요한 공간을 낭비한다.

--  오라클 xe(Express Edition)에서는 table Space의 최대 크기를 11G로 제한한다.

--  만약 Size 10G로 지정하고, 용량이 초과되어 AUTO NEXT로 추가가 되는 경우
--  전체 Size가 11G를 넘어서면 오류가 나면서 더이상 데이터를 저장할 수 없게 된다.

-- AUTO... NEXT : 만약 초기에 지정한 Size공간에 데이터가 가득차면
--  자동으로 용량을 늘려서 저장할 수 있도록 만들어라

-- Size의 1M : 기본 크기를 1024 * 1024 byte 크기로 지정하라.
--  Size를 지정할 때 1MB라고 하지 않는다.
-- Next 500K : 자동으로 확장(늘리기)를 1024 * 500 크기로 설정
--  Next를 지정할 때 500KB라고 하지 않는다.

-- CREATE로 시작되는 명령문 : DDL(Data Definition Lang) : 데이터 선언, 생성(추가와는 다름)
CREATE TABLESPACE myTS
DATAFILE 'C:/bizwork/workspace/oracle_data/myTS.dbf'
SIZE 1M AUTOEXTEND ON NEXT 500K;

-- 질의작성기에서 코드를 작성할 때 약속
-- DBMS의 SQL문은 특별한 일부 경우를 제외하고 대소문자 구별을 하지 않는다.
-- DBMS, SQL, 오라클과 관련된 키워드는 모두 대문자로 작성할 것
-- 변수, 값, 내용 등은 소문자로 사용하며
-- 특별히 대소문자를 구분해야하는 경우는 별도 공지를 할 것

-- DROP : DDL명령의 CREATE와 반대되는 개념의 명령문 
-- DRPO 명령은 데이터를 물리적으로 완전 삭제하는 개념이므로 매우 신중하게 사용해야 한다.
DROP TABLESPACE myTS -- myTS tableSpace를 삭제하면서
INCLUDING CONTENTS AND DATAFILES -- 연관된 정보와 data file도 같이 삭제하고
CASCADE CONSTRAINTS; -- 그리고 설정된 권한, 역할 등이 있으면 그들도 같이 삭제하라.

-- 위에서 생성한 TableSpace를 관리하며, 데이터를 조작할 사용자를 생성
CREATE USER user1 IDENTIFIED BY 1234 -- 사용자 ID를 user1으로 설정하고 초기 비번을 1234로 설정
DEFAULT TABLESPACE myTS;

-- DCL : Data Control Lang.
-- 새로 생성된 user1에게 권한을 부여하기
-- user1이 로그인만 할 수 있도록 권한(역할)을 부여하라
GRANT CONNECT TO user1;
-- user1이 로그인할 수 있는 권한을 제거
REVOKE CONNECT FROM user1;

-- user1이 로그인을 수행하고, 최소한으로 데이터들을 관리할 수 있도록 권한을 부여
-- RESOURCE : 오라클에서 유저에게 줄 수 있는 권한 중 상당히 많은 일을 수행할 수 있는 권한
-- 현재 시스템에 설치된 모든
--  TableSpace를 대상으로 무제한(TableSpace가 허용하는 범위) 저장이 가능한 권한
-- RESOURCE 권한은 Standard, Enterprice DBMS에서는 함부로 부여해서는 안된다.
-- CONNECT와 RESOURCE 권한을 부여하게 되면 거의 DBA 수준의 권한을 갖게된다.
GRANT CONNECT, RESOURCE TO user1;

-- 권한을 세부적으로 부여하는 것은 실무상에서 매우 필요하며 중요한 일이다.
-- 하지만
-- 학습하는 입장에서 GRANT 부여하는데 너무 많은 노력을 보이면 피곤하니,
-- xe 버전에서는 사용자에게 DBA 권한을 부여하고, 실습을 수행한다.
GRANT DBA TO user1;

-- DBA 권한(Roll)은 SysDBA보다 한단계 낮은 권한 등급을 가지며
-- 일반적으로 자신이 생성한 Table등 DB Object에만 접근하여 명령을 수행한다.

 

>> 관리자(2020-07-14).sql

-- DB에 데이터를 저장하기
-- DB를 대상으로 업무를 수행할 때 데이터가 있어야만 여러가지 일을 수행할 수 있다.
-- CRUD 중에서 가장 먼저 수행해야 할 명령이 Create형 명령
-- DML CRUD Create는 DDL CREATE 명령과 구분을 해야한다.

-- DDL의 CREATE : 생성이라는 개념이고 운영체제와 밀접한 관련이 있거나,
--  물리적인 요소가 많다.
--  CREATE TABLESPACE 저장공간,
--  CREATE USER 사용자 정도 이며 Schema 등을
--  생성하는 명령 절차

-- DML(Data Mauplation Lan, Data Management Lang.)에서 Create
--  물리적 저장공간에 실제 발생된 Data를 추가 저장하기
--  아직 저장되지 않은 논리적 개념의 데이터를 로컬스토리에 보관하는 개념
--  DML의 Create 형 명령 : "INSERT"

-- RDBMS(Relationship DataBase Manegement System, 관계형 데이터베이스 시스템)
-- 데이터를 추가하려면, 먼저 데이터 저장 공간에 대한 정의를 수행해야한다.
-- 데이터 저장공간을 논리적으로 Entity, 물리적으로 TABLE 이라고 한다.

-- TABLE : 표준 SQL(ANSI SQL)에서 데이터를 추가하는 저장공간
--  저장할 데이터의 각 필드(칼럼) 항목의 type을 결정
--  (학생정보 : 학번, 이름, 학과, 학년, 전화번호, 주소, 나이)
--  저장할 데이터의 최대 길이(크기)를 결정
--  데이터의 Key라는 것을 지정

-- PRIMARY KEY : 데이터의 Key 중에 가장 **중요한** 항목
--  데이터를 조회할 때 이 항목으로 조건을 부여하면 유일한 값이 추출되는 항목
--  PK는 절대 중복값이 있어서는 안된다.
--  PK는 절대 NULL 값이 있어서는 안된다.

-- Table 명명규칙 : 보통 tbl_ 접두사로 시작한다. snake_case 형식으로 이름 지정
-- 칼럼(Field) 명명 규칙 : 보통 table이름을 줄여서 접두사 시작
--      sanke_case 형식으로 이름 지정
-- 문자열 칼럼의 type
-- CHAR, VARCHAR2
-- CHAR : 고정길이 문자열, 칼럼에 저장되는 데이터의 길이가 모두 같은 경우에
--      주로 사용이 된다.
--      저장되는 데이터가 설정한 크기보다 작으면 남는 공간을 공백으로 채운다.
-- VARCHAR2 : 가변길이 문자열, 칼럼에 저장되는 데이터의 길이가 일정하지 않을 때,
--      저장되는 데이터가 설정한 크기보다 작으면 저장공간을 줄여서 저장.
--      저장되는 데이터가 설정한 크기보다 크면 오류 발생.

-- table을 생성하는 DDL
CREATE TABLE tbl_student (
    st_num CHAR(5) PRIMARY KEY,
    st_name VARCHAR2(20),
    st_dept VARCHAR2(20),
    st_grade NUMBER(1),
    st_tel VARCHAR2(20),
    st_addr VARCHAR2(125),
    st_age NUMBER(3)
);

-- DML의 Create
INSERT INTO tbl_student -- tbl_student table에 데이터를 추가하겠다.
    (st_num, st_name, st_dept, st_grade, st_tel, st_addr, st_age) -- 칼럼들 나열
VALUES
    ('10001','홍길동','무역학과',3,'010-111-1111','서울특별시',33); -- 데이터들
    
-- 데이터를 INSERT 할 때 혹시 실수로 PK 칼럼에 이미 저장된 데이터를
-- 또 INSERT 하려고 하면 UNIQUE 오류가 발생하면서 데이터를 저장하지 않는다.
-- 이로서 PK 칼럼에 값이 중복되는 것을 방지하여 데이터 무결성을 유지한다.
DROP TABLE tbl_student;
CREATE TABLE tbl_student (
    
    st_num	CHAR(5)		PRIMARY KEY,
    st_name	VARCHAR2(20)	NOT NULL,	
    st_dpet	VARCHAR2(10),		
    st_grade	NUMBER(1),		
    st_tel	VARCHAR2(20),		
    st_addr	VARCHAR2(125),		
    st_age	NUMBER(3)
);

-- DML : CRUD중 R
-- 추출하여 확인 : 조회(Retrive),읽기(Read)
SELECT * FROM tbl_student ;

-- CRUD에서 Update
-- tbl_student table에 있는 모든 데이터(모든 행, row, record)의
-- st_dept 칼럼의 값을 컴공과로 변경해라
-- 데이터의 개수에 관계없이 명령이 수행되어 버린다.
-- 아래의 UPDATE 명령은 매우 위험하고 신중하게 사용해야 한다.
UPDATE tbl_student
SET st_dpet = '컴공과';

-- UPDATE 명령은 특별한 경우가 아니면 항상 PK를 기준으로 하라.
UPDATE tbl_student
SET st_dpet = '무역과'
WHERE st_num = '10004';

-- 학생 이름(st_name) 칼럼의 값이 성춘향인 모든 데이터를 찾아서
-- 그 데이터들의 st_dept 칼럼 값을 음악과로 변경해라
-- 이 명령의 결과로 만약 성춘향 학생의 데이터가 1개밖에 없어서
-- 원하는 결과를 얻었더라도, 가급적 사용하지 말아야 하는 명령이다.
-- 어떤 이유로 SELECT 수행했을 때 성춘향 데이터가 1개밖에 보이지 않았더라고
-- 만에 하나 감춰진(보이지 않는) 영역에 성춘향 데이터가 또 존재를 한다면
-- 이 명령을 수행하는 순간 이 table의 데이터는 무결성을 잃게된다.
UPDATE tbl_student
SET st_dpet = '음악과'
WHERE st_name = '성춘향';

-- tbl_student 테이블에 저장된 학생 정보가 있는데
-- 장보고 학생의 데이터가 필요가 없어져 삭제를 하려고 한다.

-- 실무에서는 Master Table의 데이터는 삭제를 함부로 하지 않는다.
-- 필요가 없어진 데이터는 특정 칼럼에 값을 세팅하여
-- 칼럼 값을 기준으로 필요한 데이터, 필요없는 데이터를 구분한다.

-- table에서 데이터를 삭제하기 CRUD 마지막 Delete 명령
-- DELETE 명령도 UPDATE 명령과 마찬가지로 **반드시** WHERE절을 동반하는 형태로
-- 명령을 수행해야 한다.
-- 그렇지 않으면 최소 1주일간 지옥을 맛보게 된다.
DELETE FROM tbl_student
WHERE st_num = '10004';

-- INSERT, UPDATE, DELETE 명령을 수행한 후 명령을 확정짓는 명령
-- COMMIT 이후에는 ROLLBACK으로 해당 명령을 취소할 수 없다.
COMMIT;

-- 명령 취소
-- INSERT, UPDATE, DELETE 명령수행을 취소하는 명령
ROLLBACK;

-- 문자열 데이터를 저장하는 칼럼
-- CHAR : 데이터의 길이가 고정된 칼럼, 코드(검색하는 용도의 칼럼)등을 저장하는 칼럼
-- VARCHAR2 : 가변 문자열, 데이터의 길이가 일정하지 않은 칼럼
--      크기를 지정할 때 가장 길이가 긴 데이터를 기준으로 삼아서 정한다.
-- nVARCHAR2 : 가변문자열, 유니코드 문자열을 수용하는 칼럼
--      영문자 1글자, 한글등 1글자를 같은 크기로 취급한다.
CREATE TABLE tbl_student (
    st_num	CHAR(5)		PRIMARY KEY,
    st_name	nVARCHAR2(20)	NOT NULL,	
    st_dept	nVARCHAR2(10),		
    st_grade NUMBER(1),		
    st_tel	VARCHAR2(20),		
    st_addr	nVARCHAR2(125),		
    st_age	NUMBER(3)
);

---

• IN, WHERE, BETWEEN등 으로 조건 추가해서 SELECT문 수행하기
  • SELECTION & PROJECTION

-- SELECTION TYPE으로 데이터를 제한하여 보기

-- 학과가 컴퓨터공학인 학생들만 보여달라
SELECT * 
FROM tbl_student
WHERE st_dept = '컴퓨터공학';

-- 학과가 컴퓨터공학과 이고(AND) 학년이 3학년인 학생들만 보여달라
SELECT * 
FROM tbl_student
WHERE st_dept = '컴퓨터공학' AND st_grade = 3;

-- 학과가 컴퓨터공학 이거나(OR) 학과가 법학인 학생들만 보여라
SELECT * 
FROM tbl_student
WHERE st_dept = '컴퓨터공학' OR st_dept = '법학';

-- 학과가 컴퓨터공학 또는 법학인 학생들의 정보중 이름과 학과만 나열하라
-- SELECTION 으로 레코드를 제한하고 PROJECTION으로 칼럼을 제한하여 보여주기
SELECT st_name, st_dept 
FROM tbl_student
WHERE st_dept = '컴퓨터공학' OR st_dept = '법학';

---

• IN 연산자 & WHERE AND연산자

-- IN(포함)연산자를 사용하여 칼럼에 다수의 조건을 부여하여 값을 가져오기
SELECT st_name, st_dept 
FROM tbl_student
WHERE st_dept IN( '컴퓨터공학' , '법학');

-- 학년이 2 ~ 3인 학생들
SELECT st_name, st_dept, st_grade
FROM tbl_student
WHERE st_grade >= 2 AND st_grade <= 3;

SELECT st_name, st_dept, st_grade
FROM tbl_student
WHERE st_grade > 1 AND st_grade < 4;

-- 시작값과 종료값을 포함한 범위 데이터 조회
SELECT st_name, st_dept, st_grade
FROM tbl_student
WHERE st_grade BETWEEN 2 AND 3;

• 문자열 검색

-- 문자열 칼럼에 저장된 데이터가 모두 길이가 같고 format이 같은 경우
-- 부등호(범위연산)를 포함하여 값을 조회할 수 있다.
-- 일반 프로그래밍 코드에서는 문자일 경우는 사용불가
SELECT st_num, st_name, st_dept, st_grade
FROM tbl_student
WHERE st_num >= '20010' AND st_num <= '20020';

-- 칼럼에 날짜가 문자열로 저장되어 있을 경우
-- 날짜 범위를 설정하여 데이터를 조회 할 수 있다.
-- WHERE st_num >= '2020-01-01' AND st_num <= '2020-10-31'; 

-- 시작값과 종료값이 포함된 범위를 조회하는 코드는 
-- BETWEEN 연산자를 사용하여 조회하는 코드로 변환하여 사용할 수 있다.
SELECT st_num, st_name, st_dept, st_grade
FROM tbl_student
WHERE st_num BETWEEN '20010' AND '20020';

>>USER1(2020-07-14)