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 < 0,1,0);
SELECT CASE 1 WHEN 1 THEN "one"
WHEN 2 THEN "two" ELSE "more" END;


지금까지 SQL문에서 많은 쓰이는 함수들에 대해서 공부해 보았다. 이들 함수 말고도 많은 다른 함수들이 존재하니 꼭 한번쯤은 봐 두길 바란다. 다음은 Gruop By 절과 GROUP BY 절과 같이 쓰이는 그룹함수에 대해서 알아보자.

GROUP BY에 대해서

GROUP BY 문은 SELECT 문으로 데이터베이스로부터 얻어오는 내용 중에 최대값, 최소값, 합계, 평균 혹은 분산등 여러 열의 컬럼을 포함하는 집합 연산(aggregate function)이 적용된 경우에 주로 사용된다. 이런 연산에 사용하는 함수는 좀더 후에 배우고 먼저 GROUP BY문에 대해서 알아 보겠다.

GROUP BY절은 특정 컬럼의 값들을 하나로 묶는 역할을 한다. 이 말은 특정 컬럼의 값이 같으면 그 열 값은 하나의 열 값으로 취급한다는 말이다. 백문이 불여일견이라고 먼저 직접 테스트를 해보자. 테스트를 하기 위해서 다음과 같이 테이블을 하나 만든다.

CREATE TABLE group_test(
id varchar(10) not null,
number int not null,
string varchar(10) not null
}

위와 같이 만들었다면 테이블에 값을 다음 그림과 같이 넣어 보자.

SELECT * FROM group_test;

이제 말로만 떠들지 말고 직접 테스트를 해보자. 과연 어떤 결과가 나올지. string 컬럼을 그룹화하여 보자.

SELECT * FROM group_test GROUP BY string;

위의 그림처럼 많은 변화가 생겼다. 이렇듯 특정 컬럼을 그룹화하면 그 컬럼의 동일한 값은 하나로 묶여 출력이 된다.  이제는 이 Group By 절을 사용하여 그룹함수를 사용해 보자.

그룹함수(aggregate function)에 대해서

그룹 함수도 여러개가 존재 하지만 여기서는 자주 사용하는 함수 몇 개에 대해서 설명을 하겠다.
그 전에 그룹함수라 하여 꼭 GROUP BY절과 같이 사용해야 하는 것은 아니며 단지 일반적으로

SELECT 컬럼1,그룹함수 FROM test1

와 같은 비슷한 형식일 경우에는 GROUP BY절을 같이 사용하여야 한다. 그렇지 않고 그룹함수만 쓸 경우에는 GROUP BY 절을 꼭 같이 써야 할 필요는 없다.

AVG() : 이 함수는 ()안의 컬럼들의 평균을 출력하는 함수이다.
MAX() : 이 함수는 ()안의 컬럼들 중 가장 큰 값을 출력하는 함수이다.
MIN() : 이 함수는 ()안의 컬럼들 중 가장 작은 값을 출력하는 함수이다.
SUM() : 이 함수는 ()안의 컬럼들의 합계을 출력하는 함수이다.
COUNT() : 이 함수는 레크드의 수를 출력하는 함수이다.

SELECT count(*),avg(number),max(string),min(id),sum(number) FROM group_test;

이제 이들을 활용해 보자. 어떤 경우에 이들을 효율적으로 사용할 수 있을까?

예를 들어 어떤 사람이 'id가 'kim'이라는 사람의 number의 값의 평균과 총점을 구하고 싶다'라고 주문을 해왔다.
그러면 여러분들은 어떻게 하겠는가? 지금까지 배운데로 먼저 이들의 값을 구해보자.

SELECT avg(number),sum(number) FROM group_test WHERE id = 'kim';

여기까지는 문제가 없다. 그렇다면 이제는 id별로 그 사람들의 number의 값의 평균과 총점을 구하고 싶다고 한다.
그러면 여러분들은 어떻게 하면 되겠는가? 그렇다. 이럴때 유용하게 쓰이는 것이 GROUP BY 절이다.
이를 사용하여 id를 그룹화하면 이들 각각의 평균과 총점을 구할 수 있다.

SELECT id,avg(number),sum(number) FROM group_test GROUP BY id;


