oracle SQL 실습 예제 모음 : 기본 DML, 기본 TCL index view

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)

);

SELECT * FROM LPROD;

CREATE TABLE BUYER

(

 BUYER_ID       CHAR(6)     NOT NULL,   --거래처코드

 BUYER_NAMES    VARCHAR2(40)NOT NULL,   --거래처명

 BUYER_LGU      CHAR(4)     NOT NULL,   --취급상품 대분류

 BUYER_BANK     VARCHAR2(60),           --은행

 BUYER_BANKNO   VARCHAR2(60),           --계좌번호

 BUYER_BANKNAME VARCHAR2(15),           --예금주

 BUYER_ZIP      CHAR(7),                --우편번호

 BUYER_ADD1     VARCHAR2(100),          --주소1

 BUYER_ADD2     VARCHAR2(70),           --주소2

 BUYER_COMTEL   VARCHAR2(14)NOT NULL,   --전화번호

 BUYER_FAX      VARCHAR2(20)NOT NULL    --FAX번호

);

  

  --BUYER 테이블의 구조 확인(DESCRIBE)

DESC BUYER;

ALTER TABLE BUYER

ADD(

    CONSTRAINT PK_BUYER PRIMARY KEY(BUYER_ID)

    );

DESC BUYER;  

ALTER TABLE BUYER

ADD(

    BUYER_MAIL      VARCHAR2(60) NOT NULL,

    BUYER_CHARGER   VARCHAR2(20)        ,

    BUYER_TELEXT    VARCHAR2(2) 

    );

    

/*

        ALTER TABLE BUYER

        ADD : 컬럼추가

        MODIFY : 컬럼 수정

        DROP : 컬럼 삭제

        RENAME : 컬럼명 변경

*/

ALTER TABLE BUYER

MODIFY(

    BUYER_NAMES VARCHAR2(60)

);

ALTER TABLE BUYER

ADD(

    CONSTRAINT FR_LPROD_BUYER FOREIGN KEY(BUYER_LGU)

                REFERENCES LPROD(LPROD_GU)

);

      

SELECT * FROM USER_CONSTRAINTS; 

--BUYER_TELEXT 컬럼에 입력할 데이터는

--오직 두자리 숫자만 가능함

ALTER TABLE BUYER

ADD CONSTRAINT CHECK_PHONE

CHECK(REGEXP_LIKE(BUYER_TELEXT, '[0-9][0-9]'));

--검색속도를 빠르게 하기위해 인덱스 추가

CREATE INDEX IDX_BUYER

ON BUYER(BUYER_NAME, BUYER_ID);

DROP INDEX IDX_BUYER;

CREATE TABLE prod

(

    prod_id             varchar2(10)        NOT NULL,

    prod_name           varchar2(40)        NOT NULL,

    prod_lgu            char(4)             NOT NULL,

    prod_buyer          char(6)             NOT NULL,

    prod_cost           number(10)          NOT NULL,

    prod_price          number(10)          NOT NULL,

    prod_sale           number(10)          NOT NULL,

    prod_outline        varchar2(100)       NOT NULL,

    prod_detail         clob,

    prod_img            varchar2(40)        NOT NULL,

    prod_totalstock     number(10)          NOT NULL,

    prod_insdate        date,

    prod_properstock    number(10)          NOT NULL,

    prod_size           varchar2(20),

    prod_color          varchar2(20),

    prod_delivery       varchar2(225),

    prod_unit           varchar2(6),

    prod_qtyin          number(10),

    prod_qtysale        number(10),

    prod_mileage        number(10),

    Constraint pl_prod  Primary key (prod_id),

    Constraint fr_prod_lprod    Foreign key (prod_lgu)

                                                        References lprod(lprod_gu),

    Constraint fr_prod_buyer Foreign Key (prod_buyer)

                                                        References buyer(buyer_id) 

);

CREATE TABLE buyprod

(

    buy_date    date            NOT NULL,

    buy_prod    varchar2(10)    NOT NULL,

    buy_qty     number(10)      NOT NULL,

    buy_cost    number(10)      NOT NULL,

    Constraint pk_buyprod Primary Key (buy_date,buy_prod),

    Constraint fr_buyprod_prod  Foreign Key (buy_prod) References prod(prod_id)

);

CREATE TABLE member

(

    mem_id          varchar2(15)        NOT NULL,

    mem_pass        varchar2(15)        NOT NULL,

    mem_name        varchar2(20)        NOT NULL,

    mem_regno1      char(6)             NOT NULL,

    mem_regno2      char(7)             NOT NULL,

    mem_bir         date,

    mem_zip         char(7)             NOT NULL,

    mem_add1        varchar2(100)       NOT NULL,

    mem_add2        varchar2(80)        NOT NULL,

    mem_hometel     varchar2(14)        NOT NULL,

    mem_comtel      varchar2(14)        NOT NULL,

    mem_hp          varchar2(15),

    mem_mail        varchar2(60)        NOT NULL,

    mem_job         varchar2(40),

    mem_like        varchar2(40),

    mem_memorial    varchar2(40),

    mem_memorialday date,

    mem_mileage     number(10),

    mem_delete      varchar2(1),

    Constraint pk_member Primary Key (mem_id)

);

CREATE TABLE cart

(

    cart_member varchar2(15)    NOT NULL,

    cart_no     char(13)        NOT NULL,

    cart_prod   varchar2(10)    NOT NULL,

    cart_qty    number(8)       NOT NULL,

    Constraint fr_cart Primary Key (cart_no,cart_member),

    Constraint fr_cart_member Foreign Key (cart_member)

                                                        References member(mem_id),

    Constraint fr_cart_prod Foreign Key (cart_prod)

                                                    References prod(prod_id)

);

CREATE TABLE ziptb

(

    zipcode     char(7)           NOT NULL, 

    sido        VARCHAR2(2 char)  NOT NULL,

    gugun       varchar2(10 char) NOT NULL,

    dong        varchar2(30 char) NOT NULL,

    bunji       varchar2(10 char),

    seq         number(5)         NOT NULL

);

CREATE INDEX idx_ziptb_zipcode  ON ziptb(zipcode);

    

INSERT INTO LPROD (LPROD_ID, LPROD_GU, LPROD_NM) VALUES (1,'P101','컴퓨터제품');

INSERT INTO LPROD (LPROD_ID, LPROD_GU, LPROD_NM) VALUES (2,'P102','전자제품');

INSERT INTO LPROD (LPROD_ID, LPROD_GU, LPROD_NM) VALUES (3,'P201','여성캐주얼');

INSERT INTO LPROD (LPROD_ID, LPROD_GU, LPROD_NM) VALUES (4,'P202','남성캐쥬얼');

INSERT INTO LPROD (LPROD_ID, LPROD_GU, LPROD_NM) VALUES (5,'P301','피혁잡화');

