SQL Kur: programos ir pavyzdžiai. SQL kur programos ir pavyzdžiai Skaitinių reikšmių pasirinkimo užklausos pavyzdys

Ieškokite objektų visose serverių duomenų bazėse

Ar kada nors reikėjo rasti lentelę, kad galėtumėte ieškoti duomenų ar struktūros, bet tiksliai neprisimenate, kurioje duomenų bazėje ji yra? Man taip karts nuo karto nutinka. Pavyzdys – rasti lenteles iš penkerių metų senumo projekto, kad pamatytumėte, kaip tuomet buvo išspręsta dabartinė problema.

Laimei, yra procedūra sp_MSforeachdb, kuri leidžia gana kompaktiškai parašyti tokį scenarijų:

SKELBTI @vardą KAIP SYSNAME,
@strSQL AS VARCHAR(MAX)
SET @name = "Hierarchija"

NUSTATYTI @strSQL = "
IF EXISTS(SELECT * FROM ?..sysobjects WHERE pavadinimas LIKE ""%" [apsaugotas el. paštas]+"%" ")
PRADĖTI
PASIRINKTI " "? " db, pavadinimas IŠ ?..sysobjects WHERE pavadinimas PATINKA ""%" [apsaugotas el. paštas]+"%" "
GALAS
"
KURTI LENTELĘ #result (
dbSYSNAME,
vardas SYSNAME,
PIRMINIS RAKTAS (db,pavadinimas))

Įterpkite #result EXEC sp_MSforeachdb @strSQL
PASIRINKTI * IŠ #rezultatas

NURODYTI LENTELĘ #rezultatas

Čia viskas labai paprasta:

