SQL Gdje: aplikacije i primjeri. SQL gdje aplikacije i primjeri Uzorak upita za odabir numeričkih vrijednosti

Tražite objekte u svim serverskim bazama podataka

Da li vam je ikada bilo potrebno da pronađete tabelu da biste potražili podatke ili strukturu, ali se ne sećate tačno u kojoj se bazi podataka nalazi? Ovo mi se dešava s vremena na vreme. Primjer je pronaći tabele iz pet godina starog projekta da vidite kako je tada riješen trenutni problem.

Na sreću, postoji procedura sp_MSforeachdb, što vam omogućava da napišete takvu skriptu prilično kompaktno:

DECLARE @name KAO SYSNAME,
@strSQL KAO VARCHAR(MAX)
SET @name = "Hijerarhija"

SET @strSQL = "
AKO POSTOJI(SELECT * FROM ?..sysobjects GDJE ime LIKE ""%" [email protected]+"%" ")
POČNI
SELECT " "?" " db, ime FROM ?..sysobjects WHERE ime LIKE ""%" [email protected]+"%" "
KRAJ
"
CREATE TABLE #result (
dbSYSNAME,
ime SYSNAME,
PRIMARNI KLJUČ (db,ime))

INSERT #result EXEC sp_MSforeachdb @strSQL
SELECT * FROM #result

ISPUSTI TABELU #rezultat

Ovde je sve prilično jednostavno:

  1. Postavljamo dio imena po kojem ćemo pretraživati ​​(u ovom slučaju - Hijerarhiju).
  2. Prikupljamo dinamički SQL koji traži sysobjekte (za neke starije verzije MS SQL-a, možete koristiti VARCHAR(2000) umjesto VARCHAR(MAX), na primjer).
    U ovom slučaju, simbol "?" će biti zamijenjen imenom baze podataka.
  3. Koristeći INSERT ... EXEC konstrukciju, pohranjujemo sve rezultate u privremenu tablicu kako bismo kao rezultat prikazali jedan rezultat.

Potražite podniz u tekstovima pohranjenih procedura

Kao što znate, tekstovi pohranjenih procedura, okidača i pogleda mogu se naći u tabeli syscomments.

Kako bi nam naš prethodni zahtjev omogućio da u syscommentima pronađemo spominjanje nekog podniza, on se može prepisati na sljedeći način:

SET @strSQL="
ODABIR DISTINCT " "?" "kao db, o.name FROM [?].dbo.syscomments s
JOIN [?].dbo.sysobjects o ON o.id = s.id WHERE s. LIKE""%" [email protected]+"%" ""

Obratite pažnju na DISTINCT - može dobro doći, jer ponekad postoji nekoliko unosa u sistemskim komentarima za jedan objekat.

Potražite podniz u svim poljima niza svih tabela

Takva skripta pomaže da se mirno spava nakon "čišćenja" baze podataka prije slanja klijentu. Naravno, njegova primjena nije ograničena na ovu oblast.

Za ovaj slučaj pomoćnik u obliku procedure iz MS-a nije pronađen, pa sam morao koristiti kursore:

deklariraj @pattern kao nvarchar(MAX)
set @pattern = N"%Test%"
ne računajte na
deklarirati @sql kao nvarchar(MAX)
proglasiti @table kao sysname
deklarirati kursor tablica lokalni statički read_only za odabir imena iz sys.tables t gdje je t.is_ms_shipped = 0
otvoreni stolovi

kreiraj tablicu #results(ime sysname nije null, vrijednost nvarchar(MAX) nije null)
dok je @@FETCH_STATUS = 0
početi
postavi @sql=""
odaberite @sql = @sql + "
ubaci u #results select""" + @table + "." + ime + "" " kao ime, [" + ime + "] od [" + @tabela + "] gdje je [" + ime + "] kao " "" + @obrazac + "" ""
iz sys.columns c gdje je c.object_id = OBJECT_ID(@table) i c.system_type_id u
(odaberite system_type_id iz sys.types gdje Collation_name nije null)
exec (@sql)
dohvati sljedeće iz tablica u @table
kraj
odaberite * iz #results
zatvorite stolove
ispusti tabelu #rezultati

Osim kursora, principi su isti. Postoji nekoliko nijansi:

  • Tražimo tabele bez zastavice is_ms_shipped da bismo videli samo tabele kreirane tokom razvoja.
  • Za odabir stupaca niza, umjesto liste tipova (koju je zamorno održavati), koristi se filter "collation_name is not null".

Sažetak

Pažljivi čitalac mora da je pretpostavio iz dizajna da poslednji primer nije napisan u isto vreme kada i prva dva. Da budem precizniji, zadnji zahtjev sam napisao prije mjesec dana. To mislim da na osnovu ovog pristupa možete sami pisati korisne skripte koje rješavaju vaše specifične probleme.

