黑马程序员_学习日记50_619数据库开发及ADO.Net（临时表、表变量、视图、变量、if…else…和while、事务、存储过程、触发器）

一、临时表

（一）面试题：

一张表中的数据上百万，关联表中的数据上千万，如何查询优化？

答：将两表中有用的数据分别提取到临时表（是内存中的表）中

（二）

局部临时表 create table #tablename(列信息);

全局临时表  create table ##tablename(列信息);

 

二、表变量

--定义一个表变量

declare @varT1 table(col1 int,col2 char(2));

insert into @varT1 values(100,’A’)

insert into @varT1 values(101,’B’)

select * from @varT1

 

三、视图：存储查询语句，本身并不存储数据；如果数据库中的数据改了，视图查询出的数据也改变

（一）

--视图中查询语句必须为每列创建列名

create view vw_StudentScore

as

查询语句

--在视图中不能使用order by 语句，除非还指定了TOP或FOR XML

create view vw4

as

select top * from TblStudent order by tsage desc

--这样做以后，通过vw5查询出的数据还是没有顺序

create view vw5

as

select top 100 percent * from TblStudent order by tsage desc

 

alter 修改视图 drop 删除视图

（二）视图的优点

1、降低数据库的复杂度

2、防止未经许可的用户访问敏感数据。

 

四、变量

（一）局部变量

--声明变量

declare @name varchar(10)

declare @age int

--为变量赋值

--set @name=’yzk’

--set @age=18

select @name=’yzk’

select @age=18

--使用set与select为变量赋值的区别:

--形式上了区别：

declare @rcount int

--set @rcount=(select count(*) from TblStudent)

select @rcount=count(*) from TblStudent

print @rcount

--实际区别

declare @userAge int

--当使用set为变量赋值时，如果右边返回多个值，则“报错”

set @userAge=(select tsage from TblStudent)

print @userAge

--=================================

declare @userAge int

--当使用select为变量赋值时，如果右边返回多个值，则始终以最后一个为准

select top 5 @userAge=tsage from TblStudent

print @userAge

 

set @name=’aaa’

set @age=10

--select @name,@age

--print @name,@age –报错！！！

print @name+cast(@age as varchar(10))

 

（二）全局变量

print ‘aaa’+100

--通过判断@@error变量中是否为0，就可以判断上一条语句是否出错

print @@error

 

五、if…else…和while

declare @age int

set @age=50

if @age>10

begin

         print ‘大于10岁’

end

else

begin

         print ‘小于10岁’

end

--=============通过while计算1-100间所有奇数和===============

declare @sum int=0;

declare @i int=1

while @i<=100

begin

         if @i%2<>0

                   begin

                            set @sum=@sum+@i

end

         set @i=@i+1

end

print @sum

 

六、事务

（一）

--打开一个事务

begin tran

--提交事务

commit tran

--回滚事务

rollback tran

（二）

try…catch…

begin tran

begin try

         declare @sum int = 0

         update bank set balance = balance – 900 where cid=’0001’

         set @sum=@sum+@@error

         update bank set balance = balance + 900 where cid=’0002’

         set @sum=@sum+@@error

         commit

end try

begin catch

         rollback

end catch

（三）默认情况下自动提交事务、隐式事务

--隐式事务

SET IMPLICIT_TRANSACTIONS ON

delete from bank

select * from bank

rollback

（四）事务的特性：ACID：原子性、一致性、隔离性、永久性

 

七、存储过程

（一）使用存储过程的优点：

1、执行速度更快——在数据库中保存的存储过程语句都是编译过的

2、允许模块化程序设计——类似方法的复用

3、提高系统安全性——防止SQL注入

4、减少网络流通量——只要传输存储过程的名称

（二）系统存储过程：一般以sp_或者xp_开头

exec sp_helptextsp_databases

exec sp_helptext'sp_helptext'

exec sp_helpdb'Test'

exec sp_renamedb'test','test123'

exec sp_tables

exec sp_columns'tblstudent'

exec sp_helptext'sp_columns'

（三）创建一个自定义的存储过程

create proc usp_Helloworld

as

begin

    print 'hello world!'

end

 

exec usp_Helloworld

（四）存储过程中参数的问题

--存储过程如果有参数,则调用的时候必须为参数赋值

exec usp_Add--不传参则报错

 

alter procedure usp_Add

@n1 int,

