Sqlite语法学习笔记

SQLite 子查询 | 菜鸟教程

1.常用的文本处理函数：
length() 返回字符串的长度
lower() 将字符串转换为小写
upper() 将字符串转换为大写
2.常用的聚集函数 用于检索数据，以便分析和生成报表
avg() 返回某列的平均值
count() 返回某列的行数 COUNT(*) 包括有NULL值的列  COUNT(表达式) 返回表达式中非NULL值的数量
max() 返回某列的最大值
min() 返回某列的最小值
sum() 返回某列值之和
3.数据分组 group by 分组数据，以便能汇总表内容的子集，常和聚集函数搭配使用。例如查询每个班级中的人数、平均分
例:select count(*) from names group by 班级
使用：select 列名1[, 列名2, ...] from 表名 group by 列名

4.过滤分组 having   除了能用group by分组数据外，还可以包括哪些分组，排除哪些分组。例如：查看班级平均分大于90的班级
例:select count(*) from names group by 班级 having age>15
select 函数名（列名1）[, 列名2, ...] from 表名 group by 列名 having 函数名 限制值

5.约束 管理如何插入或处理数据库数据的规则
主键约束 （primary key）惟一的标识一行(一张表中只能有一个主键) 主键应当是对用户没有意义的（常用于索引）
语法：例:create table names(name char(10) primary key); 
create table 表名称 (列名称1 数据类型 primary key, 列名称2 数据 类型,列名称3 数据类型, ...)；

唯一约束
用来保证一个列（或一组列）中数据唯一，类似于主键，但跟主键有区别
表可包含多个唯一约束，但只允许一个主键
唯一约束列可修改或更新 创建表时，通过unique来设置 例:create table names(name char(10) unique); 
create table 表名 (列名称1 数据类型 unique[，列名称2 数据类型 unique,...]);

检查约束 用来保证一个列（或一组列）中的数据满足一组指定的条件。
指定范围，检查最大或最小范围，通过check实现 例:create table names (age int check (age>0 and age<100)); 
create table 表名 (列名 数据类型 check (判断语句));  

自增字段修饰 AUTOINCREMENT 

默认值: DEFAULT  xx

6.联结表（多表操作）保存数据时往往不会将所有数据保存在一个表中，而是在多个表中存储，联结表就是从多个表中查询数据。
在一个表中不利于分解数据，也容易使相同数据出现多次，浪费存储空间；使用联结表查看各个数据更直观，这使得在处理数据时更简单。
select * from 表1,表2 where 表1.id=表2.id;

7.order by 语句 默认按照升序对结果集进行排序，可使用 desc 关键字按照降序对结果集进行排序。
升序: select * from 表名 order by 列名;
降序: select * from 表名 order by 列名 desc;

8.视图  视图不包含数据，因此在每次使用视图时，实际上都必须执行查询语句，从返回结果信息中再检索.视图与表一样,必须唯一命名(通过.tables和.schema查看)
语法：create view 视图名 as 语句;
例:create view 视图1 as select * from 表1,表2 where 表1.id=表2.id;
例:select * from 视图1 order by 列名;

删除视图：
语法:drop view 视图名;
例:drop view 视图1
总结：
1、视图不包含数据，因此在每次使用视图时，实际上都必须执行查询语句
2、视图相当于 创建视图的时候 as后面SQL语句查询得到的结果集合。
3、从返回结果信息（视图）中再检索视图与表一样

9.表的高级操作

1、表与表连接 指定从指定表查询指定字段数据

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行 在两个关联表都有数据的时候才会查询出数据，如果其中一个表没有数据，另外一个表的数据也会被抛弃。

FULL [OUTER] JOIN 完全连接

SELECT 表1.name 表2.age FROM 表1 inner join 表2 on 表1.id=表2.id

2、表与查询结果集连接

SELECT name FROM TableA UNION SELECT name FROM TableB

3、根据现有表创建表

CREATE TABLE 表2 AS SELECT id, name FROM 表1; 

4.修改表名称   (sqlite不支持字段名称的改变，但是可以新增字段)

ALTER TABLE 旧表名 RENAME TO 新表名 

eg: ALTER TABLE or_sql_table RENAME TO new_table;

5.添加字段

ALTER TABLE 表名 ADD COLUMN 列名 数据类型 

ALTER TABLE 'IPC_FGUID' ADD 'iPassageway' VARCHAR(100) DEFAULT 0;

6.查询表结构

PRAGMA TABLE_INFO (表名)

eg: PRAGMA TABLE_INFO (new_table);

7.修改表结构字段类型

SQLite 仅仅支持 ALTER TABLE 语句的一部分功能，我们可以用 ALTER TABLE 语句来更改一个表的名字，也可向表中增加一个字段（列），但是我们不能删除一个已经存在的字段，或者更改一个已经存在的字段的名称、数据类型、限定符等等。 

而修改一列无法像其他数据库那样直接以“ALTER TABLE 表名 ADD COLUMN 列名 数据类型”的方式来完成，所以要换种思路，具体步骤看下面:

--1.将表名改为临时表
ALTER TABLE "Student" RENAME TO "_Student_old";
--2.创建新表

CREATE TABLE "Student" ("Id"  INTEGER PRIMARY KEY AUTOINCREMENT, "Name"  Text);
--3.导入数据

INSERT INTO "Student" ("Id", "Name") SELECT "Id", "Title" FROM "_Student_old";
--4.更新sqlite_sequence

UPDATE "sqlite_sequence" SET seq = 3 WHERE name = 'Student';

由于在Sqlite中使用自增长字段,引擎会自动产生一个sqlite_sequence表,用于记录每个表的自增长字段的已使用的最大值，所以要一起更新下。如果有没有设置自增长，则跳过此步骤。
--5.删除临时表(可选)

DROP TABLE _Student_old;
 

事务控制

使用下面的命令来控制事务：

• BEGIN 或 BEGIN TRANSACTION：开始事务处理。

• COMMIT：保存更改，或者可以使用 END TRANSACTION 命令。

• ROLLBACK：回滚所做的更改。

SQLite Distinct 关键字

SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。

有可能出现一种情况，在一个表中有多个重复的记录。当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。