sql 心得

 

1select 1 from mytable;select anycol(目的表集合中的任意一行) from mytable;select * from mytable 作用上来说是没有差别的,都是查看是否有记录,一般是作条件用的。select 1 from 中的1是一常量,查到的所有行的值都是它,但从效率上来说,1>anycol>*,因为不用查字典表。

2、查看记录条数可以用select sum(1) from mytable;等价于select sum(*) from mytable;

3、实际项目SQL

select c_insrnc_cde, c_nme_cn
   from t_prd_ins a
where a.c_prod_no = '01'
    and not exists
(select 1
           from (select d.c_insrnc_cde, a.c_nme_cn
                   from t_prd_ins                   a,
                        tb_bas_ply_policy_rdr       c,
                        tb_bas_ply_policy_rdr_list d
                  where a.c_insrnc_cde = d.c_insrnc_cde
                    and d.c_policy_id = c.c_policy_id
                    and d.c_seq_no = c.c_seq_no
                    and a.c_kind_no = '01'
                    and c.c_dpt_cde = '101'   ----
机构树中取出机构

                    and c.c_prod_no = '01'
                    and c.t_effc_tm <= TO_DATE('2007-9-5', 'YYYY-MM-DD')
                    and c.t_expd_tm >= TO_DATE('2007-9-5', 'YYYY-MM-DD')
                    and c.c_work_mode = '00501'
                    and c.c_reach_area = '00701'
                    ) t
          where t.c_insrnc_cde = a.c_insrnc_cde);

测试场景:(转自网络文献)
table
表是一个数据表,假设表的行数为10行。

1select  1 from table       增加临时列,每行的列值是写在select后的数,这条sql语句中是1

2select count(1)  from table   不管count(a)a值如何变化,得出的值总是table表的行数

3select sum(1) from table   计算临时列的和

 SQL SERVER中用 1 测试了一下,发现结果如下:

1:测试结果,得出一个行数和table表行数一样的临时列(暂且这么叫,我也不知道该叫什么),每行的列值是1

2:得出一个数,该数是table表的行数;

3:得出一个数,该数是table表的行数;

然后我又用“2”测试,结果如下:

1:得出一个行数和table表行数一样的临时列,每行的列值是2

2:得出一个数,该数是table表的行数;

3:得出一个数,该数是table表的行数×2的数

然后我又用更大的数测试:

1:得出一个行数和table表行数一样的临时列,每行的列值是我写在select后的数;

2:还是得出一个数,该数是table表的行数;

3:得出一个数,该数是table表的行数×写在select后的数

    综上所述:第一种的写法是增加临时列,每行的列值是写在select后的数;第二种是不管count(a)a值如何变化,得出的值总是table表的行数;第三种是计算临时列的和。
 

 

 

 

语句if not exists(select 1 from deleted d join inserted i on d.ID=i.ID
and d.col1=1 and i.col1=2)

if not exists 如果不存在
..)这里该是验证更新,更新前的id等于更新后的id,并且更新前col的值是1,更新后的值是2

 

 

2、性能优化

Select * from table a where a.name=’zhangsan’ or a.age=17

这样查找的话需要遍历整个数据库 系统效率慢,如果在系统中建立两个索引 name age 那么 整个sql 语句可以写成

Select * from table a where a.name=’zhangsan’

Union

Select * from table a where a.age=17

 

 

心得二

--讲字符串转换成一张表

  

alter  FUNCTION [GetInStr]      

             (@SourceStr varchar(8000))--源字符串    

     

RETURNS  @table table(list  varchar(100) )       

  AS       

BEGIN     

     

  select @sourcestr =  replace(@sourcestr,';',',') 

 --select replace('2;1;3' ,';',',')    

 -- select @sourcestr = replace(@sourcestr,' ',',')     

 --declare @OutStr varchar(200)       

   if charindex(',',@sourcestr)>0 

 

    begin     

      declare @i int   --@i表示起始位置 

      declare @n int   --结束位置 

      set @i=1     

      while charindex(',',@sourcestr,@i)>0  --@i算起第一个分号在@sourcestr 存在的位置

        begin     

           set @n=charindex(',',@sourcestr,@i) --从第一个到第一个分号的长度   

           insert into @table values(substring(@sourcestr,@i, @n-@i) ) --截取第一个数字   

           set @i=@n+1     

        end     

        insert into @table values(substring(@sourcestr,@i,len(@sourcestr)-@i+1))     

    end 

 

  else

      insert into @table values(@sourcestr)      

     

  delete from @table where isnull(list,'') = ''     

return     

END     

            

     

replace(@sourcestr,';',',')  中把 变量中@sourcestr 的分号转换成,

CHARINDEX函数调用方法如下:
CHARINDEX   (   expression1   ,   expression2   [   ,   start_location   ]   )
   
Expression1
是要到expression2中寻找的字符中,start_locationCHARINDEX函数开始在expression2中找expression1的位置。
     

让我们看看下面的函数命令执行的结果:
CHARINDEX('SQL',   'Microsoft   SQL   Server')

这个函数命令将返回在“Microsoft   SQL   Server”“SQL”的起始位置,在这个例子中,CHARINDEX函数将返回“S”“Microsoft   SQL   Server”中的位置11
接下来,我们看这个CHARINDEX命令:
CHARINDEX('7.0',   'Microsoft   SQL   Server   2000')
   
在这个例子中,CHARINDEX返回零,因为字符串“7.0

     

关于sysobjects

 select 1 from sysobjects where name ='#dealswaitorderrepeat' and xtype='u' 

 

sysobjects 这个表记录一个数据库里的所有对象,包括表,索引,存储过程,触发器,等等.

type字段标志了他们的类别

type: 'P'这表示是存储过程.'S'表示是系统表,'U'表示用户自建表.等等.

name是对象名称.

 

防止数据库被删除

--下面的语句在任意数据库均可执行,控制全体数据库不允许被删除

create TRIGGER StopDropDb_DDL ON ALL SERVER

FOR DROP_DATABASE

AS

PRINT '想删除数据库?那请把防删除数据库的触发器禁用掉吧,语句如下:Disable TRIGGER StopDropDb_DDL ON ALL SERVER,删完后切记启用回这个触发器,否则数据库被误删就麻烦了,启用语句如下:Enable TRIGGER StopDropDb_DDL ON ALL SERVER'

ROLLBACK;

 

--下面的语句在任意数据库均可执行,控制指定数据库不允许被删除

create TRIGGER TR_DROP_DATABASE ON ALL SERVER

   FOR DROP_DATABASE

AS

DECLARE @data XML

SET @data = EVENTDATA()

 

if 'drop database [指定的不想被删除的数据库名]'=@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')

begin

raiserror('不允许删除此数据库,请检查TR_DROP_DATABASE触发器,或停用此触发器语句为Disable TRIGGER TR_DROP_DATABASE ON ALL SERVER,启用语句为:Enable TRIGGER TR_DROP_DATABASE ON ALL SERVER',16,1)

ROLLBACK

end

 

--下面的语句在指定要控制的数据库下执行

Create TRIGGER StopDropTable_DDL ON DATABASE

FOR DROP_TABLE

AS

set nocount on

raiserror('想删除表?那请把防删除表的触发器禁用掉吧,语句如下:Disable TRIGGER StopDropTable_DDL ON DATABASE,删完后切记启用回这个触发器,否则数据被误删就麻烦了,启用语句如下:Enable TRIGGER StopDropTable_DDL ON DATABASE',16,1)

ROLLBACK

 

 

错误提示raiserror('货号: %s 数据不全,无法生成EAN13码;请检查!',16,1,@oldcode)        

 

 

 

 

处理一个数据库死锁的异常时候,其中一个建议就是使用 NOLOCK 或者 READPAST 。有关 NOLOCK READPAST的一些技术知识点:

      对于非银行等严格要求事务的行业,搜索记录中出现或者不出现某条记录,都是在可容忍范围内,所以碰到死锁,应该首先考虑,我们业务逻辑是否能容忍出现或者不出现某些记录,而不是寻求对双方都加锁条件下如何解锁的问题。

      NOLOCK READPAST 都是处理查询、插入、删除等操作时候,如何应对锁住的数据记录。但是这时候一定要注意NOLOCK READPAST的局限性,确认你的业务逻辑可以容忍这些记录的出现或者不出现:


简单来说:

NOLOCK 可能把没有提交事务的数据也显示出来.

READPAST 会把被锁住的行不显示出来

不使用 NOLOCK READPAST ,在 Select 操作时候则有可能报错误:事务(进程 ID **)与另一个进程被死锁在 锁 资源上,并且已被选作死锁牺牲品。

下面就来演示这个情况。

为了演示两个事务死锁的情况,我们下面的测试都需要在SQL Server Management Studio中打开两个查询窗口。保证事务不被干扰。

演示一 没有提交的事务,NOLOCK READPAST处理的策略:

查询窗口一请执行如下脚本:

CREATE TABLE t1 (c1 int IDENTITY(1,1), c2 int)
go

BEGIN TRANSACTION
insert t1(c2) values(1)

在查询窗口一执行后,查询窗口二执行如下脚本:

select count(*) from t1 WITH(NOLOCK)
select count(*) from t1 WITH(READPAST)

结果与分析:

查询窗口二依次显示统计结果为: 10

查询窗口一的命令没有提交事务,所以 READPAST 不会计算没有提交事务的这一条记录,这一条被锁住了,READPAST 看不到;而NOLOCK则可以看到被锁住的这一条记录。

如果这时候我们在查询窗口二中执行:

select count(*) from t1 就会看到这个执行很久不能执行完毕,因为这个查询遇到了一个死锁。

清除掉这个测试环境,需要在查询窗口一中再执行如下语句:

ROLLBACK TRANSACTION
drop table t1

演示二:对被锁住的记录,NOLOCK READPAST处理的策略

这个演示同样需要两个查询窗口。

请在查询窗口一中执行如下语句:

