Datan määrittely, MySQL

1. Datan määrittely, MySQL • Kussakin taulussa kullekin sarakkeelle määritellään tarpeen mukainen tietotyyppi. • Seuraavalla sivulla on kuvaus kokonaisluvuista, lähteenä on käytetty http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html tMyn

2. tMyn

3. Varatun sanan INT lisäksi voidaan myös kirjoittaa INTEGER. • Jos kirjoitetaan vaikkapa INT(3), tarkoittaa se vain näyttökentän leveyttä. • Liukulukutyyppeinä ovat FLOAT (4 tavua) ja DOUBLE (8 tavua). • Liukulukutyypit FLOAT ja DOUBLE eivät edusta tarkkoja lukuarvoja (approximate numeric data values). • Ei-standardin mukaiset syntaksit ovat FLOAT(m, d), REAL(m, d) ja DOUBLE PRECISION(m, d). Sulkeissa m edustaa numeroiden lukumäärää kokonaisuudessaan ja d desimaalipisteen jälkeisten numeroiden lukumäärää, siis vaikkapa FLOAT(7, 4) voisi olla 123.4567. tMyn

4. Desimaalierottimena toimii piste. • Tarkkoja liukulukuarvoja tallennetaan tyypeillä DECIMAL tai NUMERIC (exact numeric data values). • Merkintä DECIMAL(5, 2) tarkoittaa, että 5 numeroa voi olla ennen desimaalierotinta, ja 2 numeroa sen jälkeen. • Merkintä DECIMAL(5) tarkoittaa samaa kuin DECIMAL(5, 0). tMyn

5. Päivämäärän ja ajan tallentamiseen käytettäviä tietotyyppejä: • DATE: Päivämäärä annetaan muodossa vvvv-kk-pp. • DATETIME Päivämäärä ja kellonaika annetaan muodossa vvvv-kk-pp tt:mm:ss, siis vaikkapa ’2007-12-24 22:20:55’. • TIME: Kellonaika annetaan muodossa tt:mm:ss. • TIMESTAMP: Tällä tyypillä on mahdollista saada talteen halutun hetken päivämäärä ja kellonaika. Mahdollisuuksia on useita, olkoot kyseessä luotavan taulun ensimmäinen TIMESTAMP-tyyppinen sarake: tMyn

