MySQL online course

MySQL SQL Command Syntax: Statements, Keywords, and Basic Usage

Learn MySQL SQL command syntax, keywords, statements, semicolons, case rules, database selection, basic SELECT queries, and the mysql command-line client.

SQL, or Structured Query Language, is used to define, query, and modify data in relational databases. MySQL is a relational database management system that accepts its own MySQL-flavored form of SQL.

This lesson introduces the syntax conventions needed to enter basic commands in the mysql client, the command-line program used to connect to a MySQL server and run SQL statements.

What SQL Commands Are in MySQL

A statement is a complete SQL instruction sent to the MySQL server for execution. A statement is usually made from:

  • Keywords, which express an operation or part of a query, such as SELECT or FROM.
  • Identifiers, which name database objects such as databases, tables, and columns.
  • Expressions, which calculate or compare values.
  • Literal values, which are values written directly in a statement, such as 10 or 'active'.
  • Optional clauses, which add conditions or instructions, such as WHERE, ORDER BY, and LIMIT.

For example:

SELECT first_name, last_name
FROM employees
LIMIT 10;

This is readable as an instruction: select two columns from the employees table and return no more than ten rows.

SQL syntax is designed for describing data operations rather than for writing general-purpose application logic. A programming language commonly describes loops, variables, and control flow one instruction at a time. SQL usually describes the result or database operation wanted, and the database server determines how to execute it. SQL syntax is standardized in broad areas, but individual database systems have different features and syntax extensions. This lesson focuses on MySQL syntax.

Keywords, Identifiers, Values, and Clauses

A keyword is a recognized SQL word that expresses an operation or clause. Common beginner-level keywords and keyword combinations include:

  • SELECT retrieves data.
  • FROM identifies the table or other source of the data.
  • USE changes the active database for the current session.
  • LIMIT restricts the number of rows returned.
  • CREATE begins statements that define database objects.
  • DATABASE identifies the type of object used with CREATE, as in CREATE DATABASE.

A statement can contain one or more keywords, object names, values, and optional clauses. The following elements have different roles:

Keyword: expresses an operation or part of a statement. Example: SELECT.

Identifier: names an object. Example: customers as a table name or email as a column name.

Literal value: a value written directly in the statement. Example: 10 or 'London'.

Clause: a meaningful section of a statement. Example: FROM customers or LIMIT 10.

Statement delimiter: normally a semicolon marking the end of a client statement. Example: ;.

In SELECT * FROM customers;, SELECT and FROM are keywords, customers is a table identifier, * is a wildcard meaning all columns, and the semicolon terminates the statement.

The Order of a Basic SELECT Statement

The common order of clauses in a basic query is:

SELECT column_list
FROM table_name
WHERE condition
ORDER BY column_name
LIMIT row_count;

WHERE, ORDER BY, and LIMIT are optional in this pattern. When present, they normally appear in this order. The query must begin with SELECT, and the source table is specified by FROM.

SELECT and FROM

SELECT retrieves rows from a data source. The items immediately after it specify which columns to return. FROM identifies the source table.

SELECT first_name, last_name
FROM employees;

This asks MySQL to return the first_name and last_name columns from the employees table.

An asterisk is a wildcard for all columns:

SELECT * FROM customers;

This is useful for quick exploration, but selecting explicit columns is generally preferable in production-oriented queries. It makes the required output clear, avoids transferring unnecessary data, and is less affected when a table gains new columns.

LIMIT

LIMIT restricts the maximum number of rows returned:

SELECT * FROM employees LIMIT 10;

This returns at most ten rows. It is particularly useful when inspecting an unfamiliar or large table. Without an ORDER BY clause, the selected rows should not be assumed to have a meaningful order.

For more query patterns, see Query a Database and Limit Clause.

Statement Terminators and Multiple Commands

The semicolon is the normal statement delimiter in the MySQL client. It tells the client that the statement is complete and can be sent to the server.

SELECT * FROM customers;

The client can receive multiple completed statements in sequence when each statement has its own terminator:

USE employees;
SELECT * FROM employees LIMIT 10;

After entering the first semicolon, the client executes the USE statement. It then reads and executes the SELECT statement after its semicolon.

If a statement has not been terminated, the client shows a continuation prompt rather than executing it immediately. The prompt indicates that the client is still collecting input for the current statement. This can happen because the semicolon is missing or because a quote or parenthesis has not been closed.

mysql> SELECT * FROM customers
    -> ;

You can finish the statement by entering the missing delimiter. If the input is incorrect, cancel the unfinished entry and re-enter the command according to the client version and terminal controls you use.

Case Rules and Naming Conventions

MySQL keywords are generally case-insensitive, so these statements normally have the same meaning:

select * from customers;
SELECT * FROM customers;

Uppercase keywords are the conventional formatting style because they make the structure of a query easier to scan. Identifiers are different from keywords: database, table, and column names have their own naming and case-handling rules.

SQL keywords: generally case-insensitive. Recommended style: uppercase, such as SELECT and FROM.

Database names: case behavior can depend on the server platform and configuration. Recommended style: use a consistent lowercase convention.

Table names: may be case-sensitive on Unix-like systems, while Windows commonly treats them case-insensitively. Recommended style: use consistent lowercase table names.

