첫째주 정리

<Oracle>

 -SQL의 기본

 

1.데이터 베이스 소개

• 데이터(수,문자,이미지로 된 자료)를 필요에 의해 일정한 형태로 저장해 놓은것으로 정보의 양이 늘며,
• DataBase Management System(DBMS) 인 소프트웨어가 필요해 짐
• 관계형 데이터베이스(테이블에 저장됨)가 나오며 DBMS에서 조작가능한 언어가 SQL이다.
• Oracle, Mysql, Ms-sql 등의 소프트웨어가 있다.

2.oracle의 소개

• 대용량 데이터를 다룰때 사용하는 RDBMS(관계형 데이터베이스)로 Larry Ellison이 창업
• 컴퓨터 하나에 하나만 동작 가능하고
• DB Server와 DB Client로 분할되어 설치(app\owner\oradata\orcl에 저장)
• =정보저장,제공   = 정보사용
• [Oracle의 구조도]

• D:\app\owner\oradata\orcl => File 영역

  D:\app\owner\product\11.2.0\dbhome_1\BIN sqlplus.exe 존재 => 클라이언트 프로세스

• 

 

3.SQL의 소개

• Structured Query Language(SQL)로 관계형 데이터 베이스에서 데이터정의, 데이터조작, 데이터제어를 하기위한 언어, 회사별로 조금씩 다르다.

4.oracle 설치및 확인

• oracle.com 에서 DBMS를 받을수 있으며 Oracle Database 11g Release 2 32를 받는다.
• 설치시 설치파일은 한글로된 폴더아래 존재하면 안되며, 제어판-시스템의 컴퓨터 명도 영어여야함.
• 데이터베이스 생성및 구성 - \D에 설치 - 버전: standard edition, 문자집합: UTF-8, 관리자 비밀번호설정 - 데이터베이스 생성이 완료되었을때 비밀번호 관리를 눌러 sys의 비밀번호 변경 가능.
• 설치확인 방법 : 시작 - 제어판 - 시스템 및 보안 - 관리도구 - 서비스 에 들어가
• OracleServiceORCL과 ~TNListener이 살아있으면 작동한다.
• 중지되었을떈, DOS창에서 sqlplus"/as sysdba"로 관리자 계정으로 들어간다음
• startup, 서비스 종료는 shutdown abort로 한다.

5.sqlplus

• DOS창에서 sqlplus "계정명/비번" 으로 들어갈 수 있으며,
• SQL문이랑은 다르며, SQLPLUS문이 존재한다.
• Sqlplus문 : oracle tool인 sqlplus.exe 에서 제공하는 명령으로 문장끝에 세미콜론을 붙이지 않는다.
• SQL문을 실행시키고 그 결과를 볼수 있도록 오라클에서 제공하는 툴이다.
• 쿼리는 휘발성이라 쿼리문의 재사용이 불가능하여 sqlplus.exe의 edit을 사용한다.
• 툴에서 출력형식을 지정하는 등의 환경을 설정하며, sql명령문을 저장하거나 편집하는 기능을 제공한다.
• sql명령문 저장 방법 : 계정으로 로그인 후, edit c:/위치/위치/파일명.sql로 파일이 없다면 생성할수 있다.
• 저장한 .sql 실행 방법 : 로그인 상태로, @ 경로/파일명.sql
• 나가기 : exit엔터
• exit, column, col, show(all), desc, edit(ed)등 이 있다.

6.database를 만들기전에 알아야 할 것들

• SQL문 종류 및 주의할점
• -SQL문 종류
• 조작어(DML : Data Manipulation Language) : select, insert, update, delete
• 제어어(DCL : Data Control Language) : grant, revoke,(commit, rollback)
• 정의어(DDL : Data Definition Language) : create, drop, alter
• 트랜젝션 제어어(TCL : Transaction Control Language) : commit Rollback
• -주의할 점
• 컬럼명, 테이블명, 쿼리문에 대해서 대소문자를 가리지 않는다. 단, 값에 대해서 가림
• 문자열의 ''홀따옴표 안에 들어있는 'A'와 'a'는 다르다.
• 문장의 종료시 반드시 세미콜론 ; 을 넣는
• 테이블읽는법과 생성시 주의할점
• -테이블

선수명	포지션
김남일	DF
박지성	MF

• 일때, 세로 하나를 열(Column)
• 첫째 가로줄을 컬럼명(Column name), Field Name
• 첫째줄을 제외한 가로 하나를 행(Row), 튜플, 레코드
• 값 하나하나를 필드(Field)
• 전체를 테이블 이라고 한다.(이렇게 테이블에 저장하면 RDBMS가 된다)
• -주의할점
• 테이블 명은 객체를 의미하는 의미있는 이름이어야한다.
• 테이블 명은 중복되지 않아야하고, 한테이블 내에서는 컬럼명 중복도 불가능하다.
• 테이블이름을 지정후, 괄호로 묶어 칼럼을 지정후엔 데이터 유형을 꼭 지정해야 한다.
• 테이블명과 컬럼명은 반드시 문자로 시작해야하고, 벤더의 사전 정의 예약어는 쓸수없다.
• A-Z, a-z, 0-9, _, $, # 문자만 허용된다.
• Data Type
• 값을 구분하여 저장하기위한 예약된 공간들로, 테이블을 구성한다.

