hyvä ystäväni Aaron Bertrand inspiroi minua kirjoittamaan tämän artikkelin. Hän muistutti, kuinka joskus pidämme asioita itsestäänselvyyksinä, kun ne tuntuvat itsestäänselvyyksiltä, eivätkä aina vaivaudu tarkistamaan koko tarinaa niiden takana. Merkitys T-SQL on, että joskus oletamme, että tiedämme kaiken on tiedettävä tiettyjä T-SQL ominaisuuksia, ja älä aina vaivaudu tarkistaa dokumentaatio nähdä, jos siellä on enemmän niitä. Tässä artikkelissa I kattaa useita T-SQL ominaisuuksia, jotka ovat joko usein kokonaan unohdetaan, tai jotka tukevat parametreja tai ominaisuuksia, jotka usein unohdetaan. Jos sinulla on esimerkkejä oman T – SQL helmiä, jotka usein unohdetaan, älä jaa ne kommentit osassa tämän artikkelin.
ennen kuin aloitat tämän artikkelin lukemisen, kysy itseltäsi, mitä tiedät seuraavista T-SQL-ominaisuuksista: EOMONTH, TRANSLATE, TRIM, CONCAT ja CONCAT_WS, loki, kohdistinmuuttujat ja yhdistä tulosteeseen.
käytän esimerkeissäni näytetietokantaa nimeltä TSQLV5. Löydät komentosarjan, joka luo ja kansoittaa tämän tietokannan täältä, ja sen ER-kaavion täältä.
eomonthilla on toinen parametri
eomonth-funktio otettiin käyttöön SQL Server 2012: ssa. Monet ihmiset ajattelevat, että se tukee vain yhtä parametria, jolla on syöttöpäivä, ja että se yksinkertaisesti palauttaa kuukauden lopun päivämäärän, joka vastaa syöttöpäivää.
harkitse hieman hienostuneempaa tarvetta laskea edellisen kuukauden loppu. Oletetaan esimerkiksi, että sinun täytyy kysellä myynnistä.Tilaustaulukko ja edellisen kuukauden lopussa tehdyt palautustilaukset.
yksi tapa tämän saavuttamiseksi on soveltaa SYSDATETIMEEN eomonth-funktiota, jotta saadaan kuluvan kuukauden Päättymispäivä, ja sitten soveltaa DATEADD-funktiota, joka vähentää kuukauden tuloksesta näin:
USE TSQLV5; SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(DATEADD(month, -1, SYSDATETIME()));
huomaa, että jos todella suoritat tämän kyselyn tsqlv5-näytetietokannassa, saat tyhjän tuloksen, sillä viimeinen taulukkoon kirjattu tilauspäivä on 6.toukokuuta 2019. Jos taulukossa olisi kuitenkin tilauksia, joiden tilauspäivä osuu edellisen kuukauden viimeiselle päivälle, kysely olisi palauttanut ne.
monet ihmiset eivät ymmärrä, että EOMONTH tukee toista parametria, jossa ilmoitetaan kuinka monta kuukautta lisätään tai vähennetään. Tässä funktion syntaksi:
EOMONTH ( start_date )
tehtävämme voidaan saavuttaa helpommin ja luonnollisesti yksinkertaisesti määrittämällä -1 funktion toiseksi parametriksi, kuten niin:
SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(SYSDATETIME(), -1);
TRANSLATE on joskus yksinkertaisempi kuin REPLACE
monelle korvaava funktio ja sen toiminta on tuttua. Käytät sitä, kun haluat korvata kaikki yhden substraatin esiintymät toisella syöttömerkkijonossa. Joskus, kuitenkin, kun sinulla on useita korvaavia että sinun täytyy soveltaa, käyttämällä REPLACE on hieman hankalaa ja johtaa mutkikas ilmaisuja.
esimerkkinä oletetaan, että sinulle annetaan tulomerkkijono @s, joka sisältää luvun espanjalaisella muotoilulla. Espanjassa käytetään jaksoa tuhansien ryhmien erottimena ja pilkkua desimaalierottimena. Sinun täytyy muuntaa tulo Yhdysvaltain muotoiluun, jossa pilkkua käytetään erotin tuhansien ryhmien, ja ajan desimaalierottimena.
yhdellä KORVAUSFUNKTION kutsulla voidaan korvata vain kaikki yhden merkin tai substraatin esiintymät toisella. Jos haluat käyttää kahta vaihdetta (jaksoja pilkuille ja pilkkuja jaksoille), sinun on peitettävä funktiokutsuja. Hankala osa on, että jos käytät korvaa kerran muuttaa aikoja pilkuksi, ja sitten toisen kerran vastaan tulos muuttaa pilkut periodeiksi, päädyt vain periodeja. Kokeile:
DECLARE @s AS VARCHAR(20) = '123.456.789,00'; SELECT REPLACE(REPLACE(@s, '.', ','), ',', '.');
saat seuraavan tuotoksen:
123.456.789.00
Jos haluat pitää kiinni korvaa-funktion käytöstä, tarvitset kolme funktiokutsua. Yksi, joka korvaa kaudet neutraalilla merkillä, jonka tiedät, joka ei normaalisti näy datassa (vaikkapa~). Toinen vastaan tulos korvaa kaikki pilkut jaksoilla. Toinen vastaan tulos korvata kaikki esiintymät väliaikaisen merkin (~esimerkissämme) pilkuilla. Tässä koko ilmaisu:
DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT REPLACE(REPLACE(REPLACE(@s, '.', '~'), ',', '.'), '~', ',');
tällä kertaa saat oikean tuotoksen:
123,456,789.00
se on tavallaan toteutettavissa, mutta johtaa pitkään ja mutkikkaaseen ilmaisuun. Mitä jos hakisi lisää sijaisia?
monet ihmiset eivät ole tietoisia siitä, että SQL Server 2017 esitteli uuden funktion nimeltä TRANSLATE, joka yksinkertaistaa tällaisia korvauksia paljon. Tässä funktion syntaksi:
TRANSLATE ( inputString, characters, translations )
toinen tulo (merkit) on merkkijono, jossa on lista yksittäisistä merkeistä, jotka haluat korvata, ja kolmas tulo (käännökset) on merkkijono, jossa on luettelo vastaavista merkeistä, joilla haluat korvata lähdemerkit. Tämä tarkoittaa luonnollisesti sitä, että toisessa ja kolmannessa parametrissa on oltava sama määrä merkkejä. Tärkeää toiminnossa on se, että se ei tee erillisiä ohituksia jokaiselle vaihdolle. Jos se teki,se olisi mahdollisesti johtanut samaan bugiin kuin ensimmäisessä esimerkissä, jonka osoitin käyttämällä kahta korvaa-toimintoa. Näin ollen, käsittely-meidän tehtävä tulee ei-murskata:
DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT TRANSLATE(@s, '.,', ',.');
Tämä koodi tuottaa haluttu tuotos:
123,456,789.00
that ’ s pretty neat!
TRIM on enemmän kuin LTRIM(RTRIM ())
SQL Server 2017 esitteli tuen funktion TRIMILLE. Monet ihmiset, minä mukaan lukien, aluksi vain olettaa, että se on vain yksinkertainen oikotie LTRIM(RTRIM (input)). Kuitenkin, jos tarkistaa asiakirjat, huomaat, että se on itse asiassa tehokkaampi kuin että.
ennen kuin menen yksityiskohtiin, harkitse seuraavaa tehtävää: koska syöttömerkkijono @s, poista johtavat ja perään viillot (taaksepäin ja eteenpäin). Oletetaan esimerkiksi, että @s sisältää seuraavan merkkijonon:
//\\ remove leading and trailing backward (\) and forward (/) slashes \\//
haluttu tuotos on:
remove leading and trailing backward (\) and forward (/) slashes
huomaa, että tuotoksen tulee säilyttää johto-ja jälkitilat.
Jos et tiedä TRIM on täynnä ominaisuuksia, tässä on yksi tapa saatat olla ratkaista tehtävän:
DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRANSLATE(TRIM(TRANSLATE(TRIM(TRANSLATE(@s, ' /', '~ ')), ' \', '^ ')), ' ^~', '\/ ') AS outputstring;
ratkaisu alkaa käyttämällä KÄÄNTÄÄ korvata kaikki tilat neutraali merkki (~) ja vinoviivoja tilat, sitten käyttämällä TRIM leikata johtava ja välilyönnit alkaen tulos. Tämä vaihe lähinnä trimmaa johtavat ja perään eteenpäin viillot, väliaikaisesti käyttäen ~ sijasta alkuperäisen välilyöntejä. Tässä on tämän vaiheen tulos:
\\~remove~leading~and~trailing~backward~(\)~and~forward~( )~slashes~\\
toisessa vaiheessa käytetään Translatea korvaamaan kaikki välilyönnit toisella neutraalilla merkillä (^) ja taaksepäin viilletään välilyönneillä, minkä jälkeen tuloksesta trimmataan johtavia ja perään tulevia välilyöntejä. Tämä vaihe lähinnä trimmaa johtavat ja perään taaksepäin viiltoja, väliaikaisesti käyttäen ^ sijasta välitilat. Tässä on tämän vaiheen tulos:
~remove~leading~and~trailing~backward~( )~and~forward~(^)~slashes~
viimeisessä vaiheessa TRANSLATE korvaa välilyönnit taaksepäin viilloilla, ^ eteenpäin viilloilla ja ~ välilyönneillä, jolloin saadaan haluttu ulostulo:
remove leading and trailing backward (\) and forward (/) slashes
harjoituksena, yritä ratkaista tämä tehtävä pre-SQL Server 2017-yhteensopivalla ratkaisulla, jossa et voi käyttää trimmausta ja kääntämistä.
Back to SQL Server 2017 and above, if you did vaivautunut checking the documentation, you would have discovered that TRIM is more sophisticated that what you thought originally. Tässä funktion syntaksi:
TRIM ( string )
osan valinnaisilla merkeillä voi määrittää yhden tai useamman merkin, jonka haluaa trimmata syötemerkkijonon alusta ja lopusta. Meidän tapauksessamme tarvitsee vain täsmentää ” / \ ”täksi osaksi näin:
DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRIM( '/\' FROM @s) AS outputstring;
se on aika merkittävä parannus edelliseen ratkaisuun verrattuna!
CONCAT ja CONCAT_WS
Jos olet työskennellyt T-SQL: n kanssa jonkin aikaa, tiedät, kuinka hankalaa on käsitellä Nulleja, kun sinun täytyy yhdistää merkkijonoja. Esimerkkinä voidaan pitää työntekijöiden sijaintitietoja henkilöstötaulukossa:
SELECT empid, country, region, cityFROM HR.Employees;
Tämä kysely tuottaa seuraavan tuotoksen:
empid country region city----------- --------------- --------------- ---------------1 USA WA Seattle2 USA WA Tacoma3 USA WA Kirkland4 USA WA Redmond5 UK NULL London6 UK NULL London7 UK NULL London8 USA WA Seattle9 UK NULL London
huomaa, että joillekin työntekijöille alueen osa on merkityksetön ja epäolennaista aluetta edustaa nolla. Oletetaan, että sinun täytyy yhdistää sijaintiosat (maa, alue ja kaupunki), käyttäen pilkkua erotin, mutta välittämättä NULL alueilla. Kun alue on relevantti, halutaan tuloksella muotoa <coutry>,<region>,<city> ja kun alue on epäoleellinen, halutaan tuloksella muotoa <country>,<city>. Tavallisesti jonkin asian yhdistäminen nollaan tuottaa nollatuloksen. Voit muuttaa tätä käyttäytymistä sammuttamalla CONCAT_NULL_YIELDS_NULL – istuntovaihtoehdon, mutta en suosittele standardoimattoman käyttäytymisen ottamista käyttöön.
Jos et tiedä olemassaolosta CONCAT ja CONCAT_WS toimintoja, sinun olisi todennäköisesti käyttää ISNULL tai SULAUTUVAT korvata NULL tyhjä merkkijono, kuten niin:
SELECT empid, country + ISNULL(',' + region, '') + ',' + city AS locationFROM HR.Employees;
Tässä on tuotos tämän kyselyn:
empid location----------- -----------------------------------------------1 USA,WA,Seattle2 USA,WA,Tacoma3 USA,WA,Kirkland4 USA,WA,Redmond5 UK,London6 UK,London7 UK,London8 USA,WA,Seattle9 UK,London
SQL Server 2012 käyttöön toiminto CONCAT. Tämä funktio hyväksyy luettelon merkkijonon syötöistä ja yhdistää ne, ja samalla se jättää huomiotta nollat. Joten käyttämällä CONCAT voit yksinkertaistaa ratkaisua näin:
SELECT empid, CONCAT(country, ',' + region, ',', city) AS locationFROM HR.Employees;
silti erotin on erikseen määriteltävä osana funktion syötteitä. Jotta elämämme vielä helpompaa, SQL Server 2017 käyttöön samanlainen toiminto nimeltään CONCAT_WS jossa aloitat osoittamalla erotin, jonka jälkeen kohteet, jotka haluat concatenate. Tällä funktiolla ratkaisu yksinkertaistuu entisestään näin:
SELECT empid, CONCAT_WS(',', country, region, city) AS locationFROM HR.Employees;
seuraava askel on tietenkin mielenlaatu. 1. huhtikuuta 2020 Microsoft aikoo julkaista CONCAT_MR: n.toiminto hyväksyy tyhjän syötön ja selvittää automaattisesti, mitkä elementit haluat sen yhdistävän lukemalla ajatuksesi. Kysely näyttää sitten tältä:
SELECT empid, CONCAT_MR() AS locationFROM HR.Employees;
LOGILLA on toinen parametri
samanlainen kuin eomonth-funktiolla, monet eivät ymmärrä, että aloittamalla jo SQL Server 2012, log-funktio tukee toista parametria, jonka avulla voidaan ilmoittaa logaritmin kanta. Sitä ennen T-SQL tuki funktion LOG(input), joka palauttaa luonnollisen logaritmin input (käyttäen vakiota e kantana), ja LOG10(input), joka käyttää 10: tä kantana.
koska ihmiset eivät olleet tietoisia LOG-funktion toisen parametrin olemassaolosta, kun haluttiin laskea Logb(x), jossa b on muu kuin E ja 10, he tekivät sen usein pitkän matkan. Voit luottaa seuraavaan yhtälöön:
esimerkkinä Log2(8) laskemiseksi käytetään seuraavaa yhtälöä:
T-SQL: lle käännettynä sovelletaan seuraavaa laskutoimitusta:
DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x) / LOG(@b);
DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x, @b);
kursorimuuttuja
Jos olet työskennellyt T-SQL: n kanssa jonkin aikaa, sinulla oli todennäköisesti paljon mahdollisuuksia työskennellä kursoreiden kanssa. Kuten tiedät, kun työskentelet kursorin kanssa, käytät yleensä seuraavia vaiheita:
- julista kursori
- avaa kursori
- iteroi kursoritietueiden kautta
- Sulje kursori
- Deallocate kursori
esimerkkinä, oletetaan, että joudut suorittamaan jonkin tehtävän tietokantaa kohti instanssissasi. Kursoria käyttäen käytetään normaalisti samanlaista koodia:
DECLARE @dbname AS sysname; DECLARE C CURSOR FORWARD_ONLY STATIC READ_ONLY FOR SELECT name FROM sys.databases; OPEN C; FETCH NEXT FROM C INTO @dbname; WHILE @@FETCH_STATUS = 0BEGIN PRINT N'Handling database ' + QUOTENAME(@dbname) + N'...'; /* ... do your thing here ... */ FETCH NEXT FROM C INTO @dbname;END; CLOSE C;DEALLOCATE C;
LÄHIKÄSKY vapauttaa nykyisen tulosjoukon ja vapauttaa lukot. DEALLOCATE-komento poistaa kohdistimen viittauksen, ja kun viimeinen viittaus on deallocated, vapauttaa kohdistimen sisältämät Tietorakenteet. Jos yrität ajaa yllä olevaa koodia kahdesti ilman sulje-ja DEALLOCATE-komentoja, saat seuraavan virheen:
Msg 16915, Level 16, State 1, Line 4A cursor with the name 'C' already exists.Msg 16905, Level 16, State 1, Line 6The cursor is already open.
varmista, että suoritat sulje-ja DEALLOCATE-komennot ennen kuin jatkat.
monet ihmiset eivät ymmärrä, että kun heidän täytyy käyttää kursoria vain yhdessä erässä, mikä on yleisin tapaus, tavallisen kursorin sijaan voi työskennellä kursorimuuttujalla. Kuten mikä tahansa muuttuja, kursorimuuttujan soveltamisala on vain se erä, jossa se on ilmoitettu. Tämä tarkoittaa, että heti erän päätyttyä kaikki muuttujat vanhenevat. Kohdistinmuuttujan avulla, kun erä on valmis, SQL Server sulkeutuu ja deallocates sen automaattisesti, jolloin sinun tarvitsee suorittaa sulje ja DEALLOCATE-komento eksplisiittisesti.
tässä tarkistettu koodi kohdistinmuuttujalla tällä kertaa:
DECLARE @dbname AS sysname, @C AS CURSOR; SET @C = CURSOR FORWARD_ONLY STATIC READ_ONLY FOR SELECT name FROM sys.databases; OPEN @C; FETCH NEXT FROM @C INTO @dbname; WHILE @@FETCH_STATUS = 0BEGIN PRINT N'Handling database ' + QUOTENAME(@dbname) + N'...'; /* ... do your thing here ... */ FETCH NEXT FROM @C INTO @dbname;END;
Se on vain puhtaampi,eikä sinun tarvitse huolehtia kursoriresurssien säilyttämisestä, jos unohdit sulkea ja poistaa kohdistimen.
MERGE with OUTPUT
SQL Server 2005: n MUUTOSLAUSEIDEN TULOSTUSLAUSEKKEEN alusta lähtien se osoittautui hyvin käytännölliseksi työkaluksi aina, kun halusi palauttaa tietoja muokatuilta riveiltä. Ihmiset käyttävät tätä ominaisuutta säännöllisesti esimerkiksi arkistointiin, valvontaan ja moniin muihin käyttötapauksiin. Yksi ärsyttävistä asioista tässä ominaisuudessa on kuitenkin se, että jos käytät sitä lisättävien lausekkeiden kanssa, voit palauttaa tietoja vain asetetuilta riveiltä, jolloin tulostesarakkeet on lisätty. Sinulla ei ole pääsyä lähdetaulukon sarakkeisiin, vaikka joskus sinun täytyy palauttaa sarakkeet lähteestä ja sarakkeet kohteesta.
esimerkkinä voidaan pitää taulukoita T1 ja T2, jotka luot ja kansoitat ajamalla seuraavan koodin:
DROP TABLE IF EXISTS dbo.T1, dbo.T2;GO CREATE TABLE dbo.T1(keycol INT NOT NULL IDENTITY PRIMARY KEY, datacol VARCHAR(10) NOT NULL); CREATE TABLE dbo.T2(keycol INT NOT NULL IDENTITY PRIMARY KEY, datacol VARCHAR(10) NOT NULL); INSERT INTO dbo.T1(datacol) VALUES('A'),('B'),('C'),('D'),('E'),('F');
huomaa, että molemmissa taulukoissa avainten tuottamiseen käytetään identiteettiominaisuutta.
Oletetaan, että sinun täytyy kopioida joitakin rivejä T1: stä T2: een; sano ne, joissa keycol % 2 = 1. Haluat käyttää TULOSTUSLAUSEKETTA palauttaaksesi uudet avaimet T2: ssa, mutta haluat myös palauttaa kyseisten avainten rinnalle vastaavat lähdeavaimet T1: stä. Intuitiivinen odotus on käyttää seuraavaa INSERT lauseke:
INSERT INTO dbo.T2(datacol) OUTPUT T1.keycol AS T1_keycol, inserted.keycol AS T2_keycol SELECT datacol FROM dbo.T1 WHERE keycol % 2 = 1;
moniosainen tunniste ”T1.keycolia”ei voitu sitoa.
moni ei ymmärrä, että kumma kyllä tämä rajoitus ei koske YHDISTÄMISLAUSETTA. Joten vaikka se on hieman hankalaa, voit muuntaa INSERTTILAUSEEN YHDISTÄMISLAUSEEKSI, mutta jotta voit tehdä niin, sinun täytyy yhdistää predikaatti aina olla epätosi. Tämä aktivoi kun ei täsmää-lausekkeen ja soveltaa siellä vain tuettua INSERT-toimintoa. Voit käyttää nuken väärää tilaa, kuten 1 = 2. Tässä on täydellinen muunnettu koodi:
MERGE INTO dbo.T2 AS TGTUSING (SELECT keycol, datacol FROM dbo.T1 WHERE keycol % 2 = 1) AS SRC ON 1 = 2WHEN NOT MATCHED THEN INSERT(datacol) VALUES(SRC.datacol)OUTPUT SRC.keycol AS T1_keycol, inserted.keycol AS T2_keycol;
tällä kertaa koodi toimii onnistuneesti tuottaen seuraavan tuotoksen:
T1_keycol T2_keycol----------- -----------1 13 25 3