Taulujen määrittely ja muuttaminen

Taulujen perustaminen

• Taulun perustajalla täytyy olla valtuudet (GRANT) tietokantaan
• Kullekin taulun sarakkeelle annetaan nimi ja tietotyyppi (+ maksimipituus ja mahdolliset desimaalit)
• Nimeämisessä noudatettava yhteistä käytäntöä. Esim:
• http://www.isbe.state.il.us/ILDS/pdf/SQL_server_standards.pdf
• http://www.mssqltips.com/tipimages2/2110_DB_Naming_Standards_20100916.pdf

Taulujen perustaminen

• Tarvittaessa määritetään rajoitteita (NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE, DEFAULT ja CHECK)
• Sarakkeiden nimeämisessä pitää muistaa välttää varattujen sanojen käyttöä, ks.
• http://developer.mimer.se/validator/sql-reserved-words.tml
  

SQL-esimerkki: taulun luonti/1

Taulu luodaan CREATE TABLE –käskyllä:

CREATE TABLE Opiskelija (
  OpiskelijaID INTEGER NOT NULL,
  Etunimi VARCHAR(64),
  Sukunimi VARCHAR(128),
  Syntymaaika DATE,
  Koulutusohjelma VARCHAR(64),
  PRIMARY KEY (OpiskelijaID)
);

SQL-esimerkki: taulun luonti/2

CREATE TABLE Yritys (
  YritysID SMALLINT NOT NULL,
  YritysNimi VARCHAR(80),
  Toimialakoodi CHAR(10),
  YrLisaysPvm DATE DEFAULT Current_date,
  CONSTRAINT pk_YritysID PRIMARY KEY(YritysID)
);

• Neljä saraketta ja yksi rajoitin => 5 määrittelyä => 4 pilkkua!
  

Viite-eheys

Viite-eheys REFERENCES-määreellä:

CONSTRAINT fk_Yritys_Toimialakoodi FOREIGN KEY
(Toimialakoodi)
REFERENCES TOIMIALA (Toimialakoodi)

• Hyvä käytäntö: rajoittimen nimi kertoo, mikä rajoitin (fk) on kyseessä ja mihin tauluun (Yritys) ja sarakkeeseen (Toimialakoodi) rajoitin kohdistuu

Muutamia SQL-92-standardin tietotyyppejä

CHAR [(pit.), kiinteä, oletuspit. 1]

VARCHAR [(pit.), vaihtuvanmitt. merkkijono]
   -- VARCHAR on sama kuin CHAR VARYING

NUMERIC [(koko pituus [,desimaaliosa])]
   -- NUMERIC on sama kuin DECIMAL

INTEGER [kokonaisluku, pit. tuotekoht.]
   -- Voidaan lyhentää INT

DATE [vuosi (0001-9999), kk ja päivä]

TIME [(pit.), tunti, minuutti ja sekuntti]

BLOB [SQL-99, binary large object]

SQL:2003 – muutokset aikaisempaan standardiin

Uudet tietotyypit:

• BIGINT (tarkkuus tuotekohtainen, kuitenkin aina
• BIGINT >= INTEGER >= SMALLINT)
• MULTISET (voidaan luoda ARRAY-tyyppinen kokoelma)
• Sequence, CREATE TABLE LIKE ... Ym.

Esim. Taulun rakenteen kopiointi:

CREATE TABLE Opettaja LIKE Opiskelija;

Taulun muutos: ALTER TABLE

Paljon tuotekohtaisia eroja Esim. sarakkeen lisäys:

ALTER TABLE henkilo
ADD COLUMN sex varchar(16) DEFAULT 'male'
AFTER snimi;

Sarakkeen muutos/poisto: ALTER/DROP COLUMN

Ks. oma tuotteesi, esim.

ALTER TABLE henkilo
DROP COLUMN sex;

Eheysvaatimukset

• Tietokantaan kohdistuu kolmentyyppisiä eheysvaatimuksia:
• Avaineheys: taululla tulee olla perus- eli pääavain (PK)
• Viite-eheys: viiteavaimelle tulee löytyä arvo viitattavan taulun pääavaimesta
• Attribuuttieheys:  attribuuttien tulee kuulua sallittuun joukkoon
• Määritetään nk. rajoitteella (ks. seur. kalvo)
• Palvelin tarkistaa eheysvaatimusten toteutumisen

Rajoitteet eli pakotteet (constraints)

• Määritellään CREATE/ALTER TABLE -komennoissa
• Rajoitteet sarakkeille tai tauluille
• Rajoitteita ovat:
• avaimet: PRIMARY KEY, FOREIGN KEY ja UNIQUE
• oletusarvot ja raja-arvotarkistukset: DEFAULT ja CHECK
• esimerkkejä:

ALTER TABLE dept
  ADD CONSTRAINT pk_deptno PRIMARY KEY (deptno),
  ADD priority SMALLINT DEFAULT 1,
  ADD CONSTRAINT chk_sukup CHECK (sukupuoli IN (‘M’,’N’)
);

Primary Key, Unique ja Foreign key

Primary Key

CONSTRAINT pk_sarake PRIMARY KEY (sarakenimi1 [,sarakenimi2])

• Yksiköi taulun rivit
• Sarakkeiden tulee olla Not Null -tyyppisiä
• Käytetään hyväksi viite-eheyttä luotaessa

Unique

CONSTRAINT sarakenimi UNIQUE

• Luo yksilöivän indeksin
• Sarakkeet voivat sisältää Null-arvoja
• Voi olla useita / taulu

Foreign Key

CONSTRAINT fk_sarake FOREIGN KEY (sarakenimi)
REFERENCES vanhempi (perusvain)

