SQL操作

使用ALTER TABLE语句创建索引

alter table table_name add index index_name (column_list) ;
alter table table_name add unique (column_list) ;
alter table table_name add primary key (column_list) ;

删除索引

drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
8:分页存储过程sql
USE [Blog]
IF OBJECT_ID(‘Proc_Paging’, ‘P’) IS NOT NULL --删除同名存储过程
DROP PROC Proc_Paging
GO
CREATE PROC Proc_Paging
@TableFields NVARCHAR(512),
@TableName NVARCHAR(512),
@SqlWhere NVARCHAR(512),
@OrderBy NVARCHAR(64),
@PageIndex INT,
@PageSize INT,
@TotalCount INT OUTPUT
AS
DECLARE @SQL1 NVARCHAR(2048), @SQL2 NVARCHAR(2048) --@SQL1和@SQL2最好设置为比较长的字符串，否则会因为SQL语句过长而导致执行失败
–SET @SQL1 = N’SELECT TOP ’ + CONVERT(VARCHAR(2), @PageSize) + ’ * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ’ + @OrderBy + ') AS NID, ’ + @TableFields + ’ FROM ’ + @TableName + ’ WHERE ’ + @SqlWhere + ') AS TmpTable WHERE TmpTable.NID > (@PageIndex - 1) * @PageSize SELECT @TotalCount = COUNT(1) FROM ’ + @TableName + ’ WHERE ’ + @SqlWhere --用TOP关键字进行筛选
SET @SQL1 = N’SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ’ + @OrderBy + ‘) AS NID, ’ + @TableFields + ’ FROM ’ + @TableName + ’ WHERE ’ + @SqlWhere + ‘) as TmpTable WHERE TmpTable.NID BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex* @PageSize SELECT @TotalCount = COUNT(1) FROM ’ + @TableName + ’ WHERE ’ + @SqlWhere
SET @SQL2 = N’@TableFields NVARCHAR(512),@TableName NVARCHAR(512),@SqlWhere NVARCHAR(512),@OrderBy NVARCHAR(64),@PageIndex INT,@PageSize INT,@TotalCount INT OUTPUT’
EXEC SP_EXECUTESQL @SQL1, @SQL2, @TableFields, @TableName, @SqlWhere,@OrderBy,@PageIndex,@PageSize,@TotalCount OUTPUT
PRINT @SQL1 --打印执行语句

GO --测试用例
DECLARE @Count INT = 0
EXEC Proc_Paging ‘*’, ‘POSTS’, ‘PublishTime < GETDATE()’, ‘Id’, 5, 10, @Count OUTPUT --筛选发布时间小于当前时间的记录
9:写一个触发器sql
CREATE TRRIGER MYTR1
ON A
FOR INSERT
AS
DECLARE @UserName varchar(20)
SELECT @UserName=UserName from CurrentUser
INSERT INTO C (TableName,Type,dDate,UserName) VALUES (‘A’,‘Insert’,getdate(),@UserName)
GO
10:写一个存储过程包含事务的sql
ALTER proc [dbo].[Proc_InsertStudent]
@stuName nvarchar(50),@stuClassId int,@stuAge int
as
begin
set nocount on --on表示不返回计数
set xact_abort on --当执行事务时，如果出错,会将transcation设置为uncommittable状态

begin try
declare @stuCountByName int;
select @stuCountByName=count(*) from Students where Name=@stuName;

if(isnull(@stuName,’’)=’’)
begin
print(‘名字不能为空’);
return;
end

if(@stuCountByName>0)
begin
print(‘名字重复’);
return
end

begin tran --开启事务
insert into Students(Name,ClassId,Age) values(@stuName,@stuClassId,@stuAge)
commit tran --提交事务

end try

begin catch
if xact_state()=-1
rollback tran; --回滚事务
select ERROR_NUMBER() as ErrorNumber;
select ERROR_MESSAGE() as ErrorMsg;
end catch
set xact_abort off;
end
11:如何一次性往一张表【插入/更新】10条数据,存储过程实现。
UPDATE 会员表
SET 帐户分值 = 帐户分值 + t.分值
FROM 会员表,
(
SELECT 会员ID, SUM(分值) 分值
FROM 问题表
WHERE DateDiff(day,发布时间,GetDate())>10
AND 状态 = 3
GROUP BY 会员ID
) t
WHERE 会员表.ID = t.会员ID

INSERT INTO 记录表 (UID,分数,时间)
SELECT 会员ID,分值,GetDate()
FROM (
SELECT 会员ID, SUM(分值) 分值
FROM 问题表
WHERE DateDiff(day,发布时间,GetDate())>10
AND 状态 = 3
GROUP BY 会员ID
) t

UPDATE 问题表
SET 状态 = 4
WHERE DateDiff(day,发布时间,GetDate())>10
AND 状态 = 3
12:分页的实现方式？至少写3种
第一种：使用not in，select top 方法：
select top 5 * from T_user where ID not in(select top (3-1)*5 id from T_user order by ID)

说明：select top 页大小 [要查询的字段名称] from 表名 where ID not in(select top （当前页数-1）*页大小 id from 表名 order by ID)
第二种：使用select top ,max方法：
select top 5 * from T_user where(ID>=(select MAX(id)from(select top (3-1)*5+1 ID from T_user order by ID)as t))order by ID
说明:select top 页大小 [要查询的字段名称] from 表名 where(ID>=(select Max(id)from(select top(当前页数-1)*页大小+1) ID from 表名 order by ID)as t)order by ID)
第三种：如果SQLServer是2005及以上版本，可以使用ROW_NUMBER()函数进行分页：
select * from
(
select *, row_number()over(order by id) as num from T_user
)as t where t.num between (3-1)5+1 and 35
说明：select * from(select [要查询的字段名称], row_number()over(order by id) as num from 表名 )as t where t.num between (当前页-1)页大小+1 and 当前页页大小
13:写一个包含连接和分组,并且根据某个字段拼接的sql
select id,name,sum(num) count from st group by id,name;
14:写一个包含having写法的sql
SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno;
15:写一个包含连接和分组,排序的sql。
select a,b,rank() over(partition by a order by b ) nmb from test