数据库的基本操作

基础知识

1. 数据库是什么？

程序在运行的过程中，数据都是在内存中存储的

，但是内存一旦断电就会丢失数据。因此需要持久保存的数据会转交给硬盘。

在编程领域，最常用的方式是使用数据库批量的存储大量数据到硬盘。也就是说，数据库是一种电子化的资料柜。

数据库产品非常多，主流的包括：MySQL、SQLite、Oracle......这些不同的数据库都是由不同的软件公司开发而来，每种数据库各有差别。IBM公司针对市面上各种不同厂家的数据库推出了统一的操作语言——SQL语言（语句）。

嵌入式领域中最常用的数据库产品是SQLite，本次学习的数据库就是SQLite。

SQLite是一款轻量级的数据库

，只需要数兆的体积，免安装即可使用，因此很多嵌入式相关的技术框架都会内置SQLite数据库。

2. 操作环境

本次学习为了简化操作，使用SQLiteSpy软件作为开发环境，软件的布局如下。

SQLite数据库是以单文件进行存储的，一个数据库就是一个文件，文件的格式是.db或.db3，只需要使用SQLiteSpy软件打开.db或.db3文件即可。

打开之后可以看到有四张表：dept、emp、salgrade和bonus。

如果把数据库文件看做是excel文件，上面的四张表就相当于excel中的sheet。

3. 数据库操作类型

IBM把SQL语句分为以下几类：

• DML（数据操作语言）

主要指的是数据的查询（DQL）和更新（增删改）操作，这是本次学习的重点。

• DDL（数据定义语言）

用于定义数据库对象，主要用于设计数据库表的结构。

• DCL（数据控制语言）

用于控制数据库操作权限，一般用于高级的数据库，会由公司的数据库管理员负责。

4. 查看数据表

在SQLiteSpy软件中，只需要双击表名，就可以查询到表中的内容。

下面依次介绍四张表。

4.1 dept表

存储公司的部门信息。

整理表结构如下所示。

SQLiteSpy软件双击表名查询内容，实际上是执行了下面的语句。

SELECT * FROM dept;

把上面的语句粘贴到SQLiteSpy执行区域，手动执行也会有相同的效果。

编写SQL语句也可以在其它软件中进行，例如Notepad++，只需要切换到SQL模式即可。

4.2 emp表

emp表是雇员表，记录了公司里员工的信息。

整理表结构如下所示。

4.3 salgrade表

salgrade表是工资等级表。

4.4 bonus表

bonus表是奖金表。

bonus表是一张空表，只有表结构，没有内容。

5. 需要记住的内容

内容	掌握程度
表名	英汉互译
列名	英汉互译
员工的职位job对应的值	英译汉

一、单表查询

这个篇章主要讲解的是在一个表中进行数据查询。

1. 简单查询（列查询）

简单查询的结果中包含所有的条目（行），依次展示每条数据，唯一能做的是限制表中出现的列。

语法结构如下。

其中[]表示可选填内容

DISTINCT表示去掉重复行的数据

*表示查询所有的列，如果不想查询所有列，则编写具体的列名称。

【例子】查询所有雇员的编号（empno）、姓名（ename）、职位（job）、基本工资（sal）。

SELECT empno,ename,job,sal FROM emp;

【例子】查询每个雇员的编号、姓名和基本年薪（基本月薪x12）

SELECT empno,ename,sal*12 FROM emp;

也可以更改显示的表名，如下所示。

SELECT empno 编号,ename 姓名,sal*12 年薪 FROM emp;

→

【例子】查询每个雇员的编号、姓名、职位、年薪，每个雇员每个月有200元餐补、200元交通补助、夏天四个月有每月300元高温补贴，年底多发3个月基本工资。

SELECT empno,ename,job,12*(sal+400)+4*300+3*sal income 
FROM emp;

【例子】查询所有雇员的职位有哪些。

可能会写出这样的语句。

SELECT job FROM emp;

实际上此题消除重复项更为合适，可以增加DISTINCT。

SELECT DISTINCT job FROM emp;

需要注意的是，只有所有内容都重复才算重复项。

2. 限定查询

限定查询是在简单查询的基础上限制显示的条数（行数），其语法格式如下。

主要通过WHERE子句限制行数，支持六种运算：

• 关系运算
• 取值范围运算
• 基数范围运算
• 模糊查询
• 空判断
• 逻辑运算

2.1 关系运算

关系运算是最简单的运算符号，包括：

>、<、>=、<=、!=、<>（不等于）、==

【例子】查询公司工资高于2000的员工信息。

SELECT * FROM emp WHERE sal>2000;

【练习】

1.查询基本工资小于1600的全部雇员信息

SELECT * FROM emp WHERE sal<1600;

2.查询姓名是SMITH的雇员信息

SELECT * FROM emp WHERE ename=='SMITH';

文本数据要使用双引号或单引号包裹，建议在数据库中使用单引号，在编程中时使用双引号。

3.查询所有职位不是销售人员的信息

SELECT * FROM emp WHERE job != 'SALESMAN';

2.2 取值范围运算

取值范围运算使用BETWEEN...AND...

可以表示两个数的范围区间，这个区间是闭区间，包含两端。

【例子】查询所有在1981年雇佣的雇员信息。

分析：1981年1月1日-1981年12月31日

SELECT * FROM emp
WHERE hiredate BETWEEN '1981-01-01' AND '1981-12-31';

【练习】查询工资范围在1200-3000之间的雇员姓名和职位。

SELECT ename,job FROM emp WHERE sal BETWEEN 1200 AND 3000;

2.3 基数范围运算

基数范围运算使用IN关键字，表示符合给定的多个数字之一。

【例子】查询出雇员编号为7369、7566、7839、8899（不在）的雇员信息。

SELECT * FROM emp WHERE empno IN (7369,7566,7839,8899);

如果一个数据没有，可以使用NULL表示，IN给的待选数据中不允许出现NULL，NULL是一种特殊的数据状态，需要使用专用语句进行判断。

2.4 模糊查询

模糊查询可以制定查询的格式，无需进行完整的内容匹配，主要使用LIKE关键字，配合两个符号进行标记。

• _

英文下划线，表示任意一个字符。

• %

百分号，表示任意多个（0,1,2，......，n）字符。

【例子】查询所有姓名是A开头的雇员信息。

SELECT * FROM emp WHERE ename LIKE 'A%';

【练习】

1. 查询所有姓名第二个字母是A的所有雇员信息

SELECT * FROM emp WHERE ename LIKE '_A%';

