oracle两表连接查询去重,Oracle 查询(SELECT)语句（一）

?简介

本文介绍 Oracle中查询(SELECT)语句的使用，在 SQL中 SELECT语句是相对内容较多的，也是相对比较复杂一点的，所以这里拿出来单独学习。

首先，我们先来理一下思路，我们知道查询通常包含以下内容：

?查询指定的表和列

?根据指定的条件查询，即 WHERE条件

?查询数据去重，即 DISTINCT子句

?查询数据聚合，即 COUNT()、MAX()、MIN()等

?按条件输出，即 CASE WHEN THEN子句

?排序(ORDER BY)

?分组(GROUP BY)与分组过滤(HAVING)

?多表连接(INNER JOIN、LEFT JOIN等)

?子查询(SELECT子查询、WHERE子查询等)

?其他

好了，既然知道了有这些查询功能，下面我们就一一突破，准备了以下内容：

1.准备数据

2.SELECT语法

3.基本用法

4.WHERE条件

5.聚合查询

6.CASE WHEN THEN子句

7.排序

8.分组与分组过滤

1.准备数据

1)创建表结构

CREATETABLEJNUser (

UserIdNUMBER(10)NOTNULL,

NameVARCHAR2(8)NOTNULL,

SexNUMBER(3)NOTNULL,--性别(0女，1男，2未知)

AgeNUMBER(5)NOTNULL,

BirthdayDATENOTNULL,

CityVARCHAR2(6)NOTNULL,

IdNumberCHAR(18)NOTNULL,

SalaryFLOAT,

RemarksVARCHAR2(4000)NOTNULL,

PRIMARYKEY(UserId),

CONSTRAINTUQ_JNUser_IdNumberUNIQUE(IdNumber),

CONSTRAINTCK_JNUser_SexCHECK(Sex >=0ANDSex <=2)

);

CREATETABLEJNOrder (

OrderIdNUMBER(10)NOTNULL,

UserIdNUMBER(10)NOTNULL,

OrderNoVARCHAR2(16)NOTNULL,

TotalAmountFLOATNOTNULL,

OrderDateDATENOTNULL,

RemarksVARCHAR2(4000) ,

PRIMARYKEY(OrderId),

CONSTRAINTUQ_JNOrder_OrderNoUNIQUE(OrderNo)

);

2)插入数据

INSERTALL

--用户数据

INTOJNUserVALUES(1,‘孙器璇‘,2,17, to_date(‘2002/11/18‘,‘yyyy/mm/dd‘),‘北京‘,‘17730094858437637X‘,10400,‘hello‘)

INTOJNUserVALUES(2,‘周器璇‘,0,22, to_date(‘1966/05/12‘,‘yyyy/mm/dd‘),‘武汉‘,‘534668342907162757‘,15000,‘用户2‘)

INTOJNUserVALUES(3,‘张二娃‘,1,24, to_date(‘1967/01/24‘,‘yyyy/mm/dd‘),‘杭州‘,‘36935324879542561X‘,7600,‘大家好，我是张二娃‘)

INTOJNUserVALUES(4,‘陈悴‘,2,22, to_date(‘1984/10/11‘,‘yyyy/mm/dd‘),‘武汉‘,‘585823614699788016‘,7100,‘你好，贵姓？‘)

INTOJNUserVALUES(5,‘钱悴‘,2,58, to_date(‘1961/10/21‘,‘yyyy/mm/dd‘),‘深圳‘,‘449489822531507067‘,15320,‘hello张二娃‘)

INTOJNUserVALUES(6,‘张无忌‘,1,61, to_date(‘1988/02/24‘,‘yyyy/mm/dd‘),‘杭州‘,‘385929993868497572‘,17800,‘哈啰，我是张无忌‘)

INTOJNUserVALUES(7,‘张大彪‘,1,24, to_date(‘1995/05/16‘,‘yyyy/mm/dd‘),‘上海‘,‘466484458398445233‘,1200,‘我说了，我是彪爷‘)

INTOJNUserVALUES(8,‘冯小二‘,1,29, to_date(‘1976/09/28‘,‘yyyy/mm/dd‘),‘广州‘,‘40016865591929392X‘,5700,‘客观，吃点莫子？‘)

