MYSQL 함수 모음

1) 숫자 함수
ABS(X) : X 에 해당하는 절대 값을 돌려준다.
SIGN(X) : X의 값의 부호 값을 돌려 준다.(-1 : 음수, 0 : 0, 1 : 양수)
MOD(N,M) : N을 M으로 나눈 값의 나머지를 돌려 준다.
FLOOR(X) : 실수 X 값의 소수점 이하의 값은 버림을 한 정수 값을 돌려 준다.
CEILING(X) : 실수 X 값의 소수점 이하의 값을 올림을 한 정수 값을 돌려 준다.
ROUND(X,D) : 실수 X 값에서 소수점에서 D + 1번째에 해당하는 값을 올림한 값을 돌려 준다. POWER(X,Y) : X의 값을 Y번 곱한 값을 돌려 준다. RAND() : 0에서 1 사이의 숫자를 랜덤하게 발생시겨 돌려준다.

SELECT abs(-2),abs(2),sign(-45),sign(0),sign(34),mod(23,7);
SELECT floor(1,234),ceiling(1,234),round(2,49),round(2,51); SELECT power(3,2),power(3,3),rand();


문자열함수
2) 문자열 함수
CHAR(N,...) : N(1-256의 정소)값의 아스키코드를 해당 문자로 출력한다. CONCAT(str1,str2,...) : 각각의 str1,str2,...등을 하나의 스트링으로 돌려 준다. 단, NULL 이 있으면 NULL 을 출력한다.
CONCAT_WS(separator, str1, str2,...) : 각각의 str1,str2,...등을 separator을 구분자로 사용하여 하나의 스트링으로 출력한다.
LENGTH(str) : str의 문자의 길이를 출력한다.

