sqlite3命令语句, 文档小记

前言：重新复习一下sqlite3语句，记下来，权当笔记。

欢迎关注我左侧头像下的新浪微博，实时更新最新文章信息，增进交流，共同成长。

原文出处：http://blog.csdn.net/u014158743/article/details/51428329

一、文档小记

SQL: 结构化查询语言

     Struted Query Language

sqlite: sql:数据库语言
       lite:精简版

创建数据库： .open  路径\数据库名.db
             数据库不存在会创建该数据库
             数据库存在会打开该数据库

             .database  查看当前正被使用的数据库(也就是使用.open打开的数据库)

数据库是以表的形式来存储数据的：创建表的 sql语句----在当前被打开的数据库中创建
             
              create table 表名(
              
              字段1   数据类型   [约束]
              字段2   数据类型   [约束]
              字段3   数据类型   [约束]
              .........
              )
             
              数据类型:integer,real,text,blob
              兼容其它类型：smallint,int,longint,numeric,char(5),varchar(5)
              
              .table  查看当前数据库中的表
              .schema 表名   查看创建表的sql语句

              向表中插入数据:一次插入一条记录
              
              向表中所有字段插入值:
              insert into 表名 values(值1，值2，值3 ...)--- 值要和字段的类型，个数，顺序保持一致

              向表中部分字段插入值:

              insert into 表名(字段名1，字段名2,...) values(值1，值2，值3 ...)

              表中的每条记录对应一个实体，因为每个实体是独一无二的，所以每条记录也必须是独一无二的

              保证表中记录唯一的方式：unique(唯一约束),primary key(主键),autoincrement(自动增长)

              被设置为主键的字段上的值是不允许重复的，通常一张表都要有一个主键，如果一张表没有适合
              作为主键的，那么就在表中增加一个字段，专门作为主键

               默认值约束: default '值'
               检查约束:  check(条件)
               非空约束:  not null

       删除表中记录的语句:
                 
               delete from 表名 [where 条件]           
       
       修改表中记录的语句:  update 表名 set 字段名=修改之后的值,字段名=修改之后的值... [where 条件]
                       

       查询：基于表中已经存在的数据得到我们想要的数据
             
             select * from 表名 [where 条件]; *号代表所有字段

             >  >= <  <=  =  <> !=  not  and  or  between ...and  in(值，值....)
             is null   is not null
              
            聚合函数: max() min() sum() avg() count()


            排序：select 字段名  from 表名 [where 条件] order by 字段 默认是升序 asc 降序是desc
                                                                     limit 3;
            分组： group by 字段
            分组之后再筛选记录必须用having
            
            子查询：在一个查询中有用到了别的查询

       外键：如果一张表的某个字段引用了另一张表中的某个字段上的值，那么这个字段就应该

             设置为外键

              foreign key(字段) references 被引用的表(字段)

            启用外键: pragma foreign_keys= on

          多表连接查询： 内连接   join
                           左外连接 left join

                          右外连接 right join (不支持)

二、命令行实战

sqlite> .open student.db

sqlite> .database

seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             E:\student.db

// 先来一个没设主键的错误示范

sqlite> create table stuinfo(
   ...> id integer,
   ...> name text,
   ...> sex char(1),
   ...> birthday  datetime);

sqlite> insert into stuinfo values(1,'孙迪','男','1990-8-15');
sqlite> select * from stuinfo;
1|孙迪|男|1990-8-15
sqlite> insert into stuinfo values(1,'孙迪','男','1990-8-15');
sqlite> select * from stuinfo;
1|孙迪|男|1990-8-15
1|孙迪|男|1990-8-15

sqlite> drop table stuinfo;

// 删掉原来有错误的表, 重新创建新表
sqlite> create table stuinfo(
   ...> id integer primary key, // 这次加上了主键约束
   ...> name text,
   ...> sex char(1),
   ...> address text);
sqlite> insert into stuinfo values(1,'李四','男','上海');// 向表中插入数据的基本格式
sqlite> insert into stuinfo values(1,'李四','男','上海');
Error: UNIQUE constraint failed: stuinfo.id
sqlite> select * from stuinfo;
1|李四|男|上海
sqlite> insert into stuinfo values(3,'刘能','男',26,'铁岭');

