Advanced Mysql Database interview questions with answers _mysql

Thanks to our support, we can do it now and thank you for your support. In this article, we will focus on the practical skills of MySQL, talk about the interview related issues.

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 results of the following statement show the server's version and the current database name

 Mysql> SELECT VERSION (), DATABASE ();

 +-------------------------+------------+
 | VERSION ()    | DATABASE () |
 +-------------------------+------------+
 | 5.5.34-0ubuntu0.13.10.1 | NULL  |
 +-------------------------+------------+
 1 row in Set (0.06 sec)

Null is displayed in the database column because we are not currently selecting any databases. Therefore, you can see the corresponding results by using the following statement to select a database first.

mysql> use Tecmint;

Reading table information for completion of table and column names you can turn out this feature to get
a quicker star Tup with-a

Database changed
mysql> select VERSION (), database ();

+-------------------------+------------+
| VERSION ()    | DATABASE () |
+-------------------------+------------+
| 5.5.34-0ubuntu0.13.10.1 | tecmint
| +-------------------------+------------+
1 row in Set (0.00 sec)

2. Use a 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 is possible with a non-operator (!) Do you want to use it together?

A : when we use ' = ' to connect with ' and ', using '!= ' when connected with ' or ', here is an example of ' = ' and ' operator '

Mysql> SELECT * from mail WHERE user = SAM and root = Phil

Examples of '!= ' and OR operators

Mysql> SELECT * from mail WHERE user!= SAM OR root!= Phil

+---------------------+---------+---------+---------+-- -------+-------+ 
| date    | user | host | root | local | size | 
+---------------------+---------+---------+---------+---------+-------+ 
| 2001-05-14 14:42:21 | Anthony | Venus | Barb | Venus | 98151 | 
+---------------------+---------+---------+---------+---------+-------+

=: equal To
!=: Not equal to
! : An operator that represents "non"
And and or are treated as concatenation operators in MySQL

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

A : using the Ifnull () method can make the query in MySQL more accurate. The Ifnull () method tests its first argument and returns the value of the parameter if it is not NULL, otherwise the value of the second argument is returned

mysql> SELECT name, ifnull (ID, ' Unknown ') as ' id ' from taxpayer;

+---------+---------+ 
| name | ID  | 
+---------+---------+ 
| bernina | 198-48 | 
| Bertha | Unknown | 
| Ben  | Unknown | 
| Bill | 475-83 | 
+---------+---------+

5. How do you do this if you only want to know a specific few records that start at the beginning or end of a result set?

A : we need to use the limit statement after the order BY statement to achieve the above effect.

Show a row of records

Mysql> SELECT * from name LIMIT 1;

+----+------+------------+-------+----------------------+------+ 
| id | name | birth  | color | foods    | Cats | 
+----+------+------------+-------+----------------------+------+ 
| 1 | Fred | 1970-04-13 | Black | Lutefisk,fadge,pizza | 0 | 
+----+------+------------+-------+----------------------+------+

Show 5 lines of records

Mysql> SELECT * from Profile LIMIT 5;

+----+------+------------+-------+-----------------------+------+ 
| 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 | 
+----+------+------------+-------+-----------------------+------+

Displays the first record after ordered by

Mysql> SELECT * from Profile order by birth LIMIT 1;

+----+------+------------+-------+----------------+------+ 
| id | name | birth  | color | Foods   | cats 
| +----+------+------------+-------+----------------+------+ 
| 9 | Dick | 1952-08-20 | Green | Lutefisk,fadge | 0 | 
+----+------+------------+-------+----------------+------+

6. Oracle and MySQL How to choose, why?

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

Why not choose MySQL instead of Orcale

MySQL Open source
MySQL Lightweight and quick
MySQL support for command line and graphical interface is good
MySQL support is managed through query browser

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

A : getting the current date in MySQL is as simple as the following SELECT statement.

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 ' (export) option to export the MySQL table or the entire database to an XML file. When working with large tables we may need to export them manually, but for small tables you can use tools such as phpMyAdmin.

Copy Code code as follows:

Mysql-u user_name-xml-e ' 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, 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?

Answer: Mysql_pconnect () Opens a persistent database connection, which means that the database is not opened a new connection every time the page is loaded, so we cannot use Mysql_close () to turn off a persistent connection.

The most brief differences between Mysql_pconnect and Mysql_connect are:

Unlike Mysql_pconnect, Mysql_connect opens the connection every 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 look at all the indexes in the ' user ' table in a database called ' MySQL '?

A : 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.00 sec)

11. What is a CSV table?

Answer: CSV is an abbreviation of comma-separated values (comma-separated values) or also known as character-delimited values (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 a column with the same order. The CSV table is most widely used to store phone contacts for import and export, and can be used to store any type of plain text data.

The above is the entire content of this article, small series will bring other interesting articles that you should like, hope to be helpful to everyone's study, and hope that we can support cloud-dwelling community a lot.