SELECT char(48),char(76),concat(('My','S','ql'),concat('My',NULL,'Sq;');
SELECT concat_ws(',','My',NULL,'Sql',' ','is',' ','Good'); SELECT length('mysql'),locate('bar','foobarbar');


LOCATE(substr,str) : str에서 substr이 위치한 첫번째 위치를 정수 값으로 출력한다.
LEFT(str,len) : str에서 지정해준 자릿수 len만큼 왼쪽에서부터 문자열을 출력한다.
RIGHT(str,len) : str에서 지정해준 자릿수 len만큼 오른쪽에서부터 문자열을 출력한다.
SUBSTRING(str,pos,len) : 문자열 str에서 특정 시작위치 pos에서 지정된 길이 len만큼 문자를 출력한다.
LTRIM(str),RTRIM(str),TRIM(str) : str의 왼쪽,오른쪽,혹은 양쪽 모두의 공백 문자를 제거해 준다.

SELECT left('mysql is good',3),right('mysql is good',3); SELECT substring('mysql is good',1,5);
SELECT ltrim(' mysql'),rtrim(mysql '),trim(' mysql ');


SPACE(N) : N개 만큼의 공백 문자를 만들어 출력한다.
REPLACE(str,from_str,to_str) : 문자열 str에서 특정문자 from_str을 특정 문자 to_str로 바꾸어 출력한다.
REPEAT(str,count) : 문자열 str을 count 갯수만큼 반복하여 반복한 값을 출력한다.
REVERSE(str) : 문자열 str을 역으로 출력 한다.
INSERT(str,pos,len,newstr) : 문자열 str에서 특정 위치 pos에서 특정 길이 len 만큼을 newstr문자열로 바꾸어 출력한다.

SELECT concat('a',space(6),'a');
SELECT replace('mysql is good','is','very'),repeat('a',3); SELECT reverse('mysql'),insert('Quadratic',3,4,'What');


LCASE(str) : 문자열 str을 소문자로 바꾸어 출력한다.
UCASE(str) : 문자열 str을 대문자로 바꾸어 출력한다.

SELECT lcase('MYSQL'),ucase('mysql);


날짜시간함수
3) 날짜, 시간 함수
DAYOFWEEK(date) : 해당날짜의 요일값을 출력한다.(1=일요일,2=월요일,3=화요일,...,7=토요일)
WEEKDAY(date) : DayOfWeek() 함수와 같지만 요일값이 다르다.(0=월요일,1=화요일,...,6=일요일)
DAYOFMONTH(date) : 해당날짜의 날(1-31) 값을 출력한다.
DAYOFYEAR(date) : 일년중 몇번째 날(1-366)인지를 출력한다.
MONTH(date) : 해당 날짜의 달(1-12) 값을 출력한다.
DAYNAME(date) : 해당 날짜의 요일 값을 스트링으로 출력한다.

SELECT dayofweek('2003-02-22'),weekday('2003-02-23');
SELECT dayofmonth('2003-02-22'),dayofyear('2003-02-23'); SELECT month('2003-02-22),dayname('2003-02-23');


MONTHNAME(date) : 해당 날짜의 달 값을 스트링으로 출력한다.
QUARTER(date) : 총 4분기중 해당 날짜의 분기 수 값을 출력한다.
WEEK(date,first) : 해당날짜가 1년중 몇번째 주인지를 출력한다.
YEAR(date) : 해당 날짜의 년도 값을 출력한다.
YEARWEEK(date,first) : 해당 날짜의 년도와 1년중 몇번째 주인지를 출력한다.
HOUR(time) : 해당 시간의 시 값을 출력한다.

SELECT monthname('2003-02-22'),quarter('2003-02-23');
SELECT week('2003-02-22'),year('2003-02-23'); SELECT yearweek('2003-02-22'),hour('10:22:32');


MINUTE(time) : 해당 시간의 분 값을 출력한다.
SECOND(time) : 해당 시간의 초 값을 출력한다.
PERIOD_ADD(P,N) : P(YYMM or YYYYMM)값에서 N 달을 뺀 값을 출력한다.
PERIOD_DIFF(P1,P2) : P1(YYMM or YYYYMM)과 P2(YYMM or YYYYMM)의 달 차이를 출력한다.
TO_DAYS(date) : 0년 부터 date까지의 날 수를 출력한다.
FROM_DAYS(N) : N(날)에 해당하는 날짜를 출력한다.

SELECT minute('10:22:32'),second('10:22:32');
SELECT period_add(200302,3),period_diff(0302,200209); SELECT to_days(20030222),from_days(731633);


DATE_FORMAT(date,format) : date의 날짜 값을 원하는 format 값으로 바꾸어 출력한다.
TIME_FORMAT(time,format) : time의 시간 값을 원하는 format 값으로 바꾸어 출력한다.
CURDATE() : 현재의 날짜 값을 출력한다.
CURTIME() : 현재 시간을 출력한다.
NOW() : 현재 시스템의 날짜와 시간을 출력한다.

SELECT date_format('2003-02-22 22:23:00','%D %y %a %d %m %b %j');
SELECT time_format('22:23:00','%H %k %h %i %I %r %p %s %S'); SELECT curdate(),curtime(),now();


제어함수
4) 제어 함수
1) ~ 3)까지의 함수들은 이름만 들어도 대강은 어떤 함수일 거란 것을 예상 할 것이다. 그럼 제어 함수란 무엇인가. 이들 함수는 일반적인 프로그래밍 언어에서의 조건문과 같은 역할을 하는 함수들이다. 종류로는 IsNULL,NULLIF,IF,CASE등이 있으며 이들 함수로 인해 SQL문 안에서 값들을 제어 할 수가 있다.

