Piemērs vaicājuma (Query) izveidei MS SQL Server datu bāzē. Datu bāze atrodas lokālā *.mdf failā. Kā uzrakstīt SQL vaicājumus — detalizēti piemēri DataGridView vadīklas ievietošanai un datu bāzes sakaru iestatīšanai

Piemērs

Izveidosim skatu, kas sastāv no kursiem ar 3 kredītpunktiem:

24.Apkopotās funkcijas

Šīs funkcijas tiek izmantotas, lai iegūtu apkopotu rezultātu saistībā ar attiecīgajiem datiem. Tālāk ir plaši izmantotas apkopotās funkcijas.

• COUNT (col_name) - atgriež rindu skaitu;
• SUM (col_name) - atgriež šīs kolonnas vērtību summu;
• AVG (col_name) - atgriež dotās kolonnas vidējo vērtību;
• MIN (kolonnas_nosaukums) - atgriež dotās kolonnas mazāko vērtību;
• MAX (col_name) — atgriež dotās kolonnas lielāko vērtību.

25. Ligzdoti apakšvaicājumi

Ligzdotie apakšvaicājumi ir SQL vaicājumi, kas ietver klauzulas SELECT , FROM un WHERE, kas ligzdotas citā vaicājumā.

Piemērs

Atradīsim kursus, kas tika pasniegti 2009. gada rudenī un 2010. gada pavasarī:

SELECT DISTINCT kursa_id FROM sadaļa WHERE semestris = 'Rudens' UN gads= 2009 AND kursa_id IN (SELECT kursa_id FROM sadaļa WHERE semestris = 'Pavasaris' UN gads= 2010);

Katrs no mums regulāri sastopas un lietojam dažādas datu bāzes. Izvēloties e-pasta adresi, mēs strādājam ar datu bāzi. Datu bāzes izmanto meklēšanas dienesti, bankas klientu datu glabāšanai utt.

Bet, neskatoties uz pastāvīgo datu bāzu izmantošanu, pat daudziem programmatūras sistēmu izstrādātājiem ir daudz “tukšu vietu”, jo vienu un to pašu terminu interpretācijas ir atšķirīgas. Pirms SQL valodas apskatīšanas mēs sniegsim īsu datu bāzes terminu definīciju. Tātad.

Datu bāze - fails vai failu kolekcija sakārtotu datu struktūru un to attiecību glabāšanai. Ļoti bieži vadības sistēmu sauc par datu bāzi – tā ir tikai informācijas krātuve noteiktā formātā un var strādāt ar dažādām DBVS.

Tabula - Iedomāsimies mapi, kurā glabājas dokumenti, sagrupēti pēc noteiktas pazīmes, piemēram, pasūtījumu saraksts par pēdējo mēnesi. Šī ir tabula datorā. Atsevišķai tabulai ir savs unikālais nosaukums.

Datu tips - informācijas veids, ko atļauts glabāt atsevišķā kolonnā vai rindā. Tie var būt skaitļi vai noteikta formāta teksts.

Kolonna un rinda- Mēs visi esam strādājuši ar izklājlapām, kurās ir arī rindas un kolonnas. Jebkura relāciju datu bāze ar tabulām darbojas līdzīgi. Rindas dažreiz sauc par ierakstiem.

Primārā atslēga- Katrai tabulas rindai var būt viena vai vairākas kolonnas, lai to unikāli identificētu. Bez primārās atslēgas ir ļoti grūti atjaunināt, mainīt un dzēst vēlamās rindas.

Kas ir SQL?

SQL(angļu — strukturētā vaicājuma valoda) tika izstrādāta tikai darbam ar datu bāzēm un šobrīd ir standarts visām populārajām DBVS. Valodas sintakse sastāv no neliela skaita operatoru, un to ir viegli iemācīties. Bet, neskatoties uz šķietamo vienkāršību, tas ļauj izveidot SQL vaicājumus sarežģītām operācijām ar jebkura izmēra datu bāzi.

