• notice
  • Congratulations on the launch of the Sought Tech site

Mysql Chinese Reference Manual - Example of Common Search

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

Some examples use a database table "shop" containing the price of each article (item number) for a certain merchant.Assuming there is a single fixed price per article for each merchant, then (item, merchant) is the primary key for the record.

You can create example database tables like this:

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

3.1 column maximum value

"What is the largest item number?"

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

3.2 The row with the maximum value of a column

"Find the ID, merchant and price of the most expensive article"

In ANSI-SQL this is easy to do with a subquery:
SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop)

In MySQL (no subqueries yet) do it in 2 steps:

Use a SELECT statement to get the maximum value from the table.

Use this value to make up the actual query:

SELECT article, dealer, price
FROM shop
WHERE price=19.95

Another solution is to sort all rows in descending order by price 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 articles (eg 19.95 each), the LIMIT solution will only show one of them!

3.3 Column Max: By Group: Only Values

"What is the maximum price per article?"

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

3.4 The row with the maximum value between groups for a field

"For each article, find the trader with the most expensive price."

In ANSI SQL, I can do this 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)

In MySQL, this is best done in several steps:

Get a table (articles, maxprice).See 3.4 for the row with the largest between-group value for a domain.

For 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 a TEMPORARY table, you must also lock the "tmp" table.

"Can it be done with a single query?"

Yes, but only using a rather inefficient trick I call 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 can of course be made a bit more efficient by splitting the linked columns in the client.

3.5 Using foreign keys

No foreign key is required to join the 2 tables.

The only thing MySQL doesn't do is CHECK to ensure that the key you're using actually exists in the table you're referencing, and it doesn't automatically delete rows from tables that have a foreign key defined.If you use your keys as you normally would, it will work just fine!

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

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

atorvastatin 40mg pills & lt;a href="https://lipiws.top/"& gt;atorvastatin 20mg pill& lt;/a& gt; purchase atorvastatin online cheap

Uxtxow

2024-03-07

Leave a Reply

+