[08-D6][Oracle] DML, DQL, 함수, LIKE, TRANSACTION

▩ SQL-DML(INSERT, DELETE, UPDATE, DML: Data Manipulation) 문 사용하기
   - sql 파일의 생성 및 저장


--만들어진 테이블 목록을 봅니다.
SELECT * FROM tab;


--테이블을 삭제합니다.
DROP TABLE visitor;


--실습용 테이블을 생성합니다.
CREATE TABLE visitor (
  num      NUMBER(5)    NOT NULL,
  title       VARCHAR(20)         NULL,
  name    VARCHAR(20)         NULL,
  email    VARCHAR(30)         NULL,
  visited  NUMBER(5)            NULL
);


CREATE SEQUENCE visitor_num_seq
    START WITH   1             --시작번호
    INCREMENT BY 1          --증가값
    MAXVALUE     99999      --최대값
    CACHE 20                 --시쿼스 변경시 자주 update되는 것을 방지하기위한 캐시값
    NOCYCLE;   


1. INSERT
   - 레코드를 추가 합니다.
   - 컬럼명을 INSERT문에 명시하지 않으면 모든 컬럼의 값을 전부 입력해야합니다.

-- 컬럼명을 지정하지 않으면 전체 컬럼값을 명시해야 합니다.
INSERT INTO visitor
VALUES(visitor_num_seq.NextVal,
 '안녕하세요', '홍길순', 'webmaster@email.pe.kr', 0);


--컬럼을 명시한 경우는 필요한 컬럼만 값을 입력할 수 있습니다.(권장)
INSERT INTO visitor (num, title, name)
VALUES(visitor_num_seq.NextVal, '반갑습니다.','개발자');


SELECT * FROM visitor;

COMMIT WORK;



2. UPDATE

UPDATE visitor
SET title='자바 개발자입니다.';

SELECT num, title, name, email, visited FROM visitor;

-- 수정된 레코드 취소
ROLLBACK WORK;


UPDATE visitor
SET title='자바 개발자 입니다.', email='java@mail.com'
WHERE num=21;

SELECT num, title, name, email, visited FROM visitor;

COMMIT; 



3. DELETE

-- 모든 레코드 삭제
DELETE FROM visitor;
SELECT num, title, name, email, visited FROM visitor;
ROLLBACK;

-- 특정 레코드 삭제
DELETE FROM visitor WHERE num=21;
SELECT num, title, name, email, visited FROM visitor;
ROLLBACK;




▩ SELECT(DQL: Data Query Language)

1. 컬럼명에 별명을 사용한 경우

--실제의 컬럼명과 별명을 전부 사용할 수 있습니다.
SELECT num 번호, title 제목, name 성명
FROM visitor;


--권장
SELECT num as 번호, title as 제목, name as 성명
FROM visitor;


SELECT num as "_num", title as "_title", name as "_name"
FROM visitor;



2. 기본적인 WHERE 조건을 명시한 경우

--2번 방문자를 보고 싶은 경우
--WHERE절에 조건을 명시합니다.
SELECT * FROM visitor WHERE num=2;


--톰 크루즈만 보고 싶은 경우(문자는 "':Single Quotation Mark"로 닫아줍니다.)
SELECT * FROM visitor WHERE name='톰 크루즈';


--톰 크루즈의 전자우편 주소만 보고 싶은 경우
SELECT email FROM visitor WHERE name='톰 크루즈';


--num 컬럼이 5보다 큰 사람 출력
SELECT * FROM visitor WHERE num >= 5;


--num 컬럼이 4 보다 크고 6보다 작은 사람 출력
--조건을 모두 만족해야 함으로 "and"를 사용합니다.
SELECT * FROM visitor WHERE num >= 4 and num <=6;


--num 컬럼이 4 보다 작거나 6보다 큰 사람 출력
--조건이 하나만 참이라도 레코드 출력
SELECT * FROM visitor WHERE num < 4 or num > 6;



3. 레코드 정렬
   - ASC: 오름 차순 정렬(Ascending), 기본 정렬 값
   - DESC: 내림 차순 정렬(Decending), DESC로 지정


--오름차순으로 정렬하여 봅니다.
SELECT * FROM visitor ORDER BY num;

SELECT * FROM visitor ORDER BY num ASC;


--내림차순으로 정렬하여 봅니다.
SELECT * FROM visitor ORDER BY num DESC;


-- 정렬을 위한 레코드를 추가합니다.
INSERT INTO visitor (num, title, name)
VALUES(visitor_num_seq.NextVal, '반갑습니다.','1가길동');
INSERT INTO visitor (num, title, name)
VALUES(visitor_num_seq.NextVal, '반갑습니다.','2가길동');
INSERT INTO visitor (num, title, name)
VALUES(visitor_num_seq.NextVal, '반갑습니다.','A가길동');
INSERT INTO visitor (num, title, name)
VALUES(visitor_num_seq.NextVal, '반갑습니다.','B가길동');


☞ 정렬 순서 ASC : 숫자 > 영어 > 한글 (UNICODE 순)

SELECT * FROM visitor ORDER BY name ASC;

SELECT * FROM visitor ORDER BY name DESC;



-- 2개의 컬럼으로 정렬
-- title 컬럼을 기준으로 내림차순 1차 정렬한 후
-- name 컬럼을 기준으로 오름차순 2차 정렬 합니다.
SELECT * FROM visitor
ORDER BY title DESC, name ASC;



