Netzflut

Webdesign, Webentwicklung, Drupal & Zeuchs

Mysql Views

Webdevelopment

Drupal

Betriebssysteme

Design

Sonstige

Do., 20/06/2013 - 13:34

Beispiel Daten

Tabelle 1 User
  1. CREATE TABLE IF NOT EXISTS `users` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(50) CHARACTER SET latin1 NOT NULL,
  4.   `email` varchar(150) CHARACTER SET latin1 NOT NULL,
  5.   `mobile` varchar(20) CHARACTER SET latin1 NOT NULL,
  6.   `address` varchar(255) CHARACTER SET latin1 NOT NULL,
  7.   PRIMARY KEY (`id`)
  8. ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;
  1. INSERT INTO `users` (`id`, `name`, `email`, `mobile`, `address`) VALUES
  2. (1, 'Aaa Mii', 'al@min.com', '6546464', 'Dhaka,Bangladesh'),
  3. (2, 'Saa Hee', 's@hed.com', '987979', 'Dhaka, Bangladesh'),
  4. (3, 'Maa Muu', 'm@mun.com', '646465', 'Dhaka,Bangladesh'),
  5. (9, 'Foo Saa', 'f@sa.com', '1234556', 'Dhaka, Bangladesh.');
Tabelle 2 Spiele
  1. CREATE TABLE IF NOT EXISTS `spiele` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `spielname` varchar(255) NOT NULL,
  4.   PRIMARY KEY (`id`)
  5. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
  1. INSERT INTO `spiele` (`id`, `spielname`) VALUES
  2. (1, 'DOTA'),
  3. (2, 'WoW'),
  4. (3, 'Super Mario');
Tabelle 3 Spieler
  1. CREATE TABLE IF NOT EXISTS `spieler` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `user_id` int(11) NOT NULL,
  4.   `spiel_id` int(11) NOT NULL,
  5.   PRIMARY KEY (`id`)
  6. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
  1. INSERT INTO `spieler` (`id`, `user_id`, `spiel_id`) VALUES
  2. (1, 1, 2),
  3. (2, 2, 3),
  4. (3, 9, 2),
  5. (4, 9, 1),
  6. (6, 3, 3);

Mysql View

  1. CREATE VIEW v AS
  2. SELECT
  3. spieler.*,
  4. spiele.spielname,
  5. users.name
  6. FROM `spieler`
  7. INNER JOIN spiele on spiele.id = spieler.spiel_id
  8. INNER JOIN users on users.id = spieler.user_id;

Updating a View

UPDATE v SET name = "Marco" WHERE spieler_id=1 Der Wert wird auch in der base table "users" geupdated. Wir sehen also views sind updatebar, einfach genial.Doch es gibt einige Ausnahmen: http://dev.mysql.com/doc/refman/5.0/en/view-updatability.html

Quellen

http://net.tutsplus.com/tutorials/databases/introduction-to-mysql-views/ http://skyontech.com/blog/create-MySQL-view-in-phpMyAdmin http://www.mysqltutorial.org/create-sql-views-mysql.aspx
Webdevelopment
Datenbanken
Add new comment
The content of this field is kept private and will not be shown publicly.
About text formats

Plain text

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <drupal-entity data-*>
  • Web page addresses and email addresses turn into links automatically.
  • Lines and paragraphs break automatically.

User Menu

Flag List

Last Edit