sql语句操作记录

表的结构为：

查询语句：

调用datediff()日期函数计算抢修天数，getdate()返回系统当前日期和时间

也可计算年龄：datediff(year,birthday,getdate())

取别名 ：

select prj_name 工程名,start_date开始日期,end_date,'抢修天数',datediff(day,start_date,end_date) as salve_day from salvaging;

 

 

建表语句：

create table out_stock(prj_num char(8),mat_num char(8),amount int,get_date datetime default getdate(),
department char(20,primary key(prj_num,mat_num),
foreign key(prj_num,mat_num),foreign key(prj_num) references salvaging(prj_num),
foreign key (mat_num) references stock(mat_num));

create table salvaging(prj_num char(8) primary key,
prj_name varchar(50),start_date datetime,
end_date datetime,prj_status bit);

drop table if exists 'stock'
create table stock(
    mat_num char(8) NOT NULL,
    mat_name varchar(50) NOT NULL,
    speci varchar(20), NOT NULL,
    warehouse char(20), NULL,
    amount int NULL,
    unit decimal(18, 2) NULL,
    total as ([amount]*[unit]),
    check  (mat_num like '[m][0-9][0-9][0-9]')),
    primary key (mat_num) 
)
 

查询外键与删除外键：

select name from sys.foreign_key_columns f join sys.objects o on f.constraint_object_id=o.object_id where f.parent_object_id=object_id('out_stock');

alter table out_stock drop constraint FK__out_stock__mat_n__182C9B23;

查询操作：

 

去重查询：（用distinct去重，默认是all，即不去重）

条件查询：

1.查询 供电局1仓库存放的所有物资，名称。。。

select mat_num,mat_name,speci,amount from stock where warehouse='1仓库'

确定范围查询：单价在50-100的物质，名称。。。

select mat_name,amount,unit from stock where unit between 50 and 100 

不再50-100的就not between

查询存放在1仓库和2仓库的物资：（等价与warehourse=‘1仓库’ or warehourse=‘2仓库’）

select mat_name,speci,amount from stock where warehourse in('1仓库','2仓库') 

否定用not in ()    等价与 ！=    and  ！= 

 

字符串匹配查询：

like '%_abc'    %通配任意字符，_通配一个字符

比如 ：   select * from stock where mat_name like '%绝缘'

排序查询：

物资名称降序，同一物资名按价格升序（默认升序） ：

select mat_name,unit from stock where mat_name not like '%架空%' order by mat_name desc,unit asc

输出前n条记录。

select top 2 mat_name from stock

输出库存最小的前30%的记录

select top 30 percent mat_num, amount from stock order by amount asc

使用聚集函数查询：

select max(amount) max_amount,avg(amount)avg_amount,count(distinct prj_num)count_prj_num from out_stock where mat_num='m001'

注意：除count函数外，其他函数均忽略null值，where语句中不能使用聚集函数作为条件表达式

查询结果分组（统计）

1.查询每个工程的项目号以及它所使用的物资种类数（这个必须要用到group by语句对查出来的项目号进行分组才能统计出它所对应的种类数。） (即先查，再分组，后统计)

select prj_num 项目号,count(*)物资种类 from out_stock group by prj_num

查询使用了两种及两种以上物资的工程号：

select  prj_num 项目号 from out_stock group by prj_num having count(*)>=2; 

分组查询并且使用rollup和cube关键字进行数据的报表，即数据的统计分析结果，并将统计分析的数据放入查询结果中：

select department,mat_num,count(distinct prj_num)项目个数, sum(amount)领取总量 from out_stock group by department,mat_num with rollup 

 select department,mat_num,count(distinct prj_num)项目个数, sum(amount)领取总量 from out_stock group by department,mat_num with cube

compute by查询语句，控制中断和小计： 

统计各仓库的物资总价值：

select mat_name,speci ,amount ,unit ,total,warehouse from stock order by warehouse compute sum(total) by warehouse

（sql server已经将其废弃，故不建议再使用）

嵌套查询中的子查询不能使用order by，order by只能对最终查询结果排序

 

多表查询（连接查询）：

等值连接（去重叫自然连接）：

select salvaging.prj_num,prj_name,start_date,end_date,prj_status,mat_num,get_date,

department from salvaging,out_stock where salvaging.prj_num=out_stock.prj_num;

外连接：

left outer join,

right outer join,

full outer join 

举个例子：

select salvaging.prj_num,prj_name,start_date,end_date,prj_status,mat_num,get_date,

department from salvaging left outer join out_stock on(salvaging.prj_num=out_stock.prj_num);

左连接，左边的全部显示，左连接（即自然连接比等值连接的查询结果数要多） 

复合条件查询;

查询使用了护套绝缘的项目编号及名称：

select out_stock.prj_num,prj_name from stock,out_stock,salvaging where stock.,at_num-out_stock.mat_num and salvaging.prj_num=out_stock.prj_num and mat_name='护套绝缘';

查询同时使用了m001和m002的抢修工程的工程号：

select A.prj_num from out_stock A ,out_stock B

where A.prj_num=B.prj_num and A.mat_num='m001' and B.mat_num='m002'

能不用连接查询的就不用连接查询，而应多用嵌套查询：

 

查询工程项目”沙河站抢修“所使用的物资编号和物资名称：

select mat_num,mat_name from stock where mat_num in

(select mat_num from out_stock where prj_num in

(select prj_num from salvaging where prj_name='沙河站抢修')

) 

等价于等值连接：

select stock .mat_num,mat_name from stock,out_stock,salvaging  where stock.mat_num=out_stock.mat_num and out_stock.prj_num=salvaging.prj_num and salvaging.prj_name='沙河站抢修'

 

相关嵌套查询：

查询库存量超过该仓库平均库存量的物资编号。。。。

select mat_num,mat_name,speci,amount from stock s1 where amount>(select avg(amount) from stock s2 where s2.warehouse=s1.warehouse)

 带有any或者all的谓词：

不等于！=  或者 < >     其中：

>all   表示大于最大值（大于所有，即大于所有值）

>any  表示大于 最小值（大于任何一个，即大于某个值）

注：聚集函数实现子查询比用any，all的查询效率高。

exists和not exist的使用 ：

查询所有使用了m001号物资的工程项目名称：

select prj_name from salvaging where exists(select * from out_stock where prj_num=salvaging.prj_num and mat_num='m001')

 每一次按条件对照取值，若有对照值，对照值相等，where中返回true，取出此次的查询结果。exist一般和select *一起用，因为返回true或false因此用实际列名无意义。

某些查询需要用到否定之否定才能查出来。

查询所用物资包含抢修工程‘20100016’所用物资的抢修工程号：

select distinct prj_num from out_stock sx where not exists(select * from out_stock sy where sy.prj_num='20100016' and not exists(select * from out_stock sz where sz.mat_num=sy.mat_num and sz.prj_num=sx.prj_num))

集合查询：

交（intersect），并（union），差（except）

1.交：

查询同时使用了m001和m002的工程号：

select prj_num from out_stock where mat_num='m001' intersect select prj_num from out_stock where mat_num='m002'

等价于；

select distinct prj_num from out_stock where mat_num='m001' and prj_num in (select distinct prj_num from out_stock where mat_num='m002')

2.并

查询使用了m001或m002的工程号：

select prj_num from out_stock where mat_num='m001' union select prj_num from out_stock where mat_num='m002'

2.补

查询存放在1仓库的物资且单价不大于50的物资的差集，（及是查询存放1仓库，单价大于50的物资）

select* from stock where warehouse='1仓库' except select * from stock where unit<50

 或者：

select* from stock where warehouse='1仓库' and unit>50