Syntax:mail(to, subject, message, extra headers, additional parameters)
Parameter:
Name
Description
Required / Optional
Type
to
Mail address to which you want to send mail
Required
String
subject
Subject of the mail
Required
String
message
Message to be sent with the mail. Each line of the message should be separated with a LF (\n). Lines should not be larger than 70 characters.
Required
String
extra headers
Additional headers like from, CC, BCC. If more than one additional headers are used, they must be separated with CRLF (Carriage return line feed), i.e. new line.
Optional
string
additional parameters
Additional parameters like – the envelope sender address when using sendmail with the -f sendmail option, can be used using this parameter.
Optional
string
Return value:
Returns true if the mail is successfully sent, otherwise, it returns false.
Value Type: boolean
Example:
<?php
$to = '[email protected]';
$subject = 'Demo mail ';
$message = 'This is a demo mail. Please reply to make sure the mail communication is okay.';
mail($to, $subject, $message);
?>
Copy
Send a simple mail using mail() function
Browse view of the form for sending simple mail in php
We encourage you to replace the value of the parameters used in the above example and test it.
Send mail in PHP with additional parameters
You can pass additional parameters as command line options to the program configured to be used while sending mail. Those said configurations are defined by the sendmail_path configuration. One example of using additional parameter is setting the envelope sender address with -f option.
This tutorial discusses PHP form handling/processing. You will learn how to collect user-supplied form data using POST and GET method from various from controls like text, select, radio, checkbox and textarea.
When user submits data by clicking on “complete reservation”, data supplied to various fields of the form goes to the file mentioned as a value of the action attribute specified within the opening tag of the form. For this example, like often developers do, we have used this file itself for processing form data. So, <?php echo $_SERVER['PHP_SELF']; ?> points to the file itself. If you wish to use a PHP file other that this to process form data, you may replace that with filename of your choice.
Form data is submitted to the PHP file for processing using POST here. Other methods you may use instead of POST is GET and REQUEST.
In a moment we will see differences between those three methods.
GET vs POST vs REQUEST
All of these methods create an associative array(e.g. array( key => value, key2 => value2, key3 => value3, …)). This array holds key-value pairs, where keys are names of the form controls and data supplied to those controls are values.
Both GET, POST and REQUEST are treated as $_GET, $_POST and $_REQUEST. All of these are superglobals,i.e. automatically global variables. So, they can be accessed throughout a script, including within functions or methods, without declaring them explicitly as global $variable;.
$_GET is an associative array of variables passed to the current script via the URL parameters.
$_POST is an associative array of variables passed to the current script via the HTTP POST method.
$_REQUEST is also an associative array. Since it contains the contents of $_GET, $_POST and $_COOKIE automatically, it can be modified remotely and thus can’t be trusted. So, it may be not a good idea to use REQUEST when processing form data. so, you may use GET or POST.
Which one shall I use? GET or POST
Sometimes, this may bother a beginner. Let’s see both of this methods a bit more in detail, which, hopefully, help you to pick one.
GET appends name/value pairs to the URL and this way passes the values collected from form to the destination PHP file. POST method embeds the name-value pairs inside the body of the HTTP request and the information is sent to the server.
Browsers have limitations when it comes to appending name value pairs to the url, as it happens in case of GET. The effective limitation is about 2000 characters. Some Firewalls or IDS also produce unreliable results because of this. Moreover, name value pairs are also cached in browser. If somebody bookmarks the url containing name value pairs, (s)he can directly get the values passed previously.
Passing the values appended to the url, particularly when sending sensitive information is not a good idea for security reasons.
Limitations of characters does not come to POST since it embeds name-value pairs within the body of the HTTP request. Moreover POST supports advanced functionality such as support for multi-part binary input while uploading files to server.
POST is preferred by developers, in general, to send form data to the server. GET may be used for sending nonsensitive data.
Using POST
Now, we will see how we can send data using POST and how to access data send using POST. We will take the HTML form shown above in this tutorial for demonstration.
When you use POST, the value of the method attribute is POST. And you can collect values send bu POST to the file specified as the value of action attribute. In this example, we are using the file itself, where the code for the HTML form resides.
POST creates an associative array. So, after form is submit, you may access that data using $_POST array. Let’s add the following PHP code in that file (say form-handling-example-form.php)to view what we receive after filling our example HTML form with some arbitrary data.
<?php
print_r($_POST);
?>
Copy
And following will be output of after form data is submit (with data we supplied to the form for demonstration. You may use your own data).
In practice, though, you will need to collect data for individual fields/controls of the form. In a moment, we will discuss that.
How to collect text input using POST
To collect text input data, we use $_POST['fieldname'], where field name is value of the associated field’s name attribute. So, for the example form, to collect data supplied by user in Full Name field, we use $_POST['full_name']. You may collect data in this fashion for fields if value of the type attribute is text or email or date.
For collecting data form selection list, radio, and checkbox, we need to be a little tricky, though.
How to collect selection list data/value using POST
Here is a piece of code which you may use to collect data from a selection list.
The code above will collect data/value form a checkbox.
How to collect radio button data/value using POST
Using a simple PHP Switch statement, you may collect data from a radio button.
<label>Terms and conditions<span class="note">*</span></label>
<input type="radio" name="tnc" value="agree" <?php echo $tncv; ?>>I agree<br>
<input type="radio" name="tnc" value="disagree" <?php echo $tnc1v; ?>>I disagree<br>
$tnc = $_POST['tnc'];
switch($tnc)
{
case "agree":
$tncv="checked";
$tnc1v="";
break;
case "disagree":
$tncv="";
$tnc1v="checked";
break;
default: // By default 1st option is selected
$tncv="checked";
$tnc1v="";
break;
};
Copy
Detecting if the form is submit
To detect whether a form is submitted, you may use following code pattern.
if (isset($_POST['submit'])){
//do something
}
Copy
Where submit is the name of the submit button used to submit data.
Using GET
If you wish to collect data using GET instead of POST, you have to change the value of the method attribute of the form in question as GET. User supplied form data can be collected using $_GET associative array in the similar fashion we shown POST to be used to collect data.
For the form in the example in this tutorial, if we use GET instead of POST, and supply some arbitrary data to the form and submit, data will be passed to the url as name-value pairs. Following picture shows you what happens.
You may download the form used in this example and try all the options shown in this tutorial.
Form handling/processing is a very important part of learning PHP and hopefully this tutorial is useful for you to do that. If you feel some more relevant stuff can be useful, let us know.
Due to its simplicity and ease of use, PHP is a widely-used open source general-purpose scripting language. PHP is used for creating interactive and dynamic web pages quickly and can access a wide range of relational database management systems such as MySQL, PostgreSQL, and SQLite. Many of us already access MySQL databases by using either the MySQL or MySQLi extensions. As of version 5.1 PHP provides new database connection abstraction library, PHP Data Objects (PDO).
Contents:
What is PDO?
Installing PDO
Predefined Constants
Supported Databases
Sample database, table, table structure, table records for various examples
The PDO class
Details of the PDO class methods
Details of PDOStatement class
What is PDO?
PDO – PHP Data Object.
A set of PHP extensions that provide a core PDO class and database specific drivers.
Provides a vendor-neutral lightweight data-access abstraction layer.
Focus on data access abstraction rather than database abstraction.
PDO requires the new object oriented features in the core of PHP 5, therefore it will not run with earlier versions of PHP.
Installing PDO
PDO is dividing into two components: – Core which provides the interface. – Drivers to access particular driver.
Installing PDO on Unix systems: — PDO (Core) and the PDO_SQLITE driver (SQLITE driver) is enabled by default as of PHP 5.1.0. To access other databases you must enable the PDO driver. — To install PDO as a shared module the php.ini needs to be updated so that the PDO extension will be loaded automatically when PHP runs. You also need to enable other database specific drivers and they must be listed after the pdo.so line, as PDO must be initialized before the database-specific extensions can be loaded. If you built PDO and the database-specific extensions statically, you can skip this step: extension=pdo.so
Installing PDO on Windows systems: — PDO and all the major drivers ship with PHP as shared extensions, and simply need to be activated by editing the php.ini file : extension=php_pdo.dll. This step is not necessary for PHP 5.3 and above, as a DLL is no longer required for PDO. — Next, choose the other database-specific DLL files and either use dl() to load them at runtime, or enable them in php.ini below php_pdo.dll.
To get the effect of a new configuration in php.ini file you will need to restart PHP.
Predefined Constants
Supported Database
PDO interface is available in the following drivers:
Database name
Driver name
Cubrid
PDO_CUBRID
FreeTDS / Microsoft SQL Server / Sybase
PDO_DBLIB
Firebird/Interbase 6
PDO_FIREBIRD
IBM DB2
PDO_IBM
IBM Informix Dynamic Server
PDO_INFORMIX
MySQL 3.x/4.x/5.x
PDO_MYSQL
Oracle Call Interface
PDO_OCI
ODBC v3 (IBM DB2, unixODBC and win32 ODBC)
PDO_ODBC
PostgreSQL
PDO_PGSQL
SQLite 3 and SQLite 2
PDO_SQLITE
Microsoft SQL Server / SQL Azure
PDO_SQLSRV
4D
PDO_4D
Sample database, table, table structure, table records for various examples
dsn – The Data Source Name, or DSN, contains the information required to connect to the database. The string contains the prefix name (e.g. pgsql for PostgreSQL database), a colon, and the server keyword. username – A string that contains the user’s name. This parameter is optional for some PDO drivers. password – A string that contains the user’s password. This parameter is optional for some PDO drivers. driver_options – Optional. A key=>value array of driver-specific connection options.
Return Value: Returns a PDO object on success. If failure, returns a PDOException object.
Database Connections
Connections are established by creating instances of the PDO base class. It doesn’t matter which driver you want to use; you always use the PDO class name. The constructor accepts parameters for specifying the database source (known as the DSN) and optionally for the username and password (if any).
If there are any connection errors, a PDOException object will be thrown. You may catch the exception if you want to handle the error condition, or you can leave it to global exception handler which can be set up via set_exception_handler().
Error: SQLSTATE[08006] [7] could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host “localhost” (::1) and accepting TCP/IP connections on port 5432? FATAL: password authentication failed for user “postgress”
Closing a connection
<?php>
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
// use the connection here
// Following command close the connection.
$dbh = null;
?>
Copy
Persistent connections
Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.
Turns off auto-commit mode and begins a transaction. The transaction begins with PDO::beginTransaction and will end when PDO::commit or PDO::rollback is called.
Syntax:bool PDO::beginTransaction ( void )
Return Value: Returns TRUE on success or FALSE on failure.
Example: The following example a MySQL database called hr and table called user_details have used. It starts a transaction and then executes a command to add one row into the table user_details. The command is sent to the database and the transaction is explicitly ended with PDO::commit.
Commits a transaction, returning the database connection to auto-commit mode until the next call to PDO::beginTransaction() starts a new transaction.
Syntax:bool PDO::commit ( void )
Return Value: Returns TRUE on success or FALSE on failure.
Example: See previous (PDO::beginTransaction) example.
PDO::errorCode
PDO::errorCode retrieves the SQLSTATE (a two characters class value followed by a three characters subclass value) associated with the last operation on the database handle.
Syntax:mixed PDO::errorCode();
Return Value: Returns a five-char SQLSTATE as a string, or NULL if there was no operation on the statement handle.
Example:
In this example, the name of the column is misspelled (genderr instead of gender), causing an error. errorCode() displays the error.
Retrieves extended error information associated with the last operation on the database handle.
Syntax:array PDO::errorInfo();
Return Value: An array of error information about the last operation performed by this database handle. The array consists of the following fields:
0 : The SQLSTATE error code. 1 : The driver-specific error code. 2 : The driver-specific error message.
Example: In the following example (PostgreSQL database is used), the name of the column is misspelled (genderr instead of gender), causing an error, which is then reported.
Output:42703Array ( [0] => 42703 [1] => 7 [2] => ERROR: column “genderr” does not exist LINE 1: SELECT * FROM user_details where genderr=’M’ ^ )
PDO::exec
Execute an SQL statement and return the number of rows affected by the statement.
Syntax:int PDO::exec ($statement)
Parameters : statement – An SQL statement to prepare and execute.
Return Value: An integer reporting the number of rows affected. If no rows were affected, PDO::exec() returns 0.
Example:
In the following example (PostgreSQL database is used), the name of the column is misspelled (genderr instead of gender), causing an error, which is then reported.
<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
/* Delete some rows from the allcountry table */
$count = $connec->exec("DELETE FROM allcountry WHERE country_id = 'AR'");
/* Return number of rows */
echo("Number of deleted rows in allcountry table : ". $count);
?>
Copy
Output:Number of deleted rows in allcountry table : 1
PDO::getAttribute
Retrieves the value of a predefined PDO or driver attribute.
Syntax:mixed PDO::getAttribute ( $attribute )
Parameters: One of the PDO::ATTR_* constants. The constants that apply to database connections are as follows: PDO::ATTR_AUTOCOMMIT PDO::ATTR_CASE PDO::ATTR_CLIENT_VERSION PDO::ATTR_CONNECTION_STATUS PDO::ATTR_DRIVER_NAME PDO::ATTR_ERRMODE PDO::ATTR_ORACLE_NULLS PDO::ATTR_PERSISTENT PDO::ATTR_PREFETCH PDO::ATTR_SERVER_INFO PDO::ATTR_SERVER_VERSION PDO::ATTR_TIMEOUT
Return Value: A successful call returns the value of the requested PDO attribute. On failure, returns null.
Example: The following example (PostgreSQL database is used) retrieving database connection attributes.
Return Value: If a sequence name was not specified for the name parameter, PDO::lastInsertId() returns a string representing the row ID of the last row that was inserted into the database. If a sequence name was specified for the name parameter, PDO::lastInsertId() returns a string representing the last value retrieved from the specified sequence object. If the PDO driver does not support this capability, PDO::lastInsertId() triggers an IM001 SQLSTATE.
Example:
The following example (PostgreSQL database is used) returns the ID of the last inserted row or sequence value.
Parameters: statement: A string contains a valid SQL statement. driver_options: An array containing an attribute name and value (key=>value pairs ).
Return Value: Returns a PDOStatement object on success. On failure, returns a PDOException object, or false depending on the value of PDO::ATTR_ERRMODE.
Example – 1:
The following example prepares an SQL statement with named parameters.
<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
/* Execute a prepared statement by passing an array of values */
$sql = 'SELECT fname, lname, country FROM user_details
Rolls back the current transaction, as initiated by PDO::beginTransaction(). A PDOException will be thrown if no transaction is active.
Syntax:bool PDO::rollBack ( void )
Return Value: TRUE if the method call succeeded, FALSE otherwise.
Example:
Following example begins a transaction and issues a DROP statement before rolling back the changes. In MySQL the DROP TABLE statement automatically commits the transaction, therefore nothing will roll back.
Syntax:bool PDO::setAttribute ( int $attribute , mixed $value )
Here is a list of some of the available generic attributes:
PDO::ATTR_CASE: Force column names to a specific case.
PDO::CASE_LOWER: Force column names to lower case.
PDO::CASE_NATURAL: Leave column names as returned by the database driver.
PDO::CASE_UPPER: Force column names to upper case.
PDO::ATTR_ERRMODE: Error reporting.
PDO::ERRMODE_SILENT: Just set error codes.
PDO::ERRMODE_WARNING: Raise E_WARNING.
PDO::ERRMODE_EXCEPTION: Throw exceptions.
PDO::ATTR_ORACLE_NULLS (available with all drivers, not just Oracle): Conversion of NULL and empty strings.
PDO::NULL_NATURAL: No conversion.
PDO::NULL_EMPTY_STRING: Empty string is converted to NULL.
PDO::NULL_TO_STRING: NULL is converted to an empty string.
PDO::ATTR_STRINGIFY_FETCHES: Convert numeric values to strings when fetching. Requires bool.
PDO::ATTR_STATEMENT_CLASS: Set user-supplied statement class derived from PDOStatement. Cannot be used with persistent PDO instances. Requires array(string classname, array(mixed constructor_args)).
PDO::ATTR_TIMEOUT: Specifies the timeout duration in seconds. Not all drivers support this option, and it’s meaning may differ from driver to driver. For example, sqlite will wait for up to this time value before giving up on obtaining an writable lock, but other drivers may interpret this as a connect or a read timeout interval.
PDO::ATTR_AUTOCOMMIT (available in OCI, Firebird and MySQL): Whether to autocommit every single statement.
PDO::ATTR_EMULATE_PREPARES Enables or disables emulation of prepared statements. Some drivers do not support native prepared statements or have limited support for them. Use this setting to force PDO to either always emulate prepared statements (if TRUE), or to try to use native prepared statements (if FALSE).
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY (available in MySQL): Use buffered queries.
PDO::ATTR_DEFAULT_FETCH_MODE: Set default fetch mode.
Return Value: Returns TRUE on success or FALSE on failure.
Example:
Following example shows how to set the PDO::ATTR_ERRMODE attribute.
Closes the cursor, enabling the statement to be executed again.
Syntax:bool PDOStatement::closeCursor ( void )
Return Value: Returns TRUE on success or FALSE on failure.
Example – 1:
<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
/* Create a PDOStatement object */
$stmt = $connec->prepare('SELECT * from user_details');
/* Create a second PDOStatement object */
$otherStmt = $connec->prepare("SELECT * from usser_details where gender ='M'");
/* Execute the first statement */
$stmt->execute();
/* Fetch only the first row from the results */
$stmt->fetch();
/* The following call to closeCursor() may be required by some drivers */
$stmt->closeCursor();
/* Now we can execute the second statement */
$otherStmt->execute();
?>
Copy
PDOStatement::columnCount
Returns the number of columns in a result set.
Syntax:int PDOStatement::columnCount ( void )
Return Value: Returns TRUE on success or FALSE on failure.
Example – 1: The following example displays the number of columns of a particular table.
Fetch the SQLSTATE of the most recent operation on the database statement object.
Syntax: public string PDOStatement::errorCode ( void )
Return Value: Same to PDO::errorCode(), except that PDOStatement::errorCode() only retrieves error codes for operations performed with PDOStatement objects.
Example – 1: In the following example, SQL query has an error (wrong column name). errorCode() displays the error.
Retrieves error information associated with the last operation on the statement handle.
Syntax:array PDOStatement::errorInfo ( void )
Parameters:
Name
Description
Type
parameter
Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name.
mixed
value
The value to bind to the parameter.
mixed
data_type
Optional, PDO::PARAM_* constant.
int
Return Value: PDOStatement::errorInfo() returns an array of error information about the last operation performed by this statement handle. The array consists of the following fields:
0 – The SQLSTATE error code. 1 – The driver-specific error code. 2 – The driver-specific error message.
Example – 1 : In the following example, SQL statement has an error, which is displayed by errorinfo().
Syntax :public mixed PDOStatement::fetch ([ int $fetch_style [, int $cursor_orientation = PDO::FETCH_ORI_NEXT [, int $cursor_offset = 0 ]]] )
Parameters:
Name
Description
Type
parameter
Controls how the next row will be returned to the caller. This value must be one of the PDO::FETCH_* constants (see the constants details), defaulting to the value of PDO::ATTR_DEFAULT_FETCH_MODE (which defaults to PDO::FETCH_BOTH).
int
cursor_orientation
This value must be one of the PDO::FETCH_ORI_* constants, defaulting to PDO::FETCH_ORI_NEXT.
int
offset
For a PDOStatement object representing a scrollable cursor for which the cursor_orientation parameter is set to PDO::FETCH_ORI_ABS, this value specifies the absolute number of the row in the result set that shall be fetched.
int
Return Value: The return value of this function on success depends on the fetch type. In all cases, FALSE is returned on failure.
Example – 1: The following example shows how to fetch rows using different fetch styles.
<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$sth = $conn->prepare("SELECT fname, lname FROM user_details");
$sth->execute();
/* Exercise PDOStatement::fetch styles */
echo "PDO::FETCH_ASSOC: "."<br>";
echo "Return next row as an array indexed by column name"."<br>";
$result = $sth->fetch(PDO::FETCH_ASSOC);
print_r($result);
echo("<br>");
echo "PDO::FETCH_BOTH: "."<br>";
echo "Return next row as an array indexed by both column name and number"."<br>";
$result = $sth->fetch(PDO::FETCH_BOTH);
print_r($result);
echo("<br>");
echo "PDO::FETCH_LAZY: "."<br>";
echo "Return next row as an anonymous object with column names as properties"."<br>";
$result = $sth->fetch(PDO::FETCH_LAZY);
print_r($result);
echo("<br>");
echo "PDO::FETCH_OBJ: "."<br>";
echo "Return next row as an anonymous object with column names as properties"."<br>";
$result = $sth->fetch(PDO::FETCH_OBJ);
echo $result->fname;
?>
Copy
Output: PDO::FETCH_ASSOC: Return next row as an array indexed by column name Array ( [fname] => Scott [lname] => Rayy ) PDO::FETCH_BOTH: Return next row as an array indexed by both column name and number Array ( [fname] => Palash [0] => Palash [lname] => Ghosh [1] => Ghosh ) PDO::FETCH_LAZY: Return next row as an anonymous object with column names as properties PDORow Object ( [queryString] => SELECT fname, lname FROM user_details [fname] => Diana [lname] => Lorentz ) PDO::FETCH_OBJ: Return next row as an anonymous object with column names as properties John
PDOStatement::fetchAll
Returns an array containing the rows in a result set.
This argument have a different meaning depending on the value of the fetch_style parameter PDO::FETCH_COLUMN: Returns the indicated 0-indexed column. PDO::FETCH_CLASS: Returns instances of the specified class, mapping the columns of each row to named properties in the class. PDO::FETCH_FUNC: Returns the results of calling the specified function, using each row’s columns as parameters in the call.
mixed
ctor_args
Arguments of custom class constructor when the fetch_style parameter is PDO::FETCH_CLASS.
int
Return Value: An array of the remaining rows in the result set, or false if the method call fails.
Example – 1: The following example fetches all remaining rows in a result set
<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e)
{
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$sth = $connec->prepare("SELECT fname, lname FROM user_details");
$sth->execute();
/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);
?>
Copy
Output:Fetch all of the remaining rows in the result set: Array ( [0] => Array ( [fname] => Scott [0] => Scott [lname] => Rayy [1] => Rayy ) [1] => Array ( [fname] => Palash [0] => Palash [lname] => Ghosh [1] => Ghosh ) [2] => Array ( [fname] => Diana [0] => Diana [lname] => Lorentz [1] => Lorentz ) [3] => Array ( [fname] => John [0] => John [lname] => ray [1] => ray ) )
PDOStatement::fetchColumn
Returns one column in a row from the next row of a result set.
Syntax:string PDOStatement::fetchColumn ([ int $column_number = 0 ] )
Parameters:
Name
Description
Type
column_number
An optional integer indicating the zero-based column number. The default is 0 (the first column in the row).
mixed
Return Value: Returns TRUE on success or FALSE on failure.
Example – 1: The following example returns one column in a row.
Returns the number of rows affected (added, deleted, or changed) by the last statement.
Syntax:public int PDOStatement::rowCount ( void )
Return Value: Return the number of rows.
Example – 1: The following example shows how to execute a prepared statement with named placeholders.
<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e)
{
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
/* Delete all rows from the alluser table *
$del = $connec->prepare('DELETE FROM alluser');
$del->execute();
/* Return number of rows that were deleted */
$count = $del->rowCount();
echo "Deleted ".$count." number of rows ";
?>
Copy
Output:Deleted 4 number of rows
PDOStatement::setAttribute
Set a statement attribute. Currently, no generic attributes are set but only driver specific:
Syntax:bool PDOStatement::setAttribute ( int $attribute , mixed $value )
Return Value:
Returns TRUE on success or FALSE on failure.
PDOStatement::setFetchMode
Set the default fetch mode for this statement.
Syntax: public bool PDOStatement::setFetchMode ( int $mode ) public bool PDOStatement::setFetchMode ( int $PDO::FETCH_COLUMN , int $colno ) public bool PDOStatement::setFetchMode ( int $PDO::FETCH_CLASS , string $classname , array $ctorargs ) public bool PDOStatement::setFetchMode ( int $PDO::FETCH_INTO , object $object )
Parameters:
Name
Description
Type
mode
The fetch mode must be one of the PDO::FETCH_* constants. See the constants list.
mixed
colno
Column number.
int
classname
Class name.
string
ctorargs
Constructor arguments.
array
object
Object.
object
Return Value: Returns TRUE on success or FALSE on failure.
Example – 1: The following example demonstrates the uses of PDO::FETCH_ASSOC, PDO::FETCH_NUM, PDO::FETCH_BOTH, PDO::FETCH_LAZY, PDO::FETCH_OBJ constants.
In this page, we are going to discuss how to take backups of MySQL databases and restoring them.
Take backup by copying table files
You can simple copy the table files of a MySQL database to a separate location and accomplish a backup. Later, when necessary, you simply copy them back to the original location to restore the database.
If you are using WAMP server for MySQL, then you can find table files within wamp > bin > mysql > mysql5.1.36 > data > databaseName. Where databaseName is a folder whose name is same as your database.
If you have installed MySQL manually, then you can find table files within Program Files > MySQL >MySQL Server 5.0 > data > databaseName. Where databaseName is a folder whose name is same as your database.
Remember in mysql5.1.36 and MySQL Server 5.0, 5.1.36 and 5.0 indicates the version of the MySQL, which differs if you are working on a different version of MySQL.
Take backup of delimited text files
SELECT * INTO OUTFILE C:\\publisher_backup.txt FROM publisher;
The above MySQL statement will take a backup of the publisher table into a file called publisher_backup.txt located in the C drive of your windows system.
Using LOAD DATA INFILE statement, you can restore data from the delimited text files.
Take backup using mysqldump
mysqldump command can be executed from mysql prompt. For all the code for mysqldump commands bellow, the database is the name of the database.
Take backup of a database
mysqldump database > backup-file.sql;
Copy
Restore a database
mysql database < backup-file.sql;
Copy
Copying data from one server to another
mysqldump --opt database | mysql --host=remote_host -C database
Copy
Where remote_host indicates a remote server where you want to take backup.
mysqlhotcopy command can be used to take backup of MyISAM and ARCHIVE tables. It runs on Unix.
For all the code for mysqlhotcopy commnd bellow, the database is the name of the database.
mysqlhotcopy database [/path_to_new_directory]
Copy
Where path_to_new_directory is the directory where backup will be taken.
Save MySQL query results into a text or CSV file
Here is an easy way to write MySQL query results into a .txt or .CSV files through the select statement.
Save query result into a .txt file :
mysql> SELECT code, name, continent FROM country INTO OUTFILE 'e:/country.txt';Query OK, 239 rows affected (0.03 sec)
Copy
Sample from .txt file:
ABW Aruba North America AFG Afghanistan Asia AGO Angola Africa AIA Anguilla North America ALB Albania Europe AND Andorra Europe ANT Netherlands Antilles North America ARE United Arab Emirates Asia ARG Argentina
Save query result into a country.csv file :
mysql> SELECT code, name, continent FROM country
INTO OUTFILE 'e:/country.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';Query OK, 239 rows affected (0.00 sec)
Import and export data between MySQL and Excel has become much easier in Excel 2007 than it’s previous versions. To explore the feature, you need to install MySQL For Excel.
You can install the MySQL For Excel component while installing MySQL Community Server 6. Or you may install it as an add-on upon your existing MySQL Server installation. Installation of this component has some prerequisites, though.
MySQL for Excel Requirements
You must have following installed on your machine for installing MySQL For Excel Installer :
.NET Framework 4.0 (Client or Full Profile).
Microsoft Office Excel 2007 or greater, for Microsoft Windows.
Visual Studio Tools for Office 4.0 and MySQL Installer may install this for you.
An available MySQL Server connection.
MySQL for Excel is loaded and executed by selecting the Data menu tab in Excel, and then choosing the “MySQL for Excel” Database icon. This opens a new Excel sidebar with the available MySQL for Excel options. The navigation bar with the MySQL for Excel icon is shown in the following screen shot:
Edit MySQL Data in Excel
MySQL for Excel enables you to load and edit MySQL data directly from Microsoft Excel, or yon can do it manually by pressing Commit Changes.
The example below uses the location table of the example employee database, but the screen will look the same for any table. Within MySQL for Excel, Open a MySQL Connection, click the employee schema, Next, select the location table, click Edit MySQL Data, then choose Import to import the data into a new Microsoft Excel worksheet for editing.
Here is the step by step guide to editing and commit the data:
Step 1:
Load Microsoft Office Excel 7
Step 2:
Click on Data Tab, see the above picture, the” MySQL for Excel” Database icon will appear shown below.
Step 3:
Click on “MySQL for Excel” Database icon. It opens a new Excel sidebar with the available MySQL for Excel options. The navigation bar with the MySQL for Excel icon is shown in the following picture.:
Here our Database is employee and we are working with location table, but the screen will look the same for any table.
Step 4:
Within MySQL for Excel sidebar open a MySQL connection by double clicking. Here our connection is Local instance MySQL5.6 and with the following screen will appear for accepting the password.
Step 5:
Enter password for connection with MySQL server. The databases will show in MySQL for Excel sidebar. Our Database is employee. See the following picture.
Step 6:
Double click on your desire database, and the tables within the database will display. Our table is location. See the following picture.
Step 7:
Select the table which you want to edit, click “Edit MySQL Data” inside the navigation bar as shown above, and see the following screen shot.
Step 8:
Click on Import button as mention in the above picture, and watch the following screen shot. The data of the selected table will appear and if you place the cursor on the data range the Revert Data and Commit Changes button ( specified by a red color rectangle ) will appear otherwise not.
Step 9:
Here at our table, the first column ID is a primary key. If we change the value of ID in A19 cell 17 instead of 18 and press enter key the color of this cell will be green. Now we have changed the value of F16 cell is London and press enter key the cell color will change into green, and the color yellow at the last, indicate that the cells that accept new data. Data entered here is inserted into the MySQL table.
Step 10:
Now if we click the Revert Data button, watch the following screen shot.
Here in above picture if you click the Refresh Data from DB the data will be refresh and display the original data from DB, and if you click on Revert Changed Data, you will loose your changes just made. In this stage, if you click on Commit Changes button look the following screen shot.
Here in the above picture, you are looking an error message, and the color of A19 cell changed from green to red, that is because the ID column is the primary key and the uniqueness have been violated here. Now we returned the value of A19 cell in its original value, i.e. 18, and click on Commit Changes button, and now look the below screen shot.
In the above picture shows the color of cell F16 have converted from green to blue, that means Commit Changes done successfully. Now you can close the Excel window saving or not but the database table has been updated. You can see it to do the step again. If you checked the Auto Commit check box, the effect immediately is seen in your sheet and data will be updated in MySQL database.
Import MySQL Data into Excel
Data can be imported from MySQL into a Microsoft Excel spreadsheet by using the Import MySQL Data option after selecting either a table, view, or procedure to import.
First of all, you do the first 6 steps describe above in “Edit MySQL Data in Excel” then select your table which you want to import. Here our table is location. So select the location table and then click “Import MySQL Data” and look the appeared screen shot here in the below.
Choosing columns to import
By default, all columns are selected and will be imported. Specific columns may be selected (or unselected) using the standard Microsoft Windows method of either Control + Mouse click to select the individual columns, or Shift + Mouse click to select a range of columns.
The background color white indicates, the column or columns have been selected and they are ready to be imported, on the other hand, the gray color indicates that the columns are not selected and the column will not be imported.
Right-clicking anywhere in the preview grid opens a context-menu with either a Select None or Select All option, depending on the current status.
Importing Table
Include Column Names as Headers: By default, this option is enabled, and this treats the column names at the top of the Microsoft Excel spreadsheet as a “headers” row and will be inserted as a header.
Limit to and Rows and Start with Row : By default, this option is disabled if enabled, this limits the range of imported data. The Limit to option defaults to 1 and this limit can be changed by defines the number of rows to import. The Start with Row option defaults to 1, i.e. starting from the first row, and it can be changed by defines the number from where the import begins. Each option has a maximum value of COUNT(rows) in the table.
Now assumed that we want to import the columns LOATION_ID and CITY. Click the mouse on LOCATION_ID column and then press and hold CTRL key and click on CITY column, and look the following screen shot.
Now if we click the Import button all the rows for this two columns will be imported in Microsoft Excel Worksheet.
Assumes that, we want to import only 6 rows beginning from the 3rd row. Now look the following screen shot.
Here in the above picture shows all the columns have selected and the value of Limit to is 6, that means a number of 6 rows will be imported and the beginning of importing will start from the 3rd row because we have set the value of Start with Row is 3. Now click the Import button and look the following screen shot.
Append Excel Data into MySQL
The Microsoft Excel spreadsheet data can be appended to a MySQL database table by using the Append Excel Data to Table option.
Column mappings
Mapping the Excel columns to the MySQL columns can be executed automatically (default), manually, or by using a stored mapping routine. To append the data from Excel toMySQL the following step to be followed:
At first do th e above 6 steps of “Edit MySQL Data in Excel” then input records in excel spreadsheet compatible to the structure of MySQL table in which you want to append the records. Here is the example in which we have taken only one row, you may take more than one rows and then select the records. See the following screen shot.
Now click on the Append Excel Data to Table as mention above, and look the screen like.
Here in the above picture, you are looking two colors for the legend. One is Red and another is Green.
The Green color indicate that a source column is mapped ( mapped means the source columns in above grid created in the spreadsheet have been matched with structure of the MySQL table as target columns shown in below grid ) to a target column and here in the above picture shows all the source columns have been mapped with target columns.
If you drag any target column heading backed by green color and leave it in outside the grid, the color of the target column will be Red and the color of the source column will be Gray. See in below picture.
Here in the above picture, the target column4 became Red and source column4 became Gray.
The Red color indicates that a target column is not mapped and the Gray color indicate that, a source column is not mapped.
It is to be noted that, a source column may be mapped to multiple target columns, although this action generates a warning dialog and right-clicking on a target column shows a menu with options to either Remove Column Mapping for a single column or to Clear All Mappings for all columns.
Suppose, we are mapping manually by dragging source column4 with target column4 and column5. The target column4 will be mapping simply but when doing the process for target column5 the warning dialog will appear, show below.
Mapping methods
Here look the three mapping methods:
The automatic mapping method attempts to match the Excel source column names with the MySQL target table column names.
In the manual mapping method, source column names are manually dragged with the target column names. After the Automatic method is selected the Manual dragging method can also be performed.
You may store your own mapping styles using the Store Mapping button and saved a name like “name (dbname.tablename)” and it will be available in the Mapping Method combo box.
Stored mappings may be deleted or renamed within the Advanced Options dialog.
Advanced Options
If we click the Advanced Options button the dialog looks similar to:
Perform an automatic mapping when the dialog opens: By this options, it happens automatically to map the target and source when the Append Data dialog is opened.
Automatically store the column mapping for the given table:To check this option, stores each mapping routine after executing the Append operation. The mapping routine is saved using the “tablenameMapping (dbname.tablename)” format. It can be performed manually by using the Store Mapping button.
Reload stored column mapping for the selected table automatically: If a stored mapping routine exists which matches all column names in the source grid with the target grid, then it is automatically be loaded.
The advanced Field Data Options:
Use the first 100 (default) Excel data rows to preview and calculate data types. This determines the number of rows that the preview displays, and the values that affect the automatic mapping feature.
When you check the Use formatted values, the data from Excel is treated as Text, Double, or Date. This is enabled by default. If we make it disabled, the existing data is never treated as a Date type, so for example, this means that a date may be represented as a number.
The Stored Column Mappings is a list of saved column mappings that were saved with the “Automatically store the column mapping for the given table” feature, or manually with the Store Mapping option.
After completion all the steps if we click Append button the following screen will appear.
Export Excel Data into MySQL
Microsoft Excel spreadsheet’s data can be exported to a new MySQL database table by using the Export Excel Data to New Table option. At first, you do the first 6 steps above describe in Edit MySQL Data in Excel . Then enter some records with heading as you wish, into your Excel spreadsheet and select the records. Now look the sample records in the following picture –
Now click on Export Excel Data to New Table mention by the arrow in the above picture and look the following screen.
1 – Specify a unique name for the MySQL table. Suppose the name of the MySQL table is “invoice”
2 – If Add a Primary Key column radio button is checked an additional primary key column will be add, you can change the name of the column and by default, the data type will be an integer which is displaying in 7.
3 – When you check the Use Existing Columns radio button the column names will the available in the combo box and you can choose the primary key column from the list, and the column will be selected. Suppose you selected INVOICE_NO column, look the following picture.
Here in the above picture, the INVOICE_NO column has been selected and now you can change the Column Name (point 6), Data type (point 7) and the constraint (point 9) which are active.
4 – If you check the check box First Row Contains Column Names (point 4) the first column in your excel data will be heading otherwise it will be column1,column2,… etc
5 – Suppose you change the Data type and column name for other columns you can click on the column heading. Assumed that we want to change the Data type of INVOICE_DT, click on INVOICE_DT, and this column will be selected and then you can use point 6,7 and 9.
6 – You can change the Column Name.
7 – You can change the Data type
8 – Advance Option shows the following dialog.
Advanced Options
Use the first 100 (default) Excel data rows to preview and calculate data types: This option determines the number of rows that the preview displays and the specified values affect the feature of automatic mapping.
Analyze and try to detect correct data type based on column field contents: This option try to analyze the data and determine the data type for the column. When the column contains multiple types of data then it defined as VARCHAR.
Add additional buffer to VARCHAR length (round up to 12, 25, 45, 125, 255): When it is enabled, it detects the data type automatically and set to VARCHAR, then it find the maximum length for all rows within the column, and rounds it up the maximum length to one of the defined lengths above and when disabled, then the VARCHAR length is set to the length of the longest entry in the Excel spreadsheet.
Automatically check the Index checkbox for Integer columns: By default, this option is enabled , and then columns with an Integer data type will have the Create Index option enabled by default.
Automatically check the Allow Empty checkbox for columns without an index: By default, this option is enabled, and when enabled the columns without the Create Index checkbox checked will automatically enable the Allow Empty configuration option.
Use formatted values: By default, this option is enabled, and the data from Excel is treated as Text, Double, or Date but when disabled the data’s are independent, that is they have no specific data type.
Remove columns that contain no data, otherwise flag them as “Excluded”: When this option is enabled, columns without data in Excel are removed and not shown in the preview panel. By default, this option is disabled and then these columns will exist but have the Exclude Column option checked.
After completion, all setting click Export button the and see the following screen shot –
In MySQL, DROP TABLE command removes one or more tables from an existing database.
The user who is using the DROP command, must have DROP privilege for each table(s) he wants to drop. The command removes all the data and table definition from the database.
The IF EXIST optional clause can be used to avoid the error message occurred, when a specified table in the table list passed as argument, does not exist in the database.
RESTRICT and CASCADE
The RESTRICT and CASCADE options can be used to make porting easier.
TEMPORARY
This clause with statement drops only TEMPORARY tables. A TEMPORARY table is visible only to the session that created by the user. Using TEMPORARY is a good practice to ensure that accidentally you do not drop such a table which is important to you.
Example:
If you want to drop the table newauthor, the following sql can be used.
DROP TABLE IF EXISTS newauthor;
Copy
The above MySQL statement above will remove the ‘newauthor’ table with all data from the existing database.
MySQL DROP all TABLES or MySQL DROP DATABASE
If you want to drop all the tables from a database you need to use DROP DATABASE sql command to drops all tables in the database or empty the database. But you need to be very careful with this statement, and to use DROP DATABASE, you need the DROP privilege on the database
The clause IF EXISTS is used to prevent the display of an error message when the specific database does not exist which is going to be dropped.
The DROP DATABASE statement removes those files and directories that created by MySQL itself during the creation of database. The extension of files are – .BAK, .DAT, .HSH, .MRG, .MYD, .MYI, .TRG, .TRN, .db, .frm, .ibd, .ndb and .par .
Here are our databases.mysql> SHOW DATABASES; +——————–+ | Database | +——————–+ | information_schema | | bmcnetbank | | bupf | | employee | | empoloyee | | mucemppf | | mysql | | performance_schema | | sakila | | tempdatabase | | test | | world | +——————–+ 12 rows in set (0.00 sec)
We want to drop all the tables from the database tempdatabase.
Here the tables of the database tempdatabase.mysql> SHOW TABLES; +————————+ | Tables_in_tempdatabase | +————————+ | table1 | | table2 | +————————+ 2 rows in set (0.00 sec)
Now you can DROP all the tables in a database as well as the database. You can recreate the database again. Before you drop the database, start the MySQL server, then go the command prompt, then entering the password connect the server, in the following ‘mysql>’ prompt enter the statement
After DROP the database here is the remaining databases.mysql> SHOW DATABASES; +——————–+ | Database | +——————–+ | information_schema | | bmcnetbank | | bupf | | employee | | empoloyee | | mucemppf | | mysql | | performance_schema | | sakila | | test | | world | +——————–+ 12 rows in set (0.00 sec)
MySQL DROP multiple TABLES
Here the tables of the database tempdatabase.mysql> SHOW tables; +————————+ | Tables_in_tempdatabase | +————————+ | table1 | | table2 | | table3 | | table4 | | table5 | +————————+ 5 rows in set (0.10 sec)
If you want to delete the table table2,table4 and table5 from the database tempdatabase, the following sql can be used.mysql> DROP TABLE table2,table4,table5; Query OK, 0 rows affected (0.24 sec) mysql> SHOW TABLES; +————————+ | Tables_in_tempdatabase | +————————+ | table1 | | table3 | +————————+ 2 rows in set (0.00 sec)
MySQL DROP column
If MySQL ALTER command is used with DROP following the table column name, that column will be deleted from the table.
Example:
If we want to remove cate_descrip column from newcate table, the following statement can be used.
If you want to remove the column col1, col11 and col12 from the table table1, the following sql statement can be used.mysql> ALTER TABLE table1 drop col1, drop col11, drop col12; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0
MySQL DROP VIEW command is used to drop a view. You must have the DROP privilege for each view. If the view name specified into the DROP VIEW statement does not exists MySQL returns an error indicating by name.
The clause IF EXISTS prevents to display an error message from the operation happening for views does not exist.
RESTRICT, CASCADE
RESTRICT and CASCADE, if given, are parsed and ignored.
If you want to drop the view view_bookmast, the following sql statement can be used.DROP VIEW view_bookmast;
MySQL DROP INDEX
MySQL DROP INDEX command removes an index from a table. This statement is mapped to an ALTER TABLE statement to drop the index.
Syntax:DROP INDEX index_name ON table_name [algorithm_option | lock_option] … algorithm_option: ALGORITHM [=] {DEFAULT|INPLACE|COPY} lock_option: LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
Arguments:
Name
Description
index name
Index to be removed.
table name
Table the index belongs to.
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
DEFAULT – You needed this when you are applying ALTER TABLE in earlier versions of MySQL (< 5.6) while altering a table online. This method was used to use a temporary table while altering a table. The ALGORITHM=INPLACE continue the operation inside the InnoDB storage engines by using the in-place technique, and fail which are not support this features with an error. COPY – is the same a specifying no ALGORITHM clause at all.
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
DEFAULT – Permit a series of coincident events i.e. reads and writes when supported. Otherwise permit concurrent reads when supported else enforce exclusive access. NONE – When supported, permit concurrent reads and writes else return an error message. SHARED – When supported, allow concurrent reads but restrict writes. Remember that writes will be blocked even if concurrent writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation. When concurrent reads are not supported an error message will be returned. EXCLUSIVE – This enforce exclusive access. It happens even if concurrent reads/writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation.
Example:
If you want to drop the index newautid of newauthor table, the following sql statement can be used.
If you want to drop the indexes index_col2 and index_col5, the following sql statement can be used.mysql> ALTER TABLE table1 DROP INDEX index_col2, DROP INDEX index_col5; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0
After drop the indexes from the table table1, here is the indexes for the table1mysql> show index from table1; +——–+————+————–+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +——–+————+————–+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | table1 | 1 | index_col7 | 1 | col7 | A | 0 | NULL | NULL | YES | BTREE | | | | table1 | 1 | index_col910 | 1 | col9 | A | 0 | NULL | NULL | YES | BTREE | | | | table1 | 1 | index_col910 | 2 | col10 | A | 0 | NULL | NULL | YES | BTREE | | | +——–+————+————–+————–+————-+———–+————-+———-+——–+——+————+———+—————+ 3 rows in set (0.00 sec)
MySQL DROP FUNCTION , PROCEDURE
Click here to see DROP FUNCTION, PROCEDURE.
MySQL DROP TRIGGER
Click here to see DROP TRIGGER.
MySQL DROP EVENT
The DROP EVENT drops the event.
Here we are creating an event.mysql> CREATE EVENT testevent -> ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE -> DO -> UPDATE tempdatabase.table3 SET col1 = col1 + 1; Query OK, 0 rows affected (0.01 sec)
The above statement creates an event named testevent and it executes once, after one minute following its creation, by running an SQL statement it will increments the value of the column col1 of tempdatabse.table3 by 1.
Syntax :DROP EVENT [IF EXISTS] event_name
Arguments:
Name
Description
event_name
Name of the event
IF EXISTS
If the event does not exist, the error ERROR 1517 (HY000): Unknown event ‘event_name’ results will display. To prevent this error message for nonexistent events the IF EXISTS clause can be used.
I you want to remove the event testevent the following sql statement can be used.mysql> DROP EVENT IF EXISTS testevent; Query OK, 0 rows affected (0.00 sec)
MySQL DROP PRIMARY KEY
Here is the structure of the sample table.mysql> DESCRIBE table1; +——-+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——-+————-+——+—–+———+——-+ | col2 | varchar(15) | NO | PRI | | | | col4 | int(5) | YES | MUL | NULL | | | col3 | int(10) | YES | MUL | NULL | | | col5 | int(5) | YES | MUL | NULL | | | col6 | int(5) | YES | MUL | NULL | | | col7 | int(5) | YES | MUL | NULL | | | col8 | int(5) | YES | | NULL | | | col9 | int(10) | YES | MUL | NULL | | | col10 | int(5) | YES | | NULL | | +——-+————-+——+—–+———+——-+ 9 rows in set (0.01 sec)
Here in the above table structure of table1 shows col2 is the PRIMARY KEY, which is indicated by red color.
If you want to drop the PRIMARY KEY on col2, the following sql can be used.mysql> DROP INDEX PRIMARY ON table1; Query OK, 0 rows affected (0.81 sec) Records: 0 Duplicates: 0 Warnings: 0
Now look the structure of the table table1 again.mysql> describe table1; +——-+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——-+————-+——+—–+———+——-+ | col2 | varchar(15) | NO | MUL | | | | col4 | int(5) | YES | MUL | NULL | | | col3 | int(10) | YES | MUL | NULL | | | col5 | int(5) | YES | MUL | NULL | | | col6 | int(5) | YES | MUL | NULL | | | col7 | int(5) | YES | MUL | NULL | | | col8 | int(5) | YES | | NULL | | | col9 | int(10) | YES | MUL | NULL | | | col10 | int(5) | YES | | NULL | | +——-+————-+——+—–+———+——-+ 9 rows in set (0.01 sec)
There are various forms of MySQL SHOW commands, which provides information about databases, tables, columns, or status information about the commands. See the following section:
SHOW BINARY LOGS statement is used to list the binary log files on the server. Here is the syntax:SHOW BINARY LOGS
See the following examples:mysql> SHOW BINARY LOGS; +—————+———–+ | Log_name | File_size | +—————+———–+ | binlog.000015 | 724935 | | binlog.000016 | 733481 | +—————+———–+
MySQL: SHOW BINLOG EVENTS
SHOW BINLOG EVENTS statement shows the events in the binary log. Here is the syntax:SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
If you omit ‘log_name’, the first binary log is displayed.
The LIMIT clause can be used to constrain the number of rows returned by the statement.
MySQL: SHOW CHARACTER SET
SHOW CHARACTER SET statement is used to check all available character sets. Here is the syntax :SHOW CHARACTER SET [LIKE ‘pattern’ | WHERE expr]
The optional LIKE clause, if present, shows the matched character set. With WHERE clause you can use a condition.
See the following examples :mysql> SHOW CHARACTER SET; +———-+—————————–+———————+——–+ | Charset | Description | Default collation | Maxlen | +———-+—————————–+———————+——–+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | …….. 40 rows in set (0.03 sec)
Let see all the character set starting with ‘utf”mysql> SHOW CHARACTER SET LIKE ‘utf%’; +———+——————+——————–+——–+ | Charset | Description | Default collation | Maxlen | +———+——————+——————–+——–+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | +———+——————+——————–+——–+ 5 rows in set (0.00 sec)
Let see all the character set starting with ‘utf” and Maxlen is 3:
https://googleads.g.doubleclick.net/pagead/ads?gdpr=0&us_privacy=1—&gpp_sid=-1&client=ca-pub-2153208817642134&output=html&h=280&adk=2413866252&adf=1810428289&pi=t.aa~a.4097345806~i.57~rp.4&w=715&fwrn=4&fwrnh=100&lmt=1706467649&num_ads=1&rafmt=1&armr=3&sem=mc&pwprc=5856759792&ad_type=text_image&format=715×280&url=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-show.php&fwr=0&pra=3&rh=179&rw=715&rpe=1&resp_fmts=3&wgl=1&fa=27&uach=WyJXaW5kb3dzIiwiMTAuMC4wIiwieDg2IiwiIiwiMTA2LjAuNDk5OC41MiIsbnVsbCwwLG51bGwsIjY0IixbWyJOb3RfQSBCcmFuZCIsIjguMC4wLjAiXSxbIkNocm9taXVtIiwiMTIwLjAuNjA5OS4yMzQiXSxbIk9wZXJhIiwiMTA2LjAuNDk5OC41MiJdXSwxXQ..&dt=1706467506716&bpp=13&bdt=4166&idt=14&shv=r20240122&mjsv=m202401240101&ptt=9&saldr=aa&abxe=1&cookie=ID%3D6de4a56fe4484587%3AT%3D1706354011%3ART%3D1706420688%3AS%3DALNI_MYWYkKy5gAvXyEH7W4ZN6WCTnP0sA&gpic=UID%3D00000d0223079700%3AT%3D1706354011%3ART%3D1706420688%3AS%3DALNI_MaBKEehqphuMfn0yJfqUi_NOyR70w&prev_fmts=468×80%2C304x250%2C300x600%2C300x600%2C0x0%2C715x280%2C1297x644&nras=4&correlator=4273584465680&frm=20&pv=1&ga_vid=1729879426.1706400821&ga_sid=1706467505&ga_hid=1814021088&ga_fc=1&u_tz=-480&u_his=18&u_h=768&u_w=1366&u_ah=728&u_aw=1366&u_cd=24&u_sd=1&dmc=8&adx=238&ady=577&biw=1312&bih=644&scr_x=0&scr_y=0&eid=44759876%2C44759927%2C44759837%2C44795922%2C31080663%2C95320377%2C95320888%2C95321627%2C95322163%2C95323009&oid=2&pvsid=825911308207069&tmod=925717838&uas=1&nvt=1&ref=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-security.php&fc=384&brdim=0%2C0%2C0%2C0%2C1366%2C0%2C1366%2C728%2C1312%2C644&vis=1&rsz=%7C%7Cs%7C&abl=NS&fu=128&bc=31&bz=1.04&psd=W251bGwsbnVsbCxudWxsLDNd&ifi=10&uci=a!a&fsb=1&dtd=Mmysql> SHOW CHARACTER SET WHERE Maxlen=3; +———+—————————+———————+——–+ | Charset | Description | Default collation | Maxlen | +———+—————————+———————+——–+ | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | +———+—————————+———————+——–+ 3 rows in set (0.00 sec)
MySQL: SHOW COLLATION
SHOW COLLATION statement is used to list collations (a collation is a set of rules for comparing characters in a character set) supported by the server. Here is the syntax:SHOW COLLATION SET [LIKE ‘pattern’ | WHERE expr]
The optional LIKE clause, if present, shows the matched collations.
The SHOW COLUMNS statement is used to display information about the columns in a given table. Here is the syntax:SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr]
The optional LIKE clause, if present shows the matched column names. With WHERE clause you can use a condition.
See the following examples:mysql> SHOW COLUMNS FROM user_details; +————-+————–+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +————-+————–+——+—–+———+——-+ | userid | varchar(16) | NO | PRI | NULL | | | password | varchar(16) | NO | | NULL | | | fname | varchar(100) | NO | | NULL | | | lname | varchar(100) | NO | | NULL | | | gender | varchar(1) | NO | | NULL | | | dtob | date | NO | | NULL | | | country | varchar(30) | NO | | NULL | | | user_rating | int(4) | NO | | NULL | | | emailid | varchar(60) | NO | | NULL | | +————-+————–+——+—–+———+——-+ 9 rows in set (0.25 sec)
MySQL: SHOW CREATE DATABASE
SHOW CREATE DATABASE statement is used to show CREATE DATABASE statement.SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name See the following example:mysql> SHOW CREATE DATABASE hr; +———-+————————————————————-+ | Database | Create Database | +———-+————————————————————-+ | hr | CREATE DATABASE hr /*!40100 DEFAULT CHARACTER SET utf8 */ | +———-+————————————————————-+ 1 row in set (0.00 sec)
MySQL : SHOW CREATE EVENT
This statement displays the CREATE EVENT statement needed to re-create a given event. It requires the EVENT privilege for the database from which the event is to be shown.SHOW CREATE EVENT event_name
MySQL : SHOW CREATE FUNCTION
SHOW CREATE FUNCTION statement is used to get the exact string that can be used to re-create the named stored function.
Here is the syntax:SHOW CREATE FUNCTION func_name
Here is the statement to create a function ‘test1’mysql> CREATE FUNCTION test1 (aa CHAR(25)) -> RETURNS CHAR(50) DETERMINISTIC -> RETURN CONCAT(‘w3resource.’,aa,’ sites’); -> // Query OK, 0 rows affected (0.00 sec)
Here is the following statement of SHOW CREATE FUNCTION.SHOW CREATE FUNCTION test1\G
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:mysql> SHOW CREATE FUNCTION test1\G *************************** 1. row *************************** Function: test1 sql_mode: Create Function: CREATE DEFINER=root@localhost FUNCTION test1(aa CHAR(25)) RETURNS char(50) CHARSET latin1 DETERMINISTIC RETURN CONCAT(‘w3resource.’,aa,’ sites’) character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
MySQL: SHOW CREATE PROCEDURE
SHOW CREATE PROCEDURE statement is used to get the exact string that can be used to re-create the named stored procedure. The statement requires that you must be the owner of the routine.
Here is the syntax:SHOW CREATE PROCEDURE proc_name
Here is the statement to create a procedure ‘myprocedure’mysql> CREATE PROCEDURE myprocedure (OUT emp_ctr INT) -> BEGIN -> SELECT COUNT(*) INTO emp_ctr FROM empinfo.employees; -> END// Query OK, 0 rows affected (0.00 sec)
Here is the following statement of SHOW CREATE PROCEDURE.SHOW CREATE PROCEDURE empinfo.myprocedure\G
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:mysql> SHOW CREATE PROCEDURE empinfo.myprocedure\G *************************** 1. row *************************** Procedure: myprocedure sql_mode: Create Procedure: CREATE DEFINER=root@localhost PROCEDURE myprocedure(OUT emp_ctr INT) BEGIN SELECT COUNT(*) INTO emp_ctr FROM empinfo.employees; END character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
MySQL: SHOW CREATE TABLE
SHOW CREATE TABLE statement is used to show the create table statement.
Here is the syntax:SHOW CREATE TABLE table_name;
See the following example:SHOW CREATE TABLE regions\G;
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:mysql> SHOW CREATE TABLE regions\G *************************** 1. row *************************** Table: regions Create Table: CREATE TABLE regions ( REGION_ID decimal(5,0) NOT NULL, REGION_NAME varchar(25) DEFAULT NULL, PRIMARY KEY (REGION_ID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
MySQL: SHOW CREATE TRIGGER
SHOW CREATE TRIGGERS statement is used to show the CREATE TRIGGER statement that creates the named trigger.
Here is the syntax:SHOW CREATE TRIGGER trigger_name
See the following example:mysql> SHOW CREATE TRIGGER ins_sum\G; *************************** 1. row *************************** Trigger: ins_sum sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=root@::1 TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.02 sec) ERROR: No query specified
MySQL: SHOW CREATE VIEW
SHOW CREATE VIEW statement is used to show the create view statement.
Here is the syntax :SHOW CREATE VIEW view_name;
See the following example:SHOW CREATE VIEW myview\G;
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:mysql> SHOW CREATE VIEW MYVIEW\G; *************************** 1. row *************************** View: myview Create View: CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW myview AS select departments.DEPARTMENT_ID AS DEPARTMENT_ID, departments.DEPARTMENT_NAME AS DEPARTMENT_NAME, departments.MANAGER_ID AS MANAGER_ID, departments.LOCATION_ID AS LOCATION_ID from departments character_set_client: latin1 collation_connection: latin1_swedish_ci 1 row in set (0.13 sec)
MySQL: SHOW DATABASES
SHOW DATABASES statement is used to lists the databases on the MySQL server host. The SHOW SCHEMAS can be used as a synonym for SHOW DATABASES.
Here is the syntax :SHOW {DATABASES | SCHEMAS} [LIKE ‘pattern’ | WHERE expr]
If the LIKE clause is present along with the SHOW DATABASES, indicates which database names to match.
See the following example:SHOW DATABASES;
Let execute the above and see the output:
Sample Output:mysql> SHOW DATABASES; +——————–+ | Database | +——————–+ | information_schema | | bookinfo | | bupayroll | | bupf | | empinfo | | mucpf | | mucstuinfo | | mysql | +——————–+ 8 rows in set (0.16 sec)
Here is the alternate statement of SHOW DATABASES:mysql> SHOW SCHEMAS; +——————–+ | Database | +——————–+ | information_schema | | bookinfo | | bupayroll | | bupf | | empinfo | | mucpf | | mucstuinfo | | mysql | +——————–+ 8 rows in set (0.02 sec)
Here is the example of SHOW DATABASES using LIKE.mysql> SHOW DATABASES LIKE ‘m%’; +—————+ | Database (m%) | +—————+ | mucpf | | mucstuinfo | | mysql | +—————+ 3 rows in set (0.03 sec)
MySQL : SHOW ENGINE
The SHOW ENGINE statement is used to display operational information about a storage engine.
Here is the syntax:SHOW ENGINE INNODB STATUS SHOW ENGINE INNODB MUTEX SHOW ENGINE {NDB | NDBCLUSTER} STATUS SHOW ENGINE PERFORMANCE_SCHEMA STATUS
See the following example:SHOW ENGINE INNODB STATUS\G;
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:mysql> SHOW ENGINE INNODB STATUS\G; *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 130729 18:26:13 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 11 seconds ———- SEMAPHORES ———- OS WAIT ARRAY INFO: reservation count 3, signal count 3 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 4, OS waits 2; RW-excl spins 1, OS waits 1 ———— TRANSACTIONS ———— Trx id counter 0 1792 Purge done for trx’s n:o < 0 0 undo n:o < 0 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: —TRANSACTION 0 0, not started, OS thread id 3512 MySQL thread id 1, query id 16 localhost 127.0.0.1 root SHOW ENGINE INNODB STATUS ——– FILE I/O ——– I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0 Pending flushes (fsync) log: 0; buffer pool: 0 29 OS file reads, 3 OS file writes, 3 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ————————————- INSERT BUFFER AND ADAPTIVE HASH INDEX ————————————- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s — LOG — Log sequence number 0 46419 Log flushed up to 0 46419 Last checkpoint at 0 46419 0 pending log writes, 0 pending chkp writes 8 log i/o’s done, 0.00 log i/o’s/second ———————- BUFFER POOL AND MEMORY ———————- Total memory allocated 14857048; in additional pool allocated 857856 Dictionary memory allocated 20024 Buffer pool size 512 Free buffers 493 Database pages 19 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 19, created 0, written 0 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout ————– ROW OPERATIONS ————– 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 1032, state: waiting for server activity Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s —————————- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec)
MySQL: SHOW ENGINES
The SHOW ENGINES statement is used to display the status information about the server’s storage engines. It is important for checking whether a storage engine is supported, or what the default engine is.
Here is the syntax:SHOW [STORAGE] ENGINES;
See the following example:SHOW ENGINES\G
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output :
Sample Output:mysql> SHOW ENGINES\G *************************** 1. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 2. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL *************************** 3. row *************************** Engine: MyISAM Support: DEFAULT Comment: Default engine as of MySQL 3.23 with great performance Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: InnoDB Support: YES Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES 8 rows in set (0.00 sec)
MySQL : SHOW ERRORS
The SHOW ERRORS statement is used to display the errors, warnings, and notes. This statement is almost similar to SHOW WARNINGS except displaying errors.
Here is the syntax:SHOW ERRORS [LIMIT [offset,] row_count] SHOW COUNT(*) ERRORS
The LIMIT clause can be used to specify the number of rows to be retrieved.
The offset is an argument, which LIMIT takes optionally to retrieve the number of rows. When mention two arguments, the first one is from a particular position and the second one is a number of rows after the first one position.
The offset of the initial row is 0 (not 1)
The SHOW COUNT(*) ERRORS statement is used to displays the number of errors.
See the following example:
Here, in the below statement, there is an error. Execute this statement an error message will be generated.
SHOW DATABASE;
Copy
and now, here is the statement-
SHOW ERRORS\G
Copy
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:mysql> SHOW ERRORS\G *************************** 1. row *************************** Level: Error Code: 1064 Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘database’ at line 1 1 row in set (0.02 sec)
SHOW COUNT(*) ERRORS;
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW COUNT(*) ERRORS; +———————–+ | @@session.error_count | +———————–+ | 1 | +———————–+ 1 row in set (0.11 sec)
Number of errors can also be retrieve using the error_count variable in a SELECT statement.
SELECT @@error_count;
Copy
Let execute the above and see the output:
Sample Output:mysql> SELECT @@error_count; +—————+ | @@error_count | +—————+ | 1 | +—————+ 1 row in set (0.06 sec)
MySQL: SHOW EVENTS
The SHOW EVENTS statement is used to display information about Event Manager events. It requires the EVENT privilege for the database from which the events are to be shown.
Here is the syntax:SHOW EVENTS [{FROM | IN} schema_name] [LIKE ‘pattern’ | WHERE expr]
This SHOW FUNCTION STATUS statement returns the characteristics of a stored function, such as the database, name, type, creator, creation and modification dates, and character set information.
Here is the syntax:SHOW FUNCTION STATUS [LIKE ‘pattern’ | WHERE expr]
See the following example.
SHOW FUNCTION STATUS\G
Copy
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output :
Sample Output:mysql> SHOW FUNCTION STATUS\G *************************** 1. row *************************** Db: empinfo Name: test1 Type: FUNCTION Definer: root@localhost Modified: 2013-07-31 17:03:05 Created: 2013-07-31 17:03:05 Security_type: DEFINER Comment: character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
Let execute the example below using pattern matching and see the output:
Sample Output:mysql> SHOW FUNCTION STATUS like ‘tes%’\G *************************** 1. row *************************** Db: empinfo Name: test1 Type: FUNCTION Definer: root@localhost Modified: 2013-07-31 17:03:05 Created: 2013-07-31 17:03:05 Security_type: DEFINER Comment: character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
Let execute the example below using where and see the output:
Sample Output:mysql> SHOW FUNCTION STATUS WHERE Db = ’empinfo’\G *************************** 1. row *************************** Db: empinfo Name: test1 Type: FUNCTION Definer: root@localhost Modified: 2013-07-31 17:03:05 Created: 2013-07-31 17:03:05 Security_type: DEFINER Comment: character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
MySQL: SHOW GRANTS
The SHOW GRANTS statement is used to list 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; If you specify only the username part of the account name, a host name part of ‘%’ is used.
Here is the syntax:SHOW GRANTS [FOR user]
See the following example.
SHOW GRANTS FOR 'root'@'localhost';
Copy
Let execute the above and see the output:
Sample Output:+———————————————————————+ | Grants for root@localhost | +———————————————————————+ | GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ WITH GRANT OPTION | +———————————————————————+ 1 row in set (0.03 sec)
Here is the another example of not using the hostnamemysql> SHOW GRANTS FOR ‘root’; +————————————————————-+ | Grants for root@% | +————————————————————-+ | GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ WITH GRANT OPTION | +————————————————————-+ 1 row in set (0.00 sec)
In the above example, only the username have specified as the part of the account name that is why in host name part a ‘%’ have appeared.
MySQL : SHOW INDEX
The SHOW INDEX statement returns the information of index of a table.
Here is the syntax :SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
Here is another example of SHOW INDEX statement using where clausemysql> SHOW INDEX FROM employees -> FROM empinfo WHERE column_name=’employee_id’; +———–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +———–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | employees | 0 | PRIMARY | 1 | EMPLOYEE_ID | A | 107 | NULL | NULL | | BTREE | | +———–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ 1 row in set (0.00 sec)
Here are alternate statements for the above output
SHOW INDEX
FROM empinfo.employees
WHERE column_name='employee_id';
Copy
SHOW KEYS
FROM empinfo.employees
WHERE column_name='employee_id';
Copy
SHOW KEYS
IN employees
WHERE column_name='employee_id';
Copy
MySQL: SHOW MASTER STATUS
The SHOW MASTER STATUS statement provides status information about the binary log files of the master. It requires either the SUPER or REPLICATION CLIENT privilege.
Here is the syntax :SHOW MASTER STATUS
See the following example.
SHOW MASTER STATUS\G
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW MASTER STATUS\G *************************** 1. row *************************** File: mysql-bin.000129 Position: 106 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.03 sec)
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
MySQL: SHOW OPEN TABLES
SHOW OPEN TABLES statement is used to list the non-TEMPORARY tables that are currently open in the table cache. Here is the syntax :
https://www.adsensecustomsearchads.com/afs/ads?psid=5134551505&channel=AutoRsVariant&cx=r-440389826592af9d2&fexp=44759876%2C44759927%2C44759837%2C44795922%2C31080663%2C95320377%2C95320888%2C95321627%2C95322163%2C95323009%2C0%2C21404%2C17301383%2C71847096&client=pub-2153208817642134&r=m&sct=ID%3Df1c5d672aa31266c%3AT%3D1706354011%3ART%3D1706354011%3AS%3DALNI_MZlJMMg_q5l3r_1tPiuFzhttpHLOQ&sc_status=6&hl=en&rpbu=http%3A%2F%2Fgoogle.com&rpqp=q&type=3&rs_tt=c&oe=UTF-8&ie=UTF-8&format=r5&nocache=6721706467507153&num=0&output=afd_ads&domain_name=www.w3resource.com&v=3&bsl=10&pac=0&u_his=17&u_tz=-480&dt=1706467507157&u_w=1366&u_h=768&biw=1297&bih=644&psw=1297&psh=644&frm=0&cl=600476684&uio=-&cont=autors-container-0&drt=0&jsid=csa&jsv=600476684&rurl=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-show.php&referer=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-security.phpSHOW OPEN TABLES [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr]
See the following example.
SHOW OPEN TABLES FROM empinfo;
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW OPEN TABLES FROM empinfo; +———–+————-+——–+————-+ | Database | Table | In_use | Name_locked | +———–+————-+——–+————-+ | empinfo | employees | 0 | 0 | | empinfo | departments | 0 | 0 | +———–+————-+——–+————-+ 2 rows in set (0.02 sec)
The SHOW OPEN TABLES output has the following columns:
Database – the name of the database currently being used.
Table – name of the non temporary table(s) currently opened.
In_use – The number of table locks or lock requests there is in the database for the table.
Name_locked – Whether the table name is locked. Name locking is used for the operations like dropping or renaming tables.
If there are no privileges for a table, it does not show up in the output from SHOW OPEN TABLES.
Here is the another example of pattern matching
SHOW OPEN TABLES FROM employee LIKE 'e%';
Copy
Let execute the above and see the output :
Sample Output:mysql> SHOW OPEN TABLES FROM employee LIKE ‘e%’; +———–+———–+——–+————-+ | Database | Table | In_use | Name_locked | +———–+———–+——–+————-+ | employee | employees | 0 | 0 | +———–+———–+——–+————-+ 1 row in set (0.00 sec)
MySQL: SHOW PLUGINS
The SHOW PLUGINS statement is used to display the information about server plugins. The information or Plugins is also available in the INFORMATION_SCHEMA.PLUGINS table.
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
MySQL : SHOW PRIVILEGES
The SHOW PRIVILEGES statement shows the list of system privileges that the MySQL server supports. The exact list of privileges depends on the version of the server which you are using.
Here is the syntax:SHOW PRIVILEGES
See the following example.
SHOW PRIVILEGES\G
Copy
Let execute the above and see the output :
Sample Output:mysql> SHOW PRIVILEGES\G *************************** 1. row *************************** Privilege: Alter Context: Tables Comment: To alter the table *************************** 2. row *************************** Privilege: Alter routine Context: Functions,Procedures Comment: To alter or drop stored functions/procedures *************************** 3. row *************************** Privilege: Create Context: Databases,Tables,Indexes Comment: To create new databases and tables *************************** 4. row *************************** Privilege: Create routine Context: Databases Comment: To use CREATE FUNCTION/PROCEDURE *************************** 5. row *************************** Privilege: Create temporary tables Context: Databases Comment: To use CREATE TEMPORARY TABLE *************************** 6. row *************************** Privilege: Create view Context: Tables Comment: To create new views ….
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
MySQL : SHOW PROCEDURE CODE
The SHOW PROCEDURE CODE statement is used to display the internal implementation of the named stored procedure. This statement is a is available only for servers that have been built with debugging support.
Here is the syntax:SHOW PROCEDURE CODE proc_name
See the following example, here the server has not built with debugging support.mysql> SHOW PROCEDURE CODE job_data; ERROR 1289 (HY000): The ‘SHOW PROCEDURE|FUNCTION CODE’ feature is disabled; you need MySQL built with ‘–with-debug’ to have it working
MySQL : SHOW PROCEDURE STATUS
This SHOW PROCEDURE STATUS statement returns the characteristics of a stored procedure, such as the database, name, type, creator, creation and modification dates, and character set information.
Here is the syntax:SHOW PROCEDURE STATUS [LIKE ‘pattern’ | WHERE expr]
See the following example.
SHOW PROCEDURE STATUS\G
Copy
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
The SHOW PROCESSLIST statement shows you which threads are running. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.
Here is the syntax:SHOW [FULL] PROCESSLIST
See the following example.
SHOW PROCESSLIST\G
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW FULL PROCESSLIST\G *************************** 1. row *************************** Id: 1 User: root Host: localhost:1300 db: NULL Command: Query Time: 0 State: NULL Info: SHOW FULL PROCESSLIST 1 row in set (0.00 sec)
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout.
MySQL : SHOW RELAYLOG EVENTS
The SHOW RELAYLOG statement shows the events in the relay log of a replication slave. If you do not specify ‘log_name’, the first relay log is displayed.
Here is the syntax:SHOW RELAYLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
If ‘log_name’, is not specified the first relay log is displayed. This statement has no effect on the master.
MySQL: SHOW SLAVE HOSTS
SHOW SLAVE HOSTS statement is used to display a list of replication slaves currently registered with the master.
Here is the syntax:SHOW SLAVE HOSTS
MySQL: SHOW SLAVE STATUS
The SHOW SLAVE STATUS statement provides status information on essential parameters of the slave threads.
Here is the syntax:SHOW SLAVE STATUS
The statement requires either the SUPER or REPLICATION CLIENT privilege.
MySQL : SHOW STATUS
The SHOW STATUS statement provides the information of server status. The LIKE clause along with this statement helps to match the specific variable. The usage of WHERE clause can fetch rows against general conditions. This statement does not require any privilege.
Here is the syntax :SHOW [GLOBAL | SESSION] STATUS [LIKE ‘pattern’ | WHERE expr]
Sample Output:mysql> SHOW STATUS LIKE ‘Qca%’; +————————-+——-+ | Variable_name | Value | +————————-+——-+ | Qcache_free_blocks | 0 | | Qcache_free_memory | 0 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 0 | +————————-+——-+ 8 rows in set (0.00 sec)
MySQL : SHOW TABLE STATUS
The SHOW TABLE STATUS statement provides a lot of information about each non-TEMPORARY table. The LIKE clause, if present, indicates which table names to match. The usage of WHERE clause can fetch rows against general conditions.
Here is the syntax:SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr]
SHOW TABLES lists the non-TEMPORARY tables in a given database. The LIKE clause, if present, indicates which table names to match. The usage of WHERE clause can fetch rows against general conditions.
Here is the syntax :SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr]
Here is the another example of SHOW TABLES with pattern matching
SHOW TABLES FROM empinfo LIKE 'e%';
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW TABLES FROM empinfo LIKE ‘e%’; +————————–+ | Tables_in_empinfo (e%) | +————————–+ | employees | +————————–+ 1 row in set (0.02 sec)
Here is the another example of SHOW TABLES with WHERE clause
SHOW TABLES FROM empinfo
WHERE Tables_in_empinfo='employees';
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW TABLES FROM empinfo WHERE Tables_in_empinfo=’employees’; +———————+ | Tables_in_empinfo | +———————+ | employees | +———————+ 1 row in set (0.00 sec)
MySQL : SHOW TRIGGERS
SHOW TRIGGERS statement is used to list the triggers currently defined for tables in a database
Here is the syntax:SHOW TRIGGERS [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr]
MySQL : SHOW VARIABLES
The SHOW VARIABLES statement shows the values of MySQL system variables. The LIKE clause, if present, indicates which table names to match. The usage of WHERE clause can fetch rows against general conditions. This statement does not require any privilege. It requires only the ability to connect to the server.
Here is the syntax:SHOW [GLOBAL | SESSION] VARIABLES [LIKE ‘pattern’ | WHERE expr]
Here is another example of SHOW VARIABLES with GLOBAL
SHOW GLOBAL VARIABLES;
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW GLOBAL VARIABLES; +—————————————–+———————————————-+ | Variable_name | Value | +—————————————–+———————————————-+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | c:\wamp\bin\mysql\mysql5.1.36\ | | big_tables | OFF | …
Here is another example of SHOW VARIABLES with LIKE
SHOW VARIABLES LIKE 'time%';
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW VARIABLES LIKE ‘time%’; +—————+————+ | Variable_name | Value | +—————+————+ | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | timestamp | 1375447849 | +—————+————+ 4 rows in set (0.00 sec)
Here is another example of SHOW VARIABLES with LIKE
SHOW VARIABLES LIKE 'time%';
Copy
MySQL: SHOW WARNINGS
The SHOW WARNINGS statement is used to display the warnings,errors, and notes that resulted from the last statement in the current session that generated messages. It shows nothing if the last statement does not generate any message.
Here is the syntax :SHOW WARNINGS [LIMIT [offset,] row_count] SHOW COUNT(*) WARNINGS
The LIMIT clause can be used to specify the number of rows to be retrieved.
The offset is an argument, which LIMIT takes optionally to retrieve the number of rows. When mention two arguments, the first one is from a particular position and the second one is a number of rows after the first one position.
The offset of the initial row is 0 (not 1)
The SHOW COUNT(*) WARNINGS statement is used to displays the number of warnings.
See the following example:
Here, in the below statement, there is an error. Execute this statement an error message will be generated.
SELECT * FORM employees;
Copy
and now, here is the statement-
SHOW WARNINGS\G
Copy
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Error Code: 1064 Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FORM EMPLOYEES’ at line 1 1 row in set (0.00 sec)
SHOW COUNT(*) WARNINGS;
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW COUNT(*) WARNINGS; +————————-+ | @@session.warning_count | +————————-+ | 1 | +————————-+ 1 row in set (0.13 sec)
A number of warnings can also be retrieve using the warning_count variable in a SELECT statement.
SELECT @@warning_count;
Copy
Let execute the above and see the output:
Sample Output:mysql> SELECT @@warning_count; +—————–+ | @@warning_count | +—————–+ | 1 | +—————–+ 1 row in set (0.03 sec)
Database security entails allowing or disallowing user actions on the database and the objects within it. When you will create an database application, the security policy is the first step. An application security policy is a list of application security requirements and rules that regulate user access to database objects. This chapter discusses aspects of application security and MySQL Database features which contains the following topics :
Contents:
MySQL General Security Issues
The MySQL Access Privilege System
MySQL User Account Management
MySQL general security issues
Security Guidelines :
Except MySQL root account does not permit anyone to access the user table in the MySQL database.
Use the GRANT and REVOKE statements to control access to MySQL. Do not grant unnecessary privileges and never grant privileges to all hosts.
Never store simple text passwords in your database. Store the hash value using SHA2(), SHA1(), MD5() functions or other hashing function in a different way. Try to use a complex password.
Try to use a firewall and put MySQL behind the firewall.
3306 is the default user port of MySQL and this port should not be accessible from untrusted hosts. You can scan the ports from Internet using a tool such as nmap. From a remote machine you can check whether the port is open or not with this command: shell> telnet server_host 3306. If telnet hangs or the connection is refused, the port is blocked. If you get a connection and some garbage characters, the port is open and should be closed on your firewall or router, unless you really have a good reason to keep it open.
Some applications access MySQL database for different a purpose. Never trust these input data entered by the user and must validate properly before access database.
Do not transmit unencrypted data over the Internet. Use an encrypted protocol such as SSL (MySQL supports internal SSL connections) or SSH.
Use tcpdump and strings utilities. By issuing this command shell> tcpdump -l -i eth0 -w – src or dst port 3306 | strings you can check whether MySQL data streams are unencrypted or not.
Keeping Passwords Secure:
End-User Guidelines for Password Security
Use the -p or –password option on the command line with no password value specified. Here is the commandshell> mysql -u user_id -p database_name Enter password : *********** When you input the password it will not visible.
Store your password in an option file. For example Unix you can list your password in [client] section of the .my.cnf file in your home directory and to keep password safe, set the file access mode to 400 or 600.
Administrator Guidelines for Password Security : MySQL stores passwords for user accounts in the mysql.user table. Therefore this table should not be accessed by any nonadministrative accounts. User account password must reset time to time.
Passwords and Logging : Passwords can be written as plain text in SQL statements such as CREATE USER, GRANT, and SET PASSWORD, or statements that invoke the PASSWORD() function. If these statements are logged by the MySQL server as written, such passwords become available to anyone with access to the logs. Beginning with MySQL 5.6.3, statement logging is modified so that passwords do not appear in plain text for the following statements:CREATE USER … IDENTIFIED BY …GRANT … IDENTIFIED BY …SET PASSWORD …SLAVE START … PASSWORD = … (as of 5.6.4)CREATE SERVER … OPTIONS(… PASSWORD …) (as of 5.6.9)ALTER SERVER … OPTIONS(… PASSWORD …) (as of 5.6.9) Passwords in those statements are rewritten not to appear literally in statement text, for the general query log, slow query log, and binary log. Rewriting does not apply to other statements.
Password Hashing in MySQL : MySQL lists user accounts in the user table of the MySQL database. Each MySQL account can be assigned a password, although the user table does not store the cleartext version of the password, but a hash value computed from it.
Implications of Password Hashing Changes in MySQL 4.1 for Application Programs : An upgrade to MySQL version 4.1 or later can cause compatibility issues for applications that use PASSWORD() to generate passwords for their own purposes. Applications really should not do this, because PASSWORD() should be used only to manage passwords for MySQL accounts.
The validate_password plugin (available as of MySQL 5.6.6) can be used to test passwords and improve security.
Making MySQL Secure Against Attackers :
To make a MySQL system secure, you should maintain the following suggestions :
Require all MySQL accounts to have a password.
Make sure that the only Unix user account with read or write privileges in the database directories is the account that is used for running mysqld.
Never run the MySQL server as the Unix root user
Do not grant the FILE privilege to nonadministrative users
Do not permit the use of symlinks to tables.
Stored programs and views should be written using the security guidelines
If you do not trust your DNS, you should use IP addresses rather than hostnames in the grant tables.
If you want to restrict the number of connections permitted to a single account, you can do so by setting the max_user_connections variable in mysqld.
Security-Related mysqld Options and Variables :
The following table shows mysqld options and system variables that affect security.
Name
Description
Cmd-Line
Option file
System Var
Var Scope
Dynamic
allow-suspicious-udfs
This option controls whether user-defined functions that have only an xxx symbol for the main function can be loaded. By default,
Yes
Yes
automatic_sp_privileges
When this variable has a value of 1 (the default), the server automatically grants the EXECUTE and ALTER ROUTINE privileges to the creator of a stored routine, if the user cannot already execute and alter or drop the routine. (The ALTER ROUTINE privilege is required to drop the routine.) The server also automatically drops those privileges from the creator when the routine is dropped. If automatic_sp_privileges is 0, the server does not automatically add or drop these privileges.
Yes
Global
Yes
chroot
Put the mysqld server in a closed environment during startup by using the chroot() system call.
Yes
Yes
des-key-file
Read the default DES keys from this file. These keys are used by the DES_ENCRYPT() and DES_DECRYPT() functions.
Yes
Yes
local_infile
Whether LOCAL is supported for LOAD DATA INFILE statements. If this variable is disabled, clients cannot use LOCAL in LOAD DATA statements.
Yes
Global
Yes
old_passwords
This variable determines the type of password hashing performed by the PASSWORD() function and statements such as CREATE USER and GRANT.
Yes
Both
Yes
safe-user-create
If this option is enabled, a user cannot create new MySQL users by using the GRANT statement unless the user has the INSERT privilege for the mysql.user table or any column in the table. I
Yes
Yes
secure-auth
This option causes the server to block connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format. Use it to prevent all use of passwords employing the old format (and hence insecure communication over the network).
Yes
Yes
Global
Yes
– Variable: secure_auth
If this variable is enabled, the server blocks connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format.
Yes
Global
Yes
secure-file-priv
By default, this variable is empty. If set to the name of a directory, it limits the effect of the LOAD_FILE() function and the LOAD DATA and SELECT … INTO OUTFILE statements to work only with files in that directory.
Yes
Yes
Global
No
– Variable: secure_file_priv
Yes
Global
No
skip-grant-tables
This option causes the server to start without using the privilege system at all, which gives anyone with access to the server unrestricted access to all databases.
Yes
Yes
skip-name-resolve
All interaction with mysqld must be made using named pipes or shared memory (on Windows) or Unix socket files (on Unix). This option is highly recommended for systems where only local clients are permitted.
Yes
Yes
Global
No
– Variable: skip_name_resolve
Yes
Global
No
skip-networking
All interaction with mysqld must be made using named pipes or shared memory (on Windows) or Unix socket files (on Unix). This option is highly recommended for systems where only local clients are permitted.
Yes
Yes
Global
No
– Variable: skip_networking
Yes
Global
No
skip-show-database
This option sets the skip_show_database system variable that controls who is permitted to use the SHOW DATABASES statement.
Yes
Yes
Global
No
– Variable: skip_show_database
Yes
Global
No
How to Run MySQL as a Normal User:
On Windows, you can run the server as a Windows service using a normal user account.
On Unix, the MySQL server mysqld can be started and run by any user. However, you should avoid running the server as the Unix root user for security reasons.
Security Issues with LOAD DATA LOCAL:
There are two potential security issues with supporting the LOCAL version of LOAD DATA statements :
The transfer of the file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server’s choosing rather than the file named by the client in the LOAD DATA statement. Such a server could access any file on the client host to which the client user has read access.
In a Web environment where the clients are connecting from a Web server, a user could use LOAD DATA LOCAL to read any files that the Web server process has read access to (assuming that a user could run any command against the SQL server). In this environment, the client with respect to the MySQL server actually is the Web server, not the remote program being run by the user who connects to the Web server.
Client Programming Security Guidelines:
Applications that access MySQL should not trust any data entered by users, who can try to trick your code by entering special or escaped character sequences in Web forms, URLs, or whatever application you have built. Be sure that your application remains secure if a user enters something like “; DROP DATABASE mysql;”. This is an extreme example, but large security leaks and data loss might occur as a result of hackers using similar techniques if you do not prepare for them. See the following guidelines :
Enable strict SQL mode to tell the server to be more restrictive of what data values it accepts.
Try to enter single and double quotation marks (“’” and “””) in all of your Web forms. If you get any kind of MySQL error, investigate the problem right away.
Try to modify dynamic URLs by adding %22 (“””), %23 (“#”), and %27 (“’”) to them.
Try to modify data types in dynamic URLs from numeric to character types using the characters shown in the previous examples. Your application should be safe against these and similar attacks.
Try to enter characters, spaces, and special symbols rather than numbers in numeric fields. Your application should remove them before passing them to MySQL or else generate an error. Passing unchecked values to MySQL is very dangerous.
Check the size of data before passing it to MySQL.
Do not give your applications any access privileges they do not need.
The MySQL Access Privilege System
Privileges Provided by MySQL :
MySQL provides privileges that apply in different contexts and at different levels of operation:
Administrative privileges enable users to manage the operation of the MySQL server. These privileges are global because they are not specific to a particular database.
Database privileges apply to a database and to all objects within it. These privileges can be granted for specific databases, or globally so that they apply to all databases.
Privileges for database objects such as tables, indexes, views, and stored routines can be granted for specific objects within a database, for all objects of a given type within a database (for example, all tables in a database), or globally for all objects of a given type in all databases).
Permissible Privileges for GRANT and REVOKE:
Privilege
Column
Context
CREATE
Create_priv
databases, tables, or indexes
DROP
Drop_priv
databases, tables, or views
GRANT OPTION
Grant_priv
databases, tables, or stored routines
LOCK TABLES
Lock_tables_priv
databases
REFERENCES
References_priv
databases or tables
EVENT
Event_priv
databases
ALTER
Alter_priv
tables
DELETE
Delete_priv
tables
INDEX
Index_priv
tables
INSERT
Insert_priv
tables or columns
SELECT
Select_priv
tables or columns
UPDATE
Update_priv
tables or columns
CREATE TEMPORARY TABLES
Create_tmp_table_priv
tables
TRIGGER
Trigger_priv
tables
CREATE VIEW
Create_view_priv
views
SHOW VIEW
Show_view_priv
views
ALTER ROUTINE
Alter_routine_priv
stored routines
CREATE ROUTINE
Create_routine_priv
stored routines
EXECUTE
Execute_priv
stored routines
FILE
File_priv
file access on server host
CREATE TABLESPACE
Create_tablespace_priv
server administration
CREATE USER
Create_user_priv
server administration
PROCESS
Process_priv
server administration
PROXY
see proxies_priv table
server administration
RELOAD
Reload_priv
server administration
REPLICATION CLIENT
Repl_client_priv
server administration
REPLICATION SLAVE
Repl_slave_priv
server administration
SHOW DATABASES
Show_db_priv
server administration
SHUTDOWN
Shutdown_priv
server administration
SUPER
Super_priv
server administration
ALL [PRIVILEGES]
server administration
USAGE
server administration
Privilege System Grant Tables:
Normally, you manipulate the contents of the grant tables in the mysql database indirectly by using statements such as GRANT and REVOKE to set up accounts and control the privileges available to each one.
These mysql database tables contain grant information:
user: Contains user accounts, global privileges, and other non-privilege columns.
db: Contains database-level privileges.
host: Obsolete. New MySQL installations no longer create this table as of MySQL 5.6.7.
tables_priv: Contains table-level privileges.
columns_priv: Contains column-level privileges.
procs_priv: Contains stored procedure and function privileges.
proxies_priv: Contains proxy-user privileges.
Specifying Account Names:
MySQL account names consist of a username and a hostname. This enables the creation of accounts for users with the same name who can connect from different hosts. This section describes how to write account names, including special values and wildcard rules. In SQL statements such as CREATE USER, GRANT, and SET PASSWORD, write account names using the following rules:
Syntax for account names is ‘user_name’@’host_name’.
An account name consisting only of a username is equivalent to ‘user_name’@’%’. For example, ‘me’ is equivalent to ‘me’@’%’.
The username and hostname need not be quoted if they are legal as unquoted identifiers. Quotes are necessary to specify a user_name string containing special characters (such as “-”), or a host_name string containing special characters or wildcard characters (such as “%”); for example, ‘test-user’@’%.com’.
Quote usernames and hostnames as identifiers or as strings, using either backtick (“`”), single quotation marks (“’”), or double quotation marks (“””).
The username and hostname parts, if quoted, must be quoted separately. That is, write ‘me’@’localhost’, not ‘me@localhost’; the latter is interpreted as ‘me@localhost’@’%’.
A reference to the CURRENT_USER or CURRENT_USER() function is equivalent to specifying the current client’s username and hostname literally.
Access Control, Stage 1: Connection Verification:
When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, and then enters Stage 2 and waits for requests. Your identity is based on two pieces of information :
The client host from which you connect.
Your MySQL user name.
Access Control, Stage 2: Connection Verification:
After you establish a connection, the server enters Stage 2 of access control. For each request that you issue through that connection, the server determines what operation you want to perform, then checks whether you have sufficient privileges to do so. This is where the privilege columns in the grant tables come into play. These privileges can come from any of the user, db, tables_priv, columns_priv, or procs_priv tables.
The user table grants privileges that are assigned to you on a global basis and that apply no matter what the default database is. For example, if the user table grants you the DELETE privilege, you can delete rows from any table in any database on the server host! It is wise to grant privileges in the user table only to people who need them, such as database administrators.
The db table grants database-specific privileges. Values in the scope columns of this table can take the following forms :
A blank User value matches the anonymous user. A nonblank value matches literally; there are no wildcards in usernames.
The wildcard characters “%” and “_” can be used in the Host and Db columns. These have the same meaning as for pattern-matching operations performed with the LIKE operator. If you want to use either character literally when granting privileges, you must escape it with a backslash. For example, to include the underscore character (“_”) as part of a database name, specify it as “\_” in the GRANT statement.
A ‘%’ or blank Host value means “any host.”
A ‘%’ or blank Db value means “any database.”
When Privilege Changes Take Effect :
When mysqld starts, it reads all grant table contents into memory. The in-memory tables become effective for access control at that point. If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.
Causes of Access-Denied Errors:
If you encounter problems when you try to connect to the MySQL server, the following items describe some courses of action you can take to correct the problem.
Make sure that the server is running. If it is not, clients cannot connect to it.
It might be that the server is running, but you are trying to connect using a TCP/IP port, named pipe, or Unix socket file different from the one on which the server is listening. To find out where the socket file is, you can use this command: shell> netstat -ln | grep mysql
Make sure that the server has not been configured to ignore network connections or (if you are attempting to connect remotely) that it has not been configured to listen only locally on its network interfaces.
Check to make sure that there is no firewall blocking access to MySQL.
The grant tables must be properly set up so that the server can use them for access control.
After a fresh installation, you should connect to the server and set up your users and their access permissions :shell> mysql -u root mysql
If you have updated an existing MySQL installation to a newer version, run the mysql_upgrade script.
If a client program receives the error message “Client does not support authentication protocol requested by server; consider upgrading MySQL client” it means that the server expects passwords in a newer format than the client is capable of generating.
If a client program seems to be sending incorrect default connection parameters when you have not specified them on the command line, check any applicable option files and your environment.
If you get the error message “Access denied for user ‘root’@’localhost’ (using password: YES)”, it means that you are using an incorrect root password.
If you change a password by using SET PASSWORD, INSERT, or UPDATE, you must encrypt the password using the PASSWORD() function. If you do not use PASSWORD() for these statements, the password will not work.
localhost is a synonym for your local hostname, and is also the default host to which clients try to connect if you specify no host explicitly.
The Access denied error message tells you who you are trying to log in as, the client host from which you are trying to connect, and whether you were using a password.
If you get an Access denied error when trying to connect to the database with mysql -u user_name, you may have a problem with the user table.
If you get the error message “Host … is not allowed to connect to this MySQL server”, when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host.
If you specify a hostname when trying to connect, but get an error message where the hostname is not shown or is an IP address, it means that the MySQL server got an error when trying to resolve the IP address of the client host to a name:
MySQL User Account Management
UserNames and Passwords:
MySQL stores accounts in the user table of the mysql database. An account is defined in terms of a username and the client host or hosts from which the user can connect to the server. The account may also have a password
Adding and removing user accounts:
You can create MySQL accounts in two ways:
By using statements intended for creating accounts, such as CREATE USER or GRANT. These statements cause the server to make appropriate modifications to the grant tables.
By manipulating the MySQL grant tables directly with statements such as INSERT, UPDATE, or DELETE.
To remove an account, use the DROP USER statement,
Setting Account Resource Limits:
In MySQL 5.6, you can limit use of the following server resources for individual accounts:
The number of queries that an account can issue per hour
The number of updates that an account can issue per hour
The number of times an account can connect to the server per hour
The number of simultaneous connections to the server by an account
Assigning Account Passwords:
Required credentials for clients that connect to the MySQL server can include a password. In MySQL 5.6, it is also possible for clients to authenticate using plugins.
To assign a password when you create a new account with CREATE USER, include an IDENTIFIED BY clause :mysql> CREATE USER ‘user’@’localhost’ -> IDENTIFIED BY ‘mypass’;
To assign or change a password for an existing account, one way is to issue a SET PASSWORD statement :mysql> SET PASSWORD FOR -> ‘user’@’localhost’ = PASSWORD(‘mypass’);
View is a data object which does not contain any data. Contents of the view are the resultant of a base table. They are operated just like base table but they don’t contain any data of their own. The difference between a view and a table is that views are definitions built on top of other tables (or views). If data is changed in the underlying table, the same change is reflected in the view. A view can be built on top of a single or multiple tables.
Version: MySQL 5.6
Contents:
Why Views?
How to Create MySQL View?
Restrictions on View definition
Tools to create MySQL Views
Alter a MySQL view
DROP a MySQL view
MySQL CREATE VIEW with WHERE
MySQL CREATE VIEW with AND and OR
MySQL CREATE VIEW with GROUP BY
MySQL CREATE VIEW with ORDER BY
MySQL CREATE VIEW with BETWEEN and IN
MySQL CREATE VIEW with LIKE
MySQL CREATE VIEW using subqueries
MySQL CREATE VIEW with JOIN
MySQL CREATE VIEW with UNION
Why views?
Views can be effective copies of base tables.
Views can have column names and expressions.
You can use any clauses in views.
Views can be used in INSERT/UPDATE/DELETE.
Views can contain expressions in the select list.
Views can be views of views.
MySQL Views need Version 5.0 or higher
To get views to work, you’ll need to upgrade to MySQL version 5.0 (or higher). You can check your MySQL version in the following way:
Check the privileges of the current user:
The CREATE VIEW statement requires the CREATE VIEW privilege for the view and some privilege for each column selected by the SELECT statement. The following command shows the user privileges.mysql> SHOW PRIVILEGES; +—————–+—————————-+——————————————————-+ | Privilege | Context | Comment | +—————–+—————————-+——————————————————-+ | Alter | Tables | To alter the table | | Alter routine | Functions,Procedures | To alter or drop stored functions/procedures | | Create | Databases,Tables,Indexes | To create new databases and tables | | Create routine | Databases | To use CREATE FUNCTION/PROCEDURE | | Create temporary| Databases | To use CREATE TEMPORARY TABLE | | tables | | | | Create view | Tables | To create new views | | Create user | Server Admin | To create new users | | Delete | Tables | To delete existing rows | | Drop | Databases,Tables | To drop databases, tables, and views | | Event | Server Admin | To create, alter, drop and execute events | | Execute | Functions,Procedures | To execute stored routines | | File | File access on server | To read and write files on the server | | Grant option | Databases,Tables, | To give to other users those privileges you possess | | | Functions,Procedures | | | Index | Tables | To create or drop indexes | | Insert | Tables | To insert data into tables | | Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) | | Process | Server Admin | To view the plain text of currently executing queries | | Proxy | Server Admin | To make proxy user possible | | References | Databases,Tables | To have references on tables | | Reload | Server Admin | To reload or refresh tables, logs, and privileges | | Replication | Server Admin | To ask where the slave or master servers are | | client | | | | Replication | Server Admin | To read binary log events from the master | | slave | | | | Select | Tables | To retrieve rows from table | | Show databases | Server Admin | To see all databases with SHOW DATABASES | | Show view | Tables | To see views with SHOW CREATE VIEW | | Shutdown | Server Admin | To shut down the server | | Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. | | Trigger | Tables | To use triggers | | Create | Server Admin | To create/alter/drop tablespaces | | tablespace | | | | Update | Tables | To update existing rows | | Usage | Server Admin | No privileges – allow connecting only | +————————-+——————–+——————————————————-+ 31 rows in set (0.00 sec)
Select a database :
Before creating a view we must select a database. Following command shows the list of databases.
Now select the database ‘hr’ and list the tables:mysql> USE hr; Database changed mysql> SHOW TABLES; +————–+ | Tables_in_hr | +————–+ | alluser | | departments | | emp_details | | job_history | | jobs | | locations | | regions | | user | | user_details | +————–+ 9 rows in set (0.00 sec)
Create View
Following statements create a view. By default, a view is associated with the default database (currently used the database). To associate the view with a given database, specify the name as database_name. view_name when you create it. Here is the complete syntax :
view_name: view_name is the name of the view. A view always belongs to a database. By default, a new view is created in the currently used database. The view name may also be used with the database name, as database_name.view_name, but it is unnecessary if database_name is the default database.
select_statement: The select_statement is a SELECT statement and provides the definition of the view. select_statement can select data from base tables or other views.
Example:mysql> USE hr; Database changed mysql> CREATE VIEW my_v1 AS SELECT * FROM user_details; Query OK, 0 rows affected (0.13 sec)
column_list: The column_list part is optional. It provides a list of names for the view’s columns right after the view name where the names must be unique. The number of names in column_list must be the same as the number of columns retrieved by the SELECT statement. If you want to give your view columns a different name, you can do so by adding an [AS name] clause in the select list.
Example: View without column_listmysql> SELECT * FROM user; +———-+———–+——–+ | userid | password | name | +———-+———–+——–+ | scott123 | 123@sco | Scott | | ferp6734 | dloeiu@&3 | Palash | | diana094 | ku$j@23 | Diana | +———-+———–+——–+ 3 rows in set (0.04 sec) mysql> CREATE VIEW my_v2 AS SELECT * FROM user; Query OK, 0 rows affected (0.05 sec) mysql> SELECT * FROM my_v2; +———-+———–+——–+ | userid | password | name | +———-+———–+——–+ | scott123 | 123@sco | Scott | | ferp6734 | dloeiu@&3 | Palash | | diana094 | ku$j@23 | Diana | +———-+———–+——–+ 3 rows in set (0.05 sec)
Now specify the columns name in the above view:mysql> CREATE VIEW my_v3 AS SELECT userid AS User_ID, password AS Password, name AS Name FROM user; Query OK, 0 rows affected (0.04 sec) mysql> SELECT * FROM my_v3; +———-+———–+——–+ | User_ID | Password | Name | +———-+———–+——–+ | scott123 | 123@sco | Scott | | ferp6734 | dloeiu@&3 | Palash | | diana094 | ku$j@23 | Diana | +———-+———–+——–+ 3 rows in set (0.04 sec) CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
OR REPLACE: If the optional OR REPLACE clause is added with CREATE VIEW statement, the CREATE VIEW statement replaces an existing view and create a new one. If the view does not exist, CREATE VIEW is the same as CREATE OR REPLACE VIEW.CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
– ALGORITHM : The ALGORITHM clause is optional, it affects how MySQL processes the view. ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED. The default algorithm is UNDEFINED.
[DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] : The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at view invocation time.
If you specify the DEFINER clause, the following rules determine the legal DEFINER user values :
If you do not have the SUPER privilege, the only legal user value is your own account and you cannot set the definer to some other account.
If you have the SUPER privilege, you can specify any syntactically legal account name.
Within a stored routine that is defined with the SQL SECURITY DEFINER characteristic, CURRENT_USER returns the routine’s DEFINER value. This also affects a view defined within such a routine, if the view definition contains a DEFINER value of CURRENT_USER.
[WITH [CASCADED | LOCAL] CHECK OPTION] : The WITH CHECK OPTION clause can be given for an updatable view to preventing inserts or updates to rows except those for which the WHERE clause in the select_statement is true. In a WITH CHECK OPTION clause for an updatable view, the LOCAL and CASCADED keywords determine the scope of check testing when the view is defined in terms of another view. The LOCAL keyword restricts the CHECK OPTION only to the view being defined. CASCADED causes the checks for underlying views to be evaluated as well. When neither keyword is given, the default is CASCADED.
Restrictions on View definition
The SELECT statement cannot contain a subquery in the FROM clause.
The SELECT statement cannot refer to system or user variables.
Within a stored program, the definition cannot refer to program parameters or local variables.
The SELECT statement cannot refer to prepared statement parameters.
Any table or view referred to in the definition must exist.
The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view.
Any tables named in the view definition must exist at definition time.
You cannot associate a trigger with a view.
Aliases for column names in the SELECT statement are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters).
Tools to create MySQL Views
You can write a procedure in MySQL command line tool or you can use MySQL workbench which is an excellent front-end tool (here we have used version 5.3 CE).
MySQL command line tool:
Select MySQL command Client from Start menu:
Selecting MySQL command prompt following screen will come:
After a successful login you can access the MySQL command prompt:
Now you can create and run your own view, see the following example:
MySQL workbench (5.3 CE):
Select MySQL workbench from Start menu:
After selecting MySQL workbench following login screen will come:
Now input the login details:
After successful login, a new screen will come and from the object browser panel selects a database:
After selecting the database right click on Views, a new popup will come:
After selecting “Create View ” following screen will come where you can write your own view.
After writing the view click on Apply button and the following screen will come:
Next screen will be to review the script and apply on the database.
Now click on Finish button and run the view:
Alter a view
ALTER VIEW statement changes the definition of an existing view. The syntax of the statement is similar to CREATE VIEW.
The IF EXISTS clause prevents an error from occurring for views that don’t exist.
MySQL CREATE VIEW with WHERE
CREATE VIEW command can be used with WHERE clause.
Example:
Sample table: author
CREATE VIEW view_author
AS SELECT *
FROM author
WHERE country='USA'
Copy
The above MySQL statement will create a view ‘view_author’ taking records (for all columns) of author table if those records contain the value ‘USA’ for country column.
MySQL CREATE VIEW with AND and OR
CREATE VIEW command can be used with AND and OR operators.
Example:
Sample table: publisher
CREATE VIEW view_publisher
AS SELECT pub_name,pub_city,country
FROM publisher
WHERE (country='USA' AND pub_city='New York')
OR (country='India' AND pub_city='Mumbai');
Copy
The above MySQL statement will create a view ‘view_publisher’ taking records for pub_name, pub_city and country columns of publisher table, if (A)(i)value of the country column is the USA, and (ii)value of the pub_city is New York; or (B)(i)value of the country column is INDIA, and (ii)value of the pub_city is Mumbai.
MySQL CREATE VIEW with GROUP BY
CREATE VIEW command can be used with GROUP BY clause.
Example:
Sample table: book_mast
CREATE VIEW view_bookmast
AS SELECT pub_lang, count(*)
FROM book_mast
GROUP BY pub_lang
Copy
The above statement will create a view ‘view_bookmast’ taking all records grouped w.r.t. pub_lang, from pub_lang and number of books for each language (pub_lang).
MySQL CREATE VIEW with ORDER BY
CREATE VIEW command can be used with ORDER BY clause.
Example:
Sample table: book_mast
CREATE VIEW view_bookmast
AS SELECT pub_lang,count(*)
FROM book_mast
GROUP BY pub_lang ORDER BY pub_lang;
Copy
The above MySQL statement will create a view ‘view_bookmast’ taking all the records grouped w.r.t. pub_lang and sorted against pub_lang, from pub_lang and number of books for each language (pub_lang) of book_mast table.
MySQL CREATE VIEW with BETWEEN and IN
CREATE VIEW command can be used with BETWEEN and IN operator.
Example:
Sample table: book_mast
CREATE VIEW view_bookmast
AS SELECT *
FROM book_mast
WHERE book_name BETWEEN 'A' AND 'G'
AND no_page IN(165,250,350,400,510);
Copy
The above statement will create a view ‘view_bookmast’ taking all the records of book_mast table, if (A)name of the book (book_name) starts with any of the characters from ‘A’ through ‘G’ and (B) number of pages (no_page) are any of the following 165, 250, 350, 400, 510.
MySQL CREATE VIEW with LIKE
CREATE VIEW command can be used with LIKE operator.
Example:
Sample table: author
CREATE VIEW view_author
AS SELECT *
FROM author
WHERE aut_name
NOT LIKE 'T%' AND aut_name NOT LIKE 'W%';
Copy
The above MySQL statement will create a view ‘view_author’ taking all the records of author table if (A)name of the author (aut_name) does not start with ‘T’ and (B) name of the author (aut_name) does not start with ‘W’.
MySQL CREATE VIEW using subqueries
CREATE VIEW command can be used with subqueries.
Example:
Sample table: purchase
Sample table: book_mast
CREATE VIEW view_purchase
AS SELECT invoice_no,book_name,cate_id
FROM purchase
WHERE cate_id= (SELECT cate_id FROM book_mast WHERE no_page=201);
Copy
The above MySQL statement will create a view ‘view_purchase’ taking all the records of invoice_no, book_name and cate_id columns of purchase table, if category id (cate_id) satisfies the condition defined within a subquery (followed by cate_id=).
The subquery retrieves only cate_ids from book_mast table, which contain books with 201 pages.
MySQL CREATE VIEW with JOIN
CREATE VIEW command can be used along with a JOIN statement.
Example:
Sample table: category
Sample table: purchase
CREATE VIEW view_purchase
AS SELECT a.cate_id,a.cate_descrip, b.invoice_no,
b.invoice_dt,b.book_name
FROM category a,purchase b
WHERE a.cate_id=b.cate_id;
Copy
The above MySQL statement will create a view ‘view_purchase’ along with a JOIN statement.
The JOIN statement here retrieves cate_id, cate_descrip from category table and invoice_no, invoice_dt, and book_name from purchase table if cate_id of category table and that of purchase are same.
MySQL CREATE VIEW with UNION
CREATE VIEW command can be used with UNION.
Example:
Sample table: book_mast
CREATE VIEW view_bookmast AS
SELECT *
FROM book_mast
WHERE pub_id='P001' UNION
SELECT *
FROM book_mast
WHERE book_name BETWEEN 'A' AND 'G' UNION
SELECT *
FROM book_mast
WHERE no_page IN(165,250,350,400,510);
Copy
The above MySQL statement will create a view ‘view_bookmast’ contains columns as in the ‘book_mast’.
The records will be inserted with the union of three subqueries.
The first query inserts those rows into the ‘view_bookmast’ view from the ‘book_mast’ table whose ‘pub_id’ is ‘P001’.
The second query inserts those rows into the ‘view_bookmast’ view from the ‘book_mast’ table whose rows have the ‘book_name’ column beginning with any letter between ‘A’ to ‘G’.
The third query inserts those rows into the ‘view_bookmast’ view from the ‘book_mast’ table whose rows have any of the following values 165,250,350,400,510 in ‘no_page’.
Online Practice Editor:https://paiza.io/projects/e/nJh7N3gU3Cm3-HZRkFYkiA?theme=twilight