숫자형(정수)	number(크기)	22의 크기로 크기를 주지않으면 스물두자리의9가 들어갈수 있다. (?) 만약 age number(3)의 경우 0~999까지 가능하다.
숫자형(실수)	number(전체자릿수, 실수자릿수)	만약 정수부분이 4자리고, 소수자리가 2인 경우 number(6,2) 가 된다. 0~9999.99 까지 가능.
문자열형 (고정길이)	char(크기)	0~2000바이트의 크기로 고정길를 가지고 있으므로 작은데이터로 비어도 크기유지. (검색속도가 빠르며, 정해진 길이의 데터를 넣는것이 좋음.보통 한글은 3byte로 계산하고 영어는 1byte.)
문자열형 (가변길이)	varchar2(크기)	0~4000바이트의 크기.입력데이터에 따라 크기가 조정. (char보단 느리지만 저장공간절약가능. 저장공간HDD의 효율적사용)
날짜형	date	(현재 날짜느 sysdate로 추가한다.)
긴 숫자형	long	2Gbyte의 크기로, 숫자가 22자리를 넘어가면 사용한다.
긴 문자열형 (charactior long object?	clob	4Gbyte의 크기로, 시간이 오래걸릴것.
파일형 (바이너리 라지 오브젝트)	blob	시간이 너무 오래걸려 파일을 저장하지는 않는다. 파일의 이름만 저장.

• +계정이 가진 테이블 명 조회 : select tname from tab;
• +sqlplus에서 @적고 파일을 드래그 하면 경로가 나온다.
• +sqlplus에서 .sql을 열으면 Dos창이 먹히지 않으므로 우클릭-편집으로 수정하는것이 간편
• +sqlplus에서 테이블의 구조보기 : desc 테이블명(컬럼명, 데이터명 나온다.)

 

 

 

1. DDL(Data Definition Language) : create, drop, alter

• create

• -Table 생성

• -문법) create table 테이블명(

• 컬럼명 데이터형(크기) [제약사항],

• ...

• ...

• );

• []는 생략가능하며, 이러한 값만 받겠습니다. 하는것.

• 다 대문자로 저장이 되며, 테이블만 만들어져 안에 아무데이터가 없다.

• drop

• -Table에서 필요없는 컬럼 삭제=> 데이터베이스의 모든객체 삭제시 사용.

• -문법) drop column 컬럼명;

• 한번에 하나의 컬럼만 삭제가능하고, 삭제후 하나이상의 컬럼이 존재해야한다.

• 한번삭제된 컬럼은 복구가 불가능하다!

• -Table 삭제

• -문법) drop Table 테이블명;

• 테이블의 모든 데이터 및 구조 삭제(테이블 자체 삭제)

• Oracle verssion 11g부터는 휴지통으로 들어간다(전체용량 바뀌지 않는다.)

• 휴지통을 비우기전까지 .DBF파일(table space테이블저장공간)의 크기는 변하지 않는다.

• 테이블명을 조회하면 삭제가된게 아니라 삭제된 테이블명이 조회가 된다.(BIN522afe 이런식)

• 보이는 삭제된 테이블은 아무것도 하지는 못한다. 보여줄뿐.

• -오라클의 휴지통(sqlplus문)

•  내용보기 : show recyclebin

•  내용비우기 : purge recyclebin

•  버려진 테이블 복구(복구할 테이블 원래명이 없어야 복구(중복불가))

•  : flashback table 원본테이블명 to before drop

• -truncate

• -Table의 모든행(데이터)이 제거되고 저장공간 재사용이 가능하도록 만들어줌.

• -문법) truncate table 테이블명;

• 테이블의 구조는 유지되고 데이터만 전부삭제.

• 복구 불가능!

2.DML (Data Manipulation Language) : select, insert, update, delete

• select
• -Table 조회
• 문법)   select         컬럼명,컬럼명 alias, 컬럼명 산술연산자, 함수(컬럼명)
              from          테이블명    
              where        조건                        <모든 연산자 다 쓸수 있다. 관리와 논리 주로들어감..
              group by    그룹으로 묶을 껄럼
              order by    정렬 ;

• select from이 필수며, where절, group by절, order by절은 생략가능하다. 

• * : 해당 테이블의 모든 컬럼을 조회하세요 이며, 컬럼명의 자리에 들어가나 *과 컬럼명을 함께 사용할수는 없다.

•  

• -alias : 별칭(컬럼명,테이블명) 으로 긴이름을 짧게 바꿔서 사용할때 사용

• 문법) 컬럼명^별칭

•         컬럼명 as 별칭  

• 이쿼리 안에서만 그이름을 갖고 나가면 다시 본명을 가진다.

• 문법) from 테이블명 별칭 (조인할때 주로씀)

• 부여된 alias는 조건절에서 못쓰고 서브쿼리에서 쓸수있다.

• 부여된 alias는 바로 아래의 where절에서는 사용할수 없다.

• alias의 장점은 컬럼을 복사해 별칭만 달리해 여러개 붙일수 있다.(?)

• 별칭붙일때 "큰따옴표에 넣어서 부여하면 대소문자가 가려진 별칭이 된다.(검색시 요류의 가능성 권장놉)

• insert

• -Table에 레코드 삽입(한번에 하나의 레코드를 추가)

• -데이터가 순차적으로 입력되는것이 아니라 중간에 섞일수 있다.

• -Subquery가능.

• -문법) insert into 테이블명(컬럼명.....) values(값......);

• 컬럼명부분 생략가능하지만 컬럼의 순서를 명확히 알고, 순서대로 값을 모두 넣어야한다.

• 컬럼명 명시시 원하는 컬럼만 값 넣을수있고 순서와 상관없이 넣을수 있다.

• 수는 그냥 넣어도 가능하지만 문자열인 경우 ''홀따옴표가 필수

• 현재 날짜를 입력시 sysdate이고, 다른날은 날짜형식의 문자열로 ''안에 입력하면된다.

• ex)'2018-10-23'일때 시간은 00으로 저장.

• 데이터가 잘못들어가면 형과 자릿수가 맞으면 에러없이 들어가고, 0.8일때 0은 저장되지 않는다(?)

• 컬럼을 생략하면 null이 들어가는데 출력시 보이지 않고, 중복데이터가 넣는데로 들어간다.