1. Nustatome vardo dalį, pagal kurią ieškosime (šiuo atveju – Hierarchija).
2. Renkame dinaminį SQL, kuris ieško sisteminių objektų (pavyzdžiui, kai kuriose senesnėse MS SQL versijose galite naudoti VARCHAR(2000) vietoj VARCHAR(MAX).
   Šiuo atveju simbolis "?" bus pakeistas duomenų bazės pavadinimu.
3. Naudodami INSERT ... EXEC konstrukciją, visus rezultatus saugome laikinoje lentelėje, kad būtų rodomas vienas rezultatas.

Ieškokite poeilutės saugomų procedūrų tekstuose

Kaip žinote, saugomų procedūrų, trigerių ir rodinių tekstus galite rasti lentelėje sisteminiai komentarai.

Kad ankstesnė užklausa leistų mums rasti paminėjimą sistemos komentaruose kai kurioms poeilėms, ją galima perrašyti taip:

NUSTATYTI @strSQL="
PASIRINKTI ATSKIRIMĄ " "? "kaip db, o.vardas IŠ [?].dbo.syscomments s
PRISIJUNK [?].dbo.sysobjects o ON o.id = s.id WHERE s. KAIP""%" [apsaugotas el. paštas]+"%" ""

Atkreipkite dėmesį į DISTINCT – jis gali praversti, nes kartais sistemos komentaruose yra keli įrašai vienam objektui.

Ieškokite poeilutės visuose visų lentelių eilutės laukuose

Toks scenarijus padeda ramiai miegoti „išvalius“ duomenų bazę prieš siunčiant ją klientui. Natūralu, kad jo taikymas neapsiriboja šia sritimi.

Šiuo atveju asistentas procedūros pavidalu iš MS nerastas, todėl turėjau naudoti žymeklius:

paskelbti @pattern kaip nvarchar(MAX )
nustatyti @pattern = N"%Test%"
nesiskaitykite
paskelbti @sql kaip nvarchar(MAX )
paskelbti @table kaip sistemos pavadinimą
deklaruoti lenteles žymeklį vietinis statinis tik skaitomas pasirinktam pavadinimui iš sys.tables t kur t.is_ms_shipped = 0
atviri stalai

sukurti lentelę #results(pavadinimas sistemos pavadinimas ne null , reikšmė nvarchar(MAX ) not null )
o @@FETCH_STATUS = 0
pradėti
nustatyti @sql=""
pasirinkite @sql = @sql + "
įterpti į #results pasirinkite""" + @table + "." + vardas + "" " kaip pavadinimas, [" + vardas + "] iš [" + @table + "], kur [" + vardas + "], pvz., " "" + @pattern + "" ""
iš sys.columns c kur c.object_id = OBJECT_ID(@table ) ir c.system_type_id in
(pasirinkite sistemos_tipo_id iš sys.types, kur palyginimo_pavadinimas nėra nulis )
vykdytojas (@sql)
paimti kitą iš lentelių į @table
galas
pasirinkite * iš #rezultatų
uždaryti lenteles
numetimo lentelė #rezultatai

Išskyrus žymeklius, principai yra tie patys. Yra keletas niuansų:

• Ieškome lentelių be is_ms_shipped vėliavėlės, kad matytume tik kūrimo metu sukurtas lenteles.
• Norint pasirinkti eilučių stulpelius, vietoj tipų sąrašo (kurį tvarkyti yra nuobodu), naudojamas filtras „lyginimo_pavadinimas nėra nulis“.

Santrauka

Dėmesingas skaitytojas pagal dizainą turėjo atspėti, kad paskutinis pavyzdys parašytas ne tuo pačiu metu kaip pirmieji du. Jei tiksliau, paskutinį prašymą rašiau prieš mėnesį. Turiu omenyje tai, kad remdamiesi šiuo požiūriu galite patys parašyti naudingus scenarijus, kurie išspręstų jūsų konkrečias problemas.

Jei skaitytojams patinka šis formatas, kartais paskelbsiu naudingų scenarijų iš savo asmeninės kolekcijos.

Jei turite pastabų, pasiūlymų ar naujų temų - rašykite komentaruose,

Sunku paaiškinti SQL Server WHERE sąlygos sintaksę, todėl pažvelkime į keletą pavyzdžių.

Pradėsime nuo to, kaip naudoti WHERE sąlygą tik su viena sąlyga.

SELECT * FROM darbuotojai WHERE first_name = "Jane";

Šiame SQL Server WHERE sąlygos pavyzdyje mes naudojome WHERE sąlygą, norėdami filtruoti rezultatus iš darbuotojų stalo. Aukščiau pateiktas SELECT teiginys grąžintų visas eilutes iš darbuotojų lentelė, kurioje Pirmas vardas yra "Jane". Kadangi * yra naudojamas SELECT, visi laukai iš darbuotojų lentelė atsiras rezultatų rinkinyje.

Pavyzdys – IR sąlygų naudojimas

Pažiūrėkime, kaip naudoti WHERE sąlygą su sąlyga AND.

SELECT * FROM darbuotojai WHERE pavardė = "Anderson" AND darbuotojo_id >= 3000;

Šiame SQL Server WHERE sąlygos pavyzdyje WHERE sąlyga naudojama kelioms sąlygoms apibrėžti. Šiuo atveju šis SELECT sakinys naudoja sąlygą IR, kad grąžintų viską darbuotojų kurie turi a pavardė„Andersonas“ ir Darbuotojo ID yra didesnis arba lygus 3000.

Pavyzdys – sąlygos OR naudojimas

Pažiūrėkime, kaip naudoti WHERE sąlygą su sąlyga OR.

SELECT darbuotojo_id, pavardė, vardas FROM darbuotojai WHERE pavardė = "Džonsonas" OR vardas_vardas = "Danielle";

Šiame SQL serverio WHERE sąlygos pavyzdyje naudojama sąlyga WHERE, kad apibrėžtų kelias sąlygas, tačiau vietoj IR sąlygos naudojama sąlyga OR. Šiuo atveju šis SELECT sakinys grąžintų viską Darbuotojo ID, pavardė, ir Pirmas vardas vertybes iš darbuotojų lentelė, kurioje pavardė yra „Džonsonas“ arba „ Pirmas vardas yra "Danielle".

Pavyzdys – IR ir ARBA sąlygų derinimas

Pažiūrėkime, kaip naudoti WHERE sąlygą, kai sujungiame sąlygas AND & OR viename SQL sakinyje.

SELECT * FROM darbuotojai WHERE (valstybė = "Kalifornija" AND pavardė = "Smith") OR (darbuotojo_id = 82);

Šiame „SQL Server WHERE“ sąlygos pavyzdyje naudojama WHERE sąlyga, kad apibrėžtų kelias sąlygas, tačiau jame derinama sąlyga IR ir sąlyga OR. Šis pavyzdys grąžintų viską darbuotojų kurie gyvena valstybė„Kalifornijos“ ir kurio pavardė yra "Smith", taip pat visi darbuotojai, kurių Darbuotojo ID yra lygus 82.

Skliausteliuose nustatoma tvarka, kurią ir ARBA sąlygos įvertinamos. Lygiai taip pat, kaip mokėsi matematikos pamokoje operacijų tvarka!

Pavyzdys – sujungti lenteles

Pažiūrėkime, kaip naudoti WHERE sąlygą, kai sujungiame kelias lenteles.

PASIRINKTI darbuotojai.darbuotojo_id, kontaktai.pavardė NUO darbuotojų VIDINIS PRISIJUNGTI kontaktai ON darbuotojai.darbuotojo_id = contacts.contact_id WHERE darbuotojai.pirmas_vardas = "Sara";

Šiame SQL Server WHERE sąlygos pavyzdyje naudojama WHERE sąlyga, kad sujungtų kelias lenteles viename SELECT sakinyje. Šis SELECT sakinys grąžintų visas eilutes, kuriose Pirmas vardas viduje darbuotojų stalas yra "Sara". Ir darbuotojas smėlis kontaktai lentelės yra sujungtos ant Darbuotojo ID nuo darbuotojų stalas ir contact_id nuo kontaktai stalo.

Daugeliu atvejų reikia nuskaityti ne visus įrašus, o tik tuos, kurie atitinka tam tikrus kriterijus. Todėl norint filtruoti pasirinkimą SQL yra specialus operatorius KUR.

1. Paprastas filtravimas naudojant WHERE operatorių.

Pavyzdžiui, iš mūsų lentelės pasirinkite įrašus, susijusius tik su konkrečiu produktu. Norėdami tai padaryti, nurodysime papildomą pasirinkimo parametrą, kuris filtruos reikšmę pagal stulpelį produktas.

Teksto verčių pasirinkimo užklausos pavyzdys:

PASIRINKTI * IŠ Sumproduct WHERE Produktas = "Dviračiai"

Kaip matote, pasirinkimo sąlyga yra įterpta į atskiras kabutes, kurios yra privalomos filtruojant teksto reikšmes. Filtruojant skaitines reikšmes kabučių nereikia.

Skaitinių reikšmių pasirinkimo užklausos pavyzdys:

PASIRINKTI > 40000 RŪŠIUOTI PAGAL Suma

Šiame pavyzdyje pasirinkome įrašus, kuriuose pardavimo pajamos buvo didesnės nei 40 tūkstančių dolerių ir, be to, visi įrašai rūšiuojami didėjančia tvarka pagal laukus Suma.

Toliau esančioje lentelėje pateikiami palaikomi sąlyginiai teiginiai SQL:

2. Filtravimas pagal verčių diapazoną ( TARP).

Norėdami pasirinkti duomenis, esančius tam tikrame diapazone, naudokite operatorių TARP. Kitoje užklausoje bus pasirinktos visos reikšmės tarp 1000 $ V 2000 $ imtinai, lauke Suma.

PASIRINKTI * FROM Sumproduct WHERE Kiekis TARP 1000 IR 2000

Rūšiavimo tvarka priklausys nuo užklausos laukų tvarkos. Tai yra, mūsų atveju, duomenys pirmiausia bus surūšiuoti pagal stulpelius Suma, o tada iki miestas.

3. Tuščių įrašų pasirinkimas ( YRA NULL).

IN SQL yra specialus operatorius, skirtas pasirinkti tuščius įrašus (vadinamas NULL). Tuščias įrašas yra bet kuris lentelės langelis, kuriame nėra jokių simbolių. Jei įvedamas langelis 0 arba erdvė, tada laukas laikomas užpildytu.

PASIRINKTI * FROM Sumproduct WHERE Kiekis YRA NULL

Aukščiau pateiktame pavyzdyje mes sąmoningai pašalinome dvi reikšmes lauke Suma parodyti operatoriaus veikimą NULL.

4. Išplėstinis filtravimas ( IR, ARBA).

Kalba SQL neapsiriboja filtravimu pagal vieną sąlygą, savo tikslams galite naudoti gana sudėtingas struktūras, kad vienu metu atrinktumėte duomenis pagal daugelį kriterijų. Už tai į SQL yra papildomų operatorių, kurie išplečia operatoriaus galimybes KUR. Šie operatoriai yra: IR, ARBA, IN, NE. Štai keletas pavyzdžių, kaip veikia šie operatoriai.

PASIRINKTI * FROM Sumproduct WHERE Kiekis > 40000 IR miestas = "Torontas"

PASIRINKTI * FROM Sumproduct WHERE Mėnuo= "balandis"ARBA mėnuo= "kovas"

Sujungkime operatorius IR Ir ARBA. Norėdami tai padaryti, parinkkime dviračius ( Dviračiai ) ir pačiūžos ( pačiūžos ), kurie buvo parduoti kovo mėn (Kovas ).

PASIRINKTI * IŠ Sumproduct WHERE Produktas= "Dviračiai"ARBA produktas= "čiuožyklos" IR mėnuo= "kovas"

Matome, kad mūsų imtyje buvo daug verčių (išskyrus kovo mėn. Kovas), taip pat sausio mėn. sausio mėn), vasaris ( vasario mėn) ir balandį ( Balandis)). Kokia priežastis? Ir tame SQL turi komandų vykdymo prioritetus. Tai yra operatorius IR turi didesnę pirmenybę nei operatorius ARBA, tad iš pradžių buvo atrinkti kovo mėnesį parduoti rekordai su pačiūžomis, o po to visi su dviračiais susiję rekordai.

