SQL1.1 增删改查



--创建表

CREATETABLE dbo.Employees

(

empidINTNOTNULL,

firstnameVARCHAR(30)NOTNULL,

lastnameVARCHAR(30)NOTNULL,

);

--给表添加值

Insertinto [master].[dbo].[Employees]([empid],[firstname],[lastname])values(1,'Sara','Davis')

Insertinto [master].[dbo].[Employees]([empid],[firstname],[lastname])values(2,'Don','Funk')

--添加一列'

altertable [master].[dbo].[Employees]add titleofcourtesy varchar(100)

--修改(填充值)

update [master].[dbo].[Employees]set [titleofcourtesy]='Mr.'where [empid]=1

update [master].[dbo].[Employees]set [titleofcourtesy]='Dr.'where [empid]=2

--添加与修改

Insertinto [master].[dbo].[Employees]([empid],[firstname],[lastname],[titleofcourtesy])values(9,'Zoya','Dolgopyatova','Ms.')

update [master].[dbo].[Employees]set [titleofcourtesy]='Mrs.'where [empid]=9

--判断

SELECT [empid],[firstname],[lastname],[titleofcourtesy],

case titleofcourtesy

when'Mr.'then'Male'

when'Mrs.'then'Female'

else'unknown'

endas gender into employee1 --查询结果新建一张表

from [master].[dbo].[Employees]

--删除

deletefrom tabel_name --删表

deletefrom table_name where filter --删除部分内容

droptable table_name --删除表及表结构

altertable [master].[dbo].[Employee1]dropcolumn titleofcourtesy --删除某列

--表间内连接加列

with aggr as

(

select [titleofcourtesy],count(titleofcourtesy)as num from [master].[dbo].[Employees]groupby titleofcourtesy

)

SELECT o.*,a.numfrom [master].[dbo].[Employees]as o

join aggr as a

on o.titleofcourtesy=a.titleofcourtesy

---提取XML中的值

SELECT id,name,custompropertyxml

, coalesce(CustomPropertyXml.value('(/Properties/Property[@name="Predictive Eligible"])[1]', 'nvarchar(Max)'),'') C

FROM [S_viewpoint].[ContentObject]

where isdeleted=0