联结+变量+索引+表数据转存

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类型、
declare @t table (a int ,b int)
insert into @t values(1,2)
select * from @t

--临时表 session
select * into #tmp
from stu


select * from #tmp

--插入：
-- insert
--select into
--insert into
insert into #tmp
selecty * from biao

--异常处理
--抛出异常，raiseroor
--捕获异常：@@error

declare @i int
set @i=0

begin
if @i=0
raiserror('除数不能为0',16,1)
end
if @@error>0
select '除数不能为0'

declare @i int
set @i=0
print (1/@i)
select @@error

--视图：sql语句，他的数据来源于基表
--可以通过视图插入数据吗？
create view V_STU
with encryption
as
select * from stu

--索引
create table test
(
a int ,
b int
)
insert into test values(4,1)
select * from test

/*
定义：提升查询效率。比方：select * from stu where sname='abc'
索引的分类：
聚集索引：有且仅有一个，一般都给主键了。
要影响物理存储的，并将数据排序
非聚集索引：有很多>=-0个
使用逻辑算法将数据排序，不会影响物理存储。
程序员建立的索引默认都是非聚集
create index IND_TEST on test(a)
场景
*/
create clustered index IND_TEST on test(a)
create index IND_TEST on test(a)
create nonclustered index IND_TEST on test(a)