常用SQL语句总结(1)

插入

insert into tableName values()，当不指定插入的列时默认对所有列插入值，但自增的Id列除外；当对指定的列插入值时，那值、列的顺序、数量都要对应。

/*插入单行*/
1、insert into Scores values(2016001,89,86,96)
//插入时，只要格式符合要求所有非文本值都能以文本方式插入
2、insert into Scores(StudentId, CSharp, SqlServer, C语言) values(2016001,'89','86','96')
3、insert into dbo.StudentClass(ClassId,ClassName)
select StudentId,UserName from dbo.Students where StudentId=2016001

//插入时间时可以用getdate()函数，注意不要加单引号''
insert into Students(StudentId,StudentName,AddTime)values
(2016006,'蝈蝈',getdate())

/*当表名以数字开头时,必须用把[]表名括起来,增删查改操作都要括起来*/
1、insert into [360Scores] values(2016001,'89','86','96')
2、insert into [360Scores](StudentId, CSharp, SqlServer, C语言) values(2016001,89,86,96)

//Sql2008插入多行
insert into Scores values
(2016002,'85','92','91'),
(2016003,'91','93','83'),
(2016004,88,95,96)

//Sql2005或Sql2008插入多行
1、insert into dbo.Scores(StudentId,StudentName)
select StudentId,UserName from dbo.Students
2、INSERT INTO [360_EvaluatePersons](UserId,EvaluateType)
select '1','1' union all
select '2','1' union all
select '3','1'  
3、insert into dbo.Scores(StudentId,StudentName)
select StudentId,UserName from dbo.Students where StudentId=2016001 union all
select StudentId,UserName from dbo.Students where StudentId=2016002
4、insert into dbo.Scores(StudentId,StudentName)
select StudentId,UserName from dbo.Students where StudentId=2016001 union all
select StudentId,UserName from dbo.Students where StudentId>2016005

删除

1、delete Students where StudentId>2016005 and StudentId=2016001 
2、delete Students where StudentId in (2016001,2016002)
3、delete UserInfo where StudentId like '%02%'
//<>1等价于!=1
4、delete Students where DeleteFlag<>1
5、delete Students where DeleteFlag!=1

查询

1、select * from Students where StudentId>2016005 and StudentId=2016001 
2、select StudentId,StudentName,PhoneNumber from Students where StudentId in (2016001,2016002)
3、select StudentId,StudentName,PhoneNumber from Students where StudentId like '%02%'
//_是占位符，like '_0%'表示查询第一位为任意值、第二位是0、后边的内容为任意值的结果
4、select StudentId,StudentName,PhoneNumber from Students where StudentId like '_0%'
//获取值为null的项要用 IS NULL而不是=null
5、select StudentId,StudentName,PhoneNumber from Students where 
StudentId>2016005 and (DeleteFlag IS NULL OR  DeleteFlag=0)
6、select StudentId as '用户Id',StudentName as '用户名',PhoneNumber from Students where 
StudentId>2016005 and (DeleteFlag IS NULL OR  DeleteFlag=0)
7、select '用户Id'=StudentId ,'用户名'=StudentName ,PhoneNumber from Students where 
StudentId>2016005 and (DeleteFlag IS NULL OR  DeleteFlag=0)
//case when then列无列名
8、select UserId,UserName,(case when Gender='0' then '女' when Gender='1' then '男' else '未知' end) from UserInfo
//case when then列有列名，说明case when then列是在源数据的基础上新添加了一列数据，然后对其重命名，对源数据列无影响
9、select UserId,UserName,Gender,(case when Gender='0' then '女' when Gender='1' then '男' else '未知' end)as Gender from UserInfo
10、select UserId,UserName,Gender,MyGender=(case when Gender='0' then '女' when Gender='1' then '男' else '未知' end) from UserInfo

修改