2. 查询姓名中包含A的所有雇员的信息

SELECT * FROM emp WHERE ename LIKE '%A%';

2.5 空判断

NULL是一种特殊的数值，使用IS NULL来进行判断。

在数据库中，反转逻辑结果使用NOT关键字，IS NULL的反转格式有两种：

• NOT IS NULL
• IS NOT NULL

【例子】查询不领取佣金(comm)的雇员信息。

SELECT * FROM emp WHERE comm IS NULL;

【例子】查询领取佣金的雇员信息。

-- 注意NOT的位置
SELECT * FROM emp WHERE NOT comm IS NULL;

SELECT * FROM emp WHERE comm IS NOT NULL;

2.6 逻辑运算

计算机中都有逻辑运算：与或非。

与：所有的条件都满足，结果才满足，在数据库中与使用AND表示。

或：只要满足一个条件，结果就满足，在数据库中或使用OR表示。

非：结果反转，在数据库中非使用NOT表示。

【例子】查询所有工资高于1300的销售信息。

分析：需要同时满足职位是销售 且 工资大于1300

SELECT * FROM emp WHERE job='SALESMAN' AND sal>1300;

【练习】

1. 查询出工资范围不在1200~1300（闭区间）之间的员工信息。

SELECT * FROM emp WHERE sal NOT BETWEEN 1200 AND 1300;

2. 查询出10部门的经理信息。

SELECT * FROM emp WHERE deptno=10 AND job='MANAGER';

3. 查询出工资高于3000，或者职位是柜员的全部雇员信息。

SELECT * FROM emp WHERE job='CLERK' OR sal>3000;

4. 查询出所有职位不是柜员的信息

SELECT * FROM emp WHERE NOT job='CLERK';

3. 查询排序

SQLiteSpy提供了一键排序的功能，只需要点击表名就可以切换顺序或倒序排序的方式。

因为以后数据库是在代码中操作的，因此仍然需要学习语法规则，查询排序的语法结构如下所示。

两种排序的方式：

• 升序 ASC
• 降序 DESC

【例子】查询所有雇员的信息，要求按照工资从高到低排序。

分析：只要是排序题，排序永远是最后一步。

SELECT * FROM emp ORDER BY sal DESC;

可以看到有的人工资是一样，排序支持多个字段排序，如果第一字段值相同，则使用第二字段值的排序规则。

【例子】查询所有雇员的信息，要求按照工资从高到低排序，如果工资相同，则按照雇佣日期从早到晚排序。

SELECT * FROM emp ORDER BY sal DESC,hiredate ASC;

【练习】

1. 查询所有雇员的信息，结果按照年薪（基本工资x12）排序，年薪越高的越靠前。

注意：SELECT中出现了*之后，不允许写其他列名。

SELECT empno,ename,job,mgr,hiredate,sal*12 sal,comm,deptno 
FROM emp ORDER BY sal DESC

2. 查询所有柜员的信息，同时按照工资从高到低排序。

SELECT * FROM emp WHERE job='CLERK' ORDER BY sal DESC;

3. 查询所有20部门的雇员信息，要求按照雇员日期从晚到早排序。

SELECT * FROM emp WHERE deptno=20 ORDER BY hiredate DESC;

4. 查询所有10部门和30部门的雇员信息，要求按照雇员编号降序排序。

SELECT *
FROM emp
WHERE deptno=10 OR deptno=30
ORDER BY empno DESC;

4. 练习

--1. 查询部门30的所有员工信息。
SELECT * FROM emp WHERE deptno=30;

--2. 列出所有柜员（CLERK）的姓名、编号和部门编号。
SELECT ename,empno,deptno FROM emp WHERE job='CLERK';

--3. 找出部门30中所有经理和部门20中所有柜员的详细资料。
SELECT * FROM emp 
WHERE deptno=30 AND job='MANAGER' OR deptno=20 AND job='CLERK';

--4. 收取佣金的职位有哪些？
SELECT DISTINCT job FROM emp WHERE comm IS NOT NUll;

--5. 查询不收取佣金或佣金低于100的员工信息。
SELECT * FROM emp WHERE comm IS NULL OR comm<100;

5. 分页查询

为了更好地显示大量的数据，可以一次性只展示一部分数据，采用多页的形式，这种查询就是分页查询。

SQLite中使用LIMIT子句与OFFSET子句限制查询的返回数量，实现分页查询，语法格式如下。

其中LIMIT表示结果的数量，OFFSET表示丢弃前几条记录，可以视情况使用。

【例子】

1. 查询前五个雇员的信息。

SELECT * FROM emp LIMIT 5;

2. 查询工资薪水最高的三个雇员信息。

分析：授课过程中不会把分页查询加入到最基础的子句顺序中，但是实际上其执行顺序在ORDER BY之后，所有分页查询题可以先不考虑分页的问题。

此题可以先变为“查询所有人的信息，按照工资降序排布”

SELECT * FROM emp ORDER BY sal DESC;

最后，筛选出前三个数据。

SELECT * FROM emp ORDER BY sal DESC LIMIT 3; 

3. 查询公司前一半雇佣（早期雇佣）的雇员信息。

SELECT * FROM emp ORDER BY hiredate ASC LIMIT 7;

此题能做出的前提是已知14人，如果对总人数未知，则需要先获得总人数。总人数的获取在后续课程中会介绍相关的函数。

4. 查询公司薪金第四名到第八名的雇员信息。

--查询公司所有雇员的信息，按照薪金从高到底排序。
SELECT * FROM emp ORDER BY sal DESC;

--保留五个人
SELECT * FROM emp ORDER BY sal DESC LIMIT 5;

--向后移动三个人
SELECT * FROM emp ORDER BY sal DESC LIMIT 5 OFFSET 3;

【练习】

1. 查询公司员工编号前5个员工的员工编号和姓名。

SELECT empno,ename FROM emp ORDER BY empno ASC LIMIT 5;

2. 查询所有雇员的信息，要求显示工资最低的三个人。

SELECT * FROM emp ORDER BY sal ASC LIMIT 3;

3. 查询公司最早雇佣的三个人的信息。

SELECT * FROM emp ORDER BY hiredate LIMIT 3;

4. 查询公司工资第6-10名的雇员信息。

SELECT * FROM emp ORDER BY sal DESC LIMIT 5 OFFSET 5;

真正要使用分页查询可以基于以下的公式：

设每页显示的条数为n，当前页码为m，得分页查询公式为：

SELECT * FROM 表名 LIMIT n OFFSET (m-1)*n;

