6. MySQL-tietokannan käyttö ja SQL-kielen perusteet

6.1. Mitä on SQL?

Structured Query Language eli SQL on standardoitu kieli, jota käytetään relaatiotietokannoissa. Sen avulla voidaan määritellä tietokannan rakenne, määritellä käyttäjäoikeuksia, lisätä, poistaa tai muuttaa tietoja sekä tehdä erilaisia tiedonhakukyselyjä. SQL ei ole ohjelmointikieli, vaan pikemminkin määrittelykieli.

Perinteisesti SQL on totuttu jakamaan kahteen osaan: tietokannan rakenteen määrittelyyn (Data Definition Language DDL) ja tietokannan sisällön käsittelyyn (Data Manipulation Language DML).

SQL-kielen kehitys on kulkenut samaa matkaa relaatiotietokantojen kehityksen kanssa. Ensimmäinen versio syntyi vuonna 1964 (IBM:n tutkijoiden kehittämä SEQUEL). Ensimmäinen virallinen standardi tuli vuonna 1986. Nykyisin on käytössä kahta eri standardia noudattava SQL: SQL92 ja SQL99.

On huomattava, että standardoinnista huolimatta eri tiedonhallintajärjestelmien käyttämissä SQL-kielissä on eroja. Tästä eteenpäin käytetään MySQL:n tuntemaa versiota. SQL-kielen syntaksit on otettu MySQL:n dokumentaatiosta, josta voit katsoa kunkin komennon yksityiskohdat.

6.2. Tietokannan rakenteen määrittely

Oppimistehtävien 2-4 perusteella olet suunnitellut tietokannan Limppulan Nisu- ja Sokurileipomolle. Muistin virkistykseksi, tässä toimeksianto:

Limppulan kunnassa toimiva leipomo-konditoria Limppulan Nisu- ja Sokurileipomo haluaisi käyttöönsä tietokannan, jonka avulla leipomossa voitaisiin ylläpitää tietoja asiakkaista, tuotteista ja asiakkaiden tekemistä tilauksista. Näistä asioista halutaan myös asiaankuuluvat tulosteet. Asiakas voi olla joko yksityishenkilö tai jokin organisaatio. Organisaatioasiakas voi olla jälleenmyyjä tai muu asiakas. Tuotteilla on eri hinnat tavallisille asiakkaille ja jälleenmyyjille. Organisaatioasiakkaille myydään laskua vastaan, muille käteisellä.

Toimeksiannon perusteella on olemassa ainakin taulut Asiakas, Tuote ja Tilaus.

6.2.1. Tietokannan luominen ja asettaminen oletukseksi

MySQL-palvelimella voi olla useita tietokantoja, joista yksi on niin sanottu oletustietokanta. Kaikki komennot kohdistuvat oletustietokantaan, muihin tietokantoihin voi tarvittaessa viitata SQL-kielen pistenotaatiolla.

Luodaan seuraavaksi Yritys-tietokanta:

create database yritys;

Huomaa, että SQL-komennot loppuvat puolipisteeseen. Komennot voi kirjoittaa joko isoilla tai pienillä kirjaimilla. Komentorivillä tämä näkyy näin:

Asetaan luotu tietokanta oletustietokannaksi

use yritys;

6.2.2. Taulujen luominen tietokantaan

Uusi taulu luodaan create table - komennolla:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

or

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name LIKE old_table_name;

