MSSQL英儿版基础整理二
--视图：是基于 SQL 语句的结果集的可视化的表--查询条件为8的[Name],Department,[Year]数据CREATE VIEW view_W ASSELECT [Name],Department,[Year] FROM W WHERE Number=8--查询“ Year ”是 “ 1990-06-12 ”数据SELECT * FROM W WHERE [Year]='1990-06-12'--返回指定列的值的数目（NULL 不计入）SELECT COUNT(Number) FROM W--总共多少条数据SELECT COUNT(*) FROM W --平均值SELECT AVG(Number) FROM W--最大值SELECT MAX(Number) FROM W--最小值SELECT MIN(Number) FROM W--总数SELECT SUM(Number) FROM W--用于结合合计函数，根据一个或多个列对结果集进行分组SELECT SUM(Number) FROM W GROUP BY Number--SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用--查找超过 1 的数量SELECT SUM(Number) FROM W GROUP BY Number HAVING SUM(Number)>1--UCASE 函数把字段的值转换为大写SELECT UCASE([Name]) as [Name],Department FROM W--[SQL05无法识别]--LCASE 函数把字段的值转换为小写SELECT LCASE([Name]) as [Name],Department FROM W--[SQL05无法识别]--MID 函数用于从文本字段中提取字符SELECT MID([Name],1,3) as [Name] FROM W--[SQL05无法识别]--LEN 函数返回文本字段中值的长度。SELECT LEN([Name]) as 姓名 FROM W --where [Name] is not null--ROUND 函数用于把数值字段舍入为指定的小数位数SELECT [Name], ROUND(Number,0) as 数 FROM W--NOW 函数返回当前的日期和时间SELECT [Name], Number, Now() as 时间 FROM W--[SQL05无法识别]--FORMAT 函数用于对字段的显示进行格式化SELECT [Name], Number, FORMAT(Now(),'YYYY-MM-DD') as 时间 FROM W--[SQL05无法识别]--FIRST() 函数返回指定的字段中第一个记录的值。SELECT FIRST(Number) AS 数 FROM W--[SQL05无法识别]--返回指定的字段中最后一个记录的值SELECT LAST([NAME]) AS 姓名 FROM Orders--[SQL05无法识别]--合计函数 www.52mvc.com（包括Aggregate 函数和Scalar 函数）SELECT function(Number) FROM W--[有误]--选取在 "[NAME]" 列中带有 NULL 值的记录SELECT [Name],Department,[Year],NUMBER FROM W WHERE [NAME] IS NULL--选取在 "[NAME]" 列中不带有 NULL 值的记录SELECT [Name],Department,[Year],NUMBER FROM W WHERE [NAME] IS NOT NULL--如果 "UnitsOnOrder" 是 NULL，则不利于计算，因此如果值是 NULL 则 ISNULL() 返回 0。SELECT [NAME],Number*(Number+ISNULL(Number,0))FROM W
select * from W --使用公用表达式做分业alter procedure pro_W@pageIndex int,@pagePage int,@Name varchar(100)as;with cte as(select *,row_number() over (order by id) as r from W where [Name] like '%'+@Name+'%')select * from cte where rbetween (@pageIndex-1)*@pagePage+1and @pageIndex*@pagePage--查询分页exec pro_W 1,8,'王'select * from W 结果:
