sqlite3的一些常用语句

sqlite3官网地址：www.sqlite.org
源上安装：
sudo apt-get install libsqlite3-dev sqlite3
SQL:
1、创建表：
create table student(id integer primary key, name text, age integer, score integer);
2、查询表的内容：
select * from student;
select id, name from student;
3、往表中插入一条记录：
insert into student(id, name, age, score) values(3, 'XiaoMing', 21, 100);
insert into student values(3, 'XiaoMing', 21, 100);
4、修改一条记录：
update student set score=90, age=19 where id=4;
注意要加条件，否则就是修改所有记录了。
5、删除一条记录：
delete from student where id=2; 删除学号是2的学员的记录
delete from student; 删除所有记录
6、修改表的结构：
alter table student add score2 integer; 增加一个字段
但是sqlite数据库暂时不支持删除一个字段，也不支持修改一个字段。
alter table student rename to student1; 把表明从student 改变成student1
7、删除一张表：
drop table student;   删除student表.
8、备份一张表：
create table studentbak as select * from student;   把现有的student表备份成studentbak表。
9、查询表格数据：
select * from student;
select * from student where score=100;
select * from student where score!=80;
select * from student where score>=80;
select * from student order by score;
select * from student order by score asc;把记录按分数升序排列
select * from student order by score desc;把记录按分数降序排列
select * from student where score>=80 order by score;把记录先用where子句过滤，把剩下的记录排序。
select * from student where score between 80 and 90 order by score;升序显示分数在80到90之间的记录。
select * from student where name like 'X%';  显示名字以X开头的记录。
select * from student where name like '%g';  显示名字以g结尾的记录。
select * from student limit 5;显示头5条记录。
select * from student order by score desc limit 5; 显示分数最高的5名学员的记录。
select * from student order by score asc limit 2; 显示分数最低的2名学员的记录。
select * from student limit 5 offset 3; 跳过3条记录显示5条记录。
select * from student limit 5 , 3;   和上条语句类似，只不过数值含义相反。
10.group by用法：
CREATE TABLE employee(id integer primary key, name text, dep text, salary integer);
1|XiaoLi|market|6000
2|XiaoLi|tech|7000
3|XiaoMing|trs|8600
4|XiaoZhang|trs|6000
5|XiaoSong|tech|8900
6|LaoZheng|market|4000
统计整个公司工资总和：
select sum(salary) from employee;
统计每个部门的工资总和：
select dep, sum(salary) from employee group by dep;
select dep, sum(salary) from employee where id>3 group by dep; where子句要放在group by的前面。
select dep, sum(salary) from employee where id>3 group by dep having sum(salary)>5000; having子句是group by的条件子句，where子句先发生，然后才是having 子句执行。
select id from employee group by id,name,dep,salary having count(*)>1;
查找重复记录的方法。
select id,name from employeenew group by name,dep,salary having count(*) > 1;
显示名字相同的多项
select id,name,dep,salary  from employeenew group by name,dep,salary having count(*) = 1;
显示table中所有的记录
select count(*) from employeenew;
显示所有记录的个数
select dep,avg(salary) from employeenew group by dep;
显示dep下每一组的平均值
select * from studentnew where id > 3 intersect select * from studentnew where id < 9;
显示id > 3 && id < 9 的所有记录：即4 - 8 的记录
select * from studentnew where id > 3 union all select * from studentnew where id < 9;
显示所有的大于3并且小于9的，并集（如果有相同的，会重复显示）
select * from studentnew where id > 3 union all select * from studentnew where id < 9;
显示大于9的记录
select * from studentnew where id > 3 union all select * from studentnew where id < 6;
显示大于6的记录，（与上一个进行比较）
select *from student where score = (select score from student order by score desc limit 1);
显示最高分的所有学生的记录
select distinct name from fruit
显示table中名字不相同的水果;
select all name from fruit
显示所有水果的名字;
<两张表>：
1.等值连接
select student.id,student.name, score.score1,score.score2 from student,score where student.id = score.id;
（两张表进行等值（id）查询）
2.自然连接
select student.id,student.name, score.score1,score.score2 from student natural join score;
（自然连接，查询表里面必须有同名的字段，如果有相同的字段，和上一个的结果一样，如果没有同名的字段，则输出两个表的笛卡尔积）
3.内连接
select student1.stuno,student1.name, score.score1,score.score2 from student1 inner join score on student1.stuno = score.id;
（内连接，查询字段可以不相同，必须指定连接（on student1.stuno = score.id）字段）
select a.stuno,a.name,b.score1,b.score2 from student1 as a inner join score as b on a.stuno = b.id;
（as 后面的为别名）
4.外连接
 select a.stuno,a.name,b.score1,b.score2 from student1 as a left outer join score as b on a.stuno = b.id;
（左连接，显示左边表的记录）
 select a.stuno,a.name,b.score1,b.score2 from score as b left outer join student1 as a on a.stuno = b.id;
（与上一个进行对比）
sqlite3暂时不支持右连接和全连接