SQL
SQL
, SQL. SELECT , . :
SELECT column-list FROM table-list [WHERE where-clause] [ORDER BY order-by-clause]
SELECT SELECT FROM; , WHERE ORDER BY, .
SELECT , . (*) , :
SELECT *
:
SELECT CompanyName :
SELECT CompanyName, ContactName, ContactTitle
, , , :
SELECT Customers.CompanyName, Shippers.CompanyName
FROM
, , FROM, :
SELECT * FROM Customers
Customers.
CompanyName ContactName, SELECT:
SELECT CompanyName, ContactName FROM Customers
:
SELECT Customers.CompanyName, Shippers.CompanyName FROM Customers, Shippers
WHERE
, SELECT, WHERE, :
WHERE expression1 [{AND | OR} expression2 []]
, , CategoryID 4:
SELECT * FROM Products WHERE CategoryID = 4
WHERE , :
SELECT * FROM Products WHERE CategoryID = 2 AND SupplierID > 10
:
SELECT ProductName, UnitPrice FROM Products WHERE CategoryID = 3 OR UnitPrice < 50
:
SELECT ProductName, UnitPrice FROM Products WHERE Discontinued IS NOT NULL
IS NOT NULL , . WHERE , SQL. . 1.
1
|
|
<
<=
<>
=
>
>=
, , . 2.
2
ALL
ANY
BETWEEN ( )
IN
LIKE
. LIKE:
SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE M%
% () , _ () . :
SELECT CompanyName, ContactName FROM Customers WHERE CompanyName BETWEEN M AND N
. , , A C, SELECT:
SELECT CompanyName, ContactName FROM Customers WHERE CompanyName BETWEEN A AND D
LIKE, , . , , bl, :
SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE %bl%
%bl% , .
IN, , :
SELECT CompanyName, ContactName FROM Customers WHERE CustomerID IN (ALFKI, BERGS, VINET)
AND, OR NOT
AND , , . :
SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE S% AND Country = USA
, , S.
OR , , NOT , . , OR , , , S ( ):
SELECT CompanyName, ContactName FROM Customers WHERE CompanyName LIKE S% OR Region=CA
, CompanyName , , Region .
NOT. :
|
|
SELECT CompanyName, ContactName FROM Customers WHERE Country NOT IN (USA, UK)
, .
ORDER BY
ORDER BY () . ASC ( ) DESC ( ). . ORDER BY :
ORDER BY column1 [{ASC | DESC}] [, column2 [{ASC | DESC}] [,]
, SQL-:
SELECT LastName, FirstName, Title FROM Employees ORDER BY LastName, FirstName
(, ), DESC:
SELECT ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC
, , . . , , ( ):
SELECT ProductName, CategoryName FROM Products, Categories
, , , :
SELECT ProductName, CategoryName FROM Products, Categories WHERE Products.CategoryID = Categories.CategoryID
. :
SELECT column-list FROM table1, table2 WHERE table1.column1=table2.column2
Microsoft Access Microsoft SQL Server , , .
. , SQL (inner join) , :
SELECT ProductName, CategoryName FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
(outer joins) . :
SELECT ProductName, CategoryName FROM Products LEFT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID
(left outer join). (right outer join), ( ) :
SELECT ProductName, CategoryName FROM Products RIGHT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID
, , :
SELECT ProductName, CategoryName FROM Products FULL OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID
( ) CROSS JOIN :
|
|
SELECT ProductName, CategoryName FROM Products CROSS JOIN Categories
, c .
GROUP BY
GROUP BY, :
GROUP BY {column1} [, ]
, , CustomerID, CustomerID OrderID CustomerID:
SELECT Customers.CustomerID, COUNT (Orders.OrderID) FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID
SELECT COUNT, .
HAVING
HAVING , WHERE, . :
SELECT Customers.CustomerID, COUNT (Orders.OrderID) FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID HAVING COUNT(Orders.OrderID) >= 10
, , .
ALL DISTINCT
, . ALL DISTINCT SELECT. DISTINCT , , ALL , . , , , :
SELECT DISTINCT Country FROM Customers
, ALL . DISTINCT, , .
TOP
TOP n n . , :
SELECT TOP 10 * FROM PRODUCTS ORDER BY ProductName
10 , :
SELECT TOP 25 PERCENT * FROM PRODUCTS ORDER BY ProductName
.
SQL . SQL , . UPDATE, DELETE INSERT, .
UPDATE
UPDATE. c :
UPDATE tableSET column1 = expression1 [, column2 = expression2] [,] [WHERE criteria]
SET . , , 10 ., :
|
|
UPDATE Products SET UnitPrice = UnitPrice * 1.1 WHERE UnitPrice < 10
DELETE
DELETE, :
DELETE FROM table [WHERE criteria]
! WHERE , , .
, , , :
DELETE FROM Products WHERE Discontinued = 1
, SELECT , DELETE, , , . SELECT :
SELECT ProductName FROM Products WHERE Discontinued = 1
WHERE , . , , . SELECT, , :
SELECT CompanyName FROM Customers WHERE Customers.CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE OrderDate > 01/01/96)
SELECT DELETE:
DELETE FROM Customers WHERE Customers.CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE OrderDate > 01/01/96)
. SQL , , , .
INSERT
INSERT, :
INSERT [INTO] table ([column_list] { VALUES ({ DEFAULT | NULL | expression } } [, ])
, Customers :
INSERT INTO Customers (CustomerID, CompanyName) VALUES (XYZFO, XYZ Deli)
SQL , , (, .). : CREATE TABLE, ALTER TABLE DROP.
CREATE TABLE
CREATE TABLE, :
CREATE TABLE table (column1 type1 [(size1)][CONSTRAINT _ column-constraint1] [, column2 type2 [(size2)][CONSTRAINT _ column-constraint2] [,...]] [CONSTRAINT table-constraint1 _ [,table-constraint2 [,...]]]);
, ( ), ( ) , , ( CONSTRAINT). , Simple LastName, FirstName, EMail HomePage:
CREATE TABLE Simple (FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, EMail varchar(50), HomePage varchar(255))
PersonID, :
CREATE TABLE Simple (PersonID Integer NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, EMail varchar(50), HomePage varchar(255))
, LastName FirstName :
CREATE TABLE Simple (PersonID Integer NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, EMail varchar(50), HomePage varchar(255), CONSTRAINT SimpleConstraint UNIQUE (FirstName, LastName))
SELECT INTO, , , WHERE. :
SELECT * INTO NewOrders FROM Orders WHERE OrderDate > 1/1/97 NewOrders 1 1997 .
ALTER TABLE
ALTER TABLE. , . ALTER TABLE.
|
|
, :
ALTER TABLE table ADD [COLUMN] column datatype [(size)] [CONSTRAINT sinlge-column-constraint]
, , , , . , . , Phone Simple, , :
ALTER TABLE Simple ADD Phone varchar(30)
ALTER TABLE , :
ALTER TABLE table ADD CONSTRAINT constraint
, .
ALTER TABLE :
ALTER TABLE table DROP [COLUMN] column
COLUMN . :
ALTER TABLE Simple DROP Phone
, . ALTER TABLE:
ALTER TABLE table DROP CONSTRAINT index
:
ALTER TABLE Simple DROP CONSTRAINT PrimaryKey
DROP
DROP, . :
DROP TABLE table
:
DROP INDEX index ON table