diesel mysql

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 named test).

You can create and populate the example table with these statements:

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));
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:

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 |
+---------+--------+-------+

3.6.1. The Maximum Value for a Column

“What's the highest item number?”

SELECT MAX(article) AS article FROM shop;
+---------+ | article | +---------+ | 4 | +---------+

3.6.2. The Row Holding the Maximum of a Certain Column

Task: Find the number, dealer, and price of the most expensive article.

This is easily done with a subquery:

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

Another solution is to sort all rows descending by price and get only 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.

3.6.3. Maximum of Column per Group

Task: Find 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 |
+---------+-------+

3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field

Task: For each article, find the dealer or dealers with the most expensive price.

This problem can be solved with a subquery like this one:

SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article); +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | C | 1.69 | | 0004 | D | 19.95 | +---------+--------+-------+

The preceding example uses a correlated subquery, which can be inefficient (see Section 13.2.8.7, “Correlated Subqueries”). Other possibilities for solving the problem are to use a non-correlated subquery in the FROM clause or a LEFT JOIN:

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; SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < class="title">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

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 values are not affected by another connection which is also performing inserts.

Note: For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() 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 are 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.

If the AUTO_INCREMENT column is part of multiple indexes, MySQL will generate sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.

To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

More information about AUTO_INCREMENT is available here:

How to assign the AUTO_INCREMENT attribute to a column: Section 13.1.5, “CREATE TABLE Syntax”, and Section 13.1.2, “ALTER TABLE Syntax”.
How AUTO_INCREMENT behaves depending on the SQL mode: Section 5.2.6, “SQL Modes”.
Find the row that contains the most recent AUTO_INCREMENT value: Section 12.2.3, “Comparison Functions and Operators”.
Set the AUTO_INCREMENT value to be used: Section 13.5.3, “SET Syntax”.
AUTO_INCREMENT and replication: Section 6.7, “Replication Features and Known Problems”.
Server-system variables related to AUTO_INCREMENT (auto_increment_increment and auto_increment_offset) that can be used for replication: Section 5.2.3, “System Variables”.

Previous / Next / Up / Table of Contents


*************************** 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

3.6.7. Searching on Two Keys

An OR using a single key is well optimized, as is the handling of AND.

The one tricky case is that of searching on two different keys combined with OR:

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' OR field2_index = '1'

This case is optimized from MySQL 5.0.0. See Section 7.2.6, “Index Merge Optimization”.

You can also solve the problem efficiently by using a UNION that combines the output of two separate SELECT statements. See Section 13.2.7.3, “UNION Syntax”.

Each SELECT searches only one key and can be optimized:

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';

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<

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.

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 |
+----+---------+

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 |
+--------+----+---------+

To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

Labels: dev.mysql

mysql

Tuesday, April 3, 2007

3.6. Examples of Common Queries

3.6.1. The Maximum Value for a Column

3.6.2. The Row Holding the Maximum of a Certain Column

3.6.3. Maximum of Column per Group

3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field

3.6.9. Using `AUTO_INCREMENT`

3.6.7. Searching on Two Keys

3.6.8. Calculating Visits Per Day

3.6.9. Using `AUTO_INCREMENT`

0 Comments:

Previous Posts

mysql

Tuesday, April 3, 2007

3.6. Examples of Common Queries

3.6.1. The Maximum Value for a Column

3.6.2. The Row Holding the Maximum of a Certain Column

3.6.3. Maximum of Column per Group

3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field

3.6.9. Using AUTO_INCREMENT

3.6.7. Searching on Two Keys

3.6.8. Calculating Visits Per Day

3.6.9. Using AUTO_INCREMENT

0 Comments:

Previous Posts

3.6.9. Using `AUTO_INCREMENT`

3.6.9. Using `AUTO_INCREMENT`