SQL检索数据基础操作讲解
需求：
检索表里所有行的数据。
解决方法：
通过SQL的关键字*来匹配到所有行和列，结合SELECT FROM 即可满足需求。
Oracle、Sql server、Mysql:
SELECT*
FROM emp;
等价于：
SELECT empno, ename, job, mgr, hiredate, sal, comm,deptnoFROM emp;
执行结果：
empno
ename
job
mgr
hiredate
sal
comm
deptno
7369
SMITH
CLERK
7902
1980/12/17 0:00:00
800.00
20
7499
ALLEN
SALESMAN
7698
1981/2/20 0:00:00
1600.00
300.00
30
7521
WARD
SALESMAN
7698
1981/2/22 0:00:00
1250.00
500.00
30
7566
JONES
MANAGER
7839
1981/4/2 0:00:00
2975.00
20
7654
MARTIN
SALESMAN
7698
1981/9/28 0:00:00
1250.00
1400.00
30
7698
BLAKE
MANAGER
7839
1981/5/1 0:00:00
2850.00
30
7782
CLARK
MANAGER
7839
1981/6/9 0:00:00
2450.00
10
7788
SCOTT
ANALYST
7566
1987/7/13 0:00:00
3000.00
20
7839
KING
PRESIDENT
1981/11/17 0:00:00
5000.00
10
7844
TURNER
SALESMAN
7698
1981/9/8 0:00:00
1500.00
0.00
30
7876
ADAMS
CLERK
7788
1987/7/13 0:00:00
1100.00
20
7900
JAMES
CLERK
7698
1981/12/3 0:00:00
950.00
30
7902
FORD
ANALYST
7566
1981/12/3 0:00:00
3000.00
20
7934
MILLER
CLERK
7782
1982/1/23 0:00:00
1300.00
10
7.2.2 从表中查询部分行需求：
查询雇员表emp里员工号是7782的员工的详细信息。
解决方法：
先找出emp表的员工号的字段名，再结合SELECT FROM WHERE来实现。其中这里通过where关键字来限制检索的行。
Oracle、Sql server、Mysql:
SELECT*
FROM emp
WHERE empno=7782;
执行结果:
empno
ename
job
mgr
hiredate
sal
comm
deptno
7782
CLARK
MANAGER
7839
1981/6/9 0:00:00
2450.00
10
7.2.3 查询满足某个条件行
需求：
查询雇员表emp中所有的部门号等于10的行。
解决方法：
先找出emp表的部门号的字段名，再结合SELECT FROM WHERE来实现。其中的WHERE即是在加过滤条件，多部门表进行筛选，只取部门号为10的数据。
Oracle、Sql server、Mysql:
SELECT*
FROM emp
WHERE deptno=10;
执行结果:
empno
ename
job
mgr
hiredate
sal
comm
deptno
7782
CLARK
MANAGER
7839
1981/6/9 0:00:00
2450.00
10
7839
KING
PRESIDENT
1981/11/17 0:00:00
5000.00
10
7934
MILLER
CLERK
7782
1982/1/23 0:00:00
1300.00
10
注:
1 这里字段的判断支持诸如不等于(“<>”)、等于(“=”),大于（“>”）、小于(“<”) 、,
大于等于（“>=”）、小于等于(“<=”)，不等(“！”)等关系比较符。
2 上述的比较符对表字段的类型有要求，一般适用于数值类型。
3 当然我们也可以在where之后结合其它条件再过滤，后面会有案例。
7.2.4 从表中查询部分列需求：
查询雇员表emp中所有的员工编号、员工名称、职位情况。
解决方法：
先找出emp表的工编号、员工名称、职位对应的字段名，再结合SELECT FROM 即可满足需求。
Oracle、Sql server、Mysql:
SELECT empno, ename, job
FROM emp;
执行结果:
empno
ename
job
7369
SMITH
CLERK
7499
ALLEN
SALESMAN
7521
WARD
SALESMAN
7566
JONES
MANAGER
7654
MARTIN
SALESMAN
7698
BLAKE
MANAGER
7782
CLARK
MANAGER
7788
SCOTT
ANALYST
7839
KING
PRESIDENT
7844
TURNER
SALESMAN
7876
ADAMS
CLERK
7900
JAMES
CLERK
7902
FORD
ANALYST
7934
MILLER
CLERK
7.2.5 给字段取个有意义的名字需求：
查询雇员表emp里部门编号是10的员工编号、员工名称、职位情况，并给员工编号起个别名“员工号”，给员工名称起个别名“员工名”，给职位起个名字“职位名”。
解决方法：
先找出emp表的工编号、员工名称、职位对应的字段名，再结合SELECT column as “别名”FROM table的方式解决。
Oracle、Sql server、Mysql:
SELECT empnoas"员工号",enameas"员工名",jobas"职位名"
FROM emp
WHERE deptno =10;
也可以省略掉as写成如下的：
SELECT empnoas"员工号",enameas"员工名",jobas"职位名"
FROM emp
WHERE deptno =10;
执行结果:
员工号
员工名
职位名
7782
CLARK
MANAGER
7839
KING
PRESIDENT
7934
MILLER
CLERK
注: 这里为了演示,将字段名起个别名是中文， 一般情况下不会这么做。因为表名或者字段是用英文定义的，如果名字比较长了或者不好理解，可以起个别名代替。比如有个表叫employee，可以起个别名emp，这个表里有个字段叫salary，可以起个别名叫sal。
一般我们这么写：
SELECT empnoas id,sal salary,comm commission
FROM emp
WHERE deptno=10;
7.2.6 结合where使用别名需求：
查询雇员表emp里工资大于3000的员工编号、员工名称、职位、工资情况，并以工资的别名“salary”进行过滤。
解决方法：
先找出emp表的工编号、员工名称、职位、工资对应的字段名分别为empno、ename、job、sal，结合SELECT column as “别名”FROM table的方式给字段sal起个别名salary，这里需要用个只是，内敛表，即将查询的结果集用“（）”包裹起来起个别名，也可以理解成用个虚拟的表。再在这个内敛表的基础上过滤salary大于3000的记录即可。
Oracle、Sql server、Mysql:
SELECT*FROM
(
SELECT empno,ename,job,salsalary
FROM emp
)X
WHERE salary>3000
执行结果：
empno
ename
job
salary
7839
KING
PRESIDENT
5000.00
7.2.7 拼接列的值需求：
查询雇员表emp里部门编号是10员工信息，以员工名称“’job title is”职位的形式输出。示例：KING’s job title is PRESIDENT
解决方法:
首先定位到emp表和涉及到字段员工名称、职位，再考虑两列的拼接，选择字符串函数或则“+”完成，最后加上where条件过滤部门编号等于10。这里不同的数据库实现方法不同。以下分别来讲。
Mysql：
SELECT concat(ename,"'s job title is:",job)as EnameConn
FROM emp
WHERE deptno =10;
Sql server：
SELECT ename+'''s job title is:'+jobas EnameConn
FROM emp
Oracle:
SELECT ename||'''s job title is:'||jobas EnameConn
FROM emp
shenl
执行结果：
EnameConn
CLARK's job title is:MANAGER
KING's job title is:PRESIDENT
MILLER's job title is:CLERK
7.2.8 查询语句里执行条件判断需求：
查询雇员表emp里的员工名称、工资、工资分类状态。这里的工资分类状态按照以下规则显示：如果工资小于等于2000则显示UNDERPAID(即报酬比较低的)，如果工资大于4000时显示OVERPAID（即报酬较高），其它情况显示NORMAL(正常水平)。
解决方法:
首先定位到emp表和涉及到字段员工名称、工资，再考虑考虑到工资状态是个计算出来的列，所以先梳理工资分类状态这个“列”的逻辑，跟表里的工资字段有关，这里是做 if else判断，注意到纯SQL里没有if else，但有case when，所以我们选取这个关键字。case when 条件1 then value1 case when 条件2 then value2 else value3 end as 别名。
Mysql、SQL server、Oracle:
SELECT ENAME,SAL,
CASEWHEN SAL<=2000THEN'UNDERPAID'
WHEN SAL>2000THEN'OVERPAID'
ELSE'NORMAL'ENDAS STATUS
FROM emp
执行结果：
ENAME
SAL
STATUS
SMITH
800.00
UNDERPAID
ALLEN
1600.00
UNDERPAID
WARD
1250.00
UNDERPAID
JONES
2975.00
OVERPAID
MARTIN
1250.00
UNDERPAID
BLAKE
2850.00
OVERPAID
CLARK
2450.00
OVERPAID
SCOTT
3000.00
OVERPAID
KING
5000.00
OVERPAID
TURNER
1500.00
UNDERPAID
ADAMS
1100.00
UNDERPAID
JAMES
950.00
UNDERPAID
FORD
3000.00
OVERPAID
MILLER
1300.00
UNDERPAID
注：
case when还可以如下方式编写：
select ename,sal,job,
case jobwhen'CLERK'then'小职员'
when'SALESMAN'then'销售员'
when'MANAGER'then'经理'
when'ANALYST'then'分析师'
when'PRESIDENT'then'董事长'
else"其它"
endas jobtitle
from emp
执行结果：
ename
sal
job
jobtitle
SMITH
800.00
CLERK
小职员
ALLEN
1600.00
SALESMAN
销售员
WARD
1250.00
SALESMAN
销售员
JONES
2975.00
MANAGER
经理
MARTIN
1250.00
SALESMAN
销售员
BLAKE
2850.00
MANAGER
经理
CLARK
2450.00
MANAGER
经理
SCOTT
3000.00
ANALYST
分析师
KING
5000.00
PRESIDENT
董事长
TURNER
1500.00
SALESMAN
销售员
ADAMS
1100.00
CLERK
小职员
JAMES
950.00
CLERK
小职员
FORD
3000.00
ANALYST
分析师
MILLER
小职员
这种case 字段A when value1的方式适合字段有可穷举的情况，而case when 字段条件A则比这种更灵活。
7.2.9 返回的字段在某个范围内的数据需求：
查询雇员表emp里部门编号在10和30的部门编号、雇员名称、职位名称。
解决方法:
使用数据库里IN关键字来限制员工表里的deptno。
Mysql、Sql server、Oracle:
SELECT ename,job
FROM emp
WHERE deptnoIN (20,30)
执行结果：
ename
job
deptno
SMITH
CLERK
20
ALLEN
SALESMAN
30
WARD
SALESMAN
30
JONES
MANAGER
20
MARTIN
SALESMAN
30
BLAKE
MANAGER
30
SCOTT
ANALYST
20
TURNER
SALESMAN
30
ADAMS
CLERK
20
JAMES
CLERK
30
FORD
ANALYST
20
7.2.10 通过多条件组合返回数据需求：
查询雇员表emp里部门编号在10和30且职位是经理“MANAGER”的的雇员名称、职位名称。
解决方法:
使用数据库里IN关键字来限制员工表里的deptno。
Mysql、Sql server、Oracle:
SELECT ename,job
FROM emp
WHERE deptno IN (20,30)AND job='MANAGER'
执行结果：
ename
job
JONES
MANAGER
BLAKE
MANAGER
注： 这里字段的逻辑条件里常用的有and、or，注意这里的优先级，如果想改变优先级，可以通过“（）”来改变。
7.2.11 限制返回的行数需求：
查询5条雇员表emp里的雇员信息。
解决方法:
使用数据库里内置的限制行数返回的函数来解决。
Mysql:
SELECT*
FROM emplimit5;
Sql server：
SELECT TOP 5 *
FROM emp;
Oracle:
SELECT *
FROM emp
WHERErownum <=5
执行结果：
empno
ename
job
mgr
hiredate
sal
comm
deptno
7369
SMITH
CLERK
7902
1980/12/17 0:00:00
800.00
20
7499
ALLEN
SALESMAN
7698
1981/2/20 0:00:00
1600.00
300.00
30
7521
WARD
SALESMAN
7698
1981/2/22 0:00:00
1250.00
500.00
30
7566
JONES
MANAGER
7839
1981/4/2 0:00:00
2975.00
20
7654
MARTIN
SALESMAN
7698
1981/9/28 0:00:00
1250.00
1400.00
30
7.2.12 从表中随机返回N条记录
需求：
从雇员表emp里随机查询5条雇员的名称和工资信息。
解决方法:
使用数据库里内置的限制行数函数结合随机函数来解决。
Mysql:
SELECT ename,sal
FROM emp
orderbyrand()limit5;
Sql server：
SELECT TOP 5ename,sal
FROM emp
ORDER BYNEWID()
注： newid是SQL Server里的内置函数，因为newid()返回的是uniqueidentifier类型的唯一值,而且每次生成的值都不一样，所以能达到随机的效果。
Oracle:
SELECT *FROM
(
SELECT ename,sal
FROM emp
ORDERBYDbms_Random.value()
)
WHERErownum <=5
注: dbms_random是一个可以生成随机数值或者字符串的程序包。这个包有initialize()、seed()、terminate()、value()、normal()、random()、string()等几个函数，但返回随机值的函数value()是最常用的。
你可以直接访问这个随机数字的函数，它返回个0到1之间的一个小数。
SELECT Dbms_Random.value()AS RAND_VALUE
FROM DUAL;
执行结果：
RAND_VALUE
0.151493981662762
如果想生成1-100之间的随机数，你可以这么做：
SELECTTRUNC(Dbms_Random.value()*100)ASRAND_VALUE
FROM DUAL;
执行结果：
RAND_VALUE
42
7.2.13 从表中查询空值需求：
从雇员表emp里查询所有奖金为空的员工名称、工资信息和奖金信息。
解决方法:
首先找到该段逻辑的涉及到的表名和字段名，emp表，字段ename，sal，comm,使用IS NULL关键字来来解决，这里的NULL是描述数据的特殊值，即不明确该值是什么，区别于空字符串“''”，空字符串不等于NULL。
Mysql、Sql server、Oracle:
SELECT ename,sal,comm
FROM emp
WHERE commIS NULL
执行结果：
ename
sal
comm
SMITH
800.00
JONES
2975.00
BLAKE
2850.00
CLARK
2450.00
SCOTT
3000.00
KING
5000.00
ADAMS
1100.00
JAMES
950.00
FORD
3000.00
MILLER
1300.00
7.2.14 返回不重复的记录
需求：
从雇员表emp里查询所有不重复的职位信息。
解决方法:
这里用到SQL的DISTINCT关键字，即对重复的记录进行去重。
Mysql、Sql server、Oracle:
SELECTDISTINCT JOB
FROM emp
执行结果：
JOB
CLERK
SALESMAN
MANAGER
ANALYST
PRESIDENT
7.2.15 将空值转成其它值需求：
从雇员表emp里查询所有奖金为空的员工名称、工资信息和奖金信息。这里如果奖金为空则转为0。
解决方法:
首先找到该段逻辑的涉及到的表名和字段名，emp表，字段ename，sal，comm,使用IS NULL关键字来来解决，如果comm为null则转为0 如果不是null则返回本身。
Mysql、Sql Server、Oracle:
SELECT ename,sal,
caseWHEN commISNULLTHEN0
ELSE comm
ENDAS comm
FROM emp
WHERE comm IS NULL
执行结果：
ename
sal
comm
SMITH
800.00
0
JONES
2975.00
0
BLAKE
2850.00
0
CLARK
2450.00
0
SCOTT
3000.00
0
KING
5000.00
0
ADAMS
1100.00
0
JAMES
950.00
0
FORD
3000.00
0
MILLER
1300.00
0
或者用下面的方法，这里每个数据库里的函数名各不同：
Mysql:
SELECT ename,sal,
ROUND(IFNULL(comm,0))as comm
FROM emp
WHERE comm IS NULL
Sql Server:
SELECT ename,sal,
ISNULL(comm,0)AS comm
FROM emp
WHERE comm IS NULL
Oracle:
SELECT ename,sal,
NVL(comm,0)AS comm
FROM emp
WHERE commISNULL
7.2.16 按照某个模式搜索需求：
从雇员表emp里查询部门号是10或者20的员工名称，职位信息，他们要么员工名称里含有“I”要么他们的职位以“ER”结尾。
解决方法:
这里用到SQL的关键字“%”，注意如果字段里以“%匹配关键字%”则会匹配“匹配关键字”在字段里的任何地方，同理如果是“匹配关键字%”则是以“匹配关键字”开头来匹配字段，而“%匹配关键字”则是匹配以“匹配关键字”结尾来匹配字段。
Mysql、Sqlserver、Oracle:
SELECT ename,job
FROM emp
WHERE deptnoIN(10,20)
AND (enameLIKE'%I%'OR jobLIKE'%ER')
执行结果：
ename
job
SMITH
CLERK
JONES
MANAGER
CLARK
MANAGER
KING
PRESIDENT
MILLER
CLERK
7.2.17 按照指定的次序返回查询结果需求：
从雇员表emp里查询所有的员工名称，职位，奖金信息，这里要求奖金不为空的排在前面。
解决方法:
这里用到SQL的关键字 ORDER BY 以及ASC和DESC，order by是指定以某个或者某些字段排序，asc是指代以字母顺序排列而desc则是以字母降序排列。
Mysql、SqlServer:
SELECT ename,job,comm
FROM emp
orderby comm
执行结果：
ename
job
comm
SMITH
CLERK
JONES
MANAGER
BLAKE
MANAGER
CLARK
MANAGER
SCOTT
ANALYST
KING
PRESIDENT
ADAMS
CLERK
JAMES
CLERK
FORD
ANALYST
MILLER
CLERK
TURNER
SALESMAN
0.00
ALLEN
SALESMAN
300.00
WARD
SALESMAN
500.00
MARTIN
SALESMAN
1400.00
Oracle:
SELECT ename,job,comm
FROM emp
order by comm
执行结果：
ENAME
JOB
COMM
TURNER
SALESMAN
0.00
ALLEN
SALESMAN
300.00
WARD
SALESMAN
500.00
MARTIN
SALESMAN
1400.00
SCOTT
ANALYST
KING
PRESIDENT
ADAMS
CLERK
JAMES
CLERK
FORD
ANALYST
MILLER
CLERK
BLAKE
MANAGER
JONES
MANAGER
SMITH
CLERK
CLARK
MANAGER
注：因为Sql Server和Mysql默认以NULL的排在前面，而Oralce则默认以NULL的排在后面。
7.2.18 按照多个字段排序查询结果需求：
从雇员表emp里查询所有的部门编号，员工名称，职位，工资信息，这里要求先以部门编号排序再以工资从高到低排序显示。
解决方法:
这里用到SQL的关键字 ORDER BY 以及ASC和DESC，order by是指定以字段deptno字符排序，以工资降序排列。
Mysql、Sql server、Oracle:
SELECT deptno,ename,job,sal
FROM emp
orderby deptno,commdesc
执行结果：
deptno
ename
job
sal
10
CLARK
MANAGER
2450.00
10
KING
PRESIDENT
5000.00
10
MILLER
CLERK
1300.00
20
SMITH
CLERK
800.00
20
JONES
MANAGER
2975.00
20
SCOTT
ANALYST
3000.00
20
ADAMS
CLERK
1100.00
20
FORD
ANALYST
3000.00
30
MARTIN
SALESMAN
1250.00
30
WARD
SALESMAN
1250.00
30
ALLEN
SALESMAN
1600.00
30
TURNER
SALESMAN
1500.00
30
BLAKE
MANAGER
2850.00
30
JAMES
CLERK
950.00
7.2.19 按照字符串对结果排序
需求：
从雇员表emp里查询所有的部门编号，员工名称，职位，工资信息，职位的后两位信息，这里要求截取job字段里的最后两位来排序。
解决方法:
这里用到SQL的字符串截取函数再结合order by来对结果进行排序。
Mysql:
SELECT deptno,ename,job,sal,substring(job,length(job)-1)AS last2word
FROM emp
orderby last2word
Oracle:
SELECTdeptno,ename,job,sal,substr(job,length(job)-1)ASlast2word
FROM emp
orderbylast2word
Sql serer:
SELECT deptno,ename,job,sal,substring(job,len(job)-1,2)AS last2word
FROM emp
order by last2word
执行结果：
deptno
ename
job
sal
last2word
30
ALLEN
SALESMAN
1600.00
AN
30
WARD
SALESMAN
1250.00
AN
30
MARTIN
SALESMAN
1250.00
AN
30
TURNER
SALESMAN
1500.00
AN
20
JONES
MANAGER
2975.00
ER
30
BLAKE
MANAGER
2850.00
ER
10
CLARK
MANAGER
2450.00
ER
10
KING
PRESIDENT
5000.00
NT
20
SMITH
CLERK
800.00
RK
20
ADAMS
CLERK
1100.00
RK
30
JAMES
CLERK
950.00
RK
10
MILLER
CLERK
1300.00
RK
20
SCOTT
ANALYST
3000.00
ST
20
FORD
ANALYST
3000.00
ST
7.2.20 按照字符串数字组合的排序需求：
假设我们从雇员表emp里创建个视图，这里仅有一个字段，该字段叫data由员工名称和部门号拼接而成，我们想实现一个查询可以按照原来的部门编号逆序排序筛选出数据。
解决方法:
这里没有真正创建视图，因为用户权限问题，而是建了个表叫做tmp_v。字段拼接生成新字段见上面章节。这里要通过数据库里的translate和repacle结合来实现这个功能。
注：SQL Server、Mysql里参照网上实现了translate函数,详细见下。
Oracle：
createtable tmp_v
asSELECT ename||' '|| deptnoasdata
from emp
SELECTdata,replace(translate(data,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','@@@@@@@@@@@@@@@@@@@@@@@@@@'),'@','')as deptnoFROM tmp_v
orderbyreplace(translate(data,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','@@@@@@@@@@@@@@@@@@@@@@@@@@'),'@','')desc
执行结果：
DATA
DEPTNO
BLAKE 30
30
TURNER 30
30
ALLEN 30
30
MARTIN 30
30
WARD 30
30
JAMES 30
30
SCOTT 20
20
JONES 20
20
SMITH 20
20
ADAMS 20
20
FORD 20
20
KING 10
10
MILLER 10
10
注：
1因为我们的ename里的名字都是大写的，所以这里translate函数里的第二个参数都是大写的，如果data里是小写的这里自然是小写。
2 translate函数有三个参数，第一个一般是字段名或则字符串的值，第二个是要匹配的字符组合，第三个是要装换成的字符。
3 replace函数一般有三个参数，第一个一般是字段名或则字符串的值，第二个要替换的字段，第三个是要替换成的值。
SQL Server:
可以在SQLServer里实现个自定义个函数叫translate， 参考至https://blog.sina.com.cn/s/blog_95cfa64601018akj.html具体实现见下：
SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO
CREATE FUNCTION [dbo].[translate](
@string VARCHAR(MAX),
@from_str VARCHAR(MAX),
@to_str VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
-- 返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string。
-- TRANSLATE是 REPLACE 所提供的功能的一个超集。
-- 如果 from_str比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符。
-- to_str不能为空。
-- Oracle将空字符串解释为 NULL，并且如果TRANSLATE 中的任何参数为NULL，那么结果也是 NULL。
IF @string IS NULL OR @from_strIS NULL OR @to_str IS NULL
BEGIN
RETURN NULL;
END;
-- 源长度与目标长度
DECLARE @FromLen INT, @ToLen INT;
SET @FromLen = LEN(@from_str);
SET @ToLen = LEN(@to_str);
-- 准备用于返回的数值.
DECLARE @resultVal VARCHAR(MAX);
SET @resultVal =@string;
-- 用于存储 本次需要替换的字符信息.
DECLARE @thisTimeReplace CHAR(1);
-- 从后向前依次替换.
WHILE @FromLen > 0
BEGIN
-- 取得本次即将要替换的字符.
SET @thisTimeReplace =SUBSTRING(@from_str, @FromLen, 1);
IF CHARINDEX(@thisTimeReplace,@from_str)<@FromLen
BEGIN
-- 假如当前这个要替换的字符，在前面还有，那么这里就不替换了
-- 原因，为了支持
-- SELECT TRANSLATE('2KRW229','1234567890' || '2KRW229', '1234567890')
-- 这样的效果.
-- 向前处理上一个
/*
补充说明：
1 理论上TRANSLATE函数的@from_str参数和@to_str参数的长度要一致,即一一映射。比如@from_str='0123',@to_str='abcd'
2 该步骤旨在找到在@from_str参数里要替换的的字符重复指定了,比如'0123XYZ23'，这里23是重复指定了，对于重复指定的要舍弃所以需要在该步时需要将@FromLen锁定到字符Z的位置即7
*/
SET @FromLen =@FromLen - 1;
CONTINUE;
END
IF @FromLen >@ToLen
BEGIN
--from_str 比 to_str长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符。
SET @resultVal =REPLACE(@resultVal,SUBSTRING(@from_str, @FromLen, 1),'');
END
ELSE
BEGIN
-- from_str中的每个字符替换为to_str中的相应字符以后的string
--SELECT dbo.TRANSLATE('2KRW229', '12345678902KRS229','12345678902')这里用S替换时其实无效
SET @resultVal =REPLACE(@resultVal, @thisTimeReplace,SUBSTRING(@to_str, @FromLen, 1));
END;
-- 处理完当前字符后，向前处理上一个.
SET @FromLen =@FromLen - 1;
END;
-- 依次处理完毕后，返回结果.
RETURN @resultVal;
END
create view tmp_v
AS SELECT ename+' '+cast(deptno as varchar)as data
from emp
SELECT data,replace(dbo.translate(data,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','@@@@@@@@@@@@@@@@@@@@@@@@@@'),'@','')as deptno FROM tmp_v
order byreplace(dbo.translate(data,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','@@@@@@@@@@@@@@@@@@@@@@@@@@'),'@','')desc
执行结果：
DATA
DEPTNO
ALLEN 30
30
WARD 30
30
MARTIN 30
30
BLAKE 30
30
TURNER 30
30
JAMES 30
30
FORD 20
20
SMITH 20
20
ADAMS 20
20
SCOTT 20
20
JONES 20
20
CLARK 10
10
KING 10
10
Mysql:
可以在Mysql里实现个自定义个函数叫translate，具体实现见下：
DROP FUNCTIONIFEXISTS shenl.translate;
CREATE FUNCTION shenl.`translate`(
stringToTranslate VARCHAR(256),
from_str VARCHAR(256),
to_str VARCHAR(256)) RETURNSvarchar(256)CHARSET utf8
BEGIN
DECLARE resultValVARCHAR(256);
DECLARE FromLenINT;
DECLARE ToLenINT;
DECLARE thisTimeReplaceVARCHAR(1);
SET FromLen=LENGTH(from_str);
SET ToLen=LENGTH(to_str);
SET resultVal= stringToTranslate;
IF stringToTranslateISNULLOR from_strISNULLOR to_strISNULLTHEN
RETURNNULL;
END IF;
loop_label: LOOP
#因为是从后往前替换,所以FromLen=0时循环要结束了。
IF FromLen<=0THEN
LEAVE loop_label;
END IF;
#from_str里从后往前每次取1个字符
SET thisTimeReplace=SUBSTRING(from_str, FromLen,1);
#如果在from_str字符串里重复指定了要替换的字符,则舍弃。即如果from_str的值是1234567890fat29,则29是要舍弃掉的。
#即FromLen要锁定到13
IFINSTR(from_str,thisTimeReplace)< FromLenTHEN
SET FromLen= FromLen -1;
ITERATE loop_label;
ELSE
#如果from_str的长度大于ToLen则以ToLen的长度为标准,此时from_str里多提供的字符会被替换为''
IF FromLen> ToLen THEN
SET resultVal=REPLACE(resultVal,SUBSTRING(from_str, FromLen,1),'');
#如果from_str的长度等于ToLen则以ToLen对应的字符替换from_str里多的字符
ELSE
SET resultVal=REPLACE(resultVal, thisTimeReplace,SUBSTRING(to_str, FromLen,1));
END IF;
SET FromLen= FromLen -1;
END IF;
END LOOP;
RETURN resultVal;
END;
create view tmp_v
ASSELECTCONCAT(ename,' ',deptno)as data
from emp
SELECT data,replace(translate(data,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','@@@@@@@@@@@@@@@@@@@@@@@@@@'),'@','')as deptno
FROM tmp_v
orderbyreplace(translate(data,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','@@@@@@@@@@@@@@@@@@@@@@@@@@'),'@','')desc
执行结果：
data
deptno
ALLEN 30
30
MARTIN 30
30
WARD 30
30
BLAKE 30
30
JAMES 30
30
TURNER 30
30
SCOTT 20
20
ADAMS 20
20
SMITH 20
20
JONES 20
20
FORD 20
20
MILLER 10
10
KING 10
10
CLARK 10
10
7.2.21 处理空值的排序需求：
查询雇员表emp里的员工编号、员工名、奖金信息，这里需要将comm字段为空的排在前面。
解决方法:
需要对comm字段进行特殊处理，比如进行转换，然后再排序，而有的数据库可以在排序时指定null的顺序。如oracle里字段排序时可以指定nulls first或则nulls last
Oracle:
SELECTempno,ename,comm
FROM emp
ORDERBY commnullsfirst
执行结果：
EMPNO
ENAME
COMM
7369
SMITH
7782
CLARK
7902
FORD
7900
JAMES
7876
ADAMS
7566
JONES
7698
BLAKE
7934
MILLER
7788
SCOTT
7839
KING
7844
TURNER
0.00
7499
ALLEN
300.00
7521
WARD
500.00
7654
MARTIN
1400.00
Sql Server:
SELECT empno,ename,CASEWHEN comm IS NULL THEN -1ELSE comm END AS comm
FROM emp
ORDER BY comm
Mysql:
SELECT empno,ename,CASEWHEN comm ISNULLTHEN-1ELSE comm ENDAS comm
FROM emp
ORDERBY comm
Sqlserver和Mysql的执行结果见下:
empno
ename
comm
7369
SMITH
-1.00
7566
JONES
-1.00
7698
BLAKE
-1.00
7782
CLARK
-1.00
7788
SCOTT
-1.00
7839
KING
-1.00
7876
ADAMS
-1.00
7900
JAMES
-1.00
7902
FORD
-1.00
7934
MILLER
-1.00
7844
TURNER
0.00
7499
ALLEN
300.00
7521
WARD
500.00
7654
MARTIN
1400.00
7.2.22 根据数据项的值排序需求：
查询雇员表emp里的员工编号、员工名、工作信息、奖金信息，这里需要对工作是“MANAGER”和“SALESMAN”的按照comm降序排序其他员工按照员工编号降序排列。
解决方法:
这里需要在order by后接case when之类的条件判断以调整排序规则。order by可以跟多个字段，排在前面的规则会被优先应用。
Oracle:
SELECTempno,ename,job,comm
FROM emp
ORDERBYCASEWHEN job IN ('SALESMAN','MANAGER')THEN commELSE empnoENDDESC
执行结果：
EMPNO
ENAME
JOB
COMM
7782
CLARK
MANAGER
7698
BLAKE
MANAGER
7566
JONES
MANAGER
7934
MILLER
CLERK
7902
FORD
ANALYST
7900
JAMES
CLERK
7876
ADAMS
CLERK
7839
KING