数据库及ADO.NET学习（六）

临时表

简介

两个需要连接的表有上百万甚至上千万数据。
将两个表中的部分数据提取到临时表中，临时表在内存中，只用来查询。
所以临时表数据量变小，只用来查询速度高，在内存里速度也快，进行大数据量查询时进行优化。

1、局部临时表

create table #tbName(列信息);

只在当前会话中有效，不能跨连接访问。
关闭连接后再打开就没有这个表了。
如果直接在连接会话中创建的，则当前连接断开后删除，如果是在存储过程中创建的则当前存储过程执行完毕后删除。

2、全局临时表

create table ##tbName(列信息);

create table ##MyStudent
(
	stuName varchar(50),
	stuAge int
)
insert into ##MyStudent values('张三',18)
select * from ##MyStudent

多个会话可共享全局临时表
当创建全局临时表的会话断开，并且没有用户正在访问(事务正在进行中)全局临时表时删除

表变量

declare @a table (col1 int,col2 char(2))
insert into @a values(10,'A')
select * from @a

视图

普通视图

create view view1 as select * from CallRecords
select * from view1
alter view view1 as ...
drop view view1
--根据已有视图创建新的视图
create view view2 as select * from view1
select * from view2

能够把CallRecords中的数据查询出来，但是实际运行的语句还是‘select * from CallRecords’，
视图本身并不存储数据，它存储的只是一个查询语句，每次查询视图都是执行一次查询语句，返回执行结果。如果修改真实表中的数据，则通过视图查询得出的结果也变了。
视图的目的是查询语句太长的情况下方便查询。
优点：
筛选表中的行，降低数据库的复杂程度
防止未经许可的用户访问敏感数据（看不见表）
注：1、视图中所有列必须起列名
2、视图中不能使用order by语句，除非另外还指定了 TOP、OFFSET 或 FOR XML。

但是这样是可以的：

create view view3 as select * from CallRecords
select * from view3 order by StartDateTime
--而
create view view4 as 
select top 100 percent * from CallRecords order by StartDateTime;
--是不行的，最后的结果并没有按照StartDateTime进行排序，因为排序后是有序的，但是top又把它变成无序的了。

索引视图

本身视图并不存储数据，但是在视图上再建索引，视图就会存储数据了

变量

局部变量以一个@开头，先声明再使用，使用的时候也是一个@开头，而全局变量以@@开头，不能声明，也不能手动赋值，例如@@identity，只能使用。
局部变量的声明、赋值和使用：

--==============声明=============
declare @name varchar(20)
declare @age int
--=============赋值==============
set @name = '张三'
set @age=18
set @name = '张三'
set @age=18
--============输出===============
print @name
print @age
--============两种赋值方式的区别===============
declare @rcount int
set @rcount=(select count(*) from CallRecords)
select @rcount=count(*) from CallRecords
print @rcount
--都打印为10
declare @a int
set @a=(select TelNum from CallRecords)
--子查询返回的值不止一个，报错
select @a=CallerNumber from CallRecords
--子查询返回不止一个，以最后一个为准
print @a
--===========初值问题==================
declare @b int
set @b=@b+1
select @b
--输出为NULL，如果不赋值为NULL,与任何类型计算都是NULL，故建议声明时赋初值

常用的全局变量：

注：@@error是判断上一句执行是否出错，出错就是错误号，不出错就是零。

if else、while

if else语句

declare @a int
set @a=10
if @a>5
begin
	print '大于5'
end
else
begin
	print '小于5'
end

while语句

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

2500，计算的从1到100所有奇数的和。

事务

定义

一系列的事情，对一系列的sql语句的操作
最典型的就是转账问题：A的钱减少，B的钱增加
把多个语句当作一个事务来处理，要么全部执行成功，要么全部执行失败
举例：

create table bank
(
	cId char(4) primary key,
	balance money,
)
alter table bank
add constraint CH_balance check(balance >=10)
insert into bank values('0001',1000)
insert into bank values('0002',10)

如果进行以下操作：

update bank set balance=balance-1000 where cid='0001'
update bank set balance=balance + 1000 where cid='0002'

第一个由于不满足余额的条件执行失败，而第二句执行成功，明显不符合转账规则。

事务基本操作：

--打开一个事务
begin tran
--提交事务
commit tran
--出错，回滚事务
rollback tran

事务简单操作：

