
   Einige Beispiele zum Anlegen von Datenbanken, Tabellen, Datensätzen, löschen von Datensätzen, ändern von Datensätzen

// eine utf8 datenbank erzeugen
CREATE DATABASE `$db_name` DEFAULT CHARACTER SET $db_caracter_set COLLATE $db_collation;
CREATE TABLE `$db_name`.`adressen` (`adr_id` INT(15) NOT NULL AUTO_INCREMENT ,`anrede` VARCHAR(20) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`vorname` VARCHAR(30) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`nachname` VARCHAR(30) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`plz` INT(6) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`ort` VARCHAR(50) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`strasse` VARCHAR(50) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`web1` VARCHAR(100) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`mail1` VARCHAR(50) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`web2` VARCHAR(100) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`mail2` VARCHAR(50) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`geburtstag` VARCHAR(15) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`telefon1` VARCHAR(20) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`handy1` VARCHAR(20) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`abteilung` VARCHAR(50) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`beruf` VARCHAR(50) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`branche` VARCHAR(50) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`fax` VARCHAR(20) NOT CHARACTER SET $db_caracter_set COLLATE $db_collation NULL ,`filter_id` VARCHAR(15) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL DEFAULT '1',`urgend_id` VARCHAR(15) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL DEFAULT '1',`user_sperre` VARCHAR(1) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL`user_id` INT(15) NOT NULL) ENGINE = $db_engine
CREATE TABLE `$db_name`.`filter` (`filter_id` VARCHAR( 15 ) NOT NULL ,`name` VARCHAR( 20 ) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`anzeigename` VARCHAR( 50 ) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL) ENGINE = $db_engine
CREATE TABLE `$db_name`.`termine` (`datum` VARCHAR( 11 ) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`id` INT( 15 ) NOT NULL AUTO_INCREMENT ,`text` TEXT CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`user_id` INT( 15 ) NOT NULL ,`zusatz` VARCHAR( 3 ) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,PRIMARY KEY ( `id` )) ENGINE = $db_engine
CREATE TABLE `$db_name`.`user` (`user_id` INT( 15 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,`anmeldename` VARCHAR( 20 ) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`vorname` VARCHAR( 25 ) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`nachname` VARCHAR( 25 ) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`passwort` VARCHAR( 50 ) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`zusatz` TEXT CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL) ENGINE = $db_engine
CREATE TABLE `$db_name`.`wichtigkeit` (`urgend_id` INT( 15 ) NOT NULL AUTO_INCREMENT ,`bez` VARCHAR( 25 ) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`zusatz` VARCHAR( 50 ) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,PRIMARY KEY ( `urgend_id` )) ENGINE = $db_engine


// ein feld hinzufuegen
ALTER TABLE `wichtigkeit` ADD `FELDNAME` VARCHAR( 3 ) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ; // feld an tabelle anfügen

// ein feld aendern
ALTER TABLE `adressen` CHANGE `adr_id` `adr_id` INT( 15 ) NOT NULL AUTO_INCREMENT  // einen tabellenwert auf autowert setzen

// ein feld loeschen
ALTER TABLE `adressen` DROP `FELDNAME`   // eine tabellenzeile löschen

// filtertabelle anlegen
 CREATE TABLE `adressen_script`.`filter` (`filter_id` VARCHAR( 15 ) NOT NULL ,`name` VARCHAR( 20 ) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL ,`anzeigename` VARCHAR( 50 ) CHARACTER SET $db_caracter_set COLLATE $db_collation NOT NULL) ENGINE = $db_engine

// Updaten einer Tabelle, hier termine
UPDATE `adressen_script`.`termine` SET `ymdhm` = '2010_6_22_0_0', `adr_id` = '30', `geburtstag` = '1', `wiederholen` = '1' WHERE `termine`.`id` =6 LIMIT 1 ;

// einen Datensatz einfuegen
INSERT INTO `adressen_script`.`filter` (`filter_id` ,`name` ,`anzeigename`) VALUES (NULL , 'in_arbeit', 'In Arbeit');

// einen Datensatz loeschen
DELETE FROM `adressen_script`.`filter` WHERE `adressen_script`.`filter_id`= 2 LIMIT 1
