Oracle、SQL Server、MySQL 常用 SQL 語句對照速查表

CREATE TABLE _table_name (
    column1 VARCHAR2(10) PRIMARY KEY,
    column2 NUMBER(5) NOT NULL,
    memo VARCHAR2(100)
);
COMMENT ON COLUMN _table_name.column1 IS '這是註解';

CREATE TABLE _table_name (
    column1 VARCHAR(10) PRIMARY KEY,
    column2 INT NOT NULL,
    memo VARCHAR(100)
);

CREATE TABLE `_table_name` (
    `column1` VARCHAR(32) PRIMARY KEY COMMENT '註解',
    `column2` VARCHAR(30) NOT NULL COMMENT '註解'
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

ALTER TABLE t ADD CONSTRAINT pk_name
PRIMARY KEY (col) USING INDEX TABLESPACE URMSPK;

ALTER TABLE t ADD CONSTRAINT pk_name
PRIMARY KEY CLUSTERED (col)
WITH (PAD_INDEX=OFF, ...) ON URMSPK;

ALTER TABLE t ADD PRIMARY KEY pk_name (col_list);

-- 一般
CREATE INDEX idx_name$cl2 ON t (col1, col2 DESC) TABLESPACE URMSIDX;
-- 唯一
CREATE UNIQUE INDEX idx_name ON t (col1, col2);

CREATE UNIQUE NONCLUSTERED INDEX idx_name ON t (col1, col2 DESC)
WITH (...) ON [URMSIDX];
-- 非唯一移除 UNIQUE 關鍵字

-- 一般
ALTER TABLE t ADD INDEX idx_name (col1, col2 DESC);
-- 唯一
ALTER TABLE t ADD UNIQUE idx_name (col_list);

INSERT INTO t (col_list) VALUES ('val_list');

INSERT INTO t (`col_list`) VALUES ('val_list'), ('val_list2');  -- 多列一次插入