Et eksempel på å lage en spørring (Query) i en MS SQL Server-database. Databasen ligger i en lokal *.mdf-fil. Hvordan skrive SQL-spørringer - Detaljerte eksempler Plassering av en DataGridView-kontroll og oppsett av databasekommunikasjon

Eksempel

La oss lage en visning bestående av kurs med 3 studiepoeng:

24. Aggregerte funksjoner

Disse funksjonene brukes for å få et samlet resultat relatert til de aktuelle dataene. Følgende er ofte brukte aggregerte funksjoner:

• COUNT (col_name) - returnerer antall rader;
• SUM (col_name) - returnerer summen av verdiene i denne kolonnen;
• AVG (col_name) - returnerer gjennomsnittsverdien for en gitt kolonne;
• MIN (col_name) - returnerer den minste verdien av en gitt kolonne;
• MAX (col_name) – Returnerer den største verdien av en gitt kolonne.

25. Nestede underspørringer

Nestede underspørringer er SQL-spørringer som inkluderer SELECT-, FROM- og WHERE-klausuler nestet i en annen spørring.

Eksempel

La oss finne kurs som ble undervist høsten 2009 og våren 2010:

SELECT DISTINCT course_id FROM section WHERE semester = 'Høst' OG år= 2009 OG course_id IN (SELECT course_id FROM section WHERE semester = 'Vår' OG år= 2010);

Hver av oss møter og bruker regelmessig ulike databaser. Når vi velger en e-postadresse, jobber vi med en database. Databaser brukes av søketjenester, banker til å lagre kundedata mv.

Men til tross for konstant bruk av databaser, selv for mange programvaresystemutviklere er det mange "blanke flekker" på grunn av forskjellige tolkninger av de samme begrepene. Vi vil gi en kort definisjon av grunnleggende databasetermer før vi dekker SQL-språket. Så.

Database - en fil eller samling av filer for lagring av ordnede datastrukturer og deres relasjoner. Svært ofte kalles et styringssystem en database - det er bare et oppbevaringssted for informasjon i et bestemt format og kan fungere med ulike DBMS-er.

Bord - La oss forestille oss en mappe der dokumenter er lagret, gruppert i henhold til en bestemt egenskap, for eksempel en liste over bestillinger for den siste måneden. Dette er en tabell i en datamaskin En separat tabell har sitt eget unike navn.

Data-type - typen informasjon som er tillatt å lagre i en egen kolonne eller rad. Dette kan være tall eller tekst av et bestemt format.

Kolonne og rad– Vi har alle jobbet med regneark, som også har rader og kolonner. Enhver relasjonsdatabase fungerer med tabeller på en lignende måte. Rader kalles noen ganger poster.

Primærnøkkel- Hver rad i en tabell kan ha én eller flere kolonner for å identifisere den unikt. Uten en primærnøkkel er det svært vanskelig å oppdatere, endre og slette de ønskede radene.

Hva er SQL?

SQL(Engelsk - strukturert spørringsspråk) ble utviklet kun for arbeid med databaser og er for tiden standarden for alle populære DBMS-er. Språksyntaksen består av et lite antall operatorer og er lett å lære. Men til tross for sin tilsynelatende enkelhet, tillater den opprettelsen av sql-spørringer for komplekse operasjoner med en database av alle størrelser.

Siden 1992 har det vært en generelt akseptert standard kalt ANSI SQL. Den definerer den grunnleggende syntaksen og funksjonene til operatører og støttes av alle DBMS-markedsledere, for eksempel ORACLE. Det er umulig å vurdere alle funksjonene til språket i en kort artikkel, så vi vil kort vurdere bare grunnleggende SQL-spørringer. Eksempler viser tydelig språkets enkelhet og muligheter:

• lage databaser og tabeller;
• datasampling;
• legge til poster;
• endring og sletting av informasjon.

SQL-datatyper

Alle kolonner i en databasetabell lagrer samme datatype. Datatyper i SQL er de samme som i andre programmeringsspråk.

Vi lager tabeller og databaser

Det er to måter å lage nye databaser, tabeller og andre spørringer på i SQL:

• via DBMS-konsollen
• Bruke de interaktive administrasjonsverktøyene som følger med databaseserveren.

