sql 基础语法 alter用法和视图，透视

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

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('030102','大数据')

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