【例子】每页显示6条数据，查询第二页的内容。

分析：n=6，m=2

SELECT * FROM emp LIMIT 6 OFFSET 6;

【练习】

1. 每页显示3条数据，查询第五页的内容。

SELECT * FROM emp LIMIT 3 OFFSET 12;

2. 每页显示5条数据，分别查询每一页的内容。

SELECT * FROM emp LIMIT 5;
SELECT * FROM emp LIMIT 5 OFFSET 5;
SELECT * FROM emp LIMIT 5 OFFSET 10;

3. 取出emp表中9-12行的数据。

SELECT * FROM emp LIMIT 4 offset 8;

4. 假设京东显卡的数据库表名为xianka，每页显示60条数据，查询第12页的数据。

SELECT * FROM xianka LIMIT 60 offset 660;

二、函数

函数是计算程序中一段预先设置好的功能，可以满足反复调用的需求。SQLite中提供了众多函数，本次选取一些常见的函数进行讲解。

一个函数只需要关注三个部分：

• 输入参数：需要什么数据
• 函数名称：如何处理数据
• 返回值：处理后的结果是什么

1. 字符串函数

用于处理TEXT类型的数据。

1.1 大小写转换函数

• UPPER

输入参数：英文的字符串

函数名称：转换大写

返回值：转换大写后的字符串

• LOWER

输入参数：英文的字符串

函数名称：转换小写

返回值：转换小写后的字符串

【例子】

1. 把'Hello'转换为全大写和全小写。

SELECT UPPER('Hello'),LOWER('Hello');

2. 查询所有雇员的姓名，要求姓名全小写。

分析：先查询所有雇员的姓名，最后套用转大写函数。

SELECT LOWER(ename) FROM emp;

【练习】

1. 查询所有雇员的姓名和职务，要求所有字母小写。

SELECT LOWER(ename),LOWER(job) FROM emp;

2. 查询所有部门的名称，要求所有字母小写。

SELECT LOWER(dname) FROM dept;

1.2 去除左右空格

TRIM

• 输入参数：字符串
• 函数名称：去除字符串前后的空格
• 返回值：处理后的字符串

【例子】去除' hello world '前后的空格。

SELECT TRIM('       hello world       ');

2. 数字函数

2.1 四舍五入

ROUND

• 输入参数1：数字
• 输入参数2：保留的小数位，可省略，省略默认为保留整数位
• 函数名称：四舍五入
• 返回值：四舍五入后的数字

【例子】将123.456四舍五入保留两位小数。

SELECT ROUND(123.456,2);

2.2 取模

可以简单地认为取模运算就是取余。

MOD

输入参数1：被除数

输入参数2：除数

函数名称：求模

返回值：计算后的数字

【例子】求出10对4取模的结果。

分析：即求出10除以4的余数。

SELECT MOD(10,4);

2.3 绝对值

ABS

输入参数：数字

函数名称：绝对值

返回值：取绝对值之后的数字

【例子】求出-23的绝对值

SELECT ABS(-23);

3. 日期函数

3.1 提取函数

DATE

• 输入参数：符合格式的日期时间字符串
• 函数名称：只提取日期
• 返回值：只包含日期的字符串

【例子】查询所有雇员的雇用日期，要求不显示时间。

SELECT DATE(hiredate) FROM emp;

同理，也可以只显示时间，使用下面的函数。

TIME

• 输入参数：符合格式的日期时间字符串
• 函数名称：只提取时间
• 返回值：只包含时间的字符串

也可以都显示，使用下面的函数。

DATETIME

• 输入参数：符合格式的日期时间字符串
• 函数名称：提取时间和日期
• 返回值：包含日期和时间的字符串

3.2 获取本地时间

也可以使用DATETIME、DATE或TIME提取本地时区的当前时间和日期。

函数还是3.1中的三个函数，但是参数换为以下两个参数

输入参数1：'now'

输入参数2：'localtime'

【例子】获得当前时区的时间和日期。

SELECT DATE('now','localtime'),TIME('now','localtime'),DATETIME('now','localtime');

上面的函数还可以省略第二个参数'localtime'，表示基于国际标准时间（格林威治时间）。

SELECT DATE('now'),TIME('now'),DATETIME('now') FROM emp;

3.3 儒略日

儒略日（Julian Day）是公元前4713年1月1日。

JULIANDAY

输入参数：'now'或一个日期

函数名称：计算机输入日期与儒略日的差值，单位天

返回值：相差的天数，精确到小数

【例子】求出每个雇员的姓名和雇佣年数。

SELECT ename,(JULIANDAY('now')- JULIANDAY(hiredate))/365 FROM emp;

也可以直接使用日期作差：

SELECT ename,DATE('now')-DATE(hiredate) FROM emp;

3.4 时间格式化

可以通过STRFTIME函数提取需要的时间或日期数据。

输入参数1：格式，如下所示。

输入参数2：基于哪个日期时间

返回值：提取的数据，需要注意返回的数据是文本，例如'12'

【例子】查询1月雇佣的雇员信息。

分析：先提取月份，再比对。

-- 注意STRFTIME返回值的TEXT，文本与文本比对，更改为'01'
SELECT * FROM emp WHERE STRFTIME('%m',hiredate)='01';

-- 也可以把等式左边的数值转换为数字
SELECT * FROM emp WHERE CAST(STRFTIME('%m',hiredate) AS DECIMAL)=1;

【练习】

1. 现在是几点几分（可以使用任何时区）。

SELECT STRFTIME('%H:%M',TIME('now','localtime'));

2. 查询在上半年雇佣的雇员信息。

SELECT * FROM emp 
WHERE STRFTIME('%m',HIREDATE) IN ('01','02','03','04','05','06');

SELECT * FROM emp WHERE STRFTIME('%m',hiredate) <= '06';

3. 查询在每个月1号雇佣的雇员信息。

SELECT * FROM emp
WHERE CAST(STRFTIME('%d',hiredate)AS DECIMAL)=01;

4. 查询在1981年雇佣的雇员信息。

SELECT * FROM emp WHERE STRFTIME('%Y', hiredate)= '1981';

SQLite数据库相比于其它数据库，处理日期和时间的能力较弱。

4. 空值函数

【例子】查询所有雇员的姓名和月收入（薪金+佣金）。

SELECT ename,sal+comm FROM emp;

可以发现，任何数字与NULL进行数学计算，结果都会同化为NULL。空值函数IFNULL就是用来处理这种问题的，可以把NULL等效为某个数字。

