本章涉及单张表中的查询语句,包含常用的条件查询、范围查询、模糊查询等,跨表查询后续将会介绍。
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;