SQL

Admin · Posted: Tue 17:44, 05 Dec 2017 Post subject: SQL

nieistotna kolejnosc wierszy/krotek ani kolumn/atrybutow

wiele wiele zawsze jako dwie relacje 1:N

pelna nazwa kazdego obiektu jest czteroczlonowa: <nazwa_serwera>.<nazwa_bazy_danych>.<nazwa_schematu>.<nazwa_obiektu>

sprawdzanie jaki schemat jest domy¶lny

select SCHEMA_NAME() as DefaultSchema;

wybranie calej tabeli z okreslonego schematu z okreslonej bazy

use AdventureWorks2008
go

Select * from Person.Person

tworzenie wlasnych baz danych

CREATE DATABASE Testowa

informacje o liczbie pracowników, pracuj±cych w zespo³ach wieloosobowych (wiêcej ni¿ jeden pracownik) w ramach Dzia³u IT. Koñcowy wynik posortujemy rosn±co po liczbie pracowników.

SELECT Zespol, COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy
WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1
ORDER BY LiczbaPracowników

mog± siê w SELECT pojawiæ np.: DISTINCT – czyli usuwanie duplikatów oraz TOP – ograniczenie wyników

SELECT * FROM (

VALUES (1, 'pierwszy'), (2, 'drugi'), (3, 'trzeci')

) AS Tabelka(Id,Nazwa)

SELECT *
FROM (
SELECT 'raz' AS kolA
UNION
SELECT 'dwa' ) AS Tabelka

select FirstName, LastName from HeadQuarter.AdventureWorks2008.Person.Person

wyswietlanie trzech najbardziej wartosciowych zamowien kazdego klienta wedlug wartosci zamowienia malejaco

USE AdventureWorks2008
Go

SELECT *
FROM
( -- zbiór elementów jako wynik kwerendy (podzapytania)

SELECT o.TotalDue, p.BusinessEntityID, p.LastName, p.FirstName ,
ROW_NUMBER() OVER(Partition by p.BusinessEntityID Order by o.TotalDue DESC) as BestNo

FROM [Person].[Person] p inner join [Sales].[SalesOrderHeader] o
on p.BusinessEntityID= o.SalesPersonID

) as TabelaPosrednia

WHERE BestNo<4

teraz sortuje je wedlug dwoch cech:

ROW_NUMBER() OVER(PARTITION BY PSC.Name ORDER BY P.ListPrice DESC , P.Name ) AS PriceRank

STRONNICOWANIE:

with paging as (

SELECT P.Name Product, P.ListPrice,
ROW_NUMBER() OVER( ORDER BY P.ListPrice DESC) AS Pozycja
FROM Production.Product P

)
-- np. 10 strona, wiersze od 91-100
select * from paging where Pozycja between 91 and 100
go

& AND, | OR (lub) ^ XOR (albo)

select Name, Color, ListPrice
from Production.Product
WHERE ( Color = 'White' OR Color = 'Grey' ) and ListPrice > 9

-- wszystkie rekordy, dla których warto¶æ kolumny City zaczyna siê od „par”
select distinct city // distinct usuwa ze zbioru wynikowego duplikaty
from Person.Address
where City LIKE 'par%' // zastepuje ciag znakow
where City LIKE '%par%'
where City LIKE '_par%' // zastepuje jeden dowolny znak
where City LIKE '%ring[td]%' // Wszystkie rekordy zawieraj±ce w kolumnie City „ringt” lub „ringd”.
where City LIKE '%ring[^td]%' // zawiera ring i kolejnym znakiem nie jest t lub d.
where City LIKE '[ab][n-p]%' // elementy zaczynaj±ce siê na a lub b, i maj±ce drug± literê z przedzia³u n – p.

Te dwa zapisy sa rownowazne:

SELECT SalesOrderNumber, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE TotalDue >= 870 AND TotalDue <= 1000
And OrderDate >= '2002-01-01' AND OrderDate <= '2002-06-30'

SELECT SalesOrderNumber, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE TotalDue BETWEEN 870 AND 1000
and OrderDate BETWEEN '2002-01-01' AND'2002-06-30'

Te zapisy sa rownowazne:
where FirstName in('Joan', 'John' ,'Joanna') i where FirstName = 'Joan' or FirstName = 'John' or FirstName = 'Joanna'

where FirstName like 'Jo[ah]%' and LEN(FirstName)<7 //dlugosc stringa

ANY (to to samo co SOME):