Taigi, norėdami gauti teisingą pasirinkimą, turime pakeisti komandų vykdymo prioritetus. Tam mes naudojame skliausteliuose kaip matematikoje. Tada pirmiausia bus apdorojami operatoriai skliausteliuose, o tada visi kiti.

PASIRINKTI * FROM Sumproduct WHERE (Produktas= "Dviračiai"ARBA produktas= "čiuožyklos") IR mėnuo= "kovas"

5. Išplėstinis filtravimas ( IN operatorius).

PASIRINKTI * FROM Sumproduct WHERE IDIN (4, 12, 58, 67)

operatorius IN atlieka tą pačią funkciją kaip ARBA tačiau turi keletą privalumų:

• Dirbant su ilgais sąrašais, sakinys su IN lengviau skaityti;
• Naudojama mažiau operatorių, o tai pagreitina užklausų apdorojimą;
• Svarbiausias privalumas IN tuo, kad jo konstrukcijoje galima naudoti papildomą konstrukciją PASIRINKTI, Ką
atveria puikias galimybes kurti sudėtingas antrines užklausas.

6. Išplėstinis filtravimas ( NE operatorius).

PASIRINKTI * IŠ Sumproduct, kur NE miestasIN („Torontas“, „Monrealis“)

raktinis žodis NE leidžia iš pasirinkimo pašalinti nereikalingas reikšmes. Be to, jo ypatybė yra ta, kad jis dedamas prieš filtravimo stulpelio pavadinimą, o ne po jo.

