MySQL Questions & Answers (Compiled from UPSC, SSC ,PSC ,IBPS previous question papers)

  • Explain Access Control Lists.

  • AAn ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basis for MySQL server’s security model and it helps in troubleshooting problems like users not being able to connect. MySQL keeps the ACLs (also called grant tables) cached in memory. When a user tries to authenticate or run a command, MySQL checks the authentication information and permissions against the ACLs, in a predetermined order.
  • What is the usage of regular expressions in MySQL?

  • A

    In MySQL, regular expressions are used in queries for searching a pattern in a string.

    • * Matches 0 more instances of the string preceding it.
    • + matches 1 more instances of the string preceding it.
    • ? Matches 0 or 1 instances of the string preceding it.
    • . Matches a single character.
    • [abc] matches a or b or z
    • | separates strings
    • ^ anchors the match from the start.
    • "." Can be used to match any single character. "|" can be used to match either of the two strings
    • REGEXP can be used to match the input characters with the database.

    Example:

    The following statement retrieves all rows where column employee_name contains the text 1000 (example salary):

     
    1. Select employee_name  
    2. From employee  
    3. Where employee_name REGEXP '1000'  
    4. Order by employee_name  
  • How to enter Characters as HEX Numbers?

  • AIf you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox). A HEX number string will be automatically converted into a character string, if the expression context is a string.
  • How MySQL Optimizes DISTINCT?

  • A

    DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.

  • Explanation:

     

  • What is MySQL data directory?

  • AMySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default the information managed my MySQL = server mysqld is stored in data directory.
  • What is InnoDB?

  • AlnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.
  • What is the use of mysql_close()?

  • AMysql_close() cannot be used to close the persistent connection. Though it can be used to close connection opened by mysql_connect().
  • What is ISAM?

  • AISAM is abbreviated as Indexed Sequential Access Method.It was developed by IBM to store and retrieve data on secondary storage systems like tapes.
  • What are the advantages of MyISAM over InnoDB?

  • AMyISAM follows a conservative approach to disk space management and stores each MyISAM table in a separate file, which can be further compresses, if required. On the other hand, InnoDB stores the tables in tablespace. Its further optimization is difficult.
  • How is MyISAM table stored?

  • A

    MyISAM table is stored on disk in three formats.

    • '.frm' file : storing the table definition
    • '.MYD' (MYData): data file
    • '.MYI' (MYIndex): index file
  • Is Mysql query is case sensitive?

  • ASELECT VERSION(),CURRENT_DATE;
    SeLect version(),current_date;
    seleCt vErSiOn(),current_DATE;


    All these examples are same. It is not case sensitive.
  • What is the difference between BLOB and TEXT?

  • ABLOBs are binary large object holding huge data. 4 types of BLOB are TINYBLOB, BLOB, MEDIBLOB, and LONGBLOB. TEXT is case-sensitive BLOB. 4 types of TEXT are TINY TEXT, TEXT, MEDIUMTEXT, and LONG TEXT.
  • How do you backup a database in MySQl?

  • AMySQL Command - shell> mysqldump --databases test > dump.sql
    It is easy to backing up data with phpMyAdmin. Select the database you want to backup by clicking the database name in the left hand navigation bar. Then click the export button and make sure that all tables are highlighted that you want to backup. Then specify the option you want under export and save the output.
  • What is SQLyog?

  • ASQLyog program is the most popular GUI tool for admin. It is the most popular MySQL manager and admin tool. It combines the features of MySQL administrator, phpMyadmin and others MySQL front ends and MySQL GUI tools.
  • What is meant by transaction and ACID properties?

  • ATransaction is logical unit of work where either all or none of the steps should be performed. ACID is the abbreviation for Atomicity, Consistency, Isolation, and Durability that are properties of any transaction.