20171107--SQL变量，运算符，存储过程

create database shujuku03
go
use shujuku03
go
create table jiaoshi--创建jiaoshi表--
(
    code int primary key,--code 列设置主键
    name varchar(20),
    kecheng varchar(20),
    sex varchar(10),
    riqi varchar(20),
)
go
create table xuesheng--创建xuesheng表，与jiaoshi表有关联
(
    xuehao int primary key,--xuehao列设置主键
    name varchar(20),
    banji varchar(10),
    sex varchar(10),
    Cnjs int references [dbo].[jiaoshi]([code]),--设置外键，与jiaoshi关联
    Majs int references [dbo].[jiaoshi]([code]),--
    Enjs int references [dbo].[jiaoshi]([code]),--
)
go
create table fenshu--创建fenshu表，与学生表有关联
(
    xuhao int references [dbo].[xuesheng]([xuehao]),--设置外键，与xuesheng关联
    yuwen decimal(10,2),
    shuxue decimal(10,2),
    yingyu decimal(10,2),
)
go
insert into jiaoshi values (1,'刘欣欣','语文','女','1980-5-10')
insert into jiaoshi values (2,'文祥','语文','女','1983-9-19')
insert into jiaoshi values (3,'毕华','数学','男','1977-1-10')
insert into jiaoshi values (4,'闻广华','数学','男','1979-5-10')
insert into jiaoshi values (5,'张峰','英语','男','1984-5-10')
insert into jiaoshi values (6,'李莉','英语','女','1986-7-7')
insert into xuesheng values (1,'曹操','1班','男',1,3,5)
insert into xuesheng values (2,'曹丕','1班','男',1,3,5)
insert into xuesheng values (3,'司马懿','1班','男',1,3,5)
insert into xuesheng values (4,'大乔','1班','女',1,3,5)
insert into xuesheng values (5,'小乔','1班','女',1,3,5)
insert into xuesheng values (6,'张飞','2班','男',2,4,6)
insert into xuesheng values (7,'关羽','2班','男',2,4,6)
insert into xuesheng values (8,'刘备','2班','男',2,4,6)
insert into xuesheng values (9,'诸葛','2班','男',2,4,6)
insert into xuesheng values (10,'貂蝉','2班','女',2,4,6)
insert into fenshu values (1,80,80,58)
insert into fenshu values (2,65,35,80)
insert into fenshu values (3,78,42,80)
insert into fenshu values (4,85,80,15)
insert into fenshu values (5,80,87,80)
insert into fenshu values (6,86,80,76)
--update fenshu set yuwen=16,shuxue=53,yingyu=46 where xuhao=6
insert into fenshu values (7,28,82,80)
insert into fenshu values (8,45,72,68)
insert into fenshu values (9,99,99,10)
insert into fenshu values (10,87,88,36)
use shujuku03

-------------------变量--------------------
--主要作用是 临时存储数据，以方便进行多个嵌套的查询等操作
--定义变量
--declare 变量名(必须以@开头) 数据类型
declare @ab varchar(20)
--赋值
set @ab='你好'
--取值
select @ab
---以上三行需要一块执行，
declare @abc varchar(20) set @abc='你好' select @abc
--赋值 也可以结合查询语句来用：
declare @df varchar(20)
set @df='语文'
select *from jiaoshi where kecheng=@df
--一般都需要加上 where 条件使用
--下面的过程只是给变量赋值的过程并不显示。
declare @df varchar(20)
set @df='语文'
select @df=name from jiaoshi where code=1
--------------全局变量，系统变量--------------
--select print 都可使用
--返回SQLserver自上次启动以来的连接数，不管成功失败
print @@connections
select @@CONNECTIONS
--返回上一次执行SQL的语句中的错误，返回0表示没有错误
select @@ERROR
--返回当前用的是什么语言
print @@language
--受上一句命令影响的行数
print @@rowcount--默认返回int型，可以转化数据类型。用cast
select '影响了'+CAST(@@ROWCOUNT as varchar(20))+'行'
--返回SQL的版本信息
print @@version
--转义字符，单引号'
declare @zy varchar(20)
set @zy='123''4560'
print @zy

------------------运算符---------------
--+-*/%(取余数) 赋值运算符
declare @jia int
set @jia=1+2
set @jia=30%7--30除7取余数
print @jia
--比较运算符
--=,<,>,<=,>=,<>,!=,!>,!<
--<>,!=  不等于
select *from jiaoshi where code<>3
--逻辑运算符
--and,or,all,any,between,in,like,not,some,exists
--all 全部的条件满足。any,some满足任何一个条件
--not非，like像，exists存在
select *from xuesheng
--all 表示满足子查询中所有的条件，配合比较运算符使用
select *from xuesheng where Cnjs=all(select Cnjs from xuesheng where Cnjs=1and Majs=3)
--any 表示满足子查询中任何一个条件，配合比较运算符使用

