MySQL 쿼리문 정리

여기에서는 MySQL에서 일반적으로 발생하는 문제점들을 해결할 수 있는 예제들을 보여 주기로 한다.

어떤 예제들은 특정 판매자 (dealer)의 상품 (item number) 값을 가지고 있도록 테이블 shop 을 사용한다. 각 판매자는 상품 별로 정해진 단일 가격을 가지고 있고, (article, dealer)는 레코드에 대한 프라이머리 키(primary key)라고 가정한다.

명령어 라인에서 mysql을 구동 시키고 데이터 베이스를 선택한다:

shell> mysql your-database-name

아래의 명령어를 사용해서 예제 테이블을 생성하고 데이터를 그 안에 넣는다:

mysql> CREATE TABLE shop (
    -> article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    -> dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    -> price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    -> PRIMARY KEY(article, dealer));
mysql> INSERT INTO shop VALUES
    -> (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    -> (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

명령문을 입력하면 테이블은 아래의 내용을 갖게 될 것이다:

mysql> SELECT * FROM shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

컬럼의 최대 값

“최고 (highest) 아이템 숫자는 어떤 것인가?”

SELECT MAX(article) AS article FROM shop;
 
+---------+
| article |
+---------+
|       4 |
+---------+

특정 컬럼의 최대 값을 가지고 있는 열(Row)

Task: 가장 비싼 상품의 숫자, 판매자, 그리고 가격을 찾는다.

서브 쿼리를 사용하면 간단히 처리할 수 있다:

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

다른 방법으로는, 모든 열을 가격 내림 차순으로 정렬시킨 다음에 LIMIT 구문을 사용해서 처음 열만 가져오면 된다:

SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

Note: 만약에 가장 비싼 제품들이 여러 개가 있고, 가격이 19.95라면, LIMIT 솔루션은 이들 중에 한 개만 보여 주게 된다.

그룹 당 컬럼의 최대 값

Task: 상품별로 최고의 값을 찾는다.

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article
 
+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

특정 필드의 그룹 와이즈 최대 값을 가지고 있는 열(Rows)

Task: 각 상품에 대해서, 판매자를 찾거나 또는 최고의 값을 가지고 있는 판매자를 찾는다.

이 문제는 아래와 같은 서브 쿼리를 사용해서 해결할 수 있다:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

사용자 지정 변수 사용하기

이 섹션에서는 결과값을 클라이언트에 있는 임시 변수에 저장하는 대신에 MySQL사용자 변수에 기록하도록 만드는 방법을 설명한다.

예를 들면, 최고 가격 및 최저 가격을 갖는 상품을 찾기 위해서 다음과 같이 실행할 수 있을 것이다:

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

Foreign 키 사용하기

MySQL에서 InnoDB 테이블은 Foreign 키 제한 검사를 지원한다.

Foreign 키 제한은 단순히 두 테이블을 조인 (join)하기 위해 필요한 것이 아니다. InnoDB가 아닌 스토리지 엔진의 경우, 컬럼이 REFERENCES tbl_name(col_name) 구문을 사용하도록 정의하는 것이 가능한데, 이것은 실제로 아무런 효과도 없으며, 또한 여러분이 지금 정의하는 컬럼은 다른 테이블에 있는 컬럼을 참조하도록 의도된 것이라는 것을 알려주는 메모 또는 코멘트 역할만 하게 된다. 이 신텍스를 다음과 같은 상황에서 사용할 때는 특히 중요하다:

• MySQL은 col_name 이 실제로 tbl_name 에 있는지 (또는 tbl_name 자체가 존재하는지) 확인하기 위한 어떠한 종류의 CHECK도 실행하지 않는다.
• MySQL 은 여러분이 정의한 테이블에서 열을 가져와서 삭제하는 것과 같은 행동을 tbl_name 에서는 전혀 하지 않는다; 다른 말로 표현하면, 이 신텍스는 ON DELETE 또는 ON UPDATE 가 구동하는데 아무런 영향을 미치지 않는다. (여러분이ON DELETE 또는 ON UPDATE 구문을 REFERENCES 구문의 일부분으로 작성한다 하더라도, 이것은 무시되어 버린다.)
• 이 신텍스는 column을 만들어 낸다; 이것은 어떤 종류의 인덱스 또는 키를 만들지는 않는다.
• 이 신텍스는 InnoDB 테이블을 정의하기 위해 사용할 때 에러를 발생시킨다.

여러분은 여기에서 보듯이, 컬럼을 만들어서 조인(join) 컬럼으로 사용할 수 있다:

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);
 
CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);
 
