데이터베이스 기초) 오라클 SQL 쿼리 문 feat. 업데이트 중!

데이터베이스 기본 용어

Database (데이터베이스) : 저장소에 구분되는 가장 큰 단위. 
Table (테이블) : 데이터베이스에 뭔가를 저장하기 위해 첫 단계에서 만드는 테이블. 
Column (컬럼) : 관계형데이터 베이스에서 행(레코드)를 분류하는 기준. 
Row (행) : 데이터를 저장하는 값으러 컬럼(필드) 내의 단 하나의 값. 

※ 같은 의미의 용어 

Table (테이블) 과 Relation (릴레이션) 는 같은 의미
Column (컬럼) 과 Filed (필드), Attribute (어트리뷰트) 는 같은 의미 
Row (행) 과 Record (레코드), Tuple(튜플) 는 같은 의미

---

오라클 기타 명령문

오라클 버전 확인

select * from v$version

sqlplus

SELECT * FROM v$version WHERE banner LIKE 'Oracle%';

 

데이터베이스 관리 권한으로 접속

sqlplus

sqlplus / as sysdba

sqlplus system/1234 ← 비밀번호 노출로 인해서 권장하지 않는 방법.

 

sql 접속 후 다른 계정으로 접속하기 (로그인한 상태에서 다른 계정으로 로그인하는 것)

conn scott/tiger

connect sys/as sysdba

 

sql 화면 지우기

clear screen

 

포트번호 알아보기

select dbms_xdb.gethttpport() from dual;

 

포트번호 바꾸기

exec dbms_xdb.sethttpport(9090);

 

auto commit을 켤 경우,

set autocommit on;

 

auto commit을 끌 경우,

set autocommit off;

 

auto commit을 상태를 확인할 경우,

show autocommit;

 

---

오라클 시퀀스 명령어

시퀀스의 현재 값을 확인
SELECT LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'seq_board';

시퀀스의 INCREMENT 를 현재 값만큼 빼도록 설정 (아래는 현재값이 999999 일 경우)
ALTER SEQUENCE seq_board INCREMENT BY -999999;

시퀀스에서 다음 값을 가져 온다
SELECT seq_board.NEXTVAL FROM DUAL;

현재 값을 확인 해보면 -999999 만큼 증가 했다
SELECT seq_board.CURRVAL FROM DUAL;

시퀀스의 INCREMENT 를 1로 설정 한다
ALTER SEQUENCE seq_board INCREMENT BY 1;

 

오라클 시퀀스 조회(검색)

select * from user_sequences

 

-- 시퀀스 검색 (현재 계정의 모든 시퀀스를 보여줌) -- 
SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG  
FROM USER_SEQUENCES;

 

-- 시퀀스 삭제 --
DROP SEQUENCE seq_board;

-- 시퀀스 생성 --
CREATE SEQUENCE seq_board
START WITH 1
INCREMENT BY 1
MAXVALUE 10000
MINVALUE 1
NOCYCLE;

 

시작값은 말 그대로 시퀀스의 시작값입니다. 만약 1을 쓴다면 1부터 순차적으로 증가할 것이고 10을 쓴다면 10부터 순차적으로 증가합니다.
증가값은 시퀀스가 얼마씩 증가할 건지를 지정하는 값입니다. 1을 쓰면 1씩 증가하며, 2를 쓰면 2씩 증가하게 됩니다.
최대값은 시퀀스의 최대값을 의미하며, 최소값은 시퀀스의 최소값을 의미합니다.
순환 여부는 시퀀스가 최대 값이 됐을때 다시 시작값으로 돌아갈 것인지에 대해 설정합니다. 
순환한다면 CYCLE, 그렇지 않다면 NOCYCLE을 지정하시면 됩니다.

캐시 여부는 시퀀스 값을 메모리에 할당할 것인지에 대해 설정합니다. CACHE에 할당한다면 원하는 값을 넣으시면 되고 기본 값은 20입니다. 할당하지 않겠다면 NOCACHE로 지정하시면 됩니다.

출처 : https://cocodo.tistory.com/12

---

사용자계정 관련, 데이터 제어어 SQL 쿼리문 (DCL)

오라클 계정 변경

conn 계정명/비밀번호

ex) conn scott/tiger

 

오라클 사용자 계정 만들기

create user 아이디 identified by 비번

ex) create user scott identified by 1234

 

오라클 계정 비밀번호 변경

alter user 유저명 identified by 변경할비밀번호;

ex) alter user scott identified by 1234;

 

오라클 계정 삭제하는 방법

drop user 계정명 cascade;

 

오라클 계정 권한 주기

