📄 sql.txt
字号:
第一题：有两个表分别如下：2p
表A(varchar(32) NAME,int GRADE)/
数据：
ZHANGSHAN 80
LISI 60Q
WANGWU 84
表B(varchar(32) NAME,int AGE)z8
数据：
ZHANGSHAN 26 K15|p>
LISI 24802??U
WANGWU 26y)
WUTIAN 26;IS<;Z
（1）写SQL语句得到如下查询结果：k R
NAME GRADE AGE .'Hk
ZHANGSHAN 80 26 =]:
LISI 60 24\
WANGWU 84 26RV
WUTIAN NULL 26Gbn-
疑问：这里的没有成绩的那个人的记录怎么得到呢？
（2）写SQl语句根据名字（NAME）相同按年龄（AGE）分组得到不同年龄的人的平均成绩，并写出结果。
疑问：按照名字相同，WUTIAN这个人没有成绩该不该把他统计在内呢？
第二题：有一个数据库表dept中有如下数据：
id_no id_name
1000
1001
1002
1003
1000
1000
1001
表中有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语句实现题目要求的结果。
SQL> create table a0|{
2 (name varchar2(32)
3 grade int);
Table created.
SQL> insert into a_tLh
2 values('&a',&b);eM2OfP
Enter value for a: zhangshan@D
Enter value for b: 80G
old 2: values('&a',&b)
new 2: values('zhangshan',80)
1 row created.
SQL> //
Enter value for a: lisi
Enter value for b: 600
old 2: values('&a',&b)<R
new 2: values('lisi',60)
1 row created.-Ll$OH
SQL> /.V`IqI
Enter value for a: wangwud
Enter value for b: 84ktG
old 2: values('&a',&b)
new 2: values('wangwu',84)H
1 row created.K!
SQL> commit;-
Commit complete.
SQL> create table b_tk!bbf>
2 (name varchar2(32),
3 age int);cn
Table created.51F[
SQL> insert into b_tR[B'
2 values('&a',&b);DAdkV
Enter value for a: zhangshan
Enter value for b: 26.
old 2: values('&a',&b)XC
new 2: values('zhangshan',26)
1 row created.-_BgV9
SQL> /.VD
Enter value for a: lisiK0.Go
Enter value for b: 24Q
old 2: values('&a',&b)[
new 2: values('lisi',24)
1 row created.zEP
SQL> /jl
Enter value for a: wangwuNsC
Enter value for b: 26TiKAHS
old 2: values('&a',&b)6Y3:{
new 2: values('wangwu',26)/"
1 row created.jI[
SQL> /b
Enter value for a: wutian@2jl;
Enter value for b: 26Y4eNre
old 2: values('&a',&b)'$C&2
new 2: values('wutian',26)N#W>gk
1 row created.Hc
SQL> commit;0"Z
Commit complete.p;vA~
SQL>6J
SQL> col grade null 'null'm_Z=q}
SQL> l35O,oY
1 select b.name, a.grade, b.agee
2 from a_t a,b_t b|_E$8a
3* where a.name(+)=b.namejilrC
SQL> /:06t`
?达内科技论坛 -- 达内科技论坛 ~!q@,
NAME GRADE AGE(/6RE
-------------------------------- ---------- ----------0
lisi 60 24gj7
wangwu 84 26@lVB
wutian null 26/c_
zhangshan 80 26jt
?达内科技论坛 -- 达内科技论坛 .8
SQL>YY_y2
?达内科技论坛 -- 达内科技论坛 Lju
2:?达内科技论坛 -- 达内科技论坛 xp=|
SQL> lre
1 select name, avg(grade), aged3R
2 from (select b.name name, a.grade grade, b.age age:c.pNG
3 from a_t a,b_t bhN-#
4 where a.name(+)=b.name)-(
5* group by age, name8J[
SQL> /CtV.
?达内科技论坛 -- 达内科技论坛 R
NAME AVG(GRADE) AGER4^_Z
-------------------------------- ---------- ----------a6m
lisi 60 240I}M
wangwu 84 26C.P_%
wutian 26B
zhangshan 80 26Ewz,
?达内科技论坛 -- 达内科技论坛 <E[Hy
SWUTIAN这个人没有成绩不把他统计在内{pY
3:?达内科技论坛 -- 达内科技论坛 e_LEU&
1、当我们想要为一个表创建唯一索引时，如果该表有重复的记录，则无法创建成功。 \'/}
方法原理： C%}a:
1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的， NS`%
rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。 Z
?达内科技论坛 -- 达内科技论坛 Eb4,B
2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中 {+
那些具有最大rowid的就可以了，其余全部删除。 h
?达内科技论坛 -- 达内科技论坛 M}
3、以下语句用到了3项技巧：rowid、子查询、别名。 gNc
?达内科技论坛 -- 达内科技论坛 p
实现方法： pWN}
SQL> create table a ( v\GZq
2 bm char(4), --编码 )f"=5
3 mc varchar2(20) --名称 GQ
4 ) {0ltu"
5 / =pb[:z
?达内科技论坛 -- 达内科技论坛 !gjvXr
表已建立. b
?达内科技论坛 -- 达内科技论坛 Z4O
SQL> insert into a values('1111','1111'); BBU
SQL> insert into a values('1112','1111'); $J;BP
SQL> insert into a values('1113','1111'); A9o!{b
SQL> insert into a values('1114','1111'); j
?达内科技论坛 -- 达内科技论坛 JChx6k
SQL> insert into a select * from a; l?i
?达内科技论坛 -- 达内科技论坛 Fb^
插入4个记录. ?t,2
?达内科技论坛 -- 达内科技论坛 /+m
SQL> commit; lp&L:
SQL> select rowid,bm,mc from a; _*',
?达内科技论坛 -- 达内科技论坛 8&|^b&
ROWID BM MC WmF:
------------------ ---- ------- `?56s
000000D5.0000.0002 1111 1111 ]"u`
000000D5.0001.0002 1112 1111 b4
000000D5.0002.0002 1113 1111 lvEt
000000D5.0003.0002 1114 1111 v
000000D5.0004.0002 1111 1111 4O
000000D5.0005.0002 1112 1111 V
000000D5.0006.0002 1113 1111 ~X,G^o
000000D5.0007.0002 1114 1111 v!HC
?达内科技论坛 -- 达内科技论坛 +x 2
查询到8记录. s!
?达内科技论坛 -- 达内科技论坛 @6!~
?达内科技论坛 -- 达内科技论坛 bRA
查出重复记录 8rLXD
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); <[
?达内科技论坛 -- 达内科技论坛 K@
ROWID BM MC lFz
------------------ ---- -------------------- gei5$r
000000D5.0000.0002 1111 1111 ;8eA
000000D5.0001.0002 1112 1111 6H9
000000D5.0002.0002 1113 1111 A/1]Ve
000000D5.0003.0002 1114 1111 6w
?达内科技论坛 -- 达内科技论坛 0XQsg
删除重复记录 {rAQ
SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc); X
?达内科技论坛 -- 达内科技论坛 X
删除4个记录. LEz
?达内科技论坛 -- 达内科技论坛 "rd&m1
SQL> select rowid,bm,mc from a; E7AteF
?达内科技论坛 -- 达内科技论坛 <o7m
ROWID BM MC A@D&B
------------------ ---- -------------------- /)F@
000000D5.0004.0002 1111 1111 P
000000D5.0005.0002 1112 1111 *'M>>
000000D5.0006.0002 1113 1111 8l
000000D5.0007.0002 1114 1111+
Larry Zhao （赵敏）
加拿大达内科技(中国)公司（上海）
电话：021-61202630
传真：021-61202663-803
邮箱：zhaomin@tarena.com
地址：上海市黄浦区北京东路668号上海科技京城C区902
网址：http://www.tarena.com.cn
2003/12/31 10:14am IP: 已设置保密 [本文共5757字节]
SunOne
等级: 新手上路
信息:
威望: +3 积分: 0
现金: 992 雷傲元
存款: 20010 雷傲元
贷款: 没贷款
来自: 北京海淀
发帖: 1187 篇
精华: 0 篇
资料:
在线: 00 时 37 分 17 秒
注册: 2003/11/07 11:06am
造访: 2006/07/15 09:13pm
消息 查看 搜索 好友 引用 回复 只看我 [第 6 楼]
谢谢LARRY的详细解答！ 看君一回贴，胜看10天书！ 现在笔试只要涉及数据库的都会遇到这些怪题，老师上课时讲的正规题他们就是不考！！以后我可要常来向老师讨教了！ TKS！(bBVlX
2003/12/31 05:54pm IP: 已设置保密 [本文共183字节]
lingyu1099 门派: 香蕉派
等级: 新手上路
信息:
威望: +2 积分: 0
现金: 18530 雷傲元
存款: 10 雷傲元
贷款: 没贷款
来自: 北京
发帖: 1311 篇
精华: 0 篇
资料:
在线: 00 时 04 分 10 秒
注册: 2003/06/23 01:30am
造访: 2006/07/15 05:29pm
消息 查看 搜索 好友 引用 回复 只看我 [第 7 楼]
不知道是为了什么,我们学员碰到的DB问题大都很怪异,这种现象好还是不好,能考到基本功吗??b
当太阳还没出来,我们就开始奔跑!
2004/01/04 02:27am IP: 已设置保密 [本文共107字节]
vvyvvy
等级: 新手上路
信息:
威望: 0 积分: 0
现金: 2588 雷傲元
存款: 没开户
贷款: 没贷款
来自: 保密
发帖: 63 篇
精华: 0 篇
资料:
在线: 00 时 00 分 00 秒
注册: 2003/07/15 06:15am
造访: 2004/01/07 00:12am
消息 查看 搜索 好友 引用 回复 只看我 [第 8 楼]
我也遇到过类似的问题.7q0K5K
不过我当时使用in,也没有解决这个问题.6Xvm
很谢谢larry!*qX<BI
2004/01/05 11:45pm IP: 已设置保密 [本文共101字节]
larry 头衔: 论坛版主
等级: 新手上路
信息:
威望: 0 积分: 0
现金: 7099 雷傲元
存款: 没开户
贷款: 没贷款
来自: 保密
发帖: 555 篇
精华: 0 篇
资料:
在线: 00 时 18 分 38 秒
注册: 2003/07/07 10:07am
造访: 2006/06/26 07:41pm
消息 查看 搜索 好友 引用 回复 只看我 [第 9 楼]
其实方法有很多,用IN 或者用GROUP BY同样可以实现:V8o
1: 使用IN:^yc
?达内科技论坛 -- 达内科技论坛 8aVf+m
A) 找出重复数据:SL
SQL> lt
1 select rowid, e.* from a e2[u$
2* where e.rowid>(select min(x.rowid) from a x where x.bm=e.bm and x.mc=e.mc);(
ROWID BM MC<N
------------------ ---- --------------------d26@s
AAABdcAAGAAAAYyAAE 1111 1111?YH,
AAABdcAAGAAAAYyAAF 1112 1111y9)KK
AAABdcAAGAAAAYyAAG 1113 11118=^@r
AAABdcAAGAAAAYyAAH 1114 1111!ZkWm2
?达内科技论坛 -- 达内科技论坛 ]LAw+
B) 删除重复数据:_I@?
SQL> lVnG4tr
1 delete from abpc(h
2 where rowid in (select rowid from a e L-$p
3* where e.rowid>(select min(x.rowid) from a x where x.bm=e.bm and x.mc=e.mc))n'k^
SQL> /<@
?达内科技论坛 -- 达内科技论坛 ]
4 rows deleted..wmvQo
?达内科技论坛 -- 达内科技论坛 -S?
SQL> select * from a;6.,
?达内科技论坛 -- 达内科技论坛 \
BM MCs(1ub4
---- --------------------:X
1111 1111Ki9:
1112 1111R@d7o
1113 11113kv.
1114 1111ew3
?达内科技论坛 -- 达内科技论坛 nd9
SQL>"X[M
?达内科技论坛 -- 达内科技论坛 `
2: 使用GROUP BY:Lv'H'
A): 找出重复数据:O^f4y
SQL> l'
1 select bm,mcPO@
2 from aEhTmj>
3 group by bm,mca8YdNV
4* having count(*)>1L7Q>
SQL> /v
BM MCJD_
---- --------------------aw]"2
1111 1111<l
1112 11113
1113 1111sw~?`{
1114 1111p;i
SQL>6_[sW~
B) 删除重复数据:
SQL> delete from a L
2 where (bm,mc) in (select bm,mc/{
3 from acfR`YW
4 group by bm,mc8T+
5 having count(*)>1)9
6 and rowid not in (select min(rowid)kl&W%G
7 from a[AhNSm
8 group by bm,mc(aJ,tb
9 having count(*)>1);\:r9i0
4 rows deleted.=
SQL> select * from a;Ks5
BM MCt^C2
---- --------------------;
1111 1111mc
1112 1111Z
1113 1111?
1114 1111GJ:I2,
SQL>@
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -