SQL/MySQL/Table Index/Alter Table — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 13:17, 26 мая 2010
Содержание
- 1 Altering and Dropping Tables
- 2 Altering Database Tables: Add a Column
- 3 Altering Database Tables: Add More Columns
- 4 Altering Database Tables: Drop a Column
- 5 Altering Database Tables: Drop Unique Constraint and Add Primary Key
- 6 Alter table: add primary key, foreign key and add column
- 7 Alter table: add unique
- 8 Alter table: drop primary and foreign key
Altering and Dropping Tables
<source lang="sql">
Drop table Stock; CREATE TABLE Stock (
ID SMALLINT
); Describe Stock; ALTER TABLE Stock ADD COLUMN Quantity SMALLINT UNSIGNED NOT NULL, MODIFY ID SMALLINT UNSIGNED NOT NULL, ADD PRIMARY KEY (ID); Describe Stock; ALTER TABLE Stock DROP COLUMN Quantity, DROP PRIMARY KEY; Describe Stock;
</source>
Altering Database Tables: Add a Column
<source lang="sql">
/* mysql> CREATE TABLE Employee (
-> Name VARCHAR(50) PRIMARY KEY NOT NULL, -> PhoneNo VARCHAR(15) DEFAULT "Unknown Phone", -> Age INT CHECK (Age BETWEEN 20 and 30));
Query OK, 0 rows affected (0.09 sec) mysql> Describe Employee; +---------+-------------+------+-----+---------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------------+-------+ | Name | varchar(50) | | PRI | | | | PhoneNo | varchar(15) | YES | | Unknown Phone | | | Age | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------------+-------+ 3 rows in set (0.01 sec)
mysql> Select * from Employee; +----------+---------------+------+ | Name | PhoneNo | Age | +----------+---------------+------+ | John Doe | Unknown Phone | 31 | +----------+---------------+------+ 1 row in set (0.00 sec) mysql> ALTER TABLE Employee ADD Address VARCHAR(50); Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> Describe Employee; +---------+-------------+------+-----+---------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------------+-------+ | Name | varchar(50) | | PRI | | | | PhoneNo | varchar(15) | YES | | Unknown Phone | | | Age | int(11) | YES | | NULL | | | Address | varchar(50) | YES | | NULL | | +---------+-------------+------+-----+---------------+-------+ 4 rows in set (0.00 sec) mysql> Select * from Employee; +----------+---------------+------+---------+ | Name | PhoneNo | Age | Address | +----------+---------------+------+---------+ | John Doe | Unknown Phone | 31 | NULL | +----------+---------------+------+---------+ 1 row in set (0.00 sec)
- /
Drop TABLE Employee;
CREATE TABLE Employee (
Name VARCHAR(50) PRIMARY KEY NOT NULL, PhoneNo VARCHAR(15) DEFAULT "Unknown Phone", Age INT CHECK (Age BETWEEN 20 and 30));
Describe Employee; INSERT INTO Employee (Name, Phone, Age) VALUES ("Joe Wang", "666 2323", 26); INSERT INTO Employee (Name, Age) VALUES ("John Doe", 31); Select * from Employee;
ALTER TABLE Employee ADD Address VARCHAR(50); Describe Employee; Select * from Employee;
</source>
Altering Database Tables: Add More Columns
<source lang="sql">
/* mysql> CREATE TABLE Employee (
-> Name VARCHAR(50) PRIMARY KEY NOT NULL, -> PhoneNo VARCHAR(15) DEFAULT "Unknown Phone", -> Age INT CHECK (Age BETWEEN 20 and 30));
Query OK, 0 rows affected (0.12 sec) mysql> Describe Employee; +---------+-------------+------+-----+---------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------------+-------+ | Name | varchar(50) | | PRI | | | | PhoneNo | varchar(15) | YES | | Unknown Phone | | | Age | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------------+-------+ 3 rows in set (0.00 sec)
mysql> Select * from Employee; +----------+---------------+------+ | Name | PhoneNo | Age | +----------+---------------+------+ | John Doe | Unknown Phone | 31 | +----------+---------------+------+ 1 row in set (0.00 sec) mysql> ALTER TABLE Employee ADD (EMail VARCHAR(25), ICQ VARCHAR(15)); Query OK, 1 row affected (0.08 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> Describe Employee; +---------+-------------+------+-----+---------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------------+-------+ | Name | varchar(50) | | PRI | | | | PhoneNo | varchar(15) | YES | | Unknown Phone | | | Age | int(11) | YES | | NULL | | | EMail | varchar(25) | YES | | NULL | | | ICQ | varchar(15) | YES | | NULL | | +---------+-------------+------+-----+---------------+-------+ 5 rows in set (0.00 sec) mysql> Select * from Employee; +----------+---------------+------+-------+------+ | Name | PhoneNo | Age | EMail | ICQ | +----------+---------------+------+-------+------+ | John Doe | Unknown Phone | 31 | NULL | NULL | +----------+---------------+------+-------+------+ 1 row in set (0.01 sec)
- /
Drop TABLE Employee;
CREATE TABLE Employee (
Name VARCHAR(50) PRIMARY KEY NOT NULL, PhoneNo VARCHAR(15) DEFAULT "Unknown Phone", Age INT CHECK (Age BETWEEN 20 and 30));
Describe Employee; INSERT INTO Employee (Name, Phone, Age) VALUES ("Joe Wang", "666 2323", 26); INSERT INTO Employee (Name, Age) VALUES ("John Doe", 31); Select * from Employee;
ALTER TABLE Employee ADD (EMail VARCHAR(25), ICQ VARCHAR(15)); Describe Employee; Select * from Employee;
</source>
Altering Database Tables: Drop a Column
<source lang="sql">
/* mysql> CREATE TABLE Employee (
-> Name VARCHAR(50) PRIMARY KEY NOT NULL, -> PhoneNo VARCHAR(15) DEFAULT "Unknown Phone", -> Age INT CHECK (Age BETWEEN 20 and 30));
Query OK, 0 rows affected (0.14 sec) mysql> Describe Employee; +---------+-------------+------+-----+---------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------------+-------+ | Name | varchar(50) | | PRI | | | | PhoneNo | varchar(15) | YES | | Unknown Phone | | | Age | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------------+-------+ 3 rows in set (0.01 sec)
mysql> Select * from Employee; +----------+---------------+------+ | Name | PhoneNo | Age | +----------+---------------+------+ | John Doe | Unknown Phone | 31 | +----------+---------------+------+ 1 row in set (0.00 sec) mysql> ALTER TABLE Employee DROP COLUMN PhoneNo; Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> Describe Employee; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Name | varchar(50) | | PRI | | | | Age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> Select * from Employee; +----------+------+ | Name | Age | +----------+------+ | John Doe | 31 | +----------+------+ 1 row in set (0.00 sec)
- /
Drop TABLE Employee;
CREATE TABLE Employee (
Name VARCHAR(50) PRIMARY KEY NOT NULL, PhoneNo VARCHAR(15) DEFAULT "Unknown Phone", Age INT CHECK (Age BETWEEN 20 and 30));
Describe Employee; INSERT INTO Employee (Name, Phone, Age) VALUES ("Joe Wang", "666 2323", 26); INSERT INTO Employee (Name, Age) VALUES ("John Doe", 31); Select * from Employee;
ALTER TABLE Employee DROP COLUMN PhoneNo; Describe Employee; Select * from Employee;
</source>
Altering Database Tables: Drop Unique Constraint and Add Primary Key
<source lang="sql">
/* mysql> CREATE TABLE Employee (
-> Name VARCHAR(50) PRIMARY KEY NOT NULL, -> PhoneNo VARCHAR(15) DEFAULT "Unknown Phone", -> Age INT CHECK (Age BETWEEN 20 and 30));
Query OK, 0 rows affected (0.04 sec) mysql> Describe Employee; +---------+-------------+------+-----+---------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------------+-------+ | Name | varchar(50) | | PRI | | | | PhoneNo | varchar(15) | YES | | Unknown Phone | | | Age | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------------+-------+ 3 rows in set (0.01 sec) mysql> Select * from Employee; +----------+---------------+------+ | Name | PhoneNo | Age | +----------+---------------+------+ | John Doe | Unknown Phone | 31 | +----------+---------------+------+ 1 row in set (0.00 sec) mysql> ALTER TABLE Employee DROP INDEX Name; ERROR 1091 (42000): Can"t DROP "Name"; check that column/key exists mysql> ALTER TABLE Employee ADD PRIMARY KEY (Name); ERROR 1068 (42000): Multiple primary key defined mysql> Describe Employee; +---------+-------------+------+-----+---------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------------+-------+ | Name | varchar(50) | | PRI | | | | PhoneNo | varchar(15) | YES | | Unknown Phone | | | Age | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------------+-------+ 3 rows in set (0.00 sec)
- /
Drop TABLE Employee;
CREATE TABLE Employee (
Name VARCHAR(50) PRIMARY KEY NOT NULL, PhoneNo VARCHAR(15) DEFAULT "Unknown Phone", Age INT CHECK (Age BETWEEN 20 and 30));
Describe Employee; INSERT INTO Employee (Name, Phone, Age) VALUES ("Joe Wang", "666 2323", 26); INSERT INTO Employee (Name, Age) VALUES ("John Doe", 31); Select * from Employee;
ALTER TABLE Employee DROP INDEX Name;
ALTER TABLE Employee ADD PRIMARY KEY (Name);
Describe Employee;
</source>
Alter table: add primary key, foreign key and add column
<source lang="sql">
Drop table Books; CREATE TABLE Books (
ID SMALLINT NOT NULL, Name VARCHAR(40) NOT NULL, PubID SMALLINT NOT NULL DEFAULT "Unknown"
) ENGINE=INNODB; Describe Books;
ALTER TABLE Books ADD PRIMARY KEY (BookID), ADD CONSTRAINT fk_1 FOREIGN KEY (PubID) REFERENCES Publishers (PubID), ADD COLUMN Format ENUM("paperback", "hardcover") NOT NULL AFTER Name; Describe Books;
</source>
Alter table: add unique
<source lang="sql">
/* mysql> Drop table Product; mysql> CREATE TABLE Product
-> ( -> ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY, -> ModelID SMALLINT UNSIGNED NOT NULL -> );
Query OK, 0 rows affected (0.05 sec) mysql> ALTER TABLE Product
-> ADD UNIQUE (ID, ModelID);
Query OK, 0 rows affected (0.15 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 | | | 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
); ALTER TABLE Product ADD UNIQUE (ID, ModelID); Describe Product;
</source>
Alter table: drop primary and foreign key
<source lang="sql">
/* mysql> Drop table Books; Query OK, 0 rows affected (0.03 sec) mysql> CREATE TABLE Books
-> ( -> ID SMALLINT NOT NULL, -> Name VARCHAR(40) NOT NULL, -> PubID SMALLINT NOT NULL DEFAULT "Unknown" -> ) -> ENGINE=INNODB;
Query OK, 0 rows affected (0.17 sec) mysql> Describe Books; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ID | smallint(6) | | | 0 | | | Name | varchar(40) | | | | | | PubID | smallint(6) | | | 0 | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> ALTER TABLE Books
-> DROP PRIMARY KEY, -> DROP FOREIGN KEY fk_1, -> DROP COLUMN Format;
ERROR 1091 (42000): Can"t DROP "PRIMARY"; check that column/key exists mysql> Describe Books; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ID | smallint(6) | | | 0 | | | Name | varchar(40) | | | | | | PubID | smallint(6) | | | 0 | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
- /
Drop table Books; CREATE TABLE Books (
ID SMALLINT NOT NULL, Name VARCHAR(40) NOT NULL, PubID SMALLINT NOT NULL DEFAULT "Unknown"
) ENGINE=INNODB; Describe Books;
ALTER TABLE Books DROP PRIMARY KEY, DROP FOREIGN KEY fk_1, DROP COLUMN Format; Describe Books;
</source>