曾经在面试过程中遇到的关于Oracle的查询
以前在国企中做开发使用的是Oracle数据库,因此对这个数据库也进行了全面学习,经常要写增删改查,试图,存储过程等语句。这是之前换工作的时候在面试过程中遇到的几个关于Oracle数据库方面的试题,个人感觉还是很不错的,我们在项目中也会遇到类似的需求。
创建表
首先我们要在数据库中创建三张表(用户表,角色表,用户角色表),脚本如下:
/*创建用户表*/
create table USERS
(
USERID NUMBER(4) not null,
USERNAME NVARCHAR2(30)
)
/*创建角色表*/
create table ROLE
(
ROLEID NUMBER(2) not null,
ROLENAME NVARCHAR2(30)
)
/*创建用户角色关系表*/
create table USERROLES
(
USERID NUMBER(4),
ROLEID NUMBER(2)
)
问题一
有三张表用户表(users),角色表(role),用户角色表(userroles),用户与角色的关系是多对多,要求查询显示的结果如下:
下面通过两种方式实现
/* 方法一,使用分组函数 group by */
SELECT W.USERNAME, MAX(DECODE(T.ROLEID, 1, '管理员', NULL)) || ',' || MAX(DECODE(T.ROLEID, 2, '普通用户', NULL)) AS "角色"
FROM USERS W, ROLE T, USERROLES S
WHERE W.USERID = S.USERID
AND T.ROLEID = S.ROLEID
GROUP BY W.USERNAME
/* 方法二,使用oracle内置函数 WMSYS.WM_CONCAT */
SELECT P.USERNAME,
DECODE(SUBSTR(P.ROS, 0, 1), 1, '管理员') || ',' ||
DECODE(SUBSTR(P.ROS, 3, 1), 2, '普通用户')
FROM (SELECT W.USERNAME, WMSYS.WM_CONCAT(T.ROLEID) ROS
FROM USERS W, ROLE T, USERROLES S
WHERE W.USERID = S.USERID
AND T.ROLEID = S.ROLEID
GROUP BY W.USERNAME) P
问题二
有一张没有主外键关系的数据表,删除所有列相同的重复数据。
1.查询所有列相同的数据行
SELECT T.字段1, T.字段2, T.字段3, COUNT(*)
FROM TABLE_1 T
GROUP BY T.字段1, T.字段2, T.字段3
HAVING COUNT(*) > 1
2.删除上述查询的数据结果集
DELETE FROM TABLE_1 S
WHERE S.字段1, S.字段2, S.字段3 IN (
/*查询1语句*/
)
把删除语句里面加入第一个查询的语句,这样就可以删除表中所有列相同的重复数据,如果记录多的话删除会比较慢。
还也可以通过存储过程完成。
建议:先将符合条件的数据记录(即为要删除的数据库纪录)存取到一个临时数据表中,然后再进行删除时就不用进行查询了,提高了数据库的删除速度。
另外大家可能会问,这样删除肯定会存在一个问题,我们不是把所有重复的数据删掉了吗?而我们想保留重复纪录中最新一条的记录,该如何实现呢!
在Oracle中,有个隐藏的列自动rowid,里面会给每条记录唯一的rowid,如果我们想保存最新的一条记录,我们就可以利用这个字段,保存重复数据最大的rowid就可以实现了。
SELECT A.ROWID, 字段1, 字段2
FROM TABLE_1 A
WHERE A.ROWID != (SELECT MAX(B.ROWID)
FROM TABLE_1 B
WHERE A.字段1 = B.字段1
AND A.字段2 = B.字段2)
这样就可以把所有的重复记录(rowid不是最大)查找出来。子查询里面的SQL语句是查找出rowid最大的重复数据记录,而外面的是除去 rowid 最大的其他重复数据记录。当我们要删除这些记录时,可以使用语句:
DELETE FROM TABLE_1 A
WHERE A.ROWID IN A.ROWID != (SELECT MAX(B.ROWID)
FROM TABLE_1 B
WHERE A.字段1 = B.字段1
AND A.字段2 = B.字段2)
其实还有一道题,就是关于学生、老师、课程、成绩这几张表之间的关系查询,在面试过程中还是会经常遇到,有空还是需要好好的熟练数据库的基本用法,不同数据库有些语法也是不一样的,一定要多学习有助于提高自己。
温馨提示
文章内容如果写的存在问题欢迎留言指出,让我们共同交流,共同探讨,共同进步~~~
文章如果对你有帮助,动动你的小手点个赞,鼓励一下,给我前行的动力。
- 点赞
- 收藏
- 关注作者
评论(0)