SQL) 용어정리 및 요약

1) DBMS 설치하기 

- 클라이언트 프로그램으로 접속( SQL Developer, SQL plus 등)

- 환경변수 설정

- 설치한 DBMS 버전확인

 

2) 데이터베이스 생성하기 

- 데이터베이스 스키마 생성 CREATE DATABASE

- 데이터베이스 사용자 계정 생성 CREATE USER

- 사용자 권한 설정 GRANT

 

3) H2DB 기준

- 시퀸스 값 1000에서 재시작 : ALTER SEQUENCE seq_id RESTART WITH 1000;

- CREATE [OR REPLACE] TABLE ~

- CREATE [IF NOT EXISTS] TABLE~ 

- DROP TABLE IF EXISTS comm; //  테이블이 존재한다면 삭제

 

4) 데이터 컬럼 타입 

- NUMBER, VARCHAR2, DATE 등

 

5) 제약조건

- UNIQUE, NOT NULL, PRIMARY KEY, FOREIGN KEY, CHECK, CASCADE 등

 

6) INSERT 문

ex) INSERT INTO 테이블명(컬럼명1, 컬럼명2, ....)

     VALUES(컬럼명1에 삽입할 데이터, 컬럼명2에 삽입할 데이터, ..);

 

7) 오라클 에디션별 특징

- 엔터프라이즈 에디션(EE): 오라클DB의 모든 기능, 대기업용 
- 스탠다드 에디션(SE):DB의 기본기능, 중소기업용
- 스탠다드 에디션 원 (SEO) : SE와 비슷, 단일CPU환경만 가능 
- 익스프레스 에디션(XE): 무료, 기본기능 지원, 상용 사용시 기능 제한이 있음
- 퍼스널 에디션(PE): 개인용, 대부분의 기능을 지원(EE와 비슷한레벨), 단일사용자만 가능

 

8) scott 계정의 암호를 1234로 변경하기

ATLER USER scott IDENTIFIED BY 1234;

 

9) 포트번호 변경

conn sys/1234 as sysdba  // 관리자 계정(sys) 접속

SELECT dbms_xdb.gethttpport from dual;   //  포트번호 확인

exec dbms_xdb.sethttpport(9090);   // 포트번호 9090으로 변경

 

10) 테이블스페이스

1) 테이블, 인덱스, 뷰, 스퀸스, 시노님 등의 객체를 저장하는 공간
2) 필요에 따라 여러개 생성가능 
3) 하나의 테이블스페이스는 최소 하나의 데이터파일로 구성된다.

 

11) 사용자 생성

- 기본테이블 기본 테이블스페이스는 users 임시테이블스페이스 temp를 사용하는 테이블스페이스에                            계정명 test_ex 비밀번호1234 계정생성 

CREATE USER test_ex IDENTIFIED BY 1234 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

- 권한(롤) 부여 : 접속권한, DBA권한부여

GRANT CONNECT, DBA TO test_ex;

12) 테이블스페이스 생성하기

- 테이블스페이스 명 myts, 크기 100MB, 공간이 꽉 찬다면 5MB씩 자동증가, 파일생성 위치는

   C:\oraclexe\app\oracle\oradata\XE에 myts.dbf 파일명으로 생성

CREATE TABLESPACE myts DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\myts.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M;

 

13) myts에서 계정생성

CREATE USER ora_user IDENTIFIED BY ora_user   DEFAULT TABLESPACE myts   TEMPORARY TABLESPACE  TEMP;

-> 마찬가지로 접속, DBA 권한 부여

GRANT CONNECT, DBA TO ora_user;

14) hr사용자에게 scott사용자가 만든 emp테이블을 select할 수 있는 권한 부여

GRANT SELECT ON scott.emp TO hr;

권한이 부여됬으므로 hr계정에서 scott계정의 emp 테이블검색 가능

SELECT* FROM SCOTT.emp;

15) hr 사용자가 할당받은 SELECT 권한 회수

REVOKE SELECT ON SCOTT.emp from hr;

 

16) scott 계정의 암호를 1234로 변경하기

ATLER USER scott IDENTIFIED BY 1234;

17) 테이블스페이스 myts 삭제

DROP TABLESPACE myts INCLUDING CONTENTS AND DATAFILES;

