常用sql （转）

数据库

聚合函数和NULL关键字

现实NULL的技巧：将所有NULL单元格显示为‘缺考’的方法

Select  isnull(scr1,’缺考’), isnull(scr2,’缺考’), isnull(scr3,’缺考’) from mytabls where sumsrc order by name group by scr1

求平均数的技巧：Select avg(distinct price) from titles where type=’business’ 对商业类书籍求平均价，注意：该处可能有多本同样的书籍，我们只取一本进行统计，用关键字distinct。同样适用与求和等等。DISTINCT----去掉重复值

HAVING关键字和WHERE关键字的区别：WHERE 搜索条件在进行分组操作之前进行，HAVING关键字搜索条件在进行分组操作之后进行

EX：select sum(price) from titles having sum(price)>30,而不能用select sum(price) from titles where sum(price)>30

MAX（）和MIN（）函数即可以用于字母，可以用于字符串。

Char[n]和varchar[n]：固定长度字符数据类型和可变长度字符数据类型。N都位于0-8000之间，char存储大小为N，varchar存储大小为输入数据的字节的实际长度。

系统函数：

存储过程：

最大嵌套32层，用@@nestlevel函数获取当前嵌套层数。EX:

Create proc sp_myproc1

As

Select * from aa

Select @@nestlevel as ‘当前嵌套层数’

Go

Create proc sp_myproc2

As

Select * from bb

Select @@nestlevel as ‘当前嵌套层数’

返回当前数据库服务器名称：select @@servername

返回当前数据库服务厂商名称：select @@servicename

返回当前数据库版本信息：select @@version

日期和时间函数：

Select getdate()获取当前年月日

Select getutcdate()获取格林尼治时间

Select day(getdate())获取当前年月日的‘天‘。

Select year(getdate())/select month(getdate())获取当前年月的’年’和’月’。

Select dateadd(year,20,’1990-02-22’)在制定的时间上加上N年，并显示出来。

Select dateadd(month/day,20,getdate())在当前年月上加上20个月或20天，并显示出来

Select datediff(year/month/day,’2002-02-22’,getdate())计算当前时间与2002-02-22所间隔的年/月/日

Select datename(year/month/day,getdate())获取当前时间的年/月/日

数学函数

Select rand()获取小于1的随机数，float型

Select round(103.2033，-1)获取四舍五入的值，结果为100

Select charindex(‘c’,’abcdefghijk’)找到’C’所在的索引，返回值为3。

字符串函数

SELECT SUBSTRING(CONVERT(VARCHAR(20),DAY(GETDATE())),1,4)

SELECT CHARINDEX(CONVERT(VARCHAR(20),GETDATE()),’9’)

SELECT LEFT(‘ABCDEFG’,3),RIGHT(‘ABCDEFGH’,2),LTRIM(‘  ABC’),RTRIM(‘ABC ’),REPLACE(‘ABCDEFGH’,’CD’,’EFF’)

SELECT LOWER(‘ABCEDF’),UPPER(‘abcde’),reverse(‘abcd’),replicate(‘abc’,2),’哈哈’+space(2)+’啦啦’

Select  patindex(‘%s%’,job_desc) from jobs

 

 

事务(transaction)

存储过程(procedure)

Create procedure p_name

@a int,

@b varchar(20) out,

@c datetime

As

When @a!=0

Begin

Declare @retnum varchar(200)

Select * from tabName where  desc like %@b% and time=@c

 @retnum=Select @@servicename

   @b=@retnum

End

 

触发器(trigger)

Create trigger tri_Name

On mytalName

For insert update delete

 

表

Create table tabName()

Drop table tabName

Truncate table tabName

Select * into #bb from aa

Insert into aa select * from bb

视图

Create view v_tabname as selecte * from aa

索引

Create index i_tabname for aa on id

 

常用的查询

Having,group by,order by,inner join,left join,right join,all join,desc,asc,distect,

Select id,name,isnull(src1,’空缺’) from jobs

 

练习