IFNULL(expr1,expr2) : expr1 값이 널이면 expr2값이 출력이 되는 함수
NULLIF(expr1,expr2) : expr1 값과 expr2값이 같으면 NULL이 출력이 되고 다르면 expr1 값이 출력되는 함수
IF(expr1,expr2,expr3) : expr1 값이 참이면 expr2 값이 출력되고 거짓이면 expr3값이 출력되는 함수
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END : value 값이 WHEN 다음의 compare-value값과 같으면 THEN 다음의 result 값을 출력하는 함수
SELECT IFNULL(NULL,2),IFNULL(1,2),IF(1 > 0,1,0),IF(1 < id =" 'kim';" id =" a.ID)" id =" a.ID;">등을 서브쿼리(SubQuery)에서도 지원하고 있다. 이건 오픈 소스 데이터 베이스의 한계로 앞으로 이들 오픈 소스 데이터 베이스도 역시 지원을 할 것이다.

[오라클] 기본팁
<< 오라클 >>** 오라클 사용시 "commit;" 명령어 사용함 **
1. [테이블의 필드 확인] - desc 테이블명;-예) desc cybedu_board01;
2. [날짜 표현] - sysdate (MS-SQL에서는 getdate())-예) select sysdate from cybedu_section;
3. [중간 글 틀렸을때 수정] - c옵션은 변환, r옵션은 변환값으로 재실c행
예) SQL> 22* where substring(convert(varchar2(20),sysdate,120),1,10) >= 2004-07-29SQL> c/varchar2/varchar2* where substring(convert(varchar(20),sysdate,120),1,10) >= 2004-07-29SQL> 33* and substring(convert(varchar2(20),sysdate,120),1,10) <= 2004-08-05SQL> c/char2/char3* and substring(convert(varchar(20),sysdate,120),1,10) <= 2004-08-05SQL> r1 select popWidth, popHeight, popFlag from cybedu_popup2 where substring(convert(varchar(20),sysdate,120),1,10) >= 2004-07-293 and substring(convert(varchar(20),sysdate,120),1,10) <= 2004-08-054* order by 2004-07-29 desc, 2004-08-05 desc, popsn descwhere substring(convert(varchar(20),sysdate,120),1,10) >= 2004-07-29
=> 오라클에서는 substring -> substr, convert사용안함, varchar -> to_char로 사용* 아래는 문제 해결 구문 *
sql = "select popWidth, popHeight, popFlag from cybedu_popup"; sql = sql + " where substr(to_char(sysdate,'YYYY-MM-DD'),1,10) >= popstartday";sql = sql + " and substr(to_char(sysdate,'YYYY-MM-DD'),1,10) <= popendday";sql = sql + " order by popstartday desc, popendday desc, popsn desc"; 4. oracle에서 left outer join을 하시려면[MY-SQL]select aa.day,aa.count,bb.apply from (select day,count from tblCount where year='2004' and month='06') aa left outer join (select day, count(*) as apply from tblApply where year='2004' and month='06' and Deliverystate='신청접수' group by month,day) bb on aa.day = bb.day; [오라클]select aa.day,aa.count,bb.apply from (select day,count from tblCount where year='2004' and month='06') aa , (select day, count(*) as apply from tblApply where year='2004' and month='06' and Deliverystate='신청접수' group by month,day) bb where aa.day = bb.day(+) <--이런식으로 써야 outer join이 걸립니다... 5. 오라클에서 컬럼 자동 증가 하기create table cybedu_message ( msgid integer not null, /* 일련번호*/msgSendId varchar2 (10), /*쪽지보낸사람 ID*/ ~ ~ ~msgDate date, /* 쪽지보낸 날짜*/~ ~ ~msgFlagR varchar2 (2), /* 받은쪽지함에 보여지는 여부*/ primary key ( msgid ) ) ; create sequence msgid_seq; ==> 자동증가 (ms-sql: identity(1,1))
6. **** 오라클 기본 문법 ****삭제: delete from 테이블 이름 where 조건절 ;