Kopš 1992. gada pastāv vispārpieņemts standarts, ko sauc par ANSI SQL. Tas nosaka operatoru pamatsintaksi un funkcijas, un to atbalsta visi DBVS tirgus līderi, piemēram, ORACLE. Nav iespējams vienā īsā rakstā aplūkot visas valodas iespējas, tāpēc īsumā apskatīsim tikai pamata SQL vaicājumus. Piemēri skaidri parāda valodas vienkāršību un iespējas:

• datu bāzu un tabulu izveide;
• datu paraugu ņemšana;
• ierakstu pievienošana;
• informācijas grozīšana un dzēšana.

SQL datu tipi

Visas datu bāzes tabulas kolonnas glabā vienu un to pašu datu tipu. Datu tipi SQL ir tādi paši kā citās programmēšanas valodās.

Veidojam tabulas un datu bāzes

Ir divi veidi, kā izveidot jaunas datu bāzes, tabulas un citus vaicājumus SQL:

• izmantojot DBVS konsoli
• Izmantojot datu bāzes serverī iekļautos interaktīvos administrēšanas rīkus.

Operators izveido jaunu datu bāzi IZVEIDOT DATU BĀZI<наименование базы данных>; . Kā redzat, sintakse ir vienkārša un kodolīga.

Mēs izveidojam tabulas datu bāzē, izmantojot priekšrakstu CREATE TABLE ar šādiem parametriem:

• tabulas nosaukums
• kolonnu nosaukumi un datu veidi

Piemēram, izveidosim preču tabulu ar šādām kolonnām:

Izveidojiet tabulu:

IZVEIDOT TABULU Prece

(preces_id CHAR(15) NOT NULL,

vendor_id CHAR(15) NOT NULL,

preces_nosaukums CHAR(254) NULL,

commodity_price DECIMAL(8,2) NULL,

commodity_desc VARCHAR(1000) NULL);

Tabula sastāv no piecām kolonnām. Pēc nosaukuma tiek norādīts datu tips, kolonnas tiek atdalītas ar komatiem. Kolonnas vērtība var pieņemt tukšas vērtības (NULL) vai ir jāaizpilda (NOT NULL), un tas tiek noteikts, izveidojot tabulu.

Datu izgūšana no tabulas

Datu ieneses operators ir visbiežāk izmantotais SQL vaicājums. Lai iegūtu informāciju, mums jānorāda, ko mēs vēlamies atlasīt no šādas tabulas. Vispirms vienkāršs piemērs:

SELECT commodity_name FROM Commodity

Pēc SELECT priekšraksta mēs norādām kolonnas nosaukumu, lai iegūtu informāciju, un FROM definē tabulu.

Vaicājuma rezultāts būs visas tabulas rindas ar Commodity_name vērtībām tādā secībā, kādā tās tika ievadītas datu bāzē, t.i. bez jebkādas šķirošanas. Rezultāta pasūtīšanai tiek izmantots papildu punkts ORDER BY.

Lai meklētu vairākus laukus, uzskaitiet tos, atdalot tos ar komatiem, kā parādīts šajā piemērā:

ATLASĪT preces_id, preces_nosaukums, preces_cena NO Preces

Kā vaicājuma rezultātu ir iespējams iegūt visu rindas kolonnu vērtību. Lai to izdarītu, izmantojiet zīmi “*”:

SELECT * FROM Prece

• Papildus SELECT atbalsta:
• Datu kārtošana (ORDER BY operators)
• Izvēle atbilstoši nosacījumiem (KUR)
• Grupēšanas termins (GROUP BY)

Pievienojiet rindiņu

Lai tabulai pievienotu rindu, tiek izmantoti SQL vaicājumi ar INSERT operatoru. Papildināšanu var veikt trīs veidos:

• pievienot jaunu veselu rindu;
• stīgas daļa;
• vaicājuma rezultāti.

Lai pievienotu pilnu rindu, jānorāda tabulas nosaukums un jaunās rindas kolonnu (lauku) vērtības. Šeit ir piemērs:

INSERT INTO Preču VĒRTĪBAS ("106", "50", "Coca-Cola", "1.68", "Bez alkohola ,)

Piemērā tabulai tiek pievienots jauns produkts. Katras kolonnas vērtības ir norādītas aiz VALUES. Ja kolonnai nav atbilstošas ​​vērtības, tad jānorāda NULL. Kolonnas tiek aizpildītas ar vērtībām tādā secībā, kas norādīta tabulas izveides laikā.

Ja pievienojat tikai daļu no rindas, jums ir skaidri jānorāda kolonnu nosaukumi, kā parādīts piemērā:

INSERT INTO Prece (preču_id, piegādātāja_id, preces_nosaukums)

VĒRTĪBAS("106", "50", "Coca-Cola")

Mēs ievadījām tikai preces, piegādātāja un tā nosaukuma identifikatorus, bet pārējos laukus atstājām tukšus.

Vaicājuma rezultātu pievienošana

INSERT galvenokārt izmanto rindu pievienošanai, taču to var izmantot arī SELECT priekšraksta rezultātu pievienošanai.

Datu maiņa

Lai mainītu informāciju datu bāzes tabulas laukos, jāizmanto priekšraksts UPDATE. Operatoru var izmantot divos veidos:

• Visas tabulas rindas ir atjauninātas.
• Tikai konkrētai līnijai.

UPDATE sastāv no trim galvenajiem elementiem:

• tabula, kurā jāveic izmaiņas;
• lauku nosaukumi un to jaunās vērtības;
• nosacījumi maināmo rindu atlasei.

Apskatīsim piemēru. Pieņemsim, ka produktam ar ID=106 cena ir mainījusies, tāpēc šī rinda ir jāatjaunina. Mēs rakstām šādu operatoru:

ATJAUNINĀT Preču SET commodity_price = "3,2" WHERE commodity_id = "106"

Mēs norādījām tabulas nosaukumu, mūsu gadījumā Commodity, kur tiks veikta atjaunināšana, pēc tam pēc SET - kolonnas jauno vērtību un atradām vēlamo ierakstu, norādot nepieciešamo ID vērtību WHERE.

Lai mainītu vairākas kolonnas, SET priekšrakstam seko vairāki kolonnu vērtību pāri, kas atdalīti ar komatiem. Apskatīsim piemēru, kurā tiek atjaunināts preces nosaukums un cena:

UPDATE Commodity SET commodity_name=’Fanta’, commodity_price = "3.2" WHERE commodity_id = "106"

Lai noņemtu informāciju kolonnā, varat tai piešķirt vērtību NULL, ja tabulas struktūra to atļauj. Jāatceras, ka NULL ir tieši “nē” vērtība, nevis nulle teksta vai skaitļa veidā. Noņemsim produkta aprakstu:

ATJAUNINĀT Preču SET commodity_desc = NULL WHERE commodity_id = "106"

Notiek rindu noņemšana

SQL vaicājumi rindu dzēšanai tabulā tiek izpildīti, izmantojot DELETE priekšrakstu. Ir divi lietošanas gadījumi:

• Atsevišķas rindas tabulā tiek dzēstas;
• Visas tabulas rindas tiek dzēstas.

Piemērs vienas rindas dzēšanai no tabulas:

DELETE FROM Commodity WHERE preces_id = "106"

Pēc DELETE FROM norādām tās tabulas nosaukumu, kurā tiks dzēstas rindas. Klauzula WHERE satur nosacījumu, saskaņā ar kuru rindas tiks atlasītas dzēšanai. Piemērā mēs izdzēšam produktu līniju ar ID=106. Norādīt KUR ir ļoti svarīgi, jo izlaižot šo paziņojumu, tiks dzēstas visas tabulas rindas. Tas attiecas arī uz lauku vērtības maiņu.

DELETE priekšraksts nenorāda kolonnu nosaukumus vai metarakstzīmes. Tas pilnībā izdzēš rindas, bet nevar izdzēst vienu kolonnu.

SQL izmantošana programmā Microsoft Access

Parasti to izmanto interaktīvi, lai izveidotu tabulas, datu bāzes, pārvaldītu, modificētu, analizētu datus datu bāzē un ieviestu SQL Access vaicājumus, izmantojot ērtu interaktīvu vaicājumu noformētāju (Query Designer), ar kuru jūs varat izveidot un nekavējoties izpildīt jebkuras sarežģītības SQL paziņojumus.

Tiek atbalstīts arī servera piekļuves režīms, kurā Access DBMS var izmantot kā SQL vaicājumu ģeneratoru jebkuram ODBC datu avotam. Šī funkcija ļauj Access lietojumprogrammām mijiedarboties ar jebkuru formātu.

SQL paplašinājumi

Tā kā SQL vaicājumiem nav visas procesuālās programmēšanas valodu iespējas, piemēram, cilpas, atzarošana utt., DBVS ražotāji izstrādā savu SQL versiju ar uzlabotām iespējām. Pirmkārt, tas ir atbalsts saglabātajām procedūrām un procesuālo valodu standarta operatoriem.

Visizplatītākie valodas dialekti:

• Oracle datu bāze — PL/SQL
• Interbase, Firebird — PSQL
• Microsoft SQL Server — Transact-SQL
• PostgreSQL — PL/pgSQL.

SQL internetā

MySQL DBVS tiek izplatīta saskaņā ar bezmaksas GNU vispārējo publisko licenci. Ir komerciāla licence ar iespēju izstrādāt pielāgotus moduļus. Kā komponents tas ir iekļauts populārākajos interneta serveru komplektos, piemēram, XAMPP, WAMP un LAMP, un ir vispopulārākā DBVS lietojumprogrammu izstrādei internetā.

To izstrādāja Sun Microsystems, un pašlaik to atbalsta Oracle Corporation. Tiek atbalstītas datu bāzes līdz 64 terabaitiem, SQL:2003 sintakses standarts, datu bāzu replikācija un mākoņpakalpojumi.

Tabulas izteiksmes tiek saukti par apakšvaicājumiem, kas tiek izmantoti, ja ir sagaidāma tabulas klātbūtne. Ir divu veidu tabulas izteiksmes:

atvasinātas tabulas;

vispārinātas tabulas izteiksmes.

Šīs divas tabulas izteiksmju formas ir aplūkotas turpmākajās apakšnodaļās.

Atvasinātās tabulas

Atvasināta tabula ir tabulas izteiksme, kas iekļauta vaicājuma noteikumā FROM. Atvasinātās tabulas var izmantot gadījumos, kad kolonnu aizstājvārdu izmantošana nav iespējama, jo SQL tulkotājs apstrādā citu paziņojumu, pirms ir zināms aizstājvārds. Tālāk esošajā piemērā ir parādīts mēģinājums izmantot kolonnas aizstājvārdu situācijā, kad tiek apstrādāta cita klauzula, pirms ir zināms aizstājvārds:

IZMANTOT SampleDb; SELECT MONTH(EnterDate) kā ievades_mēnesis FROM Works_on GROUP BY ievades_mēnesis;

Mēģinot izpildīt šo vaicājumu, tiks parādīts šāds kļūdas ziņojums:

207. ziņojums, 16. līmenis, 1. stāvoklis, 5. rindiņa. Nederīgs kolonnas nosaukums “enter_month”. (207. ziņojums: 16. līmenis, 1. stāvoklis, 5. rindiņa Nederīgs kolonnas nosaukums ievades_mēnesis)

Kļūdas iemesls ir tāds, ka klauzula GROUP BY tiek apstrādāta, pirms tiek apstrādāts atbilstošs priekšraksta SELECT saraksts, un, apstrādājot grupu, kolonnas enter_month aizstājvārds nav zināms.

Šo problēmu var atrisināt, izmantojot atvasinātu tabulu, kurā ir iepriekšējais vaicājums (bez GROUP BY klauzulas), jo klauzula FROM tiek izpildīta pirms klauzulas GROUP BY:

IZMANTOT SampleDb; SELECT enter_month FROM (SELECT MONTH(EnterDate) kā ievades_mēnesis FROM Works_on) AS m GROUP BY ievades_mēnesis;

Šī vaicājuma rezultāts būs šāds:

Parasti tabulas izteiksmi var novietot jebkurā vietā SELECT priekšrakstā, kur var parādīties tabulas nosaukums. (Tabulas izteiksmes rezultāts vienmēr ir tabula vai īpašos gadījumos izteiksme.) Tālāk sniegtajā piemērā parādīta tabulas izteiksmes izmantošana SELECT priekšraksta atlases sarakstā:

Šī vaicājuma rezultāts:

Vispārējas tabulas izteiksmes

Kopējā tabulas izteiksme (OTB) ir nosaukta tabulas izteiksme, ko atbalsta Transact-SQL valoda. Kopējās tabulu izteiksmes tiek izmantotas šādos divu veidu vaicājumos:

nerekursīvs;

rekursīvs.

Šie divi pieprasījumu veidi ir apskatīti nākamajās sadaļās.

OTB un nerekursīvie vaicājumi

Nerekursīvo OTB formu var izmantot kā alternatīvu atvasinātajām tabulām un skatiem. Parasti OTB nosaka AR klauzulām un papildu vaicājums, kas atsaucas uz nosaukumu, kas izmantots klauzulā WITH. Transact-SQL atslēgvārda WITH nozīme ir neskaidra. Lai izvairītos no neskaidrībām, priekšraksts pirms WITH ir jābeidz ar semikolu.

IZMANTOT AdventureWorks2012; SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") UN Freight > (SELECT AVG(TotalDue) FROM Sales.OrderHate0) ")/2,5;

