sql笔记大全

1、消除卡值有重复的列
select distinct card from user

2、用假名标示字段
select age as 年龄 from user

3、 转换成另一种形式；转成值留二位小数
select cast(decimal(value,2)) from  user

4、 结果阶段判断
select
case
when value >=2000 then '很好'
when value >=1000 and value<2000 then '一般'
when value <1000 then '较差'
end as sign
from user

5、 查询介于之间的记录>=and<=
select * from user where age between 20 and 50

6、 查询结果是否包含于另一个集合中
select * from user where name in(select name from user)
查找那些用户名即不为张三又不为李四的人
select * from user where name not in('zhangsan','lisi')

7、 is not null非空

8、 模糊查询
查询是两个字的，其中一个字是‘平’的名字，_是个空间占位符
select name from user where name like '_平' or '平_'
查询某些记录，其工资是的百位数介于2-4之间，个位数介于0-9之间
select * from user where money like '_[2-6]_[0-9]'
查询某些记录，其工资是的百位数不含2-9之间的数,且姓名中包含一个‘平’字，该用户不是在office1就在office2
select * from user where money like '_[^2-9]__' and name like '%平%' and work in('office1','office2')

9、 查询某些用户，其工号或年龄中包含‘7’这个字
slect * from user where no + age  like '%7%'

10、显示年龄最大的100人
select top 100 * from user order by age desc
oracle是这么写的
select * from (select * from user order by age desc) where rownum <=3
显示20%年龄最小的人
select top 20 percent * from user order by age asc

11、随机生成并排序,选3个人
select top 3 * from user order by NEWID()

12、按所得税这个假名排列都行倒序
select name,noney,money*0.006 as 所得税 from user order by 所得税 desc

13、按用户需求对数据进行动态排序
select * from user order by
case sign
when 3 then '很好'
when 2 then '一般'
when 1 then '较差'
end

14、把某值转成另一种形式
cast(value as money) 钱形式
cast(value as decimal) 数值

15、把一个字符串从一种编码类型转换成另一种编码类型
convert(char, dest_char_set[, source_char_set ])

16、把值转成钱再转字符串再拼上符号
select name,'￥'+cast(cast(money as money) as varch(10)) as 工资 from user

17、分割字符串(好像是从1开始的，不是从0)
slect name,substring(name,1,1) as 姓,substring(name,2,2) as 名 from user

18、小写转大写
upper(string)

19、大写转小写
lower(string)

20、统计一共有多少年龄在20岁以上的男人
select count(*) as 个数 form user where sex = 'man' nad age > 20
统计一共有多少年龄在20岁以上的男人，但去除编号重复的记录
select count(distinct(no)) as 个数 form user where sex = 'man' nad age > 20

21、统计所有男人的工资总和
select sum(money) as 工资总额 from user where sex = 'man'
统计男人的总人数和总工资
select sum(*) as 男人总人数,sum(money) as 男人总工资 from user where no in (select no from user where sex = 'man')
统计北京厂的工资在1800以上的所有的人平均工资
select sum(money)/count(*) as 平均工资 from user where workplace = '北京厂' and money >1800
统计工资大于平均公资的人数
select count(*) as 人数 from user where money>  (select sum(money)/count(*) as 平均工资 form user)

22、最大和最小
select max(money) as 最高工资,min(money) as 最低工资 from user
加上工资差
select max(money) as 最高工资,min(money) as 最低工资,(max(money)-min(money)) as 工资差 from user
再加上评语
select max(money) as 最高工资,min(money) as 最低工资,(max(money)-min(money)) as 工资差,
case
when (max(money)-min(money))>300 then '差别很大'
when (max(money)-min(money))<=300 then '差别不大'
end as 评语
from user

23、返回仓库页面的平均值，NULL不算
select count(*) as 数量,max(area) as 最大面积, min(area) as 最小面积, sum(area) as 总面积, avg(area) as 仓库平均面积 from base
返回面积大于平均面积的仓库
select * from base where area>(select avg(area) from  base)
返回除最高工资和最低工资以外其他人的平均工资
select avg(money) as 平均工资 from user where money not in((select max(money)from user),(select min(money) from user))

24、返回确对值
abs(decimal)