6. CREATE TABLE kokeilu (tamaHetki TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, … • Tulkinta: Oletusarvona on luonnin yhteydessä oleva ajankohta, ja kun muutos tehdään riville, niin samalla automaattisesti päivitetään sen hetkinen ajankohta. • Sama lopputulos saadaan kun jätetään kummatkin attribuutit pois (DEFAULT ON UPDATE). tMyn

7. CREATE TABLE kokeilu (tamaHetki TIMESTAMP DEFAULT CURRENT_TIMESTAMP, … • Toiminta: Oletusarvona on luonnin yhteydessä oleva ajankohta, ja jos muutoksia tehdään riville, niin tämä ajankohta-arvo ei automaattisesti päivity. tMyn

8. CREATE TABLE kokeilu (tamaHetki TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP, … • Toiminta: Oletusarvona on ajankohdaksi 0, ja jos muutoksia tehdään riville, niin vastaava ajankohta-arvo päivittyy uudeksi arvoksi. • Jos jättää tuon jälkimmäisen attribuutin (ON UPDATE) pois, niin sitten kentän arvona pysyy 0. tMyn

9. YEAR: Vuosiluku annetaan vaikkapa muodossa ’2008’. tMyn

10. Merkkivakion tai merkkijonojen tallentamiseen käytettäviä tietotyyppejä: • CHAR(m): Kentän koko pysyy kiinteän mittaisena. Suurin arvo m=255. Esim. CHAR(30)-tyyppiseen kenttään voidaan tallentaa 30 merkkivakiota (siis olettaen toki, että käytössä on yksitavuinen merkistö, single-byte character set, vaikkapa latin1). Jos edellä olevan tyyppiseen kenttään tallennetaan vaikkapa vain yksi merkkivakio, niin muistin tarve on aina kiinteä määrä tavuja (siis edellisessä tapauksessa tuo 30 tavua). tMyn

11. VARCHAR(m): Vaihtuvan mittainen merkkijono. Suurin arvo m=65535. Muistin tarve määräytyy tallennettavan merkkijonon pituuden mukaan. tMyn

12. Taulun luontiin liittyvä syntaksi on varsin monipuolinen ja kokonaisuudessaan tuo syntaksi löytyy helpoiten netistä. Tässä käsitellään/otetaan huomioon vain muutama tavallisin piirre: tMyn

13. CREATE TABLE tbl_name (create definition, …) [table_option …] create_definition: col_name column_definition | [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,...) | [CONSTRAINT [symbol]] FOREIGN KEY (index_col_name,...) reference_definition column_definition: data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] data_type: | INT[(length)] [UNSIGNED] [ZEROFILL] | INTEGER[(length)] [UNSIGNED] [ZEROFILL] tMyn

14. | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] | DATE | TIME | TIMESTAMP | DATETIME | YEAR | CHAR(length) [CHARACTER SET charset_name] | VARCHAR(length) [CHARACTER SET charset_name] index_col_name: col_name [(length)] tMyn

15. reference_definition: REFERENCES tbl_name [(index_col_name,...)] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION table_option: [TABLESPACE tablespace_name STORAGE DISK] ENGINE [=] engine_name | [DEFAULT] CHARACTER SET charset_name tMyn

16. Luodaan kaksi taulua, ja lisätään kumpaankin muutama rivi dataa. • Luonnin yhteydessä ensimmäiseen tauluun määritellään perusavain (primary key). • Jälkimmäiseen tauluun määritellään perusavain sekä viiteavain (foreign key), joka viittaa edellisen taulun perusavaimeen. • Näillä järjestelyillä voidaan tutkia viite-eheyttä: tMyn

17. CREATE TABLE Osasto (osNro INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, osNimi VARCHAR(20) NOT NULL, osoite VARCHAR(30) NULL, CONSTRAINT osPaaAvain PRIMARY KEY(osNro)); CREATE TABLE Tyontekijat (hloNro INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, eNimi VARCHAR(15) NOT NULL, sNimi VARCHAR(20) NOT NULL, osastoNro INTEGER UNSIGNED NOT NULL, CONSTRAINT tyoPaaAvain PRIMARY KEY(hloNro), CONSTRAINT tyoViiAvain FOREIGN KEY(osastoNro) REFERENCES osasto(osNro) ON UPDATE CASCADE ON DELETE RESTRICT); tMyn

18. PRIMARY KEY yksilöi taulun kunkin rivin. • FOREIGN KEY:n avulla huolehditaan viite-eheydestä. Viite-eheydellä varmistetaan, että tieto on ensin kirjattu siihen tauluun mihin FOREIGN KEY:llä viitataan. FOREIGN KEY:llä viitataan aina jonkun toisen taulun PRIMARY KEY:hyn. • Viiteavaimen tietotyypin täytyy olla sama kuin sitä vastaavan perusavaimen tietotyyppi. • REFERENCES-määritteen avulla määritellään mihin tauluun viiteavain viittaa ja mikä kenttä siinä taulussa on PRIMARY KEY-kenttä. tMyn

19. ON UPDATE/ON DELETE-määritteillä voidaan ohjata sitä, miten toimitaan, kun viiteavaimella viitattua riviä ollaan päivittämässä (PRIMARY KEY:lle ollaan antamassa uutta arvoa vanhan tilalle) tai kyseistä riviä ollaan poistamassa. • RESTRICT ja NO ACTION tarkoittavat samaa: Jos yritetään poistaa/päivittää sellaista perusavainta, jolla on vastine viiteavaimena, ei toimenpidettä anneta suorittaa. • CASCADE tarkoittaa vyöryttämistä. Jos sellaista perusavainta päivitetään/poistetaan, jolla on vastine viiteavaimena, sama toimenpide kohdistuu myös viiteavain-kenttään. tMyn

20. SET NULL –määrite tarkoittaa, että jos perusavaimen yksi arvo poistetaan tai tätä arvoa muutetaan toiseksi arvoksi, niin vastaavan viiteavaimen arvoksi asetetaan NULL. Tämä on mahdollista vain silloin, kun taulua luotaessa viiteavainkentän määritteeksi ei ole annettu NOT NULL. tMyn

21. AUTO_INCREMENT –kenttä kasvattaa itseään automaattisesti yhdellä. Taulussa voi olla vain yksi kenttä määritelty AUTO_INCREMENT -määritteellä. • ZEROFILL-määritteellä kenttä täytetään etunollilla, jos siinä ei ole itsessään tarpeeksi merkkejä. • Taulun rakenteen näyttäminen onnistuu komennolla DESCRIBE taulunNimi. tMyn

22. Jos taulu on jo luotu, mutta sen rakennetta halutaan muuttaa, niin seuraavanlaisia muutoksia voidaan tehdä: • Lisätään uusi sarake taulukkoon • Otetaan sarake pois • Lisätään uusi rajoite (CONSTRAINT) • Poistetaan jokin rajoite • Asetetaan sarakkeelle oletusarvo (DEFAULT) • Poistetaan sarakkeelta oletusarvo • Muutetaan sarakkeen arvoaluetta tMyn

23. Taulun rakenteen muuttamiseen liittyvä syntaksi on varsin monipuolinen ja kokonaisuudessaan tuo syntaksi löytyy helpoiten netistä. Tässä käsitellään/otetaan huomioon vain muutama tavallisin piirre: tMyn

24. ALTER TABLE tbl_name alter_specification [, alter_specification] ... alter_specification: table_option ... | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (col_name column_definition,...) | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY tMyn

25. | DROP { INDEX | KEY} index_name | DROP FOREIGN KEY fk_symbol | RENAME [TO] new_tbl_name | ORDER BY col_name [, col_name] ... | CONVERT TO CHARACTER SET charset_name | [DEFAULT] CHARACTER SET charset_name column_definition: data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] data_type: | INT[(length)] [UNSIGNED] [ZEROFILL] | INTEGER[(length)] [UNSIGNED] [ZEROFILL] | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] | DATE | TIME | TIMESTAMP | DATETIME | YEAR tMyn

