Bc. David Gešvindr
MCT | MSP | MCTS | MCITP | MCPD
1. Načítání dat pomocí příkazu SELECT
2. Modifikace dat
Popisuje chování dotazu
Jeho fáze, jejich vstupy a výstupy nutně
nesouvisí s vlastním fyzickým zpracováním
dotazu
SELECT DISTINCT TOP <select list>
FROM <left table>
<join type> JOIN <right table>
ON <join condition>
WHERE <where condition>
GROUP BY <group by list>
HAVING <having condition>
ORDER BY <order by list>
1
2
3
4
5
6
7 8 9
10
1 2 3
Customers
CustomerID Name
1 David
2 Jirka
3 Martin
Orders
OrderID CustomerID
1 1
2 2
3 1
4 4
XCROSS
JOIN
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 1 1
1 David 2 2
1 David 3 1
3 Martin 4 4
...
VT1
2
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 1 1
1 David 2 2
3 Martin 4 4
...
ON CONDITION Customers.CustomerID = Orders.CustomerID
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 1 1
1 David 3 1
2 Jirka 2 2
= INNER JOIN
VT1VT2
3
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 1 1
1 David 3 1
2 Jirka 2 2
VT2
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 1 1
1 David 3 1
2 Jirka 2 2
3 Martin NULL NULL
VT3
3
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 1 1
1 David 3 1
2 Jirka 2 2
VT2
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 1 1
1 David 3 1
2 Jirka 2 2
NULL NULL 4 4
VT3
3
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 1 1
1 David 3 1
2 Jirka 2 2
VT2
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 1 1
1 David 3 1
2 Jirka 2 2
3 Martin NULL NULL
NULL NULL 4 4
VT3
SELECT DISTINCT TOP <select list>
FROM <left table>
<join type> JOIN <right table>
ON <join condition>
WHERE <where condition>
GROUP BY <group by list>
HAVING <having condition>
ORDER BY <order by list>
1
2
3
4
5
6
7 8 9
10
4
V podmínce ON, WHERE a HAVING:
NULL chápáno jako FALSE
V check-constraint:
NULL chápáno jako TRUE
V GROUP BY, ORDER BY a UNIQUE
omezeních jsou si NULL rovny
Three-valued Logic
True FalseUnknown
VT3VT4
5
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 1 1
1 David 3 1
2 Jirka 2 2
3 Martin NULL NULL
VT4
GROUP BY Customers.CustomerID
GROUPS RAW
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 1 1
David 3 1
2 Jirka 2 2
3 Martin NULL NULL
VT5
6
HAVING COUNT(Orders.OrderID) > 0
VT5
GROUPS RAW
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 1 1
David 3 1
2 Jirka 2 2
3 Martin NULL NULL
GROUPS RAW
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 1 1
David 3 1
2 Jirka 2 2
VT6
7
Vyhodnocení výrazů v části SELECT
Vytvořené aliasy sloupců použitelné
až v dalších krocích
„All-at-once“
SELECT c1 + 1 AS e1, e1 + 1 AS e2
UPDATE Customers
SET FirstName = LastName,
LastName = FirstName
UPDATE dbo.T1 SET c1 = c1 + (SELECT MAX(c1)
FROM dbo.T1)
VT6VT7
8
Odstranění duplicitních řádků
Redundantní s GROUP BY
VT7VT8
9
Vrání n prvních výsledků z dodaného
tabulky
Pokud je definováno ORDER BY,
ovlivní pořadí řádků
WITH TIES
Deterministický x nedeterministický
VT8VT9
9
Seřazení množiny výsledků
Dle ANSI SQL:1999
při použití DISTINCT jsme omezeni
na sloupce vracené příkazem
SELECT (VT7), jinak použitelná i VT6
Vrací kurzor
Nelze použít jako table-expression
View, Inline table-valued function, subquery,
derived table nebo common table expression
VT9VC10
SELECT DISTINCT TOP <select list>
FROM <left table>
<join type> JOIN <right table>
ON <join condition>
WHERE <where condition>
GROUP BY <group by list>
HAVING <having condition>
ORDER BY <order by list>
1
2
3
4
5
6
7 8 9
10
Customers
CustomerID Name
1 David
2 Jirka
3 Martin
Orders
OrderID CustomerID
3 1
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 3 1
2 Jirka 2 2
SELECT TOP 1 *
WHERE Orders.CustomerID =
Customers.CustomerID
ORDER BY Orders.OrderID DESC
Customers
CustomerID Name
1 David
2 Jirka
3 Martin
Orders
OrderID CustomerID
3 1
Customers.
CustomerID
Customers.
Name
Orders.
OrderID
Orders.
CustomerID
1 David 3 1
2 Jirka 2 2
3 Martin NULL NULL
SELECT TOP 1 *
WHERE Orders.CustomerID =
Customers.CustomerID
ORDER BY Orders.OrderID DESC
CustomerID City Category
1 Brno Small
2 Brno Medium
3 Praha Large
4 Praha Large
5 Ostrava Medium
City Small Medium Large
Brno 1 1 0
Praha 0 0 2
Ostrava 0 1 0
City Small Medium Large
Brno 1 1 0
Praha 0 0 2
Ostrava 0 1 0
City Category CustomersCount
Brno Small 1
Brno Medium 1
Praha Large 2
Ostrava Medium 1