JAVA面试SQL习题集.docx

SQL习题集数据库拆表原则1. 简单字段中的复合字段：解决方法：向上打通，或拆成两表2. 完全依赖：知道a就能知道b,返过来就不一定.实现：将同一个主题的东西放在一起.主题可以是一个实体、名词、概念。3. 无传递依赖。将传递依赖转为直接依赖。原因：减少插入冗余。减少增删异常。4. 当出现一对多时不能横拆，一定拆成两个表，特别的固定范围可以横拆(签到管理)5. 多对多必有中间表6. 代码表，大量重复的或枚举型可以用代码表.7. 能计算的字段不要数据定义语句DDLcreate alter drop数据控制语句DCLgrant deny revoke数据操纵语句DMLselect update insert delete一.SQL Server 2000Select 变化集锦1 查询所有字段(效率低)查询所有的老师select *from dbo.教师2字段枚举查询教师的ID，及姓名两项select 教师_ID,教师名from dbo.教师3字段取别名方法有二1）as可以省略select 教师名 as teacherfrom dbo.教师2）select teacher = 教师名from dbo.教师4.字段的可计算性1) 简单字段计算教师工资的10%select 工资*0.1from dbo.教师2)把一个检索结果作为查询字段本学校的师生比例select (select count(*)from dbo.教师)/1.0/(select count(*)from dbo.学生) f5取检索结果的前几个select top 3 教师_IDfrom dbo.教师6 取检索结果总数的百分比select top 30 percent 教师_IDfrom dbo.教师7 去掉重复记录select distinct 性别_IDfrom dbo.教师8 在聚合统计函数中统计不重复指定字段select count(distinct 系_ID )from dbo.教师9 case when then else end在seelct中的应用从教师表中查询，将性别_id是1的显示为男,是2的显示为女select (case when 性别_ID=1 then 男when 性别_ID=2 then 女end )from dbo.教师横向查询男女人数select sum(case when 性别_ID=1 then 1else 0end ) as nan, sum(case when 性别_ID=2 then 1else 0end ) as nvfrom dbo.教师 10 可以重复列出表的的字段select *,*,*from 教师11 检索结果放常量select 教师名, 出生于,出生日期from dbo.教师-From变化集锦1从一个表中检索select *from dbo.教师2 从一个检索结果中再检索即从临时表中检索select *from (select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_IDfrom dbo.教师)lin sqlserver2000中临时表通常要起别名where lin.教师_ID 、 =、 、 =、 !=、 、 =select 教师_IDfrom dbo.教师where 教师_ID1只检索表结构不要任何数据Select *From dbo.教师Where 1!=12 常规逻辑运算 not 、and 、orselect 教师_IDfrom dbo.教师where not( 教师_ID=1 and 教师_ID=1 并且 教师_ID any(select 教师_IDfrom dbo.教师认课)9. all 全部 相当于 大于最大maxselect *from dbo.教师where 教师_ID all(select 教师_IDfrom dbo.教师认课 )Group by 集锦1.group by 通常与聚合函数(avg ,sum ,count,max,min)配合使用查询出学生表中男女生人数select count(*) as renshufrom dbo.学生group by 性别_ID2针对多字段的分组每个系的男女生人数select count(*) as renshu from dbo.学生group by 性别_ID,系_ID3 多表查询中的分组应用select count(*) as rs,系名from dbo.学生 xs inner join dbo.系 xion xs.系_ID = xi.系_IDgroup by 系名4 对于计算字段的分组应用查询出计算机系学生数和其它系的学生数select count(*),(case when 系_ID=1 then jsjxelse qtend)from dbo.学生group by (case when 系_ID=1 then jsjxelse qtend)5 分组条件having的用法系人数在10人以上的系select count(*) as shu ,系_IDfrom dbo.学生group by 系_IDhaving count(*) 10或写成select *from (select count(*) as shu ,系_IDfrom dbo.学生group by 系_ID) linshiwhere linshi.shu 10order by集锦1. 对单个字段的排序(asc升序可省略 desc降序)select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_IDfrom dbo.教师order by 教师名 2.多字段的排序（先按第一个字段排序，如果相同再按第二个字段排序）写法1。select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_IDfrom dbo.教师order by 性别_ID, 系_ID desc写法2。select 教师_ID , 教师名, 出生日期, 系_ID as xxx , 职称_IDfrom dbo.教师order by 4, 性别_ID desc 4代表select 后面 第四个字段3.按笔画排序select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_IDfrom dbo.教师order by 教师名 Collate Chinese_PRC_Stroke_ci_as desc4.order by 可按给定表达式计算结果排序按教师年龄大小降序排序select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_IDfrom dbo.教师order by DATEDIFF( yy, 出生日期,getdate() ) desc将本年入学新生排在前面，老生排在后面select 学生_ID, 姓名, 出生日期, 性别_ID, 籍贯_ID, 系_ID, 届, 班级,(case when 届 = 2006 then 1else 0end) from dbo.学生order by (case when 届 = 2006 then 1else 0end) desc其它：-union 合并将本校学生与教师名字全部查询出来（去掉重复）。select 教师名from dbo.教师 unionselect 姓名from dbo.学生将本校学生与教师名字全部查询出来（不去掉重复）。select 姓名from dbo.学生 union allselect 姓名from dbo.学生常用函数-数学函数select ABS(-9)select CEILING(9.00001)select FLOOR(9.9)select PI()select POWER(2,3)select SQRT(9)select SIGN(0)select ROUND( 892.35,1,7 )select ROUND( 892.35,1,0 )-几位数select 1+FLOOR( LOG10(48091) )-随机数select FLOOR(RAND()*10)-字符串函数-select LEFT(aaaaa,2)select RIGHT(123456,2)select LEN(1)select LOWER(aaBN)select UPPER(aaBN)select rtrim( LTRIM( aaa ) )+*- trim().ltrim()select SUBSTRING(abcdefg,2,3)select REPLACE(aba,a,ccc)select STUFF( aaaaa,1,2,bbbbbb )-日期时间-select GETDATE()select YEAR( GETDATE() )select month( GETDATE() )select day( GETDATE() )select DATEADD( mm,100 ,1990-10-10 )select DATEDIFF( yy, 1988-8-8,getdate() )select 教师名 , DATEDIFF( yy, 出生日期, getdate() ) as 年龄from dbo.教师select DATENAME( yy,getdate() )+年select DATEPART( yy, getdate() )select DATENAME( yy,getdate() )+年+DATENAME( mm,getdate() )+月+ DATENAME( dd,getdate() )+日-本周/月/季/年的一 是 哪一天 select dateadd(day, 2- DATEPART(weekday,getdate() ,getdate() )SELECT DATEADD(wk, DATEDIFF(wk,0,getdate() , 0) SELECT DATEADD(mm, DATEDIFF(mm,0,getdate() , 0) SELECT DATEADD(qq, DATEDIFF(qq,0,getdate() , 0)SELECT DATEADD(yy, DATEDIFF(yy,0,getdate() , 0)-上周/月/季/年 最后一天SELECT dateadd(ms,-3,DATEADD(wk, DATEDIFF(wk,0,getdate(), 0) SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate(), 0) SELECT dateadd(ms,-3,DATEADD(qq, DATEDIFF(qq,0,getdate(), 0) SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate(), 0) -本月的第一个星期一 select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate(),getdate(), 0) -上个月 多少天-上月 最后一天declare a datetime set a= dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate(), 0)print 1+month(getdate() %12print convert(char(2),1+month(getdate() %12)+-select dateadd(mm, DATEDIFF(mm,0,getdate() ,0)SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate(), 0) -通用时间差declare a datetime set a= dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate(), 0)declare b datetimeset b= dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()-1, 0)print datediff(day, b,a);-转换函数-select cast( 666 as int )+100select convert(varchar(13),999 )+*-Integer.parseInt()select convert(char(4),year( getdate() )+-+convert(char(2),month( getdate() )+-+convert(char(2),day( getdate() ) )insert into 用法1.向教师表中加入一个记录，字段的顺序与数目和表中一致insert into 教师 values( ); 2. 字段的数目与顺序自己指定Insert into 教师 (姓名，性别_id) values ( 张,1)3 . 从select检索中插入成批记录insert into dbo.学生 ( 姓名)select 教师名from dbo.教师delete from dbo.xuesheng where ming like z%update用法1. 更新一个字段Update 教师Set 教师名=李四Where 教师_id = 3;教师基本工资上调5%Update 教师Set 基本工资=基本工资*(1.05)2. 多字段更新，每一个用 ，号隔开Update 教师Set 教师名=李四 , 性别_id = 1Where 教师_id = 3;集合论交集 条件A AND 条件Bselect from A表 where exist （B查询）SELECT语句A INTERSECT SELECT语句B并集条件A OR 条件BSELECT语句A UNION SELECT语句B补集NOT 条件A HAVING NOT 统计分组条件SELECT语句A UNION SELECT语句B差集select from A表 where not exist （B查询）-集合相等集合包含A包含B B中的元素 一定在A中不在A中的元素 一定不在B中1.用一句SQL语句获取 学生表中年龄最大的学生-1.是一句SQL语句，-2.绝对不能使用任何统计函数，如sum()，max()，min()，avg()，count()以及所有DBMS提供的函数。方法一select top 1 年龄from 学生 order by 年龄 desc 方法二select 年龄from 学生where 年龄 = all (select 年龄 from 学生)2.- 学生表中年龄第三大的学生select top 1 年龄from 学生where 年龄 not in (select top 2 年龄from 学生order by 年龄 desc) order by 年龄 desc3 分页sql4. 删除除了自动编号不同,其他都相同的学生冗余信息自动编号 学号 姓名 课程编号 课程名称 分数1 2005001 张三 0001 数学 692 2005002 李四 0001 数学 893 2005001 张三 0001 数学 69 Table aIdAB11221W31S42352261H7Q2812912101211addsadelete from tablename where 自动编号 not in(select min(自动编号) from tablename group by 学号,姓名,课程编号,课程名称,分数)5 . 有学生信息表学号,班级,姓名,学科,成绩,请写出每班语文成绩在前10名并且数学成绩不在倒数10名的学生的个人信息select 学号,班级,姓名,学科,成绩from 学生where 学号 in(select top 10 学号from 学生where 学科=语文order by 成绩 desc)and 学号 not in(select top 10 学号from 学生where 学科=数学order by 成绩 asc)6.查询出每门课都大于80分的学生姓名 name kecheng fenshu 张三 语文 81张三 数学 75李四 语文 76李四 数学 90王五 语文 81王五 数学 100王五 英语 90select distinct name from table where name not in (select distinct name from table where fenshu807.-查询到少选修了学生95002选修的全部课程的学生号码select distinct 学生号码from 成绩 scxwhere not exists ( select * from成绩scy where scy. 学生号码=95002 andnot exists( select * from sc sczwhere scz. 学生号码=scx. 学生号码 andscz. 课程号=scy.课程号)select distinct 学生_IDfrom dbo.学生选课 scxwhere not exists ( select * from dbo.学生选课 scywhere scy.学生_ID=1 andnot exists( select * from dbo.学生选课 sczwhere scz.学生_ID=scx.学生_ID andscz.学生_ID=scy.学生_ID)8.-查询选修所有全部课程的学生姓名-查询学生姓名,条件：没有 1门课不选select 学生名from 学生where not exists(select * from 课程where not exists (select * from 学生选课where 学生选课.学生_ID=学生.学生_ID and 学生选课.课程_ID=课程.课程_ID)9.查询所有选修了1号课程的学生姓名select 学生名from 学生where exists (select *from 学生选课where 学生选课.学生_ID=学生.学生_ID and 课程_ID=1)10. -2005年选了郭德刚课程的学生名单select distinct 学生名from dbo.学生where 学生_ID in(select 学生_ID from dbo.学生选课where 年度=2005 and 课程_ID in(select 课程_IDfrom dbo.教师任课where 年度=2005 and 教师_ID=(select 教师_IDfrom dbo.教师where 教师姓名=郭德刚)11.-2006年，选了 男老师课程 成绩不及格的男学生select distinct 学生名from 学生where 性别_ID=1 and 学生_ID in(select distinct 学生_ID from 学生选课where 成绩60 and 年度=2006 and 课程_ID in(select distinct 课程_IDfrom 教师任课where 年度=2006 and 教师_ID in(select 教师_IDfrom 教师where 性别_ID=1)12. -每科 都及格的学生-变换：没有一科 不及格的学生select 学生_ID,学生名from 学生where 学生_ID not in(select distinct 学生_ID from 学生选课where 成绩60)13.张三丰教过的 姓张的物理系的学生select 学生_ID, 姓名, 出生日期, 性别_ID, 籍贯_ID, 系_ID, 届, 班级from dbo.学生 where 姓名 like 张% and 系_ID in(select 系_IDfrom dbo.系where 系名 like %物理系%)and 学生_ID in(select 学生_IDfrom dbo.学生选课where 开课_ID in(select 开课_IDfrom dbo.教师认课where 教师_ID in(select 教师_IDfrom dbo.教师where 教师名=张三丰)14.(2003年) 在(911) 教室上过课的老师select 教师名from dbo.教师where 教师_ID in(select 教师_IDfrom dbo.教师认课where 开课_ID in(select 开课_IDfrom dbo.开课where 年度=2003 ) and 开课_ID in(select 开课_IDfrom dbo.课时安排where 教室号=911)15.-(女老师)教过的(不及格)的(男)学生？select 姓名from dbo.学生where 性别_ID in(select 性别_IDfrom dbo.代码_性别where 性别名=男)and 学生_ID in(select 学生_IDfrom dbo.学生选课where 成绩60)and 学生_ID in(select 学生_IDfrom dbo.学生选课where 开课_ID in(select 开课_IDfrom dbo.教师认课where 教师_ID in(select 教师_IDfrom dbo.教师where 性别_ID in(select 性别_IDfrom dbo.代码_性别where 性别名=女)16-(毕业年级)中学分(不够)(12学分) 的学生？select 姓名from dbo.学生where 届=year(getdate()-3 and 学生_ID in(select 学生_IDfrom dbo.学生选课 a,dbo.课程 b, dbo.开课 cwhere a.开课_ID = c.开课_ID andb.课程_ID= c.课程_IDgroup by 学生_IDhaving sum(学分)=2)and 系_ID in(select 系_IDfrom dbo.系where 系名 like %计算机%)18.本校中老师或学生重名的名字select *from(select 姓名from dbo.学生union allselect 教师名from dbo.教师) lin group by lin.姓名having count(*)=219 05年开课数最少的系？（用临时表）select 系名from dbo.系 w,(select top 1 系_ID,count(*) as shufrom dbo.课程 a,dbo.开课 bwhere a.课程_ID=b.课程_ID and 年度=2005group by 系_IDorder by shu asc)linwhere w.系_ID = lin.系_ID20.各系中教师学生比例最低的select w.系_ID, 系名from dbo.系 w,(select top 1 l1.系_ID , l1.lss/1.0/xss as bzfrom (select 系_ID, count(*) as lssfrom dbo.教师group by 系_ID)l1,(select 系_ID, count(*) xssfrom dbo.学生group by 系_ID)l2where l1.系_ID=l2.系_IDorder by bz ) lin where w.系_ID = lin.系_ID21.全都及格了的学生select 学生_ID, 姓名, 出生日期, 性别_ID, 籍贯_ID, 系_ID, 届, 班级from dbo.学生where 学生_IDin(select 学生_IDfrom dbo.学生选课group by 学生_IDhaving min(成绩)=60)22.求选了跟李宇春课程数量 相同 的学生？select 姓名from dbo.学生where 学生_ID in(select 学生_IDfrom dbo.学生选课group by 学生_IDhaving count(*)= (select shufrom (select 学生_ID,count(*) shufrom dbo.学生选课group by 学生_ID)linwhere 学生_ID = (select 学生_IDfrom dbo.学生where 姓名 = 李宇春 )and 姓名 != 李宇春 23 与java 学时 相同的 课程select 课程_ID, 课程名, 学分, 学时, 系_ID, 年级, 学期from dbo.课程where 学时 = (select 学时from dbo.课程where 课程名=java)and 课程名!=javaselect b.*from dbo.课程 a , dbo.课程 bwhere a.学时=b.学时and a.课程名=java and b.课程名!=java24课时安排冲突的记录select distinct a.开课_IDfrom dbo.课时安排 a,dbo.课时安排 bwhere a.星期=b.星期 and a.节=b.节and a.教室号=b.教室号and a.开课_ID!=b.开课_ID25 -张无忌 老师都开过什么课了select 课程_ID,课程名from dbo.课程where 课程_ID in(select 课程_IDfrom dbo.开课where 开课_ID in(select 开课_IDfrom dbo.教师认课where 教师_ID in(select 教师_IDfrom dbo.教师where 教师名=张无忌 )select 课程_ID,课程名from dbo.课程,dbo.开课,dbo.教师认课,dbo.教师where dbo.课程. 课程_ID= dbo.开课.课程_IDand dbo.开课.开课_ID=dbo.教师认课.开课_IDand dbo.教师认课.教师_ID=dbo.教师.教师_IDand dbo.教师.教师名=张无忌26-平均分比 那英 高的学生名单-查名单 平均分 那英的平均分 select 姓名from dbo.学生where 学生_ID in(select 学生_IDfrom dbo.学生选课group by 学生_IDhaving avg(成绩)(select avg(成绩)from dbo.学生选课where 学生_ID=(select 学生_IDfrom dbo.学生where 姓名=那英)27 -本年度选课 少于3门的学生select 学生_ID, 姓名from dbo.学生where 学生_ID in(select 学生_IDfrom dbo.学生选课where 开课_ID in(select 开课_IDfrom dbo.开课where 年度=year(getdate()group by 学生_IDhaving count(*) 3)28 -全及格了的学生select *from dbo.学生 where 学生_ID not in(select 学生_IDfrom dbo.学生选课where 成绩all(select datediff(yy,出生日期,getdate()from dbo.教师where 系_ID=(select 系_IDfrom dbo.系where 系名=物理系)and 系_ID=(select 系_IDfrom dbo.系where 系名=外语系)31. -java前5名，VC后5名 的学生select *from dbo.学生where 学生_ID in(select top 5 学生_IDfrom dbo.课程 a,dbo.开课 b,dbo.学生选课 cwhere c.开课_ID=b.开课_ID and b.课程_ID=a.课程_IDand 课程名=javaorder by 成绩 desc )and 学生_ID in(select top 5 学生_IDfrom dbo.课程 a,dbo.开课 b,dbo.学生选课 cwhere c.开课_ID=b.开课_ID and b.课程_ID=a.课程_IDand 课程名=vcorder by 成绩 asc )32.-2006年选了课的学生select *from dbo.学生where 学生_ID in(select 学生_IDfrom dbo.学生选课 a,dbo.开课 bwhere a.开课_ID=b.开课_ID)select *from dbo.学生 xwhere exists(select *from dbo.学生选课 a,dbo.开课 bwhere a.开