hyeonga_code
Database_79_DATABASE_DB 구축 실습 본문
-- DB 구축 실습
------------------------------------------------------
-- 1. DB 설계
-- 2. DBMS 설치_DB 생성
-- 3. 테이블스페이스 생성
-- 4. 사용자 계정 생성
-- 5. 사용자 스키마 생성_테이블, 인덱스, 뷰...
------------------------------------------------------
-- 실습 환경 구축
/*
? TABLESPACE Name : SALESDATA
? DATAFILE : C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALESDATA01.DBF SIZE
? 용량 : 100M
*/
-----< 관리자 >-----
-- 테이블스페이스를 생성합니다.
CREATE TABLESPACE salesdata
DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALESDATA01.DBF'
SIZE 100m;
/*
TABLESPACE SALESDATA이(가) 생성되었습니다.
*/
/*
? ROLE Name : R1
? R1에 포함되는 권한
? CREATE SESSION
? CREATE VIEW
? CREATE SYNONYM
*/
-- 롤을 생성합니다.
CREATE ROLE R1;
/*
Role R1이(가) 생성되었습니다.
*/
-- 롤에 권한을 추가합니다.
GRANT create session, create view, create synonym TO r1;
/*
Grant을(를) 성공했습니다.
*/
/*
? 사용자 이름 및 암호 : SALES/sales1234
? 사용자 기본 테이블스페이스 : SALESDATA
? 롤 : RESOURCE, R1
*/
-- 새 사용자를 생성합니다.
CREATE USER sales IDENTIFIED BY sales1234
DEFAULT TABLESPACE salesdata;
/*
User SALES이(가) 생성되었습니다.
*/
-- 권한을 부여합니다.
GRANT resource, r1 TO sales;
/*
Grant을(를) 성공했습니다.
*/
-----< sales >-----
-- 테이블을 생성합니다.
CREATE TABLE member (
member_id NUMBER(6) PRIMARY KEY,
member_name VARCHAR2(20) CONSTRAINT member_mname_nn NOT NULL,
passwd VARCHAR2(10) CONSTRAINT member_pw_nn NOT NULL,
gender CHAR(3),
phone_no VARCHAR2(13),
address VARCHAR2(50),
join_date DATE DEFAULT SYSDATE,
interest VARCHAR2(15)
);
/*
Table MEMBER이(가) 생성되었습니다.
*/
-- 테이블을 생성합니다.
CREATE TABLE board (
no NUMBER(4) PRIMARY KEY,
subject VARCHAR2(200) CONSTRAINT Board_sub_nn NOT NULL,
content VARCHAR2(2000),
cre_date TIMESTAMP(0) DEFAULT SYSDATE,
member_id NUMBER(6) CONSTRAINT Board_mid_fk REFERENCES member(member_id)
);
/*
Table BOARD이(가) 생성되었습니다.
*/
-----< 인사 관리 >-----
-- 뷰 생성
CREATE OR REPLACE VIEW emp_dept_list_vu
AS
SELECT employee_id AS 사원번호,
CONCAT (CONCAT (first_name, ' '), last_name) AS 사원이름,
CONCAT (email, '@test.com') AS 이메일,
phone_number AS 전화번호,
job_id AS 직급,
department_name AS 소속부서명
FROM employees JOIN departments
USING (department_id)
WHERE department_id NOT IN (80, 90);
/*
View EMP_DEPT_LIST_VU이(가) 생성되었습니다.
*/
-- 뷰 생성
CREATE OR REPLACE VIEW dept_list_sum_vu
AS
SELECT department_name AS 부서이름,
COUNT(*) AS 인원수,
SUM(salary) AS 총급여,
TRUNC(AVG(salary)) AS 평균급여
FROM employees JOIN departments
USING (department_id)
GROUP BY department_name;
/*
View DEPT_LIST_SUM_VU이(가) 생성되었습니다.
*/
-- 뷰 생성
CREATE OR REPLACE VIEW dept_addr_list_vu
AS
SELECT d.department_id, d.department_name,
'(' || postal_code || ')' || l.street_address || ' ' || l.city || ' ' || c.country_name AS address
FROM departments d JOIN locations l
ON (d.location_id=l.location_id)
JOIN countries c
ON (l.country_id=c.country_id);
/*
View DEPT_ADDR_LIST_VU이(가) 생성되었습니다.
*/
-- 객체 권한 부여
-- 뷰에 대한 SELECT 권한 부여
GRANT select on hr.emp_dept_list_vu TO sales;
/*
Grant을(를) 성공했습니다.
*/
-- 뷰에 대한 UPDATE 권한 부여
GRANT update on hr.emp_dept_list_vu TO sales;
/*
Grant을(를) 성공했습니다.
*/
-- 뷰에 대한 SELECT 권한 롤에 부여
GRANT select on hr.dept_list_sum_vu TO r1;
/*
Grant을(를) 성공했습니다.
*/
-- 뷰에 대한 SELECT 권한 PUBLIC에 부여
GRANT select on hr.dept_addr_list_vu TO PUBLIC;
/*
Grant을(를) 성공했습니다.
*/
-----< 실습 준비 완 >-----
--------------------------------------------------------------
-- PART 1 관리자로 로그인
-- SALES 사용자의 생성 정보, 사용자의 DEFAULT TABLESPACE인 SALES 테이블페이스에 대한 정보
-----< 관리자 >-----
SELECT username, account_status, default_tablespace, created
FROM dba_users
WHERE username='SALES';
/*
USEENAME ACCOUNT_STATUS DEFAULT_TABLESPACE CREATED
------------------------------------------------------------------------------------
SALES OPEN SALESDATA 23/07/19
*/
-- DBA_TABLESPACES 및 DBA_DATA_FILES를 조회
-- 사용자의 기본테이블스페이스인 SALESDATA 테이블스페이스에 대한 정보
SELECT tablespace_name, contents, status
FROM dba_tablespaces;
/*
TABLESPACE_NAME CONTENTS STATUS
-----------------------------------------------------------------
SYSTEM PERMANENT ONLINE
SYSAUX PERMANENT ONLINE
UNDOTBS1 UNDO ONLINE
TEMP TEMPORARY ONLINE
USERS PERMANENT ONLINE
SALESDATA PERMANENT ONLINE
*/
SELECT tablespace_name, file_name, bytes/1024/1024 || 'mb' AS file_size
FROM dba_data_files;
/*
TABLESPACE_NAME FILE_NAME FILE_SIZE
-------------------------------------------------------------------------------------------------------------------
USERS C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF 440mb
SYSAUX C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF 680mb
UNDOTBS1 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF 380mb
SYSTEM C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF 360mb
SALESDATA C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALESDATA01.DBF 100mb
*/
-----< sales >-----
-- 게시판의 NO 열에 사용할 board_no_seq라는 이름의 시퀀스를 생성
-- 시퀀스의 시작번호와 증분값은 각각 1로 설정하고 NOCACHE, NOCYCLE 속성 부여
CREATE SEQUENCE board_no_seq
INCREMENT BY 1
START WITH 1
NOCACHE
NOCYCLE;
/*
Sequence BOARD_NO_SEQ이(가) 생성되었습니다.
*/
-- MEMBER테이블의 ADDRESS 열의 크기를 VARCHAR2(100)으로 수정
ALTER TABLE member MODIFY address VARCHAR2(100);
/*
Table MEMBER이(가) 변경되었습니다.
*/
-- BOARD 테이블의 CRE_DATE열에 DEFAULT 값을 SYSDATE로 정의
ALTER TABLE board MODIFY cre_date DEFAULT SYSDATE;
/*
Table BOARD이(가) 변경되었습니다.
*/
-- 회원 테이블과 게시판 테이블을 참조하여 무결성 제약조건을 위반하지 않는 데이터를 3건씩 입력하시오.
/*
? 회원 주소는 시 구 동까지만 입력하시오.
? 게시판의 번호는 board_no_seq 시퀀스를 사용하여 입력하시오
*/
INSERT INTO member
VALUES ( '111111', 'NAME01', 'USER1111', '여', '010-1111-1111', '1시 1구 1동', TO_DATE('2020/11/11', 'YYYY/MM/DD'), 'DB');
/*
1 행 이(가) 삽입되었습니다.
*/
INSERT INTO member
VALUES ( '222222', 'NAME02', 'USER2222', '남', '010-2222-2222', '2시 2구 2동', TO_DATE('2020/11/22', 'YYYY/MM/DD'), 'DB');
/*
1 행 이(가) 삽입되었습니다.
*/
INSERT INTO member
VALUES ( '333333', 'NAME03', 'USER3333', '남', '010-3333-3333', '3시 3구 3동', TO_DATE('2020/11/03', 'YYYY/MM/DD'), 'INTERNET');
/*
1 행 이(가) 삽입되었습니다.
*/
INSERT INTO board
VALUES ( board_no_seq.nextval, '배송 문의', '배송 언제?', TO_DATE('2023/05/06', 'YYYY/MM/DD'), '111111');
/*
1 행 이(가) 삽입되었습니다.
*/
INSERT INTO board
VALUES ( board_no_seq.nextval, '재입고 문의', '재입고 언제?', TO_DATE('2023/05/27', 'YYYY/MM/DD'), '222222');
/*
1 행 이(가) 삽입되었습니다.
*/
INSERT INTO board
VALUES ( board_no_seq.nextval, '교환 문의', '교환 가능?', TO_DATE('2023/06/04', 'YYYY/MM/DD'), '333333');
/*
1 행 이(가) 삽입되었습니다.
*/
-- 회원 테이블에 email 열을 추가하시오.
-- 단, email 열의 데이터 타입은 VARCHAR2(100)이고 UNQUE 제약조건을 함께 정의
ALTER TABLE member ADD email VARCHAR2(100) UNIQUE;
/*
Table MEMBER이(가) 변경되었습니다.
*/
-- 회원 테이블에 국적을 나타내는 country 열을 추가
-- 데이터타입은 VARCHAR2(30), 기본값은 'Korea'로 지정
-- 열 추가 후 MEMBER 테이블을 조회해서 COUNTRY 열의 상태를 확인
ALTER TABLE member ADD country VARCHAR2(30) DEFAULT 'Korea';
SELECT member_name, country FROM member;
/*
MEMBER_NAME COUNTRY
-----------------------------------
NAME01 Korea
NAME02 Korea
NAME03 Korea
*/
-- 회원 테이블에서 INTEREST 열을 삭제
ALTER TABLE member DROP COLUMN interest;
/*
Table MEMBER이(가) 변경되었습니다.
*/
-- 게시판 테이블의 member_id 열에 대해 board_mid_ix 라는 이름의 인덱스를 생성
CREATE INDEX board_mid_ix ON board(member_id);
/*
Index BOARD_MID_IX이(가) 생성되었습니다.
*/
-- 회원테이블에서 회원아이디, 이름, 전화번호, 주소를 볼 수 있는 member_addr_phone_list_vu라는 이름의 뷰를 생성
CREATE OR REPLACE VIEW member_addr_phone_list_vu
AS
SELECT member_id, member_name, phone_no, address
FROM member;
/*
View MEMBER_ADDR_PHONE_LIST_VU이(가) 생성되었습니다.
*/
-- MEMBER_ADDR_PHONE_LIST_VU를 통해 222222 회원의 전화번호와 주소를 수정
UPDATE member_addr_phone_list_vu
SET phone_no='010-0002-0002', address='22시 22구 22동'
WHERE member_id='222222';
/*
1 행 이(가) 업데이트되었습니다.
*/
-- 정보 조회
SELECT member_id, phone_no, address FROM member;
/*
MEMBER_ID PHONE_NO ADDRESS
-------------------------------------------------
111111 010-1111-1111 1시 1구 1동
222222 010-0002-0002 22시 22구 22동
333333 010-3333-3333 3시 3구 3동
*/
-- 게시글을 작성한 회원정보를 볼 수 있독록 회원이름, 게시판글번호, 게시글제목을 볼 수 있는 board_member_list_vu라는 이름의 뷰를 생성
CREATE OR REPLACE VIEW board_member_list_vu
AS
SELECT m.member_name, b.no, b.subject
FROM member m JOIN board b
ON (m.member_id=b.member_id);
/*
View BOARD_MEMBER_LIST_VU이(가) 생성되었습니다.
*/
-- member_addr_phone_list_vu와 board_member_list_vu라는 이름의 뷰에 대하여 각각 m과 b라는 이름의 동의어를 생성
CREATE SYNONYM m FOR member_addr_phone_list_vu;
/*
Synonym M이(가) 생성되었습니다.
*/
CREATE SYNONYM b FOR board_member_list_vu;
/*
Synonym B이(가) 생성되었습니다.
*/
-- board_member_list_vu 라는 뷰에서 작성일(CRE_DATE)가 보이도록 뷰를 수정
CREATE OR REPLACE VIEW board_member_list_vu
AS
SELECT m.member_name, b.no, b.subject, b.cre_date
FROM member m JOIN board b
ON (m.member_id=b.member_id);
/*
View BOARD_MEMBER_LIST_VU이(가) 생성되었습니다.
*/
-- USER_TABLES, USER_CONSTRAINTS, USER_INDEXES 및 USER_OBJECTS를 조회
-- MEMBER와 BOARD 테이블을 비롯한 객체들에 관련된 정보를 파악
SELECT table_name, tablespace_name
FROM user_tables;
/*
TABLE_NAME TABLESPACE_NAME
---------------------------------------
MEMBER SALESDATA
BOARD SALESDATA
*/
SELECT table_name, constraint_name, constraint_type, search_condition, status
FROM user_constraints
WHERE table_name IN ('MEMBER', 'BOARD')
ORDER BY 1;
/*
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION STATUS
-----------------------------------------------------------------------------------------------------------------
BOARD BOARD_SUB_NN C "SUBJECT" IS NOT NULL ENABLED
BOARD SYS_C007175 P ( null ) ENABLED
BOARD BOARD_MID_FK R ( null ) ENABLED
MEMBER MEMBER_MNAME_NN C "MEMBER_NAME" IS NOT NULL ENABLED
MEMBER MEMBER_PW_NN C "PASSWD" IS NOT NULL ENABLED
MEMBER SYS_C007173 P ( null ) ENABLED
MEMBER SYS_C007177 U ( null ) ENABLED
*/
SELECT table_name, index_name
FROM user_indexes
WHERE table_name IN ('MEMBER', 'BOARD')
ORDER BY 1;
/*
TABLE_NAME INDEX_NAME
-------------------------------------------
BOARD BOARD_MID_IX
BOARD SYS_C007175
MEMBER SYS_C007177
MEMBER SYS_C007173
*/
SELECT object_name, object_type, status
FROM user_objects;
/*
OBJECT_NAME OBJECT_TYPE STATUS
-----------------------------------------------------------------------------------
MEMBER TABLE VALID
SYS_C007173 INDEX VALID
BOARD TABLE VALID
SYS_C007175 INDEX VALID
SYS_C007177 INDEX VALID
BOARD_NO_SEQ SEQUENCE VALID
BOARD_MID_IX INDEX VALID
MEMBER_ADDR_PHONE_LIST_VU VIEW VALID
BOARD_MEMBER_LIST_VU VIEW VALID
M SYNONYM VALID
B SYNONYM VALID
*/
-- member테이블을 삭제
DROP TABLE member CASCADE CONSTRAINT;
/*
Table MEMBER이(가) 삭제되었습니다.
*/
-- USER_OBJECTS를 조회하여 사용자 소유의 객체의 이름, 타입, 상태 등을 조회하면서 테이블 삭제의 결과 연관된 객체의 상태를 조회
SELECT object_name, object_type, status
FROM user_objects;
/*
OBJECT_NAME OBJECT_TYPE STATUS
-----------------------------------------------------------------------------------
BOARD TABLE VALID
SYS_C007177 INDEX VALID
BOARD_NO_SEQ SEQUENCE VALID
BOARD_MID_IX INDEX VALID
MEMBER_ADDR_PHONE_LIST_VU VIEW INVALID
BOARD_MEMBER_LIST_VU VIEW INVALID
M SYNONYM INVALID
B SYNONYM INVALID
*/
-- 커밋합니다.
COMMIT;
/*
커밋 완료.
*/
'Oracle Database' 카테고리의 다른 글
Database_81_PL/SQL_변수 선언, 스칼라 데이터, 조합 데이터, 참조 데이터, 부울 변수, 바인드 변수, %TYPE, %ROWTYPE (0) | 2023.08.30 |
---|---|
Database_80_PL/SQL_개요 (0) | 2023.08.30 |
Database_78_DATABASE DB_테이블 스페이스 (0) | 2023.08.28 |
Database_77_DATABASE DB 연결_SELECT, SHUTDOWN (0) | 2023.08.27 |
Database_76_ORACLE 구조_ORACLE DBMS, COMMIT 처리, CKPT (0) | 2023.08.27 |