select를 이용한 검색방법 및 SQL plus 명령어

(0) 복습

1. 오라클 설치 - 계정 로그인 - 권한필요(connect, resource) - 테이블(표) - 데이터 수정(insert, update, delete, select)

db구현 : 관리자가 운영 -> 설지, 공간 설정

sql활용 : 일반 유저 -> 기존에 이미 저장된 데이터를 어떻게 검색하고 활용하는가(3장, 4장)

Q1. 작업한 내용(sql명령어~)를 파일로 저장하는 법

<sql plus 명령어>

형식)spool 경로포함에서 경로지정~.sql or .txt ->20170427.txt

EX) spool L:\2017NCS\1.db\print\20170427.txt  ->문서 생성, 내용은 없음

       select * from tab; 

      spool off -> 저장 종료, 문서에 select~ 내용이 저장됨

Q2. 데이터 검색하는 방법

형식) select 보고자하는 필드명(=컬럼명),,,(*)

          from 테이블명,,,

          where 조건식(검색하고자 하는 필드명 연산자 검색할값(문자일시 '');

1. 검색하고자 하는 테이블 형식 알아보기

형식)desc emp(테이블명)

이름                                                 널?             유형
----------------------------------------- -------- ----------------------------
EMPNO(사원번호)                  NOT NULL NUMBER(4)
ENAME (사원명)                                       VARCHAR2(10)
JOB(업무, 직책)                                        VARCHAR2(9)
MGR(직속상관 사원번호)                         NUMBER(4)
HIREDATE(입사일)                                  DATE -> 날짜
SAL(급여)                                               NUMBER(7,2) -> 전체 7자리 중에서 소숫점 2자리까지 표현(실수로 저장)
COMM(커미션,보너스)                           NUMBER(7,2)
DEPTNO(부서번호)                                NUMBER(2)

2. 해당 테이블 검색하기

형식) select * from emp(테이블명)

-> 해당 테이블이 나타남

Q3. emp테이블에서 사원번호(empno), 이름(ename), 업무(job)만 검색하는 SQL을 작성하세요.

형식) select 보고자하는 필드명(=컬럼명),,,(*);

EX) select empno, ename, job from emp;

Q4. 오라클의 별칭 기능(edit 사용하기)

테이블명은 물리적으로 변경이 안됨 허나 가상으로 별칭을 만들수 있음

<문3의 sql문장 중에서 사원이름(ename)과 업무(job)를 별칭을 부여해서 출력하기>

* 오라클은 sql 구문을 편집하는 기능이 있음 -> edit or ed

* 명령어 약어

connect=conn

describe=desc

1. edit 입력

 SQL Buffer : 마지막 SQL 구문이 저장된 오라클의 메모리 영역

마지막으로 사용한 명령어가 편집장에 나타남

2. 편집하기

형식)select 필드명 as "변경할단어(별칭명)",

                   필드명 별칭명

EX) select empno, ename as "사원이름", job 업무 from emp

(슬래시 사이 공백은 아직 문장이 끝나지 않음을 의미)

/

* 단어 사이 공백 또는 특수기호를 표현하려면 ""를 반드시 사용

ex) "사원 ★"

3. 구현결과

SQL> ed
file afiedt.buf(이)가 기록되었습니다

1* select empno, ename as "사원이름", job 업무 from emp
2 (슬래시 사이 공백으로 문장이 끝나지 않아 2가 뜸) ;

EMPNO 사원이름 업무
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK

EMPNO 사원이름 업무
---------- ---------- ---------
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK

14 개의 행이 선택되었습니다.

Q5. 수정된 SQL 구문을 실행하는 방법

1) / 슬래시 : buffer에 저장된 SQL 명령어를 실행하여 결과를 보여줌. 즉, 입력된 내용을 그대로만 출력

2) run or r : btuffer에 저장된 SQL 명령어를 한번 보여주고나서 출력. 명령어까지 함께 출력 

Q6. 계산필드

: 기존에 가지고 있는 필드에 수식을 도입하여 가상의 필드를 생성

<emp 테이블에서 직원별 연봉을 계산해서 출력하시오

