03.11~03.13) 2-1장.SQL기본

제 1절 관계형 데이터베이스 개요

1.데이터베이스

• =>넓은 의미에서의 데이터베이스는 이러한 일상적인 정보들을 모아놓은것 자체를 의미하고 일반적으로 데이터베이스라고 말할 때는 특정기업이나 조직 쪼는 개인이 필요에 의해(부가가치가 발생하는) 데이터를 일정한 형태로 저장해 놓은 것을 의미한다.

• =>많은 사용자들이 보다 효율적인 데이터의 관리 뿐만 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고, 필요시 필요한 데이터를 복구하기 위한 강력한 기능의 소프트웨어를 필요로 하게 되었고 이러한 기본적인 요구사항을 만족시켜주는 시스템을 DBMS(Database Management System)라고 한다.

• 데이터베이스의 발전

• 1960년대 : 플로우차트 중심의 개발-파일구조로 저장/관리

• 1970년대 : DB관리기법이 처음태동되던 시기-계층형(Hierarchical)/망형(Network) 데이터베이스 제품이 상용화

• 1980년대 : 관계형 데이터베이스가 상용화(Oracle,Sybase,DB2)

• 1990년대 : 인터넷 환경의 급속한 발전과 객체지향 정보를 지원하기 위해 객체 관계형 데이터베이스로 발전

• 관계형 데이터베이스(Relational Database)

• 1970년대 영국의 수학자였던 E.F. Codd박사의 논문에서 처음으로 소개된 이후, 개발단계를 거쳐, Oracle을 선발로 상용화되었다. 이후 여러장점으로 데이터베이스 대부분을 대체하며 주력제품으로 급상승 됨. 현재는 객체 관계형 데이터베이스를 사용하고 있지만 대부분 관계형 데이터베이스구조로 저장이 되고 관계형데이터베이스를 유일하게 조작할 수 있는 SQL문장에 의해 관리되고 있으므로 중요함.

• 파일시스템 :동시처리가 불가능->여러개 복사해 사용->데이터의 불일치성이 발생->정합성보장이 힘들고 과다노력필요

• 이러한 문제에 대해 관계형 데이터베이스는 정규화를 통한 합리적인 테이블 모델링을 통해 이상(ANOMALY)현상을 제거하고 데이터 중복을 피할 수 있으며, 동시성 관리, 병행 제어를 통해 많은 사용자들이 동시에 데이터를 공유 및 조작할 수 있는 기능을 제공하고 있다. 또한 메타 데이터를 총괄관리 할수 있기 때문에 데이터의 성격,속성 또는 표현방법등을 체계화/데이터표준화를 통한 데이터품질 확보+보안 기능으로 데이터 무결성(Integrity) 보장+회복/복구 기능제공

2.SQL(Structured Query Language)

• =>SQL(Structured Query Language)은 관계형 데이터베이스에서 데이터 정의, 데이터 조작, 데이터 제어를 하기 위해 사용하는 언어로 정확한 데이터를 출력하는 것이 목표로 SQL튜닝은 시스템에 큰 영향을 주는 SQL을 가장효과적(응답시간, 자원 활용 최소화)으로 작성하는것이 목표이다.

• =>1986년부터 ANSI/ISO를 통해 표준화되고 정의된 SQL의 기능은 벤더별 DBMS개발의 목표가 된다. 일부 용어는 다르더라도 대부분의 관계형 데이터베이스는 표준을 최대한 따르고 있기 때문에 다른 DB를 사용하더라도 재활용할수도 있고 이식성을 높이는데도 공헌 한다.

• =>SQL문장은 단순 스크립트(삽입해사용하는 언어)가 아니라 개발언어처럼 DBMS안에서의 독립된 하나의 개발언어이다. 오직 관계형 데이터베이스 전담언어로 세미콜론으로 분리된 SQL문장단위로 독립되어있다. 데이터를 집합으로서 취급하며 데이터를 조회하고 입력/수정/삭제하는 과정으로 사용자는 DB와 대화하게 된다.

•  

3.TABLE

• 데이터는 관계형 데이터베이스의 기본단위인 테이블형태로 저장된다. SQL로 원하는 자료를 꺼내올수 있는 어느 특정한 주제와 목적으로 만들어지는 일종의 집합이다. 삭제하지 않는 한 지속적으로 유지하며 존재한다.

• 테이블은 데이터를 저장하는 객체(Object)로서 관계형 데이터베이스의 기본단위로 칼럼과 행의 2차원구조로 모든데이터를 저장한다. 세로방향을 칼럼(Column) 가로방향을 행(Row)라고 하고 한칸의 공간을 필드(Field)라고 한다.

•  

•  

• 테이블을 분할하여 데이터의 불필요한 중복을 줄이는 것을 정규화(Normalization)이라고 하고, 데이터의 정합성 확보와 데이터 입력/수정/삭제시 발생할 수 있는 이상현상(Anomaly)을 방지하기 위해 매우 중요하다.

•  

• 각행을 구분할 수있는 한 개이상의 칼럼을 기본키(Primary Key)라고 하며, 다른 테이블의 기본 키로 사용되면서 테이블과의 관계를 연결하는 역할을 하는 칼럼을 외부키(Foreign Key)라고 한다.

•  

4.ERD(Entity Relationship Diagram)

•  

• 위 그림은 팀과 선수가 "소속"이라는 관계로 맺어져 있다. 테이블간 서로의 상관관계를 그림으로 도식화한 것을 E-R다이어그램이라고 하며, 간단히 ERD라고 한다. 구성요소로 엔터티(Entity), 관계(Relationship), 속성(Attribute) 를 가지고 현실세계의 데이터는 이 3가지 구성요소로 모두 표현이 가능하다.(고전적 ERD로 속성이 많으면 비효율적이다.그래서 쓰는게 IE/Barker표기법)

• 아래 그림은 앞으로 사용하게 될 K-리그 테이블관계를 IE(Information Engineering) 표기법과 Barker(Case*Method)표기법으로 표현한 ERD이다. (James Martin의 정보공학 표기법)

• 양방향 관계는 다음과 같다.

• 하나의 팀은 여러명의 선수를 포함할 수 있다.-한명의 선수는 하나의 팀에 꼭 속한다

• 하나의 팀은 하나의 전용 구장을 꼭 가진다.-하나의 운동장은 하나의 홈팀을 가질 수 있다.

• 하나의 운동장은 여러게임의 스케줄을 가질 수 있다.-하나의 스케줄은 하나의 운동장에 꼭 배정된다.

•  

•  

• IE : 실선은 식별관계/점선은 비식별 관계

• Baker : 수직바는 식별관계

•  

제 2절 DDL(DATA DEFINITION LANGUAGE)-테이블 생성/구조 변경

1.데이터 유형

• =>데이터 유형은 자료입력시 그 자료를 받아들일 공간을 자료의 유형별로 나누는 기준.

•  

• 문자열 유형의 중요한 것은 저장영역과 문자열의 비교 방법.

