Before you perform any operations, you need to connect to MySQL server and select a MySQL database.
Connecting to MySQL server from command line
Command:MySQL -h HostName -u UserName -p
Parameters:
Name
Description
-h
Keyword, followed by HOSTNAME.
HostName
MySQL server name
-u
Keyword, followed by USERNAME.
UserName
MySQL user name
-p
Asks you to enter a password.
As soon as you enter this command, it asks you to provide a password for the user you mentioned. Supplying the appropriate password would allow you to connect to MySQL server.
Connecting to MySQL database from command line
Command:use DatabaseName;
Where DatabaseName is the database you want to select.
Disconnecting or closing MySQL server from command line
Command:exit
This command must be executed from the MySQL prompt.
Replace the values of the $host, $username, $password and $db_name according to your own setup. Notice that we have also selected the database with PHP, which is a must before you can fetch data from a MySQL database.
Disconnecting or closing a MySQL connection using PHP
<?php
MySQL_close($con);
?>
Copy
Where $con=MySQL_connect(“$host”, “$username”, “$password”)or die(“cannot connect”), shown in the previous example.
Note: It will be convenient for you if you keep the script you require to connect to and disconnect from MySQL in a PHP file and then include that file in each PHP file you are using to perform various operations on the database.
Using MySQL persistent connection with PHP
Description:
1. MySQL persistent connection is a connection which first tries to find if any identical (i.e. with the same hostname, username, and password) exists. If so, then commands followed will use that connection. If such a connection does not exist, it would create one.
2. MySQL persistent connection does not need a MySQL_close().
When an amount of data is stored in an organized way, that is called a database. In computers, a database is managed by a software called Database Management System.
What is a table?
A table is a set of data values. These values are organized using vertical columns and horizontal rows. Columns are identified by their names.
Pictorial representation of a database with tables
MySQL create database
In MySQL, CREATE DATABASE statement creates a database with the given name. To use this statement, you must have the CREATE privilege for the database. You will get an error if the database exists and you did not specify IF NOT EXISTS clause.
Limits on Number of Databases: MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.
CREATE SCHEMA is a synonym for CREATE DATABASE.
Syntax:CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name [create_specification] … create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
Where
database_name is the name of the new database.
Rules for valid database names are given in MySQL language structure “Schema Object Names” section.
The CHARACTER SET clause specifies the default database character set.
Example:
The following statement creates ‘bookinfo’ database.
CREATE DATABASE bookinfo;
Copy
The database names are case sensitive under Unix but this restriction does not apply in Windows. This is also true for table names. The best practice is to use same letter case for creating a database as well as a table.
Note: A database which has just been created is not current database. The user must have to instruct to make it the current database. A database needs to be created only once but a user must have to select it each time he intends to work with that database.
MySQL: Setting the Default Database
MySQL use statement is used to change the database from default to the given database.
Syntax:
use [database_name];
MySQL show database
SHOW statement displays a list of currently existing databases on the server.
Syntax:
SHOW [expression];
Example:
The following MySQL statement will show the current database.
SHOW databases;
Copy
The list of databases shown bellow by the statement may be different to the other user’s machine. SHOW DATABASES does not show databases for those you don’t have SHOW DATABASES privilege.
Sample Output:MySQL> show databases; +——————–+ | Database | +——————–+ | information_schema | | bookinfo | | MySQL | | test | +——————–+ 4 rows in set (0.01 sec)
MySQL select database
MySQL select database statement is used to see which database is currently selected.
Syntax:
SELECT [expression];
Example:
The following MySQL statement will show the current database.
SELECT DATABASE();
Copy
Sample Output:MySQL> select database(); +————+ | database() | +————+ | bookinfo | +————+ 1 row in set (0.03 sec)
MySQL show tables statement
MySQL ‘show tables’ statement displays a list of the tables in the database in use. If there is no table in the database, it returns empty rows.
Syntax:
SHOW [expression];
Example:
The following statement displays the list of tables in the database ‘bookinfo’.
Shows the CREATE DATABASE statement that creates the given database. If the SHOW statement includes an IF NOT EXISTS clause, the output to includes such a clause. SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE.
Syntax:SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
Example:
The following statement shows the create database statement of ‘world’ database.
SHOW create database world;
Copy
Sample Output:MySQL> show create database world; +———-+——————————————————————+ | Database | Create Database | +———-+——————————————————————+ | world | CREATE DATABASE world /*!40100 DEFAULT CHARACTER SET latin1 */ | +———-+——————————————————————+ 1 row in set (0.00 sec)
Find MySQL database size
Following query show you the database size in MySQL.
Sample Output:MySQL> SELECT table_schema “Database”, SUM(data_length + index_length)/1024/1024 “Size in MB” FROM information_schema.TABLES GROUP BY table_schema; +——————–+————-+ | Database | Size in MB | +——————–+————-+ | bupf | 20.09464169 | | hr | 0.28685379 | | information_schema | 0.00976563 | | mucemppf | 4.50534534 | | MySQL | 2.43705654 | | performance_schema | 0.00000000 | | sakila | 6.57598877 | | sample | 0.73437500 | | test | 0.06250000 | | tutorial | 0.02406311 | | world | 0.43582153 | +——————–+————-+ 11 rows in set (0.17 sec)
Find all the tables in a MySQL database with specific column names in them
The following statement shows all the tables in ‘hr’ database with columns ‘name’ or ‘department_id’. At first, see the tables in ‘hr’ database.
Sample Output:MySQL> USE hr; Database changed MySQL> SHOW TABLES; +—————–+ | Tables_in_hr | +—————–+ | account | | alluser | | departments | | emp_details | | job_history | | jobs | | locations | | log_emp_details | | my_v1 | | my_v2 | | my_v3 | | my_view | | new_view | | regions | | user | +—————–+ 22 rows in set (0.00 sec)
Sample Output:MySQL> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN (‘department_id’, ‘name’) AND TABLE_SCHEMA=’hr’; +————-+ | TABLE_NAME | +————-+ | departments | | job_history | | my_v2 | | my_v3 | | my_view | | user | +————-+ 7 rows in set (0.04 sec)
When an amount of data is stored in an organized way, that is called a database. In computers, a database is managed by a software called Database Management System.
What is a table?
A table is a set of data values. These values are organized using vertical columns and horizontal rows. Columns are identified by their names.
Pictorial representation of a database with tables
MySQL create database
In MySQL, CREATE DATABASE statement creates a database with the given name. To use this statement, you must have the CREATE privilege for the database. You will get an error if the database exists and you did not specify IF NOT EXISTS clause.
Limits on Number of Databases: MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.
CREATE SCHEMA is a synonym for CREATE DATABASE.
Syntax:CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name [create_specification] … create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
Where
database_name is the name of the new database.
Rules for valid database names are given in MySQL language structure “Schema Object Names” section.
The CHARACTER SET clause specifies the default database character set.
Example:
The following statement creates ‘bookinfo’ database.
CREATE DATABASE bookinfo;
Copy
The database names are case sensitive under Unix but this restriction does not apply in Windows. This is also true for table names. The best practice is to use same letter case for creating a database as well as a table.
Note: A database which has just been created is not current database. The user must have to instruct to make it the current database. A database needs to be created only once but a user must have to select it each time he intends to work with that database.
MySQL: Setting the Default Database
MySQL use statement is used to change the database from default to the given database.
Syntax:
use [database_name];
MySQL show database
SHOW statement displays a list of currently existing databases on the server.
Syntax:
SHOW [expression];
Example:
The following MySQL statement will show the current database.
SHOW databases;
Copy
The list of databases shown bellow by the statement may be different to the other user’s machine. SHOW DATABASES does not show databases for those you don’t have SHOW DATABASES privilege.
Sample Output:MySQL> show databases; +——————–+ | Database | +——————–+ | information_schema | | bookinfo | | MySQL | | test | +——————–+ 4 rows in set (0.01 sec)
MySQL select database
MySQL select database statement is used to see which database is currently selected.
Syntax:
SELECT [expression];
Example:
The following MySQL statement will show the current database.
SELECT DATABASE();
Copy
Sample Output:MySQL> select database(); +————+ | database() | +————+ | bookinfo | +————+ 1 row in set (0.03 sec)
MySQL show tables statement
MySQL ‘show tables’ statement displays a list of the tables in the database in use. If there is no table in the database, it returns empty rows.
Syntax:
SHOW [expression];
Example:
The following statement displays the list of tables in the database ‘bookinfo’.
Shows the CREATE DATABASE statement that creates the given database. If the SHOW statement includes an IF NOT EXISTS clause, the output to includes such a clause. SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE.
Syntax:SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
Example:
The following statement shows the create database statement of ‘world’ database.
SHOW create database world;
Copy
Sample Output:MySQL> show create database world; +———-+——————————————————————+ | Database | Create Database | +———-+——————————————————————+ | world | CREATE DATABASE world /*!40100 DEFAULT CHARACTER SET latin1 */ | +———-+——————————————————————+ 1 row in set (0.00 sec)
Find MySQL database size
Following query show you the database size in MySQL.
Sample Output:MySQL> SELECT table_schema “Database”, SUM(data_length + index_length)/1024/1024 “Size in MB” FROM information_schema.TABLES GROUP BY table_schema; +——————–+————-+ | Database | Size in MB | +——————–+————-+ | bupf | 20.09464169 | | hr | 0.28685379 | | information_schema | 0.00976563 | | mucemppf | 4.50534534 | | MySQL | 2.43705654 | | performance_schema | 0.00000000 | | sakila | 6.57598877 | | sample | 0.73437500 | | test | 0.06250000 | | tutorial | 0.02406311 | | world | 0.43582153 | +——————–+————-+ 11 rows in set (0.17 sec)
Find all the tables in a MySQL database with specific column names in them
The following statement shows all the tables in ‘hr’ database with columns ‘name’ or ‘department_id’. At first, see the tables in ‘hr’ database.
Sample Output:MySQL> USE hr; Database changed MySQL> SHOW TABLES; +—————–+ | Tables_in_hr | +—————–+ | account | | alluser | | departments | | emp_details | | job_history | | jobs | | locations | | log_emp_details | | my_v1 | | my_v2 | | my_v3 | | my_view | | new_view | | regions | | user | +—————–+ 22 rows in set (0.00 sec)
Sample Output:MySQL> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN (‘department_id’, ‘name’) AND TABLE_SCHEMA=’hr’; +————-+ | TABLE_NAME | +————-+ | departments | | job_history | | my_v2 | | my_v3 | | my_view | | user | +————-+ 7 rows in set (0.04 sec)
Before you perform any operations, you need to connect to MySQL server and select a MySQL database.
Connecting to MySQL server from command line
Command:MySQL -h HostName -u UserName -p
Parameters:
Name
Description
-h
Keyword, followed by HOSTNAME.
HostName
MySQL server name
-u
Keyword, followed by USERNAME.
UserName
MySQL user name
-p
Asks you to enter a password.
As soon as you enter this command, it asks you to provide a password for the user you mentioned. Supplying the appropriate password would allow you to connect to MySQL server.
Connecting to MySQL database from command line
Command:use DatabaseName;
Where DatabaseName is the database you want to select.
Disconnecting or closing MySQL server from command line
Command:exit
This command must be executed from the MySQL prompt.
Replace the values of the $host, $username, $password and $db_name according to your own setup. Notice that we have also selected the database with PHP, which is a must before you can fetch data from a MySQL database.
Disconnecting or closing a MySQL connection using PHP
<?php
MySQL_close($con);
?>
Copy
Where $con=MySQL_connect(“$host”, “$username”, “$password”)or die(“cannot connect”), shown in the previous example.
Note: It will be convenient for you if you keep the script you require to connect to and disconnect from MySQL in a PHP file and then include that file in each PHP file you are using to perform various operations on the database.
Using MySQL persistent connection with PHP
Description:
1. MySQL persistent connection is a connection which first tries to find if any identical (i.e. with the same hostname, username, and password) exists. If so, then commands followed will use that connection. If such a connection does not exist, it would create one.
2. MySQL persistent connection does not need a MySQL_close().
1. A data type specifies a particular type of data, such as integer, floating-point, Boolean etc.
2. A data type also specifies the possible values for that type, the operations that can be performed on that type and the way the values of that type are stored.
MySQL data types
MySQL supports a number of SQL standard data types in various categories. MySQL has Numeric Types, the DATETIME, DATE, and TIMESTAMP Types and String Types. Data types are discussed on this page are based on MySQL community server 5.6
MySQL Numeric Types
MySQL supports all standard SQL numeric data types which include INTEGER, SMALLINT, DECIMAL, and NUMERIC. It also supports the approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION). The keyword INT is a synonym for INTEGER, and the keywords DEC and FIXED are synonyms for DECIMAL. DOUBLE is a synonym for DOUBLE PRECISION (a nonstandard extension). REAL is a synonym for DOUBLE PRECISION (a nonstandard variation) unless the REAL_AS_FLOAT SQL mode is enabled. The BIT data type stores bit-field values and is supported for MyISAM, MEMORY, InnoDB, and NDB tables.
Integer types
SQL standard integer types INTEGER (or INT) and SMALLINT are supported by MySQL. As an extension to the standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. Following table shows the required storage and range (maximum and minimum value for signed and unsigned integer) for each integer type.
Type
Length in Bytes
Minimum Value (Signed)
Maximum Value (Signed)
Minimum Value (Unsigned)
Maximum Value (Unsigned)
TINYINT
1
-128
127
0
255
SMALLINT
2
-32768
32767
0
65535
MEDIUMINT
3
-8388608
8388607 to
0
16777215
INT
4
-2147483648
2147483647
0
4294967295
BIGINT
8
-9223372036854775808
92233720368 54775807
0
184467440737 09551615
Floating-Point Types
The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.
Types
Description
FLOAT
A precision from 0 to 23 results in a four-byte single-precision FLOAT column
DOUBLE
A precision from 24 to 53 results in an eight-byte double-precision DOUBLE column.
MySQL allows a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here values can be stored up to M digits in total where D represents the decimal point. For example, a column defined as FLOAT(8,5) will look like -999.99999. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.
Following table shows the required storage and range (maximum and minimum value for signed and unsigned integer) for each floating-point type.
Type
Length in Bytes
Minimum Value (Signed)
Maximum Value (Signed)
Minimum Value (Unsigned)
Maximum Value (Unsigned)
FLOAT
4
-3.402823466E+38
-1.175494351E-38
1.175494351E-38
3.402823466E+38
DOUBLE
8
-1.7976931348623 157E+ 308
-2.22507385850720 14E- 308
0, and 2.22507385850720 14E- 308
1.797693134862315 7E+ 308
Fixed-Point Types
Fixed-Point data types are used to preserve exact precision, for example with currency data. In MySQL DECIMAL and NUMERIC types store exact numeric data values. MySQL 5.6 stores DECIMAL values in binary format.
In standard SQL the syntax DECIMAL(5,2) (where 5 is the precision and 2 is the scale. ) be able to store any value with five digits and two decimals. Therefore the value range will be from -999.99 to 999.99. The syntax DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(M,0). MySQL supports both of these variant forms of DECIMAL syntax. The default value of M is 10. If the scale is 0, DECIMAL values contain no decimal point or fractional part. The maximum number of digits for DECIMAL is 65, but the actual range for a given DECIMAL column can be constrained by the precision or scale for a given column.
Bit Value Types
The BIT data type is used to store bit-field values. A type of BIT(N) enables storage of N-bit values. N can range from 1 to 64. To specify bit values, b’value’ notation can be used. value is a binary value written using zeros and ones. For example, b’111′ and b’10000000′ represent 7 and 128, respectively
Numeric type attributes
MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type
Types
Description
TYPE(N)
Where N is an integer and display width of the type is upto N digits.
ZEROFILL
The default padding of spaces is replaced with zeros. So, for a column INT(3) ZEROFILL, 7 is displayed as 007.
MySQL Date and Time Types
The date and time types represent DATE, TIME, DATETIME, TIMESTAMP, and YEAR. Each type has a range of valid values, as well as a “zero” value.
DATETIME, DATE, and TIMESTAMP Types
Types
Description
Display Format
Range
DATETIME
Use when you need values containing both date and time information.
YYYY-MM-DD HH:MM:SS
‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
DATE
Use when you need only date information.
YYYY-MM-DD
‘1000-01-01’ to ‘9999-12-31’.
TIMESTAMP
Values are converted from the current time zone to UTC while storing and converted back from UTC to the current time zone when retrieved.
YYYY-MM-DD HH:MM:SS
‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC
Time Type
MySQL fetches and displays TIME values in ‘HH:MM:SS’ format or ‘HHH:MM:SS’ format The range of. TIME values from ‘-838:59:59’ to ‘838:59:59’. The hours part may be rather large because not only the TIME type can be used to represent the time of day, i.e. less than 24 hours, but also the passed time or a time of interval between two events.
The TIME values in MySQL can be recognized in many different formats, some of which can include a trailing fractional seconds part in up to 6 digits microseconds precision. The range for TIME values is ‘-838:59:59.000000’ to ‘838:59:59.000000’.
MySQL explains abbreviated TIME values with colons as the time of the day. Suppose ’09:10′ means ’09:10:00′, not ’00:09:10′. MySQL understands the abbreviated values without colons as that, the two rightmost digits represent seconds. For example, we think of ‘0910’ and 0910 as meaning ’09:10:00′, i.e. 10 minutes after 9 o’clock but the reality is MySQL understand them as ’00:09:10′, i.e. 9 minutes and 10 seconds. So, be careful about using abbreviated time in MySQL.
By default, the values of time that lie outside the TIME are converted to the valid range of time values. For example, ‘-930:00:00’ and ‘930:00:00’ are converted to ‘-838:59:59’ and ‘838:59:59’. Invalid TIME values are converted to ’00:00:00′, because ’00:00:00′ is itself a valid TIME value in MySQL.
Year Type
The YEAR type is a 1-byte type used to represent year values. It can be declared as YEAR(2) or YEAR(4) to specify a display width of two or four characters. If no width is given the default is four characters
YEAR(4) and YEAR(2) have different display format but have the same range of values. For 4-digit format, MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, or 0000. For 2-digit format, MySQL displays only the last two (least significant) digits; for example, 70 (1970 or 2070) or 69 (2069).
You can specify YEAR values in a variety of formats:
String length
Range
4-digit string
‘1901’ to ‘2155’.
4-digit number
1901 to 2155.
1- or 2-digit string
‘0’ to ’99’. Values in the ranges ‘0’ to ’69’ and ’70’ to ’99’ are converted to YEAR values in the ranges 2000 to 2069 and 1970 to 1999.
1- or 2-digit number
1 to 99. Values in the ranges 1 to 69 and 70 to 99 are converted to YEAR values in the ranges 2001 to 2069 and 1970 to 1999.
String Types
The string types are CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET.
CHAR and VARCHAR Types
The CHAR and VARCHAR types are similar but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.
Types
Description
Display Format
Range in characters
CHAR
Contains non-binary strings. Length is fixed as you declare while creating a table. When stored, they are right-padded with spaces to the specified length.
Trailing spaces are removed.
The length can be any value from 0 to 255.
VARCHAR
Contains non-binary strings. Columns are variable-length strings.
As stored.
A value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.
BINARY and VARBINARY Types
The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings.
Types
Description
Range in bytes
BINARY
Contains binary strings.
0 to 255
VARBINARY
Contains binary strings.
A value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.
BLOB and TEXT Types
A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements.
Types
Description
Categories
Range
BLOB
Large binary object that containing a variable amount of data. Values are treated as binary strings.You don’t need to specify length while creating a column.
TINYBLOB
Maximum length of 255 characters.
MEDIUMBLOB
Maximum length of 16777215 characters.
LONGBLOB
Maximum length of 4294967295 characters
TEXT
Values are treated as character strings having a character set.
TINYBLOB
Maximum length of 255 characters.
MEDIUMBLOB
Maximum length of 16777215 characters.
LONGBLOB
Maximum length of 4294967295 characters
ENUM Types
A string object whose value is chosen from a list of values given at the time of table creation. For example –
The DATETIME type is used when you need values containing both date and time information. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is 1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59’.
The TIMESTAMP data type is also used when you need values containing both date and time information. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC
The major difference between DATETIME and TIMESTAMP is that TIMESTAMP values are converted from the current time zone to UTC while storing, and converted back from UTC to the current time zone when retrieved. The datetime data type value is unchanged.
See the following example :
The following command displays current time zone information :
Let create a table with two fields udatetime (data type -> datetime) utimestamp (data type -> timestamp) and insert one record with now() (returns the current date and time) as a value in both fields.
Sample Output:MySQL> select * from tempdate; +———————+———————+ | udatetime | utimestamp | +———————+———————+ | 2013-06-29 16:55:18 | 2013-06-29 13:25:18 | +———————+———————+ 1 row in set (0.00 sec)
The above output shows that udatetime (data type is DATETIME) is unchanged whereas utimestamp (data type is TIMESTAMP) is changed as the new timezone is set to ‘Europe/Paris’.
The terms literal refer to a fixed data value. MySQL evaluates seven types of literal values numeric, character string, date and time, hexadecimal, boolean, bit-field, and NULL Values
Numeric Literals
Numeric literal notation is used to specify fixed and floating-point numbers. Floating-point numbers use ‘.’ as a decimal separator. Both types of numbers may be preceded by ‘+’ or ‘-‘ to indicate a positive or negative numbers. You can use the integer notation in expressions, conditions, SQL functions, and SQL statements. The examples of integer is as follows :
Valid integers :
0
1254
-256
Valid floating-point numbers :
132.45
12.00
-21032.6309e+10
String Literals
A string is a sequence of bytes or characters, enclosed within a single quote (‘w3resource’) or double quote (“w3resource”). You can use string literal notation in expressions, conditions, SQL functions, and SQL statements. Here are some examples :
‘MySQL Tutorial’
“SQL Tutorial”
Note : If ANSI_QUOTES SQL mode is enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.
A binary string is a string of bytes that has no character set or collation whereas a nonbinary string is a string of characters that has a character set and collation. For both types of strings, comparisons are based on the numeric values of the string unit.
Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode (Disable the use of the backslash character (“\”) as an escape character within strings) is enabled. Each of these sequences begins with a backslash (“\”), known as the escape character. MySQL recognizes the escape sequences shown in the following table.
Special Character Escape Sequences
Escape Sequence
Character represented by Sequence
\0
An ASCII NUL (0x00) character.
\’
A single quote (“‘”) character.
\”
A double quote (“””) character.
\b
A backspace character.
\n
A newline (linefeed) character.
\r
A carriage return character.
\t
A tab character.
\Z
ASCII 26 (Control+Z). See note following the table.
\\
A backslash (“\”) character.
\%
A “%” character. See note following the table.
\_
A “_” character. See note following the table.
Here are some examples :MySQL> SELECT ‘w3r’, ‘”w3r”‘, “‘w3r’”, ‘””w3r””‘, ‘w3”resource’, ‘\’w3r’, ‘\”w3r’; +—–+——-+——-+———+————-+——+——+ | w3r | “w3r” | ‘w3r’ | “”w3r”” | w3’resource | ‘w3r | “w3r | +—–+——-+——-+———+————-+——+——+ | w3r | “w3r” | ‘w3r’ | “”w3r”” | w3’resource | ‘w3r | “w3r | +—–+——-+——-+———+————-+——+——+ 1 row in set (0.00 sec) MySQL> SELECT ‘The\nQuick\nBrown\nFox’; +———————+ | The Quick Brown Fox | +———————+ | The Quick Brown Fox | +———————+ 1 row in set (0.00 sec)
Date Time Literals
You can specify a date and time values in several formates, such as numbers or as quoted strings, depending on the exact type of the value and other factors. MySQL interprets ‘2017-08-22’, ‘20170822’, and 20170822 as a date.
As a string in either ‘YYYY-MM-DD’ or ‘YY-MM-DD’ format. You can use any punctuation character as the delimiter between date parts. For example, ‘2014-10-30’, ‘2014/10/30’, ‘2014^10^30, and ‘2014@10@30’ are equivalent.
As a string with no delimiters in either ‘YYYYMMDD’ or ‘YYMMDD’ format, provided that the string maintains valid date format. For example, ‘20080623’ and ‘080623’ are interpreted as ‘2008-06-23’, but ‘071432’ is illegal (it has invalid month and day parts) and becomes ‘0000-00-00’.
As a number in either YYYYMMDD or YYMMDD format provided that the number makes sense as a date. For example, 19841105 and 841105 are interpreted as ‘1984-11-05’.
As a string in either ‘YYYY-MM-DD HH:MM:SS’ or ‘YY-MM-DD HH:MM:SS’ format. You can use any punctuation character as the delimiter between date parts. For example, ‘2014-12-31 11:30:45’, ‘2014^12^31 11+30+45’, ‘2014/12/31 11*30*45’, and ‘2014@12@31 11^30^45’ are equivalent. The date and time parts can be separated by T rather than space. For example, ‘2014-11-30 11:30:45’ ‘2014-11-30T11:30:45’ are equivalent.
As a string with no delimiters in either ‘YYYYMMDDHHMMSS’ or ‘YYMMDDHHMMSS’ format, provided that the string maintains valid date format. For example, ‘20080623091528” is interpreted as ‘2008-06-23 09:15:28’, but ‘071122129015’ is illegal (it has an invalid minute part) and becomes ‘0000-00-00 00:00:00’.
As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format provided that the number maintains as a valid date. For example, 19860805132800 and 860805132800 are interpreted as ‘1986-08-05 13:28:00’.
As a string in ‘D HH:MM:SS’ format. You can also use one of the following “relaxed” syntaxes: ‘HH:MM:SS’, ‘HH:MM’, ‘D HH:MM’, ‘D HH’, or ‘SS’. Here D represents days and can have a value from 0 to 34.
As a string with no delimiters in ‘HHMMSS’ format, provided that it makes sense at a time. For example, ‘101112’ is understood as ’10:11:12′, but ‘109712’ is illegal (it has an invalid minute part) and becomes ’00:00:00′.
As a number in HHMMSS format provided that it makes sense at a time. For example, 101112 is understood as ’10:11:12′. The following alternative formats are also understood: SS, MMSS, or HHMMSS.
Hexadecimal Literals
MySQL supports hexadecimal values, written using X’val’, x’val’, or 0xval format, where val contains hexadecimal digits (0..9, A..F). Lettercase of the digits does not matter. Here are some examples :MySQL> SELECT X’773372736F75726365′; +———————–+ | X’773372736F75726365′ | +———————–+ | w3rsource | +———————–+ 1 row in set (0.01 sec) MySQL> SELECT 0xC8+0; +——–+ | 0xC8+0 | +——–+ | 200 | +——–+ 1 row in set (0.05 sec) MySQL>SELECT 0x555341; +———-+ | 0x555341 | +———-+ | USA | +———-+ 1 row in set (0.00 sec)
Boolean Literals
The constants TRUE and FALSE evaluate to 1 and 0, respectively. The constant names can be written both upper and lower case letters. See the following examples :MySQL> SELECT TRUE, true; +——+——+ | TRUE | TRUE | +——+——+ | 1 | 1 | +——+——+ 1 row in set (0.00 sec) MySQL> SELECT FALSE, false; +——-+——-+ | FALSE | FALSE | +——-+——-+ | 0 | 0 | +——-+——-+ 1 row in set (0.00 sec)
Bit-Field Literals
Bit-field values can be written using b’value’ or 0bvalue notation. value is a binary value written using zeros and ones.
NULL Values
The NULL value means “no data.” NULL can be written in any lettercase.
Schema Object Names
A schema is a collection of logical structures of data or schema objects. A schema is owned by a database user. In MySQL, some objects including database, table, column, alias, view, stored procedure, partition, tablespace, and other object names are known as identifiers. An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. Identifiers are converted to Unicode internally. They may contain these characters :
Following characters are permitted in unquoted identifiers :
ASCII : [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)
Extended : U+0080 .. U+FFFF
Following characters are permitted in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000 :
ASCII: U+0001 .. U+007F
Extended: U+0080 .. U+FFFF
ASCII NUL (U+0000) and supplementary characters (U+10000 and higher) are not permitted in quoted or unquoted identifiers.
Identifiers may begin with a digit but unless quoted may not consist solely of digits.
The database, table, and column names cannot end with space characters.
The following table shows the maximum length for each type of identifier
MySQL allows names that consist of a single identifier or multiple identifiers. The components of a multiple-part name must be separated by period (“.”) characters. The initial parts of a multiple-part name act as qualifiers that affect the context within which the final identifier is interpreted.
In MySQL, you can refer to a table column using any of the following forms.
Column Reference Meaning col_name The column col_name from whichever table used in the statement contains a column of that name. tbl_name.col_name The column col_name from table tbl_name of the default database. db_name.tbl_name.col_name The column col_name from table tbl_name of the database db_name.
The qualifier character is a separate token and needs not be contiguous with the associated identifiers. For example, tbl_name.col_name and tbl_name . col_name are equivalent.
Identifier Case Sensitivity
In MySQL database, table, and trigger names are not case sensitive in Windows, but are case sensitive in most varieties of Unix. In Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases, but logfile groups are case sensitive. This differs from standard SQL. By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X.
Mapping of Identifiers to File Names
There is a correspondence between database and table identifiers and names in the file system. MySQL represents each database as a directory in the data directory, and each table by one or more files in the appropriate database directory. For the table format files (.FRM), the data is always stored in this structure and location.
For the data and index files, the exact representation on disk is storage engine specific. These files may be stored in the same location as the FRM files, or the information may be stored in a separate file. InnoDB data is stored in the InnoDB data files. If you are using tablespaces with InnoDB, then the specific tablespace files you create are used instead.
Any character is legal in database or table identifiers except ASCII NUL (0x00). MySQL encodes any characters that are problematic in the corresponding file system objects when it creates database directories or table files:
Basic Latin letters (a..zA..Z), digits (0..9) and underscore (_) are encoded as is. Consequently, their case sensitivity directly depends on file system features.
All other national letters from alphabets that have uppercase/lowercase mapping are encoded as shown in the following table. Values in the Code Range column are UCS-2 values.
Mapping of Identifiers to File Names
MySQL 5.6 supports built-in (native) functions, user-defined functions (UDFs), and stored functions.
Built-In Function Name Parsing:
The parser uses default rules for parsing names of built-in functions. These rules can be changed by enabling the IGNORE_SPACE SQL mode. When the parser encounters a word that is the name of a built-in function, it must determine whether the name signifies a function call or is instead a nonexpression reference to an identifier such as a table or column name
Function Name Resolution : The following rules describe how the server resolves references to function names for function creation and invocation :
Built-in functions and user-defined functions An error occurs if you try to create a UDF with the same name as a built-in function.
Built-in functions and stored functions. It is possible to create a stored function with the same name as a built-in function, but to invoke the stored function it is necessary to qualify it with a schema name.
User-defined functions and stored functions share the same namespace, so you cannot create a UDF and a stored function with the same name.
MySQL: User-Defined Variables
In MySQL, you can store a value in a user-defined variable in one statement and later you can refer in another statement. This enables you to pass the values from one statement to another. User-defined variables are session-specific, therefore these variables are private to a particular user and another user can not see or use these. All variables for a given client session are automatically freed when that client exits.
Syntax : @var_name var_name consists of alphanumeric characters, “.”, “_”, and “$”. A user variable name can contain other characters if you quote it as a string or identifier (for example, @’my-var’, @”my-var”, or @my-var). Note : User variable names are not case sensitive in MySQL 5.0 and up.
One way to set a user-defined variable is by issuing a SET statement :SET @var_name = expr [, @var_name = expr] …
The following rules define expression syntax in MySQL. The grammar shown here is based on that given in the sql/sql_yacc.yy file of MySQL source distributions.
bit_expr [NOT] LIKE simple_expr [ESCAPE simple_expr]
bit_expr [NOT] REGEXP bit_expr
bit_expr
gbit_expr :
bit_expr | bit_expr
bit_expr & bit_expr
bit_expr << bit_expr
bit_expr >> bit_expr
bit_expr + bit_expr
bit_expr – bit_expr
bit_expr * bit_expr
bit_expr / bit_expr
bit_expr DIV bit_expr
bit_expr MOD bit_expr
bit_expr % bit_expr
bit_expr ^ bit_expr
bit_expr + interval_expr
bit_expr – interval_expr
simple_expr
simple_expr :
literal
identifier
function_call
simple_expr COLLATE collation_name
param_marker
variable
simple_expr || simple_expr
+ simple_expr
– simple_expr
~ simple_expr
! simple_expr
BINARY simple_expr
(expr [, expr] …)
ROW (expr, expr [, expr] …)
(subquery)
EXISTS (subquery)
{identifier expr}
match_expr
case_expr
interval_expr
MySQL Comment Syntax
MySQL supports three comment styles :
“#” character to the end of the line.
“–” sequence to the end of the line.
/* Text here */ sequence, as in the C programming language
See the following examples :MySQL> SELECT 1+1; # Single line comment MySQL> SELECT 1+1; — Single line comment MySQL> SELECT 1 /* In-line comment*/ + 1; MySQL> SELECT 1+ /* this is a multiple-line comment */ 1;
MySQL Reserved Words
Certain words such as SELECT, DELETE, or BIGINT are reserved and require special treatment for use as identifiers such as table and column names. The following table lists MySQL reserved wordS.
Storage engines (underlying software component) are MySQL components, that can handle the SQL operations for different table types to store and manage information in a database. InnoDB is mostly used general-purpose storage engine and as of MySQL 5.5 and later it is the default engine. There are many storage engines available in MySQL and they are used for different purposes.
This is the default storage engine for MySQL 5.5 and higher. It provides transaction-safe (ACID compliant) tables, supports FOREIGN KEY referential-integrity constraints. It supports commit, rollback, and crash-recovery capabilities to protect data. It also support row-level locking. It’s “consistent nonlocking reads” increases performance when used in a multiuser environment. It stores data in clustered indexes which reduces I/O for queries based on primary keys.
Provides in-memory tables, formerly known as HEAP. It sores all data in RAM for faster access than storing data on disks. Useful for quick looks up of reference and other identical data.
You can create tables with this engine, but can not store or fetch data. Purpose of this is to teach developers about how to write a new storage engine.
List of Storage Engines supported by your MySQL installation
The following command display the status information of the server’s storage engines.
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=1662646095&pi=t.aa~a.4097345806~i.31~rp.4&w=715&fwrn=4&fwrnh=100&lmt=1706401174&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-storage-engines.php&fwr=0&pra=3&rh=179&rw=715&rpe=1&resp_fmts=3&wgl=1&fa=27&uach=WyJXaW5kb3dzIiwiMTAuMC4wIiwieDg2IiwiIiwiMTA2LjAuNDk5OC40MSIsbnVsbCwwLG51bGwsIjY0IixbWyJOb3RfQSBCcmFuZCIsIjguMC4wLjAiXSxbIkNocm9taXVtIiwiMTIwLjAuNjA5OS4yMTciXSxbIk9wZXJhIiwiMTA2LjAuNDk5OC40MSJdXSwxXQ..&dt=1706401172825&bpp=10&bdt=4478&idt=10&shv=r20240122&mjsv=m202401240101&ptt=9&saldr=aa&abxe=1&cookie=ID%3D6de4a56fe4484587%3AT%3D1706354011%3ART%3D1706354346%3AS%3DALNI_MYWYkKy5gAvXyEH7W4ZN6WCTnP0sA&gpic=UID%3D00000d0223079700%3AT%3D1706354011%3ART%3D1706354346%3AS%3DALNI_MaBKEehqphuMfn0yJfqUi_NOyR70w&prev_fmts=468×80%2C304x250%2C300x600%2C300x600%2C0x0&nras=2&correlator=6076281334072&frm=20&pv=1&ga_vid=1729879426.1706400821&ga_sid=1706401171&ga_hid=716742525&ga_fc=1&u_tz=-480&u_his=3&u_h=768&u_w=1366&u_ah=728&u_aw=1366&u_cd=24&u_sd=1&dmc=8&adx=238&ady=1515&biw=1297&bih=644&scr_x=0&scr_y=0&eid=44759875%2C44759926%2C44759837%2C31080589%2C42532524%2C31080696%2C95322181%2C95320888%2C95321627%2C95322163%2C95323009&oid=2&pvsid=1669872665432750&tmod=1999805865&uas=3&nvt=1&ref=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-installation-on-linux-and-windows.php&fc=384&brdim=0%2C0%2C0%2C0%2C1366%2C0%2C1366%2C728%2C1312%2C644&vis=2&rsz=%7C%7Cs%7C&abl=NS&fu=128&bc=31&bz=1.04&psd=W251bGwsbnVsbCxudWxsLDNd&ifi=9&uci=a!9&btvi=2&fsb=1&dtd=1361mysql> SHOW ENGINES; +——————–+———+—————————————————————-+————–+——+————+ | Engine | Support | Comment | Transactions | XA | Savepoints | +——————–+———+—————————————————————-+————–+——+————+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +——————–+———+—————————————————————-+————–+——+————+ 9 rows in set (0.28 sec)
Setting the Storage Engine
In CREATE TABLE STATEMENT you can add ENGINE table option to mention a storage engine. See the following CREATE TABLE statements, where different engines have used :CREATE TABLE t1 (i INT) ENGINE = INNODB; CREATE TABLE t2 (i INT) ENGINE = CSV; CREATE TABLE t3 (i INT) ENGINE = MEMORY;
In MySQL 5.6, the default engine is InnoDB. The default storage engine is used if you do not mention the other engine name in ENGINE option. You can specify the default engine by using the –default-storage-engine server startup option (Command-Line Format), or by setting the default-storage-engine option in the my.cnf configuration file.
You can set the default storage engine for the current session by setting the default_storage_engine variable using set command.SET default_storage_engine=ARCHIVE;
If you want to convert a table form one storage engine to another, use an ALTER TABLE statement. See the following statement :ALTER TABLE table1 ENGINE = InnoDB;
To store the table and column definitions for a new table, MySQL always creates an .frm file. Depending on the storage engine the table’s index and data may be stored in one or more other files. The server creates the .frm file above the storage engine level.
MySQL: InnoDB Storage Engine
InnoDB is a storage engine for MySQL that balances high reliability and high performance. As of MySQL 5.5 and later, it is the default storage engine.
Feaures of InnoDB storage engine :
Storage limits
64TB
Transactions
Yes
Locking granularity
Row
MVCC (Multiversion concurrency control)
Yes
Geospatial data type support
Yes
Geospatial indexing support
No
B-tree indexes
Yes
T-tree indexes
No
Hash indexes
No
Full-text search indexes
Yes
Clustered indexes
Yes
Data caches
Yes
Index caches
Yes
Compressed data
Yes
Encrypted data
Yes
Cluster database support
No
Replication support
Yes
Foreign key support
Yes
Backup / point-in-time recovery
Yes
Query cache support
Yes
Update statistics for data dictionary
Yes
Advantages of InnoDB storage engine
InnoDB has maximum performance when processing large data volumes.
Its DML operations (add, update and delete data) is ACID (atomic, consistent, isolated and durable) model compatible, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.
Row-level locking (locks are placed on single records (rows)) system increase multi-user concurrency and performance. All InnoDB locks held by a transaction are released when the transaction is committed or aborted.
InnoDB tables arrange your data on disk to optimize queries based on primary keys.
InnoDB supports FOREIGN KEY constraints to maintain data integrity. Therefore inserts, updates, and deletes are all checked to ensure they do not result in inconsistencies across different tables.
It is possible to mix InnoDB tables with tables from other MySQL storage engines within the same statement. For example, you can use a join operation to combine data from InnoDB and MEMORY tables in a single query.
Creating InnoDB tables : Use CREATE TABLE statement to create am InnoDB table without any special clauses. As of MySQL 5.5, it is the default MySQL storage engine. In MySQL 5.6, issuing the CREATE TABLE statement without an ENGINE= clause creates an InnoDB table. Here is an example :mysql> CREATE TABLE table1 (col1 INT, col2 CHAR(30), PRIMARY KEY (col1)); Query OK, 0 rows affected (1.11 sec) mysql> DESC table1; +——-+———-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——-+———-+——+—–+———+——-+ | col1 | int(11) | NO | PRI | 0 | | | col2 | char(30) | YES | | NULL | | +——-+———-+——+—–+———+——-+ 2 rows in set (0.21 sec)
The following SHOW TABLE STATUS statement shows the properties of the tables (belongs to ‘tutorial’ database).mysql> SHOW TABLE STATUS FROM tutorial; +——–+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+—————–+———-+—————-+———+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +——–+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+—————–+———-+—————-+———+ | table1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2014-02-14 12:08:34 | NULL | NULL | utf8_general_ci | NULL | | | +——–+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+—————–+———-+—————-+———+ 1 row in set (0.00 sec)
Handling AUTO_INCREMENT in InnoDB :
InnoDB provides a method that improves scalability and performance of SQL statements that insert rows into tables with AUTO_INCREMENT columns. To use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column (col1 in the example) must be defined as part of an index. See the following example :mysql> CREATE TABLE table1 (col1 INT(10) NOT NULL AUTO_INCREMENT, col2 CHAR(30), PRIMARY KEY (col1)); Query OK, 0 rows affected (0.50 sec)
Handling FOREIGN KEY Constraints in InnoDB :
MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. Foreign key definitions for InnoDB tables are subject to the following conditions :
InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
InnoDB does not currently support foreign keys for tables with user-defined partitioning. This means that no user-partitioned InnoDB table may contain foreign key references or columns referenced by foreign keys.
InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.
Limitation: InnoDB table :
Maximum 1017 columns are allowed in a table (raised in MySQL 5.6.9 from the earlier limit of 1000).
Maximum 64 secondary indexes are allowed in a table. Secondary indexes is a type of InnoDB index that represents a subset of table columns.
By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix.
The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes (combined index key in a multi-column index).
The maximum row length except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is about 8000 bytes for the default page size of 16KB.
Internally InnoDB supports row sizes larger than 65,535 bytes, but MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns.
The maximum table space size is four billion database pages (64TB) and the minimum table space size is slightly larger than 10MB.
MySQL: MyISAM Storage Engine
MyISAM storage engine is based on the older ISAM storage engine (not available now) but has many useful extensions.
Features of MyISAM storage engine :
Storage limits
256TB
Transactions
No
Locking granularity
Table
MVCC (Multiversion concurrency control)
No
Geospatial data type support
Yes
Geospatial indexing support
Yes
B-tree indexes
Yes
T-tree indexes
No
Hash indexes
No
Full-text search indexes
Yes
Clustered indexes
No
Data caches
No
Index caches
Yes
Compressed data
Yes
Encrypted data
Yes
Cluster database support
No
Replication support
Yes
Foreign key support
No
Backup / point-in-time recovery
Yes
Query cache support
Yes
Update statistics for data dictionary
Yes
Each MyISAM table is stored on disk in three files.
An .frm file stores the table format.
The data file has an .MYD (MYData) extension.
The index file has an .MYI (MYIndex) extension.
Creating MyISAM tables : Use CREATE TABLE statement to create am MyISAM table with ENGINE clause. As of MySQL 5.6, it is necessary to use ENGINE clause to specify the MyISAM storage engine because InnoDB is the default engine. Here is an example :mysql> CREATE TABLE table2 (col1 INT, col2 CHAR(30)) ENGINE = MYISAM; Query OK, 0 rows affected (0.19 sec)
The following SHOW TABLE STATUS statement shows the properties of the tables (belongs to ‘tutorial’ database).mysql> SHOW TABLE STATUS FROM tutorial; +——–+——–+———+————+——+—————-+————-+——————-+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +——–+——–+———+————+——+—————-+————-+——————-+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+ | table1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2014-02-14 13:16:16 | NULL | NULL | utf8_general_ci | NULL | | | | table2 | MyISAM | 10 | Fixed | 0 | 0 | 0 | 26740122787512319 | 1024 | 0 | NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL | utf8_general_ci | NULL | | | +——–+——–+———+————+——+—————-+————-+——————-+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+ 2 rows in set (0.07 sec)
Main characteristics of MyISAM tables :
Up to 63-bit file length large files are supported on file systems and operating systems that support large files.
(232)2(1.844E+19) rows are allowed in a MyISAM table.
Maximum 64 number of indexes and 16 number of columns per index are allowed.
The maximum key length is 1000 bytes.
Internal handling of one AUTO_INCREMENT column per table is supported.
You can put the data file and index file in different directories on different physical devices to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options to CREATE TABLE
BLOB and TEXT columns can be indexed.
NULL values are permitted in indexed columns. This takes 0 to 1 bytes per key.
Each character column can have a different character set.
Support for a true VARCHAR type; a VARCHAR column starts with a length stored in one or two bytes.
Tables with VARCHAR columns may have fixed or dynamic row length.
The sum of the lengths of the VARCHAR and CHAR columns in a table may be up to 64KB.
Arbitrary length UNIQUE constraints.
Corrupted MyISAM Tables :
MyISAM table format is very reliable, but in some occasion you can get corrupted tables if any of the following events occur :
The mysqld (Known as MySQL Server) process is killed in the middle of a write.
Hardware failures.
An unexpected computer shutdown occurs.
Using an external program to modify a table
A software bug in the MySQL or MyISAM code.
MySQL: MEMORY Storage Engine
The MEMORY storage engine creates tables that are stored in memory. Because the data can be crashed due to hardware or power issues, you can only use these tables as temporary work areas or read-only caches for data pulled from other tables. When the MySQL server halts or restarts, the data in MEMORY tables is lost.
Features of MEMORY storage engine :
Storage limits
RAM
Transactions
No
Locking granularity
Table
MVCC
No
Geospatial data type support
No
Geospatial indexing support
No
B-tree indexes
Yes
T-tree indexes
No
Hash indexes
Yes
Full-text search indexes
No
Clustered indexes
No
Data caches
N/A
Index caches
N/A
Compressed data
No
Encrypted data
Yes
Cluster database support
No
Replication support
Yes
Foreign key support
No
Backup / point-in-time recover
Yes
Query cache support
Yes
Update statistics for data dictionary
Yes
Creating MEMORY tables: Use CREATE TABLE statement to create am MEMORY table with ENGINE clause. As of MySQL 5.6, it is necessary to use ENGINE clause to specify the MEMORY storage engine because InnoDB is the default engine. The following example shows how to create and use a MEMORY table :mysql> SELECT * FROM hr.departments; +—————+———————-+————+————-+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +—————+———————-+————+————-+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | | 100 | Finance | 108 | 1700 | | 110 | Accounting | 205 | 1700 | | 120 | Treasury | 0 | 1700 | |- – – – – – – -|- – – – – – – – – – – |- – – – – – |- – – – – – -| |- – – – – – – -|- – – – – – – – – – – |- – – – – – |- – – – – – -| +—————+———————-+————+————-+ 27 rows in set (0.01 sec) mysql> CREATE TABLE test7 ENGINE = MEMORY SELECT * FROM hr.departments; Query OK, 27 rows affected (0.06 sec) Records: 27 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test7 WHERE LOCATION_ID>1700; +—————+——————+————+————-+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +—————+——————+————+————-+ | 20 | Marketing | 201 | 1800 | | 40 | Human Resources | 203 | 2400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | +—————+——————+————+————-+ 4 rows in set (0.00 sec)
Indexes : The MEMORY storage engine supports both HASH and BTREE indexes. Adding a USING clause you can specify one or the other for a given index. See the following examples :CREATE TABLE test (id INT, INDEX USING HASH (id)) ENGINE = MEMORY; CREATE TABLE test (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY;
When to Use MEMORY storage engine:
Operations involving transient, non-critical data such as session management or caching.
In-memory storage for fast access and low latency. Data volume can fit entirely in memory without causing the operating system to swap out virtual memory pages.
By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix.
The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes (combined index key in a multi-column index).
The maximum row length except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is about 8000 bytes for the default page size of 16KB.
Internally InnoDB supports row sizes larger than 65,535 bytes, but MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns.
The maximum tablespace size is four billion database pages (64TB) and the minimum tablespace size is slightly larger than 10MB.
MySQL: MERGE Storage Engine
The MERGE storage engine (also known as MRG_MyISAM) is a collection of identical MyISAM tables (identical column and index information with same order) that can be used as single table. You must have SELECT, DELETE, and UPDATE privileges on the MyISAM tables that you map to a MERGE table.
Creating MERGE tables : To create a MERGE table, you must specify a UNION=(list-of-tables) option (indicates which MyISAM tables to use) in the CREAE TABLE statement. The following example at first we have created three tables with two rows then merge it into one table use MERGE storage engine :mysql> CREATE TABLE tabl1 (rollno INT NOT NULL AUTO_INCREMENT PRIMARY KEY, class CHAR(5), student_name CHAR(40)) ENGINE = MyISAM; Query OK, 0 rows affected (0.07 sec) mysql> CREATE TABLE tabl2 (rollno INT NOT NULL AUTO_INCREMENT PRIMARY KEY, class CHAR(5), student_name CHAR(40)) ENGINE = MyISAM; Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE tabl3 (rollno INT NOT NULL AUTO_INCREMENT PRIMARY KEY, class CHAR(5), student_name CHAR(40)) ENGINE = MyISAM; Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO tabl1 (class, student_name) VALUES (‘V’,’Steven’), (‘V’, ‘Neena’); Query OK, 2 rows affected (0.07 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT INTO tabl2 (class, student_name) VALUES (‘VI’,’Lex’), (‘VI’, ‘Alexander’); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT INTO tabl3 (class, student_name) VALUES (‘VII’,’Bruce’), (‘VII’, ‘David’); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE allclass (rollno INT NOT NULL, class CHAR(5), student_name CHAR(40)) ENGINE = MERGE UNION = (tabl1, tabl2, tabl3) INSERT_METHOD = LAST; Query OK, 0 rows affected (0.09 sec) mysql> select * from allclass; +——–+——-+————–+ | rollno | class | student_name | +——–+——-+————–+ | 1 | V | Steven | | 2 | V | Neena | | 1 | VI | Lex | | 2 | VI | Alexander | | 1 | VII | Bruce | | 2 | VII | David | +——–+——-+————–+ 6 rows in set (0.00 sec)
Security issue: If a user has access to MyISAM table, say t1, that user can create a MERGE table m1 that accesses t1. However, if the administrator revokes the user’s privileges on t1, the user can continue to access the data of t1 through m1.
MySQL: CSV Storage Engine
The CSV storage engine stores data in text files using comma-separated values format and the CSV storage engine is always compiled into the MySQL server. The server creates a table format file (.frm extension) and a data file (.csv extension) in the database directory when you create a CSV table. Both .frm and .csv files name begins with the table name. The data file is a plain text file and the storage engine saves data in comma-separated values format. The following example shows how to create and use a CSV table :
Sample Output:mysql> CREATE TABLE color (slno INT NOT NULL, cname CHAR(30) NOT NULL, ccode CHAR(6) NOT NULL) ENGINE = CSV; Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO color VALUES(1, ‘IndianRed’, ‘CD5C5C’), (2, ‘LightCoral’, ‘F08080’), (3, ‘Salmon’, ‘FA8072’); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * from color; +——+————+——–+ | slno | cname | ccode | +——+————+——–+ | 1 | IndianRed | CD5C5C | | 2 | LightCoral | F08080 | | 3 | Salmon | FA8072 | +——+————+——–+ 3 rows in set (0.00 sec)
You can can read, modify the ‘color.CSV’ file by spreadsheet applications such as Microsoft Excel or StarOffice Calc.
CSV Limitations :
Does not support indexing.
Does not support partitioning.
All columns must have the NOT NULL attribute in a CSV table.
The ARCHIVE storage engine is used to store large amounts of unindexed data in a very small footprint. The storage engine is included in MySQL binary distributions. To enable this storage engine (if you build MySQL from source), invoke CMake with the -DWITH_ARCHIVE_STORAGE_ENGINE option. When you create an ARCHIVE table, the server creates a table format file (.frm extension) in the database directory.
Features of ARCHIVE storage engine:
Storage limits
None
Transactions
No
Locking granularity
Table
MVCC
No
Geospatial data type support
Yes
Geospatial indexing support
No
B-tree indexes
No
T-tree indexes
No
Hash indexes
No
Full-text search indexes
No
Clustered indexes
No
Data caches
No
Index caches
No
Compressed data
Yes
Encrypted data
Yes
Cluster database support
No
Replication support
Yes
Foreign key support
No
Backup / point-in-time recovery
Yes
Query cache support
Yes
Update statistics for data dictionary
Yes
ARCHIVE storage engine supports
INSERT and SELECT.
ORDER BY operations
BLOB columns
AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index.
AUTO_INCREMENT table option in CREATE TABLE statements
ARCHIVE storage engine does not support
DELETE, REPLACE, or UPDATE
Inserting a value into an AUTO_INCREMENT column less than the current maximum column value.
ARCHIVE storage engine: Storage & Retrieval
The ARCHIVE engine uses zlib lossless data compression (see http://www.zlib.net/).
Rows are compressed as they are inserted.
On retrieval, rows are uncompressed on demand; there is no row cache.
MySQL: EXAMPLE Storage Engine
The EXAMPLE storage engine is a stub engine that does nothing and serve as an example in the MySQL source code that clarify how to begin writing new storage engines. To examine the source for the EXAMPLE engine, look in the storage/example directory of a MySQL source distribution. When you create an EXAMPLE table :
The server creates a table format file (.frm extension) in the database directory.
No other files are created
No data can be stored into the table.
Retrievals return an empty result.
Does not support indexing.
To enable the EXAMPLE storage engine if you build MySQL from source, invoke CMake with the -DWITH_EXAMPLE_STORAGE_ENGINE option.
MySQL: BLACKHOLE Storage Engine
The BLACKHOLE storage engine acts as a “black hole” that accepts data but returns an empty result. To enable the BLACKHOLE storage engine (in case of MySQL build from source), invoke CMake with the -DWITH_BLACKHOLE_STORAGE_ENGINE option. When you create a BLACKHOLE table, the server creates a table format file (.frm) in the database directory. The BLACKHOLE storage engine supports all kinds of indexes. Here is an example :mysql> CREATE TABLE test10 (slno INT, message CHAR(40)) ENGINE = BLACKHOLE; Query OK, 0 rows affected (0.16 sec) mysql> INSERT INTO test10 VALUES(1, ‘message1’), (2, ‘message2’); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test10; Empty set (0.03 sec)
The FEDERATED storage engine is used to access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables. To include the FEDERATED storage engine (in case of MySQL build from source), invoke CMake with the -DWITH_FEDERATED_STORAGE_ ENGINE option. To enable FEDERATED (not enabled by default in the running server), you must start the MySQL server binary using the –federated option. To check the source for the FEDERATED engine, look in the storage/ federated directory of a MySQL source distribution.
Create a FEDERATED table
You can create a FEDERATED table in the following ways :
Using CONNECTION
Using CREATE SERVER
Using CONNECTION : To use this method, you must specify the CONNECTION string after the engine type in a CREATE TABLE statement. Seethe following example :CREATE TABLE federated_table ( roll_no INT(3) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(42) NOT NULL DEFAULT ”, total_marks INT(5) NOT NULL DEFAULT ‘0’, PRIMARY KEY (roll_no), INDEX stu_name (stu_name), INDEX total_marks (total_marks) ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION=’mysql://feduser@remote_host:9306/federated/test10_table’;
The format of the connection string is as follows :
scheme : A recognized connection protocol. Only mysql is supported as the scheme value at this point.
The user name for the connection, must have been created on the remote server, and have suitable privileges to perform the required actions like SELECT, INSERT, UPDATE, and so forth on the remote table.
The password for user_name. (Optional)
host_name: The host name or IP address of the remote server.
port_num: The port number (default : 3306) for the remote server. (Optional)
db_name: The name of the database holding the remote table.
tbl_name: The name of the remote table.
Using CREATE SERVER: To use this method, you must specify the CONNECTION string after the engine type in a CREATE TABLE statement. See the following example:CREATE SERVER server_name FOREIGN DATA WRAPPER wrapper_name OPTIONS (option [, option] …)
The server_name is used in the connection string when creating a new FEDERATED table.
MySQL Connectors provide connectivity to the MySQL server for client programs and APIs provide low-level access to the MySQL protocol and resources. You can connect and execute MySQL statements from another language or environment, including ODBC, Python, Perl, Ruby, PHP, Java (JDBC), and native C and embedded MySQL instances through connectors and the APIs
What is a Driver? A driver is a piece of software designed to communicate with a particular database server. The driver may also call a library, such as the ‘MySQL Client Library’ or the ‘MySQL Native Driver’. These libraries implement the low-level protocol used to communicate with the database server.
What is a Connector? A connector refers to a piece of software that allows your application to connect to the MySQL database server. MySQL provides connectors for a variety of languages, including Python, Perl, Ruby, PHP, Java (JDBC), C etc.
What is an API? API, an abbreviation of application program interface, is a set of programming instructions (through classes, methods, functions and variables) and standards for accessing a web-based software application or web tool. APIs can be procedural or object-oriented. With a procedural API you call functions to carry out tasks, with the object-oriented API, you instantiate classes and then call methods on the resulting objects. Of the two the second one is usually the preferred interface, as it is more modern and leads to better-organized code.
List of MySQL Connectors
ODBC/Connector : It provides driver support for connecting (Windows, Unix, and Mac OS X platforms.) to MySQL using the Open Database Connectivity (ODBC) API.
.Net/Connector : It provides support to create .NET applications that connect to MySQL.
J/Connector : It provides driver support for connecting to MySQL from Java applications using Java Database Connectivity (JDBC) API.
Python/Connector : It provides driver support for connecting to MySQL from Python applications using Python DB API version 2.0.
C++/Connector : is a standalone replacement for the MySQL Client Library (libMySQLclient), to be used for C applications.
Note : libMySQLclient is included in MySQL distributions and in MySQL Connector/C distributions.
MySQL Workbench is GUI based tool to work with MySQL Servers. You may use this for Server Administration, for creating Entity Relationship Diagrams and for SQL Development (run queries etc).
This tutorial discusses how to perform basic MySQL Administration tasks using MySQL Workbench.
Installing MySQL workbench
To install MySQL Workbench on Linux (we used Ubuntu), you may run the following command from your terminal.sudo apt-get install MySQL-workbench
MySQL Workbench version installed at the time of writing this tutorial is 5.2.40.
On Windows, if you are installing MySQL Community Server 5.6, MySQL Workbench is installed in the installation process of the server itself. Download the appropriate .msi file from the MySQL download page, run it and follow the instructions.
After installing it successfully, when you start, you get a window like following.
Pictorial presentation of when you open MySQL Workbench
You may see that there are three sections – SQL Development, Data Modelling, and Server Administration.
Create a new database connection
The following slideshow shows you how to create a new connection using MySQL Workbench.
Create Database and table
Once the connection is created, you may use that connection to enter SQL Editor to do SQL Development tasks like creating/modifying/deleting modify database and tables, running SQL queries etc.
Edit table data
With MySQL Workbench you can select a connection, a schema and table under that to edit table data.
Edit SQL Scripts
Click on Edit SQL Scripts to edit an existing SQL script.
Manage Connections
You can manage existing connections using MySQL Workbench.
Click on Manage Connections using the link and then from the windows as shows above, you can create a new connection, delete an existing connection, create a clone of an existing connection and change parameters like name, method, username, port, password, default schema etc. of an existing connection.
Data Modelling
Using this section, you can create a new Entity Relationship models using various components available, creating EER from an existing database, from a SQL Script and open an existing one.
The following slideshow shows how to create an EER from an existing database.
Server Administration
You can create new Server Instance and manage existing Server Instances on one hand and on the other you can manage security, that is creating and managing user and provide them with permissions to perform various tasks on MySQL objects and manage import and export data and structure.
Manage Security
Import & Export
We have covered the basic tasks you can perform with MySQL Workbench in this tutorial. Hopefully, this will be helpful to you and get you started with MySQL Administration.
In this section we are going to discuss how to install MySQL on windows and Linux machines.
Download MySQLYou can download MySQL from http://dev.MySQL.com/downloads/.
Install MySQL on Linux (RedHat and its variants)
1. After downloading the required RPM package, you have to run the appropriate package with root privilege.
Syntax: rpm -i PackageName.rpm
With the similar syntax, you can install MySQL client, workbench or any other software downloaded and required.
Install MySQL on Linux (Ubuntu and Debian)
Syntax for installing MySQL server
apt-get install MySQL-server
Copy
Syntax for installing PHP module MySQL
sudo apt-get install php5-MySQL
Copy
Install MySQL on Windows
Download the binary distributions (MSI files) of MySQL server and other necessary MySQL tools from MySQL site.
Run MSI files on your Windows Machines. Make sure that you have privilege for installing software applications. Follow the instructions.
After the installation is completed, if you want to make PHP work with MySQL, make sure you complete the following steps –
1. Open php.ini file. Find and uncomment the following lines – extension=php_MySQL.dll extension=php_MySQLi.dll.
2. Create a php file on the root of your Apache or any other web server you are using. Write echo phpinfo(); in that php file. Run that PHP file form your browser (http://localhost/info.php, if your php file name is info.php). You should be able to see the following output if you have configured PHP and MySQL properly.
