3. Mysql 함수(3) -바닥부터 배우는 Mysql -

3. Mysql 함수(3)     -바닥부터 배우는 Mysql -

ABS(n), SIGN(n), MOD(n,m), FLOOR(n), CEILING(n), ROUND(n, D)

TRUNCATE(n, D), POWER(X, y)또는 POW(X, y), RAND()

IFNULL(expr1,expr2)

NULLIF(expr1,expr2)

IF(expr1,expr2,expr3)

CASE

GROUP BY

COUNT(*), SUM(), AVG(), MAX(), MIN()

HAVING

USER(), VERSION(), PASSWORD(str), DATABASE()

 

1. 숫자 함수:

ABS(n) : 절대 값 출력.

SIGN(n) : n의 값의 부호 값을 돌려 준다.(-1 : 음수, 0 : 0, 1 : 양수)

MOD(n,m) : n/m의 나머지. (연산자 %와 같음)

FLOOR(n) : 실수 n 값의 소수점 이하의 값을 버림을 한 정수 값.

CEILING(n) : 실수 n 값의 소수점 이하의 값을 올림을 한 정수 값.

ROUND(n, D) : 실수 n 값의 소수 자리 D번째에서 반올림 한 값.(D는 양수, 0 또는 음수 가능)

TRUNCATE(n, D) : 실수 n 값의 소수 자리 D번째에서 버림.

POWER(X, y)또는 POW(X, y) : X의 y승. (=Xy)

RAND() : 0에서 1 사이의 난수를 발생.

예 3-1

SELECT abs(-8), abs(6), sign(-23), sign(0), sign(16), mod(15,6);

결과

abs(-8)	abs(6)	sign(-23)	sign(0)	sign(16)	mod(15,6)
8	6	-1	0	1	3

 

SELECT floor(3.14159), ceiling(1.4142), round(1.732), round(6.302);

결과

floor(3.14159)	ceiling(1.4142)	round(1.732,2)	round(1.732,3)
3	2	1.73	1.732

 

SELECT power(2,3), power(2,4), rand();

결과

power(2,3)	power(2,4)	rand()
8	16	0.733140295122881

 

2. 제어 함수(Control flow Function):

IFNULL(expr1,expr2) : expr1 값이 NULL이면 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 값을 출력.

예 3-2

SELECT IFNULL(NULL,1),IFNULL(2,3),IF( 5>6, 1, 0 ),IF( 5<6, 1, 0);

결과

IFNULL(NULL,1)	IFNULL(2,3)	IF( 5>6, 1, 0 )	IF( 5<6, 1, 0)
1	2	0	1

 

SELECT CASE 2 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END AS answer;

결과

answer

two

 

GROUP BY:

GROUP BY절은 집계 함수와 함께 쓰여 특정 컬럼의 값들을 하나로 묶는 역할을 한다.

집계 함수:

꼭 GROUP BY와 함께 쓸 필요는 없다.

COUNT(필드명) - NULL 값이 아닌 레코드 수를 구한다.

SUM(필드명) - 필드명의 합계를 구한다.

AVG(필드명) - 각각의 그룹 안에서 필드명의 평균값을 구한다.

MAX(필드명) - 최대 값을 구한다.

MIN(필드명) - 최소 값을 구한다.

 

예제용 데이터베이스 테이블 'Orders’

ID	Name	Item	ItemGroup	Price	Qty
1	James	onion	veg	350	5
2	David	Beef	meat	5000	2
3	Hannah	pork	meat	3000	3
4	Julia	pork	meat	3000	3
5	Richird	radish	veg	500	1
6	James	chicken	meat	2000	4
7	John	chicken	meat	2000	4
8	Adam	apple	fruits	300	5
9	John	banana	fruits	400	6
10	John	coke	drink	150	10
11	Richird	milk	drink	300	2
12	James	beef	meat	5000	3
13	David	mushroom	veg	300	1
14	Adam	beans	veg	500	2
15	John	apple	fruits	300	6
16	Kim	onion	veg	350	2

 

Name이 James인 사람이 구입한 아이템의 Total Price를 구하려면

예 3-3

SELECT sum(number*Qty) FROM Orders WHERE Name = 'James';

결과

Name	Total_Price
James	24750

 

각 사람별로 Total Price를 구하려고 한다면 GROUP BY를 이용한다.

예 3-4

SELECT Name, SUM(Price*Qty) AS Total_Price FROM Orders GROUP BY Name;

결과

Name	Total_Price
Adam	2500
David	10300
Hannah	9000
James	24750
John	13700
Julia	9000
Kim	700
Richird	1100

 

집계 함수의 사용.

예 3-5

SELECT count(*), vg(Qty), ax(Price), in(Price), um(Price) FROM Orders;

결과

count(*)	avg(Qty)	max(Price)	min(Price)	sum(Price)
16	3.6875	5000	150	23450

 

ORDER BY 절을 이용해서 결과 값을 정렬.

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

먼저 특정 컬럼1이 정렬이 되고 그 정렬 속에서 특정 컬럼2가 정렬이 됨.

GROUP BY와 ORDER BY 절을 같이 쓸 경우에는 GROUP BY절을 먼저 쓰고 나중에 ORDER BY W절을 써야 에러가 안 생긴다.

예 3-6

SELECT * FROM group_test ORDER BY id ASC,number DESC, string ASC;

결과

ID	Name	Item	ItemGroup	Price	Qty
14	Adam	beans	veg	500	2
8	Adam	apple	fruits	300	5
2	David	Beef	meat	5000	2
13	David	mushroom	veg	300	1
3	Hannah	pork	meat	3000	3
12	James	beef	meat	5000	3
6	James	chicken	meat	2000	4
1	James	onion	veg	350	5
7	John	chicken	meat	2000	4
9	John	banana	fruits	400	6
15	John	apple	fruits	300	6
10	John	coke	drink	150	10
4	Julia	pork	meat	3000	3
16	Kim	onion	veg	350	2
5	Richird	radish	veg	500	1
11	Richird	milk	drink	300	2

 

HAVING 절:

합계 금액이 10000 이상인 항목만을 집계하려고 할 때 아래와 같이 WHERE절을 쓰면 안 된다.

SELECT Name, SUM(Price*Qty) AS Total_Price FROM Orders

WHERE SUM(Price*Qty) >= 10000

GROUP BY Name;

집계 함수를 조건으로 사용할 경우 WHERE 절을 사용하면 에러가 나서 안 되고 HAVING 절을 사용한다.

HAVING 절은 반드시 GROUP BY 절 다음에 나와야 한다.

예 3-7

SELECT Name, SUM(Price*Qty) AS Total_Price FROM Orders

GROUP BY Name

HAVING SUM(Price*Qty) >= 10000

ORDER BY SUM(Price*Qty);

결과

Name	Total_Price
David	10300
John	13700
James	24750

 

기타 함수들:

USER() : 현재 mysql에 접속 중인 사용자 이름을 보여준다.

VERSION( ) : mysql의 버전을 보여준다.

PASSWORD(str) : str을 암호화해서 저장. 사용자 인증에 많이 이용.

예 3-8

SELECT USER(), VERSION(), PASSWORD('SomeText');

결과

USER()	VERSION()	PASSWORD('SomeText')
sqltest_user@localhost	5.1.73	*FB3376EBFE6B22084FC2B0482768DF6F2D122B33

 

SELECT DATABASE(): 현재 사용하고 있는 데이터베이스이름

SELECT USER(): 현재 사용자의 이름.

예 3-9

SELECT DATABASE(), USER();

결과

DATABASE()	USER()
sqltest_tmp_db	sqltest_user@localhost