前言:重新复习一下sqlite3语句,记下来,权当笔记。
欢迎关注我左侧头像下的新浪微博,实时更新最新文章信息,增进交流,共同成长。
原文出处:http://blog.csdn.net/u014158743/article/details/51428329
一、文档小记
SQL: 结构化查询语言
Struted Query Language设置为外键
foreign key(字段) references 被引用的表(字段)
右外连接 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的命令复习到这里, 如果你能从头看下来相信会有收获,文档总结在文章的开头, 现在回头回忆一下加深记忆。当然这只是入门级的更多的更完善的命令大全什么的, 还有对内连接、外连接等概念不了解的童鞋可以请教度娘。
完了。祝成长!!!
联系客服