case when 子查询_Oracle数据库-单表查询

本章涉及单张表中的查询语句，包含常用的条件查询、范围查询、模糊查询等，跨表查询后续将会介绍。

1.基本查询语句

格式：SELECT[DISTINCT] column_name,…|*

FROM table_name

[WHERE conditions]

备注：添加DISTICNT可以在查询时剔除重复的字段

先创建一张名为userinfo_s1的表

SQL>create table userinfo_s1

(id number(10,0) primary key,

Username varchar2(30),

Salary number(8,2));

Insert into userinfo_1 Values(1,'robert',888.00);

Insert into userinfo_1 Values(2,'jack',777.7);

Insert into userinfo_1 Values(3,'rose',555);

在SQL*PLUS中设置格式

格式：COLUMN column_name HEADING new_name

SQL>col username heading 用户名;

格式：COLUMN column_name FORMAT dataformat

SQL>col username format a5;

备注：设置字符型的数据长度，以a起头后面跟上要保留的长度。例如，上面这条语句使得username改成5位，也就是robert 变成rober。

SQL>col salary format 9999.9;

备注：设置数据型格式，小数点之后的9表示保留的小数位数。例如，上面这条语句使得数据888.00变成888.0。假如设置的整数部分长度比表中某数据的整数部分位数要少，会使得相应数据发生错误。下面这条语句，会使得888.00变成######。

SQL> col salary format 99.9;

也可以在数据格式设置中添加符号

SQL>col salary format $9999.99;

要清除设置的数据格式，可用CLEAR

SQL> COLUMN column_name clear;

2.查询表中的所有字段及指定字段

查询所有字段

格式：select *from table_name;

先设置格式再对表进行查询

SQL>col id heading 编号;

SQL>col username heading 用户名;

SQL>col salary heading 工资;

SQL>select *from users;

3.给字段设置别名

注意，这里给字段设置别名并不会直接更改原表中的字段名称，仅仅是方便我们对数据库进行操作。

格式：SELECT column_name AS new_name,… |AS可省略，注意留出空格

FROM table_name

SQL>select id as 编号, username as 用户名, salary as 工资

From userinfo_s1;

4.在SELECT语句中使用运算符

表达式由操作数和运算符组成。oracle数据库中操作数可以说变量、常量和字段。

运算符主要有三类，算术运算符(+,-,*,/),比较运算符(>,>=,<,<=,=,<>)和逻辑运算符(and or not)，其中<>表示的是不等于。注意这些运算符是有优先级的，可类比C++语言中运算符优先级。

展示之前创建的表userinfo_s1

->ID USERNAME SALARY

----------- ---------------- -------------

1 robert 888.00

2 jack 777.7

3 rose 555

使用算术运算符

SQL>select id,username,salary+100 from userinfo_s1;

注意此时仅仅是在查询结果中显示salary每个数据加100，但是原表中的数据不改变。

使用比较运算符

SQL>select username from userinfo_s1

Where salary>600;

使用逻辑运算符

SQL>select username from userinfo_s1

Where salary>600 and salary<800;

5.带条件的查询

单一条件查询

SQL>select username,salary from userinfo_s1 where id=1;

多条件查询

如查询userinfo_s1中姓名是jack或者工资高于600低于700的员工信息

SQL>select* from userinfo_s1

Where username='jack' or (salary>600 and salary<700);

为了使逻辑更清晰，可以添加括号。

SQL>select*from userinfo_s1

Where not(username='robert');

6.模糊查询

与精确匹配查询相对应，模糊查询相当于模式匹配，查询包含某个模式的结果。

模糊查询主要用到两个通配符：下划线_和百分号%，其中一个下划线代表一个字符而百分号可以代表任意长度字符。

例如，要从之前创建的userinfo_s1表中查询用户名以r开头的用户信息

SQL>select *from userinfo_s1

Where username like 'r%';

要查询用户名第二个字符是o的用户信息

SQL>select *from userinfo_s1

Where username like '_o%';

要查询用户名中含有e的用户信息

SQL>select* from userinfo_s1

Where username like '%e%';

7.范围查询

BETWEEN…AND语句

SQL> select* from userinf_s1

Where salary between 600 and 800;

备注：不同数据库中对BETWEEN… AND端点值是否包含在内有不同的规定。在MySQL中是左闭右开，即包含左端点不包含右端点，而在Oracle11g中，左右端点都包含在内。

IN/NOT IN语句

假如要查询用户名是jack或者rose的用户信息

SQL> select* from userinf_s1

Where username in ('jack','rose');

8.对查询结果进行排序

格式：SELECT…FROM…[WHERE]

ORDER BY column1 DESC/ASC,…

备注：DESC表示按照字段降序排列，ASC表示升序排列。

若在userinfo_s1表中按照ID字段降序排列，查询用户信息

SQL>select *from userinfo_s1

Order by id desc;

可使用多个排序，按照先后原则，在前一排序中字段相等可继续向后执行排序。为此，可向userinfo_s1表中再添加一行数据进行演示。

SQL>insert into userinfo_s1

Values(4,'rose',666);

SQL>select *from userinfo_s1

Order by username desc, salary asc;

9.Case…when语句

格式：CASE column_name

WHEN value1 THEN result1,…

[ELSE result]END

SQL>select username,

case username

when 'robert' then '前端工程师'

When 'jack' then'后端工程师’

Else '其他' end as '职位'

From userinfo_s1

->USERNAME 部门

------------ ---------

Robert 前端工程师

jack 后端工程师

rose 其他

rose 其他

格式：CASE

WHEN column_name=value1

THEN result1,…[ELSE result] END

SQL>select username,Case

when username='robert' then '前端工程师'

when username='jack' then '后端工程师'

else '其他' end as'职位'

from userinfo_s1;

后一种格式比较灵活，可以使用表达式，比如

SQL>select username,case

When salary<800 then '一般'

When salary>=800 then '高'

End as '工资水平'

From userinfo_s1;

备注：若不加ELSE，则不满足CASE条件的数据对应为空值。

10.Decode函数

格式：decode(column_name,value1,result1,…,defaultvalue)

SQL>select username,decode(username, 'robert', '前端工程师', 'jack', '后端工程师', ‘其他') as '职位' from userinfo_s1;