SELECT Name,ListPrice
FROM Production.Product
WHERE ListPrice >= ANY // zwraca wartosci wieksze badz rowne od ktoregokolwiek z wynikow podzapytania
(
SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID)

WHERE ListPrice >= ALL // zwraca wartosci wieksze badz rowne od wszystkich wynikow podzapytania

SELECT Title, Country, COUNT(*) as EmQty, // Count(*) pokazuje nam liczebnosc kazdej grupy
AVG( YEAR(Getdate()) - YEAR(BirthDate) ) as AvgAge, // sr wiek, getdate() zwraca aktualna date, year wyciaga rok
MAX( YEAR(Getdate()) - YEAR(BirthDate) ) as MaxAge, // maks wiek
MIN( YEAR(Getdate()) - YEAR(BirthDate) ) as MinAge // min wiek
from dbo.Employees
GROUP BY Country,Title
ORDER BY AvgAge

trzy sposoby przypisania nowych nazw kolumnom:
select LastName AS Nazwisko, FirstName Imie, LastName [Pierwsze Imiê] // [] potrzebne gdy nowa nazwa ma spacje

wszystkie kolumny z jednej tabeli:
select p.*
from dbo.Products as p
inner join dbo.Categories as c on p.CategoryID = c.CategoryID

deklaracja dodatkowego atrybutu (dodawanie kolumny do tabeli):
select LastName, FirstName, 'Oddzia³ 1' as Division
from dbo.Employees

Ró¿nica dat (np. w latach) to datediff:
select LastName, FirstName, BirthDate,
YEAR(BirthDate) as BirthYear,
DATEDIFF(yy, BirthDate, getdate()) as Years
from dbo.Employees

Wykonywanie obliczeñ (z zadanym zaokr±gleniem):
select UnitPrice, Quantity, Discount,
Round((UnitPrice*Quantity*(1-Discount))*1.23 , 2) as TotalBrutto
from dbo.[Order Details]

z³±czenie tekstów, operacje na stringach, skracanie nazw:
select LastName + ' ' + FirstName as ImieNazwisko,
LEFT(FirstName,1) + '. ' + LastName as ImieNazwisko2
from dbo.Employees

CASE WHEN THEN i wpisywanie wyników do nowej tabeli:
CASE WHEN Gender = 'F' THEN 'Female'
ELSE 'Male' END as Gender,
CASE WHEN MaritalStatus = 'M' THEN 'Married' END as MarriedSt

Deklarowanie zmiennych:
USE NORTHWIND
GO

declare @stawaVat decimal (2,2)
Set @stawaVat = 0.23

select @stawaVat as Stawka_VAT

select UnitPrice, Quantity, Discount,
(UnitPrice*Quantity*(1-Discount))*(@stawaVat+1) as TotalBrutto // mozemy uzywac naszych zmiennych do obliczen ale z @
from dbo.[Order Details]

Zmienne globalne systemowe:
Select @@Version as Wersja, @@Servername as NazwaSerwera

select DISTINCT FirstName // usuwa duplikaty
select TOP 10 FirstName, LastName // wyswietli pierwsze 10
select TOP 10 PERCENT FirstName, LastName // wysw pierwsze 10% wynikow

Z "remisami" (podium, zawody), [zawsze z order by!!!]:
select TOP 10 WITH TIES FirstName, LastName
from Person.Person
order by LastName

jak mamy

select FirstName as Imie,
LastName as Nazwisko,

to order by Nazwisko, FirstName to to samo co order by 2, 1

mozemy sortowac po kolumnach nieokreslonych w select, czyli (kontynuujac przyklad):

order by Nazwisko, FirstName, BusinessEntityID

sortowanie w kolejnosci losowej:

select FirstName as Imie,
LastName as Nazwisko,
PersonType
from Person.Person
order by NEWID()

albo

select NEWID() as RandomValue ,FirstName as Imie,
LastName as Nazwisko,
PersonType
from Person.Person
order by 1

sortuje rosnaco domyslnie a jesli chcemy malejaco to lecimy:
order by Nazwisko, FirstName DESC

sortowanie stringow (a<A, a<±, PACZEK jest przed p±czek):

select * from collate_test
order by opis collate Polish_CS_AI

sp³aszczenie stringów do sortowania, czyli zignorowanie polskich znaków i du¿ych liter:

select * from collate_test
order by opis collate Azeri_Cyrillic_90_CI_AI

zbior wynikowy z pominieciem pierwszych n wierszy:
ORDER BY RN OFFSET 1000 ROWS

Czwarta strona wyników na ktorej wyswietlamy 10 rekordow:

