????? ????????????
?? ????????? ???? ????? ????? ????? ?????????? ? ???, ??? ????????? MySQL-??????, ?? ??????????? ??? ?????????? ? ???, ??? ??? ???????????? ????????????? ????????????. ????????, ??? ??-?? ????, ??? ??????????, ??????? ?????????? ?? MySQL, ??????? ?????????????????? ??????????????, ? ?????? ???????????? ? ?? ????? ?????, ??? ? ??? ????????. ?? ????? ??? ??? ?? ?? ????????, ????????, ?? ?????? ?????, ??? ??????????? ??? ??? ???? SQL-??????.
??????? ?????????? ??? ????????? ????????? ???? ?????? ???????? mysqlshow. ??????? ????????? ???????:
mysqlshow -p mysql
?? ??????? ?????? ??????, ??????? ????????? ? ???? ?????? mysql.
Database: mysql
+--------+
| Tables |
+--------+
| db |
| host |
| user |
+--------+
????????? mysqlshow ????? ?????????? ? ??????????????? ???????????, ?????????? ? ??????? 1.
??????? 1. ????????? ????????? mysqlshow
???????? ????????
--host=hostname ?????? ??? ?????, ? ???????? ?? ?????? ????????????
--port=port_number ?????????? ????? ????? ??? ??????? MySQL
--socket=socket ????????? ?????
--user=username ? ??????? ????? ????????? ????? ??????? ?????? ??? ????????????
-p ??????????? ????? ??????
??? ????? ?? ???????? ? ??????? ???????????? ????????? mysql. ??? ? ???????? ???????? ???????. ? ???? ????????? ????? ???????????? ?? ?? ?????, ??? ? mysqlshow. ????? ?????????????? ?????????? ????????? mysql ????? ???? ????? ?????? ???????? "-s". ? ?????????? ??? ?????? ??? ????????????. ???? ???????? ????????? ??????????? ???????? ?????????, ????????? ????????. ?? ????????? ?????? ????? ??? ?????? ???????? ??????????????????. ?? ? ????????? ?? ????? ????????? ? ????????? ??????????? ????? ?? ???????.
??????? ???? ?????? ????? ? ??????? ????????? mysqladmin. ???????????, ??? ???????????? ??? ???????? ????? ?????? ?????????????, ????????,
mysqladmin -u admin -p create my_db
??? ???????? ?????? SQL? ?????? ???????? ??????, ? ??????? ?? ?????????, ????? ?????????? ?? ????? ???????? ?? ???????, ??? ????? ???????? ? ??????? ?? ??????? ?????????, ?????? ???????? ??????? ?????, ? ??????? ???????????, ???????? ?? ?????? ?????? ???????, ?, ???? ?????? ???????? ??????, ????????? ???????.
??? ???????? ???????? ??????? ??? ?????????? ????? ???? - SQL (Structured Query Language, ????????????????? ???? ????????). ? ??????? ???????? SQL ?? ??????:
1. C???????? ???? ?????? ? ???????
2. ????????? ?????????? ? ???????
3. ??????? ??????????
4. ?????????????? ??????????
5. ???????? ?????? ??? ??????
???????????, ???????????? admin, ????? ????, ??? ?????? ????????????, ?????? ???????? ???????????????? ???????. ?????? ?????? MySQL ?????? ????????????? ?????? ? ???????. ???? ?? ??????? SELECT * FROM test ?????? mysql ????? ????? ????? ????? ? ???????:
->
??????? ???????????, ??? ????? ?????? ??????? ???????? ????????? SQL, ?? ???? ??? ????????? ?????????? ????? ????????? ?? ?? ?????. ????????? mysql ????????? ???? ??????? ?? ??? ???????. ????????, ??????, ?????????? ? ????????? SQL,
SELECT *
FROM S
WHERE Q > 10
? ????????? mysql ????? ???????? ???:
SELECT * FROM S WHERE Q > 10
?????? ???????? ??? ??????? - ?????, ??????? ? ??????.
CREATE TABLE CLIENTS
(
C_NO int NOT NULL,
FIO char(40) NOT NULL,
ADDRESS char(30) NOT NULL,
CITY char(15) NOT NULL,
PHONE char(11) NOT NULL
);
??????? CLIENTS ???????? ???? C_NO (????? ???????), FIO (???????, ???, ????????), ?????, ????? ? ???????. ??? ??? ???? ?? ????? ????????? ??????? ???????? (NOT NULL).
CREATE TABLE TOVAR
(
T_NO int NOT NULL,
DSEC char(40) NOT NULL,
PRICE numeric(9,2) NOT NULL,
QTY numeric(9,2) NOT NULL
);
??? ??????? ????? ????????? ?????? ? ???????. ??? numeric(9,2) ????????, ??? 9 ?????? ??????? ??? ????? ?????, ? ??? - ??? ???????. QTY - ??? ?????????? ?????? ?? ??????.
CREATE TABLE ORDERS
(
O_NO int NOT NULL,
DATE date NOT NULL,
C_NO int NOT NULL,
T_NO int NOT NULL,
QUANTITY numeric(9,2) NOT NULL,
AMOUNT numeric(9,2) NOT NULL
);
?????? ??????? ???????? ???????? ? ??????? - ????? ?????? (O_No), ???? ?????? (DATE), ????? ??????? (C_NO), ????? ?????? (T_NO), ?????????? (QUANTITY) ? ????? ????? ?????? AMOUNT (?? ???? AMOUNT = T_NO * TOVAR.PRICE)
?????? ??????? ?????? ? ???? ???????. ???????? ?????? ????? ? ??????? ????????? INSERT. ?????????? ????????????? ????????? INSERT:
INSERT INTO CLIENTS
VALUES (1,'?????? ?.?.', '?????? 6', '??????????','80522111111');
??????????? ???????? ?????? ??????????????? ???? ???????, ? ??????? ???? ??????????? ? ????????? CREATE. ???? ?? ?????? ????????? ?????????? ? ?????? ???????, ?? ?? ?????? ??????? ???? ??????? ? ????????? INSERT:
INSERT INTO CLIENTS (FIO,ADDRESS,C_NO,PHONE,CITY)
VALUES ('??????', '??????? 9',2,'-','??????????');
? ??????? INSERT ?? ????? ????????? ?????? ? ???????????? ????, ????????, C_NO ? FIO:
INSERT INTO CLIENTS (C_NO, FIO)
VALUES (1,'??????');
?? ?????? ?? ???????? ??? ??????, ????????? ??? ????????? ???? ????? NULL (?????? ????????), ? ???? ??????? ?? ????????? ?????? ????????. ?????????? ????? ???????? ?????? ? ?????? ???????. ??????? ?????? ? ??????? TOVAR:
INSERT INTO TOVAR
VALUES (1,'??????? LG',550.74);
???????? ????????, ??? ?? ???? ??? ?? ??????? ????????? ????? ???????, ??????? ??? ????? ?? ?????? ???????? ? ??????? ?????????? ??????. ???????? ???? ? ???? DATE ????? ? ??????? ??????? TO_DATE:
INSERT INTO ORDERS
VALUES (1,TO_DATE('01/01/02,'DD/MM/YY'),1,1,1,550.74);
?????? ?????? ????????, ??? ??????? ?????? 2002 ???? ?????? ?.?. (C_NO=1) ??????? ???? (QUANTITY=1) ??????? LG (T_NO=1).
???????????, ??? ??? ????? ???????? ??????, ????????, ?????? ?????? ???????? ? ?????? ?????. ??? ???????? ???:
UPDATE CLIENTS
SET CITY = '????'
WHERE C_NO = 1;
?????? ?????? ???? ????????, ?????? ??????? ????????? 10:
DELETE FROM CLIENTS
WHERE C_NO > 10;
? ??????? ??????? DELETE ????? ??????? ??? ?????? ???????, ?????? ???????, ??????? ???????? ??? ???? ???????, ????????
DELETE FROM CLIETNS;
???? ?????? ????? ????????? DELETE - WHERE - ?? ???????, ??????, ???????? ????????? ???????????????? ?? ??? ?????? ?????.
??????????, ????????? ? ???????? ??????? - ???, ??????????, ????? ?????? ???????, ?? ???? ????? ?? ?????? ???????????? ???????? SELECT, ??????? ???????? ?????? ?? ???????. ????????, ??? ?????? ???? ??????? ?? ??????? CLIENS, ???????:
SELECT * FROM CLIENTS;
? ?????????? ?? ???????? ????? ????? ?? ???????:
C_NO FIO ADDRESS CITY PHONE
1 ?????? ?.?. ?????? 6 ?????????? 80522111111
1 ?????? ?.?. ?????? 6 ?????????? 80522111111
2 ?????? ?.?. ??????? 9 ?????????? 80522112111
???????? ???????? ?? ?????? ??? ?????? - ??? ??????????. ????????????, ?????????? ?????????? ??????? ???????? - ?? ???? ?? ??????? ????????? ???? ???????. ???? ?? ?????? ????????? ?????????? ?????? ?? ?????? ??????? (?? ?? ?? ???????!), ??????? ??????:
SELECT DISTINCT *
FROM CLIENTS;
???????????, ??? ?? ?????? ??????? ?????? ??????? ? ????? ???????? ???????, ????? ??????? ????? ??????:
SELECT DISTINCT FIO, PHONE
FROM CLIENTS;
?????? ???????? ??????????? ????? ????????. ??????? ??? ??????, ???? ??????? ????????? 500 ??????.
SELECT *
FROM TOVAR
WHERE PRICE > 500;
?? ?????? ???????????? ?????? ????? ?????????: <,>,=,<>,<=,>=.
???? ???? ???????? ??????????? ????????? ?????????????, ? ?? ?????? ??????? ?????????? ??? ???? ????????, ??????????? ?????? LIKE:
SELECT *
FROM CLIENTS
WHERE FIO LIKE '%??????%';
?????? ???????? ???: ??????? ??? ?????????? ? ????????, ??????? ??????? ?????? ?? '??????'. ???? ?? ?????? ??????? ?????? ?? ?????? ??????, ????? ?????? ???? ????? ????????? ??? ???????. ????????? ?????? ??????? ????? ???? ????????, ??????? ???? ?? ??? ???????? ? ??? ?????:
SELECT DISTINCT CLIENTS.FIO
FROM CLIENTS, ORDERS
WHERE CLIENTS.C_NO = ODREDS.C_NO;
???????? SELECT ????????? ???????????? ????????? ???????. ????????? ???????? ?????????? ???????????:
SELECT DISTINCT CLIENTS.FIO
FROM CLIENTS
WHERE CLIENTS.C_NO IN (SELECT C_NO
FROM ORDERS);
??? ?????? ? ?????????? SELECT ??? ???????? ????????? ???????? ???????, ??????????? ?????????? ????????? (COUNT), ????? ????????? (SUM), ???????????? ? ??????????? ???????? (MAX ? MIN), ? ????? ??????? ???????? (AVG).
????????? ????????? ???????, ??????????????, ?????????? ??????? ? ??????? CLIENTS, ????? ??????? ????? ? ????? ???? ??????? ?? ??????.
SELECT COUNT(*)
FROM CLIENTS;
SELECT MAX(PRICE)
FROM TOVAR;
SELECT SUM(PRICE)
FROM TOVAR;
???????? SELECT ????????? ???????????? ???????????? ????????. ????????, ?????? ?????? (C_NO=1) ????????? ??? ????????? ? ??? ?????-?? ?????. ??????, ??? ????? ??????????? ? ??????? ORDERS ????????? ???.
??????? ????? ???? ????????, ? ????? ????? ?????? ??????? ???????.
SELECT CLIENTS.FIO, SUM(ORDERS.AMOUNT) AS TOTALSUM
FROM CLIENTS, ORDERS
WHERE CLIENTS.C_NO = ORDERS.C_NO
GROUP BY ORDERS.C_NO;
??????????? ????????? ???????? GROUP BY, ??????? ???????? ?????? ????????? SELECT. ???????? GROUP BY ????? ?????????? ? ??????? HAVING. ???? ???????? ???????????? ??? ?????? ?????, ???????????? GROUP BY. HAVING ????? ??????? ???????? WHERE, ?? ?????? ??? GROUP BY:
HAVING <???????>
????????, ??? ?????????? ?????? ???????, ??????? ???????? ??????? ?? ????? ?????, ??????????? 1000.
SELECT CLIENTS.FIO, SUM(ORDERS.AMOUNT) AS TOTALSUM
FROM CLIENTS, ORDERS
WHERE CLIENTS.C_NO = ORDERS.C_NO
GROUP BY ORDERS.C_NO
HAVING TOTALSUM > 1000;
? ???? ??????? ?? ???????????? ????????? ??????? TOTALSUM. ? ????????? ??????? SQL ??? ??????????? ?????????? ?? ????? ?????? ????????? ????? AS, ? ????????? ??????? ?????????? ????? ?????????: SUM(ORDERS.AMOUNT) TOTALSUM ??? TOTALSUM = SUM(ORDERS.AMOUNT).
???? ?? ?? ?????????? ????????? ????, ?????????? ????? ??????? ?? ???????????. ?????? ????? ?????????? ??????? ????????? ?? ? ???????. ??? ?????????? ?? ???? C_NO ?????????? ?????? ??????? CLIENTS ???????????? ????????? ???????? (???? ??????? ??? ???? ?? ???????????):
SELECT *
FROM CLIENTS
ORDER BY C_NO;
???????????, ??? ???-?? ??????? ? ??????? CLIENTS ??????
1 ??????? ??????? 11 ?????????? 80522345111
? ?? ??????????, ??? ???? ? ??? ?? ????? ??????????? ?????? ????????. ????? ??? ?? ??? ??????? ??????? LG? ????? ???????? ???????? ????????, ????? ???????????? ????????? ?????:
ALTER TABLE CUSTOMER
ADD PRIMARY KEY (C_NO);
????? ????? ??????? ???? C_NO ????? ????????? ?????? ?????????? ????????. ? ???????? ?????????? ????? ?????? ???????????? ????, ??????????? ???????? NULL. ??????? ????????? ???? ????? ????? - ??? ???????? ???????. ??? ???????? ???:
CREATE TABLE CLIENTS
(
C_NO int NOT NULL,
FIO char(40) NOT NULL,
ADDRESS char(30) NOT NULL,
CITY char(15) NOT NULL,
PHONE char(11) NOT NULL,
PRIMARY KEY (C_NO);
);
??????? ORDERS ???????? ???????? ? ???????. ?? ???? C_NO ???? ??????? ???????????????? ????????. ???????????, ??? ? ??????? ORDERS ???-?? ???? ????????, ???????? ??? ? ??????? CLIENTS. ??? ?? ??????? ?????? ??? ????? ?? ????????? ???????? ????????, ??????? ??????? ????????? ??????:
ALTER TABLE ORDERS
ADD FOREIGN KEY(C_NO) REFERENCES CLIENTS;
????????? ? ??????? ORDERS ?????? ???????? C_NO ?????? ???????????? ? ??????? CLIENTS. ?????????? ????? ???????? ??????? ???? ?? ???? T_NO. ??? ??????????? ?????????? ????????????? ????????????.
??????? ALTER ???????????? ?? ?????? ??? ?????????? ??????. ??? ????????????? ??? ????????????? ??????? ? ?????. ?? ?????? ???????? ??? ???? ????? ??? ?????????? ?????? ?????????? ???????? ??? ??????? ?? ?????. ??? ??? ????? ??????? ? ??????? ??????? ALTER:
ALTER TABLE CLIENTS
ADD ZIP char(6) NULL;
???? ???????? ????????? ? ??????? CLIENTS ????? ???? ZIP ???? char. ???????? ????????, ??? ?? ?? ?????? ???????? ????? ???? ?? ????????? NOT NULL ? ???????, ? ??????? ??? ???? ??????. ???? ???????? ???????? ? ????????? ?????? ?? ????? ? ???????????, ??????? ????????????? ?????? ?????? ?????????? ???????? ??? ??????? CLIENTS:
ALTER TABLE CLIENTS
ADD CONSTRAINT INVALID_STATE SHECK (CITY IN ('??????????','????'));
??? ??? ??????? ???????? ? ???? ????? ??????? ????? ? ??????? ??????? DISCONNECT ??????????? ?? ???, ?, ????????? ?????? CONNECT, ???????????? ? ?????? ???? ??????. ? ????????? ???????? SQL ?????? DISCONNECT ?? ????????, ? ?????? CONNECT ????? ???????????? ???????? USE.
??????, ????? ?? ??? ??????? ? ???????? SQL, ??????? ?????????. ?? ??? ?????, ??? ????????? ????????? ????, ?????? ??????? ??????? ???? ??? ???????? ???????:
CREATE TABLE T
(
/* ???????? ????? ??????? */
FOREIGN KEY KEY_NAME (LIST)
REFERENCES ANOTHER_TABLE [(LIST2)]
[ON DELETE OPTION]
[ON UPDATE OPTION]
);
????? KEY_NAME - ??? ??? ?????. ??? ?? ???????? ????????????, ?? ? ????? ?????????? ?????? ????????? ??? ????? - ???? ?? ?? ??????? ??? ?????, ?? ????? ?? ??????? ??? ???????. ? ???? ?? ??? ????? ?????????, ????????, ?? ??? ?????? ????? ?? ?????? LIST - ??? ?????? ?????, ???????? ?? ??????? ????. ?????? ??????????? ????????. ANOTHER_TABLE - ??? ?????? ???????, ?? ??????? ??????????????? ??????? ????, ? ?????????????? ??????? LIST2 - ??? ?????? ????? ???? ???????. ???? ????? ? ?????? LIST ?????? ????????? ? ?????? ????? ? ?????? LIST2. ???????????, ??? ? ?????? ??????? ? ??? ???? ??? ???? - NO ? NAME - ?????? ? ??????????? ????? ??????????????. ?? ?????? ??????? ? ??? ???? ???? ? ??????????? ??????? ? ??????. ??????????? ???????? ?????
FOREIGN KEY KEY_NAME (NO, NAME)
REFERENCES ANOTHER_TABLE (NAME, NO)
???????????, ?????? ??? ???? ????? NO ? NAME ?? ?????????. ????? ???????????? ????? ???????????:
FOREIGN KEY KEY_NAME (NO, NAME)
REFERENCES ANOTHER_TABLE (NO, NAME)
???? ?? ???? ????? ?????????? ?????, ??? ? ????? ??????, ?????? LIST2 ????? ?????? ?? ?????????, ????? ?? ????????? ???? ?????? ???????.
?????????????? ????????? ON DELETE ? ON UPDATE ?????????? ???????? ?? ?????????? ?????????? ? ???? ??????, ??? ???????? ?????????? ?? ??????? ? ??? ?? ??????????. ??????? ??? ?????? ? ?????????? ? ? ???, ??? ? ??????? ??????? ???? ???? C_NO (Client NO), ???????? ???????? ?????? ???? ? ??????? ????????. ? ? ????? ????, ??? ?? ?????? ??? ? ?????? ?????? ??????? ? ??????? 99999, ???????? ??? ? ??????? ????????? ????????? ??????? ????, ?? ????????? ??? ??????? ?? ???? C_NO. ????? ???????? ????? (? ????? ??????? ???????????????? ???? ??????), ?? ?????? ??????????? ???????, ????? ???????-???????? ?????? ??????-?????? ??????? ?? ??????? ????????. ??? ?????? ? ???????? ? ??????? ???????? ? ??????? ????????? ON DELETE ?? ????? ??????? ??????? ??????? ?? ???????? ????? ??????:
ON DELETE OPTION
???????? OPTION ????? ????????? ???? ?? ??????? ????????: CASCADE, NO ACTION, SET DEFAULT, SET NULL.
???????? CASCADE ????????, ??? ????? ?????????? ??????? ????? ?????? ?? ???? ????????? ??????. ????????, ???? ?? ??????? ??????? ? ??????? 10 ?? ??????? ????????, ?? ?? ??????? ??????? ????? ??????? ??? ?????? ????? ???????.
???????? NO ACTION ?? ????????? ???????? ??????? ?? ??? ???, ???? ?? ???? ? ????????? ???????. ??? ????????, ??? ???????-???????? ?????? ?????? ??????? ??? ?????????? ? ??????? ?? ??????? ???????
? ??????? ????????? SET_DEFAULT ?? ?????? ??????? ???????? ?? ?????????. ????????, ???? ?? ??????? SET DEFAULT 1, ?? ??? ???????? ??????? ? ????? ??????? ??? ?????? ????? ????????????? ??????? ? ??????? 1, ???????, ??????????, ?????? ???? ? ??????? CLIENTS.
???????? SET NULL ????????????? ???????? NULL ? ???????? ?????? ???????, ???? ??? ?????? ?? ??????? CLIENTS. ???????, ??? ? ????? ?????? ???? C_NO ?? ????????? ???????? NULL! ? ??? ??????? ????? ?????????? SQL ?? ????????????? ???????? ????????, ?? ? MySQL ?? ??? ?? ????? ??? ???????:
ALTER TABLE CLIENTS
DROP ZIP;
??????? ??????? ??? ?????:
DROP ORDERS;
?? ?????? ????? ???? ?????? ?? ?????????? ??????? ????? PHP ??? ?????? ? ???????? MySQL. ???? ??????? ? ??????????? ???? ??? ????????? ?? ?????? dhsilabs@mail.ru.
 |
