sql完整数据库操作、存储过程、登录判断,增删改查

create database AA
go

use AA
go

create table Student
(
sid int primary key,
sname nvarchar(20),
sex nvarchar(20),
birthday datetime,
class nvarchar(10),
pwd nvarchar(10)
)
go

create table Course
(
cid int identity(1,1) primary key,
cname nvarchar(20)
)
go

create table Score
(
sid int,
cid int,
score int,
primary key(sid,cid)
)
go


insert into Course values('C#')
insert into Course values('English')


insert into Student values(1001,'张三','男', '1990-1-12','一班','123')
insert into Student values(1002,'李四','女', '1990-4-20','一班','456')
insert into Student values(1003,'王五','男', '1991-10-11','二班','789')
insert into Student values(1004,'赵六','男', '1992-8-5','二班','101')
insert into Student values(1005,'天齐','男', '1992-5-5','三班','120')


insert into Score values(1001,1,65)
insert into Score values(1001,2,60)
insert into Score values(1002,1,50)
insert into Score values(1002,2,40)
insert into Score values(1003,1,75)
insert into Score values(1003,2,60)
insert into Score values(1004,1,72)
insert into Score values(1004,2,45)



select * from student
select sname from student
select sid,sname from student
select * from student order by sid desc --desc 降序 asc 升序
select * from student order by sex,class asc
select * from student order by birthday desc
select * from student where sid=1001
select * from student where sex='男' and birthday>'1991-1-1'
select * from student where birthday between '1990-1-1' and '1991-1-1'
select * from student where sname like '%五%' --%%是通配符
select distinct(class) from student --去除重复项
select count(sid) from student
select count(sid) from student where sid=1001 and pwd='123'
select count(sid),class from student group by class
select count(sid),sex from student group by sex
select count(sid),sex,class from student group by sex,class
select count(sid),class from student where sex='男' group by class
select sum(score) from score
select avg(score) from score where sid=1001
select max(score),cid from score group by cid
select avg(score),cid from score group by cid having avg(score)>60
select avg(score),sid,cid from score group by sid,cid having avg(score)<60
select avg(score),sid from score group by sid having avg(score)>59



select * from score where score = (select max(score) from score)
select * from student where birthday = (select min(birthday) from student)


select * from student
select * from course
select * from score

select count(sid) as Y_N from student where sid=1001 and pwd='123'
--select avg(score) as avg from score where sid=1001
--select count(sid),class from student group by class
--select count(sid),sex from student group by sex
--select count(sid),class,sex from student group by class,sex
--select count(sid),class from student where sex='男' group by class


--内连接
select stude

nt.sid,student.sname,student.class,score.score
from student inner join score
on student.sid = score.sid
--三表链接
select student.sid,student.sname,ame,score.score
from score inner join student
on student.sid=score.sid
inner join course
on score.cid=course.cid
where student.sname = '张三'


--用右外表查询（以右表为基准）
select student.sid,student.sname,student.class,score.score
from student right outer join score
on student.sid=score.sid

select student.sid,student.sname,student.class,score.score
from student left outer join score
on student.sid=score.sid
where score.score is null

--交叉链接(笛卡尔集合)(少用)
select student.sid,student.sname,score.score
from student cross join score


--嵌套查询
--查询最高分的学生
select sid,sname
from student where sid in
(select sid from score where score
= (select max(score) from score))

--总分最高的学生学号
select sid from(
select top 1 sum(score) as s,sid from score group by sid order by s desc) a

--统计每门课程最高分的学生的学号
select score.sid,score.cid,score.score from score,

(select max(score) as m,cid from score group by cid) b
where score.cid=b.cid and score.score= b.m


--查班级平均分
select avg(c.score),c.class
from (select a.class,b.score
from student a,score b
where a.sid=b.sid) c group by class

--个人平均成绩
select avg(c.score),c.sid
from (select a.sid,b.score
from student a,score b
where a.sid=b.sid) c group by sid

--单科最高分的同学学号和姓名和班级