输入参数1：可能为空的数据

输入参数2：等效数字

函数名称：如果参数1为NULL，则替换为参数2的等效数字；如果参数1不为NULL，不进 行替换。

返回值：处理后数值（要么是NULL等效后的数字，要么原数字）

给当前上面的SQL语句套入空值处理函数，如下所示。

SELECT ename,sal+IFNULL(comm,0) FROM emp;

【练习】

计算所有雇员的姓名和年薪，如果不领取佣金，则每个月发400的补助。

SELECT ename,(sal+IFNULL(comm,400))*12 FROM emp;

---

5. 统计函数

常用的统计函数包括：

• 计数

COUNT

• 求和

SUM

• 平均值

AVG

• 最大值

MAX

• 最小值

MIN

这五个函数的输入参数都是一些数据列（通常为数字类型，COUNT可以支持非数字类型），返回值都是统计后的数字。

【例子】查询公司的平均工资、人数、每个月员工基本工资成本，最高工资和最低工资，工资只算基础工资。

SELECT AVG(sal),COUNT(*),SUM(sal),MAX(sal),MIN(sal) FROM emp;

练习：

1. 求出公司最早和最晚的雇佣日期。

SELECT MIN(hiredate),MAX(hiredate) FROM emp;

2. 求出公司20部门最高的工资。

SELECT MAX(sal) FROM emp WHERE deptno=20;

3. 求出所有经理中最高的工资。

SELECT MAX(sal) FROM emp WHERE job='MANAGER';

4. 求出所有销售的平均基本工资。

SELECT AVG(sal) FROM emp WHERE job='SALESMAN';

5. 求出所有柜员的数量。

SELECT COUNT(*) FROM emp WHERE job='CLERK';

【例子】COUNT(*)、COUNT(列名)、COUNT(DISTINCT 列名)的区别。

COUNT(*)表示使用所有列作为一条数据有效性的依据，即一条数据只要有任何一列有数据，就参与计数。

COUNT(列名)不会对输入列出现NULL的情况计数，重复的数据分别计数。

COUNT(DISTINCT 列名)只会统计输入列且不重复的次数，重复的数据只会计数一次。

四、多表查询

之前的查询都是基于一张表进行的，本章多表查询指的是基于多张表进行查询，主要是针对FROM子句进行拓展。

更改之前的语法格式为：

1. 原理

【例子】分别统计emp表和dept表的数据量。

SELECT COUNT(*) FROM emp;

SELECT COUNT(*) FROM dept;

【例子】同时对emp表与dept表统计数据量。

SELECT COUNT(*) FROM emp,dept;

可以看到56=14x4，两张表相乘了，这种情况被称为笛卡尔积。

每个雇员的数据在内部都会尝试跟各个部门的数据结合，但是在结合的过程中，因为雇员只能有一个部门，因此只有一个结合是有效的，其它三个结合的数据都是无效数据。

直接进行两个表的查询，可以看到这些结合的数据。

多表查询一定要消除无效的笛卡尔积，使用WHERE子句通过关联列的等值关系可以消除无效数据，在上面的例子中这个关联列就是部门号deptno，即只需要判断两个表的deptno是否相等即可。

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

也可以通过下面的写法进行改善。

-- 起别名
SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;

【例子】查询所有雇员的编号，姓名，职位，工资，部门编号，部门名称，部门位置。

分析：

第一步，确定FROM子句。

emp表：empno,job,sal,deptno

dept表：dname,loc

FROM emp e,dept d

第二步，如果是多表查询，确定多表之间的关系，消除笛卡尔积。

WHERE e.deptno=d.deptno

第三步，查看所有人还是一部分人，如果是一部分人，则继续使用WHERE子句筛选。

第四步，确定SELECT子句。

SELECT empno,job,sal,deptno,dname,loc

第五步，确定ORDER BY子句。

第六步，拼接SQL语句，拼接时注意多表的公共列。

SELECT empno,job,sal,e.deptno,dname,loc
FROM emp e,dept d
WHERE e.deptno=d.deptno;

3. 非等式关联

多表之间建立关联并非一定需要等式关系。

【例子】查询出每个雇员的编号，姓名，职位，基本工资，雇佣日期，工资等级，结果按照基本工资从高到低排序。

分析：

第一步，确定FROM子句。

emp表：empno,ename,job,sal,hiredate

salgrade表：grade

FROM emp e,salgrade s

第二步，如果是多表查询，确定多表之间的关系，消除笛卡尔积。

WHERE e.sal BETWEEN s.losal AND s.hisal

第三步，查看所有人还是一部分人，如果是一部分人，则继续使用WHERE子句筛选。

第四步，确定SELECT子句。

SELECT empno,ename,job,sal,hiredate,grade

第五步，确定ORDER BY子句。

ORDER BY sal DESC

第六步，拼接SQL语句，拼接时注意多表的公共列。

SELECT empno,ename,job,sal,hiredate,grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
ORDER BY sal DESC;

【思考】查询每个雇员的编号、姓名、职位、雇佣日期、工资、工资等级、所在部门以及位置。

分析：

第一步，确定FROM子句。

emp表：empno,ename,job,hiredate,sal,deptno

salgrade表：grade

dept表：loc

FROM emp e,salgrade s,dept d

第二步，如果是多表查询，确定多表之间的关系，消除笛卡尔积。

WHERE sal BETWEEN losal AND hisal AND e.deptno=d.deptno

第三步，查看所有人还是一部分人，如果是一部分人，则继续使用WHERE子句筛选。

第四步，确定SELECT子句。

SELECT empno,ename,job,hiredate,sal,deptno,grade,loc

第五步，确定ORDER BY子句。

第六步，拼接SQL语句，拼接时注意多表的公共列。

SELECT empno,ename,job,hiredate,sal,e.deptno,grade,loc
FROM emp e,salgrade s,dept d
WHERE sal BETWEEN losal AND hisal AND e.deptno=d.deptno;

4. 连接方式

多表查询有不同的连接方式，可以分为：

但是SQLite数据库有些功能不支持：

右外连接与全外连接是不支持

4.1 内连接

之前的篇章使用的多表查询就是内连接，且使用的基于SQL89标准的语法，除此之外常用的写法还有SQL99标准。

【例子】查询所有雇员的姓名和部门名称。

-- SQL89
SELECT ename,dname FROM emp e,dept d WHERE e.deptno=d.deptno;

-- SQL99
SELECT ename,dname FROM emp e JOIN dept d ON e.deptno=d.deptno;

4.2 外连接