ORDER BY RN OFFSET 30 ROWS
--FETCH FIRST 10 ROWS ONLY -– FIRST/NEXT oraz ROW/ROWS
-- s± synonimami zgodnymi ze standardem ANSI
-- mo¿emy je stosowaæ wymiennie
FETCH NEXT 10 ROWS ONLY

wybranie tych pracownikow ktorzy maja samochod (laczenie wewnetrzne tabel)
SELECT *
FROM dbo.EMP as e INNER JOIN dbo.CAR as c ON e.IdPrac=c.IdPrac

laczenie zewnetrzne:
SELECT e.Imie, e.Nazwisko, e.Stanowisko , c.Marka
-- LEFT JOIN oraz LEFT OUTER JOIN oznaczaj± dok³adnie to samo
FROM dbo.EMP e LEFT JOIN dbo.CAR c ON e.IdPrac=c.IdPrac // wtedy mamy pracownikow i informacje czy ma samochod a nie tylko tych

pracownikow ktorzy maja samochod

select c.Marka, c.NrRej, c.Rocznik, e.Imie + ' ' + e.Nazwisko as Pracownik
from dbo.EMP e RIGHT JOIN dbo.CAR c on e.IdPrac=c.IdPrac // tutaj dokladne informacje o samochodach i imienazw pracownika

FULL JOIN to suma right i left:
SELECT e.Imie, e.Nazwisko, e.Stanowisko , c.Marka
-- FULL JOIN to skrót od FULL OUTER JOIN
FROM dbo.EMP e FULL JOIN dbo.CAR c ON e.IdPrac=c.IdPrac

opcja Include Actual Execution Plan pozwala sledzic plan wykonania polecen

OPTION (FORCE ORDER) dodane na koncu kodu wymusza wykonywanie polecen od lewej do prawej

jakiekolwiek porownanie z nullem daje null daltego jak piszemy zeby wyswietlil te ktore != null to nie wyswietli niczego bo to daje null. porownanie null z null tez daje null

dlatego zamiast tego musimy uzywac:
where Region IS NOT NULL

Wszystkie operacje z udzia³em warto¶ci nieznanej – daj± zawsze w wyniku NULL

NULL cd- zastapienie pustym stringiem

SELECT FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName AS FullName
FROM Person.Person

COALESCE ( wartosc_1 , wartosc_2 … wartosc_n ) – zwraca pierwsz± nie-nullow± warto¶æ z listy

NULLIF (wartosc1, wartosc2) – porównuje dwie warto¶ci i zwraca NULL je¶li s± równe

operacje pionowe:
UNION – suma zbiorów
EXCEPT – odejmowanie zbiorów
INTERSECT – iloczyn (czê¶æ wspólna)

ABC i CDE:
UNION: ABCDE
UNION ALL: ABCCDE
EXCEPT: AB
INTERSECT: C (istnieje tez intersect distinct usuwajacy duplikaty)

laczenie kwerend o roznej liczbie kolumn:

select kol1, kol2, kol3
From tabela1
union
select kol1, kol2, NULL
from tabela2

WIELOKROTNE GRUPOWANIE
GROUP BY ROLLUP( Year(Orderdate) , DatePart(q,Orderdate) ) // 2 rzeczy wpisane wiec 3 operacje
GROUP BY CUBE(Year(Orderdate) , DatePart(q,Orderdate)) // 2 rzeczy wpisane, wiec 2^2 operacji

SUBSTRINGI WYCINKI

SELECT PESEL FROM moje_dane
substr(PESEL,1,2) as rok
1900 + cast(substr(PESEL,1,2) as float) as rok, //(1,2) oznacza ze zaczynamy od pierwszej cyfry i bierzemy dwie kolejne

KWANTYLE
quantile(AVG_DOCH_NETTO,75)

liczy nam wszystkie rekordy danych typów wyksza³cenia, COUNT(*) zlicza nam ka¿d± kolumnê tabeli

SELECT COUNT(WYKSZTALCENIE) FROM moje_dane
GROUP BY WYKSZTALCENIE

SORTOWANIE PO SUBSTRINGACH
order by substr(PESEL, length(PESEL)-1,2)

SUMA BIE¯¡CA CZÊ¦CIOWA wyliczanie sumy bie¿±cej dla warto¶ci kolumny

SELECT SUM(AVG_DOCH_NETTO) FROM moje_dane
AS RUNNING_TOTAL

REGEXP - sprawdzenie czy dane w kolumnie maj± po¿±dany format

