{"id":109,"date":"2025-03-28T13:58:38","date_gmt":"2025-03-28T05:58:38","guid":{"rendered":"https:\/\/zd1269878.noblogs.org\/?p=109"},"modified":"2025-10-21T10:32:15","modified_gmt":"2025-10-21T02:32:15","slug":"2025-03-28-%e6%95%b0%e6%8d%ae%e5%ba%93-%e7%ac%94%e8%ae%b0%e6%95%b4%e7%90%86","status":"publish","type":"post","link":"https:\/\/zd1269878.noblogs.org\/en-US\/post\/2025\/03\/28\/2025-03-28-%e6%95%b0%e6%8d%ae%e5%ba%93-%e7%ac%94%e8%ae%b0%e6%95%b4%e7%90%86\/","title":{"rendered":"2025-03-28-\u6570\u636e\u5e93-\u7b14\u8bb0\u6574\u7406"},"content":{"rendered":"

20250328<\/h2>\n

\u4e3b\u952e\u7ea6\u675f \u4e0d\u53ef\u4e3a\u7a7a \u552f\u4e00<\/p>\n

\u552f\u4e00\u6027\u7ea6\u675f \u53ef\u4e3a\u7a7a \u552f\u4e00<\/p>\n

ADD \u6570\u636e\u5217\u540d<\/em><\/strong> \u6570\u636e\u7c7b\u578b #\u6dfb\u52a0\u65b0\u7684\u5217<\/p>\n

DROP COLUMN<\/em><\/strong> \u5217\u540d #\u5220\u9664\u5217<\/p>\n

ALTER COLUMN<\/em><\/strong> \u5217\u540d \u65b0\u7684\u6570\u636e\u7c7b\u578b #\u6539\u8be5\u5217\u7684\u6570\u636e\u7c7b\u578b\uff0c\u53ea\u80fd\u52a0\u957f\u4e0d\u80fd\u7f29\u77ed<\/p>\n

 <\/p>\n

\u4ee5\u4e0b\u662f\u5173\u4e8e SQL \u4e2d UPDATE\u3001DELETE \u548c TRUNCATE \u8bed\u53e5\u7684\u4f7f\u7528\u65b9\u6cd5\u53ca\u6ce8\u610f\u4e8b\u9879\u7684\u8be6\u7ec6\u8bf4\u660e\uff1a<\/p>\n

1. UPDATE <\/strong>\u8bed\u53e5<\/strong><\/h3>\n

\u7528\u9014<\/strong>\uff1a\u4fee\u6539\u8868\u4e2d\u5df2\u6709\u7684\u6570\u636e\u3002<\/p>\n

\u57fa\u672c\u8bed\u6cd5<\/strong>\uff1a<\/h4>\n

UPDATE \u8868\u540d
\nSET \u52171 = \u503c1, \u52172 = \u503c2, …
\nWHERE \u6761\u4ef6;<\/p>\n

\u793a\u4f8b<\/strong>\uff1a<\/h4>\n

— \u5c06\u7528\u6237ID\u4e3a1\u7684\u7528\u6237\u7684\u5e74\u9f84\u66f4\u65b0\u4e3a25
\nUPDATE users
\nSET age = 25
\nWHERE user_id = 1;<\/p>\n

— \u540c\u65f6\u66f4\u65b0\u591a\u4e2a\u5b57\u6bb5
\nUPDATE employees
\nSET salary = 50000, department = 'HR'
\nWHERE employee_id = 101;<\/p>\n

\u6ce8\u610f\u4e8b\u9879<\/strong>\uff1a<\/h4>\n
    \n
  • \u52a1\u5fc5\u4f7f\u7528 <\/strong>WHERE<\/strong> \u5b50\u53e5<\/strong>\uff1a\u5982\u679c\u7701\u7565 WHERE\uff0c\u8868\u4e2d\u6240\u6709\u884c\u7684\u6307\u5b9a\u5217\u90fd\u4f1a\u88ab\u66f4\u65b0\uff01<\/li>\n
  • \u4e8b\u52a1\u5b89\u5168<\/strong>\uff1a\u5728\u751f\u4ea7\u73af\u5883\u4e2d\uff0c\u5efa\u8bae\u5728\u66f4\u65b0\u524d\u5f00\u542f\u4e8b\u52a1\uff08\u5982 BEGIN TRANSACTION\uff09\uff0c\u4ee5\u4fbf\u5728\u51fa\u9519\u65f6\u56de\u6eda\u3002<\/li>\n
  • \u5907\u4efd\u6570\u636e<\/strong>\uff1a\u5bf9\u5173\u952e\u6570\u636e\u64cd\u4f5c\u524d\uff0c\u5efa\u8bae\u5148\u5907\u4efd\uff08\u5982 CREATE TABLE users_backup AS SELECT * FROM users\uff09\u3002<\/li>\n<\/ul>\n

    2. DELETE <\/strong>\u8bed\u53e5<\/strong><\/h3>\n

    \u7528\u9014<\/strong>\uff1a\u5220\u9664\u8868\u4e2d\u7684\u6570\u636e\u884c\u3002<\/p>\n

    \u57fa\u672c\u8bed\u6cd5<\/strong>\uff1a<\/h4>\n

    DELETE FROM \u8868\u540d
    \nWHERE \u6761\u4ef6;<\/p>\n

    \u793a\u4f8b<\/strong>\uff1a<\/h4>\n

    — \u5220\u9664\u7528\u6237ID\u4e3a1\u7684\u8bb0\u5f55
    \nDELETE FROM users
    \nWHERE user_id = 1;<\/p>\n

    — \u5220\u9664\u6240\u6709\u5e74\u9f84\u5927\u4e8e60\u7684\u8bb0\u5f55
    \nDELETE FROM employees
    \nWHERE age > 60;<\/p>\n

    \u6ce8\u610f\u4e8b\u9879<\/strong>\uff1a<\/h4>\n
      \n
    • WHERE<\/strong> \u5b50\u53e5\u540c\u6837\u5173\u952e<\/strong>\uff1a\u7701\u7565 WHERE \u4f1a\u5220\u9664\u8868\u4e2d\u6240\u6709\u6570\u636e\uff01<\/li>\n
    • \u6027\u80fd\u95ee\u9898<\/strong>\uff1a\u5220\u9664\u5927\u91cf\u6570\u636e\u65f6\uff0cDELETE \u4f1a\u9010\u884c\u64cd\u4f5c\u5e76\u8bb0\u5f55\u65e5\u5fd7\uff0c\u53ef\u80fd\u8f83\u6162\u3002<\/li>\n
    • \u89e6\u53d1\u5668<\/strong>\uff1aDELETE \u4f1a\u89e6\u53d1 DELETE \u7c7b\u578b\u7684\u89e6\u53d1\u5668\uff08\u5982\u679c\u6709\uff09\u3002<\/li>\n
    • \u4e8b\u52a1\u4e0e\u5907\u4efd<\/strong>\uff1a\u540c UPDATE\uff0c\u5efa\u8bae\u64cd\u4f5c\u524d\u5907\u4efd\u5e76\u4f7f\u7528\u4e8b\u52a1\u3002<\/li>\n<\/ul>\n

      3. TRUNCATE <\/strong>\u8bed\u53e5<\/strong><\/h3>\n

      \u7528\u9014<\/strong>\uff1a\u5feb\u901f\u5220\u9664\u8868\u4e2d\u6240\u6709\u6570\u636e\uff08\u4e0d\u53ef\u9006\u64cd\u4f5c\uff09\u3002<\/p>\n

      \u57fa\u672c\u8bed\u6cd5<\/strong>\uff1a<\/h4>\n

      TRUNCATE TABLE \u8868\u540d;<\/p>\n

      \u793a\u4f8b<\/strong>\uff1a<\/h4>\n

      TRUNCATE TABLE logs;\u00a0 — \u6e05\u7a7a logs \u8868\u7684\u6240\u6709\u6570\u636e<\/p>\n

      \u6ce8\u610f\u4e8b\u9879<\/strong>\uff1a<\/h4>\n
        \n
      • \u4e0d\u53ef\u9006<\/strong>\uff1aTRUNCATE \u4f1a\u76f4\u63a5\u5220\u9664\u6240\u6709\u6570\u636e\uff0c\u4e14\u65e0\u6cd5\u901a\u8fc7\u4e8b\u52a1\u56de\u6eda<\/strong>\uff08\u9664\u975e\u5728\u652f\u6301\u4e8b\u52a1\u7684\u6570\u636e\u5e93\u4e2d\u663e\u5f0f\u4f7f\u7528 ROLLBACK\uff09\u3002<\/li>\n
      • \u6548\u7387\u9ad8<\/strong>\uff1a\u4e0d\u9010\u884c\u5220\u9664\uff0c\u800c\u662f\u91ca\u653e\u6570\u636e\u9875\uff0c\u65e5\u5fd7\u8bb0\u5f55\u5c11\uff0c\u901f\u5ea6\u8fdc\u5feb\u4e8e DELETE\u3002<\/li>\n
      • \u91cd\u7f6e\u81ea\u589e\u5217<\/strong>\uff1a\u4f1a\u5c06\u81ea\u589e\u5217\uff08\u5982 AUTO_INCREMENT \u6216 IDENTITY\uff09\u91cd\u7f6e\u4e3a\u521d\u59cb\u503c\u3002<\/li>\n
      • \u6743\u9650\u8981\u6c42<\/strong>\uff1a\u901a\u5e38\u9700\u8981\u66f4\u9ad8\u6743\u9650\uff08\u5982 DROP \u6743\u9650\uff09\u3002<\/li>\n
      • \u4e0d\u89e6\u53d1\u89e6\u53d1\u5668<\/strong>\uff1a\u4e0d\u4f1a\u89e6\u53d1 DELETE \u89e6\u53d1\u5668\u3002<\/li>\n<\/ul>\n

        DELETE vs TRUNCATE <\/strong>\u5bf9\u6bd4<\/strong><\/h3>\n\n\n\n\n\n\n\n\n\n\n
        \u7279\u6027<\/td>\nDELETE<\/td>\nTRUNCATE<\/td>\n<\/tr>\n<\/thead>\n
        \u5220\u9664\u8303\u56f4<\/strong><\/td>\n\u53ef\u4ee5\u6709\u6761\u4ef6\u5220\u9664\u90e8\u5206\u6570\u636e<\/td>\n\u53ea\u80fd\u5220\u9664\u5168\u90e8\u6570\u636e<\/td>\n<\/tr>\n
        \u65e5\u5fd7\u8bb0\u5f55<\/strong><\/td>\n\u9010\u884c\u8bb0\u5f55\u65e5\u5fd7\uff0c\u8f83\u6162<\/td>\n\u6574\u4f53\u91ca\u653e\u6570\u636e\u9875\uff0c\u901f\u5ea6\u5feb<\/td>\n<\/tr>\n
        \u4e8b\u52a1\u56de\u6eda<\/strong><\/td>\n\u53ef\u56de\u6eda\uff08\u9700\u5728\u4e8b\u52a1\u4e2d\uff09<\/td>\n\u901a\u5e38\u4e0d\u53ef\u56de\u6eda<\/td>\n<\/tr>\n
        \u81ea\u589e\u5217\u91cd\u7f6e<\/strong><\/td>\n\u4e0d\u91cd\u7f6e\u81ea\u589e\u5217<\/td>\n\u91cd\u7f6e\u81ea\u589e\u5217\u5230\u521d\u59cb\u503c<\/td>\n<\/tr>\n
        \u89e6\u53d1\u5668<\/strong><\/td>\n\u89e6\u53d1 DELETE \u89e6\u53d1\u5668<\/td>\n\u4e0d\u89e6\u53d1<\/td>\n<\/tr>\n
        \u8bed\u6cd5\u590d\u6742\u5ea6<\/strong><\/td>\n\u652f\u6301\u590d\u6742\u6761\u4ef6\uff08\u5982\u5b50\u67e5\u8be2\uff09<\/td>\n\u4ec5\u80fd\u6e05\u7a7a\u6574\u4e2a\u8868<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

        \u4f7f\u7528\u5efa\u8bae<\/strong><\/h3>\n
          \n
        1. \u8c28\u614e\u64cd\u4f5c<\/strong>\uff1a\u5728\u751f\u4ea7\u73af\u5883\u6267\u884c\u524d\uff0c\u5148\u7528 SELECT * FROM \u8868\u540d WHERE \u6761\u4ef6 \u786e\u8ba4\u5f71\u54cd\u8303\u56f4\u3002<\/li>\n
        2. \u4f18\u5148\u4f7f\u7528 <\/strong>TRUNCATE<\/strong>\uff1a\u5982\u679c\u9700\u8981\u6e05\u7a7a\u8868\u4e14\u65e0\u9700\u6761\u4ef6\uff0c\u4f18\u5148\u9009\u62e9 TRUNCATE \u4ee5\u63d0\u9ad8\u6548\u7387\u3002<\/li>\n
        3. \u907f\u514d\u5168\u8868\u64cd\u4f5c<\/strong>\uff1a\u5bf9\u5927\u8868\u6267\u884c DELETE \u6216 TRUNCATE \u65f6\uff0c\u6ce8\u610f\u9501\u8868\u548c\u6027\u80fd\u95ee\u9898\u3002<\/li>\n
        4. \u6743\u9650\u63a7\u5236<\/strong>\uff1a\u4e25\u683c\u9650\u5236\u5bf9 TRUNCATE \u7684\u6743\u9650\uff0c\u9632\u6b62\u8bef\u64cd\u4f5c\u3002<\/li>\n<\/ol>\n

           <\/p>\n

           <\/p>\n

          DISTINCT\u67e5\u8be2\u65f6\u53bb\u91cd<\/p>\n

          SELECT DISTINCT FROM WHERE GROUP BY HAVING ORDER BY<\/p>\n

          \u5728 SQL \u4e2d\uff0cDISTINCT \u5173\u952e\u5b57\u7528\u4e8e\u5728\u67e5\u8be2\u7ed3\u679c\u4e2d\u53bb\u9664\u91cd\u590d\u7684\u884c\u3002\u5f53\u4e0e\u5176\u4ed6\u5b50\u53e5\uff08\u5982 WHERE\u3001GROUP BY\u3001HAVING\u3001ORDER BY\uff09\u7ed3\u5408\u65f6\uff0c\u9700\u6ce8\u610f\u5b83\u4eec\u7684\u6267\u884c\u987a\u5e8f\u548c\u903b\u8f91\u5173\u7cfb\u3002\u4ee5\u4e0b\u662f\u5177\u4f53\u7528\u6cd5\u53ca\u793a\u4f8b\uff1a<\/p>\n

          1. <\/strong>\u57fa\u672c\u8bed\u6cd5<\/strong><\/h3>\n

          SELECT DISTINCT column1, column2, …
          \nFROM table_name
          \nWHERE condition
          \nGROUP BY column1, column2, …
          \nHAVING group_condition
          \nORDER BY column1 [ASC|DESC], column2 [ASC|DESC];<\/p>\n

          2. <\/strong>\u5404\u5b50\u53e5\u4e0e <\/strong>DISTINCT<\/strong> \u7684\u7ed3\u5408\u4f7f\u7528<\/strong><\/h3>\n

          (1) <\/strong>SELECT DISTINCT<\/strong> \u57fa\u7840\u53bb\u91cd<\/strong><\/h4>\n
            \n
          • \u4f5c\u7528<\/strong>\uff1a\u8fd4\u56de\u6307\u5b9a\u5217\u7684\u552f\u4e00\u7ec4\u5408\u503c\u3002<\/li>\n
          • \u793a\u4f8b<\/strong>\uff1a<\/li>\n<\/ul>\n
              \n
            • — \u67e5\u8be2\u4e0d\u91cd\u590d\u7684\u90e8\u95e8\u540d\u79f0
              \nSELECT DISTINCT department
              \nFROM employees;– \u67e5\u8be2\u90e8\u95e8\u548c\u804c\u4f4d\u7684\u552f\u4e00\u7ec4\u5408
              \nSELECT DISTINCT department, position
              \nFROM employees;<\/li>\n<\/ul>\n

              (2) <\/strong>WHERE<\/strong> \u5b50\u53e5\u8fc7\u6ee4\u540e\u53bb\u91cd<\/strong><\/h4>\n
                \n
              • \u6267\u884c\u987a\u5e8f<\/strong>\uff1a\u5148\u6267\u884c WHERE \u8fc7\u6ee4\u6570\u636e\uff0c\u518d\u5bf9\u7ed3\u679c\u53bb\u91cd\u3002<\/li>\n
              • \u793a\u4f8b<\/strong>\uff1a<\/li>\n<\/ul>\n
                  \n
                • — \u67e5\u8be2\u5de5\u8d44\u9ad8\u4e8e 5000 \u7684\u5458\u5de5\u6240\u5728\u57ce\u5e02\uff08\u53bb\u91cd\uff09
                  \nSELECT DISTINCT city
                  \nFROM employees
                  \nWHERE salary > 5000;<\/li>\n<\/ul>\n

                  (3) <\/strong>GROUP BY<\/strong> \u4e0e <\/strong>DISTINCT<\/strong> \u7684\u5f02\u540c<\/strong><\/h4>\n
                    \n
                  • \u533a\u522b<\/strong>\uff1a\n
                      \n
                    • DISTINCT \u76f4\u63a5\u53bb\u91cd\uff0c\u8fd4\u56de\u6240\u6709\u5217\u7684\u552f\u4e00\u503c\u3002<\/li>\n
                    • GROUP BY \u7528\u4e8e\u5206\u7ec4\u805a\u5408\uff08\u5982 COUNT, SUM\uff09\u3002<\/li>\n<\/ul>\n<\/li>\n
                    • \u793a\u4f8b<\/strong>\uff1a<\/li>\n<\/ul>\n
                        \n
                      • — \u4f7f\u7528 GROUP BY \u7edf\u8ba1\u6bcf\u4e2a\u90e8\u95e8\u7684\u4eba\u6570\uff08\u65e0\u9700 DISTINCT\uff09
                        \nSELECT department, COUNT(*) AS employee_count
                        \nFROM employees
                        \nGROUP BY department;– \u82e5\u540c\u65f6\u9700\u8981\u53bb\u91cd\u548c\u5206\u7ec4\uff0c\u9700\u660e\u786e\u903b\u8f91\uff08\u5982\u7edf\u8ba1\u4e0d\u540c\u804c\u4f4d\u7684\u6570\u91cf\uff09
                        \nSELECT department, COUNT(DISTINCT position) AS unique_positions
                        \nFROM employees
                        \nGROUP BY department;<\/li>\n<\/ul>\n

                        (4) <\/strong>HAVING<\/strong> \u8fc7\u6ee4\u5206\u7ec4\u540e\u7684\u7ed3\u679c<\/strong><\/h4>\n