INSERT INTO LPROD (LPROD_ID, LPROD_GU, LPROD_NM) VALUES (6,'P302','화장품');

INSERT INTO LPROD (LPROD_ID, LPROD_GU, LPROD_NM) VALUES (7,'P401','음반/CD');

INSERT INTO LPROD (LPROD_ID, LPROD_GU, LPROD_NM) VALUES (8,'P402','도서');

INSERT INTO LPROD (LPROD_ID, LPROD_GU, LPROD_NM) VALUES (9,'P403','문구류');

COMMIT;

--  * : 아스트리크(모든컬럼)

SELECT * FROM LPROD;

SELECT LPROD_ID

    ,  LPROD_GU

    ,  LPROD_NM

FROM LPROD    

WHERE LPROD_ID = '3';

--LPROD_ID 가 3인 ROW를 SELECT 하시오

--LPROD_ID, LPROD_GU, LPROD_NM 컬럼을 모두 출력하시오

SELECT LPROD_ID

    ,  LPROD_GU

    ,  LPROD_NM

FROM LPROD

WHERE LPROD_ID = 3;

-- ID는 숫자이다 그냥 3을 쓰면된다

-- 하지만 '3'으로 문자를 입력해도 ID가 숫자라서 (숫자가 우선순위를 가져서 자동 숫자로 변환) '3'을 입력해도 같은결과가 나온다

DESC LPROD;

--MEM_ID가 'A001'인 ROW를 SELECT하시오.

--MEM_ID, MEM_NAME, MEM_MILEAGE 컬럼을 모두 출력

SELECT MEM_ID,

       MEM_NAME,

       MEM_MILEAGE

FROM MEMBER

WHERE MEM_ID = 'a001';

SELECT MEM_ID, MEM_NAME, MEM_MILEAGE FROM MEMBER;

SELECT *

FROM LPROD

WHERE LPROD_GU = 'P102';

--업데이트는 쎄대여(SET WHERE)

UPDATE LPROD

SET LPROD_NM = '향수'

WHERE LPROD_GU = 'P102';

SELECT * FROM LPROD;

ROLLBACK; -- 트랜젝션 마지막 커밋시점으로 되돌아감

--LPROD 를 LPROD2로 복사

CREATE TABLE LPROD2

AS

SELECT * FROM LPROD;

--lprod2 테이블의 lprod_gu 가 p202인 lprod_nm을 남성 케쥬얼에서 도서류로 update 하시오

UPDATE LPROD2

SET    LPROD_NM = '향수'

WHERE  LPROD_GU = 'P202';

SELECT *

FROM  LPROD2

WHERE LPROD_GU = 'P202';

SELECT * FROM LPROD2;

/*

lprod2 테이블 에서 lprod_id 가 7인 lprod_gu 를 p401에서 p303 으로 update 하시오

*/

SELECT *

FROM LPROD2

WHERE LPROD_ID = 7;

UPDATE LPROD2

SET LPROD_GU = 'P303'

WHERE LPROD_ID = 7;

/*

lprod2 테이블에서 lprod_nm 이 화장품인 데이터를

테스트로 수정하고, lprod_id를 10으로 또한 수정하기

*/

SELECT *

FROM LPROD2

WHERE LPROD_NM = '화장품';

UPDATE LPROD2

SET LPROD_NM = '테스트', LPROD_ID = '10'

WHERE LPROD_NM = '화장품';

SELECT *

FROM LPROD2;

--lprod2 테이블에서 lprod_nm이 화장품인 데이터를 삭제하시오

--등푸른생선 주세여

DELETE FROM LPROD2

WHERE LPROD_NM = '전자제품';

--TCL (transaction Contril Langueage)

-- 트랜잭션 : 데이터 베이스를수정하기위해 수행되어야 할 논리적인 단위

--COMMIT;

ROLLBACK;

SELECT *

FROM   TEST

WHERE  COL1 = 'a';

-- CRUD / Creat, select, update, delete (네가지 DML)

--상품 , 테이블로부터 모든 row와 column을 검색하시오

SELECT * FROM PROD;

--회원테이블로부터 모든 r 와 c를 검색

SELECT * FROM MEMBER;

SELECT * FROM CART;

-- BUYER 테이블을 BUYER2 테이블로 복사하시오

CREATE TABLE BUYER2

AS

SELECT * FROM BUYER;

-- BUYER2 TABLE 에서 ID, NAME, LGU, SELECT 하기

SELECT BUYER_ID

     , BUYER_NAME

     , BUYER_LGU

FROM   BUYER2;

UPDATE BUYER2

SET    BUYER_NAME = '거성'

WHERE  BUYER_ID = 'P30203';

SELECT *

FROM   BUYER2

WHERE  BUYER_ID = 'P30203';

-- 4. BUYER2 테이블의 BUYER_NAME이 피리어스인 ROW를 삭제하시오

SELECT *

FROM BUYER2

WHERE BUYER_NAME = '피리어스';

DELETE FROM BUYER2

WHERE BUYER_NAME = '피리어스';

--NAME 만 딱삭제는 UPDATE 공백으로 해야한다

UPDATE BUYER2

SET BUYER_NAME = NULL

WHERE BUYER_NAME = '피리어스';

--상품 테이블 prod로 부터 상품코드와 상품명을 검색하시오

--ID NAME

SELECT PROD_ID   상품코드

     , PROD_NAME 상품명

FROM   PROD;     

--ALIAS 라고한다 한글로 HEADING 되게 하는것

--장바구니 테이블로부터 주문번호, 상품코드 , 회원ID, 수량을 검색하시오

SELECT CART_NO     AS 주문번호     --ALIAS

      ,CART_PROD   "상품코드"       --ALIAS

      ,CART_MEMBER AS "회원ID"        --ALIAS 

      ,CART_QTY    수량               --ALIAS  모두 같은 방법 ALIAS를 표현한것이다

FROM   CART;

--회원 테ㅣ블의 마일리지를 12로 나누값을 검색하시오

SELECT MEM_MILEAGE

     , MEM_MELEAGE/12

     , ROUND(MEM_MELEAGE/12,2)

     , MEM_MELEAGE+12

     , MEM_MELEAGE-12

     , MEM_MELEAGE*12

FROM   MEMBER;

SELECT PROD_ID   AS 상품코드

    ,  PROD_NAME AS 상품명

    ,  PROD_SALE*55 AS 판매금액     -- 판매단가 를 SELECT 하여 55를 곱해서 ALIAS 를 금액으로 지정한다

FROM PROD;

--P.20

SELECT DISTINCT PROD_LGU AS 상품분류 -- DISTINCT 중복제거하여 UNIQUE 하게 검색

FROM   PROD;                        -- 컬럼명 가장앞에 한번만 쓰면 모든 컬럼의 중복값 제거ㅗ된다

