내가 보려고 쓰는 SQL 잡다한 팁

※ 더 알고있는 정보는 댓글로 적어주세요. 업데이트 하겠습니다.

※ 오라클 SQL 기준입니다

기본 명령어(select, insert, update, delete) 문법 간단 설명

1. SELECT

 

select는 테이블에서 보고싶은 컬럼명을 출력할 때 사용된다. 

SELECT 보고 싶은 컬럼명 [AS 별칭]
FROM 테이블명
[WHERE] [조건식]
[ORDER BY]

 

예시 1)

select coffee_no, coffee_name, coffee_price, coffee_origin 
from coffee

select *
from coffee

select coffee_no coffeeNo, coffee_name coffeeName, 
	   coffee_price coffeePrice, coffee_origin coffeeOrigin 
from coffee

 

설명 : coffee 테이블에서 coffee_no, coffee_name, coffee_price, coffee_origin이라는 컬럼 명을 뽑아온다.

          만약 coffee라는 테이블의 컬럼들이 위 4개가 전부라면 전부 라는 의미의 (*)로 대체할 수 있다.

         

          마지막 코드는 as[별칭]을 적어준 코드이다. as가 있어도 되고 없어도 되지만 웬만하면 붙여주도록 하자.

 

1,2번째 select문 결과

 

3번째 select문 결과, 컬럼명이 별명으로 대체되었다.

 

2. INSERT

테이블에 존재하는 컬럼마다 데이터를 넣어주기 위한 명령어, 설정시 null값을 허용했으면 생략해도 된다.

INSERT INTO 테이블명(name, profile ,date)     (1)
VALUES ("영재", "학사", Now());               (2)

 

(1) NULL이나 DEFAULT로 설정된 컬럼도 존재하기 때문에, 데이터를 삽입할 부분만을 매개변수로 넘겨준다.

(2) VALUES는 실제로 삽입할 데이터를 기입. Now()는 현재 시간을 반환하는 함수.

 

예시 1)

insert into coffee (coffee_no, coffee_name, coffee_price, coffee_origin)
values (6,'디카페인 아메리카노','4500','브라질');

 

coffee 테이블의 각 컬럼에 values의 값을 차례대로 넣어준다.

insert문을 입력하기 전 coffee 테이블

 

insert문을 입력한 후 coffee 테이블

 

3. UPDATE

테이블에 존재하고 있는 데이터를 수정할 때 사용된다.

where절을 사용하지 않으면 모든 행의 컬럼명을 수정 할 수 있으니 주의!

UPDATE 테이블명
SET 수정되어야 할 컬럼명 = 수정되기를 원하는 새로운 값
WHERE 조건절

 

꼭 모든 컬럼명이 들어갈 필요는 없으며, where 조건절을 사용하면 특정 조건에 맞는 컬럼만 변경할 수 있다.

 

예시 1)

update coffee set
coffee_name = '아메리카노'
where coffee_no = 1;

 

coffee_no의 값이 1인 coffee_name의 이름을 아메리카노로 변경한다.

update문을 입력하기 전 coffee 테이블

 

update문을 입력한 후 coffee 테이블

 

4. DELETE

특정 컬럼을 삭제하기 위해 사용된다. where절을 사용하지 않으면 모든 데이터을 삭제할 수 있으니 주의!

DELETE
FROM 테이블명
WHERE 조건식;

 

예시 1)

delete from coffee
where coffee_no = 6;

 

coffee_no가 6인 데이터를 삭제한다.

delete문을 입력한 후 coffee 테이블

delete문을 입력한 후 coffee 테이블

 

 

테이블에서 VO에 사용할 변수명 뽑아오기