begin tran
declare @sum int=0
update bank set balance=balance-1000 where cid='0001'
set @sum=@sum+@@error
update bank set balance=balance + 1000 where cid='0002'
set @sum=@sum+@@error
--判断是否有代码执行出错
if @sum=0
begin 
	print '提交'
	commit tran
end
else
begin 
	print '回滚'
	rollback tran
end

begin tran
begin try 
	declare @sum int=0
	update bank set balance=balance-1000 where cid='0001'
	set @sum=@sum+@@error
	update bank set balance=balance + 1000 where cid='0002'
	set @sum=@sum+@@error
	commit tran
end try
begin catch
	rollback tran
end catch

事务的分类：

--打开一个事务
--自动提交事务，默认情况下
insert into bank values('0003',1000)
--显式事务
begin tran
insert into bank values('0004',900)
rollback
--隐式事务，默认是关的
set implicit_transactions {on|off}
set implicit_transactions on
delete from bank
select * from bank
rollback
--隐式事务不需要begin tran作为事务的开始，但是需要rollback

事务特性ACID

原子性: 事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性：事务中包含的处理要满足数据库提前设置的约束，如主键约束或者NOT NULL 约束等。
隔离性：事务处理过程中的中间状态对外部是不可见的。
持久性：事务完成之后，它对于数据的修改是永久性的。

存储过程

就像数据库中的运行方法（函数），就是把一堆代码封装起来

优点：

速度快，它是提前写好的保存在数据库中的一段代码，省去了编译步骤；
允许模块化程序设计
防止SQL注入，提高系统安全性（存储过程也有参数）
减少网络流通量，只传输存储过程名称，而不需要传输一大堆sql语句

系统存储过程：

一般以sp_或者xp_开头

比较常用的如下：

查看存储过程源代码可以在目录中打开，也可以exec sp_helptext sp_databases查看

自定义存储过程

一般以usp_开头
简单创建与执行：

--创建存储过程
create proc usp_HelloWorld
as
begin 
	print 'HelloWorld'
end
--执行
exec usp_HelloWorld
--删除
drop procedure usp_HelloWorld

创建存储过程，计算两个数的和：

create proc usp_Add
@n1 int,@n2 int
as
begin
	print @n1+@n2
end
--执行
exec usp_add 100,50

注：如果存储过程有参数，则调用的时候必须为参数赋值，否则会报错。

参数默认值使用：

--第二个参数设置默认值
alter procedure usp_Add
@n1 int,
@n2 int = 1000
as
begin
	print @n1+@n2
end
exec usp_Add 1
--1001
--两个参数都有默认值
alter procedure usp_Add
@n1 int = 100,
@n2 int = 1000
as
begin
	print @n1+@n2
end
exec usp_Add 
--1100
--drop procedure usp_Add

但是如果第一个参数设置了默认值，第二个参数没有设置，执行的时候一个参数会报错，需要显式指定参数是给哪个变量的，如下所示。

alter procedure usp_Add
@n1 int = 100,
@n2 int
as
begin
	print @n1+@n2
end
exec usp_Add @n2=200

传递参数的时候可以显式指定参数名。

存储过程返回值：

使用到的参数：output

alter procedure usp_Add
@n1 int = 100,
@n2 int,
@sum int output --参数后面加上output表示该参数是用来返回的
as
begin
	set @sum= @n1+@n2
	print '计算完毕'
end

declare @val int
execute usp_Add @n1=1000,@n2=100,@sum=@val output--调用的时候也要加
print @val
--1100

通过ADO.NET实现存储过程：

1、简单登录实现
存储过程代码：

--登录 存储过程
alter procedure usp_UserLogin
@userName varchar(50),
@userPwd varchar(50),
@result bit output
as
begin
    declare @count int
	set @count=
	(
		select count(*) from UserLogin where userName=@userName and userPwd=@userPwd
	)
	if @count>0
	begin set @result=1 end
	else
	begin set @result=0 end
end
--验证
declare @a bit
execute usp_UserLogin '三','12345',@result=@a output
print @a

窗体代码：

