HR / Schema
user
schema만들기
<경로>
/oracle/product/19.3/db_1/demo/schema/human_resources
[oracle@localhost human_resources]$ ll
>>
total 100
-rw-r--r--. 1 oracle oinstall 1132 Oct 13 2002 hr_analz.sql
-rw-r--r--. 1 oracle oinstall 2733 Aug 30 2002 hr_code.sql
-rw-r--r--. 1 oracle oinstall 7186 Aug 30 2002 hr_comnt.sql
-rw-r--r--. 1 oracle oinstall 9381 Aug 30 2002 hr_cre.sql
-rw-r--r--. 1 oracle oinstall 2609 Nov 7 2015 hr_drop_new.sql
-rw-r--r--. 1 oracle oinstall 2611 Mar 30 2016 hr_drop.sql
-rw-r--r--. 1 oracle oinstall 1345 Aug 30 2002 hr_idx.sql
-rw-r--r--. 1 oracle oinstall 5046 Nov 7 2015 hr_main_new.sql
-rw-r--r--. 1 oracle oinstall 4755 Mar 30 2016 hr_main.sql
-rw-r--r--. 1 oracle oinstall 44288 Aug 16 2008 hr_popul.sql- 경로에 존재하는 파일들은 오라클내에서 만들어논 기본 파일 형태들이다.
- 그중 hr_main.sql 파일의 형태로의 스키마(schema)를 하나 만들어보자
<현재 경로에서 DB로 접속>
/oracle/product/19.3/db_1/demo/schema/human_resources
[oracle@localhost human_resources]$ sqlplus / as sysdba
<DB접속 후 파일 읽어오기>
sql> @hr_main.sql
>>
specify password for HR as parameter 1:
Enter value for 1: 비밀번호
specify default tablespeace for HR as parameter 2:
Enter value for 2: users
specify temporary tablespace for HR as parameter 3:
Enter value for 3: temp
specify log path as parameter 4:
Enter value for 4: $ORACLE_HOME/demo/schema/log
<..생략..>
PL/SQL procedure successfully completed.CREATE
User 생성
user 만들기
sql> CREATE USER 유저명 IDENTIFIED BY 1;
<사용자 생성 상세 설정(테이블스페이스 등)>
CREATE USER scott IDENTIFIED BY 유저명
DEFAULT TABLESPACE tblspace1 --테이블스페이스명
TEMPORARY TABLESPACE temp1 --임시테이블스페이스명
QUOTA 10M on tblspace1 --tblspace1 테이블스페이스에 10M 사용가능
--QUOTA UNLIMITED ON tblspace1 --tblspace1 테이블스페이스 무제한 사용가능
PROFILE profile1 --사용자 프로필 지정
PASSWORD EXPIRE --사용자 최초 로그인 암호 재설정
ACCOUNT LOCK; --사용자 계정 잠금 또는 해제 (LOCK or UNLOCK)사용 비밀번호 변경
<일반>
ALTER USER 유저명 IDENTIFIED BY 1;
<특수문자포함 비번>
ALTER USER 유저명 IDENTIFIED BY "abcd123!@#";
<사용자 최초 로그인 시 비밀번호를 재설정하도록 설정>
ALTER USER 유저명 IDENTIFIED BY tiger123 PASSWORD EXPIRE;계정 권한 할당/제거 방법
<GRANT>
GRANT [권한명] TO 유저명;
<REVOKE>
REVOKE [권한명] FROM 유저명;- 시스템 권한 부여 방법 정리
- SESSION
- CREATE SESSION : 데이터베이스 접속 권한
- ALTER SESSION
- RESTRICTED SESSION
- TABLE
- CREATE TABLE : 테이블 생성 권한
- CREATE ANY TABLE
- ALTER ANY TABLE
- DROP ANY TABLE
- SELECT ANY TABLE
- UPDATE ANY TABLE
- DELETE ANY TABLE
- TABLESPACE
- CREATE TABLESPACE
- ALTER TABLESPACE
- DROP TABLESPACE
- UNLIMITED TABLESPACE
- INDEX
- CREATE INDEX
- ALTER INDEX
- RESTRICTED INDEX
- SESSION
① 테이블 명, ② 컬럼 명
- 테이블, 컬럼 명의 길이는 30byte 문자 (Oracle 12c R2 부터는 128byte)
- 문자(영문, 한글), 숫자, 특수문자(_, $, #)만 가능
- 반드시 문자로 시작
③ 데이터 타입
- NUMBER(4) : 4자리의 가변 길이 정수
- NUMBER(7,2) : 7자리의 가변 길이 정수와 2자리의 가변길이 소수
- VARCHAR2(10) : 10byte의 가변 길이 문자
④ 기본 값
- 테이블에 값이 입력(INSERT) 될 때 값이 없으면 기본으로 생성되는 값
- DEFAULT [값(문자, 숫자, 날짜)], 사용하지 않으면 생략 가능
⑤ NULL 허용 여부
- 기본 값은 NULL 허용이며, NOT NULL 선언 시 해당 컬럼은 NULL 값을 허용하지 않음테이블 생성
CREATE TABLE [1.테이블명]
(
[2.컬럼명] [3.데이터 타입] [4.기본 값(생략가능)] [5.NULL(생략가능)]
);기본키 설정(제약 조건으로 추가하기)
ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건명] PRIMARY KEY([컬럼명])<예시>
CREATE TABLE SALES.TB_EMPS23
(
EID NUMBER(4) NOT NULL,
NANME VARCHAR(4) NOT NULL,
ADDRESS VARCHAR2(30) DEFAULT 'SEOUL',
SALARY NUMBER(8),
DEPTID NUMBER(4)
);
ALTER TABLE SALES.TB_EMPS23 ADD CONSTRAINT sales_pk PRIMARY KEY(EMPS23);NOT NULL :
DEFAULT 'SEOUL' : 값이 없다면, SEOUL로 넣어라.DDL 실습
제약 조건문 CHECK 범위를 지정 할때 사용
<사용법>
ALTER TABLE 테이블명 ADD CONSTRAINT [제약조건명] CHECK(조건 또는 범위);
<예시>
ALTER Table TB_EMPS23 ADD CONSTRAINT CK_SAL_ADDR_EMPS23 Check(SALARY > 0 AND ADDRESS IS NOT NULL);FOEIGN KEY
: 가장 먼저 권한 부터 확인 해야 한다.
<사용법>
GRANT REFERENCES [참조될 테이블의 기본 키] TO [사용자명];
ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건명] FOREIGN KEY(칼럼 명) REFERENCES [참조테이블]
<예시>
GRANT REFERENCES ON HR.DEPARTMENTS TO SALES;
ALTER TABLE TB_EMPS23 ADD CONSTRAINT FK_DEPTID_EMPS23 FOREIGN KEY(DEPTARTMENT_ID) REFERENCES HR.DEPARTMENTS(DEPARTMENT_ID);칼럼명 추가/ 변경
<사용법>
ALTER TABLE [테이블명] ADD [칼럼명] NUMBER(4);
ALTER TABLE [테이블명] RENAME COLUMN [이전 칼럼명] TO [이후 칼럼명];
<예시>
ALTER TABLE TB_EMPS23 ADD LOCATION_ID NUMBER(4);
ALTER TABLE TB_EMPS23 RENAME COLUMN LOCATION_ID TO LOCATIONID;테이블 명 변경
<사용법>
[이전 테이블명] RENAME [이후 테이블명];
<예시>
TB_EMPS23 RENAME TO APAC_EMPS;DML 실습
테이블 데이터 입력
<사용법>
INSERT INTO [테이블 명](컬럼 명, 컬럼 명, ... ) VALUES (데이터 값, '데이터 값', ....);
<다중 테이블 INSERT>
INSERT ALL INTO [테이블 명](컬럼 명, 컬럼 명, ... ) VALUES (데이터 값, '데이터 값', ....)
INTO [테이블 명](컬럼 명, 컬럼 명) VALUES (데이터 값, '데이터 값');
- 데이터 타입을 확인데이터 삭제
<사용법>
DELETE FROM 테이블명
WHERE 조건;
- WHERE 조건문 사용 조심DCL 실습
권한 부여
권한 제거
TCL 실습
연산자 실습
<데이터 확인>
SELECT * from employees;
select last_name, job_id, department_id from employees where (last_name = 'Whalen' AND department_id = 10);
<date 저장양식이 달라 sytax를 맞춰야된다>
SELECT employee_id, last_name, hire_date
from employees
where (hire_date >= '08/01/01');
<데이터양식 확인>
select sysdate from dual;
SELECT employee_id, last_name, salary*12 as ann_sal
from employees
where salary*12 > 150000;
SELECT employee_id, last_name
from employees
where salary <= 3000;
<between>
select employee_id, last_name, salary
from employees
where salary between 4000 And 4500;
<IN>
select employee_id, last_name, salary
FROM employees
where manager_id IN (100, 101, 201);
<LIKE '%'>
select last_name, salary
FROM employees
where last_name LIKE 'A%';
<LIKE '_'>
SELECT last_name, salary
FROM employees
where last_name Like '_b_%';
<Is NUll>
SELECT last_name
From employees
where manager_id Is NUll;
<중복 연산자>
SELECT last_name, salary
FROM employees
where salary > 10000 AND manager_id Is NUll;
SELECT last_name, salary
FROM employees
where salary > 10000 OR manager_id Is NUll;
SELECT last_name
FROM employees
where manager_id is NOT NULL;
<부정 연산자>
SELECT job_id
FROM employees
where job_id NOT IN ('AC_ACCOUNT','AD_BP');
<사이 제외>
SELECT last_name, employee_id
FROM employees
where salary NOT between 10000 AND 15000;
<전체 문자열중에 포함부분 제외 %A% >
SELECT last_name, employee_id
FROM employees
where last_name NOT LIKE '%A%';
<특정 자리에 문자 제외부분>
SELECT last_name, employee_id
FROM employees
where last_name NOT LIKE '_a%';
<값이 있다>
SELECT employee_id, last_name, commission_pct
FROM employees
Where commission_pct is not null;
This is my study archive