사원이름(ename), 업무(job), 급여(sal), 연봉(sal*12) 순으로 출력하는 SQL문을 작성하시오.>

형식) select 필드명, 필드명2, 필드명3, 수식 from 테이블명;

EX) select ename, job, sal, sal*12 from emp;

- 참고사항

ENAME    JOB       SAL    SAL*12
---------- --------- ---------- ----------
SMITH CLERK 800 9600
ALLEN SALESMAN 1600 19200
WARD SALESMAN 1250 15000

1) 구현시 수식으로 생성된 필드명은 명령문(SAL*12) 그대로 나타남

2) edit을 활용하여 수식 필드명을 별칭으로 생성할 수 있다.

ex) select ename, job, sal, sal*12 연봉 from emp

Q7. 하나 이상의 필드 데이터를 결합시키기 -> 가상의 필드를 생성하여 출력

* 언어(오라클 제외)

문자하나일 때 : 'a'

문자두개이상 : "ab"

-> "ab"+"cd" => "abcd" (문자의 결합기호)

* 문자열 결합기호(오라클)

+ 더하기 : || 

1. 필드 결합시키기

형식) select 필드명1 || 필드명2 as "별칭" from 테이블명;

EX) select ename || job as "이름 업무 결합" from emp;

이름 업무 결합
-------------------
SMITHCLERK
ALLENSALESMAN
WARDSALESMAN

2. 필드명 구분하기(단어 사이 띄어쓰기) -> 편집창 활용

형식) "ab"+" "+ "cd" => "ab cd" 

1) edit 창 열기

2) 편집하기

     select ename || ' ' || job as "이름 업무 결합" from emp
     /

3) 결과

이름 업무 결합
--------------------
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN

[where 조건식 배우기]

Q8. emp 테이블에서 급여가 3000이상인 직원을 찾아서 그 직원들의 이름, 업무, 급여 순으로 출력하는 SQL를 작성하시오

형식)

select 필드명1, 필드명2

from 테이블명

where 조건필드명 연산수식;

<급여가 3000이상>

급여 = 조건필드

3000이상 : 수식을 활용, >=3000

*관계연산자 : 대소를 비교해주는 연산하

>, >=, <, <=, =(정확한 일치), !=(부정), <>(부정)

1. SQL 작성하기

EX)

select ename, job, sal

from emp

where sal >= 3000;

2. 연산을 수정할 때 edit을 활용

- 3000이 아닌 자를 찾을 때

1) edit

2) 연산자 변경 where sal != 3000

Q9. emp테이블에서 업무(job)이 Maneger인 사원을 찾아서 그 사원의 사원번호, 이름, 업무 순으로 출력하시오.

1. SQL문 작성하기

EX)

select empno, ename, job

from emp

where job = 'manager';

2. 결과 

SQL> select empno, ename, job
2 from emp
3 where job = 'manager';

선택된 레코드가 없습니다.

-> 영문자는 대소문자를 비교해서 찾는다. manager를 소문자로 작성하여 검색결과가 없음.

3. 대문자로 manager 수정하기 

1) 다시 SQL작성하기

select empno, ename, job
from emp
where job = 'MANAGER';

2) 편집창(edit)을 열어 manager 수정하기

4. 결과

EMPNO ENAME JOB
--------- ---------- ---------
7566 JONES MANAGER
7698 BLAKE MANAGER
7782 CLARK MANAGER

Q10. emp테이블에서 급여가 1300~1800사이의 급여를 갖는 직원 정보(사원이름, 업무, 급여) 출력

*범위 연산자

between A and B

1. SQL 작성하기

select ename, job, sal

from emp

where sal between 1300 and 1800;

2. 범위연산자를 관계연산자로 바꾸기(Q.16)

select ename, job, sal
from emp
where sal >=1300 and sal <=1800;

3. 범위 내에 있지 않은 값 찾기(부정)

select ename, job, sal
from emp
where sal not between 1300 and 1800

Q11. emp 테이블에서 81/02/22 이후에 입사한 사원의 이름과 입사일을 출력하시오

* 날짜검색 : 문자처럼 검색 ' '

1. 구문 작성

select ename, hiredate

from emp

where hiredate >='81/02/22';

