Latest Top MySQL Interview Questions and Answers 2023

MySQL is a relational database management system. It can develop with the website as it’s far noticeably scalable. Most of the web sites nowadays are powered with the aid of using MySQL.

• Flexibility: MySQL runs on all operating systems
• Power: MySQL focuses on performance
• Enterprise-Level SQL Features: MySQL had for some time been lacking in advanced features such as subqueries, views, and stored procedures.
• Full-Text Indexing and Searching
• Query Caching: This helps enhance the speed of MySQL greatly
• Replication: One MySQL server can be duplicated on another, providing numerous advantages
• Configuration and Security

Use the following command to create a new database called ‘company’:

CREATE DATABASE company;

Use the following to create a table using MySQL:

CREATE TABLE author (
 author VARCHAR(128),
 title VARCHAR(128),
 type VARCHAR(16),
 year CHAR(4)
) ENGINE InnoDB;

The INSERT INTO statement is used to add new records to a MySQL table:

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

If we want to add values ​​for all columns of the table, we don’t need to specify the column names in the SQL query. However, the order of the values ​​must match the order of the columns in the table. The INSERT INTO syntax would be as follows:

INSERT INTO table_name VALUES (value1, value2, value3, ...);

The MySQL Server’s default port is 3306. Another common default port for SQL Server is 1433 in TCP/IP.

There are several types of indexes in MySQL, including a standard INDEX, a PRIMARY KEY, and a FULL-TEXT index. A quick search can be performed using an index. Indexes improve efficiency by telling the SQL engine where to look for your data, or by organizing the data on disk in a way that makes it easier to find the result.

Let’s see an example:

ALTER TABLE history ADD INDEX(size(10));
ALTER TABLE history ADD INDEX(title(10));
ALTER TABLE history ADD INDEX(price(5));

Numerous tables are always present by default. However, MySQL’s default database engine is MyISAM. There are now five different kinds of tables:

• MYISAM
• Heap
• Merge
• INNO DB
• ISAM

By executing the CREATE command and providing the required information, you can add a User.

Let’s see an example:

CREATE USER 'newuser' IDENTIFIED BY 'Your Password';

There are primarily two ways that MySQL enables us to connect to the database server:

The bin directory of the MySQL installation folder contains the command-line client utility. To run this software, use the following command in the bin directory of the installation folder:

mysql

To connect to the MySQL Server, we must next perform the command below:

shell>mysql -u root -p

Finally, key in the root user account password and hit Enter:

Enter password: ********

Using the command below after a successful connection, we can use the:

USE database_name;

CHAR is used to provide the fixed length of the table and columns when a table is constructed. The length value may fall between 1-255. The column and table lengths can be changed as needed by using the VARCHAR command.

The UPDATE statement, which contains the SET and WHERE clauses, can be used to update existing records in a table. The values ​​of the specified column are modified using the SET clause. Optionally, the WHERE clause can be used to define the condition. This statement can also be used to change values ​​in a single row or in multiple column rows at once. The generic syntax for the UPDATE command to update data in the MySQL table is as follows:

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

When specific events on a specific table or view in the database take place, a trigger is a procedural piece of code in the database that automatically runs. It can be run whenever records are added to a table or whenever any columns are modified.

The syntax used to construct a trigger in MySQL is as follows:

Referential integrity rules and entity integrity rules are the two categories of integrity rules.

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

MySQL uses the LIMIT keyword, which can be used to limit the result set. We can use it to get a range of rows, the first rows or the last rows. Finding the second, third, or nth highest salary is another use for it. It ensures that you first order the result set with the order by clause before printing the output that gives exact results. For the second highest salary in MySQL, use the following query.

SELECT salary FROM (SELECT salary FROM employees ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1;  

Sometimes the database name needs to be changed or renamed due to lack of meaning of the name. We must first create a new database on the MySQL server before we can change the database name. Copy the selected database and import all data into the newly created database. The syntax for the mysqldump command is as follows:

mysqldump -u username -p "password" -R oldDbName > oldDbName.sql

To import the data into the just formed database, run the command below:

mysql -u username -p"password" newDbName < oldDbName.sql

A primary key in MySQL is a single field or group of fields used to uniquely identify each record in a table. The primary key of a column cannot be empty or null. A primary key can be removed from the table using the ALTER TABLE statement. The primary key can be removed using the syntax shown below:

ALTER TABLE table_name DROP PRIMARY KEY;

A collection of SQL commands is called a stored procedure and is stored in the database. The stored procedure can contain SQL queries like INSERT, UPDATE, DELETE and others. By executing a single statement, a procedure allows us to repeat the same code. Save the data dictionary in the database.

CREATE PROCEDURE procedure_name [ (parameter datatype [, parameter datatype]) ]    
BEGIN    
    Body_section of SQL statements  
END;

A newly set up MySQL user needs specific credentials to perform various actions in the database. The GRANT command provides specific capabilities to the user. The following sample statement authorizes username@localhost to perform SELECT and INSERT operations on the customer table TABLE.

Common MySQL functions are as follows:

• NOWO: The function for returning the current date and time as a single value
• CURRDATEO: The function for returning the current date or time
• CONCAT (X, Y): The function to concatenate two string values creating a single string output
• DATEDIFF (X, Y): The function to determine the difference between two dates

The syntax for getting the current version of MySQL:

SELECT VERSION ();

While both are used to enforce the uniqueness of the defined column, the primary key would create a clustered index while the unique key would create a nonclustered index on the column. The primary key doesn’t allow ‘NULL’, but the unique key does.

The primary key in MySQL is used to uniquely identify each row in a table. There is only one primary key for a table. Candidate keys can be used to reference foreign keys. One of the candidate keys is the master key.

• MyISAM is the default table that is based on the sequential access method.
• Heap is the table that is used for fast data access, but the data will be lost if the table or the system crashes.
• InnoDB is the table that supports transactions using the COMMIT and ROLLBACK commands.
• BDB can support transactions similar to InnoDB, but the execution is slower.

The use of ENUM limits the values ​​that can be included in a table. For example, a user can create a table with specific monthly values ​​and other monthly values ​​would not be included in the table.

LIKE is denoted using the ‘%’ sign. For example

SELECT * FROM user WHERE user name LIKE “%NAME”

On the other hand, the use of REGEXP is as follows:

SELECT * FROM user WHERE username REGEXP “^NAME”;

In such cases when the password is lost, the user should start the DB with skip-grants-table and then change the password. Thereafter, with the new password, the user should restart the DB in a normal mode.

There are four types of joins in MySQL. The inner join returns the rows if there is at least one match in two tables. The left join returns all rows from the left table even if there is no match in the right table. The right join returns all rows from the right table even if there are no matches in the left table. The full join would return rows if there is at least one match in the tables.

BLOBs are binary large objects holding huge data. Four types of BLOBs are TINYBLOB, BLOB, MEDIBLOB, and LONGBLOB. TEXT is a case-sensitive BLOB. Four types of TEXT are TINY TEXT, TEXT, MEDIUMTEXT, and LONG TEXT.