7.1.5转换函数

隐式数据类型转换

隐式数据类型转换是指MySQL服务器能够自动地进行类型转换。如：可以将标准格式的字串日期自动转换为日期类型。

MySQL字符串日期格式为：‘YYYY-MM-DD HH:MI:SS’ 或 ‘YYYY/MM/DD HH:MI:SS’;

显示数据类型转换

显示数据类型转换是指需要依赖转换函数来完成相关类型的转换。

如：

• DATE_FORMAT(date,format) 将日期转换成字符串;
  
• STR_TO_DATE(str,format) 将字符串转换成日期;
  

示例一：

向 employees 表中添加一条数据，雇员ID：400，名字：张三，email：zhangsan@qq.cn ，入职时间：2049 年 5 月 5 日，工作部门：‘IT_PROG’。

insert into employees(EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID) values(400,'zhangsan','zhangsanqq.cn', STR_TO_DATE('2049 年 5 月 5 日','%Y 年%m 月%d 日'),'IT_PROG');

示例二：

查询 employees 表中雇员名字为 King 的雇员的入职日期，要求显示格式为 yyyy 年 MM 月 dd 日。

select DATE_FORMAT(hire_date,'%Y 年%m 月%d 日') from employees where last_name = 'King';

7.1.6通用函数

示例一：

查询部门编号是50或者80的员工信息，包含他们的名字、薪水、佣金。在income列中，如果有佣金则显示‘SAL+COMM’，无佣金则显示'SAL'。

SELECT last_name, salary, commission_pct,     
if(ISNULL(commission_pct),'SAL','SAL+COMM') income 
FROM employees 
WHERE department_id IN (50, 80);

示例二：

