标准SQL Server总结

眼里的星光lzw

已于 2022-03-13 19:55:08 修改

阅读量291

点赞数

文章标签： sql 数据库 database

于 2022-03-10 22:32:47 首次发布

本文链接：https://blog.csdn.net/qq_58563355/article/details/123333965

版权

1.建库

use master
go
if exists (select * from sysdatabases where name='TestDB')
drop database TestDB
go
create database TestDB
on primary
(
    name='TestDB_data',
    filename='D:\DB\TestDB_data.mdf',
    size=10MB,
    filegrowth=1MB
)
log on
(
    name='TestDB_log',
    filename='D:\DB\TestDB_log.ldf',
    size=2MB,
    filegrowth=1MB
)
go

2.建表

use TestDB
go
if exists(select * from sysobjects where name='ProductCategory')
drop table ProductCategory
go
create table ProductCategory
(
	CategoryId int identity(1,1) primary key,
	CategoryName varchar(20) not null
)
go

2.1创建表时添加约束

if exists (select * from sysobjects where name='Products')
drop table Products
go
create table Products
(
	ProductId varchar(50) primary key,--商品编号（商品条码）
	ProductName varchar(50) not null, 
	UnitPrice numeric(18,2) not null,--总的位数为18位数字，小数点后的位数为2位
	CategoryId int  references ProductCategory (CategoryId) not null, --（商品分类）外键
    CurrentTime smalldatetime  default(getdate()) not null --默认数据库服务器时间
)
go

if exists(select * from sysobjects where name='Students')
drop table Students
go
create table Students
(
   StudentId int identity(100000,1) primary key,
   StudentName varchar(20) not null,
   Gender char(2) check(Gender='男' or Gender='女'),  --check检查约束
   DateOfBirth datetime not null ,--(Birthday)
   StudentIdNo numeric(18,0) check(len(StudentIdNo)=18) unique, --身份证要求唯一，
   --StudentIdNo char(18) check(len(StudentIdNo)=18) unique,  --实际应用中，使用char(18)
   Age int check(age>18 and age<35),
   PhoneNumber varchar(50) ,
   StudentAddress nvarchar(200) default('地址不详'),  --default是默认约束
   ClassId int references StudentClass(ClassId)   --外键约束，要求：列的数据类型和长度和主键表对应字段必须一致
)
go

2.2创建“主键”约束primary key

if exists(select * from sysobjects where name='pk_StudentId')
alter table Students drop constraint pk_StudentId

alter table Students
add constraint pk_StudentId primary key (StudentId)

2.3创建检查约束check

if exists(select * from sysobjects where name='ck_Age')
alter table Students drop constraint ck_Age
alter table Students
add constraint ck_Age check (Age between 18 and 35) 

--创建身份证的长度检查约束
if exists(select * from sysobjects where name='ck_StudentIdNo')
alter table Students drop constraint ck_StudentIdNo
alter table Students
add constraint ck_StudentIdNo check (len(StudentIdNo)=18)

2.4创建唯一约束unique

if exists(select * from sysobjects where name='uq_StudentIdNo')
alter table Students drop constraint uq_StudentIdNo
alter table Students
add constraint uq_StudentIdNo unique (StudentIdNo)

2.5创建默认约束default

if exists(select * from sysobjects where name='df_StudentAddress')
alter table Students drop constraint df_StudentAddress
alter table Students 
add constraint df_StudentAddress default ('地址不详' ) for StudentAddress

2.6创建外键约束foreign key

if exists(select * from sysobjects where name='fk_classId')
alter table Students drop constraint fk_classId
alter table Students
add constraint fk_classId foreign key (ClassId) references StudentClass(ClassId)

3.插入数据Insert

use TestDB
go
insert into Products(ProductId,ProductName,UnitPrice ,CategoryId ,CurrentTime)
values(1000,'矿泉水',2.5,1,default)

4.查询Select

//select *from 表名
select *from Product

where按条件

group by分组:select 指定的字段必须是"分组依据字段",其他字段若想出现在select中,则必须包含在聚合函数中

having:使用groupby后的,对条件进行过滤,使用having

5.修改Update

update 表名 set 字段名1=修改值1,字段名2=修改值2 where 条件

6.删除Delete

delete from 表名 where 条件

7.触发器Trigger

主要是从下面这两个临时表中获取数据

