Oracle查询

---

Oracle查询

一、基本查询语句

• select [distinct] 字段名1, …|* from 表名 [where 条件];

二、在SQLPLUS中设置格式

1、设置字段名

• col 字段名 heading 新字段名

SQL> create table users (id varchar2(10) primary key, username varchar2(20), salary number(7,2));

表已创建。

SQL> desc users
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(10)
 USERNAME                                           VARCHAR2(20)
 SALARY                                             NUMBER(7,2)
 
SQL> col username heading 用户名;

SQL> insert into users values(1,'aaa',50);

已创建 1 行。

SQL> select * from users;

ID         用户名                   SALARY
---------- -------------------- ----------
1          aaa                          50
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2、设置字段格式

• col 字段名 format 格式

SQL> col username format a10;
SQL> select * from users;

ID         用户名         SALARY
---------- ---------- ----------
1          aaa                50

# 给数值型设置字段的格式
SQL> col salary format 9999.9;
SQL> select * from users;

ID         用户名      SALARY
---------- ---------- -------
1          aaa           50.0

// 超过数值长度用 # 代替
SQL> col salary format 9;
SQL> select * from users;

ID         用户名     SALARY
---------- ---------- ------
1          aaa            ##

SQL> col salary format $9999.9;
SQL> select * from users;

ID         用户名       SALARY
---------- ---------- --------
1          aaa           $50.0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

3、清除设置格式

• col 字段名 clear

SQL> col username clear;
SQL> col salary clear;
SQL> select * from users;

ID         USERNAME                 SALARY
---------- -------------------- ----------
1          aaa                          50
1
2
3
4
5
6
7

注意：

• 字符类型只能设置显示的长度
• 数值类型用 “9” 代表一个数字

三、查询表中的所有字段以及指定字段

1、查询所有字段

• select * from 表名;

SQL> col id heading 编号;
SQL> col username heading 用户名;
SQL> col salary heading 工资;
SQL> select * from users;

编号       用户名                     工资
---------- -------------------- ----------
1          aaa                          50
1
2
3
4
5
6
7
8

2、查询指定字段

• select 字段名1, 字段名2, … from 表名;

SQL> select username, salary from users;

用户名                     工资
-------------------- ----------
aaa                          50
1
2
3
4
5

四、给字段设置别名

• select 字段名 as 新字段名, … from 表名;

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

编号       用户名                     工资
---------- -------------------- ----------
1          aaa                          50

// 去掉用户名中重复的名
SQL> select distinct username as 用户名 from users;

用户名
--------------------
aaa
1
2
3
4
5
6
7
8
9
10
11
12

注意：

• AS可以省略，用空格隔开原来的字段名和新字段名即可

五、运算符和表达式

1、简介

• 表达式 = 操作数 + 运算符
• Oracle中的操作数可以有变量、常量和字段
• 算术运算符(+，-，*，/)
• 比较运算符(>，>=，<，<=，=，<>)
• 逻辑运算符(and，or，not)
• 逻辑运算符的优先级：按 not，and，or的顺序依次递减

2、算数运算符

SQL> select * from users;

编号       用户名                     工资
---------- -------------------- ----------
1          aaa                          50
2          bbb                         600

SQL> select id,username,salary+200 from users;

编号       用户名               SALARY+200
---------- -------------------- ----------
1          aaa                         250
2          bbb                         800

SQL> select * from users;

编号       用户名                     工资
---------- -------------------- ----------
1          aaa                          50
2          bbb                         600
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

7、比较运算符

SQL> select username from users where salary>200;

用户名
--------------------
bbb
1
2
3
4
5

8、逻辑运算符

SQL> select username from users where salary>500 and salary<>800;

用户名
--------------------
bbb

SQL> select username from users where salary>500 or salary<>800;

用户名
--------------------
aaa
bbb
1
2
3
4
5
6
7
8
9
10
11
12

六、带条件的查询

1、单一条件查询

SQL> select salary from users where username='aaa';

      工资
----------
        50
        
SQL> select username, salary from users where id = 2;

用户名                     工资
-------------------- ----------
bbb                         600
1
2
3
4
5
6
7
8
9
10
11