@n2 int=1000 --为存储过程的参数设置默认值

as

begin

    print @n1+@n2

end

（五）在存储过程中返回值也可以通过参数来做，类似于C#中的out参数

例：计算两个数的和，并将和返回

create procedure usp_Add

@n1 int = 100,  --为存储过程的参数设置默认值

@n2 int ,

@sum int output --当参数后面加了output后，表示该参数是用来返回的

as

begin

    set @sum=@n1+@n2

    print '计算完毕!'

end

 

--存储过程的output参数要得到返回值,也得传一个变量进去

declare @val int

exec usp_Add@n1=1000,@n2=100,@sum=@val output --在调用的时候也得加output关键字

print @val

（六）存储过程实现登录：

pms[2].Direction = ParameterDirection.Outout;

//执行存储过程与Sql语句最大的区别需要设置一个CommandType

cmd.CommandType = CommandType.StoredProcedure

//该存储过程执行完后，不返回任何值，是否登陆成功是根据输出参数来判断的。可以用cmd对象的任何方法（ExecuteNonQuery、ExecuteScalar、ExecuteReader）执行

//判断用户登录是否成功，关心的是执行完存储过程后的输出参数

bool b = Convert.ToBoolean(pms[2] Value)

//到底调用ExecuteNonQuery、ExecuteScalar、ExecuteReader中的什么方法，和调用Sql语句还是存储过程没有关系，只看最后执行完毕的返回结果，如果是结果集则调用ExecuteReader；如果是单个值，ExecuteScalar；不返回结果，则调用ExecuteNonQuery

 

（七）存储过程实现增删改操作

--增

create proc usp_tblCls_insert

@clsName varchar(50),

@clsDesc varchar(50)

as

begin

    insert into TblClass values(@clsName,@clsDesc)

end

--删

create proc usp_TblClass_delete

@clsId int

as

begin

    delete from TblClass where clsId=@clsId

end

--改

create proc usp_TblClass_update

@clsId int

@clsName nvarchar(50)

@clsDesc nvarchar(50)

as

begin

    update TblClass set @clsName=@clsName,clsDesc=@clsDesc

    where clsId=@clsId

end

（八）实现一个针对特定表的分页存储过程

create proc usp_GetDataByPageIndex

@PageSize int=10,

@PageIndex int

as

begin

    select * from(

    select *,row_number() over(order by id) as rowIndex from MyOrders) as Tbl

    where rowIndex between @PageSize*(@PageIndex-1)+1 and @PageSize*@PageIndex

end

 

exec usp_GetDataByPageIndex@PageIndex=9

（九）ADO.Net中实现事务

//通过Connection创建一个事务对象

SqlTransaction tran = con.BeginTransaction();

string sql = “delete from TblStudent where tsid=4”;

using(SqlCommand cmd = new SqlCommand(sql,con))

{

         cmd.Transaction = tran;

         con.Open();

         cmd.ExecuteNonQuery();

         tran.Rollback();//回滚

         Console.WriteLine(“回滚了。”);

}

 

tran.Commit();//提交事务

 

八、触发器

DML触发器、DDL触发器

inserted表和deleted表：当执行insert语句时用到inserted表，当执行deleted语句时用到deleted表

当执行update语句时，是将旧数据放到inserted表中，将新数据放到inserted表中。

语法：

--例：将插入数据显示出来

create trigger tri_TblClass_insert_after

on TblClass after insert

as

begin

         declare @id int

         declare @name varchar(50)

         declare @desc varchar(50)

         select @id=tclassId,@name=tclassname,@desc=tclassdesc from inserted

        

         print @id

         print @name

         print @desc

end

--sql server中的触发器是表级触发器，无论删除多少行或插入多少行只触发一次。

 

九、几个问题：

using(SqlCommand cmd = new SqlCommand(sql,con))

{

         //这里如果用“0”，就会调用SqlParameter类的另一个重载SqlParameter(string parameterName,SqlDbType dbtype)

         SqlParameter p1 = new SqlParameter(“@age”,(object)0);

         cmd.Parameters.Add(p1);

         con.Open();

         cmd.ExecuteNonQuery();

}

 

config文件可以用记事本打开修改连接字符串

 

作业：使用存储过程、事务、winfrom实现转账

create proc usp_bank_trans

@zh1l

CommandType

SqlHelper

用存储过程做前面的练习