Benutzer:MovGP0/SQL/Select
aus Wikipedia, der freien Enzyklopädie
< Benutzer:MovGP0 | SQL
SELECT
[Database].[Schema].[Object]
USE: Connect to Database
USE AdventureWorks;
Retrieve specific Columns
SELECT LoginID, SureName, FamilyName
FROM HumanResources.Employee;
Retrive all Columns
SELECT *
FROM HumanResources.Employee;
Operators: Speficy the Rows to be returned
SELECT Title, FirstName, LastName
FROM HumanResources.Employee
WHERE Title = 'Ms.'
AND ( LastName = 'Miller' OR LastName = 'Smith' );
| Operator | Description |
|---|---|
| != | not equal |
| !> | not greater than (less or equal) |
| !< | not less than (greater or equal) |
| < | less than |
| <= | less than or equal |
| <> | not equal |
| = | equal |
| > | greater than |
| >= | greater than or equal |
Rename Output Columns
SELECT BusinessEntityID AS "Employee ID", -- ISO Standard uses double quotes
VacationHours "Vacation", -- AS can be omitted
SickLeaveHours = [Sick Time] -- SQL Server also supports square brackets; AS can be replaced by =
FROM HumanResources.Employee;
Building Column from Expression
SELECT BusinessEntityID AS EmployeeID,
VacationHOurs + SickLeaceHours AS AvailableTimeOff -- omitting AS results in column without name
FROM HumanResources.Employee;
Providing Shorthand for Tables
SELECT E.BusinessEntityID AS "Employee ID",
E.VacationHours AS "Vacation"
E.SickLeaveHours AS "Sick Time"
FROM HumanResources.Employee AS E;
NOT: Negating a Search Condition
SELECT Title, FirstName, LastName
FROM Person.Person
WHERE NOT (Title = 'Ms.' OR Title = 'Mrs.');
BETWEEN: Specify a Range of Values
SELECT SalesOrderID, ShipDate
FROM Sales.SalesOrderHeader
WHERE ShipDate BETWEEN '2005-07-23T00:00:00' AND '2005-07-24T23:59:59'
IS: Check for NULL
SELECT ProductID, Name, Weight
FROM Production.Product
WHERE Weight IS NULL;
IN: Provide a List of Values
SELECT ProductID, Name, Color
FROM Production.Poduct
WHERE Color IN ('Red', 'Blue', 'Yellow')
equal to:
SELECT ProductID, Name, Color
FROM Production.Poduct
WHERE Color = 'Red' OR Color = 'Blue' OR Color= 'Yellow')
LIKE: Wildcards
SELECT ProductID, Name
FROM Production.Product
WHERE Name LIKE '%B'
| Wildcard | Description |
|---|---|
| * | any number of characters |
| % | 0 or more characters |
| _ | single character |
| [A-CF-F] | single character from a list |
| [^A-C] | single character not from list |
ESCAPE: define Escape Character
SELECT ProductID, Name
FROM Production.Product
WHERE Description LIKE '\%' ESCAPE '\' -- defines \ as the escape character. searches for '%'
ORDER BY: Order Results
SELECT p.Name, h.EndData, h.ListPrice
FROM Production.Product AS p
INNER JOIN Production.ProductListPriceHistory AS h
ON p.ProductID = h.ProductID
ORDER BY p.Name, h.EndDate; -- sort by Name; if the name is equal, sort by EndDate
ASC/DESC: Specify Sort Order
SELECT p.Name, h.EndData, h.ListPrice
FROM Production.Product AS p
INNER JOIN Production.ProductListPriceHistory AS h
ON p.ProductID = h.ProductID
ORDER BY p.Name DESC, -- or: DESCENDING
h.EndDate ASC; -- or: ASCENDING
Advanced Sort Order
SELECT p.ProductID, p.Name, p.Color
FROM Production.Product AS p
WHERE p.Color IS NOT NULL
ORDER BY CASE p.Color
WHEN 'Red' THEN NULL -- 'Red' will be sorted as null (first), but not filted by WHERE
ELSE p.Color
END;
variant:
SELECT p.ProductID, p.Name, p.Color
FROM Production.Product AS p
WHERE p.Color IS NOT NULL
ORDER BY CASE LOWER(p.Color) -- 'Red', 'red' and 'RED' are handeled the same
WHEN 'red' THEN ' ' -- 'red' ist ordered first
ELSE LOWER(p.Color) -- result column is still uppercase
END;
OFFSET/FETCH Paging
- make queries deterministic
- same query should result in same result later
- use order by
- isolate query with transaction when required
- think about trading consistency for performance
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; -- make sure that table does not change during transaction
BEGIN TRANSACTION;
SELECT ProductID, Name
FROM Production.Product
ORDER BY Name
OFFSET 8 ROWS -- skip 8 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows
COMMIT; -- commit transaction
