sqlite3 简明指南

sqlite3 是运行SQL语言的小型数据库软件,有着广泛的应用。本文是学习笔记，原文请参考 http://zetcode.com/db/sqlite/

--下载: https://www.sqlite.org/download.html

--开启数据库, 在terminal输入:

sqlite3 test.db

--开启帮助
.help

--读取 sql脚本
.read friends.sql

--查询有什么表格
.TABLE s

--显示一个表格的全部内容
SELECT * FROM Friends;

--设定各列之间的间隔
.separator

--不同的显示模式
.mode column
.headers on

--选出部分列
SELECT Name, Title FROM Authors NATURAL JOIN Books;

--显示不同列时的宽度
.width 1518

--显示打印的属性
.show

--显示表格的属性
.schema Cars

--更改 调用命令的前缀
.prompt "> "". "

--从 Terminal 运行 sqlite
sqlite3 test.db "SELECT * FROM Cars;"

--导出数据
--1. 指名文件
.output cars2.sql
--2. 导出sql脚本
.dump Cars

--.sqlite_history 文件
位置 /home/jinlong
tail -5~/.sqlite_history

--.sqliterc 文件, 内容为纯文本,可以保存sqlite的配置命令, 位置
/home/jinlong

每次sqlite3启动时,会自动加载其中的sqlite命令

--显示帮助
sqlite3 --help

--进入sqlite3时，设定相应的参数
sqlite3 -echo -line -noheader test.db

--显示版本
sqlite3 -version

--以html的格式显示
sqlite3 -html test.db

----#####################
---数据库的创建
CREATE 
--任何一个field,有如下几种类型

--NULL — The value is a NULL value
--INTEGER — a signed integer
--REAL — a floating point value
--TEXT — a text string
--BLOB — a blob of data

---创建一个名为 Testing的表, 包含Id一列, 为整数类型
CREATE TABLE Testing(Id INTEGER);

-- 查看创建Testing的代码
.schema Testing

-- 若Tesing已经存在, 则再次创建会出错
CREATE TABLE Testing(Id INTEGER);

-- 为了减少这种错误, 可以用 IF NOT EXISTS 检查.
CREATE TABLE IF NOT EXISTS Testing(Id INTEGER);

-- 从其他数据生成表格
CREATE TABLE Cars2 AS SELECT * FROM Cars;

--查看现有数据库名
.databases

--有多个数据库时,需要在命令中指定要更改的数据库

ATTACH DATABASE 'test2.db' AS 'test2'; -- ### 可先在sqlite中更改数据库的名称
CREATE TABLE test2.Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);
INSERT INTO test2.Cars VALUES(1,'Porsche',107699);
SELECT * FROM main.Cars WHERE Id=1;

-- 临时数据库, 其中的数据表不会显示. 临时数据库的名称为 temp

CREATE TEMPORARY TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);
INSERT INTO temp.Cars VALUES(1,'Kia',24300);
.databases

--删除表格
DROP 

--查看表格
.TABLE s
DROP TABLE Testing;
--如果 Testing表格不存在, 而用DROP则会出错.

--为了排除这种错误, 一般改为
DROP TABLE IF EXISTS Testing;

--删除某一个数据库中的数据表
DROP TABLE IF EXISTS test2.Cars;

--###########################################
-- ALTER
-- 对表格的更改

CREATE TABLE Names(Id INTEGER, NameTEXT);

--表格改名
ALTER TABLE Names RENAME TO NamesOfFriends;
.schema NamesOfFriends

--为表格中增加一列
ALTER TABLE NamesOfFriends ADD COLUMN Email TEXT;
.schema NamesOfFriends

---########################################
--- sqlite语法规则

SELECT 3,'Wolf',34.5;
.null value NULL

SELECT NULL;

---二进制大型物件 Binary Large Object (BLOB)
SELECT x'345eda2348587aeb';

---###### sqlite中的 运算符
--- Arithmetic operators
--- Boolean operators
--- Relational operators
--- Bitwise operators
--- Other operators

--- ||
--- * / %
--- + -
--- << >> & |
--- < <= > >=
--- = == != <> IS IN LIKE GLOB BETWEEN
--- AND 
--- OR

--取反字符
SELECT -(3-44);
--- unary prefixoperators:
--- - + ~ NOT

--- 数值运算
SELECT 3 * 3/9;
SELECT 3 + 4 - 1 + 5;
SELECT 11 % 3;

---逻辑运算符
SELECT 1 AND 1, 0 AND 1, 1 AND 0, 0 AND 0 ;
SELECT 3=3 AND 4=4;
SELECT 0 OR 1,1 OR 0, 1 OR 1,0 OR 1;
SELECT NOT 1, NOT 0;
SELECT NOT (3=3);


