[SQL]SQL -Day.3

Oracle

[SQL]SQL -Day.3

MoZZANG 2022. 3. 29. 21:49

DEFAULT

: 데이터 입력 시 해당 컬럼에 아무값도 입력하지 않았을 때 자동적으로 입력해주는 값이다.(제약명을 지정할 수 없다)

▲ COL4와 COL5는 각각 DEFAULT 값으로 Y와 100으로 설정했다.

▲ 위와같이 모든 데이터값을 정해서 넣어주면 디폴트 값은 아무 상관이 없다.

▲ 하지만 위 처럼 값으로 DEFAULT라고 입력해주면 기존에 정해놓았던 DEFAULT값이 자동으로 생성되어 들어가게 된다.

CHECK

- DOMAIN무결성 유지를 위한 제약으로 해당 컬럼에 잘못된 데이터가 입력 되지 않도록 유지하기 위한 제약조건이다.
- 제약 조건은 QUERY조건과 동일하게 주면 된다
- 꼭 CHECK 함수를 쓰지 않아도 된다.

예]
CHECK(regexp_like(컬럼명,'^[a-zA-Z]{3,10}|([1-9]{2,3})$'))

▲ COL2에는 CHECK함수를 이용하여 1~10사이의 숫자만 입력이 가능하며 COL3은 Y 또는 N만, COL4는 해당 정규표현식에 맞는 데이터만 입력이 가능하다.

테이블 변경

1. 기존의 테이블의 구조와 내용을 그대로 복사하기

[형식]

CREATE TABLE 새 테이블명
AS

SELECT * FROM 기존 테이블명;

※테이블 구조와 데이타는 그대로 복사되지만 제약조건 (PK,FK,UNIQUE등)은 복사가 안된다.

▲ 기존에 존재하던 테이블 YESPK. 해당 테이블의 데이터가 똑같이 들어있는 테이블을 복사해보자

▲ 똑같은 내용으로 복사가 된다. 하지만 제약조건은 COPY가 안된다는 중대한 결점이 있으므로 기존의 YESPK는 ID가 PK였기때문에 ID 중복이 불가했는데 YESPKCOPY테이블은 그런 제약조건이 없는 상태이므로 ID가 같아도 입력이 된다.

2. 새로운 컬럼 및 제약조건 추가

예]

- ALTER TABLE 테이블명 ADD 컬러명 자료형 NOT NULL

(NOT NULL제약 조건이 있는 컬럼을 추가 하려면 테이블에 데이터가 비어 있어야 한다)

- ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건

▲ 현재 MEMBER테이블에는 데이터가 존재하는 상태이다. 위 상태에서 반드시 입력해야하는 비밀번호 컬럼을 하나더 추가하고 싶어서 아래와 같이 코드를 만들어 실행시키면 어떻게 될까?

▲ 테이블에 NOT NULL이라는 제약조건을 추가하고 싶으면 무조건 테이블안의 데이터를 다 지운상태에서 추가가 가능하다는 오류가 발생한다.

▲MEMBER테이블 안을 싹비우고 패스워드 컬럼을 만들자.

▲ 우리는 지금 데이터를 추가하거나 수정하는 것이 아닌 컬럼 즉 ,객체를 수정하고 있기 때문에 UPDATE가 아닌 ALTER함수를 사용해야한다. 패스워드 컬럼을 만들고 해당 컬럼에 제약명과 UNIQUE라는 제약조건을 주었다.

즉, ID는 PK이므로 중복이 안되고, 이제부터 패스워드(PWD)또한 UNIQUE선언을 했기 때문에 ID가 달라도 패스워드 중복이 안되게 되는 것이다.

▲ID가 다르지만 PWD가 동일하기 때문에 입력불가하다.

3. 기존 컬럼 및 제약 조건 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬러명
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명
*제약 조건은 수정이 불가 고로 삭제후 다시 ADD를 통해서 추가해야 함

*객체를 제거하는 것이므로 DELETE가 아닌 DROP을 사용해야 한다.

4. 컬럼 속성 변경

-데이타 타입 변경
ALTER TABLE 테이블명 MODIFY 기존필드명 새로운 필드타입