use msgdb

go

select * from tbluser

 

use pubs

 

select * from tbltt

select top 3 * into #aa from tbltt where id=1

select top 3 * into #bb from tbltt where id=2

select top 3 * into #cc from tbltt where id=3

truncate table tbltt

insert into tbltt select * from #aa

 

insert into tbltt select * from #bb

 

insert into tbltt select * from #cc

 

select id,name,max(case id when 1 then src

         when 2 then src

         when 3 then src end)   as aa ,max(case id when 1 then src

         when 2 then src

         when 3 then src end)   as bb,max(case id when 1 then src

         when 2 then src

         when 3 then src end) as cc

        from tbltt group by id,name order by id

 

select distinct(id),name from tbltt group by id,name

 

 

 

select rand(10),round(2001.235,2),charindex('abcdef',2),substring(convert(varchar(12),year(getdate())),1,4),replace(convert(varchar(20),getdate()),' ','/')

select rand(),lower('AABBccDDeeFF'),UPPER('alkdfkjKLJlkjlkio'),left('  df   ',2),right('sdf',2),ltrim('  df  '),rtrim('  ljkj  '),

 

select replicate('abc=',3) as '重复',reverse('abcdefghijklmnOPQRST') as '字符串反转函数','chen'+space(2)+'jun' as '姓名',PATINDEX('%S%',job_desc) from jobs

 

 

 

 

 

select getdate()

select getdate(),day(getdate()),month(getdate()),year(getdate())

select day(dateadd(day,2,getdate())),datediff(day,'2009-01-25',getdate()),getdate()

 

SQL查询语句大全

关键字HAVING和WHERE 的区别：

HAVING 子句应用于由分组生成的结果集中的行。只有符合 HAVING 子句条件的组才出现在查询输出中。只能将 HAVING 子句应用于也出现在 GROUP BY 子句或聚合函数中的列。

WHERE 子句应用于表中的个别行或关系图网格中的表结构化对象。只对符合 WHERE 子句条件的行进行分组。

 

WHERE 用来分组，HAVING用于分组后的查询条件补充

重复数据查询：

select distinct name from table打开重复记录的单个字段
select  * from table where fid in(Select min(fid) FROM table group by name)打开重复记录的所有字段值
select   *   from   table   where   name in(select name from table group  by  name   having count(name)=1)打开重复任意次数的所有记录

非重复数据查询：

select * from tbl where id not in(select id from tbl group by id having count(id)>1)打开没有重复过的数据表

select * from tbl where id in (select id,count(id) as a from tbl where a<2 group by id)打开没有重复过的数据表

select * from (select id ,count(id) as aa from tbl group by id)where aa<2 传说中可以打开没有重复的数据表，但是在SQL Server2000中没有成功。

 

删除数据

Truncate table tbl删除表信息，不删除表结构

复制数据

Use mydb1

go

Select * into #a from  tbl

Go

Use mydb2

Go

Select * into tbl2 from mydb1.#a

 

Insert into tab1 (a,b,c,d) Select (e,f,g,h) from tab2             -----复制tab2内容到tab1中，（a,b,c,d,对应于e,f,g,h）

以上语句可以完成跨越数据库的表结构，信息的复制

 

 

 

Exists：用于指定一个字查询，检测当前行的存在(子查询一般涉及到关联)。

Any :用于标量值与单列集中的值进行比较 ex:select * from a where id =any (select id where b)显示a表中条件为a表id==b表id

In: 确定子查询给定的值或列表是否与当前查询匹配 select * from a where id in(‘a’,’b’,’c’,…)查询条件为id在‘a’,’b’,’c’…中的信息

Datediff(day,dateadd(day,10,getdate()),getdate())返回两个指定日期间间隔的day

数学函数Len()和函数datalength()的区别：len(),获取的是字符串所占的字符数，datalength()获取的是字符串所占的字节数。Ex:len(‘lenss哈哈’)返回值为---7---，而datalength(‘lenss哈哈’)返回值为---9---