Šiame straipsnyje papasakosiu apie sąlyginį CASE teiginį, be kurio daugelio užduočių duomenų apskaita virstų krūvomis kodų, taip pat apie tai, kaip jį naudoti SQL užklausose.

Nesvarbu, kaip gerai suprojektuosite savo duomenų bazę, visada bus problemų, kai būtini sąlyginiai teiginiai. Pavyzdžiui, vietoj didžiulių skaičių gaukite žodinį aprašymą arba, priklausomai nuo duomenų buvimo (nebuvimo), į eilutę pridėkite papildomų simbolių arba, kaip sudėtingesnis pavyzdys, priklausomai nuo įvairių niuansų, priskirkite įrašą vienam. ar kitai grupei. Daug situacijų.

Iš esmės šis operatorius turi bent dvi formas skirtingose ​​duomenų bazėse. Pirmoji parinktis primena įprastą bet kurios programavimo kalbos perjungimą. Antrasis priklauso nuo loginių išraiškų.

Tačiau šiame straipsnyje apsvarstysiu antrąjį variantą, nes jis neturi problemų su tokiomis situacijomis kaip CASE WHEN NULL (null nėra konkreti reikšmė duomenų bazėje, todėl ji negali būti naudojama kaip jungiklis). Be to, kasdieniame gyvenime su užduotimis dažniausiai susiduriama būtent antrajam variantui – skaičiavimui naudojant logines išraiškas. Todėl geriau iš karto išmokti ir toliau juo naudotis.

Paprastai jis aprašomas taip (sintaksė gali skirtis priklausomai nuo duomenų bazės):

CASE WHEN bool_expression1 THEN value1 ..... WHEN bool_expressionN THEN valueN ELSE valueElse END

