SQL SERVER 常见SQL和函数使用

一、语法

参考原文：https://blog.csdn.net/xushaozhang/article/details/55053037

1.查询插入

（1）SELECT INTO 

语句格式：
　　Oracle:Create Table2 as Select column1,column2……From Table1 或 Create Table2 as Select * From Table1

　　MS SqlServer:Select column1,column2…… into Table2 From Table1 或 Select * into Table2 From Table1

　　Table2不存在的时候进行表数据复制，编译器会根据Table1的表结构自动创建Table2，Table2和Table1的结构基本上是一致的，但是如果已经存在Table2，则编译器会报错.

 

 

（2）INSERT INTO SELECT

语句格式：Insert Into Table2(column1,column2……) Select value1,value2,value3,value4 From Table1 或 Insert Into Table2 Select * From Table1

　　表复制必须要求Table2是事先创建好的；注意Table2的主键约束，如果Table2有主键而且不为空，则column1， column2...中必须包括主键；另外，注意语法，不要加values；

 

2.查询更新

原文：https://blog.csdn.net/xcbsdu/article/details/6736503

  
MS SQL Server的语法为：
UPDATE A  SET A1 = B1, A2 = B2, A3 = B3  FROM A LEFT JOIN B ON A.ID = B.ID

Oralce和DB2支持的语法：
UPDATE A  SET (A1, A2, A3) = (SELECT B1, B2, B3 FROM B WHERE A.ID = B.ID)

 

 

二、函数

1.字符串转化为整型

 CONVERT(INT,'字符串')

 

2.结果集 输出为一段字符串

SELECT STUFF((SELECT ','+A FROM tableFOR XML PATH('')),1,1,'')

 

 

三、数据结构 

1 查询所有表

select top 1000
ROW_NUMBER() OVER (ORDER BY a.name) AS no,
a.name,
CONVERT(NVARCHAR(100),isnull(g.[value],'')) AS description
from
sys.tables a left join sys.extended_properties g
on (a.object_id = g.major_id AND g.minor_id = 0)

 

2 查询所有列

SELECT (case when a.colorder=1 then d.name else null end) 表名,
a.colorder 字段序号,a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
(case when (SELECT count(*) FROM sysobjects
WHERE (name in (SELECT name FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))
AND (xtype = 'PK'))>0 then '√' else '' end) 主键,b.name 类型,a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '√'else '' end) 允许空,
isnull(e.text,'') 默认值,isnull(g.[value], ' ') AS [说明]
FROM syscolumns a
left join systypes b on a.xtype=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.class and f.minor_id=0
where b.name is not null
--WHERE d.name='要查询的表' --如果只查询指定表,加上此条件
order by a.id,a.colorder