INTOJNUserVALUES(9,‘DBA‘,2,32, to_date(‘1997/04/28‘,‘yyyy/mm/dd‘),‘上海‘,‘488500420672587475‘,16000,‘小崽子们，你们好，我是DBA‘)

INTOJNUserVALUES(10,‘陈双‘,0,18, to_date(‘1995/02/07‘,‘yyyy/mm/dd‘),‘武汉‘,‘942310204386191671‘,10343,‘我是双儿‘)

--订单数据

INTOJNOrderVALUES(1,2,‘58977924501badf7‘,2620, to_date(‘2019/06/10‘,‘yyyy/mm/dd‘),‘又是双十一，我卖完再剁手啦‘)

INTOJNOrderVALUES(2,3,‘316626433f743978‘,1115, to_date(‘2019/08/19‘,‘yyyy/mm/dd‘),‘其他没什么，就是想买‘)

INTOJNOrderVALUES(3,4,‘8698789361c78946‘,1734, to_date(‘2019/03/16‘,‘yyyy/mm/dd‘),‘‘)

INTOJNOrderVALUES(4,5,‘58716471589e3df2‘,897, to_date(‘2019/11/13‘,‘yyyy/mm/dd‘),‘ ‘)

INTOJNOrderVALUES(5,8,‘5583165337e0ee25‘,2097, to_date(‘2019/01/21‘,‘yyyy/mm/dd‘),‘我已下单，快点发货，老板‘)

INTOJNOrderVALUES(6,8,‘395799340826d6f2‘,304, to_date(‘2019/02/27‘,‘yyyy/mm/dd‘),‘‘)

INTOJNOrderVALUES(7,3,‘887799782996b3f1‘,1246, to_date(‘2019/09/20‘,‘yyyy/mm/dd‘),‘老板，给我来个好看的包装盒，我要送老丈人‘)

INTOJNOrderVALUES(8,8,‘39482046468266d6‘,306, to_date(‘2019/02/28‘,‘yyyy/mm/dd‘),NULL)

SELECT*FROMDUAL;

COMMIT;

2.SELECT语法

SELECT []… FROM

[WHERE ]

[GROUP BY [HAVING ]

[ORDER BY [ASC | DESC]]

说明：如果输出所有列，可以指定为"*"。这里简单阐述下(SELECT *与 SELECT所有列)的一些区别： 比较项 SELECT * SELECT指明所有列 结论

1.执行效率 需要检索表中的所有列名 不需要检索列名 后者效率略高

2.后续新增字段 原程序会直接将新字段查出 需要重新更改程序中的 SQL语句 视业务情况而定

3.难易程度 比较便捷 比较麻烦 前者有优势

4.字段较多时(比如一两百多个) 减少网络流量 增加网络流量 前者有优势(可忽略的)

提示：如有其他看法，欢迎讨论。

3.基本语法

1)查询所有列

SELECT*FROMJNUser;

SELECT*FROMJNOrder;

2)查询指定列

SELECTUserId,NameFROMJNUser;

3)定义别名

SELECTUserId用户Id,Name用户名, AgeAS"性 别", U.CityFROMJNUser U;

说明：

1.表别名不能使用AS关键字；定义别名后可使用或不使用；

2.列别名可使用或不使用AS关键字；别名包含特殊字符时，采用""双引号括起来。

4)算数运算符(+、-、*、/)

SELECTSalary, Salary +200, Salary -200, Salary *12, Salary /30FROMJNUser;

5)比较运算符(>、>=、)

SELECT*FROMJNUserWHERECity <>‘上海‘;--或者使用!=

6)列连接

SELECTUserId ||‘ - ‘||Name||‘ - ‘|| CityASUserDescFROMJNUser;

7)构建表达式

SELECT(‘SELECT * FROM ‘|| TABLE_NAME ||‘;‘)ASSELFROMALL_TABLESWHEREOWNER =‘USER01‘;

8)数据去重

SELECTDISTINCTAge, CityFROMJNUser;

说明：去除每列相同的数据行，只返回一行

4.WHERE条件

1)复合条件

SELECT*FROMJNUserWHERESalary >10000;--查出工资大于10000的用户

SELECT*FROMJNUserWHERESex =0ANDSalary >10000;--查出性别为女性，且工资大于10000的用户

SELECT*FROMJNUserWHERE(Sex =0ORSex =2)ANDSalary >10000;--查出性别为女性或者未知，且工资大于10000的用户

