◆ MEMBER 테이블 생성
CREATE TABLE MEMBER (
ID VARCHAR2(20),
PASS VARCHAR2(20),
NAME VARCHAR2(10),
GENDER VARCHAR2(6),
AGE NUMBER,
BIRTHDAY VARCHAR2(10),
PHONE VARCHAR2(13),
SIGNDATE DATE
);
ㄴ▣ MEMBER 테이블 구조 변경
CREATE TABLE member (
id VARCHAR2(20),
PASS VARCHAR2(20),
NAME VARCHAR2(10), -- NVARCHAR2(10) --
GENDER VARCHAR2(6), -- NCHAR(1) F:여성 , M:남성 --
AGE NUMBER, -- 삭제 --
BIRTHDAY VARCHAR2(10),
PHONE VARCHAR2(13),
SIGNDATE DATE
-- EMAIL VARCHAR2(100) 추가 --
);
[아래 구문 실행]
ALTER TABLE MEMBER MODIFY NAME NVARCHAR2(10);
ALTER TABLE MEMBER DROP COLUMN AGE;
ALTER TABLE MEMBER ADD EMAIL VARCHAR2(100);
[수정된 쿼리]
CREATE TABLE member (
id VARCHAR2(20),
PASS VARCHAR2(20),
NAME NVARCHAR2(10),
GENDER NCHAR(1),
BIRTHDAY VARCHAR2(10),
PHONE VARCHAR2(13),
SIGNDATE DATE,
EMAIL VARCHAR2(100)
);
ㄴMEMBER 테이블 - DDL ( insert , update , delete , select )
insert into member(id,pass) values('1111','1111');
select * from member;
insert into member(id,pass) values('2222','2222');
select * from member;
update member set pass='3333' where id='2222';
select * from member;
delete from member where id='2222';
select * from member;
commit; //영구 저장되는 영역에 저장
<!-- INSERT -->
01. insert into 테이블명 ( 원하는 칼럼들 ) values ( 해당 칼럼에 해당하는 값들 - 순차적으로 매칭 );
ex) insert into member (id,pass) values ('1111','1111'); //위 예제 인용
02. insert into 테이블명 values ( 모든 칼럼에 해당하는 값들 ); //칼럼 생략
insert into member values('2222','2222','이순이','F','2002-01-01','010-2222-2222','2023-03-01','2222@2222.com'); //실행
<!-- SELECT -->
03. select * from member; //실행
04. select id,pass from member; //실행
<!-- UPDATE -->
05. update member set name='일돌이',email='1111@1111.com' where id='1111'; //실행
06. select * from member; //실행
07. commit; //영구 저장되는 영역에 저장
● 트랜젝션
● 트랜젝션(Transactions) : 하나의 논리적 작업 단위를 구성하는 하나 이상의 sql문
▶ commit : 트랜잭션의 효과를 데이터베이스에 확정하기
▶ rollback : 트랜잭션의 효과를 테이터베이스에 취소하기
● 트랜잭션의 사용 이유?
사용자, 오라클 서버, 애플리케이션 개발자, DBA 등에게 데이터 일치성을 보장하기 위해서 이다.
● 트랜잭션의 예
◎ select * from member where id='1111';
①
추가 실행
② 첫번째) 기존 Developer 에서
update member set name='일돌이' where id='1111'; //실행
③ 두번째) 추가 실행한 Developer 에서
update member set name='홍길동' where id='1111'; //실행
01. 먼저 첫번째) lock 처리 되면서 다른 사용자(두번째)의 영향을 받지 못하도록 한다. ( 다른 사용자 대기 상태 )
02. 임시로 사용되는 영역에서 먼저 테스트 처리를 한다.
03. 첫번째) commit 처리 ( 영구적으로 사용되는 영역에 저장된다. )
04. 첫번째) rollback 처리 ( 취소 처리된다. )
05. commit , rollback 둘 중 하나를 실행하면 unlock 처리가 되면서 대기 하고 있던 다른 사용자(두번째)의 쿼리가 시작한다.
◆ NOTICE 테이블 생성
CREATE TABLE NOTICE(
id NUMBER,
title NVARCHAR2(200),
content CLOB,
writer NVARCHAR2(20),
signdate TIMESTAMP,
hit NUMBER,
files NVARCHAR2(500)
);
ㄴ▣ 데이타 삽입
insert into notice values(1,'제목','내용','1111','2023-01-01 09:01:02',0,'');
insert into notice values(2,'날씨','좋다','2222','2023-02-02 19:21:34',22,'');
insert into notice (id,title,content,writer,hit) values(3,'점심','라면','1111',16);
insert into notice values(4,'저녁','고기','2222','2023-02-04 18:24:14',100,'');
insert into notice values(5,'야식','치킨','2222','2023-02-04 22:05:45',57,'');
insert into notice values(6,'아침','토스트','1111','2023-02-06 07:08:33',44,'');
insert into notice values(7,'아침','패스','2222','2023-02-06 07:48:17',121,'');
commit;
select * from notice;
● DAUL 테이블
▶ Dual 테이블의 정의
1. 오라클 자체에서 제공되는 테이블
2. 간단하게 함수를 이용해서 계산 결과값을 확인 할 때 사용하는 테이블
SELECT * FROM DUAL; //오직 한 행, 한 컬럼(dummy)을 담고 있는 테이블
▶ Dual 테이블의 사용 용도
- dual 테이블은 사용자가 함수(계산)를 실행할 때 임시로 사용하는데 적합하다.
- 함수에 대한 쓰임을 알고 싶을때 특정 테이블을 생성할 필요없이 dual 테이블을 이용하여 함수의 값을 리턴(return)받을 수 있다.
● 산술 연산자 ( + , - , * , / )
● 연산자 : + , - , * , /
01. select hit from notice;
02. select hit+1 from notice;
03. select hit+1 as hit from notice;
04. select hit+1 hit from notice; //as 생략 가능
04. select 9+2 , 9+'2' , '9'+2 , '9'+'2' from dual; //숫자 연산
05. select 9||2 , 9||'2' , '9'||2 , '9'||'2' from dual; //문자 연산
● 관계 연산자 ( and , or , between , in , not )
● 연산자 : not , and , or , between , in
01. select * from notice;
02. select * from notice where hit=0 or hit=100;
03. select * from notice where hit in(0,100);
04. select * from notice where hit not in(0,100);
05. select * from notice where 50<hit and hit<=100;
06. select * from notice where hit between 50 and 100;
● 비교 연산자 ( = , != , ^= , <> , > , < , >= , <= , is null , is not null )
● 연산자 : = , != , ^= , <> , > , < , >= , <= , is null , is not null
01. select * from notice;
02. select * from notice where writer = '1111';
03. select * from notice where writer != '1111';
04. select * from notice where writer ^= '1111';
05. select * from notice where writer <> '1111';
06. select * from notice where hit > 99;
06. select * from notice where signdate is null;
07. select * from notice where signdate is not null;
● 패턴 연산자 ( like , % )
● 연산자 : like , %
insert into member(id,pass,name,gender) values('3333','3333','삼순이','F');
insert into member(id,pass,name,gender) values('4444','4444','최이돌','M');
update member set gender='M' where id='1111';
01. select * from member; // 전체
02. select * from member where name like '이%'; // '이'로 시작하는 이름
03. select * from member where name like '%이'; // '이'로 끝나는 이름
04. select * from member where name like '%이%'; // '이' 단어를 포함한 이름
05. select * from member where name NOT like '이%'; // '이' 로 시작이 아닌 이름
● ROWNUM ( 페이징 )
01. select * from notice;
02. select * from notice where rownum between 1 and 5; //출력
03. select * from notice where rownum between 6 and 10; //출력할 내용이 없음
04. select * from (select rownum num,notice.* from notice) where num between 1 and 5; // 1페이지
05. select * from (select rownum num,notice.* from notice) where num between 6 and 10; // 2페이지
● DISTINCT ( 중복된 값 제거 )
01. select * from notice;
02. select DISTINCT writer from notice;
● 문자열 함수 ( SUBSTR , CONCAT , TRIM , LOWER , UPPER , REPLACE )
select substr('abcd efg',1,3) from dual; --첫번째 문자열에서 3자리 : abc
select substr('abcd efg',3) from dual; --3번째 자리부터 : cd efg
select substrb('abcd efg',3) from dual; --3byte부터 : cd efg
select substrb('좋은 아침',4) from dual; --4byte부터 : 은 아침
01. select * from member; -- 아래와 같이 보여질 수 있도록 값을 변경 ( birthday , phone )
<!-- year , month , day 분리해서 볼 수 있도록 출력 -->
02. select name,birthday,substr(birthday,1,4) y,substr(birthday,6,2) m,substr(birthday,9,2) d from member;
<!-- 연락처가 '010'으로 시작되는 정보를 출력 -->
03. select name,phone from member where phone like '010%'; // 패턴 연산자
04. select name,phone from member where substr(phone,1,3) = '010'; // substr() 이용
<!-- 생년월일중 '01'월과 '12'월만 출력 -->
05. select name,birthday from member where SUBSTR(birthday,6,2) = '01' or SUBSTR(birthday,6,2) = '12';
06. select name,birthday from member where SUBSTR(birthday,6,2) IN('01','12');
== 기타 함수 ==
select CONCAT('좋은','데이') from dual; -- 문자열 합치기
select LTRIM(' 좌측공백 없애기 ') from dual;
select RTRIM(' 우측공백 없애기 ') from dual;
select TRIM(' 양측공백 없애기 ') from dual;
select LOWER('ABC def') from dual; --소문자로 변환
select UPPER('ABC def') from dual; --대문자로 변환
select REPLACE('i am a boy','boy','girl') from dual; -- i am a girl 출력
-- 첫 항 문자열에서 두번째 항에 존재하는 문자열이 있다면 세번째 항에 있는 문자열로 변경 처리
select LPAD('abcde',10) from dual; -- 왼쪽부분에 빈공간 처리 (10자리 만들기) : " abcde"
select LPAD('abcde',10, '-') from dual; -- 왼쪽부분에 빈공간 '-' 처리 (10자리 만들기) : "-----abcde"
select RPAD('abcde',10) from dual; -- 오른쪽부분에 빈공간 처리 (10자리 만들기) : "abcde "
select RPAD('abcde',10, '-') from dual; -- 오늘쪽부분에 빈공간 '-' 처리 (10자리 만들기) : "abcde-----"
select INITCAP('i am a boy') from dual; -- 단어의 첫 글짜마다 대문자 처리 : I Am A Boy
● 날짜 함수
01. select SYSDATE,SYSTIMESTAMP FROM DUAL;
02. ALTER SESSION SET NLS_DATE_FORMAT= 'YYYY-MM-DD HH24:MI:SS'; -- 실행
03. select SYSDATE,SYSTIMESTAMP FROM DUAL;
select TO_CHAR(SYSDATE, 'YYYY') from dual; -- 2023
select TO_CHAR(SYSDATE, 'MM') from dual; -- 04
select TO_CHAR(SYSDATE, 'DD') from dual; --13
select TO_CHAR(SYSDATE, 'HH24') from dual; --15
select TO_CHAR(SYSDATE, 'MI') from dual; --52
select TO_CHAR(SYSDATE, 'SS') from dual; --47
select EXTRACT(MONTH FROM SYSDATE) from dual; -- 4 (한자리)
select EXTRACT(SECOND FROM SYSTIMESTAMP) from dual; --47.276 (소수점)
select SYSDATE,ADD_MONTHS(SYSDATE,1) FROM DUAL; -- 한달 후 날짜
select
SYSDATE, --현재날짜
SYSDATE + 100, --현재날짜 +100일
SYSDATE - 100 --현재날짜 -100일
from dual;
ㄴ▣ Time Zone
01. select sysdate, systimestamp from DUAL;
02. select * from v$timezone_names;
03. select tz_offset('asia/seoul') from dual; //+09:00
● 숫자 함수
01. select SIGN(10) , SIGN(-10) , SIGN(0) from dual; -- 양수,음수,0 반환
02. select ROUND(3.14),ROUND(3.64) from dual; -- 소수점 첫번째 자리에서 반올림한 정수 반환
03. select ROUND(1.23456, 2),ROUND(1.23456, 3) from dual;
04. select TRUNC(17/5) 몫, MOD(17,5) 나머지 from dual;
05. select POWER(5,2) 제곱, SQRT(25) 제곱근 from dual;
● 변환 함수 ( TO_CHAR , TO_DATE , TO_NUMBER )
◆ 숫자,날짜 ≫ 문자 : TO_CHAR()
◆ 문자 ≫ 날짜 : TO_DATE()
◆ 문자 ≫ 숫자 : TO_NUMBER()
01. 숫자 > 문자
-- 3자리마다 쉼표 처리 , 마침표 뒤는 소수점 의미
-- 좌측 항의 길이보다 우측 항의 길이가 같거나 커야한다.
① select to_char(1234567, '9,999,999') from dual; --좌,우측항의 길이가 같을 때
② select to_char(1234567, '999,999') from dual; --좌측항이 길 때
③ select to_char(1234567, '99,999,999,999') from dual; -- 우측항이 길 때
--왼쪽 공백란이 생긴다.
④ select TRIM(to_char(1234567, '99,999,999,999')) from dual; -- TRIM()함수 이용
⑤ select TRIM(to_char(1234567, '99,999,999,999.99')) from dual; --소수점 2자리 표현
⑥ select TRIM(to_char(1234567, '99,999,999,999')) || '원' from dual; --원
02. 날짜 > 문자
⑥ select TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
select TO_CHAR(sysdate, 'yyyy') from dual; --2023
select TO_CHAR(sysdate, 'RRRR') from dual; --2023
select TO_CHAR(sysdate, 'yy') from dual; --23
select TO_CHAR(sysdate, 'year') from dual; --twenty twenty-three
select TO_CHAR(sysdate, 'mm') from dual; --04
select TO_CHAR(sysdate, 'mon') from dual; --4월
select TO_CHAR(sysdate, 'month') from dual; --4월
select TO_CHAR(sysdate, 'dd') from dual; --14
select TO_CHAR(sysdate, 'day') from dual; --금요일
select TO_CHAR(sysdate, 'ddth') from dual; --14th
select TO_CHAR(sysdate, 'am') from dual; --오전
select TO_CHAR(sysdate, 'fm') from dual; --null
select TO_CHAR(sysdate, 'hh24') from dual; --09
select TO_CHAR(sysdate, 'mi') from dual; --02
select TO_CHAR(sysdate, 'ss') from dual; --16
02. 문자 > 날짜
select TO_DATE('2023-04-14') from dual;
select TO_DATE('2023-04-14 09:11:22', 'YYYY-MM-DD HH24:MI:SS') from dual;
02. 문자 > 숫자
select to_number('2023') from dua
● 집계 함수
▣ order by ( asc , desc )
▣ select * from member;
01. ORDER BY 칼럼명 ASC or DESC
① select id,pass,name,birthday from member order by birthday asc;
② select id,pass,name,birthday from member order by birthday desc;
▣ group by ( sum , min , max , count , avg )
▣ select * from notice;
① select writer,sum(hit) from notice group by writer; --writer 별 총 조회수
② select writer,min(hit) from notice group by writer; --writer 별 최저 조회수
③ select writer,max(hit) from notice group by writer; --writer 별 최고 조회수
④ select writer,avg(hit) from notice group by writer; --writer 별 총 조회수
⑤ select writer,count(*) from notice group by writer; --writer 별 총 작성수
⑥ select writer,count(*) from notice group by writer HAVING count(*) > 3; --group by 절에서 조건은 HAVING 이용
● 순위 함수 ( row_number , rank , dense_rank )
▣ select * from notice;
01. 정렬 관련 쿼리문
① select * from notice order by hit; --hit 칼럼 오름차순으로 정렬
② select ROWNUM num,notice.* from notice order by hit;
③ select * from (select ROWNUM num,notice.* from notice) order by hit;
④ select ROW_NUMBER() OVER (order by hit desc) num, notice.* from notice;
⑤ update notice set hit=44 where id=5; --조회수 변경
commit; --실행
⑥ select RANK() OVER (order by hit desc) num, notice.* from notice; --순위 출력 : 공동 4위 2건이니 다음은 6위
⑦ select DENSE_RANK() OVER (order by hit) num, notice.* from notice; --순위 출력 : 공동 4위 상관없이 다음은 5위
⑧ select DENSE_RANK() OVER (PARTITION by writer order by hit) num, notice.* from notice; --작성자 별 순위
● 서브 쿼리 ( 선택 칼럼 기준 졍렬 후 페이징 처리 )
① select ROW_NUMBER() OVER (order by hit) num, notice.* from notice; --조회수 오름차순 졍렬 후 전체 목록
② select * from (select ROW_NUMBER() OVER (order by hit) num, notice.* from notice) where num between 1 and 5; -- 1페이지
③ select * from (select ROW_NUMBER() OVER (order by hit) num, notice.* from notice) where num between 6 and 10; -- 2페이지
'Database > Oracle' 카테고리의 다른 글
| Oracle 03 Spinrg 연동 및 설정 (0) | 2023.06.09 |
|---|---|
| Oracle 기본01 설정잡기 (0) | 2023.06.08 |
댓글