为了更好地观察内连接与外连接的区别，需要在emp表增加一个没有部门的雇员。

INSERT INTO emp(empno,ename,job,hiredate,sal)
VALUES(6666,'JASON','MANAGER',DATETIME('now'),10000);

emp表中就有15个雇员了。

此时再次执行4.1节中的内连接语句。

可以发现少了一个人的数据，因为JASON的deptno为NULL，所以不满足部门号相等的条件。为了让没有部门的雇员显示，需要改为外连接。

SQLite只支持基于SQL99标准的左外连接：

-- LEFT表示让左边的表完整显示
SELECT ename,dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;

【练习】

查询所有雇员的姓名、月综合收入（sal+comm）与位置，要求显示所有雇员的信息。

SELECT ename,sal+IFNULL(comm,0),loc 
FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno;

4.3 自连接

自连接是一种特殊情况，表示多表查询的过程使用多次同一张表。

【例子】查询每个雇员的编号、姓名、职位、领导姓名。

分析：

第一步，确定FROM子句。

emp表（雇员表）：empno,ename,job

emp表（领导表）：ename

FROM emp e JOIN emp m

第二步，如果是多表查询，确定多表之间的关系，消除笛卡尔积。

ON e.mgr=m.empno

第三步，查看所有人还是一部分人，如果是一部分人，则继续使用WHERE子句筛选。

第四步，确定SELECT子句。

SELECT empno,ename,job,ename

第五步，确定ORDER BY子句。

第六步，拼接SQL语句，拼接时注意多表的公共列，自连接的每列都是公共列。

SELECT e.empno,e.ename,e.job,m.ename
FROM emp e JOIN emp m ON e.mgr=m.empno;

第七步，确定是否使用外连接。

SELECT e.empno,e.ename,e.job,m.ename
FROM emp e LEFT JOIN emp m ON e.mgr=m.empno;

【思考】查询所有雇员的姓名、工资等级，以及他们的领导的姓名。

SELECT e.ename,grade,m.ename
FROM emp e LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
LEFT JOIN emp m ON e.mgr=m.empno;

5. 其它连接写法

5.1 交叉连接

能够产生笛卡尔积，以下两种写法等效。

SELECT * FROM emp CROSS JOIN dept;
SELECT * FROM emp,dept;

5.2 自然连接

可以自动找到多表之间的关联字段消除笛卡尔积，属于内连接。

-- 自动建立emp与dept的等值连接
SELECT * FROM emp NATURAL JOIN dept;

5.3 USING指定关联字段

使用USING指出多表查询的关联字段。

SELECT * FROM emp JOIN dept USING(deptno);

5.4 交集与并集

5.4.1 并集（不显示重复记录）

使用UNION关键字，可以将两个查询结果合并，且不显示重复记录（橙色区域只显示一遍）。

【例子】查询所有20部门或者工资高于1500的雇员信息。

分析：

查询一：查询所有20部门的雇员信息。

SELECT * FROM emp WHERE deptno=20;

查询二：查询所有工资高于1500的雇员信息。

SELECT * FROM emp WHERE sal>1500;

并集。

SELECT * FROM emp WHERE deptno=20
UNION
SELECT * FROM emp WHERE sal>1500;

5.4.2 并集（显示重复记录）

使用UNION ALL关键字，可以将两个查询结果合并，且显示重复记录（橙色区域显示两遍）。

以5.4.1的例题为例，改为使用UNION ALL合并。

SELECT * FROM emp WHERE deptno=20
UNION ALL
SELECT * FROM emp WHERE sal>1500;

5.4.3 交集

使用INTERSECT可以获得两个查询结果的交集。

【例子】查询部门20且工资高于1500的雇员信息。

SELECT * FROM emp WHERE deptno=20
INTERSECT
SELECT * FROM emp WHERE sal>1500;

交集与并集的使用需要注意让两个查询的格式相同，查询结果的列能一一对应。

删除之前添加的数据。

DELETE FROM emp WHERE empno=6666;

五、分组查询

什么情况需要分组？

班级里男生一组，女生一组；公司研发部一组、市场组一组、测试部一组......

分组的前提是某些行为和特点具有同一性，比如在emp表中job和deptno等字段可以进行分组。一条数据也能独占一组，但是每组都是一条数据则毫无意义。

1. 概念

分组查询使用关键字GROUP BY实现，语法规则如下：

【例子】按照职位分组，查询每个职位的平均工资、最高工资、最低工资和人数。

分析：

第一步，确定FROM子句。

emp：sal,sal,sal,empno

FROM emp

第二步，如果是多表查询，确定多表之间的关系，消除笛卡尔积。

第三步，查看所有人还是一部分人，如果是一部分人，则继续使用WHERE子句筛选。

第四步，确定GROUP BY子句。

GROUP BY job

第五步，确定SELECT子句。

SELECT job,AVG(sal),MAX(sal),MIN(sal),COUNT(empno)

第六步，确定ORDER BY子句。

第七部，拼接SQL语句。

SELECT job,AVG(sal),MAX(sal),MIN(sal),COUNT(empno)
FROM emp
GROUP BY job;

【练习】

1. 统计所有部门的平均工资和最高工资。

SELECT deptno,AVG(sal),MAX(sal)
FROM emp 
GROUP BY deptno;

2. 统计10部门和20部门中各个职位的人数。

SELECT job,COUNT(empno) 
FROM emp
WHERE deptno=10 OR deptno=20
GROUP BY job;

3. 统计公司所有部门的平均工资，按照平均工资降序排序。

SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
ORDER BY AVG(sal) DESC;

可以看到之前学习的统计函数与分组查询密切相关。

2. 多表+分组

多表查询和分组查询可以一起使用。

【例子】查询出每个部门的名称、部门人数、平均工资。

-- 推荐使用题目中需要显示的内容作为分组字段
SELECT dname,COUNT(empno),AVG(sal)
FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno
GROUP BY dname;

-- 这种写法在SQLite中也可以，但是其它数据库中可能失败
SELECT dname,COUNT(empno),AVG(sal)
FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno
GROUP BY e.deptno;

【思考】查询出每个部门的编号、部门名称、位置、部门人数、平均工资。

SELECT d.deptno,dname,loc,COUNT(ename),AVG(sal) 
FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno 
GROUP BY e.deptno;

3. 作业

1. 查询公司工资等级在3以上（包含3），的雇员编号和姓名。

SELECT empno,ename

FROM emp,salgrade

WHERE sal BETWEEN losal AND hisal AND grade>=3;

SELECT empno,ename

