Kiev1.org Карта сайта Файлы Фотографии Киева
  
Реклама:






???????
 
 Sysadmin
 ??????????????
 ????? ? ?????
 ??????????
 ???????????? ???????
 ?????????????
 ? ???????
 ? ???????????
 ???????? ? ??????
 ???????????
 ??????????? ???????
 ???????? ????? ?????? ?????? ???????? ??????????
 ??????
 ??????
 ?????? ?????? 1924-1994
 ??????? ?? ??????
 ???? ? ???????
 ????? ??????????
 ?????????


Внимание! Читая пророчества на этом сайте помните что достоверность трудно проверить и все может во времени изменяться - самое главное думать своей головой и не верить легкомысленно всему что говорят, особенно советское телевидение
"О дне же том, или часе, никто не знает, ни Ангелы небесные, ни Сын, но только Отец (Мк. 13, 32)"

SQL - ??????? ???????? ? MySQL (SELECT, CREATE, INSERT) (sql mysql select howto)



??????? ???????? ? MySQL, ???????? ????????? ??????.
??????? ???????? ? 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 |
+----+-------+--------+-------+





 The Effective Use of Joins in Select Statements
 MySQL ??? ????????????
 ??????? ?????
 ??????????? DBF-???? ? MySQL ??? SQL ???????
 ??????????? ????????????? MySQL
 ????? ?????? ? MySQL
 ???????????? ??? ????????? php ???????? http://bm.org.ru
 ?????????? ??????????? ?? MySQL, ??? ?????????????? ??????? ?????? ???????????? root


Внимание! Читая пророчества на этом сайте помните что достоверность трудно проверить и все может во времени изменяться
"О дне же том, или часе, никто не знает, ни Ангелы небесные, ни Сын, но только Отец (Мк. 13, 32)"