sql基础

一.截断字符串

declare @str varchar(100)

set @str='123456'

print substring(@str,1,5); 从1的前面算起截取长度为5的字符串

print substring(@str,1,5);从1算起截取长度为5的字符串

print left(@str,5) 从左边开始截取

print right(@str,5)

print ltrim(@str); 去掉字符串左边的空格

print rtrim(@str)

二.not 的用法

select *  from table where column not in ('a','b','c')

not和between连用时要紧跟在where的后面

select *  from table where not column between 10 and 30

not和or 、and连用时要紧跟在它们后面

select *  from table where not column like '%a'    and  not column2 like  '%b'

三.分组并算出各分组的合计、平均值、max、min、count

select  Username ,sum(number) as a from table group by Username

select  Username ,avg(number) as a from table group by Username

select count(distinct sale_date) from cook_sales

对不同用户的number做合计，然后再按合计数据大小排序

select  Username ,sum(number) as a from table group by Username order by sum(number)  desc

四.inner  join、left join 、right join、full join

student：

class：

inner join(内连接)

方法一

select * from student as s,class as c where s.classid=c.classid
方法二
select * from student as s inner join class c on s.classid=c.classid

select * from student as s inner join class c on s.classid<>c.classid

left join(左连接)

select * from student as s left join class c on s.classid=c.classid

right join(右连接)

select * from student as s right join class c on s.classid=c.classid

full join(全连接)

select * from student as s full join class c on s.classid=c.classid

cross join（交叉连接）

select * from student as s cross join class c 

select * from student as s ,class c 

条件查询

select * from student as s cross join class c  where s.classid=c.classid

select * from student as s , class c where s.classid=c.classid

(cross join 后的条件只能用where)

五、数据类型转换

select CONVERT(int, 12.1)

select CONVERT(int, '12.1')     报错

select CONVERT(int, convert(decimal(8,1),'12.1'))   正确

select CONVERT(varchar(200),GETDATE(),120)     获取当前时间、设置格式、以字符串形式输出

六、sql拼接并去重列值

  select( select distinct  CAST(F_ModuleName as varchar)+','  from Sys_Log where  F_ModuleName!=''  for xml path('')  ) as A

第4段这一部分是参考作者：于亮的博客

在此感谢他，谢谢分享