SQL笔记-建索引

一、说明

• Oracle:

-- 创建单列索引
CREATE INDEX index_name ON table_name(column_name);

-- 创建联合索引
CREATE INDEX index_name ON table_name(column1, column2, column3);

• MySQL:

-- 创建单列索引
ALTER TABLE table_name ADD INDEX index_name(column_name);

-- 创建联合索引
ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);

• SQL Server:

-- 创建单列索引
CREATE INDEX index_name ON table_name(column_name);

-- 创建联合索引
CREATE INDEX index_name ON table_name(column1, column2, column3);

• PostgreSQL:

-- 创建单列索引
CREATE INDEX index_name ON table_name(column_name);

-- 创建联合索引
CREATE INDEX index_name ON table_name(column1, column2, column3);

二、示例

• Oracle:
  建表语句：

CREATE TABLE table1(
   id NUMBER(10) PRIMARY KEY,
   name VARCHAR2(50),
   age NUMBER(3),
   gender VARCHAR2(10),
   address VARCHAR2(100)
);

CREATE TABLE table2(
   id NUMBER(10) PRIMARY KEY,
   table1_id NUMBER(10),
   subject VARCHAR2(50),
   score NUMBER(3)
);

ALTER TABLE table2 ADD FOREIGN KEY (table1_id) REFERENCES table1(id);

数据插入语句：

-- 插入 table1 数据
INSERT INTO table1(id, name, age, gender, address) VALUES(1, 'John', 25, 'Male', 'New York');
INSERT INTO table1(id, name, age, gender, address) VALUES(2, 'Mary', 30, 'Female', 'Los Angeles');

-- 插入 table2 数据
INSERT INTO table2(id, table1_id, subject, score) VALUES(1, 1, 'Math', 80);
INSERT INTO table2(id, table1_id, subject, score) VALUES(2, 1, 'English', 90);
INSERT INTO table2(id, table1_id, subject, score) VALUES(3, 2, 'Math', 85);
INSERT INTO table2(id, table1_id, subject, score) VALUES(4, 2, 'English', 95);

建立索引：

-- 对 table1 的 id 列建立索引
CREATE INDEX idx_table1_id ON table1(id);

-- 对 table2 的 table1_id 列建立索引
CREATE INDEX idx_table2_table1_id ON table2(table1_id);

• MySQL:
  建表语句：

CREATE TABLE table1(
   id INT(10) PRIMARY KEY,
   name VARCHAR(50),
   age INT(3),
   gender VARCHAR(10),
   address VARCHAR(100)
);

CREATE TABLE table2(
   id INT(10) PRIMARY KEY,
   table1_id INT(10),
   subject VARCHAR(50),
   score INT(3)
);

ALTER TABLE table2 ADD FOREIGN KEY (table1_id) REFERENCES table1(id);

数据插入语句：

-- 插入 table1 数据
INSERT INTO table1(id, name, age, gender, address) VALUES(1, 'John', 25, 'Male', 'New York');
INSERT INTO table1(id, name, age, gender, address) VALUES(2, 'Mary', 30, 'Female', 'Los Angeles');

-- 插入 table2 数据
INSERT INTO table2(id, table1_id, subject, score) VALUES(1, 1, 'Math', 80);
INSERT INTO table2(id, table1_id, subject, score) VALUES(2, 1, 'English', 90);
INSERT INTO table2(id, table1_id, subject, score) VALUES(3, 2, 'Math', 85);
INSERT INTO table2(id, table1_id, subject, score) VALUES(4, 2, 'English', 95);

建立索引：

-- 对 table1 的 id 列建立索引
ALTER TABLE table1 ADD INDEX idx_table1_id(id);

-- 对 table2 的 table1_id 列建立索引
ALTER TABLE table2 ADD INDEX idx_table2_table1_id(table1_id);

• SQL Server:
  建表语句：

CREATE TABLE table1(
   id INT PRIMARY KEY,
   name VARCHAR(50),
   age INT,
   gender VARCHAR(10),
   address VARCHAR(100)
);

CREATE TABLE table2(
   id INT PRIMARY KEY,
   table1_id INT,
   subject VARCHAR(50),
   score INT
);

ALTER TABLE table2 ADD FOREIGN KEY (table1_id) REFERENCES table1(id);

数据插入语句：

-- 插入 table1 数据
INSERT INTO table1(id, name, age, gender, address) VALUES(1, 'John', 25, 'Male', 'New York');
INSERT INTO table1(id, name, age, gender, address) VALUES(2, 'Mary', 30, 'Female', 'Los Angeles');

-- 插入 table2 数据
INSERT INTO table2(id, table1_id, subject, score) VALUES(1, 1, 'Math', 80);
INSERT INTO table2(id, table1_id, subject, score) VALUES(2, 1, 'English', 90);
INSERT INTO table2(id, table1_id, subject, score) VALUES(3, 2, 'Math', 85);
INSERT INTO table2(id, table1_id, subject, score) VALUES(4, 2, 'English', 95);

建立索引：

-- 对 table1 的 id 列建立索引
CREATE INDEX idx_table1_id ON table1(id);

-- 对 table2 的 table1_id 列建立索引
CREATE INDEX idx_table2_table1_id ON table2(table1_id);

• PostgreSQL:
  建表语句：

CREATE TABLE table1(
   id SERIAL PRIMARY KEY,
   name VARCHAR(50),
   age INT,
   gender VARCHAR(10),
   address VARCHAR(100)
);

CREATE TABLE table2(
   id SERIAL PRIMARY KEY,
   table1_id INT,
   subject VARCHAR(50),
   score INT
);

ALTER TABLE table2 ADD FOREIGN KEY (table1_id) REFERENCES table1(id);

数据插入语句：

-- 插入 table1 数据
INSERT INTO table1(name, age, gender, address) VALUES('John', 25, 'Male', 'New York');
INSERT INTO table1(name, age, gender, address) VALUES('Mary', 30, 'Female', 'Los Angeles');

-- 插入 table2 数据
INSERT INTO table2(table1_id, subject, score) VALUES(1, 'Math', 80);
INSERT INTO table2(table1_id, subject, score) VALUES(1, 'English', 90);
INSERT INTO table2(table1_id, subject, score) VALUES(2, 'Math', 85);
INSERT INTO table2(table1_id, subject, score) VALUES(2, 'English', 95);

建立索引：

-- 对 table1 的 id 列建立索引
CREATE INDEX idx_table1_id ON table1(id);

-- 对 table2 的 table1_id 列建立索引
CREATE INDEX idx_table2_table1_id ON table2(table1_id);