5.SQL 기본

Posted on In

5.SQL 기본

SELECT 문

데이터 조회 조회할 테이블과 항목을 명시해 준다.

1
2
3
4
5
SELECT
		CUSTOMER_ID ,
		NAME ,
		CREDIT_LIMIT
FROM CUSTOMERS ;

모든 항목 조회는

1
2
3
SELECT
		*
	FROM CUSTOMERS

DUAL 테이블

DB에서 기본적으로 제공해주는 테이블로서 연산 작업에 사용할 수 있다.

1
2
3
SELECT
		*
	FROM DUAL;
1
2
3
SELECT
		(10 + 5) / 2 AS VAL
	FROM DUAL;

ORDER BY

크기를 기준으로 정렬해준다.

  • ASC: 오름차순 (디폴트)
  • DESC: 내림차순
1
2
3
4
5
6
SELECT
		NAME,
		ADDRESS,
		CREDIT_LIMIT,
	FROM CUSTOMERS
	ORDER BY NAME ASC;

ORDER BY 다음으로는 여러개의 항목을 줄 수 있다.

1
2
3
4
5
6
SELECT
		FIRST_NAME,
		LAST_NAME,
	FROM CONTACTS
	ORDER BY FIRST_NAME, LAST_NAME DESC
;

FIRST_NAME은 디폴트 값인 오름차순으로 정렬하고 그다음에 LAST_NAME을 기준으로 내림차순 정렬한다.

DISTINCT

중복 값을 제거한다.

1
2
3
4
SELECT
		DISTINCT FIRST_NAME
	FROM CONTACTS
	ORDER BY FIRST_NAME

조회 결과 중에서 FIRST_NAME 이 겹친다면 중복된 요소를 제거한다.

WHERE

조건을 주어서 검생할 수 있다.

1
2
3
4
5
6
7
SELECT
		PRODUCT_NAME,
		DESCRIPTION,
		LIST_PRICE,
		CATEGORY_ID
	FROM PRODUCTS
	WHERE PRODUCT_NAME='Kinston';

일반적인 프로그래밍 언어와 마찬가지로 AND 와 OR을 사용할 수 있고 조건 사이에 넣어 주면 된다.

BETWEEN을 입력하여 이상 이하에 조건을 간략하게 표현할 수 있다.

1
2
3
4
5
6
SELECT
		PRODUCT_NAME,
		LIST_PRICE
	FROM PRODUCTS
	WHERE LIST_PRICE BETWEEN 650 AND 680
	ORDER BY LIST_PRICE;

LIST_PRICE 가 650 이상 680 미만인 것을 조회한다.

IN 조건을 주면 집합에 속한 모든 요소를 출력할 수 있다. OR 조건과 비슷하다고 생각하면 된다.

1
2
3
4
5
6
SELECT
       PRODUCT_NAME
     , CATEGORY_ID
FROM PRODUCTS
WHERE CATEGORY_ID IN (1, 4)
ORDER BY PRODUCT_NAME ;

LIKE 를 사용해서 조건을 넣을 수 있다. 밑에는 ASUS로 시작하는 요소를 검색한다.

1
2
3
4
5
6
SELECT
       PRODUCT_NAME
     , LIST_PRICE
FROM PRODUCTS
WHERE PRODUCT_NAME LIKE 'Asus%'
ORDER BY LIST_PRICE ;

INSERT, UPDATE, DELETE 문

테이블 생성

1
2
3
4
5
6
7
DROP TABLE DISCOUNTS;
CREATE TABLE DISCOUNTS (
DISCOUNT_ID NUMBER GENERATED BY DEFAULT AS IDENTITY , DISCOUNT_NAME VARCHAR2(255) NOT NULL
, AMOUNT NUMBER(3, 1) NOT NULL
, START_DATE DATE NOT NULL
, EXPIRED_DATE DATE NOT NULL
);

INSERT

데이터를 삽입한다. 데이터를 삽입을 확정하기 위해서 COMMIT을 해야 한다. 하지만 보통 DB tools 에서는 자동으로 COMMIT 까지 들어가도록 지원한다. 테이블 생성 명령어는 COMMIT 이 필요하지 않다.

1
2
3
4
5
6
7
8
INSERT INTO
DISCOUNTS (
DISCOUNT_NAME , AMOUNT
, START_DATE
, EXPIRED_DATE )
VALUES ( 'Summer Promotion' , 9.5
, DATE '2017-05-01' , DATE '2017-08-31' );
COMMIT;

UPDATE

테이블 안에 데이터를 업데이트 한다.

