柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) SQL筆記-建索引
柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) SQL筆記-建索引
一、說(shuō)明
Oracle:
-- 創(chuàng)建單列索引
CREATE INDEX index_name ON table_name(column_name);
-- 創(chuàng)建聯(lián)合索引
CREATE INDEX index_name ON table_name(column1, column2, column3);
MySQL:
-- 創(chuàng)建單列索引
ALTER TABLE table_name ADD INDEX index_name(column_name);
-- 創(chuàng)建聯(lián)合索引
ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);
SQL Server:
-- 創(chuàng)建單列索引
CREATE INDEX index_name ON table_name(column_name);
-- 創(chuàng)建聯(lián)合索引
CREATE INDEX index_name ON table_name(column1, column2, column3);
PostgreSQL:
-- 創(chuàng)建單列索引
CREATE INDEX index_name ON table_name(column_name);
-- 創(chuàng)建聯(lián)合索引
CREATE INDEX index_name ON table_name(column1, column2, column3);
二、示例
Oracle: 建表語(yǔ)句：
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);
數(shù)據(jù)插入語(yǔ)句：
-- 插入 table1 數(shù)據(jù)
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 數(shù)據(jù)
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);
建立索引：
-- 對(duì) table1 的 id 列建立索引
CREATE INDEX idx_table1_id ON table1(id);
-- 對(duì) table2 的 table1_id 列建立索引
CREATE INDEX idx_table2_table1_id ON table2(table1_id);
MySQL: 建表語(yǔ)句：
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);
數(shù)據(jù)插入語(yǔ)句：
-- 插入 table1 數(shù)據(jù)
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 數(shù)據(jù)
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);
建立索引：
-- 對(duì) table1 的 id 列建立索引
ALTER TABLE table1 ADD INDEX idx_table1_id(id);
-- 對(duì) table2 的 table1_id 列建立索引
ALTER TABLE table2 ADD INDEX idx_table2_table1_id(table1_id);
SQL Server: 建表語(yǔ)句：
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);
數(shù)據(jù)插入語(yǔ)句：
-- 插入 table1 數(shù)據(jù)
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 數(shù)據(jù)
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);
建立索引：
-- 對(duì) table1 的 id 列建立索引
CREATE INDEX idx_table1_id ON table1(id);
-- 對(duì) table2 的 table1_id 列建立索引
CREATE INDEX idx_table2_table1_id ON table2(table1_id);
PostgreSQL: 建表語(yǔ)句：
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);
數(shù)據(jù)插入語(yǔ)句：
-- 插入 table1 數(shù)據(jù)
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 數(shù)據(jù)
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);
建立索引：
-- 對(duì) table1 的 id 列建立索引
CREATE INDEX idx_table1_id ON table1(id);
-- 對(duì) table2 的 table1_id 列建立索引
CREATE INDEX idx_table2_table1_id ON table2(table1_id);
柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) SQL筆記-建索引
相關(guān)閱讀
本文內(nèi)容根據(jù)網(wǎng)絡(luò)資料整理，出于傳遞更多信息之目的，不代表金鑰匙跨境贊同其觀點(diǎn)和立場(chǎng)。
轉(zhuǎn)載請(qǐng)注明，如有侵權(quán)，聯(lián)系刪除。
Fruugo海外環(huán)球購(gòu)
