cascade sqlite 数据库_sqlite数据库

目录

正文

进入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 [,,…] | () [AS]

[WHERE ]

[GROUP BY [HAVING ]]

[ORDER BY [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'CS';

SELECT Sname,Sage FROM Student WHERE Sage'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 中文教程

命令区分大小写.databases 查阅当前数据库

.mode 输出模式，选项可以是：逗号分隔csv、左对齐的列column、

.tables 查阅当前数据库中的表

ctrl+c 退出

.schema 查询表的结构

sqlite3 .dump > 将数据库导出到文件

sqlite3 < 用sql文件恢复数据库

.open [路径]名称.db 进入sqlite3后打开某个数据库

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

.indices 列出表的索引

.output 将输出结果保存到文件，也可以是stdout

.read 执行文件中的sql语句

.show 显示数据库中的系统设置

.timer 计时器

select 表达式; 对表达式进行运算

sqlite_version() 获取sqlite的版本号

.exit 退出sqlite

-- 注释一条语句