728x90

오라클 다운  (11g express)

http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html


다운로드 시, 가입 필요. 설치는 next만 하면 되니 생략


테이블 만들기(dbf파일을 만들고 용량이 100M 을 넘을 때는 5M씩 증가되도록 함)


CREATE TABLESPACE temp DATAFILE

'D:\oraclexe\app\oracle\oradata \temp.dbf' SIZE 100M AUTOEXTEND ON  NEXT 5M;



계정 만들기 (3줄이 한 문장이므로 마지막에만 세미콜론)

CREATE USER 계정명 IDENTIFIED BY 비밀번호

DEFAULT TABLESPACE 테이블이름

TEMPORARY TABLESPACE 테이블 이름;



만든 계정에 접속 권한을 준다.

grant connect,resource to 계정명;

저작자표시 동일조건

'DB' 카테고리의 다른 글

[오라클 DB] 유저관리, DCL  (0) 2021.01.12
[오라클 DB] DB접속 및 DCL  (0) 2021.01.05
데이터 베이스 기초  (0) 2017.06.14
SQL injection (SQL 삽입)  (0) 2017.06.01
MySQL 기초 공부  (0) 2017.05.30
728x90

데이터 베이스 

특정 조직의 업무를 수행하는데 필요한 상호 관련된 데이터들의 모임


데이터 베이스의 정의 4가지

통합 데이터(Intergrated Data) : 검색의 효율성을 위해 중복이 최소화된 데이터의 모임

저장 데이터(Stored Data) : 컴퓨터가 접근 가능한 저장 매체에 저장된 데이터

운영 데이터(Operational Data) : 조직의 목적을 위해 존재 가치가 확실하고 반드시 필요한 데이터

공유 데이터(Shared Data) : 여러 응용 프로그램들이 공동으로 사용하는 데이터


데이터 베이스의 특징

실시간 접근성 : 사용자의 질의에 대하여 즉시 처리하여 응답하는 특징


계속적인 진화 : 삽입, 삭제, 갱신을 통하여 항상 최근의 정확한 데이터를 동적으로 유지


동시 공유 : 여러 사용자가 동시에 원하는 데이터를 공용


내용에 의한 참조 : DB의 데이터를 참조할 때 튜플의 주소나 위치에 의해서가 아니라 사용자가 요구하는 데이터 내용에 따라 참조하는 특징


논리적 독립성 : 응용프로그램과 DB를 독립, 데이터의 논리적 구조를 변경시키더라도 응용프로그램은 변경되지 않는다.


물리적 독립성 : 응용프로그램과 보조기억장치와 같은 물리적 장치를 독립, DB관리 시스템의 성능향상을 위해 새로운 디스크를 도입하더라도 응용프로그램에 영향을 주지않고 데이터의 물리적 구조만 변경될 수 있다.



데이터 설계자 

기업의 업무 수행에 필요한 데이터 구조를 체계적으로 정의하는 사람


데이터 관리자

하나의 기업 또는 조직 내에서 데이터에 대한 정의, 체계화, 감독 및 보안 업무를 담당하는 관리자


데이터베이스 관리자(DBA)

개념 스키마와 물리적 스키마 설계, 보안과 권한 부여, 데이터 가용성과 장애복구의 역할을 맡는 관리자



데이터베이스 설계

데이터베이스 설계란 데이터베이스의 구조(Schema)를 개발하는 과정이다.


요구 조건 분석 

개념적 설계

논리적 설계

물리적 설계

데이터 베이스 구현




ER 모델

1:1 관계 : 관계에 참여하고 있는 두 개체 타입이 모두 하나씩의 개체 어커런스를 갖는 관계


* 어커런스 : '과목'이라는 개체 타입에 대해 '과목코드', '과목이름' 개체 어커런스가 존재할 수 있다.


1:N 관계(일대다) : 한 개체 타입은 하나의 개체 어커런스를, 다른 한 개체 타입은 여러개의 개체 어커런스를 갖는 관계

N:M관계(다대다) : 두 개체 타입 모두 여러개의 개체 어커런스를 가질 수 있는 관계




관계대수


관계해석 : 원하는 정보가 무엇이라는 것만 계산수식을 사용해 정의, 비절차적, 어떻게 얻어야 하는지는 표현하지 않음


관계대수 : 원하는 정보와 그 정보를 검색하기 위해서 어떻게 유도하는가를 기술하는 절차적 언어


세타(Theta) 조인 :  조인에 참여하는 두 릴레이션의 속성 값을 비교하여 만족하는 투플만 반환한다.  * 조건 { =, ≠, ≤, ≥,＜} 등의 비교 연산자 사용


동등 조인 : 세타 조인에서 = 연산자를 사용한 조인, 내부조인(inner join)이라고도 한다.


자연 조인 : 동등조인에서 조인에 참여한 속성이 두 번 나오지 않도록 두 번째 속성을 제거한 결과를 반환한다.




키 (Key)

데이터베이스에서 조건에 만족하는 튜플을 찾거나 순서대로 정렬할 때, 기준이 되는 속성.


기본키 : 하나의 튜플을 하나의 뜻으로 확인하기 위해 이용되는 키 (ID나 학번 등)


슈퍼키 : 한 릴레이션 내에서 유일성의 특성을 만족하는 속성 또는 속성들의 집합이다. 

( 예 : 학번, 주민등록번호, (학번,주민등록번호), (학번,주민등록번호,성명.) 

학번이나 주민번호가 최소성을 만족한다고해서 슈퍼키가 아닌 것은 아니며, 슈퍼키 중에서 유일성과 최소성을 모두 만족시키면 후보키가 된다.

예에서 (학번,주민등록번호) 와 같이 2개 이상의 필드를 조합하여 만든 키를 복합키라고 한다 ) 



