반응형
Notice
Recent Posts
Recent Comments
Link
| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 8 | 9 | 10 | 11 | 12 | 13 | 14 |
| 15 | 16 | 17 | 18 | 19 | 20 | 21 |
| 22 | 23 | 24 | 25 | 26 | 27 | 28 |
| 29 | 30 | 31 |
Tags
- 타입스크립트
- 스프링
- 이안의평일코딩
- 자스코테
- php
- 정보처리기사정리
- CSS
- 국비IT
- 정보처리기사
- VUE
- 자바스크립트
- 국비코딩
- 리액트네이티브
- javascript
- 정보처리기사실기
- ReactNative
- 리액트
- 코딩테스트
- react
- typescript
- 정보처리기사요약
- spring
- 정보처리기사실기요약
- 오라클
- Java의정석
- 정보처리기사실기정리
- 자바스크립트 코딩테스트
- 자바의정석
- 평일코딩
- Oracle
Archives
- Today
- Total
이안의 평일코딩
Oracle 4일차 - SQL 단일행함수2 / 지니뮤직 본문
반응형
2020.08.07(금)
1. 지니뮤직 데이터
SQL> conn hr/happy
Connected.
SQL> CREATE TABLE genie_music(
mno NUMBER(3),
title VARCHAR2(300),
singer VARCHAR2(100),
album VARCHAR2(200),
poster VARCHAR2(1000),
state CHAR(6),
idcrement NUMBER(3),
key VARCHAR2(50)
);
SQL> ed test6
SQL> @test6
Table created.
SQL> DESC genie_music;
Name Null? Type
----------------------------------------- -------- ----------------------------
MNO NUMBER(3)
TITLE VARCHAR2(300)
SINGER VARCHAR2(100)
ALBUM VARCHAR2(200)
POSTER VARCHAR2(1000)
STATE CHAR(6)
IDCREMENT NUMBER(3)
KEY VARCHAR2(50)MusicVO
package com.sist.dao;
public class MusicVO {
private int mno;
private String title;
private String singer;
private String album;
private String poster;
private String state;
private int idcrement;
private String key;
public int getMno() {
return mno;
}
public void setMno(int mno) {
this.mno = mno;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getSinger() {
return singer;
}
public void setSinger(String singer) {
this.singer = singer;
}
public String getAlbum() {
return album;
}
public void setAlbum(String album) {
this.album = album;
}
public String getPoster() {
return poster;
}
public void setPoster(String poster) {
this.poster = poster;
}
public String getState() {
return state;
}
public void setState(String state) {
this.state = state;
}
public int getIdcrement() {
return idcrement;
}
public void setIdcrement(int idcrement) {
this.idcrement = idcrement;
}
public String getKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
}
MusicDAO
package com.sist.dao;
import java.util.*;
import java.sql.*;
public class MusicDAO {
//연결
private Connection conn;
//오라클 => SQL 전송
private PreparedStatement ps;
//URL
private final String URL="jdbc:oracle:thin:@localhost:1521:XE";
/*
* CREATE TABLE genie_music(
mno NUMBER(3),
title VARCHAR2(300),
singer VARCHAR2(100),
album VARCHAR2(200),
poster VARCHAR2(1000),
state CHAR(6),
idcrement NUMBER(3),
key VARCHAR2(50)
);
*/
// 드라이버 등록
public MusicDAO() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}catch(Exception ex) {
System.out.println(ex.getMessage());
}
}
// 연결
public void getConnection() {
try {
conn=DriverManager.getConnection(URL, "hr", "happy");
}catch(Exception ex) {}
}
// 해제
public void disConnection() {
try {
if(ps!=null) ps.close(); //통화 닫고
if(conn!=null) conn.close(); //전화 닫고
}catch(Exception ex) {}
}
// 데이터 추가 (기능)
public void musicInsert(MusicVO vo) {
try {
getConnection();
// String sql = "INSERT INTO genie_music VALUES("
// +vo.getMno()+",'"+vo.getTitle()+"','"+vo.getSinger()
// +"','"+vo.getAlbum()+"','"+vo.getPoster()+"', '"
// +vo.getState()+"',"+vo.getIdcrement()+",'"+vo.getKey()+"')";
String sql = "INSERT INTO genie_music VALUES(?,?,?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
// ? 값을 채운다
ps.setInt(1, vo.getMno());
ps.setString(2, vo.getTitle()); // ' '넣으려면 setString
ps.setString(3, vo.getSinger());
ps.setString(4, vo.getAlbum());
ps.setString(5, vo.getPoster());
ps.setString(6, vo.getState());
ps.setInt(7, vo.getIdcrement());
ps.setString(8, vo.getKey());
// 실행
ps.executeUpdate();
}catch(Exception ex) {
System.out.println(ex.getMessage());
}finally {
disConnection();
}
}
}
MusicMain
package com.sist.main;
import com.sist.dao.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.jsoup.Jsoup;
import org.jsoup.nodes.*;
import org.jsoup.select.Elements;
public class MusicMain {
public static void main(String[] args) {
MusicDAO dao = new MusicDAO();
try {
int k=1;
for(int i=1; i<=4; i++) {
Document doc = Jsoup.connect("https://www.genie.co.kr/chart/top200?ditc=D&ymd=20200807&hh=09&rtm=Y&pg="+i).get();
//System.out.println(doc);
Elements title = doc.select("td.info a.title");
Elements singer = doc.select("td.info a.artist");
Elements album = doc.select("td.info a.albumtitle");
Elements poster = doc.select("a.cover img");
Elements temp = doc.select("span.rank");
for(int j=0; j<title.size(); j++) {
System.out.println("순위:"+k);
System.out.println("노래명:"+title.get(j).text());
System.out.println("가수명:"+singer.get(j).text());
System.out.println("앨범명:"+album.get(j).text());
System.out.println("포스터:"+poster.get(j).attr("src"));
//System.out.println("상태:"+temp.get(j).text());
String str = temp.get(j).text();
String idcrement = "";
String state = "";
if(str.equals("유지")) {
idcrement="0";
state="유지";
}
else if(str.equals("new")) {
idcrement="0";
state="NEW";
}
else {
// 60하강 => 60
idcrement = str.replaceAll("[^0-9]", ""); //^ 제외하고 공백 (숫자빼고 지움)
// 60하강 => 하강
state = str.replaceAll("[^가-힣]", ""); // (한글빼고 지움)
}
System.out.println("상태:"+state);
System.out.println("등폭:"+idcrement);
//System.out.println("동영상 키:"+youtubeKeyData(title.get(j).text()));
System.out.println("===============================");
MusicVO vo = new MusicVO();
vo.setMno(k);
vo.setTitle(title.get(j).text());
vo.setSinger(singer.get(j).text());
vo.setAlbum(album.get(j).text());
vo.setPoster(poster.get(j).attr("src"));
vo.setState(state);
vo.setIdcrement(Integer.parseInt(idcrement));
vo.setKey(youtubeKeyData(title.get(j).text()));
dao.musicInsert(vo);
Thread.sleep(100);
k++;
}
}
}catch(Exception ex) {}
//youtubeKeyData("다시 여기 바닷가");
}
public static String youtubeKeyData(String title) {
String key = "";
try {
Document doc = Jsoup.connect("https://www.youtube.com/results?search_query="+title).get();
Pattern p= Pattern.compile("/watch\\?v=[^가-힣]+"); //찾을 문자열 Pattern, +가 붙으면 한글자 이상
Matcher m = p.matcher(doc.toString()); //p에 해당하는 데이터찾기
while(m.find()) {
//System.out.println(m.group());
String str=m.group();
//watch?v=ESKfHHtiSjs" 에서 =다음부터 "앞까지
str = str.substring(str.indexOf("=")+1,str.indexOf("\""));
key = str;
break;
}
}catch(Exception ex) {System.out.println(ex.getMessage());}
return key;
}
}
===========================================================
2. 단일행 함수
1) 문자 함수
2) 숫자 함수
==========> 급여, 퇴직금
(1) ROUND : 반올림
ROUND(987.654, 2) => 소수점 이하 2자리 ROUND(실수,n) => n+1
987.65
ROUND(987.654, 0)
988
ROUND(987.654, -1)
990
SQL> SELECT ROUND(987.654,2), ROUND(987.654,0), ROUND(987.654,-1) FROM DUAL;
ROUND(987.654,2) ROUND(987.654,0) ROUND(987.654,-1)
---------------- ---------------- -----------------
987.65 988 990(2) TRUNC : 버림
TRUNC(987.654, 2) ==> 987.65
ROUND(987.654, 0) ==> 987
SQL> SELECT TRUNC(987.654,2), TRUNC(987.654,0), TRUNC(987.654,-1) FROM DUAL;
TRUNC(987.654,2) TRUNC(987.654,0) TRUNC(987.654,-1)
---------------- ---------------- -----------------
987.65 987 980(3) CEIL : 올림
CEIL(987.654)
CEIL(987.654)
-------------
988-- CEIL => 총페이지 구하기
SELECT CEIL(COUNT(*)/10.0) FROM emp;
SQL> SELECT CEIL(COUNT(*)/7.0) FROM genie_music;
// 데이터 200개를 한페이지 7개씩 나눔
CEIL(COUNT(*)/7.0)
------------------
29(4) MOD : 나머지
SQL> SELECT MOD(10,3) FROM DUAL;
MOD(10,3)
----------
1-- emp => 사번이 짝수인 사원의 사번, 이름
WHERE MOD(empno, 2) = 0;
SQL> SELECT empno, ename FROM emp WHERE MOD(empno, 2) = 0;
EMPNO ENAME
---------- --------------------
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
3) 날짜 함수
(1) SYSDATE : 시스템의 날짜, 시간 ===> 게시판 등록
-- 시스템의 오늘 날짜 읽기
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
--------
20/08/07-- 어제, 오늘, 내일 날짜 출력
SQL> SELECT SYSDATE-1, SYSDATE, SYSDATE+1 FROM DUAL;
SYSDATE- SYSDATE SYSDATE+
-------- -------- --------
20/08/06 20/08/07 20/08/08
(2) MONTHS_BETWEEN : 기간의 개월수
SQL> SELECT ename, ROUND(MONTHS_BETWEEN(SYSDATE, hiredate),0)/12 FROM emp;
ENAME ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE),0)/12
-------------------- --------------------------------------------
SMITH 39.6666667
ALLEN 39.5
SQL> SELECT ename, TRUNC(ROUND(MONTHS_BETWEEN(SYSDATE, hiredate),0)/12,0) FROM emp;
ENAME TRUNC(ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE),0)/12,0)
-------------------- -----------------------------------------------------
SMITH 39
ALLEN 39
SQL> SELECT ename, TRUNC(ROUND(MONTHS_BETWEEN(SYSDATE, hiredate),0)/12,0) AS 연차 FROM emp;
ENAME 연차
-------------------- ----------
SMITH 39
ALLEN 39
(3) ADD_MONTHS : 개월추가
SQL> SELECT ADD_MONTHS(SYSDATE,6) FROM DUAL;
ADD_MONT
--------
21/02/07
SQL> SELECT ADD_MONTHS('20/06/15',6) FROM DUAL;
ADD_MONT
--------
20/12/15
(4) NEXT_DAY : 요일에 해당하는 날짜
SQL> SELECT NEXT_DAY(SYSDATE, '금') FROM DUAL;
NEXT_DAY
--------
20/08/14
(5) LAST_DAY : 입력된 날의 마지막을 출력
SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;
LAST_DAY
--------
20/08/31
SQL> SELECT LAST_DAY('19/06/01') FROM DUAL;
LAST_DAY
--------
19/06/30
4) 변환 함수
(1) TO_CHAR : 문자열 변환
= 날짜 => 문자열
YYYY (yyyy)
RRRR (rrrr)
YY
RR
YEAR => 연도를 영문으로 표시
SQL> SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY'),
TO_CHAR(SYSDATE, 'RRRR'), TO_CHAR(SYSDATE, 'YY'), TO_CHAR(SYSDATE, 'RR'),
TO_CHAR(SYSDATE, 'YEAR') FROM DUAL;
SYSDATE YYYY RRRR YY RR YEAR
-------- -------- -------- ---- ---- -----------------------------
20/08/07 2020 2020 20 20 TWENTY TWENTYFri Aug 07 15:12:50 KST 2020
MM : 월 => 01 , 12
MON : 유닉스 영어3글자, 윈도용 오라클 한글 Aug 07
MONTH : 전체 영문
SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'MM'), TO_CHAR(SYSDATE, 'MON'), TO_CHAR(SYSDATE, 'MONTH') FROM DUAL;
SYSDATE MM MON MONTH
-------- ---- ---------------- ----------------
20/08/07 08 8월 8월DD : 일을 숫자 2자리 => 07
DAY : 요일 (리눅스:영문, 윈도우:한글)
DDTH : 몇 번째 날인지 표시
SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'DD'), TO_CHAR(SYSDATE, 'DAY'), TO_CHAR(SYSDATE, 'DDTH') FROM DUAL;
SYSDATE DD DAY DDTH
-------- ---- ------------------------ --------
20/08/07 07 금요일 07THHH : 하루를 12시간으로
HH24 : 하루를 24ㅣ간으로
MI : 분
SS : 초
SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'HH'), TO_CHAR(SYSDATE, 'HH24'),
TO_CHAR(SYSDATE, 'MI'), TO_CHAR(SYSDATE,'SS') FROM DUAL;
SYSDATE HH HH24 MI SS
-------- ---- ---- ---- ----
20/08/07 03 15 26 42= 정수 => 문자열 / $ 붙여서표시 / L=> \ 붙여서표시
SQL> SELECT ename, sal, TO_CHAR(sal,'$9,999'), TO_CHAR(sal, 'L9,999') FROM emp;
ENAME SAL TO_CHAR(SAL,'$ TO_CHAR(SAL,'L9,999')
-------------------- ---------- -------------- --------------------------------
SMITH 800 $800 ￦800
ALLEN 1600 $1,600 ￦1,600
WARD 1250 $1,250 ￦1,250
JONES 2975 $2,975 ￦2,975
MARTIN 1250 $1,250 ￦1,250
BLAKE 2850 $2,850 ￦2,850
CLARK 2450 $2,450 ￦2,450
SCOTT 3000 $3,000 ￦3,000
KING 5000 $5,000 ￦5,000
TURNER 1500 $1,500 ￦1,500
ADAMS 1100 $1,100 ￦1,100
JAMES 950 $950 ￦950
FORD 3000 $3,000 ￦3,000
MILLER 1300 $1,300 ￦1,300
(2) TO_NUMBER : 정수 변환
SQL> SELECT TO_NUMBER('5,000', '9,999') FROM DUAL;
TO_NUMBER('5,000','9,999')
--------------------------
5000(3) TO_DATE : 날짜 변환
5) 기타(일반) 함수
(1) NVL : NULL값을 다른 값으로 변경
SQL> SELECT ename, sal, comm, sal+comm FROM emp;
ENAME SAL COMM SAL+COMM
-------------------- ---------- ---------- ----------
SMITH 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975
MARTIN 1250 1400 2650
SQL> SELECT ename, sal, comm, sal+NVL(comm,0) FROM emp;
ENAME SAL COMM SAL+NVL(COMM,0)
-------------------- ---------- ---------- ---------------
SMITH 800 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975 2975
MARTIN 1250 1400 2650(2) DECODE : 다중 IF
DECODE(컬럼명, 값, 출력값,
값, 출력값,
값, 출력값)
SQL> SELECT ename, deptno, DECODE(deptno, 10, '영업부', 20, '개발부', 30, '기획부') as dname FROM emp;
ENAME DEPTNO DNAME
-------------------- ---------- ------------------
SMITH 20 개발부
ALLEN 30 기획부
WARD 30 기획부
JONES 20 개발부
MARTIN 30 기획부
BLAKE 30 기획부
CLARK 10 영업부
SCOTT 20 개발부
KING 10 영업부
TURNER 30 기획부
ADAMS 20 개발부
JAMES 30 기획부
FORD 20 개발부
MILLER 10 영업부SQL> SELECT title, DECODE(state, '유지', '-',
2 '상승', '▲',
3 '하강', '▼',
4 'NEW', 'NEW') as 상태
5 FROM genie_music;(3) CASE : 선택문
SQL> SELECT ename, CASE WHEN deptno = 10 THEN '영업부'
2 WHEN deptno = 20 THEN '개발부'
3 WHEN deptno = 30 THEN '기획부'
4 END "dname"
5 FROM emp;
ENAME dname
-------------------- ------------------
SMITH 개발부
ALLEN 기획부
WARD 기획부
JONES 개발부
MARTIN 기획부
BLAKE 기획부
CLARK 영업부
SCOTT 개발부
KING 영업부
TURNER 기획부
ADAMS 개발부
JAMES 기획부
FORD 개발부
MILLER 영업부SELECT title, CASE WHEN state = '유지' THEN '-'
WHEN state = '상승' THEN '▲'
WHEN state = '하강' THEN '▼'
WHEN state = 'NEW' THEN 'NEW'
END "상태"
FROM genie_music;SELECT ename, SUBSTR(hiredate, 4, 2) "입사월",
CASE WHEN SUBSTR(hiredate, 4, 2) BETWEEN '01' AND '03' THEN '1/4'
WHEN SUBSTR(hiredate, 4, 2) BETWEEN '04' AND '06' THEN '2/4'
WHEN SUBSTR(hiredate, 4, 2) BETWEEN '07' AND '09' THEN '3/4'
WHEN SUBSTR(hiredate, 4, 2) BETWEEN '10' AND '12' THEN '4/4'
END "입사분기"
FROM emp;
-- sal => 1~1000 (Level 1), 1001~2000 (Level 2), 2001~3000 (Level 3)
3001 ~ 4000 (Level 4), 4001~5000 (Level 5)
DECODE => switch case라서 값만 들어가야됨 (범위 지정X)
CASE => 조건을 걸수있기 때문에 여기서 더 유용
SQL> SELECT ename, sal, CASE WHEN sal BETWEEN 1 AND 1000 THEN 'Level 1'
2 WHEN sal BETWEEN 1001 AND 2000 THEN 'Level 2'
3 WHEN sal BETWEEN 2001 AND 3000 THEN 'Level 3'
4 WHEN sal BETWEEN 3001 AND 4000 THEN 'Level 4'
5 WHEN sal BETWEEN 4001 AND 5000 THEN 'Level 5'
6 END "Level"
7 FROM emp;
ENAME SAL Level
-------------------- ---------- --------------
SMITH 800 Level 1
ALLEN 1600 Level 2
WARD 1250 Level 2
JONES 2975 Level 3
MARTIN 1250 Level 2
BLAKE 2850 Level 3
CLARK 2450 Level 3
SCOTT 3000 Level 3
KING 5000 Level 5
TURNER 1500 Level 2
ADAMS 1100 Level 2
JAMES 950 Level 1
FORD 3000 Level 3
MILLER 1300 Level 2
(4) RANK() OVER : 순위 (공동순위 다음에 넘어감)
SQL> SELECT ename, sal, RANK() OVER(ORDER BY sal DESC) as rank FROM emp;
ENAME SAL RANK
-------------------- ---------- ----------
KING 5000 1
FORD 3000 2
SCOTT 3000 2
JONES 2975 4
BLAKE 2850 5
CLARK 2450 6
ALLEN 1600 7
TURNER 1500 8
MILLER 1300 9
WARD 1250 10
MARTIN 1250 10
ADAMS 1100 12
JAMES 950 13
SMITH 800 14
(5) DENSE_RANK() OVER : 순위 (공동순위 다음에 순차적)
SQL> SELECT ename, sal, DENSE_RANK() OVER(ORDER BY sal DESC) as rank FROM emp;
ENAME SAL RANK
-------------------- ---------- ----------
KING 5000 1
FORD 3000 2
SCOTT 3000 2
JONES 2975 3
BLAKE 2850 4
CLARK 2450 5
ALLEN 1600 6
TURNER 1500 7
MILLER 1300 8
WARD 1250 9
MARTIN 1250 9
ADAMS 1100 10
JAMES 950 11
SMITH 800 12
반응형
'Back-end > Oracle' 카테고리의 다른 글
| Oracle 6일차 - SQL 복수행함수2 (0) | 2020.08.11 |
|---|---|
| Oracle 5일차 - SQL 복수행함수 (0) | 2020.08.10 |
| Oracle 3일차 - SQL 단일행함수 (0) | 2020.08.06 |
| Oracle 2일차 - SQL WHERE 조건문 (0) | 2020.08.05 |
| Oracle 1일차 - SQL SELECT (2) | 2020.08.04 |
'Back-end/Oracle' Related Articles
more
Comments