Ako se čitateljima sviđa ovaj format, ponekad ću objaviti korisne skripte iz svoje lične kolekcije.

Ako imate komentare, prijedloge ili nove teme - pišite u komentarima,

Teško je objasniti sintaksu za klauzulu SQL Server WHERE, pa pogledajmo neke primjere.

Počećemo tako što ćemo pogledati kako koristiti klauzulu WHERE sa samo jednim uslovom.

SELECT * FROM zaposlenih WHERE first_name = "Jane";

U ovom primjeru klauzule WHERE SQL Servera, koristili smo klauzulu WHERE da filtriramo naše rezultate iz zaposlenima sto. Naredba SELECT iznad bi vratila sve redove iz zaposlenima sto gde je ime je "Jane". Budući da se * koristi u SELECT, sva polja iz zaposlenima tabela bi se pojavila u skupu rezultata.

Primjer - korištenje uslova I

Pogledajmo kako koristiti klauzulu WHERE sa uslovom AND.

SELECT * FROM zaposlenih WHERE prezime = "Anderson" I Employee_id >= 3000;

Ovaj primjer klauzule WHERE SQL Servera koristi klauzulu WHERE za definiranje više uvjeta. U ovom slučaju, ovaj SELECT izraz koristi uvjet AND da vrati sve zaposlenima koji imaju a prezime od "Anderson" i Employee_id je veći ili jednak 3000.

Primjer - korištenje uvjeta OR

Pogledajmo kako koristiti klauzulu WHERE sa uslovom OR.

SELECT employee_id, last_name, first_name FROM zaposlenika WHERE last_name = "Johnson" OR first_name = "Danielle";

Ovaj primjer klauzule WHERE SQL Servera koristi klauzulu WHERE za definiranje više uvjeta, ali umjesto korištenja uvjeta AND, koristi uvjet OR. U ovom slučaju, ovaj SELECT izraz bi vratio sve Employee_id, prezime, i ime vrijednosti iz zaposlenima sto gde je prezime je "Johnson" ili ime je "Danielle".

Primer - Kombinovanje uslova I & ILI

Pogledajmo kako da koristimo klauzulu WHERE kada kombinujemo uslove AND & OR u jednoj SQL naredbi.

SELECT * FROM zaposlenih WHERE (state = "California" AND fame_name = "Smith") OR (employee_id = 82);

Ovaj primjer klauzule WHERE SQL Servera koristi klauzulu WHERE za definiranje više uvjeta, ali kombinira uvjet AND i uvjet OR. Ovaj primjer bi vratio sve zaposlenima koji borave u stanje od "Kalifornije" i čije prezime je "Smith" kao i svi zaposleni čiji Employee_id je jednako 82.

Zagrade određuju redosled i ILI uslovi se procjenjuju. Baš kao što ste naučili po redoslijedu operacija na času matematike!

Primjer - spajanje tablica

Pogledajmo kako koristiti klauzulu WHERE kada spajamo više tabela.

SELECT employee.employee_id, contacts.last_name FROM zaposlenika INNER JOIN contacts ON employee.employee_id = contacts.contact_id WHERE staff.first_name = "Sarah";

Ovaj primjer klauzule WHERE SQL Servera koristi klauzulu WHERE za spajanje više tablica u jednu SELECT naredbu. Ovaj SELECT izraz bi vratio sve redove u kojima je ime u zaposlenima sto je "Sarah". I the zaposlenik s and kontakti stolovi su spojeni na Employee_id iz zaposlenima sto i contact_id iz kontakti sto.

U većini slučajeva ne morate preuzimati sve zapise, već samo one koji ispunjavaju određene kriterije. Stoga, da biste filtrirali odabir u SQL postoji poseban operater GDJE.

1. Jednostavno filtriranje sa WHERE operatorom.

Odaberimo, na primjer, zapise iz naše tabele koji se odnose samo na određeni proizvod. Da bismo to učinili, odredit ćemo dodatni parametar odabira koji će filtrirati vrijednost prema stupcu Proizvod.

Primjer upita za odabir tekstualnih vrijednosti:

SELECT * OD Sumproduct GDJE Proizvod = "Bcikli"

Kao što vidite, uslov odabira je stavljen u jednostruke navodnike, što je obavezno prilikom filtriranja tekstualnih vrijednosti. Navodnici nisu potrebni prilikom filtriranja numeričkih vrijednosti.

Primjer upita za odabir numeričkih vrijednosti:

SELECT > 40000 POREDAK PO Iznos

