首页 > 代码库 > NET
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(*)==》不要麻木相信优化，自己证实后再说~
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 |
exec msdb.dbo.sp_send_dbmail |
结果：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基础】
建库你们就自己慢慢建吧，我简单设计了一个模型：（有不合理的设计欢迎提出）
NET