1.SQL 관련 용어정리

 

1. SQL ; 관계형 DB에서 데이터를 다루기위해 나온 언어.

- 첫이름 : SEQUEL(Structured English QUEry Language)

- ANSI/ISO가 표준화(DBMS가 달라도, 적응하기 쉽다)

- 하나의 개발 언어.

- ;(세미콜론) 단위로 독립 됨.

- 수학의 집합논리가 베이스; -> SQL은 데이터를 집합으로 취급함.

-> 관계형 DB도 데이터를 집합으로 취급함.

 

2. SQL문장

; TABLE 관련 된 것이 있고/ 데이터 권한을 제어하는 것도 있다.

-> 조회 : SELECT

-> 데이터 변경 : UPDATE, DELETE, INSERT

 

SQL명령어를 DDL,DML,DCL,TCL로 나누는 것

-> SAVEPOINT 그룹으로 나눈다고 한다.

-> TCL이 DCL에 포함 될 수도 있다.

 

3. TABLE

; 특정 주제와 목적으로 만들어지는 집합.

-> 엑셀로 데이터를 저장한 것이라 생각하자.

-> 2차원 구조(row * column)이다.

-> Object(객체 ; 현실을 표현한 것)다.

-> 관계형 데이터베이스의 기본단위이다.

-> 행(row)는 없어도 되지만, 열(column)은 있어야한다.

=> column이 데이터를 분류하는 기준이기 때문.

.

필드(field): 셀(cell)을 필드라고 한다.

=> 테이블 안의 데이터를 드래그해서 블록설정 했을 때, 그 부분을 필드 라고 생각하면 된다.

 

4. 정규화(Normalization)

; 테이블을 분할하여, 원하는 정보만 뽑는 것

; 중복되는 정보를 뽑지 않는 것.

 

- 위의 테이블을, 아래 2개로 나눠서 저장하고, 키로 연결하는 방식을 정규화라고 한다. 이것이 관계형의 기본 방식이다.

 

 

================ 좀 더 알아볼 부분 ==============

 

- 구단 정보를 보자고 할 때, 선수이름에 관련정보는 필요없다.

반대로 선수정보를 보자고 할 때, 팀이랑, 연고지가 궁금하지 않을 수 있다.

-> 주제에 맞는 정보만(쓸데없는 정보를 중복하지않고) 뽑는 것이 정규화이다.

 

 

4.1 기본키(Primary Key)

: 모든 행에 대해서, 중복되지 않아서 전체적으로 구별할 수 있는 값.

ex)주민번호, 사번, 학번, 군번 등....

 

4.2 외부키(Foreign Key)

: 테이블 간에 연결되는 column

 

개개로 쪼개진 테이블이 외부키로 연결되고,

외부키를 통해서 하나의 테이블로 조립되었다면.

기본키로 정렬했을 때, 중복되는 값이 없어야 한다.

 

데이터의 구분 기준은 기본키이고,

외부키를 통해서, 테이블을 쪼갤 수 있다.

 

-> 외부키만 놓고 봤을때,

일반 프로그래밍 언어로 따지자면,

외부키는 포인터 같은 개념이다.

주소 참조를 통해서 불러오는 느낌.

 

 

================ 좀 더 알아볼 부분 ==============

 

관계형 데이터베이스

-키

슈퍼키 : 유일성을 만족하는 것.

후보키 : 유일성 + 최소성 만족

기본키 : 후보키 + 중복X, NULL X

대체키 : 기본키를 제외한 나머지 후보키

외부키 : 기본키를 참조하는 키.

 

 

 

 

 

무결성 : DB의 값과, 현실의 정보가 일치하는 것.

NULL/고유/domain/키/관계

무결성 제약 조건(Constraint) : 무결하지 않은 데이터의 저장을 방지하는 조건.

관계대수

순수관계연산자.

- SELECT

- PROJECT

- JOIN

