目录
临时表
简介
两个需要连接的表有上百万甚至上千万数据。
将两个表中的部分数据提取到临时表中,临时表在内存中,只用来查询。
所以临时表数据量变小,只用来查询速度高,在内存里速度也快,进行大数据量查询时进行优化。
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语句认为在同一个事务中。(事务不结束,就无法释放锁。)
避免在触发器中做复杂操作,影响触发器性能的因素比较多,要想编写高效的触发器考虑因素比较多。