DataBase 之 语句全解析

一、查看

(1)应用程序名称              select app_name()

(2)获取登录者名字           select suser_sname()

(3)获取字段定义的长度     select col_length(‘TableName‘,‘ColumnName‘)

(4)获取架构名                select object_schema_name(object_id(‘TableName‘))

(5)获取字段名称             select col_name(object_id(‘TableName‘), Index)  //Index从1开始

(6)查看数据库                sp_helpdb

(7)获取表的数据结构及相关信息    sp_help TableName

(8)查看表信息                 sp_tables TableName

(9)查看字段信息              sp_columns TableName

(10)查看索引                  sp_helpIndex TableName

(12)查看存储过程            sp_helptext ‘SP_Name‘

(13)查看约束                  sp_helpConstraint TableName

(14)查看触发器定义         sp_helptext ‘Trigger_Name‘

(15)查看触发器的属性      sp_helptrigger ‘Trigger_Name‘

 

二、常用的操作和常用函数

ABS：ABS函数返回数值表达式的绝对值

语法：ABS（numeric_expression）可以是整型、短整型、微短整型、小数、浮点数、货币、实型或小货币型数。

ACOS：ACOS函数是一个数学函数，它返回以弧度表示的角度，即：ARCCOS。

语法：ACOS（float_expression）变量是浮点数数据类型，表示角度的余弦。

ASIN：反正弦函数。SIN：正弦值。 COS：余弦值。

语法：SIN（ float_expression）float_expression是浮点型；

语法：COS（ float_expression）float_expression是浮点型。

TAN：正切值。 COT:余切值。ATAN：反正切函数。

SIGN：如果表达式为正，SIGN函数返回1，如果表达为负，SIGN返回-1，如果表达式为0，则函数返回0。

CEILING：返回不小于给定的数值表达式的最小整数。

语法：CEILING（numeric_expression）小数、浮点、整数、实数、货币等类型。

例如：SELECT CEILING（1.45）返回值为2

SELECT CEILING （-1.45）返回值为-1

FLOOR：返回不大于给定的数值表达式的最大整数。

语法：FLOOR（numeric_expression）小数、浮点、整数、实数、货币等类型。

例如：SELECT FLOOR（1.45）返回值为1

SELECT FLOOR （-1.45）返回值为-2

ASCII：ASCII函数返回整数，表示字符表达式最左边的字符的ASCII代码值。

语法：ASCII（character_expression）

例：ASCII（‘AB’）返回65。

CHAR：字符串函数，用于将ASCII码整数转换为字符。

语法：CHAR（integer_expression）变量是0~255间的正整数。

例如：CHAR（65）返回‘A’

CharIndex：字符串函数，返回想要的查找字符串，在目标字符中的起始位置。

语法：CharIndex（‘Pattern，Expression，[StartLocation]）；

例如：CharIndex（‘A’，‘BCDEA’，2）返回值为5，其2为从第几个字符进行搜索(不写默认为从第一个位置)。

AVG：AVG是个集合函数，它计算数值列的平均值，忽略空值，如果使用了DISTINCT则对重复的值只取一次进行平均。

语法；AVG（[All|Distinct] expression）ALL是缺省值。

例如：SELECT Distinct（AGE）FROM TABLE1 如果任意行有NULL值，则此行被忽略。

COUNT：是一个集合函数，它返回的整数代表表达式中值的个数通常为一个列，Distinct和Count一起使用时，返回单一值的个数。

语法：COUNT（{[All|Distinct] Expression |*}）

例如：SELECT  COUNT（Column_Name）FROM  Table，

返回COLUMN1中没有空值的行数，如果是使用*将返回表中所有行的数目。

使用Distinct则返回唯一值的个数。

SUM：SUM是一个集合函数，返回表格中选定的数值型列中的值的总和。

语法：SUM（[All| Distinct]expression）

MAX：MAX返回表达式中的最大值，

语法：MAX（expression）

MIN：MIN返回表达式中的最小值，

语法：MIN（expression）

GETDATE：GETDATE是一个函数，它返回系统日期和时间。

语法：GetDate（）

DATEADD：DateAdd返回根据日期时间添加的日期，

语法：DateAdd（datepart,number,date）

变量：DatePart是用于计算的日期单位（year，month，day）；Number是指添加至日期变量中的日期单位数；Date：格式化为日期的字符串，或日期时间型的列名、变量或参数。

例如：Select  DateAdd（DAY，7，GetDate（））

DATEDIFF：返回两个指定日期的日期单位差别。

语法：DateDiff（datepart,date,date）

例如：DATEDIFF（day,datevar,getdate()）

返回值为datevar和当前日期相差的天数。