INSERT INTO person VALUES (NULL, 'Antonio Paz');
 
SELECT @last := LAST_INSERT_ID();
 
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
 
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
 
SELECT @last := LAST_INSERT_ID();
 
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
 
SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+
 
SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+
 
 
SELECT s.* FROM person p, shirt s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.color <> 'white';
 
+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

이러한 형태로 사용될 경우에, REFERENCES 구문은 SHOW CREATE TABLE 또는 DESCRIBE 구문의 결과에는 나타나지 않는다:

SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

여기에서처럼 REFERENCES 를 컬럼 정의문에서 코멘트 형태 또는 “리마인더(reminder)”로 사용하는 것은 MyISAM 및 BerkeleyDB 테이블에서도 적용 할 수 있다.

두 개의 키상에서 검색하기

단일 키를 사용하는 OR는 AND에서 다루어진 것처럼 매우 잘 동작을 하게 된다.

한가지 까다로운 경우는 OR로 조합되어 있는 두 개의 다른 키에서 검색을 하는 것이다:

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'

이것은MySQL 5.0.0에서부터 최적화 되었다.

또한 두 개의 독립적인 SELECT 명령문에서 나오는 결과를 조합하는 UNION을 사용해서 문제를 효과적으로 해결할 수도 있다.

각각의SELECT 명령문은 오직 한 개의 키만을 검색하고 최적화 시킨다:

SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';

일별 방문자수 계산하기

아래의 예제는 비트 그룹 함수를 사용해서 월별 일수를 계산해서 웹 사이트에 일별로 방문한 사람의 수를 계산하는 방법을 나타내는 것이다. [출처] ::: MySQL Korea ::: - http://www.mysqlkorea.co.kr/ MySQL Korea 사이트의 컨텐츠 소유권은 MySQL Korea 에 있으므로 허락 없이 이를 무단전재 하는 경우 저작권법에 민형사적 책임을 지게 되므로 절대 무단 사용을 금해 주시기 바 랍니다 MySQL Korea 저작권 공지 : http://www.mysqlkorea.co.kr/sub.html?mcode=others&scode=04

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);


[출처] ::: MySQL Korea ::: - http://www.mysqlkorea.co.kr/

MySQL Korea 사이트의 컨텐츠 소유권은 MySQL Korea 에 있으므로
허락 없이 이를 무단전재 하는 경우 저작권법에 민형사적 책임을 지게 되므로
절대 무단 사용을 금해 주시기 바 랍니다
MySQL Korea 저작권 공지 : http://www.mysqlkorea.co.kr/sub.html?mcode=others&scode=04

이 예제 테이블에는 사용자가 페이지를 방문하는 것을 나타내는 년-월-일 (year-month-day) 값이 있다. 월별로 몇일 동안 방문자가 있었는지 알아보기 위해서는 다음의 쿼리를 사용한다: [출처] ::: MySQL Korea ::: - http://www.mysqlkorea.co.kr/ MySQL Korea 사이트의 컨텐츠 소유권은 MySQL Korea 에 있으므로 허락 없이 이를 무단전재 하는 경우 저작권법에 민형사적 책임을 지게 되므로 절대 무단 사용을 금해 주시기 바 랍니다 MySQL Korea 저작권 공지 : http://www.mysqlkorea.co.kr/sub.html?mcode=others&scode=04

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;

이에 대한 결과는 다음과 같다:

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

쿼리는 중복된 엔트리를 자동으로 삭제시키고 각 년/월 조합 테이블에서 얼마나 많은 날이 발생했는지를 계산한다.

AUTO_INCREMENT 사용하기

AUTO_INCREMENT는 새로운 열에 대해서 유니크한 (unique) 값을 생성할 때 사용한다:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
 );
 
