Oracle SQL(2)

김성국·2023년 2월 21일

SQL의 순서

1.전체조회 테이블의 개수와 상관없이
2. 조건으로 필요한 필터
3. 원하는 정렬 기준으로 조회
4. 가져온 데이터를 필여한 형태로 가공(내장함수)

■ 정렬(ORDER BY)

정렬은 두 가지로 나뉘게 되는데 1. ASC는 오름차순정렬, 2. DESC는 내림차순정렬로 나뉜다.

SELECT 컬러명 FROM 테이블명 별칭 ORDER BY 정렬컬럼 ;

SELECT i.* FROM ITEM i WHERE i.price >= 20000 ORDER BY i.code DESC;

ex)아이템 테이블에서 아이템 가격이 20000이상인것을 코드별로 내림차순하여 조회한다.

■ LIKE 함수

Like 뒤에 사용자가 지정한 문자(열)을 비교, 검색하는 역할
ex)Like"학교": 학교로 끝나는 값 검색
ex)Like "오*": 오를 포함하는 값 검색

WHERE 컬러명 LIKE '아무거나' || '포함할것' || '아무거나' ;

SELECT i.* FROM item i WHERE i.name LIKE '%' || '과' || '%' ;

아이템 테이블에서 아이템 이름에 "과"가 포함된 것을 조회한다.

■ 시퀀스 생성

숫자가 순차적으로 증가하는 역할을 한다.

CREATE SEQUENCE 시퀀스이름 INCREMENT BY 증가값 START WITH 시작할 숫자작
NOMAXVALUE NOCACHE;
COMMIT;

 CREATE SEQUENCE seq_student1_no INCREMENT BY 1 START WITH 101
 NOMAXVALUE NOCACHE;
 COMMIT;

student1 테이블을 조회시 NO값이 104부터 시작해서 109까지 1씩 증갈하면서 테이블에 추가되는 모습이 보인다.

■ TO_CHAR 함수

숫자나 날짜를 형식에 맞는 문자로 바꿔주는 역할을 한다.

SELECT i.*, TO_CHAR(i.price*i.quantity,'999,999,999') total 
FROM item i WHERE i.name
LIKE '%' || '과' || '%' ;

i.price i.quantity total가공된 테이블이 total로 바뀐다.
TO_CHAR(i.pricei.quantity,'999,999,999') total는 가격과 수량의 곱한 값을 뒤에 문자형태에 맞게 변환하여 가동된 테이블이 total로 바뀐것이다.

■ View(가상테이블)

view생성하기(view는 조회(SELECT)만 가능하다)

 SELECT i.*, ROW_NUMBER() OVER(ORDER BY i.code DESC) rown
        FROM item i WHERE i1.rown BETWEEN 1 and 5 ORDER BY i.code DESC;

ROW_NUMBER() OVER(ORDER BY i.code DESC) rown은 가상테이블이다
ROW_NUMBER() OVER()는 값이 같더라도 레코드가 달라지면 다른 순서를 매긴다
이 SELECT문에서의 WHERED은 원본데이터에 대한 조건이라서 에러가 발생한다

SELECT i1.* FROM(
    SELECT i.*, ROW_NUMBER() OVER(ORDER BY i.code DESC) rown
        FROM item i ORDER BY i.code DESC
) i1 WHERE i1.rown BETWEEN 1 and 5;

View 생성하기

CREATE OR REPLACE VIEW view 이름 AS

CREATE OR REPLACE VIEW itemview AS 
SELECT i.*, ROW_NUMBER() OVER(ORDER BY i.code DESC) rown 
        FROM item i ORDER BY i.code DESC;

item테이블에서 아이템 코드를 내림차순으로 정렬시키고, 아이템테이블 전체와 아이템코드를 내림차순으로 정렬한 것을 순서를 매겨 출력시킨것을 itemview라는 이름으로 view를 생성한다.

■ 테이블이 두개 합쳐진 view 생성하기

SELECT m.* FROM member m;
SELECT p.* FROM purchase p;
CREATE OR REPLACE VIEW pruchaseview AS
SELECT i.name,i.price, i.quantity, i.content, mp.* FROM item i,(
        SELECT m.userid, m.username, m.userage, m.userphone, m.usergender,p.no, p.cnt, p.regdate, p.code 
        FROM member m, purchase p 
        WHERE m.userid = p.userid    
    )mp WHERE i.code = mp.code;