DATEPART：返回的整数表示日期中的日期单位。

语法：Datepart（datepart,date）

例如：DATEPART（month,getdate()）

返回值为当前日期的月份数。

YEAR：显示所提供日期的年，MONTH：显示所提供日期的月，DAY：显示所提供日期的天，

语法：Year（Date）, Month（Date）,  Day（Date）

CAST：CAST语句用来进行数据类型的转换。

语法：Cast（expression  AS  datatype）

例如：CAST（MYDATE AS VARCHAR）

把MYDATE 转换成可变字符类型。

CONVERT：用来进行资料类的转换。

语法：Convert（data_type,expression）

例如：SELECT ‘my birthday is today’+convert(varchar(12),getdate( ))

把日期转换成字符串类型，用加号与前边相连。

EXP：返回以E为底的N次幂。

语法：EXP（float_expression）

例如：EXP（0）返回值为1。

LOG：LOG函数返回浮点表达式的自然对数。

语法：LOG（float_expression）

LOG10：LOG函数返回浮点表达式的以10为底的对数值。

语法：LOG10（float_expression）

ROUND：ROUND返回给出的数学表达式的，并四舍五入至指定的精度。

语法：Round（numeric_expression,length[,funcion]）

变量：numeric_expression为数值型表达式

length是四舍五入的精度，小数点后边的数，当为负值时，数字表达式在小数点左边进行四舍五入。

Function值为0时表达式四舍五入，如果不为0，将数字表达式舍位。

SQUARE：求平方

语法：Square（float_expression）

SQRT：求平方根

语法：SQRT（float_expression）

STR：可以将数值型转换为字符型资料，允许控制小数的格式。

语法：STR（float_expression[,length[,decimal]]）

变量：length：想要的字符串的总长度。缺省为10。

decimal：是小数点右边的位数。四舍五入。如果不写长度则为取整，小数后四

舍五入。

ISNULL：ISNULL可有非空资料代替NULL。

语法：IsNull（expression,value） 例如：Select IsNull(NULL,‘ABC‘)

LEFT/RIGHT：LEFT/RIGHT函数返回字符表达式从左边/右边开始的给定的字节数。如果该整数是一个负数， 就返回NULL。

语法：Left/Right（character_expression,integer_expression）

变量：character_expression可以是变量、常量、或表中的列。

Integer_expression为要返回的字符数。

LEN：LEN函数提供了字符串表达式中的字符的长度。

语法：Len（string-expression） 例如：LEN（‘ABCE’）值为4

LOWER：LOWER函数将大写字符转换为小写字符。

语法：Lower（character_expression）  变量是字符表达式。

UPPER：UPPER函数将小写字符转换为大写字符。

语法：Upper（character_expression）  变量是字符表达式。

REVERSE:REVERSE返回字符串表达式的倒序。

语法：Reverse（character_expression）  例如：REVERSE（‘PAM’）结果为‘MAP’

LTRIM：LTRIM删除字符表的前导空格。

语法：LTRIM（character_expression）  变量是字符表达式。

RTRIM：RTRIM删除字符表的后继空格。

语法：RTRIM（character_expression）  变量是字符表达式。

NULLIF：如果一对表达式相等，NULLIF函数就返回NULL，如果它们不相等，NULLIF函数就返回第一个表达式的值。

语法：NullIf（expression1,expression2） 变量：expression可以数值型，也可以是字符串。

例如：SELECT NULLIF（134，135）返回值为134。

PI：PI()函数返回pi的值3.1415926

REPLACE：REPLACE用串表达式3取代串表达式1中的表达式2。

语法：Replace（‘string1’，‘string2’，‘string3’）

例如：SELECT REPLACE（‘mydog’，‘dog’，‘car’）用CAR取代了MYDOG中的DOG，结果为MYCAT。

STUFF：函数可以将表达式的一部分用所提供的另一个字符串替代，替代部分用起始位置和长度来定义。

语法：Stuff（character_expression,start,length,character_expression）

LENGTH：是决定要替换的字符数。  例如：STUFF（‘MY DOG FIDO’，8，4，‘SALLY’）

SUBSTRING：返回字符型的一部分。

语法：SubString（expression,start,length）

例如：SUBSTRING（‘MICROSOFT’，6，4）  返回SOFT。

REPLICATE：REPLICATE将字符串表达式复制用户定义的次数。

语法：Replicate（character_expression,integer-expression）  例如：REPLCATE（‘2’，5）

 

（1）try catch使用

--打开try catch功能

set xact_abort on

begin try

 begin tran

 insert into Student(SID) values(-1)

 commit tran

 print ‘commited‘

end try

begin catch

 rollback

 print ‘rolled back‘

end catch 