--P.20

SELECT PROD_BUYER AS 거래처

FROM PROD;

SELECT DISTINCT PROD_BUYER AS 거래처

FROM PROD;

--ASC : ASCENDING(오름차순)

--DESC : CESCENDING(내림차순)

SELECT MEM_ID      회원ID    --MEMID 가 SELECT되지 않아도 정렬 가능하다

     , MEM_NAME     "아빠가 방에 들어가심"  -- 30BYTE 넘지않는선까지 입력가능하다 VARCHAR 글1개당 3BYTE 해서 10칸까지 가능

     , MEM_BIR      생일

     , MEM_MILEAGE  마일리지

FROM   MEMBER

ORDER BY MEM_ID ASC; --

SELECT MEM_ID      회원ID    --MEMID 가 SELECT되지 않아도 정렬 가능하다

     , MEM_NAME     성명  -- 30BYTE 넘지않는선까지 입력가능하다 VARCHAR 글1개당 3BYTE 해서 10칸까지 가능

     , MEM_BIR      생일

     , MEM_MILEAGE  마일리지

FROM   MEMBER

ORDER BY 성명 DESC; --               --AS 로도 정렬 기준가능

SELECT MEM_ID      

     , MEM_NAME     

     , MEM_BIR      

     , MEM_MILEAGE  

FROM   MEMBER

ORDER BY 3 ASC; -- 세번째에 있는 MEM_BIR 을 기준으로 오름차순

SELECT MEM_ID      

     , MEM_NAME     

     , MEM_BIR      

     , MEM_MILEAGE  

FROM   MEMBER

ORDER BY MEM_MILEAGE, 1; --생략하면 ASC 가 생략되어있는것이다 

                            -- 정렬이 값이 겹쳐 안될경우 겹치는 것끼리 1 번 MEM_ID 로 정렬해라

                            -- MEM_MILEAGE 는 1차 정렬, 뒤의 숫자 1은 2차정렬

                            -- 앞뒤의 ASC DESC를 따로 지정해 다르게 정렬할 수 있다

                            

                            

SELECT MEM_ID    회원ID

     , MEM_JOB   직업

     , MEM_LIKE  취미

FROM   MEMBER

ORDER BY 2 ASC,3 DESC,1 ASC;

UPDATE MEMBER

SET MEM_MEMORIAL = NULL

WHERE MEM_JOB = '회사원';

SELECT *

FROM MEMBER

WHERE MEM_JOB = '회사원';

--------

select *

from prod;

select *

from prod

order by prod_sale desc, prod_lgu, prod_name asc;

--21.p

--상품중 판매가가 170,000원인 상품조회

select prod_name 상품명

        , prod_sale 판매가

from prod

where prod_sale = 170000;

--17만원 초과 또는 미만인 상품 조회

select prod_name 상품명

        , prod_sale 판매가

from prod

where prod_sale < 170000;

select prod_name 

        , prod_sale 판매가상품명

from prod

where prod_sale > 170000;

--17만원 이상 또는 이하인 상품 조회

select prod_name 상품명

        , prod_sale 판매가

from prod

where prod_sale <= 170000;

select prod_name 상품명

        , prod_sale 판매가

from prod

where prod_sale <> 170000; --아닌 경우

select  prod_id "상품 코드" --띄어써야하는 경우... 

        , prod_name "상품 명" --하지만 변수명과 연계되기 때문에 영어, 띄어쓰기 없이 한다 

        , prod_cost "매입 가"

from prod

where prod_cost <=200000;

select mem_id 회원아이디

        , mem_name 회원이름

        , mem_regno1 주민번호앞자리

from member

--where mem_regno1 >= 760101;    -- 문자:숫자  ---자동형변환-> 숫자:숫자

where mem_regno1 >= '760101';    -- 문자:문자   

                                 -- 문자보다 날짜가 우선, 

                                 -- 날짜:문자(/-.) ---자동형변환-> 날짜:날짜 

desc member; -- 자료형 찾기 

select prod_name 상품

        , prod_lgu 상품분류

        , prod_sale 판매가 

from prod;

select prod_name 상품

        , prod_lgu 상품분류

        , prod_sale 판매가 

from prod

where prod_lgu='P201' 

and prod_sale=17000;

select prod_name 상품

        , prod_lgu 상품분류

        , prod_sale 판매가 

from prod

where prod_lgu='P201' 

or prod_sale=17000;

select prod_name 상품

        , prod_lgu 상품분류

        , prod_sale 판매가 

from prod

where 

not prod_sale=17000;

select prod_name

        , prod_lgu

        , prod_sale

from prod

where prod_lgu<>'P201' and prod_sale<>170000;

select prod_name

        , prod_lgu

        , prod_sale

from prod

where prod_lgu !='201'

and prod_sale !=170000;

--알간모르간 드모르간 적용

select prod_name

        , prod_lgu

        , prod_sale

        

from prod

where not(prod_lgu ='P201'

or prod_sale =170000);--알간모르간 드모르간 적용

select prod_id ,상품코드

        ,prod_name 상품명

        ,prod_sale 가격

from prod

where prod_sale betenn >=300000

and prod_sale <= 50000;

SELECT  *

FROM member

where mem_job='공무원' and mem_mileage>=1500;

select prod_name 상품명

        ,prod_id 상품코드

        ,prod_sale 상품가격

from prod

--where prod_sale=150000 or prod_sale=170000 or prod_sale=330000;

where prod_sale in(15000,17000,330000) and prod_cost > 100000

order by prod_sale asc, prod_sale asc, prod_sale asc;

select mem_id

        ,mem_name

from member

where mem_id in('c001','f001','w001');

--select * from lprod;

--select * from prod;

--select distinct prod_lgu;  --존재한다?

select lprod_id

        ,lprod_nm

from lprod

where lprod_gu in( --main SQL

    select distinct prod_lgu --main SQL

    from prod); 

    

select lprod_id

        ,lprod_nm

from lprod

where lprod_gu not in

(select distinct prod_lgu from prod);

    

        

select buyer_id

        ,buyer_name

from buyer

where buyer_id in

(select distinct prod_buyer from prod);

select *

from prod

where prod_price between 100000 and 300000; --이상,이하의 범위로 

select mem_id

        , mem_name

        , mem_bir

from member

where mem_bir between '19750101' and '19761231';

select prod_name

        , prod_cost

        , prod_sale

from prod

--where (prod_cost between 300000 and 15000000) --아래와 동일

--and (prod_sale between 800000 and 20000000);

where prod_cost between 300000 and 15000000

--where prod_cost between to_member('300,000','15,000,000')--별도로 선언할 수도 있다

and prod_sale between 800000 and 20000000;

select mem_id

        , mem_name

        , mem_bir

        , to_char(mem_bir,'yyyy-mm-dd')  --출력형태 조정 

