SQLite约束以及连接

前面介绍了SQLite的初步学习，学习了建表，以及对表内容的增删改出等操作，本篇文章深入学习一下SQLite。

SQLite约束

由于开始学习过Oracle数据库的基础知识，对于约束问题有一些初步了解，约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型，确保了数据库中数据的准确性和可靠性。

约束可以是列级或表级，列级约束仅适用于列，表级约束被应用到整个表

常用约束：

约束	说明
NOT NULL	确保某列不能有NULL值
DEFAULT	当某列没有指定值时，为该列提供默认值
UNIQUE	确保某列中的所有值是不同的
PRIMARY KEY	唯一标识数据库表中的各行记录
CHECK	确保某列中的所有值满足一定条件

CREATE TABLE SCHOOL(
ID    INT PRIMARY KEY     NOT NULL,
NAME  TEXT                NOT NULL,
AGE   INT                 NOT NULL,
ADDRESS   CHAR(50),      
SALARY    REAL            CHECK(SALARY>0),
GREAD     INT             DEFAULT 100.00,
WEIGHT    INT             NOT NULL UNIQUE
);

SQLite中，ALTER TABLE命令允许用户重命名表，或者在表中添加一个新的列，但是重命名列和删除一列，添加以及删除约束无法完成。

SQLite Joins

SQLite的Joins子句用于结合两个或多个数据库中表的记录，JOIN是一种通过共同值来结合两个表中字段的手段。

SQL的三个主要类型连接：
1.交叉连接——CROSS JOIN
2.内连接——INNER JOIN
3.外连接——OUTER JOIN

为了说明这三种连接方式，先创建两个表：

表一

CREATE TABLE COMPANY(
ID   INT PRIMARY KEY      NOT NULL,
NAME TEXT                 NOT NULL,
AGE  INT                  NOT NULL,
ADDRESS     CHAR(50),
SALARY      REAL
);

信息：

sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   ...> VALUES(1,'Paul',32,'Califronia',20000.00);
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   ...> VALUES(2,'Allen',25,'Texas',15000.00);          
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   ...> VALUES(3,'Teddy',23,'Norway',20000.00);         
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   ...> VALUES(4,'Mark',25,'Rich-Mond',65000.00);       
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   ...> VALUES(5,'David',27,'Texas',85000.00);          
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   ...> VALUES(6,'Kim',22,'South-Hall',45000.00);
sqlite> INSERT INTO COMPANY values(7,'James',24,'Houston',10000.00);

然后设置输出格式，显示我们的表格信息：

sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM COMPANY;  
ID          NAME        AGE         ADDRESS     SALARY    
----------  ----------  ----------  ----------  ----------
1           Paul        32          Califronia  20000.0   
2           Allen       25          Texas       15000.0   
3           Teddy       23          Norway      20000.0   
4           Mark        25          Rich-Mond   65000.0   
5           David       27          Texas       85000.0   
6           Kim         22          South-Hall  45000.0   
7           James       24          Houston     10000.0   

表二

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

信息：

sqlite> INSERT INTO DEPARTMENT VALUES(1,'IT Billing', 1);
sqlite> INSERT INTO DEPARTMENT VALUES(2,'Engineering', 2);           
sqlite> INSERT INTO DEPARTMENT VALUES(3,'Finance', 7); 

显示表格信息：

sqlite> SELECT * FROM DEPARTMENT;
ID          DEPT        EMP_ID    
----------  ----------  ----------
1           IT Billing  1         
2           Engineerin  2         
3           Finance     7         

交叉连接——CROSS JOIN

交叉连接就是把第一个表的每一行与第二个表的每一行进行匹配，交叉连接有可能产生非常大的表，很少使用。

语法：

SELECT ... FROM table1 CROSS JOIN table2 ...

基于上面的表，写一个交叉连接：

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

结果如下：

sqlite> SELECT EMP_ID,NAME,DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
EMP_ID      NAME        DEPT      
----------  ----------  ----------
1           Paul        IT Billing
2           Paul        Engineerin
7           Paul        Finance   
1           Allen       IT Billing
2           Allen       Engineerin
7           Allen       Finance   
1           Teddy       IT Billing
2           Teddy       Engineerin
7           Teddy       Finance   
1           Mark        IT Billing
2           Mark        Engineerin
7           Mark        Finance   
1           David       IT Billing
2           David       Engineerin
7           David       Finance   
1           Kim         IT Billing
2           Kim         Engineerin
7           Kim         Finance   
1           James       IT Billing
2           James       Engineerin
7           James       Finance   

不难发现，NAME是表一中的，EMP_ID和DEPT是表二中的，因此将表一每一行中的NAME与表二每一行的EMP_ID和DEPT进行匹配，即得出了交叉连接的结果表。

内连接——INNER JOIN

内连接根据条件结合两个表的列值来创建一个新的结果表，将表1的没一行和表2的每一行比较，满足条件则合并起来。

语法：

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

INNER关键字可选。

也可用USING表达式声明内连接条件：

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...

自然连接（NATURAL JOIN）类似于JOIN…USING，会自动测试存在两个表中的每一列的值之间相等值：

SELECT ... FROM table1 NATURAL JOIN table2...

现基于上面的表，写一个内连接的例子：

sqlite> SELECT EMP_ID,NAME,DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; 
EMP_ID      NAME        DEPT      
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineerin
7           James       Finance   

这是根据表1的ID和表2的EMP_ID相等的行的结果表。

外连接——OUTER JOIN

SQL标准定义了三种类型的外连接，LEFT、RIGHT、FULL，但是SQLite只支持左外连接，即LEFT OUTER JOIN。

语法：

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

可用USING声明外连接条件：

SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...

外连接的例子：

sqlite> SELECT EMP_ID,NAME,DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
EMP_ID      NAME        DEPT      
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineerin
            Teddy                 
            Mark                  
            David                 
            Kim                   
7           James       Finance   

这里ID号不同的NAME也查找出来了，这就是外连接。