• -null
• oracle에서 null은 문자열,숫자,날짜도 아닌 사용할 수 없는 값으로. insert시 생략하면 들어가고. 아무것도 조회되지 않는다.
• 문자열일땐 컬럼을 생략하거나 ''홀따옴표 두개를 공백없이 넣을때, 숫자나 날짜데이터일땐 0은 null이 아니며 컬럼을 생략하는 경우에만 들어간다.
• 공백과 숫자 0과는 다르며, 조건에 맞는 데이터가 없는 공집합과도 다르다. '아직정의되지 않은 미지의값' 이거나 '현재 데이터를 입력하지 못하는경우' 로 정의된다.
• -Column(Col)
• 조회되는 컬럼의 크기 조절하는 명령어로 툴을 쓰면 필요가 없어진다.
• sqlplus명령어로 다른 DBMS에서는 없을수 있다.
• -문법) col(column) 컬럼명 format 형식
• 으로 형식은 숫자인 경우 0 : 해당자리에 수가 없다면 0 출력 / 9 : 해당자리에 수가 없다면 아무것도 출력하지 않는다. 문자열인 경우 a글자수 로 영어는 한글자에 1byte 한글은 2byte로 계산해 출력
• 0일때 빈자리를 채워 자릿수를 맞춰주고 오히려 넘치면 ##..,실수형도 가능하다.000.00
• a8은 한행에 써지게 되고 넘치면 아랫줄로 넘어가 출력된다.

 

• update
• -Table에 컬럼의 값을 변경할때.
• Transaction대상쿼리 이므로 작업이 종료되면 commit을 해주어야한다.
• subquery사용해 다른쿼리랑 작업이 가능핟.
• -문법) update 테이블명
•          set       컬럼명=변경할 값, 컬럼명=변경할 값,,,(필요한만큼
•          where   컬럼명 연산자 기준값;
• 기준값에 해당하는 모든레코드의 컬럼값이 변경되며, 만족하는 행만 그수만큼 변경돤다.
• 잘되었으면 commit을 날리고 update에서는 where절이 중요하다
• where절
• -조건설정
• -문법) where 컬럼명 연산자 기준값;
• 연산자에는 관계연산자가 들어가게 되고, 관계연산자에는 >,<,>=,<=,=,!=(<>)가 있다.
• 논리연산자로 두개의 조건을 주기도 한다.
• 연산자
• 오라클에서 제공하는 연산자로 연산에 사용되는 예약된 부호들이다.
• 산술연산자 : +,-,*,/,mod()(나눈나머지를 구하는 함수)
• 관계연산자 : >,<,>=,<=,=,!=(<>)
• 논리연산자 : and, or(and와 or는 관계연산자를 붙일떄 사용), not
• 문자열연산자 : like,%,_
• null비교 : is null(null 이니?), is not null
• 포함이되어있는지 : in(), not in()   
• 붙임연산자 : ||
• <산술과 붙임만 select 조회하는 컬럼에서 사용가능하고 나머지는 어디든지 가능.
• -논리연산자
• and : 전항과 후항이 모두참일때 참반환,나머진F
• or : 전항과 후항이 모두 거짓일때에만 거짓반환,나머진T
• (in이라는 연산자로 바꾸어쓸수있다.)
• -붙임연산자
• 컬럼끼리 붙여서 출력하거나 출력결과를 문장으로 만들어 보여줄때 주로 쓰인다.
• -포함되어있는지
• in은 포함하는, not in은 포함하지 않는
• 문법) 컬럼명 in(값들,,,)
• delete
• -Table의 레코드의 한행 삭제(값 하나를 지우려면 update
• -Tansaction 대상쿼리로 commit해야 한다.
• 삭제는 조건절을 주의해야한다.
• -문법) delete from 테이블명;
• 모든 레코드 삭제
• -문법) delete from 테이블명;
•          where 컬럼명 연산자 기준값;
• 으로 특정 레코드 삭제
• truncate보다 delete가 삭제속도가 느리며 truncate의 경우 절삭해버리면 끝인것(모든레코드를 자를때 사용하며 rollback불가능하다. subquery사용가능

 

3.DCL(Data Control Language) : grant, revoke+(commit, rollback)

  

4.TCL (Transaction Control Language) : commit Rollback

• Transaction

• Dos창에서 sqlplus로 들어가 show all했을때 autocommit off가 되어있으면, 비정상 종료시(exit안하고 닫았을때) 적용한 쿼리문이 날라가게된다.

• Tansaction이란 DB업무 처리의 단위로, insert, update, delete 사용시 꼭 완료를 해주어야 HDD에 저장된다.

• 한개이상의 데이터베이스 조작을 가리키며, 전부적용 하거나 전부취소한다.

• 올바르게 반영된 데이터를 데이터베이스에 반영시키는 것을 commit, 트랜젝션 시작 이전의 상태로 되돌리는 것을 rollback이라고 하며, 저장점 기능까지 TCL로 분류한다.

• 오라클의 구조도를 보았을때, 다른창의 dos로 접속하여 같은 계정인 scott으로 접속해도 다른 메모리를 할당해 주는데, 대상쿼리가 아닌 create같은 경우 바로 하드디스크에 저장되지만, insert작업 같은경우 트랜젝션 대상쿼리로 메모리에만 남아있게 된다. 메모리에만 남아있는 경우 commit하지 않으면 다른창에서 사용할수 없어 진행이 안돼는 경우가 발생할수 있으며, 만약 다음에 적용된 데이터베이스를 이용하고싶으면 commit으로 HDD에 기록하게 명령해야한다. commit후엔 rollback불가.

• -commit

• 접속자 세션의(할달된 메모리) 내용을 HDD에 기록하고 변경된 내용을 모든 접속자에 통지, 바뀐내용으로 작업할수 있게 된다.(=트랜젝션의 완료)

• auto commit이 on으로 설정되면 쿼리문 하나로 Transaction이 완료된다.

• (sqlplus에서 "set autocommit on"이라는 명령으로 설정. exit하고 정상적으로 종료해도 commit된다.

• -rollback

• 접속자 세션의 내용을 취소 하는것. commit된 내용은 기록하며 메모리를 날리기 때문에 commit후에는 rollback되지 않는다. 메모리에있는것을 롤백하는 거기때문에

• (=트랜젝션 취소) 메모리의 작업한 내용을 되돌리는 것으로 직전 commit이후까지 되돌린다.

• commit된 데이터는 취소시킬수 없고 savepoint로 특정지점까지 취소시킬수 있다.

• (savepoint는 commit하면 날라간다)

• -savepoint

• -문법) savepoint 저장점명;

• 으로 몇개가 되어도 commit되면 사라진다.

• 같은이름의 저장점을 생성하면 이전 저장점은 사라지며 원하는 위치로 되돌릴수있다.

• -문법) rollback to 저장점명;

 