（2）--获取当前月前一个月有多少天（DatePart( date ,  datetime)  DateAdd(date , 数字 ，datetime) Cast(exp  as  datetime)）

select DatePart

(day,

DateAdd(DAY,-1,

Cast(cast(YEAR(getdate()) as nvarchar)+‘-‘+cast(month(getdate()) as nvarchar)+‘-01‘ as datetime)

)

)

（3）--取到小数第二位四舍五入（类型转换）

Select Convert(Numeric(20,2), IsNull(50.01634,0))

--50.02

（4）获取时间

--一个月第一天

select DateAdd(mm,DateDiff(mm,0,getdate()),0)

--DateAdd当添加的值和初试值均为0时，返回的时间为1900-01-01 00:00:000

select DATEADD(mm,0,0);

--本年：

select * from Stduent where year(date)=year(getdate())

--本月：

select * from Stduent where year(date)=year(getDate()) And month(date)=month(getdate())

--本日：

select * from Stduent where year(date)=year(getDate()) And month(date)=month(getdate()) and Day(date)=Day(getDate()) 

--昨天的记录：

datediff(day,[Datetime],getdate())=1  把Datetime换为你的相应字段，getdate()-Datetime即为时间差。

--本月/周/日记录：

Select  *  F rom 表 Where DateDiff(month/week/day,[dateadd],getdate())=0

--本周的星期一  

Select  DateAdd(wk,  DateDiff(wk,0,getdate()),  0) 

--季度的第一天  

Select  DateAdd(qq,  DateDiff(qq,0,getdate()),  0)

--当天的半夜  

Select  DateAdd(dd,  DateDiff(dd,0,getdate()),  0) 

--本年的第一天

select  DateAdd(YY,DateDiff(yy,0,getdate()),0)

--去年的最后一天（今年的第一天减去3毫秒）

select DateAdd(ms,-3,DateAdd( yy,  DateDiff( yy, 0, getdate()),  0))

--本月的第一天

select DateAdd( mm, DateDiff( mm, 0 , GetDate()), 0);

--本月的最后一天

select DateAdd(ms,-3, DateAdd(mm, DateDiff(mm,0,getdate()), 0))

--本年的最后一天

Select  DateAdd(ms,-3, DateAdd(yy,  DateDiff(yy, 0, getdate()) +1,  0))

--本月的第一个星期一

select  DateAdd(wk, DateDiff(wk,0, DateAdd(dd,6 - Datepart(day,getdate()) ,getdate()) ),  0)   

--显示星期几

select DateName(weekday , getdate() );

--显示本月的天数

select DateDiff(dd , getdate() , DateAdd(mm , 1 , getdate()) );

--显示某年某月有多少天

create   Function DaysInMonth1(@datetime datetime)

returns int

as

begin

return Day( Datepart( dd, DateAdd( ms, -3, DateAdd(mm ,DateDiff(mm,0,@datetime)+1 , 0) ) ))

end

调用select dbo.DaysInMonth1(‘2012-2-13‘);

--获取当前数据库中所有用户的表

select * from sysobjects where xtype=‘U‘  

--随机取出3条数据

Select  top 3 *  from Table  order by NewId()

--查找表中多余的重复记录

Select  *  from Table where ID  in( select SID from Table  group by ID having Count(Name)>1)

--关键字waitfor 主要有waitfor delay(推迟执行)，waitfor time (特定时间执行)

waitfor delay ‘00:00:08‘

print(‘Hello ,Im waitfrom delay‘)

waitfor time ‘20:49:50‘

print(‘Hello,Im waitfor time‘)

 

--检测数据是否存在

--检测数据库是否存在

if( Exists(select * from sys.databases where name=‘School‘))

select * from Student

go

--检测数据表是否存在

if( exists(select * from sys.objects where name=‘Student‘))

select * from Student order by SID desc

go

--检测数据列是否存在

if(exists(select * from sys.all_columns where object_id=object_id(‘Student‘) and name=‘Sname‘))

select * from Student

go

--表连接

--同时无条件的查询两个不相关的表，所得新表字段为两表字段之和，信息条数为两个信息条数的乘积 （即为：两表的笛卡尔积）     

select  T.* , Student.*  from  T , Student

--交叉连接即为上边所讲的那种连接

select T.*,Student.* from T Cross Join Student

--随机数均可以产生0-1之间的随机数

Select   Rand();    select Rand( CheckSum(NewId()) )

--随机数0-9

select ABS(Rand());   select ABS(CheckSum(NewId()))%10

--随机数a-b之间

select a+ABS(checksum(newid()))%(b-a+1)

select 1+ABS(checksum(newid()))%100

字符串操作

--SubString字符串截取(str ,StartIndex , Length)