inserted:插入的临时表

deleted:删除的临时表(也就是修改之前的数据)

Insert触发器

业务场景:商品入库表增加入库信息时,需要更新库存表中对应的商品库存数据

--给商品入库表创建Insert触发器（入库信息增加的时候）
if exists(select * from sysobjects where name='ProductStorage_Insert')
drop trigger ProductStorage_Insert
go
create trigger ProductStorage_Insert
on ProductStorage after insert
as
	declare @ProductId varchar(50),@AddedCount int
	--从插入的Inserted临时表中获取数据
	select @ProductId=ProductId,@AddedCount=AddedCount from inserted
	--更新库存表中对应的商品库存数据
	update ProductInventory set TotalCount=TotalCount+@AddedCount where ProductId=@ProductId
go

增加入库信息之前

增加入库信息之后,库存表对应数量自动增加

insert into ProductStorage (ProductId,AddedCount) values('6005004003002',1)
select * from [dbo].[ProductStorage]
select * from [dbo].[ProductInventory]

update触发器

业务场景:库存表中库存数量变化时,自动更新商品的库存状态

--给库存表创建update触发器
if exists(select * from sysobjects where name='ProductInventory_update')
drop trigger ProductInventory_update
go
create trigger ProductInventory_update on ProductInventory after update
as
	declare @ProductId varchar(50),@totalCount int
	--从插入的临时表中获取商品ID和商品库存数据
	select @ProductId=ProductId,@totalCount=TotalCount from inserted
	--根据当前的库存数量更新商品的库存状态
	update ProductInventory set StatusId=case
						when @totalCount>MaxCount then 2
						when @totalCount<MinCount and @totalCount>0 then -1
						when @totalCount=0 then -2
						else 1
					end where ProductId=@ProductId
go

测试,超过库存最大数量时,状态自动更新为2

insert into ProductStorage (ProductId,AddedCount) values('6005004003002',200)
select * from [dbo].[ProductStorage]
select * from [dbo].[ProductInventory]

Delete触发器

业务场景:删除了入库信息,更新库存表中对应商品的库存

--给商品入库表创建delete触发器（删除了入库信息）
if exists(select * from sysobjects where name='ProductStorage_Delete')
drop trigger ProductStorage_Delete
go
create trigger ProductStorage_Delete on ProductStorage after delete
as
	declare @AddedCount int,@ProductId varchar(50)
	--从删除的临时表中获取已经删除的数据
	select @AddedCount=AddedCount,@ProductId=ProductId from deleted
	--更新库存表中对应商品的库存
	update ProductInventory set TotalCount=TotalCount-@AddedCount where ProductId=@ProductId
go

测试,删除入库表的StorageId=100005的记录,,库存表对应数量自动更新

delete from ProductStorage where StorageId=100005
select * from [dbo].[ProductStorage]
select * from [dbo].[ProductInventory]

8.存储过程Procedure

存储过程调用:

exec 存储过程名称参数1,参数2,....

--调用带参数默认值得存储过程
exec usp_AddProduct2'10050040031','测试商品6',60,'箱',10,100,200--后两个参数全部使用默认值
exec usp_AddProduct2'10050040032','测试商品7',65,'箱',10,100,200,150,1--都不使用默认值
exec usp_AddProduct2'10050040033','测试商品8',65,'箱',10,100,200,150--第2个使用默认值
exec usp_AddProduct2'10050040034','测试商品9',65,'箱',10,100,200,@StatusId=1--第1个使用默认值

--根据商品编号获取商品信息
if exists(select * from sysobjects where name='usp_GetProductById')
drop procedure usp_GetProductById
go
create procedure usp_GetProductById
@ProductId varchar(50)
as
	select ProductId,ProductName,UnitPrice,Unit,Products.CategoryId,CategoryName from Products
	inner join ProductCategory on ProductCategory.CategoryId=Products.CategoryId
	where ProductId=@ProductId
go

带参数默认值得存储过程

