|
06-29-2004, 09:03 AM
|
#1 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.6 Examples of Common Queries
3.6 Examples of Common Queries
Here are examples of how to solve some common problems with MySQL. Some of the examples use the table shop to hold the price of each article (item number) for certain traders (dealers). Supposing that each trader has a single fixed price per article, then (article, 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.4 5), -> (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);After issuing the statements, the table should have the following contents: 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 |+---------+--------+-------+ |
|
|
|
06-29-2004, 09:03 AM
|
#2 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.6.1 The Maximum Value for a Column
3.6.1 The Maximum Value for a Column
``What's the highest item number?'' SELECT MAX(article) AS article FROM shop;+---------+| article |+---------+| 4 |+---------+ |
|
|
|
|
06-29-2004, 09:04 AM
|
#3 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.6.2 The Row Holding the Maximum of a Certain Column
3.6.2 The Row Holding the Maximum of a Certain Column
``Find number, dealer, and price of the most expensive article.'' In standard SQL (and MySQL Version 4.1) this is easily done with a subquery: SELECT article, dealer, priceFROM shopWHERE price=(SELECT MAX(price) FROM shop);In MySQL versions prior to 4.1, just do it in two steps:
SELECT article, dealer, priceFROM shopORDER BY price DESCLIMIT 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! |
|
|
|
|
06-29-2004, 09:04 AM
|
#4 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.6.3 Maximum of Column per Group
3.6.3 Maximum of Column per Group
``What's the highest price per article?'' SELECT article, MAX(price) AS priceFROM shopGROUP BY article+---------+-------+| article | price |+---------+-------+| 0001 | 3.99 || 0002 | 10.99 || 0003 | 1.69 || 0004 | 19.95 |+---------+-------+ |
|
|
|
|
06-29-2004, 09:05 AM
|
#5 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.6.4 The Rows Holding the Group-wise Maximum of a Certain Field
3.6.4 The Rows Holding the Group-wise Maximum of a Certain Field
``For each article, find the dealer or dealers with the most expensive price.'' In standard SQL (and MySQL Version 4.1 or greater), the problem can be solved with a subquery like this: SELECT article, dealer, priceFROM shop s1WHERE 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:
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, tmpWHERE 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'': SELECT article, SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer, 0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS priceFROM shopGROUP 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 be made a bit more efficient by doing the splitting of the concatenated column in the client. |
|
|
|
|
06-29-2004, 09:05 AM
|
#6 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.6.5 Using User Variables
3.6.5 Using User Variables
You can use MySQL user variables to remember results without having to store them in temporary variables in the client. See section 10.3 User 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 |+---------+--------+-------+ |
|
|
|
|
06-29-2004, 09:06 AM
|
#7 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.6.6 Using Foreign Keys
3.6.6 Using Foreign Keys
In MySQL 3.23.44 and up, InnoDB tables support checking of foreign key constraints. See section 16 The InnoDB Storage Engine. See also section 1.8.5.5 Foreign Keys. You don't actually need foreign keys to join two tables. For table types other than InnoDB, the only things MySQL currently doesn't do are 1) CHECK to make sure that the keys you use really exist in the table or tables 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, 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');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');INSERT INTO shirt 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 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 |+----+-------+--------+-------+ |
|
|
|
|
06-29-2004, 09:06 AM
|
#8 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.6.7 Searching on Two Keys
3.6.7 Searching on Two Keys
MySQL doesn't yet optimize when you search on two different keys combined with OR (searching on one key with different OR parts is optimized quite well): SELECT field1_index, field2_index FROM test_tableWHERE 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. See section 14.1.7.2 UNION Syntax. Each SELECT searches only one key and can be optimized: SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'UNIONSELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';Prior to MySQL 4.0, you can achieve the same effect by using a TEMPORARY table and separate SELECT statements. This type of optimization is also very good if you are using very complicated queries where the SQL server does the optimizations in the wrong order. CREATE TEMPORARY TABLE tmpSELECT field1_index, field2_index FROM test_table WHERE field1_index = '1';INSERT INTO tmpSELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';SELECT * from tmp;DROP TABLE tmp;This method of solving the problem is in effect a UNION of two queries. |
|
|
|
|
06-29-2004, 09:07 AM
|
#9 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.6.8 Calculating Visits Per Day
3.6.8 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<<day)) AS days FROM t1 GROUP BY year,month;Which returns: +------+-------+------+| year | month | days |+------+-------+------+| 2000 | 01 | 3 || 2000 | 02 | 2 |+------+-------+------+The query calculates how many different days appear in the table for each year/month combination, with automatic removal of duplicate entries. |
|
|
|
|
06-29-2004, 09:07 AM
|
#10 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.6.9 Using AUTO_INCREMENT
3.6.9 Using AUTO_INCREMENT
The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows: 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;Which returns: +----+---------+| id | name |+----+---------+| 1 | dog || 2 | cat || 3 | penguin || 4 | lax || 5 | whale || 6 | ostrich |+----+---------+You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return value is not affected by another connection also doing inserts. 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 |+--------+----+---------+Note that in this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values will be reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.) |
|
|
|
 |
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Optimizing MySQL Queries Example | codenode | General | 0 | 11-19-2004 02:58 AM |
| 3.2 Entering Queries | Administrator | Documentation | 0 | 06-09-2004 02:58 AM |
