SQL常见面试题 .pdf

《SQL常见面试题 .pdf》由会员分享，可在线阅读，更多相关《SQL常见面试题 .pdf（35页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL 常见面试题1.用一条 SQL 语句查询出每门课都大于80 分的学生姓名name kecheng fenshu 张三语文81 张三数学75 李四语文76 李四数学90 王五语文81 王五数学100 王五英语90 A:select distinct name from table where name not in(select distinct name from table where fenshu 10)begin -print Dadadada!print Dadadada!end -else begin print XiaoXiao!print XiaoXiao!end-Whil

2、e 循环控制declare i int;set i=12;print i return;while(i 18)begin print i;set i=i+1;if i 15 break;end;-CASE 分支判断select au_lname,state,犹他州 from authors where state=UT 名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 35 页 -select au_lname,state,密西西比州 from authors where state=MI select au_lname,state,肯塔基州 from authors where

3、state=KS select au_lname,state,case state when UT then 犹他州 when MI then 密西西比州 when KS then 肯塔基州 when CA then 加利福利亚 else state end from authors(4.1)系统函数-获取指定字符串中左起第一个字符的ASC 码print ascii(ABCDEF)-根据给定的ASC 码获取相应的字符print char(65)-获取给定字符串的长度print len(abcdef)-大小写转换print lower(ABCDEF)print upper(abcdef)-去空格