if exists(select * from sysobjects where name='usp_AddProduct2')
drop procedure usp_AddProduct2
go
create procedure usp_AddProduct2
--存储过程输入参数的定义
@ProductId varchar(50), 
@ProductName varchar(50), 
@UnitPrice numeric(18,2), 
@Unit varchar(50),
@CategoryId int,
@MinCount int, 
@MaxCount int,
@TotalCount int=0,  --可以给参数添加默认值，有默认值的参数必须放到后面，方便调用
@StatusId int=-2      --参数默认值
as
  --在商品信息表中，添加数据
  insert into Products(ProductId, ProductName, UnitPrice, Unit,  CategoryId)
  values(@ProductId, @ProductName, @UnitPrice, @Unit,  @CategoryId)

  --同时在商品库存表中，添加数据
  insert into ProductInventory(ProductId, MinCount, MaxCount, TotalCount,StatusId)
  values(@ProductId, @MinCount, @MaxCount,@TotalCount,@StatusId)
go

带输入输出参数的存储过程

if exists(select * from sysobjects where name='usp_AddProduct3')
drop procedure usp_AddProduct3
go
create procedure usp_AddProduct3
--存储过程输入参数的定义
@ProductId varchar(50), 
@ProductName varchar(50), 
@UnitPrice numeric(18,2), 
@Unit varchar(50),
@CategoryId int,
@ProductCount int output,   --定义输出参数
@MinCount int=100, 
@MaxCount int=200

as

  --在商品信息表中，添加数据
  insert into Products(ProductId, ProductName, UnitPrice, Unit,  CategoryId)
  values(@ProductId, @ProductName, @UnitPrice, @Unit,  @CategoryId)

  --同时在商品库存表中，添加数据
  insert into ProductInventory(ProductId, MinCount, MaxCount, TotalCount,StatusId)
  values(@ProductId, @MinCount, @MaxCount,0, -2)

  --获得输出参数
   select @ProductCount=count(*)  from Products
go

带输入输出参数的查询存储过程

if exists(select * from sysobjects where name='usp_Query3')
drop procedure  usp_Query3
go
create procedure  usp_Query3
@UnitPrice numeric(18,2),   --输入参数
@ProductCount int output   --输出参数
as
        --结果集查询
		select ProductName,UnitPrice from Products where UnitPrice>@UnitPrice
		select top 5 ProductId,TotalCount from ProductInventory

		--返回值查询
		 select @ProductCount=count(*)  from Products
go

8.1带输出参数

if exists(select * from sysobjects where name='usp_QueryWarningInfo')
drop procedure usp_QueryWarningInfo
go
create procedure usp_QueryWarningInfo--查询库存综合预警信息
@totalCount int output,--超出库存最高上限和最低下限的总数
@maxCount int output,--超出库存最好上限的总数
@minCount int output--超出库存最低下限的总数
as
	select @totalCount=COUNT(*) from ProductInventory
		where TotalCount>MaxCount or TotalCount<MinCount
	select @maxCount=COUNT(*) from ProductInventory
		where TotalCount>MaxCount
	select @minCount=COUNT(*) from ProductInventory
		where TotalCount<MinCount
	select Products.ProductId,ProductName,Unit,TotalCount,MaxCount,MinCount,
	InventoryStatus=case
		when TotalCount>MaxCount then '已满仓'
		when TotalCount<MinCount then '需填仓'
		end
	from Products
	inner join ProductInventory on ProductInventory.ProductId=Products.ProductId
	where TotalCount>MaxCount or TotalCount<MinCount
	order by InventoryStatus

	--存储过程测试
	declare @totalCount1 int,@maxCount1 int,@minCount1 int
	exec usp_QueryWarningInfo @totalCount1 output,@maxCount1 output,@minCount1 output
	print '预警总数:'+Convert(varchar(20),@totalCount1)
	print '超库存上限总数:'+convert(varchar(20),@maxCount1)
	print '超库存下限总数:'+convert(varchar(20),@minCount1)

使用

8.2输入参数

基于事务,下列有一步骤发生错误,则不提交(2个以上的增删改,建议使用事务)

根据输入参数更新信息库存---根据输入商品ID查询信息总量---根据查询总量更新库存状态

