SQL语句进阶

本文讲解的是SQL语句进阶，主要包含：
1.函数和聚合
2.数据分组group by
3.过滤分组having
4.连接表
5.触发器
6.查询优化-索引

1.函数和聚合
SQL语句支持利用函数来处理数据，函数一般是在数据上执行的，他给数据的转换和处理提供了方便。
常用的文本处理函数有：
length() 返回字符串的长度；
lower() 将字符串转换为小写；
upper() 将字符串转换为大写；
语法：
select 函数名(列名) from 表名;

假如已经创建表persons如下：

create table persons(id integer,name text,addr text,score integer);
insert into persons values(1,'lucy','beijing',90);
insert into persons values(1,'peter','shandong',95);
insert into persons values(1,'bob','suzhou',80);

示例：

select * from persons;
1|lucy|beijng|90
2|peter|shandong|95
3|bob|suzhou|80
select id,upper(name) from persons;
1|LUCY
2|PETER
3|BOB

用于检索数据的常用聚集函数有：
avg()     返回某列的平均值
count()   返回某列的行数
max()     返回某列的最大值
min()     返回某列的最小值
sum()     返回某列值的和
示例：

select max(score) from persons;
95
select count(*) from persons;
3

2.数据分组：group by 语句
分组数据，以便能汇总表内容的子集，常和聚集函数搭配使用。
语法：
select 列名1，列名2 from 表名 group by 列名；
示例：
先增加一列班名(class):

alter table persons add class text;
update persons set class='class_A' where name='lucy';
update persons set class='class_B' where name='peter';
update persons set class='class_A' where name='bob';

然后输入（输出各班人数和平均分）：

select class,count(*) from persons group by class;
class_A|2
class_B|1
select class,avg(score) from persons group by class;
class_A|75.0
class_B|95.0

注意：group by子句必须出现在where子句之后
3.过滤分组
语法：
通过having可以实现包括哪些分组，排除哪些分组，例如查看班级平均分大于90的班级。
select 函数名(列名1),列名2 from 表名 group by 列名 having 函数名 限制值
示例：

select class,avg(score) from persons group by class having avg(score) >= 90;
class_B|95.0

4.连接表
保存数据时往往不会将所有的数据保存在一个表中，而是在多个表中存储，联结表就是从多个表中查询数据；在一个表中不利于分解数据，也容易使相同数据出现多次，
浪费存储空间，使用联结表查看各个数据更直观，这使得在处理数据时更简单。
例如有学生信息和成绩的两个表：
学生信息表(persons):
id  name  addr
1   lucy  beijing
2   peter tianjing
3   bob   shanghai
学生成绩表(grade):
id   score   year
1    70      2013
2    95      2013
3    80      2013
1    88      2014
按上面数据创建两张表：persons(学生信息)和grade(学生成绩)，并插入相应的数据。
语法：
select 列名1，列名2，...from 表1，表2...where 判断语句；
示例：

select name,addr,score,year from persons,grade where persons.id = grade.id and name='lucy';
lucy|beijing|70|2013
lucy|beijing|88|2014

提示：匹配语句：persons.id = grade.id;完全限定名，用一个点(.)分隔表名和列名
视图：
用于简化复杂的SQL操作,如多表查询。
创建视图语法：
create view 视图名 as 语句;
示例：

create view PersonsGrade as select name,addr,score,year from persons,grade where persons.id = grade.id and name='lucy';

使用.tables可以查看创建的视图：
创建视图后，视图名可作为一个表来使用；示例：

select * from PersonsGrade order by year desc;
lucy|beijing|88|2014
lucy|beijing|70|2013

删除视图：
语法：drop view 视图名;
5.触发器
SQLite的触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行调用，类似回调函数；只有每当执行delete,insert,update操作时，才会触发，并执行指定的一条或多条SQL语句。
创建触发器：
语法：create trigger 触发器名 [before|after] [insert|update|delete] on 表名 
begin 
语句;
end;
查看触发器和删除触发器：
查看：select name from sqlite_master where type='trigger' and tbl_name='表名';
删除：drop trigger 触发器名;
例1：
当删除学生信息时，可以设置触发器删除另一张表中对应学生的成绩：

create trigger tg_delete after delete on persons begin delete from grade where id=old.id;end;
delete from persons where name='lucy';

查询发现相应的数据也已经被删除。
注意：old.id等价于persons.id,但此处不能写persons.id,old.id代表删除行的id。
例2：
用于保证数据一致，以及每当更新或删除表时，将记录写入日志：

create table log(time text, date text);
create trigger tg_update before update on persons begin insert into log values(datetime('now'),'update persons');end;
update persons set name='Mary' where name='peter';

用select查询发现，日志已经保存。
注意：datetime('now')获取当前系统的时间。
6.查询优化-索引
使用索引可以提高查询效率。
创建索引：
语法：create index 索引名 on 表名(列名);
查看索引：.indices
删除索引: drop index 索引名;
示例：

create index persons_id_index on persons(id);
select * from persons order by id desc;

索引创建注意：
在作为主键的列上；
在经常需要排序的列上创建索引；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。