- Division : 피제수의 모든 tuple의 교집합을 보고, 그것과 연관된 것을 제수에서 고르기.

 

출처 : http://www.jidum.com/jidums/view.do?jidumId=168

일반집합연산자

- UNION

- Intersection

- Difference

- Cartesian product

관계해석 : 관계 데이터의 연산을 표현하는 방법

 

 

 

만일 mssql을 하신다면 ms에서는 프로시저의 변수명 앞에 @(골뱅이)를 붙인답니다.

아마도 프로시저의 변수명으로 테이블을 대처한다는 뜻 같아 보이네요.

참고하세요.

 

 

 

 

2. SQL(Structured Query Language)

2.1 DDL[Data Definition Language]

; 스키마,도메인,테이블,뷰,인덱스를 다루는 언어

; 메타데이터로, 시스템 카탈로그에 저장한다.

2.1.1CREATE : 정의한다.

 CREATE SCHEMA : 하나의 사용자에 속하는 테이블/기타 구성요소를 그룹짓는 것.

EX) CREATE SCHEMA 대학교 AUTORIZATION 홍길동;

: ID가 홍길동의 스키마 대학교를 정의.

 CREATE DOMAIN : 도메인(속성이 취할 수 있는 값의 집합)을 정의.

도메인명은 데이터 타입처럼 사용한다.

EX)

CREATE DOMAIN SEX CHAR(1)

DEFAULT ‘남’

CONSTRAINT VALID-SEX CHECK (VALUE IN(‘남’, ‘여’)

SEX도메인은 문자형, 기본값 ‘남’, ‘남,여’ 중 하나만 저장 가능.

 CREATE TABLE : 테이블을 정의하는 명령문

- EX)

CREATE TABLE 학생

(이름 VARCHAR(15) NOT NULL,

학번 CHAR(8), 전공 CHAR(5), 성별 SEX, 생년월일 DATE

PRIMARY KEY(학번),

UNIQUE

FOREIGN KEY(전공) REFERENCES 학과(학과코드)

ON DELETE SET NULL

ON UPDATE CASCADE

CONSTRAINT 생년월일제약 CHECK(생년월일 >='1980-01-01’));

 CONSTRAINT를 쓰고 마지막에 FOREIGN KEY를 정의 할 수 도 있음.

 CREATE VIEW : 가상 테이블, 데이터를 제한적으로 보여주기 위한 것.

 삽입, 삭제, 갱신은 제약을 받는다.

 SELECT문을 서브쿼리로 사용하여 만든다.

EX) CREATE VIEW 뷰명 AS SELECT;

 CREATE INDEX

EX) CREATE [UNIQUE] INDEX 고객번호_idx ON 고객(고객번호 DESC);

 Unique : 중복이 없게

 Cluster : 튜플들을 그룹으로 지정하는 옵션.

 CREATE TRIGGER : DB에서 이벤트 발생 마다 자동수행 되는 사용자정의 프로시저

무결성 제약조건 구현, 관련 테이블의 데이터 일치에 사용한다.

동작시기, 테이블종류 등의 옵션이 있다.

 변화에 따라 자동으로 데이터 변경 할 수 있는 기능이다.

동작시기 옵션 : after; 테이블 변경 후, before ; 테이블 변경 전 TRIGGER가 실행

동작 옵션 : INSERT, DELETE, UPDATE 테이블이 해당 동작을 할 때 TRIGGER가 실행

NEW/OLD : 적용 테이블 옵션, NEW = 추가되는 OLD = 변경되는

SET명령어 : 값 치환.

2.1.2 ALTER : 테이블의 정의 변경

ADD : 새로운 ‘속성’을 추가한다.

ALTER : ‘속성’의 기본값(Default)을 변경

DROP COLUMN : ‘속성’을 제거

EX) ALTER TABLE 학생 ADD 학년 VARCHAR(3);

2.1.3 DROP : 제거한다.

