◆집합 연산자



먼저 실행

--set_a테이블 생성 

create table set_a(

    a number(5) 

);


-------1,2,3,4,5,6 삽입 ---------


insert into set_a values(1);

insert into set_a values(2);

insert into set_a values(3);

insert into set_a values(4);

insert into set_a values(5);                              

insert into set_a values(6);



--set_b테이블 생성

create table set_b(

    a number(5) 

);      


-------4,5,6,7,8,9 삽입 ---------

       

insert into set_b values(4);

insert into set_b values(5);

insert into set_b values(6);

insert into set_b values(7);

insert into set_b values(8);

insert into set_b values(9);





1)union   = 합집합 

:합집합이지만 복된 부부은 제거가 되있는

*쿼리에서 select 문으로 선택된 열의 개수 데이터 유형은 동일해야 한다.

*이름은 같지 않아도 된다.

*null은 중복 검사 시 무시되지 않습니다.

select * from set_a

union

select * from set_b

 

select employee_id, job_id,salary

from employees

union

select employee_id, job_id,0

from job_history;

 

2)union all   = 

:합집합이지만 중복된 부분도 그대로 출력 (union보다 빠르다)

select * from set_a

union all

select * from set_b

 

3)intersect  =  교집합

:테이블간의 중복되는만 부분 출력

*쿼리에서 select 문으로 선택된 열의 개수와 데이터 유형은 동일해야 한다.

*이름은 같지 않아도 된다.

*null은 무시되지 않습니다.

select * from set_a

intersect

select * from set_b

 

4)minus =  차집합

:현재 테이블에서 비교 테이블과 중복된 부분을 제거한 데이터를 출력

*쿼리에서 select 문으로 선택된 열의 개수와 데이터 유형은 동일해야 한다.

*이름은 같지 않아도 된다.

select * from set_a

minus

select * from set_b

 

◆Join

 데이터베이스 내의 여러 테이블 레코드를 조합하여 하나의 열로 표현한 .(위키백과)





Oracle Join

Ansi Join


1.     equal join

2.     outer join

3.     non equal join

4.     sef join

5.     threeways join


1.     inner join ~ on = join ~ on

2.     left outer join ~ on = left join ~ on

3.     right outer join ~on = right join ~ on

4.     full outer join ~ on = full join ~ on

5.     natural join

6.     join ~ using

 

Oracle Join

 

1) equal join

select ename,dname

from emp e, dept d

where e.deptno = d.deptno;

관계 (=)연산을 사용할 수 없을 떄 즉, join조건에 포함되지 못해(deptno = null) 나오지 못한 데이터가 있을 때

 

2)outer join

 

a) left outer join

select ename,nvl(dname,'대기발령')

from emp e, dept d

where e.deptno = d.deptno(+);


b) right outer join

select dname as deptName, ename

from dept d,emp e

where D.DEPTNO = E.DEPTNO(+)

order by deptName;


c) full outer join

오라클에서는 지원 x

 

3)non equal join

select ename

       ,sal

       ,s.grade

from emp e, salgrade s

where e.sal between s.losal and s.hisal;

 

4)self join

select e1.ename as mentee

      ,e2.ename as mento

from emp e1, emp e2

where e1.mgr = e2.empno(+)

order by mento;

 

 

Ansi Join

 

a)    inner join ~ on

select last_name

      ,department_name

from employees e join departments d

on e.department_id = d.department_id and e.last_name = initcap('&NAME')

order by department_name, last_name;

 

b)    left outer join ~ on, right outer join ~ on 

select last_name

      ,department_name

from employees e left outer join departments d

on e.department_id = d.department_id

order by department_name, last_name;

 

c)     full outer join ~ on

select department_name

      ,last_name as last_name

from employees e full outer join departments d

on e.department_id = d.department_id

order by department_name desc, last_name;

 

 

d)    natural join

select last_name,department_name

from employees natural join departments;

 

l  natural join 은 사용하지 않습니다.

테이블과 테이블의 공통된 컬럼을 알아서 합쳐주는 것 인데, 잘못된 결과가 나올 위험이 크다.

e)    join ~ using 

select last_name, department_name

from employees join departments

using (department_id);

 


p.s)마지막으로 카르테시안 조인(=크로스 조인)이 있습니다.


크로스 조인 = 모든 경우의 수를 나열하는 것.

select last_name,department_name

from employees cross join departments;

사원 수 (20) * 부서 수 (8) = 160개의 행이 리턴된다.

◆서브 쿼리


메인이 아닌 쿼리 = 서브쿼리


