【SQL Server学习笔记】INSERT、UPDATE 语句

1、INSERT语句 

              
--通过default关键字显式插入默认值
insert into Production.Location
(Name,CostRate,Availability,ModifiedDate)
values('Wheel Storage',11.25,80.00,
       DEFAULT)
       
--如整个表所有列都定义了默认值，通过default values显式插入一行默认值
insert into dbo.WC
defualt values

/*==================================================================
向表中的IDENTITY列显式插入一个值，
通过set identity_insert设置在当前会话中的某个表,可以显式插入值.
如果插入值大于当前的标识值,表的新插入会自动使用新值作为标识的种子

1.任何会话、任何作用域、指定表最后生成的标识值
ident_current('dbo.t')  当前标识值
ident_seed('dbo.t')     当前种子
IDENT_INCR('dbo.t')     当前增量

2.当前会话、任何作用域(跨作用域，可能会受到触发器的影响，
  看到触发器作用域中另外一个表的最后标识值)、任何表最后生成的标识值
@@identity

3.当前会话、当前作用域(不是作用域之外的触发器作用域)、任何表最后生成的标识值
  scope_identity()

4.如果表中有identity属性列，可通过关键字identitycol或者$IDENTITY表示列
  select IDENTITYCOL from t
  
  SELECT $IDENTITY FROM T

5.identity函数：只用于在带有INTO table子句的SELECT语句中将标识列插入到新表中。
尽管类似，但是IDENTITY函数与CREATE TABLE和ALTER TABLE一起使用的IDENTITY属性是不同的。

   IDENTITY (data_type [ , seed , increment ] ) AS column_name

====================================================================*/

create table t(idd int not null identity(1,1))
go

insert into t
default values
go 6   --批处理执行6次，添加6条记录


--潜在的问题:可以插入重复值-------
set identity_insert dbo.t on

insert into t(idd)
values(6)

set identity_insert dbo.t off
---------------------------------

--当前标识值
select IDENT_CURRENT('dbo.t')  --6

--仅当使用了列列表且identity_insert置为ON时可用
set identity_insert dbo.t on 

insert into t(idd)  --插入一个大于当前标识值的数
values(10)     

set identity_insert dbo.t off

--查看当前标识值已变为10
select IDENT_CURRENT('dbo.t')
  --显示当前最大的标识值
dbcc checkident('dbo.t',noreseed)

--重新设置新的当前的identity值
dbcc checkident('dbo.t',reseed,1) with no_infomsgs

--identity函数
select *,
       IDENTITY(int,1,1) as wcVID into wcTTT
from dbo.t

/*=====================================================================
在确保跨库、跨表的唯一性时，使用identity值可能会有主键冲突，
在表中定义uniqueidentifier数据类型的列，可以避免冲突。

1.此列存储一个16字节的全球唯一标识符GUID，能确保同一数据库、不同库跨表的唯一性，
  此值借助网卡GUID来生成，如没有网卡，SQL Server会产生一个唯一的并且固定的值，
  这个可能和其他服务器产生的值重复。  
  此值通常作为整数值键的补充，但是对大表，有时会导致查询性能较低

2.如果表中有uniqueidentifier类型列(一个表中可以指定多个此类型的列)，同时列定义中有关键字ROWGUIDCOL,
  那么可以用ROWGUIDCOL表示列(一个表只能有一个rowguidcol属性的列)
select ROWGUIDCOL from tt

3.插入这个列值可用NEWID()系统函数，此函数生成一个唯一uniqueidentifier类型的值

4.如需要每个新生成的值都大于之前生成的任何值，
  只能在create table或alter table语句中uniqueidentifier类型的列的，
  default表达式中指定NEWSEQUENTIALID()函数
======================================================================*/

create table tt(WC_ID uniqueidentifier DEFAULT (NEWID()) )

insert into tt
values(NEWSEQUENTIALID())


--newsequentialid()函数
create table tt_t(WC_ID uniqueidentifier rowguidcol DEFAULT (newsequentialid()))

insert into tt_t
values(default)
go 6

select rowguidcol from tt_t--调用存储过程插入数据
CREATE TABLE logs(database_name NVARCHAR(128),
                  log_size REAL,
                  log_space_used REAL,
                  status INT)

--只要列顺序保持一致，列名可以不写，注意数据类型必须一致
INSERT INTO logs
(database_name,log_size,log_space_used,status)
EXEC('DBCC SQLPERF(LOGSPACE)')
--使用insert...select语句添加行，用了上面建立的logs表
INSERT INTO logs
(database_name,log_size,log_space_used,status)
SELECT database_name,log_size,log_space_used,status FROM logs
--values关键字一次添加多条记录
INSERT INTO logs
(database_name,log_size,log_space_used,status)
VALUES ('pg',1.12,0.16,0),
       ('wc',3.18,8.18,0),
       ('abc',6.19,0.78,0),
       ('win',8.12,0.99,0)
       
--把values作为表的数据源使用
SELECT *
FROM
(
	VALUES ('pg',1.12,0.16,0),
	       ('wc',3.18,8.18,0),
	       ('abc',6.19,0.78,0),
	       ('win',8.12,0.99,0)
)x(a,b,c,d)  --必须有别名和列名，否则报错 
 

2、UPDATE语句

