摘要：
USE AdventureWorks-----使用 CROSS APPLY -----------创建表值函数-----CREATE FUNCTION dbo.fn_WorkOrderRouting(@WorkOrderID int) RETURNS TABLEASRETURNSELECT WorkOrderID,ProductID,OperationSequence,LocationIDFROM Production.WorkOrderRoutingWHERE WorkOrderID = @WorkOrderIDGO-----将Production.WorkOrder表中的WorkOrde. 阅读全文
摘要：
在表中不允许执行某些insert操作 USE [AdventureWorks]GOCREATE TRIGGER [Sales].[]iStore].[Store]AFTER INSERT ASBEGINSET NOCOUNT ON;BEGIN TRY --判断是否插入记录的CustomerID字段是否已经在[Sales].[Individua]表中存在相同的记录IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[individua]ON inserted.[CustomerID] = [Sales].[individua].[Custo. 阅读全文
摘要：
USE [AdventureWorks]GOCREATE TRIGGER [HumanResources].[dEmployee] ON [HumanResouces].[Employee] INSTEAD OF DELETE NOT FOR REPLICATION AS BEGIN SET NOCOUNT ON;DECLARE @DeleteCount int;SELECT @DeleteCount = COUNT(*) FROM deleted;IF @DeleteCount > 0 BEGINRAISERROR(N'Employees cannot be deleted . 阅读全文
摘要：
-----------创建 AFTER DML 触发器----------CREATE TRIGGER [ scema_name . ]trigger_name ----可选的架构拥有者以及必需的用户定义的新触发器的名字on table ----------应用触发器的表名--------允许你指定ENCRYPTION和/EXECUTE AS子句。ENCRYPTION回加密T-SQL的定义[ WITH [...,n]]AFTER {[INSERT][,][UPDATE] [,][DELETE]}[NOT FOR REPLICATION]AS{sql_statement[...n]} ----- 阅读全文
摘要：
-----内联结3个表返回产品的折扣信息-----SELECT p.Name,s.DiscountPct from Sales.SpecialOffer sinner JOIN Sales.SpecialOfferProduct o ONs.SpecialOfferID=o.SpecialOfferIDINNER JOIN Production.Product p ONo.ProductID=p.ProductIDWHERE p.Name ='All-Purpose Bike Stand'-----外联结2个表返回税率状态、省-----SELECT s.CountryRegio 阅读全文
摘要：
------使用distinct消除重复值----use AdventureWorksselect distinct HireDatefrom HumanResources.Employee-----在聚合函数中使用distinct ----------如果你想计算产品列表的平均价格，可以使用如下查询语句----select AVG(ListPrice) from Production.Product-----使用列别名-----select Color as 'Grouped Color', AVG(DISTINCT ListPrice) as 'Average Di 阅读全文