MySQL FAQ: Default Port, REGEXP, Index Limits, Common Queries, and Other Key Concepts

This document presents a series of frequently asked MySQL questions and their answers, offering practical information for developers and database administrators.

Default port numbers: MySQL uses port 3306 (both for the server and client).

REGEXP: Describes how MySQL uses regular expressions for pattern matching, with examples such as .*\.txt to match text files.

Maximum indexes: A standard MySQL table can have up to 16 indexes.

NOW() vs CURRENT_DATE(): NOW() returns the current date and time, while CURRENT_DATE() returns only the date.

Query to show first 20 rows: SELECT * FROM table_name LIMIT 0,20;

Query to display current date and time: SELECT NOW(); (for date only use SELECT CURRENT_DATE(); )

Savepoints: Defined points within a transaction, created with SAVEPOINT statements.

SQLyog: A popular GUI tool for MySQL management, combining features of phpMyAdmin and other front‑ends.

Backing up a database: Use phpMyAdmin’s Export function after selecting the database.

Comparison operators: =, <>, <=, <, >=, >, <<, >>, <=>, AND, OR, LIKE.

Count rows in a table: SELECT COUNT(user_id) FROM users;

Retrieve books with offset: SELECT book_title FROM books LIMIT 20,100;

Select teams with specific win counts: SELECT team_name FROM team WHERE team_won IN (1,3,5,7);

MyISAM storage files: .frm (table definition), .MYD (data), .MYI (index).

ENUM usage: Defines a set of allowed string values; inserting a value outside the set causes an error.

MyISAM vs InnoDB advantages: MyISAM stores each table in separate files and can be compressed; InnoDB stores tables in a shared tablespace.

Result fetching functions: mysql_fetch_object() returns objects, mysql_fetch_array() returns arrays, mysql_fetch_row() returns numeric arrays.

Connection functions: mysql_connect() opens a new connection; mysql_pconnect() opens a persistent connection.

mysql_close(): Closes non‑persistent connections opened by mysql_connect() .

Data directory: Default locations are C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0\data on Windows.

Regular expression operators in MySQL: *, +, ?, ., [abc], |, ^, etc., used with the REGEXP operator.

Dummy flag: The i-am-a-dummy flag prevents UPDATE and DELETE without a WHERE clause.

Viewing table contents: Use the SELECT statement.

Access Control List (ACL): Lists permissions associated with objects; MySQL checks ACLs before executing commands.

InnoDB: A storage engine providing ACID transactions and foreign key support.

ISAM: IBM’s file management system allowing sequential or random record access.

Batch mode: Run MySQL in batch with commands like mysql; and mysql mysql.out;

Federated tables: Tables that reference tables on remote servers.

Primary vs candidate keys: Primary key uniquely identifies rows; candidate keys are alternative unique identifiers.

Available drivers: PHP, JDBC, ODBC, C Wrapper, Python, Perl, Ruby, CAP11PHP, Ado.net5.mxz.

SQL command categories: DDL (e.g., CREATE TABLE), DML (e.g., INSERT, SELECT), DCL (e.g., GRANT).