1、update Studens set AddTime=getdate(),Age=Age-1,Salary=Salary+2000 where StudentId=2016001
2、update Studens set AddTime=getdate(),Age=Age-1,Salary=Salary+2000 where StudentId in (2016001,2016002)
3、update Studens set AddTime=getdate(),Age=Age-1,Salary=Salary+2000 where StudentId like '%02%'
4、update Students set AddTime = getdate(),Age=Age-1,Salary=Salary+2000 where StudentId <>2016002
5、update Students set AddTime = getdate(),Age=Age-1,Salary=Salary+2000 where StudentId !=2016002
//列值=列名，则列值不变
6、update Students set AddTime = AddTime,Age=Age,Salary=Salary where StudentId !=2016002
对比
//列值='列名'，则列值变为列名
update Students set AddTime = 'AddTime',Age='Age',Salary='Salary' where StudentId !=2016002

case when then end用法

insert用法

//()as MyGender是将列重命名为MyGender
1、insert into dbo.StudentClass
select StudentId,(case when Gender='男' then '1' when Gender='女' then '0' else '2' end)as MyGender from dbo.Students
//Gender=()是将列重命名为Gender,case when then其实是在源数据的基础上新添加了一列数据，然后对其进行重命名，对源数据列无影响
2、insert into dbo.StudentClass
select StudentId,Gender=(case when Gender='男' then '1' when Gender='女' then '0' else '2' end) from dbo.Students
3、insert into dbo.StudentClass
select StudentId,MyGender=(case 
when (Gender='男'and Age>30) then '大男人' 
when (Gender='女'and Age>30) then '大女人' 
when (Gender='男'and Age>20 and Age<=30) then '中男人' 
when (Gender='女'and Age>20 and Age<=30) then '中女人' 
when (Gender='男'and  Age<20) then '小男人' 
when (Gender='女'and Age<20) then '小女人' 
else'未知' end) from dbo.Students

delete用法

1、delete dbo.StudentClass where (case when ClassName='1' then '男' when ClassName='0' then '女' else '待定'  end)='女'
2、delete dbo.StudentClass where (case when ClassName='1' then '男' when ClassName='0' then '女' else '待定'  end)='男'
3、delete dbo.Students where (case 
when Gender='男'and Age>30 then '大男人' 
when Gender='女'and Age>30 then '大女人' 
when Gender='男'and Age>20 and Age<=30 then '中男人' 
when (Gender='女'and Age>20 and Age<=30) then '中女人' 
when Gender='男'and  Age<20 then '小男人' 
when (Gender='女'and Age<20) then '小女人' 
else'未知' end)='大女人'

select用法

1、select StudentId,UserName,Gender=(case when Gender='1' then '男' when Gender='0' then '女' else '未知' end) 
from Students
2、select StudentId,UserName,(case when Gender='1' then '男' when Gender='0' then '女' else '未知' end) as Gender
from Students
3、select StudentId,UserName,Gender=(case 
when (Gender='男'and Age>30) then '大男人' 
when (Gender='女'and Age>30) then '大女人' 
when (Gender='男'and Age>20 and Age<=30) then '中男人' 
when (Gender='女'and Age>20 and Age<=30) then '中女人' 
when (Gender='男'and  Age<20) then '小男人' 
when (Gender='女'and Age<20) then '小女人' 
else'未知' end) from dbo.Students

update用法

//Gender必须是列名，此处Gender=()不再是重命名而是对列赋值因此()as Gender写法会出错
//性别互换
1、update dbo.Students set Gender=(case when Gender='男' then '女' when Gender='女' then '男' else '未知' end)
//可以把Age列的值赋给PhoneNumber列
2、update dbo.Students set PhoneUnmber=(case when Age>=30 then '大' when Age<30 and Age>=20 then '中' when Age<20 and Age>=10 then '小' else '末' end)
//可以把Gender和Age列的组合值赋给PhoneNumber列 
3、update dbo.Students set PhoneUnmber=(case 
when (Gender='男'and Age>30) then '大男人' 
when (Gender='女'and Age>30) then '大女人' 
when (Gender='男'and Age>20 and Age<=30) then '中男人' 
when (Gender='女'and Age>20 and Age<=30) then '中女人' 
when (Gender='男'and  Age<20) then '小男人' 
when (Gender='女'and Age<20) then '小女人' 
else'未知' end)