SQL AUTO INCREMENT allows a unique number to be generated when a new record is inserted into a table.
It usually used with primary keys
Example
CREATE TABLE IF NOT EXISTS `users` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`uid`)
)
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))