create_definition:
  col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [PRIMARY KEY] [reference_definition]
  or    PRIMARY KEY (index_col_name,...)
  or    KEY [index_name] (index_col_name,...)
  or    INDEX [index_name] (index_col_name,...)
  or    UNIQUE [INDEX] [index_name] (index_col_name,...)
  or    FULLTEXT [INDEX] [index_name] (index_col_name,...)
  or    [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
            [reference_definition]
  or    CHECK (expr)

type:
        TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  or    SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  or    MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  or    INT[(length)] [UNSIGNED] [ZEROFILL]
  or    INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  or    BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  or    REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  or    NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  or    CHAR(length) [BINARY]
  or    VARCHAR(length) [BINARY]
  or    DATE
  or    TIME
  or    TIMESTAMP
  or    DATETIME
  or    TINYBLOB
  or    BLOB
  or    MEDIUMBLOB
  or    LONGBLOB
  or    TINYTEXT
  or    TEXT
  or    MEDIUMTEXT
  or    LONGTEXT
  or    ENUM(value1,value2,value3,...)
  or    SET(value1,value2,value3,...)

index_col_name:
        col_name [(length)]

reference_definition:
        REFERENCES tbl_name [(index_col_name,...)]
                   [MATCH FULL | MATCH PARTIAL]
                   [ON DELETE reference_option]
                   [ON UPDATE reference_option]

reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
	TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
or	AUTO_INCREMENT = #
or	AVG_ROW_LENGTH = #
or	CHECKSUM = {0 | 1}
or	COMMENT = "string"
or	MAX_ROWS = #
or	MIN_ROWS = #
or	PACK_KEYS = {0 | 1 | DEFAULT}
or	PASSWORD = "string"
or	DELAY_KEY_WRITE = {0 | 1}
or      ROW_FORMAT= { default | dynamic | fixed | compressed }
or	RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#
or	UNION = (table_name,[table_name...])
or	INSERT_METHOD= {NO | FIRST | LAST }
or      DATA DIRECTORY="absolute path to directory"
or      INDEX DIRECTORY="absolute path to directory"

select_statement:
	[IGNORE | REPLACE] SELECT ...  (Some legal select statement)

6.2.3. Taulutyypit ja tietotyypit

MySQL:ssa voidaan käyttää usean eri tyyppisiä tauluja. Taulutyypin valintaan vaikuttavat monet seikat ja valittu taulutyyppi taas vaikuttaa esimerkiksi tietokannan suorituskykyyn. Taulukossa on esitetty yleisimmät taulutyypit ja niident ominaisuuksia.

On suositeltavaa käyttää INNODB- taulutyyppiä, koska siinä on muun muassa tehokkaammin hoidettu yksittäisiin riveihin kohdistuvan kyselyn käsittely, rivitason lukitus ja viiteavainten käyttömahdollisuus.

MySQL-tietokannan tietotyypit jaetaan numeerisiin, merkkitietoon ja muihin tyyppeihin.

Lisää asiaa tietotyypeistä löydät MySQL:n dokumentaatiosta.

Oppimistehtävä 5. Luo Yritys-tietokannan Asiakas-, Tuote- ja Tilaus-taulut tekemäsi suunnitelman perusteella.

6.2.4. Indeksien luonti tauluihin

Indeksit ovat tietokannan sisälle rakennettava mekanismi, jonka avulla taulujen sisältämää tietoa on helpompi käsitellä Indeksien käyttö nopeuttaa tiedonhakua. Toisaalta liian runsas indeksin käyttö saattaa hieman hidastaa tietojen ylläpitoa ja kasvattaa kannan kokoa. Indeksi luodaan useimmiten siihen taulun kenttään, jota käytetään hakuavaimena tiedonhaussa, siis pääavainkenttään.

CREATE [UNIQUE|FULLTEXT] INDEX index_name
       ON tbl_name (col_name[(length)],... )

6.2.5. Taulun rakenteen muuttaminen

Taulujen rakenteeseen joutuu tekemään joskus muutoksia. Ne onnistuvat alter table - komennolla

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  or    ADD [COLUMN] (create_definition, create_definition,...)
  or    ADD INDEX [index_name] (index_col_name,...)
  or    ADD PRIMARY KEY (index_col_name,...)
  or    ADD UNIQUE [index_name] (index_col_name,...)
  or    ADD FULLTEXT [index_name] (index_col_name,...)
  or	ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
            [reference_definition]
  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition
               [FIRST | AFTER column_name]
  or    MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX index_name
  or    DISABLE KEYS
  or    ENABLE KEYS
  or    RENAME [TO] new_tbl_name
  or    ORDER BY col
  or    table_options

Oppimistehtävä 6. Indeksoi luomasi taulut

Tehtävien 5 ja 6 jälkeen lähetä tietokantasi opettajalle tarkistettavaksi (tiedostot sijaitsevat MySQL:n asennuskansion Data-kansiossa alikansiossa Yritys)

6.3. Tietokannan käyttöoikeuksien  määrittely

MySQL-tietokantaan täytyy lisätä tiedot tietokannan käyttäjistä ja heillä olevista käyttöoikeuksista. Käyttäjällä tarkoitetaan käyttäjätunnuksen ja asiakaskoneen yhdistelmää, esimerkiksi paikallisesti asennetussa MySQL- tietokannassa pääkäyttäjä on root@localhost. Sekä käyttäjätunnus että asiakaskoneen nimi voidaan korvata jokerimerkillä %, jolloin määrittely kohdistuu joko kaikkiin käyttäjiin tai kaikkiin asiakaskoneisiin.

Käyttöoikeudet voidaan määritellä tietokanta- ja taulukohtaisesti, siten samalla käyttäjällä voi olla erilaisia oikeuksia eri tietokantoihin ja tauluihin. Luonnollisesti pääkäyttäjällä tulee olla kaikki oikeudet. Muille annetaan yleensä oikeus lukea, lisätä ja päivittää tietoja.

Tietokantakohtaiset oikeudet koskevat tietyn tietokannan kaikkia tauluja, tiedot tallennetaan mysql.db- and mysql.host- tauluihin.

Taulutason oikeudet koskevat tietyn taulun kaikkia sarakkeita (kenttiä), tiedot tallennetaan mysql.tables_priv- tauluun.

Saraketason oikeudet koskevat tietyn taulun tiettyä saraketta (kenttää), tiedot tallennetaan mysql.columns_priv- tauluun.

Käyttöoikeuksia myönnetään grant - komennolla ja poistetaan revoke - komennolla. Huomaa, että revoke ei poista käyttäjätunnuksia, ne täytyy poistaa erikseen delete - komennolla.

Käyttöoikeudet voivat kohdistua seuraaviin komentoihin:

Esimerkkejä käyttöoikeuksien määrittelystä:

1. Kyselyoikeus Paavo - nimiselle käyttäjälle paikallisen koneen Yritys - tietokannan Tilaus - tauluun:

grant select on yritys.asiakas to paavo@localhost;

Jos Paavo olisi uusi käyttäjä, niin hänet lisättäisiin automaattisesti mysql.user - tauluun, jossa käyttäjätietoja säilytetään.

2. Etäkyselyoikeus Pomo - käyttäjälle kaikista asiakaskoneista varustettuna salasanalla:

grant select on yritys.asiakas to pomo@% identified by salasana;

3. Admin - oikeudet paikalliselta koneelta:

grant all on *.* to root@localhost;

4. Käyttöoikeuden poisto Paavo - nimiseltä käyttäjältä:

revoke select on yritys.asiakas to paavo@localhost;

Tietyn käyttäjän käyttöoikeudet nähdään komennolla:

show grants for paavo@localhost;

6.4. Tietokannan sisällön käsittely

SQL-kielen tärkein osa on tietokannan tietojen lisäämiseen, muuttamiseen, poistamiseen ja tietojen hakuun liittyvät käskyt.

6.4.1. Tietueiden lisääminen tauluun

Kun olet luonnut tarvittavat taulut ja indeksoinut ne, on aika ryhtyä lisäämään tauluihin tietueita. Sen voit tehdä joko insert into - lauseella tai suurten tietomäärien kyseessä ollessa load data infile - komennolla.

Komennon syntaksi on:

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES ((expression | DEFAULT),...),(...),...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name
        SET col_name=(expression | DEFAULT), ...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

 

Esimerkiksi Asiakas-tauluun lisätään yksi tietue seuraavasti:

insert into asiakas(asiakasnro,yritysnimi,sukunimi,etunimi,asiakastyyppi,postiosoite,postinro,postitoimipaikka,puhelin,sahkoposti) values (1, Oulun seudun ammattioppilaitos Kaukovainion yksikkö,Louhelainen,Esko,1,Kotkantie 2 C,90250,Oulu,(08) 312 6611,esko.louhelainen@osakk.fi)

Suuri määrä tietueita on kätevämpi lisätä yhdellä kertaa load data infile- komennon avulla tekstitiedostosta.

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES TERMINATED BY '\n']
    [IGNORE number LINES]
    [(col_name,...)]