5.Golden6 툴 사용

• 컨트롤+엔터 : 커서가 있는곳 부터 이전세미콜론까지

• F5 : 모든쿼리실행

• 특정지역 블록잡고+컨트롤+F7 : 그 부분만 실행

 

 

1.select

• -문법)   select         컬럼명,컬럼명 alias, 컬럼명 산술연산자(+,-,*,/,mod(),와 붙임연산자인 || ), 함수(컬럼명)
               from           테이블명    alias(가능하나 별로 의미가없다. 하나의 쿼리니까..)
               where         검색조건                        <모든 연산자 다 쓸수 있다. 관계(>,<,>=,<=,=,!=)와 논리(and, or, not(Oracle에서의 not은 다른쪽에 붙어 주로 쓰임) 연산자가 주로 들어감..
               group by    그룹으로 묶을 컬럼         <여러개를 묶는 중복배제하는 distint와 효과비슷
               having         그룹으로 묶을 조건         <having절 은 그룹절과 하나
               order by     정렬컬럼명,,,

• where부터는 ~절로 넣어서 쓸땐 이순서가 중요하고 따로 넣었다 빼쓸수 있다.

• 컬럼명의 컴마(,)가 컬럼을 나누게 하는것으로 띄어쓰기 잘못하면 alias된다.

• 조회하는 컬럼명에 관계연산자사용시 Error

• where절에서 레코드값을 검색시에 값은 대소문자를 가림으로 주의하여 검색한다.

 

• +산술연산자로의 연산)

• 컬럼명에 산술연산자로 수나 컬럼끼리의 연산도 가능하다. 단 컬럼명이 지저분해지니 alias 로 깔끔.

• 연산시 조회한결과가 안나오는건 null이 들어가 null연산 n= 알수없음으로 null.

• +null인컬럼에 연산을 위해서는 nvl함수 사용(nvl(컬럼명,들어갈값)

• +mod(컬럼명, 나눌값) 함수는 나눈 나머지가 나온다.

• +dual 테이블 : Oracle에 존재하는 가상테이블로 테이블리스트에 없으나 모든계정에서 사용가능하다.

• 입력되는 값으로 컬럼을 생성하여 조회하며, 간단한 테스트시 사용. 슈도테이블?사기테이블?

• +관계연산자는 where절, group by having절에서 사용하며, 미만과 이하 주의

• +null은 관계연산자로 비교할수 없으며, is null, is not null로 조회한다.

• +범위를 검색할때에는 between을 사용한다. 가독성이좋아 범위지정시 사용.

• +문법) 컬럼명 between 작은값 and 큰값

 

2.SQL연산자

• sql문장에서 사용하도록 예약되어있는 연산자로 모든데이터 타입에 연산이 가능하다.

• between a and b, in(list), like '비교문자열', is null이 있다.

• -between a and b : a와 b값 사이에 있으면 된다

• -in(list) : 리스트에 있는 값중 어느하나라도 일치하면 된다.

• -like '비교문자열' : 비교문자열과 형태가 일치하면 된다.

• 검색시 '='는 정확하게 일치해야만 검색이 되지만 like는 정확하게 알지 못해도 검색 가능

• -문법) where 컬럼명 like '기호 찾을 문자열 기호'

• -% : 글자수와 상관없는 모든 문자열

• -_ : 하나의 문자열을 찾을때. 하나당 한칸

• 기호를 쓰지 않으면 =와 같다.

• -is null : null값인 경우

3.CSV File insert

• Comma Separate value 의 약어로 데이터베으스나 표계산 소프트웨어 데이터를 보존하는 형식의 하나.

• 각항목이나 내용마다 콤마(,)로 구분해서 적는다. 텍스트파일로 보존하여 워드나 편집기에서 열람, 편집할수 있다. 범용형식이라 주소록이나 데이터를 주고받을때에도 많이 사용한다.

• 컴마가 아니고 특정문자로 구분되어 있어도 csv 라고 이야기한다. 동일문자로만 구분되면되고, 컴마가 보편적.

• csv파일은 Dos창에서 sqlldr.exe 을 사용하여 많은양의 데이터를 손쉽게 데이터베이스에 저장할수있다.

• CTL파일에 정의된내용을 로딩하여 데이터를 테이블에 추가하는 일을하며 컴마밖에 구분하지 못한다.

• (엑셀을 이용하면 탭으로 구분했더라도 컴마로 변경해 줄수 있다.)

• CTL,CSV(CTL은 스키마파일로 CSV파일을 설정하고, 그값이 어떤 테이블, 어떤 컬럼에 들어가는지 설정하는파일이고 CSV는 데이터 파일이다.) 두 개의 파일이 필요하고,DOS창에서

• 문법)sqlldr userid="scott/tiger" control=경로\파일명.ctl    <엔터시 자동커밋

• (경로와 파일의 위치가 일치해야하고, 파일은 두개다 존재해야할듯)

• 그외 방법으로는 골든툴에서 insert로 대량의 데이터를 편하게 넣을수 있다. 

4.group by절

• -문법)  select

from

where

group by

having

order by 컬럼명,,,

 

• 위의 순서가 중요하며, 집계함수와 사용시 더 보기에 편하다.

• 조회결과를 그룹으로 묶어 같은값은 하나만 출력된다.(중복배제)
• 조회되는 컬럼은 반드시 그룹으로 묶인 컬럼이거나, 아니면 집계함수를 사용한 컬럼만 사용할수있다.
• (이외의 조건에는 에러가 발생한다.)->select절 뒤에 사용할수 있는 컬럼은 group by절 뒤에 기술된 컬럼 또는 집계함수가 적용된 컬럼이어야 한다.
• 집계함수(sum,count,avg,max..등)의 통계정보는 null값을 제외하고 수행하며, alias를 사용할수 없고, 집계함수는 where절에서 사용할 수없다. where 절은 그룹으로 나누기전에 미리 행들을 제거시키고,
• group by절은 where조건을 만족하는 결과 행들에 대해서 group by절 뒤에 기술한 컬럼의 값이 같은것 끼리 그룹으로 묶는다.
• having절은 group by절의 기준항목이나 집계함수를 이용한 조건을 표시할수 있다.

