mysql common database queries

Here are some examples to learn how to solve some common problems with MySQL.

In some examples, the database table "shop" is used to store the price of each item (item number) of a certain merchant (dealer). Assuming that each merchant has a fixed price for each item, then (item, merchant) is the primary key of the record.

Start the command line tool mysql and select the database:

shell> mysql your-database-name

(In most MySQL, you can use the test database).

You can create a sample table using the following statement:

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 executing the statement, the table should contain the following:

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

1. Maximum value of column

"What is the largest item number?"

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

2. Owned The row with the maximum value of a certain column

Task: Find the number, seller and price of the most expensive item. This is easy to do with a subquery:

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

Another solution is to sort all rows by price in descending order and get only the first row with a MySQL specific LIMIT clause:

SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

NOTE: If there are multiple most expensive items ( For example the price of each is 19.95), the LIMIT solution only displays one of!

3. Maximum value of column: by group

Task: What is the maximum price of each item?

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

4. The row with the maximum value between groups of a certain field

Task: For each item, find the most expensive price dealer of items.

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

5. Using user variables

You can clear MySQL user variables to record the results without saving them to temporary variables on the client side.

For example, to find out the item with the highest or lowest price, the method is:

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

6. Using foreign keys

In MySQL, the InnoDB table supports checking of external keyword constraints.

Only when joining two tables, no external keyword is needed. For tables other than InnoDB types, you can use external keywords when using the REFERENCES tbl_name(col_name) clause to define columns. This clause has no actual effect and is only used as a memo or comment to remind you that the column you are currently defining points to another A column in the table. When executing this statement, it is important to implement the following:

· MySQL does not perform actions in table tbl_name , such as deleting rows in response to actions on rows in the table you are defining; in other words, this syntax does not cause ON DELETE or ON UPDATE behavior (if you write an ON DELETE or ON UPDATE clause in the REFERENCES clause, it will be ignored).

· This syntax can create a column; but it does not create any index or keyword.

· If you use this syntax to define an InnoDB table, an error will occur.

You can use the columns created as join columns like this:

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');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

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

Follow this way Using, the REFERENCES clause will not show up in the output of SHOW CREATE TABLE or DESCRIBE:

SHOW CREATE TABLE shirt\G
*************************** 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

Using REFERENCES as a comment or "hint" in column definitions in this way works for tables MyISAM and BerkeleyDB.

7. 根据两个键搜索

可以充分利用使用单关键字的OR子句，如同AND的处理。

一个比较灵活的例子是寻找两个通过OR组合到一起的关键字：

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

该情形是已经优化过的。

还可以使用UNION将两个单独的SELECT语句的输出合成到一起来更有效地解决该问题。

每个SELECT只搜索一个关键字，可以进行优化：

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

8. 根据天计算访问量

下面的例子显示了如何使用位组函数来计算每个月中用户访问网页的天数。

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

示例表中含有代表用户访问网页的年－月－日值。可以使用以下查询来确定每个月的访问天数：

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;

将返回：

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

该查询计算了在表中按年/月组合的不同天数，可以自动去除重复的询问。

9. 使用AUTO_INCREMENT

可以通过AUTO_INCREMENT属性为新的行产生唯一的标识：

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;

将返回：

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

你可以使用LAST_INSERT_ID()SQL函数或mysql_insert_id() C API函数来查询最新的AUTO_INCREMENT值。这些函数与具体连接有关，因此其返回值不会被其它执行插入功能的连接影响。

注释：对于多行插入，LAST_INSERT_ID()和mysql_insert_id()从插入的第一行实际返回AUTO_INCREMENT关键字。在复制设置中，通过该函数可以在其它服务器上正确复制多行插入。

对于MyISAM和BDB表，你可以在第二栏指定AUTO_INCREMENT以及多列索引。此时，AUTO_INCREMENT列生成的值的计算方法为：MAX(auto_increment_column) + 1 WHERE prefix=given-prefix。如果想要将数据放入到排序的组中可以使用该方法。

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;

将返回：

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

请注意在这种情况下（AUTO_INCREMENT列是多列索引的一部分），如果你在任何组中删除有最大AUTO_INCREMENT值的行，将会重新用到AUTO_INCREMENT值。对于MyISAM表也如此,对于该表一般不重复使用AUTO_INCREMENT值。

如果AUTO_INCREMENT列是多索引的一部分，MySQL将使用该索引生成以AUTO_INCREMENT列开始的序列值。。例如，如果animals表含有索引PRIMARY KEY (grp, id)和INDEX(id)，MySQL生成序列值时将忽略PRIMARY KEY。结果是，该表包含一个单个的序列，而不是符合grp值的序列。

要想以AUTO_INCREMENT值开始而不是1，你可以通过CREATE TABLE或ALTER TABLE来设置该值，如下所示:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;