MySQL Interview Questions & Answers part 3
Q: – Explain "CHECK TABLE" statement?
The CHECK TABLE statement performs an integrity check on table structure and contents. It works for MyISAM and InnoDB tables. For MyISAM tables, it also updates the index statistics. If the table is a view, CHECK TABLE verifies the view definition.
Q: –Explain "REPAIR TABLE" statement?
The REPAIR TABLE statement corrects problems in a table that has become corrupted. It works only for MyISAM tables.
Q: –Explain "ANALYZE TABLE" statement?
The ANALYZE TABLE statement updates a table with information about the distribution of key values in the table. This information is used by the optimizer to make better choices about query execution plans. This statement works for MyISAM and InnoDB tables.
Q: – Explain "OPTIMIZE TABLE" statement?
The OPTIMIZE TABLE statement cleans up a MyISAM table by defragmenting it. This involves reclaiming unused space resulting from deletes and updates, and coalescing records that have become split and stored non-contiguously. OPTIMIZE TABLE also sorts the index pages if they are out of order and updates the index statistics.
Q: –What is "mysqlcheck Client Program"?
mysqlcheck checks, repairs, analyzes, and optimizes tables. It can perform all these operations on MyISAM tables, and can perform some of them on InnoDB tables. It provides a command-line interface to the various SQL statements that instruct the server to perform table maintenance, such as CHECK TABLE and REPAIR TABLE.
Q: –What is "myisamchk Utility"?
The myisamchk utility performs table maintenance on MyISAM tables.
Q: – What is the use of "INFORMATION_SCHEMA Database"?
The INFORMATION_SCHEMA database provides access to database metadata.INFORMATION_SCHEMA is a "virtual database" in the sense that it is not stored anywhere on disk. But like any other database, it contains tables, and its tables contain rows and columns that can be accessed by means of SELECT statements.
Q: –What is "binary backup"?
A binary backup is a copy of the files in which database contents are stored. Copying these files preserves the databases in exactly the same format in which MySQL itself stores them on disk. Restoration involves copying the files back to their original locations. Techniques for making binary backups include file copy commands.
Q: –What is "text backup"?
A text backup is a dump of database contents into text files. Restoration involves loading the file contents back into databases by processing them through the server. Techniques for making text backups include the SELECT … INTO OUTFILE SQL statement, mysqldump, and MySQL Administrator.
Q: – How you will grant the SELECT privilege for all tables in the TEST database to a user named Ravi, who must connect from the local host and use a password of Ravi123?
Use following command.
GRANT SELECT ON TEST.* TO 'Ravi'@'localhost' IDENTIFIED BY 'Ravi123';
Q: –Explain "REVOKE statement"?
The REVOKE statement to revoke privileges from an account.
Q: –Explain the command "SHOW GRANTS FOR 'root'@'localhost';"
It will show the account has global, database-level, and table-level privileges.