리눅스, 자료실, 성경검색, 추억의게임, 고전게임, 오락실게임, rootman, http://www.rootman.co.kr
* 54.224.118.247 *
| Home | Profile | Linux | 자료실 | zabbix | Mysql 5.6 | 갤러리 | 성경검색 | 해피니스 | 자유게시판 | 게시물검색 | L | O | R |    

 
[mysql] [mysql] mysql의 다양한 query 문
 작성자 : rootman
Date : 2008-02-18 21:55  |  Hit : 9,071  
안녕하세요. 운영자입니다.
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 2212
644 php [php] mssql ms-sql 한글 변환 끄적임.. 정리 rootman 03-29 3244
643 기초강좌 java vim 에서 자바 환경 만들기 rootman 02-16 2436
642 삭제예정 [xencenter] xenserver tool 설치 rootman 08-24 3405
641 mysql [mysql] Creating a File-Per-Table Tablespace Outside the Dat… rootman 08-03 3587
640 기초강좌 Centos Xwindow 설치 rootman 06-16 4449
639 php [php] highlight 함수 rootman 04-01 4309
638 qmail [qmail] qmail-scanner에 filtering 된 이메일 처리 rootman 02-14 4753
637 Shell [날짜] awk를 이용하여 unixtime의 시간 차 구하기 rootman 01-27 5626
636 기초강좌 centos에 그놈(gnome) 설치하기 rootman 01-10 5549
635 쉘(awk) [awk] shell 변수 사용하기 rootman 12-27 5257
634 기초강좌 서버 캐시 메모리 초기화하기 (drop_caches) rootman 12-11 6223
633 windows tips 그리드 제거 배치 스크립트 rootman 11-28 5828
632 기초강좌 패스워드 lockgin control rootman 09-06 4
631 windows tips win7 자동 로그인 설정하기 rootman 08-18 5650
 1  2  3  4  5  6  7  8  9  10    
AND OR