select SubString( ‘Olive116‘, 1, 5)

--left/right从左边截取字符串

select Left/Right ( ‘Olive‘, 3 )

--字符串替换Replace( str, str1, str2)

select Replace(‘OOLive‘,‘o‘,‘1‘)

--反转排序

select Reverse(‘Olive‘)

--删除指定的长度并在指定的起点处插入另一组字符

select Stuff(‘OliveOOO‘,6,3,‘Hello‘)

--以指定的次数重复字符串的值

select Replicate(‘Olive‘,3)

--返回表达式中第一次出现的位置

select PatIndex(‘%Olive%‘,‘Hello!Olive‘)

--返回字符串中指定表达式的开始位置

select CharIndex(‘O‘,‘Hello,Olive‘)

--返回输入表达式的第一个字符的整数值

select UniCode(‘o‘)

--返回由数字数据转换来的字符数据

select STR(123.1314,4),LEN(STR(123.3212,321))

select STR(123.1334,4,4)

--发音匹配度

select Sname,SOUNDEX(Sname) from Student

--Difference()简化两个字符串发音相似度,返回0-4之间的值来反应两个字符的相似度，值越大就越相似

select Sname,SOUNDEX(Sname), Difference (Sname,‘Olive‘) from Student

--查询重复记录

select * from Table where SID in (select SID from Table group by SID having COUNT(SID)>0)

--删除重复的记录（并保留SID最小的记录）

delete from Table where

SID in (select SID  from Table group by SID having COUNT(SID)>0)

and SID not in(select MIN(SID) from Student group by SID having count(SID)>0)

--双引号

 select ‘‘‘‘‘‘ as result

--isnumeric（）判断是否是数字是为1否为0

select IsNumeric(‘a‘)

--isdate()判断是否为日期是为1否为0

select IsDate(‘2012-3-2‘)

 

四、存储过程、函数、触发器、事务、游标

（1）存储过程

--创建存储过程（无参）

create procedure ProTestA

as

select * from TestA

--修改存储过程（有参，且为输出参数)

alter procedure ProTestA

@LastName varchar(10) = null output

as

if @LastName is null

begin

     print ‘null‘

     print ‘123‘

end

else

begin

 print @LastName

 print ‘456‘

 end

 exec ProTestA

 exec ProTestA ‘Olive‘

（2）自定义函数（关键点：函数和存储过程 区别主要在于函数必须声明返回值类型）

--用户自定义函数（返回时间）

create function DayOnly(@date datetime)

returns varchar(12)

as

begin

return convert(varchar(12),@date,101)

end  

 

select dbo.DayOnly(getdate())

--用户自定义函数(返回表)

drop function TestATable

 

create function TestATable(@Name nvarchar(10))

returns table

as

return select * from TestA where id=@Name

select * from TestA

select * from TestATable(‘12‘)

 

--用户自定义函数输入参数、返回表

create function ResultTable(@Name nvarchar(10))

returns @Result table (Rid varchar(10),Rpwd varchar(10))

as

begin

insert into @Result select * from TestA where id=@Name

return

end

select * from ResultTable(‘12‘)

（3）触发器

--创建触发器

create trigger UpdateStudent

on Student

for insert

as

update Student set Sname=‘O(∩_∩)O哈哈~‘ where SID=( select Inserted.SID from Inserted)

insert into Student select ‘MM‘,‘1988-11-11‘,‘男‘

select * from Student order by SID Desc

--禁用触发器

alter table Sales.SalesOrderDetail

DisableTrigger all --这里的all可以是具体的触发器名

--删除触发器

Drop Trigger orderDetailNotDiscontinued

--修改触发器

Alter Trigger tr_title

on mytable

for insert,update

as

if update(title)

begin

print(‘title1‘)

end

--触发器的相关函数Update()

create trigger UpdateTest

on Student

for insert,update

as

if update(SName)----如果update（）中的字段有更新的话就为true否则为false

select Inserted.Sname from Inserted

insert into Student select ‘OLIVEVEVE‘,‘1989-12-22‘,‘女‘

--触发器(插入新的数据，并且在插入后更新刚插入的输入的姓名)

create trigger InsertTwo

on Student

after insert

as

update Student set Sname=‘OOOO‘ where SID=( select inserted.SID from Inserted)

 

insert into Student select ‘MoYao‘,‘1989-11-15‘,‘男‘

select * from Student

其中inserted表是一个临时表

存储的是将要插入的信息

这个触发器的目的是检查将要插入的信息是否符合规定

(在product表里没有特殊的记录)

这个例子是check约束所不能解决的了的

 

use AdventureWorks

go

Create Trigger orderDetailNotDiscontinued

On Sales.SalesOrderDetail