--not 可以结合 in like exists 使用，表示不在某个范围，不像某个值，不存在。not不可单独使用

--查询 二班学生数学成绩大于所有一班学生成绩的人的信息
select *from xuesheng where (banji='2班' and xuehao in(
select xuhao from fenshu where shuxue>(
select MAX(shuxue) from fenshu where xuhao in(
select xuehao from xuesheng where banji='1班')))



---------一元运算符
--正号(+)，负号(-)，负号使用时需要加括号，否则当做减号用

-----------------优先级-----------------------
--  *,/,%
-- 正负，+,-
-- =,<,>,<=,>=,<>,!=,!>,!<
-- not
-- and,or,between
-- all,any,some,in,like,exists
-- =

------------------------------------
begin--开始
--中间可以写任何语句块
select *from jiaoshi
end--结束
-------------------------------
--if，else
declare @bian int
set @bian=10
if @bian>5
--当if..else中间跟随多个语句时 需要用 begin..end括起来
begin
    print '你好！'
    print '你好啊！'
end
else
    print '不好！'
--练习  数学分数最高
select *from xuesheng where xuehao in (select xuhao from fenshu where shuxue=(select max(shuxue) from fenshu))

declare @shuxue decimal(18,2)
select @shuxue=MAX(shuxue) from fenshu 
declare @xuehao int
select @xuehao=xuhao from fenshu where shuxue=@shuxue
declare @sex varchar(20)
select *from xuesheng where xuehao=@xuehao
if @sex='男'
print '男同学'
else
print '女同学'
--------------------------------------
--wnile 循环 begin..end 相当于大括号，表示语句的开始和结束
declare @shu int
set @shu=85
while @shu<95
begin
    print '很好，'+cast(@shu as varchar(10))
    set @shu=@shu+1
    if @shu=93
    break
    if @shu>87 and @shu<90
    begin
        continue
    end
    print ' 123'
end
-------------------------------------
--try...catch

-------------------------------------------
--闻广华  教的班级中数学大于80的学生，如果超过3个则print达标，否则不达标
select *from jiaoshi 
select xuhao from fenshu where shuxue>=80
select COUNT(*) from xuesheng where Majs=(select code from jiaoshi where name='毕华') and xuehao in (select xuhao from fenshu where shuxue>=80) group by banji
declare @n int
select @n=COUNT(*) from xuesheng where Majs=(select code from jiaoshi where name='闻广华') and xuehao in (select xuhao from fenshu where shuxue>=80) 
if @n>3
    print '合格'
else
    print '不合格'

-----------------存储过程---------------
create procedure proc1
as
select *from xuesheng
go
--不管执行是否成功都会有一个返回值，返回0表示成功
--可编程性-存储过程-存储过程名-右键-执行存储过程
------------------------------------------
--执行命令，有返回值，但需要定义变量来接受
execute proc1
--定义变量接受返回值
declare @fanhui int
exec @fanhui=proc1
select @fanhui as 返回值
----------------------修改存储过程-----------------------------
alter proc proc1--alter(修改)，proc(存储过程)，proc1(存储过程名)
as
begin
declare @n int
select @n=COUNT(*) from xuesheng where Majs=(select code from jiaoshi where name='闻广华') and xuehao in (select xuhao from fenshu where shuxue>=80) 
if @n>3
    print '合格'
else
    print '不合格'
end
go
exec proc1
----------------------带参数------------------------------
alter proc proc_js
@name varchar(20)
as
begin
    declare @jscount int,@kc varchar(20),@geshu int
    select @jscount=COUNT(*) from jiaoshi where name=@name
        if @jscount=0
    begin
        print '没有这个老师'
    end
        else
    begin
        select @name=name,@kc=kecheng from jiaoshi where name=@name
            if @kc ='数学'
        begin
            select @geshu=count(*) from fenshu where xuhao in (
            select xuehao from xuesheng where Majs=(select code from jiaoshi where name=@name)
            ) and yuwen>=80
        end
            if @kc ='语文'
        begin
            select @geshu=count(*) from fenshu where xuhao in (
            select xuehao from xuesheng where Cnjs=(select code from jiaoshi where name=@name)
            ) and shuxue>=80
        end
            if @kc ='英语'
        begin
            select @geshu=count(*) from fenshu where xuhao in (
            select xuehao from xuesheng where Enjs=(select code from jiaoshi where name=@name)
            ) and yingyu>=80
        end
            if @geshu>=3
        begin
            print '达标'
        end
            else
        begin
            print '不达标'
        end
    end
end
exec proc_js '闻广华'
--------输入学号，判断是否结业，只有1门课及格或更少不结业  ，2门课及格/结业  ，3门课及格/结业并优秀

alter proc xs_jieye
@xuehao int
as
begin
    declare @yuwen int,@yingyu int,@shuxue int,@xh int,@ges int ,@name varchar(20)
    select @name=name from xuesheng where xuehao=@xuehao
    select @xh=MAX(xuehao) from xuesheng
    select @yuwen=COUNT(*) from fenshu where yuwen>60 and xuhao=@xuehao
    select @shuxue=COUNT(*) from fenshu where shuxue>60 and xuhao=@xuehao
    select @yingyu=COUNT(*) from fenshu where yingyu>60 and xuhao=@xuehao
    set @ges=@yuwen+@shuxue+@yingyu--课程一共有几门及格
        if @xh<@xuehao--判断输入是否正确
    begin
        print '没有学生信息'
    end
        else
    begin
            if @ges=3
        begin
            print @name+'同学'
            print '恭喜你！成功结业了，而且成绩很优秀！'
        end
            if @ges=2
        begin
            print @name+'同学'
            print '成绩合格可以结业了'
                if @yuwen=0
            begin
            print '但是你的语文成绩不及格，需要多加复习！'
            end
                if @shuxue=0
            begin
            print '但是你的数学成绩不及格，需要多加复习！'
            end
                if @yingyu=0
            begin
            print '但是你的英语成绩不及格，需要多加复习！'
            end
        end
            if @ges<=1
        begin
            print @name+'同学'
            print '不好意思，你的课程超过2门不及格，不能结业！'
        end
    end
end
exec xs_jieye 10
select name,yuwen,shuxue,yingyu from xuesheng join fenshu on xuehao=xuhao
---------------------------------------------------------------
create proc xs_2
@xuehao int
as
begin
    declare @geshu int ,--主要变量，用来判断几门课及格
    @xue int ,--用来判断输入是否有误
    @name varchar(20),--用来接收学生姓名
    @yu int ,@shu int,@ying int--用来判断哪一门没有及格
    set @geshu=0--主要变量，需要赋值
    --以下是判断课程是否及格
        if (select yuwen from fenshu where xuhao=@xuehao)>=60
    begin
        set @geshu=@geshu+1 
        set @yu=1--语文及格
    end
        if (select shuxue from fenshu where xuhao=@xuehao)>=60
    begin
        set @geshu=@geshu+1
        set @shu=1--数学及格
    end
        if (select yingyu from fenshu where xuhao=@xuehao)>=60
    begin    
        set @geshu=@geshu+1
        set @ying=1--英语及格
    end
    select @xue=MAX(xuehao) from xuesheng--查询学号的最大值
    select @name=name from xuesheng where xuehao=@xuehao--查询学生的名字
        if @xue<@xuehao and @xuehao<=0--判断输入的学号是否有误
    begin
        print '没有学生信息'
    end
        else
    begin
            if @geshu=3
        begin
            print @name+'同学'
            print '恭喜你！成功结业了，而且成绩很优秀！'
        end
            if @geshu=2
        begin
            print @name+'同学'
            print '成绩合格可以结业了'
                if @yu!=1
            begin
            print '但是你的语文成绩不及格，需要多加复习！'
            end
                if @shu!=1
            begin
            print '但是你的数学成绩不及格，需要多加复习！'
            end
                if @ying!=1
            begin
            print '但是你的英语成绩不及格，需要多加复习！'
            end
        end
            if @geshu<=1
        begin
            print @name+'同学'
            print '不好意思，你的课程超过2门不及格，不能结业！'
        end
    end
end
exec xs_2 10
-----------------------使用return返回值得存储过程--------------------------------
create proc xs_3
@xuehao int
as
begin
    declare @geshu int --主要变量，用来判断几门课及格
    set @geshu=0--主要变量，需要赋值
    --以下是判断课程是否及格
        if (select yuwen from fenshu where xuhao=@xuehao)>=60 set @geshu=@geshu+1 
        if (select shuxue from fenshu where xuhao=@xuehao)>=60 set @geshu=@geshu+1
        if (select yingyu from fenshu where xuhao=@xuehao)>=60 set @geshu=@geshu+1
    return @geshu
end
--定义变量接收执行的存储过程的返回值
declare @zhi int
exec @zhi=xs_3 10
print @zhi
--可以设默认值，   （default）
create proc cun01
@sum int=10
as
begin
    set @sum=@sum+8
    return @sum
end

declare @zhi1 int
exec @zhi1=cun01 default--(没有参数，使用默认值)
print @zhi1
-----------------------1-n的和--------------------------
drop proc he
create proc he    
@sum int 
as
begin
    declare @shu int =0,@i int =0
        while @i<=@sum
    begin
        set @shu=@i+@shu
        set @i=1+@i
    end
    return @shu
end
go
declare @he11 int
exec @he11=he 10
print @he11