SQL문 스터디 - Oracle - SQL 기본 / 오라클 기본 함수/ 그룹함수 / 테이블 구조 생성 변경 및 제거/ 무결성 제약조건 / 조인/ 서브쿼리 [개발스터디 기몬]

DB의 데이터를 가져오려면 반드시 SQL문을 사용.
database 필수 SQL (Structuerd Query Language 구조적 질의 언어) 
관계형 데이터베이스 시스템(RDBMS)에서 자료를 
관리 및 처리하기 위해 설계된 언어. 

at eclipse /////>>
preference - data management - sql editor - checked execute SQLs between delimiters ; > apply
preference - data management - sql editor 
- sql files/scrapbooks - checked ( sql file is default opened ~...) / manual  > apply

select from where 문 

오라클 데이터베이스 개념 
데이터베이스에 접속하기 위해서 사용자 생성.
형식 create user (사용자id)  identified by (비밀번호);

사용자 생성
ex)> create user tester1 identified by 1234;
역할 부여 
   >  grant connect, resource to tester1;

데이터 타입과 제약 조건

데이터 타입
스칼라
수치 numer 
문자 char/ varchar
날짜 date

데이터 타입 
CHAR 데이터 타입 - 고정길이 데이터타입
VARCHAR2 데이터 타입 - 가변길이 데이터타입

공통점은 문자열을 저장하는 것. 데이터를 정해놓고 저장하고 
데이터가 입력된 이후 나머지 공간은 공백으로 채워짐

number는 10이나 12.3과 같은 수치 데이터를 저장하며 다음과 같은 형식으로 사용. 
형식 : numer ( precision, scale )

NUMBER(5) 12345
NUMBER(8, 2)  123456.78 


데이터 무결성과 제약 조건 
NOT NULL 제약 조건 - null은 할당 받지 않은 값, 
아직 무엇인지 모르는 값을 의미.
기본 키 제약 조건 - 이상(anomaly) 현상이 일어나지 않도록 하기 위한 
방법으로 제공되는 것이 기본키로 제약조건 
기본키로 지정한 컬럼은 널(null)값이나 중복된 값을 
가질 수 없기 때문에 개체가 무결성을 유지할 수 있음.
외래 키 제약 조건 - 관계형 데이터 베이스는 데이터가 중복되어 저장하는 것을 
지양한다 .. 


Select문 (DQL) 
데이터를 조회하기 위한 SELECT문 
형식 select * from table_name;

> select [distinct] [*, column(alias), ...] from table_name;


특정 데이터만 보기 
emp 테이블의 empno, emname 컬럼 내용만 출력하기

데이터를 조회하기 위한 SELECT 문
산술 연산자
ex) select ename, sal, sal*12
     from emp;

null 처리를 위한 nvl 함수 
> select ename, comm, sal*12+nvl(comm, 0)
> FROM emp;

 

SQL 기본 

산술sql

-- null 을 정수 0으로 바꾸어주는 nvl 
select comm, nvl(comm, 0)
  from emp;
  
-- 중복되는 값을 제거하고 표시 distinct 
-- result set에 중복된 값이 있으면 하나만 선택해서 보낸다 //로우를 대상으로함. 
select distinct deptno, deptno+10
  from emp;

as 로 컬럼에 별칭 부여하기 

" " 네임드 별칭 부여는 더블 따옴표로 ., 
' ' 데이터 를 사 용할 때는 싱글 따옴표 사용


select 
where
update

조건식 참인 경우만 사용하는 것이  where sql 문

where절 //조건절
선택적 데이터 추출.. 

논리 연산자 AND , OR, NOT 

between and 연산자 


in 연산자 - in은 or을 여러개 사용할 때 쓰인다. 

like 연산사 - 와일드카드 % , _

null인 값을 찾기 위한 is null 
// is not null 


-=========================-

정렬을 위한 order by 절

데이터 정렬에 쓰이는 order by 
2개 이상의 데이터 정렬.. 

