niceo
- 浏览: 109783 次
- 性别:
- 来自: 大连
-
文章分类
最新评论
-
zjqzfyc：
项目开发文档——测试计划 -
kyng：
不能动 吖。。。 这程序 实现的正方形
一个Java3D 应用程序 -
kyng：
可以玩 。。试试了 下
swing做贪吃蛇游戏 -
tl_nicole：
小弟最近正在学习JAVA3D，求楼主源码学习~~不甚感激
Canvas3D -
peijunlin2008：
DECLARE H_SETTLE SCROLL CURSO ...
游标的实例
SQL高级查询
找出某一列里最大或最小的前几个，或是大于或小于某一个值（最大值或平均值）的数据
1：找出公司里收入最高的前三名员工：
SQL> select rownum, last_name, salary
2 from (select last_name, salary
3 from s_emp
4 order by salary desc)
5 where rownum<=3;
ROWNUM LAST_NAME SALARY
---------- ------------------------- ----------
1 Velasquez 4750
2 Ropeburn 2945
3 Nguyen 2897.5
注意：请大家分析一下一下语句为什么不对：
SQL> select rownum, last_name, salary
2 from s_emp
3 where rownum<=3
4 order by salary desc;
ROWNUM LAST_NAME SALARY
---------- ------------------------- ----------
1 Velasquez 4750
3 Nagayama 2660
2 Ngao 2000
2： 找出表中的某一行或某几行的数据：
（1）：找出表中第三行数据：
用以下方法是不行的，因为rownum后面至可以用<或<=号,不可以用=,>号和其它的比较符号。
SQL> select * from s_emp
2 where rownum=3;
no rows selected
SQL> select * from s_emp
2 where rownum between 3 and 5;
no rows selected
正确的方法如下：
SQL> l
1 select last_name, salary
2 from (select rownum a, b.*
3 from s_emp b)
4* where a=3
SQL> /
LAST_NAME SALARY
------------------------- ----------
Nagayama 2660
（2）：找出第三行到第五行之间的数据：
SQL> l
1 select last_name, salary
2 from (select rownum a, b.*
3 from s_emp b)
4* where a between 3 and 5
SQL> /
LAST_NAME SALARY
------------------------- ----------
Nagayama 2660
Quick-To-See 2755
Ropeburn 2945
3：找出那些工资高于他们所在部门的平均工资的员工。
（1）：第一种方法：
SQL> select last_name, dept_id, salary
2 from s_emp a
3 where salary>(select avg(salary)
4 from s_emp
5 where dept_id=a.dept_id);
LAST_NAME DEPT_ID SALARY
------------------------- ---------- ----------
Velasquez 50 4750
Urguhart 41 2280
Menchu 42 2375
Biri 43 2090
Catchpole 44 2470
Havel 45 2483.3
Nguyen 34 2897.5
Maduro 41 2660
Nozaki 42 2280
Schwartz 45 2090
10 rows selected.
（2）：第二种方法：
SQL> l
1 select a.last_name, a.salary, a.dept_id, b.avgsal
2 from s_emp a, (select dept_id, avg(salary) avgsal
3 from s_emp
4 group by dept_id) b
5 where a.dept_id=b.dept_id
6* and a.salary>b.avgsal
SQL> /
LAST_NAME SALARY DEPT_ID AVGSAL
------------------------- ---------- ---------- ----------
Velasquez 4750 50 3847.5
Urguhart 2280 41 2181.5
Menchu 2375 42 2055.16667
Biri 2090 43 1710
Catchpole 2470 44 1995
Havel 2483.3 45 2069.1
Nguyen 2897.5 34 2204
Maduro 2660 41 2181.5
Nozaki 2280 42 2055.16667
Schwartz 2090 45 2069.1
10 rows selected.
4：找出那些工资高于他们所在部门的manager的工资的员工。
SQL> l
1 select id, last_name, salary, manager_id
2 from s_emp a
3 where salary>(select salary
4 from s_emp
5* where id=a.manager_id)
SQL> /
ID LAST_NAME SALARY MANAGER_ID
---------- ------------------------- ---------- ----------
6 Urguhart 2280 2
7 Menchu 2375 2
8 Biri 2090 2
9 Catchpole 2470 2
10 Havel 2483.3 2
12 Giljum 2831 3
13 Sedeghi 2878.5 3
14 Nguyen 2897.5 3
15 Dumas 2755 3
16 Maduro 2660 6
10 rows selected.
第一题：有两个表分别如下：
表A(varchar(32) NAME,int GRADE)
数据：
ZHANGSHAN 80
LISI 60
WANGWU 84
表B(varchar(32) NAME,int AGE)
数据：
ZHANGSHAN 26
LISI 24
WANGWU 26
WUTIAN 26
（1）写SQL语句得到如下查询结果：
NAME GRADE AGE
ZHANGSHAN 80 26
LISI 60 24
WANGWU 84 26
WUTIAN NULL 26
疑问：这里的没有成绩的那个人的记录怎么得到呢？
（2）写SQl语句根据名字（NAME）相同按年龄（AGE）分组得到不同年龄的人的平均成绩，并写出结果。
疑问：按照名字相同，WUTIAN这个人没有成绩该不该把他统计在内呢？
（3）有一个数据库表dept中有如下数据：
id_no id_name
1000 S1
1001 S2
1002 S3
1003 S4
1000 S5
1000 S6
1001 S7
表中有id_no重复，如id_no为1000的有3条记录，如id_no为1001的有2条记录，
现在要按照id_no给表建索引，需要删除id_no重复了的那些记录，但不能删掉所有拥有该id_no的记录，必需保留一条拥有该id_no的记录(如id_no为1000的只剩下一条记录)
(1)请写出SQl语句（或SQL语句组），查询所有id_no重复的记录。
(2)请写出SQl语句实现题目要求的结果。
1:
SQL> create table a
2 (name varchar2(32),
3 grade int);
Table created.
SQL> insert into a_t
2 values('&a',&b);
Enter value for a: zhangshan
Enter value for b: 80
old 2: values('&a',&b)
new 2: values('zhangshan',80)
1 row created.
SQL> /
Enter value for a: lisi
Enter value for b: 60
old 2: values('&a',&b)
new 2: values('lisi',60)
1 row created.
SQL> /
Enter value for a: wangwu
Enter value for b: 84
old 2: values('&a',&b)
new 2: values('wangwu',84)
1 row created.
SQL> commit;
Commit complete.
SQL> create table b_t
2 (name varchar2(32),
3 age int);
Table created.
SQL> insert into b_t
2 values('&a',&b);
Enter value for a: zhangshan
Enter value for b: 26
old 2: values('&a',&b)
new 2: values('zhangshan',26)
1 row created.
SQL> /
Enter value for a: lisi
Enter value for b: 24
old 2: values('&a',&b)
new 2: values('lisi',24)
1 row created.
SQL> /
Enter value for a: wangwu
Enter value for b: 26
old 2: values('&a',&b)
new 2: values('wangwu',26)
1 row created.
SQL> /
Enter value for a: wutian
Enter value for b: 26
old 2: values('&a',&b)
new 2: values('wutian',26)
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> col grade null 'null'
SQL> l
1 select b.name, a.grade, b.age
2 from a_t a,b_t b
3* where a.name(+)=b.name
SQL> /
NAME GRADE AGE
-------------------------------- ---------- ----------
lisi 60 24
wangwu 84 26
wutian null 26
zhangshan 80 26
SQL>
2:
SQL> l
1 select name, avg(grade), age
2 from (select b.name name, a.grade grade, b.age age
3 from a_t a,b_t b
4 where a.name(+)=b.name)
5* group by age, name
SQL> /
NAME AVG(GRADE) AGE
-------------------------------- ---------- ----------
lisi 60 24
wangwu 84 26
wutian 26
zhangshan 80 26
SWUTIAN这个人没有成绩不把他统计在内
3:
1、当我们想要为一个表创建唯一索引时，如果该表有重复的记录，则无法创建成功。
方法原理：
1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，
rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。
2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中
那些具有最大rowid的就可以了，其余全部删除。
3、以下语句用到了3项技巧：rowid、子查询、别名。
实现方法：
SQL> create table a (
2 bm char(4), --编码
3 mc varchar2(20) --名称
4 )
5 /
表已建立.
SQL> insert into a values('1111','1111');
SQL> insert into a values('1112','1111');
SQL> insert into a values('1113','1111');
SQL> insert into a values('1114','1111');
SQL> insert into a select * from a;
插入4个记录.
SQL> commit;
SQL> select rowid,bm,mc from a;
ROWID BM MC
------------------ ---- -------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111
查询到8记录.
查出重复记录
SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
ROWID BM MC
------------------ ---- --------------------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
删除重复记录
SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
删除4个记录.
SQL> select rowid,bm,mc from a;
ROWID BM MC
------------------ ---- --------------------
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111
其实方法有很多,用IN 或者用GROUP BY同样可以实现:
1: 使用IN:
A) 找出重复数据:
SQL> l
1 select rowid, e.* from a e
2* where e.rowid>(select min(x.rowid) from a x where x.bm=e.bm and x.mc=e.mc);
ROWID BM MC
------------------ ---- --------------------
AAABdcAAGAAAAYyAAE 1111 1111
AAABdcAAGAAAAYyAAF 1112 1111
AAABdcAAGAAAAYyAAG 1113 1111
AAABdcAAGAAAAYyAAH 1114 1111
B) 删除重复数据:
SQL> l
1 delete from a
2 where rowid in (select rowid from a e
3* where e.rowid>(select min(x.rowid) from a x where x.bm=e.bm and x.mc=e.mc))
SQL> /
4 rows deleted.
SQL> select * from a;
BM MC
---- --------------------
1111 1111
1112 1111
1113 1111
1114 1111
SQL>
2: 使用GROUP BY:
A): 找出重复数据:
SQL> l
1 select bm,mc
2 from a
3 group by bm,mc
4* having count(*)>1
SQL> /
BM MC
---- --------------------
1111 1111
1112 1111
1113 1111
1114 1111
SQL>
B) 删除重复数据:
SQL> delete from a
2 where (bm,mc) in (select bm,mc
3 from a
4 group by bm,mc
5 having count(*)>1)
6 and rowid not in (select min(rowid)
7 from a
8 group by bm,mc
9 having count(*)>1);
4 rows deleted.
SQL> select * from a;
BM MC
---- --------------------
1111 1111
1112 1111
1113 1111
1114 1111
SQL>
分享到：
发表评论
-
学习动态性能表 第一篇--v$sysstat
2011-12-23 19:50 606学习动态性能表 第一篇--v$sysstat -
Oracle10g DG
2011-11-17 00:42 841RHEL4.5中安装Oracle10g DG物理备库 原文 ... -
win 2003 和oracle 10g
2009-10-15 00:11 922今天朋友打来电话说自己的操作系统是 win2003R2 ... -
oracle 一些总结
2009-09-09 12:56 923全局临时表：插入数据可以查询到，一但commit数据将消失 2 ... -
手工创建数据库完整步骤
2009-09-09 12:54 1221help index column aa format a22 ... -
oracle如何解除死锁[进程互相锁死]
2009-09-09 12:53 2836oracle如何解除死锁[进程互相锁死] data direc ... -
Orcale 数据服务启动是的出错异常
2009-03-31 23:35 951不知道为什么最近在使用Orcale数据库的时 ... -
使用游标操作数据
2008-08-12 15:58 792下面的示例用@@FETCH_STATUS控制在一个WHILE循 ... -
声明游标
2008-08-12 15:53 773DECLARE CURSOR语句SQL-92标准语法格式： ... -
游标的实例
2008-08-12 15:51 856ECLARE H_SETTLE SCROLL CURSOR F ...
相关推荐
SQL高级查询技术是数据库管理中不可或缺的一部分，它涵盖了多种复杂操作，使得数据处理更为高效和精确。本章主要探讨三个核心主题：日期和时间处理、层次查询以及分析查询，这些都是Oracle Database 10g系统中重要的...
SQL 高级查询（入门与提高） SQL 高级查询是数据库管理系统中的一种高级查询语言，用于查询和处理数据库中的数据。以下是 SQL 高级查询的知识点： 1. 多表查询类型： * 连接（Join）：将两个或多个表连接成一个表...
"SQL高级查询50题" SQL 高级查询是数据库管理中的一种重要技术，能够帮助开发者和数据库管理员更好地管理和维护数据库。下面是50个 SQL 高级查询题目的知识点总结： 查询“001”课程比“002”课程成绩高的所有...
本话题主要聚焦于内联查询、左外联查询以及嵌套查询，这些都是SQL高级查询的重要组成部分。 首先，内联查询（也称为子查询）是指在一个查询语句中嵌套另一个查询，用以获取所需的数据。内联查询可以在SELECT、FROM...
在SQL的世界里，高级查询是数据库管理与数据分析的关键技能，尤其对于SQL Server这样的大型关系型数据库管理系统来说。本文将深入探讨“SQL 高级查询课件”中的核心知识点，包括查询、连接查询以及子查询。 首先，...
### SQL高级查询技术详解 #### 一、连接查询：整合多表数据的艺术 连接查询是SQL高级查询技术的核心，主要用于从两个或多个表中检索数据，实现数据的整合与关联。这种技术对于处理复杂的数据关系至关重要，特别是...
本资料提供了一系列的SQL高级查询练习题，旨在帮助学习者深入理解和熟练掌握SQL的复杂查询技巧。通过实践这些题目，你可以提升在数据筛选、聚合、联接、子查询、窗口函数以及自定义排序等方面的能力。 1. **子查询*...
根据提供的标题、描述以及部分代码内容，我们可以总结出关于SQL高级查询语句的关键知识点： ### SQL高级查询语句概述 SQL（Structured Query Language）是一种用于管理关系数据库的标准语言。随着业务需求的复杂化...
### SQL高级查询详解 #### 一、多表查询与笛卡尔积 在SQL查询中，经常需要从多个表中获取数据。为了实现这一目标，我们可以使用多表查询。多表查询的基本语法是在`FROM`子句中列出所有的表名。 ##### 示例 假设...
总之，T-SQL高级查询能力是数据库管理员和开发人员必备的技能之一，尤其在处理复杂的业务逻辑和数据检索时。解决此类问题需要对SQL语法有深入理解，包括JOIN、子查询、CTE、窗口函数等高级特性。
本主题“SQL高级查询之BBS数据库上机”将带你深入理解如何利用SQL在BBS（Bulletin Board System，电子公告板）数据库中进行复杂的数据操作。BBS数据库通常包含用户信息、帖子、回复等多种类型的数据，这些数据之间的...
sql高级查询代码大全——有了这，你就可以了解整个高级查询了
在SQL高级查询中，我们经常会遇到各种复杂的数据操作，如聚合函数、模糊查询、多表连接等。本教程将通过PPT形式深入讲解这些高级查询技巧。 首先，我们要理解SQL查询的基本语法，例如在描述中提到的代码改错示例：`...
SQL 高级查询案例详解 SQL 高级查询案例是数据库查询的核心部分，掌握高级查询技巧是数据库开发和管理的必备技能。本文将详细介绍 SQL 高级查询案例，包括子查询、内联视图、条件查询、排序和分组等多个方面。 一...
oralce SQL高级查询