我为NET狂官方面试题-数据库篇答案

 题目:http://www.cnblogs.com/dunitian/p/6028838.html

汇总:http://www.cnblogs.com/dunitian/p/5977425.html

说明:如有错误可以批评指正,有更好写法也可以提点下~

 

1. 求结果:select "1"?

报错,SQL里面只有单引号,列如:'xx'

   

2. 查找包含"objs"的表?查找包含"o"的数据库?

select * from sys.objects where name like '%objs%'

select * from sys.databases where name like '%o%'  

   

3. 求今天距离2002年有多少年,多少天?

select datediff(yy,'2002',getdate())

select datediff(dd,'2002',getdate())  

   

4. 请用一句SQL获取最后更新的事务号(ID)

 

select top 1 ID from ServerUpdateTime order by  LastUpdateDate desc  

   

5. 有如下两个表:

①请查询11 ~ 15记录的User

只是解题用:

select top 5 * from (select row_number() over(order by [User].UserID) ID,* from [User]) UserInfo

where UserInfo.ID>=11 and UserInfo.ID<=15

和子查询的对比图:

 

 

真正项目往往查询User完整信息:

--其他写法

select * from 

(

    select top 5 * from (select row_number() over(order by [User].UserID) ID,* from [User]) UserInfo

    where UserInfo.ID>=11 and UserInfo.ID<=15

) Temp

inner join User_Score on Temp.UserID=User_Score.UserID

   

--推荐写法

select top 5 * from 

(

    select row_number() over(order by Temp.UserID) ID,* from 

    (

        select [User].UserID,UserName,UserType,ScoreID,Score from [User]

        inner join User_Score on [User].UserID=User_Score.UserID

    )Temp

) UserInfo

where UserInfo.ID>=11 and UserInfo.ID<=15  

   

依据:推荐写法,看起来效率应该低点,但事实证明比其他写法效率高

②查询用户类型type=1总积分排名前十的user

 select top 10  [User].* from [User] 

 inner join User_Score on [User].UserID=User_Score.UserID

 where UserType=1

 order by Score desc 

 

③写一条存储过程,实现往User中插入一条记录并返回当前UserId(自增长id)

--推荐写法

if(Exists(select * from sys.objects where name=N'Usp_InsertedID'))

  drop proc Usp_InsertedID

go

create proc Usp_InsertedID

as

  insert into [User] output inserted.UserID values(N'张三蛋',3)

 

--另一种写法(SCOPE_IDENTITY()可以得到当前范围内最近插入行生成的标示值)

if(Exists(select * from sys.objects where name=N'Usp_InsertedID'))

    drop proc Usp_InsertedID

go

create proc Usp_InsertedID

as

    insert into [User] values(N'李狗蛋',1)

    select scope_Identity()

go

 

--不推荐:(@@Identity就不一定是当前范围内了)

if(Exists(select * from sys.objects where name=N'Usp_InsertedID'))

    drop proc Usp_InsertedID

go

create proc Usp_InsertedID

as

    insert into [User] values(N'张三章',2)

    select @@Identity

go

   

exec Usp_InsertedID

   

6. 请求出每个班级的数学平均分,并按照高低进行排序

select avg(Score) AvgScore from Student

where Subject=N'数学'

group by Class

order by AvgScore desc  

   

7. 一个TestDB表有A,B两个字段。

①写一句SQL求出有重复值的记录。

--解题专用

select * from TestDB

where A in

(

    select A from TestDB

    group by A,B

    having count(*)>1

)

order by A

   

--推荐:实际运用(真实环境下往往是为了找出重复值然后删掉)

select * from

(

    select row_number() over(partition by A,B order by A) ID,* from TestDB

) Temp

where Temp.ID>1

 

执行效率还是有很大差距的,有图有真相:

②请删除重复项。(最好用两种方法)

--传统写法:

select * into #Temp from (select distinct * from TestDB) A

drop table TestDB

select * into TestDB from #Temp

drop table #Temp

   

