Ключевые слова:sql, mysql, select, howto, (найти похожие документы)
Date: Mon, 23 Oct 2000 12:18:57 +0400 (MSD)
From: MailList: Perl в примерах
Subject: SQL - Примеры запросов в MySQL (SELECT, CREATE, INSERT)
Примеры запросов в MySQL
Николай Матковский (matkovsky@ukrpost.net)
Теперь рассмотрим построение более сложных запросов.
В дальнейших примерах мы будем использовать таблицу shop. Заодно
рассмотрим, как она создается.
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)
)
Это определение немого сложнее, чем то, что вы видели в прошлой
статье, поэтому стоит немного остановиться здесь. Число в скобках
возле типа данных при объявлении типа столбца - это длина
соответстувющего поля, то есть INT(4) означает "четырехразрядное целое
число". UNSIGNED означает, что это целое не имеет знака, то есть
больше нуля, ZEROFILL - что следует заполнять нулями верхние разряды
числа, если содержит их меньше указанного количества (в даном случае -
4). DEFAULT указывает значение, которое должно быть подставлено в том
случае, если другое не указано (например, при частичном заполнении
строки в таблице), а NOT NULL обращает внимание на то, что значение
соответствующего поля не должно быть пустым (NULL). И последнее -
PRIMARY KEY задает первичный ключ таблицы - то поле или группа полей,
значения которых не должны повторяться в строках. В нашем случае это
поля article и dealer, то есть все пары значений этих полей должны
быть уникальными.
Теперь наполним эту таблицу при помощи оператора INSERT:
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);
Теперь собственно запросы.
Самая высокая цена. SELECT MAX(price) FROM shop;
+---------+
| article |
+---------+
| 4 |
+---------+
Вся информация о самой дорогой статье.
SELECT *
FROM shop
WHERE price = ( SELCT MAX(price) FROM shop )
В этом запросе используется подзапрос - значение поля сравнивается с
результатом вложеного запроса. К сожалению, MySQL "пока что" не
поддерживает подзапросы, поэтому для решения этой задачи стоит
выполнить следующее: найти самую высокую цену после чего производить
обычное сравнение : ... WHERE price=найденная цена.
Самая высокая цена за каждую из статей.
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article
Этот запрос выполнит MAX(price) для каждого значения поля article и
выведет следующее:
+---------+-------+
| article | price |
+---------+-------+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
+---------+-------+
Продавец, который продает статью по самой высокой цене, для каждой
статьи
SELECT article, dealer, price
FROM shop s1
WHERE price = ( SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article )
Наилучшим способом выполнить эту задачу в MySQL является следующее:
выполнить предыдущий запрос (нахождение наивысшей цены за каждую из
статей), после чего для каждой статьи найти строку, в которой цена
равна соответствующему найденому максимальному значению.
CREATE TEMPORARY TABLE tmp (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL);
LOCK TABLES article read;
INSERT INTO tmp SELECT article, MAX(price)
FROM shop GROUP BY article;
SELECT article, dealer, price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;
UNLOCK TABLES;
DROP TABLE tmp;
Использование внешних ключей
Единственное, что MySQL не позволяет делать - это выполнять операцию
CHECK для проверки существования ключа в таблице; и автоматически
удалять строки из таблицы с определенным внешним ключом.
Вот пример работы с внешними ключами.
Создание первой таблицы:
CREATE TABLE persons (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
Создание второй таблицы:
CREATE TABLE shirts (
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 persons,
PRIMARY KEY (id)
);
Наполнение созданных таблиц:
INSERT INTO persons VALUES (NULL, 'Antonio Paz');
INSERT INTO shirts VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());
INSERT INTO persons VALUES (NULL, 'Lilliana Angelovska');
INSERT INTO shirts VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());
Вот что они теперь содержат:
SELECT * FROM persons;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
SELECT * FROM shirts;
+----+---------+--------+-------+
| 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 |
+----+---------+--------+-------+
Теперь возможно выполнить запрос с объединением этих двух таблиц с
ключом id (Все небелые футболки, принадлежащие людям, чьи имена
начинаются с 'Lilliana'):
SELECT s.* FROM persons p, shirts 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 |
+----+-------+--------+-------+
