2021-12-02 18:33 작성
My SQL basic 메모
Table of contents
LIMIT
LIMIT은SELECT구문에서 반환하는 열의 수를 제한하는 용도로 사용된다.LIMIT은 하나 혹은 두 개의 argumnets를 받는다.- arguments의 값들은 모두
0혹은 양의 정수여야만 한다.
SELECT
select_list
FROM
table_name
LIMIT [offset,] row_count;
-- LIMIT의 변형
LIMIT row_count OFFSET offset
offset에 첫 번째로 반환되는 열의 초기값을 지정한다.offset의 첫 번째 열은1이 아닌0이다.(실제 열은 1부터 시작)row_count에 반환되는 열들의 최대 수를 지정한다.
하나의 argument를 할당한 채 LIMIT을 사용했을 때, MySQL은 이 argument를 반환될 최대 열의 수로 지정한다.(LIMIT row_count; = LIMIT 0 , row_count;)
LIMIT과 ORDER BY
SELECT
select_list
FROM
table_name
ORDER BY
sort_expression
LIMIT offset, row_count;
기본적으로 SELECT는 명시적이지 않은 순서로 열들을 반환한다. 따라서 SELECT에 LIMIT을 사용하게 되면 반환되는 열들은 예측할 수 없게 된다.
예측된 결과값을 반환하기 위해서는 ORDER BY와 함께 사용해야만 한다.
SELECT
customerNumber,
customerName,
creditLimit
FROM
customers;
output: LIMIT을 사용하지 않은 결과값이 다음과 같다고 하자.
| customerNumber | customerName | creditLimit |
|---|---|---|
| 103 | Atelier graphique | 21000.00 |
| 112 | Signal Gift Stores | 71800.00 |
| 114 | Australian Collectors, Co. | 117300.00 |
| 119 | La Rochelle Gifts | 118200.00 |
| 121 | Baane Mini Imports | 81700.00 |
| 124 | Mini Gifts Distributors Ltd. | 210500.00 |
| 125 | Havel & Zbyszek Co | 0.00 |
| 128 | Blauer See Auto, Co. | 59700.00 |
| 129 | Mini Wheels Co. | 64600.00 |
| 131 | Land of Toys Inc. | 114900.00 |
| 141 | Euro+ Shopping Channel | 227600.00 |
…
SELECT
customerNumber,
customerName,
creditLimit
FROM
customers
ORDER BY customerNumber
LIMIT 5;
output: 첫째 열부터 5개의 결과값이 반환된 것을 알 수 있다.
| customerNumber | customerName | creditLimit |
|---|---|---|
| 103 | Atelier graphique | 21000.00 |
| 112 | Signal Gift Stores | 71800.00 |
| 114 | Australian Collectors, Co. | 117300.00 |
| 119 | La Rochelle Gifts | 118200.00 |
| 121 | Baane Mini Imports | 81700.00 |
MySQL LIMIT을 이용해서 가장 높거나 가장 낮은 열 가져오기
SELECT
customerNumber,
customerName,
creditLimit
FROM
customers
ORDER BY creditLimit DESC
LIMIT 5;
output: 가장 높은 열들 5개가 출력된다.
| customerNumber | customerName | creditLimit |
|---|---|---|
| 141 | Euro+ Shopping Channel | 227600.00 |
| 124 | Mini Gifts Distributors Ltd. | 210500.00 |
| 298 | Vida Sport, Ltd | 141300.00 |
| 151 | Muscle Machine Inc | 138500.00 |
| 187 | AV Stores, Co. | 136800.00 |
SELECT
customerNumber,
customerName,
creditLimit
FROM
customers
ORDER BY creditLimit
LIMIT 5;
output: creditLimit이 가장 낮은 순이지만 값이 0인 데이터이가 더 많이 존재할 경우 매 번 같은 값을 기대할 수 없다.
| customerNumber | customerName | creditLimit | +—————-+—————————-+————-+ | 223 | Natürlich Autos | 0.00 | | 168 | American Souvenirs Inc | 0.00 | | 169 | Porto Imports Co. | 0.00 | | 206 | Asian Shopping Network, Co | 0.00 | | 125 | Havel & Zbyszek Co | 0.00 |
따라서 보다 정확한 값을 얻기 위해서 ORDER BY에 열을 하나 더 추가함으로써 원하는 형태로 데이터를 더 제한할 수 있다.
SELECT
customerNumber,
customerName,
creditLimit
FROM
customers
ORDER BY
creditLimit,
customerNumber
LIMIT 5;
output: customerNumber에 의해 다시 정렬 되므로 예측 범위 내의 값을 기대할 수 있다.
| customerNumber | customerName | creditLimit | +—————-+—————————-+————-+ | 125 | Havel & Zbyszek Co | 0.00 | | 168 | American Souvenirs Inc | 0.00 | | 169 | Porto Imports Co. | 0.00 | | 206 | Asian Shopping Network, Co | 0.00 | | 223 | Natürlich Autos | 0.00 |
MySQL LIMIT을 이용해 페이지 만들기
데이터를 스크린에 보여줄 때 열들을 페이지 단위로 나누고 싶을 때가 있을 것이다. (예를 들어 10, 20개 단위)
페이지의 수를 계산하기 위해 총 열들의 수를 페이지 당 열들의 수로 나눌 수 있다. 그리고 특정 페이지를 불러오기 위해 LIMIT을 사용할 수 있다.
아래의 COUNT(*)는 customers 표의 모든 열의 수를 반환한다.
SELECT
COUNT(*)
FROM
customers;
output:
| COUNT(*) |
|---|
| 122 |
각각의 페이지가 10개 열을 사용한다고 가정해보자. 총 122명의 customers가 존재하니 13페이지가 필요하다. 그리고 마지막 13번째 페이지는 단 두 개의 열만 포함하게 된다.
SELECT
customerNumber,
customerName
FROM
customers
ORDER BY customerName
LIMIT 10;
output: customerName 순으로 정렬한 10개의 상위 데이터를 보여준다.
| customerNumber | customerName |
|---|---|
| 242 | Alpha Cognac |
| 168 | American Souvenirs Inc |
| 249 | Amica Models & Co. |
| 237 | ANG Resellers |
| 276 | Anna’s Decorations, Ltd |
| 465 | Anton Designs, Ltd. |
| 206 | Asian Shopping Network, Co |
| 348 | Asian Treasures, Inc. |
| 103 | Atelier graphique |
| 471 | Australian Collectables, Ltd |
SELECT
customerNumber,
customerName
FROM
customers
ORDER BY customerName
LIMIT 10, 10;
output: 위 결과값에 바로 이은 11번째부터 20번째까지의 데이터를 보여준다.
| customerNumber | customerName |
|---|---|
| 114 | Australian Collectors, Co. |
| 333 | Australian Gift Network, Co |
| 256 | Auto Associés & Cie. |
| 406 | Auto Canal+ Petit |
| 198 | Auto-Moto Classics Inc. |
| 187 | AV Stores, Co. |
| 121 | Baane Mini Imports |
| 415 | Bavarian Collectables Imports, Co. |
| 293 | BG&E Collectables |
| 128 | Blauer See Auto, Co. |
n번째의 가장 높거나 가장 낮은 값 구하기
SELECT select_list
FROM table_name
ORDER BY sort_expression
LIMIT n-1, 1;
SELECT
customerName,
creditLimit
FROM
customers
ORDER BY
creditLimit DESC
LIMIT 1,1;
output: creditLimit의 내림차순 중 두 번째로 높은 값을 추출한다.
| customerName | creditLimit |
|---|---|
| Mini Gifts Distributors Ltd. | 210500.00 |
그러나 위의 사례와 달리 같은 credit limits를 가진 customer가 둘 이상 존재한다면 문제가 발생한다. 따라서 복수의 데이터가 존재하는 경우 DENSE_RANK function을 사용하는 것이 좋다.
SELECT
customerName,
creditLimit,
city,
DENSE_RANK() OVER (PARTITION BY
city
ORDER BY creditLimit
) creditLimit_rank
FROM customers;
output: creditLimit_rank는 사용자가 직접 지정하는 이름
| customerName | creditLimit | city | creditLimit_rank |
|---|---|---|---|
| Warburg Exchange | 0.00 | Aachen | 1 |
| Diecast Classics Inc. | 100600.00 | Allentown | 1 |
| Schuyler Imports | 0.00 | Amsterdam | 1 |
| GiftsForHim.com | 77700.00 | Auckland | 1 |
| Down Under Souveniers, Inc | 88000.00 | Auckland | 2 |
| Kelly’s Gift Shop | 110000.00 | Auckland | 3 |
| Enaco Distributors | 60300.00 | Barcelona | 1 |
| Rovelli Gifts | 119600.00 | Bergamo | 1 |
| Herkku Gifts | 96800.00 | Bergen | 1 |
| Der Hund Imports | 0.00 | Berlin | 1 |
| Precious Collectables | 0.00 | Bern | 1 |
| Gifts4AllAges.com | 41900.00 | Boston | 1 |
| Diecast Collectables | 85100.00 | Boston | 2 |
| Cramer Spezialitäten, Ltd | 0.00 | Brandenburg | 1 |
| Auto-Moto Classics Inc. | 23000.00 | Brickhaven | 1 |
| Online Mini Collectables | 68700.00 | Brickhaven | 2 |
| Collectables For Less Inc. | 70700.00 | Brickhaven | 3 |
| Gift Depot Inc. | 84300.00 | Bridgewater | 1 |
| Signal Collectibles Ltd. | 60300.00 | Brisbane | 1 |
city로 각각의 파티션을 나누고 각각의 파티션 별로 creditLimit ASC 순서로 나열한다. 그리고 각각의 파티션 내에서 rank를 나누어 creditLimit_rank에 rank 값을 준다.
MySQL LIMIT & DISTINCT
만약 LIMIT을 DISTINCT와 함께 사용하면, MySQL은 LIMIT에서 명시된 특정 수의 열을 찾았을 때 더이상의 검색을 중지한다.
SELECT DISTINCT
state
FROM
customers
WHERE
state IS NOT NULL
LIMIT 5;
output:
| state |
|---|
| NV |
| Victoria |
| CA |
| NY |
| PA |
IS NULL
값이 NULL인지 아닌지를 테스트하기 위해 IS NULL을 사용할 수 있다. 만약 값이 NULL일 경우 true를 반환하고 그렇지 않으면 false를 반환한다.
IS NULL은 비교 연산자이기 때문에 어느 곳에서나 사용할 수 있다.
SELECT 1 IS NULL, -- 0
0 IS NULL, -- 0
NULL IS NULL; -- 1
-- NOT NULL
SELECT 1 IS NOT NULL, -- 1
0 IS NOT NULL, -- 1
NULL IS NOT NULL; -- 0
SELECT
customerName,
country,
salesrepemployeenumber
FROM
customers
WHERE
salesrepemployeenumber IS NOT NULL
ORDER BY
customerName;
output:
| customerName | creditLimit | city | creditLimit_rank |
|---|---|---|---|
| Warburg Exchange | 0.00 | Aachen | 1 |
| Diecast Classics Inc. | 100600.00 | Allentown | 1 |
| Schuyler Imports | 0.00 | Amsterdam | 1 |
| GiftsForHim.com | 77700.00 | Auckland | 1 |
| Down Under Souveniers, Inc | 88000.00 | Auckland | 2 |
| Kelly’s Gift Shop | 110000.00 | Auckland | 3 |
| Enaco Distributors | 60300.00 | Barcelona | 1 |
| Rovelli Gifts | 119600.00 | Bergamo | 1 |
| Herkku Gifts | 96800.00 | Bergen | 1 |
| Der Hund Imports | 0.00 | Berlin | 1 |
| Precious Collectables | 0.00 | Bern | 1 |
| Gifts4AllAges.com | 41900.00 | Boston | 1 |
…
IS NULL - specialized features
ODBC 프로그램과의 호환성을 위해 MySQL은 IS NULL 연산자의 전문화된 특성을 지원한다.
1) date ‘0000-00-00’ 다루기
만약 DATE 혹은 DATETIME 열이 NOT NULL과 특수한 date '0000-00-00'를 포함한다면, 이러한 열을 찾는데 IS NULL을 사용할 수 있다.
첫째로, projects라는 표를 만들어보자:
CREATE TABLE IF NOT EXISTS projects (
id INT AUTO_INCREMENT,
title VARCHAR(255),
begin_date DATE NOT NULL,
complete_date DATE NOT NULL,
PRIMARY KEY(id)
);
둘째로, projects 표 안에 몇 개의 열을 할당한다.
INSERT INTO projects(title,begin_date, complete_date)
VALUES('New CRM','2020-01-01','0000-00-00'),
('ERP Future','2020-01-01','0000-00-00'),
('VR','2020-01-01','2030-01-01');
셋째, IS NULL 연산자는 complete_date 열에서 '0000-00-00'을 찾는다.
SELECT *
FROM projects
WHERE complete_date IS NULL;
‘0000-00-00’ 할당에 에러가 나는 경우
sql_mode에NO_ZERO_DATE세션이 적용되어 있기 때문에 발생할 수 있다. 해결 참고
2) @@sql_auto_is_null 변수의 영향
만약 @@sql_auto_is_null 변수의 값이 1이라면 IS NULL 연산자를 사용해 INSERT를 실행한 후 auto_increment column의 값을 얻을 수 있다.
@@sql_auto_is_null의 기본 값은 0이다. 다음의 예시를 보자.
먼저, @@sql_auto_is_null 변수의 값을 1로 지정한다.
SET @@sql_auto_is_null = 1;
둘째, projects 표 안에 새로운 열을 할당한다.
INSERT INTO projects(title,begin_date, complete_date)
VALUES('MRP III','2010-01-01','2020-12-31');
셋째, IS NULL 연산자를 사용해 id 열에서 만들어진 값을 얻는다.
SELECT
id
FROM
projects
WHERE
id IS NULL;