[ SQLD ] 기록용

Local 환경에서 Oracle XE ( Express Edition )와 SQL Developer 설치하기

→ local : 1. 장소의, 토지의 2. 지방[적인], 그 지방(특유)의

3. (병 등) 국부의, 국부적인 4. ( 사고방식 · 견해 등이 ) 좁은, 편협한

5. ( 교통기관의 직통에 대하여 ) 짧은 구간을 왕래하는; 구간 열차 ( local train )

→ localhost : ( computing ) The computer being used locally,

constrasted with remote computers elsewhere on a network.

로컬 호스트 ( localhost ) 는 네트워크를 통해 연결되지만 ( on a network ),

( 물리적으로 ) 다른 곳에 존재하는 ( elsewhere ) 멀리 떨어진 ( 원격) 컴퓨터들과 ( remote computers )

비교하여 ( constrasted with ) 로컬로 ( → 현지에서, 물리적으로 접근할 수 있는 가까운 위치를 의미 ) 사용되어지고 있는 컴퓨터다.

→ localhost는 ( 사용자가 ) 물리적으로 직접 사용하고 있는 컴퓨터를 의미한다.

 

사용자는 MySQL ( RDBMS, 관계형 데이터베이스 관리 시스템 ) 을 통해 데이터베이스를 사용할 수 있고,

원하는 데이터를 사용 및 활용하려면 데이터 언어로 ( data language ) 통신해야 한다.

 

데이터베이스 관리 시스템에 저장되어 있는 데이터베이스에

(사용자의) 권한과 상황에 따라 접근할 수 있는 범위가 달라야 한다 → 데이터베이스 보안

( ex. 회원과 비회원 그리고 회원이지만 등급에 따라 허용된 데이터의 사용 범위가 달라야 한다 )

 

데이터베이스 보안의 유형에는 크게 3가지가 있다.

1. 물리적 환경에 대한 보안

2. 권한 관리를 통한 보안 → 권한이 없는 사용자로부터 데이터베이스를 보호

3. 운영 관리를 통한 보안

→ 권한이 있는 사용자로부터 접근가능한

데이터베이스 범위를 조정하여 데이터베이스를 보호한다.

 

권한 관리를 통한 보안

SQL문을 이용해 데이터베이스 객체에 사용 권한을 부여하거나 부여한 권한을 취소하기

GRANT : 권한 부여, INVOKE : 권한 취소

admin ( 데이터베이스 관리 시스템에서 최고 권한을 갖는 id ) 계정 ( SYS 또는 system ) 으로 접속한 뒤

다른 사용자에게 ( 데이터베이스 ) 객체에 대한 사용 권한을 부여할 때의 SQL문

 

1) 우선 사용권한을 부여하기 전에 새로운 계정을 생성한다.

CREATE USER 생성할계정명 IDENTIFIED BY 생성할계정명의패스워드;

CREATE USER myid IDENTIFIED BY mypw; myid 라는 계정을 만드는데 ( 생성된 ) myid는 mypw로 식별한다.

 

만들어진 새로운 계정은 부여된 권한에 따라 데이터베이스를 사용 및 활용할 수 있는 범위가 달라진다.

 

2) 권한 부여하기

GRANT CONNECT, DBA, RESOURCE TO myid;

→ myid 계정에게 CONNECT ( 접속 ) 에 해당하는 여러 권한들,

DBA에 해당하는 여러 권한들, RESOURCE에 해당하는 여러 권한들을 부여해라

---

데이터 제어어 ( Data Control Language ) 는

1. 데이터베이스에 접근하고

2. 객체들을 사용하도록 권한을 주고 → GRANT

3. 권한을 회수하는 명령어를 의미한다 → REVOKE

 

트랜잭션 제어어 ( Transaction Control Language ) 는

COMMIT, ROLLBACK 그리고 Savepoint 등이 있으며

트랜잭션은 DB의 상태를 변화시키기 위한 ( 하나의 ) 작업 단위를 의미한다.

→ 트랜잭션은 Begin ( 시작 ) 과 End ( 끝 ) 사이에

INSERT / UPDATE  / DELETE 등의 여러 명령어들이 집합되어 있다.

COMMIT을 하면 변경된 내용이 모두 영구적으로 저장된다.

ROLLBACK은 발생한 변경사항을 취소하고, 이전 COMMIT한 곳까지 복구한다.

단, 작업 중간에 Savepoint가 있다면 이전 COMMIT한 곳까지가 아닌

Savepoint 지점까지 복구된다.

 

트랜잭션의 딱딱한 풀이버전

트랜잭션은 논리적인 작업의 단위를 묶어 DML에 의해 조작된 결과를

