MySQL USE Datenbank - vorwiegend für Netzanwendungen wird unter LINUX als Shareware angeboten, unter Windows mit Bezahlung läßt sich gut mit PHP oder PERL verbinden verwendet zum Anlegen von Datenbanken und für Auswertungen SQL-Statements allerdings ist nicht der volle Sprachumfang von SQL implementiert als herausragende Eigenschaft von MySQL wird seine Geschwindigkeit angegeben Universität HalleWittenberg Praktische DatenbankProgrammierung Form: USE db_name teilt MySQL mit, daß die Datenbank db_name nun die Standarddatenbank ist. Alle Befehle wirken bis auf Widerruf auf diese Datenbank: mysql> USE db1; mysql> SELECT count(*) FROM mytable; # select von db1.mytable mysql> USE db2; mysql> SELECT count(*) FROM mytable; # select von db2.mytable Wenn eine bestimmte Datenbank angesprochen wird, wird der Befehl USE übergangen: mysql> USE db1; mysql> SELECT author_name,editor_name FROM author,db2.editor WHERE author.editor_id = db2.editor.editor_id; Seite 1 Universität HalleWittenberg SELECT SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [DISTINCT | DISTINCTROW | ALL] select_expression,... [INTO OUTFILE 'file_name' export_options] [FROM table_references [WHERE where_definition] [GROUP BY col_name,...] [HAVING where_definition] [ORDER BY {unsigned_integer | col_name} [ASC | DESC] ,...] [LIMIT [offset,] rows] [PROCEDURE procedure_name] ] STRAIGHT_JOIN --> Tabellen werden exakt in der Reihenfolge der FROM-Anweisung verknüpft SELECT ... INTO OUTFILE 'file_name' Ausgabe in eine Datei SQL_SMALL_RESULT --> teile dem Optimizer mit, daß das Resultat recht klein sein wird LIMIT begrenzt die Zahl der ausgegebenen Tupel (LIMIT5,10; # Zeilen 6-15 ausgeben) Praktische DatenbankProgrammierung Seite 2 Funktionen in MySQL Das SELECT-Statement hat folgende Form: Universität HalleWittenberg Praktische DatenbankProgrammierung Seite 3 Aggregatfunktionen (COUNT, SUM, AVG, MAX, MIN, VARIANCE, STDDEV...) Datum - und Zeitfunktionen (DATE, TIME, TIMESTAMP DATETIME (=DATUM/ZEIT)) Arithmetische Funktionen (ABS, SIN, COS, LN, LOG, MOD, FLOOR, CEIL, ...) Zeichenfunktionen (TOLOWER, TOUPPER, LPAD, RPAD, REPLACE, SUBSTR, INSTR..) Umwandlungsfunktionen (TO_CHAR, TO_ASCII ...) Andere Funktionen (GREATEST, LEAST...) Universität HalleWittenberg Praktische DatenbankProgrammierung Seite 4 Workarounds in MySQL (1) Workarounds in MySQL (2) Fremdschlüssel - - nicht verwendet, um Tabellen zu verknüpfen (JOIN), sicherstellen der Integrität einiger Spalten von Tabellen Beispielsweise kann man hiermit erzwingen, dass wenn ein Datensatz verändert wird, in einer anderen Tabelle entsprechende Veränderungen automatisch vorgenommen werden. Es gibt viele Gründe, diese möglichst wenig einzusetzen, da sie das DBHandling verkomplizieren und die Geschwindigkeit des Zugriffs herabsetzen. Views Praktische DatenbankProgrammierung Locks Um die Performance zu erhöhen, verwendet MySQL ein System von Threads (Prozeß, der gerade auf die Tabelle zugreift). Locks sind selten nötig, aber möglich. Sie sperren die Tabelle für den Thread. Form: LOCK TABLES tbl_name [AS alias] {READ | [LOW_PRIORITY] WRITE} [, tbl_name {READ | [LOW_PRIORITY] WRITE} ...] ... UNLOCK TABLES Beispiel für LOCK: mysql> LOCK TABLES trans READ, customer WRITE; mysql> select sum(value) from trans where customer_id= some_id; mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id; mysql> UNLOCK TABLES; Views werden durch temporäre Tabellen ersetzt. Universität HalleWittenberg Seite 5 Universität HalleWittenberg Workarounds in MySQL (3) Subselects MySQL unterstützt die folgenden JOIN Ausdrücke in SELECT Statements: Beispiele: die Schnittmenge der Teilnehmer von 2 Kursen ist gefragt in Standard-SQL: SELECT * FROM table1 WHERE id IN (SELECT id FROM table2); SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2); Hier der Ersatz in MySQL SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id; SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL Seite 6 JOIN Subselects werden durch LEFT [OUTER] JOIN umschrieben: Praktische DatenbankProgrammierung table_reference, table_reference table_reference [CROSS] JOIN table_reference table_reference STRAIGHT_JOIN table_reference table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr table_reference LEFT [OUTER] JOIN table_reference USING (column_list) table_reference NATURAL LEFT [OUTER] JOIN table_reference Eine Tabellen-Referenz kann mit einem Alias versehen werden: tbl_name AS alias_name oder tbl_name alias_name : Komplexere Statements sollte man mit temporären Dateien umschreiben. Universität HalleWittenberg Praktische DatenbankProgrammierung Seite 7 Universität HalleWittenberg Praktische DatenbankProgrammierung Seite 8 SHOW LOCK/UNLOCK von Tabellen SHOW zeigt Informationen über Datenbanken, Tabellen, Spalten oder den Server an. Wenn die Option LIKE benutzt wird, können Wildcards angegeben werden. LOCK TABLES sperrt eine ganze Tabelle für den Thread, also für den Prozeß, der gerade auf die Tabelle zugreift. UNLOCK TABLES entsperrt die Tabelle und gibt diese für andere Threads frei. Alle Tabellen, die gesperrt sind, werden automatisch entsperrt, wenn dieser Thread eine andere Tabelle sperrt, oder die Verbindung zum Server löst. Form: Form: SHOW DATABASES [LIKE wild] oder SHOW TABLES [FROM db_name] [LIKE wild] oder SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild] oder SHOW INDEX FROM tbl_name [FROM db_name] oder SHOW STATUS oder SHOW VARIABLES [LIKE wild] oder SHOW PROCESSLIST LOCK TABLES tbl_name [AS alias] {READ | [LOW_PRIORITY] WRITE} [, tbl_name {READ | [LOW_PRIORITY] WRITE} ...] ... UNLOCK TABLES Wenn ein Thread eine READ Sperre setzt, dann kann dieser und alle andere Threads (!) nur aus dieser Tabelle lesen. Also merke: Ein WRITE Lock sperrt andere Threads für Lese-und Schreibvorgänge, ein READ Lock ermöglicht immer noch das Lesen für alle Threads. Jeder Thread wartet solange auf seinen Einsatz, bis er alle angeforderten Locks auch zugewiesen bekommt (was lange dauern kann). WRITE LOCKS haben normalerweise eine höhere Priorität vor READ LOCKS. näheres in http://www.little-idiot.de/mysql/mysql-129.html#locks oder SHOW TABLE STATUS [FROM db_name] [LIKE wild] Beispiel: mysql> SHOW INDEX FROM mydb.mytable; Universität HalleWittenberg Praktische DatenbankProgrammierung Seite 9 Universität HalleWittenberg InnoDB - Tabellen CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(erstellungs-definition,...)] [tabellen_optionen] [select_statement] tabellen_optionen [CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...) REFERENCES table_name (index_col_name, ...) [ON DELETE CASCADE | ON DELETE SET NULL] Transaktionen mit BEGIN einleiten und mit COMMIT beenden. COMMIT führt alle seit BEGIN angegebenen SQL-Kommandos tatsächlich aus. Statt COMMIT gesamte Transaktion mit ROLLBACK widerrufen.. Wie weit offene Transaktionen den Lesezugriff auf eine Tabelle und deren Veränderung durch andere Clients blockieren, hängt von der Implementierung der Transaktionsunterstützung ab. Universität HalleWittenberg Praktische DatenbankProgrammierung Seite 10 CREATE Fremdschlüssel in InnoDB Tabellen seit Version 4.0 in MySQL läßt sich damit referentielle Integrität auf DB Ebene herstellen. Praktische DatenbankProgrammierung Seite 11 TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYSISAM } oder COMMENT = "string" oder MAX_ROWS = # oder MIN_ROWS = # oder PASSWORD = "string" ....... Beispiel: CREATE TABLE kunden ( kunden_nr TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY(kunden_nr), INDEX(name, ort)) TYPE=InnoDB; Universität HalleWittenberg Praktische DatenbankProgrammierung Seite 12 Beispiel für Fremdschlüssel Links CREATE # PARENT TABLE CREATE TABLE `autor` ( `id` int(10) NOT NULL , `name` varchar(128) NOT NULL default '', PRIMARY KEY (`id`), KEY `NAME` (`name`) ) TYPE=InnoDB; # CHILD TABLE CREATE TABLE `buch` ( `id` int(10) NOT NULL, `autor_id` int(10) NOT NULL , PRIMARY KEY (`id`), INDEX idx_autor_id (autor_id), FOREIGN KEY (autor_id) REFERENCES autor(id) ON DELETE CASCADE ) TYPE=InnoDB Universität HalleWittenberg Praktische DatenbankProgrammierung Seite 13 http://www.little-idiot.de/mysql/ http://www.rent-a-database.de/mysql/ http://www.selflinux.org/selflinux/html/mysql.html http://ffm.junetz.de/members/reeg/DSP/ http://www.tbee.de/mysql/t5_mysql_foreignkey.php http://www.mysql.com http://www.linux-magazin.de/Artikel/ausgabe/2001/08/ mysql/mysql.html InnoDB erklärt Universität HalleWittenberg Praktische DatenbankProgrammierung Seite 14