2、多条件查询

SQL> select * from users where username='aaa' or salary>2000;

编号       用户名                     工资
---------- -------------------- ----------
1          aaa                          50

// 得到的结果相同
SQL> select * from users where username='aaa' or salary > 800 and salary <= 2000;

编号       用户名                     工资
---------- -------------------- ----------
1          aaa                          50

SQL> select * from users where username='aaa' or (salary > 800 and salary <= 2000);

编号       用户名                     工资
---------- -------------------- ----------
1          aaa                          50

SQL> select * from users where not(username='aaa');

编号       用户名                     工资
---------- -------------------- ----------
2          bbb                         600
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

3、模糊查询

• 使用 like 查询
• 通配符的使用（_,%）
• 一个 _ 只能代表一个字符
• % 可以代表0到多个任意字符

SQL> select * from users where username like 'a%';

编号       用户名                     工资
---------- -------------------- ----------
1          aaa                          50

SQL> select username from users where username like '_a%';

用户名
--------------------
aaa

SQL> select username from users where username like '%a%';

用户名
--------------------
aaa
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• 查询800到2000之间的员工工资 salary>=50 and salary<=2000

QL> select * from users where salary between 50 and 2000;

编号       用户名                     工资
---------- -------------------- ----------
1          aaa                          50
2          bbb                         600

SQL> select * from users where salary not between 50 and 200;

编号       用户名                     工资
---------- -------------------- ----------
2          bbb                         600
1
2
3
4
5
6
7
8
9
10
11
12

• between … and
• in / not in

SQL> select * from users where username in('aaa','bbb');

编号       用户名                     工资
---------- -------------------- ----------
1          aaa                          50
2          bbb                         600

SQL> select * from users where username not in('aaa');

编号       用户名                     工资
---------- -------------------- ----------
2          bbb                         600
1
2
3
4
5
6
7
8
9
10
11
12

4、对查询结果排序

• select … from … [where …] order by 字段名1 desc/asc;
• desc：降序
• asc：升序

// 降序
SQL> select * from users order by id desc;

编号       用户名                     工资
---------- -------------------- ----------
2          bbb                         600
1          aaa                          50

// 升序
SQL> select * from users order by id asc;

编号       用户名                     工资
---------- -------------------- ----------
1          aaa                          50
2          bbb                         600

SQL> select * from users order by id desc,salary asc;

编号       用户名                     工资
---------- -------------------- ----------
2          bbb                         600
1          aaa                          50

SQL> insert into users values(3,'aaa',200);

已创建 1 行。

SQL> select * from users order by id desc,salary asc;

编号       用户名                     工资
---------- -------------------- ----------
3          aaa                         200
2          bbb                         600
1          aaa                          50
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

5、case…when语句的使用

• case 字段名 when 值1 then 结果1, … [else 结果] end

SQL> select username,case username when 'aaa' then '计算机部门' when 'bbb' then '市场部门' else '其它部门' end as 部门 from users;

用户名               部门
-------------------- ----------
aaa                  计算机部门
bbb                  市场部门
aaa                  计算机部门
1
2
3
4
5
6
7

• case when 字段名=值1 then 结果1, … [else 结果] end

SQL> select username,case when username='aaa' then '计算机部门' when username='bbb' then '
  2  市场部门' else '其它部门' end as 部门 from users;

用户名               部门
-------------------- ----------
aaa                  计算机部门
bbb
                     市场部门

aaa                  计算机部门

1
2
3
4
5
6
7
8
9
10
11

SQL> select username,case when salary<800 then '工资低' when salary>5000 then '工资高' end as 工资水平 from users;

用户名               工资水
-------------------- ------
aaa                  工资低
bbb                  工资低
aaa                  工资低
1
2
3
4
5
6
7

6、decode函数的使用

• decode(字段名, 值1, 结果1, …, defaultvalue)
• decode函数于case…when语句效果一样

SQL> select username,decode(username,'aaa','计算机部门','bbb','市场部门')as 部门 from users;

用户名               部门
-------------------- ----------
aaa                  计算机部门
bbb                  市场部门
aaa                  计算机部门
1
2
3
4
5
6
7