( 하나의 ) 작업단위 ( Transaction ) 별로 제어하는 명령어인 Commit, Rollback, Savepoint 등이

여기에 해당하며, 일부에서는 DCL 로 분류하기도 한다.

 

 

데이터 정의어 ( Data Definition Language ) 는

테이블의 구조를 정의하거나 변경시키는 명령어로

테이블을 생성하는 CREATE, 테이블의 이름이나 컬럼 이름을 변경하는 RENAME,

테이블의 구조를 변경하는 ALTER

( → 기존 테이블에 새로운 컬럼을 추가 또는 이미 존재하는 컬럼 ( 의 데이터 타입 ) 을 변경

또는 이미 존재하는 컬럼을 삭제 하는 등의 작업이 가능하다 ),

테이블을 삭제하는 DROP 이 있다.

 

데이터 조작어 ( Data Manipulation Language ) 에는데이터를 조회하는 SELECT, 데이터를 삽입하는 INSERT, 데이터를 갱신하는 UPDATE, 데이터를 삭제하는 DELETE 가 있다.

사용자가 무슨 데이터를 원하는지 명세하는 언어지만

어떻게 데이터에 접근해야 되는지를 명세하는 언어는 아니다.

→ 어떻게 데이터에 접근해야 되는지를 명세하는 언어를 절차적 언어 ( ex. PL/SQL )라고 한다.

데이터 조작어 ( DML ) 는 사용자가 원하는 결과만 보여주는 비절차적 언어다.

 

DML 명령어 ( SELECT, INSERT, UPDATE, DELETE ) 가( 절차적, 비절차적과 상관없이 ) 호스트 프로그램 ( ex. 파이썬이나 자바와 같은 프로그래밍 언어로 만들어진 프로그램 ) 속에 삽입되어사용되는 DML ( 데이터 조작어 ) 명령어들을 데이터 부속어 ( Data Sub Languge, DSL )라고 한다. 

 

테이블을 생성하는 SQL 작성

 

CREATE TABLE 테이블명

( 컬럼이름1 데이터타입1,

컬럼이름2 데이터타입2,

컬럼이름3 데이터타입3,

...);

 

테이블 생성할 때 DDL ( 데이터 정의어 ) 인 CREATE 사용하고,

소괄호() 안에 컬럼들을 작성하며 컬럼을 작성할 때,

컬럼이름과 해당컬럼에 들어올 수 있는 값의 유형 ( 데이터타입 ) 을

콤마(,)로 구분하며 나열한다.

( 하나의 ) 명령어의 마침은 세미콜론으로 알려준다.

 

테이블을 생성할 때 컬럼에 제약조건도 작성하기

 

CREATE TABLE 테이블명 (

 컬럼이름1 데이터타입1 [ NOT NULL ] [ AUTO_INCREMENT ] [ PRIMARY KEY ] [ UNIQUE ] [ DEFAULT 기본값] [ COMMENT '컬럼 설명' ],

컬럼이름2 데이터타입2 NOT NULL  AUTO_INCREMENT PRIMARY KEY

컬럼이름3 데이터타입3 NOT NULL  UNIQUE,

컬럼이름4 데이터타입4 DEFAULT 기본값,

컬럼이름5 데이터타입5

);

 

PRIMARY KEY는 NOT NULL & UNIQUE 특성이 있어

컬럼에 PRIMARY KEY ( 제약 조건 ) 을 걸면

NOT NULL, UNIQUE 제약 조건이 자동으로 부여된다.

PRIMARY KEY IS NOT NULL & UNIQUE.

 

PRODUCT 라는 릴레이션을 생성해야 하는데

속성 ( 이름 ) 과 → 속성명 = 컬럼명

속성에 대응하는 값들의 범위를 정함 → 데이터 타입과 제약 조건 → 도메인

 

1. PRODUCT 테이블을 생성할 때 제약조건 작성하기

 

1) 테이블 생성할 때 컬럼 레벨에서 PK ( 제약 조건 중 PRIMARY KEY ) 설정하기

CREATE TABLE PRODUCT (

-- 컬럼 레벨에서 pk 제약조건 설정

PROD_ID VARCHAR2(10) NOT NULL PRIMARY KEY,

PROD_NM VARCHAR2(100) NOT NULL,

REG_DT DATE NOT NULL,

REGR_NO NUMBER(10) NULL

);

 

2) 기존 릴레이션 ( 테이블 ) 에 존재하는 컬럼 ( PROD_ID ) 에 PRIMARY KEY ( 제약 조건 ) 걸어주기

ALTER TABLE 테이블이름 ADD CONSTRAINT 제약조건이름 PRIMARY KEY ( 기존 컬럼 );