• having 절은 그룹으로 묶일 조건을 설정할수있다.

• 요즘 DBMS들은 group by절 사용시 자동정렬 기능이 없어 order by절을 사용해야한다.

 

5.distinct

 

• 위의 group by와 비슷한 중복배제

• 같은 값을 하나의 데이터로 간주할때 사용하는 옵션으로, 조회하는 컬럼명 앞에 기술한다. 에러가 날일이 없다. 조회의 옵션으로 같은값을 하나로 간주할떄 사용하는 거니까.

• distinct가 아닐떈 all 옵션이 default로 되어있어 별도로 표시하지 않아도 된다.

• 문법) select all/distinct 컬럼명,컬럼명,,,

• 조회결과를 생성할때 같은값이 존재하는지 판단하고, 존재시 출력하지 않는다. 다른컬럼과 같이 조회시에는 모든컬럼의 값이 같은 경우에만 중복으로 인식하고 출력하지 않는다.

 

 

6.order by절

 

• 입력된 레코드를 오름차순또는 내림차순으로 정렬할때 사용하는 구문으로 기본적으로 오름차순(작은값=>큰값)정렬이다. 따라서 asc는 생략가능, 내림차순인 desc는 기술.

• select의 가장 마지막에 기술하며, 모든값 정렬 가능(데이터형 상관없이)

• -문법) order by 정렬할컬럼명 asc/desc , 앞컬럼이 중복을가질때 두번째 정렬할 컬럼명 asc/desc,,,,

• 숫자는 정렬을 잘하나 문자열이 숫자형태의 값을 가지면 자릿수정렬. 1 3 100 33일때 1 100 3 33이 됨.

• 정렬시 null값이 가장 커, 오름차순일땐 마지막에 내림차순일땐 가장먼저출력된다.

• 문자의 오름차순정렬은 사전순이며, 날짜의 오름차순은 빠른 날짜(오래된 날짜)부터 늦은 날짜로, 최신날짜가 큰값이다. 

   

 

 

1.함수

 

-자주사용될 코드를 미리 정의해두고, 필요한 곳에서 호출하여 사용.(중복 줄임)

-조회하는 컬럼, where, insert 등의 구문에서 어디든 사용가능하다.

-문자열 함수 : length, upper, lower소문자로 바꾸는 함수, instr, substr글자를 자른다, concat글자를 붙인다, initcap 첫글자를 대문자로 등,trim,ltrim,rtrim,lpad,rpad

-수학 함수 (수치 함수) : sin, cos, tan, round반올림, ceil 올림, floor, trunc, abs

-변환 함수 : to_char, to_date, to_number (뒤의 데이터 타입으로 변경?)

-조건 함수 : decode

-null 변환 : nvl null로 바꾸어줌.

-그룹 함수 : count, nar, min, sum, arg

-날짜 함수 : months_between

 

 

 

-문자열 함수

•길이 구하기 : lenght(문자열, 컬럼명) ->길이를 반환
•모든 영어 대문자로 변경 : upper(문자열)
•모든 영어 소문자로 변경 : lower(문자열)
•인덱스 얻기(오라클은 1부터시작) : instr(문자열, 찾을 문자열)->해당 자릿수로 출력(없으면,0)
•문자열 자르기 : substr(문자열, 시작인덱스, 자를 글자수)-> 1부터 자릿수를 세어 적고, 자를글자수를 적지않으면 시작부터 끝까지 출력
•공백 제거 : 앞뒤 공백제거 : trim(문자열), 앞공백만 제거 : ltrim(문자열), 뒷공백만 제거 : rtrim(문자열)-> 사이 공백은 제거하지 못하고, 문자열기준 왼쪽을 left약자로 붙인것
•첫글자를 대문자로 변경 : initcap(문자열)->대문자도, 소문자도 첫글자만 대문자로 변경되고, 띄어쓰면 그첫글자도 대문자가 된다.

•문자열 결합 : concat(문자열, 붙인문자열)->여러개 붙이려하면 길어진다는 단점(||와 같다)
•왼쪽으로 문자 채우기 : lpad(문자열, 총자릿수, 채울문자)->문자열이 자릿수바이트 만큼의 크기가 아닐때, 문자열의 왼쪽으로 채울 문자를 채워 넣는다. 이때 한글은 2바이트로 계산.
•오른쪽으로 문자 채우기 : rpad(문자열, 총자릿수, 채울문자)->문자열이 총자릿수 만큼의 크기가 아닐때, 문자열의 오른쪽(Right)으로 채울문자를 부족한 바이트만큼 채워 넣는다. 이때, 한글은 2바이트이고, 1이 남으면 채우지 못한다.

•dual table : 가상테이블, 눈에 보이지는 않지만 입력된 데이터로 컬럼을 생성하며 조회하는 일을 한다.

 

 

 

 

-수학 함수

