{"id":109501,"date":"2026-09-24T10:24:57","date_gmt":"2026-09-24T02:24:57","guid":{"rendered":"https:\/\/www.wsisp.com\/helps\/109501.html"},"modified":"2026-09-24T10:24:57","modified_gmt":"2026-09-24T02:24:57","slug":"sql-%e8%af%ad%e5%8f%a5%e6%80%bb%e7%bb%93%ef%bc%88sql-server-%e5%b8%b8%e7%94%a8%e8%af%ad%e6%b3%95%e9%80%9f%e6%9f%a5%ef%bc%89","status":"publish","type":"post","link":"https:\/\/www.wsisp.com\/helps\/109501.html","title":{"rendered":"SQL \u8bed\u53e5\u603b\u7ed3\uff08SQL Server \u5e38\u7528\u8bed\u6cd5\u901f\u67e5\uff09"},"content":{"rendered":"

💡 \u4e00\u7bc7 SQL Server \u5e38\u7528\u8bed\u53e5\u901f\u67e5\u7b14\u8bb0：\u5efa\u5e93\u5efa\u8868\u3001\u589e\u5220\u6539\u67e5\u3001\u8fde\u63a5\u67e5\u8be2\u3001\u5206\u9875\u3001\u5907\u4efd\u6062\u590d\u3001\u7cfb\u7edf\u51fd\u6570，\u7528\u5230\u54ea\u67e5\u54ea\u3002<\/p>\n

\"\"<\/p>\n

SQL \u5e38\u7528\u8bed\u53e5\u4e0e\u6570\u636e\u5e93\u7684\u5173\u7cfb\u793a\u610f\u56fe<\/p>\n

\u57fa\u7840<\/h3>\n

\u521b\u5efa\u6570\u636e\u5e93<\/h4>\n

CREATE<\/span> DATABASE<\/span> database<\/span>–<\/span>name<\/p>\n

\u5220\u9664\u6570\u636e\u5e93<\/h4>\n

drop<\/span> database<\/span> dbname<\/p>\n

\u5907\u4efd SQL Server\u2014\u2014\u521b\u5efa\u5907\u4efd\u6570\u636e\u7684 device<\/h4>\n

USE<\/span> master
\nEXEC<\/span> sp_addumpdevice 'disk'<\/span>,<\/span> 'testBack'<\/span>,<\/span> 'c:\\\\mssql7backup\\\\MyNwind_1.dat'<\/span>
\n— \u5f00\u59cb \u5907\u4efd<\/span>
\nBACKUP<\/span> DATABASE<\/span> pubs TO<\/span> testBack<\/p>\n

\u521b\u5efa\u65b0\u8868<\/h4>\n

create<\/span> table<\/span> tabname(<\/span>col1 type1 [<\/span>not<\/span> null<\/span>]<\/span> [<\/span>primary<\/span> key<\/span>]<\/span>,<\/span>col2 type2 [<\/span>not<\/span> null<\/span>]<\/span>,<\/span>.<\/span>.<\/span>)<\/span>
\n\u6839\u636e\u5df2\u6709\u7684\u8868\u521b\u5efa\u65b0\u8868
\ncreate<\/span> table<\/span> tab_new like<\/span> tab_old
\n\u4f7f\u7528\u65e7\u8868\u521b\u5efa\u65b0\u8868
\ncreate<\/span> table<\/span> tab_new as<\/span> select<\/span> col1,<\/span>col2\u2026 from<\/span> tab_old definition only<\/p>\n

\u5220\u9664\u8868<\/h4>\n

drop<\/span> table<\/span> tabname<\/p>\n

\u589e\u52a0\u4e00\u4e2a\u5217<\/h4>\n

Alter<\/span> table<\/span> tabname add<\/span> column<\/span> col type<\/span><\/p>\n

\u26a0\ufe0f \u5751：\u5217\u589e\u52a0\u540e\u5c06\u4e0d\u80fd\u5220\u9664\u3002DB2 \u4e2d\u5217\u52a0\u4e0a\u540e\u6570\u636e\u7c7b\u578b\u4e5f\u4e0d\u80fd\u6539\u53d8，\u552f\u4e00\u80fd\u6539\u53d8\u7684\u662f\u589e\u52a0 varchar \u7c7b\u578b\u7684\u957f\u5ea6\u3002<\/p>\n

\u6dfb\u52a0\u4e3b\u952e<\/h4>\n

Alter<\/span> table<\/span> tabname add<\/span> primary<\/span> key<\/span>(<\/span>col)<\/span><\/p>\n

\u5220\u9664\u4e3b\u952e<\/h4>\n

Alter<\/span> table<\/span> tabname drop<\/span> primary<\/span> key<\/span>(<\/span>col)<\/span><\/p>\n

\u521b\u5efa\u7d22\u5f15<\/h4>\n

create<\/span> [<\/span>unique<\/span>]<\/span> index<\/span> idxname on<\/span> tabname(<\/span>col\u2026.<\/span>)<\/span><\/p>\n

\u5220\u9664\u7d22\u5f15<\/h4>\n

drop<\/span> index<\/span> idxname<\/p>\n

\u26a0\ufe0f \u5751：\u7d22\u5f15\u662f\u4e0d\u53ef\u66f4\u6539\u7684，\u60f3\u66f4\u6539\u5fc5\u987b\u5220\u9664\u91cd\u65b0\u5efa\u3002<\/p>\n

\u521b\u5efa\u89c6\u56fe<\/h4>\n

create<\/span> view<\/span> viewname as<\/span> select<\/span> statement<\/p>\n

\u5220\u9664\u89c6\u56fe<\/h4>\n

drop<\/span> view<\/span> viewname<\/p>\n

\u51e0\u4e2a\u7b80\u5355\u7684\u57fa\u672c SQL \u8bed\u53e5<\/h4>\n

\u9009\u62e9：select<\/span> *<\/span> from<\/span> table1 where<\/span> \u6761\u4ef6
\n\u63d2\u5165：insert<\/span> into<\/span> table1(<\/span>field1,<\/span>field2)<\/span> values<\/span>(<\/span>value1,<\/span>value2)<\/span>
\n\u5220\u9664：delete<\/span> from<\/span> table1 where<\/span> \u8303\u56f4
\n\u66f4\u65b0：update<\/span> table1 set<\/span> field1=<\/span>value1 where<\/span> \u8303\u56f4
\n\u67e5\u627e：select<\/span> *<\/span> from<\/span> table1 where<\/span> field1 like<\/span> '%value1%'<\/span> —like\u7684\u8bed\u6cd5\u5f88\u7cbe\u5999，\u67e5\u8d44\u6599!<\/span>
\n\u6392\u5e8f：select<\/span> *<\/span> from<\/span> table1 order<\/span> by<\/span> field1,<\/span>field2 [<\/span>desc<\/span>]<\/span>
\n\u603b\u6570：select<\/span> count as<\/span> totalcount from<\/span> table1
\n\u6c42\u548c：select<\/span> sum<\/span>(<\/span>field1)<\/span> as<\/span> sumvalue from<\/span> table1
\n\u5e73\u5747：select<\/span> avg<\/span>(<\/span>field1)<\/span> as<\/span> avgvalue from<\/span> table1
\n\u6700\u5927：select<\/span> max<\/span>(<\/span>field1)<\/span> as<\/span> maxvalue from<\/span> table1
\n\u6700\u5c0f：select<\/span> min<\/span>(<\/span>field1)<\/span> as<\/span> minvalue from<\/span> table1<\/p>\n

\u51e0\u4e2a\u9ad8\u7ea7\u67e5\u8be2\u8fd0\u7b97\u8bcd<\/h4>\n

A：UNION \u8fd0\u7b97\u7b26<\/h5>\n

UNION \u8fd0\u7b97\u7b26\u901a\u8fc7\u7ec4\u5408\u5176\u4ed6\u4e24\u4e2a\u7ed3\u679c\u8868（\u4f8b\u5982 TABLE1 \u548c TABLE2）\u5e76\u6d88\u53bb\u8868\u4e2d\u4efb\u4f55\u91cd\u590d\u884c\u800c\u6d3e\u751f\u51fa\u4e00\u4e2a\u7ed3\u679c\u8868\u3002\u5f53 ALL \u968f UNION \u4e00\u8d77\u4f7f\u7528\u65f6（\u5373 UNION ALL），\u4e0d\u6d88\u9664\u91cd\u590d\u884c\u3002\u4e24\u79cd\u60c5\u51b5\u4e0b，\u6d3e\u751f\u8868\u7684\u6bcf\u4e00\u884c\u4e0d\u662f\u6765\u81ea TABLE1 \u5c31\u662f\u6765\u81ea TABLE2\u3002<\/p>\n

B：EXCEPT \u8fd0\u7b97\u7b26<\/h5>\n

EXCEPT \u8fd0\u7b97\u7b26\u901a\u8fc7\u5305\u62ec\u6240\u6709\u5728 TABLE1 \u4e2d\u4f46\u4e0d\u5728 TABLE2 \u4e2d\u7684\u884c\u5e76\u6d88\u9664\u6240\u6709\u91cd\u590d\u884c\u800c\u6d3e\u751f\u51fa\u4e00\u4e2a\u7ed3\u679c\u8868\u3002\u5f53 ALL \u968f EXCEPT \u4e00\u8d77\u4f7f\u7528\u65f6（EXCEPT ALL），\u4e0d\u6d88\u9664\u91cd\u590d\u884c\u3002<\/p>\n

C：INTERSECT \u8fd0\u7b97\u7b26<\/h5>\n

INTERSECT \u8fd0\u7b97\u7b26\u901a\u8fc7\u53ea\u5305\u62ec TABLE1 \u548c TABLE2 \u4e2d\u90fd\u6709\u7684\u884c\u5e76\u6d88\u9664\u6240\u6709\u91cd\u590d\u884c\u800c\u6d3e\u751f\u51fa\u4e00\u4e2a\u7ed3\u679c\u8868\u3002\u5f53 ALL \u968f INTERSECT \u4e00\u8d77\u4f7f\u7528\u65f6（INTERSECT ALL），\u4e0d\u6d88\u9664\u91cd\u590d\u884c\u3002<\/p>\n

💡 \u8981\u70b9：\u4f7f\u7528\u8fd0\u7b97\u8bcd\u7684\u51e0\u4e2a\u67e5\u8be2\u7ed3\u679c\u884c\u5fc5\u987b\u662f\u4e00\u81f4\u7684\u3002<\/p>\n

\u4f7f\u7528\u5916\u8fde\u63a5<\/h4>\n

A：left（outer）join<\/h5>\n

\u5de6\u5916\u8fde\u63a5（\u5de6\u8fde\u63a5）：\u7ed3\u679c\u96c6\u65e2\u5305\u62ec\u8fde\u63a5\u8868\u7684\u5339\u914d\u884c，\u4e5f\u5305\u62ec\u5de6\u8fde\u63a5\u8868\u7684\u6240\u6709\u884c\u3002<\/p>\n

select<\/span> a.<\/span>a,<\/span> a.<\/span>b,<\/span> a.<\/span>c,<\/span> b.<\/span>c,<\/span> b.<\/span>d,<\/span> b.<\/span>f from<\/span> a LEFT<\/span> OUT<\/span> JOIN<\/span> b ON<\/span> a.<\/span>a =<\/span> b.<\/span>c<\/p>\n

B：right（outer）join<\/h5>\n

\u53f3\u5916\u8fde\u63a5（\u53f3\u8fde\u63a5）：\u7ed3\u679c\u96c6\u65e2\u5305\u62ec\u8fde\u63a5\u8868\u7684\u5339\u914d\u8fde\u63a5\u884c，\u4e5f\u5305\u62ec\u53f3\u8fde\u63a5\u8868\u7684\u6240\u6709\u884c\u3002<\/p>\n

C：full\/cross（outer）join<\/h5>\n

\u5168\u5916\u8fde\u63a5：\u4e0d\u4ec5\u5305\u62ec\u7b26\u5408\u8fde\u63a5\u8868\u7684\u5339\u914d\u884c，\u8fd8\u5305\u62ec\u4e24\u4e2a\u8fde\u63a5\u8868\u4e2d\u7684\u6240\u6709\u8bb0\u5f55\u3002<\/p>\n

\u5206\u7ec4 Group by<\/h4>\n

\u4e00\u5f20\u8868\u4e00\u65e6\u5206\u7ec4\u5b8c\u6210\u540e，\u67e5\u8be2\u540e\u53ea\u80fd\u5f97\u5230\u7ec4\u76f8\u5173\u7684\u4fe1\u606f（\u7edf\u8ba1\u4fe1\u606f：count\u3001sum\u3001max\u3001min\u3001avg）\u3002\u5728 SQL Server \u4e2d\u5206\u7ec4\u65f6：<\/p>\n

\u26a0\ufe0f \u5751：\u4e0d\u80fd\u4ee5 text\u3001ntext\u3001image \u7c7b\u578b\u7684\u5b57\u6bb5\u4f5c\u4e3a\u5206\u7ec4\u4f9d\u636e；select \u7edf\u8ba1\u51fd\u6570\u4e2d\u7684\u5b57\u6bb5，\u4e0d\u80fd\u548c\u666e\u901a\u7684\u5b57\u6bb5\u653e\u5728\u4e00\u8d77\u3002<\/p>\n

\u5bf9\u6570\u636e\u5e93\u8fdb\u884c\u64cd\u4f5c<\/h4>\n