6.4.2.Tietojen haku eli kyselyt

SQL-kielen käytetyin käsky on kyselyihin tarvittava select, jonka syntaksi on:

SELECT [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
      [WHERE where_definition]
      [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]
      [HAVING where_definition]
      [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
      [LIMIT [offset,] rows | rows OFFSET offset]
      [PROCEDURE procedure_name(argument_list)]
      [FOR UPDATE | LOCK IN SHARE MODE]]

Esimerkiksi, jos halutaan etsiä Asiakas-taulun kaikki tietueet, kirjoitetaan lause select * from Asiakas;

Jos halutaan etsiä vaikkapa kaikki postinumeroalueella 90120 asuvat asiakkaat, tarvitaan lause select * from Asiakas where postinumero =90120;

Voit käyttää kyselyissä vertailuoperaattoreita ja tehdä laskutoimituksia.

6.4.3. Tiedon korvaus, päivitys ja poisto

Replace- komento korvaa taulussa olevan tietueen arvot uusilla arvoilla

REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name
        SET col_name=expression, col_name=expression,...
 

Taulussa olevien tietojen päivitys tapahtuu update- komennolla, jonka syntaksi on:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT rows]

or

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

Tiedot voidaan poistaa taulusta delete- komennolla:

DELETE [LOW_PRIORITY] [QUICK] FROM table_name
       [WHERE where_definition]
       [ORDER BY ...]
       [LIMIT rows]

or

DELETE [LOW_PRIORITY] [QUICK] table_name[.*] [, table_name[.*] ...]
       FROM table-references
       [WHERE where_definition]

or

DELETE [LOW_PRIORITY] [QUICK]
       FROM table_name[.*] [, table_name[.*] ...]
       USING table-references
       [WHERE where_definition]

Oppimistehtävä 7. Tutustu MySQL-dokumentaatioon ja opiskele peruskomentoja. Tee sen jälkeen täältä löytyvät harjoitukset, jotka palautat opettajalle.