Oracle查询大集锦

Oracle查询大集锦

1、 Select 语句的基本结构

Select <字段名或表达式列表> // * 号代表全部

From <想要查询的表名> //可以是一张表或多张表或视图

Where <查询的条件> //排除不满足条件的行返回需要的记录

Group By <分组的字段名或表达式> //将查询的结果按指定的一个字段或多个字段的值进行分组统计，分组字段或表达式的值相等的被分为同一组

Having <筛选条件> //用于对Group By 字句分组的结果进一步限定搜索条件

Order By <排序的字段名或表达式> //将结果按照指定字段进行排序（升序降序）默认是升序

2、选择数据表所有的列

Select * From Tablename;

3、选择数据表指定的列

Select 商品编号,商品名称,商品价格,库存数量 From 商品信息表;

4、查询时更改列标题

Select 商品编码 [As] good_code, 商品名称 [As] good_name, 商品价格 [As] good_price, 库存数量 [As] stock_number

From 商品信息表;

输出结果的时候列名被 AS 后面的替换了

提：[AS]可以省略。

5、查询时使用计算列

Select 商品价格*库存数量 As 金额

From 商品信息表;

6、使用 dual 表查询系统变量或表达式值

提：dual 表是为了给我们在数据库查询中填充 From 字句使用的

任务（1）查询 dual 表的记录数据

Select * From dual;

任务（2）通过系统变量 sysdate 显示系统当前日期

Select sysdate From dual;

任务（3）通过系统变量 sysdate 显示当前年份

Select Extract(Year From sysdate) 当前年份 From Dual;

任务（4）查询序列 user_seq 的当前值

Select erID_seq.currval From dual;

任务（5）查询当前用户的名字

Select user From dual;

7、 Distinct选择不重复的行（如果表中有多个Null的记录它们将作为重复值处理）

Select Distinct id From Tablename;

8、 Rownum（获取数据表中前面若干行）

Select id,name From Tablename Where rownum<8; //查询表Tablename中的前7行

9、 Where子句条件查询

任务（1）从“商品信息表”中查询类型编号为“0103”的商品信息

Select * From 商品信息表 Where 类型编号=’0103’;

任务（2）从“商品信息表”中查询生产日期在2012年12月16日之前的商品信息

Select * From 商品信息表 Where 生产日期

任务（3）从“商品信息表”中查询生产日期在2011年以及2011之后的商品信息

Select * From 商品信息表 Where Extract(Year From 生产日期)>=2011;

Select * From 商品信息表 Where to_char(生产日期,’YYY’)>=2011;

任务（4）从“商品信息表”中查询价格低于800元，库存数量高于5的商品信息

Select * From 商品信息表 Where 商品价格<800 And 库存数量>5;

任务（5）从“商品信息表”中查询生产日期在‘2012-06-20’和‘2012-12-20’的商品信息

Select * From 商品信息表 Where 生产日期 Between to_date(‘2012-06-20’,’YYY-MM-DD’)And to_date(‘2012-12-20’,’YYY-MM-DD’);

任务（6）从“商品信息表”中查询商品价格分别为‘600元’、‘1000元’、‘1500元’的商品信息

Select * from 商品信息表 Where 商品价格 In(600,1000,1500);

任务（7）从“商品信息表”中查询商品名称‘三星’开头的商品信息

Select * From 商品信息表 Where 商品名称 Like ’三星%’;

任务（8）从“商品信息表”中查询商品描述为空的商品信息

Select * From 商品信息表 Where 商品描述 Is Null;

任务（9）:从“商品信息表”中查询商品名称中含有“三”的商品信息。

Select * From 商品信息表 Where 商品名称 Like ’%三%’;

任务（10）：从“商品信息表”中查询商品名称中最后一个字是“星”的商品信息

Select * From 商品信息表 Where 商品名称 Like ‘%星’;

10、使用聚合函数实现查询

任务（1）：从“商品信息表”中查询商品价格在“1000”元至“1500”元之间的商种数

Select Count(*)As 商品种数 From 商品信息表 Where 商品价格 Between 1000 And 1500;