grant 권한 to 아이디;

connect ← 로그인 권한

resource ← 자원을 사용할 수 있는 권한

dba ← db 관리자 권한

ex) grant conect, resource, dba to scott;

 

사용자에게 부여된 시스템 권한 확인

select * from dba_sys_privx where grantee = '사용자명';

 

계정에 권한 조회하기

select * from user_tab_privs_made; 
select * from user_tab_privs_recd;

 

계정에 권한 삭제하기

revoke 권한명 from 계정명; 

ex) revoke connect, resource,dba from java;

 

※ 샘플 계정 hr 로 설명!

사용자 계정 잠김 풀기 (언락)

alter user 사용자명 account unlock;

ex) alter user hr account unlock;

 

사용자 계정 암호 부여하기

alter user 사용자명 identified by 비밀번호;

ex) alter user hr identified by hr;

 

사용자 계정 잠김 풀기 및 암호부여

alter user 사용자명 identified by 원하는비밀번호 account unlock;

ex) alter user hr identified by hr account unlock;

 

현재 생성된 계정 확인하기

select * from all_users;

 

현재 접속한 사용자(계정) 조회하기

(sql 연습하는 중 갑자기 내가 뭘로 접속했지 할 때 유용한 듯!)

select user from dual;

 

 

---

테이블 관련, 데이터 정의어 SQL 쿼리문 (DDL)

테이블 스페이스(테이블저장공간) 만들기

create tablespace 테이블스페이스이름

datafile '데이터파일경로' size 초기사이즈

autoextend on ← 자동증가

next 자동증가사이즈

maxsize 최대사이즈;

 

ex)

create tablespace java

datafile 'd:\java.db' size 50m

autoextend on

next 10m

maxsize unlimited;

의미 : 초기사이즈 50메가, 용량이 꽉 차면 10메가씩 자동증가해서 무제한으로 증가시키겠다는 의미.

※ C:\에 만드는 것 권하지 않음. 루트디렉토리라서 쓰기가 안됨. 만들려면 디렉토리 하나 만들어서 사용해야함.

 

만든 테이블을 사용하겠다는 sql 문

default tablespace 테이블스페이스이름;

 

테이블의 필드 목록 조회(확인)하기.

desc member;

 

현재 계정의 테이블 목록 보기.

select * from tab;

 

테이블의 데이터(내용) 보기.

select * from 테이블명;

ex) select * from member;

 

모든 계정의 테이블 목록 보기.

select * from all_tables where OWNER ='scott';

 

테이블 이름 변경하기.

rename 변경 전 테이블명 to 변경 후 테이블명;

ex) rename member to member_user;

 

테이블 삭제하기.

drop table 테이블명;

ex) drop table member;

 

테이블 컬럼 추가하기

alter table 테이블명 add(컬럼명 데이터타입(사이즈));

ex) alter table member add(age varchar2(5));

 

테이블 컬럼 수정하기

alter table 테이블명 modify(컬럼명 데이터타입(사이즈));

ex) alter table member modify(userid varchar2(50));

해설 : user 라는 테이블의 userid 라는 컬럼을 타입을 varchar2(50) 으로 수정한다.

 

테이블 컬럼 삭제하기

alter table 테이블명 drop column 컬럼명

ex) alter table member drop column age;

 

테이블 컬럼 이름 변경하기

alter table 테이블명 rename column 원래컬렴명 to 바꿀컬럼명;

ex) alter table member rename column age to AGE;

 

---

추가, 수정, 삭제, 목록, 데이터 조작어 SQL 쿼리문 (DML)

추가(insert)

insert into 테이블명 values ('해당하는 컬럼/필드 값','해당하는 컬럼/필드 값','해당하는 컬럼/필드 값');

※ 꼭 작은 따옴표를 사용해야함.

ex) insert into member values ('june','134','홍길동');

 

수정(update)

update 테이블명 set 컬럼명='컬럼명에 해당하는 값' where 컬럼명='조건에 해당되는 값';

ex) update member set password='3333' where userid='kim';

해설 : member 테이블에 where 조건에 kim 이라는 사람의 비밀번호를 1234로 수정할 것이다.

※ 주의: update member set password='3333'; where 조건문 없이 실행시키면 member의 모든 비밀번호가 3333으로 바뀝니다.

위 처럼 테이블의 모든 데이터가 동일한 값으로 바뀌어 버리면 rollback; 을 하면 된다. (Ctrl + Z 느낌이랄까)

 

삭제 (delete, 레코드 삭제)

delete from 테이블명 where 컬럼명='row 값';

ex) delete from member where userid='kim';

 

