SQL常见面试题

1、SQL 常见面试题1.用一条SQL语句 查询出每门课都大于80分的学生姓名  name   kecheng   fenshu 张三     语文       81张三     数学       75李四     语文       76李四 

2、    数学       90王五     语文       81王五     数学       100王五     英语       90A: select distinct name from table&

3、#160; where  name not in (select distinct name from table where fenshu<=80)2.学生表 如下:自动编号   学号   姓名 课程编号 课程名称 分数1        2005001  张三  0001      数学    692      

4、;  2005002  李四  0001      数学    893        2005001  张三  0001      数学    69删除除了自动编号不同,其他都相同的学生冗余信息A: delete tablename where 自动编号 not in(select min(自动编号) from tab

5、lename group by 学号,姓名,课程编号,课程名称,分数)3. 表A(单位名称，单位帐号), 表B(单位编号，个人账号)列出各单位的名称，账号，以及单位的人数select A.name, A.dwzh, isnull(Ct.Quantity,'0') as Quantity from Aleft join (select dwzh, count(*) as Quantity from Bgroup by dwzh) as Cton A.dwzh = Ct.dwzh4. 股票表(股票代码，买卖类型，数量)按照股票代码列出，买的数量，卖的数量。select isnull

6、(a.StockID, b.StockID), isnull(a.S,'0'), isnull(b.B,'0') from (select StockID,sum(quantity) as S from stockswhere sType = 's'group by StockID ) afull join (select StockID,sum(quantity) as B from stockswhere sType = 'b'group by StockID ) bon a.StockID = b.StockID5. sel

7、ect * from tempT where ','+ tempT.description + ',' like '%,1,%'SQL Server 数据库的高级操作(1) 批处理(2) 变量(3) 逻辑控制(4) 函数(5) 高级查询*/(1)批处理将多条SQL语句作为一个整体去编译，生成一个执行计划，然后，执行！理解批处理的关键在于"编译"，对于由多条语句组成的一个批处理，如果在编译时，其中，有一条出现语法错误，将会导致编译失败！create table t(a int,b int)- 注释- 如果多行注释中包含了批处理的

8、标识符go- 在编译的过程中代码将会被go分割成多个部分来分批编译- 多行注释的标记将会被分隔而导致编译出错- 以下几条语句是三个非常经典的批处理- 你猜一下会添加几条记录！/*insert into t values (1,1)go*/insert into t values (2,2)go/*insert into t values (3,3)*/go- 查询看添加了几条记录select * from ttruncate table t(2)变量- 全局变量SQL Server中全局变量由系统定义、系统维护，用户一般仅可对其进行读取！- 查看SQL Server版本print versio

9、n- 服务器名称print servername- 系统错误编号insert into t values ('a','a')print errorinsert into t values ('a','a')if error = 245 print 'Error'- SQL Server 版本的语言信息print LANGUAGE- 一周的第一天从星期几算起print datefirst- CPU 执行命令所耗费时间的累加print cpu_busy- 获取最近添加的标识列的值create table tt

10、(a int identity(3, 10),b int)insert into tt (b) values (1)print identityselect * from tt- 局部变量局部变量由用户定义，仅可在同一个批处理中调用和访问declare intAge tinyintset intAge = 12print intAgedeclare strName varchar(12)select strName = 'state'print strNameselect au_lname, strName from authors(3)逻辑控制- IF条件判断declare

11、i intset i = 12if (i > 10) begin    -   print 'Dadadada!'  print 'Dadadada!' end    - else begin  print 'XiaoXiao!'  print 'XiaoXiao!' end- While循环控制declare i int;se

12、t i = 12;print ireturn;while (i < 18)begin print i; set i = i + 1; if i < 17  continue; if i > 15  break;end;- CASE 分支判断select au_lname, state, '犹他州' from authors where state = 'UT'select au_lname, state, '密西西比州' from authors w

13、here 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