任务（2）：从“商品信息表”中查询商品的总库存数量。

Select Sum(库存数量)As 总库存数量 From 商品信息表;

任务（3）：从“商品信息表”中查询不重复的商品类型

Select Count(Distinct(类型编码))As 商品类型数量 From 商品信息表;

任务（4）：从“商品信息表”中查询商品的最高价、最低价和平均价格。

Select Max(商品价格)As 商品最高价,Min(商品价格)As 商品最低价,Round(Avg(商品价格))As 商品平均价格 From 商品信息表;

11、使用Order By字句对查询结果排序

任务（1）：从方案“commerce”的“商品信息表”中查询商品价格在2000元以上的商品数据，要求按商品价格的升序输出

Select * From 商品信息表 Where 商品价格>2000 Order By 商品价格;

任务（2）：从方案“commerce”的“商品信息表”中查询商品价格高于1500的商品数据，要求按商品名称的降序输出

Select * From 商品信息表 Where 商品价格>1500 Order By 商品名称 DESC;

任务（3）：从方案“commerce”的“商品信息表”中查询所有的商品数据，要求按库存数量的升序输出，库存数量相同的按商品价格的降序输出。

Select * From 商品信息表 Order By 库存数量 ASC,商品价格 DESC;

12、查询时使用Group By字句进行分组

任务：从方案“commerce”的“商品信息表”中统计各类商品的平均价格和商品种数。

Select 类型编号,Round(Avg(商品价格),2)As 平均价格,Count(*)As 商品种数 From 商品信息表 Group By 类型编号;

13、查询时使用Having字句进行分组统计

任务：从方案“commerce”的“商品信息表”中查询价格高于1000元并且库存数量大于15的商品数量，同时统计平均价格在2000元以上的各类商品的总金额，并按总金额的降序排列。（降序是 Desc 升序是 Asc）

Select 类型编号,Round(Avg(商品价格),2)As 平均价格,Sum(商品价格*库存数量)As 金额 From 商品信息表 Where 商品价格>1000 And 库存数量>15 Group By 类型编号 Having Avg(商品价格)>2000 Order By 金额 Desc;

14、创建两个数据表之间的连接查询

任务：从方案“commerce”的“商品信息表”和“商品类型表”查询商品的详细信息，查询结果包含商品编码、商品名称、类型名称、商品价格、库存数量等字段。

Select 商品编码,商品名称,类型名称,商品价格,库存数量

From 商品信息表,商品类型表

Where 商品信息表.类型编号=商品类型表.类型编号;

Select 商品信息表.商品编码,商品信息表.商品名称,商品类型表.类型名称,商品信息表.商品价格,商品信息表.库存数量

From 商品信息表,商品类型表

Where 商品信息表.类型编号=商品类型表.类型编号;

Select g.商品编码,g.商品名称,t.类型名称,g.商品价格,g.库存数量

From 商品信息表 g,商品类型表 t

Where g.类型编号=t.类型编号;

15、创建多表连接查询

任务（1）：从“订单主表”、“订单明细表”、“商品信息表”、“商品类型表”4个数据表中查询所有客户订购商品的详细信息，要求查询结果包括订单编号、购买数量、商品名称、类型名称等字段。

Select m.订单编号,d.购买数量,g.商品名称,t.类型名称

From 订单主表 m,订单明细表 d,商品信息表 g,商品类型表 t

Where m.订单编号=d.订单编号

And d.商品编码=g.商品编码

And g.类型编号=t.类型编号;

任务（2）：从“订单主表”、“订单明细表”、“商品信息表”、“客户信息表”4个数据表查询客户“江北”所订购商品的详细信息，要求查询结果包括订单编号、客户名称、商品名称、购买数量等字段。

Select m.订单编号,c.客户名称,g.商品名称,d.购买数量

From 订单主表 m,订单明细表 d,商品信息表 g,客户信息表 c

Where m.订单编号=d.订单编号

And d.商品编码=g.商品编码

And m.客户=c.客户编号