???????? ???????????????? — ????? ?????? ? MySQL
??????? ???????
2000-12-06
?????? ???????, ?????? ?? ???? ??????? ?? ????????? ???????? ?????????? ???? ???????? ?????
????????????? ? ??????????? ??????? ?????? "????????
????????????????" ?? ????????? ??????, ????????? ? ???????? ???? :).
???-??? ? ??????? ?? ?????? ? ???????????? ??????? ? ?.?. ?? ?????
??????????? ???? ??????????! ???? ??? ????????? ???????????????? ?????
?? ??????? ??????.
????, ????????? ???????, ??????? ??? ????, ?????????? ?????????????? ?????? ??? MySQL.
?.: ?? ?????? ??????? ??? ??????? ????? ?????
?????????, ????? ? ???? ?????? ????????????? ??????????? ?????? ??????
(???? ????????? ? ???? ?? ??).
???????? ???? SELECT db.user, db.delete_priv, user.user,
user.delete_priv FROM db,user WHERE db.user = user.user ?? ??????
????????, ??? ??? ?????? ??? ????? ??????? ? ??????? ????????...
O.: ?????? ??????, ?? ???????, ?????? ??? ????? ???? ?? ????????? ??? ??? ????? ?????????
?.: ?? ?? ???????? ??? ?????, ?????????? ??
?????????? ????????... ???????? ?? ????-?????? ????? ?????????????,
????? ? ?????? ?? ???? ?????? ? ?????? ? ????????.
O.: ????? ???????????? - ?? ?????? ? ???? ??????????? ????????? ??? ? MS Access?
? ?????????, ??? Access ???????? ????? ??? ?? — ??????????
?????? ???????? "SQL", ? ?? ??????? ??? ????? "? ??????? ????????".
?.: ?????? ??? ????? ?????????? ??? ??? ??????, ????? ??????? ?????????... ??? ? ????? MySQL ??? ?? ????????.
O.: ????? ????????? ?????, ????????? ????? ????? ????? ???? ?????? ???? ?????? ? ????????
?.: ??????? ????????? ?????? ? ???????? —
????? ?-?? ?????????? ???????? ? ? ???? ??? ???????.... ? ?????? ?????
?? ?????? ??????? — ?? ?????? ?? ??? ??? ?????...
O.: ?????????... ????? ??? ? ????????, ????? ?????? ???????? ?? ?????.
? ?????, ?????? ????? ?????? ????????? ?? ???????? ????? ??????.
??????, ?????? ?????? - ??? ?????? ??????????? ?????????? - Voodoo ;) : ????? ???????????? - ??? ????????? reference
www.mysql.com/documentation/mysql/commented/manual.php?section=CREATE_TABLE
create table.....
reference_definition: REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL | MATCH PARTIAL]
[ON DELETE reference_option]
[ON UPDATE reference_option]
The FOREIGN KEY, CHECK, and REFERENCES clauses don't actually do
anything. The syntax for them is provided only for compatibility, to
make it easier to port code from other SQL servers and to run
applications that create tables with references. See section 5.4
Functionality Missing from MySQL.
????? ??? ??????? ???????
??????,
??????? ??????? ? ???????? ?????? ? ??????? ?? ???? ? ??? - ???????
????? ?? ???????? ?????? ???. Access ?? ??????????? ?
?????????????????? ???????? ??????? ???????.
????? ????? ????????? - ???? ??????????? ???? ??????. ? ??????
??? ???? ?????? ?? ???????? ??????? ??????????. ?????? ???? ?????? ??
???? ???? ??????????? ?? ?????? ??????? - ????????, ????????? ? ??????,
????????? ? ?? ?????? (???? ???? ??????????? ??????????), ???????? ????
? ????????? ??????? ????????? ?????????? ?????? ? ??????? ???????????
??? ??????? ?????????. ??? ???? ?????? ????? ???, ??? ????, ??
??????????? ???? ? ??????, ? ?? ??????????? ?????? ???. ??? ? ????
???????? ????? ??????????? ??. ?? ??????????? ??????? ????? (????????,
????????? ?????? ?????????? ??????), ??????? ?????? ???????????? ?????
????????. ??????? ??????? ???????????? ?? ????.
??? ?????? ???????????
????????, ??? ???:
$res1 = mysql_query("SELECT id, name FROM rubs");
while ($row = mysql_fetch_row($res1))
$rub[$row[0]] = $row[1];
?? ??????? ???????? ????? ?????? ? ???????? ? ?????? $rub.
$res2 = mysql_query("SELECT id, url, name, rub FROM sites WHERE ?????-??-???-???????");
while ($row = mysql_fetch_array($res2)) {
echo "<a href=", $row["url"], ">", $row["name"], "</a>";
echo "(??????? <a href=rub.phtml?id=", $row["id"], ">";
echo $rub[$row["rub"]], "</a><br>";
};
?????? ?????????? ???????, ? ?????? ?????????? ?????? ??????? ????????? ??????????????? ??????? ??????? ??????.
?? ????? ????, ????? ???????? ???? ?? ????????????? ??????
?????? ??? ????????? ???? ?????? $rub (? ???? ? ??? ? ????????
?????????? ??????? - ???, ????? GLOBAL ??????), ?? ???????????
????????? ? $rub[$row["rub"]] - ???? ?? ???????? ????????? ????????
????????, ?? ??????? ???????? ???-?????? ?????.
????? ????, ??? ?????? $rub ????????? ????????? ????? ?????? (?
???? ?????? ??????). ? ??????? ?????? PHP ????? ?????? ?????
??????????? ??????, ??? ??? ????????????? ??????????? ??????, ??????
??? ?? ?????????????? ????????? ????????? ??? ?????????? (? ??????? ??
4-??, ??????? ??????????? ????????? ? ?????? ????? ?????????).
??? ????
? ??????????? ???? ??????? ????? ????????? ??????????? ?????? ? ?????????? ?? ????????? ???????????.
$res = mysql_query("SELECT sites.id, url, sites.name as sitename, rubs.name as rubsname,
rubs.id as rub_id FROM sites, rubs WHERE sites.rub=rubs.id
?-?????-??-???-???????");
while ($row = mysql_fetch_array($res2)) {
echo "<a href=", $row["url"], ">", $row["sitename"], "</a>";
echo "(??????? <a href=rub.phtml?id=", $row["rub_id"], ">";
echo $row["rubsname"], "</a><br>";
};
????, ????? ????? ???????????? ?????? "SELECT sites.id, url,
sites.name as sitename, rubs.name as rubsname, rubs.id as rub_id FROM
sites, rubs WHERE sites.rub=rubs.id". ??????????, ??? ?? ????? ???????
??????, ????????? ? ?????? ?????? ??? ????????? ? ????? ?????? ????.
????????? ??????????? ??????
??????? ?????????? - INNER JOIN:
SELECT <fields> FROM table1 INNER JOIN table2 ON table1.field1=table2.field
???
SELECT <fields> FROM table1, table2 WHERE table1.field1=table2.field2
???
SELECT <fields> FROM table1 INNER JOIN table2 USING (field1)
???? ??????? ???????????? ?? ???? field1.
? ????? ?????????? ?????????? ?????? ?? ?????? ??????, ???????
????????????? ??????? ??????????? - ????????? ???????? ?????. ???? ???
?????? table1 ??? ??????????????? ?????? ?? table2, ?????? ?? ????????
? ???? ???????. ???? ?? ???? ?????????? ?????????? ?????? ? ???????
(????????? ?????? ? ?????????), ????? ?????? ?? ?????? ???????? - ?
?????? ???????? ?????? ???????, ? ??????? ???? ?????. ??? ????????
???????? ????? ???????????? LEFT JOIN.
SELECT <fields> FROM table1 LEFT JOIN table2 ON table1.field1=table2.field2
???
SELECT <fields> FROM table1 LEFT JOIN table2 USING (field1)
???? ??????? ???????????? ?? ???? field1.
??? ???? ??????????????? ?????? ? table2 ????? ? ?? ????, ????? ?
????? ?? table2 ?? ??????? NULL, ? ???? ??? ????????? ????????, ??? ?
?????? ? ??????????? ?????? ? ???????, ????? ? ???? ????? 0:
SELECT rubs.id, name, COUNT(sites.id) AS sites FROM rubs LEFT JOIN sites ON rubs.id=sites.rub GROUP BY rubs.id
????????: ???? id ???? ? ????? ????????, ??????? ? ?? ???????????
???? ???????????? ??? ???????. ??????, ???? ??? ??????????? ??
???????????? ????????? ????????, ??? ????? ????? ?????? ??? ????
?????????? AS, ????? ?? ????????? ????????.