DB 0628

CREATE TABLE

CREATE TABLE  <테이블 명>
( 필드명1 TYPE [NOT NULL |  NULL],
  필드명2 TYPE [NOT NULL |  NULL],
                           .
                           .
                           .
Constraint 인덱스키명 Primary Key (필드명1 [,필드명2])
Constraint 외부키명 Foreign Key (필드명2)
                        References 외부테이블명(외부필드명));

CREATE TABLE  lprod
(
    lprod_id   number(5) NOT NULL,           -- 순번
    lprod_gu  char(4)     NOT NULL,            -- 상품분류코드
    lprod_nm  varchar2(40) NOT NULL,        -- 상품분류명
    Constraint pk_lprod Primary Key (lprod_gu)
 );

INSERT  INTO  테이블 명 (필드명1, 필드명2 . . .)
          VALUES (값1, 값2 . . .); 

SELECT

SELECT  [ DISTINCT ]  <데이터 목록>
FROM <테이블목록>
WHERE <검색 조건> 
[ GROUP BY <열 목록> ] 
[ HAVING <검색 조건> ] 
[ ORDER BY <열 목록> ]- DESC가 명시되지 않으면 오름차순 

HAVING:
GROUP에 의한 조건 HAVING에서 씀
반드시 GROUP BY 절과 함께 사용
지정된 그룹의 적용될 조건을 기술 (예)Having COUNT(*) < 4

+산술식

SELECT mem_mileage, mem_mileage / 12 FROM member;

+중복 ROW 제거

SELECT DISTINCT prod_lgu 상품분류  FROM prod;

UPDATE

UPDATE  테이블 명 SET  필드명1 = 값1,
                       필드명2 = 값2. . .,
 WHERE  <검색 조건>
 
 UPDATE  lprod
           SET  lprod_nm = '향수'
     WHERE  lprod_gu = 'P102';

DELETE

DELETE  FROM 테이블 명
   WHERE  <검색 조건>
   
DELETE   FROM  lprod
    WHERE  lprod_gu = 'P202';

Record 단위로 작업

ALTER

ALTER TABLE  <테이블 명>
ADD ( Constraint 인덱스키명 Primary Key (필드명1,필드명2),
           Constraint 외부키명 Foreign key (필드명2)
                        References 외부테이블명(외부필드명) );

ALTER TABLE
1. FIELD 및 변경
2. INDEX or FOREIGN Key 변경
3. CHECK Option변경
ADD - 추가 : FIELD, KEY, CHECK
MODIFY - 변경 : FIELD속성

데이터 정렬

Row를 Sort하고자 하면 ORDER BY 절을 사용
- 역순으로 Sort는 Column명 뒤에 DESC
- Column명 대신 Alias 또는 Select한 Column의 순서로 지정 가능
- Default Sort순서는 Ascending
날짜 : 1990-01-01 ~ 2022-12-31 순으로 정렬
NULL : Ascending에서는 뒤에, Descending에서는 앞에 정렬

논리연산자

우선순위 : ( ), NOT, AND, OR

기타연산자

IN - 질의 탐색을 위해 사용될 둘이상의 표현식을 지정 (NOT연산자와 함께 사용 가능)
BETWEEN - 범위내의 모든 값을 탐색, 두 범위의 한계 값을 포함
LIKE
:컬럼 값을 지정된 패턴과 비교하여 문자형태가 같은
Row를 검색. Wildcard를 사용 문자의 형태 지정
% : 여러 문자 : 한 문자
"%" 나 "" 을 검색하기 위해서는 ESCAPE 사용

함수(문자열)

C || C : 둘이상의 문자열을 연결하는 결합 연산자

SELECT 'A' || 'BCDE' FROM DUAL;
SELECT MEM_ID || ' NAME IS ' || MEM_NAME FROM MEMBER;

CONCAT : 두 문자열을 연결하여 반환

 SELECT  CONCAT('My Name is ', MEM_NAME)  FROM MEMBER;

CHR, ASCII : ASCII값을 문자로, 문자를 ASCII값으로 반환

SELECT CHR(65) "CHR", ASCII('ABC') "ASCII" FROM DUAL;
SELECT ASCII( CHR(65) ) RESULT FROM DUAL;
SELECT CHR(75) "CHR", ASCII('K') "ASCII" FROM DUAL;
- 회원테이블의 회원ID Column의 ASCII값을 검색하시오
SELECT ASCII(MEM_ID) AS 회원ASCII,
               CHR(ASCII(MEM_ID)) AS 회원CHR
FROM MEMBER;