26. | CHAR(length) [CHARACTER SET charset_name] | VARCHAR(length) [CHARACTER SET charset_name] index_col_name: col_name [(length)] reference_definition: REFERENCES tbl_name [(index_col_name,...)] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION table_option: [TABLESPACE tablespace_name STORAGE DISK] ENGINE [=] engine_name | [DEFAULT] CHARACTER SET charset_name tMyn

27. Luodaan kokeiluja varten yksinkertainen taulu: CREATE TABLE tavoite (id INTEGER UNSIGNED NOT NULL, koti VARCHAR(30) NOT NULL, tyo VARCHAR(30) NULL); • HUOM! Vaikka id-kentän ominaisuudeksi voisi mieluusti antaa myös AUTO_INCREMENT (jotta siitä myöhemmin tulisi kätevästi PRIMARY KEY -kenttä), ei järjestelmä salli sitä ILMAN että nimeää sen samalla avainsarakkeeksi. tMyn

28. Lisätään yksi sarake vapaaAika VARCHAR(30) NULL: ALTER TABLE tavoite ADD COLUMN vapaaAika VARCHAR(30) NULL; tMyn

29. Lisätään yksi sarake, ja halutaan se juuri ennen vapaaAikaa: painoTavoite INT NOT NULL: ALTER TABLE tavoite ADD COLUMN painoTavoite INT NOT NULL AFTER tyo; tMyn