Vaicājums šajā piemērā atlasa pasūtījumus, kuru kopējie nodokļi (TotalDue) ir lielāki par visu nodokļu vidējo vērtību un kuru pārvadāšanas izmaksas (Freight) ir lielākas par 40% no vidējiem nodokļiem. Šī vaicājuma galvenā īpašība ir tā garums, jo apakšvaicājums ir jāraksta divas reizes. Viens no iespējamiem veidiem, kā samazināt vaicājuma konstrukcijas lielumu, ir izveidot skatu, kas satur apakšvaicājumu. Bet šis risinājums ir nedaudz sarežģīts, jo tas prasa izveidot skatu un pēc tam dzēst to pēc vaicājuma izpildes. Labāka pieeja būtu izveidot OTB. Tālāk esošajā piemērā ir parādīta nerekursīva OTB izmantošana, kas saīsina iepriekš minēto vaicājuma definīciju:

IZMANTOT AdventureWorks2012; WITH cena_aprēķins(gads_2005) AS (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT year_ price_cal20c) FROM_cence_cal20c. ) /2,5;

WITH klauzulas sintakse nerekursīvos vaicājumos ir šāda:

Parametrs cte_name apzīmē OTB nosaukumu, kas definē iegūto tabulu, un parametrs column_list apzīmē tabulas izteiksmes kolonnu sarakstu. (Iepriekš esošajā piemērā OTB sauc par price_calc, un tai ir viena kolonna, gads_2005.) Parametrs inner_query ir priekšraksts SELECT, kas norāda atbilstošās tabulas izteiksmes rezultātu kopu. Pēc tam definēto tabulas izteiksmi var izmantot vaicājumā outer_query. (Ārējais vaicājums iepriekš minētajā piemērā izmanto OTB price_calc un tās sleju year_2005, lai vienkāršotu divkārši ligzdoto vaicājumu.)

