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)
No comments :
Post a Comment