SQLite数据库简单小入门学习(二)
承接上篇博客:👉🔗SQLite数据库简单小入门学习(一)
目录
- 二、SQLite数据库
-
- (三)SQL语言
-
- 3.查询排序
- 4.常用函数
-
- (1)时间和处理函数
- (2)时间日期格式化函数
- (5)统计函数
- 5.多表查询
二、SQLite数据库
(三)SQL语言
3.查询排序
可以制定一列或多列按照升序或降序的顺序排序。使用 ORDER BY 子句配合两种排序方式的关键字:
关键字 | 含义 |
---|---|
ASC | 升序 |
DESC | 降序 |
例1:查询所有雇员的信息,按照工资降序排布。
SELECT * FROM emp ORDER BY sal DESC;
例2:查询所有雇员的信息,按照工资降序排布。如果工资相同,
则按照雇佣日期从早到晚排布。
SELECT *
FROM emp
ORDER BY sal DESC,hiredate ASC;
练1:查询所有雇员的信息,按照年薪排序,年薪越高的越靠前。
SELECT *
FROM emp
ORDER BY sal*12 DESC;
作业:
(1)查询部门30的雇员信息。
SELECT * FROM emp WHERE deptno==30;
(2)查询所有柜员的姓名、编号和部门编号。
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)查询出部门30中所有经理、部门20中所有柜员以及既不是经理又不是柜员的雇员信息。
SELECT *
FROM emp
WHERE deptno=30 AND job='MANAGER' OR deptno=20 AND job='CLERK' OR job!='MANAGER' AND job!='CLERK';
或
SELECT *
FROM emp
WHERE deptno=30 AND job='MANAGER' OR deptno=20 AND job='CLERK' OR job NOT IN ('MANAGE','CLEAR');
(5)找到不收取佣金或佣金低于100的雇员姓名。
SELECT ename 姓名
FROM emp
WHERE comm is NULL OR comm<100;
(6)查询姓名不包含字母A的雇员姓名与编号。
SELECT ename 姓名,empno 编号
FROM emp
WHERE ename NOT LIKE '%A%';
4.常用函数
函数是一组完成预设的特定功能的代码,使用时只需要调用即可。
函数调用主要包含三部分内容:
- 输入参数 类似于烹饪的原材料,表示处理之前的源数据。
- 函数名称 类似于烹饪的方式,表示处理数据的方式。
- 返回值 类似于烹饪的成品,表示数据处理的结果。
(1)时间和处理函数
函数名称 | 含义 |
---|---|
DATE | 处理日期 |
TIME | 处理时间 |
DATETIME | 处理日期和时间 |
输入参数:
参数1:‘now’
参数2:‘localtime’,此参数可以省略。
返回值:
当前时区的当前时间或日期,如果省略参数2,返回格林威治时间。
例子:分别显示当前的格林威治时间日期和东八区时间日期。
SELECT DATETIME('now'),DATETIME('now','localtime');
(2)时间日期格式化函数
函数名称:
STRFTIME 把某个时间日期转换成自定格式,通常用于提取部分数据
输入参数:
参数1:时间和日期的格式
参数2:要处理的原始时间或日期
返回值:
按照参数1的格式处理后的参数2的数据,需要注意返回的类型是字符串类型。
例子:查询在1981年雇佣的雇员信息。
SELECT *
FROM emp
WHERE STRFTIME('%Y',hiredate)='1981';
例子:查询在周一雇佣的雇员信息。
SELECT *
FROM emp
WHERE STRFTIME('%w',hiredate)=1;
练习:
(1)查询在上半年雇佣的雇员信息。
SELECT *
FROM emp
WHERE STRFTIME('%m',hiredate) BETWEEN '01' AND '06';
(2)查询在5月雇佣的柜员信息。
SELECT *
FROM emp
WHERE job=='CLERK' AND STRFTIME('%m',hiredate)='05';
(3)查询在每个月22号雇佣的销售信息。
SELECT *
FROM emp
WHERE STRFTIME("%d",hiredate)= "22" AND job= "SALESMAN";
- 空值函数
例子:查询所有雇员的姓名和月综合收入(薪金+佣金)
-- 错误的做法,NULL参与数学计算时,会把结果同化为NULL
SELECT ename,sal+comm income FROM emp;
可以使用空值函数处理上述问题。
函数名称:IFNULL
输入参数:
参数1:可能为空的列名
参数2:如果参数1的数值为NULL,则替换的数值。
返回值:
如果参数1为NULL,则返回值参数2;
如果参数1不为NULL,则返回值参数1。
对上面的例子使用IFNULL函数处理
SELECT ename,sal+IFNULL(comm,0) income FROM emp;
练习:
查询所有雇员的姓名、职位和年薪(包含佣金和薪金),
如果没有佣金,则每个月补贴200元。
SELECT ename,job,12*(sal+IFNULL(comm,200)) income FROM emp;
(5)统计函数
常用的统计函数有五个:
COUNT | 计数 |
AVG | 平均值 |
SUM | 求和 |
MAX | 最大值 |
MIN | 最小值 |
例子:求出公司中人数、支付的总工资、平均工资、最高工资和最低工资。
SELECT COUNT(*),SUM(sal),AVG(sal),MAX(sal),MIN(sal) FROM emp;
练习:
(1)统计公司支付的月总收入和月平均收入(收入=薪金+佣金)
SELECT SUM(sal+IFNULL(comm,0)),AVG(sal+IFNULL(comm,0))
FROM emp;
(2)求出公司最早和最晚的雇佣日期。
SELECT MIN(hiredate),MAX(hiredate) FROM emp;
【分页查询】
当查询结果返回的数据量很大时,可以分多页展示数据。
分页查询使用LIMIT和OFFSET子句实现。
因为分页查询并不是特别常用,因此通常不把这两个子句加入标准子句的格式中。
但是分页查询子句都是最后执行,因此所有分页查询的操作可以先不考虑分页查询,
最后再加上分页的语句。
LIMIT 可以控制结果显示的数量,LIMIT可以单独使用,也可以与OFFSET一起使用。
OFFSET 必须与LIMIT同时使用,表示跳过前几条数据。
例子:
查询默认排序中前五个雇员的信息。
SELECT * FROM emp LIMIT 5;
练习:
查询公司工资最高的三个雇员信息。
SELECT * FROM emp ORDER BY sal DESC LIMIT 3;
例子:
查询公司薪金第四名到第八名的雇员信息。
SELECT * FROM emp ORDER BY sal DESC LIMIT 5 OFFSET 3;
分页查询公式:
设每页显示的数量为n,当前页码为m,则分页查询的公式为:
SELECT * FROM 表名 LIMIT n OFFSET (m-1)*n;
例子:
每页显示6条数据,查询第二页的内容。
分析:n=6,m=2,带入公式
SELECT * FROM emp LIMIT 6 OFFSET 6;
练习:
假设京东笔记本电脑的表名为laptop,每页显示60个电脑数据,
查询第45页的内容。
SELECT * FROM laptop LIMIT 60 offset 44*60;
5.多表查询
之前的所有查询都是单表查询,因为FROM子句中只有一个表。
实际上FROM子句后面跟的表名可以有多个。
同时查询多张表,这种查询就叫多表查询。
例子:分别统计emp表和dept表的数据量。
– 统计emp表数据量
SELECT COUNT(*) FROM emp;
– 统计dept表数据量
SELECT COUNT(*) FROM dept;
– 同时统计emp表与dept表的数据量
SELECT COUNT(*) FROM emp,dept;
可以看到同时统计两张表的数据量,结果是两张表的数据量的乘积。
直接查询两个的内容。
SELECT * FROM emp,dept;
通过结果可以看到每个人都产生了一些冗余数据,这种现象被称为“笛卡尔积”。
消除“笛卡尔积”的方法就是消除冗余数据,需要找到两个表之间的关系。
这种关系通常是通过关联字段(列)表示。
对于emp表与dept表而言,其关联字段为deptno,只有两个表的deptno相同时,
结合的数据才是有效。
消除笛卡尔积:
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
例子:查询所有雇员的姓名和位置。
SELECT ename,loc
FROM emp,dept
WHERE emp.deptno=dept.deptno;
练习:
查询部门20的雇员的姓名和部门名称。
SELECT ename,dname
FROM emp,dept
WHERE emp.deptno=dept.deptno AND dept.deptno=20;
特殊的多表查询例子:
查询所有雇员的姓名、职位和工资等级。
分析:
emp表与salgrade表都需要使用,但是无法建立等式关系,
可以通过BETWEEN AND建立区间关系,让每个雇员的薪金位于
每个等级下限与上限之间。
SELECT ename,job,grade
FROM emp,salgrade
WHERE sal BETWEEN losal AND hisal;
挑战:
查询所有销售的姓名、位置和工资等级。
SELECT ename,loc,grade
FROM emp,dept,salgrade
WHERE emp.deptno=dept.deptno AND sal BETWEEN losal AND hisalAND job='SALESMAN';
【今晚作业】
(1)查询公司工资等级3以上(包含3)的雇员编号和姓名。
SELECT empno,ename
FROM emp,salgrade
WHERE sal BETWEEN losal AND salgrade.hisal AND grade>=3;
(2)查询公司所有雇员的姓名、编号、雇佣年份和部门名称。
SELECT ename,empno,hiredate,dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;
(3)查询所有雇员的雇佣时长(单位年)。
SELECT ename,DATETIME('now','localtime')-STRFTIME('%Y',hiredate)
FROM emp;
(4)查询所有在12月雇佣的雇员姓名、编号、月薪(包括佣金)。
SELECT ename,empno,sal+IFNULL(comm,0)
FROM emp,salgrade
WHERE STRFTIME('%m',hiredate)='12';
(5)查询所有非经理的雇员姓名、编号、部门编号、月薪(包括佣金),按照月薪降序排布。
周二晚上10点之前提交。
SELECT ename,empno,deptno,sal+IFNULL(comm,0)
FROM emp
WHERE job!='MANAGER' ORDER BY sal DESC;