sqlite3创建学生管理数据库

1、创建student表单，id主键，name非空，score默认为0

create table student( id int primary key not null,
			name   text        not null,
			score real default 0.0 check(score>=0));

2、任意插入10组数据，姓名成绩自定义

第一种：insert into student(id,name,score)values(1,'tianbo',90);
第二种：insert into student values(2,'lanliuzhu',98);
我这里使用第二种： 
	insert into student values (1, 'Paul', 80);
	insert into student values (2, 'Allen', 70);
	insert into student values (3, 'Teddy', 60); 
	insert into student values (5, 'David', 40);
	insert into student values (6, 'Kim', 55);
	insert into student values (7, 'James', 75);
	insert into student values (8, 'tianbo', 90); 
	insert into student values (9, 'lanliuzhu', 92);
	insert into student values (10, 'tianshuai', 95);

3、查询student表单的所有数据

.header on 
.mode column
select * from student;

4、查询成绩在80-100分的学生姓名

select name from student where score >=80 and score <=100;

5、查询成绩小于60分的学生id

select id from student where score <60;

6、查询按照成绩排序

升序：select * from student order by score asc;
降序：select * from student order by score desc;

7、查询成绩最高的3位学生的信息

select * from (select * from student order by score desc) limit 3;

8、增加班级字段

alter table student add column class char(1);

9、修改学生的班级

update student set class = '1';        //所有学生班级修改为1；

10、增加别的班级的学生

update student set class = '2' where id>6;       //id>6的学生班级修改 为'2'；

11、查询两个班级学生的个数

select count(*) from student where class = '1';
select count(*) from student where class = '2';

12、查询两个班级的平均分

select avg(score) from student;    //计算分数列的平均值
select avg(score) from student where class = '1';  //计算1班的平均分
select avg(score) from student where class = '2';  //计算2班的平均分

13、查询两个班级的最高分

select max(score) from student where class = '1';  //查询1班的最高分
select max(score) from student where class = '2';  //查询2班的最高分

14、查询两个班级的最低分

select min(score) from student where class = '1';  //查询1班的最低分
select min(score) from student where class = '2';  //查询2班的最低分

15、查询两个班级最高分学生的姓名

select name from (select * from student order by score desc)where class = '2'limit 1;

关键字on where having的区别

16、查询分数高于80分与低于20分的学生id

select id from student where score > 80 or score < 60;

17、查询班级

select distinct class from student;  //查询目前有几个班级
select class from student where name='tianbo';    //查询tianbo的班级

18、创建user表单，username主键，userpswd默认为123，isonline默认为0

create table user(username int primary key not null,
				userpswd int default 123 check(userpswd>99),
				isonline int default 0);

19、将所有学生的id以用户名存在user表单中.

//INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表
insert into user(username) select id from student;   

20、修改任意user表单中的密码与在线状态（0或1）

update user set userpswd = 123456 where username =9; 
update user set isonline = 1 where username = 9; 

21、打印所有在线的学员姓名（两表联合查询 as关键字创建对象）

select s.name from student as s inner join user as u where s.id = u.username and u.isonline = 1;

22、将姓名为123的学员密码改成456
23、查询格式为用户名，密码，在线状态，姓名，成绩，班级，并打印所有信息。

select username,userpswd,isonline,name,score,class from user inner join student on user.username = student.id;   //内连接
select user.*,student.* from user inner join student on user.username = student.id;    //内连接，打印两个表所有字段数据

24、查询别名设置为用户名，密码，在线状态，姓名，成绩，班级，并打印所有信息。

select u.username,u.userpswd,u.isonline,s.name,s.score,s.class from user as u inner join student as s on u.username = s.id;  // 表别名
select u.username as uname,u.userpswd as upswd,u.isonline as uiso,s.name as sname,s.score as sco,s.class as cla from user as u inner join student as s on u.username = s.id;   //列别名 

25、创建视图，查询别名设置为用户名，密码，在线状态，姓名，成绩，班级，并打印所有信息。

create view StuUser1_view as select username,userpswd,isonline,name,score,class from user,student on username = id;

26、创建触发器，student表单插入数据则将id作为username插入至user表单。

create trigger audit_insert after insert
on student
BEGIN    
	insert into user(username) values(new.id); //new.id 插入创建的id    new 对象
END;

27、创建触发器，将student表单的删除时将对应的user中的成员删除

create trigger audit_delete after delete
on student
BEGIN    
	delete from user where username = old .id; //old.id 被删除的id     old 对象
END;

28、创建触发器，将student表单中的删除及修改数据备份。
创建触发器前我们先创建一个新表用于备份

create table student_backup(id int  not null,
				name   text        not null,
				score real default 0.0 check(score>=0),
				class int default 0);

创建删除备份触发器

create trigger audit_delete_student_backup after delete
on student
BEGIN      
	insert into student_backup values (old.id, old.name, old.score, old.class);    
END;

创建修改备份触发器

create trigger audit_update_student_backup after update
on student
BEGIN      
	insert into student_backup values (old.id, old.name, old.score, old.class);    
END;