24、除了所有列，还加一列虚拟的平均工资列
select *,(select avg(money) from user) as 平均工资 from user
列出名字、工资、工资与平均工资之差
select name,money,(money - (select avg(money) from user)) as 工资与平均工资之差 from user
列出名字、工资、工资与平均工资之差、评语
select name,money,(money - (select avg(money) from user)) as 工资与平均工资之差,
case
when abs(money-(select avg(money) from user))>1000 then '大'
when abs(money-(select avg(money) from user))>=500 then '中'
when abs(money-(select avg(money) from user))<500 then '小'
end as 工资评语
from user
列出不同仓库的平均工资
select *,
case
when store = 's1' then (select avg(money) from user where store = 's1')
when store = 's2' then (select avg(money) from user where store = 's2')
when store = 's3' then (select avg(money) from user where store = 's3')
end as 不同仓库的平均工资
from user

25、虚拟字段的排序
select *,
case
when base = 'base1' then (select max(money) form user where base = 'base1')
when base = 'base2' then (select max(money) form user where base = 'base2')
when base = 'base3' then (select max(money) form user where base = 'base3')
end as 不同仓库的最大工资
from user order by 不同仓库的最大工资 desc

26、用户工资与平均工资比较，大于则发0.9工资，等于就全发，小于就发1.1的工资，并把最终工资正序排列
select *,
case
when money>(select avg(money) from user) then money*0.9
when money=(select avg(money) from user) then money
when money<(select avg(money) from user) then money*1.1
end as 最终工资
from user order by 最终工资 asc

27、显示面积最大的仓库信息
select * from base where area = (select max(area) from base)
显示面积最大的仓库的人员情况
select * from user where base_no in (select no from base where area = (select max(area) from base))
显示那些即非最大亦非最小的仓库信息
select * from base where area not in((select max(area) from base),(select min(area) from base))

28、根据用户分类、用户名分组统计用户的卖出量-----注意：select后面字段，除了聚合函数里的字段（如number_sell），其它字段都必须在group by后面有使用（如sort和person），不然就违背聚合原则，就会报错。
select d.sort,d.person as 用户,count(*) as 数量,avg(d.number_sell) as 卖出平均量 from trade d GROUP BY d.sort,d.person
根据仓库分类统计男人的平均工资
select base_no,avg(money) as 平均工资 from user where sex != 'woman' group by base_no
根据仓库分类统计平均工资，前提条件是人的性别不为空且工资大于base1仓库的平均工资
select no,avg(money) as 平均工资 from user where sex is not null and (money >(select avg(money) from user whrere no = 'base1')) group by no

29、where和having的意义一样，如果是写在group by前面，就用where,如果是写在group后面，就用having
显示平均工资大于1700的不同仓库的平均工资
select no,avg(money) as 平均工资 from user group by no having avg(money)>1700
select no,avg(money) as 平均工资 from user where avg(money)>1700 group by no
根据仓库分组统计最大工资用户和最小工资用户的工资差，前提条件是工资差大于350
select no,abs(max(money)-min(money)) as 最大工资和最小工资之差 from user group by no having abs(max(money)-min(money))>350

30、group by 后面跟all表示：即便all后面的记录不符合聚合条件，也会将它列出来
select no,max(money) as 最大,min(money) as 最小,(max(money)-min(money)) as 最大最小之差 from user where money>1000 group by no
select no,max(money) as 最大,min(money) as 最小,(max(money)-min(money)) as 最大最小之差 from user where money>1000 group by all no
select no,max(money) as 最大,min(money) as 最小,(max(money)-min(money)) as 最大最小之差 from user group by no having money>1000

31、group by也是可以order by的
根据仓库分类统计平均工资，前提条件是人的性别不为空且工资大于base1仓库的平均工资
select no,avg(money) as 平均工资 from user where sex is not null and (money >(select avg(money) from user whrere no = 'base1')) group by no order by 平均工资

32、显示面积等于仓库1面积的仓库（排除仓库1自己）
select * from store where (no != 'store1' and area in (select area from store where no = 'store1' ))

33、显示工资不大于仓库1和仓库2的平均工资的人员信息
select * from user where money <= (select avg(money) from user where (no = 'store1' or no = 'store2'))

34、显示工资大于仓库1的最高工资且小于仓库2的最高工资的用户信息
select * from user where money > (select max(money) from user where no = 'store1') and money < (select max(money) from user where no = 'store2')

35、显示面积大于北京地区仓库的平均面积或小于济南地区仓库的最小面积的仓储的信息
select * from store where area > (select avg(area) from store where city = 'bj') or area < (select avg(area) from store where city = 'jn')