#region 登录
string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
    //sql语句变为存储过程名称
    string procedure = "usp_UserLogin";
    using (SqlCommand cmd = new SqlCommand(procedure, con))
    {
        //+++++++++++++++++++++
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] pms = new SqlParameter[]
        {//存储过程有参数，Command对象也要增加参数
            new SqlParameter("@userName",textBox1.Text.Trim()),
            new SqlParameter("@userPwd",textBox2.Text),
            new SqlParameter("@result",SqlDbType.Bit)
        };
        //设置第三个参数是output参数
        pms[2].Direction = ParameterDirection.Output;
        cmd.Parameters.AddRange(pms);
        //设置CommandType，说明执行的是存储过程
        cmd.CommandType=CommandType.StoredProcedure;
        con.Open();
        //在这调用ExecuteNonQuery或者另外两个没有什么太大的区别
        //因为只要执行存储过程返回值就可以，把存储过程执行了就行了
        cmd.ExecuteNonQuery();
        //判断登录成功与否，关心的是执行完存储过程的输出参数
        //调用command对象执行只看执行完以后的返回结果，结果集--reader 单个值--scalar insert,delete,update
        bool b = Convert.ToBoolean(pms[2].Value);
        if(b) MessageBox.Show("登录成功！");
        else MessageBox.Show("登录失败！");
    }
}
#endregion

2、插入实现
存储过程代码：

--插入存储过程
create procedure usp_UserLogin_insert
@userName varchar(50),
@userPwd varchar(50)
as
begin
	insert into UserLogin values(@userName,@userPwd)
end
--验证
execute  usp_UserLogin_insert '李四','123456'

窗体代码：

#region 插入
string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
    //存储过程名称
    string procedure = "usp_UserLogin_insert";
    using (SqlCommand cmd = new SqlCommand(procedure, con))
    {
        //+++++++++++++++++++++
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] pms = new SqlParameter[]
        {
            new SqlParameter("@userName",textBox1.Text.Trim()),
            new SqlParameter("@userPwd",textBox2.Text)
        };
        cmd.Parameters.AddRange(pms);
        con.Open();
        int r= cmd.ExecuteNonQuery();
        con.Close();
        if (r > 0) MessageBox.Show("插入成功！");
        else MessageBox.Show("插入失败！");
    }
}
#endregion

3、查询实现
存储过程代码：

--查询存储过程
create procedure usp_UserLogin_select
as
begin
	select * from UserLogin
end
--验证
execute usp_UserLogin_select

窗体代码：

#region 查询
List<Info> list = new List<Info>();
string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
    //存储过程名称
    string procedure = "usp_UserLogin_select";
    using (SqlCommand cmd = new SqlCommand(procedure, con))
    {
        //+++++++++++++++++++++
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Info info = new Info();
                    info.userName=reader.IsDBNull(1)?string.Empty:reader.GetString(1);
                    info.userPwd=reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
                    list.Add(info);
                }
            }
        }
        con.Close();
    }
}
dataGridView1.DataSource = list;
#endregion

4、更新、删除存储过程

--删除存储过程
create procedure usp_UserLogin_delete
@autoId int
as
begin
	delete from UserLogin where autoId=@autoId
end
--验证
execute  usp_UserLogin_delete '李四','123456'
--更新存储过程
create procedure usp_UserLogin_update
@autoId int,
@userName varchar(50),
@userPwd varchar(50)
as
begin
	update UserLogin set userName=@userName,userPwd=@userPwd where autoId=@autoId
end
--验证
execute usp_UserLogin_update 12,'李四','123456'

5、使用存储过程实现分页

--分页存储过程
alter procedure usp_CallRecords
@pageSize int = 10,
@pageIndex int
as
begin
	select * from
	(select *,row_number() over(order by Id) as rowIndex from CallRecords) as tb
	where tb.rowIndex between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize
end
--执行
execute usp_CallRecords 2,3

存储过程版的sqlHelper：

实质就是加了类型参数。

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;

namespace WindowsFormsApp1
{
    public class SqlHelperProcedure
    {
        //获取配置文件中的字符串
        private static readonly string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
        /// <summary>
        /// 执行insert delete update的方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql,CommandType type, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.CommandType = type;
                    if (pms != null) cmd.Parameters.AddRange(pms);
                    con.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行sql语句，返回单个值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, CommandType type, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.CommandType = type;
                    if (pms != null) cmd.Parameters.AddRange(pms);
                    con.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 执行sql语句返回DataReader
        /// 注意：Connection和DataReader都不能关闭，执行ExecuteReader
        /// 需要传递参数
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql, CommandType type, params SqlParameter[] pms)
        {
            SqlConnection con = new SqlConnection(constr);
            try
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.CommandType = type;
                    if (pms != null) cmd.Parameters.AddRange(pms);
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                    //关闭reader的时候自动关闭连接
                    return reader;
                }
            }
            catch
            {
                if (con != null) { con.Close(); con.Dispose(); }
                throw;
            }
        }
        /// <summary>
        /// 封装返回DataTable的方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static DataTable ExecuteDateTable(string sql, CommandType type, params SqlParameter[] pms)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(sql, constr);
            if (pms != null)
            {
                adapter.SelectCommand.Parameters.AddRange(pms);
            }
            DataTable dt = new DataTable();
            adapter.SelectCommand.CommandType = type;
            adapter.Fill(dt);
            return dt;
        }
    }
}