서브 쿼리 - 1)단일 행 쿼리

   2)다중 행 쿼리




1)단일 행 서브쿼리

Abel의 급여보다 많거나 같은 사원들

select last_name, salary

from employees

where salary >= (select salary

                 from employees

                 where last_name = 'Abel');

 

Taylor와 같은 직업이고 Taylor보다 높은 급여를 받는 사원

select last_name, job_id, salary

from employees

where job_id = (select job_id

                from employees

                where last_name = 'Taylor')

and   salary > (select salary

                from employees

                where last_name = 'Taylor');

 

2)다중 행 서브쿼리

 

any

: 서브 쿼리에서 반환되는 값 들 중 하나라도 조건을 만족한다면 띄움

select employee_id, last_name, job_id, salary

from employees

where salary < any (select salary

                    from employees

                    where job_id = 'IT_PROG')

and job_id <>'IT_PROG';

 

all

: 서브 쿼리에서 반환되는 모든 값과 비교

select employee_id, last_name, job_id, salary

from employees

where salary < all (select salary

                    from employees

                    where job_id = 'IT_PROG')

and job_id <>'IT_PROG';

 

exists

: 테이블에 특정 행이 있는지 여부에 따라 결과가 달라짐

SELECT * FROM departments

WHERE NOT EXISTS

(SELECT * FROM employees

WHERE employees.department_id=departments.department_id);

 


※group by 와 select문의 distinct 알아보기

◆그룹 함수


1) 그룹 함수 유형


*기본적으로 다음 함수들은 null값은 모두 무시합니다.

*그룹 함수는 where절로 제어할 수 없습니다.

 함수

설명 

 avg(salary)

salary의 평균

 count(salary)

행의 개수 

max(salary)

salary의 최대값 

min(salary)

salary의 최소값 

stddev(salary)

salary의 표준편차 

sum(salary)

salary의 총합 

 variance(salary)

salary의 분산 



2) group by



테이블을 기준으로 묶는 것.


a)

select department_id

from employees

group by department_id;


b)그룹 함수와 같이 쓸 때        (*그룹 함수와 같이 쓸 때 그룹 함수가 아닌 컬럼은 group by로 묶어줘야 한다.)

select department_id,sum(salary)

from employees

group by department_id;



c)having

: 그룹 함수의 조건 정도로 생각하면 될 듯 싶은데.. (where의 절(조건)을 group by에 선언 가능하나 group by의 조건을 where절로 가져가지는 못한다.)

SELECT department_id, MAX(salary)

FROM employees

GROUP BY department_id

HAVING MAX(salary)>10000 ;



◆함수 사용하기


1)문자 함수


 함수

결과 

 설명

 lower('ABCDe')

 abcde

 해당 문자열을 모두 소문자로 바꾼다

upper('ABCDe') 

 ABCDE

  해당 문자열을 모두 대문자로 바꾼다 

 initcap('abcde')

Abcde 

해당 문자열의 맨 앞부분만 대문자로 바꾼다 

 concat('hi','jane')

hijane 

두 문자열을 붙여준다  

 SUBSTR('hijane',1,2)

hi 

 문자열의 부분을 잘라 가져온다

 length('hijane')

 6

문자열의 길이를 반환 

 instr('hijane', 'a')

 4

해당 문자가 몇 번째에 있는지 반환 

 lpad(salary,10,'*')

*****50000 

지정 문자수를 채우고 남은 왼쪽 공간은 원하는문자로 채움 

 rpad(salary, 10, '*')

50000***** 

 지정 문자수를 채우고 남은 오른쪽 공간은 원하는문자로 채움 

replace('JACK and JUE','J','BL') 

 BLACK and BLUE

 지정문자를 원하는 문자로 대체

 trim('H' FROM 'HelloWorld')

 elloWorld

선행 문자 혹은 후행 문자를 지움 



2)숫자 함수

 함수

 결과

 설명

 round(45.926,2)

45.93 

지정한 소수점 자리에서 반올림 

trunc(45.926,2) 

45.92 

지정한 소수점 자리 뒤는 버림 

mod(1600,300) 

100 

선행 숫자를 후행 숫자로 나누고 남은 나머지 




3)날짜 작업

a)sysdate (현재 날짜와 시간 반환)

select sysdate

from dual; 



 b)날짜 연산

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS

FROM employees

WHERE department_id = 90;



c)날짜 조작 함수

 함수

설명 

 months_between(date1,date2)

 date1과 date2 사이의 월수

 add_months(date,n)

