Databáze 2011/2012 SQL – DDL (CREATE/ALTER/DROP TABLE), DML (INSERT/UPDATE/DELETE) RNDr.David Hoksza, Ph.D. http://siret.cz/hoksza
Osnova • Seznámení s SQL Server Management Studiem (SSMS) • Základní architektura Microsoft SQL Serveru 2008
• Vytváření tabulek o o
CREATE TABLE Integritní omezení
• Změna tabulky o
ALTER TABLE
• Aktualizace dat o o
INSERT UPDATE
• Cvičení
SSMS
Architektura MSSQL 2008 Server MSSQL Server 1
MSSQL Server N
Databáze1 schéma 1
Databáze1 schéma 1
schéma 2
schéma 2
schéma N
schéma N ….
DatabázeN schéma 1
DatabázeN schéma 1
schéma 2
schéma 2
schéma N
schéma N
Dokumentace MSSQL • Microsoft SQL Server Books Online (BOL) o http://msdn.microsoft.com/en-us/library/ms130214.aspx
CREATE TABLE CREATE TABLE name (sloupec1 datovy_typ1 [DEFAULT value] [omezeni] [, sloupec2 datovy_typ2 …] ) • základní datové typy v MSSQL o o o o o o o o
INT FLOAT (N)CHAR(n), (N)VARCHAR(n) BIT MONEY DATETIME, DATE, TIME TEXT, (N)VARCHAR(MAX) IMAGE, BINARY(n), VARBINARY(MAX)
Integritní omezení (IO) • NOT NULL o sloupec nesmí obsahovat NULL hodnoty
• UNIQUE o sloupec nesmí obsahovat duplicity
• PRIMARY KEY o UNIQUE + NOT NULL
• CHECK o hodnota sloupce musí odpovídat podmínce o podmínka nesmí obsahovat poddotaz, pouze jednoduché porovnání
• FOREIGN KEY o hodnota ve sloupci (sloupcích) musí obsahovat hodnotu (hodnoty) ze sloupce (sloupců) jiné tabulky označeného (označených) jako primární klíč
CREATE TABLE - příklad CREATE TABLE publisher ( id INT PRIMARY KEY, name VARCHAR(256) NOT NULL UNIQUE, address VARCHAR(512))
CREATE TABLE book ( id INT PRIMARY KEY, name VARCHAR(256) NOT NULL UNIQUE, author VARCHAR(512) NOT NULL, price MONEY CHECK (price > 0), detail VARCHAR(MAX), reprint BIT DEFAULT 0, publisher_id INT FOREIGN KEY REFERENCES publisher(ID))
Pojmenované IO CREATE TABLE book ( id INT, name VARCHAR(256) NOT NULL, author VARCHAR(512) NOT NULL, price MONEY, detail VARCHAR(MAX), reprint BIT DEFAULT 0, publisher_id INT, CONSTRAINT pk_book_id PRIMARY KEY(id), CONSTRAINT uq_book_name UNIQUE(name), CONSTRAINT chk_book_price CHECK(price > 0), CONSTRAINT fk_book_publisher FOREIGN KEY (publisher_id) REFERENCES publisher(id))
Rozšířená referenční integrita FOREIGN KEY REFERENCES … [ON DELETE …][ON UPDATE …] • Co se stane při smazání • Co se stane při update řádku, na který je odkazovaného PK? odkazováno? o ON DELETE NO ACTION • chyba o ON DELETE CASCADE • smazání odkazujícího řádku o ON DELETE SET NULL • nastavení hodnoty FK na NULL o ON DELETE SET DEFAULT • nastavení hodnoty FK na defaultní hodnotu
o ON UPDATE NO ACTION • chyba o ON UPDATE CASCADE
• update hodnoty FK o ON DELETE SET NULL • nastavení hodnoty FK na NULL o ON DELETE SET DEFAULT • nastavení hodnoty FK na defaultní hodnotu
IDENTITY • automatické generování hodnot pro (především) PK sloupce CREATE TABLE publisher ( id INT PRIMARY KEY IDENTITY(1,1), name VARCHAR(256) NOT NULL UNIQUE, address VARCHAR(512)) INSERT INTO publisher(name) VALUES('publisher''s name');
ALTER TABLE • změna definice tabulky ALTER TABLE table_name …
ADD column_definition ADD CONSTRAINT named_constraint_definition ALTER COLUMN column_definition DROP COLUMN column_name DROP CONSTRAINT constraint_name
ALTER TABLE - příklad ALTER GO ALTER GO ALTER CHECK GO ALTER GO ALTER GO
TABLE book ADD test REAL; TABLE book ALTER COLUMN test INT; TABLE book ADD CONSTRAINT chk_book_test (test % 2 = 0); TABLE book DROP CONSTRAINT chk_book_test;
TABLE book DROP COLUMN test;
• GO ukončuje dávku v MSSQL
INSERT INTO • INSERT INTO table_name[(column1, column2, …, columnN)] VALUES (value1, value2, …, valueN) • Každý INSERT vloží jeden nový řádek • Nevyplněné hodnoty se nastaví na NULL (pokud je daný sloupec NOT NULL, vyvolá se chyba) • Pořadí a počet hodnot musí odpovídat definici sloupců v INTO části
INSERT INTO - příklad INSERT INTO publisher(id, name) VALUES (1, 'publisher 1'); INSERT INTO publisher(name, id, address) VALUES ('publisher 2', 2, 'address 2')
INSERT INTO publisher VALUES (3, 'publisher 3', 'address 2') INSERT INTO book(id, author, name, publisher_id) VALUES (1, 'author1', 'name1', 2); INSERT INTO book(id, author, name, publisher_id) VALUES (2, 'author1', 'name2', (SELECT id FROM publisher WHERE name = 'publisher 2'))
UPDATE UPDATE table_name SET column_name = value [, column_name = value, …] [FROM table_name] [WHERE condition] • FROM klauzule v UPDATE je specifikum MSSQL
UPDATE – příklad UPDATE book SET price = 15 o nastav cenu všech knih na 15
UPDATE book SET price = 2*price o zdvojnásob cenu všech knih
UPDATE book SET price = 2*price FROM book b JOIN publisher p ON (b.publisher_id = p.id) WHERE p.address LIKE '%USA%' o zdvojnásob cenu knih nakladatelů z USA o tabulka book ve FROM klauzuli se automaticky identifikuje s tabulkou book z UPDATE části, tj. s updatovanou tabulkou
DELETE DELETE FROM table_name [WHERE condition] • Vymazání záznamů odpovídající podmínce • Není-li WHERE klauzule specifikována, mažou se všechny záznamy v dané tabulce
DELETE FROM books WHERE price > 20
DROP TABLE • Smazání tabulky
DROP TABLE book DROP TABLE publisher
• Na pořadí záleží !