from member

--where not mem_bir between '1975/01/01' and '1975/12/31';  --아래와 동일, not 위치

--where mem_bir not between '1975/01/01' and '1975/12/31';  --아래와 동일

--where mem_bir not between '1975-01-01' and '1975-12-31';  --아래와 동일

where mem_bir not between '19750101' and '19751231';

 

--like p.23

select prod_id as 상품코드

        , prod_name as 상품명

from prod

where prod_name like '삼%';

select prod_id as 상품코드

        , prod_name as 상품명

from prod

where prod_name like '_성%';

select prod_id as 상품코드

        , prod_name as 상품명

from prod

where prod_name like '_성%';

    

select prod_id 상품코드

        ,prod_name 상품명

from prod

where prod_name like '%치';

select prod_id 상품코드

        ,prod_name 상품명

from prod

where prod_name like '%여름%';

select mem_id 회원id

    , mem_name 회원이름

from member

where mem_name like '김은_';

select prod_id 상품코드

        ,prod_name 상품명

        ,prod_sale 판매가

        ,prod_detail 제품설명글

from prod

where prod_name like '%삼성%'

and prod_sale <1000000

order by prod_name desc;

    

insert into lprod(lprod_id, lprod_gu, lprod_nm)

values(10,'P502','홍길동% 달성');

insert into lprod(lprod_id, lprod_gu, lprod_nm)

values(11,'P503','홍길성% 달성');

commit;

select * from lprod;

select lprod_id, 

        lprod_gu, 

        lprod_nm

from lprod

where lprod_nm like '홍길동%';

select lprod_id, 

        lprod_gu, 

        lprod_nm

from lprod

where lprod_nm like '홍길동\%' escape '\';

insert into lprod(lprod_id, lprod_gu, lprod_nm)

values(12,'P601','유다영_');

insert into lprod(lprod_id, lprod_gu, lprod_nm)

values(13,'P602','유다영짱');

insert into lprod(lprod_id, lprod_gu, lprod_nm)

values(14,'P603','유다영굿');

commit;

select * from lprod;

select lprod_id, 

        lprod_gu, 

        lprod_nm

from lprod

where lprod_nm like '%유다영%';

select lprod_id, 

        lprod_gu, 

        lprod_nm

from lprod

where lprod_nm like '유다영%';

select lprod_id, 

        lprod_gu, 

        lprod_nm

from lprod

where lprod_nm like '유다영\%' escape '\';

select lprod_id, 

        lprod_gu, 

        lprod_nm

from lprod

where lprod_nm like '유다영@_' escape '@'; --다른 특수문자도 가능

select lprod_id, 

        lprod_gu, 

        lprod_nm

from lprod

where lprod_nm = '유다영_';

-------***함수

select mem_id||'name is'||mem_name 

from member;

select concat('name is',mem_name) contcat 

from member;

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;

select ascii(mem_id) as 회원ascii

    ,chr(ascii(mem_id)) as 회원chr

from member;

select lower(mem_id) 소문자

        , upper(mem_id) 대문자

        , initcap(mem_mail) 첫자만대문자

from member;

select lpad ('java',10,'*') lpad

        ,rpad('flex',12,'^') rpad

from dual;

select prod_price prod_price

        , lpad(prod_price,10,'*') prod_result

        , rpad(prod_price,4,'*') r_study --여백이 없으니까 l이든 r이든 상관없다

        , lpad(prod_price,4,'*') l_study --여백이 없으니까 l이든 r이든 상관없다from prod

from prod

where prod_price between '300000' and'3000000'

order by prod_result desc;

        

select mem_id

        , '*****'||mem_id

        , lpad(mem_id,9,'*')

from member;

select ltrim(prod_name) ltrim

    , rtrim(prod_name) rtrim

    , trim(prod_name) trim --옵션이 있다  reading(왼쪽), both(==생략), trailing

from prod;

select ltrim('     AAA       ') ltrim

    , rtrim('     AAA       ') rtrim

    , trim('     AAA       ') trim --옵션이 있다  reading(왼쪽), both(==생략), trailing

from dual;

select '<' || ltrim('     AAA       ') ||  '>' "ltrim",

 '<' || rtrim('     AAA       ') ||  '>' "rtrim",

 '<' || trim('     AAA       ') ||  '>' "trim" --옵션이 있다  reading(왼쪽), both(==생략), trailing

from dual;

select '<' || ltrim('     AAA       ') ||  '>' "ltrim"

    ,  '<' || ltrim('Hello World','He') ||  '>' ltrim --""는 알리아스에서만 쓴다. 없어도 되고 없는 걸 권장

    ,  '<' || ltrim('llo He World','He') ||  '>' ltrim

    ,  '<' || ltrim('Hello He World','He') ||  '>' ltrim

    ,  '<' || ltrim('HeHello He World','He') ||  '>' ltrim -- 단어 끝나기 전까지 같은 문장은 계속 지운다

from dual;

select '<' || rtrim('     AAA       ') ||  '>' rtrim

    ,  '<' || rtrim('Hello World','He') ||  '>' rtrim

    ,  '<' || rtrim('Hello WorldHe','He') ||  '>' rtrim

from dual;

select '<' || trim('     AAA       ') ||  '>' trim

    ,  '<' || trim(leading 'a' from trim('       aaAaBaAaa')) ||  '>' leading --""는 알리아스에서만 쓴다. 없어도 되고 없는 걸 권장

    ,  '<' || trim(trailing'a' from ('aaAaBaAaa')) ||  '>' trailing -- 단어 끝나기 전까지 같은 문장은 계속 지운다    ,  '<' || trim('a' from ('aaAaBaAaa')) ||  '>' 생략

    ,  '<' || trim(both 'a' from ('aaAaBaAaa')) ||  '>' both

from dual;--가상테이블

select substr ('SQL PROJECT',1,3) 앞에서부터

       , substr ('SQL PROJECT',5) no인자

       , substr ('SQL PROJECT',-7,3) 뒤에서부터

from dual;--가상테이블

select mem_id 아이디

    ,mem_name 전체이름

    ,substr (mem_name,1,1) 성씨

    ,substr (mem_name,2,2) 이름

from member

order by 성씨 asc;

select prod_id 상품코드

    , prod_name 상품명

from prod

where substr (prod_name,4,2)='칼라';

//

select translate('2009-02-21','0123456789-','abcdefghijk')

from dual;

SELECT MEM_ID   회원ID

     , TRANSLATE(MEM_ID, '0123456789', ' ')  변환아이디

FROM   MEMBER; 

SELECT BUYER_NAME   거래처명

     , REPLACE(BUYER_NAME, '컴퓨터', '육')    치환

FROM   BUYER;

SELECT PROD_ID      상품코드

     , PROD_NAME    상품명

