`
kakaluyi
  • 浏览: 444483 次
  • 性别: Icon_minigender_1
  • 来自: 苏州
社区版块
存档分类
最新评论

某个大公司的sql面试题,自己不太会做有没有童鞋指点一下

阅读更多

自己对sql的理解。用一句俗话说:人有七窍,已经通了六窍,有没有童鞋指点一下

问:关系模式:User(userId, userName), Article(articleId, userId, title,   content),Vote(articleId, score),User为用户关系,Article为用户发表的文章关系,Vote为文章得票关系,title为文章标题、score为得票数。
(1)用SQL语言查询所有没发表过文章的用户名;
(2)用SQL语言查询得票数大于100的所有文章标题,按得票数倒序排列;
(3)用SQL语言查询出发表文章数大于5,文章平均得票数大于100的用户名,按平均得票数倒序排列;
(4)设计这些表的主键、外键和索引,并指出上面三个查询所使用的索引。
(5)当用户数超过1000万,文章数超过1亿时,如何考虑存储及性能的改进和优化?

 

答: 

 

1 select * from User where useid  not in(select userid from Article);
2 select title from article   inner  join vote  on article.articleid=vote.aritcleid and  vote.score>100 order by vote.score asc;
3有点不太会,下面胡乱乱写了一通

3 select * from user where userid in(select userid from Article inner join vote on article.articleid = vote.articleid group by userId  having avg(score)>100) group by userid having count(*) >5;

4主键外键应该很简单,索引第一个应该是userid,第二个是articleid 和score,第三个应该是articleid和 userid

5用户数按照id分割分布式存储,文章类似,还可以用读写分离等策略水平扩展数据库.

分享到:
评论
46 楼 wps352 2010-08-21  
mark 学习了。~~
45 楼 alvin969 2010-08-19  
第四题:
主键的话是毫无疑问的,user表里的userid,article表里的articleid,vote表里的articleid。
一般来说,在设计主键时,最好采用字符型的.不采用自动递增,在新增记录时,系统生成主键值。而且,主键最好不具有任何实际意义,因为带有实际意义的字段,还是存在被修改的可能性.而对于主键最大的忌讳就是修改主键,这可能会导致非常严重的不可估计的后果。
外键的话就是article表里的userid,vote表里的ariticleid。

建立索引的时候要注意,复合索引对多条件查询的速度提速是很明显的,但是用不好的话,不但对sql查询的速度没有提升,还会拖慢数据插入的速度。当数据量达到100万的时候,复合索引甚至会成倍的拖慢插入速度。比如article表中,建立(articleid,userid)索引,必须同时使用两列查询条件,才能使用复合索引,用userid关联user表和article表时,就不会走索引。
同理,SQL Server里面的聚类索引也要慎用。索引递增插入还好,否则就是悲剧了。

唯一性索引是效率最高的。

个人认为,user下userid列建立一个索引,article表建立两个索引,一个是articleid,一个是userid,vote建立一个索引,是articleid。
44 楼 alvin969 2010-08-19  
然后是第三题:

平常习惯就不太常用having,跟前面不用in是一样的道理,having的效率总不会比where条件更快。语句如下:
select userid from(
select a.userid,count(1) articleqty, avg(c.score) scoreavg
from user a,artical b,vote c
where a.userid=b.userid
and b.articleid=c.articleid
group by a.userid
) aa where articleqty>5 and scoreavg>100

三表关联会消除没有发表文章的userid,但是为了减少子查询的条数,还可以进一步改进:
select userid,scoreavg from(
select aa.userid,avg(bb.score) scoreavg
from(
select a.userid,b.articleid,count(1) articleqty
from user a,artical b
where a.userid=b.userid
group by a.userid
) aa,vote bb
where aa.articleid=bb.articleid
and aa.articleqty>5
group by aa.userid
) aaa
where aaa.scoreavg>100
order by scoreavg desc

这样会根据发表文章数大于5做一个初步过滤,减小驱动表的数据量。如果大量存在非活跃用户,这种筛选还是能提速不少的。当然,最外面的一层查询可以改成having。

还有一种情况,就是如果没有人评分过的文章就在vote表中添加记录,而且大量存在未评分文章,那么vote表的数量就会比article小很多,可以使用第一个SQL,三表关联,以vote作为驱动表,也应该能提高不少效率。


43 楼 alvin969 2010-08-19  
忍不住来叨叨两句,有不对的大家指点哈!

第一题:
用not in还是not exists快,这要取决于不同数据库不同sql了。就此题来说,在SQL Server中两者是一样快的,正好有现成数据,刚试验了一下,user表跟article表各五万条数据,not in和not exists的写法运行时间均是五秒。分析执行计划也是一样的。主要的时间代价花费到了三个地方:两个表的索引扫描约是55%,多线程的并行分拆及合并11%,哈希匹配14%。看了一下执行计划,SQL Server对这两者皆做了优化,主要工作还是在索引和建立hash关系上,于是就有了第三种写法:
select count(userida) from(
select a.userid userida,b.userid useridb from user a left join article b on a.userid=b.userid
) aa where useridb is null

试了试,跟not in, not exists执行计划基本一致,运行时间也是一样的。

在oracle下就复杂多了,RBO还是CBO、表的大小都有可能改变执行计划。在基于规则的RBO优化器下,exists和in的执行计划是一致的,跟 not exists, in ,not in执行计划都不一样,其中exists, not exists使用了不同的hash计算,not in是效率最低的,用的是filter,要做笛卡尔积再用条件过滤,巨慢。不过通过加HINT,可以选择合适的执行计划,这点也是我喜欢oracle不喜欢sql server的一个重要原因,在上百行的复杂sql的优化中很是有用。

综上,写not exists是最保险的做法了,基本能保证速度最快。
42 楼 cxr1217 2010-08-19  
kakaluyi 写道
vision2000 写道
我是来关注第五题的,前面的题目没兴趣

1、我把3个表合成一个表:Table(userId, userName,articleId,  title,   content,score)
   理由:一个userName不占用很多存储空间,空间换取速度,如果有其他属性,可以分成用户表和文章表,甚至可以做一扩展属性表,把不常用的属性放入扩展表,减少查询数据的表连接,userName字段的变动不会很大,即使变Table不一定要跟着变,这样可以知道在发表该文章的时候userName是什么,如果一定要变建立userId的索引,update也是很高效率的。
2、根据用户的点击率和、登录频、文章点击率等高使用频率分级存储数据
3、建立相关查询的表索引,使用服务器缓存高使用频率的数据

   恩面试完了,没有sql的问题。
谢谢大家的答复,这个朋友的建议
1首先3个表应该不能完全合并的,毕竟用户和文章是一对多的关系,
2索引,缓存数据库,分布式确实是第五题的通用解决方法,
3还有就是数据库的一些性能调优比如mysql的 table_cache key_buffer_cache,合理利用服务器超强的性能

昨天问到一个数据库连接池的设计问题和一些优化设计问题,过段时间再和大家探讨一下


我也感觉3个表合成一个是不合理的。
楼主,正确答案没有是么?或者你可以整理一下你的问题,发表一个正确答案来结贴啊。第五个问题,用分区的方式来解决应该可以吧。
mysql里面倒是可以的,使用用户的id或者名字来进行分区
article 也用分区,也可以使用articleid和userid来分区

41 楼 kakaluyi 2010-08-19  
vision2000 写道
我是来关注第五题的,前面的题目没兴趣

1、我把3个表合成一个表:Table(userId, userName,articleId,  title,   content,score)
   理由:一个userName不占用很多存储空间,空间换取速度,如果有其他属性,可以分成用户表和文章表,甚至可以做一扩展属性表,把不常用的属性放入扩展表,减少查询数据的表连接,userName字段的变动不会很大,即使变Table不一定要跟着变,这样可以知道在发表该文章的时候userName是什么,如果一定要变建立userId的索引,update也是很高效率的。
2、根据用户的点击率和、登录频、文章点击率等高使用频率分级存储数据
3、建立相关查询的表索引,使用服务器缓存高使用频率的数据

   恩面试完了,没有sql的问题。
谢谢大家的答复,这个朋友的建议
1首先3个表应该不能完全合并的,毕竟用户和文章是一对多的关系,
2索引,缓存数据库,分布式确实是第五题的通用解决方法,
3还有就是数据库的一些性能调优比如mysql的 table_cache key_buffer_cache,合理利用服务器超强的性能

昨天问到一个数据库连接池的设计问题和一些优化设计问题,过段时间再和大家探讨一下
40 楼 vision2000 2010-08-19  
我是来关注第五题的,前面的题目没兴趣

1、我把3个表合成一个表:Table(userId, userName,articleId,  title,   content,score)
   理由:一个userName不占用很多存储空间,空间换取速度,如果有其他属性,可以分成用户表和文章表,甚至可以做一扩展属性表,把不常用的属性放入扩展表,减少查询数据的表连接,userName字段的变动不会很大,即使变Table不一定要跟着变,这样可以知道在发表该文章的时候userName是什么,如果一定要变建立userId的索引,update也是很高效率的。
2、根据用户的点击率和、登录频、文章点击率等高使用频率分级存储数据
3、建立相关查询的表索引,使用服务器缓存高使用频率的数据
39 楼 finux 2010-08-18  
Mrpublic 写道
finux 写道
呵呵。。。发下我的想法,不一定正确哦~
1.
select username from user usr
left join article art on art.userid = user.userid
where art.userid is null;



可以把On 与 where 在一起用哦?? 求解

我只见过... left join ... on ...没有见过 后面还有一个where,
本人菜鸟,给解...

你试试就知道了,嘻嘻。。。
我天天写这样的SQL呀~
38 楼 tianhewulei 2010-08-18  
not in还是少用,个人感觉很慢... 左连接之后isnull应该比not in会好很多。
37 楼 pollyduan 2010-08-18  
reilost说的难道是BD?
36 楼 mogui258 2010-08-18  
都没说第五题啊,求解。
35 楼 dhj1986 2010-08-18  
讨论一下最后一个问题吧
34 楼 longhua828 2010-08-18  
哥们第一个就错了,人要的是  用户名 
33 楼 nashuoliqu 2010-08-18  
<div class="quote_title">kakaluyi 写道</div>
<div class="quote_div">
<p><span>自己对sql的理解。用一句俗话说:人有七窍,已经通了六窍,有没有童鞋指点一下<img src="/images/smiles/icon_smile.gif" alt=""></span></p>
<p><span>问:关系模式:User(userId, userName), Article(articleId, userId, title,   content),Vote(articleId, score),User为用户关系,Article为用户发表的文章关系,Vote为文章得票关系,title为文章标题、score为得票数。<br>(1)用SQL语言查询所有没发表过文章的用户名;<br>(2)用SQL语言查询得票数大于100的所有文章标题,按得票数倒序排列;<br>(3)用SQL语言查询出发表文章数大于5,文章平均得票数大于100的用户名,按平均得票数倒序排列;<br>(4)设计这些表的主键、外键和索引,并指出上面三个查询所使用的索引。<br>(5)当用户数超过1000万,文章数超过1亿时,如何考虑存储及性能的改进和优化?</span> </p>
<p> </p>
<p>答: </p>
<p> </p>
<p><span>1 select * from User where useid  not in(select userid from Article);<br>2 select title from article   inner  join vote  on article.articleid=vote.aritcleid and  vote.score&gt;100 order by vote.score asc; <br>3有点不太会,下面胡乱乱写了一通</span></p>
<p><span>3 select * from user where userid in(select userid from Article inner join vote on article.articleid = vote.articleid group by userId  having avg(score)&gt;100) group by userid having count(*) &gt;5;</span></p>
<p><span>4主键外键应该很简单,索引第一个应该是userid,第二个是articleid 和score,第三个应该是articleid和 userid</span></p>
<p><span>5用户数按照id分割分布式存储,文章类似,还可以用读写分离等策略水平扩展数据库.</span></p>
</div>
<p> </p>
32 楼 spell 2010-08-18  
msi110 写道
貌似大家都关心些SQL语句去了?
还是应该多讨论一下后面两个问题吧...............

恩,围观中,我只知道根据ID mod 下,然后找到相应的表去取数据,如userid %10 ,如果=1,就放在 user_1表,=2就在user_2中。不过这样做统计之类的话,就比较折腾了,大家有什么好的方案不?
31 楼 msi110 2010-08-18  
貌似大家都关心些SQL语句去了?
还是应该多讨论一下后面两个问题吧...............
30 楼 lirong1978 2010-08-18  
只要查出东西出来就行了吧,考虑用这个用那个累不累啊
29 楼 icefishc 2010-08-18  
Mrpublic 写道
lerous 写道
kakaluyi 写道
谢谢,第3个题目看来只要自己好好再想想就出来了,
exists在写hql和jdbc的时候从来没有用过,据说少量结果集用exists,大量结果集用in,再研究研究


如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in;反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists


请问: 你说的这个: 主查询中表较大又有索引时应该用In;反之外层主查询记录较少,子查询表大又有索引时用exists是有实践证明 ,还是某本书某个老师说的

这种东西基本都是误传。
28 楼 runshine 2010-08-18  
shaobaitou 写道

建议count(*)少用,用count(userid)之类的、


这种说法似乎是误传...
27 楼 Mrpublic 2010-08-18  
facade 写道
查询:
(1)
SELECT U.USERNAME FROM USER U WHERE NOT EXISTS
(SELECT 1 FROM ARTICLE A
 WHERE U.USERID = A.USERID) 
 


帅多,你的这个 1是什么意思呀?没见过,请给分析一下...

相关推荐

    经典sql面试题

    ### 经典SQL面试题解析 #### 颇具挑战性的SQL面试题——删除冗余记录 **题目背景与分析** 在本题中,我们面对的是一个常见的问题:如何有效地处理数据库中的冗余数据。在实际应用中,由于各种原因(如数据导入...

    sql面试题很详细的sql语句面试题

    SQL面试题详解 本资源旨在总结和详细解释SQL面试题中的各种问题和解决方案,并对每个问题进行了详细的解释和分析。 一、查询每门课都大于80分的学生姓名 问题:使用一条SQL语句查询出每门课都大于80分的学生姓名...

    尚硅谷大数据技术之企业SQL面试题_大数据_sql_尚硅谷sql_doc_

    总之,"尚硅谷大数据技术之企业SQL面试题"是一个全面复习和提升SQL技能的宝贵资源,无论是对于正在找工作的专业人士还是想要提升自己大数据技能的开发者,都是一份非常实用的学习材料。通过深入学习和实践,可以增强...

    sql面试题(适合软件测试)

    根据提供的文件内容,我们可以整理出以下几个SQL面试题及其解答,这些问题主要聚焦于SQL查询技巧、数据处理以及数据结构的理解。 ### 1. 查询每门课都大于80分的学生姓名 **题目描述**:给定一张成绩表,其中包含...

    java 面试题基础题,sql 面试题 带答案

    二、SQL 面试题 1. **SQL 数据类型:** 包括数值类型(如 INT、FLOAT)、字符串类型(如 VARCHAR、CHAR)、日期时间类型(如 DATE、TIMESTAMP)等。 2. **SELECT 语句:** 用于从数据库中选择数据,如 `SELECT *...

    SQL经典面试题及答案SQL经典面试题及答案

    SQL经典面试题及答案 从给定的文件信息中,我们可以总结出四个重要的SQL知识点: 一、使用GROUP BY语句来统计分类结果 在给定的部分内容中,我们可以看到一个经典的SQL面试题,即如何使用GROUP BY语句来统计分类...

    SQL面试题(很全,带答案)

    根据给定的文件标题、描述、标签以及部分内容,我们可以从中提炼出以下详细的IT知识点,主要聚焦于SQL语言的基础操作及面试常考知识点: ### SQL语言基础分类与功能 #### 数据定义语言(DDL) - `CREATE DATABASE`:...

    几个SQL面试题及答案.sql

    几个SQL面试题及答案.sql

    sqlserver+sqlserver面试题及答案

    sqlserver+sqlserver面试题及答案 sqlserver+sqlserver面试题及答案 sqlserver+sqlserver面试题及答案

    SQL_Server 面试笔试试题及答案

    "SQL Server 面试笔试试题及答案" 本资源摘要提供了 SQL Server 面试笔试试题及答案,涵盖了数据库...* 本资源提供了 19 道 SQL Server 面试题,涵盖了数据模型、实体关系、SQL 语言、Transact-SQL 等多方面的知识点。

    sql面试题以及答案

    sql,面试题,sql,面试题,sql,面试题,sql,面试题sql,面试题,sql,面试题sql,面试题,sql,面试题sql,面试题,sql,面试题sql,面试题,sql,面试题sql,面试题,sql,面试题sql,面试题,sql,面试题sql,...

    SQL面试题目 大公司的面试题

    很常见的面试题目 希望对大家找工作有帮助 特别是应届毕业生 SQL

    SQL面试题汇总.pdf

    在SQL面试题(1)的答案中,使用了`RIGHT JOIN`将`testtable1`和`testtable2`进行连接。右连接返回所有右表(在这里是`testtable2`)的记录,即使在左表(这里是`testtable1`)中没有匹配的记录。对于左表中没有...

    Hive面试题SQL测试题目1数据

    文章Hive面试题SQL测试题目所需数据,包含建表语句 测试数据等等...................

    SQL高级面试题及答案

    以下是一些关于SQL高级面试题的关键知识点,以及它们的解释和应用: 1. **复制表(只复制结构)**: - 使用`SELECT * INTO`语句,配合`WHERE 1&lt;&gt;1`或`TOP 0 *`,可以创建一个新表并复制源表的结构,但不包含数据。...

    经典SQL面试题及答案

    经典sql面试题及答案,所有页面使用FoxitReader、PDF-XChangeViewer、SumatraPDF和Firefox测试都可以打开。本资源转载自网络,如有侵权,请联系上传者或csdn删除。

    常见面试题sql

    常见面试sql题

    sql语句的内联外联 经典面试题

    SQL语句的内联外联经典面试题 SQL语句的内联外联是一种常见的数据库查询技术,它可以根据不同的连接方式将多个表连接起来,提高查询效率和数据整合性。在面试中,SQL语句的内联外联经典面试题是一个非常重要的考察...

Global site tag (gtag.js) - Google Analytics