En ny database opprettes av operatøren LAG DATABASE<наименование базы данных>; . Som du kan se, er syntaksen enkel og konsis.

Vi lager tabeller inne i databasen ved å bruke CREATE TABLE-setningen med følgende parametere:

• tabellnavn
• kolonnenavn og datatyper

Som et eksempel, la oss lage en varetabell med følgende kolonner:

Lag en tabell:

LAG TABELL Vare

(vare_id CHAR(15) IKKE NULL,

vendor_id CHAR(15) NOT NULL,

varenavn CHAR(254) NULL,

varepris DESIMAL(8,2) NULL,

commodity_desc VARCHAR(1000) NULL);

Tabellen består av fem kolonner. Etter navnet kommer datatypen, kolonnene er atskilt med komma. Kolonneverdien kan godta tomme verdier (NULL) eller må fylles ut (IKKE NULL), og dette bestemmes når tabellen opprettes.

Henter data fra en tabell

Datahentingsoperatoren er den mest brukte SQL-spørringen. For å få informasjon må vi angi hva vi ønsker å velge fra en slik tabell. Først et enkelt eksempel:

VELG varenavn FRA vare

Etter SELECT-setningen spesifiserer vi navnet på kolonnen for å få informasjon, og FROM definerer tabellen.

Resultatet av spørringen vil være alle rader i tabellen med Commodity_name-verdier i den rekkefølgen de ble lagt inn i databasen, dvs. uten noen sortering. En ekstra ORDER BY-klausul brukes til å bestille resultatet.

For å spørre etter flere felt, liste dem atskilt med komma, som i følgende eksempel:

SELECT commodity_id, commodity_name, commodity_price FROM Commodity

Det er mulig å få verdien av alle kolonnene i en rad som et spørringsresultat. For å gjøre dette, bruk "*"-tegnet:

VELG * FRA Råvare

• I tillegg støtter SELECT:
• Sortering av data (ORDER BY operator)
• Utvalg i henhold til forhold (HVOR)
• Grupperingsterm (GROUP BY)

Legg til en linje

For å legge til en rad i en tabell, brukes SQL-spørringer med INSERT-operatoren. Tilsetning kan gjøres på tre måter:

• legg til en ny hel linje;
• streng del;
• søkeresultater.

For å legge til en hel rad, må du spesifisere tabellnavnet og verdiene til kolonnene (feltene) i den nye raden. Her er et eksempel:

SETT INN I RÅVAREVERDIER("106", "50", "Coca-Cola", "1,68", "Ingen alkohol ,)

Eksemplet legger til et nytt produkt i tabellen. Verdier er oppført etter VERDIER for hver kolonne. Hvis det ikke er noen tilsvarende verdi for kolonnen, må NULL angis. Kolonnene er fylt ut med verdier i den rekkefølgen som ble spesifisert da tabellen ble opprettet.

Hvis du bare legger til deler av en rad, må du spesifisere navnene på kolonnene eksplisitt, som i eksemplet:

INSERT INTO Commodity (vare-id, leverandør-id, varenavn)

VERDIER("106", "50", "Coca-Cola",)

Vi skrev bare inn identifikatorene til produktet, leverandøren og navnet, og la de resterende feltene tomme.

Legger til søkeresultater

INSERT brukes først og fremst til å legge til rader, men kan også brukes til å legge til resultatene av en SELECT-setning.

Endre data

For å endre informasjon i feltene i en databasetabell, må du bruke UPDATE-setningen. Operatøren kan brukes på to måter:

• Alle rader i tabellen er oppdatert.
• Bare for en bestemt linje.

OPPDATERING består av tre hovedelementer:

• tabell der endringer må gjøres;
• feltnavn og deres nye verdier;
• betingelsene for å velge rader som skal endres.

La oss se på et eksempel. La oss si at prisen på et produkt med ID=106 har endret seg, så denne linjen må oppdateres. Vi skriver følgende operatør:

OPPDATERING Råvare SET commodity_price = "3.2" WHERE commodity_id = "106"

Vi spesifiserte navnet på tabellen, i vårt tilfelle Commodity, der oppdateringen skal utføres, deretter etter SET - den nye verdien av kolonnen og fant ønsket post ved å spesifisere den nødvendige ID-verdien i WHERE.

For å endre flere kolonner, etterfølges SET-setningen av flere kolonne-verdi-par atskilt med komma. La oss se på et eksempel der navnet og prisen på et produkt er oppdatert:

OPPDATERING RåvareSETT commodity_name=’Fanta’, commodity_price = "3.2" WHERE commodity_id = "106"

For å fjerne informasjon i en kolonne kan du tildele den verdien NULL hvis tabellstrukturen tillater det. Det må huskes at NULL er nøyaktig "ingen" verdi, og ikke null i form av tekst eller tall. La oss fjerne produktbeskrivelsen:

OPPDATERING Råvare SET commodity_desc = NULL WHERE commodity_id = "106"

Fjerner rader

SQL-spørringer for å slette rader i en tabell utføres ved hjelp av DELETE-setningen. Det er to brukstilfeller:

• Enkelte rader i tabellen slettes;
• Alle rader i tabellen slettes.

Et eksempel på sletting av én rad fra en tabell:

DELETE FROM Commodity WHERE commodity_id = "106"

Etter DELETE FROM angir vi navnet på tabellen der radene vil bli slettet. WHERE-leddet inneholder betingelsen for at rader skal velges for sletting. I eksemplet sletter vi produktlinjen med ID=106. Å spesifisere HVOR er veldig viktig fordi utelate denne setningen vil alle rader i tabellen slettes. Dette gjelder også endring av verdi på felt.

DELETE-setningen spesifiserer ikke kolonnenavn eller metategn. Den sletter rader fullstendig, men den kan ikke slette en enkelt kolonne.

Bruker SQL i Microsoft Access

Brukes vanligvis interaktivt for å lage tabeller, databaser, for å administrere, endre, analysere data i en database og implementere SQL Access-spørringer gjennom en praktisk interaktiv spørringsdesigner (Query Designer), som du kan bruke til å bygge og umiddelbart utføre SQL-setninger av enhver kompleksitet.

Servertilgangsmodusen støttes også, der Access DBMS kan brukes som en generator av SQL-spørringer til enhver ODBC-datakilde. Denne funksjonen lar Access-applikasjoner samhandle med alle formater.

SQL-utvidelser

Siden SQL-spørringer ikke har alle egenskapene til prosedyreprogrammeringsspråk, som looper, forgrening osv., utvikler DBMS-produsenter sin egen versjon av SQL med avanserte funksjoner. Først av alt er dette støtte for lagrede prosedyrer og standardoperatører av prosedyrespråk.

De vanligste dialektene på språket:

• Oracle Database - PL/SQL
• Interbase, Firebird - PSQL
• Microsoft SQL Server - Transact-SQL
• PostgreSQL - PL/pgSQL.

SQL på Internett

MySQL DBMS distribueres under den gratis GNU General Public License. Det er en kommersiell lisens med mulighet til å utvikle tilpassede moduler. Som en komponent er den inkludert i de mest populære samlingene av Internett-servere, som XAMPP, WAMP og LAMP, og er den mest populære DBMS for utvikling av applikasjoner på Internett.

Den ble utviklet av Sun Microsystems og støttes for tiden av Oracle Corporation. Databaser på opptil 64 terabyte i størrelse, SQL:2003 syntaksstandard, replikering av databaser og skytjenester støttes.

Tabelluttrykk kalles underspørringer som brukes der tilstedeværelsen av en tabell er forventet. Det finnes to typer tabelluttrykk:

avledede tabeller;

generaliserte tabelluttrykk.

Disse to formene for tabelluttrykk diskuteres i de følgende underavsnittene.

Avledede tabeller

Avledet tabell er et tabelluttrykk inkludert i FROM-leddet til en spørring. Avledede tabeller kan brukes i tilfeller der bruk av kolonnealiaser ikke er mulig fordi SQL-oversetteren behandler en annen setning før aliaset er kjent. Eksemplet nedenfor viser et forsøk på å bruke et kolonnealias i en situasjon der en annen klausul blir behandlet før aliaset er kjent:

BRUK SampleDb; SELECT MONTH(EnterDate) som enter_month FROM Works_on GROUP BY enter_month;

Hvis du prøver å kjøre denne spørringen, vises følgende feilmelding:

Melding 207, nivå 16, tilstand 1, linje 5 Ugyldig kolonnenavn "enter_month". (Melding 207: Nivå 16, tilstand 1, linje 5 Ugyldig kolonnenavn enter_month)

Årsaken til feilen er at GROUP BY-leddet behandles før den tilsvarende listen med SELECT-setningen behandles, og aliaset for enter_month-kolonnen er ukjent når gruppen behandles.

Dette problemet kan løses ved å bruke en avledet tabell som inneholder den foregående spørringen (uten GROUP BY-leddet) fordi FROM-leddet kjøres før GROUP BY-leddet:

BRUK SampleDb; VELG enter_month FROM (VELG MONTH(EnterDate) som enter_month FROM Works_on) AS m GROUP BY enter_month;

Resultatet av denne spørringen vil være slik:

Vanligvis kan et tabelluttrykk plasseres hvor som helst i en SELECT-setning der et tabellnavn kan vises. (Resultatet av et tabelluttrykk er alltid en tabell eller, i spesielle tilfeller, et uttrykk.) Eksemplet nedenfor viser bruken av et tabelluttrykk i select-listen til en SELECT-setning:

Resultatet av denne spørringen:

Generiske tabelluttrykk

Vanlige tabelluttrykk (OTB) er et navngitt tabelluttrykk som støttes av Transact-SQL-språket. Vanlige tabelluttrykk brukes i følgende to typer spørringer:

ikke-rekursiv;

tilbakevendende.

Disse to typene forespørsler diskuteres i de følgende avsnittene.

OTB og ikke-rekursive spørringer

Den ikke-rekursive formen for OTB kan brukes som et alternativ til avledede tabeller og visninger. Vanligvis bestemmes OTB av MED klausuler og en ekstra spørring som refererer til navnet brukt i WITH-leddet. I Transact-SQL er betydningen av nøkkelordet WITH tvetydig. For å unngå tvetydighet bør setningen foran WITH-setningen avsluttes med semikolon.

BRUK AdventureWorks2012; SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (VELG AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") OG Frakt > (VELG AVG(TotalDue) FROM Sales.SalesOrderHeader "Order2Dato YEAR5) ")/2.5;

Spørringen i dette eksemplet velger bestillinger hvis totale avgifter (TotalDue) er høyere enn gjennomsnittet av alle avgifter og hvis fraktkostnader (frakt) er større enn 40 % av gjennomsnittlig avgifter. Hovedegenskapen til denne spørringen er lengden, siden undersøkingen må skrives to ganger. En mulig måte å redusere mengden spørringskonstruksjon på er å lage en visning som inneholder en underspørring. Men denne løsningen er litt komplisert fordi den krever å opprette en visning og deretter slette den etter at spørringen er ferdig utført. En bedre tilnærming ville være å lage en OTB. Eksemplet nedenfor viser bruken av ikke-rekursiv OTB, som forkorter spørringsdefinisjonen ovenfor:

BRUK AdventureWorks2012; WITH price_calc(year_2005) AS (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT year_2005 FROMAND price_0cal) (SELECT year_2005 FROMAND price_0cal) ) /2,5;