- ALTER TABLE PRODUCT ADD CONSTRAINT prod_pk PRIMARY KEY ( PROD_ID );

→ CONSTRAINT 키워드와 제약조건이름 작성은 선택사항

→ 제약조건이름을 작성하려면 CONSTRAINT 키워드도 함께 작성해주어야 한다.

- ALTER TABLE PRODUCT ADD PRIMARY KEY ( PROD_ID );

 

3) 테이블 생성시 PRIMARY KEY 제약 조건을 따로 설정하기

CREATE TABLE PRODUCT (

PROD_ID VARCHAR2(10) NOT NULL,

PROD_NM VARCHAR2(100)  NOT NULL,

REG_DT DATE NOT NULL,

REGR_NO NUMBER(10) NULL,

 

ADD CONSTRAINT prod_pk PRIMARY KEY ( PROD_ID )

 

);

 

 

1) 기존 테이블의 컬럼 타입을 변경할 때 ( 단, 변경하려는 테이블의 컬럼에 데이터가 없어야 한다 )

오라클버전: ALTER TABLE 테이블이름 MODIFY 컬럼이름 변경하고자하는데이터타입;

ALTER TABLE 기관분류 MODIFY 분류명 VARCHAR2(30) NOT NULL;

ALTER TABLE 기관분류 MODIFY 등록일자 DATE NOT NULL;

 

SQL Server버전: ALTER TABLE 테이블이름 ALTER 컬럼이름 변경하고자하는데이터타입;

ALTER TABLE 기관분류 ALTER COLUMN 분류명 VARCHAR(30) NOT NULL;

ALTER TABLE 기관분류  ALTER COLUMN  등록일자 DATE NOT NULL;

 

2) 기존 테이블의 컬럼에 제약조건을 추가하거나 변경할 때

- 추가할 때 ( 제약조건이름 지정할 때 )

ALTER TABLE 테이블이름 ADD CONSTRAINT 제약조건이름 제약조건 ( 컬럼이름 );

- 추가할 때 ( 제약조건이름 지정하지 않을 때 )

ALTER TABLE 테이블이름 ADD 제약조건 ( 컬럼이름 );

 

NULL이란

1) ( 그 누구도 ) 모르는 값이다.

2) 값의 부재 ( 값이 없음 ) 를 의미한다.

3) 공백 문자 혹은 숫자 0 을 의미한다.

4) NULL 과의 모든 비교 ( IS NULL 은 제외 ) 는 알 수 없음 ( Unknown ) 을 반환한다.

NULL 값에 어떠한 연산을 하더라도 결과는 NULL 이다.

 

FOREIGN KEY ( 외래 키 ) 제약 조건 설정

1) 테이블 생성할 때 컬럼 레벨에서 fk 제약 조건 설정하기

[ CONSTRAINT 제약조건이름 ] REFERENCES 참조할테이블이름 [ ( 참조할테이블에서 참조할 컬럼이름 ) ] ON [ 삭제 규칙 ]

 

2) 테이블 생성할 때 테이블 레벨에서 fk 제약 조건 설정하기

[ CONSTRAINT 제약조건이름 ] FOREIGN KEY ( 외래키를 설정할 컬럼이름 ) REFERENCES 참조할테이블 [ ( 참조할테이블에서 참조할 컬럼이름 ) ] ON [ 삭제규칙 ]

- 참조할 테이블에서 참조할 컬럼이름이 생략된 경우

참조할 테이블에서 PRIMARY KEY로 설정된 칼럼이

자동으로 참조할 컬럼이 된다.

 

외래키 삭제 옵션

부모 테이블의 데이터 삭제시 자식 테이블의 데이터를 어떤 식으로 삭제할 것인지 설정하는 것

1) ON DELETE RESTRICT

2) ON DELETE SET NULL

3) ON DELETE CASCADE

A테이블의 a컬럼이 B테이블의 b컬럼을 참조하고 있을 때,

B테이블에서 ( 하나의 ) 행이 삭제될 경우, 삭제된 행의 b컬럼의 값을 참조하는

A테이블의 a컬럼의 행도 함께 삭제된다.

 

제약조건

1) UNIQUE: 테이블 내에서 중복되는 값이 없으며 NULL 입력이 가능하다.

2) PK: NULL 입력이 불가능하다. 주 키로 테이블 당 1개만 생성이 가능하다.

3) FK: 외래 키로 테이블 당 여러 개 생성이 가능하다. FK의 행 값은 여러 개가 중복이 되어도 상관없다.

 

참고 강의

1. [ 어쩌다DBA ] SQLD 문제풀이 1~10 | SQLD 자격증 | 노랭이