in sqlite interpreter:
.dump => dumps all inputs as SQLStatements
echo .dump | sqlite3 database.db > output.sql
.mode colum => provides nice columned output
.header on => headlines with columnnames
.tables => displays all tables
id INTEGER primary key autoincrement
.schema tablename => displays the schema of the table
INSERT:
BEGIN TRANSACTION
INSERT INTO medium VALUES (2,'3-486-27214-4','Einfuehrung in z/OS und OS/390','Basiswissen ueber z/OS und OS/390', 3);
COMMIT;
SELECTS:
select * from table_name; => displays all content of table
select column from table where id<=>value order by column asc||desc;
=> < = =< are valid matchers
order by column||title
asc = ascending
desc = descending
=> automatically increments the primary key in table
select m.titel, o.raum, o.regal, o.fach from medium m, ort o where ort_id = o.id;
=> inner join (only print values for condition)
select m.titel, o.zimmer, o.regal, o.fach from medium m left join ort o on (m.ort_id = o.id);
=> left || right join
VIEWS:
create view buchliste as select p.vname, m.titel, b.ausleihdatum from person p, medium m, besitzt_verleiht b where b.person_id = p.id and b.medium_id=m.id;
=> creates temp table and acts as an alias => select * from buchliste
UPDATE:
update person set telefon='12345' where id=0;
wildcard in sql = %
update person set telefon='123' where nname like "%string%";
ALTER:
alter table person add column alter integer;
=> alters the table per se
TRIGGER:
UPDATE, DELETE, INSERT or SELECT
call certain events as defined
i.e. delete machine => delete reservation
create trigger add_medium after insert on medium
begin
insert into besitzt_verleiht(person_id, medium_id) values(0,new.id);
end;