SQL Fortgeschritten

Nun wollen wir tiefer in SQL einsteigen. Es kommen zwar einige Befehle dazu, doch im Grunde dreht sich immernoch alles um die 4 Hauptbefehle SELECT, INSERT, UPDATE und DELETE. So werden wir z.b JOINS kennenlernen, mit deren Hilfe wir Tabelle zusammenführen können, um daraus zu selektieren. Ausserdem lernen wir noch weitere Möglichkeiten kennen, Daten zu selektieren und schauen uns hilfreiche SQL-Funktionen an.

Vorbereitung

Um die folgenden Beispiele direkt auszuprobieren, müssen wir noch ein paar weitere Tabellen mit Inhalten erzeugen. Bislang haben wir die User-Tabelle:

Nun brauchen wir noch folgende Tabellen (ganz unten steht die Erklärung wie die Tabellen zueinanderpassen):

questions-Tabelle

CREATE TABLE IF NOT EXISTS `questions` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` int(10) unsigned NOT NULL,
  `question` text character set utf8 NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `questions` (`id`, `user_id`, `question`, `created`) VALUES
(1, 1, 'Wofür ist der DELETE Befehl in SQL da?', '2010-02-12 19:26:22'),
(2, 1, 'Wer kennt den Unterschied zwischen SQL und MySQL?', '2010-02-27 12:11:05'),
(3, 2, 'Kennt jemand ein gutes SQL Tutorial?', '2010-03-01 02:01:44'),
(4, 3, 'Muss ich mich in PHP auskennen für MySQL?', '2010-03-12 14:51:13'),
(5, 7, 'Wie hoch ist der Mount Everest?', '2010-03-15 19:14:56');

question-votes-Tabelle

CREATE TABLE IF NOT EXISTS `question_votes` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `question_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

INSERT INTO `question_votes` (`id`, `question_id`, `user_id`, `created`) VALUES
(1, 1, 2, '2010-03-16 14:25:29'),
(2, 1, 4, '2010-03-16 14:25:29'),
(3, 1, 5, '2010-03-16 14:25:37'),
(4, 1, 7, '2010-03-16 14:25:37'),
(5, 2, 1, '2010-03-16 14:26:07'),
(6, 2, 7, '2010-03-16 14:26:07'),
(7, 4, 1, '2010-03-16 14:26:39');

answers-Tabelle


CREATE TABLE IF NOT EXISTS `answers` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `question_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `answer` text character set utf8 NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

INSERT INTO `answers` (`id`, `question_id`, `user_id`, `answer`, `created`) VALUES
(1, 1, 3, 'Mit DELETE kannst du Einträge/Zeilen aus einer Tabelle löschen.', '2010-03-16 14:12:24'),
(2, 1, 7, 'Hier gibt es auch ein Tutorial zu DELETE:\r\nhttp://sql.lernenhoch2.de/lernen/sql-anfanger/delete-daten-loschen/', '2010-03-16 14:12:24'),
(3, 1, 1, 'Vielen dank für die Antworten!', '2010-03-16 14:12:38'),
(4, 2, 5, 'Schau mal hier: http://mysql.lernenhoch2.de/lernen/mysql-einleitung/alternativen-zu-mysql/', '2010-03-16 14:13:36'),
(5, 3, 4, 'Hier: http://sql.lernenhoch2.de/lernen/', '2010-03-16 14:14:45'),
(6, 4, 1, 'MySQL ist quasi der Zusammenschluss von PHP und MySQL, von daher musst du dich schon in PHP auskennen.', '2010-03-16 14:17:12'),
(7, 4, 7, 'Wenn ich mal fragen darf: Wofür brauchst du MySQL, wenn du dich nicht in PHP auskennst? Normalerweise lernt man doch erst PHP und nutzt dann MySQL um auf aus seinen PHP Skripten auf eine Datenbank zuzugreifen?', '2010-03-16 14:17:12'),
(8, 4, 3, 'Mein Chef meinte unsere Firma braucht jetzt eine MySQL Datenbank und ich solle mich da mal reinarbeiten...', '2010-03-16 14:19:50'),
(9, 4, 7, 'Oh.... ja die Story kenne ich, da hat der Chef in einem Magazin was über MySQL gelesen und das es im Web weit verbreitet ist und nun braucht er das auch...\r\n\r\nFrag ihn einfach mal, warum eure Firma eine MySQL Datenbank braucht, mich würde die Antwort interessieren ^^', '2010-03-16 14:19:50');

answer-votes-Tabelle

CREATE TABLE IF NOT EXISTS `answer_votes` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `answer_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

INSERT INTO `answer_votes` (`id`, `answer_id`, `user_id`, `created`) VALUES
(1, 1, 1, '2010-03-16 14:31:52'),
(2, 2, 1, '2010-03-16 14:31:52'),
(3, 2, 3, '2010-03-16 14:32:04'),
(4, 4, 2, '2010-03-16 15:38:56'),
(5, 4, 7, '2010-03-16 16:38:56'),
(6, 4, 1, '2010-03-16 17:38:56'),
(7, 6, 1, '2010-03-16 18:12:56'),
(8, 7, 1, '2010-03-16 18:32:56'),
(9, 7, 2, '2010-03-16 18:54:56'),
(10, 7, 4, '2010-03-16 19:10:56'),
(11, 9, 3, '2010-03-16 20:18:56'),
(12, 9, 5, '2010-03-16 21:01:56'),
(13, 9, 1, '2010-03-16 21:28:56'),
(14, 9, 4, '2010-03-16 22:38:56'),
(15, 9, 2, '2010-03-16 23:38:56');

Tabellen Erklärung

Als erstes was zu den Tabellennamen: ich nutze meist die englische Übersetzung im Plural, also anstelle der Tabelle „Benutzer“ habe ich „users“, anstelle von „Frage“ oder „Fragen“ habe ich „questions“. Diese Form habe ich gelernt, als ich mir das PHP Framework CakePHP beigebracht habe und da sie sehr gut funktioniert, halte ich mich seitdem an diese Form.

Unser Datenbank-Schema hat eine Tabelle „users“, in die alle Benutzer reinkommen. Die Spalte „ID“ ist unser Primary Key, den wir brauchen um die anderen Spalten korrekt zu referenzieren. In die Tabelle „questions“ kommen alle Fragen, die unsere Benutzer stellen, jede Frage hat als Primary Key eine „ID“ und als Foreign Key, bzw. Referenz Key, die Spalte „user_id“. Diese Form habe ich ebenfalls von CakePHP übernommen, dadurch lassen sich foreign-keys sehr schnell in der Tabelle erkennen. Dazu nimmt man den Namen der Tabelle, die man referenzieren möchte, im singular und setzt den Spalten Name des Primary Key der referenzierten Tabelle nach einem „_“ daran. Hört sich komplizierter an als es ist:

Tabelle „users“ hat „ID“ => Tabelle „questions“ bekommt die Spalte „user_id“, so einfach!

Im Prinzip funktioniert das für die anderen Tabellen ähnlich. Die Tabelle „answers“ braucht natürlich auch einen Primary Key, wiedermal ID. Und natürlich muss man die User-ID referenzieren (user_id), damit man weiß, welcher User die Antwort geschrieben hat. Aber natürlich muss man noch wissen, zu welcher Frage die Antwort geschrieben wurde, deshalb: questions -> ID => „question_id“.

Ich habe zwei Vote-Tabellen erstellt (damit wir wissen welche Fragen und Antworten von den Usern gut bewertet wurden). Jeweils eine für Fragen und eine für Antworten. Gespeichert wird der User der gevotet hat und für welche Frage / Antwort er gevotet hat und wann. Ob das für eine wirkliche Webanwendung die beste Umsetzung ist mag dahingestellt sein, aber für die folgenden SQL-Queries sind sie zumindest lehrreich.

hier geht's weiter...



Feedback Formular