Opakování

V této kapitole se nenaučíte nic nového, ale protože opakování jest matkou moudrosti, zopakujete si co ste se už naučili.

V příkladech vytvořím tabulky, vložím nějaké data, upravím je, zase smažu atd. Pokusím se ukázat příkazy SQL v plné síle, někdy možná na úkor účelnosti a smyslnosti takových příkazů. Příkazy nebudu moc komentovat, sami určitě snadno poznáte co je jejich účelem.

CREATE TABLE
INSERT INTO
ALTER TABLE
UPDATE
SELECT
CREATE VIEW
DELETE FROM
DROP TABLE, VIEW
MySQL/MariaDB, SQLite, Oracle

CREATE TABLE

rimmer1=> CREATE TABLE zaci (
id SERIAL NOT NULL PRIMARY KEY,
pohlavi BOOL NOT NULL DEFAULT true,
jmeno VARCHAR(40) CHECK (jmeno != '') NOT NULL,
prijmeni VARCHAR(40) CHECK (prijmeni != '') NOT NULL
);
NOTICE: CREATE TABLE will create implicit sequence "zaci_id_seq" for serial column "zaci.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zaci_pkey" for table "zaci"
CREATE TABLE

rimmer1=> CREATE TABLE znamky (
id SERIAL NOT NULL PRIMARY KEY,
zaci_id INTEGER REFERENCES zaci(id) NOT NULL,
kod_predmetu CHAR(4) NOT NULL CHECK (kod_predmetu != ''),
znamka NUMERIC(1,0) CHECK (znamka >=1 AND znamka <= 5) DEFAULT 5,
datum DATE DEFAULT current_date
);
NOTICE: CREATE TABLE will create implicit sequence "znamky_id_seq" for serial column "znamky.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "znamky_pkey" for table "znamky"
CREATE TABLE

Jenom na okraj bych podotknul, že primární klíč by měl být jako první sloupec. Urychluje to práci s databází^(zdroj?). Také je zvykem jej pojmenovat id. A není od věci, aby měla každá tabulká svůj primární klíč.

Poznámka: current_date je funkce Postgresu vracející aktuální datum v době použití funkce (tj. v době vložení hodnot do tabulky).

INSERT INTO

INSERT INTO zaci VALUES (DEFAULT, DEFAULT, 'Jan', 'Maly');
INSERT INTO zaci VALUES (DEFAULT, FALSE, 'Jana','Mala');
INSERT INTO zaci(jmeno, prijmeni, pohlavi) VALUES ('Jana', 'Velká', false);

INSERT INTO znamky VALUES (DEFAULT, 1, 'MA10', 5, DEFAULT);
INSERT INTO znamky VALUES (DEFAULT, 1, 'FY10', 3, DEFAULT);
INSERT INTO znamky VALUES (DEFAULT, 2, 'FY10', 3, TO_DATE('2014-12-24','yyyy-mm-dd'));
INSERT INTO znamky(zaci_id, kod_predmetu, znamka) VALUES (2, 'FY10', 1);
INSERT INTO znamky(znamka, kod_predmetu, zaci_id) VALUES (2, 'FY10', 1);

rimmer1=> SELECT * FROM znamky;
id | zaci_id | kod_predmetu | znamka | datum
----+---------+--------------+--------+------------
1 | 1 | MA10 | 5 | 2013-12-05
2 | 1 | FY10 | 3 | 2013-12-05
3 | 2 | FY10 | 3 | 2014-12-24
4 | 2 | FY10 | 1 | 2013-12-05
5 | 1 | FY10 | 2 | 2013-12-05
(5 řádek)

ALTER TABLE

ALTER TABLE znamky RENAME TO hodnoceni; -- prejmenovani tabulky
ALTER TABLE hodnoceni RENAME zaci_id TO id_zaci; -- prejmenovani sloupce
ALTER TABLE zaci ADD rodne_cislo CHAR(10) UNIQUE; -- pridani sloupce
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'zaci_rodne_cislo_key' for table 'zaci'
ALTER
ALTER TABLE zaci RENAME TO studenti;

