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 ;

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)