>sqlite3
.help
.databases
.tables
.schema //描述数据库的结构
.schema TABLE_NAME
.exit / .quit
.import FILE TABLE
.read SQL_FILENAME
>sqlite3 film.db
create table stu(id,name,***,age,description); //不需要指定字段类型
create table film(title,length,year,starring);
create index film_title_index on film(title); //添加索引
insert into film values ('Silence of the Lambs, The', 118, 1991, 'Jodie Foster');
insert into film values ('Contact', 153, 1997, 'Jodie Foster');
insert into film values ('Crouching Tiger, Hidden Dragon', 120, 2000, 'Yun-Fat Chow');
insert into film values ('Hours, The', 114, 2002, 'Nicole Kidman');
>sqlite3 film.db "select * from film"
select * from film;
select * from film limit 10;
select * from film order by year limit 10;
select title, year from film order by year desc limit 10;
select * from film where starring='Jodie Foster';
select * from film where starring like 'Jodie%';
select title, year from film where starring like 'Jodie%' and year >= 1985 order by year desc limit 10;
select count(*) from film;
select count(*) from film where year >= 1985;
update film set starring='Jodie Foster' where starring='Jodee Foster';
delete from film where year < 1970;
>sqlite3 -html film.db "select * from film;" //输出 HTML 表格：
.output film.sql
.dump
.dump TABLENAME
>sqlite3 film.db ".dump" > film.sql //导出
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE stu(id,name,***,age,description);
CREATE TABLE film(title,length,year,starring);
INSERT INTO "film" VALUES('Silence of the Lambs, The',118,1991,'Jodie Foster');
INSERT INTO "film" VALUES('Contact',153,1997,'Jodie Foster');
INSERT INTO "film" VALUES('Crouching Tiger, Hidden Dragon',120,2000,'Yun-Fat Chow');
INSERT INTO "film" VALUES('Hours, The',114,2002,'Nicole Kidman');
COMMIT;
drop table film;
.read film.sql //导入
.separator | // 默认
.separator : // 修改分隔符
.mode list // 默认
.model column // 修改显示方式
.headers on // 显示表头
.width w1,w2,w3... // 设置显示宽度
.nullvalue NAN // 设置NULL值显示方式
.show // 显示当前显示设置
echo:off
explain:off
headers:on
mode:column
nullvalue:""
output:stdout
separator:"|"
stats:off
width:
配置文件.sqliterc
.mode column
.header on
.nullvalue NULL
>set
HOMEDRIVE = C:
HOMEPATH = \Documents and Settings\chenxf
USERPROFILE = C:\Documents and Settings\chenxf
可放在以上任意目录
>sqlite3 -version
可显示加载.sqliterc文件的位置
create/alter/drop table
列类型: NULL/INTEGER/REAL/TEXT/BOLB
create table testing(id integer);
.schema testing
.table
drop table testing
.schema Names
{CREATE TABLE "NamesOfFriends"(Id integer, Name text);}
alter table Names rename to NamesOfFriends; // 重命名
alter table NameOfFriends add column Email text; // 添加列
.schema NameOfFriends
{CREATE TABLE "NamesOfFriends"(Id integer, Name text, Email text);}
表达式
SELECT 3, 'Wolf', 34.5;
{3|Wolf|34.5}
.nullvalue NULL
SELECT NULL;
{NULL}
运算符
unary + - ~ NOT
||
* / %
+ -
<< <> & |
< <= > >=
= == != <> IS IN LIKE GLOB BETWEEN
AND
OR
前置运算符 - + ~ NOT
SELECT -(3-44);
{41}
算术运算符
SELECT 3*3/9;
{1}
SELECT 3 + 4 - 1 + 5;
{11}
SELECT 11 % 3;
{2}
SELECT 3+5*5, (3+5)*5;
{28|40}
逻辑运算符
SELECT 0 AND 0, 0 AND 1, 1 AND 0, 1 AND 1;
{0|0|0|1}
SELECT 0 OR 0, 0 OR 1, 1 OR 0, 1 OR 1;
{0|1|1|1}
SELECT NOT 1, NOT 0;
{0|1}
比较运算符
< <= > >= = or == != or <>
SELECT 3*3 == 9, 9 = 9;
{1|1}
SELECT 3 < 4, 3 <> 5, 4 >= 4, 5 != 5;
{1|1|1|0}
位运算符
SELECT 6 & 3;
{2}
SELECT 3 & 6;
{2}
SELECT 6 | 3;
{7}
SELECT 6 >> 1;
{3}
SELECT 6 << 1;
{12}
SELECT ~7;
{-8}
SELECT ~-8;
{7}
其他运算符 || IN LIKE GLOB BETWEEN
SELECT 'wolf' || 'hound';
{wolfhound}
SELECT 'Tom' IN ('Tom', 'Frank', 'Jane');
{1}
SELECT * FROM Cars WHERE Name IN ('Audi', 'Hummer');
{}
SELECT * FROM Cars WHERE Name LIKE 'Vol%'; // 任意字符%
{}
SELECT * FROM Cars WHERE Name LIKE '____'; // 一个字符_
{}
SELECT * FROM Cars WHERE Name GLOB '*en'; // 使用unix file glob语法, 且大小写敏感 任意字符*
{}
SELECT * FROM Cars WHERE Name GLOB '????'; // 一个字符?
{}
SELECT * FROM Cars WHERE Name GLOB '*EN'; // 大小写敏感
SELECT * FROM Cars WHERE Name LIKE '%EN'; // 大小写不敏感
{}
SELECT * FROM Cars WHERE Cost BETWEEN 20000 AND 55000;
{}
关联
SELECT 9 / 3 * 3;
{9}
SELECT 0 AND 0 OR 1;
{1}
insert/update/delete 数据
CREATE TABLE Books(id integer primary key, title text, author text, isbn text default 'not available');
INSERT INTO Books(id, title, author, isbn) VALUES(1, 'War and Peace', 'Leo Tolstoy', '978-0345472403');
INSERT INTO Books(title, author, isbn) VALUES('The Brothers Karamazov', 'Fyodor Dostoyevsky', '978-0486437910'); // 不需指定id
CREATE TEMP TABLE BooksTemp(id integer primary key, title text, author text, isbn text); // 建立临时表
INSERT INTO BooksTemp SELECT * FROM Books; // 插入数据
SELECT * FROM BooksTemp;
DELETE FROM BooksTemp WHERE id = 1;
DELETE FROM BooksTemp;
UPDATE Books SET author='Lev Nikolayevich Tolstoy' WHERE id=1;
select 数据
SELECT * FROM Cars;
SELECT Name, Cost FROM Cars; // 指定列
SELECT Name, Cost AS Price FROM Cars; // Alias
SELECT * FROM Cars LIMIT 4; // limit
SELECT * FROM Cars LIMIT 4 OFFSET 2; // limit和offset
SELECT Name, Cost FROM Cars ORDER BY Cost DESC; // order
SELECT * FROM Books WHERE id = 4; // where
SELECT * FROM Orders WHERE Customer="Smith";
SELECT * FROM Orders WHERE Customer LIKE 'B%'; // Like
SELECT Customer FROM Orders WHERE Customer LIKE 'B%';
SELECT DISTINCT Customer FROM Orders WHERE Customer LIKE 'B%'; // 取唯一值
ELECT sum(OrderPrice) AS Total, Customer FROM Orders GROUP BY Customer; // 分组
SELECT sum(OrderPrice) AS Total, Customer FROM Orders GROUP BY Customer HAVING sum(OrderPrice)>1000; // 分组过滤
NOT NULL / UNIQUE / PRIMARY KEY / FOREIGN KEY / CHECK / DEFAULT
CREATE TABLE People(Id integer, LastName text NOT NULL, FirstName text NOT NULL, City text); // 不能为NULL
CREATE TABLE Brands(Id integer, BrandName text UNIQUE); // 唯一值
CREATE TABLE Brands(Id integer PRIMARY KEY, BrandName text); // 主键 默认自动增长
CREATE TABLE Authors(AuthorId integer PRIMARY KEY, Name text); // 主键
CREATE TABLE Books(BookId integer PRIMARY KEY, title text, AuthorId integer, FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId)); //外键
CREATE TABLE Orders(Id integer PRIMARY KEY, OrderPrice integer CHECK(OrderPrice>0), Customer text); // 校验
CREATE TABLE Hotels(Id integer PRIMARY KEY, Name text, City text DEFAULT 'not available'); // 默认值
表连接
SELECT Name, Day FROM Customers AS C JOIN Reservations AS R ON C.CustomerId=R.CustomerId; // 内连接
SELECT Name, Day FROM Customers NATURAL JOIN Reservations; // 自然连接 (都有CustomerId字段)
SELECT Name, Day FROM Customers CROSS JOIN Reservations; // 交叉连接
SELECT Name, Day FROM Customers, Reservations; // 与CROSS JOIN等效
SELECT Name, Day FROM Customers LEFT JOIN Reservations ON Customers.CustomerID=Reservations.CustomerId; // 左连接
SELECT Name, Day FROM Customers LEFT JOIN Reservations USING (CustomerId); // 同上
SELECT Name, Day FROM Customers NATURAL LEFT OUTER JOIN Reservations; // 同上
核心函数
SELECT sqlite_version() AS 'SQLite Version';
{3.7.10}
SELECT random() AS Random; // between -9223372036854775808 and +9223372036854775807.
{1056892254869386643}
SELECT max(cost) FROM Cars; // 最大值
SELECT min(cost) FROM Cars; // 最小值
{}
SELECT upper(name) AS 'Names in Capitals' FROM Friends; // 大写
{}
SELECT length('ZetCode');
{7}
SELECT total_changes() AS 'Total changes'; // 返回insert,update,delete影响的行数
{}
SELECT count(*) AS '# of cars' FROM Cars; // 返回行数
{}
SELECT count(DISTINCT Customer) AS '# of customers' FROM Orders; // 统计字段唯一的行数
{}
SELECT count(id) AS '# of non NULL values' FROM Testing; // 统计非NULL的行数
{}
SELECT avg(cost) AS 'Average price' FROM Cars; // 平均值
{}
SELECT sum(OrderPrice) AS Sum FROM Orders; // 总和
{}
SELECT date('now'); // 日期
{2012-02-14}
SELECT datetime('now'); // UTC时间 非北京时间
{2012-02-14 15:34:44}
SELECT strftime('%d-%m-%Y'); // 格式化时间
{14-02-2012}
SELECT strftime('%j', 'now'); // 在一年中的天数
{45}
SELECT date('now','start of year','10 months','weekday 4'); // 指定日期
{2012-11-01}
视图
SELECT * FROM Cars;
CREATE VIEW CheapCars AS SELECT Name FROM Cars WHERE Cost < 30000;
SELECT * FROM CheapCars;
.tables
DROP VIEW CheapCars;
.tables
触发器
CREATE TABLE Log(Id integer PRIMARY KEY, OldName text, NewName text, Date text);
// 更新Friends表的Name列时触发
CREATE TRIGGER mytrigger UPDATE OF Name ON Friends
BEGIN
INSERT INTO Log(OldName, NewName, Date) VALUES(old.Name, new.Name, datetime('now'));
END;
事务 BEGIN TRANSACTION / COMMIT / ROLLBACK
BEGIN TRANSACTION;
CREATE TABLE Test(Id integer NOT NULL);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
INSERT INTO Test VALUES(3);
INSERT INTO Test VALUES(NULL);
COMMIT;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS Test(Id integer NOT NULL);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
INSERT INTO Test VALUES(3);
INSERT INTO Test VALUES(NULL);
ROLLBACK;
样例1 - movies.sql
-- SQL for the Actors table
BEGIN TRANSACTION;
CREATE TABLE Actors(AId integer primary key autoincrement, Name text);
INSERT INTO Actors VALUES(1,'Philip Seymour Hofman');
INSERT INTO Actors VALUES(2,'Kate Shindle');
INSERT INTO Actors VALUES(3,'Kelci Stephenson');
INSERT INTO Actors VALUES(4,'Al Pacino');
INSERT INTO Actors VALUES(5,'Gabrielle Anwar');
INSERT INTO Actors VALUES(6,'Patricia Arquette');
INSERT INTO Actors VALUES(7,'Gabriel Byrne');
INSERT INTO Actors VALUES(8,'Max von Sydow');
INSERT INTO Actors VALUES(9,'Ellen Burstyn');
INSERT INTO Actors VALUES(10,'Jason Miller');
COMMIT;
-- SQL for the Movies table
BEGIN TRANSACTION;
CREATE TABLE Movies(MId integer primary key autoincrement, Title text);
INSERT INTO Movies VALUES(1,'Capote');
INSERT INTO Movies VALUES(2,'Scent of a woman');
INSERT INTO Movies VALUES(3,'Stigmata');
INSERT INTO Movies VALUES(4,'Exorcist');
INSERT INTO Movies VALUES(5,'Hamsun');
COMMIT;
-- SQL for the ActorsMovies table
BEGIN TRANSACTION;
CREATE TABLE ActorsMovies(Id integer primary key autoincrement,
AId integer, MId integer);
INSERT INTO ActorsMovies VALUES(1,1,1);
INSERT INTO ActorsMovies VALUES(2,2,1);
INSERT INTO ActorsMovies VALUES(3,3,1);
INSERT INTO ActorsMovies VALUES(4,4,2);
INSERT INTO ActorsMovies VALUES(5,5,2);
INSERT INTO ActorsMovies VALUES(6,6,3);
INSERT INTO ActorsMovies VALUES(7,7,3);
INSERT INTO ActorsMovies VALUES(8,8,4);
INSERT INTO ActorsMovies VALUES(9,9,4);
INSERT INTO ActorsMovies VALUES(10,10,4);
INSERT INTO ActorsMovies VALUES(11,8,5);
COMMIT;
样例2 - test.sql
-- SQL for the Cars table
BEGIN TRANSACTION;
CREATE TABLE Cars(Id integer PRIMARY KEY, Name text, Cost integer);
INSERT INTO Cars VALUES(1,'Audi',52642);
INSERT INTO Cars VALUES(2,'Mercedes',57127);
INSERT INTO Cars VALUES(3,'Skoda',9000);
INSERT INTO Cars VALUES(4,'Volvo',29000);
INSERT INTO Cars VALUES(5,'Bentley',350000);
INSERT INTO Cars VALUES(6,'Citroen',21000);
INSERT INTO Cars VALUES(7,'Hummer',41400);
INSERT INTO Cars VALUES(8,'Volkswagen',21600);
COMMIT;
-- SQL for the orders table
BEGIN TRANSACTION;
CREATE TABLE Orders(Id integer PRIMARY KEY, OrderPrice integer CHECK(OrderPrice>0),
Customer text);
INSERT INTO Orders(OrderPrice, Customer) VALUES(1200, "Williamson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(200, "Robertson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(40, "Robertson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(1640, "Smith");
INSERT INTO Orders(OrderPrice, Customer) VALUES(100, "Robertson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(50, "Williamson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(150, "Smith");
INSERT INTO Orders(OrderPrice, Customer) VALUES(250, "Smith");
INSERT INTO Orders(OrderPrice, Customer) VALUES(840, "Brown");
INSERT INTO Orders(OrderPrice, Customer) VALUES(440, "Black");
INSERT INTO Orders(OrderPrice, Customer) VALUES(20, "Brown");
COMMIT;
-- SQL for the Friends table
BEGIN TRANSACTION;
CREATE TABLE Friends(Id integer PRIMARY KEY, Name text UNIQUE NOT NULL,
*** text CHECK(*** IN ('M', 'F')));
INSERT INTO Friends VALUES(1,'Jane', 'F');
INSERT INTO Friends VALUES(2,'Thomas', 'M');
INSERT INTO Friends VALUES(3,'Franklin', 'M');
INSERT INTO Friends VALUES(4,'Elisabeth', 'F');
INSERT INTO Friends VALUES(5,'Mary', 'F');
INSERT INTO Friends VALUES(6,'Lucy', 'F');
INSERT INTO Friends VALUES(7,'Jack', 'M');
COMMIT;
-- SQL for the Customers, Reservations tables
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS Customers(CustomerId integer PRIMARY KEY, Name text);
INSERT INTO Customers(Name) VALUES('Paul Novak');
INSERT INTO Customers(Name) VALUES('Terry Neils');
INSERT INTO Customers(Name) VALUES('Jack Fonda');
INSERT INTO Customers(Name) VALUES('Tom Willis');
CREATE TABLE IF NOT EXISTS Reservations(Id integer PRIMARY KEY,
CustomerId integer, Day text);
INSERT INTO Reservations(CustomerId, Day) VALUES(1, '2009-22-11');
INSERT INTO Reservations(CustomerId, Day) VALUES(2, '2009-28-11');
INSERT INTO Reservations(CustomerId, Day) VALUES(2, '2009-29-11');
INSERT INTO Reservations(CustomerId, Day) VALUES(1, '2009-29-11');
INSERT INTO Reservations(CustomerId, Day) VALUES(3, '2009-02-12');
COMMIT;
-- SQL for the Names table
BEGIN TRANSACTION;
CREATE TABLE Names(Id integer, Name text);
INSERT INTO Names VALUES(1,'Tom');
INSERT INTO Names VALUES(2,'Lucy');
INSERT INTO Names VALUES(3,'Frank');
INSERT INTO Names VALUES(4,'Jane');
INSERT INTO Names VALUES(5,'Robert');
COMMIT;
-- SQL for the Books table
BEGIN TRANSACTION;
CREATE TABLE Books(Id integer PRIMARY KEY, Title text, Author text,
Isbn text default 'not available');
INSERT INTO Books VALUES(1,'War and Peace','Leo Tolstoy','978-0345472403');
INSERT INTO Books VALUES(2,'The Brothers Karamazov',
'Fyodor Dostoyevsky','978-0486437910');
INSERT INTO Books VALUES(3,'Crime and Punishment',
'Fyodor Dostoyevsky','978-1840224306');
COMMIT;