후보키 : 릴레이션의 튜플(tuple)들을 구별할 수 있는 최소한의 속성들의 집합, 모든 릴레이션은 최소한 하나의 후보 키를 가진다. 슈퍼 키 중에서 유일성과 최소성을 모두 만족 시키는 키가 후보키가 된다. (학번이나 주민번호 등)


* 유일성(Unique) : 하나의 키 값으로 하나의 튜플만을 유일하게 식별 가능

* 최소성(Minimality) : 키를 구성하는 속성 하나를 제거하면 유일한 것으로 식별할 수 없게 되는 최소의 속성.


외래키 : 다른 릴레이션의 기본키를 참조하는 속성 또는 속성들의 집합


대체키 : 후보키 중에서 선정된 기본키를 제외한 나머지 후보키

예) 학번,주민번호,성명으로 구성된 <학생> 릴레이션에서 학번이 기본키로 설정되면 주민번호는 대체키가 된다. (성명은 중복가능하므로 슈퍼키도 후보키도 될 수 없다)



* 튜플 : 릴레이션의 각 행을 의미, row 혹은 record와 같다.



DDL (데이터 정의 언어 : Data Define Language)

Schema, Domain,Table, View, Index를 정의하거나 변경 또는 삭제할 때 사용하는 언어


DDL 유형

CREATE, ALTER, DROP


CREATE 문


CREATE SCHEMA

스키마를 정의하는 명령문    * 스키마 : 하나의 사용자에 속하는 테이블과 기타 구성요소를 구별짓기 위한 것 (자세한 내용은 http://npcore.tistory.com/70)

1
CREATE SCHEMA 스키마명 AUTHORIZATION 사용자ID;
cs

EX) 스키마 중학교를 정의, 사용자는 윤정수

1
CREATE SCHEMA 중학교 AUTHORIZATION 윤정수
cs


EX2) 스키마 개인정보를 정의, 사용자는 김철수

1
CREATE SCHEMA 개인정보 AUTHORIZATION 김철수;
cs


CREATE TABLE

테이블을 정의하는 명령문


1
2
3
4
5
6
7
8
9
CREATE TABLE 테이블명
    (속성명 데이터_타입 [NOT NULL}, ...
    [, PRIMARY KEY (기본키_속성명, ...) ]
    [, UNIQUE (대체키_속성명, ...) ]
    [, FOREIGN KEY (이ㅗ래키_속성명 ...)
        REFERENCES 참조테이블(기본키_속성명, ...) ]
        [ON DELETE 옵션]
        [ON UPDATE 옵션]
    [, CONSTRAINT 제약조건명] [CHECK (조건식) ]);
cs


테이블 생성시 칼럼의 값이 NULL값을 갖지 못하게 하려면

1
2
CREATE TABLE User
( id CHAR(5) NOT NULL,
(생략...) );
cs


옵션

ON DELETE  : 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 사항을 지정


ON UPDATE : 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블에 취해야 할 사항을 지정


하위 옵션 

CASCADE : 참조 테이블의 튜플이 삭제되면 기본 테이블의 관련 튜플도 모두 삭제되고, 변경시에도 동일한 액션을 취함

SET NULL : 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플의 속성 값을 기본값으로 변경


EX)

2번째 행 : Department 테이블에서 튜플이 삭제되면 관련된 모든 튜플의 'dept' 속성의 값을 NULL로 변경

3번째 행 : Department 테이블에서 튜플이 변경되면 관련된 모든 튜플의 'dept' 속성의 값도 같은 값으로 변경 

1
2
3
FOREIGN KEY(dept) REFERENCES Department(name)
ON DELETE SET NULL
ON UPDATE CASCADE
cs

*주의할 점 : FOREIGN KEY~부터 ON UPDATE CASCADE까지 한 문장이므로 사이에 쉼표( , )를 쓰지 않는다.



1
FOREIGN KEY (속성) REFERENCES 테이블(속성)
cs

위의 FOREIGN 절은 다음과 같이 줄여 쓸수도 있다.
1
FOREIGN KEY REFERENCES 테이블명
cs



VARCHAR2 데이터타입

String2 형태의 가변길이 값을 저장하며, 4000 바이트의 최대 가변길의 문자열을 저장하는데 사용한다.



CREATE DOMAIN

도메인을 정의하는 명령문    * 도메인 : 특정 속성(칼럼)에서 사용할 데이터 범위를 사용자가 정의하는 사용자 정의 데이터 타입


1
2
3
CREATE DOMAIN 도메인명  데이터 타입
[DEFAULT] 기본값]    -- 대괄호는 생략이 가능하단 의미
[CONSTRAINT 제약조건명 CHECK (범위 값) ];
cs



제약 조건 정의의 예

1
CONSTRAINT isSex CHECK (sex='f' or sex='m')
cs


FOREIGN KEY(ID) REFERENCES student(student_num)에서 제약조건명을 정의하기 위해, CONSTRAINT절 사용

1
CONSTRAINT id_fk FOREIGN KEY(ID) REFERENCES student(student_num)
cs


EX) 남, 여 와 같이 정해진 1개의 문자로 표현되는 도메인 SEX 를 정의하는 SQL문


1
2
3
CREATE DOMAIN SEX CHAR(1)
DEFAULT '남'
CONSTRAINT isSex CHECK (VALUE IN ('남', '여'));
cs




CREATE INDEX

인덱스를 정의하는 명령문    * 인덱스는 검색을 빠르게 하기 위해 만든 보조적 데이터 구조