WITH-leddets syntaks i ikke-rekursive søk er som følger:

Parameteren cte_name representerer OTB-navnet som definerer den resulterende tabellen, og column_list-parameteren representerer listen over kolonner i tabelluttrykket. (I eksemplet ovenfor kalles OTB price_calc og har én kolonne, year_2005.) Inner_query-parameteren representerer en SELECT-setning som spesifiserer resultatsettet til det tilsvarende tabelluttrykket. Det definerte tabelluttrykket kan deretter brukes i den ytre_query. (Den ytre spørringen i eksempelet ovenfor bruker OTB price_calc og dens år_2005-kolonne for å forenkle det dobbelt nestede søket.)

OTB og rekursive søk

Denne delen presenterer materiale med økt kompleksitet. Derfor, når du leser den for første gang, anbefales det å hoppe over den og gå tilbake til den senere. OTB-er kan brukes til å implementere rekursjoner fordi OTB-er kan inneholde referanser til seg selv. Den grunnleggende OTB-syntaksen for et rekursivt søk ser slik ut:

Parameterne cte_name og column_list har samme betydning som i OTB for ikke-rekursive spørringer. Brødteksten i en WITH-klausul består av to spørringer kombinert av operatøren UNION ALLE. Den første spørringen kalles bare én gang, og den begynner å akkumulere resultatet av rekursjonen. Den første operanden til UNION ALL-operatøren refererer ikke til OTB. Denne spørringen kalles en referansespørring eller kilde.

