{"id":61,"date":"2022-07-06T10:48:50","date_gmt":"2022-07-06T02:48:50","guid":{"rendered":"https:\/\/xinchewhd.com.cn\/?p=61"},"modified":"2023-05-25T14:27:31","modified_gmt":"2023-05-25T06:27:31","slug":"mysql%e8%bf%9b%e9%98%b6","status":"publish","type":"post","link":"https:\/\/xinchewhd.com.cn\/index.php\/mysql\/mysql%e8%bf%9b%e9%98%b6\/","title":{"rendered":"MySQL\u8fdb\u9636"},"content":{"rendered":"\n<p><strong>1\u3001\u590d\u5236\u8868(\u53ea\u590d\u5236\u7ed3\u6784\uff0c\u6e90\u8868\u540d(a) \u65b0\u8868\u540d(b)) <\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>\u65b9\u6cd5\u4e00\uff1aselect * into b from a where 1&lt;&gt;1<strong>  \u6ce8\uff1a\uff08\u4ec5\u7528\u4e8eSQlServer\uff09<\/strong><\/li><li>\u65b9\u6cd5\u4e8c\uff1aselect top 0 * into b from a <\/li><\/ul>\n\n\n\n<p><strong>2\u3001\u62f7\u8d1d\u8868(\u62f7\u8d1d\u6570\u636e\uff0c\u6e90\u8868\u540d(a) \u76ee\u6807\u8868\u540d(b)) <\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> insert into b(a, b, c) select d,e,f from b<\/li><\/ul>\n\n\n\n<p><strong>3\u3001\u8de8\u6570\u636e\u5e93\u4e4b\u95f4\u8868\u7684\u62f7\u8d1d(\u5177\u4f53\u6570\u636e\u4f7f\u7528\u7edd\u5bf9\u8def\u5f84) <\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> insert into b (a, b, c) select d,e,f from b in\u2018\u5177\u4f53\u6570\u636e\u5e93\u2019where&nbsp;\u6761\u4ef6 <\/li><li> \u4f8b\u5b50\uff1a..from b in '\"&amp;Server.MapPath(\".\")&amp;\"\\data.mdb\" &amp;\"' where.. <\/li><\/ul>\n\n\n\n<p><strong>4\u3001\u5b50\u67e5\u8be2(\u8868\u540d1(a) \u8868\u540d2(b))<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select a,b,c from a where a IN (select d from b )&nbsp;<\/li><li>\u6216<\/li><li>select a,b,c from a where a IN (1,2,3) <\/li><\/ul>\n\n\n\n<p><strong>5\u3001\u663e\u793a\u6587\u7ae0\u3001\u63d0\u4ea4\u4eba\u548c\u6700\u540e\u56de\u590d\u65f6\u95f4<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b <\/li><\/ul>\n\n\n\n<p><strong>6\u3001\u5916\u8fde\u63a5\u67e5\u8be2(\u8868\u540d1(a) \u8868\u540d2(b))<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c <\/li><\/ul>\n\n\n\n<p><strong>7\u3001\u5728\u7ebf\u89c6\u56fe\u67e5\u8be2(\u8868\u540d1(a) )<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select * from (SELECT a,b,c FROM a) T where t.a &gt; 1<\/li><\/ul>\n\n\n\n<p><strong>8\u3001between\u7684\u7528\u6cd5<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> <strong>between\u9650\u5236\u67e5\u8be2\u6570\u636e\u8303\u56f4\u65f6\u5305\u62ec\u4e86\u8fb9\u754c\u503c<\/strong><ul><li>   select * from table1 where time between time1 and time2 <\/li><\/ul><\/li><li> <strong>not between\u4e0d\u5305\u62ec<\/strong> <ul><li> select a,b,c, from table1 where a not between&nbsp;\u6570\u503c1 and&nbsp;\u6570\u503c2 <\/li><\/ul><\/li><\/ul>\n\n\n\n<p><strong>9\u3001in \u7684\u4f7f\u7528\u65b9\u6cd5<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select * from table1 where a [not] in ('\u503c1','\u503c2','\u503c4','\u503c6') <\/li><\/ul>\n\n\n\n<p><strong>10\u3001\u4e24\u5f20\u5173\u8054\u8868\uff0c\u5220\u9664\u4e3b\u8868\u4e2d\u5df2\u7ecf\u5728\u526f\u8868\u4e2d\u6ca1\u6709\u7684\u4fe1\u606f&nbsp;<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) <\/li><\/ul>\n\n\n\n<p><strong>11\u3001\u56db\u8868\u8054\u67e5\u95ee\u9898<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where&nbsp;..... <\/li><\/ul>\n\n\n\n<p><strong>12\u3001\u65e5\u7a0b\u5b89\u6392\u63d0\u524d\u4e94\u5206\u949f\u63d0\u9192&nbsp;<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select * from&nbsp;\u65e5\u7a0b\u5b89\u6392&nbsp;where datediff('minute',f\u5f00\u59cb\u65f6\u95f4,getdate())&gt;5 <\/li><\/ul>\n\n\n\n<p><strong>13\u3001SQL \u5206\u9875<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select top 10 b.* from (select top 20&nbsp;\u4e3b\u952e\u5b57\u6bb5,\u6392\u5e8f\u5b57\u6bb5&nbsp;from&nbsp;\u8868\u540d&nbsp;order by&nbsp;\u6392\u5e8f\u5b57\u6bb5&nbsp;desc) a,\u8868\u540d&nbsp;b where b.\u4e3b\u952e\u5b57\u6bb5&nbsp;= a.\u4e3b\u952e\u5b57\u6bb5&nbsp;order by a.\u6392\u5e8f\u5b57\u6bb5 <\/li><\/ul>\n\n\n\n<p><strong>\u5177\u4f53\u5b9e\u73b0\uff1a<\/strong>\u5173\u4e8e\u6570\u636e\u5e93\u5206\u9875\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>declare\u00a0@start\u00a0int,@end\u00a0int\u00a0\u00a0@sql\u00a0\u00a0nvarchar(600)\u00a0\u00a0set\u00a0@sql=\u2019select\u00a0top\u2019+str(@end-@start+1)+\u2019+from\u00a0T\u00a0where\u00a0rid\u00a0not\u00a0in(select\u00a0top\u2019+str(@str-1)+\u2019Rid\u00a0from\u00a0T\u00a0where\u00a0Rid>-1)\u2019\u00a0\u00a0exec sp_executesql @sql<\/code><\/pre>\n\n\n\n<p><strong>\u6ce8\u610f\uff1a<\/strong>\u5728top\u540e\u4e0d\u80fd\u76f4\u63a5\u8ddf\u4e00\u4e2a\u53d8\u91cf\uff0c\u6240\u4ee5\u5728\u5b9e\u9645\u5e94\u7528\u4e2d\u53ea\u6709\u8fd9\u6837\u7684\u8fdb\u884c\u7279\u6b8a\u7684\u5904\u7406\u3002Rid\u4e3a\u4e00\u4e2a\u6807\u8bc6\u5217\uff0c\u5982\u679ctop\u540e\u8fd8\u6709\u5177\u4f53\u7684\u5b57\u6bb5\uff0c\u8fd9\u6837\u505a\u662f\u975e\u5e38\u6709\u597d\u5904\u7684\u3002\u56e0\u4e3a\u8fd9\u6837\u53ef\u4ee5\u907f\u514d top\u7684\u5b57\u6bb5\u5982\u679c\u662f\u903b\u8f91\u7d22\u5f15\u7684\uff0c\u67e5\u8be2\u7684\u7ed3\u679c\u540e\u5b9e\u9645\u8868\u4e2d\u7684\u4e0d\u4e00\u81f4\uff08\u903b\u8f91\u7d22\u5f15\u4e2d\u7684\u6570\u636e\u6709\u53ef\u80fd\u548c\u6570\u636e\u8868\u4e2d\u7684\u4e0d\u4e00\u81f4\uff0c\u800c\u67e5\u8be2\u65f6\u5982\u679c\u5904\u5728\u7d22\u5f15\u5219\u9996\u5148\u67e5\u8be2\u7d22\u5f15\uff09<\/p>\n\n\n\n<p><strong>14\u3001\u524d10\u6761\u8bb0\u5f55<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select top 10 * form table1 where&nbsp;\u8303\u56f4 <\/li><\/ul>\n\n\n\n<p><strong>15\u3001\u9009\u62e9\u5728\u6bcf\u4e00\u7ec4b\u503c\u76f8\u540c\u7684\u6570\u636e\u4e2d\u5bf9\u5e94\u7684a\u6700\u5927\u7684\u8bb0\u5f55\u7684\u6240\u6709\u4fe1\u606f(\u7c7b\u4f3c\u8fd9\u6837\u7684\u7528\u6cd5\u53ef\u4ee5\u7528\u4e8e\u8bba\u575b\u6bcf\u6708\u6392\u884c\u699c,\u6bcf\u6708\u70ed\u9500\u4ea7\u54c1\u5206\u6790,\u6309\u79d1\u76ee\u6210\u7ee9\u6392\u540d,\u7b49\u7b49.)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select&nbsp;a,b,c&nbsp;from&nbsp;tablename ta&nbsp;where a=(select max(a) from tablename tb where tb.b=ta.b) <\/li><\/ul>\n\n\n\n<p><strong>16\u3001\u5305\u62ec\u6240\u6709\u5728&nbsp;tableA\u4e2d\u4f46\u4e0d\u5728&nbsp;tableB\u548ctableC\u4e2d\u7684\u884c\u5e76\u6d88\u9664\u6240\u6709\u91cd\u590d\u884c\u800c\u6d3e\u751f\u51fa\u4e00\u4e2a\u7ed3\u679c\u8868<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> (select a from tableA ) except (select a from tableB) except (select a from tableC) <\/li><\/ul>\n\n\n\n<p><strong>17\u3001\u968f\u673a\u53d6\u51fa10\u6761\u6570\u636e<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select top 10 * from&nbsp;tablename&nbsp;order by&nbsp;newid() <\/li><\/ul>\n\n\n\n<p><strong>18\u3001\u968f\u673a\u9009\u62e9\u8bb0\u5f55<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select newid() <\/li><\/ul>\n\n\n\n<p><strong>19\u3001\u8bf4\u660e\uff1a\u5220\u9664\u91cd\u590d\u8bb0\u5f55<br><\/strong><\/p>\n\n\n\n<p><strong>1)&nbsp;<\/strong>delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)<\/p>\n\n\n\n<p><br><strong>2)&nbsp;<\/strong>select distinct * into temp from&nbsp;tablename<br>&nbsp;&nbsp;delete from&nbsp;tablename<br>&nbsp;&nbsp;insert into&nbsp;tablename&nbsp;select * from temp<\/p>\n\n\n\n<p><strong>\u8bc4\u4ef7\uff1a<\/strong>\u8fd9\u79cd\u64cd\u4f5c\u7275\u8fde\u5927\u91cf\u7684\u6570\u636e\u7684\u79fb\u52a8\uff0c\u8fd9\u79cd\u505a\u6cd5\u4e0d\u9002\u5408\u5927\u5bb9\u91cf\u4f46\u6570\u636e\u64cd\u4f5c3),\u4f8b\u5982\uff1a\u5728\u4e00\u4e2a\u5916\u90e8\u8868\u4e2d\u5bfc\u5165\u6570\u636e\uff0c\u7531\u4e8e\u67d0\u4e9b\u539f\u56e0\u7b2c\u4e00\u6b21\u53ea\u5bfc\u5165\u4e86\u4e00\u90e8\u5206\uff0c\u4f46\u5f88\u96be\u5224\u65ad\u5177\u4f53\u4f4d\u7f6e\uff0c\u8fd9\u6837\u53ea\u6709\u5728\u4e0b\u4e00\u6b21\u5168\u90e8\u5bfc\u5165\uff0c\u8fd9\u6837\u4e5f\u5c31\u4ea7\u751f\u597d\u591a\u91cd\u590d\u7684\u5b57\u6bb5\uff0c\u600e\u6837\u5220\u9664\u91cd\u590d\u5b57\u6bb5<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>alter\u00a0table\u00a0tablename--\u6dfb\u52a0\u4e00\u4e2a\u81ea\u589e\u5217add\u00a0\u00a0column_b\u00a0int\u00a0identity(1,1)\u00a0delete\u00a0from\u00a0tablename\u00a0where\u00a0column_b\u00a0not\u00a0in(select\u00a0max(column_b)\u00a0from\u00a0tablename\u00a0group\u00a0by\u00a0column1,column2,...)alter\u00a0table\u00a0tablename\u00a0drop\u00a0column\u00a0column_b<\/code><\/pre>\n\n\n\n<p><strong>20\u3001\u5217\u51fa\u6570\u636e\u5e93\u91cc\u6240\u6709\u7684\u8868\u540d<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select name from sysobjects where type='U' <\/li><li><strong>\u6ce8\uff1aU\u4ee3\u8868\u7528\u6237 <\/strong><\/li><\/ul>\n\n\n\n<p><strong>21\u3001\u5217\u51fa\u8868\u91cc\u7684\u6240\u6709\u7684\u5217\u540d<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select name from syscolumns where id=object_id('TableName') <\/li><\/ul>\n\n\n\n<p><strong>22\u3001<\/strong>\u5217\u793atype\u3001vender\u3001pcs\u5b57\u6bb5\uff0c\u4ee5type\u5b57\u6bb5\u6392\u5217\uff0ccase\u53ef\u4ee5\u65b9\u4fbf\u5730\u5b9e\u73b0\u591a\u91cd\u9009\u62e9\uff0c\u7c7b\u4f3cselect \u4e2d\u7684case\u3002<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type <\/li><\/ul>\n\n\n\n<p><strong>\u663e\u793a\u7ed3\u679c\uff1a<br>type vender pcs<br><\/strong>\u7535\u8111&nbsp;A 1<br>\u7535\u8111&nbsp;A 1<br>\u5149\u76d8&nbsp;B 2<br>\u5149\u76d8&nbsp;A 2<br>\u624b\u673a&nbsp;B 3<br>\u624b\u673a&nbsp;C 3<\/p>\n\n\n\n<p><strong>23\u3001\u521d\u59cb\u5316\u8868table1<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> TRUNCATE TABLE table1 <\/li><\/ul>\n\n\n\n<p><strong>24\u3001\u9009\u62e9\u4ece10\u523015\u7684\u8bb0\u5f55<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> select top 5 * from (select top 15 * from table order by id asc) table_\u522b\u540d&nbsp;order by id desc <\/li><\/ul>\n","raw":"","protected":false},"excerpt":{"rendered":"<p>1\u3001\u590d\u5236\u8868(\u53ea\u590d\u5236\u7ed3\u6784\uff0c\u6e90\u8868\u540d(a) \u65b0\u8868\u540d(b)) \u65b9\u6cd5\u4e00\uff1aselect * into b from a where 1&lt; &#8230;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"emotion":"","emotion_color":"","title_style":"","license":"","footnotes":""},"categories":[3],"tags":[],"class_list":["post-61","post","type-post","status-publish","format-standard","hentry","category-mysql"],"post_thumbnail_image":"","content_first_image":null,"post_medium_image_300":"","post_thumbnail_image_624":"","post_frist_image":null,"post_medium_image":"","post_large_image":"","post_full_image":"","post_all_images":[],"videoAdId":"","listAd":"0","listAdId":"","listAdEvery":6,"total_comments":0,"category_name":"MySQL","post_date":"2022-07-06","like_count":"1","praiseWord":"\u9f13\u52b1","copyright_state":"","excitationAd":"0","rewardedVideoAdId":"","detailAdId":"","detailAd":"0","enterpriseMinapp":"0","audios":[],"postImageUrl":"https:\/\/wp-moto-1258805347.cos.ap-shanghai.myqcloud.com\/2023\/05\/20230519082947553.jpg","avatarurls":[{"avatarurl":"https:\/\/wp-moto-1258805347.cos.ap-shanghai.myqcloud.com\/2023\/05\/20230525140221253.jpg"}],"related_posts":null,"pageviews":316,"next_post_id":69,"next_post_title":"MySQL\u6280\u5de7","previous_post_id":47,"previous_post_title":"MySQL\u57fa\u7840\u8bed\u53e5","_links":{"self":[{"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/posts\/61","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/comments?post=61"}],"version-history":[{"count":0,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/posts\/61\/revisions"}],"wp:attachment":[{"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/media?parent=61"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/categories?post=61"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/tags?post=61"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}