SQLServer2005 常用语句

use asp
go
select * from 用户
select * from 订单
select * from 订单细目
select * from 书

select 订单.订单编号,用户.用户名,用户.地址,订单.购买日期,书.书名,
书.单价,订单细目.数量,订单细目.数量*书.单价 as 小计--订单细目.金额
from 用户,订单,订单细目,书
where 订单.用户编号=用户.用户编号 and 订单.订单编号=订单细目.订单编号
and 订单细目.书的编号=书.编号 --and 订单.订单编号=6
go

--子查询--
--尝试与提高
--1.断号的查询
use java_data
go
create table n(n1 int)
go
select * from n
select * from n where n1 not in(select n1+1 from n)
go

--2.为表加行号
select * from dept
select sn=4,* from dept
alter table dept add sn int identity
go
alter table dept drop column sn
go
--函数 ROW_NUMBER() sql 2005中
select ROW_NUMBER() OVER(ORDER BY dept_id asc) AS 'Row Number',*
from dept
go
--子查询
delete from dept where dept_name='test'
select sn=(select count(*) from dept d1 where d1.dept_id<=d2.dept_id),* from dept d2
go
select sn=(select count(*) from stu_s s1 where s1.stu_score<=s2.stu_score),* from stu_s s2
order by s2.stu_score asc
go
--3.删除重复行
create table ss(
s1 varchar(10),
s2 varchar(10)
)
go
select * from ss
--3.1 临时表
create table #t(t1 int,t2 int)
go
select * from #t
select distinct * from ss
select distinct * into #ss from ss
go
select * from #ss
--方法一：
drop table ss
go
select * into ss from #ss
go
select * from ss
--方法二：
delete from ss
--select * into ss from #ss
insert into ss select * from #ss
go
select * from ss

--3.2 子查询
select * from ss
alter table ss add s3 int identity
go
select *
from ss f_s,ss s_s
where f_s.s1=s_s.s1 and f_s.s2=s_s.s2 and f_s.s3<s_s.s3
go
delete from ss where s1 in(
select s1 from ss s_s where ss.s1=s_s.s1 and ss.s2=s_s.s2 and ss.s3<s_s.s3
)
go
alter table ss drop column s3
go