通过ADO.NET实现事务

string constr = "Data Source=LAPTOP-CELEUP2E;Initial Catalog=TestSchool;User ID=sa;Password=123456";
using (SqlConnection con = new SqlConnection(constr))
{
    con.Open();
    //通过Connection创建一个事务对象
    SqlTransaction tran=con.BeginTransaction();
    string sql = "delete from CallRecords where Id=1";
    using (SqlCommand cmd = new SqlCommand(sql, con))
    {
        cmd.Transaction = tran;
        int i=cmd.ExecuteNonQuery();
        Console.WriteLine("影响行数："+i);
        1、执行后所在行不会被删除，因为回滚了
        tran.Rollback();
        Console.WriteLine("回滚");
        2、执行后所在行会被删除，因为事务已经完成并提交
        tran.Commit();
        Console.WriteLine("提交");
    }
}
Console.WriteLine("ok");
Console.ReadKey();

触发器

定义：
触发器的作用：自动化操作，减少了手动操作以及出错的几率，触发器主要是通过事件进行触发而被执行。
触发器是一个功能强大的工具，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。
触发器在SQL Server里面就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。
inserted表与deleted表：
(1）deleted表存放由于执行delete或update语句而要从表中删除的所有行。
在执行delete或update操作时，被删除的行从激活触发器的表中被移动到deleted表，这两个表不会有共同的行。
(2）inserted表存放由于执行insert或update语句而要向表中插入的所有行。
在执行insert或update事物时，新的行同时添加到激活触发器的表中和inserted表中，inserted表的内容是激活触发器的表中新行的拷贝。
update事务可以看作是先执行一个delete操作，再执行一个insert操作，旧的行首先被移动到deleted表，让后新行同时添加到激活触发器的表中和inserted表中。
分类：
DML触发器：
Insert、delete、update（不支持select）
after触发器(for)：事后触发器、instead of触发器（不支持before触发器）：替换触发器
DDL触发器：
Create table、create database、alter、drop….

After触发器：
在语句执行完毕之后触发，按语句触发，而不是所影响的行数，无论所影响为多少行，只触发一次。
只能建立在常规表上，不能建立在视图和临时表上。
可以递归触发，最高可达32级。
update(列)，在update语句触发时，判断某列是否被更新，返回布尔值。
after和for都表示after触发器

instead of触发器：
用来替换原本的操作
不会递归触发
可以在约束被检查之前触发
可以建在表和视图上

常用语法：
1、执行插入语句完毕之后触发

create trigger tri_TblClass_insert_after
on UserLogin after insert
as
begin
	declare @autoId int
	declare @userName varchar(50)
	declare @userPwd varchar(50)
	select @autoId=autoId,@userName=userName,@userPwd=userPwd
	from inserted

	print @autoId
	print @userName
	print @userPwd
end

insert into UserLogin values('李明','1234')

结果：

13
李明
1234

2、执行删除语句完毕后触发

select top 0 * into UserLogin1 from UserLogin
--每次删除之后将deleted中的数据插入到UserLogin1
create trigger tri_UserLogin_delete_after
on UserLogin after delete 
as
begin
	insert into UserLogin1
	select * from deleted
end
select * from UserLogin
select * from UserLogin1
delete from UserLogin where autoId=10

3、替换

--原来的删除操作被替换成了 insert into UserLogin1 select * from deleted
--被替换了，执行别的去了
create trigger tri_UserLogin_delete_instead_of
on UserLogin instead of delete 
as
begin
	insert into UserLogin1
	select * from deleted
end

select * from UserLogin
select * from UserLogin1
delete from UserLogin where autoId=13

触发器使用注意事项：
尽量避免在触发器中执行耗时操作，因为触发器会与SQL语句认为在同一个事务中。（事务不结束，就无法释放锁。）
避免在触发器中做复杂操作，影响触发器性能的因素比较多，要想编写高效的触发器考虑因素比较多。