1
2
CREATE INDEX <인덱스명>
ON 테이블명(속성명)
cs

ex) <직원> 테이블에 대해 '이름'속성으로 '직원_이름'이란 인덱스 정의
1
2
CREATE INDEX 직원_이름
ON 직원(이름)
cs


UNIQUE가 사용되면 중복값이 없는 속성으로 인덱스 생성(생략시 중복값 허용)


ASC : 오름차순, DESC : 내림차순 (생략시 오름차순)


CLUSTER : 지정된 키에 따라 튜플들을 그룹으로 지정하기 위해 사용

1
2
3
CREATE [UNIQUE] INDEX <인덱스명>
ON 테이블명( {속성명 [ASC | DEXC] [,속성명 [ASC | DEXC] ]} )
[CLUSTER];
cs


EX) <Student> 테이블의 ssn 속성에 대해, 중복값이 없는 속성 'Stud_idx"라는 이름으로 오름차순 인덱스 생성

1
2
CREATE UNIQUE INDEX Stud_idx
ON Student(ssn ASC);
cs


트리거(Trigger)

데이터베이스 시스템에서 데이터의 입력, 갱신, 삭제 등의 이벤트가 발생할 때마다 자동적으로 수행되는 사용자 정의 프로시저


1
2
3
4
5
CREATE TRIGGER 트리거명 [동작시기 옵션] [동작옵션] ON 테이블명
REFERENCING [NEW | OLD] TABLE AS 테이블명
FOR EACH ROW
WHEN 조건식
트리거 BODY
cs




뷰(view)

하나 이상의 기본 테이블로부터 유도되는, 이름을 갖는 가상 테이블


1
2
3
CREATE VIEW 뷰 이름[ (속성명 [, 속성명, ~~~~] ) ]    
-- 대괄호는 생략 가능, 괄호안의 속성은 VIEW에 적용, 생략시 SELECT문 속성명 자동적용
AS 
SELECT문;     -- Sub Query
cs


EX) 고객 테이블에서 주소가 안산시인 고객들의 성명과 전화번호를 보여주는 '안산고객' 이란 이름의 뷰 생성


1
2
3
4
5
6
CREATE VIEW 안산고객(성명, 전화번호)   
-- 안산고객 이란 뷰 이름으로 정의, 속성명은 성명과 전화번호 / 속성명 기술하지 않으면 SELECT문 속성명 자동 적용
AS 
SELECT 성명, 전화번호
FROM 고객            
WHERE 주소='안산시';    
cs



EX 2) <교수> 테이블 에서, 재직년도가 10년 이상인 교수의 이름 속성을 추출한 <장기임용교수명단> 뷰를 정의하는 SQL문 작성

교수(@교수번호, 주민등록번호, 이름, 직위, 재직년도)


1
2
3
4
5
CREATE VIEW 장기임용교수명단
AS SELECT 이름
FROM 교수
WHERE 재직년도 >= 10
WITH CHECK OPTION;
cs



WITH CHECK OPTION은 생성된 뷰에 갱신이나 삽입 연산 수행 시, 

뷰의 정의 조건(위의 예에서는 '재직년도' 10년 이상)을 위배하면 실행을 거절하는 옵션이다.





ALTER 문

EX) patient 테이블에 job 속성(문자 20자리)을 추가한다.

1
2
ALTER TABLE patient
ADD job CHAR(20);
cs


EX) <학생> 테이블의 '사진'속성을 삭제하되 '사진' 속성을 참조하는 데이터도 함께 삭제한다.

1
2
ALTER TABLE 제품
DROP COLUMN 사진 CASCADE;
cs


EX) <학생> 테이블의 '나이' 속성의 기본값을 20으로 변경한다.

1
2
ALTER TABLE 학생
    ALTER 나이 SET DEFAULT 20;
cs



SELECT문

: 릴레이션에 존재하는 튜플 중에서 선택 조건을 만족하는 튜플의 부분집합을 구하여 새로운 릴레이션을 만든다.


관계대수식 표기 ) 시그마를 사용, R은 릴레이션을 의미

σ<조건>(R)


ex) <학생>릴레이션에서 학년이 4학년인 릴레이션을 생성

σ학년=4(학생)


ex) <학생>릴레이션에서 학년, 학과 속성만 가지고 릴레이션 생성

σ학년,학과(학생)



특정 속성 값의 합계를 구하여 검색하고, 이 속성을 다른 속성으로 표현하고자 할 때


EX) 소매점명 과 반품수 를 검색하고, 반품수는 합계를 구해 총 반품수로 나타낸다.

1
SELECT 소매점명, SUM(반품수) AS 총반품수
cs


중복되는 속성의 값 제거


Ex) 'name'을 표시하되, 같은 'name' 속성의 값은 한번만 표시

1
SELECT DISTINCT name
cs



FROM 절

검색할 데이터가 들어있는 테이블 명을 기술


EX) 반품현황 , 소매점 테이블

1
FROM 반품현황, 소매점
cs



WHERE 절

검색 조건을 기술. 다양한 조건 연산자 사용이 가능하고, 각 연산자 처리순서는 연산자 우선순위를 따른다.

1
WHERE 반품현황.소매점 코드



하위질위

ex) <여가활동> 테이블에서 취미가 '나이트댄스'인 사원('이름')에 대해, <사원> 테이블에서 사원의 '이름'과 '주소'를 검색하는 SQL문

1
2
3
SELECT 이름, 주소
FROM  사원
WHERE 이름 IN (SELECT 이름 FROM 여가활동 WHERE 취미='나이트댄스);
cs


ex) 여가활동이 없는 사원들을 검색하는 SQL문

