insert into Student (firstName, lastName) values ('Lin', 'Chen');
insert into Student (firstName, lastName) values ('Yanhua', 'Feng');
select * from sqlite_master, sqlite_master table saves all table information in a database
select (...) from tableName where condition, select records from a table
select firstName, lastName from Student where id == 1;
display
- .mode column, display by columns
- .header on, display headers
.width w1, w2, setting output column width
update tableName set columnName = value where, update record
update Student
set firstName = 'Hannah'
where id == 1;
delete from tableName where, remove a record
delete from Student where id == 1;
LIKE, case insensitve
- %, zero, one or multiple character
- _, one character
GLOB, case sensitive
- *, zero, one, or multiple characters
- ?, one character
select (...) from tableName limit number offset n, limit the number of display records and figure out the start record
SELECT * FROM COMPANY LIMIT 2 OFFSET 2;
order by [ASC|DESC], sort records
group by columnName, group records
having, set up conditions
distinct, eliminate all duplicate records
Constraints
- Not Null, ensures that a column cannot have NULL value
- Default, provides a default value for a column when none is specified
- Unique, ensures that all values in a column are different
- Primary Key, uniquely identifies
- Check, ensures that all values in a column satisfies certain conditions
- Not allowed to add or remove contraints from a table
create table Info(
id int autoincrement,
name text,
age int,
constraint c1 primary key (id, name),
constraint c2 check (age > 20),
constraint c3 foreign key (id) references Team(id));
Insert from a sql file
- sqlite3 temp.db < info.sql
BEGIN TRANSACTION;
insert into Info values (2, 'Yanhua', 37);
COMMIT;
Trigger
- SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers
- Use references of the form NEW.column-name and OLD.column-name
- Reference tables by tablename not database.tablename
- Function RAISE() may be used within a trigger-program to raise an exception
- drop trigger triggerName, drop trigger
create trigger teamInfo after insert on Info
begin
insert into Team values (new.id, new.name);
end;
Index
- create index indexName on tableName (column1, column2, ...), create an index
- create unique on tableName (column1), create a unique
- Indexes are automatically created for primary key constraints and unique constraints
- .index tableName, list down all the indices available of a table
- drop index indexName, drop an index
create index nameIndex on Student (lastName);
select * from Student indexed by nameIndex where lastName > '';
Alter table
alter table Student add column age INTEGER;
update Student
set age = 38;
where ID = 3;
Delete, delete rows from a table
delete
from Student
where ID = 4;
View, sqlite view is read-only
create view nameView as
select firstName, lastName
from Student
Transaction control
begin transaction;
delete from Student where ID = 2;
rollback;
Explain, is intended for interactive analysis and troubleshooting only
Vacuum, eliminates free pages, aligns table data to be contiguous
Limitations
- Only LEFT OUTER JOIN is implemented
- DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are not supported
- FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers
- VIEWs in SQLite are read-only
- GRANT and REVOKE are not needed, use the access permissions of underlying operating system