Saturday, January 18, 2014
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)
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.
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.
Friday, July 20, 2012
Mysql prompt-III Compound Statements
MySql Compound Statements:
BEGIN END
CASE STATEMENT
CLOSE
DECLARE CONDITION
DECLARE CURSOR
DECLARE HANDLER
DECLARE VARIABLE
FETCH
IF STATEMENT
ITERATE
LABELS
LEAVE
LOOP OPEN
REPEAT LOOP
RESIGNAL
RETURN
SIGNAL
WHILE
mysql> help begin end;
Name: 'BEGIN END'
Description:
Syntax:
[begin_label:] BEGIN
[statement_list]
END [end_label]
BEGIN ... END syntax is used for writing compound statements, which can
appear within stored programs (stored procedures and functions,
triggers, and events). A compound statement can contain multiple
statements, enclosed by the BEGIN and END keywords. statement_list
represents a list of one or more statements, each terminated by a
semicolon () statement delimiter. The statement_list itself is
optional, so the empty compound statement (BEGIN END) is legal.
BEGIN ... END blocks can be nested.
Use of multiple statements requires that a client is able to send
statement strings containing the statement delimiter. In the mysql
command-line client, this is handled with the delimiter command.
Changing the end-of-statement delimiter (for example, to //) permit
to be used in a program body
mysql> help case statement;
Name: 'CASE STATEMENT'
Description:
Syntax:
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
Or:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
The CASE statement for stored programs implements a complex conditional
construct.
*Note*: There is also a CASE expression, which differs from the CASE
statement. The
CASE statement cannot have an ELSE NULL clause, and it is terminated
with END CASE instead of END.
For the first syntax, case_value is an expression. This value is
compared to the when_value expression in each WHEN clause until one of
them is equal. When an equal when_value is found, the corresponding
THEN clause statement_list executes. If no when_value is equal, the
ELSE clause statement_list executes, if there is one.
This syntax cannot be used to test for equality with NULL because NULL
= NULL is false.
For the second syntax, each WHEN clause search_condition expression is
evaluated until one is true, at which point its corresponding THEN
clause statement_list executes. If no search_condition is equal, the
ELSE clause statement_list executes, if there is one.
If no when_value or search_condition matches the value tested and the
CASE statement contains no ELSE clause, a Case not found for CASE
statement error results.
Each statement_list consists of one or more SQL statements an empty
statement_list is not permitted.
To handle situations where no value is matched by any WHEN clause, use
an ELSE containing an empty BEGIN ... END block, as shown in this
example. (The indentation used here in the ELSE clause is for purposes
of clarity only, and is not otherwise significant.)
DELIMITER |
CREATE PROCEDURE p()
BEGIN
DECLARE v INT DEFAULT 1
CASE v
WHEN 2 THEN SELECT v
WHEN 3 THEN SELECT 0
ELSE
BEGIN
END
END CASE
END
|
Eg:
DELIMITER $$
USE `myt` $$
DROP FUNCTION IF EXISTS `case`$$
CREATE DEFINER = `root` @`localhost` FUNCTION `case` (p1 INT) RETURNS TEXT CHARSET utf8 DETERMINISTIC
BEGIN
DECLARE ans INT ;
DECLARE str TEXT ;
SELECT
MOD(p1, 2) INTO ans ;
CASE
WHEN (ans = 0) THEN
SET str = 'Its is even.' ;
RETURN str ;
WHEN (ans = 1) THEN
SET str = 'Its is odd' ;
RETURN str ;
END CASE ;
END $$
DELIMITER ;
mysql> help declare variable;
Name: 'DECLARE VARIABLE'
Description:
Syntax:
DECLARE var_name [, var_name] ... type [DEFAULT value]
This statement declares local variables within stored programs. To
provide a default value for a variable, include a DEFAULT clause. The
value can be specified as an expression it need not be a constant. If
the DEFAULT clause is missing, the initial value is NULL.
Local variables are treated like stored routine parameters with respect
to data type and overflow checking. See [HELP CREATE PROCEDURE].
Variable declarations must appear before cursor or handler
declarations.
Local variable names are not case sensitive. Permissible characters and
quoting rules are the same as for other identifiers, as described in
The scope of a local variable is the BEGIN ... END block within which
it is declared. The variable can be referred to in blocks nested within
the declaring block, except those blocks that declare a variable with
the same name.
mysql> help if statement;
Name: 'IF STATEMENT'
Description:
Syntax:
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
The IF statement for stored programs implements a basic conditional
construct.
*Note*: There is also an IF() function, which differs from the IF
statement.The
IF statement can have THEN, ELSE, and ELSEIF clauses, and it is
terminated with END IF.
If the search_condition evaluates to true, the corresponding THEN or
ELSEIF clause statement_list executes. If no search_condition matches,
the ELSE clause statement_list executes.
Each statement_list consists of one or more SQL statements an empty
statement_list is not permitted.
DELIMITER $$
USE `myt`$$
DROP FUNCTION IF EXISTS `ifelseif1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `ifelseif1`(p1 INT, p2 INT, p3 INT)
RETURNS INT(11)
DETERMINISTIC
BEGIN
DECLARE ans INT ;
IF((p1 < p2) && (p1 < p3))
THEN SET ans = p1 ;
ELSE
IF((p2 < p3) && (p2 < p1))
THEN SET ans = p2 ;
ELSE
SET ans = p3 ;
END IF ;
END IF;
RETURN ans ;
END$$
DELIMITER ;
mysql> help iterate;
Name: 'ITERATE'
Description:
Syntax:
ITERATE label
ITERATE can appear only within LOOP, REPEAT, and WHILE statements.
ITERATE means "start the loop again."
mysql> help labels;
Name: 'LABELS'
Description:
Syntax:
[begin_label:] BEGIN
[statement_list]
END [end_label]
[begin_label:] LOOP
statement_list
END LOOP [end_label]
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
Labels are permitted for BEGIN ... END blocks and for the LOOP, REPEAT,
and WHILE statements. Label use for those statements follows these
rules:
o begin_label must be followed by a colon.
o begin_label can be given without end_label. If end_label is present,
it must be the same as begin_label.
o end_label cannot be given without begin_label.
o Labels at the same nesting level must be distinct.
o Labels can be up to 16 characters long.
To refer to a label within the labeled construct, use an ITERATE or
LEAVE statement. The following example uses those statements to
continue iterating or terminate the loop:
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1
IF p1 < 10 THEN ITERATE label1 END IF
LEAVE label1
END LOOP label1
END
The scope of a block label does not include the code for handlers
mysql> help leave;
Name: 'LEAVE'
Description:
Syntax:
LEAVE label
This statement is used to exit the flow control construct that has the
given label. If the label is for the outermost stored program block,
LEAVE exits the program.
LEAVE can be used within BEGIN ... END or loop constructs (LOOP,
REPEAT, WHILE).
mysql> help loop;
Name: 'LOOP'
Description:
Syntax:
[begin_label:] LOOP
statement_list
END LOOP [end_label]
LOOP implements a simple loop construct, enabling repeated execution of
the statement list, which consists of one or more statements, each
terminated by a semicolon () statement delimiter. The statements
within the loop are repeated until the loop is terminated. Usually,
this is accomplished with a LEAVE statement. Within a stored function,
RETURN can also be used, which exits the function entirely.
Neglecting to include a loop-termination statement results in an
infinite loop.
A LOOP statement can be labeled. For the rules regarding label use,
Eg:
DELIMITER $$
USE `myt`$$
DROP FUNCTION IF EXISTS `loop1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `loop1`(p1 INT) RETURNS INT(11)
DETERMINISTIC
BEGIN
DECLARE ans INT ;
SET ans = 1 ;
label1 :
LOOP
SET ans = ans * p1 ;
SET p1 =p1-1;
IF p1 > 1 THEN
ITERATE label1 ;
END IF ;
LEAVE label1 ;
END LOOP label1 ;
RETURN ans ;
END$$
DELIMITER ;
mysql> help repeat;
Name: 'REPEAT LOOP'
Description:
Syntax:
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
The statement list within a REPEAT statement is repeated until the
search_condition expression is true. Thus, a REPEAT always enters the
loop at least once. statement_list consists of one or more statements,
each terminated by a semicolon () statement delimiter.
A REPEAT statement can be labeled. For the rules regarding label use,
see [HELP labels].
Examples:
mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0
-> REPEAT
-> SET @x = @x + 1
-> UNTIL @x > p1 END REPEAT
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)
mysql> help return;
Name: 'RETURN'
Description:
Syntax:
RETURN expr
The RETURN statement terminates execution of a stored function and
returns the value expr to the function caller. There must be at least
one RETURN statement in a stored function. There may be more than one
if the function has multiple exit points.
This statement is not used in stored procedures, triggers, or events.
The LEAVE statement can be used to exit a stored program of those
types.
mysql> help while;
Name: 'WHILE'
Description:
Syntax:
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
The statement list within a WHILE statement is repeated as long as the
search_condition expression is true. statement_list consists of one or
more SQL statements, each terminated by a semicolon () statement
delimiter.
A WHILE statement can be labeled. For the rules regarding label use,
see [HELP labels].
URL: http://dev.mysql.com/doc/refman/5.5/en/while-statement.html
Examples:
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5
WHILE v1 > 0 DO
...
SET v1 = v1 - 1
END WHILE
END
Eg:
DELIMITER $$
USE `myt`$$
DROP FUNCTION IF EXISTS `while1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `while1`(p1 INT) RETURNS INT(11)
DETERMINISTIC
BEGIN
DECLARE ans INT ;
SET ans = 0 ;
WHILE p1 > 0 DO
SET ans = ans + p1 ;
SET p1 = p1 - 1 ;
END WHILE ;
RETURN ans;
END$$
DELIMITER ;
BEGIN END
CASE STATEMENT
CLOSE
DECLARE CONDITION
DECLARE CURSOR
DECLARE HANDLER
DECLARE VARIABLE
FETCH
IF STATEMENT
ITERATE
LABELS
LEAVE
LOOP OPEN
REPEAT LOOP
RESIGNAL
RETURN
SIGNAL
WHILE
mysql> help begin end;
Name: 'BEGIN END'
Description:
Syntax:
[begin_label:] BEGIN
[statement_list]
END [end_label]
BEGIN ... END syntax is used for writing compound statements, which can
appear within stored programs (stored procedures and functions,
triggers, and events). A compound statement can contain multiple
statements, enclosed by the BEGIN and END keywords. statement_list
represents a list of one or more statements, each terminated by a
semicolon () statement delimiter. The statement_list itself is
optional, so the empty compound statement (BEGIN END) is legal.
BEGIN ... END blocks can be nested.
Use of multiple statements requires that a client is able to send
statement strings containing the statement delimiter. In the mysql
command-line client, this is handled with the delimiter command.
Changing the end-of-statement delimiter (for example, to //) permit
to be used in a program body
mysql> help case statement;
Name: 'CASE STATEMENT'
Description:
Syntax:
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
Or:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
The CASE statement for stored programs implements a complex conditional
construct.
*Note*: There is also a CASE expression, which differs from the CASE
statement. The
CASE statement cannot have an ELSE NULL clause, and it is terminated
with END CASE instead of END.
For the first syntax, case_value is an expression. This value is
compared to the when_value expression in each WHEN clause until one of
them is equal. When an equal when_value is found, the corresponding
THEN clause statement_list executes. If no when_value is equal, the
ELSE clause statement_list executes, if there is one.
This syntax cannot be used to test for equality with NULL because NULL
= NULL is false.
For the second syntax, each WHEN clause search_condition expression is
evaluated until one is true, at which point its corresponding THEN
clause statement_list executes. If no search_condition is equal, the
ELSE clause statement_list executes, if there is one.
If no when_value or search_condition matches the value tested and the
CASE statement contains no ELSE clause, a Case not found for CASE
statement error results.
Each statement_list consists of one or more SQL statements an empty
statement_list is not permitted.
To handle situations where no value is matched by any WHEN clause, use
an ELSE containing an empty BEGIN ... END block, as shown in this
example. (The indentation used here in the ELSE clause is for purposes
of clarity only, and is not otherwise significant.)
DELIMITER |
CREATE PROCEDURE p()
BEGIN
DECLARE v INT DEFAULT 1
CASE v
WHEN 2 THEN SELECT v
WHEN 3 THEN SELECT 0
ELSE
BEGIN
END
END CASE
END
|
Eg:
DELIMITER $$
USE `myt` $$
DROP FUNCTION IF EXISTS `case`$$
CREATE DEFINER = `root` @`localhost` FUNCTION `case` (p1 INT) RETURNS TEXT CHARSET utf8 DETERMINISTIC
BEGIN
DECLARE ans INT ;
DECLARE str TEXT ;
SELECT
MOD(p1, 2) INTO ans ;
CASE
WHEN (ans = 0) THEN
SET str = 'Its is even.' ;
RETURN str ;
WHEN (ans = 1) THEN
SET str = 'Its is odd' ;
RETURN str ;
END CASE ;
END $$
DELIMITER ;
mysql> help declare variable;
Name: 'DECLARE VARIABLE'
Description:
Syntax:
DECLARE var_name [, var_name] ... type [DEFAULT value]
This statement declares local variables within stored programs. To
provide a default value for a variable, include a DEFAULT clause. The
value can be specified as an expression it need not be a constant. If
the DEFAULT clause is missing, the initial value is NULL.
Local variables are treated like stored routine parameters with respect
to data type and overflow checking. See [HELP CREATE PROCEDURE].
Variable declarations must appear before cursor or handler
declarations.
Local variable names are not case sensitive. Permissible characters and
quoting rules are the same as for other identifiers, as described in
The scope of a local variable is the BEGIN ... END block within which
it is declared. The variable can be referred to in blocks nested within
the declaring block, except those blocks that declare a variable with
the same name.
mysql> help if statement;
Name: 'IF STATEMENT'
Description:
Syntax:
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
The IF statement for stored programs implements a basic conditional
construct.
*Note*: There is also an IF() function, which differs from the IF
statement.The
IF statement can have THEN, ELSE, and ELSEIF clauses, and it is
terminated with END IF.
If the search_condition evaluates to true, the corresponding THEN or
ELSEIF clause statement_list executes. If no search_condition matches,
the ELSE clause statement_list executes.
Each statement_list consists of one or more SQL statements an empty
statement_list is not permitted.
DELIMITER $$
USE `myt`$$
DROP FUNCTION IF EXISTS `ifelseif1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `ifelseif1`(p1 INT, p2 INT, p3 INT)
RETURNS INT(11)
DETERMINISTIC
BEGIN
DECLARE ans INT ;
IF((p1 < p2) && (p1 < p3))
THEN SET ans = p1 ;
ELSE
IF((p2 < p3) && (p2 < p1))
THEN SET ans = p2 ;
ELSE
SET ans = p3 ;
END IF ;
END IF;
RETURN ans ;
END$$
DELIMITER ;
mysql> help iterate;
Name: 'ITERATE'
Description:
Syntax:
ITERATE label
ITERATE can appear only within LOOP, REPEAT, and WHILE statements.
ITERATE means "start the loop again."
mysql> help labels;
Name: 'LABELS'
Description:
Syntax:
[begin_label:] BEGIN
[statement_list]
END [end_label]
[begin_label:] LOOP
statement_list
END LOOP [end_label]
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
Labels are permitted for BEGIN ... END blocks and for the LOOP, REPEAT,
and WHILE statements. Label use for those statements follows these
rules:
o begin_label must be followed by a colon.
o begin_label can be given without end_label. If end_label is present,
it must be the same as begin_label.
o end_label cannot be given without begin_label.
o Labels at the same nesting level must be distinct.
o Labels can be up to 16 characters long.
To refer to a label within the labeled construct, use an ITERATE or
LEAVE statement. The following example uses those statements to
continue iterating or terminate the loop:
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1
IF p1 < 10 THEN ITERATE label1 END IF
LEAVE label1
END LOOP label1
END
The scope of a block label does not include the code for handlers
mysql> help leave;
Name: 'LEAVE'
Description:
Syntax:
LEAVE label
This statement is used to exit the flow control construct that has the
given label. If the label is for the outermost stored program block,
LEAVE exits the program.
LEAVE can be used within BEGIN ... END or loop constructs (LOOP,
REPEAT, WHILE).
mysql> help loop;
Name: 'LOOP'
Description:
Syntax:
[begin_label:] LOOP
statement_list
END LOOP [end_label]
LOOP implements a simple loop construct, enabling repeated execution of
the statement list, which consists of one or more statements, each
terminated by a semicolon () statement delimiter. The statements
within the loop are repeated until the loop is terminated. Usually,
this is accomplished with a LEAVE statement. Within a stored function,
RETURN can also be used, which exits the function entirely.
Neglecting to include a loop-termination statement results in an
infinite loop.
A LOOP statement can be labeled. For the rules regarding label use,
Eg:
DELIMITER $$
USE `myt`$$
DROP FUNCTION IF EXISTS `loop1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `loop1`(p1 INT) RETURNS INT(11)
DETERMINISTIC
BEGIN
DECLARE ans INT ;
SET ans = 1 ;
label1 :
LOOP
SET ans = ans * p1 ;
SET p1 =p1-1;
IF p1 > 1 THEN
ITERATE label1 ;
END IF ;
LEAVE label1 ;
END LOOP label1 ;
RETURN ans ;
END$$
DELIMITER ;
mysql> help repeat;
Name: 'REPEAT LOOP'
Description:
Syntax:
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
The statement list within a REPEAT statement is repeated until the
search_condition expression is true. Thus, a REPEAT always enters the
loop at least once. statement_list consists of one or more statements,
each terminated by a semicolon () statement delimiter.
A REPEAT statement can be labeled. For the rules regarding label use,
see [HELP labels].
Examples:
mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0
-> REPEAT
-> SET @x = @x + 1
-> UNTIL @x > p1 END REPEAT
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)
mysql> help return;
Name: 'RETURN'
Description:
Syntax:
RETURN expr
The RETURN statement terminates execution of a stored function and
returns the value expr to the function caller. There must be at least
one RETURN statement in a stored function. There may be more than one
if the function has multiple exit points.
This statement is not used in stored procedures, triggers, or events.
The LEAVE statement can be used to exit a stored program of those
types.
mysql> help while;
Name: 'WHILE'
Description:
Syntax:
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
The statement list within a WHILE statement is repeated as long as the
search_condition expression is true. statement_list consists of one or
more SQL statements, each terminated by a semicolon () statement
delimiter.
A WHILE statement can be labeled. For the rules regarding label use,
see [HELP labels].
URL: http://dev.mysql.com/doc/refman/5.5/en/while-statement.html
Examples:
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5
WHILE v1 > 0 DO
...
SET v1 = v1 - 1
END WHILE
END
Eg:
DELIMITER $$
USE `myt`$$
DROP FUNCTION IF EXISTS `while1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `while1`(p1 INT) RETURNS INT(11)
DETERMINISTIC
BEGIN
DECLARE ans INT ;
SET ans = 0 ;
WHILE p1 > 0 DO
SET ans = ans + p1 ;
SET p1 = p1 - 1 ;
END WHILE ;
RETURN ans;
END$$
DELIMITER ;
MySql at Prompt- II Administration
mysql> help administration
You asked for help about help category: "Administration"
For more information, type 'help <item>', where <item> is one of the following
topics:
BINLOG
CACHE INDEX
CHANGE MASTER TO
DEALLOCATE PREPARE
EXECUTE STATEMENT
FLUSH
FLUSH QUERY CACHE
HELP COMMAND
KILL
LOAD INDEX
PREPARE
PURGE BINARY LOGS
RESET
RESET MASTER
RESET SLAVE
SET
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
SET SQL_LOG_BIN
SHOW
SHOW AUTHORS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW CONTRIBUTORS
SHOW CREATE DATABASE
SHOW CREATE EVENT
SHOW CREATE FUNCTION
SHOW CREATE PROCEDURE
SHOW CREATE TABLE
SHOW CREATE TRIGGER
SHOW CREATE VIEW
SHOW DATABASES
SHOW ENGINE
SHOW ENGINES
SHOW ERRORS
SHOW EVENTS
SHOW FUNCTION CODE
SHOW FUNCTION STATUS
SHOW GRANTS
SHOW INDEX
SHOW MASTER STATUS
SHOW OPEN TABLES
SHOW PLUGINS
SHOW PRIVILEGES
SHOW PROCEDURE CODE
SHOW PROCEDURE STATUS
SHOW PROCESSLIST
SHOW PROFILE
SHOW PROFILES
SHOW RELAYLOG EVENTS
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW TRIGGERS
SHOW VARIABLES
SHOW WARNINGS
START SLAVE
STOP SLAVE
mysql> help deallocate prepare;
Name: 'DEALLOCATE PREPARE'
Description:
Syntax:
{DEALLOCATE | DROP} PREPARE stmt_name
To deallocate a prepared statement produced with PREPARE, use a
DEALLOCATE PREPARE statement that refers to the prepared statement
name. Attempting to execute a prepared statement after deallocating it
results in an error.
mysql> help execute statement;
Name: 'EXECUTE STATEMENT'
Description:
Syntax:
EXECUTE stmt_name
[USING @var_name [, @var_name] ...]
After preparing a statement with PREPARE, you execute it with an
EXECUTE statement that refers to the prepared statement name. If the
prepared statement contains any parameter markers, you must supply a
USING clause that lists user variables containing the values to be
bound to the parameters. Parameter values can be supplied only by user
variables, and the USING clause must name exactly as many variables as
the number of parameter markers in the statement.
You can execute a given prepared statement multiple times, passing
different variables to it or setting the variables to different values
before each execution.
mysql> help prepare;
Name: 'PREPARE'
Description:
Syntax:
PREPARE stmt_name FROM preparable_stmt
The PREPARE statement prepares a statement and assigns it a name,
stmt_name, by which to refer to the statement later. Statement names
are not case sensitive. preparable_stmt is either a string literal or a
user variable that contains the text of the statement. The text must
represent a single SQL statement, not multiple statements. Within the
statement, "?" characters can be used as parameter markers to indicate
where data values are to be bound to the query later when you execute
it. The "?" characters should not be enclosed within quotation marks,
even if you intend to bind them to string values. Parameter markers can
be used only where data values should appear, not for SQL keywords,
identifiers, and so forth.
If a prepared statement with the given name already exists, it is
deallocated implicitly before the new statement is prepared. This means
that if the new statement contains an error and cannot be prepared, an
error is returned and no statement with the given name exists.
A prepared statement is executed with EXECUTE and released with
DEALLOCATE PREPARE.
The scope of a prepared statement is the session within which it is
created. Other sessions cannot see it.
Eg:mysql> prepare alllog from 'select * from login';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> execute alllog;
+-------------+---------------+---------------------+
| Register_ID | University_ID | Password |
+-------------+---------------+---------------------+
| 1 | 10106104014 | aRUN123 |
-------------------------------------------------------
mysql> drop prepare alllog;
Query OK, 0 rows affected (0.00 sec)
mysql> prepare alllog from 'select * from login where Register_ID=?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> help set;
Name: 'SET'
Description:
Syntax:
SET variable_assignment [, variable_assignment] ...
variable_assignment:
user_var_name = expr
| [GLOBAL | SESSION] system_var_name = expr
| [@@global. | @@session. | @@]system_var_name = expr
The SET statement assigns values to different types of variables that
affect the operation of the server or your client. Older versions of
MySQL employed SET OPTION, but this syntax is deprecated in favor of
SET without OPTION.
mysql> SET @a=1;
Query OK, 0 rows affected (0.00 sec)
mysql> execute alllog using @a;
+-------------+---------------+----------+
| Register_ID | University_ID | Password |
+-------------+---------------+----------+
| 1 | 10106104014 | aRUN123 |
+-------------+---------------+----------+
1 row in set (0.00 sec)
mysql> help show columns;
Name: 'SHOW COLUMNS'
Description:
Syntax:
SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
SHOW COLUMNS displays information about the columns in a given table.
It also works for views. The LIKE clause, if present, indicates which
column names to match. The WHERE clause can be given to select rows
using more general conditions, as discussed in
SHOW COLUMNS displays information only for those columns for which you
have some privilege.
If the data types differ from what you expect them to be based on a
CREATE TABLE statement, note that MySQL sometimes changes data types
when you create or alter a table. The conditions under which this
occurs are described in
http://dev.mysql.com/doc/refman/5.5/en/silent-column-changes.html.
The FULL keyword causes the output to include the column collation and
comments, as well as the privileges you have for each column.
You can use db_name.tbl_name as an alternative to the tbl_name FROM
db_name syntax. In other words, these two statements are equivalent:
mysql> SHOW COLUMNS FROM mytable FROM mydb
mysql> SHOW COLUMNS FROM mydb.mytable
SHOW COLUMNS displays the following values for each table column:
Field indicates the column name.
Type indicates the column data type.
Collation indicates the collation for nonbinary string columns, or NULL
for other columns. This value is displayed only if you use the FULL
keyword.
The Null field contains YES if NULL values can be stored in the column,
NO if not.
The Key field indicates whether the column is indexed:
o If Key is empty, the column either is not indexed or is indexed only
as a secondary column in a multiple-column, nonunique index.
o If Key is PRI, the column is a PRIMARY KEY or is one of the columns
in a multiple-column PRIMARY KEY.
o If Key is UNI, the column is the first column of a UNIQUE index. (A
UNIQUE index permits multiple NULL values, but you can tell whether
the column permits NULL by checking the Null field.)
o If Key is MUL, the column is the first column of a nonunique index in
which multiple occurrences of a given value are permitted within the
column.
If more than one of the Key values applies to a given column of a
table, Key displays the one with the highest priority, in the order
PRI, UNI, MUL.
A UNIQUE index may be displayed as PRI if it cannot contain NULL values
and there is no PRIMARY KEY in the table. A UNIQUE index may display as
MUL if several columns form a composite UNIQUE index although the
combination of the columns is unique, each column can still hold
multiple occurrences of a given value.
The Default field indicates the default value that is assigned to the
column.
The Extra field contains any additional information that is available
about a given column. The value is auto_increment if the column was
created with the AUTO_INCREMENT keyword and empty otherwise.
Privileges indicates the privileges you have for the column. This value
is displayed only if you use the FULL keyword.
Comment indicates any comment the column has. This value is displayed
only if you use the FULL keyword.
SHOW FIELDS is a synonym for SHOW COLUMNS. You can also list a table's
columns with the mysqlshow db_name tbl_name command.
The DESCRIBE statement provides information similar to SHOW COLUMNS.
See [HELP DESCRIBE].
The SHOW CREATE TABLE, SHOW TABLE STATUS, and SHOW INDEX statements
also provide information about tables. See [HELP SHOW].
Eg:
mysql> show columns from login;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| Register_ID | int(11) | NO | PRI | NULL | auto_increment |
| University_ID | varchar(12) | NO | | NULL | |
| Password | varchar(30) | NO | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> help show create database;
Name: 'SHOW CREATE DATABASE'
Description:
Syntax:
SHOW CREATE {DATABASE | SCHEMA} db_name
Shows the CREATE DATABASE statement that creates the given database.
SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE.
mysql> show create database test;
+----------+---------------------------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> help show create function;
Name: 'SHOW CREATE FUNCTION'
Description:
Syntax:
SHOW CREATE FUNCTION func_name
This statement is similar to SHOW CREATE PROCEDURE but for stored
functions. See [HELP SHOW CREATE PROCEDURE].
mysql> show create function `case`;
CREATE DEFINER=`root`@`localhost` FUNCTION`case`(p1 int)
RETURNS text CHARSET utf8 DETERMINISTIC
BEGIN
DECLARE ans int ;
declare str text ;
select
mod(p1, 2) into ans ;
case
when (ans = 0)
then set str = 'Its is even.' ;
return str;
when (ans = 1)
then set str = 'Its is odd' ;
return str;
end case ;
END case;
mysql> help show create procedure;
Name: 'SHOW CREATE PROCEDURE'
Description:
Syntax:
SHOW CREATE PROCEDURE proc_name
This statement is a MySQL extension. It returns the exact string that
can be used to re-create the named stored procedure. A similar
statement, SHOW CREATE FUNCTION, displays information about stored
functions (see [HELP SHOW CREATE FUNCTION]).
Both statements require that you be the owner of the routine or have
SELECT access to the mysql.proc table. If you do not have privileges
for the routine itself, the value displayed for the Create Procedure or
Create Function field will be NULL.
mysql> show create procedure primelist;
CREATE DEFINER=`root`@`localhost` PROCEDURE `primelist`(a int)
BEGIN
DECLARE i INT DEFAULT 1 ;
DECLARE j INT ;
DECLARE k int ;
DECLARE flag BOOL DEFAULT FALSE ;
DROP temporary TABLE IF exists `primenumbers` ;
CREATE temporary TABLE primenumbers (prime int) ;
WHILE ! flag DO
SET k = 0 ;
SET j = 1 ;
WHILE j <= i DO
IF i % j = 0 THEN
SET k = k + 1 ;
END IF ;
SET j = j + 1 ;
END WHILE ;
IF k = 2 THEN
INSERT INTO primenumbers VALUES (i) ;
END IF ;
SET i = i + 1 ;
IF i > a THEN
SET flag = true ;
END IF ;
END WHILE ;
SELECT * FROM primenumbers ;
END
mysql> help show create table;
Name: 'SHOW CREATE TABLE'
Description:
Syntax:
SHOW CREATE TABLE tbl_name
Shows the CREATE TABLE statement that creates the given table. To use
this statement, you must have some privilege for the table. This
statement also works with views.
SHOW CREATE TABLE quotes table and column names according to the value
of the sql_quote_show_create option.
mysql> show create table customers;
CREATE TABLE `customers` (
`CustomerId` int(11) NOT NULL,
`Firstname` varchar(50) DEFAULT NULL,
`Lastname` varchar(50) DEFAULT NULL,
PRIMARY KEY (`CustomerId`)
)
mysql> help show databases;
Name: 'SHOW DATABASES'
Description:
Syntax:
SHOW {DATABASES | SCHEMAS}
[LIKE 'pattern' | WHERE expr]
SHOW DATABASES lists the databases on the MySQL server host. SHOW
SCHEMAS is a synonym for SHOW DATABASES. The LIKE clause, if present,
indicates which database names to match. The WHERE clause can be given
to select rows using more general conditions, as discussed in
http://dev.mysql.com/doc/refman/5.5/en/extended-show.html.
You see only those databases for which you have some kind of privilege,
unless you have the global SHOW DATABASES privilege. You can also get
this list using the mysqlshow command.
If the server was started with the --skip-show-database option, you
cannot use this statement at all unless you have the SHOW DATABASES
privilege.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dreamy |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.11 sec)
mysql> help show grants;
Name: 'SHOW GRANTS'
Description:
Syntax:
SHOW GRANTS [FOR user]
This statement lists the GRANT statement or statements that must be
issued to duplicate the privileges that are granted to a MySQL user
account. The account is named using the same format as for the GRANT
statement for example, 'jeffrey'@'localhost'. If you specify only the
user name part of the account name, a host name part of '%' is used.
For additional information about specifying account names, see [HELP
GRANT].
mysql> SHOW GRANTS FOR 'root'@'localhost'
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
To list the privileges granted to the account that you are using to
connect to the server, you can use any of the following statements:
SHOW GRANTS
SHOW GRANTS FOR CURRENT_USER
SHOW GRANTS FOR CURRENT_USER()
If SHOW GRANTS FOR CURRENT_USER (or any of the equivalent syntaxes) is
used in DEFINER context, such as within a stored procedure that is
defined with SQL SECURITY DEFINER), the grants displayed are those of
the definer and not the invoker.
mysql> SHOW GRANTS FOR CURRENT_USER;
+-----------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-----------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> help show open tables;
Name: 'SHOW OPEN TABLES'
Description:
Syntax:
SHOW OPEN TABLES [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
SHOW OPEN TABLES lists the non-TEMPORARY tables that are currently open
in the table cache. See
http://dev.mysql.com/doc/refman/5.5/en/table-cache.html. The FROM
clause, if present, restricts the tables shown to those present in the
db_name database. The LIKE clause, if present, indicates which table
names to match. The WHERE clause can be given to select rows using more
general conditions.
mysql> help show privileges;
Name: 'SHOW PRIVILEGES'
Description:
Syntax:
SHOW PRIVILEGES
SHOW PRIVILEGES shows the list of system privileges that the MySQL
server supports. The exact list of privileges depends on the version of
your server.
mysql> help show tables;
Name: 'SHOW TABLES'
Description:
Syntax:
SHOW [FULL] TABLES [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
SHOW TABLES lists the non-TEMPORARY tables in a given database. You can
also get this list using the mysqlshow db_name command. The LIKE
clause, if present, indicates which table names to match. The WHERE
clause can be given to select rows using more general conditions..
This statement also lists any views in the database. The FULL modifier
is supported such that SHOW FULL TABLES displays a second output
column. Values for the second column are BASE TABLE for a table and
VIEW for a view.
If you have no privileges for a base table or view, it does not show up
in the output from SHOW TABLES or mysqlshow db_name.
mysql> show tables;
+---------------+
| Tables_in_myt |
+---------------+
| customers |
| orderproduct |
| orders |
| products |
| table1 |
+---------------+
5 rows in set (0.19 sec)
Monday, June 4, 2012
MySql at Prompt- I Account Management
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.8-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> help;
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
notee (\t) Don't write into outfile.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog
with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
For server side help, type 'help contents'
mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
Account Management
Administration
Compound Statements
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Help Metadata
Language Structure
Plugins
Table Maintenance
Transactions
User-Defined Functions
Utility
mysql> help account management
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following
topics:
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD
mysql> help create user
Name: 'CREATE USER'
Description:
Syntax:
CREATE USER user_specification
[, user_specification] ...
user_specification:
user
[
IDENTIFIED BY [PASSWORD] 'password'
| IDENTIFIED WITH auth_plugin [AS 'auth_string']
]
The CREATE USER statement creates new MySQL accounts. To use it, you
must have the global CREATE USER privilege or the INSERT privilege for
the mysql database. For each account, CREATE USER creates a new row in
the mysql.user table and assigns the account no privileges. An error
occurs if the account already exists.
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'
If you specify only the user name part of the account name, a host name
part of '%' is used.
The user specification may indicate how the user should authenticate
when connecting to the server:
o To enable the user to connect with no password (which is insecure),
include no IDENTIFIED BY clause:
Sample 1.1
mysql> create user 'dreamy'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select user, password from mysql.user;
+--------+----------+
| user | password |
+--------+----------+
| root | |
| root | |
| root | |
| | |
| root | |
| dreamy | |
+--------+----------+
6 rows in set (0.00 sec)
In this case, the server uses built-in authentication and clients
must provide no password.
o To assign a password, use IDENTIFIED BY with the literal plaintext
password value:
Sample 1.2
mysql> create user 'dreamy'@'localhost' identified by 'arun';
Query OK, 0 rows affected (0.00 sec)
mysql> select user, password from mysql.user;
+--------+-------------------------------------------+
| user | password |
+--------+-------------------------------------------+
| root | |
| root | |
| root | |
| | |
| dreamy | *F70361020567813A819C38085D2B08A8748C5FC8 |
| root | |
+--------+-------------------------------------------+
6 rows in set (0.00 sec)
The server uses built-in authentication and clients must match the
given password.
o To avoid specifying the plaintext password if you know its hash value
(the value that PASSWORD() would return for the password), specify
the hash value preceded by the keyword PASSWORD:
CREATE USER 'jeffrey'@'localhost'
IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689'
Sample 1.3
mysql> select password('arun');
+-------------------------------------------+
| password('arun') |
+-------------------------------------------+
| *F70361020567813A819C38085D2B08A8748C5FC8 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> create user 'dreamy'@'localhost' identified by password '*F70361020567813A819C38085D2B08A8748C5FC8';
Query OK, 0 rows affected (0.00 sec)
mysql> select user, password from mysql.user;
+--------+-------------------------------------------+
| user | password |
+--------+-------------------------------------------+
| root | |
| root | |
| root | |
| | |
| root | |
| dreamy | *F70361020567813A819C38085D2B08A8748C5FC8 |
+--------+-------------------------------------------+
6 rows in set (0.00 sec)
The server uses built-in authentication and clients must match the
given password.
o If the account should authenticate using a specific authentication
plugin, use IDENTIFIED WITH. auth_plugin is an authentication plugin
name. It can be an unquoted name or a quoted string literal.
'auth_string' is an optional quoted string literal to pass to the
plugin. The plugin interprets the meaning of the string.
CREATE USER 'jeffrey'@'localhost'
IDENTIFIED WITH my_auth_plugin
The server uses the named plugin and clients must provide credentials
as required for the authentication method that the plugin implements.
If the server cannot find the plugin, an error occurs. IDENTIFIED
WITH can be given as of MySQL 5.5.7.
The IDENTIFIED BY and IDENTIFIED WITH clauses are mutually exclusive,
so at most one of them can be specified for a given user.
mysql> help drop user
Name: 'DROP USER'
Description:
Syntax:
DROP USER user [, user] ...
The DROP USER statement removes one or more MySQL accounts and their
privileges. It removes privilege rows for the account from all grant
tables. To use this statement, you must have the global CREATE USER
privilege or the DELETE privilege for the mysql database.
DROP USER 'jeffrey'@'localhost'
If you specify only the user name part of the account name, a host name
part of '%' is used.
Sample 1.4
mysql> drop user 'dreamy'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> help rename userName: 'RENAME USER'
Description:
Syntax:
RENAME USER old_user TO new_user
[, old_user TO new_user] ...
The RENAME USER statement renames existing MySQL accounts. To use it,
you must have the global CREATE USER privilege or the UPDATE privilege
for the mysql database. An error occurs if any old account does not
exist or any new account exists. Each account name uses the format
described in http://dev.mysql.com/doc/refman/5.5/en/account-names.html.
For example:
RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1'
If you specify only the user name part of the account name, a host name
part of '%' is used.
RENAME USER causes the privileges held by the old user to be those held
by the new user. However, RENAME USER does not automatically drop or
invalidate databases or objects within them that the old user created.
This includes stored programs or views for which the DEFINER attribute
names the old user. Attempts to access such objects may produce an
error if they execute in definer security context.
Sample 1.5
mysql> rename user 'dreamy'@'localhost' to 'kamali'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> select user from mysql.user;
+--------+
| user |
+--------+
| root |
| root |
| root |
| |
| kamali |
| root |
+--------+
6 rows in set (0.00 sec)
mysql> help grant
Name: 'GRANT'
Description:
Syntax:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]
GRANT PROXY ON user_specification
TO user_specification [, user_specification] ...
[WITH GRANT OPTION]
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
user_specification:
user
[
IDENTIFIED BY [PASSWORD] 'password'
| IDENTIFIED WITH auth_plugin [AS 'auth_string']
]
ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
The GRANT statement grants privileges to MySQL user accounts. GRANT
also serves to specify other account characteristics such as use of
secure connections and limits on access to server resources. To use
GRANT, you must have the GRANT OPTION privilege, and you must have the
privileges that you are granting.
Normally, a database administrator first uses CREATE USER to create an
account, then GRANT to define its privileges and characteristics. For
example:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'
GRANT ALL ON db1.* TO 'jeffrey'@'localhost'
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost'
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90
However, if an account named in a GRANT statement does not already
exist, GRANT may create it under the conditions described later in the
discussion of the NO_AUTO_CREATE_USER SQL mode.
The REVOKE statement is related to GRANT and enables administrators to
remove account privileges. See [HELP REVOKE].
To determine what privileges an account has, use SHOW GRANTS. See [HELP
SHOW GRANTS].
Sample 1.6
mysql> show grants for 'kamali'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for kamali@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kamali'@'localhost' IDENTIFIED BY PASSWORD '*F70361020567813A819C38085D2B08A8748C5FC8' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Sample 1.7
C:\wamp\bin\mysql\mysql5.5.8\bin>mysql -h localhost -u kamali -p
Enter password: ****
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
Sample 1.8
mysql> grant select on onlintest.* to 'kamali'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'kamali'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for kamali@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kamali'@'localhost' IDENTIFIED BY PASSWORD '*F70361020567813A819C38085D2B08A8748C5FC8' |
| GRANT SELECT ON `onlintest`.* TO 'kamali'@'localhost' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select db from mysql.db where user='kamali';
+-----------+
| db |
+-----------+
| onlintest |
+-----------+
1 row in set (0.00 sec)
mysql> grant all on *.* to 'kamali'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Sample 1.9:
mysql> revoke all privileges,grant option from 'kamali'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'kamali'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for kamali@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kamali'@'localhost' IDENTIFIED BY PASSWORD '*F70361020567813A819C38085D2B08A8748C5FC8' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select password('dreamy');
+-------------------------------------------+
| password('dreamy') |
+-------------------------------------------+
| *21935FCF3EA4C49338463622BB6F36A8855720FC |
+-------------------------------------------+
1 row in set (0.01 sec)
mysql> select user, password from mysql.user;
+--------+-------------------------------------------+
| user | password |
+--------+-------------------------------------------+
| root | |
| root | |
| root | |
| | |
| kamali | *F70361020567813A819C38085D2B08A8748C5FC8 |
| root | |
+--------+-------------------------------------------+
6 rows in set (0.03 sec)
mysql> use mysql;
Database changed
mysql> update user set password='*21935FCF3EA4C49338463622BB6F36A8855720FC' WHERE user='kamali';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select user, password from mysql.user;
+--------+-------------------------------------------+
| user | password |
+--------+-------------------------------------------+
| root | |
| root | |
| root | |
| | |
| kamali | *21935FCF3EA4C49338463622BB6F36A8855720FC |
| root | |
+--------+-------------------------------------------+
6 rows in set (0.00 sec)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.8-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> help;
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
notee (\t) Don't write into outfile.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog
with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
For server side help, type 'help contents'
mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
Account Management
Administration
Compound Statements
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Help Metadata
Language Structure
Plugins
Table Maintenance
Transactions
User-Defined Functions
Utility
mysql> help account management
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following
topics:
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD
mysql> help create user
Name: 'CREATE USER'
Description:
Syntax:
CREATE USER user_specification
[, user_specification] ...
user_specification:
user
[
IDENTIFIED BY [PASSWORD] 'password'
| IDENTIFIED WITH auth_plugin [AS 'auth_string']
]
The CREATE USER statement creates new MySQL accounts. To use it, you
must have the global CREATE USER privilege or the INSERT privilege for
the mysql database. For each account, CREATE USER creates a new row in
the mysql.user table and assigns the account no privileges. An error
occurs if the account already exists.
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'
If you specify only the user name part of the account name, a host name
part of '%' is used.
The user specification may indicate how the user should authenticate
when connecting to the server:
o To enable the user to connect with no password (which is insecure),
include no IDENTIFIED BY clause:
Sample 1.1
mysql> create user 'dreamy'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select user, password from mysql.user;
+--------+----------+
| user | password |
+--------+----------+
| root | |
| root | |
| root | |
| | |
| root | |
| dreamy | |
+--------+----------+
6 rows in set (0.00 sec)
In this case, the server uses built-in authentication and clients
must provide no password.
o To assign a password, use IDENTIFIED BY with the literal plaintext
password value:
Sample 1.2
mysql> create user 'dreamy'@'localhost' identified by 'arun';
Query OK, 0 rows affected (0.00 sec)
mysql> select user, password from mysql.user;
+--------+-------------------------------------------+
| user | password |
+--------+-------------------------------------------+
| root | |
| root | |
| root | |
| | |
| dreamy | *F70361020567813A819C38085D2B08A8748C5FC8 |
| root | |
+--------+-------------------------------------------+
6 rows in set (0.00 sec)
The server uses built-in authentication and clients must match the
given password.
o To avoid specifying the plaintext password if you know its hash value
(the value that PASSWORD() would return for the password), specify
the hash value preceded by the keyword PASSWORD:
CREATE USER 'jeffrey'@'localhost'
IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689'
Sample 1.3
mysql> select password('arun');
+-------------------------------------------+
| password('arun') |
+-------------------------------------------+
| *F70361020567813A819C38085D2B08A8748C5FC8 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> create user 'dreamy'@'localhost' identified by password '*F70361020567813A819C38085D2B08A8748C5FC8';
Query OK, 0 rows affected (0.00 sec)
mysql> select user, password from mysql.user;
+--------+-------------------------------------------+
| user | password |
+--------+-------------------------------------------+
| root | |
| root | |
| root | |
| | |
| root | |
| dreamy | *F70361020567813A819C38085D2B08A8748C5FC8 |
+--------+-------------------------------------------+
6 rows in set (0.00 sec)
The server uses built-in authentication and clients must match the
given password.
o If the account should authenticate using a specific authentication
plugin, use IDENTIFIED WITH. auth_plugin is an authentication plugin
name. It can be an unquoted name or a quoted string literal.
'auth_string' is an optional quoted string literal to pass to the
plugin. The plugin interprets the meaning of the string.
CREATE USER 'jeffrey'@'localhost'
IDENTIFIED WITH my_auth_plugin
The server uses the named plugin and clients must provide credentials
as required for the authentication method that the plugin implements.
If the server cannot find the plugin, an error occurs. IDENTIFIED
WITH can be given as of MySQL 5.5.7.
The IDENTIFIED BY and IDENTIFIED WITH clauses are mutually exclusive,
so at most one of them can be specified for a given user.
mysql> help drop user
Name: 'DROP USER'
Description:
Syntax:
DROP USER user [, user] ...
The DROP USER statement removes one or more MySQL accounts and their
privileges. It removes privilege rows for the account from all grant
tables. To use this statement, you must have the global CREATE USER
privilege or the DELETE privilege for the mysql database.
DROP USER 'jeffrey'@'localhost'
If you specify only the user name part of the account name, a host name
part of '%' is used.
Sample 1.4
mysql> drop user 'dreamy'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> help rename userName: 'RENAME USER'
Description:
Syntax:
RENAME USER old_user TO new_user
[, old_user TO new_user] ...
The RENAME USER statement renames existing MySQL accounts. To use it,
you must have the global CREATE USER privilege or the UPDATE privilege
for the mysql database. An error occurs if any old account does not
exist or any new account exists. Each account name uses the format
described in http://dev.mysql.com/doc/refman/5.5/en/account-names.html.
For example:
RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1'
If you specify only the user name part of the account name, a host name
part of '%' is used.
RENAME USER causes the privileges held by the old user to be those held
by the new user. However, RENAME USER does not automatically drop or
invalidate databases or objects within them that the old user created.
This includes stored programs or views for which the DEFINER attribute
names the old user. Attempts to access such objects may produce an
error if they execute in definer security context.
Sample 1.5
mysql> rename user 'dreamy'@'localhost' to 'kamali'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> select user from mysql.user;
+--------+
| user |
+--------+
| root |
| root |
| root |
| |
| kamali |
| root |
+--------+
6 rows in set (0.00 sec)
mysql> help grant
Name: 'GRANT'
Description:
Syntax:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]
GRANT PROXY ON user_specification
TO user_specification [, user_specification] ...
[WITH GRANT OPTION]
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
user_specification:
user
[
IDENTIFIED BY [PASSWORD] 'password'
| IDENTIFIED WITH auth_plugin [AS 'auth_string']
]
ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
The GRANT statement grants privileges to MySQL user accounts. GRANT
also serves to specify other account characteristics such as use of
secure connections and limits on access to server resources. To use
GRANT, you must have the GRANT OPTION privilege, and you must have the
privileges that you are granting.
Normally, a database administrator first uses CREATE USER to create an
account, then GRANT to define its privileges and characteristics. For
example:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'
GRANT ALL ON db1.* TO 'jeffrey'@'localhost'
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost'
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90
However, if an account named in a GRANT statement does not already
exist, GRANT may create it under the conditions described later in the
discussion of the NO_AUTO_CREATE_USER SQL mode.
The REVOKE statement is related to GRANT and enables administrators to
remove account privileges. See [HELP REVOKE].
To determine what privileges an account has, use SHOW GRANTS. See [HELP
SHOW GRANTS].
Sample 1.6
mysql> show grants for 'kamali'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for kamali@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kamali'@'localhost' IDENTIFIED BY PASSWORD '*F70361020567813A819C38085D2B08A8748C5FC8' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Sample 1.7
C:\wamp\bin\mysql\mysql5.5.8\bin>mysql -h localhost -u kamali -p
Enter password: ****
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
Sample 1.8
mysql> grant select on onlintest.* to 'kamali'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'kamali'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for kamali@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kamali'@'localhost' IDENTIFIED BY PASSWORD '*F70361020567813A819C38085D2B08A8748C5FC8' |
| GRANT SELECT ON `onlintest`.* TO 'kamali'@'localhost' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select db from mysql.db where user='kamali';
+-----------+
| db |
+-----------+
| onlintest |
+-----------+
1 row in set (0.00 sec)
mysql> grant all on *.* to 'kamali'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Sample 1.9:
mysql> revoke all privileges,grant option from 'kamali'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'kamali'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for kamali@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kamali'@'localhost' IDENTIFIED BY PASSWORD '*F70361020567813A819C38085D2B08A8748C5FC8' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select password('dreamy');
+-------------------------------------------+
| password('dreamy') |
+-------------------------------------------+
| *21935FCF3EA4C49338463622BB6F36A8855720FC |
+-------------------------------------------+
1 row in set (0.01 sec)
mysql> select user, password from mysql.user;
+--------+-------------------------------------------+
| user | password |
+--------+-------------------------------------------+
| root | |
| root | |
| root | |
| | |
| kamali | *F70361020567813A819C38085D2B08A8748C5FC8 |
| root | |
+--------+-------------------------------------------+
6 rows in set (0.03 sec)
mysql> use mysql;
Database changed
mysql> update user set password='*21935FCF3EA4C49338463622BB6F36A8855720FC' WHERE user='kamali';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select user, password from mysql.user;
+--------+-------------------------------------------+
| user | password |
+--------+-------------------------------------------+
| root | |
| root | |
| root | |
| | |
| kamali | *21935FCF3EA4C49338463622BB6F36A8855720FC |
| root | |
+--------+-------------------------------------------+
6 rows in set (0.00 sec)
Subscribe to:
Posts
(
Atom
)