1
2
3
4
UPDATE PARTS
SET COST = 130
WHERE PART_ID = 1 ;
COMMIT;

조건을 입력하지 않으면 테이블에 모든 행을 업데이트 한다. 조건문이 없는 UPDATE, DELETE 문은 DB tools 에서 경고문을 던진다.

1
2
3
4
UPDATE PARTS
   SET COST = COST * 1.05
;
COMMIT;

DELETE

데이터를 삭제한다.

1
2
3
4
5
6
DELETE
  FROM SALES
 WHERE ORDER_ID = 1
   AND ITEM_ID = 1
;
COMMIT;

VIEW

VIEW 는 물리적으로 데이터를 저장하지 않는 테이블이라고 생각할 수 있는데 쉽게 생각하면 어떤 SQL에 결과값을 저장해 둔 것이라고 생각하면 된다.

INLINE VIEW

SQL 문 안에서 INLINE VIEW 를 사용할 수 있다.

1
2
3
4
5
6
7
8
SELECT A.* FROM
(
	SELECT
		NAME
	, CREDIT_LIMIT
	FROM CUSTOMERS
 )A
;

VIEW

복잡한 쿼리에 대한 결과를 VIEW로 저장해두고 사용할 수 있다. 다음 쿼리는 발송된 주문에 대한 연도별 각 고객의 매출 총 금액을 구하는 SQL 문이다.

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
C.NAME AS CUSTOMER
, TO_CHAR(A.ORDER_DATE, 'YYYY') AS YEAR
, SUM( B.QUANTITY * B.UNIT_PRICE ) SALES_AMOUNT FROM ORDERS A
     , ORDER_ITEMS B
, CUSTOMERS C
WHERE 1=1
AND A.STATUS = 'Shipped'
AND A.ORDER_ID = B.ORDER_ID
AND A.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY C.NAME, TO_CHAR(A.ORDER_DATE, 'YYYY') ORDER BY C.NAME
;

CREATE OR REPLACE VIEW 로 VIEW를 생성할 수있다.

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE VIEW CUSTOMER_SALES AS SELECT
C.NAME AS CUSTOMER
, TO_CHAR(A.ORDER_DATE, 'YYYY') AS YEAR
, SUM( B.QUANTITY * B.UNIT_PRICE ) SALES_AMOUNT
FROM ORDERS A
, ORDER_ITEMS B , CUSTOMERS C
 WHERE 1=1
   AND A.STATUS = 'Shipped'
   AND A.ORDER_ID = B.ORDER_ID
   AND A.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY C.NAME, TO_CHAR(A.ORDER_DATE, 'YYYY') ORDER BY C.NAME;

다음 부터는 CUSTOMER_SALES 로 VIEW 테이블을 조회할 수 있다.

1
2
3
4
5
6
SELECT
       CUSTOMER
     , SALES_AMOUNT
FROM CUSTOMER_SALES
WHERE YEAR = 2017
ORDER BY SALES_AMOUNT DESC;

서브 쿼리

서브쿼리 기본

select 절에 또다른 select절이 있어서 쿼리 안에 또다른 쿼리가 있으면 서브 쿼리라고 한다.

1
2
3
4
5
6
7
8
SELECT
PRODUCT_ID , PRODUCT_NAME , LIST_PRICE
  FROM PRODUCTS
 WHERE LIST_PRICE = (
SELECT
                          MAX(LIST_PRICE)
                     FROM PRODUCTS
);

스칼라 서브 쿼리

select 중간에 쿼리가 있으면 스칼라 서브 쿼리라고 한다.

1
2
3
4
5
6
7
8
9
10
SELECT
A.PRODUCT_NAME
, A.LIST_PRICE
, ROUND( (SELECT AVG(K.LIST_PRICE)
                 FROM PRODUCTS K
               WHERE K.CATEGORY_ID = A.CATEGORY_ID
            ), 2
       ) AVG_LIST_PRICE
  FROM PRODUCTS A
ORDER BY A.PRODUCT_NAME;

인라인 뷰 서브 쿼리

앞서서 뷰를 보았는데 인라인 뷰도 일종의 서브 쿼리이다. 그래서 이를 인라인 뷰 서브쿼리라고 한다.

1
2
3
4
5
6
7
8
9
10
11
SELECT ORDER_ID
, ORDER_VALUE
FROM
(
SELECT ORDER_ID
, SUM( QUANTITY * UNIT_PRICE ) ORDER_VALUE
  FROM ORDER_ITEMS
 GROUP BY ORDER_ID
 ORDER BY ORDER_VALUE DESC
)
WHERE ROWNUM <= 10;