일반적인 쿼리의 예제들 (Examples)
페이지 정보
작성자 서방님 댓글 0건 조회 187회 작성일 12-02-01 15:07본문
컬럼의 최대 값
특정 컬럼의 최대 값을 가지고 있는 열(Row)
그룹 당 컬럼의 최대 값
특정 필드의 그룹 와이즈 최대 값을 가지고 있는 열(Rows)
사용자 지정 변수 사용하기
Foreign 키 사용하기
두 개의 키상에서 검색하기
일별 방문자수 계산하기
AUTO_INCREMENT 사용하기
여기에서는 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, “서버 시스템 변수”를 참조.
댓글목록
등록된 댓글이 없습니다.
