220830 - 오라클 - SQL PLUS, 쿼리문

SQL PLUS

• EDIT(ED) : 버퍼 저장된 쿼리문 편집 (메모장에서)

•  

ed //마지막 쿼리문 메모장으로 열고 편집

ed sample //sample 파일 열고 편집

 

• SAVE : 마지막에 실행한 쿼리문 파일로 저장

•  

SAVE sample; //sample 파일로 저장

SAVE sample REPLACE //마지막에 실행한 쿼리문으로 내용 대체

SAVE sample APPEND //sample 파일에 마지막 쿼리문 추가

=>
select * from department
/
select * from employee where salary>=1500
/

 

 

 

 

• @경로...파일이름.sql : 파일에 저장된 내용을 일괄로 실행

 

 

 

 

• SPOOL : SPOOL 에서 실행된 모든 명령들을 하나의 파일로 생성

.LST 파일 생성

 

•  

SPOOL 파일 이름

SPOOL OFF  //SPOOL 끄기 必

 

 

• column

column dname format a40 //dname 열 크기 조정 => 문자 40개 들어가도록

 

 

 

• set

set linesize 130 //한 화면에 130열 출력
set pagesize 20 //한 페이지에 20행 출력

 

 

 

 

데이터베이스

• 데이터베이스 구축 순서

1. 데이터베이스 생성 (사용자 계정 생성)
2. 테이블 생성
3. 자료 입력
4. 자료 추가입력/조회/수정/삭제

 

 

• 사용자 계정 생성 방법

• 1

create user C##mydb identified by 1234  
grant create table to C##mydb

 

• ★2

1세션 변경 : alter session set "_ORACLE_SCRIPT"=true;

2계정 생성 : create user mydb2 identified by 1234;

3권한 부여 (system 계정에서만 가능) : 
(세션 권한) grant create session to mydb2;
(테이블 생성 권한) grant create table to mydb2;

4테이블 스페이스 영역 할당 : 
alter user mydb2 default tablespace users quota 10m on users; 
//계정에 테이블 만들 공간 10메가 할당
//quota unlimited : 무한

5계정 연결 : conn mydb2/1234;


※show user; //현 사용자 계정 확인

 

 

 

• 테이블 생성 방법

• CREATE

create table 테이블명(컬럼명 데이터타입, ...);


※ 데이터타입
char(바이트 크기) - 고정길이 문자 (2000byte) //잘 안 쓰임
varchar2(바이트 크기) - 가변길이 문자 (4000byte)
=> 영문:1byte 한글:2~3byte

number(p,s) - p: 전체 숫자 길이 s: 소수점 길이 
ex. 123.45 => number(5,2)

•  

create table schooltbl(name varchar2(10), kor number(3), eng number(3));

create table producttbl(code varchar2(8), pname varchar(20), amount number(4), price number(8), maker varchar(10));

 

 

 

 

 

• INSERT : 테이블 내 데이터 추가

INSERT INTO 테이블(컬럼1, 컬럼2,..) VALUES(값1, 값2,..);

 

•  

insert into producttbl values('ss6654','냉장고', 20, 1980000,'삼성');
insert into producttbl values('lg33453','청소기', 10, 960000,'엘지');
insert into producttbl (code, pname, maker) values ('ss1344','에어컨', '삼성');
insert into producttbl values('ss4883','청소기', 25, null,null);

=>

CODE             PNAME                                        AMOUNT      PRICE MAKER
---------------- ---------------------------------------- ---------- ---------- --------------------
ss6654           냉장고                                           20    1980000 삼성
lg33453          청소기                                           10     960000 엘지
ss1344           에어컨                                                         삼성
ss4883           청소기                                           25



※
create table emp_copy as select * from employee; //employee 테이블 복사

 

 

 

 

• UPDATE : 데이터 변경

update 테이블명 set 컬럼명1=변경값, .. where 조건;

 

 

•  

update emp_copy set ename='KAIN' where ename='JONES'; //JONES 값을 KAIN으로 변경

update emp_copy set salary=1000 where salary<1000;

 

 

 

 

 

• DELETE : 데이터 삭제

delete from 테이블명 where 조건;

 

•  

DELETE FROM emp_copy where eno=9999;

 

 

 

 

함수

• 대소문자 함수 (UPPER, LOWER, INITCAP)

•  

select 'Computer', upper('computer'), lower('COMPUTER'), INITCAP('computer') from dual;

=>

'COMPUTER'       UPPER('COMPUTER' LOWER('COMPUTER' INITCAP('COMPUTE
---------------- ---------------- ---------------- ----------------
Computer         COMPUTER         computer         Computer

 

 

•  

select ename, lower(ename), job, initcap(job) from employee;

=>

ENAME                LOWER(ENAME)         JOB                INITCAP(JOB)
-------------------- -------------------- ------------------ ------------------
SMITH                smith                CLERK              Clerk
ALLEN                allen                SALESMAN           Salesman
WARD                 ward                 SALESMAN           Salesman
JONES                jones                MANAGER            Manager
MARTIN               martin               SALESMAN           Salesman
BLAKE                blake                MANAGER            Manager
CLARK                clark                MANAGER            Manager
SCOTT                scott                ANALYST            Analyst
KING                 king                 PRESIDENT          President
TURNER               turner               SALESMAN           Salesman
ADAMS                adams                CLERK              Clerk
JAMES                james                CLERK              Clerk
FORD                 ford                 ANALYST            Analyst
MILLER               miller               CLERK              Clerk

 

•  

select eno, ename, dno from employee where lower(ename)='scott';

=>

       ENO ENAME                       DNO
---------- -------------------- ----------
      7788 SCOTT                        20

 

 

 

 

• 문자 길이 반환 함수(LENGTH, LENGTHB)

•  

select length('computer'), length('대한민국') from dual;

=>

LENGTH('COMPUTER') LENGTH('대한민국')
------------------ ------------------
                 8                  4

 

 

 

•  

select lengthb('computer'), lengthb('대한민국') from dual;

=>

LENGTHB('COMPUTER') LENGTHB('대한민국')
------------------- -------------------
                  8                  12

 

 

• 문자 조작 함수 (CONCAT, SUBSTR, SUBSTRB, INSTR, INSTRB(△), LPAD, RPAD, TRIM)

•  

select concat('대한민국','만세') from dual;

=>

CONCAT('대한민국','만세')
------------------------------------
대한민국만세

 

 

•  

 select substr('computer',4,3), substr('computer',-3,3) from dual;  
//인덱스4에서부터 3개 //뒤에서 3번째부터 세글자

=>

SUBSTR SUBSTR
------ ------
put    ter

 

 

•  

select instr('computer','m') from dual;  //m 위치 반환

=>

INSTR('COMPUTER','M')
---------------------
                    3

•  

select instr('computer mainama','m',5,2) from dual;  
//인덱스5에서부터 두번째로 나오는 m 찾기

=>

INSTR('COMPUTERMAINAMA','M',5,2)
--------------------------------
                              15

•  

select lpad(salary,10,'*') from employee; //출력 10자리, 빈 공간은 왼쪽에 *로 채움

LPAD(SALARY,10,'*')
--------------------------------------------------------------------------------
*******800
******1600
******1250
******2975
******1250
******2850
******2450
******3000
******5000
******1500
******1100
*******950
******3000
******1300

 

 

•  

select rpad(salary,10,'*') from employee;

RPAD(SALARY,10,'*')
--------------------------------------------------------------------------------
800*******
1600******
1250******
2975******
1250******
2850******
2450******
3000******
5000******
1500******
1100******
950*******
3000******
1300******