CREATE TABLE t2 (UserID int , NickName nvarchar(50))
go
insert t2(UserID,NickName) values(1,'
郭红俊
')
insert t2(UserID,NickName) values(2,'
蝈蝈俊
')
go

BEGIN TRANSACTION
update t2 set NickName = '
蝈蝈俊.net' where UserID = 2

请在查询窗口二中执行如下脚本:

select * from t2 WITH(NOLOCK) where UserID = 2
select * from t2 WITH(READPAST) where UserID = 2

结果与分析:

查询窗口二中, NOLOCK 对应的查询结果中我们看到了修改后的记录,READPAST对应的查询结果中我们没有看到任何一条记录。这种情况下就可能发生脏读

 

 

一、首先你要确认你的鉴别模式: WIN NT鉴别模式呢还是混合模式,其中混合模式包括WIN NT鉴别模式和SQL SERVER 鉴别模式 实施鉴别模式的步骤 1、核实采用了可信连接 2、设置鉴别模式 3、关闭和重启MSSQLServer服务程序 4、创建WIN NT分组和用户 5、授权WIN NT分组和用户可存取SQL Server 6、为用非可信任连接的用户创建SQL Server登录帐号 二、为用户和角色分配登录帐号 三、给角色分配登录权 四、为用户和角色分配许可权限 在改进SQL Server 7.0系列所实现的安全机制的过程中,Microsoft建立了一种既灵活又强大的安全管理 机制,它能够对用户访问SQL Server服务器系统和数据库的安全进行全面地管理。按照本文介绍的步骤,你 可以为SQL Server 7.0(或2000)构造出一个灵活的、可管理的安全策略,而且它的安全性经得起考验。 一、验证方法选择 本文对验证(authentication)和授权(authorization)这两个概念作不同的解释。验证是指检验用户 的身份标识;授权是指允许用户做些什么。在本文的讨论中,验证过程在用户登录SQL Server的时候出现, 授权过程在用户试图访问数据或执行命令的时候出现。 构造安全策略的第一个步骤是确定SQL Server用哪种方式验证用户。SQL Server的验证是把一组帐户、密 码与Master数据库Sysxlogins表中的一个清单进行匹配。Windows NT/2000的验证是请求域控制器检查用户身 份的合法性。一般地,如果服务器可以访问域控制器,我们应该使用Windows NT/2000验证。域控制器可以是 Win2K服务器,也可以是NT服务器。无论在哪种情况下,SQL Server都接收到一个访问标记(Access Token)。 访问标记是在验证过程中构造出来的一个特殊列表,其中包含了用户的SID(安全标识号)以及一系列用户所 在组的SID。正如本文后面所介绍的,SQL Server以这些SID为基础授予访问权限。注意,操作系统如何构造访 问标记并不重要,SQL Server只使用访问标记中的SID。也就是说,不论你使用SQL Server 2000、SQL Server 7.0、Win2K还是NT进行验证都无关紧要,结果都一样。 如果使用SQL Server验证的登录,它最大的好处是很容易通过Enterprise Manager实现,最大的缺点在于 SQL Server验证的登录只对特定的服务器有效,也就是说,在一个多服务器的环境中管理比较困难。使用SQL Server进行验证的第二个重要的缺点是,对于每一个数据库,我们必须分别地为它管理权限。如果某个用户 对两个数据库有相同的权限要求,我们必须手工设置两个数据库的权限,或者编写脚本设置权限。如果用户数 量较少,比如25个以下,而且这些用户的权限变化不是很频繁,SQL Server验证的登录或许适用。但是,在几 乎所有的其他情况下(有一些例外情况,例如直接管理安全问题的应用),这种登录方式的管理负担将超过它 的优点。 二、Web环境中的验证 即使最好的安全策略也常常在一种情形前屈服,这种情形就是在Web应用中使用SQL Server的数据。在这 种情形下,进行验证的典型方法是把一组SQL Server登录名称和密码嵌入到Web服务器上运行的程序,比如 ASP页面或者CGI脚本;然后,由Web服务器负责验证用户,应用程序则使用它自己的登录帐户(或者是系统管 理员sa帐户,或者为了方便起见,使用Sysadmin服务器角色中的登录帐户)为用户访问数据。 这种安排有几个缺点,其中最重要的包括:它不具备对用户在服务器上的活动进行审核的能力,完全依 赖于Web应用程序实现用户验证,当SQL Server需要限定用户权限时不同的用户之间不易区别。如果你使用的 是IIS 5.0或者IIS 4.0,你可以用四种方法验证用户。第一种方法是为每一个网站和每一个虚拟目录创建一 个匿名用户的NT帐户。此后,所有应用程序登录SQL Server时都使用该安全环境。我们可以通过授予NT匿名 帐户合适的权限,改进审核和验证功能。 第二种方法是让所有网站使用Basic验证。此时,只有当用户在对话框中输入了合法的帐户和密码,IIS 才会允许他们访问页面。IIS依靠一个NT安全数据库实现登录身份验证,NT安全数据库既可以在本地服务器 上,也可以在域控制器上。当用户运行一个访问SQL Server数据库的程序或者脚本时,IIS把用户为了浏览 页面而提供的身份信息发送给服务器。如果你使用这种方法,应该记住:在通常情况下,浏览器与
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值