Using User-Defined Variables
218
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;
LEFT JOIN
:
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;
The
LEFT JOIN
works on the basis that when
s1.price
is at its maximum value, there is no
s2.price
with a greater value and the
s2
rows values will be
NULL
. See
Section 13.2.8.2, “
JOIN
Syntax”
.
3.6.5. Using User-Defined Variables
You can employ MySQL user variables to remember results without having to store them in temporary
variables in the client. (See
Section 9.4, “User-Defined Variables”
.)
For example, to find the articles with the highest and lowest price you can do this:
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 |
+---------+--------+-------+
Note
It is also possible to store the name of a database object such as a table or a
column in a user variable and then to use this variable in an SQL statement;
however, this requires the use of a prepared statement. See
Section 13.5, “SQL
Syntax for Prepared Statements”
, for more information.
3.6.6. Using Foreign Keys
In MySQL,
InnoDB
tables support checking of foreign key constraints. See
Section 14.2, “The
InnoDB
Storage Engine”
, and
Section 1.8.5.4, “Foreign Keys”
.
A foreign key constraint is not required merely to join two tables. For storage engines other than
InnoDB
, it is possible when defining a column to use a
REFERENCES tbl_name(col_name)
clause,
which has no actual effect, and serves only as a memo or comment to you that the column which
you are currently defining is intended to refer to a column in another table. It is extremely important to
realize when using this syntax that:
• MySQL does not perform any sort of
CHECK
to make sure that
col_name
actually exists in
tbl_name
(or even that
tbl_name
itself exists).
• MySQL does not perform any sort of action on
tbl_name
such as deleting rows in response to
actions taken on rows in the table which you are defining; in other words, this syntax induces no
ON DELETE
or
ON UPDATE
behavior whatsoever. (Although you can write an
ON DELETE
or
ON
UPDATE
clause as part of the
REFERENCES
clause, it is also ignored.)
• This syntax creates a column; it does not create any sort of index or key.
You can use a column so created as a join column, as shown here:
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
