?????磺
????select classes.major ??students.name from classes ??students where classes.id = 2 and classes.year > 2011;
????????????
????select classes.major as clsmj ??students.name as stunm from classes as cls??students as stu where cls.id = 2 and cls.year >2011;
????where ???
????where ??????????????????????????????????????????
????+ ??-??*??/??=????=??>??<??<=??>=
?????????????????or not??and
????????like ??????????????????
????select * from students where name like “t%”;??????????а???t??????????
????group by ????
?????????????????????в????????????ν??з?????????磺
????select count??*?? ??cls_id from students group by cls_id;
?????????????е?class.id ???????????cls_id????
????having ???? groupby????
????group by???? ???????????????having
?????磺
????select count(*)??cls_id from students group by cls_id having cls_id >2;
??????cls_id???????cls_id ????2 ??students??????
????????order by
????asc ????desc????
??????select ?????????????磺 select * from students order by name asc??year desc;
??????????? limit
?????????????????????????????????????????
????select * from students limit 10??offset 2;
???????students???10?????????rowid ?2 ???????????????????????
????## distinct ???
????distinct ????select?????????????????????磺
????select distinct cls_id from students;
???????cls_id??????????cls_id
??????????
?????????????????Ρ??????????????У?
?????????? ???? count???? ??????????????? Avg???? ????????ε????? Min???? ???????С? Max???? ????????? Length???? ??????γ??? Upper???? ?????????????????д Lower???? ??Сд Abs???? ??????????
??????????????select??
???????????students???и???????????
????==select ???????????????????==
????select count(*) ??cls_id from students group by cls_id;
????students ?????????????
????select avg(length(name)) from students;????????????
????????????????where????
???????students??cls_id????????classes?ж??id????????????????磺
????select * from students ??classes where students.cls_id == classes.id;
????????????????student????κ???????cls_id??classes?????Σ?students?????ж????classes???????в???????id??
???????????A inner join B on ????
????????????????????????????????????????????????ж??????????????????????
??????????????????
??????????????select * from students inner join classes on students.cls_id = classes.id;
???????????? left outer join on
??????inner join ????????????????????????????????????е??????????е????????????
????select * from students left outer join classes on students.cls_id = classes.id;
???????students??cls_id??classes?в???????????????????students???У???y??????classes????????????
????????????????????
????????????????????????????????????????????????????????????????????????????????????SQLite???????????????
????????
????SQLite ????
???????????????????????????????? SQLite ?????????? WHERE ????е?????
?????????????????????????????????????????????????????????????????
?????????????? SELECT??INSERT??UPDATE ?? DELETE ??????????????????????????? =??<??>??>=??<=??IN??BETWEEN ???
??????????????????????????????
????1 ??????????????????????
????2 ?????? SELECT ??????????????У???????????????ж??У?????????????н??б???
????3 ORDER BY ?????????????У???????????????? ORDER BY????????????????? GROUP BY???????? GROUP BY ?????
????4 ?????????????У?????????????????????? IN ???????
????5 BETWEEN ???????????????????????????BETWEEN ????????????á?
????update
????update??????±????????????????θ?????
????update table students set name =”houson” ?? tel_no = “119” where id=0;
????update ????????????????????
????delete
????delete from .. where ..
???????????????????delete from classes ???????classes??????????
????????alter
????Sqlite ?????????????????????????Σ????????????ε??
????alter table students rename to stu_table;
????alter table students add column age default >0;
????????????????????????????????????drop???????
???????????
????create table stu_tmp (id integer primary key autoincrement ??name varchar(20) not null??tel_no varchar(11) not null ??cls_id integer not null) ;
????insert into stu_tmp id??name??tel_no select id??name??tel_no from students;
????drop table students;
????alter table stu_tmp rename to students
????????????
???????????????????????????????????????Щ??ν??в???????????????????洢??????????????
????create index [unique] ?????? on ???? ??????б??
????????????????????????????????insert ??delete??insert ???????????????á?
????SQLite???????????ж?????????????
????????????????????е?????????????????????????????????????ο??????????β??????????
?????磺
????create index student_table_index on students (id ??name??cls_id);
????select * from students where id = 1 and cls_id = 2;
????????name??г????????????У????b???????????β??е?????id ??λ??????????
????select * from students where id >1 and name =”houson” and cls_id =4;
??????????????ε???????е????id??>??????= ????????е???????????