в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:17:25Z

1 версия

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

==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>

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

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

в 13:46, 26 мая 2010

Admin: 1 версия