计算雇员的年报酬，你需要用 12 乘以月薪，再加上它的佣金 (等于年薪乘以佣金

SELECT last_name, salary, IFNULL(commission_pct, 0), (salary*12) +(salary*12*IFNULL(commission_pct, 0)) AN_SAL 
FROM employees;

示例三

查询员工表，显示他们的名字、名字的长度该列名为expr1，姓氏、姓氏的长度该列名为expr2。在result列中，如果名字与姓氏的长度相同则显示空，如果不相同则显示名字长度。

SELECT first_name, LENGTH(first_name) "expr1",last_name, LENGTH(last_name) "expr2",     
   NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM employees;

示例四：

查询员工表，显示他们的名字，如果 COMMISSION_PCT 值是非空，显示它。如果COMMISSION_PCT 值是空，则显示 SALARY 。如果 COMMISSION_PCT 和SALARY 值都是空，那么显示 10。在结果中对佣金列升序排序。

SELECT last_name,
COALESCE(commission_pct, salary, 10) comm 
FROM employees 
ORDER BY commission_pct;

示例五：

查询员工表，如果 JOB_ID 是 IT_PROG，薪水增加 10%；如果 JOB_ID 是 ST_CLERK，薪水增加 15%；如果 JOB_ID 是 SA_REP，薪水增加 20%。对于所有其他的工作角色，不增加薪水。

SELECT last_name, job_id, salary, 
    CASE job_id WHEN 'IT_PROG' THEN 1.10*salary 
          WHEN 'ST_CLERK' THEN 1.15*salary 
          WHEN 'SA_REP' THEN 1.20*salary 
    ELSE salary END "REVISED_SALARY"
FROM employees;

7.1.7单行函数练习

1.显示受雇日期在 1998 年 2 月 20 日 和 2005 年 5 月 1 日 之间的雇员的名字、岗位和受雇日期。按受雇日期顺序排序查询结果。

select LAST_NAME,JOB_ID,HIRE_DATE 
FROM employees 
WHERE  HIRE_DATE BETWEEN '1998-02-20' AND '2005-05-01' 
ORDER BY HIRE_DATE;

2.显示每一个在 2002 年受雇的雇员的名字和受雇日期。

select LAST_NAME,HIRE_DATE 
FROM employees 
WHERE  YEAR(HIRE_DATE) = 2002;

或者

select LAST_NAME,HIRE_DATE 
FROM employees 
WHERE  HIRE_DATE LIKE '2002%';

3.对每一个雇员，显示 employee number、last_name、salary 和 salary 增加 15%，并且表示成整数，列标签显示为 New Salary。

SELECT EMPLOYEE_ID 'employee number',LAST_NAME,SALARY,ROUND(1.15*SALARY) 'New Salary'
FROM employees;

4.写一个查询，显示名字的长度，对所有名字开始字母是 J、A 或 M 的雇员。用雇员的 lastname排序结果。

SELECT LAST_NAME, LENGTH(LAST_NAME)
FROM employees
WHERE SUBSTR(LAST_NAME,1,1) IN ('J','A','M')
ORDER BY LAST_NAME;

5.创建一个查询显示所有雇员的 last name 和 salary。将薪水格式化为 15 个字符长度，用 $左填充 。

SELECT LAST_NAME, LPAD(SALARY,15,'$')
FROM employees;

6.创建一个查询显示雇员的 last names 和 commission (佣金) 比率。如果雇员没有佣金，显示 “No Commission”，列标签 COMM。

SELECT LAST_NAME, IFNULL(COMMISSION_PCT,'No Commission') COMM
FROM employees;

7.写一个查询，按照下面的数据显示所有雇员的基于 JOB_ID 列值的级别。

1. SELECT JOB_ID,
2. CASE JOB_ID
3.        WHEN 'AD_PRES' THEN  'A' 
4.          WHEN 'ST_MAN' THEN  'B' 
5.          WHEN 'IT_PROG' THEN  'C'
6.          WHEN 'SA_REP' THEN  'D'
7.          WHEN 'ST_CLERK' THEN  'E'
8.     ELSE
9.         0
10. END
11. FROM employees;

7.2多行函数 （聚合函数）

聚合函数也称之为多行函数，组函数或分组函数。聚合函数不象单行函数，聚合函数对行的分组进行操作，对每组给出一个结果。如果在查询中没有指定分组，那么聚合函数则将查询到的结果集视为一组。

7.2.1聚合函数的类型

7.2.2聚合函数使用方式

7.2.3聚合函数的使用原则

• DISTINCT 使得函数只考虑不重复的值；
• 所有聚合函数忽略空值。为了用一个值代替空值，用 IFNULL 或 COALESCE 函数。

7.2.4AVG 和 SUM 函数

   AVG(arg)函数

    对分组数据做平均值运算。

    arg:参数类型只能是数字类型。

  SUM(arg)函数

    对分组数据求和。

   arg:参数类型只能是数字类型。

示例：

计算员工表中工作编号含有REP的工作岗位的平均薪水与薪水总和。

SELECT AVG(salary),SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

7.2.5MIN 和 MAX 函数

MIN(arg)函数

求分组中最小数据。

arg:参数类型可以是字符、数字、 日期。

MAX(arg)函数

求分组中最大数据。

arg:参数类型可以是字符、数字、 日期。

示例：

查询员工表中入职时间最短与最长的员工，并显示他们的入职时间。

SELECT MIN(hire_date), MAX(hire_date) FROM employees;

7.2.6count函数

返回分组中的总行数。

COUNT 函数有三种格式：

• COUNT(*)：返回表中满足 SELECT 语句的所有列的行数，包括重复行，包括有空值列的行。
  
• COUNT(expr)：返回在列中的由 expr 指定的非空值的数。
  
• COUNT(DISTINCT expr)：返回在列中的由 expr 指定的唯一的非空值的数。
  

使用 DISTINCT 关键字

• COUNT(DISTINCT expr) 返回对于表达式 expr 非空并且值不相同的行数
  
• 显示 EMPLOYEES 表中不同部门数的值
  

示例一：

显示员工表中部门编号是80中有佣金的雇员人数。

SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;

7.2.7数据分组

在没有进行数据分组之前，所有聚合函数是将结果集作为一个大的信息组进行处理。但是，有时，则需要将表的信息划分为较小的组，可以用 GROUP BY 子句实现。

GROUP BY 子句语法

原则

• 使用 WHERE 子句，可以在划分行成组以前过滤行。
• 如果有WHERE子句，那么GROUP BY 子句必须在WHERE的子句后面。
• 在 GROUP BY 子句中必须包含列。

使用 GROUP BY 子句

下面是包含一个 GROUP BY 子句 SELECT 语句的求值过程：

• SELECT 子句指定要返回的列：
  
• 在 EMPLOYEES 表中的部门号
  − GROUP BY 子句中指定分组的所有薪水的平均值
  − FROM 子句指定数据库必须访问的表：EMPLOYEES 表。
  
• WHERE 子句指定被返回的行。因为无 WHERE 子句默认情况下所有行被返回。
  
• GROUP BY 子句指定行怎样被分组。行用部门号分组，所以 AVG 函数被应用于薪水列，以计算每个部门的平均薪水。
  

示例：

计算每个部门的员工总数。

SELECT DEPARTMENT_ID, COUNT(*) FROM employees GROUP BY DEPARTMENT_ID;

八、多表查询

多表查询分类

• sql92标准：内连接(等值连接 、非等值连接 、 自连接)。
• sql99标准：内连接、外连接(左外、右外、全外(MySQL不支持全外连接))、交叉连接。