bool_expressionX yra loginė sąlyga

valueX yra reikšmė, kuri bus pakeista, jei bus įvykdyta atitinkama loginė sąlyga

valueElse yra reikšmė, kuri bus pakeista, jei anksčiau nebuvo įvykdyta jokia sąlyga.

Po tokios mažos nuorodos pereikime prie praktikos.

Pastaba: Beje, verta žinoti, kad dažniausiai šis teiginys gali būti naudojamas ne tik select, bet ir bet kurioje vietoje, kur galima naudoti laukus. Pavyzdžiui, kada sujungti lenteles ar net filtravimas (turėjimas) grupuojant (grupuoti pagal).

Sąlyginis teiginys CASE...When...THEN

Norėdami geriau suprasti sąlyginį teiginį ATVEJIS...KAI...TAI, įsivaizduokite nedidelę problemą. Tarkime, kad turite lentelę su duomenimis apie klientus ir bendrą jų pirkinių skaičių. O užduotis – dinamiškai suformuoti nuolaidą. Žinoma, nuolaidą būtų galima nustatyti ir rankiniu būdu. Bet jūs turite slenkstį, o slenksčiai yra prijungti prie laidų (kažkas panašaus - suma yra didesnė nei 1000 gaukite 2% nuolaidą, o daugiau nei 5000 - gaukite 5%) ir norėtumėte automatizuoti šį procesą, kad nebūtų kiekvieną kartą gilinantis į duomenų bazę tenka ieškoti klaidų (klientas turi sukaupęs reikiamą sumą – nuolaida atsirado automatiškai).

Paimkime sąlyginės lentelės klientą su trimis klientais. Pavyzdžiui, jų pakaks.

Dabar nustatykime keletą sąlygų, kaip automatiškai suteikti nuolaidas pagal užduotį. Kartu manome, kad maksimali nuolaida klientui suteikiama bet kokiu atveju.

1. Suma nuo 1000 - 2% nuolaida

2. Suma nuo 5000 - 5% nuolaida

3. Suma nuo 10000 - 8% nuolaida

4. Užsakymų kiekis nuo 10 - 7% nuolaida

5. Užsakymų kiekis nuo 20 - 8% nuolaida

Kaip matote, nuolaida priklauso nuo dviejų veiksnių: sumos ir kiekio. Dabar pabandykime sukurti sąlygas iš jų remiantis nuolaida, tai yra, taisyklės yra atvirkščiai, kad jas būtų galima naudoti sql užklausoje. Gauname šiuos dalykus:

1. 2% – Suma nuo 1000 iki 4999, o užsakymų skaičius mažesnis nei 10.

2. 5% – Suma nuo 5000 iki 9999, o užsakymų skaičius mažesnis nei 10.

3. 7% – užsakymų skaičius nuo 10 iki 19, o suma mažesnė nei 10000

4. 8% – Kiekis nuo 20 arba suma nuo 10000

Dabar belieka tai užsirašyti. Gaukite šią sql užklausą

Rodydami pavadinimą ir kitus duomenis pasirinkite pavadinimą, order_count, total_sum, -- O dabar rodoma nuolaida CASE -- Pirmoji taisyklė 2 % WHEN c.total_sum >= 1000 ir c.total_sum<= 4999 and c.order_count < 10 THEN 2 -- Второе правило 5% WHEN c.total_sum >= 5000 ir c.total_sum<= 9999 and c.order_count < 10 THEN 5 -- Третье правило 7% WHEN c.total_sum < 10000 and c.order_count >= 10 ir c.užsakymų_skaičius<= 19 THEN 5 -- Четвертое правило 8% WHEN c.total_sum >= 10 000 arba c.užsakymų_skaičius >= 20 TAD 5 – neatitiko jokios taisyklės, todėl nuolaida yra 0. KITAIP 0 PABAIGA kaip nuolaida iš kliento c

Vykdydami gauname tokią lentelę:

Kaip matote, dviem pirkėjams buvo pritaikyta 8 procentų, o vienam – 2 procentų nuolaida. Tuo pačiu su kiekvienu užsakymu procentas bus skaičiuojamas automatiškai ir nieko koreguoti nereikės. Pavyzdžiui, jei Petya suma padidės iki 5000, tada jo nuolaida automatiškai padidės iki 5% (bent jau, nes vis dar yra daug užsakymų).