Column names: should also follow a consistent naming convention. Always use the exact intended identifier and avoid relying on inconsistent capitalization.

A table name that works on one machine can fail on another if its capitalization differs and the systems handle table names differently. Treat identifier case as significant for portability, especially for table names on Unix-like systems.

Using the MySQL Command-Line Client

Start the client with a MySQL account name by running:

mysql -u your_user -p

-u supplies the account name, and -p tells the client to prompt for authentication. At a high level, the server checks the account credentials and permissions before allowing the session to connect.

After a successful connection, you commonly see a prompt similar to:

mysql>

The mysql> prompt indicates that the client is ready to accept a MySQL command. A continuation prompt indicates that the previous statement is not complete yet.

Query output commonly contains:

  • Column headings, which identify each returned column.
  • Data rows, which contain the values returned by the query.
  • A row count, such as the number of rows in the result.
  • Execution timing or status information, depending on the client version and display format.

A result may look conceptually like this:

mysql> SELECT first_name, last_name FROM employees LIMIT 2;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Ana        | Silva     |
| Omar       | Khan      |
+------------+-----------+
2 rows in set (0.00 sec)

The headings are not data rows. They label the values beneath them. Before interpreting the result, verify the active database and the table name used by the query.

For command-line setup and connection guidance, see Start the Command Line Interface and Access MySQL.

Selecting a Database

The active database is the database selected for the current MySQL session. If a query uses an unqualified table name such as employees, MySQL needs an active database in which to find that table.

Use the USE statement to select a database:

USE training_db;

After a successful change, MySQL returns a confirmation similar to:

Database changed

The selection applies to the current client session. It does not rename the database or change any table data.

You can query a table without changing the active database by using a fully qualified table name. This is a table reference that includes the database name followed by a period:

SELECT * FROM employees.employees LIMIT 10;

Here, the first employees is the database name and the second is the table name. Fully qualified names are useful when working with multiple databases or when you want the query to state its source explicitly.

Creating a Database

The basic CREATE DATABASE statement creates a database:

CREATE DATABASE training_db;

You need the appropriate MySQL privilege to create databases. A safer practice script uses IF NOT EXISTS:

CREATE DATABASE IF NOT EXISTS training_db;
USE training_db;

IF NOT EXISTS tells MySQL to create the database only when a database with that name does not already exist. If it already exists, MySQL does not create a second database with the same name. Depending on the situation, the server may issue a warning, so this option should not be interpreted as proof that a new database was created.

After creating or confirming the database, USE makes it active for subsequent unqualified table references. More database-creation examples are available in Create a Database.

Reading Query Results Safely

When exploring a database, use a small, deliberate query first:

SELECT * FROM employees LIMIT 10;

Check the following before drawing conclusions from the output:

  1. Confirm the active database, or use a fully qualified table name.
  2. Confirm that the table name is spelled and capitalized as stored by the server.
  3. Read the column headings to understand what each value represents.
  4. Use LIMIT to avoid producing an unnecessarily large result.
  5. Select explicit columns when you know which fields you need.

For example, this query is more focused than selecting every column:

SELECT first_name, last_name
FROM employees
LIMIT 10;

Basic Commands at a Glance

CREATE DATABASE: creates a database. Typical result: a success message, or a notice when IF NOT EXISTS finds an existing database.

USE: changes the active database. Typical result: Database changed.

SELECT: retrieves data. Typical result: column headings, data rows, a row count, and timing information.

LIMIT: restricts the number of rows returned by a query. Typical result: a smaller result set, with no more than the specified number of rows.

Troubleshooting Common Syntax Problems

The client shows a continuation prompt

Likely cause: The statement has no delimiter, or it contains an unmatched quote or parenthesis.

Resolution: Finish the statement correctly and add the semicolon. If the input is beyond repair, cancel the unfinished command and enter it again.

No database is selected

Likely cause: The query uses an unqualified table name, but the session has no active database.

Resolution: Run USE database_name; or write the table reference as database_name.table_name.

A table is missing on one machine

Likely cause: The table's letter case differs, and the server platform or configuration handles table names differently.

Resolution: Check the exact stored table name and use a consistent lowercase naming convention, especially for tables shared across operating systems.

CREATE DATABASE is denied

Likely cause: The connected account does not have permission to create databases.

Resolution: Connect with an authorized account or ask a database administrator to grant the required privilege.

SELECT * returns too much data

Likely cause: The query requests every column and every matching row.

Resolution: Add a LIMIT while inspecting data and list only the columns required by the task.

Key Points to Remember

  • MySQL accepts SQL statements for defining, querying, and modifying relational data.
  • A statement combines keywords with identifiers, values, expressions, and optional clauses.
  • A common query order is SELECT, FROM, WHERE, ORDER BY, then LIMIT.
  • The semicolon normally tells the mysql client that a statement is complete.
  • Keywords are generally case-insensitive, but identifier case handling is separate and table-name behavior can vary by platform.
  • Use USE database_name; before querying tables by unqualified name.
  • Use LIMIT when inspecting unfamiliar tables, and prefer explicit column lists for production queries.

Next, you can learn how to define table structures with Create a Table, or explore filtering and sorting with Sort Results.