Q12/13. 자주 사용하거나 검색이 되는 SQL구문을 파일로 저장하여 불러오기(SQL plus 명령어: save)

1. 저장하기

형식) save '경로\파일명(.sql or .txt)'

-> 마지막 작업한 구문만 저장

2. 불러오기

1) get 경로/파일명(.sql of .txt) : SQL 구문을 불러옴, 내용을 먼저 확인 출력할지 말지 선택 가능

get L:\2017NCS\1.db\print\datesch.sql

-결과 화면

SQL> get L:\2017NCS\1.db\print\datesch.sql
1 select ename, hiredate
2 from emp
3* where hiredate >='81/02/22'
SQL>

2) @경로/파일명 : 명령한 데이터를 순서대로 출력

@L:\2017NCS\1.db\print\datesch.sql

- 결과 화면

SQL> @L:\2017NCS\1.db\print\datesch.sql

ENAME HIREDATE
---------- --------
WARD 81/02/22
JONES 81/04/02
MARTIN 81/09/28
BLAKE 81/05/01
CLARK 81/06/09
SCOTT 87/04/19
KING 81/11/17
TURNER 81/09/08

Q14. 관계연산자, 범위연산자(between A and B, Not) : 문자열 검색

1.로그인 SQL 구문 : 정확한 데이터 검색

-> member(회원) : id(회원번호), passwd(암호), name(회원이름)~

 nup/1234 -> 로그인버튼을 눌렀을 때

-> select * (필드명을 전부 보여주나 많을 경우 로딩이 느림) or

    select id, passwd

    from member 

    where id='nup' and passwd='1234'

[ like 연산자 ] 정확히 찾고자 하는 데이터를 모르는 경우

-> 모르는 단어 : %, _로 연결해서 찾는다(맵핑)

1) % : 문자열 길이를 정확히 모르는 경우

     - 김% : 김길수, 김외,, -> 김으로 시작하는 사람이름 전부

     - %수 : 수로 끝나는 단어

     - %동% : 동이 포함된 단어 찾기

2) _ : 찾는 문자열 한개와 연결

     - 김__ -> 김길수, 김갑순,,,

Q15. emp테이블에서 사원명이 A자로 시작하는 사원을 찾아서 그 사원의 이름과 급여를 출력하시오(Like 연산자)

형식)

select ename, sal

from emp

where ename like 'A%';

출력)

ENAME SAL

---------- ----------
ALLEN 1600
ADAMS 1100

- A가 들어가는 사원 모두 : like '%A%'

[ OR 과 AND ]

* 조건식이 여러개일 경우

~ where 조건식1 or 조건식2

~ where 조건식1 and 조건식2

1) OR : 둘 중 하나만 만족해도 결과 출력

2) AND : 조건식 둘다 참인 경우 적용, 즉 모두 만족해야 결과 출력

Q16. 급여가 1000에서 3000사이에 있는 사원의 이름과 급여를 관계 연산자를 사용하여 출력하기

select ename "사원명", sal as 급여
from emp
where sal >=1000 and sal <=3000; (=between 1000 and 3000)

[ 오라클에서 SQL구문을 편집하는 방법 ]

1. edit창을 열어서 수정 : 편집할 양이 많을 경우 주로 사용

2. change 명령어 : 단어 중심으로 변경, 특정한 단어만 변경할 경우

1) 라인번호를 확인 :

- list(l) -> 전체행을 출력

SQL> l
1 select ename "사원명", sal as 급여
2 from emp
3* where sal >=1000 and sal <=3000

- l 라인번호 -> l 3

SQL> l 3

3* where sal >=1000 and sal <=3000

- 공백 라인번호 -> [ 3]

SQL> 3
3* where sal >=1000 and sal <=3000

2) change 사용하기

형식) change(c)/변경전 단어명/변경단어

EX) change/and/or

결과)

SQL> change/and/or
3* where sal >=1000 or sal <=3000
SQL> /

사원명 급여
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250

Q17. emp테이블에서 두번째 글자가 L자가 나오는 직원의 정보(이름, 급여)를 출력하시오

select ename, sal

from emp

where ename like '_L%';

* not 명령어 : 명령어 앞에 not을 붙임