1
2
3
SELECT *
FROM 사원
WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동);
cs



ORDER BY 절

특정 속성을 기준으로 정렬하여 검색할 때 사용. [ASC | DESC] : ASC(오름차순)이 기본값이며, DESC 내림차순 정렬


EX) 소매점 테이블에서 반품수 합계 (총 반품수)를 내림차순으로 표시하는 SQL문

1
ORDER BY SUM(반품수) DESC
cs



COUNT 함수와 GROUP BY 활용


<결제> 테이블에서 '결제여부'별 학생수를 검색 (학생수는 COUNT 함수로 집계 후 학생수라는 속성을 부여한다.)

1
2
3
SELECT 결제여부, COUNT(*) AS 학생수
FROM 결제
GROUP BY 결제여부
cs




JOIN 연산

공통 속성을 중심으로 2개의 릴레이션을 하나로 합쳐서 새로운 릴레이션을 만든다. 기호 ⋈


두 테이블을 특정 속성(칼럼)으로 동일 JOIN 하고자 할 때


EX) 반품현황, 소매점 테이블이 있고 두 테이블을 '소매점 코드'로 조인하고자 한다.


1
WHERE 반품현황.소매점 코드 = 소매점.소매점코드



PROJECT 연산


주어진 릴레이션에서 속성 리스트에 제시된 속성 값만 추출하여 새로운 릴레이션을 만든다. 기호  π (파이)

연산 결과에 중복이 발생하면 중복은 제거된다.


관계대수식 표기 )

π <속성리스트> (R)      * R은 릴레이션을 의미


ex) <성적> 릴레이션에서 이름과 국어 성적 추출

π이름,국어(성적)



DML (Data Manipulation Language)

데이터베이스 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 관리하는데 사용되는 언어


INSERT    : 테이블에 새로운 튜플 삽입

EX) 사원 테이블의 성명과 연차 속성에 새로운 데이터를 삽입

1
2
INSERT INTO 사원(성명, 연차)
VALUES ('김민정', 2);
cs


ex) SELECT문을 사용하여 다른 테이블의 검색 결과 삽입

1
2
3
4
INSERT INTO 기획부(성명, 경력, 주소, 기본급)
SELECT 성명, 경력, 주소, 기본급
FROM 사원
WHERE 부서='기획';
cs



DELETE    : 테이블에서 튜플 삭제

EX) 사원 테이블에서 이름 칼럼의 데이터가 조정윤인 튜플 삭제

1
2
DELETE FROM 사원
WHERE 이름='조정윤';
cs

EX) 사원 테이블의 전체 데이터 삭제(WHERE 조건이 없으므로 모두 삭제된다)
1
DROP FROM 사원;
cs



UPDATE    : 테이블에서 튜플 내용 갱신

EX) 사원 테이블에서 김민정과 이철수란 이름이 있으면 부서를 편집으로 갱신

1
2
3
UPDATE 사원 
SET  부서='편집' 
WHERE 이름 IN ('김민정', '이철수')
cs


트랜잭션(Transaction)

데이터베이스의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위 또는 한꺼번에 모두 수행되어야 할 일련의 연산


- 제어명령어 : COMMIT, ROLLBACK

: 수행한 연산은 DB에 최종 반영시 커밋(COMMIT)하여 연산을 마침

: 또는 롤백(ROLLBACK)을 통해 해당 작업을 되돌릴 수 있음



튜닝 ( Tunning )

데이터베이스를 최적화 하는 것. (업무의 최적화, 하드웨어적인 병목 현상 해결, SQL 최적화 등)



식별관계( Identifying Relationship )

부모 테이블의 기본키가 자식 테이블의 기본키의 구성원으로 전이되는 식별관계 (부모가 자식의 모든 정보를 저장하게 됨)


ex) <지점> 테이블에 지점코드(PK) 칼럼이 <지점 세부> 테이블의 지점코드(FK) 의 구성원일 때 

( <지점> 테이블의 지점코드는 <지점세부> 테이블의 외래키이면서 동시에 기본키)



정규화

속성들 간의 종속성을 분해해서 기본적으로 하나의 종속성이 하나의 릴레이션에 표현되도록 분해하는 것



1NF (제1 정규형) 

테이블 R에 속한 모든 속성의 도메인이 원자 값만으로 되어 있는 정규형

 (제1 정규화라고 하면 비정규 릴레이션에서 바로 이 1NF로 만드는 것을 의미하며, 1정규화를 거쳐 1정규형이 되었다고 표현한다)


EX) 하나의 학번(속성)이 여러 개의 수강 과목(값)을 가지고 있을 때, 이러한 다중 값 속성을 제거해서 릴레이션의 모든 속성 값이 원자 값만을 갖도록 한다.


2NF (제 2 정규형) 

테이블 R이 제 1 정규형이고, 기본키가 아닌 모든 속성이 기본키에 대하여 완전 함수적 종속을 만족하는 정규형 (즉, 부분함수적 종속을 제거)


3NF (제 3 정규형) 

테이블 R이 제 2 정규형이고 기본키가 아닌 모든 속성이 기본 키에 대해 이행적 함수적 종속을 만족하지 않는 정규형


BCNF (보이스 코드 정규형 Boyce-Codd Normal Form)     * 3 정규형과 4정규형의 사이에 속한다.

테이블 R에서 모든 결정자가 후보키인 정규형이다. 


* 결정자 : 학번->이름 일때 '학번'이 결정자이고 '이름'이 종속자이다. (학번에 따라 이름이 결정)


ex) 보이스 코드 정규화의 예


<주문정보>

