[MySQL] 다양한 Query 문

------------------------------------------------------------------------------
group 함수
------------------------------------------------------------------------------
mysql> select count(*) from DMI_Info;
+----------+
| count(*) |
+----------+
|     4188 | 
+----------+
1 row in set (0.00 sec)


------------------------------------------------------------------------------
having 함수
------------------------------------------------------------------------------
mysql> select CheckDate, count(*) from DMI_Info group by CheckDate having count(*) > 100;
+-----------+----------+
| CheckDate | count(*) |
+-----------+----------+
| 20080215  |     4091 | 
+-----------+----------+
1 row in set (0.05 sec)


------------------------------------------------------------------------------
avg 함수
------------------------------------------------------------------------------
mysql> select avg(Checkdate) from DMI_Info;
+-----------------+
| avg(Checkdate)  |
+-----------------+
| 20080214.935769 | 
+-----------------+
1 row in set (0.04 sec)


------------------------------------------------------------------------------
min 함수
------------------------------------------------------------------------------
mysql> select min(CheckDate) from DMI_Info;
+----------------+
| min(CheckDate) |
+----------------+
| 20080106       | 
+----------------+
1 row in set (0.04 sec)


------------------------------------------------------------------------------
sum 함수
------------------------------------------------------------------------------
mysql> select sum(CheckDate) from DMI_Info;
+----------------+
| sum(CheckDate) |
+----------------+
|    84095940151 | 
+----------------+
1 row in set (0.04 sec)


------------------------------------------------------------------------------
sum 함수
------------------------------------------------------------------------------
mysql> select min(CheckDate) from DMI_Info;
+----------------+
| min(CheckDate) |
+----------------+
| 20080106       | 
+----------------+
1 row in set (0.04 sec)


------------------------------------------------------------------------------
ASCII(str) 함수 - 아스키 코드 값 리턴
------------------------------------------------------------------------------
mysql> select ascii('a'), ascii('A');
+------------+------------+
| ascii('a') | ascii('A') |
+------------+------------+
|         97 |         65 | 
+------------+------------+
1 row in set (0.02 sec)


------------------------------------------------------------------------------
length 함수 - 길이 체크
------------------------------------------------------------------------------
mysql> select CheckDate, length(CheckDate) AS "길이", count(*) from DMI_Info group by CheckDate;
+-----------+------+----------+
| CheckDate | 길이 | count(*) |
+-----------+------+----------+
| 20080106  |    8 |        5 | 
| 20080215  |    8 |     4091 | 
| 20080218  |    8 |       92 | 
+-----------+------+----------+
3 rows in set (0.05 sec)


------------------------------------------------------------------------------
left 함수 - 좌측으로부터 데이터 자름
------------------------------------------------------------------------------
mysql> select left(CheckDate, 4), count(*) from DMI_Info group by CheckDate;
+--------------------+----------+
| left(CheckDate, 4) | count(*) |
+--------------------+----------+
| 2008               |        5 | 
| 2008               |     4091 | 
| 2008               |       92 | 
+--------------------+----------+
3 rows in set (0.06 sec)


------------------------------------------------------------------------------
right 함수 - 좌측으로부터 데이터 자름
------------------------------------------------------------------------------
mysql> select right(CheckDate, 4) as "Month/Day", count(*) from DMI_Info group by CheckDate;
+-----------+----------+
| Month/Day | count(*) |
+-----------+----------+
| 0106      |        5 | 
| 0215      |     4091 | 
| 0218      |       92 | 
+-----------+----------+
3 rows in set (0.05 sec)


------------------------------------------------------------------------------
substring 함수 - 원하는 위치에서 길이 자름
------------------------------------------------------------------------------
mysql> select substring(Checkdate, 1, 6) as "Year", count(*) from DMI_Info group by Checkdate;
+--------+----------+
| Year   | count(*) |
+--------+----------+
| 200801 |        5 | 
| 200802 |     4091 | 
| 200802 |       92 | 
+--------+----------+
3 rows in set (0.05 sec)


------------------------------------------------------------------------------
LTRIM 함수 - 좌측 공백 없앰
------------------------------------------------------------------------------
mysql> select LTRIM(CheckDate) from DMI_Info limit 5;
+------------------+
| LTRIM(CheckDate) |
+------------------+
| 20080215         | 
| 20080215         | 
| 20080215         | 
| 20080215         | 
| 20080215         | 
+------------------+
5 rows in set (0.00 sec)


------------------------------------------------------------------------------
RTRIM 함수 - 우측 공백 없앰
------------------------------------------------------------------------------
mysql> select RTRIM(CheckDate) from DMI_Info limit 5;
+------------------+
| RTRIM(CheckDate) |
+------------------+
| 20080215         | 
| 20080215         | 
| 20080215         | 
| 20080215         | 
| 20080215         | 
+------------------+
5 rows in set (0.00 sec)


