Sqlite3 学习记录

前言

笔者不是专门做数据库的，只是在项目研发过程中用到，并且大部分用的还是函数接口去处理的数据库。虽然大部分的命令在平时的项目中也用过，不过没有完整的看过sqlite3的命令和使用。对此特地花了几天的时间完整的看了一天教程，敲了敲指令。
Sqlite3 并不复杂，建议学习的人看一看命令的解释，动手敲一敲，实际测试下。笔者将自己学的过程整理出来，供参考。
笔者不再去具体的写sqlite3 的语法规则了，可以直接看https://www.runoob.com/sqlite/sqlite-having-clause.html，或者去官网查看，不过官网都是英文，学基础命令就没必要看了，用函数接口，还是要看看的。

1. 创建数据库

首先，笔者创建一个班级数据库，在数据库中新建一个表格，录入下表所示的学生成绩信息。

姓名	年龄	性别	科目	分数
张三	16	男	语文	90.5
张三	16	男	数学	89
李四	15	男	语文	92.5
李四	15	男	数学	93
王五	18	男	语文	88
王五	18	男	数学	95.5
刘二	19	男	语文	87
刘二	19	男	数学	90.5
小风	16	女	语文	38
小风	16	女	数学	98
小芳	19	女	语文	89.5
小芳	19	女	数学	91.5
Ran-brave	15	男	语文	96
Ran-brave	15	男	数学	87
Stem-orinal	17	男	语文	78
Stem-orinal	17	男	数学	82
勾勾	35	男	历史	67

创建过程如下所示，

forlinx@ubuntu:~/myfile/sqlite3Test$ sqlite3 class1.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table Student (
   ...> ID INT PRIMARY KEY NOT NULL,
   ...> NAME TEXT NOT NULL,
   ...> AGE INT,
   ...> SEX INT,
   ...> SUJECT TEXT,
   ...> SCORE DOUBLE
   ...> );
sqlite> .table
Student
sqlite> insert into Student (ID,NAME,AGE,SEX,SUJECT,SCORE) VALUES(1,'张三',16,1,'语文',90.5);
sqlite> insert into Student values(2,'张三',16,1,'数学',89);
sqlite> insert into Student values(3,'李四',15,1,'语文',92.5);
sqlite> insert into Student values(4,'李四',15,1,'数学',93);
sqlite> insert into Student values(5,'王五',18,1,'语文',88);
sqlite> insert into Student values(6,'王五',18,1,'数学',95.5);
sqlite> insert into Student values(7,'刘二',19,1,'语文',87);
sqlite> insert into Student values(8,'刘二',19,1,'数学',90.5);
sqlite> insert into Student values(9,'小风',16,0,'语文',38);
sqlite> insert into Student values(10,'小风',16,0,'数学',98);
sqlite> insert into Student values(11,'小芳',19,0,'语文',89.5);
sqlite> insert into Student values(12,'小芳',19,0,'数学',91.5);
sqlite> insert into Student values(13,'Ran-brave',15,1,'语文',96);
sqlite> insert into Student values(14,'Ran-brave',15,1,'语文',87);
sqlite> insert into Student values (15 , 'Stem-orinal' , 17 , 1, '语文',78 );
sqlite> insert into Student values (16 , 'Stem-orinal' , 17 , 1, '数学',82) ;
sqlite> insert into Student values (17 , '勾勾', 35 , 1 , '历史' , 67 );

2. 查看数据库

插入所有数据之后，需要查看是否插入正确，如下图所示：
可以看到，直接使用select 查找，打印出来的格式看着很难受。使用mode和header命令调整格式。

3. 修改表中记录

李四的语文成绩录错了，应该是93，进行修改。

update Student set score = 93 where id = 3;

4. 删除表中记录

勾勾是其他班的，删除这条记录。

sqlite> delete from Student where name = '勾勾';

5. 统计信息

接下来，对这张表做一些统计工作。
查看成绩大于90分的学生信息；
查看成绩在80到90分之间的学生信息；
查看成绩小于80的学生信息；
查看所有男生的信息；
查看所有女生的信息；
查看外国学生的信息。

6. 排序和统计

接下来，依旧做一些信息统计。

1. 统计每一个学生的语文和数学的总分；
2. 将总分按照从高到低进行排序；
3. 将上述两部，按照每次三个人的数量进行输出显示；
4. 显示总分在180分以上的学生信息。

7. 统计姓名

接下来，我们只需要统计姓名就可以其他什么都不要。但是表中每个人的姓名都有两条记录，因为有两门学科。怎么办呢？

8. 添加列

现在，在Student这张表中，新增加一个列，这个列为自己的老师姓名，如下所示：

ALTER TABLE Student ADD COLUMN TEACHER TEXT;

之后为每一个学生添加老师姓名。

我们再创建一个表，Teachers:

create table Teachers(
   ...>  ID  INTEGER  PRIMARY  KEY  AUTOINCREMENT,
   ...>  NAME  TEXT  NOT NULL,
   ...>  AGE  INT NOT  NULLL ,
   ...>  salary  INT default  8000  ,
   ...>  class int check (class =1)
   ...> );

sqlite> insert into Teachers(NAME , AGE , SALARY, CLASS) values('王峰',35,10000,1);
sqlite> insert into Teachers(NAME , AGE , SALARY, CLASS) values('贾三',55,20000,1);
sqlite> insert into Teachers(NAME , AGE , CLASS)  values('琳琅',30,1);
sqlite> select * from Teachers;
ID          NAME        AGE         SALARY      CLASS     
----------  ----------  ----------  ----------  ----------
1           王峰      35          10000       1         
2           贾三      55          20000       1         
3           琳琅      30          8000        1    
   

这次的创建，稍作解释， salary INT default 8000 ,是指默认值，我们在添加 琳琅的时候没有写上salary 的值，但是会默认为8000。
class int check (class =1) 在insert的时候会检查这个class的值是否为1。

9. 再次进行查看

为了测试，现在Student中插入一条数据，两张表的信息如下所示：

我们进行查找，只显示Teachers 表中存在的老师姓名的学生信息：

sqlite> select  Student.NAME,SCORE from Student inner join Teachers on Student.TEACHER = Teachers.NAME;
NAME        SCORE     
----------  ----------
张三      90.5      
张三      89.0      
李四      93.0      
李四      93.0      
王五      88.0      
王五      95.5      
刘二      87.0      
刘二      90.5      
小风      38.0      
小风      98.0      
小芳      89.5      
小芳      91.5      
Ran-brave   96.0      
Ran-brave   87.0      
Stem-orina  78.0      
Stem-orina  82.0  

常用的命令就这些了，不过用接口的话倒是没这么复杂，我都是用的回调函数进行处理，比较方便。