iOS-数据库-SQL

ios数据库:
CoreData、 自带的框架
SQLite、
FMDB ：第三方类库

SQL（structured query language）：结构化查询语言
无服务器的、零配置的、事务性的 SQL 数据库引擎

不区分大小写 但最好关键字大写
每条语句都必须以分号 ; 结尾

局限性：
不支持的特性 如：RIGHT OUTER JOIN （但支持 LEFT OUTER JOIN）

常用关键字有
select、insert、update、delete、from、create、where、desc、order、by、group、table、alter、view、index等等
数据库中不可以使用关键字来命名表、字段

SQL语句的种类：
1.数据定义语句（DDL：Data Definition Language）
包括create和drop ALTER等操作
在数据库中创建新表或删除表（create table或 drop table）
ALTER 修改
ALTER TABLE table_name RENAME TO new_table_name;
ALTER TABLE table_name ADD COLUMN column_def...;

2.数据操作语句（DML：Data Manipulation Language）
包括insert、update、delete等操作
上面的3种操作分别用于添加、修改、删除表中的数据

3.数据查询语句（DQL：Data Query Language）
可以用于查询获得表中的数据
关键字select是DQL（也是所有SQL）用得最多的操作

其他DQL常用的关键字有
子句：
DISTINCT EXISTS GLOB IN NOT IN Like ORDER BY WHERE GROUP BY HAVING
BETWEEN COUNT AND/OR

create table if not exists 表名 (字段名1 字段类型1, 字段名2 字段类型2, …) ;
create table t_student (id integer, name text, age integer, score real) ;

SQLite将数据划分为以下几种存储类型：
NULL ： NULL 值
integer : 整型值
real : 浮点值
text : 文本字符串
blob : 二进制数据（比如文件）
没有 Boolean 没有用于存储日期和/或时间的类型，可用其他类型代替
实际上SQLite是无类型的，就算声明为integer类型，还是能存储字符串文本（主键除外）
建表时声明啥类型或者不声明类型都可以，也就意味着创表语句可以这么写：
create table t_student(name, age);

drop table 表名 ;
drop table if exists 表名 ;
drop table t_student ;

insert into 表名 (字段1, 字段2, …) values (字段1的值, 字段2的值, …) ;
insert into t_student (name, age) values (‘mj’, 10) ;
数据库中的字符串内容应该用单引号 ’ 括住
为所有列添加值 可省略字段
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

使用一个表来填充另一个表：
INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];

update 表名 set 字段1 = 字段1的值, 字段2 = 字段2的值, … ;
update t_student set name = ‘jack’, age = 20 ;

delete from 表名 ;
delete from t_student ;

运算符：
算术运算符: + - * / %
比较运算符: == = != <> > < >= <= !< !>
逻辑运算符: AND BETWEEN EXISTS IN NOT IN LIKE GLOB OR IS NULL IS IS NOT || UNIQUE
位运算符: & | ~ << >>
GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。
GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
百分号 （%）
下划线 （）
百分号（%）代表零个、一个或多个数字或字符。下划线（）代表一个单一的数字或字符。
SELECT * FROM COMPANY WHERE AGE LIKE '2%';
星号 （）
问号 （?）
星号（）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符。
SELECT * FROM COMPANY WHERE AGE GLOB '2*';

== 检查两个操作数的值是否相等，如果相等则条件为真。 (a == b) 不为真。
<> 检查两个操作数的值是否相等，如果不相等则条件为真。 (a <> b) 为真。
where 字段 = 某个值 ; // 不能用两个 =
where 字段 is 某个值 ; // is 相当于 =
where 字段 != 某个值 ;
where 字段 is not 某个值 ; // is not 相当于 !=
where 字段 > 某个值 ;
where 字段1 = 某个值 and 字段2 > 某个值 ; // and相当于C语言中的 &&
where 字段1 = 某个值 or 字段2 = 某个值 ; // or 相当于C语言中的 ||
SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
SELECT * FROM COMPANY
WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

where
用于指定从一个表或多个表中获取数据的条件。
select name, age from t_student ;
select * from t_student ;
select * from t_student where age > 10 ; // 条件查询

update t_student set age = 5 where age > 10 and name != ‘jack’ ;
UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;
delete from t_student where age <= 10 or age > 30 ;
DELETE FROM COMPANY; //删除所有记录

select ： 以结果表的形式返回数据
select 字段1, 字段2, … from 表名 ;
select * from 表名; // 查询所有的字段

起别名:
(用在多个表 联合查询)
select 字段1 别名 , 字段2 别名 , … from 表名 别名 ;
select 字段1 别名, 字段2 as 别名, … from 表名 as 别名 ;
select 别名.字段1, 别名.字段2, … from 表名 别名 ;

select name myname, age myage from t_student ;
给name起个叫做myname的别名，给age起个叫做myage的别名
select s.name, s.age from t_student s ;
给t_student表起个别名叫做s，利用s来引用表中的字段

常用函数
COUNT MAX MIN AVG SUM RANDOM ABS UPPER LOWER LENGTH sqlite_version
count
select count (字段) from 表名 ;
select count ( * ) from 表名 ;
select count (age) from t_student ;
select count ( * ) from t_student where score >= 60;
SELECT count(*) FROM COMPANY;
SELECT max(salary) FROM COMPANY;
SELECT min(salary) FROM COMPANY;
SELECT avg(salary) FROM COMPANY;
SELECT sum(salary) FROM COMPANY;
SELECT upper(name) FROM COMPANY;
SELECT lower(name) FROM COMPANY;
SELECT name, length(name) FROM COMPANY; //返回name存的值的length
SELECT random() AS Random;

order by:
子句是用来基于一个或多个列按升序或降序顺序排列数据。
select * from t_student order by 字段 ;
select * from t_student order by age ;
select * from t_student order by age desc ; //降序
select * from t_student order by age asc ; // 升序（默认）
select * from t_student order by age asc, height desc ;
SELECT * FROM COMPANY ORDER BY SALARY ASC;

limit:
限制由 SELECT 语句返回的数据数量。
select * from 表名 limit 数值1, 数值2 ;
select * from t_student limit 4, 8 ; //第n页：limit 5*(n-1), 5
select * from t_student limit 7 ;
相当于select * from t_student limit 0, 7 ;表示取最前面的7条记录

GROUP BY :
用于与 SELECT 语句一起使用，来对相同的数据进行分组
GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME; 相同的NAME的SALARY会加起来
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

HAVING
过滤将出现在最终结果中的分组结果
HAVING 子句在 SELECT 查询中的位置：
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前。
SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2; //返回name个数小于2的

DISTINCT
消除所有重复的记录，并只获取唯一一次记录。
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]
SELECT DISTINCT name FROM COMPANY;

约束：

not null ：规定字段的值不能为null
unique ：规定字段的值必须唯一
default ：指定字段的默认值
PRIMARY Key 主键唯一 只能有一个主键
CHECK
autoincrement 自动增长
主键字段默认就包含了not null 和 unique 两个约束

create table t_student (id integer primary key autoincrement, name text not null unique, age integer not null default 1) ;

外键约束:
外键的一般情况是：一张表的某个字段，引用着另一张表的主键字段
create table t_student (id integer primary key autoincrement, name text, age integer, class_id integer, constraint fk_student_class foreign key (class_id) references t_class (id));
t_student表中有一个叫做fk_t_student_class_id_t_class_id的外键
这个外键的作用是用t_student表中的class_id字段引用t_class表的id字段

表连接查询 / 联合查询
内连接：inner join 或者 join （显示的是左右表都有完整字段值的记录）
左外连接：left outer join （保证左表数据的完整性）
select s.name,s.age from t_student s, t_class c where s.class_id = c.id and c.name = ‘0316iOS’;

JOIN
通过共同值来结合两个表中字段的手段
交叉连接 - CROSS JOIN
内连接 - INNER JOIN (INNER 可省略)
外连接 - OUTER JOIN
SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
如果两个输入表分别有 x 和 y 列，则结果表有 x*y 列

JOIN...USING
JOIN...ON
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL
SQLite 只支持 左外连接（LEFT OUTER JOIN）
外连接（OUTER JOIN）声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 关键字来表达。
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

UNION
合并两个或多个 SELECT 语句的结果，不返回任何重复的行
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

UNION ALL
运算符用于结合两个 SELECT 语句的结果，包括重复行
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION ALL
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

NULL
NULL 值与零值或包含空格的字段是不同的
UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);
SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NOT NULL;
SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NULL;

别名
别名的关键字 as 可以被省略
SELECT C.ID, C.NAME, C.AGE, D.DEPT
FROM COMPANY AS C, DEPARTMENT AS D
WHERE C.ID = D.EMP_ID;

Trigger
触发器（Trigger）是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。

索引（Index）
加快数据检索

ALTER
修改表结构，不修改表里的数据
ALTER TABLE COMPANY RENAME TO OLD_COMPANY;
ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

Truncate Table

视图（View）

事务（Transaction）
BEGIN TRANSACTION：开始事务处理。
COMMIT：保存更改，或者可以使用 END TRANSACTION 命令。
ROLLBACK：回滚所做的更改。

子查询
SELECT子查询
SELECT *
FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY
WHERE SALARY > 45000) ;
INSERT子查询
INSERT INTO COMPANY_BKP
SELECT * FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY) ;
UPDATE子查询
UPDATE COMPANY
SET SALARY = SALARY * 0.50
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE >= 27 );
DELETE子查询
DELETE FROM COMPANY
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE > 27 );

日期 & 时间
date 以 YYYY-MM-DD 格式返回日期。
time 以 HH:MM:SS 格式返回时间。
datetime 以 YYYY-MM-DD HH:MM:SS 格式返回。
strftime