sqlite3数据库练习

 

//1、创建 persons 表， id 设置为主键，表的格式
sqlite> create table persons(id int primary key,name text,addr text,class text);

sqlite> .schema

CREATE TABLE persons(id int primary key,name text,addr text,class text);
//2、创建grade表,设置约束条件(score>0),表的格式
sqlite> create table grade(id int,score int check(score>0),year text);
sqlite> .schema

CREATE TABLE persons(id int primary key,name text,addr text,class text);
CREATE TABLE grade(id int,score int check(score>0),year text);
//3、追加相应的数据
sqlite> insert into persons values(1,'lucy','beijing','class_A');
sqlite> insert into persons values(2,'peter','tianjing','class_B');
sqlite> insert into persons values(3,'bob','hebei','class_A');
sqlite> select * from persons;
id          name        addr        class     
----------  ----------  ----------  ----------
1           lucy        beijing     class_A   
2           peter       tianjing    class_B   
3           bob         hebei       class_A   
sqlite> insert into grade values(1,70,'2013');
sqlite> insert into grade values(2,95,'2013');
sqlite> insert into grade values(3,80,'2013');
sqlite> insert into grade values(1,88,'2014');
sqlite> select * from grade;
id          score       year      
----------  ----------  ----------
1           70          2013      
2           95          2013      
3           80          2013      
1           88          2014  
//4、打印所有学生的信息：包括 name,addr,class,score,year    

sqlite> select name,addr,class,score,year from persons,grade where persons.id=grade.id;
name        addr        class       score       year      
----------  ----------  ----------  ----------  ----------
lucy        beijing     class_A     70          2013      
peter       tianjing    class_B     95          2013      
bob         hebei       class_A     80          2013      
lucy        beijing     class_A     88          2014   
//5、将打印所以学生信息的语句,创建视图PersonsGrade,方便后面使用   

sqlite> create view PersonsGrade as select persons.id,name,addr,class,score,year from persons,grade where persons.id=grade.id;

sqlite> select * from PersonsGrade;
id          name        addr        class       score       year      
----------  ----------  ----------  ----------  ----------  ----------
1           lucy        beijing     class_A     70          2013      
2           peter       tianjing    class_B     95          2013      
3           bob         hebei       class_A     80          2013      
1           lucy        beijing     class_A     88          2014    
//6、查看最高分数是多少？  

sqlite> select max(score) from PersonsGrade;
max(score)
----------
95        
//7、查看每个班的平均分，以及对应的班级名称和班级人数

sqlite> select avg(score),class,count(*) from PersonsGrade group by class;
avg(score)        class       count(*)  
----------------  ----------  ----------
79.3333333333333  class_A     3         
95.0              class_B     1        
//8、在 persons 表中 id 列创建一个索引 persons_id_index 
sqlite> create index persons_id_index on persons(id);
sqlite> .indices
persons_id_index
 
sqlite> select * from PersonsGrade order by id;//根据id排序
id          name        addr        class       score       year      
----------  ----------  ----------  ----------  ----------  ----------
1           lucy        beijing     class_A     70          2013      
1           lucy        beijing     class_A     88          2014      
2           peter       tianjing    class_B     95          2013      
3           bob         hebei       class_A     80          2013    
//9、查看 lucy 在 2013 年的成绩  
sqlite> select score from PersonsGrade where name='lucy' and year='2013';
score     
----------
70        
//10、设置触发器 tg_delete


sqlite> create trigger tg_delete after delete on persons
   ...> begin delete from grade where grade.id=old.id;
   ...> end;
sqlite> select name from sqlite_master where type='trigger' and name='tg_delete';
name      
----------
tg_delete 
//11、执行删除 bob 的操作
sqlite> delete from persons where name='bob';
//12、查看所有人的信息，确定是否执行了删除操作

sqlite> select * from PersonsGrade;
id          name        addr        class       score       year      
----------  ----------  ----------  ----------  ----------  ----------
1           lucy        beijing     class_A     70          2013      
2           peter       tianjing    class_B     95          2013      
1           lucy        beijing     class_A     88          2014