数据库高级

/*************************************************************************************//*************************************************************************************//******************************sql server数据库高级复习********************************//*************************************************************************************/--第二章 --1.变量--DECLARE @变量名 变量类型DECLARE @ID INTSET @ID=12PRINT @ID--SELECT @ID FROM TB WHERE ....--2.全局变量PRINT @@ERROR        --返回上一条sql语句执行错误号PRINT @@IDENTITY    --返回最后插入的标识值得系统函数--3.逻辑控制语句/*IF<条件表达式>    BEGIN        <代码块>    ENDELSE    BEGIN        <代码块>    END    WHILE<条件表达式>    BEGIN        <代码块>    ENDCASE    WHEN <条件表达式> THEN <运算式>    WHEN <条件表达式> THEN <运算式>    WHEN <条件表达式> THEN <运算式>    ELSE ''END*/--第三章 高级查询-----1、子查询--1.1 什么是子查询--可以用在增删改查语句中的查询，也可叫内部查询或嵌套查询--1.2为什么要使用子查询--降低SQL复杂度，增强可读性--1.3子查询的三种用法--子查询作为where条件(保证返回值是唯一的)--查询马文才的成绩select * from stuexam where stuno = (select stuno from stuinfo where stuname= '马文才')--子查询作为表使用--查询笔试成绩大于80分的学员信息select * from stuinfo s1 left join (select * from stuexam where writtenExam > 80) s2on s1.stuno = s2.stuno--子查询作为列使用（不好理解）--查询笔试成绩大于80分的学员信息select *,(select writtenexam from stuexam s2 where writtenExam > 80 and s1.stuno = s2.stuno) as 分数 from stuinfo s1---第三章 SQL高级查询（第二次课）-----Exists 和 Not Existsif exists (select * from sys.databases where name='studentDB')    drop database studentDB--查询机试及格的学生信息select * from stuexam where not exists (select * from stuexam where labExam >= 120)--some any all--必须跟子查询--some any 一些（只要比最小的大）--all 全部（比最大的都大）--0 1 2 3 4 5 6 7 8 9--2 4 6 8--3 4 5 6 7 8 9--9 select * from stuinfo--查询出比“李斯文”和“梅超风”年龄大的有那些select * from stuinfo where stuage > some(select stuage from stuinfo where stuname = '李斯文' or stuname ='梅超风')--查询出比“李斯文”和“梅超风”年龄都大select * from stuinfo where stuage > all(select stuage from stuinfo where stuname = '李斯文' or stuname ='梅超风')------------------我是分割线-------------------------------聚合技术--聚合函数(avg、sum、count)--compute 和 compute by 用一个查询可以得到两个结果（一个明细一个汇总）select * from stuinfo--查询班长所有的成绩及平均分select * from stuexamselect avg(labexam) from stuexam--computeselect * from stuexam compute avg(labexam)--compute by(可以根据分组后的结果再进行汇总)--查询所有人的平均年龄select * from stuinfo order by stuage compute avg(stuage) by stuageselect * from stuinfo order by stuaddress compute avg(stuage) by stuaddress------------------我是分割线-------------------------------排序函数--查询结果进行排序,可以明确具体名次--三个排序函数--1 Row_Number() 没有并列，不跳空  1 2 3 4--2 Rank()       有并列，有跳空    1 2 2 4 --3 Dense_Rank() 有并列，不跳空    1 2 2 3--语法：select Row_Number() over(order by 分数 desc) as 别名,列1,列2 from stuexam--查询分数表的机试排名及个人信息select row_number() over(order by labexam desc) as 排名,stuname,labexam from stuinfo as s1,stuexam as s2 where s1.stuno = s2.stunoselect rank() over(order by labexam desc) as 排名,stuname,labexam from stuinfo as s1,stuexam as s2 where s1.stuno = s2.stunoselect dense_rank() over(order by labexam desc) as 排名,stuname,labexam from stuinfo as s1,stuexam as s2 where s1.stuno = s2.stuno--公式表达式(临时表)--语法：with 临时表的名字(查询的字段)--      as--     (select 语句)with test(stuno,stuname,labexam)as(    select s1.stuno,stuname,labexam from stuinfo as s1,stuexam as s2 where s1.stuno = s2.stuno)select * from testgo--视图create view testasselect s1.stuno,stuname,labexam from stuinfo as s1,stuexam as s2 where s1.stuno = s2.stunogoselect * from test-----------第四章 索引和视图------------------数据库对象：索引、视图、事务、游标、存储过程、触发器--准备工作create database Testgouse Testgocreate table stuinfo(    stuid int,    stuname varchar(20))declare @num intset @num = 1while(@num < 1000001)begin    set @num = @num + 1    insert into stuinfo values(@num,'eric')endselect count(*) from stuinfo---索引--1、什么是索引--索引相当于给表中的数据编个号，方便查询，提高查询速度--2、索引的分类--聚集索引和非聚集索引--聚集索引,只有一个，而且一定是主键--非聚集索引，至多249--3、创建索引--手动和半手动（写sql语句）--查询一次数据select * from stuinfo where stuid = 9999--语法create  clustered --聚集索引index IX_stuid1on stuinfo(stuid)--with fillfactor = 0-100 --填充因子create table stuexam(    examid int primary key,    score int)--非聚集索引--可以在非主键字段create nonclustered --非聚集索引index IX_stunameon stuinfo(stuname)select * from stuinfo where stuname = 'eric' and stuid = 9999--使用索引（必须在条件语句中使用这个字段）--显式使用select * from stuinfo with(index=IX_stuname) where stuname = 'eric' and stuid = 9999--隐式使用select * from stuinfo where stuname = 'eric' and stuid = 9999--删除索引if exists(select * from sysindexes where name = 'IX_stuname')    drop index stuinfo.IX_stunamegocreate nonclustered --非聚集索引index IX_stunameon stuinfo(stuname)--索引只在查询时起作用--------------------------我是分割线-----------------------------视图--1、什么是视图--虚拟表，对原表数据没有影响--2、视图的优点--娜姐 学生信息电话、住址等信息--eric 学生的成绩select * from stuscore--delete from stusocredrop view stuscore--2.1 按需所取，只放某个用户关心的数据，简化表的结构--2.2 保护原表中的数据（数据信息，保护表名）--3、视图的创建语法create view 视图名as查询语句go--4、加密视图create view stuscoreasselect stuname,stuage,stuaddress from stuinfo,stuexam where stuinfo.stuno = stuexam.stunogocreate view stuscore1asselect stuname,stuage,stuaddress from stuinfo,stuexam where stuinfo.stuno = stuexam.stunogo--查询当前数据下所有视图的信息select * from information_schema.views--加密create view stuscore2with encryptionasselect stuname,stuage,stuaddress from stuinfo,stuexam where stuinfo.stuno = stuexam.stunogo---第六章 存储过程--优点：--1、减少网络流通量（传输存储过程的名字）--2、执行速度快（预编译）--3、安全(权限)--4、相互隔离（锁）--存储过程分类--不带参数--带参数--带输入参数create proc proc_test@id int = 1,@name varchar(20)assql语句go--带默认值的存储过程(调用的时候有区别，有默认值，调用该存储过程参数可以省略)SELECT * from stuinfocreate proc proc_insertStuinfo@stuno varchar(10),@stuname varchar(10),@stuage int = 18,@stuadd varchar(20) = '湖北武汉'asinsert into stuinfo values(@stuno,@stuname,@stuage,@stuadd)go--调用存储过程exec proc_insertStuinfo 's666','eric'exec proc_insertStuinfo 's777','lily',@stuadd = '北京'select * from stuinfo--带输出参数--output--输出马文才的机试成绩create proc proc_score@name varchar(20),@score int outputasselect @score=labExam from stuinfo as t1,stuexam as t2 where t1.stuno = t2.stuno and stuname = @namego--调用存储过程declare @score123 intexec proc_score '马文才',@score123 output --输出必须在调用是要标明output关键字print '马文才的成绩是'+convert(varchar(20),@score123)--Raiserror(自定错误) --try catch--语法create proc proc_math@num1 int,@num2 int = 0as    if(@num2 = 0)        begin            raiserror('亲，除法的除数不能为0',15,1)--catch            return --返回        end    declare @result int    set @result = @num1 / @num2    go@@errorexec proc_math 100create proc proc_trans1@trnsmoney intas    if(@trnsmoney > 30000)        begin            raiserror('亲，金额太大，请分批处理',15,1)--catch            return --返回        end    goexec proc_trans1 50000create proc pro_stu1@num int outputascreate table tab(    num int)goselect rand()*100000-----第七章 触发器-----1、什么是触发器？--特殊的存储过程，事务，被动执行的sql语句--2、为什么使用触发器?--一般用在增删修的动作之后，还要自动做的sql指令--3、触发器的分类--DML 数据操作语言 Data Mandatory Language--select insert update delete --DDL 数据定义语言 Data Define Language--create drop --DCL 数据控制语言 Data Control Language--grant revoke--创建添加触发器create trigger tri_firston stuinfo --创建触发动作表for  insert--delete insert update(三选一) --创建什么触发器as--触发动作declare @stuno varchar(20)select @stuno = stuno from insertedinsert into stuexam values(@stuno,0,0)go--测试触发器insert into stuinfo values ('s2508','mike','18','上海')--创建添加触发器2--往商品记录表中添加一条记录，修改库存create trigger  tri_goodinfoon goodinfofor insert as--触发动作declare @id intselect @id = goodid from insertedupdate 库存表 set 库存数量 = 库存数量 -1  where 商品id = @idgo--删除触发器create trigger tri_stuinfo_deleteon stuinfo for deleteasdeclare @no varchar(10)select @no = stuno from deleted delete from stuexam where stuno =@no go--测试删除触发器delete from stuinfo where stuno = 's2508'--修改触发器create trigger tri_stuinfo_updateon stuinfofor updateas    declare @no varchar(20)    select @no = stuno from deleted     insert into stuexam values(@no,0,0)go--测试update stuinfo set stuname = 'eric' where stuname = 'lily'--修改触发器2create trigger tri_stuexam_updateon stuexamfor updateas    declare @before int    declare @after int    select @before = labexam from deleted    select @after = labexam from inserted    print '亲，修改完成，修改前的分数为：'+convert(varchar(10),@before)    print '亲，修改完成，修改后的分数为：'+convert(varchar(10),@after)    go--测试update stuexam set labexam = 59 where labexam =90 and stuno = 's2506'