MySQL Interview Questions – Set 10

by

How to change the table name in MySQL?

Sometimes our table name is non-meaningful. In that case, we need to change or rename the table name. MySQL provides the following syntax to rename one or more tables in the current database:

mysql> RENAME old_table TO new_table;
If we want to change more than one table name, use the below syntax:

RENAME TABLE old_tab1 TO new_tab1,
old_tab2 TO new_tab2, old_tab3 TO new_tab3;

How to set auto increment in MySQL?

Auto Increment is a constraint that automatically generates a unique number while inserting a new record into the table. Generally, it is used for the primary key field in a table. In MySQL, we can set the value for an AUTO_INCREMENT column using the ALTER TABLE statement as follows:

ALTER TABLE table_name AUTO_INCREMENT = value;

What is the difference between UNIX timestamps and MySQL timestamps?

Actually, both Unix timestamp and MySQL timestamp are stored as 32-bit integers, but MySQL timestamp is represented in the readable format of YYYY-MM-DD HH:MM:SS format.

What is a trigger in MySQL?

A trigger is a set of codes that executes in response to some events.

What is the query to display the top 20 rows?

SELECT * FROM table_name LIMIT 0,20;

What are the advantages of MyISAM over InnoDB?

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

How to create a Stored Procedure in MySQL?

A stored procedure is a group of SQL statements that we save in the database. The SQL queries, including INSERT, UPDATE, DELETE, etc. can be a part of the stored procedure. A procedure allows us to use the same code over and over again by executing a single statement. It stores in the database data dictionary.

We can create a stored procedure using the below syntax:

CREATE PROCEDURE procedure_name [ (parameter datatype [, parameter datatype]) ]
BEGIN
Body_section of SQL statements
END;
This statement can return one or more value through parameters or may not return any result. The following example explains it more clearly:

DELIMITER $$
CREATE PROCEDURE get_student_info()
BEGIN
SELECT * FROM Student_table;
END$$

How to check USERS in MySQL?

If we want to manage a database in MySQL, it is required to see the list of all user’s accounts in a database server. The following command is used to check the list of all users available in the database server:

mysql> SELECT USER FROM mysql.user;

What is the difference between MySQL_connect and MySQL_pconnect?

Mysql_connect:

  • It opens a new connection to the database.
  • Every time you need to open and close the database connection, depending on the request.
  • Opens page whenever it is loaded.

Mysql_pconnect:

  • In Mysql_pconnect, “p” stands for persistent connection, so it opens the persistent connection.
  • The database connection cannot be closed.
  • It is more useful if your site has more traffic because there is no need to open and close connection frequently and whenever the page is loaded.

Write a query to count the number of rows of a table in MySQL.

SELECT COUNT user_id FROM users;