SQL ALTER TABLE  is used to add, delete, or modify columns in a table .

Add columns

Syntax

ALTER TABLE table_name ADD column_name datatype

Example

mysql> ALTER TABLE orders  ADD price int ;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

Delete columns

Syntax

ALTER TABLE table_name DROP COLUMN column_name

Example

mysql> ALTER TABLE orders DROP COLUMN price;
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

Modify columns

Syntax

ALTER TABLE table_name CHANGE column_name column_name datatype

Example

mysql> ALTER TABLE  orders CHANGE  items  items VARCHAR( 255 );
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

SQL  TRUNCATE  can be used to delete records in table .

Example

mysql> TRUNCATE TABLE orders;
Query OK, 0 rows affected (0.00 sec)

SQL DROP DATABASE  can be used to delete databases .

Example

mysql> DROP DATABASE database_name;
Query OK, 1 row affected (0.54 sec)

SQL DROP TABLE  can be used to delete tables .

Example

mysql> DROP TABLE user2;
Query OK, 0 rows affected (0.01 sec)

SQL DROP INDEX can be used to delete indexes

Example

mysql> ALTER TABLE users DROP INDEX user_index;
Query OK, 5 rows affected (0.10 sec)
Records: 5  Duplicates: 0  Warnings: 0

SQL DROP can be used to delete table,indexes and databases

Below given are the SQL DROP dtatements

SQL DROP INDEX – Delete indexes

SQL DROP TABLE – Delete tables

SQL DROP DATABASE – Delete databases

SQL CREATE INDEX  is used to create indexes in tables.

It let the database application to find data fast  .

Example

mysql> CREATE INDEX user_index ON users (uid);
Query OK, 5 rows affected (0.12 sec)
Records: 5  Duplicates: 0  Warnings: 0

SQL DEFAULT is used to insert a default value into a column.

The default value will be added to all new records, if no other value is given.

Example

CREATE TABLE users(uid int NOT NULL,name varchar(255) NOT NULL DEFAULT ‘phpcode’)

SQL CHECK  is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

Example

CREATE TABLE users(uid int NOT NULL,name varchar(255) NOT NULL,CHECK (uid>0))

SQL FOREIGN KEYin one table points to the primary key of another table .

Table – users

+—-+——+
| uid | name |
+—-+——+
|  1 | AAAA |
|  2 | BBBB |
|  3 | CCCC |
|  4 | DDDD |
|  5 | EEEE |
+—-+——+

Table  – orders

+—–+—–+——–+
| oid | uid | items  |
+—–+—–+——–+
|   1 |   1 | Pen    |
|   2 |   2 | Watch  |
|   3 |   3 | shoe   |
|   4 |   4 | mobile |
+—–+—–+——–+

Here the field uid in the table orders points to the column uid in users .

That is  uid in the orders is the foreign key and that in users is primary key

FOREIGN KEY constraint prevent any action that may destroy the relation between the tables.

It also prevent invalid data and make sure that the data to be inserted should be one of the primary key value in another

Example

CREATE TABLE IF NOT EXISTS `orders` (
`oid` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`items` varchar(255) NOT NULL,
PRIMARY KEY (`oid`),
FOREIGN KEY (uid) REFERENCES users(uid)
)