After insert,update

as

if exists

 (

select ‘true‘ from Inserted i

join Production.Product p

on i.ProductID= p.ProductID

where p.DiscontinuedDateis not null

 )

begin

print(‘error appear.‘)

rollback tran

end

4）事务

sql事务是使用SQL server自身的事务：在存储过程中直接使用Begin Tran，Rollback Tran，Commit Tran实现事务：

优点：执行效率最佳

限制：事务上下文仅在数据库中调用，难以实现复杂的业务逻辑。

begin try

  begin tran

  insert into Student(SID) values(-1)

  commit tran

  print ‘commited‘

end try

begin catch

  rollback

  print ‘rolled back‘

end catch 

 

TransactionScope事务

TransactionScope事务类，它可以使代码块成为事务性代码。并自动提升为分布式事务

优点：实现简单，同时能够自动提升为分布式事务

那么如何解决数据库并发问题呢？

解决此问题的办法就是为数据库加锁，以防止多个组件读取数据，通过锁住事务所用的数据，能保证开锁之前，只有本事务才能访问数据库。这样就避免了交叉存取的问题

（5）游标

--游标：数据集合里的上下移动的指针

Drop cursor StudentCursor

--声明变量

Declare @SID int

Declare @Sname nvarchar(30)

Declare @Sage nvarchar(30)

Declare @Ssex nvarchar(5)

--声明游标

Declare StudentCursor cursor  for

select * from Student

--打开游标

Open StudentCursor

--将游标移向下一行，将获取的数据放入之前声明的变量中

fetch next from StudentCursor into @SID,@Sname,@Sage,@Ssex

--判断是否获取数据成功，@@fetch_status为系统全局变量，0-表示成功，1-表示取不到数据，2-超出了最后一条数据

while @@fetch_status=0

begin

print(Cast(@SID as varchar)+‘:‘+@Sage+‘:‘+@Ssex)

--提取下一条记录

fetch next from StudentCursor into  @SID,@Sname,@Sage,@Ssex

end

close StudentCursor

deallocate StudentCursor

 

--存储过程中使用游标（游标的作用域）

create procedure TestStudentCursor

as

--声明变量填充游标检索到的数据

declare @SID int

declare @Sname nvarchar(30)

declare @Sage nvarchar(30)

declare @Ssex nvarchar(5)

--声明存储过程内部的全局游标

declare StudentCursor  cursor global for

select * from Student

--打开游标

open StudentCursor

--读取下一条数据，并将数据填充到之前声明的变量中

fetch next from StudentCursor into @SID,@Sname,@Sage,@Ssex

--判断是否取到数据，且取到的数据是否符合要求

while(@SID<5) and (@@fetch_status=0)

begin

--打印数据

print(cast(@SID as varchar)+‘::‘+@Sname+‘::‘+@Sage)

--读取下一条数据

fetch next from StudentCursor into @SID,@Sname,@Sage,@Ssex

end

--关闭游标

close StudentCursor

--释放游标

deallocate StudentCursor

 

--执行存储过程

exec TestStudentCursor

五、实用功能

（一）分页

分页的几种写法（性能效果最好的是top/max 和Row_number()两种方法）

--写法1，not in/top

select top 50 * from pagetest

where id not in (select top 9900 id from pagetest order by id)

order by id

 

--写法2，not exists

select top 50 * from pagetest

where not exists

(select 1 from (select top 9900 id from pagetest order by id) a  where a.id=pagetest.id)

order by id

 

--写法3，max/top

select top 50 * from pagetest

where id>(select max(id) from (select top 9900 id from pagetest order by id)a)

order by id

 

--写法4，row_number()

select top 50 * from

(select row_number()over(order by id)rownumber,* from pagetest)a

where rownumber>9900

 

select * from

(select row_number()over(order by id)rownumber,* from pagetest)a

where rownumber>9900 and rownumber<9951

 

select * from

(select row_number()over(order by id)rownumber,* from pagetest)a

where rownumber between 9901 and 9950

 

--分页函数（先循环插入100000000条数据然后再分页）

create table  #FYTable (id int identity(1,1),title nvarchar(20),con nvarchar(20))

declare @title nvarchar(300),

        @con nvarchar(300)

declare @start int,@end int

set @start=0

set @end=1000000

set @title=‘Olive‘

set @con=‘I love‘

while @start<@end

begin

 insert into #FYTable select @title,@con

 set @start=@start+1

 end

select * from #FYTable

create procedure dbo.FenYe

@count int,@yeshu int

as

select top(@yeshu) * from #FYTable where id not in (select top(@count*@yeshu) id from #FYTable)

 

(2)排序

--排序函数

select SName,Sage,SSex from(

select SName,Sage,Ssex ,Rank() over(order by sage) as rank from Student

) as a where a.rank>1 and a.rank<9;

（3）表行列转换

--数据表行列转换（case when then）

create table Factory( FID int, FDepartment nvarchar(20), FMeterial nvarchar(20),  FNumber int)

insert into Factory values(1,‘工厂1‘,‘材料1‘,1);

insert into Factory values(1,‘工厂2‘,‘材料1‘,1);

insert into Factory values(1,‘工厂2‘,‘材料2‘,2);

insert into Factory values(1,‘工厂2‘,‘材料3‘,1);

insert into Factory values(1,‘工厂3‘,‘材料3‘,1);

insert into Factory values(1,‘工厂2‘,‘材料1‘,2);

insert into Factory values(1,‘工厂3‘,‘材料2‘,1);

select * from Factory

 

select  FDepartment,

SUM(case FMeterial when ‘材料1‘ then FNumber else 0 end) as 材料1,

SUM(case FMeterial when ‘材料2‘ then FNumber else 0 end) as 材料2,

SUM(case FMeterial when ‘材料3‘ then FNumber else 0 end) as 材料3

from Factory group by FDepartment

 

--行列转换函数Pivot

create table Sell

(

[Year] int , [Quarter] nvarchar(2) , Quantity int

)

insert into Sell values(2011,‘Q1‘,12)

insert into Sell values(2011,‘Q2‘,13)

insert into Sell values(2011,‘Q2‘,14)

insert into Sell values(2011,‘Q3‘,15)

insert into Sell values(2011,‘Q2‘,12)

insert into Sell values(2011,‘Q3‘,13)

insert into Sell values(2011,‘Q4‘,15)

insert into Sell values(2011,‘Q4‘,17)

insert into Sell values(2011,‘Q4‘,11)

insert into Sell values(2012,‘Q3‘,13)

insert into Sell values(2012,‘Q4‘,15)

insert into Sell values(2012,‘Q2‘,17)

insert into Sell values(2012,‘Q4‘,11)

insert into Sell values(2012,‘Q3‘,13)

insert into Sell values(2012,‘Q1‘,15)

insert into Sell values(2012,‘Q1‘,17)

insert into Sell values(2012,‘Q4‘,11)

select * from Sell

 

select * from Sell Pivot(sum(Quantity) for [Quarter] in (Q1,Q2,Q3,Q4) )as p

select [Year], SUM(case when [Quarter]=‘Q1‘ then Quantity end) as Q1,

               SUM(case when [Quarter]=‘Q2‘ then Quantity end) as Q2,

               SUM(case when [Quarter]=‘Q3‘ then Quantity end) as Q3,

               SUM(case when [Quarter]=‘Q4‘ then Quantity end) as Q4

               from Sell group by [Year]

 

 

--case when then 用例

drop table T

go

create table T (TID int identity(1,1) not null  primary key,Quantity int not null,ProductID int not null,[Type] int not null)

go

insert into T select 2,12,1

insert into T select 6,12,2

insert into T select 3,10,2

insert into T select 8,10,1

insert into T select 2,12,1

select * from T

go

--统计每种商品有多少件

select ProductID,SUM (case when [Type]=1 then Quantity else -Quantity end) from T group by ProductID

 

--使用case when 为表的新列填充不同的数据

alter table T add Descriptions nvarchar(20) null

go

update T set Descriptions= case when [Type]=1 then ‘现存‘

                                when [Type]=2 then ‘售出‘

                                else ‘未知‘

                                end

go               

 

--怎么把这样一个表

--year  month amount

--1991   1     1.1

--1991   2     1.2

--1991   3     1.3

--1991   4     1.4

--1992   1     2.1

--1992   2     2.2

--1992   3     2.3

--1992   4     2.4

--查成这样一个结果

--year  m1  m2  m3  m4

--1991  1.1   1.2   1.3   1.4

--1992  2.1   2.2   2.3   2.4

create table tb_Test1

([year] int,[month] int,amount float)

insert into tb_Test1

select 1991,1,1.1

union select 1991,2,1.2

union select 1991,3,1.3

union select 1991,4,1.4

union select 1992,1,1.1

union select 1992,2,1.2

union select 1992,3,1.3

union select 1992,4,1.4

select * from tb_Test1t

--根据月份的不同分为不同的字段，然后再按照年份分组

select [year],sum(case [MONTH] when 1 then amount else 0   end) as m1,sum(case [MONTH] when 2 then amount  else 0 end) as m2,sum(case [MONTH] when 3 then amount else 0 end) as m3,sum(case [MONTH] when 4 then amount else 0 end) as m4 from tb_Test1 group by [year]

 

