MySQL has following types of Storage Engine :
- MyISAM, The default storage engine till the release of mysql version 5.5. Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type, an .frm file stores the table format, the data file has an .MYD(MYData) extension, the index file has an .MYI (MYIndex) extension.
- InnoDB, becomes the default storage engine with the release of MySQL 5.5, InnoDB is a transaction-safe storage engine which obeys all the ACID property and has commit, rollback, and crash-recovery
capabilities to protect user data.
- Merge, the MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one. “Identical” means that all tables have identical column and index information. You cannot merge MyISAM tables in which the columns are listed in a different order, do not have exactly the same columns, or have the indexes in different order.
- Heap/Memory, the MEMORY or HEAP storage engine creates special-purpose tables with contents that are stored in memory. Because the data is vulnerable to crashes, hardware issues, or power outages, only use these tables as temporary work areas or read-only caches for data pulled from other tables.
- NDB,also known as NDBCLUSTER — This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.
- CSV, The CSV storage engine stores data in text files using comma-separated values format. You can use the CSV engine to easily exchange data between other software and applications that can import and export in CSV format.
AES_DECRYPT() are the MySQL functions use to encrypt and decrypt data. It uses AES (Advanced Encryption Standard) algorithm to achieve the same.
Mysql gives a number of ways to find a record with second highest value. Some of few are:
- By using aggregate function (Complex way), you can use the mysql
max() function using in the below example.
# To find only 2nd highest value
SELECT max(`field_name`) FROM `table_name`
#To find all the attributes that poses 2nd highest value
SELECT * FROM `table_name` WHERE `field_name` = (
SELECT max( `field_name` ) FROM `table_name` WHERE `field_name` <
(SELECT max( `field_name` ) FROM `table_name` ) ) ;
- Without using aggregate function (Simple way) , you can first arrange the records in descending order and then you can limit the records , see the below example.
SELECT * FROM `table_name` ORDER BY `field_name` DESC LIMIT 1 , 1 ;
# LIMIT 1,1 represents the offset value and the limit value respectively. #
Use the following command to backup/dump a mysql database.
mysqldump -u [user-name] -p[password] [database_name] > dumpfilename.sql
#the following example will backup the database "test"
mysqldump -u root -p test > test.sql
#Backup multiple databases
mysqldump -u root -p test1 test2 test3 > dumptest.sql //the above example will backup the test1 test2 test3 database to dumptest.sql
#Backup all the databases
mysqldump -u root -p --all-databases > all-database.sql
#Backup a database table
mysqldump -u [username] -p[password] [db] [table] \ > dumpfile.sql
Use the following command to restore a MySQL database or dump.
mysql -u [user-name] -p[password] [database_name] < dumpfilename.sql
#the following example will restore the database "test"
mysql -u root -p test < test.sql
password of the remote server database to connect. See the following example.
#To connect to a remote db server
mysql -h [hostname] -u [username] -p[password] // this will connect you to remote server
mysql -h 192.168.0.1 -u root -ptempaassword
#To list all database;
mysql > show databases; //to list all the database present
#To use a database;
mysql > use db-name; // to use desired database
MySql table allows following 6 triggers:
- BEFORE INSERT
- AFTER INSERT
- BEFORE UPDATE
- AFTER UPDATE
- BEFORE DELETE and
- AFTER DELETE
FLOAT stores floating point numbers with accuracy up to eight places and has four bytes while
DOUBLE stores floating point numbers with accuracy upto 18 places and has eight bytes.
Advantages of MySQL in comparison with Oracle.
- MySQL is open source software available at zero cost.
- It is portable
- It can be used as both GUI and command prompt.
- Administration is supported by MySQL Query Browser.
BLOB sorting and comparison is performed in case-sensitive for BLOB values whereas in
TEXT types sorting and comparison is performed case-insensitive.