SŘBD MySQLNejen praktická část

Delfín se jmenuje Sakila

Osnova Motivace Vlastnosti Architektura a zpracovatelé Instalace a konfig. instance MySQL Způsoby práce s MySQL Datové typy Jazyk pro definici dat - JDD Jazyk pro modifikaci dat - JMD Import dat Administrace, Odkazy

Motivace Rychlý, spolehlivý a bezpečný,

jednoduchý Cena?

› MySQL Enterprise basic €479 /Server/Year Vývojové nástroje Dobrá dokumentace Množství uživatelů Pokračující vývoj Různé platformy- přenositelnost Bezpečnost

Vlastnosti Je to relační SŘBD Je otevřený (používat a modifikovat) -

the GPL (GNU General Public License) Multivláknový SQL server, který

podporuje různé koncové zpracovatele, několik různých klientských programů, knihoven, administrativní nástroje, a hodně programových prostředí (APIs)

architektura client-server or embeded

Vlastnosti 2 APIs for C, C++, Eiffel, Java, Perl, PHP,

Python, Ruby, and Tcl Lokalizace – chybová hlášení,

podporuje různé znakové sady – i pro třídění a po-rovnávání

Vlastnosti 3 Od verze 5.0 podporuje

› Uložené procedury (stored procedures)› Pohledy (views)› Kurzory (cursor)› Transakce› Triggery (spouštěče)

Zpracovatel Innodb - poskytuje kompletní ACID transakce (atomic, consistent, isolated, durable) s potvrzování transakcí, rollback, crash recovery ... referenční integrita

Connectors: Native C API, JDBC, ODBC, .NET, PHP, Python, Perl, Ruby, VB

Management Services & UtilitiesBackup & Reco-very, Security, Replication, Cluster, Adminnistration, Configuration,Mig-ration & Meta-data

Connection pool Authentication, Thread Reuse, Connection Limits, Check Memory, CachesSQL Interface DDL, DML, uložené proc., pohledy, triggery

Parser Query translation, Object privilege

OptimizerAccess Paths, Statistics

Caches&Buffers

Storage Engines Memory, Index & Storage Management

MyISAM

InnoDB

Archive Federated

Memory

Merge Cluster

BDB

File SystemNTFS - NFS, SAN - NAS

Files & Logs

MySQL Servers

Porovnání zpracovatelů MyISAM: každá MyISAM tabulka je

uložena ve 3 souborech .frm (table format), .MYD (data), .MYI (indexy), uloží 232 vět, max. 64 indexů v tabulce - index spotřebovává disk. prostor -

(key_length+4)/0.67, spočteno za všechny indexy. Podporuje fultextové vyhledávání.

InnoDB: umožňuje transakce, definovat cizí klíče – vhodný pro velké databáze s velkým množstvím přístupů

Federated : pro zpracování dat na vzdálených serverech

Instalace MySQL MySQL 5.1 je současná verze (Release Candidate

release). MySQL 5.0 je současná stabilní (stable

production-quality - Generaly available) verze. Při instalaci z bin. balíčku s

instalátorem › vyberete typ instalace(Typical, Complete,

Custom) a spustíte instalaci› instalátor nastaví registry› změní menu Start› vytoří adresář. strukturu MySQL v Program

Files a nakopíruje soubory

Konfigurace instance MySQL serveru

