一、sql中要学习的有：存储过程、游标、索引、触发器、事务、分页、数据导入导出、树形数据处理、海量数据查询、视图以及简单的sql语句的编写并思考它的执行效率
二、学习方法：
是什么？
有什么作用？
什么时候用？
怎么用？
效率怎样？
三、今天要总结的是一些基本的sql 语句的编写思路和一些子句的执行效率的分析
1、建数据库以及表
create database CAP
create table CUSTOMERS
(
cid varchar(10) primary key,
canme varchar(10),
city varchar(20),
discnt money
)
insert into CUSTOMERS values ('c001','TipTop','Duluth',10.00)
insert into CUSTOMERS values ('c002','Basics','Dallas',12.00)
insert into CUSTOMERS values ('c003','Allied','Dallas',8.00)
insert into CUSTOMERS values ('c004','ACME','Duluth',8.00)
insert into CUSTOMERS values ('c006','ACME','Kyoto',0.00)
create table PRODUCTS
(
pid varchar(10)primary key,
pname varchar(10),
city varchar(20),
quantity varchar(10),
price money
)
insert into PRODUCTS values ('p01','comb','Dallas','111400',0.50)
insert into PRODUCTS values ('p02','brush','Newark','203000',0.50)
insert into PRODUCTS values ('p03','razor','Duluth','150600',1.00)
insert into PRODUCTS values ('p04','pen','Duluth','125300',1.00)
insert into PRODUCTS values ('p05','pencil','Dallas','221400',1.00)
insert into PRODUCTS values ('p06','folder','Dallas','123100',2.00)
insert into PRODUCTS values ('p07','case','Newark','100500',1.00)
create table AGENTS
(
aid varchar(4) primary key,
aname varchar(10),
city varchar(10),
[percent] int
)
insert into AGENTS values('a01','Smith','New York',6)
insert into AGENTS values('a02','Jones','Newark',6)
insert into AGENTS values('a03','Brown','Tokyo',7)
insert into AGENTS values('a04','Gray','New York',6)
insert into AGENTS values('a05','Otasi','Duluth',5)
insert into AGENTS values('a06','Smith','Dallas',5)
create table ORDERS
(
ordno varchar(4),
[month] varchar(3),
cid varchar(10),
aid varchar(4),
pid varchar(10),
qty int,
dollars money
)
insert into ORDERS values('1011','jan','c001','a01','p01',1000,450.00)
insert into ORDERS values('1012','jan','c001','a01','p01',1000,450.00)
insert into ORDERS values('1019','feb','c001','a02','p02',400,180.00)
insert into ORDERS values('1017','feb','c001','a06','p03',600,540.00)
insert into ORDERS values('1018','feb','c001','a03','p04',600,540.00)
insert into ORDERS values('1023','mar','c001','a04','p05',500,450.00)
insert into ORDERS values('1022','mar','c001','a05','p06',400,720.00)
insert into ORDERS values('1025','apr','c001','a05','p07',800,720.00)
insert into ORDERS values('1013','jan','c002','a03','p03',1000,880.00)
insert into ORDERS values('1026','may','c002','a05','p03',800,704.00)
insert into ORDERS values('1015','jan','c003','a03','p05',1200,1104.00)
insert into ORDERS values('1014','jan','c003','a03','p05',1200,1104.00)
insert into ORDERS values('1021','feb','c004','a06','p01',1000,460.00)
insert into ORDERS values('1016','jan','c006','a01','p01',1000,500.00)
insert into ORDERS values('1020','feb','c006','a03','p07',600,600.00)
insert into ORDERS values('1024','mar','c006','a06','p01',800,400.00)
2、sql语句的编写
--题1：找出住在纽约的代理商的aid值和名字
select aid ,aname from AGENTS where city='New York'
--题2：检索订货记录中所有零件的pid值
select distinct pid from ORDERS
--题3:检索所有满足以下条件的顾客-代理商姓名对(cname,aname),其中的cname 通过aname订了货(曾经出过错)
create table b
(cid varchar(10),
aid varchar(4)
)
insert into b
select cid,aid
from ORDERS group by cid,aid
select distinct aname,cname from AGENTS,CUSTOMERS,b where AGENTS.aid=b.aid and CUSTOMERS.cid=b.cid
--上面是通过建临时表得出结果,如果直接写呢?(注意一定还要有distinct)
select distinct cname,aname from agents,customers,orders where agents.aid=orders.aid and customers.cid=orders.cid
--答案
select distinct CUSTOMERS.cname,AGENTS.aname from CUSTOMERS,AGENTS,ORDERS where CUSTOMERS.cid=ORDERS.cid and AGENTS.aid=ORDERS.aid
--可以看出,答案非常简单
--题4:在orders表的基础上生成含有列ordno,cid,aid,pid和profit的"表",其中的profit是由quantity和price计算所得,方法是全部销售收入减去60%的销售收入.顾客的折扣以及代理商的酬金百分率
--答案：此题告诉我们关键字加[]的重要性，不然一直出现“关键字 'percent' 附近有语法错误。”
select ordno,x.cid,x.aid,x.pid,40*(x.qty*p.price)-01*(c.discnt+a.[percent])*(x.qty*p.price) as profit
from orders as x,customers as c,agents as a,products as p
where c.cid=x.cid and a.aid=x.aid and p.pid=x.pid;
--题5：求出住在同一城市的顾客对
select distinct c1.cname,c2.cname,c1.city from customers c1, customers c2 where c1.city=c2.city and c1.cname<c2.cname
select c1.cid,c2.cid from customers c1,customers c2
where c1.city=c2.city and c1.cid<c2.cid
--此题的关键点即为可以给一个表取两个别名.如果题目要求在一个表中的同一列取出匹配项的话都可以用这种方法
--题6:找出至少被两个顾客订购的产品的pid值
select pid,count(cid) as 订购产品的顾客数量 from orders group by pid having count(cid)>=2
--此时用此法更好一些
--答案：
select distinct x1.pid from orders x1, orders x2
where x1.pid=x2.pid and x1.cid<x2.cid
--可以总结为对一个表的操作大多可以通过去别名来完成，这里应该会有冗余
--题6:查询那些订购了某个被代理商a06订购过的产品的顾客的cid值
select distinct cid from orders where pid in (select pid from orders where aid='a06')
--这时用in感觉浑然天成，因为这里要去的是“某一个”被代理商a06订购过的产品
--同理,对一个表里面的这种操作都可以通过取别名(且这种方式更简单)
select distinct y.cid from orders x,orders y
where y.pid=x.pid and x.aid='a06'
--总结:可以用一次查询的就用一次查询,通过表取别名
--接下来的例子将要用到子查询
--题1:求出通过住在Duluth和Dallas的代理商订了货的顾客的cid值
select distinct cid from orders where aid in (select aid from agents where city='Duluth' or city='Dallas' )
--题2:检索有关住在Duluth或Dallas的代理商的所有信息
select * from agents where city='Duluth' or city='Dallas'
--题3：求出通过住在Duluth或Dallas的代理商订货的所有顾客的姓名和折扣
select cname,discnt from customers where cid in (select cid from orders where aid in (select aid from agents where city='Duluth' or city='Dallas') )
--或者
select cname,discnt from customers where cid in (select cid from orders where aid in (select aid from agents where city in ('Duluth' ,'Dallas')))
--题4：找出订购了产品p05的顾客的名字
select cname from customers where cid in (select cid from orders where pid='p05')
--答案用最直接的SQL语句来解决该查询问题
select distinct cname from customers,orders where customers.cid = orders.cid and orders.pid='p05';
--用连接也能达到相同的效果，重要的是拆解题目的意思
select distinct cname from customers inner join orders on customers.cid = orders.cid and orders.pid='p05';
--那么我们来看一下三种情况的执行效率
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE a
@pid varchar(10)
AS
BEGIN
--select cname from customers where cid in (select cid from orders where pid=@pid) 16ms
--select distinct cname from customers,orders where customers.cid = orders.cid and orders.pid=@pid; 3ms
--select distinct cname from customers inner join orders on customers.cid = orders.cid and orders.pid=@pid; 3ms
END
GO
DBCC FREEPROCCACHE --清除缓存，以免下次计算时间
declare @begin datetime
declare @End datetime
set @begin=getdate()
exec a 'p05'
set @End=getdate()
select datediff(ms,@begin,@End) as 执行时间(毫秒)
--由此可见，一般情况下这种题目能直接写的就直接用连接的方法，用in的效率极低
--题5：要得到从代理商a03处订购了产品p07的顾客的名字
select cname from customers inner join orders on customers.cid =orders.cid and aid='a03' and pid='p07'
select cname from customers where cid in (select cid from orders where aid='a03' and pid='p07')
--题6：检索由住在Duluth的顾客和住在New York 的代理商组成的所有订货记录的ordno值
select ordno from orders where cid in (select cid from customers where city='Duluth') and aid in (select aid from agents where city='New York') --6ms
--答案：
select ordno from orders x where exists (select cid,aid from customers c,agents a
where c.cid=x.cid and a.aid=x.aid and c.city='Duluth' and a.city='New York') --10ms
--疑惑：难道in比exists执行效率高，还是只是该题的问题
--题7：找出佣金百分率最小的代理商的aid值
select top(1) aid from agents order by [percent] --我能想到的就是排序然后取第一个，但是我这样做有问题，因为我求出来的只可能有 一个，而实际情况是可能有相同值的不止一个
--答案：
select aid from agents where [percent]<=all(select [percent] from agents)
----题8：找出住在Dallas或Boston的顾客拥有相同折扣的所有顾客
--select c1.cname ,c2.cname from customers c1,customers c2 where c1.discnt=c2.discnt and c1.cid<c2.cid --该方法得出的结果跟实际不符合
----我没想出来，该怎么做？
--题9：找出与住在Dallas或Boston的顾客拥有相同折扣的所有顾客
select cid,cname from customers where discnt in (select discnt from customers where city='Dallas' or city='Boston')
--答案：
select cid,cname from customers where discnt=some(select discnt from customers where city='Dallas' or city='Boston')
--执行效率：in 3ms,some 6ms,难道in 的执行效率比some高？
--题10：求出所有满足一下条件的顾客的cid值：该顾客的discnt值小于任一住在Duluth的顾客的discnt值
select cid from customers where discnt<any(select discnt from customers where city='Duluth') --这里是错误的，题目中的任一应该是对应所有的，所以应把any改为all
--这种题目应谨慎，留意
--题11：检索通过代理商a05订货的所有顾客的名字
select cname from customers where cid in (select cid from orders where aid='a05' )
--总结，凡是这种题目，都可以直接做取别名，或连接或in，但是in的效率最低
----题12：求出既订购了产品p01又订购了产品p07的顾客的cid值
--select cid from orders where pid='p01'
--select cid from orders where pid='p07'
----然后求上面两式的交集，我没做出来
--
--select distinct cid from orders where pid='p07' and exists (select cid from orders where pid='p01' )
----这样做虽 然答案正确，但是换位置之后就有错误了
--遇到这种问题的思路是什么样的？
--正确答案：
select distinct cid from orders x
where pid='p01' and exists (select * from orders where cid=x.cid and pid='p07')
--为什么这里一定要取别名
--取别名除了有方便的好处外，有什么情况是必须用到的吗？
select cid from orders where pid='p01' intersect select cid from orders where pid='p07'
--注：两个的交集，可以用intersect关键字
--3.4.12 检索没有通过代理商a05订货的所有顾客的名字
select cid,cname from customers where cid not in (select cid from orders where aid='a05')
--这个时候in 不能用exists 代替
----答案：
--select distinct c.cid ,c.cname from customers c
-- where not exists (select * from orders x where c.cid=x.cid and x.cid='a05')
----实际上答案是错的，但是中文解释好像又能够解释通，为什么呢？
--
--3.4.15检索订购了产品p01的顾客所在的city
select cname,city from customers where cid in (select cid from orders where pid='p01')
select distinct cname,city from customers inner join orders on customers.cid=orders.cid and orders.pid='p01'
--3.5.1 建立一个包含了顾客所在的或者代理商所在的或者两者皆在的城市的名单
select distinct city from agents union (select city from customers)
--3.5.2 求出通过住在New York的所有代理商订了货的顾客的cid值
select distinct cid from orders where aid in (select aid from agents where city='New York' )
--3.5.3 求出住在New York 或Duluth 并订购了价格超过一美元的所有产品的代理商的aid值
select aid from agents where aid in (select aid from orders where dollars/qty>1) and city='New York' or city='Duluth'
--3.5.4 找出订购了产品p01和价格超过1美元的所有产品的代理商的aid值
select aid from orders where dollars/qty>1 intersect select aid from orders where pid='p01' --并且或交集的意思在SQL里面如何表达？
--
select aid from orders where pid in (select pid from products where price>1 or pid='p01' )
--这显然也是错误的，不是要它满足某个条件就行，而是要同时包含这两者
--此题没想出来
--可见，求交集的时候intersect的重要性
--答案：
select y.aid from orders y where y.pid='p01' and not exists (select p.pid from products p where p.price>1.0000 and
not exists (select * from orders x where x.pid=p.pid and x.aid=y.aid))
--3.5.5 找出具有以下性质的顾客的cid 值：如果顾客c006订购了某种产品，那要检索的顾客也订购了该产品
select cname,cid from customers where cid in (select cid from orders where pid in (select pid from orders where cid='c006'))
--跟答案不符,那么该怎么写呢？问题还是应该为包含，而不是在其中满足某个条件
--答案：
select cid from customers c where not exists (select z.pid from orders z
where z.cid='c006' and not exists (select * from orders y where y.pid=z.pid and y.cid=c.cid)
)
--3.5.6 找出被所有住在Duluth的顾客订购的产品的pid值
select distinct pid from orders where cid in (select cid from customers where city='Duluth' )
--同理：肯定是错的，对待这种要包含的问题该如何写sql语句
--答案：
select pid from products p where not exists (select c.cid from customers c where c.city='Duluth'
and not exists (select * from orders x where x.pid=p.pid and x.cid=c.cid)
)
--高级SQL语法
--3.6.2 检索没有通过代理商a05订货的所有顾客的名字
select cname from customers except
(select cname from customers,orders where customers.cid=orders.cid and orders.aid='a05')
--这时except是关键
---3.6.3 检索对同一产品至少订购了两次的所有顾客的名字
select cname from customers where cid in
(select cid from orders group by cid,pid having count(pid)>=2)
--答案:
select distinct cname from (select o.cid as spcid from orders o,orders x where o.cid=x.cid
and o.pid=x.pid and o.ordno<> x.ordno)y, customers c where y.spcid=c.cid;
--3.6.4 检索至少订购了一件价格低于￥0.50 的商品的所有顾客的姓名
--答案:我没做出来,下面这种方法运行没通过
select distinct cname from (orders join products using(pid)) join customers using(cid) where price<0.50
--法2:将3个表直接连接起来就可以了
select distinct cname from (orders o join products p on o.pid=p.pid) join customers c on o.cid=c.cid where p.price<0.5
--3.7.1 求出所有订货交易的总金额
select sum(dollars) as totaldollars from orders;
--3.7.2 求出产品p03的订购总量
select pid,count(pid) as 订购总量 from orders where pid='p03' group by pid --错误的，没理解题意
--答案：
select sum(qty) as total from orders where pid='p03'
--3.7.3 求出顾客总数的查询
select count(*) as 顾客总数 from customers
--3.7.4 求出有顾客居住的城市的数目
select count(distinct city) as 有顾客居住的城市数目 from customers
--3.7.5 列出折扣值小于最大折扣值的所有顾客的cid值
select cid,cname,discnt from customers where discnt<
(select max(discnt) from customers)
--实际上那条空值的记录没有选进来
--3.7.6 找出至少被两个顾客订购的所有产品（可以推广到多于两个顾客的情况）
select pid from orders group by pid having count(cid)>=2
--我的思路是 select pid from orders
--select pid,count(cid) as 产品被几个顾客订购 from orders group by pid having count(cid)>=2
--答案如下：
select p.pid from products p where 2<=(select count(distinct cid) from orders where pid=p.pid)
--3.7.7
insert into customers (cid,cname,city)
values ('c009','Windix','Dallas');
select * from customers where discnt<=10 or discnt>10
--显然,没有查出所有记录
--使用特殊谓词is null
select * from customers where discnt is null or discnt<=10 or discnt>10
--3.8 SQL中行的分组
--3.8.1 创建一个计算每样产品被每个代理商订购的总量的查询
select aid,pid,sum(qty) as 每个代理商订购的总量 from orders group by aid,pid
3、执行效率的分析
--题4：找出订购了产品p05的顾客的名字
select cname from customers where cid in (select cid from orders where pid='p05')
--答案用最直接的SQL语句来解决该查询问题
select distinct cname from customers,orders where customers.cid = orders.cid and orders.pid='p05';
--用连接也能达到相同的效果，重要的是拆解题目的意思
select distinct cname from customers inner join orders on customers.cid = orders.cid and orders.pid='p05';
--那么我们来看一下三种情况的执行效率
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE a
@pid varchar(10)
AS
BEGIN
--select cname from customers where cid in (select cid from orders where pid=@pid) 16ms
--select distinct cname from customers,orders where customers.cid = orders.cid and orders.pid=@pid; 3ms
--select distinct cname from customers inner join orders on customers.cid = orders.cid and orders.pid=@pid; 3ms
END
GO
DBCC FREEPROCCACHE --清除缓存，以免下次计算时间
declare @begin datetime
declare @End datetime
set @begin=getdate()
exec a 'p05'
set @End=getdate()
select datediff(ms,@begin,@End) as 执行时间(毫秒)
--由此可见，一般情况下这种题目能直接写的就直接用连接的方法，用in的效率极低
1219
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
