Archiwa kategorii: RÓŻNE

Tworzenie indeksów.

Index to nic innego jak struktura danych wprowadzana do bazy dla zwiększenia prędkości wykonywania się operacji na tabelach. Najprościej index można porównać do spisu treści w książce. Nie ma sensu szukać jakiejś informacji w książce kartka po kartce jeśli możemy zajrzeć do spisu treści i odnaleźć intunieresujące nas informacje.

Struktura polecenia CREATE INDEX

CREATE INDEX nazwa_naszego_indeksu ON nazwa_tabeli(nazwa_kolumny)

Przykład:

CREATE INDEX idxtab_orders ON orders(id)

utworzyliśmy index na kolumnie id w tabeli orders i nadaliśmy mu nazwę idxtab_orders

Dodatkowe informacje:

W poleceniu Create index możemy zastosować m.in. argumenty

UNIQUE – argument nie dopuszcza dwóch identycznych wartości w kolumnie na której tworzymy index. Jeśli taka sytuacja wystąpi index nie zostanie utworzony.

CLUSTERED/NONCLUSTERED – argumenty określają sposób tworzenia indeksu.

Northwind – przykładowa baza danych

Northwind to przykładowa baza danych którą można pobrać ze strony Microsoft

http://download.microsoft.com/download/d/e/5/de57f203-3140-43e0-9473-d694d45bbd9b/SQL2000SampleDb.msi

Baza Northwind to baza danych firmy sprzedającej artykuły spożywcze. Zawiera ona informacje na temat: towarów, dostawców, klientów oraz zamówień klientów.

Diagramy bazy Northwind można zobaczyć po kliknięciu w poniższe linki

Poniżej wyjaśnienie do poszczególnych tabel w bazie Northwind:

Categories – kategorie oferowanych produktów

Products – informacja o oferowanych produktach (nazwy, dostawcy

ceny itp.)

Suppliers – informacja o dostawcach (nazwy, adresy itp.)

Shippers – informacja o spedytorach (firmach zajmujących się

dostawą towarów)

Customers – informacja o klientach

Employees – informacja o pracownikach

Orders – zamówienia składane przez klientów

OrderDetails – szczegóły zamówień (lista zamawianych produktów)

Territories – terytoria/obszary/miasta

Region – Regiony

EmployeeTerritories – informacja o terytoriach/obszarach/miastach

(obslugiwanych przez poszczególnych pracowników)

CustomerDemographics – grupy klientów

CustomerCustomerDemo – przyporządkowanie klientów do grup

 

Odejmowanie dwóch dat (obecna – data zdarzenia) i prezentacja wyników w dniach

PROBLEM:

Odejmowanie dwóch dat (obecna – data zdarzenia) i prezentacja wyników w dniach

SZCZEGÓŁY:

Chciałbym odjąć dwie daty, obecną – data zdarzenia (np. data zapisania rekordu do bazy), w celu prezentacji wyników odejmowania w dniach. Jeśli zdarzenie (np. data zapisania rekordu do bazy) mała miejsce dzisiaj to w wyniku zapytania chcę otrzymać tekst „dzisiaj” i analogicznie jeśli zdarzenie miało miejsce wczoraj to chcę otrzymać tekst ‚wczoraj’ i tak samo z ‚przedwczoraj’. Jeśli zdarzenie miało miejsce później niż przedwczoraj to wynikiem odejmowania ma być prezentacja daty zdarzenia w formacie „Y-m-d”, czyli Rok-miesiąc-dzień.

ROZWIĄZANIE:

Możemy ten problem rozwiązać na poziomie aplikacji czy strony WWW, pobrać datę z bazy i porównać ją daty obecnej. Pewniejszym rozwiązaniem będzie uzyskanie potrzebnego wyniku od razy z zapytania SQL. W tym celu możemy skorzystać z funkcji DATEDIFF, która zwróci nam różnicę między parametrami (w tym przypadku datami). Aby uzyskać zadowalający nas efekt musimy jeszcze skorzystać z funkcji CASE która w zależności od wyniku odejmowania dat zwróci nam wartość o którą nam chodzi (‚dzisiaj’, ‚wczoraj’, ‚przedwczoraj’ lub datę zapisu rekordu do bazy inną nić wymienione).

