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