FROM   PROD

WHERE  SUBSTR(PROD_NAME, 4, 2) = '칼라';  

SELECT MEM_NAME    회원명

     , REPLACE(MEM_NAME, '이', '리')  회원명치환

FROM   MEMBER;

--

select mem_name 회원명 

        , replace(mem_name, '이','리') 회원명치환

from member;

select instr('I have a hat.', 'ha')

from dual;

select instr('I have a hat.', 'ha') --첫번째

        , instr('I have a hat.', 'ha', 1) --첫번째

        , instr('I have a hat.', 'ha', 1,1) --첫번째

        , instr('I have a hat.', 'ha', 4) --두번째

        , instr('I have a hat.', 'ha', 1,2) --두번째

        , instr('I have a hat.', 'ha', -1) --뒤에서 찾는 첫번째

        , instr('I have a hat.', 'ha', -1,2) --뒤에서 찾는 두번째

from dual;

select instr('i have a hat that i had have been found that hat before 2 years ago.', 'ha', 1, 5)

from dual;

select instr('mepch@test.com','@',1)--결과6

from dual;

select substr('mepch@test.com',1,5) 아이디

       , substr('mepch@test.com',7) 도메인

from dual;

select substr('mepch@test.com',1,instr('mepch@test.com','@',1)-1) 아이디

       , substr('mepch@test.com',instr('mepch@test.com','@',1)+1) 도메인

from dual;

select mem_id 회원ID

        , mem_name 회원이름

        , substr(mem_mail,1,instr(mem_mail,'@',1)-1) 메일

        , substr(mem_mail,instr(mem_mail,'@',1)+1) 도메인

from member;

select buyer_id 거래처코드

        , length(buyer_id) 거래처코드길이

        , buyer_name 거래처명

        , length(buyer_name) 거래처명길이

        , lengthb(buyer_name) 거래처명바이트수

from buyer;

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), sqrt(3) from dual;

select greatest(10,20,30) 큰값

    , least(10,20,30) 작은값

from dual;

select ascii('ㄱ') from dual;

select chr(14910641) from dual;

select greatest('강아지',256,'송아지') 큰값

    , least('강아지',256,'송아지') 작은값

from dual;

select greatest('a','23456787145678796543678976543456789876543')

from dual;

select mem_name

    ,  mem_mileage

    ,  greatest(mem_mileage,1000)

from member;

select round(345.123, -2) 결과 from dual;

select round(345.123, -1) 결과 from dual;

select round(345.123, 0) 결과 from dual;

select round(345.123, 1) 결과 from dual;

select round(345.123, 2) 결과 from dual;

select round(345.123, 3) 결과 from dual;

select trunc(345.123, -2) 결과 from dual;

select trunc(345.123, -1) 결과 from dual;

select trunc(345.123, 0) 결과 from dual;

select trunc(345.123, 1) 결과 from dual;

select trunc(345.123, 2) 결과 from dual;

select trunc(345.123, 3) 결과 from dual;

select mem_name 이름

    ,  round(mem_mileage/12,3) 반올림

    ,  trunc(mem_mileage/12,3) 절삭

from member;

select prod_name 상품명

    , round((prod_cost/prod_price)*100,1) 원가율1

    , trunc((prod_cost/prod_price)*100,1) 원가율2

from prod;

----------0618

----------

select 1619/13 from dual;

select round(1619/13,2) from dual;

select round(1619/13,0) from dual;

select round(1619/13,-2) from dual;

select mod(10,3) from dual; --나머지 및 짝수/홀수 구분

select floor(1332.69), ceil(1332.69) from dual;

select floor(-1332.69), ceil(1332.69) from dual;

select floor(2.69), ceil(2.69) from dual;

select floor(-2.69), ceil(-2.69) from dual;

select sysdate from dual;

select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') 현재시간

    , sysdate -1 어제이시간

    , sysdate +1 내일이시간

    , to_char(sysdate +1/24,'yyyy-mm-dd hh:mi:ss') 한시간후

from dual;

select mem_name 이름

    , mem_bir 생일

    , mem_bir+12000 "12000일"

from member;

select to_date('1982-05-28') +12000 "12000일"

from member;

select to_date('1982-05-28') 생일

    , round(sysdate - to_date('1982-05-28'),0) 생존일

    , (sysdate - to_date('1982-05-28')) *3*3000 "생존식대(원)"

from dual;

select add_months(sysdate,6) from dual;

select next_day(sysdate, '월요일')

    , last_day(sysdate)

from dual;

select sysdate 오늘날짜

    , last_day(sysdate) 마지막날짜

    , last_day(sysdate)- sysdate 이번달남은날짜

from dual;

select round(sysdate,'mm') --15일 기준

    , trunc(sysdate,'mm')

from dual;

select round(sysdate,'year') --절반 기준

    , trunc(sysdate,'year')

from dual;

select months_between(sysdate,'2018-01-01')

from dual;

select trunc(months_between(sysdate,'2018-01-01')) --기념일

from dual;

select extract(year from sysdate) 연도

    , extract(month from sysdate) 월

    , extract(day from sysdate) 일

    , extract(hour from systimestamp) +9 시

    , extract(minute from systimestamp) 분

    , extract(second from systimestamp) 초

from dual;

select mem_id 아이디

    , mem_name 이름

    , mem_bir 생일

    , extract(month from mem_bir) "3월생일"

from member

where extract(month from mem_bir)= 3;

select mem_id 아이디

    , mem_name 이름

    , mem_bir 생일

from member

where extract(month from mem_bir)= 3;

select mem_id 아이디

    , mem_name 이름

    , mem_bir 생일

from member

where extract(month from mem_bir)= 3;

--and substr(mem_bir,4,2)='03'

--and mem_bir like '%/03/%'

--and mem_bir like '_/03/_'

select buy_prod 상품코드

    , buy_date 입고일자

    , buy_qty 매입수량

    , buy_cost 매입단가

from buyprod

where extract(month from buy_date)=3 --숫자와 숫자간 비교

and substr(buy_date,4,2)='03' --결과는 문자, 문자와 문자간 비교  

and buy_date like '%_/03/_%'

and buy_date like '%/03/%';

---- cast --as를 써서 '명시'적 형변환

to_char() -- 날짜, 숫자, 문자를 -> 문자로 형변환

to_number -- '숫자형식의 문자'가 -> 우리가 숫자로 형변환

to_date -- '날짜형 문자가2018/02/02' -> 날짜로 형변환

select 'hello' 형변환

    , cast('hello' as char(30)) -- 고정길이 문자형

    , cast('hello' as varchar2(30)) -- 가변길이 문자형

from dual;

select to_date('2018/01/01') +10

from dual;

select cast('2018/01/01' as date) +10

from dual;

select cast('2018/01/01' as date)

from dual;

