Tuesday, November 6, 2012

MySql prompt IV- Data Definition (DDL)

DDL-Data Definition languages comprises of scripting
  ALTER DATABASE
  ALTER EVENT
  ALTER FUNCTION
  ALTER LOGFILE GROUP
  ALTER PROCEDURE
  ALTER SERVER
  ALTER TABLE
  ALTER TABLESPACE
  ALTER VIEW
  CONSTRAINT
  CREATE DATABASE

  CREATE EVENT
  CREATE FUNCTION
  CREATE INDEX
  CREATE PROCEDURE
  CREATE SERVER
  CREATE TABLE
  CREATE TABLESPACE
  CREATE TRIGGER
  CREATE VIEW
  DROP DATABASE
  DROP EVENT
  DROP FUNCTION
  DROP INDEX
  DROP PROCEDURE
  DROP SERVER
  DROP TABLE
  DROP TABLESPACE
  DROP TRIGGER
  DROP VIEW

  MERGE
  RENAME TABLE
  TRUNCATE TABLE


 mysql> help create database;
 Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.
Eg:

mysql> create database  if not exists home;
Query OK, 1 row affected (0.00 sec)

mysql> help alter database;

Name: 'ALTER DATABASE'
Description:
Syntax:
ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
    UPGRADE DATA DIRECTORY NAME

alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

ALTER DATABASE enables you to change the overall characteristics of a
database. These characteristics are stored in the db.opt file in the
database directory. To use ALTER DATABASE, you need the ALTER privilege
on the database. ALTER SCHEMA is a synonym for ALTER DATABASE.

The database name can be omitted from the first syntax, in which case
the statement applies to the default database.

National Language Characteristics

The CHARACTER SET clause changes the default database character set.
The COLLATE clause changes the default database collation.
http://dev.mysql.com/doc/refman/5.5/en/charset.html, discusses
character set and collation names.

You can see what character sets and collations are available using,
respectively, the SHOW CHARACTER SET and SHOW COLLATION statements. See
[HELP SHOW CHARACTER SET], and [HELP SHOW COLLATION],

If you change the default character set or collation for a database,
stored routines that use the database defaults must be dropped and
recreated so that they use the new defaults. (In a stored routine,
variables with character data types use the database defaults if the
character set or collation are not specified explicitly. See [HELP
CREATE PROCEDURE].)

Upgrading from Versions Older than MySQL 5.1