•sin(값), cos(값), tan(값)
•절대값_음수를 양수로 : abs(값) -> 마이너스값을 마이너스를 뺀 정수값으로 출력
•반올림 : round(실수에 대한 값, 반올림을 할 자릿수) ->닷(.)을 기준으로 정수수분이 -3,-2,-1 이고 소수부분이 1,2,3이다.(2를 넣으면, 3번째 자리의수를 반올림해 2자리를 남기고, -1을 넣으면 1의자리를 반올림하여 0을만든다.

•올림 : ceil(값) : 무조건 소수점 1자리에서 올린다. 0을 빼고는 다 올려버림.

•내림 : floor(값) : 무조건 소숫점 1자리에서 내린다.

•절삭 : trunc(값, 자를자릿수) -> 실수부는 그자리 다음을 자르고, 정수부는 그자리를 자른다.(-1일때 1원 자리 절삭)

 

 

-변환 함수

•null 변환 : nvl(값, null이었을때 보여줄값) -> 값이 null 이면 보여줄값을 출력하고, 단, 보여줄값은 값에들어가는 결과 데이터타입이 같아야한다.(널이아니면 값을 반환)

 

•문자 변환 숫자: to_char(숫자인 값, 0/9) -> 문자가 아닌것을 문자열로 변환시켜주는 것으로, 0일땐 채우고, 9일땐 자릿수만 맞추어 출력. 사람이 보기 편하게 만들어주고, 단 문자열은 연산불가

 

 

•문자 변환 날짜 : to_char(날짜, pattern) -> 날짜를 원하는 형식의 문자열으로 변환시킬수 있으며,  pattern => y(Y) : 년, m(M) : 월, d(D) : 일, h(H) : 시, mi : 분, s : 초, day : 요일 (목요일), dy : 요일 (목), q : 분기, am : 오전, 오후..? 로 패턴안에서 패턴이 제공하는 문자열이 아닌 문자열을 사용할경우 쌍따옴표로 묶어 사용.

시간의 경우 그냥쓰면 12시간제, hh24로 넣으면 24시간으로 나온다. 또한 패턴이 너무 길면 에러가 발생한다. 이경우 나누어서 출력하면 ok~

월과 일은 mm 과 dd로 적어야 하며, 값이 yyyyyy로 넘어가도 나오긴한다. 시간정보는 sysdate로 저장한 경우에만 존재하며, 문자열 형태로 저장한값은 시간정보가 존재하지 않는다.

날짜도 크고 작은 경우가 비교되며, 오름차순이 빠른날부터 나오므로 주의해야 한다.

데이터형을 주의 해야하고 문자열일시 ''홀따옴표를 이용하는게 좋다.

=>to_char 함수의 경우 꼭 날짜나 숫자값일때 변경할수있고, 문자형일땐 에러가 난다.

 

•문자열을 날짜형으로 변환 : to_date(문자열, pattern) ->위에나온 패턴과 같으며 문자열과 패턴의 배열을 같게하여 이숫자가 년도인지, 날짜인지 알려주어야 한다.

날짜데이터형에 데이터를 넣을때 날짜형식의 문자열이면 to_date를 쓰지 않아도 잘 들어간다.

•숫자로의 변환 : to_number('숫자형식의 문자열') ->문자열이 숫자형식이면 계산이 안되야 하지만 오라클이 형변환을 잘해주어서 쓸일이 잘없다.(문자열이 숫자형식이 아닌건 안됨)

 

 

 

-조건 함수

•비교해서 다른값을 내보내는것으로 비교값과 출력값 짝으로 이루어 진다.

•decode)

•PL/SQL에서는 사용하지 못하고, 컬럼의 값이 비교값과 같으면 그에 따른 출력값을 반환하는 일을 한다.
•비교하여 실행될 코드가 짧거나 간단한 경우에 사용한다. 문자열도, 연산되는 값도 ok

•문법)  decode(컬럼명, 비교값, 출력값, 비교값, 출력값,,,,,,,비교값이 없을때 출력값)

 

•case)

•select의 조회컬럼에 사용하며, 컬럼의 값을 비교하여 코드 수행시에 사용한다.

•비교하여 실행될코드가 길거나 복잡한경우 사용한다.

•문법) select case 컬럼명 when 비교값 then 실행코드,

                                 when 비교값 then 실행코드,

                                 when 비교값 then 실행코드,

                                 when 비교값 then 실행코드,

                             else 비교값이 없을때 실행될 코드

 

                             end alias   =>alias의 경우 컬럼명이 길어짐으로 출력해줌.    

 

-집계 함수

      •컬럼값을 묶어서 보여줄때 사용, 한행이 조회

•여러행이 조회되는 컬럼과 사용하게 되면 Error (함수사용시 1건, 일반 컬럼이 오면 n건으로 불일치)가 발생하며, group by와 함께 사용하여 그룹별집계를 할수 있다.

•모두 집계함수를 이용하여 한행이 나오면 출력가능.

•주의 - null값이 나오면 제외하고 수행한다. & where절에서는 사용할수 없다.

•count, sum, avg, max, min모두 컬럼을 다 합쳐 하나를 내보낸다.

 

•count함수 : count(컬럼명) -> 조회되는 레코드의 수를 셈, null은 세지 않는다. 컬럼명대신 * 는 모든레코드 카운트

•sum : sum(컬럼명) -> 컬럼값의 합

•avg : avg(컬럼명) -> 컬럼값의 평균

•max : max(컬럼명) -> 컬럼값의 가장 큰 값

•min : min(컬럼명) -> 컬럼값중 가장 작은 값

 

-날짜 함수

•월 더하기 : addMonths(날짜, 더할개월수) -> sysdate+100은 일에 더해지고, 개월을 더하려면 이 함수를 사용하여야 한다.

•날짜간 개월수 : monthsbetween(날짜, 날짜) -> 큰일자 - 작은일자 이어야 하며, 반대는 마이너스가 나온다. 주로 휴먼계정에 사용되고, 이때, 큰일자란 말그대로 수가더 큰 일자이다. 값은 달기준으로 나온다.

 

 

 

1.Rank

 

• rank()^over()와 row_number()^over() 두가지가 있으며, 조회된 레코드의 순위를 표현할때 사용.

• 단순순위 구하기 : rank() over( order by 순위를 정할컬럼명 asc/desc) =>중복순위 발생(1,1,3,4)

• 그룹별 순위 구하기 : row() over( [partition by 그룹으로 묶을 컬럼명] order by 순위정할컬럼명 asc/desc) -> partition by 그룹으로묶을 컬럼명은 생략가능 하며 조회된 레코드를 그룹별 순위로 묶는 역할을 한다. 로우오버는 중복순위가 발생하지 않는다.

• select절에 컬럼명들과 함께 사용하며, alias로 컬럼명 정리가 가능하다.

• 부서별 연봉순위 라고 했을때)  rank() over(partition by deptno order by sal) rank 로 줄수있으며, 중복순위가 싫을때) row_number() over(partition by deptno order by sal) rank로도 사용가능하다.

2.group by절 함수_rollup(), cube()

 

• 조회되는 레코드를 같은값이 나오는것들을 묶음(조회시 조회되는 컬럼의수가일치하는 컬럼들이어야함)

• rollup과 cube 함수가 있으며, 집계의 합인 소계와 총계를 구하나 출력의 순서가 다르다.