no like~, not between A and B~, not in~

[ Null을 위한 연산자 ] in, null -> not null

* in : 같은 조건식을 묶어줌

 null :값이 들어가 있지 않은 상태, 값이 없는 상태의 필드를 검색

not null : 반드시 값이 들어가 있는 상태, 제약조건(회원가입 폼에서 필수입력 폼과 같은 반드시 데이터가 입력되야 함)

' ' : 빈문자는 저장은 되고 눈에 보이지 않음

Q18. emp테이블에서 사원번호가 7782, 7788, 7844인 사원을 검색하여 해당 사원에 급여와 연봉을 출력하시오

-> 범위 연산자와 or 연산자와 연관이 있음

1) OR 나열 구문

select sal, sal*12 as 연봉

from emp

where empno=7782 or empno=7788 or empno=7844;

2) In을 활용 구문

select sal, sal*12 as 연봉

from emp

where empno in (7782, 7788, 7844); <-문자일 경우 ('홍길동','과장','부장')

- 값이 없는 필드 검색(is NULL)

형식) select 필드명 from 테이블병

where 검색대상 필드 is NULL

select sal, comm from emp

where comm is NULL;

- 데이터가 들어가 있는 것 검색(is Not NULL)

Q19. 중복된 데이터를 한번만 출력하시오(distinct)

중복된 데이터 : job

형식) distinct 중복필드명

EX) select distinct job from emp;

Q20. emp테이블에서 업무가 PRESIDENT이고(and) 급여가 1500이상이거나(or) 업무가 SALESMAN인 사원을 찾아서 그 사원의 사번, 이름, 업무, 급여 순으로 출력하시오

* and, or이 동시에 나오면 해석에 따라 결과가 달라짐

select empno, ename, job, sal from emp

where job='PRESIDENT' and sal >=1500 or job='SALESMAN';

select empno, ename, job, sal from emp

where job='SALESMAN' or job='PRESIDENT' and sal >=1500; <-우선순위에 따라 두개가 출력됨

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500

* and와 or이 동시에 나올 경우 우선순위를 고려해서 구문 작성

 -> ()를 사용하라는 의미, or 끼리 묶음

*우선순위가 높은 연산자

1) 괄호 ()

2) 연결연산자 ||

3) 비교연산자(=관계) >.>=.<.<=.!=

4) is (not) null, like, not in

5) not between

6) not 논리연산자

7) and 연산자

8) or~

SQL> select empno, ename, job, sal from emp
where job in ('PRESIDENT','SALESMAN') and sal>=1500;

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 1600
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500

---

[ 정렬 ORDER BY ]

형식)

select 필드명,,

from 테이블명

where 조건식

order by 정렬하고자 하는 필드명 [정렬기준(선택)]

-> order by는 항상 공식의 맨 마지막

* 정렬기준

오름차순 : ascending(asc) ㄱ~ㅎ, A~Z

내림차순 : desceing(desc) ㅎ~ㄱ, Z~A (게시판 정렬은 내림차순)

Q21. 계산필드(가상필드)를 활용하여 고액연봉자 순으로 이름, 급여, 연봉을 출력하라(내림차순)

1)

select ename as 사원명, sal 급여, sal*12 as "연  봉"

from emp

order by sal*12 desc;

2) 가상필드(별칭)으로 필드명 입력 가능

select ename as 사원명, sal 급여, sal*12 as "연  봉"

from emp

order by "연  봉" desc;

* 내림차순일 경우는 정렬 기준을 반드시 입력, 오름차순은 상관없음

다중정렬 : 게시판

형식) order by 필드명 (정렬기준), 필드명2 (정렬기준),,,

-> 앞의 필드명 정렬기준부터 우선 정렬

Q22. 위(21)의 emp테이블에서 급여가 많은 순으로 정렬하되 만약에 급여가 같은 사원이 존재한다면 사원명을 내림차순으로 정렬하시오

*정렬하시오는 무조건 오름차순

select ename as 사원명, sal 급여, sal*12 as "연  봉"

from emp

order by "연  봉" desc, ename desc;