18) CREATE TABLE : 테이블 생성

칼럼명     데이터타입   크기 
-------------------------------
 dno        NUMBER     
 dname    VARCHAR(2)   14
 loc         VARCHAR(2)   13

CREATE TABLE dept(     dno NUMBER,     dname VARCHAR2(14),     loc VARCHAR2(13) );

CREATE TABLE emp(     eno NUMBER,     ename VARCHAR2(10),     dno NUMBER );

19) 테이블 수정 

- emp 테이블의 ename크기를 25로 변경

ALTER TABLE emp MODIFY ename VARCHAR2(25);

20) hr계정에서  employees테이블을 employess2로 복사하기: CTAS 

CREATE TABLE employess2  AS   SELECT * FROM employees;

- 필요로하는 요소만 가져와서 테이블을 복사

CREATE TABLE employees2(emp_id,fname,sal,dep_it) AS  SELECT employee_id,first_name,salary,department_id FROM employees;

- 테이블의 구조만 복사 -> WHERE 조건절을 강제로 false를 만들어 내용을 가져오지 않는다.

CREATE TABLE emp_sample     AS     SELECT* FROM employees WHERE 1=0;

 

21) 테이블 이름 변경

RENAME employees2 TO emp;

22) 테이블 삭제

DROP TABLE emp;

23) 테이블의 컬럼에 기본키 지정

-employees 테이블의 컬럼 employ_id를 기본키로 pk_emp_sample란 이름으로 지정

ALTER TABLE employees ADD CONSTRAINT pk_emp_sample PRIMARY KEY(employ_id);

24) emp_sample의 부서번호 컬럼에 존재하지 않는 부서번호가 저장되지 않도록 
     테이블dapartments의 department_id를 외래키지정하기 

ALTER TABLE emp_sample         ADD CONSTRAINT fk_emp_sample FOREIGN KEY(department_id)         REFERENCES departments(department_id);

25) emp_sample의 커미션 컬럼에 0보다 큰 값만 입력하도록 제약조건 생성

   (제약조건의 이름: chk_emp_sample_comm)

ALTER TABLE emp_sample      ADD CONSTRAINT chk_emp_sample_comm      CHECK(commission_pct > 0);

26) dept 테이블에서 loc 컬럼을 UNUSED로 설정: 삭제와 비슷 

-> 여기서 UNUSED는 삭제대상을 마킹하여 사용하지 못하도록 빠르게 설정한다

ALTER TABLE dept SET UNUSED(loc);

-> 삭제 마킹된 컬럼을 삭제한다. 

ALTER TABLE dept DROP UNUSED COLUMNS;

27) 컬럼(열) 삭제

ALTER TABLE dept DROP COLUMN loc;

28) 시퀸스 생성 및 삭제

- 시퀸스 생성

CREATE SEQUENCE emp_seq;

- 시퀸스 현재번호 호출(최초 호출불가능: 오라클 기준)

SELECT emp_seq.currval from dual;

- 시퀸스 다음 번호 생성

SELECT emp_seq.nextval from dual;

29) VIEW 

EX) emp 테이블에서 사원번호, 사원이름, 부서번호 20번을 가져오는 select이 빈번히 사용될 때
     VIEW로 지정하여 한번에 전송할 수 있다 -> 성능이슈, 트래픽감소

CREATE VIEW V_emp     AS     SELECT empno, ename, deptno FROM emp WHERE deptno = 20 ;

EX) 기존 뷰에 v_emp에 대해서 sal 컬럼도 출력하도록 수정 (컬럼 추가) -> 재생성이라는 개념이 맞다

CREATE OR REPLACE VIEW V_emp     AS     SELECT empno, ename, deptno,sal FROM emp WHERE deptno =20;

- 뷰 삭제

DROP VIEW v_emp;

30) 인덱스 INDEX

EX) emp 테이블의 eno 컬럼에 인덱스 idx_emp_eno 생성하기 -> SELECT 속도가 빨라짐

CREATE INDEX idx_emp_eno ON emp(eno);

EX) 인덱스 삭제

DROP INDEX idx_emp_eno;

31) 데이터베이스 관리자  vs 데이터베이스 개발자
- 관리자 : 사용자 생성, 권한 생성, 권한 부여
-개발자 : CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE INDEX.....