sql syntax cheat sheet

sqlite_master  is the master table

create table

CREATE TABLE users (id INTEGER PRIMARY KEY, name CHAR(255), email CHAR(255));

      NULL. The value is a NULL value.
      INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
      REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
      TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
      BLOB. The value is a blob of data, stored exactly as it was input.

drop table

drop table apple

select

select * from apple where name="banana" order by number desc limit 1
select distinct name from apple;

cross join
SELECT * FROM Artists, CDs;     
SELECT * FROM Artists CROSS JOIN CDs;
SELECT Artists.ArtistName, CDs.Title FROM Artists, CDs WHERE Artists.ArtistID=CDs.ArtistID;
SELECT a.ArtistName, c.Title FROM Artists a, CDs c WHERE a.ArtistID=c.ArtistID;         

inner join
 SELECT Artists.ArtistName, CDs.Title FROM Artists INNER JOIN CDs ON Artists.ArtistID=CDs.ArtistID;

left outer join
SELECT * FROM Artists LEFT OUTER JOIN CDs ON Artists.ArtistID = CDs.ArtistID;

insert

INSERT INTO users (id,name,email) VALUES (NULL,'User1','user1@domain.com');
insert into apple (name,number) values ("cherry",9);

delete

DELETE from apple WHERE name="banana";

update

UPDATE Artists SET ArtistName ='Santana' WHERE ArtistID=5;