Sql

Tipps und Tricks

Tabelle kopieren:

create table person_backup as select * from person;

Zurückkopieren:

truncate person;
insert into person select * from person_backup;

Test-DB

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

MySql Datentypen

• tinytext (255B)
• text (64kB)
• mediumtext (16MB)
• longtext (4GB)

create table example (
   id integer primary key auto_increment;
   info text;

Virtuelle Tabelle

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(*)
;

Sql-Developer

• Statistik aktualisieren:
  • Kontextmenü auf Tabelle: "Statistiken erstellen"
• Query-Plan:
  • Notepad: "select * from person where ix > 3 and ...;" markieren
  • Icon "Explain plan" (F10)