{"id":2597,"date":"2025-03-11T23:20:19","date_gmt":"2025-03-11T15:20:19","guid":{"rendered":"https:\/\/www.laixuexila.com\/?p=2597"},"modified":"2025-03-11T23:20:19","modified_gmt":"2025-03-11T15:20:19","slug":"postgresql-%e5%88%9b%e5%bb%ba%e8%a1%a8%e6%a0%bc-create-table","status":"publish","type":"post","link":"https:\/\/www.laixuexila.com\/index.php\/2025\/03\/11\/postgresql-%e5%88%9b%e5%bb%ba%e8%a1%a8%e6%a0%bc-create-table\/","title":{"rendered":"PostgreSQL \u521b\u5efa\u8868\u683c (CREATE TABLE)"},"content":{"rendered":"\n

\u5728 PostgreSQL \u4e2d\uff0cCREATE TABLE<\/code> \u8bed\u53e5\u7528\u4e8e\u521b\u5efa\u65b0\u7684\u8868\u683c\uff08\u5173\u7cfb\uff09\uff0c\u5b9a\u4e49\u8868\u7684\u5217\u53ca\u5176\u6570\u636e\u7c7b\u578b\u3001\u7ea6\u675f\u7b49\u3002\u4ee5\u4e0b\u662f\u5b8c\u6574\u7684 CREATE TABLE<\/code> \u8bed\u6cd5\u548c\u8be6\u7ec6\u89e3\u6790\u3002<\/p>\n\n\n\n

\n\n\n\n

1. \u57fa\u672c CREATE TABLE<\/code> \u8bed\u6cd5<\/strong><\/h2>\n\n\n\n
CREATE TABLE table_name (\n    column_name1 data_type constraints,\n    column_name2 data_type constraints,\n    ...\n);<\/code><\/pre>\n\n\n\n
    \n
  • table_name<\/code>\uff1a\u8868\u540d\uff0c\u5fc5\u987b\u552f\u4e00\uff0c\u4e0d\u80fd\u4e0e\u5df2\u6709\u8868\u540d\u51b2\u7a81\u3002<\/li>\n\n\n\n
  • column_name<\/code>\uff1a\u5217\u540d\uff0c\u6bcf\u4e2a\u5217\u4ee3\u8868\u4e00\u4e2a\u5b57\u6bb5\u3002<\/li>\n\n\n\n
  • data_type<\/code>\uff1a\u5217\u7684\u6570\u636e\u7c7b\u578b\uff0c\u5982 INTEGER<\/code>, VARCHAR(255)<\/code>, BOOLEAN<\/code> \u7b49\u3002<\/li>\n\n\n\n
  • constraints<\/code>\uff1a\u5217\u7ea6\u675f\uff08\u53ef\u9009\uff09\uff0c\u5982 PRIMARY KEY<\/code>, NOT NULL<\/code>, UNIQUE<\/code> \u7b49\u3002<\/li>\n<\/ul>\n\n\n\n
    \n\n\n\n
    2. \u521b\u5efa\u4e00\u4e2a\u57fa\u672c\u8868<\/strong><\/h2>\n\n\n\n
    \u521b\u5efa\u4e00\u4e2a\u540d\u4e3a employees<\/code> \u7684\u8868\uff1a<\/p>\n\n\n\n
    CREATE TABLE employees (\n    id SERIAL PRIMARY KEY,\n    first_name VARCHAR(50) NOT NULL,\n    last_name VARCHAR(50) NOT NULL,\n    email VARCHAR(100) UNIQUE NOT NULL,\n    salary NUMERIC(10,2) CHECK (salary > 0),\n    hire_date DATE DEFAULT CURRENT_DATE\n);<\/code><\/pre>\n\n\n\n
    \u89e3\u91ca<\/strong><\/h3>\n\n\n\n
    \u5217\u540d<\/strong><\/th>\u6570\u636e\u7c7b\u578b<\/strong><\/th>\u7ea6\u675f<\/strong><\/th><\/tr><\/thead>
    id<\/code><\/td>SERIAL<\/code><\/td>PRIMARY KEY<\/code>\uff08\u81ea\u52a8\u9012\u589e\u4e3b\u952e\uff09<\/td><\/tr>
    first_name<\/code><\/td>VARCHAR(50)<\/code><\/td>NOT NULL<\/code>\uff08\u4e0d\u80fd\u4e3a\u7a7a\uff09<\/td><\/tr>
    last_name<\/code><\/td>VARCHAR(50)<\/code><\/td>NOT NULL<\/code><\/td><\/tr>
    email<\/code><\/td>VARCHAR(100)<\/code><\/td>UNIQUE NOT NULL<\/code>\uff08\u552f\u4e00\u4e14\u4e0d\u80fd\u4e3a\u7a7a\uff09<\/td><\/tr>
    salary<\/code><\/td>NUMERIC(10,2)<\/code><\/td>CHECK (salary > 0)<\/code>\uff08\u9650\u5236\u5fc5\u987b\u5927\u4e8e0\uff09<\/td><\/tr>
    hire_date<\/code><\/td>DATE<\/code><\/td>DEFAULT CURRENT_DATE<\/code>\uff08\u9ed8\u8ba4\u503c\u4e3a\u5f53\u524d\u65e5\u671f\uff09<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    \n\n\n\n
    3. \u5e38\u89c1\u6570\u636e\u7c7b\u578b<\/strong><\/h2>\n\n\n\n
    \u6570\u636e\u7c7b\u578b<\/strong><\/th>\u63cf\u8ff0<\/strong><\/th><\/tr><\/thead>
    INTEGER<\/code><\/td>\u6574\u6570\u7c7b\u578b<\/td><\/tr>
    SERIAL<\/code><\/td>\u81ea\u52a8\u9012\u589e\u6574\u6570\uff08\u7528\u4e8e\u4e3b\u952e\uff09<\/td><\/tr>
    VARCHAR(n)<\/code><\/td>\u53ef\u53d8\u957f\u5ea6\u5b57\u7b26\u4e32\uff0c\u6700\u591a n<\/code> \u4e2a\u5b57\u7b26<\/td><\/tr>
    TEXT<\/code><\/td>\u957f\u6587\u672c<\/td><\/tr>
    BOOLEAN<\/code><\/td>\u5e03\u5c14\u503c\uff08TRUE<\/code> \/ FALSE<\/code>\uff09<\/td><\/tr>
    DATE<\/code><\/td>\u65e5\u671f\uff08YYYY-MM-DD\uff09<\/td><\/tr>
    TIMESTAMP<\/code><\/td>\u65e5\u671f\u65f6\u95f4\uff08YYYY-MM-DD HH:MI:SS\uff09<\/td><\/tr>
    NUMERIC(p,s)<\/code><\/td>\u5b9a\u70b9\u6570\uff0cp<\/code> \u4e3a\u603b\u4f4d\u6570\uff0cs<\/code> \u4e3a\u5c0f\u6570\u4f4d\u6570<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    \n\n\n\n
    4. \u7ea6\u675f\uff08Constraints\uff09<\/strong><\/h2>\n\n\n\n
    \u7ea6\u675f\u7528\u4e8e\u5b9a\u4e49\u6570\u636e\u5b8c\u6574\u6027\uff0cPostgreSQL \u652f\u6301\u4ee5\u4e0b\u51e0\u79cd\u7ea6\u675f\uff1a<\/p>\n\n\n\n
    (1) PRIMARY KEY<\/code>\uff08\u4e3b\u952e\uff09<\/strong><\/h3>\n\n\n\n
      \n
    • \u552f\u4e00\u6807\u8bc6\u6bcf\u4e00\u884c\u6570\u636e\uff0c\u4e0d\u80fd\u91cd\u590d\u4e14\u4e0d\u80fd\u4e3a\u7a7a\u3002<\/li>\n\n\n\n
    • \u4f8b\u5982\uff1a<\/li>\n<\/ul>\n\n\n\n
        id SERIAL PRIMARY KEY<\/code><\/pre>\n\n\n\n
        \n
      • \u6216\u8005\uff1a<\/li>\n<\/ul>\n\n\n\n
          CREATE TABLE test (\n      id INT,\n      CONSTRAINT test_pk PRIMARY KEY (id)\n  );<\/code><\/pre>\n\n\n\n
        (2) NOT NULL<\/code>\uff08\u975e\u7a7a\uff09<\/strong><\/h3>\n\n\n\n
          \n
        • \u786e\u4fdd\u5217\u4e0d\u80fd\u4e3a\u7a7a\uff1a<\/li>\n<\/ul>\n\n\n\n
            email VARCHAR(100) NOT NULL<\/code><\/pre>\n\n\n\n
          (3) UNIQUE<\/code>\uff08\u552f\u4e00\u7ea6\u675f\uff09<\/strong><\/h3>\n\n\n\n
            \n
          • \u786e\u4fdd\u5217\u503c\u4e0d\u91cd\u590d\uff1a<\/li>\n<\/ul>\n\n\n\n
              email VARCHAR(100) UNIQUE<\/code><\/pre>\n\n\n\n
            (4) CHECK<\/code>\uff08\u68c0\u67e5\u7ea6\u675f\uff09<\/strong><\/h3>\n\n\n\n
              \n
            • \u9650\u5236\u503c\u7684\u8303\u56f4\uff1a<\/li>\n<\/ul>\n\n\n\n
                salary NUMERIC(10,2) CHECK (salary > 0)<\/code><\/pre>\n\n\n\n
              (5) DEFAULT<\/code>\uff08\u9ed8\u8ba4\u503c\uff09<\/strong><\/h3>\n\n\n\n
                \n
              • \u5982\u679c\u672a\u6307\u5b9a\u503c\uff0c\u4f7f\u7528\u9ed8\u8ba4\u503c\uff1a<\/li>\n<\/ul>\n\n\n\n
                  hire_date DATE DEFAULT CURRENT_DATE<\/code><\/pre>\n\n\n\n
                (6) FOREIGN KEY<\/code>\uff08\u5916\u952e\uff09<\/strong><\/h3>\n\n\n\n
                  \n
                • \u786e\u4fdd\u5217\u7684\u503c\u6765\u81ea\u53e6\u4e00\u4e2a\u8868\uff1a<\/li>\n<\/ul>\n\n\n\n
                    CREATE TABLE departments (\n      dept_id SERIAL PRIMARY KEY,\n      dept_name VARCHAR(100) NOT NULL\n  );\n\n  CREATE TABLE employees (\n      id SERIAL PRIMARY KEY,\n      first_name VARCHAR(50) NOT NULL,\n      dept_id INT REFERENCES departments(dept_id)\n  );<\/code><\/pre>\n\n\n\n
                    \n
                  • \u4e5f\u53ef\u4ee5\u663e\u5f0f\u547d\u540d\uff1a<\/li>\n<\/ul>\n\n\n\n
                      CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)<\/code><\/pre>\n\n\n\n
                    \n\n\n\n
                    5. \u590d\u6742\u8868\u521b\u5efa\u793a\u4f8b<\/strong><\/h2>\n\n\n\n
                    CREATE TABLE orders (\n    order_id SERIAL PRIMARY KEY,\n    customer_id INT NOT NULL,\n    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    total_amount NUMERIC(10,2) CHECK (total_amount >= 0),\n    status VARCHAR(20) DEFAULT 'Pending',\n    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE\n);<\/code><\/pre>\n\n\n\n
                      \n
                    • order_id<\/code> \u662f PRIMARY KEY<\/code>\uff0c\u81ea\u52a8\u9012\u589e\u3002<\/li>\n\n\n\n
                    • customer_id<\/code> \u4e0d\u80fd\u4e3a\u7a7a\uff0c\u4e14\u4e3a\u5916\u952e\uff0c\u5f15\u7528 customers<\/code> \u8868\u7684 customer_id<\/code>\u3002<\/li>\n\n\n\n
                    • order_date<\/code> \u9ed8\u8ba4\u4e3a\u5f53\u524d\u65f6\u95f4\u3002<\/li>\n\n\n\n
                    • total_amount<\/code> \u4e0d\u80fd\u5c0f\u4e8e 0\u3002<\/li>\n\n\n\n
                    • status<\/code> \u9ed8\u8ba4\u503c\u4e3a 'Pending'<\/code>\u3002<\/li>\n\n\n\n
                    • ON DELETE CASCADE<\/code>\uff1a\u5982\u679c customers<\/code> \u8868\u4e2d\u7684\u5ba2\u6237\u88ab\u5220\u9664\uff0c\u5176\u8ba2\u5355\u4e5f\u4f1a\u88ab\u5220\u9664\u3002<\/li>\n<\/ul>\n\n\n\n
                      \n\n\n\n
                      6. \u67e5\u770b\u8868\u7ed3\u6784<\/strong><\/h2>\n\n\n\n
                      \u521b\u5efa\u8868\u540e\uff0c\u53ef\u4ee5\u4f7f\u7528 psql<\/code> \u67e5\u770b\u8868\u7ed3\u6784\uff1a<\/p>\n\n\n\n
                      \\d employees<\/code><\/pre>\n\n\n\n
                      \u6216\u8005\uff1a<\/p>\n\n\n\n
                      SELECT column_name, data_type, is_nullable, column_default\nFROM information_schema.columns\nWHERE table_name = 'employees';<\/code><\/pre>\n\n\n\n
                      \n\n\n\n
                      7. \u5220\u9664\u8868<\/strong><\/h2>\n\n\n\n
                      \u5982\u679c\u9700\u8981\u5220\u9664\u8868\uff0c\u53ef\u4ee5\u4f7f\u7528\uff1a<\/p>\n\n\n\n
                      DROP TABLE employees;<\/code><\/pre>\n\n\n\n
                      \u6216\u8005\uff1a<\/p>\n\n\n\n
                      DROP TABLE IF EXISTS employees;<\/code><\/pre>\n\n\n\n
                      \n\n\n\n
                      8. \u4fee\u6539\u8868<\/strong><\/h2>\n\n\n\n
                      (1) \u6dfb\u52a0\u65b0\u5217<\/strong><\/h3>\n\n\n\n
                      ALTER TABLE employees ADD COLUMN phone VARCHAR(15);<\/code><\/pre>\n\n\n\n
                      (2) \u4fee\u6539\u5217\u6570\u636e\u7c7b\u578b<\/strong><\/h3>\n\n\n\n
                      ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12,2);<\/code><\/pre>\n\n\n\n
                      (3) \u5220\u9664\u5217<\/strong><\/h3>\n\n\n\n
                      ALTER TABLE employees DROP COLUMN phone;<\/code><\/pre>\n\n\n\n
                      (4) \u6dfb\u52a0\u7ea6\u675f<\/strong><\/h3>\n\n\n\n
                      ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);<\/code><\/pre>\n\n\n\n
                      \n\n\n\n
                      9. \u603b\u7ed3<\/strong><\/h2>\n\n\n\n
                        \n
                      • \u4f7f\u7528 CREATE TABLE<\/code> \u521b\u5efa\u8868\uff0c\u5b9a\u4e49\u5217\u540d\u3001\u6570\u636e\u7c7b\u578b\u548c\u7ea6\u675f\u3002<\/li>\n\n\n\n
                      • PRIMARY KEY<\/code> \u4f5c\u4e3a\u552f\u4e00\u6807\u8bc6\uff0cFOREIGN KEY<\/code> \u7528\u4e8e\u5173\u8054\u5176\u4ed6\u8868\u3002<\/li>\n\n\n\n
                      • NOT NULL<\/code>, UNIQUE<\/code>, CHECK<\/code> \u4fdd\u969c\u6570\u636e\u5b8c\u6574\u6027\u3002<\/li>\n\n\n\n
                      • DEFAULT<\/code> \u8bbe\u7f6e\u9ed8\u8ba4\u503c\uff0cON DELETE CASCADE<\/code> \u7ef4\u62a4\u8868\u5173\u7cfb\u3002<\/li>\n\n\n\n
                      • ALTER TABLE<\/code> \u7528\u4e8e\u4fee\u6539\u8868\u7ed3\u6784\uff0cDROP TABLE<\/code> \u7528\u4e8e\u5220\u9664\u8868\u3002<\/li>\n<\/ul>\n\n\n\n
                        \u66f4\u591a\u8be6\u7ec6\u5185\u5bb9\u8bf7\u5173\u6ce8\u5176\u4ed6\u76f8\u5173\u6587\u7ae0\uff01<\/p>\n","protected":false},"excerpt":{"rendered":"
                        \u5728 PostgreSQL \u4e2d\uff0cCREATE TABLE \u8bed\u53e5\u7528\u4e8e\u521b\u5efa\u65b0\u7684\u8868\u683c\uff08\u5173\u7cfb\uff09\uff0c\u5b9a\u4e49\u8868\u7684\u5217\u53ca\u5176\u6570\u636e\u7c7b\u578b\u3001 […]<\/p>\n","protected":false},"author":1,"featured_media":2598,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[78],"tags":[],"class_list":["post-2597","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql"],"_links":{"self":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts\/2597","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/comments?post=2597"}],"version-history":[{"count":1,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts\/2597\/revisions"}],"predecessor-version":[{"id":2599,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/posts\/2597\/revisions\/2599"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/media\/2598"}],"wp:attachment":[{"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/media?parent=2597"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/categories?post=2597"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.laixuexila.com\/index.php\/wp-json\/wp\/v2\/tags?post=2597"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}