Vše za -- (dvěmi mínus) až do konce řádku je považováno za komentář.

Poznámka: Po přejmenování tabulky zaci na studenti tabulka hodnoceni (dříve znamky) odkazuje sloupec id_zaci do tabulky studenti (čili žádný problém, bro!).

UPDATE

UPDATE studenti SET rodne_cislo = '7951010611', prijmeni = 'Vdana'
WHERE id = 2;
UPDATE studenti SET rodne_cislo = '7901220611'
WHERE studenti.id = 1;
UPDATE hodnoceni SET znamka = 1*1+2 WHERE (znamka > 2 AND
znamka < 2+2) OR znamka = 3; -- dokážete to zjednodušit? :-)
UPDATE 2

SELECT

rimmer1=> SELECT * FROM studenti, hodnoceni WHERE id = id_zaci;
ERROR: column reference "id" is ambiguous

Sloupec se jménem id je v obou tabulkách. Tak takhle to nepůjde. S tím se musíte nějak vypořádat (přest tzv. tečkovou notaci).

rimmer1=> SELECT * FROM studenti, hodnoceni
WHERE studenti.id = id_zaci;
id | pohlavi | jmeno | prijmeni | rodne_cislo | id | id_zaci | kod_predmetu | znamka | datum
----+---------+-------+----------+-------------+----+---------+--------------+--------+------------
1 | t | Jan | Maly | 7901220611 | 1 | 1 | MA10 | 5 | 2013-12-05
2 | f | Jana | Vdana | 7951010611 | 4 | 2 | FY10 | 1 | 2013-12-05
1 | t | Jan | Maly | 7901220611 | 5 | 1 | FY10 | 2 | 2013-12-05
1 | t | Jan | Maly | 7901220611 | 2 | 1 | FY10 | 3 | 2013-12-05
2 | f | Jana | Vdana | 7951010611 | 3 | 2 | FY10 | 3 | 2014-12-24
(5 řádek)

Zapamatujte si, že pokud vytváříte nějaký SQL příkaz, který budete používat opakovaně, je lepší používat tečkovou notaci. Nikdy totiž nevíte, kdy vám někdo přídá do jedné tabulky pomocí ALTER TABLE sloupeček se stejným názvem, jako je v jiné tabulce a z fungujícího SQL příkazu vám udělá nefungující.

CREATE VIEW

DELETE FROM

rimmer1=> DELETE FROM hodnoceni WHERE znamka >= 2*2 AND id_zaci = 2;
DELETE 0
rimmer1=> DELETE FROM hodnoceni; -- bacha, bez WHERE se smaze vsechno!
DELETE 5

DROP TABLE, VIEW

rimmer1=> DROP VIEW IF EXISTS zaci;
DROP VIEW
rimmer1=> DROP TABLE studenti;
ERROR: cannot drop table studenti because other objects depend on it
DETAIL: constraint znamky_zaci_id_fkey on table hodnoceni depends on table studenti
rimmer1=> DROP TABLE IF EXISTS hodnoceni;
DROP TABLE
rimmer1=> DROP TABLE studenti;
DROP TABLE

MySQL/MariaDB, SQLite, Oracle

Protože byla tahle kapitola tak extrémě nenáročná, přepsat SQL příkazy do ostatních databází vám dám za domácí úkol.

Trochu napovím: MySQL nemá funkci TO_DATE a ignoruje CHECK, který navíc musí být na konci definice sloupce. MySQL, SQLite ani Oracle nemají typ SERIAL. Oracle navíc nemá ani AUTO_INCREMENT, takže se musí hodnoty v primárním klíči vždy INSERTovat pomocí sequence.NEXTVAL. Oracle nezná klauzuli IF EXISTS. SQLite neumí používat DEFAULT v INSERTu. Oracle nepoužívá AS pro přejmenování tabulky (ale bez AS to umí). SQLite má jen omezený příkaz ALTER TABLE. V Oracle byste měli používat typ VARCHAR2 místo typu VARCHAR.

Na zbytek už určitě příjdete sami.