11 Advanced MySQL Database interview questions and answers

Thanks to our support, we will be able to do so now, and thank you for your support. In this article, we'll focus on the practical skills of MySQL, and talk about questions related to the interview.

1. How do I use the SELECT statement to find the version of the server you are running and print out the name of the current database?

A: The result of the following statement shows the version of the server and the current database name

| VERSION ()               5.5.  34-0ubuntu0.  13.10. Inset (0.  SEC)      

Null is shown in the database column because we are not currently selecting any databases. Therefore, use the following statement to select a database before you can see the corresponding results.

This feature to get a quicker startup With-adatabase changed

Mysql> Select VERSION (), DATABASE (), +-------------------------+------------+| VERSION ()               5.5.  34-0ubuntu0.  13.10.| tecmint |+-------------------------+------------+ inset (0.  XX sec)        

2. Use non-operator (!) List all user records except "SAM" from table "Tecmint"

Answer: Use the following statement

Mysql> SELECT * from tecmint WHERE user!=sam;+---------------------+---------+---------+---------+---------+----- --+| date | user | host | root | Local | Size | +---------------------+---------+---------+---------+---------+-------+ | 2001-05-14 14 :42:21 | Anthony | Venus | Barb | Venus | 98151 | | 2001-05-15 08:50:57 | TIM | Venus | Phil | Venus | 978 | +---------------------+---------+---------+---------+-- -------+-------+ 

3. The ' and ' operator may take a non-operator (!) Use it together?

A: When we use ' = ' to connect with ' and ', with '! = ' when connected with ' OR ', the following is an example of ' = ' and the AND operator

and root = Phil

Examples of '! = ' and OR operators

mysql> select * from mail WHERE user! = SAM OR Root! = phil+---------------------+---------+---- -----+---------+---------+-------+ | date | user | H OST | Root | local | size | +---------------------+---------+--------- +---------+---------+-------+ | 2001-05-14 14:42: 21 | Anthony | Venus | Barb | Venus | 98151 | +---------------------+---------+-- -------+---------+---------+-------+ 

• =: equals
• ! =: Not equal to
• ! : operator that represents "non"

And and or are treated as join operators in MySQL

4. Ifnull () What is the role of playing in MySQL?

A: Using the Ifnull () method can make queries in MySQL more accurate. The Ifnull () method will test its first argument, return the value of the parameter if it is not NULL, or return the value of the second parameter

mysql> SELECT name, ifnull (ID,| name    | id |      198-48  475-| +---------+--------- +

5. What if you want to know only a few records from the beginning or end of a result set?

A: We need to use the limit statement after the order BY statement to achieve this effect.

Display a row of records

| Birth      | Foods                1970-04-| 0 | +----+------+------------+-------+----------------------+--- ---+

displaying 5 rows of records

Mysql> SELECT * from Profile LIMIT5;+----+------+------------+-------+-----------------------+------+| ID | Name| Birth | Color| Foods | Cats| +----+------+------------+-------+-----------------------+------+ |1| Fred |1970-04-13| Black | Lutefisk,fadge,pizza| 0 || 2 | Mort| 1969-09-30 | White| Burrito,curry,eggroll | 3 | | 3 | Brit | 1957-12-01 | Red | Burrito,curry,pizza | 1 | | 4 | Carl | 1973-11-02 | Red | Eggroll,pizza | 4 | | 5 | Sean | 1963-07-04 | Blue | burrito,curry | 5 | +----+------+------------+-------+-- ---------------------+------+ 

Show first record after order by

| Birth      | Foods          1952-08-| 0 | +----+------+------------+-------+----------------+------+ 

6. How does Oracle and MySQL choose, and why?

A: They all have their own pros and cons. Considering the time factor, I tend to MySQL.

Why choose MySQL instead of Orcale

• MySQL Open source
• MySQL Lightweight fast
• MySQL support for command line and GUI is good
• MySQL support for management via query browser

7. How do I get the current date in MySQL?

Answer: Getting the current date in MySQL is as simple as the SELECT statement below.

Mysql> SELECT current_date (); +----------------+| Current_date () | +----------------+| 2014-06-17     |  +----------------+

8. How do I export a table to an XML file in MySQL?

A: We can use the '-e ' option to export MySQL tables or entire databases to an XML file. When working with large tables we may need to export them manually, but for small tables you can use tools like phpMyAdmin and so on.

' SELECT * FROM table_name ' > Table_name.xml

In the example above, user_name is the user name of the database, TABLE_NAME is the name of the table to be exported as an XML file, and Table_name.xml is the XML file that holds the data

9. What is Mysql_pconnect? What's the difference between it and mysql_connect?

A: Mysql_pconnect () opens a persistent database connection, which means that the database does not open a new connection every time the page loads, so we cannot use Mysql_close () to close a persistent connection.

The most concise differences between Mysql_pconnect and Mysql_connect are:

Unlike Mysql_pconnect, Mysql_connect opens the connection each time the page is loaded, and the connection can be closed using the mysql_close () statement.

10. What do you do when you need to see all the indexes in the ' user ' table in a database named ' MySQL '?

Answer: The following command will show all indexes in the ' user ' table

Mysql> Show index from user;+-------+------------+----------+--------------+-------------+-----------+--------- ----+----------+--------+------+------------+---------+---------------+| Table | Non_unique| Key_name | Seq_in_index| column_name | Collation| Cardinality | Sub_part| Packed | Null| Index_type | Comment| index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+--- ---+------------+---------+---------------+| user |0| PRIMARY | 1 | Host | A | NULL | NULL | NULL | | BTREE | | | | user | 0 | PRIMARY | 2 | User | A | 4 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+----- ---+------+------------+---------+---------------+ 2 rows in set (0.  XX sec)                  

11. What is a CSV table?

A: CSV is a comma-separated value (comma-separated values) or an abbreviation that is also known as a character-delimited value (character-separated values). CSV tables store data in plain text and tabular form.

Each record is separated by a specific delimiter (such as a comma, semicolon,...), and each record has the same column in the same order. CSV tables are most widely used to store phone contacts for import and export, and can be used to store any type of plain text data.

This is all that is to be said. I will also bring other interesting articles that you would like to have. Then stay tuned and visit Tecmint and don't forget to leave your valuable comments in the comments box below.

11 Advanced MySQL Database interview questions and answers