-- . - obojêtna warto¶æ, \d - cyfry, \s - spacja b±d¼ tab, mo¿emy te¿ po prostu wpisac spacjê,
-- \w - litery, \D,\S,\W - zaprzeczenie dowolnego po prostu z wielkiej litery
-- 3? - czy wystêpuje b±d¼ nie dana warto¶æ, (0000)? - czy wystêpuj± b±d¼ nie, wiele warto¶ci
-- wtedy zwraca nam warto¶æ 1 je¶li dana warto¶æ wystêpuja lub nie wystêpuje
-- poniewa¿ kropka jest znakiem specjalnym musimy u¿yæ \. aby program wiedzia³, ¿e chodzi nam o kropkê
-- w ten sposób mo¿emy wyszukaæ niepo¿±dane warto¶ci: WHERE WOJ_ZAMIESZ NOT REGEXP ...
-- 0{n} - chcemy aby warto¶æ 0 wyst±pi³a przed naszym ci±giem znaków dok³adnie n razy
-- 0{n,m} - od n do m razy
-- \s+ - wystêpuje jeden lub wiêcej razy
-- REGEXP '[a±bcædeêfghijkl³mnñoóprs¶tuwxyz¿¼]\w' w ten sposób zadajemy zbiór, z którego chcemy ¿eby wystêpowa³y nam znaki
-- '(\w{3})\.\s\w{5,15}' - je¶li wrzucimy jakie¶ wyra¿enie w nawias okrag³y to program zapamiêtuje dok³andnie to s³owo,
--które tam jest, pózniej je¶li chcemy zadaæ w³a¶nie to s³owo to u¿ywamy z \1 - program numeruje te nawiasy lub te¿
-- (?1) - je¶li chcemy odwo³aæ siê nie do warto¶ci, a to samej formu³y

WHERE TEL_KOM NOT REGEXP '\+48\d{9}' AND TEL_KOM NOT REGEXP '\d{9}'

-- .* - zero lub dowolna ilo¶æ powtórzeñ dowolnego znaku, zadajemy z jakiej zmiennej, 1 odnosi siê do tej warto¶ci któr± chcemy stamt±d wyci±gn±æ

SELECT WOJ_ZAMIESZ, REGEXP_GROUP ('.*(\d{9}).*',WOJ_ZAMIESZ, 1) FROM moje_dane

? 0 or 1, greedy
-- ?+ 0 or 1, possessive
-- ?? 0 or 1, lazy
-- * 0 or more, greedy
-- *+ 0 or more, possessive
-- *? 0 or more, lazy
-- + 1 or more, greedy
-- ++ 1 or more, possessive
-- +? 1 or more, lazy
-- {n} exactly n
-- {n,m} at least n, no more than m, greedy
-- {n,m}+ at least n, no more than m, possessive
-- {n,m}? at least n, no more than m, lazy
-- {n,} n or more, greedy
-- {n,}+ n or more, possessive
-- {n,}? n or more, lazy

Greedy: As Many As Possible (longest match)
Lazy: As Few As Possible (shortest match)
Possessive: Don't Give Up Characters

#terauser = 'ti08498'
#terapass = 'xxxxxx'

UPDATE pracownicy
SET pensja = pensja * 1.1
WHERE staz > 2;

Podwy¿sza o 10% pensjê (SET pensja = pensja * 1.1) pracownikom, których sta¿ jest wiêkszy ni¿ 2 (np. lata).

DELETE FROM pracownicy
WHERE imie = 'Jan' AND nazwisko = 'Kowalski';

Usuwa z tabeli „pracownicy” wszystkie wiersze (rekordy) dotycz±ce pracownika o imieniu „Jan” i nazwisku „Kowalski” (czyli takie, w których pole "imiê" ma warto¶æ Jan, a pole "nazwisko" – Kowalski).

CREATE TABLE pracownicy
(
imie varchar(255),
nazwisko varchar(255),
pensja float,
staz int
);

Tworzy tabelê „pracownicy” zawieraj±c± pola tekstowe zmiennej d³ugo¶ci (varchar) o nazwach „imie” (imiê) i „nazwisko”, o maksymalnej d³ugo¶ci 255 znaków, zapisan± za pomoc± liczby rzeczywistej (float od ang. floating point) pensjê oraz zapisany za pomoc± liczby ca³kowitej (int od ang. integer) sta¿.

DROP TABLE pracownicy;

Usuwa z bazy tabelê „pracownicy”.

ALTER TABLE pracownicy
ADD dzial varchar(255);