14、 '加利福利亚' else state endfrom authors(4.1)系统函数- 获取指定字符串中左起第一个字符的ASC码print ascii('ABCDEF')- 根据给定的ASC码获取相应的字符print char(65)- 获取给定字符串的长度print len('abcdef')- 大小写转换print lower('ABCDEF')print upper('abcdef')- 去空格print ltrim('    abcd 

15、 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()- 获取系统时间print getdate()- 获取3天前的时间print dateadd(day, -3 , getdate()- 获取3天后的时间pr

16、int 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-

17、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 'ab

18、c' + 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 titlesprint '123' + convert(varchar(3), 123)print

19、'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(

20、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

21、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,

22、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)- 利用系统函数作为默认值约束drop table tttcreate table ttt(stu_name varchar(12),stu_birthday datetime default (getdate()

23、alter table tttadd constraint df_ttt_stu_birthday default  (getdate() for stu_birthdayinsert into ttt values ('ANiu', '2005-04-01')insert into ttt values ('ANiu', getdate()insert into ttt values ('AZhu', default)sp_help tttselect * from ttt （4.2）自定义函数select

24、title_idfrom 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 au_id, count(title_id)from

25、titleauthorgroup by au_idSELECT 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_idGROUP BY dbo.authors.au_idorder by '作品数量'- 自定义函数的引子（通过这个子查询来引入函

26、数的作用）- 子查询- 统计每个作者的作品数- 将父查询中的作者编号传入子查询- 作为查询条件利用聚合函数count统计其作品数量select au_lname,   (select count(title_id)  from titleauthor as ta  where ta.au_id = a.au_id ) as TitleCountfrom authors as aorder by TitleCount - 是否可以定义一个函数- 将作者编号作为参数统计其作品数量并将其返回select au_id, au_lname, d

27、bo.GetTitleCountByAuID(au_id) as TitleCount from authorsorder by TitleCount- 根据给定的作者编号获取其相应的作品数量create function GetTitleCountByAuID(au_id varchar(12)returns intbegin return (select count(title_id)   from titleauthor  where au_id = au_id)end - 利用函数来显示每个作者的作品数量create proc pro_

28、CalTitleCountasselect au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount from authorsorder by TitleCountgo- 执行存储过程execute pro_CalTitleCount- vb中函数定义格式function GetTitleCountByAuID(au_id as string) as integer  . GetTitleCountByAuID = ?end function- SALES 作品销售信息select * from s

29、ales- 根据书籍编号查询其销售记录（其中，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- 是否可以考虑定义一个函数根据书籍编号来计算其总销

30、售量- 然后，将其应用到任何一条包含了书籍编号的查询语句中select title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSalesfrom titlesorder by TotalSales- 定义一个函数根据书籍编号来计算其总销售量create function GetTotalSaleByTitleID(tid varchar(24)returns intbegin return(select sum(qty) from sales where title_id = tid)end- 统计书籍销量的前10位

31、- 其中，可以利用函数计算结果的别名作为排序子句的参照列select top 10 title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSalesfrom titlesorder by TotalSales desc- 根据书籍编号计算其销量排名create function GetTheRankOfTitle(id varchar(20)returns intbegin return(select count(TotalSales)   from titles   wher

32、e ToalSales >(  select TotalSales   from titles   where title_id=id)end- 根据书籍编号计算其销量排名select dbo.GetTheRankOfTitle('pc1035') from titlesselect count(title_id) + 1from titles where dbo.GetTotalSaleByTitleID(title_id) > dbo.GetTotalSaleByTitleID('pc1035&

33、#39;)- 删除函数drop function GetRankByTitleId- 根据书籍编号计算其销量排名create function GetRankByTitleId(tid varchar(24)returns intbegin return (select count(title_id) + 1  from titles   where dbo.GetTotalSaleByTitleID(title_id) > dbo.GetTotalSaleByTitleID(tid)end- 在查询语句中利用函数统计每本书的总销量和总排

34、名select title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSales, dbo.GetRankByTitleId(title_id) as TotalRankfrom titlesorder by TotalSales desc- 查看表结构sp_help titles- 查看存储过程的定义内容sp_helptext GetRankByTitleIdsp_helptext sp_helptext sp_helptext xp_cmdshell- ORDER DETAILS 订单详细信息selec

35、t * from order details select * from order details where productid = 23- 根据产品编号在订单详细信息表中统计总销售量select sum(quantity) from order details where productid = 23- 构造一个函数根据产品编号在订单详细信息表中统计总销售量create function GetTotalSaleByPID(Pid varchar(12)returns intbegin return(select sum(quantity) from order details

36、 where productid = Pid)endselect * from products- 在产品表中查询，统计每一样产品的总销量select productid, productname, dbo.GetTotalSaleByPID(productid) from products- CREATE FUNCTION LargeOrderShippers ( FreightParm money )RETURNS OrderShipperTab TABLE   (    ShipperID    

37、int,    ShipperName   nvarchar(80),    OrderID       int,    ShippedDate   datetime,    Freight       money   )ASBEGIN   INSERT OrderShipper

38、Tab        SELECT S.ShipperID, S.CompanyName,               O.OrderID, O.ShippedDate, O.Freight        FROM Shippers AS S INNER JOIN Orders AS O  

39、0;           ON S.ShipperID = O.ShipVia        WHERE O.Freight > FreightParm   RETURNENDSELECT * FROM LargeOrderShippers( $500 )- 根据作者编号计算其所得版权费create function fun_RoyalTyper ( au_id id)returns intasbeg

40、in declare rt int select rt = sum(royaltyper) from titleauthor where au_id = au_id return (rt)endgoselect top 1 au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as '版权费' from authorsorder by  dbo.fun_RoyalTyper(au_id) descgocreate function fun_MaxRoyalTyper_Au_id ()returns i

41、dasbegin  declare au_id id select au_id = au_id from authors order by  dbo.fun_RoyalTyper(au_id) return(au_id)endgoselect dbo.fun_MaxRoyalTyper_Au_id()goselect au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as '版权税'  from authorswhere au_id = dbo.fun_Ma

42、xRoyalTyper_Au_id()go(5)高级查询 select title_id, price from titles- 查找最高价格select max(price) from titles- 查找最贵书籍的价格(排序)，如果存在多本价格最贵的书，此方法将会遗漏select top 1 title_id, price from titlesorder by price desc- 查找最贵书籍的价格(子查询)select title_id, price from titleswhere price = (select max(price) from titles)- 查询指

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

44、e =  '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 = 'busine

45、ss123'select * from titles where 1 = 1 - 在订单表中寻找满足以下条件的订单编号以及相应的客户编号- 在详细订单表中存在对应的订单编号并且其中包含产品编号为23的产品- 然后将产品编号为23的产品订购量返回判断是否大于20USE northwindSELECT orderid, customeridFROM orders AS or1WHERE 20 < (SELECT quantity FROM order details AS od         

46、    WHERE or1.orderid = od.orderid              AND  ductid = 23)GOSELECT au_lname, au_fname FROM authors WHERE 100 IN  ( SELECT royaltyper FROM titleauthor  WHERE titleauthor.au_ID = authors.au

47、_id )  select authors.au_lname,authors.au_fnamefrom authors join  titleauthor on titleauthor.au_ID=authors.au_idwhere titleauthor.royaltyper =100 USE pubsSELECT au_lname, au_fnameFROM authorsWHERE au_id IN   (SELECT au_id   FROM titleauthor   WHERE title_

48、id IN      (SELECT title_id      FROM titles      WHERE type = 'popular_comp') select distinct t.type, a.au_lname, a.au_fnamefrom authors as a join titleauthor as ta on a.au_id = ta.au_id   join ti

49、tles as t on ta.title_id = t.title_idwhere t.type = 'business'- 查找类型为'business'或是'trad_cook'类型的书籍select * from titles where type = 'business'select * from titles where type = 'trad_cook'- 查找类型为'business'或是'trad_cook'类型的书籍(Or)select * from title

50、s where type = 'business' or type = 'trad_cook'- 查找类型为'business'或是'trad_cook'类型的书籍(In)select * from titles where type in ('business', 'trad_cook')- 查找来自'KS'或是'UT'的作者select au_lname, state from authors where state = 'KS'select au

51、_lname, state from authors where state = 'UT'- 查找来自'KS'或是'UT'的作者(Or)select au_lname, state from authors where state = 'UT' or state = 'KS'- 查找来自'KS'或是'UT'的作者(In)select au_lname, state from authors where state in ('UT', 'KS')sele

52、ct au_lname, state from authors where state not in ('UT', 'KS')- 查找出版了类型为'business'类型的书籍的出版社SELECT pub_id FROM titles WHERE type = 'business'SELECT pub_id,pub_nameFROM publishersWHERE pub_id IN ('1389', '0736')- 查找出版了类型为'business'类型的书籍的出版社(In和子

53、查询)SELECT pub_id,pub_nameFROM publishersWHERE pub_id IN   (SELECT pub_id   FROM titles   WHERE type = 'business') SELECT title, advanceFROM titlesWHERE advance >    (    SELECT MAX(advance)    FROM publishers INNER

54、 JOIN titles ON       titles.pub_id = publishers.pub_id    WHERE pub_name = 'Algodata Infosystems'   )SELECT title, advanceFROM titlesWHERE advance > all   (    SELECT advance    FROM publishers INNE

55、R JOIN titles ON       titles.pub_id = publishers.pub_id    WHERE pub_name = 'Algodata Infosystems' and advance is not null   )declare i intset i = 12if i < null print 'DDDDD'else print 'XXXXX'  

56、60;SELECT advance    FROM publishers INNER JOIN titles ON       titles.pub_id = publishers.pub_id    WHERE pub_name = 'Algodata Infosystems' select title_id, price from titleswhere price > all(select price from titles where type

57、= 'business')select title_id, price from titleswhere price > (select max(price) from titles where type = 'business')select title_id, price from titleswhere price > any(select price from titles where type = 'business')select title_id, price from titleswhere price > (s

58、elect 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

59、9; and state ='UT') print 'Welcome'else print 'Bye-Bye'- 筛选出'business'以及'trad_cook'类型的书籍(联合查询)select title_id, type from titles where type = 'business'unionselect title_id, type from titles where type = 'trad_cook'- 统计'business

60、9;类型的书籍的总价(联合查询)select title, price from titles where type = 'business'unionselect '合计:', sum(price) from titles where type = 'business'- 统计所有书籍的类型剔除重复(Distinct)select distinct type from titles- 作者记录的复制(Select Into)select * into au from authorsselect * from au- 查看数据表结构(Select

61、 Into并没有对数据表的约束进行复制)sp_help authorssp_help au- 分页(子查询的经典应用之一)- 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 jo

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

63、不支持在 select 的查询列表中直接使用局部变量- 比如：select top PageSize * from jobs- 那么，可以考虑对sql命令进行拼装，然后，利用系统存储过程 sp_executesql 来执行exec sp_executesql N'Select * from jobs'- 存储过程的实现- 其中，CurrentPageSize用于确定最后一页的页面大小create proc proGetJobsByPageCurrentPageSize int,PageSize int,CurrentPage intasDeclare strSql nvarch

64、ar(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'ex

65、ec sp_executesql strSqlgo- 测试exec proGetJobsByPage 2, 4, 4 (6)存储过程- 扩展存储过程- 查询系统目录下文件信息xp_cmdshell 'dir *.*'- 启动Windows系统服务xp_cmdshell 'net start iisadmin' (7)游标- 游标的五个基本操作步骤：- 声明declare cur_titles cursorfor select title, price from titles- 打开open cur_titles- 提取fetch cur_ti

66、tlesfetch next from cur_titles- 关闭close cur_titles- 释放deallocate cur_titles - 利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书- 这一段为批处理版- 与批处理版相比，存储过程版更方便调试以及代码的重用- 声明declare cur_titles cursorfor select title, price from titles- 打开open cur_titlesdeclare title varchar(80)declare price numeric(9,4)declare title_temp varchar(80)declare price_temp numeric(9,4)- 提取fetch cur_titles into title, pricefetch cur_titles into title_temp, price_tempwhile fetch_status = 0begin if price < price_temp begin  set price = price_te