• Luo viite-eheyden taulujen välille
• Voi sisältää useita sarakkeita
  

Primary Key / MySQL

”A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly ( and silently). A table can have only one PRIMARY KEY.”

http://dev.mysql.com/doc/refman/5.5/en/create-table.html#id879566

Viiteavaimen lisäys ja poistojen/päivitysten vyörytys

• Viite-eheys: CREATE/ALTER TABLE -käskyillä Esim. Puhelin-taulu, jonka vanhempi on Henkilo; jos henkilö poistetaan niin myös henkilön puhelimet poistetaan (ON DELETE CASCADE)
  

ALTER TABLE Puhelin
ADD CONSTRAINT fk_HenkiloID
FOREIGN KEY (HenkiloID)
REFERENCES Henkilo (HenkiloID)
ON DELETE CASCADE

• Viisi vaihtoehtoa: CASCADE, SET DEFAULT, SET NULL, NO ACTION, RESTRICT, joista kaksi viimeistä estävät poiston, jos vanhemmalla on lapsia
• Myös päivitykset voidaan vyöryttää: ON UPDATE CASCADE
  

Laskuri: automaattisesti kasvava sarake

• Taululle voidaan määritellä sarake, joka on automaattisesti kasvava numero
• Määrittelyssä annetaan alkuarvo ja lisäys
• Standardin mukaan laskuri luodaan joko IDENTITY-määreellä tai SEQUENCE-käskyllä (MySQL:ssä AUTO_INCREMENT-määreellä)
• Ominaisuus voidaan luoda myös CREATE TABLE, ALTER TABLE tai SELECT INTO –komennon yhteydessä
• Laskuri toimii usein perusavaimena
  

Laskurin luonti, 2 standardivaihtoehtoa

Esimerkki 1

CREATE TABLE tuote (
tuoteID INTEGER GENERATED ALWAYS AS IDENTITY
-- ja lisäksi esim.
START WITH 100
INCREMENT 1
MINVALUE 100
NO MAXVALUE
NO CYCLE,
... )

Esimerkki 2

CREATE SEQUENCE tuoteID
AS INTEGER
START WITH 1
INCREMENT BY 1
MAXVALUE 100000
MINVALUE 1

Laskurin luonti: MySQL

Luonti:

CREATE TABLE car (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(64)
)
AUTO_INCREMENT = 50; -- eka arvo 50

Käyttö:

INSERT INTO car (name) VALUES ('Ford');
    –--> id=50

SELECT LAST_INSERT_ID();

INSERT INTO car VALUES (100, 'Opel');
   ---> id=100, seuraava 101

• http://www.sqlines.com/mysql/auto_increment

Taulun poisto: DROP TABLE

• Taulun poisto tapahtuu DROP TABLE-käskyllä, esim. DROP TABLE temp
• Tällöin vyörytetään poistot, jos CASCADE DELETE on lapsitauluun määritetty, muutoin poisto estetään (jos viite-eheys on asetettu)
• Samalla poistuu taulussa olevat tiedot (olihan siis backup tallessa tai ROLLBACK-käsky hanskassa?)
• SQL:2008 standardoi TRUNCATE-käskyn, joka tyhjentää nopeasti taulun sisällön; käskyä ei voi peruuttaa
  

Taulun poisto vs. tyhjäys

Taulun poisto:

DROP TABLE henkilo;

Taulun tyhjäys:

SET SQL_SAFE_UPDATES=0;
truncate henkilo;
SET SQL_SAFE_UPDATES=1;

• MySQL WorkBench tarvii oletusasetuksillaan SQL_SAFE_UPDATES-muuttujan arvoon muutoksia

DML (Data Manipulation Language)

• SELECT-kyselyt
  
• Tietojen lisäys, muutos ja poisto
• Tapahtumien eli transaktioiden hallinta
  

Tietojen lisääminen

• INSERT INTO taulu VALUES (arvot), esim.

INSERT INTO tuote(tuoteNimi, hinta)
VALUES ('SQL-opas', 20);

• Sarakkeiden nimiä ei tarvitse luetella, jos kaikille annetaan arvo tai niillä on oletusarvo tai ne sallivat Null-arvon; on kuitenkin suositeltavaa luetella ne!
• Taulusta toiseen:
  

INSERT INTO taulu1 SELECT s1, s2 FROM taulu2;

• Usein voidaan antaa useita lisättäviä rivejä (Multirow inserts) yhdessä käskyssä

Tietojen muuttaminen eli päivittäminen

Olemassa olevien tietojen päivittäminen  / Syntaksi:

UPDATE taulu SET arvo = uusiarvo [WHERE ehto]

Esimerkki 1

UPDATE tuote SET hinta = 120
WHERE tuoteID = 123;          -- tuotteen 123 hinnaksi 120

10 %:n hinnan lisäys:

UPDATE tuotteet SET hinta = hinta * 1.1

Päivitys, jossa 2 taulua on liitetty toisiinsa

UPDATE tuote SET varastosaldo = 10
FROM tuote INNER JOIN myyja
ON tuote.myyjaID = myyja.myyjaID;

Tietojen poistaminen

Rivien poistaminen taulusta  / Syntaksi:

DELETE {FROM} [taulun nimi] WHERE [ehto poistettaville riveille]:

Esimerkki 1:

DELETE FROM tuote WHERE tuoteNimi = 'auto'

• WHERE-ehto tärkeä, muuten poistetaan KAIKKI rivit!

Poisto alikyselyn avulla:

DELETE FROM asiakas WHERE asiakasID
NOT IN (SELECT asiakasID FROM TILAUS)