MySQL Interview Questions – Set 06

by

What is REGEXP?

REGEXP is a pattern match using a regular expression. The regular expression is a powerful way of specifying a pattern for a sophisticated search.

Basically, it is a special text string for describing a search pattern. To understand it better, you can think of a situation of daily life when you search for .txt files to list all text files in the file manager. The regex equivalent for .txt will be .*.txt.

What are the drivers in MySQL?

Following are the drivers available in MySQL:

  • PHP Driver
  • JDBC Driver
  • ODBC Driver
  • C WRAPPER
  • PYTHON Driver
  • PERL Driver
  • RUBY Driver
  • CAP11PHP Driver
  • Ado.net5.mxz

How to create a Trigger in MySQL?

A trigger is a procedural code in a database that automatically invokes whenever certain events on a particular table or view in the database occur. It can be executed when records are inserted into a table, or any columns are being updated. We can create a trigger in MySQL using the syntax as follows:

CREATE TRIGGER trigger_name
[before | after]
{insert | update | delete}
ON table_name [FOR EACH ROW]
BEGIN
–variable declarations
–trigger code
END;

How to update the table in MySQL?

We can update existing records in a table using the UPDATE statement that comes with the SET and WHERE clauses. The SET clause changes the values of the specified column. The WHERE clause is optional, which is used to specify the condition. This statement can also use to change values in one or more columns of a single row or multiple rows at a time. Following is a generic syntax of UPDATE command to modify data into the MySQL table:

UPDATE table_name
SET field1=new-value1, field2=new-value2, …
[WHERE Clause]

How many Triggers are possible in MySQL?

There are only six Triggers allowed to use in the MySQL database.

  1. Before Insert
  2. After Insert
  3. Before Update
  4. After Update
  5. Before Delete
  6. After Delete

What is the use of mysql_close()?

Mysql_close() cannot be used to close the persistent connection. However, it can be used to close a connection opened by mysql_connect().

How to delete a table in MySQL?

We can delete a table in MySQL using the Drop Table statement. This statement removes the complete data of a table, including structure and definition from the database permanently. Therefore, it is required to be careful while deleting a table. After using the statement, we cannot recover the table in MySQL. The statement is as follows:

DROP TABLE table_name;

What are the advantages of MySQL in comparison to Oracle?

  1. MySQL is a free, fast, reliable, open-source relational database while Oracle is expensive, although they have provided Oracle free edition to attract MySQL users.
  2. MySQL uses only just under 1 MB of RAM on your laptop, while Oracle 9i installation uses 128 MB.
  3. MySQL is great for database enabled websites while Oracle is made for enterprises.
  4. MySQL is portable.

What is SQLyog?

SQLyog 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.

Which command is used to view the content of the table in MySQL?

The SELECT command is used to view the content of the table in MySQL.

Explain Access Control Lists.

An ACL is a list of permissions that are associated with an object. MySQL keeps the Access Control Lists cached in memory, and whenever the user tries to authenticate or execute a command, MySQL checks the permission required for the object, and if the permissions are available, then execution completes successfully.