不同的数据库的建表语句

1、SQL Server

-- 创建学生表
CREATE TABLE students (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    sno VARCHAR(50),
    enrollment_date DATE
);

2、Oracle

-- DROP TABLE BU_TREE_NODE
CREATE TABLE BU_TREE_NODE(
  ID 			VARCHAR2(32) DEFAULT SYS_GUID() PRIMARY KEY,
  TNAME 		VARCHAR2(100),
  PARENT_ID 	VARCHAR2(255),
  DATA_CONTENT 	VARCHAR2(255),
 
  STATUS            VARCHAR2(10),
  REMARKS           VARCHAR2(255),
  RES_ONE           VARCHAR2(100),
  RES_TWO           VARCHAR2(100),
  RES_THREE         VARCHAR2(100),
  RES_FOUR          VARCHAR2(100),
  CREATE_BY         VARCHAR2(64),     
  CREATE_TIME 	    DATE,             
  UPDATE_BY         VARCHAR2(64),
  UPDATE_TIME       DATE
);
-- 建表字段注释
COMMENT ON TABLE  BU_TREE_NODE                  IS '树结构表';
COMMENT ON COLUMN BU_TREE_NODE.ID               IS '表主键ID';
COMMENT ON COLUMN BU_TREE_NODE.TNAME            IS '名称';
COMMENT ON COLUMN BU_TREE_NODE.PARENT_ID        IS '父ID';
COMMENT ON COLUMN BU_TREE_NODE.DATA_CONTENT     IS '数据内容';
 
COMMENT ON COLUMN BU_TREE_NODE.STATUS           IS '状态';					      
COMMENT ON COLUMN BU_TREE_NODE.REMARKS          IS '备注';
COMMENT ON COLUMN BU_TREE_NODE.RES_ONE          IS '预留字段一';
COMMENT ON COLUMN BU_TREE_NODE.RES_TWO          IS '预留字段二';
COMMENT ON COLUMN BU_TREE_NODE.RES_THREE        IS '预留字段三';
COMMENT ON COLUMN BU_TREE_NODE.RES_FOUR         IS '预留字段四';
COMMENT ON COLUMN BU_TREE_NODE.CREATE_BY        IS '创建者';
COMMENT ON COLUMN BU_TREE_NODE.CREATE_TIME      IS '创建时间';
COMMENT ON COLUMN BU_TREE_NODE.UPDATE_BY        IS '更新者';
COMMENT ON COLUMN BU_TREE_NODE.UPDATE_TIME      IS '更新时间';
/

当然主键也可以使用序列去自增。

3、MySQL

