数据库的基本操作
基础知识1. 数据库是什么？
步伐在运行的过程中，数据都是在内存中存储的
https://img-blog.csdnimg.cn/img_convert/7553df70e9bd7ef82f28bc34797729ed.png
，但是内存一旦断电就会丢失数据。因此需要持久生存的数据会转交给硬盘。
https://img-blog.csdnimg.cn/img_convert/6885713253d5b25b1f56673dfe0be6da.png
在编程领域，最常用的方式是使用数据库批量的存储大量数据到硬盘。也就是说，数据库是一种电子化的资料柜。
数据库产物非常多，主流的包括：MySQL、SQLite、Oracle......这些不同的数据库都是由不同的软件公司开辟而来，每种数据库各有差异。IBM公司针对市面上各种不同厂家的数据库推出了统一的操作语言——SQL语言（语句）。
嵌入式领域中最常用的数据库产物是SQLite，本次学习的数据库就是SQLite。
SQLite是一款轻量级的数据库
https://img-blog.csdnimg.cn/img_convert/5dd63ccc42cc4d26005e772ae3e5605c.png
，只需要数兆的体积，免安装即可使用，因此许多嵌入式相关的技术框架都会内置SQLite数据库。
2. 操作环境
本次学习为了简化操作，使用SQLiteSpy软件作为开辟环境，软件的布局如下。
https://img-blog.csdnimg.cn/img_convert/71495a441e140c17b0e57b979aae26d4.png
SQLite数据库是以单文件进行存储的，一个数据库就是一个文件，文件的格式是.db或.db3，只需要使用SQLiteSpy软件打开.db或.db3文件即可。
https://img-blog.csdnimg.cn/img_convert/725a7cc292bad6138a5f040588905dbf.png
打开之后可以看到有四张表：dept、emp、salgrade和bonus。
如果把数据库文件看做是excel文件，上面的四张表就相当于excel中的sheet。
3. 数据库操作类型
IBM把SQL语句分为以下几类：
[*]DML（数据操作语言）
主要指的是数据的查询（DQL）和更新（增编削）操作，这是本次学习的重点。
[*]DDL（数据界说语言）
用于界说数据库对象，主要用于筹划数据库表的布局。
[*]DCL（数据控制语言）
用于控制数据库操作权限，一样平常用于高级的数据库，会由公司的数据库管理员负责。
4. 查看数据表
在SQLiteSpy软件中，只需要双击表名，就可以查询到表中的内容。
下面依次先容四张表。
4.1 dept表
存储公司的部门信息。
https://img-blog.csdnimg.cn/img_convert/0cd238b422db5718adf36ee417e5ff1e.png
整理表布局如下所示。
https://img-blog.csdnimg.cn/img_convert/48e40777434ef971a510ab17ef4d88bc.png
SQLiteSpy软件双击表名查询内容，现实上是实行了下面的语句。
SELECT * FROM dept;
把上面的语句粘贴到SQLiteSpy实行区域，手动实行也会有相同的效果。
https://img-blog.csdnimg.cn/img_convert/964f551bab44c58a97880e481c74b893.png
编写SQL语句也可以在其它软件中进行，例如Notepad++，只需要切换到SQL模式即可。
https://img-blog.csdnimg.cn/img_convert/86c9c204635ed4b02a10cfff847ab71d.png
4.2 emp表
emp表是雇员表，记录了公司里员工的信息。
https://img-blog.csdnimg.cn/img_convert/37f607f2752d7a3c0ba8b93ab0f262d4.png
整理表布局如下所示。
https://img-blog.csdnimg.cn/img_convert/dcab8eea3e58358aaebaa861a2bf8a7c.png
4.3 salgrade表
salgrade表是工资品级表。
https://img-blog.csdnimg.cn/img_convert/8c4ef3b151c8e6e5e626f00acef26701.png
https://img-blog.csdnimg.cn/img_convert/e07e110be0cc140280fad1b6376053ff.png
4.4 bonus表
bonus表是奖金表。
https://img-blog.csdnimg.cn/img_convert/bf6509f725ca1791e90c01ec45b5cef3.png
bonus表是一张空表，只有表布局，没有内容。
5. 需要记着的内容
内容
掌握程度
表名
英汉互译
列名
英汉互译
员工的职位job对应的值
英译汉
一、单表查询
这个篇章主要解说的是在一个表中进行数据查询。
1. 简单查询（列查询）
简单查询的效果中包罗全部的条目（行），依次展示每条数据，唯一能做的是限制表中出现的列。
https://img-blog.csdnimg.cn/img_convert/1f6c70b82702d585b17414eaa4e497e0.png
语法布局如下。
https://img-blog.csdnimg.cn/img_convert/95d924ebd558e85f204d091a6823cc99.png
其中[]表示可选填内容
DISTINCT表示去掉重复行的数据
*表示查询全部的列，如果不想查询全部列，则编写详细的列名称。
【例子】查询全部雇员的编号（empno）、姓名（ename）、职位（job）、基本工资（sal）。
SELECT empno,ename,job,sal FROM emp
;
https://img-blog.csdnimg.cn/img_convert/b2cfc507c39fb39bb5e1bcf90f548b79.png
【例子】查询每个雇员的编号、姓名和基本年薪（基本月薪x12）
SELECT empno,ename,sal*12 FROM emp
;
也可以更改显示的表名，如下所示。
SELECT empno 编号,ename 姓名,sal*12 年薪 FROM emp
;
https://img-blog.csdnimg.cn/img_convert/171c6d94ad3633a21ce54fc7e1dc85bc.png
→
【例子】查询每个雇员的编号、姓名、职位、年薪，每个雇员每个月有200元餐补、200元交通补助、夏天四个月有每月300元高温补贴，年底多发3个月基本工资。
SELECT empno,ename,job,12*(sal+400)+4*300+3*sal income
FROM emp
;
https://img-blog.csdnimg.cn/img_convert/7d0f3165d9c781618687b47e7a318d19.png
【例子】查询全部雇员的职位有哪些。
可能会写出这样的语句。
SELECT job FROM emp
;
https://img-blog.csdnimg.cn/img_convert/2a279c836411d9926895fad1cd3a5413.png
现实上此题消除重复项更为合适，可以增长DISTINCT。
SELECT DISTINCT job FROM emp
;
https://img-blog.csdnimg.cn/img_convert/6a9378a701f7086ee67ead0128fb9e65.png
需要注意的是，只有全部内容都重复才算重复项。
2. 限定查询
限定查询是在简单查询的基础上限制显示的条数（行数），其语法格式如下。
https://img-blog.csdnimg.cn/img_convert/9140fde495bad0276d169a86e435894d.png
主要通过WHERE子句限制行数，支持六种运算：
[*]关系运算
[*]取值范围运算
[*]基数范围运算
[*]暗昧查询
[*]空判断
[*]逻辑运算
2.1 关系运算
关系运算是最简单的运算符号，包括：
>、<、>=、<=、!=、<>（不等于）、==
【例子】查询公司工资高于2000的员工信息。
SELECT * FROM emp
WHERE sal>2000;
https://img-blog.csdnimg.cn/img_convert/d80f3bcfc9f1cc04485ed85700f2bf88.png
【练习】
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';
https://img-blog.csdnimg.cn/img_convert/ab8ee980545387109cf9df35581f80aa.png
【练习】查询工资范围在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);
https://img-blog.csdnimg.cn/img_convert/01a774519eaf6d8626279682e068af8b.png
如果一个数据没有，可以使用NULL表示，IN给的待选数据中不允许出现NULL，NULL是一种特殊的数据状态，需要使用专用语句进行判断。
2.4 暗昧查询
暗昧查询可以制定查询的格式，无需进行完备的内容匹配，主要使用LIKE关键字，共同两个符号进行标志。
[*]_
英文下划线，表示恣意一个字符。
[*]%
百分号，表示恣意多个（0,1,2，......，n）字符。
【例子】查询全部姓名是A开头的雇员信息。
SELECT * FROM emp
WHERE ename LIKE 'A%';
https://img-blog.csdnimg.cn/img_convert/dc24047e232373d245d01bd2056c5dc6.png
【练习】
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;
https://img-blog.csdnimg.cn/img_convert/4611d7e66335c5880b839a62932abad7.png
【例子】查询领取佣金的雇员信息。
-- 注意NOT的位置
SELECT * FROM emp
WHERE NOT comm IS NULL;
SELECT * FROM emp
WHERE comm IS NOT NULL;
https://img-blog.csdnimg.cn/img_convert/1e9652c804a0ef67570da21b74285056.png
2.6 逻辑运算
计算机中都有逻辑运算：与或非。
与：全部的条件都满足，效果才满足，在数据库中与使用AND表示。
或：只要满足一个条件，效果就满足，在数据库中或使用OR表示。
非：效果反转，在数据库中非使用NOT表示。
【例子】查询全部工资高于1300的贩卖信息。
分析：需要同时满足职位是贩卖 且 工资大于1300
SELECT * FROM emp
WHERE job='SALESMAN' AND sal>1300;
https://img-blog.csdnimg.cn/img_convert/f3c681a8fe87dcb5d5cd51170ef177e6.png
【练习】
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提供了一键排序的功能，只需要点击表名就可以切换次序或倒序排序的方式。
https://img-blog.csdnimg.cn/img_convert/1418a17e9efe9516a0ebd9e71920b634.png
由于以后数据库是在代码中操作的，因此仍然需要学习语法规则，查询排序的语法布局如下所示。
https://img-blog.csdnimg.cn/img_convert/a9cb8c8cd16bb94aed89571a8206ebeb.png
两种排序的方式：
[*]升序 ASC
[*]降序 DESC
【例子】查询全部雇员的信息，要求按照工资从高到低排序。
分析：只要是排序题，排序永世是最后一步。
SELECT * FROM emp
ORDER BY sal DESC
;
https://img-blog.csdnimg.cn/img_convert/e97137fdc1e194d74ebfecf0fdfb1530.png
可以看到有的人工资是一样，排序支持多个字段排序，如果第一字段值相同，则使用第二字段值的排序规则。
【例子】查询全部雇员的信息，要求按照工资从高到低排序，如果工资相同，则按照雇佣日期从早到晚排序。
SELECT * FROM emp
ORDER BY sal DESC
,hiredate ASC;
https://img-blog.csdnimg.cn/img_convert/5618724412c8aeeca7552bf28eb08023.png
【练习】
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子句限制查询的返回数量，实现分页查询，语法格式如下。
https://img-blog.csdnimg.cn/img_convert/7d6c32033b24363bb44aedfdd71d2fac.png
其中LIMIT表示效果的数量，OFFSET表示扬弃前几条记录，可以视环境使用。
【例子】
1. 查询前五个雇员的信息。
SELECT * FROM emp
LIMIT 5;
https://img-blog.csdnimg.cn/img_convert/c0d29aab838d2004e4a4b4a38e2342f8.png
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中提供了众多函数，本次选取一些常见的函数进行解说。
https://img-blog.csdnimg.cn/img_convert/6204bcee545cb87ea091ab5320ca7edb.png
一个函数只需要关注三个部分：
[*]输入参数：需要什么数据
[*]函数名称：如何处置惩罚数据
[*]返回值：处置惩罚后的效果是什么
1. 字符串函数
用于处置惩罚TEXT类型的数据。
1.1 巨细写转换函数
[*]UPPER
输入参数：英文的字符串
函数名称：转换大写
返回值：转换大写后的字符串
[*]LOWER
输入参数：英文的字符串
函数名称：转换小写
返回值：转换小写后的字符串
【例子】
1. 把'Hello'转换为全大写和全小写。
SELECT UPPER('Hello'),LOWER('Hello');
https://img-blog.csdnimg.cn/img_convert/4898cd48504e02ad07906a008df90443.png
2. 查询全部雇员的姓名，要求姓名全小写。
分析：先查询全部雇员的姓名，最后套用转大写函数。
SELECT LOWER(ename) FROM emp
;
https://img-blog.csdnimg.cn/img_convert/f601e25053e9f9a998cbb543e9b284e7.png
【练习】
1. 查询全部雇员的姓名和职务，要求全部字母小写。
SELECT LOWER(ename),LOWER(job) FROM emp
;
2. 查询全部部门的名称，要求全部字母小写。
SELECT LOWER(dname) FROM dept;
1.2 去除左右空格
TRIM
[*]输入参数：字符串
[*]函数名称：去除字符串前后的空格
[*]返回值：处置惩罚后的字符串
【例子】去除' hello world '前后的空格。
SELECT TRIM(' hello world ');
https://img-blog.csdnimg.cn/img_convert/07b7cb4df5ce57f39de56c5756b813d7.png
2. 数字函数
2.1 四舍五入
ROUND
[*]输入参数1：数字
[*]输入参数2：生存的小数位，可省略，省略默认为生存整数位
[*]函数名称：四舍五入
[*]返回值：四舍五入后的数字
【例子】将123.456四舍五入生存两位小数。
SELECT ROUND(123.456,2);
https://img-blog.csdnimg.cn/img_convert/e7d2cb6f81d282c9f11a7aee05beb353.png
2.2 取模
可以简单地认为取模运算就是取余。
MOD
输入参数1：被除数
输入参数2：除数
函数名称：求模
返回值：计算后的数字
【例子】求出10对4取模的效果。
分析：即求出10除以4的余数。
SELECT MOD(10,4);
https://img-blog.csdnimg.cn/img_convert/56106feb20d6c50158729aba3a77c1e1.png
2.3 绝对值
ABS
输入参数：数字
函数名称：绝对值
返回值：取绝对值之后的数字
【例子】求出-23的绝对值
SELECT ABS(-23);
https://img-blog.csdnimg.cn/img_convert/b0e36a49a933d4dd72ea79c4952546d3.png
3. 日期函数
3.1 提取函数
DATE
[*]输入参数：符合格式的日期时间字符串
[*]函数名称：只提取日期
[*]返回值：只包罗日期的字符串
【例子】查询全部雇员的雇用日期，要求不显示时间。
SELECT DATE(hiredate) FROM emp
;
https://img-blog.csdnimg.cn/img_convert/3ee37db7205ae5540b2c68c61f867fb8.png
同理，也可以只显示时间，使用下面的函数。
TIME
[*]输入参数：符合格式的日期时间字符串
[*]函数名称：只提取时间
[*]返回值：只包罗时间的字符串
也可以都显示，使用下面的函数。
DATETIME
[*]输入参数：符合格式的日期时间字符串
[*]函数名称：提取时间和日期
[*]返回值：包罗日期和时间的字符串
3.2 获取本地时间
也可以使用DATETIME、DATE或TIME提取本地时区的当前时间和日期。
函数还是3.1中的三个函数，但是参数换为以下两个参数
输入参数1：'now'
输入参数2：'localtime'
【例子】获得当前时区的时间和日期。
SELECT DATE('now','localtime'),TIME('now','localtime'),DATETIME('now','localtime');
https://img-blog.csdnimg.cn/img_convert/354d28a287a8c35cfa14b7a7965c5da2.png
上面的函数还可以省略第二个参数'localtime'，表示基于国际尺度时间（格林威治时间）。
SELECT DATE('now'),TIME('now'),DATETIME('now') FROM emp
;
https://img-blog.csdnimg.cn/img_convert/2ef7b063cb5ed00135072b57986156a4.png
3.3 儒略日
儒略日（Julian Day）是公元前4713年1月1日。
JULIANDAY
输入参数：'now'或一个日期
函数名称：计算机输入日期与儒略日的差值，单位天
返回值：相差的天数，精确到小数
【例子】求出每个雇员的姓名和雇佣年龄。
SELECT ename,(JULIANDAY('now')- JULIANDAY(hiredate))/365 FROM emp
;
https://img-blog.csdnimg.cn/img_convert/d3e5218abec33ce25675d090fe9f15bf.png
也可以直接使用日期作差：
SELECT ename,DATE('now')-DATE(hiredate) FROM emp
;
3.4 时间格式化
可以通过STRFTIME函数提取需要的时间或日期数据。
输入参数1：格式，如下所示。
https://img-blog.csdnimg.cn/img_convert/824edc069183d7df28112e45d54e6d3f.png
输入参数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
;
https://img-blog.csdnimg.cn/img_convert/3bb12d236280a0981e865fe1d47548c3.png
可以发现，任何数字与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
;
https://img-blog.csdnimg.cn/img_convert/102aef0cd2b517f0c89f1c3c39c7f119.png
练习：
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子句进行拓展。
更改之前的语法格式为：
https://img-blog.csdnimg.cn/img_convert/05b7ab19fcad463c6d7700c066492c9a.png
1. 原理
【例子】分别统计emp表和dept表的数据量。
SELECT COUNT(*) FROM emp
;
https://img-blog.csdnimg.cn/img_convert/edc2efd09d3d986fb67390bd8f8c12fa.png
SELECT COUNT(*) FROM dept;
https://img-blog.csdnimg.cn/img_convert/e857ecfb54d4c6f141492abeb4bc9932.png
【例子】同时对emp表与dept表统计数据量。
SELECT COUNT(*) FROM emp
,dept;
https://img-blog.csdnimg.cn/img_convert/7d86a9e4dc72c6cbd6d34a1814b8e250.png
可以看到56=14x4，两张表相乘了，这种环境被称为笛卡尔积。
https://img-blog.csdnimg.cn/img_convert/a3750b626b850ae2be4340585808471f.png
每个雇员的数据在内部都会尝试跟各个部门的数据联合，但是在联合的过程中，由于雇员只能有一个部门，因此只有一个联合是有效的，其它三个联合的数据都是无效数据。
直接进行两个表的查询，可以看到这些联合的数据。
https://img-blog.csdnimg.cn/img_convert/d3c7497a7bb2723c4dece3b3074dfa53.png
多表查询一定要消除无效的笛卡尔积，使用WHERE子句通过关联列的等值关系可以消除无效数据，在上面的例子中这个关联列就是部门号deptno，即只需要判断两个表的deptno是否相等即可。
SELECT * FROM emp
,dept WHERE emp.deptno=dept.deptno;
https://img-blog.csdnimg.cn/img_convert/c572a0fa9b135186546ec9f0374c7d74.png
也可以通过下面的写法进行改善。
-- 起别名
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,locFROM emp
e,dept d
WHERE e.deptno=d.deptno
;
https://img-blog.csdnimg.cn/img_convert/2cbe98d5a23e1b8a6178fcdd6304d508.png
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
;
https://img-blog.csdnimg.cn/img_convert/ce52d7701976951616df7384c086c37e.png
【思考】查询每个雇员的编号、姓名、职位、雇佣日期、工资、工资品级、所在部门以及位置。
分析：
第一步，确定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,locFROM emp
e,salgrade s
,dept dWHERE sal BETWEEN losal AND hisal AND e.deptno=d.deptno
;
https://img-blog.csdnimg.cn/img_convert/bc696966b0f328c40fdc2b3ed2b64c7c.png
4. 连接方式
多表查询有不同的连接方式，可以分为：
https://img-blog.csdnimg.cn/img_convert/3bd80299427eb73fd450b718c904474e.png
但是SQLite数据库有些功能不支持：
右外连接与全外连接是不支持
4.1 内连接
之前的篇章使用的多表查询就是内连接，且使用的基于SQL89尺度的语法，除此之外常用的写法还有SQL99尺度。
【例子】查询全部雇员的姓名和部门名称。
-- SQL89SELECT 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);
https://img-blog.csdnimg.cn/img_convert/0846ee48ceb84281936e372b45b4904b.png
emp表中就有15个雇员了。
此时再次实行4.1节中的内连接语句。
https://img-blog.csdnimg.cn/img_convert/06f02b943ed25f5c7271d42762559414.png
可以发现少了一个人的数据，由于JASON的deptno为NULL，所以不满足部门号相等的条件。为了让没有部门的雇员显示，需要改为外连接。
SQLite只支持基于SQL99尺度的左外连接：
-- LEFT表示让左边的表完整显示
SELECT ename,dname FROM emp
e LEFT JOIN dept d ON e.deptno=d.deptno;
https://img-blog.csdnimg.cn/img_convert/ff07cb0fc9fd9e68e1819a71bf685826.png
【练习】
查询全部雇员的姓名、月综合收入（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
第二步，如果是多表查询，确定多表之间的关系，消除笛卡尔积。
https://img-blog.csdnimg.cn/img_convert/f24caefdd005a2ca0816eeced8f2d981.png
ON e.mgr=m.empno
第三步，查看全部人还是一部分人，如果是一部分人，则继承使用WHERE子句筛选。
第四步，确定SELECT子句。
SELECT empno,ename,job,ename
第五步，确定ORDER BY子句。
第六步，拼接SQL语句，拼接时注意多表的公共列，自连接的每列都是公共列。
SELECT e.empno,e.ename,e.job,m.enameFROM emp
e JOIN emp m
ON e.mgr=m.empno
;
https://img-blog.csdnimg.cn/img_convert/458d992781300edb0c4d0a8a2a3f2a9b.png
第七步，确定是否使用外连接。
SELECT e.empno,e.ename,e.job,m.enameFROM emp
e LEFT JOIN emp m ON e.mgr=m.empno
;
https://img-blog.csdnimg.cn/img_convert/1d3179a86c62a3619de361a84d72bb60.png
【思考】查询全部雇员的姓名、工资品级，以及他们的领导的姓名。
SELECT e.ename,grade,m.enameFROM emp
e LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisalLEFT 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;
https://img-blog.csdnimg.cn/img_convert/615038c2c140f1af85bc6f2bb1a80092.png
5.3 USING指定关联字段
使用USING指出多表查询的关联字段。
SELECT * FROM emp
JOIN dept USING(deptno);
https://img-blog.csdnimg.cn/img_convert/a99449f493dd944d811086152ef4e928.png
5.4 交集与并集
https://img-blog.csdnimg.cn/img_convert/e908fbd25a80ddc3474917b9697691af.png
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=20UNIONSELECT * FROM emp
WHERE sal>1500;
https://img-blog.csdnimg.cn/img_convert/756905a995bb751f70d343809cb511d1.png
5.4.2 并集（显示重复记录）
使用UNION ALL关键字，可以将两个查询效果合并，且显示重复记录（橙色区域显示两遍）。
以5.4.1的例题为例，改为使用UNION ALL合并。
SELECT * FROM emp
WHERE deptno=20UNION ALLSELECT * FROM emp
WHERE sal>1500;
https://img-blog.csdnimg.cn/img_convert/812399df6586e5ed9f68c85168bd93d7.png
5.4.3 交集
使用INTERSECT可以获得两个查询效果的交集。
【例子】查询部门20且工资高于1500的雇员信息。
SELECT * FROM emp
WHERE deptno=20INTERSECTSELECT * FROM emp
WHERE sal>1500;
https://img-blog.csdnimg.cn/img_convert/83632499cc35c1186fd40d1e488d6a07.png
交集与并集的使用需要注意让两个查询的格式相同，查询效果的列能逐一对应。
删除之前添加的数据。
DELETE FROM emp
WHERE empno=6666;
五、分组查询
什么环境需要分组？
班级里男生一组，女生一组；公司研发部一组、市场组一组、测试部一组......
分组的条件是某些举动和特点具有同一性，好比在emp表中job和deptno等字段可以进行分组。一条数据也能独占一组，但是每组都是一条数据则毫无意义。
1. 概念
分组查询使用关键字GROUP BY实现，语法规则如下：
https://img-blog.csdnimg.cn/img_convert/27236630be4ca6d6d6008a542f2076e0.png
【例子】按照职位分组，查询每个职位的均匀工资、最高工资、最低工资和人数。
分析：
第一步，确定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
;
https://img-blog.csdnimg.cn/img_convert/80856f27934249026faf32b4d5356ed6.png
【练习】
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=20GROUP BY job
;
3. 统计公司全部部门的均匀工资，按照均匀工资降序排序。
SELECT deptno,AVG(sal)FROM emp
GROUP BY deptnoORDER 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)>2000GROUP BY job
;
实行后，直接报错。
https://img-blog.csdnimg.cn/img_convert/6968a46b0ec43be0114365f82ea04a12.png
意思是WHERE子句中不允许使用AVG函数，现实上WHERE中不允许出现任何统计函数，由于WHERE是在GROUP BY之前实行的，如果是在分组后进行筛选，应该引入HAVING子句来完成这个功能。
增长HAVING子句到实行次序中，语法格式如下。
https://img-blog.csdnimg.cn/img_convert/5145a41f145c86fe0a85bd7fdb11272d.png
修改上一道题的答案为：
SELECT job,AVG(sal)FROM emp
GROUP BY job
HAVING AVG(sal)>2000;
https://img-blog.csdnimg.cn/img_convert/4aeb9fb74d94a16267dba58f6bdad5b4.png
再次分析一下WHERE和HAVING的区别。
https://img-blog.csdnimg.cn/img_convert/9492eadca78273fae79650b37597d3f9.png
总结：
[*]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;
https://img-blog.csdnimg.cn/img_convert/81f492fa0f2d85c2d59488a389471c13.png
【练习】查询公司各个工资品级的人数与均匀工资，要求显示均匀工资高于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;
https://img-blog.csdnimg.cn/img_convert/96a986e8674b5adec701f8200391ea48.png
【思考】统计出公司领取佣金和不领取佣金的人数与均匀工资（sal）。
分析：如果按照通例分组来做，如下所示。
https://img-blog.csdnimg.cn/img_convert/7b76d1cda0f79199a10009f001b6d270.png
可以看到效果不符合题意，这种环境下需要手动分组，使用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 NULLUNIONSELECT '不领取佣金',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
);
https://img-blog.csdnimg.cn/img_convert/c6dd99c4828b4db863db0a0751c6f369.png
【练习】统计出公司最早雇佣的雇员信息（使用子查询完成）。
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
);
https://img-blog.csdnimg.cn/img_convert/ecc07cc4af718a4d3074e491084f5085.png
【练习】求出与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');
https://img-blog.csdnimg.cn/img_convert/7b256d992a3ab89d6297568030b2e1c1.png
【例子】显示工资跟各个贩卖不相同的雇员信息。
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 deptnoHAVING AVG(sal)>(SELECT AVG(sal) FROM emp
);
https://img-blog.csdnimg.cn/img_convert/c5b2b645030ef9ec13e14876e88d1cac.png
【思考】查询均匀工资最低的职位的信息（此职位的名称、人数与此职位的均匀工资）
分析：
第一步，查询每个职位的均匀工资。
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;
https://img-blog.csdnimg.cn/img_convert/fbba5b64d96a2f352589bd344df57d47.png
这种方法无需掌握。
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;
https://img-blog.csdnimg.cn/img_convert/494d15b15500c7129279076c838fbc01.png
第二步，把上一步查询的效果作为一张表与dept表进行多表查询。
SELECT d.deptno,dname,loc,temp.rs,temp.asalFROM dept d LEFT JOIN( SELECT deptno,COUNT(empno) rs,AVG(sal) asal FROM emp
GROUP BY deptno)temp ON d.deptno=temp.deptno;
https://img-blog.csdnimg.cn/img_convert/5e8fe726d61de538d6aa4c5bd3539be4.png
https://img-blog.csdnimg.cn/img_convert/11bc6a82f79d27fcca5d690f1992ab61.png
只管搞明确，实在搞不懂的必须掌握实现方式一。
现实的开辟中，有可能会遇到巨大的数据量，不妨把当前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
;
https://img-blog.csdnimg.cn/img_convert/d9df7abec1e8669033f76a9bfa883b52.png
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);
https://img-blog.csdnimg.cn/img_convert/cc6d57e3e1170d5d11aebf542362c0ac.png
https://img-blog.csdnimg.cn/img_convert/663b9ea768c12a6d1bae3aa82a1a9e26.png
实现方式二：使用简易格式
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子句表示更新全部数据。
【例子】将‘SMITH’的工资修改为5000，佣金修改为2000
UPDATE myemp SET sal=5000,comm=2000 WHERE ename='SMITH';
【例子】将全部贩卖的工资修改为2000
UPDATE myemp SET sal=2000 WHERE job='SALESMAN';
https://img-blog.csdnimg.cn/img_convert/1724603c585c724d08ed3131c2eceeb3.png
【练习】
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');
https://img-blog.csdnimg.cn/img_convert/f1fe657fd1650411ee1dd3bf81c26f2e.png
3. 删除数据
删除数据的语法格式如下：
DELETE FROM 表名
需要注意的是，如果不写WHERE子句，表示删除全部数据！
【例子】删除全部30部门的雇员。
DELETE FROM myemp WHERE deptno=30;
第一次实行，显示
https://img-blog.csdnimg.cn/img_convert/7e1c1d4e316269d781d47541ba266980.png
；再次实行，显示，由于这些数据已经被删除了。
【练习】
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常用的存储类有：
https://img-blog.csdnimg.cn/img_convert/fb99f1330c6bee133edc5b9671e25176.png
存储类又可以动态地细分为不同的亲和类型，仅做相识。
https://img-blog.csdnimg.cn/img_convert/bbb97dad012433fccb410eff0faaa629.png
2. 创建表
创建一张表的语法格式如下：
CREATE TABLE 表名(
列名1 类型 ,
列名2 类型 ,
...,
列名n 类型
);
如果创建表的语句较短，也可以写成一行：
CREATE TABLE 表名(列名1 类型 ,列名2 类型 ,...,列名n 类型 );
【例子】创建一张表。
CREATE TABLE hqyj23032(
id INTEGER,
nameTEXT DEFAULT '佚名',
birth TEXT DEFAULT CURRENT_DATE,
sal REAL DEFAULT 0
);
https://img-blog.csdnimg.cn/img_convert/9598d1ea7361725b3270a45a94d88669.png
可以对这个表进行正常的增编削查。
https://img-blog.csdnimg.cn/img_convert/e27e8a009351fb7f018898b7a0290e62.png
可以使用下面的语句删除表。
DROP TABLE 表名;
3. 束缚
束缚是在表的数据列中增长欺压实行的规则，例如上一节中的默认值，如果一列没有默认值束缚且不手动添加数值，则会使用NULL表示这个数据。
除了默认值以外，还有其它的束缚，这些束缚可以保障数据表的准确性和可靠性。
一共有如下束缚类型：
https://img-blog.csdnimg.cn/img_convert/85a07a3667c8005dc44b9a812b34b435.png
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);
https://img-blog.csdnimg.cn/img_convert/3b66f5a9b12fe5a077f8b26a85976fbc.png
插入失败。
3.2 唯一束缚
【例子】创建一个表，包罗唯一束缚的列，并测试。
CREATE TABLE member(id INTEGER,name TEXT UNIQUE);
-- 可以执行
INSERT INTO member VALUES(1,'张三');
-- 不可以执行
INSERT INTO member VALUES(2,'张三');
https://img-blog.csdnimg.cn/img_convert/3d5ce2a0d4f6bdba90c2ac9b079b4a3c.png
当重复时显示
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,'李四');
https://img-blog.csdnimg.cn/img_convert/e73e9f9ef5929ae5756f24f22492cae7.png
上面第9行代码会报错。
-- 自动填充序列
INSERT INTO member VALUES(NULL,'王五');
https://img-blog.csdnimg.cn/img_convert/840ea66fcf4f9a11c735d887f35af7de.png
主键束缚还可以增长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('王五');
https://img-blog.csdnimg.cn/img_convert/e1cf13e4f3c23cec1b09e92a30fd65db.png
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);
https://img-blog.csdnimg.cn/img_convert/71904183b5dd8868233acbe744d69a3e.png
其它内容可以自学：
https://img-blog.csdnimg.cn/img_convert/656e80058aa0098e3ef5f3be74ed6ec5.png
https://img-blog.csdnimg.cn/img_convert/c6ab4a3ac901a8ee70bbb001b8f7a84d.png
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]