MySQL Getting Started Tutorial 7 - Examples of Common Database Queries

Here are some examples of how to use MySQL to solve some common problems.

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 content:

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 the column

"What is the largest item number?"

SELECT MAX(article) AS article FROM shop;

+---------+
| article |
+---------+
|       4 |
+---------+

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

Task: Find the number, seller and price. 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 the 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 will only display one of them!

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 of a certain field between groups

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

You can solve this problem 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 changing them Save to a temporary variable 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 external Checking of keyword constraints.

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

· MySQL does not perform actions in table tbl_name, such as in response to actions on rows in the table you are defining. Deletes the row; 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 does not create any index or keyword.

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

You can use columns created as join columns as follows:

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 sWHERE p.name LIKE &#39;Lilliana%&#39;AND s.owner = p.idAND s.color <> &#39;white&#39;;
+----+-------+--------+-------+| id | style | color  | owner |+----+-------+--------+-------+|  4 | dress | orange |     2 ||  5 | polo  | red    |     2 ||  6 | dress | blue   |     2 |+----+-------+--------+-------+

Used this way, 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 manner applies to tables MyISAM and BerkeleyDB.

7. Searching based on two keys

can make full use of the OR clause using a single keyword, just like AND.

A more flexible example is to find two keywords combined by OR:

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

This situation has been optimized.

You can also use UNION to combine the output of two separate SELECT statements together to solve this problem more effectively.

Each SELECT only searches for one keyword, which 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';

8. Calculate visits based on days

The following example shows how to use the bit group function to Calculate the number of days in each month that users visit the 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 ​​that represent the user's visit to the web page. The following query can be used to determine the number of access days per month:

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

will return:

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

This query counts the number of distinct days combined by year/month in the table and can automatically remove duplicates inquiry.

9. Use AUTO_INCREMENT

You can use the AUTO_INCREMENT attribute to generate a unique identifier for the new row:

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;

will return:

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

You can use LAST_INSERT_ID ()SQL function or mysql_insert_id() C API function to query the latest AUTO_INCREMENT value. These functions are connection-specific, so their return values ​​are not affected by other connections performing insert functions.

Note: For multi-row inserts, LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT keyword from the first row inserted. In a replication setup, this function allows multi-row inserts to be replicated correctly on other servers.

For MyISAM and BDB tables, you can specify AUTO_INCREMENT and multi-column indexes in the second column. At this time, the calculation method for the value generated by the AUTO_INCREMENT column is: MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. Use this method if you want to put the data into sorted 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;

will return:

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

 以上就是MySQL入门教程7 —— 常用数据库查询的示例的内容，更多相关内容请关注PHP中文网（www.php.cn）！