【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内