FROM emp JOIN salgrade ON sal BETWEEN losal AND hisal

WHERE grade>=3;

2. 查询公司所有雇员的姓名，雇员编号，部门名称，要求部门名称小写且显示所有部门。

SELECT ename,empno,LOWER(dname)

FROM dept d

LEFT JOIN emp e ON e.deptno=d.deptno;

3. 要求算出部门30的所有人员的姓名、雇佣年限（要求四舍五入到一位小数）、部门位置。

SELECT ename, ROUND((JULIANDAY('now')-JULIANDAY(hiredate))/365,1),loc

FROM emp e JOIN dept d ON e.deptno=d.deptno

WHERE e.deptno=30;

4. 查询出所有雇员和其领导的姓名和年薪。

SELECT e.ename,m.ename,(m.sal+IFNULL(m.comm,0))*12

FROM emp e LEFT JOIN emp m ON e.mgr=m.empno;

5. 统计销售和经理的职位的平均月综合收入、最高月综合收入、职位人数。

SELECT job, AVG(sal+IFNULL(comm,0)),MAX(sal+IFNULL(comm,0)),COUNT(empno)

FROM emp e WHERE job='MANAGER' OR job='SALESMAN'

GROUP BY job;

4. HAVING子句——分组后筛选

【思考】按照职位分组，统计每个职位的平均工资，显示平均工资高于2000的职位。

分析：按照之前的做法，可能会写出下面的SQL语句。

SELECT job,AVG(sal)
FROM emp
WHERE AVG(sal)>2000
GROUP BY job;

执行后，直接报错。

意思是WHERE子句中不允许使用AVG函数，实际上WHERE中不允许出现任何统计函数，因为WHERE是在GROUP BY之前执行的，如果是在分组后进行筛选，应该引入HAVING子句来完成这个功能。

增加HAVING子句到执行顺序中，语法格式如下。

修改上一道题的答案为：

SELECT job,AVG(sal)
FROM emp
GROUP BY job
HAVING AVG(sal)>2000;

再次阐明一下WHERE和HAVING的区别。

总结：

• WHERE是在分组之前对个体进行筛选，HAVING是分组之后对群体进行筛选。
• WHERE可以独立存在，HAVING必须依赖于GROUP BY子句。
• WHERE通常不适用统计函数，HAVING通常使用统计函数

【练习】

查询每个部门的名称、部门人数、最高工资和最低工资，要求显示最低工资高于1000的部门信息。

分析：

第一步，确定FROM子句。

FROM dept d JOIN emp e

第二步，确定关联字段。

ON d.deptno=e.deptno

第三步，确定WHERE子句，是否有针对个体的筛选。

第四步，确定GROUP BY子句。

GROUP BY e.deptno

第五步，确定HAVING子句，是否有针对组的筛选。

HAVING MIN(sal)>1000     

第六步，确定SELECT子句。

SELECT dname,COUNT(empno),MAX(sal),MIN(sal)    

第七步，确定ORDER BY子句。

第八步，拼接并检查。

SELECT dname,COUNT(empno),MAX(sal),MIN(sal)
FROM dept d JOIN emp e ON d.deptno=e.deptno
GROUP BY e.deptno
HAVING MIN(sal)>1000;

【练习】查询公司各个工资等级的人数与平均工资，要求显示平均工资高于1500的职位信息，且按照平均工资降序排布。

SELECT grade,COUNT(empno),AVG(sal)
FROM salgrade JOIN emp ON sal BETWEEN losal AND hisal
GROUP BY grade
HAVING AVG(sal)>1500
ORDER BY AVG(sal) DESC;

【思考】统计出公司领取佣金和不领取佣金的人数与平均工资（sal）。

分析：如果按照常规分组来做，如下所示。

可以看到结果不符合题意，这种情况下需要手动分组，使用UNION子句。

-- 公司领取佣金的人数与平均工资
SELECT COUNT(empno),AVG(sal) FROM emp WHERE comm IS NOT NULL;

-- 公司不领取佣金的人数与平均工资
SELECT COUNT(empno),AVG(sal) FROM emp WHERE comm IS NULL;

手动合并分组结果。

SELECT '领取佣金',COUNT(empno),AVG(sal) FROM emp WHERE comm IS NOT NULL
UNION
SELECT '不领取佣金',COUNT(empno),AVG(sal) FROM emp WHERE comm IS NULL;

六、子查询

子查询指的是在一个查询中嵌入多个小的查询，即查询的嵌套，子查询一定要出现()

子查询在绝大多数情况下符合如下规律：

• 子查询返回的结果是单行单列（一个数据）：HAVING、WHERE
• 子查询返回的结果是单行多列（一行）：WHERE
• 子查询返回的结果是多行多列（一个表）：FROM

其它子句使用子查询的情况较少。

1. 在WHERE子句中使用子查询

WHERE子句的功能是控制显示的行数，支持以下几种子查询：

• 单行单列
• 多行单列
• 多行多列

1.1 返回单行单列

【例子】统计出所有高于公司平均工资的雇员信息。

分析：

第一步，求出公司的平均工资。

SELECT AVG(sal) FROM emp;

第二步，把第一步的结果嵌入到WHERE子句中。

SELECT * FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp);

【练习】统计出公司最早雇佣的雇员信息（使用子查询完成）。

SELECT * FROM emp 
WHERE hiredate=(SELECT MIN(hiredate) FROM emp);

1.2 返回单行多列

【例子】显示出公司雇佣最早且工资最低的雇员（刚好存在）。

分析：

第一步，统计出公司最早的雇佣日期和最低的工资。

SELECT MIN(hiredate) FROM emp;
SELECT MIN(sal) FROM emp;

第二步，将上面的两个结果嵌套在WHERE子句中显示满足条件的信息。

SELECT * FROM emp
WHERE hiredate=(SELECT MIN(hiredate) FROM emp)
AND sal=(SELECT MIN(sal) FROM emp);

【练习】求出与SCOTT工资相同且职位相同的雇员信息。

SELECT * FROM emp 
WHERE job=(SELECT job FROM emp WHERE ename='SCOTT') 
AND sal=(SELECT sal FROM emp WHERE ename='SCOTT')
AND ename!='SCOTT';

1.3 返回多行单列

相当于提供了一些查询范围，通常与IN这种操作结合使用。

【例子】显示工资跟各个经理相同的雇员信息（包含各个经理本身）。

分析：

第一步，查询出各个经理的工资。

SELECT sal FROM emp WHERE job='MANAGER';

