首页 > 数据库技术 > 详细

sqlite数据库

时间:2019-07-13 10:54:28      阅读:38      评论:0      收藏:0      [点我收藏+]

标签:clust   表达   而且   课程   抽象   key   blocks   deb   处理   

目录

一、安装sqlite3. 1

二、定义数据... 1

1.进入和定义数据库... 1

2.基本表的定义、删除、修改... 1

(1)数据类型... 1

(2)完整性约束条件... 1

(3)定义基本表... 1

(4)删除基本表... 2

(5)修改基本表... 2

3.索引的建立和删除... 2

(1)建立索引... 2

(2)修改索引... 2

(3)删除索引... 3

4.触发器的定义和删除... 3

二、查询数据... 3

1.单表查询... 4

2.连接查询,查询将涉及多张表... 5

3.嵌套查询,在上一级查询的结果上进行查询,也是类似于编程语言中的多层循环... 6

4.集合查询,参加操作的行的列数、数据类型都必须相同... 6

5.基于派生表的查询,子查询的结果可以出现在FROM子句中作为临时的派生表,sqlite3不支持... 6

三、更新数据... 6

1.添加数据... 6

2.修改数据... 6

3.删除数据... 7

四、特殊处理... 7

1.空值... 7

2.视图... 7

五、附录... 7

1.参考网址... 7

2.sqlite3的常用命令... 7

正文

一、安装sqlite3

进入https://www.sqlite.org/,下载for widows和sqlite-amalgamation-….zip的全部文件;

创建文件夹sqlite3_32(bin,include,lib)或sqlite3_64(bin,include,lib);

创建环境路径E:\CodeBlocks\MinGW\bin; E:\sqlite3_32\bin,这一步不是必须的,但如果经常用命令行的话会方便很多;

sqlite3_32/lib路径下命令行执行dlltool -D sqlite3.dll -d sqlite3.def -l libsqlite3dll.a;

Build options->Search directories->Compiler选入E:\sqlite3_32\include、Build options->Search directories->Linker选入E:\sqlite3_32\lib、Build options->Linker settings加入E:\sqlite3_32\lib\libsqlite3dll.a;

如果出现0xc000007b的错误提示,表示使用的32或64位版本和机器不兼容(比如32位的电脑上用了64位的dll)。

二、定义数据

1.进入和定义数据库

sqlite3的shell命令

sqlite3 <[路径]数据库的名称>

2.基本表的定义、删除、修改

sql语句中的单引号和双引号没有必然区别,有时候能够起到互为转义的作用

(1)基本数据类型

NULL

空值

INT

整形

NUMBERIC

精确数值

VARCHAR

可变长的文本

FLOAT

浮点数

DOUBLE

双精度浮点数

DECIMAL(10,5)

精确小数

BOOLEAN

布尔值

DATE

日期

//定义一个主键自增的表,这个地方要用integer否则自增会报错

CREATE TABLE peopleinfo(

id integer primary key autoincrement,

name text not null,

age integer,

height float);

(2)完整性约束条件

比如PRIMARY KEY、FOREIGN KEY(Cpno) REFERENCES Course(Cno)、NOT NULL

(3)定义基本表

sql标准语句

CREATE TABLE <表名>(<列名>< 数据类型>[ 列级完整性约束条件]

[,<列名>< 数据类型>[ 列级完整性约束条件]]

[,<标记完整性约束条件>]);

(4)删除基本表

sql标准语句

CASCADE级联、与该表相关的对象全部删除,RESTRICT限制、如果该表没有相关的对象就可以执行这些语句

DROP TABLE <表名称> [RESTRICT|CASCADE];

(5)修改基本表

sql标准语句

ALTER TABLE <表名>

[ADD [COLUMN] <新列名>< 数据类型>[ 完整性约束]]

[ADD <表级完整性约束>]

[DROP [COLUMN] <列名> [CASECADE|RESTRICT]]

[DROP CONSTRANT <完整性约束> [CASECADE|RESTRICT]]

[ALTER COLUMN <列名>< 数据类型>];

sqlite3的shell命令

ALTER TABLE 数据库名称.表名称ADD [COLUMN] <新列名>< 数据类型>[ 完整性约束];

ALTER TABLE数据库名称. 表名称 RENAME TO 新名称;

3.索引的建立和删除

当表的数据相当庞大而且需要查询时,使用索引是很有必要的

标准sql语句

(1)建立索引

UNIQUE表示索引中的每个索引值值对应一个数据记录,CLUSTER表示聚簇索引;索引可以建在一列或多列上,次序默认是ASC升序、次序DESC是降序

CREATE [UNIQUE][CLUSTER] INDEX <索引的名称>

ON <表的名称>(<列名> [次序] [,<列名>[次序]]…);

(2)修改索引

sqlite3不支持修改索引

ALTER INDEX <旧索引名> RENAME TO <新索引名>;

(3)删除索引

DROP INDEX <索引名>;

4.触发器的定义和删除