SCHEMA/DOMAIN/TABLE/VIEW/INDEX/TRIGGER/CONSTRAINT

EX) 학생테이블과 참조하는 모든 데이터 제거

DROP TABLE 학생 CASCADE;

2.2 SELECT : 조건을 만족하는 튜플을 검색하여, 임시테이블로 구성하라.

SELECT [PREDICATE] FROM [WHERE] [GROUP BY] [HAVING] [ORDER BY]

PREDICATE : 검색 대상 튜플을 제한하는 부분.

ALL : 기본값, 모든 튜플

DISTINCT : 중복 튜플 제외

DISTINCTROW : 튜플 전체를 대상으로 중복 된 건 제외.

속성명 : 2개 이상일 때는, 테이블명.속성ㅁ여으로 사용한다.

AS : 속성, 연산에 명칭을 부여하기 위함

GROUP BY : 대상 속성을 기준으로 그룹화 하여 검색.

HAVING : 그룹에 대한 조건.

ORDER BY [ASC|DESC] : 속성을 기준으로 정렬.

연산

우선순위 : 산술 > 관계 > 논리

조건연산 : = > < >= <= 는 동일, 같지않다 : <>

논리연산 : NOT, AND, OR

LIKE 연산 :

- * , % : 모든 문자를 대표함

- ? , _ : 문자 하나를 대표함

- # : 숫자 하나를 대표함.

그룹함수

- COUNT : 그룹별 튜플 수

- MAX : 그룹별 최대값

- MIN : 그룹별 최소값

- SUM : 그룹별 합계

- AVG : 그룹별 평균.

Trim : 공백 제거 함수.

2.2.1 조건검색 : SELECT [PREDICATE] FROM [WHERE] [GROUP BY] [HAVING] [ORDER BY]

WHERE 속성 = ‘값’ : 속성이 해당하는 값인 튜플 검색.

WHERE LIKE ‘김%’ ; 첫 글자가 김인 모든 튜플 검색.

WHERE BETWEEN A AND B : A와 B사이 값인 튜플 검색.

NULL 사용 : IS NOT NULL => 값이 입력된 자료만 검색.

2.2.2 정렬

ORDER BY 앞의 값을 먼저 정렬하고, 개개를 그룹화하여 뒤의 것 정렬

2.2.3 그룹검색

그룹이 나오면 일반검색처럼 SELECT 부서, 이름, 생일 이렇게 쓸 수 없다.

 그룹에 해당하는 것만 사용 가능.

2.2.4 하위질의

Ex)

SELECT 이름, 주소

FROM 사원

WHERE 이름 IN (SELECT 이름 FROM 여가활동 WHERE 취미 = ‘나이트 댄스’);

1. 먼저 WHERE절 안의 쿼리로, 전체 쿼리의 검색 대상을 줄여놓는다.

여가활동 TABLE에서 취미 = 나이트 댄스인 대상의 이름 찾기.

2. 사원 테이블에서, 아까 찾아놓은 이름인 대상의 이름과 주소를 뽑는다.

 SELECT 이름, 주소 FROM 사원 WHERE 이름 IN 성춘향으로 정리된다.

2.2.5 복수 테이블 검색

사원TABLE과 여가활동TABLE을 동시에 검색하여 자료를 뽑는다.

EX) SELECT 사원.이름 사원.부서, 여가활동.취미, 여가활동. 경력

FROM 사원, 여가활동 WHERE 여가활동.경력 >=10 AND 사원.이름 = 여가활동.이름

2.2.6 UNION 질의 : 합집합으로 조회하는 것이다. 위의 복수 테이블 검색과 다르게, 중복은 제거된다.

EX) SELECT * FROM (임원 UNION SELECT * FROM 직원);

 임원과 직원을 UNION한디 SELECT한다.

2.3. JOIN : 2 TABLE의 연관된 튜플 결합. => 새로운 RELATION 반환.

