联结+变量+索引+表数据转存
select type,sum(price) from titles
where type in('business','mod_cook')
group by type
having type='business'
create database stu

use stu

drop  table stu
create table stu
(
  sid int identity(1,1) primary key,
  sname nvarchar(20)
)

insert into stu values('c')
create table scos
(
  ssid int identity(1,1) primary key,
  sid  int references stu(sid),
  sco int,
  km nvarchar(20)
)
insert into scos values (2,60,'c')
insert into scos values (NULL,90,'c')

select * from stu
select * from scos
--
--交叉.定义：场景:
   select * from stu,scos

--内联接:定义：两个表相同的部分，场景：
  select a.sname,b.sid,b.sco,b.km,b.sco
     from stu a,scos b  
  where a.sid=b.sid

  select a.sname,b.sid,b.sco,b.km,b.sco
     from stu a inner join scos b  
  on a.sid=b.sid
 
select a.sname,b.sid,b.sco,b.km,b.sco
     from stu a  join scos b  
  on a.sid=b.sid

--采用优化器 hash,merge,romote,
  select a.sname,b.sid,b.sco,b.km,b.sco
     from stu a inner merge join scos b  
  on a.sid=b.sid


--外联结
--左外：内联接+左边异同
select a.sname,b.sid,b.sco,b.km,b.sco
     from stu a  left outer join scos b  
  on a.sid=b.sid
--右外
select a.sname,b.sid,b.sco,b.km,b.sco
     from stu a  right outer join scos b  
  on a.sid=b.sid

--全外
select a.sname,b.sid,b.sco,b.km,b.sco
     from stu a  full outer join scos b  
  on a.sid=b.sid

--自联结,定义
select a.city,a.au_lname,b.au_lname from
   authors a
  inner join  authors b
  on (a.city=b.city and a.au_lname !=b.au_lname)
order by a.city
--连接,a b串联
--union,minus
  select 1 a,2 b
  union all
  select 1 a,2 b

  select fname,hire_date from employee
   union
  select au_lname,getdate() from authors
--链接查询，也叫即席查询
  select fname,lname from employee
  union
  select firstname,lastname from northwind..employees

  select * from  OPENROWSET('MSDASQL',
   'DRIVER={SQL Server};SERVER=localhost;UID=sa;PWD=sa',
   pubs.dbo.authors)

  --any some all
  -->any/some 大于最小值 
  -->all大于最大值
--标准sql end
--T-SQL
--变量常量
declare @i int
--给变量赋值
--m1：
set @i=1
--m2:
select @i =2
--输出
--m1:
print @i
--m2:
select @i
--if
declare @j int
set @j = 2
if @j>1
   print '...'
else
   print 'sss'
--代码段
declare @j int
set @j = 2
if @j>1
   begin
      print '...'
      print '...ddd'
   end
else
   print 'sss'
--case when
  declare @k int
  select @k=1
  select
  case @k
    when 1 then 'dsf'
    when 2 then 'dsf2'
    else  'dsf3'
  end

--select into
select * into stu2 from stu
--while
  select * from stu2

  declare @i int
  set @i=1
  while @i<1000
  begin
   insert into stu2 values('test')
   set @i=@i+1
  end

--table类型、