order by // (오름차순)asc , (내림차순)desc
order by 절의 정렬 방식.  

정렬 방식에 여러 가지 조건 제시
SELECT *
FROM emp 
ORDER BY hiredate DESC, ename ASC;

==========================

집합 연산자 
union , difference, intersect 
union all, difference all, intersect all  

-- 합집합 연산자 union  입력 테이블과 결과 테이블에서 중복된 레코드가 배제됨.
select * from emp where deptno = 10
union
select * from emp where sal >= 500;

-- union all 연산은 union과 같지만 중복된 데이터가 제거되지 않음. 
select * from emp where deptno = 10
union all
select * from emp where sal >= 500;

-- 교집합 intersect
select * from emp where deptno = 10
intersect
select * from emp where sal >= 500;

--차집합 minus
select * from emp where deptno = 10
minus
select * from emp where sal >= 500;

select * from emp where sal >= 500
minus
select * from emp where deptno = 10;

=======================

오라클 주요 함수 
dual table // 가상 테이블 . 
간단한 산술연산 결과를 한줄로 얻기 위해 오라클에서 제공하는 테이블..

--  dual table
select 10/3 from dept;
select 10/3 from emp;
select 10/3 from dual;

-- DUAL 
-- 모든 유저가 접근할 수 있는 결과를 한줄로 얻기 위해서 
-- 오라클에서 제공해주는 테이블 // 함수 자체를 이해할 때 활용도가 높다. 
select * from dual; // (>> 결과  dummy 1 : x)

숫자 함수 
: 숫자 데이터를 처리하기 위한 함수.. 

========================

jdbc 프로그램에서 할수 있는게 기본 

실무에서는 자동화 프레임워크를 사용.  

at eclipse 
scott user - connect. 

windows - preferences - data management 
- sql editor - sql files/  setting 
oracle scott xe sql file is default opened after creation manual 

windows - preferences - general - keys - search : sql 
- execute selected text : shift + enter - apply and close
- execute current text : ctrl+enter - apply and close


=============================

--
-- DDL (Data Definition Language)
-- create
-- alter
-- drop
-- truncate
--
-- DQL ( Data Query Language)
select * from alpha;

--
-- CRUD ( Create Retrive Update Delete)
-- DML ( Data Manipulation Langueage)
-- insert
-- update
-- delete
--


============================

at eclipse 
main 작성 시 database connection -----------
DriverMagager.getConnection(url, user, password);

unhandled exception type SQLException 
: add throw ~~~~

DriverMagager.getConnection(url, user, password);
how to get url user password?? 
data source explorer - scott 우측마우스 - properties 
- Driver Properties - general / connection url / user name / password

============================

 

오라클 SQL 기본 
오라클 주요 함수 

dual table 

숫자 함수 

절대값 구하는 abs 함수 

소수점 아래를 버리는 floor 함수 

특정 자릿수에서 반올림하는 round 함수 

특정 자릿수에서 잘라내는 turnc 함수 

나머지 구하는 mod 함수 

문자 처리 함수 
- 문자 처리 함수는 자형의 값을 조작하겨 변환된 문자 값을 
반환하는 함수로서 대소문자간의 변환을 위한 함수와 
문자열을 정교하게 조작하는 함수로 나눔..

uppper - 대문자 변환
lower - 소문자 변환
initcap - 첫 글자만 대문자로, 나머지 글자는 소문자로 변환

문자 길이를 구하는 함수 
length 문자의 길이를 반환 (한글 1byte) 유니코드 기준 ..
lengthb 문자의 메모리를 차지하는 바이트 수를 구한다. 
>>> select length ('문자') from dual; 

문자 조작 함수 
concat 문자값 연결 
substr 문자를 잘라 추출한다
substrb 문자를 잘라 추출한다// 특수한 경우만.. 사용
instr 특정문자의 위치 값을 반환한다
instrb 특정문자의 위치 값을 반환한다//
lpad, rpad  입력 받은 문자열과 기호를 정렬하여 
특정 길이의 문자열로 반환한다. 

