MsSqlServer語句
--如果 成績>100 優
--如果成績>90 良
select * from TblScore
select 英語成績=
(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 user5
select 等級=(case when [level]=1 then'骨灰' when [level]=2 then'菜鳥' when [level]=3then '大神' end) from user5
--第三個練習
--6000 5500 4500
select * from MyOrders
select 銷售員,銷售總金額=SUM(銷售數量*銷售價格),稱號=(
case
when SUM(銷售價格*銷售數量)>6000
then '金牌'
when SUM(銷售價格*銷售數量)>5500
then '銀牌'
when SUM(銷售價格*銷售數量)>4500
then '銅牌'
else
'通牌'
end
) from MyOrders
group by 銷售員
--收入 支出
select * from test
select number,收入=(
case
when amount>0
then amount
when amount<0
then 0
end
),支出=(case
when amount<0
then ABS(amount)
when amount>0
then 0
end) 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 student
select top 8 * from student where TSId not in(select top 8 TSId from student) --
--16 到 26
select top 8 * from student where TSId not in( select top 15 TSId from student)
select * from student
use nononodeleteImportant
select * 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*9
select tMath,名次= ROW_NUMBER() over(order by tMath) from TblScore
select tMath,名次=RANK() over(order by tMath) from TblScore --rank相同成績的排名相同
select * from MyOrders
select 商品名稱,行號=ROW_NUMBER() over(partition by 商品名稱 order by id) from MyOrders --partition by 分區
--銷售員的銷售總金額
select * from MyOrders
select 銷售員,銷售總金額=SUM(銷售數量*銷售價格) from MyOrders
group by 銷售員
--2.統計每個銷售員(訂單)的銷售金額占總銷售金額的百分比。
select * ,銷售數量*銷售價格,
百分比=銷售數量*銷售價格*1.0/SUM(銷售數量*銷售價格) over(partition by 銷售員 )*100
from MyOrders
--鏈接查詢
--查詢這個學生的時候能不能把這個學生所在的班級的名字也顯示出來
select TblStudent.tSName,TblStudent.tSAge,TblStudent.tSGender,TblClass.tClassName from TblStudent
inner join TblClass
on TblStudent.tSClassId=TblClass.tClassId
--查詢這個學生在哪個班級，他（她）的考試成績
select TblStudent.tSName,TblStudent.tSGender,TblClass.tClassName,TblScore.tEnglish,TblScore.tMath from TblStudent
inner join TblClass
on TblStudent.tSClassId=TblClass.tClassId
inner join TblScore
on TblStudent.tSId=TblScore.tSId
--創建視圖
create view vw_Stu_Cla_Sco_newView
as
select TblStudent.tSName,TblStudent.tSGender,TblClass.tClassName,TblScore.tEnglish,TblScore.tMath from TblStudent
inner join TblClass
on TblStudent.tSClassId=TblClass.tClassId
inner join TblScore
on 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
TblClass
on
TblStudent.tSClassId=TblClass.tClassId
inner join
TblScore
on
TblStudent.tSId=TblScore.tSId
where TblStudent.tSAge>20
--
--查詢所有學生(參加及未參加考試的都算)及成績
select * from TblStudent
inner join TblScore
on TblStudent.tSClassId=TblScore.tSId --參加考試的學生
select TblStudent.tSName, TblScore.tMath,TblScore.tEnglish from TblStudent
left join TblScore
on TblStudent.tSClassId=TblScore.tSId --參加考試的學生和沒參加考試的學生
select TblStudent.tSName, TblScore.tMath,TblScore.tEnglish from TblStudent
left join TblScore
on TblStudent.tSClassId=TblScore.tSId
where TblScore.tSId is null --沒參加考試的學生
--查詢所有參加考試的,english分數不為null學生姓名、年齡及成績
select TblStudent.tSName, TblScore.tMath,TblScore.tEnglish from TblStudent
inner join TblScore
on TblStudent.tSClassId=TblScore.tSId
where TblScore.tEnglish is not null --參加考試的學生,英語成績不為null
--練習3：查詢所有學生(參加和未參加考試)的學生姓名、年齡、成績，如果沒有參加考試顯示缺考,如果小於english&math60分顯示不及格
use nononodeleteImportant
select TblStudent.tSName,TblStudent.tSAge,英語成績=(case
when tEnglish is null
then '缺考'
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.tSId
select * from TblArea
select 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 @number
select @number
if(@number=30)
begin
print '好帥'
end
else
begin
select '真心惡心'
end
declare @avg int =0
set @avg=(select AVG(tMath) from TblScore)
if(@avg>60)
begin
select top 3 * from TblScore order by tMath desc
end
else
begin
select top 3 * from TblScore order by tMath asc
end