LOWER : 해당 문자나 문자열을 소문자로 반환,
UPPER : 대문자로 반환,
INITCAP : 첫 글자를 대문자로 나머지는 소문자로 반환

SELECT LOWER('DATA manipulation Language') "LOWER", 
                   UPPER('DATA manipulation Language') "UPPER",
                   INITCAP('DATA manipulation Language') "INITCAP"
    FROM dual;

LPAD,RPAD(c1, n, [c2]) : 지정된 길이 n에서 c1을 채우고 남은 공간을 c2로 채워서 반환

SELECT LPAD ('Java', 10, '*') "LPAD", 
       RPAD ('Flex', 12, '^') "RPAD"
   FROM DUAL;

LTRIM,RTRIM(c1, [c2])
: LTRIM은 좌측, RTRIM은 우측의 공백문자를 제거
: c2문자가 있는 경우 일치하는 문자를 제거

SELECT '<' || LTRIM('     AAA     ') || '>' "LTRIM1",
       '<' || LTRIM('Hello World', 'He') || '>' "LTRIM2" 
   FROM DUAL;
 SELECT '<' || RTRIM('     A A A     ') || '>' "RTRIM1",
        '<' || RTRIM('Hello World', 'ld') || '>' "RTRIM2"
    FROM DUAL;

TRIM
: LTRIM, RTRIM함수를 조합한 형태( [ [LEADING|TRAILING|BOTH][c1] FROM] source )
: 문자열의 앞, 뒤, 또는 앞뒤에서 공백 문자를 제거함

SELECT '<' || TRIM('     AAA     ') || '>'  TRIM1,
       '<' || TRIM(LEADING 'a' FROM 'aaAaBaAaa') || '>'  TRIM2,
       '<' || TRIM(TRAILING 'a' FROM 'aaAaBaAaa') || '>'  TRIM3,
       '<' || TRIM(BOTH 'a' FROM 'aaAaBaAaa') || '>'  TRIM4, 
       '<' || TRIM( 'a' FROM 'aaAaBaAaa') || '>'  TRIM5 
  FROM DUAL;

SUBSTR(c, m, [n])
: 문자열의 일부분을 선택 - BYTE 아니고 글자 기준
c문자열의 m위치부터 길이 n만큼의 문자 리턴
m이 0 또는 1이면 첫 글자를 의미
m이 음수이면 뒤쪽에서부터 처리

SELECT SUBSTR('SQL PROJECT', 1, 3)  RESULT1,
                  SUBSTR('SQL PROJECT', 5)  RESULT2,
                  SUBSTR('SQL PROJECT', -7, 3) RESULT3   FROM DUAL;

-회원테이블의 성씨 조회
  SELECT mem_id,   SUBSTR(mem_name, 1, 1) 성씨
      FROM member;

TRANSLATE(c1, c2, c3)
: c1문자열에 포함된 문자 중 c2에 지정된 문자가 c3문자로 각각 변경
: c3 문자가 c2보다 적은 경우 해당 문자는 제거

SELECT
TRANSLATE('2009-02-28', '0123456789-',  'ABCDEFGHIJK') RESULT
FROM DUAL; 

REPLACE(c1, c2, [c3])
: 문자나 문자열을 치환
: c1에 포함된 c2문자를 c3값으로 치환
: c3가 없는 경우 찾은 문자를 제거

SELECT REPLACE('SQL Project', 'SQL', 'SSQQLL') 문자치환1,
        REPLACE('Java Flex Via', 'a')
FROM DUAL;

SELECT BUYER_NAME, REPLACE(BUYER_NAME, '삼', '칠') "삼->칠"
FROM BUYER;

INSTR(c1, c2, [m, [n]])
: c1문자열에서 c2문자가 처음 나타나는 위치를 리턴
: m은 시작 위치, n은 n번째

SELECT INSTR('hello heidi', 'he')  AS RESULT1,  
       INSTR('hello heidi', 'he', 3) AS RESULT2 
   FROM DUAL;

LENGTH,LENGTHB : 문자열의 길이를 돌려줌(한글은 3바이트)

SELECT LENGTH('SQL 프로젝트') "LENGTH",  --8
       LENGTHB('SQL 프로젝트') "LENGTHB" --16
  FROM DUAL;

GREATEST,LEAST( m [,n1 …] )
: 열거된 항목 중 가장 큰 또는 작은 항목을 리턴
: m의 데이터타입에 따라 n의 항목도 판단

SELECT GREATEST(10, 20, 30) "큰값",
                   LEAST(10,20,30) "작은값"   
 FROM DUAL;

SELECT GREATEST('강아지', 256, '송아지')  "큰값",
                   LEAST('강아지', 256, '송아지')  "작은값"  
 FROM DUAL;