*기존 컬럼을 새로운 데이타 타입으로 변경하려면 테이블에 데이타가 비어 있어야 함.

*기존 자료형보다 큰 자료형으로 수정은 상관없으나 작은 자료형으로 수정할 경우 기존의 저장된 데이타가 작은 자료형보다 자리수가 크다면 에러 발생

-컬럼명 변경
ALTER TABLE 테이블명 RENAME COLUMN 기존 컬럼명 TO 새로운 컬럼명

MEMBER테이블의 각 컬럼의 유형과 크기는 다음과 같다.

▲ 동일한 타입의 자료형으로 바꿀때는 크기가 동일하거나 크게만 수정할 수 있다. 또한

▲ 데이터가 저장되어 있다면 비워야지 변경할 수 있다.

테이블 삭제

DROP TABLE 테이블명 : 테이블이 삭제 된후 휴지통에 들어감
DROP TABLE 테이블명 PURGE : 테이블 삭제시 휴지통에 안놓고 완전삭제
FLASHBACK TABLE 테이블명 TO BEFORE DROP : 휴지통에 들어간 테이블 복원시
SHOW RECYCLEBIN : 휴지통 내용보기
PURGE RECYCLEBIN : 휴지통 비우기
PURGE TABLE 테이블명 : 휴지통에 들어간 특정 테이블 완전삭제

*기본적으로 자식테이블에서 부모테이블을 참조하고 있다면 부모테이블을 삭제할 수 없다.

DROP TABLE 부모테이블 CASCADE CONSTRAINT:
자식이 참조하더라도 강제 삭제하는 경우 단, 자식은 삭제 안됨

데이터 입력

-데이터 입력을 위한 SQL구문 작성시 문자형(CHAR혹은 VARCHAR2등)은 반드시 '(작은따옴표)로 값을 감싸야 한다.
숫자형은 작은따옴표가 필요없다.

1.입력방법 첫번째  SQL구문

INSERT INTO  테이블명(컬럼명1,컬러명2,---------,컬러명N)
VALUES(값1,값2,---------------,값N)

2.입력방법 두번째  SQL구문

INSERT INTO  테이블명
VALUES(값1,값2,---------------,값N)

※ 컬럼에 NULL삽입시: ' ' 혹은 NULL
  디폴트 제약조건을 준 컬럼에 DEFALUT값 삽입시:DEFAULT
  예)
   INSERT INTO  테이블명
VALUES(값1,값2,NULL,DEFAULT)

데이터 수정

-SQL구문

UPDATE 기존테이블명
SET 컬럼명 = 새로운값
[WHERE 업데이트 조건]
--WHERE 없이 사용하면 모든 컬럼이 수정됨

▲ WHERE절 없이 수정하면 모든 컬럼이 수정되므로 꼭 WHERE절을 통해 범위를 정해주자!

VIEW

VIEW는 하나 또는 그 이상의 테이블로부터 생성된 물리적으로 존재하지 않는 가상의 테이블이다.
데이터베이스의 선택적인 내용을 보여줄 수 있기 때문에 데이터베이스에 대한 ACCESS 제한이 가능하다.
복잡한 질의어를 통해 얻을 수 있는 결과를 간단한 질의어를 써서 구할 수 있다.
하나의 테이블로 만든 VIEW에서는 DML(INSERT,DELETE,UPDATE)문장을 수행할 수 있지만 여러 테이블로 만든 VIEW(PK와 FK로 JOIN한)에서는 DML문을 수행할 수 없다. 단, UPDATE와 DELETE는 가능.

VIEW의 장점

(1)보안성 - 중요한 컬럼을 숨길수 잇어 보안을 유지 할 수 있다.
(2)편의성 - 여러 개의 테이블 사용을 위해 복잡한 조인이 사용되었다 하더라도 사용자는 이를 몰라도 된다.
(3)간결성 - 뷰를 사용하면 APPLICATION을 개발할 때 복잡한 쿼리문을 숨길 수 있어 소스가 간결해 진다.

VIEW생성 구문 형식

CREATE  [ OR REPLACE] VIEW VIEW명[(컬럼별칭1,컬럼별칭2...)] //별칭 부여시 "" 로 감싸줘라
AS
SELECT  구문
[WITH READ ONLY]  --뷰를 읽기전용으로 만들때

