[Oracle] select를 이용한 검색방법 및 SQLPlus 명령어 사용법
<문1> 작업한내용(sql명령어)을 파일로 저장하고 싶다?
형식) spool 경로포함해서 경로지정 ~.sql or .txt =>20200630.txt
SQL>spool c:\webtest\2.db\print\20200630.txt
<문2>id가 1인 홍길동인 데이터를 찾아서 김길동으로 변경하시오. (name이 홍길동인 데이터를 찾아서 김길동으로 변경하시오.)
SQL>select * from prtest;
ID NAME
---------- ----------
1 홍길동 => 김길동
2 테스트
3 임시
4 임시2
5 테스트2SQL> update prtest set name='김길동' where id=1;
1 행이 갱신되었습니다.
sql>select * from prtest
ID NAME
---------- ----------
1 김길동
2 테스트
3 임시
4 임시2
5 테스트2
<문3> name 임시2를 삭제하시오.
형식)
-
delete from 삭제시킬 테이블명; => 테이블의 모든 데이터 삭제
-
delete from 삭제시킬 테이블명 where 조건식 => 조건식에 만족하는 데이터를 삭제
(잘못 삭제되는 경우도 있기 때문에 백업테이블을 꼭 생성)
백업테이블 생성
create table 백업t명(b_테이블명) as select * from 원본t명 where 조건식; //조건에 만족하는 데이터를 찾아서 복사하라 part backup(=부분 backup)
prtes t=> b_prtest 백업테이블을 생성->내용,구조를 똑같이 복사
SQL>create table b_prtest as select * from prtest;id가 3번인 데이터를 prtest에서 찾아서 b_prtest2라는 백업테이블을 생성하라.
SQL>create table b_prtest2 as select * from prtest
SQL> select * from b_prtest2;
ID NAME
---------- ----------
3 임시백업테이블 b_prtest의 모든 내용을 삭제하라
SQL>delete from b_prtest;
SQL>delete from prtest where name='임시2';
1 행이 삭제되었습니다.
SQL> select * from prtest;
ID NAME
---------- ----------
1 김길동
2 테스트
3 임시
5 테스트2
검색
형식) select 보고자하는 필드명,,,,(=컬럼명),,(*)
from 검색할 테이블명,,,
where 조건식(검색하는 필드명 연산자 검색할 값
테이블의 구조를 확인하고 싶다. =>필드?,몇개,자료형으로 구성
=>desc 구조확인할 테이블명
테이블의 내용을 확인하고 싶다.=>select * from 테이블명
계정으로 로그인했을때 그때 작업한 내용을 확인 => select * from tab;
SQL> desc emp
이름 널? 유형
----------------------------------------- -------- ----------------------------
EMPNO(사원번호) NOT NULL NUMBER(4)=>숫자
ENAME(사원명)
VARCHAR2(10)=>영문자(10글자)
JOB(업무 또는 직책) 한글 5글자
VARCHAR2(9)
MGR(직속상관 사원번호)
NUMBER(4)
HIREDATE(입사일)
DATE(날짜)
SAL(급여)
NUMBER(7,2)=>전체 7자리중에서
COMM(보너스) 소수점 둘째자리까지(실수)
NUMBER(7,2)
DEPTNO(부서번호)
NUMBER(2)
sql>select * from emp; //실무X -> where 조건식 사용
//데이터 갯수가 적을때 사용
SQL>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 80/12/17 800
20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300
30
7521 WARD SALESMAN 7698 81/02/22 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 81/04/02 2975
20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400
30
7698 BLAKE MANAGER 7839 81/05/01 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 81/06/09 2450
10
7788 SCOTT ANALYST 7566 87/04/19 3000
20
7839 KING PRESIDENT 81/11/17 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 81/09/08 1500 0
30
7876 ADAMS CLERK 7788 87/05/23 1100
20
7900 JAMES CLERK 7698 81/12/03 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 81/12/03 3000
20
7934 MILLER CLERK 7782 82/01/23 1300
10
14 개의 행이 선택되었습니다.
<문4>emp테이블에서 사원번호,이름,업무만 검색하는 SQL구문을 작성?
sql> select empno,ename,job from emp;
EMPNO ENAME JOB =>필드명이 영어로 만들어져 있다.
---------- ---------- --------- =>한국어로 가상으로 변경(=가상필드)
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 ENAME JOB
---------- ---------- ---------
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
14 개의 행이 선택되었습니다.
<문5>위의 문4의 sql문장중에서 EMPNO ENAME JOB 사원이름 업무로 별칭을 부여해서 출력하는 문장을 작성하시오.
*** edit or ed=>sql구문을 편집할 수 있는 편집창(=SQL Buffer창)이 실행
현재 방금 실행한 SQL구문의 내용을 수정하고 싶을때 사용
=>관리자 권한으로 실행했을때 사용할 수 있는 명령어
=>SQL Buffer창에서는 문장 끝에 ;을 쓰면 안된다.
SQL Buffer창=>마지막 SQL구문이 저장된 오라클의 메모리영역
형식) select 필드명 as 변경할단어(별칭명),필드명 별칭명 from 테이블명
<문6> 수정을 하고나서 실행시키는 방법
1) /
1* select empno,ename as "irum",job j from emp
SQL> / =>SQL Buffer에 저장된 SQL명령어를 실행해서 결과를 보여주는 역할
SQL> /
EMPNO irum J
---------- ---------- ---------
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 irum J
---------- ---------- ---------
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
14 개의 행이 선택되었습니다.
2) run or r
SQL> r =>한번 버퍼에 저장된 내용을 보여주고 나서 출력
1* select empno,ename as "irum",job j from emp
SQL> r
1* select empno,ename as "irum",job j from emp
EMPNO irum J
---------- ---------- ---------
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 irum J
---------- ---------- ---------
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
14 개의 행이 선택되었습니다.
* 별칭 작성시 주의사항
1* select empno,ename as ir um,job j from emp
SQL> /
select empno,ename as ir um,job j from emp
*
1행에 오류:
ORA-00923: FROM 키워드가 필요한 위치에 없습니다=>별칭을 작성할때 공백이나 특수기호가 포함된 별칭을 만들때에는 반드시 " "을 줘야 된다.
=>1* select empno,ename as "ir um",job j from emp
<문7> 계산필드 => 기존의 필드를 이용해서 수식도입 -> 가상으로 필드를 생성
emp 테이블에서 직원별로 연봉을 계산해서 출력?
사원이름(ename), 업무(job), 급여(sal), 연봉순(sal*12)으로 출력하는 SQL 작성하라.
sql> 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
JONES MANAGER 2975 35700
MARTIN SALESMAN 1250 15000
BLAKE MANAGER 2850 34200
CLARK MANAGER 2450 29400
SCOTT ANALYST 3000 36000
KING PRESIDENT 5000 60000
TURNER SALESMAN 1500 18000
ADAMS CLERK 1100 13200
ENAME JOB SAL SAL*12
---------- --------- ---------- ----------
JAMES CLERK 950 11400
FORD ANALYST 3000 36000
MILLER CLERK 1300 15600
14 개의 행이 선택되었습니다.sal*12 에 별칭 적용
SQL>ed
SQL> r
1* select ename,job,sal,sal*12 as "year price" from emp
ENAME JOB SAL year price
---------- --------- ---------- ----------
SMITH CLERK 800 9600
ALLEN SALESMAN 1600 19200
WARD SALESMAN 1250 15000
JONES MANAGER 2975 35700
MARTIN SALESMAN 1250 15000
BLAKE MANAGER 2850 34200
CLARK MANAGER 2450 29400
SCOTT ANALYST 3000 36000
KING PRESIDENT 5000 60000
TURNER SALESMAN 1500 18000
ADAMS CLERK 1100 13200
ENAME JOB SAL year price
---------- --------- ---------- ----------
JAMES CLERK 950 11400
FORD ANALYST 3000 36000
MILLER CLERK 1300 15600
14 개의 행이 선택되었습니다.
<문8> 하나이상의 필드들의 데이터들을 결합->출력(가상의 필드생성)
-
언어->'a',"ab"=>"ab"+"cd"=>"abcd"
+ 문자열 결합=>+ 대신에 `||`
+ "ab"+" "+cd"=>"ab cd"
|| 결합
select ename || job as "concat field" from emp;
concat field
-------------------
SMITHCLERK
ALLENSALESMAN
WARDSALESMAN
JONESMANAGER
MARTINSALESMAN
BLAKEMANAGER
CLARKMANAGER
SCOTTANALYST
KINGPRESIDENT
TURNERSALESMAN
ADAMSCLERK
concat field
-------------------
JAMESCLERK
FORDANALYST
MILLERCLERK
14 개의 행이 선택되었습니다.
|| ' ' || 띄어쓰기 적용
1* select ename || ' ' || job as "concat field" from emp
SQL> /
concat field
--------------------
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
concat field
--------------------
JAMES CLERK
FORD ANALYST
MILLER CLERK
14 개의 행이 선택되었습니다.
<문9> emp테이블에서 급여가 3000이상인 직원을 찾아서 그 직원들의 이름,업무,급여 순으로 출력하는 SQL 작성하시오.
관계연산자
-
=>>,>=,<,<=, =(정확하게 알고 있는 경우)
-
부정!= ,^= ,<>
급여가 3000보다 큰 경우
SQL> select ename,job,sal
2 from emp
3 where sal >= 3000;
ENAME JOB SAL
---------- --------- ----------
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000급여가 3000이 아닌 경우
ed
1 select ename,job,sal
2 from emp
3* where sal != 3000 =>3000가 아닌 데이터를 찾는것
SQL> /
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
ENAME JOB SAL
---------- --------- ----------
MILLER CLERK 1300
12 개의 행이 선택되었습니다.
<문10> emp테이블에서 업무(job)이 Manager인 사원을 찾아서 그 사원의 사원번호,이름,업무순으로 출력하시오.
select empno as num,ename irum,job
from emp
where job='Manager'; //영문자는 대,소문자를 비교해서 찾는다.
선택된 레코드가 없습니다.조건에 만족하는 데이터가 없다는 의미
Manager -> MANAGER 수정
1 select empno as num,ename irum,job
2 from emp
3 where job='MANAGER'
NUM IRUM JOB
---------- ---------- ---------
7566 JONES MANAGER
7698 BLAKE MANAGER
7782 CLARK MANAGER
<문11>emp테이블에서 급여가 1300에서 1800사이의 직원의 정보를 출력(단 사원이름,업무,급여순)
-
범위를 지정=>between A and B
select ename,job,sal
from emp
where sal between 1300 and 1800;
ENAME JOB SAL
---------- --------- ----------
ALLEN SALESMAN 1600
TURNER SALESMAN 1500
MILLER CLERK 1300
범위 밖의 직원을 구하고자 할 때
select ename,job,sal
from emp
where sal not between 1300 and 1800
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
ADAMS CLERK 1100
JAMES CLERK 950
FORD ANALYST 3000
11 개의 행이 선택되었습니다.
<문12> SQL구문 작성=>자주 사용이 되거나 검색이 되는 SQL구문 있으면 파일로 저장
-
형식) save '경로포함해서 저장할 파일명(~.sql or .txt)
sql>save 'c:\webtest\2.db\print\notbetween.txt'
file c:\webtest\2.db\print\notbetween.txt(이)가 생성되었습니다
<문13> 저장된 파일을 불러오기
-
형식) get 경로포함해서 불러올 파일명 or @경로포함해서 불러올 파일명
-
get=>파일을 불러올때 바로 실행하는 것이 아니라 실행시킬 SQL구문을 출력시켜준다. =>확인 후 / or r으로 실행시킨다.
-
***@=>파일을 불러오면서 바로 그 파일의 내용대로 실행시킨다. (파일의 내용을 정확하게 알고 있을때 사용하는 방법)
SQL> @'c:\webtest\2.db\print\notbetween.txt'
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
ADAMS CLERK 1100
JAMES CLERK 950
FORD ANALYST 3000
11 개의 행이 선택되었습니다.
<문14> 문자열 검색=> 사원이름이 ALLEN 을 찾아라 (사원이름이 A자로 시작하는 사원을 찾아라(일부분))
-
like 연산자=>정확히 찾고자하는 데이터를 모를때 사용 %,_ 를 이용 %(문자열의 길이를 정확히 모를때) _(문자열의 한글자로 매치) ___
-
ex) 이%=>이로 시작하는 사람 김% ->김길수,김외,김수(김으로 시작하는 사람이름 전부) 김_ =>찾는문자열은 2글자
%수=>수로 끝나는 사람이름, 단어 %동이름% =>동이름이 포함된 단어 찾기
emp테이블에서 사원명이 A자로 시작하는 사원을 찾아서 그 사원의 이름과 급여를 출력하는 SQL문장?
SQL> select ename,sal
2 from emp
3 where ename like 'A%';
ENAME SAL
---------- ----------
ALLEN 1600
ADAMS 1100not like
ed
1 select ename,sal
2 from emp
3* where ename not like 'A%'
SQL> /
ENAME SAL
---------- ----------
SMITH 800
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
JAMES 950
FORD 3000
ENAME SAL
---------- ----------
MILLER 1300
12 개의 행이 선택되었습니다.
<문15> 급여가 1000에서 3000 사이에 있는 사원을 찾아라(이름,업무,급여순으로 출력) =>between A and B
-
where 조건식 or 조건식2 =>둘중의 하나만 만족해도 결과출력 and 조건식2 =>조건이 모두 만족해야 결과 출력
SQL> select ename,job,sal from emp
where sal>=1000 and sal <=3000; //between 1000 and 3000;
ENAME JOB SAL
---------- --------- ----------
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
TURNER SALESMAN 1500
ADAMS CLERK 1100
FORD ANALYST 3000
MILLER CLERK 1300
11 개의 행이 선택되었습니다.
<문16> emp테이블에서 두번째 글자가 L자가 나오는 직원의 정보를 찾아서 이름,급여순으로 출력?
SQL> select ename,sal from emp where ename like '_L%';
ENAME SAL
---------- ----------
ALLEN 1600
BLAKE 2850
CLARK 2450
<문17>emp테이블에서 사원번호가 7782,7788,7844 인 사원을 검색해서 이 사원의 급여,연봉을 출력?
SQL> select sal,sal*12 as "year price" from emp
where empno=7782 or empno=7788 or empno=7844;AB+AC+AD=A(B+C+D) => in 범위연산자 사용하여 수정
select sal,sal*12 as "year price" from emp
where empno in (7782,7788,7844)
SAL year price
---------- ----------
2450 29400
3000 36000
1500 18000not in
ed
1 select sal,sal*12 as "year price" from emp
2 where empno not in (7782,7788,7844)
SQL> /
SAL year price
---------- ----------
800 9600
1600 19200
1250 15000
2975 35700
1250 15000
2850 34200
5000 60000
1100 13200
950 11400
3000 36000
1300 15600
11 개의 행이 선택되었습니다.
<문18>emp테이블에서 업무가 PRESIDENT 이고(=and) 급여가 1500이상이거나(or) 업무가 SALESMAN인 사원을 찾아서 그 사원의 사번,이름,업무,급여순으로 출력하는 SQL 작성?
select empno,ename,job,sal
from emp
where (job='SALESMAN' or job='PRESIDENT') and sal>=1500;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 1600
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
<문19> 정렬문제
-
select 출력할 필드명,,, from 테이블명 where 조건식=>생략이 가능하다. order by 정렬하고자하는 필드명,필드명2,,,[정렬기준(선택)] default ->asc
-
오름차순->ㄱ~ㅎ, A~Z 내림차순->ㅎ~ㄱ, Z~A(게시판) asc(ending) desc(ending) 1,2,3,,,10 10,9,8,7~
계산필드(=가상필드)=>연봉=>고액연봉자순으로 정렬하시오(=내림차순)
SQL> select ename as "name", sal, sal*12 year
from emp
order by year desc;
KING 5000 60000
FORD 3000 36000
SCOTT 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
name SAL YEAR
---------- ---------- ----------
ADAMS 1100 13200
JAMES 950 11400
SMITH 800 9600
14 개의 행이 선택되었습니다.
<문20> 데이터 검색시 중복된 데이터를 한번만 출력하게 작성.
직원의 업무를 출력하라
SQL> select job from emp;
중복제거
-
형식) select distinct 중복된 필드명 from 테이블명
SQL> select distinct job from emp;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
<문21>파라미터 인수 질의=매개변수 질의 (자주 검색이 되는 검색어를 입력받아서 처리해주는 SQL문장)
emp테이블에서 부서번호가 10번인 사원을 찾아서 그 사원들의 사원이름 급여,부서번호순으로 출력하되 급여가 많은순으로 정렬하시오.(=내림차순)
SQL> select ename,sal,deptno
from emp
where deptno=10
order by sal desc;
ENAME SAL DEPTNO
---------- ---------- ----------
KING 5000 10
CLARK 2450 10
MILLER 1300 10
형식) where deptno=&변수명(=>필드명을 변수명)
SQL> select ename,sal,deptno
from emp
where deptno=&deptno
order by sal desc
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 20SQL> select ename,sal,deptno
from emp
where deptno=&deptno
order by sal desc
deptno의 값을 입력하십시오: 30
구 3: where deptno=&deptno
신 3: where deptno=30
ENAME SAL DEPTNO
---------- ---------- ----------
BLAKE 2850 30
ALLEN 1600 30
TURNER 1500 30
WARD 1250 30
MARTIN 1250 30
JAMES 950 30