------------------------------------------------------------------------------
TRIM 함수 - 좌측/우측 공백 없앰
------------------------------------------------------------------------------
mysql> select TRIM(CheckDate) from DMI_Info limit 5;
+-----------------+
| TRIM(CheckDate) |
+-----------------+
| 20080215        | 
| 20080215        | 
| 20080215        | 
| 20080215        | 
| 20080215        | 
+-----------------+
5 rows in set (0.00 sec)


------------------------------------------------------------------------------
replace 함수 - string 교체
------------------------------------------------------------------------------
mysql> update DMI_Info set CheckDate = replace(CheckDate, '20080106', '20080218');
Query OK, 5 rows affected (0.07 sec)
Rows matched: 4188  Changed: 5  Warnings: 0


------------------------------------------------------------------------------
reverse 함수 - string의 순서를 교체
------------------------------------------------------------------------------
mysql> select reverse(CheckDate) from DMI_Info group by CheckDate;
+--------------------+
| reverse(CheckDate) |
+--------------------+
| 51208002           | 
| 81208002           | 
+--------------------+
2 rows in set (0.07 sec)


------------------------------------------------------------------------------
now 함수 - 년-월-일 시간
------------------------------------------------------------------------------
mysql> select year(now()), month(now()), dayofmonth(now()), now();
+-------------+--------------+-------------------+---------------------+
| year(now()) | month(now()) | dayofmonth(now()) | now()               |
+-------------+--------------+-------------------+---------------------+
|        2008 |            2 |                18 | 2008-02-18 21:45:58 | 
+-------------+--------------+-------------------+---------------------+
1 row in set (0.00 sec)

mysql> select curdate(), curtime(), now();    
+------------+-----------+---------------------+
| curdate()  | curtime() | now()               |
+------------+-----------+---------------------+
| 2008-02-18 | 21:47:35  | 2008-02-18 21:47:35 | 
+------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(), unix_timestamp('2008-02-18 21:38:50'), from_unixtime(1203338932), from_unixtime(1203338932,'%H:%i:%s');
+------------------+---------------------------------------+---------------------------+--------------------------------------+
| unix_timestamp() | unix_timestamp('2008-02-18 21:38:50') | from_unixtime(1203338932) | from_unixtime(1203338932,'%H:%i:%s') |
+------------------+---------------------------------------+---------------------------+--------------------------------------+
|       1203339010 |                            1203338330 | 2008-02-18 21:48:52       | 21:48:52                             | 
+------------------+---------------------------------------+---------------------------+--------------------------------------+
1 row in set (0.00 sec)


------------------------------------------------------------------------------
if 구문 제어 함수 - if(Case, 'A', 'B') 
------------------------------------------------------------------------------
mysql> select CheckDate, if(CheckDate >20080217, '유효날짜', '만기') AS "CheckJumsu", count(*) from DMI_Info group by CheckDate;
+-----------+------------+----------+
| CheckDate | CheckJumsu | count(*) |
+-----------+------------+----------+
| 20080215  | 만기       |     4091 | 
| 20080218  | 유효날짜   |       97 | 
+-----------+------------+----------+
2 rows in set (0.06 sec)


mysql> select CheckDate, if((CheckDate >20080217 || CheckDate < 20090000), '유효날짜', '만기') AS "CheckJumsu", count(*) from DMI_Info group by CheckDate;


------------------------------------------------------------------------------
시스템 함수 - 현재 접속 DB, user 
------------------------------------------------------------------------------
mysql> select version(), database(), user(), session_user();
+-----------+------------+------------------+------------------+
| version() | database() | user()           | session_user()   |
+-----------+------------+------------------+------------------+
| 4.0.27    | mydb       | root@11.11.11.11 | root@11.11.11.11 | 
+-----------+------------+------------------+------------------+
1 row in set (0.00 sec)


------------------------------------------------------------------------------
문자열 함수 - 특정 값에서 구분자로 데이터 자름 
------------------------------------------------------------------------------
mysql> select SubString_Index(A.Host ,'.',1) from DMI_Info;



------------------------------------------------------------------------------
password, encrypt 함수 - 암호화 구현
------------------------------------------------------------------------------
mysql> select password("rootman"), encrypt("rootman");
+---------------------+--------------------+
| password("rootman") | encrypt("rootman") |
+---------------------+--------------------+
| 2c99fd460bafb776    | rD0T9YxN2AghM      | 
+---------------------+--------------------+
1 row in set (0.00 sec)


------------------------------------------------------------------------------
md5 함수 - md5
------------------------------------------------------------------------------
mysql> select md5('rootman');
+----------------------------------+
| md5('rootman')                   |
+----------------------------------+
| f170c4421a73c03c903c1f83cdcf82dc | 
+----------------------------------+
1 row in set (0.02 sec)


------------------------------------------------------------------------------
benchmark 함수 - 벤치마크
------------------------------------------------------------------------------
mysql> select benchmark(10000000, password('rootman'));
+------------------------------------------+
| benchmark(10000000, password('rootman')) |
+------------------------------------------+
|                                        0 | 
+------------------------------------------+