• varchar 유형은 가변길이라 필요한 영역은 실제 데이터 크기 뿐/저장측면에서도 char유형보다 작은영역에 저장할 수 있어 장점

• char유형과 varchar유형 비교 방법의 차이

• char에서는 문자열을 비교할때 공백을 채워서 비교하는데, 우선 짧은 쪽의 끝에 공백을 추가하여 2개의 데이터가 같은길이가 되도록 해 앞에서부터 한 문자씩 비교한다. 그래서 공백만 다른 문자열은 같다고 판단된다.('aa'='aa ')

• varchar유형에서는 맨 처음부터 한 문자씩 비교하고 공백도 하나의 문자로 취급함으로 끝의 공백이 다르면 다른 문자로 판단한다.('aa'!='aa ')

• char가 아닌 varchar, numeric유형에서 정의한 길이나 자릿수의 의미는 해당 데이터 유형이 가질 수 있는 최대한의 한계값을 정의한것

2.CREATE TABLE

• 테이블과 칼럼 정의

• 테이블에 존재하는 모든 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼이나 칼럼의 조합들(후보키) 중에 하나를 선정하여 기본키로 지정한다. 그리고 테이블과 테이블간에 정의된 관계는 기본키와 외부키를 활용해서 설정한다.

• CREATE TABLE

•  

• 생성시 주의해야 할 규칙

• 객체를 의미하는 적절한 이름 사용(가능한 단수형 권고)

• 다른 테이블 명과 중복되지 않아야한다.

• 한테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.

• 칼럼에 대해서는 다른테이블까지 고려하여 일관성있게 사용(데이터 표준화 관점)

• 테이블명과 칼럼은 반드시 문자로 시작해야 하고 벤더별로 길이에 대한 한계가 있다.

• 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸수 없다.

• A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

• 테이블 이름을 지정하고 각칼럼들은 과호 "( )" 로 묶어 지정한다(구문 형식)

• 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다(구문형식)

• 각칼럼은 콤마로 구분되고 테이블 생성문의 끝은 항상 세미콜론으로 끝난다(구문형식)

• 10_PLAYER : 반드시 숫자가 아닌 문자로 시작되어야 한다.

• T-PLAYER : 특수문자 -가 허용되지 않음

•  

•  

• SQL Server

• TINYINT-1bytes

• SMALLINT-2bytes

• 추가 주의사항

• 대소문자 구분을 하지 않는다.

• 기본적으로 테이블이나 칼럼명은 대문자로 만들어진다.

• DATETIME데이터 유형에는 별도로 크기를 지정하지 않는다.

• 문자데이터 유형은 반드시 가질 수 있는 최대길이를 표시해야한다.

• 칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.

• 제약조건(CONSTRAINT)

• =>사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 제약이다. 반드시 기술할 필요는 없지만 ALTER TABLE로 추가, 수정하는경우 입력상태로는 쉽지 않아 초기부터 검토

• 제약조건의 종류

•  

• 대체키

• 후보키

• //

• Null 의 의미

• 공백이나 숫자0과는 전혀 다른 값으로 데이터가 없을때의 공집합과도 다르다. NULL은 아직 정의되지 않은 미지의 값 이거나 현재 데이터를 입력하지 못하는 경우를 의미한다.

• DEFAULT 의 의미

• 데이터 입력 시에 칼럼의 값이 지정되어 있지 않을 경우 기본값을 사전에 설정할 수 있다. 지정하지 않은경우에 NULL값이 입력되고 DEFAULT값을 정의 했다면 해당칼럼에 사전에 정의된 기본값이 자동으로 입력된다.

•  

•  

• =>컬럼명과 함께 Constraint를 정의하면 컬럼레벨 방식이고, 맨마지막에 constraint를 정의하면 테이블레벨 방식이다.

• 생성된 테이블 구조 확인

• Oracle : DESC 테이블명;/DESCRIBE 테이블명;

• SQL Server : [exec] sp_help 'dao.테이블명' (exec 실행하기위한..)

• SELECT문장을 통한 테이블 생성 사례

• Oracle에서 Select문장을 활용해 테이블을 생성할 수 있는 방법으로 Create table ~ As Select가 있다. 칼럼별로 데이터 유형을 다시 재정의 하지 않아도 되는 장점이 있다. 단점으로는 기존 테이블의 제약조건중 NOT NULL만 새 복제 테이블에 적용이되고 나머지 다른 제약 조건들은 없어진다. 제약조건 추가를 위해 ALTER TABLE을 사용해야 한다.

• SQL Server에서는 Select ~ into~를 활용하여 같은 결과를 얻을 수 있다. 단, 칼럼속성에 Identity를 사용했다면 Identity속성까지 같이 적용이 된다.(유일값 + Not Null)

3.ALTER TABLE

• 칼럼을 추가/삭제/변경 하거나 제약조건을 추가/삭제/변경하는 작업을 한다.

• ADD COLUMN : 기존 테이블에 칼럼 추가

• ALTER TABLE 테이블명

  ADD 추가할컬럼명 데이터유형;

• 새롭게 추가된 칼럼은 테이블의 무조건 마지막 칼럼이 되고 칼럼의 위치를 지정할 수는 없다.

• DROP COLUMN : 테이블에서 필요없는 칼럼 삭제, 데이터가 있거나 없거나 모두 삭제가능하지만 한번에 하나의 칼럼만 삭제가능하고, 삭제 후 최소 하나이상의 칼럼이 테이블에 존재해야 한다. 한번 삭제된 칼럼은 복구 불가능하다.

• ALTER TABLE 테이블명

  DROP COLUMN 삭제할칼럼명;

• MODIFY COLUMN : 칼럼의 데이터 유형, 디폴트값, NOT NULL제약조건에 대한 변경(칼럼 정의 변경)

• [Oracle]

  ALTER TABLE 테이블명

  MODIFY (칼럼명1 데이터유형 [DEFAULT 식] [NOT NULL},

  칼럼명2 데이터유형,,,);

• [SQL Server]

  ALTER TEBLE 테이블명

  ALTER COLUMN(변경대상) (칼럼명1 데이터유형 [DEFAULT 식] [NOT NULL},

  칼럼명2 데이터 유형,,,);

• 칼럼을 변경할 때에는 몇가지 고려해 변경

• 크기를 늘릴수 는 있지만 줄이지는 못한다. (기존 데이터 훼손의 위험)

• NULL값만 가지고 있거나 테이블에 아무행도 없으면 칼럼의 폭을 줄일 수 있다.

• NULL값만을 가지고 있으면 데이터유형을 변경할 수 있다.

• DEFAULT 값을 바꾸면 변경작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.

• NULL값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.

• SQL Server의 경우

• ??

• RENAME COLUMN : 칼럼명 변경

• [Oracle]

  ALTER TABLE 테이블명

  RENAME COLUMN 변경칼럼명 TO 새칼럼명;

