, ,
FROM. , . JOIN. . ( ). --:
Employees | ||||
TabNum | Name | Position | DeptNum | Salary |
. | ||||
Departments | ||
DeptNum | City | Name |
Cities | |
City | Name |
SELECT Employees.TabNum, Employees.Name,Departments.Name
FROM Employees
JOIN Departments ON Employees.DeptNum =
Departments.DeptNum
:
SELECT Employees.TabNum, Employees.Name, Departments.Name, Cities.Name
FROM Employees
JOIN Departments ON Employees.DeptNum = Departments.DeptNum
JOIN Cities ON Departments.City = Cities.City
:
:
SELECT Table1.Field1, Table2.Field2
FROM Table1
JOIN Table2
ON Table2.ID1 =Table1.ID1
AND Table2.ID2 =Table1.ID2
AND .
:
JOIN | . , |
LEFT JOIN | . Table1 Table2. , Table2 |
RIGHT JOIN | . Table2 Table1. , Table1 |
FULL JOIN | . . Table1 Table2. Table2 . Table2, Table1, . Table1 . |
CROSS JOIN | Cartesian product. Table1 Table2. . |
. :
Table1 | Table2 | |||
Key1 | Field1 | Key2 | Field2 | |
A | AAA | |||
BBB | ||||
C | CCC | |||
DDD |
SELECT Table1.Field1, Table2.Field2
|
|
FROM Table1
JOIN Table2 ON Table1.Key1 = Table2.Key2
:
A | AAA |
B | BBB |
B | CCC |
SELECT Table1.Field1, Table2.Field2
FROM Table1
LEFT JOIN Table2 ON Table1.Key1 = Table2.Key2
:
A | AAA |
B | BBB |
B | CCC |
C |
SELECT Table1.Field1, Table2.Field2
FROM Table1
RIGHT JOIN Table2 ON Table1.Key1 = Table2.Key2
:
A | AAA |
B | BBB |
B | CCC |
DDD |
SELECT Table1.Field1, Table2.Field2
FROM Table1
FULL JOIN Table2 ON Table1.Key1 = Table2.Key2
:
A | AAA |
B | BBB |
B | CCC |
DDD | |
C |
SELECT Table1.Field1, Table2.Field2
FROM Table1
CROSS JOIN Table2
:
A | AAA |
A | BBB |
A | CCC |
A | DDD |
B | AAA |
B | BBB |
B | CCC |
B | DDD |
C | AAA |
C | BBB |
C | CCC |
C | DDD |
WHERE
WHERE. WHERE :
WHERE [ NOT ] <1>[ AND | OR <2>]
:
< , >
< > < , >
IS [ NOT ] NULL
[ NOT ] LIKE <>
[ NOT ] IN (< >)
[ NOT ] BETWEEN < > AND < >
:
< | |
<= | |
<> | |
> | |
>= | |
= |
:
SELECT * FROM Table WHERE Field > 100
SELECT * FROM Table WHERE Field1 <= (Field2 + 25)
IS [NOT] NULL [] :
SELECT * FROM Table WHERE Field IS NOT NULL
SELECT * FROM Table WHERE Field IS NULL
, SQL, , - . NULL -. NULL - , . NULL . NULL , ( SQL) , . . - SQL .
[ NOT ] LIKE [] . % () , _ () .
SELECT * FROM Employees WHERE Name LIKE %
:
SELECT * FROM Employees WHERE Name LIKE __%
:
[NOT] IN [] :
SELECT * FROM Employees WHERE Position IN (, )
[ NOT ] BETWEEN [] :
|
|
SELECT * FROM Employees WHERE Salary BETWEEN 200 AND 500
, 200 .. 500 ..
AND, OR NOT:
SELECT *
FROM Employees
WHERE Position IN (, )
AND Salary BETWEEN 200 AND 500
SELECT *
FROM Employees
WHERE (Position = OR Position =
)
AND Salary BETWEEN 200 AND 500
SELECT *
FROM Employees
WHERE NOT (Position = OR Position =
)
ORDER BY
ORDER BY SELECT . :
ORDER BY Field1 [ASC | DESC] [, Field2 [ASC | DESC] ] [, ]
ASC , DESC . ASC DESC , ASC. ,
:
SELECT *
FROM Employees
WHERE Position =
ORDER BY Salary DESC
( ) ( ):
SELECT *
FROM Employees
ORDER BY DeptNum ASC, Salary DESC
ASC , :
SELECT *
FROM Employees
ORDER BY DeptNum, Salary DESC
, , . :
SUM(Field) | |
MIN(Field) | |
MAX(Field) | |
AVG(Field) | |
COUNT(*) | |
COUNT(Field) |
, , , :
SELECT MAX (SALARY)
FROM Employees
:
SELECT COUNT (*)
FROM Employees
GROUP BY
, , . , GROUP BY:
GROUP BY Field1 [, Field2] [, ]
, :
SELECT DeptNum, MAX (SALARY)
FROM Employees
GROUP BY DeptNum
MAX DeptNum.
HAVING
HAVING. , 1000 ..:
SELECT DeptNum, MAX (SALARY)
FROM Employees
GROUP BY DeptNum
HAVING MAX (SALARY) > 1000
, HAVING WHERE . WHERE , , GROUP BY. HAVING.
:
- ;
- ;
- .
INSERT
INSERT. :
INSERT INTO < > [(< >)]
|
|
VALUES (< >)
, :
INSERT INTO Employees(TabNum, Name, Position, DeptNum,
Salary)
VALUES (45, , , 15, 850)
Employees :
Employees | ||||
TabNum | Name | Position | DeptNum | Salary |
- , (NULL):
INSERT INTO Employees(TabNum, Name, DeptNum, Salary)
VALUES (45, , 15, 850)
Employees :
Employees | ||||
TabNum | Name | Position | DeptNum | Salary |
VALUES . INSERT . VALUES , . :
INSERT INTO Employees VALUES (45, ,
, 15, 850)
:
INSERT INTO Employees VALUES (45, , 15, 850)
, .
NULL. :
INSERT INTO Employees VALUES (45, , NULL, 15, 850)
, Position . ,
:
INSERT INTO Employees(TabNum, Name, Position, DeptNum,
Salary)
VALUES (45, , NULL, 15, 850)
, INSERT . :
INSERT INTO < > [(< >)]
< SELECT>
:
INSERT INTO Table1(Field1, Field2)
SELECT Field3, (Field4 + 5) FROM Table2
DELETE
, DELETE:
DELETE FROM < > [ WHERE < >]
WHERE, . , . WHERE SELECT DELETE .
DELETE:
DELETE FROM Employees
DELETE FROM Employees WHERE TabNum = 45
UPDATE
INSERT UPDATE:
UPDATE < >
SET < > = < >, < > =
< >,
WHERE < >]
DELETE, WHERE . . :
UPDATE Employees SET Salary = Salary + 100
UPDATE Employees
SET Position = , Salary = 1000
WHERE TabNum = 45 AND Position IS NULL
|
|