ORDER BY 절에 대해서 이제는 결과 값을 정렬하는 방법에 대해서 알아보자. 정렬을 하기 위해서 사용하는 것이 ORDER BY 절이다.
이 절은 특정 컬럼을 중심으로 내림차순 정렬 혹은 오름차순 정렬을 할 수가 있다. 그럼 먼저 형식을 보자.

ORDER BY 특정 컬럼1 [Asc|Desc],특정 컬럼2 [Asc|Desc],...

이며 제일 먼저 특정 컬럼1이 정렬이 되고 그 정렬 속에서 특정 컬럼2가 정렬이 된다. 그럼 직접 실습을 해보자.
SELECT * FROM group_test ORDER BY id ASC,number DESC, string ASC;

이렇게 ORDER BY 절을 이용하면 원하는 정렬이 가능하다.
여기서 한가지 주의할 점이 있는데 ORDER BY 절을 먼저 쓰고 GROUP BY절을 나중에 쓰면 오류가 발생하므로
반드시 GROUP BY와 ORDER BY 절을 같이 쓸 경우에는 GROUP BY절을 먼저 쓰고 뒤에 order by절을 쓰길 바란다.

일반적인 SQL문 순서

SELECT 컬럼1,컬럼2,... FROM 테이블1,테이브2,.. WHERE 조건문 GROUP BY 그룹화 할 컬럼1,컬럼2,...
ORDER BY 정렬할 컬럼1 [ASC|DESC],컬럼2,[ASC|DESC],...


서브쿼리(SubQuery?)에 대해서

서브쿼리(SubQuery)에 대해서
드디여 여기까지 왔다. 이 부분은 조인(Join)처럼 매우 중요하므로 꼭 마스터 하길 바란다.
이젠 조인(Join)에 대해서 어느 정도 이해가 가는가? 그렇다면 조인(join)만큼이나 중요하고
복잡한 서브쿼리(SubQuery)에 대해서 알아보자. 먼저 알아 보기 전에 다음과 같이 테이블을 만든다.
참고로 MySQL에서는 서브쿼리(SubQuery)를 지원하지 않는다. mysql.com 에 따르면 4.1버전부터 지원을 한다고
하며 우리는 일단 PostgreSQL를 사용하여 테스트를 할 것이다.

CREATE TABLE Student(
ID varchar(50),
Name varchar(50),
Class int
);

CREATE TABLE Score(
ID varchar(50),
Score int
);

위와 같이 테이블을 만들었다면 [그림17]과 [그림18]처럼 열를 추가한다.

SELECT * FROM Student;

SELECT * FROM Score;

추가 하였다면 이제 본격적으로 서브쿼리(SubQuery)에 대하여 공부하여 보자.

서브쿼리(SubQuery)는 SELECT, INSERT, UPDATE, DELETE 문이나 다른 서브쿼리(SubQuery) 내부에 중첩된 SELECT 쿼리이다.
서브쿼리(SubQuery)는 식이 허용되는 모든 위치에서 사용할 수 있고 ()로 묶어서 사용된다.
다음 예제에서 서브쿼리(SubQuery)는 SELECT 문에서 Score 라는 컬럼으로 사용된다.

SELECT a.Name, a.Class,
(SELECT Score FROM Math AS b
WHERE b.ID = a.ID) AS Score
FROM Student AS a

이것은 학생들의 이름과 반, 점수을 뽑아내는 서브쿼리(SubQuery)문이다.
대부분의 서브쿼리(SubQuery)문은 조인(join)으로 나타낼 수 가 있다.
또한 서브쿼리(SubQuery)가 포함된 문장이나 의미상 동일한 문장에서는 서브쿼리(SubQuery)나 조인(Join)이나 성능면에서는 차이가 없다.
이제 위의 문장을 테스트 해보자.

위에서 하위 쿼리(SubQuery)는 대부분이 조인(Join)으로 출력할 수 있다고 했다. 그럼 이젠 앞 강에서 배운 조인(Join)으로 출력해보자.

SELECT a.Name,a.Class,b.Score FROM Student a INNER JOIN Score b ON b.ID = a.ID;