select distinct(d.sid),d.sname,d.class from (
select c.sid,c.sname,c.class from student c,(
select sid,score from score,(
select max(score) as s,cid from score group by cid) a
where a.s = score.score) b
where c.sid=b.sid) d



declare @i int --int i =0;
declare @s nvarchar(10) --string s;
set @i = 1; --i = 10;
--set @s = 'AAAAAAA' --s = 'AAAAAAA'

while @i<10 --while 循环
begin
print @i
set @i = @i + 1 --i++
end

select sid,score,case when cid=1 then 'C#' when cid=2 then 'English' end from score

select sid,cid,score,
case when score>59 then '及格' else '不及格' end
from score



--print @i --本地测试用print
--print @s
--
--if @i=0 --if条件语句
--begin
--print 'BBBBBBBBBBBBBBBBBBBBBBBB'
--end
--else
--begin
--print 'SSSSSSSSSSSSS'
--end


--使用存储过程判断用户登录信息
alter proc sp_login
@sid int, --输入参数
@pwd nvarchar(20), --输入参数
@s nvarchar(20) output --输出参数
as
declare @i int
set @i = (select count(1) from student where sid=@sid and pwd=@pwd )
--return @i
if @i = 1
set @s = '合法用户'
else
set @s = '非法用户'

declare @s nvarchar(20)
exec sp_login 1001,'123',@s output
print @s

if(select min(score) from score where sid=1001)>90
print '学生1001成绩全部优秀'
else if(select min(score) from score where sid = 1001)>

59
print '学生1001成绩全部及格'
else print '学生1001有成绩不及格'

--为表创建具有不同字段名的视图
create view v_stu(sid,sname,sex)
as
select sid,sname,sex from student
--查看视图数据
select * from v_stu

--使用存储过程实现从表及联删除
alter proc sp_delete
@sid int
as
delete from score where sid = @sid
delete from student where sid = @sid

exec sp_delete 1001
--使用存储过程添加学生信息
-------------------添加---------------------------------------开始-------------------------------------------------------------------------
alter proc sp_add
@sid int,
@sname nvarchar(20),
@sex nvarchar(10),
@birthday datetime,
@class nvarchar(10),
@pwd nvarchar(10),
@i int,
@r nvarchar(10) output --一个过程可以有多个输出参数但只有一个返回值 输出参数是任何类型
as
if @i = 0
begin
if not exists (select sid from student where sid=@sid)
begin
insert into student values(@sid,@sname,@sex,@birthday,@class,@pwd)
set @r= '添加成功'
end
else
set @r= '重复添加'
end
else
begin
update student set sname = @sname,sex=@sex,birthday=@birthday,class=@class,pwd=@pwd where sid=@sid
if @@rowcount >0
set @r = '修改成功'
else
set @r = '修改无效'
end

declare @r int
exec @r = sp_add 1011,'李四','男' ,'1990-1-12','一班','123'
print @r
------------------------------------查找所有学生分数--------------------一个-------------------------------------------------------------------
alter proc sp_selectAllStudentScore
as
select student.sid,student.sname,ame,score.score
from score right join student
on student.sid=score.sid
left join course
on score.cid=course.cid
------------------------------------------------
exec sp_selectAllStudentScore

select * from student
delete from student where sid= 0
-----------------------------------条件查询学生分数----------------------一个-----------------------------------------------------------
create proc sp_selectstudentscore
@sid int,
@sname nvarchar(10)
as
if @sid>0
begin
select student.sid,student.sname,ame,score.score
from score inner join student
on student.sid=score.sid
inner join course
on score.cid=course.cid
where student.sid=@sid
end
else
begin
select student.sid,student.sname,ame,score.score
from score inner join student
on student.sid=score.sid
inner join course
on score.cid=course.cid
where student.sname like '%'+@sname+'%'
end
---------------------------------------------
exec sp_selectstudentscore 1001,''
exec sp_selectstudentscore '','三'
--------------------------查找与删除学生----------------------------------一个-----------------------------------------------------------
alter proc sp_operstudent
@sid int,
@i int
as
if @i = 0
select * from student where sid = @sid
else
delete from student where sid = @sid