주문번호

 거주지

 구매자

 BI0124

 부산 

 김길동 

 BI0125

 대구

 이상민
 BI0126 부산  김길동
 BI0127 서울  홍영수


<주문정보> 릴레이션에는 결정자이지만 후보키가 아닌 속성이 존재한다.  ( '구매자' -> '거주지역'에서 '구매자'는 결정자이지만 후보키가 아님 )

같은 구매자가 시간을 두고 구매한 건이 있을 때, 구매자 속성만으로는 레코드를 유일하게 식별할 수 없기에 후보키가 아닌 속성을 제거해야한다.




함수적 종속 관계

어떤 릴레이션 R에서, X와 Y를 각각 R의 애트리뷰트 집합의 부분 집합이라고 하면, 애트리뷰트 X의 값 각각에 대해 시간에 관계없이 항상 애트리뷰트 Y의 값이 오직 하나만 연관되어 있을 때 Y는 X에 함수 종속이라 하고, X → Y라고 표기한다.


정규화를 하기 위해서는 해당 릴레이션에 존재하는 함수적 종속 을 먼저 파악해야 한다.


<학생>릴레이션에서 '학번' 속성이 '학과' 속성을 함수적으로 결정한다고 하면,  '학과'는 '학번'에 함수적 종속이라고 하고,

이를 학번 -> 학과 와 같이 표현한다.



EX) 주문 릴레이션 (주문번호, 부품번호, 거래처코드, 거래처지역, 부품가격, 주문물량, 주문날짜)


주문번호, 부품번호는 식별자이며, 이외의 다른 모든 속성은 기본키인 (주문번호, 부품번호)에 의해 함수적으로 종속된다.

표기 ) 주문번호, 부품번호 -> 거래처 코드, 거래처 지역....


주문번호가 주문 날짜를 결정한다. (특정 주문 번호는 항상 같은 주문 날짜를 가진다고 할 때)

표기 ) 주문번호 -> 주문날짜



완전 함수적 종속

EX) 학번(PK), 과목(PK), 학점 속성을 가진 릴레이션 R이 있다고 할 때, 기본키인 '학번'과 '과목'속성을 참고해야만 '학점'을 알 수 있을 때, 이는 완전 함수적 종속이다.



부분 함수적 종속

학번(PK, 과목코드(PK), 과목명 속성을 가진 릴레이션 R이 있다고 할 때, 과목코드만 참고 해도 '과목명'을 알 수 있기 때문에 이는, 부분 함수적 종속이다.



이행적 함수 종속

'주문번호'가 '상품 코드'와 '회원 ID'를 결정지을 때, '회원 ID' 만으로 '회원명'과 '전화번호'를 알 수 있다면 이는 A->B->C가 된다. 

즉, A->B 이고 B->C 일때, A->C를 만족하므로 이행적 함수 종속이 생긴 것이다.


다치 종속

A, B, C 기본키 속성을 가진 릴레이션 R이 있다고 할 때, A->B이지만 C는 다른 여러개의 속성에 함수적 종속된다면, 이는 다치 종속이 생긴 것이다. 제 4정규화에서 다치 종속성을 제거하게 된다.



역정규화

분해된 릴레이션을 다시 합치는 것 


역정규화를 하는 이유 : 과도하게 정규화된 (분해된) 릴레이션으로 성능이 떨어질 수 있다, 분해된 릴레이션을 JOIN을 사용해 연결하는 질의 소요 비용이 증가한다



무결성

데이터베이스에서 무결성은 결함이 없는 성질을 의미하며, 정확성, 유효성을 뜻한다. 무결성의 핵심은 데이터베이스에 저장된 데이터 값을 정확하게 유지하는데 있다.


참조 무결성 : 외래키 값은 NULL 이거나 참조 릴레이션의 기본키 값과 동일해야 한다!

개체 무결성 : 기본 릴레이션의 기본키를 구성하는 어떤 속성도 NULL 일 수 없다.


도메인 무결성 : 특정 속성의 값이 그 속성이 정의된 도메인에 속한 값이어야 한다. (예 : <고객> 릴레이션의 성별 속성에는 '남', '여'만 존재)


* 릴레이션 : 데이터의 집합, 테이블이라고도 한다.




갱신 분실(Lost Update)

2개 이상의 트랙잭션이 같은 데이터를 공유하여 갱신할 때 생기는 문제.



DCL(Data Control Language)

데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용되는 언어


GRANT : 권한 부여를 위한 명령어


REVOKE : 권한 취소를 위한 명령어



사용자 등급 지정

1
GRANT 사용자등급 TO 사용자id리스트[IDENTIFIED BY 암호];
cs


사용자 등급 해제

1
REVOKE 사용자등급 FROM 사용자id리스트;
cs


테이블 및 속성에 대한 권한 부여 

1
GRANT 권한종류 ON 개체 TO 사용자 [WITH GRANT OPTION];
cs
* WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게도 부여할 수 있는 권한을 부여


테이블 및 속성에 대한 권한 취소

1
REVOKE [GRANT OPTION FOR] 권한종류 ON 개체 FROM 사용자 [CASCADE];
cs

* GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소

* CASCADE : 권한 취소 시, 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연속적(CASCADE)으로 취소


* 자신에게 특정 권한이 없어지면 다른 사용자에게 그 권한을 부여할 수 없다.

REVOKE UPDATE ON 수강 FROM 김민희 CASCADE; 라고 명령을 내리면 '김민희' 사용자에게 UPDATE권한이 없어짐과 함께 다른 사용자에게 권한을 부여할 수 있는 권한, 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연속적으로 취소된다.


* 다음에 주의

REVOKE GRANT OPTION FOR INSERT ON 강좌 FROM 김철수; 라고 하면 '김철수' 사용자의 INSERT 권한은 유지하면서 다른 사용자에게 권한을 부여할 수 있는 권한만 취소하는 것이다.

 

REVOKE GRANT OPTION FOR UPDATE ON 강좌 FROM 김철수 CASCADE;

 '임꺽정'  유저에게 부여한 <강좌> 테이블의 권한 중 UPDATE 권한을 다름 사람에게 부여할 수 있는 권한과 이미 다른 사람에게 부여했던 권한도 함께 취소하며, 임꺽정 자신은 UPDATE 권한을 유지한다.

저작자표시 동일조건

'DB' 카테고리의 다른 글

[오라클 DB] DB접속 및 DCL  (0) 2021.01.05
[오라클 DB] 설치 및 접속  (0) 2017.07.08
SQL injection (SQL 삽입)  (0) 2017.06.01
MySQL 기초 공부  (0) 2017.05.30
SQL Server 2014 설치방법  (0) 2017.03.17
728x90

SQL injection


SQL 삽입은 데이터베이스를 엑세스하기 위해 동적 SQL 문을 사용하는 경우, 사용자로부터 입력된 값이 검증 작업없이 쿼리문을 생성하기 위한 일부로 사용할 때 발생한다. (동적 SQL 문이란 외부 입력값을 이용하여 SQL문을 실행하는 것을 말한다.)


모든 종류에 DBMS에 적용 가능한 공격기법으로, 이 공격을 통해 불법적인 로긴과 DB 엑세스, 프로시저를 통한 운영체제 명령어 수행이 가능해지므로 치명적 피해를 입을 수 있다.



비 정상적 입력값으로 인증 우회


사용자명 : ' or 'a'='a

비밀번호 : ' or 'a'='a





Error Based SQL Injection Check(에러 기반 SQL 삽입 체크)


서버에서의 에러 메시지가 브라우저가 그대로 출력된다면 공격자에게 많은 정보를 제공하게 된다.

MS-SQL로 가지고 이를 테스트해보겠다.


예 1) 싱글 쿼터( ' )를 사용했을 때의 에러 메시지

입력 값 : test'


에러 메시지 :

문자열 'test''의 따옴표가 짝이 맞지 않습니다.

'test'' 근처의 구문이 잘못되었습니다.



예 2)  테이블 명 추출 (집계함수를 가지고 조건 비교를 할 때 사용하는 having절 사용)

