数据库基础学习（第四天）-优快云博客

2.2 求模

可以简单的认为求模运算类似于取余运算。

MOD

函数功能：求模

输入参数：

● 被除数

● 除数

返回值：模

【例子】10对3取模。

2.3 绝对值

ABS

函数功能：求绝对值

参数：要处理的数字

返回值：处理后的数字

【例子】对-123求绝对值

3. 日期时间函数

主要处理日期和时间。

3.1 获取时间

DATETIME

函数功能：获取某个时区的日期和时间。

输入参数：

● 参数1:基准日期时间，常用值为'now'

● 参数2：时区，常用值'localtime'

返回值：符合格式的日期时间字符串

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

【例子】查询每个雇员的姓名、职位和雇佣天数。

可以发现求出的是雇佣的年数而非天数。

3.2 儒略日

JULIANDAY

函数功能：返回值是儒略日(公元前4714年11月24日)到设定日期的天数。

输入参数：日期或'now'

返回值:天数

【例子】查询每个雇员的姓名、职位和雇佣天数。

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

3.3 格式化时间

STRFTIME

函数功能:格式化时间

参数：

● 参数1:格式符号(字符串类型)

● 参数2：要提取的日期和时间数据

返回值：从参数2中按照参数1的格式提取的数据，返回值是字符串类型。

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

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

【练习】查询上半年雇佣的雇员信息。

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

练习4.2

1. 选择部门30的所有员工

SELECT * FROM emp WHERE deptno=30;

2. 列出所有柜员（CLERK）的姓名、编号和部门编号。

SELECT ename,empno,deptno FROM emp WHERE job='CLERK';

3. 列出佣金高于薪金30%的员工姓名、职位和收入（薪金+佣金）;

SELECT ename,job,comm+sal FROM emp WHERE comm>0.3*sal;

4. 找出部门30中所有经理和部门20中所有柜员的详细资料。

SELECT * FROM emp
WHERE job='MANAGER' AND deptno=30 OR job='CLERK' AND deptno=20;

5. 找出部门30中所有经理、部门20中所有柜员以及既不是经理又不是柜员并且薪金大于等于2000的员工的详细资料。

SELECT * FROM emp
WHERE (job='MANAGER' AND deptno=30) OR (job='CLERK' AND deptno=20) 
OR (job!='MANAGER' AND job!='CLERK' AND sal>=2000);

6. 收取佣金的工作有哪些？

SELECT DISTINCT job FROM emp
WHERE comm IS NOT NULL;

7. 找出不收取佣金或佣金低于100的员工。

SELECT * FROM emp
WHERE comm<100 OR comm IS NULL;

8. 显示不带有A的员工的姓名

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

9.显示姓名字段的任何位置包含E的所有员工的详细资料，显示的结果按照基本工资从高到低排序，如果基本工资相同则按照雇佣时间从早到晚排序，如果雇佣的日期相同，则按照职位的字母顺序排序。

SELECT * FROM emp WHERE ename LIKE '%E%'
ORDER BY sal DESC,hiredate ASC,job ASC;

10.显示非经理员工的姓名、编号、部门编号和总收入INCOME（薪金+佣金），按照总收入从高到低排序；如果总收入相同，按照薪金从高到低排序；如果薪金相同，按照部门号从小到大排序。

SELECT ename,empno,deptno,sal+comm
FROM emp
WHERE job!='MANAGER'
ORDER BY sal+comm DESC,sal DESC,deptno ASC;

可以发现，NULL与任何数字计算都会把结果同化为NULL。

4. 空值函数

IFNULL

函数功能:可以把NULL转换为人任何指定的数字，从而进行数学计算和统计。

输入参数：

● 参数1:可能为NULL的数据。

● 参数2：如果参数1为NULL，则替换为此数据。

返回值：如果参数1为NULL，则返回参数2；如果参数1不为NULL，则返回参数1。

【例子】显示非经理员工的姓名、编号、部门编号和总收入INCOME（薪金+佣金），按照总收入从高到低排序；如果总收入相同，按照薪金从高到低排序；如果薪金相同，按照部门号从小到大排序。

SELECT ename,empno,deptno,sal+comm
FROM emp
WHERE job!='MANAGER'
ORDER BY sal+comm DESC,sal DESC,deptno ASC;

可以改为：

SELECT ename,empno,deptno,sal+IFNULL(comm,0)
FROM emp
WHERE job!='MANAGER'
ORDER BY sal+IFNULL(comm,0) DESC,sal DESC,deptno ASC;

上面的SQL语句中sal+IFNULL(comm,0)执行了两次，可以通过起别名的方式变形二一次，优化如下:

SELECT ename,empno,deptno,sal+IFNULL(comm,0) income
FROM emp
WHERE job!='MANAGER'
ORDER BY income DESC,sal DESC,deptno ASC;

需要注意的是，在SELECT子句中其的别名可以在ORDERBY中使用的原因是：SELECT子句在ORDERBY之前执行。

5. 统计函数

与Excel相似，数据库也有五个统计函数：

● COUNT计数

● SUM求和

● AVG平均值

● MAX最大值

● MIN最小值

【例子】统计公司每个月支付的总基本工资，平均基本工资、最高基本工资、最低基本工资、雇佣人数

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

注意：*代表所有行

【练习】

1. 统计公司支付的总年薪与平均年薪（算上基本工资+佣金）

SELECT SUM((sal+IFNULL(comm,0))*12),AVG((sal+IFNULL(comm,0))*12)
FROM emp;

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

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

【注意】关于COUNT的用法

COUNT函数的参数可能有三种情况：

● *

● 字段(列名)

NULL不计算数量

● DISTINCT字段(列名)

去重后统计

统计函数会在后续分组中大量使用。

3. 多表查询

1. 定义

之前的查询都是单表查询，多表查询指的是一个SQL语句可以同时查询多张表，主要是针对FROM子句进行扩展。

2. 笛卡尔积

【例子】统计emp表的数量，统计dept表的数量。

【例子】同时统计emp和dept表的数量

56=14*4

进一步通过查询两个表数据展示：

会发现每个人出现了四次

这个过程就是多表查询生成笛卡尔积的过程。

消除笛卡尔积的思路就是让两个表的关联字段(deptno)建立等式关系，从而消除无效关联。

可以利用where加条件从而消除无效关联：

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

可以通过别名继续优化上面的SQL语句：

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

【例子】查询每一个雇员的编号、姓名、职位、工资、部门编号、部门名称、部门位置。

分析：

1. 确定FROM子句

FROM emp e,dept d

2. 确定WHERE子句

--消除笛卡尔积
WHERE e.deptno = d.deptno

此题所有人参与，不需要继续在WHERE中加条件了。

3. 确定SELECT子句

需要判断显示字段(列)是否有多张表共存的内容，如有需要区分。

SELECT empno,ename,job,sal,e.deptno,dname,loc

4. 确定ORDERBY子句

5. 拼接内容、

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

【思考】查询出每个雇员的编号、姓名、职位、工资、雇佣日期、工资等级，结果按照工资从高到底排序。

分析:

1. 确定FROM子句

FROM emp e,salgrade s

2. 确定WHERE子句

--消除笛卡尔积
WHERE sal BETWEEN losal AND hisal

3. 确定SELECT子句

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

4. 确定ORDERBY子句

ORDER BY sal DESC

5. 合并

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

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

分析：

1. 确定FROM子句

FROM emp e,salgrade s,dept d

2. 确定WHERE子句

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

3. 确定SELECT子句

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

4. 确定ORDERBY子句

5. 合并

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