2. SQL.
. .
, SQL Server. SQL Server . :
àà
:
select
TitleOfCourtesy + LastName + ' ' + FirstName as FIO
from Employees
, . , , , . LEFT(), :
select
TitleOfCourtesy + LastName + ' ' + LEFT(FirstName, 1) + '.' as FIO
from Employees
, , YEAR(). . :
select * from Orders
where YEAR(OrderDate) = 1998
DATEPART(), (, , , ..):
select * from Orders
where DATEPART(yy, OrderDate) = 1998
,
1.
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| weekday | dw |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
:
select GETDATE() -
select Region, ISNULL(Region, '---') from Employees NULL, ,
select
BirthDate, LastName + ' ' + CONVERT(VARCHAR(15), BirthDate, 104) AS [FB],
'ID = ' + CAST(EmployeeID as VARCHAR(3)) + ' F = ' + LastName AS [IdF]
from Employees
: CONVERT CAST.
CONVERT 3 : , ; ; .
CAST .
, , SQL Server , .
, :
select
MIN(UnitPrice) as [ ],
MAX(UnitPrice) as [ ],
COUNT(*) as [ ],
|
|
|
SUM(UnitPrice)
from [Order Details]
.
, .
, . , [Order Details]. OrderID OrderID. COUNT.
select OrderID, COUNT(*) as [ ]
from [Order Details]
GROUP BY OrderID
, ORDER BY, , .
, :
select ProductID, COUNT(*) as [ ]
from [Order Details]
GROUP BY ProductID
, ProductID.
select ProductID, MIN(UnitPrice) as [ ]
from [Order Details]
GROUP BY ProductID
, MIN, MAX, AVG, SUM , , ..
, , , :
select SUM(Quantity*UnitPrice)
from [Order Details]
WHERE Discount = 0
. , WHERE:
select OrderID, SUM(Quantity*UnitPrice)
from [Order Details]
WHERE Discount = 0
GROUP BY OrderID
. .
.
1.
2.
3.
4. 5
, . , , . , , , , . WHERE, HAVING:
select OrderID, SUM(Quantity*UnitPrice)
from [Order Details]
GROUP BY OrderID
HAVING SUM(Quantity*UnitPrice) > 1000
.
1. , , ,
2. , , 250
.
, , : . . , Northwind: .
|
|
|
, Territories Region RegionID. Region , Territories . - RegionID Region RegionID Territories. , , , . Territories, :
, , , Region. .
:
1. INNER JOIN.
2. : LEFT JOIN, RIGHT JOIN, FULL JOIN.
3. .
MS SQL Server 2005:
( , , = <>). .
. , students, courses.
. , .
FROM, .
LEFT JOIN LEFT OUTER JOIN
, LEFT OUTER, , . , NULL .
RIGHT JOIN RIGHT OUTER JOIN
. . NULL , .
FULL JOIN FULL OUTER JOIN
. , , NULL. , .
. . .
, , :
select * from Territories t
LEFT JOIN Region r ON t.RegionID = r.RegionID
Region Territories. , , . . t r. ON , : t.RegionID = r.RegionID. , , . , :
|
|
|
select t.*, RegionDescription from territories t left join
region r on t.RegionID = r.RegionID
t RegionDescription r. : , , , , . , :
select TerritoryID, TerritoryDescription, RegionID, RegionDescription
from territories t left join
region r on t.RegionID = r.RegionID
209, 16, 1, 1
"RegionID".
RegionID , . , , , :
select TerritoryID, TerritoryDescription, t.RegionID, RegionDescription
from territories t left join
region r on t.RegionID = r.RegionID
, , . , , :
select d.ProductID, ProductName, SUM(Quantity)
from [Order Details] d left join
[Products] p on p.ProductID = d.ProductID
group by d.ProductID, ProductName
:
1.
select * from Territories t
INNER JOIN Region r ON t.RegionID = r.RegionID
where RegionDescription = 'Northern'
2.
select * from Territories t
INNER JOIN Region r ON t.RegionID = r.RegionID
where RegionDescription = 'Northern' and TerritoryDescription LIKE 'P%'
3.
select * from Orders o
left join [Order Details] d on o.OrderID = d.OrderID
left join [Products] p on p.ProductID = d.ProductID
where CategoryID = 4
.
1. , , .
2. , 1998 , , .
3. , .
4. , 1998 .
.
SQL , : , . INSERT, UPDATE, DELETE.
INSERT . : INSERT INTO <Table_Name> (<columns>) VALUES (<values>).
Categories :
INSERT INTO Categories (CategoryName, [Description])
VALUES (' ', ' ')
, . INSERT : , NULL ( DEFAULT); . CategoryID , INSERT.
UPDATE . WHERE . ! WHERE , !
, , :
|
|
|
UPDATE Categories SET CategoryName = ' '
WHERE CategoryID = 9
:
UPDATE Categories SET CategoryName = ' ',
CategoryDescription = ' '
WHERE CategoryID = 9
DELETE. WHERE . :
DELETE FROM Categories WHERE CategoryID = 9
