| 8.3 examples of common queries The following describes how to useMySQLExamples of solving some common problems. Some examples use the database table "Shop", which contains every article of a merchant.Article(Item Number) price. Assume that each merchant's article has a separate fixed price, then (item, merchant) is the primary key of the record. You can create an example database table as follows: Create Table shop (article int (4) unsigned zerofill default '100' not null, dealer char (20) default ''not null, price double (0000) 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, the example data is as follows: 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 | + --------- + -------- + ------- +
8.3.1 maximum value of a column "What is the largest item number ?" Select max (Article) as article from shop + --------- + | Article | + --------- + | 4 | + --------- + 8.3.2 rows with the maximum value of a column "Find the number, merchant, and price of the most expensive article" In the ANSI-SQL this is easily done with a subquery: Select article, dealer, pricefrom shopwhere price = (select max (price) from shop) InMySQLIn step 2 (no subquery is available:
- Use one
SelectStatement to obtain the maximum value from the table.
- Use this value to compile the actual query:
Select article, dealer, pricefrom shopwhere price = 19.95
Another solution is to sort all rows by price in descending order and useMySQLSpecificLimitClause only obtains the first line: Select article, dealer, pricefrom shoporder by price desclimit 1 Note:: If there are multiple most expensive articles (for example, each 19.95), LimitThe solution only displays one of them! 8.3.3 maximum value of a column: by group: only values "What is the highest price for each article ?" Select article, max (price) as pricefrom shopgroup by Article + --------- + ------- + | Article | price | + --------- + ------- + | 0001 | 3.99 | 0002 | 10.99 | 0003 | 1.69 | 0004 | 19.95 | + --------- + ------- + 8.3.4 rows with the maximum value between groups for a field "Find the most expensive traders for each article ." InANSI SQL, I can use this subquery: Select article, dealer, pricefrom shop s1where price = (select max (s2.price) from shop S2 where s1.article = s2.article) InMySQL, It is best to do it in several steps:
- Get a table (article, maxprice ). See 8.3.4 rows with the maximum value between groups in a domain.
- For each article, get the row corresponding to the maximum storage price.
This can be easily done using a temporary table: Create temporary table TMP (article int (4) unsigned zerofill default '100' not null, price double (0000) 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, tmpwhere shop. article = TMP. articel and shop. price = TMP. price; unlock tables; drop table TMP; If you do not useTemporaryTable, you must also lock the "tmp" table. "Can it be done in a single query ?" Yes, but there is only one rather inefficient trick to use what I call "MAX-CONCAT tricks: 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 final example can certainly beProgramTo make it more effective. 8.3.5 use foreign keys No foreign keys are required to join two tables. MySQLThe only thing you don't do isCheckTo ensure that the key you are using exists in the referenced table, and it does not automatically delete rows from the table with a foreign key definition. If you use your key value as usual, it will work well!
Create Table persons (ID smallint unsigned not null auto_increment, name char (60) not null, primary key (ID); 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 | + ---- + --------------------- + | 1 | 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 | + ---- + ------- + -------- + ------- + |
