MySQL-Übungen, Teil2 (Stand:12.9.02, Florian Jurgeit) ****************************************************** mysql> create table buch (b_id int not null auto_increment, titel varchar(20), verlag_id int, preis float, primary key(b_id)); Query OK, 0 rows affected (0.06 sec) mysql> describe buch; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | b_id | int(11) | | PRI | NULL | auto_increment | | titel | varchar(20) | YES | | NULL | | | verlag_id | int(11) | YES | | NULL | | | preis | float | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> insert into buch values ('','php',1,10.50); Query OK, 1 row affected (0.00 sec) mysql> select * from buch; +------+-------+-----------+-------+ | b_id | titel | verlag_id | preis | +------+-------+-----------+-------+ | 1 | mysql | 1 | 10.5 | | 2 | php | 1 | 10.5 | | 3 | win98 | 2 | 12 | +------+-------+-----------+-------+ 3 rows in set (0.00 sec) mysql> create table verlag (v_id int not null auto_increment, name varchar(20), ort varchar(20), primary key(v_id)); Query OK, 0 rows affected (0.01 sec) mysql> insert into verlag values ('','bhv','frankfurt'); Query OK, 1 row affected (0.00 sec) mysql> insert into verlag values ('','m+t','berlin'); Query OK, 1 row affected (0.00 sec) mysql> select * from verlag; +------+------+-----------+ | v_id | name | ort | +------+------+-----------+ | 1 | bhv | frankfurt | | 2 | m+t | berlin | +------+------+-----------+ 2 rows in set (0.00 sec) mysql> select * from buch, verlag where buch.verlag_id = verlag.v_id; +------+-------+-----------+-------+------+------+-----------+ | b_id | titel | verlag_id | preis | v_id | name | ort | +------+-------+-----------+-------+------+------+-----------+ | 1 | mysql | 1 | 10.5 | 1 | bhv | frankfurt | | 2 | php | 1 | 10.5 | 1 | bhv | frankfurt | | 3 | win98 | 2 | 12 | 2 | m+t | berlin | +------+-------+-----------+-------+------+------+-----------+ 3 rows in set (0.00 sec) mysql> select * from buch; +------+---------+-----------+-------+ | b_id | titel | verlag_id | preis | +------+---------+-----------+-------+ | 1 | mysql | 1 | 10.5 | | 2 | php | 1 | 10.5 | | 3 | win98 | 2 | 12 | | 4 | win2000 | 5 | 20 | +------+---------+-----------+-------+ 4 rows in set (0.00 sec) mysql> select * from buch, verlag where buch.verlag_id = verlag.v_id; +------+-------+-----------+-------+------+------+-----------+ | b_id | titel | verlag_id | preis | v_id | name | ort | +------+-------+-----------+-------+------+------+-----------+ | 1 | mysql | 1 | 10.5 | 1 | bhv | frankfurt | | 2 | php | 1 | 10.5 | 1 | bhv | frankfurt | | 3 | win98 | 2 | 12 | 2 | m+t | berlin | +------+-------+-----------+-------+------+------+-----------+ 3 rows in set (0.00 sec) mysql> select * from buch left join verlag on buch.verlag_id = verlag.v_id; +------+---------+-----------+-------+------+------+-----------+ | b_id | titel | verlag_id | preis | v_id | name | ort | +------+---------+-----------+-------+------+------+-----------+ | 1 | mysql | 1 | 10.5 | 1 | bhv | frankfurt | | 2 | php | 1 | 10.5 | 1 | bhv | frankfurt | | 3 | win98 | 2 | 12 | 2 | m+t | berlin | | 4 | win2000 | 5 | 20 | NULL | NULL | NULL | +------+---------+-----------+-------+------+------+-----------+ 4 rows in set (0.00 sec) mysql> select * from buch right join verlag on buch.verlag_id = verlag.v_id; +------+-------+-----------+-------+------+------+-----------+ | b_id | titel | verlag_id | preis | v_id | name | ort | +------+-------+-----------+-------+------+------+-----------+ | 1 | mysql | 1 | 10.5 | 1 | bhv | frankfurt | | 2 | php | 1 | 10.5 | 1 | bhv | frankfurt | | 3 | win98 | 2 | 12 | 2 | m+t | berlin | +------+-------+-----------+-------+------+------+-----------+ 3 rows in set (0.00 sec) mysql> insert into verlag values ('','oreilly','london'); Query OK, 1 row affected (0.00 sec) mysql> select * from buch right join verlag on buch.verlag_id = verlag.v_id; +------+-------+-----------+-------+------+---------+-----------+ | b_id | titel | verlag_id | preis | v_id | name | ort | +------+-------+-----------+-------+------+---------+-----------+ | 1 | mysql | 1 | 10.5 | 1 | bhv | frankfurt | | 2 | php | 1 | 10.5 | 1 | bhv | frankfurt | | 3 | win98 | 2 | 12 | 2 | m+t | berlin | | NULL | NULL | NULL | NULL | 3 | oreilly | london | +------+-------+-----------+-------+------+---------+-----------+ 4 rows in set (0.00 sec) mysql> select * from buch left join verlag on buch.verlag_id = verlag.v_id; +------+---------+-----------+-------+------+------+-----------+ | b_id | titel | verlag_id | preis | v_id | name | ort | +------+---------+-----------+-------+------+------+-----------+ | 1 | mysql | 1 | 10.5 | 1 | bhv | frankfurt | | 2 | php | 1 | 10.5 | 1 | bhv | frankfurt | | 3 | win98 | 2 | 12 | 2 | m+t | berlin | | 4 | win2000 | 5 | 20 | NULL | NULL | NULL | +------+---------+-----------+-------+------+------+-----------+ 4 rows in set (0.00 sec) mysql> select * from buch, verlag where (buch.verlag_id = verlag.v_id) and verlag.name='bhv'; +------+-------+-----------+-------+------+------+-----------+ | b_id | titel | verlag_id | preis | v_id | name | ort | +------+-------+-----------+-------+------+------+-----------+ | 1 | mysql | 1 | 10.5 | 1 | bhv | frankfurt | | 2 | php | 1 | 10.5 | 1 | bhv | frankfurt | +------+-------+-----------+-------+------+------+-----------+ 2 rows in set (0.00 sec) mysql> create table autor_buch (b_id int, autor_id int); Query OK, 0 rows affected (0.00 sec) mysql> create table autor (autor_id int not null auto_increment, name varchar(20), telnummer int, primary key(autor_id)); Query OK, 0 rows affected (0.00 sec) mysql> insert into autor values ('','huber',26367), ('','muster',78456); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 2 mysql> insert into autor_buch values (1,1),(2,1),(3,2); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from buch, autor_buch, autor where (buch.b_id = autor_buch.b_id) and (autor_buch.autor_id = autor.autor_id); +------+-------+-----------+-------+----------+------+----------+----------+--------+-----------+ | b_id | titel | verlag_id | preis | autor_id | b_id | autor_id | autor_id | name | telnummer | +------+-------+-----------+-------+----------+------+----------+----------+--------+-----------+ | 1 | mysql | 1 | 10.5 | 1 | 1 | 1 | 1 | huber | 26367 | | 2 | php | 1 | 10.5 | 1 | 2 | 1 | 1 | huber | 26367 | | 3 | win98 | 2 | 12 | 1 | 3 | 2 | 2 | muster | 78456 | +------+-------+-----------+-------+----------+------+----------+----------+--------+-----------+ 3 rows in set (0.00 sec)