select to_date('2018/01/01') +1 형변환1

    , cast('2018/01/01' as date) +1 형변환2

from dual;

select sysdate

    , to_char(sysdate, 'ad yyyy, cc"세기")')

from dual;

select prod_name 상품명

    , prod_price 상품판매가

    , to_char(prod_insdate,'yyyy-mm-dd hh:mi:ss') 입고일

from prod;

desc prod; -- prod_insdate에 대한 타입 확인,

          -- 날짜형식이라, to_date가 아닌 tochar로

----------------180619=========================

select mem_name ||'님은'

    || to_char(mem_bir,'YYYY "년," mm "출생이고 태어난 요일은" day')

from member;

   

select prod_id , prod_name

    , lprod_gu, lprod_id

from prod, lprod;

select buy_prod || '상품은' || to_char(buy_date, ' yyyy "년" mm "월" dd "일에" ')

    || buy_qty || '개가 입고됨'

from buyprod;

select prod_id 

    , prod_name

from prod;

select '대분류 코드는' 

    || substr(prod_id,1,4) 

    || '이고 순번은' 

    || substr(prod_id,5) ||'이다.'

from prod;

select to_char(1234.6, 'L9,999.00')

from dual;

select to_char(1234.6, '$9,999.00')

from dual;

select to_char(-1234.6, 'L9,999.00')

    , to_char(-1234.6, 'L9,999.00PR')-- 걱쇄로 표현

    , to_char(-1234.6, 'L9,999.00MI') --뒤에 마이너스

from dual;

select prod_id

    , prod_name

    , to_char(prod_sale, 'L9,999,999.00') --막쓰면 안된다 개수를 헤아려 9를 찍어라

from prod;

select prod_id

    , prod_name

    , prod_sale

    , to_char(prod_sale, 'p9,999,999') --막쓰면 안된다 개수를 헤아려 9를 찍어라

from prod;

select prod_id

    , prod_name

    , to_char(prod_price,'L9,999,999')

    , to_char(prod_cost,'L9,999,999')

    , to_char(prod_sale,'L9,999,999')

from prod;

--select to_char(buy_cost,'L9,999,999.000')/ 

--from buyprod;

select to_char(round(avg(buy_cost),2),'L9,999,999.000')

from buyprod;

--숫자 오른쪽 정렬

select 3.1415 from dual; --숫자

select '3.1415' from dual; --문자

select to_number('3.1415') from dual; --숫자

select to_number('￦1,200') +1 from dua1; --에러임 ￦는 숫자로 못바꿈

select to_number('개똥이') +1 from dua1; --에러임 문자는 숫자로 못바꿈

select to_number('￦1,200','L999,999')+1   --문자를 숫자로

    , to_char(to_number('￦1,200','L999,999')+1,'L999,999') --문자를 숫자로

from dual;

--to_number :알려주는 것

select to_number('369','99') from dual; --에러

select to_number('369','999') from dual; 

            --'369'가 뒤에 '999'의 형식이라는 것을 알려주는 역할!

            --생략가능 

            

--to_char : 바꾸는 것

select to_char('369','999') from dual; 

            --'369'을 뒤에 '999'의 형식으로 보내는 역할!

select to_char('369','000000') from dual; 

            --'369'을 뒤에 '999'의 형식으로 보내는 역할!

select substr(mem_id,1,2) || to_number(substr(mem_id,2,4)+'10')  

                            --문자 +10 = 숫자가 우선, 자동 ! 형변환(mem_id,2,4) =1 이후 +10

from member

where mem_name='이쁜이';

select substr(mem_id,1,2) || substr(mem_id,2,4) + '10' --에러  '||'가 +를 우선한다

from member

where mem_name='이쁜이';

select substr(mem_id,1,2) || (substr(mem_id,2,4) + '10')--해결 '()가 ||를 우선한다

from member

where mem_name='이쁜이';

select substr(mem_id,1,2) || to_number(substr(mem_id,2,4)+10) 

from member

where mem_name='이쁜이';

select substr(mem_id,1,2) || to_char(substr(mem_id,2,4)+10)

from member

where mem_name='이쁜이';

select '2016-01-24' +3 --에러임 문자는 숫자로 못바꿈

from dual;

select '개똥이' +3 --에러임 문자는 숫자로 못바꿈

from dual;

select to_date('2016-01-24') + 3 --에러임 문자는 숫자로 못바꿈

from dual;

select to_date('20160124','yyyy-mm-dd') +3

from dual;

select to_date('2016/01/24','yyyy-mm-dd') +3

from dual;

select to_char('2016/01/24','yyyy-mm-dd hh:mi:ss') --에러

from dual;

select to_char(to_date('2016/01/24'),'yyyy-mm-dd hh:mi:ss')

from dual;

select '200812031234'+3

from dual;

select to_char(to_date('200812031234','yyyy-mm-dd hh:mi:ss')+3,'yyyy-mm-dd hh:mi:ss')

from dual;

--=====================================

select mem_name 이름

    , mem_regno1 주민번호$_#

    , to_date(mem_regno1,'yymmdd') 치환

from member;

select cart_no 넘버

    ,   cart_prod 제품

    ,   to_date(substr(cart_no,1,8)) 숫자화1

    ,   to_date(substr(cart_no,1,8),'yyyymmdd')숫자화2

    ,   to_char(to_date(substr(cart_no,1,8),'yyyymmdd'),'yyyy"년" mm"월" dd"일"')문자열

from cart;

select buyer_name 거래처

    , buyer_charger 담당자

from buyer

where buyer_charger like '김%';

update buyer 

set buyer_charger = null

where buyer_charger like '김%';

select buyer_charger 담당자

from buyer;

select buyer_name 거래처

    , buyer_charger 담당자

from buyer

where buyer_charger like '성%';

update buyer

set buyer_charger = null

where buyer_charger like '성%'; -- '성%'는 와일드카드

select buyer_name 거래처

    , buyer_charger 담당자

from buyer;

commit ;

select buyer_name

    , buyer_charger

from buyer

where buyer_charger =null;

select buyer_name

    , buyer_charger

from buyer

where buyer_charger is null;

select buyer_name

    , buyer_charger

from buyer

where buyer_charger is not null;

select buyer_name

    , buyer_charger

from buyer

where not(buyer_charger is null);

select buyer_name

    , buyer_charger

    , nvl(buyer_charger, '없다')

from buyer

where buyer_charger is null;

select nvl(null,0)+10

    , 10*nvl(null,1)

from buyer;

select buyer_name 거래처

    , nvl(buyer_charger, '없다') 담당자

from buyer;

select buyer_name 거래처

    , nvl2(buyer_charger, buyer_charger ,'없다') 담당자

from buyer;

select buyer_name 거래처

    , nvl2(buyer_charger, '있지롱', '없다') 담당자

