??????? ???????? ? MySQL
??????? ?????????? (matkovsky@ukrpost.net)
?????? ?????????? ?????????? ????? ??????? ????????.
? ?????????? ???????? ?? ????? ???????????? ??????? shop. ??????
??????????, ??? ??? ?????????.
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)
)
??? ??????????? ?????? ???????, ??? ??, ??? ?? ?????? ? ???????
??????, ??????? ????? ??????? ???????????? ?????. ????? ? ???????
????? ???? ?????? ??? ?????????? ???? ??????? - ??? ?????
???????????????? ????, ?? ???? INT(4) ???????? "???????????????? ?????
?????". UNSIGNED ????????, ??? ??? ????? ?? ????? ?????, ?? ????
?????? ????, ZEROFILL - ??? ??????? ????????? ?????? ??????? ???????
?????, ???? ???????? ?? ?????? ?????????? ?????????? (? ????? ?????? -
4). DEFAULT ????????? ????????, ??????? ?????? ???? ??????????? ? ???
??????, ???? ?????? ?? ??????? (????????, ??? ????????? ??????????
?????? ? ???????), ? NOT NULL ???????? ???????? ?? ??, ??? ????????
???????????????? ???? ?? ?????? ???? ?????? (NULL). ? ????????? -
PRIMARY KEY ?????? ????????? ???? ??????? - ?? ???? ??? ?????? ?????,
???????? ??????? ?? ?????? ??????????? ? ???????. ? ????? ?????? ???
???? article ? dealer, ?? ???? ??? ???? ???????? ???? ????? ??????
???? ???????????.
?????? ???????? ??? ??????? ??? ?????? ????????? INSERT:
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);
?????? ?????????? ???????.
????? ??????? ????. SELECT MAX(price) FROM shop;
+---------+
| article |
+---------+
| 4 |
+---------+
??? ?????????? ? ????? ??????? ??????.
SELECT *
FROM shop
WHERE price = ( SELCT MAX(price) FROM shop )
? ???? ??????? ???????????? ????????? - ???????? ???? ???????????? ?
??????????? ????????? ???????. ? ?????????, MySQL "???? ???" ??
???????????? ??????????, ??????? ??? ??????? ???? ?????? ?????
????????? ?????????: ????? ????? ??????? ???? ????? ???? ???????????
??????? ????????? : ... WHERE price=????????? ????.
????? ??????? ???? ?? ?????? ?? ??????.
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article
???? ?????? ???????? MAX(price) ??? ??????? ???????? ???? article ?
??????? ?????????:
+---------+-------+
| article | price |
+---------+-------+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
+---------+-------+
????????, ??????? ??????? ?????? ?? ????? ??????? ????, ??? ??????
??????
SELECT article, dealer, price
FROM shop s1
WHERE price = ( SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article )
????????? ???????? ????????? ??? ?????? ? MySQL ???????? ?????????:
????????? ?????????? ?????? (?????????? ????????? ???? ?? ?????? ??
??????), ????? ???? ??? ?????? ?????? ????? ??????, ? ??????? ????
????? ???????????????? ????????? ????????????? ????????.
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.article AND shop.price=tmp.price;
UNLOCK TABLES;
DROP TABLE tmp;
????????????? ??????? ??????
????????????, ??? MySQL ?? ????????? ?????? - ??? ????????? ????????
CHECK ??? ???????? ????????????? ????? ? ???????; ? ?????????????
??????? ?????? ?? ??????? ? ???????????? ??????? ??????.
??? ?????? ?????? ? ???????? ???????.
???????? ?????? ???????:
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 |
+----+---------+--------+-------+
?????? ???????? ????????? ?????? ? ???????????? ???? ???? ?????? ?
?????? id (??? ??????? ????????, ????????????? ?????, ??? ?????
?????????? ? 'Lilliana'):
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 |
+----+-------+--------+-------+
|