U ovom primjeru smo odabrali zapise u kojima je prihod od prodaje bio veći od 40 hiljada dolara i, dodatno, svi zapisi su sortirani uzlaznim redoslijedom po polju Iznos.

Tabela ispod navodi podržane uslovne izraze SQL:

2. Filtriranje po rasponu vrijednosti ( IZMEĐU).

Da biste odabrali podatke koji se nalaze u određenom rasponu, koristite operator IZMEĐU. Sljedeći upit će odabrati sve vrijednosti između 1000 $ V 2000 $ inkluzivno, na terenu Iznos.

SELECT * OD Sumproduct GDJE Količina IZMEĐU 1000 I 2000

Redoslijed sortiranja ovisit će o redoslijedu polja u upitu. To jest, u našem slučaju, podaci će se prvo sortirati po kolonama Iznos, a zatim po grad.

3. Izbor praznih zapisa ( IS NULL).

IN SQL postoji poseban operator za odabir praznih zapisa (tzv NULL). Prazan unos je svaka ćelija u tabeli koja ne sadrži znakove. Ako je ćelija unesena 0 ili prostor, tada se polje smatra popunjenim.

SELECT * OD Sumproduct GDJE Količina IS NULL

U gornjem primjeru, namjerno smo uklonili dvije vrijednosti u polju Iznos da se demonstrira rad operatera NULL.

4. Napredno filtriranje ( I, ILI).

Jezik SQL nije ograničeno na filtriranje prema jednom uslovu, za vlastite potrebe možete koristiti prilično složene strukture za odabir podataka istovremeno prema više kriterija. Za ovo u SQL postoje dodatni operateri koji proširuju mogućnosti operatera GDJE. Ovi operateri su: I, ILI, U, NE. Evo nekoliko primjera kako ti operateri rade.

SELECT * OD Sumproduct GDJE Količina > 40000 I grad = "Toronto"

SELECT * OD Sumproduct WHERE Mjesec= "april"ILI mjesec= "mart"

Hajde da kombinujemo operatore I I ILI. Da bismo to učinili, napravimo izbor bicikala ( Bicikli ) i klizaljke ( klizaljke ), koji su prodati u martu (mart ).

SELECT * OD Sumproduct GDJE Proizvod= "Bcikli"ILI Proizvod= "klizaljke" I mjesec= "mart"

Vidimo da je naš uzorak uključivao mnoge vrijednosti (osim za mart ( mart), također januar ( Januar), Februar ( februar) i april ( april)). Šta je razlog? I u tome SQL ima prioritete izvršenja naredbi. Odnosno, operater I ima veći prioritet od operatora ILI, pa su prvo odabrane ploče sa klizaljkama koje su prodate u martu, a potom i sve ploče vezane za bicikle.

Dakle, da bismo dobili ispravan izbor, moramo promijeniti prioritete izvršavanja naredbi. Za ovo koristimo zagrade kao u matematici. Tada će se prvo obraditi operatori u zagradama, a zatim svi ostali.

SELECT * OD Sumproduct GDJE (Proizvod= "Bcikli"ILI Proizvod= "klizaljke") I mjesec= "mart"

5. Napredno filtriranje ( IN operater).

SELECT * IZ Sumproduct WHERE IDIN (4, 12, 58, 67)

Operater IN obavlja istu funkciju kao ILI međutim, ima niz prednosti:

  • Kada radite sa dugim listama, rečenica sa IN lakše za čitanje;
  • Koristi se manje operatora, što ubrzava obradu zahtjeva;
  • Najvažnija prednost IN u tome što je u njegovoj izgradnji moguće koristiti dodatnu konstrukciju SELECT, Šta
    • otvara velike mogućnosti za kreiranje složenih podupita.

6. Napredno filtriranje ( NIJE operater).

SELECT * IZ Sumproduct GDJE NIJE GradIN ("Toronto", "Montreal")

Ključna riječ NE omogućava vam da uklonite nepotrebne vrijednosti iz odabira. Takođe, njegova karakteristika je da se postavlja ispred naziva kolone uključene u filtriranje, a ne iza.

U ovom članku ću vam reći o uslovnoj naredbi CASE, bez koje bi se obračun podataka u brojnim zadacima pretvorio u hrpu dijelova koda, kao io tome kako ga koristiti u SQL upitima.

Bez obzira na to koliko dobro dizajnirate svoju bazu podataka, uvijek će postojati problemi gdje su uvjetni iskazi neophodni. Na primjer, umjesto ogromnih brojeva, dobiti verbalni opis, ili, ovisno o prisutnosti (odsustvu) podataka, dodati dodatne znakove u niz, ili, kao složeniji primjer, ovisno o različitim nijansama, pripisati zapis jednom ili drugu grupu. Mnogo situacija.

