몰래보는 SQL Query 코드조각


      
      
      
    
    
      <code />
    
-- 두 번 이상 쓰인 이름, 해당 이름이 쓰인 횟수
SELECT
    NAME
    , COUNT(NAME)
FROM
    ANIMAL_INS
WHERE
    NAME IS NOT NULL
GROUP BY
    NAME
HAVING
    COUNT(NAME) > 1
ORDER BY
    NAME


      
      
      
    
    
      <code />
    
-- 각 시간대별 건수
SELECT
    HOUR(DATETIME) AS HOUR
    , COUNT(HOUR(DATETIME))
FROM
    ANIMAL_OUTS
GROUP BY
    HOUR
HAVING
    HOUR BETWEEN 9 AND 19
ORDER BY
    HOUR


      
      
      
    
    
      <code />
    
-- 각 시간대별 건수(빈 값 포함) ★★★
SET @HOUR = -1;
SELECT
    (@HOUR := @HOUR +1) AS HOUR
    , (
        SELECT COUNT(HOUR(DATETIME))
        FROM
            ANIMAL_OUTS
        WHERE
            HOUR(DATETIME)=@HOUR
      ) AS COUNT
FROM
    ANIMAL_OUTS
WHERE
    @HOUR < 23;

3. JOIN 관련


      
      
      
    
    
      <code />
    
-- 한쪽 테이블에만 값이 있는 경우(OUTER JOIN)
SELECT
    OUTS.ANIMAL_ID
    , OUTS.NAME
FROM
    ANIMAL_OUTS OUTS
    LEFT OUTER JOIN
        ANIMAL_INS INS
    ON
        OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE
    INS.ANIMAL_ID IS NULL
ORDER BY
    OUTS.ANIMAL_ID


      
      
      
    
    
      <code />
    
-- 양쪽 테이블에 일치하는 값이 있고
-- JOIN 후 특정 조건 검색
SELECT
    INS.ANIMAL_ID
    , INS.NAME
FROM
    ANIMAL_INS INS
    JOIN
        ANIMAL_OUTS OUTS
    ON
        INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE
    INS.DATETIME > OUTS.DATETIME
ORDER BY
    INS.DATETIME


      
      
      
    
    
      <code />
    
-- INNER JOIN 후 평균 구하기
SELECT
    RI.REST_ID,
    RI.REST_NAME,
    RI.FOOD_TYPE,
    RI.FAVORITES,
    RI.ADDRESS,
    RR.SCORE
FROM
    REST_INFO RI
    INNER JOIN (
        SELECT
            REST_ID,
            ROUND(AVG(REVIEW_SCORE), 2) SCORE
        FROM
            REST_REVIEW
        GROUP BY REST_ID
    ) RR
    ON RI.REST_ID = RR.REST_ID
WHERE
    RI.ADDRESS LIKE '서울%'
ORDER BY
    RR.SCORE DESC,
    RI.FAVORITES DESC

4. String 관련


      
      
      
    
    
      <code />
    
-- 특정 문자열 조건 검색
SELECT
    ANIMAL_ID
    , NAME
FROM
    ANIMAL_INS
WHERE
    ANIMAL_TYPE = "Dog"
    AND NAME LIKE "%EL%"
ORDER BY
    NAME


      
      
      
    
    
      <code />
    
-- 특정 문자열 조건이면 O, X로 표시하기
SELECT
    ANIMAL_ID
    , NAME
    , CASE
        WHEN
            SEX_UPON_INTAKE LIKE '%Neutered%'
            OR SEX_UPON_INTAKE LIKE '%Spayed%'
        THEN 'O'
        ELSE 'X'
    END as '중성화'
FROM
    ANIMAL_INS
ORDER BY
    ANIMAL_ID

5. Date 관련


      
      
      
    
    
      <code />
    
-- 날짜끼리 연산
SELECT
    INS.ANIMAL_ID
    , INS.NAME
    -- OUTS.DATETIME - INS.DATETIME
FROM
    ANIMAL_INS INS
    JOIN
        ANIMAL_OUTS OUTS
    ON
        INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE
    OUTS.DATETIME > INS.DATETIME
ORDER BY
    OUTS.DATETIME - INS.DATETIME DESC
LIMIT 2


      
      
      
    
    
      <code />
    
-- DATETIME 포맷 변경
SELECT
    ANIMAL_ID
    , NAME
    , DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜
FROM
    ANIMAL_INS
ORDER BY
    ANIMAL_ID

6. 중복제거(DISTINCT) 관련


      
      
      
    
    
      <code />
    
-- 중복을 제거한 카운트
SELECT
    COUNT(DISTINCT NAME)
FROM
    ANIMAL_INS

7. 빈 값(NULL) 처리


      
      
      
    
    
      <code />
    
-- 이름이 NULL 값일 때 처리
SELECT
    ANIMAL_TYPE
    , IFNULL(NAME, "No name")
    , SEX_UPON_INTAKE
FROM
    ANIMAL_INS
ORDER BY
    ANIMAL_ID

8. 복잡한 쿼리(PL/SQL)


      
      
      
    
    
      <code />
    
-- 동적 쿼리를 이용한 평균나이 구하기
CREATE OR REPLACE FUNCTION avg_ages(n INTEGER)
RETURNS NUMERIC AS $$
DECLARE
    r RECORD;
    total NUMERIC := 0;
    query TEXT;
BEGIN
    query := 'SELECT age FROM person LIMIT $1';
    FOR r IN EXECUTE query USING n
    LOOP
        total := total + r.age;
    END LOOP;
    RETURN total / n;
END;
$$ LANGUAGE plpgsql;


      
      
      
    
    
      <code />
    
--- 재구매가 일어난 상품과 회원 리스트


      
      
      
    
    
      <code />
    
