CREATE
cd C:\sqlite
.shell cd
sqlite3 [Link]
.tables
CREATE TABLE STORE(STOREID INT, ITEMID INT, ITEM_NAME TEXT);
INSERT INTO STORE(STOREID, ITEMID, ITEM_NAME) VALUES (1,11,'Pencil');
INSERT INTO STORE VALUES (2,22,'Pen');
SELECT * FROM STORE;
SAVE
.save STORE
.save mydb
Quit
OPEN
cd C:\sqlite
.open [Link]
sqlite3 [Link]
FILTER
SELECT * FROM STORE WHERE STOREID = 1;
.header on
SELECT * FROM STORE WHERE item_name = 'pencil';es
AND , OR
SELECT * FROM STORE WHERE ITEM_NAME = 'pen' and ITEM_NAME = 'Pencil';
SELECT * FROM STORE WHERE ITEM_NAME = 'pen' and ITEM_ID= 22;
SELECT * FROM STORE WHERE ITEM_NAME = 'pen' or STOREID = 2;
INSERT INTO STORE VALUES(3,44,'Book');
INSERT INTO STORE VALUES(5,67,'NoteBook');
SELECT * FROM STORE;
INSERT INTO STORE VALUES(1,44,'Book');
SELECT * FROM STORE;
GROUPING
SELECT * FROM STORE group by ITEMID;
CREATE TABLE INVOICE(INVOICEID INTEGER, INVOICENAME TEXT, INVOICEAMT INT);
INSERT INTO INVOICE VALUES(10,'WEST',100);
INSERT INTO INVOICE VALUES(10,'EAST',1500);
INSERT INTO INVOICE VALUES(10,'NORTH',13500);
INSERT INTO INVOICE VALUES(30,'NORTHEAST',16500);
INSERT INTO INVOICE VALUES(20,'WESTEAST',1700);
INSERT INTO INVOICE VALUES(50,'WESTNORTH',6500);
INSERT INTO INVOICE VALUES(50,'SOUTH',7500);
SELECT * FROM INVOICE;
SELECT INVOICEID, COUNT(INVOICEID) FROM INVOICE GROUP BY INVOICEID;
SORTING THE RECORDS
SELECT * FROM INVOICE ORDER BY INVOICEAMT;
SELECT * FROM INVOICE ORDER BY INVOICEAMT DESC;
BETWEEN AND (IN) OPERATOR
SELECT * FROM INVOICE WHERE INVOICEAMT BETWEEN 1700 AND 6500;
SELECT INVOICEID FROM INVOICE WHERE INVOICEID IN(10,50);
SELECT INVOICEID FROM INVOICE WHERE INVOICEID=10 OR INVOICEID=50;
UPDATE THE RECORDS
UPDATE INVOICE SET INVOICENAME = 'TEST' WHERE INVOICEID = 10 AND INVOICEAMT = 100;
STRUCTURE OF THE TABLE
.schema
.schema INVOICE
DATE FUNCTIONS
SELECT DATE();
SELECT TIME();
SELECT DATETIME();
PRIMARY KEY CONSTRAINTS
CREATE TABLE STUDENT(SID INTEGER PRIMARY KEY, SNAME TEXT,SPHONE TEXT, SEMAIL TEXT);
INSERT INTO STUDENT VALUES(1, 'TOM','+5464845475','[Link]');
INSERT INTO STUDENT VALUES(1, 'TOM','+5464845475','TEST1@[Link]');
INSERT INTO STUDENT VALUES(2, 'TOM','+5464845475','TEST1@[Link]');
NOT NULL CONSTRAINTS
INSERT INTO STUDENT VALUES(4, '','+5464845475','TEST1@[Link]');
CREATE TABLE STUDENTNOTNULL(SID INT, SNAME TEXT);
CREATE TABLE STUDENTNOTNULL1(SID INT PRIMARY KEY, SNAME TEXT NOT NULL);
INSERT INTO STUDENTNOTNULL1 VALUES(1,'SAM');
INSERT INTO STUDENTNOTNULL1 VALUES(2,'');
SELECT * FROM STUDENTNOTNULL;
SELECT * FROM STUDENTNOTNULL1;
INSERT INTO STUDENTNOTNULL1 (SID) VALUES(3);
INSERT INTO STUDENTNOTNULL1 (SID,SNAME) VALUES(3,'Michael');
UNIQUE CONSTRAINTS
create table t1(tid int unique not null, tname text);
insert into t1 values(1,'Hello');
insert into t1 values(2,'Hi');
DEFAULT CONSTRAINTS
create table t2(tid int unique not null, tname text default 'unknown');
insert into t2(tid) values(2);
select * from t2;
CHECK CONSTRAINTS
create table t3(tid int unique not null, tamt real check(tamt > 900));
insert into t3 values(1,950);
select * from t3;
insert into t3 values(2,901);
ALTER TABLE STRUCTUREr
alter table t3 add tname text;
select * from t3;
insert into t3 values(3,990.0,'ikikik');
alter table t3 rename tname to tname1;
alter table t3 rename to t3new;
select * from t3;
DELETE AND DROP
drop table t1;
select * from t1;
select * from t3new;
delete from t3new where tid =2;
select * from t3new;
select * from t2;
insert into t2 values(3,'dyyjg');
insert into t2 values(3,'vhjjh');
insert into t2 values(4,'vhjjh');
insert into t2 values(9,'vhjjh');
select * from t2;
delete from t2;
BACKUP AND RESTORE
.backup mydb_bkp.db
.tables
drop table t2;
.schema t2
select * from t2;
.restore mydb_bkp.db
.tables
select * from invoice;
VARIOUS MODES IN SQLITE
.mode csv
select * from invoice;
.mode insert
select * from invoice;
.mode tab
select * from invoice;
.mode column
select * from invoice;
.mode list
select * from invoice;
.save [Link]
.q
.save [Link]