Dodaje do struktury tabeli „pracownicy” kolumnê „dzial” (dzia³), jako pole tekstowe o d³ugo¶ci maks. 255 bajtów.

-- Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )
CAST(9.5 AS decimal(6,4)) AS decimal;

-- Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
CONVERT(decimal(6,4), 9.5) AS decimal;

IIF:
DECLARE @a int = 45, @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;

Result
--------
TRUE [/b]

CHOOSE:

USE AdventureWorks2012;
GO
SELECT ProductCategoryID, CHOOSE (ProductCategoryID, 'A','B','C','D','E') AS Expression1
FROM Production.ProductCategory;

Last value i first value

USE AdventureWorks2012;
GO
SELECT Department, LastName, Rate, HireDate,
LAST_VALUE(HireDate) OVER (PARTITION BY Department ORDER BY Rate) AS LastValue
FROM HumanResources.vEmployeeDepartmentHistory AS edh
INNER JOIN HumanResources.EmployeePayHistory AS eph
ON eph.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = edh.BusinessEntityID
WHERE Department IN (N'Information Services',N'Document Control');

Lag pomaga braæ poprzedni± (lub któr±¶ z poprzednich) warto¶æ z danej kolumny: lag(sk±d, ile wierszy wstecz, co zwrocic jesli null)

LEAD dziala podobnie tylko w druga strone (nastepne wiersze)

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

stdev odchylenie standardowe
stdevp odchstand w populacji
SELECT STDEVP (DISTINCT Quantity) AS Distinct_Values, STDEVP(Quantity) AS All_Values

suma czê¶ciowa i ¶rednia czê¶ciowa
movingavg i cumulativetotal w sqlfunctions

percentyle (mozna tym mediane i kwantyle)
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ph.Rate)
OVER (PARTITION BY Name) AS MedianCont

,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY ph.Rate)
OVER (PARTITION BY Name) AS MedianDisc

ta druga zwraca wartosc ktora na pewno jest w zbiorze a pierwsza usrednia dwie srodkowe (?)

zwracanie jakiegos wycinka daty np. roku miesiaca kwartalu dnia (dzien) sekundy minuty tygodnia

datename(datepart, date)

zwraca ostatni dzien miesiaca

DECLARE @date DATETIME = '12/1/2011';
SELECT EOMONTH ( @date ) AS Result;

dodawanie wierszy rekordów do tabeli
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

wartosci bezwzgledne (moduly)
SELECT ABS(-1.0), ABS(0.0), ABS(1.0);

najwieksza calkowita wieksza badz rowna zadanej (sufit)
i podloga
CEILING ( numeric_expression )
FLOOR ( numeric_expression )

eksponenta liczba eulera logarytm ln logarytm naturalny
SELECT EXP( LOG(20)), LOG( EXP(20))

potegi potegowanie
POWER ( float_expression , y )

okreslanie znaku (-1 dla ujemnych 0 dla 0 1 dla dodatnich, signum)
SIGN ( numeric_expression )

SELECT CONCAT(hd, ' Gb') volume FROM PC WHERE model=1232;
to to samo co
SELECT CAST(hd AS VARCHAR) + ' Gb' volume FROM PC WHERE model = 1232;

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

tworzenie w³asnych funkcji funkcje

CREATE FUNCTION dbo.LiczbaDniRoboczych
(
-- Funkcja nie uwzglêdnia ¶wi±t, innych dni wolnych poza sobot± i niedziel±
-- okre¶lenie parametrów wej¶ciowych – s± opcjonalne
-- u nas konieczne s± dwa, okre¶laj±ce zakres dat
@StartDate datetime,
@EndDate datetime
)
-- okre¶lenie typu zwracanej warto¶ci (to obowi±zkowo)
RETURNS int
AS
-- cia³o funkcji
BEGIN

RETURN (DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = DATENAME(dw,6) THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = DATENAME(dw,5) THEN 1 ELSE 0 END)

END;

wywo³ywanie funkcji wywo³anie

SELECT OrderId, OrderDate, ShippedDate,
dbo.LiczbaDniRoboczych(OrderDate, ShippedDate) as WorkingDays

pivot
select *
from
(
select *
from
(
select top 3 with ties trip_no, ROW_NUMBER() OVER(Order by trip_no DESC) as BestNo
from trip
order by trip_no desc
) as a

union

select *
from
(
select top 3 with ties trip_no, 3+ ROW_NUMBER() OVER(Order by trip_no asc) as BestNo
from trip
order by trip_no asc
) as b
) as c

pivot(min(trip_no) for bestno in([4],[5],[6],[3],[2],[1])) as pvt