sqlite 数据库 相关知识
|
一基本简介 SQLite 数据库引擎实现了主要的 SQL-92 标准，引擎本身只有一个文件，大小不到 300k ，但是并不作为一个独立的进程运行，而是动态或者静态的链接到其他应用程序中。它生成的数据库文件是一个普通的磁盘文件，可以放置在任何目录下。SQLite 本身是 C 语言开发的，开源也跨平台，并且被所有的主流编程语言支持， SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其他数据库一样，您不需要在系统中配置。 就像其他数据库，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SQLite 直接访问其存储文件。 SQLite 常用约束如下
二、主键 PRIMARY KEY我们还是进入 SQLite 命令行环境，建立一个 test.db 数据库用来做实验，如下 myqiao@ubuntu:~/My Documents/db$ sqlite3 test.db -- Loading resources from /home/myqiao/.sqliterc SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .tables sqlite> 运行 .tables 命令没有返回，说明数据库是空的。如果你的数据库里面有内容并影响到下面的实验， 你可以用我们上一篇学的 DROP TABLE 来删除造成影响的表， 或者用 ALTER TABLE ... RENAME TO ... 来改名。 下面言归正转，我们来说说主键 PRIMARY KEY 。
下面我们来做实验 sqlite> sqlite> CREATE TABLE Teachers(Id integer PRIMARY KEY,Name text); sqlite> .tables Teachers sqlite> INSERT INTO Teachers(Name) Values('张三'); sqlite> INSERT INTO Teachers(Name) Values('李四'); sqlite> INSERT INTO Teachers(Name) Values('王二麻子'); sqlite> SELECT * FROM Teachers; Id Name ---------- ---------- 1 张三 2 李四 3 王二麻 sqlite> INSERT INTO Teachers(Id,Name) Values(2,'孙悟空'); Error: PRIMARY KEY must be unique sqlite> 我们先新建了一个 Teachers 表，并设置了两个字段列，其中 Id 字段列为主键列。 然后，我们向其中插入三条数据并查询，反馈一切正常。 注意：在插入前三条数据的时候，命令中并没有明确指明 Id 的值，系统自动赋值，并且数值自动增长。 插入第四条数据的时候，我给了一个明确的 Id 编号为 2，因为李四的编号已经是 2 了， 所以系统提示我错误：主键必须唯一。 三、默认值 DEFAULT有一些特别的字段列，在每一条记录中，他的值基本上都是一样的。只是在个别情况下才改为别的值，这样的字段列我们可以给他设一个默认值。 下面我们来做实验 sqlite> sqlite> DROP TABLE Teachers; sqlite> .tables sqlite> sqlite> CREATE TABLE Teachers(Id integer PRIMARY KEY,Name text,Country text DEFAULT '中国'); sqlite> .tables Teachers sqlite> INSERT INTO Teachers(Name) Values('张三'); sqlite> INSERT INTO Teachers(Name) Values('李四'); sqlite> INSERT INTO Teachers(Name) Values('王二麻子'); sqlite> INSERT INTO Teachers(Name,Country) Values('孙悟空','天庭'); sqlite> SELECT * FROM Teachers; Id Name Country ---- --------------- --------------- 1 张三 中国 2 李四 中国 3 王二麻子 中国 4 孙悟空 天庭 sqlite> 先把之前的 Teachers 表删除，然后重新创建。这回 Teachers 表多了一个 Country 字段， 并且设置默认值为“中国”，然后我们插入四条数据到 Teachers 表。 前三条数据都没有明确指明 Country 字段的值，只有第四条数据指明了“孙悟空”的 Country 为“天庭”。 查询数据，发现前三条数据都填上了默认值，实验成功。 数据显示有点走样，命令 .width 4 15 15 设置的列宽，可以通过 .show 查看， 可能是因为中文的原因，所以没有对齐。 四、非空 NOT NULL有一些字段我们可能一时不知到该填些什么，同时它也没设定默认值， 当添加数据时，我们把这样的字段空着不填，系统认为他是 NULL 值。 但是还有另外一类字段，必须被填上数据，如果不填，系统就会报错。 这样的字段被称为 NOT NULL 非空字段,需要在定义表的时候事先声明。 下面我们来做实验 sqlite> sqlite> DROP TABLE Teachers; sqlite> .tables sqlite> sqlite> CREATE TABLE Teachers(Id integer PRIMARY KEY,Age integer NOT NULL,City text); sqlite> .tables Teachers sqlite> INSERT INTO Teachers(Name,Age) Values('Alice',23); sqlite> INSERT INTO Teachers(Name,Age) Values('Bob',29); sqlite> INSERT INTO Teachers(id,Name,Age) Values(6,'Jhon',36); sqlite> SELECT * FROM Teachers; Id Name Age City ---- --------------- --------------- --------------- 1 Alice 23 NULL 2 Bob 29 NULL 6 Jhon 36 NULL sqlite> INSERT INTO Teachers(Name) Values('Mary'); Error: Teachers.Age may not be NULL sqlite> 还是先删除旧表，创建新表。 这回 Teachers 表声明了一个 NOT NULL 字段 Age，同时还有一个可以为 NULL 的字段 City 插入前三条数据都没有指定 City 的值，查询可以看到 City 字段全部为空 注意：这里的 NULL 只是对“什么都没有”的一种显示形式， 可以通过 .nullvalue 命令改为别的形式，具体见第一篇 插入第四条数据时没有指定 Age 的值，系统就报错了： Teachers.Age 不能为空 五、 唯一 UNIQUE这一约束很好理解，除了主列以为，还有一些列也不能有重复值。不多说，直接看代码 sqlite> sqlite> DROP TABLE Teachers; sqlite> .tables sqlite> sqlite> CREATE TABLE Teachers(Id integer PRIMARY KEY,Name text UNIQUE); sqlite> .tables Teachers sqlite> INSERT INTO Teachers(Name) VALUES('Alice'); sqlite> INSERT INTO Teachers(Name) VALUES('Bob'); sqlite> INSERT INTO Teachers(Name) VALUES('Jane'); sqlite> INSERT INTO Teachers(Name) VALUES('Bob'); Error: column Name is not unique sqlite> 这次的 Teachers 表只有 Name 这一列，但是 Name 列不能有重复值。可以看到，到我们第二次插入 Bob 时，系统就报错了。 六、 条件检查 CHECK某些值必须符合一定的条件才允许存入，这是就需要用到这个 CHECK 约束。 sqlite> sqlite> DROP TABLE Teachers; sqlite> .tables sqlite> sqlite> CREATE TABLE Teachers(Id integer PRIMARY KEY,Age integer CHECK(Age>22)); sqlite> .tables Teachers sqlite> INSERT INTO Teachers(Age) VALUES(45); sqlite> INSERT INTO Teachers(Age) VALUES(33); sqlite> INSERT INTO Teachers(Age) VALUES(23); sqlite> INSERT INTO Teachers(Age) VALUES(21); Error: constraint failed sqlite> Age 字段要求必须大于 22，当插入的数据小于22时，系统报错。 七、外键 FOREIGN KEY现在，我们的数据库中已经有 Teachers 表了，假如我们再建立一个 Students 表， 要求 Students 表中的每一个学生都对应一个 Teachers 表中的教师。 很简单，只需要在 Students 表中建立一个 TeacherId 字段，保存对应教师的 Id 号， 这样，学生和教师之间就建立了关系。 问题是：我们有可能给学生存入一个不在 Teachers 表中的 TeacherId 值， 而且发现不了这个错误。 这种情况下，可以把 Students 表中 TeacherId 字段声明为一个外键， 让它的值对应到 Teachers 表中的 Id 字段上。 这样，一旦在 Students 表中存入一个不存在的教师 Id ，系统就会报错。 sqlite> sqlite> .tables Teachers sqlite> CREATE TABLE Students (Id integer PRIMARY KEY,TeacherId integer,FOREIGN KEY(TeacherId) REFERENCES Teachers(id) ); sqlite> .tables Students Teachers sqlite> SELECT * FROM Teachers; Id Age ---- --------------- 1 40 2 33 3 23 sqlite> INSERT INTO Students(TeacherId) VALUES(1); sqlite> INSERT INTO Students(TeacherId) VALUES(3); sqlite> INSERT INTO Students(TeacherId) VALUES(9); sqlite> SELECT * FROM Students; Id TeacherId ---- --------------- 1 1 2 3 3 9 sqlite> 这里建立了 Students 表，并且把 TeacherId 作为外键与 Teachers 表的 Id 列相对应。 问题来了：插入的前两条数据没问题，因为 Id 编号 1、3 都在 Teachers 表中； 但是数字 9 并不在 Teachers 表中，不但没有报错，系统还保存进去了，这是为什么呢？ 据说 SQLite 的外键约束默认情况下并不是开启的，如果你需要这个功能，你可能需要下载源代码版本，设置每个编译参数，然后重新编译，这样你就得到支持外键的 SQLite 了。 根据前面几篇的内容，我们可以很轻送的创建一个数据表，并向其中插入一些数据，不多说，看例子： myqiao@ubuntu:~/My Documents/db$ sqlite3 test.db -- Loading resources from /home/myqiao/.sqliterc SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .tables sqlite> sqlite> CREATE TABLE Teachers( ...> Id integer PRIMARY KEY, ...> Name text NOT NULL, ...> Age integer CHECK(Age>22), ...> Country text DEFAULT 'USA'); sqlite> .tables Teachers sqlite> sqlite> INSERT INTO Teachers VALUES(1,'Alice',25,'CHN'); sqlite> INSERT INTO Teachers VALUES(2,'Bob','BRA'); sqlite> INSERT INTO Teachers(Id,Age,Country) VALUES(3,'Charls',33,'USA'); sqlite> INSERT INTO Teachers(Name,Age) VALUES('Jhon',43); sqlite> SELECT * FROM Teachers; Id Name Age Country ---- --------------- --------------- --------------- 1 Alice 25 CHN 2 Bob 25 BRA 3 Charls 33 USA 4 Jhon 43 USA sqlite> 很简单，创建了一个 Teachers 表并向其中添加了四条数据，设定了一些约束，其中有自动增加的主键、默认值等等。 八、修改数据 UPDATE 表 SET 列 = '新值' 【WHERE 条件语句】UPDATE 语句用来更新表中的某个列，如果不设定条件，则所有记录的这一列都被更新； 如果设定了条件，则符合条件的记录的这一列被更新， WHERE 子句被用来设定条件，如下例： sqlite> sqlite> SELECT * FROM Teachers; Id Name Age Country ---- --------------- --------------- --------------- 1 Alice 25 CHN 2 Bob 25 BRA 3 Charls 33 USA 4 Jhon 43 USA sqlite> sqlite> sqlite> UPDATE Teachers SET Country='China'; sqlite> SELECT * FROM Teachers; Id Name Age Country ---- --------------- --------------- --------------- 1 Alice 25 China 2 Bob 25 China 3 Charls 33 China 4 Jhon 43 China sqlite> sqlite> sqlite> UPDATE Teachers SET Country='America' WHERE Id=3; sqlite> SELECT * FROM Teachers; Id Name Age Country ---- --------------- --------------- --------------- 1 Alice 25 China 2 Bob 25 China 3 Charls 33 America 4 Jhon 43 China sqlite> sqlite> sqlite> UPDATE Teachers SET Country='India' WHERE Age<30; sqlite> SELECT * FROM Teachers; Id Name Age Country ---- --------------- --------------- --------------- 1 Alice 25 India 2 Bob 25 India 3 Charls 33 America 4 Jhon 43 China sqlite> 九、删除数据 DELETE FROM 表 【WHERE 条件语句】如果设定 WHERE 条件子句，则删除符合条件的数据记录；如果没有设定条件语句，则删除所有记录 sqlite> sqlite> SELECT * FROM Teachers; Id Name Age Country ---- --------------- --------------- --------------- 1 Alice 25 India 2 Bob 25 India 3 Charls 33 America 4 Jhon 43 China sqlite> sqlite> sqlite> DELETE FROM Teachers WHERE Age>30; sqlite> SELECT * FROM Teachers; Id Name Age Country ---- --------------- --------------- --------------- 1 Alice 25 India 2 Bob 25 India sqlite> sqlite> sqlite> DELETE FROM Teachers; sqlite> SELECT * FROM Teachers; sqlite> 十、查找数据 SELECT 列... FROM 表为了后面的练习，需要一些样本数据。 首先将下面的 SQL 语句保存到 data.sql 文件中 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 ; 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 ,Customer) VALUES ( 1640 ,"Smith"); INSERT INTO Orders(OrderPrice,Customer) VALUES ( 100 ,Customer) VALUES ( 50 ,Customer) VALUES ( 150 ,Customer) VALUES ( 250 ,Customer) VALUES ( 840 ,"Brown"); INSERT INTO Orders(OrderPrice,Customer) VALUES ( 440 ,"Black"); INSERT INTO Orders(OrderPrice,Customer) VALUES ( 20 ,"Brown"); COMMIT ; 然后在在终端执行命令 .read data.sql，将数据导入到数据库中 sqlite> sqlite> .tables Friends sqlite> .read data.sql sqlite> .tables Cars Orders Teachers sqlite> 可以看到，Cars 表和 Orders 表已经导入到数据库中，现在可以查询了 sqlite> sqlite> SELECT * FROM Cars; Id Name Cost ---- --------------- --------------- 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 sqlite> SELECT * FROM Orders; Id OrderPrice Customer ---- --------------- --------------- 1 1200 Williamson 2 200 Robertson 3 40 Robertson 4 1640 Smith 5 100 Robertson 6 50 Williamson 7 150 Smith 8 250 Smith 9 840 Brown 10 440 Black 11 20 Brown sqlite> 十一、 限制返回数量 SELECT 列... FROM 表 LIMIT 数量 OFFSET 位置有时候数据库中的数据太多，全部返回可不行，可以限制返回的数量，还可以设定返回的起始位置，如下： sqlite> sqlite> SELECT * FROM Cars LIMIT 4; Id Name Cost ---- --------------- --------------- 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 sqlite> sqlite> SELECT * FROM Cars LIMIT 4 OFFSET 2; Id Name Cost ---- --------------- --------------- 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 sqlite> 十二、 别名 SELECT 列 AS 别名，列 AS 别名 FROM我们可以给返回数据集中的某些列起一个比较直观的名字，比如把 Cost 改为"Price Of Car" sqlite> sqlite> SELECT Name,Cost AS 'Price Of Car' FROM Cars; Name Price Of Car ---- --------------- Audi 52642 Merc 57127 Skod 9000 Volv 29000 Bent 350000 Citr 21000 Humm 41400 Volk 21600 sqlite> 十三、 条件查询 SELECT 列 FROM 表 【WHERE 条件语句】一般的条件语句都是大于、小于、等于之类的，这里有几个特别的条件语句 LIKE
sqlite> sqlite> SELECT * FROM Cars WHERE Name Like '____'; Id Name Cost ---- --------------- --------------- 1 Audi 52642 sqlite> sqlite> SELECT * FROM Cars WHERE Name Like '%en'; Id Name Cost ---- --------------- --------------- 6 Citroen 21000 8 Volkswagen 21600 sqlite> sqlite> SELECT * FROM Cars WHERE Name Like '%EN'; Id Name Cost ---- --------------- --------------- 6 Citroen 21000 8 Volkswagen 21600 sqlite> GLOB
BETWEEN 值1 AND 值2返回两个值之间的数据集合。下面的语句查询价格在 20000 到 55000 之间的车，都是好车啊。 sqlite> sqlite> SELECT * FROM Cars WHERE Cost BETWEEN 20000 AND 55000; Id Name Cost ---- --------------- --------------- 1 Audi 52642 4 Volvo 29000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 sqlite> IN （集合）对应列的值必须在集合中。下面的语句查找奥迪和悍马的价格。 sqlite> sqlite> SELECT * FROM Cars WHERE Name IN ('Audi','Hummer'); Id Name Cost ---- --------------- --------------- 1 Audi 52642 7 Hummer 41400 sqlite> 十四、 排序 ORDER BY 列 ASC (DESC)指定某个列进行排序，ASC 为升序，DESC 为降序。下面的语句查询汽车品牌和价格，并以价格排序 sqlite> sqlite> SELECT Name,Cost FROM Cars ORDER BY Cost DESC; Name Cost ---- --------------- Bent 350000 Merc 57127 Audi 52642 Humm 41400 Volv 29000 Volk 21600 Citr 21000 Skod 9000 sqlite> 十五、 区分 DISTINCT 列有一些字段的值可能会出现重复，比如订单表中，一个客户可能会有好几份订单，因此客户的名字会重复出现。 到底有哪些客户下了订单呢？下面的语句将客户名字区分出来。 sqlite> sqlite> Select * FROM Orders; Id OrderPrice Customer ---- --------------- --------------- 1 1200 Williamson 2 200 Robertson 3 40 Robertson 4 1640 Smith 5 100 Robertson 6 50 Williamson 7 150 Smith 8 250 Smith 9 840 Brown 10 440 Black 11 20 Brown sqlite> sqlite> SELECT DISTINCT Customer FROM ORDERS; Customer --------------- Black Brown Robertson Smith Williamson sqlite> 十六、 分组 GROUP BY 列分组和前面的区分有一点类似。区分仅仅是为了去掉重复项，而分组是为了对各类不同项进行统计计算。 比如上面的例子，我们区分出 5 个客户，这 5 个客户一共下了 11 个订单，说明很多客户都下了不止一个订单。 下面的语句统计每个客户在订单上总共花费了多少钱。 sqlite> sqlite> SELECT sum(OrderPrice) AS Total,Customer FROM Orders GROUP BY Customer; Total Customer --------------- --------------- 440 Black 860 Brown 340 Robertson 2040 Smith 1250 Williamson sqlite> 这里 Sum 是 SQLite 内置的统计函数，在这个例子中用来求每个顾客的订单价格的和。 统计结果也可以设定返回条件，但是不能用 WHERE 子句，而是用 HAVING 子句，如下例，返回订单总额大于 1000 的顾客。 sqlite> sqlite> SELECT sum(OrderPrice) AS Total,Customer FROM Orders ...> GROUP BY Customer HAVING sum(OrderPrice)>1000; Total Customer --------------- --------------- 2040 Smith 1250 Williamson sqlite> 十七、 逻辑运算符有的查询涉及的条件语句很复杂，是有好几个条件语句经过逻辑运算得来的，一共有三种逻辑运算符：
一般稍微了解点编程知识的应该都没问题。 AND 运算符AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。使用 AND 运算符时，只有当所有条件都为真（true）时，整个条件为真（true）。例如，只有当 condition1 和 condition2 都为真（true）时，[condition1] AND [condition2] 为真（true）。 语法带有 WHERE 子句的 AND 运算符的基本语法如下： SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN]; 您可以使用 AND 运算符来结合 N 个数量的条件。SQLite 语句需要执行的动作是，无论是事务或查询，所有由 AND 分隔的条件都必须为真（TRUE）。 实例假设 COMPANY 表有以下记录： ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 下面的 SELECT 语句列出了 AGE 大于等于 25 且工资大于等于 65000.00 的所有记录： sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 OR 运算符OR 运算符也用于结合一个 SQL 语句的 WHERE 子句中的多个条件。使用 OR 运算符时，只要当条件中任何一个为真（true）时，整个条件为真（true）。例如，只要当 condition1 或 condition2 有一个为真（true）时，[condition1] OR [condition2] 为真（true）。 语法带有 WHERE 子句的 OR 运算符的基本语法如下： SELECT column1, columnN FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN] 您可以使用 OR 运算符来结合 N 个数量的条件。SQLite 语句需要执行的动作是，无论是事务或查询，只要任何一个由 OR 分隔的条件为真（TRUE）即可。 实例假设 COMPANY 表有以下记录： ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 下面的 SELECT 语句列出了 AGE 大于等于 25 或工资大于等于 65000.00 的所有记录： sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |