sql server 2005杂谈一

--创建表A
CREATE TABLE A
(
 aname varchar(20) NOT NULL,
 aid int NOT NULL PRIMARY KEY,
 yuan  nchar(10)
)
--插入数据
INSERT INTO A
SELECT '王五',0,'GOOD'
UNION ALL
SELECT '张一',2,'GOOD'
UNION ALL
SELECT '张二',5,'GOOD'
UNION ALL
SELECT '张三',8,'GOOD'
--查询A表
SELECT * FROM A

--事务的基本写法；
BEGIN TRANSACTION inserta
    INSERT INTO A VALUES ('王五',12)  ---SQL语句
 IF(@@ERROR <>0)
    BEGIN
    ROLLBACK TRANSACTION inserta
    END
 ELSE
    BEGIN
    COMMIT TRANSACTION inserta
    END
  

--触发器基本写法；
ALTER TRIGGER trigger_name
ON A
--AFTER DELETE       --在执行DElete 子后再查询A表；
--INSTEAD OF DELETE  --  在执行Delete 之后表A数据还存在,并没有执行Delete；
FOR DELETE           --在执行DElete 子后再查询A表；
AS
BEGIN
 SELECT * FROM A
END

--索引的基本用法；
 --创建聚集索引(只能创建一次)
 CREATE CLUSTERED INDEX aidindex
 ON A(aid DESC)           --aid asc升序|desc降序
 --删除聚集索引
 DROP  INDEX A.aidindex   --A为表明，aidindex为索引名
 --插叙A表
 SELECT * FROM dbo.A
 
 --创建非聚集索引（可以创建多次）
 CREATE NONCLUSTERED INDEX aidnonindex
 ON A(aid DESC)
 --删除非聚集索引
 DROP INDEX A.aidnonindex
 SELECT * FROM A
 
 --创建惟一索引类似UNIQUE约束(不容许两行具有相同的索引值)
 CREATE UNIQUE NONCLUSTERED INDEX aidunique
 ON A(aid asc)
 DROP INDEX A.aidunique
 SELECT * FROM A
  
 --主键索引（是惟一索引的特殊例子,要求被创建索引的列满足主键的要求-不能为空,数值惟一）
 ALTER TABLE A ADD CONSTRAINT aidprimary PRIMARY KEY (aid)
 DROP INDEX a.aidprimary
    SELECT * FROM A
   
    --禁用索引
    Alter INDEX aidprimary ON DBO.A DISABLE
    Alter INDEX aidprimary ON DBO.A REBUILD
   
    --为表添加Check约束
    ALTER TABLE a ADD CONSTRAINT aid CHECK(aname !='jinzhiyuan')
   
    ALTER TABLE A DROP CONSTRAINT aaname
    --添加Default约束
    ALTER TABLE A ADD CONSTRAINT aaname DEFAULT 'shanghai' for aname
    --删除表中的某列
    ALTER TABLE A DROP  COLUMN yuan
    --添加列(带默认值)
    ALTER TABLE a  ADD  yuan VARCHAR(20) NOT NULL DEFAULT('GOOD')

--查看A表的所有索引；
EXEC sp_help a
EXEC sp_helpindex a
EXEC sp_helpconstraint a

--向A表插入数据
INSERT INTO A
(
  aid
)
VALUES
(
 15 /* aid */
)
SELECT * FROM dbo.A
DELETE a WHERE aid=15

--判断表中的字段是否有主键
IF exists(SELECT 1 FROM sysobjects WHERE parent_obj=OBJECT_ID('a') AND xtype='pk')
BEGIN
PRINT('有主键') 
END
ELSE
BEGIN
print('没有主键')
END

--创建filetext表
CREATE TABLE filetext
(
 id int PRIMARY KEY,
 ftext varchar(100)
)

INSERT INTO filetext
SELECT 1,'我是水，一片汪洋的海'
union all
select 2,'测试历练'
--查询filetext表
SELECT * FROM filetext
--创建索引(惟一索引)
CREATE UNIQUE INDEX ix_usertb_id ON filetext(id)
--起用全文索引
EXEC sp_fulltext_database enable
--全文索引目录
CREATE fulltext catalog catalog_name AS DEFAULT
--创建全文索引
CREATE fulltext INDEX ON dbo.filetext(ftext) KEY INDEX ix_usertb_id
--查询全文信息
--全文检索一
SELECT * FROM filetext WHERE FREETEXT(ftext,'测试')
--全文检索二
select *from filetext where CONTAINS(ftext,'测试')
--修改表中字段的长度等
ALTER TABLE filetext alter column ftext varchar(1002)
--cuts为表的原名， filetext为要更改成的名字；
exec sp_rename 'cuts','filetext'
--将FILETEXT中的updateid 重命名为 id
exec sp_rename 'filetext.[updateid]','id','column'

--创建角色 r_test
EXEC sp_addrole 'r_test'

--授予角色r_test对filetext表的Select权限
GRANT SELECT ON filetext TO r_test

--添加登陆 1_test,设置密码为pwd,默认数据库为fulltextcs
EXEC   sp_addlogin   'jinzhiyuan', 'jinzhiyuan;','fulltextcs'

--为登录 jinzhiyuan 在数据库 pubs 中添加安全账户 u_test
EXEC sp_grantdbaccess 'jinzhiyuan','u_test'

--添加 u_test 为角色 r_test 的成员
EXEC sp_addrolemember 'r_test','u_test'

/*--完成上述步骤后,用 l_test 登录,可以对jobs表进行所有操作,但无法对titles表查询,虽然角色 r_test 有titles表的select权限,但已经在安全账户中明确拒绝了对titles的select权限,所以l_test无titles表的select权限--*/

--从数据库 pubs 中删除安全账户
EXEC sp_revokedbaccess 'u_test'

--删除登录 jinzhiyuan
EXEC sp_droplogin 'jinzhiyuan'

--删除角色 r_test
EXEC sp_droprole 'r_test'

--拒绝安全帐户 r_test 对 filetext的update 权限
deny update on filetext to r_test
 
 
 --创建表 OriginSalary
 create table OriginSalary
 (
  O_ID VARCHAR(20),
  O_Name varchar(20),
  O_Salary int
 )
 
 --创建表 AddSalary
 create table AddSalary
 (
    A_ID VARCHAR(20),
    A_Salary  int
 )
 insert into OriginSalary
 select 'Y2007001','达哥',3666
 union all
 select 'Y2007002','小杜',4888
 union all
 select 'Y2007003','小胖',6110
 
 insert into AddSalary
 select 'Y2007001',333
 union ALL
 select 'Y2007002',444
 union all
 select 'Y2007003',555
 
 select * from originsalary
 select * from addsalary
 
 
 select * from dbo.OriginSalary as A left join dbo.AddSalary as B on A.O_ID=B.A_ID
 --游标的基本用法
 EXEC PK_TEST
 CREATE PROCEDURE PK_Test
AS
--声明2个变量
declare @O_ID nvarchar(20)  
declare @A_Salary float
 
--声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同
declare mycursor cursor for select A_ID,A_Salary from AddSalary
 
--打开游标
open mycursor
 
--从游标里取出数据赋值到我们刚才声明的2个变量中
fetch next from mycursor into @O_ID,@A_Salary
 
--判断游标的状态
--0 fetch语句成功    
---1 fetch语句失败或此行不在结果集中    
---2被提取的行不存在
while (@@fetch_status=0)
begin
 
--显示出我们每次用游标取出的值
   print '游标成功取出一条数据'
   print @O_ID
   print @A_Salary
 
--用游标去取下一条记录
   fetch next from mycursor into @O_ID,@A_Salary
end
--关闭游标
close mycursor
--撤销游标
deallocate customercursor1

declare customercursor1 cursor for     --声明游标
 select * from originsalary
 open customercursor1                  --打开游标
 fetch next from customercursor1
-- while(@@fetch_status=0)
--    begin
     fetch first from customercursor1
--     if(@@error<>0)
--     begin
--          fetch next from customercursor1
--     end
--     else begin
--       break
--        end
--  end
close customercursor1                   --关闭游标
 
 --释放游标
 DEALLOCATE customercursor2

 
 
 select * from originsalary
 declare customercursor2 cursor scroll for     --声明游标
 select * from originsalary
 open customercursor2                  --打开游标
 fetch next from customercursor2       --下一条记录
 fetch last from customercursor2       --最后一条记录
 fetch prior from customercursor2      --上一条记录
 fetch first from customercursor2      --第一条记录
 fetch Absolute 3 from customercursor2 --获取第三条记录
 close customercursor2                 --关闭游标

 

--关于int类型数据显示效果 为‘001’；

alter proc praseint
@i  int
as
begin
select case when len(cast(@i as varchar))>3
       then
            cast(@i as varchar)
       else
           RIGHT('000'+CAST(@i AS Nvarchar),3)
       end
end