사원명 급여 연 봉
---------- ---------- ----------
KING 5000 60000
SCOTT 3000 36000
FORD 3000 36000
JONES 2975 35700
BLAKE 2850 34200
CLARK 2450 29400
ALLEN 1600 19200
TURNER 1500 18000
MILLER 1300 15600
WARD 1250 15000
MARTIN 1250 15000

* order by 기능

1) 가상필드(계산필드/별칭) (Q21)

2) 다중정렬 (Q22)

3) select ~ from 사이에 나오는 필드를 번호로 인식시켜서 정렬할 수 있다(Q22-2)

Q22-2. change와 필드명을 번호로 인식시켜 정렬하시오

select ename as 사원명, sal 급여, sal*12 as 연봉

from emp

order by 연봉 desc, ename desc;

1) 필드명 번호

ename=1 , sal=2, sal*12=3

2) chage 활용하기

SQL> l 3
3* order by 연봉 desc, ename desc
SQL> change/연봉/3
3* order by 3 desc, ename desc
SQL> /

-> ed로 편집창 들어가면 order by 3 desc, ename desc 변경됨 

---

---

[파라미터 인수 질의(query) 검색(매개변수 질의)]

Q23. emp 테이블에서 부서번호가 10번인 사원을 찾아서 그 사원들의 이름, 급여, 부서번호 순으로 출력하되 급여가 많은 순으로 정렬하시오.

1)

select ename, sal, deptno

from emp

where deptno=10

order by sal desc; (= order by 2 desc)

- 같은 검색 필드에 부서번호만 바뀔 경우

where deptno=10 -> 20, 30, 40

2) 자주 사용되는 구분을 save(파일로 저장), 검색되는 값을 집적 입력을 받아서 검색을 하고자 할 때

*입력을 받는 부분 where 필드명(=&필드명)

-> 실행할 때마다 입력문구가 나옴

형식) ed

select ename, sal, deptno
from emp
where deptno=&변수(필드명)
order by sal desc
/

출력)

SQL> ed
file afiedt.buf(이)가 기록되었습니다

1 select ename, sal, deptno
2 from emp
3 where deptno=&deptno
4* order by sal desc
SQL> /
deptno의 값을 입력하십시오: 20
구 3: where deptno=&deptno
신 3: where deptno=20

ENAME SAL DEPTNO
---------- ---------- ----------
SCOTT 3000 20
FORD 3000 20
JONES 2975 20
ADAMS 1100 20
SMITH 800 20

SQL> /
deptno의 값을 입력하십시오:

Q24. emp테이블에서 사원이름이 SCOTT인 직원을 찾아서 직원의 사원번호, 이름, 급여를 출력하는 SQL문을 작성하시오.

select empno, ename, sal

from emp

where ename='SCOTT';

-> 정적인 SQL문장

* 동적인 SQL 문장, 사용자로부터 값을 입력을 받아서 처리해주는 SQL 구문 ex) 로그인

응용) 동적인 SQL문장을 활용하여 ALLEN, TURNER의 정보를 출력하시오

select empno, ename, sal

from emp

where ename='&ename'; <-문자일 경우 '' 삽입

출력)

SQL> ED
file afiedt.buf(이)가 기록되었습니다

1 select empno, ename, sal
2 from emp
3* where ename='&ename'

SQL> /
ename의 값을 입력하십시오: TURNER
구 3: where ename=&ename
신 3: where ename='TURNER'

EMPNO ENAME SAL
---------- ---------- ----------
7844 TURNER 1500

=> 동적으로 입력받을 때 주의사항

1) 숫자데이터 입력의 경우 : &필드명(&deptno)

2) 문자데이터 입력의 경우 : '&필드명'('&ename;)

* 자주쓰는 구문 파일로 저장하거나 매소드 또는 함수로 만들어서 작업한다

SQL> save 'L:\2017NCS\1.db\print\namesch.sql'
file L:\2017NCS\1.db\print\namesch.sql(이)가 생성되었습니다
SQL> @'L:\2017NCS\1.db\print\namesch.sql'
ename의 값을 입력하십시오: ALLEN
구 3: where ename='&ename'
신 3: where ename='ALLEN'

EMPNO ENAME SAL
---------- ---------- ----------
7499 ALLEN 1600