好久不手写SQL,好多东西都感觉陌生了不少,没有编辑工具,IN和EXISTS的一些用法都写不清楚了。拿出来罗列罗列一些常用的写法。
View Code
1 --使用EXISTS查找PRODUCE类别的产品
2 SELECT * FROM DBO.PRODUCTS P
3 WHERE EXISTS (SELECT 1 FROM DBO.CATEGORIES C WHERE C.CATEGORYID=P.CATEGORYID
4 AND C.CATEGORYNAME ='PRODUCE')
5 --使用IN查找PRODUCE类别的产品
6 SELECT * FROM DBO.PRODUCTS P
7 WHERE P.CATEGORYID IN (SELECT C.CATEGORYID FROM DBO.CATEGORIES C
8 WHERE C.CATEGORYNAME ='PRODUCE')
9 --使用EXISTS查找类别不是CON开头的产品
10 SELECT * FROM DBO.PRODUCTS P
11 WHERE NOT EXISTS (SELECT 1 FROM DBO.CATEGORIES C WHERE C.CATEGORYID=P.CATEGORYID
12 AND C.CATEGORYNAME LIKE 'CON%')
13 ORDER BY CATEGORYID
14 --使用IN查找类别不是CON开头的产品
15 SELECT * FROM DBO.PRODUCTS P
16 WHERE P.CATEGORYID NOT IN (SELECT C.CATEGORYID FROM DBO.CATEGORIES C
17 WHERE C.CATEGORYNAME LIKE 'CON%')
18 --使用EXISTS判断范围,执行插入
19 INSERT INTO DBO.CATEGORIES
20 (CATEGORYNAME,DESCRIPTION,PICTURE)
21 SELECT CATEGORYNAME, DESCRIPTION,PICTURE
22 FROM DBO.CATEGORIES C
23 WHERE EXISTS (SELECT 1 FROM DBO.CATEGORIES CC WHERE C.CATEGORYID = CC.CATEGORYID AND CC.CATEGORYNAME LIKE '%FOOD%');
24 --使用EXISTS判断范围,执行更新
25 UPDATE DBO.CATEGORIES
26 SET CATEGORYNAME ='NEWFOOD'
27 WHERE EXISTS
28 ( SELECT 1
29 FROM DBO.CATEGORIES CC
30 WHERE CC.CATEGORYID = CATEGORIES.CATEGORYID AND CC.CATEGORYID>8);
31 --使用EXISTS判断判为,执行删除
32 DELETE DBO.CATEGORIES
33 WHERE EXISTS
34 (SELECT 1 FROM DBO.CATEGORIES CC
35 WHERE CC.CATEGORYID = CATEGORIES.CATEGORYID AND CC.CATEGORYID>8
36 )
都是一些简单的使用,关于使用效率方面,网上有很多人在讨论,咱还没到能发表评论的程度,仅此学习!