sql server 常用的查询语句

最近在加强sql 语句的学习，整理一下基本语法，现在记录下

select * from dbo.cangku where city='河南'

select distinct(city), cangkuId from dbo.cangku //取消重复的列值

select SUM (gongzi) as zgz from dbo.zhigong //总工资

select cangkuId ,city from dbo.cangku where not city='河南' //不等于

select * from zhigong where gongzi between 1000 and 1500 //between 在什么。。。之间

select * from zhigong where xingbie in('男') // 包含in

select * from zhigong where xingbie is null //不为空

select * from zhigong where xiangming like '%金%' //%代表多个字符

select * from zhigong where xiangming like '_金_' //下划线代表一个字符

select top 10 * from dbo.zhigong //查询前10条数据

select * from zhigong order by ID //按id排序

select * from zhigong order by NEWID() //NEWID() 随机排序

select avg (gongzi) as 平均工资 from dbo.zhigong //平均值

select max(gongzi) as 最高工资 from dbo.zhigong //最大值

select min (gongzi)as 最低工资 from dbo.zhigong //最小值

select count (*) as zs from dbo.zhigong //记录总数

select COUNT (distinct(cangkuId)) as zs from cangku //去除重复的总数

select MAX (gongzi)-MIN (gongzi)as chage from zhigong //工资差

select cangkuId,max(gongzi) as 不同仓库的最高工资 from zhigong group by cangkuId //查询不同仓库的最高工资

select cangkuId,AVG (gongzi) as 不同仓库的平均工资 from zhigong group by cangkuId having AVG (gongzi) >1000 //查询不同仓库的平均工资

select cangkuId,xingbei,MIN (gongzi) as 不同仓库的最低工资 from zhigong group by cangkuId,xingbei //查询不同仓库的最低工资

select name,SUBSTRING(name,1,1) as 姓氏, SUBSTRING (name,2,2) as 名字 from zhigong //SUBSTRING 截取

select cangkuId,UPPER(cangkuId) as 仓库号大写 from cangkuId //转换大写

select cangkuId,LOWER (cangkuId) as 仓库号小写 from cangkuId //转换小写

select cangkuId,CONVERT(char(10),createTime,111) as createTime from cangku //111代表日期格式 yy/mm/dd

巴特西