E-mail :
대용량 DB 관리 (MySQL) :: 분류없음
MySQL 롤백이 안된다~~(스토리지 엔진에 따라 다르다네;;)
SELECT
가능한 명시적으로 쿼리해라~~
LIMIT을 사용해서 스크롤해라
mysql> SELECT * FROM City ORDER BY Id LIMIT 0,2;
+----+----------+---------+----------+------------+
| Id | Name | Country | District | Population |
+----+----------+---------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
+----+----------+---------+----------+------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM City ORDER BY Id LIMIT 2,2;
+----+----------------+---------+----------+------------+
| Id | Name | Country | District | Population |
+----+----------------+---------+----------+------------+
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
+----+----------------+---------+----------+------------+
2 rows in set (0.00 sec)
총 레코드 개수 알기
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM City ORDERBY BY id LIMIT 0, 2;
mysql> SELECT FOUND_ROWS() AS TOTAL;
+-------+
| TOTAL |
+-------+
| 4079 |
+-------+
1 row in set (0.00 sec)
LIMIT은 네트웍으로 가저오는 양만 조절한다.
어짜피 전체 연산을 하기때문에 LIMIT을 써도 전체 Row를 아는데는
지장없고 더 빠른 장점이 있다.
DISTINCT : Oracle할때는 성능때문에 쓰지 말라고 했었는데-_-;;;
AS : 컬럼명 지정
mysql> SELECT Name, Population,
-> Population * 1.20 AS NewPop
-> FROM Country LIMIT 5;
+----------------------+------------+-------------+
| Name | Population | NewPop |
+----------------------+------------+-------------+
| Afghanistan | 22720000 | 27264000.00 |
| Netherlands | 15864000 | 19036800.00 |
| Netherlands Antilles | 217000 | 260400.00 |
| Albania | 3401200 | 4081440.00 |
| Algeria | 31471000 | 37765200.00 |
+----------------------+------------+-------------+
5 rows in set (0.00 sec)
WHERE
와일드카드
'_' : 임의의 캐릭터 하나 (?)
'%' : 임의의 문자열 (*)
ORDER BY & GROUP BY
GROUP BY는 ORDER BY 앞에 온다
둘 다 AS로 정의된 필드를 사용할 수 있음
둘 다 여러 개의 필드를 사용할 수 있음
GROUP BY와 ORDER BY에 서로 다른 필드를 사용 가능
GROUP BY를 사용할 경우
- 반드시 해당 필드를 SELECT해야 한다
- 더 추려내기 위하여 HAVING 조건절을 사용할 수 있다
ORDER BY를 사용할 경우
- 역차순 정렬을 원하는 경우 DESC를 사용
- 칼럼 번호로 구분 가능
- ORDER BY를 위해 반드시 해당 필드를 SELECT할 필요는 없음
SUM, AVG, COUNT, MAX, MIN, STD(표준편차)
GROUP_CONCAT() : 그룹에서 연결된 문자형 결과값을 얻는다.
mysql> SELECT continent, GROUP_CONCAT(distinct region)
-> FROM country GROUP BY continent;
+---------------+-----------------------------------------------------+
| continent | GROUP_CONCAT(distinct region) |
+---------------+-----------------------------------------------------+
| Asia | Eastern Asia,Middle East,Southeast Asia,Southern a… |
| Europe | Southern Europe,Baltic Countries,Eastern Europe,Nor…|
| North America | Central America,Caribbean,North America |
| Africa | Central Africa,Eastern Africa,Western Africa,Northe…|
| Oceania | Australia and New Zealand,Melanesia,Polynesia,Micr… |
| Antarctica | Antarctica |
| South America | South America |
+---------------+-----------------------------------------------------+
7 rows in set (0.00 sec)
GROUP BY … WITH ROLLUP은 GROUP BY에 사용된
칼럼의 데이터를 합계하여 요약된 데이터를 만든다.
- 요약 row는 GROUP BY의 가장 좌측 칼럼이 바뀔 때 마다
생성된다
- NULL은 어떠한 칼럼이 요약 row를 만드는데 사용되었는지를
표시하기 위해 사용된다
- 요약 row는 쿼리의 수행이 모두 완료 된 다음에 추가되므로
HAVING 또는 유사한 방법으로 참조할 수 없다
mysql> SELECT continent, name, SUM(Population)
-> FROM Country
-> GROUP BY continent, name
-> WITH ROLLUP;
+---------------+------------------------+-----------------+
| continent | name | SUM(Population) |
+---------------+------------------------+-----------------+
| Asia | Afghanistan | 22720000 |
| Asia | Armenia | 3520000 |
| Asia | Azerbaijan | 7734000 |
| Asia | Bahrain | 617000 |
| Asia | Bangladesh | 129155000 |
| … | … | … |
| South America | Venezuela | 24170000 |
| South America | NULL | 345780000 |
| NULL | NULL | 6078749450 |
+---------------+------------------------+-----------------+
INSERT
mysql> INSERT INTO Country (Code, Name, Continent)
-> VALUES(‘FIN’, ‘Finland’, ‘Europe’),
-> (‘SWE’, ‘Sweden’, ‘Europe’);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
모든 필드에 값을 입력하면 필드 리스트는 필요 없다.
여러개의 레코드를 한번에 입력할 수 있다.
AUTO_INCREMENT 필드에는 NULL이나 0값을 주면 안된다.
INSERT INTO SELECT
mysql> INSERT INTO CountryCopy
-> SELECT * FROM Country;
Query OK, 239 rows affected (0.03 sec)
Records: 239 Duplicates: 0 Warnings: 0
CREATE TABLE … SELECT와 비슷하나 테이블 정의를 만들지 않는다
SELECT는 SELECT구문에 맞는 것이면 어떠한 것이든
가능하며 칼럼 타입은 필요하다면 자동으로 변환된다
UPDATE
mysql> UPDATE CountryCopy
-> SET HeadOfState = 'Wizard'
-> WHERE Code = 'OZ';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
4.0.2 버전 이후로 JOIN조건을 주어 한꺼번에 여러 테이블을 업데이트 할 수 있다
한번에 여러 필드를 SET에 설정할 수 있다
SET 할 때 값이 아닌 필드가 포함된 표현을 사용할 수 있으며
필드 그 자체를 참조하는 표현을 사용할 수 있다
LIMIT이나 ORDER BY를 사용하여 영향을 받게 될 row를 조정할 수 있다
DELETE
mysql> DELETE FROM CountryCopy
-> WHERE code = 'VAL';
Query OK, 0 rows affected (0.00 sec)
4.0버전 이후로 JOIN을 사용하여 여러 개의 테이블에서 한번에 DELETE할 수 있다
UPDATE와 마찬가지로 LIMIT을 사용할 수 있다
4.0버전 이후로 ORDER BY역시 사용할 수 있다
TRUNCATE TABLE
mysql> DELETE FROM CountryCopy
-> WHERE code = 'VAL';
Query OK, 0 rows affected (0.00 sec)
InnoDB 테이블에서는 TRUNCATE TABLE은 정확히
DELETE FROM을 WHERE절 없이 사용한것과 똑같다
그 외 스토리지 엔진에서는 TRUNCATE TABLE은
몇 가지 부분에서 DELETE와 다르다
- 테이블이 드롭되고 다시 생성됨
- 한번 수행되고 나면 되돌릴 수 없음
- MyISAM의 경우에도 AUTO_INCREMENT값이 리셋됨
- 테이블을 초기화하는 가장 빠른 방법
- 파일이 삭제되기 때문에 문제가 발생한 테이블에도 잘 작동함
MySQL 확장
REPLACE는 INSERT 또는 DELETE + INSERT이다
키의 유일성에 저촉될 경우 삭제를 먼저 하게 된다
표준 SQL이 아닌 MySQL 확장 SQL이다
코딩 시간과 응답시간을 향상시킨다
데이터베이스 테이블의 현재 row를 완전히 바꾸고자
할 경우 특별히 유용하다
REPLACE는 절대 현재 레코드를 업데이트 하는 것이 아니다.
빠진 필드는 기본값을 가지게 된다
만약 레코드가 여러 개의 유니크 키와 충돌한다면 해당
레코드는 모두 삭제되게 된다
mysql> ALTER TABLE CountryCopy ADD UNIQUE (Code);
Query OK, 239 rows affected (0.42 sec)
Records: 239 Duplicates: 0 Warnings: 0
mysql> REPLACE INTO CountryCopy
-> (Code, Name, Continent)
-> VALUE('OZ', 'Country of Oz', 'Oceania');
Query OK, 1 row affected (0.03 sec)
mysql> REPLACE INTO CountryCopy
-> (Code, Name, Continent)
-> VALUES('OZ', 'Republic of Oz', 'Europe');
Query OK, 2 rows affected (0.02 sec)
INSERT … ON DUPLICATE KEY UPDATE
ON DUPLICATE KEY UPDATE는 유니크 인덱스 또는 프라이머리 키와
중복되는 경우 인서트 대신 업데이트를 수행하게 된다
REPLACE와 다른 점은 이전 값이 유지되는 상태에서 지정된
칼럼의 값만이 변경된다는 것이다
mysql> INSERT INTO CountryCopy (Code, Name)
-> VALUES ('OZ', 'Republic of Oz')
-> ON DUPLICATE KEY UPDATE
-> Code = 'OZ', Name = 'Republic of Oz';
Query OK, 2 rows affected (0.03 sec)
함수
IN : 오라클하고 같다.
mysql> SELECT Code, Name, Population
-> FROM Country
-> WHERE Code IN('FIN', 'SWE', 'NOR', 'DNK', 'ISL');
+------+---------+------------+
| Code | Name | Population |
+------+---------+------------+
| DNK | Denmark | 5330000 |
| FIN | Finland | 5171300 |
| ISL | Iceland | 279000 |
| NOR | Norway | 4478500 |
| SWE | Sweden | 8861400 |
+------+---------+------------+
5 rows in set (0.00 sec)
IF: 그냥 IF
IF(Boolean_Test, ValueIfTrue, ValueIfFalse)
Boolean_Test는 반드시 True(0이 아닌 값) 거나 False
(0) 둘 중 하나를 판단할 수 있는 것이어야 한다
ValueIfTure는 문자열, 날짜 또는 숫자 일 수 있다
ValueIfFalse는 문자열, 날짜 또는 숫자 일 수 있다
메트릭스형 리포트의 칼럼을 만들 경우에 유용하다
mysql> SELECT Name, GNP/Population * 1000 AS PerCapita,
-> LifeExpectancy,
-> IF(LifeExpectancy < 70, '<<
-> WHERE Continent = 'Europe' AND GNP > 0
-> ORDER BY PerCapita LIMIT 10;
+------------------------+-----------+----------------+--------+
| Name | PerCapita | LifeExpectancy | Att |
+------------------------+-----------+----------------+--------+
| Moldova | 0.360502 | 64.5 | <<
| Ukraine | 0.835738 | 66.0 | <<
| Albania | 0.942314 | 71.6 | |
| Belarus | 1.339781 | 68.0 | <<
| Yugoslavia | 1.597744 | 72.4 | |
| Romania | 1.699272 | 69.9 | <<
10 rows in set (0.03 sec)
CASE WHEN
mysql> SELECT CASE Code
-> WHEN 'USA' THEN '1. USA'
-> WHEN 'DEU' THEN '2. Germany'
-> WHEN 'FRA' THEN '3. Fraance'
-> WHEN 'GBR' THEN '4. UK'
-> ELSE '5. Rest of World' END AS Area,
-> SUM(GNP), SUM(population), SUM(SurfaceArea)
-> FROM Country
-> GROUP BY Area;
+------------------+-------------+-----------------+------------------+
| Area | SUM(GNP) | SUM(population) | SUM(SurfaceArea) |
+------------------+-------------+-----------------+------------------+
| 1. USA | 8510700.00 | 278357000 | 9363520.00 |
| 2. Germany | 2133367.00 | 82164700 | 357022.00 |
| 3. Fraance | 1424285.00 | 59225700 | 551500.00 |
| 4. UK | 1378330.00 | 59623400 | 242900.00 |
| 5. Rest of World | 15914029.65 | 5638602400 | 138441364.90 |
+------------------+-------------+-----------------+------------------+
5 rows in set (0.00 sec)
mysql> SELECT CASE
-> WHEN Code='USA' THEN '1. USA'
-> WHEN Continent='Europe' THEN '2. Europe'
-> WHEN Continent='Asia' THEN '3. Asia'
-> WHEN Continent='North America' THEN '4. N. America'
-> ELSE '5. Rest of World' END AS Area,
-> SUM(GNP), SUM(Population), SUM(SurfaceArea)
-> FROM Country
-> GROUP BY Area;
+------------------+------------+-----------------+------------------+
| Area | SUM(GNP) | SUM(Population) | SUM(SurfaceArea) |
+------------------+------------+-----------------+------------------+
| 1. USA | 8510700.00 | 278357000 | 9363520.00 |
| 2. Europe | 9498865.00 | 730074600 | 23049133.90 |
| 3. Asia | 7655392.00 | 3705025700 | 31881005.00 |
| 4. N. America | 1177927.20 | 204636000 | 14850950.00 |
| 5. Rest of World | 2517827.45 | 1199879900 | 69811698.00 |
+------------------+------------+-----------------+------------------+
5 rows in set (0.00 sec)
수식 함수
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html
산수 관련
- +, -, *, /
- PI()와 같은 상수값
- ABS(), SIGN()
삼각함수 관련
- SIN(), COS(), TAN()
- ASIN(), ATAN()
- DEGREES(), RADIANS()
로그와 제곱관련
- EXP, LOG, SQRT
반올림 관련
- CEILING()은 올림
- FLOOR()는 내림
- ROUND()는 가장 가까운
정수 (0.5인 경우 OS별로 다름)
- TRUNCATE()는 소수점 이하 버림
문자열 함수
CONCAT은 문자열을 합치는데 사용. 1~n개의 파라메터를 가짐
SUBSTRING은 위치를 기준으로 문자열의 일부를 뽑아낸다
SUBSTRING_INDEX는 구분자를 기준으로 문자열의 일부를
뽑아낸다
INSRT 다른 문자열에서 매칭되는 문자열을 찾아낸다
REPLACE는 다른 문자열에서 해당 문자열을 찾아 변경한다
LENGTH는 문자열의 길이를 계산한다
LIKE는 문자열을 와일드카드를 이용하여 비교할 때 사용된다
TRIM은 문자열에서 공백을 제거한다
예)
SUBSTRING_INDEX(‘bjkim@nhncorp.com’,’@’,1) bjkim
SUBSTRING_INDEX(‘www.naver.com’, ‘.’, -2) naver.com
REVERSE(‘www.naver.com’) moc.revan,www
UPPER(‘naver’) NAVER
RPAD(‘bejay’, 5, ‘.’) bejay.....
CHAR(17, 121,83,81, 76) MySQL
LEFT(‘bejay kim’, 3) bej
RIGHT(‘bejay kim’, 3) kim
TRIM(‘Q’ FROM ‘QQQMySQL ABQQQ’) MySQL AB
Date Time 함수
NOW와 몇 가지 동의어들
날짜 계산을 위한 DATE_ADD와 DATE_SUB
날짜 사이의 간격을 계산하기 위한 DATEDIFF
날짜를 여러 가지 형식으로 보여주기 위한 DATE_FORMAT
EXTRACT를 사용하여 날짜 중 특정 부분만 추출
- DAY
- DAYOFMONTH
- DAYNAME
- WEEK
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2007-01-15 14:13:42 |
+---------------------+
1 row in set (0.03 sec)
mysql> SELECT NOW() + INTERVAL 2 DAY;
+------------------------+
| NOW() + INTERVAL 2 DAY |
+------------------------+
| 2007-01-17 14:13:57 |
+------------------------+
1 row in set (0.00 sec)
INTERVAL 숫자 키워드 형식
키워드에는 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
DATE_FORMAT으로 날자 포멧 바꾸기
mysql> SELECT webdate
-> DATE_FORMAT(webdate, ‘%M %e, %Y’) AS American,
-> DATE_FORMAT(webdate, ‘%e, %c %Y’) AS Swedish
-> FROM weblog
-> ORDER BY RAND() LIMIT 7;
+------------+-----------------------+------------+
| webdate | American | Swedish |
+------------+-----------------------+------------+
| 2001-10-11 | October 11, 2001 | 11.10 2001 |
| 2001-10-13 | October 13, 2001 | 13.10 2001 |
| 2001-10-03 | October 3, 2001 | 3.10 2001 |
| 2001-09-11 | September 11, 2001 | 11.9 2001 |
| 2001-08-31 | August 31, 2001 | 31.8 2001 |
| 2001-10-20 | October 20, 2001 | 20.10 2001 |
| 2001-08-29 | August 11, 2001 | 29.8 2001 |
+------------+-----------------------+------------+
7 rows in set (0.11 sec)
MAX-CONCAT 트릭
각 대륙별로 어떤 나라가 가장 인구가 많은가?
mysql> SELECT Continent,
-> SUBSTRING(MAX(CONCAT(LPAD(Population, 10, '0'),Name)), 11) AS Country,
-> 0 + LEFT(MAX(CONCAT(LPAD(Population, 10, '0'),Name)), 10) AS Population
-> FROM Country
-> GROUP BY Continent
-> ORDER BY Population DESC;
+---------------+----------------------------------------------+------------+
| Continent | Country | Population |
+---------------+----------------------------------------------+------------+
| Asia | China | 1277558000 |
| North America | United States | 278357000 |
| South America | Brazil | 170115000 |
| Europe | Russian Federation | 146934000 |
| Africa | Nigeria | 117081300 |
| Oceania | Australia | 18886000 |
| Antarctica | South Georgia and the South Sandwich Islands | 0 |
+---------------+----------------------------------------------+------------+
7 rows in set (0.02 sec)
IFNULL : NULL을 달리 보여주기
mysql> SELECT Name,
-> IFNULL(LifeExpectancy, 'N/A') AS LifeE
-> FROM Country
-> WHERE Continent = 'Europe'
-> AND Region LIKE 'Nor%';
+------------------------+-------+
| Name | LifeE |
+------------------------+-------+
| Faroe Islands | 78.4 |
| Svalbard and Jan Mayen | N/A |
| Iceland | 79.4 |
| Norway | 78.7 |
| Sweden | 79.6 |
| Finland | 77.4 |
| Denmark | 76.5 |
+------------------------+-------+
7 rows in set (0.00 sec)
JOIN
mysql> SELECT Name, Capital
-> FROM Country
-> WHERE Region LIKE 'Nordic%';
+------------------------+---------+
| Name | Capital |
+------------------------+---------+
| Faroe Islands | 901 |
| Svalbard and Jan Mayen | 938 |
| Iceland | 1449 |
| Norway | 2807 |
| Sweden | 3048 |
| Finland | 3236 |
| Denmark | 3315 |
+------------------------+---------+
7 rows in set (0.00 sec)
mysql> SELECT Id, Name
-> FROM City
-> WHERE Id IN(901,938,1449,
-> 2807, 3048,3236,3315);
+------+------------------------+
| Id | Name |
+------+------------------------+
| 901 | Trshavn |
| 938 | Longyearbyen |
| 1449 | Reykjavk |
| 2807 | Oslo |
| 3048 | Stockholm |
| 3236 | Helsinki [Helsingfors] |
| 3315 | Kbenhavn |
+------+------------------------+
7 rows in set (0.14 sec)
Cartesian Product
NordicCountries와 NordicCapitals이라는 테이블이 있다고
가정하고 두 테이블의 모든 Row를 합친다고 하면 아래와 같다
mysql> SELECT *
-> FROM NordicCountries, NordicCapitals;
+------------------------+---------+------+------------------------+
| Name | Capital | Id | Name |
+------------------------+---------+------+------------------------+
| Faroe Islands | 901 | 901 | Trshavn |
| Svalbard and Jan Mayen | 938 | 901 | Trshavn |
| Iceland | 1449 | 901 | Trshavn |
| Norway | 2807 | 901 | Trshavn |
| Sweden | 3048 | 901 | Trshavn |
| Finland | 3236 | 901 | Trshavn |
| Denmark | 3315 | 901 | Trshavn |
| Faroe Islands | 901 | 938 | Longyearbyen |
| Svalbard and Jan Mayen | 938 | 938 | Longyearbyen |
| … | … | … | … |
| Denmark | 3315 | 3315 | Kbenhavn |
+------------------------+---------+------+------------------------+
INNER JOIN
mysql> SELECT Country.Name AS Contry, Country.Capital, City.Id, City.Name AS City
-> FROM Country INNER JOIN City
-> ON Capital = ID
-> WHERE Region LIKE 'Nordic%';
+------------------------+---------+------+------------------------+
| Contry | Capital | Id | City |
+------------------------+---------+------+------------------------+
| Faroe Islands | 901 | 901 | Trshavn |
| Svalbard and Jan Mayen | 938 | 938 | Longyearbyen |
| Iceland | 1449 | 1449 | Reykjavk |
| Norway | 2807 | 2807 | Oslo |
| Sweden | 3048 | 3048 | Stockholm |
| Finland | 3236 | 3236 | Helsinki [Helsingfors] |
| Denmark | 3315 | 3315 | Kbenhavn |
+------------------------+---------+------+------------------------+
49 rows in set (0.00 sec)
LEFT JOIN
SELECT … FROM A LEFT JOIN B join_condition WHERE …
LEFT JOIN을 할 경우 WHERE절에 맞는 TABLE A의 모든
ROW는 JOIN을 위해 SELECT된다
- TABLE B에 매칭되는 값이 없는 row 역시 모두 포함된다
TABLE B에 매치되는 row가 없는 경우 NULL로 표시된다
서버에서는 TABLE A가 TABLE B보다 언제나 먼저 읽히게 된다
(오라클에서도 JOIN할때는 큰 테이블 순서대로 쓰라고 들었음..튜닝)
JOIN 조건은 아래와 같은 것들이 있다
- ON A.col1 = B.col2
- USING (col1, col2)
mysql> SELECT Country.Name, City.Name
-> FROM Country
-> LEFT JOIN City
-> ON Capital=ID
-> WHERE Continent = 'Oceania'
-> ORDER BY Country.Name;
+--------------------------------------+--------------------+
| Name | Name |
+--------------------------------------+--------------------+
| American Samoa | Fagatogo |
| Australia | Canberra |
| Christmas Island | Flying Fish Cove |
| Cocos (Keeling) Islands | West Island |
| Cook Islands | Avarua |
| Fiji Islands | Suva |
| … | … |
| United States Minor Outlying Islands | NULL |
| Vanuatu | Port-Vila |
| Wallis and Futuna | Mata-Utu |
+--------------------------------------+--------------------+
28 rows in set (0.17 sec)
참고 : 대용량 DB 솔루션 P.536~ http://blog.naver.com/kisow/20032827735