--查询没有被删除的学生

alter table StuInfo --修改列属性

alter column isdelete bit null 

alter table StuInfo --删除列

drop column isdelete

alter table StuInfo --增加列

add isdelete bit not null default(0)

select * from StuInfo as si

inner join Classinfo as ci on si.classId=ci.classId

where isdelete=0

--创建视图，用于二次查询

create view StuInfo_Class

as

select si.*,ci.className from StuInfo as si

inner join Classinfo as ci on si.classId=ci.classId

where isdelete=0

--视图中存储的是select语句，而不是结果集合

select * from StuInfo_Class

drop view StuInfo_Class -- 删除

select * from StuInfo

select * from ScoreInfo

--查询参与了考试的学生信息,子查询exists,in

select * from StuInfo

where stuId in(select distinct stuId from ScoreInfo)

select * from StuInfo as si

where exists

(select * from ScoreInfo as sco where si.stuId=sco.StuId)

--分页 已知：页大小（一页显示多少数据），页索引

--              3                        1，2，3，4

--利用子查询进行分页：

select * from

(

    select *,

    ROW_NUMBER()over(order by stuPhone asc) as rowIndex

    from StuInfo

) as t1

where rowIndex between 1 and 3 --先排序后再查询在一个区间的列

--当前学生的科目成绩

create view S_S_C

as

select si.*,ci.cName,sco.score from StuInfo as si

inner join ScoreInfo as sco on si.stuId=sco.StuId

inner join CourseInfo as ci on ci.cId=sco.cId

select * from S_S_C

--数据透视

--要求按格式：姓名 数据库 算法设计

select stuName as 姓名,

max(case cName when '数据库' then score end) as 数据库, --case..when..then语句的两种写法

max(case when cName='算法设计' then score end) as 算法设计

from S_S_C

--然后还要用聚合函数max|sum将上面的结果合并起来,去掉null的位置

group by stuName

select * from course

select * from score

select * from student

select * from teacher

--[20,25]岁男同学的姓名，性别，年龄

create view student_view

as

select stuName,stuAge,stuSexy from student

where stuAge between 20 and 25

select * from student_view

--每位同学姓名，课程名称和成绩

create view score_view

as

select si.stuName,ci.cName,sco.score from student as si

inner join score as sco on si.stuId=sco.stuId

inner join course as ci on sco.cId=ci.cId

select * from score_view

select * from class

insert into class

values('','大数据')

select * from course

select * from student

select * from teacher

select * from score

--透视练习：按课程名字 男生 女生

create view Cou_Stu

as

select si.stuId,ci.cName,si.stuSexy from student as si

inner join score as sco on si.stuId=sco.stuId

inner join course as ci on sco.cId=ci.cId

drop view Cou_Stu

select * from Cou_Stu

create view TMP

as

select cName,stuSexy,COUNT(*)as num from Cou_Stu

group by stuSexy,cName

select * from TMP

select cName,

(case when stuSexy=1 then num end)as '男生',

(case when stuSexy=0 then num end)as '女生'

from TMP

--再通过分组+聚合函数把null给去了

select cName,

max(case when stuSexy=1 then num end)as '男生',

max(case when stuSexy=0 then num end)as '女生'

from TMP

group by cName