; 관계형 DB는 여러 개의 TABLE로 분리될 수 밖에 없다. 그래서 필수적인 연산이다.

2.3.1 INNER JOIN

2.3.1.1 EQUI JOIN(조인조건 ‘=’)

: 공통 속성을 기준으로 같은 값을 가지는 행을 연결.

; 공통속성 => ‘JOIN속성’ 이라 부른다.

; NATURAL JOIN : 동일 속성 2번 나오면 중복 제거.

EX) 학생과, 학과에서 학과코드가 같은 것을 JOIN하여 출력

1. SELECT 학번, 이름, 학생.학과코드 , 학과명

FROM 학생, 학과

WHERE 학생.학과코드 = 학과. 학과코드;

2. SELECT 학번, 이름, 학생.학과코드, 학과명

FROM 학생 NATURAL JOIN 학과;  그럼 외래키인걸 알아야겠네

3. SELECT 학번, 이름, 학생.학과코드, 학과명

FROM 학생 JOIN 학과 USING(학과코드)

2.3.1.2 NON EQUI JOIN

: JOIN 조건 >,<.<>, >=, <= 를 사용 => 실무에서 거의 안쓴다.

2.3.2 OUTER JOIN : INNER조인의 연산이 선행된다. 이후 추가 필터를 적용한 것.

2.3.2.1 LEFT : 우측과 맞지 않는 좌측의 튜플에 null을 붙임

2.3.2.2 RIIGHT : 좌측가 맞지 않는 우측의 튜플에 null을 붙임

2.3.2.3 FULL: 좌우 모두에 맞지 않는 튜플에 null을 붙임.

 

LEFT, RIGHT는 조건문에 반대 항에 (+)를 붙인다.

FULL은 FULL OUTER JOIN이라고 명령을 넣어준다.

2.3.3. SELF JOIN : 1개의 테이블을 2개의 이름으로 사용. EQUI JOIN의 일종.

3.DML(D ata Manipulation Language)

: 저장된 데이터를 실질적으로 관리하는 것.

QUERY는 DDL, DML, DCL 모두에서 사용하는 ‘기능’이다.

3.1 INSERT : 새로운 튜플 삽입

EX) INSERT INTO 사원(이름,부서) VALUES (‘이순신’, ‘기획’);

: 순서대로 대응된다. 이름이 이순신, 부서가 기획으로 들어간다.

3.2 DELETE: 튜플 삭제

테이블 내의 ‘튜플만 삭제함’을 유의하자. => 테이블 제거는 DROP사용

EX) DELETE FROM 사원 WHERE 이름 = ‘임꺽정’;

3.3 UPDATE: 튜플 내용 갱신.

EX) UPDATE 사원 SET 부서 = ‘기획’, 기본급=기본급+5 WHERE 이름 = ‘황진이’;

4.DCL(Data Control Language)

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

: DBA가 관리용으로 사용.

4.1 COMMIT : 작업을 영구적으로 반영. 완료

정상적으로 작업이 완료되면 COMMINT 해야한다.

4.2 ROLLBACK: 비정상 종료시에 원상복구

트랜젝션이 비정상적으로 끝나면, 상태가 불안하니 무조건 ROLLBACK해야한다.

4.3 GRANT : 사용권한 부여

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

4.4 REVOKE : 사용권한을 취소

REVOKE 사용자등급 FROM 사용자_ID_리스트;

GRANT RESOURCE TO NAVI : 나비에게 DB & TABLE 생성 권한 부여.

GRANT CONNECT TO STAR : SATR에게 검색 권한 부여.

REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR;

UPDATE권한을 다름사람에게 부여하는 권한 취소

RESOURCE : DB & TABLE 생성 권한

CONNECT : 단순 사용자.

4.뷰(VIEW) ; 기본테이블에서 유도 된 테이블

- 필요한 데이터만 처리.

- 시스템에 저장 후, 뷰를 쿼리에서 사용하면, 기본테이블이 뷰로 나온다.