----------------------登录---------------------------

----------一个-------------------------------------------------------------------
alter proc sp_login
@sid int,
@pwd nvarchar(20)
as
declare @i int
set @i = (select count(1) from student where sid = @sid and pwd = @pwd)
return @i



--------------------------查看学生分数信息----------------------------------------------------------------------------------------------------
alter proc sp_viewstudent
@sid int,
@sum int output,
@avg int output,
@pid int output
as
declare cur cursor for select avg(score) p, sum(score) s,sid from score group by sid order by s desc
open cur
declare @p int, @s int, @sid2 int, @i int
set @i = 1
fetch from cur into @p, @s,@sid2
while @@fetch_status = 0
begin
if @sid2 = @sid
begin
set @pid = @i
set @sum = @s
set @avg = @p
end
set @i = @i + 1
fetch from cur into @p, @s,@sid2
end
close cur
deallocate cur

select cname,score from score a,course b
where a.cid = b.cid and a.sid = @sid


declare @avg int,@pid int,@sum int
exec sp_viewstudent 1003 ,@pid output,@sum output,@avg output
print @sum
print @avg
print @pid
-------------------------------------------------------------结束------------------------------------------------------
--使用存储过程添加新课程信息，并输入新课程的ID
alter proc sp_addCourse
@cname nvarchar(10)
as
insert into course values(@cname)
return @@identity

declare @i int
exec @i = sp_addCourse 'PHP'
print @i

select * from course

--实现数据表分页查询
--页码 = 总行数 / 每页行数 page
--每页行数 size
--总行数

--paixu linshibiao zhuanyonghanshu

select top 5 * from (select top (9-(2-1)*5) * from employees order by employeeid desc) a
order by a.employeeid


alter proc sp_page
@page int, --页码
@table varchar(10), --表名称
@orderby varchar(10), --排序字段名
@size int
as
declare @sql varchar(500) --放sql语句字符串
set @sql = 'declare @count int '
set @sql = @sql +' set @count =(select count(1) from ' + @table + ')'
set @sql = @sql + ' select top ' + str(@size) + ' * from (select top (@count - (' + convert(varchar(10),@page) + '-1)*'
+str(@size)+') * from ' + @table + ' order by ' + @orderby + ' desc) a ' +
'order by a.'+ @orderby
exec (@sql)

print @sql

exec sp_page 1,'employees','employeeid',3



--函数
declare @str varchar(10)
set @str = '1234'
declare @i int
set @i = 1234
--print len(@str)
print len(ltrim(str(@i)))

print substring(@str,1,2)
print floor(rand()*1000)

--
print getdate()--Datetime.Now
print dateadd(day,2,getdate())

print dateadd(day,2,'2011-10-20')--往后添加时间

print datediff(day,'2011-10-25','2012-12-10')--时间差 TimeSpan.days

print datepart(year,'2011-10-25')--获取时间格式中的一部分（即年，月或日）
print datepart(month,'2011-10-25')
print datepart(day,'2011-10-25')
print convert(nvarchar(10),datepart(year,'2011-10-25')

)
+ '/' + convert(nvarchar(10),datepart(month,'2011-10-25'))
+ '/' + convert(nvarchar(10),datepart(day,'2011-10-25'))

--按出生年份统计学生人数
--sid counts years

select count(sid),datepart(year,birthday) from student group by datepart(year,birthday)

select * from @table

declare @table nvarchar(10)
set @table = 'student'
exec('select * from ' + @table)

create proc sp_AA
@table varchar(10)
as
exec ('select * from ' + @table)

exec sp_AA 'student'




--编写通用版的分页存储过程
--自定义函数
create function fun(@i int) returns nvarchar(10)
as
begin
return convert(nvarchar(10),@i)
end

print dbo.fun(10)--dbo 当前数据库里有效

--定义函数 输入姓名后返回学号
create function fun2(@sname nvarchar(10)) returns int
as
begin
declare @i int
set @i = (select sid from student where sname=@sname)
return @i
end

print dbo.fun2('张三')