第二步，将上面的SQL语句嵌入到WHERE子句中搭配IN操作使用。

SELECT * FROM emp
WHERE sal IN (SELECT sal FROM emp WHERE job='MANAGER');

【例子】显示工资跟各个销售不相同的雇员信息。

SELECT * FROM emp 
WHERE NOT sal IN (SELECT sal FROM emp WHERE job='SALESMAN');

2. HAVING子查询

如果有HAVING子句，就一定分组统计。

HAVING子句中出现的子查询只能是单行单列的。

【例子】查询出高于公司平均工资的部门编号和这些部门的平均工资。

分析：

第一步，计算出公司的平均工资。

SELECT AVG(sal) FROM emp;

第二步，分组后在HAVING子句中嵌入第一步的SQL语句。

SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal)>(SELECT AVG(sal) FROM emp);

【思考】查询平均工资最低的职位的信息（此职位的名称、人数与此职位的平均工资）

分析：

第一步，查询每个职位的平均工资。

SELECT AVG(sal) FROM emp GROUP BY job;

第二步， 查询平均工资最低的职位的平均工资。

SELECT MIN(asal)
FROM (SELECT AVG(sal) asal FROM emp GROUP BY job);

注意需要起别名asal才能在外部使用。

第三步，使用上一步的结果作为HAVING子句中子查询的条件。

SELECT job,COUNT(empno),AVG(sal)
FROM emp
GROUP BY job
HAVING AVG(sal)=(
    SELECT MIN(asal)
    FROM (SELECT AVG(sal) asal FROM emp GROUP BY job)
);

此外，此题还可以使用其他解法。

SELECT job,COUNT(empno),AVG(sal) 
FROM emp 
GROUP BY job 
HAVING sal 
ORDER BY AVG(sal) ASC 
LIMIT 1;

3. SELECT子查询

出现频率较低，使用需求较低。

【例子】显示所有雇员的姓名、职位、部门名称与部门位置。

分析：

正常解法就是一个普通的多表查询。但是此处也可以使用SELECT子查询完成。

SELECT e.ename,e.job,
(SELECT dname FROM dept WHERE deptno=e.deptno),
(SELECT loc FROM dept WHERE deptno=e.deptno)
FROM emp e;

这种方法无需掌握。

4. FROM子查询

FROM子查询通常返回的是多行多列，可以把子查询返回的结果看做是一张单独的表。

【例子】查询出每个部门的编号、名称、位置、部门人数、平均工资。

实现方式一：直接使用之前的多表查询。

SELECT d.deptno,dname,loc,COUNT(empno),AVG(sal)
FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno
GROUP BY d.deptno;

实现方式二：FROM子查询

第一步，单独查询出所有部门的部门号（两个表都有），部门人数（

emp表），平均工资（emp表）

SELECT deptno,COUNT(empno),AVG(sal)
FROM emp
GROUP BY deptno;

第二步，把上一步查询的结果作为一张表与dept表进行多表查询。

SELECT d.deptno,dname,loc,temp.rs,temp.asal
FROM dept d LEFT JOIN(
    SELECT deptno,COUNT(empno) rs,AVG(sal) asal
    FROM emp
    GROUP BY deptno
)temp ON d.deptno=temp.deptno;

尽量搞明白，实在搞不懂的必须掌握实现方式一。

实际的开发中，有可能会遇到庞大的数据量，不妨把当前emp与dept表的数据量扩大100倍，即emp表中有1400条数据，dept表中有400条数据。

• 实现方式一的数据量

直接使用多表查询，会在后台生成笛卡尔积，其数量为1400*400=560000

• 实现方式二的数据量

第一步（内嵌的子查询）：操作数据量为1400，返回的数据量为最多为400

第二步（子查询与dept表多表查询）：400*400=160000

总数据量 = 160000+1400 = 161400

实现方式二的理论性能是实现方式一的1/3，结论是：

小的数据量使用实现方式一，大的数据量使用实现方式二。

5. 作业

1. 列出至少有四个员工的所有部门编号、部门名称、部门人数

SELECT d.deptno,dname,COUNT(empno)

FROM emp e JOIN dept d ON e.deptno=d.deptno

GROUP BY d.deptno

HAVING COUNT(empno)>=4;

2. 列出雇佣日期早于其领导的所有员工的编号，姓名，部门名称，领导名称。

SELECT e.empno,e.ename,d.dname,p.ename

FROM emp e LEFT JOIN emp p ON e.mgr=p.empno JOIN dept d ON e.deptno=d.deptno

WHERE p.hiredate>e.hiredate;

3. 列出各个部门的CLERK（柜员）的最低薪金。

SELECT deptno,MIN(sal)

FROM emp

WHERE job='CLERK'

GROUP BY deptno;

4. 列出工资比SMITH多的所有员工

SELECT * FROM emp WHERE sal>(

SELECT sal FROM emp WHERE ename='SMITH'

);

5. 列出在部门“SALES”工作的员工姓名和工资

SELECT ename,sal

FROM emp e JOIN dept d ON e.deptno=d.deptno

WHERE d.dname="SALES";

6. 求出部门名称中带‘S’字符的部门,其员工的工资合计和部门的人数

SELECT dname,SUM (sal),COUNT(e.deptno)

FROM emp e JOIN dept d ON e.deptno=d.deptno

GROUP BY dname

HAVING dname LIKE '%S%';

七、数据更新操作

针对DML（数据操作语言）分为两类：查询（DQL）和更新，更新分为三种操作：

• 增加 INSERT
• 修改 UPDATE
• 删除 DELETE

数据更新操作会直接更改数据库中的数值，为了防止emp表的原始数据被破坏，在学本章知识点之前，先复制一份emp表，新的emp表叫myemp，执行如下操作：

CREATE TABLE myemp AS SELECT * FROM emp;

myemp只是数据与emp完全相同，但是一些约束关系不相同。

本章后续的操作都基于myemp表。如果不小心破坏了emp表，可以去群文件中重新下载scott.db文件。

1. 插入数据

插入数据的语法格式如下所示：

INSERT INTO 表名[(列1,列2,...)] VALUES(值1,值2,...)

针对目前已经接触过的数据类型：

• 字符串：要求使用英文半角的单引号或双引号包括内容
• 数字：直接写数字
• 日期和时间
  • 如果是当前日期时间，可以使用DATETIME('now')等写法，如果是本地日期和时间别忘了第二个参数增加'localtime'
  • 也可以使用固定格式的字符串，例如'1992-01-06 02:33:33'