테이블 컬럼 여러개 삭제하기 (in, or 이용하기)

delete from 테이블명 where 컬럼명 in (값, 값);

delete from 테이블명 where 컬럼명='값' or 컬럼명='값';

ex) delete from TBL_BOARD where bno in (27, 28));
ex) delete from tbl_board where bno='22' or bno='36';

---

검색 할 때, 데이터 질의어 SQL 쿼리문 (DQL)

기본형식)

select 컬럼명1, 컬럼명2, .... from 테이블명 where 조건절 order by 정렬의 기준 컬럼명 [asc/desc]

※ asc : 오름차순, desc : 내림차순

 

데이터베이스에서 원하는 데이터를 검색, 추출

SELECT [ALL | DISTINCT] 열리스트

[FROM 테이블_리스트]

[WHERE 조건]

[GROUP BY 열_리스트 [HAVING 조건]]

[ORDER BY 열_리스트 [ASC | DESC]];

 

기능

Projection : 원하는 컬럼 선택

Selection : 원하는 튜플 선택

Join : 두 개의 테이블 결합

기타 : 각종 계산, 정렬, 요약

 

* ← 별표의 의미는 모든 필드를 의미한다.

 

테이블의 모든 데이터 조회하기

select * from emp;

 

특정 테이블의 모든 데이터 검색하기

select * from 테이블명 order by 컬럼명;

ex) select * from emp order by empno;

 

특정 필드를 지정해서 조회하기 (insert한 순서대로 나옴)

select 컬럼명,컬럼명 from 테이블명;

ex) select userid,password from member;

 

특정 필드 목록의 데이터 검색하기 (오름차순 asc, 내림차순 desc)

select 컬럼명,컬럼명 from 테이블명 order by asc

select empno,ename from emp order by ename asc;

※ asc 를 안써도 기본적으로 가나다순의 오름차순으로 나온다. asc는 기본값이라 생략가능.

 

테이블에서 특정 컬럼의 데이터 보기

select * from 테이블명 where 컬럼명= 'row 값';

ex) select * from member where userid = 'kim';

 

** 이 데이터를 기본으로 쿼리문을 작성해 보겠습니다. **

컬럼명 설명

EMPNO : 사번
ENAME : 사원명
JOB : 직급
MGR : 매니저
HIREDATE : 입사일
SAL : 급여
COMM : 보너리(보너스개념)
DEPTNO : 부서명(부서코드)

---

◎ order by : 정렬

 

사원명과 함께 같은 부서를 출력하고 급여가 높은 순으로 출력하라.

select ename,sal,deptno from emp order by deptno, sal desc;

※ order by 필드를 두번적으면 된다. 그러면 부서가 같으면 급여가 높은 순서로 나온다.

※ 여러개의 order by를 사용할 경우 왼쪽부터 순차적으로 진행된다, 그래서 순서를 고려해야 한다.

 

** 위의 결과 **

 

◎ distinct : 중복된 데이터를 허용하지 않음
    all : 중복된 데이터를 허용함

 

distinct 없이 job을 출력한 것과 distunct 를 넣어 출력한 것을 비교해본다.

select job from emp;

** 중복되어 있는 직급과 중복된 데이터를 걸러서 출력한다. **

◎ alias : 별명 짓기

기본형식)

컬렴명 [as] 별명

※ as 생략 가능

 

select ename , job, sal from emp order by job,sal desc;

** ENAME, JOB, SAL 의 컬럼을 확인할 수 있다 **

이제 컬럼명 옆에 as 를 붙여보자.
select ename as 이름, job as 직업, sal as 급여 from emp order by job,sal desc;

** as 를 붙이니깐 ename, job, sal 컬럼명이 한글로 바뀐걸 확인할 수 있다 **

select ename 이름, job 직업, sal 급여 from emp order by job,sal desc;

※ alias 에서 as 는 생략하고 사용해도 같은 결과를 얻을 수 있다.

 

 

◎ where 절 : 검색에 조건을 부여함.

 

급여가 100 보다 많고 400 보다 작은 직원을 검색해라 (급여는 내림차순이다)

select * from emp where sal > 100 and sal < 400 order by sal desc;

※ and 자리에 or 을 사용해도 된다 물론 뜻은 다르다. 관계연산자 between 을 사용해서 같은 결과를 얻을 수 있다.

 

급여가 100 이하 또는 400 이상의 직원 검색하기 (급여 내림차순)

즉, 100 ~ 400 사이 빼고 나머지를 출력.

select * from emp where not (sal > 100 and sal < 400)  order by sal desc;