-- mysql建表语句
DROP TABLE IF EXISTS project_info;
CREATE TABLE IF NOT EXISTS project_info (
    id            BIGINT(32) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '表ID',
    name          VARCHAR(100) COMMENT '项目名称',
    info          VARCHAR(100) COMMENT '项目信息',

    status        VARCHAR(10) COMMENT '状态',
    remarks       VARCHAR(255) COMMENT '备注',
    create_by     VARCHAR(50) COMMENT '创建人',
    create_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_by     VARCHAR(50) COMMENT '更新人',
    update_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    res_one       VARCHAR(255) COMMENT '预留字段一',
    res_two       VARCHAR(255) COMMENT '预留字段二'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '项目信息表';

主键使用自动递增的方式。

4、PostgreSQL

-- pgsql建表语句
-- DROP SEQUENCE IF EXISTS public.news_info_seq;
CREATE SEQUENCE news_info_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- DROP TABLE IF EXISTS public.news_info;
-- 建表时指定了COLLATE pg_catalog."default"，意味着表中的字符串列将使用PostgreSQL的默认排序规则进行字符比较和排序。
CREATE TABLE IF NOT EXISTS public.news_info
(
    id bigint NOT NULL DEFAULT nextval('news_info_seq'::regclass),
    title character varying(100) COLLATE pg_catalog."default",
    content text COLLATE pg_catalog."default",
    img_url text COLLATE pg_catalog."default",
    vid_url character varying(500) COLLATE pg_catalog."default",

    remarks character varying(500) COLLATE pg_catalog."default",
    status character varying(10) COLLATE pg_catalog."default" DEFAULT 0,
    create_time TIMESTAMP without time zone CURRENT_TIMESTAMP,
    create_by character varying(60) COLLATE pg_catalog."default",
    update_time TIMESTAMP without time zone CURRENT_TIMESTAMP,
    update_by character varying(60) COLLATE pg_catalog."default",
    res_one character varying(500) COLLATE pg_catalog."default",
    res_two character varying(100) COLLATE pg_catalog."default",
    res_three character varying(100) COLLATE pg_catalog."default",
    res_four character varying(100) COLLATE pg_catalog."default",
    CONSTRAINT news_info_pkey PRIMARY KEY (id)
)
WITH (OIDS = FALSE)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.news_info OWNER to postgres;
-- Tbale Annotation
COMMENT ON TABLE public.news_info IS '新闻信息表';
COMMENT ON COLUMN public.news_info.id IS '表主键ID';
COMMENT ON COLUMN public.news_info.title IS '新闻标题';
COMMENT ON COLUMN public.news_info.content IS '新闻内容';
COMMENT ON COLUMN public.news_info.img_url IS '图片地址';
COMMENT ON COLUMN public.news_info.vid_url IS '视频地址';

COMMENT ON COLUMN public.news_info.remarks IS '备注';
COMMENT ON COLUMN public.news_info.status IS '状态';
COMMENT ON COLUMN public.news_info.create_time IS '创建时间';
COMMENT ON COLUMN public.news_info.create_by IS '创建人';
COMMENT ON COLUMN public.news_info.update_time IS '更新时间';
COMMENT ON COLUMN public.news_info.update_by IS '更新人';
COMMENT ON COLUMN public.news_info.res_one IS '预留字段一';
COMMENT ON COLUMN public.news_info.res_two IS '预留字段二';
COMMENT ON COLUMN public.news_info.res_three IS '预留字段三';
COMMENT ON COLUMN public.news_info.res_four  IS '预留字段四';

主键使用序列ID递增。

5、SQLite

--SQLite建表语句
-- 字段类型：INTEGER: 无符号整型值。REAL: 浮点值。TEXT: 文本字符串，存储使用的编码方式为UTF-8、UTF-16BE、UTF-16LE。BLOB: 存储Blob数据，该类型数据和输入数据完全相同
CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age 		 INTEGER,
	status 		 TEXT,
	remarks      TEXT,
    create_by    TEXT,
    create_time  TEXT,
    update_by    TEXT,
    update_time  TEXT,
    res_one      TEXT,
    res_two  	 TEXT
);

达梦数据库

-- 达梦数据库
DROP TABLE IF EXISTS STUDENT_INFO;
CREATE TABLE STUDENT_INFO
(
    ID INT IDENTITY(1, 1) NOT NULL,
    NAME       VARCHAR(255)  COMMENT '姓名',
    AGE        VARCHAR(10)  COMMENT '年龄',
    SNO        VARCHAR(10)  COMMENT '学号',
    ENROLLMENT_DATE  VARCHAR(255)  COMMENT '入学日期',

    CREAT_TIME 	  DATE DEFAULT (SYSDATE)  COMMENT '创建时间',
    UPDATE_TIME   VARCHAR(255) COMMENT '更新时间',
    REMARKS       VARCHAR(255)  COMMENT '备注',
    RES_ONE       VARCHAR(255)  COMMENT '预留一',
    RES_TWO       VARCHAR(255)  COMMENT '预留二',
    CLUSTER PRIMARY KEY("ID"));

comment on table SYSDBA.STUDENT_INFO is '学生信息表'