sql练习

use master
create database Test
go
--------------
use Test
--学生表
if exists (select *from sysobjects where name='Students')
drop table Students,Score
go
create table Students(
stuId int primary key ,
stuName nvarchar(50) not null,
sex nvarchar(50) not null ,
className nvarchar(max) not null,
age int check(len(age)>0)
)
go
--成绩表
create table Score(
stuId int references Students(stuId) not null,--外键约束
sub nvarchar(50) not null,
sco int not null 
)
go

--删除外键约束
use
Test
go
alter table Score
drop constraint fk_PerOrders
-----插入数据
--Students表
insert into Students(stuId,stuName,sex,className,age) values(001,'张静','女','一班',20)
insert into Students(stuId,stuName,sex,className,age) values(002,'王伟','男','一班',19)
insert into Students(stuId,stuName,sex,className,age) values(003,'张三','男','二班',18)
insert into Students(stuId,stuName,sex,className,age) values(004,'王武','男','一班',19)
insert into Students(stuId,stuName,sex,className,age) values(005,'谢文','男','一班',21)
insert into Students(stuId,stuName,sex,className,age) values(006,'叶问','男','一班',22)
insert into Students(stuId,stuName,sex,className,age) values(007,'张强','男','一班',20)
insert into Students(stuId,stuName,sex,className,age) values(008,'李丽','女','二班',18)
--Score表
insert into Score(stuId,sub,sco)values(001,'语文',70)
insert into Score(stuId,sub,sco)values(001,'数学',84)
insert into Score(stuId,sub,sco)values(002,'数学',88)
insert into Score(stuId,sub,sco)values(003,'语文',92)
insert into Score(stuId,sub,sco)values(003,'数学',98)
insert into Score(stuId,sub,sco)values(004,'语文',86)
insert into Score(stuId,sub,sco)values(005,'数学',67)
insert into Score(stuId,sub,sco)values(006,'语文',89)
insert into Score(stuId,sub,sco)values(006,'数学',94)
insert into Score(stuId,sub,sco)values(007,'语文',93)
insert into Score(stuId,sub,sco)values(007,'数学',78)
insert into Score(stuId,sub,sco)values(008,'语文',69)
insert into Score(stuId,sub,sco)values(008,'数学',100)
insert into Score(stuId,sub,sco)values(004,'数学',99)
--练习三
--1.删除学会为005的学生和学生成绩
delete from Score where stuId=5
delete from Students where stuId=5
--2.更新学号为001语文的成绩为78
update Score set sco=78 where stuId=1
--3.查询一班男生的成绩
select stu.stuId,stu.stuName,stu.sex,stu.className,sco.sco
from Students as stu,Score as sco
where stu.stuId=sco.stuId and stu.className='一班' and stu.sex='男'
--4.查询一班语文科目成绩，并通过成绩降序
select stu.stuId,stu.stuName,sco.sco,sco.sub
from Students as stu
inner join Score as sco
on stu.stuId=sco.stuId
where stu.className='一班'
order by sco.sco desc
--5.查询语文成绩在90-100之间
select *from Students
inner join Score
on Students.stuId=Score.stuId
where score.sub='语文' and Score.sco>=90
--6.通过班级和科目分组，查询平均分和总分
select stu.className, sco.sub, avg(sco.sco)as 平均分,sum(sco.sco) as 总分
from Students as stu
inner join Score as sco
on stu.stuId=sco.stuId
group by stu.classname,sco.sub

重点是group by分组要注意 

当查询中存在group by子句时，select列表（或是having子句）中只能存在分组函数，或是出现在group by子句中的字段。

group by 字句也和where条件语句结合在一起使用。当结合在一起时，where在前，group by 在后。即先对select xx from xx的记录集合用where进行筛选，然后再使用group by 对筛选后的结果进行分组 使用having字句对分组后的结果进行筛选。
即

1. having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)。

2. where用在group by 之前，即先筛选后分组。

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

练习2

1. 创建存储过程：
   1. 实现可以通过班级查询不同科目的平均分，并统计该班级男生数量
2. 创建函数：
   1. 标量：实现可以通过班级、性别查询总人数
   2. 表值：实现可以通过科目，查询该科目下不同性别学生的总分和平均分

use Test
go
----标量函数
--实现可以通过班级、性别查询总人数
if exists(select *from sysobjects where name='PCount')--判断是否存在 ，这里函数名不能加dbo.前缀，加了就找不到了
drop function dbo.PCount
go
--创建函数
create function Fun_PCount(
	@className nvarchar(max),--班级参数
	@sex nvarchar(50)--性别参数
)
returns int--返回值的数据类型
as 
begin 
	declare @pCount int;
	select @pCount=count(*) from Students as stu where stu.className=@className and stu.sex=@sex
	return @pCount;
end
go
--调用标量函数
select dbo.Fun_PCount('一班','男') as 总人数;
go

--表值函数
--实现可以通过科目，查询该科目下不同性别学生的总分和平均分
create function Fun_Score(@subject nvarchar(50))
returns table --返回类型为表
as return(
	select sco.sub,stu.sex, sum(sco.sco)as 总分,avg(sco.sco)as 平均分 from Students as stu inner join Score as sco on stu.stuId=sco.stuId group by sco.sub,stu.sex having sco.sub=@subject
)
go
--调用表值函数
select*from dbo.Fun_Score('数学')