SQL Server 基础:拾遗

1.相同长达完整的sql语句:

select top | distinct   字段, 表达式, 函数, ...
from   表表达式
where  筛选条件
group by  分组条件
having     筛选条件
order by   排序依据;

SQL的履行顺序:

–第一步:执行FROM

–第二步:WHERE条件过滤

–第三步:GROUP BY分组

–第四步:执行SELECT投影列

–第五步:HAVING条件过滤

–第六步:执行ORDER
BY 排序

2.复制表结构

select * into 新表 from 旧表 where 1 > 2;

3.DML

SQL Server 1

4.ROW_NUMBER()高效分页

            int pageIndex = 1;//第几页
            int pageSize = 1000;//每页显示条数
            int start = (pageIndex - 1) * pageSize + 1;
            int end = pageIndex * pageSize;
            string sqlQuery = string.Format("SELECT * FROM (SELECT * ,ROW_NUMBER() OVER(ORDER BY 排序字段 DESC) AS num FROM TableName ) AS t WHERE num BETWEEN {0} AND {1} ORDER BY PTime DESC", start, end);

5.创新字段

update [专家库20161222]
set 履历 = replace(履历,'
','$$')

  

相关文章