// 也可以指定插入值的字段, 不过要注意主键、非空等关键字的约束
sqlite> insert into stuinfo(id,name,age,address) values(4,'赵四',28,'铁岭');
sqlite> select * from stuinfo;
1|李四|男|23|北京
2|王四|男|23|
3|刘能|男|26|铁岭
4|赵四|男|28|铁岭
sqlite> delete from stuinfo where id=2;// 删除表中记录的基本格式
sqlite> select * from stuinfo;
1|李四|男|23|北京
3|刘能|男|26|铁岭
4|赵四|男|28|铁岭
sqlite> update stuinfo set address='上海' where id=3;// 修改表中记录的基本格式
sqlite> select * from stuinfo;
1|李四|男|23|北京
3|刘能|男|26|上海
4|赵四|男|28|铁岭
sqlite> update stuinfo set sex='女',address='北京' where id=4;// 可以一次性对多个字段上的值进行修改
sqlite> select * from stuinfo;
1|李四|男|23|北京
3|刘能|男|26|上海
4|赵四|女|28|北京
sqlite> select * from stuinfo;
1|李四|男|23|北京
3|刘能|男|26|上海
4|赵四|女|28|北京
sqlite> select * from stuinfo where sex='男';// 附带查询条件的查询语句
1|李四|男|23|北京
3|刘能|男|26|上海


// 让我们进一步, 重新创建表, 示范一系列带查询条件的查询语句
sqlite> create table stuinfo(
   ...> id integer primary key,
   ...> name text,
   ...> sex char(1),
   ...> score int);
sqlite> insert into stuinfo values(1,'张三','女',78);
sqlite> insert into stuinfo values(2,'王三','男',68);
sqlite> insert into stuinfo values(3,'王丽','女',98);
sqlite> insert into stuinfo values(4,'王刚','男',58);
sqlite> insert into stuinfo values(5,'赵刚','男',88);
sqlite> insert into stuinfo values(6,'赵英','女',86);
sqlite> select * from stuinfo where score>80;
3|王丽|女|98
5|赵刚|男|88
6|赵英|女|86
sqlite> select * from stuinfo where score>=80;
3|王丽|女|98
5|赵刚|男|88
6|赵英|女|86
sqlite> select * from stuinfo where score<>80;
1|张三|女|78
2|王三|男|68
3|王丽|女|98
4|王刚|男|58
5|赵刚|男|88
6|赵英|女|86
sqlite> select * from stuinfo where score!=80;
1|张三|女|78
2|王三|男|68
3|王丽|女|98
4|王刚|男|58
5|赵刚|男|88
6|赵英|女|86
sqlite> select * from stuinfo where score=80;
sqlite> select * from stuinfo where sex='男';
2|王三|男|68
4|王刚|男|58
5|赵刚|男|88
sqlite> select * from stuinfo where not sex='男'; // 注意not的位置
1|张三|女|78
3|王丽|女|98
6|赵英|女|86
sqlite> select * from stuinfo where sex='男' and score>80; // 查询条件多个的情况
5|赵刚|男|88
sqlite> select * from stuinfo where sex='男' or score>80;
2|王三|男|68
3|王丽|女|98
4|王刚|男|58
5|赵刚|男|88
6|赵英|女|86
sqlite> select * from stuinfo where score>=70 and score<=90;
1|张三|女|78
5|赵刚|男|88
6|赵英|女|86
sqlite> select * from stuinfo where score between 70 and 90;
1|张三|女|78
5|赵刚|男|88
6|赵英|女|86
sqlite> select * from stuinfo where score=78 or score=88;
1|张三|女|78
5|赵刚|男|88
sqlite> select * from stuinfo where score in(78,88);
1|张三|女|78
5|赵刚|男|88
sqlite> insert into stuinfo values(7,'赵霞','女',null);
sqlite> insert into stuinfo values(8,'孙迪','男',null);
sqlite> select * from stuinfo where score is null;
7|赵霞|女|
8|孙迪|男|
sqlite> select * from stuinfo where score is not null;
1|张三|女|78
2|王三|男|68
3|王丽|女|98
4|王刚|男|58
5|赵刚|男|88
6|赵英|女|86
sqlite> selcet name,max(score) from stuinfo; // 聚合函数: max() min() sum() avg() count()的用法示范
Error: near "selcec": syntax error
sqlite> select  name,max(score) from stuinfo;
王丽|98
sqlite> select  name,min(score) from stuinfo;
王刚|58
sqlite> select  sum(score) from stuinfo;
476
sqlite> select avg(score)  from stuinfo;
79.3333333333333
sqlite> select count(*)  from stuinfo;
8
sqlite> select count(*) from stuinfo where sex='男';
4
sqlite> select count(*) from stuinfo where sex='女';
4
sqlite> select avg(score) from stuinfo where sex='女';
87.3333333333333
sqlite> select avg(score) from stuinfo where sex='男';
71.3333333333333

