Bearbeiten von „Sql“

== Tipps und Tricks ==
Tabelle kopieren:
<pre>create table person_backup as select * from person;
</pre>
Zurückkopieren:
<pre>truncate person;
insert into person select * from person_backup;
</pre>

== Test-DB ==
<pre>create table region (
  rid integer primary key,
  region varchar(64)
);
create table person (
  pid integer primary key,
  name varchar(64),
  rid integer references region(rid)
  );
create table sale (
  sid integer primary key,
  pid integer references person(pid),
  price numeric(13,2),
  time date
);
insert into region values(1, 'Baden-Würtemberg');
insert into region values(2, 'Bayern');
insert into region values(3, 'Hessen');

insert into person values(1, 'Mayer', 2);
insert into person values(2, 'Huber', 2);
insert into person values(3, 'Schmidt', 3);
insert into person values(4, 'Seggele', 1);

insert into sale values(1, 1, 100, '2016-07-22'); 
insert into sale values(2, 1, 200, '2016-08-22); 
insert into sale values(3, 2, 300, '2016-09-03'); 
insert into sale values(4, 2, 400, '2016-10-09'); 
insert into sale values(5, 4, 500, '2016-11-22'); 
insert into sale values(6, 4, 600, '2016-12-02'); 

select sid, name, region, price from region r, person p, sale s 
where r.rid = p.rid and s.pid = p.pid;

select name, count(*), sum(price) 
from region r, person p, sale s 
where r.rid = p.rid and s.pid = p.pid
group by name;

Huber 2 700
Seggele 2 11000
Mayer 2 300

select name, count(*), sum(price) 
from region r, person p, sale s 
where r.rid = p.rid and s.pid = p.pid
group by name having sum(price) > 500;

Seggele  2  11000
</pre>

== MySql Datentypen ==
* tinytext (255B)
* text (64kB)
* mediumtext (16MB)
* longtext (4GB)

<pre>create table example (
   id integer primary key auto_increment;
   info text;
</pre>

== Sql-Developer ==
* Statistik aktualisieren:
** Kontextmenü auf Tabelle: "Statistiken erstellen"
* Query-Plan:
** Notepad: "select * from person where ix > 3 and ...;" markieren
** Icon "Explain plan" (F10)
@@ Zeile 69: / Zeile 69: @@
     id integer primary key auto_increment;
     info text;
-</pre>
-== Virtuelle Tabelle ==
-<pre>with temp_table as
-(select
-  geo_bezirk
- from
-  samples tt
-  join geo gg on gg.geo_id=tt.sample_position
-  join sysflags ll on gg.geo_land=ll.sysflag_id
-  group by geo_bezirk
-)
-select count(*)
-;
 </pre>