SQL常见面试题 .pdf

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-While 循环控制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 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)-去空格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()-获取 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(month,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 title_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 datepart(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,2008-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,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 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 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.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 统计其作品数量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 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 pro_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 sales 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-定义一个函数根据书籍编号来计算其总销售量create 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 function 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 titles 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.GetTotalSaleByTitleID(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 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 return(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(80),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)-根据作者编号计算其所得版权费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(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=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=(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)-查询指定出版社出版的书(分开查询)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 的产品-然后将产品编号为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 advance 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 titles 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 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 where 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并没有对数据表的约束进行复制)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*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 的查询列表中直接使用局部变量-比如：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 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_cmdshell 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-利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书名师资料总结-精品资料欢迎下载-名师精心整理-第 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 into 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-定义一个存储过程-利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书（游标具体应用的经典）-这段存储过程的实现代码相对下面的实现方式略有不同-代码重复，但是思路更清晰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)-存储从游标中提取出来的书籍的信息 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 -显示处理结果 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_temp 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 名师资料总结-精品资料欢迎下载-名师精心整理-第 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 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 TempSales 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.使用嵌套游标生成报表输出下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。*/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 WHERE 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