▲위 EMP테이블에서 원하는 컬럼들만으로 테이블을 구성하고 싶을 때 VIEW는 사용하면 된다.

▲ VW_EMP라는 이름으로 만들어진 VIEW테이블에는 내가 선택한 컬럼만이 들어있기 때문에 앞으로는 VW_EMP를 마치 하나의 테이블명처럼 사용하면 되는 것이다.

하지만 컬럼명이 보기 불편해 가독성이 좋게 바꾸고 싶다. 아래와 같이 하면 컬럼명을 수정할 수 있다.

▲ CREATE OR REPLACE를 사용하면 만약 기존에 없던 VIEW라면 새로 만들어지게되고, 만약 기존에 존재했었다면 수정이 된다.

▲JOIN한 테이블로도 VIEW를 만들 수 있다.

INDEX

행의 검색 속도를 향상시킬 수 있는 개체이다.
인덱스를 명시적(CREATE INDEX) 또는 자동적으로(PRIMARY KEY, UNIQUE KEY)로 생성할 수 있다.
컬럼에 대한 인덱스가 없으면 한 테이블 전체를 검색해야한다. 즉, 인덱스는 쿼리의 성능향상을 시키는 것이 목적.
INSERT/UPDATE/DELETE가 많은 컬럼에 대해서 INDEX를 되도록이면 설정하지 말자.
인덱스가 많은 것이 항상 좋은 것은 아니기 때문이다. 왜냐하면 인덱스를 가진 테이블에 대한 DML작업은 인덱스도 갱신되어야 함을 의미하기 때문이다

인덱스의 종류

클러스터형 인덱스(Clustered Index)
넌 클러스터형 인덱스(Nonclustered Index)

쉽게 책에 비유하자면 클러스터 인덱스는 페이지를 알기 때문에 바로 그 페이지를 펴는 것이고, 넌 클러스터 인덱스는 뒤에 목차에서 찾고자 하는 내용의 페이지를 찾고 그 페이지로 이동하는 것과 같다. 테이블 스캔은 처음부터 한 장씩 넘기면서 내용을 찾는 것과 같다.

결론

클러스터 인덱스는 데이터 위치를 바로 알기 때문에 그 데이터로 바로 접근할 수 있고, 넌 클러스터 인덱스는 인덱스 페이지를 한번 거쳐서 데이터에 접근하는 방식이다.

출처: https://junghn.tistory.com/entry/DB-클러스터-인덱스와-넌클러스터-인덱스-개념-총정리 [코딩 시그널]

[데이터베이스] 클러스터 인덱스와 넌클러스터 인덱스/ 개념 총정리

오늘은 인덱스의 종류인 클러스터 인덱스, 넌 클러스터 인덱스에 대해 정리해보겠습니다. 일단 인덱스란 데이터를 빠르게 검색할 수 있게 해주는 객체입니다. 컬럼을 정렬한 후에 데이터를 빠

junghn.tistory.com

사용자 생성 및 권한

-아래 작업은 DBA(data bse administrator)역할이 있는 최고 관리자(SYSTEM/SYS)로 접속하여야 한다.

1. 사용자 생성 및 암호설정

CREATE USER 아이디 IDENTIFIED BY 암호
*사용자는 생성된 후 어떠한 권한도 가지지 못한다

▲ 단순 생성으로는 아~무것도 할 수 있는 것이 없다. 그저 존재하는 것일뿐

2.사용자에 권한 혹은 역할 부여

권한부여 구문]