OTB un rekursīvie vaicājumi

Šajā sadaļā ir parādīts paaugstinātas sarežģītības materiāls. Tāpēc, lasot to pirmo reizi, ieteicams to izlaist un atgriezties vēlāk. OTB var izmantot, lai ieviestu rekursijas, jo OTB var saturēt atsauces uz sevi. Pamata OTB sintakse rekursīvajam vaicājumam izskatās šādi:

Parametriem cte_name un column_list ir tāda pati nozīme kā OTB nerekursīviem vaicājumiem. WITH klauzulas pamatteksts sastāv no diviem vaicājumiem, ko savieno operators SAVIENĪBA VISU. Pirmais vaicājums tiek izsaukts tikai vienu reizi, un tas sāk uzkrāt rekursijas rezultātu. Operatora UNION ALL pirmais operands neatsaucas uz OTB. Šo vaicājumu sauc par atsauces vaicājumu vai avotu.

Otrais vaicājums satur atsauci uz OTB un attēlo tā rekursīvo daļu. Šī iemesla dēļ to sauc par rekursīvo dalībnieku. Pirmajā rekursīvās daļas izsaukumā OTB atsauce atspoguļo atsauces vaicājuma rezultātu. Rekursīvais dalībnieks izmanto pirmā vaicājuma izsaukuma rezultātu. Pēc tam sistēma vēlreiz izsauc rekursīvo daļu. Zvans rekursīvajam dalībniekam tiek pārtraukts, kad iepriekšējais zvans tam atgriež tukšu rezultātu kopu.