from buyer;

//

select substr(mem_name,1,1)

from member

where mem_name ='ㄷ';

select *

from member

where mem_name between '바' and '빟';

update member

set mem_mileage=null

where mem_name between '바' and '빟';

select *

from member

where mem_name between '바' and '빟';

commit;

select mem_name

    , mem_mileage

    , nvl(mem_mileage,0)+100

    , nvl2(mem_mileage,mem_mileage+100,100)

from member;

select mem_name

    , mem_mileage

    , nvl2(mem_mileage,'정상', '비정상')

from member;    

select nullif(123,123) "같을경우 null반환"

    , nullif(123,1234) "다를경우 앞의수반환"

    , nullif('A','B') "다를경우 앞의수반환"

from dual;

select decode (9

        , 10,'A'

        , 8,'B'

        , 9,'C'

        ,'D')

from dual;  

       

select  prod_name

    , prod_price

    , decode(substr(lprod_gu,1,2)

            , 'p1', prod_price*1.1

            , 'p2', prod_price*1.5

            , prod_price)

from prod, lprod;

select * from lprod;

select  prod_name

    , prod_price

    , decode(substr(prod_lgu,1,2)

            , 'p1', prod_price*1.1

            , 'p2', prod_price*1.5

            , prod_price)

from prod;

select mem_id  --에러1

    , mem_name

    , mem_mileage

    , (substr(mem_bir,4,2)

    , decode(substr(mem_bir,4,2)

            , '03',mem_mileage*1.1

            , (to_number(substr(mem_bir,4,2))%2==0), mem_mileage*0.5

            , mem_mileage)

from member;

select mem_bir from member;

select mem_id  --에러2 test

    , mem_name

    , mem_mileage

    , to_number(substr(mem_bir,4,2)) 숫자로

    , decode(substr(mem_bir,4,2)

            , '03',mem_mileage*1.1

            , mem_mileage) 월_3

    , decode(mod(to_number(substr(mem_bir,4,2)),2)

            , 0,mem_mileage*1.05

            , mem_mileage) 짝수

from member;

select mod(10,2) 나머지구하기 from dual; 

select mem_id   --반쪽짜리

    , mem_name

    , mem_mileage

    , decode(substr(mem_bir,4,2)

            , '03',mem_mileage*1.1

            , mem_mileage)

from member;

select mem_id 

    , mem_name

    , mem_mileage

    , to_number(substr(mem_bir,4,2)) 월_숫자

    , decode(substr(mem_bir,4,2)

            , '03',mem_mileage*1.1

            , decode(mod(to_number(substr(mem_bir,4,2)),2)

                    , 0,mem_mileage*1.05

                    , mem_mileage)) 월_3

from member;

select mem_id

    , mem_name

    , mem_add1 

    , decode(substr(mem_add1,1,2)

            ,'충남', '충청남도'

            ,'대전', '대전광역시'

            ,'대구', '대구광역시'

            ,'그 외')

from member;   

select mem_id  --응용

    , mem_name

    , mem_add1 

    , decode(substr(mem_add1,1,2)

            ,'충남', '충청남도'

            ,substr(mem_add1,1,2), substr(mem_add1,1,2)||'광역시')

from member;   

select --simple case expression

    case '나'   WHEN '철호' THEN '아니다'

                WHEN '나' THEN '아니다'

                WHEN '나' THEN '맞다'

                ELSE '모르겠다'  

    END result

from dual;

select --searched case expression

    case    WHEN '철호' = '나' THEN '아니다'

            WHEN '나' = '나'  THEN '아니다'

            WHEN '나' = '나'  THEN '맞다'

            ELSE '모르겠다'  

    END result

from dual;

---------------

--------6월 28일

select prod_name 상품

    , prod_lgu 분류

    , case

            when prod_lgu = 'P101' then '컴퓨터제품'

            when prod_lgu = 'P102' then '전자제품'

            when prod_lgu = 'P201' then '여성캐쥬얼'

            when prod_lgu = 'P202' then '남성캐쥬얼'

            when prod_lgu = 'P301' then '피역잡화'

            when prod_lgu = 'P302' then '화장품'

            when prod_lgu = 'P401' then '음반'

            when prod_lgu = 'P402' then '도서'

            when prod_lgu = 'P403' then '문구류'

        else '미등록분류'

     end 상품분류

from prod;

select mem_id 아이디

    , mem_name 이름

    , mem_regno2 주민번호

    , case

            when substr(mem_regno2,1,1) = '1' then '남성'  --1도 가능 = 자동형변환 

            when substr(mem_regno2,1,1) = '2' then '여성'

            when substr(mem_regno2,1,1) = '3' then '남아'

            when substr(mem_regno2,1,1) = '4' then '여아'

        else '알수없음'

    end 성별

from member;

select prod_name 상품명

    , prod_price 판매가

    , case

            when (100000-prod_price) >= 0 then '10만원 미만'             

            when (200000-prod_price) >= 0 then '10만원대'  

            when (300000-prod_price) >= 0 then '20만원대'

            when (400000-prod_price) >= 0 then '30만원대'

            when (500000-prod_price) >= 0 then '40만원대'

            when (600000-prod_price) >= 0 then '50만원대'

            when (700000-prod_price) >= 0 then '60만원대'

            when (800000-prod_price) >= 0 then '70만원대'

            when (900000-prod_price) >= 0 then '80만원대'

            when (1000000-prod_price) >= 0 then '90만원대'

        else '100만원 이상'

    end 가격대

from prod

where prod_price>100000;

select mem_id 아이디

    , mem_name 이름

    , mem_like 취미

    , case

        when mem_like = '수영' then '미나리먹고 미쳤냐'

        when mem_like = '독서' then '도라지먹고 돌았냐'

        when mem_like = '당구' then '생강먹고 생각해봐'

        when mem_like = '스키' then '와이 와이'

        else '와이파이'

    end 대답 

from member;

------ tcl 트랜젝션 ------

create table test1(

    deptno number(2)

    , dname varchar2(30)

    , loc  varchar2(30)

);

insert into test1(deptno, dname, loc) values(10,'account','new york');

insert into test1(deptno, dname, loc) values(20,'research','dallas');

insert into test1(deptno, dname, loc) values(30,'sales','chicago');

insert into test1(deptno, dname, loc) values(40,'operation','boston');

select * from test1;

-- 트랜젝션 사례 1)

commit; -- 새로운 트랜젝션 시작

--deptno를 모두 20으로 

update test1 set deptno = 20;

select * from test1;

--sales 를 chicago 에서 seoul로

update test1 set loc = 'seoul'

where dname = 'sales';

select * from test1;

--operation 삭제

delete from test1

where dname = 'operation';

select * from test1;

rollback; --마지막 '커밋' 시점으로 돌아간 후  -- 새로운 트랜젝션 시작