• 제약조건에 대해서도 자동으로 변경되는 장점이 있지만 ADD/DROP COLUMN기능 처럼 ANSI/ISO에 명시된 기능이 아니라 Oracle 등의 일부 DBMS만 지원하는 기능이다.

• [SQL Server] - sp_rename 저장프로시저를 이용하여 칼럼 이름 변경

  sp_rename 변경칼럼명, 새칼럼명, 'COLUMN';

•  

• DROP CONSTTAINT : 제약조건 삭제

• ALTER TABLE 테이블명

  DROP CONSTRAINT 제약조건명;

• ADD CONSTARINT : 특정 칼럼에 제약조건 추가

• ALTER TABLE 테이블명

  ADD CONSTARINT 제약조건명 제약조건 (칼럼명);

• =>참조 제약조건을 추가해 참조당하게 되면 참조 무결성 옵션에 따라서 삭제하려 할 경우 외부에서 참조되고 있기 때문에 삭제가 불가능하게 제약을 할 수 있다. 즉, 외부키(FK)를 설정함으로써 실수에 의한 테이블 삭제나 필요한 데이터의 의도하지 않은 삭제와 같은 불상사를 방지하는 효과를 볼 수 있다.

4.RENAME TABLE

• 테이블의 이름 변경

• [Oracle]

  RENAME 변경할테이블명 TO 새테이블명;

• [SQL Server] - sp_rename저장프로시저로 이름 변경

  sp_rename 변경할 테이블명, 새테이블명;

5.DROP TABLE

• 테이블 삭제-테이블의 모든 데이터 및 구조 삭제

• DROP TABLE 테이블명 [CASCADE CONSTRAINT];

• CASCADE CONSTRAINT옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제

• SQL Server는 옵션이 존재하지 않고 테이블 삭제전에 참조하는 FOREIGN KEY제약조건 또는 참조하는 테이블을 먼저 삭제해야 한다.

6.TRUNCATE TABLE

• 테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든행들이 제거되고 저장공간을 재사용 가능하도록 만들어 준다. 테이블 구조를 삭제하기 위해서는 DROP TABLE을 실행하거나 (delete from)실행한다.

• TRUNCATE TABLE 테이블명;

• =>DROP TABLE의 경우는 테이블 자체가 없어지기 때문에 테이블구조를 확인할 수 없다. 반면 TRUNCATE TABLE의 경우는 테이블 구조는 그대로 유지한 채 데이터만 전부 삭제하는 기능으로 AUTO COMMIT 의 특성등으로 DDL로 분류함.

• =>테이블 전체의 데이터를 삭제하는경우, 시스템 활용 측면에서는 DELETE TABLE보다는 시스템 부하가 적은 TRUNCATE TABLE을 권고한다. 단, 정상적인 복구가 불가능하므로 주의해야 한다.(로그가 없어 Rollback불가능 주의!단,SQL Server의 경우 가능..)

제 3절 DML(DATA MANIPULATION LANGUAGE)-입력,수정,삭제,조회

1.INSERT

• 한번에 한건만 입력된다.

• INSERT INTO 테이블명 ( COLUMN_LIST)

  VALUES (CULUMN_LIST에 넣을 VALUE_LIST);

• INSERT INTO 테이블명

  VALUES (전체 COLUMN에 넣을 VALUE_LIST);

• 칼럼명과 값을 1:1로 매핑해서 입력하고 문자유형일 경우 '로 묶어 입력한다. 숫자의경우는 붙이지 않는다.

• 칼럼명들을 나열할 때에는 칼럼순서와 테이블의 칼럼순서는 상관없으며 정의하지 않은 칼럼은 DEFAULT로 NULL값이 입력된다. 단, Primary Key나 NOT NULL로 지정된 칼럼은 NULL이 허용되지 않는다.

• 모든 칼럼에 데이터를 입력하는 경우로 칼럼리스트를 나열하지 않아도 되지만 칼럼의 순서대로 빠짐없이 데이터가 입력되어야 한다.

• null값을 넣을경우 ''또는 null 이라고 명시적으로 표현

2.UPDATE

• UPDATE 테이블명

  SET 수정될칼럼명=변경할 값;

3.DELETE

• 데이터 삭제-WHERE절을 사용하지 않으면 테이블의 전체 데이터가 삭제된다.

• DELETE [FROM] 삭제할 테이블명;

• =>DDL과 DML명령어를 처리하는 방식에 있어서 차이를 보이는데

• DDL(create, alter, rename, drop)명령어인 경우에는 직접 데이터베이스의 테이블에 영향을 미치기 때문에 입력순간 작업이 즉시 AUTO COMMIT완료된다.

• 하지만 DML(insert,update,delete,select)명령어의 경우 조작하려는 테이블을 메모리 버퍼에 올려놓고 작업하기 때문에 실시간으로 영향을 미치는것은 아니다. 실제 테이블에 반영되기 위해서는 commit명령어로 transaction을 종료(완료)해야 한다.

• 그러나 SQL Server의 경우 DML도 AUTO COMMIT으로 처리되어 따로 완료시킬 필요가 없다.

• 테이블의 전체데이터를 삭제하는경우 삭제된 데이터를 로그로 저장하는 delete보다는 시스템 부하가 적은 truncate table을 권고한다.

4.SELECT

• SELECT [ALL/DISTINCT] 칼럼명,칼럼명,,,

  FROM 테이블명

  ALL: Default 옵션으로 별도 표시가 없으면 all이다. 모두 출력

  DISTINCT : 중복된 데이터가 있는경우 1건으로 처리해 출력

• DISTINCT 옵션

• 종류별로 1개씩+NULL까지 출력

• WILDCARD 사용

• 해당테이블의 모든 칼럼정보를 보고 싶은 경우에는 와일드카드로 애스터리스크(*)를 사용하여 조회한다.

• SELECT * FROM 테이블명; (모두 조회)

• 칼럼레이블(LABLE)=칼럼명이 맨위에 보이고 다음줄부터 데이터가 출력된다. 레이블은 기본적으로 대문자로 보이고 문자및 날짜데이터는 좌측정렬, 숫자데이터는 우측정렬이 된다.

• ALIAS 부여하기

• 일종의 별명(ALIAS, ALIASES)를 부여해 칼럼 레이블을 변경

• 칼럼명 바로뒤에 정의한다

• 칼럼명과 ALIAS사이에AS,as 키워드로 정의할 수도 있다

• 이중인용부호 ""로 ALIAS가 공백, 특수문자, 대소문자 구분을 포함할 수 있다. (SQL Server의 경우 "",'',[]로 부여가능)

5.산술 연산자와 합성연산자

• 산술 연산자

•  

• 합성(CONCATENATION) 연산자

• 문자와 문자를 연결하는 경우 2개의 수직바 ||에 의해 이루어진다(Oracle)

• 문자와 문자를 연경하는 경우 +표식에 의해 이루어진다(SQL Server)

• 두벤더 모두 CONCAT(str1, str2)함수로 붙일 수도 있다.

• 칼럼과 문자 또는 다른 칼럼과 연결시킨다.