ROUND(n, l) : 지정된 자릿수(l) 밑에서 반올림 - 숫자의 반올림 : ROUND(Column명, 위치)
TRUNC(n, l) : ROUND와 동일. 단, 반올림이 아닌 절삭

SELECT ROUND(345.123, -1) 결과1,
        TRUNC(345.123, -1) 결과2
FROM DUAL;
--회원 테이블의 마일리지를 12로 나눈 값을 검색(소수3째자리 반올림, 절삭)
SELECT (ROUND(MEM_MILEAGE/12,3)) FROM MEMBER;

MOD(c, n) :n으로 나눈 나머지

SELECT MOD(10, 3) FROM DUAL;

FLOOR(n) : n과 같거나 작은 수 중에 가장 큰 정수
CEIL(n)
: n과 같거나 큰 수 중에 가장 작은 정수
: 소수점 이하의 값이 존재하면 무조건 올림
: 급여.세금과 같은 금액관련 계산 중에 자주 사용
COS, SIN, TAN, LOG 등 수학관련함수

SELECT  FLOOR(1332.69), CEIL(1332.69)  FROM DUAL;

 SELECT  FLOOR(-1332.69), CEIL(-1332.69) FROM DUAL;

REMAINDER(c, n) : n으로 나눈 나머지, MOD 함수와 유사

MOD 함수, 나머지 값 구하기 함수 구현방식
나머지 = (원값) - (나눌값 x 나눈값을 소수 첫째자리에서 버림한 값)
= 10 - 3 * FLOOR( 10 / 3 )

REMAINDER 함수, 나머지 값 구하기 함수 구현방식
나머지 = (원값) - (나눌값 x 나눈값을 소수 첫째자리에서 반올림한 값)
= 10 - 3 * ROUND( 10 / 3 )

 SELECT MOD(10, 3), REMAINDER(10, 3)  FROM DUAL;
    = 10-3*FLOOR(3.33333…,0)    = 10-3*ROUND(3.33333…,0)
    = 10-3*3  					= 10-3*3
    = 10-9						= 10-9
    = 1							= 1

SELECT MOD(10, 3.7) , REMAINDER(10, 3.7) FROM DUAL;
= 10-3.7FLOOR(2.702702…,0) = 10-3.7ROUND(2.702702…,0)
= 10-3.72 = 10-3.73
= 10-7.4 = 10-11.1
= 2.6 = -1.1

**WIDTH_BUCKET(c, min, max, b)**
:min에서 max의 범위로 설정하고  b구간으로 나누어 c가 어느 구간에 속하는지 리턴

SELECT WIDTH_BUCKET(88, 0, 100, 10) FROM DUAL;

• 회원 테이블에서 회원이름, 마일리지, 등급을 출력하시오
  (단, 등급은 마일리지를 500 부터 9000까지 5등급으로 한다.)
  SELECT MEM_ID, MEM_NAME, MEM_MILEAGE,
  WIDTH_BUCKET(MEM_MILEAGE, 9000, 500, 5) MILEAGE_GROUP
  FROM MEMBER
  ORDER BY MILEAGE_GROUP;

#### 함수(숫자열)
ABS(n) : 절대 값
SIGN(n) : 양수, 음수, 0을 구분. 각 1, -1, 0 리턴. 음수인지 판단/비교 시 사용
POWER(n, y) : 승수 값(n의 y승)
SQRT(n) : n의 제곱근

SELECT ABS(-365) FROM DUAL;

SELECT SIGN(12), SIGN(0), SIGN(-55)
FROM DUAL;

SELECT POWER(3, 2) , POWER(2, 10)
FROM DUAL;

SELECT SQRT(2) , SQRT(9) FROM DUAL;

#### WHILE문 사용하여 다음 형태의 피라미드 만들기

SET SERVEROUTPUT ON

DECLARE --DECLARE선언 : 변수 지정
V_ID NUMBER := 1; --(:=) => 초기값 지정
V_ID2 NUMBER := 10;
BEGIN
DBMS_OUTPUT.PUT_line(' '); --맨 위에 공백 한 줄
WHILE V_ID < 20 LOOP --END LOOP까지
DBMS_OUTPUT.PUT(RPAD(' ', v_id2, ' '));
DBMS_OUTPUT.PUT_LINE(RPAD('', V_ID, ''));
V_ID := V_ID + 2;
V_ID2 := V_ID2 - 1;
END LOOP;
END;
/

결과)

         *
        ***
       *****
      *******
     *********
    ***********
   *************
  ***************
 *****************
*******************

PL/SQL 프로시저가 성공적으로 완료되었습니다.