Oracle 기초 공부하기

본격 시작 전 준비해야 할 것

1. Oracle DB 설치 : https://backendcode.tistory.com/266 또는 https://old-developer.tistory.com/119

2. Oracle SQL Developer / DBeaver 설치

 

만약 2번의 Oracle SQL Developer를 설치하고 유저를 생성하는 과정에서

상태: 실패 -테스트 실패: ORA-01017: 사용자명/비밀번호가 부적합, 로그온할 수 없습니다.

라는 문구가 뜬다면 유저 아이디를 아래와 같이 해보는 것을 추천함 !

 

SQL Developer 줄번호 표시
> 도구 > 환경설정 > 코드편집기 > 행여백 > 행번호표시 체크

DBeaver 줄번호 표시
윈도우 - 설정 - 편집기 - 문서편집기 - show line numbers

---

USER 생성

-- 12C 버전부터 c## 이 생겨서, 이전 버전과 같이 사용하고자 아래 구문 실행 
-- 자세한 내용은 - 구글링으로 아래 에러 검색 
-- ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다.
ALTER SESSION SET "_ORACLE_SCRIPT"=true;

-- 이유는 모르겠지만, 첫번째 계정으로 scott / tiger 를 만들어야 할것 같은..
create user scott identified by tiger;

-- 접속 권한 부여
grant connect, resource to scott;

-- 테이블스페이스 사용 권한 부여
alter user scott quota unlimited on users;

 

cf. Ctrl+Enter 또는 F9 으로 실행 가능 ! / F5는 스크립트 전체 실행

---

Connection & Table 생성

CREATE TABLE coffee_menu (
 no number GENERATED AS IDENTITY ,
 coffee VARCHAR2(100) NOT NULL ,
 kind VARCHAR2(100) NOT NULL ,
 price number(11) DEFAULT 0 NOT NULL,
 reg_day date DEFAULT sysdate NOT NULL ,
 mod_day date DEFAULT sysdate NOT NULL ,
 CONSTRAINT pk_coffee_menu PRIMARY KEY(no)
)
;

COMMENT ON TABLE coffee_menu IS '커피/음료 메뉴';

-----------------------------------------------------

CREATE TABLE cust_info (
 no number GENERATED AS IDENTITY ,
 cust_id VARCHAR2(100) NOT NULL,
 name VARCHAR2(100) NOT NULL,
 email VARCHAR2(100) NOT NULL,
 role VARCHAR2(100) DEFAULT 'MEMBER' NOT NULL  ,
 reg_day date default sysdate NOT NULL,
 CONSTRAINT pk_cust_info PRIMARY KEY(no)
);


COMMENT ON TABLE cust_info IS '고객정보';

-- unique index 생성

ALTER TABLE cust_info ADD CONSTRAINT idx_cust_info UNIQUE(cust_id);

-----------------------------------------------------

CREATE TABLE order_list (
 no number GENERATED AS IDENTITY ,
 coffee_no number(11) NOT NULL,
 coffee VARCHAR2(100) NOT NULL,
 price number(11) NOT NULL,
 cust_id VARCHAR2(100) NOT NULL,
 name VARCHAR2(100) NOT NULL,
 reg_day date default sysdate ,
 CONSTRAINT pk_order_list PRIMARY KEY(no)
);


COMMENT ON TABLE order_list IS '주문내역';

-- foreign key 생성

ALTER TABLE order_list
ADD CONSTRAINT fk_coffee_no
FOREIGN KEY (coffee_no)
REFERENCES coffee_menu(no);

ALTER TABLE order_list
ADD CONSTRAINT fk_cust_id
FOREIGN KEY (cust_id)
REFERENCES cust_info(cust_id);

---

INSERT

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

 

 

예시

INSERT INTO coffee_menu (coffee, kind, price)
VALUES ('아메리카노','커피',2000);

INSERT INTO coffee_menu (coffee, kind, price)
VALUES ('카페라떼','커피',3000);

INSERT INTO coffee_menu (coffee, kind, price)
VALUES ('카푸치노','커피',3000);

INSERT INTO coffee_menu (coffee, kind, price)
VALUES ('그린티','논커피',3500);

INSERT INTO coffee_menu (coffee, kind, price)
VALUES ('초코라떼','논커피',4000);

INSERT INTO coffee_menu (coffee, kind, price)
VALUES ('파인애플','에이드',3000);

INSERT INTO coffee_menu (coffee, kind, price)
VALUES ('제주한라봉','에이드',3000);

INSERT INTO coffee_menu (coffee, kind, price)
VALUES ('오렌지','에이드',3000);

-------------------------------------

INSERT INTO cust_info(cust_id,NAME,email,role)
VALUES('ID1','홍길동','email1@hong.com','MEMBER');

INSERT INTO cust_info(cust_id,NAME,email,role)
VALUES('ID2','홍길성','email2@hong.com','MEMBER');

INSERT INTO cust_info(cust_id,NAME,email,role)
VALUES('ID3','홍길자','email3@hong.com','MEMBER');

INSERT INTO cust_info(cust_id,NAME,email,role)
VALUES('ID4','홍길순','email4@hong.com','MEMBER');

