SQL - Structured Query Language - I

Šeit izklāstīti SQL valodas pamati, tās komandas, sintakse un lietojumi. SQL komandas var rakstīt gan lieliem gan maziem burtiem, bet datubāzu tabulās ievietojamie dati, protams, var būt reģistrjūtīgi. Lasāmības uzlabošanai SQL komandas rakstīsim lielajiem burtiem, bet datubāzei specifiskos identifikatorus (tabulu, lauku vārdus) - ar mazajiem burtiem. SQL komandas var sastāvēt no viena vai vairākiem vārdiem, reizēm iespējami vairāki komandas pieraksta varianti. Šajā rakstā minētie SQL komandu piemēri pielāgoti MySQL datubāzes serveriem. Webkursu veidotāji izsaka pateicību Gunāram Rodinam, kurš palīdzējis šī materiāla tapšanā.

Šajā dokumentā aplūkosim sekojošas komandas:

Komanda CREATE TABLE

Šī komanda veido tabulas. Lai veidotu tabulas, vispirms ir jāpārdomā datubāzes struktūra, t.i. kādu informāciju kādās tabulās glabāt. Ir īpaša teorija - datu bāzes normalizēšana, kura māca kā izveidot pareizu un viegli uzturamu datubāzi; šo teoriju te sīkāk neaplūkosim. Viss kas rakstīts kursīvā - ir aizvietojami jēdzieni. Komandas sintakse ir šāda:

CREATE TABLE tabulas_vārds
 (kolonas_vārds tips [modificētāji]
 [,kolonas_vārds tips [modificētāji]])

Ņemiet vērā, ka tabulu un kolonnu nosaukumus drīkst veidot tikai ar parastajiem angļu burtiem un cipariem un pasvītrojuma zīmi ("_"), nedrīkst lietot latviešu vai krievu burtus, kā arī tie nedrīkst sākties ar cipariem. SQL nav reģistrjūtīgs, t.i. ir vienalga vai šie vārdi rakstīti ar lieliem vai maziem burtiem.
Teorētiski tabulas lauku skaits nav ierobežots, bet prakse liecina ka vislabāk, ja lauku skaits tabulā nepārsniedz 8-20 kolonas. Gan viegli administrēt, gan cietā diska vieta ietaupās. Tabulas vārds var būt gan īss un vienkāršs, gan komplekss. Piemēram lietotajs un mani_2004g_darba_kontakti.

Kolonnu tipus var izpētīt MySQL dokumentācijā ( Column types) bet SQL standartā tie ir:
INT, SMALINT, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET and ENUM

Kā vienkāršu piemēru, izveidosim tabulu par cilvēkiem.

Tabula: cilveks
id_cilveksINT
vardsCHAR(25)
uzvardsCHAR(25)
id_adreseINT

Tagad uzrakstīsim piemēru, kā šādu tabulu uztaisīt ar SQL komandu:

CREATE TABLE cilveks 
    (id_cilveks INT, 
    vards CHAR(25), 
    uzvards CHAR(25), 
    id_adrese INT)

Pilns darbību saraksts, lai izveidotu šādu tabulu tukšā vietā ir - pieslēgties mysql klientam, izveidot jaunu datubāzi mana_datubaaze, pāriet uz šo datubāzi ar komandu USE, izveidot tabulu, un visbeidzot paskatīties uz izveidotās tabulas struktūru ar komandu DESCRIBE. Šīs darbības izklāstītas zemāk (treknā fontā drukāts tas, kas jāraksta lietotājam):

c:\mysql\bin>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.21-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE manadatubaaze;
Query OK, 1 row affected (0.03 sec)

mysql> USE manadatubaaze;
Database changed
mysql> CREATE TABLE cilveks (
    -> id_cilveks INT,
    -> vards CHAR(25),
    -> uzvards CHAR(25),
    -> id_adrese INT);
Query OK, 0 rows affected (0.08 sec)

mysql> DESCRIBE cilveks;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| id_cilveks | int(11)  | YES  |     | NULL    |       |
| vards      | char(25) | YES  |     | NULL    |       |
| uzvards    | char(25) | YES  |     | NULL    |       |
| id_adrese  | int(11)  | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
4 rows in set (0.03 sec)

mysql>exit

Visas datubāzē esošās tabulas var aplūkot ar MySQL komandrindas utilītprogrammu mysqlshow (parasti tā instalēta c:\mysql\bin). Piemēram:

c:\mysql\bin>mysqlshow manadatubaaze -u root -p
Enter password: ********
Database: manadatubaaze
+---------+
| Tables  |
+---------+
| cilveks |
+---------+

Atsauces

  1. Reālās datubāzēs izmanto daudz kompleksākas tabulas, ar jau iebūvētām kontrolēm un indeksējumiem, sk. MySQL dokumentāciju (piemēram, Multiple column indexes).
  2. CREATE TABLE komandai ir arī citas iespējas un parametri, sk. MySQL dokumentāciju: CREATE TABLE sintakse.
  3. Datubāzu, tabulu un lauku vārdiem jābūt MySQL identifikatoriem - sk. MySQL dokumentāciju sk. Legal names.

Komanda INSERT INTO

Šī komanda aizpilda tabulas. Tas ir, mēs jaunus datus tieši ar šo komandu pievienojam kā jaunas rindas tabulām. Lai mainītu ievadītos datus lieto UPDATE komandu. Ņemiet vērā, ka ir trīs sintakses kā šo komandu lieto:

Pirmais variants (klasiskais) - INSERT INTO ... VALUES ...

INSERT INTO tbl_name [(col_name,...)]
    VALUES (expression,...),(...),...

Otrais variants - INSERT ar SELECT

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...

Trešais variants - INSERT "update stilā"

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
    [INTO] tbl_name
    SET col_name=expression, col_name=expression, ...

Protams, visi šie varianti strādā uz MySQL serveriem; ir iespējams ka daži no tiem strādās arī citur. Tikai pirmais variants ir SQL standarts un tam jāstrādā visos SQL serveros. Tagad nodemonstrēsim kādu piemēru klasiskajā variantā. Teiksim, ievadīsim mūsu cilveks tabulā ierakstus, kuri tika izmantoti CREATE INDEX piemērā.

INSERT INTO CILVEKS (id_cilveks,vards,uzvards,id_adrese) 
    VALUES (1, "Antero", "Vipunens", -1);

Nākamais piemērs dara to pašu ko iepriekšējais, bet tas ir slikts stils. Tas balstās uz secību, kurā definēti tabulas lauki, kā arī neaizpildām paši visus tabulas laukus (paļaujamies, ka lauks ID_ADRESE pēc noklusējuma būs -1). Tikko mums izmanīsies lauku secība, vai kāds lauks tabulai tiks pievienots vai noņemts, šis pieprasījums nestrādās. Kā redzams piemērā, lietotājs var šādos gadījumos viegli sajaukt lauku secību, piemēram, aizmirst, kurš lauks tabulā bija pirmais - vārds vai uzvārds.

INSERT CILVEKS VALUES(1,"Vipunens","Antero");

Atsauces

  1. INSERT INTO citas iespējas un parametrus sk. MySQL dokumentācijā - INSERT sintakse.

Komanda SELECT

SELECT komanda ļauj mums dabūt iegūt no datubāzes kādus datus. Tā ir komplicēta komanda. Ar to var ne tikai atlasīt datus, bet arī iegūt statistiskus pārskatus, aprēķinu un formulu rezultātus, izsaukt citas funkcijas un uzzināt sistēmas mainīgo vērtības, izpildīt tekstu operācijas, un daudz ko citu, kas atgriež lietotājam vēlamo vienas vai vairāku tabulas datu kombināciju vai šķērsgriezumu.

SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
       select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
        [WHERE where_definition]
        [GROUP BY col_name,...]
        [HAVING where_definition]
        [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
        [LIMIT [offset,] rows]
        [PROCEDURE procedure_name] ]

Par pamatu atkal ņemsim tabulas cilveks un adrese, kas ir CREATE INDEX aprakstā.

SELECT c.id_cilveks AS npk,SUBSTRING(c.Vards,1)+"."+c.uzvards AS persona,a.valsts,a.pilseta,a.iela+" "+a.nr_dz as maja
FROM cilveks c, adrese AS a
WHERE c.id_adrese=a.id_adrese
      AND (UPPER(c.uzvards) like "OZOL%" OR c.uzvards like "PRIED%")
      AND (LOWER(a.valsts) = "latvija")
GROUP BY  a.valsts,a.pilseta
ORDER BY c.uzvards,c.vards DESC
LIMIT 10

Šis piemērs pēc būtības dara sekojošo: Atrod visus cilvēkus, kas dzīvo Latvijā un kuriem uzvārds sākās ar "OZOL" vai "PRIED", sagrupē tos pa piederībām valstij, tad pilsētai, un pēc tam sakārto uzvārda augošā secībā, un vārdu dilstošā secībā. Rezultātā vienā laukā ar nosaukumu persona parāda vārda pirmo burtu, aiz tā punktu un tad pilnu uzvārdu. Rezultāts šādai meklēšanai varētu būt tāds:

npkPersonaValstspilsetamaja
423548K.OzoliņšLatvijaCēsisĻeņina 5-3
32543258Z.PriedeLatvijaCēsisKurbada 32-9
0x54FFCG.PriedeslaipaLatvijaCēsisHakeru 0xE3-0x16
23248K.OzoliņšLatvijaRīgaKino 1-1
852235A.OzoliņšLatvijaRīgaDzelzs 82
34589835M.PriedeLatvijaRīgaKandavas 22

Atsauces

  1. SELECT citas iespējas un parametri ir MySQL dokumentācijā Select syntax.

Komanda DELETE

DELETE komanda ir paredzēta esošo datu dzēšanai. Komanda ir viena no vienkāršākajām un BĪSTAMAJĀKĀM SQL valodā, jo šī komanda parasti atļauta visiem lietotājiem. Tās konstrukcija ir:

DELETE [LOW_PRIORITY] FROM tbl_name
    [WHERE where_definition] [LIMIT rows]

Droši pamanījāt, ka WHERE daļa nav obligāta, tas nozīmē, ka pietiek jums aizmirst nodefinēt robežas, lai visi dati tiktu izdzēsti:

DELETE FROM cilveks

Tāpēc ļoti jāuzmanās ar šo komandu. Noteikti definējiet nepārprotamas komandas darbības robežas - citādi var tik pazaudēti svarīgi dati dēļ nepietiekamiem ierobežojumiem. Jums var rasties problēmas izdzēst ierakstu no kādas tabulas, ja uz šo ierakstu ir atsauce citā tabulā vai ierakstā, jums SQL serveris neļaus to izdarīt (MySQL gan neparedz ārējās atslēgas, tāpēc bez brīdinājuma var izdzēst arī to, uz ko rindas citās tabulās atsaucas). Mūsu piemērā par cilvēku un adresi, nevarēs izdzēst adresi, kurai adrese_id=1, ja tabulā cilveks ir ar šo adreses vērtību. T.i. nevajag dzēst adreses, ja ir cilvēki, kuriem šīs adreses ir vajadzīgas.

Labākais, ko šādā situācijā var darīt ir vispirms padarīt visus cilvēkus ar doto adresi par tādiem, kuriem adreses nav, un tikai tad dzēst adresi:

UPDATE cilveks SET id_adrese=-1 WHERE id_adrese=1;
DELETE adrese WHERE id_adrese=1;

Atsauces

  1. DELETE citas iespējas un parametri ir MySQL dokumentācijā: DELETE syntax.

Lapa mainīta 2004-11-15 22:35:00