【例子】在myemp表中增加一条数据。

实现方式一：使用完整的格式

INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(8888,'张三','厨师',7839,DATETIME('now','localtime'),10000,40);

实现方式二：使用简易格式

INSERT INTO myemp VALUES(1000,'罗翔','律师',8888,'2000-03-30 09:44:34',1,NULL,10);

第二种方式需要注意，如果列数据为NULL，需要手动写上插入的数据为NULL。

推荐使用第一种方式。

【练习】

分别使用两种方式插入自己和同桌的信息。

INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(6666,'IKUN','唱跳rap篮球',7839,DATETIME('now','localtime'),135,11);

INSERT INTO myemp VALUES(230,'小黑子','鸡你太美',6666,'2022-09-30 09:44:34',123,NULL,10);

2. 修改数据

修改数据的语法如下：

UPDATE 表名 SET 列名1=值1,列名2=值2,...[WHERE 更新条件(s)]

需要注意的是，如果不写WHERE子句表示更新所有数据。

【例子】将‘SMITH’的工资修改为5000，佣金修改为2000

UPDATE myemp SET sal=5000,comm=2000 WHERE ename='SMITH';

【例子】将所有销售的工资修改为2000

UPDATE myemp SET sal=2000 WHERE job='SALESMAN';

【练习】

1. 将公司最早雇佣的员工的工资增长20%

UPDATE myemp SET sal=1.2*sal ORDER BY hiredate ASC LIMIT 1;

UPDATE myemp SET sal=1.2*sal 
WHERE hiredate=(SELECT MIN(hiredate) FROM emp);

2. 将公司所有雇员的雇佣日期改为现在。

UPDATE myemp SET hiredate=DATETIME('now');

3. 删除数据

删除数据的语法格式如下：

DELETE FROM 表名 [WHERE 删除条件(s)]

需要注意的是，如果不写WHERE子句，表示删除所有数据！

【例子】删除所有30部门的雇员。

DELETE FROM myemp WHERE deptno=30;

第一次执行，显示

；再次执行，显示，因为这些数据已经被删除了。

【练习】

1. 删除公司工资最高的雇员。

DELETE FROM myemp WHERE sal=(SELECT MAX(sal) FROM myemp);

2. 删除没有领导的雇员。

DELETE FROM myemp WHERE mgr IS NULL;

3. 删除所有雇员。

DELETE FROM myemp;

在实际的开发中，为了保护数据，随时可以恢复数据，通常执行逻辑删除，而不执行物理删除。

物理删除就是使用DELETE语句进行删除；

逻辑删除指的是，在设计表时可以增加一列，用这一列的数据表示当前的数据的可用性（例如1为数据可用，0为数据不可用）。那么在查询的时候多增加一个判断为1的条件，表示查询所有数据。删除时，可以把这一列的数据置为0，使用UPDATE操作来进行逻辑删除。

八、表的创建与管理

之前的所有学习都是以DML（数据操作语言）为主，如果想对表本身进行维护，则需要使用DDL（数据定义语言），DDL通常在项目的初期使用。

1. 数据类型

大多数数据库使用的都是静态数据类型，即值的类型由它的容器（存储值的列）决定。但是SQLite使用的是动态数据类型，值本身决定数据类型，不与容器相关。

SQLite常用的存储类有：

存储类又可以动态地细分为不同的亲和类型，仅做了解。

2. 创建表

创建一张表的语法格式如下：

CREATE TABLE 表名(
    列名1    类型    [DEFAULT],
    列名2    类型    [DEFAULT],
    ...,
	列名n    类型    [DEFAULT]
);

如果创建表的语句较短，也可以写成一行：

CREATE TABLE 表名(列名1 类型 [DEFAULT],列名2 类型 [DEFAULT],...,列名n 类型 [DEFAULT]);

【例子】创建一张表。

CREATE TABLE hqyj23032(
    id    INTEGER,
    name  TEXT    DEFAULT    '佚名',
    birth TEXT    DEFAULT    CURRENT_DATE,
    sal   REAL    DEFAULT    0
);

可以对这个表进行正常的增删改查。

可以使用下面的语句删除表。

DROP TABLE 表名;

3. 约束

约束是在表的数据列中增加强制执行的规则，例如上一节中的默认值，如果一列没有默认值约束且不手动添加数值，则会使用NULL表示这个数据。

除了默认值以外，还有其它的约束，这些约束可以保障数据表的准确性和可靠性。

一共有如下约束类型：

3.1 非空约束

【例子】创建一个表，包含非空约束的列，并测试。

CREATE TABLE member(id INTEGER,name TEXT NOT NULL);

尝试插入数据，使name列的数值为NULL。

INSERT INTO member VALUES(1,NULL);
INSERT INTO member(id) VALUES(2);

插入失败。

3.2 唯一约束

【例子】创建一个表，包含唯一约束的列，并测试。

CREATE TABLE member(id INTEGER,name TEXT UNIQUE);

-- 可以执行
INSERT INTO member VALUES(1,'张三');
-- 不可以执行
INSERT INTO member VALUES(2,'张三');

当重复时显示

3.3 主键约束

主键约束 ≈ 非空约束+唯一约束

作为每个记录的唯一标识，通常把第一列（序号）作为主键。

与其它数据库不同的是，为了非空特性，当强行插入NULL时，会自动填充序列。

【例子】创建一个表，包含主键约束的列，并测试。

-- 先删除
DROP TABLE member;
-- 再建表
CREATE TABLE member(id INTEGER PRIMARY KEY,name TEXT);

-- 正确
INSERT INTO member VALUES(1,'张三');
-- 错误：id列重复了
INSERT INTO member VALUES(1,'李四');

上面第9行代码会报错。

-- 自动填充序列
INSERT INTO member VALUES(NULL,'王五');

主键约束还可以增加AUTOINCREMENT关键字，设置为自增长。

-- 先删除
DROP TABLE member;
-- 再建表，id列自增长
CREATE TABLE member(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT);

-- 测试
INSERT INTO member(name) VALUES('张三');
INSERT INTO member(name) VALUES('李四');
INSERT INTO member(name) VALUES('王五');

3.4 条件检查约束

【例子】创建一个表，包含条件检查约束的列，并测试。

-- 先删除
DROP TABLE member;
-- 再建表
CREATE TABLE member(id INTEGER,sal INTEGER CHECK(sal>1000));

-- 测试：成功
INSERT INTO member VALUES(1,1001);
-- 测试：失败
INSERT INTO member VALUES(2,999);

其它内容可以自学：