입력 값 : ' having 1=1 --


에러 메시지 : 

열 'tbl_user.name'이(가) 집계 함수나 GROUP BY 절에 없으므로 SELECT 목록에서 사용

할 수 없습니다.


select 문의 컬럼에 대한 GROUP BY절이 기술되어 있지 않을 때 위와 같은 에러가 발생한다.

위 에러 메시지에서 테이블 이름이 tbl_user이며 첫번째 칼럼 이름이 name인 것을 확인 가능하다.


예 3) DB 이름 추출
입력 값 : ' and db_name() =1 --

에러 메시지 :
nvarchar 값 'ADO'을(를) 데이터 형식 int(으)로 변환하지 못했습니다.

DB 이름과 숫자 1을 비교하도록 쿼리문을 조작해 에러 메시지를 발생 시킨다. 이를 통해 DB 이름이 ADO라는 것을 확인 가능하다.




SQL 인젝션을 방지하는 방법

C# 서버 기준 -  SqlParameter를 사용
SqlCommand cmd;

SqlDataReader sqlReader = null;

cmd.CommandText = @"SELECT * FROM tbl_user WHERE name=@userName AND pass=@password";
cmd.Parameters.AddWithValue("@userName", field[0]);
cmd.Parameters.AddWithValue("@password", field[1]); 
                                       
sqlReader = cmd.ExecuteReader();
로그인 완료 후나, 아이디 비밀번호 불일치 시에는 sqlReader를 닫고 parameter를 clear해준다.

sqlReader.Close();    
cmd.Parameters.Clear();

"SQLSERVER SP 개체이름이 잘못되었습니다" 오류가 날 때 해결법
: You need to refresh the Intellisense cache (Ctrl + Shift + R). Or you can go to Edit -> IntelliSense -> Refresh Local Cache.


C# 서버 기준 - 저장 프로시저 사용

SQL-SERVER에서의 저장 프로시저

1
2
3
4
5
6
7
8
CREATE PROCEDURE FindName
( @name nchar(10) )
AS  
BEGIN
   DECLARE @sql nvarchar(255)
   SET @sql = 'SELECT * FROM tbl_user WHERE name = @userName'
   exec sp_executesql @sql, N'@userName nchar(10)', @name
END;
cs


C# 서버의 코드
1
2
3
4
cmd.CommandText = "FindName";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter uInput = new SqlParameter("@name", field[0]);
cmd.Parameters.Add(uInput);
cs


C# 서버 기준 - 특수 문자 필터링

정규식을 사용해 특수문자를 필터링한다. (3~8자로 제한)
1
2
3
4
5
6
 bool idChecker = Regex.IsMatch(field[0], @"[0-9a-zA-Z]{3,8}$");
 
if (idChecker == false)
{
    return; //Console.WriteLine("잘못된 아이디 형식 입니다.");
}
cs


저작자표시 동일조건

'DB' 카테고리의 다른 글

[오라클 DB] 설치 및 접속  (0) 2017.07.08
데이터 베이스 기초  (0) 2017.06.14
MySQL 기초 공부  (0) 2017.05.30
SQL Server 2014 설치방법  (0) 2017.03.17
MySQL 설치 및 기초  (0) 2017.03.06
728x90


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
create database iot;    -- db생성
show databases;    -- db 보여주기
use iot;    -- 사용할 db 선택
show tables;    -- 현재 선택된 db의 테이블 보여줌
 