sqlite> select * from stuinfo order by score;// 开始对查询出来的结果进行简单的排序, 可见默认为升序
7|赵霞|女|
8|孙迪|男|
4|王刚|男|58
2|王三|男|68
1|张三|女|78
6|赵英|女|86
5|赵刚|男|88
3|王丽|女|98
sqlite> select * from stuinfo order by score desc;
3|王丽|女|98
5|赵刚|男|88
6|赵英|女|86
1|张三|女|78
2|王三|男|68
4|王刚|男|58
7|赵霞|女|
8|孙迪|男|
sqlite> select * from stuinfo group by sex;// group by 字段, 分组进行查询, 用select *只显示第一条满足分组条件的第一条数据
8|孙迪|男|
7|赵霞|女|
sqlite> select count(*) from stuinfo group by sex;
4
4
sqlite> select sex,count(*) from stuinfo group by sex;
男|4
女|4
sqlite> select avg(score) from stuinfo group by sex;
71.3333333333333
87.3333333333333
sqlite> select sex,avg(score) from stuinfo group by sex;
男|71.3333333333333
女|87.3333333333333
sqlite> select sex,avg(score) from stuinfo group by sex having avg(score)>80;// 分组之后再筛选记录必须用having
女|87.3333333333333
sqlite> select * from stuinfo where score>(select avg(score)  from stuinfo);// 子查询, 在满足子查询条件的记录中进行查询
3|王丽|女|98
5|赵刚|男|88
6|赵英|女|86
sqlite> select * from stuinfo order by score desc limit 3;// 限制查询结果记录条数
3|王丽|女|98
5|赵刚|男|88
6|赵英|女|86



// 快完了, 坚持住, 让我们更进一步, 最后进入多表操作
sqlite> drop table stuinfo;
sqlite> create table stuinfo(
   ...> id integer primary key,
   ...> name text,
   ...> sex char(1),
   ...> address text);

sqlite> create table stuscore(
   ...> id integer primary key,
   ...> stunum integer,
   ...> kemu text,
   ...> score int,
   ...> foreign key(stunum) references stuinfo(id));

sqlite> insert into stuinfo values(1,'李四','男','上海');
sqlite> insert into stuinfo values(2,'王四','男','上海');
sqlite> insert into stuinfo values(3,'赵四','男','上海');

sqlite> insert into stuscore values(1,4,'java',65);
sqlite> select * from stuscore;
1|4|java|65
sqlite> pragma foreign_keys= on;// 记得开启外键约束
sqlite> insert into stuscore values(2,5,'java',65);// stunum=5不是任何一个学院信息表中id的值, 即不是外键
Error: FOREIGN KEY constraint failed
sqlite> insert into stuscore values(3,1,'java',75);
sqlite> insert into stuscore values(4,2,'java',85);
sqlite> insert into stuscore values(5,3,'java',55);
sqlite> select * from stuscore;
1|4|java|65
3|1|java|75
4|2|java|85
5|3|java|55
sqlite> delete from  stuscore where id = 1;
sqlite> select * from stuscore;
3|1|java|75
4|2|java|85
5|3|java|55

//接下来引入多表连接查询
sqlite> select * from stuinfo;
1|李四|男|上海
2|王四|男|上海
3|赵四|男|上海
sqlite> select * from stuscore;
3|1|java|75
4|2|java|85
5|3|java|55
sqlite> select name,address,score from stuinfo join stuscore on stuinfo.id=stuscore.stunum;// 内连接
李四|上海|75
王四|上海|85
赵四|上海|55
sqlite> select name,address,score from stuinfo left join stuscore on stuinfo.id=stuscore.stunum;// 左外连接
李四|上海|75
王四|上海|85
赵四|上海|55

// 来认真体会区别
sqlite> delete from stuscore where id = 5;
sqlite> select * from stuscore;
3|1|java|75
4|2|java|85
sqlite> select name,address,score from stuinfo join stuscore on stuinfo.id=stuscore.stunum;
李四|上海|75
王四|上海|85
sqlite> select name,address,score from stuinfo left join stuscore on stuinfo.id=stuscore.stunum;
李四|上海|75
王四|上海|85
赵四|上海|

// 内连接取交集，外连接分左和右， 左连接左边的全取， 右连接右边的全取(sqlite不支持右连接)
sqlite> select name,address,score from stuinfo right join stuscore on stuinfo.id=stuscore.stunum;
Error: RIGHT and FULL OUTER JOINs are not currently supported

sqlite3的命令复习到这里, 如果你能从头看下来相信会有收获，文档总结在文章的开头, 现在回头回忆一下加深记忆。当然这只是入门级的更多的更完善的命令大全什么的， 还有对内连接、外连接等概念不了解的童鞋可以请教度娘。

完了。祝成长！！！