函数和聚合

1.函数

SQL语句支持利用函数来处理数据，函数一般是在数据上执行的，它给数据的转换和处理提供了方便

常用的文本处理函数：

函数名	函数作用
length()	返回字符串的长度
lower()	将字符串转换为小写
upper()	将字符串转换为大写

select 函数名(列名) from 表名；

• 例：查看名字长度
  
• 例：名字大小写转换
  

2.聚合函数

使用聚集函数，用于检索数据，以便分析和报表生成

常用聚集函数：

函数名	返回值
avg()	返回某列的平均值
count()	返回某列的行数（个数）
max()	返回某列的最大值
min()	返回某列的最小值
sum()	返回某列值之和

• 例：求平均成绩
  
• 例：求及格人数

• 例：求总分数

count 的一个应用：判断数据库中是否有某张表
select count( * ) from sqlite_master where type=’ table ’ and name=’ 表名称 '；
如果返回非0，则有这张表；如果返回0，则没有。

sqlite_master 是数据库自带的一个表。当用户创建一张表时，数据库会将用户新建的表的信息存放在sqlite_master 这张表中

数据分组 group by

分组数据，以便能汇总表内容的子集，常和聚集函数搭配使用。

#group by的句子必须出现在where子句后

select 列名 1[, 列名 2, …] from 表名 group by 列名；

• 过滤分组 having
  对group by的分组进行筛选细化

select 函数名（列名 1）[, 列名 2, …] from 表名 group by 列名 having 函数名 限制值；

约束

常用的约束：主键、唯一约束、检查约束

• 主键
  每一行唯一的标识(一张表的属性中只能有一个主键，该列的每个id不能重复)
  对用户没有意义（常用于索引，或和其他表的链接）
  不需要更新主键
  主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等
  主键可由计算机自动生成（保证唯一性）

create table 表名称 (列名称 1 数据类型 primary key, 列名称 2 数据 类型,列名称 3 数据类型, …)；

• 唯一约束
  用来保证一个列（或一组列）中数据唯一，类似于主键，但跟主键有区别
  表可包含多个唯一约束，但只允许一个主键
  唯一约束列可修改或更新，可以是有意义的数据
  创建表时，通过 unique 来设置

create table 表名 (列名称 1 数据类型 unique[，列名称 2 数据类型 unique,…])；

• 检查约束
  用来保证一个列（或一组列）中的数据满足一组指定的条件
  指定范围，检查最大或最小范围，通过 check 实现

create table 表名 (列名 数据类型 check (判断语句))；

联结表 多表操作

保存数据时往往不会将所有数据保存在一个表中，而是在多个表中存储，联结表就是从多个表中查询数据。
在一个表中不利于分解数据，也容易使相同数据出现多次，浪费存储空间，查看和处理不方便；使用联结表查看各个数据更直观，在处理数据时更简单。

通过特殊的列（基本都是主键列）将不同的表关联起来。

select 列名 1,列名 2,… from 表 1,表 2,… where 判断语句；

例：
先建立学生数据库stu.db
创建两个表，学生信息表stu和成绩表grade如下

插入学生基本信息

录入学生分数

联结表查询 两张表中相同名称的列要用 [表名.列名] 表示
查看lucy获得的成绩

视图 虚拟的表

视图不包含数据，因此在每次使用视图时，实际上都必须执行查询语句
从返回结果信息中再检索视图与表一样,必须唯一命名

• 创建视图：
  create view 视图名 as 语句；
• 删除视图：
  drop view 视图名；
  
  

触发器

SQLite 的触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行调用
1、只有每当执行 delete，insert 或 update 操作时，才会触发，并执行指定的一条或多条 SQL 语句
2、触发器常用于保证数据一致，以及每当更新或删除表时，将记录写入日志表

• 创建触发器
  create trigger 触发器名 [ before | after ] [ insert | update | delete ] on 表名
  begin 语句； end；
• 查看触发器
  select name from sqlite_master where type=‘trigger’ and tbl_name=‘表名’；
• 删除触发器
  drop trigger 触发器名；

例：数据操作------删除学生信息，成绩表相关信息删除

创建触发器
此处old.id=id； old.id是stu表中被删除的学生的id，id是在grade表中查找的id

删除学生信息，自动调用触发器

例：写入日志------更新stu表，将操作记录添加到日志表（log表）

索引 查询优化

数据库中往往存储了大量的数据，普通查询的默认方法是调用顺序扫描，如果没有索引，必须遍历整个表，直到 ID 等于 10000 的这一行被找到为止。为了提高查询的效率，可以使用索引。

索引其实是一种查找算法，使用索引可快速访问数据库表中的特定信息。
索引可能要占用大量的存储空间，因此并非所有数据都适合索引。
索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。

常见查找：普通查找（遍历），二分查找，哈希查找（空间换时间 散列表）等
常见的查找算法（七）：哈希查找

• 创建索引
  create index 索引名 on 表名(列名)；
• 查看索引
  .indices
• 删除索引：
  drop index 索引名；

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