create database shujuku03gouse shujuku03gocreate table jiaoshi--创建jiaoshi表--(    code int primary key,--code 列设置主键    name varchar(20),    kecheng varchar(20),    sex varchar(10),    riqi varchar(20),)gocreate 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]),--)gocreate table fenshu--创建fenshu表，与学生表有关联(    xuhao int references [dbo].[xuesheng]([xuehao]),--设置外键，与xuesheng关联    yuwen decimal(10,2),    shuxue decimal(10,2),    yingyu decimal(10,2),)goinsert 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=6insert 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 @@connectionsselect @@CONNECTIONS--返回上一次执行SQL的语句中的错误，返回0表示没有错误select @@ERROR--返回当前用的是什么语言print @@language--受上一句命令影响的行数print @@rowcount--默认返回int型，可以转化数据类型。用castselect ‘影响了‘+CAST(@@ROWCOUNT as varchar(20))+‘行‘--返回SQL的版本信息print @@version--转义字符，单引号‘declare @zy varchar(20)set @zy=‘123‘‘4560‘print @zy

------------------运算符-----------------+-*/%(取余数) 赋值运算符declare @jia intset @jia=1+2set @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 jiaoshiend--结束---------------------------------if，elsedeclare @bian intset @bian=10if @bian>5--当if..else中间跟随多个语句时 需要用 begin..end括起来begin    print ‘你好！‘    print ‘你好啊！‘endelse    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 intselect @xuehao=xuhao from fenshu where shuxue=@shuxuedeclare @sex varchar(20)select *from xuesheng where xuehao=@xuehaoif @sex=‘男‘print ‘男同学‘elseprint ‘女同学‘----------------------------------------wnile 循环 begin..end 相当于大括号，表示语句的开始和结束declare @shu intset @shu=85while @shu<95begin    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>=80select COUNT(*) from xuesheng where Majs=(select code from jiaoshi where name=‘毕华‘) and xuehao in (select xuhao from fenshu where shuxue>=80) group by banjideclare @n intselect @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 proc1asselect *from xueshenggo--不管执行是否成功都会有一个返回值，返回0表示成功--可编程性-存储过程-存储过程名-右键-执行存储过程--------------------------------------------执行命令，有返回值，但需要定义变量来接受execute proc1--定义变量接受返回值declare @fanhui intexec @fanhui=proc1select @fanhui as 返回值----------------------修改存储过程-----------------------------alter proc proc1--alter(修改)，proc(存储过程)，proc1(存储过程名)asbegindeclare @n intselect @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 ‘不合格‘endgoexec proc1----------------------带参数------------------------------alter proc proc_js@name varchar(20)asbegin    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    endendexec proc_js ‘闻广华‘--------输入学号，判断是否结业，只有1门课及格或更少\不结业  ，2门课及格/结业  ，3门课及格/结业并优秀alter proc xs_jieye@xuehao intasbegin    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    endendexec xs_jieye 10select name,yuwen,shuxue,yingyu from xuesheng join fenshu on xuehao=xuhao---------------------------------------------------------------create proc xs_2@xuehao intasbegin    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    endendexec xs_2 10-----------------------使用return返回值得存储过程--------------------------------create proc xs_3@xuehao intasbegin    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 @geshuend--定义变量接收执行的存储过程的返回值declare @zhi intexec @zhi=xs_3 10print @zhi--可以设默认值，   （default）create proc cun01@sum int=10asbegin    set @sum=@sum+8    return @sumenddeclare @zhi1 intexec @zhi1=cun01 default--(没有参数，使用默认值)print @zhi1-----------------------1-n的和--------------------------drop proc hecreate proc he    @sum int asbegin    declare @shu int =0,@i int =0        while @i<=@sum    begin        set @shu=@i+@shu        set @i=1+@i    end    return @shuendgodeclare @he11 intexec @he11=he 10print @he11