U principu, ovaj operator ima najmanje dva oblika u različitim bazama podataka. Prva opcija liči na uobičajeno prebacivanje sa bilo kojeg programskog jezika. Drugi zavisi od logičkih izraza.

Međutim, u ovom članku ću razmotriti drugu opciju, jer nema problema sa situacijama kao što je CASE WHEN NULL (null nije specifična vrijednost u bazi podataka, tako da se ne može koristiti u naredbi poput switch). Osim toga, u svakodnevnom životu zadaci se najčešće susreću upravo za drugu opciju - izračunavanje kroz logičke izraze. Stoga je bolje odmah naučiti i nastaviti ga koristiti.

Obično se opisuje ovako (sintaksa može varirati ovisno o bazi podataka):

CASE WHEN bool_izraz1 ONDA vrijednost1 ..... KADA bool_izrazN ONDA vrijednostN ELSE vrijednostElse END

bool_expressionX je boolean uslov

valueX je vrijednost koja će biti zamijenjena ako je ispunjen odgovarajući logički uvjet

valueElse je vrijednost koja će biti zamijenjena ako prethodno nije ispunjen nijedan uvjet.

Nakon tako male reference, pređimo na praksu.

Bilješka: Usput, vrijedi znati da se obično ova izjava može koristiti ne samo u odabiru, već i na bilo kojem mjestu gdje se mogu koristiti polja. Na primjer, kada spojiti stolove ili čak filtriranje (imati) pri grupisanju (grupi po).

Uvjetni izraz CASE...WHEN...THEN

Da biste bolje razumjeli uslovni izraz CASE...WHEN...THEN, zamislite mali problem. Recimo da imate tabelu sa podacima o kupcima i njihovom ukupnom broju kupovina. A zadatak je dinamički formirati popust. Popust bi, naravno, bilo moguće postaviti ručno. Ali, imate jedan prag, a pragovi su fiksirani (nešto kao - iznos veći od 1000 dobijete 2% popusta, a više od 5000 - dobijete 5%) i želite da automatizujete ovaj proces tako da ne morate tražiti greške svaki put kada ulazite u bazu podataka (klijent je akumulirao potreban iznos - popust se automatski pojavio).

Uzmimo klijenta uslovne tabele sa tri klijenta. Na primjer, oni će biti dovoljni.

Sada postavimo nekoliko uslova za automatsko odobravanje popusta na osnovu zadatka. Istovremeno, vjerujemo da je u svakom slučaju klijentu omogućen maksimalan popust.

1. Iznos od 1000 - 2% popusta

2. Iznos od 5000 - 5% popusta

3. Iznos od 10000 - 8% popusta

4. Količina narudžbi od 10 - 7% popusta

5. Količina narudžbi od 20 - 8% popusta

Kao što vidite, popust zavisi od dva faktora: količine i količine. Pokušajmo sada od njih stvoriti uslove na osnovu popusta, odnosno pravila su obrnuto kako bi se mogla koristiti u sql upitu. Dobijamo sljedeće:

1. 2% - Iznos od 1000 do 4999 i broj narudžbi manji od 10.

2. 5% - Iznos od 5000 do 9999 i broj narudžbi manji od 10.

3. 7% - Broj narudžbi od 10 do 19 i iznos manji od 10000

4. 8% - Količina od 20 ili iznos od 10000

Sve što je ostalo je da to zapišete. Nabavite sljedeći sql upit

Prikazivanjem imena i drugih podataka odaberite ime, broj_porudžbine, total_sum, -- I sada prikazujemo popust SLUČAJ -- Prvo pravilo 2% KADA c.total_sum >= 1000 i c.total_sum<= 4999 and c.order_count < 10 THEN 2 -- Второе правило 5% WHEN c.total_sum >= 5000 i c.total_sum<= 9999 and c.order_count < 10 THEN 5 -- Третье правило 7% WHEN c.total_sum < 10000 and c.order_count >= 10 i c.order_count<= 19 THEN 5 -- Четвертое правило 8% WHEN c.total_sum >= 10000 ili c.order_count >= 20 ONDA 5 -- Nema podudaranja pravila, tako da je popust 0. ELSE 0 END kao popust od klijenta c

Kao rezultat izvršenja, dobijamo sljedeću tabelu:

Kao što vidite, dva kupca su dobila popust od 8 posto, a jedan kupac 2 posto. Istovremeno, sa svakom narudžbom, postotak će biti automatski izračunat i nećete morati ništa prilagođavati. Na primjer, ako se Petyin iznos poveća na 5000, tada će njegov popust automatski porasti na 5% (barem, budući da još uvijek postoji veliki broj narudžbi).

mob_info