数据库

--1、 查询Student表中的所有记录的Sname、Ssex和Class列。select Sname,Ssex,class from Student--2、 查询教师所有的单位即不重复的Depart列。select distinct depart from teacher--3、 查询Student表的所有记录。select *from student--4、 查询Score表中成绩在60到80之间的所有记录。select *from Score where degree between 60 and 80--5、 查询Score表中成绩为85，86或88的记录。select *from Score where degree in (85,86,88)--6、 查询Student表中“95031”班或性别为“女”的同学记录。select *from student where class='95031' or ssex='女'--7、 以Class降序查询Student表的所有记录。select *From student order by class desc--8、 以Cno升序、Degree降序查询Score表的所有记录。select *from score order by Cno,Degree desc--9、 查询“95031”班的学生人数。--聚合函数：针对数据列，计算求和或者计数等一系列算术性操作select count(*) from student where class='95031'--sum(),avg(),max(),min()select MAX(Degree) as maxfen,MIN(Degree) minfen from Score where Cno='3-105'--10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）select *from score where Degree = (select MAX(degree) from score)select top 1 *from Score order by Degree desc--11、 查询每门课的平均成绩。--当分组和聚合结合的时候,先分组,然后对每一组分别进行聚合select AVG(degree) as 平均成绩,cno from Score group by cno--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。select cno,avg(degree) as degreess from score where Cno like '3%' group by cno having COUNT(*)>=5 order by degreess--13、查询分数大于70，小于90的Sno列。select sno from Score where degree>70 and degree (select Degree from Score where Cno='3-105' and Sno='107')

--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。1.查询学号出现两次及以上的学号出来select sno from Score group by Sno having COUNT(*)>=22.查询步骤1中的学号的人的分数select *from Score where Sno in (select sno from Score group by Sno having COUNT(*)>=2)3.查询所有人的分数最高分出来select max(degree) from Score where Sno in (select sno from Score group by Sno having COUNT(*)>=2)4.从步骤2的结果中剔除最高分即步骤3的select *from Score where Sno in (select sno from Score group by Sno having COUNT(*)>=2) and Degree !=( select max(degree) from Score  ) --这个是剔除选多门课的所有人的分数最高分select *from Score where Sno IN (select Sno from Score GROUP by sno having COUNT(*)>1)and  Degree not In(select max(Degree)from Score where Sno IN (select Sno from Score GROUP by sno having COUNT(*)>1))

--剔除选多门课的每门课最高分select *from Score a where Degree not IN (select MAX(degree) from Score b where b.Cno=a.Cno) and Sno in (select Sno from Score group by Sno having COUNT(*)>1)

--查询除了每门课最高分学生之外的其他学生分数信息select *from Score a where Degree not IN (select MAX(degree) from Score b where b.Cno=a.Cno)

--21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。select *from Score where Degree > (select Degree from Score where Cno='3-105' and Sno='109')

--22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。select sno, sname, sbirthday from student where YEAR(Sbirthday) = (select year(Sbirthday) from Student where Sno='109')

--时间日期函数：    YEAR(时间日期列)   ： 取年份    month():取月份值    day():取日期值    dayofyearselect *from Student where year(Sbirthday) > 1976select sno,sname,dayofyear(sbirthday) as 年份,sbirthday from student

select dateadd(weekday ,1,'1999-03-22')print datediff(day,'2016-2-22','2015-4-23')print datename(weekday,'2015-4-23')print datepart(weekday,'2015-4-23')

print isdate('1999-2-3')print sysdatetime()select  getdate()

--23、查询“张旭“教师任课的学生成绩。select *from score where Cno in (select Cno from Course where Tno in  (select Tno from Teacher where Tname='张旭'))

--24、查询选修某课程的同学人数多于5人的教师姓名。select tname from Teacher where Tno in (select Tno from Course where Cno in (select Cno from Score group by Cno having COUNT(*)>5))

--25、查询95033班和95031班全体学生的记录。select *from student where class in ('95033','95031')select *from Score where Sno in (select sno from student where class in ('95033','95031'))select * from Student,Score where class in ('95033','95031') and Student.Sno=Score.sno--26、  查询存在有85分以上成绩的课程Cno.select distinct cno from Score where Degree>=85

--27、查询出“计算机系“教师所教课程的成绩表。select *from score where cno in (select cno from course where tno in (select tno from teacher where depart='计算机系'))

--28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。select tname,prof from teacher where prof not in (select prof from teacher where depart = '电子工程系' and prof in(select prof from teacher where depart = '计算机系') ) and depart in ('计算机系','电子工程系')

--查询两个系中相同职称的职称名称select prof from teacher where depart = '电子工程系' and prof in(select prof from teacher where depart = '计算机系')

select tname,prof from Teacher a where Prof not in (select Prof from Teacher b where a.Prof=b.Prof and a.Depart='计算机系' and b.Depart='电子工程系')

--29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。select *from score where cno='1-101' and degree>(select MIN(degree) from score where cno='1-102') order by degree descselect *from score where cno='1-101' and degree >any(select degree from score where cno='1-102')

--30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.select *from score where cno='1-101' and degree >all(select degree from score where cno='1-102')

--31、 查询所有教师和同学的name、sex和birthday.select sname,ssex,sbirthday from studentunionselect tname,tsex,tbirthday from teacher--注意:列数要对应,列的类型要按先后顺序对应

--32、查询所有“女”教师和“女”同学的name、sex和birthday.select sname,ssex,sbirthday from student where ssex='女'unionselect tname,tsex,tbirthday from teacher where tsex='女'

--33、 查询成绩比该课程平均成绩低的同学的成绩表。--相关子查询select * from score a where a.Degree=2

--37、查询Student表中不姓“王”的同学记录。select *from student where sname not like '王%'

--38、查询Student表中每个学生的姓名和年龄。select sname,YEAR(GETDATE())-year(sbirthday) as 年龄 from student

--39、查询Student表中最大和最小的Sbirthday日期值。select MAX(sbirthday) ,MIN(sbirthday) from student

--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。select *from student order by class desc,sbirthday asc--41、查询“男”教师及其所上的课程。select *from course join teacher on course.tno=teacher.tno where tsex='男'

--42、查询最高分同学的Sno、Cno和Degree列。select *from score where degree = (select MAX(degree) from score)

--43、查询和“李军”同性别的所有同学的Sname.select sname from student where ssex in (select ssex from student where sname='李军')

--44、查询和“李军”同性别并同班的同学Sname.select sname from student where ssex in (select ssex from student where sname='李军') and class in (select class from student where sname='李军')--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。select *from score where sno in (select sno from student where ssex = '男') andcno in (select cno from course where cname='计算机基础')

select *From studentselect *From teacherselect *From scoreselect *From courseupdate teacher set depart='计算机系' where tno='888'

--处理字符串，以及类型转换

--字符串函数select LEFT('abcdef',2)--从左往右截取固定长度select RIGHT('abcdef',2)--从右往左截取select LOWER('Abc')--转换为小写select UPPER('acc')--转换为大写select LEN('qweqwe')--字符串长度intprint LTRIM('    asd  daa                    ')--去除左空格print LTRIM('    asd  daa')print rTRIM('    asd  daa                    ')--去除右边空格print LTRIM('    asd  daa                    ')print substring('abccccddd',1,2)--从起始位置开始截取固定长度,索引从1开始print replace('asdaddsdadfasf','as','12312')--替换print replicate('asd',10)print str(123.456,5,2)print space(100)print 'aasdfas'+'adfdsaf'print  reverse('abcde')print patindex('%abc%','123abc123')print stuff('abcdefghijklmnopqrst',4,0,'zzzz')--类型转换函数print cast('123' as int)+12--类型转换print cast(123 as varchar(20))+'123'print convert(int,'123')+13--数学函数print abs(-13)--绝对值print ceiling(1.23)--天花板，取上限print floor(1.9)--地板print pi()print rand(10)--随机种子print round(1.234567,2)--四舍五入print sqrt(4)--根号print square(4)--平方

select sname ,'出生日期为'+CAST( YEAR(sbirthday) as varchar(20))+'年'+convert(varchar(20),MONTH(sbirthday))+'月'+cast(DAY(sbirthday) as varchar(20))+'日'  as 出生日期 from student