• 문자표현식의 결과에 의해 새로운 칼럼을 생성한다.

• =>컬럼명과 함께 나열해 데이터들을 모아 문장으로 만들 수도 있다.

제 4절 TCL(TRANSACTION CONTROL LANGUAGE)

1.트랜잭션 개요

• =>논리적 연산 단위로 밀접히 관련되어 분리될 수 없는 한개 이상의 데이터베이스 조작을 가리킨다. 하나의 sql문장이 포함되고 분할할 수 없는 최소한의 단위가 된다. 때문에 전부적용 또는 전부취소하게 된다. (ALL OR NOTHING)

• =>올바르게 반영된 데이터를 DB에 반영시키는것을 COMMIT, 트랜잭션 시작 이전의 상태로 되돌리는것을 ROLLBACK이라고 하며 SAVEPOINT기능까지 TCL로 분류한다.

• 대상이 되는 SQL문은 UPDATE, INSERT, DELETE등 데이터를 수정하는 DML문이다.

•  

• =>트랜잭션의 특성(특히 원자성)을 충족하기 위해 데이터베이스는 다양한 레벨의 잠금기능을 제공하고 있는데, 잠금은 기본적으로 트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른트랜잭션이 동시에 접급하지 못하도록 제한하는 기법이다. 독점적으로 접근할 수 있고, 간섭이나 방해를 받지않는것이 보장된다.=>잠금을 수행한 트랜잭션만이 해제가능.

2.COMMIT

• 트랜잭션을 완료하는 명령어

• COMMIT이나 ROLLBACK이전의 데이터 상태

• 메모리 버퍼만 영향을 받아 이전상태로 복구가능

• 현재 사용자는 SELECT문장으로 결과 확인가능

• 다른 사용자는 현재사용자가 수행한 명령의 결과를 볼 수 없다.

• 변경된 행은 잠금이 설정되어 다른사용자가 변경할 수 없다.

• UPDATE,INSERT,DELETE 작업으로 변경완료되었음을 DB에 알려주기위해 사용하는 것

• COMMIT이후의 상태

• 변경사항이 DB에 반영

• 이전데이터는 영원히 잃어버림

• 모든사용자가 결과를 볼 수 있다.

• 관련된 행에 잠금이 풀리고 다른사용자가 행을 조작할 수 있게 된다.

• SQL Server 의 COMMIT

• =>Oracle은 COMMIT또는 ROLLBACK을 해주어야 트랜젝션이 종료되지만 일부 툴에서는 AUTO COMMIT을 옵션으로 선택가능하다. 하지만 SQL Server 는 기본적으로 AUTO COMMIT이기 때문에 COMMIT이나 ROLLBACK을 처리할 필요가 없다. 성공이면 자동COMMIT 오류가 발생하면 자동으로 ROLLBACK처리된다.

• AUTO COMMIT

• 기본 방식으로 DML,DDL을 수행할때마다 DBMS가 트랜잭션을 컨트롤하는 방식

• 암시적 트랜잭션

• Oracle과 같은 방식으로 처리, 트랜잭션의 시작은 DBMS가 , 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK으로 처리

• 인스턴스 단위 또는 세션단위로 설정가능하고 인스턴스 단위는 서버 속성창의 연결화면에서 기본연결 옵션중 암시적트랜잭션에 체크 / 세션단위는 세션옵션중 SET IMPLICIT TRANSACTION ON을 이용한다.

• 명시적 트랜잭션

• 시작과 끝을 모두 사용자가 명시적으로 지정. BEGIN TRANSACTION(BEGIN TRAN)으로 시작하고 COMMIT [TRANSACTION] 또는 ROLLBACK [TRANSACTION] 으로 종료한다. 롤백은 최초의 시작지점까지 모두 롤백된다.

3.ROLLBACK

• =>변경사항 취소로 이전의 상태로 데이터가 복구되며 잠금이 풀리고 다른사용자가 변경을 할 수 있게 된다.

• SQL Server의 ROLLBACK

• AUTO COMMIT이 기본이라 명시적으로 선언해주어야 한다.

• BEGIN TRANSACTION(BEGIN TRAN)

  ~~~~

  COMMIT [TRANSACTION] || ROLLBACK [TRANSACTION]

• ROLLBACK후의 데이터 상태

• 변경사항은 취소된다

• 이전데이터는 다시 재저장

• 관련된 행에 잠금이 풀리고 행조작가능

• COMMIT과 ROLLBACK 사용함으로 보는 효과

• 데이터 무결성 보장

• 영구변경 하기전에 변경사항 확인가능

• 논리적으로 연관된 작업을 그룹핑하여 처리가능

4.SAVEPOINT

• =>저장점을 정의하면 롤백할때 전체작업을 롤백하는것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백

• 복수의 저장점을 정의할 수 있으며, 동일이름으로 저장점을 정의했을때에는 나중정의한 저장점이 유효하다.

• [Oracle]

  SAVEPOINT 저장점명;

  ROLLBACK TO 저장점명;

• [SQL Server]

  SAVE TRANSACTION 저장점명;

  ROLLBACK TRANSACTION 저장점명;

•  

• =>A로 되돌리고 나서는 B와같이 미래 방향으로 되돌릴 수는 없다. 일단 저장점까지 롤백하고 나면 그이후의 저장점이 무효가 되기 때문이다. 즉 ROLLBACK TO A를 실행한 지점에선 B는 존재하지 않는다. 저장점 없이 ROLLBACK만 실행한 경우 모든 사항을 취소하고 시작위치로 돌아간다.

=>트랜잭션 다시 정리

• 데이터의 값변경을 발생시기는 INSERT, UPDATE, DELETE수행시 그 변경되는 데이터의 무결성을 보장하는것이 COMMIT과 ROLLBACK이다. Oracle의 트랜잭션은 트랜잭션의 대상이되는 SQL문장을 실행하면 자동으로 시작되고, COMMIT또는 ROLLBACK을 실행한 시점에서 종료된다. 단, 다음의 경우에는 COMMIT과 ROLLBACK을 하지 않아도 자동으로 종료된다.

• CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE등 DDL문장을 실행하면 그전후 시점에 자동으로 커밋

• DML문장 이후에 커밋없이 DDL문장이 실행되면 DDL수행 전에 자동으로 커밋

• 데이터베이스를 정상적으로 종료하면 자동으로 트랜잭션이 커밋

• 애플리케이션의 이상종료로 접속이 단절되었을때는 자동으로 롤백

• SQL Server의 경우 AUTOCOMMIT이 기본이지만 애플리케이션의 이상종료로 데이터베이스(인스턴스)와의 접속이 단절되었을때 트랜잭션이 자동으로 롤백되어 종료된다.

제 5절 WHERE 절

1.WHERE 조건절 개요

