database and sql [sqlite]

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;

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>