SQL数据库 存储过程、索引、视图与临时表

--数据库编程2.

--存储过程  exec
  --执行速度快：存储过程是编译之后的语句，保存在数据库的内容；存储过程的操作只有一次。

--系统存储过程 存放在master数据库中，都以“sp_”或“xp_”开头

exec sp_databases
exec sp_renamedb 'studentmanager','sm' --修改数据库的名称
exec sp_columns a --查看表a 列的信息
use sm
exec sp_tables table_stu8
--用户自定义存储过程
--无参数的定义存储过程
--仅有输入参数的存储过程
create proc proc_add
@num1 int, --不加output 说明是输入函数
@num2 int
as
declare @num int 
set @sum=@num1+@num2
print @sum
go


exec proc_add 6,2


 if exists(select *from sysobjects where name='proc_score2')
drop proc_score2
go


create proc proc_score2
as


declare @avgWritten float
declare @avgLab float
select @avgWritten=avg(writtenExam) from stuMarks
select @avgLab=avg(labExam) from stuMarks
print '笔试平均分：'+convert(varchar(20),@avgWritten)
print '机试平均分：'+convert(varchar(20),@avgLab)
if @avgWritten>=70 and @avgLab>=70
begin
print '成绩情况：优秀'
end
else
begin
print '成绩情况：不优秀'
end
select *from stuMarks where writtenExam<60 or labExam<60

go


  --调用存储过程
exec proc_score2
  --可以指定默认值
select *from sysobjects
drop proc proc_score2


--既有输入参数又有输出参数
create proc proc_score3
@www float=60,
@qqq float=60,
@num int output,
@score float output
as


declare @avgWritten float
declare @avgLab float
select @avgWritten=avg(writtenExam) from stuMarks
select @avgLab=avg(labExam) from stuMarks
print '笔试平均分：'+convert(varchar(20),@avgWritten)
print '机试平均分：'+convert(varchar(20),@avgLab)
if @avgWritten>=70 and @avgLab>=70
begin
print '成绩情况：优秀'
end
else
begin
print '成绩情况：不优秀'
end
select *from stuMarks where writtenExam<@www or labExam<@qqq

select @num=count(*) from stuMarks where writtenExam<@www or labExam<@qqq
select @score=count(*) from stuMarks where writtenExam<@www or labExam<@qqq
go
--调用
declare @peoplenum int
declare @pscore int
exec proc_score3 60,60,@peoplenum output,@pscore output 
print @peoplenum
---------------------案例
--0901班/0902班/0903班 写一个存储过程  截取数据
create proc proc_split
@str varchar(100),
@head varchar(20) output,
@tail varchar(100) output
as
--查找/的位置
declare @position int 
set @position=charindex('/',@str)
if @position=0
begin
--说明传入的只有一个班级
set @head=@str
set @tail=null
end
else
begin
--说明至少有两个班级
set @head=substring(@str,1,@position-1)
set @tail=substring(@str,@position+1,len(@str)-@position)
end
go
--调用
declare @head varchar(20)
declare @tail varchar(100)
exec proc_split '0901班/0902班/0903班',@head output,@tail output
print @head
print @tail


declare @head2 varchar(20)
declare @tail2 varchar(100)
declare @str1 varchar(100)
set @str1= '0901班/0902班/0903班/01班/02班/03班'
while @str1 is not null 
begin
exec proc_split @str1,@head2 output,@tail2 output
print @head2
set @str1=@tail2
end



--视图 只能针对于查询语句来写  试图在做查询跟表是一样的 视图是一张虚表 并不存储数据 数据来自于真实的表
-- 不能做增删改  只能做查询
use sm
create view view_show --创建视图
as
select *from table_student
go
--使用
select *from view_show

--临时表 一旦关闭 则不存在 断开数据库连接时，临时表自动删除
create table #aa
(
id int primary key,
name varchar(20) not null
)

--索引   可以提高查询速度  但是减慢了增删的速度   类似于书籍中的目录 

--唯一索引
--主键索引
--聚集索引
--非聚集索引
  --加索引的原则
1.建立索引依据 (非聚集索引)
where age>15    order by createtime desc
2.建立聚集索引（只能有一个）,一般选取唯一的额列，列的大小长度尽量小

   --创建索引
  create clustered index index_lname 
on A(lname) --在表A的lname列上建立索引


--使用索引
  select *from A with(index=index_lname)
where lname='sasas'