--- 判断
--- Symbol Meaning
--- < strictly less than
--- <= less than or equal to
--- > greater than
--- >= greater than or equal to
--- = or == equal to
--- != or <> not equal to

SELECT 3 * 3==9, 9=9;
SELECT 3 <4, 3<>5, 4>=4, 5!=5;

---位运算, 针对二进制的运算

--- bitwise and operator &,

SELECT 6 & 3;
SELECT 3 & 6;

--- bitwise or operator|

--- bitwise sh IF t << >>

--- 取反
--- bitwise negation operator
SELECT ~7;

--- 其他运算符
--- ||字符串连接符
--- IN判断某个字符是否在一个向量中

--例如 
SELECT 'Tom' IN ('Tom','Frank','Jane');
---例如
SELECT * FROM Cars WHERE Name IN('Audi','Hummer');

---LIKE, 配合 WHERE , 用正则表达式筛选
SELECT * FROM Cars WHERE Name LIKE'Vol%';

--- 特定字符数的字符筛选
SELECT * FROM Cars WHERE Name LIKE'____';

--- GLOB, 类似于 Like, 但是区分大小写, 同时识别UNIX字符
SELECT * FROM Cars WHERE Name GLOB ' * en';
SELECT * FROM Cars WHERE Name GLOB '????';---四个字符
AND
BETWEEN
SELECT * FROM Cars WHERE Price BETWEEN 20000 AND 55000;

--- 运算符的优先级
--- unary + - ~ NOT
--- ||
--- * / %
--- + -
--- << <> & |
--- < <= > >=
--- = == != <> IS IN LIKE GLOB BETWEEN
--- AND 
--- OR

---同一级别运算符的顺序-
---从左至右
SELECT 9/3 * 3;
---- 从左至右的运算符包括
---- Arithmetic, boolean, relational, and bitwise operators are all left to right associated.


--- 数据的插入, 更新和删除
DROP TABLE IF EXISTS Cars;
CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER DEFAULT 'Not available');
INSERT INTO Cars(Id, Name, Price) VALUES (1,'Audi',52642);
INSERT INTO Cars(Name, Price) VALUES ('Mercedes',57127);
SELECT * FROM Cars;
--- ### 设定为 INTEGER PRIMARY KEY 之后, 插入数据时, 可以忽略, 数据库会为新纪录自动增加一个Id

---若未能在INSERT 时给出各列名称, 则需要按照长度提供所有值
INSERT INTO Cars VALUES (3, 'Skoda', 9000);

--- 显示 NULL as NULL
.nullvalue NULL

SELECT * FROM Cars WHERE Id=4;

--- 对于已经有primary key条目的插入
INSERT INTO Cars VALUES(4,'Volvo',29000);
INSERT OR REPLACE INTO Cars VALUES(4,'Volvo',29000);
SELECT * FROM Cars WHERE Id=4;

--- 如果插入数据不成功， 会提示fail
INSERT OR FAIL INTO Cars VALUES(4,'Bentley',350000);

--- 不会提示任何信息
INSERT OR IGNORE INTO Cars VALUES(4,'Bentley',350000);

--- sqlite 3.7.11 之后, 可以同时插入多行
CREATE TABLE Ints(Id INTEGER PRIMARY KEY, Val INTEGER);

INSERT INTO Ints(Val) VALUES(1),(3),(5),(6),(7),(8),(6),(4),(9);
CREATE TABLE Cars2(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);
INSERT INTO Cars2 SELECT * FROM Cars;

---############################################
--- ####### DELETE 删除部分数据

DELETE FROM Cars2 WHERE Id=1; 
SELECT * FROM Cars2; -- 删除Cars2中的全部内容
DELETE FROM Cars2;

SELECT Count(Id)AS'# of cars'FROM Cars2;
.read cars.sql
SELECT * FROM Cars;
DELETE FROM Cars LIMIT5;
SELECT * FROM Cars;

----############################################
---- ####### Update,按照条件对数据进行修改
.read cars.sql
UPDATE Cars SET Name='Skoda Octavia' WHERE Id=3;
SELECT * FROM Cars WHERE Id=3;

----###########################################
---- #### SELECT 的使用
---- ### 显示全部表格
SELECT * FROM Cars;

----### 选择特定的列
SELECT Name, Price FROM Cars;

---- ### 查询结果, 但是改查询结果的名
SELECT Name, Price AS 'Price of car'FROM Cars;

---- ### 显示部分数据
SELECT * FROM Cars LIMIT 4;

SELECT * FROM Cars LIMIT 2, 4; --- 选择显示四行,但是不要查看前两行

SELECT * FROM Cars LIMIT 4 OFFSET 2; --- Offset 忽略前两行,但是显示四行

----##############################################
----### 输出数据的排序