• =>SQL문장에 제한 조건, 조인조건을 기술하거나 결과를 제한하기 위한 조건 기술. 기본적인 SQL문장은 Oracle의 경우 필수적으로 SELECT절과 FROM절로 이루어져 있다. SQL Server의 경우 칼럼이 없는 경우는 FROM이 필요없지만, 칼럼이 상ㅇ된경우에는 필요.

• SELECT [DISTINCT/ALL] 칼럼명 [ALIAS명]

  FROM 테이블명

  WHERE 조건절;

• FROM다음에 위치하며 칼럼명, 비교연산자, 문자/숫자/표현식, 비교칼럼명(JOIN사용시)으로 구성된다.

2.연산자의 종류

•  

• (괄낫비교앤올)

• 괄호로 묶은 연산이 제일먼저 부정연산자가 그다음, 비교연산자, SQL비교연산자가 처리되고 논리연산자중에서는 AND, OR순으로 처리된다.

•  

3.비교 연산자

•  

• =>비교할 때, char변수나 varchar2와 같은 문자형 타입을 가진 칼럼을 특정값과 비교하기 위해서는 인용부호(',")로 묶어서 비교처리를 해야한다. (숫자형은 아님)

•  

4.SQL 연산자

•  

• IN(list) 연산자

• 컬럼명 in('값1','값2',,,);

• (컬럼명1,컬럼명2) in (('컬럼1값1','컬럼2값1'),(컬럼1값2','컬럼2값2'),,,) 로 다중리스트도 가능

• 단 컬럼명1(값들) and 컬럼명2(값들) 과는 결과가 다르니 주의

• LIKE 연산자

•  

• 컬럼명 LIKE '_은%'

• BEWTEEN a AND b 연산자

• a이상 b 이하의 값들이 나오게 된다.

• IS NULL 연산자

• 컬럼명 IS NULL

• 컬럼명 IS NOT NULL

• null은 값이 존재하지 않은것으로 확정되지 않은 값을 표현할때 사용하는데, 수치연산은 null을 리턴하고, 비교연산은 false를 리턴(결과값 없게됨)한다. 어떤값과 비교할 수 없으며, 특정값보다 크거나 적다고 표현할수 없다.

• 그때 필요한게 IS NULL, IS NOT NULL연산자이다.

5.논리 연산자

•  

• =>괄호를 잘 사용하지 못하면(누락되면) 연산자 순서에 의해 잘못된 결과를 얻을수 있다.( (),not,and,or 순서 )

• =>IN연산자와 OR연산자는 결과도 같고 내부적으로 처리하는 방법도 같다.(컬럼명=값1or컬럼명=값2와 컬럼명 in (값1,값2)는 같다) 그리고 컬럼명>=값1 and 컬럼명 <=값2 과 컬럼명 between 값1 and 값2 는 조건도 결과도 같고 내부적으로 처리되는 방법도 같은 기능이다.

6.부정 연산자

•  

7.ROWNUM, TOP 사용

• ROWNUM

• Oracle의 ROWNUM은 칼럼과 비슷한 성격의 Pseudo Column 으로써 SQL처리결과집합의 각 행에 대해 임시로 부여되는 일련번호로 원하는만큼의 행만 가져오고 싶을때 where 절에서 행의 갯수를 제한하는 목적으로 쓰인다(select 컬럼명 부분에 적어서 순서를 보이는 용도로 사용하기도 함)

• 한건의 행만 가져오고 싶을때는

• rownum=1;

• rownum<=1;

• rownum<2;

• 두건이상의 n행을 가져오고 싶을때에는 rownum=n은 안되고

• rownum<=n;

• rownum

• 무조건 1부터만 출력가능하고 rownum으로 고유키나 인덱스값을 만들수 있다.

• update 테이블명 set 컬럼명=rownum;

• TOP

• SQL server는 top절을 사용하여 결과집합으로 출력되는 행의수 제한

• TOP(expression-행의수) [PERCENT-결과집합에서 행의수%의 행만 반환] [WITH TIES-Order by절이 있을때만 사용가능하며 TOP N(percent)의 마지막행과 같은 값이 있는경우 추가행이 출력되도록 지정]

• 한건의 행만

• select top(1) 컬럼명 from 테이블명;

• 두건이상의 n행

• select top(n) 컬럼명 from 테이블명;

• =>SQL문장에서 ORDER BY절이 사용되지 않으면 위의 두개는 같은 기능을 하지만 ORDER BY절이 사용되면 기능의 차이가 발생한다.

제 6절 함수(FUNCTION)

1.내장 함수(BUILT-IN FUNCTION) 개요

• =>내장 함수는 다시 함수의 입력값이 단일행 값이 입력되는 단일행 함수(Single-Row Function)와 여러행의 값이 입력되는 다중행함수(Multi-Row Function)로 나눌수 있다. 다중행 함수는 다시 집계함수(Aggregate Function), 그룹함수(Group Function), 윈도우함수(Window Function)로 나눌수 있다. 함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1관계라는 중요한 특징을 가지고 있다

• 함수명 (컬럼이나 표현식 [,Arg1, Arg2,,,])

•  

• =>단일행 함수의 특징

• select, where, order by절에 사용가능

• 각 행들에 개별적으로 작용하며 데이터 값들을 조작하고 조작한결과를 리턴한다.

• 여러인자(Argument)를 입력해도 단하나의 결과만 리턴

• 함수의 인자로 상수,변수,표현식이 사용가능하고, 하나의 인수를 가지는 경우도 있지만, 여러개의 인수를 가질수도 있다.

• 특별한 경우가 아니면 함수의인자로 함수를 사용하는 함수의 중첩이 가능

2.문자형 함수

•  

•  

• =>Oracle은 select절과 from절을 select문장의 필수 절로 지정해 테이블이 필요없는 문장에도 DUAL이라는 테이블을 FROM절에 지정한다. DUAL테이블의 특성

• 사용자 SYS가 소유하며 모든 사용자가 엑세스 가능한 테이블

• select~ from~ 형식을 갖추기 위한 일종의 DUMMY테이블이다.

• DUMMY라는 문자열 유형의 칼럼에 'X'라는 값이 들어있는 행을 1건 포함하고 있다.(select * from dual)

• =>반면 SQL Server의 경우는 select절만으로도 실행이 가능하기 때문에 DUAL이 필요 없다. 그래도 사용자의 테이블을 조회할 때에는 From절이 필수!@

• =>따라서 칼럼명기술하는 부분에 조작을 함으로서 얻는 데이터들도 조작된 값을 얻을 수 있는데 그렇게 문장또는 가공을 할 때에는 ALIAS를 붙여 칼럼명 유지에 신경쓰자

3.숫자형 함수

•  

•  

4.날짜형 함수

• DATE타입의 값을 연산하는 함수로 Oracle의 to_number(to_char()) 함수의 경우 변환형 함수로 구분할 수도 있으나 sql server와 매핑하기 위해 함께 설명

•  

•  

5.변환형 함수

•  

6.CASE 표현

• =>IF-THEN-ELSE(END-IF)논리와 유사한 방식으로 sql의 비교연산기능 보완. 함수와 같은 성격으로 Oracle의 Decode함수와 같은기능

•  

•  

•  

•  

• =>case표현은 함수의 성질을 가져 다른 함수처럼 중첩 사용가능

7.NULL 관련 함수

• NVL/ISNULL함수

•  

•  

•  

• =>벤더 공통으로 사용하려면 case문장으로 표현

• select case when 칼럼명 IS null

• then '없음'

• else 컬럼명(원래값)

• end as alias명

• =>NVL함수를 사용하지 않으면 계산 결과가 null이 되어서 잘못된 결과가 나온다 따라서 NVL(컬럼명, 0)처럼 값을 0으로 변환하여 계산해야 한다.

• =>그러나 NVL함수를 다중행 함수의 인자로 사용하는 경우는 오히려 불필요한 부하를 잘생할 수 있으므로 사용x, 다중행 함수는 입력값으로 전체건수가 null 값인 경우만 함수의 결과가 null이 나오고 일부만 null이 나오는 경우는 다중행 함수의 대상에서 제외한다.(즉, 평균을 구하는 AVG함수를 사용하면 null값이 아닌 값들의 값을 구하게 된다)

• NULL과 공집합

• 일반적인 NVL/ISNULL함수 사용

• 공집합의 NVL/ISNULL함수 사용

• select 1 from dual where 1=2; 와 같은 조건이 대표적인 공집합을 발생시키는 쿼리로 조건에 맞는 데이터가 한건도 없는 경우를 공집합이라고 하고, null과는 또 다르다.

• NVL함수를 사용해도 인수의 값이 공집합인 경우에도 역시 공집합이 출력된다.(없는 값으로 찾을때)=>적절한 집계함수로 대체하면 NULL이 나온다. (MAX같은...다른 함수와 달리 집계함수와 Scalar subquery의 경우는 인수의 결과 값이 공집합인 경우에도 NULL을 출력한다.)=>따라서 NVL(MAX(컬럼명), 대체값) alias 로 공집합도 임의의 값을 넣을 수 있다.

• =>Oracle의 SQL Plus같이 데이터베이스와 직접대화하는 환경이라면 찾을수 없다 라는 문구로 쉽게 구분할 수 있지만 다른 개발언어 내에 포함된 경우에는 구분이 힘들다. 따라서 NVL/ISNULL함수를 사용하는 경우와 집계함수를 포함해 사용해야하는경우, 집계함수를 사용하지 않아야 될 경우까지 잘 이해해 정확히 사용해야 한다.

• NULLIF

• NULLIF 함수는 EXPR1이 EXPR2와 같으면 NULL을, 같지 않으면 EXPR1을 리턴한다. 특정값 대체에 유용

• NULLIF ( EXPR1, EXPR2)

• =

• case when 컬럼명=값

• then null

• else 컬럼명(원래값)

• end alias명

• 기타 NULL 관련 함수(coalesce)

• =>COALESCE함수는 인수의 숫자가 한정되어 있지 않으며, 임의의 갯수 EXPR에서 NULL이 아닌 최초의 EXPR을 나타낸다. 만일 모든 EXPR이 NULL이라면 NULL리턴

• COALESCE(EXPR1, EXPR2,,,)

• ex) 커미션을 1차선택값, 급여를 2차 선택값으로 선택하되 두 칼럼 모두 null인 경우 null로 표시

• COALESCE(커미션,급여) ALIAS명

• =

• case when 커미션 IS NOT NULL

• then 커미션

• else (case when 급여 IS NOT NULL

• then 급여

• else null

• end)

• end ALIAS명

제 7절 GROUP BY, HAVING 절

1.집계 함수(Aggregate Function)

• 여러 행들의 그룹이 모여서 그룹당 단하나의 결과를 돌려주는 다중행 함수중 집계합수로 그 특징

• 여러행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수

• GROUP BY절은 행들을 소그룹화 한다.

• select절, having절, order by절에 사용할 수 있다.

•  

• =>일반적으로 집계함수는 GROUP BY절과 함께 사용되지만 테이블 전체가 하나의 그룹이 되는경우 단독으로 사용가능하다.

2.group by절

• =>SQL문에서 From절과 where절 뒤에 오며, 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을때 사용된다.

• SELECT [DISTINCT] 칼럼명 [ALIAS명]

• FROM 테이블명

• [WHERE 조건식]

• [GROUP BY 칼럼이나 표현식]

• [HAVING 그룹조건식];

• =>GROUP BY절과 HAVING절의 특징

• GROUP BY절을 통해 소그룹별기준을 정하고 SELECT절에 집계함수 사용

• 집계 함수의 통계정보는 NULL값을 가진 행을 제외하고 수행

• GROUP BY절에서는 ALIAS명을 사용할 수 없다

• 집계함수는 WHERE절에는 올 수 없다.(WHERE절이 먼저수행되기 때문)

• WHERE절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거

• HAVING절은 GROUP BY절의 기준 항목이나 소그룹의 집계함수를 이용한 조건을 표시

• GROUP BY절에 의한 소그룹별로 만들어진 집계 데이터중, HAVING절에서 제한조건을 두어 만족하는 내용만 출력

• ORDER BY절을 명시해야 데이터 정렬이 수행된다.(9i부터 정렬X)

3.having절

• WHERE절에는 집계함수를 사용할 수 없는데, HAVING절에서는 가능, 결과적으로 조건을 만족하는 내용만 출력되고 즉, WHRER절과 비슷하지만 그룹을 나타내는 결과집합의 행에 조건이 적용된다는 점이 차이점.

• GROUP BY 소그룹의 데이터 중 일부만 필요한 경우, 연산전 WHERE절에서 조건을 적용해 추출후 GROUP BY연산을 하는 방법과 연산후 HAVING절에서 필터링하는 두가지 방법을 사용할 수 있다. 가능하면 WHERE절에서 적용해 계산 대상을 줄이는것이 효율적.

• HAVING절은 SELECT절에 사용되지 않은 칼럼이나 집계함수가 아니더라도 GROUP BY절의 기준 항목이나 소그룹의 집계함수를 이용한 조건표시 가능. 주의할 것은 WHERE절의 조건변경은 대상 데이터의 개수가 변경되므로 결과 데이터 값이 변경될 수 있지만, HAVING절의 조건변경은 결과데이터의 변경은 없고 출력되는 레코드의 개수만 변경될 수 있다.

4.case표현을 활용한 월별 데이터 집계

• =>개별 데이터 확인(FROM의 INLINE VIEW)->월별 데이터 구분(CASE MONTH WHEN 1 THEN SAL END M01,,,)->부서별 데이터 집계(AVG함수+GROUP BY)

• =>장문의 프로그램을 코딩하는 것에 비해 하나의 SQL문자으로 처리가능하므로 DBMS의 자원활용이나 처리속도에서 훨씬 효율적. 건수가 많아질수록 속도차이는 더 커질 수 잇다. 가능한 하나의 SQL문장으로 요구사항을 처리할 수 있도록 노력해야 한다.

• ORACLE의 DECODE함수를 사용하여 같게 쓰일 수도 있다.(DECODE(MONTH, 1,SAL) M01,,,)

5.집계 함수와 NULL처리

• 다중행 함수를 사용하는 경우 부하가 발생하므로 굳이 NVL(ORACLE)/ISNULL(SQL SERVER)함수를 다중행 함수 안에 사용할 필요가 없다. 다중행 함수는 입력값으로 전체 건수가 NULL인경우만 함수의 결과가 NULL이 나오고 전체 건수중에서 일부만 NULL인경우는 NULL인행을 다중행 함수의 대상에서 제외한다.(알아서 제외)

• CASE 표현 사용시 ELSE절을 생략하게 되면 DEFAULT값이 NULL이다. NULL은 연산의 대상이 아닌 반면, 0을 지정하게 되면 불필요하게 0이 연산에 사용되므로 자원의 사용이 많아진다. 같은 결과를 얻을수 있다면 가능한 ELSE절을 지정하지 않도록 해야한다. 같은이유로 ORACLE의 DECODE함수의 4번째인자를 지정하지 않으면 NULL이 DEFAULT로 할당된다.

제 8절 ORDER BY 절

1.Order by 절

• 특정 칼럼을 기준으로 정렬하여 출력하는 것으로 칼럼명,ALIAS,칼럼 순서를 나타내는 정수도 가능하다.(+오름차순(ASC)가 기본)

• SELECT [DISTINCT] 칼럼명 [ALIAS명]

• FROM 테이블명

• [WHERE 조건식]

• [GROUP BY 칼럼이나 표현식]

• [HAVING 그룹조건식]

• [ORDER BY 칼럼이나 표현식 [ASC|DESC] ;

• ASC(ASCENDING) : 조회한 데이터를 오름차순 정렬(기본으로 생략가능)

• DESC(DESCENDING) : 데이터를 내림차순 정렬

• =>이때, ORACLE은 NULL을 가장 큰값으로 취급하고, SQL SERVER는 NULL을 가장 작은 값으로 취급한다.

• ORDER BY 절 사용특징

• 기본정렬 순서는 오름차순(ASC)이다.

• 숫자형 데이터 타입은 오름차순 정렬했을 때, 가장작은 값부터 출력된다.(1,2,,,,100,,점점 커진다)

• 날짜형 데이터 타입은 오름차순 정렬했을 때, 날짜값이 가장 오래된(옛날) 값이 먼저 출력되고, 가장 최신의 날짜(미래날짜)가 큰값이 되어 출력된다.

• ORACLE에서는 NULL값을 가장 큰값으로 간주하여 오름차순 정렬시 가장마지막에 출력된다.

• 반면, SQL SERVER에서는 NULL값을 가장 작은 값으로 간주하여 오름차순 정렬시 가장 먼저 출력된다.

• 칼럼명 대신 ALIAS명 대신 SELECT절의 칼럼순서를 정수로 매핑하여 사용할수도 있지만 칼럼명이길거나 정렬조건이 많을경우 편리하게 사용할수 있지만 향후 유지보수성이나 가독성이 떨어지므로 칼럼명또는 ALIAS명을 권고 한다. 이 세가지를 혼용하는것도 가능하다.

2.select 문장 실행 순서

• 5. SELECT 칼럼명 [ALIAS명]

• 1. FROM 테이블명

• 2. WHERE 조건식

• 3. GROUP BY 칼럼(Column)이나 표현식

• 4. HAVING 그룹조건식

• 6. ORDER BY 칼럼(Column)이나 표현식;

• 오,일이!!!삼사육?!

• ---

  1.발췌대상테이블을 참조(FROM)

• 2.발췌대상 데이터가 아닌것 제거(WHERE)

• 3.행들을 소그룹화(GROUP BY)

• 4.그룹핑된 값의 조건에 맞는 것만 출력(HAVING)

• 5.데이터 값을 출력/계산(SELECT)

• 6.데이터를 정렬(ORDER BY)

• =>위 순서는 옵티마이저가 구문,의미 에러를 점검하는 순서이기도하다. (FROM절에 정의되지 않은 칼럼을 WHERE절, GROUP BY절, HAVING절, SELECT절, ORDER BY절에서 사용하면 에러가 발생한다.

• =>그러나, ORDER BY절에는 SELECT 목록에 나타나지 않은 문자형 항목이 포함될 수 있다. 단, SELECT DISTINCT를 지정하거나 SQL문장에 GROUP BY절이있거나 SELECT문에 UNION연산자가 있으면 SELECT목록에 표시되어야 한다. 관계형 데이터베이스가 데이터를 메모리에 올릴 때 행 단위로 모든 칼럼을 가져와 SELECT절에서 일부칼럼을 선택해도 ORDER BY절에서 메모리에 올라와있는 다른 칼럼의 데이터를 사용할 수 있는것이다.

• +인라인 뷰의 SELECT절에서 정의한 칼럼은 메인쿼리에서도 사용할 수 있다.

• =>그러나 서브쿼리의 SELECT절에서 선택되지 않은 칼럼들을 계속 유지되는 것이 아니라 서브쿼리범위를 벗어나면 더이상 사용할 수 없게 된다.(인라인 뷰도 동일).

• =>GROUP BY에서 그룹핑기준을 정의하게 되면 데이터베이스는 일반적인 SELECT 문장처럼 FROM절에 정의된 테이블의 구조를 그대로 가지고 가는것이 아니라, GROUP BY절의 그룹핑 기준에 사용된 칼럼과 집계함수에 사용될 수 있는 숫자형 데이터 칼럼들의 집합을 새로 만든다.(개별데이터는 필요없으므로 저장하지 않는다)

• =>GROUP BY절이후 수행절인 SELECT절이나 ORDER BY절에서 개별 데이터를 사용하는 경우 에러가 발생한다. 결과적으로 SELECT절에서는 그룹핑 기준과 숫자형식 칼럼의 집계 함수를 사용할수 있지만, 그룹핑기준외의 문자형식 칼럼은 정할 수 없다.(개별데이터를 사용하는경우에는 꼭 그룹핑의 기준이 되어야 한다.)

• =>SELECT SQL에서 GROUP BY절이 사용되었기 때문에 SELECT절에 정의하지 않은 MAX, SUM,COUNT집계함수도 ORDER BY절에서 사용할수 있는것을 실행결과에서 확인할 수 있다.

3.TOP N 쿼리

• ROWNUM

• Oracle에서 순위가 높은 n개의 로우를 추출하기 위해 ORDER BY절과 WHERE절의 ROWNUM조건을 같이 사용하는 경우가 있는데 그조건으로는 원하는 결과를 얻을수 없다. 데이터의 일부가 먼저 추출된 후, 정렬작업이 일어나므로 주의해야 한다. 따라서 ORDER BY절과 사용되는경우 먼저처리되기 위해 인라인 뷰에서 먼저 정렬을 수행한 뒤 메인쿼리에서 ROWNUM조건을 사용해야 한다.

• SELECT 칼럼1, 칼럼2

• FROM (SELECT 칼럼1, 칼럼2

• FROM 테이블명

• ORDER BY 칼럼2 DESC)----->먼저 정렬해 뽑고

• WHERE ROWNUM <4; ------>뽑은걸로 순위매겨 자르기

• =>이렇게 함으로 순서와 로우순서를 일치해 TOP N의 결과 생성!

• TOP( )

• 반면 SQL Server에서는 TOP조건을 사용하게 되면 별도의 처리 없이 정렬후 일부 데이터만 쉽게 출력할 수 있다.

• TOP (EXPRESSION) [PERCENT] [WITH TIES]

• TOP절을 사용하여 결과집합으로 반환되는 행의 수를 제한 할 수 있다. WITH TIES옵션은 ORDER BY절의 조건 기준으로 TOP N의 마지막행으로 표시되는 추가 행의 데이터가 같을경우 N+동일 정렬 순서 데이터를 추가 반환하도록 지정하는 옵션이다. (동일 수치의 데이터를 추가로 더 추출하는 것->만약 2등까지 뽑는데 공동 2등인 행이 하나 더 있으면 3건이 출력된다.)

제 9절 조인(JOIN)

1.JOIN 개요

• =>두 개이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는것, 일반적인경우 pk나 fk값의 연관에 의해 JOIN이 성립되지만 어떤경우는 이런 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립된다. 다만, 한가지 주의할 점은 FROM절에 여러테이블이 나열되어도 데이터를 처리할 때에는 단 두개의 집합간에만 조인이 일어난다. 특정2개의 테이블만 먼저 조인되고, 2개의 테이블에서 조인이되어 처리된 새로운 데이터집합과 남은 한개의 테이블이 다음 조인이 되는것이다. 4개 이상이 되어도 같은 프로세스를 반복한다. (2개씩 조인 처리..이때 조인순서는 옵티마이저에 의해 결정되고 주요 튜닝 포인트가 된다)

2.EQUI JOIN

• =>등가 조인은 두개의 테이블간에 칼럼값들이 서로 정확하게 일치하는 경우에 사용되는 방법으로 대부분 PK-FK관계를 기반으로 한다. (반드시는 아님) 이 기능은 계층형(Hierarchical)이나 망형(Network)데이터베이스와 비교해서 관계형 데이터베이스의 큰장점.

• JOIN의 조건은 WHERE절에 기술하게 되는데 '='연산자를 이용해 표현한다.

• SELECT 테이블1.칼럼명, 테이블2.칼럼명,...

• FROM 테이블1, 테이블2

• WHERE 테이블1.칼럼명1=테이블2.칼럼명2;(JOIN조건)

• ANSI/ISO SQL표준▼

• SELECT 테이블1.칼럼명, 테이블2.칼럼명,...

• FROM 테이블1 INNER JOIN 테이블2

• ON 테이블1.칼럼명1=테이블2.칼럼명2;(JOIN조건)----->INNER JOIN 명시되면 +ON?

• 칼럼명 앞에 테이블을 명시하는 이유는 테이블에 같은 칼럼명이 존재하는 경우 에러방지/어느테이블에있는 칼럼인지 가독성이나 유지보수성을 높이는 효과

• 조인조건에 맞는 데이터만 출력하는 INNER JOIN에 참여하는 테이블이 N개라고 했을때 필요한 JOIN조건은 N-1개 이상이 필요하다.

 

• 선수-팀 EQUI JOIN 사례

• 보통 FROM절의 테이블에 대해서도 ALIAS를 사용해 가독성을 높여 여러테이블을 사용하는 조인을 이용할 때 유용하게 사용된다.

• 선수-팀 where절 검색 조건 사례

• WHERE절에서 추가로 JOIN조건 외 검색조건에 대한 제한조건을 덛붙여 사용가능. 부수적인 제한조건을 논리연산자를 통하여 추가 입력하는것이다.

• 이때 주의 사항은 만약 테이블에 대한 ALIAS를 적용해 SQL문장을 작성했을때 WHERE절과 SELECT 절에는 테이블명이 아닌 ALIAS를 사용해야 한다.( 권장사항은 아니지만 하나의 SQL문장에서 유일하게 사용하는 칼럼명이라면 아무것도 붙이지 않아도 됨)

3.Non EQUI JOIN

• =>비등가 조인은 두개의 테이블간에 칼럼값들이 서로 정확하게 일치하지 않는 경우에 사용. '='연산자가 아닌 다른 연산자들을 사용하여 JOIN수행. 정확하게 일치하지 않는경우에는 EQUI JOIN을 사용할 수 없는데. 이런경우 NON~을 시도할 수 있으나 데이터 모델에 따라 불가능한 경우도 있다.

• SELECT 테이블1.칼럼명, 테이블2.칼럼명,...

• FROM 테이블1, 테이블2

• WHERE 테이블1.칼럼명1 BETWEEN 테이블2.칼럼명1 AND 테이블2.칼럼명2;(JOIN조건-BETWEEN A AND B는 한 사례)

• >,<와 같은 다른 연산자도 모두 해당되고, 데이터 모델에 따라 불가능 하기도 하다.

4.3개 이상 TABLE JOIN

• 관계가 없을 때 중간에 서로의 연관관계가 있는 테이블을 추가해 세개의 테이블을 JOIN해 원하는 데이터를 얻을 수 있다.

• FROM 테이블1, 테이블2, 테이블3

• WHERE 1.칼럼1=2.칼럼1

• AND 2.칼럼2=3.칼럼2

• 이었을때 INNER JOIN을 명시하면

• FROM 테이블1 INNER JOIN 테이블2

• ON 1.칼럼1=2.칼럼1

• INNER JOIN 테이블3

• ON 2.칼럼2=3.칼럼2

• 가 된다.

=>JOIN이 필요한 기본적인 이유는 정규화에서 시작된다. 정규화는 불필요한 데이터의 정합성을 확보하고 이상현상(ANOMALY)발생을 피하기 위해, 테이블을 분할하여 생성하는것이다. 하나의 테이블에 모아놓고 조회할 수도 있지만 정합성에 큰비용이 들고 데이터를 추가, 삭제, 수정하는 작업 역시 상당한 노력이 요구 될것이다. 성능측면에서도 간단한 데이터를 조회하는 경우 오히려 검색속도가 떨어질 수 있다. 따라서 정규화를 하여 분할하게되면 해결된다. 그렇지만 테이블간에 논리적인 연관관계가 필요하고 그런관계성을 통해 다양한 데이터 출력이 가능한것이다. 이런 논리적관계를 구체적으로 표현하는것이 JOIN 조건인것이다. 관계형 데이터베이스의 큰 장점이면서 SQL튜닝의 중요대상이 되는 JOIN을 잘못하면 자원부족이나 과다 응답시간을 발생시키는 원인이 되므로 JOIN 조건은 신중하게 작성한다.