SQL Server 第三章 结构化查询语言
SQL脚本：
create database electric; use electric go create table Salvaging( prj_no int primary key, prj_name varchar(255), start_date datetime, end_date datetime, prj_status bit ) insert Salvaging values (20100015,'220kV清经线接地箱及接地线被盗抢修','2010-10-12','2010-10-13',1); insert Salvaging values (20100016,'沙河站2#公变出线电缆老化烧毁抢修','2010-11-05','2010-11-05',1); insert Salvaging values (20100001,'西丽站电缆短路烧毁抢修工程','2011-01-03','2011-01-03',1); insert Salvaging values (20100002,'西丽站电缆接地抢修','2011-01-03','2011-01-05',1); insert Salvaging values (20100003,'观澜站光缆抢修','2011-02-10','2011-02-11',1); insert Salvaging values (20100004,'小径墎低压线被盗抢修','2011-02-15','2011-02-15',1); insert Salvaging values (20100005,'明珠立交电缆沟盖板破损抢修','2011-03-02','2011-03-05',0); insert Salvaging values (20100010,'朝阳围公变低压线被盗抢修','2011-03-08','2011-03-10',0); create table Stock( mat_no varchar(4) primary key, mat_name varchar(50), speci varchar(50), warehouse varchar(50), amount int, unit decimal(8,2), total decimal(8,2) ) insert Stock values('m001','护套绝缘电线','BVV-120','供电局1#仓库',220,89.80,19756.00); insert Stock values('m002','架空绝缘电线','10KV-150','供电局1#仓库',30,17.00,510.00); insert Stock values('m003','护套绝缘电线','BVV-35','供电局2#仓库',80,22.80,1824.00); insert Stock values('m004','护套绝缘电线','BVV-50','供电局2#仓库',283,32.00,9056.00); insert Stock values('m005','护套绝缘电线','BVV-70','供电局2#仓库',130,40.00,5200.00); insert Stock values('m006','护套绝缘电线','BVV-150','供电局3#仓库',46,null,null); insert Stock values('m007','架空绝缘电线','10KV-120','供电局3#仓库',85,14.08,1196.80); insert Stock values('m009','护套绝缘电线','BVV-16','供电局3#仓库',90,null,null); insert Stock values('m011','护套绝缘电线','BVV-95','供电局3#仓库',164,null,null); insert Stock values('m012','交联聚乙烯绝缘电缆','YJV22-15KV','供电局4#仓库',45,719.80,32391.00); insert Stock values('m013','户外真空短路器','ZW12-12','供电局4#仓库',1,13600.00,13600.00); create table Out_Stock( prj_no int, mat_no varchar(4), amount int, get_date datetime, department varchar(20), primary key (prj_no,mat_no), foreign key (prj_no) references Salvaging(prj_no), foreign key (mat_no) references Stock(mat_no), ) insert Out_Stock values(20100015,'m001',2,'2010-10-12','工程1部'); insert Out_Stock values(20100015,'m002',1,'2010-10-12','工程1部'); insert Out_Stock values(20100016,'m001',3,'2010-11-05','工程1部'); insert Out_Stock values(20100016,'m003',10,'2010-11-05','工程1部'); insert Out_Stock values(20100001,'m001',2,'2011-01-03','工程2部'); insert Out_Stock values(20100002,'m001',1,'2011-01-03','工程2部'); insert Out_Stock values(20100002,'m013',1,'2011-01-03','工程2部'); insert Out_Stock values(20100003,'m001',5,'2011-02-11','工程3部'); insert Out_Stock values(20100003,'m012',1,'2011-02-11','工程3部'); insert Out_Stock values(20100004,'m001',3,'2011-02-15','工程3部'); insert Out_Stock values(20100004,'m004',20,'2011-02-15','工程3部'); insert Out_Stock values(20100005,'m001',2,'2011-03-02','工程2部'); insert Out_Stock values(20100005,'m003',10,'2011-03-02','工程2部'); insert Out_Stock values(20100005,'m006',3,'2011-03-02','工程2部'); insert Out_Stock values(20100010,'m001',5,'2011-03-09','工程1部');
查询实例：
--查询三个表。 select * from Salvaging; select * from Stock; select * from Out_Stock; --查询所有配电物资的物资编号、物资名称、规格。 select mat_no,mat_name,speci from Stock; --查询所有配电物资的物资名称、物资编号、规格和所在仓库名称。 select mat_name,mat_no,speci,warehouse from Stock; --查询所有配电物资的记录。 select * from Stock; --查询所有抢修工程的抢修天数。 --getdate() 获取当前系统时间 year(date)获取年份 month(date)获取月份 day(date)获取天 datediff(datepart,start_date,end_date)获取相差时间 select prj_no,prj_name,datediff(day,start_date,end_date) from Salvaging; --查询所有抢修工程的抢修天数，并在实际抢修天数列前加入一个列，此列的每行数据均为‘抢修天数’常量值。 select prj_no,prj_name,'抢修天数',datediff(day,start_date,end_date) from Salvaging; --用户可以通过指定别名来改变查询结果的列标题，语法格式为：列名|表达式 [AS] 列标题 或：列标题=列名|表达式 select prj_no,prj_name,datediff(day,start_date,end_date) '抢修天数' from Salvaging; --在配电物资库存记录表中查询出所有的仓库名称，并去掉结果表中的重复行。 select distinct warehouse from Stock; --查询供电局1#仓库存放的所有物资编号、物资名称、规格以及数量。 select mat_no,mat_name,speci,amount from Stock where warehouse='供电局1#仓库'; --查询所有单价小于80的物资名称、数量及其单价。 select mat_name,amount,unit from Stock where unit<80; select mat_name,amount,unit from Stock where not unit>=80; --查询单价在50~100之间的物资名称、数量及其单价。 select mat_name,amount,unit from Stock where unit>=50 and unit<=100; select mat_name,amount,unit from Stock where unit between 50 and 100; --查询存放在供电局1#仓库和供电局2#仓库的物资名称、规格及其数量。 select mat_name,speci,amount from Stock where warehouse in('供电局1#仓库','供电局2#仓库'); select mat_name,speci,amount from Stock where warehouse='供电局1#仓库' or warehouse='供电局2#仓库'; --查询既没有存放在供电局1#仓库，也没有存放在供电局2#仓库的物资名称、规格及其数量。 SELECT mat_name, speci, amount FROM stock WHERE warehouse NOT IN ('供电局1#仓库','供电局2#仓库'); --找出满足给定匹配条件的字符串，其格式为：[NOT] LIKE ‘<匹配串>’[ESCAPE ‘<换码字符>’] --查询存放在供电局1#仓库的物资的详细情况。 SELECT * FROM stock WHERE warehouse LIKE '供电局1#仓库' --查询所有绝缘电线的物资编号、名称和规格。 select * from stock where mat_name like '%绝缘电线'; --查询物资名称中第三、四个字为“绝缘”的物资编号、名称和规格。 select * from stock where mat_name like '__绝缘%'; --查询所有不带绝缘两个字的物资编号、名称和规格。 SELECT mat_num , mat_name, speci FROM stock WHERE mat_name NOT LIKE '%绝缘%' --查询物资名称为‘断路器_户外真空’物资信息。 select * from stock where mat_name like '断路器\_户外真空' escape '\'; --查询无库存单价的物资编号及其名称。 select mat_no,mat_name from stock where unit is null; --查询规格为BVV-120的护套绝缘电线的物资编号、库存数量及库存地点。 select mat_no,amount,warehouse from Stock where speci='BVV-120' and mat_name='护套绝缘电线'; --查询“护套绝缘电线”的物资编号及其单价，查询结果按单价降序排列。 select mat_no,unit from Stock order by unit desc --查询所有物资的信息，查询结果按所在仓库名降序排列，同一仓库的物资按库存量升序排列。 select * from Stock order by warehouse desc,amount; --TOP n子句：在查询结果中输出前面的n条记录； --TOP n PERCENT子句：在查询结果中输出前面占结果记录总数的n%条记录。 --显示stock表中，库存量最大的两条记录。 select top 2 * from stock order by amount desc; /* --显示stock表中占总数20%的记录。（注意，top子句不能和Stock关键字一起使用） select 20 percent * from stock; Some Problems */ --统计领取了物资的抢修工程项目数。 select count(distinct prj_no) '抢修项目数' from Out_stock; --查询使用m001号物资的抢修工程的最高领取数量、最低领取数量以及平均领取数量。 select max(amount) '最高领取数量',min(amount) '最低领取数量',avg(amount) '平均领取数量' from out_stock where mat_no='m001'; --GROUP BY子句可以将查询结果表按一列或多列取值相等的原则进行分组。 --分组进行聚合函数的聚合 --查询每个抢修工程项目号及使用的物资种类。 select prj_no,count(*) '物资种类' from out_stock group by prj_no; --HAVING可以针对集函数的结果值进行筛选，它是作用于分组计算的结果集；跟在Group By子句的后面，有GROUP BY才有HAVING --查询使用了2种以上物资的抢修工程项目号。 select prj_no,count(*) '物资种类' from out_stock group by prj_no having count(*)>2; --统计存放于供电局2#仓库的所有物资的总价值。 SELECT mat_name,speci,amount,unit,total FROM stock WHERE warehouse='供电局2#仓库' COMPUTE SUM(total) --统计存放于各个仓库的物资总价值，并查询物资名称、规格、单价、数量等，按仓库分组显示查询结果。 select * from stock order by warehouse compute sum(total) by warehouse
-- --连接查询 -- use electric go --查询项目号为“20100015”抢修项目所使用的物资编号、物资名称、规格和使用数量 select Stock.mat_no,mat_name,speci,Out_Stock.amount from Stock,Out_Stock where Stock.mat_no=Out_Stock.mat_no and prj_no='20100015'; --查询同时使用了物资编号为m001和m002的抢修工程的工程号与工程名称 select A.prj_no from Out_Stock A,Out_Stock B where A.prj_no=B.prj_no and A.mat_no='m001' and B.mat_no='m002'; -- --嵌套查询 -- --查询与规格为“BVV-120”的“护套绝缘电线”在同一个仓库存放的物资名称、规格和数量 select mat_name,speci,amount from Stock where warehouse in (select warehouse from Stock where speci='BVV-120' and mat_name='护套绝缘电线'); --查询工程项目为“观澜站光缆抢修”抢修所使用的物资编号和名称 select mat_no,mat_name from Stock where mat_no in (select mat_no from Salvaging where prj_name='观澜站光缆抢修'); --查询出库存量 超过该仓库物资 平均库存量的 物资编号、名称、规格及数量 select mat_no,mat_name,speci,amount from Stock where amount > (select avg(amount) from Stock); -- --带有ANY或ALL谓词的子查询 -- --查询其他仓库中比供电局1#仓库的某一物资库存量少的物资名称、规格和数量 select mat_name,speci,amount from Stock where warehouse != '供电局1#仓库' and amount < any (select amount from stock where warehouse = '供电局1#仓库'); --查询其他仓库中比供电局1#仓库的所有物资库存量少的物资名称、规格和数量 select mat_name,speci,amount from Stock where warehouse != '供电局1#仓库' and amount < all (select amount from stock where warehouse = '供电局1#仓库'); -- --带有EXISTS谓词的子查询 -- --查询所有使用了m001号物资的工程项目名称 select prj_name from salvaging S where exists (select * from out_stock where prj_no=S.prj_no and mat_no='m001'); --查询没有使用了m001号物资的工程项目名称 select prj_name from salvaging S where not exists (select * from out_stock where prj_no=S.prj_no and mat_no='m001'); -- --Union Intersect Except 集合查询 -- --查询存放在供电局1#仓库的物资及单价不大于50的物资 select * from stock where warehouse='供电局1#仓库' union select * from stock where unit<=50; --查询使用了物资编号为m001或m002的抢修工程的工程号 select prj_no from out_stock where mat_no='m001' union select prj_no from out_stock where mat_no='m002'; --查询存放在供电局1#仓库且单价不大于50的物资 select * from stock where warehouse='供电局1#仓库' intersect select * from stock where unit<=50; --查询同时使用了物资编号为m001和m002的抢修工程的工程号 select prj_no from out_stock where mat_no='m001' intersect select prj_no from out_stock where mat_no='m002'; --查询存放在供电局1#仓库的物资与单价不大于50的物资的差集 select * from stock where warehouse='供电局1#仓库' except select * from stock where unit<=50;