/*==================================================
如果在update中要与其他表进行关联，
那么应该在from子句中再次指定要更新的表，同时指定别名，
这个from中表只是其他关联其他表的作用

update 更新的表
set 列名 = 值
from 更新的表 as 别名1
inner join 其他表 as 别名2
        on 关联条件
where 过滤条件
        
注意：其他表也可以是CTE表达式，可以写比较复杂的语句
====================================================*/

--新增一列
alter table sales.ShoppingCartItem
add wc varchar(10) default 'wc'

UPDATE sales.ShoppingCartItem
set Quantity = 2,
    ModifiedDate = GETDATE(),
    wc = DEFAULT  --增加一列默认值
from sales.ShoppingCartItem c
inner join Production.Product p
        on c.ProductID = p.ProductID
where p.Name = 'Full-Finger Gloves,M' 
      and c.Quantity > 2

/*==================================================
对大值数据类型进行插入、更新、删除，与普通列完全一样.

update命令的.wirte方法能灵活更新大值：
      set 列名 .write(expression,@offset,@Length)

expression:要放入列的文本
@Offset:指定了新文本在现有数据中存放的起始位置,
        从0开始计算,但不能大于要在写操作中更新的列长度
        如果是NULL，表示会添加到列的末尾
@Length:指定了需要覆盖部分的长度
====================================================*/

CREATE TABLE t(v VARCHAR(max))

INSERT INTO T
VALUES('abc') 

UPDATE t
SET v .WRITE('ABC',NULL,NULL)  --追加：'abcABC'

UPDATE T
SET v .write('123',0,1)        --替换了1个字符： '123bcABC'

UPDATE T
SET v .write('xyz',9,1)        --报错，因为第二个参数偏移量大于要更新的v列长度--通过openrowset、bulk插入更新图片
create table t(vid int not null,
               pic varbinary(max) not null)

insert into t
(vid,pic)
select 1,
       BulkColumn   --这个是从数据源获取的，要插入的列，默认的列名
from openrowset(
                 BULK 'c:\DWHJ.jpg',--指定文件路劲，这里是本地路径
                      SINGLE_BLOB   --指定二进制数据是SINGLE_BLOB
                                    --指定文本是SINGLE_CLOB、SINGLE_NCLOB
               )X   --必须要别名

--引用定义的列别名               
insert into t
(vid,pic)
select 1,
       binColumn   --引用后面定义的列别名
from openrowset(
                 BULK 'c:\DWHJ.jpg',--指定文件路劲，这里是本地路径
                      SINGLE_BLOB   --指定二进制数据是SINGLE_BLOB
                                    --指定文本是SINGLE_CLOB、SINGLE_NCLOB
               )X(binColumn)   --这里还定义了列的别名
               
update t
set pic = (
		     select BulkColumn
		     from openrowset(bulk 'c:\dwhj.bmp',
		                          SINGLE_BLOB) AS X  --一定不要忘了定义别名
          )--将非结构化数据存储在文件系统中，可以超过2GB的限制
--在SQL Server内可以控制事务和可恢复性

--1.在SQL Server配置管理器中的服务中选SQL Server服务，
--右键属性，再选择FILESTREAMx选项卡，设置启动即可

--2.配置访问级别，查看服务器的文件流属性
sp_configure 'filestream access level',2
go
reconfigure with override
go

select SERVERPROPERTY('FilestreamShareName'),   
       SERVERPROPERTY('FilestreamEffectiveLevel'),
       SERVERPROPERTY('FilestreamConfiguredLevel')

--3.创建数据库，必须有一个独立的文件组，只是存放文件流数据
create database wc
on primary
(name = 'wc_data',
 filename = 'c:\wc_data.mdf'),
filegroup wc_filestream CONTAINS FILESTREAM --设置这个文件组包含文件流 
(name = 'wc_fs_data',
 filename = 'c:\wc\FILESTREAM'   --指定的目录是wc，
                                 --后面FILESTREAM必须写，但不能在wc文件夹内
                                 --不是文件
)
log on
(name = 'wc_log',
 filename = 'c:\wc_log.ldf')
go

/*================================================
4.建立一个表，在具有filestream属性的列的表中，
  列的定义必须满足条件：
  A.必须具有一个NOT NULL的、
  B.唯一的(primary key或unique)、
  C.rowguid(uniqueidentifier类型且rowguidcol属性)
==================================================*/
create table t(vid uniqueidentifier ROWGUIDCOL not null PRIMARY KEY,
               name varchar(50) not null,
               pic varbinary(max) FILESTREAM)

--5.通过openrowset、bulk添加数据
insert into t
(vid,name,pic)
select NEWID(),
       'c:\DWHJ.jpg',
       BulkColumn
from openrowset(bulk 'c:\DWHJ.jpg',
                     SINGLE_BLOB) AS X  

--6.oledb程序实现文件流
--需要用OpenSQLFileStream API、文件路径、事务标记来取得文件句柄，
--然后通过ReadFile、WriteFile API函数读取、写入文件，然后关闭文件句柄
begin tran
	select pic.PathName()   --取得文件路径名
	from t
	                        --取得事物的标记
	select GET_FILESTREAM_TRANSACTION_CONTEXT()
commit tran

--7.删除文件,可能不会马上看到文件系统下的底层文件被删除，最后会被垃圾收集进程删除
update t
set pic = NULL  


--8、如果关闭FILESTREAM属性，那么所有数据都会存储在数据库数据文件中，
--而不会存储在文件系统，列最大尺寸限制在2GB内