LPAD/ RPAD 함수 
LPAD(LEFT PADDING) 함수는 컬럼이나 대상 문자열을 
명시된 자리수에서 오른쪽에 나타내고, 
남은 왼쪽 자리를 특정 기호로 채움.

RPAD(RIGHT PADDING) 함수는 컬럼이나 대상 문자열을 
명시된 자리수에서 왼쪽에 나타내고, 
남은 오른쪽 자리를 특정 기호로 채움.

형변환 함수 
변환 함수는 자료형을 변환시키고자 할 때 사용하는 
형 변환 함수로는 to_number, to_char, to_date 가 있음. 
to_char : 날짜형 혹은 숫자형을 문자형으로 변환
to_date : 문자형을 날짜형으로 변환한다.
to_number : 문자형을 숫자형으로 변환한다. 

to_char ( 날짜 데이터, '출력형식')
yyyy  년도 표현 4자리
yy  년도 표현 2자리
mm  월을 숫자로 표현
mon  월을 알파벳으로 표현 
day 요일 표현
dy 요일을 약어로 표현 
am또는 pm   오전오후 시각표시
hh  또는 hh12  시간 (1~12)
hh12 24시간으로 표현 (0~23)
mi 분 표현
ss 초 표현

숫자형을 문자형으로 변환하기

날짜형으로 변환하는 to_date 함수 

숫자형으로 변환하는 to_number함수

날짜 함수 
날짜 함수는 date형에 사용하는 함수 
결과 값으로 날짜 또는 기간을 얻는다. 
기간은 주로 일 단위로 계산되지만, 월 단위로 계산되는 경우도 있다. 

날짜 함수: 
sysdate 시스템에 저장된 현재 날짜 반환
months_between  두 날짜 사이가 몇 개월인지를 반환
add_months  특정 날짜에 개월 수를 더한다. 
next_day 특정 날짜에서 최초로 도래하는 인자로 받은 요일의 날짜를 반환한다
last_day 해당 달의 마지막 날짜를 반환한다
round 인자로 받은 날짜를 특정 기준으로 반올림한다.
trunc  인자로 받은 날짜를 특정기준으로 버린다.

날짜형 데이터 더하기와 빼기와 같은 연산을 할수 있음. 


null을 다른 값으로 변환하는 함수 NVL
>> nvl(expr1, expr2) 
수치 데이터를 사용한 nvl 함수 
>> nvl (comm, 0)
: comm 컬럼에 null이 저장되어 있으면 이 컬럽 값을 0으로 변환. 


선택을 위한 decode 함수 
 decode 함수는 프로그램 언어에서 가장 많이 사용되는 
switch case 문과 같은 기능.. 


조건에 따라 서로 다른 처리가 가능한 case
: case 함수는 다양한 비교 연산자를 이용하여 조건을 
제시할 수 있으므로 범위를 지정할 수도 있음..
case 함수는 프로그램 언어의 if else if else 와 유사한 구조를 갖는다. 



=================================

전제 그룹 함수 

count (*) 전체 카운트 


at eclipse 

windows - prefernces - datamanagement - sql development 
- sql results view - max ~ count change.. (to 1000) 

 

그룹함수 
단일행 함수와 그룹 함수의 차이 
single / group 

그룹 함수는 하나 이상의 행을 그룹으로 묶어 연산하여 
총합, 평균 등 하나의 결과로 나타냄. 

database connetion check /./ 
at cmd >>> sqlplus myusername/mypassword@Host/urltype??



SQL문 순차적으로 처리되지 않는다 .. 
select * 
  from emp
 where deptno=10
 order by sal desc;

in this case... from line starts first, 
and the where line starts seconds after that .,, 
select comes , lastly,, 'order by sal desc' comes  


그룹함수는 null을 제외하고 동작한다.
(null을 포함하지 않는다. )
만일 count 함수에 count(*)처럼 *를 적용하면 
전체 로우 수를 구하게 됨. 