[MS-SQL] 기본 사용법
1. CEILING -> 소숫점 아래수를 반올림해서 정수값 돌려줌
SELECT Count(*),CEILING(CAST(Count(*) AS FLOAT)/보여줄 페이지수)FROM 테이블명 WHERE search_word LIKE '%SearchString%'
rs.Open strSQL, dbcon
intTotalCount = rs(0) ' 총 페이지 수 (검색어가 있을때, 없을때)intTotalPage = rs(1) ' 보여줄 페이지 수의 값(intPageSize가 10이므로 10페이지씩 몇개를 보여줄지의 수)rs.Close
2. Top 숫자 -> 상위의 "숫자"만큼 뿌려줌
strSQL = "SELECT Top " & intNowPage * intPageSize & " brdnum, brdtitle, brdname, writedatetime, brdcount "strSQL = strSQL+ "FROM gong_news_board WHERE brdstate = '02' "if search_word <> "" thenstrSQL = strSQL & " AND " & search_word & " LIKE '%" & SearchString & "%'"end ifstrSQL = strSQL & " ORDER BY brdnum DESC"
3. ISNULL 사용법
예> SELECT EMPNO, ENAME, DEPTNO FROM EMP;
EMPNO ENAME DEPTNO SALARY--------------------------------------------01001 김갑돌 101 NULL01002 이몽룡 102 1000001003 홍길동 NULL NULL01004 고인돌 NULL 500001005 손오공 111 NULL
이러한 경우,
SELECT EMPNO, ENAME, ISNULL(DEPTNO,'999'), ISNULL(SALARY,0)FROM EMP;
EMPNO ENAME DEPTNO SALARY--------------------------------------------01001 김갑돌 101 001002 이몽룡 102 1000001003 홍길동 999 001004 고인돌 999 500001005 손오공 111 0
이와 같이 얻으실수 있습니다.
4. sql문 case -> if 문으로 변환
[case 문] [if 문]
select m.[번호] select m.[번호], m.[이름] , m.[이름], [회원구분] = , [회원구분] =case if ( when m.[번호] in ( m.[번호] in (select [번호] select [번호]from [정회원] as r ---> from [정회원] as rwhere r.[번호] = m.[번호] where r.[번호] = m.[번호]) )then '정' , '정'else '준' , '준')end , m.[전화번호] , m.[전화번호]from [회원] as m from [회원] as morder by m.[번호] order by m.[번호]
5. ** 해당 날짜 사이의 값 가져오기 **
select count(distinct lecNo) from cybedu_curriculum, cybedu_course, cybedu_section, cybedu_lecture left join cybedu_train on ( lecNo = traNo )where convert(varchar(10),getdate(),120) between secStartregist and secEndregistand secNo=lecSecno and crsCode=lecCrscodeand curCode = crsCurcode and curCode = 'curCode' and secFlag='0'
=> 설명 : from 뒤의 테이블과 join 뒤의 테이블을 left join해서(lecNo = traNo) 값중에서between뒤의 시작날짜와 종료날짜 사이의 값을 가져오라는것..
(아래 sql문은 업그레이드 한 것인거 같네여~)..위랑 같은 내용
select count(distinct lecNo) from cybedu_curriculum, cybedu_course, cybedu_section, cybedu_lecture left join cybedu_train on ( lecNo = traNo ) where convert(varchar(10),getdate(),120) between secStartregist and secEndregistand secNo=lecSecno and crsCode=lecCrscodeand curCode = crsCurcode and secFlag='secFlag' and curcode <> 'offline'
6. delete 구문delete 테이블명 where 조건문
7. update 구문update 테이블명 set 바꿀구문 where 조건구문

[MS-SQL] 중요팁2

----------------------------------<<>>-----------------------------------
1. 기본 쿼리sp_spaceused : DB사용자 정보 보여줌sp_dboption : 가능한 DB 옵션들sp_helpdb : 각DB이름,사이즈,owner등을 보여줌sp_helpdb 디비명 : 디비의 설명
2. 다른 DB의 테이블 불러서 사용하기- 우선 sql서버 엔터프라이즈 관리자에서/ENT01/users/ 사용자를 추가한다.- DB -> ENT01, 111
- 111에서 ENT01의 board14테이블 사용하려면
ex) select * from ENT01.111.board14 -> select * from 해당DB.테이블소유자명.테이블명

