Examples of Common Queries
==========================
Here are examples of how to solve some common problems with MySQL.
`dealer') is a primary key for the records.
Start the command-line tool `mysql' and select a database:
shell> mysql your-database-name
(In most MySQL installations, you can use the database name `test').
You can create and populate the example table with these statements:
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);
After issuing the statements, the table should have the following
contents:
mysql> | 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
The Maximum Value for a Column
------------------------------
"What's the highest item number?"
SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
| 4 |
+---------+
The Row Holding the Maximum of a Certain Column
-----------------------------------------------
"Find number, dealer, and price of the most expensive article."
In SQL-99 (and MySQL Version 4.1) this is easily done with a subquery:
SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
In MySQL versions prior to 4.1, just do it in two steps:
1. Get the maximum price value from the table with a `SELECT'
statement.
mysql> SELECT MAX(price) FROM shop;
+------------+
| MAX(price) |
+------------+
| 19.95 |
+------------+
2. Using the value 19.95 shown by the previous query to be the maximum
article price, write a query to locate and display the
corresponding record:
mysql> SELECT article, dealer, price
-> FROM shop
-> WHERE price=19.95;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0004 | D | 19.95 |
+---------+--------+-------+
Another solution is to sort all rows descending by price and only get
the first row using the MySQL-specific `LIMIT' clause:
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
*NOTE*: If there were several most expensive articles, each with a
price of 19.95, the `LIMIT' solution would show only one of them!
Maximum of Column per Group
---------------------------
"What's the highest price per article?"
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 |
+---------+-------+
The Rows Holding the Group-wise Maximum of a Certain Field
----------------------------------------------------------
"For each article, find the dealer(s) with the most expensive price."
In SQL-99 (and MySQL Version 4.1 or greater), the problem can be solved
with a subquery like this:
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
In MySQL versions prior to 4.1, it's best do it in several steps:
1. Get the list of (article,maxprice) pairs.
2. For each article, get the corresponding rows that have the stored
maximum price.
This can easily be done with a temporary table and a join:
CREATE TEMPORARY TABLE tmp (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL);
LOCK TABLES shop READ;
INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;
SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;
UNLOCK TABLES;
DROP TABLE tmp;
If you don't use a `TEMPORARY' table, you must also lock the `tmp'
table.
"Can it be done with a single query?"
Yes, but only by using a quite inefficient trick called the "MAX-CONCAT
trick":
BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+
The last example can, of course, be made a bit more efficient by doing
the splitting of the concatenated column in the client.
Using User Variables
--------------------
You can use MySQL user variables to remember results without having to
store them in temporary variables in the client. *Note 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 |
+---------+--------+-------+
Using Foreign Keys
------------------
In MySQL 3.23.44 and up, `InnoDB' tables support checking of foreign
key constraints. *Note `InnoDB': InnoDB. See also *Note ANSI diff
Foreign Keys::.
in the table(s)
you're referencing and 2) automatically delete rows from a table with a
foreign key definition. Using your keys to join tables will work just
fine:
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,
colour 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');
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
LAST_INSERT_ID());
SELECT * FROM person;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | colour | 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.colour <> 'white';
+----+-------+--------+-------+
| id | style | colour | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+
Searching on Two Keys
---------------------
MySQL doesn't yet optimise when you search on two different keys
combined with `OR' (searching on one key with different `OR' parts is
optimised quite well):
SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR field2_index = '1'
The reason is that we haven't yet had time to come up with an efficient
way to handle this in the general case. (The `AND' handling is, in
comparison, now completely general and works very well.)
In MySQL 4.0 and up, you can solve this problem efficiently by using a
`UNION' that combines the output of two separate `SELECT' statements.
*Note UNION::. Each `SELECT' searches only one key and can be
optimised:
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';
where the SQL server does the optimisations in the wrong order.
CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;
The above way to solve this query is in effect a `UNION' of two queries.
Calculating Visits Per Day
--------------------------
The following example shows how you can use the bit group functions to
calculate the number of days per month a user has visited a web page.
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);
The example table contains year-month-day values representing visits by
users to the page. To determine how many different days in each month
these visits occur, use this query:
SELECT year,month,BIT_COUNT(BIT_OR(1<na("Источник: Документация\n 8C8C8A938E80CD8EB8BEB7BB878ECD80B1A38D859E818ADFDFF8E7ADF186DBDC80C59083899CDDA5A89CEDA7BDBA9694869DCB87978A9D8E858D90A3F6B287A08287869BCADA9E9E9E8ADC9FEBF7A2AE9A959C8AD1BAEA8287818682C3ADB9B4F2C285C8D0D5819DC49B98D4A3B7ADBBA7B1C2DD9B86898DD7998DC1DFC7C4E6F5A5ECACF79F968D9687D380C29999C3A3F5E4EA809EC3929DDDB1A9969FCB8586F1B5A7A782C38099D1D0C7DD9A89969FB79EBBABB0829DDD8DC5839BD381CAD3CA8EE1E1EDBCDC")
+----+---------+
You can retrieve the most recent `AUTO_INCREMENT' value with the
`LAST_INSERT_ID()' SQL function or the `mysql_insert_id()' C API
function. Note: For a multiple-row insert,
`LAST_INSERT_ID()'/`mysql_insert_id()' will actually return the
`AUTO_INCREMENT' key from the *first* of the inserted rows. This
allows multiple-row inserts to be reproduced correctly on other servers
in a replication setup.
For `MyISAM' and `BDB' tables you can specify `AUTO_INCREMENT' on a
secondary column in a multiple-column index. In this case, the
generated value for the `AUTO_INCREMENT' column is calculated as
`MAX(auto_increment_column)+1) WHERE prefix=given-prefix'. This is
useful when you want to put data into ordered groups.
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;
Which returns:
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
group.
This happens even for `MyISAM' tables, for which `AUTO_INCREMENT'
values normally are not reused.)
[Назад] [Содержание] [Вперед]
| Главная |