date 월수에 n을 추가 

 next_day(date,'char')

date다음에 오는 지정된 요일의 날짜 반환 

 last_day(date)

date에 해당하는 날짜가 있는 월의 말일 날짜를 반환 




◆데이터 제한 및 정렬


1) where 


SELECT last_name, job_id, department_id

FROM employees

WHERE last_name = 'Whalen' ;


2) 비교 연산자


특정 표현식을 다른 값이나 표현식과 비교하는 조건에서 사용


 연산자

의미 

같음 

큼 

>= 

크거나 같다 

작다 

<= 

작거나 같다 

between ~ and  

사이(=범위) 

in(,,,,) 

리스트 범위 

like 

문자 패턴 검색 

is null 

null의 여부 



SELECT last_name, salary

FROM employees

WHERE salary <= 3000 ;


3)between a and b (a와 b사이의 값)

SELECT last_name, salary

FROM employees

WHERE salary between 3000 and 5000;


4)in (리스트 범위 제한)


select employee_id,

        last_name,

        salary,

        manager_id

from employees

where employee_id in (100,101,102);

 SELECT LAST_NAME, JOB_ID

FROM EMPLOYEES

WHERE JOB_ID

NOT IN('IT_PROG','ST_CLERK','SA_REP');



5)like 


(a) r로 끝나는 문자

 select first_name

        , last_name

from employees

where first_name like '%r';  -- 'r%'로 하게 되면 r로 시작하는 문자


(b)2번째 단어가 'o'인 문자

select last_name

from employees

where last_name like '_o%';


(c)문자열 안에서 _(언더바) 찾기 

select last_name

from emptest

where last_name like '\_' ESCAPE '\'; -- escape 지정하기 \ 뒤에 붙은 _ 언더바는 문자 그대로의 언더바로 받음.


6)And , Or


And : 구성 요소 조건이 모두 참인 경우

Or : 구성 요소 중 하나라도 참인 경우

not : 참이라면 거짓으로 바꾸고 거짓이라면 참으로 바꾸기


 

SELECT LAST_NAME, JOB_ID

FROM EMPLOYEES

WHERE JOB_ID

NOT IN('IT_PROG','ST_CLERK','SA_REP');


7) 우선 순위 규칙


 연산자

의미 

산술 연산자

연결 연산자 

비교 조건 

is null, like, in 

5. 

between 

같지 않음 

not 

and 

or 



8)order by a   ( a기준으로 데이터 정렬하기)


desc : 내림차순

asc   : 오름 차순(디폴트임 즉, desc나 asc라고 쓰지 않으면 자동으로 asc로 선택됨.)

SELECT last_name, job_id,department_id, hire_date

FROM employees

ORDER BY hire_date DESC; -- hire_date 를 기준으로 내림차순 정렬



9)치환 변수(&=앰퍼샌드)


변수 앞에 앰퍼샌드를 붙이면 유저가 값을 입력하도록 함.


SELECT employee_id, last_name, salary, department_id

FROM employees

WHERE employee_id = &employee_num ;


10)define (미리 정의해둔 값)


&에 넣을 값을 미리 설정해놓는 것.


DEFINE employee_num = 200

SELECT employee_id, last_name, salary, department_id

FROM employees

WHERE employee_id = &employee_num ;

UNDEFINE employee_num --undefine 꼭 해줘야 함.


※사용하는 오라클 소프트웨어 = Oracle Database 11g Express Edition , 클라이언트 툴 = sqldeveloper



◆기본 Select문


1) 전체 열 검색

SELECT *

 FROM departments;


2) 원하는 열 검색

SELECT department_id, location_id

 FROM departments;


3) 산술식

: (+,-,*,/) → 더하기, 빼기, 곱하기, 나누기

SELECT last_name, salary, salary + 300

 FROM employees;


4)alias 

: 쿼리 결과에서 열의 이름을 바꿀 수 있습니다.

SELECT last_name as "이름"

FROM employees; 


5)연결 연산자

: 쿼리 결과문의 결과들을 문자형태로 연결

SELECT last_name||job_id AS "Employees"

FROM employees;

SELECT last_name ||' is a '||job_id

AS "Employee Details"

FROM employees;


6)q 연산

:나타낼 문자에   ' 를 쓰고 싶을 때

SELECT department_name || q'[ Department's Manager Id: ]'

|| manager_id

AS "Department and Manager"

FROM departments;


7)중복 행 제거

:같은 값을 가지는 행을 하나만 띄우기

SELECT DISTINCT department_id

FROM employees;


