----------------------------------DML（数据操作语言）----------------------------------
-- 一、INSERT VALUES语句：将基于指定值的行插入表
-- insert into 表名(列名列表) values(值列表);
insert into Students(SName,SSex,Age) values('张某某',default,16);
-- 插入多条语句
insert into Students(SName,SSex,Age)
values('张某某',default,16),
('李某某',default,16),
('王某某',default,16);
--使用VALUES子句构建虚拟表
select * from
(
values('张某某',default,16),
('李某某',default,16),
('王某某',default,16)
) --as之后为虚拟表指定一个别名
as VTable(SName,SSex,Age) --在圆括号中为目标列指定别名
-- 二、INSERT SELECT语句：将一组由SELECT查询返回的结果行插入目标表中
--insert into 目标表(列) select 列 from Students;
insert into Table1(SName,SSex,Age) select SName,SSex,Age from Students;
-- 三、INSERT EXEC语句：把存储过程或动态SQL批处理返回的结果集插入目标表
-- insert into 目标表(列) exec 存储过程或动态SQL批处理
-- 四、SELECT INTO语句：创建一个新表，并用查询返回的结果来填充它
-- select identity(int,1,1),列名 into 新表 from 来源表
--SELECT INTO语句会复制来源表的基本结构(列名、数据类型、是否允许为NULL及IDENTITY属性)和数据，
--但不会复制约束、索引和触发器
-- 五、BULK INSERT语句：将文件中的数据导入一个已经存在的表
use tempdb
bulk insert dbo.Student from 'd:\student.txt'
with
(
datefiletype = 'char',
fieldterminator=',',
rowterminator='\n'
);
--insert 表(列) select 值 union
---------获取当前更新的标识列(得到自动增长字段的主键值)---------
--@@identity返回会话最后生成的一个标识值，而不考虑任何作用域
--insert into 表名(列名列表) values(值列表) select @@identity;
--推荐使用,scope_identity()返回当前作用域内会话生成的最后一个标识值
--insert into 表名(列名列表) values(值列表) select scope_identity();
--scope_identity()函数只能返回当前范围内会话最后生成的所有标识值，但对于一次插入多行的INSERT语句，要返回生成的所有标识值scope_identity()就帮不上多少忙了
--insert into 表名(列名列表) output inserted.Id values(值列表);
--如果项知道一个表当前的标识值而不考虑作用域，则使用IDENT_CURRENT函数，并将表名作为其输入参数
SELECT scope_identity() as [SCOPE_IDENTITY],
@@identity as [@@identity],
ident_current('tb_Craft') as [IDENT_CURRENT]
--使用通用格式来查询T表中的标识列：
select $identity from T
--output子句：在修改语句中添加output子句可以实现从修改语句中返回数据的功能
-- 语法：output inserted或deleted 列或基于现有列的表达式
-- 在INSERT语句中要引用inserted，在DELETE语句中要引用deleted；
-- 而在UPDATE语句中，如果需要更新前的行的映像，则引用deleted；如果需要更新后行的映像，则引用inserted
-- 对于在output子句中请求的各个属性，它会把来自修改过的各行中的相应值作为结果集而返回。如果想把结果集导入另一个表，可以增加一个INTO子句，并提供目标表的名称；
-- 如果既想把修改过的行返回给调用者，又想把这些数据导入另一个表，则可以指定两个output子句(一个有INTO子句，另一个没有)。
declare @NewTable table(Id int,Name nvarchar(20));
insert into dbo.Student(Name)
output inserted.Id,inserted.Name into @NewTable
values('小明');
select * from @NewTable
-----------------------------end--------------------------------
--update 表名 set 列名=更新值 where 条件
--基于联接的UPDATE
--T-SQL支持一种基于联接的UPDATE语法,这不是一种标准的SQL语法
UPDATE stu SET StuName='小明'
FROM Student as stu
JOIN Class as c ON c.Id = stu.ClassId
WHERE c.ClassName='302班';
--标准代码实现相同功能
UPDATE Student SET StuName='小明'
WHERE EXISTS
(
SELECT * FROM Class as c
WHERE Student.ClassId = c.Id AND c.ClassName='302班'
);
--delete from 表名 where 条件
--truncate table 表名 --(只删除表中的所有行，但表的结构、列、约束、索引等不会被改动；不能用于删除有外键约束引用的表)
--基于联接的DELETE
--T-SQL支持一种基于联接的DELETE语法,这不是一种标准的SQL语法
DELETE FROM stu
FROM Student as stu
JOIN Class as c ON c.Id = stu.ClassId
WHERE c.ClassName='302班';
--标准代码实现相同功能
DELETE FROM Student
WHERE EXISTS
(
SELECT * FROM Class as c
WHERE Student.ClassId = c.Id AND c.ClassName='302班'
);
--合并数据
merge into 目标表 as tgt
using 来源表 as src on 条件(如:tgt.ID = src.ID)
when mathed and(额外的条件) then
--当存在匹配时，执行INSERT、UPDATE、DELETE等操作来将来源表的数据更新操作目标表(如果ID同时在目标表和来源表中存在，就是匹配)
when not matched then
--当不存在匹配时，执行INSERT、UPDATE、DELETE等操作来将来源表的数据更新操作目标表(如果来源表的ID在目标表中不存在，就是不匹配)
when not matched by source
--当目标表中的某一行在来源表中找不到匹配行时，执行INSERT、UPDATE、DELETE等操作(如果目标表的ID在来源表中找不到时操作)
; --merge语句必须以分号结束
----------------------------------with ties、over----------------------------------
--with ties附加属性，表示额外附加与最后一条记录相等的记录
select top 5 with ties orderid,orderdate from Orders order by orderdate desc;
--SQL Server先按照orderdate desc的顺序，返回Top 5行，再从表中返回orderdate值和已经访问过的前5行中最后一行相同的其他所有行。
--over 称为开窗函数,只有在select和order by处理阶段才允许使用over子句
--over()子句会提供所有行进行计算，这里的“所有行”并不一定是在FROM子句中出现的哪些表中的所有行，而是在FROM、WHERE、GROUP BY，以及HAVING处理完成后仍然可用的那些行。
select lastname,firstname,count(*) over() as 员工总数 from employees;
--over(partition by 条件)对行进行限制或分区 partition by根据某条件，相当于做一个相等的选择，选出等于的值
select orderid,custid,val,sum(val) over(partition by custid) as 顾客总价,sum(val) over() as 总价 from OrderValues
--排名函数
--ROW_NUMBER()函数:行号，根据作为参数传递给这个函数的ORDER BY子句的值，返回一个不断递增的整数值。如果ROW_NUMBER的ORDER BY的值和结果集中的顺序相匹配，返回值将是递增的，以升序排列。如果ROW_NUMBER的ORDER BY子句的值和结果集中的顺序不同，这些值将不会按顺序列出
--RANK()函数:排名， RANK()函数保留列表中行的位置序号，对于每个重复的值，该函数会跳过下面与其相邻的值，于是就可以将下一个不重复的值保留在正确的位置上。
--DENSE_RANK()函数:密集排名,DENSE_RANK()函数的工作方式与RANK()函数相同，不过它不会跳过每个连接后的值，这样就不会有值被跳过了，但是在连接处排列序号位置将会丢失。
--NTILE(n)函数:分组编号
----------------------------------DQL（数据查询语言）----------------------------------
--写法顺序：
--select 列名 from 表名
--where 条件
--group by 列名
--having 筛选条件
--order by 列名 asc或desc
--逻辑处理顺序：
--FROM
--WHERE
--GROUP BY
--HAVING
--SELECT
-- OVER
-- DISTINCT
-- TOP
--ORDER BY
--联接查询
--交叉联接
select s.stuName,g.groupName from Student as s
cross join StudentGroup as g
--内联接
select s.stuName,g.groupName from Student as s
inner join StudentGroup as g on s.stuId = g.stuId
select s.stuName,g.groupName from Student as s
inner join StudentGroup as g on s.stuId <> g.stuId
--左外联接
select s.stuName,g.groupName from Student as s
left outer join StudentGroup as g on s.stuId = g.stuId
--右外联接
select s.stuName,g.groupName from Student as s
right outer join StudentGroup as g on s.stuId = g.stuId
--全外联接
select s.stuName,g.groupName from Student as s
full outer join StudentGroup as g on s.stuId = g.stuId
----------------------------------删除姓名、年龄重复的记录（常考）----------------------------------
Id name age salary
yzk 80 1000
yzk 80 2000
tom 20 20000
tom 20 20000
im 20 20000
--取得不重复的数据
select * from Persons
where Id in
(
SELECT MAX(Id) AS Expr1
FROM Persons
GROUP BY Name,Age --n个不重复的Name、Ages生成了n个组
)
--select distinct Name,Age from Persons
--根据姓名、年龄分组，取出每组的Id最大值，然后将Id最大值之外的排除。
--删除重复的数据：
delete from Persons
where Id not in
(
SELECT MAX(Id) AS Expr1
FROM Persons
GROUP BY Name,Age
)
----------------------------------DDL（数据定义语言,建表、建库等语句。）----------------------------------
--创建文件夹
exec xp_cmdshell 'mdir d:\MyDB'
use master
go
if exists(select * from sysdatabases where name='MySchool')
drop database MySchool
create database MySchool
on primary
(
--括号一定是圆括号
name='MySchool_data', --数据库名称
filename='d:\MySchool_data.mdf',--物理文件名
size=3mb, --初始大小
maxsize=10mb, --最大大小
filegrowth=15% --主文件增长率
)
log on
(
name='MySchool_log', --日志文件名
filename='d:\MySchool_log.ldf', --日志物理文件名
size=1mb, --初始大小
maxsize=4mb, --最大大小
filegrowth=1mb
)
go --和后续的SQL语句分隔开
--db_id函数接受一个数据库名称作为输入，返回它的内部数据库ID。如果输入名称指定的数据库不存在，返回NULL
if db_id('testDB') is null
create database testDB
use MySchool
go
if exists(select * from sysobjects where name='Student')
drop table Student
create table Student --创建学生信息表
(
sId int identity(1,1) not null primary key,--自动编号
sClassId int not null, --班级外键
sName nvarchar(50) not null,
sAge int not null,
sNo numeric(18,0),
sSex char(2) not null,
sEmail varchar(50)
)
-- object_id函数接受一个对象名称和类型作为它的输入，类型'U'代表用户表。
--如果匹配给定输入名称和类型的对象存在，返回内部的对象ID，否则返回NULL
if object_id('Student','U') is not null
drop table Student;
----------------------------------约束----------------------------------
--主键约束(PK) primary key constraint 唯一且不为空
--非空约束 Not NULL 列不能为空
--唯一约束 (UQ)unique constraint 唯一，允许为空，但只能出现一次
--默认约束 (DF)default constraint 默认值
--检查约束 (CK)check constraint 范围以及格式限制
--外键约束 (FK)foreign key constraint 表关系
--添加主键约束
alter table Score
add constraint PK_Score primary key(sId)
--添加唯一约束
alter table student
add constraint UQ_student unique(sNo)
--非空约束,为EmpName增加一个非空约束
alter table Employees
alter column EmpName varchar(50) not null
--添加默认约束
alter table student
add constraint DF_student default('男') for sSex
--添加检查约束
alter table student
add constraint CK_student check(sAge >=18 and sAge <=100)
--添加外键约束（主键表Class 外键表student）
--alter table 外键表
--add constraint 约束名
--foreign key(外键列) references 主键表(主键列)
alter table student
add constraint FK_student
foreign key(sClassId) references Class(cId) --外键student表中的sClassId来references引用主键表中的cid
--设置主外键关系的级联删除
alter table Employees
add constraint FK_Employees_Department_DepId
foreign key(DepId) references Department(DepId)
on delete CASCADE --设置级联删除
--on update cascade --设置级联更新
--删除约束
alter table student
drop constraint FK_student
--手动删除一列(删除EmpAddress列)
alter table Employees drop column EmpAddress
--手动增加一列(增加一列EmpAddr varchar(500))
alter table Employees add EmpAddr11 varchar(500)
--手动修改一下EmpEmail的数据类型(varchar(200))
alter table Employees alter column EmpAddr varchar(1000)
----------------------------------视图----------------------------------
--获取视图定义的文本
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.tables'))
EXEC sp_helptext 'sys.tables'
--系统视图
select * from sys.tables
select * from sys.objects
--系统表
--select * from sysobjects
--判断数据库名称和表名称是否存在
use master
go
if exists(select * from sys.databases where name='demo')
drop database demo
create database demo
go
use demo
go
if exists(select * from sys.objects where name='test')
drop table test
create table test
(
tId int identity(1,1) primary key,
tName nvarchar(10)
)
if exists(select * from sys.objects where name='UQ_test_tName')
alter table test drop constraint UQ_test_tName
alter table test
add constraint UQ_test_tName unique(tName)
--创建视图
use MySchool
if exists(select * from sys.objects where name='v_Student')
drop view v_Student
go
create view v_Student
as
select sName,sAge,sSex from student
--查看视图
select * from v_Student
---------------索引视图---------------
create view vw_ix_T1
WITH SCHEMABINDING
as
select autoid,uname,usrId from dbo.T1
create unique clustered index ix_vw_t1 on vw_ix_T1(autoId)
----------------------------------索引----------------------------------
--系统表
select * from sysindexes
--系统视图
select * from sys.indexes
use MySchool
go
if exists(select name from sysindexes where name='ix_StedentName')
drop index 表名.ix_StudentName
--唯一索引、聚集索引|非聚集索引
create [unique] [clustered | nonclustered] index 索引名
on 表(列) with fillfactor=数字
--指定按索引查询
selec * from Student with(index=ix_StedentName) where StudentName like '李%'
----------------------------------局部变量_必须以标记@作为前缀，先声明再赋值----------------------------------
--DECLARE @变量名 数据类型
declare @name nvarchar(10)
declare @id int
--赋值：SET @变量名=值 --set用于普通的赋值；SELECT @变量名=值 --用于从表中查询数据并赋值，可以一次给多个变量赋值
set @name='张三'
set @id = 1
select @name
select @id
declare @name nvarchar(10),@id int
--set只能对一个变量赋值
--set @name='张三'
--set @id=1
--select 可以同时对多个变量赋值
select @name='张三',@id=1
--在查询语句中对变量赋值
select @name=sName,@id=sId from student
----------------------------------输出变量的值----------------------------------
--SELECT 以表格的方式输出,可以同时输出多个变量
--PRINT 以文本的方式输出，一次只能输出一个变量的值
select @name,@id
print @name
print @id
----------------------------------全局变量（系统变量）必须以标记@@作为前缀----------------------------------
select @@error --最后一个T-SQL错误的错误号
insert into class values(1,'','')
select @@identity --最后一次插入的标识值
select @@servername --本地服务器的名称
select @@version --SQL Server的版本信息
select @@LANGUAGE --当前使用的语言的名称
select @@TRANCOUNT --当前连接打开的事务数
select @@rowcount --受上一个SQL语句影响的行数
print 'SQLServer的版本'+@@VERSION
print '服务器名称: '+@@SERVERNAME
print '最后一次放生的错误号'+convert(varchar(5),@@ERROR)
print @@identity
----------------------------------条件结构----------------------------------
--if else语句
IF(条件表达式)
BEGIN --相当于C#里的{
语句1
END --相当于C#里的}
ELSE
BEGIN
语句2
END
--计算平均分数并输出，如果平均分数超过60分输出成绩最高的三个学生的成绩，否则输出后三名的学生
declare @avg float
select @avg=avg(english) from score
if(@avg >= 70)
print '平均分数超过70'
else
if(@avg >= 60)
begin
print '成绩最高的三个学生'
select top 3 * from score order by english desc
end
else
begin
print '成绩最低的三个学生'
select top 3 * from score order by english asc
end
--while语句
WHILE(条件表达式)
BEGIN --相当于C#里的{
语句
BREAK
END --相当于C#里的}
declare @i int
set @i = 0
while(@i < 1000000)
begin
set @i = @i + 1
insert into score values(1,@i,@i)
end
select * from score
----------------------------------事务(Transaction)----------------------------------
--开始事务：BEGIN TRANSACTION
--事务提交：COMMIT TRANSACTION
--事务回滚：ROLLBACK TRANSACTION
declare @sumError int
set @sumError = 0
--设置事务回滚到原点
SET XACT_ABORT on
begin tran --transaction
update bank set balance = balance + 1000 where cId = ''
set @sumError = @sumError + @@error
update bank set balance = balance - 1000 where cid=''
set @sumError = @sumError + @@error
--事务执行成功 提交事务
if(@sumError = 0)
commit tran
--事务执行失败，回滚
else
rollback tran
select @sumError
----------------------------------存储过程----------------------------------
--系统存储过程
--由系统定义，存放在master数据库中
--名称以“sp_”开头或”xp_”开头
--自定义存储过程
--由用户在自己的数据库中创建的存储过程
sp_databases --列出服务器上的所有数据库。
sp_helpdb --报告有关指定数据库或所有数据库的信息
sp_renamedb --更改数据库的名称
sp_tables --返回当前环境下可查询的对象的列表
sp_columns --回某个表列的信息
sp_help --查看某个表的所有信息
sp_helpconstraint --查看某个表的约束
sp_helpindex --查看某个表的索引
sp_stored_procedures--列出当前环境中的所有存储过程。
sp_password --添加或修改登录帐户的密码。
sp_helptext --显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。
--通过xp_cmdshell执行DOS命令
xp_cmdshell 'dir c:' --查询c盘目录
xp_cmdshell 'net user abc password:123 /add' --创建WindowXP用户
xp_cmdshell 'md c:\abc' --创建文件夹
exec sp_databases --列出当前系统中的数据库
exec sp_renamedb 'demo','test' --改变数据库名称
exec sp_tables --查看当前数据库中可查询对象的列表
exec sp_help Student --查看表Student的所有信息
exec sp_helpconstraint Student --查看表Student的约束
exec sp_helptext CK_Student_sAge
exec sp_helptext v_Student --查看视图的语句文本
exec sp_helptext 'sys.objects'
--附加数据库
sp_attach_db
exec sp_attach_db @dbname='test',@filename1='C:\test.mdf',@filename2='C:\test_log.LDF'
--分离数据库
use master
exec sp_detach_db test
--创建存储过程
定义存储过程的语法
CREATE PROC[EDURE] 存储过程名
@参数1 数据类型 = 默认值 OUTPUT,
@参数n 数据类型 = 默认值 OUTPUT
AS
SQL语句
--执行存储过程
EXEC 过程名 [参数]
--创建带默认值参数的存储过程
if exists(select * from sys.objects where name='usp_upGrade')
drop proc usp_upGrade
go
create proc usp_upGrade
@passEnglish float = 60,
@passMath float = 60
as
declare @count int,@english int,@math int
select @count=count(*) from score
select @english=count(*) from score where english < @passEnglish
select @math=count(*) from score where math < @passMath
print '英语不及格的人数' + Convert(varchar,@english)
print '数学不及格人数' + Convert(varchar,@math)
go
exec sp_helptext usp_upGrade
--调用存储过程
exec usp_upGrade 60,30
exec usp_upGrade @passEnglish=70,@passMath=30
exec usp_upGrade @passMath=30 --英语及格分数线用默认值
--存储过程输出值
if exists(select * from sys.objects where name='usp_upGrade')
drop proc usp_upGrade
go
create proc usp_upGrade
@passEnglish float = 60,
@passMath float = 60
as
declare @count int,@english int,@math int
select @count=count(*) from score
select @english=count(*) from score where english < @passEnglish
select @math=count(*) from score where math < @passMath
print '英语不及格的人数' + Convert(varchar,@english)
print '数学不及格人数' + Convert(varchar,@math)
return @count
--调用有返回值的存储过程
declare @num int
exec @num = usp_upGrade
print @num
--带输出参数的存储过程
if exists(select * from sys.objects where name='usp_upGrade')
drop proc usp_upGrade
go
create proc usp_upGrade
@c int output,
@e int output,
@m int output,
@passEnglish float = 60,
@passMath float = 60
as
declare @count int,@english int,@math int
select @count=count(*) from score
select @english=count(*) from score where english < @passEnglish
select @math=count(*) from score where math < @passMath
print '英语不及格的人数' + Convert(varchar,@english)
print '数学不及格人数' + Convert(varchar,@math)
set @c = @count
set @e = @english
set @m = @math
--调用有输出参数的存储过程
declare @count int,@english int,@math int
exec usp_upGrade @count output,@english output,@math output
select @count,@english,@math
--分页
select top @pageSize * from tb_ProduceOrder
where Id not in (select top @pageSize*(@pageIndex-1) Id from tb_ProduceOrder)
--分页存储过程
if exists(select * from sys.objects where name='usp_GetPageData')
drop proc usp_GetPageData
go
create proc usp_GetPageData
@pageSize int, --一页多少条数据
@pageIndex int, --第几页
@pageCount int output --共多少页
as
declare @count int
select * from
(select row_number() over(order by sId desc) as num,* from student) as t
where num between (@pageSize*(@pageIndex-1) + 1) and (@pageSize*@pageIndex)
order by sId desc
select @count = count(*) from student
--求总共多少页
set @pageCount = ceiling(@count/convert(float,@pageSize))
--执行分页存储过程
declare @count int
exec usp_GetPageData 3,3,@count output
print @count
select ceiling(7/convert(float,3))
--分页查询
--当@pageIndex!=1时
select top @pageSize *
from tb_Customers
where cust_id <
(select min(cust_id) from (select top @pageSize*(@pageIndex-1) cust_id from tb_Customers @strWhere order by cust_id asc) as c) @strWhere
order by c.cust_id desc
select top @pageSize *
from tb_Customers
where cust_id >
(select max(cust_id) from (select top @pageSize*(@pageIndex-1) cust_id from tb_Customers @strWhere order by cust_id asc) as c) @strWhere
order by c.cust_id asc
--当@pageIndex=1时
select top @pageSize *
from tb_Customers
@strWhere
order by c.cust_id
--示例如下：
CREATE PROCEDURE pro_CustOrders_select
@CustCode nvarchar(50),
@Id int=0,
@ProductCode nvarchar(50)=null,
@UserName nvarchar(50)=null,
@IsVerify int=-1,
@BeginDate nvarchar(50)=null,
@EndDate nvarchar(50)=null,
@PageIndex int=0,
@PageSize int=30,
@TotalCount int output
AS
BEGIN
declare @strSql nvarchar(4000)
declare @strWhere nvarchar(2000)
declare @strRowCount nvarchar(500)
set @strWhere=' where 1=1 '
if(@CustCode is null)
begin
select null
set @TotalCount=0
return
end
else
begin
set @strWhere=@strWhere+' and CustCode like ''%'+@CustCode+'%'''
end
if(@Id <> 0)
set @strWhere=@strWhere+' and Id like ''%'+CAST(@Id AS nvarchar(20))+'%'''
if(@ProductCode is not null)
set @strWhere=@strWhere+' and ProductCode like ''%'+@ProductCode+'%'''
if(@UserName is not null)
set @strWhere=@strWhere+' and UserName like ''%'+@UserName+'%'''
if(@IsVerify <> -1)
set @strWhere=@strWhere+' and IsVerify='+CAST(@IsVerify AS nvarchar(5))
if((@BeginDate is not null) and (@EndDate is not null))
set @strWhere=@strWhere+' and convert(nvarchar(10),CustOrders.OrderDate,21) >= '''+@BeginDate+''' and convert(nvarchar(10),CustOrders.OrderDate,21) <= '''+@EndDate+''''
if(@PageIndex = 1)
SET @strSql='select top '+CAST(@PageSize AS nvarchar(20))+' * from CustOrders '+@strWhere+' order by CustOrders.Id asc'
else
SET @strSql='select top '+CAST(@PageSize AS nvarchar(20))+' * from CustOrders where Id > (select max(Id) from (select top '+CAST(@PageSize*(@PageIndex-1) AS nvarchar(20))+' Id from CustOrders '+@strWhere+' order by Id asc) as T)'+REPLACE(@strWhere,'where 1=1','') +' order by CustOrders.Id asc'
execute(@strSql)
print @strSql
set @strRowCount = 'select @TotalCount=count(*) from CustOrders '+@strWhere -- 返回总行数
exec sp_executesql @strRowCount,N'@TotalCount int out',@TotalCount out
END
--分页查询二：
CREATE PROCEDURE pro_CustFile_select
@CustCode nvarchar(50),
@ModelName nvarchar(200)=null,
@FileAddress1 nvarchar(500)=null,
@PageIndex int=1,
@PageSize int=30,
@TotalCount int output
AS
BEGIN
declare @strSql nvarchar(4000)
declare @strWhere nvarchar(2000)
declare @strRowCount nvarchar(500)
set @strWhere=''
if(@CustCode is null)
begin
select null
set @TotalCount=0
return
end
else
begin
set @strWhere=@strWhere+' where CustCode = '''+@CustCode+''''
if(@ModelName is not null)
set @strWhere=@strWhere+' and ModelName like ''%'+@ModelName+'%'''
if(@FileAddress1 is not null)
set @strWhere=@strWhere+' and FileAddress1 like ''%'+@FileAddress1+'%'''
SET @strSql='select * from (select row_number() over(order by Id asc) as rowNum,* from CustFile '+@strWhere
+' ) as T where T.rowNum > '+CAST(@PageSize*(@Pageindex-1) AS nvarchar(20))+' and T.rowNum <= '+CAST(@PageSize*@PageIndex AS nvarchar(20))+' order by Id asc'
execute(@strSql)
print @strSql
set @strRowCount = 'select @TotalCount=count(*) from CustFile '+@strWhere -- 返回总行数
exec sp_executesql @strRowCount,N'@TotalCount int out',@TotalCount out
end
END
--分页查询三：
-- 获取会员的兑换券
CREATE PROCEDURE pro_GetMemberOrderCouponItemByPage
@MemberId int,
@CouponStatus int=0,
@OrderCode nvarchar(20)='',
@PageIndex int=1,
@PageSize int=20,
@TotalCount int out
AS
BEGIN
declare @lowerLimit int --下限(最小的限度)
declare @upperLimit int --上限(最大的限度)
set @lowerLimit=(@PageIndex-1)*@PageSize;
set @upperLimit=@PageIndex*@PageSize;
set @TotalCount=0;
select Id
,OrderId
,CreateDate
,OrderCode
,SafetyCode
,CouponCode
,MemberId
,CouponStatus
,ValidDateStart
,ValidDateEnd
,UseDate
,OpShopIdORSupplierId
from (
select ROW_NUMBER() over(order by Id) as rowNum
,Id
,OrderId
,CreateDate
,OrderCode
,SafetyCode
,CouponCode
,MemberId
,CouponStatus
,ValidDateStart
,ValidDateEnd
,UseDate
,OpShopIdORSupplierId
from Order_OrderCouponItem
where MemberId=@MemberId
and (@CouponStatus=0 or CouponStatus=@CouponStatus)
and (@OrderCode='' or OrderCode=@OrderCode)
) as T
where T.rowNum between @lowerLimit+1 and @upperLimit
select @TotalCount=count(*) from Order_OrderCouponItem
where MemberId=@MemberId and (CouponStatus=0 or CouponStatus=@CouponStatus)
END
GO
----------------------------------触发器----------------------------------
CREATE TRIGGER triggerName ON 表名
{after|for(for与after都表示after触发器)|instead of}
UPDATE|INSERT|DELETE
AS
begin
…
end
--创建触发器
create trigger tr_insertStudent
on score
for insert
as
declare @stuId int,@sId int
select @stuId = studentId,@sId=sId from inserted
if not exists(select * from student where sId=@stuId)
delete from score where sId=@sId
else
print '插入成功'
--当插入数据的时候就会引发触发器
insert into score(studentId,english) values(1,10)