if(exists(select * from sysobjects where name='update_InventorySet'))
drop procedure update_InventorySet
go
create procedure update_InventorySet
@ProductId varchar(50),
@CurrentCount int,
@MaxCount int,
@MinCount int
as
	declare @errorSum int,@TotalCount int
	set @errorSum=0
	begin transaction
		begin
			--更新对应商品的库存
			update ProductInventory set TotalCount=@CurrentCount,MinCount=@MinCount,MaxCount=@MaxCount
			where ProductId=@ProductId

			set @errorSum=@errorSum+@@ERROR
			--查询当前商品的现有库存
			select @TotalCount=TotalCount from ProductInventory where ProductId=@ProductId
			--根据当前库存数量更新商品的库存状态
			update ProductInventory set StatusId=case
				when @TotalCount>MaxCount then 2
				when @TotalCount<MinCount and @TotalCount>0 then -1
				when @TotalCount=0 then -2
				else 1
				end where ProductId=@ProductId
			set @errorSum=@errorSum+@@ERROR
			if @errorSum>0
				rollback transaction
			else
				commit transaction
		end

if exists(select * from sysobjects where name='usp_UpdateInventory')
drop procedure usp_UpdateInventory
go
create procedure usp_UpdateInventory
@ProductId varchar(50),
@AddedCount int
as
	declare @errorSum int,@TotalCount int
	set @errorSum=0
	begin transaction
		begin
			--增加入库数据
			insert into ProductStorage(ProductId,AddedCount) values(@ProductId,@AddedCount)
			set @errorSum=@errorSum+@@ERROR
			--更新对应商品的库存
			update ProductInventory set TotalCount=TotalCount+@AddedCount
			where ProductId=@ProductId
			set @errorSum=@errorSum+@@ERROR
			--查询当前商品的现有库存
			select @TotalCount=TotalCount from ProductInventory where ProductId=@ProductId
			--根据当前库存数量更新商品的库存状态
			update ProductInventory set StatusId=case
				when @TotalCount>MaxCount then 2
				when @TotalCount<MinCount and @TotalCount>0 then -1
				when @TotalCount=0 then -2
				else 1
				end where ProductId=@ProductId
			set @errorSum=@errorSum+@@ERROR
			if @errorSum>0
				rollback transaction
			else
				commit transaction
		end
go

8.3分页查询

思路:当前页显示的数据条数=整个数据条数-每页显示条数*(当前页-1)

--分页查询
if exists(select * from sysobjects where name='usp_LogDataPage')
drop procedure usp_LogDataPage
go
create procedure usp_LogDataPage
@PageSize int,--每页显示多少条
@CurrentPage int,--当前第多少页
@BeginTime datetime,--开始时间
@EndTime datetime--结束时间
as
	select Top (@PageSize) LogId,LoginId,SPName,ServerName,LoginTime,ExitTime from LoginLogs
	where LogId not in(select Top (@PageSize*(@CurrentPage-1)) LogId from LoginLogs 
	where LoginTime between @BeginTime and @EndTime 
	order by LoginTime ASC)
	and LoginTime between @BeginTime and @EndTime  order by LoginTime ASC
	--查询满足条件的记录总数
	select recordCount =COUNT(*) from LoginLogs where LoginTime between @BeginTime and @EndTime
go

8.4多表操作

--添加商品信息,需要使用事务
if exists(select * from sysobjects where name='usp_AddProduct_lzw')
drop procedure usp_AddProduct_lzw
go
create procedure usp_AddProduct_lzw
@ProductId varchar(50),
@ProductName varchar(50),
@Unit varchar(20),
@UnitPrice numeric(18,2),
@CategoryId int,
@MaxCount int,
@MinCount int
as
declare @erroSum int
set @erroSum=0
begin transaction
	begin
		insert into Products(ProductId,ProductName,Unit,UnitPrice,Discount,CategoryId)
		values(@ProductId,@ProductName,@Unit,@UnitPrice,0,@CategoryId)
		set @erroSum=@erroSum+@@ERROR
		insert into ProductInventory([ProductId],[MaxCount],[MinCount],[TotalCount],[StatusId])
		values(@ProductId,@MaxCount,@MinCount,0,-2)
		set @erroSum=@erroSum+@@ERROR
		if @erroSum>0
			rollback transaction
		else
			commit transaction	
	end
go

9.视图View

--创建视图
if exists(select * from sysobjects where name='View_Test')
drop view View_Test
go

create view View_Test
as
	select Products.ProductId,ProductName,InvenStatus=case
					when TotalCount>MaxCount then '已满仓'
					when TotalCount<MaxCount then '需填仓'
					else '库存正常'
					end,Unit
	from Products
	inner join ProductInventory on Products.ProductId=ProductInventory.ProductId				
go
select * from [dbo].[View_Test]