select  count(*), count(comm), sum(comm) // projection 
  from emp // 1번으로 수행.
 where comm is not null; // 2번으로 수행. 




제 1 정류화? 
1개의 컬럼 안에는 한개의 정보만 담을 수 있다. 
각각의 컬럼은 각각의 정보를 소유한다. 

데이터 그룹 group by 절 
특정 컬럼을 기준으로 그룹 함수를 사용해야 할 경우 어떤 컬럼 값을 
기준으로 그룹함수를 적용해 줄지를 결정해주어야 하는데 이때 사용하는 것이
group by  절.  ( : 요약한 테이블을 구해냄. )

그룹
 where - from에서 온 low 결과 제한
 having - group by에서 온 group 결과 제한
where 와 having의 조건식 작성을 같다.

그룹 결과 제한 having 절
select절에 조건을 사용하여 결과를 제한할 때는 where절을 사용하지만
그룹의 결과를 제한 할때는 having 절을 사용. 



SELECT문의 수행순서=====
1. from 
2. where
3. group by
4. having 
5. select
6. order by


------------------

쿼리 작성. + 연결 .. 

서브쿼리   - sql문 안에 sql문.. 

group by 를 쓰는 순간 low는 없어짐 
group을 요약한다 min / mix사용이 유용하다..
having 사용해서 그룹함수에 
제한을 주고 조건문을 작성한다. 


====================


테이블에 내용을 추가하는 Insert문
테이블의 내용을 추가, 수정, 삭제하는 dml와 트랜젝션

테이블의 무결성 제약조건. 
table primary key 
 -- table primary key 테이블 제약조건이 있기 때문에 
 -- 중복된 값을 넣을 수 없다. /// 제약조건에 위배되는 것을 제한한다. 
 -- 오류 . 무결성 제약조건에 위배 됨.  

==================
update

테이블에 내용을 수정하는 update문
update - where
where절을 생략하면 전체 테이블의 모든 행이 수정 업데이트됨. 


===================
delete

테이블 내용을 삭제하는 delete문
delete - where
where절을 생략하면 전체 테이블의 모든 행이 삭제됨. 

===================
Transection

TCL (transection control language)

commit 과 rollback  
영구저장하기 위해서 commit 문 실행하기 

복원을 위해서 rollback 실행하기 

===================

 

oracle join 

1. 조인 join 
조인의 필요성
조인 : 관계형 데이터베이스에서는 테이블 간의 관계가 
중요하기 때문에 하나 이상의 테이블이 빈번히 결합하여 사용됨. 
한개 이상의 테이블에서 데이터를 조회하기 위해서 사용되는 것이 조인. 

SQL에서는 두 개 이상의 테이블을 결합해야만 
원하는 결과를 얻을 수 있을 때 한 번의 질의로 
원하는 결과를 얻을 수 있는 조인 기능을 제공.   

2. Cross Join 
특별한 키워드 없이 select문의 from 절에 사원(emp) 테이블과 
부서(dept) 테이블을 콤마로 연결해서 연속으로 기술하는 것. 

조인의 종류 
: 조인 결과가 의미 있으려면 조인할 때 조건을 지정해야 함. 
조인 조건에 따라 조인의 종류가 결정됨

종류  설명
Equi Join  동일 칼럼을 기준으로 조인.
Non-Equi Join  동일 칼럼이 없이 다른 조건을 사용하여 조인
Outer Join  조인 조건에 만족하지 않는 행도 나타냄
Self Join  한 테이블 내에서 조인.

3. Equi Join 
 : Equi Join은 가장 많이 사용하는 조인 방법. 
조인 대상이 되는 두 테이블에서 공통으로 존재하는 칼럽의 값이 
일치되는 행을 연결하여 결과를 생성하는 조인 방법.

Equi Join 에 AND 연산하기 

> select ename, dname
> from emp, dept
> where emp.deptno = dept.deptno;