--有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value。这道题的SQL语句怎么写？

create table tb_A(keys int,value varchar(20))

create table tb_B(keys int,value varchar(20))

insert into tb_A values(1,‘aa‘),(2,‘ab‘),(3,‘ac‘)

insert into tb_B values(1,‘aa‘),(2,‘a‘),(3,‘a‘)

方法一--先将更新B中value的值更新为A中value的值，同时限定B中的keys=A.Keys 同时B.value<>A.value（不等于）

update tb_B set value=(select a.value from tb_A a where a.keys=tb_B.keys)

where tb_B.keys in(select tb_B.keys from tb_B,tb_A where tb_A.keys=tb_B.keys and tb_A.value<>tb_B.value)

select * from tb_B

select * from tb_A

delete from tb_A

delete from tb_B

方法二--先删除A，B一样的部分，然后再根据两表中Keys 的相等进行更新

update tb_B set tb_B.value=s.value from (select * from tb_A except select * from tb_B) as s where tb_B.keys=s.Keys

--原表:

--courseid  coursename  score

--1　　　  java 　　      70

--2 　　   oracle 　　    90

--3 　　   xml 　　      40

--4 　　   jsp 　　       30

--5 　　   servlet 　　    80

--为了便于阅读,查询此表后的结果显式如下(及格分数为60):

--courseid  coursename  score  mark

--1 　　     java 　　    70 　pass

--2  　　    oracle 　　  90 　pass

--3 　　     xml 　　    40 　fail

--4 　　     jsp 　　     30 　fail

--5 　　　  servlet 　　   80   pass

create table tb_B(id int identity(1,1),coursename varchar(30),score int )

insert into tb_B values(‘java‘,70),(‘oracle‘,90),(‘xml‘,40),(‘jsp‘,30),(‘servlet‘,80)

select * from tb_B

--case when 的使用

select id,coursename,score, case when score > 60 then ‘pass‘ else ‘fail‘ end as mark from tb_B                               

 

--原表:

--id proid proname

--1  1    M

--1  2    F

--2  1    N

--2  2    G

--3  1    B  

--3  2    A

--查询后的表:

--id  pro1  pro2

--1  M     F

--2  N     G

--3  B     A

create table C(id int,proid int,proname varchar(2))

insert into C

values(1,1,‘M‘),(1,2,‘F‘),(2,1,‘N‘),(2,2,‘G‘),(3,1,‘B‘),(3,2,‘A‘)

select * from C

select id,(select proname

           from C c1

           where proid=1 and c.id=c1.id ) as pro1,

           (select proname from C c2 where proid=2 and c2.id=c.id ) as pro2

from C c group by id

--如下表a

--列    a1 a2

--记录  1  a

--      1  b

--      2  x

--      2  y

--      2  z

--用select能选成以下结果吗？

--1 ab

--2 xyz

create table D (a1 varchar(2),a2 varchar(2))

insert into  D values (‘1‘,‘a‘),(‘1‘,‘b‘),(‘2‘,‘x‘),(‘2‘,‘y‘),(‘2‘,‘z‘)

Create table D1 (id varchar(2),value varchar(2))

Alter table D1 alter column value  varchar(20)

insert into D1 select Distinct(a1),‘‘ from D

declare @id varchar(2),@value varchar(20)

declare LianJie cursor for select * from D

open LianJie

fetch next from LianJie into @id,@value

while @@FETCH_STATUS=0

begin

update D1 set value=value+@value where id=@id

fetch next from LianJie  into @id,@value

end

close LianJie

deallocate  LianJie

select * from D

select * from D1

 

——3. 表内容：

--2005-05-09 胜

--2005-05-09 胜

--2005-05-09 负

--2005-05-09 负

--2005-05-10 胜

--2005-05-10 负

--2005-05-10 负

--如果要生成下列结果, 该如何写sql语句?

--           胜   负

--2005-05-09  2    2

--2005-05-10  1    2

create table E (dt varchar(20),shengfu nvarchar(20))

insert into E values(‘2012-9-15‘,‘胜‘),(‘2012-9-15‘,‘胜‘),(‘2012-9-15‘,‘负‘),(‘2012-9-15‘,‘负‘),(‘2012-9-16‘,‘胜‘),(‘2012-9-16‘,‘胜‘),(‘2012-9-16‘,‘负‘)

select * from E

select  dt, SUM(case when shengfu=‘胜‘ then 1 else 0 end) as 胜,SUM(case when shengfu=‘负‘ then 1 else 0 end) as 负 from E group by dt

 

--表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列

create table E1 (A int ,B int,C int)

insert into E1 values(1,3,5),(6,4,2),(8,7,9)

select * from E1