--구글 카멜변환(https://heavenly-appear.tistory.com/270)
SELECT COLUMN_NAME
, DATA_TYPE
, CASE WHEN DATA_TYPE='NUMBER' THEN 'private int ' || FN_GETCAMEL(COLUMN_NAME) || ';'
WHEN DATA_TYPE IN('VARCHAR2','CHAR') THEN 'private String ' || FN_GETCAMEL(COLUMN_NAME) || ';'
WHEN DATA_TYPE='DATE' THEN 'private Date ' || FN_GETCAMEL(COLUMN_NAME) || ';'
ELSE 'private String ' || FN_GETCAMEL(COLUMN_NAME) || ';'
END AS CAMEL_CASE
, '<result property="'||FN_GETCAMEL(COLUMN_NAME)||'" column="'||COLUMN_NAME||'"/>' RESULTMAP
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = '테이블이름(대문자)'
AND       USER = '사용자이름(대문자)';

 

컬럼값 자동 증가 쿼리

select nvl(substr(max(book_id),1,1) || trim(to_char(substr(max(book_id),3)+1,'000')),'A001') 
from book;

• nvl() 을 통해 NULL 값일 경우 ‘A001’ 로 치환
• substr(max(book_id),1,2) : book_id 최댓값의 1번째 글자에서부터 1만큼의 길이를 잘라낸다. = 'A'
• trim(to_char(substr(max(book_id),3)+1,'000')),'A001') : book_id 최댓값의 3번째부터 끝까지 짤라낸 후 1을 더하고 00n의 문자열 형식으로 출력한다. = '001'        
• SQL은 문자 + 숫자의 증가식에서는 문자가 숫자로 자동 변환되기 때문에 '001' + 1 의 식이 있어도 '002'가 아닌 2로 값이 나오기 때문에 to_char('','000')의 형변환식이 필요하다.

이 쿼리문은 book_id와 같은 중복값이 들어가면 안되고, 1씩 증가해야할 때 사용될 수 있다.

 

※ 해당 방법을 통해 PK_컬럼 같은 값을 입력받지 않고 자동으로 증가시켜 넣을 수 있다.

<insert id="createPost" parameterType="bookVO">

<selectKey resultType="String" order="BEFORE" keyProperty="bookId">
	select nvl(substr(max(book_id),1,1) || trim(to_char(substr(max(book_id),3)+1,'000')),'A001') 
	from book;
</selectKey>

	insert into book(BOOK_ID, TITLE, CATEGORY, PRICE, INSERT_DATE)
      values(#{bookId}, #{title}, #{category}, #{price}, SYSDATE)

</insert>

 

해당 xml은 bookId의 값을 받아오고 있지 않고 쿼리문이 돌아갈 수 있다. 

 

selectKey 안에 있는 쿼리문이 order="BEFORE"로 인해 insert 태그보다 먼저 계산을 끝낸 후 값을 bookId의 key값으로 보내준다.

 

PL/SQL

종류 : Package, User Function(많이 사용됌), Stored Procedure, Trigger, Anonymous Block

/
DECLARE
BEGIN
    -- I : 자동 선언 정수형 변수
    FOR I IN 1..127 LOOP
    INSERT INTO CONT_US(CONT_CODE, CONT_NAME, CONT_EMAIL, CONT_SUBJECT, CONT_MESSAGE)
    VALUES(
        (SELECT NVL(SUBSTR(MAX(CONT_CODE),1,2)|| TRIM(TO_CHAR(SUBSTR(MAX(CONT_CODE),3) + 1,'000')),'CU001')
        FROM CONT_US), '개똥이'||I, 'test@test.com','제목'||I, '내용'||I);
    END LOOP;
    
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('ERR:'|| SQLERRM);
END;
/
COMMIT;
/

 

아까 만든 쿼리문은 PL/SQL에도 사용할 수 있다.

I가 1~127까지 반복하고, cont_code부분에 서브쿼리로 작성하여 1씩 더해지는 쿼리를 넣어준다.

 

데이터가 없을 때 (cont_code=null)일때도 nvl로 인해 자동으로 CU001로 변환된 후 쿼리가 진행된다.

 

위 PL/SQL의 결과, 127까지 데이터가 자동으로 들어갔다.