36、显示工资在仓库1平均工资和所有仓库平均工资之间的人员信息
select * from store where ((monry <= (select avg(monry) from store where store = 'store1') and monry >= (select avg(monry) from store)) or (monry >= (select avg(monry) from store where store = 'store1') and monry <= (select avg(monry) from store))
select * from store where ((monry between (select avg(monry) from store where store = 'store1') and (select avg(monry) from store)) or (monry  between (select avg(monry) from store where store = 'store1') and (select avg(monry) from store)))

37、显示工资不大于北京地区仓库平均工资的职工信息
select * from user where store_no in (select store_no from store where city = 'bj')

38、显示仓库面积最大或最小的的职工信息
select * from user where store_no in ( select store_no from store where area = ((select max(area) from store),(select min(area) from store)))

39、显示仓库面积最大或最小的的职工的订单信息
select * form order where user_id in(select user_id from user where store_no in ( select store_no from store where area = ((select max(area) from store),(select min(area) from store))))

40、显示工资不是最高也不是最低的职工所在仓库的信息
select * from store where store_no in(select distinct(store_no) from user where money not in ((select max(money) from user),(select min(money) from user)))

41、exists 是否存在，返回真或假，是其它查询的前置条件
如果根据用户表里的用户存储的仓库ID，可以在仓库表里找到该仓库信息，则显示这些记录的名称
select s.name from store s where exists(select u.* from user u where u.store_no = s.no)

42、显示有职工并且工资大于2000的仓库信息
select s.* from store s wehre exists(select u.* from user u where u.store_id = s.id and u.money > 2000)

43、any 集合中任何一个为真即返回真，全假才为假，是其它查询的前置条件
显示工资大于store1仓库任何一名职工工资的职工信息
select u.* from user u where u.money > any( select s.money from user s where s.no = 'store1' )

44、显示姓名中含一个“平”字，并且工资大于姓名中含“王”字的任一名职工工资的职工信息
select u.* from user u where ((u.name like '%平%') and (u.money > any(select s.money from user s where s.name like '%王%')))

45、all 集合中全部成员都为真才返回真，否则即为假，是其它查询的前置条件
显示工资大于等于store1仓库中所有职工工资的职工信息
select u.* from user u where u.money >= all(select s.money from user s where s.no = 'store1')

46、在订购单中订加平增多工资信息
select o.*,(select avg(u.money) from user u) as 平均工资 from order o

47、显示用户姓名、工资、所属仓库的平均工资
select u.name,u.money,
case
when u.store_no = 'store1' then (select avg(a.money) from user a where a.store_no = 'store1')
when u.store_no = 'store2' then (select avg(a.money) from user a where a.store_no = 'store2')
when u.store_no = 'store3' then (select avg(a.money) from user a where a.store_no = 'store3')
end as 所属仓库的平均工资
from user u

48、按平均工资从高到低显示不同仓库的编号、平均工资、职工人数、最大工资
select u.store_no,avg(u.money) as 部门平均值,count(u.*) as 职工人数,max(u.money) as 最大工资 from user u group by u.store_no order by 部门平均值 desc

49、显示不同职工经手订单金额最大的订单信息
select o.* from order o where o.money = (select max(a.money) from order a where a.name = o.name)

50、显示职工的姓名、工资、平均工资及销售金额，条件是工资大于等于平均工资
select u.name,u.money,(select avg(u2.money) from user u2) as 平均工资,o.xs from user u,order o where (u.money >= (select avg(u3.money) from user u3))

51、显示职工的姓名、工资、工资与平均工资之差及销售金额，条件是工资与平均工资之差大于400
select u.name,u.money,u.money - (select avg(u2.money) from user u2) as 工资与平均工资之差,o.xs from user u,order o where abs(u.money - (select avg(u3.money) from user u3)) > 400

52、带有保存功能的多表连接查询
查询仓库号、所属城市、仓库面积，用户名、工资，用户销售金额，将结果写到新表里
select s.no,s.city,s.area,u.name,u.money,o.je into new_table from store s,user u,order o where s.no = u.store_no and u.no = o.user_no

53、内连接（交集）
显示职工姓名及其所在仓库的城市信息
select u.name,s.city from user u inner join store s on u.store_no = s.no

54、显示职工姓名及其所在仓库的城市信息，城市不为空，姓名中含有“王”字
select u.name,s.city from user u inner join store s on u.store_no = s.no and s.city is not null and u.name like '%王%'

55、显示职工所在城市、面积、姓名、工资和金额信息，条件是工资大于1800、面积小于1000、金额不等于16600
select s.city,s.area,u.name,u.money,o.je from store s inner join user u on s.no = u.store_no inner join order o on o.user_no = u.no and u.money > 1800 and s.area < 1000 and o.je != 16000

56、内连接（交集），左连接（左表全+右表的交集部分），右连接（右表全+左表的交集部分），全连接（并集）
inner join left join right join full join

47、显示城市不为空，姓名中包含“王”字的人员和和城市信息，用左连接现实。
select u.name,s.city from store s left join user u on u.sotre_no = s.no and s.city is not null and u.name like '%王%'

48、显示城市不为空，姓名中包含“王”字的人员和和城市信息，用右连接现实。
select u.name,s.city from store s right join user u on u.sotre_no = s.no and s.city is not null and u.name like '%王%'

49、显示城市不为空，姓名中包含“王”字的人员和和城市信息，用全连接现实。
select u.name,s.city from store s full join user u on u.sotre_no = s.no and s.city is not null and u.name like '%王%'

50、显示职工所在城市、面积、姓名、工资和金额信息。条件是工资大于1800，面积小于1000,金额不等于16600，仓库表与职工表左连接，职工表与订单表右连接。
select s.city,s.area,u.name,u.money,o.je from store s left join user u on s.no = u.store_no right join order o on o.user_no = u.no and u.money > 1800 and s.area < 1000 and o.je != 16000

51、union、intersect、except查询结果集合的并、交、差（注意：内连接、左连接、右连接是针对SQL本身）

52、列出两组查询结果并集
select s.no from store s where s.city = 'bj'
union
select u.store_no from user u where u.money > 2000

53、列出两组查询结果并集,但保留重复行
select s.no from store s where s.city = 'bj'
union all
select u.store_no from user u where u.money > 2000

54、列出两组查询结果交集
select s.no from store s where s.city = 'bj'
intersect
select u.store_no from user u where u.money > 2000

55、列出两组查询结果差集(前面集里不属后面集的元素组成的新集)
select s.no from store s where s.city = 'bj'
except
select u.store_no from user u where u.money > 2000

56、绝对值函数abs(num)
abs(1)=1
abs(-1)=1

57、返回大于等于N的最小整数值ceiling(num)
ceiling(1.7)=2
ceiling(-1.5)=-1

57、随机函数rand()
rand()*10 在10以内产生随机数
rand()*100 在100以内产生随机数

58、ascii(char) 字符转ASCII码
char(ascii) ASCII码转字符

59、str(num) 数字转字符串

60、upper(str)小写转大写
lower(str)大写转小写

61、len(str)求取字符串长度
ltrim(str)去掉头部空格
rtrim(str)去掉尾部空格
left(str,length)左截取指定长度
right(str,length)右截取指定长度
subsring(str,start,length)从指定位截取指定长度
replace(str1,str2,str3)用str3替换str1里面的所有的str2
stuff(str1,start,length,str2)str1字符串从start起length长字符串由str2替换

62、cast(num) 转成字符串
convert(num,code)从一种编码转成另一种编码

63、getdate()当前日期
year()年
month()月
day()日
datename(返回形式,指定日期) 返回字符串
datepart(返回形式,指定日期) 返回整数型
dateadd(返回形式,偏移量,指定日期) 加日期
datediff(返回形式,日期1,日期2) 日期差
显示当前日期
csst(year(getdate()) as varchar(50))+'年'+csst(month(getdate()) as varchar(50))+'月'+csst(day(getdate()) as varchar(50))+'日'

64、获取时间
datename(hour.getdate())+':'+datename(minute.getdate())+':'+datename(second.getdate())
datediff(hour,'2014-5-6 05:36:24','2014-5-4 03:12:12') as 小时差
datediff(minute,'2014-5-6 05:36:24','2014-5-4 03:12:12') as 分钟差
datediff(second,'2014-5-6 05:36:24','2014-5-4 03:12:12') as 秒差

65、获取星期
select datename(dw,getdate()) as 今天是星期几
select datename(wk,datediff(wk,0,getdate()),0) as 本周第一天的日期

66、显示4年之内的订单情况
select * from order where regdate > dateadd(year,-4,getdate())