数据库sql 语句，期待高手出手相救

        因为最近酷爱数据库查询，以前知道数据库特别容易学，可是今天回想起来，却是那么的无助，除了排序和分组，基本的统计查询一点都不会，今天用了点时间，写了些代码，希望高手能帮帮我！

use test
go
CREATE TABLE [myTrea] (
 [trea_id] [int] NOT NULL ,
 [trea_name] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [trea_date] [datetime] NULL ,
 [trea_price] [float] NULL ,
  PRIMARY KEY  CLUSTERED
 (
  [trea_id]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [myTrea2] (
 [trea_id] [int] NOT NULL ,
 [trea_name] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [trea_date] [datetime] NULL ,
 [trea_price] [float] NULL ,
 [trea_regdate] [datetime] NULL ,
  PRIMARY KEY  CLUSTERED
 (
  [trea_id]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

select * from  dbo.stuscore
--查找语文和数学的成绩
select * from  dbo.stuscore where subject='数学' union
(select * from  dbo.stuscore where subject='语文')
--查找语文及格的人
select * from  dbo.stuscore where score >any (select score from stuscore where score>60  ) and subject='语文'
--SELECT INTO 和 INSERT INTO SELECT 两种表复制语句
--此语句要myTrea2存在
Insert into myTrea2(trea_id,trea_name,trea_date) select trea_id,trea_name,trea_date from myTrea
--此语句要myTrea2 不存在
select trea_id,trea_name,trea_date into myTrea3 from myTrea
--查找语文最高成绩的学生
select * from stuscore  as a,
(select stuid,max(score) as 成绩 from stuscore where subject='语文' and score=(select  max(score) from stuscore where subject='语文') group by stuid) as b
where a.stuid=b.stuid
--查找成绩最高分
select DISTINCT a.stuid ,a.name ,b.maxscore as 总分 from stuscore as a ,
(select top 1 stuid,sum(score) as maxscore from stuscore  group by  stuid order by maxscore desc)  as b
where a.stuid=b.stuid
--查找每位学生的平均分
select DISTINCT a.stuid,a.name,b.avgscore as 平均分  from stuscore  as  a,
(select stuid,avg(score) as avgscore from stuscore group by stuid) as b
where a.stuid=b.stuid
--查找语文的平均分
select subject,avg(score) as 平均成绩 from stuscore where subject='语文' group by  subject
--其实这个平均分没有算好，其实语文的总分是350,有5个学生，平均分是70，有待求高手
(select sum(score) as ascore from stuscore where subject='语文')
(select count(DISTINCT stuid) from stuscore)

 

--列转行静态的方法
select name 姓名,
  max(case subject when '语文' then score else 0 end) 语文,
  max(case subject when '数学' then score else 0 end) 数学,
  max(case subject when '物理' then score else 0 end) 物理
from stuscore
group by name
--列转行定义方法动态的方法
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then score else  0 end) [' + Subject + ']'
from (select distinct Subject from stuscore) as a
set @sql = @sql + ' from stuscore group by name'
exec(@sql)

 

 

我陆续会增加文章内容，上面有错的，或者需要改正的，希望各位高手踊跃告诉我！

--T-SQL 语句
--定义(声明)变量给变量赋值
declare @username varchar(4)  --定义变量(声明,变量名称,变量数据类型 必须指定长度，否则一直为1)
declare @userid   int         --自动增长列
select @username='abcd'       --select赋值
set @username='艳明二号'      --se赋值
set @userid=(select max(trea_id)  from mytrea)--获取自动增长列的值
print @username
print @userid
insert into mytrea values(@userid+1,@username,dateadd(day,-1,getdate()),15000)
select * from mytrea where trea_name =@username
--if else 的用法
declare @rowcount int
if not exists(select trea_id from mytrea)
  print '暂时没有数据'
else
  set @rowcount=(select count(trea_id) from mytrea  )
  print cast(@rowcount as varchar)+'条数据被检索到'
--begin 和end 用法如果条件内部要跟代码块(多条语句)就要用到begin和end
declare @trea_id int
if(@trea_id is null)
  begin
   print '@trea_id没有赋值';
   set @trea_id=(select trea_id from mytrea where trea_id=1)
   print '@trea_id已经获值'+convert(varchar,@trea_id);
  end
else
  print '@trea_id已经赋值'+convert(varchar,@trea_id);
--case 用法
select * from mytrea
select trea_id,trea_name,trea_date
   =case
  when  trea_date ='2012-06-12' then trea_price
  when  trea_date ='2012-05-12' then trea_price
  else '0'
  end
from mytrea
-- while 循环语句while允许break和continue的存在
while 1=1
  begin
  waitfor time '10:21'
  update mytrea set trea_name = '天下无敌2' where trea_id = 1
  waitfor time '10:23'
  SELECT GETDATE()
  PRINT '该下班了！'
end
--:inner join 是在做排除，如果任一行在两个表中不匹配，则注定将从最终的结果中排除掉
select * from mytrea  a inner join   mytrea2  b on a.trea_id=b.trea_id
--outer join无论左侧表(e)中的行是否与右侧表中的行相匹配都要显示(就是left join 或者right join)
select * from mytrea  a left outer join   mytrea2  b on a.trea_id=b.trea_id
--full  join  与  cross  join
--full  join  是  left join  和  right join 的结合
--full  join将包含位于连接两侧的表的所有行
select * from mytrea  a full outer join   mytrea2  b on a.trea_id=b.trea_id
--unionunion更像是从一个查询直接向另一个查询进行的数据追加（差别还是有的）
--1：select 列表中字段的数量必须相同
--2：字段的数据类型必须隐士兼容
--3：返回的结果集的标头取自第一个查询
--4：默认返回方式是distinct，union  alll返回全部的集合
select trea_name from mytrea union  all select trea_name from mytrea2
--any  some  和  all
--any与some功能上是相同的，推荐用some
--any与some一般都是和比较运算符一起使用的(>=  <=  <>  !> 等  )
-->some (1,2,3)意味着大于1    >some就是大于最小的一个值
--=some和in  起的作用是一样的
--not in (a,b,c)展开为  <>a and  <>b and <>c
--<>some (a,b,c)展开为  <>a or <>b  or <> c
-->all (1,2,3)意味着大于3
select * from mytrea where trea_id = any(select trea_id from mytrea  where trea_name='艳明')
select * from mytrea where trea_id <> all(select trea_id from mytrea  where trea_name='艳明')
select * from mytrea where trea_id = all(select trea_id from mytrea  where trea_id=2)
--创建视图:删除和修改视图
--alter view  yourviewname   as...
--drop view  yourviewname   as...
--加密视图alter  view  yourviewname  with encryption as...加密了之后连你自己也看不到原代码了
create view myview
as
select * from mytrea
select * from myview

--存储过程
create proc myProc
as
update mytrea set  trea_name='存储过程' where trea_id=7
select * from mytrea
exec myProc  --调用
--带参数存储过程
create proc spComputer
    @trea_name nvarchar(50) = null
as
if @trea_name is null
  print '你未输入查询参数'
else
select * from mytrea where trea_name=@trea_name

exec spComputer '存储过程'
--输出存储过程
drop proc spComputer
alter proc spComputer
    @reX      nvarchar(50) ='123',
    @reY      nvarchar(50) = '456',
    @LastName nvarchar(50) = null output
as
if @LastName is null
    begin
        print @reX
        return '123';
    end
else
    begin
        print @reY
        return '456'
    end
--调用的
declare @myOut varchar(50)
set @myOut='123'
exec @myOut = spComputer @myOut,@myOut,@myOut output
print @myOut

--自定义函数(时间格式处理)
drop function DayOnly
Create function dbo.DayOnly(@date datetime)
    returns varchar(12)
as
begin
    return convert(varchar(12),@date,103)
end
select  dbo.DayOnly(trea_date) from mytrea --时间调用

--自定义table 查询
create function dbo.fnTableSearch(@TreaName nvarchar(50))
   returns table
as
  return (select * from mytrea where trea_name like @TreaName+'%')

select * from fnTableSearch('存储过程') --调用table 数据
--需要参数函数
drop function selTableByParam
create function dbo.selTableByParam
    (@id as int,@name as varchar)  --输入参数
    returns @allrows table  --临时表(及字段)
    (
        trea_id  int not null, 
        trea_name  nvarchar(40) null,
 trea_price float   null
    )
as
begin
    insert into @allrows  select trea_id,trea_name,trea_price from mytrea where trea_id = @id
return
end
go
select * from selTableByParam(2,'12233')