INSERT INTO animals (name) VALUES 
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');
 
SELECT * FROM animals;

이에 대한 결과는 다음과 같다:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

여러분은 LAST_INSERT_ID() SQL 함수 또는 mysql_insert_id() C API 함수를 사용해서 가장 최근의 AUTO_INCREMENT 값을 불러올 수가 있다. 이 함수들은 연결 특성 (connection-specific)이 있기 때문에, 이것들이 생성하는 결과 값들은 삽입을 실행하는 다른 연결에는 영향을 미치지 않게 된다.

Note: 다중 열 삽입에 대해서, LAST_INSERT_ID() 와 mysql_insert_id()는 실제로 삽입된 맨 처음 열로부터 AUTO_INCREMENT 를 리턴한다. 이를 통해서 리플리케이션 설정에 있는 다른 서버에서 다중 열 삽입이 정확히 이루어진다.

MyISAM 및 BDB 테이블의 경우에는 다중 컬럼 인덱스에 있는 두번째(secondary)컬럼에서 AUTO_INCREMENT를 지정할 수 있다. 이와 같은 경우, AUTO_INCREMENT 컬럼을 위해 생성된 값은 MAX(auto_increment_column) + 1 WHERE prefix=given-prefix와 같이 계산된다. 이것은 데이터를 주문 받은 (ordered) 그룹 안에 넣고자 할 경우에 매우 유용하다.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
);
 
INSERT INTO animals (grp,name) VALUES 
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');
 
SELECT * FROM animals ORDER BY grp,id;

이에 대한 결과는 다음과 같다:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

이와 같은 경우에 (AUTO_INCREMENT 컬럼이 다중 컬럼 인덱스의 일부분이 되는 경우), 여러분이 어떤 그룹에 있는 제일 큰 AUTO_INCREMENT 값을 가지고 열을 삭제한다면, AUTO_INCREMENT 값이 다시 사용된다는 점을 알아두기 바란다. 이것은 MyISAM 테이블에서도 동일하다. 하지만, 일반적인 경우에는 AUTO_INCREMENT 값이 다시 사용되지는 않는다.

만약에 AUTO_INCREMENT 컬럼이 다중 인덱스의 부분이라면, MySQL은 AUTO_INCREMENT 컬럼을 가지고 시작하는 인덱스를 사용해서 시퀀스 값을 만들어 낼 것이다. 예를 들면, 만약에 animals 테이블이 PRIMARY KEY (grp, id) 및 INDEX (id) 인덱스를 가지고 있다면, MySQL은 시퀀스 값을 만들기 위해 PRIMARY KEY를 무시하게 될 것이다. 그 결과로, 테이블은 단일 시퀀스를 갖게 되며, grp에 있는 값 별로 시퀀스를 갖지는 못하게 된다.

AUTO_INCREMENT 의 값이 1인 아닌 것으로 시작으로 하기 위해서는, 아래와 같이 그 값을 CREATE TABLE 또는 ALTER TABLE을 사용해서 설정한다:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

AUTO_INCREMENT에 대한 보다 자세한 정보는 다음과 같다:

• AUTO_INCREMENT 속성을 컬럼에 할당하는 방법: Section 13.1.5, “CREATE TABLE 신텍스”, 및 Section 13.1.2, “ALTER TABLE 신텍스”를 참조.
• SQL 모드에 따른 AUTO_INCREMENT 의 동작 방법: Section 5.2.5, “서버 SQL 모드”를 참조.
• 가장 최신의 AUTO_INCREMENT 값을 찾기: Section 12.1.3, “비교 함수와 연산자”를 참조.
• AUTO_INCREMENT 값을 사용하도록 설정하기: Section 13.5.3, “SET 신텍스”를 참조.
• AUTO_INCREMENT 및 리플리케이션: Section 6.7, “리플리케이션 의 특징과 알려진 문제점들”를 참조할 것.
• 리플리케이션용으로 사용할 수 있는 AUTO_INCREMENT (auto_increment_increment 및 auto_increment_offset)와 관련된 서버 시스템 변수: Section 5.2.2, “서버 시스템 변수”를 참조.

출처 : MySQL 코리아