学习中的SQL

drop table t1
create table t1(
 id int identity (1,1) not null ,
 q1 varchar(50) null,
 q2 varchar(50) null,
 q3 int null,
 q4 int null
)
drop table t2
create table t2(
 id int identity (1,1) not null ,
 w1 varchar(50) null,
 w2 varchar(50) null,
 w3 int null
)

insert into t1(q1,q2,q3,q4) values('1','2',3,11)
insert into t1(q1,q2,q3,q4) values('22','23',34,12)
insert into t1(q1,q2,q3,q4) values('33','24',35,13)
insert into t1(q1,q2,q3,q4) values('44','45',46,14)
insert into t1(q1,q2,q3,q4) values('66','','',15)
insert into t1(q1,q2,q3,q4) values(NULL,'45',null,16)

insert into t2(w1,w2,w3) values('1','2',3)
insert into t2(w1,w2,w3) values('23','22',11)
insert into t2(w1,w2,w3) values('33','24',35)
insert into t2(w1,w2,w3) values('44','45',46)
insert into t2(w1,w2,w3) values('88','65',8)
insert into t2(w1,w2,w3) values('','75', '')
insert into t2(w1,w2,w3) values('79',NULL,NULL)
insert into t2(w1,w2,w3) values('79','age','')
insert into t2(w1,w2,w3) values('79','brand',NULL)

/*查询出所有表t1,t2的数据t1表ID与t2表id相等的表*/
select a.q1,a.q2,a.q3,b.w1,b.w2,b.w3 from t1 a join  t2 b on a.id=b.id

/*单查询表t1,t2*/
select * from t1
select * from t2

/*not in 找出表t1中q1有而在t2中w1没有的所有记录*/
select * from t1 where q1 not in(select w1 from t2)

/*SUM(ISNULL(q1,0))    ISNULL(q2,0)    sum(isnull(q1,0)+sum(isnull(q2,0)))  118 81 199*/
select sum(isnull(q3,0)) as 别名 from t1    /*结果返回118*/
select sum(q4) as 别名 from t1             /*结果返回81*/
select isnull(q3,0) as 别名 from t1    /*结果返回q3列把NULL转换成0*/
select q3 as 别名 from t1 order by isnull(q3,0) desc     /*把NULL转换成0正行排列*/
select sum(isnull(q3,0)+isnull(q4,0)) as 别名 from t1    /*结果返回199*/
select sum(q3+q4) as 别名 from t1     /*结果返回183 null+16 返回空*/

/*使用sum和count代替avg*/
select q3 from t1     /*查询q3列*/
select sum(q3)/count(*) from t1    /*结果返回19*/
select sum(isnull(q3,0))/count(*) from t1  /*结果返回19*/
select avg(isnull(q3,0)) from t1   /*结果返回19,把空转换成0*/
select avg(q3) from t1     /*结果返回23，NULL没有计算成数量*/
select avg(q3) from t1 where id<6   /*测试，结果返回23，从这里可以知道NULL没有算作总列数*/

/*max 代替 min*/
select min(q3) from t1    /*结果返回0,min*/
select -min(-q3) from t1   /*结果返回46,min 转换max*/
select max(q3) from t1    /*结果返回19,max*/
select -max(-q3) from t1   /*结果返回19,max转换min*/

/*order by 时把NULL排在a 开头的字符串和以b开头的字符串中间*/
select ascii(left(w2,1)),isnull(w2,'a'),ascii(w2) from t2 order by isnull(ascii(left(w2,1)),97.7) /*这里没有整理明白，没有到想要的结果*/
select isnull(ascii(w2),97.5) as u,w2, isnull(w2,97.5) as y from t2 order by u

select * From t2
/*把某一列的零值用NULL替换 nullif(字段名，0)*/
select nullif(w3,0) from t2    /*把int w3中所有0值转换成NULL*/

 

drop table t
create table t(
 id int identity not null,
 name varchar(20),
 wage int,
 sex int
)

insert into t(name,wage,sex) values ('A',1100,1)
insert into t(name,wage,sex) values ('B',1000,0)
insert into t(name,wage,sex) values ('C',900,1)
insert into t(name,wage,sex) values ('D',800,1)
insert into t(name,wage,sex) values ('E',700,0)

SELECT * FROM T
/*工资大于等于1000的为高工资，其它的为低工资*/
SELECT name as 姓名,工资类别=case
  when wage>1000 then '高工资'
  else '低工资'
  end
from T

/*这样可以统计出工资类别，以及工资 类别的人数 GROUP BY 分组*/
SELECT 工资类别=case
  when wage>1000 then '高工资'
  else '低工资'
  end,
 人数=COUNT(*)
from t  GROUP BY CASE
   WHEN wage>1000 then '高工资'
   else '低工资'
  end

/*得到性别为男的sum总合*/
select 男=sum(case sex
     when 1 then 1
   else 0
       end),
 女=sum(case sex
     when 0 then 1
  else 0
       end
  
) from t

