Sqlite数据库

简介
是一款微型数据库，它用在嵌入式系统中，比如Android系统。它跟传统的数据库有比较大的差别。省略了大多数的功能，比如权限，管理、触发器、存储过程等。只保留对数据库最常用到的增删改查操作。（弱类型的数据库）。

数据库相关名词

• DB（数据库）：按一定的数据结构来存放数据的仓库
• DBMS（数据库管理系统）：包括了DB，还多了一些操作管理维护DB的相关应用
• DBA（数据库管理员）
• 标准的SQL（结构化查询语言）
• 用于对数据库进行操作和管理的语言

所有数据库都通用

• 分类：
• DDL（数据定义语言）：创建库、创建表、删除库、删除表
• DML（数据操作语言）：插入、删除、修改数据
• DQL（数据查询语言）：数据查询语言
• DCL（数据控制语言）：创建用户、设置权限、撤销权限等。

表的管理

• 打开/创建数据库

// 进入指定的数据库，如不存在则创建
sqlite3  数据库名

• 创建表

语法
create table 表名（
    列名1 数据类型 约束条件，
    列名2 数据类型 约束条件，
    ......）;

sqlite数据库支持的数据类型（5种类型）

• null 空
• integer 整型
• real 浮点型
• text/varchar 字符串
• blob 二进制

sqlite支持的约束条件

• primary key：主键约束，非空且唯一
每张表都应该设置一个主键，找不到合适的主键的话，可以添加一个自增长列（只能是整型）来作为表的主键。
一张表最多只能有一个主键
• not null：非空约束
• unique：唯一约束，可以为空
• check：检查约束
• default：默认约束
• foreign key：外键约束
主要用于维护引用的完整性（默认没有开启，要想使用必须手动开启）
手动开启外键约束：PRAGMA foreign_keys = ON
外键列的值必须在主键列中存在，否则就会出错。
语法：字段名 数据类型 references 表名（字段名）

删除表

语法
drop table 表名

常用的指令

• .quit 退出
• .tables 查看数据库中所有表名
• .headers on/off 显示/隐藏表头
• .schema 表名 查看表结构
• select * from 表名 查看指定表内的所有数据（一般用于测试）

数据的操作

• 插入（insert）

语法
insert into 表名 (字段列表) values(值列表);

注意事项：
• 插入完整的一条数据时可以省略字段名
• 插入的列的个数和值的个数要对应
• 字符串都必须加上单引号，数值可加可不加
• 插入的数据必须符合建表时所规定的约束条件
• 自增长列我们不用手动去插入值

• 更新（update）

语法
update 表名 set 字段名1 = ？,字段名2 = ？, ... [where 条件];

• 删除（delete）

语法
delete from 表名 [where 条件];

注意事项：
• 不加条件默认删除表中所有的记录。
• 删除主键记录的时候，如果外键表有记录引用此记录的主键的时候会出错

• 查询

语法
select 列名1,列名2,...from 表名[where 条件]
[group by 列名] [having 条件] [order by 列名 asc/desc] [limit 初始位置,查询个数]

注意事项：
• 注意条件的顺序
• select from 表名：代表所有列，一般在测试的时候使用，开发中不使用
• 查询结果可以只显示指定的列

• 给列取别名

select name as 姓名,address as 家庭住址 from stu_info;
// as 可以省略
select name 姓名,address 家庭住址 from stu_info; 
select stu_info.name,stu_info.address from stu_info;
当多表查询的时候，可能不同表中有同名的列，所以查询同名列的时候必须列名之前加上表名。

• 给表取别名

select s.name,s.address from stu_info [as] s;

• 带条件的查询语句

select name,age,gender,address from stu_info where address='上海'; select name,age,gender,address from stu_info where age>=13;

多表查询

• 内联（inner join）
  要求：把两张表中满足条件的记录拼成一条记录。
  有两种写法：

//1)
select i.name,i.age,i.gender,i.address,c.math,c.english from stu_info as i,stu_score c where i.stuno=c.sno;
//2)
select i.name,i.age,i.gender,i.address,c.math,c.english from stu_info as i inner join stu_score as c on i.stuno=c.sno;

• 外联（outer join）
  • 左外查询（left [outer] join）
  以左表为基准表，先和右表做内联，接下来左表中匹配不到的记录也会拼成一条记录，只是值为null
  • 右外查询（right [outer] join）

sqlite不支持

• 交叉连接（cross join）没有连接条件。

示例：
select i.name,i.age,i.gender,i.address,c.math,c.english from stu_info as i cross join stu_score as c;

• 按列来进行排序

select name,age,gender,address from stu_info order by age asc;
select name,age,gender,address from stu_info order by age desc;
select name,age,gender,address from stu_info order by age desc,name desc;

以多个列来排序，先按age降序，再把age相同的记录按名字降序排序
获取指定个数的记录
要求：查询出班级中年龄最大的前三个学员的个人信息
select name,age,address from stu_info order by age desc limit 0,3;
查询指定字段值（不）为空的记录
要求：求没有参加英语考试的学员信息

//错误写法：
select sno from stu_score where english = null; 
//正确写法：
select sno from stu_score where english is null;
select sno from stu_score where english is not null;

• 聚合函数
  count（列名）、sum（列名）、max（列名）、min（列名），avg（列名）在做查询语句的时候，我们不能使用列名和聚合函数一起查询，因为列查询出来的个数可能有多个，而聚合函数的结构只有一个，不能组成一张规则的表。
• 分组查询
  要求：打印输出班级中每个城市所对应的学生数。
  注意事项：
  • 在分组中使用聚合函数，聚合函数统计的不是表中的所有记录，而是统计每个组中的记录
  • 在分组查询中，分组的列可以和聚合函数一起查询
  • 可以以多个列进行分组。

select address,count(*)as 人数 from stu_info group by address having 人数>1;

• where 和 having的区别？
  where在分组之前对表中所有的记录进行条件过滤，而having必须用在group by 之后，主要用来对组进行设置过滤条件。
  模糊查询
  关键字: like
  • % : 代表任意多个字符（0到多）
  • _ : 代表任意一个字符（1）
  示例：
  • 查询姓名中包含字母a的所有人
  select name,age,address from stu_info where name like '%a%';
  • 查询姓名中第二个字母为h的人员信息
  select name,age,address from stu_info where name like '_h%';

• 其他语句
  PRAGMA table_info(表名); 查看表的字段