› Vyberete typ konfigurace (detailní, standardní)› Vyberete typ serveru (pro vývoj (min. memo),

server (medium), dedicated server (všechnu paměť pro mysql)

› Zvolíte použití databáze (vícefunkční, transakční aplikace, bez transakcí)

......› Nakonec zadáte heslo správce

Způsoby práce s MySQL1. MySQL Command Line Client (CLC)2. Dávkové zpracování dat3. Mysql Administrator 4. Query Browser5. phpMyAdmin6. Příkazy v hostitelském jazyce7. ...

1/ MySQL Command Line Client

příkazový řádek

2/ Dávkové zpracování datProč dávkové zpracování? Opakované pravidelné jetí skriptů Jednodušší úprava překlepu Distribuce skriptů

shell>mysql -h host -u user -p < script.sql Enter password: ********

Postupné prohlížení dlouhých výstupů shell> mysql < script.sql | more

Uložení výstupu do souboru shell> mysql < script.sql > mysql.out-v -v -v … zobrazí i vykonávané příkazy ve výstupu-t … stejný formát výstupu jako u interaktivního módu

4/ MYSQL ADMINISTRATOR

5/ MySQL Query Browser

6/ PHPMYADMIN

7/ Příkazy MySQL v PHPSoubor.php...<? $spo = mysql_pconnect("server","user","heslo"); if ( !$spo ){ echo "\n<b>Chyba: </b>Nepodařilo se připojení k databázovému

serveru\n"; exit(); } $db = mysql_select_db("jméno_databaze"); if ( !$db ) { echo "\n<b>Chyba: </b>Nepodařilo se vybrat databázi\n"; exit(); } $rec = mysql_query("SELECT * FROM ucitel WHERE jmeno <> '' ORDER BY prijmeni"); if( !$rec ) { echo "\n<b>Chyba: </b>",MySQL_Error(),"\n"; exit(); } $n = mysql_num_rows($rec); if( $n == 0 ) { echo "<b>Nevybrala se zadna veta!</b>\n"; exit(); } ?>...

7/ Příkazy MySQL v PHP 2<table border=1>   <tr><th>Katedra</th>

 <th>Jméno</th>      <th>Telefon</th> <th>Místnost</th>   <th>Zrušení</th> <th>Opravení</th></tr>

<? for($i = 0; $i < $n; $i++) {$r = mysql_fetch_object($man); ?>

   <tr><td> <? echo $r->katedra ?></td>    <td><? echo "$r->prijmeni $r->jmeno";  ?></td> <td><? echo " $r->telefon \n"; </td>

<td><? echo $r->mistnost ?></td> <td><a href="javascript:do_delete('<? echo "$r->id', '$r->prijmeni"; ?>')">Zrušit</a></td>

<td><a href="fdemo-pri.php3?DBid=<? echo $r->id ?>"> Opravit</a></td>   </tr>

<?   } /* konec cyklu for */ ?>  </table>

Datové typy v MySQLBIT[(length)]TINYINT[(length)] SMALLINT[(length)]MEDIUMINT[(length)] INT[(length)]

INTEGER[(length)] BIGINT[(length)] REAL[(length,decimals)] DOUBLE[(length,decimals)]

FLOAT[(length,decimals)] DECIMAL(length,decimals) NUMERIC(length,decimals)

DATE | TIME | TIMESTAMP DATETIME | YEAR CHAR(length) VARCHAR(length) BINARY(length) VARBINARY(length) TINYBLOB | BLOB MEDIUMBLOB LONGBLOB TINYTEXT | TEXT MEDIUMTEXT | LONGTEXT ENUM(value1,value2,value3,...) SET(value1,value2,value3,...)

JDD – definice cizích klíčůCREATE TABLE test.pobocka ( ono char(1) NOT NULL PRIMARY KEY , address varchar(35), city varchar(15),... ); INSERT INTO test.pobocka VALUES('A','Ostrava'); INSERT INTO test.pobocka VALUES('B','Brno');

CREATE TABLE test.zakaznik ( cno char(5) NOT NULL PRIMARY KEY, company varchar(35), ... address varchar(30), ono char(1) REFERENCES pobocka,... );INSERT INTO test.zakaznik VALUES('A1234','VSB Ostrava','A');INSERT INTO test.zakaznik VALUES('B1111','VSB Ostrava-Poruba','B');INSERT INTO test.zakaznik VALUES('C2222','Ostravska Univerzita','C');

Funguje jako poznámka - nedělá nic !!!

JDD definice cizích klíčů 2mysql> show create table zakaznik\G // vypis definice tabulky*************************** 1. row *************************** Table: zakaznikCreate Table: CREATE TABLE `zakaznik` ( `cno` char(5) NOT NULL, `company` varchar(35) default NULL, `ono` char(1) default NULL, PRIMARY KEY (`cno`), KEY `FK_pobocka` (`ono`), CONSTRAINT `FK_pobocka` FOREIGN KEY (`ono`) REFERENCES `pobocka` (`ono`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

mysql> INSERT INTO test.zakaznik VALUES('A1234','VSB Ostrava','A');Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO test.zakaznik VALUES('B1111','VSB Ostrava-Poruba','B');Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO test.zakaznik VALUES('C2222','Ostravska Univerzita','C');ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

(`test/zakaznik`, CONSTRAINT `FK_pobocka` FOREIGN KEY (`ono`) REFERENCES `pobocka` (`ono`)

Zpracovatel Innodb

Cizí klíče a InnoDB[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

REFERENCES tbl_name (index_col_name, ...) [ON DELETE {RESTRICT |CASCADE |SET NULL |NO ACTION}] [ON UPDATE {RESTRICT |CASCADE |SET NULL|NO ACTION}] Tabulky musí existovat, blob a text nelze definovat jako cizí klíče Nedovolí vložit/aktualizovat větu s hodnotou, která není

v hlavní tabulce RESTRICT(NO ACTION)- nedovolí smazat/aktualizovat

záznamy v hlavní tabulce, když existují závislé věty - default

CASCADE - automaticky smaže věty, či aktualizuje hodnoty klíče odpovídajících vět v závislé tabulce

SET NULL - smaže nebo aktualizuje větu v hlavní tabulce a hodnoty cizího klíče v závislé tabulce nastaví na null.

JDD vytváření pohledůCREATE VIEW jm_pohledu(atr1, atr2,...) AS select .....

select nesmí obsahovat - poddotaz za FROM, odkaz na systémovou nebo uživ. proměnnou, odkaz na temporary tabulku apod.

atr1, atr2 - případné přejmenování sloupců

SHOW CREATE VIEWSELECT * FROM jm_pohledu

JDD - triggery2)drop trigger vyplog;DELIMITER |CREATE TRIGGER vyplog BEFORE INSERT ON lidi FOR EACH ROW BEGIN SET @prefix = SUBSTR(NEW.PRIJMENI, 1, 3);

select max(substr(login, 4, 3)) into @postfix from lidi where substr(prijmeni, 1, 3) = @prefix ;

set @postfix1 = ifnull(@postfix, 0)+1; set @postfix2 = LPAD(@postfix1, 3, '0'); SET NEW.LOGIN = Concat(lower(@prefix), @postfix2); END; |

DELIMITER ;

3)INSERT INTO lidi VALUES(null,'a','Jarda','Novotny');

1) Create table lidi ( id int(10) not null primary key auto_increment, login varchar(8) not null, jmeno varchar(20) not null, prijmeni varchar(30) );

JDD – uložené procedury-- příklad jednoduché procedurydrop procedure simpleproc;delimiter //CREATE PROCEDURE simpleproc (OUT param1 INT)BEGINSELECT COUNT(*) INTO param1 FROM zakaznik;END;//

delimiter ;

CALL simpleproc(@a);SELECT @a;+------+| @a |+------+| 2 |+------+

Import dat do MySQLload.sql load data local infile 'C:\\MySQL-obchod\\faktury3.txt' into table faktura fields terminated by ',’ enclosed by '\"' lines terminated by '\r\n' ignore 1 lines;

Faktury3.txt … ino,cno,idate,itotal,salesman 1054,"C8934",2004-03-15,1100.24,"647" 1055,"P8438",1990-05-07,604.91,"667" 1056,"M9916",1990-05-07,2615.58,"434" 1057,"Q7813",1990-05-07,35.09,"667" 1058,"T1401",1990-05-07,121.48,"281" 1059,"Z2361",1990-05-07,1673.21,"271"

Administrace

-- vytvoreni uzivatele a databaze stejneho jmenaCREATE USER ‘user'@'%' IDENTIFIED BY ‘heslo' ;GRANT USAGE ON * . * TO ' user '@'%' IDENTIFIED BY

‘heslo‘ WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

CREATE DATABASE IF NOT EXISTS `user` ; GRANT ALL PRIVILEGES ON `user` . * TO ‘user'@'%'; GRANT SELECT , INSERT , UPDATE , DELETE , CREATE ,

DROP , REFERENCES , INDEX , ALTER , CREATE TEMPORARY TABLES , CREATE VIEW , SHOW VIEW , CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON `test` . * TO ‘user'@'%';

literaturawww.mysql.comhttp://www.fsf.org/licenses/www.linuxsoft.czwww.kosek.cz - stránky J. Koskawww.supersvet.czInstalace php, MySQL, Apache,...www.apachefriends.org www.easyphp.org vertrigoserv.sourceforge.net