SELECT * FROM Cars ORDER BY Price;
SELECT Name, Price FROM Cars ORDER BY Price DESC;

----##############################################
---和 WHERE 搭配使用

SELECT * FROM Orders WHERE Id=6;
SELECT * FROM Orders WHERE Customer = "Smith";
SELECT * FROM Orders WHERE Customer LIKE 'B%';

#################################################
--- 检查重复记录
SELECT Customer FROM Orders WHERE Customer LIKE 'B%';

--- 去除重复
SELECT DISTINCT Customer FROM Orders WHERE Customer LIKE 'B%';

----#####################################################
----数据分组

SELECT 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);

INSERT INTO People VALUES(1,'Hanks', 'Robert', 'New York');
INSERT INTO People VALUES(2, NULL, 'Marianne', 'Chicago');

----##########################################################
CREATE TABLE Brands(Id INTEGER, BrandName TEXT UNIQUE);
----#### 不符合条件时, 要报错
INSERT INTO Brands VALUES(1,'Coca Cola');
INSERT INTO Brands VALUES(2,'Pepsi');
INSERT INTO Brands VALUES(3,'Pepsi');

---PRIMARY KEY 限制条件, PRIMARY KEY只能指定一个, 该列所有值为唯一的, 并且顺序增加. primary key是其他表格引用该表格的基础.
DROP TABLE Brands;
CREATE TABLE Brands(Id INTEGER PRIMARY KEY,BrandName TEXT);
INSERT INTO Brands(BrandName)VALUES('CocaCola');
INSERT INTO Brands(BrandName)VALUES('Pepsi');
INSERT INTO Brands(BrandName)VALUES('Sun');
INSERT INTO Brands(BrandName)VALUES('Oracle');
SELECT * FROM Brands;

----引用其他表中的id
FOREIGN KEY(AuthorId) REFERENCES

BEGIN TRANSACTION;
DROP TABLE IF EXISTS Books;
DROP TABLE IF EXISTS Authors;

CREATE TABLE Authors(AuthorId INTEGER PRIMARY KEY, Name TEXT);
INSERT INTO Authors VALUES(1,'Jane Austen');
INSERT INTO Authors VALUES(2,'Leo Tolstoy');
INSERT INTO Authors VALUES(3,'Joseph Heller');
INSERT INTO Authors VALUES(4,'Charles Dickens');

CREATE TABLE Books(BookId INTEGER PRIMARY KEY, TitleTEXT, AuthorId INTEGER, FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId));
INSERT INTO Books VALUES(1,'Emma',1);
INSERT INTO Books VALUES(2,'War and Peace',2);
INSERT INTO Books VALUES(3,'Catch XII',3);
INSERT INTO Books VALUES(4,'David Copperfield',4);
INSERT INTO Books VALUES(5,'Good as Gold',3);
INSERT INTO Books VALUES(6,'Anna Karenia',2);
COMMIT;

---将两个表格的id绑定(其中一个引用另外一个),则被引用表格中的数据不能直接删除
PRAGMA foreign_keys=1;
DELETE FROM Authors WHERE AuthorId=1;

CREATE TABLE Books(BookId INTEGER PRIMARY KEY, TitleTEXT, AuthorId INTEGER, FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId)ON DELETE CASCADE);

SELECT Name, Title FROM Authors NATURAL JOIN Books;

DELETE FROM Authors WHERE AuthorId=2;
SELECT Name, Title FROM Authors NATURAL JOIN Books;

.schema Orders --- 查看表格头以及各列的名称和数据类型

INSERT INTO Orders(OrderPrice, Customer) VALUES (-10,'Johnson');

---默认值关键字 DEFAULT
CREATE TABLE Hotels(Id INTEGER PRIMARY KEY, NameTEXT, City TEXT DEFAULT 'not available');

INSERT INTO Hotels(Name) VALUES ('Slovan');

----###############################################
----两个表格的合并

----三种类型的合INNER 并
-- (1) INNER JOIN,
-- (2) NATURAL INNER JOIN
-- (3) CROSS INNER JOIN

---(1)INNER JOIN,
SELECT Name, Day FROM Customers AS C JOIN Reservations AS R ON C.CustomerId = R.CustomerId;
--- 注意AS XX 是为数据表临时改名, 以方便引用

---以上句子可以用SELECT 实现相同的效果
SELECT Name, Day FROM Customers, Reservations WHERE Customers.CustomerId = Reservations.CustomerId;

---(2)NATURAL INNERJOIN
--- NATURALJOIN自动以两个表中相同的列名进行匹配
SELECT Name, Day FROM Customers NATURAL JOIN Reservations;

---(3)CROSS INNER JOIN 是将表1和表2所有项进行匹配,并无实际应用价值.

