{"id":7393,"date":"2022-09-30T15:12:24","date_gmt":"2022-09-30T07:12:24","guid":{"rendered":"https:\/\/aif.amtbbs.org\/?p=7393"},"modified":"2022-09-30T15:12:24","modified_gmt":"2022-09-30T07:12:24","slug":"sql%e9%ab%98%e6%95%88%e6%9f%a5%e8%af%a2%e5%bb%ba%e8%ae%ae%ef%bc%8c%e4%bd%a0%e5%ad%a6%e4%bc%9a%e4%ba%86%e5%90%97%ef%bc%9f","status":"publish","type":"post","link":"https:\/\/aif.amtbbs.org\/index.php\/2022\/09\/30\/7393\/","title":{"rendered":"SQL\u9ad8\u6548\u67e5\u8be2\u5efa\u8bae\uff0c\u4f60\u5b66\u4f1a\u4e86\u5417\uff1f"},"content":{"rendered":"

\"\"<\/p>\n

\u4e3a\u4ec0\u4e48\u522b\u4eba\u7684\u67e5\u8be2\u53ea\u8981\u51e0\u79d2\uff0c\u800c\u4f60\u7684\u67e5\u8be2\u8bed\u53e5\u5c11\u5219\u5341\u591a\u79d2\uff0c\u591a\u5219\u5341\u51e0\u5206\u949f\u751a\u81f3\u51e0\u4e2a\u5c0f\u65f6\uff1f\u4e0e\u4f60\u7684\u67e5\u8be2\u8bed\u53e5\u662f\u5426\u9ad8\u6548\u6709\u5f88\u5927\u5173\u7cfb\u3002<\/p>\n

\u4eca\u5929\u6211\u4eec\u6765\u770b\u770b\u5982\u4f55\u5199\u51fa\u6bd4\u8f83\u9ad8\u6548\u7684\u67e5\u8be2\u8bed\u53e5\u3002<\/p>\n

1.\u5c3d\u91cf\u4e0d\u8981\u4f7f\u7528NULL\u5f53\u9ed8\u8ba4\u503c<\/h4>\n

\u5728\u6709\u7d22\u5f15\u7684\u5217\u4e0a\u5982\u679c\u5b58\u5728NULL\u503c\u4f1a\u4f7f\u5f97\u7d22\u5f15\u5931\u6548\uff0c\u964d\u4f4e\u67e5\u8be2\u901f\u5ea6\uff0c\u8be5\u5982\u4f55\u4f18\u5316\u5462\uff1f\u4f8b\u5982\uff1a<\/p>\n

SELECT * \u00a0FROM [Sales].[Temp_SalesOrder] WHERE UnitPrice IS NULL<\/p>\n

\u6211\u4eec\u53ef\u4ee5\u5c06NULL\u7684\u503c\u8bbe\u7f6e\u62100\u6216\u5176\u4ed6\u56fa\u5b9a\u6570\u503c\uff0c\u8fd9\u6837\u4fdd\u8bc1\u7d22\u5f15\u80fd\u591f\u7ee7\u7eed\u6709\u6548\u3002<\/p>\n

SELECT * \u00a0FROM [Sales].[Temp_SalesOrder] WHERE UnitPrice =0<\/p>\n

\u8fd9\u662f\u6539\u5199\u540e\u7684\u67e5\u8be2\u8bed\u53e5\uff0c\u6548\u7387\u4f1a\u6bd4\u4e0a\u9762\u7684\u5feb\u5f88\u591a\u3002<\/p>\n

2.\u5c3d\u91cf\u4e0d\u8981\u5728WHERE\u6761\u4ef6\u8bed\u53e5\u4e2d\u4f7f\u7528!=\u6216<><\/h4>\n

\u5728WHERE\u8bed\u53e5\u4e2d\u4f7f\u7528!=\u6216<>\u4e5f\u4f1a\u4f7f\u5f97\u7d22\u5f15\u5931\u6548\uff0c\u8fdb\u800c\u8fdb\u884c\u5168\u8868\u626b\u63cf\uff0c\u8fd9\u6837\u5c31\u4f1a\u82b1\u8d39\u8f83\u957f\u65f6\u95f4\u4e86\u3002<\/p>\n

3.\u5e94\u5c3d\u91cf\u907f\u514d\u5728 WHERE\u5b50\u53e5\u4e2d\u4f7f\u7528 OR<\/h4>\n

\u9047\u5230\u6709OR\u7684\u60c5\u51b5\uff0c\u6211\u4eec\u53ef\u4ee5\u5c06OR\u4f7f\u7528UNION ALL\u6765\u8fdb\u884c\u6539\u5199<\/p>\n

\u4f8b\u5982\uff1a<\/p>\n

SELECT * FROM T1 WHERE NUM=10 OR NUM=20<\/p>\n

\u53ef\u4ee5\u6539\u5199\u6210<\/p>\n

SELECT * FROM T1 WHERE NUM=10<\/p>\n

UNION ALL<\/p>\n

SELECT * FROM T1 WHERE NUM=20<\/p>\n

4.IN\u548cNOT IN\u4e5f\u8981\u614e\u7528<\/h4>\n

\u9047\u5230\u8fde\u7eed\u786e\u5207\u503c\u7684\u65f6\u5019 \uff0c\u6211\u4eec\u53ef\u4ee5\u4f7f\u7528BETWEEN AND\u6765\u8fdb\u884c\u4f18\u5316<\/p>\n

\u4f8b\u5982\uff1a<\/p>\n

SELECT * FROM T1 WHERE NUM IN (5,6,7,8)<\/p>\n

\u53ef\u4ee5\u6539\u5199\u6210\uff1a<\/p>\n

SELECT * FROM T1 WHERE NUM BETWEEN 5 AND 8.<\/p>\n

5.\u5b50\u67e5\u8be2\u4e2d\u7684IN\u53ef\u4ee5\u4f7f\u7528EXISTS\u6765\u4ee3\u66ff<\/h4>\n

\u5b50\u67e5\u8be2\u4e2d\u7ecf\u5e38\u4f1a\u4f7f\u7528\u5230IN\uff0c\u5982\u679c\u6362\u6210EXISTS\u505a\u5173\u8054\u67e5\u8be2\u4f1a\u66f4\u5feb<\/p>\n

\u4f8b\u5982\uff1a<\/p>\n

SELECT * FROM T1 WHERE ORDER_ID IN (SELECT ORDER_ID FROM ORDER WHERE PRICE>20);<\/p>\n

\u53ef\u4ee5\u6539\u5199\u6210\uff1a<\/p>\n

SELECT * FROM T1 AS A WHERE EXISTS (SELECT 1 FROM ORDER \u00a0AS B WHERE A.ORDER_ID=B.ORDER_ID AND B.PRICE>20)<\/p>\n

\u867d\u7136\u4ee3\u7801\u91cf\u53ef\u80fd\u6bd4\u4e0a\u9762\u7684\u591a\u4e00\u70b9\uff0c\u4f46\u662f\u5728\u4f7f\u7528\u6548\u679c\u4e0a\u4f1a\u4f18\u4e8e\u4e0a\u9762\u7684\u67e5\u8be2\u8bed\u53e5\u3002<\/p>\n

6.\u6a21\u7cca\u5339\u914d\u5c3d\u91cf\u4f7f\u7528\u524d\u7f00\u5339\u914d<\/h4>\n

\u5728\u8fdb\u884c\u6a21\u7cca\u67e5\u8be2\uff0c\u4f7f\u7528LIKE\u65f6\u5c3d\u91cf\u4f7f\u7528\u524d\u7f00\u5339\u914d\uff0c\u8fd9\u6837\u4f1a\u8d70\u7d22\u5f15\uff0c\u51cf\u5c11\u67e5\u8be2\u65f6\u95f4\u3002<\/p>\n

\u4f8b\u5982\uff1a<\/p>\n

SELECT * FROM T1 WHERE NAME LIKE ‘%\u674e\u56db%’<\/p>\n

\u6216\u8005<\/p>\n

SELECT * FROM T1 WHERE NAME LIKE ‘%\u674e\u56db’<\/p>\n

\u5747\u4e0d\u4f1a\u8d70\u7d22\u5f15\uff0c\u53ea\u6709\u5f53\u5982\u4e0b\u60c5\u51b5<\/p>\n

SELECT * FROM T1 WHERE NAME LIKE ‘\u674e\u56db%’\u624d\u4f1a\u8d70\u7d22\u5f15\u3002<\/p>\n

\u4e0a\u8ff0\u8fd9\u4e9b\u90fd\u662f\u5e73\u5e38\u7ecf\u5e38\u4f1a\u9047\u5230\u7684\uff0c\u5c31\u76f4\u63a5\u544a\u8bc9\u5927\u5bb6\u600e\u4e48\u64cd\u4f5c\u4e86\uff0c\u5177\u4f53\u53ef\u4ee5\u4e0b\u53bb\u505a\u8bd5\u9a8c\u5c1d\u8bd5\u4e00\u4e0b\u3002<\/p>\n

<\/div>\n

<\/g><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

\u4e3a\u4ec0\u4e48\u522b\u4eba\u7684\u67e5\u8be2\u53ea\u8981\u51e0\u79d2\uff0c\u800c\u4f60\u7684\u67e5\u8be2\u8bed\u53e5\u5c11\u5219\u5341\u591a\u79d2\uff0c\u591a\u5219\u5341\u51e0\u5206\u949f\u751a\u81f3\u51e0\u4e2a\u5c0f\u65f6\uff1f\u4e0e\u4f60\u7684\u67e5\u8be2\u8bed\u53e5\u662f\u5426\u9ad8\u6548\u6709\u5f88\u5927\u5173\u7cfb […]<\/p>\n

<\/div>\n

<\/g><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"author":3,"featured_media":7395,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[],"class_list":["post-7393","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-17"],"_links":{"self":[{"href":"https:\/\/aif.amtbbs.org\/index.php\/wp-json\/wp\/v2\/posts\/7393","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/aif.amtbbs.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/aif.amtbbs.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/aif.amtbbs.org\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/aif.amtbbs.org\/index.php\/wp-json\/wp\/v2\/comments?post=7393"}],"version-history":[{"count":1,"href":"https:\/\/aif.amtbbs.org\/index.php\/wp-json\/wp\/v2\/posts\/7393\/revisions"}],"predecessor-version":[{"id":7396,"href":"https:\/\/aif.amtbbs.org\/index.php\/wp-json\/wp\/v2\/posts\/7393\/revisions\/7396"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/aif.amtbbs.org\/index.php\/wp-json\/wp\/v2\/media\/7395"}],"wp:attachment":[{"href":"https:\/\/aif.amtbbs.org\/index.php\/wp-json\/wp\/v2\/media?parent=7393"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/aif.amtbbs.org\/index.php\/wp-json\/wp\/v2\/categories?post=7393"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/aif.amtbbs.org\/index.php\/wp-json\/wp\/v2\/tags?post=7393"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}