标准sql语句

定义触发器

CREATE TRIGGER <触发器名称>

{BEFORE|AFTER} <触发事件> ON <表的名称>

REFERENCING NEW|OLD ROW AS <变量>

FOR EACH {ROW|STATEMENT}

[WHEN <触发条件>] <触发动作体>

删除触发器

DROP TRIGGER <触发器名称> ON <表的名称>;

sqlite3的shell命令

定义触发器

事件名称可以是INSERT、UPDATE、DELETE;在sqlite_master这张表中可以找到触发器的信息,最前面是type类型

CREATE TRIGGER 触发器名称 [BEFORE|AFTER] 事件名称

ON 表的名称

BEGIN

类似于sql的逻辑语句

END;

删除触发器

DROP TRIGGER 触发器名称;

二、查询数据

标准sql语句

SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>,…]

FROM <表或视图的名称> [,<表或视图的名称>,…] | (<SELECT 语句>) [AS] <别名>

[WHERE <条件表达式>]

[GROUP BY <列的名称1> [HAVING <条件表达式>]]

[ORDER BY <列的名称2> [ASC|DESC]];

1.单表查询

查询指定列

//Sno、Sname是Student表中的列,但是列举出来的列的顺序可以是任意的

SELECT Sno,Sname FROM Student;

查询全部列

SELECT * FROM Student;

查询经过计算的值

SELECT Sname,2014-Sage FROM Student;

//加入字符串常量、函数

SELECT Sname NAME,‘Year of birth:‘ BIRTH,2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT FROM Student;

消除完全重复的行

//默认是全部ALL

SELECT DISTINCT Sno FROM SC;

查询满足条件的行

//比较大小:会用到WHERE子句和运算符(!=或<>,!>,!<等)

SELECT Sname,Sage FROM Student WHERE Sage<20;

//在某个范围内、不在某个范围内

SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23;

SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;

//在某个集合内、不在某个集合内

SELECT Sname,Ssex FROM Student WHERE Sdept IN(‘CS‘,‘MA‘,‘IS‘);

SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN(‘CS‘,‘MA‘,‘IS‘);

//字符串匹配,[NOT] LIKE ‘用于匹配的字符串’ [ESCAPE ‘转义字符’],%表示匹配任意长度的字符串、_匹配一个字符

SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE ‘刘%‘;

SELECT Cno,Ccredit FROM Course WHERE Cname LIKE ‘DB\_Design‘ ESCAPE ‘\‘;

//涉及空值的查询,实际过程中有些数据可能为空

SELECT Sno,Cno FROM SC WHERE Grade IS NULL;

SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;

//多种条件的查询,用AND、OR来连接各个查询条件,AND的优先级高于OR但是可以用括号来改变

SELECT Sname FROM Student WHERE Sdept=‘CS‘ AND Sage<20;

ORDER BY子句,对上一级结果按照一列或多列进行升序ASC(默认值)、降序DESC输出

SELECT * FROM Student ORDER BY Sdept,Sage DESC;

聚集函数,只能用于SELECT子句和GROUP BY中的HAVING子句

COUNT([DISTINCT|ALL] <列的名称>)

统计满足条件的列的数目

SUM([DISTINCT|ALL] <列的名称>)

列值的总和

AVG([DISTINCT|ALL] <列的名称>)

列值的平均值

MAX([DISTINCT|ALL] <列的名称>)

列中的最大值

MIN([DISTINCT|ALL] <列的名称>)

列中的最小值

LOWER()

将字符串转换为小写

UPPER()

将字符串转换为大写

RANDOM()

获取一个随机数

ABS()

求绝对值

LENGTH()

求字符串的长度

//获取学生的总数目

SELECT COUNT(*) FROM Student;

//计算3号课程的平均成绩

SELECT AVG(Grade) FROM SC WHERE Cno=‘3‘;

GROUP BY子句,对一列或多列进行分组、之后对每一组使用聚集函数后输出,如果最终选出的组还需要满足一定的条件,那就使用HAVING条件子句

SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno;

SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)>2;

2.连接查询,查询将涉及多张表

等值与非等值的连接查询,类似编程中的双层循环,之前建立的索引在这里会加快查询的过程

//WHERE子句有一定的格式,“表1.列的名称 比较运算符 表2.列的名称”或者“表1.列的名称 BETWEEN 表2.列的名称 AND 表2.列的名称”

SELECT Student.Sno,SC.Cno,Sage FROM Student,SC WHERE Student.Sno=SC.Sno;

自身连接

//先修课的先修课

SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND WHERE FIRST.Cpno=SECOND.Cno;

外连接也是一种连接查询,无损一个表然后尽可能的填充输出

//这个一个左外连接,类似的有右外连接

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);

多表连接,按先后顺序先进行两个表的连接,得到的结果继续和下一个表进行连接

//跨表查询每个学生的学号、姓名、选修的课程名和对应的成绩

SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

3.嵌套查询,在上一级查询的结果上进行查询,也是类似于编程语言中的多层循环

带有IN的子查询

//不相关子查询,查找和“刘晨”在同一个系的学生

SELECT * FROM Student WHERE Sdept IN(SELECT Sdept FROM Student WHERE Sname=‘刘晨‘);

带有比较运算符的子查询

//相关子查询,找出每个学生超过他自身选修课程平均成绩的课程号

SELECT Sno,Cno FROM SC x WHERE Grade>=(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);

带有ANY(SOME)或ALL的子查询,sqlite3不支持应该用后一种办法来代替

SELECT Sname,Sage FROM Student WHERE Sage<ANY(SELECT Sage FROM Student WHERE Sdept=‘CS‘) AND Sdept<>‘CS‘;

SELECT Sname,Sage FROM Student WHERE Sage<(SELECT MAX(Sage) FROM Student WHERE Sdept=‘CS‘) AND Sdept<>‘CS‘;

带有量词EXISTS或NOT EXISTS的子查询,子查询的结果返回true或false

//查询所有选修了课程1的学生姓名

SELECT Sname FROM Student WHERE EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=‘1‘);

//查询选修了全部课程的学生姓名,可能要用到量词转换规律解决类似的至多至少问题

SELECT Sname FROM Student WHERE NOT EXISTS(SELECT * FROM Course WHERE NOT EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno));

4.集合查询,参加操作的行的列数、数据类型都必须相同

//可能会用到并操作UNION、交操作INTERSECT、差操作EXCEPT

SELECT * FROM Student WHERE Sdept=‘CS‘ UNION SELECT * FROM Student WHERE Sage<=19;

5.基于派生表的查询,子查询的结果可以出现在FROM子句中作为临时的派生表,sqlite3不支持

//找出每个学生超过他选修课程平均成绩的课程号

SELECT Sno,Cno FROM SC,(SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno) AS Avg_sc(avg_sno,avg_grade) WHERE SC.Sno=Avg_sc.avg_sno AND SC.Grade>=Avg_sc.avg_grade;

三、更新数据

标准sql语句

1.添加数据

INSERT INTO <表名>[属性列1,…,属性列n]

VALUES(数据1,…,数据n);

或者

INSERT INTO <表名>[属性列1,…,属性列n]

[数据集合];

2.修改数据

sql标准语句

UPDATE <表名>

SET <列名>=<表达式> [,<列名>=<表达式>,…]

[WHERE <条件>];

3.删除数据

DELETE FROM <表名称> [WHERE <条件>];

四、特殊处理

1.空值

插入空值

INSERT INTO SC VALUES(‘201215126‘,‘1‘,NULL);

空值的判断,使用IS NULL或IS NOT NULL

2.视图

在表的基础上抽象出自己感兴趣的部分得到视图,视图有着和表类似的操作;数据库不会存放视图的数据,只是存放它的定义;WITH CHECK OPTION表示更新视图时要时刻加上子查询中的条件;sqlite3不支持WITCH CHECK OPTION、不支持CASCADE、不支持更新视图

建立视图

CREATE VIEW <视图的名称> [(<列的名称>[,<列的名称>…])]

AS <子查询>

[WITH CHECK OPTION]

删除视图

//CASCADE表示与之相关的都一并删除

DROP VIEW <视图的名称> [CASCADE];

五、附录

1.参考网址

www.sqlite.org

下载sqlite3

www.runoob.com

中文教程

   

2.sqlite3的常用命令

命令区分大小写

.databases

查阅当前数据库

.mode <选项>

输出模式,选项可以是:逗号分隔csv、左对齐的列column、<table> 代码html、insert语句insert、每行一个值line、separator 字符串分隔list、Tab 分隔tabs、TCL 列表tcl

.tables

查阅当前数据库中的表

ctrl+c

退出

.schema <表名称>

查询表的结构

sqlite3 <[路径]数据库名称> .dump > <[路径]名称.sql>

将数据库导出到文件

sqlite3 <[路径]名称.db> < <[路径]文件名称.sql>

用sql文件恢复数据库

.open [路径]名称.db

进入sqlite3后打开某个数据库

.excel

可以将查询到的结果导出到excel文件

.indices <表名称>

列出表的索引

.output <文件名>

将输出结果保存到文件,也可以是stdout

.read <文件名>

执行文件中的sql语句

.show

显示数据库中的系统设置

.timer <ON|OFF>

计时器

select 表达式;

对表达式进行运算

sqlite_version()

获取sqlite的版本号

.exit

退出sqlite

--

注释一条语句

sqlite数据库

标签:clust   表达   而且   课程   抽象   key   blocks   deb   处理   

原文:https://www.cnblogs.com/guomc/p/11179475.html

(0)
(0)
   
举报
评论 一句话评论(0
登录后才能评论!
© 2014 bubuko.com 版权所有 鲁ICP备09046678号-4
打开技术之扣,分享程序人生!
             

鲁公网安备 37021202000002号