SQL基础3-子查询

--执行插入语句返回刚刚生成的自动编号
insert into TblClass output inserted.ClsId values('大一一班','11',18)

------------CASE函数用法------------
--相当于switch 注意then后面的数据类型要一样
select * from Tblscore
select 
tScoreid,
tenglish,
评分=
case
    when tenglish>=95 then '优秀'
    when tenglish>=90 then '良好'
    when tenglish>80 then '优'
    when tenglish is null then '101'
    else '乔布斯'
end
from TblScore

--等值判断
select 
tScoreid,
tenglish,
评分=
case tenglish
    when 95 then '优秀'
    when 90 then '良好'
    when 80 then '优'
    when  null then '101'
    else '乔布斯'
end
from TblScore



--聚集索引（聚簇索引）：
-------当数据实际的存储顺序，与索引的顺序一致就把该索引叫聚集索引
--非聚集索引（非聚簇索引）
-------当索引中数据的顺序与，数据实际存储的顺序不一致的时候，该索引叫非聚集索引。
=======非聚集索引=============
--在表Sales.SalesPerson中给SalesQuota, SalesYTD这两列创建非聚集索引
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD); GO 
====创建唯一非聚集索引=============
CREATE UNIQUE INDEX AK_UnitMeasure_Name ON Production.UnitMeasure(Name); GO 
=======创建聚集索引=================
CREATE TABLE t1 (a int, b int, c AS a/b); 
--创建唯一的聚集索引
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c); INSERT INTO t1 VALUES (1, 0);
--删除索引
drop index T8.IX_T8_tage



----------------子查询--------------------
--把一个查询结果作为另一个查询的查询源

select * from 
(select fname,fage,fgender from MyStudent
where fage between 18 and 24 and fgender='女') as tbl
where fname like '赵%'

--把另外一个查询的结果作为当前查询的where条件来使用。


select * from tblstudent where tsclassid=
(select tclassid from tblclass where tclassname='高二二班')

---exists-----
--如果exists包含了的查询，能查到结果，就返回true,否则返回false
if(exists(select * from tblstudent where tsid<>1))
begin
  print '有数据'
end
else
begin
   print '无查询结果'
end


--查询所有'高二二班'与'高二一班'的学生的信息
--子查询中=、!=、<、<=、>、>= 之后只能返回单个值，如果多个值就报错了。

--这个写法是错误的
select * from tblstudent where tsclassid=
(
select tclassid from tblclass where tclassname='高二二班' or tclassname='高二一班'
)
---这个写法是正确的
select * from tblstudent where tsclassid in
(
select tclassid from tblclass where tclassname='高二二班' or tclassname='高二一班'
)


------------------分页----------------
------desc   降序排序从高到底  asc升序排序（默认）

-------第一种分页----------------
-----每页5条数据，找第二页
select top 5 * from tblstudent where tsid not in
(
select top((2-1)*5) tsid from tblstudent order by tsid
) order by tsid

---第二种分页--
select * from
(
select *, row_number() over(order by tsid) as number from tblstudent
) as T
 where T.number between 6 and 10
 
 
 
 --开窗函数与聚合函数一起使用，可以让聚合函数对每一条数据都计算一次。
select * ,count(*) over() as '总条数' from tblstudent 


--------------连接join-----------------
--案例3：查询学生姓名、年龄、班级及成绩
select 
ts.tsname as '学生姓名',
ts.tsage as '年龄',
tc.tclassname '班级',
tb.tenglish '英语成绩',
tb.tmath '数学成绩'

from tblstudent as ts
inner join tblscore as tb on ts.tsid=tb.tsid
inner join tblclass as tc on tc.tclassid=ts.tsclassid


--请查询出所有没有参加考试（在成绩表中不存在的学生）的学生的姓名。
select ts.tsname
from tblstudent as ts
left join tblscore as tb on ts.tsid=tb.tsid
where tb.tenglish is null and tb.tmath is null