INSERT INTO cust_info(cust_id,NAME,email,role)
VALUES('ID5','홍길선','email5@hong.com','MEMBER');

INSERT INTO cust_info(cust_id,NAME,email,role)
VALUES('ID6','관리자','admin@hong.com','ADMIN');

--------------------------------------

INSERT INTO order_list (coffee_no,coffee,price,cust_id,NAME)
VALUES(1,'아메리카노',2000,'ID1','홍길동');

INSERT INTO order_list (coffee_no,coffee,price,cust_id,NAME)
VALUES(1,'아메리카노',2000,'ID2','홍길성');

INSERT INTO order_list (coffee_no,coffee,price,cust_id,NAME)
VALUES(3,'카푸치노',3000,'ID3','홍길자');

INSERT INTO order_list (coffee_no,coffee,price,cust_id,NAME)
VALUES(3,'카푸치노',3000,'ID3','홍길자');

INSERT INTO order_list (coffee_no,coffee,price,cust_id,NAME)
VALUES(5,'초코라떼',4000,'ID4','홍길순');

INSERT INTO order_list (coffee_no,coffee,price,cust_id,NAME)
VALUES(8,'오렌지',3000,'ID2','홍길성');

INSERT INTO order_list (coffee_no,coffee,price,cust_id,NAME)
VALUES(4,'그린티',3500,'ID3','홍길자');

INSERT INTO order_list (coffee_no,coffee,price,cust_id,NAME)
VALUES(2,'카페라떼',3000,'ID5','홍길선');

---

SELECT - 단일 테이블

 

SQL의 문법 순서와 실행 순서는 서로 다르다
[ 문법 작성 순서 ]
① SELECT 컬럼명
② FROM 테이블명
③ WHERE 조건식 (IN, LIKE, EXIST 등)
④ GROUP BY 컬럼명
⑤ HAVING 조건식
⑥ ORDER BY 칼럼명 (ASC, DESC)

SELECT
  CategoryID,
  MAX(Price) AS MaxPrice, 
  MIN(Price) AS MinPrice,
  TRUNCATE((MAX(Price) + MIN(Price)) / 2, 2) AS MedianPrice,
  TRUNCATE(AVG(Price), 2) AS AveragePrice
FROM Products
WHERE CategoryID > 2
GROUP BY CategoryID
HAVING
  AveragePrice BETWEEN 20 AND 30
  AND MedianPrice < 40;

[ 실행 작동 순서 ]
① FROM
② ON
③ JOIN
④ WHERE
⑤ GROUP BY
⑥ CUBE | ROLLUP
⑦ HAVING
⑧ SELECT
⑨ DISTINCT
⑩ ORDER BY
⑪ TOP

실행 작동순서를 자주 사용하는 파란색 글씨로 된 쿼리문만 설명하면,
1. 조회 테이블 확인(FROM)
2. 데이터 추출 조건 확인(WHERE)
3. 컬럼 그룹화(GROUP BY)
4. 그룹화 조건(HAVING)
5. 데이터 추출(SELECT)
6. 데이터 순서 정렬(ORDER BY)

순으로 이루어진다

---

SELECT - Table Join

[ JOIN ]

SELECT ~~ 
FROM TABLE_A A (LEFT|생략(INNER)|RIGHT|FULL) JOIN TABLE_B B ON A.KEY=B.KEY

---

SELECT Subquery

SELECT * 
FROM 테이블명 
WHERE (컬럼1, 컬럼2) IN (SELECT 서브쿼리_컬럼1, 서브쿼리_컬럼2 FROM 서브쿼리_테이블);

---

UPDATE

UPDATE 테이블명
SET 컬럼=값, 컬럼=값
WHERE 검색조건 (=, IN, LIKE, EXIST)

-- UPDATE문에 Subquery 사용하기
UPDATE 테이블명
SET (컬럼, 컬럼) = (select 컬럼명 from 테이블명 where 조건문)
WHERE 검색조건 (=, IN, LIKE, EXIST)
	  검색조건 = (select 컬럼명 from 테이블명 where 조건문)

---

DELETE

DELETE 테이블명
WHERE 검색조건 (=, IN, LIKE, EXIST)

---

VIEW 생성

뷰를 생성할 수 있는 권한을 주고

VIEW를 생성. (SELECT Query 작성)

-> 뷰=테이블 비슷하게 사용 가능함. FROM절에 뷰 이름 입력. WHERE절도 사용 가능함.

---

+ 더 공부하면 좋을 것들

1. 오라클 내장 함수
ex. to_char, to_number, to_date, nvl, decode, case, length, trim

 

2. 오라클 분석/집계 함수
ex. count(), min(), max(), avg(), over(), partition by

 

3. Index
- 인덱스 이해, 인덱스의 올바른 생성, 사용

 

4. PL/SQL (Trigger)
- DBMS 자체로 프로그램을

 

참고 사이트
- Live SQL : https://livesql.oracle.com/apex/f?p=590:49::::RP::
- IT늦공 유툽 : https://www.youtube.com/watch?v=OSglfI54C78&list=PL3036mp45iYxIWncxyM8QCwv-4st7wucz