容易忽略的SQL语句(二)

 

1使用WITH ROLLUP来汇总数据

结合使用WITH ROLLUP和GROUP BY子句中的列的次序来增加层次化的数据汇总，WITH ROLLUP会对应每一个细项的分给进行一次汇总统计。如下条语句：

select CLPAIssueUserID,CLPADeposeJudge,COUNT(*) from CLPatientAccount group by CLPAIssueUserID,CLPADeposeJudge with rollup

2 插入默认值

  插入数据时使用默认值

INSERT INTO TABLE VALUES(DEFAULT,...)

3显示的向一个IDENTITY列插入值

要显示的把一个数字值插入到一个具有IDENTITY属性的列中，必须使用SET IDENTITY_INSERT命令。如下：

打开插入  SET   IDENTITY_INSERT  TableA  ON

插入后关闭  SET   IDENTITY_INSERT  TableA  OFF

4 使用INSERT...SELECT语句插入行

例：

insert into test select '3','c','2','200'

或

Insert into test select * from test1

4 根据FROM 和 WHERE子句更新行

UPDATE ShoppingCartItem

SET Quantity=2

MoidfyDate=GetDate()

From ShoppingCartItem c 

INNER JOIN Product  p

ON C.productid=p.productid

Where p.name='steven' and c.Qutity>2

其实FROM后面的子句只是为了过滤数据

5 TRUNCATE TABLE

TRUNCATE TABLE语句和DELETE语句差不多用于从表中删除行。TRUNCATE TABLE删除行比DELETE快，因为它记录很少的日志。和DELETE不同的是，TRUNCATE TABLE必须删除表中的所有行。

尽管TRUNCATE TABLE 能更快的删除行，但是如果表中的列具有外键约束，或者表使用事务复制或合复制发布，或者表参与了一个索引的视图则不能使用TRUNCATE TABLE。还要记住，如果有IDENTITY列，列会被重置为列定义的种子值。

6 分块删除行。

使用 DELETE TOP(n) 来实现分块删除行。如：

 while(select COUNT(*) from DSDrugFinance)>0

 begin

 delete top(10) from DSDrugFinance

 End

这种分块的方法也能够用于INSERT 和UPDATE，

INSERT TOP(100) PLUserr set col1=''

...

UPDATE TOP(25) table set column='...'

...

7 事务

DECLARE @Error int 

BEGIN TRANSACTION

INSERT INTO test VALUES ('','',DEFAULT,'')

SET @Error=@@ERROR

IF(@Error<>0) GOTO Error_Handel

COMMIT TRANSACTION

Error_Handel:

if @Error<>0

BEGIN

  ROLLBACK TRANSACTION

END

对要做的业务处理事务操作的时间尽可能的短。保持事务打开会让资源在很长一段时间处于锁定状态，这样会阻塞其他用户进行操作或读取数据

最小化由事务锁定资源。

在事务执行SELECT语句会在关联表上加锁，这样会导致其他用户/会话不能执行操作或读取数据

不要开启一个需要在事务内等待用户或外部反馈的事务。

8 创建计算列

在CREATE TABLE或ALTER TABLE中能够从独立的或者基于列的计算继承。

ALTER TABLE Production

ADD CostPerUnit AS (ActualCost/Quantity)

计算列不会保存在数据库中，加上关键字PEERSISTED后，计算列中的数据可以存储在数据库中

ALTER TABLE Production

ADD CostPerUnit AS ((ActualCost/Quantity) PERSISTED）

9 一个表能有多个外键，并且每一个外键能基于一个或者引用超过一列（引用组合主键或者唯一索引）的多重(组合)键。而且，尽管照外键和主键的列名不需要一致，但是主键/唯一列必须要有一致的数据类型。另外，你不双腿在跨数据库或者服务器的表上定义外键约束

10 创建递归外键引用

表中的外键列能定义成引用它自己的主键/唯一键。这个技术通常用于表现递归关系。

--创建递归外键引用

CREATE TABLE Company

(

 CompanyID int  NOT null primary key ,

 ParentCompanyID int null,

 CompanyName varchar(25) not null,

 CONSTRAINT FK_Company_Company

 FOREIGN KEY(ParentCompanyID)

 REFERENCES Company(CompanyID)

)

GO

INSERT INTO Company

(CompanyID,CompanyName)

VALUES

(

 1,'ANTA'

)

--插入成功

INSERT INTO Company

(CompanyID,ParentCompanyID,CompanyName)

VALUES

(

 2,1,'ERKE'

)

--插入失败，因为CompanyID为的数据

INSERT INTO Company

(CompanyID,ParentCompanyID,CompanyName)

VALUES

(

 3,8,'PEAK'

)