[MS-SQL] 중요팁
1. 오픈 쿼리 사용법
* 링크드 리스트가 설정이 되어 있어야 오픈쿼리 사용됨.(MS-SQL에서 사용됨)
------------------------------------ (사용예) ---------------------------------------/*sql = "select convert(varchar(20),user_id), convert(varchar(20),name), convert(varchar(20),jumin)";sql = sql + " from OPENQUERY(jejunet,'SELECT user_id, jumin, name FROM member_table') as member_table where (convert(varchar(20),name) = ?) and (substring(convert(varchar(20),jumin),1,6) = ?)";
pstmt = con.prepareStatement(sql);pstmt.setString(1,name);pstmt.setString(2,pass);rs = pstmt.executeQuery();if(rs.next()){student.setId(rs.getString(1)); */
sql = "select korname, usrjumin from cybedu_userinfo where (korname = ?) and (usrjumin = ?)"; //out.print(sql);
pstmt = con.prepareStatement(sql);pstmt.setString(1,name);pstmt.setString(2,pass);rs = pstmt.executeQuery();if(rs.next()){student.setId(rs.getString(1));--------------------------------------------------------------------------------------
2. 병렬 처리 사용 방법
* 생성시 같이 정의 create table line_item (item_no number(7), item_name varchar(20), ... ) parallel 20;create index item_index on line_item (item) parallel 20;
* 이미 작성된 오브젝트에 대한 병렬처리 alter table line_item parallel 20;alter index item_index rebuild parallel 20;응용프로그램의 수정없이 즉시 사용
* 해당 SQL만 병렬로 처리 : Hint 기능 사용 select /*+ parallel(line_item,20) */ item_name, ... from line_item, products .....

각종 DBMS JDBC 드라이버 셋팅법 정리
//********** 각 경우별 드라이버 연동하기 **************/
** JDK jdbc-odbc driver (Type1) **연결 URL : "jdbc:odbc:"드라이버 클래스 : sun.jdbc.odbc.JdbcOdbcDriver
** Oracle thin driver (Type4) **연결 URL : "jdbc:oracle:thin:@:port:"드라이버 클래스 : oracle.jdbc.driver.OracleDriver
** Oracle oci driver (Type2) **연결 URL : "jdbc:oracle:oci:@"드라이버 클래스 : oracle.jdbc.driver.OracleDriver
** Sybase jConnect driver (Type2) **연결 URL : "jdbc:sybase:Tds::"드라이버 클래스 : com.sybase.jdbc2.jdbc.SybDriver
** Informix JC1 driver (Type4) **연결 URL : "jdbc:informix-sql://:/:INFORMIXSERVER"드라이버 클래스 : com.informix.jdbc.IfxDriver
** mSQL Imaginary JDBC driver (Type4) **연결 URL : "jdbc:msql://:/"드라이버 클래스 : com.imaginary.sql.msql.MsqlDriver
** Postgres driver (Type4) **연결 URL : "jdbc:postgresql://:/"드라이버 클래스 : postgresql.driver
** MM MySQL driver (Type4) **연결 URL : "jdbc:mysql://:/"드라이버 클래스 : org.git.mm.mysql.Driver
mysql은 JConnector 3.0 부터는 com.mysql.jdbc.Driver
연결 URL 은 jdbc:mysql://localhost/dbname?Unicode=true&characterEncoding=EUC_KR
같이 인코딩 타입을 직접 줌으로써 한글 변환문제에 좀더 쉽게 해결할 수 있음.

// ******************************Connected To IBM AS/400
Class.forName("com.ibm.as400.access.AS400JDBCDriver"); com = Driver.Manager.getConnection("jdbc:as400://10.20.30.40/testlib;user=user;password=pass");
// ******************************Connected To Unisql
Class.forName("unisql.jdbc.driver.UniSQLDriver"); con = Driver.Manager.getConnection("jdbc:unisql:10.20.30.40:43300:demodb:::", "user","pass");
// ******************************Connected To Jdbc-Odbc Type - 1 Driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // con = DriverManager.getConnection("Jdbc:Odbc:dsnname","userid","password"); con = DriverManager.getConnection("jdbc:odbc:Driver={SQL Server};Server=servername;Database=pubs","userid","password");
// ******************************Connected To Ms-Access JDBC ODBC Driver .
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverManager.getConnection("Jdbc:Odbc:dsnname","",""); // con = DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=G:/admin.mdb","","");
// ******************************Connected To Ms-Access Type-3 Driver.
Class.forName ("acs.jdbc.Driver"); String url = "jdbc:atinav:servername:5000:C:\admin.mdb"; String username="Admin"; String password=""; Connection con = DriverManager.getConnection(url,username,password);
// ******************************Connected To Microsoft SQL.
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); con = DriverManager.getConnection("jdbc:microsoft:sqlserver://servername:1433","userid","password");
// ******************************Connected To Merant.
Class.forName("com.merant.datadirect.jdbc.sqlserver.SQLServerDriver"); con = DriverManager.getConnection("jdbc:merant:sqlserver://servername:1433;User=userid;Password=password");
// ******************************Connected To Atinav SqlServer.
Class.forName ("net.avenir.jdbc2.Driver"); con= DriverManager.getConnection("jdbc:AvenirDriver://servername:1433/pubs","userid","password");
// ******************************Connected To J-Turbo.
String server="servername"; String database="pubs"; String user="userid"; String password="password";
Class.forName("com.ashna.jturbo.driver.Driver"); con= DriverManager.getConnection("jdbc:JTurbo://"+server+"/"+database,user,password);
// ******************************Connected To jk Jdbc Driver.
String url= "jdbc:jk:server@pubs:1433"; Properties prop = new Properties(); prop.put("user","userid");//Set the user name prop.put("password","password");//Set the password
Class.forName ("com.jk.jdbc.Driver").newInstance(); con = DriverManager.getConnection (url, prop);*/
// ******************************Connected To jNetDirect Type - 4 Driver
String sConnect = "jdbc:JSQLConnect://127.0.0.1/database=pubs&user=userid&password=password"; Class.forName ("com.jnetdirect.jsql.JSQLDriver").newInstance(); Connection con= DriverManager.getConnection(sConnect);
// ******************************Connected To AvenirDriver Type - 4 Driver
// String url= "jdbc: AvenirDriver: //servername:1433/pubs"; // java.util.Properties prop = new java.util.Properties (); // prop.put("user","userid"); // prop.put("password","password");
Class.forName ("net.avenir.jdbc2.Driver"); System.out.println(" Connected To AvenirDriver Type - 4 Driver"); con= DriverManager.getConnection("jdbc:AvenirDriver://servername:1433/pubs","userid","password");
// ******************************Connected To iNet Sprinta2000 Type - 4 Driver
String url="jdbc:inetdae7:servername:1433"; String login="userid"; String password="password"; Class.forName("com.inet.tds.TdsDriver"); System.out.println(" Connected To iNet Sprinta2000 Type - 4 Driver"); con=DriverManager.getConnection(url,login,password);
// ******************************Connected To iNet Opta2000 Type - 4 Driver String url="jdbc:inetdae7:servername:1433"; String login="sagar"; String password="sagar"; Class.forName("com.inet.tds.TdsDriver").newInstance(); System.out.println(" Connected To iNet Opta2000 Type - 4 Driver"); con=DriverManager.getConnection(url,login,password);
출처 : http://blog.empas.com/yooncom77/18149708
(출처 : BoKi - http://boki.tistory.com/9)