• rollup은 group by의 확장된 형태로 사용하기가 쉬우며 병렬로 수행이 가능해 효과적이고, 시간과 지역처럼 계층적 분류를 포함하고있는 데이터의 집계에 적합하다.

• cube는 결합가능한 모든 값에 대하여 다차원적인 집계를 생성하게 되므로 rollup에 비해 다양한 데이터를 얻는 장점이있는 반면에, 시스템에 부하는 많이주는 단점을 가진다.

• 보통 그룹으로 묶이는 컬럼값이 하나이상인 경우에 사용한다. 하나면 소계와 총계가 같기때문!

• 요즘 DBMS들은 group by절 사용시 자동정렬 기능이 없어 order by절을 사용해야한다.

• rollup으로 하나의 컬럼만 그룹핑 했을땐, 모두 자동정렬이 되지만, 두개의 컬럼을 했을땐 정렬되지 않는다. ...=> Rollup, Cube, Grouping Sets결과에대한 정렬이 필요한 경우 Order by 절에 정렬칼럼을 명시!!!

• 날짜 데이터의 경우 그룹화 할때에 시간의 초까지 같으면 같은 그룹으로 묶을수 있다.

•  

• rollup(a,b)는 a and b 별, a별 로 합을 구하고(항목a의 그룹을 집계하고 항목b별로 정렬후ㄴㄴ집계후, 소계를 출력하고 다시반복, 마지막엔 총계출력

• cube(a,b)는 a and b별, a별, b별로 합을 구한다.

•  

• -rollup : 그룹별 집계의 합(소계) 와 총계를 레코드 출력후인 가장 마지막에 보여준다.컬럼나열순서중요

• 문법) group by rollup (컬럼명,컬럼명,,);

• group by 수행시 생성되는 표준집계->앞 컬럼별 모든 뒷컬럼의 subtotal->Grand totoal 순으로 이루어지며, 계층간 집계는 이렇게 정렬해 주지만, 계층내 group by수행시 생성되는 표준집계는 정렬을 지원하지 않는다(각순서의 계층내 정렬을 위해서는 별도의 order by절 사용)

• -cube : 소계와 총계를 레코드 출력전 먼저 출력, 각그룹의 모든 경우의 수에대한 소계와 총계를 구한다. (결합가능한 모든값에 대하여 다차원 집계 생성, 양쪽의 query에서 모두)

• 문법) group by cube(컬럼명, 컬럼명,,);

• 표시된컬럼명들에 대한 계층별 집계를 구할수 있고, 컬럼들과 계층구조인 rollup 과달리 평등하여 순서가 바뀌어 정렬의 순서느 바뀔수 있어도 데이터 결과는 같다. 결과에 대해 정렬이 필요한 경우 order by절에 정렬컬럼을 적어야 한다.

• cube만 한경우에는 총계->두번째 컬럼의 소계 -> 첫번째 컬럼에 대한 소계 순으로 정렬되어 나타나지만 rollup과 같이 각부분에 대한 정렬은 order by절을 사용해야한다.

• 만약 order by절 이용시 rollup과 비슷하게 나타나며 단지 두번째 컬럼의 소계가 밑에 추가되는것처럼 보인다.

 

3.Subquery

• 쿼리문안에 조회 쿼리문을 작성하여 사용하는것.(sql문안에 포함되어있는 또다른 sql문)

• 단수행(단일행), 복수행(다중행) 두가지가 있으며, 단수행은 조회쿼리가 한행을 조회하고, 복수행은 여려행을 조회하는 것을 일컫는다.

• 서브쿼리가 먼저 수행되고, 수행된 값 또는 여러 행을 조회한다.

• create, insert, update, delete, select 에서 사용가능하고 어디가 되었든 괄호에 넣어 사용한다.

• subquery는 메인쿼리의 컬럼을 모두 사용할수 있지만, 메인쿼리는 서브쿼리의 컬럼을 사용할수 없다.

• 서브쿼리는 order by절을 사용하지 못한다. select절에 한개만 올수 있어 메인쿼리의 마지막에 준다(?)

• 단일행은 (=,<,>,<=,>=,<>)의 비교연산자가 있고, 다중행은 (in, all, any, some, exists)가 있다.

• select절에 오는 1행반환은 scalar sub query라고 하기도 하고, from절에 오는 sub query 는 inline view(인라인뷰), where절은 그냥 sub query라고 한다. 메인쿼리와의 연관성에따라서도 구분한다.

• -create subquery

• 테이블의 복사

• table을 조회한 레코드의 결과를 가지고 테이블을 생성할때 사용되고, 이때 조회되는 쿼리의 컬럼명, 데이터형, 크기, 레코드로 생성된다.(But, not null을 제외한제약사항은 복사되지 않는다.)

• 문법) create table 새테이블명 as (select...)   

• 조회되는 행의 수에따라 단수행인지 복수행인지 정해짐.

• 테이블의 구조만 복사시에는 where조건이 F가 되면되고, 테이블의 구조와 레코드까지 복사시에는 where절이 T가 되면된다.(1=0=>F, 1=1=>T)

• -insert subquery

• 단수행

• 다른(내) 테이블의 값을 조회하여 추가할때. (복수행이면 당연히 Error!)

• -문법) insert into 테이블명(컬럼명,,,) values(값,,(select~~)..);  

• +commit 필요!

• 서브쿼리에서 여러개의 컬럼이 조회되는경우 와 컬럼은 하나이지만 여러행이 조회되는경우 Error발생.

• 복수행

• 다른테이블의 여러행, 여러칼럼의 값을 추가할때, 주로 정산작업에 사용.

• -문법) insert into 테이블명 (컬럼명....(select...));

• +values 쓰지 않으나 commit은 insert이기 때문에 필요

• 컬럼명들을 쓰지않고 서브쿼리만으로 넣을수도 있다.

• -updatae subquery

• 단수행 subquery만 가능, 다른테이블의 컬럼값으로 변경작업을 할때 사용.

• (다른테이블의 값을 참조하여 현재 테이블의 값을 변경할때.)