Den andre spørringen inneholder en referanse til OTB og representerer dens rekursive del. På grunn av dette kalles det et rekursivt medlem. I det første kallet til den rekursive delen representerer OTB-referansen resultatet av referansespørringen. Det rekursive medlemmet bruker resultatet av det første spørringsanropet. Etter dette kaller systemet opp den rekursive delen igjen. Et anrop til et rekursivt medlem stopper når et tidligere anrop til det returnerer et tomt resultatsett.

UNION ALL-operatøren kobler seg til de nåværende akkumulerte radene, så vel som ytterligere rader lagt til av det gjeldende anropet til det rekursive medlemmet. (Tilstedeværelsen av UNION ALL-operatøren betyr at dupliserte rader ikke vil bli fjernet fra resultatet.)

Til slutt spesifiserer parameteren outer_query den ytre spørringen som OTB bruker for å hente alle anrop til sammenføyningen til begge medlemmene.

For å demonstrere den rekursive formen av OTB, bruker vi flytabellen definert og fylt ut med koden vist i eksemplet nedenfor:

BRUK SampleDb; CREATE TABLE Fly(ContainingAssembly VARCHAR(10), ContainedAssembly VARCHAR(10), QuantityContained INT, UnitCost DESIMAL(6,2)); INSERT INTO Airplane VALUES ("Fly", "Fuselage", 1, 10); INSERT INTO Airplane VALUES ("Fly", "Vinger", 1, 11); INSERT INTO Airplane VALUES ("Fly", "hale", 1, 12); SETTE INN I FLYVERDIER ("Fuselage", "Salon", 1, 13); SETTE INN I FLYVERDIER ("Fuselage", "Cockpit", 1, 14); INSERT INTO Airplane VALUES ("Fuselage", "Nose",1, 15); INSERT INTO Airplane VALUES ("Cabin", NULL, 1,13); INSERT INTO Airplane VALUES ("Cockpit", NULL, 1, 14); INSERT INTO Airplane VALUES ("Nese", NULL, 1, 15); INSERT INTO Airplane VALUES ("Vinger", NULL,2, 11); INSERT INTO Airplane VALUES ("hale", NULL, 1, 12);

Flytabellen har fire kolonner. Kolonnen ContainingAssembly identifiserer sammenstillingen, og ContainedAssembly-kolonnen identifiserer delene (en etter en) som utgjør den tilsvarende sammenstillingen. Figuren nedenfor viser en grafisk illustrasjon av en mulig type fly og dets komponenter:

Flytabellen består av følgende 11 rader:

Følgende eksempel bruker WITH-leddet til å definere en spørring som beregner den totale kostnaden for hvert bygg:

BRUK SampleDb; WITH list_of_parts(assembly1, quantity, cost) AS (SELECT ContainingAssembly, QuantityContained, UnitCost FROM Airplane WHERE ContainedAssembly IS NULL UNION ALL SELECT a.ContainingAssembly, a.QuantityContained, CAST(l.quantity * DEC IMALcost AS) ) FROM list_of_parts l, Fly a WHERE l.assembly1 = a.ContainedAssembly) SELECT assembly1 "Part", quantity "Quantity", cost "Price" FROM list_of_parts;

WITH-leddet definerer en OTB-liste kalt list_of_parts, bestående av tre kolonner: assembly1, quantity og cost. Den første SELECT-setningen i eksemplet kalles bare én gang for å lagre resultatene fra det første trinnet i rekursjonsprosessen. SELECT-setningen på den siste linjen i eksemplet viser følgende resultat.