MySQL Interview Questions – Set 08

by

What is MySQL data directory?

MySQL 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 the data directory.

How to clear screen in MySQL?

If we use MySQL in Windows, it is not possible to clear the screen before version 8. At that time, the Windows operating system provides the only way to clear the screen by exiting the MySQL command-line tool and then again open MySQL.

After the release of MySQL version 8, we can use the below command to clear the command line screen:

mysql> SYSTEM CLS;

How many columns can you create for an index?

You can a create maximum of 16 indexed columns for a standard table.

What are the disadvantages of MySQL?

  1. MySQL is not so efficient for large scale databases.
  2. It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.
  3. Transactions are not handled very efficiently.
  4. The functionality of MySQL is highly dependent on other addons.
  5. Development is not community-driven.

How do you backup a database in MySQL?

It is easy to back 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 back up. Then specify the option you want under export and save the output.

Why do we use the MySQL database server?

First of all, the MYSQL server is free to use for developers and small enterprises.

MySQL server is open source.

MySQL’s community is tremendous and supportive; hence any help regarding MySQL is resolved as soon as possible.

MySQL has very stable versions available, as MySQL has been in the market for a long time. All bugs arising in the previous builds have been continuously removed, and a very stable version is provided after every update.

The MySQL database server is very fast, reliable, and easy to use. You can easily use and modify the software. MySQL software can be downloaded free of cost from the internet.

How to change the MySQL password?

We can change the MySQL root password using the below statement in the new notepad file and save it with an appropriate name:

ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘NewPassword’;
Next, open a Command Prompt and navigate to the MySQL directory. Now, copy the following folder and paste it in our DOS command and press the Enter key.

C:Usersjavatpoint> CD C:Program FilesMySQLMySQL Server 8.0bin
Next, enter this statement to change the password:

mysqld –init-file=C:\mysql-notepadfile.txt
Finally, we can log into the MySQL server as root using this new password. After launches the MySQL server, it is to delete the C:myswl-init.txt file to ensure the password change.

How to view the database in MySQL?

Working with the MySQL server, it is a common task to view or list the available databases. We can view all the databases on the MySQL server host using the following command:

mysql> SHOW DATABASES;

What is the usage of ENUMs in MySQL?

ENUMs are string objects. By defining ENUMs, we allow the end-user to give correct input as in case the user provides an input that is not part of the ENUM defined data, then the query won’t execute, and an error message will be displayed which says “The wrong Query”. For instance, suppose we want to take the gender of the user as an input, so we specify ENUM(‘male’, ‘female’, ‘other’), and hence whenever the user tries to input any string any other than these three it results in an error.

ENUMs are used to limit the possible values that go in the table:

For example:

CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’); INSERT months VALUES (‘April’).

How to drop the primary key in MySQL?

MySQL primary key is a single or combination of the field used to identify each record in a table uniquely. A primary key column cannot be null or empty. We can remove or delete a primary key from the table using the ALTER TABLE statement. The following syntax is used to drop the primary key:

ALTER TABLE table_name DROP PRIMARY KEY;

What is BLOB and TEXT in MySQL?

BLOB is an acronym that stands for a large binary object. It is used to hold a variable amount of data.

There are four types of the BLOB.

  1. TINYBLOB
  2. BLOB
  3. MEDIUMBLOB
  4. LONGBLOB
    The differences among all these are the maximum length of values they can hold.

TEXT is a case-insensitive BLOB. TEXT values are non-binary strings (character string). They have a character set, and values are stored and compared based on the collation of the character set.

There are four types of TEXT.

  1. TINYTEXT
  2. TEXT
  3. MEDIUMTEXT
  4. LONGTEXT