4、print ltrim(abcd dfd df )print rtrim(abcd dfd df )-求绝对值print abs(-12)-幂-3 的 2 次方print power(3,2)print power(3,3)-随机数-0-1000 之间的随机数print rand()*1000-获取圆周率print pi()名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 35 页 -获取系统时间print getdate()-获取 3 天前的时间print dateadd(day,-3,getdate()-获取 3 天后的时间print dateadd(day,3,getdate

5、()-获取 3 年前的时间print dateadd(year,-3,getdate()-获取 3 年后的时间print dateadd(year,3,getdate()-获取 3 月后的时间print dateadd(month,3,getdate()-获取 9 小时后的时间print dateadd(hour,9,getdate()-获取 9 分钟后的时间print dateadd(minute,9,getdate()-获取指定时间之间相隔多少年print datediff(year,2005-01-01,2008-01-01)-获取指定时间之间相隔多少月print datediff(mo

6、nth,2005-01-01,2008-01-01)-获取指定时间之间相隔多少天print datediff(day,2005-01-01,2008-01-01)-字符串合并print abc+def print abcder print abc+456 print abc+456-类型转换print abc+convert(varchar(10),456)select title_id,type,price from titles-字符串连接必须保证类型一致(以下语句执行将会出错)-类型转换select title_id+type+price from titles-正确select tit

7、le_id+type+convert(varchar(10),price)from titles 名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 35 页 -print 123+convert(varchar(3),123)print 123+123 print convert(varchar(12),2005-09-01,110)-获取指定时间的特定部分print year(getdate()print month(getdate()print day(getdate()-获取指定时间的特定部分print datepart(year,getdate()print datepar

8、t(month,getdate()print datepart(day,getdate()print datepart(hh,getdate()print datepart(mi,getdate()print datepart(ss,getdate()print datepart(ms,getdate()-获取指定时间的间隔部分-返回跨两个指定日期的日期和时间边界数print datediff(year,2001-01-01,2008-08-08)print datediff(month,2001-01-01,2008-08-08)print datediff(day,2001-01-01,2

9、008-08-08)print datediff(hour,2001-01-01,2008-08-08)print datediff(mi,2001-01-01,2008-08-08)print datediff(ss,2001-01-01,2008-08-08)-在向指定日期加上一段时间的基础上，返回新的datetime 值print dateadd(year,5,getdate()print dateadd(month,5,getdate()print dateadd(day,5,getdate()print dateadd(hour,5,getdate()print dateadd(mi

10、,5,getdate()print dateadd(ss,5,getdate()-其他print host_id()print host_name()print db_id(pubs)print db_name(5)名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 35 页 -利用系统函数作为默认值约束drop table ttt create table ttt(stu_name varchar(12),stu_birthday datetime default(getdate()alter table ttt add constraint df_ttt_stu_birthday

11、 default (getdate()for stu_birthday insert into ttt values(ANiu,2005-04-01)insert into ttt values(ANiu,getdate()insert into ttt values(AZhu,default)sp_help ttt select*from ttt（4.2）自定义函数select title_id from titles where type=business select stuff(title_id,1,3,ABB),type from titles where type=business

12、 select count(title_id)from titles where type=business select title_id from titles where type=business select *,count(dbo.titleauthor.title_id)FROM dbo.authors INNER JOIN dbo.titleauthor ON dbo.authors.au_id=dbo.titleauthor.au_id select au_id,count(title_id)from titleauthor group by au_id SELECT dbo

13、.authors.au_id,COUNT(dbo.titleauthor.title_id)AS 作品数量 FROM dbo.authors left outer JOIN dbo.titleauthor ON dbo.authors.au_id=dbo.titleauthor.au_id GROUP BY dbo.authors.au_id order by 作品数量 名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 35 页 -自定义函数的引子（通过这个子查询来引入函数的作用）-子查询-统计每个作者的作品数-将父查询中的作者编号传入子查询-作为查询条件利用聚合函数count 统

14、计其作品数量select au_lname,(select count(title_id)from titleauthor as ta where ta.au_id=a.au_id )as TitleCount from authors as a order by TitleCount-是否可以定义一个函数-将作者编号作为参数统计其作品数量并将其返回select au_id,au_lname,dbo.GetTitleCountByAuID(au_id)as TitleCount from authors order by TitleCount-根据给定的作者编号获取其相应的作品数量create

15、 function GetTitleCountByAuID(au_id varchar(12)returns int begin return(select count(title_id)from titleauthor where au_id=au_id)end-利用函数来显示每个作者的作品数量create proc pro_CalTitleCount as select au_id,au_lname,dbo.GetTitleCountByAuID(au_id)as TitleCount from authors order by TitleCount go-执行存储过程execute pr

16、o_CalTitleCount 名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 35 页 -vb 中函数定义格式function GetTitleCountByAuID(au_id as string)as integer .GetTitleCountByAuID=?end function-SALES 作品销售信息select*from sales-根据书籍编号查询其销售记录（其中，qty 表示销量）select*from sales where title_id=BU1032-根据书籍编号统计其总销售量（其中，qty 表示销量）select sum(qty)from sale

17、s where title_id=BU1032-利用分组语句（group by），根据书籍编号统计每本书总销售量（其中，qty 表示销量）select title_id,sum(qty)from sales group by title_id-是否可以考虑定义一个函数根据书籍编号来计算其总销售量-然后，将其应用到任何一条包含了书籍编号的查询语句中select title_id,title,dbo.GetTotalSaleByTitleID(title_id)as TotalSales from titles order by TotalSales-定义一个函数根据书籍编号来计算其总销售量cre

18、ate function GetTotalSaleByTitleID(tid varchar(24)returns int begin return(select sum(qty)from sales where title_id=tid)end-统计书籍销量的前10 位-其中，可以利用函数计算结果的别名作为排序子句的参照列select top 10 title_id,title,dbo.GetTotalSaleByTitleID(title_id)as TotalSales from titles order by TotalSales desc-根据书籍编号计算其销量排名create fu

19、nction GetTheRankOfTitle(id varchar(20)returns int begin return(select count(TotalSales)from titles 名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 35 页 -where ToalSales(select TotalSales from titles where title_id=id)end-根据书籍编号计算其销量排名select dbo.GetTheRankOfTitle(pc1035)from titles select count(title_id)+1 from tit

20、les where dbo.GetTotalSaleByTitleID(title_id)dbo.GetTotalSaleByTitleID(pc1035)-删除函数drop function GetRankByTitleId-根据书籍编号计算其销量排名create function GetRankByTitleId(tid varchar(24)returns int begin return(select count(title_id)+1 from titles where dbo.GetTotalSaleByTitleID(title_id)dbo.GetTotalSaleByTitl

21、eID(tid)end-在查询语句中利用函数统计每本书的总销量和总排名select title_id,title,dbo.GetTotalSaleByTitleID(title_id)as TotalSales,dbo.GetRankByTitleId(title_id)as TotalRank from titles order by TotalSales desc-查看表结构sp_help titles-查看存储过程的定义内容sp_helptext GetRankByTitleId sp_helptext sp_helptext sp_helptext xp_cmdshell-ORDER

22、DETAILS 订单详细信息select*from order details select*from order details where productid=23 名师资料总结-精品资料欢迎下载-名师精心整理-第 11 页，共 35 页 -根据产品编号在订单详细信息表中统计总销售量select sum(quantity)from order details where productid=23-构造一个函数根据产品编号在订单详细信息表中统计总销售量create function GetTotalSaleByPID(Pid varchar(12)returns int begin retu

23、rn(select sum(quantity)from order details where productid=Pid)end select*from products-在产品表中查询，统计每一样产品的总销量select productid,productname,dbo.GetTotalSaleByPID(productid)from products-CREATE FUNCTION LargeOrderShippers(FreightParm money)RETURNS OrderShipperTab TABLE(ShipperID int,ShipperName nvarchar(8

24、0),OrderID int,ShippedDate datetime,Freight money)AS BEGIN INSERT OrderShipperTab SELECT S.ShipperID,S.CompanyName,O.OrderID,O.ShippedDate,O.Freight FROM Shippers AS S INNER JOIN Orders AS O ON S.ShipperID=O.ShipVia WHERE O.Freight FreightParm RETURN END SELECT*FROM LargeOrderShippers($500)-根据作者编号计算

25、其所得版权费create function fun_RoyalTyper(au_id id)名师资料总结-精品资料欢迎下载-名师精心整理-第 12 页，共 35 页 -returns int as begin declare rt int select rt=sum(royaltyper)from titleauthor where au_id=au_id return(rt)end go select top 1 au_lname,au_fname,dbo.fun_RoyalTyper(au_id)as 版权费 from authors order by dbo.fun_RoyalTyper

26、(au_id)desc go create function fun_MaxRoyalTyper_Au_id()returns id as begin declare au_id id select au_id=au_id from authors order by dbo.fun_RoyalTyper(au_id)return(au_id)end go select dbo.fun_MaxRoyalTyper_Au_id()go select au_lname,au_fname,dbo.fun_RoyalTyper(au_id)as 版权税 from authors where au_id=

27、dbo.fun_MaxRoyalTyper_Au_id()go(5)高级查询select title_id,price from titles-查找最高价格select max(price)from titles-查找最贵书籍的价格(排序)，如果存在多本价格最贵的书，此方法将会遗漏select top 1 title_id,price 名师资料总结-精品资料欢迎下载-名师精心整理-第 13 页，共 35 页 -from titles order by price desc-查找最贵书籍的价格(子查询)select title_id,price from titles where price=(

28、select max(price)from titles)-查询指定出版社出版的书(连接)select p.pub_name as 出版社,t.title as 书籍名称 from publishers as p join titles as t on p.pub_id=t.pub_id where pub_name=New Moon Books-查询指定出版社出版的书(子查询)select title from titles where pub_id=(select pub_id from publishers where pub_name=New Moon Books)-查询指定出版社出版

29、的书(分开查询)select title from titles where pub_id=0736 select pub_id from publishers where pub_name=New Moon Books-重点-理解相关子查询的基础-select*from titles where type=business select*from titles where type=business123 select*from titles where 1=1-在订单表中寻找满足以下条件的订单编号以及相应的客户编号-在详细订单表中存在对应的订单编号并且其中包含产品编号为23 的产品-然后将

30、产品编号为23 的产品订购量返回判断是否大于20 USE northwind SELECT orderid,customerid FROM orders AS or1 WHERE 20 (SELECT MAX(advance)FROM publishers INNER JOIN titles ON titles.pub_id=publishers.pub_id WHERE pub_name=Algodata Infosystems)名师资料总结-精品资料欢迎下载-名师精心整理-第 16 页，共 35 页 -SELECT title,advance FROM titles WHERE advan

31、ce all(SELECT advance FROM publishers INNER JOIN titles ON titles.pub_id=publishers.pub_id WHERE pub_name=Algodata Infosystems and advance is not null)declare i int set i=12 if i all(select price from titles where type=business)select title_id,price from titles where price (select max(price)from tit

32、les where type=business)名师资料总结-精品资料欢迎下载-名师精心整理-第 17 页，共 35 页 -select title_id,price from titles where price any(select price from titles where type=business)select title_id,price from titles where price (select min(price)from titles where type=business)select price from titles where type=business if

33、 exists(select*from titles where type=123)print ZZZZZ else print BBBBB if exists(select*from authors where city=Berkeley and state=UT)print Welcome else print Bye-Bye-筛选出 business 以及 trad_cook 类型的书籍(联合查询)select title_id,type from titles where type=business union select title_id,type from titles wher

34、e type=trad_cook-统计 business 类型的书籍的总价(联合查询)select title,price from titles where type=business union select 合计:,sum(price)from titles where type=business-统计所有书籍的类型剔除重复(Distinct)select distinct type from titles-作者记录的复制(Select Into)select*into au from authors select*from au-查看数据表结构(Select Into并没有对数据表的约

35、束进行复制)sp_help authors sp_help au 名师资料总结-精品资料欢迎下载-名师精心整理-第 18 页，共 35 页 -分页(子查询的经典应用之一)-Jobs 职务信息表（pubs 数据库）-在实际项目中，显示职务信息时，而职务信息量非常庞大，可能需要将其分为若干个页面来显示-比如：每页显示4 条记录，那么，第一页将显示1，2，3，4，第二页将显示5，6，7，8。-显示所有信息SELECT*FROM jobs-显示前4 信息select top 4*from jobs-显示前8 信息select top 8*from jobs-显示前12 信息select top 12*

36、from jobs-寻找规律，每一页的信息源于前（页面大小*页码）条信息的反序结果的前页面大小条记录-比如：第二页就是前8 条记录的反序结果的前4 条select top 4*from(select top 8*from jobs)as tt order by job_id desc-当然，对于期望按升序显示查询结果的要求可以对查询结果进行再次排序select*from(select top 4*from(select top 8*from jobs)as tt order by job_id desc)as stt order by job_id-SQL 命令中不支持在select 的查询列

37、表中直接使用局部变量-比如：select top PageSize*from jobs-那么，可以考虑对sql 命令进行拼装，然后，利用系统存储过程sp_executesql 来执行exec sp_executesql NSelect*from jobs-存储过程的实现-其中，CurrentPageSize用于确定最后一页的页面大小create proc proGetJobsByPage CurrentPageSize int,PageSize int,CurrentPage int 名师资料总结-精品资料欢迎下载-名师精心整理-第 19 页，共 35 页 -as Declare strSql

38、 nvarchar(400)set strSql=select*from (select top +convert(nvarchar(4),CurrentPageSize)+*from(select top +convert(nvarchar(4),(PageSize*CurrentPage)+*from jobs)as tt order by job_id desc)as stt order by job_id exec sp_executesql strSql go-测试exec proGetJobsByPage 2,4,4(6)存储过程-扩展存储过程-查询系统目录下文件信息xp_cmds

39、hell dir*.*-启动 Windows系统服务xp_cmdshell net start iisadmin(7)游标-游标的五个基本操作步骤：-声明declare cur_titles cursor for select title,price from titles-打开open cur_titles-提取fetch cur_titles fetch next from cur_titles-关闭close cur_titles-释放deallocate cur_titles-利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书名师资料总结-精品资料欢迎下载-名师精心整理

40、-第 20 页，共 35 页 -这一段为批处理版-与批处理版相比，存储过程版更方便调试以及代码的重用-声明declare cur_titles cursor for select title,price from titles-打开open cur_titles declare title varchar(80)declare price numeric(9,4)declare title_temp varchar(80)declare price_temp numeric(9,4)-提取fetch cur_titles into title,price fetch cur_titles in

41、to title_temp,price_temp while fetch_status=0 begin if price price_temp begin set price=price_temp set title=title_temp end fetch cur_titles into title_temp,price_temp end-关闭close cur_titles-释放deallocate cur_titles-显示处理结果print 最贵的书是:+title+价格是:+convert(varchar(12),price)go-定义一个存储过程-利用游标遍历所有书籍信息，通过冒泡

42、排序法进行比较，找出最高价格的书（游标具体应用的经典）-这段存储过程的实现代码相对下面的实现方式略有不同-代码重复，但是思路更清晰create procedure pro_GetMaxTitle 名师资料总结-精品资料欢迎下载-名师精心整理-第 21 页，共 35 页 -as -声明 declare cur_titles cursor for select title,price from titles -打开 open cur_titles -存储最贵的书籍信息 declare title varchar(80)declare price numeric(9,4)-存储从游标中提取出来的书籍

43、的信息 declare title_temp varchar(80)declare price_temp numeric(9,4)-提取 fetch cur_titles into title,price -判断是否存在书籍信息 if fetch_status 0 begin print 没有书籍信息!-关闭 close cur_titles -释放 deallocate cur_titles -结束存储过程 return end fetch cur_titles into title_temp,price_temp -判断是否只存在一本书 if fetch_status 0 begin -显

44、示处理结果 print 最贵的书是:+title+价格是:+convert(varchar(12),price)-关闭名师资料总结-精品资料欢迎下载-名师精心整理-第 22 页，共 35 页 -close cur_titles -释放 deallocate cur_titles -结束存储过程 return end while fetch_status=0 begin if price price_temp begin set price=price_temp set title=title_temp end fetch cur_titles into title_temp,price_tem

45、p end -显示处理结果 print 最贵的书是:+title+价格是:+convert(varchar(12),price)-关闭 close cur_titles -释放 deallocate cur_titles go-定义一个存储过程-利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书-相对上面的实现方式，以下实现方式更简洁create procedure pro_GetMaxTitle as -声明 declare cur_titles cursor for select title,price from titles -打开 open cur_titles 名师资

46、料总结-精品资料欢迎下载-名师精心整理-第 23 页，共 35 页 -存储最贵的书籍信息 declare title varchar(80)declare price numeric(9,4)-存储从游标中提取出来的书籍的信息 declare title_temp varchar(80)declare price_temp numeric(9,4)-提取 fetch cur_titles into title,price -判断是否存在书籍信息 if fetch_status=0 begin print 没有书籍信息!goto errNoTitles end fetch cur_titles

47、into title_temp,price_temp -判断是否只存在一本书 if fetch_status=0 begin goto errOnlyOne end while fetch_status=0 begin if price y print t+convert(varchar(5),y)+销售量太低 名师资料总结-精品资料欢迎下载-名师精心整理-第 25 页，共 35 页 -else print t+convert(varchar(5),y)+销售量太高 fetch TempSales into t,y end close TempSales deallocate TempSale

48、s end else print 作者编号无效！go exec pro_sales_avg 213-46-8915/*示例A.使用简单游标和语法打开该游标时所生成的结果集包括pubs 数据库的authors 表中的所有行和列。可以更新该游标，对该游标所做的所有更新和删除均在提取中表现出来。因为没指定SCROLL 选项，FETCH NEXT 是唯一可用的提取选项。*/DECLARE authors_cursor CURSOR FOR SELECT*FROM authors OPEN authors_cursor FETCH NEXT FROM authors_cursor/*B.使用嵌套游标生成

49、报表输出下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。*/SET NOCOUNT ON DECLARE au_id varchar(11),au_fname varchar(20),au_lname varchar(40),message varchar(80),title varchar(80)PRINT-Utah Authors report-DECLARE authors_cursor CURSOR FOR SELECT au_id,au_fname,au_lname 名师资料总结-精品资料欢迎下载-名师精心整理-第 26 页，共 35 页 -FROM authors W

50、HERE state=UT ORDER BY au_id OPEN authors_cursor FETCH NEXT FROM authors_cursor INTO au_id,au_fname,au_lname WHILE FETCH_STATUS=0 BEGIN PRINT SELECT message=-Books by Author:+au_fname+au_lname PRINT message-Declare an inner cursor based -on au_id from the outer cursor.DECLARE titles_cursor CURSOR FO