в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:17:26Z

1 версия

Новая страница

==Create index on a column==



<source lang="sql">
/*
mysql> Drop table Product;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Product
-> (
-> ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
-> ModelID SMALLINT UNSIGNED NOT NULL
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE INDEX index_1 ON Product (ModelID);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> Describe Product;
+---------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| ID | smallint(5) unsigned | | PRI | 0 | |
| ModelID | smallint(5) unsigned | | MUL | 0 | |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

*/
Drop table Product;
CREATE TABLE Product
(
ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
ModelID SMALLINT UNSIGNED NOT NULL
);
CREATE INDEX index_1 ON Product (ModelID);
Describe Product;

</source>

==Create table: alter table to drop index==



<source lang="sql">
/*
mysql> Drop table Product;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE Product
-> (
-> ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
-> ModelID SMALLINT UNSIGNED NOT NULL,
-> UNIQUE unique_1 (ID, ModelID)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> Describe Product;
+---------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| ID | smallint(5) unsigned | | PRI | 0 | |
| ModelID | smallint(5) unsigned | | | 0 | |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE Orders
-> DROP INDEX unique_1;
ERROR 1091 (42000): Can"t DROP "unique_1"; check that column/key exists
mysql> Describe Product;
+---------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| ID | smallint(5) unsigned | | PRI | 0 | |
| ModelID | smallint(5) unsigned | | | 0 | |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

*/
Drop table Product;
CREATE TABLE Product
(
ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
ModelID SMALLINT UNSIGNED NOT NULL,
UNIQUE unique_1 (ID, ModelID)
);
Describe Product;
ALTER TABLE Orders
DROP INDEX unique_1;
Describe Product;

</source>

==Create table: Make index==



<source lang="sql">
/*
mysql> Drop table Product;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Product
-> (
-> ID SMALLINT UNSIGNED NOT NULL,
-> ModelID SMALLINT UNSIGNED NOT NULL,
-> PRIMARY KEY (ID),
-> INDEX (ModelID)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> Describe Product;
+---------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| ID | smallint(5) unsigned | | PRI | 0 | |
| ModelID | smallint(5) unsigned | | MUL | 0 | |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

*/
Drop table Product;
CREATE TABLE Product
(
ID SMALLINT UNSIGNED NOT NULL,
ModelID SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (ID),
INDEX (ModelID)
);
Describe Product;

</source>

==Creating a Table with an Index==



<source lang="sql">

Drop table CD;
CREATE TABLE CD
(
ID SMALLINT UNSIGNED NOT NULL,
Name VARCHAR(40) NOT NULL,
INDEX (Name)
);
Describe CD;

</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL/MySQL/Table Index/Index - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия