sqlite数据库的基本操作
细节：
1，什么是主键：
用来唯一标识一张表中的某一条数据，所有的表都必须有自己的主键，主键可以是整型，一般都用自增，也可以是字符串类型， 如果是字符串类型的话一般使用uuid（是用java语言生成的）。一般使用主键来唯一查询某一条记录，或者更新删除某一条记录。
2，order by永远放到最后面，语法规范！
3，函数
函数count： 用来统计数量，一般统计行数有几行
函数max: 用来计算某列中的最大值，列的类型一定是整型（一般情况下遇到类似年龄，分数等将来可能会计算最大值的或者本身就是数字，类型设置为整型）。
函数sum: 类似max，列的字段类型一定是整型
4，左连接：左边表全部显示，右边显示匹配成功的。
5，全连接：左右两边完全匹配成功。
6，sqlite 无右连接
-------------------表的插入------------
insert into students (id,name,age,sex)values('3','Mike','21','女');--add
insert into students (id,name,age,sex)values('4','Lisa','21','女');--add
insert into students (id,name,age,sex)values('5','Raro','21','女');--add
insert into students (id,num,name,age,sex,c_id)values(6,'201107014303','Jame','21','女','')
------------条件删除 ---------------------
delete from students where name='Jame';--delete
select *from students;
select *from students where name='Mike' and age='12';
select * from students where name like 's%' collate nocase;
------------修改表名------------
alter table student rename to students--修改表名
alter table students {rename to student|add column course text}--不识别 {}:表示为可选项，但是运行失败
------------添加列---------------------
alter table students add column tel text not null default '' collate nocase
------------条件修改数据-----------
update students set tel='18500111111' where name='lisa' collate nocase
update students set num='201107014301'
update students set age='20' where name='Mike'
----------创建表，id，course两列
create table course (id integer primary key autoincrement, course text);
------------表删除--，只能删除表，不能删除列----------
drop table course;--删除一张表（没有关联关系）
-----------如果想忽略大小写，即 case-insensitive，需要用到COLLATE NOCASE ：-----------
update students set age='12' where name='mike' collate nocase;
----------执行以下语句查看所有用户的用户名和年龄：
select name,age from students;
select *from cqx_db where type='table' and name='teacher';
--查看一张表中 字段name没有重复的条目
select distinct name from students;
select * from students group by age having count(*)>1;
-------------limit a offset b或者limit b ,a ---表示调过b项，返回a项数据--------
select * from students where num like '_011%' order by age asc, name limit 1,2;--如果相同，则根据名字的首字母排序
select * from students where num like '2011%' order by age asc -- num 以2011结尾的所有学生升序，默认为升序
select * from students where num like '2011%' order by age desc, age limit 2 offset 1;--根据年龄排序
select * from students where num like '2011%' limit 2 offset 1;
--------------------'%a' :以a结尾(_a),'a%':以a开头 '%a%':包含a-------------------------------
select * from students where num like '%01' order by age desc; 以01结尾的匹配
select * from students where num not like '%02%';选取不包含02的num
--函数upper（ name ） 大写 ，lower( name ) 小写 ，count(*)统计数量大小，sum(age) 求和,max(age)求最大值
select upper(name) ,length(name) from students where num like '2011%'
select lower(name) ,length(name) from students where num like '2011%'
select upper(name) ,length(name) from students where num like '%01' and length(name)<5
----------分组group by- 并采用函数计算租大小---------
select * ,count(*)from students group by num;--按照num进行分组
select count(*) from students where num like '%01';
-------------------------去掉重复 distinct-----------------------
select distinct num from students ;
---------------多表连接 join-------------------------
insert into teacher (id,t_name,t_course) values(1,'a','语文')
insert into teacher (id,t_name,t_course) values(2,'a','语文')
insert into teacher (id,t_name,t_course) values(3,'a','语文')
update teacher set c_id='1'
update students set c_id=12 where num like '%01';
update teacher set c_id=1;
--添加列 not null时 default ''
alter table teacher add column c_id text not null default '';
alter table teacher drop column c_course;
select teacher.t_name,students.name from teacher ,students where teacher.c_id=students.c_id;
------------------------内连接 inner join--------------
select * from teacher inner join students on teacher.c_id=students.c_id;
alter table course add column id integer;
insert into course values(1,1,'语文');
insert into course values(1,2,'语文');
select * from students inner join course on students.c_id=course.c_id;
---------------交叉连接-----------------
select * from students,course;
----------左外连接 未能匹配以左为基准，右为null--，无右连接------------
select * from students left outer join teacher on students.c_id=teacher.c_id;
--------------------别名-类似表名.相同的列名，from +原名 别名------------------------
select s.name,t.name from students s,teacher t where s.c_id=t.c_id limit 3;
--------------null 不等于任何值 是确实信息的占位符
--三种逻辑运算
select null is null;--结果1 非0的任何值都表示为真
select null is not null;--结果0 非0的任何值都表示为真
-------------coalesce函数表示为将一组值输入并返回其中第一个非null的值------
select coalesce(null,7,null,4);--结果7
-------------nullif函数相反，表示为2个值输入，如果相同则返回null,否则返回第一个参数----
select nullif (1,1);--结果null
select nullif (1,2);--结果1
-------子查询-------------
select teacher.name from teacher where teacher.c_id in(select students.c_id from students where num like '2011%')
select count(name) from students where students.[c_id] in(select teacher.c_id from teacher )
select * from students group by num like '%01'
---------==================考核 练习================================-----
---查询
select * from students where name='Lisa';
select name,sex from students where name like '%am%'; --查询包含am的所有name,sex
-----分组-------
select sex, count(id) from students group by sex; --根据性别sex分组，查询sex和对用的数量
select sex,age,count(id) from students group by sex, age; --根据性别和年龄分组，查询性别和年龄以及数量
select name,age from students order by age desc ; --根据年龄降序查询显示姓名和年龄
------------------------------函数max: 用来计算某列中的最大值，列的类型一定是整型（一般情况下遇到类似年龄，分数等将来可能会计算最大值的或者本身就是数字，类型设置为整型）。-------------------------------------------
select max(age) from students ; --从学生表中选取年龄最大值
--从学生表中选取年龄最大值的姓名和对应年龄
select name, age from students where age in (select max(age) from students)
--计算学生表中的年龄和
select sum(age) from students ;
--按照性别分组并显示性别和对应的和
select sex, count(id) from students group by sex;
--采用别名的方式全连接 ，别名规则为from+原表名 别名，别的地方使用到表名则采用别名
select t.t_name,s.name from teacher t,students s where t.c_id=s.c_id;
--左连接 通过学生和教师表查询学生和教师名称 sqlite无右连接
select students.name,teacher.t_name from students left join teacher on students.c_id=teacher.c_id;
--通过别名的方式左连接
select s.name,t.t_name from students s left join teacher t on s.c_id=t.c_id;
--通过左连接查询性别为女和年龄大于21的
select s.name,t.t_name,s.age from students s left join teacher t on s.c_id=t.c_id where sex='女' and age>21 ;
