Essential MySQL Q&A: Master Version, Queries, Functions, and More

1. How to use SELECT to find the server version and print the current database name?

Answer: The following statement displays the server 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)

The NULL in the DATABASE column indicates that no database is selected. Select a database first:

mysql> USE Tecmint;
Database changed

mysql> SELECT VERSION(), DATABASE();

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

2. List all user records from table "Tecmint" except "SAM" using the NOT operator (!)

Answer: Use the following query:

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. Can the AND operator be used together with the NOT operator (!)?

Answer: When using the equality operator (=), combine conditions with AND. When using the inequality operator (!=), combine with OR. Example using = and AND: mysql> SELECT * FROM mail WHERE user = SAM AND root = phil; Example using != and OR:

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

!= : not equal

! : logical NOT operator

In MySQL, AND and OR are treated as logical connectors.

4. What does IFNULL() do in MySQL?

Answer: IFNULL() tests its first argument; if it is not NULL, the function returns that value, otherwise it returns the second argument.

mysql> SELECT name, IFNULL(id,'Unknown') AS id FROM taxpayer;

+---------+----------+
| name    | id       |
+---------+----------+
| bernina | 198-48   |
| bertha  | Unknown  |
| ben     | Unknown  |
| bill    | 475-83   |
+---------+----------+

5. How to retrieve a specific number of records from the beginning or end of a result set?

Answer: Use LIMIT after an ORDER BY clause.

Show one record

mysql> SELECT * FROM name LIMIT 1;

+----+------+------------+-------+----------------------+------+
| id | name | birth      | color | foods                | cats |
+----+------+------------+-------+----------------------+------+
| 1  | Fred | 1970-04-13 | black | lutefisk,fadge,pizza| 0    |
+----+------+------------+-------+----------------------+------+

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

Show the first record after ordering by birth

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. How to choose between Oracle and MySQL, and why?

Answer: Both have advantages and disadvantages; considering time constraints, MySQL is preferred.

Reasons to choose MySQL over Oracle

MySQL is open source.

MySQL is lightweight and fast.

MySQL offers strong command‑line and GUI support.

MySQL can be managed with tools like Query Browser.

7. How to get the current date in MySQL?

Answer: Use the following SELECT statement:

mysql> SELECT CURRENT_DATE();

+----------------+
| CURRENT_DATE()|
+----------------+
| 2014-06-17     |
+----------------+

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

Answer: Use the -e (export) option:

mysql -u USER_NAME -xml -e 'SELECT * FROM table_name' > table_name.xml

Replace USER_NAME with the database user, table_name with the table to export, and table_name.xml with the output file.

9. What is MySQL_pconnect and how does it differ from MySQL_connect?

Answer: MySQL_pconnect() opens a persistent connection that remains open across page loads, so MySQL_close() cannot be used. In contrast, MySQL_connect() opens a new connection for each page request, which can be closed with MySQL_close().

10. How to list all indexes of the user table in the mysql database?

Answer: Run the following command:

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 stands for Comma‑Separated Values (or Character‑Separated Values). A CSV table stores data as plain‑text rows where each record’s fields are separated by a delimiter such as a comma or semicolon. It is widely used for importing and exporting contact lists and other plain‑text data.