¸®´ª½º, ÀÚ·á½Ç, ¼º°æ°Ë»ö, Ãß¾ïÀǰÔÀÓ, °íÀü°ÔÀÓ, ¿À¶ô½Ç°ÔÀÓ, rootman, http://www.rootman.co.kr
* 52.91.176.251 *
| Home | Profile | Linux | ÀÚ·á½Ç | zabbix | Mysql 5.6 | °¶·¯¸® | ¼º°æ°Ë»ö | ÇØÇǴϽº | ÀÚÀ¯°Ô½ÃÆÇ | °Ô½Ã¹°°Ë»ö | L | O | R |    

 
[mysql] [mysql] mysqlÀÇ ´Ù¾çÇÑ query ¹®
 ÀÛ¼ºÀÚ : rootman
Date : 2008-02-18 21:55  |  Hit : 9,364  
¾È³çÇϼ¼¿ä. ¿î¿µÀÚÀÔ´Ï´Ù.
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; <br/>

------------------------------------------------------------------------------
½Ã½ºÅÛ ÇÔ¼ö - ÇöÀç Á¢¼Ó 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 |
+------------------------------------------+



 
 

Total. 645
¹øÈ£ ºÐ·ù Á¦¸ñ ÀÛ¼ºÀÚ µî·ÏÀÏ Á¶È¸¼ö
645 ±âÃʰ­Á NFS server unable to open connection to tcp6/udp6 networks rootman 09-27 2712
644 php [php] mssql ms-sql ÇÑ±Û º¯È¯ ²ôÀûÀÓ.. Á¤¸® rootman 03-29 3848
643 ±âÃʰ­Á java vim ¿¡¼­ Àڹ٠ȯ°æ ¸¸µé±â rootman 02-16 2870
642 »èÁ¦¿¹Á¤ [xencenter] xenserver tool ¼³Ä¡ rootman 08-24 4006
641 mysql [mysql] Creating a File-Per-Table Tablespace Outside the Dat¡¦ rootman 08-03 4187
640 ±âÃʰ­Á Centos Xwindow ¼³Ä¡ rootman 06-16 5097
639 php [php] highlight ÇÔ¼ö rootman 04-01 4958
638 qmail [qmail] qmail-scanner¿¡ filtering µÈ À̸ÞÀÏ Ã³¸® rootman 02-14 5413
637 Shell [³¯Â¥] awk¸¦ ÀÌ¿ëÇÏ¿© unixtimeÀÇ ½Ã°£ Â÷ ±¸Çϱâ rootman 01-27 6447
636 ±âÃʰ­Á centos¿¡ ±×³ð(gnome) ¼³Ä¡Çϱâ rootman 01-10 6379
635 ½©(awk) [awk] shell º¯¼ö »ç¿ëÇϱâ rootman 12-27 5918
634 ±âÃʰ­Á ¼­¹ö ij½Ã ¸Þ¸ð¸® ÃʱâÈ­Çϱâ (drop_caches) rootman 12-11 7329
633 windows tips ±×¸®µå Á¦°Å ¹èÄ¡ ½ºÅ©¸³Æ® rootman 11-28 6576
632 ±âÃʰ­Á ÆÐ½º¿öµå lockgin control rootman 09-06 4
631 windows tips win7 ÀÚµ¿ ·Î±×ÀÎ ¼³Á¤Çϱâ rootman 08-18 6918
 1  2  3  4  5  6  7  8  9  10    
AND OR