--- OUTER JOINS,也分为三种 (left outer joins,right outer joins,and full outer joins),但是SQLite只支持第一种 left outer joins
--- OUTER JOINS 查询左侧表格的所有内容,即便在右侧表格没有值,也将以NULL的形式给出

SELECT Name, Day FROM Customers LEFT JOIN Reservations ON Customers.CustomerId = Reservations.CustomerId;
---另一种方法选择
SELECT Name, Day FROM Customers LEFT JOIN Reservations USING(CustomerId);

----自动匹配另一个表格
NATURALLEFTOUTERJOIN
SELECT Name, Day FROM Customers NATURAL LEFT OUTER JOIN Reservations;

----#############################################################
----SQLite中的函数

----第一类 核心函数
SELECT sqlite_version()
SELECT random() AS Random;
SELECT abs(11),abs(-5),abs(0),abs(NULL);
SELECT max(Price),min(Price) FROM Cars;
SELECT upper (Name) AS 'Names in capitals' FROM Friends;
SELECT lower (Name) AS 'Names in lowercase' FROM Friends WHERE Id IN(1,2,3);
SELECT length('ZetCode');
SELECT total_changes() AS 'Total changes'; --- 数据库连接以来INSERT ,UPDATE,orDELETE的次数
SELECT sqlite_compileoption_used('SQLITE_DEFAULT_FOREIGN_KEYS') AS 'FK'; ---查看某选项的状态
SELECT typeof(12), typeof('ZetCode'), typeof(33.2), typeof(NULL), typeof(x'345edb');

----第二类 聚合函数
SELECT * FROM Cars;
SELECT count( * ) AS '# of cars' FROM Cars;--- 返回行数
SELECT count(Customer) AS '# of orders' FROM Orders;
SELECT count(DISTINCT Customer) AS '# of customers' FROM Orders;

----让sqlite显示NULL
.nullvalue NULL
CREATE TABLE TESTING(Id INTEGER);
INSERT INTO Testing VALUES(1),(2),(3),(NULL),(NULL);
SELECT last_insert_rowid();

--- count( * )和 count(Id)的区别,count( * )考虑NULL,而后者全部去掉NULL
SELECT count(*) AS '# of rows' FROM Testing;
SELECT count(Id) AS '# of non NULL values' FROM Testing;

---平均值
SELECT avg(Price) AS 'Average price' FROM Cars;

---求和
SELECT sum(OrderPrice) AS Sum FROM Orders;

----第三类 日期和时间函数
SELECT date('now');
SELECT datetime('now');
SELECT time('now');
SELECT time();
SELECT date();
SELECT date('now','2 months');
SELECT date('now','-55 days');
SELECT date('now','start of year');
SELECT datetime('now','start of day');
SELECT date('now','weekday 6'); --- 其中 Sunday is0, Monday 1,..., Saturday 6
SELECT date('now','start of year','10 months','weekday 4');
SELECT strftime('%d-%m-%Y');
SELECT 'Current day: '|| strftime('%d');
SELECT 'Days to XMas: '||(strftime('%j','2015-12-24')- strftime('%j','now'));

---- ########### 视图 VIEWS #################

SELECT * FROM Cars;
CREATE VIEW CheapCars AS SELECT Name FROM Cars WHERE Price <30000;
SELECT * FROM CheapCars;
.TABLE s
DROP VIEW CheapCars;
.TABLE s

--VIEW是一种虚拟表格
--记录数据操作
-- Triggers 记录

CREATE TABLE Log(Id INTEGER PRIMARY KEY, OldName TEXT, NewName TEXT,Date TEXT);

CREATE TRIGGER mytrigger UPDATE OF Name ON Friends

BEGIN
INSERT INTO Log(OldName, NewName,Date) VALUES(old.Name,new.Name, datetime('now'));
END;

----查看 trigger
SELECT name, tbl_name FROM sqlite_master WHERE type='trigger';

SELECT * FROM Friends;
UPDATE FriendsSET Name='Frank' WHERE Id=3;
SELECT * FROMLog;

SELECT name, tbl_name FROM sqlite_master WHERE type='trigger';

----SQLite命令组

----两种, 分别以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(11);
INSERT INTO Test VALUES(12);
INSERT INTO Test VALUES(13);
INSERT INTO Test VALUES(NULL);
ROLLBACK;

--- ############### 导入和导出
.mode --查询模式

--- 导入CSV文件 .IMPORT
.import --- 文件名\表

---### 需要设定 字段的分隔符。
.mode CSV
.import C:/work/mydat.csv TABLE 1

--- 导出 .output
.head on
.mode csv
.output out_Cars.csv
SELECT * FROM Cars;
.output stdout

--- 导出为html格式
.header on
.separator ","
.mode html
.output out_Cars.html
SELECT * FROM Cars;
.output stdout