/*得到性别为男的count总合*/
select 男=count(case sex
              when 1 then 1
     
  end
  ),
 女=count(case sex
        when 0 then 1
   
  end 
  )
from t

/*统计男女的人数*/
select 性别=case
  when sex=1 then '男'
  else '女'
     end,
 人数=count(*)
from t group by case
  when sex=1 then '男'
  else '女'
     end

 

drop table xt
create table xt
(
 id int identity not null,
 year int,
 jb varchar(2),
 salse varchar(4)
)
/*清空表所有数据*/
truncate table xt

insert into xt(year,jb,salse) values (1990,'春','1.1')
insert into xt(year,jb,salse) values (1990,'夏','1.2')
insert into xt(year,jb,salse) values (1990,'秋','1.3')
insert into xt(year,jb,salse) values (1990,'冬','1.4')
insert into xt(year,jb,salse) values (1991,'春','2.1')
insert into xt(year,jb,salse) values (1991,'夏','2.2')
insert into xt(year,jb,salse) values (1991,'秋','2.3')
insert into xt(year,jb,salse) values (1991,'冬','2.4')

select * from xt

select 年份=year,春=sum(case 
   when jb='春' then cast(salse as float)
   else 0
   end
  ),
  夏=sum(case
   when jb='夏' then cast(salse as float)
   else 0
   end
  ),
  秋=sum(case
   when jb='秋' then cast(salse as float)
   else 0
   end
  ),
  冬=sum(case
   when jb='冬' then cast(salse as float)
   else 0
   end
  )
from xt group by year

 

/*联接:笛卡儿积（直乘积或直交积）的过渡,自然连接 ，下面得到的两个同样的结果*/
select a.q1,b.w1 from t1 a join t2 b on a.id=b.id
select q1,w1 from t1 join t2 on t1.id=t2.id

/*普通连接：非等值连接的连接*/
/*HAVING 的语法如下：

SELECT "栏位1", SUM("栏位2")
FROM "表格名"
GROUP BY "栏位1"
HAVING (函数条件)

*/

drop table xt1
create table xt1(
 id int identity not null,
 name varchar(8) null,
 number int null,
 rank int null
)

truncate table xt1
insert into xt1(name,number,rank) values ('A',100,2)
insert into xt1(name,number,rank) values ('B',90,1)
insert into xt1(name,number,rank) values ('C',80,6)
insert into xt1(name,number,rank) values ('D',70,3)
insert into xt1(name,number,rank) values ('E',60,4)
insert into xt1(name,number,rank) values ('F',99,5)

select * From xt1
SELECT a.NAME,更高的分数=b.number from xt1 a join xt1 b on a.number<=b.number
/*通过name分组统计出，总合number分值大于80的所有值，having 做为一个条件*/
select name,名次=count(*) from xt1 group by name having sum(number)>80

/*得到名次上升的人的名字,并得出上升了多少名 desc,asc*/
select * From xt1 order by number desc
select a.name,a.rank, 名次=count(*),上升名=(a.rank-count(*)) from xt1 a join xt1 b on a.number<=b.number  group by a.name,a.rank having count(*)<a.rank

drop table xt2
create table xt2(
 id int identity not null,
 name varchar(20),
 lastSorce int,
 nowSorce int
)

truncate table xt2
insert into xt2 (name,lastSorce,nowSorce) values ('A',100,95)
insert into xt2(name,lastSorce,nowSorce) values('B',90,100)
insert into xt2(name,lastSorce,nowSorce) values('C',80,90)
insert into xt2(name,lastSorce,nowSorce) values('D',70,80)
insert into xt2(name,lastSorce,nowSorce) values('E',92,70)
insert into xt2(name,lastSorce,nowSorce) values('F',60,88)

select * from xt2
/*形成笛卡儿积，加入上次排名和本次排名，过滤：上次排名=本次排名*/
/*导出表*/
select 名次=(select count(*) from xt2 t where t.lastSorce>t.lastSorce)

select count(*) as 名次, a.lastSorce, a.nowSorce from xt2 a join xt2 b on a.lastSorce<b.lastSorce group by a.lastSorce,a.nowSorce order by 名次

/*外联，union 和 union 前者把重复的内容去掉了，而后者是不去掉重复的内容的*/
/*动态SQL语句*/
exec('select * from xt2')
exec sp_executesql N'select * from xt2'

declare @fName varchar(20)
set @fName='name'
exec('select '+@fName+' from xt2')
exec sp_executesql N'select '+@fName+' from xt2'   /*不能执行*/

declare @fName varchar(20)
set @fName='name'
declare @s varchar(50)
set @s='select '+@fName+' from xt2'
exec(@s)
exec sp_executesql @s /*@s 不能为varchar*/

declare @fName varchar(20)
set @fName='name'
declare @s Nvarchar(50)
set @s='select '+@fName+' from xt2'
exec(@s)
exec sp_executesql @s /*@s 可以为varchar，nchar,ntext类型*/