> select ename, dname 
> from emp, dept
> where emp.deptno = dept.deptno 
> and ename = '사원이름'; 

컬럼의 모호성 해결 
 ) 칼럼 명 앞에 테이블 명을 명시하기 
테이블에 별칭 부여하기
> from (테이블명)emp (별칭)e, (테이블명)dept (별칭)d

Non-Equi Join   
: 조인 저건에 특정 범위 내에 있는 지를 조사하기 위해서 where절에
조인 조건을 = 연산자 이외의 비교 연산자를 사용함.
대소 등급  <=, >= 


4. self join
self join:  조인은 두 개 이상의 서로 다른 테이블을 서로 연결하는 것뿐만 
아니라 , 하나의 테이블 내에서 조인을 해야만 원하는 자료를 얻는 경우가 생김.
Self join 이란 말 그대로 자기 자신과 조인을 맺는 것을 말함. 





==========================

select /5 
from  /1
where /2
group by  /3
having  /4
order by  /6

where/ group by / having 은 Select 이전에 수행되어야 한다. 


single row function / group function 

when you connect the data with the program it's mainly 
use by 'select', 'insert', 'update', 'delete'

db의 무결성 data integrity 
data는 무결성을 유지해야 한다  database 의data가 논리적으로 합당해야함


data integrity 를 지키기 위해서는 transaction이 확실하게 이루어져야함.

transaction - commit or rollback , savepoint 
transaction ' all or nothing '

db의 변경을 시작하면 transaction 이 계속해서 수행된다. 
(commit과 rollback으로 transaction 이 끝나기까지 계속해서 transactioning 되는 상태)

=============================

데이터의 효율성 - 데이터를 중복시키지 않는 것. 
테이블을 분산해서 저장한다 - why ? 중복을 피하기 위해서.

cross join 이후에  inner join으로 데이터의 의미있는 중복을 제한 한 
데이터를 가져오는 것 . 조인 조건은 where / on 으로 inner join에 대한 조건을 준다. 

cross join(물리적인 데이터 통합)으로 메모리상에 데이터가 합해지고 
이후에 inner join 으로 유의미한 데이터를 조건을 통해서 출력 할 수 있음.
중복 또는 잘못된 정보를 제한 join 정보를 가져옴. 

join 조건,, 
inner join - on / where
inner join - equi join / non equi join 

sql 표준  - inner join .. 

cross join을 잘 이해해야 inner join/ outer join을 이해할 수 있다. 

outer join은 inner join에 더한 정보를 전부 포함 시키는 join
outer join 에는 right / left / full join이 있다. 


Join 종류  cross join - inner join / outer join 

 

서브쿼리
1. 서브쿼리의 기본개념 
: 하나의 select 문장의 절 안에 포함된 또 하나의 select 문장. 
그렇게에 서브쿼리를 포함하고 있는 쿼리문을 메인쿼리, 
포함된 또하나의 쿼리를 서브쿼리라고 함.

- 서브쿼리는 일반적으로 select, insert, update, delete와 같은 
DML(data manipulation language)문 모두에서 사용할 수 있다. 

서브쿼리의 전형적 형식...
> select select_list  // main query
> from table_list
> where column = ( select expression  // sub query 
from select_list
where table_list )

서브쿼리 규칙 
- 서브 쿼리 안에 서브 쿼리가 들어갈 수 있음. 이것을 (nesting)네스팅이라 하며, 
메모리가 허용하는 한 무제한으로 중첩할 수 있음. 
- 메인쿼리에서 서브쿼리의 결과값을 조건으로 사용할 때 some, any 또는 all 연산자를 
사용하지 않는 일반적인 경우에슨 서브쿼리에서는 하나의 레코드 값만 리턴해야 한다. 
그러므로 대부분의 경우 서브쿼리에는 group by, having 문을 사용할 수 없다.
- 서브쿼리에서 order by 문은 top 연산자와 함께 있을 때만 사용 가능. 
- 서브쿼리에서 select하지 않은 칼럼은 주 쿼리에서 사용할 수 없다.