SELECT
id
,tytul
,opis
,cena
,case(datediff(now(),data_dodania))               <- tutaj następuje odjęcie dat

WHEN 0 THEN ‚dzisiaj’      <- jeśli zdarzenie miało miejsce dzisiaj to różnicą w odejmowaniu jest 0, wtedy chcemy wyświetlić tekst  ‚dzisiaj’

WHEN 1 THEN ‚wczoraj’  <- jeśli zdarzenie miało miejsce wczoraj to różnicą w odejmowaniu jest 1, wtedy chcemy wyświetlić tekst  ‚wczoraj’

WHEN 2 THEN ‚przedwczoraj’   <- analogicznie jak wyżej

ELSE date(data_dodania) END as data_dodania   <- wszystkie inne przypadki to sytuacje kiedy zdarzenie miało miejsce wcześniej niż przedwczoraj i wtedy chcemy wyświetlić konkretną datę w formacie „Y-m-d”
,data_dodania as data_dodania_org   <- jeśli oryginalna data jest w formacie DATETIME to możemy ją wykorzystać do sortowania
FROM
tab1
WHERE
[..TUTAJ PODAJEMY WARUNKI WYSZUKIWANIA REKORDÓW…]
ORDER BY
data_dodania_org DESC   <- jak już wcześniej pisałem możemy wykorzystać oryginalną datę do sortowania

GOTOWE 🙂 MIŁEJ ZABAWY

Wyszukiwanie liczb z jednego zbioru w drugim zbiorze.

PROBLEM:

jak napisać zapytanie SQL do bazy żeby zliczył ile razy liczby z jednego zbioru (tabeli) wystąpiły w drugim zbiorze (tabeli)?

SZCZEGÓŁY:
np. mamy dwie tabele o nazwach „tab1” i „tab2„. W tabeli „tab1” w kolumnie „liczba” znajdują się wartości 1,12,24,48 (np. jest to numer zamówienia). W tabeli „tab2” są cztery kolumny nazwijmy je „kol1„, „kol2„, „kol3” i „kol4„. W każdej z tych kolumn może wystąpić numer zamówienia z  tabeli „tab1” z kolumny „liczba„.

Jak napisać zapytanie SQL żeby zliczył ile razy liczby/numer zamówień z tabeli „tab1” wystąpiły w całej tabeli „tab2” ?

 

ROZWIĄZANIE:

SELECT
tab1.liczba
,COUNT(*)

FROM
tab1 LEFT OUTER JOIN tab2 ON tab1.liczba = tab2.kol1
OR tab1.liczba = tab2.kol2
OR tab1.liczba = tab2.kol3
OR tab1.liczba = tab2.kol4

GROUP BY
tab1.liczba

Włączamy numerację linii w SQL Server 2008 R2

Po uruchomieniu zapytania, jeśli nie jest ono dobrze skonstruowane, często otrzymujemy błędy, które zawierają podpowiedź co zrobiliśmy źle. Przy błędzie jest także informacja w której linijce wystąpił i wszystko jest w porządku jeśli nasze zapytanie ma 10 lub 20 linijek. Ale co w przypadku kiedy nasze zapytanie ma 100 lub więcej ? Na szczęście możemy włączyć sobie numerowanie linii w oknie pisania zapytań. Można to zrobić w bardzo łatwy sposób.

1. Widok okna gdzie piszemy zapytania przed włączeniem numeracji linii

Okno zapytań przed włączeniem numeracji linii.
Okno zapytań przed włączeniem numeracji linii.

2. Klikamy w menu TOOLS i później w OPTIONS

Klikamy w menu TOOLS a później w OPTIONS
Klikamy w menu TOOLS a później w OPTIONS

3. rozwijamy drzewko z lewej strony TEXT EDITOR -> ALL LANGUAGES i zaznaczamy checkbox przy opcji LINE NUMBERS i klikamy w przycisk OK dla potwierdzenia zmian.

Rozwijamy drzewko TEXT EDITOR -> ALL LANGUAGES
Rozwijamy drzewko TEXT EDITOR -> ALL LANGUAGES

4. Widok okna zapytań po włączeniu numeracji linii.

Widok okna zapytań po włączeniu numeracji linii.
Widok okna zapytań po włączeniu numeracji linii.