最近在加强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