create table dept (    
deptno int(2) primary key,    -- 칼럼명/type/제약조건
dname varchar(20) not null,
loc varchar(20)
) ENGINE = InnoDB;
desc dept; -- table 스키마 보기
 
create table emp(
empno int(4) primary key,
ename varchar(10) not null,
job varchar(20) not null,
mgr int (4),
hiredate date, -- 입사일
sal int(8),    -- 연봉
comm int(8),    -- 커미션
deptno int(2),    -- 부서 번호
FOREIGN KEY fk_dept(deptno) 
REFERENCES dept(deptno)
) ENGINE = InnoDB; -- dept테이블의 deptno칼럼 참조
 
desc emp;
 
-- 관계된 테이블의 데이터 없이 참조하려고 하면 error
-- insert into emp values(1111, 'aaa', 'aaa', 1111, '2017/05/30', 3000, null, 10); 
 
insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'SALES', 'CHICAGO');
insert into dept values(40, 'OPERATING', 'BOSTON');
 
select * from dept;
desc emp;
 
insert into emp values(7369, 'SMITH', 'CLERK', 7902, 1980/12/17, 800, null, 20);
insert into emp values(7499, 'ALLEN', 'SALESMAN', 7698, '1981/02/20', 1600, 300, 30);
insert into emp values(7521, 'WARD', 'SALESMAN', 7698, '1981/02/22', 1250, 500, 30);
insert into emp values(7566, 'JONES', 'MANAGER', 7839, '1981/04/02', 2975, null, 20);
insert into emp values(7654, 'MARTIN', 'SALESMAN', 7698, '1981/09/28', 1250, 1400, 30);
insert into emp values(7698, 'BLAKE', 'MANAGER', 7839, '1981/05/01', 2850, null, 30);
insert into emp values(7788, 'SCOTT', 'ANALYST', 7566, '1987/04/19', 3000, null, 20);
insert into emp values(7839, 'KING', 'PRESIDENT', null, '1981/11/17', 5000, null, 10);
insert into emp values(7844, 'TURNER', 'SALESMAN', 7698, '1981/09/08', 1500, 0, 30);
insert into emp values(7876, 'ADAMS', 'CLERK', 7788, '1987/05/23', 1100, null, 20);
insert into emp values(7900, 'JAMES', 'CLERK', 7698, '1981/12/03', 950, null, 30);
insert into emp values(7902, 'FORD', 'ANALYST', 7566, '1981/12/03', 3000, null, 20);
insert into emp values(7934, 'MILLER', 'CLERK', 7782, '1982/01/23', 1300, null, 10);
insert into emp values(7782, 'CLARK', 'MANAGER', 7839, '1981/06/09', 2450, null, 10);
 
select * from emp;
 
insert into dept values(10, 'aaa', 'aaa'); -- 기본키 중복 error
insert into dept values(50, null, 'aaa'); -- dname은 not null이므로 error
insert into dept values(50, 'aaa', 'aaa'); -- error없이 정상 삽입
 
select * from dept where loc='NEW YORK';
select deptno from dept;
 
SELECT @@AUTOCOMMIT;  -- 오토커밋 여부를 조회
set autocommit=FALSE; -- 0으로 설정(0, FALSE: 해제 / 1,TRUE : 활성화)
 
delete from dept where deptno=50;
-- commit; 
 
select * from dept;
rollback; 
 
cs


저작자표시 동일조건

'DB' 카테고리의 다른 글

데이터 베이스 기초  (0) 2017.06.14
SQL injection (SQL 삽입)  (0) 2017.06.01
SQL Server 2014 설치방법  (0) 2017.03.17
MySQL 설치 및 기초  (0) 2017.03.06
SQLD 관련 자료 & 사이트  (0) 2017.03.05
728x90

SQL Server2014 설치법.pdf


서버 이름

210.119.12.79,1617


로그인

sa


비밀번호

i**7



c# 데이터베이스 관련

SQL 프로그래밍 배우기 : http://www.sqlprogram.com/Default.aspx

C# 데이터 처리 http://www.csharpstudy.com/Data/Data.aspx

C# MongoDB 활용 예제 : http://www.csharpstudy.com/Practical/Prac-mongodb.aspx




성적처리(ms-sql 작성중)


StudentManager.7z



저작자표시 동일조건

'DB' 카테고리의 다른 글

데이터 베이스 기초  (0) 2017.06.14
SQL injection (SQL 삽입)  (0) 2017.06.01
MySQL 기초 공부  (0) 2017.05.30
MySQL 설치 및 기초  (0) 2017.03.06
SQLD 관련 자료 & 사이트  (0) 2017.03.05
728x90

apm setup 설치

http://software.naver.com/software/summary.nhn?softwareId=MFS_111815

 

 

root password는 apmsetup (변경가능)

 

 

MySQL monitor 실행 (cmd또는 apm setup모니터에서 실행)

 

1
mysql -u아이디 -p비밀번호
1
mysql -h호스트주소 -p포트번호 -u아이디 -p비밀번호

 

 

C:\Users\PKNU>mysql -uroot -p    // -u는 user명 -p는 password인데 보안상 바로 입력하지 않고 다음줄에서 입력하는 것이 좋다.
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.1.41-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 

 

SQL문은 세미콜론으로 구분, 키워드는 대소문자 구분이 없다.

 

 