내용을 보면 똑같은 결과를 출력하였다. 서브쿼리(SubQuery)에 대해서 위에서 설명할 때 식이 허용하는한 어디에서든 사용할 수 있다고 했다. 그럼 이제는 서브쿼리(SubQuery)문을 현재의 위치가 아닌 다른 위치에서 사용하여 보자. 기본적인 형식은 다음과 같다.

SELECT 컬럼1,컬럼2,.....
FROM 테이블1 where 비교할 컬럼 [IN|NOT IN]
(select 비교할 컬럼 From 테이블2)
여기서 주목할 곳은 IN과 NOT IN이다. IN은 =와 같은 뜻이며 NOT IN은 !=과 같은 뜻으로 사용된다. 그럼 간단한 실습을 해보자.

SELECT ID,Name,Class FROM Student WHERE ID IN (SELECT ID FROM Score);
SELECT ID,Name,Class FROM Student WHERE ID NOT IN (SELECT ID FROM Score);

이상과 같이 서브쿼리(SubQuery)에 대해서 살펴보았다. 앞으로 데이터 베이스를 사용하면서 서브쿼리(SubQuery)문과 조인(join)문은 성능향상을 위해서도 꼭 필요한 것들이므로 반드시 마스터하길 바란다. 참고로 MSSQL과 오라클 등 상업적인 데이터 베이스는 IN,NOT IN뿐만이 아니라 =,!=,<,>등을 서브쿼리(SubQuery)에서도 지원하고 있다.
이건 오픈 소스 데이터 베이스의 한계로 앞으로 이들 오픈 소스 데이터 베이스도 역시 지원을 할 것이다.

테이블,컬럼,구조를 알고 싶을때..

bin> mysqlshow [옵션] db_name table_name cloumn_name
[옵션] -k , --key : 테이블 키에 대한 정보도 출력해준다..
ex)mysqlshow testdb table_test
mysqlshow -k testdb table_test

데이터베이스 백업하기

1. 테이블 백업할때..
mysqldump -u[아이디] -p[비번] DB명 table명 > C:\파일명

bin> mysqldump db_name table_name > file

ex) mysqldump testdb table_test > test.sql

백업한 화일 복구
mysql -u[아이디] -p[비번] DB명 < C:\파일명

bin> mysql -u[id] -p db_name < file

ex) mysql -u root -p testdb < test.sql

2.데이터베이스 전체 백업할때..
bin> mysqldump db_name > test.sql

복구...
bin> mysqladmin drop db_name
bin> mysqladmin create db_name
bin> mysql db_name < test.sql

3. 유용한 query
mysql> select database(); <-- 현재 사용하고 있는 데이터베이스이름
mysql> select user(); <-- 현재 사용자의 이름을 나타내는 함수

mysql> ifnull(column, x) : column이 널이면 x로 바꾸라

ex)select ifnull(test, 1) from test

mysql> isnull(column) : column이 널이면 0, 아니면 1

날짜 함수들...
mysql> select name, data_format(day, "%Y-%m-%d") from test

dayofweek(date)
weekday(date)
dayofmonth(date)
dayofyear(date)
month(date)
dayname(date)
monthname(date)
week(date)
week(date,first) : first는 한주의 시작이 일요일인자 월요일인지 나타
냅니다. 0은 일요일이 한 주의 시작임을 알리고, 1은 월요일이 한주
의 시작임을 알립니다.
year(date)
yearweek(date)
yearweek(date,first): yearweek와 동일하고 first는 week에서의
first와 동일합니다.
hour(date)
minute(date)
second(date)

날짜 더하고 빼는 함수
date_add(date, interval expr type)
date_sub(date, interval expr type)

ex)
mysql> select adddate('1997-12-31 23:59:59', interval 1 day)
result ==> 1998-01-01 23:59:59

mysql> select date_sub('1997-01-02', interval 31 day)
result ==> 1997-12-02

mysql> select date_add('1998-01-01 00:00:00', interval '-1 10' day_hour)
result ==> 1997-12-30 14:00:00

type
second, minute, hour, day, month, year
minute_second : "분:초"
hour_minute : "시:분"
day_hour : "날 시"
year_month : "년-달"
hour_second : "시:분:초"
day_minute : "날 시:초"
day_second : "날 시:분:초"