--查询学号，课程号，分数 同时在分数栏将<60的成绩直接输出为不及格
--学号 课程 分数
--1001 1 95
--1002 1 不及格
alter function fun3(@score int) returns nvarchar(10)
as
begin
declare @r nvarchar(10)
if @score<60
set @r = '不及格'
else
set @r = convert(nvarchar(10),@score)

return @r
end

select sid,cid,dbo.fun3(score) from score
--在同一列，输出 Employees 表中的lastname 和 firstname 字段

create function fun4(@lastname varchar(10),@firstname varchar(10)) returns varchar(20)
as
begin
return @lastname +'-'+@firstname
end

select employeeid,dbo.fun4(lastname,firstname),title from employees
--计算 order details 表 每条订单的总价
select * from [order details]

create function fun5(@unitprice money,@quantity smallint,@discount real) returns smallint
as
begin
return @unitprice * @quantity * (1 - @discount)
end

select *,dbo.fun5(unitprice,quantity,discount) from [order details]

--游标 不占用物理内存，全部是临时文件
declare cur1 cursor for select sid,sname,class from student
open cur1
declare @sid int, @sname nvarchar(10),@class nvarchar(10),@i int
set @i = 1
fetch from cur1 into @sid,@sname,@class --fetch 取游标所在的行的值
while @@fetch_status = 0
begin
print convert(nvarchar(10),@i)+'. '+ convert(nvarchar(10),@sid)+','+@sname+','+@class
set @i =@i + 1
fetch from cur1 into @sid,@sname,@class
end
close cur1
deallocate cur1
--给所有分数＜60的人加送10分
declare cur2 cursor for select * from score
open cur2
declare @sid int,@cid int,@score int
fetch from cur2 into @sid,@cid,@score
while @@fetch_status = 0 --0语句成功 -1语句失败或行不在结果集中 -2提取的行不存在
begin
if @score < 60
update score set score = @score + 10 where sid = @sid and cid = @cid
fetch from cur2 into @sid,@cid,@score
end
close cur2
deallocate cur2 --清空资源

--找出重名的人，并使用合适的方式返回

结果数据。
select * from student

alter proc sp_Find
as
create table #table1
(
sid int,
sname nvarchar(10)
)
declare cur3 cursor for select sid,sname from student
open cur3
declare @sid int,@sname nvarchar(10),@count int
fetch from cur3 into @sid,@sname
while @@fetch_status = 0
begin
set @count = (select count(sid) from student where sname=@sname)
if @count > 1
insert into #table1 values(@sid,@sname)
--print convert(nvarchar(10),@sid)+' '+@sname
fetch from cur3 into @sid,@sname
end
close cur3
deallocate cur3
select * from #table1

exec sp_Find
--触发器
--级联删除学生信息
alter trigger t_delete on student for delete
as
begin
declare @sid int
set @sid = (select sid from deleted)
--print @sid
delete from score where sid = @sid
end

delete from student where sid = 1004

select * from student
select * from score

--添加学生信息,同时设置每人的考试成绩默认为0
alter trigger t_insert on student for insert
as
begin
declare @sid int
set @sid = (select sid from inserted)
declare cur cursor for select cid from course
open cur
declare @cid int
fetch from cur into @cid
while @@fetch_status = 0
begin
insert into score values(@sid,@cid,0)
fetch from cur into @cid
end
close cur
deallocate cur
end


insert into Student values(1030,'十几','男', '1992-5-5','三班','120')
select * from student
select * from score

--修改学生学号信息 101001 111001
alter trigger t_update on student for update
as
begin
declare @sid1 int,@sid2 int
set @sid1 = (select sid from deleted)
set @sid2 = (select sid from inserted)
update score set sid = @sid2 where sid=@sid1
end

update student set sid = 101001 where sid = 1001
select * from course

begin tran
begin try
delete from student where sid = 1002
end try
begin catch
rollback --回滚（撤销）
end catch
select * from student

--排序
declare @temp table(pid int identity(1,1), s int,sid int)
insert into @temp select sum(score) s,sid from score group by sid
order by s desc
select pid from @temp where sid =1001