-- 트랜젝션 사례 2)

update test1 set deptno = 20;--deptno를 모두 20으로 

commit; -- 새로운 트랜젝션 시작

update test1 set loc = 'seoul'--sales 를 chicago 에서 seoul로

where dname = 'sales';

delete from test1--operation 삭제

where dname = 'operation';

rollback; --최종 '커밋' 시점으로 돌아간 후  -- 새로운 트랜젝션 시작. 아무리 롤백해도 더 앞으로 못감

select * from test1;

-- 트랜젝션 사례 3)

update test1 set deptno = 20;--deptno를 모두 20으로 

update test1 set loc = 'seoul'--sales 를 chicago 에서 seoul로

where dname = 'sales';

delete from test1--operation 삭제

where dname = 'operation';

select * from test1;

create table test2 --추가 테이블 생성 //오류 : 개똥이 테이블 없음. 

as result * from 개똥이;

rollback;  -- 롤백은 개똥이 테이블은 오류로 생성은 안되었지만 

            -- 결과와 상관없이 트랜젝션은 이미 create table test2 시점에서 실행되었기 때문에 

            -- 롤백은 그 이전으로 돌아갈 수 없음

                    -- = ddl 이 실행되면 자동으로 commit됨

                    --따라서 오라클을 쓸 경우, 롤백하고나서 커밋하는 버릇이 되어야함!

                    -- 단 mssql의 경우 롤백이 가능하긴하다...

                    

                    

                    

                    

                

select * from test1;

--savepoint 중간저장 예시 

update test1 set deptno = 20;--deptno를 모두 20으로 

savepoint A1; --*

update test1 set loc = 'seoul'--sales 를 chicago 에서 seoul로

where dname = 'sales';

savepoint A2; --*

delete from test1--operation 삭제

where dname = 'operation';

savepoint A3; --*

rollback to A2;

select * from test1;

--에러 

rollback to A3; --방금 롤백한 A2 이후의 생성해둔 세이브 포인트(A3)로는 롤백 불가능 

--그 이전으로는 이동 가능

rollback to A1;  --방금 롤백한 A2 이전에 생성해둔 세이브 포인트(A1)로는 롤백 가능

select * from test1;

--데이터 사전

desc test1;

desc all_objects;

select *

from all_objects

where owner = 'PC03'; --대문자로

select distinct object_type

from all_objects 

where owner = 'PC03';

--데이터 사전 활용 예1

select *

from dictionary --데이터 명칭에 대한 사전적 '의미' 찾기 

where table_name = 'ALL_USERS'; --대문자로 -> 결과 : 시스템 뷰 테이블

select * from ALL_USERS;

--데이터 사전 활용 예2

select *

from dictionary --데이터 명칭에 대한 사전적 '의미' 찾기 

where comments like '%users%';  -> 결과 : 시스템 뷰 테이블

select * from USER_INDEXES;

select table_name

    , comments

from dictionary

where table_name like 'ALL_%'; --대문자로

select * from all_objects;

--현재 로그인한 사용자가 만든 모든 객체 정보를 출력

select object_name

    , object_type

    , created

from all_objects

where owner='PC03'

order by object_type asc;

--각테이블 전체 레코드 수 출력

    --1먼저 사전을 통해 테이블명이 의미하는 바를 확인

select *

from dictionary

where table_name='USER_TABLES'; --대문자

    --2각테이블 전체 레코드 수 출력

select table_name 테이블명 

    , nvl(num_rows,0) 레코드수

from user_tables

order by 2 desc; -- "2 = nvl(num_rows,0)"

--USER_CONSTRAINTS,

--USER_CONS_COLUMNS의 컬럼 상세를 확인하고

--상품 테이블의 제약조건을 출력하시요

--(컬럼명, 제약명, 타입, 제약내용)

select B.column_name vnf 

    , A.constraint_name 제약명

    , A.constraint_type 타입

    , A.search_condition 제약내용

from user_constraints a, user_cons_columns b

where A.table_name = B.table_name

and A.table_name='PROD'; --대문자

select 개똥이.lprod_gu  --개똥이= LPROD의 별칭, join에서 구체적으로 학습

    , 개똥이.lprod_nm

from USER_TABLES 개똥이;

-- INDEX 인덱스  

-- = 목차(대표적인 제목글에 페이지지 수 사용)) 

-- 작을 수록 좋다 

--회원 생일이 조건절에 자주 사용되어 B-TREE INDEX를 생성

CREATE INDEX IDX_MEMBER_BIR

ON MEMBER(MEM_BIR);

--INDEX생성 확인

SELECT * FROM USER_INDEXES;

SELECT ROWID    -- 추출이 빨라진다네

     , MEM_ID

     , MEM_NAME

     , MEM_JOB

     , MEM_BIR

FROM   MEMBER

WHERE  MEM_BIR LIKE '75%';

--회원 생일에서 년도만 분리하여 인덱스를 생성 FUNCTION-BASED INDEX

CREATE INDEX IDX_MEMBER_BIR_YEAR

ON     MEMBER(TO_CHAR(MEM_BIR, 'YYYY'));

SELECT MEM_ID

     , MEM_NAME

     , MEM_JOB

     , MEM_BIR

FROM   MEMBER

WHERE  TO_CHAR(MEM_BIR, 'YYYY') = '1975';

--인덱스 생성

CREATE INDEX IDX_MEMBER_BIR_YEAR

ON     MEMBER(MEM_BIR);

select * from member;

--IDX_MEMBER_BIR_YEAR 인덱스는 REBUILD 하시오?

ALTER INDEX IDX_MEMBER_BIR_YEAR REBUILD;

--idx_member_bir_year를 rebuild하시오

ALTER INDEX IDX_MEMBER_BIR_YEAR REBUILD;

--사용자가 생성한 인덱스 확인

select * from user_indexes;

--idx_member_bir_year는 삭제

drop index idx_member_bir_year;

--INDEX KEY COLUMN에 변형을 막는 QUERY문 사용 권장

SELECT BUY_DATE

     , BUY_PROD

     , BUY_QTY

FROM   BUYPROD

WHERE  BUY_DATE - 10 = TO_DATE('2005-01-29'); --비추방법

       -- BUY_DATE는 변경될 수 있다.     

select buy_date

    , buy_prod

    , buy_qty

from buyprod

where buy_date=to_date('2005-02-20') +10; --추천방법

SELECT CART_NO

     , CART_PROD

     , CART_QTY

FROM   CART

WHERE  SUBSTR(CART_NO,1,8) = '20050405'; --비추방법

select cart_no, cart_prod, cart_qty

from cart

where cart_no like '20050405%';--추천방법

select cart_no, cart_prod, cart_qty

from cart

where cart_no > '2005040500000'

and cart_no < '2005040599999'--추천방법