8)테이블 구조 보기

 DESCRIBE employees


sqldeveloper 설치 후 접속 계정을 만드는 과정에서 IO 오류: Undefined Error 대처법입니다.


1) 포트 바꿔보기?

다른 포트로 바꿔보시는 것도 좋을 것 같습니다. ex) 1522


2)윈도우 도메인 계정 문제


먼저 내 컴퓨터 이름을 알아보자  내PC -우클릭 후 '속성' - 오른쪽에 보면 컴퓨터 이름이 나와있을 것입니다(저는 "desktop-mo")


다음으로 C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN에 들어가서 tnsnames.ora 파일을 열어보자

xe= 부분이 보일텐데 그 안에 [HOST=본인 컴퓨터 이름]  으로 되어있는지 확인해주세요.


다음은 listener.ora를 열어봅시다 LISTENER= 부분 마찬가지로 host가 본인 컴퓨터 이름으로 되어있는지 확인바랍니다.


[출처 : https://blog.naver.com/codingspecialist/221126868614]


3)

cmd로 먼저 계정 생성한 후 sqldeveloper로 들어가보기

지난 포스팅에 이어서 오늘은 cmd가 아닌 툴(sql developer)을 사용하도록 하겠습니다.


먼저 sql developer를 설치하도록 합시다!


1. www.oracle.com 에 접속하여주세요


2. 메뉴 - Downloads - SQL developer에 들어가주세요.


3. Accept 라디오 버튼 누르시고 다운로드 버튼을 눌러주세용 (운영체제는 본인의 사양에 맞게 선택해주세요.)


4. 다운받은 파일의 압축을 푸시게 되면 sqldeveloper.exe파일이 보이실겁니다. 더블 클릭!!


5. 전에는 계정을 cmd에서 user create~로 만들었지만 sqldeveloper 환경에서는 왼쪽의 아이콘을 통하여 간단하게 만들 수 있습니다.


6. 접속 이름과 사용자 이름 비밀번호를 입력하신 후 테스트를 클릭합니다. 왼쪽 하단에 성공이라는 메시지가 뜬다면 저장을 눌러주세요.


7. 방금전 접속 아이콘이 있던 아래에 n1 계정이 생긴 것을 알 수 있습니다. 더블 클릭(=접속)해주세요. 참 쉽습니다.!! 끗





저번 글에서는 설치를 진행 했었습니다.


그렇다면 이번 글에서는 cmd에서 oracle을 다뤄보도록 합시다^^.


먼저 지난 번에 배웠었던 접속 방법으로 다들 접속을 해주세요



계정을 먼저 만들어보도록 할게요.


create user [아이디] identified by [비밀번호]; 




계정을 만들었다면 그 계정에 권한을 주게 할 차례입니다.


 grant connect,resource to [아이디];



이제 접속을 만든 계정으로 로그인을 해봅시다


conn [id]/[pw] 




다음은 아주 유명한 샘플인 scott.sql을 집어 넣어보도록 하겠습니다.  


C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin 경로에 들어가게 되면 scott.sql이라는 파일이 있습니다.


cmd에 @를 먼저 입력하신 후에 파일을 cmd창으로 밀어넣게 되면 다음과 같은 명령어가 생길것입니다. 


@C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql



sql문을 잘 실행했다면 다음과 같이 어떤 테이블들이 있는지 확인해보도록 합시다

select * from tab; 




*저는 7개의 테이블들이 보이네요.


한번에 보기 쉽게 하기 위해 설정을 조금 해보겠습니다


한줄에 출력되는 문자의 갯수를 120으로 설정해보겠습니다.


set lines 120


그리고 슬래쉬 (/)를 입력하게 되면 방금전에 했던 select * from tab; 명령어가 다시 실행될 것입니다. 아마도 바로 전에 명령어를 다시 부르는 것인가 보네요.


*조금 더 깔끔하게 나오네요. ㅎ


하지만 TNAME이 쓸데 없이 너무 긴 바람에 이것도 보기가 흉하니 컬럼의 크기를 조금 조정하겠습니다.


  col tname for a35


테이블의 tname의 보여주는 최대의 갯수를 35로 설정하였습니다.






이것으로 cmd를 통해서 어느정도 접근을 해봤는데요!! 재미지죠?? 


그래도 cmd창으로만 하기에는 너무 불편하니 아무래도 GUI를 사용하는 것이 제일 편할 것 같습니다. 

다음 편에서부터는 GUI를 사용하여 배워보도록 하겠습니다. 


그럼 20000~

+ Recent posts