-- 정렬 기준 세분화

9. DCL(Data Control Language) 관련


      
      
      
    
    
      <code />
    
-- WHERE, UPDATE를 쓸 수 있도록 권한 부여
GRANT
    SELECT,
    UPDATE
ON
    USERS.list
TO
    managerA

10. DDL(Data Definition Language) 관련


      
      
      
    
    
      <code />
    
-- 테이블 정의
CREATE TABLE USER_INFO(
    이름    varchar2(10),
    연락처  number not null,
    방문일  date,
    고객번호    varchar2(10) primary key -- PK = Not Null & Unique
);


      
      
      
    
    
      <code />
    
-- 테이블 수정
ALTER TABLE USER_INFO
RENAME TO USERS

-- 컬럼 수정
ALTER TABLE USER_INFO
RENAME COLUMN 연락처
    TO 전화번호

-- 컬럼 속성 변경
ALTER TABLE USER_INFO
MODIFY (이름 varchar(20) NOT NULL);

-- 컬럼 추가
ALTER TABLE USER_INFO
ADD (주소 varchar(10) NOT NULL);

ALTER TABLE USER_INFO
ADD CONSTRAINT 연락처_PK FOREIGN KEY(연락처); -- <제약조건 명>으로 제약조건을 추가 가능

-- 컬럼 삭제
ALTER TABLE USER_INFO
DROP COLUMN 주소;

ALTER TABLE USER_INFO
DROP CONSTRAINT 연락처_PK -- 제약조건도 삭제 가능


      
      
      
    
    
      <code />
    
-- 테이블 삭제
-- 데이블 전체를 삭제, 공간, 객체를 삭제한다. 삭제 후 절대 되돌릴 수 없다.
DROP 명령어는 데이블 전체를 삭제, 공간, 객체를 삭제한다. 삭제 후 절대 되돌릴 수 없다.
DROP TABLE USER_INFO

-- 테이블 초기화(구조는 남기고, 데이터만 초기화)
-- 테이블 용량이 줄어 들고, 인덱스 등도 모두 삭제 된다.
-- 삭제 후 절대 되돌릴 수 없다.
TRUNCATE TABLE USER_INFO

-- 데이터 삭제(DML이지만 맥락상 추가함)
-- 테이블 용량이 줄어 들지 않고, 원하는 데이터만 지울 수 있다.
-- 삭제 후 잘못 삭제한 것을 되돌릴 수 있다.
DELETE FROM USER_INFO
DELETE USER_INFO -- FROM 생략가능
DELETE FROM USER_INFO
    WHERE 이름 = 'Jane';

11. 기타 CASE 모음


      
      
      
    
    
      <code />
    
-- 오류 CASE 모음

-- NOT NULL인 컬럼을 채우지 않으면 오류
INSERT INTO USER_INFO (이름, 방문일) VALUES ('Jane', 2020-09-05)

-- 컬럼명 지정이 이뤄지지 않으면 전체값이 들어가야함
INSERT INTO USER_INFO VALUES ('Jane', '010-0000-0000' , 2020-09-05)


      
      
      
    
    
      <code />
    
-- 특수한 CASE

INSERT INTO USER_INFO (...)
SAVEPOINT S1
UPDATE USER_INFO SET ... WHERE ...
SAVEPOINT S2
--COMMIT << 커밋을 했다면 S2상태로 복원된다.
ROLLBACK S1;
COMMIT;
-- ROLLBACK 시, SAVEPOINT가 있을때 => COMMIT이 없는 경우, 해당 SAVEPOINT 이후는 무효가 된다.
-- ROLLBACK 시, SAVEPOINT가 없을때 => 가장 최신의 COMMIT 상태로 복원된다.


      
      
      
    
    
      <code />
    

---



<br>

_참고자료_

_[PostgreSQL의 PL/pgSQL 튜토리얼 – 3 : 변수와 상수](http://www.gisdeveloper.co.kr/?p=4573)_

_[PostgreSQL의 PL/pgSQL 튜토리얼 – 4 : IF 조건문](http://www.gisdeveloper.co.kr/?p=4582)_

_[PostgreSQL의 PL/pgSQL 튜토리얼 – 5 : CASE 조건문](http://www.gisdeveloper.co.kr/?p=4601)_

_[PostgreSQL의 PL/pgSQL 튜토리얼 – 6 : 반복문](http://www.gisdeveloper.co.kr/?p=4621)_

_[PostgreSQL의 PL/pgSQL 튜토리얼 – 7 : 질의 결과를 반환하는 함수](http://www.gisdeveloper.co.kr/?p=4642)_

_[SQLD 자격증 문제 풀이](https://www.youtube.com/watch?v=8uP_E6SyiuM&list=PLLyuWzYmiwulMJrt5B-atyAFjEGDRYDDd&index=7)_

'How-to' 카테고리의 다른 글

몰래보는 VS Code 단축키 정리 (0)	2023.01.18
몰래보는 인텔리제이(IntelliJ) 단축키 정리 (0)	2023.01.18
몰래보는 파이썬(Python) 코드조각 (0)	2023.01.18
몰래보는 자바(Java) 코드조각 (0)	2023.01.18
마크다운(Markdown) 작성법 정리 (0)	2023.01.18

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

1. INDEX

2. GROUP 관련

3. JOIN 관련

4. String 관련

5. Date 관련

6. 중복제거(DISTINCT) 관련

7. 빈 값(NULL) 처리

8. 복잡한 쿼리(PL/SQL)

9. DCL(Data Control Language) 관련

10. DDL(Data Definition Language) 관련

11. 기타 CASE 모음

'How-to' 카테고리의 다른 글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

'How-to' 카테고리의 다른 글

검색 태그

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역