iOS-sqlite数据库常用SQL语句

1.数据类型

NULL                   空
INTEGER                整形
REAL                   浮点
TEXT                   文本
BLOB                  二进制数据

没有对应的Boolean和Date类型，需要转成其他类型

2.运算符

2.1算术运算符

+        加    
-        减
*        乘
/        取除
%        取余

2.2比较运算符

=          等于    
!=        不等于
<>       不等于
>          大于
<          小于
>=        大于等于
<=         小于等于

2.3逻辑运算符

and        并 用于where子句多个条件同时存在 
or         或
between    在某个范围内 ，和and 搭配
exists     表中搜索行的存在。
in         把某个值与一系列指定列表的值进行比较
not in     in的对立面
like       用来匹配文本和数字 %：0、1或多个字符  _单一字符或数字
glob       用来匹配文本和数字区分大小写  *：0、1或多个字符  ？单一字符或数字
is         与=相似
is null    与null比较
||         连接两个不同的字符串，得到一个新的字符串。
unique     运算符搜索指定表中的每一行，确保唯一性（无重复）。

3.创建表

create table 表名(列名1 type,.....)

3.1.创建表并添加主键

create table 表名(列名1 type not null primary key ,列名2 type,.....)

3.2.创建表并自动添加主键

create table 表名(列名1 INTRGER primary key autoincrement ,列名2 type,.....)
autoincrement 自动递增

3.3.表不存在时才创建

create table if not exists 表名(列名1 INTRGER primary key autoincrement ,列名2 type,.....)

4.删除表

drop table 表名

5.插入

insert into 表名 values(值1,值2,值3.....)

注意：values后面应该将所有列的值附上，否则插入会失败

插入一条数据给部分列赋值，其余为空。not null修饰的列必须赋值
insert into 表名(列1,列2....) values(值1,值2.....)

6.查找

6.1查询表所有列的数据

select * from 表名

6.2.查询表某列的数据

select 列1,列2 from 表名

6.3.查询某列不重复的值

distinct 与select一起使用，消除所有重复记录

select distinct 列名 from 表名称

6.4.带条件查询

select 列名 from 表名 where 列 = xx

• like用法
  用来匹配文本和数字 %：0、1或多个字符 _单一字符或数字
  如：查询sh开头的结果
  select * from 表名 where 列名 like 'sh%'
  如：查询第二位为s的结果
  select * from 表名 where 列名 like '_s%'
• glob用法
  与like类似，区分大小写
  *：0、1或多个字符 ?:单一字符或数字
  如：查询第二位为S的结果
  select *from 表名 where 列名 glob '?S*'
• limit用法
  限制返回数据条数，与offset 配合可以从特定的偏移开始返回数据
  select *from 表名 limit 数据条数 offset 偏移
• order by 用法
  asc 升序(可选) desc 降序
  升序
  select * from 表名 order by 列名
  降序
  select * from 表名 order by 列名 desc
• group by 用法
  SQLite 的 group by 子句用于与 select 语句一起使用，来对相同的数据进行分组。
  在 select 语句中，group by 子句放在 where 子句之后，放在 order by 子句之前。
  如：select 列 函数... group by 列 order by 列 desc
• having 用法
  having 子句允许指定条件来过滤将出现在最终结果中的分组结果。与group by 混合使用。
  select * group by 列 having xxxxx
  如：select * group by name having count(name)<2
• 函数用法
  count(列) 数据库列的行数
  max(列) 某列最大值
  min(列) 某列最大值
  avg(列) 平均值
  sum(列) 某列总和
  abs() 取绝对值
  upper() 转成大写
  lower() 转小写
  length() 返回字符串长度
  如：获取表的行数
  select count(*) from 表名

7.修改

update 表 set 列 = xx ... where 条件

• 条件见查询

8.删除

8.1.按条件删除表中的数据

delete form 表 where 条件

• 条件见查询

8.2.删除表中所有数据，保留列信息

truncate table 表
但这种方法无法将递增数归零。如果要将递增数归零，可以使用以下方法：
delete from sqlite_sequence where name = '表名'

8.3.删除表包括列信息

drop table 表

8.4.删除数据库

drop database 数据库名

9其他

9.1约束

not null 非空
default 设置默认值
unique 防止记录两个相同的值
primary key 主键，约束唯一标识数据库表中的每个记录。在一个表中可以有多个 UNIQUE 列，但只能有一个主键
check 检查值
如：
create table 表 （ID integer primary key not null , age integer check(age>=0),idcode integer unique not null）

9.2.Join

SQL 定义了三种主要类型的连接：