The syntax that includes the UPGRADE DATA DIRECTORY NAME clause updates
the name of the directory associated with the database to use the
encoding implemented in MySQL 5.1 for mapping database names to
database directory names (see
http://dev.mysql.com/doc/refman/5.5/en/identifier-mapping.html). This
clause is for use under these conditions:

o It is intended when upgrading MySQL to 5.1 or later from older
  versions.

o It is intended to update a database directory name to the current
  encoding format if the name contains special characters that need
  encoding.

o The statement is used by mysqlcheck (as invoked by mysql_upgrade).

For example, if a database in MySQL 5.0 has the name a-b-c, the name
contains instances of the - (dash) character. In MySQL 5.0, the
database directory is also named a-b-c, which is not necessarily safe
for all file systems. In MySQL 5.1 and later, the same database name is
encoded as a@002db@002dc to produce a file system-neutral directory
name.

When a MySQL installation is upgraded to MySQL 5.1 or later from an
older version,the server displays a name such as a-b-c (which is in the
old format) as #mysql50#a-b-c, and you must refer to the name using the
#mysql50# prefix. Use UPGRADE DATA DIRECTORY NAME in this case to
explicitly tell the server to re-encode the database directory name to
the current encoding format:

ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME

After executing this statement, you can refer to the database as a-b-c
without the special #mysql50# prefix.

Eg:
mysql> alter database home  charset=utf8;
Query OK, 1 row affected (0.00 sec)

mysql> help drop database;
Name: 'DROP DATABASE'
Description:
Syntax:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP DATABASE drops all tables in the database and deletes the
database. Be very careful with this statement! To use DROP DATABASE,
you need the DROP privilege on the database. DROP SCHEMA is a synonym
for DROP DATABASE.

*Important*: When a database is dropped, user privileges on the
database are not automatically dropped. See [HELP GRANT].

IF EXISTS is used to prevent an error from occurring if the database
does not exist.

mysql> drop database if exists home;
Query OK, 0 rows affected (0.12 sec)

mysql> help create function;
Name: 'CREATE FUNCTION'
Description:
The CREATE FUNCTION statement is used to create stored functions and
user-defined functions (UDFs):

o For information about creating stored functions, see [HELP CREATE
  PROCEDURE].

o For information about creating user-defined functions, see [HELP
  CREATE FUNCTION UDF].

mysql> delimiter $$
mysql> CREATE DEFINER=`root`@`localhost` FUNCTION `normal1`(p1 INT, p2 INT)
    -> RETURNS INT(11)
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE ans INT ;
    -> SET ans= p1 +p2 ;
    -> RETURN ans ;
    -> END
    -> $$

Query OK, 0 rows affected (0.00 sec)


mysql> delimiter  ;
 mysql> select normal1(5,6);
+--------------+
| normal1(5,6)   |
+--------------+
|           11         |
+--------------+
1 row in set (0.00 sec)

mysql> help alter function
Name: 'ALTER FUNCTION'
Description:
Syntax:
ALTER FUNCTION func_name [characteristic ...]

characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

This statement can be used to change the characteristics of a stored
function. More than one change may be specified in an ALTER FUNCTION
statement. However, you cannot change the parameters or body of a
stored function using this statement to make such changes, you must
drop and re-create the function using DROP FUNCTION and CREATE
FUNCTION.

You must have the ALTER ROUTINE privilege for the function. (That
privilege is granted automatically to the function creator.) If binary
logging is enabled, the ALTER FUNCTION statement might also require the
SUPER privilege.


mysql> help drop function;
Name: 'DROP FUNCTION'
Description:
The DROP FUNCTION statement is used to drop stored functions and
user-defined functions (UDFs):

mysql> drop function normal1;
Query OK, 0 rows affected (0.78 sec)

mysql> help create procedure;
Name: 'CREATE PROCEDURE'
Description:
Syntax:
CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

These statements create stored routines. By default, a routine is
associated with the default database. To associate the routine
explicitly with a given database, specify the name as db_name.sp_name
when you create it.

The CREATE FUNCTION statement is also used in MySQL to support UDFs
(user-defined functions). A UDF can be regarded as an external stored function. Stored functions share
their namespace with UDFs.  for the rules describing how the server interprets references to different
kinds of functions.

To invoke a stored procedure, use the CALL statement (see [HELP CALL]).
To invoke a stored function, refer to it in an expression. The function
returns a value during expression evaluation.

CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE
privilege. They might also require the SUPER privilege, depending on
the DEFINER value, as described later in this section. If binary
logging is enabled, CREATE FUNCTION might require the SUPER privilege,

By default, MySQL automatically grants the ALTER ROUTINE and EXECUTE
privileges to the routine creator. This behavior can be changed by
disabling the automatic_sp_privileges system variable. See
http://dev.mysql.com/doc/refman/5.5/en/stored-routines-privileges.html.

The DEFINER and SQL SECURITY clauses specify the security context to be
used when checking access privileges at routine execution time, as
described later in this section.

If the routine name is the same as the name of a built-in SQL function,
a syntax error occurs unless you use a space between the name and the
following parenthesis when defining the routine or invoking it later.
For this reason, avoid using the names of existing SQL functions for
your own stored routines.

The IGNORE_SPACE SQL mode applies to built-in functions, not to stored
routines. It is always permissible to have spaces after a stored
routine name, regardless of whether IGNORE_SPACE is enabled.

The parameter list enclosed within parentheses must always be present.
If there are no parameters, an empty parameter list of () should be
used. Parameter names are not case sensitive.

Each parameter is an IN parameter by default. To specify otherwise for
a parameter, use the keyword OUT or INOUT before the parameter name.

 Specifying a parameter as IN, OUT, or INOUT is valid only for a
PROCEDURE. For a FUNCTION, parameters are always regarded as IN
parameters.

An IN parameter passes a value into a procedure. The procedure might
modify the value, but the modification is not visible to the caller
when the procedure returns. An OUT parameter passes a value from the
procedure back to the caller. Its initial value is NULL within the
procedure, and its value is visible to the caller when the procedure
returns. An INOUT parameter is initialized by the caller, can be
modified by the procedure, and any change made by the procedure is
visible to the caller when the procedure returns.

For each OUT or INOUT parameter, pass a user-defined variable in the
CALL statement that invokes the procedure so that you can obtain its
value when the procedure returns. If you are calling the procedure from
within another stored procedure or function, you can also pass a
routine parameter or local routine variable as an IN or INOUT
parameter.

The following example shows a simple stored procedure that uses an OUT
parameter:

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t
    -> END//

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter
mysql> CALL simpleproc(@a)
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

The example uses the mysql client delimiter command to change the
statement delimiter from  to // while the procedure is being defined.
This enables the  delimiter used in the procedure body to be passed
through to the server rather than being interpreted by mysql itself.

The RETURNS clause may be specified only for a FUNCTION, for which it
is mandatory. It indicates the return type of the function, and the
function body must contain a RETURN value statement. If the RETURN
statement returns a value of a different type, the value is coerced to
the proper type. For example, if a function specifies an ENUM or SET
value in the RETURNS clause, but the RETURN statement returns an
integer, the value returned from the function is the string for the
corresponding ENUM member of set of SET members.

The following example function takes a parameter, performs an operation
using an SQL function, and returns the result. In this case, it is
unnecessary to use delimiter because the function definition contains
no internal  statement delimiters:

mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!')

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world')
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

Parameter types and function return types can be declared to use any
valid data type, except that the COLLATE attribute cannot be used prior
to MySQL 5.5.3. As of 5.5.3, COLLATE can be used if preceded by the
CHARACTER SET attribute.

The routine_body consists of a valid SQL routine statement. This can be
a simple statement such as SELECT or INSERT, or a compound statement
written using BEGIN and END. Compound statements can contain
declarations, loops, and other control structure statements. The syntax
for these statements is described in

MySQL permits routines to contain DDL statements, such as CREATE and
DROP. MySQL also permits stored procedures (but not stored functions)
to contain SQL transaction statements such as COMMIT. Stored functions
may not contain statements that perform explicit or implicit commit or
rollback. Support for these statements is not required by the SQL
standard, which states that each DBMS vendor may decide whether to
permit them.

Statements that return a result set can be used within a stored
procedure but not within a stored function. This prohibition includes
SELECT statements that do not have an INTO var_list clause and other
statements such as SHOW, EXPLAIN, and CHECK TABLE. For statements that
can be determined at function definition time to return a result set, a
Not allowed to return a result set from a function error occurs
(ER_SP_NO_RETSET). For statements that can be determined only at
runtime to return a result set, a PROCEDURE %s can't return a result
set in the given context error occurs (ER_SP_BADSELECT).

USE statements within stored routines are not permitted. When a routine
is invoked, an implicit USE db_name is performed (and undone when the
routine terminates). The causes the routine to have the given default
database while it executes. References to objects in databases other
than the routine default database should be qualified with the
appropriate database name.

MySQL stores the sql_mode system variable setting that is in effect at
the time a routine is created, and always executes the routine with
this setting in force, regardless of the server SQL mode in effect when
the routine is invoked.

The switch from the SQL mode of the invoker to that of the routine
occurs after evaluation of arguments and assignment of the resulting
values to routine parameters. If you define a routine in strict SQL
mode but invoke it in nonstrict mode, assignment of arguments to
routine parameters does not take place in strict mode. If you require
that expressions passed to a routine be assigned in strict SQL mode,
you should invoke the routine with strict mode in effect.

mysql> delimiter $$
mysql> create definer=`root`@`localhost` procedure `sample`(a int)
    -> begin
    -> declare i int default 0;
    -> declare j int default 1;
    -> declare flag bool default false;
    -> drop temporary table if exists `sampletable`;
    -> create temporary table sampletable (fib int);
    -> while ! flag do
    -> insert into sampletable values(i),(j);
    -> set i=i+j;
    -> set j=i+j;
    -> if i > a then
    -> set flag=true;
    -> end if;
    -> end while;
    -> select * from sampletable where fib < a;
    -> end $$

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call sample(10);

+------+
| fib  |
+------+
|    0 |
|    1 |
|    1 |
|    2 |
|    3 |
|    5 |
|    8 |
+------+

7 rows in set (0.36 sec)

Query OK, 0 rows affected (0.37 sec)

mysql> help alter procedure
Name: 'ALTER PROCEDURE'
Description:
Syntax:
ALTER PROCEDURE proc_name [characteristic ...]

characteristic:
    COMMENT 'string'
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

This statement can be used to change the characteristics of a stored
procedure. More than one change may be specified in an ALTER PROCEDURE
statement. However, you cannot change the parameters or body of a
stored procedure using this statement to make such changes, you must
drop and re-create the procedure using DROP PROCEDURE and CREATE
PROCEDURE.

You must have the ALTER ROUTINE privilege for the procedure. By
default, that privilege is granted automatically to the procedure
creator. This behavior can be changed by disabling the
automatic_sp_privileges system variable. See

mysql> help drop procedure;
Name: 'DROP PROCEDURE'
Description:
Syntax:
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

This statement is used to drop a stored procedure or function. That is,
the specified routine is removed from the server. You must have the
ALTER ROUTINE privilege for the routine. (If the
automatic_sp_privileges system variable is enabled, that privilege and
EXECUTE are granted automatically to the routine creator when the
routine is created and dropped from the creator when the routine is
dropped.

The IF EXISTS clause is a MySQL extension. It prevents an error from
occurring if the procedure or function does not exist. A warning is
produced that can be viewed with SHOW WARNINGS.



mysql> drop procedure if exists sample;
Query OK, 0 rows affected (0.00 sec)


mysql> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]

data_type:
    BIT[(length)]
  | TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | YEAR
  | CHAR[(length)]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(length)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | BINARY[(length)]
  | VARBINARY(length)
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | TEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | MEDIUMTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | LONGTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | ENUM(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | SET(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | spatial_type

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

table_options:
    table_option [[,] table_option] ...

table_option:
    ENGINE [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]

select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...   (Some legal select statement)

CREATE TABLE creates a table with the given name. You must have the
CREATE privilege for the table.

Rules for permissible table names are given in
http://dev.mysql.com/doc/refman/5.5/en/identifiers.html. By default,
the table is created in the default database, using the InnoDB storage
engine. An error occurs if the table exists, if there is no default
database, or if the database does not exist.

Creating a Temporary table we seen is already on the procedure.
let see normal table creation.

mysql> CREATE TABLE `dept` (
    ->   `Id` int(11) NOT NULL COMMENT 'Department ID',
    ->   `Department` varchar(255) DEFAULT NULL COMMENT 'Department Name',
    ->   PRIMARY KEY (`Id`)
    -> );

Query OK, 0 rows affected (0.60 sec)

Sample with  Foreign Key Constaint.

mysql> CREATE TABLE `student` (
    ->   `Id` varchar(255) NOT NULL COMMENT 'Student ID(ref University ID)',
    ->   `name` varchar(255) DEFAULT NULL COMMENT 'Student Name',
    ->   `DOB` date DEFAULT NULL COMMENT 'Student''s Date of Birth',
    ->   `address` varchar(255) DEFAULT NULL COMMENT 'Student''s address',
    ->   `email` varchar(255) DEFAULT NULL COMMENT 'Student''s email ID',
    ->   `mobile` int(10) DEFAULT NULL COMMENT 'student''s maobile number',
    ->   `dept_id` int(11) DEFAULT NULL COMMENT 'Student''s department ID',
    ->   PRIMARY KEY (`Id`),
    ->   KEY `FK_student` (`dept_id`),
    ->   CONSTRAINT `FK_student` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`Id`)
    -> ) ;

Query OK, 0 rows affected (0.30 sec)



It Forms One-to-Many Mapping.

mysql> help alter table;
Name: 'ALTER TABLE'
Description:
Syntax:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
    partition_options

alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name ]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        reference_definition
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP {INDEX|KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | MAX_ROWS = rows
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name [, col_name] ...
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | FORCE
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | TRUNCATE PARTITION {partition_names | ALL }
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | ANALYZE PARTITION  {partition_names | ALL }
  | CHECK PARTITION  {partition_names | ALL }
  | OPTIMIZE PARTITION  {partition_names | ALL }
  | REBUILD PARTITION  {partition_names | ALL }
  | REPAIR PARTITION  {partition_names | ALL }
  | PARTITION BY partitioning_expression
  | REMOVE PARTITIONING

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

table_options:
    table_option [[,] table_option] ...  (see CREATE TABLE options)

partition_options:
    (see CREATE TABLE options)

ALTER TABLE changes the structure of a table. For example, you can add
or delete columns, create or destroy indexes, change the type of
existing columns, or rename columns or the table itself. You can also
change characteristics such as the storage engine used for the table or
the table comment.

Partitioning-related clauses for ALTER TABLE can be used with
partitioned tables for repartitioning, for adding, dropping, merging,
and splitting partitions, and for performing partitioning maintenance.
For more information, see
http://dev.mysql.com/doc/refman/5.5/en/alter-table-partition-operations
.html.

Following the table name, specify the alterations to be made. If none
are given, ALTER TABLE does nothing.