2)IN子句

IN子句表示取出值包含在给出的值范围内的记录。

SELECT*FROMJNUserWHERECityIN(‘武汉‘,‘上海‘,‘北京‘)ORDERBYCity;--查出所在城市在武汉、上海、北京的用户，并按城市升序排序

3)BETWEEN子句

BETWEEN子句表示取出值在起始值与结束值之间的记录，且包含起始值与结束值(它是包头包围的)。

SELECT*FROMJNUserWHERESalaryBETWEEN10400AND16000;--查出工资在10400(包含)至16000(包含)之间的用户

4)LIKE模糊匹配

SELECT*FROMJNUserWHERERemarksLIKE‘h%‘;--查出备注以h开头的用户(注意：匹配时区分大小写)

SELECT*FROMJNUserWHERERemarksLIKE‘%好%‘;--查出备注中包含“好”字的用户

SELECT*FROMJNUserWHERENameLIKE‘张__‘;--查出姓张的，并且名为2个字的用户

注意：在实际应用场景中不到万不得已，尽量避免使用LIKE模糊查询，因为使用模糊的字段就不能使用索引了，影响查询效率。

5)使用&变量

SELECT*FROMJNUserWHERECity =‘&City‘ANDSalary > &Salary;

说明：该查询方式只适合在 PL/SQL Developer中使用，提供一个条件参数占位符，用于在窗口中输入参数值。

5.聚合查询

聚合函数是 Oracle提供内置函数，用于计算某一列的聚合计算(如：数量、平均值等)

SELECTCOUNT(*)ASCOUNTFROMJNUserWHERECity =‘上海‘;--统计有多少上海用户，*可以改为数字1

SELECTMAX(SALARY)ASMAXFROMJNUserWHERECity =‘上海‘;--查询上海用户最高薪资

SELECTMIN(SALARY)ASMINFROMJNUserWHERECity =‘上海‘;--查询上海用户最低薪资

SELECTSUM(SALARY)ASSUMFROMJNUserWHERECity =‘上海‘;--查询上海用户总薪资

SELECTAVG(SALARY)ASAVGFROMJNUserWHERECity =‘上海‘;--查询上海用户平均薪资

6.CASE WHEN THEN子句

CASE WHEN THEN定义在 SELECT与 FROM之间，用于判断当条件符合 WHEN时，就返回 THEN对应的值，否则返回 ELSE中的值。

1)第一种写法

SELECTCity, (CASECityWHEN‘上海‘THEN‘SH‘WHEN‘武汉‘THEN‘WH‘ELSE‘WZ‘END)ASJianChengFROMJNUser;

2)第二种写法

SELECTCity, (CASEWHENCity =‘上海‘THEN‘SH‘WHENCity =‘武汉‘THEN‘WH‘ELSE‘WZ‘END)ASJianChengFROMJNUser;

7.排序

排序采用 ORDER BY子句，包含升序(ASC)和降序(DESC)排序方式。

SELECT*FROMJNUserORDERBYSalary;--ASC升序排序(默认排序方式)

SELECT*FROMJNUserORDERBYSalaryDESC;--DESC降序排序

SELECT*FROMJNUserORDERBYSexASC, SalaryDESC;--首先按性别升序排序，再按薪资降序排序

SELECT*FROMJNUserWHERESex =1ORDERBY4ASC;--查出男性用户所有用户，并按照第4列(AGE)升序排序

8.分组与分组过滤

分组采用 GROUP BY子句，注意：包含分组的 SELECT输出列中只能包含，分组的列和聚合计算的列。GRUOUP BY分组后还可以跟条件子句，即 HAVING，通常用于聚合计算过滤。

1)查出工资大于8000的用户，每个性别各占的数量，并按性别倒序排序

SELECTSex,COUNT(1)ASCountFROMJNUser

WHERESalary >8000

GROUPBYSex

ORDERBYSexDESC;

2)查出工资大于8000的用户，每个性别的平均工资大于13000，并按性别倒序排序

SELECTSex,AVG(Salary)ASAvgFROMJNUser

WHERESalary >8000

GROUPBYSex

HAVINGAVG(Salary) >13000

ORDERBYSexDESC;

原文：https://www.cnblogs.com/abeam/p/12005851.html