select * from emp where sal <= 100 or sal >= 400 order by sal desc; (위와 동일한 결과 값을 준다)

※ 괄호안의 조건을 뽑았는데 not을 사용했기에 괄호안의 조건을 부정하는 것이다.

---

◇ 연산자의 종류

• 산술연산자 : +, -, *, /
• 비교연산자 : =, !=, >, >=, <, <=
• 논리연산자 : and, or, not

◇ 연산자의 우선순위

• 1순위 : 비교연산자, SQL 연산자, 산술연산자
• 2순위 : not
• 3순위 : and
• 4순위 : or
• 5순위 : 결합연산자
• ※ 괄호 ( ) : 연산자 우선순위 보다 우선으로 한다.

select empno, sal from emp where not(sal > 200 and sal < 300) order by sal;
-- 이것은 괄호 안의 조건 전체가 not이 되는 거라서 결과 값이 200 이하 또는 300 이상의 결과가 나온다.
즉, 괄호 안의 결과를 제외하고 출력된다.

select empno, sal from emp where not sal > 200 and sal < 300 order by sal;
-- 이것은 sal > 200 여기에만 not이 적용된거라서 200 이하만 결과가 출력된다.

◇ SQL 연산자 : in, any, all, between, like, is null, is not null

where 연산자에서 다양한 값들을 선택할 수 있게 해주는 연산자들.

 

in

여러개의 or 조건을 연속으로 사용하는 것과 같은 효과를 낸다.

 

SELECT 컬럼명 FROM 테이블명 WHERE 컬럼명 IN (값1, 값2, ...);

select * from emp where deptno= 10 or deptno = 20 or deptno = 30;
select * from emp where deptno in (10,20,30);

 

between

주어진 범위에 대해서 값들을 선택할 수 있도록 해준다. (값은 number, text, date)

사용할 때 중요한 점이 비교하려는 값을 포함해야한다는 것이다. 양쪽 끝이 포함된다. 1과 10을 넣으면 1에 해당하는 값과 10에 해당하는 값이 포함되어 온다는 것이다.

 

SELECT 컬럼명 FROM 테이블명 WHERE 컬럼명 BETWEEN 값1 AND 값2; 

select * from emp where sal between 100 and 400 order by sal desc;

 

like

where 절 안에서 쓰인다. 열에서 어떤 특정한 패턴에 부합하는 부분을 찾으려고 할 때 사용된다.

like 연산자는 두 가지의 와일드카드 문자를 쓴다.

• % : 0, 1 혹은 하나 이상의 char 에 사용
• _ : 한 개의 char 에 사용

SELECT 컬럼명1, 컬럼명2, ... FROM 테이블명 WHERE 컬럼명 LIKE 패턴;

select * from emp where ename like '김%'; -- 김이 포함된 데이터를 출력
select * from emp where ename like '&성%'; -- 위치에 상관없이 성이 다 포함된 데이터를 출력
select * from emp where ename like '_철%'; -- 두번째 글자가 철인 데이터를 출력

 

is null, is not null

NULL은 일반적인 비교 연산자인 =,<,<> 등... 비교가 불가능하다. 그래서 있는 것이 is null 과 is not null이다.

 

SELECT 컬럼명 FROM 테이블명 WHERE 컬럼명 IS NULL;

SELECT 컬럼명 FROM 테이블명 WHERE 컬럼명 IS NOT NULL;

select ename, comm from emp; -- null값과 아닌 값이 같이 나옴을 확인할 수 있다.
select ename, comm from emp where comm is null; -- null인 데이터만을 출력한다.
select ename, comm from emp where comm is not null; -- null을 제외한 데이터를 출력한다.

※ 참고 ※
-- 계산을 할 때, NULL 값을 없애고 계산하기 (NVL 함수이용) --
select ename, sal, comm, sal*12+comm from emp;
-- null 이 들어간 상태에서 연산을 하면 null이 나온다. 0과 null은 다르다.
select ename, sal, comm, sal*12+nvl(comm, 0) from emp;
-- 괄호 안의 comm이 null이면 뒤에 값을 사용해서 계산해라 라는 뜻

 

◇ 결합 연산자

|| ← 이게 자바에서는 or 지만 오라클에서는 문자열의 더하기이다. 다른 필드끼리 연결을 해서 문장을 만들때 사용.

select ename || '의 급여는 ' || sal || '입니다.' from emp; --김철수의 급여는 270입니다. 이런식으로 출력이 된다.

 

---

트랜잭션 제어어 SQL 쿼리문 (TCL)

DML문을 DB에 저장 또는 취소할 때 사용

commit;

rollback;

savepoint