단일 행 서브쿼리
단일 행 (single row) 서브쿼리는 수행 결과가 오직 하나의 로우(행, row) 만을 
반환하는 서브쿼리를 갖는 것을 말함. 
ex ) '이문세'의 부서명 알아내기 
> select dname 
> from dept
> where deptno = ( select deptno 
from emp
where ename = '이문세' );

_____ 단일 행 (single row) 서브쿼리에서는 수행 결과가 오직 하나의 로우(행, row) 만을 
반환되는 서브 쿼리의 결과는 메인 쿼리에 보내게 되는데 메인 쿼리 where 절에서는 단일 행 
비교 연산자인 =, >, <, >=, <=, <>를 사용해야 함. 



서브 쿼리에서 그룹 함수의 사용
* 평균 급여보다 더 많은 급여를 받는 사원 출력.. 
> select ename, sal
> from emp
> where sal > (  select avg(sal)
         from emp);


다중 행 서브쿼리 
: 다중 행 서브 쿼리는 서브 쿼리에서 반환되는 결과가 하나 이상의 행일 때 사용하는 서브쿼리
다중 행 서브쿼리는 반드시 다중 행 연산자 (multiple row operator)와 함께 사용. 
IN
ANY, SOME
ALL
EXIST

in 연산자 : 메인 쿼리의 비교 저건에서 서브 쿼리의 출력 결과와 하나라도 일치하면
메인 쿼리의 where 절이 참이 되도록 하는 연산자..

all 연산자 : 메인쿼리의 비교 저건이 서브쿼리의 검색 결과와 모든 값이 일치하면 참. 
찾아진 값에 대해서 and 연산을 해서 모두 참이 되면 참이 되는 셈. 
> ALL 은 " 모든 비교값 보다 크냐" 고 묻는 것이 되므로 최대값보다 더 크면 참. 

any 연산자 : 메인 쿼리의 비교 조건이 서브쿼리의 검색 결과와 하나 이상만 일치하면 참
> ANY 는 찾아진 값에 대해서 하나라도 크면 참이되는 셈 
찾아진 값 중에서 가장 작은 값 즉, 최소값 보다 크면 참이 됨.

exists 연산자 : 서브 쿼리문에서 주로 사용하며 서브쿼리의 결과 값이 참이 나오기만 하면 
바로 메인 쿼리의 결과 값을 리턴한다. 
서브쿼리의 결과 값이 존재하지 않는다면 메인 쿼리의 어떤 값도 리턴되지 않는 문장. 
쿼리 속도 면에서는 서브쿼리 사용시 in 보다는 exists가 훨씬 빠름. 
  exists의 반대말로 not exists도 사용가능.

1. 서브쿼리를 이용한 테이블 생성, 데이터 조작.

서브쿼리로 테이블 생성하기  create table 문에서 서브쿼리를 사용하며 
이미 존재하는 테이블과 동일한 구조와 내용을 갖는 새로운 테이블을 생성할 수 있음. 

> create table emp05
> as
> select * from emp
> where deptno=10;

테이블의 구조만 복사하기
> create table emp06
> as
> select * from emp where 1=0;

: where 1=0; 조건은 항상 거짓. 이를 이용하여 
테이블의 데이터는 가져오지 않고 구조만 복사하게 됨.

서브쿼리로 데이터 삽입하기 
  테이블에 내용추가하기 
> insert into dept01
> select * from dept;

서브쿼리를 이용한 데이터 변경
테이블 내용 변경. 
> update dept01
> set loc = ( select loc
      from dept01
        where deptno = 40)
> where deptno = 20;

테이블 내용 확인
> select * from dept01;

서브쿼리를 이용한 데이터 삭제 
데이터 삭제하기
> delete from emp02
> where deptno = ( select deptno
  from dept
 where dname = '영업부');



======================================