• set절에는 꼭 단수행이어야하고 복수행을 쓰려면 where절에 in 을 사용해 쓰지만, 권장ㄴㄴ

• 여러개 결과가 나오다 원하지 않는데 끼어있을 가능성이 있다.

• 문법)  update 테이블명

•          set 컬럼명 = 값, 컬럼명 = (select 단수행)

•          where 컬럼명 in (복수행)

• Error : 단수행서브쿼리에 복수의 행이 발생하면, 컬럼이 여러개 발생하면

• -delete subquery

• 단수행 subquery만 가능, 다른 테이블값을 참조하여 레코드를 삭제할때.

• 문법) delete from 테이블명 where 컬럼명 =(select~)

• in써서 복수형 쓸수있지만 역시 엉뚱한게 삭제될수 있어 권장ㄴㄴ

• -select subquery

• 단수행일때, scalar sub query)

• - select 절에 오는 서브쿼리로 한번에 결과를 1행씩 반환

• -보통 조인과 비슷하게 사용되고, 그중 outer join과 비슷.(서브쿼리 결과가 없을땐 null)

• -ex) select name,    ( select dname

from dept2 d where e.deptno= d.dcode) "DNAME"

from emp2 e;

=>dept2테이블의 deptno를 기준으로 밖테이블의 emp2의 deptno가 같을때 dname을 뽑아 DNAME라는 컬럼명으로 조회하고, emp2테이블의 name과 출력한다.?(join driving과 같은줄..

=>emp2테이블에서 이름과 부서명을 조회할때 부서명이 없고 부서번호만 존재한다면 부서테이블에서 부서번호를 찾아 부서명을 가져와야 한다. 이런문제가 되겠고, 따라서 결과값을 제공받는쪽이 맞겠다.

 

• -그러나 반드시 같은건아니고, main집합과의 조인컬럼 기준으로 중복된값이 없는 경우일때에만 (dept2테이블은 부서번호dcode 컬럼기준으로 중복된 값이 없음)결과 동일.

• -중복된값이 존재한다면 distinct나 group by를 사용.

• -데이터의 양이 적을경우 join보다 scalar join이 더 빨라 사용한다.

• -메인쿼리를 수행한후 필요한 값을 서브쿼리에 제공->서브쿼리수행을 위해 해당블록을 메모리로딩->주어진 조건으로 값찾음->메모리할당후 메인쿼리가 끝날때까지 반복

• -빠른이유가 바로 메모리에 만들어져있는 값을 찾아오기때문..!

• -단수행은 컬럼부분에 서브쿼리가 들어가며, 다른테이블의 값을 조회하여 보여줄때와 다른테이블의 값으로 조건을 만들때 사용한다.

• -문법)

• select 컬럼명, (select...)=>스칼라 서브쿼리

• from 테이블명

• where 컬럼명=(select...)=>그냥 서브쿼리

• join과 비슷하기때문에 서브쿼리에서 조건을 줄떄에 테이블명.컬럼명 으로 같은이름의 컬럼명을 구분한다.

• 복수행

• -조회한 결과를 가지고 재조회 할때 사용

• -문법)

• select 컬럼명,,,         <실제 테이블이아니라 조회된 인라인뷰의 컬럼명

• from (select 컬럼명... from 테이블명)=>인라인뷰

• =>슈퍼쿼리의 조회결과를 from절안의 인라인뷰인 서브쿼리에서 재조회

• ex) 게시판의 정보를 불러올때, 3000건이 있으면, 10건씩 묶어 한페이지에 출력하는 모든게시글>10개 묶어 나열>이런 조회의 재조회에 많이쓰임

• 다중행 서브쿼리이니 비교연산자가 in, exists, any, all 을 사용해야한다.

• https://blog.naver.com/dlscjf1505/220864929709

3-1.쿼리의 외부값 입력받기

• 사용법) &변수명

• &입력시 prompt가 동작, 입력창에 입력한 값이 쿼리에 대입되어 수행된다. 쿼리문에 그대로 들어가 문자열인경우 ''(홀따옴표)로 묶어주어야 에러가 발생하지 않는다.

• 주석처리 안에도 쓰지않는것이 좋다

 

4.rownum

• 가상컬럼으로 조회된 결과에 순차적인 번호를 부여하는 컬럼

• rownum이 order by절보다 먼저 수행되어 rownum과 같이 쓰면 순차적인번호가 섞여버린다.

• where절에서 조건으로 사용시 1부터 차례대로는 조회가 되지만, 다른번호로 비교를 하면 조회되지않는다.(rownum between 2 and 5면 Error!)

• 문법) select rownum, 컬럼명...

• select마다 사용가능하고, 서브쿼리에서 사용시 서브쿼리의 rownum을 갖고나오는게 아니라 밖의조회된컬럼순으로 번호가 붙게 된다.(덮어씀=>이때에 alias를 사용하면 사용가능하다.)

 

5.Constraint(제약사항)

• 컬럼에 필요한 값만 입력받을때 사용하는것으로 테이블생성시 부여하거나, 만들어두고 alter를 이용해서 부여할 수 있다.

• user_constraints oracle dictionary(오라클에서 제공하는 정보가 들어있는 테이블)에서 제약사항을 확인할수 있고 / select * from user_constraints로 확인 가능하다.

• primary key, foreign key, unique, check, not null 이 있고, default도 함께 많이 본다.

• key의 제약사항을 설정하면 테이블간의 관계가 설정된다.(P부모, F자식 관계)

• 제약사항명은 테이블명처럼 유일해야하고, 테이블이 지워지면 제약사항도 함께 지워진다.

• 문법) create table 테이블명 (

•            컬럼명 데이터형(크기) constraint 제약사항명 제약사항의종류,

•              ~,                            =>컬럼명과 정의하면 컬럼단위 제약사항(Column Level Constraint)

•              ~,                               (제약사항명은 생략가능하나, SYS~로 명명되어 구분이 어렵다.)

•              ~,                                (오라클은 자동생성부여해줌)

•            constraint 제약사항명 제약사항종류(적용컬럼),

•             ~,                  =>테이블 정의 끝에 정의하면 테이블단위 제약사항(Table Level Constraint)

•             ~

•         );