--번호가 5보다 큰 컬럼만 정렬 대상으로 한 경우
SELECT *
FROM visitor
WHERE num >= 5
ORDER BY title DESC, name ASC;




▩ 함수(Function)의 사용

-- 레코드의 갯수를 구하는 count()함수의 사용
-- 게시판의 글 수를 파악 할 경우 사용
   SELECT count(*) as count FROM visitor;


-- 특정 컬럼을 지정하여 레코드의 갯수를 구하는 count()함수의 사용, null값을 가지고 있는
-- 컬럼은 갯수에서 제외됩니다.
-- null값이 없는 컬럼을 대상으로 count()함수를 사용합니다.
   SELECT count(num) as num_count FROM visitor;
  
   SELECT count(email) as email_count FROM visitor;
  
   SELECT * FROM visitor;


-- 합계를 구하는 sum()함수의 사용
-- 매출액의 합계를 구하는 경우등에 사용합니다.
   SELECT sum(num) as num_sum FROM visitor;


-- 평균을 구하는 avg()함수의 사용
-- 년도별, 월별 평균 매출액을 산출시 사용합니다.
   select avg(num) as num_avg from visitor;


-- email 컬럼이 null값이면 'Q'로 출력
-- null값을 프로그램에서 'null'문자 출력 됨으로 이 문자를
-- 제거하기위해 null값을 특수한 문자로 변경합니다.
   SELECT num, nvl(email, 'Q') as email
   FROM visitor;


-- visited컬럼이 null값이면 0으로 출력
   SELECT num, nvl(visited, 0) as visited
   FROM visitor;


-- 지정된 날짜 형식으로 출력
   SELECT TO_CHAR(sysdate, 'YYYY/MM/DD HH:MI:SS') from dual;


-- 반올림 함수
   SELECT round(12541.005, 0) FROM dual;
   SELECT round(12541.005, 2) FROM dual;
   SELECT round(12541.005, -1) FROM dual;
   SELECT round(12541.005, -3) FROM dual;
  


-- 천단위 컴마사용 함수, 자리수를 적게주면 '#'이 출력됩니다.
   SELECT TO_CHAR(2000000, '9,999') FROM dual;
   SELECT TO_CHAR(2000000, '9,999,999') FROM dual;



▩ LIKE
   - "LIKE"는 비슷한 문자를 찾는 경우에 사용합니다.
   - "="는 완전히 일치하는 문자를 찾습니다.
   
   - %KOREA%: KOREA이란 단어가 들어간 레코드 출력
   - KOREA%: 시작 문자가 KOREA로 시작하는 레코드 출력    
   - %KOREA: KOREA로 끝나는 레코드 출력  
   - KOREA: KOREA과 일치하는 레코드 출력  

   SELECT * FROM visitor;
  
   -- 가길동과 일치하는 레코드 출력
   SELECT * FROM visitor WHERE name LIKE '가길동';
  
   -- 가길동으로 끝나는 레코드 출력
   SELECT * FROM visitor WHERE name LIKE '%가길동';  
  
   -- 1로 시작하는 레코드 출력
   SELECT * FROM visitor WHERE name LIKE '1%'; 
  
   -- 길자가 들어간 레코드 출력
   SELECT * FROM visitor WHERE name LIKE '%길%'; 
  
   


▩ TRANSACTION
   - INSERT, DELETE, UPDATE 쿼리의 실행을 트랜잭션이라고 부릅니다.
   - 데이터에 변화가 발생하는 쿼리는 트랜잭션이라고 합니다.
   - DBEdit에서 트랜잭션 설정 'Auto-commit on'을 체크해제합니다.
   - DDL문 즉 구조와 관련된 쿼리는 트랜잭션과 관련이 없습니다.
   - COMMIT, ROLLBACK후에는 DB에 적용이 되어
     다시 COMMIT, ROLLBACK을 할 수 없습니다.
  
   CREATE TABLE sungjuk(
      hakbun INT NOT NULL, -- 학번
      kuk    INT NOT NULL, -- 국어
      eng    INT NOT NULL, -- 영어
      tot    INT NULL      -- 총점
   )   
  
   INSERT INTO sungjuk(hakbun, kuk, eng) VALUES (1, 100, 90);
  
   SELECT * FROM sungjuk;
  
   ROLLBACK WORK;
  
   SELECT * FROM sungjuk;
  
   INSERT INTO sungjuk(hakbun, kuk, eng) VALUES (1, 100, 90);
   INSERT INTO sungjuk(hakbun, kuk, eng) VALUES (2, 90, 90);     
     
   SELECT * FROM sungjuk;
  
   ROLLBACK WORK;
  
   SELECT * FROM sungjuk;
  
   INSERT INTO sungjuk(hakbun, kuk, eng) VALUES (1, 100, 90);
   INSERT INTO sungjuk(hakbun, kuk, eng) VALUES (2, 90, 90);
   UPDATE sungjuk SET tot = kuk + eng;
  
   SELECT * FROM sungjuk;
  
   COMMIT WORK;  -- DB에 적용합니다.
  
   ROLLBACK WORK;
  
   SELECT * FROM sungjuk;

Posted by 행복한 프로그래머 궁금쟁이박
TAG

댓글을 달아 주세요