30. Lisätään vielä yksi sarake (joka voidaan sitten poistaa ”ilman tappioita”) jotakinIhanMuuta VARCHAR(30) NULL: ALTER TABLE tavoite ADD COLUMN jotakinIhanMuuta VARCHAR(30) NULL; tMyn

31. Poistetaan äskeinen sarake: ALTER TABLE tavoite DROP COLUMN jotakinIhanMuuta; tMyn

32. Lisätään uusi rajoite ADD CONSTRAINT ainutkertainen UNIQUE KEY (painoTavoite): ALTER TABLE tavoite ADD CONSTRAINT ainutkertainen UNIQUE KEY (painoTavoite); tMyn

33. Poistetaan rajoite (DROP KEY) sarakkeelta painoTavoite : ALTER TABLE tavoite DROP KEY ainutkertainen; tMyn

34. Asetetaan sarakkeelle tyo oletusarvo (SET DEFAULT) 'lisää liksaa': ALTER TABLE tavoite ALTER COLUMN tyo SET DEFAULT 'lisää liksaa'; tMyn

35. Kokeillaan oletusarvon toimivuutta: INSERT INTO tavoite (id, koti, painoTavoite, vapaaAika) VALUES (1, 'siivous perjantaisin', 67, 'kävelyä arki-iltoina'); • Ja katsotaan miltä näyttää (ja sen jälkeen rivi voidaan poistaa, jotta voidaan jatkaa kokeiluja rauhassa): SELECT * FROM tavoite; DELETE FROM tavoite; tMyn

36. tMyn

37. Poistetaan sarakkeelta tyo oletusarvo (DROP DEFAULT): ALTER TABLE tavoite ALTER COLUMN tyo DROP DEFAULT; tMyn

38. Muutetaan sarakkeen arvoaluetta: sarake tyo on VARCHAR(30) NULL, muutetaan se CHAR(30) NULL. • HUOM! Vaikka siis nimi pysyy samana, on silti annettava syntaksin mukaisesti old_col_name new_col_name: ALTER TABLE tavoite CHANGE COLUMN tyo tyo CHAR(30) NULL; tMyn

39. Jos sitä vastoin käyttää MODIFY COLUMN-määrettä, niin silloin ei tarvitse antaa old_col_name new_col_name -osaa. Palautetaan sarakkeen tyo tietotyyppi alkuperäiseksi: ALTER TABLE tavoite MODIFY COLUMN tyo VARCHAR(30) NULL; tMyn

40. Muutetaan sitten sarakkeen tyo nimeä mutta ei mitään muuta ominaisuutta siitä sarakkeesta: ALTER TABLE tavoite CHANGE COLUMN tyo tyoJutut VARCHAR(30) NULL; tMyn

41. Muutetaan sarakkeen tyoJutut ominaisuus NULL ominaisuudeksi NOT NULL: ALTER TABLE tavoite CHANGE COLUMN tyoJutut tyoJutut VARCHAR(30) NOT NULL; tMyn

42. Muutetaan sarakkeen tyoJutut ominaisuus NOT NULL takaisin ominaisuudeksi NULL: ALTER TABLE tavoite MODIFY COLUMN tyoJutut VARCHAR(30) NULL; tMyn

43. Lisätään uusi rajoite CONSTRAINT taPaaAvain PRIMARY KEY(id): ALTER TABLE tavoite ADD CONSTRAINT taPaaAvain PRIMARY KEY(id); tMyn

44. Lisätään sarakkeelle id ominaisuus AUTO_INCREMENT: ALTER TABLE tavoite MODIFY COLUMN id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT; tMyn

45. Muutetaan taulun tavoite nimeksi tavoiteAsetanta: ALTER TABLE tavoite RENAME TO tavoiteAsetanta; tMyn