- 상수,계산식, 그룹함수(DISTINCT, GROUP BY, HAVING)로 만들어지면 해당 뷰는 변경 불가.

- 둘 이상 테이블에서 유도된 건 변경 불가.

 

 

4.1 CREATE VIEW: 뷰 정의

CREATE VIEW DB_학생 AS SELECT 학번, 이름, 학과 FROM 학생

WHERE 학과 = ‘컴퓨터공학과’ WITH CHECK OPTION

컴공과 학생의 학번,이름,학과 속성의 뷰를 만든다.

4.2 DROP VIEW ; 뷰 제거

DROP VIEW 뷰명 {RESTRICT | CASCADE};

RESTRICT : 다른 곳에서 참조하면 제거 취소

CASCADE : 연관되어 있으면 해당하는 것들 모두 제거

 

5.내장 SQL : 응용프로그램 안에 SQL문을 삽입한 것.

-> 호스트 프로그램 언어에서 실행문이 나타나는 어느곳에서나 사용 가능하다.

-> 맨 처음 튜플 하나만 반환.

- 호스트의 변수와, DB의 필드 이름은 같아도 됨.

- 호스트의 DATATYPE은 대응하는 SQL 데이터 타입과 일치해야함.

- 실행되면, SQL실행상태가 SQL상태변수에 전달됨.

5.1 C/C++ : EXEC SQL ~ ; 사이에 기술

5.2 JAVA : #SQL{<내장 SQL문>};

-내장SQL안에서, 호스트 변수는 앞에 ‘:’를 붙인다.

5.3 커서(CURSOR)

- 내장 SQL의 여러 개 결과 튜플을 접근할 수 있게 하는 것.

- 튜플에 대한 포인터

DECLARE : 커서 정의, 커서 관련 선언

OPEN : 쿼리의 첫번째 튜플을 가리키도록 설정

FETCH : 다음 튜플로 커서 이동.

CLOSE : 커서를 닫기.

6. 저장 프로시저(STORED PROCEDURE)

- 특정 작업을 하는 SQL을 묶어 놓은 것. == 프로그램의 축소판

- 연속된 SQL을 하나로 모아 SQL 서버에 미리 컴파일, 저장해 놓은 것.

- 호출을 통해, 복잡한 SQL의 일괄작업 처리에 적합.

-

6.1 선언부 : 타입,커서,상수,변수, 서브 프로그램 선언.

6.2 실행부 : 코드 실행 제어, 데이터 조작.

6.3 예외처리부 : 프로시저 실행중 발생하는 예외를 처리하는 곳.

이득

1. 한번 만들면 재사용 가능.

2. 원본 코드와 상관없이 수정 가능(????)

3. 빠른 SQL실행시간( 1. 만들어 질 때, 자동 최적화, 2. 메모리에 캐시 됨)

4. 보안성 : 접근ㄴ권한을 저장프로시저에 국한 시킬 수 있다.

5. 네트웤 통신량 감소 : 실행문 하나만 보내서 동일한 작업 가능.

 

 

 

6.4 생성

CREATE [OR REPLACE] PROCEDURE 프로시저명 (파라미터)

[지역변수 선언]

프로시저 BODY;

- OR REPLACE : 동일한 프로시저 이름이 존재하면 대체할 수 있다(덮어쓴다는 거?)

- 파라미터(IN/OUT/INOUT) : IN은 프로시저에 전달, OUT 프로시저에서 반환, INOUT은 둘다필요하다는 설정.

- BODY : 본문. BEGIN~ END 로 구성. 적어도 1개의 SQL 필요. 값 치환시 SET예약어 사용.

6.5 제거

DROP PROCEDURE [프로시저명]

6.6 실행

EXCUTE 프로시저명 ; 혹은 EXEC 프로시저명;

http://hklovecw.tistory.com/7

 

 

 

출처 : http://www.gurubee.net/lecture/2367