8.3 Examples of common queries are some examples of learning how to solve some common problems with MySQL. Some examples use database table "Shop", including the price of each article (item number) of a businessman. Assume that each article of each business has a separate fixed price, then (item, businessman) is the primary key recorded. 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);
Ok, the example data is like 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 |
------- ------ -------
8.3.1 Maximum value "What is the biggest item number?" SELECT MAX (Article) AS Article from SHOP
-------
| article |
-------
| 4 |
-------
8.3.2 Rows with a list of the biggest values "identify the most expensive articles, business people and prices" in ANSI-SQL this is easy to use a child: SELECT ARTICLE, DEALER, PRICE
From shop
Where price = (SELECT MAX (Price) from shop)
In mysql (no subquery yet): 2 steps:
Use a SELECT statement to get the maximum from the table. Use this value to compare actual queries: SELECT ARTICLE, DEALER, PRICE
From shop
Where price = 19.95
Another solution is the first line that is sorted in descending order and uses mysql-specific Limit clauses: SELECT ARTICLE, DEALER, PRICE
From shop
Order by Price DESC
Limit 1
Note: If there are multiple most expensive articles (such as each 19.95), the Limit solution only shows one of them! 8.3.3 Maximum: Press Group: Only Value "What is the highest price for each article?" SELECT ARTICLE, MAX (Price) AS PriceFrom SHOP
GROUP BY ARTICLE
------- -----
| article | price |
------- -----
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
------- -----
8.3.4 Row of the largest value of a group of a field "to each article, find the tradant with the most expensive price." In ANSI SQL, I can use such a child: SELECT ARTICLE, DEALER, PRICE
From shop s1
Where price = (S2.PRice)
From shop s2
WHERE S1.Article = S2.Article)
In mysql, it is best to take a few steps:
Get a table (article, MaxPrice). See 8.3.4 Row with the largest value of the group in a certain domain. For each article, you can get rows corresponding to the maximum price of storage. This can be easily 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 shopping by articles;
Select Article, Dealer, Price from SHOP, TMP
WHERE Shop.Article = tmp.Articel and shop.price = tmp.price;
UNLOCK TABLES;
Drop Table TMP;
If you don't use a Temporary table, you must also lock the "TMP" table. "Can it do a single query?" Yes, but only using a quite inefficient trick called "Max-Concat Trick": SELECT ARTICLE,
Substring (Max (Concat (PRICAT (PRICAT, 6, '0'), DEALER), 7) AS Dealer,
0.00 Left (Max (Concat (PRICAT, 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 can of course be more effective by dividing the loop in the client program. 8.3.5 Use Foreign Commitments No Two tables are required. MySQL unique things are Check to ensure that the keys you use are actually existed in the reference table, and it does not automatically remove rows from the table with a foreign key definition. If you use your key value like usually, it will work very 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 *.
-- --------- ------ -------
| 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 |
---- ------- ------ -------