MySQL Workbench 를 통해 실행한 결과 (https://dev.mysql.com/downloads/workbench/

 

 

// 명령어 입력도중 취소하려면 \c 입력 

mysql> select
    -> user()
    -> \c    // ->는 다음 줄을 기다린다는 의미
mysql>    // 새 명령을 받아들일 준비가 되었음

 

 

//  데이터베이스 목록 보기 (show) 

1
SHOW DATABASES;


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| phpmyadmin         |
+--------------------+
3 rows in set (0.00 sec)

mysql>

 

 

// 데이터베이스 생성 및 선택

생성

1
CREATE DATABASE `데이터베이스명` CHARACTER SET utf8 COLLATE utf8_general_ci;

선택

1
USE `데이터베이스명`


mysql> create database rutel;    // rutel 데이터베이스 생성
Query OK, 1 row affected (0.00 sec)


mysql> use rutel;                    // rutel 데이터베이스 선택
Database changed
mysql>

 


// mysql 시작할 때, 데이터베이스(rutel)를 선택

C:\Windows\System32>mysql -hlocalhost -uroot -p rutel
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.1.41-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



// show를 통해 rutel 데이터베이스가 존재함을 볼 수 있다.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| rutel               |
| mysql              |
| phpmyadmin         |
+--------------------+
4 rows in set (0.00 sec)



// table 보기

1
SHOW tables;


mysql> show tables;

Empty set (0.00 sec)


데이터베이스의 설계

DB는 테이블들의 그룹이라고 할 수 있다.


pet table

애완동물 이름(name), 소유주(owner), 종(species), 암수(sex), 출생일(birth, death)


나이 대신 출생일을 속성으로 두는 이유?

: 나이는 계속 변하며, 출생일은 고정. 출생일을 통해 생일 이벤트, 사망일을 통해 수명을 알 수 있다.

데이터 베이스에선 설계단계에서부터 이러한 것들을 고려하여야 한다.


// 테이블 생성 (varchar, char, date는 자료형)

mysql> create table pet (name varchar(20), owner varchar(20),

    -> species varchar(20), sex char(2), birth date, death date);

Query OK, 0 rows affected (0.01 sec)


CREATE TABLE score (    

    학번  tinyint NOT NULL ,

    성명  varchar(4) NOT NULL ,

    국어  tinyint NOT NULL ,

    영어  tinyint NOT NULL ,

    수학  tinyint NOT NULL ,

    총점  tinyint NOT NULL ,

    평균  tinyint NOT NULL ,

    등급  char(2) NOT NULL ,

    PRIMARY KEY (학번)

);



varchar는 가변길이 문자열을 뜻한다.


//  테이블 스키마 열람 (describe또는 desc로 줄여써도 된다)

1
DESC `테이블명`

스키마 : 테이블에 적재될 데이터의 구조와 형식을 정의 하는 것


데이터 타입 목록


Var type은 입력받은 데이터 만큼만 공간을 차지하며 유연하며, 공백도 하나의 문자로 취급한다. ('AA' != 'AA ')

고정 문자는 검색의 성능면에서 유리한 장점이 있다고 하지만 Var type이 크게 성능에 영향을 주는건 아니고 유연하기에 Var type을 주로 사용한다.

 (참고 : http://okky.kr/article/217655)


길이가 절대 바뀔 일이 없는 데이터는 CHAR를 사용할 수도 있겠다. (예 : 주민번호)


enum은 열거형으로, 성별같이 정해진 값을 강제할 수 있다.


name, owner...와 같은 컬럼들을 갖는 테이블을 볼 수 있다.


// 레코드 입력하기(Insertion of records)

mysql> insert into pet

    -> values('Ludwig', 'rutel', 'dog', 'm', '2017-03-07', NULL);

Query OK, 1 row affected (0.00 sec)



// SELECT 질의로 검색하기

select * from pet;




// UPDATE 구문을 통한 데이터 수정

mysql> UPDATE pet

    -> SET species = "cat"    // 종을 cat으로 변경

    -> WHERE owner = "rutel";    // 소유자가 "rutel"인 것을 찾아

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0    // match되는 레코드가 한 건, 변경된 레코드가 1건, 경고(문제) 없음





// 테이블 데이터 제거 (테이블 구조를 제거하는 것이 아니다)

mysql> DELETE FROM pet;

저작자표시 동일조건

'DB' 카테고리의 다른 글

데이터 베이스 기초  (0) 2017.06.14
SQL injection (SQL 삽입)  (0) 2017.06.01
MySQL 기초 공부  (0) 2017.05.30
SQL Server 2014 설치방법  (0) 2017.03.17
SQLD 관련 자료 & 사이트  (0) 2017.03.05
728x90


구루비 SQLP 연구회 - http://wiki.gurubee.net/pages/viewpage.action?pageId=27426887



KDB 시험예제

SQL자격검정시험 예제(2010 10).pdf


요약 정리 - http://blog.naver.com/liberty264/220567987579



SQL 전문가 되어보기 WikiDocs - https://wikidocs.net/book/159



시험 후기

http://blog.naver.com/senticri/220240528846

http://118k.tistory.com/170



Study_Room_Doc_02_SQLD_예상문제(기출21회참고).pdf

Study_Room_Doc_02_SQLD_예상문제해답(기출21회참고).pdf


저작자표시 동일조건

'DB' 카테고리의 다른 글

데이터 베이스 기초  (0) 2017.06.14
SQL injection (SQL 삽입)  (0) 2017.06.01
MySQL 기초 공부  (0) 2017.05.30
SQL Server 2014 설치방법  (0) 2017.03.17
MySQL 설치 및 기초  (0) 2017.03.06

+ Recent posts

티스토리툴바