select case when A>B then A else B end as AB,case when B>C then B else C end as BC from E1

 

table1

月份          部门业绩

一月份      01      10

一月份      02      10

一月份      03      5

二月份      02      8

二月份      04      9

三月份      03      8

table2

部门     部门名称

01      国内业务一部

02      国内业务二部

03      国内业务三部

04      国际业务部

table3 （result）

部门部门名称  一月份      二月份      三月份

  01  国内业务一部    10        null      null

  02   国内业务二部   10         8        null

  03   国内业务三部   null       5        8

  04   国际业务部   null      null      9

create table #F([month] nvarchar(20), bumen nvarchar(20), yeji int)

insert into #F values(‘一月份‘,‘01‘,10),(‘一月份‘,‘02‘,10),(‘一月份‘,‘03‘,5),(‘二月份‘,‘02‘,8),(‘二月份‘,‘04‘,9),(‘三月份‘,‘03‘,8)

create table #F1(bumen nvarchar(20),bumenmingcheng nvarchar(20))

insert into #F1 values(‘01‘,‘国内业务一部‘),(‘02‘,‘国内业务二部‘),(‘03‘,‘国内业务三部‘),(‘04‘,‘国际业务部‘)

select * from #F1

select f.bumen ,f1.bumenmingcheng,sum(case when f.[month]=‘一月份‘ then f.yeji else null end) as 一月份,

sum(case when f.[month]=‘二月份‘ then f.yeji else null end) as 二月份,

sum(case when f.[month]=‘三月份‘ then f.yeji else null end) as 三月份 from #F f,#F1 f1 where f.bumen=f1.bumen group by f.bumen,f1.bumenmingcheng

 

drop table #Str

create table #Str(id int identity(1,1),str11 varchar(20), [type] int )

insert into #Str values(‘how‘,1),(‘are‘,1),(‘you‘,1),(‘fine‘,2),(‘thank‘,2),(‘you‘,2)

select * from #Str

create table #Str1(ss nvarchar(100),id int )

insert into #Str1 select Distinct ‘‘,([type]) from #Str

select * from #Str1

declare @id1 int,@str1 nvarchar(100)

declare StrCursor cursor for select str11,[type] from #Str

open StrCursor

fetch next from StrCursor into @str1,@id1

while @@FETCH_STATUS=0

begin

 update #Str1 set ss=ss+@str1 whereid=@id1

 fetch next from StrCursor into @str1,@id1

 

 end

 close StrCursor

 deallocate StrCursor

 select * from #Str1

 

 

 --Post

 create Table Post(id int identity(1,1) primary key,title nvarchar(200),content text,CreateDate nvarchar(30))

 --Comments

 create Table Comments(Cid int identity(1,1) primary key,id int,content text,CreateDate nvarchar(30))

insert into Post select ‘我从远方赶来‘,‘我为她而狂野，我为你来看我不顾一切‘,cast(GETDATE() as nvarchar)

insert into Comments values

(1,‘不虚此行呀‘,CAST(getdate() as nvarchar)),

(1,‘这一个不能停留太久的世界‘,cast(getdate() as nvarchar)),

(1,‘无可取代‘,cast(getdate() as nvarchar))

select * from Comments

create table #Ts (id int,com nvarchar(max))

insert into #Ts select Distinct(id),‘‘ from Comments

declare @ii int,@com nvarchar(max)

declare TsCursor cursor for select id ,content from Comments

open TsCursor

fetch next from TsCursor into @ii,@com

while @@FETCH_STATUS=0

begin

update #Ts set com=com+@com whereid=@ii

fetch next from TsCursor into @ii,@com

end

close TsCursor

deallocate TsCursor

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL对表的基本操作

1：向表中添加字段

　　Alter table [表名] add [列名] 类型

2: 删除字段

　　Alter table [表名] drop column [列名]

3: 修改表中字段类型 (可以修改列的类型，是否为空)

　　Alter table [表名] alter column [列名] 类型

4：添加主键

　　Alter table [表名] add constraint [ 约束名] primary key( [列名])

5：添加唯一约束

　　Alter table [表名] add constraint [ 约束名] unique([列名])

6：添加表中某列的默认值

　　Alter table [表名] add constraint [约束名] default(默认值) for [列名]

7：添加约束

　　Alter table [表名] add constraint [约束名] check (内容)

8:添加外键约束

　　Alter table [表名] add constraint [约束名] foreign key(列名) referencese [另一表名](列名)

9:删除约束

　　Alter table [表名] drop constraint [约束名]

10:重命名表

　　exec sp_rename ‘[原表名]‘,‘[新表名]‘

11：重命名列名

exec sp_rename ‘[表名].[列名]‘,‘[表名].[新列名]‘