SQL Server高级部分笔记

SQL Sever数据库

SQL sever基本语句：

输出语句：Print

声明变量语句：declare

数据类型转换：Convert(varchar,@age)

输出语句：

print 10*9 select 10*9 as '乘积';

最后一次插入的数据行的标识值。

@@IDENTITY

受上一个SQL语句影响的行数
select @@ROWCOUNT

本地的服务名称
select @@SERVERNAME

SQL Sever的if语句：

if（条件）  begin  （输出的内容）  end  else  begin  （输出的内容）  end

第一单元

第二单元

1、查询操作

案例1：查询学号“20209130501”的学生的年龄，并输出

案例代码：

declare @age int;--声明一个局部变量,用于保存年龄 select @age=Age from Baselnfo where StuNo=20209130501; print '学号为20209130501的学生的年龄'+ Convert(varchar,@age);--输出变量的值，等效于C#中的Console.WritrLine(@age) go

案例2：查询学号在：哈哈同学前一个和后一个的学生信息

案例代码：

declare @stuNo bigint; select @stuNo=StuNo from Baselnfo where Name='哈哈';  select * from Baselnfo where StuNo=@stuNo-1 or StuNo=@stuNo+1; go

案例3：找表中年龄最大的学生

案例代码：

declare @maxAge int; declare @name nvarchar(50); select @maxAge=Max(Age) from Baselnfo; select @name=Name from Baselnfo where Age=@maxAge; print @name; go

2、内联接查询

查询男女生C#科目的平均成绩

案例代码：

select Gender,AVG(Score) from BaseInfo bi inner join StuScore ss on bi.StuId=ss.StuId  where Subject='C#'group by Gender;

3、逻辑控制语句if-else

根据男女生C#科目的平均分,若男生的高于女的，则输出男生C#平均分高于女生，否则输出女生C#平均分高于男生。

案例代码：

--求男生的平均分 declare @maleAVG float; select @maleAVG=AVG(Score) from BaseInfo bi inner join StuScore ss on bi.StuId =ss.StuId where Subject='C#' and Gender='1';  --求女生的平均分 declare @femaleAVG float; select @femaleAVG=AVG(Score) from BaseInfo bi inner join StuScore ss on bi.StuId =ss.StuId where Subject='C#' and Gender='0';  if(@maleAVG>=@femaleAVG) begin print'男生C#的平均分大于女生平均分' end    else begin print'女生C#的平均分大于男生平均分' end go

4、多分支语句：case...end

案例代码：

select StuId '学号',Name '姓名',(case when Gender=1 then '男' else '女' end) as'性别' from BaseInfo;

--case语句的第一种格式：case后不带条件表达式，而是在when子句后带条件表达式，若条件表达式为真，则执行相应的then子句后的子句.

select StuId '学号',Name '姓名',(case when Gender=1 then '男' else '女' end) as'性别' from BaseInfo

case语句的第二种格式：case后带有条件表达式

select StuId '学号',Name '姓名',(case Gender when 1 then '男' else '女' end) as'性别' from BaseInfo

5、用case... end来判断平均分大小

案例代码：

select distinct'比较大结果'= (case when @maleAVG>@femaleAVG then '男生的C#的平均分大于女生的平均分' when @maleAVG=@femaleAVG then '男生的C#的平均分等于女生的平均分' else '男生的C#的平均分小于女生的平均分'  end) from StuScore;

6、while循环

--在T-SQL中，循环使用while语句来实现

--查询SQLSever科目的平均分 declare @AVGScore float; select @AVGScore=AVG(Score) from StuScore where Subject='SQLSever'; while(@AVGScore<80) begin   update StuScore set Score=Score+1 where Subject='SQLSever' and Score<100;  select @AVGScore=AVG(Score) from StuScore where Subject='SQLSever'; end print @AVGScore go

第三章

1、子查询

查询“张瑛”同学高于八十分的成绩信息

1.1联合查询

select * from StuScore t1,BaseInfo t2 where  t1.StuId= t2.StuId and Score>80 and Name='张瑛';

1.2使用子查询来实现

1.2.1子查询作为临时表来使用

 select  s2.StuId,s2.Name,s1.Subject,s1.Score from  BaseInfo s2,  (select * from StuScore where Score>80) s1  where Name='张瑛' and s1.StuId=s2.StuId;

1.2.2 子查询作为查询条件来使用

查询学号比张瑛同学小的，且挨得最近的一个学生信息

select top 1 * from BaseInfo where StuId< (select StuId from BaseInfo where Name='张瑛') order by StuId desc;

1.2.3子查询作为列来使用

查询所有学生的C#成绩信息，若无分数则显示null

select bi. *, (select score from StuScore ss where bi.StuId=ss.StuId and Subject='C#')CSharpScore  from BaseInfo bi

