跟大家讲解下有关MsSqlServer语句_MySQL，相信小伙伴们对这个话题应该也很关注吧，现在就为小伙伴们说说MsSqlServer语句_MySQL，小编也收集到了有关MsSqlServer语句_MySQL的相关资料，希望大家看到了会喜欢。

--如果 成绩>100 优--如果成绩>90 良select * from TblScoreselect 英语成绩=(case when tEnglish>90 then '良' when tEnglish>100 then'优' end),数学成绩=(case when tMath>90 then '良' when tMath>100 then'优' end) from TblScore--第二个练习 1 2 3 select * from user5select 等级=(case when [level]=1 then'骨灰' when [level]=2 then'菜鸟' when [level]=3then '大神' end) from user5--第三个练习--6000 5500 4500select * from MyOrdersselect 销售员,销售总金额=SUM(销售数量*销售价格),称号=(case when SUM(销售价格*销售数量)>6000 then '金牌' when SUM(销售价格*销售数量)>5500 then '银牌' when SUM(销售价格*销售数量)>4500 then '铜牌' else '通牌'end) from MyOrdersgroup by 销售员--收入 支出select * from testselect number,收入=(case when amount>0 then amount when amount<0 then 0end),支出=(case when amount0then 0end) from test--查询所有的英语成绩 并英语的成绩>90 --子查询做select * from ( select tEnglish from TblScore ) as t where t.tEnglish>90--查询性别是男 年龄在20岁以上的select * from( select * from TblStudent where tSGender='男') as t where t.tSAge>20--1.查询出班级中所有24岁的男生(子查询)select * from ( select * from TblStudent where tSGender='男') as t where tSAge=24--2.查询出高一三班和高二二班的所有学生(子查询)select * from TblStudent where tSClassId in( select tClassId from TblClass where tClassName='高一一班' or tClassName='高二二班') --2.查出黑马一期和黑马二期的所有学生 use MyItcast select * from student select * from TblClass select * from student where TClassId in(select TClassId from TblClass where TClassName='黑马一期' or TClassName='黑马二期' )--3.查询出的总人数,男同学多少人,数学平均成绩(子查询)select 总人数=(select COUNT(*)from student) ,男同学多少人=(select COUNT(*) from student where TSGender=1),数学平均成绩=(select AVG(TblScore.TSMath) from TblScore)--9条到16条的数据select * from studentselect top 8 * from student where TSId not in(select top 8 TSId from student) ----16 到 26select top 8 * from student where TSId not in( select top 15 TSId from student)select * from studentuse nononodeleteImportantselect * from TblStudent--每页三条 查第五页的select * from (select * ,编号=ROW_NUMBER() over(order by tSid) from TblStudent ) as newTbl where newTbl.编号 between (5-1)*3+1 and 5*3--每页9条数据 查询13页的select * from (select 编号=ROW_NUMBER() over(order by tSId),* from TblStudent) as t where t.编号 between (13-1)*9+1 and 13*9select tMath,名次= ROW_NUMBER() over(order by tMath) from TblScoreselect tMath,名次=RANK() over(order by tMath) from TblScore --rank相同成绩的排名相同select * from MyOrdersselect 商品名称,行号=ROW_NUMBER() over(partition by 商品名称 order by id) from MyOrders --partition by 分区--销售员的销售总金额select * from MyOrdersselect 销售员,销售总金额=SUM(销售数量*销售价格) from MyOrders group by 销售员--2.统计每个销售员(订单)的销售金额占总销售金额的百分比。select * ,销售数量*销售价格,百分比=销售数量*销售价格*1.0/SUM(销售数量*销售价格) over(partition by 销售员 )*100from MyOrders--链接查询--查询这个学生的时候能不能把这个学生所在的班级的名字也显示出来select TblStudent.tSName,TblStudent.tSAge,TblStudent.tSGender,TblClass.tClassName from TblStudentinner join TblClasson TblStudent.tSClassId=TblClass.tClassId--查询这个学生在哪个班级，他（她）的考试成绩select TblStudent.tSName,TblStudent.tSGender,TblClass.tClassName,TblScore.tEnglish,TblScore.tMath from TblStudentinner join TblClasson TblStudent.tSClassId=TblClass.tClassIdinner join TblScoreon TblStudent.tSId=TblScore.tSId--创建视图create view vw_Stu_Cla_Sco_newViewasselect TblStudent.tSName,TblStudent.tSGender,TblClass.tClassName,TblScore.tEnglish,TblScore.tMath from TblStudentinner join TblClasson TblStudent.tSClassId=TblClass.tClassIdinner join TblScoreon TblStudent.tSId=TblScore.tSId--select * from vw_Stu_Cla_Sco_newView --查询视图drop view vw_Stu_Cla_Sco_newView --删除视图--查询年龄超过20岁的学生的姓名、年龄及所在班级select TblStudent.tSName,TblStudent.tSAge,TblClass.tClassName from TblStudent inner join TblClassonTblStudent.tSClassId=TblClass.tClassIdinner join TblScoreonTblStudent.tSId=TblScore.tSIdwhere TblStudent.tSAge>20----查询所有学生(参加及未参加考试的都算)及成绩select * from TblStudent inner join TblScoreon TblStudent.tSClassId=TblScore.tSId --参加考试的学生select TblStudent.tSName, TblScore.tMath,TblScore.tEnglish from TblStudent left join TblScoreon TblStudent.tSClassId=TblScore.tSId --参加考试的学生和没参加考试的学生select TblStudent.tSName, TblScore.tMath,TblScore.tEnglish from TblStudent left join TblScoreon TblStudent.tSClassId=TblScore.tSId where TblScore.tSId is null --没参加考试的学生--查询所有参加考试的,english分数不为null学生姓名、年龄及成绩select TblStudent.tSName, TblScore.tMath,TblScore.tEnglish from TblStudent inner join TblScoreon TblStudent.tSClassId=TblScore.tSId where TblScore.tEnglish is not null --参加考试的学生,英语成绩不为null--练习3：查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩，如果没有参加考试显示缺考,如果小于english&math60分显示不及格use nononodeleteImportantselect TblStudent.tSName,TblStudent.tSAge,英语成绩=(casewhen tEnglish is nullthen '缺考'else CONVERT(nvarchar,tEnglish) end),数学成绩=(case when tMath IS null then '缺考' else CONVERT(nvarchar,tMath) end ),是否及格=(case when tEnglish>60 and tMath>60 then '及格' else '不及格' end) from TblStudent left join TblScore on TblStudent.tSId=TblScore.tSIdselect * from TblAreaselect t.AreaId,t.AreaName,t1.AreaName from TblArea as t inner join TblArea as t1 on t.AreaPId=t1.AreaId--声明变量declare @number int ;set @number=30;print @numberselect @numberif(@number=30)begin print '好帅'endelsebegin select '真心恶心'enddeclare @avg int =0set @avg=(select AVG(tMath) from TblScore)if(@avg>60)begin select top 3 * from TblScore order by tMath descendelsebegin select top 3 * from TblScore order by tMath ascend

来源：php中文网