sqlserver基本操作语法

sqlserver基本操作语法

写在前面
sqlserver的基本语法，和Orcal、mysql还是有些区别的。具体的区别会在另一个篇幅去解读。这里我主要结合实时工作应用总结一下sqlserver数据库的基本操作。

1.数据表备份

select * into A_tmp from A ； --A表数据库的表、A_tmp 是备份表

2.表结构修改

• 修改字段类型
  alter table A alter COLUMN A.clmn varchar(80);
• 修改表名：
  EXEC sp_rename ‘原有表名’, ‘新表名’;
• 修改列名：
  EXEC sp_rename ‘表名.[原有列名]’, ‘新列名’ , ‘COLUMN’;
• 修改主键
  思路是先删除主键、修改新的主键列为非空，添加主键
  alter table A drop constraint PK_A ; --PK_TableName原主键名称，删除原来的主键
  alter table A alter column A.clmn varchar(60) not null ;–修改数据类型
  alter table A add constraint PK_A
  primary key clustered(clmn1,clmn2,clmn); --重新创建主键
• 添加表字段注释
  execute sp_addextendedproperty ‘MS_Description’,‘列1’,‘user’,‘dbo’,‘table’,‘LES_EP_FX’,‘column’,‘CURR1’;

3.锁表与解锁

• 查看锁表

       select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
       from   sys.dm_tran_locks where resource_type='OBJECT';
- 解锁
      declare @spid int 
      Set @spid = 66 --锁表进程
      declare @sql varchar(1000)
      set @sql='kill '+cast(@spid as varchar)
      exec(@sql)

4.查看所有表及字段注释

SELECT
     表名       = Case When A.colorder=1 Then D.name Else '' End,
     表说明     = Case When A.colorder=1 Then isnull(convert(varchar(100),F.value),'') Else '' End,
     --字段序号   = A.colorder,
     字段名     = A.name,
     字段说明   = isnull(convert(varchar(100),G.[value]),'')
     --标识       = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,
   --  主键       = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (
   --                   SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end,
   --  类型       = B.name,
   --  占用字节数 = A.Length,
    -- 长度       = COLUMNPROPERTY(A.id,A.name,'PRECISION'),
    -- 小数位数   = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),
    -- 允许空     = Case When A.isnullable=1 Then '√'Else '' End,
    -- 默认值     = isnull(E.Text,'')
FROM
	syscolumns A
Left Join systypes B On
	A.xusertype = B.xusertype
Inner Join sysobjects D On
	A.id = D.id
	and D.xtype = 'U'
	and D.name <> 'dtproperties'
Left Join syscomments E on
	A.cdefault = E.id
Left Join sys.extended_properties G on
	A.id = G.major_id
	and A.colid = G.minor_id
Left Join sys.extended_properties F On
	D.id = F.major_id
	and F.minor_id = 0
	-- where d.name='table_name'    --如果只查询指定表,加上此条件
Order By
	A.id,
	A.colorder ;

5.字段类别对比

sql server中的varchar和Nvarchar有什么区别？

**varchar(n)：**长度为 n 个字节的可变长度且非 Unicode 的字符数据。n 必须是一个介于 1 和 8,000 之间的数值。存储大小为输入数据的字节的实际长度，而不是 n 个字节。
**nvarchar(n)：**包含 n 个字符的可变长度 Unicode 字符数据。n 的值必须介于 1 与 4,000 之间。字节的存储大小是所输入字符个数的两倍。
–例：两字段分别有字段值：我和coffee
–那么varchar字段占2×2+6=10个字节的存储空间，而nvarchar字段占8×2=16个字节的存储空间。
结论：如字段值只是英文可选择varchar，而字段值存在较多的双字节（中文、韩文等）字符时用nvarchar

datetime 和date的区别

**date类型：**是SQL Server 2008开始新引进的一种数据类型，他只包含日期 不包含小数部分 可以表示的日期范围为 0001 年 1 月 1 日 -到 9999 年 12 月 31 日 需要3个字节的存储空间
**datetime：**包含日期和时间部分，可以表示的范围为1753年1月1日 00:00:00到9999年12月31日 23:59:59:997 精度为 3.33 毫秒 占8个字节
–默认时间 1900-01-01 00:00:00
结论：存储年月日格式，用date类型，存储年年月日时分秒类型用datetime

6.查看数据库所有表占用空间

   select
A.REC_RCODE,
A.PAY_RCODE,
  case
    when A.REC_RCODE = 'FIXED'
    and A.PAY_RCODE = '' then '02'
    when A.REC_RCODE = ''
    and A.PAY_RCODE = 'FIXED' then '01'
    else null end AS BUYERSellr 
from
  les_data.dbo.LES_DERIVATIVE_MTM A
where
  A.GL_PRODUCT_CODE in ( '320','590');

select object_name(id) tablename,8*reserved/1024 reserved,rtrim(8*dpages/1024)+'Mb' used,8*(reserved-dpages)/1024 unused,8*dpages/1024-rows/1024*minlen/1024 free,
rows from sysindexes
where indid=1
order by reserved desc 

#####7.一些常用的函数

• 去除字符空格 LTrim(RTrim( ))
• 生成8为随机数 RIGHT(100000000 + CONVERT(bigint, ABS(CHECKSUM(NEWID()))), 8)
• 将字符串格式的 2019-06-30 转为 20190630 convert(char(8),A.dt_date,112) AS dt_date