2、使用in和not in 完成子查询

--使用in完成子查询 select Name as'姓名' from BaseInfo where StuId in   (select StuId from StuScore where Subject='C#' and Score>=70)      --使用not in来完成子查询   select StuId as '学号', Name as'姓名' from BaseInfo where StuId not in   (select StuId from StuScore where Subject='C#' and Score>=70);

3、使用exists和not exists完成子查询

-- 使用exists查询存在成绩的学生的学号和姓名 select StuId,Name from BaseInfo where exists (select StuId from StuScore where StuScore.StuId=BaseInfo.StuId)  --  使用exists查询不存在成绩的学生的学号和姓名  select StuId,Name from BaseInfo where not exists (select StuId from StuScore where

4、使用some，any，all进行子查询

查询学号为20209130203不低于学号20209130504最高分还要高的成绩信息

--使用all select Subject, Score from StuScore where StuId=20209130503 and Score> all (select Score from StuScore where StuId=20209130504) --使用any  select Subject, Score from StuScore where StuId=20209130503 and Score> any (select Score from StuScore where StuId=20209130504) --使用some  select Subject, Score from StuScore where StuId=20209130503 and Score> some (select Score from StuScore where StuId=20209130504)

5、排序函数

5.1 ROW_NUMBER()函数：没有并列编号，不跳空编号

select ROW_NUMBER() over(order by Score desc) as '名次',bi.StuId '学号',Name '姓名',Score '分数'  from StuScore ss, BaseInfo bi where Subject='C#' and ss.StuId=bi.StuId;

5.2 RANK()函数：有并列编号，有跳空函数

select RANK() over(order by Score desc) as '名次',bi.StuId '学号',Name '姓名',Score '分数'  from StuScore ss, BaseInfo bi where Subject='C#' and ss.StuId=bi.StuId;

5.3 DENSE_RANK()函数：有并列编号，没有跳空编号

select DENSE_RANK() over(order by Score desc) as '名次',bi.StuId '学号',Name '姓名',Score '分数'  from StuScore ss, BaseInfo bi where Subject='C#' and ss.StuId=bi.StuId;

6、公式表表达式

--将公式表表达式（CTE）视为临时结果集，在select，insert，update，delete或create vlew 语句的执行范围内进行定义 with ScoreInfo(StuId ,Name ,Score ,Subject) as (select bi.StuId '学号',Name '姓名',Score '分数',Subject as'科目' from StuScore ss, BaseInfo bi where Subject='C#' and ss.StuId=bi.StuId) select * from ScoreInfo go

第四单元视图和索引

4.1、创建视图

--如果有视图则删除 if exists( select * from sysobjects where name='ew_Student') begin drop view ew_Student end go --创建视图 create view ew_Student as ( select bi.StuId, Name,Gender,ExamNo,Subject,Score  from BaseInfo bi,StuScore ss where bi.StuId=ss.StuId ) go  --案例2 --创建视图ew_Student2， create view ew_Student2 as ( select bi.StuId as '学号', Name as '姓名',Gender as '性别',ExamNo,Subject as '科目',Score as '成绩'  from BaseInfo bi  left join StuScore ss on bi.StuId=ss.StuId ) go --查询视图ew_Student2 select * from ew_Student2;

4.2、创建索引

--创建索引 if exists(select * from sysindexes where name='TX_score') begin drop index Studentinfo2.TX_score--删除索引 end go create nonclustered index TX_score on StuScore(score) with fillfactor=30 go  --查询StuScore表所有的索引 sp_helpindex StuScore  --使用索引进行查询 select * from StuScore with (index=TX_score);

索引查询的语法：

select * from 表名with (index=索引名);

查询结果：

第五章事务和游标

回滚事务：RollBack transaction

提交事务：commit transaction

系统事务：@@error

5.1、事务

5.1.1、事务的特性：

原子性，一致性，隔离性，持久性

5.1.2、事务可以分为三种类型：

显示事务，自动提交事务，隐式事务

案例代码：

--使用事务实现转账 begin transaction tran_Bank --开始一个事务 declare @error int--声明局部变量， set @error=0--变量初始值 update Bank set Yuer=Yuer-1000 where Name='桑永波'; set @error=@error+@@error; update Bank set Yuer=Yuer+1000 where Name='詹露'; set @error=@error+@@error; if(@error<>0) 	begin 	RollBack transaction--回滚事务 	print'转账失败' 	end  	else 	begin 	commit transaction--提交事务 	print'转账成功' 	end 	go  select * from Bank;

5.2、游标

--游标 declare cursor_Mark cursor scroll for select * from StuScore;--定义游标  open cursor_Mark--打开游标  --检索游标 declare @examNo int declare @stuId bigint declare @Subject nvarchar(50) declare @Score float fetch first from cursor_Mark into @ExamNo,@StuId,@Subject,@Score   while(@@fetch_status=0) begin  if(@Score+5>100) update StuScore set Score=100 where examNo=@examNo else update StuScore set Score=Score+5 where examNo=@examNo  fetch next from cursor_Mark into @ExamNo,@StuId,@Subject,@Score end  close cursor_Mark--关闭游标  deallocate cursor_Mark--删除游标

第六章存储过程

concat：字符拼接

output：输出参数

intput:输入参数

sp：系统存储过程

xp：调用操作系统

up：自定义存储过程

sp_helpindex：查看某个表的索引

sp_stored_procedures：列出当前环境中的所有存储过程

6.1、存储过程

--案例1： --创建存储过程（不带参数的） --查询参加了考试的所有学生信息 if exists(select * from sysobjects where name='sp_Score') drop proc sp_Score go create proc sp_Score as 	select bi.StuId ,bi.Name,ss.ExamNo,ss.Score,ss.Subject 	from BaseInfo bi,StuScore ss 	where bi.StuId=ss.StuId go --2.调用存储过程 exec sp_Score  --案例2： --根据学生姓名查询参加了考试的所有学生信息 if exists(select * from sysobjects where name='sp_ScoreByName') drop proc sp_ScoreByName go create proc sp_ScoreByName @Name nvarchar(50)='张英'--参数带有默认值 as 	select bi.StuId ,bi.Name,ss.ExamNo,ss.Score,ss.Subject 	from BaseInfo bi,StuScore ss 	where bi.StuId=ss.StuId and Name=@Name go exec sp_ScoreByName '牛开军' --案例3： --根据学生姓名查询该学生的C#成绩信息 if exists(select * from sysobjects where name='sp_ScoreByNameandSubJect') drop proc sp_ScoreByNameandSubJect go create proc sp_ScoreByNameandSubJect @Name nvarchar(50)='张英',--参数带有默认值 @Score float output--输出参数 as 	select @Score=Score 	from BaseInfo bi,StuScore ss 	where bi.StuId=ss.StuId and Name=@Name and Subject='C#' go declare @Score float--先声明输出参数对应的变量 exec sp_ScoreByNameandSubJect '张英' , @Score output print concat('张英的C#成绩是：',@Score)  --扩展内容：将学习成绩表BaseInfo表中的学号和姓名列显示为“学号-姓名”格式 select Convert( varchar,StuId)+'-'+Name from BaseInfo select concat(StuId,'-',Name) from BaseInfo

第七章

触发器的类

delete 触发器

update触发器

insert触发器

7.1、创建触发器

语法：

create trigger trig_insert
on TransInfo for insert
as

案例代码：

create trigger trig_insert on TransInfo for insert  as declare @accountNumber varchar(20),@transType nvarchar(20),@transMoney money  select  @accountNumber=AccountNumber,@transType=TransType,@transMoney=TransMoney from inserted; if(@TransType='存入') begin update Account set CurrentMoney=CurrentMoney+@transMoney where AccountNumber=@AccountNumber select CurrentMoney from Account where AccountNumber=@accountNumber print '存款成功账户信息如下：' print '卡号：'+concat(@accountNumber,',存入金额',@transMoney) end else--交易类型为支取时 begin update Account set CurrentMoney=CurrentMoney-@transMoney where AccountNumber=@AccountNumber select CurrentMoney from Account where AccountNumber=@accountNumber print '存款成功账户信息如下：' print '卡号：'+concat(@accountNumber,',取出金额',@transMoney) end go insert into TransInfo Values('123456789','存入','200',getdate())

SQL Sever数据库

SQL sever基本语句：

第一单元

第二单元

1、查询操作

2、内联接查询

3、逻辑控制语句if-else

4、多分支语句：case...end

5、用case... end来判断平均分大小

6、while循环

第三章

1、子查询

1.1联合查询

1.2使用子查询来实现

1.2.1子查询作为临时表来使用

1.2.2 子查询作为查询条件来使用

1.2.3子查询作为列来使用

2、 使用in和not in 完成子查询

3、使用exists和not exists完成子查询

4、使用some，any，all进行子查询

5、排序函数

5.1 ROW_NUMBER()函数：没有并列编号，不跳空编号

5.2 RANK()函数：有并列编号，有跳空函数

5.3 DENSE_RANK()函数：有并列编号，没有跳空编号

6、公式表表达式

第四单元 视图和索引

4.1、创建视图

4.2、创建索引

第五章 事务和游标

5.1、事务

5.1.1、事务的特性：

5.1.2、事务可以分为三种类型：

5.2、游标

第六章 存储过程

6.1、存储过程

第七章

触发器的类

7.1、创建触发器

2、使用in和not in 完成子查询

第四单元视图和索引

第五章事务和游标

第六章存储过程