Chinese MySQL Reference Manual – the tutorial 8.MySQL – 8.3 Examples of Common Queries

Here is an example of some learn how to use MySQL to solve some common problems.

Some examples of use the database table “shop”, contains a businessman of the price of each article (item number).Each article assumes that every businessman has a single fixed price, then (article, dealer) is the record’s primary key.

You can create an example database table:

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

Well, for example, data is this:

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

“The largest Item No. What is it?”

  SELECT MAX (article) AS article FROM shop 

  + --------- + 
  | Article | 
  + --------- + 
  | 4 | 
  + --------- + 

“To find out the number of the most expensive articles, businessmen and price”

It’s easy to use ANSI-SQL in a subquery do:

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

In MySQL (no subquery) Step 2 do:

  1. Get the maximum value from the table using aSELECTstatement.
  2. Using this value compile the actual query:
      SELECT article, dealer, price 
      FROM shop 
      WHERE price = 19.95 
    

Another solution is sorted in descending order by price all rows with MySQL-specificLIMITclause only get the first line:

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

Note: If you have more than the most expensive articles (for example, each 19.95)?LIMITsolution shows only one of them!

The highest price of each article is it? “

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

Each article, find out the most expensive price traders.

InANSI SQL, I can use a subquery to do:

  SELECT article, dealer, price 
  FROM shop s1 
  WHERE price = (SELECT MAX (s2.price) 
                FROM shop s2 
                WHERE s1.article = s2.article) 

In MySQL, it is best to be done in several steps:

  1. Get a table (articles, maxprice). See .
  2. Each article, get the row corresponding to the stored maximum price.

This can easily be done with a temporary table:

  CREATE TEMPORARY TABLE tmp ( 
          article INT (4) UNSIGNED ZEROFILL DEFAULT '0000 'NOT NULL, 
          price DOUBLE (16,2) DEFAULT '0 .00 'NOT NULL); 

  LOCK TABLES article read; 

  INSERT INTO tmp SELECT article, MAX (price) FROM shop GROUP BY article; 

  SELECT article, dealer, price FROM shop, tmp 
  WHERE shop.article = tmp.articel AND shop.price = tmp.price; 

  UNLOCK TABLES; 

  DROP TABLE tmp; 

If you do not use aTEMPORARYtable, you must also lock the tmp table.

“Can a single query to do it?”

Yes, but only 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 price 
  FROM shop 
  GROUP BY article; 

  + --------- + -------- + ------- + 
  | Article | dealer | price | 
  + --------- + -------- + ------- + 
  | 0001 | B | 3.99 | 
  | 0002 | A | 10.99 | 
  | 0003 | C | 1.69 | 
  | 0004 | D | 19.95 | 
  + --------- + -------- + ------- + 

The last example, of course, through the split link column in the client program to make it more effective.

No foreign key links two tables.

MySQLonly does not do isCHECKto ensure that you use the key indeed you are the reference table, and it does not automatically deleted from the table has a foreign key definition line. If you use your key as usual, it will work very well!

  CREATE TABLE persons ( 
      id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,  
      name CHAR (60) NOT NULL, 
      PRIMARY KEY (id) 
  ); 

  The CREATE TABLE shirts ( 
      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 persons, 
      PRIMARY KEY (id) 
  ); 

  INSERT INTO persons VALUES (NULL, 'Antonio Paz'); 

  INSERT INTO shirts VALUES 
  (NULL, 'polo', 'blue', LAST_INSERT_ID ()), 
  (NULL, 'dress', 'white', LAST_INSERT_ID ()), 
  (NULL, 't-shirt', 'blue', LAST_INSERT_ID ()); 

  INSERT INTO persons VALUES (NULL, 'Lilliana Angelovska'); 

  INSERT INTO shirts 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 persons; 
  + ---- + --------------------- + 
  | Id | name | 
  + ---- + --------------------- + 
  | | Antonio Paz | 
  | 2 | Lilliana Angelovska | 
  + ---- + --------------------- + 

  SELECT * FROM shirts; 
  + ---- + --------- + -------- + ------- + 
  | 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 persons p, shirts 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 | 
  + ---- + ------- + -------- + ------- +