--推荐写法(真正项目中基本上不会真删)

delete Temp from (select row_number() over(partition by A,B order by A) ID,* from TestDB)Temp

where Temp.ID>1

   

8. 表中有A,B,C三列,用SQL实现:当A列>B列选择A,否则选择B,当B列>C列选择B,否则选择C

select

 (

    case

        when A>B then A

        else B

    end

 ),

 (

    case

        when B>C then B

        else C

    end

 )from ABC

   

9. 数据行列互换

转换前:

转换后:

select Name,

sum(

    case Courses

        when '语文' then Score else 0

    end

) 语文,

sum(

    case Courses

        when '数学' then Score else 0

    end

)数学,

sum(

    case Courses

        when '物理' then Score else 0

    end

)物理 from Student_Courses_Score

group by Name

 

10. 请统计每个URL访问次数,并按访问次数由高到低的顺序排序

select url,Count(*) n from WebUrl

group by url

order by n desc

 

顺便打破一个伪结论:count(1)性能大于count(*)==》不要麻木相信优化,自己证实后再说~(PS:一般都是count(主键),有时候其他情况有大坑

 

11. 用户注册表中id是自增长的。

①请查询出一天24h每小时注册的人数

select datepart(hh,CreateTime) '小时',count(*) '注册人数' from User_Register

where CreateTime>=convert(varchar(10),getdate(),120) and CreateTime <convert(varchar(10),dateadd(day,1,getdate()),120)

group by datepart(hh,CreateTime)

 

②请查询第4条记录

select * from (select row_number() over(order by ID) RId,* from User_Register) Temp

where RId=4

 

③请查询ID重复次数大于2次的记录

--传统方法(偏向于全部找出来)

select * from User_Register

where ID in

(

    select ID from User_Register

    group by ID having count(ID)>1

)

order by ID

   

--推荐方法(偏向于找多余重复值)

select * from (select row_number() over(partition by ID order by ID) RId,* from User_Register) Temp

where RId>1

 

12. 图书表(图书号,图书名,作者编号,出版社,出版日期)作者表(作者编号,作者姓名,年龄,性别)。用SQL语句查询出年龄小于平均年龄的作者名称、图书名,出版社

select WriterName,BookName,PublishingHouse from Books

inner join Writer on Books.WriterNo=Writer.WriterNo

where Writer.Age < (select avg(Age) from Writer)

   

13. 返回num最小的记录(禁止使用min,max等统计函数)

select top 1 * from TestNums

where num is not null

order by num 

 

14. 举例说下项目中视图的好处?

项目里面一般把一些业务比较复杂的东西封装在一个视图里面,比如说项目里面这个查询用到了10多张表,表与表之间的关系逻辑你都得搞清楚,后期维护的时候又要拿出来弄懂,太浪费时间了,这时候视图的作用就突袭出了 

 

15. SQLServer有哪些系统数据库?分别是干什么的?

Master,系统用的一些表、存储过程

Tempdb,临时表存放的数据库

Msdb,定时任务存放的系统数据库

Model,数据库模版,新建数据库的时候,他会把Model里面的东西拷贝一份到新的数据库里面

eg:(其实不止这些系统表,这些是比较常用的)

   

 

16. 索引有什么好处,又有何缺点?聚集索引和非聚集索引有什么区别?

索引都是为了提高查询速度的,索引一般添加到不是频繁改动的字段上。

 

索引也是占空间滴,查询速度是快了增删改可就慢咯~

聚集索引影响排序,非聚集索引不影响排序。(主键默认是聚集索引哦)

聚集索引是主键时候的排序是这个样子的:

聚集索引改成Title01

默认排序就以Title01为准了

 

17. 什么时候需要SQLServer发邮件?怎么去发邮件(只要求掌握图形化页面,命令会使用即可)?

这个应用案例很多,一般都是预警,比如异常连接的时候,或者数据库报错的时候,一般都会和定时任务联合使用。

 

发邮件相关介绍:http://www.cnblogs.com/dunitian/p/6022826.html

简单说下:

在配置之前请先把邮件的POP3之类的设置一下:

 图形化演示: 

配置名字随意取,可以用项目名。显示名称建议用版本号+服务器ip,这样出问题可以定位跟踪

   

 微软图形化的东西一般有个特点,一路下一步基本上能解决所有基础问题

勾选一下(貌似不勾选也没事)

测试一下:

发一封邮件到"我为NET狂"的官方邮件去

去看看:

命令演示:(不需要记,你又不是DBA,会用即可)

 

 

发送邮件脚本:

1
2
3
4
5
6

exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQLServer_DotNetCrazy1', --配置名称
@recipients = 'dotnetcrazy@foxmail.com', --收件名称
@body_format = 'HTML', --内容格式
@subject = '文章标题',
@body = '邮件内容'

结果:20的ip也发过来了

--相关查询

--select * from msdb.dbo.sysmail_allitems

--select * from msdb.dbo.sysmail_faileditems --失败状态的消息

--select * from msdb.dbo.sysmail_unsentitems --看未发送的消息

--select * from msdb.dbo.sysmail_sentitems --查看已发送的消息

--select * from msdb.dbo.sysmail_event_log --记录日记 

 

18. 存储过程有什么优点?又有哪些缺点?

存储过程执行效率高。1.传输的字节少响应也就快了嘛;2.存储过程创建的时候已经预编译好了,运行时直接进行执行计划,而传统的sql脚本得先生成执行计划再执行。3.SQL注入防护

 

扩展不方便,比如数据库是复合的Nosql+MSSQL,代码修改业务更方便。存储过程里面的SQL就不适合了(你SQLServer的脚本总不能和其他NoSQL的通用吧),得抽出来用代码实现。 

 

19. 数据库TestStudent中学生表用到了TestMain中的Class表。

①请查询一下TestStudent中的学生在哪个班级?

一个服务器,多个数据库

--跨数据库查询

select SId,SName,CName from [TestStudent].[dbo].[StudentInfo] as Student

inner join [TestMain].[dbo].[Class] as Class on Student.SClassId=Class.CId

go

 

--多个服务器,多个数据库

--先链接服务器

 

 

--跨数据库查询

select SId,SName,CName from [q***257691.my3w.com].[q***257691_db].[dbo].[StudentInfo] as Student

inner join [TestMain].[dbo].[Class] as Class on Student.SClassId=Class.CId

go

 

②思考一下要是我修改了TestMain的数据库名如何避免再次去批量修改SQL?

一个服务器,多个数据库

--要是我手动改了数据库名或者表名岂不歇菜?所有就有了同义词

use TestMain

if(exists(select * from sys.synonyms where name='TestMainClass'))

    drop synonym TestMainClass

create synonym TestMainClass for [TestMain].[dbo].[Class]

   

if(exists(select * from  sys.synonyms where name='TestStudentInfo'))

    drop synonym TestStudentInfo

create synonym TestStudentInfo for [TestStudent].[dbo].[StudentInfo]

   

--跨数据库查询

use TestMain

select SId,SName,CName from TestStudentInfo as Student

inner join TestMainClass as Class on Student.SClassId=Class.CId

go

 

 

--多个服务器,多个数据库

--先链接服务器,再同义词

--要是我手动改了数据库名或者表名岂不歇菜?所有就有了同义词

use TestMain

if(exists(select * from sys.synonyms where name='TestMainClass'))

    drop synonym TestMainClass

create synonym TestMainClass for [TestMain].[dbo].[Class]

   

if(exists(select * from  sys.synonyms where name='TestStudentInfo'))

    drop synonym TestStudentInfo

create synonym TestStudentInfo for [q***257691.my3w.com].[q***257691_db].[dbo].[StudentInfo]

   

--跨数据库查询

use TestMain

select SId,SName,CName from TestStudentInfo as Student

inner join TestMainClass as Class on Student.SClassId=Class.CId

go

 

20. 针对索引缺点,项目中我们一般怎么解决?

读写分离(发布订阅)

读库建立索引,写库不建立索引 

 

简单演示一下发布订阅,具体的可以自行研究:

发布:

订阅:

数据同步问题就不用你操心了

 

21. 随着业务的发展,你们数据库层面是怎么逐步处理的?(我以前在群里也系统的说过,这个主要考察你是否真正参与一次颇具规模的完整项目中,不一定长篇大论,说你知道的就行了)

先声明一些,如果有什么错误欢迎反馈,毕竟这个下面的东西都是逆天自己慢慢摸索的,并没有人指导,所以难免会出错~~~(还是先说下的好,不然有些不愿意分享的人会揪着小问题说啥误人子弟。PS:逆天宁愿别人也这样误误我,自己捣鼓说出来都是泪啊!)

 

一开始是数据量慢慢大了查询特别慢,于是在不经常修改又经常使用的列建立了索引,等差不多表里有100w左右的数据了,开始有点吃不消了,于是就有了分表技术。分表技术很多,hashcode取余,路由表等等。。。刚开始就是伪分表,也就是传说中的水平分表,还是在一个数据库里面,主要目的就是为了解决ID溢出或者单个表数据太多而导致查询太慢 

 

后来还是有点吃不消,总不能因为某个表而影响整体性能吧,于是就把这个特别影响数据库整体性能的表拎出来,放到另外的数据库里面,这个就是分库技术,把一些影响整体性能的表单独放到其他数据库里面叫做垂直分库,因为不在同一个数据库了,也就可以不放在一个盘里面了,大大化解了IO的压力。后来衍生出了垂直分表的概念(把某些分表放在其他库里面,这时候路由表的表名就得写全了)。

(扩:水平分库http://www.cnblogs.com/dunitian/p/5276431.html)

 

举了个简单的例子:

后来系统继续用继续用,发现...又不给力了,这时候是服务器瓶颈了(网络,IO,连接数,CPU,内存等硬件瓶颈),这时候所谓的本机垂直分库就意义不大了,就有了分布式的概念,分布式分布式,也就是单机变成多机器嘛,这时候sql上遇到各种问题,为了解决这些问题引入了同义词链接服务器的概念(19题考察的就是这个),这下以为没啥事情了,发现...什么情况,增删改各种慢?查询倒是还行。

 

细细研究发现,我去,是索引的问题(16,20题考察的内容)。然后借鉴MySQL的相关概念,他们天天说什么读写分离,那么我们是不是也可以走一个呢?于是就搞了多个库,2个读1个写。这时候想到一个问题!数据同步怎么办?数据怎么保证一致性?!!!

 

于是就有了发布订阅(这个里面又有两种,一种是主数据库一改变就推送给从数据库,一种是从数据库定期向主数据库发起同步请求【效率低】)这种读写分离,主数据库进行增删改,2个从数据库只用来查,只给新手读库的权限,再也不用担心他们修改不加where了~

 

后来就是业务问题了,我点,我点,我再点~我去,报了一个莫名的错误怎么办?

靠,是谁删了这条数据!怎么知道?

靠,磁盘快满了,怎么没人说?!!

不用担心==》引入数据库异常预警的功能(XEVENT+数据库发邮件)【这个是站在前人肩上的成果】

 

现在:集群怎么搞?故障转移怎么走起?逆天正在研究中........

 

如果经过上面优化而且数据库数据不算大(百G左右吧),那么可以得出个结论==》代码太烂,重构去,二期走起~

 

22. 设计题:请根据以下图设计一下商品相关的简表(不包含活动、订单、运费等)

a.    画出设计图【主要考察是否有一定的真实项目经历】

b.    写出建库建表语句(每个表数据不少于3个)【主要考察SQL基础】

   

   

   

   

   

 建库你们就自己慢慢建吧,我简单设计了一个模型:(有不合理的设计欢迎提出

 

posted @ 2016-11-08 09:30  毒逆天  阅读(6298)  评论(39编辑  收藏  举报