• 交叉连接 - CROSS JOIN
• 内连接 - INNER JOIN
• 外连接 - OUTER JOIN
  1)交叉连接：把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行
  select ... from 表1 cross join 表2
  2）内连接：内连接根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。INNER 关键字是可选的。
  select ... from table1 join table2 on 条件
  3）外连接
  外连接是内连接的扩展。SQLite 只支持 左外连接（LEFT OUTER JOIN），左外连接在内连接的基础上会将table1中任何未连接的行合并进来，外连接的列使用 NULL 值，将它们附加到结果表中。
  select ... from table1 left outer join on 条件

9.3alter

在 SQLite 中，除了重命名表和在已有的表中添加列，ALTER TABLE 命令不支持其他操作。
1)表重命名
alter table 表名 rename to 表名
2）新增列
alter table 表名 add column 列名 类型 描述

9.4unions

1）SQLite的 unions 子句/运算符用于合并两个或多个 selete 语句的结果，不返回任何重复的行。为了使用 unions，每个 selete 被选择的列数必须是相同的，相同数目的列表达式，相同的数据类型，并确保它们有相同的顺序，但它们不必具有相同的长度。
select 列 from 表 union select 列 from 表
2）union all 运算符用于结合两个 selete 语句的结果，包括重复行。
select 列 from 表 union all select 列 from 表

9.5子查询

字查询的结果可以作为另一个语句的输入
以下是子查询必须遵循的几个规则：
1）子查询必须用括号括起来。
2）子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。
3）ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。
4）子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。
BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。
如:
select 列 from 表 where id in (select id from 表 条件)

9.6索引

索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。
1）添加单列索引
create unique index 索引名 on 表名(列名)
2）添加组合索引
create index 索引名 on 表名(列名,列名...)
3）隐私索引
隐式索引是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。
4)删除索引
drop index 索引名

9.7触发器

1.SQLite 的触发器（Trigger）可以指定在特定的数据库表发生 DELETE、INSERT 或 UPDATE 时触发，或在一个或多个指定表的列发生更新时触发。
2.如果提供 WHEN 子句，则只针对 WHEN 子句为真的指定行执行 SQL 语句。如果没有提供 WHEN 子句，则针对所有行执行 SQL 语句。
3.BEFORE 或 AFTER 关键字决定何时执行触发器动作

• 创建触发器
  1）语句执行前触发
  creat trigger 触发器名 before update of 表名
  1）语句执行后触发触发
  creat trigger 触发器名 after update of 表名

9.8事务

事务（Transaction）是指一个或多个更改数据库的扩展。例如，如果您正在创建一个记录或者更新一个记录或者从表中删除一个记录，那么您正在该表上执行事务。重要的是要控制事务以确保数据的完整性和处理数据库错误。

• 开始事务
  begin transaction
• commit
  end transaction
• rollback
  rollback

9.9日期

1. 日期 （以 YYYY-MM-DD 格式返回日期）
   date(timestring,modifier...)
   2)时间 （以 HH:MM:SS 格式返回时间。）
   time(timestring, modifier, modifier, ...)
   3）日期时间 （以 YYYY-MM-DD HH:MM:SS 格式返回。）
   datetime(timestring, modifier, ...)
   4）指定格式时间
   strftime(format, timestring, modifier, ...)
   timestring说明
   1 YYYY-MM-DD
   2 YYYY-MM-DD HH:MM
   3 YYYY-MM-DD HH:MM:SS.SSS
   4 MM-DD-YYYY HH:MM
   5 HH:MM
   6 YYYY-MM-DDTHH:MM
   7 HH:MM:SS
   8 YYYYMMDD HHMMSS
   9 now
   如 2019-01-01
   modifier说明
   1 NNN days
   2 NNN hours
   3 NNN minutes
   4 NNN.NNNN seconds
   5 NNN months
   6 NNN years
   7 start of month
   8 start of year
   9 start of day
   10 weekday N
   11 unixepoch
   12 localtime
   13 utc
   如 -1 days 标识一天前
   format说明
   1 %d 一月中的第几天，01-31
   2 %f 带小数部分的秒，SS.SSS
   3 %H 小时，00-23
   4 %j 一年中的第几天，001-366
   5 %J 儒略日数，DDDD.DDDD
   6 %m 月，00-12
   7 %M 分，00-59
   8 %s 从 1970-01-01 算起的秒数
   9 %S 秒，00-59
   10 %w 一周中的第几天，0-6 (0 is Sunday)
   11 %W 一年中的第几周，01-53
   12 %Y 年，YYYY
   13 %% % symbol
   如 %Y%m%d 20190101

最近把常用的一些SQL语句进行了总结，有不对的地方还望指出。谢谢