• 목록

안녕하세요. 운영자입니다.
mysql의 다양한 query문을 올립니다.

최근에, 프로그램 짜면서 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 | [email protected] | [email protected] |
+-----------+------------+------------------+------------------+
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 |
+------------------------------------------+