Operators UNION ALL pievienojas pašlaik uzkrātajām rindām, kā arī papildu rindām, kuras rekursīvajam dalībniekam pievieno pašreizējais izsaukums. (Operatora UNION ALL klātbūtne nozīmē, ka no rezultāta netiks noņemtas dublētās rindas.)

Visbeidzot, parametrs outer_query norāda ārējo vaicājumu, ko OTB izmanto, lai izgūtu visus abu dalībnieku savienojuma zvanus.

Lai parādītu OTB rekursīvo formu, mēs izmantojam tabulu Lidmašīna, kas definēta un aizpildīta ar kodu, kas parādīts tālāk esošajā piemērā:

IZMANTOT SampleDb; CREATE TABLE Lidmašīna(ContainingAssembly VARCHAR(10), ContainedAssembly VARCHAR(10), QuantityContained INT, UnitCost DECIMAL(6,2)); INSERT INTO Airplane VALUES ("Lidmašīna", "Fizelāža", 1, 10); INSERT INTO Airplane VALUES ("Lidmašīna", "Spārni", 1, 11); INSERT INTO Airplane VALUES ("Lidmašīna", "Aste", 1, 12); INSERT INTO Airplane VALUES ("Fizelāža", "Salons", 1, 13); INSERT INTO Airplane VALUES ("Fizelāža", "Kabīne", 1, 14); INSERT INTO Airplane VALUES ("Fizelāža", "Deguns",1, 15); INSERT INTO Airplane VALUES ("Cabin", NULL, 1,13); INSERT INTO Airplane VALUES ("Cockpit", NULL, 1, 14); INSERT INTO Airplane VALUES ("Deguns", NULL, 1, 15); INSERT INTO Airplane VALUES ("Wings", NULL,2, 11); INSERT INTO Airplane VALUES ("Tail", NULL, 1, 12);

Tabulā Lidmašīna ir četras kolonnas. Kolonnā ContainingAssembly ir norādīts mezgls, un kolonnā ContainedAssembly ir norādītas detaļas (pa vienai), kas veido atbilstošo mezglu. Zemāk esošajā attēlā parādīts iespējamā gaisa kuģa tipa un tā sastāvdaļu grafisks attēls:

Lidmašīnu tabula sastāv no šādām 11 rindām:

Nākamajā piemērā tiek izmantota klauzula WITH, lai definētu vaicājumu, kas aprēķina katras būves kopējās izmaksas:

IZMANTOT SampleDb; WITH_detaļu_saraksts(montāža1, daudzums, izmaksas) AS (SELECT ContainingAssembly, QuantityContained, UnitCost FROM Airplane WHERE ContainedAssembly IS NULL UNION ALL SELECT a.ContainingAssembly, a.Quantity Contained, CAST(l.quantity) *DECl. ) NO detaļu_saraksta l, lidmašīna a WHERE l.assembly1 = a.ContainedAssembly) SELECT montāža1 "Daļa", daudzums "Daudzums", izmaksas "Cena" NO detaļu_saraksta;

WITH klauzula definē OTB sarakstu ar nosaukumu list_of_parts, kas sastāv no trim kolonnām: montāža1, daudzums un izmaksas. Pirmais SELECT priekšraksts piemērā tiek izsaukts tikai vienu reizi, lai saglabātu rekursijas procesa pirmā posma rezultātus. Priekšraksts SELECT piemēra pēdējā rindā parāda šādu rezultātu.