Match Group MySQL Interview Questions Answers

Match Group Most Frequently Asked Latest MySQL Interview Questions Answers

What is MySQL data directory? How to determine the location of the data directory?

MySQL stores its data on the disk on the data dictionary. Each subdirectory under this data dictionary represents a MySQL database, inside those directories. By default the information managed my MySQL = server mysqld is stored in data directory.A default location of data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data..

What you can use Regular Expression for in MySQL? Support your answer with an example.

Regular expressions in MySql are used in queries for searching a pattern in a string.

1. * Matches 0 more instances of the string preceding it.
2. + matches 1 more instances of the string preceding it.
3. ? Matches 0 or 1instances of the string preceding it.
4. . Matches a single character.
5. [abc] matches a or b or z
6. | separates strings
7. ^ anchors the match from the start.

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):

Select employee_name
From employee
Where employee_name REGEXP ‘1000’
Order by employee_name

“.” Can be used to match any single character. “|” can be used to match either of the two strings

Match Group Most Frequently Asked Latest MySQL Interview Questions Answers

How will you export tables as an XML file in MySQL?

From the command prompt type the following statement:

mysql -u test --xml -e 'SELECT * FROM t1' > t1.xml

where ‘–u test‘ is the user name, --xml indicates the type of the file is xml, -e for export

What is the use of i-am-a-dummy flag in MySQL?

The flag i-am-a-dummy flag makes the MySQL engine to deny the UPDATE and DELETE commands unless the WHERE clause is present.

What are the differences between MySQL_fetch_array(), MySQL_fetch_object(), MySQL_fetch_row()?

The mysql_fetch_object() returns the result from the database as an object.

Ex: $result->name

The mysql_fetch_array() returns the result from the database as an associative array or numeric array or both by using mysql_NUM or mysql_ASSOC options.

EX: $result[0] ,$result['name']

The mysql_fetch_row() returns the result from the database as a numeric array.

Ex: $result[0]

What is difference between mysql_connect and mysql_pconnect?

Mysql_connect:

- Opens a new connection to the database.
- The database connection can be closed.
- Opens the page every time the page is loaded.

Mysql_pconnect:

- Opens a persistent connection to the database.
- The database connection can not be closed.
- The page need not be opened every time the page is loaded.

What is MySQL data directory? How to determine the location of the data directory?

MySQL data directory is most important location in which all MySQL databases are stored. The default data directory is located in the file mysql.

If the out of the space is the issue, then the directory need to be moved another location. Before moving, the database need to be closed. After moving the MySQL configuration file need to be edited. Look for the ‘datadir’ entry and change the path to the new directory.

What you can use Regular Expression for in MySQL? Support your answer with an example.

Regular expressions are a set of characters. Regular expressions are used for finding certain sequences in strings.

The following are the regular expression characters:

- * Represents matching characters that are 0 or more occurrences of the string that precedes it.
- + Represents matching characters that are 1 or more occurrences of the string that precedes it.
- ? Represents matching characters that are 0 or 1 occurrences of the string that precedes it.
- . Represents single character pattern matching.
- [abc] Represents matching characters that is either a or b or c.
- | Used for separation of strings.
- ^ Finds the matching pattern starting from the beginning.

To match the input characters with database tables, REGEXP is used. For example:

Select cityname
from territories
where cityname REGEXP ‘^(jo)*’;

The above query returns all city names that has the substring ‘jo’.

What are the steps required to view your MYSQL database?

There are certain steps that are required to before you can view MySQL database and they are as follows:

1. Login as database user

example:

[root@ tmp]# mysql -u mysqluser -p sales

2. List all your MySQL databases: use the show command to view the list of all available MySQL databases.

example

To view sales database use the command shown below:

mysql > show databases;
| Database |
| salesdata |

Write a command to view MySQL database table structure

To view the database table structure describe command is used that provides the list of all the data fields used in the database table.

Example

A table named sales will have the four fields like: name, description, num and date.

mysql > describe test;
| Field | Type | Null | Key | Default | Extra|
| num | int(11)| |PRI | NULL | auto_increment |
| date | date| | MUL | 0000-00-00 |
| name | varchar(50) | MUL |
| description | varchar(75) | YES | | NULL

Write a command to view the content of the table

To view all the data that is contained inside a table named sales use the select command. For example: to see the data of first row in a table using the command as:

mysql > select * from sales limit 1;

If the database is created recently then it will give a blank listing, but after the data is being entered it will show the full listing in a tabular form.

What is the procedure to configure the application of MYSQL?

The procedure to configure the application of MySQL is:

1. First create a database
2. Test the database by informing about the database name, IP address of database client server, username and password of application.
3. Edit the special application specific configuration file using a GUI or command line.
4. Configure the language with which MySQL will interact.