Grant 시스템 권한1[,시스템 권한2,...] |[역할1[,역할2,..]
To 사용자1[,사용자2,..]|[역할1[,역할2,..]
[WITH ADMIN OPTION] //받은 시스템 권한을 다른 사용자에게 부여 할 수 있는 권한

- 사용자는 데이타베이스에 액세스하기위해 시스템 권한(SYSTEM PRIVILEGE)이 필요하고 데이타베이스의 개체의 내용을 조작하기 위해 객체 권한(OBJECT PRIVILEGE)이 필요하다

- 시스템 권한은 주로 DBA가 부여한다.(SYS/SYSTEM)
- DBA는 상급의 시스템권한을 부여한다

1. CREATE USER:사용자 생성
2. DROP USER:사용자 제거
3. DROP ANY TABLE:테이블 제거 등

-개체 권한
-시스템 권한을 주면 (CREATE TABLE)
자동으로 개체 권한(INSERT/SELECT/DELETE/UPDATE등이 부여 된다)

*역할
       DBA:최고 권한
       CONNECT:DB에 액세스 할 수 있는 권한
       RESOURCE:개체를 생성할 수 있는 권한

예1]GRANT CREATE TABLE,CREATE SESSION,CREATE VIEW TO 사용자아이디
: 사용자에게 테이블을 생성하고 접속하고 VIEW를 생성할 수 있는 권한 부여

예2]GRANT CONNECT,RESOURCE TO  사용자 아이디
: 사용자에게 접속 및 테이블 생성 권한 동시에 주기(VIEW생성 권한은 없음)

3. 역할을 통해 권한 동시에 주기

4. 암호변경 및 할당량 부여

5. 권한이 없는 다른 사용자에게 특정 테이블 조회 권한 부여하기

PL(PROCEDURAL LANGUAGE)/SQL = SQL + 프로그램

- 프로그래밍 언어의 특성을 수용한 SQL의 확장.
- SQL의 데이타 조작(DML)과 질의문을 블락 구조에 절차적 단위(IF,FOR,WHILE,LOOP등)로 된 커맨드를
포함 할 수 있으며프로그래밍을 가능하게 한 언어이다.

PL/SQL 구조]

DECLARE
변수 및 상수, CURSOR,EXCEPTION선언등 //선언부-선택사항
BEGIN
SQL,PL/SQL문; //실행부 시작-필수사항
EXCEPTION
BEGIN
예외처리 ;//예외처리부-선택사항
END;
END; //실행부 끝 -필수사항
/ ----->반드시 붙여라

- PL/SQL블락내에서 한명령문이 종료할때마다 세미콜론(;)을 붙인다.
- END뒤에 세미콜론(;)을 붙여 하나의 BLOCK이 끝났다는 것을 명시
- 마지막에 반드시 / 를 붙여야 한다.

변수선언]

변수명 [CONSTANT] 자료형 [NOT NULL] [:= 초기값 | DEFAULT 초기값 ];
예]
v_num NUMBER;
v_name VARCHAR2(10) :='홍길동'; //v_name VARCHAR2(10) DEFAULT '홍길동';
V_PI CONSTANT NUMBER(3,2) :=3.14;

-한 라인에 하나의 식별자만 가능
-상수선언에서 CONSTANT는 자료형보다 먼저 기술
-대소문자를 구분하지 않는다.

변수에 값 할당]

사용 예]
VAR[IABLE] NUM NUMBER
DECLARE
    v_date DATE;
    v_num NUMBER ;
    v_name VARCHAR2(10);
BEGIN
    v_date := TO_DATE('2012-01-01','YYYY-MM-DD');
v_num:=10;
v_name:='홍길동';
end;
/

※ 변수의 자료형 지정시 %TYPE을 사용하면 해당 테이블의 자료형으로 선언 한것과 같음
v_addr 테이블명.컬럼명%TYPE;
v_name emp.ename%TYPE => EMP테이블의 ENAME컬럼과 같은 자료형으로 선언한것과 같다
v_name varchar2(10);

바인드 변수 선언]

-호스트 환경에서 선언된 변수
-PL/SQL 프로그램의 내부나 외부에서 전달하기 위해서 사용
-PL/SQL문이 프로시저나 함수 안에 잇지 않는다면 호스트에서 선언된 변수(바인드 변수)를 PL/SQL문장에서 참조 할 수있다.
-호스트 변수와 PL/SQL내에서의 변수를 구분하기 위해 호스트 변수앞에 콜론(:)을 붙인다.
-선언만 할 수 있고 호스트 변수는 선언시에 값을 할 당 못한다.

-선언 방법
VAR[IABLE] 변수명 자료형

사용 예 1]
VARIABLE return_var NUMBER
DECLARE -- 선언부 없으면 생략 가능
BEGIN
:return_var:=300;
END;
/